ALTER TABLE `lab_requisition` ADD `sample_collected_at` VARCHAR(50) NOT NULL AFTER `created_at`;

ALTER TABLE `patientfileno` ADD `phone` VARCHAR(15) NOT NULL AFTER `surname`;
ALTER TABLE `transactions` ADD `bankName` VARCHAR(50) NOT NULL AFTER `modeOfPayment`;
ALTER TABLE `transactions` CHANGE `bankName` `bankName` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `patientfileno` ADD `guarantor_name` VARCHAR(50) NULL DEFAULT NULL AFTER `approved_by`, ADD `guarantor_phoneNo` VARCHAR(20) NULL DEFAULT NULL AFTER `guarantor_name`, ADD `guarantor_address` VARCHAR(100) NULL DEFAULT NULL AFTER `guarantor_phoneNo`;
DROP PROCEDURE `customer_deposit`; CREATE DEFINER=`root`@`localhost` PROCEDURE `customer_deposit`(IN `in_acct` INT, IN `amount_paid` INT, IN `userId` VARCHAR(50), IN `in_receiptDateSN` VARCHAR(50), IN `in_receiptSN` VARCHAR(50), IN `in_description` VARCHAR(1000), IN `in_mode_of_payment` VARCHAR(20), IN `in_facId` VARCHAR(50), IN `sourceAcct` VARCHAR(50), IN `acc_name` VARCHAR(100), IN `acc_type` VARCHAR(20), IN `in_date` DATETIME, IN `in_address` VARCHAR(100), IN `in_contactPhone` VARCHAR(20), IN `in_email` VARCHAR(50), IN `in_website` VARCHAR(50), IN `in_payables_head` VARCHAR(50), IN `in_recievables_head` VARCHAR(50), IN `in_bankName` VARCHAR(50)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 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_balan[...]

DROP PROCEDURE `customer_deposit`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `customer_deposit`(IN `in_acct` INT, IN `amount_paid` INT, IN `userId` VARCHAR(50), IN `in_receiptDateSN` VARCHAR(50), IN `in_receiptSN` VARCHAR(50), IN `in_description` VARCHAR(1000), IN `in_mode_of_payment` VARCHAR(20), IN `in_facId` VARCHAR(50), IN `sourceAcct` VARCHAR(50), IN `acc_name` VARCHAR(100), IN `acc_type` VARCHAR(20), IN `in_date` DATETIME, IN `in_address` VARCHAR(100), IN `in_contactPhone` VARCHAR(20), IN `in_email` VARCHAR(50), IN `in_website` VARCHAR(50), IN `in_payables_head` VARCHAR(50), IN `in_recievables_head` VARCHAR(50), IN `in_bankName` VARCHAR(50)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 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 then
    
    
		insert into patientfileno (accountNo,accName,balance,facilityId,status,accountType,contactAddress,contactPhone,contactEmail,contactWebsite)
		values(in_acct,acc_name,Amount_paid,in_facId,'approved',acc_type,in_address,in_contactPhone,in_email,in_website);

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

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

	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,facilityId,createdAt)
		values (in_acct,Amount_paid,0,in_receiptDateSN,in_facId,in_date);
		 
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,bankName,enteredBy,client_acct,patient_id,facilityId,createdAt)
		values (in_description,sourceAcct,Amount_paid,0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,in_bankName,userId,in_acct,in_acct,in_facId,in_date);

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

	-- # 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,facilityId,createdAt)
		values (in_acct,Amount_paid,0,in_receiptDateSN,in_facId,in_date);

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

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

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

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

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

END








