DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `query_lab_setup`(IN `in_head` INT, IN `facId` VARCHAR(50), IN `in_query_type` VARCHAR(50), IN `in_subhead` INT, IN `in_description` VARCHAR(100), IN `in_label_name` VARCHAR(50), IN `in_no_of_labels` INT, IN `in_unit` VARCHAR(10), IN `in_range_from` VARCHAR(10), IN `in_range_to` VARCHAR(10), IN `in_report_type` VARCHAR(50), IN `in_form_mode` VARCHAR(50), IN `in_unit_name` VARCHAR(50), IN `in_specimen` VARCHAR(50), IN `in_to_collect_sample` VARCHAR(5), IN `in_to_be_analyzed` VARCHAR(5), IN `in_to_be_reported` VARCHAR(5), IN `in_upload_doc` VARCHAR(5), IN `in_label_type` VARCHAR(50), IN `in_print_type` VARCHAR(50))
    NO SQL
IF in_query_type = 'next subhead' THEN
	SELECT ifnull(MAX(subhead)+ 1, concat(in_head,'1'))  as next_code FROM lab_setup WHERE head=in_head AND facilityId=facId;
ELSEIF in_query_type = 'report_type' THEN
	SELECT DISTINCT report_type from lab_setup WHERE report_type is not null;
ELSEIF in_query_type = 'new_test' THEN
	INSERT INTO lab_setup (head,subhead,description, label_name, noOfLabels, unit, range_from, range_to, report_type,facilityId, specimen, collect_sample, to_be_analyzed, to_be_reported, upload_doc) VALUES (in_head,in_subhead,in_description, in_label_name, in_no_of_labels, in_unit, in_range_from, in_range_to, in_report_type, facId,in_specimen, in_to_collect_sample, in_to_be_analyzed, in_to_be_reported, in_upload_doc);
ELSEIF in_query_type = 'update_test' THEN
UPDATE lab_setup SET head=in_head, description=in_description, label_name=in_label_name, noOfLabels=in_no_of_labels, unit=in_unit, range_from=in_range_from, range_to=in_range_to, report_type=in_report_type, specimen=in_specimen, collect_sample=in_to_collect_sample, to_be_analyzed=in_to_be_analyzed, to_be_reported=in_to_be_reported, upload_doc=in_upload_doc WHERE subhead=in_subhead AND facilityId=facId;
ELSEIF in_query_type = 'delete' THEN 
	DELETE FROM lab_setup WHERE subhead = in_subhead AND facilityId=facId;
ELSEIF in_query_type = 'code_setup' THEN
	UPDATE lab_setup SET label_type=in_label_type, print_type=in_print_type WHERE subhead=in_subhead AND facilityId=facId;
ELSEIF in_query_type = 'test_info' THEN
	SELECT * FROM lab_setup WHERE subhead=in_subhead;
ELSEIF in_query_type = 'group_list' THEN
SELECT * FROM lab_setup WHERE subhead IN (SELECT DISTINCT head FROM lab_setup);
ELSEIF in_query_type = 'unit_list' THEN
	IF in_head = 'all' THEN
		SELECT DISTINCT unit_name,unit_code FROM lab_setup;
    ELSE 
    	SELECT DISTINCT unit_name,unit_code FROM lab_setup WHERE lab_code = in_head;
    END IF;
ELSEIF in_query_type = 'department_list' THEN
	SELECT * FROM lab_setup WHERE head=1000;
ELSEIF in_query_type = 'Barcode Setup' THEN
	IF in_form_mode = 'Stand Alone Test' THEN 
    	SELECT * FROM lab_setup WHERE label_type='single';
    ELSE 
    	SELECT * FROM lab_setup WHERE label_type IN ('grouped', 'singular_group', 'grouped_single');
    END IF;
   	ELSEIF in_query_type = 'by_group' THEN
    call get_lab_setup_account('','', in_subhead, facId);
END IF$$
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 ;

--latest script
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `save_lab_account_head`(IN `in_department` VARCHAR(50), IN `in_account_type` VARCHAR(50), IN `in_account_head` VARCHAR(50), IN `in_facilityId` VARCHAR(50))
    NO SQL
INSERT INTO `save_lab_account_head`(`id`, `department`, `account_type`, `account_head`,`facilityId`) VALUES (in_department,in_account_type,in_account_head,in_facilityId)$$
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 != "" and in_unit!="" and in_group!="" THEN

SELECT * from lab_setup WHERE head=in_group and unit=in_unit and lab_code=in_department and facilityId=in_facilityId;

ELSEIF in_department != "" and in_unit !="" THEN

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

ELSEIF in_department != "" and in_group !="" THEN

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


ELSEIF in_unit!="" and in_group!="" THEN

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

ELSEIF 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 ;



CREATE TABLE `save_lab_account_head` (
  `id` int(50) NOT NULL,
  `department` varchar(50) NOT NULL,
  `account_type` varchar(50) NOT NULL,
  `account_head` varchar(50) NOT NULL,
  `facilityId` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `save_lab_account_head`
--
ALTER TABLE `save_lab_account_head`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `save_lab_account_head`
--
ALTER TABLE `save_lab_account_head`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT;
COMMIT;


DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `query_lab_setup`(IN `in_head` INT, IN `facId` VARCHAR(50), IN `in_query_type` VARCHAR(50), IN `in_subhead` INT, IN `in_description` VARCHAR(100), IN `in_label_name` VARCHAR(50), IN `in_no_of_labels` INT, IN `in_unit` VARCHAR(10), IN `in_range_from` VARCHAR(10), IN `in_range_to` VARCHAR(10), IN `in_report_type` VARCHAR(50), IN `in_form_mode` VARCHAR(50), IN `in_unit_name` VARCHAR(50), IN `in_specimen` VARCHAR(50), IN `in_to_collect_sample` VARCHAR(5), IN `in_to_be_analyzed` VARCHAR(5), IN `in_to_be_reported` VARCHAR(5), IN `in_upload_doc` VARCHAR(5), IN `in_label_type` VARCHAR(50), IN `in_print_type` VARCHAR(50), IN `in_price` INT(30))
    NO SQL
IF in_query_type = 'next subhead' THEN
	SELECT ifnull(MAX(subhead)+ 1, concat(in_head,'1'))  as next_code FROM lab_setup WHERE head=in_head AND facilityId=facId;
ELSEIF in_query_type = 'report_type' THEN
	SELECT DISTINCT report_type from lab_setup WHERE report_type is not null;
    ELSEIF in_query_type ="update_lab_setup_account" THEN
update lab_setup set price=in_price where subhead=in_subhead;
ELSEIF in_query_type = 'new_test' THEN
	INSERT INTO lab_setup (head,subhead,description, label_name, noOfLabels, unit, range_from, range_to, report_type,facilityId, specimen, collect_sample, to_be_analyzed, to_be_reported, upload_doc) VALUES (in_head,in_subhead,in_description, in_label_name, in_no_of_labels, in_unit, in_range_from, in_range_to, in_report_type, facId,in_specimen, in_to_collect_sample, in_to_be_analyzed, in_to_be_reported, in_upload_doc);
ELSEIF in_query_type = 'update_test' THEN
UPDATE lab_setup SET head=in_head, description=in_description, label_name=in_label_name, noOfLabels=in_no_of_labels, unit=in_unit, range_from=in_range_from, range_to=in_range_to, report_type=in_report_type, specimen=in_specimen, collect_sample=in_to_collect_sample, to_be_analyzed=in_to_be_analyzed, to_be_reported=in_to_be_reported, upload_doc=in_upload_doc WHERE subhead=in_subhead AND facilityId=facId;
ELSEIF in_query_type = 'delete' THEN 
	DELETE FROM lab_setup WHERE subhead = in_subhead AND facilityId=facId;
ELSEIF in_query_type = 'code_setup' THEN
	UPDATE lab_setup SET label_type=in_label_type, print_type=in_print_type WHERE subhead=in_subhead AND facilityId=facId;
ELSEIF in_query_type = 'test_info' THEN
	SELECT * FROM lab_setup WHERE subhead=in_subhead;
ELSEIF in_query_type = 'group_list' THEN
SELECT * FROM lab_setup WHERE subhead IN (SELECT DISTINCT head FROM lab_setup);
ELSEIF in_query_type = 'unit_list' THEN
	IF in_head = 'all' THEN
		SELECT DISTINCT unit_name,unit_code FROM lab_setup;
    ELSE 
    	SELECT DISTINCT unit_name,unit_code FROM lab_setup WHERE lab_code = in_head;
    END IF;
ELSEIF in_query_type = 'department_list' THEN
	SELECT * FROM lab_setup WHERE head=1000;
ELSEIF in_query_type = 'Barcode Setup' THEN
	IF in_form_mode = 'Stand Alone Test' THEN 
    	SELECT * FROM lab_setup WHERE label_type='single';
    ELSE 
    	SELECT * FROM lab_setup WHERE label_type IN ('grouped', 'singular_group', 'grouped_single');
    END IF;
   	ELSEIF in_query_type = 'by_group' THEN
    call get_lab_setup_account('','', in_subhead, facId);
END IF$$
DELIMITER ;