static public List <Library.TermoCompromissoParcela> GetParcelasAtrasadasById(long idCliente)
{
NpgsqlDataReader rdr = null;
NpgsqlConnection conexao = null;
NpgsqlCommand comando = null;
try
{
conexao = new NpgsqlConnection(global::Connection.Connection.StringPostgree());
comando = new NpgsqlCommand();
string query = "SELECT tcp.data, tcp.id, tcp.idTermoCompromisso, tcp.valor, tcp.pago FROM \"Cliente\" AS c " +
"INNER JOIN \"TermoCompromisso\" AS tc ON c.id = tc.idCliente " +
"INNER JOIN \"TermoCompromissoParcela\" AS tcp ON tcp.idTermoCompromisso = tc.id " +
"WHERE (c.id = " + idCliente + ") AND (tcp.pago = 0) AND (tcp.data < NOW())";
comando.CommandText = query;
comando.Connection = conexao;
conexao.Open();
rdr = comando.ExecuteReader();
List <Library.TermoCompromissoParcela> termos = new List <Library.TermoCompromissoParcela>();
while (rdr.Read())
{
DateTime date = DateTime.MinValue;
DateTime.TryParse(rdr["data"].ToString(), out date);
Library.TermoCompromissoParcela termoCompromissoParcela = new TermoCompromissoParcela();
termoCompromissoParcela.Id = (long)rdr["id"];
termoCompromissoParcela.TermoCompromisso = Library.TermoCompromissoBD.FindById((long)rdr["idTermoCompromisso"]);
termoCompromissoParcela.Valor = (double)rdr["valor"];
termoCompromissoParcela.Data = date;
termoCompromissoParcela.Pago = (int)rdr["pago"];
termos.Add(termoCompromissoParcela);
}
return(termos);
}
catch (Exception ex)
{
Library.Classes.Logger.Error(ex);
}
finally
{
conexao.Close();
comando.Dispose();
}
return(null);
}
public void SalvarTermoCompromissoParcela(Library.TermoCompromisso termo)
{
for (int i = 0; i < dataGridViewResultado.Rows.Count; i++)
{
//dataGridViewResultado.Rows[i];
Library.TermoCompromissoParcela tcp = new Library.TermoCompromissoParcela();
tcp.Valor = (double)dataGridViewResultado.Rows[i].Cells[0].Value;
tcp.Data = DateTime.Parse(dataGridViewResultado.Rows[i].Cells[1].Value.ToString());
tcp.TermoCompromisso = termo;
Library.TermoCompromissoParcelaBD.Save(tcp);
}
}
static public Library.TermoCompromissoParcela FindById(long idTermoCompromissoParcela)
{
NpgsqlConnection conexao = null;
NpgsqlDataAdapter dap = null;
DataSet ds = null;
//Library.TermoCompromissoParcela TermoCompromissoParcela = null;
try
{
conexao = new NpgsqlConnection(global::Connection.Connection.StringPostgree());
dap = new NpgsqlDataAdapter("SELECT * FROM \"TermoCompromissoParcela\" WHERE id='" + idTermoCompromissoParcela + "'", conexao);
ds = new DataSet();
dap.Fill(ds, "TermoCompromissoParcela");
if (ds.Tables["TermoCompromissoParcela"].Rows.Count == 1)
{
DateTime date = DateTime.MinValue;
DateTime.TryParse(ds.Tables["TermoCompromissoParcela"].Rows[0]["data"].ToString(), out date);
Library.TermoCompromissoParcela termoCompromissoParcela = new TermoCompromissoParcela();
termoCompromissoParcela.Id = (long)ds.Tables["TermoCompromissoParcela"].Rows[0]["id"];
termoCompromissoParcela.TermoCompromisso = Library.TermoCompromissoBD.FindById((long)ds.Tables["TermoCompromissoParcela"].Rows[0]["idTermoCompromisso"]);
termoCompromissoParcela.Valor = (double)ds.Tables["TermoCompromissoParcela"].Rows[0]["valor"];
termoCompromissoParcela.Data = date;
termoCompromissoParcela.Pago = (int)ds.Tables["TermoCompromissoParcela"].Rows[0]["pago"];
return(termoCompromissoParcela);
}
else
{
return(null);
}
}
catch (Exception ex)
{
Library.Classes.Logger.Error(ex);;
}
finally
{
conexao.Close();
dap.Dispose();
ds.Dispose();
}
return(null);
}
static public List <Library.TermoCompromissoParcela> FindAdvanced(params Library.Classes.QItem[] args)
{
NpgsqlDataReader rdr = null;
NpgsqlConnection conexao = null;
try
{
conexao = new NpgsqlConnection(global::Connection.Connection.StringPostgree());
using (NpgsqlCommand comando = new NpgsqlCommand())
{
string query = "SELECT * FROM \"TermoCompromissoParcela\" AS tcp "
+ "INNER JOIN \"TermoCompromisso\" AS tc ON tc.id = tcp.idTermoCompromisso ";
int p = 0;
string pre = "";
foreach (Library.Classes.QItem qi in args)
{
if (p == 0)
{
pre = "WHERE ";
}
else
{
pre = "AND ";
}
p++;
switch (qi.Campo)
{
case "tcp.id":
query += pre + "(tcp.id = @id)";
comando.Parameters.AddWithValue("@id", qi.Objeto);
break;
case "tc.id":
query += pre + "(tc.id = @idTermo)";
comando.Parameters.AddWithValue("@idTermo", qi.Objeto);
break;
case "ORDER BY":
query += " ORDER BY " + qi.Objeto;
break;
}
}
comando.CommandText = query;
comando.Connection = conexao;
conexao.Open();
rdr = comando.ExecuteReader();
}
List <Library.TermoCompromissoParcela> TermoCompromissoParcelas = new List <Library.TermoCompromissoParcela>();
while (rdr.Read())
{
DateTime date = DateTime.MinValue;
DateTime.TryParse(rdr["data"].ToString(), out date);
Library.TermoCompromissoParcela TermoCompromissoParcela = new TermoCompromissoParcela();
TermoCompromissoParcela.Id = (long)rdr["id"];
TermoCompromissoParcela.TermoCompromisso = Library.TermoCompromissoBD.FindById((long)rdr["idTermoCompromisso"]);
TermoCompromissoParcela.Valor = (double)rdr["valor"];
TermoCompromissoParcela.Data = date;
TermoCompromissoParcela.Pago = (int)rdr["pago"];
TermoCompromissoParcelas.Add(TermoCompromissoParcela);
}
return(TermoCompromissoParcelas);
}
catch (Exception ex)
{
Library.Classes.Logger.Error(ex);;
}
finally
{
if (conexao != null)
{
conexao.Close();
}
}
return(null);
}