ALTER TABLE `labservices` CHANGE `speciment` `specimen` VARCHAR(100);
ALTER TABLE `labservices` ADD `price` INT(12) NOT NULL AFTER `test_range`;
ALTER TABLE `labservices` ADD `range_from` VARCHAR(100) NOT NULL AFTER `value`;
ALTER TABLE `labservices` ADD `range_to` VARCHAR(100) NOT NULL AFTER `range_from`;

CREATE PROCEDURE `service_transaction`(in in_description varchar(1000),in in_acct int,in service_amount int,in in_receiptDateSN int,
										in in_receiptSN int, in in_mode_of_payment varchar(20), in patientId varchar(50), 
                                    	in in_facId varchar(50), in sourceAcct varchar(50))

BEGIN
	declare client_balance int;
	declare main_balance int;
    declare receivable int;
   
--    declare service_description varchar(200);
	select balance into client_balance  from patientfileno where accountNo=in_acct;
    
	
	
    set main_balance = client_balance-service_amount;
    set receivable  = service_amount-client_balance;
      
    -- #set service_description = concat(in_description,' for ',in_acct);

	-- #if client_balance is null then
	-- #insert into customer_table (Acct,name,balance)
	-- #values(in_acct,in_description,Amount_paid);

	-- #insert into transactions1 (description,acct,debit,credit,receiptDateSN)
	-- #values (in_description,'Cash',Amount_paid,0,in_receiptDateSN);

	-- #insert into transactions1 (description,acct,debit,credit,receiptDateSN)
	-- #values (in_description,'Payable',0,abs(Amount_paid) ,in_receiptDateSN);

	-- #end if;

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

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

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
		values (in_description,in_acct,0,service_amount,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',abs(service_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);

	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,service_amount,0);

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

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

		-- 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)
		values (in_description,'payable',abs(client_balance),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',abs(receivable),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);
	elseif client_balance < 0  then
            
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
		values (in_description,'payable',abs(client_balance),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',abs(main_balance),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);
	
		end if;
	elseif  main_balance = 0 then
 
		update patientfileno set balance= main_balance  where acct=in_acct;

		insert into account_entries (acct,dr,cr)
		values (in_acct,0,service_amount);
		 
		-- #insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
		-- #values (in_description,'Cash',service_amount,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,in_acct,0,service_amount,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',abs(client_balance),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId);

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





-- Supplier Transaction

CREATE PROCEDURE `purchase_transaction`(IN `in_description` VARCHAR(1000), IN `in_acct` INT, IN `goods_amount` INT, 
	IN `in_receiptDateSN` VARCHAR(50), IN `in_receiptSN` VARCHAR(50), IN `in_mode_of_payment` VARCHAR(20), 
	IN `patientId` VARCHAR(50), IN `in_facId` VARCHAR(50), IN `sourceAcct` VARCHAR(50), IN `userId` VARCHAR(50), 
	IN `in_goods_head` VARCHAR(50), IN `txnType` VARCHAR(10))
BEGIN

	declare supplier_balance int;
	declare main_balance int;
	declare receivable int;
	DECLARE instant_balance int;
 
--    declare service_description varchar(200);
	select balance into supplier_balance  from suppliersinfo where id=in_acct AND facilityId=in_facId;

	set main_balance = supplier_balance+goods_amount;
	set receivable  = goods_amount-supplier_balance;
	set instant_balance = supplier_balance + goods_amount;
   
   IF paymentStatus = 'Full Payment' THEN
   
    -- update patientfileno set balance= instant_balance where accountNo=in_acct AND facilityId=in_facId;
       
       insert into supplier_entries (acct,dr,cr,reference_no,facilityId)
           values (in_acct,0,goods_amount,in_receiptDateSN,in_facId);
   
    insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId)
           values (in_description,in_goods_head,0,goods_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId);

           insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId)
           values (in_description,sourceAcct,abs(goods_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId);
   
   	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 suppliersinfo set balance= main_balance where id=in_acct AND facilityId=in_facId;

           insert into supplier_entries (acct,dr,cr,reference_no,facilityId)
           values (in_acct,0,goods_amount,in_receiptDateSN,in_facId);

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

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

       	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)
           values (in_acct,0,goods_amount,in_receiptDateSN,in_facId);

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

           if supplier_balance = 0 then
           insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId)
           values (in_description,'Receivable',abs(goods_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId);

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

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

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

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

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

           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,facilityId)
           values (in_acct,0,goods_amount,in_receiptDateSN,in_facId);

           -- #insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id)
           -- #values (in_description,'Cash',goods_amount,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,facilityId)
           values (in_description,in_goods_head,0,goods_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId);

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

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