ALTER TABLE `drugs` ADD `client_acct` VARCHAR(50) NOT NULL AFTER `markup`;


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 ;
  
  UPDATE drugpurchaserecords set dispensary_balance = balance_dispensary - quantity_in WHERE drug=drug_name AND cost_price=cost 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,client_acct) VALUES (d_code,drug_name,expiry,selling_price,unit,quantity_in,0,'sold_items',supplierId,receiptsn,userId,genericName,facId,accNo);
  
  INSERT INTO drugs (drug_code,drug,expiry_date,price,unit_of_issue,qty_in,qty_out,source,supplier,receipt_no,created_by,genericName,facilityId,client_acct) VALUES (d_code,drug_name,expiry,selling_price,unit,0,quantity_in,'dispensary',supplierId,receiptsn,userId,genericName,facId,accNo);


 -- 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;
  
  UPDATE drugpurchaserecords set dispensary_balance = balance_dispensary - quantity_in WHERE drug=drug_name AND cost_price=cost 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,client_acct) VALUES (d_code,drug_name,expiry,selling_price,unit,quantity_in,0,'sold_items',supplierId,receiptsn,userId,genericName,facId,accNo);
  
  INSERT INTO drugs (drug_code,drug,expiry_date,price,unit_of_issue,qty_in,qty_out,source,supplier,receipt_no,created_by,genericName,facilityId,client_acct) VALUES (d_code,drug_name,expiry,selling_price,unit,0,quantity_in,'dispensary',supplierId,receiptsn,userId,genericName,facId,accNo);


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


DROP PROCEDURE `record_returned_drugs`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `record_returned_drugs`(IN `quantity_in` INT, IN `receiptsn` VARCHAR(50), IN `d_code` VARCHAR(50), IN `cost` INT, IN `expiry` DATE, IN `facId` VARCHAR(50), IN `userId` VARCHAR(50), IN `drug_name` VARCHAR(100), IN `selling_price` INT, IN `unit` INT, IN `supplierId` INT, IN `generic` INT, IN `in_accNo` INT) NOT DETERMINISTIC NO 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+markUp=cost AND expiry_date=expiry ;
  
  UPDATE drugpurchaserecords set dispensary_balance = balance_dispensary + quantity_in WHERE drug=drug_name AND cost_price+markUp=cost 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,client_acct) VALUES (d_code,drug_name,expiry,selling_price,unit,0,quantity_in,'sold_items',supplierId,receiptsn,userId,generic,facId,in_accNo);
  
  INSERT INTO drugs (drug_code,drug,expiry_date,price,unit_of_issue,qty_in,qty_out,source,supplier,receipt_no,created_by,genericName,facilityId,client_acct) VALUES (d_code,drug_name,expiry,selling_price,unit,quantity_in,0,'returned_drugs',supplierId,receiptsn,userId,generic,facId,in_accNo);

END