/*
Copyright 2012-2017 Jose Robson Mariano Alves
This file is part of bgfinancas.
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This package is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
package badernageral.bgfinancas.biblioteca.banco;
import badernageral.bgfinancas.biblioteca.sistema.Janela;
import badernageral.bgfinancas.biblioteca.utilitario.Datas;
import badernageral.bgfinancas.idioma.Linguagem;
import badernageral.bgfinancas.modelo.Agenda;
import badernageral.bgfinancas.modelo.Configuracao;
import badernageral.bgfinancas.modelo.Conta;
import badernageral.bgfinancas.modelo.Despesa;
import badernageral.bgfinancas.modelo.Grupo;
import badernageral.bgfinancas.modelo.Receita;
import badernageral.bgfinancas.modelo.Transferencia;
import java.sql.SQLException;
import java.time.LocalDate;
public final class Database {
private Database(){ }
public static void verificarBanco() {
try {
Conexao banco = Conexao.getInstance();
banco.executeQuery("SELECT COUNT(TABLE_NAME) AS quantidade FROM INFORMATION_SCHEMA.SYSTEM_TABLES where TABLE_TYPE='TABLE'");
banco.getResultSet().next();
if (banco.getResultSet().getInt("quantidade") < 16) {
Database.criarBanco();
}
Database.executarAtualizacoes();
} catch (SQLException ex) {
Janela.showException(ex);
}
}
private static void criarBanco(){
Conexao banco = Conexao.getInstance();
banco.executeUpdate("CREATE TABLE agenda (\n" +
" id_agenda INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" id_tipo INTEGER, \n" +
" descricao VARCHAR_IGNORECASE(100), \n" +
" valor DECIMAL(10,2), \n" +
" data DATE)");
banco.executeUpdate("CREATE TABLE agenda_tipos (\n" +
" id_tipo INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" nome VARCHAR_IGNORECASE(100))");
banco.executeUpdate("CREATE TABLE contas (\n" +
" id_conta INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" nome VARCHAR_IGNORECASE(100), \n" +
" valor DECIMAL(10,2), \n" +
" ativada INTEGER, \n" +
" saldo_total VARCHAR_IGNORECASE(10))");
banco.executeUpdate("CREATE TABLE despesas (\n" +
" id_despesa INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" id_conta INTEGER, \n" +
" id_item INTEGER, \n" +
" quantidade DECIMAL(10,2), \n" +
" valor DECIMAL(10,2), \n" +
" data DATE, \n" +
" hora TIME)");
banco.executeUpdate("CREATE TABLE despesas_categorias (\n" +
" id_categoria INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" nome VARCHAR_IGNORECASE(100))");
banco.executeUpdate("CREATE TABLE despesas_itens (\n" +
" id_item INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" id_categoria INTEGER, \n" +
" nome VARCHAR_IGNORECASE(100))");
banco.executeUpdate("CREATE TABLE planejamento (\n" +
" id_planejamento INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" mes INTEGER, \n" +
" ano INTEGER, \n" +
" valor DECIMAL(10,2))");
banco.executeUpdate("CREATE TABLE planejamento_componentes (\n" +
" id_planejamento INTEGER, \n" +
" id_item INTEGER, \n" +
" valor DECIMAL(10,2))");
banco.executeUpdate("CREATE TABLE planejamento_itens (\n" +
" id_item INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" nome VARCHAR_IGNORECASE(100))");
banco.executeUpdate("CREATE TABLE receitas (\n" +
" id_receita INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" id_conta INTEGER, \n" +
" id_item INTEGER, \n" +
" descricao VARCHAR_IGNORECASE(100), \n" +
" valor DECIMAL(10,2), \n" +
" data DATE, \n" +
" hora TIME)");
banco.executeUpdate("CREATE TABLE receitas_categorias (\n" +
" id_categoria INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" nome VARCHAR_IGNORECASE(100))");
banco.executeUpdate("CREATE TABLE receitas_itens (\n" +
" id_item INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" id_categoria INTEGER, \n" +
" nome VARCHAR_IGNORECASE(100))");
banco.executeUpdate("CREATE TABLE relatorios_grupos (\n" +
" id_relatorios_grupos INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" nome VARCHAR_IGNORECASE(100), \n" +
" valor DECIMAL(10,2), \n" +
" saldo_total VARCHAR_IGNORECASE(10))");
banco.executeUpdate("CREATE TABLE relatorios_grupos_itens (\n" +
" id_relatorios_grupos INTEGER, \n" +
" id_despesas_categorias INTEGER)");
banco.executeUpdate("CREATE TABLE transferencias (\n" +
" id_transferencia INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" id_conta1 INTEGER, \n" +
" id_conta2 INTEGER, \n" +
" id_item INTEGER, \n" +
" descricao VARCHAR_IGNORECASE(100), \n" +
" valor DECIMAL(10,2), \n" +
" data DATE, \n" +
" hora TIME)");
banco.executeUpdate("CREATE TABLE transferencias_categorias (\n" +
" id_categoria INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" nome VARCHAR_IGNORECASE(100))");
banco.executeUpdate("CREATE TABLE transferencias_itens (\n" +
" id_item INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" id_categoria INTEGER, \n" +
" nome VARCHAR_IGNORECASE(100))");
banco.executeUpdate("CREATE TABLE usuarios (\n" +
" id_usuarios INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" nome VARCHAR_IGNORECASE(100), \n" +
" usuario VARCHAR_IGNORECASE(100), \n" +
" senha VARCHAR_IGNORECASE(100))");
}
private static void executarAtualizacoes(){
Configuracao.verificar();
Conexao banco = Conexao.getInstance();
String versao = Configuracao.getPropriedade("versao");
if(versao.equals("3.0")){
banco.executeUpdate("ALTER TABLE despesas ADD COLUMN agendada INTEGER DEFAULT 0 NOT NULL");
banco.executeUpdate("ALTER TABLE despesas ADD COLUMN parcela VARCHAR_IGNORECASE(10)");
banco.executeUpdate("DROP TABLE planejamento_componentes");
banco.executeUpdate("DROP TABLE planejamento_itens");
banco.executeUpdate("DROP TABLE planejamento");
versao = setValorVersao("3.1");
}
if(versao.equals("3.1")){
versao = setValorVersao("3.1.1");
}
if(versao.equals("3.1.1")){
versao = setValorVersao("3.2");
}
if(versao.equals("3.2")){
versao = setValorVersao("3.2.1");
}
if(versao.equals("3.2.1")){
banco.executeUpdate("CREATE TABLE cartao_credito (\n" +
" id_cartao_credito INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
" nome VARCHAR_IGNORECASE(100), \n" +
" limite DECIMAL(10,2), \n" +
" vencimento INTEGER)");
banco.executeUpdate("ALTER TABLE despesas ADD COLUMN id_cartao_credito INTEGER DEFAULT NULL");
versao = setValorVersao("3.3");
}
if(versao.equals("3.3")){
versao = setValorVersao("3.4");
}
if(versao.equals("3.4")){
versao = setValorVersao("3.5");
}
if(versao.equals("3.5")){
versao = setValorVersao("3.6");
}
}
private static String setValorVersao(String versao){
Configuracao.setPropriedade("versao", versao);
return versao;
}
public static void popularBanco(){
Conexao banco = Conexao.getInstance();
String data = Datas.getDataSqlHoje();
if(Configuracao.getPropriedade("idioma").equals("en_US")){
if(new Agenda().listar().isEmpty()){
banco.executeUpdate("INSERT INTO AGENDA VALUES(0,0,'Tom',100.00,'"+data+"')");
banco.executeUpdate("INSERT INTO AGENDA VALUES(1,0,'Harry',50.00,'"+data+"')");
banco.executeUpdate("INSERT INTO AGENDA_TIPOS VALUES(0,'Accounts receivable')");
}
if(new Conta().listar().isEmpty()){
banco.executeUpdate("INSERT INTO CONTAS VALUES(0,'Bank',500.00,1,'0')");
banco.executeUpdate("INSERT INTO CONTAS VALUES(1,'Wallet',100.00,1,'0')");
banco.executeUpdate("INSERT INTO CONTAS VALUES(2,'Savings',200.00,1,'1')");
}
if(new Despesa().listar().isEmpty()){
banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(0,'Car')");
banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(1,'Essentials')");
banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(2,'Recreation')");
banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(3,'Snacks')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(0,0,'Gasoline')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(1,0,'Maintenance')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(2,1,'Rice')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(3,1,'Bean')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(4,2,'Cinema')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(5,3,'Donut')");
banco.executeUpdate("INSERT INTO DESPESAS VALUES(0,1,0,20.00,60.00,'"+data+"','08:18:04',0,NULL,NULL)");
banco.executeUpdate("INSERT INTO DESPESAS VALUES(2,0,2,1.00,10.00,'"+data+"','08:19:47',0,NULL,NULL)");
banco.executeUpdate("INSERT INTO DESPESAS VALUES(3,0,3,1.00,5.00,'"+data+"','08:20:08',0,NULL,NULL)");
banco.executeUpdate("INSERT INTO DESPESAS VALUES(4,1,4,2.00,30.00,'"+data+"','08:20:41',0,NULL,NULL)");
banco.executeUpdate("INSERT INTO DESPESAS VALUES(5,1,5,1.00,3.00,'"+data+"','08:21:59',0,NULL,NULL)");
}
if(new Despesa().setSomenteAgendamento().listar().isEmpty()){
banco.executeUpdate("INSERT INTO DESPESAS VALUES(1,1,1,1.00,100.00,'"+data+"','08:19:01',1,NULL,NULL)");
}
if(new Receita().listar().isEmpty()){
banco.executeUpdate("INSERT INTO RECEITAS VALUES(0,0,0,'"+Linguagem.getInstance().getNomeMes(LocalDate.now().getMonthValue())+" / "+LocalDate.now().getYear()+"',900.00,'"+data+"','15:28:52')");
banco.executeUpdate("INSERT INTO RECEITAS VALUES(1,1,1,'Repair',50.00,'"+data+"','15:29:55')");
banco.executeUpdate("INSERT INTO RECEITAS_CATEGORIAS VALUES(0,'Salary')");
banco.executeUpdate("INSERT INTO RECEITAS_CATEGORIAS VALUES(1,'Others')");
banco.executeUpdate("INSERT INTO RECEITAS_ITENS VALUES(0,0,'Company X')");
banco.executeUpdate("INSERT INTO RECEITAS_ITENS VALUES(1,1,'Services')");
}
if(new Grupo().listar().isEmpty()){
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(0,'Food',200.00,NULL)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(1,'Recreation',100.00,NULL)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(2,'Vehicles',150.00,NULL)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(0,1)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(0,3)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(1,2)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(2,0)");
}
if(new Transferencia().listar().isEmpty()){
banco.executeUpdate("INSERT INTO TRANSFERENCIAS VALUES(0,0,1,0,'to food',100.00,'"+data+"','08:25:32')");
banco.executeUpdate("INSERT INTO TRANSFERENCIAS VALUES(1,1,2,1,'saving',50.00,'"+data+"','08:26:21')");
banco.executeUpdate("INSERT INTO TRANSFERENCIAS_CATEGORIAS VALUES(0,'Bank')");
banco.executeUpdate("INSERT INTO TRANSFERENCIAS_ITENS VALUES(0,0,'Withdraw')");
banco.executeUpdate("INSERT INTO TRANSFERENCIAS_ITENS VALUES(1,0,'Deposit')");
}
}else{
if(new Agenda().listar().isEmpty()){
banco.executeUpdate("INSERT INTO AGENDA VALUES(0,0,'Fulano',100.00,'"+data+"')");
banco.executeUpdate("INSERT INTO AGENDA VALUES(1,0,'Ciclano',50.00,'"+data+"')");
banco.executeUpdate("INSERT INTO AGENDA_TIPOS VALUES(0,'Conta a receber')");
}
if(new Conta().listar().isEmpty()){
banco.executeUpdate("INSERT INTO CONTAS VALUES(0,'Banco',500.00,1,'0')");
banco.executeUpdate("INSERT INTO CONTAS VALUES(1,'Carteira',100.00,1,'0')");
banco.executeUpdate("INSERT INTO CONTAS VALUES(2,'Poupan\u00e7a',200.00,1,'1')");
}
if(new Despesa().listar().isEmpty()){
banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(0,'Carro')");
banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(1,'Cesta B\u00e1sica')");
banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(2,'Lazer')");
banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(3,'Lanches')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(0,0,'Gasolina')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(1,0,'Manuten\u00e7\u00e3o')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(2,1,'Arroz')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(3,1,'Feij\u00e3o')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(4,2,'Cinema')");
banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(5,3,'Pastel')");
banco.executeUpdate("INSERT INTO DESPESAS VALUES(0,1,0,20.00,60.00,'"+data+"','08:18:04',0,NULL,NULL)");
banco.executeUpdate("INSERT INTO DESPESAS VALUES(2,0,2,1.00,10.00,'"+data+"','08:19:47',0,NULL,NULL)");
banco.executeUpdate("INSERT INTO DESPESAS VALUES(3,0,3,1.00,5.00,'"+data+"','08:20:08',0,NULL,NULL)");
banco.executeUpdate("INSERT INTO DESPESAS VALUES(4,1,4,2.00,30.00,'"+data+"','08:20:41',0,NULL,NULL)");
banco.executeUpdate("INSERT INTO DESPESAS VALUES(5,1,5,1.00,3.00,'"+data+"','08:21:59',0,NULL,NULL)");
}
if(new Despesa().setSomenteAgendamento().listar().isEmpty()){
banco.executeUpdate("INSERT INTO DESPESAS VALUES(1,1,1,1.00,100.00,'"+data+"','08:19:01',1,NULL,NULL)");
}
if(new Receita().listar().isEmpty()){
banco.executeUpdate("INSERT INTO RECEITAS VALUES(0,0,0,'"+Linguagem.getInstance().getNomeMes(LocalDate.now().getMonthValue())+" / "+LocalDate.now().getYear()+"',900.00,'"+data+"','15:28:52')");
banco.executeUpdate("INSERT INTO RECEITAS VALUES(1,1,1,'Conserto',50.00,'"+data+"','15:29:55')");
banco.executeUpdate("INSERT INTO RECEITAS_CATEGORIAS VALUES(0,'Sal\u00e1rio')");
banco.executeUpdate("INSERT INTO RECEITAS_CATEGORIAS VALUES(1,'Outros')");
banco.executeUpdate("INSERT INTO RECEITAS_ITENS VALUES(0,0,'Empresa X')");
banco.executeUpdate("INSERT INTO RECEITAS_ITENS VALUES(1,1,'Servi\u00e7os prestados')");
}
if(new Grupo().listar().isEmpty()){
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(0,'Alimenta\u00e7\u00e3o',200.00,NULL)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(1,'Lazer',100.00,NULL)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(2,'Ve\u00edculos',150.00,NULL)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(0,1)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(0,3)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(1,2)");
banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(2,0)");
}
if(new Transferencia().listar().isEmpty()){
banco.executeUpdate("INSERT INTO TRANSFERENCIAS VALUES(0,0,1,0,'para comida',100.00,'"+data+"','08:25:32')");
banco.executeUpdate("INSERT INTO TRANSFERENCIAS VALUES(1,1,2,1,'poupando',50.00,'"+data+"','08:26:21')");
banco.executeUpdate("INSERT INTO TRANSFERENCIAS_CATEGORIAS VALUES(0,'Banco')");
banco.executeUpdate("INSERT INTO TRANSFERENCIAS_ITENS VALUES(0,0,'Saque')");
banco.executeUpdate("INSERT INTO TRANSFERENCIAS_ITENS VALUES(1,0,'Dep\u00f3sito')");
}
}
}
}