Menu
▾
▴
[Web-erp-svn] SF.net SVN: web-erp:[6999] trunk
|
From: <dai...@us...> - 2014-11-22 02:39:24
|
Revision: 6999
http://sourceforge.net/p/web-erp/reponame/6999
Author: daintree
Date: 2014-11-22 02:39:16 +0000 (Sat, 22 Nov 2014)
Log Message:
-----------
script to export sales analysis automatically to an ftp server intended to be run from cron
Modified Paths:
--------------
trunk/Z_AutoCustomerAllocations.php
Added Paths:
-----------
trunk/Z_ExportSalesAnalysis.php
Modified: trunk/Z_AutoCustomerAllocations.php
===================================================================
--- trunk/Z_AutoCustomerAllocations.php 2014-11-22 02:28:56 UTC (rev 6998)
+++ trunk/Z_AutoCustomerAllocations.php 2014-11-22 02:39:16 UTC (rev 6999)
@@ -19,7 +19,7 @@
if (isset($_GET['DebtorNo'])) {
// Page called with customer code
-
+
$SQL = "SELECT debtortrans.id,
debtortrans.transno,
systypes.typename,
@@ -53,7 +53,7 @@
}
echo '<table class="selection">';
echo $TableHeader;
-
+
while ($myrow = DB_fetch_array($result)) {
unset($_SESSION['Alloc']->Allocs);
unset($_SESSION['Alloc']);
@@ -215,7 +215,7 @@
'" . $_SESSION['CompanyRecord']['debtorsact'] . "',
'',
'" . -$MovtInDiffOnExch . "')";
-
+
if( !$Result = DB_query($SQL) ) {
$Error = _('Could not update debtors control in General Ledger');
}
@@ -237,15 +237,4 @@
}
}
-?>
-ocTrans']);
- }
-}
-
-?>
-
-}
-
-?>
-
-?>
+?>
\ No newline at end of file
Added: trunk/Z_ExportSalesAnalysis.php
===================================================================
--- trunk/Z_ExportSalesAnalysis.php (rev 0)
+++ trunk/Z_ExportSalesAnalysis.php 2014-11-22 02:39:16 UTC (rev 6999)
@@ -0,0 +1,165 @@
+<?php
+/* $Id: Z_ExportSalesAnalysis.php 5785 2012-12-29 04:47:42Z daintree $*/
+
+/* Creates the csv files necessary for Phocas sales analysis and sends to an ftp server*/
+
+/*Config */
+
+$FTP_Server = 'someftpserver.com';
+$FTP_User = 'someuser';
+$FTP_Password = '';
+
+$AllowAnyone=true;
+$PageSecurity=15;
+$_POST['CompanyNameField']= 'yourdatabase';
+
+include ('includes/session.inc');
+$Title = _('Create and send sales analysis files');
+include ('includes/header.inc');
+
+echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/inventory.png" title="' . _('Export Sales Analysis Files') .'" alt="" /><b>' . $Title. '</b></p>';
+
+function stripcomma($str) { //because we're using comma as a delimiter
+ return str_replace(',', '', $str);
+}
+
+echo '<div class="centre">' . _('Making a comma separated values file of the stock items') . '</div>';
+
+$ErrMsg = _('The SQL to get the stock items failed with the message');
+
+$sql = "SELECT stockid, categoryid, description FROM stockmaster";
+$result = DB_query($sql, $ErrMsg);
+
+if (!file_exists($_SESSION['reports_dir'])){
+ $Result = mkdir('./' . $_SESSION['reports_dir']);
+}
+
+$ItemsFileName = $_SESSION['reports_dir'] . '/StockItems.csv';
+
+unlink($ItemsFileName);
+
+$fp = fopen($ItemsFileName,'w');
+
+if ($fp==FALSE){
+
+ prnMsg(_('Could not open or create the file under') . ' ' . $ItemsFileName,'error');
+ include('includes/footer.inc');
+ exit;
+}
+fputs($fp, '"Item Code", "Category ID", "Item Description"' . "\n");
+While ($myrow = DB_fetch_row($result)){
+ $line = stripcomma($myrow[0]) . ', ' . stripcomma($myrow[1]) . ', ' . stripcomma($myrow[2]);
+ //fputs($fp,"\xEF\xBB\xBF" . $line . "\n");
+ fputs($fp, $line . "\n");
+}
+
+fclose($fp);
+//Now the customers
+
+echo '<div class="centre">' . _('Making a comma separated values file of the customers') . '</div>';
+
+$ErrMsg = _('The SQL to get the customers failed with the message');
+
+$sql = "SELECT debtorsmaster.debtorno, debtorsmaster.name, custbranch.branchcode, brname, salestype, area, salesman FROM debtorsmaster INNER JOIN custbranch ON debtorsmaster.debtorno=custbranch.debtorno";
+$result = DB_query($sql, $ErrMsg);
+
+$CustomersFileName = $_SESSION['reports_dir'] . '/Customers.csv';
+
+unlink($CustomersFileName);
+
+$fp = fopen($CustomersFileName,'w');
+
+if ($fp==FALSE){
+
+ prnMsg(_('Could not open or create the file under') . ' ' . $CustomersFileName,'error');
+ include('includes/footer.inc');
+ exit;
+}
+fputs($fp, '"Customer Code", "Customer Name", "Branch Code", "Branch Name", "Price List", "Sales Area", "Salesman"' . "\n");
+While ($myrow = DB_fetch_row($result)){
+ $line = stripcomma($myrow[0]) . ', ' . stripcomma($myrow[1]). ', ' . stripcomma($myrow[2]) . ', ' . stripcomma($myrow[3]) . ', ' . stripcomma($myrow[4]) . ', ' . stripcomma($myrow[5]) . ', ' . stripcomma($myrow[6]);
+ //fputs($fp,"\xEF\xBB\xBF" . $line . "\n");
+ fputs($fp, $line . "\n");
+}
+
+fclose($fp);
+
+//Now the sales analysis invoice & credit note lines
+
+echo '<div class="centre">' . _('Making a comma separated values file of the sales lines') . '</div>';
+
+$ErrMsg = _('The SQL to get the sales data failed with the message');
+
+$sql = "SELECT stockmoves.debtorno,
+ stockmoves.branchcode,
+ stockid,
+ trandate,
+ -qty,
+ -price*(1-discountpercent)*qty,
+ -standardcost*qty,
+ transno
+ FROM stockmoves
+ INNER JOIN custbranch
+ ON stockmoves.debtorno=custbranch.debtorno
+ AND stockmoves.branchcode=custbranch.branchcode
+ WHERE (stockmoves.type=10 OR stockmoves.type=11)
+ AND show_on_inv_crds=1";
+
+$result = DB_query($sql, $ErrMsg);
+
+$SalesFileName = $_SESSION['reports_dir'] . '/SalesAnalysis.csv';
+
+unlink($SalesFileName);
+
+$fp = fopen($SalesFileName,'w');
+
+if ($fp==FALSE){
+
+ prnMsg(_('Could not open or create the file under') . ' ' . $SalesFileName,'error');
+ include('includes/footer.inc');
+ exit;
+}
+fputs($fp,'"Customer Code", "Branch Code", "Item Code", "Date", "Quantity", "Line Value", "Line Cost", "Inv/Credit Number"' . "\n");
+While ($myrow = DB_fetch_row($result)){
+ $line = stripcomma($myrow[0]) . ', ' . stripcomma($myrow[1]) . ', ' . stripcomma($myrow[2]) . ', ' . stripcomma($myrow[3]) . ', ' . stripcomma($myrow[4]) . ', ' . stripcomma($myrow[5]) . ', ' . stripcomma($myrow[6]) . ', ' . stripcomma($myrow[7]);
+ //fputs($fp,"\xEF\xBB\xBF" . $line . "\n");
+ fputs($fp, $line . "\n");
+}
+
+fclose($fp);
+
+// set up basic ftp connection
+$conn_id = ftp_connect($FTP_Server);
+
+// login with username and password
+$login_result = ftp_login($conn_id, $FTP_User, $FTP_Password);
+
+// check connection
+if ((!$conn_id) || (!$login_result)) {
+ echo "FTP connection has failed!";
+ echo "Attempted to connect to $FTP_Server with user $FTP_User";
+ exit;
+} else {
+ echo "Connected to ftp_server";
+}
+
+// upload the files
+$upload = ftp_put($conn_id, '/' . Date('Y-m-d') .'_Sales.csv', $SalesFileName, FTP_BINARY);
+$upload = ftp_put($conn_id, '/' . Date('Y-m-d') .'_Items.csv', $ItemsFileName, FTP_BINARY);
+$upload = ftp_put($conn_id, '/' . Date('Y-m-d') .'_Customers.csv', $CustomersFileName, FTP_BINARY);
+
+// check upload status
+if (!$upload) {
+ echo "FTP upload has failed!";
+} else {
+ echo "Uploaded $source_file to $ftp_server as $destination_file";
+}
+
+// close the FTP stream
+ftp_close($conn_id);
+
+include('includes/footer.inc');
+
+?>oter.inc');
+
+?>
\ No newline at end of file
Property changes on: trunk/Z_ExportSalesAnalysis.php
___________________________________________________________________
Added: svn:executable
## -0,0 +1 ##
+*
\ No newline at end of property
|
