mz/crm.twinpol.com
1
0
Fork 0
Code Issues 1 Pull Requests 1 Actions Packages Projects Releases Wiki Activity
Files
main
crm.twinpol.com/modules/EcmReportsBackUp20151106/ListDailySalesToExcelFile.php

266 lines
15 KiB
PHP
Raw Permalink Normal View History

Add php files
2025-05-12 15:44:39 +00:00
<?php
require_once 'ListDailySales.inc';
/*****************************************************/
/******************* EXCELL Export *******************/
/*****************************************************/
/**
* PHPExcel
*
* Copyright (C) 2006 - 2014 PHPExcel
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel
* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version 1.8.0, 2014-03-02
*/
/** PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/PhpExcell/Classes/PHPExcel/IOFactory.php';
$objPHPExcel = new PHPExcel();
// Set document properties
echo date('H:i:s') , " Set document properties" , EOL;
$objPHPExcel->getProperties()->setCreator("E5")
->setLastModifiedBy("E5")
->setTitle("E5 - Dily Sales Report")
->setSubject("E5 - Report Document")
->setDescription("Report Sales - description")
->setKeywords("e5, report, daily sales, daily, sales")
->setCategory("Reports");
/*****************************************************/
/******************* EXCELL Styles *******************/
/*****************************************************/
// Set default widths and heights
$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);
$headerStyle = array(
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
),
'font' => array(
'bold' => true,
'color' => array('rgb' => 'FFFFFF'),
'name' => 'Verdana',
),
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array(
'rgb' => '272822',
),
),
);
$columnAlignRight = array(
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
),
);
$objPHPExcel->setActiveSheetIndex(0)->getRowDimension('1')->setRowHeight(25);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(60);
/*****************************************************/
/********************* EXCELL Data *******************/
/*****************************************************/
// Add header
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', $mod_strings['LBL_CONTRACTOR'] )
->setCellValue('B1', $mod_strings['LBL_INVOICE_NUMBER'])
->setCellValue('C1', $mod_strings['LBL_TYPE'])
->setCellValue('D1', $mod_strings['LBL_REGISTER_DATE'])
->setCellValue('E1', $mod_strings['LBL_PDF_TYPE'])
->setCellValue('F1', $mod_strings['LBL_NET_VALUE'])
->setCellValue('G1', "TH1")
->setCellValue('H1', "WG1")
->setCellValue('I1', "PP1")
->setCellValue('J1', "TR1")
->setCellValue('K1', $mod_strings['LBL_GROSS_VALUE'])
->setCellValue('L1', $mod_strings['LBL_COST'])
->setCellValue('M1', "TH2")
->setCellValue('N1', "WG2")
->setCellValue('O1', "PP2")
->setCellValue('P1', "TR2")
->setCellValue('Q1', $mod_strings['LBL_MARGIN']);
/*****************************************************/
/********************* EXCELL Data *******************/
/*****************************************************/
// Add all data
$lastRowNumber = 2;
foreach ( $data as $rowNumber => &$row )
{
// --------------------------------------------
$pdfType = "";
if( $row["pdf_type"] == 'U')
$pdfType = $mod_strings['LBL_PDF_TYPE_U'];
else if( $row["pdf_type"] == 'K')
$pdfType = $mod_strings['LBL_PDF_TYPE_K'];
else if( $row["pdf_type"] == 'E')
$pdfType = $mod_strings['LBL_PDF_TYPE_E'];
// --------------------------------------------
$type = "";
if( $row["type"] == 'normal' )
$type = $mod_strings['LBL_PDF_TYPE_NORMAL'];
else if( $row["type"] == 'correct' )
$type = $mod_strings['LBL_PDF_TYPE_CORRECT'];
// --------------------------------------------
// Jeżeli z bazy danych sumy są nullami tu o to dbam, żeby nie było pustych pól w Excelu
$purchase1 = "0";
$purchase2 = "0";
$purchase3 = "0";
if( $row['purchasePriceGroupKS1'] != NULL && $row['purchasePriceGroupKS1'] != '' )
{
$purchase1 = $row['purchasePriceGroupKS1'];
}
if( $row['purchasePriceGroupKS2'] != NULL && $row['purchasePriceGroupKS2'] != '' )
{
$purchase2 = $row['purchasePriceGroupKS2'];
}
if( $row['purchasePriceGroupKS3'] != NULL && $row['purchasePriceGroupKS3'] != '' )
{
$purchase3 = $row['purchasePriceGroupKS3'];
}
if( $row['purchasePriceGroupKS4'] != NULL && $row['purchasePriceGroupKS4'] != '' )
{
$purchase4 = $row['purchasePriceGroupKS4'];
}
//echo number_format($row["subtotal"],2,".","")."(".gettype(number_format($row["subtotal"],2,".","")).")<br>";
//echo printf("%01.2f", $row['subtotal'])."(".gettype(printf("%01.2f", $row['subtotal'])).")<br>";
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.($rowNumber+2), $row['account']["name"])
->setCellValue('B'.($rowNumber+2), $row['document_no'])
->setCellValue('C'.($rowNumber+2), $row["showType"])
->setCellValue('D'.($rowNumber+2), $row['register_date'])
->setCellValue('E'.($rowNumber+2), $pdfType)
->setCellValue('F'.($rowNumber+2), number_format( $row['total_netto'], 2, ".", "" ))
->setCellValue('G'.($rowNumber+2), number_format( $row['subPriceGroupKS1'], 2, ".", "" ))
->setCellValue('H'.($rowNumber+2), number_format( $row['subPriceGroupKS2'], 2, ".", "" ))
->setCellValue('I'.($rowNumber+2), number_format( $row['subPriceGroupKS3'], 2, ".", "" ))
->setCellValue('J'.($rowNumber+2), number_format( $row['subPriceGroupKS3'], 2, ".", "" ))
->setCellValue('K'.($rowNumber+2), number_format( $row['total_brutto'], 2, ".", "" ))
->setCellValue('L'.($rowNumber+2), number_format( $row['purchase_price'], 2, ".", "" ))
->setCellValue('M'.($rowNumber+2), number_format( $purchase1 , 2, ".", "" ))
->setCellValue('N'.($rowNumber+2), number_format( $purchase2 , 2, ".", "" ))
->setCellValue('O'.($rowNumber+2), number_format( $purchase3 , 2, ".", "" ))
->setCellValue('P'.($rowNumber+2), number_format( $purchase4 , 2, ".", "" ))
->setCellValue('Q'.($rowNumber+2), number_format( $row['margin'], 2, ".", "")."%");
// Formatowanie walutowe
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.($rowNumber+2))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G'.($rowNumber+2))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('H'.($rowNumber+2))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('I'.($rowNumber+2))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('J'.($rowNumber+2))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('K'.($rowNumber+2))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('L'.($rowNumber+2))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('M'.($rowNumber+2))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('N'.($rowNumber+2))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('O'.($rowNumber+2))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('P'.($rowNumber+2))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('Q'.($rowNumber+2))->getNumberFormat()->setFormatCode("#0.#0");
// Wyrównanie pól do prawej
$objPHPExcel->setActiveSheetIndex(0)->getStyle("F".($rowNumber+2))->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("G".($rowNumber+2))->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("H".($rowNumber+2))->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("I".($rowNumber+2))->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("J".($rowNumber+2))->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("K".($rowNumber+2))->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("L".($rowNumber+2))->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("M".($rowNumber+2))->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("N".($rowNumber+2))->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("O".($rowNumber+2))->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("P".($rowNumber+2))->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("Q".($rowNumber+2))->applyFromArray($columnAlignRight);
$lastRowNumber++;
}
// Sumy --------------------------------------------------------------------------------------------------
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('E'.($lastRowNumber), "Podsumowanie: ")
->setCellValue('F'.($lastRowNumber), number_format( $sum['netto'], 2, ".", "" ))
->setCellValue('G'.($lastRowNumber), number_format( $sum['subPriceGroupKS1Sum'], 2, ".", "" ))
->setCellValue('H'.($lastRowNumber), number_format( $sum['subPriceGroupKS2Sum'], 2, ".", "" ))
->setCellValue('I'.($lastRowNumber), number_format( $sum['subPriceGroupKS3Sum'], 2, ".", "" ))
->setCellValue('J'.($lastRowNumber), number_format( $sum['subPriceGroupKS4Sum'], 2, ".", "" ))
->setCellValue('K'.($lastRowNumber), number_format( $sum['brutto'], 2, ".", "" ))
->setCellValue('L'.($lastRowNumber), number_format( $sum['cost'], 2, ".", "" ))
->setCellValue('M'.($lastRowNumber), number_format( $sum['purchasePriceGroupKS1Sum'], 2, ".", "" ))
->setCellValue('N'.($lastRowNumber), number_format( $sum['purchasePriceGroupKS2Sum'], 2, ".", "" ))
->setCellValue('O'.($lastRowNumber), number_format( $sum['purchasePriceGroupKS3Sum'], 2, ".", "" ))
->setCellValue('P'.($lastRowNumber), number_format( $sum['purchasePriceGroupKS4Sum'], 2, ".", "" ))
->setCellValue('Q'.($lastRowNumber), number_format( $sum['margin'], 2, ".", "" ));
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('H'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('I'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('J'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('K'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('L'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('M'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('N'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('O'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('P'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
$objPHPExcel->setActiveSheetIndex(0)->getStyle('Q'.($lastRowNumber))->getNumberFormat()->setFormatCode("#0.#0");
// Wyrównanie pól ostatniego wiersza do prawej
$objPHPExcel->setActiveSheetIndex(0)->getStyle("E".$lastRowNumber.":Q".$lastRowNumber)->applyFromArray($headerStyle);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("F".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("G".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("H".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("I".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("J".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("K".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("L".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("M".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("N".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("O".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("P".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("Q".$lastRowNumber)->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A1:Q1")->applyFromArray($headerStyle);
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Raport sprzedaży');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 2007 file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save( __DIR__ . "/ExcelFiles/DailySales.xls");
?>
Reference in New Issue Copy Permalink