/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package br.com.ddns.grupotsergio;

import java.sql.*;

/**
 *
 * @author Edson
 */
public class lancaccGS {
    int cc_idcc = 0;
    int chave = 0;
    String dtlanca = null;
    String debito = null;
    String credito = null;
    String historico = null;
    String doc = null;
    String auditoria = null;
    int filiais_idfiliais = 0;

    private static PreparedStatement stm = null;
    private static ResultSet rs = null;
    private static Statement stmt;

    public lancaccGS(){
    }

    public String getAuditoria() {
        return auditoria;
    }

    public void setAuditoria(String auditoria) {
        this.auditoria = auditoria;
    }

    public int getCc_idcc() {
        return cc_idcc;
    }

    public void setCc_idcc(int cc_idcc) {
        this.cc_idcc = cc_idcc;
    }

    public int getChave() {
        return chave;
    }

    public void setChave(int chave) {
        this.chave = chave;
    }

    public String getCredito() {
        return credito;
    }

    public void setCredito(String credito) {
        this.credito = credito;
    }

    public String getDebito() {
        return debito;
    }

    public void setDebito(String debito) {
        this.debito = debito;
    }

    public String getDoc() {
        return doc;
    }

    public void setDoc(String doc) {
        this.doc = doc;
    }

    public String getDtlanca() {
        return dtlanca;
    }

    public void setDtlanca(String dtlanca) {
        this.dtlanca = dtlanca;
    }

    public String getHistorico() {
        return historico;
    }

    public void setHistorico(String historico) {
        this.historico = historico;
    }

    public int getFiliais_idfiliais() {
        return filiais_idfiliais;
    }

    public void setFiliais_idfiliais(int filiais_idfiliais) {
        this.filiais_idfiliais = filiais_idfiliais;
    }

    public static boolean inserirLcc (lancaccGS cttos){
        try{
            stm = Conexao.conectar().prepareStatement("insert into lancacc("
                    + "idlancacc,"
                    + "dtlanca,"
                    + "debito,"
                    + "credito,"
                    + "historico,"
                    + "doc,"
                    + "auditoria,"
                    + "cc_idcc,"
                    + "filiais_idfiliais) "
                    + "values (?,?,?,?,?,?,?,?,?)");
            stm.setInt(1, cttos.getChave());
            stm.setString(2, cttos.getDtlanca());
            stm.setString(3, cttos.getDebito());
            stm.setString(4, cttos.getCredito());
            stm.setString(5, cttos.getHistorico());
            stm.setString(6, cttos.getDoc());
            stm.setString(7, cttos.getAuditoria());
            stm.setInt(8, cttos.getCc_idcc());
            stm.setInt(9, cttos.getFiliais_idfiliais());
            stm.executeUpdate();
            stm.close();
            return true;
        }catch(Exception e){
            System.out.println("Erro ao inserir");
            return false;
        }
    }

    public static boolean altCCdtlan(lancaccGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacc set "
                    + "dtlanca=? "
                    + "where idlancacc = ? "
                    + "and cc_idcc=? "
                    + "and filiais_idfiliais = ?");
            stm.setString(1, cttos.getDtlanca());
            stm.setInt(2, cttos.getChave());
            stm.setInt(3, cttos.getCc_idcc());
            stm.setInt(4, cttos.getFiliais_idfiliais());
            int executeUpdate = stm.executeUpdate();

            if(executeUpdate > 0)
                testa = true;
            else
                testa = false;
        }catch(Exception e){
            System.out.println("Erro ao alterar");
        }
        return testa;
    }

    public static boolean altCCdeb(lancaccGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacc set "
                    + "debito=? "
                    + "where idlancacc = ? "
                    + "and cc_idcc=? "
                    + "and filiais_idfiliais = ?");
            stm.setString(1, cttos.getDebito());
            stm.setInt(2, cttos.getChave());
            stm.setInt(3, cttos.getCc_idcc());
            stm.setInt(4, cttos.getFiliais_idfiliais());
            int executeUpdate = stm.executeUpdate();

            if(executeUpdate > 0)
                testa = true;
            else
                testa = false;
        }catch(Exception e){
            System.out.println("Erro ao alterar");
        }
        return testa;
    }

    public static boolean altCCcre(lancaccGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacc set "
                    + "credito=? "
                    + "where idlancacc = ? "
                    + "and cc_idcc=? "
                    + "and filiais_idfiliais = ?");
            stm.setString(1, cttos.getCredito());
            stm.setInt(2, cttos.getChave());
            stm.setInt(3, cttos.getCc_idcc());
            stm.setInt(4, cttos.getFiliais_idfiliais());
            int executeUpdate = stm.executeUpdate();

            if(executeUpdate > 0)
                testa = true;
            else
                testa = false;
        }catch(Exception e){
            System.out.println("Erro ao alterar");
        }
        return testa;
    }

    public static boolean altCChist(lancaccGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacc set "
                    + "historico=? "
                    + "where idlancacc = ? "
                    + "and cc_idcc=? "
                    + "and filiais_idfiliais = ?");
            stm.setString(1, cttos.getHistorico());
            stm.setInt(2, cttos.getChave());
            stm.setInt(3, cttos.getCc_idcc());
            stm.setInt(4, cttos.getFiliais_idfiliais());
            int executeUpdate = stm.executeUpdate();

            if(executeUpdate > 0)
                testa = true;
            else
                testa = false;
        }catch(Exception e){
            System.out.println("Erro ao alterar");
        }
        return testa;
    }

    public static boolean altCCdoc(lancaccGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacc set "
                    + "doc=? "
                    + "where idlancacc = ? "
                    + "and cc_idcc=? "
                    + "and filiais_idfiliais = ?");
            stm.setString(1, cttos.getDoc());
            stm.setInt(2, cttos.getChave());
            stm.setInt(3, cttos.getCc_idcc());
            stm.setInt(4, cttos.getFiliais_idfiliais());
            int executeUpdate = stm.executeUpdate();

            if(executeUpdate > 0)
                testa = true;
            else
                testa = false;
        }catch(Exception e){
            System.out.println("Erro ao alterar");
        }
        return testa;
    }

    public static boolean altCCaud(lancaccGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacc set "
                    + "auditoria=? "
                    + "where idlancacc = ? "
                    + "and cc_idcc=? "
                    + "and filiais_idfiliais = ?");
            stm.setString(1, cttos.getAuditoria());
            stm.setInt(2, cttos.getChave());
            stm.setInt(3, cttos.getCc_idcc());
            stm.setInt(4, cttos.getFiliais_idfiliais());
            int executeUpdate = stm.executeUpdate();

            if(executeUpdate > 0)
                testa = true;
            else
                testa = false;
        }catch(Exception e){
            System.out.println("Erro ao alterar");
        }
        return testa;
    }

    public static boolean excluiLcc(lancaccGS cttos){
        boolean testa = false;
        try {
            stm = Conexao.conectar().prepareStatement("delete from lancacc "
                    + "where idlancacc = ? "
                    + "and cc_idcc = ?");
            stm.setInt(1, cttos.getChave());
            stm.setInt(2, cttos.getCc_idcc());
            int executeUpdate = stm.executeUpdate();

            if(executeUpdate > 0)
                testa = true;
            else
                testa = false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return testa;
    }

    public void selLcc(String Id, String Idc, String Idf) {
        try {
            lancaccGS scid = null;
            stmt = Conexao.conectar().createStatement();
            rs = stmt.executeQuery("Select * from lancacc "
            + "Where idlancacc ='"+Id+"' "
            + "and cc_idcc = '"+Idc+"' "
            + "and filiais_idfiliais = '"+Idf+"'");
            if (rs.next()) {
                scid = new lancaccGS();
                scid.setChave(rs.getInt("chave"));
                scid.setAuditoria(rs.getString("auditoria"));
                scid.setDtlanca(rs.getString("dtlanca"));
                scid.setDoc(rs.getString("doc"));
                scid.setHistorico(rs.getString("historico"));
                scid.setCc_idcc(rs.getInt("cc_idcc"));
                scid.setCredito(rs.getString("credito"));
                scid.setDebito(rs.getString("debito"));
            }
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static int getAutoInc(String Idc) {
        int Codigo = 0;
        try {
            PreparedStatement pstmt = Conexao.conectar().prepareStatement(
                    "Select max(idlancacc) From lancacc "
                    + "where cc_idcc = '"+Idc+"'");
            ResultSet rss = pstmt.executeQuery();
            rss.next();
            Codigo = rss.getInt(1) + 1;
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return Codigo;
    }

    public void listaLcc(String Id){
        try{
            stmt = Conexao.conectar().createStatement();
            rs = stmt.executeQuery("select * from lancacc "
            + "where cc_idcc = '"+Id+"' "
            + "order by dtlanca,idlancacc");
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void listaLccData(String Id, String Idd){
        try{
            stmt = Conexao.conectar().createStatement();
            rs = stmt.executeQuery("select * from lancacc "
            + "where cc_idcc = '"+Id+"' "
            + "and dtlanca = '"+Idd+"' "
            + "order by dtlanca,idlancacc");
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void listaLccRelat(String Id, String dataini, String datafim){
        try{
            stmt = Conexao.conectar().createStatement();
            stm = Conexao.conectar().prepareStatement("select l.*, c.banco, "
            + "c.nrobanco, c.agencia, c.nroagencia, c.ctacorrente "
            + "from ct_dados.lancacc l, ct_dados.cc c "
            + "where l.cc_idcc = c.idcc "
            + "and l.cc_idcc = ? "
            + "and l.dtlanca >= ? "
            + "and l.dtlanca <= ? order by dtlanca,idlancacc;");
            stm.setInt(1, Integer.parseInt(Id));
            stm.setString(2, dataini);
            stm.setString(3, datafim);
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void LccSaliniRelat(String Id, String dtini, String Idf){
        try{
            stmt = Conexao.conectar().createStatement();
            rs = stmt.executeQuery("select sum(credito)-sum(debito) as total "
                    + "from lancacc "
                    + "where cc_idcc = '"+Id+"' "
                    + "and dtlanca < '"+dtini+"' "
                    + "and filiais_idfiliais = '"+Idf+"';");
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void ImprimeRelat(){
        try{
            stmt = Conexao.conectar().createStatement();
            rs = stmt.executeQuery("select * from extrato;");
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public ResultSet getResultado(){
        return rs;
    }
}