public static Report_AlarmDoor getAlarmDoorReportObj()
{
if (alarmDoorReportObj == null)
{
alarmDoorReportObj = new Report_AlarmDoor();
}
return(alarmDoorReportObj);
}
public static Report_AlarmDoor getAlarmDoorReportObj()
{
if (alarmDoorReportObj == null)
{
alarmDoorReportObj = new Report_AlarmDoor();
}
return alarmDoorReportObj;
}
public static List <ReportRow> getPPQueryData(String ppQuery, String stTime, String endTime)
{
List <ReportRow> rowList = new List <ReportRow>();
SqlConnection connection = null;
SqlDataReader sqlreader = null;
try
{
ppQuery = ppQuery.Replace("[view_rs_top_alarm_by_door]", "[view_rs_top_alarm_by_door_PP]");
// create and open a connection object
connection = ConnectionManager.getConnection();
connection.Open();
SqlCommand command = new SqlCommand();
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"))
{
TimeSpan endQueryTime = new TimeSpan(Convert.ToInt32(endTime.Substring(0, 2)), Convert.ToInt32(endTime.Substring(2, 2)), Convert.ToInt32(endTime.Substring(4, 2)));
command.Parameters.AddWithValue("@endTime", endQueryTime);
}
//Set the query string for the reporting server connection
command.Connection = connection;
command.CommandText = ppQuery;
int count = 0;
using (sqlreader = command.ExecuteReader())
{
//Read from the reader
while (sqlreader.Read())
{
Report_AlarmDoor report = new Report_AlarmDoor();
report.InputGroup = sqlreader.GetSqlValue(0).ToString().Trim();
report.InputDesc = sqlreader.GetSqlValue(1).ToString().Trim();
report.Alarm = sqlreader.GetSqlValue(2).ToString().Trim();
report.Facility = sqlreader.GetSqlValue(3).ToString().Trim();
report.HappenedDateTime = sqlreader.GetSqlValue(4).ToString().Trim();
report.Day = sqlreader.GetSqlValue(5).ToString().Trim();
//Fill the report row object for response
ReportRow repRow = new ReportRow();
repRow.id = (++count).ToString();
repRow.datarow = report;
rowList.Add(repRow);
}
}
return(rowList);
}
finally
{
if (connection != null)
{
connection.Close();
}
if (sqlreader != null)
{
sqlreader.Close();
}
}
}
public List <ReportRow> getData(String facilityId, String alarmDesc, String inputDesc, String stDate, String endDate, String stTime, String endTime, String daysStr, String monthsStr)
{
Report_AlarmDoor reportObj = Report_AlarmDoor.getAlarmDoorReportObj();
SqlConnection conn = null;
SqlDataReader sqlreader = null;
bool isLatestData = false;
try
{
// create and open a connection object
conn = ConnectionManager.getConnection();
conn.Open();
String query = "";
String ppQuery = "";
SqlCommand command = new SqlCommand();
List <ReportRow> rowList = new List <ReportRow>();
//This flag is used for dynamic query formation.
//It checks if AND or WHERE needs to be appended after the SELECT clause
Report_AlarmDoor.isConditionSelected = false;
int[] days = null;
int[] months = null;
//Convert the days string to an integer array
if (!daysStr.Equals("null"))
{
string[] d = daysStr.Split(',');
days = new int[d.Length];
for (int i = 0; i < d.Length; i++)
{
days[i] = Convert.ToInt32(d[i]);
}
}
//Convert the months string to an integer array
if (!monthsStr.Equals("null"))
{
string[] m = monthsStr.Split(',');
months = new int[m.Length];
for (int i = 0; i < m.Length; i++)
{
months[i] = Convert.ToInt32(m[i]);
}
}
//Check if the data needs to be pulled from PP
if (!stDate.Equals("null"))
{
DateTime startDateTime;
startDateTime = new DateTime(Convert.ToInt32(stDate.Substring(0, 4)), Convert.ToInt32(stDate.Substring(4, 2)), Convert.ToInt32(stDate.Substring(6, 2)), 0, 0, 0);
//Check if the date is today's date to pull today's data from PP
if (startDateTime.CompareTo(DateTime.Today) >= 0)
{
isLatestData = true;
}
}
if (!endDate.Equals("null"))
{
DateTime endDateTime;
endDateTime = new DateTime(Convert.ToInt32(endDate.Substring(0, 4)), Convert.ToInt32(endDate.Substring(4, 2)), Convert.ToInt32(endDate.Substring(6, 2)), 23, 59, 59);
if (endDateTime.CompareTo(DateTime.Today) >= 0)
{
isLatestData = true;
}
}
//Form the base query string
query = "SELECT INPUT_GROUP,INPUT_DESCRIPTION,ALARM_DESC,FACILITY,[HAPPENED_DATETIME],DATENAME(dw, [HAPPENED_DATETIME]) AS DAY FROM [view_rs_top_alarm_by_door]";
if (!facilityId.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
{
query = query + " AND [view_rs_top_alarm_by_door].FACILITY_ID = " + facilityId;
}
else
{
query = query + " WHERE [view_rs_top_alarm_by_door].FACILITY_ID = " + facilityId;
}
Report_AlarmDoor.isConditionSelected = true;
}
if (!alarmDesc.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
{
query = query + " AND [view_rs_top_alarm_by_door].ALARM_ID =" + alarmDesc;
}
else
{
query = query + " WHERE [view_rs_top_alarm_by_door].ALARM_ID =" + alarmDesc;
}
Report_AlarmDoor.isConditionSelected = true;
}
if (!inputDesc.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
{
query = query + " AND [view_rs_top_alarm_by_door].INPUT_ID =" + inputDesc;
}
else
{
query = query + " WHERE [view_rs_top_alarm_by_door].INPUT_ID =" + inputDesc;
}
Report_AlarmDoor.isConditionSelected = true;
}
if (!stTime.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
{
query = query + " AND @startTime <= [view_rs_top_alarm_by_door].HAPPENED_TIME ";
}
else
{
query = query + " WHERE @startTime <= [view_rs_top_alarm_by_door].HAPPENED_TIME ";
}
Report_AlarmDoor.isConditionSelected = true;
}
if (!endTime.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
{
query = query + " AND @endTime >= [view_rs_top_alarm_by_door].HAPPENED_TIME ";
}
else
{
query = query + " WHERE @endTime >= [view_rs_top_alarm_by_door].HAPPENED_TIME ";
}
Report_AlarmDoor.isConditionSelected = true;
}
if (days != null && days.Count() > 0)
{
//Forms the query string based on the days array
query = Report_AlarmDoor.getDaysQueryString(days, query);
}
if (months != null && months.Count() > 0)
{
//Forms the query string based on the months array
query = Report_AlarmDoor.getMonthQueryString(months, query);
}
ppQuery = query;
//Dynamic query creation
if (!stDate.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
{
query = query + " AND @startDate <= [view_rs_top_alarm_by_door].HAPPENED_DATETIME ";
}
else
{
query = query + " WHERE @startDate <= [view_rs_top_alarm_by_door].HAPPENED_DATETIME ";
}
Report_AlarmDoor.isConditionSelected = true;
}
if (!endDate.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
{
query = query + " AND @endDate >= [view_rs_top_alarm_by_door].HAPPENED_DATETIME ";
}
else
{
query = query + " WHERE @endDate >= [view_rs_top_alarm_by_door].HAPPENED_DATETIME ";
}
Report_AlarmDoor.isConditionSelected = true;
}
//Append the query date parameters
if (!stDate.Equals("null"))
{
DateTime startDateTime;
startDateTime = new DateTime(Convert.ToInt32(stDate.Substring(0, 4)), Convert.ToInt32(stDate.Substring(4, 2)), Convert.ToInt32(stDate.Substring(6, 2)), 0, 0, 0);
command.Parameters.AddWithValue("@startDate", startDateTime);
}
if (!endDate.Equals("null"))
{
DateTime endDateTime;
endDateTime = new DateTime(Convert.ToInt32(endDate.Substring(0, 4)), Convert.ToInt32(endDate.Substring(4, 2)), Convert.ToInt32(endDate.Substring(6, 2)), 23, 59, 59);
if (isLatestData)
{
//If the end time is later than yesterday, pull the data till yesterday from Reporting Server and the rest from PP
endDateTime = DateTime.Today.AddDays(-1).AddHours(23).AddMinutes(59).AddSeconds(59);
}
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"))
{
TimeSpan endQueryTime = new TimeSpan(Convert.ToInt32(endTime.Substring(0, 2)), Convert.ToInt32(endTime.Substring(2, 2)), Convert.ToInt32(endTime.Substring(4, 2)));
command.Parameters.AddWithValue("@endTime", endQueryTime);
}
//Set the sqlcommand
command.Connection = conn;
command.CommandText = query;
command.CommandTimeout = 300;
//Execute and fill the object array
int count = 0;
using (sqlreader = command.ExecuteReader())
{
//Read from the reader
while (sqlreader.Read())
{
//Fill in the object
//INPUT_GROUP,DOOR,ALARM_DESC,FACILITY,[HAPPENED_DATETIME],DATENAME(dw, [HAPPENED_DATETIME]) AS DAY
Report_AlarmDoor report = new Report_AlarmDoor();
report.InputGroup = sqlreader.GetSqlValue(0).ToString().Trim();
report.InputDesc = sqlreader.GetSqlValue(1).ToString().Trim();
report.Alarm = sqlreader.GetSqlValue(2).ToString().Trim();
report.Facility = sqlreader.GetSqlValue(3).ToString().Trim();
report.HappenedDateTime = sqlreader.GetSqlValue(4).ToString().Trim();
report.Day = sqlreader.GetSqlValue(5).ToString().Trim();
//Fill the report row object for response
ReportRow repRow = new ReportRow();
repRow.id = (++count).ToString();
repRow.datarow = report;
rowList.Add(repRow);
}
}
if (conn != null)
{
conn.Close();
}
if (sqlreader != null)
{
sqlreader.Close();
}
/*List<ReportRow> ppDataRow = new List<ReportRow>();
* if (isLatestData)
* {
* ppDataRow = getPPQueryData(ppQuery, stTime, endTime);
* }
*
* rowList.AddRange(ppDataRow);*/
return(rowList);
}
finally
{
if (conn != null)
{
conn.Close();
}
if (sqlreader != null)
{
sqlreader.Close();
}
}
}
public List<ReportRow> getData(String facilityId, String alarmDesc, String inputDesc, String stDate, String endDate, String stTime, String endTime, String daysStr, String monthsStr)
{
Report_AlarmDoor reportObj = Report_AlarmDoor.getAlarmDoorReportObj();
SqlConnection conn = null;
SqlDataReader sqlreader = null;
bool isLatestData = false;
try
{
// create and open a connection object
conn = ConnectionManager.getConnection();
conn.Open();
String query = "";
String ppQuery = "";
SqlCommand command = new SqlCommand();
List<ReportRow> rowList = new List<ReportRow>();
//This flag is used for dynamic query formation.
//It checks if AND or WHERE needs to be appended after the SELECT clause
Report_AlarmDoor.isConditionSelected = false;
int[] days = null;
int[] months = null;
//Convert the days string to an integer array
if (!daysStr.Equals("null"))
{
string[] d = daysStr.Split(',');
days = new int[d.Length];
for (int i = 0; i < d.Length; i++)
{
days[i] = Convert.ToInt32(d[i]);
}
}
//Convert the months string to an integer array
if (!monthsStr.Equals("null"))
{
string[] m = monthsStr.Split(',');
months = new int[m.Length];
for (int i = 0; i < m.Length; i++)
{
months[i] = Convert.ToInt32(m[i]);
}
}
//Check if the data needs to be pulled from PP
if (!stDate.Equals("null"))
{
DateTime startDateTime;
startDateTime = new DateTime(Convert.ToInt32(stDate.Substring(0, 4)), Convert.ToInt32(stDate.Substring(4, 2)), Convert.ToInt32(stDate.Substring(6, 2)), 0, 0, 0);
//Check if the date is today's date to pull today's data from PP
if (startDateTime.CompareTo(DateTime.Today) >= 0)
{
isLatestData = true;
}
}
if (!endDate.Equals("null"))
{
DateTime endDateTime;
endDateTime = new DateTime(Convert.ToInt32(endDate.Substring(0, 4)), Convert.ToInt32(endDate.Substring(4, 2)), Convert.ToInt32(endDate.Substring(6, 2)), 23, 59, 59);
if (endDateTime.CompareTo(DateTime.Today) >= 0)
{
isLatestData = true;
}
}
//Form the base query string
query = "SELECT INPUT_GROUP,INPUT_DESCRIPTION,ALARM_DESC,FACILITY,[HAPPENED_DATETIME],DATENAME(dw, [HAPPENED_DATETIME]) AS DAY FROM [view_rs_top_alarm_by_door]";
if (!facilityId.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
query = query + " AND [view_rs_top_alarm_by_door].FACILITY_ID = " + facilityId;
else
query = query + " WHERE [view_rs_top_alarm_by_door].FACILITY_ID = " + facilityId;
Report_AlarmDoor.isConditionSelected = true;
}
if (!alarmDesc.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
query = query + " AND [view_rs_top_alarm_by_door].ALARM_ID =" + alarmDesc;
else
query = query + " WHERE [view_rs_top_alarm_by_door].ALARM_ID =" + alarmDesc;
Report_AlarmDoor.isConditionSelected = true;
}
if (!inputDesc.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
query = query + " AND [view_rs_top_alarm_by_door].INPUT_ID =" + inputDesc;
else
query = query + " WHERE [view_rs_top_alarm_by_door].INPUT_ID =" + inputDesc;
Report_AlarmDoor.isConditionSelected = true;
}
if (!stTime.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
query = query + " AND @startTime <= [view_rs_top_alarm_by_door].HAPPENED_TIME ";
else
query = query + " WHERE @startTime <= [view_rs_top_alarm_by_door].HAPPENED_TIME ";
Report_AlarmDoor.isConditionSelected = true;
}
if (!endTime.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
query = query + " AND @endTime >= [view_rs_top_alarm_by_door].HAPPENED_TIME ";
else
query = query + " WHERE @endTime >= [view_rs_top_alarm_by_door].HAPPENED_TIME ";
Report_AlarmDoor.isConditionSelected = true;
}
if (days != null && days.Count() > 0)
{
//Forms the query string based on the days array
query = Report_AlarmDoor.getDaysQueryString(days, query);
}
if (months != null && months.Count() > 0)
{
//Forms the query string based on the months array
query = Report_AlarmDoor.getMonthQueryString(months, query);
}
ppQuery = query;
//Dynamic query creation
if (!stDate.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
query = query + " AND @startDate <= [view_rs_top_alarm_by_door].HAPPENED_DATETIME ";
else
query = query + " WHERE @startDate <= [view_rs_top_alarm_by_door].HAPPENED_DATETIME ";
Report_AlarmDoor.isConditionSelected = true;
}
if (!endDate.Equals("null"))
{
if (Report_AlarmDoor.isConditionSelected)
query = query + " AND @endDate >= [view_rs_top_alarm_by_door].HAPPENED_DATETIME ";
else
query = query + " WHERE @endDate >= [view_rs_top_alarm_by_door].HAPPENED_DATETIME ";
Report_AlarmDoor.isConditionSelected = true;
}
//Append the query date parameters
if (!stDate.Equals("null"))
{
DateTime startDateTime;
startDateTime = new DateTime(Convert.ToInt32(stDate.Substring(0, 4)), Convert.ToInt32(stDate.Substring(4, 2)), Convert.ToInt32(stDate.Substring(6, 2)), 0, 0, 0);
command.Parameters.AddWithValue("@startDate", startDateTime);
}
if (!endDate.Equals("null"))
{
DateTime endDateTime;
endDateTime = new DateTime(Convert.ToInt32(endDate.Substring(0, 4)), Convert.ToInt32(endDate.Substring(4, 2)), Convert.ToInt32(endDate.Substring(6, 2)), 23, 59, 59);
if(isLatestData)
{
//If the end time is later than yesterday, pull the data till yesterday from Reporting Server and the rest from PP
endDateTime = DateTime.Today.AddDays(-1).AddHours(23).AddMinutes(59).AddSeconds(59);
}
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"))
{
TimeSpan endQueryTime = new TimeSpan(Convert.ToInt32(endTime.Substring(0, 2)), Convert.ToInt32(endTime.Substring(2, 2)), Convert.ToInt32(endTime.Substring(4, 2)));
command.Parameters.AddWithValue("@endTime", endQueryTime);
}
//Set the sqlcommand
command.Connection = conn;
command.CommandText = query;
command.CommandTimeout = 300;
//Execute and fill the object array
int count = 0;
using (sqlreader = command.ExecuteReader())
{
//Read from the reader
while (sqlreader.Read())
{
//Fill in the object
//INPUT_GROUP,DOOR,ALARM_DESC,FACILITY,[HAPPENED_DATETIME],DATENAME(dw, [HAPPENED_DATETIME]) AS DAY
Report_AlarmDoor report = new Report_AlarmDoor();
report.InputGroup = sqlreader.GetSqlValue(0).ToString().Trim();
report.InputDesc = sqlreader.GetSqlValue(1).ToString().Trim();
report.Alarm = sqlreader.GetSqlValue(2).ToString().Trim();
report.Facility = sqlreader.GetSqlValue(3).ToString().Trim();
report.HappenedDateTime = sqlreader.GetSqlValue(4).ToString().Trim();
report.Day = sqlreader.GetSqlValue(5).ToString().Trim();
//Fill the report row object for response
ReportRow repRow = new ReportRow();
repRow.id = (++count).ToString();
repRow.datarow = report;
rowList.Add(repRow);
}
}
if (conn != null)
{
conn.Close();
}
if (sqlreader != null)
{
sqlreader.Close();
}
/*List<ReportRow> ppDataRow = new List<ReportRow>();
if (isLatestData)
{
ppDataRow = getPPQueryData(ppQuery, stTime, endTime);
}
rowList.AddRange(ppDataRow);*/
return rowList;
}
finally
{
if (conn != null)
{
conn.Close();
}
if (sqlreader != null)
{
sqlreader.Close();
}
}
}
public static List<ReportRow> getPPQueryData(String ppQuery,String stTime,String endTime)
{
List<ReportRow> rowList = new List<ReportRow>();
SqlConnection connection = null;
SqlDataReader sqlreader = null;
try
{
ppQuery = ppQuery.Replace("[view_rs_top_alarm_by_door]", "[view_rs_top_alarm_by_door_PP]");
// create and open a connection object
connection = ConnectionManager.getConnection();
connection.Open();
SqlCommand command = new SqlCommand();
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"))
{
TimeSpan endQueryTime = new TimeSpan(Convert.ToInt32(endTime.Substring(0, 2)), Convert.ToInt32(endTime.Substring(2, 2)), Convert.ToInt32(endTime.Substring(4, 2)));
command.Parameters.AddWithValue("@endTime", endQueryTime);
}
//Set the query string for the reporting server connection
command.Connection = connection;
command.CommandText = ppQuery;
int count = 0;
using (sqlreader = command.ExecuteReader())
{
//Read from the reader
while (sqlreader.Read())
{
Report_AlarmDoor report = new Report_AlarmDoor();
report.InputGroup = sqlreader.GetSqlValue(0).ToString().Trim();
report.InputDesc = sqlreader.GetSqlValue(1).ToString().Trim();
report.Alarm = sqlreader.GetSqlValue(2).ToString().Trim();
report.Facility = sqlreader.GetSqlValue(3).ToString().Trim();
report.HappenedDateTime = sqlreader.GetSqlValue(4).ToString().Trim();
report.Day = sqlreader.GetSqlValue(5).ToString().Trim();
//Fill the report row object for response
ReportRow repRow = new ReportRow();
repRow.id = (++count).ToString();
repRow.datarow = report;
rowList.Add(repRow);
}
}
return rowList;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (sqlreader != null)
{
sqlreader.Close();
}
}
}
