static public List <Library.VendaParcela> GetParcelasAtrasadasById(long idCliente) { SqlDataReader rdr = null; SqlConnection conexao = null; SqlCommand comando = null; try { conexao = new SqlConnection(global::Connection.Connection.String()); comando = new SqlCommand(); string query = "SELECT vp.id, vp.idVenda, vp.data, vp.pago, vp.valor " + "FROM Cliente as c " + "INNER JOIN Venda as v on c.id = v.idCliente " + "INNER JOIN VendaParcela as vp on vp.idVenda = v.id " + "WHERE (c.id = " + idCliente + ") and (vp.pago = 0) and (CONVERT(varchar,vp.data, 23) < CONVERT(varchar,GETDATE(),23 ))"; comando.CommandText = query; comando.Connection = conexao; conexao.Open(); rdr = comando.ExecuteReader(); List <Library.VendaParcela> vendaParcelas = new List <Library.VendaParcela>(); while (rdr.Read()) { Library.VendaParcela vendaParcela = new VendaParcela(); vendaParcela.Id = (long)rdr["id"]; vendaParcela.Venda = Library.VendaBD.FindVendaById((long)rdr["idVenda"]); vendaParcela.Data = (DateTime)rdr["data"]; vendaParcela.Pago = (int)rdr["pago"]; vendaParcela.Valor = (decimal)rdr["valor"]; vendaParcelas.Add(vendaParcela); } return(vendaParcelas); } catch (Exception ex) { Library.Diagnostics.Logger.Error(ex); } finally { conexao.Close(); comando.Dispose(); } return(null); }
static public Library.VendaParcela FindById(long id) { SqlConnection conexao = null; SqlDataAdapter dap = null; DataSet ds = null; try { conexao = new SqlConnection(global::Connection.Connection.String()); dap = new SqlDataAdapter("SELECT * FROM VendaParcela WHERE id= " + id, conexao); ds = new DataSet(); dap.Fill(ds, "VendaParcela"); Library.VendaParcela vendaParcela = new VendaParcela(); if (ds.Tables["VendaParcela"].Rows.Count == 1) { vendaParcela.Id = (long)ds.Tables["VendaParcela"].Rows[0]["id"]; vendaParcela.Venda = Library.VendaBD.FindById((long)ds.Tables["VendaParcela"].Rows[0]["idVenda"]); vendaParcela.Vencimento = (DateTime)ds.Tables["VendaParcela"].Rows[0]["vencimento"]; DateTime data = DateTime.MinValue; DateTime.TryParse(ds.Tables["VendaParcela"].Rows[0]["pagamento"].ToString(), out data); vendaParcela.Pagamento = data; vendaParcela.Pago = (int)ds.Tables["VendaParcela"].Rows[0]["pago"]; vendaParcela.Valor = (decimal)ds.Tables["VendaParcela"].Rows[0]["valor"]; vendaParcela.ValorPago = (decimal)ds.Tables["VendaParcela"].Rows[0]["valorPago"]; } return(vendaParcela); } catch (Exception ex) { Library.Diagnostics.Logger.Error(ex); } finally { conexao.Close(); dap.Dispose(); ds.Dispose(); } return(null); }
static public List <Library.VendaParcela> FindAdvanced(params Library.Classes.QItem[] args) { SqlDataReader rdr = null; SqlConnection conexao = null; SqlCommand comando = null; try { conexao = new SqlConnection(global::Connection.Connection.String()); comando = new SqlCommand(); string query = "SELECT * FROM VendaParcela AS vp "; query += "INNER JOIN Venda AS v ON vp.idVenda = v.id "; 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 "vp.id": query += pre + "vp.id = @id"; comando.Parameters.AddWithValue("@id", qi.Objeto); break; case "vp.idVenda": query += pre + "vp.idVenda = @idVenda"; comando.Parameters.AddWithValue("@idVenda", qi.Objeto); break; case "vp.data": query += pre + "vp.data = @data"; comando.Parameters.AddWithValue("@data", qi.Objeto); break; case "vp.pago": query += pre + "vp.pago = @pago"; comando.Parameters.AddWithValue("@pago", qi.Objeto); break; case "vp.valor": query += pre + "vp.valor = @valor"; comando.Parameters.AddWithValue("@valor", qi.Objeto); break; case "v.id": query += pre + "v.id = @id"; comando.Parameters.AddWithValue("@id", 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.VendaParcela> vendaParcelas = new List <Library.VendaParcela>(); while (rdr.Read()) { Library.VendaParcela vendaParcela = new VendaParcela(); vendaParcela.Id = (long)rdr["id"]; vendaParcela.Venda = Library.VendaBD.FindVendaById((long)rdr["idVenda"]); vendaParcela.Data = (DateTime)rdr["data"]; vendaParcela.Pago = (int)rdr["pago"]; vendaParcela.Valor = (decimal)rdr["valor"]; vendaParcelas.Add(vendaParcela); } return(vendaParcelas); } catch (Exception ex) { Library.Diagnostics.Logger.Error(ex); } finally { conexao.Close(); comando.Dispose(); } return(null); }