static public List <Library.Cheque> 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 Cheque AS cq " +
"LEFT JOIN Venda AS v ON cq.idVenda = v.id " +
"LEFT JOIN Servico AS s ON cq.idServico = s.id ";
//"INNER JOIN Venda AS v ON cq.idVenda = v.id " +
//"INNER JOIN Servico AS s ON cq.idServico = s.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 "cq.id":
query += pre + "cq.id = @id";
comando.Parameters.AddWithValue("@id", qi.Objeto);
break;
case "cq.data varchar":
query += pre + "CONVERT(varchar, cq.data, 103) = @data";
comando.Parameters.AddWithValue("@data", qi.Objeto);
break;
case "v.id":
query += pre + "v.id = @idVenda";
comando.Parameters.AddWithValue("@idVenda", qi.Objeto);
break;
case "s.id":
query += pre + "s.id = @idServico";
comando.Parameters.AddWithValue("@idServico", 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.Cheque> cheques = new List <Library.Cheque>();
while (rdr.Read())
{
Library.Cheque cheque = new Cheque();
cheque.Id = (long)rdr["id"];
cheque.Data = (DateTime)rdr["data"];
cheque.Valor = (decimal)rdr["valor"];
cheque.Numero = rdr["numero"].ToString();
cheque.Pago = (int)rdr["pago"];
if (!string.IsNullOrEmpty(rdr["idVenda"].ToString()))
{
cheque.Venda = Library.VendaBD.FindById((long)rdr["idVenda"]);
}
else
{
cheque.Venda = null;
}
cheques.Add(cheque);
}
return(cheques);
}
catch (Exception ex)
{
Library.Diagnostics.Logger.Error(ex);
}
finally
{
conexao.Close();
comando.Dispose();
}
return(null);
}