Profit and Loss report showing incorrect data

[Jace996]

Expected Behavior

No response

Current Behavior

1. I have a purchase invoice date with 01/01/2023 as picture below
   
2. This is Ledger report showing the date 01/01/2023 without the problem.
   
3. When I open profit and loss report. The invoice PINV1287 amount showing on year 2022 as picture below
   
4. How I know that amount is from invoice PINV1287? I checked the AccountingLedgerEntry table and the date is 2022-12-31. See picture below
   

5) Because of this. My profit and loss report showed $7.20 and extra $120 in that amount $854.40 (correct amount is 734.40) on year 2022.

Steps to Reproduce

No response

FrappeBooks Version

0.20.0

Path or Feature name

Profit and loss

Country

MYR

Language

EN

OS

Macos (Intel)

Additional OS Info

No response

[Isaac-GC]

Hi @Jace996!

You listed the version you are using as 0.20.0. Could you doublecheck that please?

If you are running v0.20.0, try updating to v0.20.1

[Jace996]

Hi @Isaac-GC
Thanks for you replied.
Just now did upgraded to v0.20.1

The problem is still remain the same.

However, the ledger report now will not show PINV1287 amount if From Date filter is set to 01/01/2023. Instead, set to 31/12/2022 will show that amount. Please refer image below.

In addition, I found out the date recorded in AccountingLedgerEntry table are less one day for all the entry. Example my invoice and payment date is 01/01/2024 then date in AccountingLedgerEntry table will show 2023-12-31

Here is the testing database i created.

PurchaseInvoice

Payment

AccountingLedgerEntry

[Isaac-GC]

Hey @Jace996!

I've been trying to replicate the issue myself, but seem unable to. Not sure if its a setting on my test db or something else

Can you provide a demo/example database with the issues please?

Thanks!

[Jace996]

Hi @Isaac-GC,

This is screen recording for your reference
https://github.com/frappe/books/assets/116773059/2c0adef0-f923-42a9-ad7d-ea0a4d72bab3

Test dataase
test.books.db.zip

[Isaac-GC]

Thanks @Jace996! This actually helped narrow the issue down a lot! :)

I have a PR opened here #831 for the issue and will have it merged with an update as soon as I can confirm that the database patches that need to go in the PR work properly.

[Isaac-GC]

v0.21.1 patch update was just released. Can you validate the information shows as expected now please?

[Jace996]

Hi @Isaac-GC Thanks for the release.

1. Feature testing result.
   Looks good for
   Ledger

P&L

Balance Sheet

Trial balance
I created an opening entry using Journal with amount 10,000.00
But the Trial balance is displayed 0.00 for both opening(Dr) and opening(Cr).
I'm not sure this correct or not.

2. Database patch result
   There have date issue in this patch.

Before patch
Example my invoice date is 2023-06-09
Current date in database is 2023-06-08T16:00:00.000Z
Expected date after patch is 2023-06-09T00:00:00.000Z

After patch
Date became 2023-06-10T00:00:00.000Z which is incorrect

Debug for your reference
Image for debug logging

Logging result at below

Begin Loop function
Date from db  2023-06-08T16:00:00.000Z
Date init  2023-06-08T16:00:00.000Z
In timeZoneOffset condition
Check getDate value  9 <--- this should be 8
Final date
2023-06-10T00:00:00.000Z

Possible fix
I added entryDate.setUTCHours(0,0,0,0); after Date init then solve the issue

Logging result at below

Begin Loop function
Date from db  2023-06-08T16:00:00.000Z
Date init  2023-06-08T16:00:00.000Z
In timeZoneOffset condition
Check getDate value  8
Final date
2023-06-09T00:00:00.000Z

Im not sure this solution available for all the timezone or not. I just tested my database.

[Isaac-GC]

Thanks for testing this out! When doing local testing, the dates seemed correct when testing in the various timezones, but it looks like the next database patch will be a little more complicated. A patch is being worked on and should be out soon, and I'll do some more testing to see if the patch (both on the db and in the LedgerReport) is not working as expected,

There could be date issues with the DB, but it is also likely the numbers in the various reports (ledger, profit/loss, etc) seems to related to the other issue #815

[Isaac-GC]

Fixed the patch in #843 --> https://github.com/frappe/books/blob/556d91dc6cb230cf172c0ca8d3e9b6d964b3c9e7/backend/patches/v0_21_0/fixLedgerDateTime.ts

Still working on the reports issue, but the db issue should be permanently fixed now irregardlessly

[Jace996]

@Isaac-GC Hi, I have found another bug in Profit and Loss report
Version 0.21.2

I created a Journal with date 01/04/2023. It displayed on balance sheet but not in profit and loss.

Balance Sheet

Profit and Loss
The value does not display when both filter date is same 01/04/2023

I have to change to date +1 day as 02/04/2023 to show value

Possible solution
I modified this line and working well

Result

[bynicolas]

Following this, since I've been having this issue for quite some time now(#176), and it's never been fixed for me. not even with this patch

But I'd like to add a behaviour that I'm seeing, and before opening a new issue, I'd like to confirm.

When accessing the Profit and Loss report, I get a No Values to be Displayed message. when I'm supposed to get the data from invoices and expenses. (The general ledger report is displaying data)

You guys seem to be having data from that report. Am I missing something or was this a bug introduced between patches?