CREATE view transaction_view AS 
    SELECT facilityId, createdAt,acct,debit,description,patient_id
        FROM transactions 
        WHERE debit <> 0

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `facilityId` varchar(50) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `transaction_id` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `description` varchar(225) DEFAULT NULL,
  `acct` varchar(100) DEFAULT NULL,
  `debit` int(50) DEFAULT NULL,
  `credit` int(50) DEFAULT NULL,
  `enteredBy` varchar(20) DEFAULT NULL,
  `receiptDateSN` varchar(18) NOT NULL DEFAULT '0',
  `receiptNo` int(7) DEFAULT '0',
  `modeOfPayment` varchar(15) DEFAULT NULL,
  `status` varchar(10) DEFAULT 'pending',
  `approvedBy` varchar(20) DEFAULT NULL,
  `paymentStatus` varchar(11) NOT NULL DEFAULT '',
  `client_acct` varchar(11) DEFAULT NULL,
  `patient_id` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `transactions`
--

INSERT INTO `transactions` (`facilityId`, `createdAt`, `transaction_id`, `id`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `status`, `approvedBy`, `paymentStatus`, `client_acct`, `patient_id`) VALUES
('6c6af0c0-35ea-40d8-a928-b13a9766113a', '2020-08-16 06:22:48', 1, 0, 'Admission', 'Consultatio', 0, 3000, 'cash', '40', 16082011, '1', 'pending', NULL, '', '9', '9-1'),
('6c6af0c0-35ea-40d8-a928-b13a9766113a', '2020-08-16 06:22:48', 2, 0, 'Admission', 'Bank', 3000, 0, 'cash', '40', 16082011, '1', 'pending', NULL, '', '9', '9-1');

--
-- Triggers `transactions`
--
DELIMITER $$
CREATE TRIGGER `set_txn_date` BEFORE INSERT ON `transactions` FOR EACH ROW BEGIN
    		IF (isnull(new.createdAt)) THEN
    			SET NEW.createdAt=CURDATE();
            END IF;
        END
$$
DELIMITER ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`transaction_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `transactions`
--
ALTER TABLE `transactions`
  MODIFY `transaction_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

-- Create comment table
CREATE TABLE comment ( `id` INT(100) NOT NULL , `booking_no` INT(100) NOT NULL , `user_id` INT(100) NOT NULL , `comment` VARCHAR(100) NOT NULL , `lab_name` VARCHAR(100) NOT NULL , `created_at` TIMESTAMP NOT NULL , `updated_at` VARCHAR(100) NOT NULL ,`facilityId` VARCHAR(100) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB;

-- Create lab requisition table
CREATE TABLE lab_requisition ( `id` INT(100) NOT NULL , `booking_no` INT(100) NOT NULL , `patient_id` INT(100) NOT NULL , `test` VARCHAR(100) NOT NULL , `result` VARCHAR(100) NOT NULL , `created_at` TIMESTAMP NOT NULL , `updated_at` VARCHAR(100) NOT NULL , `facilityId` VARCHAR(100) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB;

-- Create lab setup table
CREATE TABLE lab_setup ( `id` INT(100) NOT NULL , `head` VARCHAR(100) NOT NULL , `sub_head` VARCHAR(100) NOT NULL , `unit` VARCHAR(100) NOT NULL , `range_from` VARCHAR(100) NOT NULL , `range_to` VARCHAR(100) NOT NULL , `specimen` VARCHAR(100) NOT NULL , `price` INT(100) NOT NULL , `created_at` TIMESTAMP NOT NULL , `updated_at` VARCHAR(100) NOT NULL,`facilityId` VARCHAR(100) NOT NULL ) ENGINE = InnoDB;
ALTER TABLE `lab_setup` ADD PRIMARY KEY(`id`);
ALTER TABLE `lab_setup` CHANGE `id` `id` INT(100) NOT NULL AUTO_INCREMENT;

-- update the new lab services procedure
DROP PROCEDURE `add_new_lab_service`;
CREATE 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_range` VARCHAR(50), IN `test_to` VARCHAR(50), IN `spec` VARCHAR(50), IN `test_price` INT) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
    INSERT INTO lab_setup(`head`, `sub_head`, `unit`, `test`, `facilityId`,range_from,range_to,specimen,price) VALUES (labhead, labsubhead , unit, test, facId,test_from,test_to,spec,test_price );
END

-- create new lab head
CREATE PROCEDURE `add_new_lab_head`(IN `labhead` VARCHAR(50), IN `labsubhead` VARCHAR(50), IN `spec` VARCHAR(50), IN `facId` INT) 
BEGIN
    INSERT INTO lab_setup(`head`, `sub_head`, `facilityId`,specimen) VALUES (labhead, labsubhead , facId,spec );
END

-- allow null 
ALTER TABLE `lab_setup` CHANGE `id` `id` INT(100) NOT NULL AUTO_INCREMENT, CHANGE `head` `head` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL, CHANGE `sub_head` `sub_head` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL, CHANGE `unit` `unit` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL, CHANGE `range_from` `range_from` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL, CHANGE `range_to` `range_to` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL, CHANGE `specimen` `specimen` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL, CHANGE `price` `price` INT(100) NULL, CHANGE `created_at` `created_at` TIMESTAMP on update CURRENT_TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CHANGE `updated_at` `updated_at` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;



DROP PROCEDURE `new_service_from_deposit`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_service_from_deposit`(IN `facId` VARCHAR(100), IN `patientId` VARCHAR(50), IN `amt` INT, IN `source_head` VARCHAR(100), IN `dest_head` VARCHAR(100), IN `userId` VARCHAR(50), IN `receiptsn` VARCHAR(50), IN `receiptno` VARCHAR(50), IN `payment_mode` VARCHAR(20), IN `descr` VARCHAR(100)) BEGIN 
  DECLARE acc_balance int;
  SELECT balance INTO acc_balance FROM patientfileno WHERE accountNo = patientId;
  UPDATE patientfileno SET balance = acc_balance - amt WHERE accountNo = patientId;
  INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, modeOfPayment,patient_id,facilityId) 
    VALUES (descr,source_head,0,amt,userId,receiptsn,receiptno,payment_mode,patientId,facId);
  INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, modeOfPayment,patient_id,facilityId) 
    VALUES (descr,dest_head,amt,0,userId,receiptsn,receiptno,payment_mode,patientId,facId);
  
  COMMIT;
END

CREATE PROCEDURE `get_next_lab_id`(IN `facId` VARCHAR(50)) 
BEGIN
	SELECT IFNULL(max(id),0) + 1 AS labId from lab_requisition WHERE facilityId=facId; 

	COMMIT;
END

ALTER TABLE `patientrecords` ADD `status` VARCHAR(50) NOT NULL DEFAULT 'registered' AFTER `date_assigned`;
ALTER TABLE `lab_requisition` ADD `department` VARCHAR(50) NOT NULL AFTER `result`;
ALTER TABLE `lab_requisition` ADD `price` INT NOT NULL AFTER `test`;
ALTER TABLE `lab_requisition` CHANGE `booking_no` `booking_no` VARCHAR(100) NOT NULL;

CREATE PROCEDURE `get_lab_requisitions`(IN `facId` VARCHAR(50)) 
 SELECT concat(firstname, ' ', surname) as name, id, dateCreated FROM `patientrecords` 
    WHERE status = 'lab_requisition' AND facilityId=facId ORDER BY dateCreated DESC

ALTER TABLE `drugpurchaserecords` ADD `expiry_alert` INT(5) NOT NULL AFTER `expiry_date`;
ALTER TABLE `drugpurchaserecords` CHANGE `date` `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `drugs` ADD `qty_in` INT NOT NULL AFTER `unit_of_issue`, ADD `qty_out` INT NOT NULL AFTER `qty_in`, ADD `source` VARCHAR(100) NOT NULL AFTER `qty_out`;
ALTER TABLE `drugs` DROP `balance`;
ALTER TABLE `drugs`
  DROP `quantity`;
ALTER TABLE `drugs` CHANGE `purchased_by` `created_by` VARCHAR(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `drugpurchaserecords` ADD `status` VARCHAR(10) NOT NULL AFTER `balance`;
DROP PROCEDURE `get_list_of_drugs`; CREATE DEFINER=`root`@`localhost` PROCEDURE `get_list_of_drugs`(IN `facId` VARCHAR(50))
 BEGIN 
    SELECT * FROM drugs WHERE facilityId=facId ORDER BY created_at DESC; 
END

DROP PROCEDURE `get_purchase_records`;
CREATE PROCEDURE `get_purchase_records`(IN `facId` VARCHAR(50)) 
BEGIN
    SELECT * FROM drugpurchaserecords WHERE facilityId=facId ORDER BY created_at DESC;
END

DROP PROCEDURE `get_drugs_list`;
CREATE PROCEDURE `get_drugs_list`(IN `facId` VARCHAR(50)) 
BEGIN
    SELECT SUM(quantity) as quantity, drug from drugpurchaserecords WHERE facilityId=facId GROUP BY drug;
END

DROP PROCEDURE `get_pharm_total_stock`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_pharm_total_stock`(IN `fac_id` VARCHAR(50)) 
SELECT sum(quantity) AS totalStock FROM drugpurchaserecords WHERE facilityId = fac_id AND quantity > 0

ALTER TABLE `drugpurchaserecords` ADD `dispensary_balance` INT NOT NULL AFTER `balance`;
ALTER TABLE `drugpurchaserecords` ADD `drug_code` VARCHAR(50) NOT NULL FIRST;

DROP PROCEDURE `new_drug_purchase`;
CREATE PROCEDURE `new_drug_purchase`(IN `drug_name` VARCHAR(100), IN `cost` INT, IN `expiry` VARCHAR(20), IN `genericName` VARCHAR(100), IN `unit` VARCHAR(50), IN `reorder` INT, IN `mark_up` INT, IN `quantity_in` INT, IN `userId` VARCHAR(50), IN `supplierId` VARCHAR(50), IN `paymentStatus` VARCHAR(10), IN `p_date` VARCHAR(10), IN `receiptPic` VARCHAR(100), IN `expiryAlert` INT, IN `descr` VARCHAR(100), IN `source_head` VARCHAR(100), IN `amt` INT, IN `receiptsn` VARCHAR(50), IN `receiptno` VARCHAR(50), IN `payment_mode` VARCHAR(50), IN `dest_head` VARCHAR(100), IN `facId` VARCHAR(50), IN `d_code` VARCHAR(50), IN `selling_price` INT) BEGIN 
  DECLARE store_balance int;
  SELECT balance INTO store_balance FROM drugpurchaserecords WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;

  IF store_balance IS NULL THEN 

  INSERT INTO drugpurchaserecords (drug_code,drug,generic_name,unit_of_issue,reorder_level,cost_price,markUp,quantity,
                                    by_whom,supplier,receipt_no,payment_status,created_at,receipt_picture,expiry_date,
                                    expiry_alert,balance,facilityId)
    VALUES (d_code,drug_name,genericName,unit,reorder,cost,mark_up,quantity_in,userId,supplierId,receiptsn,paymentStatus,
            p_date,receiptPic,expiry,expiryAlert,quantity_in,facId);
            
  INSERT INTO drugs (drug_code,drug,expiry_date,price,unit_of_issue,qty_in,qty_out,source,supplier,receipt_no,created_by,genericName,facilityId) 
  VALUES (d_code,drug_name,expiry,selling_price,unit,quantity_in,0,'purchases',supplierId,receiptsn,userId,genericName,facId);

  INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo,
                            modeOfPayment,facilityId) 
        VALUES (descr,source_head,0,amt,userId,receiptsn,receiptno,payment_mode,facId);
  INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, 
                            modeOfPayment,facilityId) 
      VALUES (descr,dest_head,amt,0,userId,receiptsn,receiptno,payment_mode,facId);

  ELSE 
  UPDATE drugpurchaserecords set balance = store_balance + quantity_in, status='' 
    WHERE drug=drug_name AND cost_price=cost AND supplier=supplierId AND expiry_date=expiry;
    
    INSERT INTO drugs (drug_code,drug,expiry_date,price,unit_of_issue,qty_in,qty_out,source,supplier,receipt_no,created_by,genericName,facilityId) 
        VALUES (d_code,drug_name,expiry,selling_price,unit,quantity_in,0,'purchases',supplierId,receiptsn,userId,genericName,facId);

  
  INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo,
                            modeOfPayment,facilityId) 
        VALUES (descr,source_head,0,amt,userId,receiptsn,receiptno,payment_mode,facId);
  INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, 
                            modeOfPayment,facilityId) 
      VALUES (descr,dest_head,amt,0,userId,receiptsn,receiptno,payment_mode,facId);
      
 END IF;

  COMMIT;
END

CREATE PROCEDURE `move_items_to_dispensary`(IN `drug_name` VARCHAR(100), IN `cost` INT, IN `expiry` VARCHAR(20), IN `d_code` VARCHAR(50), IN `d_price` INT, IN `unit` INT, IN `in_qty` INT, IN `userId` VARCHAR(50), IN `generic` VARCHAR(100), IN `facId` VARCHAR(50)) BEGIN
  DECLARE store_qty int;
  DECLARE dispensary_qty int;
  
  select quantity, dispensary_balance into store_qty, dispensary_qty FROM drugpurchaserecords WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;
  
  UPDATE drugpurchaserecords SET quantity = store_qty - in_qty, dispensary_balance = dispensary_qty + in_qty WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;

  INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId)
        values (d_code,drug_name,expiry,d_price,unit,0,in_qty,'purchases',userId,generic,facId);
        
   INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId)
        values (d_code,drug_name,expiry,d_price,unit,in_qty,0,'dispensary',userId,generic,facId);

END


DROP PROCEDURE `new_drug_sale`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_drug_sale`(IN `drug_name` VARCHAR(100), IN `cost` INT, IN `expiry` VARCHAR(20), IN `genericName` VARCHAR(100), IN `unit` VARCHAR(50), IN `quantity_in` INT, IN `userId` VARCHAR(50), IN `supplierId` VARCHAR(50), IN `descr` VARCHAR(100), IN `source_head` VARCHAR(100), IN `amt` INT, IN `receiptsn` VARCHAR(50), IN `receiptno` VARCHAR(50), IN `payment_mode` VARCHAR(50), IN `dest_head` VARCHAR(100), IN `facId` VARCHAR(50), IN `d_code` VARCHAR(50), IN `selling_price` INT, IN `accNo` VARCHAR(50), IN `transactionType` VARCHAR(20)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN 
  DECLARE balance_dispensary int;
  DECLARE acc_balance int;
  
  IF transactionType='insta' THEN
  
  SELECT dispensary_balance INTO balance_dispensary FROM drugpurchaserecords WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;
  
  UPDATE drugpurchaserecords set dispensary_balance = balance_dispensary - quantity_in WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;
  
  INSERT INTO drugs (drug_code,drug,expiry_date,price,unit_of_issue,qty_in,qty_out,source,supplier,receipt_no,created_by,genericName,facilityId) VALUES (d_code,drug_name,expiry,selling_price,unit,quantity_in,0,'sold_items',supplierId,receiptsn,userId,genericName,facId);
  
  INSERT INTO drugs (drug_code,drug,expiry_date,price,unit_of_issue,qty_in,qty_out,source,supplier,receipt_no,created_by,genericName,facilityId) VALUES (d_code,drug_name,expiry,selling_price,unit,0,quantity_in,'dispensary',supplierId,receiptsn,userId,genericName,facId);


  INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo,
                            modeOfPayment,facilityId,client_acct) 
        VALUES (descr,source_head,0,amt,userId,receiptsn,receiptno,payment_mode,facId,accNo);
  INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, 
                            modeOfPayment,facilityId,client_acct) 
      VALUES (descr,dest_head,amt,0,userId,receiptsn,receiptno,payment_mode,facId,accNo);
      
      ELSEIF transactionType='deposit' THEN
      
      SELECT dispensary_balance INTO balance_dispensary FROM drugpurchaserecords WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;
  
  UPDATE drugpurchaserecords set dispensary_balance = balance_dispensary - quantity_in WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;
  
  INSERT INTO drugs (drug_code,drug,expiry_date,price,unit_of_issue,qty_in,qty_out,source,supplier,receipt_no,created_by,genericName,facilityId) VALUES (d_code,drug_name,expiry,selling_price,unit,quantity_in,0,'sold_items',supplierId,receiptsn,userId,genericName,facId);
  
  INSERT INTO drugs (drug_code,drug,expiry_date,price,unit_of_issue,qty_in,qty_out,source,supplier,receipt_no,created_by,genericName,facilityId) VALUES (d_code,drug_name,expiry,selling_price,unit,0,quantity_in,'dispensary',supplierId,receiptsn,userId,genericName,facId);


  INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo,
                            modeOfPayment,facilityId,client_acct) 
        VALUES (descr,source_head,0,amt,userId,receiptsn,receiptno,payment_mode,facId,accNo);
  INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, 
                            modeOfPayment,facilityId,client_acct) 
      VALUES (descr,dest_head,amt,0,userId,receiptsn,receiptno,payment_mode,facId,accNo);
      
	  SELECT balance into acc_balance from patientfileno WHERE accountNo=accNo AND facilityId=facId;
	  UPDATE patientfileno SET balance = acc_balance - amt WHERE accountNo = accNo AND facilityId=facId;
   END IF;
COMMIT;
END

CREATE VIEW overview AS 
    select drug AS drug,quantity AS `quantity_bought`,(quantity * cost_price) AS `amount_bought`,
    `balance` AS `quantity_in_store`,(`balance` * `cost_price`) AS `amount_in_store`,
    dispensary_balance AS `quantity_in_shelf`,(dispensary_balance * (cost_price + markUp)) AS `amount_in_shelf`,
    (`quantity` - (`balance` + `dispensary_balance`)) AS `quantity_sold`,
    ((`quantity` - (balance + dispensary_balance)) * `cost_price`) AS `amount_sold`,
    `created_at` AS `purchase_date`,`created_at` AS `sales_date`, facilityId from drugpurchaserecords

CREATE PROCEDURE `update_client_account`(IN `acc_no` VARCHAR(50), IN `amount` INT, IN `facId` VARCHAR(50)) 
BEGIN
    DECLARE acc_balance int;
    SELECT balance into acc_balance from patientfileno WHERE accountNo=acc_no AND facilityId=facId;
    UPDATE patientfileno SET balance = acc_balance - amount WHERE accountNo = acc_no AND facilityId=facId;


END

DROP PROCEDURE `get_purchase_records`;
CREATE PROCEDURE `get_purchase_records`(IN `facId` VARCHAR(50))  
BEGIN
    SELECT a.drug,a.cost_price,a.balance as quantity,a.expiry_date,a.created_at,b.supplier_name as supplier 
    FROM drugpurchaserecords a JOIN suppliersinfo b ON a.supplier=b.id 
    WHERE a.facilityId=facId ORDER BY created_at DESC;
END

DROP PROCEDURE `get_pharm_sales_summary`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_pharm_sales_summary`(IN `fac_id` VARCHAR(50), IN `dateFrom` DATE, IN `dateTo` DATE) SELECT  ifnull(SUM(credit), 0) totalAmount FROM transactions WHERE DATE(createdAt) BETWEEN date(dateFrom) AND date(dateTo) AND facilityId=fac_id AND credit!=0 AND acct='Drug Sales';

DROP PROCEDURE `get_pharm_total_stock`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_pharm_total_stock`(IN `fac_id` VARCHAR(50)) SELECT sum(balance) AS totalStock, sum(balance*cost_price) AS totalStockAmount, sum(dispensary_balance) as totalDisp, sum(dispensary_balance*(cost_price+markUp)) as totalDispAmount FROM drugpurchaserecords WHERE facilityId = fac_id

CREATE PROCEDURE `get_drugs_sold`(IN `facId` VARCHAR(50), IN `dateFrom` DATE, IN `dateTo` DATE) SELECT SUM(qty_out) AS quantity, drug FROM drugs WHERE date(created_at) BETWEEN date(dateFrom) AND date(dateTo) AND source='dispensary' AND facilityId=facId GROUP BY drug

DROP PROCEDURE `get_purchase_records`; CREATE DEFINER=`root`@`localhost` PROCEDURE `get_purchase_records`(IN `facId` VARCHAR(50)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN SELECT a.drug,a.cost_price,a.balance as quantity, dispensary_balance as dispensary_quantity ,a.expiry_date,a.created_at,b.supplier_name as supplier FROM drugpurchaserecords a JOIN suppliersinfo b ON a.supplier=b.id WHERE a.facilityId=facId ORDER BY created_at DESC; END

create view transaction_view2 as SELECT a.drug,a.qty_in,a.price, a.qty_in * (a.price - b.cost_price) as profit FROM drugs a JOIN drugpurchaserecords b ON a.drug_code=b.drug_code WHERE a.source='sold_items'

DROP PROCEDURE `move_items_to_dispensary`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `move_items_to_dispensary`(IN `drug_name` VARCHAR(100), IN `cost` INT, IN `expiry` VARCHAR(20), IN `d_code` VARCHAR(50), IN `d_price` INT, IN `unit` INT, IN `in_qty` INT, IN `userId` VARCHAR(50), IN `generic` VARCHAR(100), IN `facId` VARCHAR(50), IN `supplierId` VARCHAR(50), IN `itemSource` VARCHAR(10)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN
  DECLARE store_qty int;
  DECLARE dispensary_qty int;
  
  IF itemSource = 'store' THEN
  select balance, dispensary_balance into store_qty, dispensary_qty FROM drugpurchaserecords WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;
  
  UPDATE drugpurchaserecords SET balance = store_qty - in_qty, dispensary_balance = dispensary_qty + in_qty WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;

  INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId)
        values (d_code,drug_name,expiry,d_price,unit,0,in_qty,'purchases',userId,generic,facId);
        
   INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId)
        values (d_code,drug_name,expiry,d_price,unit,in_qty,0,'dispensary',userId,generic,facId);
        
        ELSE
        
        INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId)
        values (d_code,drug_name,expiry,d_price,unit,0,in_qty,'purchases',userId,generic,facId);
        
   INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId)
        values (d_code,drug_name,expiry,d_price,unit,in_qty,0,'dispensary',userId,generic,facId);
        
        end if;

END

ALTER TABLE `hospitals` ADD `haveStore` INT NOT NULL AFTER `type`;

DROP PROCEDURE `move_items_to_dispensary`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `move_items_to_dispensary`(IN `drug_name` VARCHAR(100), IN `cost` INT, IN `expiry` VARCHAR(20), IN `d_code` VARCHAR(50), IN `d_price` INT, IN `unit` INT, IN `in_qty` INT, IN `userId` VARCHAR(50), IN `generic` VARCHAR(100), IN `facId` VARCHAR(50), IN `supplierId` VARCHAR(50), IN `itemSource` VARCHAR(10), IN `mark_up` INT) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
  DECLARE store_qty int;
  DECLARE dispensary_qty int;
  
  IF itemSource = 'store' THEN
  select balance, dispensary_balance into store_qty, dispensary_qty FROM drugpurchaserecords WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;
  
  UPDATE drugpurchaserecords SET balance = store_qty - in_qty, dispensary_balance = dispensary_qty + in_qty WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;

  INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId,markup)
        values (d_code,drug_name,expiry,cost,unit,0,in_qty,'purchases',userId,generic,facId,mark_up);
        
   INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId,markup)
        values (d_code,drug_name,expiry,cost,unit,in_qty,0,'dispensary',userId,generic,facId,mark_up);
        
        ELSE
        
        INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId,markup)
        values (d_code,drug_name,expiry,cost,unit,0,in_qty,'purchases',userId,generic,facId,mark_up);
        
   INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId,markup)
        values (d_code,drug_name,expiry,cost,unit,in_qty,0,'dispensary',userId,generic,facId,mark_up);
        
        end if;

END

----------------------

DROP PROCEDURE `move_items_to_dispensary`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `move_items_to_dispensary`(IN `drug_name` VARCHAR(100), IN `cost` INT, IN `expiry` VARCHAR(20), IN `d_code` VARCHAR(50), IN `d_price` INT, IN `unit` INT, IN `in_qty` INT, IN `userId` VARCHAR(50), IN `generic` VARCHAR(100), IN `facId` VARCHAR(50), IN `supplierId` VARCHAR(50), IN `itemSource` VARCHAR(10), IN `mark_up` INT) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
  DECLARE store_qty int;
  DECLARE dispensary_qty int;
  
  IF itemSource = 'store' THEN
  select balance, dispensary_balance into store_qty, dispensary_qty FROM drugpurchaserecords WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;
  
  UPDATE drugpurchaserecords SET balance = store_qty - in_qty, dispensary_balance = dispensary_qty + in_qty WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;

  INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId,markup)
        values (d_code,drug_name,expiry,cost,unit,0,in_qty,'purchases',userId,generic,facId,mark_up);
        
   INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId,markup)
        values (d_code,drug_name,expiry,cost,unit,in_qty,0,'dispensary',userId,generic,facId,mark_up);
        
        ELSE
        select dispensary_balance into dispensary_qty FROM drugpurchaserecords WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;
        
        IF dispensary_qty IS NOT null THEN
  
  UPDATE drugpurchaserecords SET dispensary_balance = dispensary_qty + in_qty WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry AND supplier=supplierId;
  
   INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId,markup,supplier)
        values (d_code,drug_name,expiry,cost,unit,0,in_qty,'purchases',userId,generic,facId,mark_up,supplierId);
        
   INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId,markup,supplier)
        values (d_code,drug_name,expiry,cost,unit,in_qty,0,'dispensary',userId,generic,facId,mark_up,supplierId);
  
  else 
  
        INSERT into drugpurchaserecords (drug_code,drug,generic_name,unit_of_issue,cost_price,markUp,quantity, by_whom,supplier,receipt_no,payment_status,expiry_date,dispensary_balance,facilityId) VALUES (d_code,drug_name,genericName,unit,cost,mark_up,in_qty,userId,supplierId,'0','paid',expiry,in_qty,facId);
        
        INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId,markup,supplier)
        values (d_code,drug_name,expiry,cost,unit,0,in_qty,'purchases',userId,generic,facId,mark_up,supplierId);
        
   INSERT INTO drugs(drug_code,drug,expiry_date,price,
        unit_of_issue,
        qty_in,
        qty_out,
        source,
        created_by,
        genericName,
        facilityId,markup,supplier)
        values (d_code,drug_name,expiry,cost,unit,in_qty,0,'dispensary',userId,generic,facId,mark_up,supplierId);
        
        END IF;
        end if;

END

------------------

DELIMITER $$

CREATE TRIGGER after_edit
AFTER UPDATE
ON suppliersinfo FOR EACH ROW
BEGIN
    IF OLD.supplier_name <> new.supplier_name THEN
        INSERT INTO audit_trail(id,facilityId,	supplier_name,	date,	address,	phone,	code,new_supplier_name)
        VALUES(old.id, old.facilityId, old.supplier_name,old.date,old.address,old.phone,old.code,new.new_supplier_name);
    END IF;
END$$

DELIMITER ;

CREATE PROCEDURE `search_drug`(IN `drug` VARCHAR(50), IN `facId` VARCHAR(50), IN `t_source` VARCHAR(20)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER IF t_source = 'dispensary' THEN

SELECT drug,drug_code,generic_name,cost_price,cost_price+markUp as price,balance,dispensary_balance as d_balance,
        supplier,unit_of_issue,supplier, expiry_date,markUp as markup
        FROM drugpurchaserecords
        WHERE facilityId="${facilityId}" AND date(created_at) = (
          SELECT MIN(date(created_at)) FROM drugpurchaserecords WHERE drug like '%${drug}%' OR drug_code like '%${drug}%'
        ) AND (drug like '%${drug}%' OR drug_code like '%${drug}%');
        
  ELSE
  
  SELECT drug,drug_code,genericName,price as cost_price,price+markup as price, unit_of_issue,supplier, expiry_date,markup as markup
        FROM drugs
        WHERE facilityId="${facilityId}" AND date(created_at) = (
          SELECT MIN(date(created_at)) FROM drugs WHERE drug like '%${drug}%' OR drug_code like '%${drug}%'
        ) AND (drug like '%${drug}%' OR drug_code like '%${drug}%');
        
  END IF;

CREATE 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 `p_status` VARCHAR(50), IN `labno` VARCHAR(50), IN `phone` VARCHAR(13), IN `hemaStatus` VARCHAR(50), IN `pathoStatus` VARCHAR(50), IN `microStatus` VARCHAR(50), IN `radioStatus` VARCHAR(50)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN 

INSERT INTO patientrecords (patient_id,accountNo,beneficiaryNo,firstname,surname,other,Gender, DOB,email,facilityId,status,phoneNo,hematology,microbiology,chem_path,radiology) VALUES (p_id,acc_no,ben_no,first_name, last_name,other_name, p_gender,birth,mail,facId,p_status,phone,hemaStatus,microStatus,pathoStatus,radioStatus); 

INSERT INTO lab_numbers (facilityId,patient_id,lab_no,status) VALUES (facId,p_id,labno,p_status); 

END

ALTER TABLE `lab_requisition` ADD `status` VARCHAR(20) NOT NULL AFTER `department`;

CREATE PROCEDURE `new_test_result`(IN `facId` VARCHAR(50), IN `res` VARCHAR(50), IN `dept` VARCHAR(50), IN `tst` VARCHAR(50), IN `bk_no` VARCHAR(50)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER UPDATE lab_requisition SET result=res, status="Result" WHERE department=dept AND test=tst AND booking_no=bk_no AND facilityId=facId


CREATE PROCEDURE `new_comment`(IN `facId` VARCHAR(50), IN `labno` VARCHAR(50), IN `userId` VARCHAR(50), IN `t_comment` VARCHAR(4000), IN `labname` VARCHAR(50)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER 
BEGIN

DECLARE commentId int;
SELECT id INTO commentId FROM comment WHERE booking_no=labno AND user_id=userId AND lab_name=labname AND facilityId=facId;

IF commentId is null THEN

INSERT INTO comment (facilityId,booking_no,user_id,comment,lab_name)
        VALUES (facId, labno, userId, t_comment, labname);
   ELSE
   UPDATE comment SET comment=t_comment WHERE booking_no=labno AND user_id=userId AND lab_name=labname AND facilityId=facId;
 
 END IF;
 
 END;

ALTER TABLE `patientfileno` ADD `status` VARCHAR(50) NOT NULL DEFAULT 'pending' AFTER `balance`;

CREATE 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)) 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) VALUES (facId,s_name,s_name,gender,maritalStatus,dob,c_phone,c_email,p_occupation,c_address,patientId);

    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 `new_expense`;
CREATE PROCEDURE `new_expense`(IN `facId` VARCHAR(50), IN `descr` VARCHAR(100), IN `source_head` VARCHAR(100), IN `dest_head` VARCHAR(100), IN `receiptsn` VARCHAR(50), IN `receiptno` VARCHAR(50), IN `payment_mode` VARCHAR(50), IN `userId` VARCHAR(50), IN `amt` INT, IN `collected` VARCHAR(100)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN 
    INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, modeOfPayment,facilityId,client_acct) 
        VALUES (descr,source_head,0,amt,userId,receiptsn,receiptno,payment_mode,facId,collected);
    INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, modeOfPayment,facilityId,client_acct) 
      VALUES (descr,dest_head,amt,0,userId,receiptsn,receiptno,payment_mode,facId,collected);
    
    COMMIT;
  END


CREATE PROCEDURE `customer_deposit`(in in_acct int,  in amount_paid int,  in userId varchar(50),in in_receiptDateSN int,
                                    in in_receiptSN int, in in_description varchar(1000),in in_mode_of_payment varchar(20), 
                                    in patientId varchar(50), in in_facId varchar(50), in sourceAcct varchar(50))
BEGIN
	declare client_balance double;
	declare main_balance double;
	select balance into client_balance from patientfileno where accountNo=in_acct;

	set main_balance = client_balance+Amount_paid;

	-- # IF customer is a NEW client,
    -- # We create a new account for the customer and deposit the amount
	if client_balance is null then
    
    
		insert into patientfileno (accountNo,accName,balance,facilityId,status)
		values(in_acct,in_description,Amount_paid,facId,'approved');

       insert into account_entries (acct,dr,cr)
		values (in_acct,Amount_paid,0);
        
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
		values (in_description,sourceAcct,Amount_paid,0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
		values (in_description,'Payable',0,abs(Amount_paid) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);

	end if;
    
    -- # IF customer has an account, but the deposit amount nullifies his account balance
    -- # i.e. he's paying up his debt
	if  main_balance = 0 then
 
		update patientfileno set balance= main_balance  where patientfileno=in_acct;

		insert into account_entries (acct,dr,cr)
		values (in_acct,Amount_paid,0);
		 
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
		values (in_description,sourceAcct,Amount_paid,0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
		values (in_description,'Receivable',0,abs(client_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);

	-- # IF customer has an account, and his new account balance after the deposit is a top up
    -- # to his initial account balance
	elseif main_balance  > 0  then
 
		update patientfileno set balance= main_balance where accountNo=in_acct;

		insert into account_entries (acct,dr,cr)
		values (in_acct,Amount_paid,0);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
		values (in_description,sourceAcct,Amount_paid,0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);

		-- # IF customer's was owing us some money, then
		if  client_balance < 0 then

			insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
			values (in_description,'Receivable',0,abs(client_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);

			insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
			values (in_description,'Payable',0,abs(main_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);
            
		-- # ELSE IF customer had some money in his/her account
		elseif client_balance >= 0 then
			insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
			values (in_description,'Payable',0,abs(Amount_paid) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);
		end if;

	-- # IF the customer was owing some money and the deposit amount is not upto the amount owed
	elseif main_balance  < 0 then
		update patientfileno set balance= main_balance where accountNo=in_acct;

		insert into account_entries (acct,dr,cr)
		values (in_acct,Amount_paid,0);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
		values (in_description,sourceAcct,Amount_paid,0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
		values (in_description,'Receivable',0,abs(Amount_paid) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);

	end if;

END

CREATE PROCEDURE `select_admin_role`() SELECT * FROM `users` WHERE role = "Admin"

create view expenditure_view as select a.subhead,b.description,b.modeOfPayment,b.acct,b.createdAt,b.debit as amount,b.facilityId from account a join transactions b where a.head = b.acct AND a.subhead='Expenditure'

ALTER TABLE `comment` ADD `comment_type` VARCHAR(50) NOT NULL AFTER `comment`;