DROP PROCEDURE IF EXISTS `doctorLabRequest`;

DELIMITER $$
CREATE PROCEDURE `doctorLabRequest`(IN `in_department` VARCHAR(50), 
IN `in_test` VARCHAR(100), IN `in_percentage` VARCHAR(5), IN `in_price` VARCHAR(11), 
IN `in_code` VARCHAR(50), IN `in_noOfLabels` VARCHAR(7), IN `in_test_group` VARCHAR(30), 
IN `in_print_type` VARCHAR(30), IN `in_label_type` VARCHAR(30), IN `in_patient_id` VARCHAR(50), 
IN `in_requested_by` VARCHAR(50), IN `in_facilityId` VARCHAR(50), IN `in_status` VARCHAR(50), 
IN `query_type` VARCHAR(20), IN `in_req_id` VARCHAR(50), IN `in_description` VARCHAR(100), 
IN `in_patient_status` VARCHAR(20), IN `in_from` VARCHAR(20), IN `in_to` VARCHAR(20), 
IN `in_created_by` VARCHAR(50), IN `in_receiptDateSN` VARCHAR(50), IN `in_payment_status` VARCHAR(20), 
IN `in_old_price` VARCHAR(10), IN `in_payables_head` VARCHAR(10), IN `in_recievables_head` VARCHAR(10), 
IN `in_account` VARCHAR(10), IN `in_account_name` VARCHAR(50), IN `in_patient_name` VARCHAR(100), 
IN `in_department_code` VARCHAR(10), IN `in_unit_code` VARCHAR(10), IN `in_unit_name` VARCHAR(50), 
IN `in_unit` VARCHAR(10), IN `in_range_from` VARCHAR(10), IN `in_range_to` VARCHAR(10), 
IN `in_client_type` VARCHAR(20), IN `in_client_account` VARCHAR(10), IN `in_discount` VARCHAR(10), 
IN `in_discount_head` VARCHAR(10), IN `in_discount_head_name` VARCHAR(50), IN `in_approval_status` VARCHAR(20), 
IN `in_discount_amount` VARCHAR(10), IN `in_request_id` VARCHAR(20))
NO SQL
BEGIN
IF query_type= 'insert' THEN

INSERT INTO lab_requisition(test,patient_id,facilityId,price,percentage,
    department,test_group,status,created_by,receiptNo,payment_status,label_type,noOfLabels,
    print_type,payable_head,receivable_head,account,account_name,
    patient_name,department_code,unit_code,unit_name,unit,range_from,range_to,client_type,
    client_account,discount,discount_head,discount_head_name,approval_status,discount_amount,request_id, patient_status, requested_by,description) 
VALUES (in_test,in_patient_id,in_facilityId,in_price,in_percentage,in_department,in_test_group,
    in_status,in_created_by,in_receiptDateSN,in_payment_status,in_label_type,in_noOfLabels,
    in_print_type,in_payables_head,in_recievables_head,in_account,in_account_name,
    in_patient_name,in_department_code,in_unit_code,in_unit_name,in_unit,in_range_from, in_range_to,
    in_client_type,in_client_account,in_discount,in_discount_head,in_discount_head_name,in_approval_status,
    in_discount_amount,in_req_id,in_patient_status,in_requested_by,in_description);
COMMIT;
IF in_price > 0 THEN
    INSERT INTO pending_txn (transaction_id, description, head, subhead, amount, service_type, created_at, 
    patient_name, patient_id, total_amount,client_acc, tx_status, patient_type) VALUES (in_req_id, in_description, '',
    in_account, in_price, 'LAB', now(), in_patient_name, in_patient_id, 0, in_client_account, 'pending', in_client_type);
END IF;

ELSEIF query_type='ordered_list' THEN 
SELECT patient_name as name, created_at, patient_id,'' as DOB,request_id, patient_status FROM lab_requisition 
WHERE status='ordered' AND date(created_at) BETWEEN date(in_from) AND date(in_to) 
GROUP BY patient_id, created_at,patient_id,request_id,patient_status, patient_name;

ELSEIF query_type = 'lab_processed' THEN
UPDATE lab_requisition SET status = 'Sample Collected' WHERE request_id = in_req_id;
call createbooking_list(in_req_id);
call  createlabcode_list(in_req_id);
END IF;
END$$

ALTER TABLE `prime`.`lab_requisition` 
CHANGE COLUMN `id` `id` INT NOT NULL AUTO_INCREMENT FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `facilityId`);

DELIMITER $$
CREATE PROCEDURE 
$$