1 Topic by boxygen (edited by boxygen 03/01/2018 05:57:54 pm)

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 650

Topic: Bug Found and Fixed in rep101.php

We can't restrict any user to record a BankPayment to a customer through BankPayment Entry. This happens in practical environment when same party works as a customer or supplier or in any other capacity.

Modifying below statements From Line 35 onwards will fix this issue.

$sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE." OR (t.type IN (".ST_JOURNAL." , ".ST_BANKPAYMENT.") AND t.ov_amount>0),
             -abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges,";

$sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type IN (".ST_JOURNAL." , ".ST_BANKPAYMENT.") AND t.ov_amount>0),
             abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits,";

Please verify it

www.boxygen.pk

boxygen's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Bug Found and Fixed in rep101.php

If the same party is both a customer and a supplier, then in standard FA, we treat them as Customer and Supplier separately.

@joe/@itronics: is this okay to implement?

apmuthu's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Bug Found and Fixed in rep101.php

Hello Guys.

No we are not combining Customers and Suppliers even if they are the same legal entity.

However I understand @boxygens post. It will arise if the following condition has happened:

1. Sales Invoice is sent for products.
2. Sales Invoice is paid by Customer.
3. Customer is returning Items to us for some reason and the items are ok.
4. Credit Note is sent to Customer.
5. Bank Payment is sent to Customer as Compensation for returned Items.

This no. 5 has to be taken care of in the system.

So we must fix this.
I guess we have a similar condition on the Supplier side.

Joe

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Bug Found and Fixed in rep101.php

Hello again, Guys.

I have now produced the no 1-5 in the above example.
What I needed is to add a
6. Go into Allocations, because the Credit Note has taken over the former allocation and the original Payment is left with no allocation. Use this original Payment for allocating your Bank Payment to the Customer and everything is correct.

Instead of doing the no. 5 Bank Payment to pay the customer as compensation, you can make an agreement with the customer to let the Customer Payment be there waiting for allocation of a future Sales Invoice.

We don't have to change anything in the SQL sentences in rep101. Everything is ok.

@boxygen: You should NOT change anything in the REP101 SQL.

/Joe

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Bug Found and Fixed in rep101.php

Will not the original Sales Invoice be left orphaned and remaining payable even though a Credit Note is monetarily adjusted in the GL?

In your "6", do we allocate the original Bank Payment to the Credit Note issued to the customer - is that possible in Allocations?

apmuthu's Website

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 650

Re: Bug Found and Fixed in rep101.php

@Joe, you are right. This is the most professional way to do it.

But I have many clients who are not professional accountants.

Since in FA, we have an option to make a BankPayment to customer so either this option be disabled to stop users to exercise it OR

we have to make necessary adjustments to make report show the actual balance.

If we don't commit the suggested changes to rep101.php and other such reports the Opening Balance is not showing the correct value.

www.boxygen.pk

boxygen's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Bug Found and Fixed in rep101.php

@apmuthu.
As I wrote, if a credit note is made after a payment has been used for allocation the credit note is taken over the allocation, leaving the original Customer Payment open for other allocations.

@boxygen
A Bank Payment will show up in the Customer Balance. No problems with that. If the Bank Payment is not allocated with an existing Customer Payment, you can see that in the rep101.

Joe

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 650

Re: Bug Found and Fixed in rep101.php

@joe
You are absolutely right, but let me show you the situation with real client's data.

Login Here
id: admin
pass: Pakistan1947

Open customer balances report from 1st Jan 2017 till date
Select customer QY009-BABJEE TRADERS
Set Show Balance = Yes
Note down the Ending Balance = 811,922

Now set the date from 23 NOv 2017 till Date with same parameters.
Now the Ending balance will be = -2,609,707

This is because the Opening Balance is wrong i.e. -2,286,233
that is supposed to be 1,135,467

Because you can see 5 Bank Payment Entries that are made to this customer by client.

BP319, BP426, BP747, BP748, BP749.

My point is that since we can't stop customer to make a Bank Payment to a Customer because option is there in FA.

So we need to adjust our report that shall contain such silly accounting mistakes.

I hope my point is clear now.

www.boxygen.pk

boxygen's Website

  • Alaa
  • Alaa
  • Senior Member
  • Offline
  • From: Jordan
  • Registered: 03/27/2015
  • Posts: 156

Re: Bug Found and Fixed in rep101.php

boxygen wrote:

@joe
You are absolutely right, but let me show you the situation with real client's data.

Login Here
id: admin
pass: Pakistan1947

Open customer balances report from 1st Jan 2017 till date
Select customer QY009-BABJEE TRADERS
Set Show Balance = Yes
Note down the Ending Balance = 811,922

Now set the date from 23 NOv 2017 till Date with same parameters.
Now the Ending balance will be = -2,609,707

This is because the Opening Balance is wrong i.e. -2,286,233
that is supposed to be 1,135,467

Because you can see 5 Bank Payment Entries that are made to this customer by client.

BP319, BP426, BP747, BP748, BP749.

My point is that since we can't stop customer to make a Bank Payment to a Customer because option is there in FA.

So we need to adjust our report that shall contain such silly accounting mistakes.

I hope my point is clear now.


nice catch..
i also think that the same bug causes the same mistake in report 201.
i been facing it and could never figure out the problem..
it seems this happens when there is opening balance and the bug causes the transactions to appear in the wrong side and the opening balance to be minus.

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Bug Found and Fixed in rep101.php

You are right Guys. I will look into this.

/Joe

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Bug Found and Fixed in rep101.php

@boxygen
I am terrible sorry that I did not accept your change in the SQL sentences in rep110. It turns out that you were right in changing it.
The 3.rd SQL sentence regarding the open balance allocation, it should also be changed accordingly.
The 4.th SQL sentence seems to be ok as is.

I am attaching the corrected file, rep101.php1. Please download and rename it to rep101.php and update your reporting folder.

Please test it and tell me if this is ok. I will then commit it and change rep201.php in the same way.

Joe

Post's attachments

rep101.php1 9.8 kb, 6 downloads since 2018-03-03 

You don't have the permssions to download the attachments of this post.

12 Reply by apmuthu (edited by apmuthu 03/04/2018 12:21:27 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Bug Found and Fixed in rep101.php

Sourceforge Git web UI seems to be down now. Works now.

apmuthu's Website

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 650

Re: Bug Found and Fixed in rep101.php

@joe, I think you have check the supplier side also.

www.boxygen.pk

boxygen's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Bug Found and Fixed in rep101.php

@boxygen
I have just tested to rep201.php and it seems to already work.

I now recall why we got this error in rep101. We had to take care of the new options to add AR/AP lines in Journal Entry.
And we changed the rep101 and rep201 (1, 2) to fix these items, but forgot to fix rep101 regarding the Bank Payment.

I really hope that it is working in rep101 now. Did you try with your data?

/Joe

Committed

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 650

Re: Bug Found and Fixed in rep101.php

Yes it is working. Thanks alot for your time and input.

www.boxygen.pk

boxygen's Website

  • Alaa
  • Alaa
  • Senior Member
  • Offline
  • From: Jordan
  • Registered: 03/27/2015
  • Posts: 156

Re: Bug Found and Fixed in rep101.php

do we need to update rep201? i am still getting wrong numbers specially with the opening balance.

Regards.

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Bug Found and Fixed in rep101.php

@alaa

I cannot reproduce any errors in rep201. Could you be more specific and detailed.

Specific trans types? Or?

Joe

18 Reply by boxygen (edited by boxygen 03/10/2018 09:47:28 am)

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 650

Re: Bug Found and Fixed in rep101.php

@joe, still one problem exists in rep101.php after applying your last commit

Check the same customer's balances with Parameter ShowBalance=>No

First Date Range 1st Jan 2017 till 31 Dec 2017

Note down Charges, Credits, Allocated and Balance Closing figures

Second Date Range 1st Jan 2018 till date

The above closing shall reflect in this opening but there is a difference Opening

www.boxygen.pk

boxygen's Website

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 650

Re: Bug Found and Fixed in rep101.php

Hello @joe, could you check this bug

www.boxygen.pk

boxygen's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Bug Found and Fixed in rep101.php

@boxygen

Hello, I am overloaded with other work this week and next week. If you have a suggestion for changing the sql, then I would be extremely glad smile

/Joe

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Bug Found and Fixed in rep101.php

At Line 52 in reporting/rep101.php, the value of the $sql variable for the first available debtor_no in the second Date Range (2018) in the function get_open_balance() is:

SELECT 
    SUM(IF(t.type = 10 OR (t.type IN (0 , 1) AND t.ov_amount>0), -ABS(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges
   ,SUM(IF(t.type != 10 AND NOT(t.type IN (0 , 1) AND t.ov_amount>0), ABS(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits
   ,SUM(IF(t.type != 10 AND NOT(t.type IN (0 , 1)), t.alloc * -1, t.alloc)) AS Allocated
   ,SUM(IF(t.type = 10, 1, -1) * (ABS(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) - ABS(t.alloc))) AS OutStanding
FROM 1_debtor_trans t
WHERE t.debtor_no = '1'
  AND t.type <> 13
  AND t.tran_date < '2018-01-01'
GROUP BY debtor_no;

The output corresponding to the statements attached is:

charges    credits    Allocated    OutStanding
-6540        -6540            0            0

Do you want to zero out the Charges and Credits in this instance?

Post's attachments

CustBals.zip 4.1 kb, 2 downloads since 2018-03-22 

You don't have the permssions to download the attachments of this post.

apmuthu's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Bug Found and Fixed in rep101.php

@apmuthu

No I guess not.
-----
Regarding the open balances. There should be no differences when you check the 'Show Balance' box. The Sum of all record outstandings before the period should be the same as the Open Balance.
There might be some allocations that are not done that can influence on the period result.

But I cannot reproduce any errors here.

@boxygen
Please be a little more specific regarding your observation.

/Joe

23 Reply by boxygen (edited by boxygen 03/28/2018 06:17:03 pm)

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 650

Re: Bug Found and Fixed in rep101.php

@Joe,
Please check these reports of 2017 and 2018.

Check the closing balance of 2017 and opening balance of 2018

You will find the difference in Allocated and Outstanding Figures of Closing and Opening Balances.

I hope I could have explained the issue.

Regards.

Customer is QY009 Babjee Traders

You can Login

id: admin
pass: Pakistan1947

To reproduce this open rep101.php
select above customer
Show Balance => No
View report of 2017 whole year
View report of 2018 till date

PS: To understand the problem read the whole thread once again. We were discussing that due to Bank Payment Transaction to customer, the SQL Query shall be adjusted to produce correct results.

Post's attachments

2017-2018-rep101.zip 8.6 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
www.boxygen.pk

boxygen's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Bug Found and Fixed in rep101.php

If we first look at the Customer Balance Report with the Show Balance set to Yes.

Then the sum of all credits and debits and balance are shown correctly for closing and opening balances. The discrepancy in the allocations have something to do with the time of allocations (not really importing for the report. Maybe we should eliminate the summation for the allocations). The balance column is correct and this is very important.

If the report is taken with the Show Balance set to No.

The sum of all credits and debits are shown correctly. Here the lines are calculated on a record base line by line. The total of all outstanding with allocations added gives the total balance (the same result as on the balanced report).

Again the discrepancy in the allocations aare subject to closer investigation from my side. Will do this asap, but I am still rather overloaded with other work.

Of course you are welcome with suggestings regarding the Sql statements.

Joe

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Bug Found and Fixed in rep101.php

The outstandings seem to be computed for each line independently without being cumulative. This is wrong.
The allocations get summed up even for previous years as the date in the common WHERE clause is taken instead of being a specific range for allocations alone.

apmuthu's Website