<?php

namespace Modules\Facture\Repository;

use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use PDO;

class ReglementFacture extends AbsRepository
{
    
    public function fetchAll($post, $numReg2Exclude = null, $dateTypeC = 0)
    {
        $req =
        "SELECT FAC_REG.NUM_FAC, FAC_REG.NUM_REG, FAC_REG.REF_REG, FAC_REG.DTE_REG, FAC_REG.MODE_REG, FAC_REG.MT_REG, FAC_REG.MT_REG_DEV, FAC_REG.DEVISE, FAC_REG.TYP_MT, FAC_REG.NM_COMMENT, MEMOS.CONTMEMO, FAC_ENT.CODE_FOUR, FAC_ENT.CODE_STE, FOURNISS.RS AS RS_FOURNISS, SOCIETE.RAIS_SOC AS RAIS_SOC
        FROM FAC_REG with (nolock) 
        LEFT JOIN MEMOS with (nolock) ON MEMOS.NODEMEMO = FAC_REG.NM_COMMENT 
        LEFT JOIN FAC_ENT WITH (NOLOCK) ON FAC_ENT.NUM_FACT = FAC_REG.NUM_FAC
        LEFT JOIN FOURNISS WITH (NOLOCK) ON FOURNISS.CODE = FAC_ENT.CODE_FOUR
        LEFT JOIN SOCIETE WITH (NOLOCK) ON SOCIETE.CODE_STE = FAC_ENT.CODE_STE
        WHERE 1=1";
        $arrayReq= [];
        
        if (isset($post['NUM_FACT']) &&  $post['NUM_FACT'] !='') {
            $req .=' AND NUM_FAC = :numFact';
            $arrayReq[':numFact'] = $post['NUM_FACT'];
        }
        
        if ($numReg2Exclude!=null) {
            $req .=' AND NUM_REG <> :numreg';
            $arrayReq[':numreg'] = $numReg2Exclude;
        }

        if ($dateTypeC!=0) {
            if ($numReg2Exclude!=null) {
                $req .=
                ' AND (DTE_REG >= :dateTypeC or NUM_REG in (
                    SELECT b.NUM_REG 
                    FROM FAC_REG b with (nolock) 
                    WHERE b.DTE_REG=\''.$dateTypeC.'\' AND b.NUM_REG>\''.$numReg2Exclude.'\'))';
            } else {
                $req .= ' AND DTE_REG > :dateTypeC';
            }
            
            $arrayReq[':dateTypeC'] = $dateTypeC;
        }
        
        if (isset($post['filter_NUM_FACT']) &&  $post['filter_NUM_FACT'] !='') {
            $req .=' AND NUM_FAC = :filter_NUM_FACT';
            $arrayReq[':filter_NUM_FACT'] = $post['filter_NUM_FACT'];
        }
        
        if (isset($post['filter_DTE_REG']) &&  $post['filter_DTE_REG'] !='') {
            \Helpers\Outil\Date::dateToSql($post['filter_DTE_REG']);
            $req .=' AND DTE_REG = :filter_DTE_REG';
            $arrayReq[':filter_DTE_REG'] = $post['filter_DTE_REG'];
        }
        
        if (isset($post['filter_REF_REG']) &&  $post['filter_REF_REG'] !='') {
            $req .=' AND REF_REG like :filter_REF_REG';
            $arrayReq[':filter_REF_REG'] = '%'.$post['filter_REF_REG'].'%';
        }
        
        
        if (isset($post['filter_TYP_MT']) &&  $post['filter_TYP_MT'] !='') {
            $req .=' AND TYP_MT = :filter_TYP_MT';
            $arrayReq[':filter_TYP_MT'] = $post['filter_TYP_MT'];
        }
        
        
        if (isset($post['filter_MODE_REG']) &&  $post['filter_MODE_REG'] !='') {
            $req .=' AND MODE_REG = :filter_MODE_REG';
            $arrayReq[':filter_MODE_REG'] = $post['filter_MODE_REG'];
        }
        
        
        if (isset($post['filter_DEVISE']) &&  $post['filter_DEVISE'] !='') {
            $req .=' AND DEVISE = :filter_DEVISE';
            $arrayReq[':filter_DEVISE'] = $post['filter_DEVISE'];
        }
        
        
        if (isset($post['filter_MT_REG']) &&  $post['filter_MT_REG'] !='') {
            $req .=' AND MT_REG = :filter_MT_REG';
            $arrayReq[':filter_MT_REG'] = $post['filter_MT_REG'];
        }
        
        if (isset($post['filter_MT_REG_DEV']) &&  $post['filter_MT_REG_DEV'] !='') {
            $req .=' AND MT_REG_DEV = :filter_MT_REG_DEV';
            $arrayReq[':filter_MT_REG_DEV'] = $post['filter_MT_REG_DEV'];
        }

        if (isset($post['filter_CODE_FOUR']) && $post['filter_CODE_FOUR'] != '') {
            $req .= ' AND FAC_ENT.CODE_FOUR = :filter_CODE_FOUR';
            $arrayReq[':filter_CODE_FOUR'] = $post['filter_CODE_FOUR'];
        }

        if (isset($post['filter_RS_FOURNISS']) && $post['filter_RS_FOURNISS'] != '') {
            $req .= ' AND FOURNISS.RS LIKE :filter_RS_FOURNISS';
            $arrayReq[':filter_RS_FOURNISS'] = '%' . $post['filter_RS_FOURNISS'] . '%';
        }

        if (isset($post['filter_RAIS_SOC']) && $post['filter_RAIS_SOC'] != '') {
            $req .= ' AND SOCIETE.RAIS_SOC LIKE :filter_RAIS_SOC';
            $arrayReq[':filter_RAIS_SOC'] = '%' . $post['filter_RAIS_SOC'] . '%';
        }

        if (isset($post['filter_CODE_STE']) && $post['filter_CODE_STE'] != '') {
            $req .= ' AND FAC_ENT.CODE_STE = :filter_CODE_STE';
            $arrayReq[':filter_CODE_STE'] = $post['filter_CODE_STE'];
        }

        // TODO refactorize code above into applyFilter(FilterTable)
        $this->applyFilter($req, $arrayReq, [
            'tri_NUM_FAC' => 'ASC',
            'tri_DTE_REG' => 'DESC',
            'tri_NUM_REG' => 'DESC',
        ]);

        (new PaginatorGestion())->injecterSqlCount($req, $post, $arrayReq);
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    public function getMaxNumReg($numFact)
    {
        $req = "SELECT max(NUM_REG) + 1 as NUM_REG FROM FAC_REG with (nolock) WHERE NUM_FAC = :numFac";
        $arrayReq= [':numFac' => $numFact];
        
        return $this->fetchAssocOne($req, $arrayReq);
    }
    
    public function getReglement($numFact, $numReg)
    {
        $req =
        "SELECT FAC_REG.NUM_FAC, FAC_REG.NUM_REG, FAC_REG.REF_REG, FAC_REG.DTE_REG, FAC_REG.MODE_REG, FAC_REG.MT_REG, FAC_REG.MT_REG_DEV, FAC_REG.DEVISE, FAC_REG.TYP_MT, FAC_REG.NM_COMMENT, MEMOS.CONTMEMO 
        FROM FAC_REG with (nolock) 
        LEFT JOIN MEMOS with (nolock) ON MEMOS.NODEMEMO = NM_COMMENT 
        WHERE NUM_FAC = :numFact AND NUM_REG = :numReg";
        $arrayReq= [
            ':numFact' => $numFact,
            ':numReg' => $numReg
        ];
        
        return $this->fetchAssocOne($req, $arrayReq);
    }
}
