reporting/includes/excel_report.inc

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12 include_once($path_to_root . "/reporting/includes/Workbook.php");
  13 include_once($path_to_root . "/admin/db/company_db.inc");
  14 include_once($path_to_root . "/admin/db/fiscalyears_db.inc");
  15 include_once($path_to_root . "/config.php");
  16 define('MAX_ROW_SHEET', 65530);
  17 // xls version
  18 class FrontReport extends Spreadsheet_Excel_Writer_Workbook
  19 {
  20         var $size;
  21         var $company;
  22         var $user;
  23         var $host;
  24         var $fiscal_year;
  25         var $title;
  26         var $filename;
  27         var $unique_name;
  28         var $path;
  29         var $code;
  30         var $bottomMargin = 0;
  31         var $lineHeight;
  32         var $leftMargin = 0;
  33         var $pageWidth;         // dummy
  34         var $rightMargin;       // dummy
  35
  36         var $cols;
  37         var $params;
  38         var $headers;
  39         var $aligns;
  40         var $headers2;
  41         var $aligns2;
  42         var $cols2;
  43         var $fontSize;
  44         var $oldFontSize;
  45         var $currency;
  46         var $row = 9999999;
  47         var $y;
  48         var $numcols;
  49         var $excelColWidthFactor;
  50         var $endLine;
  51
  52         var $formatTitle;
  53         var $formatDateTime;
  54         var $formatDate;
  55         var $formatHeaderLeft;
  56         var $formatHeaderRight;
  57         var $formatFooter;
  58         var $formatAmount = array();
  59         var $formatRight;
  60         var $formatLeft;
  61         var $formatTopHeaderLeft;
  62         var $formatBottomHeaderLeft;
  63         var $formatTopHeaderRight;
  64         var $formatBottomHeaderRight;
  65         var $headerFunc;
  66
  67         var $sheet;
  68
  69         function __construct($title, $filename, $size = 'A4', $fontsize = 9, $orientation = 'P', $margins = NULL, $excelColWidthFactor = 6.5)
  70         {
  71                 global $SysPrefs, $page_security;
  72
  73                 if (!$_SESSION["wa_current_user"]->can_access_page($page_security))
  74                 {
  75                         display_error(_("The security settings on your account do not permit you to print this report"));
  76                         end_page();
  77                         exit;
  78                 }
  79                 $this->size = $size;
  80                 $this->title = $title;
  81                 $this->lineHeight = 12;
  82                 $this->endLine = 760;
  83                 $this->SetFontSize($fontsize);
  84                 $this->oldFontSize = 0;
  85                 $this->y = 0;
  86                 $this->currency = '';
  87                 $this->excelColWidthFactor = $excelColWidthFactor;
  88                 $rtl = ($_SESSION['language']->dir == 'rtl');
  89                 $this->code = strtolower($_SESSION['language']->encoding);
  90                 $this->filename = $filename.".xls";
  91                 $this->unique_name = random_id().".xls";
  92                 $this->path = company_path(). '/pdf_files';
  93                 parent::__construct($this->path."/".$this->unique_name);
  94                 if ($this->code != "iso-8859-1")
  95                         $this->setVersion(8); // set biff version to 8 (0x0006 internal)
  96                 $this->sheet = $this->addWorksheet($this->worksheetNameGenerator($this->title));
  97                 if ($this->code != "iso-8859-1")
  98                         $this->sheet->setInputEncoding($this->code); // set sheet encoding
  99                 if ($rtl)
 100                         $this->sheet->setRTL();
 101                 $this->formatTitle =& $this->addFormat();
 102                 $this->formatTitle->setSize(16);
 103                 $this->formatTitle->setBold();
 104                 $this->formatTitle->setAlign($rtl ? 'right' : 'left');
 105                 $this->formatTitle->setTop(2);
 106                 $this->formatTitle->setTopColor('gray');
 107
 108                 $how = user_date_format();
 109                 $sep = $SysPrefs->dateseps[user_date_sep()];
 110                 if ($sep == '.')
 111                         $sep = "\\.";
 112                 if ($how == 0)
 113                 {
 114                         $dateformat_long = "mm{$sep}dd{$sep}yyyy\ \ hh:mm\ am/pm";
 115                         $dateformat = "mm{$sep}dd{$sep}yyyy";
 116                 }
 117                 elseif ($how == 1)
 118                 {
 119                         $dateformat_long = "dd{$sep}mm{$sep}yyyy\ \ hh:mm";
 120                         $dateformat = "dd{$sep}mm{$sep}yyyy";
 121                 }
 122                 else
 123                 {
 124                         $dateformat_long = "yyyy{$sep}mm{$sep}dd\ \ hh:mm";
 125                         $dateformat = "yyyy{$sep}mm{$sep}dd";
 126                 }
 127                 $this->formatDateTime =& $this->addFormat();
 128                 $this->formatDateTime->setNumFormat($dateformat_long);
 129                 $this->formatDateTime->setAlign($rtl ? 'right' : 'left');
 130                 $this->formatDate =& $this->addFormat();
 131                 $this->formatDate->setNumFormat($dateformat);
 132                 $this->formatDate->setAlign($rtl ? 'right' : 'left');
 133                 $this->formatRight =& $this->addFormat();
 134                 $this->formatRight->setAlign($rtl ? 'left' : 'right');
 135                 $this->formatLeft =& $this->addFormat();
 136                 $this->formatLeft->setAlign($rtl ? 'right' : 'left');
 137
 138                 $this->formatHeaderLeft =& $this->addFormat();
 139                 $this->formatHeaderLeft->setItalic();
 140                 $this->formatHeaderLeft->setTop(2);
 141                 $this->formatHeaderLeft->setTopColor('gray');
 142                 $this->formatHeaderLeft->setBottom(2);
 143                 $this->formatHeaderLeft->setBottomColor('gray');
 144                 $this->formatHeaderLeft->setAlign('vcenter');
 145                 $this->formatTopHeaderLeft =& $this->addFormat();
 146                 $this->formatTopHeaderLeft->setItalic();
 147                 $this->formatTopHeaderLeft->setTop(2);
 148                 $this->formatTopHeaderLeft->setTopColor('gray');
 149                 $this->formatTopHeaderLeft->setAlign('vcenter');
 150                 $this->formatBottomHeaderLeft =& $this->addFormat();
 151                 $this->formatBottomHeaderLeft->setItalic();
 152                 $this->formatBottomHeaderLeft->setBottom(2);
 153                 $this->formatBottomHeaderLeft->setBottomColor('gray');
 154                 $this->formatBottomHeaderLeft->setAlign('vcenter');
 155                 $this->formatDate->setAlign($rtl ? 'right' : 'left');
 156                 $this->formatHeaderRight =& $this->addFormat();
 157                 $this->formatHeaderRight->setItalic();
 158                 $this->formatHeaderRight->setTop(2);
 159                 $this->formatHeaderRight->setTopColor('gray');
 160                 $this->formatHeaderRight->setBottom(2);
 161                 $this->formatHeaderRight->setBottomColor('gray');
 162                 $this->formatHeaderRight->setAlign('vcenter');
 163                 $this->formatHeaderRight->setAlign('right');
 164                 $this->formatTopHeaderRight =& $this->addFormat();
 165                 $this->formatTopHeaderRight->setItalic();
 166                 $this->formatTopHeaderRight->setTop(2);
 167                 $this->formatTopHeaderRight->setTopColor('gray');
 168                 $this->formatTopHeaderRight->setAlign('vcenter');
 169                 $this->formatTopHeaderRight->setAlign('right');
 170                 $this->formatBottomHeaderRight =& $this->addFormat();
 171                 $this->formatBottomHeaderRight->setItalic();
 172                 $this->formatBottomHeaderRight->setBottom(2);
 173                 $this->formatBottomHeaderRight->setBottomColor('gray');
 174                 $this->formatBottomHeaderRight->setAlign('vcenter');
 175                 $this->formatBottomHeaderRight->setAlign('right');
 176                 $this->formatFooter =& $this->addFormat();
 177                 $this->formatFooter->setTop(2);
 178                 $this->formatFooter->setTopColor('gray');
 179                 $this->SetHeaderType("header");
 180         }
 181
 182
 183         /*
 184                 Set header handler
 185         */
 186         function SetHeaderType($name) {
 187                 $this->headerFunc = $name;
 188         }
 189         // Check a given name to see if it's a valid Excel worksheet name,
 190         // and fix if necessary
 191         function worksheetNameGenerator($name)
 192         {
 193                 // First, strip out characters which aren't allowed
 194                 $illegal_chars = array(':', '\\', '/', '?', '*', '[', ']');
 195                 for ($i = 0; $i < count($illegal_chars); $i++)
 196                         $name = str_replace($illegal_chars[$i], '', $name);
 197                 // Now, if name is longer than 31 chars, truncate it
 198                 if (strlen($name) > 31)
 199                         $name = substr($name, 0, 31);
 200                 return $name;
 201         }
 202
 203         function NumFormat($dec)
 204         {
 205                 if (!isset($this->formatAmount[$dec]))
 206                 {
 207                         $dec = (int)$dec;
 208                         $tsep = ',';
 209                         $dsep = '.';
 210                         $format = "###{$tsep}###{$tsep}###{$tsep}##0";
 211                         if ($dec>0)
 212                                 $format .= "{$dsep}".str_repeat('0',$dec);
 213                         $this->formatAmount[$dec] =& $this->addFormat();
 214                         $this->formatAmount[$dec]->setNumFormat($format);
 215                         $this->formatAmount[$dec]->setAlign('right');
 216                 }
 217                 return $this->formatAmount[$dec];
 218         }
 219
 220         function Font($fontname = '', $style = 'normal')
 221         {
 222         }
 223
 224         function SetFontSize($size)
 225         {
 226                 $this->fontSize = $size;
 227         }
 228
 229         function Info($params, $cols, $headers, $aligns,
 230                 $cols2 = null, $headers2 = null, $aligns2 = null)
 231         {
 232                 $this->company = get_company_prefs();
 233                 $year = get_current_fiscalyear();
 234                 if ($year['closed'] == 0)
 235                         $how = _("Active");
 236                 else
 237                         $how = _("Closed");
 238                 $this->fiscal_year = sql2date($year['begin']) . " - " . sql2date($year['end']) . "  " . "(" . $how . ")";
 239                 $this->user = $_SESSION["wa_current_user"]->name;
 240                 $this->host = $_SERVER['SERVER_NAME'];
 241                 $this->params = $params;
 242                 $this->cols = $cols;
 243                 $this->headers = $headers;
 244                 $this->aligns = $aligns;
 245                 $this->cols2 = $cols2;
 246                 $this->headers2 = $headers2;
 247                 $this->aligns2 = $aligns2;
 248                 $this->numcols = count($this->headers);
 249                 $tcols = count_array($this->headers2);
 250                 if ($tcols > $this->numcols)
 251                         $this->numcols = $tcols;
 252                 for ($i = 0; $i < $this->numcols; $i++)
 253                         $this->sheet->setColumn($i, $i, $this->px2units($this->cols[$i + 1] - $this->cols[$i]));
 254         }
 255
 256         function Header()
 257         {
 258                 $tcol = $this->numcols - 1;
 259                 $this->sheet->setRow($this->y, 20);
 260                 for ($i = 0; $i < $this->numcols; $i++)
 261                         $this->sheet->writeBlank($this->y, $i, $this->formatTitle);
 262                 $this->sheet->writeString($this->y, 0, $this->title, $this->formatTitle);
 263                 $this->sheet->mergeCells($this->y, 0, $this->y, $tcol);
 264                 $this->NewLine();
 265                 $str = _("Print Out Date") . ':';
 266                 $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 267                 $this->sheet->writeString($this->y, 1, Today() . "  ".Now(), $this->formatLeft);
 268                 $this->sheet->writeString($this->y, $tcol-1, $this->company['coy_name'], $this->formatLeft);
 269                 $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol);
 270                 $this->NewLine();
 271                 $str = _("Fiscal Year") . ':';
 272                 $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 273                 $str = $this->fiscal_year;
 274                 $this->sheet->writeString($this->y, 1, $str, $this->formatLeft);
 275                 $this->sheet->writeString($this->y, $tcol-1, $this->host, $this->formatLeft);
 276                 $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol);
 277                 for ($i = 1; $i < count($this->params); $i++)
 278                 {
 279                         if ($this->params[$i]['from'] != '')
 280                         {
 281                                 $this->NewLine();
 282                                 $str = $this->params[$i]['text'] . ':';
 283                                 $this->sheet->writeString($this->y, 0, $str);
 284                                 $str = $this->params[$i]['from'];
 285                                 if ($this->params[$i]['to'] != '')
 286                                         $str .= " - " . $this->params[$i]['to'];
 287                                 $this->sheet->writeString($this->y, 1, $str, $this->formatLeft);
 288                                 if ($i == 1)
 289                                 {
 290                                         $this->sheet->writeString($this->y, $tcol-1, $this->user, $this->formatLeft);
 291                                         $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol);
 292                                 }
 293                         }
 294                 }
 295                 if ($this->params[0] != '') // Comments
 296                 {
 297                         $this->NewLine();
 298                         $str = _("Comments") . ':';
 299                         $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 300                         $this->sheet->writeString($this->y, 1, $this->params[0], $this->formatLeft);
 301                 }
 302                 $this->NewLine();
 303                 if ($this->headers2 != null)
 304                 {
 305                         for ($i = 0, $j = 0; $i < $this->numcols; $i++)
 306                         {
 307                                 if ($this->cols2[$j] >= $this->cols[$i] && $this->cols2[$j] <= $this->cols[$i + 1])
 308                                 {
 309                                         if ($this->aligns2[$j] == "right")
 310                                                 $this->sheet->writeString($this->y, $i, $this->headers2[$j], $this->formatHeaderRight);
 311                                         else
 312                                                 $this->sheet->writeString($this->y, $i, $this->headers2[$j], $this->formatHeaderLeft);
 313                                         $j++;
 314                                 }
 315                                 else
 316                                         $this->sheet->writeString($this->y, $i, "", $this->formatHeaderLeft);
 317                         }
 318                         $this->NewLine();
 319                 }
 320
 321                 for ($i = 0; $i < $this->numcols; $i++)
 322                 {
 323                         if (!isset($this->headers[$i]))
 324                                 $header = "";
 325                         else
 326                                 $header = $this->headers[$i];
 327                         if ($this->aligns[$i] == "right")
 328                                 $this->sheet->writeString($this->y, $i, $header, $this->formatHeaderRight);
 329                         else
 330                                 $this->sheet->writeString($this->y, $i, $header, $this->formatHeaderLeft);
 331                 }
 332                 $this->NewLine();
 333         }
 334
 335         function Header2($myrow, $branch, $sales_order, $bankaccount, $doctype)
 336         {
 337                 return;
 338         }
 339
 340         // Alternate header style - primary differences are for PDFs
 341         function Header3()
 342         {
 343                 // Flag to make sure we only print the company name once
 344                 $companyNamePrinted = false;
 345
 346                 $this->y = 0;
 347                 $tcol = $this->numcols - 1;
 348                 $this->sheet->setRow($this->y, 20);
 349                 // Title
 350                 for ($i = 0; $i < $this->numcols; $i++)
 351                         $this->sheet->writeBlank($this->y, $i, $this->formatTitle);
 352                 $this->sheet->writeString($this->y, 0, $this->title, $this->formatTitle);
 353                 $this->sheet->mergeCells($this->y, 0, $this->y, $tcol);
 354
 355                 // Dimension 1 - optional
 356                 // - only print if available and not blank
 357                 if (count($this->params) > 3)
 358                         if ($this->params[3]['from'] != '')
 359                         {
 360                                 $this->NewLine();
 361                                 $str = $this->params[3]['text'] . ':';
 362                                 $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 363                                 $this->sheet->writeString($this->y, 1, $this->params[3]['from'], $this->formatLeft);
 364                                 // Company Name - at end of this row
 365                                 if (!$companyNamePrinted)
 366                                 {
 367                                         $this->sheet->writeString($this->y, $tcol-1, $this->company['coy_name'], $this->formatLeft);
 368                                         $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol);
 369                                         $companyNamePrinted = true;
 370                                 }
 371                         }
 372
 373
 374                 // Dimension 2 - optional
 375                 // - only print if available and not blank
 376                 if (count($this->params) > 4)
 377                         if ($this->params[4]['from'] != '')
 378                         {
 379                                 $this->NewLine();
 380                                 $str = $this->params[4]['text'] . ':';
 381                                 $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 382                                 $this->sheet->writeString($this->y, 1, $this->params[4]['from'], $this->formatLeft);
 383                                 // Company Name - at end of this row
 384                                 if (!$companyNamePrinted)
 385                                 {
 386                                         $this->sheet->writeString($this->y, $tcol-1, $this->company['coy_name'], $this->formatLeft);
 387                                         $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol);
 388                                         $companyNamePrinted = true;
 389                                 }
 390                         }
 391
 392                 // Tags - optional
 393                 // TBD!!!
 394
 395                 // Report Date - time period covered
 396                 // - can specify a range, or just the end date (and the report contents
 397                 //   should make it obvious what the beginning date is)
 398                 $this->NewLine();
 399                 $str = _("Report Date") . ':';
 400                 $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 401                 $str = '';
 402                 if ($this->params[1]['from'] != '')
 403                         $str = $this->params[1]['from'] . ' - ';
 404                 $str .= $this->params[1]['to'];
 405                 $this->sheet->writeString($this->y, 1, $str, $this->formatLeft);
 406                 // Company Name - at end of this row
 407                 if (!$companyNamePrinted)
 408                 {
 409                         $this->sheet->writeString($this->y, $tcol-1, $this->company['coy_name'], $this->formatLeft);
 410                         $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol);
 411                         $companyNamePrinted = true;
 412                 }
 413
 414                 // Timestamp of when this copy of the report was generated
 415                 $this->NewLine();
 416                 $str = _("Generated At") . ':';
 417                 $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 418                 $this->sheet->writeString($this->y, 1, Today() . "  ".Now(), $this->formatLeft);
 419
 420                 // Name of the user that generated this copy of the report
 421                 $this->NewLine();
 422                 $str = _("Generated By") . ':';
 423                 $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 424                 $str = $this->user;
 425                 $this->sheet->writeString($this->y, 1, $str, $this->formatLeft);
 426
 427                 // Comments - display any user-generated comments for this copy of the report
 428                 if ($this->params[0] != '')
 429                 {
 430                         $this->NewLine();
 431                         $str = _("Comments") . ':';
 432                         $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 433                         $this->sheet->writeString($this->y, 1, $this->params[0], $this->formatLeft);
 434                 }
 435                 $this->NewLine();
 436
 437                 if ($this->headers2 != null)
 438                 {
 439                         for ($i = 0, $j = 0; $i < $this->numcols; $i++)
 440                         {
 441                                 if ($this->cols2[$j] >= $this->cols[$i] && $this->cols2[$j] <= $this->cols[$i + 1])
 442                                 {
 443                                         if ($this->aligns2[$j] == "right")
 444                                                 $this->sheet->writeString($this->y, $i, $this->headers2[$j], $this->formatTopHeaderRight);
 445                                         else
 446                                                 $this->sheet->writeString($this->y, $i, $this->headers2[$j], $this->formatTopHeaderLeft);
 447                                         $j++;
 448                                 }
 449                                 else
 450                                         $this->sheet->writeString($this->y, $i, "", $this->formatTopHeaderLeft);
 451                         }
 452                         $this->NewLine();
 453                 }
 454
 455                 for ($i = 0; $i < $this->numcols; $i++)
 456                 {
 457                         if (!isset($this->headers[$i]))
 458                                 $header = "";
 459                         else
 460                                 $header = $this->headers[$i];
 461                         if ($this->aligns[$i] == "right")
 462                                 if ($this->headers2 == null)
 463                                         $this->sheet->writeString($this->y, $i, $header, $this->formatHeaderRight);
 464                                 else
 465                                         $this->sheet->writeString($this->y, $i, $header, $this->formatBottomHeaderRight);
 466                         else
 467                                 if ($this->headers2 == null)
 468                                         $this->sheet->writeString($this->y, $i, $header, $this->formatHeaderLeft);
 469                                 else
 470                                         $this->sheet->writeString($this->y, $i, $header, $this->formatBottomHeaderLeft);
 471                 }
 472                 $this->NewLine();
 473         }
 474
 475         /**
 476          * Format a numeric string date into something nicer looking.
 477          *
 478          * @param string $date Date string to be formatted.
 479          * @param int $input_format Format of the input string.  Possible values are:<ul><li>0: user's default (default)</li></ul>
 480          * @param int $output_format Format of the output string.  Possible values are:<ul><li>0: Month (word) Day (numeric), 4-digit Year - Example: January 1, 2000 (default)</li><li>1: Month 4-digit Year - Example: January 2000</li><li>2: Month Abbreviation 4-digit Year - Example: Jan 2000</li></ul>
 481          * @access public
 482          */
 483         function DatePrettyPrint($date, $input_format = 0, $output_format = 0)
 484         {
 485                 if ($date != '')
 486                 {
 487                         $date = date2sql($date);
 488                         $year = (int) (substr($date, 0, 4));
 489                         $month = (int) (substr($date, 5, 2));
 490                         $day = (int) (substr($date, 8, 2));
 491                         if ($output_format == 0)
 492                                 return(date('F j, Y', mktime(12, 0, 0, $month, $day, $year)));
 493                         elseif ($output_format == 1)
 494                                 return(date('F Y', mktime(12, 0, 0, $month, $day, $year)));
 495                         elseif ($output_format == 2)
 496                                 return(date('M Y', mktime(12, 0, 0, $month, $day, $year)));
 497                 }
 498                 else
 499                         return $date;
 500         }
 501
 502         function AddImage($logo, $x, $y, $w, $h)
 503         {
 504                 return;
 505         }
 506
 507         function SetDrawColor($r, $g, $b)
 508         {
 509                 return;
 510         }
 511
 512         function SetTextColor($r, $g, $b)
 513         {
 514                 return;
 515         }
 516
 517         function SetFillColor($r, $g, $b)
 518         {
 519                 return;
 520         }
 521
 522         function GetCellPadding()
 523         {
 524                 return 0;
 525         }
 526
 527         function SetCellPadding($pad)
 528         {
 529                 return;
 530         }
 531
 532         function Text($c, $txt, $n=0, $corr=0, $r=0, $align='left', $border=0, $fill=0, $link=NULL, $stretch=0)
 533         {
 534                 return;
 535         }
 536
 537         function TextWrap($xpos, $ypos, $len, $str, $align = 'left', $border = 0, $fill = 0, $link = NULL, $stretch = 0)
 538         {
 539                 return;
 540         }
 541
 542         function TextCol($c, $n, $txt, $corr=0, $r=0, $border=0, $fill=0, $link=NULL, $stretch=0)
 543         {
 544                 $txt = html_entity_decode($txt);
 545                 if ($this->aligns[$c] == 'right')
 546                         $this->sheet->writeString($this->y, $c, $txt, $this->formatRight);
 547                 else
 548                         $this->sheet->writeString($this->y, $c, $txt, $this->formatLeft);
 549                 if ($n - $c > 1)
 550                         $this->sheet->mergeCells($this->y, $c, $this->y, $n - 1);
 551         }
 552
 553         function AmountCol($c, $n, $txt, $dec=0, $corr=0, $r=0, $border=0, $fill=0, $link=NULL, $stretch=0, $color_red=false)
 554         {
 555                 if (!is_numeric($txt))
 556                         $txt = 0;
 557                 $this->sheet->writeNumber($this->y, $c, $txt, $this->NumFormat($dec));
 558         }
 559
 560         function AmountCol2($c, $n, $txt, $dec=0, $corr=0, $r=0, $border=0, $fill=0, $link=NULL, $stretch=0, $color_red=false, $amount_locale = NULL, $amount_format = NULL)
 561         {
 562                 if (!is_numeric($txt))
 563                         $txt = 0;
 564                 $this->sheet->writeNumber($this->y, $c, $txt, $this->NumFormat($dec));
 565         }
 566
 567         function DateCol($c, $n, $txt, $conv=false, $corr=0, $r=0, $border=0, $fill=0, $link=NULL, $stretch=0)
 568         {
 569                 if (!$conv)
 570                         $txt = date2sql($txt);
 571                 list($year, $mo, $day) = explode("-", $txt);
 572                 $date = $this->ymd2date((int)$year, (int)$mo, (int)$day);
 573                 $this->sheet->writeNumber($this->y, $c, $date, $this->formatDate);
 574         }
 575
 576         function TextCol2($c, $n, $txt, $corr=0, $r=0, $border=0, $fill=0, $link=NULL, $stretch=0)
 577         {
 578                 $txt = html_entity_decode($txt);
 579                 $this->sheet->writeString($this->y, $c, $txt, $this->formatLeft);
 580                 if ($n - $c > 1)
 581                         $this->sheet->mergeCells($this->y, $c, $this->y, $n - 1);
 582         }
 583
 584         function TextColLines($c, $n, $txt, $corr=0, $r=0, $border=0, $fill=0, $link=NULL, $stretch=0)
 585         {
 586                 return;
 587         }
 588
 589         function TextWrapLines($c, $width, $txt, $align='left', $border=0, $fill=0, $link=NULL, $stretch=0)
 590         {
 591                 return;
 592         }
 593
 594         /**
 595          * Crude text wrap calculator based on PDF version.
 596          */
 597         function TextWrapCalc($txt, $width, $spacebreak=false)
 598         {
 599                 // Assume an average character width
 600                 $avg_char_width = 5;
 601                 $ret = "";
 602                 $txt2 = $txt;
 603                 $w = strlen($txt) * $avg_char_width;
 604                 if ($w > $width && $w > 0 && $width != 0)
 605                 {
 606                         $n = strlen($txt);
 607                         $k = intval($n * $width / $w);
 608                         if ($k > 0 && $k < $n)
 609                         {
 610                                 $txt2 = substr($txt, 0, $k);
 611                                 if ($spacebreak && (($pos = strrpos($txt2, " ")) !== false))
 612                                 {
 613                                         $txt2 = substr($txt2, 0, $pos);
 614                                         $ret = substr($txt, $pos+1);
 615                                 }
 616                                 else
 617                                         $ret = substr($txt, $k);
 618                         }
 619                 }
 620                 return array($txt2, $ret);
 621         }
 622
 623         function SetLineStyle($style)
 624         {
 625                 return;
 626         }
 627
 628         function SetLineWidth($width)
 629         {
 630                 return;
 631         }
 632
 633         function LineTo($from, $row, $to, $row2)
 634         {
 635                 return;
 636         }
 637
 638         function Line($row, $height = 0)
 639         {
 640                 return;
 641         }
 642
 643         function UnderlineCell($c, $r = 0, $type = 1, $linewidth = 0, $style = array())
 644         {
 645                 return;
 646         }
 647
 648         function NewLine($l=1, $np=0, $h=NULL)
 649         {
 650                 $this->y += $l;
 651                 if ($this->y > MAX_ROW_SHEET)
 652                 {
 653                         $this->y = 0;
 654                         $this->sheet = $this->addWorksheet();
 655                         if ($this->code != "iso-8859-1")
 656                                 $this->sheet->setInputEncoding($this->code); // set sheet encoding
 657                         if ($_SESSION['language']->dir == 'rtl')
 658                                 $this->sheet->setRTL();
 659                         for ($i = 0; $i < $this->numcols; $i++)
 660                                 $this->sheet->setColumn($i, $i, $this->px2units($this->cols[$i + 1] - $this->cols[$i]));
 661                 }
 662         }
 663
 664         function NewPage()
 665         {
 666                 if (method_exists($this, $this->headerFunc))    // draw predefined page layout if any
 667                         $this->{$this->headerFunc}();
 668         }
 669
 670         function ymd2Date($year, $mon, $day) // XLS internal date representation is a number between 1900-01-01 and 2078-12-31
 671         {                                                                               // if we need the time part too, we have to add this value after a decimalpoint.
 672         $mo = array(0,31,28,31,30,31,30,31,31,30,31,30,31);
 673         $BASE = 1900;
 674                 $MAXYEAR = 2075;
 675                 if (($year % 4) == 0)
 676                 $mo[2]++;
 677         if ($mon < 1)
 678             $mon = 1;
 679         elseif ($mon > 12)
 680             $mon = 12;
 681         if ($day < 1)
 682             $day = 1;
 683         elseif ($day > $mo[$mon])
 684             $day = $mo[$mon];
 685         if ($year < $BASE)
 686             $year = $BASE;
 687         elseif ($year > $MAXYEAR)
 688             $year = $MAXYEAR;
 689         $jul = (int)$day;
 690         for ($n = 1; $n < $mon; $n++)
 691         {
 692             $jul += $mo[$n];
 693         }
 694         for ($n = $BASE; $n < $year; $n++)
 695         {
 696             $jul += 365;
 697             if (($n % 4) == 0)
 698                 $jul++;
 699         }
 700         return $jul;
 701         }
 702
 703         function px2units($px) // XLS app conversion. Not bulletproof.
 704         {
 705                 $excel_column_width_factor = 256;
 706                 $unit_offset_length = $this->excelColWidthFactor;
 707                 return ($px / $unit_offset_length);
 708         }
 709
 710         function End($email=0, $subject='')
 711         {
 712                 global $path_to_root;
 713                 ++$this->y;
 714                 for ($i = 0; $i < $this->numcols; $i++)
 715                         $this->sheet->writeBlank($this->y, $i, $this->formatFooter);
 716                 $this->sheet->mergeCells($this->y, 0, $this->y, $this->numcols - 1);
 717                 $this->close();
 718                 // first have a look through the directory,
 719                 // and remove old temporary pdfs
 720                 if ($d = @opendir($this->path)) {
 721                         while (($file = readdir($d)) !== false) {
 722                                 if (!is_file($this->path.'/'.$file) || $file == 'index.php') continue;
 723                                 // then check to see if this one is too old
 724                                 $ftime = filemtime($this->path.'/'.$file);
 725                                 // seems 3 min is enough for any report download, isn't it?
 726                                 if (time()-$ftime > 180){
 727                                         unlink($this->path.'/'.$file);
 728                                 }
 729                         }
 730                         closedir($d);
 731                 }
 732
 733                 meta_forward($path_to_root.'/reporting/prn_redirect.php', "xls=1&filename=$this->filename&unique=$this->unique_name");
 734                 exit();
 735         }
 736 }
 737