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 // xls version
  17 class FrontReport extends Spreadsheet_Excel_Writer_Workbook
  18 {
  19         var $size;
  20         var $company;
  21         var $user;
  22         var $host;
  23         var $fiscal_year;
  24         var $title;
  25         var $filename;
  26         var $unique_name;
  27         var $path;
  28         var $code;
  29         var $bottomMargin = 0;
  30         var $lineHeight;
  31         var $leftMargin = 0;
  32
  33         var $cols;
  34         var $params;
  35         var $headers;
  36         var $aligns;
  37         var $headers2;
  38         var $aligns2;
  39         var $cols2;
  40         var $fontSize;
  41         var $oldFontSize;
  42         var $currency;
  43         var $row = 9999999;
  44         var $y;
  45         var $numcols;
  46
  47         var $formatTitle;
  48         var $formatDateTime;
  49         var $formatDate;
  50         var $formatHeaderLeft;
  51         var $formatHeaderRight;
  52         var $formatFooter;
  53         var $formatAmount = array();
  54
  55         var $sheet;
  56
  57         function FrontReport($title, $filename, $size = 'A4', $fontsize = 9)
  58         {
  59                 global $comp_path, $dateseps, $page_security;
  60                 if (!$_SESSION["wa_current_user"]->can_access_page($page_security))
  61                 {
  62                         display_error(_("The security settings on your account do not permit you to print this report"));
  63                         end_page();
  64                         exit;
  65                 }
  66                 $this->size = $size;
  67                 $this->title = $title;
  68                 $this->lineHeight = 12;
  69                 $this->fontSize = $fontsize;
  70                 $this->oldFontSize = 0;
  71                 $this->y = 1;
  72                 $this->currency = '';
  73                 $rtl = ($_SESSION['language']->dir == 'rtl');
  74                 $this->code = strtolower($_SESSION['language']->encoding);
  75                 $this->filename = $filename.".xls";
  76                 $this->unique_name = uniqid('').".xls";
  77                 $this->path = $comp_path.'/'.user_company(). '/pdf_files';
  78                 $this->Spreadsheet_Excel_Writer_Workbook($this->path."/".$this->unique_name);
  79                 //$this->setCountry(48);
  80                 if ($this->code != "iso-8859-1")
  81                         $this->setVersion(8); // set biff version to 8 (0x0006 internal)
  82                 $this->sheet =& $this->addWorksheet($this->title);
  83                 if ($this->code != "iso-8859-1")
  84                         $this->sheet->setInputEncoding($this->code); // set sheet encoding
  85                 if ($rtl)
  86                         $this->sheet->setRTL();
  87                 $this->formatTitle =& $this->addFormat();
  88                 $this->formatTitle->setSize(16);
  89                 $this->formatTitle->setBold();
  90                 $this->formatTitle->setAlign($rtl ? 'right' : 'left');
  91                 $this->formatTitle->setTop(2);
  92                 $this->formatTitle->setTopColor('gray');
  93
  94                 $how = user_date_format();
  95                 $sep = $dateseps[user_date_sep()];
  96                 if ($sep == '.')
  97                         $sep = "\\.";
  98                 if ($how == 0)
  99                 {
 100                         $dateformat_long = "mm{$sep}dd{$sep}yyyy\ \ hh:mm\ am/pm";
 101                         $dateformat = "mm{$sep}dd{$sep}yyyy";
 102                 }
 103                 elseif ($how == 1)
 104                 {
 105                         $dateformat_long = "dd{$sep}mm{$sep}yyyy\ \ hh:mm";
 106                         $dateformat = "dd{$sep}mm{$sep}yyyy";
 107                 }
 108                 else
 109                 {
 110                         $dateformat_long = "yyyy{$sep}mm{$sep}dd\ \ hh:mm";
 111                         $dateformat = "yyyy{$sep}mm{$sep}dd";
 112                 }
 113                 $this->formatDateTime =& $this->addFormat();
 114                 $this->formatDateTime->setNumFormat($dateformat_long);
 115                 $this->formatDateTime->setAlign($rtl ? 'right' : 'left');
 116                 $this->formatDate =& $this->addFormat();
 117                 $this->formatDate->setNumFormat($dateformat);
 118                 $this->formatDate->setAlign($rtl ? 'right' : 'left');
 119                 $this->formatRight =& $this->addFormat();
 120                 $this->formatRight->setAlign($rtl ? 'left' : 'right');
 121                 $this->formatLeft =& $this->addFormat();
 122                 $this->formatLeft->setAlign($rtl ? 'right' : 'left');
 123
 124                 $this->formatHeaderLeft =& $this->addFormat();
 125                 $this->formatHeaderLeft->setItalic();
 126                 $this->formatHeaderLeft->setTop(2);
 127                 $this->formatHeaderLeft->setTopColor('gray');
 128                 $this->formatHeaderLeft->setBottom(2);
 129                 $this->formatHeaderLeft->setBottomColor('gray');
 130                 $this->formatHeaderLeft->setAlign('vcenter');
 131                 $this->formatDate->setAlign($rtl ? 'right' : 'left');
 132                 $this->formatHeaderRight =& $this->addFormat();
 133                 $this->formatHeaderRight->setItalic();
 134                 $this->formatHeaderRight->setTop(2);
 135                 $this->formatHeaderRight->setTopColor('gray');
 136                 $this->formatHeaderRight->setBottom(2);
 137                 $this->formatHeaderRight->setBottomColor('gray');
 138                 $this->formatHeaderRight->setAlign('vcenter');
 139                 $this->formatHeaderRight->setAlign('right');
 140                 $this->formatFooter =& $this->addFormat();
 141                 $this->formatFooter->setTop(2);
 142                 $this->formatFooter->setTopColor('gray');
 143         }
 144
 145         function NumFormat($dec)
 146         {
 147                 if (!isset($this->formatAmount[$dec]))
 148                 {
 149                         //global $thoseps,$decseps;
 150                         $dec = (int)$dec;
 151                         //$tsep = $thoseps[user_tho_sep()];
 152                         //$dsep = $decseps[user_dec_sep()];
 153                         $tsep = ',';
 154                         $dsep = '.';
 155                         $format = "###{$tsep}###{$tsep}###{$tsep}##0";
 156                         if ($dec>0)
 157                                 $format .= "{$dsep}".str_repeat('0',$dec);
 158                         $this->formatAmount[$dec] =& $this->addFormat();
 159                         $this->formatAmount[$dec]->setNumFormat($format);
 160                         $this->formatAmount[$dec]->setAlign('right');
 161                 }
 162                 return $this->formatAmount[$dec];
 163         }
 164
 165         function Font($style = 'normal')
 166         {
 167         }
 168
 169         function Info($params, $cols, $headers, $aligns,
 170                 $cols2 = null, $headers2 = null, $aligns2 = null)
 171         {
 172                 global $app_title, $version, $power_by, $power_url;
 173                 $this->company = get_company_prefs();
 174                 $year = get_current_fiscalyear();
 175                 if ($year['closed'] == 0)
 176                         $how = _("Active");
 177                 else
 178                         $how = _("Closed");
 179                 $this->fiscal_year = sql2date($year['begin']) . " - " . sql2date($year['end']) . "  " . "(" . $how . ")";
 180                 $this->user = $_SESSION["wa_current_user"]->name;
 181                 $this->host = $_SERVER['SERVER_NAME'];
 182                 $this->params = $params;
 183                 $this->cols = $cols;
 184                 $this->headers = $headers;
 185                 $this->aligns = $aligns;
 186                 $this->cols2 = $cols2;
 187                 $this->headers2 = $headers2;
 188                 $this->aligns2 = $aligns2;
 189                 $this->numcols = count($this->headers);
 190                 $tcols = count($this->headers2);
 191                 if ($tcols > $this->numcols)
 192                         $this->numcols = $tcols;
 193                 for ($i = 0; $i < $this->numcols; $i++)
 194                         $this->sheet->setColumn($i, $i, $this->px2units($this->cols[$i + 1] - $this->cols[$i]));
 195         }
 196
 197         function Header()
 198         {
 199                 $this->y = 0;
 200                 $tcol = $this->numcols - 1;
 201                 $this->sheet->setRow($this->y, 20);
 202                 for ($i = 0; $i < $this->numcols; $i++)
 203                         $this->sheet->writeBlank($this->y, $i, $this->formatTitle);
 204                 $this->sheet->writeString($this->y, 0, $this->title, $this->formatTitle);
 205                 $this->sheet->mergeCells($this->y, 0, $this->y, $tcol);
 206                 $this->NewLine();
 207                 $str = _("Print Out Date") . ':';
 208                 $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 209                 $this->sheet->writeString($this->y, 1, Today() . "  ".Now(), $this->formatLeft);
 210                 $this->sheet->writeString($this->y, $tcol-1, $this->company['coy_name'], $this->formatLeft);
 211                 $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol);
 212                 $this->NewLine();
 213                 $str = _("Fiscal Year") . ':';
 214                 $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 215                 $str = $this->fiscal_year;
 216                 $this->sheet->writeString($this->y, 1, $str, $this->formatLeft);
 217                 $this->sheet->writeString($this->y, $tcol-1, $this->host, $this->formatLeft);
 218                 $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol);
 219                 for ($i = 1; $i < count($this->params); $i++)
 220                 {
 221                         if ($this->params[$i]['from'] != '')
 222                         {
 223                                 $this->NewLine();
 224                                 $str = $this->params[$i]['text'] . ':';
 225                                 $this->sheet->writeString($this->y, 0, $str);
 226                                 $str = $this->params[$i]['from'];
 227                                 if ($this->params[$i]['to'] != '')
 228                                         $str .= " - " . $this->params[$i]['to'];
 229                                 $this->sheet->writeString($this->y, 1, $str, $this->formatLeft);
 230                                 if ($i == 1)
 231                                 {
 232                                         $this->sheet->writeString($this->y, $tcol-1, $this->user, $this->formatLeft);
 233                                         $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol);
 234                                 }
 235                         }
 236                 }
 237                 if ($this->params[0] != '') // Comments
 238                 {
 239                         $this->NewLine();
 240                         $str = _("Comments") . ':';
 241                         $this->sheet->writeString($this->y, 0, $str, $this->formatLeft);
 242                         $this->sheet->writeString($this->y, 1, $this->params[0], $this->formatLeft);
 243                 }
 244                 $this->NewLine();
 245                 if ($this->headers2 != null)
 246                 {
 247                         for ($i = 0, $j = 0; $i < $this->numcols; $i++)
 248                         {
 249                                 if ($this->cols2[$j] >= $this->cols[$i] && $this->cols2[$j] <= $this->cols[$i + 1])
 250                                 {
 251                                         if ($this->aligns2[$j] == "right")
 252                                                 $this->sheet->writeString($this->y, $i, $this->headers2[$j], $this->formatHeaderRight);
 253                                         else
 254                                                 $this->sheet->writeString($this->y, $i, $this->headers2[$j], $this->formatHeaderLeft);
 255                                         $j++;
 256                                 }
 257                                 else
 258                                         $this->sheet->writeString($this->y, $i, "", $this->formatHeaderLeft);
 259                         }
 260                         $this->NewLine();
 261                 }
 262
 263                 for ($i = 0; $i < $this->numcols; $i++)
 264                 {
 265                         if (!isset($this->headers[$i]))
 266                                 $header = "";
 267                         else
 268                                 $header = $this->headers[$i];
 269                         if ($this->aligns[$i] == "right")
 270                                 $this->sheet->writeString($this->y, $i, $header, $this->formatHeaderRight);
 271                         else
 272                                 $this->sheet->writeString($this->y, $i, $header, $this->formatHeaderLeft);
 273                 }
 274                 $this->NewLine();
 275         }
 276
 277         function Header2($myrow, $branch, $sales_order, $bankaccount, $doctype)
 278         {
 279                 return;
 280         }
 281
 282         function AddImage($logo, $x, $y, $w, $h)
 283         {
 284                 return;
 285         }
 286
 287         function SetDrawColor($r, $g, $b)
 288         {
 289                 return;
 290         }
 291
 292         function SetTextColor($r, $g, $b)
 293         {
 294                 return;
 295         }
 296
 297         function Text($c, $txt, $n=0, $corr=0, $r=0)
 298         {
 299                 return;
 300         }
 301
 302         function TextWrap($xpos, $ypos, $len, $str, $align = 'left')
 303         {
 304                 return;
 305         }
 306
 307         function TextCol($c, $n, $txt, $corr=0, $r=0)
 308         {
 309                 if ($this->aligns[$c] == 'right')
 310                         $this->sheet->writeString($this->y, $c, $txt, $this->formatRight);
 311                 else
 312                         $this->sheet->writeString($this->y, $c, $txt, $this->formatLeft);
 313                 if ($n - $c > 1)
 314                         $this->sheet->mergeCells($this->y, $c, $this->y, $n - 1);
 315         }
 316
 317         function AmountCol($c, $n, $txt, $dec=0, $corr=0, $r=0)
 318         {
 319                 if (!is_numeric($txt))
 320                         $txt = 0;
 321                 $this->sheet->writeNumber($this->y, $c, $txt, $this->NumFormat($dec));
 322         }
 323
 324         function DateCol($c, $n, $txt, $conv=false, $corr=0, $r=0)
 325         {
 326                 if (!$conv)
 327                         $txt = date2sql($txt);
 328                 list($year, $mo, $day) = explode("-", $txt);
 329                 $date = $this->ymd2date((int)$year, (int)$mo, (int)$day);
 330                 $this->sheet->writeNumber($this->y, $c, $date, $this->formatDate);
 331         }
 332
 333         function TextCol2($c, $n, $txt, $corr=0, $r=0)
 334         {
 335                 $this->sheet->writeString($this->y, $c, $txt, $this->formatLeft);
 336                 if ($n - $c > 1)
 337                         $this->sheet->mergeCells($this->y, $c, $this->y, $n - 1);
 338         }
 339
 340         function TextColLines($c, $n, $txt, $corr=0, $r=0)
 341         {
 342                 return;
 343         }
 344
 345         function TextWrapLines($c, $width, $txt, $align='left')
 346         {
 347                 return;
 348         }
 349
 350         function LineTo($from, $row, $to, $row2)
 351         {
 352                 return;
 353         }
 354
 355         function Line($row, $height = 0)
 356         {
 357                 return;
 358         }
 359
 360         function NewLine($l=1, $np=0)
 361         {
 362                 $this->y += $l;
 363         }
 364
 365         function ymd2Date($year, $mon, $day) // XLS internal date representation is a number between 1900-01-01 and 2078-12-31
 366         {                                                                               // if we need the time part too, we have to add this value after a decimalpoint.
 367         $mo = array(0,31,28,31,30,31,30,31,31,30,31,30,31);
 368         $BASE = 1900;
 369                 $MAXYEAR = 2075;
 370                 if (($year % 4) == 0)
 371                 $mo[2]++;
 372         if ($mon < 1)
 373             $mon = 1;
 374         elseif ($mon > 12)
 375             $mon = 12;
 376         if ($day < 1)
 377             $day = 1;
 378         elseif ($day > $mo[$mon])
 379             $day = $mo[$mon];
 380         if ($year < $BASE)
 381             $year = $BASE;
 382         elseif ($year > $MAXYEAR)
 383             $year = $MAXYEAR;
 384         $jul = (int)$day;
 385         for ($n = 1; $n < $mon; $n++)
 386         {
 387             $jul += $mo[$n];
 388         }
 389         for ($n = $BASE; $n < $year; $n++)
 390         {
 391             $jul += 365;
 392             if (($n % 4) == 0)
 393                 $jul++;
 394         }
 395         return $jul;
 396         }
 397
 398         function px2units($px) // XLS app conversion. Not bulletproof.
 399         {
 400                 $excel_column_width_factor = 256;
 401                 $unit_offset_length = 6.5;
 402                 return ($px / $unit_offset_length);
 403         }
 404
 405         function End($email=0, $subject=null, $myrow=null, $doctype = 0)
 406         {
 407                 for ($i = 0; $i < $this->numcols; $i++)
 408                         $this->sheet->writeBlank($this->y, $i, $this->formatFooter);
 409                 $this->sheet->mergeCells($this->y, 0, $this->y, $this->numcols - 1);
 410                 $this->close();
 411                 // first have a look through the directory,
 412                 // and remove old temporary pdfs
 413                 if ($d = @opendir($this->path)) {
 414                         while (($file = readdir($d)) !== false) {
 415                                 if (!is_file($this->path.'/'.$file) || $file == 'index.php') continue;
 416                                 // then check to see if this one is too old
 417                                 $ftime = filemtime($this->path.'/'.$file);
 418                                 // seems 3 min is enough for any report download, isn't it?
 419                                 if (time()-$ftime > 180){
 420                                         unlink($this->path.'/'.$file);
 421                                 }
 422                         }
 423                         closedir($d);
 424                 }
 425                 meta_forward($_SERVER['PHP_SELF'], "xls=1&filename=$this->filename&unique=$this->unique_name");
 426                 exit();
 427         }
 428 }
 429
 430 ?>