-- ALTER TABLE `lab_setup` ADD `percentage` INT NOT NULL AFTER `price`;

-- DROP PROCEDURE `add_new_lab_service`; CREATE DEFINER=`root`@`localhost` PROCEDURE `add_new_lab_service`(IN `labhead` VARCHAR(50), IN `labsubhead` VARCHAR(50), IN `unit` VARCHAR(50), IN `test` VARCHAR(50), IN `facId` VARCHAR(50), IN `test_from` VARCHAR(50), IN `test_to` VARCHAR(50), IN `spec` VARCHAR(50), IN `test_price` INT, IN `userId` VARCHAR(50), IN `descr` VARCHAR(50), IN `no_of_labels` INT, IN `in_percentage` INT) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN INSERT INTO lab_setup(head, subhead, description, noOfLabels, unit, facilityId, range_from, range_to, specimen, price, percentage, created_by) VALUES (labhead, labsubhead, descr, no_of_labels, unit, facId, test_from, test_to, spec, test_price, in_percentage, userId); END


ALTER TABLE `lab_requisition` ADD `sample_collected_at` DATETIME NULL AFTER `sample_collected_by`;
ALTER TABLE `lab_requisition` ADD `analyzed_at` DATETIME NULL AFTER `analyzed_by`;
ALTER TABLE `lab_requisition` ADD `result_at` DATETIME NULL AFTER `result_by`;
ALTER TABLE `lab_requisition` ADD `reviewed_at` DATETIME NULL AFTER `reviewed_by`;
ALTER TABLE `lab_requisition` ADD `printed_at` DATETIME NULL AFTER `printed_by`;

DROP PROCEDURE `create_new_lab_client`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_new_lab_client`(IN `p_id` VARCHAR(50), IN `acc_no` VARCHAR(50), IN `ben_no` VARCHAR(50), IN `first_name` VARCHAR(50), IN `last_name` VARCHAR(50), IN `other_name` VARCHAR(50), IN `p_gender` VARCHAR(50), IN `birth` DATE, IN `mail` VARCHAR(50), IN `facId` VARCHAR(50), IN `labno` VARCHAR(50), IN `phone` VARCHAR(13)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN 

INSERT INTO patientrecords (patient_id,accountNo,beneficiaryNo,id,firstname,surname,other,Gender, DOB,email,facilityId,phoneNo) VALUES (p_id,acc_no,ben_no,CONCAT(acc_no,'-',ben_no),first_name, last_name,other_name, p_gender,birth,mail,facId,phone); 


END

ALTER TABLE `sensitivity_results` ADD `isolates` VARCHAR(50) NULL AFTER `antibiotic`;
ALTER TABLE `sensitivity_results` DROP `id`;
ALTER TABLE `sensitivity_results` ADD PRIMARY KEY( `antibiotic`, `labno`);
ALTER TABLE `sensitivity_results` CHANGE `updated_at` `updated_at` DATETIME NULL;

ALTER TABLE `lab_requisition`  ADD `unit` VARCHAR(20) NULL  AFTER `printed_at`,  ADD `range_from` VARCHAR(20) NULL  AFTER `unit`,  ADD `range_to` VARCHAR(20) NULL  AFTER `range_from`;
ALTER TABLE `lab_requisition` ADD `sensitivity` VARCHAR(4000) NULL AFTER `culture_yielded`, ADD `resistivity` VARCHAR(4000) NULL AFTER `sensitivity`;

ALTER TABLE `lab_requisition` CHANGE `result` `result` VARCHAR(4000) NULL, CHANGE `appearance` `appearance` VARCHAR(4000) NULL, CHANGE `serology` `serology` VARCHAR(4000) NULL, CHANGE `culture_yielded` `culture_yielded` VARCHAR(4000) NULL;


-- # lab_info
SELECT
    a.`created_at` AS `created_at`,
    a.`facilityId` AS `facilityId`,
    a.`id` AS `id`,
    a.`code` AS `code`,
    a.`booking_no` AS `booking_no`,
    a.`patient_id` AS `patient_id`,
    b.`head` AS `head`,
    b.`subhead` AS `subhead`,
    b.`unit` AS `unit`,
    b.`range_from` AS `range_from`,
    b.`range_to` AS `range_to`,
    b.`price` AS `price`,
    b.`description` AS `description`,
    b.`specimen` AS `specimen`,
    b.`noOfLabels` AS `noOfLabels`,
    a.`department` AS `department`,
    a.`result` AS `result`,
    a.`appearance` AS `appearance`,
    a.`serology` AS `serology`,
    a.`culture_yielded` AS `culture_yielded`,
    a.`sensitivity` AS `sensitivity`,
    a.`resistivity` AS `resistivity`,
    a.`status` AS `status`,
    a.`created_by` AS `created_by`,
    a.`sample_collected_by` AS `sample_collected_by`,
    a.`sample_collected_at` AS `sample_collected_at`,
    a.`analyzed_by` AS `analyzed_by`,
    a.`analyzed_at` AS `analyzed_at`,
    a.`result_by` AS `result_by`,
    a.`result_at` AS `result_at`,
    a.`reviewed_by` AS `reviewed_by`,
    a.`reviewed_at` AS `reviewed_at`,
    a.`printed_by` AS `printed_by`,
    a.`printed_at` AS `printed_at`,
    a.`unit` AS `n_unit`,
    a.`range_from` AS `n_range_from`,
    a.`range_to` AS `n_range_to`
FROM
    (
        lab_setup b
    JOIN lab_requisition a ON
        (b.`subhead` = a.`test`)
    )
WHERE
    a.`facilityId` = b.`facilityId`


-- # lab_request2
SELECT
    a.`created_at` AS `created_at`,
    a.`facilityId` AS `facilityId`,
    a.`id` AS `id`,
    a.`code` AS `code`,
    a.`booking_no` AS `booking_no`,
    a.`patient_id` AS `patient_id`,
    a.`head` AS `head`,
    a.`subhead` AS `subhead`,
    a.`unit` AS `unit`,
    a.`range_from` AS `range_from`,
    a.`range_to` AS `range_to`,
    a.`price` AS `price`,
    a.`description` AS `description`,
    a.`specimen` AS `specimen`,
    a.`noOfLabels` AS `noOfLabels`,
    b.`description` AS `group_head`,
    a.`department` AS `department`,
    a.`result` AS `result`,
    a.`appearance` AS `appearance`,
    a.`serology` AS `serology`,
    a.`culture_yielded` AS `culture_yielded`,
    a.`sensitivity` AS `sensitivity`,
    a.`resistivity` AS `resistivity`,
    a.`status` AS `status`,
    a.`created_by` AS `created_by`,
    a.`sample_collected_by` AS `sample_collected_by`,
    a.`sample_collected_at` AS `sample_collected_at`,
    a.`analyzed_by` AS `analyzed_by`,
    a.`analyzed_at` AS `analyzed_at`,
    a.`result_by` AS `result_by`,
    a.`result_at` AS `result_at`,
    a.`reviewed_by` AS `reviewed_by`,
    a.`reviewed_at` AS `reviewed_at`,
    a.`printed_by` AS `printed_by`,
    a.`printed_at` AS `printed_at`,
    a.`n_unit` AS `n_unit`,
    a.`n_range_from` AS `n_range_from`,
    a.`n_range_to` AS `n_range_to`
FROM
    (
        `mylikita`.`lab_info` a
    JOIN `mylikita`.`lab_setup` b ON
        (
            b.`subhead` = a.`head`
        )
    )
WHERE
    a.`facilityId` = b.`facilityId`



-- # lab_process
SELECT
    `a`.`created_at` AS `created_at`,
    `a`.`facilityId` AS `facilityId`,
    `a`.`id` AS `id`,
    `a`.`code` AS `code`,
    `a`.`booking_no` AS `booking_no`,
    `a`.`patient_id` AS `patient_id`,
    `a`.`head` AS `head`,
    `a`.`subhead` AS `subhead`,
    `a`.`unit` AS `unit`,
    `a`.`range_from` AS `range_from`,
    `a`.`range_to` AS `range_to`,
    `a`.`price` AS `price`,
    `a`.`description` AS `description`,
    `a`.`specimen` AS `specimen`,
    `a`.`noOfLabels` AS `noOfLabels`,
    `a`.`group_head` AS `group_head`,
    `a`.`department` AS `department`,
    `b`.`description` AS `department_head`,
    `a`.`result` AS `result`,
    `a`.`appearance` AS `appearance`,
    `a`.`serology` AS `serology`,
    `a`.`culture_yielded` AS `culture_yielded`,
    a.`sensitivity` AS `sensitivity`,
    a.`resistivity` AS `resistivity`,
    `a`.`status` AS `status`,
    `a`.`created_by` AS `created_by`,
    a.`sample_collected_by` AS `sample_collected_by`,
    a.`sample_collected_at` AS `sample_collected_at`,
    a.`analyzed_by` AS `analyzed_by`,
    a.`analyzed_at` AS `analyzed_at`,
    a.`result_by` AS `result_by`,
    a.`result_at` AS `result_at`,
    a.`reviewed_by` AS `reviewed_by`,
    a.`reviewed_at` AS `reviewed_at`,
    a.`printed_by` AS `printed_by`,
    a.`printed_at` AS `printed_at`,
    a.`n_unit` AS `n_unit`,
    a.`n_range_from` AS `n_range_from`,
    a.`n_range_to` AS `n_range_to`
FROM
    (
        `mylikita`.`lab_request2` `a`
    JOIN `mylikita`.`lab_setup` `b`
    ON
        (`a`.`department` = `b`.`subhead`)
    )
WHERE
    `a`.`facilityId` = `b`.`facilityId`

-----------------------------
-----------------------------
-- 11-22-2020

DROP PROCEDURE `get_id`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_id`(IN `facId` VARCHAR(50)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
    select ifnull(max(accountNo), 0) + 1 as nextId from patientrecords WHERE facilityId=facId;
END

DROP
PROCEDURE `create_new_client_acc`;
CREATE DEFINER = `root`@`localhost` PROCEDURE `create_new_client_acc`(
    IN `accType` VARCHAR(20),
    IN `s_name` VARCHAR(50),
    IN `f_name` VARCHAR(50),
    IN `p_gender` VARCHAR(20),
    IN `dob` DATE,
    IN `maritalStatus` VARCHAR(20),
    IN `p_occupation` VARCHAR(20),
    IN `address` VARCHAR(100),
    IN `depositAmount` INT,
    IN `modeOfPayment` VARCHAR(20),
    IN `c_name` VARCHAR(50),
    IN `c_address` VARCHAR(100),
    IN `c_phone` VARCHAR(20),
    IN `c_email` VARCHAR(50),
    IN `web` VARCHAR(20),
    IN `facId` VARCHAR(50),
    IN `userId` VARCHAR(50),
    IN `patientId` VARCHAR(50),
    IN `kName` VARCHAR(200),
    IN `kPhone` VARCHAR(100),
    IN `kRel` VARCHAR(20),
    IN `kEmail` VARCHAR(50),
    IN `kAddress` VARCHAR(200)
) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
BEGIN
        IF accType = 'family' THEN
    INSERT INTO patientfileno(
        facilityId,
        accountType,
        accName,
        contactName,
        contactAddress,
        contactPhone,
        contactEmail,
        contactWebsite,
        firstname,
        surname
    )
VALUES(
    facId,
    accType,
    CONCAT(s_name, ' ', f_name),
    c_name,
    c_address,
    c_phone,
    c_email,
    web,
    f_name,
    s_name
);
INSERT INTO patientrecords(
    facilityId,
    surname,
    firstname,
    gender,
    maritalstatus,
    DOB,
    phoneNo,
    email,
    occupation,
    address,
    patient_id,
    kinName,
    kinRelationship,
    kinPhone,
    kinEmail,
    kinAddress
)
VALUES(
    facId,
    s_name,
    s_name,
    gender,
    maritalStatus,
    dob,
    c_phone,
    c_email,
    p_occupation,
    c_address,
    patientId,
    kName,
    kRel,
    kPhone,
    kEmail,
    kAddress
); ELSE
INSERT INTO patientfileno(
    facilityId,
    accountType,
    contactName,
    contactAddress,
    contactPhone,
    contactEmail,
    contactWebsite,
    description,
    accName
)
VALUES(
    facId,
    accType,
    c_name,
    c_address,
    c_phone,
    c_email,
    web,
    f_name,
    s_name
);
    END IF;
COMMIT
    ;
END


DROP PROCEDURE `get_facility_info`;
CREATE PROCEDURE `get_facility_info`(IN `fac_id` VARCHAR(50)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER SELECT id as facility_id, name as facility_name, code, logo, type, address, admin FROM hospitals WHERE id = fac_id

ALTER TABLE `lab_setup` ADD `commission_type` VARCHAR(50) NOT NULL AFTER `price`;


-- 1-12-2020
ALTER TABLE `lab_setup` ADD `account` VARCHAR(50) NULL AFTER `id`;
ALTER TABLE `lab_requisition` ADD `intermediaryTo` VARCHAR(4000) NULL AFTER `resistivity`;
ALTER TABLE `doctor_entries` CHANGE `doc_acct` `doc_acct` VARCHAR(50) NULL DEFAULT NULL;


-- 2-12-2020
DROP PROCEDURE `get_rev_acc_heads`;
CREATE PROCEDURE `get_rev_acc_heads`(IN `facId` VARCHAR(50)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
    SELECT head as title, subhead, description, price FROM account WHERE subhead = '20000' and facilityId=facId;
END