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