. ***********************************************************************/ include_once($path_to_root . "/reporting/includes/Workbook.php"); include_once($path_to_root . "/admin/db/company_db.inc"); include_once($path_to_root . "/admin/db/fiscalyears_db.inc"); include_once($path_to_root . "/config.php"); // xls version class FrontReport extends Spreadsheet_Excel_Writer_Workbook { var $size; var $company; var $user; var $host; var $fiscal_year; var $title; var $filename; var $unique_name; var $path; var $code; var $bottomMargin = 0; var $lineHeight; var $leftMargin = 0; var $pageWidth; // dummy var $rightMargin; // dummy var $cols; var $params; var $headers; var $aligns; var $headers2; var $aligns2; var $cols2; var $fontSize; var $oldFontSize; var $currency; var $row = 9999999; var $y; var $numcols; var $excelColWidthFactor; var $endLine; var $formatTitle; var $formatDateTime; var $formatDate; var $formatHeaderLeft; var $formatHeaderRight; var $formatFooter; var $formatAmount = array(); var $headerFunc; var $sheet; function FrontReport($title, $filename, $size = 'A4', $fontsize = 9, $orientation = 'P', $margins = NULL, $excelColWidthFactor = 6.5) { global $dateseps, $page_security; if (!$_SESSION["wa_current_user"]->can_access_page($page_security)) { display_error(_("The security settings on your account do not permit you to print this report")); end_page(); exit; } $this->size = $size; $this->title = $title; $this->lineHeight = 12; $this->endLine = 760; $this->SetFontSize($fontsize); $this->oldFontSize = 0; $this->y = 0; $this->currency = ''; $this->excelColWidthFactor = $excelColWidthFactor; $rtl = ($_SESSION['language']->dir == 'rtl'); $this->code = strtolower($_SESSION['language']->encoding); $this->filename = $filename.".xls"; $this->unique_name = uniqid('').".xls"; $this->path = company_path(). '/pdf_files'; $this->Spreadsheet_Excel_Writer_Workbook($this->path."/".$this->unique_name); //$this->setCountry(48); if ($this->code != "iso-8859-1") $this->setVersion(8); // set biff version to 8 (0x0006 internal) $this->sheet =& $this->addWorksheet($this->worksheetNameGenerator($this->title)); if ($this->code != "iso-8859-1") $this->sheet->setInputEncoding($this->code); // set sheet encoding if ($rtl) $this->sheet->setRTL(); $this->formatTitle =& $this->addFormat(); $this->formatTitle->setSize(16); $this->formatTitle->setBold(); $this->formatTitle->setAlign($rtl ? 'right' : 'left'); $this->formatTitle->setTop(2); $this->formatTitle->setTopColor('gray'); $how = user_date_format(); $sep = $dateseps[user_date_sep()]; if ($sep == '.') $sep = "\\."; if ($how == 0) { $dateformat_long = "mm{$sep}dd{$sep}yyyy\ \ hh:mm\ am/pm"; $dateformat = "mm{$sep}dd{$sep}yyyy"; } elseif ($how == 1) { $dateformat_long = "dd{$sep}mm{$sep}yyyy\ \ hh:mm"; $dateformat = "dd{$sep}mm{$sep}yyyy"; } else { $dateformat_long = "yyyy{$sep}mm{$sep}dd\ \ hh:mm"; $dateformat = "yyyy{$sep}mm{$sep}dd"; } $this->formatDateTime =& $this->addFormat(); $this->formatDateTime->setNumFormat($dateformat_long); $this->formatDateTime->setAlign($rtl ? 'right' : 'left'); $this->formatDate =& $this->addFormat(); $this->formatDate->setNumFormat($dateformat); $this->formatDate->setAlign($rtl ? 'right' : 'left'); $this->formatRight =& $this->addFormat(); $this->formatRight->setAlign($rtl ? 'left' : 'right'); $this->formatLeft =& $this->addFormat(); $this->formatLeft->setAlign($rtl ? 'right' : 'left'); $this->formatHeaderLeft =& $this->addFormat(); $this->formatHeaderLeft->setItalic(); $this->formatHeaderLeft->setTop(2); $this->formatHeaderLeft->setTopColor('gray'); $this->formatHeaderLeft->setBottom(2); $this->formatHeaderLeft->setBottomColor('gray'); $this->formatHeaderLeft->setAlign('vcenter'); $this->formatTopHeaderLeft =& $this->addFormat(); $this->formatTopHeaderLeft->setItalic(); $this->formatTopHeaderLeft->setTop(2); $this->formatTopHeaderLeft->setTopColor('gray'); $this->formatTopHeaderLeft->setAlign('vcenter'); $this->formatBottomHeaderLeft =& $this->addFormat(); $this->formatBottomHeaderLeft->setItalic(); $this->formatBottomHeaderLeft->setBottom(2); $this->formatBottomHeaderLeft->setBottomColor('gray'); $this->formatBottomHeaderLeft->setAlign('vcenter'); $this->formatDate->setAlign($rtl ? 'right' : 'left'); $this->formatHeaderRight =& $this->addFormat(); $this->formatHeaderRight->setItalic(); $this->formatHeaderRight->setTop(2); $this->formatHeaderRight->setTopColor('gray'); $this->formatHeaderRight->setBottom(2); $this->formatHeaderRight->setBottomColor('gray'); $this->formatHeaderRight->setAlign('vcenter'); $this->formatHeaderRight->setAlign('right'); $this->formatTopHeaderRight =& $this->addFormat(); $this->formatTopHeaderRight->setItalic(); $this->formatTopHeaderRight->setTop(2); $this->formatTopHeaderRight->setTopColor('gray'); $this->formatTopHeaderRight->setAlign('vcenter'); $this->formatTopHeaderRight->setAlign('right'); $this->formatBottomHeaderRight =& $this->addFormat(); $this->formatBottomHeaderRight->setItalic(); $this->formatBottomHeaderRight->setBottom(2); $this->formatBottomHeaderRight->setBottomColor('gray'); $this->formatBottomHeaderRight->setAlign('vcenter'); $this->formatBottomHeaderRight->setAlign('right'); $this->formatFooter =& $this->addFormat(); $this->formatFooter->setTop(2); $this->formatFooter->setTopColor('gray'); $this->SetHeaderType("header"); } /* Set header handler */ function SetHeaderType($name) { $this->headerFunc = $name; } // Check a given name to see if it's a valid Excel worksheet name, // and fix if necessary function worksheetNameGenerator($name) { // First, strip out characters which aren't allowed $illegal_chars = array(':', '\\', '/', '?', '*', '[', ']'); for ($i = 0; $i < count($illegal_chars); $i++) $name = str_replace($illegal_chars[$i], '', $name); // Now, if name is longer than 31 chars, truncate it if (strlen($name) > 31) $name = substr($name, 0, 31); return $name; } function NumFormat($dec) { if (!isset($this->formatAmount[$dec])) { //global $thoseps,$decseps; $dec = (int)$dec; //$tsep = $thoseps[user_tho_sep()]; //$dsep = $decseps[user_dec_sep()]; $tsep = ','; $dsep = '.'; $format = "###{$tsep}###{$tsep}###{$tsep}##0"; if ($dec>0) $format .= "{$dsep}".str_repeat('0',$dec); $this->formatAmount[$dec] =& $this->addFormat(); $this->formatAmount[$dec]->setNumFormat($format); $this->formatAmount[$dec]->setAlign('right'); } return $this->formatAmount[$dec]; } function Font($fontname = '', $style = 'normal') { } function SetFontSize($size) { $this->fontSize = $size; } function Info($params, $cols, $headers, $aligns, $cols2 = null, $headers2 = null, $aligns2 = null) { global $app_title, $version, $power_by, $power_url; $this->company = get_company_prefs(); $year = get_current_fiscalyear(); if ($year['closed'] == 0) $how = _("Active"); else $how = _("Closed"); $this->fiscal_year = sql2date($year['begin']) . " - " . sql2date($year['end']) . " " . "(" . $how . ")"; $this->user = $_SESSION["wa_current_user"]->name; $this->host = $_SERVER['SERVER_NAME']; $this->params = $params; $this->cols = $cols; $this->headers = $headers; $this->aligns = $aligns; $this->cols2 = $cols2; $this->headers2 = $headers2; $this->aligns2 = $aligns2; $this->numcols = count($this->headers); $tcols = count($this->headers2); if ($tcols > $this->numcols) $this->numcols = $tcols; for ($i = 0; $i < $this->numcols; $i++) $this->sheet->setColumn($i, $i, $this->px2units($this->cols[$i + 1] - $this->cols[$i])); } function Header() { $tcol = $this->numcols - 1; $this->sheet->setRow($this->y, 20); for ($i = 0; $i < $this->numcols; $i++) $this->sheet->writeBlank($this->y, $i, $this->formatTitle); $this->sheet->writeString($this->y, 0, $this->title, $this->formatTitle); $this->sheet->mergeCells($this->y, 0, $this->y, $tcol); $this->NewLine(); $str = _("Print Out Date") . ':'; $this->sheet->writeString($this->y, 0, $str, $this->formatLeft); $this->sheet->writeString($this->y, 1, Today() . " ".Now(), $this->formatLeft); $this->sheet->writeString($this->y, $tcol-1, $this->company['coy_name'], $this->formatLeft); $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol); $this->NewLine(); $str = _("Fiscal Year") . ':'; $this->sheet->writeString($this->y, 0, $str, $this->formatLeft); $str = $this->fiscal_year; $this->sheet->writeString($this->y, 1, $str, $this->formatLeft); $this->sheet->writeString($this->y, $tcol-1, $this->host, $this->formatLeft); $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol); for ($i = 1; $i < count($this->params); $i++) { if ($this->params[$i]['from'] != '') { $this->NewLine(); $str = $this->params[$i]['text'] . ':'; $this->sheet->writeString($this->y, 0, $str); $str = $this->params[$i]['from']; if ($this->params[$i]['to'] != '') $str .= " - " . $this->params[$i]['to']; $this->sheet->writeString($this->y, 1, $str, $this->formatLeft); if ($i == 1) { $this->sheet->writeString($this->y, $tcol-1, $this->user, $this->formatLeft); $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol); } } } if ($this->params[0] != '') // Comments { $this->NewLine(); $str = _("Comments") . ':'; $this->sheet->writeString($this->y, 0, $str, $this->formatLeft); $this->sheet->writeString($this->y, 1, $this->params[0], $this->formatLeft); } $this->NewLine(); if ($this->headers2 != null) { for ($i = 0, $j = 0; $i < $this->numcols; $i++) { if ($this->cols2[$j] >= $this->cols[$i] && $this->cols2[$j] <= $this->cols[$i + 1]) { if ($this->aligns2[$j] == "right") $this->sheet->writeString($this->y, $i, $this->headers2[$j], $this->formatHeaderRight); else $this->sheet->writeString($this->y, $i, $this->headers2[$j], $this->formatHeaderLeft); $j++; } else $this->sheet->writeString($this->y, $i, "", $this->formatHeaderLeft); } $this->NewLine(); } for ($i = 0; $i < $this->numcols; $i++) { if (!isset($this->headers[$i])) $header = ""; else $header = $this->headers[$i]; if ($this->aligns[$i] == "right") $this->sheet->writeString($this->y, $i, $header, $this->formatHeaderRight); else $this->sheet->writeString($this->y, $i, $header, $this->formatHeaderLeft); } $this->NewLine(); } function Header2($myrow, $branch, $sales_order, $bankaccount, $doctype) { return; } // Alternate header style - primary differences are for PDFs function Header3() { // Flag to make sure we only print the company name once $companyNamePrinted = false; $this->y = 0; $tcol = $this->numcols - 1; $this->sheet->setRow($this->y, 20); // Title for ($i = 0; $i < $this->numcols; $i++) $this->sheet->writeBlank($this->y, $i, $this->formatTitle); $this->sheet->writeString($this->y, 0, $this->title, $this->formatTitle); $this->sheet->mergeCells($this->y, 0, $this->y, $tcol); // Dimension 1 - optional // - only print if available and not blank if (count($this->params) > 3) if ($this->params[3]['from'] != '') { $this->NewLine(); $str = $this->params[3]['text'] . ':'; $this->sheet->writeString($this->y, 0, $str, $this->formatLeft); $this->sheet->writeString($this->y, 1, $this->params[3]['from'], $this->formatLeft); // Company Name - at end of this row if (!$companyNamePrinted) { $this->sheet->writeString($this->y, $tcol-1, $this->company['coy_name'], $this->formatLeft); $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol); $companyNamePrinted = true; } } // Dimension 2 - optional // - only print if available and not blank if (count($this->params) > 4) if ($this->params[4]['from'] != '') { $this->NewLine(); $str = $this->params[4]['text'] . ':'; $this->sheet->writeString($this->y, 0, $str, $this->formatLeft); $this->sheet->writeString($this->y, 1, $this->params[4]['from'], $this->formatLeft); // Company Name - at end of this row if (!$companyNamePrinted) { $this->sheet->writeString($this->y, $tcol-1, $this->company['coy_name'], $this->formatLeft); $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol); $companyNamePrinted = true; } } // Tags - optional // TBD!!! // Report Date - time period covered // - can specify a range, or just the end date (and the report contents // should make it obvious what the beginning date is) $this->NewLine(); $str = _("Report Date") . ':'; $this->sheet->writeString($this->y, 0, $str, $this->formatLeft); $str = ''; if ($this->params[1]['from'] != '') $str = $this->params[1]['from'] . ' - '; $str .= $this->params[1]['to']; $this->sheet->writeString($this->y, 1, $str, $this->formatLeft); // Company Name - at end of this row if (!$companyNamePrinted) { $this->sheet->writeString($this->y, $tcol-1, $this->company['coy_name'], $this->formatLeft); $this->sheet->mergeCells($this->y, $tcol-1, $this->y, $tcol); $companyNamePrinted = true; } // Timestamp of when this copy of the report was generated $this->NewLine(); $str = _("Generated At") . ':'; $this->sheet->writeString($this->y, 0, $str, $this->formatLeft); $this->sheet->writeString($this->y, 1, Today() . " ".Now(), $this->formatLeft); // Name of the user that generated this copy of the report $this->NewLine(); $str = _("Generated By") . ':'; $this->sheet->writeString($this->y, 0, $str, $this->formatLeft); $str = $this->user; $this->sheet->writeString($this->y, 1, $str, $this->formatLeft); // Comments - display any user-generated comments for this copy of the report if ($this->params[0] != '') { $this->NewLine(); $str = _("Comments") . ':'; $this->sheet->writeString($this->y, 0, $str, $this->formatLeft); $this->sheet->writeString($this->y, 1, $this->params[0], $this->formatLeft); } $this->NewLine(); if ($this->headers2 != null) { for ($i = 0, $j = 0; $i < $this->numcols; $i++) { if ($this->cols2[$j] >= $this->cols[$i] && $this->cols2[$j] <= $this->cols[$i + 1]) { if ($this->aligns2[$j] == "right") $this->sheet->writeString($this->y, $i, $this->headers2[$j], $this->formatTopHeaderRight); else $this->sheet->writeString($this->y, $i, $this->headers2[$j], $this->formatTopHeaderLeft); $j++; } else $this->sheet->writeString($this->y, $i, "", $this->formatTopHeaderLeft); } $this->NewLine(); } for ($i = 0; $i < $this->numcols; $i++) { if (!isset($this->headers[$i])) $header = ""; else $header = $this->headers[$i]; if ($this->aligns[$i] == "right") if ($this->headers2 == null) $this->sheet->writeString($this->y, $i, $header, $this->formatHeaderRight); else $this->sheet->writeString($this->y, $i, $header, $this->formatBottomHeaderRight); else if ($this->headers2 == null) $this->sheet->writeString($this->y, $i, $header, $this->formatHeaderLeft); else $this->sheet->writeString($this->y, $i, $header, $this->formatBottomHeaderLeft); } $this->NewLine(); } /** * Format a numeric string date into something nicer looking. * * @param string $date Date string to be formatted. * @param int $input_format Format of the input string. Possible values are: