<?php
require_once dirname(__FILE__) . '/PhpExcell/Classes/PHPExcel/IOFactory.php';

global $app_list_strings;
global $mod_strings;
$db = $GLOBALS ['db'];
/** 	
 * ************* GET DATA FROM DB********************
 */
$date_from = $_GET['date_from'];
$date_to = $_GET['date_to'];

$query = "
SELECT 
    wc.worker_id worker_id,
    wc.worker_name work_name
FROM
    ecmworkcards_ecmaction wca,
    ecmworkcards wc
WHERE
    wca.ecmworkcards = wc.id
        AND wc.deleted = 0
		AND wc.date >= STR_TO_DATE('" . $date_from . "','%d.%m.%Y')
		AND wc.date <= STR_TO_DATE('" . $date_to . "','%d.%m.%Y')
GROUP BY wc.worker_id;";
$rows = $db->query($query);
if ($rows->num_rows == 0) {
    
} else {
    while ($r = $db->fetchByAssoc($rows)) {
        $row = array();
        $row['work_name'] = $r['work_name'];
        $row['class'] = 'parent';
        $row['actions'] = array();
        $wherequery = "
            SELECT 
                id
            FROM
                ecmworkcards
            WHERE
                deleted = 0
                AND date >= STR_TO_DATE('" . $date_from . "','%d.%m.%Y') 
                AND date <= STR_TO_DATE('" . $date_to . "','%d.%m.%Y') "
                . " AND worker_id = '" . $r['worker_id'] . "'";
        $actionquery = "
			SELECT 
				wca.ecmproduct ecmproduct,
				wca.ecmaction ecmaction,
				wca.quantity quantity,
				wca.ecmactioncost ecmactioncost,
				wca.costbrutto costbrutto,
				a.name actionname,
				a.indeks actioncode,
				p.name productname,
				p.code productcode
            FROM
                ecmworkcards_ecmaction wca,
                ecmactions a,
                ecmproducts p
            WHERE
                a.id = wca.ecmaction
                    AND wca.ecmproduct = p.id"
                . " AND wca.ecmworkcards IN (" . $wherequery . ")
				ORDER BY p.code, a.indeks";
        $actionrows = $db->query($actionquery);
        if ($actionrows->num_rows == 0) {
            $row['actions'] = null;
        } else {
            $sumkanetto = 0;
            $sumkabrutto = 0;
            while ($rr = $db->fetchByAssoc($actionrows)) {
                $test = false; //sprawdza czy dana czynnosc jest juz pod pracownikiem z danym produktem
                // jesli tak to dodaje do porzedniego rekordu, jesli nie to dodaje nowy
                foreach ($row['actions'] as $key => $value) {
                    if ($value['actioncode'] == $rr['actioncode'] && $value['productcode'] == $rr['productcode']) {
                        $row['actions'][$key]['quantity'] += $rr['quantity'];
                        $row['actions'][$key]['netto'] += round($rr['ecmactioncost'] * $rr['quantity'], 2);
                        $row['actions'][$key]['brutto'] += round($rr['costbrutto'] * $rr['quantity'], 2);
                        $test = true;
                        $sumkanetto += round($rr['ecmactioncost'] * $rr['quantity'], 2);
                        $sumkabrutto += round($rr['costbrutto'] * $rr['quantity'], 2);
                        break;
                    }
                }
                if (!$test) {
                    $rowaction = array();
                    $rowaction['actionname'] = str_replace("&quot;",'"',$rr['actionname']);
                    $rowaction['actioncode'] = $rr['actioncode'];
                    $rowaction['productname'] = str_replace("&quot;",'"',$rr['productname']);
                    $rowaction['ecmproduct'] = $rr['ecmproduct'];
                    $rowaction['productcode'] = $rr['productcode'];
                    $rowaction['netto'] = round($rr['ecmactioncost'] * $rr['quantity'], 2);
                    $rowaction['brutto'] = round($rr['costbrutto'] * $rr['quantity'], 2);
                    $rowaction['quantity'] = $rr['quantity'];
                    $rowaction['class'] = 'child';
                    $sumkanetto += round($rr['ecmactioncost'] * $rr['quantity'], 2);
                    $sumkabrutto += round($rr['costbrutto'] * $rr['quantity'], 2);
                    $row['actions'][] = $rowaction;
                }
            }
        }
        foreach ($row['actions'] as $key => $value) {
            $row['actions'][$key]['netto'] = number_format($row['actions'][$key]['netto'], 2, ",", ".");
            $row['actions'][$key]['brutto'] = number_format($row['actions'][$key]['brutto'], 2, ",", ".");
        }
        $row['netto'] = number_format($sumkanetto, 2, ",", ".");
        $row['brutto'] = number_format($sumkabrutto, 2, ",", ".");
        $suma['netto']+= $sumkanetto;
        $suma['brutto']+= $sumkabrutto;
        $return[] = $row;
    }
}




// Create new PHPExcel object
echo date('H:i:s'), " Create new PHPExcel object", EOL;
$objPHPExcel = new PHPExcel();

// Set document properties
echo date('H:i:s'), " Set document properties", EOL;
$objPHPExcel->getProperties()->setCreator("E5")
        ->setLastModifiedBy("E5")
        ->setTitle("E5 - Report Sales")
        ->setSubject("E5 - Report Document")
        ->setDescription("Report Sales - description")
        ->setKeywords("e5, report, report 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',
        ),
    ),
);

$categoryStyle = array(
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
        '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' => '4E5044',
        ),
    ),
);

$subCategoryStyle = array(
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
        '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' => '8F937D',
        ),
    ),
);

$columnAlignRight = array(
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    ),
);

$columnAlignLeft = array(
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    ),
);

$objPHPExcel->setActiveSheetIndex(0)->getRowDimension('1')->setRowHeight(25);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(40);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(40);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A2:A4000")->applyFromArray($columnAlignLeft);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("B2:B4000")->applyFromArray($columnAlignLeft);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("C2:C4000")->applyFromArray($columnAlignLeft);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("D2:D4000")->applyFromArray($columnAlignLeft);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("E2:E4000")->applyFromArray($columnAlignLeft);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("F2:F4000")->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("G2:G4000")->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("H2:H4000")->applyFromArray($columnAlignRight);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A1:H1")->applyFromArray($headerStyle);

/* * ************************************************** */
/* * ******************* EXCELL Data ****************** */
/* * ************************************************** */
// Add header
$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', $mod_strings['LBL_WORKER_NAME'])
        ->setCellValue('B1', $mod_strings['LBL_ACTION_CODE'])//indeks czynnosc
        ->setCellValue('C1', $mod_strings['LBL_ACTION'])
        ->setCellValue('D1', $mod_strings['LBL_PRODUCT_CODE'])
        ->setCellValue('E1', $mod_strings['LBL_PRODUCTS'])
        ->setCellValue('F1', $mod_strings['LBL_QUANTITY'])
        ->setCellValue('G1', $mod_strings['LBL_SUM_NETTO'])
        ->setCellValue('H1', $mod_strings['LBL_SUM_BRUTTO']);


/* * ************************************************** */
/* * ******************* EXCELL Data ****************** */
/* * ************************************************** */
// Add all data
$lastRowNumber = 2;
$rowNumber = 2;

foreach ($return as $key => $item) {
    foreach($item['actions'] as $key1 => $item1){
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A' . ($rowNumber), $item['work_name'])
        ->setCellValue('B' . ($rowNumber), $item1['actioncode'])
        ->setCellValue('C' . ($rowNumber), $item1['actionname'])
        ->setCellValue('D' . ($rowNumber), $item1['productcode'])
        ->setCellValue('E' . ($rowNumber), $item1['productname'])
        ->setCellValue('F' . ($rowNumber), $item1['quantity'])
        ->setCellValue('G' . ($rowNumber), $item1['netto'])
        ->setCellValue('H' . ($rowNumber), $item1['brutto']);
    $rowNumber++;
    $lastRowNumber++;
    }

}



// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($mod_strings['LBL_REPORT_ECMWORKCARDS']);

// 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/ReportEcmWorkCards.xls");
?>