FrontAccounting forum — Closing a fiscal year needs to update the query a bit.

Re: Closing a fiscal year needs to update the query a bit.

null@example.com (joe) — Sun, 26 Aug 2018 15:14:00 +0000

No, not really. However in normal conditions the old code should work.

Joe

Re: Closing a fiscal year needs to update the query a bit.

null@example.com (apmuthu) — Sun, 26 Aug 2018 11:05:53 +0000

@joe: any developments?

Re: Closing a fiscal year needs to update the query a bit.

null@example.com (joe) — Sat, 18 Aug 2018 13:58:59 +0000

Ok, let me investigate this a little more.

Joe

Re: Closing a fiscal year needs to update the query a bit.

null@example.com (kvvaradha) — Sat, 18 Aug 2018 12:11:03 +0000

joe wrote:

But isn't the voided entries all set to 0 amounts? So in my eyes this is a redundant change, right?
Joe

I guess you are right. But one of a FA user asked to check it,when he closes the fiscal year. he had some voided balance included in the balance. His version is 2.4.3.

Re: Closing a fiscal year needs to update the query a bit.

null@example.com (joe) — Sat, 18 Aug 2018 06:26:49 +0000

But isn't the voided entries all set to 0 amounts? So in my eyes this is a redundant change, right?

Joe

Re: Closing a fiscal year needs to update the query a bit.

null@example.com (apmuthu) — Fri, 17 Aug 2018 16:05:35 +0000

@kvvaradha: Since you are making a LEFT JOIN for the voided entries, there could be many null voided entries in the result set that would have the v.date_ field as NULL anyway!

@joe: can polish it up and check and include it if needed.

Re: Closing a fiscal year needs to update the query a bit.

null@example.com (kvvaradha) — Fri, 17 Aug 2018 02:28:22 +0000

rafat wrote:

Which file is this in?

Fiscal years db file. The fiscal year close function

Re: Closing a fiscal year needs to update the query a bit.

null@example.com (poncho1234) — Thu, 16 Aug 2018 14:30:01 +0000

@rafat, numerous text editors have search in file functionality(Notepad++ for one), and it takes seconds to carry out

Re: Closing a fiscal year needs to update the query a bit.

null@example.com (rafat) — Thu, 16 Aug 2018 14:15:43 +0000

Which file is this in?

Closing a fiscal year needs to update the query a bit.

null@example.com (kvvaradha) — Thu, 16 Aug 2018 09:53:55 +0000

The fiscal year close needs to change the query to get the amount.

It was now like this.

// retrieve total balances from balance sheet accounts
    $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans INNER JOIN ".TB_PREF."chart_master ON account=account_code
        INNER JOIN ".TB_PREF."chart_types ON account_type=id INNER JOIN ".TB_PREF."chart_class ON class_id=cid 
        WHERE ctype>=".CL_ASSETS." AND ctype <=".CL_EQUITY." AND tran_date <= '$to'";

And it should be like this

// retrieve total balances from balance sheet accounts        
        $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans trans LEFT JOIN ".TB_PREF."voided v ON trans.type_no=v.id AND v.type=trans.type INNER JOIN ".TB_PREF."chart_master ON account=account_code
        INNER JOIN ".TB_PREF."chart_types types ON account_type=types.id INNER JOIN ".TB_PREF."chart_class ON class_id=cid WHERE ctype>=".CL_ASSETS." AND ctype <=".CL_EQUITY." AND tran_date <= '$to' AND ISNULL(v.date_)";

I think we missed to check voided transactions before getting results.