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

class Caixa
{
    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 z.nomefun, z.cdfunre, z.cdfil, z.nrrqu as nrcpm, z.nrrqu as nrdocori, SUM(z.soma) as soma FROM
        (SELECT c.dtope, (c.vrcxa - c.vrdscv) soma, f.nomefun, r.nrrqu, r.cdfunre, r.cdfil
        FROM fc31110 c 
        INNER JOIN FC12100 r ON c.cdfilr = r.cdfil AND c.cdpro = r.nrrqu
        INNER JOIN FC08000 f ON f.cdcon = r.cdconre AND f.cdfun = r.cdfunre
        WHERE c.dtope BETWEEN ? AND ?
        AND r.cdfil IN ($placeholders)
        GROUP BY c.dtope, r.nrrqu, r.cdfunre, soma, f.nomefun, r.cdfil) z
        GROUP BY z.cdfunre, z.nomefun, z.cdfil, z.nrrqu";

        $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 f.nomefun, a.cdfunre, a.cdfil, a.nrcpm, a.nrcpm as nrdocori, SUM(b.vrliq + b.vrtxag) as soma 
        FROM FC31100 a 
        INNER JOIN fc31110 b ON a.cdfil = b.cdfil AND a.cdtml = b.cdtml AND a.operid = b.operid AND a.nrcpm = b.nrcpm
        INNER JOIN fc03000 p ON p.cdpro = b.cdpro
        INNER JOIN fc08000 f ON f.cdcon = a.cdconre AND f.cdfun = a.cdfunre
        WHERE a.dtope BETWEEN ? AND ?
        AND b.tpitm = 'V' 
        AND b.tppag NOT IN ('3', '2')
        AND p.setor NOT IN (276, 330)
        AND a.cdfil IN ($placeholders)
        GROUP BY a.cdfunre, f.nomefun, a.cdfil, a.nrcpm";

        $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 f.nomefun, a.cdfunre, a.cdfil, a.nrcpm, a.nrcpm as nrdocori, SUM(b.vrliq + b.vrtxag) as soma 
        FROM FC31100 a 
        INNER JOIN fc31110 b ON a.cdfil = b.cdfil AND a.cdtml = b.cdtml AND a.operid = b.operid AND a.nrcpm = b.nrcpm
        INNER JOIN fc03000 p ON p.cdpro = b.cdpro
        INNER JOIN fc08000 f ON f.cdcon = a.cdconre AND f.cdfun = a.cdfunre
        WHERE a.dtope BETWEEN ? AND ?
        AND b.tpitm = 'V' 
        AND b.tppag NOT IN ('3', '2')
        AND p.setor IN (276, 330)
        AND a.cdfil IN ($placeholders)
        GROUP BY a.cdfunre, f.nomefun, a.cdfil, a.nrcpm";

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