<?php

namespace Modules\Reception\Repository;

use Helpers\Outil\Date;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use PDO;

class Lot extends AbsRepository
{
    private PaginatorGestion $paginator;

    /**
     * __construct
     *
     * @param  PDO $pdo
     */
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
        $this->paginator = new PaginatorGestion();
    }

    public function resetOxaBrLigneLot(): bool
    {
        $query = "DELETE FROM oxa_br_ligne_lots
        WHERE login = :login ";

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

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

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

        return $delete->execute();
    }

    /**
     * initOxaBrLigneLot
     *
     * @param  string $order
     * @return bool
     */
    public function initOxaBrLigneLot(string $order): bool
    {
        $query = "INSERT INTO oxa_br_ligne_lots (login, NO_LIG_BR, CODE_ART, NO_LOT)
        SELECT :login, cal.NO_PO, cal.CODE_ART, 0
        FROM CA_LIGNE cal
            LEFT JOIN SARTICLE sa with(nolock) ON sa.CODE_ART = cal.CODE_ART
        WHERE cal.NO_CDE = :NO_CDE
            AND cal.SITE_STK <> ''
            AND sa.TRACABLE = 0x54 ";

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

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

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

        return $insert->execute();
    }

    public function initOxaBrLigneLotRetour(string $brNumber): bool
    {
        $query = "INSERT INTO oxa_br_ligne_lots (login, NO_BR, NO_LIG_BR, CODE_ART, NO_LOT, QTE_LOT, PRIX_LOT, DTE_PEREM)
        SELECT :login, sbl.NO_BR, sbl.NO_LIG_BR, sbl.CODE_ART, sbl.NO_LOT, sbl.QTE_LOT, sbl.PRIX_LOT, sbl.DTE_PEREM
        FROM SBR_LOTS sbl
        WHERE sbl.NO_BR = :brNumber";

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

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

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

        return $insert->execute();
    }

    /**
     * getBatchLines
     *
     * @param  string $brLine
     * @return array<mixed>
     */
    public function getBatchLines(string $brLine = ''): array
    {
        $query = "SELECT DISTINCT obll.NO_LIG_BR, obll.CODE_ART, obll.NO_LOT, obll.QTE_LOT, obll.DTE_PEREM, cal.UNITE, u.NB_DECIM, ISNULL(sbl.NO_LOT, 'FALSE') AS DTE_PEREM_NON_MODIF
        FROM oxa_br_ligne_lots obll WITH(NOLOCK)
            LEFT JOIN oxa_br_entete obe WITH(NOLOCK) ON obe.login = obll.login
            LEFT JOIN oxa_br_ligne obl WITH(NOLOCK) ON obl.login = obll.login AND obl.is_reception = 0x54
            LEFT JOIN CA_LIGNE cal WITH(NOLOCK) ON cal.NO_CDE = obe.no_cde AND cal.NO_PO = obll.NO_LIG_BR
            LEFT JOIN UNITES u with(nolock) ON u.CODE_UNI = cal.UNITE
            LEFT JOIN SLOTGLOB sbl with(nolock) ON sbl.NO_LOT = obll.NO_LOT AND sbl.CODE_ART = obll.CODE_ART
        WHERE obll.login = :login";

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

        if ($brLine !== '') {
            $query .= " AND obll.NO_LIG_BR = :line ";
            $params[':line'] = [
                'value' => $brLine,
                'option' => 1
            ];
        }

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

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

        $data->execute();
        $result = $data->fetchAll(PDO::FETCH_ASSOC);
        
        if ($result) {
            return $result;
        }
        return [];
    }

    /**
     * getOxaBrLigneLot
     *
     * @param  string $line
     * @param  string $lot
     * @return array<mixed>
     */
    public function getOxaBrLigneLot(string $line, string $lot): array
    {
        $query = "SELECT obll.NO_LIG_BR, obll.CODE_ART, obll.NO_LOT, obll.QTE_LOT, obll.DTE_PEREM, ISNULL(sbl.NO_LOT, 'FALSE') AS DTE_PEREM_NON_MODIF
        FROM oxa_br_ligne_lots obll WITH(NOLOCK)
            LEFT JOIN SLOTGLOB sbl WITH (NOLOCK) ON sbl.NO_LOT = obll.NO_LOT AND sbl.CODE_ART = obll.CODE_ART
        WHERE obll.login = :login
            AND obll.NO_LIG_BR = :NO_LIG_BR
            AND obll.NO_LOT = :NO_LOT";

        $params = [
            ':login'   => [
                'value'     => $_SESSION['Session_Login'],
                'option'    => PDO::PARAM_STR
            ],
            ':NO_LIG_BR'   => [
                'value'     => $line,
                'option'    => PDO::PARAM_STR
            ],
            ':NO_LOT'   => [
                'value'     => $lot,
                '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);
    }

    /**
     * updateOxaBrLot
     *
     * @param  string $line
     * @param  string $lot
     * @param  string $field
     * @param  string $value
     * @return bool
     */
    public function updateOxaBrLot(string $line, string $lot, string $field, string $value)
    {
        $option = 1;
        if ($field == 'DTE_PEREM') {
            $option = 2;
            Date::dateToSql($value);
        }

        $query = "UPDATE oxa_br_ligne_lots
        SET {$field} = :value
        WHERE login = :login
            AND NO_LIG_BR = :NO_LIG_BR
            AND NO_LOT = :NO_LOT";

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

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

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

        return $update->execute();
    }

    /**
     * listeLots
     *
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function listeLots(array $post): array
    {
        $query = "SELECT CODE_ART, NO_LOT, DTE_CREAT, QTE_MINI, QTE_REST, QTE_INDIS, PRIX, DTE_PEREM
        FROM SLOTGLOB WITH(NOLOCK)
        WHERE (DTE_PEREM > :today OR DTE_PEREM = '00000000')
            AND QTE_REST > 0 ";

        $params = [
            ':today' => date('Ymd'),
        ];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $query .= $whereArray[0] . $whereArray['tri'];

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

        $this->paginator->injecterSqlCount($query, $post, $params, $this->pdo);

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

    /**
     * getInfoGlobalBatch
     *
     * @param  string $batch
     * @param  string $line
     * @return array<mixed>
     */
    public function getInfoGlobalBatch(string $batch, string $line): array
    {
        $query = "SELECT distinct slgb.CODE_ART, slgb.NO_LOT, slgb.DTE_CREAT, slgb.QTE_MINI, slgb.QTE_REST, slgb.QTE_INDIS, slgb.PRIX, slgb.DTE_PEREM
        FROM SLOTGLOB slgb WITH (NOLOCK)
        LEFT JOIN oxa_br_ligne_lots obll with(nolock) ON obll.CODE_ART = slgb.CODE_ART
        WHERE slgb.NO_LOT = :batch
            AND obll.NO_LIG_BR = :line";

        $params = [
            ':batch'    => [
                'value'   => $batch,
                'option'  => 2
            ],
            ':line' => [
                'value'   => $line,
                'option'  => 1
            ]
        ];

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

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


        $data->execute();

        $result = $data->fetch(PDO::FETCH_ASSOC);

        if ($result) {
            return $result;
        }

        return [];
    }

    public function addBatch(string $line): bool
    {
        $query = "INSERT INTO oxa_br_ligne_lots (login, NO_LIG_BR, CODE_ART, NO_LOT)
        SELECT obl.login, obl.no_ligne_cde, obl.code_art, 0
        FROM oxa_br_ligne obl
        WHERE login = :login
            AND obl.no_ligne_cde = :no_ligne_cde";

        $params = [
            ':login'    => [
                'value'   => $_SESSION['Session_Login'],
                'option'  => 2
            ],
            ':no_ligne_cde' => [
                'value'   => $line,
                'option'  => 1
            ]
        ];

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

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

        return $insert->execute();
    }

    public function removeBatch(string $line, string $batch): bool
    {
        $query = "DELETE FROM oxa_br_ligne_lots 
        WHERE login = :login
            AND NO_LIG_BR = :NO_LIG_BR
            AND NO_LOT = :NO_LOT";

        $params = [
            ':login'    => [
                'value'   => $_SESSION['Session_Login'],
                'option'  => 2
            ],
            ':NO_LIG_BR' => [
                'value'   => $line,
                'option'  => 1
            ],
            ':NO_LOT' => [
                'value'   => $batch,
                'option'  => 2
            ]
        ];

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

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

        return $delete->execute();
    }
    
    /**
     * getRestrictedBatch
     *
     * @param  string $batch
     * @return array<mixed>
     */
    public function getRestrictedBatch(string $batch): array
    {
        $query = "SELECT CODE_ART, NO_LOT, DTE_CREAT, QTE_MINI, QTE_REST, QTE_INDIS, PRIX, DTE_PEREM
        FROM SLOTGLOB WITH(NOLOCK)
        WHERE (DTE_PEREM <= :today AND DTE_PEREM <> '00000000')
            AND QTE_REST <= 0 
            AND NO_LOT = :batch ";

        $params = [
            ':today' => [
                'value' => date('Ymd'),
                'option'    => 2
            ],
            ':batch' => [
                'value' => $batch,
                'option' => 2
            ],
        ];

        $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);
    }

    /**
     * getStillToBeDistributed
     * retourne le reste à livrer d'une ligne de BR à répartir en lots
     *
     * @param  string $line
     * @param  string $batch
     * @return string
     */
    public function getStillToBeDistributed(string $line, string $batch): string
    {
        $query = "
        DECLARE
        @v_login VARCHAR(30)    = ' ',
        @v_line INT             = 0,
        @v_batch VARCHAR(12)    = 0

        SET @v_login    = :login
        SET @v_line     = :line
        SET @v_batch    = :batch
            
        SELECT qte_a - (
            SELECT ISNULL(SUM(QTE_LOT), 0)
            FROM oxa_br_ligne_lots obll 
            WHERE login = @v_login AND NO_LIG_BR = @v_line AND NO_LOT <> @v_batch) AS reste
        FROM oxa_br_ligne obl
        WHERE login = @v_login
            AND no_ligne_br = @v_line ";

        $params = [
            ':login' => [
                'value'   => $_SESSION['Session_Login'],
                'option'  => 2
            ],
            ':line' => [
                'value'   => $line,
                'option'  => 1
            ],
            ':batch' => [
                'value'   => $batch,
                'option'  => 2
            ],
        ];

        $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)['reste'];
    }

    public function saveBatch(int $brNumber) :void
    {
        $query = "INSERT INTO SBR_LOTS (NO_BR, NO_LIG_BR, CODE_ART, NO_LOT, QTE_LOT, PRIX_LOT, DTE_PEREM)
        SELECT brl.NUM_BR, brl.NO_PO_BR, obll.CODE_ART, obll.NO_LOT, obll.QTE_LOT, obll.PRIX_LOT, obll.DTE_PEREM
		FROM oxa_br_ligne_lots obll
        JOIN BR_LIGNE brl 
            ON brl.NUM_BR = obll.NO_BR 
            AND brl.NO_PO_CDE = obll.NO_LIG_BR
		WHERE obll.login = :login AND obll.NO_BR = :NUM_BR";

        $this->execute($query, [
            ':login' => $_SESSION['Session_Login'],
            ':NUM_BR' => $brNumber,
        ]);
    }
    
    /**
     * getBatchLinesForReturn
     *
     * @param  string $brNumber
     * @return array<mixed>
     */
    public function getBatchLinesForReturn(string $brNumber): array
    {
        $query = "SELECT sbl.NO_BR, sbl.NO_LIG_BR, sbl.CODE_ART, sbl.NO_LOT, sbl.QTE_LOT, sbl.PRIX_LOT, sbl.DTE_PEREM, ISNULL(sl.QTE_INDIS, 0) AS QTE_INDIS, ISNULL(sl.QTE_LOC, 0) AS QTE_LOC, ISNULL(sl.QTE_TRF, 0) AS QTE_TRF, sa.CONVER, ISNULL(conva.VAL_1_2, 1) AS CONV_ART_1_2, ISNULL(convs.VAL_1_2, 1) AS CONVERS_1_2, u.NB_DECIM, ISNULL(obll.NO_LOT, 'FALSE') AS SELECTED, ISNULL(obll.QTE_LOT, 0) AS QTE_LOT_RETOUR
		FROM SBR_LOTS sbl with(nolock)
			LEFT JOIN SLOTLOC sl with(nolock) ON sl.NO_LOT = sbl.NO_LOT
			LEFT JOIN BR_LIGNE brl with(nolock) ON brl.NUM_BR = sbl.NO_BR AND brl.NO_PO_BR = sbl.NO_LIG_BR AND sbl.CODE_ART = brl.CODE_ART
			LEFT JOIN CA_LIGNE cal with(nolock) ON cal.NO_CDE = brl.NUM_CDE AND cal.NO_PO = brl.NO_PO_CDE AND cal.CODE_ART = brl.CODE_ART
			LEFT JOIN SARTICLE sa with(nolock) ON sa.CODE_ART = sbl.CODE_ART
			LEFT JOIN CONV_ART conva with(nolock) ON conva.CODE_ART = sbl.CODE_ART
			LEFT JOIN ARTICLES a with(nolock) ON a.CODE_ART = sbl.CODE_ART
			LEFT JOIN CONVERS convs with(nolock) ON convs.CODE_UNI1 = cal.UNITE AND convs.CODE_UNI2 = a.UNIT
			LEFT JOIN UNITES u with(nolock) ON u.CODE_UNI = cal.UNITE
            LEFT JOIN oxa_br_ligne_lots obll with(nolock) ON obll.login = :login AND obll.NO_LOT = sbl.NO_LOT AND obll.NO_LIG_BR = sbl.NO_LIG_BR
		where sbl.NO_BR = :brNumber ";

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

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

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

        $data->execute();
        $result = $data->fetchAll(PDO::FETCH_ASSOC);
        
        if ($result) {
            return $result;
        }
        return [];
    }

    public function insertBatchReturn(string $brNumber, string $line, string $batch): bool
    {
        $query = "INSERT INTO oxa_br_ligne_lots (login, NO_LIG_BR, CODE_ART, NO_LOT, QTE_LOT, PRIX_LOT, DTE_PEREM)
        SELECT :login, sbl.NO_LIG_BR, sbl.CODE_ART, sbl.NO_LOT, sbl.QTE_LOT, sbl.PRIX_LOT, sbl.DTE_PEREM
		FROM SBR_LOTS sbl
		WHERE sbl.NO_BR = :brNumber
            AND sbl.NO_LIG_BR = :line
            AND sbl.NO_LOT = :batch";

        $params = [
            ':login'    => [
                'value'   => $_SESSION['Session_Login'],
                'option'  => 2
            ],
            ':brNumber' => [
                'value'   => $brNumber,
                'option'  => 1
            ],
            ':line' => [
                'value'   => $line,
                'option'  => 1
            ],
            ':batch' => [
                'value'   => $batch,
                'option'  => 2
            ]
        ];

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

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

        return $insert->execute();
    }

    public function deleteBatchReturn(string $line = '', string $batch = ''): bool
    {
        $query = "DELETE FROM oxa_br_ligne_lots 
		WHERE login = :login ";

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


        if ($line != '') {
            $query .= " AND NO_LIG_BR = :line ";
            $params[':line'] = [
                'value'   => $line,
                'option'  => 1
            ];
        }
        
        if ($batch != '') {
            $query .= " AND NO_LOT = :batch ";
            $params[':batch'] = [
                'value'   => $batch,
                'option'  => 2
            ];
        }

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

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

        return $delete->execute();
    }

    public function updateBatchReturn(string $line, string $batch, string $field, string $value): bool
    {
        $query = "UPDATE oxa_br_ligne_lots
        SET {$field} = :value
        WHERE login =:login
            AND NO_LIG_BR = :line
            AND NO_LOT = :batch ";

        $params = [
            ':value'    => [
                'value'   => $value,
                'option'  => 2
            ],
            ':login'    => [
                'value'   => $_SESSION['Session_Login'],
                'option'  => 2
            ],
            ':line'    => [
                'value'   => $line,
                'option'  => 1
            ],
            ':batch' => [
                'value'   => $batch,
                'option'  => 2
            ]
        ];

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

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

        return $update->execute();
    }
}
