/*
    Employee ID     |   Version     |   Date Range      |   CR ID       |   Propose
    5                   initial         19.01.22            5-220119        Introduction of Transaction Table Status Flag in Join Statement
    7                   initial         05.05.22            7-220505        joining issue fix
*/

DROP PROCEDURE `sp_ip_details_report`;
CREATE PROCEDURE `sp_ip_details_report`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `is_generated` VARCHAR(50), IN `report_type` VARCHAR(50), IN `doctor_id` INT(10))
BEGIN 
SET 
  @start_date = start_date;
SET 
  @end_date = end_date;
SET 
  SESSION sql_mode = '';
	DROP TEMPORARY TABLE IF EXISTS `tmp_ip_result_listing`;
	CREATE TEMPORARY TABLE `tmp_ip_result_listing`(
		`labdetails` INT DEFAULT 0,
		`booking_date` DATE NOT NULL,
		`case_id` VARCHAR(50) NOT NULL,
		`patient_name` VARCHAR(1000) NOT NULL,
		`docid` INT DEFAULT 0,
		`doctor_name` VARCHAR (255) NULL DEFAULT '',
		`ip_pay_mode` VARCHAR(50) DEFAULT NULL,
		`analyte_name` VARCHAR(50) NOT NULL DEFAULT '',
		`ip_remarks` VARCHAR(500) NULL,
		`paid_amount` DECIMAL(10,2) NULL DEFAULT '0',
		`barcode_no` VARCHAR(50) DEFAULT 'N/A',
		`net_amount` DECIMAL(10,2) NULL DEFAULT '0',
		`ip_charge` DECIMAL(10,2) NULL DEFAULT '0'
	);
	
	INSERT INTO `tmp_ip_result_listing` (
		labdetails,
		booking_date,
		case_id,
		patient_name,
		docid,
		doctor_name,
		ip_pay_mode,
		analyte_name,
		ip_remarks,
		paid_amount,
		barcode_no,
		net_amount,
		ip_charge
	)
	SELECT 
		lab_booking.id as lbdetails, 
		lab_booking.booking_date, 
		lab_booking.case_id, 
		CONCAT_WS('/', patients.patient_name, patients.age, patients.gender) patient_name, 
		doctors.id as docid,
		doctors.doctor_name, 
		doctors.ip_pay_mode,
		lab_analyte_master.analyte_name, 
		doctor_ip_transaction.remarks as ip_remarks,
		doctor_ip_transaction.paid_amount as paid_amount,
		COALESCE(lab_booking_details.barcode_no, 'NA') as barcode_no,
		(patient_service_charge_details.net_amount - (COALESCE(patient_service_charges.discount_percentage,0)/100)*patient_service_charge_details.net_amount),
		fn_get_analyte_ip_details(
				lab_analyte_master.id, 
				COALESCE(lab_booking.referred_doc_id, 0), 
				(patient_service_charge_details.net_amount 
					- 
				(COALESCE(patient_service_charges.discount_percentage,0)/100)*patient_service_charge_details.net_amount)
		) ip_charge
	FROM 
		lab_booking 
		JOIN lab_booking_details ON lab_booking_details.lab_booking = lab_booking.id 
		JOIN doctors ON lab_booking.referred_doc_id = doctors.id 
		JOIN patients ON patients.id = lab_booking.patient_id 
		JOIN lab_analyte_master ON lab_analyte_master.id = lab_booking_details.lab_analyte_id 
		JOIN service_rates ON service_rates.id = lab_analyte_master.charge_id
		JOIN patient_service_charges ON patient_service_charges.patient_id = lab_booking.patient_id 
		AND patient_service_charges.admission_no = lab_booking.case_id 
		JOIN patient_service_charge_details ON patient_service_charge_details.patient_service_charge_id = patient_service_charges.id 
		AND patient_service_charge_details.service_rate_id = service_rates.id 
		
		LEFT JOIN doctor_ip_transaction ON doctor_ip_transaction.ip_doctor_id = doctors.id AND doctor_ip_transaction.booking_id = lab_booking.id AND doctor_ip_transaction.is_active = '1'
		
	WHERE 
		patient_service_charges.due_amount <= 0 
		AND lab_booking.is_ip_generated = IF(is_generated = '1', '1', '0') 
		AND lab_booking.created_at BETWEEN @start_date 
		AND @end_date 
		AND (lab_booking.referred_doc_id = doctor_id OR doctor_id = '')
		AND doctors.ip_applicable = 'Y'
		AND lab_booking.collection_type IN ('LAB','HMC');
		
	SELECT 
		tmip.labdetails,
		GROUP_CONCAT(DISTINCT tmip.booking_date SEPARATOR ", ") booking_date,
		GROUP_CONCAT(DISTINCT tmip.case_id SEPARATOR ", ") case_id,
		GROUP_CONCAT(DISTINCT tmip.patient_name SEPARATOR ", ") patient_name,
		tmip.docid,
		tmip.doctor_name,
		tmip.ip_pay_mode,
		(GROUP_CONCAT(DISTINCT tmip.analyte_name SEPARATOR ", ")) analyte_name, 
		tmip.ip_remarks, 
		tmip.paid_amount,
		tmip.barcode_no,
		SUM(tmip.net_amount) net_amount,
		SUM(tmip.ip_charge) ip_charge
		FROM 
			`tmp_ip_result_listing` tmip
		GROUP BY 
		CASE 
			WHEN report_type = 'DOCTOR' THEN tmip.docid
			WHEN report_type = 'TEST' THEN tmip.analyte_name
			ELSE tmip.case_id
		END;
	
 		TRUNCATE TABLE `tmp_ip_result_listing`;
 		DROP TEMPORARY TABLE IF EXISTS tmp_ip_result_listing;
END