public static List <CL_ComlModelo> listar(string con, string marca)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM coml_modelo WHERE m_marca=@m_marca ORDER BY m_codigo";
List <CL_ComlModelo> objList = new List <CL_ComlModelo>();
CL_ComlModelo obj = null;
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("m_marca", marca);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
//instancio objeto cliente a cada item da lista de registos
obj = new CL_ComlModelo();
//leio as informações dos campos e jogo para o objeto
obj.m_codigo = Convert.ToInt32(dr["m_codigo"]);
obj.m_nome = dr["m_nome"].ToString().Trim();
obj.m_marca = Convert.ToInt32(dr["m_marca"]);
obj.m_infor = dr["m_infor"].ToString().Trim();
objList.Add(obj);
}
dr.Close();
return(objList);
}
else
{
return(null);
}
}
catch (Exception ex)
{
ex.ToString();
return(null);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static CL_Veiculos verificaVeiculos(CL_Veiculos objVeiculos, string con)
{
string sql = "SELECT * FROM veiculo WHERE ve_chassi =('" + objVeiculos.ve_chassi + "')";
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
objVeiculos.ve_chassi = dr["ve_chassi"].ToString().Trim();
objVeiculos.ve_placa = dr["ve_placa"].ToString().Trim();
objVeiculos.ve_serie = dr["ve_serie"].ToString().Trim();
objVeiculos.ve_rrae = dr["ve_rrae"].ToString().Trim();
objVeiculos.ve_nomprop = dr["ve_nompro"].ToString().Trim();
objVeiculos.ve_prop = dr["ve_prop"].ToString().Trim();
objVeiculos.ve_linha = dr["ve_linha"].ToString().Trim();
objVeiculos.ve_marca = dr["ve_marca"].ToString().Trim();
objVeiculos.ve_anofab = dr["ve_anofab"].ToString().Trim();
objVeiculos.ve_modelo = dr["ve_modelo"].ToString().Trim();
objVeiculos.ve_anomod = dr["ve_anomod"].ToString().Trim();
objVeiculos.ve_cup1 = dr["ve_cup1"].ToString().Trim();
objVeiculos.ve_cup2 = dr["ve_cup2"].ToString().Trim();
objVeiculos.ve_cup3 = dr["ve_cup3"].ToString().Trim();
objVeiculos.ve_cup4 = dr["ve_cup4"].ToString().Trim();
objVeiculos.ve_cup5 = dr["ve_cup5"].ToString().Trim();
objVeiculos.ve_estmaq = dr["ve_estmaq"].ToString().Trim();
return(objVeiculos);
}
}
objVeiculos.ve_chassi = null;
return(objVeiculos);
}
catch (Exception ex)
{
ex.ToString();
return(objVeiculos);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static CL_Equipamento buscaEquipPatrimon(CL_Equipamento objEquipamento, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM equipamento WHERE e_patrimon='" + objEquipamento.e_nPatrimon + "' ORDER BY e_cod";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
objEquipamento.e_descri = dr["e_descri"].ToString().Trim();
objEquipamento.e_marca = dr["e_marca"].ToString().Trim();
objEquipamento.e_modelo = dr["e_modelo"].ToString().Trim();
objEquipamento.e_nmarca = Convert.ToInt32(dr["e_nmarca"]);
objEquipamento.e_nmodelo = Convert.ToInt32(dr["e_nmodelo"]);
objEquipamento.e_nPatrimon = dr["e_patrimon"].ToString().Trim();
objEquipamento.e_nserie = dr["e_nserie"].ToString().Trim();
objEquipamento.e_ncontrato = Convert.ToInt32(dr["e_ncontrato"]);
return(objEquipamento);
}
else
{
objEquipamento = null;
return(objEquipamento);
}
}
else
{
objEquipamento = null;
return(objEquipamento);
}
}
catch (Exception ex)
{
ex.ToString();
objEquipamento = null;
return(objEquipamento);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static List <CL_CicloProducao> listar(string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM prod_agricola ORDER BY p_id";
CL_CicloProducao objProducao = null;
List <CL_CicloProducao> objListProducao = new List <CL_CicloProducao>();
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
//instancio objeto cliente a cada item da lista de registos
objProducao = new CL_CicloProducao();
objProducao.p_id = dr["p_id"] is DBNull ? 0 : Convert.ToInt32(dr["p_id"]);
objProducao.p_nome = dr["p_nome"].ToString().Trim();
objProducao.p_situacao = dr["p_situacao"].ToString().Trim();
objProducao.p_inicio = dr["p_inicio"].ToString().Trim();
objProducao.p_fim = dr["p_fim"].ToString().Trim();
objProducao.p_qtdhec = Convert.ToInt32(dr["p_qtdhec"]);
objListProducao.Add(objProducao);
}
dr.Close();
return(objListProducao);
}
else
{
objListProducao = null;
return(objListProducao);
}
}
catch (Exception ex)
{
ex.ToString();
objListProducao = null;
return(objListProducao);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static CL_ComlModelo buscaModelo(CL_ComlModelo objComlModelo, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM coml_modelo WHERE m_codigo=@m_codigo ORDER BY m_codigo";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("m_codigo", objComlModelo.m_codigo);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
objComlModelo.m_codigo = Convert.ToInt16(dr["m_codigo"]);
objComlModelo.m_nome = dr["m_nome"].ToString().Trim();
objComlModelo.m_infor = dr["m_infor"].ToString().Trim();
objComlModelo.m_marca = Convert.ToInt32(dr["m_marca"]);
return(objComlModelo);
}
else
{
objComlModelo = null;
return(objComlModelo);
}
}
else
{
objComlModelo = null;
return(objComlModelo);
}
}
catch (Exception ex)
{
ex.ToString();
objComlModelo = null;
return(objComlModelo);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static CL_TipoMov buscaTipo(int cod, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM tipomovtitulo WHERE t_codigo=@t_cod";
CL_TipoMov objTipo = new CL_TipoMov();
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("t_cod", cod);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
objTipo.t_codigo = dr["t_codigo"] is DBNull ? 0 : Convert.ToInt32(dr["t_codigo"]);
objTipo.t_descri = dr["t_descri"].ToString().Trim();
objTipo.t_tipo = dr["t_tipo"].ToString().Trim();
objTipo.t_acumul = dr["t_acumul"].ToString().Trim();
objTipo.t_somatot = dr["t_somatot"].ToString().Trim();
objTipo.t_ctacre = dr["t_ctacre"].ToString().Trim();
objTipo.t_ctadeb = dr["t_ctadeb"].ToString().Trim();
dr.Close();
return(objTipo);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
catch (Exception ex)
{
ex.ToString();
return(null);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static CL_LogMecanic buscaLogMecanic(int obj, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM log_mecanico WHERE l_idapp=" + obj;
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
CL_LogMecanic objLog = new CL_LogMecanic();
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
objLog.l_id = Convert.ToInt32(dr["l_id"]);
objLog.l_localiz = dr["l_localiz"].ToString().Trim();
objLog.l_meccod = Convert.ToInt32(dr["l_meccod"]);
objLog.l_mecnome = dr["l_mecnome"].ToString().Trim();
objLog.l_mectipo = dr["l_mectipo"].ToString().Trim();
objLog.l_data = Convert.ToDateTime(dr["l_data"]);
objLog.l_idapp = Convert.ToInt64(dr["l_idapp"]);
return(objLog);
}
else
{
objLog = null;
return(objLog);
}
}
else
{
objLog = null;
return(objLog);
}
}
catch (Exception ex)
{
ex.ToString();
objLog = null;
return(objLog);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static bool ultMov(CL_Est objEst, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
DateTime data;
try
{
string sql = "SELECT mov_data FROM movest WHERE est_cod='" + objEst.est_cod + "' ORDER BY mov_data DESC";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
data = Convert.ToDateTime(dr["mov_data"]);
if (data.AddYears(5) > DateTime.Now)
{
return(true);
}
else
{
return(false);
}
}
else
{
return(true);
}
}
else
{
return(true);
}
}
catch (Exception ex)
{
ex.ToString();
return(false);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static CL_Landesp buscaDesp(int l_id, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
CL_Landesp objLand = new CL_Landesp();
string sql = "SELECT * FROM movdesp WHERE l_id=@l_id";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("l_id", l_id);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
objLand.l_id = l_id;
objLand.l_data = Convert.ToDateTime(dr["l_data"]);
objLand.l_tipo = dr["l_tipo"].ToString().Trim();
objLand.l_valor = Convert.ToDouble(dr["l_valor"]);
objLand.l_forma = dr["l_forma"].ToString().Trim();
objLand.l_obs = dr["l_obs"].ToString().Trim();
return(objLand);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
catch (Exception ex)
{
ex.ToString();
return(null);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static List <CL_Ccusto> listar(string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM ccustos ORDER BY c_id";
List <CL_Ccusto> objList = new List <CL_Ccusto>();
CL_Ccusto obj = null;
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
//instancio objeto cliente a cada item da lista de registos
obj = new CL_Ccusto();
//leio as informações dos campos e jogo para o objeto
obj.c_id = Convert.ToInt32(dr["c_id"]);
obj.c_descri = dr["c_descri"].ToString().Trim();
obj.c_tipo = dr["c_tipo"].ToString().Trim();
obj.c_modelo = dr["c_modelo"].ToString().Trim();
objList.Add(obj);
}
dr.Close();
return(objList);
}
else
{
objList = null;
return(objList);
}
}
catch (Exception ex)
{
ex.ToString();
objList = null;
return(objList);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static CL_RotaCidade buscaRota(int r_id, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM rota_cidade WHERE r_id=@r_id";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("r_id", r_id);
NpgsqlDataReader dr;
CL_RotaCidade objRota = new CL_RotaCidade();
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
objRota.r_id = r_id;
objRota.r_bairro = dr["r_bairro"].ToString().Trim();
objRota.r_cidade = dr["r_cidade"].ToString().Trim();
objRota.r_identreg = Convert.ToInt32(dr["r_identreg"]);
return(objRota);
}
else
{
objRota = null;
return(objRota);
}
}
else
{
objRota = null;
return(objRota);
}
}
catch (Exception ex)
{
ex.ToString();
objRota = null;
return(objRota);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static CL_Ccusto buscaCcusto(CL_Ccusto objCcusto, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM ccustos WHERE c_id=@c_id ORDER BY c_id";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("c_id", objCcusto.c_id);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
objCcusto.c_descri = dr["c_descri"].ToString().Trim();
objCcusto.c_modelo = dr["c_modelo"].ToString().Trim();
objCcusto.c_tipo = dr["c_tipo"].ToString().Trim();
objCcusto.c_tipodesc = dr["c_tipodesc"].ToString().Trim();
return(objCcusto);
}
else
{
objCcusto = null;
return(objCcusto);
}
}
else
{
objCcusto = null;
return(objCcusto);
}
}
catch (Exception ex)
{
ex.ToString();
objCcusto = null;
return(objCcusto);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static bool cadMaquinario(CL_Maquinario objMaquinario, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
try
{
Conn.Open();
string sql = "INSERT INTO veiculo (ve_marca, ve_chassi, ve_dtvenda, ve_concvda, ve_anomod, ve_anofab, ve_modelo, ve_placa, ve_serie, ve_dtcpa, ve_vctgar, " +
"ve_estmaq, ve_mdmotor, ve_alienada, ve_dtaliberacao, ve_instituicao, ve_potencia, ve_ntcpa, ve_prop, ve_vlraqui, ve_percpropriet, ve_vlrhora) " +
"VALUES " +
"(@ve_marca, @ve_chassi, @ve_dtvenda, @ve_concvda, @ve_anomod, @ve_anofab, @ve_modelo, @ve_placa, @ve_serie, @ve_dtcpa, @ve_vctgar, " +
"@ve_estmaq, @ve_mdmotor, @ve_alienada, @ve_dtaliberacao, @ve_instituicao, @ve_potencia, @ve_ntcpa, @ve_prop, @ve_vlraqui, @ve_percpropriet, @ve_vlrhora)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, Conn);
cmd.Parameters.AddWithValue("ve_marca", objMaquinario.ve_marca);
cmd.Parameters.AddWithValue("ve_chassi", objMaquinario.ve_chassi);
cmd.Parameters.AddWithValue("ve_dtvenda", objMaquinario.ve_dtvenda.ToShortDateString());
cmd.Parameters.AddWithValue("ve_concvda", objMaquinario.ve_concvda);
cmd.Parameters.AddWithValue("ve_anomod", objMaquinario.ve_anomod);
cmd.Parameters.AddWithValue("ve_anofab", objMaquinario.ve_anofab);
cmd.Parameters.AddWithValue("ve_modelo", objMaquinario.ve_modelo);
cmd.Parameters.AddWithValue("ve_placa", objMaquinario.ve_placa);
cmd.Parameters.AddWithValue("ve_serie", objMaquinario.ve_serie);
cmd.Parameters.AddWithValue("ve_dtcpa", objMaquinario.ve_dtcpa.ToShortDateString());
cmd.Parameters.AddWithValue("ve_vctgar", objMaquinario.ve_vctgar.ToShortDateString());
cmd.Parameters.AddWithValue("ve_estmaq", objMaquinario.ve_estmaq);
cmd.Parameters.AddWithValue("ve_mdmotor", objMaquinario.ve_mdmotor);
cmd.Parameters.AddWithValue("ve_alienada", objMaquinario.ve_alienada);
cmd.Parameters.AddWithValue("ve_dtaliberacao", objMaquinario.ve_dtaliberacao.ToShortDateString());
cmd.Parameters.AddWithValue("ve_instituicao", objMaquinario.ve_instituicao);
cmd.Parameters.AddWithValue("ve_potencia", objMaquinario.ve_potencia);
cmd.Parameters.AddWithValue("ve_ntcpa", objMaquinario.ve_ntcpa);
cmd.Parameters.AddWithValue("ve_prop", objMaquinario.ve_prop);
cmd.Parameters.AddWithValue("ve_vlraqui", objMaquinario.ve_vlaqui);
cmd.Parameters.AddWithValue("ve_percpropriet", objMaquinario.ve_percpropriet);
cmd.Parameters.AddWithValue("ve_vlrhora", objMaquinario.ve_vlrhora);
cmd.ExecuteScalar();
return(true);
}
catch (Exception ex)
{
ex.ToString();
return(false);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static List <CL_Movduprec> buscaMov(int dupcod, int dupparc, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT mr_data, mr_tipo, mr_acumul, mr_valor, t_descri, t_somatot FROM movduprec, tipomovtitulo " +
"WHERE mr_duplic=@mr_duplic AND mr_parc=@mr_parc AND mr_tipo=t_codigo";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("mr_duplic", dupcod);
comand.Parameters.AddWithValue("mr_parc", dupparc);
NpgsqlDataReader dr;
List <CL_Movduprec> objList = new List <CL_Movduprec>();
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
objList.Add(new CL_Movduprec()
{
mr_data = Convert.ToDateTime(dr["mr_data"]),
mr_acumul = dr["mr_acumul"].ToString().Trim(),
mr_valor = Convert.ToDouble(dr["mr_valor"]),
mr_tipo = Convert.ToInt32(dr["mr_tipo"]),
mr_descricao = dr["t_descri"].ToString().Trim(),
mr_somatot = dr["t_somatot"].ToString().Trim(),
});
}
dr.Close();
return(objList);
}
else
{
return(null);
}
}
catch (Exception ex)
{
ex.ToString();
return(null);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static CL_Usudac buscaUsudacEmail(string email, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
CL_Usudac objUsudac = new CL_Usudac();
if (Conn.State == ConnectionState.Closed)
{
Conn.Open();
}
string sql = "SELECT u_tipoaces FROM usudac WHERE u_email=@email";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("email", email);
NpgsqlDataReader dr;
try
{
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
objUsudac.u_tipoaces = dr["u_tipoaces"].ToString().Trim();
return(objUsudac);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
catch (Exception ex)
{
ex.ToString();
objUsudac = null;
return(objUsudac);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static List <CL_Duprec> buscaDupCliente(int p_clicod, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM duprec WHERE dup_codcli=@dup_codcli AND dup_pgto IS NULL";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("dup_codcli", p_clicod);
NpgsqlDataReader dr;
List <CL_Duprec> objList = new List <CL_Duprec>();
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
objList.Add(new CL_Duprec()
{
dup_cod = Convert.ToInt32(dr["dup_cod"]),
dup_parc = Convert.ToInt32(dr["dup_parc"]),
dup_nota = Convert.ToInt32(dr["dup_nota"]),
dup_emis = Convert.ToDateTime(dr["dup_emis"]),
dup_vcto = Convert.ToDateTime(dr["dup_vcto"]),
dup_valor = Convert.ToDouble(dr["dup_valor"]),
});
}
dr.Close();
return(objList);
}
else
{
return(objList);
}
}
catch (Exception ex)
{
ex.ToString();
return(null);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static CL_Pluviometro buscaPluv(CL_Pluviometro objPluv, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM pluviometro WHERE p_id=" + objPluv.p_id + " ORDER BY p_id";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
objPluv.p_data = Convert.ToDateTime(dr["p_data"]);
objPluv.p_turno = dr["p_turno"].ToString().Trim();
objPluv.p_duracao = Convert.ToDouble(dr["p_duracao"]);
objPluv.p_qtdade = Convert.ToDouble(dr["p_qtdade"]);
objPluv.p_idlavoura = Convert.ToInt32(dr["p_idlavoura"]);
return(objPluv);
}
else
{
objPluv = null;
return(objPluv);
}
}
else
{
objPluv = null;
return(objPluv);
}
}
catch (Exception ex)
{
ex.ToString();
objPluv = null;
return(objPluv);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static List <CL_Landesp> pesquisa(DateTime data, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM movdesp WHERE l_data=@l_data";
List <CL_Landesp> objList = new List <CL_Landesp>();
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("l_data", data.ToShortDateString());
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
objList.Add(new CL_Landesp()
{
l_id = Convert.ToInt32(dr["l_id"]),
l_data = Convert.ToDateTime(dr["l_data"]),
l_tipo = dr["l_tipo"].ToString().Trim(),
l_valor = Convert.ToDouble(dr["l_valor"]),
l_forma = dr["l_forma"].ToString().Trim(),
l_obs = dr["l_obs"].ToString().Trim(),
});
}
dr.Close();
return(objList);
}
else
{
return(null);
}
}
catch (Exception ex)
{
ex.ToString();
return(null);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static int buscaCod(string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
int s_codigo = 0;
string sql = "SELECT s_codigo FROM sittitulo ORDER BY s_codigo DESC LIMIT 1";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
s_codigo = Convert.ToInt16(dr["s_codigo"]);
s_codigo = s_codigo + 1;
return(s_codigo);
}
else
{
s_codigo = 0;
return(s_codigo);
}
}
else
{
s_codigo = 1;
return(s_codigo);
}
}
catch (Exception ex)
{
ex.ToString();
s_codigo = 0;
return(s_codigo);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static List <CL_RotaCidade> buscaRotasEntregadores(string identreg, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT r_id, r_bairro, r_cidade, r_identreg, mec_nome FROM rota_cidade, mecanico WHERE r_identreg=@r_identreg AND mec_cod=r_identreg";
List <CL_RotaCidade> objListRota = new List <CL_RotaCidade>();
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("r_identreg", identreg);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
objListRota.Add(new CL_RotaCidade()
{
r_id = Convert.ToInt32(dr["r_id"]),
r_nome = dr["mec_nome"].ToString().Trim(),
r_identreg = Convert.ToInt32(identreg),
r_bairro = dr["r_bairro"].ToString().Trim(),
r_cidade = dr["r_cidade"].ToString().Trim(),
});
}
dr.Close();
return(objListRota);
}
else
{
return(objListRota);
}
}
catch (Exception ex)
{
ex.ToString();
objListRota = null;
return(objListRota);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static int buscaNrTentativas(long e_identreg, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
int m_nrtent = 0;
string sql = "SELECT m_nrtent FROM mov_entregas WHERE m_identreg=@m_identreg ORDER BY m_nrtent DESC LIMIT 1";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("m_identreg", e_identreg);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
m_nrtent = dr["m_nrtent"] is DBNull ? 0 : Convert.ToInt32(dr["m_nrtent"]);
m_nrtent = m_nrtent + 1;
return(m_nrtent);
}
else
{
m_nrtent = 1;
return(m_nrtent);
}
}
else
{
m_nrtent = 1;
return(m_nrtent);
}
}
catch (Exception ex)
{
ex.ToString();
m_nrtent = 0;
return(m_nrtent);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static int buscaCod(string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
int d_id = 0;
string sql = "SELECT d_id FROM despesa_lavoura ORDER BY d_id DESC LIMIT 1";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
d_id = Convert.ToInt32(dr["d_id"]);
d_id = d_id + 1;
return(d_id);
}
else
{
d_id = 0;
return(d_id);
}
}
else
{
d_id = 1;
return(d_id);
}
}
catch (Exception ex)
{
ex.ToString();
d_id = 0;
return(d_id);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static bool alteraPartComplende(CL_Partcomplende objPartComplende, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
try
{
string sql = "UPDATE part_compl SET pc_codpart=@pc_codpart, pc_nome=@pc_nome, pc_cnpj=@pc_cnpj, pc_ende=@pc_ende, " +
"pc_nr=@pc_nr, pc_compl=@pc_compl, pc_bairro=@pc_bairro, pc_ibge=@pc_ibge, pc_cida=@pc_cida, pc_uf=@pc_uf, pc_respons=@pc_respons, " +
"pc_matric=@pc_matric, pc_email=@pc_email, pc_fone=@pc_fone, pc_cep=@pc_cep, pc_iest=@pc_iest, pc_ativo=@pc_ativo, pc_situac=@pc_situac " +
"WHERE pc_codigo=@pc_codigo";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("pc_codigo", objPartComplende.pc_codigo);
comand.Parameters.AddWithValue("pc_codpart", objPartComplende.pc_codpart);
comand.Parameters.AddWithValue("pc_nome", objPartComplende.pc_nome);
comand.Parameters.AddWithValue("pc_cnpj", objPartComplende.pc_cnpj);
comand.Parameters.AddWithValue("pc_ende", objPartComplende.pc_ende);
comand.Parameters.AddWithValue("pc_nr", objPartComplende.pc_nr);
comand.Parameters.AddWithValue("pc_compl", objPartComplende.pc_compl);
comand.Parameters.AddWithValue("pc_ibge", objPartComplende.pc_ibge);
comand.Parameters.AddWithValue("pc_cida", objPartComplende.pc_cida);
comand.Parameters.AddWithValue("pc_uf", objPartComplende.pc_uf);
comand.Parameters.AddWithValue("pc_matric", objPartComplende.pc_matric);
comand.Parameters.AddWithValue("pc_email", objPartComplende.pc_email);
comand.Parameters.AddWithValue("pc_bairro", objPartComplende.pc_bairro);
comand.Parameters.AddWithValue("pc_fone", objPartComplende.pc_fone);
comand.Parameters.AddWithValue("pc_cep", objPartComplende.pc_cep);
comand.Parameters.AddWithValue("pc_iest", objPartComplende.pc_iest);
comand.Parameters.AddWithValue("pc_ativo", objPartComplende.pc_ativo);
comand.Parameters.AddWithValue("pc_respons", objPartComplende.pc_respons);
comand.Parameters.AddWithValue("pc_situac", objPartComplende.pc_situac);
Conn.Open();
comand.ExecuteScalar();
return(true);
}
catch (Exception ex)
{
ex.ToString();
return(false);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static List <CL_TipoMov> buscaTipos(string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM tipomovtitulo";
List <CL_TipoMov> objList = new List <CL_TipoMov>();
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
objList.Add(new CL_TipoMov()
{
t_codigo = dr["t_codigo"] is DBNull ? 0 : Convert.ToInt32(dr["t_codigo"]),
t_descri = dr["t_descri"].ToString().Trim(),
t_tipo = dr["t_tipo"].ToString().Trim(),
t_acumul = dr["t_acumul"].ToString().Trim(),
t_somatot = dr["t_somatot"].ToString().Trim(),
t_ctacre = dr["t_ctacre"].ToString().Trim(),
t_ctadeb = dr["t_ctadeb"].ToString().Trim(),
});
}
dr.Close();
return(objList);
}
else
{
return(objList);
}
}
catch (Exception ex)
{
ex.ToString();
return(null);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static int buscaCod(int v_lcto, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT v_lcto FROM visitas ORDER BY v_lcto DESC LIMIT 1";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
v_lcto = Convert.ToInt16(dr["v_lcto"]);
v_lcto = v_lcto + 1;
return(v_lcto);
}
else
{
v_lcto = 0;
return(v_lcto);
}
}
else
{
v_lcto = 1;
return(v_lcto);
}
}
catch (Exception ex)
{
ex.ToString();
v_lcto = 0;
return(v_lcto);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static int buscaID(string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
int e_id = 0;
string sql = "SELECT e_id FROM entregas ORDER BY e_id DESC LIMIT 1";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
e_id = dr["e_id"] is DBNull ? 0 : Convert.ToInt32(dr["e_id"]);
e_id = e_id + 1;
return(e_id);
}
else
{
e_id = 0;
return(e_id);
}
}
else
{
e_id = 1;
return(e_id);
}
}
catch (Exception ex)
{
ex.ToString();
e_id = 0;
return(e_id);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static bool attDadosRequis(List <CL_Requis> objListRequisRetorno, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
int req_cod = 0, req_codcli = 0, req_oserv = 0;
NpgsqlCommand comand = null;
try
{
Conn.Open();
string sql = "";
foreach (CL_Requis obj in objListRequisRetorno)
{
if (obj.req_qtdade > obj.req_qtdDev)
{
sql = "UPDATE requis SET req_qtdade=@req_qtdade WHERE req_oserv=@req_oserv AND req_codcli=@req_codcli AND req_cod=@req_cod";
comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("req_qtdade", obj.req_qtdade);
comand.Parameters.AddWithValue("req_oserv", obj.req_oserv);
comand.Parameters.AddWithValue("req_codcli", obj.req_codcli);
comand.Parameters.AddWithValue("req_cod", obj.req_cod);
comand.ExecuteScalar();
req_cod = obj.req_cod;
req_oserv = obj.req_oserv;
req_codcli = obj.req_codcli;
}
}
sql = "UPDATE requis SET req_situac='R' WHERE req_oserv=@req_oserv AND req_codcli=@req_codcli AND req_cod=@req_cod";
comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("req_oserv", req_oserv);
comand.Parameters.AddWithValue("req_codcli", req_codcli);
comand.Parameters.AddWithValue("req_cod", req_cod);
comand.ExecuteScalar();
return(true);
}
catch (Exception ex)
{
ex.ToString();
return(false);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static long buscaCod(long o_cod, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT o_cod FROM oserv ORDER BY o_cod DESC LIMIT 1";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
o_cod = Convert.ToInt64(dr["o_cod"]);
o_cod = o_cod + 1;
return(o_cod);
}
else
{
o_cod = 0;
return(o_cod);
}
}
else
{
o_cod = 1;
return(o_cod);
}
}
catch (Exception ex)
{
ex.ToString();
o_cod = 0;
return(o_cod);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static List <CL_MovProposta> buscaProposta(int p_id, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM mov_proposta WHERE mp_proposta=@p_id";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("p_id", p_id);
NpgsqlDataReader dr;
List <CL_MovProposta> objList = new List <CL_MovProposta>();
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
objList.Add(new CL_MovProposta()
{
mp_id = Convert.ToInt32(dr["mp_id"]),
mp_perda = Convert.ToDouble(dr["mp_perda"]),
mp_area = Convert.ToDouble(dr["mp_area"]),
mp_nome = dr["mp_nome"].ToString().Trim(),
mp_numsinistro = dr["mp_numsinistro"].ToString(),
});
}
dr.Close();
return(objList);
}
else
{
return(null);
}
}
catch (Exception ex)
{
ex.ToString();
return(null);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static CL_Composic buscaComposicf(CL_Composic objComposic, string con)
{
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string sql = "SELECT * FROM composic WHERE com_cod=@com_cod AND com_codf=@com_codf";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("com_cod", objComposic.com_cod);
comand.Parameters.AddWithValue("com_codf", objComposic.com_codf);
NpgsqlDataReader dr;
try
{
Conn.Open();
dr = comand.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
objComposic.com_famil = dr["com_famil"].ToString().Trim();
objComposic.com_linha = dr["com_linha"].ToString().Trim();
objComposic.com_modelo = dr["com_modelo"].ToString().Trim();
return(objComposic);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
catch (Exception ex)
{
ex.ToString();
return(null);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}