inc/view/company_year_end.php

   1 <?php
   2
   3 // Viewer component for company records
   4 $viewer = new Viewer("Company Year End");
   5 $viewer->AddField( 'companycode' );
   6 $viewer->AddField( 'name', 'l.legalname' );
   7 $viewer->AddField( 'parentcode', 'l.parentcode' );
   8 $joins = "company l LEFT JOIN client c USING ( clientcode ) ";
   9 $viewer->SetJoins( $joins );
  10 $id = intval($_REQUEST['id']);
  11 $viewer->SetWhere( 'companycode='.$id );
  12
  13 $template = <<<EOTEMPLATE
  14 <table>
  15  <tr>
  16   <th class="right">Company:</th>
  17   <td class="center">##companycode.value##</td>
  18   <td class="left">##name.value##</td>
  19  </tr>
  20 </table>
  21
  22 EOTEMPLATE;
  23
  24 param_to_global( 'fy_code', 'int', 'fy', 'year' );
  25 if ( !isset($fy_code) ) $fy_code = $c->current_financial_year;
  26
  27 $viewer->SetTemplate( $template );
  28 $viewer->GetRecord();
  29 $viewer->SetTitle($viewer->Record->{'name'}." at end of $fy_code financial year");
  30 $page_elements[] = $viewer;
  31 $c->page_title = $viewer->Title;
  32
  33 include_once("menus_entityaccount.php");
  34
  35 // Now list income accounts
  36 require_once('classBrowser.php');
  37
  38 $template = new Browser("Accounts");
  39 $template->AddHidden( 'account', "entitytype || '-' || TO_CHAR(entitycode,'FM00009') || '-' || TO_CHAR(accountcode,'FM0009.00')" );
  40 $template->AddHidden( 'entitytype' );
  41 $template->AddHidden( 'entitycode' );
  42 $template->AddColumn( 'accountgroupcode', 'Group', 'left', '<td class="left" style="width:5em;">%s</td>' );
  43 $template->AddColumn( 'accountcode', 'Account', 'right', '', "TO_CHAR(accountcode,'FM0009.00')" );
  44 $template->AddColumn( 'name', 'Name', 'left', '<td class="left" style="width:30em;">%s</td>', 'chartofaccount.name' );
  45 $template->AddColumn( 'balance', 'Balance', 'right', '%0.2lf', '(CASE WHEN COALESCE(creditgroup,FALSE) THEN -1 ELSE 1 END * balance)' );
  46 $template->AddTotal( 'balance' );
  47 $template->SetWhere( "balance != 0.0" );
  48 $rowurl = '/view.php?t=account&id=%s';
  49 $template->RowFormat( "<tr onclick=\"window.location='$rowurl';\" class=\"r%d\">\n", "</tr>\n", 'account', '#even' );
  50
  51 /**
  52 * Income and expenditure accounts just look at the current financial year
  53 */
  54 $template->SetJoins( "chartofaccount LEFT OUTER JOIN year_end_balance('L',$id,$fy_code) USING ( accountcode ) LEFT JOIN accountgroup USING ( accountgroupcode )" );
  55
  56 $browser = clone($template);
  57 $browser->SetTitle("Income Accounts");
  58 $browser->AndWhere("grouptype = 'P' AND creditgroup" );
  59 $browser->SetOrdering( 'accountcode', 'ASC', 1 );
  60 $page_elements[] = $browser;
  61
  62 $browser = clone($template);
  63 $browser->SetTitle("Expense Accounts");
  64 $browser->AndWhere("grouptype = 'P' AND NOT creditgroup" );
  65 $browser->SetOrdering( 'accountcode', 'ASC', 2 );
  66 $page_elements[] = $browser;
  67
  68
  69 $sql = "SELECT sum(balance) FROM year_end_balance('L',$id,$fy_code) LEFT JOIN chartofaccount USING ( accountcode ) LEFT JOIN accountgroup USING ( accountgroupcode ) WHERE grouptype = 'P'";
  70 $qry = new PgQuery($sql);
  71 if ( $qry->Exec('company_year_end') && $qry->rows == 1 ) {
  72   $r = $qry->Fetch();
  73   $net_type = ($r->sum < 0 ? 'Profit' : 'Loss');
  74   $page_elements[] = sprintf( "<h3>Net %s is %14.2lf</h3>\n", $net_type, abs($r->sum) );
  75 }
  76
  77
  78 /**
  79 * Asset and liability accounts look at the full history, which is included in the
  80 * function we are calling to get the balance.
  81 */
  82
  83 $browser = clone($template);
  84 $browser->SetTitle("Asset Accounts");
  85 $browser->AndWhere("grouptype != 'P' AND NOT creditgroup" );
  86 $browser->SetOrdering( 'accountcode', 'ASC', 3 );
  87 $page_elements[] = $browser;
  88
  89 $browser = clone($template);
  90 $browser->SetTitle("Liability Accounts");
  91 $browser->AndWhere("grouptype != 'P' AND creditgroup" );
  92 $browser->SetOrdering( 'accountcode', 'ASC', 4 );
  93 $page_elements[] = $browser;
  94
  95 $sql = "SELECT sum(balance) FROM year_end_balance('L',$id,$fy_code) LEFT JOIN chartofaccount USING ( accountcode ) LEFT JOIN accountgroup USING ( accountgroupcode ) WHERE grouptype IS NULL";
  96 $qry = new PgQuery($sql);
  97
  98 if ( $qry->Exec('company_year_end') && $qry->rows == 1 ) {
  99   $r = $qry->Fetch();
 100   if ( $r->sum != 0 ) {
 101     $browser = clone($template);
 102     $browser->SetTitle("Bogus Accounts Incorrectly Set Up");
 103     $browser->AndWhere("grouptype IS NULL" );
 104     $browser->SetOrdering( 'accountcode', 'ASC', 5 );
 105     $page_elements[] = $browser;
 106   }
 107 }
 108
 109
 110 $sql = "SELECT sum(balance) FROM year_end_balance('L',$id,$fy_code) LEFT JOIN chartofaccount USING ( accountcode ) LEFT JOIN accountgroup USING ( accountgroupcode ) WHERE grouptype != 'P'";
 111 $qry = new PgQuery($sql);
 112 if ( $qry->Exec('company_year_end') && $qry->rows == 1 ) {
 113   $r = $qry->Fetch();
 114   $net_type = ($r->sum < 0 ? 'Liabilities' : 'Assets' );
 115   $page_elements[] = sprintf( "<h3>Net %s are %14.2lf</h3>\n", $net_type, abs($r->sum) );
 116 }
 117
 118
 119 function post_render_function() {
 120 global $page_elements;
 121
 122   $ledger_total = 0;
 123   foreach( $page_elements AS $element ) {
 124     if ( gettype($element) == 'object' && method_exists($element, 'GetTotal') ) {
 125       $ledger_total += $element->GetTotal( 'balance' );
 126       printf( "<p>Total for element is %14.2lf</p", $element->GetTotal( 'balance' ) );
 127     }
 128
 129   }
 130   printf( "<p>Total for ledger is %14.2lf</p", $ledger_total );
 131 }