<?php

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

use Helpers\Filter\FilterTable;
use Helpers\QueryBuilder\AbsRepository;
use PDO;

class Reporting extends AbsRepository
{
    /**
     * budgetMonitoring
     *
     * @param  string $user
     * @param  string $period
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function budgetMonitoring(string $user, string $period, string $uoList, array $post = []): array
    {
        $query = " 
        DECLARE 
        @periode VARCHAR(7)           = '',
        @date_debut_periode CHAR(8)   = '',
		@ind_rev INT                  = 0,
        @date_fin_periode CHAR(8)     = '',
        @user VARCHAR(20)             = '',
        @company CHAR(7)              = '0000000';  

        SET @periode    = :periode
        SET @user       = :user
        SET @company    = :soc

        -- on initie les dates de début et de fin des périodes n et n-1 dans des variables
        SELECT @date_debut_periode = DATE_DEB, @date_fin_periode = DATE_FIN 
        FROM PERIODES with(nolock)
        WHERE CODE_PER = @periode

		SELECT @ind_rev = IND_REV
		FROM REV_BUDG
		WHERE COD_SOC = @company
			AND COD_PER = @periode
			AND COD_BUD = @periode
			AND ACTI = 0x54

        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
            LEFT OUTER 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 OUTER 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 = @company 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 AND @date_fin_periode
                            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 = @company 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 AND @date_fin_periode
                        -- 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 b1v.IND_REVI = @ind_rev
            AND b1v.CODE_PER = @periode
            AND b1.ENT_AXE_UN {$uoList}
        ";

        $params = [
            ':user'         => $user,
            ':periode'      => $period,
            ':soc'          => $_SESSION['Session_SocUsrProfil'],
        ];

        /**
         * Prise en compte de la pagination et des filtres
         */
        $whereArray = FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $query .= $filtreAvance->generateSql(0);
        $filtreAvance->pushArray($params);

        if (count($whereArray) >  0) {
            if ($whereArray['tri'] ?? '' != '') {
                $query .= $whereArray[0] . $whereArray['tri'];
            } else {
                $query .= $whereArray[0];
            }

            if (count($whereArray[1]) != 0) {
                $params = array_merge($params, $whereArray[1]);
            }
        }

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

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