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); }