Care2007/modules/weberp/EDISendInvoices.php

   1 <?php
   2
   3 /* $Revision: 1.9 $ */
   4
   5 $PageSecurity =15;
   6
   7 include ('includes/session.inc');
   8 include ('includes/header.inc');
   9 include('includes/SQL_CommonFunctions.inc'); //need for EDITransNo
  10 include('includes/htmlMimeMail.php'); // need for sending email attachments
  11
  12 /*Get the Customers who are enabled for EDI invoicing */
  13 $sql = 'SELECT debtorno,
  14                 edireference,
  15                 editransport,
  16                 ediaddress,
  17                 ediserveruser,
  18                 ediserverpwd,
  19                 daysbeforedue,
  20                 dayinfollowingmonth
  21         FROM debtorsmaster INNER JOIN paymentterms ON debtorsmaster.paymentterms=paymentterms.termsindicator
  22         WHERE ediinvoices=1';
  23
  24 $EDIInvCusts = DB_query($sql,$db);
  25
  26 if (DB_num_rows($EDIInvCusts)==0){
  27         exit;
  28 }
  29
  30 while ($CustDetails = DB_fetch_array($EDIInvCusts)){
  31
  32         /*Figure out if there are any unset invoices or credits for the customer */
  33
  34         $sql = "SELECT debtortrans.id,
  35                         transno,
  36                         type,
  37                         order_,
  38                         trandate,
  39                         ovgst,
  40                         ovamount,
  41                         ovfreight,
  42                         ovdiscount,
  43                         debtortrans.branchcode,
  44                         custbranchcode,
  45                         invtext,
  46                         shipvia,
  47                         rate,
  48                         brname,
  49                         braddress1,
  50                         braddress2,
  51                         braddress3,
  52                         braddress4
  53                 FROM debtortrans INNER JOIN custbranch ON custbranch.debtorno = debtortrans.debtorno
  54                 AND custbranch.branchcode = debtortrans.branchcode
  55                 WHERE (type=10 or type=11)
  56                 AND edisent=0
  57                 AND debtortrans.debtorno='" . $CustDetails['debtorno'] . "'";
  58
  59         $ErrMsg = _('There was a problem retrieving the customer transactions because');
  60         $TransHeaders = DB_query($sql,$db,$ErrMsg);
  61
  62
  63         if (DB_num_rows($TransHeaders)==0){
  64                 break; /*move on to the next EDI customer */
  65         }
  66
  67         /*Setup the variable from the DebtorsMaster required for the message */
  68         $CompanyEDIReference = $_SESSION['EDIReference'];
  69         $CustEDIReference = $CustDetails['edireference'];
  70         $TaxAuthorityRef = $_SESSION['CompanyRecord']['gstno'];
  71
  72         while ($TransDetails = DB_fetch_array($TransHeaders)){
  73
  74 /*Set up the variables that will be needed in construction of the EDI message */
  75                 if ($TransDetails['type']==10){ /* its an invoice */
  76                         $InvOrCrd = 388;
  77                 } else { /* its a credit note */
  78                         $InvOrCrd = 381;
  79                 }
  80                 $TransNo = $TransDetails['transno'];
  81                 /*Always an original in this script since only non-sent transactions being processed */
  82                 $OrigOrDup = 9;
  83                 $TranDate = SQLDateToEDI($TransDetails['trandate']);
  84                 $OrderNo = $TransDetails['order_'];
  85                 $CustBranchCode = $TransDetails['custbranchcode'];
  86                 $BranchName = $TransDetails['brname'];
  87                 $BranchStreet =$TransDetails['braddress1'];
  88                 $BranchCity = $TransDetails['braddress2'];
  89                 $BranchState = $TransDetails['braddress3'];
  90                 $ExchRate = $TransDetails['rate'];
  91                 $TaxTotal = $TransDetails['ovgst'];
  92
  93                 $DatePaymentDue = ConvertToEDIDate(CalcDueDate(ConvertSQLDate($TransDetails['trandate']),$CustDetails['dayinfollowingmonth'], $CustDetails['daysbeforedue']));
  94
  95                 $TotalAmountExclTax = $TransDetails['ovamount']+ $TransDetails['ovfreight'] + $TransDetails['ovdiscount'];
  96                 $TotalAmountInclTax = $TransDetails['ovamount']+ $TransDetails['ovfreight'] + $TransDetails['ovdiscount'] + $TransDetails['ovgst'];
  97
  98                 /* NOW ... Get the message lines
  99                         then replace variable names with data
 100                         write the output to a file one line at a time */
 101
 102                 $sql = "SELECT section, linetext FROM edimessageformat WHERE partnercode='" . $CustDetails['debtorno'] . "' AND messagetype='INVOIC' ORDER BY sequenceno";
 103                 $ErrMsg =  _('An error occurred in getting the EDI format template for') . ' ' . $CustDetails['debtorno'] . ' ' . _('because');
 104                 $MessageLinesResult = DB_query($sql, $db,$ErrMsg);
 105
 106
 107                 if (DB_num_rows($MessageLinesResult)>0){
 108
 109
 110                         $DetailLines = array();
 111                         $ArrayCounter =0;
 112                         While ($MessageLine = DB_fetch_array($MessageLinesResult)){
 113                                 if ($MessageLine['section']=='Detail'){
 114                                         $DetailLines[$ArrayCounter]=$MessageLine['linetext'];
 115                                         $ArrayCounter++;
 116                                 }
 117                         }
 118                         DB_data_seek($MessageLinesResult,0);
 119
 120                         $EDITransNo = GetNextTransNo(99,$db);
 121                         $fp = fopen( $_SESSION['EDI_MsgPending'] . '/EDI_INV_' . $EDITransNo , 'w');
 122
 123                         while ($LineDetails = DB_fetch_array($MessageLinesResult)){
 124
 125                                 if ($LineDetails['section']=='Heading'){
 126                                         $MsgLineText = $LineDetails['linetext'];
 127                                         include ('includes/EDIVariableSubstitution.inc');
 128                                         $LastLine ='Heading';
 129                                 } elseif ($LineDetails['section']=='Summary' AND $LastLine=='Heading') {
 130                                         /*This must be the detail section
 131                                         need to get the line details for the invoice or credit note
 132                                         for creating the detail lines */
 133
 134                                         if ($TransDetail['type']==10){ /*its an invoice */
 135                                                  $sql = "SELECT stockmoves.stockid,
 136                                                                 stockmaster.description,
 137                                                                 -stockmoves.qty as quantity,
 138                                                                 stockmoves.discountpercent,
 139                                                                 ((1 - stockmoves.discountpercent) * stockmoves.price * " . $ExchRate . "* -stockmoves.qty) AS fxnet,
 140                                                                 (stockmoves.price * " . $ExchRate . ") AS fxprice,
 141                                                                 stockmoves.taxrate,
 142                                                                 stockmaster.units
 143                                                         FROM stockmoves,
 144                                                                 stockmaster
 145                                                         WHERE stockmoves.stockid = stockmaster.stockid
 146                                                         AND stockmoves.type=10
 147                                                         AND stockmoves.transno=" . $TransNo . "
 148                                                         AND stockmoves.show_on_inv_crds=1";
 149                                         } else {
 150                                         /* credit note */
 151                                                 $sql = "SELECT stockmoves.stockid,
 152                                                                 stockmaster.description,
 153                                                                 stockmoves.qty as quantity,
 154                                                                 stockmoves.discountpercent,
 155                                                                 ((1 - stockmoves.discountpercent) * stockmoves.price * " . $ExchRate . " * stockmoves.qty) as fxnet,
 156                                                                 (stockmoves.price * " . $ExchRate . ") AS fxprice,
 157                                                                 stockmoves.taxrate,
 158                                                                 stockmaster.units
 159                                                         FROM stockmoves,
 160                                                                 stockmaster
 161                                                         WHERE stockmoves.stockid = stockmaster.stockid
 162                                                         AND stockmoves.type=11 and stockmoves.transno=" . $TransNo . "
 163                                                         AND stockmoves.show_on_inv_crds=1";
 164                                         }
 165                                         $TransLinesResult = DB_query($sql,$db);
 166
 167                                         $LineNumber = 0;
 168                                         while ($TransLines = DB_fetch_array($TransLinesResult)){
 169                                                 /*now set up the variable values */
 170
 171                                                 $LineNumber++;
 172                                                 $StockID = $TransLines['StockID'];
 173                                                 $sql = "SELECT partnerstockid
 174                                                                 FROM ediitemmapping
 175                                                         WHERE supporcust='CUST'
 176                                                         AND partnercode ='" . $CustDetails['debtorno'] . "'
 177                                                         AND stockid='" . $TransLines['stockid'] . "'";
 178
 179                                                 $CustStkResult = DB_query($sql,$db);
 180                                                 if (DB_num_rows($CustStkResult)==1){
 181                                                         $CustStkIDRow = DB_fetch_row($CustStkResult);
 182                                                         $CustStockID = $CustStkIDRow[0];
 183                                                 } else {
 184                                                         $CustStockID = 'Not_Known';
 185                                                 }
 186                                                 $ItemDescription = $TransLines['description'];
 187                                                 $QtyInvoiced = $TransLines['quantity'];
 188                                                 $LineTotalExclTax = round($TransLines['fxnet'],3);
 189                                                 $UnitPrice = round( $TransLines['fxnet'] / $TransLines['quantity'], 3);
 190                                                 $LineTaxAmount = round($TransLines['taxrate'] * $TransLines['fxnet'],3);
 191
 192                                                 /*now work through the detail line segments */
 193                                                 foreach ($DetailLines as $DetailLineText) {
 194                                                         $MsgLineText = $DetailLineText;
 195                                                         include ('includes/EDIVariableSubstitution.inc');
 196                                                 }
 197
 198                                         }
 199                                         /*to make sure dont do the detail section again */
 200                                         $LastLine ='Summary';
 201                                         $NoLines = $LineNumber;
 202                                 } elseif ($LineDetails['section']=='Summary'){
 203                                         $MsgLineText = $LineDetails['linetext'];
 204                                         include ('includes/EDIVariableSubstitution.inc');
 205                                 }
 206                         } /*end while there are message lines to parse and substitute vbles for */
 207                         fclose($fp); /*close the file at the end of each transaction */
 208                         //DB_query("UPDATE DebtorTrans SET EDISent=1 WHERE ID=" . $TransDetails['ID'],$db);
 209                         /*Now send the file using the customer transport */
 210                         if ($CustDetails['editransport']=='email'){
 211
 212                                 $mail = new htmlMimeMail();
 213                                 $attachment = $mail->getFile( $_SESSION['EDI_MsgPending'] . "/EDI_INV_" . $EDITransNo);
 214                                 $mail->SetSubject('EDI Invoice/Credit Note ' . $EDITransNo);
 215                                 $mail->addAttachment($attachment, 'EDI_INV_' . $EDITransNo, 'application/txt');
 216                                 $mail->setFrom($_SESSION['CompanyRecord']['coyname'] . '<' . $_SESSION['CompanyRecord']['email'] . '>');
 217                                 $MessageSent = $mail->send(array($CustDetails['ediaddress']));
 218
 219                                 if ($MessageSent==True){
 220                                         echo '<BR><BR>';
 221                                         prnMsg(_('EDI Message') . ' ' . $EDITransNo . ' ' . _('was sucessfully emailed'),'success');
 222                                 } else {
 223                                         echo '<BR><BR>';
 224                                         prnMsg(_('EDI Message') . ' ' . $EDITransNo . _('could not be emailed to') . ' ' . $CustDetails['ediaddress'],'error');
 225                                 }
 226                         } else { /*it must be ftp transport */
 227
 228                                 // set up basic connection
 229                                 $conn_id = ftp_connect($CustDetails['ediaddress']); // login with username and password
 230                                 $login_result = ftp_login($conn_id, $CustDetails['ediserveruser'], $CustDetails['ediserverpwd']); // check connection
 231                                 if ((!$conn_id) || (!$login_result)) {
 232                                         prnMsg( _('Ftp connection has failed'). '<BR>' . _('Attempted to connect to') . ' ' . $CustDetails['ediaddress'] . ' ' ._('for user') . ' ' . $CustDetails['ediserveruser'],'error');
 233                                         include('includes/footer.inc');
 234                                         exit;
 235                                 }
 236                                 $MessageSent = ftp_put($conn_id, $_SESSION['EDI_MsgPending'] . '/EDI_INV_' . $EDITransNo, 'EDI_INV_' . $EDITransNo, FTP_ASCII); // check upload status
 237                                 if (!$MessageSent) {
 238                                         echo '<BR><BR>';
 239                                         prnMsg(_('EDI Message') . ' ' . $EDITransNo . ' ' . _('could not be sent via ftp to') .' ' . $CustDetails['ediaddress'],'error');
 240                                 } else {
 241                                         echo '<BR><BR>';
 242                                         prnMsg( _('Successfully uploaded EDI_INV_') . $EDITransNo . ' ' . _('via ftp to') . ' ' . $CustDetails['ediaddress'],'success');
 243                                 } // close the FTP stream
 244                                 ftp_quit($conn_id);
 245                         }
 246
 247
 248                         if ($MessageSent==True){ /*the email was sent sucessfully */
 249                                 /* move the sent file to sent directory */
 250                                 copy ($_SESSION['EDI_MsgPending'] . '/EDI_INV_' . $EDITransNo, $_SESSION['EDI_MsgSent'] . '/EDI_INV_' . $EDITransNo);
 251                                 unlink($_SESSION['EDI_MsgPending'] . '/EDI_INV_' . $EDITransNo);
 252                         }
 253
 254                 } else {
 255
 256                         prnMsg( _('Cannot create EDI message since there is no EDI INVOIC message template set up for') . ' ' . $CustDetails['debtorno'],'error');
 257                 } /*End if there is a message template defined for the customer invoic*/
 258         } /* loop around all the customer transactions to be sent */
 259
 260 } /*loop around all the customers enabled for EDI Invoices */
 261
 262 include ('includes/footer.inc');
 263 ?>