/* $Revision: 1.8 $ */
$PageSecurity = 5;
include 'includes/session.inc';
$title = _('Produce Stock Quantities CSV');
include 'includes/header.inc';
function stripcomma($str)
{
//because we're using comma as a delimiter
return str_replace(",", "", $str);
}
echo '<p>' . _('Making a comma separated values file of the current stock quantities');
$ErrMsg = _('The SQL to get the stock quantities failed with the message');
$sql = 'SELECT stockid, SUM(quantity) FROM locstock GROUP BY stockid HAVING SUM(quantity)<>0';
$result = DB_query($sql, $db, $ErrMsg);
if (!file_exists($_SESSION['reports_dir'])) {
$Result = mkdir('./' . $_SESSION['reports_dir']);
}
$filename = $_SESSION['reports_dir'] . '/StockQties.csv';
$fp = fopen($filename, "w");
if ($fp == FALSE) {
prnMsg(_('Could not open or create the file under') . ' ' . $_SESSION['reports_dir'] . '/StockQties.csv', 'error');
include 'includes/footer.inc';
exit;
}
while ($myrow = DB_fetch_row($result)) {
$line = stripcomma($myrow[0]) . ', ' . stripcomma($myrow[1]);
fputs($fp, $line . "\n");
}
fclose($fp);
echo "<p><a href='" . $rootpath . '/' . $_SESSION['reports_dir'] . "/StockQties.csv'>" . _('click here') . '</a> ' . _('to view the file') . '<br>';
include 'includes/footer.inc';
$SQL = "SELECT userid,\n\t\t\tpassword,\n\t\t\trealname,\n\t\t\tcustomerid,\n\t\t\tphone,\n\t\t\temail,\n\t\t\tdefaultlocation,\n\t\t\tfullaccess,\n\t\t\tlastvisitdate,\n\t\t\tbranchcode,\n\t\t\tpagesize,\n\t\t\tmodulesallowed,\n\t\t\tblocked,\n\t\t\tdisplayrecordsmax,\n\t\t\ttheme,\n\t\t\tlanguage\n\t\tFROM www_users\n\t\tWHERE (customerid <> '') OR\n\t\t\t(NOT customerid IS NULL)";
$SecUserResult = DB_query($SQL, '', '', false, false);
if (DB_error_no() != 0) {
$Title = _('Security User List Export Problem ....');
include 'includes/header.inc';
prnMsg(_('The Security User List could not be retrieved by the SQL because') . ' - ' . DB_error_msg(), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
$CSVContent = stripcomma('userid') . ',' . stripcomma('password') . ',' . stripcomma('realname') . ',' . stripcomma('customerid') . ',' . stripcomma('phone') . ',' . stripcomma('email') . ',' . stripcomma('defaultlocation') . ',' . stripcomma('fullaccess') . ',' . stripcomma('lastvisitdate') . ',' . stripcomma('branchcode') . ',' . stripcomma('pagesize') . ',' . stripcomma('modulesallowed') . ',' . stripcomma('blocked') . ',' . stripcomma('displayrecordsmax') . ',' . stripcomma('theme') . ',' . stripcomma('language') . ',' . stripcomma('pinno') . ',' . stripcomma('swipecard') . "\n";
while ($SecUserList = DB_fetch_array($SecUserResult, $db)) {
$CSVContent .= stripcomma($SecUserList['userid']) . ',' . stripcomma($SecUserList['password']) . ',' . stripcomma($SecUserList['realname']) . ',' . stripcomma($SecUserList['customerid']) . ',' . stripcomma($SecUserList['phone']) . ',' . stripcomma($SecUserList['email']) . ',' . stripcomma($SecUserList['defaultlocation']) . ',' . stripcomma($SecUserList['fullaccess']) . ',' . stripcomma($SecUserList['lastvisitdate']) . ',' . stripcomma($SecUserList['branchcode']) . ',' . stripcomma($SecUserList['pagesize']) . ',' . stripcomma($SecUserList['modulesallowed']) . ',' . stripcomma($SecUserList['blocked']) . ',' . stripcomma($SecUserList['displayrecordsmax']) . ',' . stripcomma($SecUserList['theme']) . ',' . stripcomma($SecUserList['language']) . ',' . stripcomma($SecUserList['pinno']) . ',' . stripcomma($SecUserList['swipecard']) . "\n";
}
header('Content-type: application/csv');
header('Content-Length: ' . mb_strlen($CSVContent));
header('Content-Disposition: inline; filename=SecUserList.csv');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
echo $CSVContent;
exit;
} else {
$Title = _('Data Exports');
include 'includes/header.inc';
// SELECT EXPORT FOR PRICE LIST
echo '<br />';
echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">';
$RetainedEarningsAct = $_SESSION['CompanyRecord']['retainedearnings'];
$SQL = "SELECT accountgroups.groupname,\n\t\t\taccountgroups.parentgroupname,\n\t\t\taccountgroups.pandl,\n\t\t\tchartdetails.accountcode ,\n\t\t\tchartmaster.accountname,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_GET['FromPeriod'] . "' THEN chartdetails.bfwd ELSE 0 END) AS firstprdbfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_GET['FromPeriod'] . "' THEN chartdetails.bfwdbudget ELSE 0 END) AS firstprdbudgetbfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_GET['ToPeriod'] . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS lastprdcfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_GET['ToPeriod'] . "' THEN chartdetails.actual ELSE 0 END) AS monthactual,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_GET['ToPeriod'] . "' THEN chartdetails.budget ELSE 0 END) AS monthbudget,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_GET['ToPeriod'] . "' THEN chartdetails.bfwdbudget + chartdetails.budget ELSE 0 END) AS lastprdbudgetcfwd\n\t\tFROM chartmaster INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname\n\t\t\tINNER JOIN chartdetails ON chartmaster.accountcode= chartdetails.accountcode\n\t\tGROUP BY accountgroups.groupname,\n\t\t\t\taccountgroups.parentgroupname,\n\t\t\t\taccountgroups.pandl,\n\t\t\t\taccountgroups.sequenceintb,\n\t\t\t\tchartdetails.accountcode,\n\t\t\t\tchartmaster.accountname\n\t\tORDER BY accountgroups.pandl desc,\n\t\t\taccountgroups.sequenceintb,\n\t\t\taccountgroups.groupname,\n\t\t\tchartdetails.accountcode";
$AccountsResult = DB_query($SQL);
while ($myrow = DB_fetch_array($AccountsResult)) {
if ($myrow['pandl'] == 1) {
$AccountPeriodActual = $myrow['lastprdcfwd'] - $myrow['firstprdbfwd'];
$AccountPeriodBudget = $myrow['lastprdbudgetcfwd'] - $myrow['firstprdbudgetbfwd'];
$PeriodProfitLoss += $AccountPeriodActual;
$PeriodBudgetProfitLoss += $AccountPeriodBudget;
$MonthProfitLoss += $myrow['monthactual'];
$MonthBudgetProfitLoss += $myrow['monthbudget'];
$BFwdProfitLoss += $myrow['firstprdbfwd'];
} else {
/*PandL ==0 its a balance sheet account */
if ($myrow['accountcode'] == $RetainedEarningsAct) {
$AccountPeriodActual = $BFwdProfitLoss + $myrow['lastprdcfwd'];
$AccountPeriodBudget = $BFwdProfitLoss + $myrow['lastprdbudgetcfwd'] - $myrow['firstprdbudgetbfwd'];
} else {
$AccountPeriodActual = $myrow['lastprdcfwd'];
$AccountPeriodBudget = $myrow['firstprdbfwd'] + $myrow['lastprdbudgetcfwd'] - $myrow['firstprdbudgetbfwd'];
}
}
$CSV_File .= $myrow['accountcode'] . ', ' . stripcomma($myrow['accountname']) . ', ' . $AccountPeriodActual . ', ' . $AccountPeriodBudget . "\n";
}
//loop through the accounts
function stripcomma($str)
{
//because we're using comma as a delimiter
return str_replace(',', '', $str);
}
echo $CSV_File;
/* $Id$*/
include 'includes/session.inc';
$title = _('Produce Stock Quantities CSV');
include 'includes/header.inc';
echo '<p class="page_title_text"><img src="' . $rootpath . '/css/' . $theme . '/images/inventory.png" title="' . _('Inventory') . '" 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 current stock quantities') . '</div>';
$ErrMsg = _('The SQL to get the stock quantities failed with the message');
$sql = "SELECT stockid, SUM(quantity) AS qty FROM locstock GROUP BY stockid HAVING SUM(quantity)<>0";
$result = DB_query($sql, $db, $ErrMsg);
if (!file_exists($_SESSION['reports_dir'])) {
$Result = mkdir('./' . $_SESSION['reports_dir']);
}
$filename = $_SESSION['reports_dir'] . '/StockQties.csv';
$fp = fopen($filename, "w");
if ($fp == FALSE) {
prnMsg(_('Could not open or create the file under') . ' ' . $_SESSION['reports_dir'] . '/StockQties.csv', 'error');
include 'includes/footer.inc';
exit;
}
while ($myrow = DB_fetch_array($result)) {
$line = stripcomma($myrow['stockid']) . ', ' . stripcomma($myrow['qty']);
fputs($fp, $line . "\n");
}
fclose($fp);
echo '<br /><div class="centre"><a href="' . $rootpath . '/' . $_SESSION['reports_dir'] . '/StockQties.csv ">' . _('click here') . '</a> ' . _('to view the file') . '</div>';
include 'includes/footer.inc';
//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 \tstockmoves.debtorno,\n\t\t\t\tstockmoves.branchcode,\n\t\t\t\tstockid,\n\t\t\t\ttrandate,\n\t\t\t\t-qty,\n\t\t\t\t-price*(1-discountpercent)*qty,\n\t\t\t\t-standardcost*qty,\n\t\t\t\ttransno\n\t\t\tFROM stockmoves\n\t\t\tINNER JOIN custbranch\n\t\t\tON stockmoves.debtorno=custbranch.debtorno\n\t\t\t\tAND stockmoves.branchcode=custbranch.branchcode\n\t\t\tWHERE (stockmoves.type=10 OR stockmoves.type=11)\n\t\t\tAND 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";
<th></th>
<th>' . _('Opening Balance') . '</th>
<th>' . _('Debits') . '</th>
<th>' . _('Credits') . '</th>
<th>' . _('Balance') . '</th>
</tr>
<tr>
<td>' . _('TOTALS') . '</td>
<td class="number">' . locale_number_format($OpeningBalances, $_SESSION['CompanyRecord']['decimalplaces']) . '</td>
<td class="number">' . locale_number_format($Debits, $_SESSION['CompanyRecord']['decimalplaces']) . '</td>
<td class="number">' . locale_number_format($Credits, $_SESSION['CompanyRecord']['decimalplaces']) . '</td>
<td class="number">' . locale_number_format($ClosingBalances, $_SESSION['CompanyRecord']['decimalplaces']) . '</td>
</tr>
</table>';
} else {
$CSVFile .= '"' . _('TOTALS') . '","' . stripcomma($OpeningBalances) . '","' . stripcomma($Debits) . '","' . stripcomma($Credits) . '","' . stripcomma($ClosingBalances) . '"' . "\n";
}
}
if (isset($_POST['CreateCSV'])) {
header('Content-Encoding: UTF-8');
header('Content-type: text/csv; charset=UTF-8');
header("Content-disposition: attachment; filename=CustomerBalancesMovement_" . FormatDateForSQL($_POST['FromDate']) . '-' . FormatDateForSQL($_POST['ToDate']) . '.csv');
header("Pragma: public");
header("Expires: 0");
echo "";
// UTF-8 BOM
echo $CSVFile;
exit;
}
include 'includes/footer.inc';
function stripcomma($str)
$SQLStarter .= " AND stockmoves.loccode ='" . $_POST['Location'] . "'";
}
$CSVListing = _('Category ID') . ',' . _('Category Description') . ',' . _('Stock ID') . ',' . _('Description') . ',' . _('QOH') . ',';
for ($i = 0; $i < 24; $i++) {
$CSVListing .= $Periods[$i]['Month'] . ',';
}
$CSVListing .= "\r\n";
$Category = '';
while ($InventoryPlan = DB_fetch_array($InventoryResult)) {
$SQL = $SQLStarter . " AND stockid='" . $InventoryPlan['stockid'] . "' GROUP BY stockmoves.stockid";
$SalesResult = DB_query($SQL, _('The stock usage of this item could not be retrieved because'));
if (DB_num_rows($SalesResult) == 0) {
$CSVListing .= stripcomma($InventoryPlan['categoryid']) . ',' . stripcomma($InventoryPlan['categorydescription']) . ',' . stripcomma($InventoryPlan['stockid']) . ',' . stripcomma($InventoryPlan['description']) . ',' . stripcomma($InventoryPlan['qoh']) . "\r\n";
} else {
$SalesRow = DB_fetch_array($SalesResult);
$CSVListing .= stripcomma($InventoryPlan['categoryid']) . ',' . stripcomma($InventoryPlan['categorydescription']) . ',' . stripcomma($InventoryPlan['stockid']) . ',' . stripcomma($InventoryPlan['description']) . ',' . stripcomma($InventoryPlan['qoh']);
for ($i = 0; $i < 24; $i++) {
$CSVListing .= ',' . $SalesRow['prd' . $i];
}
$CSVListing .= "\r\n";
}
}
header('Content-Encoding: UTF-8');
header('Content-type: text/csv; charset=UTF-8');
header("Content-disposition: attachment; filename=InventoryPlanning_" . Date('Y-m-d:h:m:s') . '.csv');
header("Pragma: public");
header("Expires: 0");
echo "";
// UTF-8
echo $CSVListing;
exit;
