update customer_deposit, lab_summary PROCEDURE


-- QUERY
ALTER TABLE `transactions`  ADD `acct_name` VARCHAR(300) NOT NULL  AFTER `acct`;
ALTER TABLE `account_entries` ADD `approval_status` VARCHAR(50) NOT NULL DEFAULT 'pending' AFTER `txn_status`;
ALTER TABLE `lab_requisition` ADD `approval_status` VARCHAR(50) NOT NULL DEFAULT 'pending' AFTER `payment_status`;

-- VIEW
ALTER VIEW `lab_summary` AS SELECT CONCAT(`b`.`firstname`, ' ', `b`.`surname`) AS `fullname`, 
`a`.`acct` AS `acct`, SUM(`a`.`dr`) AS `paid`, SUM(`a`.`cr`) AS `total`, SUM(`a`.`cr`) - SUM(`a`.`dr`) AS `balance`, 
`a`.`description` AS `description`, `a`.`reference_no` AS `reference_no`, `a`.`createdAt` AS `createdAt`, 
`a`.`txn_status` AS `txn_status`, `a`.`client_id` AS `client_id`, a.approval_status 
    FROM ( `account_entries` `a` 
        JOIN `patientrecords` `b` ON ( `a`.`client_id` = `b`.`id` AND `a`.`facilityId` = `b`.`facilityId` ) ) 
        GROUP BY CONCAT(`b`.`firstname`, ' ', `b`.`surname`), `a`.`reference_no`, CAST(`a`.`createdAt` AS DATE) 
        ORDER BY `a`.`createdAt` DESC;


ALTER
 VIEW `lab_info`
 AS SELECT
    `b`.`sort_index` AS `sn`,
    `a`.`created_at` AS `created_at`,
    `a`.`facilityId` AS `facilityId`,
    `a`.`id` AS `id`,
    `a`.`code` AS `code`,
    `a`.`booking_no` AS `booking_no`,
    `a`.`request_id` AS `request_id`,
    `a`.`patient_id` AS `patient_id`,
    `b`.`qms_dept_id` AS `qms_dept_id`,
    `b`.`head` AS `head`,
    `b`.`subhead` AS `subhead`,
    `b`.`unit` AS `unit`,
    `b`.`range_from` AS `range_from`,
    `b`.`range_to` AS `range_to`,
    `b`.`price` AS `price`,
    `b`.`old_price` AS `old_price`,
    `b`.`commission_type` AS `commission_type`,
    `b`.`percentage` AS `percentage`,
    `b`.`description` AS `description`,
    `b`.`specimen` AS `specimen`,
    `a`.`noOfLabels` AS `noOfLabels`,
    `a`.`label_type` AS `label_type`,
    `b`.`label_name` AS `label_name`,
    `a`.`department` AS `department`,
    `a`.`result` AS `result`,
    `a`.`h_value` AS `h_value`,
    `a`.`o_value` AS `o_value`,
    `a`.`appearance` AS `appearance`,
    `a`.`serology` AS `serology`,
    `a`.`culture_yielded` AS `culture_yielded`,
    `a`.`sensitivity` AS `sensitivity`,
    `a`.`resistivity` AS `resistivity`,
    `a`.`intermediaryTo` AS `intermediaryTo`,
    `b`.`lab_code` AS `lab_code`,
    `b`.`selectable` AS `selectable`,
    `a`.`status` AS `status`,
    `a`.`print_type` AS `print_type`,
    `b`.`report_type` AS `report_type`,
    `b`.`collect_sample` AS `collect_sample`,
    `b`.`to_be_reported` AS `to_be_reported`,
    `b`.`to_be_analyzed` AS `to_be_analyzed`,
    `a`.`created_by` AS `created_by`,
    `a`.`sample_collected_by` AS `sample_collected_by`,
    `a`.`sample_collected_at` AS `sample_collected_at`,
    `a`.`analyzed_by` AS `analyzed_by`,
    `a`.`analyzed_at` AS `analyzed_at`,
    `a`.`result_by` AS `result_by`,
    `a`.`result_at` AS `result_at`,
    `a`.`reviewed_by` AS `reviewed_by`,
    `a`.`reviewed_at` AS `reviewed_at`,
    `a`.`printed_by` AS `printed_by`,
    `a`.`printed_at` AS `printed_at`,
    `a`.`unit` AS `n_unit`,
    `a`.`range_from` AS `n_range_from`,
    `a`.`range_to` AS `n_range_to`,
    `a`.`receiptNo` AS `receiptNo`,
    `a`.`sop_instance_id` AS `sop_instance_id`,
    `a`.`uploaded_at` AS `uploaded_at`,
    `a`.`uploaded_by` AS `uploaded_by`,
    a.payment_status, a.approval_status
FROM
    (
        `optimum_160921-new`.`lab_setup` `b`
    JOIN `optimum_160921-new`.`lab_requisition` `a`
    ON
        (`b`.`subhead` = `a`.`test`)
    )
WHERE
    `a`.`facilityId` = `b`.`facilityId`;


ALTER
 VIEW `lab_request2`
 AS SELECT
    `a`.`sn` AS `sn`,
    `a`.`created_at` AS `created_at`,
    `a`.`facilityId` AS `facilityId`,
    `a`.`id` AS `id`,
    `a`.`qms_dept_id` AS `qms_dept_id`,
    `a`.`code` AS `code`,
    `a`.`booking_no` AS `booking_no`,
    `a`.`request_id` AS `request_id`,
    `a`.`patient_id` AS `patient_id`,
    `a`.`head` AS `head`,
    `a`.`subhead` AS `subhead`,
    `a`.`unit` AS `unit`,
    `a`.`range_from` AS `range_from`,
    `a`.`range_to` AS `range_to`,
    `a`.`price` AS `price`,
    `a`.`old_price` AS `old_price`,
    `a`.`commission_type` AS `commission_type`,
    `a`.`percentage` AS `percentage`,
    `a`.`description` AS `description`,
    `a`.`specimen` AS `specimen`,
    `a`.`noOfLabels` AS `noOfLabels`,
    `a`.`label_type` AS `label_type`,
    `a`.`label_name` AS `label_name`,
    `b`.`description` AS `group_head`,
    `a`.`department` AS `department`,
    `a`.`result` AS `result`,
    `a`.`h_value` AS `h_value`,
    `a`.`o_value` AS `o_value`,
    `a`.`appearance` AS `appearance`,
    `a`.`serology` AS `serology`,
    `a`.`culture_yielded` AS `culture_yielded`,
    `a`.`sensitivity` AS `sensitivity`,
    `a`.`resistivity` AS `resistivity`,
    `a`.`intermediaryTo` AS `intermediaryTo`,
    `a`.`lab_code` AS `lab_code`,
    `a`.`selectable` AS `selectable`,
    `a`.`status` AS `status`,
    `a`.`print_type` AS `print_type`,
    `a`.`report_type` AS `report_type`,
    `b`.`collect_sample` AS `collect_sample`,
    `b`.`to_be_reported` AS `to_be_reported`,
    `b`.`to_be_analyzed` AS `to_be_analyzed`,
    `a`.`created_by` AS `created_by`,
    `a`.`sample_collected_by` AS `sample_collected_by`,
    `a`.`sample_collected_at` AS `sample_collected_at`,
    `a`.`analyzed_by` AS `analyzed_by`,
    `a`.`analyzed_at` AS `analyzed_at`,
    `a`.`result_by` AS `result_by`,
    `a`.`result_at` AS `result_at`,
    `a`.`reviewed_by` AS `reviewed_by`,
    `a`.`reviewed_at` AS `reviewed_at`,
    `a`.`printed_by` AS `printed_by`,
    `a`.`printed_at` AS `printed_at`,
    `a`.`n_unit` AS `n_unit`,
    `a`.`n_range_from` AS `n_range_from`,
    `a`.`n_range_to` AS `n_range_to`,
    `a`.`receiptNo` AS `receiptNo`,
    `a`.`sop_instance_id` AS `sop_instance_id`,
    `a`.`uploaded_at` AS `uploaded_at`,
    `a`.`uploaded_by` AS `uploaded_by`,
    a.payment_status, a.approval_status
FROM
    (
        `optimum_160921-new`.`lab_info` `a`
    JOIN `optimum_160921-new`.`lab_setup` `b`
    ON
        (`b`.`subhead` = `a`.`head`)
    )
WHERE
    `a`.`facilityId` = `b`.`facilityId`;



ALTER
 VIEW `lab_process`
 AS SELECT
    `a`.`sn` AS `sn`,
    `a`.`created_at` AS `created_at`,
    `a`.`facilityId` AS `facilityId`,
    `a`.`id` AS `id`,
    `a`.`qms_dept_id` AS `qms_dept_id`,
    `a`.`code` AS `code`,
    `a`.`booking_no` AS `booking_no`,
    `a`.`request_id` AS `request_id`,
    `a`.`patient_id` AS `patient_id`,
    CONCAT(`c`.`firstname`, ' ', `c`.`surname`) AS `name`,
    `c`.`DOB` AS `DOB`,
    `c`.`Gender` AS `Gender`,
    `a`.`head` AS `head`,
    `a`.`subhead` AS `subhead`,
    `a`.`unit` AS `unit`,
    `a`.`range_from` AS `range_from`,
    `a`.`range_to` AS `range_to`,
    `a`.`price` AS `price`,
    `a`.`old_price` AS `old_price`,
    `a`.`commission_type` AS `commission_type`,
    `a`.`percentage` AS `percentage`,
    `a`.`description` AS `description`,
    `a`.`specimen` AS `specimen`,
    `a`.`noOfLabels` AS `noOfLabels`,
    `a`.`label_type` AS `label_type`,
    `a`.`label_name` AS `label_name`,
    `a`.`group_head` AS `group_head`,
    `a`.`department` AS `department`,
    `b`.`description` AS `department_head`,
    `a`.`result` AS `result`,
    `a`.`h_value` AS `h_value`,
    `a`.`o_value` AS `o_value`,
    `a`.`appearance` AS `appearance`,
    `a`.`serology` AS `serology`,
    `a`.`culture_yielded` AS `culture_yielded`,
    `a`.`sensitivity` AS `sensitivity`,
    `a`.`resistivity` AS `resistivity`,
    `a`.`intermediaryTo` AS `intermediaryTo`,
    `a`.`lab_code` AS `lab_code`,
    `a`.`selectable` AS `selectable`,
    `a`.`status` AS `status`,
    `a`.`print_type` AS `print_type`,
    `a`.`report_type` AS `report_type`,
    `a`.`collect_sample` AS `collect_sample`,
    `a`.`to_be_reported` AS `to_be_reported`,
    `a`.`to_be_analyzed` AS `to_be_analyzed`,
    `a`.`created_by` AS `created_by`,
    `a`.`sample_collected_by` AS `sample_collected_by`,
    `a`.`sample_collected_at` AS `sample_collected_at`,
    `a`.`analyzed_by` AS `analyzed_by`,
    `a`.`analyzed_at` AS `analyzed_at`,
    `a`.`result_by` AS `result_by`,
    `a`.`result_at` AS `result_at`,
    `a`.`reviewed_by` AS `reviewed_by`,
    `a`.`reviewed_at` AS `reviewed_at`,
    `a`.`printed_by` AS `printed_by`,
    `a`.`printed_at` AS `printed_at`,
    `a`.`n_unit` AS `n_unit`,
    `a`.`n_range_from` AS `n_range_from`,
    `a`.`n_range_to` AS `n_range_to`,
    `a`.`receiptNo` AS `receiptNo`,
    `a`.`sop_instance_id` AS `sop_instance_id`,
    `a`.`uploaded_at` AS `uploaded_at`,
    `a`.`uploaded_by` AS `uploaded_by`,
    a.payment_status, a.approval_status
FROM
    (
        (
            `optimum_160921-new`.`lab_request2` `a`
        JOIN `optimum_160921-new`.`lab_setup` `b`
        )
    JOIN `optimum_160921-new`.`patientrecords` `c`
    )
WHERE
    `a`.`department` = `b`.`subhead` AND `a`.`patient_id` = `c`.`id` AND `a`.`facilityId` = `b`.`facilityId`;




DELIMITER $$
CREATE PROCEDURE `customer_deposit`(IN `in_acct` INT, IN `amount_paid` INT, IN `userId` VARCHAR(50), IN `in_receiptDateSN` VARCHAR(50), IN `in_receiptSN` VARCHAR(50), IN `in_description` VARCHAR(1000), IN `in_mode_of_payment` VARCHAR(20), IN `in_facId` VARCHAR(50), IN `sourceAcct` VARCHAR(50), IN `acc_name` VARCHAR(100), IN `acc_type` VARCHAR(20), IN `in_date` DATETIME, IN `in_address` VARCHAR(100), IN `in_phone` VARCHAR(20), IN `in_email` VARCHAR(50), IN `in_website` VARCHAR(50), IN `in_payables_head` VARCHAR(50), IN `in_recievables_head` VARCHAR(50), IN `in_guarantor_name` VARCHAR(50), IN `in_guarantor_address` VARCHAR(100), IN `in_guarantor_phone` VARCHAR(20), IN `in_bank_name` VARCHAR(50), IN `in_pid` VARCHAR(50), IN `in_txn_status` VARCHAR(50))
BEGIN
	declare client_balance double;
	declare main_balance double;
    #DECLARE remaining_balance double;
	select balance into client_balance from patientfileno where accountNo=in_acct AND facilityId=in_facId;

	#SET remaining_balance = Amount_paid - client_balance;
	set main_balance = client_balance+Amount_paid;

	-- # IF customer is a NEW client,
    -- # We create a new account for the customer and deposit the amount
	if client_balance is null and Amount_paid < 0 then
  
        	insert into patientfileno (accountNo,accName,balance,facilityId,status,accountType,contactAddress,contactPhone,contactEmail,contactWebsite,guarantor_name,
               guarantor_address,guarantor_phone)
		values(in_acct,acc_name,Amount_paid,in_facId,'approved',acc_type,in_address,in_phone,in_email,in_website,in_guarantor_name,
               in_guarantor_address,in_guarantor_phone);

       insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt,client_id,txn_status)
		values (in_acct,abs(Amount_paid),0,in_receiptDateSN,in_description,in_facId,in_date,in_pid,in_txn_status);
        
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,
                                  patient_id,facilityId,createdAt,bank_name,transaction_date)
		values (in_description,sourceAcct,abs(Amount_paid),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name, date(in_date));

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,
                                  patient_id,facilityId,createdAt,bank_name,transaction_date)
		values (in_description,in_recievables_head,0,abs(Amount_paid) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name,date(in_date));
            
            
            
            
            
            ELSEif client_balance is null then
    
		insert into patientfileno (accountNo,accName,balance,facilityId,status,accountType,contactAddress,contactPhone,contactEmail,contactWebsite,guarantor_name,
               guarantor_address,guarantor_phone)
		values(in_acct,acc_name,Amount_paid,in_facId,'approved',acc_type,in_address,in_phone,in_email,in_website,in_guarantor_name,
               in_guarantor_address,in_guarantor_phone);

       insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt,client_id,txn_status)
		values (in_acct,abs(Amount_paid),0,in_receiptDateSN,in_description,in_facId,in_date,in_pid,in_txn_status);
        
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
		values (in_description,sourceAcct,abs(Amount_paid),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name,date(in_date));

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
		values (in_description,in_payables_head,0,abs(Amount_paid) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name,date(in_date));

	end if;
    
    -- # IF customer has an account, but the deposit amount nullifies his account balance
    -- # i.e. he's paying up his debt
	if  main_balance = 0 then
 
		update patientfileno set balance= main_balance  where accountNo=in_acct AND facilityId=in_facId;

		insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt,client_id,txn_status)
		values (in_acct,abs(Amount_paid),0,in_receiptDateSN,in_description,in_facId,in_date,in_pid,in_txn_status);
		 
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment, enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
		values (in_description,sourceAcct,abs(Amount_paid),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name,date(in_date));

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment, enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
		values (in_description,in_recievables_head,0,abs(client_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name,date(in_date));

	-- # IF customer has an account, and his new account balance after the deposit is a top up
    -- # to his initial account balance
	elseif main_balance  > 0  then
 
		update patientfileno set balance= main_balance where accountNo=in_acct AND facilityId=in_facId;

		insert into account_entries (acct,dr,cr, reference_no, description, facilityId, createdAt,client_id,txn_status)
		values (in_acct,Amount_paid,0,in_receiptDateSN, in_description,in_facId, in_date,in_pid,in_txn_status);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
		values (in_description,sourceAcct,abs(Amount_paid),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name,date(in_date));

		-- # IF customer's was owing us some money, then
		if  client_balance < 0 then

			insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                      client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
			values (in_description,in_recievables_head,0,abs(client_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                    userId,in_acct,in_acct,in_facId,in_date,in_bank_name,date(in_date));

			insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                      client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
			values (in_description,in_payables_head,0,abs(main_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                    userId,in_acct,in_acct,in_facId,in_date,in_bank_name,date(in_date));
            
		-- # ELSE IF customer had some money in his/her account
		elseif client_balance >= 0 then
			insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                      client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
			values (in_description,in_payables_head,0,abs(Amount_paid) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                    userId,in_acct,in_acct,in_facId,in_date,in_bank_name,date(in_date));
		end if;

	-- # IF the customer was owing some money and the deposit amount is not upto the amount owed
	elseif main_balance  < 0 then
		update patientfileno set balance= main_balance where accountNo=in_acct AND facilityId=in_facId;

		insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt,client_id,txn_status)
		values (in_acct,abs(Amount_paid),0,in_receiptDateSN,in_description,in_facId,in_date,in_pid,in_txn_status);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,
                                  modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
		values (in_description,sourceAcct,abs(Amount_paid),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name,date(in_date));
        
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,
                                  enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
		values (in_description,in_recievables_head,0,abs(Amount_paid) ,in_receiptDateSN,
                in_receiptSN,in_mode_of_payment,userId,in_acct,in_acct,in_facId,in_date,in_bank_name,date(in_date));
        
        
	end if;

END$$


DELIMITER $$
CREATE PROCEDURE `lab_summary`(IN `in_query_type` VARCHAR(50), IN `in_facId` VARCHAR(50), IN `in_fromdate` DATE, IN `in_todate` DATE, IN `in_report_by` VARCHAR(50))
    NO SQL
BEGIN
	IF in_query_type = 'patient income' THEN
    	if in_report_by = 'all' THEN
        	SELECT * FROM lab_summary WHERE txn_status='completed' AND total>0 AND date(createdAt) between in_fromdate AND in_todate ORDER BY createdAt DESC;
        ELSE
        	SELECT concat(b.firstname,' ',b.surname) as fullname, sum(debit) as debit, sum(credit) as credit, sum(debit - credit) as balance, a.createdAt FROM transactions a JOIN patientrecords b ON a.patient_id=b.id AND a.facilityId=b.facilityId WHERE a.facilityId=in_facId AND a.enteredBy=in_report_by AND date(a.createdAt) between in_fromdate AND in_todate GROUP BY fullname, date(a.createdAt) ORDER BY createdAt DESC;
        END IF;
        
    ELSEIF in_query_type = 'daily total' THEN
    	IF in_report_by = 'all' THEN
    		SELECT date(createdAt) as createdAt, Acct_source, sum(debit) as amount, acct FROM trial_balance where facilityId=in_facId AND acct in (400021,400022,400023,400024,400025) AND date(createdAt) between in_fromdate AND in_todate GROUP BY Acct_source, acct, date(createdAt) ORDER BY createdAt desc;
        ELSE 
        	SELECT date(createdAt) as createdAt, Acct_source, sum(debit) as amount, acct FROM trial_balance where facilityId=in_facId AND enteredBy=in_report_by AND acct in (400021,400022,400023,400024,400025) AND date(createdAt) between in_fromdate AND in_todate GROUP BY Acct_source, acct, date(createdAt) ORDER BY createdAt desc;
        END IF;
        
    ELSEIF in_query_type = 'pending income' THEN
    	SELECT * FROM `lab_summary` WHERE txn_status='pending';
    ELSEIF in_query_type = 'detail_by_receipt' THEN
    	SELECT acct, a.description, sum(dr) as paid, sum(cr) as total, sum(cr)-sum(dr) as balance, b.account FROM account_entries a JOIN lab_setup b ON a.description = b.description WHERE a.reference_no=in_report_by GROUP BY a.description;
    ELSEIF in_query_type = 'pending approval' THEN
    	SELECT * FROM `lab_summary` WHERE total > 0 AND approval_status = 'pending';
    END IF;
END$$
DELIMITER ;


DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_lab_setup_account`(IN `in_department` VARCHAR(100), IN `in_unit` VARCHAR(100), IN `in_group` VARCHAR(100), IN `in_facilityId` VARCHAR(100))
    NO SQL
if in_department != "" THEN

SELECT * from lab_setup WHERE lab_code = in_department and facilityId=in_facilityId;

ELSEIF in_unit !="" THEN 

SELECT * from lab_setup WHERE unit=in_unit and facilityId=in_facilityId;

ELSEIF in_group != "" THEN 

SELECT * from lab_setup WHERE head=in_group and facilityId=in_facilityId;

end if$$
DELIMITER ;