/*
 * Faturamento.java
 */
package gicron.bss;

import com.microsoft.sqlserver.jdbc.SQLServerException;
import gi.Config;
import gi.cliente.ClienteArquivo;
import gi.empresa.Empresa;
import gi.util.ConfigFile;
import gi.util.DateUtil;
import gi.util.Format;
import gi.util.NativeDB;
import gicron.Cron;
import gicron.bean.Assinatura;
import gicron.bean.AssinaturaServico;
import gicron.bean.CallVSC;
import gicron.bean.DescontoAgendado;
import gicron.bean.Duplicata;
import gicron.bean.FormaPgto;
import gicron.bean.Minutagem;
import gicron.bean.Obj;
import gicron.bean.Titulo;
import gicron.bean.TituloServico;
import gicron.bean.Vencimento;
import giejb.GIModulo;
import giejb.core.GenericRemote;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

/**
 *
 * @author britto
 */
public class Faturamento extends Cron {

    private NativeDB dbVSC;

    /**
     * Argumentos da Rotina
     *
     * @return argumentos da rotina
     */
    @Override
    protected String getArgs() {
        String s = this.getClass() + "\n\tArgs: bss.Faturamento d m y"
                + "\n\td\tDia de fechamento"
                + "\n\tm\tMês de fechamento"
                + "\n\ty\tAno de fechamento";
        return s;
    }

    List<Minutagem> tiposLigacao;

    private List<Minutagem> tiposLigacao() throws Exception {
        String sql = "SELECT null minutos, codigo, grupo FROM ossgi.ligacaotipo";
        List<Minutagem> l = db().getList(sql, Minutagem.class);
        return l;
    }

    /**
     * Executa a Rotina
     *
     * @param args
     * @throws java.lang.Exception
     */
    @Override
    protected void Exec(String[] args) throws Exception {
        GenericRemote r = super.getRemote(GIModulo.GI_CORE, GenericRemote.class);
        try {
            Date dia = new Date();
            Integer d, m, y;
            switch (args.length) {
                case 2:
                    d = Integer.parseInt(args[1]);
                    m = dia.getMonth();
                    y = dia.getYear();
                    break;
                case 3:
                    d = Integer.parseInt(args[1]);
                    m = Integer.parseInt(args[2]) - 1;
                    y = dia.getYear();
                    break;
                case 4:
                    d = Integer.parseInt(args[1]);
                    m = Integer.parseInt(args[2]) - 1;
                    y = Integer.parseInt(args[3]) - 1900;
                    break;
                default:
                    d = dia.getDate();
                    m = dia.getMonth();
                    y = dia.getYear();
            }
            dia = new Date(y, m, d);
            super.log("Iniciando faturamento de: " + Format.maskData(dia)); // Output para controle de execução.

            // Carrega configurações
            setVSC(); // Carrega as infomações para acesso à VSC (telefonia)
            String historicoTaxa = r.getConfig(Config.FINANCEIRO_TAXA).getValor(); // somente para geração da fatura
            Integer descontoId = Integer.parseInt(r.getConfig(Config.FINANCEIRO_DESCONTO_ASSINATURA).getValor());
            String historicoLigacao = r.getConfig(Config.FINANCEIRO_LIGACAO).getValor();
            Integer unidadeLigacaoId = Integer.parseInt(r.getConfig(Config.FINANCEIRO_LIGACAO_UNIDADE).getValor());
            Integer descontoLigacaoId = Integer.parseInt(r.getConfig(Config.FINANCEIRO_DESCONTO_LIGACAO).getValor());
            Integer arquivoTipoId = Integer.parseInt(r.getConfig(Config.ARQUIVO_TIPO_EXTRATO).getValor());
            List<Empresa> empresas = r.findAll(Empresa.class, "nome", Boolean.TRUE);

            // Lista Vencimentos e Formas de pagamento
            List<Vencimento> vencimentos = vencimentos(dia);
            List<FormaPgto> formasPgto = formasPgto();
            tiposLigacao = tiposLigacao();

            for (Vencimento v : vencimentos) { // Para cada vencimento
                super.log("Vencimento: " + v.getId() + " - Fech." + v.getFechamento() + " Venc." + v.getDia());

                for (FormaPgto f : formasPgto) { // Para cada forma de pagamento
                    gicron.bean.Faturamento fat = faturamentoInit(dia, v, f); // Inicializa o faturamento
                    if (!faturado(fat)) { // Não fatura novamente
                        fat = saveFaturamento(fat);
                        Set<Integer> clienteIds = new HashSet();

                        List<Assinatura> assinaturas = assinaturas(v.getId(), f.getId());
                        super.log("\tFormaPgto: " + f.getNome() + "\t(" + assinaturas.size() + ")\tFat:" + fat.getId() + " \tde:" + Format.maskData(fat.getInicio()) + " a " + Format.maskData(fat.getFim()));

                        Integer numero = getNum(f.getContaId(), f.getId()); // Número da fatura

                        for (Assinatura a : assinaturas) { // Percorre as assinaturas
                            //super.log("\t\tAssinatura:" + a.getId() + "/" + a.getClienteId() + "\t" + a.getValidade());
                            if (a.getCtrlNumber() != null) {
                                // Criar título mesmo sem valor
                                super.log("\t\t\tConsultar telefonia: " + a.getCtrlNumber());
                                saveLigacao(fat, a, historicoLigacao, unidadeLigacaoId, descontoLigacaoId, arquivoTipoId); // Salva título com valor das ligações
                            }

                            if (f.getTaxa().floatValue() > 0) {
                                saveTaxa(fat, a, historicoTaxa, f.getTaxa()); // Salva taxa de envio de boleto
                            }
                            for (AssinaturaServico s : a.getServicos()) { // Gera títulos para os serviços prestados
                                saveTitulo(fat, a, s, descontoId);
                            }
                            if (a.getSepararduplicata()) {
                                numero++; // Próximo número do documento
                                saveDuplicata(fat, numero, a.getClienteId(), a.getId(), f); //Gerar Duplicata separado por assinatura
                            } else {
                                clienteIds.add(a.getClienteId()); // adiciona cliente para gerar duplicata com títulos gerados e em aberto
                            }
                            updateValidade(a, fat.getFim()); // Atualiza validade da assinatura
                        }

                        // Gerar Duplicatas
                        System.out.println("Gerando duplicatas");
                        for (Integer clienteId : clienteIds) {
                            numero++; // Próximo número do documento
                            saveDuplicata(fat, numero, clienteId, f);
                            System.out.print(".");
                        }
                        System.out.println("Duplicatas salvas.");
                        // Atualiza número de duplicatas da conta bancária
                        db().exec("UPDATE bssgi.bancoconta SET numduplicata = ? WHERE id = ?", numero, f.getContaId()); // Atualiza número da fatura
                        db().exec("UPDATE bssgi.duplicata d INNER JOIN bssgi.tituloreceber t ON (t.duplicata_id = d.id) SET t.quitado = 1 , d.cancelada = 1 WHERE d.valor = 0.00 AND d.faturamento_id = ?", fat.getId()); // Atualiza títulos e duplicatas zeradas

                        //Atualiza valor do faturamento
                        Obj total = db().getFirst("SELECT SUM(valor) AS obj FROM bssgi.duplicata WHERE faturamento_id = ?", Obj.class, fat.getId());
                        if (total.getObj() != null) {
                            db().exec("UPDATE bssgi.faturamento SET valor = ? WHERE id = ?", total.getObj(), fat.getId());
                        }

                        gerarNotas(fat, empresas);// Gerar Notas Fiscais
                        gerarReciboSVA(fat, empresas);//Gerar Recibo SVA
                    }
                }
            }
            db().close(Boolean.FALSE);
            if (dbVSC != null) {
                dbVSC.close(Boolean.FALSE);
            }
        } catch (Exception e) { // Caso haja algum problema executa rollback
            db().close(Boolean.TRUE);
            throw e;
        }
    }

    /**
     * Criar notas fiscais das faturas geradas
     *
     * @param fat
     * @param empresas
     * @throws Exception
     */
    private void gerarNotas(gicron.bean.Faturamento fat, List<Empresa> empresas) throws Exception {
        String sql = "SELECT s.id servicoId, t.id tituloId, a.cfop_id cfopId, f.id fiscalId, f.tipoutilizacao tipo, f.classificacao, c.nome historico, f.aliquota, f.comporbc bc, t.valor, t.desconto "
                + "FROM bssgi.tituloreceber t "
                + "INNER JOIN bssgi.assinaturaservico a ON (a.id = t.assinaturaservico_id) "
                + "INNER JOIN bssgi.unidade u ON (u.id = a.unidade_id) "
                + "INNER JOIN bssgi.servico s ON (s.id = a.servico_id) "
                + "INNER JOIN bssgi.servicoclassificacao c ON (c.id = s.servicoclassificacao_id) "
                + "INNER JOIN bssgi.servicofiscal f ON (f.servicoclassificacao_id = c.id AND u.empresa_id = f.empresa_id) "
                + "WHERE c.id NOT IN (4,6) AND t.duplicata_id = ? AND u.empresa_id = ?"; //servicoclassificao tem que ser diferente de locação de equipamentos/locação de espaço

        List<Duplicata> l = db().getList("SELECT id, cliente_id clienteId FROM bssgi.duplicata WHERE faturamento_id = ?", Duplicata.class, fat.getId());
        for (Duplicata d : l) {
            for (Empresa e : empresas) {
                List<TituloServico> titulos = db().getList(sql, TituloServico.class, d.getId(), e.getId());

                if (!titulos.isEmpty()) { // Gerar pré-notas
                    BigDecimal valor = BigDecimal.ZERO; //Valor da Nota

                    for (TituloServico t : titulos) {
                        valor = valor.add(t.total());
                    }

                    db().exec("INSERT INTO bssgi.notafiscal (processamento,dia,tipo,emitida,cancelada,enviada,duplicata_id,cfop_id,empresa_id,cliente_id,servicofiscal_id,valor,situacao) "
                            + "VALUES (NOW(), NOW(), 'Serviço', 0, 0, 0, ?, ?, ?, ?, ?, ?, ?)",
                            d.getId(), // duplicata_id
                            titulos.get(0).getCfopId(), // cfop_id
                            e.getId(), // empresa_id
                            d.getClienteId(), // cliente_id
                            titulos.get(0).getFiscalId(), // servicofiscal_id
                            valor, // valor
                            "N"); // situacao

                    Obj id = db().getFirst("SELECT LAST_INSERT_ID() AS obj", Obj.class);
                    for (TituloServico t : titulos) { // Salva os itens da nota
                        db().exec("INSERT INTO bssgi.notafiscalitem (notafiscal_id,tituloreceber_id,historico,aliquota,basecalculo,valor,desconto,tipoutilizacao,classificacao,servico_id) "
                                + "VALUES (?,?,?,?,?,?,?,?,?,?)",
                                id.getObj(), // notafiscal_id
                                t.getTituloId(), // tituloreceber_id
                                t.getHistorico(), // historico
                                t.getAliquota(), // aliquota
                                (t.getBc()) ? t.total() : BigDecimal.ZERO, // basecalculo
                                t.getValor(), // valor
                                t.getDesconto(), // desconto
                                t.getTipo(), // tipoutilizacao
                                t.getClassificacao(),// Classificacao
                                t.getServicoId());  // Código do servio
                    }
                }
            }
        }
    }

    /**
     * Criar notas de fatura a partir dos servicos de locacao
     *
     * @param fat
     * @param empresas
     * @throws Exception
     */
    private void gerarReciboSVA(gicron.bean.Faturamento fat, List<Empresa> empresas) throws Exception {
        String sql = "SELECT s.id servicoId, t.id tituloId, a.cfop_id cfopId, f.id fiscalId, f.tipoutilizacao tipo, f.classificacao, s.nome historico, f.aliquota, f.comporbc bc, t.valor, t.desconto "
                   + "FROM bssgi.tituloreceber t "
                   + "INNER JOIN bssgi.assinaturaservico a ON (a.id = t.assinaturaservico_id) "
                   + "INNER JOIN bssgi.unidade u ON (u.id = a.unidade_id) "
                   + "INNER JOIN bssgi.servico s ON (s.id = a.servico_id) "
                   + "INNER JOIN bssgi.servicoclassificacao c ON (c.id = s.servicoclassificacao_id) "
                   + "INNER JOIN bssgi.servicofiscal f ON (f.servicoclassificacao_id = c.id AND u.empresa_id = f.empresa_id) "
                   + "WHERE c.id IN (1,4,6) AND f.modelofiscal = 25 AND t.duplicata_id = ? AND u.empresa_id = ?";

        List<Duplicata> l = db().getList("SELECT id, cliente_id clienteId FROM bssgi.duplicata WHERE faturamento_id = ?", Duplicata.class, fat.getId());
        System.out.println("Gravando notas fatura");
        for (Duplicata d : l) {
            for (Empresa e : empresas) {
                List<TituloServico> titulos = db().getList(sql, TituloServico.class, d.getId(), e.getId());

//                super.log("\t\t\tDuplicata: " + d.getId() + " empresa: " + e.getNome());
//                super.log("\t\t\tTitulos a gerar recibo: " + titulos.size());

                if (!titulos.isEmpty()) { // Gerar pré-notas
                    System.out.print(".");

                    BigDecimal valor = BigDecimal.ZERO; //Valor da Nota

                    for (TituloServico t : titulos) {
                        valor = valor.add(t.total());
                    }

//                    super.log("\t\t\tgravando nota de fatura");

                    Obj num = db().getFirst("SELECT MAX(numero) AS obj FROM bssgi.notafatura WHERE empresa_id =  ?", Obj.class, e.getId());
                    Integer numero = (Integer) num.getObj();
                    if(numero != null)
                        numero++;
                    else
                        numero = 1;

                    db().exec("INSERT INTO bssgi.notafatura (processamento,dataemissao,dataentradasaida,empresa_id,cliente_id,servicofiscal_id,duplicata_id,numero,vencimento,valor,tipo) "
                            + "VALUES (NOW(), NOW(),  NOW(), ?, ?, ?, ?, ?, ?, ?, ?)",
                            e.getId(), // empresa_id
                            d.getClienteId(), // cliente_id
                            titulos.get(0).getFiscalId(), // servicofiscal_id
                            d.getId(), // duplicata_id
                            numero, //numero fatura
                            fat.getVencimento(), //vencimento
                            valor,
                            "Locação"); // valor

//                    super.log("\t\t\tgravando itens");

                    Obj id = db().getFirst("SELECT LAST_INSERT_ID() AS obj", Obj.class);
                    for (TituloServico t : titulos) { // Salva os itens da nota

                        db().exec("INSERT INTO bssgi.notafaturaitem (notafatura_id,tituloreceber_id,servico_id,codigo,descricao,unidade,quantidade,valorunitario,valortotal) "
                                + "VALUES (?,?,?,?,?,?,?,?,?)",
                                id.getObj(), // notafatura_id
                                t.getTituloId(), // tituloreceber_id
                                t.getServicoId(), // servico_id
                                "", // codigo(indefinido)
                                t.getHistorico(), // historico (nome do servico)
                                "UN", // unidade
                                1, // quantidade
                                t.total(), // valor unitario
                                t.total()); // valor total
                    }
                }
            }
        }
        System.out.println("Notas fatura salvas.");
    }

    /**
     * Carrega o último número gerado para duplicatas da conta corrente
     *
     * @param contaId
     * @return
     * @throws Exception
     */
    private Integer getNum(Integer contaId) throws Exception {
        Obj num = db().getFirst("SELECT numduplicata AS obj FROM bssgi.bancoconta WHERE id = ?", Obj.class, contaId);
        return (Integer) num.getObj();
    }

    private Integer getNum(Integer contaId, Integer formaPgtoId) throws Exception {
        Obj max = db().getFirst("SELECT MAX(numero) AS obj FROM bssgi.duplicata WHERE formapgto_id = ?", Obj.class, formaPgtoId);
        Obj num = db().getFirst("SELECT numduplicata AS obj FROM bssgi.bancoconta WHERE id = ?", Obj.class, contaId);

        //Problema com numeros duplicados
        if(max.getObj() != null) {
            if( ((Integer) max.getObj()) > (Integer) num.getObj()) {
                return (Integer) max.getObj();
            }
        }

        return (Integer) num.getObj();
    }

    /**
     * Atualiza a validade da assinatura
     *
     * @param a
     * @param fim
     * @throws Exception
     */
    private void updateValidade(Assinatura a, Date fim) throws Exception {
        if (a.getValidade().before(fim)) {
            a.setValidade((Date) fim.clone());
            a.getValidade().setDate(a.getValidade().getDate() + 1);
            db().exec("UPDATE bssgi.assinatura SET validade = ?, diassuspenso = 0 WHERE id = ?",
                    a.getValidade(),
                    a.getId());
        }
    }

    /**
     * Cria nova duplicata com os títulos do vencimento corrente separado por
     * assinatura
     *
     * @param fat
     * @param numero
     * @param clienteId
     * @param assinaturaId
     * @param f
     * @throws Exception
     */
    private void saveDuplicata(gicron.bean.Faturamento fat, Integer numero, Integer clienteId, Long assinaturaId, FormaPgto f) throws Exception {
        BigDecimal valor = BigDecimal.ZERO; //Valor da Duplicata

        List<Titulo> titulos = titulos(clienteId, assinaturaId, fat.getVencimento()); // Listar Títulos
        for (Titulo t : titulos) {
            valor = valor.add(t.total());
        }

        String sql = "INSERT INTO bssgi.duplicata (formapgto_id,cliente_id,vencimento,valor,numero,valorpago,cancelada,faturamento_id,vencimento_original,processamento,status_remessa) "
                + "VALUES (?, ?, ?, ?, ?, 0.0, 0, ?, ?, NOW(),'NOVA')";

        db().exec(sql,
                f.getId(),
                clienteId,
                fat.getVencimento(),
                valor,
                numero,
                fat.getId(),
                fat.getVencimento());

        Obj id = db().getFirst("SELECT LAST_INSERT_ID() AS obj", Obj.class);
        for (Titulo t : titulos) { // atualiza os títulos da duplicata
            db().exec("UPDATE bssgi.tituloreceber SET duplicata_id = ? WHERE id = ?", id.getObj(), t.getId());
        }
    }

    /**
     * Cria nova duplicata com os títulos do vencimento corrente
     *
     * @param fat
     * @param numero
     * @param clienteId
     * @param f
     * @throws Exception
     */
    private void saveDuplicata(gicron.bean.Faturamento fat, Integer numero, Integer clienteId, FormaPgto f) throws Exception {
        BigDecimal valor = BigDecimal.ZERO; //Valor da Duplicata

        List<Titulo> titulos = titulos(clienteId, fat.getVencimento()); // Listar Títulos

        if(titulos.isEmpty()){ //caso não haja titulos
            return;
        }

        for (Titulo t : titulos) {
            valor = valor.add(t.total());
        }

        String sql = "INSERT INTO bssgi.duplicata (formapgto_id,cliente_id,vencimento,vencimento_original,valor,numero,valorpago,cancelada,faturamento_id,status_remessa) "
                + "VALUES (?, ?, ?, ?, ?, ?, 0.0, 0, ?,'NOVA')";

        db().exec(sql,
                f.getId(),
                clienteId,
                fat.getVencimento(),
                fat.getVencimento(),
                valor,
                numero,
                fat.getId());

        Obj id = db().getFirst("SELECT LAST_INSERT_ID() AS obj", Obj.class);
        for (Titulo t : titulos) { // atualiza os títulos da duplicata
            db().exec("UPDATE bssgi.tituloreceber SET duplicata_id = ? WHERE id = ?", id.getObj(), t.getId());
        }
    }

    /**
     * Lista os títulos não faturados do cliente para o vencimento informado
     *
     * @param clienteId
     * @param vencimento
     * @return
     * @throws Exception
     */
    private List<Titulo> titulos(Integer clienteId, Date vencimento) throws Exception {
        String sql = "SELECT id, assinatura_id assinaturaId, assinaturaservico_id assinaturaServicoId, cliente_id clienteId, "
                + "unidade_id unidadeId, inicio, fim, vencimento, historico, valor, desconto, juros, credito "
                + "FROM bssgi.tituloreceber WHERE cliente_id = ? AND vencimento = ? and duplicata_id IS NULL";
        List<Titulo> l = db().getList(sql, Titulo.class, clienteId, vencimento);
        return l;
    }

    /**
     * Lista os títulos não faturados do cliente para o vencimento informado
     *
     * @param clienteId
     * @param assinaturaId
     * @param vencimento
     * @return
     * @throws Exception
     */
    private List<Titulo> titulos(Integer clienteId, Long assinaturaId, Date vencimento) throws Exception {
        String sql = "SELECT id, assinatura_id assinaturaId, assinaturaservico_id assinaturaServicoId, cliente_id clienteId, "
                + "unidade_id unidadeId, inicio, fim, vencimento, historico, valor, desconto, juros, credito "
                + "FROM bssgi.tituloreceber WHERE cliente_id = ? AND assinatura_id = ? AND vencimento = ? and duplicata_id IS NULL";
        List<Titulo> l = db().getList(sql, Titulo.class, clienteId, assinaturaId, vencimento);
        return l;
    }

    /**
     * Cria nova taxa de boleto
     *
     * @param fat
     * @param a
     * @param historicoTaxa
     * @param valor
     * @throws Exception
     */
    private void saveTaxa(gicron.bean.Faturamento fat, Assinatura a, String historicoTaxa, BigDecimal valor) throws Exception {
        String sql = "INSERT INTO bssgi.tituloreceber (cliente_id, processamento, inicio, vencimento, "
                + "historico, valor, desconto, juros, credito, valorpago, quitado, unidade_id, negociado) VALUES (?, NOW(), ?, ?, ?, ?, 0.0, 0.0, 0.0, 0.0, 0, ?, 0)";
        db().exec(sql,
                a.getClienteId(),
                a.getValidade(),
                fat.getVencimento(),
                historicoTaxa,
                valor,
                a.getUnidadeId());
    }

    public Boolean chamadaMesmoTipo(CallVSC c, Minutagem m){
        if(c.getTipo().equals(m.getCodigo())){
            return true;
        }else{ // Verifica no grupo
            for (Minutagem ligacao : tiposLigacao) {
                if(c.getTipo().equals(ligacao.getCodigo())
                        && m.getCodigo().equals(ligacao.getGrupo())){
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * Cria título e salva o extrato de ligações realizadas
     *
     * @param fat Faturamento corrente
     * @param a Assinatura do cliente
     * @param historicoLigacao
     * @param unidadeLigacaoId Unidade que receberá
     * @param descontoLigacaoId
     * @param arquivoTipoId
     * @throws Exception
     */
    private void saveLigacao(gicron.bean.Faturamento fat, Assinatura a, String historicoLigacao, Integer unidadeLigacaoId, Integer descontoLigacaoId, Integer arquivoTipoId) throws Exception {
        BigDecimal total = BigDecimal.ZERO;
        BigDecimal desc = BigDecimal.ZERO;

        List<CallVSC> l = ligacoes(a, fat);

        if (l != null) {
            for (CallVSC c : l) {
                total = total.add(c.getValor());
            }

            for (Minutagem m : a.getMinutagens()) {
                Integer franquia = 0;
                for (CallVSC c : l) {
                    if (chamadaMesmoTipo(c,m) && franquia <= m.getMinutos()) {
                        franquia += c.getDuracao() / 60;// Divide por 60 por que a Procedure retorna duração em segundos e a franquia é configurada em minutos
                        desc = desc.add(c.getValor());
                        c.setValor(BigDecimal.ZERO); // Zera a ligação dentro da franquia
                    }
                }
            }

            // Salva arquivo CSV
            String csv = "momento,origem,destino,tipo,descricao,duracao,valor,userCode,transactionId\n";
            String nome = (new Date()).getTime() + "-extrato.csv";
            for (CallVSC c : l) {
                //csv += c.getMomento() + "," + c.getOrigem() + "," + c.getDestino() + "," + c.getTipo() + "," + c.getDescricao() + "," + c.getDuracao() + "," + c.getValor() + "," + c.getUserCode() + "," + c.getTransactionId() + "\n";
                csv += c.getMomento() + "," + c.getOrigem() + "," + c.getDestino() + "," + c.getTipo() + "," + c.getDescricao() + "," + c.getDuracao() + "," + c.getValor() + ",00000," + c.getTransactionId() + "\n";
            }
            try (BufferedWriter out = new BufferedWriter(new FileWriter(ClienteArquivo.PATH + nome))) {
                out.write(csv);
            }

            String sql = "INSERT INTO bssgi.clientearqv (cliente_id,tipoarqv_id,momento,nome,extensao,arquivo,ativo) VALUES (?,?,NOW(),?,'text/csv',?,1)";
            db().exec(sql,
                    a.getClienteId(),
                    arquivoTipoId,
                    Format.maskData(fat.getInicio()) + " - " + Format.maskData(fat.getFim()),
                    nome);

            AssinaturaServico s = null;
            for (AssinaturaServico as : a.getServicos()) { // Gera títulos para os serviços prestados
                if (as.getTipo().equals(gi.comercial.Servico.TIPO_TELEFONE)) {
                    s = as;
                    break;
                }
            }

            // Salva o título
            sql = "INSERT INTO bssgi.tituloreceber (assinatura_id, assinaturaservico_id, cliente_id, processamento, inicio, fim, vencimento, "
                    + "historico, valor, desconto, juros, credito, valorpago, quitado, unidade_id, negociado) VALUES (?,?,?, NOW(), ?, ?, ?, ?, ?, ?, 0.0, 0.0, 0.0, 0, ?, 0)";
            db().exec(sql,
                    a.getId(),
                    (s == null) ? null : s.getId(),
                    a.getClienteId(),
                    a.getValidade(),
                    fat.getFim(),
                    fat.getVencimento(),
                    historicoLigacao,
                    total,
                    desc,
                    unidadeLigacaoId);

            // Salva o desconto
            if (desc.floatValue() > 0) {
                Obj id = db().getFirst("SELECT LAST_INSERT_ID() AS obj", Obj.class);
                db().exec("INSERT INTO bssgi.desconto (descontotipo_id,tituloreceber_id,momento,valor,excluido) VALUES (?,?,NOW(),?,0)",
                        descontoLigacaoId,
                        id.getObj(),
                        desc);
            }
        }
    }

    /**
     * Salva novo título
     *
     * @param fat
     * @param a
     * @param s
     * @param descontoId
     * @throws Exception
     */
    private void saveTitulo(gicron.bean.Faturamento fat, Assinatura a, AssinaturaServico s, Integer descontoId) throws Exception {
        String sql = "INSERT INTO bssgi.tituloreceber (assinaturaservico_id, cliente_id, processamento, inicio, fim, vencimento, "
                + "historico, valor, desconto, juros, credito, valorpago, quitado, unidade_id, assinatura_id, negociado) VALUES (?, ?, NOW(), ?, ?, ?, ?, ?, ?, 0.0, 0.0, 0.0, 0, ?, ?, 0)";

//        Date fim = fat.getFim();
        Integer diasSuspenso = a.getDiassuspenso();
        if(a.getSuspensao() != null){
                 if(a.getValidade().compareTo(a.getSuspensao()) < 0){
                     diasSuspenso += DateUtil.dias(a.getSuspensao(), fat.getFim());
                 }else{
                     return; // Não fatura que está suspenso a muitos dias
                 }
        }

        BigDecimal desconto = BigDecimal.ZERO;
        if (s.getDescontoLimite() != null && s.getDescontoLimite().before(fat.getVencimento())) {
            db().exec("UPDATE bssgi.assinaturaservico SET desconto = 0, descontolimite = NULL WHERE id = ?", s.getId());
        } else {
            desconto = gi.financeiro.Faturamento.proporcionalValidade(a.getValidade(), fat.getInicio(), fat.getFim(), s.getDesconto());
            desconto = desconto.subtract(gi.financeiro.Faturamento.proporcional(diasSuspenso, fat.getInicio(), fat.getFim(), s.getDesconto()));
            desconto = (desconto.floatValue() < 0) ? BigDecimal.ZERO : desconto;
        }

        BigDecimal valor = gi.financeiro.Faturamento.proporcionalValidade(a.getValidade(), fat.getInicio(), fat.getFim(), s.getValor());

//        super.log("ASSINATURA " + a.getId());
//        super.log("DIAS SUSPENSO " + a.getDiassuspenso());
//        super.log("VALIDADE " + a.getValidade());
//        super.log("INICIO FAT " + fat.getInicio());
//        super.log("FIM FAT " + fat.getFim());

        db().exec(sql,
                s.getId(),
                a.getClienteId(),
                a.getValidade(),
                fat.getFim(),
                fat.getVencimento(),
                s.getNome(),
                valor,
                desconto,
                s.getUnidadeId(),
                a.getId());
        Obj id = db().getFirst("SELECT LAST_INSERT_ID() AS obj", Obj.class);

        if (desconto.floatValue() > 0) {
            db().exec("INSERT INTO bssgi.desconto (descontotipo_id,tituloreceber_id,momento,valor,excluido) VALUES (?,?,NOW(),?,0)",
                    descontoId,
                    id.getObj(),
                    desconto);
        }

        // Desconto de dias suspensos
        if(diasSuspenso > 0){
            BigDecimal desc = gi.financeiro.Faturamento.proporcional(diasSuspenso, fat.getInicio(), fat.getFim(), s.getValor());
            db().exec("INSERT INTO bssgi.desconto (descontotipo_id,tituloreceber_id,momento,valor,excluido) VALUES (?,?,NOW(),?,0)",
                    4, // Desconto Suspensão - (DÉBITO/TEMPORÁRIA)
                    id.getObj(),
                    desc);

            db().exec("UPDATE bssgi.tituloreceber SET desconto = desconto + ? WHERE id = ?", desc, id.getObj());
        }

        // Analisa o desconto agendado
        sql = "SELECT id, descontotipo_id descontoTipoId, valor, usuario_id usuarioId FROM bssgi.descontoagendado WHERE assinaturaservico_id = ? AND vencimento = ? AND excluido = 0 AND processado = 0";
        List<DescontoAgendado> l = db().getList(sql, DescontoAgendado.class, s.getId(), fat.getVencimento());
        if (!l.isEmpty()) {
            BigDecimal valorTitulo = valor.add(desconto.negate());

            List<DescontoAgendado> lproc = new LinkedList<>();
            for (DescontoAgendado d : l) {
                if (valorTitulo.add(d.getValor().negate()).floatValue() < BigDecimal.ZERO.floatValue()) { // agenda para o próximo faturamento
                    Date vencimentoProximo = (Date) fat.getVencimento().clone();
                    vencimentoProximo.setMonth(vencimentoProximo.getMonth() + 1);
                    db().exec("UPDATE bssgi.descontoagendado SET vencimento = ? WHERE id = ?", vencimentoProximo, d.getId());
                } else { // Se o valor for maior que zero processa o desconto agendado.
                    lproc.add(d);
                    valorTitulo = valorTitulo.add(d.getValor().negate());
                }
            }
            for (DescontoAgendado d : lproc) {
                db().exec("INSERT INTO bssgi.desconto (descontotipo_id,tituloreceber_id,momento,valor,excluido,usuario_id) VALUES (?,?,NOW(),?,0,?)",
                        d.getDescontoTipoId(),
                        id.getObj(),
                        d.getValor(),
                        d.getUsuarioId());
                db().exec("UPDATE bssgi.tituloreceber SET desconto = desconto + ? WHERE id = ?", d.getValor(), id.getObj());
                db().exec("UPDATE bssgi.descontoagendado SET processado = 1 WHERE id = ?", d.getId());
            }
        }

    }

    /**
     * Salva o novo faturamento
     *
     * @param fat Faturamento inicializado
     * @return
     * @throws Exception
     */
    private gicron.bean.Faturamento saveFaturamento(gicron.bean.Faturamento fat) throws Exception {
        String sql = "INSERT INTO bssgi.faturamento (vencimento_id,formapgto_id,processamento,inicio,fim,valor) "
                + "VALUES (?, ?, NOW(), ?, ?, 0.0)";
        db().exec(sql,
                fat.getVencimentoId(),
                fat.getFormaPgtoId(),
                fat.getInicio(),
                fat.getFim());

        Obj id = db().getFirst("SELECT LAST_INSERT_ID() AS obj", Obj.class);
        fat.setId(((BigInteger) id.getObj()).intValue());
        return fat;
    }

    /**
     * Inicializa o faturamento para o vencimento e forma de pagamento
     *
     * @param dia Dia do fechamento
     * @param v Vencimento
     * @param f Forma de pagamento
     * @return Novo faturamento
     * @throws Exception
     */
    private gicron.bean.Faturamento faturamentoInit(Date dia, Vencimento v, FormaPgto f) throws Exception {
        gicron.bean.Faturamento fat = new gicron.bean.Faturamento();
        fat.setVencimento(new Date(dia.getYear(), dia.getMonth(), v.getDia()));

        Date fim = (Date) fat.getVencimento().clone();
        fim.setDate(v.getFechamento());
        if (v.getDia() < v.getFechamento()) { // fechamento no mês anterior
            fat.getVencimento().setMonth(fat.getVencimento().getMonth() + 1);
        }
        Date inicio = (Date) fim.clone();
        inicio.setMonth(inicio.getMonth() - 1);
        fim.setDate(v.getFechamento() - 1); // fatura um mês até ontem

        fat.setInicio(inicio);
        fat.setFim(fim);
        fat.setFormaPgtoId(f.getId());
        fat.setVencimentoId(v.getId());

        return fat;
    }

    /**
     * Verifica se existe outro faturamento com os dados criados
     *
     * @param fat Fatuamento criado
     * @return
     * @throws Exception
     */
    private Boolean faturado(gicron.bean.Faturamento fat) throws Exception {
        String sql = "SELECT id, vencimento_id vencimentoId, formapgto_id formaPgtoId, inicio, fim, valor, null vencimento FROM bssgi.faturamento WHERE vencimento_id = ? AND formapgto_id = ? AND inicio = ? AND fim = ?";
        gicron.bean.Faturamento f = db().getFirst(sql, gicron.bean.Faturamento.class, fat.getVencimentoId(), fat.getFormaPgtoId(), fat.getInicio(), fat.getFim());
        return f != null;
    }

    /**
     * Lista vencimentos no dia de fechamento informado
     *
     * @param dia
     * @return
     * @throws Exception
     */
    private List<Vencimento> vencimentos(Date dia) throws Exception {
        List<Obj> ids = db().getList("SELECT DISTINCT(a.vencimento_id) obj FROM bssgi.assinatura a INNER JOIN bssgi.assinaturasituacao s ON (a.assinaturasituacao_id = s.id) WHERE !s.cancelado", Obj.class);

        String sql = "SELECT * FROM bssgi.vencimento WHERE fechamento = ? AND id in (?)";
        List<Vencimento> l = db().getList(sql, Vencimento.class, dia.getDate(), ids);
        if (l.isEmpty()) {
            System.err.println("FATURAMENTO: Nenhum fechamento para: " + Format.maskData(dia));
            System.exit(1);
        }
        return l;
    }

    /**
     * Lista formas de pagamento disponíveis no sistema
     *
     * @return
     * @throws Exception
     */
    private List<FormaPgto> formasPgto() throws Exception {
        String sql = "SELECT f.id, f.nome, b.bancoconta_id contaId, b.taxa FROM bssgi.formapgto f "
                + "INNER JOIN bssgi.formapgtoboleto b ON (f.id = b.id) "; // Boletos
        List<FormaPgto> l = db().getList(sql, FormaPgto.class);

        sql = "SELECT f.id, f.nome, b.bancoconta_id contaId, 0.0 taxa FROM bssgi.formapgto f "
                + "INNER JOIN bssgi.formapgtodebito b ON (f.id = b.id) "; // Débitos
        l.addAll(db().getList(sql, FormaPgto.class));

        if (l.isEmpty()) {
            System.err.println("FATURAMENTO: Nenhuma forma de pagamento configurada");
            System.exit(1);
        }
        return l;
    }

    /**
     * Lista as assinaturas s serviços com o vencimento e forma de pagamento
     * informados
     *
     * @param vencimentoId id do vencimento
     * @param formaPgtoId id da forma de pagamento
     * @return
     * @throws Exception
     */
    private List<Assinatura> assinaturas(Integer vencimentoId, Integer formaPgtoId) throws Exception {
        String sql = "SELECT null minutagens, null servicos, a.id, a.pacote_id pacoteId, a.cliente_id clienteId, a.validade, a.suspensao, a.diassuspenso, a.unidade_id unidadeId, a.ctrl_number ctrlNumber, t.separarduplicata "
                + "FROM bssgi.assinatura a INNER JOIN bssgi.cliente c ON (c.id = a.cliente_id) INNER JOIN bssgi.clientetipo t ON (t.id = c.clientetipo_id) WHERE a.cancelamento IS NULL AND a.vencimento_id = ? AND a.formapgto_id = ?";
        List<Assinatura> l = db().getList(sql, Assinatura.class, vencimentoId, formaPgtoId);

        sql = "SELECT a.id, a.unidade_id unidadeId, a.servico_id servicoId, a.valor, a.desconto, a.descontolimite descontoLimite, s.codigo, s.nome, s.tipo FROM bssgi.assinaturaservico a "
                + "INNER JOIN bssgi.servico s ON (s.id = a.servico_id) WHERE a.assinatura_id = ?";
        for (Assinatura a : l) {
            a.setServicos(db().getList(sql, AssinaturaServico.class, a.getId()));
        }
        sql = "SELECT m.minutos, t.codigo, null grupo FROM bssgi.minutagem m "
                + "INNER JOIN ossgi.ligacaotipo t ON (t.id = m.ligacaotipo_id) WHERE m.pacote_id = ?";
        for (Assinatura a : l) {
            a.setMinutagens(db().getList(sql, Minutagem.class, a.getPacoteId()));
        }
        return l;
    }

    /**
     * Inicializa as informações de acesso à VSC
     *
     * @throws SQLServerException
     * @throws Exception
     */
    private void setVSC() throws SQLServerException, Exception {
//        try {
            GenericRemote r = super.getRemote(GIModulo.GI_CORE, GenericRemote.class);
            Config url = r.getConfig(Config.VSC_DB_URL);
            Config port = r.getConfig(Config.VSC_DB_PORT);
            Config user = r.getConfig(Config.VSC_DB_USER);
            Config pass = r.getConfig(Config.VSC_DB_PASS);
            Config base = r.getConfig(Config.VSC_DB_BASE);
            Config driver = r.getConfig(Config.VSC_DB_DRIVER);
            dbVSC = new NativeDB(url.getValor(), user.getValor(), pass.getValor(), base.getValor(), Integer.parseInt(port.getValor()), driver.getValor());
//            dbVSC = new NativeDB(url.getValor(), user.getValor(), pass.getValor(), "VSCDBCDRs", Integer.parseInt(port.getValor()), driver.getValor());
//        } catch (Exception ex) {
//            System.err.println("Não foi possível conectar à VSC: " + ex);
//        }
    }

    /**
     * Carrega as ligações a partir da storage procedure no período a ser
     * faturado
     *
     * @param a Assinatura do cliente
     * @param fat Faturamento corrente
     * @return
     * @throws Exception
     */
    private List<CallVSC> ligacoes(Assinatura a, gicron.bean.Faturamento fat) throws Exception {
        if (dbVSC != null) {
//            String key = UserInfo.getUserCode(a.getClienteId(), a.getId());
            String from = (fat.getInicio().getYear() + 1900) + "-" + (fat.getInicio().getMonth() + 1) + "-" + fat.getInicio().getDate() + " 00:00:00";
            String to = (fat.getFim().getYear() + 1900) + "-" + (fat.getFim().getMonth() + 1) + "-" + fat.getFim().getDate() + " 23:59:59";
            List<CallVSC> l = dbVSC.getListProc(ConfigFile.get("sql.Calls.proc"), CallVSC.class, from, to, a.getCtrlNumber());
            Collections.sort(l, CallVSC.COMPARATOR_MOMENTO); // Importante ordenar por tipo
            Collections.sort(l, CallVSC.COMPARATOR_TIPO);
            return l;
        }
        return null;
    }

}