-- 16-12-21
update procedure update_lab_test

-- 09-12-21
-- CREATE TABLE echo

-- CREATE PROCEDURE query_echo

-- MODIFY PROCEDURE get_lab_list



-- 28-11-21 (optimum_231121)
-- create procedure query_code

-- -- 24-11-21
-- update procedure update_lab_test add commit

-- 24-11-21
-- update procedure update_lab_test, get_sample_doc_details;

-- 20-11-2021 (optimum_101121-clean)
-- create procedure get_queue

-- get doctors reporting fees
SELECT description, name, result_at,
    CASE 
        WHEN commission_type = 'percentage' THEN old_price * percentage/100
        ELSE percentage
    END as commission
    FROM lab_info WHERE result_by='auwal02' AND price > 0 AND created_at BETWEEN '2021-11-24' AND '2021-12-24'



ALTER TABLE `transactions3` ADD INDEX( `acct`);
ALTER TABLE `account` ADD INDEX(`head`);
ALTER TABLE `transactions3` CHANGE `acct` `acct` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `transactions3` ADD CONSTRAINT `fk_account_head` FOREIGN KEY (`acct`) REFERENCES `account`(`head`) ON DELETE RESTRICT ON UPDATE RESTRICT;

UPDATE `transactions3` set modeOfPayment='KNCV  Receivables' WHERE modeOfPayment=''

-- create procedure get_doc_fee_for_lab
-- MODIFY procedure get_doc_report_fees, move_money

-- add report_fee_status to lab_info, lab_request2, lab_process

-- UPDATE lab_setup SET old_price = 0 WHERE price=0;
-- ALTER TABLE `doctor_entries` ADD `patient_name` VARCHAR(100) NULL AFTER `new_price`;
-- ALTER TABLE `doctor_entries` CHANGE `description` `description` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
-- ALTER TABLE `lab_requisition` ADD `report_fee_status` VARCHAR(50) NULL AFTER `discount_amount`;

-- ALTER TABLE `doctor_fees` ADD `old_price` INT NULL AFTER `createdAt`, ADD `new_price` INT NULL AFTER `old_price`;

-- update procedure get_all_lab_services;
-- new procedure query_lab_setup, get_lab_setup_account

-- -- controller/account.js
-- --         modified:   controller/lab.js

-- -- lab setup get account


-- update procedure get_lab_result, service_transaction3, get_client_accounts, get_balance, lab_summary
-- -- create procedure create_discount

-- ALTER TABLE `comment` CHANGE `comment` `comment` VARCHAR(10000) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
-- ALTER TABLE `report_templates` CHANGE `body` `body` VARCHAR(10000) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
-- ALTER TABLE `lab_requisition` ADD `discount` VARCHAR(50) NULL AFTER `client_account`, ADD `discount_head` VARCHAR(50) NULL AFTER `discount`;
-- ALTER TABLE `lab_requisition` ADD `discount_head_name` VARCHAR(50) NULL AFTER `discount_head`;
-- ALTER TABLE `lab_requisition` ADD `discount_amount` INT NULL AFTER `discount_head_name`;

-- CREATE TABLE `optimum_181021`.`discount` ( `id` INT NOT NULL AUTO_INCREMENT ,  `discountName` VARCHAR(50) NOT NULL ,  `discountType` VARCHAR(20) NOT NULL ,  `discountAmount` INT NOT NULL ,  `discountHead` VARCHAR(50) NOT NULL ,  `discountHeadName` VARCHAR(50) NOT NULL ,    PRIMARY KEY  (`id`)) ENGINE = InnoDB;
-- ALTER TABLE `discount` ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `discountHeadName`, ADD `created_by` VARCHAR(50) NOT NULL AFTER `created_at`;
-- ALTER TABLE `discount` ADD `facilityId` VARCHAR(50) NOT NULL AFTER `created_by`;


-- UPDATE PROCEDURE get_lab_result, get_pending_lab, get_lab_list
-- -- CREATE PROCEDURE update_lab_test


-- modified:   config/config.json
-- modified:   controller/lab.js
-- modified:   controller/lab2.js
-- modified:   models/optimum.sql
-- modified:   routes/lab.js

-- procedure update get_pending_lab, get_lab_list
-- new PROCEDURE update_receipt_no



-- export procedure get_lab_list, 
-- MODIFY PROCEDURE create_org_acc, get_beneficiary_no,getLabReceipt
-- CREATE PROCEDURE get_lab_by_booking, get_client_accounts

-- ALTER TABLE `patientfileno` ADD `payable_head_name` VARCHAR(100) NULL AFTER `approved_by`, ADD `payable_head` VARCHAR(50) NULL AFTER `payable_head_name`;
-- ALTER TABLE `patientfileno` ADD `receivable_head_name` VARCHAR(100) NULL AFTER `payable_head`, ADD `receivable_head` VARCHAR(50) NULL AFTER `receivable_head_name`;

-- ALTER TABLE `lab_requisition` ADD `client_type` VARCHAR(50) NOT NULL AFTER `modeOfPayment`;
-- ALTER TABLE `lab_requisition` ADD `client_account` VARCHAR(50) NOT NULL AFTER `client_type`;



-- UPDATE PROCEDURE lab_summary,get_all_lab_services,get_lab_result,get_lab2



-- modified:   config/config.json
--         modified:   controller/diagnosis.js
--         modified:   controller/lab.js
--         modified:   controller/pharmacy.js
--         modified:   controller/users.js
--         modified:   models/mylikita_professional2.sql
--         modified:   models/optimum.sql
--         modified:   models/prime_changes.sql
--         modified:   routes/pharmacy.js

-- new PROCEDURE new_org_client

-- update procedure get_lab_result, get_all_lab_services


ALTER TABLE `users` ADD `signature_title` VARCHAR(50) NOT NULL AFTER `branch_name`;
INSERT INTO `booking_no2` (`id`, `department`, `unit_name`, `lab_code`, `unit_code`, `initials`, `year_code`, `booking`) VALUES (NULL, 'Cardiology', 'Cardiology', '6000', '1', '1', '21', '1');

        modified:   config/config.json
        modified:   controller/lab.js
        modified:   controller/lab2.js
        modified:   models/optimum.sql

-- CREATE TABLE transaction_entries, transactions3,pagenavigation and copy data;booking_no2,bank_accounts
-- MODIFY PROCEDURE service_transactions, get_all_lab_services, customer_deposit, lab_summary, get_lab2,department;
-- CREATE PROCEDURE get_homepage,bank_accounts,service_transactions3

-- ALTER TABLE `lab_setup`  ADD `payable_head` VARCHAR(10) NOT NULL  AFTER `label_name`,  ADD `receivable_head` VARCHAR(10) NOT NULL  AFTER `payable_head`;
-- ALTER TABLE `lab_requisition` ADD `old_price` INT NULL AFTER `price`;
-- ALTER TABLE `lab_requisition` ADD `test_group_price` INT NOT NULL AFTER `test_group`, ADD `test_group_old_price` INT NOT NULL AFTER `test_group_price`;
-- ALTER TABLE `lab_requisition` ADD `payable_head` INT NULL AFTER `print_type`, ADD `receivable_head` INT NULL AFTER `payable_head`;
-- ALTER TABLE `lab_setup` ADD `receivable_head_name` VARCHAR(50) NOT NULL AFTER `payable_head`;
-- ALTER TABLE `lab_setup` ADD `payable_head_name` VARCHAR(50) NOT NULL AFTER `payable_head`;
-- ALTER TABLE `lab_setup` ADD `account_name` VARCHAR(50) NOT NULL AFTER `account`;
-- UPDATE lab_setup SET account_name='Hematology', receivable_head_name='Hematology Receivables', payable_head_name='Hematology Payables' WHERE account='20001';
-- UPDATE lab_setup SET account_name='Chemical Pathology', receivable_head_name='Chemical Pathology Receivables', payable_head_name='Chemical Pathology Payables' WHERE account='20002';
-- UPDATE lab_setup SET account_name='Microbiology', receivable_head_name='Microbiology Receivables', payable_head_name='Microbiology Payables' WHERE account='20003';
-- UPDATE lab_setup SET account_name='Radiology', receivable_head_name='Radiology Receivables', payable_head_name='Radiology Payables' WHERE account='20004';
-- ALTER TABLE `lab_requisition` ADD `account` VARCHAR(50) NOT NULL AFTER `receivable_head`, ADD `account_name` VARCHAR(50) NOT NULL AFTER `account`;
-- ALTER TABLE `lab_requisition` ADD `patient_name` VARCHAR(200) NOT NULL AFTER `patient_id`;
-- ALTER TABLE `lab_requisition` ADD `receivable_head_name` VARCHAR(50) NOT NULL AFTER `receivable_head`;
-- UPDATE lab_setup SET payable_head='5000211',payable_head_name='Trade Payables',receivable_head='4000236',receivable_head_name='General Patient Receivables';
-- ALTER TABLE `lab_setup` ADD `department_code` VARCHAR(50) NOT NULL AFTER `lab_code`, ADD `unit_code` VARCHAR(50) NOT NULL AFTER `department_code`;
-- ALTER TABLE `lab_requisition` ADD `department_code` VARCHAR(50) NOT NULL AFTER `account_name`, ADD `unit_code` VARCHAR(50) NOT NULL AFTER `department_code`;
-- ALTER TABLE `lab_requisition` ADD `unit_name` VARCHAR(100) NOT NULL AFTER `unit_code`;
-- ALTER TABLE `lab_setup` ADD `unit_name` VARCHAR(100) NOT NULL AFTER `unit_code`;
-- ALTER TABLE `transaction_entries` ADD `patient_name` VARCHAR(200) NOT NULL AFTER `patient_id`;
-- ALTER TABLE `transaction_entries` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);

UPDATE lab_setup SET unit_code = 1, unit_name='Radiology', department_code=5000 where subhead like '5%';
UPDATE lab_setup SET unit_code = 2, unit_name='Hematology', department_code=2000 where subhead like '2%';
UPDATE lab_setup SET unit_code = 3, unit_name='Chemistry', department_code=3000 where subhead like '3%';
UPDATE lab_setup SET unit_code = 7, unit_name='Serology' where head = '4045';
UPDATE lab_setup SET unit_code = 6, unit_name='Microbiology' where head = '4044';
UPDATE lab_setup SET unit_code = 4, unit_name='Tumor Markers' where head = '30098';
UPDATE lab_setup SET unit_code = 5, unit_name='Hormones' where head = '300100';


MODIFY VIEW lab_info, lab_request2, lab_process;


-- UPDATE lab_setup SET price = 0 WHERE selectable='not allowed' AND price > 0;


3.
modified:   config/config.json        
modified:   controller/lab.js
modified:   controller/lab2.js        
modified:   controller/transactions.js
modified:   models/optimum.sql

2.
modified:   controller/doc.js
modified:   controller/lab.js
modified:   controller/lab2.js
modified:   models/optimum.sql
modified:   routes/doc.js



1.
modified:   config/config.json
modified:   controller/lab.js
modified:   controller/lab2.js
modified:   controller/transactions.js
modified:   models/optimum.sql
modified:   routes/lab.js





-- CREATE PROCEDURE `update_token`(IN `labno` VARCHAR(50), IN `in_token` VARCHAR(50)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER UPDATE lab_requisition SET token=in_token WHERE booking_no = labno;


-- drop PROCEDURE getLabReceipt;
-- DELIMITER $$
-- CREATE PROCEDURE `getLabReceipt`(IN `in_receiptNo` VARCHAR(50), IN `in_facilityId` VARCHAR(50))
--     NO SQL
-- SELECT description,credit as price, patient_id,createdAt,enteredBy,modeOfPayment FROM  transactions    WHERE receiptDateSN = in_receiptNo AND credit != 0 AND facilityId=in_facilityId AND acct != '500021'$$
-- DELIMITER ;

-- -- update procedure get_lab_request


-- -- -- create procedure get_last_count_per_dept;


-- -- -- -- my pc
-- -- -- create procedure 
-- -- -- CREATE PROCEDURE `get_lab_request`(IN `in_pid` VARCHAR(50), IN `in_labno` VARCHAR(50), IN `in_facId` VARCHAR(50), IN `in_query_type` VARCHAR(50)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER IF in_query_type = 'lab_request' THEN SELECT * FROM lab_requisition WHERE booking_no=in_labno AND facilityId=in_facId AND patient_id=in_pid; ELSEIF in_query_type = 'comment' THEN SELECT * FROM comment WHERE booking_no=in_labno AND facilityId=in_facId; ELSEIF in_query_type = 'patientrecords' THEN SELECT * FROM patientrecords WHERE id=in_id AND facilityId=in_facId; END IF;