Menu
▾
▴
[Web-erp-svn] SF.net SVN: web-erp:[7586] trunk
|
From: <te...@us...> - 2016-08-12 08:42:37
|
Revision: 7586
http://sourceforge.net/p/web-erp/reponame/7586
Author: tehonu
Date: 2016-08-12 08:42:34 +0000 (Fri, 12 Aug 2016)
Log Message:
-----------
List of expenses of Petty cash Tab in Excel between dates
Modified Paths:
--------------
trunk/includes/MainMenuLinksArray.php
trunk/sql/mysql/upgrade4.13-4.13.1.sql
Added Paths:
-----------
trunk/PcTabExpensesList.php
Added: trunk/PcTabExpensesList.php
===================================================================
--- trunk/PcTabExpensesList.php (rev 0)
+++ trunk/PcTabExpensesList.php 2016-08-12 08:42:34 UTC (rev 7586)
@@ -0,0 +1,236 @@
+<?php
+require_once ('Classes/PHPExcel.php');
+
+include('includes/session.inc');
+include('includes/SQL_CommonFunctions.inc');
+
+if (isset($_POST['submit'])) {
+ submit($db, $_POST['Tabs'], $_POST['FromDate'], $_POST['ToDate']);
+} else {
+ display($db);
+}
+
+//####_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT####
+function submit(&$db, $TabToShow, $FromDate, $ToDate) {
+
+ //initialise no input errors
+ $InputError = 0;
+
+ //first off validate inputs sensible
+
+ if ($InputError == 0){
+ // Search absic PC Tab information
+ $SQL = "SELECT pctabs.tabcode,
+ pctabs.usercode,
+ pctabs.typetabcode,
+ pctabs.currency,
+ pctabs.tablimit,
+ pctabs.assigner,
+ pctabs.authorizer
+ FROM pctabs
+ WHERE pctabs.tabcode = '" . $TabToShow . "'";
+ $result = DB_query($SQL);
+ $myTab = DB_fetch_array($result);
+
+ $SQL = "SELECT SUM(pcashdetails.amount) AS previous
+ FROM pcashdetails
+ WHERE pcashdetails.tabcode = '" . $TabToShow . "'
+ AND pcashdetails.date < '" . FormatDateForSQL($FromDate) . "'";
+ $result = DB_query($SQL);
+ $myPreviousBalance = DB_fetch_array($result);
+
+ $SQL = "SELECT pcashdetails.date,
+ pcashdetails.codeexpense,
+ pcashdetails.amount,
+ pcashdetails.authorized,
+ pcashdetails.notes,
+ pcashdetails.receipt
+ FROM pcashdetails
+ WHERE pcashdetails.tabcode = '" . $TabToShow . "'
+ AND pcashdetails.date >= '" . FormatDateForSQL($FromDate) . "'
+ AND pcashdetails.date <= '" . FormatDateForSQL($ToDate) . "'
+ ORDER BY pcashdetails.date,
+ pcashdetails.counterindex";
+ $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("PC Tab Expenses List")
+ ->setSubject("PC Tab Expenses List")
+ ->setDescription("PC Tab Expenses List")
+ ->setKeywords("")
+ ->setCategory("");
+
+ // Formatting
+ $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
+ $objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode('dd/mm/yyyy');
+ $objPHPExcel->getActiveSheet()->getStyle('B5')->getNumberFormat()->setFormatCode('#,###');
+ $objPHPExcel->getActiveSheet()->getStyle('C:D')->getNumberFormat()->setFormatCode('#,###');
+ $objPHPExcel->getActiveSheet()->getStyle('E1:E2')->getNumberFormat()->setFormatCode('dd/mm/yyyy');
+ $objPHPExcel->getActiveSheet()->getStyle('G')->getNumberFormat()->setFormatCode('dd/mm/yyyy');
+
+ // Add title data
+ $objPHPExcel->setActiveSheetIndex(0);
+ $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Tab Code');
+ $objPHPExcel->getActiveSheet()->setCellValue('B1', $myTab['tabcode']);
+ $objPHPExcel->getActiveSheet()->setCellValue('A2', 'User Code');
+ $objPHPExcel->getActiveSheet()->setCellValue('B2', $myTab['usercode']);
+ $objPHPExcel->getActiveSheet()->setCellValue('A3', 'Type of Tab');
+ $objPHPExcel->getActiveSheet()->setCellValue('B3', $myTab['typetabcode']);
+ $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Currency');
+ $objPHPExcel->getActiveSheet()->setCellValue('B4', $myTab['currency']);
+ $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Limit');
+ $objPHPExcel->getActiveSheet()->setCellValue('B5', $myTab['tablimit']);
+ $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Assigner');
+ $objPHPExcel->getActiveSheet()->setCellValue('B6', $myTab['assigner']);
+ $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Authorizer');
+ $objPHPExcel->getActiveSheet()->setCellValue('B7', $myTab['authorizer']);
+
+ $objPHPExcel->getActiveSheet()->setCellValue('D1', 'From');
+ $objPHPExcel->getActiveSheet()->setCellValue('E1', $FromDate);
+ $objPHPExcel->getActiveSheet()->setCellValue('D2', 'To');
+ $objPHPExcel->getActiveSheet()->setCellValue('E2', $ToDate);
+
+ $objPHPExcel->getActiveSheet()->setCellValue('A9', 'Date');
+ $objPHPExcel->getActiveSheet()->setCellValue('B9', 'Expense Code');
+ $objPHPExcel->getActiveSheet()->setCellValue('C9', 'Amount');
+ $objPHPExcel->getActiveSheet()->setCellValue('D9', 'Balance');
+ $objPHPExcel->getActiveSheet()->setCellValue('E9', 'Notes');
+ $objPHPExcel->getActiveSheet()->setCellValue('F9', 'Receipt');
+ $objPHPExcel->getActiveSheet()->setCellValue('G9', 'Authorized');
+
+ $objPHPExcel->getActiveSheet()->setCellValue('B10', 'Previous Balance');
+ $objPHPExcel->getActiveSheet()->setCellValue('D10', $myPreviousBalance['previous']);
+
+ // Add data
+ $i = 11;
+ while ($myrow = DB_fetch_array($result)) {
+
+ $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, ConvertSQLDate($myrow['date']));
+ $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $myrow['codeexpense']);
+ $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $myrow['amount']);
+ $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, '=D'.($i-1).'+C'.$i.'');
+ $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $myrow['notes']);
+ $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $myrow['receipt']);
+ $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, ConvertSQLDate($myrow['authorized']));
+
+ $i++;
+ }
+
+ // Freeze panes
+ $objPHPExcel->getActiveSheet()->freezePane('A10');
+
+ // Auto Size columns
+ foreach(range('A','G') as $columnID) {
+ $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
+ ->setAutoSize(true);
+ }
+
+ // Rename worksheet
+ $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\x92s web browser (Excel2007)
+ header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
+ $File = 'ExpensesList-' . $TabToShow. '.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 Tab Expenses List');
+ include('includes/header.inc');
+ prnMsg('No data to analyse');
+ include('includes/footer.inc');
+ }
+ }
+} // End of function submit()
+
+
+function display(&$db) //####DISPLAY_DISPLAY_DISPLAY_DISPLAY_DISPLAY_DISPLAY_#####
+{
+// Display form fields. This function is called the first time
+// the page is called.
+ $Title = _('Excel file for Petty Cash Tab Expenses List');
+
+ include('includes/header.inc');
+
+ echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">
+ <div>
+ <br/>';
+ echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
+
+ echo '<p class="page_title_text">
+ <img src="' . $RootPath . '/css/' . $Theme . '/images/magnifier.png" title="' . _('Excel file for Petty Cash Tab Expenses List') . '" alt="" />' . ' ' . _('Excel file for Petty Cash Tab Expenses List') . '
+ </p>';
+
+ # Sets default date range for current month
+ if (!isset($_POST['FromDate'])){
+ $_POST['FromDate']=Date($_SESSION['DefaultDateFormat'], mktime(0,0,0,Date('m'),1,Date('Y')));
+ }
+ if (!isset($_POST['ToDate'])){
+ $_POST['ToDate'] = Date($_SESSION['DefaultDateFormat']);
+ }
+
+ echo '<table class="selection">
+ <tr>
+ <td>' . _('For Petty Cash Tab') . ':</td>
+ <td><select name="Tabs">';
+
+ $sql = "SELECT tabcode
+ FROM pctabs
+ ORDER BY tabcode";
+ $CatResult=DB_query($sql);
+
+ while ($myrow=DB_fetch_array($CatResult)){
+ echo '<option value="' . $myrow['tabcode'] . '">' . $myrow['tabcode'] . '</option>';
+ }
+ echo '</select>
+ </td>
+ </tr>';
+
+ echo '<tr>
+ <td>' . _('Date Range') . ':</td>
+ <td><input type="text" class="date" alt="' .$_SESSION['DefaultDateFormat'] .'" name="FromDate" size="10" maxlength="10" value="' . $_POST['FromDate'] . '" />
+ ' . _('To') . ':<input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="ToDate" size="10" maxlength="10" value="' . $_POST['ToDate'] . '" /></td>
+ </tr>';
+
+ echo '
+ <tr><td> </td></tr>
+ <tr>
+ <td> </td>
+ <td><input type="submit" name="submit" value="' . _('Create Petty Cash Tab Expenses List Excel File') . '" /></td>
+ </tr>
+ </table>
+ <br />';
+ echo '</div>
+ </form>';
+ include('includes/footer.inc');
+
+} // End of function display()
+
+function beginning_of_month($date){
+ $date2 = explode("-",$date);
+ $m = $date2[1];
+ $y = $date2[0];
+ $first_of_month = $y . '-' . $m . '-01';
+ return $first_of_month;
+}
+
+?>
\ No newline at end of file
Modified: trunk/includes/MainMenuLinksArray.php
===================================================================
--- trunk/includes/MainMenuLinksArray.php 2016-08-11 09:43:28 UTC (rev 7585)
+++ trunk/includes/MainMenuLinksArray.php 2016-08-12 08:42:34 UTC (rev 7586)
@@ -454,9 +454,11 @@
'/PcAuthorizeExpenses.php');
$MenuItems['PC']['Reports']['Caption'] = array(_('PC Tab General Report'),
+ _('PC Tab Expenses List'),
_('PC Expenses Analysis'));
$MenuItems['PC']['Reports']['URL'] = array('/PcReportTab.php',
+ '/PcTabExpensesList.php',
'/PcAnalysis.php');
$MenuItems['PC']['Maintenance']['Caption'] = array( _('Types of PC Tabs'),
Modified: trunk/sql/mysql/upgrade4.13-4.13.1.sql
===================================================================
--- trunk/sql/mysql/upgrade4.13-4.13.1.sql 2016-08-11 09:43:28 UTC (rev 7585)
+++ trunk/sql/mysql/upgrade4.13-4.13.1.sql 2016-08-12 08:42:34 UTC (rev 7586)
@@ -3,5 +3,5 @@
INSERT INTO securitytokens VALUES (19,'Internal stock request fully access authority');
INSERT INTO scripts VALUES ('PDFGLJournalCN.php',1,'Print GL Journal Chinese version');
ALTER table custcontacts ADD statement tinyint(4) NOT NULL DEFAULT 0;
+INSERT INTO scripts VALUES ('PcTabExpensesList.php', '15', 'Creates excel with all movements of tab between dates');
-
|
