<?php

include 'requires.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


class dynamicsController
{

    const TYP_MM = 9,
         TYP_PZ = 10,
        TYP_WZ = 11,
        TYP_PW = 12;

    private $sl_cechy;

    public function __construct()
    {
        $this->sl_cechy = $this->getSlCechy();
    }

    private function getSlCechy(){

        $db = new connectionController;
        $pdo = $db->getPdo();

        $cechy = $pdo->query("SELECT s.* FROM dbo.sl_CechaTw s ");
        $cechy = $cechy->fetchAll();

        $filtered = [];

        foreach ($cechy as $cecha){

            $filtered[$cecha['ctw_Id']] = $cecha['ctw_Nazwa'];

        }

        return $filtered;

    }

    public function indexAction()
    {

        $db = new connectionController;
        $pdo = $db->getPdo();
        $tw = new twigController;
        $twig = $tw->getTwig();


        $uzyte_cechy = [];
        $timestamp = null;
        $generated = false;

        $format = 'xlsx';

        $sprzedazWmagazynie = [];
        $filterName = '';
        $filterSymbol = '';
        $magazynyIds = [];

        $docTypesIds = [2, 21];

        $towary = [];
        $today = new DateTime('now');
        $dateFrom = (isset($_POST['dateFrom'])) ? $_POST['dateFrom'] : $today->format('Y-m-d');
        $dateTo = (isset($_POST['dateTo'])) ? $_POST['dateTo'] : $today->modify('+7 days')->format('Y-m-d');
        $grupa = (isset($_POST['grupa'])) ? $_POST['grupa'] : 0;
        $cecha = (isset($_POST['cecha'])) ? $_POST['cecha'] : 0;


        $label = '';
        $sprzedaz = 0;
        $stan = 0;

        if(isset($_POST['sMagazyny'])){
            $magazynyIds = array_merge($magazynyIds, $_POST['sMagazyny']);
        }
        if(isset($_POST['sTypy'])){
            $docTypesIds =$_POST['sTypy'];
        }

        $where = '';

        if($grupa > 0){
            $where .= "AND t.tw_IdGrupa = ".$_POST['grupa']." ";
        }

//        if($cecha > 0){
//           $where .= "AND c.cht_IdCecha = ".$_POST['cecha']." ";
//        }


        if(isset($_POST['nameText']) && strlen($_POST['nameText']) > 0){
            $filterName = $_POST['nameText'];
            if($_POST['nameHow'] == 1){
                $where .= "AND t.tw_Nazwa LIKE '%".$_POST['nameText']."%'";
            }
            elseif($_POST['nameHow'] == 2){
                $where .= "AND t.tw_Nazwa LIKE '".$_POST['nameText']."%'";
            }
        }
        if(isset($_POST['symbolText']) && strlen($_POST['symbolText']) > 0){
            $filterSymbol = $_POST['symbolText'];
            if($_POST['symbolHow'] == 1){
                $where .= "AND t.tw_Symbol LIKE '%".$_POST['symbolText']."%'";
            }
            elseif($_POST['symbolHow'] == 2){
                $where .= "AND t.tw_Symbol LIKE '".$_POST['symbolText']."%'";
            }
        }
        if(isset($_POST['renderTable']) && $_POST['renderTable'] == 1) {


            $objects = $pdo->query("SELECT t.tw_Id, t.tw_Nazwa, t.tw_Symbol
                                    FROM dbo.tw__Towar t 
                                    WHERE t.tw_Id >= 0 ".$where." ORDER BY t.tw_Nazwa ");
            $objects = $objects->fetchAll();


            $objectsIds = [];
            foreach ($objects as $object){
                $objectsIds[] = $object['tw_Id'];
            }



            if(count($objectsIds) > 0){

                $cechy_all = $pdo->query("SELECT sa.* FROM dbo.tw_CechaTw sa WHERE sa.cht_IdTowar IN(".implode(',', array_unique($objectsIds)).") ");
                $cechy_all = $cechy_all->fetchAll();


                $towar_cecha = [];
                $uzyte_cechy = [];

                foreach($cechy_all as $c){
                    if(!array_key_exists($c['cht_IdTowar'], $towar_cecha)){
                        $towar_cecha[$c['cht_IdTowar']] = [];
                    }

                    $towar_cecha[$c['cht_IdTowar']][] = $c['cht_IdCecha'];

                    if(!in_array($c['cht_IdCecha'], $uzyte_cechy)){
                        $uzyte_cechy[] = $c['cht_IdCecha'];
                    }
                }


                $pozycje = $pdo->query("SELECT p.ob_TowId, p.ob_IloscMag, d.dok_MagId 
                                          FROM dbo.dok_Pozycja p 
                                          LEFT JOIN dbo.dok__Dokument d ON p.ob_DokHanId = d.dok_Id
                                          WHERE p.ob_TowId IN(".implode(',', array_unique($objectsIds)).") AND d.dok_Typ IN(".implode(',', $docTypesIds).") AND d.dok_DataWyst BETWEEN '$dateFrom' AND '$dateTo' ");
                $pozycje = $pozycje->fetchAll();


                $sprzedaz = [];

                $sprzedazWmagazynie = [];

                foreach ($pozycje as $pozycja) {
                    if (!array_key_exists($pozycja['ob_TowId'], $sprzedaz)) {
                        $sprzedaz[$pozycja['ob_TowId']] = [];
                        $sprzedaz[$pozycja['ob_TowId']]['all_magazines'] = 0;
                        $sprzedaz[$pozycja['ob_TowId']]['selected_magazines'] = 0;
                    }
                    if (!array_key_exists($pozycja['dok_MagId'], $sprzedaz[$pozycja['ob_TowId']])) {
                        $sprzedaz[$pozycja['ob_TowId']][$pozycja['dok_MagId']] = 0;
                    }
                    if (!array_key_exists($pozycja['dok_MagId'], $sprzedazWmagazynie)) {
                        $sprzedazWmagazynie[$pozycja['dok_MagId']] = 0;
                    }
                    $sprzedaz[$pozycja['ob_TowId']][$pozycja['dok_MagId']] += $pozycja['ob_IloscMag'];
                    $sprzedaz[$pozycja['ob_TowId']]['all_magazines'] += $pozycja['ob_IloscMag'];

                    if(in_array($pozycja['dok_MagId'], $magazynyIds)){
                        $sprzedaz[$pozycja['ob_TowId']]['selected_magazines'] += $pozycja['ob_IloscMag'];
                    }

                    $sprzedazWmagazynie[$pozycja['dok_MagId']] += $pozycja['ob_IloscMag'];
                }

                $stany = [];

                $stan = $pdo->query("SELECT st_TowId, st_MagId, st_Stan, st_StanRez FROM dbo.tw_Stan WHERE st_MagId IN(".implode(',', $magazynyIds).") AND st_TowId IN(".implode(',', array_unique($objectsIds)).") ");
                $stan = $stan->fetchAll();

                foreach($stan as $s){

                    if(!array_key_exists($s['st_TowId'], $stany)){

                        $stany[$s['st_TowId']] = 0;

                    }

                    $tmpStan = $s['st_Stan'] - $s['st_StanRez'];

                    $stany[$s['st_TowId']] += $tmpStan;

                }


                $magDocTypesIds = [self::TYP_PZ, self::TYP_WZ, self::TYP_PW];

                $pzs = $pdo->query("SELECT p.ob_TowId, p.ob_IloscMag, d.dok_MagId, d.dok_DataWyst, d.dok_Typ, d.dok_PodTyp 
                                          FROM dbo.dok_Pozycja p 
                                          LEFT JOIN dbo.dok__Dokument d ON p.ob_DokMagId = d.dok_Id
                                          WHERE p.ob_TowId IN(".implode(',', array_unique($objectsIds)).") 
                                          AND d.dok_Typ IN(".implode(',', $magDocTypesIds).")
                                          AND d.dok_DataWyst BETWEEN '$dateFrom' AND '$dateTo'
                                          ORDER BY d.dok_DataWyst");
                $pzs = $pzs->fetchAll();


                // pz and mm  655360,655361,655362,2293760,-1
                // mm -1


                $pzAndMm = $pdo->query("SELECT tw_Id, tw_Symbol, SUM(ob_IloscMag ) as ob_IloscMag
                                                    FROM vwZstPrzWgKhnt 
                                                    LEFT JOIN tw__Towar ON ob_TowId = tw_Id                                                   
                                                    WHERE dok_Status <> 2  
                                                    AND dok_DataWyst BETWEEN '$dateFrom' AND '$dateTo'                                                 
                                                    AND (
                                                    (dbo.fnMAKE_DOKPARAM(dok_Typ, dok_Podtyp) IN (-1) 
                                                    AND  dok_MagId IN(".implode(',', $magazynyIds).") 
                                                    ) OR 
                                                    (  dbo.fnMAKE_DOKPARAM(dok_Typ, dok_Podtyp) IN (589824) 
                                                    AND dok_OdbiorcaId IN(".implode(',', $magazynyIds).") 
                                                    )
                                                    )  
                                                    AND tw_Rodzaj<>16
                                                    AND ob_TowId IN(".implode(',', array_unique($objectsIds)).") 
                                                    GROUP BY tw_Id, tw_Rodzaj, tw_Nazwa, tw_Symbol, tw_JednMiary ");
                $pzAndMm = $pzAndMm->fetchAll();

                $docsPzAndMm = [];

                foreach ($pzAndMm as $doc){

                    if (!array_key_exists($doc['tw_Id'], $docsPzAndMm)) {

                        $docsPzAndMm[$doc['tw_Id']] = [];
                        $docsPzAndMm[$doc['tw_Id']]['oldest'] = "";
                        $docsPzAndMm[$doc['tw_Id']]['sum'] = 0;
                        $docsPzAndMm[$doc['tw_Id']]['sum_magazines'] = 0;

                    }

                    $docsPzAndMm[$doc['tw_Id']]['sum'] += $doc['ob_IloscMag'];
                    $docsPzAndMm[$doc['tw_Id']]['sum_magazines'] += $doc['ob_IloscMag'];

                }


                $docsPz = [];
                $docsPw = [];
                $docsWz = [];
                $docType = '';

                foreach ($pzs as $pz){

                    if($pz['dok_Typ'] == self::TYP_PZ){

                        $docType = "docsPz";

                    }

                    elseif($pz['dok_Typ'] == self::TYP_PW){

                        $docType = "docsPw";

                    }

                    elseif($pz['dok_Typ'] == self::TYP_WZ){

                        $docType = "docsWz";

                    }

                    if (!array_key_exists($pz['ob_TowId'], $$docType)) {

                        $date =  new DateTime($pz['dok_DataWyst']);

                        $$docType[$pz['ob_TowId']] = [];
                        $$docType[$pz['ob_TowId']]['oldest'] = $date->format('Y-m-d');
                        $$docType[$pz['ob_TowId']]['sum'] = 0;
                        $$docType[$pz['ob_TowId']]['sum_magazines'] = 0;
                    }

                    $$docType[$pz['ob_TowId']]['sum'] += $pz['ob_IloscMag'];

                    if(in_array($pz['dok_MagId'], $magazynyIds)){

                        $$docType[$pz['ob_TowId']]['sum_magazines'] += $pz['ob_IloscMag'];

                    }



                }

            }

            $sprzedazWedlugSymbolu = [];

            $spreadsheet = new Spreadsheet();

            $sheet = $spreadsheet->getActiveSheet();


            $sheet->setCellValueByColumnAndRow(1, 1, "Symbol");
            $sheet->setCellValueByColumnAndRow(2, 1, "Nazwa");
            $sheet->setCellValueByColumnAndRow(3, 1, "Najstarsze Pz");
            $sheet->setCellValueByColumnAndRow(4, 1,"Suma Pz");
            $sheet->setCellValueByColumnAndRow(5, 1,"Suma Pz wybrane");
            $sheet->setCellValueByColumnAndRow(6, 1,"Suma Mm wybrane");
            $sheet->setCellValueByColumnAndRow(7, 1,"Suma Pw wybrane");
            $sheet->setCellValueByColumnAndRow(8, 1, "Suma Wz");
            $sheet->setCellValueByColumnAndRow(9, 1, "Suma Wz wybrane");
            $sheet->setCellValueByColumnAndRow(10, 1, "Stan wybrane");
            $sheet->setCellValueByColumnAndRow(11, 1, "Suma sprzedaży");
            $sheet->setCellValueByColumnAndRow(12, 1, "Suma sprzedaży wybrane magazyny");


            $i = 13;
            foreach ($uzyte_cechy as $uc){

                $sheet->setCellValueByColumnAndRow($i, 1, $this->sl_cechy[$uc]);
                $sheet->setCellValueByColumnAndRow($i + 1, 1, "Stan");

                $i = $i + 2;
            }



            $i = 1;
            foreach ($objects as $object) {

                $i++;
                $towarId = $object['tw_Id'];

                if (!array_key_exists($towarId, $towary)) {
                    $towary[$towarId]['id'] = $object['tw_Id'];
                    $towary[$towarId]['nazwa'] = $object['tw_Nazwa'];
                    $towary[$towarId]['symbol'] = $object['tw_Symbol'];
                    $towary[$towarId]['all_magazines'] = isset($sprzedaz[$towarId]['all_magazines']) ? $sprzedaz[$towarId]['all_magazines'] : 0;
                    $towary[$towarId]['selected_magazines'] = isset($sprzedaz[$towarId]['selected_magazines']) ? $sprzedaz[$towarId]['selected_magazines'] : 0;
                    $towary[$towarId]['oldest_pz'] = isset($docsPz[$towarId]['oldest']) ? $docsPz[$towarId]['oldest'] : 'n/a';
                    $towary[$towarId]['sum_pz'] = isset($docsPz[$towarId]['sum']) ? $docsPz[$towarId]['sum'] : 0;
                    $towary[$towarId]['features'] = isset($towar_cecha[$towarId]) ? $towar_cecha[$towarId] : null;

                    $towary[$towarId]['pz_magazines'] = isset($docsPz[$towarId]['sum_magazines']) ? $docsPz[$towarId]['sum_magazines'] : 0;

                    $towary[$towarId]['pw_magazines'] = isset($docsPw[$towarId]['sum_magazines']) ? $docsPw[$towarId]['sum_magazines'] : 0;

                    $towary[$towarId]['mm_magazines'] =  isset($docsPzAndMm[$towarId]['sum_magazines']) ? $docsPzAndMm[$towarId]['sum_magazines'] : 0;

                    $towary[$towarId]['wz_all'] = isset($docsWz[$towarId]['sum']) ? $docsWz[$towarId]['sum'] : 0;

                    $towary[$towarId]['wz_magazines'] = isset($docsWz[$towarId]['sum_magazines']) ? $docsWz[$towarId]['sum_magazines'] : 0;

                    $towary[$towarId]['stan'] = isset($stany[$towarId]) ? $stany[$towarId] : 0;

                    $sheet->setCellValueByColumnAndRow(1, $i, $towary[$towarId]['symbol']);
                    $sheet->setCellValueByColumnAndRow(2, $i, $towary[$towarId]['nazwa']);
                    $sheet->setCellValueByColumnAndRow(3, $i, $towary[$towarId]['oldest_pz']);
                    $sheet->setCellValueByColumnAndRow(4, $i, $towary[$towarId]['sum_pz']);
                    $sheet->setCellValueByColumnAndRow(5, $i, $towary[$towarId]['pz_magazines']);
                    $sheet->setCellValueByColumnAndRow(6, $i, $towary[$towarId]['mm_magazines']);
                    $sheet->setCellValueByColumnAndRow(7, $i, $towary[$towarId]['pw_magazines']);
                    $sheet->setCellValueByColumnAndRow(8, $i, $towary[$towarId]['wz_all']);
                    $sheet->setCellValueByColumnAndRow(9, $i, $towary[$towarId]['wz_magazines']);
                    $sheet->setCellValueByColumnAndRow(10, $i, $towary[$towarId]['stan']);
                    $sheet->setCellValueByColumnAndRow(11, $i, $towary[$towarId]['all_magazines']);
                    $sheet->setCellValueByColumnAndRow(12, $i, $towary[$towarId]['selected_magazines']);

                    $j = 13;
                    foreach ($uzyte_cechy as $uc){

                        if(is_array($towary[$towarId]['features']) && in_array($uc, $towary[$towarId]['features'])){

                            $sheet->setCellValueByColumnAndRow($j, $i, 1);
                            $sheet->setCellValueByColumnAndRow($j + 1, $i, $towary[$towarId]['stan']);

                        }

                        $j = $j + 2;
                    }

                }

            }

            $tdate = new DateTime();
            $timestamp = $tdate->getTimestamp();

            if (!is_dir("xlsx")) {
                mkdir("xlsx", 0774, true);
            }

            if (!is_dir("xlsx/dynamics")) {
                mkdir("xlsx/dynamics", 0774, true);
            }

            $format = $_POST['format'];

            if($format == 'ods'){
                $writer = new \PhpOffice\PhpSpreadsheet\Writer\Ods($spreadsheet);
            }else{
                $writer = new Xlsx($spreadsheet);
            }

            $writer->save("xlsx/dynamics/dynamics_$timestamp.$format");

            $generated = true;


        }


        $magazyny = $pdo->query("SELECT m.* FROM dbo.sl_Magazyn m ");
        $magazyny = $magazyny->fetchAll();

        $magazynyNazwy = [];

        foreach($magazyny as $magazyn){

            $magazynyNazwy[$magazyn['mag_Id']] = $magazyn['mag_Symbol'];

        }

        $cechy = $pdo->query("SELECT c.* FROM dbo.sl_CechaTw c");
        $cechy = $cechy->fetchAll();

        $grupy = $pdo->query("SELECT g.* FROM dbo.sl_GrupaTw g");
        $grupy = $grupy->fetchAll();


        $filters = [
            'magazynyIds' => $magazynyIds,
            'nameText' => $filterName,
            'nameHow' => (isset($_POST['nameHow'])) ? $_POST['nameHow'] : 1,
            'symbolText' => $filterSymbol,
            'symbolHow' => (isset($_POST['symbolHow'])) ? $_POST['symbolHow'] : 1,
            'docTypesIds' => $docTypesIds,
            'dateFrom' => $dateFrom,
            'dateTo' => $dateTo,
            'grupa' => $grupa,
            'cecha' => $cecha
        ];

        echo $twig->render('dynamics/index.html.twig', ['towary' => $towary,
            'magazynyIds' => $magazynyIds,
            'dTypy' => $this->getDocTypes(),
            'filters' => $filters,
            'magazyny' => $magazyny,
            'magazynyNazwy' => $magazynyNazwy,
            'cechy' => $cechy,
            'grupy' => $grupy,
            'label' => $label,
            'sprzedaz' => $sprzedaz,
            'stan' => $stan,
            'uzyte_cechy' => $uzyte_cechy,
            'sl_cechy' => $this->sl_cechy,
            'link' => "xlsx/dynamics/dynamics_$timestamp.$format",
            'generated' => $generated
            ]);

    }

    private function getDocTypes(){

        $docTypes[1] = ['short' => 'FS', 'name' => 'Faktura VAT sprzedaży', 'id' => 2];
        //$docTypes[2] = ['short' => 'FSzal', 'name' => 'Faktura sprzedaży zaliczkowa', 'id' => ];
        //$docTypes[3] = ['short' => 'FSz', 'name' => 'Faktura sprzedaży zbiorcza', 'id' => ];
        $docTypes[4] = ['short' => 'FM', 'name' => 'Faktura sprzedaży marżą', 'id' => 62];
        $docTypes[5] = ['short' => 'RS', 'name' => 'Rachunek sprzedaży', 'id' => 4];
        $docTypes[6] = ['short' => 'KFS', 'name' => 'Korekta faktury VAT sprzedaży', 'id' => 6];
        //$docTypes[7] = ['short' => 'KFSn', 'name' => 'Korekta faktury sprzedaży do nieistniejącego', 'id' => ];
        //$docTypes[8] = ['short' => 'KFSzc', 'name' => 'Korekta faktury sprzedaży zaliczkowej', 'id' => ];
        $docTypes[9] = ['short' => 'KFM', 'name' => 'Korekta faktury VAT marżą', 'id' => 67];
        //$docTypes[10] = ['short' => 'KFMn', 'name' => 'Korekta faktury marża do nieistniejącego', 'id' => ];
        //$docTypes[11] = ['short' => 'FSd', 'name' => 'Faktura sprzedaży detaliczna', 'id' => ];
        $docTypes[12] = ['short' => 'PA', 'name' => 'Paragon', 'id' => 21];
        //$docTypes[13] = ['short' => 'PAi', 'name' => 'Paragon imienny', 'id' => ];
        //$docTypes[14] = ['short' => 'PAf', 'name' => 'Paragoon fiskalny', 'id' => ];
        //$docTypes[15] = ['short' => 'PAk', 'name' => 'Paragon odebrany z kasy', 'id' => ];
        $docTypes[16] = ['short' => 'ZW', 'name' => 'Zwrot ze sprzedaży detalicznej', 'id' => 14];
        //$docTypes[17] = ['short' => 'ZWn', 'name' => 'Zwrot do nieistniejącego', 'id' => ];


        return $docTypes;

    }

    private function print_mem()
    {
        /* Currently used memory */
        $mem_usage = memory_get_usage();

        /* Peak memory usage */
        $mem_peak = memory_get_peak_usage();

        echo 'The script is now using: <strong>' . round($mem_usage / 1024 / 1024) . 'MB</strong> of memory.<br>';
        echo 'Peak usage: <strong>' . round($mem_peak / 1024 / 1024) . 'MB</strong> of memory.<br><br>';
    }

}


?>
