/* $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;