<?php

namespace Modules\Reception\Repository;

use Entity\Model\Paravrac;
use Helpers\ChampsSupplementaire\ChampSupp;
use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use PDO;

class CreationBR extends AbsRepository
{

    /**
     * __construct
     */
    public function __construct(PDO $pdo)
    {
        parent::__construct($pdo);
    }

    /**
     * deleteOxaBrEntete
     *
     * @param  string $login
     * @return bool
     */
    public function deleteOxaBrEntete(string $login): bool
    {
        $query = "DELETE FROM oxa_br_entete
        WHERE login = :login ";

        $params = [
            ':login' => [
                'value' => $login,
                'option' => PDO::PARAM_STR
            ],
        ];

        $delete = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $delete->bindParam($key, $param['value'], $param['option']);
        }

        return $delete->execute();
    }

    /**
     * getInfoBrEntete
     *
     * @param  string $login
     * @return array<mixed>
     */
    public function getInfoBrEntete(string $login, string $field = ''): array
    {
        if ($field != '') {
            $select = $field;
        } else {
            $select = '*';
        }
        $query = "SELECT {$select}
        FROM oxa_br_entete
        WHERE login = :login ";

        $params = [
            ':login' => [
                'value' => $login,
                'option' => PDO::PARAM_STR
            ],
        ];

        $data = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $data->bindParam($key, $param['value'], $param['option']);
        }

        $data->execute();
        return $data->fetch(PDO::FETCH_ASSOC);
    }

    /**
     * deleteOxaBrLigne
     *
     * @param  string $login
     * @param  string $init
     * @return bool
     */
    public function deleteOxaBrLigne(string $login, string $init = ''): bool
    {
        $query = "DELETE FROM oxa_br_ligne
        WHERE login = :login ";

        if ($init != '1') {
            $query .= " AND no_ligne_br <> 0 ";
        }

        $params = [
            ':login' => [
                'value' => $login,
                'option' => PDO::PARAM_STR
            ],
        ];

        $delete = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $delete->bindParam($key, $param['value'], $param['option']);
        }

        return $delete->execute();
    }

    /**
     * updateOxaBrLigne
     *
     * @param  string $line
     * @param  string $field
     * @param  string $value
     * @param  bool $bool
     * @return void
     */
    public function updateOxaBrLigne(string $line, string $field, string $value, $bool = false, string $order = '')
    {
        $option = 2;
        if ($bool) {
            if ($value == 'false') {
                if ($field == 'IS_ANNULE') {
                    $value = 0;
                } else {
                    $value = 0x46;
                }
            } else {
                if ($field == 'IS_ANNULE') {
                    $value = 1;
                } else {
                    $value = 0x54;
                }
            }
            $option = 1;
        }

        if ($field == 'MNT_REC' || $field == 'MNT_REC_TTC') {
            $this->updateOxaBrLigneMntTtc($line, $value, $field, $order);
        }

        $query = "UPDATE oxa_br_ligne
        SET " . $field . " = :value
        WHERE login = :login
            AND no_ligne_br = :no_ligne_br";

        $params = [
            ':login' => [
                'value' => $_SESSION['Session_Login'],
                'option' => PDO::PARAM_STR
            ],
            ':no_ligne_br' => [
                'value' => $line,
                'option' => PDO::PARAM_INT
            ],
            ':value' => [
                'value' => $value,
                'option' => $option
            ],
        ];

        $update = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $update->bindValue($key, $param['value'], $param['option']);
        }

        $update->execute();
    }

    /**
     * updateOxaBrLigneRal
     *
     * @param  string $order
     * @param  string $line
     * @param  bool $amount
     * @return bool
     */
    public function updateOxaBrLigneRal(string $order, string $line, bool $amount = false): bool
    {
        $updateQuantities = '';
        // si la valeur est donnée en montant, on met aussi à jour les quantités
        if ($amount) {
            $updateQuantities = ' , 
            qte_r = (CAST(MNT_REC AS FLOAT) / @v_pu),
            qte_c = (CAST(MNT_REC AS FLOAT) / @v_pu),
            qte_a = (CAST(MNT_REC AS FLOAT) / @v_pu) ';
        }

        $query = "SELECT ''
        DECLARE
        @v_login VARCHAR(30)    = ' ',
        @v_no_cde INT           = 0,
        @v_no_br INT            = 0,
        @v_no_ligne_br INT      = 0,
        @v_pu FLOAT             = 0

        SET @v_no_cde         = :order
        SET @v_no_ligne_br    = :line
        SET @v_login          = :login
        
        SELECT @v_pu = PU_REMISE FROM oxa_br_ligne WHERE login = @v_login AND no_ligne_br = @v_no_ligne_br
        SELECT @v_no_br = no_br FROM oxa_br_entete WHERE login = @v_login
        
        UPDATE oxa_br_ligne
        SET ral = ISNULL((
                SELECT SUM(QTE_COMM)
                FROM CA_PL_PR
                WHERE NO_CA = @v_no_cde 
                    AND NO_PO_CA = @v_no_ligne_br
            ) - ISNULL((
                SELECT SUM(
                    CASE
                        WHEN (brl.A_CONTROL = 0x46 OR brl.CTRL_FAIT = 0x54)
                        THEN brl.QTE_ACCEP
                        ELSE brl.QTE_COMPT
                    END)
                FROM BR_LIGNE brl
                WHERE brl.NUM_CDE = @v_no_cde 
                    AND brl.NO_PO_CDE = @v_no_ligne_br
                    AND brl.NUM_BR <> ISNULL(@v_no_br, '')
            ), 0), 0) - ISNULL((
                SELECT SUM(MNT_REC)
                FROM BR_LIGNE brl
                WHERE brl.NUM_CDE = @v_no_cde 
                    AND brl.NO_PO_CDE = @v_no_ligne_br
                    AND brl.NUM_BR <> ISNULL(@v_no_br, '')) / (@v_pu
            ), 0) + ISNULL((
                SELECT SUM(MNT_RET)
                FROM BR_LIGNE brl
                    LEFT JOIN BR_RECEP brc 
                        ON brc.NUM_BR = brl.NUM_BR
                        AND brl.NUM_CDE = brc.NUM_CDE
                WHERE brl.NUM_CDE = @v_no_cde 
                    AND brl.NO_PO_CDE = @v_no_ligne_br
                    AND brl.NUM_BR <> isnull(@v_no_br, '')
					AND brc.NO_BR_ANN <> 0), 0) / (@v_pu)
			+ ISNULL((
                SELECT SUM(QTERETOUR)
                FROM BR_LIGNE brl
                    LEFT JOIN BR_RECEP brc 
                        ON brc.NUM_BR = brl.NUM_BR
                        AND brl.NUM_CDE = brc.NUM_CDE
                WHERE brl.NUM_CDE = @v_no_cde 
                    AND brl.NO_PO_CDE = @v_no_ligne_br
                    AND brl.NUM_BR <> isnull(@v_no_br, '')
					AND brc.NO_BR_ANN <> 0
			), 0) - (CAST(MNT_REC AS FLOAT) / @v_pu) {$updateQuantities}

        WHERE login = @v_login
            AND no_ligne_br = @v_no_ligne_br ";

        $params = [
            ':order' => [
                'value' => $order,
                'option' => PDO::PARAM_INT
            ],
            ':line' => [
                'value' => $line,
                'option' => PDO::PARAM_INT
            ],
            ':login' => [
                'value' => $_SESSION['Session_Login'],
                'option' => PDO::PARAM_STR
            ],
        ];

        $update = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $update->bindParam($key, $param['value'], $param['option']);
        }

        return $update->execute();
    }

    /**
     * updateOxaBrLigneMnt
     */
    public function updateOxaBrLigneMnt(string $line, string $value, bool $return = false): void
    {
        if ($return) {
            $field = 'MNT_RET';
        } else {
            $field = 'MNT_REC';
        }

        $this->execute(
            "UPDATE oxa_br_ligne
            SET {$field} = PU_REMISE * :value
            WHERE login = :login
                AND no_ligne_br = :no_ligne_br ",
            [
                ':login' => $_SESSION['Session_Login'],
                ':no_ligne_br' => intval($line),
                ':value' => $value,
            ]
        );
    }

    /**
     * updateOxaBrLigneReturn
     *
     * @param  string $value
     * @param  string $value
     * @return void
     */
    public function updateOxaBrLigneReturn(string $value, string $line = ''): void
    {
        $value = $value == 'false'
            ? 0
            : 1;
        $addWhere = '';
        if ($line != '') {
            $addWhere = " AND no_ligne_br = {$line} ";
        }

        $this->execute(
            "UPDATE oxa_br_ligne 
            SET 
                IS_ANNULE = :value, QTE_ANNULE =
                CASE 
                    WHEN EN_MNT = 0x46
                    THEN (qte_a - QTE_DEJA_ANNULE)
                    else 0
                END, MNT_RET = 
                CASE 
                    WHEN EN_MNT = 0x46
                    THEN PU_REMISE * (qte_a - QTE_DEJA_ANNULE) 
                    ELSE MNT_REC - (PU_REMISE * QTE_DEJA_ANNULE) 
                END 
            WHERE login = :login 
                {$addWhere}",
            [
                ':login' => $_SESSION['Session_Login'],
                ':value' => $value,
            ]
        );

        // // return bool pour s'assurer que la requête a été correctement exécutée
        // return is_null($update->errorInfo()[1]);
    }

    /**
     * updateOxaBrLigneMntTtc
     *
     * @param  string $line
     * @param  float|string $value
     * @param  string $field
     * @param  string $order
     */
    public function updateOxaBrLigneMntTtc(string $line, $value, string $field, string $order): void
    {
        if ($field == 'MNT_REC') {
            $multiplier = '1';
        } else {
            $multiplier = '(1 / (1 + cal.TVA / 100))';
        }

        $this->execute(
            "UPDATE obl
            SET obl.MNT_REC = {$multiplier} * CONVERT(float, :value)
            FROM oxa_br_ligne AS obl
            JOIN CA_LIGNE AS cal
                ON obl.no_ligne_cde = cal.NO_PO
            WHERE obl.no_ligne_br = :line
                AND obl.login = :login
                AND cal.NO_CDE = :order",
            [
                ':login' => $_SESSION['Session_Login'],
                ':line' => intval($line),
                ':order' => intval($order),
                ':value' => $value,
            ]
        );
    }

    /**
     * updateOxaBrEntete
     *
     * @param  string $field
     * @param  string $value
     * @return bool
     */
    public function updateOxaBrEntete(string $field, string $value): bool
    {
        $query = "UPDATE oxa_br_entete
        SET " . $field . " = :value
        WHERE login = :login
        ";

        $params = [
            ':login' => [
                'value' => $_SESSION['Session_Login'],
                'option' => PDO::PARAM_STR
            ],
            ':value' => [
                'value' => $value,
                'option' => PDO::PARAM_STR
            ],
        ];

        $update = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $update->bindParam($key, $param['value'], $param['option']);
        }

        return $update->execute();
    }

    /**
     * getOrderLines
     *
     * @param  string $order
     * @param  string $line
     * @param  array<mixed> $params
     * @return array<mixed>
     */
    public function getOrderLines(string $order, string $line = '', array $params = []): array
    {
        $configAxeImp = (new Paravrac($this->pdo))->getCommandeAxeImpConfig($_SESSION['Session_SocUsr']);
        $sectImp = $configAxeImp->SectImp->isSecto ? ' cal.CODE_STE ' : "''";
        $codeCompl = $configAxeImp->CodeCompl->isSecto ? ' cal.CODE_STE ' : "''";
        $sousCompteAna = $configAxeImp->SousCompteAna->isSecto ? ' cal.CODE_STE ' : "''";

        $query = "SELECT
            cal.NO_CDE, cal.NO_PO, cal.CODE_ART, cal.DES_SOMM, cal.NM_DCOMP, cal.SSFAM, cal.PU, cal.TVA, cal.REMISE, cal.QTE_COMM, cal.UNITE, u.LIBELLE AS UNIT_LABEL, cal.SECTIMP, si.LIBELLE AS SI_LABEL, cal.CODE_COMP, cc.LIBELLE AS CC_LABEL, cal.SSCPTEANA, sca.LIBELLE AS SCA_LABEL, cal.DTELIVCON, cal.DTELIVPRE, cal.NO_DA, cal.NO_PO_DA, cal.QTE_FACTU, cal.QTEFACVAL, cal.SOM_FACTU, cal.ET_CDELIG, cal.SOMFACVAL, cal.SITE_STK, cal.CTRL_BR, cal.SAISIEREC, cal.TOLLIVAVA, cal.CODE_STE, cal.TITREAV, cal.SSTOAPR, cal.ACCORCA, cal.POIDS, cal.LONGU, cal.LARG, cal.HAUT, cal.VOLUME, cal.PAY_ORI, cal.PAY_PRO, cal.DOUANE, cal.TYPNMP, cal.MASQ_QTE, cal.MASQ_UNI, cal.MASQ_PU, cal.MASQ_DS, cal.TEMPINIT, cal.TYPDATE, cal.POIDSNET, cal.CODGES, cal.LIBGES, cal.REF_FOUR, cal.COD_FAB, cal.COD_GAM, cal.REF_FAB, cal.ID_CG, cal.SOLD_PHYS, cal.ID_CAT_ORI, cal.RECEP_AUTO, cal.DATEDEBCONT, cal.DATEFINCONT, cal.CONTRAT_PERIOD, cal.FIN_DEB_PER, cal.CALI_RECEPAUTO_JOUR, cal.ID_EXTERNE, cal.ETAT_FIN,
            (cal.PU) * (1 - cal.REMISE / 100) AS PU_REMISE,
            CA_ENTET.DEVISE,
            ISNULL((SELECT MIN(CA_PL_PR.DTE_LIV) FROM CA_PL_PR WITH(NOLOCK) WHERE CA_PL_PR.NO_CA = cal.NO_CDE AND CA_PL_PR.NO_PO_CA = cal.NO_PO), '00000000') DATE_LIVR_PR,
            ISNULL(CAST(MEMOS.CONTMEMO AS TEXT), '') AS CONTMEMO,
            CASE
                WHEN (cal.NO_PO_DA = 0 OR EXISTS (SELECT NULL
                    FROM DA_ENTET WITH(NOLOCK)
                    WHERE DA_ENTET.NO_DA = cal.NO_DA
                        AND NO_DEM = obl.login))
                THEN 1
                ELSE 0
            END 'is_reception',
            -- on calcule le RAL réel
            ISNULL((
                SELECT SUM(QTE_COMM)
                FROM CA_PL_PR
                WHERE NO_CA = cal.NO_CDE
                    AND NO_PO_CA = cal.NO_PO
            ) - ISNULL((
                SELECT SUM(
                    CASE
                        WHEN (brl.A_CONTROL = 0x46 OR brl.CTRL_FAIT = 0x54)
                        THEN brl.QTE_ACCEP
                        ELSE brl.QTE_COMPT
                    END)
                FROM BR_LIGNE brl
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> ISNULL(obe.no_br, '')
            ), 0), 0) - ISNULL((
                SELECT SUM(MNT_REC)
                FROM BR_LIGNE brl
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> ISNULL(obe.no_br, '')) / IIF(cal.PU = 0 OR cal.REMISE = 100, 1, (cal.PU * (1 - cal.REMISE / 100))
            ), 0) + ISNULL((
                SELECT SUM(MNT_RET)
                FROM BR_LIGNE brl
				left join BR_RECEP brc
					on brc.NUM_BR = brl.NUM_BR
					and brl.NUM_CDE = brc.NUM_CDE
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> isnull(obe.no_br, '')
					and brc.NO_BR_ANN <> 0) / IIF(cal.PU = 0 OR cal.REMISE = 100, 1, (cal.PU * (1 - cal.REMISE / 100))
			), 0) + ISNULL((
                SELECT SUM(QTERETOUR)
                FROM BR_LIGNE brl
				left join BR_RECEP brc
					on brc.NUM_BR = brl.NUM_BR
					and brl.NUM_CDE = brc.NUM_CDE
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> isnull(obe.no_br, '')
					and brc.NO_BR_ANN <> 0
			), 0) AS RAL_QTE,
            -- on calcule le RAL en montant réel
            (ISNULL((
                SELECT SUM(QTE_COMM)
                FROM CA_PL_PR
                WHERE NO_CA = cal.NO_CDE
                    AND NO_PO_CA = cal.NO_PO
            ) - ISNULL((
                SELECT SUM(
                    CASE
                        WHEN (brl.A_CONTROL = 0x46 OR brl.CTRL_FAIT = 0x54)
                        THEN brl.QTE_ACCEP
                        ELSE brl.QTE_COMPT
                    END)
                FROM BR_LIGNE brl
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> ISNULL(obe.no_br, '')
            ), 0), 0) - ISNULL((
                SELECT SUM(MNT_REC)
                FROM BR_LIGNE brl
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> ISNULL(obe.no_br, '')) / IIF(cal.PU = 0 OR cal.REMISE = 100, 1, (cal.PU * (1 - cal.REMISE / 100))
            ), 0) + ISNULL((
                SELECT SUM(MNT_RET)
                FROM BR_LIGNE brl
				left join BR_RECEP brc
					on brc.NUM_BR = brl.NUM_BR
					and brl.NUM_CDE = brc.NUM_CDE
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> isnull(obe.no_br, '')
					and brc.NO_BR_ANN <> 0) / IIF(cal.PU = 0 OR cal.REMISE = 100, 1, (cal.PU * (1 - cal.REMISE / 100))
			), 0) + ISNULL((
                SELECT SUM(QTERETOUR)
                FROM BR_LIGNE brl
				left join BR_RECEP brc
					on brc.NUM_BR = brl.NUM_BR
					and brl.NUM_CDE = brc.NUM_CDE
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> isnull(obe.no_br, '')
					and brc.NO_BR_ANN <> 0
			), 0)) * (cal.PU) * (1 - cal.REMISE / 100) AS RAL_MNT,
            -- on calcule le RAL en montant réel TTC
            (ISNULL((
                SELECT SUM(QTE_COMM)
                FROM CA_PL_PR
                WHERE NO_CA = cal.NO_CDE
                    AND NO_PO_CA = cal.NO_PO
            ) - ISNULL((
                SELECT SUM(
                    CASE
                        WHEN (brl.A_CONTROL = 0x46 OR brl.CTRL_FAIT = 0x54)
                        THEN brl.QTE_ACCEP
                        ELSE brl.QTE_COMPT
                    END)
                FROM BR_LIGNE brl
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> ISNULL(obe.no_br, '')
            ), 0), 0) - ISNULL((
                SELECT SUM(MNT_REC)
                FROM BR_LIGNE brl
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> ISNULL(obe.no_br, '')) / IIF(cal.PU = 0 OR cal.REMISE = 100, 1, (cal.PU * (1 - cal.REMISE / 100))
            ), 0) + ISNULL((
                SELECT SUM(MNT_RET)
                FROM BR_LIGNE brl
				left join BR_RECEP brc
					on brc.NUM_BR = brl.NUM_BR
					and brl.NUM_CDE = brc.NUM_CDE
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> isnull(obe.no_br, '')
					and brc.NO_BR_ANN <> 0) / IIF(cal.PU = 0 OR cal.REMISE = 100, 1, (cal.PU * (1 - cal.REMISE / 100))
			), 0) + ISNULL((
                SELECT SUM(QTERETOUR)
                FROM BR_LIGNE brl
				left join BR_RECEP brc
					on brc.NUM_BR = brl.NUM_BR
					and brl.NUM_CDE = brc.NUM_CDE
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> isnull(obe.no_br, '')
					and brc.NO_BR_ANN <> 0
			), 0)) * (cal.PU) * NULLIF((1 - cal.REMISE / 100), 0) * (1 + (cal.TVA / 100)) AS RAL_MNT_TTC,
            -- on calcule le montant réceptionné
            ISNULL((
                SELECT SUM(
                    CASE
                        WHEN EN_MNT = 0x54
                        THEN MNT_REC
                        ELSE QTE_RECUE * IIF(cal2.PU <> 0 OR cal2.REMISE <> 100, (cal2.PU * (1 - cal2.REMISE / 100)), 1)
                    END) - SUM(
                    CASE
                        WHEN EN_MNT = 0x54
                        THEN MNT_RET
                        ELSE QTERETOUR * IIF(cal2.PU <> 0 OR cal2.REMISE <> 100, (cal2.PU * (1 - cal2.REMISE / 100)), 1)
                    END) AS mnt_recu
                FROM BR_LIGNE brl
                    LEFT JOIN CA_LIGNE cal2
                        ON cal2.NO_CDE = brl.NUM_CDE
                            AND cal2.NO_PO = brl.NO_PO_CDE
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
                    AND brl.NUM_BR <> ISNULL(obe.no_br, '')
            ), 0) AS MNT_RECU,
            -- on vérifie si la ligne a été ou non reçue en montant dans un BR précédent
            ISNULL((
                SELECT TOP 1 '1' AS mnt_recu
                FROM BR_LIGNE brl2
                    LEFT JOIN CA_LIGNE cal3
                        ON cal3.NO_CDE = brl2.NUM_CDE
                            AND cal3.NO_PO = brl2.NO_PO_CDE
                WHERE brl2.NUM_CDE = cal.NO_CDE
                    AND brl2.NO_PO_CDE = cal.NO_PO
                        AND brl2.EN_MNT = 0x54
                        AND brl2.NUM_BR <> ISNULL(obe.no_br, '')
            ), '0') AS LIGNE_RECUE_EN_MNT,
            ISNULL((
                SELECT TOP 1 '1' AS qte_recue
                FROM BR_LIGNE brl3
                    LEFT JOIN CA_LIGNE cal4
                        ON cal4.NO_CDE = brl3.NUM_CDE
                            AND cal4.NO_PO = brl3.NO_PO_CDE
                WHERE brl3.NUM_CDE = cal.NO_CDE
                    AND brl3.NO_PO_CDE = cal.NO_PO
                        AND brl3.EN_MNT = 0x46
                        AND brl3.NUM_BR <> ISNULL(obe.no_br, '')
            ), '0') AS LIGNE_RECUE_EN_QTE,
            sa.TRACABLE
        FROM CA_LIGNE cal WITH (NOLOCK)
            LEFT JOIN SARTICLE sa WITH (NOLOCK)
				ON sa.CODE_ART = cal.CODE_ART
            LEFT JOIN SECTIMPU si WITH (NOLOCK)
                ON si.CODE_SI = cal.SECTIMP
                    AND si.CODE_STE = {$sectImp}
            LEFT JOIN SSCPTANA sca WITH (NOLOCK)
                ON sca.CODE_SCA = cal.SSCPTEANA
                    AND sca.CODE_STE = {$sousCompteAna}
            LEFT JOIN CODECOMP cc WITH (NOLOCK)
                ON cc.CODE_CDC = cal.CODE_COMP
                    AND cc.CODE_STE = {$codeCompl}
            LEFT JOIN UNITES u
                ON u.CODE_UNI = cal.UNITE
            LEFT OUTER JOIN CQUALITE WITH(NOLOCK)
                ON CTRL_BR = CODE_CTRL
            LEFT OUTER JOIN CA_ENTET WITH(NOLOCK)
                ON CA_ENTET.NO_CDE = cal.NO_CDE
            LEFT OUTER JOIN MEMOS WITH(NOLOCK)
                ON NM_DCOMP  = NODEMEMO
            LEFT OUTER JOIN oxa_br_ligne obl
				ON obl.login = :LOGIN
					AND obl.no_ligne_cde = cal.NO_PO
			LEFT OUTER JOIN oxa_br_entete obe
				ON obe.login = obl.login
        WHERE cal.NO_CDE = :NO_CDE
            AND cal.SAISIEREC = 0x54
            AND cal.RECEP_AUTO = 0x46
            AND cal.SOLD_PHYS = 0x46
        ";
        
        $params = [
            ':NO_CDE' => [
                'value' => $order,
                'option' => PDO::PARAM_INT
            ],
            ':LOGIN' => [
                'value' => $_SESSION['Session_Login'],
                'option' => PDO::PARAM_STR
            ],
        ];

        if ($line != '') {
            $query .= " AND cal.NO_PO = :line ";
            $params[':line'] = [
                'value' => $line,
                'option' => PDO::PARAM_INT
            ];
        }

        $details = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $details->bindParam($key, $param['value'], $param['option']);
        }

        $details->execute();
        $return = $details->fetchAll(PDO::FETCH_ASSOC);

        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * initOxaBrEntete
     *
     * @param  string $order
     * @param  string $login
     * @return bool
     */
    public function initOxaBrEntete(string $order, string $login, int $brNumber = 0): bool
    {
        $query = "INSERT INTO oxa_br_entete (login, no_cde, date_bl, DATE_PER, TYPE_BR, NUM_BR_ANNULE)
            VALUES (:login, :no_cde, :date_bl, :DATE_PER, :TYPE_BR, :NUM_BR_ANNULE)
            ";

        $params = [
            ':login' => [
                'value' => $login,
                'option' => PDO::PARAM_STR
            ],
            ':no_cde' => [
                'value' => $order,
                'option' => PDO::PARAM_INT
            ],
            ':date_bl' => [
                'value' => '00000000',
                'option' => PDO::PARAM_STR
            ],
            ':DATE_PER' => [
                'value' => \date('Ymd'),
                'option' => PDO::PARAM_STR
            ],
            ':TYPE_BR' => [
                'value' => $brNumber === 0 ? 'R' : 'A',
                'option' => PDO::PARAM_STR
            ],
            ':NUM_BR_ANNULE' => [
                'value' => $brNumber,
                'option' => PDO::PARAM_INT
            ],
        ];

        $init = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $init->bindParam($key, $param['value'], $param['option']);
        }

        return $init->execute();
    }

    public function initOxaBrEnteteModif(string $brNumber): bool
    {
        $query = "INSERT INTO oxa_br_entete 
            SELECT :login, br.NUM_CDE, br.BON_LIVR, br.DATE_BL, ISNULL(m.CONTMEMO, ''), br.NUM_BR, br.TYP_BR, 'M', br.NO_BR_ANN, ISNULL(m2.CONTMEMO, ''), br.DATE_PER
            FROM BR_RECEP br
                LEFT JOIN MEMOS m ON m.NODEMEMO = br.NM_COMMENT
                LEFT JOIN MEMOS m2 ON m2.NODEMEMO = br.NM_BNOTE
            WHERE br.NUM_BR = :NUM_BR
            ";

        $params = [
            ':login' => [
                'value' => $_SESSION['Session_Login'],
                'option' => PDO::PARAM_STR
            ],
            ':NUM_BR' => [
                'value' => $brNumber,
                'option' => PDO::PARAM_INT
            ],
        ];

        $init = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $init->bindParam($key, $param['value'], $param['option']);
        }

        return $init->execute();
    }

    /**
     * resetCsBr
     *
     * @param  string $login
     * @return bool
     */
    public function resetCsBr(string $login): bool
    {
        $query = "DELETE FROM CS_DON
        WHERE COD_ENR = :login
            AND COD_TAB = 'BR_OXAPROC'";

        $params[':login'] = [
            'value' => $login,
            'option' => PDO::PARAM_STR
        ];

        $data = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $data->bindParam($key, $param['value'], $param['option']);
        }

        return $data->execute();
    }

    /**
     * initCsBr
     *
     * @param  string $numBr
     * @return bool
     */
    public function initCsBr(string $numBr = ''): bool
    {
        $params = [];

        if ($numBr != '') {
            $memos = "
            CASE
                WHEN CS_TYPES.FORMAT = 'M'
                THEN ISNULL((
                    SELECT CONTMEMO
                    FROM MEMOS WITH (NOLOCK)
                    WHERE NODEMEMO = CAST((SELECT VALEUR FROM CS_DON WITH (NOLOCK) WHERE COD_TAB='BR_RECEP' AND COD_ENR = :numBr AND CS_DON.NUM_FIX = CS_CHPS.NUM_FIX) AS INT)),'')
                ELSE ISNULL((SELECT VALEUR FROM CS_DON WITH (NOLOCK) WHERE COD_TAB='BR_RECEP' AND COD_ENR = :numBr2 AND CS_DON.NUM_FIX = CS_CHPS.NUM_FIX) ,'')
            END AS VALUECHAMPSUPP ";

            $params = [
                ':numBr' => [
                    'value' => $numBr,
                    'option' => PDO::PARAM_INT
                ],
                ':numBr2' => [
                    'value' => $numBr,
                    'option' => PDO::PARAM_INT
                ],
            ];
        } else {
            $memos = "''";
        }

        $query = "INSERT INTO CS_DON
        SELECT 'BR_OXAPROC', :login, NUM_FIX, {$memos}, ''
        FROM CS_CHPS WITH (NOLOCK)
            LEFT JOIN CS_TYPES WITH(NOLOCK) ON CS_TYPES.TYPE = CS_CHPS.TYPE
        WHERE COD_TAB = 'BR_RECEP'";

        $params[':login'] = [
            'value' => $_SESSION['Session_Login'],
            'option' => PDO::PARAM_STR
        ];

        $data = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $data->bindParam($key, $param['value'], $param['option']);
        }

        return $data->execute();
    }

    /**
     * deleteCcTempBr
     *
     * @return bool
     */
    public function deleteCcTempBr(): bool
    {
        $query = "DELETE FROM CC_LDOC
        WHERE CC_LDOC.DOC_TYPE = 'BRT'
            AND CC_LDOC.DOC_NUM = 0
            AND CC_LDOC.ID_USER_BASKET = :ID_USER_BASKET";

        $params = [
            ':ID_USER_BASKET' => [
                'value' => $_SESSION['id_user'],
                'option' => PDO::PARAM_INT
            ],
        ];

        $data = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $data->bindParam($key, $param['value'], $param['option']);
        }

        return $data->execute();
    }

    /**
     * initCcBr
     *
     * @return bool
     */
    public function initCcBr(): bool
    {
        $query = "INSERT INTO CC_LDOC (DOC_TYPE, DOC_NUM, DOC_LINE_NUM, ID_USER_BASKET, CC_ID, CC_FREE, CC_DISPLAY_NUM, CC_IS_TITLE, CC_NAME, CC_TYPE, CC_UPD_ONLY_RECEIPT, CC_INVIS_RECEIPT, CC_REQUIRED, CC_PRINTED_OF, CC_CALC, CC_CALC_EXP, CC_VALUE, DOC_AMD) 
        SELECT 'BRT', 0, cc.DOC_LINE_NUM, :ID_USER_BASKET, cc.CC_ID, cc.CC_FREE, cc.CC_DISPLAY_NUM, cc.CC_IS_TITLE, cc.CC_NAME, cc.CC_TYPE, cc.CC_UPD_ONLY_RECEIPT, cc.CC_INVIS_RECEIPT, cc.CC_REQUIRED, cc.CC_PRINTED_OF, cc.CC_CALC, cc.CC_CALC_EXP, cc.CC_VALUE, cc.DOC_AMD
            FROM CC_LDOC cc
                LEFT join oxa_br_ligne obl
                    ON obl.no_ligne_cde = cc.DOC_LINE_NUM
                LEFT JOIN oxa_br_entete obe
                    ON obe.no_cde = cc.DOC_NUM
                        AND obe.login = obl.login
            WHERE DOC_TYPE = 'CA'
                AND obe.login = :login";
        $params = [
            ':ID_USER_BASKET' => [
                'value' => $_SESSION['id_user'],
                'option' => PDO::PARAM_INT
            ],
            ':login' => [
                'value' => $_SESSION['Session_Login'],
                'option' => PDO::PARAM_STR
            ],
        ];

        $data = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $data->bindParam($key, $param['value'], $param['option']);
        }

        return $data->execute();
    }

    /**
     * orderDetails
     *
     * @param  int $orderNumber
     * @return array<mixed>
     */
    public function orderDetails(int $orderNumber): array
    {
        $query = "SELECT
            cae.NO_CDE, cae.AVENANT, cae.ETAT_CDE, cae.ETAT_FIN, cae.DTE_VALID, cae.DTE_AR, cae.DTE_PERIO, cae.CODE_FOU, cae.CTCT_FOU, cae.TEL_CTCT, cae.FAX_CTCT, cae.EMA_CTCT, cae.DTE_CREAT, cae.ACH, cae.SECTIMP, cae.CODECOMP, cae.SSCPTEANA, cae.CTR_EMETT, cae.CTR_LIV, cae.CTR_FACTU, cae.VAL_DEVSTE, cae.REM_GLOB, cae.ACC_CADRE, cae.ZNLIBNUM, cae.DEVISE, cae.VAL_DEVIS, cae.COND_PAIE, cae.MODE_PAIE, cae.MODE_LIV, cae.COMM_LIBR, cae.TOT_CDE, cae.TOT_CDEHT, cae.TOT_TTC, cae.DEJA_FAC, cae.SOMFACVAL, cae.REL_AC, cae.REL_PREV, cae.RELDTEREC, cae.REL_RET, cae.FLAG_ARCH, cae.VALDEVTER, cae.MOT_AVNT, cae.NM_TITRE, cae.OBJET_CA, cae.DEMANDEUR, cae.SIGNEE, cae.REFUSEE, cae.DTE_LIV, cae.SAISIEREC, cae.TYP_DOC, cae.CODE_STE, cae.ADCDEFOU, cae.VILCDEFO, cae.PAYCDEFO, cae.CDE_EXP, cae.MOD_TRS, cae.TRANSIT, cae.CT_TRANS, cae.TELCTTRS, cae.FAXCTTRS, cae.PORCTTRS, cae.MAICTTRS, cae.DEVTRS, cae.VALDEVTR, cae.FOURLIVR, cae.ADRLIVR, cae.VILLIVR, cae.PAYLIVR, cae.PREACH, cae.RE_ACH, cae.MDTREACH, cae.CTRLENTR, cae.DTEPRVCTR, cae.ORGCTRL, cae.CTORGCT, cae.TELCTORG, cae.FAXCTORG, cae.PORCTORG, cae.MAICTORG, cae.LICIMPORT, cae.FRANCTVA, cae.FRNEXPORT, cae.MTSIMFACT, cae.DTEFACEXP, cae.DTEFACPF, cae.PORTCFOU, cae.REFOFFOU, cae.CHARGFOU, cae.NMADLVEN, cae.TLADLVEN, cae.FXADLVEN, cae.ATTDAU, cae.TYPNMP, cae.TYPDATE, cae.MODELE, cae.COD_LNG, cae.NOM_LIV, cae.ADR_LIV, cae.VIL_LIV, cae.PAY_LIV, cae.NMCTC1L, cae.FTCTC1L, cae.TLCTC1L, cae.FXCTC1L, cae.PRCTC1L, cae.MLCTC1L, cae.NMCTC2L, cae.FTCTC2L, cae.TLCTC2L, cae.FXCTC2L, cae.PRCTC2L, cae.MLCTC2L, cae.LOGIST, cae.REFIMPCDE, cae.CODGES, cae.LIBGES, cae.CPCDEFO, cae.VILLEFOU, cae.CPLIVR, cae.VIL_LIVR, cae.CPLIV, cae.VILLELIV, cae.AR_NCONF, cae.AR_REF, cae.COD_TAC, cae.INTERFA, cae.ID_CG, cae.ID_GED, cae.URL_GED, cae.EXP_SOLDEES, cae.EVE_SIG, cae.EVE_VAL, cae.EVE_IMP, cae.NO_DOC_CONT, cae.DTE_RECCONT, cae.EXPE_COLIS_0, cae.RECEP_AUTO, cae.DATEDEBCONT, cae.DATEFINCONT, cae.CONTRAT_PERIOD, cae.DTE_REL_AR, cae.FIN_DEB_PER, cae.CAEN_RECEPAUTO_JOUR, cae.CAEN_ECART_LIVR, f.RS, H.NOM as DEMANDEUR_NOM
        FROM CA_ENTET cae WITH (NOLOCK)
            LEFT JOIN FOURNISS f WITH (NOLOCK)
                ON f.CODE = cae.CODE_FOU
            LEFT JOIN
                HASTUTIL H with (nolock) ON H.CODE = cae.DEMANDEUR
        WHERE cae.NO_CDE = :NO_CDE
        ";

        $params = [
            ':NO_CDE' => [
                'value' => $orderNumber,
                'option' => PDO::PARAM_INT
            ],
        ];
        $details = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $details->bindParam($key, $param['value'], $param['option']);
        }

        $details->execute();
        $details = $details->fetch(PDO::FETCH_ASSOC);

        return $details;
    }

    /**
     * insertOxaBrLigne
     *
     * @param  array<mixed> $lineDetails
     * @param  string $datePer
     */
    public function insertOxaBrLigne(array $lineDetails, string $initRecep, string $datePer): void
    {
        if (empty($lineDetails)) {
            return;
        }

        $i = 0;
        foreach ($lineDetails as $line) {
            $line['is_reception'] = false;
            $line['LIGNE_RECUE_EN_MNT'] = Sql::toBool($line['LIGNE_RECUE_EN_MNT']);
            $line['LIGNE_RECUE_EN_QTE'] = Sql::toBool($line['LIGNE_RECUE_EN_QTE']);
            if ($initRecep == 'ECHUE') {
                if ($line['DATE_LIVR_PR'] <= $datePer && $line['RAL_QTE'] > 0) {
                    $line['is_reception'] = true;
                }
            } elseif ($initRecep == 'SOLDE') {
                if ($line['RAL_QTE'] > 0) {
                    $line['is_reception'] = true;
                }
            }

            // contrôle qualité présent sur la ligne de commande ?
            if ($line['CTRL_BR'] != '') {
                $is_ctrl_qualite = true;
            } else {
                $is_ctrl_qualite = false;
            }

            // type de réception accepté 'M' => 'montant', 'Q' => 'quantité'
            if ($line['LIGNE_RECUE_EN_MNT'] == '0x54') {
                $line['TYPE_RECEPT_POSSIBLE'] = 'M';
            } elseif ($line['LIGNE_RECUE_EN_QTE'] == '0x54') {
                $line['TYPE_RECEPT_POSSIBLE'] = 'Q';
            } else {
                $line['TYPE_RECEPT_POSSIBLE'] = '';
            }

            // si une ligne de commande a un site de stock, la réception ne peut pas être en montant
            if ($line['SITE_STK'] != '') {
                $line['TYPE_RECEPT_POSSIBLE'] = 'Q';
            }

            $query = "INSERT INTO oxa_br_ligne (login, no_ligne_cde, no_ligne_br, code_art, design_sommaire, unite, is_reception, qte_r, qte_c, qte_a, ral, PU_REMISE, is_ctrl_qualite, type_ctrl, design_comp, CODE_SI, CODE_CG, CODE_CDC, CODE_SCA, SI_LIBELLE, CG_LIBELLE, CDC_LIBELLE, SCA_LIBELLE, REF_FOUR, REF_FAB, DTE_LIV, EN_MNT, MNT_REC, COD_DEV, TYPE_RECEPT_POSSIBLE)
                VALUES (:login$i, :no_ligne_cde$i, :no_ligne_br$i, :code_art$i, :design_sommaire$i, :unite$i, :is_reception$i, :qte_r$i, :qte_c$i, 
                :qte_a$i, :ral$i, :PU_REMISE$i, :is_ctrl_qualite$i, :type_ctrl$i, CONVERT(VARCHAR(3000), :design_comp$i), :CODE_SI$i, :CODE_CG$i, 
                :CODE_CDC$i, :CODE_SCA$i, :SI_LIBELLE$i, :CG_LIBELLE$i, :CDC_LIBELLE$i, :SCA_LIBELLE$i, :REF_FOUR$i, :REF_FAB$i, :DTE_LIV$i, 
                :EN_MNT$i, :MNT_REC$i, :COD_DEV$i, :TYPE_RECEPT_POSSIBLE$i)";

            $params[":login$i"] = $_SESSION['Session_Login'];
            $params[":no_ligne_cde$i"] = $line['NO_PO'];
            $params[":no_ligne_br$i"] = $line['NO_PO'];
            $params[":code_art$i"] = $line['CODE_ART'];
            $params[":design_sommaire$i"] = $line['DES_SOMM'];
            $params[":unite$i"] = $line['UNITE'];
            $params[":is_reception$i"] = $line['is_reception'];
            $params[":qte_r$i"] = $line['RAL_QTE'];
            $params[":qte_c$i"] = $line['RAL_QTE'];
            $params[":qte_a$i"] = $line['RAL_QTE'];
            $params[":ral$i"] = $line['RAL_QTE'];
            $params[":PU_REMISE$i"] = $line['PU_REMISE'];
            $params[":is_ctrl_qualite$i"] = $is_ctrl_qualite;
            $params[":type_ctrl$i"] = $line['CTRL_BR'];
            $params[":design_comp$i"] = $line['CONTMEMO'];
            $params[":CODE_SI$i"] = $line['SECTIMP'];
            $params[":CODE_CG$i"] = $line['CODGES'];
            $params[":CODE_CDC$i"] = $line['CODE_COMP'];
            $params[":CODE_SCA$i"] = $line['SSCPTEANA'];
            $params[":SI_LIBELLE$i"] = $line['SI_LABEL'];
            $params[":CG_LIBELLE$i"] = $line['LIBGES'];
            $params[":CDC_LIBELLE$i"] = $line['CC_LABEL'];
            $params[":SCA_LIBELLE$i"] = $line['SCA_LABEL'];
            $params[":REF_FOUR$i"] = $line['REF_FOUR'];
            $params[":REF_FAB$i"] = $line['REF_FAB'];
            $params[":DTE_LIV$i"] = $line['DATE_LIVR_PR'];
            $params[":EN_MNT$i"] = $line['LIGNE_RECUE_EN_MNT'];
            $params[":MNT_REC$i"] = $line['RAL_MNT'];
            $params[":COD_DEV$i"] = $line['DEVISE'];
            $params[":TYPE_RECEPT_POSSIBLE$i"] = $line['TYPE_RECEPT_POSSIBLE'];
            $i++;

            $this->execute($query, $params);
        }
    }

    /**
     * getOxaBrLigne
     *
     * @param  string $login
     * @param  string $line
     * @param  array<mixed> $params
     * @return array<mixed>
     */
    public function getOxaBrLigne(string $login, string $line = '', array $params = []): array
    {
        $query = "SELECT DISTINCT lig.*, (cal.PU), u.LIBELLE AS UNIT_LABEL, u.NB_DECIM, (lig.MNT_REC * (1 + cal.TVA / 100)) AS MNT_REC_TTC
        FROM oxa_br_ligne lig
            LEFT JOIN oxa_br_entete ent
                ON ent.login = lig.login
            LEFT OUTER JOIN BR_LIGNE brl
                ON brl.NUM_CDE = ent.no_cde
                    AND brl.NO_PO_CDE = lig.no_ligne_cde
            LEFT JOIN CA_LIGNE cal
                ON cal.NO_CDE = ent.no_cde
                    AND cal.NO_PO = lig.no_ligne_cde
            LEFT JOIN UNITES u
                ON u.CODE_UNI = lig.unite
        WHERE lig.login = :login 
            AND lig.no_ligne_cde <> 0";

        $params = [
            ':login' => [
                'value' => $login,
                'option' => PDO::PARAM_STR
            ]
        ];

        if ($line != '') {
            $query .= " AND lig.no_ligne_br = :line ";
            $params[':line'] = [
                'value' => $line,
                'option' => PDO::PARAM_INT
            ];
        }

        $lines = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $lines->bindParam($key, $param['value'], $param['option']);
        }

        $lines->execute();
        if ($line) {
            $return = $lines->fetch(PDO::FETCH_ASSOC);
        } else {
            $return = $lines->fetchAll(PDO::FETCH_ASSOC);
        }


        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * getSelectedLines
     * requête qui retourne la liste des lignes du BR sélectionnées en réception
     *
     * @return array<mixed>
     */
    public function getSelectedLines()
    {
        $query = "SELECT no_ligne_br, design_sommaire, code_art, unite, qte_a
        FROM oxa_br_ligne WITH (NOLOCK)
        WHERE (is_reception = 0x54 OR IS_ANNULE = 1)
            AND login = :login";

        $params = [
            ':login' => [
                'option' => PDO::PARAM_STR,
                'value' => $_SESSION['Session_Login']
            ]
        ];

        $return = $this->pdo->prepare($query);
        foreach ($params as $key => $param) {
            $return->bindParam($key, $param['value'], $param['option']);
        }

        $return->execute();

        $return = $return->fetchAll(\PDO::FETCH_ASSOC);

        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * getDateBL
     *
     * @param  int $order
     * @return string
     */
    public function getDateBL(int $order)
    {
        $query = "SELECT date_bl
        FROM oxa_br_entete WITH(NOLOCK)
        WHERE no_cde = :no_cde";

        $params = [
            ':no_cde' => [
                'option' => PDO::PARAM_INT,
                'value' => $order
            ]
        ];

        $date = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $date->bindParam($key, $param['value'], $param['option']);
        }

        $date->execute();
        $date = $date->fetch(PDO::FETCH_ASSOC);
        return $date['date_bl'] ?? false;
    }

    /**
     * getlinesWithAdditionalCharacteristics
     *
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getlinesWithAdditionalCharacteristics(array $post = []): array
    {
        $query = "SELECT cc.DOC_TYPE, cc.DOC_NUM, cc.DOC_LINE_NUM, cc.ID_USER_BASKET,
        cc.DOC_AMD, cc.CC_ID, cc.CC_FREE, cc.CC_DISPLAY_NUM,
        cc.CC_IS_TITLE, cc.CC_NAME NOM, cc.CC_TYPE, cc.CC_UPD_ONLY_RECEIPT, cc.CC_INVIS_RECEIPT,
        cc.CC_REQUIRED, cc.CC_PRINTED_OF, cc.CC_CALC, cc.CC_CALC_EXP, cc.CC_VALUE VALEUR,
        cs.FORMAT, cs.ALP_LONG, cs.PE_LONG, cs.PD_LONG, cs.NEG_AUTO, cs.CONTROLE, cs.CTRL_STD, cs.TBLSECTO,
        obl.code_art, obl.design_sommaire, obl.unite, u.LIBELLE AS unite_label
        FROM CC_LDOC cc WITH (NOLOCK)
            LEFT JOIN CS_TYPES cs WITH (NOLOCK)
                ON cs.TYPE = cc.CC_TYPE
            LEFT JOIN oxa_br_ligne obl WITH (NOLOCK)
                ON obl.no_ligne_cde = cc.DOC_LINE_NUM
            LEFT JOIN UNITES u
                ON u.CODE_UNI = obl.unite
        WHERE cc.DOC_TYPE = 'BRT'
            AND cc.DOC_NUM = 0
            AND cc.ID_USER_BASKET = :ID_USER_BASKET
            AND obl.login = :login";

        $params = [
            ':ID_USER_BASKET' => [
                'option' => PDO::PARAM_INT,
                'value' => $_SESSION['id_user']
            ],
            ':login' => [
                'option' => PDO::PARAM_STR,
                'value' => $_SESSION['Session_Login']
            ]
        ];

        $lines = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $lines->bindParam($key, $param['value'], $param['option']);
        }

        $lines->execute();
        $return = $lines->fetchAll(\PDO::FETCH_ASSOC);

        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * getCodeSsPeriod
     *
     * @param  string $date
     * @return string|bool
     */
    public function getCodeSsPeriod(string $date)
    {
        $today = date('Ymd');
        $query = "SELECT sp.CODE_PER
        FROM SSPERIOD sp WITH (NOLOCK)
            LEFT JOIN SSP_STE_CLOSING WITH (NOLOCK)
                on sp.CODE_PER=SSP_STE_CLOSING.CODE_PER
                    AND sp.DATE_DEB=SSP_STE_CLOSING.DATE_DEB
                    AND SSP_STE_CLOSING.CODE_STE = '{$_SESSION['Session_SocUsr']}'
		WHERE :date BETWEEN sp.DATE_DEB AND sp.DATE_FIN
            AND (sp.ETAT = 'O' OR (
                    sp.ETAT = ''
                    AND sp.DATE_DEB <= '{$today}'
                    AND (sp.DATE_FIN >= '{$today}' OR SSPERIOD.PREV_CLOSING_DATE >= '{$today}' OR ISNULL(SSP_STE_CLOSING.PREV_CLOSING_DATE,'00000000') >= '{$today}'))
                )";

        $params = [
            ':date' => [
                'option' => PDO::PARAM_STR,
                'value' => $date
            ]
        ];

        $date = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $date->bindParam($key, $param['value'], $param['option']);
        }

        $date->execute();
        $date = $date->fetch(PDO::FETCH_ASSOC);
        return $date['CODE_PER'] ?? false;
    }

    /**
     * getTauxMargeBrExcedentaire
     *
     * @param  string $order
     * @return array<mixed>
     */
    public function getTauxMargeBrExcedentaire(string $order): array
    {
        $query = "SELECT TYPDOC.TXLIVEXCD, TYPDOC.MTLIVEXCD
        FROM TYPDOC WITH (NOLOCK)
            LEFT JOIN CA_ENTET cae
                ON cae.TYP_DOC = TYPDOC.TYPE
        WHERE cae.NO_CDE = :order ";

        $params = [
            ':order' => [
                'option' => PDO::PARAM_STR,
                'value' => $order
            ]
        ];

        $rate = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $rate->bindParam($key, $param['value'], $param['option']);
        }

        $rate->execute();
        $rate = $rate->fetch(PDO::FETCH_ASSOC);
        return $rate;
    }

    /**
     * getTotalHtBr
     *
     * @param  string $login
     * @return string
     */
    public function getTotalHtBr(string $login): string
    {
        $query = "SELECT SUM(obl.MNT_REC) AS TOTAL_HT
        FROM oxa_br_ligne obl WITH (NOLOCK)
            WHERE login = :login
                AND obl.is_reception = 0x54";

        $params = [
            ':login' => [
                'option' => PDO::PARAM_STR,
                'value' => $login
            ]
        ];

        $total = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $total->bindParam($key, $param['value'], $param['option']);
        }

        $total->execute();
        $total = $total->fetch(PDO::FETCH_ASSOC);

        return $total['TOTAL_HT'];
    }

    /**
     * getTotalTtcBr
     *
     * @param  string $login
     * @param  string $orderNumber
     * @return string
     */
    public function getTotalTtcBr(string $login, string $orderNumber): string
    {
        $query = "SELECT SUM(obl.MNT_REC + (obl.MNT_REC * cal.TVA / 100)) AS TOTAL_TTC
        FROM oxa_br_ligne obl WITH(NOLOCK)
            LEFT OUTER join CA_LIGNE cal WITH(NOLOCK)
                ON cal.NO_PO = obl.no_ligne_cde
            WHERE obl.login = :login
                AND obl.is_reception = 0x54
                AND cal.NO_CDE = :no_cde";

        $params = [
            ':login' => [
                'option' => PDO::PARAM_STR,
                'value' => $login
            ],
            ':no_cde' => [
                'option' => PDO::PARAM_INT,
                'value' => $orderNumber
            ]
        ];

        $total = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $total->bindParam($key, $param['value'], $param['option']);
        }

        $total->execute();
        $total = $total->fetch(PDO::FETCH_ASSOC);
        return $total['TOTAL_TTC'] == '' ? 0 : $total['TOTAL_TTC'];
    }

    /**
     * getTotalHtBr
     *
     * @param  string $login
     * @return string
     */
    public function getTotalHtBrRetour(string $login): string
    {
        $query = "SELECT SUM(obl.MNT_RET) AS TOTAL_HT
        FROM oxa_br_ligne obl WITH (NOLOCK)
            WHERE login = :login
                AND obl.IS_ANNULE = 1";

        $params = [
            ':login' => [
                'option' => PDO::PARAM_STR,
                'value' => $login
            ]
        ];

        $total = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $total->bindParam($key, $param['value'], $param['option']);
        }

        $total->execute();
        $total = $total->fetch(PDO::FETCH_ASSOC);

        return $total['TOTAL_HT'];
    }

    /**
     * getTotalTtcBr
     *
     * @param  string $login
     * @param  string $orderNumber
     * @return string
     */
    public function getTotalTtcBrRetour(string $login, string $orderNumber): string
    {
        $query = "SELECT SUM(obl.MNT_RET + (obl.MNT_RET * cal.TVA / 100)) AS TOTAL_TTC
        FROM oxa_br_ligne obl WITH(NOLOCK)
            LEFT OUTER join CA_LIGNE cal WITH(NOLOCK)
                ON cal.NO_PO = obl.no_ligne_cde
            WHERE obl.login = :login
                AND obl.IS_ANNULE = 1
                AND cal.NO_CDE = :no_cde";

        $params = [
            ':login' => [
                'option' => PDO::PARAM_STR,
                'value' => $login
            ],
            ':no_cde' => [
                'option' => PDO::PARAM_INT,
                'value' => $orderNumber
            ]
        ];

        $total = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $total->bindParam($key, $param['value'], $param['option']);
        }

        $total->execute();
        $total = $total->fetch(PDO::FETCH_ASSOC);
        return $total['TOTAL_TTC'] == '' ? 0 : $total['TOTAL_TTC'];
    }

    /**
     * saveBrEntete
     *
     * @param  string $login
     * @param  array<mixed> $insertArray
     * @return bool
     */
    public function saveBrEntete(string $login, array $insertArray): bool
    {
        $query = "INSERT INTO BR_RECEP (NUM_BR, DATE_PER, NUM_CDE, VAL_DEV, DATE_CRE, TOTAL_HT, TOTAL_TTC, BON_LIVR, DATE_BL, NM_BNOTE, CODE_STE, CREATEUR, TYP_BR, NM_COMMENT)
        SELECT obe.no_br, obe.DATE_PER, obe.no_cde, cae.VAL_DEVIS, :DATE_CRE, :TOTAL_HT, :TOTAL_TTC, obe.no_bl, obe.date_bl, :NM_BNOTE, cae.CODE_STE, obe.login, obe.TYPE_BR, :NM_COMMENT
        FROM oxa_br_entete obe WITH (NOLOCK)
            LEFT JOIN CA_ENTET cae
                ON cae.NO_CDE = obe.no_cde
        WHERE obe.login = :login";

        $params = [
            ':login' => [
                'option' => PDO::PARAM_STR,
                'value' => $login
            ]
        ];

        $params = \array_merge($params, $insertArray);

        $register = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $register->bindParam($key, $param['value'], $param['option']);
        }

        return $register->execute();
    }

    /**
     * saveBrEntete
     *
     * @param  string $login
     * @param  array<mixed> $insertArray
     * @return bool
     */
    public function saveBrEnteteRetour(string $login, array $insertArray): bool
    {
        $query = "INSERT INTO BR_RECEP (NUM_BR, DATE_PER, NUM_CDE, VAL_DEV, DATE_CRE, TOTAL_HT, TOTAL_TTC, BON_LIVR, DATE_BL, NM_BNOTE, CODE_STE, CREATEUR, TYP_BR, NO_BR_ANN, NM_COMMENT)
        SELECT obe.no_br, obe.DATE_PER, obe.no_cde, cae.VAL_DEVIS, :DATE_CRE, :TOTAL_HT, :TOTAL_TTC, obe.no_bl, obe.date_bl, :NM_BNOTE, cae.CODE_STE, obe.login, obe.TYPE_BR, obe.NUM_BR_ANNULE, :NM_COMMENT
        FROM oxa_br_entete obe WITH (NOLOCK)
            LEFT JOIN CA_ENTET cae
                ON cae.NO_CDE = obe.no_cde
        WHERE obe.login = :login";

        $params = [
            ':login' => [
                'option' => PDO::PARAM_STR,
                'value' => $login
            ]
        ];

        $params = \array_merge($params, $insertArray);

        $register = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $register->bindParam($key, $param['value'], $param['option']);
        }

        return $register->execute();
    }

    /**
     * saveBrLigne
     *
     * @param  array<mixed> $insertArray
     * @return bool
     */
    public function saveBrLigne(array $insertArray): bool
    {
        $query = "SELECT ''
        DECLARE
        @p_i_Num_Br INT                       = 0,
        @p_i_Num_Cde INT                      = 0,
        @p_i_Login VARCHAR(30)                = ' ',
        @v_login VARCHAR(30)                  = ' ',
        @v_no_ligne_cde INT                   = 0,
        @v_no_ligne_br INT                    = 0,
        @v_code_art VARCHAR(13)               = ' ',
        @v_design_sommaire VARCHAR(60)        = ' ',
        @v_unite VARCHAR(7)                   = ' ',
        @v_is_reception BINARY(1)             = 0x46,
        @v_ral FLOAT                          = 0,
        @v_qte_r FLOAT                        = 0,
        @v_qte_c FLOAT                        = 0,
        @v_qte_a FLOAT                        = 0,
        @v_is_incident BINARY(1)              = 0x46,
        @v_code_inc VARCHAR(7)                = ' ',
        @v_comment_inc VARCHAR(3000)          = ' ',
        @v_is_ctrl_qualite BINARY(1)          = 0x46,
        @v_ctrl_qualite_done BINARY(1)        = 0x46,
        @v_type_ctrl VARCHAR(3)               = ' ',
        @v_libelle_ctrl VARCHAR(20)           = ' ',
        @v_design_comp VARCHAR(3000)          = ' ',
        @v_QTE_ANNULE FLOAT                   = 0,
        @v_CODE_SI VARCHAR(14)                = ' ',
        @v_CODE_CG VARCHAR(14)                = ' ',
        @v_QTE_DEJA_ANNULE FLOAT              = 0,
        @v_IS_ANNULE INT                      = 0,
        @v_CODE_CDC VARCHAR(14)               = ' ',
        @v_CODE_SCA VARCHAR(14)               = ' ',
        @v_SI_LIBELLE VARCHAR(60)             = ' ',
        @v_CG_LIBELLE VARCHAR(60)             = ' ',
        @v_CDC_LIBELLE VARCHAR(60)            = ' ',
        @v_SCA_LIBELLE VARCHAR(60)            = ' ',
        @v_COMMENT VARCHAR(3000)              = ' ',
        @v_REF_FOUR VARCHAR(30)               = ' ',
        @v_REF_FAB VARCHAR(30)                = ' ',
        @v_DTE_LIV CHAR (8)                   = '00000000',
        @v_EN_MNT BINARY(1)                   = 0x46,
        @v_MNT_REC FLOAT                      = 0,
        @v_MNT_RET FLOAT                      = 0,
        @v_PU_REMISE FLOAT                    = 0,
        @v_COD_DEV VARCHAR(3)                 = ' ',
        @v_TYPE_RECEPT_POSSIBLE VARCHAR(1)    = ' ',
        @v_price_divider FLOAT                = 0

        SET @p_i_Num_Br                       = :NUM_BR
        SET @p_i_Num_Cde                      = :NUM_CDE
        SET @p_i_Login                        = :login
        SELECT @v_price_divider = 
			CASE 
				WHEN (SELECT LIB01PARV FROM PARAVRAC WHERE TYPEPARV ='OXAPROC' AND CODEPARV ='BR_SAISIE_TTC') = 'O'
				THEN 1 + cal.TVA / 100
				ELSE 1
			END 
		FROM CA_LIGNE cal WITH (NOLOCK)
		WHERE NO_CDE = @p_i_Num_Cde 
        
        -- on démarre une boucle sur l'ensemble des lignes d'oxa_br_ligne
        DECLARE lines_list
            CURSOR FOR
                SELECT login, no_ligne_cde, no_ligne_br, code_art, design_sommaire, unite, is_reception, ral, qte_r, qte_c, qte_a, is_incident, code_inc, comment_inc, is_ctrl_qualite, ctrl_qualite_done, type_ctrl, libelle_ctrl, design_comp, QTE_ANNULE, CODE_SI, CODE_CG, QTE_DEJA_ANNULE, IS_ANNULE, CODE_CDC, CODE_SCA, SI_LIBELLE, CG_LIBELLE, CDC_LIBELLE, SCA_LIBELLE, COMMENT, REF_FOUR, REF_FAB, DTE_LIV, EN_MNT, MNT_REC, MNT_RET, PU_REMISE, COD_DEV, TYPE_RECEPT_POSSIBLE 
                FROM oxa_br_ligne
                WHERE login = @p_i_Login
        
        OPEN lines_list
            FETCH NEXT FROM lines_list
                INTO @v_login, @v_no_ligne_cde, @v_no_ligne_br, @v_code_art, @v_design_sommaire, @v_unite, @v_is_reception, @v_ral, @v_qte_r, @v_qte_c, @v_qte_a, @v_is_incident, @v_code_inc, @v_comment_inc, @v_is_ctrl_qualite, @v_ctrl_qualite_done, @v_type_ctrl, @v_libelle_ctrl, @v_design_comp, @v_QTE_ANNULE, @v_CODE_SI, @v_CODE_CG, @v_QTE_DEJA_ANNULE, @v_IS_ANNULE, @v_CODE_CDC, @v_CODE_SCA, @v_SI_LIBELLE, @v_CG_LIBELLE, @v_CDC_LIBELLE, @v_SCA_LIBELLE, @v_COMMENT, @v_REF_FOUR, @v_REF_FAB, @v_DTE_LIV, @v_EN_MNT, @v_MNT_REC, @v_MNT_RET, @v_PU_REMISE, @v_COD_DEV, @v_TYPE_RECEPT_POSSIBLE
            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF (@v_is_reception = 0x54)
                    BEGIN
                        INSERT INTO BR_LIGNE (NUM_BR, NO_PO_BR, NUM_CDE, NO_PO_CDE, CODE_ART, DESIGN, QTE_RECUE, QTE_COMPT, QTE_ACCEP,
                                    MNT_REC, TYPE_CTRL, A_CONTROL, CTRL_FAIT, CODE_STE, EN_MNT, NM_COMMENT, NM_DESCOM)
                        VALUES (@p_i_Num_Br, (SELECT ISNULL(MAX(NO_PO_BR), 0) + 1 FROM BR_LIGNE WITH(NOLOCK) WHERE NUM_BR = @p_i_Num_Br), @p_i_Num_Cde, @v_no_ligne_cde, @v_code_art, @v_design_sommaire,
                        CASE
                            WHEN @v_EN_MNT <> 0x54
                            THEN @v_qte_r
                            ELSE 0
                        END,
                        CASE
                            WHEN @v_EN_MNT <> 0x54
                            THEN @v_qte_c
                            ELSE 0
                        END,
                        CASE
                            WHEN @v_EN_MNT <> 0x54
                            THEN @v_qte_a
                            ELSE 0
                        END,
                        CASE
                            WHEN @v_EN_MNT = 0x54
                            THEN @v_MNT_REC
                            ELSE 0
                        END,
                        @v_type_ctrl, @v_is_ctrl_qualite,
                        CASE
                            WHEN @v_is_ctrl_qualite = 0x54
                            THEN @v_ctrl_qualite_done
                            ELSE 0x54
                        END,
                        (SELECT CODE_STE FROM CA_ENTET WHERE NO_CDE = @p_i_Num_Cde), @v_EN_MNT, 0, 0)
                    END
        
                -- on passe alors à la ligne suivante
                FETCH NEXT FROM lines_list
                    INTO @v_login, @v_no_ligne_cde, @v_no_ligne_br, @v_code_art, @v_design_sommaire, @v_unite, @v_is_reception, @v_ral, @v_qte_r, @v_qte_c, @v_qte_a, @v_is_incident, @v_code_inc, @v_comment_inc, @v_is_ctrl_qualite, @v_ctrl_qualite_done, @v_type_ctrl, @v_libelle_ctrl, @v_design_comp, @v_QTE_ANNULE, @v_CODE_SI, @v_CODE_CG, @v_QTE_DEJA_ANNULE, @v_IS_ANNULE, @v_CODE_CDC, @v_CODE_SCA, @v_SI_LIBELLE, @v_CG_LIBELLE, @v_CDC_LIBELLE, @v_SCA_LIBELLE, @v_COMMENT, @v_REF_FOUR, @v_REF_FAB, @v_DTE_LIV, @v_EN_MNT, @v_MNT_REC, @v_MNT_RET, @v_PU_REMISE, @v_COD_DEV, @v_TYPE_RECEPT_POSSIBLE
            END
        CLOSE lines_list
        DEALLOCATE lines_list";

        $params = $insertArray;

        $register = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $register->bindParam($key, $param['value'], $param['option']);
        }

        return $register->execute();
    }

    /**
     * saveBrLigne
     *
     * @param  array<mixed> $insertArray
     * @return bool
     */
    public function saveBrLigneRetour(array $insertArray): bool
    {
        $query = "SELECT ''
        DECLARE
        @p_i_Num_Br INT                       = 0,
        @p_i_Num_Cde INT                      = 0,
        @p_i_Login VARCHAR(30)                = ' ',
        @v_login VARCHAR(30)                  = ' ',
        @v_no_ligne_cde INT                   = 0,
        @v_no_ligne_br INT                    = 0,
        @v_code_art VARCHAR(13)               = ' ',
        @v_design_sommaire VARCHAR(60)        = ' ',
        @v_unite VARCHAR(7)                   = ' ',
        @v_is_reception BINARY(1)             = 0x46,
        @v_ral FLOAT                          = 0,
        @v_qte_r FLOAT                        = 0,
        @v_qte_c FLOAT                        = 0,
        @v_qte_a FLOAT                        = 0,
        @v_is_incident BINARY(1)              = 0x46,
        @v_code_inc VARCHAR(7)                = ' ',
        @v_comment_inc VARCHAR(3000)          = ' ',
        @v_is_ctrl_qualite BINARY(1)          = 0x46,
        @v_ctrl_qualite_done BINARY(1)        = 0x46,
        @v_type_ctrl VARCHAR(3)               = ' ',
        @v_libelle_ctrl VARCHAR(20)           = ' ',
        @v_design_comp VARCHAR(3000)          = ' ',
        @v_QTE_ANNULE FLOAT                   = 0,
        @v_CODE_SI VARCHAR(14)                = ' ',
        @v_CODE_CG VARCHAR(14)                = ' ',
        @v_QTE_DEJA_ANNULE FLOAT              = 0,
        @v_IS_ANNULE INT                      = 0,
        @v_CODE_CDC VARCHAR(14)               = ' ',
        @v_CODE_SCA VARCHAR(14)               = ' ',
        @v_SI_LIBELLE VARCHAR(60)             = ' ',
        @v_CG_LIBELLE VARCHAR(60)             = ' ',
        @v_CDC_LIBELLE VARCHAR(60)            = ' ',
        @v_SCA_LIBELLE VARCHAR(60)            = ' ',
        @v_COMMENT VARCHAR(3000)              = ' ',
        @v_REF_FOUR VARCHAR(30)               = ' ',
        @v_REF_FAB VARCHAR(30)                = ' ',
        @v_DTE_LIV CHAR (8)                   = '00000000',
        @v_EN_MNT BINARY(1)                   = 0x46,
        @v_MNT_REC FLOAT                      = 0,
        @v_MNT_RET FLOAT                      = 0,
        @v_PU_REMISE FLOAT                    = 0,
        @v_COD_DEV VARCHAR(3)                 = ' ',
        @v_TYPE_RECEPT_POSSIBLE VARCHAR(1)    = ' ',
        @v_price_divider FLOAT                = 0

        SET @p_i_Num_Br                       = :NUM_BR
        SET @p_i_Num_Cde                      = :NUM_CDE
        SET @p_i_Login                        = :login
        SELECT @v_price_divider = 
			CASE 
				WHEN (SELECT LIB01PARV FROM PARAVRAC WHERE TYPEPARV ='OXAPROC' AND CODEPARV ='BR_SAISIE_TTC') = 'O'
				THEN 1 + cal.TVA / 100
				ELSE 1
			END 
		FROM CA_LIGNE cal WITH (NOLOCK)
		WHERE NO_CDE = @p_i_Num_Cde 
        
        -- on démarre une boucle sur l'ensemble des lignes d'oxa_br_ligne
        DECLARE lines_list
            CURSOR FOR
                SELECT login, no_ligne_cde, no_ligne_br, code_art, design_sommaire, unite, is_reception, ral, qte_r, qte_c, qte_a, is_incident, code_inc, comment_inc, is_ctrl_qualite, ctrl_qualite_done, type_ctrl, libelle_ctrl, design_comp, QTE_ANNULE, CODE_SI, CODE_CG, QTE_DEJA_ANNULE, IS_ANNULE, CODE_CDC, CODE_SCA, SI_LIBELLE, CG_LIBELLE, CDC_LIBELLE, SCA_LIBELLE, COMMENT, REF_FOUR, REF_FAB, DTE_LIV, EN_MNT, (MNT_REC / @v_price_divider), (MNT_RET / @v_price_divider), (PU_REMISE / @v_price_divider), COD_DEV, TYPE_RECEPT_POSSIBLE 
                FROM oxa_br_ligne
                WHERE login = @p_i_Login
        
        OPEN lines_list
            FETCH NEXT FROM lines_list
                INTO @v_login, @v_no_ligne_cde, @v_no_ligne_br, @v_code_art, @v_design_sommaire, @v_unite, @v_is_reception, @v_ral, @v_qte_r, @v_qte_c, @v_qte_a, @v_is_incident, @v_code_inc, @v_comment_inc, @v_is_ctrl_qualite, @v_ctrl_qualite_done, @v_type_ctrl, @v_libelle_ctrl, @v_design_comp, @v_QTE_ANNULE, @v_CODE_SI, @v_CODE_CG, @v_QTE_DEJA_ANNULE, @v_IS_ANNULE, @v_CODE_CDC, @v_CODE_SCA, @v_SI_LIBELLE, @v_CG_LIBELLE, @v_CDC_LIBELLE, @v_SCA_LIBELLE, @v_COMMENT, @v_REF_FOUR, @v_REF_FAB, @v_DTE_LIV, @v_EN_MNT, @v_MNT_REC, @v_MNT_RET, @v_PU_REMISE, @v_COD_DEV, @v_TYPE_RECEPT_POSSIBLE
            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF (@v_IS_ANNULE = 1)
                    BEGIN
                        INSERT INTO BR_LIGNE (NUM_BR, NO_PO_BR, NUM_CDE, NO_PO_CDE, CODE_ART, DESIGN, CODE_STE, NM_DESCOM, NOBRLIGANN, QTERETOUR, NM_COMMENT, EN_MNT, MNT_RET, TYPE_CTRL, A_CONTROL, CTRL_FAIT)
                        VALUES (@p_i_Num_Br, (SELECT ISNULL(MAX(NO_PO_BR), 0) + 1 FROM BR_LIGNE WITH(NOLOCK) WHERE NUM_BR = @p_i_Num_Br), @p_i_Num_Cde, @v_no_ligne_cde, @v_code_art, @v_design_sommaire, 
						(SELECT CODE_STE FROM CA_ENTET WHERE NO_CDE = @p_i_Num_Cde), 
						0,   
						@v_no_ligne_br,
						CASE
							WHEN @v_EN_MNT = 0x54
							THEN 0
							ELSE @v_QTE_ANNULE
						END, 
						0, @v_EN_MNT,
						CASE
							WHEN @v_EN_MNT = 0x54
							THEN @v_MNT_RET
							ELSE 0
						END, 
                        @v_type_ctrl, @v_is_ctrl_qualite,
                        CASE
                            WHEN @v_is_ctrl_qualite = 0x54
                            THEN @v_ctrl_qualite_done
                            ELSE 0x54
                        END)
                    END
                
                -- on passe alors à la ligne suivante
                FETCH NEXT FROM lines_list
                    INTO @v_login, @v_no_ligne_cde, @v_no_ligne_br, @v_code_art, @v_design_sommaire, @v_unite, @v_is_reception, @v_ral, @v_qte_r, @v_qte_c, @v_qte_a, @v_is_incident, @v_code_inc, @v_comment_inc, @v_is_ctrl_qualite, @v_ctrl_qualite_done, @v_type_ctrl, @v_libelle_ctrl, @v_design_comp, @v_QTE_ANNULE, @v_CODE_SI, @v_CODE_CG, @v_QTE_DEJA_ANNULE, @v_IS_ANNULE, @v_CODE_CDC, @v_CODE_SCA, @v_SI_LIBELLE, @v_CG_LIBELLE, @v_CDC_LIBELLE, @v_SCA_LIBELLE, @v_COMMENT, @v_REF_FOUR, @v_REF_FAB, @v_DTE_LIV, @v_EN_MNT, @v_MNT_REC, @v_MNT_RET, @v_PU_REMISE, @v_COD_DEV, @v_TYPE_RECEPT_POSSIBLE
            END
        CLOSE lines_list
        DEALLOCATE lines_list";

        $params = $insertArray;

        $register = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $register->bindParam($key, $param['value'], $param['option']);
        }

        return $register->execute();
    }

    /**
     * saveCcBr
     *
     * @return void
     */
    public function saveCcBr(): void
    {
        $query = "INSERT INTO CC_LDOC(DOC_TYPE, DOC_NUM, DOC_LINE_NUM, ID_USER_BASKET, CC_ID, CC_FREE, CC_DISPLAY_NUM, CC_IS_TITLE, CC_NAME, CC_TYPE,
        CC_UPD_ONLY_RECEIPT, CC_INVIS_RECEIPT, CC_REQUIRED, CC_PRINTED_OF, CC_CALC, CC_CALC_EXP, CC_VALUE, DOC_AMD)
            SELECT 'BR', obe.no_br, obl.no_ligne_br, cc.ID_USER_BASKET, cc.CC_ID, cc.CC_FREE, cc.CC_DISPLAY_NUM, cc.CC_IS_TITLE, cc.CC_NAME, cc.CC_TYPE,
            cc.CC_UPD_ONLY_RECEIPT, cc.CC_INVIS_RECEIPT, cc.CC_REQUIRED, cc.CC_PRINTED_OF, cc.CC_CALC, cc.CC_CALC_EXP, cc.CC_VALUE, cc.DOC_AMD
            FROM CC_LDOC cc WITH(NOLOCK)
                LEFT join oxa_br_ligne obl
                    ON obl.no_ligne_cde = cc.DOC_LINE_NUM
                LEFT JOIN oxa_br_entete obe
                    ON obe.login = obl.login
            WHERE DOC_TYPE = 'BRT'
                AND obe.login = :login
                AND DOC_NUM = 0 
				AND ID_USER_BASKET = :ID_USER_BASKET";

        $params = [
            ':login' => $_SESSION['Session_Login'],
            ':ID_USER_BASKET' => $_SESSION['id_user'],
        ];

        $this->execute($query, $params);

        return;
    }

    public function initCcBrModif(): void
    {
        $query = "INSERT INTO CC_LDOC(DOC_TYPE, DOC_NUM, DOC_LINE_NUM, ID_USER_BASKET, CC_ID, CC_FREE, CC_DISPLAY_NUM, CC_IS_TITLE, CC_NAME, CC_TYPE,
        CC_UPD_ONLY_RECEIPT, CC_INVIS_RECEIPT, CC_REQUIRED, CC_PRINTED_OF, CC_CALC, CC_CALC_EXP, CC_VALUE, DOC_AMD)
            SELECT 'BRT', obe.no_br, obl.no_ligne_br, :ID_USER_BASKET, cc.CC_ID, cc.CC_FREE, cc.CC_DISPLAY_NUM, cc.CC_IS_TITLE, cc.CC_NAME, cc.CC_TYPE,
            cc.CC_UPD_ONLY_RECEIPT, cc.CC_INVIS_RECEIPT, cc.CC_REQUIRED, cc.CC_PRINTED_OF, cc.CC_CALC, cc.CC_CALC_EXP, cc.CC_VALUE, cc.DOC_AMD
            FROM CC_LDOC cc WITH(NOLOCK)
                LEFT join oxa_br_ligne obl
                    ON obl.no_ligne_cde = cc.DOC_LINE_NUM
                LEFT JOIN oxa_br_entete obe
                    ON obe.login = obl.login
                        AND obe.no_br = cc.DOC_NUM
            WHERE DOC_TYPE = 'BR'
                AND obe.login = :login";

        $params = [
            ':login' => $_SESSION['Session_Login'],
            ':ID_USER_BASKET' => $_SESSION['id_user'],
        ];

        $this->execute($query, $params);

        return;
    }

    /**
     * enregistrement CS_DON : COD_TAB = 'BR_OXAPROC' => 'BR_RECEP'
     */
    public function saveCsBr(int $br): void
    {
        $numBr = ChampSupp::autoCompletCodeEnr((string)$br);
        $this->execute(
            "DELETE FROM CS_DON 
            WHERE COD_TAB = 'BR_RECEP'
                AND COD_ENR = :NUM_BR",
            [':NUM_BR'  => $numBr]
        );

        // TODO : merge the INSERT and the deleteCsTempBr into an UPDATE
        $this->execute(
            "INSERT INTO CS_DON (COD_TAB, COD_ENR, NUM_FIX, VALEUR, VALEUR_COMP)
            SELECT 'BR_RECEP', :NUM_BR, NUM_FIX, VALEUR, VALEUR_COMP
            FROM CS_DON with(nolock)
            WHERE COD_TAB = 'BR_OXAPROC'
                AND COD_ENR = :login",
            [
                ':login'   => $_SESSION['Session_Login'],
                ':NUM_BR'  => $numBr,
            ]
        );
        // suppression dans la table CS_DON WHERE COD_TAB = 'BR_OXAPROC' (BR en cours de création)
        $this->deleteCsTempBr();
    }

    public function initCsBrModif(string $br): bool
    {
        $query = "INSERT INTO CS_DON (COD_TAB, COD_ENR, NUM_FIX, VALEUR, VALEUR_COMP)
            SELECT 'BR_OXAPROC', :login, NUM_FIX, VALEUR, VALEUR_COMP
            FROM CS_DON WITH(NOLOCK)
            WHERE COD_TAB = 'BR_RECEP'
                AND COD_ENR = :NUM_BR";

        $insert = $this->pdo->prepare($query);

        $params = [
            ':login' => [
                'option' => PDO::PARAM_STR,
                'value' => $_SESSION['Session_Login']
            ],
            ':NUM_BR' => [
                'option' => PDO::PARAM_STR,
                'value' => \str_pad($br, 9, '0', STR_PAD_LEFT)
            ],
        ];

        foreach ($params as $key => $param) {
            $insert->bindParam($key, $param['value'], $param['option']);
        }

        return $insert->execute();
    }

    /**
     * deleteCsTempBr
     *
     * @return bool
     */
    public function deleteCsTempBr(): bool
    {
        $query = "DELETE FROM CS_DON
        WHERE CS_DON.COD_TAB = 'BR_OXAPROC'
            AND CS_DON.COD_ENR = :login";

        $params = [
            ':login' => [
                'value' => $_SESSION['Session_Login'],
                'option' => PDO::PARAM_STR
            ],
        ];

        $data = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $data->bindParam($key, $param['value'], $param['option']);
        }

        return $data->execute();
    }

    /**
     * @return array<mixed>
     */
    public function getBuyer(): array
    {
        return $this->fetchAssocOne(
            "SELECT buyer.*
            FROM oxa_br_entete obe
            LEFT JOIN CA_ENTET cae
                ON cae.NO_CDE = obe.no_cde
            LEFT JOIN HASTUTIL buyer
                ON buyer.CODE = cae.ACH
            WHERE obe.login = :login",
            [':login' => $_SESSION['Session_Login']]
        );
    }

    /**
     * getBuyerFromBr
     *
     * @param  string $br
     * @return array<mixed>
     */
    public function getBuyerFromBr(string $br): array
    {
        return $this->fetchAssocOne(
            "SELECT buyer.*
            FROM HASTUTIL buyer
                LEFT JOIN CA_ENTET cae
                    ON buyer.CODE = cae.ACH
                LEFT JOIN BR_RECEP brc
                    ON brc.NUM_CDE = cae.NO_CDE
            WHERE brc.NUM_BR = :NUM_BR",
            [':NUM_BR' => $br]
        );
    }

    public function initOxaBrLigneRetour(int $brNumber): bool
    {
        $sectimpu = Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE') ? ' cae.CODE_STE ' : " ' ' ";
        $codecomp = Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE') ? ' cae.CODE_STE ' : " ' ' ";
        $sscptana = Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE') ? ' cae.CODE_STE ' : " ' ' ";

        $query = "INSERT INTO oxa_br_ligne (login, no_ligne_cde, no_ligne_br, code_art, design_sommaire, unite, ral, qte_r, qte_c, qte_a, MNT_REC, design_comp, CODE_SI, CODE_CG, QTE_DEJA_ANNULE, CODE_CDC, CODE_SCA, SI_LIBELLE, CG_LIBELLE, CDC_LIBELLE, SCA_LIBELLE, REF_FOUR, REF_FAB, DTE_LIV, EN_MNT, COD_DEV, TYPE_RECEPT_POSSIBLE, PU_REMISE)
        SELECT :login, brl.NO_PO_CDE, brl.NO_PO_BR, brl.CODE_ART, brl.DESIGN, cal.UNITE, 
            (
                SELECT SUM(QTE_COMM)
                FROM CA_PL_PR
                WHERE NO_CA = cal.NO_CDE
                    AND NO_PO_CA = cal.NO_PO
            ) - ISNULL((
                SELECT 
                    SUM(CASE 
                        WHEN brl.EN_MNT = 0x54
                        THEN brl.MNT_REC / (cal2.PU) * (1 - cal2.REMISE / 100)
                        ELSE (
                            CASE
                                WHEN (brl.A_CONTROL = 0x46 OR brl.CTRL_FAIT = 0x54)
                                THEN brl.QTE_ACCEP
                                ELSE brl.QTE_COMPT
                            END)
                    END)
                FROM BR_LIGNE brl
                    LEFT JOIN CA_LIGNE cal2 ON cal2.NO_CDE = brl.NUM_CDE AND cal2.NO_PO = brl.NO_PO_CDE
                WHERE brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO
            ), 0) + ISNULL((
                SELECT 
                    SUM(CASE 
                        WHEN brl.EN_MNT = 0x54
                        THEN brl.MNT_RET / (cal2.PU) * (1 - cal2.REMISE / 100)
                        ELSE brl.QTERETOUR
                    END)
                FROM BR_LIGNE brl
                    LEFT JOIN CA_LIGNE cal2 ON cal2.NO_CDE = brl.NUM_CDE AND cal2.NO_PO = brl.NO_PO_CDE
                WHERE brl.NOBRLIGANN <> 0
                    AND brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO), 0), brl.QTE_RECUE, brl.QTE_COMPT, brl.QTE_ACCEP, brl.MNT_REC, ISNULL(m.CONTMEMO, ''), cal.SECTIMP, cal.CODGES, 
            ISNULL((SELECT 
                SUM(CASE 
                    WHEN brl2.EN_MNT = 0x54
                    THEN brl2.MNT_RET / (cal2.PU) * (1 - cal2.REMISE / 100)
                    ELSE brl2.QTERETOUR
                END)
            FROM BR_LIGNE brl2
                LEFT JOIN CA_LIGNE cal2 ON cal2.NO_CDE = brl.NUM_CDE AND cal2.NO_PO = brl.NO_PO_CDE
                LEFT JOIN BR_RECEP brc2 ON brc2.NO_BR_ANN = brl.NUM_BR
            WHERE brl2.NOBRLIGANN <> 0
                AND brl2.NUM_CDE = cal.NO_CDE
                AND brl2.NO_PO_CDE = cal.NO_PO
                AND brl2.NUM_BR = brc2.NUM_BR
                AND brc2.DATE_VAL <> '00000000'), 0),
            cal.CODE_COMP, cal.SSCPTEANA, 
            ISNULL(si.LIBELLE, ''), cal.LIBGES, 
            ISNULL(cdc.LIBELLE, ''), 
            ISNULL(sca.LIBELLE, ''), cal.REF_FOUR, cal.REF_FAB, br.DATE_BL, brl.EN_MNT, cae.DEVISE,
            CASE 
                WHEN brl.EN_MNT = 0x54
                THEN 'M'
                ELSE 'Q'
            END,
            (cal.PU) * (1 - cal.REMISE / 100)
        FROM BR_LIGNE brl WITH(NOLOCK)
            LEFT JOIN CA_LIGNE cal WITH(NOLOCK) ON cal.NO_CDE = brl.NUM_CDE AND cal.NO_PO = brl.NO_PO_CDE
            LEFT JOIN CA_ENTET cae WITH(NOLOCK) ON cae.NO_CDE = brl.NUM_CDE
            LEFT JOIN MEMOS m WITH(NOLOCK) ON m.NODEMEMO = cal.NM_DCOMP
            LEFT JOIN SECTIMPU si WITH(NOLOCK) ON si.CODE_SI = cal.SECTIMP AND si.CODE_STE = {$sectimpu}
            LEFT JOIN CODECOMP cdc WITH(NOLOCK) ON cdc.CODE_CDC = cal.CODE_COMP AND cdc.CODE_STE = {$codecomp}
            LEFT JOIN SSCPTANA sca WITH(NOLOCK) ON sca.CODE_SCA = cal.SSCPTEANA AND sca.CODE_STE = {$sscptana}
            LEFT JOIN BR_RECEP br WITH(NOLOCK) ON br.NUM_BR = brl.NUM_BR
        WHERE brl.NUM_BR = :NUM_BR";

        $params = [
            ':NUM_BR' => [
                'value' => $brNumber,
                'option' => PDO::PARAM_INT
            ],
            ':login' => [
                'value' => $_SESSION['Session_Login'],
                'option' => PDO::PARAM_STR
            ]
        ];

        $insert = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $insert->bindParam($key, $param['value'], $param['option']);
        }

        return $insert->execute();
    }
    public function initOxaBrLigneModif(string $brNumber): bool
    {
        $sectimpu = Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE') ? ' cae.CODE_STE ' : " ' ' ";
        $codecomp = Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE') ? ' cae.CODE_STE ' : " ' ' ";
        $sscptana = Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE') ? ' cae.CODE_STE ' : " ' ' ";

        $query = "INSERT INTO oxa_br_ligne (login, no_ligne_cde, no_ligne_br, code_art, design_sommaire, unite, ral, qte_r, qte_c, qte_a, MNT_REC, design_comp, CODE_SI, CODE_CG, QTE_DEJA_ANNULE, CODE_CDC, CODE_SCA, SI_LIBELLE, CG_LIBELLE, CDC_LIBELLE, SCA_LIBELLE, REF_FOUR, REF_FAB, DTE_LIV, EN_MNT, COD_DEV, TYPE_RECEPT_POSSIBLE, PU_REMISE)
        SELECT :login, brl.NO_PO_CDE, brl.NO_PO_BR, brl.CODE_ART, brl.DESIGN, cal.UNITE, 
            (
                SELECT SUM(QTE_COMM)
                FROM CA_PL_PR
                WHERE NO_CA = cal.NO_CDE
                    AND NO_PO_CA = cal.NO_PO
            ) - ISNULL((
                SELECT 
                    SUM(CASE 
                        WHEN brl2.EN_MNT = 0x54
                        THEN brl2.MNT_REC / (cal2.PU) * (1 - cal2.REMISE / 100)
                        ELSE (
                            CASE
                                WHEN (brl2.A_CONTROL = 0x46 OR brl2.CTRL_FAIT = 0x54)
                                THEN brl2.QTE_ACCEP
                                ELSE brl2.QTE_COMPT
                            END)
                    END)
                FROM BR_LIGNE brl2
                    LEFT JOIN CA_LIGNE cal2 ON cal2.NO_CDE = brl2.NUM_CDE AND cal2.NO_PO = brl2.NO_PO_CDE
                WHERE brl2.NUM_CDE = cal.NO_CDE
                    AND brl2.NO_PO_CDE = cal.NO_PO
                    AND brl2.NUM_BR <> brl.NUM_BR
            ), 0) + ISNULL((
                SELECT 
                    SUM(CASE 
                        WHEN brl.EN_MNT = 0x54
                        THEN brl.MNT_RET / (cal2.PU) * (1 - cal2.REMISE / 100)
                        ELSE brl.QTERETOUR
                    END)
                FROM BR_LIGNE brl
                    LEFT JOIN CA_LIGNE cal2 ON cal2.NO_CDE = brl.NUM_CDE AND cal2.NO_PO = brl.NO_PO_CDE
                WHERE brl.NOBRLIGANN <> 0
                    AND brl.NUM_CDE = cal.NO_CDE
                    AND brl.NO_PO_CDE = cal.NO_PO), 0), brl.QTE_RECUE, brl.QTE_COMPT, brl.QTE_ACCEP, brl.MNT_REC, ISNULL(m.CONTMEMO, ''), cal.SECTIMP, cal.CODGES, 
            ISNULL((SELECT 
                SUM(CASE 
                    WHEN brl2.EN_MNT = 0x54
                    THEN brl2.MNT_RET / (cal2.PU) * (1 - cal2.REMISE / 100)
                    ELSE brl2.QTERETOUR
                END)
            FROM BR_LIGNE brl2
                LEFT JOIN CA_LIGNE cal2 ON cal2.NO_CDE = brl.NUM_CDE AND cal2.NO_PO = brl.NO_PO_CDE
            WHERE brl2.NOBRLIGANN <> 0
                AND brl2.NUM_CDE = cal.NO_CDE
                AND brl2.NO_PO_CDE = cal.NO_PO
                AND brl2.NUM_BR = brl.NUM_BR), 0), 
            cal.CODE_COMP, cal.SSCPTEANA, 
            ISNULL(si.LIBELLE, ''), cal.LIBGES, 
            ISNULL(cdc.LIBELLE, ''), 
            ISNULL(sca.LIBELLE, ''), cal.REF_FOUR, cal.REF_FAB, br.DATE_BL, brl.EN_MNT, cae.DEVISE,
            CASE 
                WHEN brl.EN_MNT = 0x54
                THEN 'M'
                ELSE 'Q'
            END,
            (cal.PU) * (1 - cal.REMISE / 100)
        FROM BR_LIGNE brl WITH(NOLOCK)
            LEFT JOIN CA_LIGNE cal WITH(NOLOCK) ON cal.NO_CDE = brl.NUM_CDE AND cal.NO_PO = brl.NO_PO_CDE
            LEFT JOIN CA_ENTET cae WITH(NOLOCK) ON cae.NO_CDE = brl.NUM_CDE
            LEFT JOIN MEMOS m WITH(NOLOCK) ON m.NODEMEMO = cal.NM_DCOMP
            LEFT JOIN SECTIMPU si WITH(NOLOCK) ON si.CODE_SI = cal.SECTIMP AND si.CODE_STE = {$sectimpu}
            LEFT JOIN CODECOMP cdc WITH(NOLOCK) ON cdc.CODE_CDC = cal.CODE_COMP AND cdc.CODE_STE = {$codecomp}
            LEFT JOIN SSCPTANA sca WITH(NOLOCK) ON sca.CODE_SCA = cal.SSCPTEANA AND sca.CODE_STE = {$sscptana}
            LEFT JOIN BR_RECEP br WITH(NOLOCK) ON br.NUM_BR = brl.NUM_BR
        WHERE brl.NUM_BR = :NUM_BR";

        $params = [
            ':NUM_BR' => [
                'value' => $brNumber,
                'option' => PDO::PARAM_INT
            ],
            ':login' => [
                'value' => $_SESSION['Session_Login'],
                'option' => PDO::PARAM_STR
            ]
        ];

        $insert = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $insert->bindParam($key, $param['value'], $param['option']);
        }

        return $insert->execute();
    }

    public function updateOxaBrLigneRetourModif(): bool
    {
        $query = "UPDATE obl
        SET obl.QTE_ANNULE = ISNULL(brl.QTERETOUR, 0), obl.IS_ANNULE = CASE WHEN ISNULL(brl.QTERETOUR, 0) = 0 THEN 0 ELSE 1 END
        FROM oxa_br_ligne obl
            LEFT JOIN oxa_br_entete obe ON obe.login = obl.login
            LEFT JOIN BR_LIGNE brl ON brl.NO_PO_CDE = obl.no_ligne_cde AND brl.NUM_BR = obe.no_br
        WHERE obl.login = :login";

        $params = [
            ':login' => [
                'value' => $_SESSION['Session_Login'],
                'option' => PDO::PARAM_STR
            ]
        ];

        $insert = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $insert->bindParam($key, $param['value'], $param['option']);
        }

        return $insert->execute();
    }

    public function updateBrLigneQteRep(int $brNumber, string $type): bool
    {
        if ($type == 'R') {
            $noPo = 'NO_PO_CDE';
        } else {
            $noPo = 'NO_PO_BR';
        }

        $query = "SELECT ''
        DECLARE
            @p_i_Num_Br INT           = 0,
            @p_i_Num_Cde INT          = 0,
            @p_i_Login VARCHAR(30)    = ' ',
            @v_login VARCHAR(30)      = ' ',
            @v_NO_BR INT              = 0,
            @v_NO_LIG_BR INT          = 0,
            @v_CODE_ART VARCHAR(30)   = 0,
            @v_NO_LOT VARCHAR(30)     = ' ',
            @v_QTE_LOT FLOAT          = 0,
            @v_PRIX_LOT INT           = 0,
            @v_DTE_PEREM CHAR(8)      = '00000000',
            @v_previous_line_br int   = 0,
            @v_cumul_qte FLOAT        = 0
        
        SET @p_i_Num_Br           = :brNumber
        SET @p_i_Login            = :login
        SET @v_previous_line_br   = 0

        UPDATE oxa_br_ligne_lots 
        SET NO_BR = @p_i_Num_Br
        WHERE login = @p_i_Login
                
        -- on démarre une boucle sur l'ensemble des lignes d'oxa_br_ligne_lots
        DECLARE lines_list
            CURSOR FOR
                SELECT obll.login, NO_BR, NO_LIG_BR, CODE_ART, NO_LOT, QTE_LOT, PRIX_LOT, DTE_PEREM
                FROM oxa_br_ligne_lots obll
                    LEFT JOIN oxa_br_ligne obl ON obl.login = obll.login AND obl.no_ligne_br = obll.NO_LIG_BR
                WHERE obll.login = @p_i_Login AND (obl.is_reception = 0x54 OR obl.IS_ANNULE = 1)
                
        OPEN lines_list
            FETCH NEXT FROM lines_list
                INTO @v_login, @v_NO_BR, @v_NO_LIG_BR, @v_CODE_ART, @v_NO_LOT, @v_QTE_LOT, @v_PRIX_LOT, @v_DTE_PEREM
            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF (@v_previous_line_br = @v_NO_LIG_BR)
                    BEGIN
                        SET @v_cumul_qte = @v_cumul_qte + @v_QTE_LOT
                        UPDATE BR_LIGNE SET QTE_REPLO = @v_cumul_qte WHERE NUM_BR = @p_i_Num_Br AND {$noPo} = @v_previous_line_br
                    END
                ELSE
                    BEGIN
                        SET @v_cumul_qte = @v_QTE_LOT
                        SET @v_previous_line_br = @v_NO_LIG_BR
                        UPDATE BR_LIGNE SET QTE_REPLO = @v_cumul_qte WHERE NUM_BR = @p_i_Num_Br AND {$noPo} = @v_previous_line_br
                    END

                -- on passe alors à la ligne suivante
                FETCH NEXT FROM lines_list
                    INTO @v_login, @v_NO_BR, @v_NO_LIG_BR, @v_CODE_ART, @v_NO_LOT, @v_QTE_LOT, @v_PRIX_LOT, @v_DTE_PEREM
            END
        CLOSE lines_list
        DEALLOCATE lines_list";

        $params = [
            ':brNumber' => [
                'value' => $brNumber,
                'option' => PDO::PARAM_INT
            ],
            ':login' => [
                'value' => $_SESSION['Session_Login'],
                'option' => PDO::PARAM_STR
            ]
        ];

        $update = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $update->bindParam($key, $param['value'], $param['option']);
        }

        return $update->execute();
    }

    public function updateRalCaLigne(string $order): bool
    {
        $query = "  UPDATE cal SET cal.RAL = (cal.QTE_COMM - ISNULL(
                            (SELECT SUM(brl.QTE_ACCEP - brl.QTERETOUR + (brl.MNT_REC - brl.MNT_RET) /
                                        (IIF(cal2.PU = 0 OR cal2.REMISE = 100,
                                            1,
                                        (cal2.PU * (1 - cal2.REMISE / 100) ))
                            ))
                    FROM BR_LIGNE brl
                    LEFT JOIN CA_LIGNE cal2 ON cal2.NO_CDE = brl.NUM_CDE AND cal2.NO_PO = brl.NO_PO_CDE
                    WHERE brl.NUM_CDE = cal.NO_CDE AND brl.NO_PO_CDE = cal.NO_PO
                    ),0))
                    FROM CA_LIGNE cal
                    WHERE cal.NO_CDE = :order
        ";

        $params = [
            ':order' => [
                'value' => $order,
                'option' => PDO::PARAM_INT
            ],
        ];

        $update = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $update->bindParam($key, $param['value'], $param['option']);
        }

        return $update->execute();
    }

    /**
     * isOrderSettled
     *
     * @param  string $brNumber
     * @return bool
     */
    public function isOrderSettled(string $brNumber): bool
    {
        $query = "SELECT NUM_BR 
        FROM BR_RECEP WITH(NOLOCK) 
        WHERE BR_RECEP.NUM_BR = :NUM_BR
            AND EXISTS (
                SELECT NULL 
                FROM CA_LIGNE WITH(NOLOCK) 
                WHERE CA_LIGNE.NO_CDE = BR_RECEP.NUM_CDE 
                    AND CA_LIGNE.RAL > 0 
                    AND CA_LIGNE.ET_CDELIG='E' 
                    AND CA_LIGNE.SOLD_PHYS=0x46 )";

        $params = [
            ':NUM_BR' => [
                'value' => $brNumber,
                'option' => PDO::PARAM_STR
            ],
        ];

        $check = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $check->bindParam($key, $param['value'], $param['option']);
        }

        $check->execute();
        $check = $check->fetch(PDO::FETCH_ASSOC);

        if (!empty($check)) {
            return false;
        }

        return true;
    }
}
