public JsonResult GetAlertBatchItems()
{
var navall = _context.AlertJobsNav.AsNoTracking().FromSql($"usp_AlertGetNavigation_sel").ToList();
var NavData = (from byJob in navall
//group byJob by new { byJob.Job, byJob.AlertJobsQueueID } into Grouping
group byJob by byJob.Job into Grouping
//orderby Grouping.Key
select new
{
label = Grouping.Key,
children = from byDueday in Grouping
group byDueday by
new { DueDay = byDueday.Due.ToString("MM/dd/yyyy"), byDueday.AlertJobsQueueID }
into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key.DueDay + " (" + SubGrouping.Count() + ")",
data = new
{
Due = SubGrouping.Key.DueDay,
AlertJobQueueID = SubGrouping.Key.AlertJobsQueueID,
Job = Grouping.Key
}
}
}).ToList();
return(Json(NavData));
}
/// <summary>
/// This is the left navigation pane for Alerts.
/// Items are grouped by Job, then by Day
/// </summary>
/// <returns></returns>
public object GetAlertBatchItems()
{
var navall = _context.AlertJobsNav.AsNoTracking().FromSql($"usp_AlertGetNavigation_sel").ToList();
var NavData = (from byJob in navall
group byJob by(byJob.Job) into Grouping
select new
{
label = Grouping.Key,
//AlertJobQueueID = Grouping.Key.AlertJobsQueueID,
//AlertJobQueueID = Grouping.Key.get
children = from byDueday in Grouping
group byDueday by
new { DueDay = byDueday.Due.ToString("MM/dd/yyyy"), byDueday.AlertJobsQueueID }
into SubGrouping
select new
{
label = SubGrouping.Key.DueDay + " (" + SubGrouping.Count() + ")",
data = new
{
Due = SubGrouping.Key.DueDay,
AlertJobQueueID = SubGrouping.Key.AlertJobsQueueID,
Grouping.Key
}
}
});
return(NavData);
}
public JsonResult GetNewsNavigation()
{
var NewsDatePeriod = _configuration.GetSection("EditorialSettings:NewsDatePeriod").Value;
IQueryable <NewsJobsNav> navall = _context.NewsJobsNav.AsNoTracking().FromSql($"usp_NewsGetNavigation_sel {NewsDatePeriod}")
.Select(navdata => new NewsJobsNav
{
WatchID = navdata.WatchID,
Caption = navdata.Caption,
CountryName = navdata.CountryName,
CountryID = navdata.CountryID,
LanguageID = navdata.LanguageID,
LanguageName = navdata.LanguageName,
});
var newsnavdata = (from bycountry in navall
group bycountry by new { bycountry.CountryName, bycountry.CountryID } into Grouping
select new
{
label = Grouping.Key.CountryName + "(" + Grouping.Count() + ")",
children = from bylanguage in Grouping
group bylanguage by new { bylanguage.LanguageID, bylanguage.LanguageName } into SubGrouping
select new
{
label = SubGrouping.Key.LanguageName + "(" + SubGrouping.Count() + ")",
children = from byname in SubGrouping
group byname by new { byname.Caption, byname.WatchID } into SubSubGrouping
select new
{
label = SubSubGrouping.Key.Caption + " (" + SubSubGrouping.Count() + ")",
data = new
{
CountryName = Grouping.Key.CountryName,
CountryID = Grouping.Key.CountryID,
LanguageName = SubGrouping.Key.LanguageName,
LanguageID = SubGrouping.Key.LanguageID,
Caption = SubSubGrouping.Key.Caption,
WatchID = SubSubGrouping.Key.WatchID
}
}
}
}).ToList();
// My
return(Json(newsnavdata));
}
/// <summary>
/// Left Navigation data for the Investigations page
/// This function returns the Items to be used in the collapsing menu on the left pane used for navigation
/// </summary>
/// <param name="appUserId"></param>
/// <param name="configuration"></param>
/// <returns></returns>
public object GetNavigationsWithCounts(int appUserId)
{
// All Investigation records
IQueryable <InvNavDataWithUser> barnavall = _context.InvNavDataWithUser.AsNoTracking().FromSql("usp_INVGetNavigation_sel")
.Select(navdata => new InvNavDataWithUser
{
InvestigationID = navdata.InvestigationID,
InvestigationName = navdata.InvestigationName,
Priority = navdata.Priority,
Country = navdata.Country,
Function = navdata.Function,
Disposition = navdata.Disposition,
MMMDDUsersID = navdata.MMMDDUsersID,
User = navdata.User,
DateCreatedUTC = navdata.DateCreatedUTC,
Aging = navdata.Aging
});
// ALL
// By Aging
#region By Aging
var ByAgingAll = (from byaging in barnavall
group byaging by byaging.Aging into Grouping
select new
{
label = ((Grouping.Key == 1) ? "11+ days" : "1-10 days") + " (" + Grouping.Count() + ")",
//data = (Grouping.Key == 1) ? "11+ days" : "1-10 days",
children = from subg in Grouping
group subg by subg.Country into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
//data = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
aging = Grouping.Key,
},
children = from subsubg in SubGrouping
group subsubg by subsubg.Function into SubSubGrouping
select new
{
label = SubSubGrouping.Key + " (" + SubSubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = SubSubGrouping.Key,
aging = Grouping.Key
}
}
}
});
#endregion
// By Category
#region By Category
var ByFunctionAll = (from byfunction in barnavall
group byfunction by new { byfunction.Function, byfunction.Aging } into Grouping
select new
{
label = Grouping.Key.Function + " (" + Grouping.Count() + ")",
data = new
{
categoryName = Grouping.Key.Function,
aging = Grouping.Key.Aging,
},
children = from subg in Grouping
group subg by subg.Country into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = Grouping.Key.Function,
batchName = "",
aging = Grouping.Key.Aging
}
}
});
#endregion
// By Country
#region By Country
var ByCountryAll = (from bycountry in barnavall
group bycountry by new { bycountry.Country, bycountry.Aging } into Grouping
select new
{
label = Grouping.Key.Country + " (" + Grouping.Count() + ")",
data = new
{
countryName = Grouping.Key.Country,
aging = Grouping.Key.Aging,
},
children = (from subg in Grouping
group subg by subg.Function into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key.Country,
categoryName = SubGrouping.Key,
batchName = "",
aging = 1
}
})
});
#endregion
/*
* var alldata1 = JObject.FromObject(new { label = "Country", data = "Country branch", children = ByCountryAll });
* var alldata2 = JObject.FromObject(new { label = "Category", data = "Category branch", children = ByFunctionAll });
* var alldata3 = JObject.FromObject(new { label = "Aging", data = "Aging branch", children = ByAgingAll });
* var alldata = JObject.FromObject(new { label = "All", children = new JArray(alldata3, alldata1, alldata2) });
*/
var alldata1 = JObject.FromObject(new { label = "Country", children = ByCountryAll });
var alldata2 = JObject.FromObject(new { label = "Category", children = ByFunctionAll });
var alldata3 = JObject.FromObject(new { label = "Aging", children = ByAgingAll });
var alldata = JObject.FromObject(new { label = "All", children = new JArray(alldata3, alldata1, alldata2) });
// MY WORK data
IQueryable <InvNavDataWithUser> barnavuser = _context.InvNavDataWithUser.AsNoTracking().FromSql("usp_INVGetNavigationWithUser_sel {0} ", appUserId)
.Select(navdata => new InvNavDataWithUser
{
InvestigationID = navdata.InvestigationID,
InvestigationName = navdata.InvestigationName,
Priority = navdata.Priority,
Country = navdata.Country,
Function = navdata.Function,
Disposition = navdata.Disposition,
MMMDDUsersID = navdata.MMMDDUsersID,
User = navdata.User,
DateCreatedUTC = navdata.DateCreatedUTC,
Aging = navdata.Aging
});
// By Aging
#region By Aging
var ByAgingMy = (from byaging in barnavuser
group byaging by byaging.Aging into Grouping
select new
{
label = ((Grouping.Key == 1) ? "11+ days" : "1-10 days") + " (" + Grouping.Count() + ")",
//data = (Grouping.Key == 1) ? "11+ days" : "1-10 days",
children = from subg in Grouping
group subg by subg.Country into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
aging = Grouping.Key,
appUserID = appUserId
},
//data = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
children = from subsubg in SubGrouping
group subsubg by subsubg.Function into SubSubGrouping
select new
{
label = SubSubGrouping.Key + " (" + SubSubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = SubSubGrouping.Key,
aging = Grouping.Key,
appUserID = appUserId
}
}
}
});
#endregion
// By Category
#region By Category
var ByFunctionMy = (from byfunction in barnavuser
group byfunction by new { byfunction.Function, byfunction.Aging } into Grouping
select new
{
label = Grouping.Key.Function + " (" + Grouping.Count() + ")",
data = new
{
categoryName = Grouping.Key.Function,
aging = Grouping.Key.Aging,
appUserID = appUserId
},
children = from subg in Grouping
group subg by subg.Country into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = Grouping.Key.Function,
batchName = "",
aging = Grouping.Key.Aging,
appUserID = appUserId
}
}
});
#endregion
// By Country
#region By Country
var ByCountryMy = (from bycountry in barnavuser
group bycountry by new { bycountry.Country, bycountry.Aging } into Grouping
select new
{
label = Grouping.Key.Country + " (" + Grouping.Count() + ")",
data = new
{
countryName = Grouping.Key.Country,
aging = Grouping.Key.Aging,
appUserID = appUserId
},
children = (from subg in Grouping
group subg by subg.Function into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key.Country,
categoryName = SubGrouping.Key,
batchName = "",
aging = Grouping.Key.Aging,
appUserID = appUserId
}
})
});
#endregion
var mydata1 = JObject.FromObject(new { label = "Country", children = ByCountryMy });
var mydata2 = JObject.FromObject(new { label = "Category", children = ByFunctionMy });
var mydata3 = JObject.FromObject(new { label = "Aging", children = ByAgingMy });
var mydata = JObject.FromObject(new { label = "My Work", children = new JArray(mydata3, mydata1, mydata2) });
var result = new JArray(mydata, alldata);
return(result);
}
public JsonResult GetNavWithCountsAndUser(int appuserid)
{
//IQueryable<BwqNavData> barnav = _context.BwqNavData.FromSql(@"usp_BWQGetNavigation_sel")
IQueryable <BwqNavDataWithUser> barnavall = _context.BwqNavDataWithUser.AsNoTracking().FromSql(@"usp_BWQGetNavigation_sel")
.Select(navdata => new BwqNavDataWithUser
{
CountryID = navdata.CountryID,
CountryName = navdata.CountryName,
FunctionTypeID = navdata.FunctionTypeID,
FunctionTypeName = navdata.FunctionTypeName,
MMMEntityID = navdata.MMMEntityID,
BWQID = navdata.BWQID,
BatchName = navdata.BatchName,
StatusCollectionItemID = navdata.StatusCollectionItemID,
StartDateUTC = navdata.StartDateUTC,
AppUserId = navdata.AppUserId,
Aging = navdata.Aging
});
// My Work
#region My Work
IQueryable <BwqNavDataWithUser> barnavuser = _context.BwqNavDataWithUser.AsNoTracking().FromSql($"usp_BWQGetNavigationWithUser_sel {appuserid} ")
.Select(navdata => new BwqNavDataWithUser
{
CountryID = navdata.CountryID,
CountryName = navdata.CountryName,
FunctionTypeID = navdata.FunctionTypeID,
FunctionTypeName = navdata.FunctionTypeName,
MMMEntityID = navdata.MMMEntityID,
BWQID = navdata.BWQID,
BatchName = navdata.BatchName,
StatusCollectionItemID = navdata.StatusCollectionItemID,
StartDateUTC = navdata.StartDateUTC,
AppUserId = navdata.AppUserId,
Aging = navdata.Aging
});
/*
* IQueryable<BwqNavDataWithUser> barnavuser = (from withuser in barnavall
* where withuser.AppUserId == appuserid
* select withuser);
*/
// By Category
#region By Category
var funcNavData = (from byfunction in barnavuser
group byfunction by byfunction.FunctionTypeName into Grouping
//orderby Grouping.Key
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
data = Grouping.Key,
children = from subg in Grouping
group subg by subg.CountryName into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = Grouping.Key,
batchName = "",
aging = 1,
appUserID = appuserid
}
}
});
#endregion
//By Country
#region By Country
var counNavData = (from bycountry in barnavuser
group bycountry by bycountry.CountryName into Grouping
//orderby Grouping.Key
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
children = (from subg in Grouping
group subg by subg.FunctionTypeName into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key,
categoryName = SubGrouping.Key,
batchName = "",
aging = 1,
appUserID = appuserid
}
})
});
#endregion
// By Batch
#region By Batch
var bactNavData = (from byfunction in barnavuser
group byfunction by byfunction.BatchName into Grouping
//orderby Grouping.Key
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
data = new
{
countryName = "",
categoryName = "",
batchName = Grouping.Key,
aging = 1,
appUserID = appuserid
}
});
#endregion
//By Aging
#region By Aging
var agingData = (from byaging in barnavuser
group byaging by byaging.Aging into Grouping
//orderby Grouping.Key
select new
{
label = ((Grouping.Key == 1) ? "11+ days" : "1-10 days") + " (" + Grouping.Count() + ")",
data = (Grouping.Key == 1) ? "11+ days" : "1-10 days",
children = from subg in Grouping
group subg by subg.CountryName into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
children = from subsubg in SubGrouping
group subsubg by subsubg.FunctionTypeName into SubSubGrouping
//orderby SubSubGrouping.Key
select new
{
label = SubSubGrouping.Key + " (" + SubSubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = SubSubGrouping.Key,
batchName = "",
aging = Grouping.Key,
appUserID = appuserid
}
}
}
});
#endregion
var userdata1 = JObject.FromObject(new { label = "Batch", data = "Batch branch", children = bactNavData });
var userdata2 = JObject.FromObject(new { label = "Country", data = "Country branch", children = counNavData });
var userdata3 = JObject.FromObject(new { label = "Category", data = "Category branch", children = funcNavData });
var userdata4 = JObject.FromObject(new { label = "Aging", data = "Aging branch", children = agingData });
var alluserdata = JObject.FromObject(new { label = "My Work", children = new JArray(userdata4, userdata1, userdata2, userdata3) });
#endregion
// All
#region all BWQ
// By Category
#region By Category
var funcNavDataAll = (from byfunction in barnavall
group byfunction by byfunction.FunctionTypeName into Grouping
//orderby Grouping.Key
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
data = Grouping.Key,
children = from subg in Grouping
group subg by subg.CountryName into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = Grouping.Key,
batchName = "",
aging = 1
}
}
});
#endregion
//By Country
#region By Country
var counNavDataAll = (from bycountry in barnavall
group bycountry by bycountry.CountryName into Grouping
//orderby Grouping.Key
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
children = (from subg in Grouping
group subg by subg.FunctionTypeName into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key,
categoryName = SubGrouping.Key,
batchName = "",
aging = 1
}
})
});
#endregion
// By Batch
#region By Batch
var bactNavDataAll = (from byfunction in barnavall
group byfunction by byfunction.BatchName into Grouping
//orderby Grouping.Key
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
data = new
{
countryName = "",
categoryName = "",
batchName = Grouping.Key,
aging = 1
}
});
#endregion
//By Aging
#region By Aging
var agingAll = (from byaging in barnavall
group byaging by byaging.Aging into Grouping
//orderby Grouping.Key
select new
{
label = ((Grouping.Key == 1) ? "11+ days" : "1-10 days") + " (" + Grouping.Count() + ")",
data = (Grouping.Key == 1) ? "11+ days" : "1-10 days",
children = from subg in Grouping
group subg by subg.CountryName into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
children = from subsubg in SubGrouping
group subsubg by subsubg.FunctionTypeName into SubSubGrouping
//orderby SubSubGrouping.Key
select new
{
label = SubSubGrouping.Key + " (" + SubSubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = SubSubGrouping.Key,
aging = Grouping.Key
}
}
}
});
#endregion
var alldata1 = JObject.FromObject(new { label = "Batch", data = "Batch branch", children = bactNavDataAll });
var alldata2 = JObject.FromObject(new { label = "Country", data = "Country branch", children = counNavDataAll });
var alldata3 = JObject.FromObject(new { label = "Category", data = "Category branch", children = funcNavDataAll });
var alldata4 = JObject.FromObject(new { label = "Aging", data = "Aging branch", children = agingAll });
var alldata = JObject.FromObject(new { label = "All", children = new JArray(alldata4, alldata1, alldata2, alldata3) });
#endregion
var t = new JArray(alluserdata, alldata);
return(Json(t));
}
/// <summary>
/// BWQ Left Navigation pane
/// SP usp_BWQGetNavigation_sel gathers information from the Editorial DB
/// Script below groups the result BY Country, Category, Batch name and Aging
/// All data shows all
/// My Work shows data for the logged in Editorial user through the appuserid varible
/// </summary>
/// <param name="appuserid"></param>
/// <returns></returns>
public object GetNavCountsWithUser(int appuserid)
{
// All Data
var barnavall = _context.BwqNavDataWithUser.AsNoTracking().FromSql($"usp_BWQGetNavigation_sel").ToList();
// By Category
#region By Category
var funcNavDataAll = (from byfunction in barnavall
group byfunction by new { byfunction.FunctionTypeName, byfunction.Aging } into Grouping
select new
{
label = Grouping.Key.FunctionTypeName + " (" + Grouping.Count() + ")",
//data = Grouping.Key.FunctionTypeName,
data = new
{
categoryName = Grouping.Key.FunctionTypeName,
aging = Grouping.Key.Aging,
},
children = from subg in Grouping
group subg by subg.CountryName into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = Grouping.Key.FunctionTypeName,
batchName = "",
aging = Grouping.Key.Aging
}
}
});
#endregion
//By Country
#region By Country
var counNavDataAll = (from bycountry in barnavall
group bycountry by new { bycountry.CountryName, bycountry.Aging } into Grouping
select new
{
label = Grouping.Key.CountryName + " (" + Grouping.Count() + ")",
data = new
{
countryName = Grouping.Key.CountryName,
aging = Grouping.Key.Aging,
},
children = (from subg in Grouping
group subg by subg.FunctionTypeName into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key.CountryName,
categoryName = SubGrouping.Key,
batchName = "",
aging = Grouping.Key.Aging,
}
})
}).OrderBy(x => x.label);
#endregion
// By Batch
#region By Batch
var bactNavDataAll = (from byfunction in barnavall
group byfunction by byfunction.BatchName into Grouping
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
data = new
{
countryName = "",
categoryName = "",
batchName = Grouping.Key,
aging = 1
}
});
#endregion
//By Aging
#region By Aging
var agingAll = (from byaging in barnavall
group byaging by byaging.Aging into Grouping
select new
{
label = ((Grouping.Key == 1) ? "11+ days" : "1-10 days") + " (" + Grouping.Count() + ")",
//data = (Grouping.Key == 1) ? "11+ days" : "1-10 days",
children = from subg in Grouping
group subg by subg.CountryName into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new {
countryName = SubGrouping.Key,
aging = Grouping.Key,
},
//data = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
children = from subsubg in SubGrouping
group subsubg by subsubg.FunctionTypeName into SubSubGrouping
select new
{
label = SubSubGrouping.Key + " (" + SubSubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = SubSubGrouping.Key,
aging = Grouping.Key
}
}
}
});
#endregion
/*
* var alldata1 = JObject.FromObject(new { label = "Batch", data = "Batch branch", children = bactNavDataAll });
* var alldata2 = JObject.FromObject(new { label = "Country", data = "Country branch", children = counNavDataAll });
* var alldata3 = JObject.FromObject(new { label = "Category", data = "Category branch", children = funcNavDataAll });
* var alldata4 = JObject.FromObject(new { label = "Aging", data = "Aging branch", children = agingAll });
*/
var alldata1 = JObject.FromObject(new { label = "Batch", children = bactNavDataAll });
var alldata2 = JObject.FromObject(new { label = "Country", children = counNavDataAll });
var alldata3 = JObject.FromObject(new { label = "Category", children = funcNavDataAll });
var alldata4 = JObject.FromObject(new { label = "Aging", children = agingAll });
var alldata = JObject.FromObject(new { label = "All", children = new JArray(alldata4, alldata1, alldata2, alldata3) });
// My Work
var barnavuser = _context.BwqNavDataWithUser.AsNoTracking().FromSql("usp_BWQGetNavigationWithUser_sel {0} ", appuserid).ToList();
// By Category
#region By Category
var funcNavData = (from byfunction in barnavuser
group byfunction by new { byfunction.FunctionTypeName, byfunction.Aging } into Grouping
select new
{
label = Grouping.Key.FunctionTypeName + " (" + Grouping.Count() + ")",
//data = Grouping.Key.FunctionTypeName,
data = new
{
categoryName = Grouping.Key.FunctionTypeName,
aging = Grouping.Key.Aging,
appUserID = appuserid
},
children = from subg in Grouping
group subg by subg.CountryName into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = Grouping.Key.FunctionTypeName,
batchName = "",
aging = Grouping.Key.Aging,
appUserID = appuserid
}
}
});
#endregion
//By Country
#region By Country
var counNavData = (from bycountry in barnavuser
group bycountry by new { bycountry.CountryName, bycountry.Aging } into Grouping
select new
{
label = Grouping.Key.CountryName + " (" + Grouping.Count() + ")",
data = new
{
countryName = Grouping.Key.CountryName,
aging = Grouping.Key.Aging,
appUserID = appuserid
},
children = (from subg in Grouping
group subg by subg.FunctionTypeName into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key.CountryName,
categoryName = SubGrouping.Key,
batchName = "",
aging = Grouping.Key.Aging,
appUserID = appuserid
}
})
});
#endregion
// By Batch
#region By Batch
var bactNavData = (from byfunction in barnavuser
group byfunction by byfunction.BatchName into Grouping
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
data = new
{
countryName = "",
categoryName = "",
batchName = Grouping.Key,
aging = 1,
appUserID = appuserid
}
});
#endregion
//By Aging
#region By Aging
var agingData = (from byaging in barnavuser
group byaging by byaging.Aging into Grouping
select new
{
label = ((Grouping.Key == 1) ? "11+ days" : "1-10 days") + " (" + Grouping.Count() + ")",
//data = (Grouping.Key == 1) ? "11+ days" : "1-10 days",
children = from subg in Grouping
group subg by subg.CountryName into SubGrouping
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
//data = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
aging = Grouping.Key,
appUserID = appuserid
},
children = from subsubg in SubGrouping
group subsubg by subsubg.FunctionTypeName into SubSubGrouping
select new
{
label = SubSubGrouping.Key + " (" + SubSubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = SubSubGrouping.Key,
batchName = "",
aging = Grouping.Key,
appUserID = appuserid
}
}
}
});
#endregion
/*
* var userdata1 = JObject.FromObject(new { label = "Batch", data = "Batch branch", children = bactNavData });
* var userdata2 = JObject.FromObject(new { label = "Country", data = "Country branch", children = counNavData });
* var userdata3 = JObject.FromObject(new { label = "Category", data = "Category branch", children = funcNavData });
* var userdata4 = JObject.FromObject(new { label = "Aging", data = "Aging branch", children = agingData });
*/
var userdata1 = JObject.FromObject(new { label = "Batch", children = bactNavData });
var userdata2 = JObject.FromObject(new { label = "Country", children = counNavData });
var userdata3 = JObject.FromObject(new { label = "Category", children = funcNavData });
var userdata4 = JObject.FromObject(new { label = "Aging", children = agingData });
var alluserdata = JObject.FromObject(new { label = "My Work", children = new JArray(userdata4, userdata1, userdata2, userdata3) });
var result = new JArray(alluserdata, alldata);
return(result);
}
/// <summary>
/// Left Navigation data for the NewsFeed page
/// </summary>
/// <param name="appuserid"></param>
/// <param name="configuration"></param>
/// <returns></returns>
public object GetNewsNavigation(int appuserid, IConfiguration configuration)
{
var NewsDatePeriod = configuration.GetSection("EditorialSettings:NewsDatePeriod").Value;
List <NewsJobsNav> navall = _context.NewsJobsNav.AsNoTracking().FromSql("usp_NewsGetNavigation_sel {0}", NewsDatePeriod)
.Select(navdata => new NewsJobsNav
{
WatchID = navdata.WatchID,
Caption = navdata.Caption,
CountryName = navdata.CountryName,
CountryID = navdata.CountryID,
LanguageID = navdata.LanguageID,
LanguageName = navdata.LanguageName,
}).ToList();
// All
#region All data
var newsnavdata = (from bycountry in navall
group bycountry by new { bycountry.CountryName, bycountry.CountryID } into Grouping
select new
{
label = Grouping.Key.CountryName + " (" + Grouping.Count() + ")",
data = new
{
countryName = Grouping.Key.CountryName,
countryID = Grouping.Key.CountryID,
},
children = from bylanguage in Grouping
group bylanguage by new { bylanguage.LanguageID, bylanguage.LanguageName } into SubGrouping
select new
{
label = SubGrouping.Key.LanguageName + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key.CountryName,
countryID = Grouping.Key.CountryID,
languageName = SubGrouping.Key.LanguageName,
languageID = SubGrouping.Key.LanguageID,
},
children = from byname in SubGrouping
group byname by new { byname.Caption, byname.WatchID } into SubSubGrouping
select new
{
label = SubSubGrouping.Key.Caption + " (" + SubSubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key.CountryName,
countryID = Grouping.Key.CountryID,
languageName = SubGrouping.Key.LanguageName,
languageID = SubGrouping.Key.LanguageID,
caption = SubSubGrouping.Key.Caption,
watchID = SubSubGrouping.Key.WatchID
}
}
}
}).ToList();
#endregion
// My Work
#region My Work
var userCountries = (from usercontry in _context.AppUserCountry
.Where(x => x.AppUserID == appuserid)
select usercontry.CountryID).ToList();
var userLanguages = (from userlangs in _context.AppUserLanguage
.Where(x => x.AppUserID == appuserid)
select userlangs.LanguageTypeID).ToList();
var newsnavdataforuser = (from navforuser in navall
where userCountries.Contains(navforuser.CountryID) ||
userLanguages.Contains(navforuser.LanguageID)
select navforuser);
var newsnavdataforusergrouped = (from bycountry in newsnavdataforuser
group bycountry by new { bycountry.CountryName, bycountry.CountryID } into Grouping
select new
{
label = Grouping.Key.CountryName + " (" + Grouping.Count() + ")",
data = new
{
countryName = Grouping.Key.CountryName,
countryID = Grouping.Key.CountryID
},
children = from bylanguage in Grouping
group bylanguage by new { bylanguage.LanguageID, bylanguage.LanguageName } into SubGrouping
select new
{
label = SubGrouping.Key.LanguageName + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key.CountryName,
countryID = Grouping.Key.CountryID,
languageName = SubGrouping.Key.LanguageName,
languageID = SubGrouping.Key.LanguageID
},
children = from byname in SubGrouping
group byname by new { byname.Caption, byname.WatchID } into SubSubGrouping
select new
{
label = SubSubGrouping.Key.Caption + " (" + SubSubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key.CountryName,
countryID = Grouping.Key.CountryID,
languageName = SubGrouping.Key.LanguageName,
languageID = SubGrouping.Key.LanguageID,
caption = SubSubGrouping.Key.Caption,
watchID = SubSubGrouping.Key.WatchID
}
}
}
}).ToList();
#endregion
var alldata = JObject.FromObject(new { label = "All", children = newsnavdata });
var alluserdata = JObject.FromObject(new { label = "My Work", children = newsnavdataforusergrouped });
var result = new JArray(alluserdata, alldata);
return(result);
}
public JsonResult GetINVWithCountsAndUser(int appuserid)
{
IQueryable <InvNavDataWithUser> barnavall = _context.InvNavDataWithUser.AsNoTracking().FromSql(@"usp_INVGetNavigation_sel")
.Select(navdata => new InvNavDataWithUser
{
InvestigationID = navdata.InvestigationID,
InvestigationName = navdata.InvestigationName,
Priority = navdata.Priority,
Country = navdata.Country,
Function = navdata.Function,
Disposition = navdata.Disposition,
MMMDDUsersID = navdata.MMMDDUsersID,
User = navdata.User,
DateCreatedUTC = navdata.DateCreatedUTC,
Aging = navdata.Aging
});
// MY WORK
IQueryable <InvNavDataWithUser> barnavuser = _context.InvNavDataWithUser.AsNoTracking().FromSql($"usp_INVGetNavigationWithUser_sel {appuserid} ")
.Select(navdata => new InvNavDataWithUser
{
InvestigationID = navdata.InvestigationID,
InvestigationName = navdata.InvestigationName,
Priority = navdata.Priority,
Country = navdata.Country,
Function = navdata.Function,
Disposition = navdata.Disposition,
MMMDDUsersID = navdata.MMMDDUsersID,
User = navdata.User,
DateCreatedUTC = navdata.DateCreatedUTC,
Aging = navdata.Aging
});
/*
* IQueryable<InvNavDataWithUser> barnavuser = (from withuser in barnavall
* where withuser.User == appuserid
* select withuser);
*
*/
// By Aging
#region By Aging
var ByAgingMy = (from byaging in barnavuser
group byaging by byaging.Aging into Grouping
//orderby Grouping.Key
select new
{
label = ((Grouping.Key == 1) ? "11+ days" : "1-10 days") + " (" + Grouping.Count() + ")",
data = (Grouping.Key == 1) ? "11+ days" : "1-10 days",
children = from subg in Grouping
group subg by subg.Country into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
children = from subsubg in SubGrouping
group subsubg by subsubg.Function into SubSubGrouping
//orderby SubSubGrouping.Key
select new
{
label = SubSubGrouping.Key + " (" + SubSubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = SubSubGrouping.Key,
aging = Grouping.Key
}
}
}
});
#endregion
// By Category
#region By Category
var ByFunctionMy = (from byfunction in barnavuser
group byfunction by byfunction.Function into Grouping
//orderby Grouping.Key
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
data = Grouping.Key,
children = from subg in Grouping
group subg by subg.Country into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = Grouping.Key,
batchName = "",
aging = 1
}
}
});
#endregion
// By Country
#region By Country
var ByCountryMy = (from bycountry in barnavuser
group bycountry by bycountry.Country into Grouping
//orderby Grouping.Key
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
children = (from subg in Grouping
group subg by subg.Function into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key,
categoryName = SubGrouping.Key,
batchName = "",
aging = 1
}
})
});
#endregion
var mydata1 = JObject.FromObject(new { label = "Country", data = "Country branch", children = ByCountryMy });
var mydata2 = JObject.FromObject(new { label = "Category", data = "Category branch", children = ByFunctionMy });
var mydata3 = JObject.FromObject(new { label = "Aging", data = "Aging branch", children = ByAgingMy });
var mydata = JObject.FromObject(new { label = "My Work", children = new JArray(mydata3, mydata1, mydata2) });
// ALL
// By Aging
#region By Aging
var ByAgingAll = (from byaging in barnavall
group byaging by byaging.Aging into Grouping
//orderby Grouping.Key
select new
{
label = ((Grouping.Key == 1) ? "11+ days" : "1-10 days") + " (" + Grouping.Count() + ")",
data = (Grouping.Key == 1) ? "11+ days" : "1-10 days",
children = from subg in Grouping
group subg by subg.Country into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
children = from subsubg in SubGrouping
group subsubg by subsubg.Function into SubSubGrouping
//orderby SubSubGrouping.Key
select new
{
label = SubSubGrouping.Key + " (" + SubSubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = SubSubGrouping.Key,
aging = Grouping.Key
}
}
}
});
#endregion
// By Category
#region By Category
var ByFunctionAll = (from byfunction in barnavall
group byfunction by byfunction.Function into Grouping
//orderby Grouping.Key
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
data = Grouping.Key,
children = from subg in Grouping
group subg by subg.Country into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = SubGrouping.Key,
categoryName = Grouping.Key,
batchName = "",
aging = 1
}
}
});
#endregion
// By Country
#region By Country
var ByCountryAll = (from bycountry in barnavall
group bycountry by bycountry.Country into Grouping
//orderby Grouping.Key
select new
{
label = Grouping.Key + " (" + Grouping.Count() + ")",
children = (from subg in Grouping
group subg by subg.Function into SubGrouping
//orderby SubGrouping.Key
select new
{
label = SubGrouping.Key + " (" + SubGrouping.Count() + ")",
data = new
{
countryName = Grouping.Key,
categoryName = SubGrouping.Key,
batchName = "",
aging = 1
}
})
});
#endregion
var alldata1 = JObject.FromObject(new { label = "Country", data = "Country branch", children = ByCountryAll });
var alldata2 = JObject.FromObject(new { label = "Category", data = "Category branch", children = ByFunctionAll });
var alldata3 = JObject.FromObject(new { label = "Aging", data = "Aging branch", children = ByAgingAll });
var alldata = JObject.FromObject(new { label = "All", children = new JArray(alldata3, alldata1, alldata2) });
var t = new JArray(mydata, alldata);
return(Json(t));
}