<?php

namespace Modules\API\APIInterne\OxaMobility\Outil;

use Exception;
use Helpers\Outil\Sql;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use Modules\Stock\Service\RepoService;

class DemandeService extends AbsRepository
{

    /**
     * Liste des Demandes de Service (DS) à satisfaire
     *   - Statut SOLDE_DS = 'E'
     *   - Pour créer un BS (Bon de sortie) : Type de demande = S
     *   - Pour créer un BT (Bon de transfert) : Type de demande = T
     *  - On a 2 requêtes différentes pour faire une jointure différente sur le code tiers :
     *   vers la table TIERS pour un BS, vers la table  la table des sites (SSITESTO) pour un BT
     *
     * @param string $user
     * @param string $codeSte
     * @param string $codeSite
     * @param string $typeDemande
     * @param string $filtreTiersDestinataire
     * @param int $page
     * @return array<mixed>|false
     */
    public function getListDS($user, $codeSte, $codeSite, $typeDemande, $filtreTiersDestinataire, $page)
    {

        $stmt = "
            DECLARE
                @v_Utilisateur				varchar(30)		= :user,
                @v_CodeSoc					varchar(7)		= :codeSte,
                @v_CodeSite					varchar(7)		= :codeMag,
                @v_TypeDemande				varchar(1)		= :typeDemande, -- 'S' ou 'T'
                @v_FiltreTiersDestinataire	varchar(30)		= :filtreTiers -- 'RACHAT', 'SIEGE' ou 'MAG_RA'
            ;
            ";


        if ($typeDemande === 'S') {
            $stmt .= "
                    SELECT
                        ent.NO_DS, ent.DTE_CREAT, ent.TIERS, t.RS AS NOM_TIERS, ent.SITE
                    FROM
                        SDS_ENTE ent WITH(nolock)
                    LEFT OUTER JOIN
                        TIERS t WITH(nolock) ON t.CODE = ent.TIERS
                    WHERE ent.TYPE_DEM = @v_TypeDemande
                        AND ent.CODE_STE = @v_CodeSoc
                        AND ent.SITE = @v_CodeSite
                        AND (ent.TIERS = @v_FiltreTiersDestinataire OR @v_FiltreTiersDestinataire = '')
                        AND ent.SOLDE_DS = 'E'
            ";
        } else {
            $stmt .= "
                    SELECT
                        ent.NO_DS, ent.DTE_CREAT, ent.TIERS, t.DESIGN AS NOM_TIERS, ent.SITE
                    FROM
                        SDS_ENTE ent WITH(nolock)
                    LEFT OUTER JOIN
                        SSITESTO t WITH(nolock) ON t.SITE = ent.TIERS
                    WHERE ent.TYPE_DEM = @v_TypeDemande
                        AND ent.CODE_STE = @v_CodeSoc
                        AND ent.SITE = @v_CodeSite
                        AND (ent.TIERS = @v_FiltreTiersDestinataire OR @v_FiltreTiersDestinataire = '')
                        AND ent.SOLDE_DS = 'E'
            ";
        }

        $arrayReq = [
            'user'          => $user,
            'typeDemande'   => $typeDemande,
            'codeSte'       => $codeSte,
            'codeMag'      => $codeSite,
            'filtreTiers'   => $filtreTiersDestinataire
        ];


        $paginatorGestion = new PaginatorGestion();
        $paginatorGestion->injecterSqlCount($stmt, [], $arrayReq, $this->pdo, true);

        if ($_SESSION['PaginatorGestion']['nomAttributOrder']) {
            $stmt .= " ORDER BY {$_SESSION['PaginatorGestion']['nomAttributOrder']}";
        }
        if ($page && $_SESSION['PaginatorGestion']['nbrArticleParPage']) {
            $stmt .= " OFFSET (( {$page} - 1 ) * {$_SESSION['PaginatorGestion']['nbrArticleParPage']} ) ROWS
                       FETCH NEXT {$_SESSION['PaginatorGestion']['nbrArticleParPage']} ROWS ONLY";
        }

        return $this->fetchAssocAll($stmt, $arrayReq);
    }
    /**
     * Liste des Lignes d'une Demande de Service (DS) à satisfaire
        - Statut ETAT_LIG = 'E' (en cours)
        - Avec une quantité restant à servir > 0
     * @param int $numDS
     * @return array<mixed>|false
     */
    public function getListDSLine($numDS)
    {
        $stmt = "
            DECLARE
                @v_NumDS int = :numDS
            ;

            SELECT
                lig.NO_LIG_DS as NumLig, lig.ART AS CodeArt, lig.RESTE AS Qte
            FROM
                SDS_LIGN lig WITH(nolock)
            WHERE
                lig.NO_DS = @v_NumDS
                AND lig.ETAT_LIG = 'E'
                AND lig.RESTE > 0
            ORDER BY
                lig.NO_LIG_DS ASC
        ";

        $params = [
            'numDS' => $numDS,
        ];

        $query = $this->pdo->prepare($stmt);
        $query->execute($params);

        return $query->fetchAll($this->pdo::FETCH_ASSOC);
    }

    /**
     * @param string $typeDemande
     * @param string $filtreTiersDestinataire
     * @return int
     */
    public function checkExistFilterTiers($typeDemande, $filtreTiersDestinataire)
    {
        if ($typeDemande === 'S') {
            $stmt = "
                SELECT
                    count(*) as counter
                FROM
                    TIERS
                WHERE
                    CODE = :filtre
            ";
        } else {
            $stmt = "
                SELECT
                    count(*) as counter
                FROM
                    SSITESTO
                WHERE
                    SITE = :filtre
            ";
        }

        $params = [
            'filtre' => $filtreTiersDestinataire,
        ];

        $query = $this->pdo->prepare($stmt);
        $query->execute($params);

        return (int) $query->fetch($this->pdo::FETCH_ASSOC)['counter'];
    }

    /**
     * @param int $numDS
     * @return mixed
     */
    public function checkDS(int $numDS)
    {
        $stmt = "
            SELECT
                count(*) as counter
            FROM
                SDS_ENTE
            WHERE
                NO_DS = :numDS
        ";

        $params = [
            'numDS' => (int) $numDS
        ];

        $query = $this->pdo->prepare($stmt);
        $query->execute($params);

        return $query->fetch($this->pdo::FETCH_ASSOC)['counter'];
    }
}
