public static Report_TopSoundingAlarm getTopSoundingAlarmReportObj() { if (topSoundingAlarmReportObj == null) { topSoundingAlarmReportObj = new Report_TopSoundingAlarm(); } return(topSoundingAlarmReportObj); }
public static Report_TopSoundingAlarm getTopSoundingAlarmReportObj() { if (topSoundingAlarmReportObj == null) { topSoundingAlarmReportObj = new Report_TopSoundingAlarm(); } return topSoundingAlarmReportObj; }
public List <ReportRow> getData(String criteria, String top, String stDate, String stTime, String endDate, String endTime) { Report_TopSoundingAlarm reportObj = Report_TopSoundingAlarm.getTopSoundingAlarmReportObj(); SqlConnection conn = null; SqlDataReader sqlreader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query = ""; SqlCommand command = new SqlCommand(); List <ReportRow> rowList = new List <ReportRow>(); Report_TopSoundingAlarm.isConditionSelected = false; if (top.Equals("null")) { top = "5"; } else { int n; bool isNumeric = int.TryParse(top, out n); if (!isNumeric) { top = "5"; } } if (criteria.Equals("input_desc")) { query = "SELECT TOP " + top + " [input_desc] AS INPUT_DESC,Count(*) AS COUNT FROM [view_rs_top_sounding_alarm_report]"; } else if (criteria.Equals("alarm_desc")) { query = "SELECT TOP " + top + " [alarm_desc] AS ALARM_DESC,Count(*) AS COUNT FROM [view_rs_top_sounding_alarm_report]"; } else { query = "SELECT TOP " + top + " [description] AS FACILITY,Count(*) AS COUNT FROM [view_rs_top_sounding_alarm_report]"; } if (!stDate.Equals("null")) { if (Report_TopSoundingAlarm.isConditionSelected) { query = query + " AND @startDate <= [view_rs_top_sounding_alarm_report].HAPPENED_DATETIME "; } else { query = query + " WHERE @startDate <= [view_rs_top_sounding_alarm_report].HAPPENED_DATETIME "; } Report_TopSoundingAlarm.isConditionSelected = true; } if (!endDate.Equals("null")) { if (Report_TopSoundingAlarm.isConditionSelected) { query = query + " AND @endDate >= [view_rs_top_sounding_alarm_report].HAPPENED_DATETIME "; } else { query = query + " WHERE @endDate >= [view_rs_top_sounding_alarm_report].HAPPENED_DATETIME "; } Report_TopSoundingAlarm.isConditionSelected = true; } if (!stTime.Equals("null")) { if (Report_TopSoundingAlarm.isConditionSelected) { query = query + " AND @startTime <= [view_rs_top_sounding_alarm_report].HAPPENED_TIME "; } else { query = query + " WHERE @startTime <= [view_rs_top_sounding_alarm_report].HAPPENED_TIME "; } Report_TopSoundingAlarm.isConditionSelected = true; } if (!endTime.Equals("null")) { if (Report_TopSoundingAlarm.isConditionSelected) { query = query + " AND @endTime >= [view_rs_top_sounding_alarm_report].HAPPENED_TIME "; } else { query = query + " WHERE @endTime >= [view_rs_top_sounding_alarm_report].HAPPENED_TIME "; } Report_TopSoundingAlarm.isConditionSelected = true; } if (!stDate.Equals("null")) { DateTime startDateTime = new DateTime(Convert.ToInt32(stDate.Substring(0, 4)), Convert.ToInt32(stDate.Substring(4, 2)), Convert.ToInt32(stDate.Substring(6, 2))); command.Parameters.AddWithValue("@startDate", startDateTime); } if (!endDate.Equals("null")) { DateTime endDateTime = new DateTime(Convert.ToInt32(endDate.Substring(0, 4)), Convert.ToInt32(endDate.Substring(4, 2)), Convert.ToInt32(endDate.Substring(6, 2))); command.Parameters.AddWithValue("@endDate", endDateTime); } if (!stTime.Equals("null")) { TimeSpan startQueryTime = new TimeSpan(Convert.ToInt32(stTime.Substring(0, 2)), Convert.ToInt32(stTime.Substring(2, 2)), Convert.ToInt32(stTime.Substring(4, 2))); command.Parameters.AddWithValue("@startTime", startQueryTime); } if (!endTime.Equals("null")) { DateTime endDateTime = new DateTime(Convert.ToInt32(endDate.Substring(0, 4)), Convert.ToInt32(endDate.Substring(4, 2)), Convert.ToInt32(endDate.Substring(6, 2))); command.Parameters.AddWithValue("@endDate", endDateTime); } if (criteria.Equals("input_desc")) { query = query + " GROUP BY [input_desc] ORDER BY COUNT(*) desc"; } else if (criteria.Equals("alarm_desc")) { query = query + " GROUP BY [alarm_desc] ORDER BY COUNT(*) desc"; } else { query = query + " GROUP BY [description] ORDER BY COUNT(*) desc"; } command.Connection = conn; command.CommandText = query; command.CommandTimeout = 300; int count = 0; using (sqlreader = command.ExecuteReader()) { while (sqlreader.Read()) { Report_TopSoundingAlarm report = new Report_TopSoundingAlarm(); report.Criteria = sqlreader.GetSqlValue(0).ToString().Trim(); report.Count = Convert.ToInt32(sqlreader.GetSqlValue(1).ToString().Trim()); ReportRow repRow = new ReportRow(); repRow.id = (++count).ToString(); repRow.datarow = report; rowList.Add(repRow); } } return(rowList); } finally { if (conn != null) { conn.Close(); } if (sqlreader != null) { sqlreader.Close(); } } }
public List<ReportRow> getData(String criteria, String top,String stDate,String stTime,String endDate,String endTime) { Report_TopSoundingAlarm reportObj = Report_TopSoundingAlarm.getTopSoundingAlarmReportObj(); SqlConnection conn = null; SqlDataReader sqlreader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query = ""; SqlCommand command = new SqlCommand(); List<ReportRow> rowList = new List<ReportRow>(); Report_TopSoundingAlarm.isConditionSelected = false; if(top.Equals("null")) { top = "5"; } else { int n; bool isNumeric = int.TryParse(top, out n); if (!isNumeric) top = "5"; } if (criteria.Equals("input_desc")) { query = "SELECT TOP " +top+" [input_desc] AS INPUT_DESC,Count(*) AS COUNT FROM [view_rs_top_sounding_alarm_report]"; } else if (criteria.Equals("alarm_desc")) { query = "SELECT TOP " + top + " [alarm_desc] AS ALARM_DESC,Count(*) AS COUNT FROM [view_rs_top_sounding_alarm_report]"; } else { query = "SELECT TOP " + top + " [description] AS FACILITY,Count(*) AS COUNT FROM [view_rs_top_sounding_alarm_report]"; } if (!stDate.Equals("null")) { if (Report_TopSoundingAlarm.isConditionSelected) query = query + " AND @startDate <= [view_rs_top_sounding_alarm_report].HAPPENED_DATETIME "; else query = query + " WHERE @startDate <= [view_rs_top_sounding_alarm_report].HAPPENED_DATETIME "; Report_TopSoundingAlarm.isConditionSelected = true; } if (!endDate.Equals("null")) { if (Report_TopSoundingAlarm.isConditionSelected) query = query + " AND @endDate >= [view_rs_top_sounding_alarm_report].HAPPENED_DATETIME "; else query = query + " WHERE @endDate >= [view_rs_top_sounding_alarm_report].HAPPENED_DATETIME "; Report_TopSoundingAlarm.isConditionSelected = true; } if (!stTime.Equals("null")) { if (Report_TopSoundingAlarm.isConditionSelected) query = query + " AND @startTime <= [view_rs_top_sounding_alarm_report].HAPPENED_TIME "; else query = query + " WHERE @startTime <= [view_rs_top_sounding_alarm_report].HAPPENED_TIME "; Report_TopSoundingAlarm.isConditionSelected = true; } if (!endTime.Equals("null")) { if (Report_TopSoundingAlarm.isConditionSelected) query = query + " AND @endTime >= [view_rs_top_sounding_alarm_report].HAPPENED_TIME "; else query = query + " WHERE @endTime >= [view_rs_top_sounding_alarm_report].HAPPENED_TIME "; Report_TopSoundingAlarm.isConditionSelected = true; } if (!stDate.Equals("null")) { DateTime startDateTime = new DateTime(Convert.ToInt32(stDate.Substring(0, 4)), Convert.ToInt32(stDate.Substring(4, 2)), Convert.ToInt32(stDate.Substring(6, 2))); command.Parameters.AddWithValue("@startDate", startDateTime); } if (!endDate.Equals("null")) { DateTime endDateTime = new DateTime(Convert.ToInt32(endDate.Substring(0, 4)), Convert.ToInt32(endDate.Substring(4, 2)), Convert.ToInt32(endDate.Substring(6, 2))); command.Parameters.AddWithValue("@endDate", endDateTime); } if (!stTime.Equals("null")) { TimeSpan startQueryTime = new TimeSpan(Convert.ToInt32(stTime.Substring(0, 2)), Convert.ToInt32(stTime.Substring(2, 2)), Convert.ToInt32(stTime.Substring(4, 2))); command.Parameters.AddWithValue("@startTime", startQueryTime); } if (!endTime.Equals("null")) { DateTime endDateTime = new DateTime(Convert.ToInt32(endDate.Substring(0, 4)), Convert.ToInt32(endDate.Substring(4, 2)), Convert.ToInt32(endDate.Substring(6, 2))); command.Parameters.AddWithValue("@endDate", endDateTime); } if (criteria.Equals("input_desc")) { query = query + " GROUP BY [input_desc] ORDER BY COUNT(*) desc"; } else if (criteria.Equals("alarm_desc")) { query = query + " GROUP BY [alarm_desc] ORDER BY COUNT(*) desc"; } else { query = query + " GROUP BY [description] ORDER BY COUNT(*) desc"; } command.Connection = conn; command.CommandText = query; command.CommandTimeout = 300; int count = 0; using (sqlreader = command.ExecuteReader()) { while (sqlreader.Read()) { Report_TopSoundingAlarm report = new Report_TopSoundingAlarm(); report.Criteria = sqlreader.GetSqlValue(0).ToString().Trim(); report.Count = Convert.ToInt32(sqlreader.GetSqlValue(1).ToString().Trim()); ReportRow repRow = new ReportRow(); repRow.id = (++count).ToString(); repRow.datarow = report; rowList.Add(repRow); } } return rowList; } finally { if (conn != null) { conn.Close(); } if (sqlreader != null) { sqlreader.Close(); } } }