Care2007/modules/weberp/GLAccountInquiry.php

   1 <?php
   2
   3 /* $Revision: 1.19 $ */
   4
   5
   6 $PageSecurity = 8;
   7 include ('includes/session.inc');
   8 $title = _('General Ledger Account Inquiry');
   9 include('includes/header.inc');
  10 include('includes/GLPostings.inc');
  11
  12 if (isset($_POST['Account'])){
  13         $SelectedAccount = $_POST['Account'];
  14 } elseif (isset($_GET['Account'])){
  15         $SelectedAccount = $_GET['Account'];
  16 }
  17
  18 if (isset($_POST['Period'])){
  19         $SelectedPeriod = $_POST['Period'];
  20 } elseif (isset($_GET['Period'])){
  21         $SelectedPeriod = $_GET['Period'];
  22 }
  23
  24 echo "<FORM METHOD='POST' ACTION=" . $_SERVER['PHP_SELF'] . '?' . SID . '>';
  25
  26 /*Dates in SQL format for the last day of last month*/
  27 $DefaultPeriodDate = Date ('Y-m-d', Mktime(0,0,0,Date('m'),0,Date('Y')));
  28
  29 /*Show a form to allow input of criteria for TB to show */
  30 echo '<CENTER><TABLE>
  31         <TR>
  32          <TD>'._('Account').":</TD>
  33          <TD><SELECT Name='Account'>";
  34          $sql = 'SELECT accountcode, accountname FROM chartmaster ORDER BY accountcode';
  35          $Account = DB_query($sql,$db);
  36          while ($myrow=DB_fetch_array($Account,$db)){
  37             if($myrow['accountcode'] == $SelectedAccount){
  38                 echo '<OPTION SELECTED VALUE=' . $myrow['accountcode'] . '>' . $myrow['accountcode'] . ' ' . $myrow['accountname'];
  39             } else {
  40                 echo '<OPTION VALUE=' . $myrow['accountcode'] . '>' . $myrow['accountcode'] . ' ' . $myrow['accountname'];
  41             }
  42          }
  43          echo '</SELECT></TD></TR>
  44          <TR>
  45          <TD>'._('For Period range').':</TD>
  46          <TD><SELECT Name=Period[] multiple>';
  47          $sql = 'SELECT periodno, lastdate_in_period FROM periods ORDER BY periodno DESC';
  48          $Periods = DB_query($sql,$db);
  49          $id=0;
  50          while ($myrow=DB_fetch_array($Periods,$db)){
  51
  52             if($myrow['periodno'] == $SelectedPeriod[$id]){
  53               echo '<OPTION SELECTED VALUE=' . $myrow['periodno'] . '>' . _(MonthAndYearFromSQLDate($myrow['lastdate_in_period']));
  54             $id++;
  55             } else {
  56               echo '<OPTION VALUE=' . $myrow['periodno'] . '>' . _(MonthAndYearFromSQLDate($myrow['lastdate_in_period']));
  57             }
  58
  59          }
  60          echo "</SELECT></TD>
  61         </TR>
  62 </TABLE><P>
  63 <INPUT TYPE=SUBMIT NAME='Show' VALUE='"._('Show Account Transactions')."'></CENTER></FORM>";
  64
  65 /* End of the Form  rest of script is what happens if the show button is hit*/
  66
  67 if (isset($_POST['Show'])){
  68
  69         if (!isset($SelectedPeriod)){
  70                 prnMsg(_('A period or range of periods must be selected from the list box'),'info');
  71                 include('includes/footer.inc');
  72                 exit;
  73         }
  74         /*Is the account a balance sheet or a profit and loss account */
  75         $result = DB_query("SELECT pandl
  76                                 FROM accountgroups
  77                                 INNER JOIN chartmaster ON accountgroups.groupname=chartmaster.group_
  78                                 WHERE chartmaster.accountcode=$SelectedAccount",$db);
  79         $PandLRow = DB_fetch_row($result);
  80         if ($PandLRow[0]==1){
  81                 $PandLAccount = True;
  82         }else{
  83                 $PandLAccount = False; /*its a balance sheet account */
  84         }
  85
  86         $FirstPeriodSelected = min($SelectedPeriod);
  87         $LastPeriodSelected = max($SelectedPeriod);
  88
  89         $sql= "SELECT type,
  90                         typename,
  91                         gltrans.typeno,
  92                         trandate,
  93                         narrative,
  94                         amount,
  95                         periodno
  96                 FROM gltrans, systypes
  97                 WHERE gltrans.account = $SelectedAccount
  98                 AND systypes.typeid=gltrans.type
  99                 AND posted=1
 100                 AND periodno>=$FirstPeriodSelected
 101                 AND periodno<=$LastPeriodSelected
 102                 ORDER BY periodno, gltrans.trandate, counterindex";
 103
 104         $ErrMsg = _('The transactions for account') . ' ' . $SelectedAccount . ' ' . _('could not be retrieved because') ;
 105         $TransResult = DB_query($sql,$db,$ErrMsg);
 106
 107         echo '<table>';
 108
 109         $TableHeader = "<TR>
 110                         <TD class='tableheader'>" . _('Type') . "</TD>
 111                         <TD class='tableheader'>" . _('Number') . "</TD>
 112                         <TD class='tableheader'>" . _('Date') . "</TD>
 113                         <TD class='tableheader'>" . _('Debit') . "</TD>
 114                         <TD class='tableheader'>" . _('Credit') . "</TD>
 115                         <TD class='tableheader'>" . _('Narrative') . '</TD>
 116                         </TR>';
 117
 118         echo $TableHeader;
 119
 120         if ($PandLAccount==True) {
 121                 $RunningTotal = 0;
 122         } else {
 123                // added to fix bug with Brought Forward Balance always being zero
 124                                         $sql = "SELECT bfwd,
 125                                                 actual,
 126                                                 period
 127                                         FROM chartdetails
 128                                         WHERE chartdetails.accountcode= $SelectedAccount
 129                                         AND chartdetails.period=" . $FirstPeriodSelected;
 130
 131                                 $ErrMsg = _('The chart details for account') . ' ' . $SelectedAccount . ' ' . _('could not be retrieved');
 132                                 $ChartDetailsResult = DB_query($sql,$db,$ErrMsg);
 133                                 $ChartDetailRow = DB_fetch_array($ChartDetailsResult);
 134                                 // --------------------
 135
 136                 $RunningTotal =$ChartDetailRow['bfwd'];
 137                 if ($RunningTotal < 0 ){ //its a credit balance b/fwd
 138                         echo "<TR bgcolor='#FDFEEF'>
 139                                 <TD COLSPAN=3><B>" . _('Brought Forward Balance') . '</B><TD>
 140                                 </TD></TD>
 141                                 <TD ALIGN=RIGHT><B>' . number_format(-$RunningTotal,2) . '</B></TD>
 142                                 <TD></TD>
 143                                 </TR>';
 144                 } else { //its a debit balance b/fwd
 145                         echo "<TR bgcolor='#FDFEEF'>
 146                                 <TD COLSPAN=3><B>" . _('Brought Forward Balance') . '</B></TD>
 147                                 <TD ALIGN=RIGHT><B>' . number_format($RunningTotal,2) . '</B></TD>
 148                                 <TD COLSPAN=2></TD>
 149                                 </TR>';
 150                 }
 151         }
 152         $PeriodTotal = 0;
 153         $PeriodNo = -9999;
 154         $ShowIntegrityReport = False;
 155         $j = 1;
 156         $k=0; //row colour counter
 157
 158         while ($myrow=DB_fetch_array($TransResult)) {
 159
 160                 if ($myrow['periodno']!=$PeriodNo){
 161                         if ($PeriodNo!=-9999){ //ie its not the first time around
 162                                 /*Get the ChartDetails balance b/fwd and the actual movement in the account for the period as recorded in the chart details - need to ensure integrity of transactions to the chart detail movements. Also, for a balance sheet account it is the balance carried forward that is important, not just the transactions*/
 163
 164                                 $sql = "SELECT bfwd,
 165                                                 actual,
 166                                                 period
 167                                         FROM chartdetails
 168                                         WHERE chartdetails.accountcode= $SelectedAccount
 169                                         AND chartdetails.period=" . $PeriodNo;
 170
 171                                 $ErrMsg = _('The chart details for account') . ' ' . $SelectedAccount . ' ' . _('could not be retrieved');
 172                                 $ChartDetailsResult = DB_query($sql,$db,$ErrMsg);
 173                                 $ChartDetailRow = DB_fetch_array($ChartDetailsResult);
 174
 175                                 echo "<TR bgcolor='#FDFEEF'>
 176                                         <TD COLSPAN=3><B>" . _('Total for period') . ' ' . $PeriodNo . '</B></TD>';
 177                                 if ($PeriodTotal < 0 ){ //its a credit balance b/fwd
 178                                         echo '<TD></TD>
 179                                                 <TD ALIGN=RIGHT><B>' . number_format(-$PeriodTotal,2) . '</B></TD>
 180                                                 <TD></TD>
 181                                                 </TR>';
 182                                 } else { //its a debit balance b/fwd
 183                                         echo '<TD ALIGN=RIGHT><B>' . number_format($PeriodTotal,2) . '</B></TD>
 184                                                 <TD COLSPAN=2></TD>
 185                                                 </TR>';
 186                                 }
 187                                 $IntegrityReport .= '<BR>' . _('Period') . ': ' . $PeriodNo  . _('Account movement per transaction') . ': '  . number_format($PeriodTotal,2) . ' ' . _('Movement per ChartDetails record') . ': ' . number_format($ChartDetailRow['actual'],2) . ' ' . _('Period difference') . ': ' . number_format($PeriodTotal -$ChartDetailRow['actual'],3);
 188
 189                                 if (ABS($PeriodTotal -$ChartDetailRow['actual'])>0.01){
 190                                         $ShowIntegrityReport = True;
 191                                 }
 192                         }
 193                         $PeriodNo = $myrow['periodno'];
 194                         $PeriodTotal = 0;
 195                 }
 196
 197                 if ($k==1){
 198                         echo "<tr bgcolor='#CCCCCC'>";
 199                         $k=0;
 200                 } else {
 201                         echo "<tr bgcolor='#EEEEEE'>";
 202                         $k++;
 203                 }
 204
 205                 $RunningTotal += $myrow['amount'];
 206                 $PeriodTotal += $myrow['amount'];
 207
 208                 if($myrow['amount']>=0){
 209                         $DebitAmount = number_format($myrow['amount'],2);
 210                         $CreditAmount = '';
 211                 } else {
 212                         $CreditAmount = number_format(-$myrow['amount'],2);
 213                         $DebitAmount = '';
 214                 }
 215
 216                 $FormatedTranDate = ConvertSQLDate($myrow['trandate']);
 217                 $URL_to_TransDetail = $rootpath . '/GLTransInquiry.php?' . SID . '&TypeID=' . $myrow['type'] . '&TransNo=' . $myrow['typeno'];
 218
 219                 printf("<td>%s</td>
 220                         <td><A HREF='%s'>%s</A></td>
 221                         <td>%s</td>
 222                         <td ALIGN=RIGHT>%s</td>
 223                         <td ALIGN=RIGHT>%s</td>
 224                         <td>%s</td>
 225                         </tr>",
 226                         $myrow['typename'],
 227                         $URL_to_TransDetail,
 228                         $myrow['typeno'],
 229                         $FormatedTranDate,
 230                         $DebitAmount,
 231                         $CreditAmount,
 232                         $myrow['narrative']);
 233
 234                 $j++;
 235
 236                 If ($j == 18){
 237                         echo $TableHeader;
 238                         $j=1;
 239                 }
 240
 241         }
 242
 243         echo "<TR bgcolor='#FDFEEF'><TD COLSPAN=3><B>";
 244         if ($PandLAccount==True){
 245                 echo _('Total Period Movement');
 246         } else { /*its a balance sheet account*/
 247                 echo _('Balance C/Fwd');
 248         }
 249         echo '</B></TD>';
 250
 251         if ($RunningTotal >0){
 252                 echo '<TD ALIGN=RIGHT><B>' . number_format(($RunningTotal),2) . '</B></TD><TD COLSPAN=2></TD></TR>';
 253         }else {
 254                 echo '<TD></TD><TD ALIGN=RIGHT><B>' . number_format((-$RunningTotal),2) . '</B></TD><TD COLSPAN=2></TD></TR>';
 255         }
 256         echo '</table>';
 257 } /* end of if Show button hit */
 258
 259
 260
 261 if ($ShowIntegrityReport){
 262
 263         prnMsg( _('There are differences between the sum of the transactions and the recorded movements in the ChartDetails table') . '. ' . _('A log of the account differences for the periods report shows below'),'warn');
 264         echo '<P>'.$IntegrityReport;
 265 }
 266 include('includes/footer.inc');
 267 ?>