<?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");

	
?>