<?php





function runRegraOnOS($os)
{
    exec('start ../bd/ConfiguraNF.exe "<OS>' . $os . '</OS>"', $output, $return);
}



function getGeneratorNumNota($codloja)
{
    include 'conecta.inc.php';

    $select = "SELECT NOMEGENNFE FROM lojas WHERE codloja=$codloja";

    $qry = ibase_query($con, $select); //cria o id q recebe o select
    $result = ibase_fetch_row($qry); //pega a unica linha que o select retorna

    return $result[0];
}




function getNumNota($codos)
{
    include 'conecta.inc.php';

    $select = "select numnota from notafiscal where codos = $codos";

    $qry = ibase_query($con, $select); //cria o id q recebe o select
    $result = ibase_fetch_row($qry); //pega a unica linha que o select retorna

    return $result[0];
}


function getIndpres($codNfOperacao)
{
    include 'conecta.inc.php';

    $select = "SELECT * FROM NFOPERACOES WHERE codnfoperacao=$codNfOperacao";

    $qry = ibase_query($con, $select); //cria o id q recebe o select
    $result = ibase_fetch_assoc($qry); //pega a unica linha que o select retorna

    return $result["NUMNFOPERACAO"];
}


function generateNumNota($codloja)
{
    $generator = getGeneratorNumNota($codloja);

    include 'conecta.inc.php';

    $select = "SELECT GEN_ID($generator,1) from rdb\$database ";

    $qry = ibase_query($con, $select); //cria o id q recebe o select
    $result = ibase_fetch_row($qry); //pega a unica linha que o select retorna

    return $result[0];
}


function generateCodNF()
{

    include 'conecta.inc.php';

    $select = "SELECT GEN_ID(GNGERAL,1) from rdb\$database ";

    $qry = ibase_query($con, $select); //cria o id q recebe o select
    $result = ibase_fetch_row($qry); //pega a unica linha que o select retorna

    return $result[0];
}





function getModFrete($tpfrete, $tiposFrete)
{

    switch ($tpfrete) {
        case $tiposFrete['emitente']:
            return 0;
        case $tiposFrete['destinatario']:
            return 1;
        case $tiposFrete['ter']:
            return 2;
        case $tiposFrete['semFrete']:
            return 9;

        default:
            return 0;
    }
}


function getNFConfig($codloja)
{
    include 'conecta.inc.php';

    $select = "SELECT * from nfconfig where codloja=$codloja";

    $result = qry($con, $select, $a_campos);

    if (cria_matriz_campos($result, $a_campos, $r)) {
        if (is_null($r) || !is_array($r) || is_null($r[0]))
            return null;

        return $r[0];
    }
}


function getCFOP($codCFOP)
{
    include 'conecta.inc.php';

    $select = "SELECT codcfop, cfop, natoper, isento from cfop where codcfop=$codCFOP";

    $result = qry($con, $select, $a_campos);

    if (cria_matriz_campos($result, $a_campos, $r)) {
        if (is_null($r) || !is_array($r) || is_null($r[0]))
            return null;

        return $r[0];
    }
}

function getCSOSN($codCSOSN)
{
    include 'conecta.inc.php';

    $select = "SELECT * from csosn where codcsosn = $codCSOSN";

    $result = qry($con, $select, $a_campos);

    if (cria_matriz_campos($result, $a_campos, $r)) {
        if (is_null($r) || !is_array($r) || is_null($r[0]))
            return null;

        return $r[0];
    }
}

function getCST($codCST)
{
    include 'conecta.inc.php';

    $select = "SELECT * FROM prodcst WHERE codcst = $codCST";

    $result = qry($con, $select, $a_campos);

    if (cria_matriz_campos($result, $a_campos, $r)) {
        if (is_null($r) || !is_array($r) || is_null($r[0]))
            return null;

        return $r[0];
    }
}



function getFCI($codpro, $codloja, $revcons)
{
    include 'conecta.inc.php';

    $select = "SELECT FCI FROM produtosFCI WHERE codpro=$codpro AND codloja=$codloja AND revcons = $revcons rows 1 to 1";

    $result = qry($con, $select, $a_campos);

    if (cria_matriz_campos($result, $a_campos, $r)) {
        if (is_null($r) || !is_array($r) || is_null($r[0]))
            return null;

        return $r[0]["FCI"];
    }
}



function salvarCamposNota($os, $codusu, $codloja=null)
{
    include '../idiomas/config.inc';

    if(!is_null($codloja)) {
        $os["numNota"] = generateNumNota($os["codloja"]);
    } else {
        $os["numNota"] = "NULL";
    }
    $codNF = insertOnMaster($os, $codusu);
    if (!$codNF)
        throw new Exception("$msg_throw_insert " . $os["os"] . " $msg_throw_complemento");

    insertOnDetail($codNF, $os, $codusu);


    return $codNF;
}


function salvarCamposNotaExistente($os, $codusu)
{
    include '../idiomas/config.inc';

    $numNota = getNumNota($os['codos']);
    $codNF = getCodNfByCodOs($os['codos']);

    deleteNotaByCodNF($codNF);

    $os["numNota"] = $numNota;
    $codNF = insertOnMaster($os, $codusu);
    if (!$codNF)
        throw new Exception("$msg_throw_insert " . $os["os"] . " $msg_throw_complemento");

    insertOnDetail($codNF, $os, $codusu);

    return $codNF;
}




function somaValorTotalIPI($itens)
{
    $totalIPI = 0;
    foreach ($itens as $item) {
        $totalIPI += $item['vlripi'];
    }
    return $totalIPI;
}

function somaValorTotalPIS($itens)
{
    $totalPIS = 0;
    foreach ($itens as $item) {
        $totalPIS += $item['vlrpis'];
    }
    return $totalPIS;
}

function somaValorTotalCOFINS($itens)
{
    $totalCOFINS = 0;
    foreach ($itens as $item) {
        $totalCOFINS += $item['vlrcofins'];
    }
    return $totalCOFINS;
}

function anyProductHasAliqSubstituicao($itens)
{

    foreach ($itens as $item) {
        if ($item["aliqst"] <> 0) // não tenho certeza se é este campo aqui...
            return true;
    }
    return false;
}

function somaValorTotalProdutos($itens)
{
    $totalProdutos = 0;
    foreach ($itens as $item) {
        $totalProdutos += $item['unitcomdesc'] * $item['qtde'];
    }
    return $totalProdutos;
}

function somaValorTotalDescontos($itens)
{
    $totalProdutos = 0;
    foreach ($itens as $item) {
        $totalProdutos += $item['desconto'] * $item['qtde'];
    }
    return $totalProdutos;
}

function getTotalPesoLiq($itens)
{
    $total = 0;
    foreach ($itens as $item) {
        if(is_numeric($item['pesoliq']))
            $total += $item['pesoliq'] * $item['qtde'];
    }
    return $total;
}
function getTotalPesoBruto($itens)
{
    $total = 0;
    foreach ($itens as $item) {
        if(is_numeric($item['pesobruto']))
            $total += $item['pesobruto'] * $item['qtde'];
    }
    return $total;
}

function getTotalQuantidadeVolumes($itens, $somarQtdeVolume)
{
    if($somarQtdeVolume != "1")
        return 1;

    $total = 0;
    foreach ($itens as $item) {
        if(is_numeric($item['qtde']))
            $total += $item['qtde'];
    }
    return $total;
}

function getTotalProdutos($itens)
{
    return somaValorTotalProdutos($itens); //- somaValorTotalPIS($itens) - somaValorTotalCOFINS($itens);
}

function getTotalDesconto($itens)
{
    return somaValorTotalDescontos($itens);
}

function concatenaParcelas($parcelas)
{
    $resultado = '';

    foreach ($parcelas as $parcela) {
        $resultado .= number_format($parcela['valor'], 2, ',', '.') . ' ' . $parcela['dtvencto'] /* dd/mm/yyyy */ . ' \_ ';
    }
    return $resultado;
}

function isNotaValida($os)
{
}

function saveRetornoDaNota($codNf, $chaveNfe, $recibo, $numeroProtocolo, $codStatusSefaz, $digestValue = null, $xMotivo = null, $cStat = null)
{
    include '../idiomas/config.inc';

    $isDenegada = $codStatusSefaz == 5 ? '1' : 'null';
    $isCancelada = $codStatusSefaz != 3 ? '1' : 'null';
    $update = "UPDATE notafiscal SET
            chavenfe='$chaveNfe',
            recibonfe='$recibo',
            protocolonfe='$numeroProtocolo',
            codstatussefaz='$codStatusSefaz',
            cancelada=$isCancelada,
            anulada=null,  
            imprimindo=null,
            isdenegada=$isDenegada
            " . ($digestValue != null ? ",digestValue='$digestValue'" : "") . "
            " . ($xMotivo != null ? ",msgerronfe='$xMotivo'" : "") . "
            " . ($cStat != null ? ",codmsgnfe='$cStat'" : "") . "

            WHERE codnf = $codNf
    ";

    if (exec_sql_simples($con, $update)) {
        return true;
    } else {
        throw new Exception("$msg_throw_update " . $os["os"] . " $msg_throw_complemento");
    }
}



function saveNumNota($codNf, $numnota)
{
    include '../idiomas/config.inc';

    $update = "UPDATE notafiscal
            SET numnota ='$numnota'
            WHERE codnf = $codNf
    ";

    if (exec_sql_simples($con, $update)) {
        return true;
    } else {
        throw new Exception("$msg_throw_numnota " . $os["os"] . " $msg_throw_complemento");
    }
}
function salvarVolumeNF($codNf, $volume)
{
    include 'conecta.inc.php';
    include_once 'funcoes.inc';
    include '../idiomas/config.inc';

    $update = "UPDATE notafiscal
            SET traqtde ='$volume'
            WHERE codnf = $codNf
    ";

    if (exec_sql_simples($con, $update)) {
        return true;
    } else {
        throw new Exception("$msg_throw_numnota " . $os["os"] . " $msg_throw_complemento");
    }
}



function getRetornoDaNota($codNf)
{
    include 'conecta.inc.php';
    include_once 'funcoes.inc';

    $select = "SELECT 
            chavenfe,
            recibonfe,
            protocolonfe,
            codstatussefaz

            FROM notafiscal
            WHERE codnf = $codNf
    ";

    $query = qry($con, $select, $a_campos); //cria o id q recebe o select

    if (cria_matriz_campos($query, $a_campos, $result)) {
        ibase_close($con); //fecha a conexao
        $retorno = $result[0];
        return array(
            'codNF' => $codNf,
            'chaveNFe' => $retorno['chavenfe'],
            'reciboNFe' => $retorno['recibonfe'],
            'protocoloNFe' => $retorno['protocolonfe'],
            'codStatusSefaz' => $retorno['codstatussefaz']
        );
    }
}


function salvarCamposDest($codos, $dest)
{
    include 'conecta.inc.php';
    include_once 'funcoes.inc';
    include '../idiomas/config.inc';

    $update = "UPDATE NOTAFISCAL
            SET
                rgie = '" .  $dest['ie_e17'] . "',
                endereco = '" .  $dest['xlgr_e06'] . "',
                endnum = '" .  $dest['nro_e07'] . "',
                complemento = '" .  $dest['xcpl_e08'] . "',
                bairro = '" .  $dest['xbairro_e09'] . "',
                codcid = " .  $dest['cmun_e10'] . ",
                uf = '" .  $dest['uf_e12'] . "',
                cep = '" .  $dest['cep_e13'] . "',
                fone1 = '" .  $dest['fone_e16'] . "'
            where codos = $codos";

    if (exec_sql_simples($con, $update)) {
        return true;
    } else {
        throw new Exception("$msg_throw_dados_destinatario");
    }
}

function deleteNotaByCodNF($codNF)
{
    include 'conecta.inc.php';
    include_once 'funcoes.inc';
    include '../idiomas/config.inc';

    $deleteNota = "DELETE FROM NOTAFISCAL where codnf = $codNF";

    if (exec_sql_simples($con, $deleteNota)) {
        $deleteNota = "DELETE FROM NFITENS where codnf = $codNF";

        if (exec_sql_simples($con, $deleteNota)) {
            return true;
        } else {
            throw new Exception("$msg_throw_deletar_nf_itens");
        }
    } else {
        throw new Exception("$msg_throw_deletar_tabela_notafiscal");
    }
}



function insertOnMaster($os, $codusu)
{
    include '../idiomas/config.inc';

    $codNF = generateCodNF();
    $configs = getNFConfig($os["codloja"]);


    $tiposNota = array(
        "entrada" => 0,
        "saida" => 1
    );

    $tiposCliente = array(
        "revenda" => 0,
        "consumidor" => 1,
        "indefinido" => 1
    );

    $tiposFrete = array(
        "emitente" => 1,
        "destinatario" => 2,
        "ter" => 3, // ???? não sei que caralhos é 'ter'. Talvez seja teceiros
        "semFrete" => 4
    );

    $tipoEmitende = array(
        "proprio" => 'P',
        "terceiros" => 'T'
    );

    $situacaoDaNota = array(
        "naoEmitida" => 'N',
        "emitida" => 'N',
        "cancelada" => 'S',
        "canceladaN" => 'E',
        "canceladaX" => 'X',
    );

    
    $hasSubstituicao = anyProductHasAliqSubstituicao($os['itens']);

    $isNotaValida = isNotaValida($os);


    $isCFOPIsento = false;

    foreach ($os["CFOPs"] as $cfop) {
        if ($cfop["isento"] == 1) {
            $isCFOPIsento = true;
        }
    }

    $totalPesoLiq = getTotalPesoLiq($os['itens']);
    $totalPesoBruto = getTotalPesoBruto($os['itens']);
    $totalQuantidadeVolumes = getTotalQuantidadeVolumes($os['itens'], $configs["somarqtdevolume"]);

    $insert = "
    INSERT INTO NOTAFISCAL (
                NUMNOTA,
                BAIRRO,
                BASECALCICMS,
                BASECALCICMSSUBS,
                CEP,
                CFOP,
                CIDADE,
                CLFISC,
                CODOS,
                CODPRODS,
                CODSITTRIB,
                CODTRANSP,
                CPCN,
                DATAOS,
                DATAOSFIN,
                DESCSERVS,
                DESCPRODS,
                DTSAIDA,
                DTEMISSAO,
                ENDERECO,
                COMPLEMENTO,
                ENDCOB,
                FONE1,
                HORASAIDA,
                ICMSPRODS,
                QTDE,
                UNITARIO,
                VALTOTALIPI,
                NATUREZAOP,
                NPEDIDO,
                OBS,
                OS,
                OUTRASDESPESAS,
                QTDESER,
                RAZAO,
                LOCALENTREGA,
                RGIE,
                SPEDIDO,
                SITTRIBUT,
                TOTALSERV,
                TOTALPROD,
                TOTALGERALSERV,
                TOTALGERALPRODS,
                TOTAL,
                TRANSPORTADORA,
                TRANSENDERECO,
                TRANSCIDADE,
                TRANSUF,
                TRANSCPCN,
                TRANSRGIE,
                TRAESPECIE,
                TRAMARCA,
                TRAQTDE,
                TRANUMERO,
                TRAPESOBRUTO,
                TRAPESOLIQ,
                UNID,
                UF,
                UNITARIOSER,
                VALORICMS,
                VALTOTALNOTA,
                VALSEGURO,
                VALORICMSSUBS,
                VALFRETE,
                CONDPAG,
                VALISS,
                CANCELADA,
                ENTSAI,
                CODLOJA,
                CODNF,
                CFOP1,
                CFOP2,
                CFOP3,
                NINCIDEPRODS,
                ISENTOPRODS,
                CFOPPRODS,
                NINCIDESERVS,
                ISENTOSERVS,
                CFOPSERVS,
                MODELO,
                SERIE,
                EMITENTE,
                SITUACAONOTA,
                DESPESASACESSORIAS,
                ISENTO,
                ANULADA,
                ENVIADOSINTEGRA,
                CODTRA,
                CODENT,
                IMPRIMINDO,
                MSGCANCELA,
                BONIFICA,
                CODEXP,
                CFOP4,
                NATOP1,
                NATOP2,
                NATOP3,
                NATOP4,
                DADOSADIC2,
                PRAZOPAG,
                CODVEI,
                VEIPLACA,
                VEIMOTOR,
                VEIANO,
                VEICHASSI,
                VEIMODELO,
                VEICOR,
                PSAI,
                PENT,
                PFREMIT,
                PFRDEST,
                ISRETIDO,
                ISCFOPISENTO,
                ISELETRONICA,
                MSGRETENCAO,
                ISISENTO,
                DESCONTOOS,
                ISSUBST,
                MSGSUBST,
                NUMENT,
                NUMTRANSF,
                VEIHODOMETRO,
                POSSUIENTREGA,
                CODPAIS,
                CODDEVOLUCAO,
                ISNOTBOANFP,
                CODOSAUX,
                INFOFINAN,
                BCALCMSG,
                ICMMSG,
                CODUSUEMITE,
                ISSIMPFATURA,
                ALIQPIS,
                VLRCOFINS,
                ALIQCOFINS,
                VLRPIS,
                NSUFRAMA,
                ISSUFRAMA,
                CODOSAUX2,
                OSAUX,
                CODLANCAUX,
                MSGCREDPRENF,
                MSGDEPOSITONF,
                ENDNUM,
                MODFRETE,
                CODCID,
                EMAIL,
                IndPag,
                CRT,
                TRAVSERV,
                TRAVBCRET,
                TRAICMSRET,
                TRAVLRICMS,
                TRACFOP,
                TRACMUNFG,
                TRAPLACA,
                TRAVEIUF,
                TRARNTC,
                VTOTTRIB,
                NAOENVIAFATURA,
                CHAVEREFERENCIADA,
                INDFINAL,
                CODNFOPERACAO,
                IDESTRANGEIRO,
                CODNFIEDEST,
                VLRICMSDESON,
                CODNFTIPOEMISSAO,
                VlrII,
                vlrdesconto,
                VLRFCP,
                CODSTATUSSEFAZ,
                IS_EMITIDA_AFV
            ) VALUES (
                " . $os["numNota"] . ", /* NUMNOTA */
                '" . $os["cliente"]["bairro"] . "', /* BAIRRO */
                " . returnZeroIfEmpty($os["totbcalcicms"]) . ", /* BASECALCICMS */
                " . returnZeroIfEmpty($os["totbcalcsubst"]) . ", /* BASECALCICMSSUBS */
                '" . $os["cliente"]["cep"] . "', /* CEP */
                '', /* CFOP */
                '" . $os["cliente"]["cidade"]["cidade"] . "', /* CIDADE */
                '', /* CLFISC */
                " . $os["codos"] . ", /* CODOS */
                '', /* CODPRODS */
                '', /* CODSITTRIB */
                " . $os["codtra"] . ", /* CODTRANSP */
                '" . $os["cliente"]["cpcn"] . "', /* CPCN */
                " . ($os["dataos"] == '' ? 'null' : ("'" . formataData($os["dataos"]) . "'")) . ", /* DATAOS */
                " . ($os["dataosfin"] == '' ? 'null' : ("'" . formataData($os["dataosfin"]) . "'")) . ", /* DATAOSFIN */
                '', /* DESCSERVS */
                '', /* DESCPRODS */
                current_date, /* DTSAIDA */
                current_date, /* DTEMISSAO */
                '" . $os["cliente"]["endereconf"] . "', /* ENDERECO */
                '" . $os["cliente"]["enderecocompl"] . "', /* COMPLEMENTO */
                '" . $os["cliente"]["endcob"] . "', /* ENDCOB */
                '" . $os["cliente"]["telefonenf"] . "', /* FONE1 */
                '" . date('H:i:s') . "', /* HORASAIDA */
                '', /* ICMSPRODS */
                '', /* QTDE */
                '', /* UNITARIO */
                " . somaValorTotalIPI($os['itens']) . ", /* VALTOTALIPI */
                '', /* NATUREZAOP */
                null, /* NPEDIDO */
                '" . $os["obs"] . "', /* OBS */
                " . $os["os"] . ", /* OS */
                " . returnZeroIfEmpty($os["outrasdesp"]) . ", /* OUTRASDESPESAS */
                '', /* QTDESER */
                '" . $os["cliente"]["razao"] . "', /* RAZAO */
                '', /* LOCALENTREGA */
                '" . $os["cliente"]["rgie"] . "', /* RGIE */
                '', /* SPEDIDO */
                '', /* SITTRIBUT */
                '', /* TOTALSERV */
                '', /* TOTALPROD */
                0, /* TOTALGERALSERV */
                " . returnZeroIfEmpty($os['totprod']) . ", /* TOTALGERALPRODS */
                " . $os["totnf"] . ", /* TOTAL */
                '" . $os["transportadora"]["transportadora"] . "', /* TRANSPORTADORA */
                '" . $os["transportadora"]["endereco"] . "', /* TRANSENDERECO */
                '" . $os["transportadora"]["cidade"] . "', /* TRANSCIDADE */
                '" . $os["transportadora"]["uf"] . "', /* TRANSUF */
                '" . $os["transportadora"]["cpcn"] . "', /* TRANSCPCN */
                '" . $os["transportadora"]["rgie"] . "', /* TRANSRGIE */
                '', /* TRAESPECIE */
                '', /* TRAMARCA */
                " . returnZeroIfEmpty($totalQuantidadeVolumes) . ", /* TRAQTDE */
                '', /* TRANUMERO */
                " . returnZeroIfEmpty($totalPesoBruto) . ", /* TRAPESOBRUTO */
                " . returnZeroIfEmpty($totalPesoLiq) . ", /* TRAPESOLIQ */
                '', /* UNID */
                '" . $os["cliente"]["uf"] . "', /* UF */
                '', /* UNITARIOSER */
                " . $os["totvlricms"] . ", /* VALORICMS */
                " . $os["totnf"] . ", /* VALTOTALNOTA */
                0, /* VALSEGURO */
                " . returnZeroIfEmpty($os["totvlrsubst"]) . ", /* VALORICMSSUBS */
                0, /* VALFRETE */
                '" . concatenaParcelas($os['parcelas']) . "', /* CONDPAG */
                " . returnZeroIfEmpty($os["vlriss"]) . ", /* VALISS */
                1, /* CANCELADA */
                " . ($tiposNota["saida"] + 1) . ", /* ENTSAI */
                " . returnZeroIfEmpty($os["codloja"]) . ", /* CODLOJA */
                " . returnZeroIfEmpty($codNF) . ", /* CODNF */
                '" . (isset($os["CFOPs"][0]) ? $os["CFOPs"][0]['cfop'] : '') . "', /* CFOP1 */
                '" . (isset($os["CFOPs"][1]) ? $os["CFOPs"][1]['cfop'] : '') . "', /* CFOP2 */
                '" . (isset($os["CFOPs"][2]) ? $os["CFOPs"][2]['cfop'] : '') . "', /* CFOP3 */
                '', /* NINCIDEPRODS */
                '', /* ISENTOPRODS */
                '', /* CFOPPRODS */
                '', /* NINCIDESERVS */
                '', /* ISENTOSERVS */
                '', /* CFOPSERVS */
                '" . $configs["modelopadrao"] . "', /* MODELO */
                '" . $configs["seriepadrao"] . "', /* SERIE */
                '" . $tipoEmitende["proprio"] . "', /* EMITENTE */
                '" . $situacaoDaNota["naoEmitida"] . "', /* SITUACAONOTA */
                " . returnZeroIfEmpty($os["outrasdesp"]) . ", /* DESPESASACESSORIAS */
                null, /* ISENTO */
                null, /* ANULADA */
                null, /* ENVIADOSINTEGRA */
                null, /* CODTRA */
                null, /* CODENT */
                null, /* IMPRIMINDO */
                null, /* MSGCANCELA */
                NULL, /* BONIFICA */
                null, /* CODEXP */
                '" . (isset($os["CFOPs"][3]) ? $os["CFOPs"][3]['cfop'] : '') . "', /* CFOP4 */
                '" . (isset($os["CFOPs"][0]) ? $os["CFOPs"][0]['natoper'] : '') . "', /* NATOP1 */
                '" . (isset($os["CFOPs"][1]) ? $os["CFOPs"][1]['natoper'] : '') . "', /* NATOP2 */
                '" . (isset($os["CFOPs"][2]) ? $os["CFOPs"][2]['natoper'] : '') . "', /* NATOP3 */
                '" . (isset($os["CFOPs"][3]) ? $os["CFOPs"][3]['natoper'] : '') . "', /* NATOP4 */
                '', /* DADOSADIC2 */
                '" . $os["forma"]["descricao"] . "', /* PRAZOPAG */
                " . returnZeroIfEmpty($os["codvei"]) . ", /* CODVEI */
                '" . $os["veiculo"]['placa'] . "', /* VEIPLACA */
                '" . $os["veiculo"]['motor'] . "', /* VEIMOTOR */
                '" . $os["veiculo"]['ano'] . "', /* VEIANO */
                '" . $os["veiculo"]['chassis'] . "', /* VEICHASSI */
                '" . $os["veiculo"]['modelo'] . "', /* VEIMODELO */
                '" . $os["veiculo"]['cor'] . "', /* VEICOR */
                'X', /* PSAI */
                '', /* PENT */
                '" . ($os["tpfrete"] != $tiposFrete['destinatario'] ? '1' : '') . "', /* PFREMIT */
                '" . ($os["tpfrete"] == $tiposFrete['destinatario'] ? '2' : '') . "', /* PFRDEST */
                0, /* ISRETIDO */
                " . ($isCFOPIsento ? 1 : 0) . ", /* ISCFOPISENTO */
                0, /* ISELETRONICA */
                '" . ($configs["usaregraretiss"] ? $configs["MSGRETISSCORRETO"] : '') . "', /* MSGRETENCAO */
                " . ($isCFOPIsento ? 1 : 0) . ", /* ISISENTO */
                0, /* DESCONTOOS */
                " . ($hasSubstituicao ? 1 : 0) . ", /* ISSUBST */
                '" . ($hasSubstituicao ? $configs["MSGCOMSUBST"] : '') . "', /* MSGSUBST */
                null, /* NUMENT */
                null, /* NUMTRANSF */
                " . returnZeroIfEmpty($os["hodometro"]) . ", /* VEIHODOMETRO */
                0, /* POSSUIENTREGA */
                1, /* CODPAIS */
                null, /* CODDEVOLUCAO */
                " . ($isNotaValida ? 1 : 0) . ", /* ISNOTBOANFP */
                " . returnZeroIfEmpty($os["tipo"] != '5' ? $os["codos"] : $os["codosorigem"]) . ", /* CODOSAUX */
                '', /* INFOFINAN */
                '" . returnZeroIfEmpty($os["bcalcicms"]) . "', /* BCALCMSG */
                '" . returnZeroIfEmpty($os["totvlricms"]) . "' , /* ICMMSG */
                $codusu, /* CODUSUEMITE */
                0, /* ISSIMPFATURA */
                " . returnZeroIfEmpty($os["aliqpis"]) . ", /* ALIQPIS */
                " . returnZeroIfEmpty($os["vlrcofins"]) . ", /* VLRCOFINS */
                " . returnZeroIfEmpty($os["aliqCofins"]) . ", /* ALIQCOFINS */
                " . returnZeroIfEmpty($os["vlrpis"]) . ", /* VLRPIS */
                '" . ($os["issuframa"] == 1 ? $os["cliente"]["nsuframa"] : '') . "', /* NSUFRAMA */
                " . returnZeroIfEmpty($os["issuframa"])  . ", /* ISSUFRAMA */
                " . returnZeroIfEmpty($os["codos"])  . ", /* CODOSAUX2 */
                " . returnZeroIfEmpty($os["os"])  . ", /* OSAUX */
                " . returnZeroIfEmpty($os["codos"])  . ", /* CODLANCAUX */
                '" . $configs["msgcredpre"]  . "', /* MSGCREDPRENF */
                '', /* MSGDEPOSITONF */
                '" . $os["cliente"]["numeronf"]  . "', /* ENDNUM */
                " . getModFrete($os["tpfrete"], $tiposFrete)  . ",  /* MODFRETE */  
                " . returnZeroIfEmpty($os["cliente"]["codcidnfe"])  . ", /* CODCID */
                '" . $os["cliente"]["emailnfe"]  . "', /* EMAIL */
                " . returnZeroIfEmpty($os["forma"]["indpag"])  . ", /* IndPag */
                " . $configs["crt"]  . ", /* CRT */
                null, /* TRAVSERV */
                null, /* TRAVBCRET */
                null, /* TRAICMSRET */
                null, /* TRAVLRICMS */
                '', /* TRACFOP */
                null, /* TRACMUNFG */
                '', /* TRAPLACA */
                '', /* TRAVEIUF */
                '', /* TRARNTC */
                " . number_format(returnZeroIfEmpty($os["vtottrib"]), 3, ".", "")  . ", /* VTOTTRIB */
                " . ($configs["naoenviafatura"] == 1 ? 1 : 0) . ", /* NAOENVIAFATURA */
                '', /* CHAVEREFERENCIADA */
                " . ($os["revcons"] == $tiposCliente["revenda"] ? 0 : 1) . ", /* INDFINAL */
                " . returnZeroIfEmpty($os["cliente"]["codnfoperacao"])  . ", /* CODNFOPERACAO */
                '" . $os["cliente"]["idestrangeiro"]  . "', /* IDESTRANGEIRO */
                " . returnZeroIfEmpty($os["cliente"]["codnfiedest"])  . ", /* CODNFIEDEST */
                " . returnZeroIfEmpty($os["vlricmsdeson"])  . ", /* VLRICMSDESON */
                " . $configs["codnftipoemissao"]  . ", /* CODNFTIPOEMISSAO */
                null, /* VlrII */
                0, /* vlrdesconto */
                " . returnZeroIfEmpty($os["vlrfcp"])  . " /* VLRFCP */,
                1 /* CODSTATUSSEFAZ */,
                1 /* IS_EMITIDA_AFV */
            )
    ";

    if (exec_sql_simples($con, $insert)) {
        return $codNF;
    } else {
        throw new Exception("$msg_throw_insert " . $os["os"] . " $msg_throw_complemento");
    }
}

function getNcmsUF($uf, $codloja, $codncm)
{
    include '../auxiliar/parametros.inc';
    include 'conecta.inc.php';
    include_once 'funcoes.inc';

    $select = "SELECT *  FROM NCMUFS WHERE upper(uf) = '" . strtoupper($uf) .  "' AND codloja=$codloja AND codncm=$codncm";


    $query = qry($con, $select, $a_campos); //cria o id q recebe o select

    if (cria_matriz_campos($query, $a_campos, $result)) {
        ibase_close($con); //fecha a conexao
        return $result[0];
    }
}

function getCodNfByCodOs($codOs)
{
    include '../auxiliar/parametros.inc';
    include 'conecta.inc.php';
    include_once 'funcoes.inc';

    $select = "SELECT codnf FROM NOTAFISCAL WHERE codos=$codOs";


    $query = qry($con, $select, $a_campos); //cria o id q recebe o select

    if (cria_matriz_campos($query, $a_campos, $result)) {
        ibase_close($con); //fecha a conexao
        return $result[0]["codnf"];
    }
}


function getCBenef($cst, $codloja, $codpro)
{
    include '../auxiliar/parametros.inc';
    include 'conecta.inc.php';
    include_once 'funcoes.inc';

    $select = "SELECT 
            B.DESCRICAO,
            n.codcst,
            n.motDesICMS
        FROM NFCBENEFPROD N 
            JOIN NFCBENEF B ON N.CODCBENEF = B.CODCBENEF
            JOIN PRODCST C ON C.CODCST = N.CODCST 

        WHERE 
            C.CST = $cst 
            AND N.CODPRO = $codpro 
            AND N.CODLOJA = $codloja
       ";


    $query = qry($con, $select, $a_campos); //cria o id q recebe o select

    if (cria_matriz_campos($query, $a_campos, $result)) {
        ibase_close($con); //fecha a conexao
        return $result[0];
    }
}









function insertOnDetail($codNF, $os, $codusu)
{
    include '../idiomas/config.inc';
    
    foreach ($os["itens"] as $item) {

        $cfop = getCFOP($item["codcfop"]);
        $csosn = getCSOSN($item["codcsosn"]);
        $cst = getCST($item["codcst"]);


        $fci = getFCI($item["codpro"], $os["codloja"], $os['revcons']);
        $ncmUf = getNcmsUF($os["cliente"]["uf"], $os["codloja"], $item['codncm']);


        $insert = "
            INSERT INTO NFITENS (
                CODNF,
                CODOS,
                CODPRO,
                DESCRICAO,
                TOTALITEN,
                NUMITEM,
                CODIGO,
                UN,
                UTRIB,
                CF,
                CST,
                ICMS,
                ICMSDESONERACAO,
                SUBTOTICMS,
                ALIQ_SUBST,
                VLRICMSSUB,
                ICMS_INT_UF,
                DESCONTO,
                DESCONTOPCT,
                UNITARIO,
                VUNTRIB,
                QTDE,
                QTRIB,
                CFOP,
                ISENTO,
                INCIDE,
                PROSER,
                VALORIPI,
                NUMORDEM,
                NUMPEDCOMP,
                ALIQIPI,
                ALIQISS,
                CFISCALCOMP,
                CODCFOP,
                VLRPIS,
                ALIQPIS,
                VLRCOFINS,
                ALIQCOFINS,
                VLRBASECALCIMP,
                VlrDespAduaneiras,
                VlrImpostoImp,
                VlrIOF,
                PREDBCST,
                PREDBC,
                MODBC,
                MODBCST,
                BASECALCSTRET,
                VLRICMSSTRET,
                BASECALCICMS,
                BASECALCICMSST,
                CSOSN,
                FRETE,
                PESO,
                ALIQISUBORI,
                PESOTOT,
                VLTOTCFRETE,
                NCM,
                BCIPI,
                BCPIS,
                BCCOFINS,
                OUTRASDESP,
                CSTPIS,
                CSTIPI,
                CSTCOFINS,
                VTOTTRIB,
                VLRICMSDESON,
                OBS,
                VLRICMSDIFERIDO,
                VLRICMSOP,
                PCTDIFERIMENTO,
                numitemos,
                FCI,
                VLRICMSUFDest_Itens,
                VLRICMSUFRemet_Itens,
                VLRCFPUFDEST_Itens,
                VBCSTRET,
                VICMSSTRET
            ) VALUES (
                " . returnZeroIfEmpty($codNF) . ", /* CODNF */
                " . returnZeroIfEmpty($os["codos"]) . ", /* CODOS */
                " . returnZeroIfEmpty($item["codpro"]) . ", /* CODPRO */
                '" . $item["descricao"] . "', /* DESCRICAO */
                " . number_format(returnZeroIfEmpty($item["unitcomdesc"] * $item["qtde"]), 2, ".", "") . ", /* TOTALITEN */
                " . returnZeroIfEmpty($item["numitem"]) . ", /* NUMITEM */
                '" . $item["codigo"] . "', /* CODIGO */
                '" . $item["un"] . "', /* UN */
                '" . $item["un"] . "', /* UTRIB */
                '" . $item["cf"] . "', /* CF */
                '" . $cst["cst"] . "', /* CST */
                " . number_format(returnZeroIfEmpty($item["aliqicms"]), 4, ".", "") . ", /* ICMS */
                " . number_format(returnZeroIfEmpty($item["icmsdesoneracao"]), 3, ".", "") . ", /* ICMSDESONERACAO */
                " . number_format(returnZeroIfEmpty($item["vlricms"]), 3, ".", "") . ", /* SUBTOTICMS */
                " . number_format(returnZeroIfEmpty($ncmUf["aliq_subst"]), 4, ".", "") . ", /* ALIQ_SUBST */
                " . number_format(returnZeroIfEmpty($item["vlrsubst"]), 3, ".", "") . ", /* VLRICMSSUB */
                " . number_format(returnZeroIfEmpty($os["icmsUF"]["icms_int"]), 3, ".", "") . ", /* ICMS_INT_UF */
                " . number_format(0, 2, ".", "") . ", /* DESCONTO */
                " . number_format(0, 3, ".", "") . ", /* DESCONTOPCT */
                " . number_format(returnZeroIfEmpty($item["unitcomdesc"]), 5, '.', '') . ", /* UNITARIO */
                " . number_format(returnZeroIfEmpty($item["unitcomdesc"]), 5, '.', '') . ", /* VUNTRIB */
                " . number_format(returnZeroIfEmpty($item["qtde"]), 4, ".", "") . ", /* QTDE */
                " . number_format(returnZeroIfEmpty($item["qtde"]), 4, ".", "") . ", /* QTRIB */
                '" . $cfop['cfop'] . "', /* CFOP */
                0, /* ISENTO */
                0, /* INCIDE */
                1, /* PROSER */
                " . number_format(returnZeroIfEmpty($item["vlripi"]), 2, ".", "") . ", /* VALORIPI */
                " . returnZeroIfEmpty($item["ORDEMPEDCOMP"]) . ", /* NUMORDEM */
                " . returnZeroIfEmpty($item["numpedcomp"]) . ", /* NUMPEDCOMP */
                " . number_format(returnZeroIfEmpty($item["ipi"]), 4, ".", "") . ", /* ALIQIPI */
                0, /* ALIQISS */
                '" . $item['cf'] . "', /* CFISCALCOMP */
                " . returnZeroIfEmpty($item["codcfop"]) . ", /* CODCFOP */
                " . number_format(returnZeroIfEmpty($item["vlrpis"]), 2, ".", "") . ", /* VLRPIS */
                " . number_format(returnZeroIfEmpty($item["aliqpis"]), 4, ".", "") . ", /* ALIQPIS */
                " . number_format(returnZeroIfEmpty($item["vlrcofins"]), 2, ".", "") . ", /* VLRCOFINS */
                " . number_format(returnZeroIfEmpty($item["aliqcofins"]), 4, ".", "") . ", /* ALIQCOFINS */
                0, /* VLRBASECALCIMP */
                0, /* VlrDespAduaneiras */
                0, /* VlrImpostoImp */
                0, /* VlrIOF */
                " . number_format(returnZeroIfEmpty($item["predbcst"]), 4, ".", "") . ", /* PREDBCST */
                " . number_format(returnZeroIfEmpty($item["predbc"]), 4, ".", "") . ", /* PREDBC */
                0, /* MODBC */
                0, /* MODBCST */
                " . number_format(returnZeroIfEmpty($item["basecalcstret"]), 2, ".", "") . ", /* BASECALCSTRET */
                " . number_format(returnZeroIfEmpty($item["vlricmsstret"]), 2, ".", "") . ", /* VLRICMSSTRET */
                " . number_format(returnZeroIfEmpty($item["bcalcicms"]), 2, ".", "") . ", /* BASECALCICMS */
                " . number_format(returnZeroIfEmpty($item["bcalcsubst"]), 2, ".", "") . ", /* BASECALCICMSST */
                '" . $csosn['csosn'] . "', /* CSOSN */
                " . number_format(returnZeroIfEmpty($item["vlrfrete"]), 2, ".", "") . ", /* FRETE */
                " . number_format(returnZeroIfEmpty($item["peso"]), 3, ".", "") . ", /* PESO */
                0, /* ALIQISUBORI */
                " . number_format(returnZeroIfEmpty($item["peso"]) * returnZeroIfEmpty($item["qtde"]), 3, ".", "") . ",  /* PESOTOT */
                " . number_format(returnZeroIfEmpty($item["totalcomdesc"]) + returnZeroIfEmpty($item["vlrfrete"]), 2, ".", "") . ",  /* VLTOTCFRETE */
                '" . $item['ncm'] . "', /* NCM */
                " . number_format(returnZeroIfEmpty($item["baseipi"]), 2, ".", "") . ", /* BCIPI */
                " . number_format(returnZeroIfEmpty($item["basepis"]), 2, ".", "") . ", /* BCPIS */
                " . number_format(returnZeroIfEmpty($item["basecofins"]), 2, ".", "") . ", /* BCCOFINS */
                " . number_format(returnZeroIfEmpty($item["outrasdesp"]), 2, ".", "") . ", /* OUTRASDESP */
                '" . $item['cstpis'] . "', /* CSTPIS */
                '" . $item['cstipi'] . "', /* CSTIPI */
                '" . $item['cstcofins'] . "', /* CSTCOFINS */
                " . number_format(returnZeroIfEmpty($item["vtottrib"]), 3, ".", "") . ", /* VTOTTRIB */
                " . number_format(returnZeroIfEmpty($item["vlricmsdeson"]), 3, ".", "") . ", /* VLRICMSDESON */
                '" . $item['obs'] . "', /* OBS */
                " . number_format(returnZeroIfEmpty($item["vlricmsdiferido"]), 3, ".", "") . ", /* VLRICMSDIFERIDO */
                " . number_format(returnZeroIfEmpty($item["vlricmsop"]), 3, ".", "") . ", /* VLRICMSOP */
                " . number_format(returnZeroIfEmpty($item["pctdiferimento"]), 4, ".", "") . ", /* PCTDIFERIMENTO */
                " . returnZeroIfEmpty($item['numitem']) . ", /* numitemos */
                " . ($fci ? "'" . $fci . "'" : 'null') . ", /* FCI */
                " . number_format(returnZeroIfEmpty($item["vlricmsufdest"]), 4, ".", "") . ", /* VLRICMSUFDest_Itens */
                " . number_format(returnZeroIfEmpty($item["vlricmsufremet"]), 4, ".", "") . ", /* VLRICMSUFRemet_Itens */
                " . number_format(returnZeroIfEmpty($item["vfcp"]), 4, ".", "") . ", /* VLRCFPUFDEST_Itens */
                " . number_format(returnZeroIfEmpty($item["vbcstret"]), 2, ".", "") . ", /* VBCSTRET */
                " . number_format(returnZeroIfEmpty($item["vicmsstret"]), 2, ".", "") . " /* VICMSSTRET */
            )
            ";

        if (!exec_sql_simples($con, $insert)) {
            throw new Exception("$msg_throw_deletar_nf_itens Codnf = $codNF CodOs=" . $os["codos"] . " Item => " . $item["codpro"]);
        }
    }
}



function extractDigits($value)
{
    if (is_null($value) || $value == '')
        return 0;
    return preg_replace('/[^0-9]/', '', $value);
}

function returnZeroIfEmpty($value)
{
    if (is_null($value) || $value == '')
        return 0;
    return $value;
}

function returnNullIfEmpty($value)
{
    if (is_null($value) || $value == '')
        return 'null';
    return $value;
}
function formataData($data)
{
    $date = new DateTime($data);
    return $date->format("Y/m/d");
}


function getDadosEmitente($codloja)
{
    include 'conecta.inc.php';
    include_once 'funcoes.inc';

    $select = "SELECT
            u.icms_int,
            coalesce(l.issimples, 0) as issimples,
            l.codloja,
            u.codbacen as cUF_B02,
            l.cmunfg as cMunFG_B12,
            l.cnpj as CNPJ_C02,
            l.razao as xNome_C03,
            l.razao as xFant_C04,
            l.endereco as xLgr_C06,
            l.numero as nro_C07,
            l.complemento as xCpl_C08,
            l.bairro as xBairro_C09,
            l.cmunfg as cMun_C10,
            l.cidade as xMun_C11,
            l.uf as UF_C12,
            l.cep as CEP_C13,
            l.telefone as fone_C16,
            l.ie as IE_C17,
            l.imunicipal as IM_C19,
            l.cnae as CNAE_C20,
            l.aliqicmssimples as pCredSN_n29,
            l.cmunfg as cMunFG_U05,
            /*l.cstpis as CST_Q06,
            l.cstcofins as CST_S06*/
            '000' as CST_Q06,
            '000' as CST_S06,
            l.iscontribipi,
            l.AUTXMLCPF,
            l.AUTXMLCNPJ,
            l.ISCONTRIBIPI


        from
            lojas l
            left join uf u on l.uf = u.uf
        where
            l.codloja = $codloja";


    $query = qry($con, $select, $a_campos); //cria o id q recebe o select

    if (cria_matriz_campos($query, $a_campos, $result)) {
        ibase_close($con); //fecha a conexao
        return $result[0];
    }
}


function getNF($codNF)
{
    include 'conecta.inc.php';
    include_once 'funcoes.inc';


    $select = "SELECT
                nf.codent,
                nf.codstatussefaz,
                nf.naoenviafatura,
                nf.codexp,
                nf.anulada,
                nf.numnota,
                nf.chavenfe,
                nf.protocolonfe,
                nf.msgcancela,
                nf.email,
                nf.entsai,
                nf.codloja,
                nf.codnf,
                nf.codos,
                cli.emailnfe,
                nf.codtra,
                nf.coddevolucao,
                nf.IDDEST,
                cli.isorgpublico,
                nf.recibonfe,
                nf.isdenegada,
                nf.codnftipoemissao,
                nf.vlrii as vii_w11,
                nf.tranumero as nvol_x30,
                nf.chavereferenciada as refnfe_b13,
                case
                    when (coalesce(nf.codos, 0) <> 0) then (
                        select
                            coalesce(ts.codtiponf, 1)
                        from
                            tiposaidas ts
                            join os o on o.codtiposai = ts.codtiposai
                        where
                            o.codos = nf.codos
                    )
                    else (
                        case
                            when (coalesce(nf.codent, 0) <> 0) then te.codtiponf
                            else 1
                        end
                    )
                end as finnfe_b25,
                coalesce(trim(nf.natop1), '') as natop1,
                coalesce(trim(nf.natop2), '') as natop2,
                coalesce(trim(nf.natop3), '') as natop3,
                coalesce(trim(nf.natop4), '') as natop4,
                nf.vlricmsdeson as vicmsdeson_w04a,
                nf.codnfiedest as indiedest_e16a,
                nf.idestrangeiro as idestrangeiro_e03a,
                nf.indfinal as indfinal_b25a,
                nf.codnfoperacao as indpres_b25b,
                nf.vtottrib as vtottrib_w16a,
                nf.numnota as cnf_b03,
                nf.modelo as mod_b06,
                nf.numnota as nnf_b08,
                nf.dtemissao as demi_b09,
                nf.dtsaida as dsaient_b10,
                nf.horasaida as hsaient_b10a,
                nf.serie as serie_b07,
                nf.crt as crt_c21,
                nf.cpcn as cnpj_e02,
                nf.cpcn as cpf_e03,
                nf.razao as xnome_e04,
                nf.rgie as ie_e17,
                nf.endereco as xlgr_e06,
                nf.endnum as nro_e07,
                nf.complemento as xcpl_e08,
                nf.bairro as xbairro_e09,
                nf.codcid as cmun_e10,
                nf.cidade as xmun_e11,
                nf.uf as uf_e12,
                nf.cep as cep_e13,
                nf.fone1 as fone_e16,
                nf.nsuframa as isuf_e18,
                nf.email as email_e19,
                nf.transcpcn as cnpj_x04,
                nf.transportadora as xnome_x06,
                nf.transrgie as ie_x07,
                nf.transendereco as xender_x08,
                nf.transcidade as xmun_x09,
                nf.transuf as uf_x10,
                nf.traqtde as qvol_x27,
                nf.traespecie as esp_x28,
                nf.tramarca as marca_x29,
                nf.trapesoliq as pesol_x31,
                nf.trapesobruto as pesob_x32,
                nf.basecalcicms as vbc_w03,
                nf.valoricms as vicms_w04,
                nf.basecalcicmssubs as vbcst_w05,
                nf.valoricmssubs as vst_w06,
                nf.totalgeralprods as vprod_w07,
                nf.valfrete as vfrete_w08,
                nf.valseguro as vseg_w09,
                nf.vlrdesconto as vdesc_w10,
                nf.valtotalipi as vipi_w12,
                nf.vlrpis as vpis_w13,
                nf.vlrcofins as vcofins_w14,
                nf.outrasdespesas as voutro_w15,
                nf.valtotalnota as vnf_w16,
                nf.modfrete as modfrete_x02,
                nf.totalgeralserv as vserv_w18,
                nf.totalgeralserv as vbc_w19,
                nf.valiss as viss_w20,
                nf.numnota as nfat_y03,
                nf.valtotalnota as vorig_y04,
                nf.valtotalnota as vliq_y06,
                nf.numnota as ndup_y08,
                nf.obs as infcpl_z03,
                nf.vipidevol,
                nf.travserv as vserv_x12,
                nf.travbcret as vbcret_x13,
                nf.traicmsret as picmsret_x14,
                nf.travlricms as vicmsret_x15,
                nf.tracfop as cfop_x16,
                nf.tracmunfg as cmunfg_x17,
                nf.traplaca as placa_x19,
                nf.traveiuf as uf_x20,
                nf.trarntc as rntc,
                te.isimportacao
                /*tag 1*/
            ,
                p.codbacen as cpais_e14,
                p.pais as xpais_e15,
                tr.email as emailtrans,
                u.descufst as iest_c18,
                cli.fisicocomie,
                nf.indpag,
                coalesce(o.exuf, '') as ufsaidapais_za02,
                coalesce(o.exembarque, '') as xlocexporta_za03,
                coalesce(o.exdespacho, '') as xlocdespacho_za04,
                o.vlricmsufdest as vicmsufdest_w04b,
                o.vlricmsufremet as vicmsufremet_w04c,
                o.vfcp,
                o.vfcpst,
                o.vfcpstret,
                o.referencia_nota_remessa,
                o.vlrfcp as vfcpufdest_w04c,
                CLI.CPCN_ENT,
                CLI.RAZAO_ENT,
                CLI.CODNFIEDEST_ENT,
                CLI.NUMEND_ENT,
                CLI.CPTO_ENT,
                CLI.CODCIDNFE_ENT,
                CLI.CODPAIS_ENT,
                CLI.FONE_ENT,
                CLI.EMAIL_ENT,
                CLI.USAENDENTNFE,
                CLI.Bairro_Ent,
                CLI.CEP_Ent,
                CLI.End_Ent,
                CLI.UF_Ent
            from
                notafiscal nf
                left join os o on o.codos = nf.codos
                left join entradas ent on ent.codent = nf.codent
                left join tipoentradas te on te.codtipoent = ent.codtipoent
                left join transportadora tr on tr.codtra = nf.codtransp
                left join clientes cli on cli.razao = nf.razao
                and nf.cpcn = cli.cpcn
                join paises p on p.codpais = nf.codpais
                left join uf u on u.uf = nf.uf
                and nf.codloja = u.codloja
            where
                nf.codnf = $codNF
    
    ";




    $query = qry($con, $select, $a_campos); //cria o id q recebe o select

    if (cria_matriz_campos($query, $a_campos, $result)) {
        ibase_close($con); //fecha a conexao
        return $result[0];
    }
}


function getDestNfByCodos($codos)
{
    include 'conecta.inc.php';
    include_once 'funcoes.inc';

    $select = "SELECT
            cpcn as cnpj_e02,
            cpcn as cpf_e03,
            razao as xnome_e04,
            rgie as ie_e17,
            endereco as xlgr_e06,
            endnum as nro_e07,
            complemento as xcpl_e08,
            bairro as xbairro_e09,
            codcid as cmun_e10,
            cidade as xmun_e11,
            uf as uf_e12,
            cep as cep_e13,
            fone1 as fone_e16
        FROM NOTAFISCAL
        WHERE codos = $codos
    ";

    $query = qry($con, $select, $a_campos); //cria o id q recebe o select

    if (cria_matriz_campos($query, $a_campos, $result)) {
        ibase_close($con); //fecha a conexao
        return $result[0];
    }
}


function getNfItens($codNF)
{
    include '../auxiliar/parametros.inc';
    include 'conecta.inc.php';
    include_once 'funcoes.inc';

    $select = "SELECT
            DISTINCT nfi.proser,
            nfi.codpro,
            nfi.valoripi,
            nfi.total,
            p.isimportado,
            nfi.fci as nFCI_I70,
            nfi.vlricmsop as vICMSOp_n16a,
            nfi.pctdiferimento as pDif_n16b,
            nfi.vlricmsdiferido as vICMSDif_n16c,
            nfi.numitemos,
            nfi.vlricmsdeson as vicmsdeson_n27a,
            nfi.vtottrib as vTotTrib_M02,
            nfi.numitem as nItem_H02,
            nfi.codigo as cProd_I02,
            nfi.icmsdesoneracao,
            nfi.desconto as vDesc_I17,
            nfi.descricao as xProd_I04,
            nfi.bcpis as vBC_Q07,
            nfi.cfop as CFOP_I08,
            nfi.un as uCom_I09,
            nfi.qtde as qCom_I10,
            nfi.bccofins as vBC_S07,
            nfi.unitario as vUnCom_I10a,
            nfi.unitario_importacao as vuncom_i10b,
            nfi.totaliten as vProd_I11,
            case
                when coalesce(nfi.UTRIB, '') <> '' then nfi.UTRIB
                else nfi.un
            end as uTrib_I13,
            case
                when coalesce(nfi.QTRIB, 0) <> 0 then nfi.QTRIB
                else nfi.qtde
            end as qTrib_I14,
            case
                when coalesce(nf.codent, 0) <> 0 then nfi.unitariotribut
                else case
                    when coalesce(nfi.VUNTRIB, 0) <> 0 then nfi.VUNTRIB
                    else nfi.unitario
                end
            end as vUnTrib_I14a,
            nfi.outrasdesp as vOutro_I17a,
            case
                when (coalesce(oi.codprodorigem, 0) <> 0) then pc2.codorigemnfe
                else pc.codorigemnfe
            end as orig_n11,
            nfi.cst as CST_n12,
            nfi.basecalcicmsst as vBCSTDest_n31,
            nfi.vlricmssub as vICMSSTDest_n32,
            nfi.modbc as modBC_n13,
            nfi.basecalcicms as vBC_n15,
            nfi.predbc as pRedBC_n14,
            nfi.icms as pICMS_n16,
            nfi.subtoticms as vICMS_n17,
            nc.codmodbc as modBCST_n18,
            nfi.aliq_subst as pMVAST_n19,
            nfi.predbcst as pRedBCST_n20,
            nfi.basecalcicmsst as vBCST_n21,
            case
                when coalesce(oi.picmsst, 0) <> 0 then oi.picmsst
                else u.icms_int
            end as pICMSST_n22,
            nfi.vlricmssub as vICMSST_n23,
            nfi.icms as pBCOp_n25,
            nfi.csosn,
            nfi.csosn as CSOSN_n12a,
            nfi.aliqipi pIPI_O13,
            nfi.qtde as qUnid_O11,
            nfi.totaliten as vUnid_O12,
            nfi.valoripi as vIPI_O14,
            nfi.vlrbasecalcimp as vBC_P02,
            nfi.vlrdespaduaneiras as vDespAdu_P03,
            nfi.vlrimpostoimp as vII_P04,
            nfi.vlriof as vIOF_P05,
            nfi.totaliten as vBC_u02,
            nfi.aliqiss as vAliq_u03,
            nfi.bcipi as vBC_O10,
            nfi.frete as vFrete_i15,
            nf.uf as UFST_n24,
            nf.valiss as vISSQN_u04,
            lj.aliqicmssimples as pCredSN_n29,
            lj.cmunfg as cMunFG_U05,
            nfi.obs as InfAdProd_v01,
            nfi.cstipi as CST_O09,
            p.codbarras as cEAN_I03,
            p.codigoanp as cprodanp_l102,
            np.natoper as natOp_B04,
            (
                case
                    when nfi.ncm is null then '11111111'
                    else nfi.ncm
                end
            ) as NCM_I05,
            nfi.aliqpis as pPIS_Q08,
            nfi.vlrpis as vPIS_Q09,
            nfi.aliqcofins as pCOFINS_S08,
            nfi.vlrcofins as vCOFINS_S11,
            nfi.cstpis as CST_Q06,
            nfi.cstcofins as CST_S06,
            coalesce(nc.nve, '') as nve_105a,
            p.pctmixgn as pMixGN_l102a,
            p.descanp,
            nfi.numordem as nitemped_I61,
            nfi.numpedcomp as xPed_I60,
            case
                when coalesce(nfi.pst, 0) = 0 then oi.pst
                else nfi.pst
            end as pst,
            oi.exndraw as nDraw_I51,
            oi.exnre as nRE_I53,
            oi.exchnfe as chNFe_I54,
            oi.exqexport as qExport_I55,
            coalesce(di.coddi, 0) as coddi,
            di.numero as nDI_I19,
            di.dtregistro as dDI_I20,
            di.localdesembaraco as xLocDesemb_I21,
            di.ufdesembaraco as UFDesemb_I22,
            di.dtdesembaraco as dDesemb_I23,
            di.codexp as cExportador_I24,
            di.codviatransp as tpViaTransp_I23a,
            di.vlrafrmm as vAFRMM_I23b,
            di.codintermedio as tpIntermedio_I23c,
            lj.cnpj as CNPJ_I23d,
            lj.uf as UFTerceiro_I23e,
            case
                when (coalesce(oi.enqlegalipi, '') = '') then nc.enqlegalipi
                else oi.enqlegalipi
            end as cEnq_O06,
            nc.cest as cest_i05w,
            oi.vbcalcicmsufdest as vBCUFDest_NA03,
            oi.aliqicmsufdest as pFCPUFDest_NA05,
            oi.aliqicmsinter as pICMSUFDest_NA07,
            oi.aliqicmsinterpart as pICMSInter_NA09,
            oi.vlricmsufdest as pICMSInterPart_NA11,
            oi.vlricmsufremet as vFCPUFDest_NA13,
            oi.aliqcfpufdest as vBCFCPUFDest_NA04,
            oi.vlrcfpufdest as vfcpufdest_na10,
            oi.vbcfcp,
            oi.pfcp,
            oi.vfcp,
            oi.vbcfcpst,
            oi.pfcpst,
            oi.vfcpst,
            oi.vbcfcpstret,
            oi.pfcpstret,
            oi.vfcpstret,
            oi.vbcfcpufdest,
            coalesce(oi.vicmssubstituto, 0) as vicmssubstituto,
            p.codprodanvisa,
            p.pmaxcons_anvisa,
            p.ISISENTOICMS,
            p.motivoisentaanvisa,
            p.extipi as EXTIPI_I06,
            p.codanvisa as codanvisa,
            nfi.vbcstret as vBCSTRet_n26,
            nfi.vicmsstret as vICMSSTRet_n27
        FROM
            nfitens nfi
            join notafiscal nf on nf.codnf = nfi.codnf
            join lojas lj on lj.codloja = nf.codloja
            left join uf u on u.uf = nf.uf
            and nf.codloja = u.codloja
            left join produtos p on p.codpro = nfi.codpro
            left join prodorigens pc on pc.codprodorigem = p.codprodorigem
            left join cfop c on c.codcfop = nfi.codcfop
            left join naturezaoperacao np on np.codcfop = c.codcfop
            left join ositenspro oi on nf.codos = oi.codos
            and oi.codpro = nfi.codpro
            and nfi.numitemos = oi.numitem
            left join prodorigens pc2 on pc2.codprodorigem = oi.codprodorigem
            left join ncms nc on nc.codncm = p.codncm
            left join declaracaoimportacao di on (
                di.codent = coalesce(nf.codent, 0)
                and di.codpro = nfi.codpro
                and di.numitem = nfi.numitemos
            )
        where
            nfi.codnf = $codNF
        order by
            nfi.numitem
    
    ";


    $query = qry($con, $select, $a_campos); //cria o id q recebe o select

    if (cria_matriz_campos($query, $a_campos, $result)) {
        ibase_close($con); //fecha a conexao
        return $result;
    }
}




function getDadosAdicionaisNF($codNF, $codloja, $referenciaNotaRemessa = "")
{
    include '../auxiliar/parametros.inc';
    include 'conecta.inc.php';
    include_once 'funcoes.inc';

    $nfConfig = getNFConfig($codloja);
    $select = $nfConfig["selnfda"];

    $result = preparedQuery($con, $select, $codNF);
    
    if (count($result) < 1)
        return "";

    $obsRemessa = "";
    if ($referenciaNotaRemessa != "") {
        $obsRemessa = "Emissao Ref. a NF Simples Remessa: $referenciaNotaRemessa";
    }
    return array(
        "infCpl_Z03" => trim($result[0][0]) . $obsRemessa,
        "infAdFisco_Z02" => ""
    );
}



/*
    CAMPOS FALTANDO NO CAFE CARNEIRO

    ALTER TABLE OSITENSPRO ADD VBCSTRET DOUBLE PRECISION;
    ALTER TABLE OSITENSPRO ADD VICMSSTRET DOUBLE PRECISION;
    ALTER TABLE OSITENSPRO ADD PICMSST DOUBLE PRECISION;
    ALTER TABLE OSITENSPRO ADD VICMSSUBSTITUTO NUMERIC(10,4)

    ALTER TABLE NFITENS ADD UTRIB VARCHAR(4);
    ALTER TABLE NFITENS ADD QTRIB NUMERIC(10,3);
    ALTER TABLE NFITENS ADD VUNTRIB NUMERIC(10,4)
    ALTER TABLE NFITENS ADD PST NUMERIC(10,4)

    ALTER TABLE NFITENS ADD VLRICMSUFDEST_ITENS DOUBLE PRECISION
    ALTER TABLE NFITENS ADD VLRICMSUFRemet_Itens DOUBLE PRECISION
    ALTER TABLE NFITENS ADD VLRCFPUFDEST_Itens DOUBLE PRECISION

    ALTER TABLE NOTAFISCAL ADD CODSTATUSSEFAZ INTEGER;

    ALTER TABLE PRODUTOS ADD USAGRADE SMALLINT;
    ALTER TABLE FORMAS ADD ISMOSTRAAFV SMALLINT;
    ALTER TABLE TIPOSPAG ADD ISMOSTRAAFV SMALLINT;


    ALTER TABLE LOJAS ADD NOMEGENNFE VARCHAR(150);
    

    CREATE GENERATOR gnnumnotaloja1




    CREATE TABLE NFCBENEFPROD(
        ID INTEGER NOT NULL,
        CODCST INTEGER,
        CODPRO INTEGER,
        CODLOJA INTEGER,
        CODCBENEF INTEGER,
        PRIMARY KEY(ID)
    );

    CREATE GENERATOR GNBENEF;

    CREATE TABLE NFCBENEF (
        CODCBENEF        INTEGER NOT NULL,
        CODUSUALT     INTEGER,
        CODUSUCRIA     INTEGER,
        DATAALT    TIMESTAMP,
        DATACRIA     TIMESTAMP,
        DESCRICAO    VARCHAR(200),
        CADINATIVO     SMALLINT,
        CONSTRAINT "PK_CBENEF" PRIMARY KEY ("CODCBENEF")
    );
  

    alter table nfcbenefprod add motDesICMS integer
 */
