$Periods = DB_query($sql);
while ($myrow = DB_fetch_array($Periods, $db)) {
echo '<option value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
}
echo '</select></td></tr>';
$sql = "SELECT MAX(periodno) FROM periods";
$MaxPrd = DB_query($sql);
$MaxPrdrow = DB_fetch_row($MaxPrd);
$DefaultToPeriod = (int) ($MaxPrdrow[0] - 1);
echo '<tr><td>' . _('Select Period To') . ':</td><td><select name="ToPeriod">';
$RetResult = DB_data_seek($Periods, 0);
while ($myrow = DB_fetch_array($Periods, $db)) {
if ($myrow['periodno'] == $DefaultToPeriod) {
echo '<option selected="selected" value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
} else {
echo '<option value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
}
}
echo '</select></td></tr></table>';
echo '<div class="centre"><input type="submit" name="recalc" value="' . _('Do the Recalculation') . '" /></div>
</div>
</form>';
} else {
/*OK do the updates */
for ($i = $_POST['FromPeriod']; $i <= $_POST['ToPeriod']; $i++) {
$sql = "SELECT accountcode,\n\t\t\t\t\tperiod,\n\t\t\t\t\tbudget,\n\t\t\t\t\tactual,\n\t\t\t\t\tbfwd,\n\t\t\t\t\tbfwdbudget\n\t\t\t\tFROM chartdetails\n\t\t\t\tWHERE period ='" . $i . "'";
$ErrMsg = _('Could not retrieve the ChartDetail records because');
$result = DB_query($sql, $ErrMsg);
while ($myrow = DB_fetch_array($result)) {
$CFwd = $myrow['bfwd'] + $myrow['actual'];
$CFwdBudget = $myrow['bfwdbudget'] + $myrow['budget'];
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<table class="selection">';
echo '<tr>
<td>' . _('From Supplier Code') . ':</td>
<td><input type="text" maxlength="6" size="7" name="FromCriteria" value="' . $_POST['FromCriteria'] . '" /></td>
</tr>
<tr>
<td>' . _('To Supplier Code') . ':</td>
<td><input type="text" maxlength="6" size="7" name="ToCriteria" value="' . $_POST['ToCriteria'] . '" /></td>
</tr>
<tr>
<td>' . _('Balances As At') . ':</td>
<td><select name="PeriodEnd">';
$sql = "SELECT periodno,\n\t\t\t\t\tlastdate_in_period\n\t\t\tFROM periods\n\t\t\tORDER BY periodno DESC";
$ErrMsg = _('Could not retrieve period data because');
$Periods = DB_query($sql, $db, $ErrMsg);
while ($myrow = DB_fetch_array($Periods, $db)) {
echo '<option value="' . $myrow['lastdate_in_period'] . '" selected="selected" >' . MonthAndYearFromSQLDate($myrow['lastdate_in_period'], 'M', -1) . '</option>';
}
echo '</select></td>
</tr>';
echo '</table>
<br />
<div class="centre">
<input type="submit" name="PrintPDF" value="' . _('Print PDF') . '" />
</div>';
echo '</div>
</form>';
include 'includes/footer.inc';
}
/*end of else not PrintPDF */
$pdf->line($Left_Margin + 250, $YPos, $Left_Margin + 500, $YPos);
$pdf->OutputD($_SESSION['DatabaseName'] . '_GL_Trial_Balance_' . Date('Y-m-d') . '.pdf');
$pdf->__destruct();
exit;
} else {
include 'includes/header.inc';
echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">
<div>
<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
<input type="hidden" name="FromPeriod" value="' . $_POST['FromPeriod'] . '" />
<input type="hidden" name="ToPeriod" value="' . $_POST['ToPeriod'] . '" />';
$NumberOfMonths = $_POST['ToPeriod'] - $_POST['FromPeriod'] + 1;
$sql = "SELECT lastdate_in_period\n\t\t\tFROM periods\n\t\t\tWHERE periodno='" . $_POST['ToPeriod'] . "'";
$PrdResult = DB_query($sql, $db);
$myrow = DB_fetch_row($PrdResult);
$PeriodToDate = MonthAndYearFromSQLDate($myrow[0]);
$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='" . $_POST['FromPeriod'] . "' THEN chartdetails.bfwd ELSE 0 END) AS firstprdbfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['FromPeriod'] . "' THEN chartdetails.bfwdbudget ELSE 0 END) AS firstprdbudgetbfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['ToPeriod'] . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS lastprdcfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['ToPeriod'] . "' THEN chartdetails.actual ELSE 0 END) AS monthactual,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['ToPeriod'] . "' THEN chartdetails.budget ELSE 0 END) AS monthbudget,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['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.pandl,\n\t\t\t\taccountgroups.sequenceintb,\n\t\t\t\taccountgroups.parentgroupname,\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, $db, _('No general ledger accounts were returned by the SQL because'), _('The SQL that failed was:'));
echo '<p class="page_title_text"><img src="' . $RootPath . '/css/' . $Theme . '/images/magnifier.png" title="' . _('Trial Balance') . '" alt="" />' . ' ' . _('Trial Balance Report') . '</p>';
/*show a table of the accounts info returned by the SQL
Account Code , Account Name , Month Actual, Month Budget, Period Actual, Period Budget */
echo '<table cellpadding="2" class="selection">';
echo '<tr>
<th colspan="6"><b>' . _('Trial Balance for the month of ') . $PeriodToDate . _(' and for the ') . $NumberOfMonths . _(' months to ') . $PeriodToDate . '</b></th>
</tr>';
$TableHeader = '<tr>
<th>' . _('Account') . '</th>
<th>' . _('Account Name') . '</th>
<th>' . _('Month Actual') . '</th>
<th>' . _('Month Budget') . '</th>
$DefaultToPeriod = $MaxPrdrow[0];
} else {
$DefaultToPeriod = $_POST['ToPeriod'];
}
echo '<tr>
<td>' . _('Start Period:') . '</td>
<td><select minlength="0" name="FromPeriod">';
$ToSelect = '<tr><td>' . _('End Period:') . '</td>
<td><select minlength="0" name="ToPeriod">';
$SQL = "SELECT periodno, lastdate_in_period FROM periods ORDER BY periodno";
$perResult = DB_query($SQL, $db);
while ($perRow = DB_fetch_array($perResult)) {
$FromSelected = $perRow['periodno'] == $DefaultFromPeriod ? 'selected="selected"' : '';
echo '<option ' . $FromSelected . ' value="' . $perRow['periodno'] . '">' . MonthAndYearFromSQLDate($perRow['lastdate_in_period']) . '</option>';
$ToSelected = $perRow['periodno'] == $DefaultToPeriod ? 'selected="selected"' : '';
$ToSelect .= '<option ' . $ToSelected . ' value="' . $perRow['periodno'] . '">' . MonthAndYearFromSQLDate($perRow['lastdate_in_period']) . '</option>';
}
DB_free_result($perResult);
echo '</select></td></tr>';
echo $ToSelect . '</select></td></tr>';
echo '</table>';
echo '<br /><input type="submit" name="Show" value="' . _('Accept') . '" />';
echo '<input type="submit" value="' . _('Cancel') . '" />';
if (isset($_POST['Show'])) {
//
//========[ SHOW SYNOPSYS ]===========
//
echo '<br /><table border="1">';
echo '<tr>
<th>' . _('Period') . '</th>
<th>' . _('Bal B/F in GL') . '</th>
$tableheader = "<TR><TH>" . _('Month') . "</TH><TH>" . _('Usage') . '</TH></TR>';
echo $tableheader;
$j = 1;
$k = 0;
//row colour counter
$TotalUsage = 0;
$PeriodsCounter = 0;
while ($myrow = DB_fetch_array($MovtsResult)) {
if ($k == 1) {
echo '<tr class="EvenTableRows">';
$k = 0;
} else {
echo '<tr class="OddTableRows">';
$k++;
}
$DisplayDate = MonthAndYearFromSQLDate($myrow['lastdate_in_period']);
$TotalUsage += $myrow['qtyused'];
$PeriodsCounter++;
printf('<td>%s</td><td ALIGN=RIGHT>%s</td></tr>', $DisplayDate, number_format($myrow['qtyused'], $DecimalPlaces));
//end of page full new headings if
}
//end of while loop
echo '</TABLE>';
if ($TotalUsage > 0 && $PeriodsCounter > 0) {
echo '<BR>' . _('Average Usage per month is') . ' ' . number_format($TotalUsage / $PeriodsCounter);
}
}
/* end if Show Usage is clicked */
echo "<HR><A HREF='{$rootpath}/StockStatus.php?" . SID . "&StockID={$StockID}'>" . _('Show Stock Status') . '</A>';
echo "<BR><A HREF='{$rootpath}/StockMovements.php?" . SID . "&StockID={$StockID}&StockLocation=" . $_POST['StockLocation'] . "'>" . _('Show Stock Movements') . '</A>';
echo "<BR><A HREF='{$rootpath}/SelectSalesOrder.php?" . SID . "&SelectedStockItem={$StockID}&StockLocation=" . $_POST['StockLocation'] . "'>" . _('Search Outstanding Sales Orders') . '</A>';
if ($TransRow['account'] == $_SESSION['CompanyRecord']['debtorsact']) {
$Credit = locale_number_format(-($DetailRow['ovamount'] + $DetailRow['ovgst'] + $DetailRow['ovfreight']) / $DetailRow['rate'], $_SESSION['CompanyRecord']['decimalplaces']);
$Debit = ' ';
} else {
$Credit = locale_number_format(($DetailRow['ovamount'] + $DetailRow['ovgst']) / $DetailRow['rate'], $_SESSION['CompanyRecord']['decimalplaces']);
$Debit = ' ';
}
}
if ($j == 1) {
echo '<tr class="OddTableRows">';
$j = 0;
} else {
echo '<tr class="EvenTableRows">';
$j++;
}
echo ' <td>' . MonthAndYearFromSQLDate($TransRow['lastdate_in_period']) . '</td>
<td>' . $TranDate . '</td>
<td><a href="' . $URL . $DetailRow['otherpartycode'] . $FromDate . '">' . $TransRow['accountname'] . ' - ' . $DetailRow['otherparty'] . '</a></td>
<td>' . $TransRow['narrative'] . '</td>
<td class="number">' . $Debit . '</td>
<td class="number">' . $Credit . '</td>
<td>' . $Posted . '</td>
</tr>';
}
DB_free_result($DetailResult);
$AnalysisCompleted = 'Done';
}
}
DB_free_result($TransResult);
echo '<tr style="background-color:#FFFFFF">
<td class="number" colspan="4"><b>' . _('Total') . '</b></td>
function submit(&$db, $TabToShow)
{
//initialise no input errors
$InputError = 0;
//first off validate inputs sensible
if ($InputError == 0) {
// Creation of beginning of SQL query
$SQL = "SELECT pcexpenses.codeexpense,";
// Creation of periods SQL query
$period_today = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
$sqlPeriods = "SELECT periodno,\r\n\t\t\t\t\t\tlastdate_in_period\r\n\t\t\t\tFROM periods\r\n\t\t\t\tWHERE periodno <= " . $period_today . "\r\n\t\t\t\tORDER BY periodno DESC\r\n\t\t\t\tLIMIT 24";
$Periods = DB_query($sqlPeriods);
$numPeriod = 0;
$LabelsArray = array();
while ($myrow = DB_fetch_array($Periods, $db)) {
$numPeriod++;
$LabelsArray[$numPeriod] = MonthAndYearFromSQLDate($myrow['lastdate_in_period']);
$SQL = $SQL . "(SELECT SUM(pcashdetails.amount)\r\n\t\t\t\t\t\t\tFROM pcashdetails\r\n\t\t\t\t\t\t\tWHERE pcashdetails.codeexpense = pcexpenses.codeexpense";
if ($TabToShow != 'All') {
$SQL = $SQL . " \tAND pcashdetails.tabcode = '" . $TabToShow . "'";
}
$SQL = $SQL . "\t\tAND date >= '" . beginning_of_month($myrow['lastdate_in_period']) . "'\r\n\t\t\t\t\t\t\t\tAND date <= '" . $myrow['lastdate_in_period'] . "') AS expense_period" . $numPeriod . ", ";
}
// Creation of final part of SQL
$SQL = $SQL . " pcexpenses.description\r\n\t\t\t\tFROM pcexpenses\r\n\t\t\t\tORDER BY pcexpenses.codeexpense";
$result = DB_query($SQL);
if (DB_num_rows($result) != 0) {
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("webERP")->setLastModifiedBy("webERP")->setTitle("Petty Cash Expenses Analysis")->setSubject("Petty Cash Expenses Analysis")->setDescription("Petty Cash Expenses Analysis")->setKeywords("")->setCategory("");
$objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('C:AB')->getNumberFormat()->setFormatCode('#,###');
// Add title data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Code');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Description');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Total 12 Months');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'Average 12 Months');
$objPHPExcel->getActiveSheet()->setCellValue('E1', $LabelsArray[24]);
$objPHPExcel->getActiveSheet()->setCellValue('F1', $LabelsArray[23]);
$objPHPExcel->getActiveSheet()->setCellValue('G1', $LabelsArray[22]);
$objPHPExcel->getActiveSheet()->setCellValue('H1', $LabelsArray[21]);
$objPHPExcel->getActiveSheet()->setCellValue('I1', $LabelsArray[20]);
$objPHPExcel->getActiveSheet()->setCellValue('J1', $LabelsArray[19]);
$objPHPExcel->getActiveSheet()->setCellValue('K1', $LabelsArray[18]);
$objPHPExcel->getActiveSheet()->setCellValue('L1', $LabelsArray[17]);
$objPHPExcel->getActiveSheet()->setCellValue('M1', $LabelsArray[16]);
$objPHPExcel->getActiveSheet()->setCellValue('N1', $LabelsArray[15]);
$objPHPExcel->getActiveSheet()->setCellValue('O1', $LabelsArray[14]);
$objPHPExcel->getActiveSheet()->setCellValue('P1', $LabelsArray[13]);
$objPHPExcel->getActiveSheet()->setCellValue('Q1', $LabelsArray[12]);
$objPHPExcel->getActiveSheet()->setCellValue('R1', $LabelsArray[11]);
$objPHPExcel->getActiveSheet()->setCellValue('S1', $LabelsArray[10]);
$objPHPExcel->getActiveSheet()->setCellValue('T1', $LabelsArray[9]);
$objPHPExcel->getActiveSheet()->setCellValue('U1', $LabelsArray[8]);
$objPHPExcel->getActiveSheet()->setCellValue('V1', $LabelsArray[7]);
$objPHPExcel->getActiveSheet()->setCellValue('W1', $LabelsArray[6]);
$objPHPExcel->getActiveSheet()->setCellValue('X1', $LabelsArray[5]);
$objPHPExcel->getActiveSheet()->setCellValue('Y1', $LabelsArray[4]);
$objPHPExcel->getActiveSheet()->setCellValue('Z1', $LabelsArray[3]);
$objPHPExcel->getActiveSheet()->setCellValue('AA1', $LabelsArray[2]);
$objPHPExcel->getActiveSheet()->setCellValue('AB1', $LabelsArray[1]);
// Add data
$i = 2;
while ($myrow = DB_fetch_array($result)) {
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $myrow['codeexpense']);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $myrow['description']);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, '=SUM(Q' . $i . ':AB' . $i . ')');
$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, '=AVERAGE(Q' . $i . ':AB' . $i . ')');
$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, -$myrow['expense_period24']);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $i, -$myrow['expense_period23']);
$objPHPExcel->getActiveSheet()->setCellValue('G' . $i, -$myrow['expense_period22']);
$objPHPExcel->getActiveSheet()->setCellValue('H' . $i, -$myrow['expense_period21']);
$objPHPExcel->getActiveSheet()->setCellValue('I' . $i, -$myrow['expense_period20']);
$objPHPExcel->getActiveSheet()->setCellValue('J' . $i, -$myrow['expense_period19']);
$objPHPExcel->getActiveSheet()->setCellValue('K' . $i, -$myrow['expense_period18']);
$objPHPExcel->getActiveSheet()->setCellValue('L' . $i, -$myrow['expense_period17']);
$objPHPExcel->getActiveSheet()->setCellValue('M' . $i, -$myrow['expense_period16']);
$objPHPExcel->getActiveSheet()->setCellValue('N' . $i, -$myrow['expense_period15']);
$objPHPExcel->getActiveSheet()->setCellValue('O' . $i, -$myrow['expense_period14']);
$objPHPExcel->getActiveSheet()->setCellValue('P' . $i, -$myrow['expense_period13']);
$objPHPExcel->getActiveSheet()->setCellValue('Q' . $i, -$myrow['expense_period12']);
$objPHPExcel->getActiveSheet()->setCellValue('R' . $i, -$myrow['expense_period11']);
$objPHPExcel->getActiveSheet()->setCellValue('S' . $i, -$myrow['expense_period10']);
$objPHPExcel->getActiveSheet()->setCellValue('T' . $i, -$myrow['expense_period9']);
$objPHPExcel->getActiveSheet()->setCellValue('U' . $i, -$myrow['expense_period8']);
$objPHPExcel->getActiveSheet()->setCellValue('V' . $i, -$myrow['expense_period7']);
$objPHPExcel->getActiveSheet()->setCellValue('W' . $i, -$myrow['expense_period6']);
$objPHPExcel->getActiveSheet()->setCellValue('X' . $i, -$myrow['expense_period5']);
$objPHPExcel->getActiveSheet()->setCellValue('Y' . $i, -$myrow['expense_period4']);
$objPHPExcel->getActiveSheet()->setCellValue('Z' . $i, -$myrow['expense_period3']);
$objPHPExcel->getActiveSheet()->setCellValue('AA' . $i, -$myrow['expense_period2']);
$objPHPExcel->getActiveSheet()->setCellValue('AB' . $i, -$myrow['expense_period1']);
$i++;
}
// Freeze panes
$objPHPExcel->getActiveSheet()->freezePane('E2');
// Auto Size columns
foreach (range('A', 'AB') as $columnID) {
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}
// Rename worksheet
if ($TabToShow == 'All') {
$objPHPExcel->getActiveSheet()->setTitle('All Accounts');
} else {
$objPHPExcel->getActiveSheet()->setTitle($TabToShow);
}
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$File = 'PCExpensesAnalysis-' . Date('Y-m-d') . '.xlsx';
header('Content-Disposition: attachment;filename="' . $File . '"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
} else {
$Title = _('Excel file for petty Cash Expenses Analysis');
include 'includes/header.inc';
prnMsg('No data to analyse');
include 'includes/footer.inc';
}
}
}
$PageSecurity = 15;
include 'includes/session.inc';
$title = _('Recalculation of GL Balances in Chart Details Table');
include 'includes/header.inc';
echo "<FORM METHOD='POST' ACTION=" . $_SERVER['PHP_SELF'] . '?' . SID . '>';
if (!isset($_POST['FromPeriod'])) {
/*Show a form to allow input of criteria for TB to show */
echo '<CENTER><TABLE>
<TR>
<TD>' . _('Select Period From') . ":</TD>\n <TD><SELECT Name='FromPeriod'>";
$sql = 'SELECT periodno,
lastdate_in_period
FROM periods ORDER BY periodno';
$Periods = DB_query($sql, $db);
while ($myrow = DB_fetch_array($Periods, $db)) {
echo '<OPTION VALUE=' . $myrow['periodno'] . '>' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']);
}
echo '</SELECT></TD>
</TR>
</TABLE>';
echo "<INPUT TYPE=SUBMIT Name='recalc' Value='" . _('Do the Recalculation') . "' onclick=\"return confirm('" . _('Are you sure you wish to re-post all general ledger transactions since the selected period .... this can take some time?') . '\');"></CENTER></FORM>';
} else {
/*OK do the updates */
/* Make the posted flag on all GL entries including and after the period selected = 0 */
$sql = 'UPDATE gltrans SET posted=0 WHERE periodno >=' . $_POST['FromPeriod'];
$UpdGLTransPostedFlag = DB_query($sql, $db);
/* Now make all the actuals 0 for all periods including and after the period from */
$sql = 'UPDATE chartdetails SET actual =0 WHERE period >= ' . $_POST['FromPeriod'];
$UpdActualChartDetails = DB_query($sql, $db);
$ChartDetailBFwdResult = DB_query('SELECT accountcode, bfwd FROM chartdetails WHERE period=' . $_POST['FromPeriod'], $db);
while ($ChartRow = DB_fetch_array($ChartDetailBFwdResult)) {
for ($i = 1; $i++; $i <= $NumberOfPeriodsUsage) {
$UsageRow = DB_fetch_array($MovtsResult);
if (!$UsageRow) {
break;
} else {
$UsageArray[] = array(MonthAndYearFromSQLDate($UsageRow['lastdate_in_period']), $UsageRow['qtyused']);
}
}
} else {
$graph->SetDataColors(array("blue", "red"), array("black"));
for ($i = 1; $i++; $i <= 12) {
$UsageRow = DB_fetch_array($MovtsResult);
if (!$UsageRow) {
break;
}
$UsageArray[] = array(MonthAndYearFromSQLDate($UsageRow['lastdate_in_period']), $UsageRow['qtyused']);
}
for ($i = 0, $i++; $i <= 11;) {
$UsageRow = DB_fetch_array($MovtsResult);
if (!$UsageRow) {
break;
}
$UsageArray[$i][0] = MonthAndYearFromSQLDate($UsageRow['lastdate_in_period']);
$UsageArray[$i][2] = $UsageRow['qtyused'];
}
}
//$graph->SetDrawXGrid(TRUE);
$graph->SetDataValues($UsageArray);
$graph->SetDataColors(array("blue", "red"), array("black"));
//Draw it
$graph->DrawGraph();
$graph->SetDataType('text-data');
$SalesResult = DB_query($SQL, $db);
if (DB_error_no($db) != 0) {
prnMsg(_('The sales graph data for the selected criteria could not be retrieved because') . ' - ' . DB_error_msg($db), 'error');
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($SalesResult) == 0) {
prnMsg(_('There is not sales data for the criteria entered to graph'), 'info');
include 'includes/footer.inc';
exit;
}
$GraphArrays = array();
$i = 0;
while ($myrow = DB_fetch_array($SalesResult)) {
$GraphArray[$i] = array(MonthAndYearFromSQLDate($myrow['lastdate_in_period']), $myrow['sales'], $myrow['budget']);
$i++;
}
$graph->SetDataValues($GraphArray);
$graph->SetDataColors(array('grey', 'wheat'), array('black'));
$graph->SetLegend(array(_('Actual'), _('Budget')));
//Draw it
$graph->DrawGraph();
echo '<br /><table class="selection" summary="' . _('Sales Report Graph') . '">
<tr>
<th>' . _('Sales Report Graph') . '
<img src="' . $RootPath . '/css/' . $Theme . '/images/printer.png" class="PrintIcon noPrint" title="' . _('Print') . '" alt="' . _('Print') . '" onclick="window.print();" />
</th>
</tr>
<tr>
<td>
}
}
echo '</select></td></tr>';
// End select tag
echo '<tr>
<td>' . _('For Period range') . ':</td>
<td><select Name=Period[] multiple>';
$sql = 'SELECT periodno, lastdate_in_period FROM periods ORDER BY periodno DESC';
$Periods = DB_query($sql, $db);
$id = 0;
while ($myrow = DB_fetch_array($Periods, $db)) {
if (isset($SelectedPeriod[$id]) and $myrow['periodno'] == $SelectedPeriod[$id]) {
echo '<option selected VALUE=' . $myrow['periodno'] . '>' . _(MonthAndYearFromSQLDate($myrow['lastdate_in_period']));
$id++;
} else {
echo '<option VALUE=' . $myrow['periodno'] . '>' . _(MonthAndYearFromSQLDate($myrow['lastdate_in_period']));
}
}
echo "</select></td>\n </tr>\n</table><p>\n<div class='centre'><input type=submit name='Show' VALUE='" . _('Show Account Transactions') . "'></div></form>";
/* End of the Form rest of script is what happens if the show button is hit*/
if (isset($_POST['Show'])) {
if (!isset($SelectedPeriod)) {
prnMsg(_('A period or range of periods must be selected from the list box'), 'info');
include 'includes/footer.inc';
exit;
}
/*Is the account a balance sheet or a profit and loss account */
$result = DB_query("SELECT pandl\n\t\t\t\tFROM accountgroups\n\t\t\t\tINNER JOIN chartmaster ON accountgroups.groupname=chartmaster.group_\n\t\t\t\tWHERE chartmaster.accountcode={$SelectedAccount}", $db);
$PandLRow = DB_fetch_row($result);
if ($PandLRow[0] == 1) {
$PandLAccount = True;
} elseif (strpos($EndDateSQL, '.')) {
$Date_Array = explode('.', $EndDateSQL);
}
if (strlen($Date_Array[2]) > 4) {
$Date_Array[2] = substr($Date_Array[2], 0, 2);
}
$StartDateSQL = date('Y-m-d', mktime(0, 0, 0, (int) $Date_Array[1], 1, (int) $Date_Array[0]));
$sql = "SELECT \ttrandate,\n\t\t\t\tSUM(price*(1-discountpercent)* (-qty)) as salesvalue,\n\t\t\t\tSUM((standardcost * -qty)) as cost\n\t\t\tFROM stockmoves\n\t\t\t\tINNER JOIN custbranch\n\t\t\t\tON stockmoves.debtorno=custbranch.debtorno\n\t\t\t\t\tAND stockmoves.branchcode=custbranch.branchcode\n\t\t\t\tINNER JOIN stockmaster\n\t\t\t\tON stockmoves.stockid=stockmaster.stockid\n\t\t\t\tINNER JOIN stockcategory\n\t\t\t\tON stockmaster.categoryid=stockcategory.categoryid\n\t\t\tWHERE (stockmoves.type=10 or stockmoves.type=11)\n\t\t\tAND show_on_inv_crds =1\n\t\t\tAND trandate>='" . $StartDateSQL . "'\n\t\t\tAND trandate<='" . $EndDateSQL . "'";
if ($_POST['StockType'] != '') {
$sql .= " AND stockcategory.stocktype='" . $_POST['StockType'] . "'";
}
$sql .= " GROUP BY stockmoves.trandate ORDER BY stockmoves.trandate";
$ErrMsg = _('The sales data could not be retrieved because') . ' - ' . DB_error_msg($db);
$SalesResult = DB_query($sql, $db, $ErrMsg);
echo '<table cellpadding="2" class="selection">';
echo '<tr><th colspan="7"><font color="navy" size="3">' . _('Sales For The Month Of') . ' ' . MonthAndYearFromSQLDate($StartDateSQL) . '</font></th></tr>';
if ($_POST['StockType'] != '') {
echo '<tr><th colspan="7"><font color="navy" size="2">' . _('For sales of type') . ' ' . GetStockType($_POST['StockType']) . '</font></th></tr>';
} else {
echo '<tr><th colspan="7"><font color="navy" size="2">' . _('For all sales') . '</font></th></tr>';
}
echo '<tr>
<th width="14.285714286%">' . _('Sunday') . '</th>
<th width="14.285714286%">' . _('Monday') . '</th>
<th width="14.285714286%">' . _('Tuesday') . '</th>
<th width="14.285714286%">' . _('Wednesday') . '</th>
<th width="14.285714286%">' . _('Thursday') . '</th>
<th width="14.285714286%">' . _('Friday') . '</th>
<th width="14.285714286%">' . _('Saturday') . '</th></tr>';
$CumulativeTotalSales = 0;
$CumulativeTotalCost = 0;
