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