1<?php
2
3require_once ('Classes/PHPExcel.php');
4
5include('includes/session.php');
6include('includes/SQL_CommonFunctions.inc');
7
8if (isset($_POST['submit'])) {
9    submit($_POST['Tabs'], $_POST['FromDate'], $_POST['ToDate']);
10} else {
11    display();
12}
13
14//####_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT####
15function submit($TabToShow, $FromDate, $ToDate) {
16
17	//initialise no input errors
18	$InputError = 0;
19
20	//first off validate inputs sensible
21
22	if ($InputError == 0){
23		// Search absic PC Tab information
24		$SQL = "SELECT pctabs.tabcode,
25					   pctabs.usercode,
26					   pctabs.typetabcode,
27					   pctabs.currency,
28					   pctabs.tablimit,
29					   pctabs.assigner,
30					   pctabs.authorizer,
31					   pctabs.authorizerexpenses
32				FROM  pctabs
33				WHERE pctabs.tabcode = '" . $TabToShow . "'";
34		$Result = DB_query($SQL);
35		$MyTab = DB_fetch_array($Result);
36
37		$SQL = "SELECT SUM(pcashdetails.amount) AS previous
38				FROM  pcashdetails
39				WHERE pcashdetails.tabcode = '" . $TabToShow . "'
40					AND pcashdetails.date < '" . FormatDateForSQL($FromDate) . "'";
41		$Result = DB_query($SQL);
42		$MyPreviousBalance = DB_fetch_array($Result);
43
44		$SQL = "SELECT counterindex,
45						tabcode,
46						tag,
47						date,
48						codeexpense,
49						amount,
50						authorized,
51						posted,
52						purpose,
53						notes,
54						receipt
55				FROM  pcashdetails
56				WHERE pcashdetails.tabcode = '" . $TabToShow . "'
57					AND pcashdetails.date >= '" . FormatDateForSQL($FromDate) . "'
58					AND pcashdetails.date <= '" . FormatDateForSQL($ToDate) . "'
59				ORDER BY pcashdetails.date,
60					pcashdetails.counterindex";
61		$Result = DB_query($SQL);
62
63		if (DB_num_rows($Result) != 0){
64
65			// Create new PHPExcel object
66			$objPHPExcel = new PHPExcel();
67
68			// Set document properties
69			$objPHPExcel->getProperties()->setCreator("webERP")
70										 ->setLastModifiedBy("webERP")
71										 ->setTitle("PC Tab Expenses List")
72										 ->setSubject("PC Tab Expenses List")
73										 ->setDescription("PC Tab Expenses List")
74										 ->setKeywords("")
75										 ->setCategory("");
76
77			// Formatting
78
79			$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
80			$objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode('dd/mm/yyyy');
81			$objPHPExcel->getActiveSheet()->getStyle('B5')->getNumberFormat()->setFormatCode('#,##0.00');
82			$objPHPExcel->getActiveSheet()->getStyle('C:E')->getNumberFormat()->setFormatCode('#,##0.00');
83			$objPHPExcel->getActiveSheet()->getStyle('E1:E2')->getNumberFormat()->setFormatCode('dd/mm/yyyy');
84			$objPHPExcel->getActiveSheet()->getStyle('J')->getNumberFormat()->setFormatCode('dd/mm/yyyy');
85			$objPHPExcel->getActiveSheet()->getStyle('A:J')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
86			$objPHPExcel->getActiveSheet()->getStyle('10')->getFont()->setBold(true);
87			$objPHPExcel->getActiveSheet()->getStyle('A1:A8')->getFont()->setBold(true);
88			$objPHPExcel->getActiveSheet()->getStyle('D1:D2')->getFont()->setBold(true);
89
90			// Add title data
91			$objPHPExcel->setActiveSheetIndex(0);
92			$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Tab Code');
93			$objPHPExcel->getActiveSheet()->setCellValue('B1', $MyTab['tabcode']);
94			$objPHPExcel->getActiveSheet()->setCellValue('A2', 'User Code');
95			$objPHPExcel->getActiveSheet()->setCellValue('B2', $MyTab['usercode']);
96			$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Type of Tab');
97			$objPHPExcel->getActiveSheet()->setCellValue('B3', $MyTab['typetabcode']);
98			$objPHPExcel->getActiveSheet()->setCellValue('A4', 'Currency');
99			$objPHPExcel->getActiveSheet()->setCellValue('B4', $MyTab['currency']);
100			$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Limit');
101			$objPHPExcel->getActiveSheet()->setCellValue('B5', $MyTab['tablimit']);
102			$objPHPExcel->getActiveSheet()->setCellValue('A6', 'Cash Assigner');
103			$objPHPExcel->getActiveSheet()->setCellValue('B6', $MyTab['assigner']);
104			$objPHPExcel->getActiveSheet()->setCellValue('A7', 'Authorizer - Cash');
105			$objPHPExcel->getActiveSheet()->setCellValue('B7', $MyTab['authorizer']);
106			$objPHPExcel->getActiveSheet()->setCellValue('A8', 'Authorizer - Expenses');
107			$objPHPExcel->getActiveSheet()->setCellValue('B8', $MyTab['authorizerexpenses']);
108
109			$objPHPExcel->getActiveSheet()->setCellValue('D1', 'From');
110			$objPHPExcel->getActiveSheet()->setCellValue('E1', $FromDate);
111			$objPHPExcel->getActiveSheet()->setCellValue('D2', 'To');
112			$objPHPExcel->getActiveSheet()->setCellValue('E2', $ToDate);
113
114			$objPHPExcel->getActiveSheet()->setCellValue('A10', 'Date');
115			$objPHPExcel->getActiveSheet()->setCellValue('B10', 'Expense Code');
116			$objPHPExcel->getActiveSheet()->setCellValue('C10', 'Gross Amount');
117			$objPHPExcel->getActiveSheet()->setCellValue('D10', 'Balance');
118			$objPHPExcel->getActiveSheet()->setCellValue('E10', 'Tax');
119			$objPHPExcel->getActiveSheet()->setCellValue('F10', 'Tax Group');
120			$objPHPExcel->getActiveSheet()->setCellValue('G10', 'Tag');
121			$objPHPExcel->getActiveSheet()->setCellValue('H10', 'Business Purpose');
122			$objPHPExcel->getActiveSheet()->setCellValue('I10', 'Notes');
123			$objPHPExcel->getActiveSheet()->setCellValue('J10', 'Receipt Attachment');
124			$objPHPExcel->getActiveSheet()->setCellValue('K10', 'Date Authorized');
125
126			$objPHPExcel->getActiveSheet()->setCellValue('B11', 'Previous Balance');
127			$objPHPExcel->getActiveSheet()->setCellValue('D11', $MyPreviousBalance['previous']);
128
129			// Add data
130			$i = 12;
131			while ($MyRow = DB_fetch_array($Result)) {
132
133				$SQLDes = "SELECT description
134							FROM pcexpenses
135							WHERE codeexpense = '" . $MyRow['codeexpense'] . "'";
136				$ResultDes = DB_query($SQLDes);
137				$Description=DB_fetch_array($ResultDes);
138				if (!isset($Description[0])) {
139						$ExpenseCodeDes = 'ASSIGNCASH';
140				} else {
141						$ExpenseCodeDes = $MyRow['codeexpense'] . ' - ' . $Description[0];
142				}
143
144				$TagSQL = "SELECT tagdescription FROM tags WHERE tagref='" . $MyRow['tag'] . "'";
145				$TagResult = DB_query($TagSQL);
146				$TagRow = DB_fetch_array($TagResult);
147				if ($MyRow['tag'] == 0) {
148					$TagRow['tagdescription'] = _('None');
149				}
150				$TagTo = $MyRow['tag'];
151				$TagDescription = $TagTo . ' - ' . $TagRow['tagdescription'];
152
153				$TaxesDescription = '';
154				$TaxesTaxAmount = '';
155				$TaxSQL = "SELECT counterindex,
156									pccashdetail,
157									calculationorder,
158									description,
159									taxauthid,
160									purchtaxglaccount,
161									taxontax,
162									taxrate,
163									amount
164								FROM pcashdetailtaxes
165								WHERE pccashdetail='" . $MyRow['counterindex'] . "'";
166				$TaxResult = DB_query($TaxSQL);
167				while ($MyTaxRow = DB_fetch_array($TaxResult)) {
168					$TaxesDescription .= $MyTaxRow['description'];
169					$TaxesTaxAmount .= locale_number_format($MyTaxRow['amount'], $CurrDecimalPlaces);
170				}
171
172				//Generate download link for expense receipt, or show text if no receipt file is found.
173				$ReceiptSupportedExt = array('png','jpg','jpeg','pdf','doc','docx','xls','xlsx'); //Supported file extensions
174				$ReceiptDir = $PathPrefix . 'companies/' . $_SESSION['DatabaseName'] . '/expenses_receipts/'; //Receipts upload directory
175				$ReceiptSQL = "SELECT hashfile,
176										extension
177										FROM pcreceipts
178										WHERE pccashdetail='" . $MyRow['counterindex'] . "'";
179				$ReceiptResult = DB_query($ReceiptSQL);
180				$ReceiptRow = DB_fetch_array($ReceiptResult);
181				if (DB_num_rows($ReceiptResult) > 0) { //If receipt exists in database
182					$ReceiptHash = $ReceiptRow['hashfile'];
183					$ReceiptExt = $ReceiptRow['extension'];
184					$ReceiptFileName = $ReceiptHash . '.' . $ReceiptExt;
185					$ReceiptPath = $ReceiptDir . $ReceiptFileName;
186					$ReceiptText = _('Open Attachment');
187					$ReceiptURL = htmlspecialchars($_SERVER['REQUEST_SCHEME'] . '://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . '/' . $ReceiptPath, ENT_QUOTES, 'UTF-8');
188				} elseif ($ExpenseCodeDes == 'ASSIGNCASH') {
189				$ReceiptText = '';
190				} else {
191				$ReceiptText = _('No attachment');
192				}
193
194				if ($MyRow['authorized'] == '0000-00-00') {
195					$AuthorisedDate = _('Unauthorised');
196				} else {
197					$AuthorisedDate = ConvertSQLDate($MyRow['authorized']);
198				}
199
200				$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, ConvertSQLDate($MyRow['date']));
201				$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $ExpenseCodeDes);
202				$objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $MyRow['amount']);
203				$objPHPExcel->getActiveSheet()->setCellValue('D'.$i, '=D'.($i-1).'+C'.$i.'');
204				$objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $TaxesTaxAmount);
205				$objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $TaxesDescription);
206				$objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $TagDescription);
207				$objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $MyRow['purpose']);
208				$objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $MyRow['notes']);
209				$objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $ReceiptText);
210				if (isset($ReceiptURL)) {
211					$objPHPExcel->getActiveSheet()->getCell('J'.$i)->getHyperlink()->setUrl($ReceiptURL);
212					$objPHPExcel->getActiveSheet()->getStyle('J'.$i)->applyFromArray(array( 'font' => array( 'color' => ['rgb' => '0000FF'], 'underline' => 'single' )));
213				}
214				$objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $AuthorisedDate);
215
216				$i++;
217			}
218
219			// Freeze panes
220			$objPHPExcel->getActiveSheet()->freezePane('A11');
221
222			// Auto Size columns
223			foreach(range('A','K') as $columnID) {
224				$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
225					->setAutoSize(true);
226			}
227
228			// Rename worksheet
229			$objPHPExcel->getActiveSheet()->setTitle($TabToShow);
230			// Set active sheet index to the first sheet, so Excel opens this as the first sheet
231			$objPHPExcel->setActiveSheetIndex(0);
232
233			// Redirect output to a client�s web browser (Excel2007)
234			header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
235			$File = 'ExpensesList-' . $TabToShow. '.xlsx';
236			header('Content-Disposition: attachment;filename="' . $File . '"');
237			header('Cache-Control: max-age=0');
238			// If you're serving to IE 9, then the following may be needed
239			header('Cache-Control: max-age=1');
240
241			// If you're serving to IE over SSL, then the following may be needed
242			header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
243			header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
244			header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
245			header ('Pragma: public'); // HTTP/1.0
246
247			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
248			$objWriter->save('php://output');
249
250		}else{
251			$Title = _('Excel file for Petty Cash Tab Expenses List');
252			include('includes/header.php');
253			prnMsg('There is no data to analyse');
254			include('includes/footer.php');
255		}
256	}
257} // End of function submit()
258
259
260function display()  //####DISPLAY_DISPLAY_DISPLAY_DISPLAY_DISPLAY_DISPLAY_#####
261{
262// Display form fields. This function is called the first time
263// the page is called.
264	$Title = _('Excel file for Petty Cash Tab Expenses List');
265	$ViewTopic = 'PettyCash';// Filename's id in ManualContents.php's TOC.
266	$BookMark = 'top';// Anchor's id in the manual's html document.
267
268	include('includes/header.php');
269
270	echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">
271          <div>
272			<br/>';
273	echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
274
275	echo '<p class="page_title_text">
276			<img src="' . $RootPath . '/css/' . $_SESSION['Theme'] . '/images/magnifier.png" title="' . _('Excel file for Petty Cash Tab Expenses List') . '" alt="" />' . ' ' . _('Excel file for Petty Cash Tab Expenses List') . '
277		</p>';
278
279	# Sets default date range for current month
280	if (!isset($_POST['FromDate'])){
281		$_POST['FromDate'] = Date($_SESSION['DefaultDateFormat'], mktime(0,0,0,Date('m'),1,Date('Y')));
282	}
283	if (!isset($_POST['ToDate'])){
284		$_POST['ToDate'] = Date($_SESSION['DefaultDateFormat']);
285	}
286
287	echo '<table class="selection">
288		<tr>
289		<td>' . _('For Petty Cash Tab') . ':</td>
290		<td><select name="Tabs">';
291
292	$SQL = "SELECT tabcode
293			FROM pctabs
294			ORDER BY tabcode";
295	$CatResult = DB_query($SQL);
296
297	while ($MyRow = DB_fetch_array($CatResult)){
298		echo '<option value="' . $MyRow['tabcode'] . '">' . $MyRow['tabcode'] . '</option>';
299	}
300	echo '</select>
301			</td>
302		</tr>';
303
304	echo '<tr>
305			<td>' . _('Date Range') . ':</td>
306			<td><input type="text" class="date" name="FromDate" size="11" maxlength="10" value="' . $_POST['FromDate'] . '" />
307			' . _('To') . ':<input type="text" class="date" name="ToDate" size="11" maxlength="10" value="' . $_POST['ToDate'] . '" /></td>
308		</tr>';
309
310	echo '
311		<tr><td>&nbsp;</td></tr>
312		<tr>
313			<td>&nbsp;</td>
314			<td><input type="submit" name="submit" value="' . _('Create Petty Cash Tab Expenses List Excel File') . '" /></td>
315		</tr>
316		</table>
317		<br />';
318	echo '</div>
319         </form>';
320	include('includes/footer.php');
321
322} // End of function display()
323
324function beginning_of_month($Date){
325	$Date2 = explode("-",$Date);
326	$M = $Date2[1];
327	$Y = $Date2[0];
328	$FirstOfMonth = $Y . '-' . $M . '-01';
329	return $FirstOfMonth;
330}
331
332?>