X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;ds=inline;f=reporting%2Fincludes%2Fexcel_report.inc;h=483d0fde157170d2d66fd99dc390d856908f6a83;hb=2aea99756a8c19766f0f9dfee56b27f7c1994f37;hp=f2a36b2f64984e33d327b768389e441631b56d81;hpb=4221bf69f713a2fb0bb4c4cae6f8f4f819c3ec31;p=fa-stable.git diff --git a/reporting/includes/excel_report.inc b/reporting/includes/excel_report.inc index f2a36b2f..483d0fde 100644 --- a/reporting/includes/excel_report.inc +++ b/reporting/includes/excel_report.inc @@ -13,6 +13,7 @@ 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"); +define('MAX_ROW_SHEET', 65530); // xls version class FrontReport extends Spreadsheet_Excel_Writer_Workbook { @@ -29,6 +30,8 @@ class FrontReport extends Spreadsheet_Excel_Writer_Workbook var $bottomMargin = 0; var $lineHeight; var $leftMargin = 0; + var $pageWidth; // dummy + var $rightMargin; // dummy var $cols; var $params; @@ -43,7 +46,9 @@ class FrontReport extends Spreadsheet_Excel_Writer_Workbook var $row = 9999999; var $y; var $numcols; - + var $excelColWidthFactor; + var $endLine; + var $formatTitle; var $formatDateTime; var $formatDate; @@ -51,12 +56,14 @@ class FrontReport extends Spreadsheet_Excel_Writer_Workbook var $formatHeaderRight; var $formatFooter; var $formatAmount = array(); + var $headerFunc; var $sheet; - function FrontReport($title, $filename, $size = 'A4', $fontsize = 9) + function __construct($title, $filename, $size = 'A4', $fontsize = 9, $orientation = 'P', $margins = NULL, $excelColWidthFactor = 6.5) { - global $comp_path, $dateseps, $page_security; + global $SysPrefs, $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")); @@ -66,20 +73,21 @@ class FrontReport extends Spreadsheet_Excel_Writer_Workbook $this->size = $size; $this->title = $title; $this->lineHeight = 12; - $this->fontSize = $fontsize; + $this->endLine = 760; + $this->SetFontSize($fontsize); $this->oldFontSize = 0; - $this->y = 1; + $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 = $comp_path.'/'.user_company(). '/pdf_files'; - $this->Spreadsheet_Excel_Writer_Workbook($this->path."/".$this->unique_name); - //$this->setCountry(48); + $this->unique_name = random_id().".xls"; + $this->path = company_path(). '/pdf_files'; + parent::__construct($this->path."/".$this->unique_name); if ($this->code != "iso-8859-1") $this->setVersion(8); // set biff version to 8 (0x0006 internal) - $this->sheet =& $this->addWorksheet($this->title); + $this->sheet = $this->addWorksheet($this->worksheetNameGenerator($this->title)); if ($this->code != "iso-8859-1") $this->sheet->setInputEncoding($this->code); // set sheet encoding if ($rtl) @@ -92,7 +100,7 @@ class FrontReport extends Spreadsheet_Excel_Writer_Workbook $this->formatTitle->setTopColor('gray'); $how = user_date_format(); - $sep = $dateseps[user_date_sep()]; + $sep = $SysPrefs->dateseps[user_date_sep()]; if ($sep == '.') $sep = "\\."; if ($how == 0) @@ -128,6 +136,16 @@ class FrontReport extends Spreadsheet_Excel_Writer_Workbook $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(); @@ -137,19 +155,50 @@ class FrontReport extends Spreadsheet_Excel_Writer_Workbook $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"; @@ -162,14 +211,18 @@ class FrontReport extends Spreadsheet_Excel_Writer_Workbook return $this->formatAmount[$dec]; } - function Font($style = 'normal') + 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) @@ -196,7 +249,6 @@ class FrontReport extends Spreadsheet_Excel_Writer_Workbook function Header() { - $this->y = 0; $tcol = $this->numcols - 1; $this->sheet->setRow($this->y, 20); for ($i = 0; $i < $this->numcols; $i++) @@ -279,6 +331,168 @@ class FrontReport extends Spreadsheet_Excel_Writer_Workbook 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: