I have committed the Summation of the Outstanding Open Balances in this report, Customer Balance.

Still investigating the allocation column, although this is not an important column, but anyhow.

/Joe

Please try this change in rep101.php. Rename the rep101.php1 to rep101.php and replace.

Tell me if the outstanding column is correct now when selecting Show Balance to No.

I guess the allocation column still has some discrepancies.

I want this clarified before continuing with the allocation column.

@apmuthu and @boxygen
Maybe we can inplement the Branch selection too. If we write this information above in the report with the other parameter info, I guess we need no more columns. Right?
Sorry Guys. We have no link between the customers and branches in Report Selections, so it will be a major change here. So be patient, maybe in the next major.

/Joe

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

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.

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

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?

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

/Joe

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

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

Specific trans types? Or?

Joe

Regards.

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