• From: India, TN, Karur
  • Registered: 05/16/2014
  • Posts: 1,195

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

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.

Subscription service based on FA
HRM CRM POS batch Themes

kvvaradha's Website

  • rafat
  • Senior Member
  • Offline
  • From: UAE, Dubai
  • Registered: 11/28/2017
  • Posts: 550

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

Which file is this in?

rafat's Website

  • From: Colombia
  • Registered: 12/29/2014
  • Posts: 629

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

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

The FrontAccounting Wiki(Manual, examples, tips, setup info, links to accounting sites, etc) https://frontaccounting.com/fawiki/
  • From: India, TN, Karur
  • Registered: 05/16/2014
  • Posts: 1,195

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

rafat wrote:

Which file is this in?

Fiscal years db file. The fiscal year close function

Subscription service based on FA
HRM CRM POS batch Themes

kvvaradha's Website

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

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

@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.

apmuthu's Website

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

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

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

Joe

7 Reply by kvvaradha (edited by kvvaradha 08/18/2018 12:11:29 pm)

  • From: India, TN, Karur
  • Registered: 05/16/2014
  • Posts: 1,195

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

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.

Subscription service based on FA
HRM CRM POS batch Themes

kvvaradha's Website

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

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

Ok, let me investigate this a little more.

Joe

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

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

@joe: any developments?

apmuthu's Website

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

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

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

Joe