1<?php 2 3 4// InsertSalesOrderHeader and ModifySalesOrderHeader have date fields 5// which need to be converted to the appropriate format. This is 6// a list of such fields used to detect date values and format appropriately. 7$SOH_DateFields = array ('orddate', 8 'deliverydate', 9 'datepackingslipprinted', 10 'quotedate', 11 'confirmeddate' ); 12 13/* Check that the custmerref field is 50 characters or less long */ 14 function VerifyCustomerRef($customerref, $i, $Errors) { 15 if (mb_strlen($customerref)>50) { 16 $Errors[$i] = InvalidCustomerRef; 17 } 18 return $Errors; 19 } 20 21/* Check that the buyername field is 50 characters or less long */ 22 function VerifyBuyerName($buyername, $i, $Errors) { 23 if (mb_strlen($buyername)>50) { 24 $Errors[$i] = InvalidBuyerName; 25 } 26 return $Errors; 27 } 28 29/* Check that the comments field is 256 characters or less long */ 30 function VerifyComments($comments, $i, $Errors) { 31 if (mb_strlen($comments)>256) { 32 $Errors[$i] = InvalidComments; 33 } 34 return $Errors; 35 } 36 37/* Check that the order date is a valid date. The date 38 * must be in the same format as the date format specified in the 39 * target webERP company */ 40 function VerifyOrderDate($orddate, $i, $Errors) { 41 $sql="SELECT confvalue FROM config WHERE confname='DefaultDateFormat'"; 42 $result=api_DB_query($sql); 43 $myrow=DB_fetch_array($result); 44 $DateFormat=$myrow[0]; 45 if (mb_strstr($orddate,"/")) { 46 $DateArray = explode('/',$orddate); 47 } elseif (mb_strstr($orddate,".")) { 48 $DateArray = explode('.',$orddate); 49 } 50 if ($DateFormat=='d/m/Y') { 51 $Day=$DateArray[0]; 52 $Month=$DateArray[1]; 53 $Year=$DateArray[2]; 54 } elseif ($DateFormat=='m/d/Y') { 55 $Day=$DateArray[1]; 56 $Month=$DateArray[0]; 57 $Year=$DateArray[2]; 58 } elseif ($DateFormat=='Y/m/d') { 59 $Day=$DateArray[2]; 60 $Month=$DateArray[1]; 61 $Year=$DateArray[0]; 62 } elseif ($DateFormat=='d.m.Y') { 63 $Day=$DateArray[0]; 64 $Month=$DateArray[1]; 65 $Year=$DateArray[2]; 66 } 67 if (!checkdate(intval($Month), intval($Day), intval($Year))) { 68 $Errors[$i] = InvalidOrderDate; 69 } 70 return $Errors; 71 } 72 73/* Check that the order type is set up in the weberp database */ 74 function VerifyOrderType($ordertype, $i, $Errors) { 75 $Searchsql = "SELECT COUNT(typeabbrev) 76 FROM salestypes 77 WHERE typeabbrev='" . $ordertype."'"; 78 $SearchResult=api_DB_query($Searchsql); 79 $answer = DB_fetch_row($SearchResult); 80 if ($answer[0] == 0) { 81 $Errors[$i] = SalesTypeNotSetup; 82 } 83 return $Errors; 84 } 85 86/* Check that the delivery name field is 40 characters or less long */ 87 function VerifyDeliverTo($delverto, $i, $Errors) { 88 if (mb_strlen($delverto)>40) { 89 $Errors[$i] = InvalidDeliverTo; 90 } 91 return $Errors; 92 } 93 94/* Verify that the last freight cost is numeric */ 95 function VerifyFreightCost($freightcost, $i, $Errors) { 96 if (!is_numeric($freightcost)) { 97 $Errors[$i] = InvalidFreightCost; 98 } 99 return $Errors; 100 } 101 102/* Check that the from stock location is set up in the weberp database */ 103 function VerifyFromStockLocation($FromStockLocn, $i, $Errors) { 104 $Searchsql = "SELECT COUNT(loccode) 105 FROM locations 106 WHERE loccode='". $FromStockLocn."'"; 107 $SearchResult=api_DB_query($Searchsql); 108 $answer = DB_fetch_row($SearchResult); 109 if ($answer[0] == 0) { 110 $Errors[$i] = LocationCodeNotSetup; 111 } 112 return $Errors; 113 } 114 115/* Check that the delivery date is a valid date. The date 116 * must be in the same format as the date format specified in the 117 * target webERP company */ 118 function VerifyDeliveryDate($DeliveryDate, $i, $Errors) { 119 $sql="SELECT confvalue FROM config WHERE confname='DefaultDateFormat'"; 120 $result=api_DB_query($sql); 121 $myrow=DB_fetch_array($result); 122 $DateFormat=$myrow[0]; 123 if (mb_strstr($DeliveryDate,'/')) { 124 $DateArray = explode('/',$DeliveryDate); 125 } elseif (mb_strstr($PeriodEnd,'.')) { 126 $DateArray = explode('.',$DeliveryDate); 127 } 128 if ($DateFormat=='d/m/Y') { 129 $Day=$DateArray[0]; 130 $Month=$DateArray[1]; 131 $Year=$DateArray[2]; 132 } elseif ($DateFormat=='m/d/Y') { 133 $Day=$DateArray[1]; 134 $Month=$DateArray[0]; 135 $Year=$DateArray[2]; 136 } elseif ($DateFormat=='Y/m/d') { 137 $Day=$DateArray[2]; 138 $Month=$DateArray[1]; 139 $Year=$DateArray[0]; 140 } elseif ($DateFormat=='d.m.Y') { 141 $Day=$DateArray[0]; 142 $Month=$DateArray[1]; 143 $Year=$DateArray[2]; 144 } 145 if (!checkdate(intval($Month), intval($Day), intval($Year))) { 146 $Errors[$i] = InvalidDeliveryDate; 147 } 148 return $Errors; 149 } 150 151/* Verify that the quotation flag is a 1 or 0 */ 152 function VerifyQuotation($quotation, $i, $Errors) { 153 if ($quotation!=0 and $quotation!=1) { 154 $Errors[$i] = InvalidQuotationFlag; 155 } 156 return $Errors; 157 } 158 159/* Fetch the next line number */ 160 function GetOrderLineNumber($OrderNo, $i, $Errors) { 161 $linesql = "SELECT MAX(orderlineno) 162 FROM salesorderdetails 163 WHERE orderno='" . $OrderNo . "'"; 164 $lineresult = api_DB_query($linesql); 165 if ($myrow=DB_fetch_row($lineresult)) { 166 return $myrow[0] + 1; 167 } else { 168 return 1; 169 } 170 } 171 172/* Check that the order header already exists */ 173 function VerifyOrderHeaderExists($OrderNo, $i, $Errors) { 174 $Searchsql = "SELECT COUNT(orderno) 175 FROM salesorders 176 WHERE orderno='".$OrderNo."'"; 177 $SearchResult=api_DB_query($Searchsql); 178 $answer = DB_fetch_row($SearchResult); 179 if ($answer[0] == 0) { 180 $Errors[$i] = OrderHeaderNotSetup; 181 } 182 return $Errors; 183 } 184 185/* Verify that the unit price is numeric */ 186 function VerifyUnitPrice($unitprice, $i, $Errors) { 187 if (!is_numeric($unitprice)) { 188 $Errors[$i] = InvalidUnitPrice; 189 } 190 return $Errors; 191 } 192 193/* Verify that the quantity is numeric */ 194 function VerifyQuantity($quantity, $i, $Errors) { 195 if (!is_numeric($quantity)) { 196 $Errors[$i] = InvalidQuantity; 197 } 198 return $Errors; 199 } 200 201/* Verify that the discount percent is numeric */ 202 function VerifyDiscountPercent($discountpercent, $i, $Errors) { 203 if (!is_numeric($discountpercent) or $discountpercent>100) { 204 $Errors[$i] = InvalidDiscountPercent; 205 } 206 return $Errors; 207 } 208 209/* Check that the narrative field is 256 characters or less long */ 210 function VerifyNarrative($narrative, $i, $Errors) { 211 if (mb_strlen($narrative)>256) { 212 $Errors[$i] = InvalidNarrative; 213 } 214 return $Errors; 215 } 216 217/* Check that the poline field is 10 characters or less long */ 218 function VerifyPOLine($poline, $i, $Errors) { 219 if (mb_strlen($poline)>10) { 220 $Errors[$i] = InvalidPOLine; 221 } 222 return $Errors; 223 } 224 225/* Check that the item due date is a valid date. The date 226 * must be in the same format as the date format specified in the 227 * target webERP company */ 228 function VerifyItemDueDate($ItemDue, $i, $Errors) { 229 $sql="SELECT confvalue FROM config WHERE confname='DefaultDateFormat'"; 230 $result=api_DB_query($sql); 231 $myrow=DB_fetch_array($result); 232 $DateFormat=$myrow[0]; 233 if (mb_strstr($ItemDue,'/')) { 234 $DateArray = explode('/',$ItemDue); 235 } elseif (mb_strstr($PeriodEnd,'.')) { 236 $DateArray = explode('.',$ItemDue); 237 } 238 if ($DateFormat=='d/m/Y') { 239 $Day=$DateArray[0]; 240 $Month=$DateArray[1]; 241 $Year=$DateArray[2]; 242 } elseif ($DateFormat=='m/d/Y') { 243 $Day=$DateArray[1]; 244 $Month=$DateArray[0]; 245 $Year=$DateArray[2]; 246 } elseif ($DateFormat=='Y/m/d') { 247 $Day=$DateArray[2]; 248 $Month=$DateArray[1]; 249 $Year=$DateArray[0]; 250 } elseif ($DateFormat=='d.m.Y') { 251 $Day=$DateArray[0]; 252 $Month=$DateArray[1]; 253 $Year=$DateArray[2]; 254 } 255 if (!checkdate(intval($Month), intval($Day), intval($Year))) { 256 $Errors[$i] = InvalidItemDueDate; 257 } 258 return $Errors; 259 } 260 261/* Create a customer sales order header in webERP. If successful 262 * returns $Errors[0]=0 and $Errors[1] will contain the order number. 263 */ 264 function InsertSalesOrderHeader($OrderHeader, $user, $password) { 265 $Errors = array(); 266 $db = db($user, $password); 267 if (gettype($db)=='integer') { 268 $Errors[0]=NoAuthorisation; 269 return $Errors; 270 } 271 foreach ($OrderHeader as $key => $value) { 272 $OrderHeader[$key] = DB_escape_string($value); 273 } 274 $Errors=VerifyDebtorExists($OrderHeader['debtorno'], sizeof($Errors), $Errors); 275 $Errors=VerifyBranchNoExists($OrderHeader['debtorno'],$OrderHeader['branchcode'], sizeof($Errors), $Errors); 276 if (isset($OrderHeader['customerref'])){ 277 $Errors=VerifyCustomerRef($OrderHeader['customerref'], sizeof($Errors), $Errors); 278 } 279 if (isset($OrderHeader['buyername'])){ 280 $Errors=VerifyBuyerName($OrderHeader['buyername'], sizeof($Errors), $Errors); 281 } 282 if (isset($OrderHeader['comments'])){ 283 $Errors=VerifyComments($OrderHeader['comments'], sizeof($Errors), $Errors); 284 } 285 if (isset($OrderHeader['orddate'])){ 286 $Errors=VerifyOrderDate($OrderHeader['orddate'], sizeof($Errors), $Errors); 287 } 288 if (isset($OrderHeader['ordertype'])){ 289 $Errors=VerifyOrderType($OrderHeader['ordertype'], sizeof($Errors), $Errors); 290 } 291 if (isset($OrderHeader['shipvia'])){ 292 $Errors=VerifyShipVia($OrderHeader['shipvia'], sizeof($Errors), $Errors); 293 } 294 if (isset($OrderHeader['deladd1'])){ 295 $Errors=VerifyAddressLine($OrderHeader['deladd1'], 40, sizeof($Errors), $Errors); 296 } 297 if (isset($OrderHeader['deladd2'])){ 298 $Errors=VerifyAddressLine($OrderHeader['deladd2'], 40, sizeof($Errors), $Errors); 299 } 300 if (isset($OrderHeader['deladd3'])){ 301 $Errors=VerifyAddressLine($OrderHeader['deladd3'], 40, sizeof($Errors), $Errors); 302 } 303 if (isset($OrderHeader['deladd4'])){ 304 $Errors=VerifyAddressLine($OrderHeader['deladd4'], 40, sizeof($Errors), $Errors); 305 } 306 if (isset($OrderHeader['deladd5'])){ 307 $Errors=VerifyAddressLine($OrderHeader['deladd5'], 20, sizeof($Errors), $Errors); 308 } 309 if (isset($OrderHeader['deladd6'])){ 310 $Errors=VerifyAddressLine($OrderHeader['deladd6'], 15, sizeof($Errors), $Errors); 311 } 312 if (isset($OrderHeader['contactphone'])){ 313 $Errors=VerifyPhoneNumber($OrderHeader['contactphone'], sizeof($Errors), $Errors); 314 } 315 if (isset($OrderHeader['contactemail'])){ 316 $Errors=VerifyEmailAddress($OrderHeader['contactemail'], sizeof($Errors), $Errors); 317 } 318 if (isset($OrderHeader['deliverto'])){ 319 $Errors=VerifyDeliverTo($OrderHeader['deliverto'], sizeof($Errors), $Errors); 320 } 321 if (isset($OrderHeader['deliverblind'])){ 322 $Errors=VerifyDeliverBlind($OrderHeader['deliverblind'], sizeof($Errors), $Errors); 323 } 324 if (isset($OrderHeader['freightcost'])){ 325 $Errors=VerifyFreightCost($OrderHeader['freightcost'], sizeof($Errors), $Errors); 326 } 327 if (isset($OrderHeader['fromstkloc'])){ 328 $Errors=VerifyFromStockLocation($OrderHeader['fromstkloc'], sizeof($Errors), $Errors); 329 } 330 if (isset($OrderHeader['deliverydate'])){ 331 $Errors=VerifyDeliveryDate($OrderHeader['deliverydate'], sizeof($Errors), $Errors); 332 } 333 if (isset($OrderHeader['quotation'])){ 334 $Errors=VerifyQuotation($OrderHeader['quotation'], sizeof($Errors), $Errors); 335 } 336 $FieldNames=''; 337 $FieldValues=''; 338 global $SOH_DateFields; 339 $OrderHeader['orderno'] = GetNextTransNo(30); 340 foreach ($OrderHeader as $key => $value) { 341 $FieldNames.=$key.', '; 342 if (in_array($key, $SOH_DateFields) ) { 343 $value = FormatDateforSQL($value); // Fix dates 344 } 345 $FieldValues.="'".$value."', "; 346 } 347 $sql = "INSERT INTO salesorders (" . mb_substr($FieldNames,0,-2) . ") 348 VALUES (" . mb_substr($FieldValues,0,-2). ")"; 349 if (sizeof($Errors)==0) { 350 351 $result = api_DB_Query($sql); 352 353 if (DB_error_no() != 0) { 354 //$Errors[0] = DatabaseUpdateFailed; 355 $Errors[0] = $sql; 356 } else { 357 $Errors[0]=0; 358 $Errors[1]=$OrderHeader['orderno']; 359 } 360 } 361 return $Errors; 362 } 363 364/* Modify a customer sales order header in webERP. 365 */ 366 function ModifySalesOrderHeader($OrderHeader, $user, $password) { 367 $Errors = array(); 368 $db = db($user, $password); 369 if (gettype($db)=='integer') { 370 $Errors[0]=NoAuthorisation; 371 return $Errors; 372 } 373 foreach ($OrderHeader as $key => $value) { 374 $OrderHeader[$key] = DB_escape_string($value); 375 } 376 $Errors=VerifyOrderHeaderExists($OrderHeader['orderno'], sizeof($Errors), $Errors); 377 $Errors=VerifyDebtorExists($OrderHeader['debtorno'], sizeof($Errors), $Errors); 378 $Errors=VerifyBranchNoExists($OrderHeader['debtorno'],$OrderHeader['branchcode'], sizeof($Errors), $Errors); 379 if (isset($OrderHeader['customerref'])){ 380 $Errors=VerifyCustomerRef($OrderHeader['customerref'], sizeof($Errors), $Errors); 381 } 382 if (isset($OrderHeader['buyername'])){ 383 $Errors=VerifyBuyerName($OrderHeader['buyername'], sizeof($Errors), $Errors); 384 } 385 if (isset($OrderHeader['comments'])){ 386 $Errors=VerifyComments($OrderHeader['comments'], sizeof($Errors), $Errors); 387 } 388 if (isset($OrderHeader['orddate'])){ 389 $Errors=VerifyOrderDate($OrderHeader['orddate'], sizeof($Errors), $Errors); 390 } 391 if (isset($OrderHeader['ordertype'])){ 392 $Errors=VerifyOrderType($OrderHeader['ordertype'], sizeof($Errors), $Errors); 393 } 394 if (isset($OrderHeader['shipvia'])){ 395 $Errors=VerifyShipVia($OrderHeader['shipvia'], sizeof($Errors), $Errors); 396 } 397 if (isset($OrderHeader['deladd1'])){ 398 $Errors=VerifyAddressLine($OrderHeader['deladd1'], 40, sizeof($Errors), $Errors); 399 } 400 if (isset($OrderHeader['deladd2'])){ 401 $Errors=VerifyAddressLine($OrderHeader['deladd2'], 40, sizeof($Errors), $Errors); 402 } 403 if (isset($OrderHeader['deladd3'])){ 404 $Errors=VerifyAddressLine($OrderHeader['deladd3'], 40, sizeof($Errors), $Errors); 405 } 406 if (isset($OrderHeader['deladd4'])){ 407 $Errors=VerifyAddressLine($OrderHeader['deladd4'], 40, sizeof($Errors), $Errors); 408 } 409 if (isset($OrderHeader['deladd5'])){ 410 $Errors=VerifyAddressLine($OrderHeader['deladd5'], 20, sizeof($Errors), $Errors); 411 } 412 if (isset($OrderHeader['deladd6'])){ 413 $Errors=VerifyAddressLine($OrderHeader['deladd6'], 15, sizeof($Errors), $Errors); 414 } 415 if (isset($OrderHeader['contactphone'])){ 416 $Errors=VerifyPhoneNumber($OrderHeader['contactphone'], sizeof($Errors), $Errors); 417 } 418 if (isset($OrderHeader['contactemail'])){ 419 $Errors=VerifyEmailAddress($OrderHeader['contactemail'], sizeof($Errors), $Errors); 420 } 421 if (isset($OrderHeader['deliverto'])){ 422 $Errors=VerifyDeliverTo($OrderHeader['deliverto'], sizeof($Errors), $Errors); 423 } 424 if (isset($OrderHeader['deliverblind'])){ 425 $Errors=VerifyDeliverBlind($OrderHeader['deliverblind'], sizeof($Errors), $Errors); 426 } 427 if (isset($OrderHeader['freightcost'])){ 428 $Errors=VerifyFreightCost($OrderHeader['freightcost'], sizeof($Errors), $Errors); 429 } 430 if (isset($OrderHeader['fromstkloc'])){ 431 $Errors=VerifyFromStockLocation($OrderHeader['fromstkloc'], sizeof($Errors), $Errors); 432 } 433 if (isset($OrderHeader['deliverydate'])){ 434 $Errors=VerifyDeliveryDate($OrderHeader['deliverydate'], sizeof($Errors), $Errors); 435 } 436 if (isset($OrderHeader['quotation'])){ 437 $Errors=VerifyQuotation($OrderHeader['quotation'], sizeof($Errors), $Errors); 438 } 439 global $SOH_DateFields; 440 $sql='UPDATE salesorders SET '; 441 foreach ($OrderHeader as $key => $value) { 442 if (in_array($key, $SOH_DateFields) ) { 443 $value = FormatDateforSQL($value); // Fix dates 444 } 445 $sql .= $key.'="'.$value.'", '; 446 } 447 $sql = mb_substr($sql,0,-2). " WHERE orderno='" . $OrderHeader['orderno']. "'"; 448 if (sizeof($Errors)==0) { 449 $result = api_DB_Query($sql); 450 echo DB_error_no(); 451 if (DB_error_no() != 0) { 452 $Errors[0] = DatabaseUpdateFailed; 453 } else { 454 $Errors[0]=0; 455 } 456 } 457 return $Errors; 458 } 459 460/* Create a customer sales order line in webERP. The order header must 461 * already exist in webERP. 462 */ 463 function InsertSalesOrderLine($OrderLine, $user, $password) { 464 465 $Errors = array(); 466 $db = db($user, $password); 467 if (gettype($db)=='integer') { 468 $Errors[0]=NoAuthorisation; 469 return $Errors; 470 } 471 foreach ($OrderLine as $key => $value) { 472 $OrderLine[$key] = DB_escape_string($value); 473 } 474 $OrderLine['orderlineno'] = GetOrderLineNumber($OrderLine['orderno'], sizeof($Errors), $Errors); 475 $Errors=VerifyOrderHeaderExists($OrderLine['orderno'], sizeof($Errors), $Errors); 476 $Errors=VerifyStockCodeExists($OrderLine['stkcode'], sizeof($Errors), $Errors); 477 if (isset($OrderLine['unitprice'])){ 478 $Errors=VerifyUnitPrice($OrderLine['unitprice'], sizeof($Errors), $Errors); 479 } 480 if (isset($OrderLine['quantity'])){ 481 $Errors=VerifyQuantity($OrderLine['quantity'], sizeof($Errors), $Errors); 482 } 483 if (isset($OrderLine['discountpercent'])){ 484 //$OrderLine['discountpercent'] = $OrderLine['discountpercent'] * 100; 485 $Errors=VerifyDiscountPercent($OrderLine['discountpercent'], sizeof($Errors), $Errors); 486 $OrderLine['discountpercent'] = $OrderLine['discountpercent']/100; 487 } 488 if (isset($OrderLine['narrative'])){ 489 $Errors=VerifyNarrative($OrderLine['narrative'], sizeof($Errors), $Errors); 490 } 491 /* 492 * Not sure why the verification of itemdue doesn't work 493 if (isset($OrderLine['itemdue'])){ 494 $Errors=VerifyItemDueDate($OrderLine['itemdue'], sizeof($Errors), $Errors); 495 } 496 */ 497 if (isset($OrderLine['poline'])){ 498 $Errors=VerifyPOLine($OrderLine['poline'], sizeof($Errors), $Errors); 499 } 500 $FieldNames=''; 501 $FieldValues=''; 502 foreach ($OrderLine as $key => $value) { 503 $FieldNames.=$key.', '; 504 if ($key == 'actualdispatchdate') { 505 $value = FormatDateWithTimeForSQL($value); 506 } elseif ($key == 'itemdue') { 507 $value = FormatDateForSQL($value); 508 } 509 $FieldValues.= "'" . $value . "', "; 510 } 511 512 $sql = "INSERT INTO salesorderdetails (" . mb_substr($FieldNames,0,-2) . ") 513 VALUES (" . mb_substr($FieldValues,0,-2) . ")"; 514 515 if (sizeof($Errors)==0) { 516 $result = api_DB_Query($sql); 517 if (DB_error_no() != 0) { 518 $Errors[0] = DatabaseUpdateFailed; 519 } else { 520 $Errors[0]=0; 521 } 522 } 523 return $Errors; 524 } 525 526/* Modify a customer sales order line in webERP. The order header must 527 * already exist in webERP. 528 */ 529 function ModifySalesOrderLine($OrderLine, $user, $password) { 530 $Errors = array(); 531 $db = db($user, $password); 532 if (gettype($db)=='integer') { 533 $Errors[0]=NoAuthorisation; 534 return $Errors; 535 } 536 foreach ($OrderLine as $key => $value) { 537 $OrderLine[$key] = DB_escape_string($value); 538 } 539 $Errors=VerifyOrderHeaderExists($OrderLine['orderno'], sizeof($Errors), $Errors); 540 $Errors=VerifyStockCodeExists($OrderLine['stkcode'], sizeof($Errors), $Errors); 541 if (isset($OrderLine['unitprice'])){ 542 $Errors=VerifyUnitPrice($OrderLine['unitprice'], sizeof($Errors), $Errors); 543 } 544 if (isset($OrderLine['quantity'])){ 545 $Errors=VerifyQuantity($OrderLine['quantity'], sizeof($Errors), $Errors); 546 } 547 if (isset($OrderLine['discountpercent'])){ 548 //$OrderLine['discountpercent'] = $OrderLine['discountpercent'] * 100; 549 $Errors=VerifyDiscountPercent($OrderLine['discountpercent'], sizeof($Errors), $Errors); 550 $OrderLine['discountpercent'] = $OrderLine['discountpercent']/100; 551 } 552 if (isset($OrderLine['narrative'])){ 553 $Errors=VerifyNarrative($OrderLine['narrative'], sizeof($Errors), $Errors); 554 } 555 if (isset($OrderLine['itemdue'])){ 556 $Errors=VerifyItemDueDate($OrderLine['itemdue'], sizeof($Errors), $Errors); 557 } 558 if (isset($OrderLine['poline'])){ 559 $Errors=VerifyPOLine($OrderLine['poline'], sizeof($Errors), $Errors); 560 } 561 $sql='UPDATE salesorderdetails SET '; 562 foreach ($OrderLine as $key => $value) { 563 if ($key == 'actualdispatchdate') { 564 $value = FormatDateWithTimeForSQL($value); 565 } 566 elseif ($key == 'itemdue') 567 $value = FormatDateForSQL($value); 568 $sql .= $key.'="'.$value.'", '; 569 } 570 //$sql = mb_substr($sql,0,-2).' WHERE orderno="'.$OrderLine['orderno'].'" and 571 // " orderlineno='.$OrderLine['orderlineno']; 572 $sql = mb_substr($sql,0,-2)." WHERE orderno='" . $OrderLine['orderno']."' AND stkcode='" . $OrderLine['stkcode']."'"; 573 //echo $sql; 574 //exit; 575 if (sizeof($Errors)==0) { 576 $result = api_DB_Query($sql); 577 echo DB_error_no(); 578 if (DB_error_no() != 0) { 579 $Errors[0] = DatabaseUpdateFailed; 580 } else { 581 $Errors[0]=0; 582 } 583 } 584 return $Errors; 585 } 586 587/* This function takes a Order Header ID and returns an associative array containing 588 the database record for that Order. If the Order Header ID doesn't exist 589 then it returns an $Errors array. 590*/ 591 function GetSalesOrderHeader($OrderNo, $user, $password) { 592 $Errors = array(); 593 $db = db($user, $password); 594 if (gettype($db)=='integer') { 595 $Errors[0]=NoAuthorisation; 596 return $Errors; 597 } 598 $Errors=VerifyOrderHeaderExists($OrderNo, sizeof($Errors), $Errors); 599 if (sizeof($Errors)!=0) { 600 return $Errors; 601 } 602 $sql="SELECT * FROM salesorders WHERE orderno='".$OrderNo."'"; 603 $result = api_DB_Query($sql); 604 if (sizeof($Errors)==0) { 605 return DB_fetch_array($result); 606 } else { 607 return $Errors; 608 } 609 } 610 611/* This function takes a Order Header ID and returns an associative array containing 612 the database record for that Order. If the Order Header ID doesn't exist 613 then it returns an $Errors array. 614*/ 615 function GetSalesOrderLine($OrderNo, $user, $password) { 616 617 $Errors = array(); 618 $db = db($user, $password); 619 if (gettype($db)=='integer') { 620 $Errors[0]=NoAuthorisation; 621 return $Errors; 622 } 623 $Errors=VerifyOrderHeaderExists($OrderNo, sizeof($Errors), $Errors); 624 if (sizeof($Errors)!=0) { 625 return $Errors; 626 } 627 $sql="SELECT * FROM salesorderdetails WHERE orderno='" . $OrderNo . "'"; 628 $result = api_DB_query($sql); 629 if (sizeof($Errors)==0) { 630 return DB_fetch_array($result); 631 } else { 632 return $Errors; 633 } 634 } 635 636 637 function InvoiceSalesOrder($OrderNo, $User, $Password) { 638 639 $Errors = array(); 640 $db = db($User, $Password); 641 if (gettype($db)=='integer') { 642 $Errors[]=NoAuthorisation; 643 return $Errors; 644 } 645 $Errors=VerifyOrderHeaderExists($OrderNo, sizeof($Errors), $Errors); 646 if (sizeof($Errors)!=0) { 647 return $Errors; 648 } 649 /*Does not deal with assembly items or serialise/lot track items - for use by POS */ 650 /*Get Company Defaults */ 651 $ReadCoyResult = api_DB_query("SELECT debtorsact, 652 freightact, 653 gllink_debtors, 654 gllink_stock 655 FROM companies 656 WHERE coycode=1"); 657 658 $CompanyRecord = DB_fetch_array($ReadCoyResult); 659 if (DB_error_no() != 0) { 660 $Errors[] = NoCompanyRecord; 661 } 662 663 $OrderHeaderSQL = "SELECT salesorders.debtorno, 664 debtorsmaster.name, 665 salesorders.branchcode, 666 salesorders.customerref, 667 salesorders.orddate, 668 salesorders.ordertype, 669 salesorders.shipvia, 670 custbranch.area, 671 custbranch.taxgroupid, 672 debtorsmaster.currcode, 673 currencies.rate, 674 salesorders.fromstkloc, 675 custbranch.salesman 676 FROM salesorders 677 INNER JOIN debtorsmaster 678 ON salesorders.debtorno = debtorsmaster.debtorno 679 INNER JOIN custbranch 680 ON salesorders.debtorno = custbranch.debtorno 681 AND salesorders.branchcode = custbranch.branchcode 682 INNER JOIN locations 683 ON locations.loccode=salesorders.fromstkloc 684 INNER JOIN currencies 685 ON debtorsmaster.currcode=currencies.currabrev 686 WHERE salesorders.orderno = '" . $OrderNo . "'"; 687 688 $OrderHeaderResult = api_DB_query($OrderHeaderSQL); 689 if (DB_error_no() != 0) { 690 $Errors[] = NoReadOrder; 691 } 692 693 $OrderHeader = DB_fetch_array($OrderHeaderResult); 694 695 $TaxProvResult = api_DB_query("SELECT taxprovinceid FROM locations WHERE loccode='" . $OrderHeader['fromstkloc'] ."'"); 696 if (DB_error_no() != 0) { 697 $Errors[] = NoTaxProvince; 698 } 699 $myrow = DB_fetch_row($TaxProvResult); 700 $DispTaxProvinceID = $myrow[0]; 701 702 $LineItemsSQL = "SELECT stkcode, 703 unitprice, 704 quantity, 705 discountpercent, 706 taxcatid, 707 mbflag, 708 materialcost+labourcost+overheadcost AS standardcost 709 FROM salesorderdetails INNER JOIN stockmaster 710 ON salesorderdetails.stkcode = stockmaster.stockid 711 WHERE orderno ='" . $OrderNo . "' 712 AND completed=0"; 713 714 $LineItemsResult = api_DB_query($LineItemsSQL); 715 if (DB_error_no() != 0 OR DB_num_rows($LineItemsResult)==0) { 716 $Errors[] = NoReadOrderLines; 717 return $Errors; 718 } 719 720 /*Start an SQL transaction */ 721 $result = DB_Txn_Begin(); 722 /*Now Get the next invoice number - function in SQL_CommonFunctions*/ 723 $InvoiceNo = GetNextTransNo(10); 724 $PeriodNo = GetCurrentPeriod(); 725 726 $TaxTotals =array(); 727 728 $TotalFXNetInvoice = 0; 729 $TotalFXTax = 0; 730 $LineCounter =0; 731 732 while ($OrderLineRow = DB_fetch_array($LineItemsResult)) { 733 734 $StandardCost = $OrderLineRow['standardcost']; 735 $LocalCurrencyPrice= ($OrderLineRow['unitprice'] *(1- floatval($OrderLineRow['discountpercent'])))/ $OrderHeader['rate']; 736 $LineNetAmount = $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] *(1- floatval($OrderLineRow['discountpercent'])); 737 738 /*Gets the Taxes and rates applicable to this line from the TaxGroup of the branch and TaxCategory of the item 739 and the taxprovince of the dispatch location */ 740 741 $SQL = "SELECT taxgrouptaxes.calculationorder, 742 taxauthorities.description, 743 taxgrouptaxes.taxauthid, 744 taxauthorities.taxglcode, 745 taxgrouptaxes.taxontax, 746 taxauthrates.taxrate 747 FROM taxauthrates INNER JOIN taxgrouptaxes ON 748 taxauthrates.taxauthority=taxgrouptaxes.taxauthid 749 INNER JOIN taxauthorities ON 750 taxauthrates.taxauthority=taxauthorities.taxid 751 WHERE taxgrouptaxes.taxgroupid='" . $OrderHeader['taxgroupid'] . "' 752 AND taxauthrates.dispatchtaxprovince='" . $DispTaxProvinceID . "' 753 AND taxauthrates.taxcatid = '" . $OrderLineRow['taxcatid'] . "' 754 ORDER BY taxgrouptaxes.calculationorder"; 755 756 $GetTaxRatesResult = api_DB_query($SQL); 757 758 if (DB_error_no() != 0) { 759 $Errors[] = TaxRatesFailed; 760 } 761 $LineTaxAmount = 0; 762 while ($myrow = DB_fetch_array($GetTaxRatesResult)){ 763 764 if (!isset($TaxTotals[$myrow['taxauthid']]['FXAmount'])) { 765 $TaxTotals[$myrow['taxauthid']]['FXAmount']=0; 766 } 767 $TaxAuthID=$myrow['taxauthid']; 768 $TaxTotals[$myrow['taxauthid']]['GLCode'] = $myrow['taxglcode']; 769 $TaxTotals[$myrow['taxauthid']]['TaxRate'] = $myrow['taxrate']; 770 $TaxTotals[$myrow['taxauthid']]['TaxAuthDescription'] = $myrow['description']; 771 772 if ($myrow['taxontax'] ==1){ 773 $TaxAuthAmount = ($LineNetAmount+$LineTaxAmount) * $myrow['taxrate']; 774 } else { 775 $TaxAuthAmount = $LineNetAmount * $myrow['taxrate']; 776 } 777 $TaxTotals[$myrow['taxauthid']]['FXAmount'] += $TaxAuthAmount; 778 779 /*Make an array of the taxes and amounts including GLcodes for later posting - need debtortransid 780 so can only post once the debtor trans is posted - can only post debtor trans when all tax is calculated */ 781 $LineTaxes[$LineCounter][$myrow['calculationorder']] = array('TaxCalculationOrder' =>$myrow['calculationorder'], 782 'TaxAuthID' =>$myrow['taxauthid'], 783 'TaxAuthDescription'=>$myrow['description'], 784 'TaxRate'=>$myrow['taxrate'], 785 'TaxOnTax'=>$myrow['taxontax'], 786 'TaxAuthAmount'=>$TaxAuthAmount); 787 $LineTaxAmount += $TaxAuthAmount; 788 789 }//end loop around Taxes 790 791 $TotalFXNetInvoice += $LineNetAmount; 792 $TotalFXTax += $LineTaxAmount; 793 794 /*Now update SalesOrderDetails for the quantity invoiced and the actual dispatch dates. */ 795 $SQL = "UPDATE salesorderdetails 796 SET qtyinvoiced = qtyinvoiced + " . $OrderLineRow['quantity'] . ", 797 actualdispatchdate = '" . $OrderHeader['orddate'] . "', 798 completed='1' 799 WHERE orderno = '" . $OrderNo . "' 800 AND stkcode = '" . $OrderLineRow['stkcode'] . "'"; 801 802 $Result = api_DB_query($SQL,'','',true); 803 804 805 if ($OrderLineRow['mbflag']=='B' OR $OrderLineRow['mbflag']=='M') { 806 $Assembly = False; 807 808 /* Need to get the current location quantity 809 will need it later for the stock movement */ 810 $SQL="SELECT locstock.quantity 811 FROM locstock 812 WHERE locstock.stockid='" . $OrderLineRow['stkcode'] . "' 813 AND loccode= '" . $OrderHeader['fromstkloc'] . "'"; 814 $Result = api_DB_query($SQL); 815 816 if (DB_num_rows($Result)==1){ 817 $LocQtyRow = DB_fetch_row($Result); 818 $QtyOnHandPrior = $LocQtyRow[0]; 819 } else { 820 /* There must be some error this should never happen */ 821 $QtyOnHandPrior = 0; 822 } 823 824 $SQL = "UPDATE locstock 825 SET quantity = locstock.quantity - " . $OrderLineRow['quantity'] . " 826 WHERE locstock.stockid = '" . $OrderLineRow['stkcode'] . "' 827 AND loccode = '" . $OrderHeader['fromstkloc'] . "'"; 828 $Result = api_DB_query($SQL,'','',true); 829 830 $SQL = "INSERT INTO stockmoves (stockid, 831 type, 832 transno, 833 loccode, 834 trandate, 835 debtorno, 836 branchcode, 837 price, 838 prd, 839 reference, 840 qty, 841 discountpercent, 842 standardcost, 843 newqoh) 844 VALUES ('" . $OrderLineRow['stkcode'] . "', 845 '10', 846 '" . $InvoiceNo . "', 847 '" . $OrderHeader['fromstkloc'] . "', 848 '" . $OrderHeader['orddate'] . "', 849 '" . $OrderHeader['debtorno'] . "', 850 '" . $OrderHeader['branchcode'] . "', 851 '" . $LocalCurrencyPrice . "', 852 '" . $PeriodNo . "', 853 '" . $OrderNo . "', 854 '" . -$OrderLineRow['quantity'] . "', 855 '" . $OrderLineRow['discountpercent'] . "', 856 '" . $StandardCost . "', 857 '" . ($QtyOnHandPrior - $OrderLineRow['quantity']) . "' )"; 858 859 $Result = api_DB_query($SQL,'','',true); 860 861 } else if ($OrderLineRow['mbflag']=='A'){ /* its an assembly */ 862 /*Need to get the BOM for this part and make 863 stock moves for the components then update the Location stock balances */ 864 $Assembly=True; 865 $StandardCost =0; /*To start with - accumulate the cost of the comoponents for use in journals later on */ 866 $SQL = "SELECT bom.component, 867 bom.quantity, 868 stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standard 869 FROM bom INNER JOIN stockmaster 870 ON bom.component=stockmaster.stockid 871 WHERE bom.parent='" . $OrderLineRow['stkcode'] . "' 872 AND bom.effectiveafter <= '" . date('Y-m-d') . "' 873 AND bom.effectiveto > '" . date('Y-m-d') . "'"; 874 875 $AssResult = api_DB_query($SQL); 876 877 while ($AssParts = DB_fetch_array($AssResult)){ 878 879 $StandardCost += ($AssParts['standard'] * $AssParts['quantity']) ; 880 /* Need to get the current location quantity 881 will need it later for the stock movement */ 882 $SQL="SELECT locstock.quantity 883 FROM locstock 884 WHERE locstock.stockid='" . $AssParts['component'] . "' 885 AND loccode= '" . $OrderHeader['fromstkloc'] . "'"; 886 887 $Result = api_DB_query($SQL); 888 if (DB_num_rows($Result)==1){ 889 $LocQtyRow = DB_fetch_row($Result); 890 $QtyOnHandPrior = $LocQtyRow[0]; 891 } else { 892 /*There must be some error this should never happen */ 893 $QtyOnHandPrior = 0; 894 } 895 if (empty($AssParts['standard'])) { 896 $AssParts['standard']=0; 897 } 898 $SQL = "INSERT INTO stockmoves (stockid, 899 type, 900 transno, 901 loccode, 902 trandate, 903 debtorno, 904 branchcode, 905 prd, 906 reference, 907 qty, 908 standardcost, 909 show_on_inv_crds, 910 newqoh) 911 VALUES ('" . $AssParts['component'] . "', 912 10, 913 '" . $InvoiceNo . "', 914 '" . $OrderHeader['fromstkloc'] . "', 915 '" . $DefaultDispatchDate . "', 916 '" . $OrderHeader['debtorno'] . "', 917 '" . $OrderHeader['branchcode'] . "', 918 '" . $PeriodNo . "', 919 '" . _('Assembly') . ': ' . $OrderLineRow['stkcode'] . ' ' . _('Order') . ': ' . $OrderNo . "', 920 '" . -$AssParts['quantity'] * $OrderLineRow['quantity'] . "', 921 '" . $AssParts['standard'] . "', 922 0, 923 '" . ($QtyOnHandPrior - $AssParts['quantity'] * $OrderLineRow['quantity']) . "' )"; 924 925 $Result = DB_query($SQL,'','',true); 926 927 $SQL = "UPDATE locstock 928 SET quantity = locstock.quantity - " . ($AssParts['quantity'] * $OrderLineRow['quantity']) . " 929 WHERE locstock.stockid = '" . $AssParts['component'] . "' 930 AND loccode = '" . $OrderHeader['fromlocstk'] . "'"; 931 932 $Result = DB_query($SQL,'','',true); 933 } /* end of assembly explosion and updates */ 934 } /* end of its an assembly */ 935 936 937 if ($OrderLineRow['mbflag']=='A' OR $OrderLineRow['mbflag']=='D'){ 938 /*it's a Dummy/Service item or an Assembly item - still need stock movement record 939 * but quantites on hand are always nil */ 940 $SQL = "INSERT INTO stockmoves (stockid, 941 type, 942 transno, 943 loccode, 944 trandate, 945 debtorno, 946 branchcode, 947 price, 948 prd, 949 reference, 950 qty, 951 discountpercent, 952 standardcost, 953 newqoh) 954 VALUES ('" . $OrderLineRow['stkcode'] . "', 955 '10', 956 '" . $InvoiceNo . "', 957 '" . $OrderHeader['fromstkloc'] . "', 958 '" . $OrderHeader['orddate'] . "', 959 '" . $OrderHeader['debtorno'] . "', 960 '" . $OrderHeader['branchcode'] . "', 961 '" . $LocalCurrencyPrice . "', 962 '" . $PeriodNo . "', 963 '" . $OrderNo . "', 964 '" . -$OrderLineRow['quantity'] . "', 965 '" . $OrderLineRow['discountpercent'] . "', 966 '" . $StandardCost . "', 967 '0' )"; 968 969 $Result = api_DB_query($SQL,'','',true); 970 } 971 /*Get the ID of the StockMove... */ 972 $StkMoveNo = DB_Last_Insert_ID('stockmoves','stkmoveno'); 973 /*Insert the taxes that applied to this line */ 974 foreach ($LineTaxes[$LineCounter] as $Tax) { 975 976 $SQL = "INSERT INTO stockmovestaxes (stkmoveno, 977 taxauthid, 978 taxrate, 979 taxcalculationorder, 980 taxontax) 981 VALUES ('" . $StkMoveNo . "', 982 '" . $Tax['TaxAuthID'] . "', 983 '" . $Tax['TaxRate'] . "', 984 '" . $Tax['TaxCalculationOrder'] . "', 985 '" . $Tax['TaxOnTax'] . "')"; 986 987 $Result = DB_query($SQL,'','',true); 988 } 989 990 /*Insert Sales Analysis records */ 991 992 $SQL="SELECT COUNT(*), 993 salesanalysis.stkcategory, 994 salesanalysis.area, 995 salesanalysis.salesperson, 996 salesanalysis.periodno, 997 salesanalysis.typeabbrev, 998 salesanalysis.cust, 999 salesanalysis.custbranch, 1000 salesanalysis.stockid 1001 FROM salesanalysis, 1002 custbranch, 1003 stockmaster 1004 WHERE salesanalysis.stkcategory=stockmaster.categoryid 1005 AND salesanalysis.stockid=stockmaster.stockid 1006 AND salesanalysis.cust=custbranch.debtorno 1007 AND salesanalysis.custbranch=custbranch.branchcode 1008 AND salesanalysis.area=custbranch.area 1009 AND salesanalysis.salesperson=custbranch.salesman 1010 AND salesanalysis.typeabbrev ='" . $OrderHeader['ordertype'] . "' 1011 AND salesanalysis.periodno='" . $PeriodNo . "' 1012 AND salesanalysis.cust " . LIKE . " '" . $OrderHeader['debtorno'] . "' 1013 AND salesanalysis.custbranch " . LIKE . " '" . $OrderHeader['branchcode'] . "' 1014 AND salesanalysis.stockid " . LIKE . " '" . $OrderLineRow['stkcode'] . "' 1015 AND salesanalysis.budgetoractual='1' 1016 GROUP BY salesanalysis.stockid, 1017 salesanalysis.stkcategory, 1018 salesanalysis.cust, 1019 salesanalysis.custbranch, 1020 salesanalysis.area, 1021 salesanalysis.periodno, 1022 salesanalysis.typeabbrev, 1023 salesanalysis.salesperson"; 1024 1025 $ErrMsg = _('The count of existing Sales analysis records could not run because'); 1026 $DbgMsg = _('SQL to count the no of sales analysis records'); 1027 $Result = DB_query($SQL,$ErrMsg,$DbgMsg,true); 1028 1029 $myrow = DB_fetch_row($Result); 1030 1031 if ($myrow[0]>0){ /*Update the existing record that already exists */ 1032 1033 $SQL = "UPDATE salesanalysis 1034 SET amt=amt+" . filter_number_format($OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate']) . ", 1035 qty=qty +" . $OrderLineRow['quantity'] . ", 1036 disc=disc+" . filter_number_format($OrderLineRow['discountpercent'] * $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate']) . " 1037 WHERE salesanalysis.area='" . $myrow[2] . "' 1038 AND salesanalysis.salesperson='" . $myrow[3] . "' 1039 AND typeabbrev ='" . $OrderHeader['ordertype'] . "' 1040 AND periodno = '" . $PeriodNo . "' 1041 AND cust " . LIKE . " '" . $OrderHeader['debtorno'] . "' 1042 AND custbranch " . LIKE . " '" . $OrderHeader['branchcode'] . "' 1043 AND stockid " . LIKE . " '" . $OrderLineRow['stkcode'] . "' 1044 AND salesanalysis.stkcategory ='" . $myrow[1] . "' 1045 AND budgetoractual='1'"; 1046 1047 } else { /* insert a new sales analysis record */ 1048 1049 $SQL = "INSERT INTO salesanalysis ( typeabbrev, 1050 periodno, 1051 amt, 1052 cost, 1053 cust, 1054 custbranch, 1055 qty, 1056 disc, 1057 stockid, 1058 area, 1059 budgetoractual, 1060 salesperson, 1061 stkcategory ) 1062 SELECT '" . $OrderHeader['ordertype']. "', 1063 '" . $PeriodNo . "', 1064 '" . $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate'] . "', 1065 0, 1066 '" . $OrderHeader['debtorno'] . "', 1067 '" . $OrderHeader['branchcode'] . "', 1068 '" . $OrderLineRow['quantity'] . "', 1069 '" . $OrderLineRow['discountpercent'] * $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate'] . "', 1070 '" . $OrderLineRow['stkcode'] . "', 1071 custbranch.area, 1072 1, 1073 custbranch.salesman, 1074 stockmaster.categoryid 1075 FROM stockmaster, custbranch 1076 WHERE stockmaster.stockid = '" . $OrderLineRow['stkcode'] . "' 1077 AND custbranch.debtorno = '" . $OrderHeader['debtorno'] . "' 1078 AND custbranch.branchcode='" . $OrderHeader['branchcode'] . "'"; 1079 1080 } 1081 1082 $Result = api_DB_query($SQL,'','',true); 1083 1084 if ($CompanyRecord['gllink_stock']==1 AND $StandardCost !=0){ 1085 1086/*first the cost of sales entry - GL accounts are retrieved using the function GetCOGSGLAccount from includes/GetSalesTransGLCodes.inc */ 1087 1088 $SQL = "INSERT INTO gltrans (type, 1089 typeno, 1090 trandate, 1091 periodno, 1092 account, 1093 narrative, 1094 amount) 1095 VALUES (10, 1096 '" . $InvoiceNo . "', 1097 '" . $OrderHeader['orddate'] . "', 1098 '" . $PeriodNo . "', 1099 '" . GetCOGSGLAccount($OrderHeader['area'], $OrderLineRow['stkcode'], $OrderHeader['ordertype']) . "', 1100 '" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $StandardCost . "', 1101 '" . ($StandardCost * $OrderLineRow['quantity']) . "')"; 1102 1103 $Result = api_DB_query($SQL,'','',true); 1104 1105/*now the stock entry - this is set to the cost act in the case of a fixed asset disposal */ 1106 $StockGLCode = GetStockGLCode($OrderLineRow['stkcode']); 1107 1108 $SQL = "INSERT INTO gltrans (type, 1109 typeno, 1110 trandate, 1111 periodno, 1112 account, 1113 narrative, 1114 amount) 1115 VALUES (10, 1116 '" . $InvoiceNo . "', 1117 '" . $OrderHeader['orddate'] . "', 1118 '" . $PeriodNo . "', 1119 '" . $StockGLCode['stockact'] . "', 1120 '" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $StandardCost . "', 1121 '" . (-$StandardCost * $OrderLineRow['quantity']) . "')"; 1122 1123 $Result = api_DB_query($SQL,'','',true); 1124 1125 } /* end of if GL and stock integrated and standard cost !=0 and not an asset */ 1126 1127 if ($CompanyRecord['gllink_debtors']==1 AND $OrderLineRow['unitprice'] !=0){ 1128 1129 //Post sales transaction to GL credit sales 1130 $SalesGLAccounts = GetSalesGLAccount($OrderHeader['area'], $OrderLineRow['stkcode'], $OrderHeader['ordertype']); 1131 1132 $SQL = "INSERT INTO gltrans (type, 1133 typeno, 1134 trandate, 1135 periodno, 1136 account, 1137 narrative, 1138 amount ) 1139 VALUES ('10', 1140 '" . $InvoiceNo . "', 1141 '" . $OrderHeader['orddate'] . "', 1142 '" . $PeriodNo . "', 1143 '" . $SalesGLAccounts['salesglcode'] . "', 1144 '" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $OrderLineRow['unitprice'] . "', 1145 '" . -$OrderLineRow['unitprice'] * $OrderLineRow['quantity']/$OrderHeader['rate'] . "' 1146 )"; 1147 $Result = api_DB_query($SQL,'','',true); 1148 1149 if ($OrderLineRow['discountpercent'] !=0){ 1150 1151 $SQL = "INSERT INTO gltrans (type, 1152 typeno, 1153 trandate, 1154 periodno, 1155 account, 1156 narrative, 1157 amount) 1158 VALUES (10, 1159 '" . $InvoiceNo . "', 1160 '" . $OrderHeader['orddate'] . "', 1161 '" . $PeriodNo . "', 1162 '" . $SalesGLAccounts['discountglcode'] . "', 1163 '" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " @ " . ($OrderLineRow['discountpercent'] * 100) . "%', 1164 '" . ($OrderLineRow['unitprice'] * $OrderLineRow['quantity'] * $OrderLineRow['discountpercent']/$OrderHeader['rate']) . "')"; 1165 1166 $Result = DB_query($SQL,'','',true); 1167 } /*end of if discount !=0 */ 1168 1169 } /*end of if sales integrated with gl */ 1170 1171 $LineCounter++; //needed for the array of taxes by line 1172 } /*end of OrderLine loop */ 1173 1174 $TotalInvLocalCurr = ($TotalFXNetInvoice + $TotalFXTax)/$OrderHeader['rate']; 1175 1176 if ($CompanyRecord['gllink_debtors']==1){ 1177 1178 /*Now post the tax to the GL at local currency equivalent */ 1179 if ($CompanyRecord['gllink_debtors']==1 AND $TaxAuthAmount !=0) { 1180 1181 /*Loop through the tax authorities array to post each total to the taxauth glcode */ 1182 foreach ($TaxTotals as $Tax){ 1183 $SQL = "INSERT INTO gltrans (type, 1184 typeno, 1185 trandate, 1186 periodno, 1187 account, 1188 narrative, 1189 amount ) 1190 VALUES (10, 1191 '" . $InvoiceNo . "', 1192 '" . $OrderHeader['orddate']. "', 1193 '" . $PeriodNo . "', 1194 '" . $Tax['GLCode'] . "', 1195 '" . $OrderHeader['debtorno'] . "-" . $Tax['TaxAuthDescription'] . "', 1196 '" . -$Tax['FXAmount']/$OrderHeader['rate'] . "' )"; 1197 1198 $Result = api_DB_query($SQL,'','',true); 1199 } 1200 } 1201 1202 /*Post debtors transaction to GL debit debtors, credit freight re-charged and credit sales */ 1203 if (($TotalInvLocalCurr) !=0) { 1204 $SQL = "INSERT INTO gltrans (type, 1205 typeno, 1206 trandate, 1207 periodno, 1208 account, 1209 narrative, 1210 amount) 1211 VALUES ('10', 1212 '" . $InvoiceNo . "', 1213 '" . $OrderHeader['orddate'] . "', 1214 '" . $PeriodNo . "', 1215 '" . $CompanyRecord['debtorsact'] . "', 1216 '" . $OrderHeader['debtorno'] . "', 1217 '" . $TotalInvLocalCurr . "')"; 1218 1219 $Result = api_DB_query($SQL,'','',true); 1220 } 1221 EnsureGLEntriesBalance(10,$InvoiceNo); 1222 1223 } /*end of if Sales and GL integrated */ 1224 1225 /*Update order header for invoice charged on */ 1226 $SQL = "UPDATE salesorders SET comments = CONCAT(comments,' Inv ','" . $InvoiceNo . "') WHERE orderno= '" . $OrderNo . "'"; 1227 $Result = api_DB_query($SQL,'','',true); 1228 1229 /*Now insert the DebtorTrans */ 1230 1231 $SQL = "INSERT INTO debtortrans (transno, 1232 type, 1233 debtorno, 1234 branchcode, 1235 trandate, 1236 inputdate, 1237 prd, 1238 reference, 1239 tpe, 1240 order_, 1241 ovamount, 1242 ovgst, 1243 rate, 1244 shipvia, 1245 salesperson) 1246 VALUES ( 1247 '". $InvoiceNo . "', 1248 10, 1249 '" . $OrderHeader['debtorno'] . "', 1250 '" . $OrderHeader['branchcode'] . "', 1251 '" . $OrderHeader['orddate'] . "', 1252 '" . date('Y-m-d H-i-s') . "', 1253 '" . $PeriodNo . "', 1254 '" . $OrderHeader['customerref'] . "', 1255 '" . $OrderHeader['ordertype'] . "', 1256 '" . $OrderNo . "', 1257 '" . $TotalFXNetInvoice . "', 1258 '" . $TotalFXTax . "', 1259 '" . $OrderHeader['rate'] . "', 1260 '" . $OrderHeader['shipvia'] . "', 1261 '" . $OrderHeader['salesman'] . "')"; 1262 1263 $Result = api_DB_query($SQL,'','',true); 1264 1265 $DebtorTransID = DB_Last_Insert_ID('debtortrans','id'); 1266 1267 /*for each Tax - need to insert into debtortranstaxes */ 1268 foreach ($TaxTotals AS $TaxAuthID => $Tax) { 1269 1270 $SQL = "INSERT INTO debtortranstaxes (debtortransid, 1271 taxauthid, 1272 taxamount) 1273 VALUES ('" . $DebtorTransID . "', 1274 '" . $TaxAuthID . "', 1275 '" . $Tax['FXAmount']/$OrderHeader['rate'] . "')"; 1276 $Result = api_DB_query($SQL,'','',true); 1277 } 1278 1279 if (sizeof($Errors)==0) { 1280 1281 $Result = DB_Txn_Commit(); 1282 $Errors[0]=0; 1283 $Errors[1]=$InvoiceNo; 1284 } else { 1285 $Result = DB_Txn_Rollback(); 1286 } 1287 return $Errors; 1288 } //end InvoiceSalesOrder function 1289 1290 1291 function GetCurrentPeriod () { 1292 1293 $TransDate = time(); //The current date to find the period for 1294 /* Find the unix timestamp of the last period end date in periods table */ 1295 $sql = "SELECT MAX(lastdate_in_period), MAX(periodno) from periods"; 1296 $result = DB_query($sql); 1297 $myrow=DB_fetch_row($result); 1298 1299 if (is_null($myrow[0])){ 1300 $InsertFirstPeriodResult = api_DB_query("INSERT INTO periods VALUES (0,'" . Date('Y-m-d',mktime(0,0,0,Date('m')+1,0,Date('Y'))) . "')"); 1301 $InsertFirstPeriodResult = api_DB_query("INSERT INTO periods VALUES (1,'" . Date('Y-m-d',mktime(0,0,0,Date('m')+2,0,Date('Y'))) . "')"); 1302 $LastPeriod=1; 1303 $LastPeriodEnd = mktime(0,0,0,Date('m')+2,0,Date('Y')); 1304 } else { 1305 $Date_Array = explode('-', $myrow[0]); 1306 $LastPeriodEnd = mktime(0,0,0,$Date_Array[1]+1,0,(int)$Date_Array[0]); 1307 $LastPeriod = $myrow[1]; 1308 } 1309 /* Find the unix timestamp of the first period end date in periods table */ 1310 $sql = "SELECT MIN(lastdate_in_period), MIN(periodno) from periods"; 1311 $result = api_DB_query($sql); 1312 $myrow=DB_fetch_row($result); 1313 $Date_Array = explode('-', $myrow[0]); 1314 $FirstPeriodEnd = mktime(0,0,0,$Date_Array[1],0,(int)$Date_Array[0]); 1315 $FirstPeriod = $myrow[1]; 1316 1317 /* If the period number doesn't exist */ 1318 if (!PeriodExists($TransDate)) { 1319 /* if the transaction is after the last period */ 1320 if ($TransDate > $LastPeriodEnd) { 1321 1322 $PeriodEnd = mktime(0,0,0,Date('m', $TransDate)+1, 0, Date('Y', $TransDate)); 1323 1324 while ($PeriodEnd >= $LastPeriodEnd) { 1325 if (Date('m', $LastPeriodEnd)<=13) { 1326 $LastPeriodEnd = mktime(0,0,0,Date('m', $LastPeriodEnd)+2, 0, Date('Y', $LastPeriodEnd)); 1327 } else { 1328 $LastPeriodEnd = mktime(0,0,0,2, 0, Date('Y', $LastPeriodEnd)+1); 1329 } 1330 $LastPeriod++; 1331 CreatePeriod($LastPeriod, $LastPeriodEnd); 1332 } 1333 } else { 1334 /* The transaction is before the first period */ 1335 $PeriodEnd = mktime(0,0,0,Date('m', $TransDate), 0, Date('Y', $TransDate)); 1336 $Period = $FirstPeriod - 1; 1337 while ($FirstPeriodEnd > $PeriodEnd) { 1338 CreatePeriod($Period, $FirstPeriodEnd); 1339 $Period--; 1340 if (Date('m', $FirstPeriodEnd)>0) { 1341 $FirstPeriodEnd = mktime(0,0,0,Date('m', $FirstPeriodEnd), 0, Date('Y', $FirstPeriodEnd)); 1342 } else { 1343 $FirstPeriodEnd = mktime(0,0,0,13, 0, Date('Y', $FirstPeriodEnd)); 1344 } 1345 } 1346 } 1347 } else if (!PeriodExists(mktime(0,0,0,Date('m',$TransDate)+1,Date('d',$TransDate),Date('Y',$TransDate)))) { 1348 /* Make sure the following months period exists */ 1349 $sql = "SELECT MAX(lastdate_in_period), MAX(periodno) from periods"; 1350 $result = DB_query($sql); 1351 $myrow=DB_fetch_row($result); 1352 $Date_Array = explode('-', $myrow[0]); 1353 $LastPeriodEnd = mktime(0,0,0,$Date_Array[1]+2,0,(int)$Date_Array[0]); 1354 $LastPeriod = $myrow[1]; 1355 CreatePeriod($LastPeriod+1, $LastPeriodEnd); 1356 } 1357 1358 /* Now return the period number of the transaction */ 1359 1360 $MonthAfterTransDate = Mktime(0,0,0,Date('m',$TransDate)+1,Date('d',$TransDate),Date('Y',$TransDate)); 1361 $GetPrdSQL = "SELECT periodno 1362 FROM periods 1363 WHERE lastdate_in_period < '" . Date('Y-m-d', $MonthAfterTransDate) . "' 1364 AND lastdate_in_period >= '" . Date('Y-m-d', $TransDate) . "'"; 1365 1366 $ErrMsg = _('An error occurred in retrieving the period number'); 1367 $GetPrdResult = DB_query($GetPrdSQL,$ErrMsg); 1368 $myrow = DB_fetch_row($GetPrdResult); 1369 1370 return $myrow[0]; 1371 } 1372 1373?>