Care2007/modules/weberp/PDFStockCheckComparison.php

   1 <?php
   2 /* $Revision: 1.10 $ */
   3 $PageSecurity = 2;
   4 include('includes/session.inc');
   5
   6 If (isset($_POST['PrintPDF']) AND isset($_POST['ReportOrClose'])){
   7
   8         include('includes/PDFStarter.php');
   9         include('includes/SQL_CommonFunctions.inc');
  10
  11
  12 /*First off do the Inventory Comparison file stuff */
  13         if ($_POST['ReportOrClose']=='ReportAndClose'){
  14
  15                 $sql = "SELECT stockcheckfreeze.stockid,
  16                                 stockcheckfreeze.loccode,
  17                                 qoh,
  18                                 materialcost+labourcost+overheadcost AS standardcost
  19                         FROM stockmaster INNER JOIN stockcheckfreeze
  20                                 ON stockcheckfreeze.stockid=stockmaster.stockid
  21                         ORDER BY stockcheckfreeze.loccode, stockcheckfreeze.stockid";
  22
  23                 $StockChecks = DB_query($sql, $db,'','',false,false);
  24                 if (DB_error_no($db) !=0) {
  25                         $title = _('Stock Freeze') . ' - ' . _('Problem Report') . '....';
  26                         include('includes/header.inc');
  27                         echo '<BR>';
  28                         prnMsg( _('The inventory check file could not be retreived because'). ' - ' . DB_error_msg($db),'error');
  29                         echo '<BR><A HREF="' .$rootpath .'/index.php?' . SID . '">'. _('Back to the menu').'</A>';
  30                         if ($debug==1){
  31                                 echo '<BR>' . $sql;
  32                         }
  33                         include('includes/footer.inc');
  34                         exit;
  35                 }
  36
  37                 $PeriodNo = GetPeriod (Date($_SESSION['DefaultDateFormat']), $db);
  38                 $SQLAdjustmentDate = FormatDateForSQL(Date($_SESSION['DefaultDateFormat']));
  39                 $AdjustmentNumber = GetNextTransNo(17,$db);
  40
  41                 while ($myrow = DB_fetch_array($StockChecks)){
  42
  43                         $sql = "SELECT SUM(stockcounts.qtycounted) AS totcounted,
  44                                         COUNT(stockcounts.stockid) AS noofcounts
  45                                 FROM stockcounts
  46                                 WHERE stockcounts.stockid='" . $myrow['stockid'] . "'
  47                                 AND stockcounts.loccode='" . $myrow['loccode'] . "'";
  48
  49                         $StockCounts = DB_query($sql, $db);
  50                         if (DB_error_no($db) !=0) {
  51                                 $title = _('Stock Count Comparison') . ' - ' . _('Problem Report') . '....';
  52                                 include('includes/header.inc');
  53                                 echo '<BR>';
  54                                 prnMsg( _('The inventory counts file could not be retreived because'). ' - ' . DB_error_msg($db). 'error');
  55                                 echo '<BR><A HREF="' .$rootpath .'/index.php?' . SID . '">'. _('Back to the menu').'</A>';
  56                                 if ($debug==1){
  57                                         echo '<BR>'. $sql;
  58                                 }
  59                                 include('includes/footer.inc');
  60                                 exit;
  61                         }
  62
  63                         $StkCountResult = DB_query($sql,$db);
  64                         $StkCountRow = DB_fetch_array($StkCountResult);
  65
  66                         $StockQtyDifference = $StkCountRow['totcounted'] - $myrow['qoh'];
  67
  68                         if ($_POST['ZeroCounts']=='Leave' AND $StkCountRow['noofcounts']==0){
  69                                 $StockQtyDifference =0;
  70                         }
  71
  72                         if ($StockQtyDifference !=0){ // only adjust stock if there is an adjustment to make!!
  73
  74                                 $SQL = 'BEGIN';
  75                                 $Result = DB_query($SQL,$db);
  76
  77                                 // Need to get the current location quantity will need it later for the stock movement
  78                                 $SQL="SELECT locstock.quantity
  79                                                 FROM locstock
  80                                         WHERE locstock.stockid='" . $myrow['stockid'] . "'
  81                                         AND loccode= '" . $myrow['loccode'] . "'";
  82
  83                                 $Result = DB_query($SQL, $db);
  84                                 if (DB_num_rows($Result)==1){
  85                                         $LocQtyRow = DB_fetch_row($Result);
  86                                         $QtyOnHandPrior = $LocQtyRow[0];
  87                                 } else {
  88                                         // There must actually be some error this should never happen
  89                                         $QtyOnHandPrior = 0;
  90                                 }
  91
  92                                 $SQL = "INSERT INTO stockmoves (stockid,
  93                                                                 type,
  94                                                                 transno,
  95                                                                 loccode,
  96                                                                 trandate,
  97                                                                 prd,
  98                                                                 reference,
  99                                                                 qty,
 100                                                                 newqoh)
 101                                                 VALUES ('" . $myrow['stockid'] . "',
 102                                                         17,
 103                                                         " . $AdjustmentNumber . ",
 104                                                         '" . $myrow['loccode'] . "',
 105                                                         '" . $SQLAdjustmentDate . "',
 106                                                         " . $PeriodNo . ",
 107                                                         '" . _('Inventory Check') . "',
 108                                                         " . $StockQtyDifference . ",
 109                                                         " . ($QtyOnHandPrior + $StockQtyDifference) . "
 110                                                 )";
 111
 112                                 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock movement record cannot be inserted because');
 113                                 $DbgMsg = _('The following SQL to insert the stock movement record was used');
 114                                 $Result = DB_query($SQL,$db, $ErrMsg, $DbgMsg, true);
 115
 116                                 $SQL = "UPDATE locstock
 117                                                 SET quantity = quantity + " . $StockQtyDifference . "
 118                                                 WHERE stockid='" . $myrow['stockid'] . "'
 119                                                 AND loccode='" . $myrow['loccode'] . "'";
 120                                 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
 121                                 $DbgMsg = _('The following SQL to update the stock record was used');
 122                                 $Result = DB_query($SQL,$db, $ErrMsg, $DbgMsg, true);
 123
 124                                 if ($_SESSION['CompanyRecord']['gllink_stock']==1 AND $myrow['standardcost'] > 0){
 125
 126                                         $StockGLCodes = GetStockGLCode($myrow['stockid'],$db);
 127                                         $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
 128                                         $DbgMsg = _('The following SQL to insert the GL entries was used');
 129
 130                                         $SQL = "INSERT INTO gltrans (type,
 131                                                                         typeno,
 132                                                                         trandate,
 133                                                                         periodno,
 134                                                                         account,
 135                                                                         amount,
 136                                                                         narrative)
 137                                                         VALUES (17,
 138                                                                 " .$AdjustmentNumber . ",
 139                                                                 '" . $SQLAdjustmentDate . "',
 140                                                                 " . $PeriodNo . ",
 141                                                                 " .  $StockGLCodes['adjglact'] . ",
 142                                                                 " . $myrow['standardcost'] * -($StockQtyDifference) . ",
 143                                                                 '" . $myrow['stockid'] . " x " . $StockQtyDifference . " @ " . $myrow['standardcost'] . " - " . _('Inventory Check') . "')";
 144                                         $Result = DB_query($SQL,$db, $ErrMsg, $DbgMsg, true);
 145
 146                                         $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
 147                                         $DbgMsg = _('The following SQL to insert the GL entries was used');
 148
 149                                         $SQL = "INSERT INTO gltrans (type,
 150                                                                         typeno,
 151                                                                         trandate,
 152                                                                         periodno,
 153                                                                         account,
 154                                                                         amount,
 155                                                                         narrative)
 156                                                         VALUES (17,
 157                                                                 " .$AdjustmentNumber . ",
 158                                                                 '" . $SQLAdjustmentDate . "',
 159                                                                 " . $PeriodNo . ",
 160                                                                 " .  $StockGLCodes['stockact'] . ",
 161                                                                 " . $myrow['standardcost'] * $StockQtyDifference . ", '" . $myrow['stockid'] . " x " . $StockQtyDifference . " @ " . $myrow['standardcost'] . " - " . _('Inventory Check') . "')";
 162                                         $Result = DB_query($SQL,$db, $ErrMsg, $DbgMsg, true);
 163
 164                                 } //END INSERT GL TRANS
 165                                 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Unable to COMMIT transaction while adjusting stock in StockCheckAdjustmet report');
 166                                 $SQL = "COMMIT";
 167                                 $Result = DB_query($SQL,$db, $ErrMsg,'',true);
 168
 169                         } // end if $StockQtyDifference !=0
 170
 171                 } // end loop round all the checked parts
 172         } // end user wanted to close the inventory check file and do the adjustments
 173
 174         // now do the report
 175         $ErrMsg = _('The Inventory Comparison data could not be retrieved because');
 176         $DbgMsg = _('The following SQL to retrieve the Inventory Comparison data was used');
 177         $sql = "SELECT stockcheckfreeze.stockid,
 178                         description,
 179                         stockmaster.categoryid,
 180                         stockcategory.categorydescription,
 181                         stockcheckfreeze.loccode,
 182                         locations.locationname,
 183                         stockcheckfreeze.qoh
 184                         FROM stockcheckfreeze INNER JOIN stockmaster
 185                                 ON stockcheckfreeze.stockid=stockmaster.stockid
 186                         INNER JOIN locations
 187                                 ON stockcheckfreeze.loccode=locations.loccode
 188                         INNER JOIN stockcategory
 189                                 ON stockmaster.categoryid=stockcategory.categoryid
 190                         ORDER BY stockcheckfreeze.loccode,
 191                                 stockmaster.categoryid,
 192                                 stockcheckfreeze.stockid";
 193
 194         $CheckedItems = DB_query($sql,$db, $ErrMsg, $DbgMsg);
 195
 196         if (DB_num_rows($CheckedItems)==0){
 197                 include('includes/header.inc');
 198                 echo '<p>';
 199                 prnMsg( _('There is no inventory check data to report on', 'warn') );
 200                 echo '<p>'. _('To start an inventory check first run the'). ' <a href="' . $rootpath . '/StockCheck.php?' . SID . '">'. _('inventory check sheets') . '</A> - '. _('and select the option to create new Inventory Comparison data file');
 201                 include('includes/footer.inc');
 202                 exit;
 203         }
 204
 205         $pdf->addinfo('Title', _('Check Comparison Report') );
 206         $pdf->addinfo('Subject', _('Inventory Check Comparision'). ' ' . Date($_SESSION['DefaultDateFormat']));
 207
 208
 209         $PageNumber=1;
 210         $line_height=15;
 211
 212         include ('includes/PDFStockComparisonPageHeader.inc');
 213
 214         $Location = '';
 215         $Category = '';
 216
 217         While ($CheckItemRow = DB_fetch_array($CheckedItems,$db)){
 218
 219                 if ($Location!=$CheckItemRow['loccode']){
 220                         $FontSize=14;
 221                         if ($Location!=''){ /*Then it's NOT the first time round */
 222                                 /*draw a line under the Location*/
 223                                 $pdf->line($Left_Margin, $YPos-2,$Page_Width-$Right_Margin, $YPos-2);
 224                                 $YPos -=$line_height;
 225                         }
 226
 227                         $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,260-$Left_Margin,$FontSize,$CheckItemRow['loccode'] . ' - ' . $CheckItemRow['locationname'], 'left');
 228                         $Location = $CheckItemRow['loccode'];
 229                         $YPos -=$line_height;
 230                 }
 231
 232
 233                 if ($Category!=$CheckItemRow['categoryid']){
 234                         $FontSize=12;
 235                         if ($Category!=''){ /*Then it's NOT the first time round */
 236                                 /*draw a line under the CATEGORY TOTAL*/
 237                                 $pdf->line($Left_Margin, $YPos-2,$Page_Width-$Right_Margin, $YPos-2);
 238                                 $YPos -=$line_height;
 239                         }
 240
 241                         $LeftOvers = $pdf->addTextWrap($Left_Margin+15,$YPos,260-$Left_Margin,$FontSize,$CheckItemRow['categoryid'] . ' - ' . $CheckItemRow['categorydescription'], 'left');
 242                         $Category = $CheckItemRow['categoryid'];
 243                         $YPos -=$line_height;
 244                 }
 245
 246                 $YPos -=$line_height;
 247                 $FontSize=8;
 248
 249                 $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,120,$FontSize,$CheckItemRow['stockid'], 'left');
 250                 $LeftOvers = $pdf->addTextWrap(135,$YPos,180,$FontSize,$CheckItemRow['description'], 'left');
 251                 $LeftOvers = $pdf->addTextWrap(315,$YPos,60,$FontSize,$CheckItemRow['qoh'], 'right');
 252
 253                 $SQL = "SELECT qtycounted, reference FROM stockcounts WHERE loccode ='" . $Location . "' AND stockid = '" . $CheckItemRow['stockid'] . "'";
 254
 255                 $Counts = DB_query($SQL,$db,'','',false,false);
 256
 257                 if (DB_error_no($db) !=0) {
 258                         $title = _('Inventory Comparison') . ' - ' . _('Problem Report') . '.... ';
 259                         include('includes/header.inc');
 260                         echo '<BR>';
 261                         prnMsg( _('The inventory counts could not be retrieved by the SQL because').' - ' . DB_error_msg($db), 'error');
 262                         echo '<BR><A HREF="' .$rootpath .'/index.php?' . SID . '">'. _('Back to the menu'). '</A>';
 263                         if ($debug==1){
 264                                 echo '<BR>'. $SQL;
 265                         }
 266                         include('includes/footer.inc');
 267                         exit;
 268                 }
 269
 270
 271                 if (DB_num_rows($Counts)==0){
 272                         $LeftOvers = $pdf->addTextWrap(380, $YPos,160, $FontSize, _('No counts entered'), 'left');
 273                         if ($_POST['ZeroCounts']=='Adjust'){
 274                                 $LeftOvers = $pdf->addTextWrap(485, $YPos, 60, $FontSize, -($CheckItemRow['qoh']), 'right');
 275                         }
 276                 } else {
 277                         $TotalCount =0;
 278                         while ($CountRow=DB_fetch_array($Counts,$db)){
 279
 280                                 $LeftOvers = $pdf->addTextWrap(375, $YPos, 60, $FontSize, ($CountRow['qtycounted']), 'right');
 281                                 $LeftOvers = $pdf->addTextWrap(440, $YPos, 100, $FontSize, $CountRow['reference'], 'left');
 282                                 $TotalCount += $CountRow['qtycounted'];
 283                                 $YPos -= $line_height;
 284
 285                                 if ($YPos < $Bottom_Margin + $line_height){
 286                                         $PageNumber++;
 287                                         include('includes/PDFStockComparisonPageHeader.inc');
 288                                 }
 289                         } // end of loop printing count information
 290                         $LeftOvers = $pdf->addTextWrap($LeftMargin, $YPos, 375-$LeftMargin, $FontSize, _('Total for') . ': ' . $CheckItemRow['stockid'], 'right');
 291                         $LeftOvers = $pdf->addTextWrap(375, $YPos, 60, $FontSize, $TotalCount, 'right');
 292                         $LeftOvers = $pdf->addTextWrap(485, $YPos, 60, $FontSize, $TotalCount-$CheckItemRow['qoh'], 'right');
 293                 } //end of if there are counts to print
 294
 295                 $pdf->line($Left_Margin, $YPos-2,$Page_Width-$Right_Margin, $YPos-2);
 296
 297                 if ($YPos < $Bottom_Margin + $line_height){
 298                    $PageNumber++;
 299                    include('includes/PDFStockComparisonPageHeader.inc');
 300                 }
 301
 302         } /*end STOCK comparison while loop */
 303
 304         $YPos -= (2*$line_height);
 305
 306         $pdfcode = $pdf->output();
 307         $len = strlen($pdfcode);
 308
 309       if ($len<=20){
 310                 $title = _('Print Stock check comparison Error');
 311                 include('includes/header.inc');
 312                 echo '<p>';
 313                 prnMsg( _('There were no Inventory Comparison sheets to print out'), 'error');
 314                 echo '<BR><A HREF="' . $rootpath . '/index.php?' . SID . '">' . _('Back to the menu') . '</A>';
 315                 include('includes/footer.inc');
 316                 exit;
 317       } else {
 318                 header('Content-type: application/pdf');
 319                 header('Content-Length: ' . $len);
 320                 header('Content-Disposition: inline; filename=StockComparison.pdf');
 321                 header('Expires: 0');
 322                 header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 323                 header('Pragma: public');
 324
 325                 $pdf->Stream();
 326
 327         }
 328
 329         if ($_POST['ReportOrClose']=='ReportAndClose'){
 330                 //need to print the report first before this but don't risk re-adjusting all the stock!!
 331                 $sql = 'TRUNCATE TABLE stockcheckfreeze';
 332                 $result = DB_query($sql,$db);
 333
 334                 $sql = 'TRUNCATE TABLE stockcounts';
 335                 $result = DB_query($sql,$db);
 336         }
 337
 338
 339
 340 } else { /*The option to print PDF was not hit */
 341
 342         $title= _('Inventory Comparison Comparison Report');
 343         include('includes/header.inc');
 344
 345         echo '<FORM ACTION="' . $_SERVER['PHP_SELF'] . '" METHOD="POST"><CENTER><TABLE>';
 346
 347         echo '<TR><TD>' . _('Choose Option'). ':</FONT></TD><TD><SELECT name="ReportOrClose">';
 348
 349         if ($_POST['ReportOrClose']=='ReportAndClose'){
 350                 echo '<OPTION SELECTED VALUE="ReportAndClose">'. _('Report and Close the Inventory Comparison Processing Adjustments As Necessary');
 351                 echo '<OPTION VALUE="ReportOnly">'. _('Report The Inventory Comparison Differences Only - No Adjustments');
 352         } else {
 353                 echo '<OPTION SELECTED VALUE="ReportOnly">' . _('Report The Inventory Comparison Differences Only - No Adjustments');
 354                 echo '<OPTION VALUE="ReportAndClose">' . _('Report and Close the Inventory Comparison Processing Adjustments As Necessary');
 355         }
 356
 357         echo '</SELECT></TD></TR>';
 358
 359
 360         echo '<TR><TD>'. _('Action for Zero Counts') . ':</TD><TD><SELECT name="ZeroCounts">';
 361
 362         if ($_POST['ZeroCounts'] =='Adjust'){
 363                 echo '<OPTION SELECTED VALUE="Adjust">'. _('Adjust System stock to Nil');
 364                 echo '<OPTION VALUE="Leave">' . _("Don't Adjust System stock to Nil");
 365         } else {
 366                 echo '<OPTION VALUE="Adjust">'. _('Adjust System stock to Nil');
 367                 echo '<OPTION SELECTED VALUE="Leave">' . _("Don't Adjust System stock to Nil");
 368         }
 369
 370         echo '</TABLE><INPUT TYPE=Submit Name="PrintPDF" Value="' . _('Print PDF'). '"></CENTER>';
 371
 372         include('includes/footer.inc');
 373
 374 } /*end of else not PrintPDF */
 375
 376 ?>