public JsonResult GetInvString(int stationId, string beginDate, string invNo) { StringPerformanceView _strModel = new StringPerformanceView(); try { CultureHelper.SetCultureInfo(); DateTime date = DateTime.Parse(beginDate); DateTime curDate = DateTime.Now; _strModel.strModel = new StringModel(); List <String_Hour_DTO> values = new List <String_Hour_DTO>(); int _invNo = int.Parse(invNo); string invStrName = "DCB" + _invNo; if (curDate.Date.Year == date.Date.Year && curDate.Date.Month == date.Date.Month) { NUMBER_FORMAT_DTO convertFormat = DateTimeHelper.ConvertNumberFormatMinute(beginDate); long _numberDateBegin = convertFormat._begin; long _numberDateEnd = convertFormat._end; values = (from u in DB.StringOzetQuarterHourAVG join v in DB.stationString on u.STRING_ID equals v.STRING_ID where v.IS_DELETED == false && v.STATION_ID == stationId && u.STATION_ID == stationId && _numberDateBegin <= u.TARIH_NUMBER && _numberDateEnd >= u.TARIH_NUMBER && v.DISPLAY_NAME.StartsWith(invStrName) orderby u.TARIH_NUMBER ascending select new String_Hour_DTO { NAME = v.DISPLAY_NAME, ID = u.STRING_ID, TARIH_NUMBER = u.TARIH_NUMBER.Value, VALUE = u.VALUE }).ToList(); var stringNameList = values.OrderBy(a => a.NAME) .Select(a => a.NAME) .Distinct() .ToList(); foreach (var item in stringNameList) { _strModel.strModel.StringList.Add(item.Substring(8, 7).Replace("_", "")); } var valueList = new List <Values>(); string[] groupTime = values.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).Select(a => a.Key.ToString().Substring(8, 4)).ToArray(); int endHour = int.Parse(groupTime[groupTime.Length - 1]); DateTime EndDate = DateTime.Now.Date; TimeSpan tEndDate = new TimeSpan((int.Parse(groupTime[groupTime.Length - 1].Substring(0, 2))), (int.Parse(groupTime[groupTime.Length - 1].Substring(2, 2))), 0); EndDate = EndDate.Date + tEndDate; DateTime LastDate = EndDate; TimeSpan tLastDate = new TimeSpan(21, 0, 0); LastDate = LastDate.Date + tLastDate; for (int i = 0; i < groupTime.Length; i++) { _strModel.strModel.Hours.Add(groupTime[i]); } while (EndDate < LastDate) { EndDate = EndDate.AddMinutes(15); _strModel.strModel.Hours.Add(DateTimeHelper.HourMinuteFormat(EndDate)); } var groupList = values.OrderBy(a => a.NAME).GroupBy(a => a.NAME).ToList(); foreach (var item in groupList) { var listvalue = new Values(); foreach (var i in item.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).ToList()) { listvalue.values.Add((float)Math.Round(i.Max(a => a.VALUE), 2)); } valueList.Add(listvalue); } _strModel.strModel.series = valueList.ToList(); _strModel.ErrorMessage = ""; } else { NUMBER_FORMAT_DTO convertFormat = DateTimeHelper.ConvertNumberFormatHour(beginDate); long _numberDateBegin = convertFormat._begin; long _numberDateEnd = convertFormat._end; values = (from u in DB.StringOzetAVG join v in DB.stationString on u.STRING_ID equals v.STRING_ID where v.IS_DELETED == false && v.STATION_ID == stationId && u.STATION_ID == stationId && _numberDateBegin <u.TARIH_NUMBER && _numberDateEnd> u.TARIH_NUMBER && v.DISPLAY_NAME.StartsWith(invStrName) orderby u.TARIH_NUMBER ascending select new String_Hour_DTO { NAME = v.DISPLAY_NAME, ID = u.STRING_ID, TARIH_NUMBER = u.TARIH_NUMBER.Value, VALUE = u.VALUE }).ToList(); var stringNameList = values.OrderBy(a => a.NAME) .Select(a => a.NAME) .Distinct() .ToList(); foreach (var item in stringNameList) { _strModel.strModel.StringList.Add(item.Substring(8, 7).Replace("_", "")); } var valueList = new List <Values>(); string[] groupTime = values.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).Select(a => a.Key.ToString().Substring(8, 2)).ToArray(); int endHour = int.Parse(groupTime[groupTime.Length - 1]); for (int i = 0; i < groupTime.Length; i++) { _strModel.strModel.Hours.Add(groupTime[i] + "00"); } for (int i = endHour + 1; i < 21; i++) { if (!groupTime.Contains(i.ToString())) { _strModel.strModel.Hours.Add(i.ToString() + "00"); } } var groupList = values.OrderBy(a => a.NAME).GroupBy(a => a.NAME).ToList(); foreach (var item in groupList) { var listvalue = new Values(); foreach (var i in item.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).ToList()) { listvalue.values.Add((float)Math.Round(i.Max(a => a.VALUE), 2)); } valueList.Add(listvalue); } _strModel.strModel.series = valueList.ToList(); _strModel.ErrorMessage = ""; } } catch (Exception ex) { _strModel.ErrorMessage = ex.ToString(); } return(Json(_strModel, JsonRequestBehavior.AllowGet)); }
public JsonResult HourlyColorReport(int stationId, string slctDate) { StringPerformanceView _strModel = new StringPerformanceView(); try { Thread.CurrentThread.CurrentCulture = new CultureInfo("tr-TR"); DateTime date = DateTime.Parse(@slctDate); DateTime curDate = DateTime.Now; _strModel.strModel = new StringModel(); List <String_Hour_DTO> values = new List <String_Hour_DTO>(); if (curDate.Date == date.Date) { values = DB.VWStringOzetDaily.Where(p => p.STATION_ID == stationId).Select(a => new String_Hour_DTO { NAME = a.DISPLAY_NAME, ID = a.STRING_ID, date = a.INSERT_DATE, VALUE = a.VALUE, } ).ToList(); var stringlist1 = values.OrderBy(a => a.NAME) .Select(a => a.NAME) .Distinct() .ToList(); foreach (var item in stringlist1) { _strModel.strModel.StringList.Add(item.Substring(8, 6)); } var list1_ = new List <Values>(); var groupList1 = values.OrderBy(a => a.NAME).GroupBy(a => a.NAME).ToList(); string[] groupTime = values.GroupBy(grp => grp.date).Select(a => a.Key.Split(' ')[1]).ToArray(); int endHour = int.Parse(groupTime[groupTime.Length - 1]); for (int i = 0; i < groupTime.Length; i++) { _strModel.strModel.Hours.Add(groupTime[i]); } for (int i = endHour + 1; i < 21; i++) { if (!groupTime.Contains(i.ToString())) { _strModel.strModel.Hours.Add(i.ToString()); } } foreach (var item in groupList1) { var list1 = new Values(); foreach (var i in item.OrderBy(a => a.date).GroupBy(grp => grp.date).ToList()) { list1.values.Add((float)Math.Round((float)i.Max(a => a.VALUE), 2)); } list1_.Add(list1); } _strModel.strModel.series = list1_.ToList(); _strModel.ErrorMessage = ""; } else { NUMBER_FORMAT_DTO convertFormat = ConvertNumberFormatHour(slctDate); long _numberDateBegin = convertFormat._begin; long _numberDateEnd = convertFormat._end; values = (from u in DB.StringOzetAVG join v in DB.stationString on u.STRING_ID equals v.STRING_ID where v.IS_DELETED == false && v.STATION_ID == stationId && u.STATION_ID == stationId && _numberDateBegin <u.TARIH_NUMBER && _numberDateEnd> u.TARIH_NUMBER orderby u.TARIH_NUMBER ascending select new String_Hour_DTO { NAME = v.DISPLAY_NAME, ID = u.STRING_ID, TARIH_NUMBER = u.TARIH_NUMBER.Value, VALUE = u.VALUE }).ToList(); var stringNameList = values.OrderBy(a => a.NAME) .Select(a => a.NAME) .Distinct() .ToList(); foreach (var item in stringNameList) { _strModel.strModel.StringList.Add(item.Substring(8, 6)); } var valueList = new List <Values>(); string[] groupTime = values.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).Select(a => a.Key.ToString().Substring(8, 2)).ToArray(); int endHour = int.Parse(groupTime[groupTime.Length - 1]); for (int i = 0; i < groupTime.Length; i++) { _strModel.strModel.Hours.Add(groupTime[i]); } for (int i = endHour + 1; i < 21; i++) { if (!groupTime.Contains(i.ToString())) { _strModel.strModel.Hours.Add(i.ToString()); } } var groupList = values.OrderBy(a => a.NAME).GroupBy(a => a.NAME).ToList(); foreach (var item in groupList) { var listvalue = new Values(); foreach (var i in item.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).ToList()) { listvalue.values.Add((float)Math.Round(i.Max(a => a.VALUE), 2)); } valueList.Add(listvalue); } _strModel.strModel.series = valueList.ToList(); _strModel.ErrorMessage = ""; } } catch (Exception ex) { _strModel.ErrorMessage = ex.ToString(); } return(Json(_strModel)); }
public JsonResult HourlyColorReport(int stationId, string slctDate) { StringPerformanceView _strModel = new StringPerformanceView(); try { Thread.CurrentThread.CurrentCulture = new CultureInfo("tr-TR"); DateTime date = DateTime.Parse(@slctDate); DateTime curDate = DateTime.Now; List <TempDTO> strTagNames = DB.stationString .Join(DB.Tags, r => r.STRING_ID, ro => ro.ID, (r, ro) => new { r, ro }) .Where(x => x.r.STATION_ID == stationId && x.r.DISPLAY_NAME.Contains("temp") == false && x.r.IS_DELETED == false) .GroupBy(x => x.r.DISPLAY_NAME) .Select(g => new TempDTO { NAME = g.Key, ID = g.FirstOrDefault().ro.ID }).OrderBy(o => o.NAME) .ToList(); _strModel.strModel = new StringModel(); List <String_Hour_DTO> values = new List <String_Hour_DTO>(); List <String_Hour_DTO> valuesTemp = new List <String_Hour_DTO>(); if (curDate.Date.Year == date.Date.Year && curDate.Date.Month == date.Date.Month) { NUMBER_FORMAT_DTO convertFormat = ConvertNumberFormatMinute(slctDate); long _numberDateBegin = convertFormat._begin; long _numberDateEnd = convertFormat._end; valuesTemp = (from u in DB.StringOzetQuarterHourAVG join v in DB.stationString on u.STRING_ID equals v.STRING_ID join t in DB.Tags on v.STRING_ID equals t.ID where v.IS_DELETED == false && v.STATION_ID == stationId && u.STATION_ID == stationId && _numberDateBegin <= u.TARIH_NUMBER && _numberDateEnd >= u.TARIH_NUMBER orderby u.TARIH_NUMBER ascending select new String_Hour_DTO { NAME = t.NAME, DISPLAY_NAME = v.DISPLAY_NAME, ID = u.STRING_ID, TARIH_NUMBER = u.TARIH_NUMBER.Value, VALUE = u.VALUE }).ToList(); foreach (var item in valuesTemp.GroupBy(gg => gg.TARIH_NUMBER).ToList()) { foreach (var item2 in strTagNames) { var vv = valuesTemp.Where(w => w.TARIH_NUMBER == item.Key && w.ID == item2.ID).FirstOrDefault(); values.Add(vv); } } foreach (var item in strTagNames) { _strModel.strModel.StringList.Add(item.NAME); } var valueList = new List <Values>(); string[] groupTime = values.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).Select(a => a.Key.ToString().Substring(8, 4)).ToArray(); int endHour = int.Parse(groupTime[groupTime.Length - 1]); DateTime EndDate = DateTime.Now.Date; TimeSpan tEndDate = new TimeSpan((int.Parse(groupTime[groupTime.Length - 1].Substring(0, 2))), (int.Parse(groupTime[groupTime.Length - 1].Substring(2, 2))), 0); EndDate = EndDate.Date + tEndDate; DateTime LastDate = EndDate; TimeSpan tLastDate = new TimeSpan(21, 0, 0); LastDate = LastDate.Date + tLastDate; for (int i = 0; i < groupTime.Length; i++) { _strModel.strModel.Hours.Add(groupTime[i]); } while (EndDate < LastDate) { EndDate = EndDate.AddMinutes(15); _strModel.strModel.Hours.Add(HourMinuteFormat(EndDate)); } //VALUE SIRALAMA var groupList = values.GroupBy(a => a.DISPLAY_NAME).ToList(); foreach (var item in groupList) { var listvalue = new Values(); foreach (var i in item.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).ToList()) { listvalue.values.Add((float)Math.Round((i.Max(a => a.VALUE)), 2)); } valueList.Add(listvalue); } _strModel.strModel.series = valueList.ToList(); _strModel.ErrorMessage = ""; } else { NUMBER_FORMAT_DTO convertFormat = ConvertNumberFormatHour(slctDate); long _numberDateBegin = convertFormat._begin; long _numberDateEnd = convertFormat._end; valuesTemp = (from u in DB.StringOzetAVG join v in DB.stationString on u.STRING_ID equals v.STRING_ID join t in DB.Tags on v.STRING_ID equals t.ID where v.IS_DELETED == false && v.STATION_ID == stationId && u.STATION_ID == stationId && _numberDateBegin <u.TARIH_NUMBER && _numberDateEnd> u.TARIH_NUMBER orderby u.TARIH_NUMBER ascending select new String_Hour_DTO { NAME = t.NAME, DISPLAY_NAME = v.DISPLAY_NAME, ID = u.STRING_ID, TARIH_NUMBER = u.TARIH_NUMBER.Value, VALUE = u.VALUE }).ToList(); foreach (var item in valuesTemp.GroupBy(gg => gg.TARIH_NUMBER).ToList()) { foreach (var item2 in strTagNames) { var vv = valuesTemp.Where(w => w.TARIH_NUMBER == item.Key && w.ID == item2.ID).FirstOrDefault(); values.Add(vv); } } foreach (var item in strTagNames) { _strModel.strModel.StringList.Add(item.NAME); } var valueList = new List <Values>(); string[] groupTime = values.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).Select(a => a.Key.ToString().Substring(8, 2)).ToArray(); int endHour = int.Parse(groupTime[groupTime.Length - 1]); for (int i = 0; i < groupTime.Length; i++) { _strModel.strModel.Hours.Add(groupTime[i] + "00"); } for (int i = endHour + 1; i < 21; i++) { if (!groupTime.Contains(i.ToString())) { _strModel.strModel.Hours.Add(i.ToString() + "00"); } } //VALUE SIRALAMA var groupList = values.GroupBy(a => a.DISPLAY_NAME).ToList(); foreach (var item in groupList) { var listvalue = new Values(); foreach (var i in item.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).ToList()) { listvalue.values.Add((float)Math.Round((i.Max(a => a.VALUE)), 2)); } valueList.Add(listvalue); } _strModel.strModel.series = valueList.ToList(); _strModel.ErrorMessage = ""; } } catch (Exception ex) { _strModel.ErrorMessage = ex.ToString(); } return(Json(_strModel)); }
public JsonResult HourlyColorReport2(int stationId, string slctDate) { StringPerformanceView _strModel = new StringPerformanceView(); try { Thread.CurrentThread.CurrentCulture = new CultureInfo("tr-TR"); DateTime date = DateTime.Parse(@slctDate); DateTime curDate = DateTime.Now; _strModel.strModel = new StringModel(); List <String_Hour_DTO> values = new List <String_Hour_DTO>(); if (curDate.Date.Year == date.Date.Year && curDate.Date.Month == date.Date.Month) { NUMBER_FORMAT_DTO convertFormat = ConvertNumberFormatMinute(slctDate); long _numberDateBegin = convertFormat._begin; long _numberDateEnd = convertFormat._end; values = (from u in DB.StringOzetQuarterHourAVG join v in DB.stationString on u.STRING_ID equals v.STRING_ID join t in DB.Tags on v.STRING_ID equals t.ID where v.IS_DELETED == false && v.STATION_ID == stationId && u.STATION_ID == stationId && _numberDateBegin <= u.TARIH_NUMBER && _numberDateEnd >= u.TARIH_NUMBER orderby u.TARIH_NUMBER ascending select new String_Hour_DTO { NAME = t.NAME, ID = u.STRING_ID, TARIH_NUMBER = u.TARIH_NUMBER.Value, VALUE = u.VALUE }).ToList(); /////// List <TempDTO> strTagNames = values .GroupBy(x => x.NAME) .Select(g => new TempDTO { NAME = g.Key, ID = g.FirstOrDefault().ID, TARIH_NUMBER = g.FirstOrDefault().TARIH_NUMBER, VALUE = g.FirstOrDefault().VALUE }) .ToList(); for (int i = 0; i < strTagNames.Count; i++) { strTagNames[i].INPUT_NO = Convert.ToInt32(strTagNames[i].NAME.Split('_')[3].Replace(".", "")); strTagNames[i].INV_NO = Convert.ToInt32(strTagNames[i].NAME.Split('_')[0].Replace("INV", "")); } strTagNames = strTagNames.OrderBy(x => x.INV_NO).ThenBy(x => x.INPUT_NO).ToList(); //// foreach (var item in strTagNames) { _strModel.strModel.StringList.Add(item.NAME); } var valueList = new List <Values>(); string[] groupTime = values.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).Select(a => a.Key.ToString().Substring(8, 4)).ToArray(); int endHour = int.Parse(groupTime[groupTime.Length - 1]); DateTime EndDate = DateTime.Now.Date; TimeSpan tEndDate = new TimeSpan((int.Parse(groupTime[groupTime.Length - 1].Substring(0, 2))), (int.Parse(groupTime[groupTime.Length - 1].Substring(2, 2))), 0); EndDate = EndDate.Date + tEndDate; DateTime LastDate = EndDate; TimeSpan tLastDate = new TimeSpan(21, 0, 0); LastDate = LastDate.Date + tLastDate; for (int i = 0; i < groupTime.Length; i++) { _strModel.strModel.Hours.Add(groupTime[i]); } while (EndDate < LastDate) { EndDate = EndDate.AddMinutes(15); _strModel.strModel.Hours.Add(HourMinuteFormat(EndDate)); } //VALUE SIRALAMA List <TempDTO> strValue = values .Select(g => new TempDTO { NAME = g.NAME, ID = g.ID, VALUE = g.VALUE, TARIH_NUMBER = g.TARIH_NUMBER }) .ToList(); for (int i = 0; i < strValue.Count; i++) { strValue[i].INPUT_NO = Convert.ToInt32(strValue[i].NAME.Split('_')[3].Replace(".", "")); strValue[i].INV_NO = Convert.ToInt32(strValue[i].NAME.Split('_')[0].Replace("INV", "")); strValue[i].VALUE = strValue[i].VALUE; strValue[i].TARIH_NUMBER = strValue[i].TARIH_NUMBER; } strValue = strValue.OrderBy(x => x.INV_NO).ThenBy(x => x.INPUT_NO).ToList(); var groupList = strValue.GroupBy(a => a.NAME).ToList(); foreach (var item in groupList) { var listvalue = new Values(); foreach (var i in item.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).ToList()) { listvalue.values.Add((float)Math.Round(i.Max(a => a.VALUE.Value), 2)); } valueList.Add(listvalue); } _strModel.strModel.series = valueList.ToList(); _strModel.ErrorMessage = ""; } else { NUMBER_FORMAT_DTO convertFormat = ConvertNumberFormatHour(slctDate); long _numberDateBegin = convertFormat._begin; long _numberDateEnd = convertFormat._end; values = (from u in DB.StringOzetAVG join v in DB.stationString on u.STRING_ID equals v.STRING_ID join t in DB.Tags on v.STRING_ID equals t.ID where v.IS_DELETED == false && v.STATION_ID == stationId && u.STATION_ID == stationId && _numberDateBegin <u.TARIH_NUMBER && _numberDateEnd> u.TARIH_NUMBER orderby u.TARIH_NUMBER ascending select new String_Hour_DTO { NAME = t.NAME, ID = u.STRING_ID, TARIH_NUMBER = u.TARIH_NUMBER.Value, VALUE = u.VALUE }).ToList(); /////// List <TempDTO> strTagNames = values .GroupBy(x => x.NAME) .Select(g => new TempDTO { NAME = g.Key, ID = g.FirstOrDefault().ID, TARIH_NUMBER = g.FirstOrDefault().TARIH_NUMBER, VALUE = g.FirstOrDefault().VALUE }) .ToList(); for (int i = 0; i < strTagNames.Count; i++) { strTagNames[i].INPUT_NO = Convert.ToInt32(strTagNames[i].NAME.Split('_')[3].Replace(".", "")); strTagNames[i].INV_NO = Convert.ToInt32(strTagNames[i].NAME.Split('_')[0].Replace("INV", "")); } strTagNames = strTagNames.OrderBy(x => x.INV_NO).ThenBy(x => x.INPUT_NO).ToList(); //// foreach (var item in strTagNames) { _strModel.strModel.StringList.Add(item.NAME); } var valueList = new List <Values>(); string[] groupTime = values.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).Select(a => a.Key.ToString().Substring(8, 2)).ToArray(); int endHour = int.Parse(groupTime[groupTime.Length - 1]); for (int i = 0; i < groupTime.Length; i++) { _strModel.strModel.Hours.Add(groupTime[i] + "00"); } for (int i = endHour + 1; i < 21; i++) { if (!groupTime.Contains(i.ToString())) { _strModel.strModel.Hours.Add(i.ToString() + "00"); } } //VALUE SIRALAMA List <TempDTO> strValue = values .Select(g => new TempDTO { NAME = g.NAME, ID = g.ID, VALUE = g.VALUE, TARIH_NUMBER = g.TARIH_NUMBER }) .ToList(); for (int i = 0; i < strValue.Count; i++) { strValue[i].INPUT_NO = Convert.ToInt32(strValue[i].NAME.Split('_')[3].Replace(".", "")); strValue[i].INV_NO = Convert.ToInt32(strValue[i].NAME.Split('_')[0].Replace("INV", "")); strValue[i].VALUE = strValue[i].VALUE; strValue[i].TARIH_NUMBER = strValue[i].TARIH_NUMBER; } strValue = strValue.OrderBy(x => x.INV_NO).ThenBy(x => x.INPUT_NO).ToList(); var groupList = strValue.GroupBy(a => a.NAME).ToList(); foreach (var item in groupList) { var listvalue = new Values(); foreach (var i in item.GroupBy(grp => grp.TARIH_NUMBER).OrderBy(a => a.Key).ToList()) { listvalue.values.Add((float)Math.Round(i.Max(a => a.VALUE.Value), 2)); } valueList.Add(listvalue); } _strModel.strModel.series = valueList.ToList(); _strModel.ErrorMessage = ""; } } catch (Exception ex) { _strModel.ErrorMessage = ex.ToString(); } return(Json(_strModel)); }