-- Accounting
-- Patient Deposit
CREATE PROCEDURE `deposit`(IN `amt` INT, IN `patientId` VARCHAR(50), IN `descr` VARCHAR(100), 
                            IN `source_head` VARCHAR(100), IN `userId` VARCHAR(50), IN `receiptsn` VARCHAR(50), 
                            IN `receiptno` VARCHAR(50), IN `payment_mode` VARCHAR(50), IN `dest_head` VARCHAR(100), 
                            IN `facId` VARCHAR(100)) 
  BEGIN 
    DECLARE acc_balance int;
    SELECT balance INTO acc_balance FROM patientfileno WHERE accountNo = patientId AND facilityId=facId;
    UPDATE patientfileno SET balance = acc_balance + amt WHERE accountNo = patientId AND facilityId=facId;
    INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, modeOfPayment,client_acct,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,client_acct,facilityId) 
      VALUES (descr,dest_head,amt,0,userId,receiptsn,receiptno,payment_mode,patientId,facId);
    
    COMMIT;
  END
  -- BEGIN 
  --   DECLARE acc_balance int;
  --   SELECT balance INTO acc_balance FROM patientfileno WHERE accountNo = patientId AND facilityId=facId;
  --   UPDATE patientfileno SET balance = acc_balance + amt WHERE accountNo = patientId AND facilityId=facId;
  --   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;

-- New Service/Sales (pay from deposit)
CREATE 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))
  BEGIN 
    DECLARE acc_balance int;
    SELECT balance INTO acc_balance FROM patientfileno WHERE accountNo = patientId AND facilityId=facId;
    UPDATE patientfileno SET balance = acc_balance - amt WHERE accountNo = patientId AND facilityId=facId;
    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

-- Instant payment
CREATE PROCEDURE `new_service_instant_payment`(IN `facId` VARCHAR(50), IN `patientId` 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 `accNo` VARCHAR(50))
 BEGIN 
  INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, modeOfPayment,patient_id,client_acct,facilityId) 
      VALUES (descr,source_head,0,amt,userId,receiptsn,receiptno,payment_mode,patientId,accNo,facId);
    INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, modeOfPayment,patient_id,client_acct,facilityId) 
      VALUES (descr,dest_head,amt,0,userId,receiptsn,receiptno,payment_mode,patientId,accNo,facId);
  
  COMMIT;
END

-- Record Expenses
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))
  BEGIN 
    INSERT INTO transactions (description, acct, debit, credit, enteredBy, receiptDateSN, receiptNo, modeOfPayment,facilityId,client_acct) 
        VALUES (descr,source_head,amt,0,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,0,amt,userId,receiptsn,receiptno,payment_mode,facId,collected);
    
    COMMIT;
  END


-- Pharmacy
-- Store Management
-- New Intake
BEGIN 
  DECLARE store_balance int;
  SELECT balance INTO store_balance FROM drugs WHERE drug='' AND cost_price='' AND expiry_date='' ;

  IF store_balance IS NULL THEN 
  INSERT INTO drugs () VALUES ();
  INSERT INTO drugpurchaserecords () VALUES ();
  INSERT INTO drugpurchaserecords () VALUES ();
  INSERT INTO transactions () VALUES ()
  INSERT INTO transactions () VALUES ()

  ELSE 
  UPDATE drugs SET balance = store_balance + 2000 WHERE drug='' AND cost_price='' AND expiry_date='' ;
  INSERT INTO drugpurchaserecords () VALUES ();
  INSERT INTO drugpurchaserecords () VALUES ();
  INSERT INTO transactions () VALUES ()
  INSERT INTO transactions () VALUES ()
  
  END IF;

  COMMIT;
END;


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 `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)) 
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,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 (drug_name,genericName,unit,reorder,cost,mark_up,quantity_in,userId,supplierId,receiptsn,paymentStatus,
            date,receiptPic,expiry,expiryAlert,quantity_in,facId);
            
  INSERT INTO drugs (drug,expiry_date,price,unit_of_issue,qty_in,qty_out,source,supplier,receipt_no,created_by,genericName,facilityId) 
    VALUES (drug_name,expiry,cost,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,expiry_date,price,unit_of_issue,qty_in,qty_out,source,supplier,receipt_no,created_by,genericName,facilityId) 
    VALUES (drug_name,expiry,cost,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

-- BEGIN 
--   DECLARE store_balance int;
--   SELECT balance INTO store_balance FROM drugs WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry ;

--   IF store_balance IS NULL THEN 
--   INSERT INTO drugs (drug,expiry_date,quantity,price,unit_of_issue,balance,date_brought,reorder_level,supplier,
--                      receipt_no,purchased_by,payment_status,receipt_image,expiryAlert,genericName,facilityId) 
--   	VALUES ();
--   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);

--   ELSE 
--   UPDATE drugs SET balance = store_balance + amt WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry ;
--   INSERT INTO drugpurchaserecords (drug,generic_name,unit_of_issue,reorder_level,cost_price,markUp,selling_price,quantity,by_whom,
--                                    supplier,cost,receipt_no,payment_status,date,receipt_picture,expiry_date,balance,facilityId) VALUES ();
--   INSERT INTO drugpurchaserecords () VALUES ();
  
--   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);
      
  
--   END IF;

--   COMMIT;
-- END;

-- New Service/Sales (pay from deposit)
-- CREATE PROCEDURE `new_lab_requisition_txn`(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))
--   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


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 AND supplier=supplierName ;

  IF store_balance IS NULL THEN 
  -- INSERT INTO drugs (drug,expiry_date,quantity,price,unit_of_issue,balance,date_brought,reorder_level,supplier,
  --                   receipt_no,purchased_by,payment_status,receipt_image,expiryAlert,genericName,facilityId) 
  --	VALUES ();

  INSERT INTO drugpurchaserecords (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 (drug_name,genericName,unit,reorder,cost,mark_up,quantity_in,userId,supplierName,receiptNo,paymentStatus,
            date,receiptPic,expiry,expiryAlert,quantity,facId);

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

  ELSE 
  -- UPDATE drugs SET balance = store_balance + amt WHERE drug=drug_name AND cost_price=cost AND expiry_date=expiry ;
  UPDATE drugpurchaserecords set balance = store_balance + quantity_in, status='' 
    WHERE drug=drug_name AND cost_price=cost AND supplier=supplierName AND expiry_date=expiry;

  -- INSERT INTO drugpurchaserecords (drug,generic_name,unit_of_issue,reorder_level,cost_price,markUp,selling_price,quantity,by_whom,
  --                                  supplier,cost,receipt_no,payment_status,date,receipt_picture,expiry_date,balance,facilityId) VALUES ();
  -- INSERT INTO drugpurchaserecords () VALUES ();
  
  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);
      
  END IF;

  COMMIT;
END;


select a.createdAt AS `date`,a.acct AS `source`,a.debit AS `dr`,(((a.debit / b.qty_out)- c.cost_price) * b.qty_out) AS `profit`,b.qty_out as quantity,a.description from transactions a JOIN drugs b ON a.receiptDateSN=b.receipt_no JOIN drugpurchaserecords c on b.drug_code = c.drug_code where (a.debit <> 0) AND (b.qty_out<>0)