1<?php
2
3/* $Revision: 1.9 $ */
4
5$PageSecurity =15;
6
7include ('includes/session.php');
8include ('includes/header.php');
9include('includes/SQL_CommonFunctions.inc'); //need for EDITransNo
10include('includes/htmlMimeMail.php'); // need for sending email attachments
11
12//Important: Default value for EDIsent in debtortrans should probably be 1 for non EDI customers
13//updated to 0 only for EDI enabled customers. As it stands run some sql to update all existing
14//transactions to EDISent = 1 for newly enabled EDI customers. If you don't do this and try to run
15//this code you will create a very large number of EDI invoices.
16
17/*Get the Customers who are enabled for EDI invoicing */
18$sql = 'SELECT debtorno,
19		edireference,
20		editransport,
21		ediaddress,
22		ediserveruser,
23		ediserverpwd,
24		daysbeforedue,
25		dayinfollowingmonth
26	FROM debtorsmaster INNER JOIN paymentterms ON debtorsmaster.paymentterms=paymentterms.termsindicator
27	WHERE ediinvoices=1';
28
29$EDIInvCusts = DB_query($sql);
30
31if (DB_num_rows($EDIInvCusts)==0){
32	exit;
33}
34
35while ($CustDetails = DB_fetch_array($EDIInvCusts)){
36
37	/*Figure out if there are any unset invoices or credits for the customer */
38
39	$sql = "SELECT debtortrans.id,
40			transno,
41			type,
42			order_,
43			trandate,
44			ovgst,
45			ovamount,
46			ovfreight,
47			ovdiscount,
48			debtortrans.branchcode,
49			custbranchcode,
50			invtext,
51			shipvia,
52			rate,
53			brname,
54			braddress1,
55			braddress2,
56			braddress3,
57			braddress4,
58			braddress5
59		FROM debtortrans INNER JOIN custbranch ON custbranch.debtorno = debtortrans.debtorno
60		AND custbranch.branchcode = debtortrans.branchcode
61		WHERE (type=10 or type=11)
62		AND edisent=0
63		AND debtortrans.debtorno='" . $CustDetails['debtorno'] . "'";
64
65	$ErrMsg = _('There was a problem retrieving the customer transactions because');
66	$TransHeaders = DB_query($sql,$ErrMsg);
67
68
69	if (DB_num_rows($TransHeaders)==0){
70		break; /*move on to the next EDI customer */
71	}
72
73	/*Setup the variable from the DebtorsMaster required for the message */
74	$CompanyEDIReference = '0' . strval($_SESSION['EDIReference']); //very annoying, but had to add leading 0
75	//because our GLN had leading 0 and GetConfig.php looks for numbers and text fields, saw GLN as number and skipped 0
76	$CustEDIReference = $CustDetails['edireference'];
77	$TaxAuthorityRef = $_SESSION['CompanyRecord']['gstno'];
78
79	while ($TransDetails = DB_fetch_array($TransHeaders)){
80
81/*Set up the variables that will be needed in construction of the EDI message */
82		if ($TransDetails['type']==10){ /* its an invoice */
83			$InvOrCrd = 388;
84		} else { /* its a credit note */
85			$InvOrCrd = 381;
86		}
87		$TransNo = $TransDetails['transno'];
88		/*Always an original in this script since only non-sent transactions being processed */
89		$OrigOrDup = 9;
90		//$TranDate = SQLDateToEDI($TransDetails['trandate']);
91		$TranDate = date('Ymd');  //probably should use the date edi was created not the date filed in our system
92		$TranDateTime = date('Ymd:hi');
93		$OrderNo = $TransDetails['order_'];
94		$CustBranchCode = $TransDetails['branchcode'];
95		$BranchName = $TransDetails['brname'];
96		$BranchStreet =$TransDetails['braddress1'];
97		$BranchSuburb = $TransDetails['braddress2'];
98		$BranchState = $TransDetails['braddress3'];
99		$BranchZip = $TransDetails['braddress4'];
100		$BranchCountry = $TransDetails['braddress5'];
101		$ExchRate = $TransDetails['rate'];
102		$TaxTotal = number_format($TransDetails['ovgst'],2, '.', '');
103		$ShipToFreight = number_format(round($TransDetails['ovfreight'],2),2, '.', '');
104		$SegCount = 1;
105
106
107		$DatePaymentDue = ConvertToEDIDate(CalcDueDate(ConvertSQLDate($TransDetails['trandate']),$CustDetails['dayinfollowingmonth'], $CustDetails['daysbeforedue']));
108
109		$TotalAmountExclTax = number_format(($TransDetails['ovamount']+ $TransDetails['ovfreight'] + $TransDetails['ovdiscount']),2, '.', '');
110		$TotalAmountInclTax = number_format(($TransDetails['ovamount']+ $TransDetails['ovfreight'] + $TransDetails['ovdiscount'] + $TransDetails['ovgst']),2, '.', '');
111
112		//**************Need to get delivery address as may be diff from branch address
113
114		$sql = "SELECT deliverto,
115				deladd1,
116				deladd2,
117				deladd3,
118				deladd4,
119				deladd5,
120				deladd6,
121				salesorders.customerref
122				FROM debtortrans INNER JOIN salesorders ON debtortrans.order_ = salesorders.orderno
123				WHERE order_ = '" . $OrderNo . "'";
124
125				$ErrMsg = _('There was a problem retrieving the ship to details because');
126				$ShipToLines = DB_query($sql,$ErrMsg);
127
128				While ($ShipTo = DB_fetch_array($ShipToLines)){
129					$ShipToName = $ShipTo[0];
130					$ShipToStreet = $ShipTo[1];
131					$ShipToSuburb = $ShipTo[2];
132					$ShipToState = $ShipTo[3];
133					$ShipToZip = $ShipTo[4];
134					$ShipToCountry = $ShipTo[5];
135					$CustOrderNo = $ShipTo[7];
136
137				}
138
139		//**************Need to get delivery address as may be diff from branch address
140
141		//**************Reece needs NAD ST in every invoice, sometimes freeform text, so no real code
142
143		if($ShipToName === $BranchName){
144			$ShipToCode = $CustBranchCode;
145		} Else {
146			$ShipToCode = $ShipToName;
147		}
148
149		//**************Reece needs NAD ST in every invoice, sometimes freeform text, so no real code
150
151		//**************Taxrate, need to find
152
153		$sql = "SELECT 	stockmovestaxes.taxrate
154	                        FROM stockmoves,
155							stockmovestaxes
156	                        WHERE stockmoves.stkmoveno = stockmovestaxes.stkmoveno
157	                        AND stockmoves.transno=" . $TransNo . "
158	                        AND stockmoves.show_on_inv_crds=1
159	                        LIMIT 0,1";
160
161		                $ResultTax = DB_query($sql);
162
163		                $TaxRate = 100 * (mysql_result($ResultTax, 0));
164
165                //**************Taxrate, need to find
166
167		//**************Check to see if freight was added, probably specific to Reece and some other OZ hardware stores
168
169		        if($ShipToFreight > 0){
170		                $FreightTax = number_format(round(($ShipToFreight * $TaxRate/100),2),2, '.', '');
171		                $Freight_YN = "ALC+C" . "'" . "MOA+64:" .$ShipToFreight. "'" . "TAX+7+GST+++:::" .$TaxRate. "'". "MOA+124:" .$FreightTax."'";
172		                $SegCount = $SegCount + 3;
173						} else {
174		                $Freight_YN = "";
175		                }
176
177		//**************Check to see if freight was added could do this in Substitution, skip if 0 freight
178
179
180
181		//Get the message lines, replace variable names with data, write the output to a file one line at a time
182
183		$sql = "SELECT section, linetext FROM edimessageformat WHERE partnercode='" . $CustDetails['debtorno'] . "' AND messagetype='INVOIC' ORDER BY sequenceno";
184		$ErrMsg =  _('An error occurred in getting the EDI format template for') . ' ' . $CustDetails['debtorno'] . ' ' . _('because');
185		$MessageLinesResult = DB_query($sql,$ErrMsg);
186
187
188		if (DB_num_rows($MessageLinesResult)>0){
189
190
191			$DetailLines = array();
192			$ArrayCounter =0;
193			While ($MessageLine = DB_fetch_array($MessageLinesResult)){
194				if ($MessageLine['section']=='Detail'){
195					$DetailLines[$ArrayCounter]=$MessageLine['linetext'];
196					$ArrayCounter++;
197				}
198			}
199			DB_data_seek($MessageLinesResult,0);
200
201			$EDITransNo = GetNextTransNo(99);
202			$fp = fopen('EDI_INV_' . $TransNo . '.txt', 'w');
203
204			while ($LineDetails = DB_fetch_array($MessageLinesResult)){
205
206				if ($LineDetails['section']=='Heading'){
207					$MsgLineText = $LineDetails['linetext'];
208					include ('includes/EDIVariableSubstitution.inc');
209					$LastLine ='Heading';
210					}
211
212				if ($LineDetails['section']=='Detail' AND $LastLine=='Heading') {
213					/*This must be the detail section
214					need to get the line details for the invoice or credit note
215					for creating the detail lines */
216
217
218					if ($TransDetails['type']==10){ /*its an invoice */
219						 $sql = "SELECT stockmoves.stockid,
220						 		stockmaster.description,
221								-stockmoves.qty as quantity,
222								stockmoves.discountpercent,
223								((1 - stockmoves.discountpercent) * stockmoves.price * " . $ExchRate . " * -stockmoves.qty) AS fxnet,
224								(stockmoves.price * " . $ExchRate . ") AS fxprice,
225								stockmaster.units
226							FROM stockmoves,
227								stockmaster
228							WHERE stockmoves.stockid = stockmaster.stockid
229							AND stockmoves.type=10
230							AND stockmoves.transno=" . $TransNo . "
231							AND stockmoves.show_on_inv_crds=1";
232
233					} else {
234					/* credit note */
235			 			$sql = "SELECT stockmoves.stockid,
236								stockmaster.description,
237								stockmoves.qty as quantity,
238								stockmoves.discountpercent,
239								((1 - stockmoves.discountpercent) * stockmoves.price * " . $ExchRate . " * stockmoves.qty) as fxnet,
240								(stockmoves.price * " . $ExchRate . ") AS fxprice,
241								stockmaster.units
242							FROM stockmoves,
243								stockmaster
244							WHERE stockmoves.stockid = stockmaster.stockid
245							AND stockmoves.type=11 and stockmoves.transno=" . $TransNo . "
246							AND stockmoves.show_on_inv_crds=1";
247					}
248					$TransLinesResult = DB_query($sql);
249
250					$LineNumber = 0;
251					while ($TransLines = DB_fetch_array($TransLinesResult)){
252						/*now set up the variable values */
253
254						$LineNumber++;
255						$StockID = $TransLines['stockid'];
256						$sql = "SELECT partnerstockid
257								FROM ediitemmapping
258							WHERE supporcust='CUST'
259							AND partnercode ='" . $CustDetails['debtorno'] . "'
260							AND stockid='" . $TransLines['stockid'] . "'";
261
262						$CustStkResult = DB_query($sql);
263						if (DB_num_rows($CustStkResult)==1){
264							$CustStkIDRow = DB_fetch_row($CustStkResult);
265							$CustStockID = $CustStkIDRow[0];
266						} else {
267							$CustStockID = 'Not_Known';
268						}
269						$ItemDescription = $TransLines['description'];
270						$QtyInvoiced = $TransLines['quantity'];
271						$LineTotalExclTax = number_format(round($TransLines['fxnet'],3),2, '.', '');
272						$UnitPriceExclTax = number_format(round( $TransLines['fxnet'] / $TransLines['quantity'], 3),2, '.', '');
273						$LineTaxAmount = number_format(round($TaxRate/100 * $TransLines['fxnet'],3),2, '.', '');
274						$LineTotalInclTax = number_format(round((1+$TaxRate/100) * $LineTotalExclTax,3),2, '.', '');
275						$UnitPriceInclTax = number_format(round((1+$TaxRate/100) * $UnitPriceExclTax,2),2, '.', '');
276
277						/*now work through the detail line segments */
278						foreach ($DetailLines as $DetailLineText) {
279							$MsgLineText = $DetailLineText;
280							include ('includes/EDIVariableSubstitution.inc');
281						}
282
283
284					}
285
286
287					$LastLine ='Detail';
288					$NoLines = $LineNumber;
289					}
290
291					if($LineDetails['section']=='Summary' AND $LastLine=='Detail'){
292					$MsgLineText = $LineDetails['linetext'];
293					include ('includes/EDIVariableSubstitution.inc');
294					}
295			} /*end while there are message lines to parse and substitute vbles for */
296			fclose($fp); /*close the file at the end of each transaction */
297			DB_query("UPDATE debtortrans SET EDISent=1 WHERE ID=" . $TransDetails['id']);
298			/*Now send the file using the customer transport */
299			if ($CustDetails['editransport']=='email'){
300
301				$mail = new htmlMimeMail();
302				$attachment = $mail->getFile( "EDI_INV_" . $TransNo .".txt");
303				$mail->SetSubject('EDI Invoice/Credit Note ' . $TransNo);
304				$mail->addAttachment($attachment, 'EDI_INV_' . $TransNo . '.txt', 'application/txt');
305				if($_SESSION['SmtpSetting']==0){
306					$mail->setFrom($_SESSION['CompanyRecord']['coyname'] . '<' . $_SESSION['CompanyRecord']['email'] . '>');
307					$MessageSent = $mail->send(array($CustDetails['ediaddress']));
308				}else{
309					$MessageSent = SendmailBySmtp($mail,array($CustDetails['ediaddress']));
310				}
311
312
313				if ($MessageSent==True){
314					echo '<BR><BR>';
315					prnMsg(_('EDI Message') . ' ' . $TransNo . ' ' . _('was sucessfully emailed'),'success');
316				} else {
317					echo '<BR><BR>';
318					prnMsg(_('EDI Message') . ' ' . $TransNo . _('could not be emailed to') . ' ' . $CustDetails['ediaddress'],'error');
319				}
320			} else { /*it must be ftp transport */
321
322						 //Godaddy limitations make it impossible to sftp using ssl or curl, so save to EDI_Sent file and 'rsynch' back to sftp server
323
324              			/* set up basic connection
325              			$conn_id = ftp_connect($CustDetails['ediaddress']); // login with username and password
326              			$login_result = ftp_login($conn_id, $CustDetails['ediserveruser'], $CustDetails['ediserverpwd']); // check connection
327              			if ((!$conn_id) || (!$login_result)) {
328                  			prnMsg( _('Ftp connection has failed'). '<BR>' . _('Attempted to connect to') . ' ' . $CustDetails['ediaddress'] . ' ' ._('for user') . ' ' . $CustDetails['ediserveruser'],'error');
329                  			include('includes/footer.php');
330					exit;
331              			}
332              			$MessageSent = ftp_put($conn_id, $_SESSION['EDI_MsgPending'] . '/EDI_INV_' . $EDITransNo, 'EDI_INV_' . $EDITransNo, FTP_ASCII); // check upload status
333              			if (!$MessageSent) {
334                   			echo '<BR><BR>';
335					prnMsg(_('EDI Message') . ' ' . $EDITransNo . ' ' . _('could not be sent via ftp to') .' ' . $CustDetails['ediaddress'],'error');
336                   		} else {
337                   			echo '<BR><BR>';
338					prnMsg( _('Successfully uploaded EDI_INV_') . $EDITransNo . ' ' . _('via ftp to') . ' ' . $CustDetails['ediaddress'],'success');
339              			} // close the FTP stream
340              			ftp_quit($conn_id);
341              			*/
342			}
343
344
345			if ($MessageSent==True){ /*the email was sent sucessfully */
346				/* move the sent file to sent directory */
347				$source = 'EDI_INV_' . $TransNo . '.txt';
348                                $destination = 'EDI_Sent/EDI_INV_' . $TransNo . '.txt';
349                                rename($source, $destination);
350
351			}
352
353		} else {
354
355			prnMsg( _('Cannot create EDI message since there is no EDI INVOIC message template set up for') . ' ' . $CustDetails['debtorno'],'error');
356		} /*End if there is a message template defined for the customer invoic*/
357
358
359	} /* loop around all the customer transactions to be sent */
360
361} /*loop around all the customers enabled for EDI Invoices */
362
363include ('includes/footer.php');
364?>
365