<?php
namespace Teknisa\PebLibs\Factories;

use Zeedhi\Framework\DB\StoredProcedure\Param;
use Zeedhi\Framework\DB\StoredProcedure\StoredProcedure;

class Est58000Retirada {

    protected $entityManager;
    protected $connection;

  public function __construct($entityManager) {
      $this->entityManager = $entityManager;
      $this->connection = $entityManager->getConnection();
    }

    public function callProcedure($cdfilial, $cdproduto, $dtposicao) {
        $procedure = new StoredProcedure($this->entityManager->getConnection(), 'POSICAO_ESTOQUE');
        $procedure->addParam(new Param('P_FILIAL', Param::PARAM_INPUT, $cdfilial, Param::PARAM_TYPE_STR));
        $procedure->addParam(new Param('P_PRODUTO', Param::PARAM_INPUT, $cdproduto, Param::PARAM_TYPE_STR));
        $procedure->addParam(new Param('P_DTPOSICAO', Param::PARAM_INPUT, $dtposicao, Param::PARAM_TYPE_STR));
        $procedure->addParam(new Param('P_QTDEESTO', Param::PARAM_OUTPUT, null, Param::PARAM_TYPE_STR, 50));
        $procedure->addParam(new Param('P_VALRESTO', Param::PARAM_OUTPUT, null, Param::PARAM_TYPE_STR, 50));
        $procedure->addParam(new Param('P_VALRBRUT', Param::PARAM_OUTPUT, null, Param::PARAM_TYPE_STR, 50));
        $output = $procedure->execute();

        $vrBruto = !empty($output['P_VALRBRUT']) ? $output['P_VALRBRUT'] : null;
        return $vrBruto;
    }

    public function deleteItlanctoest($params) {
        $sql = "DELETE FROM ITLANCTOEST
                      WHERE CDFILIAL   = :CDFILIAL
                        AND NRLANCESTQ = :NRLANCTOEST
                        AND NRSEQUITEM = :NRSEQUITEM";

        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }
    
    public function deleteAllItlanctoest($params) {
        $sql = "DELETE FROM ITLANCTOEST
                      WHERE CDFILIAL   = :CDFILIAL
                        AND NRLANCESTQ = :NRLANCTOEST";

        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function deleteItrequesto($params) {
        $sql = "DELETE FROM ITREQUESTO
                      WHERE CDFILIAL      = :CDFILIAL
                        AND CDFILLANCREQC = :CDFILLANCREQC
                        AND NRLANCREQC    = :NRLANCREQC
                        AND CDPRODUTO     = :CDPRODUTO
                        AND NRLOTEESTQ    = :NRLOTEESTQ
                        AND NRSUBLOTE     = :NRSUBLOTE";

        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function deleteLanctoestoq($params) {
        $sql = "DELETE FROM LANCTOESTOQ
                      WHERE CDFILIAL   = :CDFILIAL
                        AND NRLANCESTQ = :NRLANCTOEST";

        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function deleteRequesto($params) {
        $sql = "DELETE FROM REQUESTO
                      WHERE CDFILIAL      = :CDFILIAL
                        AND CDFILLANCREQC = :CDFILLANCREQC
                        AND NRLANCREQC    = :NRLANCREQC";

        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function deleteRetiradaesto($params) {
        $sql = "DELETE FROM RETIRADAESTO
                      WHERE CDFILIALLAN = :CDFILIAL AND
                            NRLANCESTQ  = :NRLANCTOEST";

        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function freeItemRequisicaoComplementar($params) {
        $sql = "UPDATE ITREQUESTO
                   SET NRSEQITREQC   = NULL,
                       CDFILLANCREQC = NULL,
                       NRLANCREQC    = NULL
                 WHERE CDFILIAL   = :CDFILIAL
                   AND NRREQUESTO = :NRREQUESTO
                   AND CDPRODUTO  = :CDPRODUTO
                   AND NRLOTEESTQ = :NRLOTEESTQ
                   AND NRSUBLOTE  = :NRSUBLOTE";

        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }
    
    public function limpaCampoConsumo($params) {
        
        $sql = "UPDATE CONSUMOREAL
                   SET NRLANCESTQ  = NULL
                 WHERE CDFILIAL   = :CDFILIMOVI
                   AND NRLANCESTQ = :NRLANCESTQ";

        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function freeRequisicaoComplementar($params) {
        $sql = "UPDATE ITREQUESTO
                   SET NRSEQITREQC   = NULL,
                       CDFILLANCREQC = NULL,
                       NRLANCREQC    = NULL
                 WHERE CDFILIAL   = :CDFILIAL
                   AND NRREQUESTO = :NRREQUESTO";

        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function getCdtipocusto($params) {
        $sql = "SELECT NVL(CDTIPCUSTEST, 3) AS CDTIPCUSTEST
                FROM PARAMFILIAL
                WHERE CDFILIAL = :CDFILIAL
                  AND NRORG = :NRORG";

        $query = $this->connection->executeQuery($sql, $params);

        return $query->fetchAssociative();
    }

    public function getItensByRequisicao($params) {
        $sql = "SELECT IT.NRSEQUITEM, IT.CDPRODUTO, PR.CDPRODESTO, IT.CDFILIAL AS CDFILIMOVI,
                  IT.NRLOTEESTQ, IT.NRSUBLOTE, IT.QTREQUESTO  AS QTTOTLANCTO,
                  RE.CDALMOXSAID AS CDALMOXARIFE, RE.CDLCESTSAID AS CDLOCALESTOQ,
                  IT.IDSTITREQU, IT.QTDEVOREQU  AS QTDEVOLESTOQ, ' ' AS VRUNILANCTO
                FROM ITREQUESTO IT, PRODUTO PR, REQUESTO RE
                WHERE IT.CDFILIAL = :CDFILIAL
                  AND IT.NRORG      = :NRORG
                  AND IT.NRREQUESTO = :NRREQUESTO
                  AND IT.CDPRODUTO  = PR.CDPRODUTO
                  AND IT.NRORG      = PR.NRORG
                  AND IT.CDFILIAL   = RE.CDFILIAL
                  AND IT.NRORG      = RE.NRORG
                  AND IT.IDSTITREQU = NVL(:IDSTITREQU, IT.IDSTITREQU)
                  AND IT.NRREQUESTO = RE.NRREQUESTO
                ORDER BY IT.CDPRODUTO";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAllAssociative();
    }

    public function getItensFromRetirada($params) {
        $sql = "SELECT QTTOTLANCTO, QTDEVOLESTOQ, QTLANCTOEST, NRSEQUITEM, NRLANCESTQ,
                    NRORG, CDFILIAL
                FROM ITLANCTOEST
                WHERE NRLANCESTQ = :NRLANCESTQ
                  AND CDFILIAL     = :CDFILIAL
                  AND NRORG        = :NRORG";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAllAssociative();
    }

    public function getLancamento($params) {
        $sql = "SELECT L.NRORG, L.CDFILIAL, F.NMFILIAL, L.NRLANCESTQ, L.IDTPLANCTO,
                  L.DTLANCESTQ, L.DSLANCESTQ, L.CDFILIMOVI, L.CDOPERADOR, R.NRRETEST,
                  R.DTRETEST, R.DSRETEST, R.CDFILIALLAN, R.CDSETOR, S.NMSETOR,
                  R.CDCENTCUST, R.NRORDPROD, R.NRSUBORDPROD, R.CDETAPAPROD,
                  R.NRREQUESTO, Q.DSREQUESTO, L.CDALMOXARIFE, L.CDLOCALESTOQ,
                  NRLANCESTRET
                FROM LANCTOESTOQ L, RETIRADAESTO R, REQUESTO Q, SETOR S,
                  AJURELACRET A, FILIAL F
                WHERE(L.CDFILIMOVI = :CDFILIAL)
                  AND (L.DTLANCESTQ  = :DATA)
                  AND (L.IDTPLANCTO  = '3')
                  AND (L.CDFILIAL    = R.CDFILIALLAN)
                  AND L.CDFILIAL     = F.CDFILIAL
                  AND L.NRORG        = F.NRORG
                  AND L.NRLANCESTQ   = :NRLANCESTQ
                  AND (L.NRLANCESTQ  = R.NRLANCESTQ)
                  AND L.NRORG = :NRORG
                  AND (L.NRORG       = R.NRORG)
                  AND (R.CDFILIAL    = Q.CDFILIAL(+))
                  AND (R.NRREQUESTO  = Q.NRREQUESTO(+))
                  AND (R.NRORG       = Q.NRORG(+))
                  AND (R.CDSETOR     = S.CDSETOR(+) )
                  AND (R.CDCENTCUST  = S.CDCENTCUST(+))
                  AND (R.NRORG       = S.NRORG(+))
                  AND (L.CDFILIAL    = A.CDFILIALRET(+))
                  AND (L.NRLANCESTQ  = A.NRLANCESTRET(+))
                  AND (L.NRORG       = A.NRORG(+))
                  AND EXISTS
                    (SELECT I.CDFILIAL, I.NRLANCESTQ
                    FROM ITLANCTOEST I
                    WHERE I.CDFILIAL = L.CDFILIAL
                       AND I.NRLANCESTQ = L.NRLANCESTQ
                       AND I.NRORG      = L.NRORG
                    )
                UNION
                SELECT L.NRORG, L.CDFILIAL, F.NMFILIAL, L.NRLANCESTQ, L.IDTPLANCTO,
                  L.DTLANCESTQ, L.DSLANCESTQ, L.CDFILIMOVI, L.CDOPERADOR,
                  R.NRRETESTVND, R.DTRETESTVND, L.DSLANCESTQ, R.CDFILIAL,
                  R.CDSETOR, S.NMSETOR, R.CDCENTCUST, '' AS NRORDPROD, '' AS NRSUBORDPROD,
                  '' AS CDETAPAPROD, '' AS NRREQUESTO, '' AS DSREQUESTO,
                  L.CDALMOXARIFE, L.CDLOCALESTOQ, '' AS NRLANCESTRET
                FROM LANCTOESTOQ L, RETIRADAESTOVND R, SETOR S, FILIAL F
                WHERE (L.CDFILIMOVI = :CDFILIAL )
                  AND L.CDFILIAL      = F.CDFILIAL
                  AND L.NRORG = :NRORG
                  AND L.NRORG         = F.NRORG
                  AND (L.IDTPLANCTO   = '3' )
                  AND (L.CDFILIAL     = R.CDFILIAL )
                  AND L.NRLANCESTQ    = :NRLANCESTQ
                  AND (L.NRLANCESTQ   = R.NRLANCESTQ )
                  AND (L.NRORG        = R.NRORG )
                  AND (R.CDSETOR      = S.CDSETOR (+))
                  AND (R.CDCENTCUST   = S.CDCENTCUST(+))
                  AND (R.NRORG        = S.NRORG(+))
                  AND (L.DTLANCESTQ   = :DATA)
                  AND (EXISTS
                    (SELECT I.CDFILIAL, I.NRLANCESTQ
                    FROM ITLANCTOEST I
                    WHERE (I.CDFILIAL = L.CDFILIAL )
                      AND (I.NRLANCESTQ = L.NRLANCESTQ)
                      AND (I.NRORG      = L.NRORG)))
                ORDER BY DTLANCESTQ DESC, NRLANCESTQ ASC";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAssociative();
    }

    public function getParamProduto($params) {
        $sql = "SELECT CDPRODESTO, VRFATOCONV
                FROM PRODUTO
                WHERE CDPRODUTO = :CDPRODUTO
                  AND NRORG = :NRORG";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAssociative();
    }

    public function getProdesto($params) {
        $sql = "SELECT NVL(CDPRODESTO, CDPRODUTO) AS CDPRODESTO
                  FROM PRODUTO
                 WHERE CDPRODUTO = :CDPRODUTO";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAssociative();
    }

    public function getDtposiestq($params) {
        $sql = "SELECT MAX(DTPOSIESTQ) DTPOSIESTQ
                  FROM POSESTDIA
                 WHERE (CDFILIAL     = :CDFILIAL)
                   AND (CDALMOXARIFE = :CDALMOXARIFE)
                   AND (CDLOCALESTOQ = :CDLOCALESTOQ)
                   AND (NRLOTEESTQ   = :NRLOTEESTQ)
                   AND (NRSUBLOTE    = :NRSUBLOTE)
                   AND (CDPRODUTO    = :CDPRODESTO)
                   AND (DTPOSIESTQ   <= SYSDATE + 1)";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAssociative();
    }

    public function getPosestdia($params) {
        $sql = "SELECT NVL(CUSTO_PRODUTO_LCEST(:CDFILIAL, D.CDPRODUTO, :INTPCUSTO, :DTPOSIESTQ, SYSDATE, D.CDALMOXARIFE, D.CDLOCALESTOQ, D.NRLOTEESTQ, D.NRSUBLOTE),0) * P.VRFATOCONV AS VRCUSTOPROD,
                      (D.QTESTOQDIA/P.VRFATOCONV) AS QTESTOQDIA,
                       P.SGUNIDADE,
                       DTVALILOTE,
                       P.NMPRODUTO,
                      'S' AS IDCNTRESTOQ,
                       D.CDALMOXARIFE,
                       D.CDLOCALESTOQ,
                       D.NRLOTEESTQ,
                       D.NRSUBLOTE,
                       P.VRFATOCONV,
                      (QTESTOQANT/P.VRFATOCONV) AS QTESTOQANT,
                       D.CDPRODUTO,
                       CDPRODESTO,
                      :CDFILIAL AS CDFILIAL
                  FROM PARAMFILIAL PF, PRODUTO P, POSESTDIA D, LOTE L
                WHERE (D.CDFILIAL     = :CDFILIAL)
                  AND (D.CDALMOXARIFE = :CDALMOXARIFE)
                  AND (D.CDLOCALESTOQ = :CDLOCALESTOQ)
                  AND (P.CDPRODUTO    = :CDPRODESTO)
                  AND (D.NRLOTEESTQ   = :NRLOTEESTQ)
                  AND (D.NRSUBLOTE    = :NRSUBLOTE)
                  AND (DTPOSIESTQ     = :DTPOSIESTQ)
                  AND (D.CDPRODUTO    = P.CDPRODESTO)
                  AND ((D.QTESTOQDIA   > 0) 
                    OR (PF.IDPERMESTNEG = 'S'))
                  AND (PF.CDFILIAL    = L.CDFILIAL)
                  AND (D.CDFILIAL     = L.CDFILIAL)
                  AND (D.NRLOTEESTQ   = L.NRLOTEESTQ)
                  AND (D.NRSUBLOTE    = L.NRSUBLOTE)
                  AND (D.CDPRODUTO    = L.CDPRODUTO)";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAssociative();
    }

    public function getRequisicaoComplementar($params) {
        $sql = "SELECT CDFILLANCREQC,
                       NRLANCREQC,
                       CDFILIAL
                  FROM REQUESTO
                 WHERE CDFILLANCREQC = :CDFILLANCREQC
                   AND NRLANCREQC    = :NRLANCREQC";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAssociative();
    }

    public function getRequisicaoComplementarAberta($params) {
        $sql = "SELECT NRREQUESTO
                  FROM REQUESTO
                 WHERE CDFILLANCREQC = :CDFILLANCREQC
                   AND NRLANCREQC    = :NRLANCREQC
                   AND IDSTATUSREQ  <> 'E'";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAssociative();
    }

    public function getRequisicaoComplementarParameters($params) {
        $sql = "SELECT CDFILIAL,
                       NRREQUESTO,
                       DTREQUESTO,
                       DSREQUESTO,
                       IDREQUESTO,
                       DTPREVREQU,
                       CDALMOXSAID,
                       CDLCESTSAID,
                       CDALMOXENTR,
                       CDLCESTENTR,
                       CDCENTCUST,
                       CDSETOR
                  FROM REQUESTO
                 WHERE CDFILIAL   = :CDFILIMOVI
                   AND NRREQUESTO = :NRREQUESTO";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAssociative();
    }

    public function getUsedItrequesto($params) {
        $sql = "SELECT *
                  FROM ITREQUESTO
                 WHERE CDFILIAL      = :CDFILIAL
                   AND CDFILLANCREQC = :CDFILLANCREQC
                   AND NRLANCREQC    = :NRLANCREQC
                   AND CDPRODUTO = NVL(:CDPRODUTO, CDPRODUTO)
                   AND NRLOTEESTQ = NVL(:NRLOTEESTQ, NRLOTEESTQ)
                   AND NRSUBLOTE = NVL(:NRSUBLOTE, NRSUBLOTE)
                   AND NRLANCESTQ IS NOT NULL";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAllAssociative();
    }

    public function insertItlanctoest($params) {
     
    
      $sql = "INSERT INTO ITLANCTOEST
                    (CDFILIAL, NRLANCESTQ, NRSEQUITEM, CDPRODUTO, DSLANCESTIT, CDALMOXARIFE,
                    CDLOCALESTOQ, NRLOTEESTQ, NRSUBLOTE, CDFILIMOVI, CDPRODMOVI, IDORDEMCLC,
                    DTLANCMOVI, IDEVOLESTOQ, CDTIPORETI, CDCENTCUST, QTTOTLANCTO, IDTIPOMOVI,
                    QTLANCTOEST, VRUNILANCTO, VRTOTLANCTO, QTDEVOLESTOQ, VRLANCTOEST,
                    VRLANCTOBRUT, NRREQUESTO, NRORG, IDATIVO, DTULTATU, NRORGULTATU,
                    CDOPERULTATU, DTINCLUSAO, NRORGINCLUSAO, CDOPERINCLUSAO)
                VALUES
                    (:CDFILIAL, :NRLANCESTQ, :NRSEQUITEM, :CDPRODUTO, :DSLANCESTIT, :CDALMOXARIFE,
                    :CDLOCALESTOQ, :NRLOTEESTQ, :NRSUBLOTE, :CDFILIMOVI, :CDPRODMOVI, :IDORDEMCLC,
                    :DTLANCMOVI, :IDEVOLESTOQ, :CDTIPORETI, :CDCENTCUST, :QTTOTLANCTO, :IDTIPOMOVI,
                    :QTLANCTOEST, :VRUNILANCTO, :VRTOTLANCTO, :QTDEVOLESTOQ, :VRLANCTOEST,
                    :VRLANCTOBRUT, :NRREQUESTO, :NRORG, :IDATIVO, :DTULTATU, :NRORGULTATU,
                    :CDOPERULTATU, :DTINCLUSAO, :NRORGINCLUSAO, :CDOPERINCLUSAO)";

        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function insertLanctoestoq($params) {
        $sql = "INSERT INTO LANCTOESTOQ
                   (CDFILIAL, CDOPERADOR, IDTPLANCTO, DTLANCESTQ, DSLANCESTQ, CDFILIMOVI, NRLANCESTQ,
                    NRORG, IDATIVO, DTULTATU, NRORGULTATU, CDOPERULTATU, DTINCLUSAO, NRORGINCLUSAO,
                    CDOPERINCLUSAO)
                 VALUES
                   (:CDFILIAL, :CDOPERADOR, :IDTPLANCTO, :DTLANCESTQ, :DSLANCESTQ, :CDFILIMOVI, :NRLANCESTQ,
                   :NRORG, :IDATIVO, :DTULTATU, :NRORGULTATU, :CDOPERULTATU, :DTINCLUSAO, :NRORGINCLUSAO,
                   :CDOPERINCLUSAO)";
        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function insertRetiradaesto($params) {
        $sql = "INSERT INTO RETIRADAESTO
                    (CDFILIAL, NRRETEST, DTRETEST, DSRETEST, CDFILIALLAN, NRLANCESTQ, CDSETOR, CDCENTCUST, NRORDPROD,
                     NRREQUESTO, NRORG, IDATIVO, DTULTATU, NRORGULTATU, CDOPERULTATU, DTINCLUSAO, NRORGINCLUSAO,
                     CDOPERINCLUSAO)
                VALUES
                    (:CDFILIAL, :NRRETEST, :DTRETEST, :DSRETEST, :CDFILIALLAN, :NRLANCESTQ, :CDSETOR, :CDCENTCUST,
                     :NRORDPROD, :NRREQUESTO, :NRORG, :IDATIVO, :DTULTATU, :NRORGULTATU, :CDOPERULTATU, :DTINCLUSAO,
                     :NRORGINCLUSAO, :CDOPERINCLUSAO)";
        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function insertRetiradaestoComplementar($params) {
        $sql = "INSERT INTO REQUESTO (CDFILIAL, NRREQUESTO, DTREQUESTO, DTPREVREQU, DSREQUESTO,
                            CDSETOR, CDCENTCUST, CDOPERREQU, CDALMOXSAID, CDLCESTSAID,
                            CDALMOXENTR, CDLCESTENTR, CDFILLANCREQC, NRLANCREQC)
                   VALUES (:CDFILIAL, :NRREQUESTO, :DTREQUESTO, :DTPREVREQU, :DSREQUESTO,
                           :CDSETOR, :CDCENTCUST, :CDOPERREQU, :CDALMOXSAID, :CDLCESTSAID,
                           :CDALMOXENTR, :CDLCESTENTR, :CDFILLANCREQC, :NRLANCREQC)";
        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function insertItrequestoComplementar($params) {
        $sql = "INSERT INTO ITREQUESTO (CDFILIAL, NRREQUESTO, CDPRODUTO, QTREQUESTO, QTDEVOREQU,
                                        IDSTITREQU, CDFILILANC, NRLANCESTQ, NRSEQUITEM, NRLOTEESTQ,
                                        NRSUBLOTE, CDFILLANCREQC, NRLANCREQC , NRSEQITREQC, NRSEQITEM)
                               VALUES (:CDFILIAL, :NRREQUESTO, :CDPRODUTO, :QTREQUESTO, :QTDEVOREQU,
                                       :IDSTITREQU, :CDFILILANC, :NRLANCESTQ, :NRSEQUITEM, :NRLOTEESTQ,
                                       :NRSUBLOTE, :CDFILLANCREQC, :NRLANCREQC, :NRSEQITREQC, :NRSEQITEM)";
        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function updateItlanctoest($params) {
        $sql = "UPDATE ITLANCTOEST
                SET QTTOTLANCTO  = :QTTOTLANCTO,
                  QTDEVOLESTOQ   = :QTDEVOLESTOQ,
                  QTLANCTOEST    = :QTLANCTOEST,
                  CDCENTCUST     = :CDCENTCUST,
                  CDTIPORETI     = :CDTIPORETI
                WHERE NRLANCESTQ = :NRLANCESTQ
                  AND NRORG        = :NRORG
                  AND NRSEQUITEM   = :NRSEQUITEM
                  AND CDFILIAL   = :CDFILIAL";
        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function updateItrequestoStatus($params) {
        $sql = "UPDATE ITREQUESTO
                   SET IDSTITREQU = :IDSTITREQU,
                       NRLANCESTQ = :NRLANCESTQ,
                       CDFILILANC = :CDFILILANC,
                       NRSEQUITEM = :NRSEQUITEM
                 WHERE CDFILIAL   = :CDFILIAL
                   AND NRREQUESTO = :NRREQUESTO
                   AND (NRLANCESTQ = :OLD_NRLANCESTQ OR :OLD_NRLANCESTQ IS NULL)
                   AND CDPRODUTO  = NVL(:CDPRODUTO, CDPRODUTO)
                   AND NRSEQITEM  = NVL(:NRSEQITEM, NRSEQITEM)
                   AND NRORG      = :NRORG";
        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function updateLanctoesto($params) {
        $sql = "UPDATE LANCTOESTOQ
                SET DSLANCESTQ   = :DSLANCESTQ
                WHERE CDFILIAL = :CDFILIAL
                  AND NRORG        = :NRORG
                  AND NRLANCESTQ   = :NRLANCESTQ";
        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function updateRetiradaesto($params) {
        $sql = "UPDATE RETIRADAESTO
                SET CDSETOR    = :CDSETOR,
                  CDCENTCUST   = :CDCENTCUST
                WHERE CDFILIAL = :CDFILIAL
                  AND NRORG      = :NRORG
                  AND NRRETEST   = :NRRETEST";
        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function updateRequestoStatus($params) {
        $sql = "UPDATE REQUESTO
                   SET CDFILLANCREQC = NULL,
                       NRLANCREQC    = NULL,
                       IDREQUESTO    = :IDREQUESTO,
                       IDSTATUSREQ   = :IDSTATUSREQ
                 WHERE CDFILIAL   = :CDFILIAL
                   AND NRREQUESTO = :NRREQUESTO";

        $query = $this->connection->prepare($sql);
        $query->execute($params);
    }

    public function verificaInventarioRotativo($params) {
        $sql = "SELECT IDINVENTOK, CDALMOXARIFE, CDLOCALESTOQ
                FROM INVEROTA
                WHERE (CDFILIAL = :CDFILIAL)
                  AND (DTINVEROTA =
                    (SELECT MAX(DTINVEROTA)
                    FROM INVEROTA
                    WHERE (CDFILIAL   = :CDFILIAL)
                       AND (CDALMOXARIFE = ' ')
                       AND (CDLOCALESTOQ = ' ')
                       AND (DTINVEROTA   < :DTLANCAMENTO)))
                  AND (CDALMOXARIFE = ' ')
                  AND (CDLOCALESTOQ = ' ')
                  AND (IDINVENTOK    = 'N')
                  AND (NRORG = :NRORG)
                UNION ALL
                SELECT IDINVLOTEOK, CDALMOXARIFE, CDLOCALESTOQ
                FROM INVEROTALOTE
                WHERE (CDFILIAL = :CDFILIAL)
                  AND (DTINVELOTE =
                    (SELECT MAX(DTINVELOTE)
                    FROM INVEROTALOTE
                    WHERE (CDFILIAL   = :CDFILIAL)
                      AND (CDALMOXARIFE = ' ')
                      AND (CDLOCALESTOQ = ' ')
                      AND (DTINVELOTE   < :DTLANCAMENTO)))
                  AND (CDALMOXARIFE = ' ')
                  AND (CDLOCALESTOQ = ' ')
                  AND (IDINVLOTEOK  = 'N')
                  AND (NRORG = :NRORG)";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAllAssociative();
    }
    
    public function getProdutoTabela($parameters){
        $sql = "";
        if(isset($parameters["P_CDARVPROD"])){
           $sql = "SELECT DISTINCT PF.CDFILIAL,
                        P.CDPRODUTO,
                        P.CDARVPROD,
                        P.NMPRODUTO,
                        P.SGUNIDADE,
                        P.VRFATOCONV,
                        P.CDPRODESTO AS CDPRODMOVI,
                        P.NRORG,
                        ' ' AS CDALMOXARIFE,
                        ' ' AS CDLOCALESTOQ,
                        ' ' AS NRLOTEESTQ,
                        ' ' AS NRSUBLOTE,
                        0 AS VRUNILANCTO,
                        0 AS VRTOTLANCTO,
                        0 AS VRLANCTOEST,
                        0 AS VRUNITBRUT,
                        0 AS QTTOTLANCTO
                    FROM
                        PRODUTO P JOIN
                        PRODFILI PF ON P.CDPRODUTO = PF.CDPRODUTO
                    WHERE
                        P.CDARVPROD = :P_CDARVPROD AND
                        PF.CDFILIAL = :P_CDFILIAL"; 
        }else if(isset($parameters["P_CDBARPRODUTO"])){
            $sql = "SELECT DISTINCT PF.CDFILIAL,
                            P.CDPRODUTO,
                            P.CDARVPROD,
                            P.NMPRODUTO,
                            P.SGUNIDADE,
                            P.VRFATOCONV,
                            P.CDPRODESTO AS CDPRODMOVI,
                            P.NRORG,
                            ' ' AS CDALMOXARIFE,
                            ' ' AS CDLOCALESTOQ,
                            ' ' AS NRLOTEESTQ,
                            ' ' AS NRSUBLOTE,
                            0 AS VRUNILANCTO,
                            0 AS VRTOTLANCTO,
                            0 AS VRLANCTOEST,
                            0 AS VRUNITBRUT,
                            0 AS QTTOTLANCTO
                      FROM PRODUTO P
                      JOIN PRODFILI PF 
                        ON P.CDPRODUTO = PF.CDPRODUTO
                     WHERE P.CDBARPRODUTO = :P_CDBARPRODUTO
                       AND PF.CDFILIAL = :P_CDFILIAL
                     UNION
                    SELECT  PF.CDFILIAL,
                            P.CDPRODUTO,
                            P.CDARVPROD,
                            P.NMPRODUTO,
                            P.SGUNIDADE,
                            P.VRFATOCONV,
                            P.CDPRODESTO AS CDPRODMOVI,
                            P.NRORG,
                            ' ' AS CDALMOXARIFE,
                            ' ' AS CDLOCALESTOQ,
                            ' ' AS NRLOTEESTQ,
                            ' ' AS NRSUBLOTE,
                            0 AS VRUNILANCTO,
                            0 AS VRTOTLANCTO,
                            0 AS VRLANCTOEST,
                            0 AS VRUNITBRUT,
                            0 AS QTTOTLANCTO
                      FROM PRODUTO P
                      LEFT JOIN CDBARRAPROD CDBP
                        ON P.CDPRODUTO = CDBP.CDPRODUTO
                      LEFT JOIN PRODFILI PF 
                        ON P.CDPRODUTO = PF.CDPRODUTO
                     WHERE CDBP.CDBARRAPROD = :P_CDBARPRODUTO
                       AND PF.CDFILIAL = :P_CDFILIAL"; 
            
        } else{
            $sql = "SELECT DISTINCT PF.CDFILIAL,
                        P.CDPRODUTO,
                        P.CDARVPROD,
                        P.NMPRODUTO,
                        P.SGUNIDADE,
                        P.VRFATOCONV,
                        P.CDPRODESTO AS CDPRODMOVI,
                        P.NRORG,
                        ' ' AS CDALMOXARIFE,
                        ' ' AS CDLOCALESTOQ,
                        ' ' AS NRLOTEESTQ,
                        ' ' AS NRSUBLOTE,
                        0 AS VRUNILANCTO,
                        0 AS VRTOTLANCTO,
                        0 AS VRLANCTOEST,
                        0 AS VRUNITBRUT,
                        0 AS QTTOTLANCTO
                    FROM
                        PRODUTO P JOIN
                        PRODFILI PF ON P.CDPRODUTO = PF.CDPRODUTO
                    WHERE
                        P.CDPRODUTO = :P_CDPRODUTO AND
                        PF.CDFILIAL = :P_CDFILIAL"; 
        }
        $std = $this->connection->executeQuery($sql, $parameters);
        $result = $std->fetchAllAssociative();

        if(isset($result[0])){
            return $result[0];
        } else{
            return null;
        }
    }
    
    public function insertLoteProduto($cdfilial, $cdproduto, $nrloteestq, $nrsublote, $dtlancamento, $dtvalilote, $dtfrabric){
        try{
            $params = array(
                'P_CDFILIAL'   => $cdfilial,
                'P_CDPRODUTO'  => $cdproduto,
                'P_NRLOTEESTQ' => $nrloteestq,
                'P_NRSUBLOTE'  => $nrsublote,
                'P_DTLANCAMENTO' => $dtlancamento,
                'P_DTVALILOTE' => $dtvalilote,
                'P_DTFABRPROD' => $dtfrabric
            );
            $query = "INSERT INTO LOTE
                    (CDFILIAL, NRLOTEESTQ, NRSUBLOTE, CDPRODUTO, IDSTATLOTE, DTENTRLOTE, DTVALILOTE, DTFABRPROD)
                    (SELECT :P_CDFILIAL CDFILIAL, :P_NRLOTEESTQ NRLOTEESTQ, :P_NRSUBLOTE NRSUBLOTE, :P_CDPRODUTO CDPRODUTO,
                    'L' IDSTATLOTE, :P_DTLANCAMENTO DTENTRLOTE, :P_DTVALILOTE DTVALILOTE, :P_DTFABRPROD DTFABRPROD
                     FROM DUAL
                     MINUS
                     SELECT CDFILIAL, NRLOTEESTQ, NRSUBLOTE, CDPRODUTO, 'L', :P_DTLANCAMENTO DTENTRLOTE, :P_DTVALILOTE DTVALILOTE, :P_DTFABRPROD DTFABRPROD
                     FROM LOTE
                     WHERE  :P_CDFILIAL   =  CDFILIAL     AND
                            :P_NRLOTEESTQ =  NRLOTEESTQ   AND
                            :P_NRSUBLOTE  =  NRSUBLOTE    AND
                            :P_CDPRODUTO  =  CDPRODUTO)";

            $this->entityManager->getConnection()->executeQuery($query, $params);
            
        }
         catch (\Exception $e) {
            return $e->getMessage();
        }
    }
    
    public function insertAlmoxarife($cdfilial, $cdalmoxarife, $dsalmoxarife, $nrorg){
        try{
            $params = array(
                'P_CDFILIAL'   => $cdfilial,
                'P_CDALMOXARIFE'  => $cdalmoxarife,
                'P_DSALMOXARIFE' => $dsalmoxarife,
                'P_NRORG'  => $nrorg
            );
            $query = "INSERT INTO ALMOXARIFADO
                    (CDFILIAL, CDALMOXARIFE, DSALMOXARIFE, NRORG)
                    (SELECT :P_CDFILIAL CDFILIAL, :P_CDALMOXARIFE CDALMOXARIFE, :P_DSALMOXARIFE DSALMOXARIFE, NRORG
                     FROM ALMOXARIFADO
                     MINUS
                     SELECT CDFILIAL, CDALMOXARIFE, DSALMOXARIFE, NRORG
                     FROM ALMOXARIFADO
                     WHERE  :P_CDFILIAL     =  CDFILIAL     AND
                            :P_CDALMOXARIFE  =  CDALMOXARIFE  AND
                            :P_NRORG        =  NRORG)";
                            
            $this->entityManager->getConnection()->executeQuery($query, $params);
            
        }
         catch (\Exception $e) {
            return $e->getMessage();
        }
    }
    
    public function insertLocalEstoque($cdfilial, $cdalmoxarife, $cdlocalestoq, $dslocalestoq, $estdesmemb, $nrorg){
        try{
            $params = array(
                'P_CDFILIAL'     => $cdfilial,
                'P_CDALMOXARIFE' => $cdalmoxarife,
                'P_CDLOCALESTOQ' => $cdlocalestoq,
                'P_DSLOCALESTOQ' => $dslocalestoq,
                'P_IDESTDESMEMB' => $estdesmemb,
                'P_NRORG'        => $nrorg
            );
            $query = "INSERT INTO LOCALESTOQUE
                    (CDFILIAL, CDALMOXARIFE, CDLOCALESTOQ, DSLOCALESTOQ, IDESTDESMEMB, NRORG)
                    (SELECT :P_CDFILIAL CDFILIAL, :P_CDALMOXARIFE CDALMOXARIFE, :P_CDLOCALESTOQ CDLOCALESTOQ,
                    :P_DSLOCALESTOQ DSLOCALESTOQ, :P_IDESTDESMEMB IDESTDESMEMB, NRORG
                     FROM LOCALESTOQUE
                     MINUS
                     SELECT CDFILIAL, CDALMOXARIFE, CDLOCALESTOQ, DSLOCALESTOQ, IDESTDESMEMB, NRORG
                     FROM LOCALESTOQUE
                     WHERE  :P_CDFILIAL     =  CDFILIAL     AND
                            :P_CDALMOXARIFE =  CDALMOXARIFE AND
                            :P_CDLOCALESTOQ =  CDLOCALESTOQ AND
                            :P_NRORG        =  NRORG)";
                            
            $this->entityManager->getConnection()->executeQuery($query, $params);
            
        }
         catch (\Exception $e) {
            return $e->getMessage();
        }
    }
    
    public function getLanctoExistente($params) {
        $sql = "SELECT * FROM LANCTOESTOQ
                WHERE CDFILIAL = :P_CDFILIAL
                AND NRLANCESTQ = :P_NRLANCESTQ";

        $query = $this->connection->executeQuery($sql, $params);
        return $query->fetchAllAssociative();
    }

}