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();
}
}
}