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> </td></tr> 312 <tr> 313 <td> </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?>