Beispiel #1
0
        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));
        }
Beispiel #2
0
        /// <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);
        }
Beispiel #3
0
        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));
        }
Beispiel #4
0
        /// <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);
        }
Beispiel #5
0
        /// <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);
        }
Beispiel #6
0
        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));
        }