ALTER TABLE `lab_requisition` ADD `requested_by` VARCHAR(50) NULL AFTER `approval_status`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `doctorLabRequest`(IN `in_department` VARCHAR(50), IN `in_test` VARCHAR(100), IN `in_percentage` INT(5), IN `in_price` INT(11), IN `in_code` VARCHAR(50), IN `in_noOfLabels` INT(7), IN `in_subhead` VARCHAR(30), IN `in_account` VARCHAR(11), IN `in_collect_sample` VARCHAR(11), IN `in_to_be_analyzed` VARCHAR(50), IN `in_to_be_reported` VARCHAR(50), IN `in_print_type` VARCHAR(30), IN `in_upload_doc` VARCHAR(50), 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(11), IN `query_type` VARCHAR(20))
    NO SQL
BEGIN
IF query_type= 'insert' THEN
INSERT INTO lab_requisition (department,test,percentage,price,
specimen,code,noOfLabels,subhead,account,collect_sample,to_be_analyzed,to_be_reported,
print_type,upload_doc,label_type,patient_id,requested_by,facilityId,status) 
VALUES (in_department,in_test,in_percentage,in_price,in_code,in_noOfLabels,in_subhead,in_account,in_collect_sample,in_to_be_analyzed,in_to_be_reported,
in_print_type,in_upload_doc,in_label_type,in_patient_id,in_requested_by,in_facilityId,in_status);
ELSEIF query_type='fetch' THEN 
SELECT * FROM lab_requisition WHERE patient_id=in_patient_id;
END IF;
END$$
DELIMITER ;

-- 12 of october 2021

CREATE TABLE `previous_doc` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `patient_id` varchar(20) DEFAULT NULL,
 `file_type` varchar(50) DEFAULT NULL,
 `file_url` varchar(200) DEFAULT NULL,
 `file_date` date DEFAULT NULL,
 `created_at` datetime NOT NULL DEFAULT current_timestamp(),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `previous_doc`(IN `in_patient_id` VARCHAR(20), IN `in_file_type` VARCHAR(50), IN `in_file_url` VARCHAR(200), IN `in_file_date` DATE, IN `query_type` VARCHAR(50))
    NO SQL
BEGIN 
IF query_type='insert' THEN
INSERT INTO previous_doc (patient_id,file_type,file_url,file_date) VALUES(in_patient_id,in_file_type,in_file_url,in_file_date);
END IF;
END$$
DELIMITER ;