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