1<?php 2/*This page adds the total of allocation records and compares this to the recorded allocation total in DebtorTrans table */ 3 4include('includes/session.php'); 5$Title = _('Customer Allocations != DebtorTrans.Alloc'); 6include('includes/header.php'); 7 8/*First off get the DebtorTransID of all invoices where allocations dont agree to the recorded allocation */ 9$sql = "SELECT debtortrans.id, 10 debtortrans.debtorno, 11 debtortrans.transno, 12 ovamount+ovgst AS totamt, 13 SUM(custallocns.Amt) AS totalalloc, 14 debtortrans.alloc 15 FROM debtortrans INNER JOIN custallocns 16 ON debtortrans.id=custallocns.transid_allocto 17 WHERE debtortrans.type=10 18 GROUP BY debtortrans.ID, 19 debtortrans.type, 20 ovamount+ovgst, 21 debtortrans.alloc 22 HAVING SUM(custallocns.amt) < debtortrans.alloc - 1"; 23 24$result = DB_query($sql); 25 26if (DB_num_rows($result)==0){ 27 prnMsg(_('There are no inconsistencies with allocations') . ' - ' . _('all is well'),'info'); 28} 29 30while ($myrow = DB_fetch_array($result)){ 31 $AllocToID = $myrow['id']; 32 33 echo '<br />' . _('Allocations made against') . ' ' . $myrow['debtorno'] . ' ' . _('Invoice Number') . ': ' . $myrow['transno']; 34 echo '<br />' . _('Original Invoice Total') . ': '. $myrow['totamt']; 35 echo '<br />' . _('Total amount recorded as allocated against it') . ': ' . $myrow['alloc']; 36 echo '<br />' . _('Total of allocation records') . ': ' . $myrow['totalalloc']; 37 38 $sql = "SELECT type, 39 transno, 40 trandate, 41 debtortrans.debtorno, 42 reference, 43 debtortrans.rate, 44 ovamount+ovgst+ovfreight+ovdiscount AS totalamt, 45 custallocns.amt, 46 decimalplaces AS currdecimalplaces 47 FROM debtortrans INNER JOIN custallocns 48 ON debtortrans.id=custallocns.transid_allocfrom 49 INNER JOIN debtorsmaster ON 50 debtortrans.debtorno=debtorsmaster.debtorno 51 INNER JOIN currencies ON 52 debtorsmaster.currcode=currencies.currabrev 53 WHERE custallocns.transid_allocto='" . $AllocToID . "'"; 54 55 $ErrMsg = _('The customer transactions for the selected criteria could not be retrieved because'); 56 $TransResult = DB_query($sql,$ErrMsg); 57 58 echo '<table class="selection">'; 59 60 $tableheader = '<tr> 61 <th>' . _('Type') . '</th> 62 <th>' . _('Number') . '</th> 63 <th>' . _('Reference') . '</th> 64 <th>' . _('Ex Rate') . '</th> 65 <th>' . _('Amount') . '</th> 66 <th>' . _('Alloc') . '</th></tr>'; 67 echo $tableheader; 68 69 $RowCounter = 1; 70 $AllocsTotal = 0; 71 72 while ($myrow1=DB_fetch_array($TransResult)) { 73 74 if ($myrow1['type']==11){ 75 $TransType = _('Credit Note'); 76 } else { 77 $TransType = _('Receipt'); 78 } 79 $CurrDecimalPlaces = $myrow1['currdecimalplaces']; 80 81 printf( '<tr class="striped_row"> 82 <td>%s</td> 83 <td>%s</td> 84 <td>%s</td> 85 <td>%s</td> 86 <td class="number">%s</td> 87 <td class="number">%s</td> 88 </tr>', 89 $TransType, 90 $myrow1['transno'], 91 $myrow1['reference'], 92 locale_number_format($myrow1['exrate'],4), 93 locale_number_format($myrow1['totalamt'],$CurrDecimalPlaces), 94 locale_number_format($myrow1['amt'],$CurrDecimalPlaces)); 95 96 $RowCounter++; 97 If ($RowCounter == 12){ 98 $RowCounter=1; 99 echo $tableheader; 100 } 101 //end of page full new headings if 102 $AllocsTotal +=$myrow1['amt']; 103 } 104 //end of while loop 105 echo '<tr><td colspan="6" class="number">' . locale_number_format($AllocsTotal,$CurrDecimalPlaces) . '</td></tr>'; 106 echo '</table> 107 <br />'; 108} 109 110include('includes/footer.php'); 111 112?>