ALTER TABLE `lab_setup` ADD `print_type` VARCHAR(20) NULL AFTER `noOfLabels`, ADD `collect_sample` VARCHAR(20) NULL AFTER `print_type`;
ALTER TABLE `lab_setup` ADD `upload_doc` VARCHAR(20) NULL AFTER `collect_sample`;
ALTER TABLE `lab_setup` ADD `to_be_analyzed` VARCHAR(20) NULL AFTER `collect_sample`, ADD `to_be_reported` VARCHAR(20) NULL AFTER `to_be_analyzed`;

-- update chem_path and hema
UPDATE `lab_setup` SET collect_sample='yes', print_type='grouped' WHERE head like '2%'
UPDATE `lab_setup` SET collect_sample='yes', print_type='grouped' WHERE head like '3%'

-- micro, update serology tests
UPDATE `lab_setup` SET collect_sample='yes', print_type='grouped' WHERE head=4045
-- micro, update microbiology tests
UPDATE `lab_setup` SET collect_sample='yes', print_type='single' WHERE head=4044

-- Also update widal test setup

-- Touch the joint statement in lab_summary procedure
...FROM transactions a JOIN patientrecords b ON a.patient_id=b.id ...

-- Also modify the getReportByType where query_type = 'trial_balance'

-- 24-03-2021
-- get_doctors procedure now takes two arguments
-- add visit_id to lab_requisition
ALTER TABLE `lab_requisition` ADD `visit_id` VARCHAR(50) NULL AFTER `receiptNo`;

-- update users where department is chemical, change to "Chemical Pathology"

ALTER TABLE `lab_setup` ADD `use_formula` VARCHAR(5) NULL AFTER `print_type`, ADD `formula` VARCHAR(100) NULL AFTER `use_formula`;

UPDATE lab_setup set to_be_analyzed='yes'
UPDATE lab_setup set collect_sample='no'

UPDATE `lab_setup` SET report_type='table' where head like '2%' OR head like '3%'
UPDATE `lab_setup` SET report_type='ho_widal' WHERE head=4042 OR subhead=4042
update `lab_setup` SET report_type='microbiology_form' WHERE head=4044 and report_type is NULL

ALTER TABLE `lab_requisition`  ADD `h_value` VARCHAR(10) NULL  AFTER `result`,  ADD `o_value` VARCHAR(10) NULL  AFTER `h_value`;

update lab_info, lab_request2, lab_process with h_value and o_value


-- // latest

-- create procedure create_bed, get_beds, bed_allocation, get_patient_list, get_lab_result, get_drug_freq, get_prescribed_drugs, get_patient_drug_schedule
-- create table bedlist, bed_allocation, vital_signs, drug_frequency,drug_schedule
-- create view bedlist_view, patient_bed, in_patient 

create procedure get_nursing_reports, new_nursing_report, department
update procedure bed_allocation
create table department
create view sample_history1,sample_history2,sample_history3,sample_history4,sample_history5,sample_history


-- update procedures get_patient_list, get_pending_lab for the arrangement of patient name to be "firstname surname"
-- update procedure get_lab_list to update the where condition, WHERE a.status != "printed"

-- 07-07-2021
create table refund_requests
create procedure refund_requests, get_patient_txn
	update procedure get_lab_list,get_pending_lab
remove extra atribute on created_at from lab_requisition table

=================================
DELIMITER $$
CREATE PROCEDURE `refund_requests`(IN `query_type` VARCHAR(20), IN `in_patient_id` VARCHAR(50), IN `userId` VARCHAR(50), IN `labno` VARCHAR(50), IN `in_amount_paid` INT, IN `in_refund_amount` INT, IN `in_reasons` VARCHAR(1000), IN `approved_by` VARCHAR(50), IN `approved_at` DATETIME, IN `in_date` DATE, IN `facId` VARCHAR(50))
    NO SQL
IF query_type = 'new' THEN
	INSERT INTO refund_requests (created_at,created_by,client_id,affected_test,amount_paid,refund_amount,reasons, status, facilityId) VALUES (in_date,userId,in_patient_id,labno,in_amount_paid,in_refund_amount,in_reasons,"pending",facId);
ELSEIF query_type = 'pending' THEN
	SELECT * FROM refund_requests where status='pending' AND facilityId=facId;
END IF$$
DELIMITER ;

=================================
DELIMITER $$
CREATE PROCEDURE `get_lab_list`(IN `in_query_type` VARCHAR(10), IN `in_facId` VARCHAR(50), IN `in_pid` VARCHAR(50), IN `date_from` DATE, IN `date_to` DATE)
    NO SQL
IF in_query_type = 'all' THEN
	SELECT a.booking_no, concat(b.firstname, ' ', b.surname) as name,a.patient_id,a.created_at,a.department,count(a.status) as tests,
    (SELECT COUNT(*) FROM lab_requisition WHERE status IN ('pending') AND booking_no=a.booking_no ) as pending,
	(SELECT COUNT(*) FROM lab_requisition WHERE status IN ('Sample Collected', 'saved') AND booking_no=a.booking_no ) as collected,
	(SELECT COUNT(*) FROM lab_requisition WHERE status IN ('analyzed') AND booking_no=a.booking_no ) as analyzed,
    (SELECT COUNT(*) FROM lab_requisition WHERE status='result' AND booking_no=a.booking_no AND facilityId=in_facId) as completed
		FROM lab_requisition a JOIN patientrecords b ON a.patient_id=b.id 
        WHERE a.facilityId=in_facId AND a.status!='printed'
        AND (a.created_at BETWEEN date(date_from) AND date(date_to)) AND b.facilityId=in_facId 
        GROUP BY booking_no, a.patient_id, a.created_at, name order by count(a.status)-completed, count(a.status)-analyzed, 
        a.created_at DESC;
    ELSE
    	
       SELECT booking_no, patient_id,created_at,department,count(status) as tests,(SELECT COUNT(*) FROM lab_requisition WHERE status='result' AND booking_no=booking_no AND facilityId=in_facId) as completed
		FROM lab_requisition WHERE patient_id=in_pid AND facilityId=in_facId GROUP BY booking_no, created_at order by count(status)-completed, created_at DESC;
        END IF$$
DELIMITER ;

================================
DELIMITER $$
CREATE PROCEDURE `get_pending_lab`(IN `query_type` VARCHAR(10), IN `in_dept` VARCHAR(50), IN `facId` VARCHAR(50), IN `fromDate` DATE, IN `toDate` DATE)
    NO SQL
BEGIN SELECT DISTINCT a.booking_no as labno, concat(b.surname, ' ', b.firstname) as name, a.department_head AS department, COUNT(distinct description) AS no_of_tests, a.patient_id, description,a.subhead,a.code FROM lab_process a JOIN patientrecords b ON a.patient_id=b.id WHERE a.status = query_type AND a.department_head=in_dept AND a.facilityId=facId AND b.facilityId=facId 
AND date(created_at) BETWEEN date(fromDate) AND date(toDate)
GROUP by labno, description,a.patient_id; END$$
DELIMITER ;

======================
DELIMITER $$
CREATE `get_patient_txn`(IN `query_type` VARCHAR(20), IN `in_patient_id` VARCHAR(50), IN `facId` VARCHAR(50))
    NO SQL
IF query_type = 'all' THEN
	SELECT * FROM transactions where patient_id=in_patient_id AND debit<>0 AND facilityId=facId;
END IF$$
DELIMITER ;

====================================
CREATE TABLE `refund_requests` (
 `created_at` datetime NOT NULL,
 `created_by` varchar(50) NOT NULL,
 `client_id` varchar(50) NOT NULL,
 `affected_test` varchar(50) NOT NULL,
 `amount_paid` int(11) NOT NULL,
 `refund_amount` int(11) NOT NULL,
 `reason` varchar(1000) NOT NULL,
 `status` varchar(20) DEFAULT NULL,
 `approved_by` varchar(50) DEFAULT NULL,
 `approved_at` timestamp NULL DEFAULT NULL,
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `facilityId` varchar(50) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


====================================================================
update table lab_setup add column label_type
setup report_type as single,grouped,grouped_by_dept,singular_group
setup label_type as single, grouped

==========================
set table_result report_type;

==========================
add label_type to lab_process, lab_request2, lab_setup VIEW
add label_type to get_lab_result PROCEDURE


modify hospitals table
ALTER TABLE `hospitals` ADD `modules` VARCHAR(100) NULL AFTER `admin`, ADD `features` VARCHAR(500) NULL AFTER `modules`, ADD `printTitle` VARCHAR(50) NULL AFTER `features`, ADD `printSubtitle1` VARCHAR(200) NULL AFTER `printTitle`, ADD `printSubtitle2` VARCHAR(200) NULL AFTER `printSubtitle1`;

modify procedure get_facility_info

add old_price to lab_process, lab_request2, lab_setup VIEW


SELECT a.booking_no,a.receiptNo,a.user_id,u_price,a.department,a.created_at, b.description, b.debit FROM report_w_receipt a JOIN transactions b on a.receiptNo=b.receiptDateSN where debit <> 0

modify PROCEDURE get_lab_list, to get from lab_process, add receiptNo and department_head to columns, 
update procedure create_new_lab_client

UPDATES FOR PRIME
ALTER TABLE `bed_allocation` ADD `allocation_status` VARCHAR(20) NULL AFTER `patient_id`;
ALTER TABLE `bed_allocation`  ADD `ended_by` VARCHAR(50) NOT NULL  AFTER `allocated_by`;
ALTER TABLE `bedlist` ADD `account` VARCHAR(10) NOT NULL AFTER `class_type`;

modify in_patient_list, patient_bed, get_beds, get_patient_list VIEW

add allocation_status to bed_allocation

