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