public PWMsModel GetPWM(PWMsFilter filter)
{
string searchTextQuery = "";
string subquery = "";
string filterQuery = "";
string CountTextQuery = "";
if (!string.IsNullOrWhiteSpace(filter.SearchText))
{
searchTextQuery = " P.ProductName like '%" + filter.SearchText + "%' or P.Category like '%" + filter.SearchText + "%' or P.SubCategory like '%" + filter.SearchText + "%' or c.Quantity like '%" + filter.SearchText + "%' or W.WarehouseName like '%" + filter.SearchText + "%' and ";
CountTextQuery = " where P.ProductName like '%" + filter.SearchText + "%' or P.Category like '%" + filter.SearchText + "%' or P.SubCategory like '%" + filter.SearchText + "%' or c.Quantity like '%" + filter.SearchText + "%' or W.WarehouseName like '%" + filter.SearchText + "%' ";
}
string rawQuery = @"
declare @pagesize int
declare @pageno int
set @pagesize = " + filter.UnitPerPage + @"
set @pageno = " + filter.PageNumber + @"
declare @pagestart int
set @pagestart=(@pageno-1)* @pagesize
select TOP (@pagesize) c.*, P.*, W.* FROM ProductWarehouseMaps c
inner join Products P on P.ProductId=C.productId
inner join Warehouses W on W.WarehouseId=C.WarehouseId
where {1}{2} c.Id NOT IN(Select TOP (@pagestart) Id from ProductWarehouseMaps {0})
{0}
";
string CountQuery = string.Format("Select * from ProductWarehouseMaps c {0}", CountTextQuery);
rawQuery = string.Format(rawQuery, subquery, searchTextQuery, filterQuery);
int TotalCount = 0;
List <PWMvm> dsResult = new List <PWMvm>();
try
{
var ctx = DataContext.getInstance();
dsResult = context.Database.SqlQuery <PWMvm>(rawQuery, new object[] { }).ToList <PWMvm>();
TotalCount = ctx.ProductWarehouseMap.SqlQuery(CountQuery).ToList().Count;
}
catch (Exception ex)
{
}
PWMsModel pwmsModel = new PWMsModel();
pwmsModel.PWMList = dsResult;
//context.Dispose();
pwmsModel.TotalCount = TotalCount;
return(pwmsModel);
}
public ActionResult LoadPWMList(PWMsFilter filter)
{
if (filter.WarehouseId == "-1" || filter.WarehouseId == null)
{
filter.WarehouseId = "";
}
if (filter.PageNumber == 0)
{
filter.PageNumber = 1;
}
filter.UnitPerPage = 12;
if (filter.PageNumber == null || filter.PageNumber == 0)
{
filter.PageNumber = 1;
}
PWMsModel pwmsList = productsFacade.GetPWM(filter);
ViewBag.OutOfNumber = pwmsList.TotalCount;
if ((int)ViewBag.OutOfNumber == 0)
{
ViewBag.Message = "No Content Available !";
}
if (@ViewBag.OutOfNumber == 0)
{
filter.PageNumber = 1;
}
ViewBag.PageNumber = filter.PageNumber;
if ((int)ViewBag.PageNumber * filter.UnitPerPage > (int)ViewBag.OutOfNumber)
{
ViewBag.CurrentNumber = (int)ViewBag.OutOfNumber;
}
else
{
ViewBag.CurrentNumber = (int)ViewBag.PageNumber * filter.UnitPerPage;
}
ViewBag.PageCount = Math.Ceiling((double)ViewBag.OutOfNumber / filter.UnitPerPage.Value);
pwmsList.PWMList = pwmsList.PWMList.Where(x => x.WarehouseId.ToString().Contains(filter.WarehouseId)).ToList();
return(View(pwmsList.PWMList));
}
