/*
Employee ID     |Version          |Date Range         |CR ID                     |Propose
5               initial            21.01.22            5-220121                   For Signup Charge Details Fetching
*/

DROP PROCEDURE IF EXISTS `sp_signup_charge_details_report`;
CREATE PROCEDURE `sp_signup_charge_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_signup_charge_result_listing`;
	CREATE TEMPORARY TABLE `tmp_signup_charge_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 '',
		`sign_pay_mode` VARCHAR(50) DEFAULT NULL,
		`analyte_name` VARCHAR(50) NOT NULL DEFAULT '',
		`analyte_id` INT DEFAULT '0',
		`sign_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',
		`sign_charge` DECIMAL(10,2) NULL DEFAULT '0'
	);
	
	INSERT INTO `tmp_signup_charge_result_listing` (
		labdetails,
		booking_date,
		case_id,
		patient_name,
		docid,
		doctor_name,
		sign_pay_mode,
		analyte_name,
		analyte_id,
		sign_remarks,
		paid_amount,
		barcode_no,
		net_amount,
		sign_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, 
		doctor_signup_transaction.payment_mode,
		lab_analyte_master.analyte_name, 
		lab_booking_details.lab_analyte_id, 
		doctor_signup_transaction.remarks as sign_remarks,
		doctor_signup_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_signup_charge_details(
				lab_analyte_master.id, 
				COALESCE(lab_report_result.radio_signing_doctor_id, 0), 
				(patient_service_charge_details.net_amount 
					- 
				(COALESCE(patient_service_charges.discount_percentage,0)/100)*patient_service_charge_details.net_amount)
		) sign_charge
	FROM 
		lab_booking 
		JOIN 
			lab_booking_details ON lab_booking_details.lab_booking = lab_booking.id
			
		JOIN 
			lab_report_result ON lab_report_result.lab_details_id = lab_booking_details.id 
			AND 
			lab_report_result.is_active = '1'
			
		JOIN 
			doctors ON lab_report_result.radio_signing_doctor_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 lab_analyte_master.charge_id = service_rates.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_signup_transaction ON doctor_signup_transaction.doctor_id = lab_report_result.radio_signing_doctor_id 
			AND 
			doctor_signup_transaction.booking_id = lab_booking_details.lab_booking 
			AND 
			doctor_signup_transaction.test_id = lab_booking_details.lab_analyte_id
	WHERE 
		patient_service_charges.due_amount <= 0 
		AND lab_booking.is_signup_generated = IF(is_generated = '1', '1', '0') 
		AND lab_report_result.created_at BETWEEN @start_date 
		AND @end_date 
		AND (lab_report_result.radio_signing_doctor_id = doctor_id OR doctor_id = '')
		AND doctors.doc_type LIKE '%RADIO%'
		AND lab_booking.collection_type IN ('LAB','HMC');
		
	SELECT 
		tmsign.labdetails,
		GROUP_CONCAT(DISTINCT tmsign.booking_date SEPARATOR ", ") booking_date,
		GROUP_CONCAT(DISTINCT tmsign.case_id SEPARATOR ", ") case_id,
		GROUP_CONCAT(DISTINCT tmsign.patient_name SEPARATOR ", ") patient_name,
		tmsign.docid,
		tmsign.doctor_name,
		tmsign.sign_pay_mode,
		(GROUP_CONCAT(DISTINCT tmsign.analyte_name SEPARATOR ", ")) analyte_name, 
		tmsign.analyte_id, 
		tmsign.sign_remarks, 
		tmsign.paid_amount,
		tmsign.barcode_no,
		SUM(tmsign.net_amount) net_amount,
		SUM(tmsign.sign_charge) sign_charge
		FROM 
			`tmp_signup_charge_result_listing` tmsign
		GROUP BY 
			tmsign.docid,
			CASE 
				WHEN report_type = 'TEST' THEN tmsign.analyte_name
				ELSE tmsign.case_id
			END;
	
 		TRUNCATE TABLE `tmp_signup_charge_result_listing`;
 		DROP TEMPORARY TABLE IF EXISTS tmp_signup_charge_result_listing;
END