public IList <ITransactionType> GetTransactionTypes(ITransactionTypeFilter filter)
{
SqlQueryBuilder sqlQueryBuilder = new SqlQueryBuilder();
sqlQueryBuilder.Select = " SELECT * ";
sqlQueryBuilder.From = " FROM [dbo].[TransactionType] ";
if (filter.Id.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Id = @Id", Param = new SqlParameter("@Id", filter.Id.Value)
});
}
if (filter.Income.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Income = @Income", Param = new SqlParameter("@Income", filter.Income.Value)
});
}
if (!string.IsNullOrWhiteSpace(filter.Color))
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Color = @Color", Param = new SqlParameter("@Color", filter.Color)
});
}
if (!string.IsNullOrWhiteSpace(filter.Name))
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Name = @Name", Param = new SqlParameter("@Name", filter.Name)
});
}
var table = SqlService.GetDataTable(sqlQueryBuilder);
var myEnumerable = table.AsEnumerable();
return((from item in myEnumerable select new TransactionType {
Income = item.Field <bool>("Income"),
Color = item.Field <string>("Color"),
Name = item.Field <string>("Name"),
Description = item.Field <string>("Description"),
Id = item.Field <int>("Id")
}).ToList <ITransactionType>());
}
public IList <ITransaction> GetTransactions(ITransactionFilter filter)
{
SqlQueryBuilder sqlQueryBuilder = new SqlQueryBuilder();
sqlQueryBuilder.Select = " SELECT * ";
sqlQueryBuilder.From = " FROM [dbo].[Transaction] ";
if (filter.Id.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Id = @Id", Param = new SqlParameter("@Id", filter.Id.Value)
});
}
if (filter.CustomerId.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "CustomerId = @CustomerId", Param = new SqlParameter("@CustomerId", filter.CustomerId.Value)
});
}
if (filter.TransactionTypeId.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "TransactionTypeId = @TransactionTypeId", Param = new SqlParameter("@TransactionTypeId", filter.TransactionTypeId.Value)
});
}
if (!string.IsNullOrWhiteSpace(filter.Name))
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Name = @Name", Param = new SqlParameter("@Name", filter.Name)
});
}
if (filter.Value.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Value = @Value", Param = new SqlParameter("@Value", filter.Value.Value)
});
}
if (filter.ValueMin.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Value >= @ValueMin", Param = new SqlParameter("@ValueMin", filter.ValueMin.Value)
});
}
if (filter.ValueMax.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Value <= @ValueMax", Param = new SqlParameter("@ValueMax", filter.ValueMax.Value)
});
}
if (filter.Date.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Date = @Date", Param = new SqlParameter("@Date", filter.Date.Value)
});
}
if (filter.DateMin.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Date >= @DateMin", Param = new SqlParameter("@DateMin", filter.DateMin.Value)
});
}
if (filter.DateMax.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Date <= @DateMax", Param = new SqlParameter("@DateMax", filter.DateMax.Value)
});
}
var table = SqlService.GetDataTable(sqlQueryBuilder);
var myEnumerable = table.AsEnumerable();
var result = (from item in myEnumerable select new Transaction {
Id = item.Field <int>("Id"),
Name = item.Field <string>("Name"),
Description = item.Field <string>("Description"),
Value = item.Field <decimal>("Value"),
TransactionTypeId = item.Field <int>("TransactionTypeId"),
CustomerId = item.Field <int?>("CustomerId"),
Date = item.Field <DateTime>("Date")
}).ToList <ITransaction>();
var ttService = new TransactionTypeService();
var tts = ttService.GetTransactionTypes();
var cService = new CustomerService();
var cs = cService.GetCustomers();
foreach (var trans in result)
{
trans.TransactionType = tts.Where(tt => tt.Id == trans.TransactionTypeId).FirstOrDefault();
if (trans.CustomerId.HasValue)
{
trans.Customer = cs.Where(c => c.Id == trans.CustomerId.Value).FirstOrDefault();
}
}
return(result);
}
public static DataTable GetDataTable(SqlQueryBuilder query)
{
return(GetDataTable(query.GetSql, query.GetParams));
}