<?php
namespace Modules\Catalogue\Repository;

use Helpers\QueryBuilder\AbsRepository;

class ChargementRepository extends AbsRepository
{
    public function searchDoublon():void
    {
        $req = "IF EXISTS (SELECT name FROM sysobjects WHERE name = 'TMP_TRACEBLK' AND type = 'U') DROP TABLE TMP_TRACEBLK 
                SELECT LEFT(FABRICANT,12) AS 'fabricant', LEFT(REFARTFAB,30) as 'refartfab', LEFT(REFARTFOU,30) as 'refartfou', LEFT(CODEFOUR,12) as 'codefour', LEFT(CODEARTI,13) as 'article' , COUNT(IDLIGNE) AS 'DOUBLON' 
                INTO TMP_TRACEBLK
                FROM CATABULK
                GROUP BY LEFT(FABRICANT,12), LEFT(REFARTFAB,30), LEFT(REFARTFOU,30), LEFT(CODEFOUR,12), LEFT(CODEARTI,13)
                HAVING COUNT(IDLIGNE) > 1
                ORDER BY COUNT(IDLIGNE) DESC";

        $this->execute($req, []);
    }

    public function extractDoublon():void
    {
        $req = "IF EXISTS (SELECT name FROM sysobjects WHERE name = 'TMP_TRACEBLK1' AND type = 'U') DROP TABLE TMP_TRACEBLK1 
                SELECT A.* INTO TMP_TRACEBLK1 FROM CATABULK A WITH (NOLOCK)  INNER JOIN TMP_TRACEBLK B WITH (NOLOCK)  ON 
                isnull(LEFT(A.FABRICANT,12),'') = ISNULL(B.fabricant,'') and isnull(LEFT(A.REFARTFAB,30),'') = isnull(B.refartfab,'') and isnull(LEFT(A.REFARTFOU,30),'') = isnull(B.refartfou,'') 
                and isnull(LEFT (A.CODEFOUR,12),'') = isnull(B.codefour,'')
                and isnull(LEFT(A.CODEARTI,13),'')= isnull(B.article,'')";

        $this->execute($req, []);
    }

    /**
     * @return array<mixed>
     */
    public function getDoublon()
    {
        $req = "SELECT REFARTFAB, REFARTFOU, FABRICANT, CODEFOUR, CODEARTI, IDLIGNE  FROM TMP_TRACEBLK1 WITH (NOLOCK)";

        return $this->fetchAssocAll($req, []);
    }

    public function deleteDoublon():void
    {
        $req = "DELETE FROM CATABULK
                WHERE IDLIGNE IN (SELECT IDLIGNE FROM TMP_TRACEBLK1)";

        $this->execute($req, []);
    }

    public function updateFournisseur(string $codeFrs = ''):void
    {
        $req = "UPDATE CATABULK SET CODEFOUR = :codeFou";

        $arrayReq = [':codeFou' => $codeFrs];

        $this->execute($req, $arrayReq);
    }

    public function updateDevise(string $devise):void
    {
        $req = "UPDATE CATABULK
                SET DEVISE_CAT = ISNULL((select DEVISE from FOURNISS WITH(NOLOCK)  WHERE FOURNISS.CODE=CATABULK.CODEFOUR),'')
                WHERE CATABULK.CODEFOUR <> '' AND ISNULL(DEVISE_CAT,'') = ''";

        $requete = $this->pdo->prepare($req);
        $requete->execute();
                
        $req = "UPDATE CATABULK
                SET DEVISE_CAT = :devise
                WHERE ISNULL(DEVISE_CAT,'') = ''";

        $arrayReq = [':devise' => $devise];

        $this->execute($req, $arrayReq);
    }

    /**
     * @param string $idFormat
     * @param string $idLigne
     * @return array<mixed>
     */
    public function getRegleByLigne(string $idFormat, string $idLigne)
    {
        $req = "with listeRegleValidation as  (
                    select * from REGVALID	with (nolock) where IDFORMAT=:idFormat and LGMAPPING=:idLigne
                ), valeurEgal as (
                    select COUNT(*) countValeurEgal from listeRegleValidation where REGCOMPAR= 'V'
                ), supperieur as (
                    select COUNT(*) countSupperieur from listeRegleValidation where REGCOMPAR= 'S'
                ), inferieur as (
                    select COUNT(*) countInferieur from listeRegleValidation where REGCOMPAR= 'I'
                ), entre as (
                    select COUNT(*) countEntre from listeRegleValidation where REGCOMPAR= 'E'
                ), liste as (
                    select COUNT(*) countListe from listeRegleValidation where REGCOMPAR= 'L'
                ) , listeAvecParenthense as (
                    select COUNT(*) countListeParenthese from listeRegleValidation where REGCOMPAR= 'L' and LEFT(TRIM(ZONE1),1) = '(' and RIGHT(TRIM(ZONE1),1) =')'
                )
                select * from valeurEgal, supperieur,inferieur,entre,liste,listeAvecParenthense";


        $arraReq = [
            ':idFormat' => $idFormat,
            ':idLigne'  => $idLigne
        ];

        return $this->fetchAssocOne($req, $arraReq);
    }

    /**
     * @return array<mixed>
     */
    public function getListeRegleListeWithParenthese(string $idFormat, string $idLigne)
    {
        $req = "select ZONE1 from REGVALID	with (nolock) 
                where IDFORMAT=:idFormat
                and LGMAPPING= :idLigne
                and REGCOMPAR= 'L' 
                and LEFT(TRIM(ZONE1),1) = '(' and RIGHT(TRIM(ZONE1),1) =')'";

        $arraReq = [
            ':idFormat' => $idFormat,
            ':idLigne'  => $idLigne
        ];

        return $this->fetchAssocAll($req, $arraReq);
    }

    public function RAZIdCatabulk():void
    {
        $req = "update CCATASRC
                set IDCATABLK = 0
                where IDCATABLK <> 0";

        $this->execute($req, []);
    }
}
