1<?php 2// GLAccountInquiry.php 3// Shows the general ledger transactions for a specified account over a specified range of periods. 4 5include ('includes/session.php'); 6$Title = _('General Ledger Account Inquiry'); 7$ViewTopic = 'GeneralLedger'; 8$BookMark = 'GLAccountInquiry'; 9include('includes/header.php'); 10 11echo '<p class="page_title_text"><img alt="" src="', $RootPath, '/css/', $Theme, '/images/transactions.png" title="',// Icon image. 12 _('General Ledger Account Inquiry'), '" /> ',// Icon title. 13 _('General Ledger Account Inquiry'), '</p>';// Page title. 14 15include('includes/GLPostings.inc'); 16 17if(isset($_POST['Account'])) { 18 $SelectedAccount = $_POST['Account']; 19} elseif(isset($_GET['Account'])) { 20 $SelectedAccount = $_GET['Account']; 21} 22 23if(isset($_POST['Period'])) { 24 $SelectedPeriod = $_POST['Period']; 25} elseif(isset($_GET['Period'])) { 26 $SelectedPeriod = array($_GET['Period']); 27} 28 29if(isset($_GET['Show'])) { 30 $_POST['Show'] = $_GET['Show']; 31} 32 33/* Get the start and periods, depending on how this script was called*/ 34if(isset($SelectedPeriod)) { //If it was called from itself (in other words an inquiry was run and we wish to leave the periods selected unchanged 35 $FirstPeriodSelected = min($SelectedPeriod); 36 $LastPeriodSelected = max($SelectedPeriod); 37} elseif(isset($_GET['PeriodFrom'])) { //If it was called from the Trial Balance/P&L or Balance sheet 38 $FirstPeriodSelected = $_GET['PeriodFrom']; 39 $LastPeriodSelected = $_GET['PeriodTo']; 40 $SelectedPeriod[0] = $_GET['PeriodFrom']; 41 $SelectedPeriod[1] = $_GET['PeriodTo']; 42} else { // Otherwise just highlight the current period 43 $FirstPeriodSelected = GetPeriod(date($_SESSION['DefaultDateFormat'])); 44 $LastPeriodSelected = GetPeriod(date($_SESSION['DefaultDateFormat'])); 45} 46 47echo '<div class="page_help_text noprint">' . _('Use the keyboard Shift key to select multiple periods') . '</div><br />'; 48echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">'; 49echo '<div class="noprint">';// Begin input of criteria div. 50echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 51 52/*Dates in SQL format for the last day of last month*/ 53$DefaultPeriodDate = Date ('Y-m-d', Mktime(0,0,0,Date('m'),0,Date('Y'))); 54 55/*Show a form to allow input of criteria for TB to show */ 56echo '<table class="selection"> 57 <tr> 58 <td>' . _('Account').':</td> 59 <td><select name="Account">'; 60 61$sql = "SELECT chartmaster.accountcode, 62 bankaccounts.accountcode AS bankact, 63 bankaccounts.currcode, 64 chartmaster.accountname 65 FROM chartmaster LEFT JOIN bankaccounts 66 ON chartmaster.accountcode=bankaccounts.accountcode 67 INNER JOIN glaccountusers ON glaccountusers.accountcode=chartmaster.accountcode AND glaccountusers.userid='" . $_SESSION['UserID'] . "' AND glaccountusers.canview=1 68 ORDER BY chartmaster.accountcode"; 69$Account = DB_query($sql); 70while($myrow=DB_fetch_array($Account)) { 71 if($myrow['accountcode'] == $SelectedAccount) { 72 if(!is_null($myrow['bankact'])) { 73 $BankAccount = true; 74 } 75 echo '<option selected="selected" value="' . $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' ' . htmlspecialchars($myrow['accountname'], ENT_QUOTES, 'UTF-8', false) . '</option>'; 76 } else { 77 echo '<option value="' . $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' ' . htmlspecialchars($myrow['accountname'], ENT_QUOTES, 'UTF-8', false) . '</option>'; 78 } 79 } 80echo '</select></td> 81 </tr>'; 82 83//Select the tag 84echo '<tr> 85 <td>' . _('Select Tag') . ':</td> 86 <td><select name="tag">'; 87 88$SQL = "SELECT tagref, 89 tagdescription 90 FROM tags 91 ORDER BY tagref"; 92 93$result=DB_query($SQL); 94echo '<option value="0">0 - '._('All tags') . '</option>'; 95 96while($myrow=DB_fetch_array($result)) { 97 if(isset($_POST['tag']) and $_POST['tag']==$myrow['tagref']) { 98 echo '<option selected="selected" value="' . $myrow['tagref'] . '">' . $myrow['tagref'].' - ' .$myrow['tagdescription'] . '</option>'; 99 } else { 100 echo '<option value="' . $myrow['tagref'] . '">' . $myrow['tagref'].' - ' .$myrow['tagdescription'] . '</option>'; 101 } 102} 103echo '</select></td> 104 </tr>'; 105// End select tag 106echo '<tr> 107 <td>' . _('For Period range').':</td> 108 <td><select name="Period[]" size="12" multiple="multiple">'; 109 110$sql = "SELECT periodno, lastdate_in_period FROM periods ORDER BY periodno DESC"; 111$Periods = DB_query($sql); 112while($myrow=DB_fetch_array($Periods)) { 113 if(isset($FirstPeriodSelected) AND $myrow['periodno'] >= $FirstPeriodSelected AND $myrow['periodno'] <= $LastPeriodSelected) { 114 echo '<option selected="selected" value="' . $myrow['periodno'] . '">' . _(MonthAndYearFromSQLDate($myrow['lastdate_in_period'])) . '</option>'; 115 } else { 116 echo '<option value="' . $myrow['periodno'] . '">' . _(MonthAndYearFromSQLDate($myrow['lastdate_in_period'])) . '</option>'; 117 } 118} 119echo '</select></td> 120 </tr> 121 </table> 122 <br /> 123 <div class="centre"> 124 <input type="submit" name="Show" value="'._('Show Account Transactions').'" /> 125 </div> 126 </div>',// End input of criteria div. 127 '</form>'; 128 129/* End of the Form rest of script is what happens if the show button is hit*/ 130 131if(isset($_POST['Show'])) { 132 133 if(!isset($SelectedPeriod)) { 134 prnMsg(_('A period or range of periods must be selected from the list box'),'info'); 135 include('includes/footer.php'); 136 exit; 137 } 138 /*Is the account a balance sheet or a profit and loss account */ 139 $result = DB_query("SELECT pandl 140 FROM accountgroups 141 INNER JOIN chartmaster ON accountgroups.groupname=chartmaster.group_ 142 WHERE chartmaster.accountcode='" . $SelectedAccount ."'"); 143 $PandLRow = DB_fetch_row($result); 144 if($PandLRow[0]==1) { 145 $PandLAccount = True; 146 } else { 147 $PandLAccount = False; /*its a balance sheet account */ 148 } 149 150 $FirstPeriodSelected = min($SelectedPeriod); 151 $LastPeriodSelected = max($SelectedPeriod); 152 153 $sql= "SELECT counterindex, 154 type, 155 typename, 156 gltrans.typeno, 157 trandate, 158 narrative, 159 amount, 160 periodno, 161 gltrans.tag, 162 tagdescription 163 FROM gltrans INNER JOIN systypes 164 ON systypes.typeid=gltrans.type 165 LEFT JOIN tags 166 ON gltrans.tag = tags.tagref 167 WHERE gltrans.account = '" . $SelectedAccount . "' 168 AND posted=1 169 AND periodno>='" . $FirstPeriodSelected . "' 170 AND periodno<='" . $LastPeriodSelected . "'"; 171 172 if($_POST['tag']!=0) { 173 $sql = $sql . " AND tag='" . $_POST['tag'] . "'"; 174 } 175 176 $sql = $sql . " ORDER BY periodno, gltrans.trandate, counterindex"; 177 $namesql = "SELECT accountname FROM chartmaster WHERE accountcode='" . $SelectedAccount . "'"; 178 $nameresult = DB_query($namesql); 179 $namerow=DB_fetch_array($nameresult); 180 $SelectedAccountName=$namerow['accountname']; 181 $ErrMsg = _('The transactions for account') . ' ' . $SelectedAccount . ' ' . _('could not be retrieved because') ; 182 $TransResult = DB_query($sql,$ErrMsg); 183 $BankAccountInfo = isset($BankAccount) 184 ? '<th>' . _('Org Currency') . '</th> 185 <th>' . _('Amount in Org Currency') . '</th> 186 <th>' . _('Bank Ref') . '</th>' 187 : '' ; 188 echo '<br /> 189 <table class="selection"> 190 <thead> 191 <tr> 192 <th colspan="11"><b>', _('Transactions for account'), ' ', $SelectedAccount, ' - ', $SelectedAccountName, '</b></th> 193 </tr> 194 <tr> 195 <th>', _('Type'), '</th> 196 <th>', _('Number'), '</th> 197 <th>', ('Date'), '</th> 198 <th>', _('Narrative'), '</th> 199 <th>', _('Debit'), '</th> 200 <th>', _('Credit'), '</th> 201 <th>', _('Balance'), '</th> 202 <th>', _('Tag'), '</th>', 203 $BankAccountInfo, ' 204 </tr> 205 </thead><tbody>'; 206 207 if($PandLAccount==True) { 208 $RunningTotal = 0; 209 } else { 210 // added to fix bug with Brought Forward Balance always being zero 211 $Sql = "SELECT bfwd, 212 actual, 213 period 214 FROM chartdetails 215 WHERE chartdetails.accountcode='" . $SelectedAccount . "' 216 AND chartdetails.period='" . $FirstPeriodSelected . "'"; 217 $ErrMsg = _('The chart details for account') . ' ' . $SelectedAccount . ' ' . _('could not be retrieved'); 218 $ChartDetailsResult = DB_query($Sql, $ErrMsg); 219 $ChartDetailRow = DB_fetch_array($ChartDetailsResult); 220 $RunningTotal = $ChartDetailRow['bfwd']; 221 echo '<tr> 222 <td colspan="4"><b>', _('Brought Forward Balance'), '</b></td>'; 223 if($RunningTotal < 0 ) {// It is a credit balance b/fwd 224 echo '<td> </td> 225 <td class="number"><b>', locale_number_format(-$RunningTotal,$_SESSION['CompanyRecord']['decimalplaces']), '</b></td>'; 226 } else {// It is a debit balance b/fwd 227 echo '<td class="number"><b>', locale_number_format($RunningTotal,$_SESSION['CompanyRecord']['decimalplaces']), '</b></td> 228 <td> </td>'; 229 } 230 echo '<td colspan="5"> </td> 231 </tr>'; 232 } 233 $PeriodTotal = 0; 234 $PeriodNo = -9999; 235 $ShowIntegrityReport = False; 236 $j = 1; 237 $IntegrityReport=''; 238 while($myrow=DB_fetch_array($TransResult)) { 239 if($myrow['periodno']!=$PeriodNo) { 240 if($PeriodNo!=-9999) { //ie its not the first time around 241 /*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*/ 242 243 $sql = "SELECT bfwd, 244 actual, 245 period 246 FROM chartdetails 247 WHERE chartdetails.accountcode='" . $SelectedAccount . "' 248 AND chartdetails.period='" . $PeriodNo . "'"; 249 250 $ErrMsg = _('The chart details for account') . ' ' . $SelectedAccount . ' ' . _('could not be retrieved'); 251 $ChartDetailsResult = DB_query($sql,$ErrMsg); 252 $ChartDetailRow = DB_fetch_array($ChartDetailsResult); 253 254 echo '<tr> 255 <td colspan="4"><b>' . _('Total for period') . ' ' . $PeriodNo . '</b></td>'; 256 if($PandLAccount==True) { 257 $RunningTotal = 0; 258 } 259 if($PeriodTotal < 0 ) {// It is a credit balance b/fwd 260 echo '<td> </td> 261 <td class="number"><b>', locale_number_format(-$PeriodTotal,$_SESSION['CompanyRecord']['decimalplaces']), '</b></td>'; 262 } else {// It is a debit balance b/fwd 263 echo '<td class="number"><b>', locale_number_format($PeriodTotal,$_SESSION['CompanyRecord']['decimalplaces']), '</b></td> 264 <td> </td>'; 265 } 266 echo '<td colspan="5"> </td> 267 </tr>'; 268 $IntegrityReport .= '<br />' . _('Period') . ': ' . $PeriodNo . _('Account movement per transaction') . ': ' . locale_number_format($PeriodTotal,$_SESSION['CompanyRecord']['decimalplaces']) . ' ' . _('Movement per ChartDetails record') . ': ' . locale_number_format($ChartDetailRow['actual'],$_SESSION['CompanyRecord']['decimalplaces']) . ' ' . _('Period difference') . ': ' . locale_number_format($PeriodTotal -$ChartDetailRow['actual'],3); 269 270 if(ABS($PeriodTotal -$ChartDetailRow['actual'])>0.01) { 271 $ShowIntegrityReport = True; 272 } 273 } 274 $PeriodNo = $myrow['periodno']; 275 $PeriodTotal = 0; 276 } 277 278 $BankRef = ''; 279 $OrgAmt = ''; 280 $Currency = ''; 281 if($myrow['type'] == 12 OR $myrow['type'] == 22 OR $myrow['type'] == 2 OR $myrow['type'] == 1) { 282 $banksql = "SELECT ref,currcode,amount FROM banktrans 283 WHERE type='" .$myrow['type']."' AND transno='" . $myrow['typeno'] . "' AND bankact='" . $SelectedAccount . "'"; 284 $ErrMsg = _('Failed to retrieve bank data'); 285 $bankresult = DB_query($banksql,$ErrMsg); 286 if(DB_num_rows($bankresult)>0) { 287 $bankrow = DB_fetch_array($bankresult); 288 $BankRef = $bankrow['ref']; 289 $OrgAmt = $bankrow['amount']; 290 $Currency = $bankrow['currcode']; 291 } elseif($myrow['type'] == 1) { 292 //We should find out when transaction happens between bank accounts; 293 $bankreceivesql = "SELECT ref,type,transno,currcode,amount FROM banktrans 294 WHERE ref LIKE '@%' AND transdate='" . $myrow['trandate'] . "' AND bankact='" . $SelectedAccount . "'"; 295 $ErrMsg = _('Failed to retrieve bank receive data'); 296 $bankresult = DB_query($bankreceivesql,$ErrMsg); 297 if(DB_num_rows($bankresult)>0) { 298 while($bankrow = DB_fetch_array($bankresult)) { 299 if(substr($bankrow['ref'],1,strpos($bankrow['ref'],' ')-1) == $myrow['typeno']) { 300 $BankRef = $bankrow['ref']; 301 $OrgAmt = $bankrow['amount']; 302 $Currency = $bankrow['currcode']; 303 $BankReceipt = true; 304 break; 305 } 306 } 307 } 308 if(!isset($BankReceipt)) { 309 $BankRef = ''; 310 $OrgAmt = $myrow['amount']; 311 $Currency = $_SESSION['CompanyRecord']['currencydefault']; 312 } 313 314 } elseif(isset($BankAccount)) { 315 $BankRef = ''; 316 $OrgAmt = $myrow['amount']; 317 $Currency = $_SESSION['CompanyRecord']['currencydefault']; 318 } 319 } 320 321 322 $URL_to_TransDetail = $RootPath . '/GLTransInquiry.php?TypeID=' . urlencode($myrow['type']) . '&TransNo=' . urlencode($myrow['typeno']); 323 $FormatedTranDate = ConvertSQLDate($myrow['trandate']); 324 if($myrow['amount']>=0) { 325 $DebitAmount = locale_number_format($myrow['amount'], $_SESSION['CompanyRecord']['decimalplaces']); 326 $CreditAmount = ''; 327 } else { 328 $CreditAmount = locale_number_format(-$myrow['amount'], $_SESSION['CompanyRecord']['decimalplaces']); 329 $DebitAmount = ''; 330 } 331 $RunningTotal += $myrow['amount']; 332 $PeriodTotal += $myrow['amount']; 333 echo '<tr class="striped_row"> 334 <td class="text">', _($myrow['typename']), '</td> 335 <td class="number"><a href="', $URL_to_TransDetail, '">', $myrow['typeno'], '</a></td> 336 <td class="centre">', $FormatedTranDate, '</td> 337 <td class="text">', $myrow['narrative'], '</td> 338 <td class="number">', $DebitAmount, '</td> 339 <td class="number">', $CreditAmount, '</td> 340 <td class="number">', locale_number_format($RunningTotal, $_SESSION['CompanyRecord']['decimalplaces']), '</td> 341 <td class="text">', $myrow['tagdescription'], '</td>'; 342 if(isset($BankAccount)) { 343 echo '<td class="text">', $Currency, '</td> 344 <td class="number"><b>', locale_number_format($OrgAmt, $_SESSION['CompanyRecord']['decimalplaces']), '</b></td> 345 <td class="text">', $BankRef, '</td>'; 346 } 347 echo '</tr>'; 348 } 349 350 echo '<tr> 351 <td colspan="4"><b>'; 352 if($PandLAccount==True) { 353 echo _('Total Period Movement');/* RChacon: "Total for period XX"? */ 354 } else { /*its a balance sheet account*/ 355 echo _('Balance C/Fwd'); 356 } 357 echo '</b></td>'; 358 if($RunningTotal < 0) {// It is a debit Total Period Movement or Balance C/Fwd 359 echo '<td> </td> 360 <td class="number"><b>', locale_number_format((-$RunningTotal), $_SESSION['CompanyRecord']['decimalplaces']), '</b></td>'; 361 } else {// It is a credit Total Period Movement or Balance C/Fwd 362 echo '<td class="number"><b>', locale_number_format(($RunningTotal), $_SESSION['CompanyRecord']['decimalplaces']), '</b></td> 363 <td> </td>'; 364 } 365 echo '<td colspan="5"> </td> 366 </tr> 367 </tbody></table>'; 368} /* end of if Show button hit */ 369 370if(isset($ShowIntegrityReport) AND $ShowIntegrityReport==True AND $_POST['tag']=='0') { 371 if(!isset($IntegrityReport)) { 372 $IntegrityReport=''; 373 } 374 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'); 375 echo '<p>' . $IntegrityReport; 376} 377include('includes/footer.php'); 378?> 379