Care2007/modules/weberp/PDFCustomerList.php

   1 <?php
   2 /* $Revision: 1.9 $ */
   3
   4 $PageSecurity = 2;
   5 include('includes/session.inc');
   6
   7 if (isset($_POST['PrintPDF'])){
   8
   9         include('includes/PDFStarter.php');
  10
  11         if ($_POST['Activity']!='All'){
  12                 if (!is_numeric($_POST['ActivityAmount'])){
  13                         $title = _('Customer List') . ' - ' . _('Problem Report') . '....';
  14                         include('includes/header.inc');
  15                         echo '<P>';
  16                         prnMsg( _('The acitivty amount is not numeric and you elected to print customer relative to a certain amount of activity') . ' - ' . _('this level of activity must be specified in the local currency') .'.', 'error');
  17                         include('includes/footer.inc');
  18                         exit;
  19                 }
  20         }
  21
  22         $PageNumber = 0;
  23
  24         $FontSize=10;
  25         $pdf->addinfo('Title', _('Customer Listing') );
  26         $pdf->addinfo('Subject', _('Customer Listing') );
  27
  28         $line_height=12;
  29
  30         /* Now figure out the customer data to report for the selections made */
  31
  32         if (in_array('All', $_POST['Areas'])){
  33                 if (in_array('All', $_POST['SalesPeople'])){
  34                         $SQL = 'SELECT debtorsmaster.debtorno,
  35                                         debtorsmaster.name,
  36                                         debtorsmaster.address1,
  37                                         debtorsmaster.address2,
  38                                         debtorsmaster.address3,
  39                                         debtorsmaster.address4,
  40                                         debtorsmaster.address5,
  41                                         debtorsmaster.address6,
  42                                         debtorsmaster.salestype,
  43                                         custbranch.branchcode,
  44                                         custbranch.brname,
  45                                         custbranch.braddress1,
  46                                         custbranch.braddress2,
  47                                         custbranch.braddress3,
  48                                         custbranch.braddress4,
  49                                         custbranch.braddress5,
  50                                         custbranch.braddress6,
  51                                         custbranch.contactname,
  52                                         custbranch.phoneno,
  53                                         custbranch.faxno,
  54                                         custbranch.email,
  55                                         custbranch.area,
  56                                         custbranch.salesman,
  57                                         areas.areadescription,
  58                                         salesman.salesmanname
  59                                 FROM debtorsmaster INNER JOIN custbranch
  60                                 ON debtorsmaster.debtorno=custbranch.debtorno
  61                                 INNER JOIN areas
  62                                 ON custbranch.area = areas.areacode
  63                                 INNER JOIN salesman
  64                                 ON custbranch.salesman=salesman.salesmancode
  65                                 ORDER BY area,
  66                                         salesman,
  67                                         debtorsmaster.debtorno,
  68                                         custbranch.branchcode';
  69                 } else {
  70                 /* there are a range of salesfolk selected need to build the where clause */
  71                         $SQL = 'SELECT debtorsmaster.debtorno,
  72                                         debtorsmaster.name,
  73                                         debtorsmaster.address1,
  74                                         debtorsmaster.address2,
  75                                         debtorsmaster.address3,
  76                                         debtorsmaster.address4,
  77                                         debtorsmaster.address5,
  78                                         debtorsmaster.address6,
  79                                         debtorsmaster.salestype,
  80                                         custbranch.branchcode,
  81                                         custbranch.brname,
  82                                         custbranch.braddress1,
  83                                         custbranch.braddress2,
  84                                         custbranch.braddress3,
  85                                         custbranch.braddress4,
  86                                         custbranch.braddress5,
  87                                         custbranch.braddress6,
  88                                         custbranch.contactname,
  89                                         custbranch.phoneno,
  90                                         custbranch.faxno,
  91                                         custbranch.email,
  92                                         custbranch.area,
  93                                         custbranch.salesman,
  94                                         areas.areadescription,
  95                                         salesman.salesmanname
  96                                 FROM debtorsmaster INNER JOIN custbranch
  97                                 ON debtorsmaster.debtorno=custbranch.debtorno
  98                                 INNER JOIN areas
  99                                 ON custbranch.area = areas.areacode
 100                                 INNER JOIN salesman
 101                                 ON custbranch.salesman=salesman.salesmancode
 102                                 WHERE (';
 103
 104                                 $i=0;
 105                                 foreach ($_POST['SalesPeople'] as $Salesperson){
 106                                         if ($i>0){
 107                                                 $SQL .= ' OR ';
 108                                         }
 109                                         $i++;
 110                                         $SQL .= "custbranch.salesman='" . $Salesperson ."'";
 111                                 }
 112
 113                                 $SQL .=') ORDER BY area,
 114                                                 salesman,
 115                                                 debtorsmaster.debtorno,
 116                                                 custbranch.branchcode';
 117                 } /*end if SalesPeople =='All' */
 118         } else { /* not all sales areas has been selected so need to build the where clause */
 119                 if (in_array('All', $_POST['SalesPeople'])){
 120                         $SQL = 'SELECT debtorsmaster.debtorno,
 121                                         debtorsmaster.name,
 122                                         debtorsmaster.address1,
 123                                         debtorsmaster.address2,
 124                                         debtorsmaster.address3,
 125                                         debtorsmaster.address4,
 126                                         debtorsmaster.address5,
 127                                         debtorsmaster.address6,
 128                                         debtorsmaster.salestype,
 129                                         custbranch.branchcode,
 130                                         custbranch.brname,
 131                                         custbranch.braddress1,
 132                                         custbranch.braddress2,
 133                                         custbranch.braddress3,
 134                                         custbranch.braddress4,
 135                                         custbranch.braddress5,
 136                                         custbranch.braddress6,
 137                                         custbranch.contactname,
 138                                         custbranch.phoneno,
 139                                         custbranch.faxno,
 140                                         custbranch.email,
 141                                         custbranch.area,
 142                                         custbranch.salesman,
 143                                         areas.areadescription,
 144                                         salesman.salesmanname
 145                                 FROM debtorsmaster INNER JOIN custbranch
 146                                 ON debtorsmaster.debtorno=custbranch.debtorno
 147                                 INNER JOIN areas
 148                                 ON custbranch.area = areas.areacode
 149                                 INNER JOIN salesman
 150                                 ON custbranch.salesman=salesman.salesmancode
 151                                 WHERE (';
 152
 153                         $i=0;
 154                         foreach ($_POST['Areas'] as $Area){
 155                                 if ($i>0){
 156                                         $SQL .= ' OR ';
 157                                 }
 158                                 $i++;
 159                                 $SQL .= "custbranch.area='" . $Area ."'";
 160                         }
 161
 162                         $SQL .= ') ORDER BY custbranch.area,
 163                                         custbranch.salesman,
 164                                         debtorsmaster.debtorno,
 165                                         custbranch.branchcode';
 166                 } else {
 167                 /* there are a range of salesfolk selected need to build the where clause */
 168                         $SQL = 'SELECT debtorsmaster.debtorno,
 169                                         debtorsmaster.name,
 170                                         debtorsmaster.address1,
 171                                         debtorsmaster.address2,
 172                                         debtorsmaster.address3,
 173                                         debtorsmaster.address4,
 174                                         debtorsmaster.address5,
 175                                         debtorsmaster.address6,
 176                                         debtorsmaster.salestype,
 177                                         custbranch.branchcode,
 178                                         custbranch.brname,
 179                                         custbranch.braddress1,
 180                                         custbranch.braddress2,
 181                                         custbranch.braddress3,
 182                                         custbranch.braddress4,
 183                                         custbranch.braddress5,
 184                                         custbranch.braddress6,
 185                                         custbranch.contactname,
 186                                         custbranch.phoneno,
 187                                         custbranch.faxno,
 188                                         custbranch.email,
 189                                         custbranch.area,
 190                                         custbranch.salesman,
 191                                         areas.areadescription,
 192                                         salesman.salesmanname
 193                                 FROM debtorsmaster INNER JOIN custbranch
 194                                 ON debtorsmaster.debtorno=custbranch.debtorno
 195                                 INNER JOIN areas
 196                                 ON custbranch.area = areas.areacode
 197                                 INNER JOIN salesman
 198                                 ON custbranch.salesman=salesman.salesmancode
 199                                 WHERE (';
 200
 201                         $i=0;
 202                         foreach ($_POST['Areas'] as $Area){
 203                                 if ($i>0){
 204                                         $SQL .= ' OR ';
 205                                 }
 206                                 $i++;
 207                                 $SQL .= "custbranch.area='" . $Area ."'";
 208                         }
 209
 210                         $SQL .= ') AND (';
 211
 212                         $i=0;
 213                         foreach ($_POST['SalesPeople'] as $Salesperson){
 214                                 if ($i>0){
 215                                         $SQL .= ' OR ';
 216                                 }
 217                                 $i++;
 218                                 $SQL .= "custbranch.salesman='" . $Salesperson ."'";
 219                         }
 220
 221                         $SQL .=') ORDER BY custbranch.area,
 222                                         custbranch.salesman,
 223                                         debtorsmaster.debtorno,
 224                                         custbranch.branchcode';
 225                 } /*end if Salesfolk =='All' */
 226
 227         } /* end if not all sales areas was selected */
 228
 229
 230         $CustomersResult = DB_query($SQL,$db);
 231
 232         if (DB_error_no($db) !=0) {
 233           $title = _('Customer List') . ' - ' . _('Problem Report') . '....';
 234           include('includes/header.inc');
 235            prnMsg( _('The customer List could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db) );
 236            echo '<BR><A HREF="' .$rootpath .'/index.php?' . SID . '">'. _('Back to the menu'). '</A>';
 237            if ($debug==1){
 238               echo '<BR>'. $SQL;
 239            }
 240            include('includes/footer.inc');
 241            exit;
 242         }
 243
 244         include('includes/PDFCustomerListPageHeader.inc');
 245
 246         $Area ='';
 247         $SalesPerson='';
 248
 249         While ($Customers = DB_fetch_array($CustomersResult,$db)){
 250
 251                 if ($_POST['Activity']!='All'){
 252
 253                         /*Get the total turnover in local currency for the customer/branch
 254                         since the date entered */
 255
 256                         $SQL = "SELECT SUM((ovamount+ovfreight+ovdiscount)/rate) AS turnover
 257                                 FROM debtortrans
 258                                 WHERE debtorno='" . $Customers['debtorno'] . "'
 259                                 AND branchcode='" . $Customers['branchcode'] . "'
 260                                 AND (type=10 or type=11)
 261                                 AND trandate >='" . FormatDateForSQL($_POST['ActivitySince']). "'";
 262                         $ActivityResult = DB_query($SQL, $db, _('Could not retrieve the activity of the branch because'), _('The failed SQL was'));
 263
 264                         $ActivityRow = DB_fetch_row($ActivityResult);
 265                         $LocalCurrencyTurnover = $ActivityRow[0];
 266
 267                         if ($_POST['Activity'] =='GreaterThan'){
 268                                 if ($LocalCurrencyTurnover > $_POST['ActivityAmount']){
 269                                         $PrintThisCustomer = true;
 270                                 } else {
 271                                         $PrintThisCustomer = false;
 272                                 }
 273                         } elseif ($_POST['Activity'] =='LessThan'){
 274                                 if ($LocalCurrencyTurnover < $_POST['ActivityAmount']){
 275                                         $PrintThisCustomer = true;
 276                                 } else {
 277                                         $PrintThisCustomer = false;
 278                                 }
 279                         }
 280                 } else {
 281                         $PrintThisCustomer = true;
 282                 }
 283
 284                 if ($PrintThisCustomer){
 285                         if ($Area!=$Customers['area']){
 286                                 $FontSize=10;
 287                                 $YPos -=$line_height;
 288                                 if ($YPos < ($Bottom_Margin + 80)){
 289                                         include('includes/PDFCustomerListPageHeader.inc');
 290                                 }
 291                                 $pdf->selectFont('./fonts/Helvetica-Bold.afm');
 292                                 $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,260-$Left_Margin,$FontSize,_('Customers in') . ' ' . $Customers['areadescription']);
 293                                 $Area = $Customers['area'];
 294                                 $pdf->selectFont('./fonts/Helvetica.afm');
 295                                 $FontSize=8;
 296                                 $YPos -=$line_height;
 297                         }
 298
 299                         if ($SalesPerson!=$Customers['salesman']){
 300                                 $FontSize=10;
 301                                 $YPos -=($line_height);
 302                                 if ($YPos < ($Bottom_Margin + 80)){
 303                                         include('includes/PDFCustomerListPageHeader.inc');
 304                                 }
 305                                 $pdf->selectFont('./fonts/Helvetica-Bold.afm');
 306                                 $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,300-$Left_Margin,$FontSize,$Customers['salesmanname']);
 307                                 $pdf->selectFont('./fonts/Helvetica.afm');
 308                                 $SalesPerson = $Customers['salesman'];
 309                                 $FontSize=8;
 310                                 $YPos -=$line_height;
 311                         }
 312
 313                         $YPos -=$line_height;
 314
 315
 316                         $LeftOvers = $pdf->addTextWrap(20,$YPos,60,$FontSize,$Customers['debtorno']);
 317                         $LeftOvers = $pdf->addTextWrap(80,$YPos,150,$FontSize,$Customers['name']);
 318                         $LeftOvers = $pdf->addTextWrap(80,$YPos-10,150,$FontSize,$Customers['address1']);
 319                         $LeftOvers = $pdf->addTextWrap(80,$YPos-20,150,$FontSize,$Customers['address2']);
 320                         $LeftOvers = $pdf->addTextWrap(80,$YPos-30,150,$FontSize,$Customers['address3']);
 321                         $LeftOvers = $pdf->addTextWrap(140,$YPos-30,150,$FontSize,$Customers['address4']);
 322                         $LeftOvers = $pdf->addTextWrap(180,$YPos-30,150,$FontSize,$Customers['address5']);
 323                         $LeftOvers = $pdf->addTextWrap(210,$YPos-30,150,$FontSize,$Customers['address6']);
 324
 325                         $LeftOvers = $pdf->addTextWrap(230,$YPos,60,$FontSize,$Customers['branchcode']);
 326                         $LeftOvers = $pdf->addTextWrap(230,$YPos-10,60,$FontSize, _('Price List') . ': ' . $Customers['salestype']);
 327
 328                         if ($_POST['Activity']!='All'){
 329                                 $LeftOvers = $pdf->addTextWrap(230,$YPos-20,60,$FontSize,_('Turnover'),'right');
 330                                 $LeftOvers = $pdf->addTextWrap(230,$YPos-30,60,$FontSize,number_format($LocalCurrencyTurnover), 'right');
 331                         }
 332
 333                         $LeftOvers = $pdf->addTextWrap(290,$YPos,150,$FontSize,$Customers['brname']);
 334                         $LeftOvers = $pdf->addTextWrap(290,$YPos-10,150,$FontSize,$Customers['contactname']);
 335                         $LeftOvers = $pdf->addTextWrap(290,$YPos-20,150,$FontSize, _('Ph'). ': ' . $Customers['phoneno']);
 336                         $LeftOvers = $pdf->addTextWrap(290,$YPos-30,150,$FontSize, _('Fax').': ' . $Customers['faxno']);
 337                         $LeftOvers = $pdf->addTextWrap(440,$YPos,150,$FontSize,$Customers['braddress1']);
 338                         $LeftOvers = $pdf->addTextWrap(440,$YPos-10,150,$FontSize,$Customers['braddress2']);
 339                         $LeftOvers = $pdf->addTextWrap(440,$YPos-20,150,$FontSize,$Customers['braddress3']);
 340                         $LeftOvers = $pdf->addTextWrap(500,$YPos-20,150,$FontSize,$Customers['braddress4']);
 341                         $LeftOvers = $pdf->addTextWrap(540,$YPos-20,150,$FontSize,$Customers['braddress5']);
 342                         $LeftOvers = $pdf->addTextWrap(570,$YPos-20,150,$FontSize,$Customers['braddress6']);
 343                         $LeftOvers = $pdf->addTextWrap(440,$YPos-30,150,$FontSize,$Customers['email']);
 344
 345                         $pdf->line($Page_Width-$Right_Margin, $YPos-32,$Left_Margin, $YPos-32);
 346
 347                         $YPos -=40;
 348                         if ($YPos < ($Bottom_Margin +30)){
 349                                 include('includes/PDFCustomerListPageHeader.inc');
 350                         }
 351                 } /*end if $PrintThisCustomer == true */
 352         } /*end while loop */
 353
 354
 355         $pdfcode = $pdf->output();
 356         $len = strlen($pdfcode);
 357
 358       if ($len<=20){
 359                 $title = _('Print Customer List Error');
 360                 include('includes/header.inc');
 361                 echo '<p>';
 362                 prnMsg( _('There were no customers to print out for the selections specified') );
 363                 echo '<BR><A HREF="'. $rootpath.' /index.php?' . SID . '">'. _('Back to the menu'). '</A>';
 364                 include('includes/footer.inc');
 365                 exit;
 366         } else {
 367                 header('Content-type: application/pdf');
 368                 header('Content-Length: ' . $len);
 369                 header('Content-Disposition: inline; filename=CustomerList.pdf');
 370                 header('Expires: 0');
 371                 header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 372                 header('Pragma: public');
 373
 374                 $pdf->Stream();
 375
 376         }
 377         exit;
 378
 379 } else {
 380
 381         $title = _('Customer Details Listing');
 382         include('includes/header.inc');
 383
 384         echo '<FORM ACTION=' . $_SERVER['PHP_SELF'] . ' METHOD="POST"><CENTER><TABLE>';
 385         echo '<TR><TD>' . _('For Sales Areas') . ':</TD><TD><SELECT name=Areas[] multiple>';
 386
 387         $sql='SELECT areacode, areadescription FROM areas';
 388         $AreasResult= DB_query($sql,$db);
 389
 390         echo '<OPTION SELECTED VALUE="All">' . _('All Areas');
 391
 392         While ($myrow = DB_fetch_array($AreasResult)){
 393                 echo '<OPTION VALUE="' . $myrow['areacode'] . '">' . $myrow['areadescription'];
 394         }
 395         echo '</SELECT></TD></TR>';
 396
 397         echo '<TR><TD>' . _('For Sales folk'). ':</TD><TD><SELECT name=SalesPeople[] multiple>';
 398
 399         echo '<OPTION SELECTED VALUE="All">'. _('All sales folk');
 400
 401         $sql = 'SELECT salesmancode, salesmanname FROM salesman';
 402         $SalesFolkResult = DB_query($sql,$db);
 403
 404         While ($myrow = DB_fetch_array($SalesFolkResult)){
 405                 echo '<OPTION VALUE="' . $myrow['salesmancode'] . '">' . $myrow['salesmanname'];
 406         }
 407         echo '</SELECT></TD></TR>';
 408
 409         echo '<TR><TD>' . _('Level Of Activity'). ':</TD><TD><SELECT name="Activity">';
 410
 411         echo '<OPTION SELECTED VALUE="All">'. _('All customers');
 412         echo '<OPTION VALUE="GreaterThan">'. _('Sales Greater Than');
 413         echo '<OPTION VALUE="LessThan">'. _('Sales Less Than');
 414         echo '</SELECT>';
 415
 416         echo '<INPUT TYPE="text" NAME="ActivityAmount" SIZE=8 MAXLENGTH=8></TD></TR>';
 417
 418         $DefaultActivitySince = Date($_SESSION['DefaultDateFormat'], Mktime(0,0,0,Date('m')-6,0,Date('y')));
 419         echo '<TR><TD>' . _('Activity Since'). ':</TD><TD><INPUT TYPE="text" NAME="ActivitySince" SIZE=10 MAXLENGTH=10
 420                 VALUE="' . $DefaultActivitySince . '"></TD></TR>';
 421
 422         echo '</TABLE><INPUT TYPE=Submit Name="PrintPDF" Value="'. _('Print PDF'). '"></CENTER>';
 423
 424         include('includes/footer.inc');
 425
 426 } /*end of else not PrintPDF */
 427 ?>