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?>