public static IQueryable<vw_Claim_Dashboard> PrepareQuery(IQueryable<vw_Claim_Dashboard> dasQ, vw_Claim_Dashboard das)
{
#region Append WHERE clause if applicable
dasQ = dasQ.Where(o => o.Archived == das.Archived);
if (!string.IsNullOrEmpty(das.ClaimNos))// Filter for multiple Claim No.s
{
int SingleClaimNo = -1;
if (int.TryParse(das.ClaimNos, out SingleClaimNo))
dasQ = dasQ.Where(o => SqlMethods.Like(o.ClaimNo.ToString(), "%" + SingleClaimNo.ToString() + "%"));
else
dasQ = dasQ.Where(o => Defaults.stringToIntList(das.ClaimNos).Contains(o.ClaimNo));
}
if (!string.IsNullOrEmpty(das.CustRefNo))
dasQ = dasQ.Where(o => SqlMethods.Like(o.CustRefNo.ToLower(), das.CustRefNo.ToLower()));
if (das.BrandID > 0) dasQ = dasQ.Where(o => o.BrandID == das.BrandID);
else if (!string.IsNullOrEmpty(das.BrandName))
dasQ = dasQ.Where(o => SqlMethods.Like(o.BrandName.ToLower(), "%" + das.BrandName.ToLower() + "%"));
if (das.StatusID > 0) dasQ = dasQ.Where(o => o.StatusID == das.StatusID);
else if (!string.IsNullOrEmpty(das.Status))
dasQ = dasQ.Where(o => SqlMethods.Like(o.Status.ToLower(), das.Status.ToLower()));
if (das.AssignedTo > 0) dasQ = dasQ.Where(o => o.AssignedTo == das.AssignedTo);
if (das.VendorID > 0) dasQ = dasQ.Where(o => o.VendorID == das.VendorID);
if (das.CustID > 0) dasQ = dasQ.Where(o => o.CustID == das.CustID);
else if (!string.IsNullOrEmpty(das.CustOrg))
dasQ = dasQ.Where(o => SqlMethods.Like(o.CustOrg.ToLower(), das.CustOrg.ToLower()));
if (das.SalespersonID > 0) dasQ = dasQ.Where(o => o.SalespersonID == das.SalespersonID);
else if (!string.IsNullOrEmpty(das.Salesperson))
dasQ = dasQ.Where(o => SqlMethods.Like(o.Salesperson.ToLower(), das.Salesperson.ToLower()));
if (das.ShipToLocationID > 0) dasQ = dasQ.Where(o => o.ShipToLocationID == das.ShipToLocationID);
else if (!string.IsNullOrEmpty(das.ShipToLoc)) dasQ = dasQ.Where
(o => SqlMethods.Like(o.ShipToLoc.ToLower(), das.ShipToLoc.ToLower()));
//Apply date filter
//http://www.filamentgroup.com/lab/date_range_picker_using_jquery_ui_16_and_jquery_ui_css_framework/
if (das.ClaimDateFrom.HasValue)
dasQ = dasQ.Where(o => o.ClaimDate.Date >= das.ClaimDateFrom_SQL.Value.Date);
if (das.ClaimDateTo.HasValue)
dasQ = dasQ.Where(o => o.ClaimDate.Date <= das.ClaimDateTo_SQL.Value.Date);
#endregion
return dasQ;
}
public DashboardNav(string NavString)
{
Alias = new string[]
{
"dashboard","dashbaord", "dshboard", "dasboard", "dashboad",
"search", "sarch", "searh", "seerch", "search",
"claims", "clais", "caims",
"all", "al"
};
filter = new vw_Claim_Dashboard();
Data = NavString;
}
public List<ClaimRpt.BrandwiseClaim> GetBrandClaimCount(vw_Claim_Dashboard das)
{
List<vw_Claim_Dashboard> dasList = SearchClaim(claimSortOn, das);
List<ClaimRpt.BrandwiseClaim> dasCountLst = new List<ClaimRpt.BrandwiseClaim>(dasList.Count);
//Get grouped data
var grouped = from dl in dasList
group dl by new { dl.BrandID, dl.BrandName } into g
orderby g.Key.BrandName
select new { BrandID = g.Key.BrandID, Brand = g.Key.BrandName, Count = g.Count() };
//Populate UserCount list
foreach (var u in grouped)
dasCountLst.Add(new ClaimRpt.BrandwiseClaim() { BrandID = u.BrandID, Brand = u.Brand, Count = u.Count });
return dasCountLst;
}
public List<vw_Claim_Dashboard> SearchClaim(string orderBy, vw_Claim_Dashboard das)
{
orderBy = string.IsNullOrEmpty(orderBy) ? userSortOn : orderBy;
using (dbc)
{
IQueryable<vw_Claim_Dashboard> dasQ = (from vw_u in dbc.vw_Claim_Dashboards select vw_u);
//Get filters - if any
dasQ = DashboardService.PrepareQuery(dasQ, das);
// Apply Sorting, Pagination and return PagedList
return dasQ.OrderBy(orderBy).ToList();
}
}
public List<ClaimRpt.StatuswiseClaim> GetStatusClaimCount(vw_Claim_Dashboard das)
{
List<vw_Claim_Dashboard> dasList = SearchClaim(claimSortOn, das);
List<ClaimRpt.StatuswiseClaim> dasCountLst = new List<ClaimRpt.StatuswiseClaim>(dasList.Count);
//Get grouped data
var grouped = from ul in dasList
group ul by new { ul.StatusID, ul.Status } into g
orderby g.Key.Status
select new { StatusID = g.Key.StatusID, Status = g.Key.Status, Count = g.Count() };
//Populate UserCount list
foreach (var u in grouped)
dasCountLst.Add(new ClaimRpt.StatuswiseClaim() { StatusID = u.StatusID, Status = u.Status, Count = u.Count });
return dasCountLst;
}
public List<ClaimRpt.CustwiseClaim> GetCustClaimCount(vw_Claim_Dashboard das)
{
List<vw_Claim_Dashboard> dasList = SearchClaim(claimSortOn, das);
List<ClaimRpt.CustwiseClaim> dasCountLst = new List<ClaimRpt.CustwiseClaim>(dasList.Count);
//Get grouped data
var grouped = from dl in dasList
group dl by new { dl.CustID, dl.CustOrg } into g
orderby g.Key.CustOrg
select new { CustID = g.Key.CustID, Cust = g.Key.CustOrg, Count = g.Count() };
//Populate UserCount list
foreach (var u in grouped)
dasCountLst.Add(new ClaimRpt.CustwiseClaim() { CustID = u.CustID, Cust = u.Cust, Count = u.Count });
return dasCountLst;
}
public ActionResult SetSearchOpts(vw_Claim_Dashboard searchObj)
{
if (searchObj != null)
{//Called only to set filter via ajax
searchOpts = searchObj;
return Json(true);
}
return Json(false);
}
public List<vw_Claim_Dashboard> Search(string orderBy, int? pgIndex, int pageSize, vw_Claim_Dashboard das, bool isExcelReport, bool applyLocFilter)
{
orderBy = string.IsNullOrEmpty(orderBy) ? sortOn : orderBy;
using (dbc)
{
IQueryable<vw_Claim_Dashboard> dasQ;
#region Special case for customer (apply accessible location filter)
if (!applyLocFilter)
dasQ = (from vw_u in dbc.vw_Claim_Dashboards select vw_u);
else // only for customer
dasQ = (from vw_u in dbc.vw_Claim_Dashboards
join ul in dbc.UserLocations on new { LocID = vw_u.ShipToLocationID } equals new { LocID = ul.LocID }
where ul.UserID == _SessionUsr.ID
select vw_u);
#endregion
//Get filters - if any
dasQ = PrepareQuery(dasQ, das);
// Apply Sorting, Pagination and return PagedList
#region Sort and Return result
//Special case to replace Customproperty with original (for ShipToLoc)
// For better implementation: SO: 2241643/how-to-use-a-custom-property-in-a-linq-to-entities-query
orderBy = (orderBy ?? "").Replace("ShipToLocAndCode", "ShipToLoc");
if (isExcelReport)
return dasQ.OrderBy(orderBy).ToList<vw_Claim_Dashboard>();
else
return dasQ.OrderBy(orderBy).ToPagedList(pgIndex ?? 1, pageSize);
/* Apply pagination and return - kept for future ref
return dasQ.OrderBy(orderBy).Skip(pgIndex.Value).Take(pageSize).ToList<vw_Claim_Dashboard>();
*/
#endregion
}
}
public JsonResult ClaimListKO(int? index, string qData, bool? fetchAll)
{
base.SetTempDataSort(ref index);// Set TempDate, Sort & index
//Make sure searchOpts is assigned to set ViewState
vw_Claim_Dashboard oldSearchOpts = (vw_Claim_Dashboard)searchOpts;
searchOpts = new vw_Claim_Dashboard() { Archived = oldSearchOpts.Archived };// CAUTION: otehrwise archived saved search will show null records
populateData(false);
index = (index > 0) ? index + 1 : index; // paging starts with 2
var result = from vw_u in new DashboardService().SearchKO(
sortExpr, index, gridPageSize * 2, (vw_Claim_Dashboard)searchOpts, fetchAll ?? false, _Session.IsOnlyCustomer)
select new
{
ID = vw_u.ID,
CNo = vw_u.ClaimNo,
StatusID = vw_u.StatusID,
AsgnTo = vw_u.AssignToName,
CustRef = vw_u.CustRefNo,
Brand = vw_u.BrandName,
CustOrg = vw_u.CustOrg,
SP = vw_u.Salesperson,
CDate = vw_u.ClaimDateOnly,
Archvd = vw_u.Archived,
Status = vw_u.Status,
Cmts = vw_u.CommentsExist,
Files = vw_u.FilesHExist,
CDtTxt = vw_u.ClaimDateTxt,//ClaimDate.ToString(Defaults.dtFormat, Defaults.ci),
ShpLocCode = vw_u.ShipToLocAndCode
};
return Json(new { records = result, search = oldSearchOpts }, JsonRequestBehavior.AllowGet);
}
public ActionResult Excel()
{
//HttpContext context = ControllerContext.HttpContext.CurrentHandler;
//Essense of : http://stephenwalther.com/blog/archive/2008/06/16/asp-net-mvc-tip-2-create-a-custom-action-result-that-returns-microsoft-excel-documents.aspx
this.Response.Clear();
this.Response.AddHeader("content-disposition", "attachment;filename=" + "Dashboard_" + _SessionUsr.ID + ".xls");
this.Response.Charset = "";
this.Response.Cache.SetCacheability(HttpCacheability.NoCache);
this.Response.ContentType = "application/vnd.ms-excel";
//DON'T do the following
//this.Response.Write(content);
//this.Response.End();
populateData(false);
var result = new DashboardService().Search(sortExpr, 1, gridPageSize, (vw_Claim_Dashboard)searchOpts, true, _Session.IsOnlyCustomer);
searchOpts = new vw_Claim_Dashboard();
populateData(false);
return View("Excel", result);
}
public ActionResult ClaimListKO(vw_Claim_Dashboard searchObj, string doReset, string qData, bool? fetchAll)
{
searchOpts = (doReset == "on") ? new vw_Claim_Dashboard() : searchObj; // Set or Reset Search-options
populateData(false);// Populate ddl Viewdata
return Json(true);// WE just need to set it in the session
}