------------------------------------------ get_client_stmt

BEGIN
	SELECT acct, description, dr as debit, cr as credit, reference_no, createdAt from account_entries WHERE 
    acct=clientId AND
     date(createdAt) BETWEEN date(dateFrom) AND date(dateTo) AND facilityId=facId;
END

------------------------------------------
ALTER TABLE `account_entries` ADD `description` VARCHAR(100) NOT NULL AFTER `cr`;

------------------------------------------ service_transaction
BEGIN
	declare client_balance int;
	declare main_balance int;
    declare receivable int;
    DECLARE instant_balance int;
   
--    declare service_description varchar(200);
	select balance into client_balance  from patientfileno where accountNo=in_acct AND facilityId=in_facId;
    
	
	
    set main_balance = client_balance-service_amount;
    set receivable  = service_amount-client_balance;
    set instant_balance = client_balance + service_amount;
    
    IF txnType = 'insta' THEN
    
    	update patientfileno set balance= instant_balance where accountNo=in_acct AND facilityId=in_facId;
        
        insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt)
            values (in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date);
    
    	insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,in_service_head,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);

insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,'500011',0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);


            insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,sourceAcct,abs(service_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);
    
    ELSE

        -- IF the service amount is equal to the customer account balance (initial deposit amount)
        -- IF after the service amount is deducted, the client is still a debtor,

        if main_balance  > 0  then

            update patientfileno set balance= main_balance where accountNo=in_acct AND facilityId=in_facId;

            insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt)
            values (in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date);

            insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,in_service_head,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);

insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,'500011',0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);

            insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,in_payables_head,abs(service_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);

        elseif main_balance < 0 then
            update patientfileno set balance= main_balance where accountNo=in_acct AND facilityId=in_facId;

            insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt)
            values (in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date);

            insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,in_service_head,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);

insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,'500011',0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);

            if client_balance = 0 then
            insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,in_recievables_head,abs(service_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);

            -- IF client had some amount in their deposit
        elseif client_balance > 0  then

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

            insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,in_recievables_head,abs(receivable),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);
        elseif client_balance < 0  then

            insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,in_recievables_head,abs(service_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);

            end if;
        elseif  main_balance = 0 then

            update patientfileno set balance= main_balance  where accountNo=in_acct AND facilityId=in_facId;

            insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt)
            values (in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date);

            insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,in_service_head,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);

insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt)
            values (in_description,'500011',0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date);

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

        -- IF the service amount is less the customer account balance
        end if;
    END IF;
END



--------------------------------- customer deposit
BEGIN
	declare client_balance double;
	declare main_balance double;
    #DECLARE remaining_balance double;
	select balance into client_balance from patientfileno where accountNo=in_acct AND facilityId=in_facId;

	#SET remaining_balance = Amount_paid - client_balance;
	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 and Amount_paid < 0 then
  
        	insert into patientfileno (accountNo,accName,balance,facilityId,status,accountType,contactAddress,contactPhone,contactEmail,contactWebsite,guarantor_name,
               guarantor_address,guarantor_phone)
		values(in_acct,acc_name,Amount_paid,in_facId,'approved',acc_type,in_address,in_phone,in_email,in_website,in_guarantor_name,
               in_guarantor_address,in_guarantor_phone);

       insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt)
		values (in_acct,abs(Amount_paid),0,in_receiptDateSN,in_description,in_facId,in_date);
        
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,
                                  patient_id,facilityId,createdAt,bank_name)
		values (in_description,sourceAcct,abs(Amount_paid),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,
                                  patient_id,facilityId,createdAt,bank_name)
		values (in_description,in_recievables_head,0,abs(Amount_paid) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);
            
            
            
            
            
            ELSEif client_balance is null then
    
		insert into patientfileno (accountNo,accName,balance,facilityId,status,accountType,contactAddress,contactPhone,contactEmail,contactWebsite,guarantor_name,
               guarantor_address,guarantor_phone)
		values(in_acct,acc_name,Amount_paid,in_facId,'approved',acc_type,in_address,in_phone,in_email,in_website,in_guarantor_name,
               in_guarantor_address,in_guarantor_phone);

       insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt)
		values (in_acct,abs(Amount_paid),0,in_receiptDateSN,in_description,in_facId,in_date);
        
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,sourceAcct,abs(Amount_paid),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,in_payables_head,0,abs(Amount_paid) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

	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 accountNo=in_acct AND facilityId=in_facId;

		insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt)
		values (in_acct,abs(Amount_paid),0,in_receiptDateSN,in_description,in_facId,in_date);
		 
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment, enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,sourceAcct,abs(Amount_paid),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment, enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,in_recievables_head,0,abs(client_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

	-- # 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 AND facilityId=in_facId;

		insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt)
		values (in_acct,Amount_paid,0,in_receiptDateSN,in_description,in_facId,in_date);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,sourceAcct,abs(Amount_paid),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

		-- # 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,facilityId,createdAt,bank_name)
			values (in_description,in_recievables_head,0,abs(client_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                    userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

			insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                      client_acct,patient_id,facilityId,createdAt,bank_name)
			values (in_description,in_payables_head,0,abs(main_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                    userId,in_acct,in_acct,in_facId,in_date,in_bank_name);
            
		-- # 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,facilityId,createdAt,bank_name)
			values (in_description,in_payables_head,0,abs(Amount_paid) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                    userId,in_acct,in_acct,in_facId,in_date,in_bank_name);
		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 AND facilityId=in_facId;

		insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt)
		values (in_acct,abs(Amount_paid),0,in_receiptDateSN,in_description,in_facId,in_date);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,
                                  modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,sourceAcct,abs(Amount_paid),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);
        
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,
                                  enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,in_recievables_head,0,abs(Amount_paid) ,in_receiptDateSN,
                in_receiptSN,in_mode_of_payment,userId,in_acct,in_acct,in_facId,in_date,in_bank_name);
        
        
	end if;

END


----------------------------------------------- patientfileno
ALTER TABLE `patientfileno`  ADD `guarantor_name` VARCHAR(50) NULL  AFTER `status`,  ADD `guarantor_address` VARCHAR(100) NULL  AFTER `guarantor_name`,  ADD `guarantor_phone` VARCHAR(20) NULL  AFTER `guarantor_address`;

----------------------------------------------- transactions
ALTER TABLE `transactions` ADD `bank_name` VARCHAR(50) NULL AFTER `modeOfPayment`;

----------------------------------------------- drugpurchaserecords
ALTER TABLE `drugpurchaserecords` ADD `shift` VARCHAR(20) NULL AFTER `dispensary_balance`;
ALTER TABLE `drugs` ADD `shift` VARCHAR(20) NULL AFTER `client_acct`;

------------------------------------------------  account
ALTER TABLE `account` ADD `price` INT NULL AFTER `description`;

------------------------------------------------- get_acc_chart()
CREATE PROCEDURE `get_acc_chart`(IN `facId` VARCHAR(50)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
	SELECT head as title,subhead,description,price FROM account WHERE facilityId=facId;
END

----------------------------------------------- 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, IN `in_receiptno` VARCHAR(50), IN `in_shift` VARCHAR(20))
BEGIN
 DECLARE store_qty int;
 DECLARE dispensary_qty int;
 DECLARE drug_id INT;
 
 SELECT id INTO drug_id FROM druglist WHERE name=drug_name AND generic_name=generic;
 IF drug_id IS null THEN
 INSERT INTO druglist (name,generic_name) VALUES (drug_name,generic);
 end IF;
 
 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,receipt_no,shift)
       values (d_code,drug_name,expiry,cost,unit,0,in_qty,'purchases',userId,generic,facId,mark_up,in_receiptno,in_shift);
       
  INSERT INTO drugs(drug_code,drug,expiry_date,price,
       unit_of_issue,
       qty_in,
       qty_out,
       source,
       created_by,
       genericName,
       facilityId,markup,receipt_no,shift)
       values (d_code,drug_name,expiry,cost,unit,in_qty,0,'dispensary',userId,generic,facId,mark_up,in_receiptno,in_shift);
       
       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 balance=dispensary_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,supplier,receipt_no,shift)
       values (d_code,drug_name,expiry,cost,unit,0,in_qty,'purchases',
               userId,generic,facId,mark_up,supplierId,in_receiptno,in_shift);
       
  INSERT INTO drugs(drug_code,drug,expiry_date,price,
       unit_of_issue,
       qty_in,
       qty_out,
       source,
       created_by,
       genericName,
       facilityId,markup,supplier,receipt_no,shift)
       values (d_code,drug_name,expiry,cost,unit,in_qty,0,'dispensary',
               userId,generic,facId,mark_up,supplierId,in_receiptno, in_shift);
 
 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,generic,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,receipt_no,shift)
       values (d_code,drug_name,expiry,cost,unit,0,in_qty,'purchases',userId,
               generic,facId,mark_up,supplierId,in_receiptno,in_shift);
       
  INSERT INTO drugs(drug_code,drug,expiry_date,price,
       unit_of_issue,
       qty_in,
       qty_out,
       source,
       created_by,
       genericName,
       facilityId,markup,supplier,receipt_no,shift)
       values (d_code,drug_name,expiry,cost,unit,in_qty,0,'dispensary',userId,
               generic,facId,mark_up,supplierId,in_receiptno,in_shift);
       
       END IF;
       end if;

END

------------------------------------------------- new_acc_head
CREATE PROCEDURE `new_acc_head`(IN `head` VARCHAR(100), IN `subhead` VARCHAR(100), IN `description` VARCHAR(200), IN `balance` INT(11), IN `facId` VARCHAR(50), IN `in_price` INT) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
    INSERT INTO account(head, subhead, description, balance, facilityId,price) VALUES (description,subhead,head,balance, facId,in_price);
END
