1<?php 2 3 4include('includes/session.php'); 5 6if (isset($_POST['PrintPDF']) 7 AND isset($_POST['FromCriteria']) 8 AND mb_strlen($_POST['FromCriteria'])>=1 9 AND isset($_POST['ToCriteria']) 10 AND mb_strlen($_POST['ToCriteria'])>=1){ 11 12 include('includes/PDFStarter.php'); 13 $pdf->addInfo('Title',_('Customer Balance Listing')); 14 $pdf->addInfo('Subject',_('Customer Balances')); 15 $FontSize=12; 16 $PageNumber=0; 17 $line_height=12; 18 19 /*Get the date of the last day in the period selected */ 20 21 $SQL = "SELECT lastdate_in_period FROM periods WHERE periodno = '" . $_POST['PeriodEnd']."'"; 22 $PeriodEndResult = DB_query($SQL,_('Could not get the date of the last day in the period selected')); 23 $PeriodRow = DB_fetch_row($PeriodEndResult); 24 $PeriodEndDate = ConvertSQLDate($PeriodRow[0]); 25 26 /*Now figure out the aged analysis for the customer range under review */ 27 28 $SQL = "SELECT debtorsmaster.debtorno, 29 debtorsmaster.name, 30 currencies.currency, 31 currencies.decimalplaces, 32 SUM((debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc)/debtortrans.rate) AS balance, 33 SUM(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc) AS fxbalance, 34 SUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "' THEN 35 (debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount)/debtortrans.rate ELSE 0 END) AS afterdatetrans, 36 SUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "' 37 AND (debtortrans.type=11 OR debtortrans.type=12) THEN 38 debtortrans.diffonexch ELSE 0 END) AS afterdatediffonexch, 39 SUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "' THEN 40 debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount ELSE 0 END 41 ) AS fxafterdatetrans 42 FROM debtorsmaster INNER JOIN currencies 43 ON debtorsmaster.currcode = currencies.currabrev 44 INNER JOIN debtortrans 45 ON debtorsmaster.debtorno = debtortrans.debtorno 46 WHERE debtorsmaster.debtorno >= '" . $_POST['FromCriteria'] . "' 47 AND debtorsmaster.debtorno <= '" . $_POST['ToCriteria'] . "' 48 GROUP BY debtorsmaster.debtorno, 49 debtorsmaster.name, 50 currencies.currency, 51 currencies.decimalplaces"; 52 53 $CustomerResult = DB_query($SQL,'','',false,false); 54 55 if (DB_error_no() !=0) { 56 $Title = _('Customer Balances') . ' - ' . _('Problem Report'); 57 include('includes/header.php'); 58 prnMsg(_('The customer details could not be retrieved by the SQL because') . DB_error_msg(),'error'); 59 echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>'; 60 if ($debug==1){ 61 echo '<br />' . $SQL; 62 } 63 include('includes/footer.php'); 64 exit; 65 } 66 67 if (DB_num_rows($CustomerResult) == 0) { 68 $Title = _('Customer Balances') . ' - ' . _('Problem Report'); 69 include('includes/header.php'); 70 prnMsg(_('The customer details listing has no clients to report on'),'warn'); 71 echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>'; 72 include('includes/footer.php'); 73 exit; 74 } 75 76 include ('includes/PDFDebtorBalsPageHeader.inc'); 77 78 $TotBal=0; 79 80 while ($DebtorBalances = DB_fetch_array($CustomerResult)){ 81 82 $Balance = $DebtorBalances['balance'] - $DebtorBalances['afterdatetrans'] + $DebtorBalances['afterdatediffonexch'] ; 83 $FXBalance = $DebtorBalances['fxbalance'] - $DebtorBalances['fxafterdatetrans']; 84 85 if (abs($Balance)>0.009 OR ABS($FXBalance)>0.009) { 86 87 $DisplayBalance = locale_number_format($DebtorBalances['balance'] - $DebtorBalances['afterdatetrans'],$DebtorBalances['decimalplaces']); 88 $DisplayFXBalance = locale_number_format($DebtorBalances['fxbalance'] - $DebtorBalances['fxafterdatetrans'],$DebtorBalances['decimalplaces']); 89 90 $TotBal += $Balance; 91 92 $LeftOvers = $pdf->addTextWrap($Left_Margin+3,$YPos,220-$Left_Margin,$FontSize,$DebtorBalances['debtorno'] . 93 ' - ' . html_entity_decode($DebtorBalances['name'],ENT_QUOTES,'UTF-8'),'left'); 94 $LeftOvers = $pdf->addTextWrap(220,$YPos,60,$FontSize,$DisplayBalance,'right'); 95 $LeftOvers = $pdf->addTextWrap(280,$YPos,60,$FontSize,$DisplayFXBalance,'right'); 96 $LeftOvers = $pdf->addTextWrap(350,$YPos,100,$FontSize,$DebtorBalances['currency'],'left'); 97 98 99 $YPos -=$line_height; 100 if ($YPos < $Bottom_Margin + $line_height){ 101 include('includes/PDFDebtorBalsPageHeader.inc'); 102 } 103 } 104 } /*end customer aged analysis while loop */ 105 106 $YPos -=$line_height; 107 if ($YPos < $Bottom_Margin + (2*$line_height)){ 108 $PageNumber++; 109 include('includes/PDFDebtorBalsPageHeader.inc'); 110 } 111 112 $DisplayTotBalance = locale_number_format($TotBal,$_SESSION['CompanyRecord']['decimalplaces']); 113 114 $LeftOvers = $pdf->addTextWrap(50,$YPos,160,$FontSize,_('Total balances'),'left'); 115 $LeftOvers = $pdf->addTextWrap(220,$YPos,60,$FontSize,$DisplayTotBalance,'right'); 116 117 $pdf->OutputD($_SESSION['DatabaseName'] . '_DebtorBals_' . date('Y-m-d').'.pdf'); 118 $pdf->__destruct(); 119 120} else { /*The option to print PDF was not hit */ 121 122 $Title=_('Debtor Balances'); 123 124 $ViewTopic = 'ARReports'; 125 $BookMark = 'PriorMonthDebtors'; 126 127 include('includes/header.php'); 128 echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/customer.png" title="' . _('Search') . 129 '" alt="" />' . ' ' . $Title . '</p><br />'; 130 131 if (!isset($_POST['FromCriteria']) OR !isset($_POST['ToCriteria'])) { 132 133 /*if $FromCriteria is not set then show a form to allow input */ 134 135 echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post"> 136 <div>'; 137 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 138 139 echo '<table class="selection">'; 140 echo '<tr> 141 <td>' . _('From Customer Code') .':</td> 142 <td><input tabindex="1" type="text" maxlength="10" size="8" name="FromCriteria" required="required" data-type="no-illegal-chars" title="' . _('Enter a portion of the code of first customer to report') . '" value="1" /></td> 143 </tr> 144 <tr> 145 <td>' . _('To Customer Code') . ':</td> 146 <td><input tabindex="2" type="text" maxlength="10" size="8" name="ToCriteria" required="required" data-type="no-illegal-chars" title="' . _('Enter a portion of the code of last customer to report') . '" value="zzzzzz" /></td> 147 </tr> 148 <tr> 149 <td>' . _('Balances As At') . ':</td> 150 <td><select tabindex="3" name="PeriodEnd">'; 151 152 $sql = "SELECT periodno, lastdate_in_period FROM periods ORDER BY periodno DESC"; 153 $Periods = DB_query($sql,_('Could not retrieve period data because'),_('The SQL that failed to get the period data was')); 154 155 while ($myrow = DB_fetch_array($Periods)){ 156 157 echo '<option value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>'; 158 159 } 160 } 161 162 echo '</select></td> 163 </tr> 164 </table> 165 <br /> 166 <div class="centre"> 167 <input tabindex="5" type="submit" name="PrintPDF" value="' . _('Print PDF') . '" /> 168 </div> 169 </div> 170 </form>'; 171 172 include('includes/footer.php'); 173} /*end of else not PrintPDF */ 174 175?> 176