<?php

namespace YSquare\GiftCert\Controller\Admin;

use OxidEsales\Eshop\Core\Registry;

class GiftCertLogController extends \OxidEsales\Eshop\Application\Controller\Admin\AdminController
{

    protected $_sThisTemplate = 'ys_giftcert__admin_logs.tpl';

    protected $_aGiftcertList = null;
    protected $_aGiftcertListSize = null;

    protected $_aUsedGiftcertList = null;

    /**
     * @param $blOpen All giftcerts or only available giftcerts
     * @return mixed|null
     */
    public function getGiftcertList($blOpen = false) {
        $page = Registry::getRequest()->getRequestEscapedParameter("page") ?: 0;
        $numberPerPage = Registry::getRequest()->getRequestEscapedParameter("numberPerPage") ?: 100;
        $offset = $page * $numberPerPage;

        if(null === $this->_aGiftcertList) {
            $filter = $this->getFilters();
            $sQ = "SELECT oxid from ysgiftcert";

            $whereClause = "";
            if($blOpen or $filter["onlyWithValue"] === true){
                $whereClause .= " where ysrestdiscount > 0";
            }
            if(isset($filter["filterByCode"]) && trim($filter["filterByCode"]) != ""){
                if($whereClause != ""){
                    $whereClause .= " and ";
                } else {
                    $whereClause .= " where ";
                }
                $whereClause .= "ysgiftcertnr like '%". str_replace("'","",$filter["filterByCode"]) . "%'";
            }

            $sQ .= $whereClause;

            $sQ .= " order by ysdatepurchased DESC, ysgiftcertnr ASC limit {$numberPerPage} offset {$offset}";
            $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb(\OxidEsales\Eshop\Core\DatabaseProvider::FETCH_MODE_ASSOC);

            $rs = $oDb->select($sQ);
            if ($rs != false && $rs->count() > 0) {
                while (!$rs->EOF) {
                    $giftcert = oxNew(\YSquare\GiftCert\Model\GiftCert::class);
                    $giftcert->load($rs->fields['oxid']);
                    $this->_aGiftcertList[$rs->fields['oxid']] = $giftcert;
                    $rs->fetchRow();
                }
            }
        }
        return $this->_aGiftcertList;
    }

    public function getGiftcertListSize($blOpen = false) {
        if(null === $this->_aGiftcertListSize) {
            $filter = $this->getFilters();

            $sQ = "SELECT count(oxid) from ysgiftcert";

            $whereClause = "";
            if($blOpen or $filter["onlyWithValue"] === true){
                $whereClause .= " where ysrestdiscount > 0";
            }

            if(isset($filter["filterByCode"]) && trim($filter["filterByCode"]) != ""){
                if($whereClause != ""){
                    $whereClause .= " and ";
                } else {
                    $whereClause .= " where ";
                }
                $whereClause .= "ysgiftcertnr like '%". str_replace("'","",$filter["filterByCode"]) . "%'";
            }

            $sQ .= $whereClause;
            $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb(\OxidEsales\Eshop\Core\DatabaseProvider::FETCH_MODE_ASSOC);

            $this->_aGiftcertListSize = $oDb->getOne($sQ);
        }
        return $this->_aGiftcertListSize;
    }

    public function getPage() {
        $page = Registry::getRequest()->getRequestEscapedParameter("page") ?: 0;
        return $page;
    }

    public function getFilters() {
        $request = Registry::getRequest();
        $oSession = Registry::getSession();
        if($oSession->hasVariable("ysGiftcertAdminFilters")){
            $filters = $oSession->getVariable("ysGiftcertAdminFilters");
        } else {
            // Default
            $filters = ["onlyWithValue" => false];
        }
        return $filters;
    }

    public function getGiftcertFilter() {
        $oSession = Registry::getSession();
        if($oSession->hasVariable("ysGiftcertAdminFilters")) {
            $filters = $oSession->getVariable("ysGiftcertAdminFilters");
            if(isset($filters["filterByCode"]) && trim($filters["filterByCode"]) != ""){
                return $filters["filterByCode"];
            }
        }
        return false;
    }

    public function getUsedGiftcertList($monthCount = 24) {
        if(null === $this->_aUsedGiftcertList) {
            $sQ = "SELECT l.oxid, g.ysgiftcertnr, l.ysamountused, l.ysdateused from ysgiftcertlog l left join ysgiftcert g on l.ysgiftcertid = g.oxid";
            $sQ .= " where l.ysdateused > DATE_SUB(NOW(), INTERVAL $monthCount MONTH) order by l.ysdateused";

            $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb(\OxidEsales\Eshop\Core\DatabaseProvider::FETCH_MODE_ASSOC);

            $rs = $oDb->select($sQ);
            if ($rs != false && $rs->count() > 0) {
                while (!$rs->EOF) {
                    $this->_aUsedGiftcertList[$rs->fields['oxid']] = [$rs->fields['ysgiftcertnr'], $rs->fields['ysamountused'], $rs->fields['ysdateused']];
                    $rs->fetchRow();
                }
            }
        }
        return $this->_aUsedGiftcertList;
    }

    public function getCustomerStringById($custId) {
        $customer = oxNew(\OxidEsales\Eshop\Application\Model\User::class);
        if($customer->load($custId)) {
            return $customer->oxuser__oxcustnr->value;
        } else {
            return "N/A";
        }
    }

    public function getOrderStringById($orderId) {
        $order = oxNew(\OxidEsales\Eshop\Application\Model\Order::class);
        if($order->load($orderId)) {
            return $order->oxorder__oxordernr->value;
        } else {
            return "N/A";
        }
    }


    public function canBeRedeemedPartly() {
        return Registry::getConfig()->getConfigParam('blYsGiftcertRedeemPartly');
    }

    public function shouldRedeemToPool() {
        // Only available if redeemPartly is allowed
        if($this->canBeRedeemedPartly()) {
            return Registry::getConfig()->getConfigParam('blYsGiftcertRedeemToPool');
        }
        return false;
    }

    public function getGiftcertSummary(){
        $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb();
        $sql = "select sum(ysrestdiscount) from ysgiftcert";
        return $oDb->getOne($sql);
    }

    public function getGiftcertOpenCredits(){
        $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb();
        $sql = "SELECT sum(ys_giftcert_pool_amount) from oxuser";
        return $oDb->getOne($sql);
    }

    public function getGiftcertCount(){
        $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb();
        $sql = "select count(*) from ysgiftcert where ysrestdiscount > 0";
        return $oDb->getOne($sql);
    }

    public function exportOpenGiftcerts(){
        $giftcerts = $this->getOpenGiftcerts();
        if ($giftcerts) {
            $csv = new \ParseCsv\Csv();
            $csv->linefeed = "\n";
            $header = array('code', 'amount', 'valid until');
            $csv->output('open_giftcerts_' . date('Ymd') .'.csv', $giftcerts, $header, ';');
        }
        die();
    }

    public function exportUsedGiftcerts(){
        $giftcerts = $this->getUsedGiftcerts();
        if ($giftcerts) {
            $csv = new \ParseCsv\Csv();
            $csv->linefeed = "\n";
            $header = array('code', 'amount', 'date used');
            $csv->output('used_giftcerts_' . date('Ymd') .'.csv', $giftcerts, $header, ';');
        }
        die();
    }

    public function exportUserGiftcerts(){
        $giftcerts = $this->getUserGiftcerts();
        if ($giftcerts) {
            $csv = new \ParseCsv\Csv();
            $csv->linefeed = "\n";
            $header = array('email', 'amount');
            $csv->output('user_giftcert_amount_' . date('Ymd') .'.csv', $giftcerts, $header, ';');
        }
        die();
    }

    public function exportGeneratedGiftcerts(){
        $oSession = Registry::getSession();
        $codes = $oSession->getVariable("generatedCode");
        if ($codes) {
            $giftcerts = $this->getGiftcertsByCode($codes);
            $csv = new \ParseCsv\Csv();
            $csv->linefeed = "\n";
            $header = array('date', 'code', 'amount');
            $csv->output('generated_giftcerts_' . date('Ymd') .'.csv', $giftcerts, $header, ';');
        }
        die();
    }

    protected function getOpenGiftcerts()
    {
        $oGiftcertList = $this->getGiftcertList(true);
        $giftcerts = array();
        if($oGiftcertList){
            foreach($oGiftcertList as $oGiftcert){
                $giftcerts[] = array($oGiftcert->ysgiftcert__ysgiftcertnr->value, $oGiftcert->ysgiftcert__ysrestdiscount->value, $oGiftcert->getGiftcertExpirationDate());
            }
        }

        return $giftcerts;
    }

    protected function getUsedGiftcerts()
    {
        $giftcerts = $this->getUsedGiftcertList();

        return $giftcerts;
    }

    protected function getUserGiftcerts()
    {
        $sQ = "SELECT oxusername, ys_giftcert_pool_amount from oxuser where ys_giftcert_pool_amount > 0";

        $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb(\OxidEsales\Eshop\Core\DatabaseProvider::FETCH_MODE_ASSOC);

        $giftcerts = array();
        $rs = $oDb->select($sQ);
        if ($rs != false && $rs->count() > 0) {
            while (!$rs->EOF) {
                $giftcerts[] = array($rs->fields['oxusername'], $rs->fields['ys_giftcert_pool_amount']);
                $rs->fetchRow();
            }
        }

        return $giftcerts;
    }

    protected function getGiftcertsByCode($codes)
    {
        $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb(\OxidEsales\Eshop\Core\DatabaseProvider::FETCH_MODE_ASSOC);
        if($codes && is_array($codes)){
            $inCodes = "";
            foreach($codes as $code){
                if(!empty($inCodes)){
                    $inCodes .= ", ";
                }
                $inCodes .= $oDb->quote($code);
            }
        }else{
            return;
        }
        $sQ = "SELECT ysdatepurchased, ysgiftcertnr, ysrestdiscount from ysgiftcert where ysgiftcertnr in ($inCodes)";

        $giftcerts = array();
        $rs = $oDb->select($sQ);
        if ($rs != false && $rs->count() > 0) {
            while (!$rs->EOF) {
                $giftcerts[] = array($rs->fields['ysdatepurchased'], $rs->fields['ysgiftcertnr'], $rs->fields['ysrestdiscount']);
                $rs->fetchRow();
            }
        }

        return $giftcerts;
    }
}