<?php
require '../../conexao/conexao.php';

class LojaDetalhado
{
    private $connectionmysql;
    private $connection;

    public function __construct()
    {
        global $connectionmysql;
        global $connection;
        $this->connectionmysql = $connectionmysql;
        $this->connection = $connection;
    }

    public function getConvenioMarca($datainicio, $datafinal, $filial, $cdfun = false)
    {
        $sql = "SELECT ";

        if ($cdfun) {
            $sql .= "c.CDFUNRE, ";
        }

        $sql .= "c.CDCONRE,conv.nrdocori,SUM(i.VRTOT - i.VRDSC - i.VRDSCV + i.VRTXAV) as VRRCB FROM fc31320 conv
        INNER JOIN FC14100 i ON i.cdfil = conv.cdfil and i.nrcpm = conv.nrdocori
        INNER JOIN FC14000 c ON c.NRCPM = i.NRCPM AND c.CDFIL = i.CDFIL
        INNER JOIN FC03000 prod ON i.CDPRO = prod.CDPRO
        where prod.SETOR  IN (276, 330)";

        // Aplicar filtro de período
        if ($datainicio && $datafinal) {
            $sql .= " AND DTOPE BETWEEN '$datainicio' AND '$datafinal'";
        }

        // Aplicar filtro de filial
        if ($filial && $filial != "Todos") {
            $sql .= " AND conv.CDFIL IN ($filial)";
        }
        if ($cdfun && $cdfun != "Todos") {
            $sql .= " AND c.cdfunre IN ($cdfun)";
        }

        $sql .= " GROUP BY ";

        if ($cdfun) {
            $sql .= "c.CDFUNRE, ";
        }

        $sql .= "c.CDCONRE,conv.nrdocori";

        // Executar a consulta SQL
        $queryResult = ibase_query($this->connection, $sql);

        if (!$queryResult) {
            die('Erro na consulta: ' . ibase_errmsg());
        }

        $result = [];
        while ($row = ibase_fetch_assoc($queryResult)) {
            $result[] = $row;
        }
        return $result;
    }

    public function getConvenioManipulado($datainicio, $datafinal, $filial, $cdfun = false)
    {

        $sql = "SELECT ";

        if ($cdfun) {
            $sql .= "z.cdfunre, ";
        }

        $sql .= "z.cdconre,sum(z.VRRCB) as VRRCB
        FROM (SELECT DISTINCT r.cdconre, r.cdfunre,r.nrrqu,conv.vrrcb as VRRCB FROM fc31320 conv
        INNER join fc31110 cx  ON cx.cdfilr = conv.cdfilori and cx.cdpro = conv.nrdocori
        INNER JOIN   FC12100 r ON cx.cdfilr = r.cdfil and cx.cdpro = r.nrrqu
        INNER JOIN FC08000 f on f.cdcon = r.cdconre and f.cdfun = r.cdfunre
        where conv.tpitm = 'R' ";

        // Aplicar filtro de período
        if ($datainicio && $datafinal) {
            $sql .= " AND conv.DTOPE BETWEEN '$datainicio' AND '$datafinal'";
        }

        // Aplicar filtro de filial
        if ($filial && $filial != "Todos") {
            $sql .= " AND r.cdconre IN ($filial)";
        }

        // Aplicar filtro de filial
        if ($cdfun && $cdfun != "Todos") {
            $sql .= " AND r.cdfunre IN ($cdfun)";
        }

        $sql .= ") z GROUP BY ";

        if ($cdfun) {
            $sql .= "z.cdfunre, ";
        }

        $sql .= "z.cdconre";

        // Executar a consulta SQL
        $queryResult = ibase_query($this->connection, $sql);

        if (!$queryResult) {
            die('Erro na consulta: ' . ibase_errmsg());
        }

        $result = [];
        while ($row = ibase_fetch_assoc($queryResult)) {
            $result[] = $row;
        }

        // Retornar os resultados em forma de array
        return $result;
    }

    public function getConvenioRevenda($datainicio, $datafinal, $filial, $cdfun = false)
    {
        $sql = "SELECT ";

        if ($cdfun) {
            $sql .= "c.CDFUNRE, ";
        }

        $sql .= "c.CDCONRE,conv.nrdocori,SUM(i.VRTOT - i.VRDSC - i.VRDSCV + i.VRTXAV) as VRRCB FROM fc31320 conv
        INNER JOIN FC14100 i ON i.cdfil = conv.cdfil and i.nrcpm = conv.nrdocori
        INNER JOIN FC14000 c ON c.NRCPM = i.NRCPM AND c.CDFIL = i.CDFIL
        INNER JOIN FC03000 prod ON i.CDPRO = prod.CDPRO
        where prod.SETOR NOT IN (276, 330)";

        if ($datainicio && $datafinal) {
            $sql .= " AND DTOPE BETWEEN '$datainicio' AND '$datafinal'";
        }

        if ($filial && $filial != "Todos") {
            $sql .= " AND conv.CDFIL IN ($filial)";
        }
        if ($cdfun && $cdfun != "Todos") {
            $sql .= " AND c.cdfunre IN ($cdfun)";
        }

        $sql .= " GROUP BY ";

        if ($cdfun) {
            $sql .= "c.CDFUNRE, ";
        }
        $sql .= "c.CDCONRE,conv.nrdocori";

        $queryResult = ibase_query($this->connection, $sql);

        if (!$queryResult) {
            die('Erro na consulta: ' . ibase_errmsg());
        }

        $result = [];
        while ($row = ibase_fetch_assoc($queryResult)) {
            $result[] = $row;
        }

        return $result;
    }


    public function getDetalhesManipulado($datainicio, $datafinal, $filial, $cdfun = null)
    {
        $filiais = array_map('trim', explode(',', $filial));
        $placeholders = implode(',', array_fill(0, count($filiais), '?'));

        $sql = "SELECT
                A1.codigo_empresa_padrao AS cdfil,
                CAST(COALESCE(SUM(A1.VALORVENDA_DESC), 0) AS NUMERIC(18,2)) AS SOMA,
                A1.codigo AS nrcpm
            FROM ATENDIMENTO_A1 A1
            LEFT JOIN UTIL_REL_AG1 (1) AG ON AG.AG1_OP > 0
            LEFT JOIN ATENDIMENTO_CF CF ON A1.CODIGO_ATEND_FL = CF.CODIGO_ATEND_FL AND CF.CANCELAMENTO_CF IS NULL
            LEFT JOIN MEDICO M ON M.CODIGO = A1.CODIGO_MEDICO
            LEFT JOIN MEDICO_CR CR ON CR.CODIGO = M.CODIGO_CONSELHO_REGIONAL
            INNER JOIN FUNCIONARIO FU ON FU.CODIGO = IIF(AG.AG1_OP = 1, A1.CODIGO_FUNCIONARIO_COMISSAO, A1.AVIADA_CF)
            WHERE A1.TIPO_ATENDIMENTO = 1
       AND CAST(A1.entregue_dt AS DATE) BETWEEN ? AND ?
        AND A1.codigo_empresa_padrao IN ($placeholders)
         GROUP BY nrcpm, A1.codigo_empresa_padrao ";

        $stmt = ibase_prepare($this->connection, $sql);
        $params = array_merge([$datainicio, $datafinal], $filiais);
        $queryResult = ibase_execute($stmt, ...$params);

        if (!$queryResult) {
            die('Erro na consulta: ' . ibase_errmsg());
        }

        $result = [];
        while ($row = ibase_fetch_assoc($queryResult)) {
            $result[] = $row;
        }

        return $result;
    }


    public function getDetalhesRevenda($datainicio, $datafinal, $filial, $cdfun = null)
    {
        $filiais = array_map('trim', explode(',', $filial));
        $placeholders = implode(',', array_fill(0, count($filiais), '?'));

        $sql = "SELECT 
                A1.AVIADA_LJ AS cdfil,
                 A1.codigo as nrcpm,
                CAST(SUM(A3.valorvenda_desc) AS NUMERIC(18,2)) AS SOMA
           FROM atendimento_a3 A3
           INNER JOIN atendimento_a1 A1 ON A3.codigo_atend_a1 = A1.codigo
           INNER JOIN estoque_geral EG ON A3.codigo_produto = EG.codigo
           INNER JOIN medico MD ON MD.codigo = A1.codigo_medico
           LEFT JOIN medico_cr CR ON CR.codigo = MD.codigo_conselho_regional
           WHERE A1.tipo_atendimento = 2
               AND A3.excluirproduto_cf IS NULL
               AND A1.cancelamento_lj IS NULL
               AND A3.QUANTIDADE > 0
               AND A1.PAGAR_COMISSAO_FUNCIONARIO = '-1'
               AND A1.STATUS_MOV = '-1'
               AND A3.codigo_produto NOT IN ('3024873', '3025044')
               AND EG.codigo_estoque_classificacao NOT IN (300002)
               AND CAST(A1.entregue_dt AS DATE) BETWEEN ? AND ?
                AND A1.AVIADA_LJ IN ($placeholders)
                    GROUP BY nrcpm,A1.AVIADA_LJ
        ";

        $stmt = ibase_prepare($this->connection, $sql);
        $params = array_merge([$datainicio, $datafinal], $filiais);
        $queryResult = ibase_execute($stmt, ...$params);

        if (!$queryResult) {
            die('Erro na consulta: ' . ibase_errmsg());
        }

        $result = [];
        while ($row = ibase_fetch_assoc($queryResult)) {
            $result[] = $row;
        }

        return $result;
    }


    public function getDetalhesMarca($datainicio, $datafinal, $filial, $cdfun = null)
    {
        $filiais = array_map('trim', explode(',', $filial));
        $placeholders = implode(',', array_fill(0, count($filiais), '?'));

        $sql = "SELECT
           A1.AVIADA_LJ AS cdfil,
           A1.codigo as nrcpm,
           CAST(SUM(A3.valorvenda_desc) AS NUMERIC(18,2)) AS SOMA
           FROM atendimento_a3 A3
           INNER JOIN atendimento_a1 A1 ON A3.codigo_atend_a1 = A1.codigo
           INNER JOIN estoque_geral EG ON A3.codigo_produto = EG.codigo
           INNER JOIN medico MD ON MD.codigo = A1.codigo_medico
           LEFT JOIN medico_cr CR ON CR.codigo = MD.codigo_conselho_regional
           WHERE A1.tipo_atendimento = 2
           AND A3.excluirproduto_cf IS NULL
           AND A1.cancelamento_lj IS NULL
           AND A3.QUANTIDADE > 0
           AND A1.PAGAR_COMISSAO_FUNCIONARIO = '-1'
           AND A1.STATUS_MOV = '-1'
           AND (A3.codigo_produto IN ('3024873', '3025044') OR EG.codigo_estoque_classificacao IN (300002))
           AND CAST(A1.entregue_dt AS DATE) BETWEEN ? AND ?
           AND A1.AVIADA_LJ IN ($placeholders)
           GROUP BY nrcpm,A1.AVIADA_LJ";

        $stmt = ibase_prepare($this->connection, $sql);
        $params = array_merge([$datainicio, $datafinal], $filiais);
        $queryResult = ibase_execute($stmt, ...$params);

        if (!$queryResult) {
            die('Erro na consulta: ' . ibase_errmsg());
        }

        $result = [];
        while ($row = ibase_fetch_assoc($queryResult)) {
            $result[] = $row;
        }

        return $result;
    }
}
