<?php

namespace KupShop\ElninoBundle\Admin;

use Query\Operator;

class ExportSales
{
    private function getHeader()
    {
        return [
            'date_created',
            'count(invoice_email)',
            'sum(total_price)',
        ];
    }

    private function setWidthOfColumns(&$objPHPExcel)
    {
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    }

    private function getData($dateFrom, $dateTo, $user_group)
    {
        $qb = sqlQueryBuilder()->select('DATE(o.date_created) AS `date`, COUNT(DISTINCT o.invoice_email) AS `count`, SUM(o.total_price) AS `sum`')
            ->from('orders', 'o')
            ->join('o', 'users', 'u', 'u.email = o.invoice_email')
            ->join('u', 'users_groups_relations', 'ugr', 'ugr.id_user = u.id')
            ->where(Operator::between('DATE(o.date_created)', new \Range($dateFrom, $dateTo)))
            ->andWhere(Operator::equals(['ugr.id_group' => $user_group]))
            ->andWhere('o.status_storno <> 1')
            ->groupBy('DATE(o.date_created)');
        $data = $qb->execute()->fetchAll();
        $sheet = (empty($data) ? [] : $data);

        return $sheet;
    }

    public function exportXLS()
    {
        $dateFrom = getVal('dateFrom');
        $dateTo = getVal('dateTo');
        $user_group = getVal('user_group');

        ini_set('memory_limit', '512M');

        $from = $this->prepdate($dateFrom);
        $to = $this->prepdate($dateTo);
        $sheet = array_merge([$this->getHeader()], $this->getData($from, $to, $user_group));

        global $cfg;

        include $cfg['Path']['shared_class'].'PHPExcel/CachedObjectStorageFactory.php';
        include $cfg['Path']['shared_class'].'PHPExcel/Settings.php';

        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
        $cacheSettings = ['memoryCacheSize' => 10];

        \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

        $doc = new \PHPExcel();
        $doc->setActiveSheetIndex(0);
        $doc->getActiveSheet()->fromArray($sheet, null, 'A1');
        $this->setWidthOfColumns($doc);

        $objWriter = \PHPExcel_IOFactory::createWriter($doc, 'Excel5');

        $filename = 'export_sales-gr'.$user_group.'-'.$from.'-'.$to.'.xls';
        header('Content-type: application/xls');
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        $objWriter->save('php://output');

        writeDownActivity("Export prodeje: {$filename}");
        exit;
    }

    protected function prepdate($date)
    {
        $date = new \DateTime($date);

        return $date->format('Y-m-d');
    }
}