public HttpResponseMessage t2SubCaptionTrendMtd(HttpRequestMessage request) { appLog.InfoFormat("Calling the t2SubCaptionTrendMtd() method"); HttpResponseMessage res = null; var newList2 = new List <SubCaptionTrendListInfo>(); DListInfo dlist = new DListInfo(); //var latestmonthyear = entityContext.IncomeSetUpDailySet.OrderByDescending(x => x.Year).Take(1); //int latestyear = Convert.ToInt32(latestmonthyear.Max(x => x.Year)); //int period = Convert.ToInt32(latestmonthyear.Max(x => x.CurrentPeriod)); int latestyear = Convert.ToInt32(System.Web.HttpContext.Current.Session["latestyear"]); int period = Convert.ToInt32(System.Web.HttpContext.Current.Session["currentperiod"]); appLog.InfoFormat("Latestyear: {0} and Latestperiod: {1} are gotten.", latestyear, period); //string miscode = System.Web.HttpContext.Current.Session["session_miscode"].ToString(); //int level = Convert.ToInt32(System.Web.HttpContext.Current.Session["session_level"]); string miscode = Convert.ToString(System.Web.HttpContext.Current.Session["session_miscode"]); string currentlyselectedmiscode = Convert.ToString(System.Web.HttpContext.Current.Session["session_currentlyselectedmiscode"]); int level = Convert.ToInt32(System.Web.HttpContext.Current.Session["session_level"]); int currentlyselectedlevel = Convert.ToInt32(System.Web.HttpContext.Current.Session["session_currentlyselectedlevel"]); //if (currentlyselectedmiscode != "") //{ // miscode = ""; // miscode = currentlyselectedmiscode; // level = currentlyselectedlevel; //} appLog.InfoFormat("level: {0} received", level); appLog.InfoFormat("miscode: {0} received", miscode); var conf = entityContext.LandingPageConfigurationSet.Where(x => x.Deleted == false).ToList(); string d2 = conf.Where(x => x.DisplayCaptionObjectCode == "D2").Select(x => x.MainCaption).FirstOrDefault(); var newList = new List <SubMixListInfo>(); using (var con = new SqlConnection(connectionString)) { var cmd = new SqlCommand("proc_mpr_report_dashboard_by_caption", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 0; cmd.Parameters.Add(new SqlParameter { ParameterName = "MisCode", Value = miscode, }); cmd.Parameters.Add(new SqlParameter { ParameterName = "Period", Value = period, }); cmd.Parameters.Add(new SqlParameter { ParameterName = "Year", Value = latestyear, }); cmd.Parameters.Add(new SqlParameter { ParameterName = "Level", Value = level, }); cmd.Parameters.Add(new SqlParameter { ParameterName = "MainCaption", Value = d2, }); cmd.Parameters.Add(new SqlParameter { ParameterName = "TrendOrMonthly", Value = "trend", }); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { var pts = new SubMixListInfo(); pts.SubCaption = reader["SubCaption"] != DBNull.Value ? reader["SubCaption"].ToString() : "null"; pts.Amount = reader["Amount"] != DBNull.Value ? Convert.ToDouble(reader["Amount"].ToString()) : 0; pts.Budget = reader["Budget"] != DBNull.Value ? Convert.ToDouble(reader["Budget"].ToString()) : 0; pts.Period = reader["Period"] != DBNull.Value ? Convert.ToInt32(reader["Period"].ToString()) : 0; newList.Add(pts); } con.Close(); } // using List <string> sub = newList.Select(x => x.SubCaption).Distinct().ToList(); foreach (var vv in sub) { //var query = menu2.Where(x => x.MenuList == p).Select(x => new mprFintrakMenu_SubObjectListInfo { ReportPAth = x.ReportPAth, ParameterKey = x.ParameterKey, ReportTitle = x.ReportTitle, UIsrefState = x.UIsrefState, ID = x.ID }); var query = newList.Where(x => x.SubCaption == vv).Select(x => new ABPTrendListInfo { Amount = x.Amount, Budget = x.Budget, Period = x.Period }).GroupBy(x => x.Period).Select(x => x.FirstOrDefault()).ToList(); var ob = new SubCaptionTrendListInfo() { abpList = query.ToList(), SubCaption = vv }; newList2.Add(ob); } res = request.CreateResponse <IEnumerable>(HttpStatusCode.OK, newList2); appLog.InfoFormat("about to return the result"); return(res); }
//======================= Stored procedure 8 starts ======================================================================= public List <SubCaptionTrendListInfo> DashboardMainCaptionMtd(string typevalue, string trendORmonthly) { var newList2 = new List <SubCaptionTrendListInfo>(); DListInfo dlist = new DListInfo(); //var latestmonthyear = entityContext.IncomeSetUpDailySet.OrderByDescending(x => x.Year).Take(1); //int latestyear = latestmonthyear.Select(x => x.Year).FirstOrDefault(); //int period = latestmonthyear.Max(x => x.CurrentPeriod); int latestyear = Convert.ToInt32(System.Web.HttpContext.Current.Session["latestyear"]); int period = Convert.ToInt32(System.Web.HttpContext.Current.Session["currentperiod"]); //string miscode = System.Web.HttpContext.Current.Session["session_miscode"].ToString(); //int level = Convert.ToInt32(System.Web.HttpContext.Current.Session["session_level"]); string miscode = Convert.ToString(System.Web.HttpContext.Current.Session["session_miscode"]); string currentlyselectedmiscode = Convert.ToString(System.Web.HttpContext.Current.Session["session_currentlyselectedmiscode"]); int level = Convert.ToInt32(System.Web.HttpContext.Current.Session["session_level"]); int currentlyselectedlevel = Convert.ToInt32(System.Web.HttpContext.Current.Session["session_currentlyselectedlevel"]); if (currentlyselectedmiscode != "") { miscode = ""; miscode = currentlyselectedmiscode; level = currentlyselectedlevel; } var conf = entityContext.LandingPageConfigurationSet.Where(x => x.Deleted == false).ToList(); var newList = new List <SubMixListInfo>(); using (var con = new SqlConnection(connectionString)) { var cmd = new SqlCommand("proc_mpr_report_dashboard_by_caption_8", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 0; cmd.Parameters.Add(new SqlParameter { ParameterName = "MisCode", Value = miscode, }); cmd.Parameters.Add(new SqlParameter { ParameterName = "Period", Value = period, }); cmd.Parameters.Add(new SqlParameter { ParameterName = "Year", Value = latestyear, }); cmd.Parameters.Add(new SqlParameter { ParameterName = "Level", Value = level, }); cmd.Parameters.Add(new SqlParameter { ParameterName = "type", Value = typevalue, }); cmd.Parameters.Add(new SqlParameter { ParameterName = "TrendOrMonthly", // Value = "Trend", Value = trendORmonthly, }); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { var pts = new SubMixListInfo(); //pts.SubCaption = reader["SubCaption"].ToString(); //pts.Amount = Convert.ToDouble(reader["Amount"]); //pts.Budget = Convert.ToDouble(reader["Budget"]); //pts.Period = Convert.ToInt32(reader["Period"]); pts.SubCaption = reader["SubCaption"] != DBNull.Value ? reader["SubCaption"].ToString() : "null"; pts.Amount = reader["Amount"] != DBNull.Value ? Convert.ToDouble(reader["Amount"].ToString()) : 0; pts.Budget = reader["Budget"] != DBNull.Value ? Convert.ToDouble(reader["Budget"].ToString()) : 0; pts.Period = reader["Period"] != DBNull.Value ? Convert.ToInt32(reader["Period"].ToString()) : 0; newList.Add(pts); } con.Close(); } // using List <string> sub = newList.Select(x => x.SubCaption).Distinct().ToList(); foreach (var vv in sub) { //var query = menu2.Where(x => x.MenuList == p).Select(x => new mprFintrakMenu_SubObjectListInfo { ReportPAth = x.ReportPAth, ParameterKey = x.ParameterKey, ReportTitle = x.ReportTitle, UIsrefState = x.UIsrefState, ID = x.ID }); var query = newList.Where(x => x.SubCaption == vv).Select(x => new ABPTrendListInfo { Amount = x.Amount, Budget = x.Budget, Period = x.Period }).GroupBy(x => x.Period).Select(x => x.FirstOrDefault()).ToList(); var ob = new SubCaptionTrendListInfo() { abpList = query.ToList(), SubCaption = vv }; newList2.Add(ob); } return(newList2); }