/*
 * 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 lancacontabGS {
    int chave = 0;
    String datalan = null;
    int idpcdeb = 0;
    int idpccre = 0;
    String vlrdeb = null;
    String vlrcre = null;
    String historico = null;
    String doc = null;
    String auditoria = null;
    String lanca_exc = null;
    int filiais_idfiliais = 0;

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

    public lancacontabGS(){
    }

    public String getAuditoria() {
        return auditoria;
    }

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

    public int getChave() {
        return chave;
    }

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

    public String getDatalan() {
        return datalan;
    }

    public void setDatalan(String datalan) {
        this.datalan = datalan;
    }

    public String getDoc() {
        return doc;
    }

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

    public int getFiliais_idfiliais() {
        return filiais_idfiliais;
    }

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

    public String getHistorico() {
        return historico;
    }

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

    public int getIdpccre() {
        return idpccre;
    }

    public void setIdpccre(int idpccre) {
        this.idpccre = idpccre;
    }

    public int getIdpcdeb() {
        return idpcdeb;
    }

    public void setIdpcdeb(int idpcdeb) {
        this.idpcdeb = idpcdeb;
    }

    public String getLanca_exc() {
        return lanca_exc;
    }

    public void setLanca_exc(String lanca_exc) {
        this.lanca_exc = lanca_exc;
    }

    public String getVlrcre() {
        return vlrcre;
    }

    public void setVlrcre(String vlrcre) {
        this.vlrcre = vlrcre;
    }

    public String getVlrdeb() {
        return vlrdeb;
    }

    public void setVlrdeb(String vlrdeb) {
        this.vlrdeb = vlrdeb;
    }

    public static boolean inserirLconta (lancacontabGS cttos){
        try{
            stm = Conexao.conectar().prepareStatement("insert into lancacontab("
                    + "idlancacontab,"
                    + "datalan,"
                    + "idpcdeb,"
                    + "idpccre,"
                    + "vlrdeb,"
                    + "vlrcre,"
                    + "historico,"
                    + "doc,"
                    + "auditoria,"
                    + "lanca_exc,"
                    + "filiais_idfiliais) "
                    + "values (?,?,?,?,?,?,?,?,?,?,?)");
            stm.setInt(1, cttos.getChave());
            stm.setString(2, cttos.getDatalan());
            stm.setInt(3, cttos.getIdpcdeb());
            stm.setInt(4, cttos.getIdpccre());
            stm.setString(5, cttos.getVlrdeb());
            stm.setString(6, cttos.getVlrcre());
            stm.setString(7, cttos.getHistorico());
            stm.setString(8, cttos.getDoc());
            stm.setString(9, cttos.getAuditoria());
            stm.setString(10, cttos.getLanca_exc());
            stm.setInt(11, cttos.getFiliais_idfiliais());
            stm.executeUpdate();
            stm.close();
            return true;
        }catch(Exception e){
            System.out.println("Erro ao inserir");
            return false;
        }
    }

    public static boolean altLcontaData(lancacontabGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacontab set "
                    + "datalan=? "
                    + "where filiais_idfiliais=? and idlancacontab = ?");
            stm.setString(1, cttos.getDatalan());
            stm.setInt(2, cttos.getFiliais_idfiliais());
            stm.setInt(3, cttos.getChave());
            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 altLcontaCtaDeb(lancacontabGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacontab set "
                    + "idpcdeb=? "
                    + "where filiais_idfiliais=? and idlancacontab = ?");
            stm.setInt(1, cttos.getIdpcdeb());
            stm.setInt(2, cttos.getFiliais_idfiliais());
            stm.setInt(3, cttos.getChave());
            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 altLcontaCtaCre(lancacontabGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacontab set "
                    + "idpccre=? "
                    + "where filiais_idfiliais=? and idlancacontab = ?");
            stm.setInt(1, cttos.getIdpccre());
            stm.setInt(2, cttos.getFiliais_idfiliais());
            stm.setInt(3, cttos.getChave());
            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 altLcontaVlrDeb(lancacontabGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacontab set "
                    + "vlrdeb=? "
                    + "where filiais_idfiliais=? and idlancacontab = ?");
            stm.setString(1, cttos.getVlrdeb());
            stm.setInt(2, cttos.getFiliais_idfiliais());
            stm.setInt(3, cttos.getChave());
            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 altLcontaVlrCre(lancacontabGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacontab set "
                    + "vlrcre=? "
                    + "where filiais_idfiliais=? and idlancacontab = ?");
            stm.setString(1, cttos.getVlrcre());
            stm.setInt(2, cttos.getFiliais_idfiliais());
            stm.setInt(3, cttos.getChave());
            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 altLcontaHist(lancacontabGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacontab set "
                    + "historico=? "
                    + "where filiais_idfiliais=? and idlancacontab = ?");
            stm.setString(1, cttos.getHistorico());
            stm.setInt(2, cttos.getFiliais_idfiliais());
            stm.setInt(3, cttos.getChave());
            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 altLcontaDoc(lancacontabGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacontab set "
                    + "doc=? "
                    + "where filiais_idfiliais=? and idlancacontab = ?");
            stm.setString(1, cttos.getDoc());
            stm.setString(2, cttos.getAuditoria());
            stm.setString(3, cttos.getLanca_exc());
            stm.setInt(10, cttos.getFiliais_idfiliais());
            stm.setInt(11, cttos.getChave());
            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 altLcontaAud(lancacontabGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacontab set "
                    + "auditoria=? "
                    + "where filiais_idfiliais=? and idlancacontab = ?");
            stm.setString(1, cttos.getAuditoria());
            stm.setInt(2, cttos.getFiliais_idfiliais());
            stm.setInt(3, cttos.getChave());
            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 altLcontaExLan(lancacontabGS cttos){
        boolean testa = false;
        try{
            stm = Conexao.conectar().prepareStatement("update lancacontab set "
                    + "lanca_exc=? "
                    + "where filiais_idfiliais=? and idlancacontab = ?");
            stm.setString(1, cttos.getLanca_exc());
            stm.setInt(2, cttos.getFiliais_idfiliais());
            stm.setInt(3, cttos.getChave());
            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 excluiLconta(lancacontabGS cttos){
        boolean testa = false;
        try {
            stm = Conexao.conectar().prepareStatement("delete from lancacontab "
                    + "where filiais_idfiliais=? and idlancacontab = ?");
            stm.setInt(1, cttos.getFiliais_idfiliais());
            stm.setInt(2, cttos.getChave());
            int executeUpdate = stm.executeUpdate();

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

    public void selLconta(String Id, String Idf) {
        try {
            lancacontabGS scid = null;
            stmt = Conexao.conectar().createStatement();
            rs = stmt.executeQuery("Select * from lancacontab "
            + " Where idlancacontab ='"+Id+"' "
            + " and filiais_idfiliais = '"+Idf+"'");
            if (rs.next()) {
                scid = new lancacontabGS();
                scid.setChave(rs.getInt("chave"));
                scid.setAuditoria(rs.getString("auditoria"));
                scid.setDatalan(rs.getString("datalan"));
                scid.setDoc(rs.getString("doc"));
                scid.setFiliais_idfiliais(rs.getInt("filiais_idfiliais"));
                scid.setHistorico(rs.getString("historico"));
                scid.setIdpccre(rs.getInt("idpccre"));
                scid.setIdpcdeb(rs.getInt("idpcdeb"));
                scid.setLanca_exc(rs.getString("lanca_exc"));
                scid.setVlrcre(rs.getString("vlrcre"));
                scid.setVlrdeb(rs.getString("vlrdeb"));
            }
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

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

    public void listaLconta(String Id){
        try{
            stmt = Conexao.conectar().createStatement();
            rs = stmt.executeQuery("select * from lancacontab "
            + "where filiais_idfiliais = '"+Id+"' "
            + "order by datalan,idlancacontab");
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void listaLcontaData(String Id, String datalan){
        try{
            stmt = Conexao.conectar().createStatement();
            rs = stmt.executeQuery("select * from lancacontab "
            + "where filiais_idfiliais = '"+Id+"' "
            + "and datalan = '"+datalan+"' "
            + "order by idlancacontab");
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void listaRazaoDeb(String Id, String Idp, String dti, String dtf){
        try{
            stm = Conexao.conectar().prepareStatement("select p.codigo, p.descricao, datalan, "
                    + "idlancacontab, doc, historico, vlrdeb, razao_social "
                    + "from lancacontab l, filiais f, pcontas p "
                    + "where l.filiais_idfiliais = f.idfiliais "
                    + "and p.filiais_idfiliais = f.idfiliais "
                    + "and p.idpcontas=l.idpcdeb "
                    + "and l.filiais_idfiliais = ? "
                    + "and idpcdeb = ? "
                    + "and datalan >= ? "
                    + "and datalan <= ? "
                    + "and vlrdeb > 0 "
                    + "order by datalan, idlancacontab;");
            stm.setInt(1, Integer.parseInt(Id));
            stm.setInt(2, Integer.parseInt(Idp));
            stm.setString(3, dti);
            stm.setString(4, dtf);
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void listaRazaoCre(String Id, String Idp, String dti, String dtf){
        try{
            stm = Conexao.conectar().prepareStatement("select p.codigo, p.descricao, datalan, "
                    + "idlancacontab, doc, historico, vlrcre, razao_social "
                    + "from lancacontab l, filiais f, pcontas p "
                    + "where l.filiais_idfiliais = f.idfiliais "
                    + "and p.filiais_idfiliais = f.idfiliais "
                    + "and p.idpcontas=l.idpccre "
                    + "and l.filiais_idfiliais = ? "
                    + "and idpccre = ? "
                    + "and datalan >= ? "
                    + "and datalan <= ? "
                    + "and vlrdeb > 0 "
                    + "order by datalan, idlancacontab;");
            stm.setInt(1, Integer.parseInt(Id));
            stm.setInt(2, Integer.parseInt(Idp));
            stm.setString(3, dti);
            stm.setString(4, dtf);
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void somaRazaoDeb(String Id, String dti, String dtf, String conta) throws SQLException{
        try{
            stm = Conexao.conectar().prepareStatement("select sum(vlrdeb) "
            + "from lancacontab "
            + "where filiais_idfiliais = ? "
            + "and datalan >= ? "
            + "and datalan <= ? "
            + "and idpcdeb = ?");
            stm.setInt(1, Integer.parseInt(Id));
            stm.setString(2, dti);
            stm.setString(3, dtf);
            stm.setString(4, conta);
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
            System.out.println(conta);
        }
    }

    public void somaRazaoCre(String Id, String dti, String dtf, String conta) throws SQLException{
        try{
            stm = Conexao.conectar().prepareStatement("select sum(vlrcre) "
            + "from lancacontab "
            + "where filiais_idfiliais = ? "
            + "and datalan >= ?  "
            + "and datalan <= ? "
            + "and idpccre = ?");
            stm.setInt(1, Integer.parseInt(Id));
            stm.setString(2, dti);
            stm.setString(3, dtf);
            stm.setString(4, conta);
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
            System.out.println(conta);
        }
    }

    public void listaRazao(){
        try{
            stm = Conexao.conectar().prepareStatement("select * from razao;");
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void listaRazaoConta(){
        try{
            stm = Conexao.conectar().prepareStatement("select distinct conta FROM "
                    + "razao order by conta;");
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void listaRazaoDet(String conta){
        try{
            stm = Conexao.conectar().prepareStatement("select * from razao "
                    + "where conta = '"+conta+"' "
                    + "order by dtlan;");
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void listaRazaoChMaiorZero(){
        try{
            stm = Conexao.conectar().prepareStatement("select * from razao "
                    + "where idlanca > 0 "
                    + "order by conta;");
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void listaRazaoChZero(String conta){
        try{
            stm = Conexao.conectar().prepareStatement("select count(*) as nro from razao "
                    + "where idlanca = 0 and conta = '"+conta+"';");
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void somaDeb(String Idf, String Idc, String dti, String dtf) throws SQLException{
        try{
            stm = Conexao.conectar().prepareStatement("select sum(vlrdeb) "
            + "from ct_dados.lancacontab "
            + "where filiais_idfiliais = ? "
            + "and idpcdeb = ? "
            + "and datalan > ? "
            + "and datalan < ?;");
            stm.setInt(1, Integer.parseInt(Idf));
            stm.setString(2, Idc);
            stm.setString(3, dti);
            stm.setString(4, dtf);
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void somaCre(String Idf, String Idc, String dti, String dtf) throws SQLException{
        try{
            stm = Conexao.conectar().prepareStatement("select sum(vlrcre) from "
            + "ct_dados.lancacontab "
            + "where filiais_idfiliais = ? "
            + "and idpccre = ? "
            + "and datalan > ? "
            + "and datalan < ?;");
            stm.setInt(1, Integer.parseInt(Idf));
            stm.setString(2, Idc);
            stm.setString(3, dti);
            stm.setString(4, dtf);
            rs = stm.executeQuery();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public ResultSet getResultado(){
        return rs;
    }

}