public static IEnumerable <OrdersModel> GetOrdersByRegionId(DapperService db, int regionId)
{
List <OrdersModel> items = new List <OrdersModel>();
db.ClearParams();
db.AddParam("@regionId", DbType.Int32, regionId);
string query = @" select distinct o.*, u.FirstName + ' ' + u.LastName as CustomerName from Orders o
left join Users u on u.Id = o.UserId
where o.RegionId = @regionId order by o.Id";
using (IDataReader reader = db.Select(query))
{
while (reader.Read())
{
OrdersModel ret = new OrdersModel();
ret.Id = (int)reader["Id"];
ret.Status = (int)reader["Status"];
ret.Sum = (decimal)reader["Sum"];
ret.CreatedTime = (DateTime)reader["CreatedTime"];
ret.UserId = ret.User.Id = (int)reader["UserId"];
ret.RegionId = ret.User.Id = (int)reader["RegionId"];
ret.Region.Name = RegionsService.GetRegionFullAreaName(db, ret.RegionId);
ret.User.FullName = (string)reader["CustomerName"];
items.Add(ret);
}
}
return(items);
}
public IEnumerable <OrdersModel> GetList(string itemName, string regionName, int page, ref int itemsCount)
{
List <OrdersModel> items = new List <OrdersModel>();
db.ClearParams();
db.AddParam("@page", DbType.Int32, page == 1 ? 0 : (page - 1) * Pagination.ItemsForPage);
db.AddParam("@ItemsForPage", DbType.Int32, Pagination.ItemsForPage);
string query = @" select distinct o.*, u.FirstName + ' ' + u.LastName as CustomerName from Orders o
left join Users u on u.Id = o.UserId
inner join Regions r on r.Id = o.RegionId
inner join OrderItems oi on oi.OrderId = o.Id
inner join Items i on i.Id = oi.ItemId
where (r.Name LIKE N'%" + regionName + "%' and i.Name LIKE N'%" + itemName + "%') order by o.Id OFFSET @page ROWS FETCH NEXT @ItemsForPage ROWS ONLY; select count(distinct o.Id) as ItemsCount from Orders o left join Users u on u.Id = o.UserId inner join Regions r on r.Id = o.RegionId inner join OrderItems oi on oi.OrderId = o.Id inner join Items i on i.Id = oi.ItemId where (r.Name LIKE N'%" + regionName + "%' and i.Name LIKE N'%" + itemName + "%')";
using (IDataReader reader = db.Select(query))
{
while (reader.Read())
{
OrdersModel ret = new OrdersModel();
ret.Id = (int)reader["Id"];
ret.Status = (int)reader["Status"];
ret.Sum = (decimal)reader["Sum"];
ret.CreatedTime = (DateTime)reader["CreatedTime"];
ret.UserId = ret.User.Id = (int)reader["UserId"];
ret.RegionId = ret.User.Id = (int)reader["RegionId"];
ret.Region.Name = RegionsService.GetRegionFullAreaName(db, ret.RegionId);
ret.User.FullName = (string)reader["CustomerName"];
items.Add(ret);
}
if (reader.NextResult())
{
while (reader.Read())
{
itemsCount = (int)reader["ItemsCount"];
}
}
}
return(items);
}
public OrdersModel Get(int id)
{
OrdersModel ret = null;
db.ClearParams();
db.AddParam("@Id", DbType.Int32, id);
using (IDataReader reader = db.Select(@"select o.*,u.FirstName + ' ' + u.LastName as CustomerName from Orders o
left join Users u on u.Id = o.UserId where o.Id = @Id;
select Id, Name, Description, Price from Items i where i.Id in (select o.ItemId from OrderItems o where o.OrderId = @Id) order by i.Id"))
{
while (reader.Read())
{
ret = new OrdersModel();
ret.Id = (int)reader["Id"];
ret.Status = (int)reader["Status"];
ret.Sum = (decimal)reader["Sum"];
ret.CreatedTime = (DateTime)reader["CreatedTime"];
ret.UserId = ret.User.Id = (int)reader["UserId"];
ret.RegionId = ret.Region.Id = (int)reader["RegionId"];
ret.Region.Name = RegionsService.GetRegionFullAreaName(db, ret.RegionId);
ret.User.FullName = (string)reader["CustomerName"];
}
if (reader.NextResult())
{
while (reader.Read())
{
ret.Items.Add(new ItemsModel {
Id = (int)reader["Id"],
Name = reader["Name"] as string,
Description = reader["Description"] as string,
Price = (decimal)reader["Price"],
});
}
}
}
return(ret);
}