<?php

include_once '../bd/funcoes.inc';

function getServicos($filtro) {
    include 'conecta.inc.php';
      
    $sql = "SELECT 
                s.codigo,
                s.codser,
                s.precovenda,
                s.descricao
            FROM servicos s
            WHERE codigo LIKE '%$filtro%'
                OR descricao LIKE '%$filtro%'";

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

    if (cria_matriz_campos($result, $a_campos, $servicos)) {
        return $servicos;
    } else {
        return array();
    }
}

function getDescricao($codser) {
    include 'conecta.inc.php';
      
    $sql = "SELECT 
                s.descricao

            FROM servicos s
            WHERE codser = $codser
            ";

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

    if (cria_matriz_campos($result, $a_campos, $servicos)) {
        if(isset($servicos[0]))
            return $servicos[0]['descricao'];
    } else {
        return array();
    }
}


function insertServico($servico, $countItem) {
    include 'conecta.inc.php';


    $codosMaisCount = $servico["codos"] . $countItem;
    
    $numItem = $servico["codos"] . $countItem . substr(time(), strlen($codosMaisCount)-9);

    $sql = "INSERT INTO ositensser ( 
                        codos,
                        numitem,
                        codser,
                        codtec,
                        codvnd,
                        QTDE,
                        UNITARIO,
                        PRECOBASE,
                        DESCONTOPCT,
                        DESCONTO
                        )
                    values( 
                        ". $servico["codos"] .",
                        ". $numItem .",
                        ". $servico["codser"] .",
                        ". "NULL" .",
                        ". $servico["codvnd"] .",
                        ". $servico["quantidade"] .",
                        ". $servico["precoVenda"] .",
                        ". $servico["precoVenda"] .",
                        ". $servico["descontoParaCliente"] .",
                        ". $servico["valorDesconto"] ."
                    )";

    return exec_sql_simples($con, $sql);
}


function getItemServicosByCodOs($codos){
    $sql = "
    SELECT
        o.codos,
        s.codser,
        s.codigo,
        s.descricao, 
        oi.qtde,
        oi.unitario,
        oi.descontopct as descvenda,
        oi.desconto as desconto,
        oi.unitcomdesc,
        oi.unitcomdesc * oi.qtde as total,
        oi.totalcomdesc
    
    FROM os o
        JOIN ositensser oi ON o.codos = oi.codos
        JOIN servicos s ON s.codser = oi.codser
        JOIN clientes c ON o.codcli = c.codcli
    
    WHERE o.codos = $codos";


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

    if (cria_matriz_campos($result, $a_campos, $servicos)) {
        return $servicos;
    }
    return array();
}


function getItemServico($codos, $codser){
    $sql = "
    SELECT
        o.codos,
        s.codser,
        s.codigo,
        s.descricao, 
        oi.qtde,
        oi.unitario,
        oi.descontopct as descvenda,
        oi.desconto as desconto,
        oi.unitcomdesc,
        oi.unitcomdesc * oi.qtde as total,
        oi.totalcomdesc
    
    FROM os o
        JOIN ositensser oi ON o.codos = oi.codos
        JOIN servicos s ON s.codser = oi.codser
        JOIN clientes c ON o.codcli = c.codcli
    
    WHERE o.codos = $codos AND oi.codser = $codser";


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

    if (cria_matriz_campos($result, $a_campos, $servicos)) {
        return $servicos[0];
    }
    return array();

}




function removeItemServico($codos, $codser) {
    include 'conecta.inc.php';

    $sql = "DELETE FROM ositensser WHERE codos = $codos AND codser = $codser ";
    
    return exec_sql_simples($con, $sql);
}



function aplicarDescontoNosServicosDaOS($codos, $servicos) {
    include 'conecta.inc.php';

    foreach($servicos as $servico) {
        $desconto = $servico["desconto"];
        $codser = $servico["codser"];
        $sql = "UPDATE ositensser SET desconto = $desconto WHERE codos = $codos AND codser = $codser";
        exec_sql_simples($con, $sql);
        $descontoDe = $servico["descontopct"];
    }
    $sql = "UPDATE ositensser SET descontopct = $descontoDe WHERE codos = $codos";
    
    return exec_sql_simples($con, $sql);
}



function aplicarDescontoNoServico($codos, $servico) {
    include 'conecta.inc.php';

    $desconto = $servico["desconto"];
    $codser = $servico["codser"];
    $descontoDe = $servico["descontopct"];

    $sql = "UPDATE ositensser SET descontopct = $descontoDe, desconto = $desconto WHERE codos = $codos AND codser = $codser";
    return exec_sql_simples($con, $sql);
}

function updateServico($codos, $servico) {
    include 'conecta.inc.php';

    $codos = $servico['codos'];
    $codser = $servico['codser'];
    $qtde = $servico['qtde'];
    $unitario = $servico['unitario'];
    $tipoDesconto = $servico['tipoDesconto'];
    $desc = floatval($servico['desconto']);


    if($tipoDesconto == "absoluto") {
        $desconto = $desc;
        $descontoPct = ($desc*100)/ $unitario;
    } else {
        $descontoPct = $desc;
        $desconto = $unitario * ($desc / 100);
    }

    $sql = "UPDATE ositensser SET descontopct = $descontoPct, desconto = $desconto, unitario = $unitario, qtde = $qtde WHERE codos = $codos AND codser = $codser";
    exec_sql_simples($con, $sql);

}













function GeraImpressoServico($p_codos, $mostrarel = true) {
    include '../idiomas/config.inc';

    $html = "<font size=8  face=Arial color=black><div>
                <BR><BR><BR><BR><BR><BR><BR>
<table  border=0 width=100%>";



    $sql = " select o.os, c.razao, c.cpcn, c.rgie, c.endereco, c.bairro, c.cidade,
                c.uf, c.cep, c.complemento, c.fone1, c.contato1 ,f.descricao as formapagto,tp.descricao as tipopagto,
                t.transportadora,
                (Extract (day from o.dataos) || '/' || extract(month from o.dataos) || '/' ||
                       Extract (year from o.dataos)) as dataos,
                v.vendedor,c.email, c.fax,
                (c.end_ent || ' - ' ||   c.bairro_ent || ' - ' || c.cidade_ent || ' - ' || c.uf_ent || ' - ' || c.cep_ent) as enderecoentrega,
                (c.endcob || ' - ' ||   c.bairrocob || ' - ' || c.cidadecob || ' - ' || c.ufcob || ' - ' || c.cepcob) as enderecocobranca
                , o.totvlrsubst,
                
                l.razao as loja,
                l.codloja,
                o.dadosadicionais

                from os o
                join clientes c on c.codcli = o.codcli
                join vendedores v on v.codvnd = o.codvnd
                left join lojas l on l.codloja = o.codloja
                left join formas f on f.codforma = o.codforma
                left join tipospag tp on tp.codtipo = o.codtipo
                left join transportadora t on t.codtra = o.codtra
                where o.codos = $p_codos ";

    if (cria_matriz_campos_pelo_sql($sql, $mestre)) {
        $html .= MostraMestreImpressoServicos($mostrarel, $mestre[0][os], $mestre[0][razao], $mestre[0][cpcn],
                        $mestre[0][rgie], $mestre[0][endereco], $mestre[0][bairro], $mestre[0][cidade],
                        $mestre[0][uf], $mestre[0][cep], $mestre[0][complemento], $mestre[0][fone1],
                        $mestre[0][contato1], $mestre[0][formapagto], $mestre[0][tipopagto], $mestre[0][transportadora],
                        $mestre[0][dataos], $mestre[0][vendedor], $mestre[0][email],
                        $mestre[0][fax], $mestre[0][enderecoentrega], $mestre[0][enderecocobranca], $mestre[0][totvlrsubst], "(" .$mestre[0][codloja] . ") " . $mestre[0][loja], $mestre[0][dadosadicionais]);
        $formapagto = $mestre[0][formapagto]; //vai ser usado la em baixo
    }


    $servicos = getItemServicosByCodOs($p_codos);



    $tags = "</table><br /><br /><br />
<table border=1 width=100% >
<tr>
<td width=60 align=center>$coluna_serv_qtde</td>
<td width=120>$coluna_serv_cod</td>
<td width=355>$coluna_serv_servico</td>
<td width=120 align=right>$coluna_serv_vlr_serv</td>
<td width=120 align=center>$coluna_serv_total</td>

</tr>";
//155

    $html .= $tags;


    $p_totalbruto = 0;
    $p_totalos = 0;
    foreach ($servicos as $servico) {
        $html .= MostraItensImpressoServico($servico[codigo], $servico[descricao],
                        $servico[desconto], $servico[qtde], $servico[total],
                        $servico[unitcomdesc], $servico[descvenda]);

        $p_totalbruto += $servico[unitcomdesc] * $servico[qtde];
        $p_totalos += $servico[unitcomdesc] * $servico[qtde];
    }

    $tags = "</table>";
    $html .= $tags;




    $tags = "<br />
<table border=0 width=80% >
<tr>
<td width=130 align=right>$coluna_serv_subTotal " . number_format($p_totalbruto, 2, ',', '.') . "</td>
</tr>

<table border=0 width=80% >


<tr>
<td width=130 align=right><b>$coluna_serv_total_final " . number_format($p_totalos, 2, ',', '.') . "</b></td>
</tr>
</table>
<BR>

<table>
<tr>
<td width=790> $coluna_serv_obs " . TrazValorCampo("os", "cast(substring(obs from 1 for 255) as varchar(255))", "codos", $p_codos) . " </td>
</tr>

</table>
";


$tags .= "
</div></font>
<br>
<br>
<br>
<div>
<font size=10  face=Arial color=black>
                                                                  $coluna_serv_Autorizacao_de_Servico_N " . $mestre[0][os] . "
<br>
<br>
<br>
$coluna_serv_Eu ____________________________________________________________________________ $coluna_serv_autorizo_a_execucao <br><br>
$coluna_serv_do_servico_acima_e_aqui_relacionados ____________________________________________________________ <br><br>
$coluna_serv_Na_falta_de_pagamento_autorizo_a_emissao_de_cobranca_bancaria ___________________________________________ <br>
$coluna_serv_Valor_do_servico _____________________________________________________________________________ 
<br>
<br>
<br>
<br>
Data                                  $coluna_serv_Assinatura_do_Cliente                                              $coluna_serv_Assinatura_do_Vendedor<br>
____/____/_____             __________________________________             __________________________________
";





    $tags .= "
</div></font>"; // class='impresso' - div geral para manipular todo o relatorio



    $html .= $tags;

    return $html;
}







function getPrecoMinimoServico($codser) {
    
    $sql = "SELECT precominimo FROM servicos WHERE codser = $codser";
    
    $result = qry($con, $sql, $a_campos);

    if (cria_matriz_campos($result, $a_campos, $precomin)) {
        if(!isset($precomin[0]))
            return 0;
        return $precomin[0]["precominimo"];
    }
}










function GeraImpressoViaInternaModeloSBA($p_codos) {
   include '../idiomas/config.inc';

    $sql = " select o.os, c.razao, c.cpcn, c.rgie, c.endereco, c.bairro, c.cidade,
                c.uf, c.cep, c.complemento, c.fone1, c.contato1 ,f.descricao as formapagto,
                t.transportadora,
                (Extract (day from o.dataos) || '/' || extract(month from o.dataos) || '/' ||
                       Extract (year from o.dataos)) as dataos,
                v.vendedor,c.email, c.fax,
                (c.end_ent || ' - ' ||   c.bairro_ent || ' - ' || c.cidade_ent || ' - ' || c.uf_ent || ' - ' || c.cep_ent) as enderecoentrega,
                (c.endcob || ' - ' ||   c.bairrocob || ' - ' || c.cidadecob || ' - ' || c.ufcob || ' - ' || c.cepcob) as enderecocobranca
                , o.totvlrsubst,
                l.loja,
                l.codloja,
                o.dadosadicionais

                from os o
                join clientes c on c.codcli = o.codcli
                join vendedores v on v.codvnd = o.codvnd
                left join lojas l on l.codloja = o.codloja
                left join formas f on f.codforma = o.codforma
                left join transportadora t on t.codtra = o.codtra
                where o.codos = $p_codos ";

        
    $html = "";

    if (cria_matriz_campos_pelo_sql($sql, $mestre)) {
        $html = "
<table  border=0 width=100%>
<tr>
<td width=500>
    <span>.</span>
</td>
<td align=center width=200>
<h1 style='text-align: center'>$coluna_serv_ORDEM_DE_SERVICO</h1>
</td>
</tr>
<tr>
<td width=500 >
    <span>.</span>
</td>
<td align=center width=200>
    <h1 style='text-align: center'>
    <B>             N " . $mestre[0][os] . "</B>
    </h1>
</td>
</tr>
</table>
<font size=8 face=Arial color=black><div>
                <BR><BR><BR><BR>
<table  border=0 width=100%>";


        $html .= MostraMestreImpressoInterno($mestre[0][razao], "(" .$mestre[0][codloja] . ") " . $mestre[0][loja],$mestre[0][dataos], $mestre[0][vendedor], $mestre[0]["dadosadicionais"]);
        $formapagto = $mestre[0][formapagto]; //vai ser usado la em baixo
    }


    $servicos = getItemServicosByCodOs($p_codos);


    $html .= "</table><br><table border=1 width=100%><thead>
<tr>
<td width=60 align=center>Qtde</td>
<td width=200>$coluna_serv_codigo</td>
<td width=475>$coluna_serv_descricao</td>
<td width=40>$coluna_serv_ok</td>
</tr></thead>";

    foreach ($servicos as $servico) {
        $html .= "<tbody>
<tr>
<td width=60 align=center>" . number_format($servico[qtde], 0, ',', '.') . "</td>
<td width=200>" . $servico['codigo'] . "</td>
<td width=475>" . $servico['descricao'] . "</td>
<td width=40>.</td>
</tr>";
    }


    $html .= "</tbody></table>";
    $html .= "
</div></font>"; // class='impresso' - div geral para manipular todo o relatorio

    $html .= 
    "<br><br><table>
<tr>
<td width=790> $coluna_serv_obs " . TrazValorCampo("os", "cast(substring(obs2 from 1 for 255) as varchar(255))", "codos", $p_codos) . " </td>
</tr>

</table>";

    return $html;
}








function MostraMestreImpressoInterno($p_razao, $loja, $p_data, $vendedor, $dadosAdicionais) {
    include '../idiomas/config.inc';


    $tags = "<tbody><tr>
<td width=500>$coluna_serv_cliente $p_razao</td>
<td width=200>$coluna_serv_loja $loja</td>
</tr>
<tr>
<td width=150>$coluna_serv_data $p_data</td>
<td width=200>$coluna_serv_ssuario $vendedor </td>
<td width=150>Vendedor: $vendedor </td>
<td width=200>$coluna_serv_dados_adicionais $dadosAdicionais </td>
</tr></tbody>";

    return $tags;
}









function MostraItensImpressoServico($p_codigo, $p_descricao, $p_desconto,
        $p_qtde, $p_tot, $p_unitariofinal, $p_descontopct) {

    $tags = "
<tr>
<td width=60 align=center>" . number_format($p_qtde, 0, ',', '.') . "</td>
<td width=120>$p_codigo</td>
<td width=355>$p_descricao</td>
<td width=120 align=right>R$ " . number_format($p_unitariofinal, 2, ',', '.') . "</td>

<td width=120 align=right>R$ " . number_format($p_tot, 2, ',', '.') . "</td>

</tr>";


    return $tags;
}

