static public Library.Condicional FindOrcamentoById(long idOrcamento)
{
SqlConnection conexao = null;
SqlDataAdapter dap = null;
DataSet ds = null;
Library.Condicional orcamento = null;
try
{
conexao = new SqlConnection(global::Connection.Connection.String());
dap = new SqlDataAdapter("SELECT * FROM Orcamento WHERE id='" + idOrcamento + "'", conexao);
ds = new DataSet();
dap.Fill(ds, "Orcamento");
if (ds.Tables["Orcamento"].Rows.Count == 1)
{
orcamento = new Condicional();
orcamento.Id = (long)ds.Tables["Orcamento"].Rows[0]["id"];
orcamento.Cliente = Library.ClienteBD.FindById((long)ds.Tables["Orcamento"].Rows[0]["idCliente"]);
orcamento.Funcionario = Library.FuncionarioBD.FindById((long)ds.Tables["Orcamento"].Rows[0]["idFuncionario"]);
orcamento.Data = (DateTime)ds.Tables["Orcamento"].Rows[0]["data"];
orcamento.Valor = (decimal)ds.Tables["Orcamento"].Rows[0]["valor"];
}
return(orcamento);
}
catch (Exception ex)
{
Library.Diagnostics.Logger.Error(ex);;
}
finally
{
conexao.Close();
dap.Dispose();
ds.Dispose();
}
return(null);
}
static public List <Library.Condicional> 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 Orcamento AS o ";
query += "INNER JOIN Cliente AS c ON o.idCliente = c.id ";
query += "INNER JOIN Funcionario AS f ON o.idFuncionario = f.id ";
int p = 0;
string pre = "";
foreach (Library.Classes.QItem qi in args)
{
if (qi.Campo != null)
{
if (p == 0)
{
pre = "WHERE ";
}
else
{
pre = "AND ";
}
p++;
switch (qi.Campo)
{
case "o.id":
query += pre + "o.id = @id";
comando.Parameters.AddWithValue("@id", qi.Objeto);
break;
case "o.data varchar":
query += pre + "(CONVERT(varchar, o.data, 103) = '@data')";
comando.Parameters.AddWithValue("@data", qi.Objeto);
break;
case "o.formaPagamento":
query += pre + "(o.formaPagamento = @formaPagamento)";
comando.Parameters.AddWithValue("@formaPagamento", qi.Objeto);
break;
case "c.nome LIKE %%":
query += pre + "(c.nome LIKE '%' + @nome + '%')";
comando.Parameters.AddWithValue("@nome", qi.Objeto);
break;
case "dataMaior":
query += pre + "(CONVERT(varchar,o.data, 23) <= @dataMaior)";
comando.Parameters.AddWithValue("@dataMaior", qi.Objeto);
break;
case "dataMenor":
query += pre + "(CONVERT(varchar,o.data, 23) >= @dataMenor)";
comando.Parameters.AddWithValue("@dataMenor", 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.Condicional> orcamentos = new List <Library.Condicional>();
while (rdr.Read())
{
Library.Condicional orcamento = new Condicional();
orcamento.Id = (long)rdr["id"];
orcamento.Cliente = Library.ClienteBD.FindById((long)rdr["idCliente"]);
orcamento.Funcionario = Library.FuncionarioBD.FindById((long)rdr["idFuncionario"]);
orcamento.Data = (DateTime)rdr["data"];
orcamento.Valor = (decimal)rdr["valor"];
orcamentos.Add(orcamento);
}
return(orcamentos);
}
catch (Exception ex)
{
Library.Diagnostics.Logger.Error(ex);
}
finally
{
conexao.Close();
comando.Dispose();
}
return(null);
}