1<?php 2 3 4/*The supplier transaction uses the SuppTrans class to hold the information about the invoice 5the SuppTrans class contains an array of GRNs objects - containing details of GRNs for invoicing 6Also an array of GLCodes objects - only used if the AP - GL link is effective 7Also an array of shipment charges for charges to shipments to be apportioned accross the cost of stock items */ 8 9include('includes/DefineSuppTransClass.php'); 10include('includes/DefinePOClass.php'); //needed for auto receiving code 11 12/* Session started in header.php for password checking and authorisation level check */ 13include('includes/session.php'); 14 15$Title = _('Enter Supplier Invoice'); 16/* webERP manual links before header.php */ 17$ViewTopic= 'AccountsPayable'; 18$BookMark = 'SupplierInvoice'; 19include('includes/header.php'); 20include('includes/SQL_CommonFunctions.inc'); 21 22 23if (empty($_GET['identifier'])) { 24 $identifier=date('U'); 25} else { 26 $identifier=$_GET['identifier']; 27} 28 29if (!isset($_SESSION['SuppTrans']->SupplierName)) { 30 $sql="SELECT suppname FROM suppliers WHERE supplierid='" . $_GET['SupplierID'] . "'"; 31 $result = DB_query($sql); 32 $myrow = DB_fetch_row($result); 33 $SupplierName=$myrow[0]; 34 if (!isset($_SESSION['SuppTrans']->SupplierID)) { 35 $_SESSION['SuppTrans']->SupplierID = $_GET['SupplierID']; 36 } 37} else { 38 $SupplierName=$_SESSION['SuppTrans']->SupplierName; 39} 40echo '<p class="page_title_text"><img alt="" src="'.$RootPath . '/css/' . $Theme . 41 '/images/transactions.png" title="' . _('Supplier Invoice') . '" />' . ' ' . 42 _('Enter Supplier Invoice') . ': ' . $SupplierName . ' ' . $_SESSION['SuppTrans']->SupplierID . '</p>'; 43if (isset($_GET['SupplierID']) AND $_GET['SupplierID']!=''){ 44 45 /*It must be a new invoice entry - clear any existing invoice details from the SuppTrans object and initiate a newy*/ 46 if (isset( $_SESSION['SuppTrans'])){ 47 unset ( $_SESSION['SuppTrans']->GRNs); 48 unset ( $_SESSION['SuppTrans']->GLCodes); 49 unset($_SESSION['SuppTrans']->Assets); 50 unset ( $_SESSION['SuppTrans']); 51 } 52 53 if (isset( $_SESSION['SuppTransTmp'])){ 54 unset ( $_SESSION['SuppTransTmp']->GRNs); 55 unset ( $_SESSION['SuppTransTmp']->GLCodes); 56 unset ( $_SESSION['SuppTransTmp']); 57 } 58 $_SESSION['SuppTrans'] = new SuppTrans; 59 60/*Now retrieve supplier information - name, currency, default ex rate, terms, tax rate etc */ 61 62 $sql = "SELECT suppliers.suppname, 63 suppliers.supplierid, 64 paymentterms.terms, 65 paymentterms.daysbeforedue, 66 paymentterms.dayinfollowingmonth, 67 suppliers.currcode, 68 currencies.rate AS exrate, 69 currencies.decimalplaces, 70 suppliers.taxgroupid, 71 taxgroups.taxgroupdescription 72 FROM suppliers, 73 taxgroups, 74 currencies, 75 paymentterms, 76 taxauthorities 77 WHERE suppliers.taxgroupid=taxgroups.taxgroupid 78 AND suppliers.currcode=currencies.currabrev 79 AND suppliers.paymentterms=paymentterms.termsindicator 80 AND suppliers.supplierid = '" . $_GET['SupplierID'] . "'"; 81 82 $ErrMsg = _('The supplier record selected') . ': ' . $_GET['SupplierID'] . ' ' ._('cannot be retrieved because'); 83 $DbgMsg = _('The SQL used to retrieve the supplier details and failed was'); 84 85 $result = DB_query($sql, $ErrMsg, $DbgMsg); 86 87 $myrow = DB_fetch_array($result); 88 89 $_SESSION['SuppTrans']->SupplierName = $myrow['suppname']; 90 $_SESSION['SuppTrans']->TermsDescription = $myrow['terms']; 91 $_SESSION['SuppTrans']->CurrCode = $myrow['currcode']; 92 $_SESSION['SuppTrans']->ExRate = $myrow['exrate']; 93 $_SESSION['SuppTrans']->CurrDecimalPlaces = $myrow['decimalplaces']; 94 $_SESSION['SuppTrans']->TaxGroup = $myrow['taxgroupid']; 95 $_SESSION['SuppTrans']->TaxGroupDescription = $myrow['taxgroupdescription']; 96 $_SESSION['SuppTrans']->SupplierID = $myrow['supplierid']; 97 98 if ($myrow['daysbeforedue'] == 0){ 99 $_SESSION['SuppTrans']->Terms = '1' . $myrow['dayinfollowingmonth']; 100 } else { 101 $_SESSION['SuppTrans']->Terms = '0' . $myrow['daysbeforedue']; 102 } 103 $_SESSION['SuppTrans']->SupplierID = $_GET['SupplierID']; 104 105 $LocalTaxProvinceResult = DB_query("SELECT taxprovinceid 106 FROM locations 107 WHERE loccode = '" . $_SESSION['UserStockLocation'] . "'"); 108 109 if(DB_num_rows($LocalTaxProvinceResult)==0){ 110 prnMsg(_('The tax province associated with your user account has not been set up in this database. Tax calculations are based on the tax group of the supplier and the tax province of the user entering the invoice. The system administrator should redefine your account with a valid default stocking location and this location should refer to a valid tax province'),'error'); 111 include('includes/footer.php'); 112 exit; 113 } 114 115 $LocalTaxProvinceRow = DB_fetch_row($LocalTaxProvinceResult); 116 $_SESSION['SuppTrans']->LocalTaxProvince = $LocalTaxProvinceRow[0]; 117 118 $_SESSION['SuppTrans']->GetTaxes(); 119 120 121 $_SESSION['SuppTrans']->GLLink_Creditors = $_SESSION['CompanyRecord']['gllink_creditors']; 122 $_SESSION['SuppTrans']->GRNAct = $_SESSION['CompanyRecord']['grnact']; 123 $_SESSION['SuppTrans']->CreditorsAct = $_SESSION['CompanyRecord']['creditorsact']; 124 125 $_SESSION['SuppTrans']->InvoiceOrCredit = 'Invoice'; 126 127} elseif (!isset( $_SESSION['SuppTrans'])){ 128 129 prnMsg( _('To enter a supplier invoice the supplier must first be selected from the supplier selection screen'),'warn'); 130 echo '<br /><a href="' . $RootPath . '/SelectSupplier.php">' . _('Select A Supplier to Enter an Invoice For') . '</a>'; 131 include('includes/footer.php'); 132 exit; 133 134 /*It all stops here if there ain't no supplier selected */ 135} 136 137/* The code below automatically receives the outstanding balances on the purchase order ReceivePO and adds all the GRNs from that purchase order onto the invoice 138 * This is geared towards smaller businesses that have purchase orders that are automatically approved by users, and they want to enter the invoice directly based 139 * on the details entered in the purchase order screen. 140 */ 141if (isset($_GET['ReceivePO']) AND $_GET['ReceivePO']!=''){ 142 143 /*Need to check that the user has permission to receive goods */ 144 145 if (! in_array($_SESSION['PageSecurityArray']['GoodsReceived.php'], $_SESSION['AllowedPageSecurityTokens'])){ 146 prnMsg(_('Your permissions do not allow receiving of goods. Automatic receiving of purchase orders is restricted to those only users who are authorised to receive goods/services'),'error'); 147 } else { 148 /* The user has permission to receive goods then lets go */ 149 150 $_GET['ModifyOrderNumber'] = intval($_GET['ReceivePO']); 151 include('includes/PO_ReadInOrder.inc'); 152 153 if ($_SESSION['PO'.$identifier]->Status == 'Authorised'){ 154 $Result = DB_Txn_Begin(); 155 /*Now Get the next GRN - function in SQL_CommonFunctions*/ 156 $GRN = GetNextTransNo(25); 157 if (!isset($_GET['DeliveryDate'])){ 158 $DeliveryDate = date($_SESSION['DefaultDateFormat']); 159 } else { 160 $DeliveryDate = $_GET['DeliveryDate']; 161 } 162 $_POST['ExRate'] = $_SESSION['SuppTrans']->ExRate; 163 $_POST['TranDate'] = $DeliveryDate; 164 165 $PeriodNo = GetPeriod($DeliveryDate); 166 167 $OrderHasControlledItems = false; //assume the best 168 foreach ($_SESSION['PO'.$identifier]->LineItems as $OrderLine) { 169 //Set the quantity to receive with this auto delivery assuming all is well 170 $_SESSION['PO'.$identifier]->LineItems[$OrderLine->LineNo]->ReceiveQty = $OrderLine->Quantity - $OrderLine->QtyReceived; 171 172 if ($OrderLine->Controlled ==1) { // it's a controlled item - we can't deal with auto receiving controlled items!!! 173 prnMsg(_('Auto receiving of controlled stock items that require serial number or batch number entry is not currently catered for. Only orders with normal non-serial numbered items can be received automatically'),'error'); 174 $OrderHasControlledItems = true; 175 } 176 } 177 if ($OrderHasControlledItems == false){ 178 foreach ($_SESSION['PO'.$identifier]->LineItems as $OrderLine) { 179 $LocalCurrencyPrice = ($OrderLine->Price / $_SESSION['SuppTrans']->ExRate); 180 181 if ($OrderLine->StockID!='') { //Its a stock item line 182 /*Need to get the current standard cost as it is now so we can process GL jorunals later*/ 183 $SQL = "SELECT materialcost + labourcost + overheadcost as stdcost 184 FROM stockmaster 185 WHERE stockid='" . $OrderLine->StockID . "'"; 186 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The standard cost of the item being received cannot be retrieved because'); 187 $DbgMsg = _('The following SQL to retrieve the standard cost was used'); 188 $Result = DB_query($SQL,$ErrMsg,$DbgMsg,true); 189 190 $myrow = DB_fetch_row($Result); 191 $CurrentStandardCost = $myrow[0]; 192 193 if ($OrderLine->QtyReceived==0){ //its the first receipt against this line 194 $_SESSION['PO'.$identifier]->LineItems[$OrderLine->LineNo]->StandardCost = $CurrentStandardCost; 195 } 196 197 /*Set the purchase order line stdcostunit = weighted average / standard cost used for all receipts of this line 198 This assures that the quantity received against the purchase order line multiplied by the weighted average of standard 199 costs received = the total of standard cost posted to GRN suspense*/ 200 $_SESSION['PO'.$identifier]->LineItems[$OrderLine->LineNo]->StandardCost = (($CurrentStandardCost * $OrderLine->ReceiveQty) + ($_SESSION['PO'.$identifier]->LineItems[$OrderLine->LineNo]->StandardCost *$OrderLine->QtyReceived)) / ($OrderLine->ReceiveQty + $OrderLine->QtyReceived); 201 202 } elseif ($OrderLine->QtyReceived==0 AND $OrderLine->StockID=='') { 203 /*Its a nominal item being received */ 204 /*Need to record the value of the order per unit in the standard cost field to ensure GRN account entries clear */ 205 $_SESSION['PO'.$identifier]->LineItems[$OrderLine->LineNo]->StandardCost = $LocalCurrencyPrice; 206 } 207 208 if ($OrderLine->StockID=='') { /*Its a NOMINAL item line */ 209 $CurrentStandardCost = $_SESSION['PO'.$identifier]->LineItems[$OrderLine->LineNo]->StandardCost; 210 } 211 212 /*Now the SQL to do the update to the PurchOrderDetails */ 213 214 $SQL = "UPDATE purchorderdetails SET quantityrecd = quantityrecd + '" . $OrderLine->ReceiveQty . "', 215 stdcostunit='" . $_SESSION['PO'.$identifier]->LineItems[$OrderLine->LineNo]->StandardCost . "', 216 completed='1' 217 WHERE podetailitem = '" . $OrderLine->PODetailRec . "'"; 218 219 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The purchase order detail record could not be updated with the quantity received because'); 220 $DbgMsg = _('The following SQL to update the purchase order detail record was used'); 221 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true); 222 223 224 if ($OrderLine->StockID !=''){ /*Its a stock item so use the standard cost for the journals */ 225 $UnitCost = $CurrentStandardCost; 226 } else { /*otherwise its a nominal PO item so use the purchase cost converted to local currency */ 227 $UnitCost = $OrderLine->Price / $_SESSION['SuppTrans']->ExRate; 228 } 229 230 /*Need to insert a GRN item */ 231 232 $SQL = "INSERT INTO grns (grnbatch, 233 podetailitem, 234 itemcode, 235 itemdescription, 236 deliverydate, 237 qtyrecd, 238 supplierid, 239 stdcostunit) 240 VALUES ('" . $GRN . "', 241 '" . $OrderLine->PODetailRec . "', 242 '" . $OrderLine->StockID . "', 243 '" . DB_escape_string($OrderLine->ItemDescription) . "', 244 '" . FormatDateForSQL($DeliveryDate) . "', 245 '" . $OrderLine->ReceiveQty . "', 246 '" . $_SESSION['PO'.$identifier]->SupplierID . "', 247 '" . $CurrentStandardCost . "')"; 248 249 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('A GRN record could not be inserted') . '. ' . _('This receipt of goods has not been processed because'); 250 $DbgMsg = _('The following SQL to insert the GRN record was used'); 251 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true); 252 253 if ($OrderLine->StockID!=''){ /* if the order line is in fact a stock item */ 254 255 /* Update location stock records - NB a PO cannot be entered for a dummy/assembly/kit parts */ 256 257 /* Need to get the current location quantity will need it later for the stock movement */ 258 $SQL="SELECT locstock.quantity 259 FROM locstock 260 WHERE locstock.stockid='" . $OrderLine->StockID . "' 261 AND loccode= '" . $_SESSION['PO'.$identifier]->Location . "'"; 262 263 $Result = DB_query($SQL); 264 if (DB_num_rows($Result)==1){ 265 $LocQtyRow = DB_fetch_row($Result); 266 $QtyOnHandPrior = $LocQtyRow[0]; 267 } else { 268 /*There must actually be some error this should never happen */ 269 $QtyOnHandPrior = 0; 270 } 271 272 $SQL = "UPDATE locstock 273 SET quantity = locstock.quantity + '" . $OrderLine->ReceiveQty . "' 274 WHERE locstock.stockid = '" . $OrderLine->StockID . "' 275 AND loccode = '" . $_SESSION['PO'.$identifier]->Location . "'"; 276 277 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because'); 278 $DbgMsg = _('The following SQL to update the location stock record was used'); 279 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true); 280 281 /* Insert stock movements - with unit cost */ 282 283 $SQL = "INSERT INTO stockmoves (stockid, 284 type, 285 transno, 286 loccode, 287 trandate, 288 userid, 289 price, 290 prd, 291 reference, 292 qty, 293 standardcost, 294 newqoh) 295 VALUES ( 296 '" . $OrderLine->StockID . "', 297 25, 298 '" . $GRN . "', 299 '" . $_SESSION['PO'.$identifier]->Location . "', 300 '" . FormatDateForSQL($DeliveryDate) . "', 301 '" . $_SESSION['UserID'] . "', 302 '" . $LocalCurrencyPrice . "', 303 '" . $PeriodNo . "', 304 '" . $_SESSION['PO'.$identifier]->SupplierID . " (" . DB_escape_string($_SESSION['PO'.$identifier]->SupplierName) . ") - " .$_SESSION['PO'.$identifier]->OrderNo . "', 305 '" . $OrderLine->ReceiveQty . "', 306 '" . $_SESSION['PO'.$identifier]->LineItems[$OrderLine->LineNo]->StandardCost . "', 307 '" . ($QtyOnHandPrior + $OrderLine->ReceiveQty) . "' 308 )"; 309 310 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('stock movement records could not be inserted because'); 311 $DbgMsg = _('The following SQL to insert the stock movement records was used'); 312 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true); 313 314 } /*end of its a stock item - updates to locations and insert movements*/ 315 316 /* Check to see if the line item was flagged as the purchase of an asset */ 317 if ($OrderLine->AssetID !='' AND $OrderLine->AssetID !='0'){ //then it is an asset 318 319 /*first validate the AssetID and if it doesn't exist treat it like a normal nominal item */ 320 $CheckAssetExistsResult = DB_query("SELECT assetid, 321 datepurchased, 322 costact 323 FROM fixedassets 324 INNER JOIN fixedassetcategories 325 ON fixedassets.assetcategoryid=fixedassetcategories.categoryid 326 WHERE assetid='" . $OrderLine->AssetID . "'"); 327 if (DB_num_rows($CheckAssetExistsResult)==1){ //then work with the assetid provided 328 329 /*Need to add a fixedassettrans for the cost of the asset being received */ 330 $SQL = "INSERT INTO fixedassettrans (assetid, 331 transtype, 332 transno, 333 transdate, 334 periodno, 335 inputdate, 336 fixedassettranstype, 337 amount) 338 VALUES ('" . $OrderLine->AssetID . "', 339 25, 340 '" . $GRN . "', 341 '" . FormatDateForSQL($DeliveryDate) . "', 342 '" . $PeriodNo . "', 343 '" . Date('Y-m-d') . "', 344 '" . _('cost') . "', 345 '" . $CurrentStandardCost * $OrderLine->ReceiveQty . "')"; 346 $ErrMsg = _('CRITICAL ERROR! NOTE DOWN THIS ERROR AND SEEK ASSISTANCE The fixed asset transaction could not be inserted because'); 347 $DbgMsg = _('The following SQL to insert the fixed asset transaction record was used'); 348 $Result = DB_query($SQL,$ErrMsg, $DbgMsg, true); 349 350 /*Now get the correct cost GL account from the asset category */ 351 $AssetRow = DB_fetch_array($CheckAssetExistsResult); 352 /*Over-ride any GL account specified in the order with the asset category cost account */ 353 $_SESSION['PO'.$identifier]->LineItems[$OrderLine->LineNo]->GLCode = $AssetRow['costact']; 354 /*Now if there are no previous additions to this asset update the date purchased */ 355 if ($AssetRow['datepurchased']=='0000-00-00'){ 356 /* it is a new addition as the date is set to 0000-00-00 when the asset record is created 357 * before any cost is added to the asset 358 */ 359 $SQL = "UPDATE fixedassets 360 SET datepurchased='" . FormatDateForSQL($DeliveryDate) . "', 361 cost = cost + " . ($CurrentStandardCost * $OrderLine->ReceiveQty) . " 362 WHERE assetid = '" . $OrderLine->AssetID . "'"; 363 } else { 364 $SQL = "UPDATE fixedassets SET cost = cost + " . ($CurrentStandardCost * $OrderLine->ReceiveQty) . " 365 WHERE assetid = '" . $OrderLine->AssetID . "'"; 366 } 367 $ErrMsg = _('CRITICAL ERROR! NOTE DOWN THIS ERROR AND SEEK ASSISTANCE. The fixed asset cost and date purchased was not able to be updated because:'); 368 $DbgMsg = _('The following SQL was used to attempt the update of the cost and the date the asset was purchased'); 369 $Result = DB_query($SQL,$ErrMsg, $DbgMsg, true); 370 371 } //assetid provided doesn't exist so ignore it and treat as a normal nominal item 372 } //assetid is set so the nominal item is an asset 373 374 /* If GLLink_Stock then insert GLTrans to debit the GL Code and credit GRN Suspense account at standard cost*/ 375 if ($_SESSION['PO'.$identifier]->GLLink==1 AND $OrderLine->GLCode !=0){ 376 /*GLCode is set to 0 when the GLLink is not activated this covers a situation where the GLLink is now active but it wasn't when this PO was entered */ 377 378 /*first the debit using the GLCode in the PO detail record entry*/ 379 $SQL = "INSERT INTO gltrans (type, 380 typeno, 381 trandate, 382 periodno, 383 account, 384 narrative, 385 amount) 386 VALUES ( 387 25, 388 '" . $GRN . "', 389 '" . FormatDateForSQL($DeliveryDate) . "', 390 '" . $PeriodNo . "', 391 '" . $OrderLine->GLCode . "', 392 'PO: " . $_SESSION['PO'.$identifier]->OrderNo . " " . $_SESSION['PO'.$identifier]->SupplierID . " - " . $OrderLine->StockID 393 . " - " . DB_escape_string($OrderLine->ItemDescription) . " x " . $OrderLine->ReceiveQty . " @ " . 394 locale_number_format($CurrentStandardCost,$_SESSION['CompanyRecord']['decimalplaces']) . "', 395 '" . $CurrentStandardCost * $OrderLine->ReceiveQty . "' 396 )"; 397 398 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The purchase GL posting could not be inserted because'); 399 $DbgMsg = _('The following SQL to insert the purchase GLTrans record was used'); 400 $Result = DB_query($SQL,$ErrMsg, $DbgMsg, true); 401 402 /* If the CurrentStandardCost != UnitCost (the standard at the time the first delivery was booked in, and its a stock item, then the difference needs to be booked in against the purchase price variance account */ 403 404 /*now the GRN suspense entry*/ 405 $SQL = "INSERT INTO gltrans (type, 406 typeno, 407 trandate, 408 periodno, 409 account, 410 narrative, 411 amount) 412 VALUES (25, 413 '" . $GRN . "', 414 '" . FormatDateForSQL($DeliveryDate) . "', 415 '" . $PeriodNo . "', 416 '" . $_SESSION['CompanyRecord']['grnact'] . "', 417 '" . _('PO'.$identifier) . ': ' . $_SESSION['PO'.$identifier]->OrderNo . ' ' . $_SESSION['PO'.$identifier]->SupplierID . ' - ' . $OrderLine->StockID . ' - ' . DB_escape_string($OrderLine->ItemDescription) . ' x ' . $OrderLine->ReceiveQty . ' @ ' . locale_number_format($UnitCost,$_SESSION['CompanyRecord']['decimalplaces']) . "', 418 '" . -$UnitCost * $OrderLine->ReceiveQty . "' 419 )"; 420 421 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The GRN suspense side of the GL posting could not be inserted because'); 422 $DbgMsg = _('The following SQL to insert the GRN Suspense GLTrans record was used'); 423 $Result = DB_query($SQL, $ErrMsg, $DbgMsg,true); 424 425 } /* end of if GL and stock integrated and standard cost !=0 */ 426 } /*end of OrderLine loop */ 427 428 $StatusComment=date($_SESSION['DefaultDateFormat']) .' - ' . _('Order Completed on entry of GRN') . '<br />' . $_SESSION['PO'.$identifier]->StatusComments; 429 $sql="UPDATE purchorders 430 SET status='Completed', 431 stat_comment='" . $StatusComment . "' 432 WHERE orderno='" . $_SESSION['PO'.$identifier]->OrderNo . "'"; 433 $result=DB_query($sql); 434 435 if ($_SESSION['PO'.$identifier]->GLLink==1) { 436 EnsureGLEntriesBalance(25, $GRN); 437 } 438 439 $Result = DB_Txn_Commit(); 440 441 //Now add all these deliveries to this purchase invoice 442 443 444 $SQL = "SELECT grnbatch, 445 grnno, 446 purchorderdetails.orderno, 447 purchorderdetails.unitprice, 448 grns.itemcode, 449 grns.deliverydate, 450 grns.itemdescription, 451 grns.qtyrecd, 452 grns.quantityinv, 453 grns.stdcostunit, 454 grns.supplierref, 455 purchorderdetails.glcode, 456 purchorderdetails.shiptref, 457 purchorderdetails.jobref, 458 purchorderdetails.podetailitem, 459 purchorderdetails.assetid, 460 stockmaster.decimalplaces 461 FROM grns INNER JOIN purchorderdetails 462 ON grns.podetailitem=purchorderdetails.podetailitem 463 LEFT JOIN stockmaster ON grns.itemcode=stockmaster.stockid 464 WHERE grns.supplierid ='" . $_SESSION['SuppTrans']->SupplierID . "' 465 AND purchorderdetails.orderno = '" . intval($_GET['ReceivePO']) . "' 466 AND grns.qtyrecd - grns.quantityinv > 0 467 ORDER BY grns.grnno"; 468 $GRNResults = DB_query($SQL); 469 470 while ($myrow=DB_fetch_array($GRNResults)){ 471 472 if ($myrow['decimalplaces']==''){ 473 $myrow['decimalplaces']=2; 474 } 475 $_SESSION['SuppTrans']->Add_GRN_To_Trans($myrow['grnno'], 476 $myrow['podetailitem'], 477 $myrow['itemcode'], 478 $myrow['itemdescription'], 479 $myrow['qtyrecd'], 480 $myrow['quantityinv'], 481 $myrow['qtyrecd'] - $myrow['quantityinv'], 482 $myrow['unitprice'], 483 $myrow['unitprice'], 484 true, 485 $myrow['stdcostunit'], 486 $myrow['shiptref'], 487 $myrow['jobref'], 488 $myrow['glcode'], 489 $myrow['orderno'], 490 $myrow['assetid'], 491 0, 492 $myrow['decimalplaces'], 493 $myrow['grnbatch'], 494 $myrow['supplierref']); 495 } 496 } //end if the order has no controlled items on it 497 } //only allow auto receiving of all lines if the PO is authorised 498 } //only allow auto receiving if the user has permission to receive goods 499} // Page called with link to receive all the items on a PO 500 501 502/* Set the session variables to the posted data from the form if the page has called itself */ 503if (isset($_POST['ExRate'])){ 504 $_SESSION['SuppTrans']->ExRate = filter_number_format($_POST['ExRate']); 505 $_SESSION['SuppTrans']->Comments = $_POST['Comments']; 506 $_SESSION['SuppTrans']->TranDate = $_POST['TranDate']; 507 508 if (mb_substr( $_SESSION['SuppTrans']->Terms,0,1)=='1') { /*Its a day in the following month when due */ 509 $DayInFollowingMonth = (int) mb_substr( $_SESSION['SuppTrans']->Terms,1); 510 $DaysBeforeDue = 0; 511 } else { /*Use the Days Before Due to add to the invoice date */ 512 $DayInFollowingMonth = 0; 513 $DaysBeforeDue = (int) mb_substr( $_SESSION['SuppTrans']->Terms,1); 514 } 515 516 $_SESSION['SuppTrans']->DueDate = CalcDueDate($_SESSION['SuppTrans']->TranDate, $DayInFollowingMonth, $DaysBeforeDue); 517 518 $_SESSION['SuppTrans']->SuppReference = $_POST['SuppReference']; 519 520 if ( $_SESSION['SuppTrans']->GLLink_Creditors == 1){ 521 522/*The link to GL from creditors is active so the total should be built up from GLPostings and GRN entries 523if the link is not active then OvAmount must be entered manually. */ 524 525 $_SESSION['SuppTrans']->OvAmount = 0; /* for starters */ 526 if (count($_SESSION['SuppTrans']->GRNs) > 0){ 527 foreach ( $_SESSION['SuppTrans']->GRNs as $GRN){ 528 $_SESSION['SuppTrans']->OvAmount += ($GRN->This_QuantityInv * $GRN->ChgPrice); 529 } 530 } 531 if (count($_SESSION['SuppTrans']->GLCodes) > 0){ 532 foreach ( $_SESSION['SuppTrans']->GLCodes as $GLLine){ 533 $_SESSION['SuppTrans']->OvAmount += $GLLine->Amount; 534 } 535 } 536 if (count($_SESSION['SuppTrans']->Shipts) > 0){ 537 foreach ( $_SESSION['SuppTrans']->Shipts as $ShiptLine){ 538 $_SESSION['SuppTrans']->OvAmount += $ShiptLine->Amount; 539 } 540 } 541 if (count($_SESSION['SuppTrans']->Contracts) > 0){ 542 foreach ( $_SESSION['SuppTrans']->Contracts as $Contract){ 543 $_SESSION['SuppTrans']->OvAmount += $Contract->Amount; 544 } 545 } 546 if (count($_SESSION['SuppTrans']->Assets) > 0){ 547 foreach ( $_SESSION['SuppTrans']->Assets as $FixedAsset){ 548 $_SESSION['SuppTrans']->OvAmount += $FixedAsset->Amount; 549 } 550 } 551 $_SESSION['SuppTrans']->OvAmount = round($_SESSION['SuppTrans']->OvAmount,$_SESSION['SuppTrans']->CurrDecimalPlaces); 552 }else { 553/*OvAmount must be entered manually */ 554 $_SESSION['SuppTrans']->OvAmount = round(filter_number_format($_POST['OvAmount']),$_SESSION['SuppTrans']->CurrDecimalPlaces); 555 } 556} 557 558 559if (!isset($_POST['PostInvoice'])){ 560 561 if (isset($_POST['GRNS']) 562 AND $_POST['GRNS'] == _('Purchase Orders')){ 563 /*This ensures that any changes in the page are stored in the session before calling the grn page */ 564 echo '<meta http-equiv="Refresh" content="0; url=' . $RootPath . '/SuppInvGRNs.php">'; 565 echo '<div class="centre">' . _('You should automatically be forwarded to the entry of invoices against goods received page') . 566 '. ' . _('If this does not happen') .' (' . _('if the browser does not support META Refresh') . ') ' . 567 '<a href="' . $RootPath . '/SuppInvGRNs.php">' . _('click here') . '</a> ' . _('to continue') . '</div> 568 <br />'; 569 exit; 570 } 571 if (isset($_POST['Shipts']) AND $_POST['Shipts'] == _('Shipments')){ 572 /*This ensures that any changes in the page are stored in the session before calling the shipments page */ 573 echo '<meta http-equiv="Refresh" content="0; url=' . $RootPath . '/SuppShiptChgs.php">'; 574 echo '<div class="centre">' . _('You should automatically be forwarded to the entry of invoices against shipments page') . 575 '. ' . _('If this does not happen') . ' (' . _('if the browser does not support META Refresh'). ') ' . 576 '<a href="' . $RootPath . '/SuppShiptChgs.php">' . _('click here') . '</a> ' . _('to continue') . '.</div><br />'; 577 exit; 578 } 579 if (isset($_POST['GL']) AND $_POST['GL'] == _('General Ledger')){ 580 /*This ensures that any changes in the page are stored in the session before calling the shipments page */ 581 echo '<meta http-equiv="Refresh" content="0; url=' . $RootPath . '/SuppTransGLAnalysis.php">'; 582 echo '<div class="centre">' . _('You should automatically be forwarded to the entry of invoices against the general ledger page') . 583 '. ' . _('If this does not happen') . ' (' . _('if the browser does not support META Refresh'). ') ' . 584 '<a href="' . $RootPath . '/SuppTransGLAnalysis.php">' . _('click here') . '</a> ' . _('to continue') . '.</div><br />'; 585 exit; 586 } 587 if (isset($_POST['Contracts']) AND $_POST['Contracts'] == _('Contracts')){ 588 /*This ensures that any changes in the page are stored in the session before calling the shipments page */ 589 echo '<meta http-equiv="refresh" content="0; url=' . $RootPath . '/SuppContractChgs.php">'; 590 echo '<div class="centre">' . _('You should automatically be forwarded to the entry of invoices against contracts page') . 591 '. ' . _('If this does not happen') . ' (' . _('if the browser does not support META Refresh'). ') ' . 592 '<a href="' . $RootPath . '/SuppContractChgs.php">' . _('click here') . '</a> ' . _('to continue') . '.</div> 593 <br />'; 594 exit; 595 } 596 if (isset($_POST['FixedAssets']) 597 AND $_POST['FixedAssets'] == _('Fixed Assets')){ 598 /*This ensures that any changes in the page are stored in the session before calling the shipments page */ 599 echo '<meta http-equiv="refresh" content="0; url=' . $RootPath . '/SuppFixedAssetChgs.php">'; 600 echo '<div class="centre">' . _('You should automatically be forwarded to the entry of invoice amounts against fixed assets page') . 601 '. ' . _('If this does not happen') . ' (' . _('if the browser does not support META Refresh'). ') ' . 602 '<a href="' . $RootPath . '/SuppFixedAssetChgs.php">' . _('click here') . '</a> ' . _('to continue') . '.</DIV><br />'; 603 exit; 604 } 605 /* everything below here only do if a Supplier is selected 606 fisrt add a header to show who we are making an invoice for */ 607 608 echo '<br /><table class="selection"> 609 <tr> 610 <th>' . _('Supplier') . '</th> 611 <th>' . _('Currency') . '</th> 612 <th>' . _('Terms') . '</th> 613 <th>' . _('Tax Authority') . '</th> 614 </tr>'; 615 616 echo '<tr> 617 <td><b>' . $_SESSION['SuppTrans']->SupplierID . ' - ' . 618 $_SESSION['SuppTrans']->SupplierName . '</b></td> 619 <th><b>' . $_SESSION['SuppTrans']->CurrCode . '</b></th> 620 <td><b>' . $_SESSION['SuppTrans']->TermsDescription . '</b></td> 621 <td><b>' . $_SESSION['SuppTrans']->TaxGroupDescription . '</b></td> 622 </tr> 623 </table>'; 624 625 echo '<br /><form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post" id="form1">'; 626 echo '<div>'; 627 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 628 629 echo '<br /><table class="selection">'; 630 631 echo '<tr> 632 <td>' . _('Supplier Invoice Reference') . ':</td> 633 <td><input type="text" required="required" pattern=".{1,20}" title="'._('The input should not be blank and should be less than 20 characters').'" placeholder="'._('Within 20 characters needed').'" size="20" maxlength="20" name="SuppReference" value="' . $_SESSION['SuppTrans']->SuppReference . '" /></td>'; 634 635 if (!isset($_SESSION['SuppTrans']->TranDate)){ 636 $_SESSION['SuppTrans']->TranDate= Date($_SESSION['DefaultDateFormat'], Mktime(0,0,0,Date('m'),Date('d')-1,Date('y'))); 637 } 638 echo '<td>' . _('Invoice Date') . ' (' . _('in format') . ' ' . $_SESSION['DefaultDateFormat'] . ') :</td> 639 <td><input type="text" class="date" size="11" maxlength="10" name="TranDate" value="' . $_SESSION['SuppTrans']->TranDate . '" /></td> 640 <td>' . _('Exchange Rate') . ':</td> 641 <td><input class="number" maxlength="12" name="ExRate" size="14" type="text" value="' . locale_number_format($_SESSION['SuppTrans']->ExRate,10) . '" /></td> 642 </tr> 643 </table>'; 644 645 echo '<br /> 646 <div class="centre"> 647 <input type="submit" name="GRNS" value="' . _('Purchase Orders') . '" /> 648 <input type="submit" name="Shipts" value="' . _('Shipments') . '" /> 649 <input type="submit" name="Contracts" value="' . _('Contracts') . '" /> '; 650 651 if ( $_SESSION['SuppTrans']->GLLink_Creditors == 1){ 652 echo '<input type="submit" name="GL" value="' . _('General Ledger') . '" /> '; 653 } 654 echo ' <input type="submit" name="FixedAssets" value="' . _('Fixed Assets') . '" /> 655 </div>'; 656 $CanSubmit = false;//To avoid a empty submit 657 658 $TotalGRNValue = 0; 659 660 if (count( $_SESSION['SuppTrans']->GRNs)>0){ /*if there are any GRNs selected for invoicing then */ 661 /*Show all the selected GRNs so far from the SESSION['SuppInv']->GRNs array */ 662 $CanSubmit = true; 663 664 echo '<br /> 665 <table class="selection"> 666 <tr> 667 <th colspan="6">' . _('Purchase Order Charges') . '</th> 668 </tr>'; 669 $tableheader = '<tr style="tableheader"> 670 <th>' . _('Seq') . ' #</th> 671 <th>' . _('GRN Batch') . '</th> 672 <th>' . _('Supplier Ref') . '</th> 673 <th>' . _('Item Code') . '</th> 674 <th>' . _('Description') . '</th> 675 <th>' . _('Quantity Charged') . '</th> 676 <th>' . _('Price in') . ' ' . $_SESSION['SuppTrans']->CurrCode . '</th> 677 <th>' . _('Line Total') . ' ' . $_SESSION['SuppTrans']->CurrCode . '</th> 678 </tr>'; 679 echo $tableheader; 680 681 foreach ($_SESSION['SuppTrans']->GRNs as $EnteredGRN){ 682 683 echo '<tr> 684 <td>' . $EnteredGRN->GRNNo . '</td> 685 <td>' . $EnteredGRN->GRNBatchNo . '</td> 686 <td>' . $EnteredGRN->SupplierRef . '</td> 687 <td>' . $EnteredGRN->ItemCode . '</td> 688 <td>' . $EnteredGRN->ItemDescription . '</td> 689 <td class="number">' . locale_number_format($EnteredGRN->This_QuantityInv,$EnteredGRN->DecimalPlaces) . '</td> 690 <td class="number">' . locale_number_format($EnteredGRN->ChgPrice,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 691 <td class="number">' . locale_number_format($EnteredGRN->ChgPrice * $EnteredGRN->This_QuantityInv,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 692 </tr>'; 693 694 $TotalGRNValue += ($EnteredGRN->ChgPrice * $EnteredGRN->This_QuantityInv); 695 696 } 697 698 echo '<tr> 699 <td colspan="5" class="number" style="color:blue">' . _('Total Value of Goods Charged') . ':</td> 700 <td class="number" style="color:blue">' . locale_number_format($TotalGRNValue,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 701 </tr> 702 </table>'; 703 } 704 705 $TotalShiptValue = 0; 706 707 if (count( $_SESSION['SuppTrans']->Shipts) > 0){ /*if there are any Shipment charges on the invoice*/ 708 $CanSubmit = true; 709 710 echo '<br /> 711 <table class="selection"> 712 <tr> 713 <th colspan="2">' . _('Shipment Charges') . '</th> 714 </tr>'; 715 $TableHeader = '<tr> 716 <th>' . _('Shipment') . '</th> 717 <th>' . _('Amount') . '</th> 718 </tr>'; 719 echo $TableHeader; 720 721 $i=0; //row counter 722 723 foreach ($_SESSION['SuppTrans']->Shipts as $EnteredShiptRef){ 724 725 echo '<tr> 726 <td>' . $EnteredShiptRef->ShiptRef . '</td> 727 <td class="number">' . locale_number_format($EnteredShiptRef->Amount,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 728 </tr>'; 729 730 $TotalShiptValue += $EnteredShiptRef->Amount; 731 732 $i++; 733 if ($i > 15){ 734 $i = 0; 735 echo $TableHeader; 736 } 737 } 738 739 echo '<tr> 740 <td class="number" style="color:blue">' . _('Total shipment charges') . ':</td> 741 <td class="number" style="color:blue">' . locale_number_format($TotalShiptValue,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 742 </tr> 743 </table>'; 744 } 745 746 $TotalAssetValue = 0; 747 748 if (count( $_SESSION['SuppTrans']->Assets) > 0){ /*if there are any fixed assets on the invoice*/ 749 $CanSubmit = true; 750 751 echo '<br /> 752 <table class="selection"> 753 <tr> 754 <th colspan="3">' . _('Fixed Asset Additions') . '</th> 755 </tr>'; 756 $TableHeader = '<tr> 757 <th>' . _('Asset ID') . '</th> 758 <th>' . _('Description') . '</th> 759 <th>' . _('Amount') . ' ' . $_SESSION['SuppTrans']->CurrCode . '</th> 760 </tr>'; 761 echo $TableHeader; 762 763 foreach ($_SESSION['SuppTrans']->Assets as $EnteredAsset){ 764 765 echo '<tr> 766 <td>' . $EnteredAsset->AssetID . '</td> 767 <td>' . $EnteredAsset->Description . '</td> 768 <td class="number">' . locale_number_format($EnteredAsset->Amount,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 769 </tr>'; 770 771 $TotalAssetValue += $EnteredAsset->Amount; 772 773 $i++; 774 if ($i > 15){ 775 $i = 0; 776 echo $TableHeader; 777 } 778 } 779 780 echo '<tr> 781 <td colspan="2" class="number" style="color:blue">' . _('Total asset additions') . ':</td> 782 <td class="number" style="color:blue">' . locale_number_format($TotalAssetValue,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 783 </tr> 784 </table>'; 785 } //end loop around assets added to invocie 786 787 $TotalContractsValue = 0; 788 789 if (count( $_SESSION['SuppTrans']->Contracts) > 0){ /*if there are any contract charges on the invoice*/ 790 $CanSubmit = true; 791 792 echo '<br /> 793 <table class="selection"> 794 <tr> 795 <th colspan="3">' . _('Contract Charges') . '</th> 796 </tr>'; 797 $TableHeader = '<tr> 798 <th>' . _('Contract') . '</th> 799 <th>' . _('Narrative') . '</th> 800 <th>' . _('Amount') . ' ' . $_SESSION['SuppTrans']->CurrCode . '</th> 801 </tr>'; 802 echo $TableHeader; 803 804 805 $i=0; 806 foreach ($_SESSION['SuppTrans']->Contracts as $Contract){ 807 808 echo '<tr> 809 <td>' . $Contract->ContractRef . '</td> 810 <td>' . $Contract->Narrative . '</td> 811 <td class="number">' . locale_number_format($Contract->Amount,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 812 </tr>'; 813 814 $TotalContractsValue += $Contract->Amount; 815 816 $i++; 817 if ($i == 15){ 818 $i = 0; 819 echo $TableHeader; 820 } 821 } 822 823 echo '<tr> 824 <td colspan="2" class="number" style="color:blue">' . _('Total contract charges') . ':</td> 825 <td class="number" style="color:blue">' . locale_number_format($TotalContractsValue,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 826 </tr> 827 </table>'; 828 } 829 830 $TotalGLValue = 0; 831 832 if ( $_SESSION['SuppTrans']->GLLink_Creditors == 1){ 833 834 if (count($_SESSION['SuppTrans']->GLCodes) > 0){ 835 $CanSubmit = true; 836 echo '<br /> 837 <table class="selection"> 838 <tr> 839 <th colspan="5">' . _('General Ledger Analysis') . '</th> 840 </tr>'; 841 $TableHeader = '<tr> 842 <th>' . _('Account') . '</th> 843 <th>' . _('Account Name') . '</th> 844 <th>' . _('Narrative') . '</th> 845 <th>' . _('Tag') . '</th> 846 <th>' . _('Amount') . '<br />' . _('in') . ' ' . $_SESSION['SuppTrans']->CurrCode . '</th> 847 </tr>'; 848 echo $TableHeader; 849 850 foreach ($_SESSION['SuppTrans']->GLCodes as $EnteredGLCode){ 851 echo '<tr> 852 <td>' . $EnteredGLCode->GLCode . '</td> 853 <td>' . $EnteredGLCode->GLActName . '</td> 854 <td>' . $EnteredGLCode->Narrative . '</td> 855 <td>' . $EnteredGLCode->Tag . ' - ' . $EnteredGLCode->TagName . '</td> 856 <td class="number">' . locale_number_format($EnteredGLCode->Amount,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 857 </tr>'; 858 859 $TotalGLValue += $EnteredGLCode->Amount; 860 861 } 862 863 echo '<tr> 864 <td colspan="4" class="number" style="color:blue">' . _('Total GL Analysis') . ':</td> 865 <td class="number" style="color:blue">' . locale_number_format($TotalGLValue,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 866 </tr> 867 </table>'; 868 } 869 870 $_SESSION['SuppTrans']->OvAmount = ($TotalGRNValue + $TotalGLValue + $TotalAssetValue + $TotalShiptValue + $TotalContractsValue); 871 872 echo '<br /> 873 <table class="selection"> 874 <tr> 875 <td>' . _('Amount in supplier currency') . ':</td> 876 <td colspan="2" class="number">' . locale_number_format( $_SESSION['SuppTrans']->OvAmount,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '</td> 877 </tr>'; 878 } else { 879 echo '<br /> 880 <table class="selection"> 881 <tr> 882 <td>' . _('Amount in supplier currency') . ':</td> 883 <td colspan="2" class="number"><input type="text" class="number" title="'._('The input must be numeric').'" size="12" maxlength="10" name="OvAmount" value="' . locale_number_format( $_SESSION['SuppTrans']->OvAmount,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '" /></td> 884 </tr>'; 885 } 886 887 echo '<tr> 888 <td colspan="2"><input type="submit" name="ToggleTaxMethod" value="' . _('Update Tax Calculation') . '" /></td> 889 <td><select name="OverRideTax" onchange="ReloadForm(form1.ToggleTaxMethod)">'; 890 891 if (isset($_POST['OverRideTax']) AND $_POST['OverRideTax']=='Man'){ 892 echo '<option value="Auto">' . _('Automatic') . '</option> 893 <option selected="selected" value="Man">' . _('Manually') . '</option>'; 894 } else { 895 echo '<option selected="selected" value="Auto">' . _('Automatic') . '</option> 896 <option value="Man">' . _('Manually') . '</option>'; 897 } 898 899 echo '</select></td> 900 </tr>'; 901 $TaxTotal =0; //initialise tax total 902 903 foreach ($_SESSION['SuppTrans']->Taxes as $Tax) { 904 905 echo '<tr> 906 <td>' . $Tax->TaxAuthDescription . '</td> 907 <td>'; 908 909 /*Set the tax rate to what was entered */ 910 if (isset($_POST['TaxRate' . $Tax->TaxCalculationOrder])){ 911 $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxRate = filter_number_format($_POST['TaxRate' . $Tax->TaxCalculationOrder])/100; 912 } 913 914 /*If a tax rate is entered that is not the same as it was previously then recalculate automatically the tax amounts */ 915 916 if (!isset($_POST['OverRideTax']) 917 OR $_POST['OverRideTax']=='Auto'){ 918 919 echo ' <input type="text" class="number" name="TaxRate' . $Tax->TaxCalculationOrder . '" maxlength="4" size="4" value="' . locale_number_format($_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxRate * 100,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '" />%'; 920 921 /*Now recaluclate the tax depending on the method */ 922 if ($Tax->TaxOnTax ==1){ 923 924 $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount = $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxRate * ($_SESSION['SuppTrans']->OvAmount + $TaxTotal); 925 926 } else { /*Calculate tax without the tax on tax */ 927 928 $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount = $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxRate * $_SESSION['SuppTrans']->OvAmount; 929 930 } 931 932 echo '<input type="hidden" name="TaxAmount' . $Tax->TaxCalculationOrder . '" value="' . locale_number_format(round($_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount,$_SESSION['SuppTrans']->CurrDecimalPlaces),$_SESSION['SuppTrans']->CurrDecimalPlaces) . '" />'; 933 934 echo '</td><td class="number">' . locale_number_format($_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount,$_SESSION['SuppTrans']->CurrDecimalPlaces); 935 936 } else { /*Tax being entered manually accept the taxamount entered as is*/ 937// if (!isset($_POST['TaxAmount' . $Tax->TaxCalculationOrder])) { 938// $_POST['TaxAmount' . $Tax->TaxCalculationOrder]=0; 939// } 940 $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount = filter_number_format($_POST['TaxAmount' . $Tax->TaxCalculationOrder]); 941 942 echo ' <input type="hidden" name="TaxRate' . $Tax->TaxCalculationOrder . '" value="' . locale_number_format($_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxRate * 100,$_SESSION['SuppTrans']->CurrDecimalPlaces) . '" />'; 943 944 echo '</td> 945 <td><input type="text" class="number" size="12" maxlength="12" name="TaxAmount' . $Tax->TaxCalculationOrder . '" value="' . locale_number_format(round($_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount,$_SESSION['SuppTrans']->CurrDecimalPlaces),$_SESSION['SuppTrans']->CurrDecimalPlaces) . '" />'; 946 } 947 948 $TaxTotal += $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount; 949 echo '</td> 950 </tr>'; 951 } 952 953 $_SESSION['SuppTrans']->OvAmount = round($_SESSION['SuppTrans']->OvAmount,$_SESSION['SuppTrans']->CurrDecimalPlaces); 954 955 $DisplayTotal = locale_number_format(( $_SESSION['SuppTrans']->OvAmount + $TaxTotal), $_SESSION['SuppTrans']->CurrDecimalPlaces); 956 957 echo '<tr> 958 <td>' . _('Invoice Total') . ':</td> 959 <td colspan="2" class="number"><b>' . $DisplayTotal . '</b></td> 960 </tr> 961 </table>'; 962 963 echo '<br /> 964 <table class="selection"> 965 <tr> 966 <td>' . _('Comments') . '</td> 967 <td><textarea name="Comments" cols="40" rows="2">' . $_SESSION['SuppTrans']->Comments . '</textarea></td> 968 </tr> 969 </table>'; 970 if ($CanSubmit) { 971 972 echo '<br /> 973 <div class="centre"> 974 <input type="submit" name="PostInvoice" value="' . _('Enter Invoice') . '" /> 975 </div>'; 976 } 977 978 echo '</div> 979 </form>'; 980} else { // $_POST['PostInvoice'] is set so do the postings -and dont show the button to process 981 982/*First do input reasonableness checks 983then do the updates and inserts to process the invoice entered */ 984 $TaxTotal =0; 985 foreach ($_SESSION['SuppTrans']->Taxes as $Tax) { 986 /*Set the tax rate to what was entered */ 987 if (isset($_POST['TaxRate' . $Tax->TaxCalculationOrder])){ 988 $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxRate = filter_number_format($_POST['TaxRate' . $Tax->TaxCalculationOrder])/100; 989 } 990 if ($_POST['OverRideTax']=='Auto' OR !isset($_POST['OverRideTax'])){ 991 /*Now recaluclate the tax depending on the method */ 992 /*Now recaluclate the tax depending on the method */ 993 if ($Tax->TaxOnTax ==1){ 994 995 $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount = $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxRate * ($_SESSION['SuppTrans']->OvAmount + $TaxTotal); 996 997 } else { /*Calculate tax without the tax on tax */ 998 999 $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount = $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxRate * $_SESSION['SuppTrans']->OvAmount; 1000 1001 } 1002 } else { /*Tax being entered manually accept the taxamount entered as is*/ 1003 $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount = filter_number_format($_POST['TaxAmount' . $Tax->TaxCalculationOrder]); 1004 } 1005 $TaxTotal += $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount; 1006 } 1007 1008 1009 $InputError = False; 1010 if ( $TaxTotal + $_SESSION['SuppTrans']->OvAmount < 0){ 1011 1012 $InputError = True; 1013 prnMsg(_('The invoice as entered cannot be processed because the total amount of the invoice is less than 0') . '. ' . _('Invoices are expected to have a positive charge'),'error'); 1014 echo '<p>' . _('The tax total is') . ' : ' . locale_number_format($TaxTotal,$_SESSION['SuppTrans']->CurrDecimalPlaces); 1015 echo '<p>' . _('The ovamount is') . ' : ' . locale_number_format($_SESSION['SuppTrans']->OvAmount,$_SESSION['SuppTrans']->CurrDecimalPlaces); 1016 1017 } elseif ( $TaxTotal + $_SESSION['SuppTrans']->OvAmount == 0){ 1018 1019 prnMsg(_('The invoice as entered will be processed but be warned the amount of the invoice is zero!') . '. ' . _('Invoices are normally expected to have a positive charge'),'warn'); 1020 1021 } elseif (mb_strlen( $_SESSION['SuppTrans']->SuppReference)<1){ 1022 1023 $InputError = True; 1024 prnMsg(_('The invoice as entered cannot be processed because the there is no suppliers invoice number or reference entered') . '. ' . _('The supplier invoice number must be entered'),'error'); 1025 1026 } elseif (!Is_date( $_SESSION['SuppTrans']->TranDate)){ 1027 1028 $InputError = True; 1029 prnMsg( _('The invoice as entered cannot be processed because the invoice date entered is not in the format') . ' ' . $_SESSION['DefaultDateFormat'],'error'); 1030 1031 } elseif (DateDiff(Date($_SESSION['DefaultDateFormat']), $_SESSION['SuppTrans']->TranDate, 'd') < 0){ 1032 1033 $InputError = True; 1034 prnMsg(_('The invoice as entered cannot be processed because the invoice date is after today') . '. ' . _('Purchase invoices are expected to have a date prior to or today'),'error'); 1035 1036 }elseif ( $_SESSION['SuppTrans']->ExRate <= 0){ 1037 1038 $InputError = True; 1039 prnMsg( _('The invoice as entered cannot be processed because the exchange rate for the invoice has been entered as a negative or zero number') . '. ' . _('The exchange rate is expected to show how many of the suppliers currency there are in 1 of the local currency'),'error'); 1040 1041 } elseif ( $_SESSION['SuppTrans']->OvAmount < round($_SESSION['SuppTrans']->Total_Shipts_Value() + $_SESSION['SuppTrans']->Total_GL_Value() + $_SESSION['SuppTrans']->Total_Contracts_Value()+ $_SESSION['SuppTrans']->Total_Assets_Value()+$_SESSION['SuppTrans']->Total_GRN_Value(),$_SESSION['SuppTrans']->CurrDecimalPlaces)){ 1042 1043 prnMsg( _('The invoice total as entered is less than the sum of the shipment charges, the general ledger entries (if any), the charges for goods received, contract charges and fixed asset charges. There must be a mistake somewhere, the invoice as entered will not be processed'),'error'); 1044 $InputError = True; 1045 1046 } else { 1047 1048 $sql = "SELECT count(*) 1049 FROM supptrans 1050 WHERE supplierno='" . $_SESSION['SuppTrans']->SupplierID . "' 1051 AND supptrans.suppreference='" . $_POST['SuppReference'] . "'"; 1052 1053 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sql to check for the previous entry of the same invoice failed'); 1054 $DbgMsg = _('The following SQL to test for a previous invoice with the same reference from the same supplier was used'); 1055 $result=DB_query($sql, $ErrMsg, $DbgMsg, True); 1056 1057 $myrow=DB_fetch_row($result); 1058 if ($myrow[0] == 1){ /*Transaction reference already entered */ 1059 prnMsg( _('The invoice number') . ' : ' . $_POST['SuppReference'] . ' ' . _('has already been entered') . '. ' . _('It cannot be entered again'),'error'); 1060 $InputError = True; 1061 } 1062 } 1063 1064 if ($InputError == False){ 1065 1066 /* SQL to process the postings for purchase invoice */ 1067 /*Start an SQL transaction */ 1068 1069 $Result = DB_Txn_Begin(); 1070 1071 /*Get the next transaction number for internal purposes and the period to post GL transactions in based on the invoice date*/ 1072 $InvoiceNo = GetNextTransNo(20); 1073 $PeriodNo = GetPeriod( $_SESSION['SuppTrans']->TranDate); 1074 $SQLInvoiceDate = FormatDateForSQL( $_SESSION['SuppTrans']->TranDate); 1075 1076 if ( $_SESSION['SuppTrans']->GLLink_Creditors == 1){ 1077 /*Loop through the GL Entries and create a debit posting for each of the accounts entered */ 1078 $LocalTotal = 0; 1079 1080 /*the postings here are a little tricky, the logic goes like this: 1081 if its a shipment entry then the cost must go against the GRN suspense account defined in the company record 1082 1083 if its a general ledger amount it goes straight to the account specified 1084 1085 if its a GRN amount invoiced then there are two possibilities: 1086 1087 1 The PO line is on a shipment. 1088 The whole charge goes to the GRN suspense account pending the closure of the 1089 shipment where the variance is calculated on the shipment as a whole and the clearing entry to the GRN suspense 1090 is created. Also, shipment records are created for the charges in local currency. 1091 1092 2. The order line item is not on a shipment 1093 The cost as originally credited to GRN suspense on arrival of goods is debited to GRN suspense. 1094 Depending on the setting of WeightedAverageCosting: 1095 If the order line item is a stock item and WeightedAverageCosting set to OFF then use standard costing ..... 1096 Any difference 1097 between the std cost and the currency cost charged as converted at the ex rate of of the invoice is written off 1098 to the purchase price variance account applicable to the stock item being invoiced. 1099 Otherwise 1100 Recalculate the new weighted average cost of the stock and update the cost - post the difference to the appropriate stock code 1101 1102 Or if its not a stock item 1103 but a nominal item then the GL account in the orignal order is used for the price variance account. 1104 */ 1105 1106 foreach ($_SESSION['SuppTrans']->GLCodes as $EnteredGLCode){ 1107 1108 /*GL Items are straight forward - just do the debit postings to the GL accounts specified - 1109 the credit is to creditors control act done later for the total invoice value + tax*/ 1110 //skamnev added tag 1111 $SQL = "INSERT INTO gltrans (type, 1112 typeno, 1113 trandate, 1114 periodno, 1115 account, 1116 narrative, 1117 tag, 1118 amount) 1119 VALUES (20, 1120 '" . $InvoiceNo . "', 1121 '" . $SQLInvoiceDate . "', 1122 '" . $PeriodNo . "', 1123 '" . $EnteredGLCode->GLCode . "', 1124 '" . $_SESSION['SuppTrans']->SupplierID . ' - ' . $EnteredGLCode->Narrative . "', 1125 '" . $EnteredGLCode->Tag . "', 1126 '" . $EnteredGLCode->Amount/ $_SESSION['SuppTrans']->ExRate ."')"; 1127 1128 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction could not be added because'); 1129 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1130 1131 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1132 1133 $LocalTotal += $EnteredGLCode->Amount/ $_SESSION['SuppTrans']->ExRate; 1134 } 1135 1136 foreach ($_SESSION['SuppTrans']->Shipts as $ShiptChg){ 1137 1138 /*shipment postings are also straight forward - just do the debit postings to the GRN suspense account 1139 these entries are reversed from the GRN suspense when the shipment is closed*/ 1140 1141 $SQL = "INSERT INTO gltrans (type, 1142 typeno, 1143 trandate, 1144 periodno, 1145 account, 1146 narrative, 1147 amount) 1148 VALUES (20, 1149 '" . $InvoiceNo . "', 1150 '" . $SQLInvoiceDate . "', 1151 '" . $PeriodNo . "', 1152 '" . $_SESSION['SuppTrans']->GRNAct . "', 1153 '" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('Shipment charge against') . ' ' . $ShiptChg->ShiptRef . "', 1154 '" . $ShiptChg->Amount/ $_SESSION['SuppTrans']->ExRate . "')"; 1155 1156 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction for the shipment') . 1157 ' ' . $ShiptChg->ShiptRef . ' ' . _('could not be added because'); 1158 1159 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1160 1161 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1162 1163 $LocalTotal += $ShiptChg->Amount/ $_SESSION['SuppTrans']->ExRate; 1164 1165 } 1166 1167 foreach ($_SESSION['SuppTrans']->Assets as $AssetAddition){ 1168 /* only the GL entries if the creditors/GL integration is enabled */ 1169 $SQL = "INSERT INTO gltrans (type, 1170 typeno, 1171 trandate, 1172 periodno, 1173 account, 1174 narrative, 1175 amount) 1176 VALUES ('20', 1177 '" . $InvoiceNo . "', 1178 '" . $SQLInvoiceDate . "', 1179 '" . $PeriodNo . "', 1180 '". $AssetAddition->CostAct . "', 1181 '" . $_SESSION['SuppTrans']->SupplierID . ' ' . _('Asset Addition') . ' ' . $AssetAddition->AssetID . ': ' . $AssetAddition->Description . "', 1182 '" . ($AssetAddition->Amount/ $_SESSION['SuppTrans']->ExRate) . "')"; 1183 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction for the asset addition could not be added because'); 1184 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1185 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1186 1187 $LocalTotal += ($AssetAddition->Amount/ $_SESSION['SuppTrans']->ExRate); 1188 } 1189 1190 foreach ($_SESSION['SuppTrans']->Contracts as $Contract){ 1191 1192 /*contract postings need to get the WIP from the contract items stock category record 1193 * debit postings to this WIP account 1194 * the WIP account is tidied up when the contract is closed*/ 1195 $result = DB_query("SELECT wipact FROM stockcategory 1196 INNER JOIN stockmaster ON 1197 stockcategory.categoryid=stockmaster.categoryid 1198 WHERE stockmaster.stockid='" . $Contract->ContractRef . "'"); 1199 $WIPRow = DB_fetch_row($result); 1200 $WIPAccount = $WIPRow[0]; 1201 $SQL = "INSERT INTO gltrans (type, 1202 typeno, 1203 trandate, 1204 periodno, 1205 account, 1206 narrative, 1207 amount) 1208 VALUES ('20', 1209 '" . $InvoiceNo . "', 1210 '" . $SQLInvoiceDate . "', 1211 '" . $PeriodNo . "', 1212 '". $WIPAccount . "', 1213 '" . $_SESSION['SuppTrans']->SupplierID . ' ' . _('Contract charge against') . ' ' . $Contract->ContractRef . "', 1214 '" . ($Contract->Amount/ $_SESSION['SuppTrans']->ExRate) . "')"; 1215 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction for the contract') . ' ' . $Contract->ContractRef . ' ' . _('could not be added because'); 1216 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1217 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1218 $LocalTotal += ($Contract->Amount/ $_SESSION['SuppTrans']->ExRate); 1219 } 1220 1221 foreach ($_SESSION['SuppTrans']->GRNs as $EnteredGRN){ 1222 1223 if (mb_strlen($EnteredGRN->ShiptRef) == 0 1224 OR $EnteredGRN->ShiptRef == 0){ 1225 /*so its not a GRN shipment item 1226 enter the GL entry to reverse the GRN suspense entry created on delivery 1227 * at standard cost/or weighted average cost used on delivery */ 1228 1229 /*Always do this - for weighted average costing and also for standard costing */ 1230 1231 if ($EnteredGRN->StdCostUnit * ($EnteredGRN->This_QuantityInv ) != 0) { 1232 $SQL = "INSERT INTO gltrans (type, 1233 typeno, 1234 trandate, 1235 periodno, 1236 account, 1237 narrative, 1238 amount) 1239 VALUES ('20', 1240 '" . $InvoiceNo . "', 1241 '" . $SQLInvoiceDate . "', 1242 '" . $PeriodNo . "', 1243 '" . $_SESSION['SuppTrans']->GRNAct . "', 1244 '" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('GRN') . ' ' . $EnteredGRN->GRNNo . ' - ' . $EnteredGRN->ItemCode . ' x ' . $EnteredGRN->This_QuantityInv . ' @ ' . 1245 _('std cost of') . ' ' . $EnteredGRN->StdCostUnit . "', 1246 '" . ($EnteredGRN->StdCostUnit * $EnteredGRN->This_QuantityInv) . "')"; 1247 1248 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction could not be added because'); 1249 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1250 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1251 } 1252 1253 $PurchPriceVar = $EnteredGRN->This_QuantityInv * (($EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate) - $EnteredGRN->StdCostUnit); 1254 1255 /*Yes.... but where to post this difference to - if its a stock item the variance account must be retrieved from the stock category record 1256 if its a nominal purchase order item with no stock item then there will be no standard cost and it will all be variance so post it to the 1257 account specified in the purchase order detail record */ 1258 1259 if ($PurchPriceVar !=0){ /* don't bother with this lot if there is no difference ! */ 1260 if (mb_strlen($EnteredGRN->ItemCode)>0 OR $EnteredGRN->ItemCode != ''){ /*so it is a stock item */ 1261 1262 /*need to get the stock category record for this stock item - this is function in SQL_CommonFunctions.inc */ 1263 $StockGLCode = GetStockGLCode($EnteredGRN->ItemCode); 1264 1265 /*We have stock item and a purchase price variance need to see whether we are using Standard or WeightedAverageCosting */ 1266 1267 if ($_SESSION['WeightedAverageCosting']==1){ /*Weighted Average costing */ 1268 1269 /* 1270 First off figure out the new weighted average cost Need the following data: 1271 1272 How many in stock now 1273 The quantity being invoiced here - $EnteredGRN->This_QuantityInv 1274 The cost of these items - $EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate 1275 */ 1276 1277 $sql ="SELECT SUM(quantity) FROM locstock WHERE stockid='" . $EnteredGRN->ItemCode . "'"; 1278 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The quantity on hand could not be retrieved from the database'); 1279 $DbgMsg = _('The following SQL to retrieve the total stock quantity was used'); 1280 $Result = DB_query($sql, $ErrMsg, $DbgMsg, True); 1281 $QtyRow = DB_fetch_row($Result); 1282 $TotalQuantityOnHand = $QtyRow[0]; 1283 1284 /*The cost adjustment is the price variance / the total quantity in stock 1285 But that is only provided that the total quantity in stock is greater than the quantity charged on this invoice 1286 1287 If the quantity on hand is less the amount charged on this invoice then some must have been sold and the price variance on these must be written off to price variances*/ 1288 1289 $WriteOffToVariances =0; 1290 1291 if ($EnteredGRN->This_QuantityInv > $TotalQuantityOnHand){ 1292 1293 /*So we need to write off some of the variance to variances and only the balance of the quantity in stock to go to stock value */ 1294 1295 /*if the TotalQuantityOnHand is negative then this variance to write off is inflated by the negative quantity - which makes sense */ 1296 1297 $WriteOffToVariances = ($EnteredGRN->This_QuantityInv - $TotalQuantityOnHand) * (($EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate) - $EnteredGRN->StdCostUnit); 1298 1299 $SQL = "INSERT INTO gltrans (type, 1300 typeno, 1301 trandate, 1302 periodno, 1303 account, 1304 narrative, 1305 amount) 1306 VALUES (20, 1307 '" . $InvoiceNo . "', 1308 '" . $SQLInvoiceDate . "', 1309 '" . $PeriodNo . "', 1310 '" . $StockGLCode['purchpricevaract'] . "', 1311 '" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('GRN') . ' ' . $EnteredGRN->GRNNo . ' - ' . $EnteredGRN->ItemCode . ' x ' . ($EnteredGRN->This_QuantityInv -$TotalQuantityOnHand) . ' x ' . _('price var of') . ' ' . round(($EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate) - $EnteredGRN->StdCostUnit,2) . "', 1312 '" . $WriteOffToVariances . "')"; 1313 1314 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction could not be added for the price variance of the stock item because'); 1315 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1316 1317 1318 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1319 } // end if the quantity being invoiced here is greater than the current stock on hand 1320 1321 /*Now post any remaining price variance to stock rather than price variances */ 1322 1323 $SQL = "INSERT INTO gltrans (type, 1324 typeno, 1325 trandate, 1326 periodno, 1327 account, 1328 narrative, 1329 amount) 1330 VALUES (20, 1331 '" . $InvoiceNo . "', 1332 '" . $SQLInvoiceDate . "', 1333 '" . $PeriodNo . "', 1334 '" . $StockGLCode['stockact'] . "', 1335 '" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('Average Cost Adj') . 1336 ' - ' . $EnteredGRN->ItemCode . ' x ' . $TotalQuantityOnHand . ' x ' . 1337 round(($EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate) - $EnteredGRN->StdCostUnit,$_SESSION['CompanyRecord']['decimalplaces']) . "', 1338 '" . ($PurchPriceVar - $WriteOffToVariances) . "')"; 1339 1340 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction could not be added for the price variance of the stock item because'); 1341 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1342 1343 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1344 1345 } else { //It must be Standard Costing 1346 1347 $SQL = "INSERT INTO gltrans (type, 1348 typeno, 1349 trandate, 1350 periodno, 1351 account, 1352 narrative, 1353 amount) 1354 VALUES (20, 1355 '" . $InvoiceNo . "', 1356 '" . $SQLInvoiceDate . "', 1357 '" . $PeriodNo . "', 1358 '" . $StockGLCode['purchpricevaract'] . "', 1359 '" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('GRN') . ' ' . $EnteredGRN->GRNNo . ' - ' . $EnteredGRN->ItemCode . ' x ' . $EnteredGRN->This_QuantityInv . ' x ' . _('price var of') . ' ' . round(($EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate) - $EnteredGRN->StdCostUnit,2) . "', 1360 '" . $PurchPriceVar . "')"; 1361 1362 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction could not be added for the price variance of the stock item because'); 1363 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1364 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1365 } 1366 } else { 1367 /* its a nominal purchase order item that is not on a shipment so post the whole lot to the GLCode specified in the order, the purchase price var is actually the diff between the 1368 order price and the actual invoice price since the std cost was made equal to the order price in local currency at the time 1369 the goods were received */ 1370 $GLCode = $EnteredGRN->GLCode; //by default 1371 if ($EnteredGRN->AssetID!=0) { //then it is an asset 1372 1373 /*Need to get the asset details for posting */ 1374 $result = DB_query("SELECT costact 1375 FROM fixedassets INNER JOIN fixedassetcategories 1376 ON fixedassets.assetcategoryid= fixedassetcategories.categoryid 1377 WHERE assetid='" . $EnteredGRN->AssetID . "'"); 1378 if (DB_num_rows($result)!=0){ // the asset exists 1379 $AssetRow = DB_fetch_array($result); 1380 $GLCode = $AssetRow['costact']; 1381 } 1382 } //the item was an asset received on a purchase order 1383 1384 $SQL = "INSERT INTO gltrans (type, 1385 typeno, 1386 trandate, 1387 periodno, 1388 account, 1389 narrative, 1390 amount) 1391 VALUES (20, 1392 '" . $InvoiceNo . "', 1393 '" . $SQLInvoiceDate . "', 1394 '" . $PeriodNo . "', 1395 '" . $GLCode . "', 1396 '" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('GRN') . ' ' . $EnteredGRN->GRNNo . ' - ' . $EnteredGRN->ItemDescription . ' x ' . $EnteredGRN->This_QuantityInv . ' x ' . _('price var') . ' ' . locale_number_format(($EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate) - $EnteredGRN->StdCostUnit,$_SESSION['SuppTrans']->CurrDecimalPlaces) . "', 1397 '" . $PurchPriceVar . "')"; 1398 1399 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction could not be added for the price variance of the stock item because'); 1400 1401 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1402 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1403 } 1404 } 1405 1406 } else { 1407 /*then its a purchase order item on a shipment - whole charge amount to GRN suspense pending closure of the shipment when the variance is calculated and the GRN act cleared up for the shipment */ 1408 1409 $SQL = "INSERT INTO gltrans (type, 1410 typeno, 1411 trandate, 1412 periodno, 1413 account, 1414 narrative, 1415 amount) 1416 VALUES (20, 1417 '" . $InvoiceNo . "', 1418 '" . $SQLInvoiceDate . "', 1419 '" . $PeriodNo . "', 1420 '" . $_SESSION['SuppTrans']->GRNAct . "', 1421 '" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('GRN') . ' ' . $EnteredGRN->GRNNo . ' - ' . $EnteredGRN->ItemCode . ' x ' . $EnteredGRN->This_QuantityInv . ' @ ' . $_SESSION['SuppTrans']->CurrCode . ' ' . $EnteredGRN->ChgPrice . ' @ ' . _('a rate of') . ' ' . $_SESSION['SuppTrans']->ExRate . "', 1422 '" . (($EnteredGRN->ChgPrice * $EnteredGRN->This_QuantityInv) / $_SESSION['SuppTrans']->ExRate) . "')"; 1423 1424 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction could not be added because'); 1425 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1426 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1427 } 1428 $LocalTotal += ($EnteredGRN->ChgPrice * $EnteredGRN->This_QuantityInv) / $_SESSION['SuppTrans']->ExRate; 1429 } /* end of GRN postings */ 1430 1431 if ($debug == 1 AND ( abs($_SESSION['SuppTrans']->OvAmount/ $_SESSION['SuppTrans']->ExRate) - $LocalTotal) >0.009999){ 1432 1433 echo '<p>' . _('The total posted to the debit accounts is') . ' ' . 1434 $LocalTotal . ' ' . _('but the sum of OvAmount converted at ExRate') . ' = ' . 1435 ( $_SESSION['SuppTrans']->OvAmount / $_SESSION['SuppTrans']->ExRate); 1436 } 1437 1438 foreach ($_SESSION['SuppTrans']->Taxes as $Tax){ 1439 /* Now the TAX account */ 1440 if ($Tax->TaxOvAmount <>0){ 1441 $SQL = "INSERT INTO gltrans (type, 1442 typeno, 1443 trandate, 1444 periodno, 1445 account, 1446 narrative, 1447 amount) 1448 VALUES (20, 1449 '" . $InvoiceNo . "', 1450 '" . $SQLInvoiceDate . "', 1451 '" . $PeriodNo . "', 1452 '" . $Tax->TaxGLCode . "', 1453 '" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('Inv') . ' ' . 1454 $_SESSION['SuppTrans']->SuppReference . ' ' . $Tax->TaxAuthDescription . ' ' . locale_number_format($Tax->TaxRate*100,2) . '% ' . $_SESSION['SuppTrans']->CurrCode . 1455 $Tax->TaxOvAmount . ' @ ' . _('exch rate') . ' ' . $_SESSION['SuppTrans']->ExRate . "', 1456 '" . ($Tax->TaxOvAmount/ $_SESSION['SuppTrans']->ExRate) . "')"; 1457 1458 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction for the tax could not be added because'); 1459 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1460 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1461 } 1462 1463 } /*end of loop to post the tax */ 1464 /* Now the control account */ 1465 1466 $SQL = "INSERT INTO gltrans (type, 1467 typeno, 1468 trandate, 1469 periodno, 1470 account, 1471 narrative, 1472 amount) 1473 VALUES (20, 1474 '" . $InvoiceNo . "', 1475 '" . $SQLInvoiceDate . "', 1476 '" . $PeriodNo . "', 1477 '" . $_SESSION['SuppTrans']->CreditorsAct . "', 1478 '" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('Inv') . ' ' . 1479 $_SESSION['SuppTrans']->SuppReference . ' ' . $_SESSION['SuppTrans']->CurrCode . 1480 locale_number_format( $_SESSION['SuppTrans']->OvAmount + $TaxTotal,$_SESSION['SuppTrans']->CurrDecimalPlaces) . 1481 ' @ ' . _('a rate of') . ' ' . $_SESSION['SuppTrans']->ExRate . "', 1482 '" . -($LocalTotal + ( $TaxTotal / $_SESSION['SuppTrans']->ExRate)) . "')"; 1483 1484 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction for the control total could not be added because'); 1485 $DbgMsg = _('The following SQL to insert the GL transaction was used'); 1486 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1487 1488 EnsureGLEntriesBalance(20, $InvoiceNo); 1489 } /*Thats the end of the GL postings */ 1490 1491 /*Now insert the invoice into the SuppTrans table*/ 1492 1493 $SQL = "INSERT INTO supptrans (transno, 1494 type, 1495 supplierno, 1496 suppreference, 1497 trandate, 1498 duedate, 1499 ovamount, 1500 ovgst, 1501 rate, 1502 transtext, 1503 inputdate) 1504 VALUES ( 1505 '". $InvoiceNo . "', 1506 20 , 1507 '" . $_SESSION['SuppTrans']->SupplierID . "', 1508 '" . $_SESSION['SuppTrans']->SuppReference . "', 1509 '" . $SQLInvoiceDate . "', 1510 '" . FormatDateForSQL($_SESSION['SuppTrans']->DueDate) . "', 1511 '" . $_SESSION['SuppTrans']->OvAmount . "', 1512 '" . $TaxTotal . "', 1513 '" . $_SESSION['SuppTrans']->ExRate . "', 1514 '" . $_SESSION['SuppTrans']->Comments . "', 1515 '" . Date('Y-m-d') ."')"; 1516 1517 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The supplier invoice transaction could not be added to the database because'); 1518 $DbgMsg = _('The following SQL to insert the supplier invoice was used'); 1519 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1520 $SuppTransID = DB_Last_Insert_ID('supptrans','id'); 1521 1522 /* Insert the tax totals for each tax authority where tax was charged on the invoice */ 1523 foreach ($_SESSION['SuppTrans']->Taxes AS $TaxTotals) { 1524 1525 $SQL = "INSERT INTO supptranstaxes (supptransid, 1526 taxauthid, 1527 taxamount) 1528 VALUES ( 1529 '" . $SuppTransID . "', 1530 '" . $TaxTotals->TaxAuthID . "', 1531 '" . $TaxTotals->TaxOvAmount . "')"; 1532 1533 $ErrMsg =_('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The supplier transaction taxes records could not be inserted because'); 1534 $DbgMsg = _('The following SQL to insert the supplier transaction taxes record was used:'); 1535 $Result = DB_query($SQL,$ErrMsg,$DbgMsg,true); 1536 } 1537 1538 /* Now update the GRN and PurchOrderDetails records for amounts invoiced - can't use the other loop through the GRNs as this was only where the GL link to credtors is active */ 1539 1540 foreach ($_SESSION['SuppTrans']->GRNs as $EnteredGRN){ 1541 1542 //in local currency 1543 $ActualCost = $EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate; 1544 $PurchPriceVar = $EnteredGRN->This_QuantityInv * ($ActualCost - $EnteredGRN->StdCostUnit); 1545 1546 $SQL = "UPDATE purchorderdetails 1547 SET qtyinvoiced = qtyinvoiced + " . $EnteredGRN->This_QuantityInv .", 1548 actprice = '" . $EnteredGRN->ChgPrice . "' 1549 WHERE podetailitem = '" . $EnteredGRN->PODetailItem . "'"; 1550 1551 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The quantity invoiced of the purchase order line could not be updated because'); 1552 1553 $DbgMsg = _('The following SQL to update the purchase order details was used'); 1554 1555 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1556 1557 $SQL = "UPDATE grns 1558 SET quantityinv = quantityinv + " . $EnteredGRN->This_QuantityInv . " 1559 WHERE grnno = '" . $EnteredGRN->GRNNo . "'"; 1560 1561 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The quantity invoiced off the goods received record could not be updated because'); 1562 $DbgMsg = _('The following SQL to update the GRN quantity invoiced was used'); 1563 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1564 1565 $SQL = "INSERT INTO suppinvstogrn VALUES ('" . $InvoiceNo . "', 1566 '" . $EnteredGRN->GRNNo . "')"; 1567 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The invoice could not be mapped to the 1568 goods received record because'); 1569 $DbgMsg = _('The following SQL to map the invoice to the GRN was used'); 1570 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1571 1572 if (mb_strlen($EnteredGRN->ShiptRef)>0 AND $EnteredGRN->ShiptRef != '0'){ 1573 /* insert the shipment charge records */ 1574 $SQL = "INSERT INTO shipmentcharges (shiptref, 1575 transtype, 1576 transno, 1577 stockid, 1578 value) 1579 VALUES ( 1580 '" . $EnteredGRN->ShiptRef . "', 1581 20, 1582 '" . $InvoiceNo . "', 1583 '" . $EnteredGRN->ItemCode . "', 1584 '" . ($EnteredGRN->This_QuantityInv * $EnteredGRN->ChgPrice) / $_SESSION['SuppTrans']->ExRate . "')"; 1585 1586 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The shipment charge record for the shipment') . 1587 ' ' . $EnteredGRN->ShiptRef . ' ' . _('could not be added because'); 1588 $DbgMsg = _('The following SQL to insert the Shipment charge record was used'); 1589 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1590 1591 } //end of adding GRN shipment charges 1592 else { 1593 /*so its not a GRN shipment item its a plain old stock item */ 1594 1595 if ($PurchPriceVar !=0){ /* don't bother with any of this lot if there is no difference ! */ 1596 1597 if (mb_strlen($EnteredGRN->ItemCode)>0 OR $EnteredGRN->ItemCode != ''){ /*so it is a stock item */ 1598 1599 /*We need to: 1600 * 1601 * a) update the stockmove for the delivery to reflect the actual cost of the delivery 1602 * 1603 * b) If a WeightedAverageCosting system and the stock quantity on hand now is negative then the cost that has gone to sales analysis and the cost of sales stock movement records will have been incorrect ... attempt to fix it retrospectively 1604 */ 1605 /*Get the location that the stock was booked into */ 1606 $result = DB_query("SELECT intostocklocation 1607 FROM purchorders 1608 WHERE orderno='" . $EnteredGRN->PONo . "'"); 1609 $LocRow = DB_fetch_array($result); 1610 $LocCode = $LocRow['intostocklocation']; 1611 1612 /* First update the stockmoves delivery cost */ 1613 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock movement record for the delivery could not have the cost updated to the actual cost'); 1614 $SQL = "UPDATE stockmoves SET price = '" . $ActualCost . "' 1615 WHERE stockid='" .$EnteredGRN->ItemCode . "' 1616 AND type=25 1617 AND loccode='" . $LocCode . "' 1618 AND transno='" . $EnteredGRN->GRNBatchNo . "'"; 1619 1620 $result = DB_query($SQL,$ErrMsg,$DbgMsg,True); 1621 1622 if ($_SESSION['WeightedAverageCosting']==1){ 1623 /* 1624 * How many in stock now? 1625 * The quantity being invoiced here - $EnteredGRN->This_QuantityInv 1626 * If the quantity in stock now is less than the quantity being invoiced 1627 * here then some items sold will not have had this cost factored in 1628 * The cost of these items = $ActualCost 1629 */ 1630 1631 $sql ="SELECT sum(quantity) 1632 FROM locstock 1633 WHERE stockid='" . $EnteredGRN->ItemCode . "'"; 1634 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The quantity on hand could not be retrieved from the database'); 1635 $DbgMsg = _('The following SQL to retrieve the total stock quantity was used'); 1636 $Result = DB_query($sql, $ErrMsg, $DbgMsg); 1637 $QtyRow = DB_fetch_row($Result); 1638 $TotalQuantityOnHand = $QtyRow[0]; 1639 1640 /* If the quantity on hand is less the quantity charged on this invoice then some must have been sold and the price variance should be reflected in the cost of sales*/ 1641 1642 if ($EnteredGRN->This_QuantityInv > $TotalQuantityOnHand){ 1643 1644 /* The variance to the extent of the quantity invoiced should also be written off against the sales analysis cost - as sales analysis would have been created using the cost at the time the sale was made... this was incorrect as hind-sight has shown here. However, how to determine when these were last sold? To update the sales analysis cost. Work through the last 6 months sales analysis from the latest period in which this invoice is being posted and prior. 1645 1646 The assumption here is that the goods have been sold prior to the purchase invoice being entered so it is necessary to back track on the sales analysis cost. 1647 * Note that this will mean that posting to GL COGS will not agree to the cost of sales from the sales analysis 1648 * Of course the price variances will need to be included in COGS as well 1649 * */ 1650 1651 $QuantityVarianceAllocated = $EnteredGRN->This_QuantityInv; 1652 $CostVarPerUnit = $ActualCost - $EnteredGRN->StdCostUnit; 1653 $PeriodAllocated = $PeriodNo; 1654 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales analysis records could not be updated for the cost variances on this purchase invoice'); 1655 1656 while ($QuantityVarianceAllocated >0) { 1657 $SalesAnalResult=DB_query("SELECT cust, 1658 custbranch, 1659 typeabbrev, 1660 periodno, 1661 stkcategory, 1662 area, 1663 salesperson, 1664 cost, 1665 qty 1666 FROM salesanalysis 1667 WHERE salesanalysis.stockid = '" . $EnteredGRN->ItemCode . "' 1668 AND salesanalysis.budgetoractual=1 1669 AND periodno='" . $PeriodAllocated . "'"); 1670 if (DB_num_rows($SalesAnalResult)>0){ 1671 while ($SalesAnalRow = DB_fetch_array($SalesAnalResult) AND $QuantityVarianceAllocated >0){ 1672 if ($SalesAnalRow['qty']<=$QuantityVarianceAllocated){ 1673 $QuantityVarianceAllocated -= $SalesAnalRow['qty']; 1674 $QuantityAllocated = $SalesAnalRow['qty']; 1675 } else { 1676 $QuantityAllocated = $QuantityVarianceAllocated; 1677 $QuantityVarianceAllocated=0; 1678 } 1679 $UpdSalAnalResult = DB_query("UPDATE salesanalysis 1680 SET cost = cost + " . ($CostVarPerUnit * $QuantityAllocated) . " 1681 WHERE cust ='" . $SalesAnalRow['cust'] . "' 1682 AND stockid='" . $EnteredGRN->ItemCode . "' 1683 AND custbranch='" . $SalesAnalRow['custbranch'] . "' 1684 AND typeabbrev='" . $SalesAnalRow['typeabbrev'] . "' 1685 AND periodno='" . $PeriodAllocated . "' 1686 AND area='" . $SalesAnalRow['area'] . "' 1687 AND salesperson='" . $SalesAnalRow['salesperson'] . "' 1688 AND stkcategory='" . $SalesAnalRow['stkcategory'] . "' 1689 AND budgetoractual=1", 1690 $ErrMsg, 1691 $DbgMsg, 1692 True); 1693 } 1694 } //end if there were sales in that period 1695 $PeriodAllocated--; //decrement the period 1696 if ($PeriodNo - $PeriodAllocated >6) { 1697 /*if more than 6 months ago when sales were made then forget it */ 1698 break; 1699 } 1700 } /*end loop around different periods to see which sales analysis records to update */ 1701 1702 /*now we need to work back through the sales stockmoves up to the quantity on this purchase invoice to update costs 1703 * Only go back up to 6 months looking for stockmoves and 1704 * Only in the stock location where the purchase order was received 1705 * into - if the stock was transferred to another location then 1706 * we cannot adjust for this */ 1707 $result = DB_query("SELECT stkmoveno, 1708 type, 1709 qty, 1710 standardcost 1711 FROM stockmoves 1712 WHERE loccode='" . $LocCode . "' 1713 AND qty < 0 1714 AND stockid='" . $EnteredGRN->ItemCode . "' 1715 AND trandate>='" . FormatDateForSQL(DateAdd($_SESSION['SuppTrans']->TranDate,'m',-6)) . "' 1716 ORDER BY stkmoveno DESC"); 1717 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock movements for invoices cannot be updated for the cost variances on this purchase invoice'); 1718 $QuantityVarianceAllocated = $EnteredGRN->This_QuantityInv; 1719 while ($StkMoveRow = DB_fetch_array($result) AND $QuantityVarianceAllocated >0){ 1720 if ($StkMoveRow['qty']+$QuantityVarianceAllocated>0){ 1721 if ($StkMoveRow['type']==10) { //its a sales invoice 1722 $result = DB_query("UPDATE stockmoves 1723 SET standardcost = '" . $ActualCost . "' 1724 WHERE stkmoveno = '" . $StkMoveRow['stkmoveno'] . "'", 1725 $ErrMsg, 1726 $DbgMsg, 1727 True); 1728 } 1729 } else { //Only $QuantityVarianceAllocated left to allocate so need need to apportion cost using weighted average 1730 if ($StkMoveRow['type']==10) { //its a sales invoice 1731 1732 $WACost = (((-$StkMoveRow['qty']- $QuantityVarianceAllocated)*$StkMoveRow['standardcost'])+($QuantityVarianceAllocated*$ActualCost))/-$StkMoveRow['qty']; 1733 1734 $UpdStkMovesResult = DB_query("UPDATE stockmoves 1735 SET standardcost = '" . $WACost . "' 1736 WHERE stkmoveno = '" . $StkMoveRow['stkmoveno'] . "'", 1737 $ErrMsg, 1738 $DbgMsg, 1739 True); 1740 } 1741 } 1742 $QuantityVarianceAllocated+=$StkMoveRow['qty']; 1743 } 1744 } // end if the quantity being invoiced here is greater than the current stock on hand 1745 1746 /*Now to update the stock cost with the new weighted average */ 1747 1748 /*Need to consider what to do if the cost has been changed manually between receiving the stock and entering the invoice - this code assumes there has been no cost updates made manually and all the price variance is posted to stock. 1749 1750 A nicety or important?? */ 1751 1752 1753 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost could not be updated because'); 1754 $DbgMsg = _('The following SQL to update the cost was used'); 1755 1756 if ($TotalQuantityOnHand>0) { 1757 1758 $CostIncrement = ($PurchPriceVar - $WriteOffToVariances) / $TotalQuantityOnHand; 1759 1760 $sql = "UPDATE stockmaster 1761 SET lastcost=materialcost+overheadcost+labourcost, 1762 materialcost=materialcost+" . $CostIncrement . " 1763 WHERE stockid='" . $EnteredGRN->ItemCode . "'"; 1764 $Result = DB_query($sql, $ErrMsg, $DbgMsg, True); 1765 } else { 1766 /* if stock is negative then update the cost to this cost */ 1767 $sql = "UPDATE stockmaster 1768 SET lastcost=materialcost+overheadcost+labourcost, 1769 materialcost='" . $ActualCost . "' 1770 WHERE stockid='" . $EnteredGRN->ItemCode . "'"; 1771 $Result = DB_query($sql, $ErrMsg, $DbgMsg, True); 1772 } 1773 } /* End if it is weighted average costing we are working with */ 1774 } /*Its a stock item */ 1775 } /* There was a price variance */ 1776 } 1777 if ($EnteredGRN->AssetID!=0) { //then it is an asset 1778 1779 if ($PurchPriceVar !=0) { 1780 /*Add the fixed asset trans for the difference in the cost */ 1781 $SQL = "INSERT INTO fixedassettrans (assetid, 1782 transtype, 1783 transno, 1784 transdate, 1785 periodno, 1786 inputdate, 1787 fixedassettranstype, 1788 amount) 1789 VALUES ('" . $EnteredGRN->AssetID . "', 1790 20, 1791 '" . $InvoiceNo . "', 1792 '" . $SQLInvoiceDate . "', 1793 '" . $PeriodNo . "', 1794 '" . Date('Y-m-d') . "', 1795 'cost', 1796 '" . ($PurchPriceVar) . "')"; 1797 $ErrMsg = _('CRITICAL ERROR! NOTE DOWN THIS ERROR AND SEEK ASSISTANCE The fixed asset transaction could not be inserted because'); 1798 $DbgMsg = _('The following SQL to insert the fixed asset transaction record was used'); 1799 $Result = DB_query($SQL,$ErrMsg, $DbgMsg, true); 1800 1801 /*Now update the asset cost in fixedassets table */ 1802 $SQL = "UPDATE fixedassets SET cost = cost + " . ($PurchPriceVar) . " 1803 WHERE assetid = '" . $EnteredGRN->AssetID . "'"; 1804 1805 $ErrMsg = _('CRITICAL ERROR! NOTE DOWN THIS ERROR AND SEEK ASSISTANCE. The fixed asset cost could not be updated because:'); 1806 $DbgMsg = _('The following SQL was used to attempt the update of the asset cost:'); 1807 $Result = DB_query($SQL,$ErrMsg, $DbgMsg, true); 1808 } //end if there was a difference in the cost 1809 } //the item was an asset received on a purchase order 1810 } /* end of the GRN loop to do the updates for the quantity of order items the supplier has invoiced */ 1811 1812 /*Add shipment charges records as necessary */ 1813 foreach ($_SESSION['SuppTrans']->Shipts as $ShiptChg){ 1814 1815 $SQL = "INSERT INTO shipmentcharges (shiptref, 1816 transtype, 1817 transno, 1818 value) 1819 VALUES ('" . $ShiptChg->ShiptRef . "', 1820 '20', 1821 '" . $InvoiceNo . "', 1822 '" . $ShiptChg->Amount/ $_SESSION['SuppTrans']->ExRate . "')"; 1823 1824 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The shipment charge record for the shipment') . 1825 ' ' . $ShiptChg->ShiptRef . ' ' . _('could not be added because'); 1826 1827 $DbgMsg = _('The following SQL to insert the Shipment charge record was used'); 1828 1829 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1830 1831 } 1832 /*Add contract charges records as necessary */ 1833 1834 foreach ($_SESSION['SuppTrans']->Contracts as $Contract){ 1835 1836 if($Contract->AnticipatedCost ==true){ 1837 $Anticipated =1; 1838 } else { 1839 $Anticipated =0; 1840 } 1841 $SQL = "INSERT INTO contractcharges (contractref, 1842 transtype, 1843 transno, 1844 amount, 1845 narrative, 1846 anticipated) 1847 VALUES ('" . $Contract->ContractRef . "', 1848 '20', 1849 '" . $InvoiceNo . "', 1850 '" . $Contract->Amount/ $_SESSION['SuppTrans']->ExRate . "', 1851 '" . $Contract->Narrative . "', 1852 '" . $Anticipated . "')"; 1853 1854 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The contract charge record for contract') . ' ' . $Contract->ContractRef . ' ' . _('could not be added because'); 1855 $DbgMsg = _('The following SQL to insert the contract charge record was used'); 1856 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, True); 1857 } 1858 1859 foreach ($_SESSION['SuppTrans']->Assets as $AssetAddition){ 1860 1861 /*Asset additions need to have 1862 * 1. A fixed asset transaction inserted for the cost 1863 * 2. A general ledger transaction to fixed asset cost account if creditors linked 1864 * 3. The fixedasset table cost updated by the addition 1865 */ 1866 1867 /* First the fixed asset transaction */ 1868 $SQL = "INSERT INTO fixedassettrans (assetid, 1869 transtype, 1870 transno, 1871 transdate, 1872 periodno, 1873 inputdate, 1874 fixedassettranstype, 1875 amount) 1876 VALUES ('" . $AssetAddition->AssetID . "', 1877 20, 1878 '" . $InvoiceNo . "', 1879 '" . $SQLInvoiceDate . "', 1880 '" . $PeriodNo . "', 1881 '" . Date('Y-m-d') . "', 1882 '" . _('cost') . "', 1883 '" . ($AssetAddition->Amount / $_SESSION['SuppTrans']->ExRate) . "')"; 1884 $ErrMsg = _('CRITICAL ERROR! NOTE DOWN THIS ERROR AND SEEK ASSISTANCE The fixed asset transaction could not be inserted because'); 1885 $DbgMsg = _('The following SQL to insert the fixed asset transaction record was used'); 1886 $Result = DB_query($SQL,$ErrMsg, $DbgMsg, true); 1887 1888 /*Now update the asset cost in fixedassets table */ 1889 $result = DB_query("SELECT datepurchased 1890 FROM fixedassets 1891 WHERE assetid='" . $AssetAddition->AssetID . "'"); 1892 $AssetRow = DB_fetch_array($result); 1893 1894 $SQL = "UPDATE fixedassets SET cost = cost + " . ($AssetAddition->Amount / $_SESSION['SuppTrans']->ExRate) ; 1895 if ($AssetRow['datepurchased']=='0000-00-00'){ 1896 $SQL .= ", datepurchased='" . $SQLInvoiceDate . "'"; 1897 } 1898 $SQL .= " WHERE assetid = '" . $AssetAddition->AssetID . "'"; 1899 $ErrMsg = _('CRITICAL ERROR! NOTE DOWN THIS ERROR AND SEEK ASSISTANCE. The fixed asset cost and date purchased was not able to be updated because:'); 1900 $DbgMsg = _('The following SQL was used to attempt the update of the cost and the date the asset was purchased'); 1901 $Result = DB_query($SQL,$ErrMsg, $DbgMsg, true); 1902 } //end of non-gl fixed asset stuff 1903 1904 $Result = DB_Txn_Commit(); 1905 1906 prnMsg(_('Supplier invoice number') . ' ' . $InvoiceNo . ' ' . _('has been processed'),'success'); 1907 echo '<br /> 1908 <div class="centre"> 1909 <a href="' . $RootPath . '/SupplierInvoice.php?&SupplierID=' .$_SESSION['SuppTrans']->SupplierID . '">' . _('Enter another Invoice for this Supplier') . '</a> 1910 <br /> 1911 <a href="' . $RootPath . '/Payments.php?&SupplierID=' .$_SESSION['SuppTrans']->SupplierID . '&Amount=' . ($_SESSION['SuppTrans']->OvAmount+$TaxTotal) . '">' . _('Enter payment') . '</a> 1912 </div>'; 1913 unset( $_SESSION['SuppTrans']->GRNs); 1914 unset( $_SESSION['SuppTrans']->Shipts); 1915 unset( $_SESSION['SuppTrans']->GLCodes); 1916 unset( $_SESSION['SuppTrans']->Contracts); 1917 unset( $_SESSION['SuppTrans']); 1918 } 1919 1920} /*end of process invoice */ 1921 1922if(isset($InputError) AND $InputError==true){ //add a link to return if users make input errors. 1923 echo '<div class="centre"><a href="'.$RootPath.'/SupplierInvoice.php" >' . _('Back to Invoice Entry') . '</a></div>'; 1924} //end of return link for input errors 1925 1926include('includes/footer.php'); 1927?> 1928