<?php

namespace Modules\Additional\Specifique\Client158\Modules\EtatStatistique\Repository;

use Entity\Model\Periodes;
use Helpers\Filter\FilterTable;
use Helpers\QueryBuilder\AbsRepository;
use PDO;

class Reporting extends AbsRepository
{
    /**
     * budgetMonitoring
     *
     * @param  string $user
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function budgetMonitoring(string $user, string $periodId, string $uoList, array $post = []): array
    {
        $period = (new Periodes($this->pdo))->find($periodId);
        if (empty($period)) {
            return [];
        }

        $query =
        "SELECT DISTINCT 
            b1.LIBELLE liba1, 
            b1.ENT_AXE_UN AS CODE_SI,
            cs2.VALEUR responsable_si, 
            h.NOM, 
            b12.AXE_DEUX, 
            b12.LIBELLE liba2, 
            b1v.CODE_PER, 
            bc.BUDGE, 
            (SELECT SUM(MON_ENG+MON_FAC) FROM BUD_CUM WITH(NOLOCK) WHERE SI=b1v.AXE_UN AND 
                (SCA IN (SELECT b12v2.AXE_DEUX FROM BUD_12_VAL b12v2 WITH(NOLOCK)
                WHERE b12.CODE_BUD = b12v2.CODE_BUD
                    AND b12.CODE_SOC = b12v2.CODE_SOC
                    AND b12.CODE_PER = b12v2.CODE_PER
                    AND b12.IND_REVI = b12v2.IND_REVI
                    AND b12.AXE_UN = b12v2.ENT_AXE_UN
                    AND b12.AXE_DEUX = b12v2.ENT_AXE_DEUX)
                OR EXISTS (SELECT b12v2.AXE_DEUX FROM BUD_12_VAL b12v2 WITH(NOLOCK)
                WHERE b12.CODE_BUD = b12v2.CODE_BUD
                    AND b12.CODE_SOC = b12v2.CODE_SOC
                    AND b12.CODE_PER = b12v2.CODE_PER
                    AND b12.IND_REVI = b12v2.IND_REVI
                    AND b12.AXE_UN = b12v2.ENT_AXE_UN
                    AND b12.AXE_DEUX = b12v2.ENT_AXE_DEUX
                    AND b12v2.AXE_DEUX='*')
                )
            ) CONSOMME,
            caljoin.NO_DA, 
            caljoin.NO_PO_DA, 
            caljoin.suivi_projet, 
            caljoin.NO_CA, 
            caljoin.NO_PO_CA, 
            caljoin.SSCPTEANA,
            sca.LIBELLE AS SCA_LABEL, 
            caljoin.CODE_FOU, 
            f.RS, 
            caljoin.CODE_ART, 
            caljoin.DES_SOMM, 
            caljoin.NM_DCOMP, 
            caljoin.MONTANT_HT, 
            FACTURE.DATE_ENR, 
            FACTURE.NUM_FACT, 
            FACTURE.FACTURE, 
            REPLACE(REPLACE(m.CONTMEMO, CHAR(10), ' '), CHAR(13), ' ') AS CONTMEMO,
            si.LIBELLE AS SI_LABEL,
            dern_livr.date_dern_livr,
            a.DESIGN AS LIBELLE_ARTICLE
        FROM BUD_1_VAL b1v
        JOIN PERIODES 
            ON PERIODES.CODE_PER = :periode
            AND PERIODES.CODE_PER = b1v.CODE_PER
        JOIN SOCIETE ON SOCIETE.CODE_STE = :soc
        JOIN REV_BUDG
            ON REV_BUDG.IND_REV = b1v.IND_REVI
            AND REV_BUDG.COD_SOC = SOCIETE.CODE_STE
            AND REV_BUDG.COD_PER = PERIODES.CODE_PER
            AND REV_BUDG.COD_BUD = PERIODES.CODE_PER
            AND REV_BUDG.ACTI = 0x54
        LEFT JOIN BUD_1 b1
            ON b1.CODE_BUD = b1v.CODE_BUD
                AND b1.CODE_SOC = b1v.CODE_SOC
                AND b1.CODE_PER = b1v.CODE_PER
                AND b1.IND_REVI = b1v.IND_REVI
                AND b1.ENT_AXE_UN = b1v.ENT_AXE_UN
        LEFT JOIN BUD_12 b12
            ON b12.CODE_BUD = b1v.CODE_BUD
            AND b12.CODE_SOC = b1v.CODE_SOC
            AND b12.CODE_PER = b1v.CODE_PER
            AND b12.IND_REVI = b1v.IND_REVI
            AND b12.AXE_UN = b1v.ENT_AXE_UN
        LEFT JOIN SECTIMPU si with(nolock)
            ON si.CODE_SI = b1.ENT_AXE_UN
            AND (si.CODE_STE = SOCIETE.CODE_STE OR si.CODE_STE = '')
        LEFT JOIN CS_DON cs2 with(nolock)
            ON cs2.COD_TAB = 'SECTIMPU' 
                AND b1v.AXE_UN = SUBSTRING(cs2.COD_ENR, 8, 14) 
                AND cs2.NUM_FIX = 3
        -- infos ligne de commande
        LEFT JOIN 
            (
                SELECT capl.NO_CA, capl.NO_PO_CA, cal.SECTIMP, cal.SSCPTEANA, cae.CODE_FOU, cs1.VALEUR as suivi_projet,  SUM(capl.QTE_COMM * cal.PU * (1 - cal.REMISE / 100)) AS MONTANT_HT, cal.NO_DA, cal.NO_PO_DA, cal.CODE_ART, cal.DES_SOMM, cal.NM_DCOMP
                FROM CA_PL_CT capl with(nolock)
                LEFT JOIN CA_LIGNE cal with(nolock) ON cal.NO_CDE = capl.NO_CA
                    AND cal.NO_PO = capl.NO_PO_CA
                JOIN CA_ENTET cae with(nolock)
                    ON cae.NO_CDE = cal.NO_CDE
                LEFT JOIN CS_DON cs1 with(nolock) ON COD_TAB = 'CA_LIGNE' AND cal.NO_CDE = CAST(substring(COD_ENR, 1, 9) as int) AND cal.NO_PO = CAST(substring(COD_ENR, 10,3) AS INT) AND NUM_FIX = 1
                WHERE capl.DTE_LIV BETWEEN :date_debut_periode_0 AND :date_fin_periode_0
                GROUP BY capl.NO_CA, capl.NO_PO_CA, cal.SECTIMP, cal.SSCPTEANA, cae.CODE_FOU, cs1.VALEUR, cal.NO_DA, cal.NO_PO_DA, cal.CODE_ART, cal.DES_SOMM, cal.NM_DCOMP
            ) caljoin 
                ON caljoin.SECTIMP = b1v.AXE_UN 
                AND (
                    caljoin.SSCPTEANA IN (
                        SELECT b12v2.AXE_DEUX 
                        FROM BUD_12_VAL b12v2 WITH(NOLOCK)
                        WHERE b12.CODE_BUD = b12v2.CODE_BUD
                            AND b12.CODE_SOC = b12v2.CODE_SOC
                            AND b12.CODE_PER = b12v2.CODE_PER
                            AND b12.IND_REVI = b12v2.IND_REVI
                            AND b12.AXE_UN = b12v2.ENT_AXE_UN
                            AND b12.AXE_DEUX = b12v2.ENT_AXE_DEUX
                    ) 
                    OR EXISTS (
                        SELECT b12v2.AXE_DEUX 
                        FROM BUD_12_VAL b12v2 WITH(NOLOCK)
                        WHERE b12.CODE_BUD = b12v2.CODE_BUD
                            AND b12.CODE_SOC = b12v2.CODE_SOC
                            AND b12.CODE_PER = b12v2.CODE_PER
                            AND b12.IND_REVI = b12v2.IND_REVI
                            AND b12.AXE_UN = b12v2.ENT_AXE_UN
                            AND b12.AXE_DEUX = b12v2.ENT_AXE_DEUX
                            AND b12v2.AXE_DEUX='*'
                    )
                ) 
        LEFT JOIN SSCPTANA sca with(nolock)
            ON sca.CODE_SCA = caljoin.SSCPTEANA
            AND (sca.CODE_STE = SOCIETE.CODE_STE OR sca.CODE_STE = '')
        -- montant facturé
        LEFT JOIN 
            (
                SELECT SUM(fl.MONTANT) AS FACTURE, fl.NUM_LICDE, fl.NUM_CDE, max(fl.NUM_FACT) NUM_FACT, MAX(fe.DATE_ENR) DATE_ENR
                FROM FAC_LIG fl with(nolock)
                LEFT JOIN FAC_ENT fe with(nolock) ON fe.NUM_FACT = fl.NUM_FACT
                WHERE fe.DATE_FAFO BETWEEN :date_debut_periode_1 AND :date_fin_periode_1
                    -- exclusion des factures dont les affectations ne sont pas validées
                    AND fe.AFF_VALID = 0x54
                GROUP BY fl.NUM_LICDE, fl.NUM_CDE 
            ) FACTURE ON FACTURE.NUM_CDE = caljoin.NO_CA AND FACTURE.NUM_LICDE = caljoin.NO_PO_CA 
        LEFT JOIN BUD_CTRL bc with(nolock)
            ON bc.CD_PER = b12.CODE_PER
            AND bc.CD_SOC = b12.CODE_SOC
            AND bc.AXE_UN = b12.AXE_UN
            AND bc.AXE_DEUX = b12.AXE_DEUX
        -- dernière livraison
        LEFT JOIN (
            select brl.NUM_CDE, brl.NO_PO_CDE, max(brc.DATE_CRE) date_dern_livr
            from BR_LIGNE brl with(nolock)
            left join BR_RECEP brc with(nolock) on brc.NUM_BR = brl.NUM_BR
            group by brl.NUM_CDE, brl.NO_PO_CDE
        ) dern_livr
            ON dern_livr.NUM_CDE = caljoin.NO_CA AND dern_livr.NO_PO_CDE = caljoin.NO_PO_CA
        LEFT JOIN HASTUTIL h with(nolock)
            ON h.CODE = cs2.VALEUR
        LEFT JOIN ARTICLES a with(nolock)
            ON a.CODE_ART = caljoin.CODE_ART
        LEFT JOIN FOURNISS f with(nolock)
            ON f.CODE = caljoin.CODE_FOU
        LEFT JOIN MEMOS m with(nolock)
            ON m.NODEMEMO = caljoin.NM_DCOMP
        WHERE b1.ENT_AXE_UN {$uoList}
        ";

        $params = [
            ':periode'      => $periodId,
            ':soc'          => $_SESSION['Session_SocUsrProfil'],

            ':date_debut_periode_0' => $period['DATE_DEB'],
            ':date_fin_periode_0' => $period['DATE_FIN'],
            ':date_debut_periode_1' => $period['DATE_DEB'],
            ':date_fin_periode_1' => $period['DATE_FIN'],
        ];

        $query = $this->applyFilter($query, $params, $post);

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($query, $post, $params, $this->pdo, true);

        return $this->fetchAssocAll($query, $params);
    }
}
