Menu
▾
▴
[Web-erp-svn] SF.net SVN: web-erp:[5294] trunk
|
From: <dai...@us...> - 2012-04-29 03:46:05
|
Revision: 5294
http://web-erp.svn.sourceforge.net/web-erp/?rev=5294&view=rev
Author: daintree
Date: 2012-04-29 03:45:59 +0000 (Sun, 29 Apr 2012)
Log Message:
-----------
David Shorts EDI work
Modified Paths:
--------------
trunk/Labels.php
trunk/PDFPrintLabel.php
trunk/doc/Change.log
trunk/includes/PDFStarter.php
Added Paths:
-----------
trunk/EDISendInvoices_Reece.php
Added: trunk/EDISendInvoices_Reece.php
===================================================================
--- trunk/EDISendInvoices_Reece.php (rev 0)
+++ trunk/EDISendInvoices_Reece.php 2012-04-29 03:45:59 UTC (rev 5294)
@@ -0,0 +1,359 @@
+<?php
+
+/* $Revision: 1.9 $ */
+
+$PageSecurity =15;
+
+include ('includes/session.inc');
+include ('includes/header.inc');
+include('includes/SQL_CommonFunctions.inc'); //need for EDITransNo
+include('includes/htmlMimeMail.php'); // need for sending email attachments
+
+//Important: Default value for EDIsent in debtortrans should probably be 1 for non EDI customers
+//updated to 0 only for EDI enabled customers. As it stands run some sql to update all existing
+//transactions to EDISent = 1 for newly enabled EDI customers. If you don't do this and try to run
+//this code you will create a very large number of EDI invoices.
+
+/*Get the Customers who are enabled for EDI invoicing */
+$sql = 'SELECT debtorno,
+ edireference,
+ editransport,
+ ediaddress,
+ ediserveruser,
+ ediserverpwd,
+ daysbeforedue,
+ dayinfollowingmonth
+ FROM debtorsmaster INNER JOIN paymentterms ON debtorsmaster.paymentterms=paymentterms.termsindicator
+ WHERE ediinvoices=1';
+
+$EDIInvCusts = DB_query($sql,$db);
+
+if (DB_num_rows($EDIInvCusts)==0){
+ exit;
+}
+
+while ($CustDetails = DB_fetch_array($EDIInvCusts)){
+
+ /*Figure out if there are any unset invoices or credits for the customer */
+
+ $sql = "SELECT debtortrans.id,
+ transno,
+ type,
+ order_,
+ trandate,
+ ovgst,
+ ovamount,
+ ovfreight,
+ ovdiscount,
+ debtortrans.branchcode,
+ custbranchcode,
+ invtext,
+ shipvia,
+ rate,
+ brname,
+ braddress1,
+ braddress2,
+ braddress3,
+ braddress4,
+ braddress5
+ FROM debtortrans INNER JOIN custbranch ON custbranch.debtorno = debtortrans.debtorno
+ AND custbranch.branchcode = debtortrans.branchcode
+ WHERE (type=10 or type=11)
+ AND edisent=0
+ AND debtortrans.debtorno='" . $CustDetails['debtorno'] . "'";
+
+ $ErrMsg = _('There was a problem retrieving the customer transactions because');
+ $TransHeaders = DB_query($sql,$db,$ErrMsg);
+
+
+ if (DB_num_rows($TransHeaders)==0){
+ break; /*move on to the next EDI customer */
+ }
+
+ /*Setup the variable from the DebtorsMaster required for the message */
+ $CompanyEDIReference = '0' . strval($_SESSION['EDIReference']); //very annoying, but had to add leading 0
+ //because our GLN had leading 0 and GetConfig.php looks for numbers and text fields, saw GLN as number and skipped 0
+ $CustEDIReference = $CustDetails['edireference'];
+ $TaxAuthorityRef = $_SESSION['CompanyRecord']['gstno'];
+
+ while ($TransDetails = DB_fetch_array($TransHeaders)){
+
+/*Set up the variables that will be needed in construction of the EDI message */
+ if ($TransDetails['type']==10){ /* its an invoice */
+ $InvOrCrd = 388;
+ } else { /* its a credit note */
+ $InvOrCrd = 381;
+ }
+ $TransNo = $TransDetails['transno'];
+ /*Always an original in this script since only non-sent transactions being processed */
+ $OrigOrDup = 9;
+ //$TranDate = SQLDateToEDI($TransDetails['trandate']);
+ $TranDate = date('Ymd'); //probably should use the date edi was created not the date filed in our system
+ $TranDateTime = date('Ymd:hi');
+ $OrderNo = $TransDetails['order_'];
+ $CustBranchCode = $TransDetails['branchcode'];
+ $BranchName = $TransDetails['brname'];
+ $BranchStreet =$TransDetails['braddress1'];
+ $BranchSuburb = $TransDetails['braddress2'];
+ $BranchState = $TransDetails['braddress3'];
+ $BranchZip = $TransDetails['braddress4'];
+ $BranchCountry = $TransDetails['braddress5'];
+ $ExchRate = $TransDetails['rate'];
+ $TaxTotal = number_format($TransDetails['ovgst'],2, '.', '');
+ $ShipToFreight = number_format(round($TransDetails['ovfreight'],2),2, '.', '');
+ $SegCount = 1;
+
+
+ $DatePaymentDue = ConvertToEDIDate(CalcDueDate(ConvertSQLDate($TransDetails['trandate']),$CustDetails['dayinfollowingmonth'], $CustDetails['daysbeforedue']));
+
+ $TotalAmountExclTax = number_format(($TransDetails['ovamount']+ $TransDetails['ovfreight'] + $TransDetails['ovdiscount']),2, '.', '');
+ $TotalAmountInclTax = number_format(($TransDetails['ovamount']+ $TransDetails['ovfreight'] + $TransDetails['ovdiscount'] + $TransDetails['ovgst']),2, '.', '');
+
+ //**************Need to get delivery address as may be diff from branch address
+
+ $sql = "SELECT deliverto,
+ deladd1,
+ deladd2,
+ deladd3,
+ deladd4,
+ deladd5,
+ deladd6,
+ salesorders.customerref
+ FROM debtortrans INNER JOIN salesorders ON debtortrans.order_ = salesorders.orderno
+ WHERE order_ = '" . $OrderNo . "'";
+
+ $ErrMsg = _('There was a problem retrieving the ship to details because');
+ $ShipToLines = DB_query($sql,$db,$ErrMsg);
+
+ While ($ShipTo = DB_fetch_array($ShipToLines)){
+ $ShipToName = $ShipTo[0];
+ $ShipToStreet = $ShipTo[1];
+ $ShipToSuburb = $ShipTo[2];
+ $ShipToState = $ShipTo[3];
+ $ShipToZip = $ShipTo[4];
+ $ShipToCountry = $ShipTo[5];
+ $CustOrderNo = $ShipTo[7];
+
+ }
+
+ //**************Need to get delivery address as may be diff from branch address
+
+ //**************Reece needs NAD ST in every invoice, sometimes freeform text, so no real code
+
+ if($ShipToName === $BranchName){
+ $ShipToCode = $CustBranchCode;
+ } Else {
+ $ShipToCode = $ShipToName;
+ }
+
+ //**************Reece needs NAD ST in every invoice, sometimes freeform text, so no real code
+
+ //**************Taxrate, need to find
+
+ $sql = "SELECT stockmovestaxes.taxrate
+ FROM stockmoves,
+ stockmovestaxes
+ WHERE stockmoves.stkmoveno = stockmovestaxes.stkmoveno
+ AND stockmoves.transno=" . $TransNo . "
+ AND stockmoves.show_on_inv_crds=1
+ LIMIT 0,1";
+
+ $ResultTax = DB_query($sql,$db);
+
+ $TaxRate = 100 * (mysql_result($ResultTax, 0));
+
+ //**************Taxrate, need to find
+
+ //**************Check to see if freight was added, probably specific to Reece and some other OZ hardware stores
+
+ if($ShipToFreight > 0){
+ $FreightTax = number_format(round(($ShipToFreight * $TaxRate/100),2),2, '.', '');
+ $Freight_YN = "ALC+C" . "'" . "MOA+64:" .$ShipToFreight. "'" . "TAX+7+GST+++:::" .$TaxRate. "'". "MOA+124:" .$FreightTax."'";
+ $SegCount = $SegCount + 3;
+ } else {
+ $Freight_YN = "";
+ }
+
+ //**************Check to see if freight was added could do this in Substitution, skip if 0 freight
+
+
+
+ //Get the message lines, replace variable names with data, write the output to a file one line at a time
+
+ $sql = "SELECT section, linetext FROM edimessageformat WHERE partnercode='" . $CustDetails['debtorno'] . "' AND messagetype='INVOIC' ORDER BY sequenceno";
+ $ErrMsg = _('An error occurred in getting the EDI format template for') . ' ' . $CustDetails['debtorno'] . ' ' . _('because');
+ $MessageLinesResult = DB_query($sql, $db,$ErrMsg);
+
+
+ if (DB_num_rows($MessageLinesResult)>0){
+
+
+ $DetailLines = array();
+ $ArrayCounter =0;
+ While ($MessageLine = DB_fetch_array($MessageLinesResult)){
+ if ($MessageLine['section']=='Detail'){
+ $DetailLines[$ArrayCounter]=$MessageLine['linetext'];
+ $ArrayCounter++;
+ }
+ }
+ DB_data_seek($MessageLinesResult,0);
+
+ $EDITransNo = GetNextTransNo(99,$db);
+ $fp = fopen('EDI_INV_' . $TransNo . '.txt', 'w');
+
+ while ($LineDetails = DB_fetch_array($MessageLinesResult)){
+
+ if ($LineDetails['section']=='Heading'){
+ $MsgLineText = $LineDetails['linetext'];
+ include ('includes/EDIVariableSubstitution.inc');
+ $LastLine ='Heading';
+ }
+
+ if ($LineDetails['section']=='Detail' AND $LastLine=='Heading') {
+ /*This must be the detail section
+ need to get the line details for the invoice or credit note
+ for creating the detail lines */
+
+
+ if ($TransDetails['type']==10){ /*its an invoice */
+ $sql = "SELECT stockmoves.stockid,
+ stockmaster.description,
+ -stockmoves.qty as quantity,
+ stockmoves.discountpercent,
+ ((1 - stockmoves.discountpercent) * stockmoves.price * " . $ExchRate . " * -stockmoves.qty) AS fxnet,
+ (stockmoves.price * " . $ExchRate . ") AS fxprice,
+ stockmaster.units
+ FROM stockmoves,
+ stockmaster
+ WHERE stockmoves.stockid = stockmaster.stockid
+ AND stockmoves.type=10
+ AND stockmoves.transno=" . $TransNo . "
+ AND stockmoves.show_on_inv_crds=1";
+
+ } else {
+ /* credit note */
+ $sql = "SELECT stockmoves.stockid,
+ stockmaster.description,
+ stockmoves.qty as quantity,
+ stockmoves.discountpercent,
+ ((1 - stockmoves.discountpercent) * stockmoves.price * " . $ExchRate . " * stockmoves.qty) as fxnet,
+ (stockmoves.price * " . $ExchRate . ") AS fxprice,
+ stockmaster.units
+ FROM stockmoves,
+ stockmaster
+ WHERE stockmoves.stockid = stockmaster.stockid
+ AND stockmoves.type=11 and stockmoves.transno=" . $TransNo . "
+ AND stockmoves.show_on_inv_crds=1";
+ }
+ $TransLinesResult = DB_query($sql,$db);
+
+ $LineNumber = 0;
+ while ($TransLines = DB_fetch_array($TransLinesResult)){
+ /*now set up the variable values */
+
+ $LineNumber++;
+ $StockID = $TransLines['stockid'];
+ $sql = "SELECT partnerstockid
+ FROM ediitemmapping
+ WHERE supporcust='CUST'
+ AND partnercode ='" . $CustDetails['debtorno'] . "'
+ AND stockid='" . $TransLines['stockid'] . "'";
+
+ $CustStkResult = DB_query($sql,$db);
+ if (DB_num_rows($CustStkResult)==1){
+ $CustStkIDRow = DB_fetch_row($CustStkResult);
+ $CustStockID = $CustStkIDRow[0];
+ } else {
+ $CustStockID = 'Not_Known';
+ }
+ $ItemDescription = $TransLines['description'];
+ $QtyInvoiced = $TransLines['quantity'];
+ $LineTotalExclTax = number_format(round($TransLines['fxnet'],3),2, '.', '');
+ $UnitPriceExclTax = number_format(round( $TransLines['fxnet'] / $TransLines['quantity'], 3),2, '.', '');
+ $LineTaxAmount = number_format(round($TaxRate/100 * $TransLines['fxnet'],3),2, '.', '');
+ $LineTotalInclTax = number_format(round((1+$TaxRate/100) * $LineTotalExclTax,3),2, '.', '');
+ $UnitPriceInclTax = number_format(round((1+$TaxRate/100) * $UnitPriceExclTax,2),2, '.', '');
+
+ /*now work through the detail line segments */
+ foreach ($DetailLines as $DetailLineText) {
+ $MsgLineText = $DetailLineText;
+ include ('includes/EDIVariableSubstitution.inc');
+ }
+
+
+ }
+
+
+ $LastLine ='Detail';
+ $NoLines = $LineNumber;
+ }
+
+ if($LineDetails['section']=='Summary' AND $LastLine=='Detail'){
+ $MsgLineText = $LineDetails['linetext'];
+ include ('includes/EDIVariableSubstitution.inc');
+ }
+ } /*end while there are message lines to parse and substitute vbles for */
+ fclose($fp); /*close the file at the end of each transaction */
+ DB_query("UPDATE debtortrans SET EDISent=1 WHERE ID=" . $TransDetails['id'],$db);
+ /*Now send the file using the customer transport */
+ if ($CustDetails['editransport']=='email'){
+
+ $mail = new htmlMimeMail();
+ $attachment = $mail->getFile( "EDI_INV_" . $TransNo .".txt");
+ $mail->SetSubject('EDI Invoice/Credit Note ' . $TransNo);
+ $mail->addAttachment($attachment, 'EDI_INV_' . $TransNo . '.txt', 'application/txt');
+ $mail->setFrom($_SESSION['CompanyRecord']['coyname'] . '<' . $_SESSION['CompanyRecord']['email'] . '>');
+ $MessageSent = $mail->send(array($CustDetails['ediaddress']));
+
+ if ($MessageSent==True){
+ echo '<BR><BR>';
+ prnMsg(_('EDI Message') . ' ' . $TransNo . ' ' . _('was sucessfully emailed'),'success');
+ } else {
+ echo '<BR><BR>';
+ prnMsg(_('EDI Message') . ' ' . $TransNo . _('could not be emailed to') . ' ' . $CustDetails['ediaddress'],'error');
+ }
+ } else { /*it must be ftp transport */
+
+ //Godaddy limitations make it impossible to sftp using ssl or curl, so save to EDI_Sent file and 'rsynch' back to sftp server
+
+ /* set up basic connection
+ $conn_id = ftp_connect($CustDetails['ediaddress']); // login with username and password
+ $login_result = ftp_login($conn_id, $CustDetails['ediserveruser'], $CustDetails['ediserverpwd']); // check connection
+ if ((!$conn_id) || (!$login_result)) {
+ prnMsg( _('Ftp connection has failed'). '<BR>' . _('Attempted to connect to') . ' ' . $CustDetails['ediaddress'] . ' ' ._('for user') . ' ' . $CustDetails['ediserveruser'],'error');
+ include('includes/footer.inc');
+ exit;
+ }
+ $MessageSent = ftp_put($conn_id, $_SESSION['EDI_MsgPending'] . '/EDI_INV_' . $EDITransNo, 'EDI_INV_' . $EDITransNo, FTP_ASCII); // check upload status
+ if (!$MessageSent) {
+ echo '<BR><BR>';
+ prnMsg(_('EDI Message') . ' ' . $EDITransNo . ' ' . _('could not be sent via ftp to') .' ' . $CustDetails['ediaddress'],'error');
+ } else {
+ echo '<BR><BR>';
+ prnMsg( _('Successfully uploaded EDI_INV_') . $EDITransNo . ' ' . _('via ftp to') . ' ' . $CustDetails['ediaddress'],'success');
+ } // close the FTP stream
+ ftp_quit($conn_id);
+ */
+ }
+
+
+ if ($MessageSent==True){ /*the email was sent sucessfully */
+ /* move the sent file to sent directory */
+ $source = 'EDI_INV_' . $TransNo . '.txt';
+ $destination = 'EDI_Sent/EDI_INV_' . $TransNo . '.txt';
+ rename($source, $destination);
+
+ }
+
+ } else {
+
+ prnMsg( _('Cannot create EDI message since there is no EDI INVOIC message template set up for') . ' ' . $CustDetails['debtorno'],'error');
+ } /*End if there is a message template defined for the customer invoic*/
+
+
+ } /* loop around all the customer transactions to be sent */
+
+} /*loop around all the customers enabled for EDI Invoices */
+
+include ('includes/footer.inc');
+?>
Modified: trunk/Labels.php
===================================================================
--- trunk/Labels.php 2012-04-28 19:41:43 UTC (rev 5293)
+++ trunk/Labels.php 2012-04-29 03:45:59 UTC (rev 5294)
@@ -16,7 +16,8 @@
'Letter',
'Letter_Landscape',
'Legal',
- 'Legal_Landscape');
+ 'Legal_Landscape',
+ '28mm_x_45mm_label');
echo '<p class="page_title_text">
@@ -414,9 +415,10 @@
}
//END WHILE LIST LOOP
$i--; //last increment needs to be wound back
- echo '<input type="hidden" name="NoOfFieldsDefined" value="' . $i . '" />';
+
} //end if there are label definitions to show
-
+ echo '<input type="hidden" name="NoOfFieldsDefined" value="' . $i . '" />';
+
echo '<tr>
<td><select name="FieldName">
<option value="itemcode">' . _('Item Code') . '</option>
Modified: trunk/PDFPrintLabel.php
===================================================================
--- trunk/PDFPrintLabel.php 2012-04-28 19:41:43 UTC (rev 5293)
+++ trunk/PDFPrintLabel.php 2012-04-29 03:45:59 UTC (rev 5294)
@@ -3,7 +3,7 @@
include('includes/session.inc');
-$PtsPerMM = 2.83465; //pdf millimetres to points
+$PtsPerMM = 2.83465; //pdf points per mm
if (isset($_POST['ShowLabels'])
@@ -163,25 +163,26 @@
for ($i=0;$i <= $_POST['NoOfLabels'];$i++){
if ($_POST['PrintLabel'.$i]=='on'){
- if ($Field['FieldValue']== 'price'){
- $Value = $_POST['Price' . $i];
- } elseif ($Field['FieldValue']== 'stockid'){
- $Value = $_POST['StockID' . $i];
- } elseif ($Field['FieldValue']== 'description'){
- $Value = $_POST['Description' . $i];
- } elseif ($Field['FieldValue']== 'barcode'){
- $Value = $_POST['Barcode' . $i];
- }
foreach ($LabelFields as $Field){
-
+ //print_r($Field);
+
+ if ($Field['FieldValue']== 'price'){
+ $Value = $_POST['Price' . $i];
+ } elseif ($Field['FieldValue']== 'stockid'){
+ $Value = $_POST['StockID' . $i];
+ } elseif ($Field['FieldValue']== 'description'){
+ $Value = $_POST['Description' . $i];
+ } elseif ($Field['FieldValue']== 'barcode'){
+ $Value = $_POST['Barcode' . $i];
+ }
if ($Field['FieldValue'] == 'price'){ //need to format for the number of decimal places
$LeftOvers = $pdf->addTextWrap($XPos+$Field['HPos'],$YPos-$LabelDimensions['label_height']+$Field['VPos'],$LabelDimensions['label_width']-$Field['HPos'],$Field['FontSize'],$_POST['Price' . $i],'center');
} elseif($Field['Barcode']==1) {
-
+
/* write1DBarcode($code, $type, $x='', $y='', $w='', $h='', $xres='', $style='', $align='')
* Note that the YPos for this function is based on the opposite origin for the Y axis i.e from the bottom not from the top!
*/
-
+
$pdf->write1DBarcode(str_replace('_','',$Value), 'C128',$XPos+$Field['HPos'],$Page_Height - $YPos+$LabelDimensions['label_height']-$Field['VPos']-$Field['FontSize'],$LabelDimensions['label_width']-$Field['HPos'], $Field['FontSize'], 0.4, $style, 'N');
} else {
$LeftOvers = $pdf->addTextWrap($XPos+$Field['HPos'],$YPos-$LabelDimensions['label_height']+$Field['VPos'],$LabelDimensions['label_width']-$Field['HPos']-20,$Field['FontSize'],$Value);
@@ -213,7 +214,7 @@
} //this label is set to print
} //loop through labels selected to print
-
+
$FileName=$_SESSION['DatabaseName']. '_' . _('Price_Labels') . '_' . date('Y-m-d').'.pdf';
ob_clean();
$pdf->OutputD($FileName);
Modified: trunk/doc/Change.log
===================================================================
--- trunk/doc/Change.log 2012-04-28 19:41:43 UTC (rev 5293)
+++ trunk/doc/Change.log 2012-04-29 03:45:59 UTC (rev 5294)
@@ -1,4 +1,5 @@
webERP Change Log
+29/4/12 David Short: Added EDISendInvoices_Reece.php to send Reece format EDI invoices - approved by Reece (Australian Plumbing retailer)
28/4/2012 Exson: Fixed bugs in MRPCalendar.php which caused working days cannot be calculated correctly.
06/4/2012 Exson: Fixed PO header that does not display user's default warehous when iusse a new PO in PO_Header.php.
25/4/12 Phil: Complete rewrite of PDFPrintLabels.php and Labels.php in webERP style - half the code and maintainable with templates stored with all the other data in the database. The new labels also allow fields to be printed as barcodes too.
Modified: trunk/includes/PDFStarter.php
===================================================================
--- trunk/includes/PDFStarter.php 2012-04-28 19:41:43 UTC (rev 5293)
+++ trunk/includes/PDFStarter.php 2012-04-29 03:45:59 UTC (rev 5294)
@@ -128,7 +128,8 @@
case 'legal':
- $DocumentPaper = 'LEGAL'; $DocumentOrientation ='P';
+ $DocumentPaper = 'LEGAL';
+ $DocumentOrientation ='P';
$Page_Width=612;
$Page_Height=1008;
@@ -140,7 +141,8 @@
case 'legal_landscape':
- $DocumentPaper = 'LEGAL'; $DocumentOrientation ='L';
+ $DocumentPaper = 'LEGAL';
+ $DocumentOrientation ='L';
$Page_Width=1008;
$Page_Height=612;
@@ -149,6 +151,19 @@
$Left_Margin=30;
$Right_Margin=25;
break;
+
+ case '28mm_x_45mm_label':
+ //$DocumentPaper can be an array or width/height of paper size
+ //as an alternative to the string ISO paper size required by tcpdf
+ $DocumentPaper = array(127.6,79.4);
+ $DocumentOrientation='L';
+ $Page_Width=127.6;
+ $Page_Height=79.4;
+ $Top_Margin=0;
+ $Bottom_Margin=0;
+ $Left_Margin=0;
+ $Right_Margin=0;
+ break;
}
// Javier: I correct the call to the constructor to match TCPDF (and FPDF ;-)
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
