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 badgeId, String personId, String companyId, String divisionId, String empId, String categoryId, String wildCardType, String wildCardData) { Report_Audit reportObj = Report_Audit.getAccessReportObj(); SqlConnection conn = null; SqlDataReader sqlreader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query = ""; query = "SELECT TOP 1000 [FIRST_NAME],[LAST_NAME],[COMPANY_NAME],[DIVISION],[EMPLOYEE_ID],[CATEGORY_NAME],[BADGE_ID] FROM [view_rs_audit_report_1]"; Report_Audit.isConditionSelected = false; if (!badgeId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND [view_rs_audit_report_1].BADGE_ID like '" + badgeId+"'"; else query = query + " WHERE [view_rs_audit_report_1].BADGE_ID like '" + badgeId+"'"; Report_Audit.isConditionSelected = true; } if (!companyId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND [view_rs_audit_report_1].COMPANY_ID = " + companyId; else query = query + " WHERE [view_rs_audit_report_1].COMPANY_ID = " + companyId; Report_Audit.isConditionSelected = true; } if (!personId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND [view_rs_audit_report_1].PERSON_ID = " + personId; else query = query + " WHERE [view_rs_audit_report_1].PERSON_ID = " + personId; Report_Audit.isConditionSelected = true; } if (!empId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND [view_rs_audit_report_1].EMPLOYEE_ID = " + empId; else query = query + " WHERE [view_rs_audit_report_1].EMPLOYEE_ID = " + empId; Report_Audit.isConditionSelected = true; } if (!categoryId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND [view_rs_audit_report_1].CATEGORY_ID = " + categoryId; else query = query + " WHERE [view_rs_audit_report_1].CATEGORY_ID = " + categoryId; Report_Audit.isConditionSelected = true; } if (!divisionId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND [view_rs_audit_report_1].DIVISION_ID = " + divisionId; else query = query + " WHERE [view_rs_audit_report_1].DIVISION_ID = " + divisionId; Report_Audit.isConditionSelected = true; } query = getWildCardQueryString(wildCardType, wildCardData, query); SqlCommand command = new SqlCommand(query, conn); List<ReportRow> rowList = new List<ReportRow>(); int count = 0; using (sqlreader = command.ExecuteReader()) { while (sqlreader.Read()) { Report_Audit report = new Report_Audit(); report.CompanyName = sqlreader.GetSqlValue(2).ToString().Trim(); report.FirstName = sqlreader.GetSqlValue(0).ToString().Trim(); report.LastName = sqlreader.GetSqlValue(1).ToString().Trim(); report.Division = sqlreader.GetSqlValue(3).ToString().Trim(); report.EmpId = sqlreader.GetSqlValue(4).ToString().Trim(); report.Category = sqlreader.GetSqlValue(5).ToString().Trim(); report.Badge = sqlreader.GetSqlValue(6).ToString().Trim(); report.Name = report.FirstName + " " + report.LastName; 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 badgeId, String personId, String companyId, String divisionId, String empId, String categoryId, String badgeStatus,String wildCardType, String wildCardData) { Report_Audit reportObj = Report_Audit.getAccessReportObj(); SqlConnection conn = null; SqlDataReader sqlreader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query = ""; query = "SELECT distinct comp.companyId AS COMPANY_ID, comp.companyName AS COMPANY_NAME, div.divisionId AS DIVISION_ID, div.divisionName AS DIVISION, p.employee AS EMPLOYEE_ID,p.id AS PERSON_ID, p.first_name AS FIRST_NAME, p.last_name AS LAST_NAME, "; query = query + " c.description AS CATEGORY_NAME,c.id AS CATEGORY_ID, b.unique_id AS BADGE_ID, p.address3 AS ADDRESS,p.address5 AS ZIP_CODE,bsts.cond_desc as BADGE_STATUS"; query = query + " FROM person p, department d, category c, person_category pc,badge b, rs_company comp, rs_division div, badgests bsts "; query = query + " WHERE "; query = query + " b.bid is NOT NULL AND (LEN(b.bid) = 11 OR LEN(b.bid) = 12) "; query = query + " AND b.person_id is NOT NULL "; query = query + " AND c.id = pc.category_id AND p.id = pc.person_id "; query = query + " AND d.id = p.department AND b.person_id = p.id AND d.user1 Like comp.companyName AND d.user2 like div.divisionName AND bsts.id = b.status AND b.unique_id IS NOT NULL AND b.unique_id <> ''"; Report_Audit.isConditionSelected = true; if (!badgeId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND b.bid like '" + badgeId + "'"; else query = query + " WHERE b.bid like '" + badgeId + "'"; Report_Audit.isConditionSelected = true; } if (!companyId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND comp.companyId = " + companyId; else query = query + " WHERE comp.companyId = " + companyId; Report_Audit.isConditionSelected = true; } if (!personId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND p.employee = " + personId; else query = query + " WHERE p.employee = " + personId; Report_Audit.isConditionSelected = true; } if (!empId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND p.employee = " + empId; else query = query + " WHERE p.employee = " + empId; Report_Audit.isConditionSelected = true; } if (!categoryId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND c.id = " + categoryId; else query = query + " WHERE c.id = " + categoryId; Report_Audit.isConditionSelected = true; } if (!divisionId.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND div.divisionId = " + divisionId; else query = query + " WHERE div.divisionId = " + divisionId; Report_Audit.isConditionSelected = true; } if (!badgeStatus.Equals("null")) { if (Report_Audit.isConditionSelected) query = query + " AND b.status = " + badgeStatus; else query = query + " WHERE b.status = " + badgeStatus; Report_Audit.isConditionSelected = true; } query = getWildCardquery(wildCardType, wildCardData, query); SqlCommand command = new SqlCommand(query, conn); command.CommandTimeout = 300; List<ReportRow> rowList = new List<ReportRow>(); int count = 0; using (sqlreader = command.ExecuteReader()) { while (sqlreader.Read()) { Report_Audit report = new Report_Audit(); report.CompanyName = sqlreader.GetSqlValue(1).ToString().Trim(); report.FirstName = sqlreader.GetSqlValue(6).ToString().Trim(); report.LastName = sqlreader.GetSqlValue(7).ToString().Trim(); report.Division = sqlreader.GetSqlValue(3).ToString().Trim(); report.EmpId = sqlreader.GetSqlValue(4).ToString().Trim(); report.Category = sqlreader.GetSqlValue(8).ToString().Trim(); report.Badge = sqlreader.GetSqlValue(10).ToString().Trim(); report.Name = report.FirstName + " " + report.LastName; report.Status = sqlreader.GetSqlValue(13).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 badgeId, String personId, String companyId, String divisionId, String empId, String categoryId, String wildCardType, String wildCardData) * { * Report_Audit reportObj = Report_Audit.getAccessReportObj(); * SqlConnection conn = null; * * SqlDataReader sqlreader = null; * try * { * // create and open a connection object * conn = ConnectionManager.getConnection(); * conn.Open(); * * String query = ""; * query = "SELECT TOP 1000 [FIRST_NAME],[LAST_NAME],[COMPANY_NAME],[DIVISION],[EMPLOYEE_ID],[CATEGORY_NAME],[BADGE_ID] FROM [view_rs_audit_report_1]"; * * * Report_Audit.isConditionSelected = false; * * if (!badgeId.Equals("null")) * { * if (Report_Audit.isConditionSelected) * query = query + " AND [view_rs_audit_report_1].BADGE_ID like '" + badgeId+"'"; * else * query = query + " WHERE [view_rs_audit_report_1].BADGE_ID like '" + badgeId+"'"; * Report_Audit.isConditionSelected = true; * } * * if (!companyId.Equals("null")) * { * if (Report_Audit.isConditionSelected) * query = query + " AND [view_rs_audit_report_1].COMPANY_ID = " + companyId; * else * query = query + " WHERE [view_rs_audit_report_1].COMPANY_ID = " + companyId; * Report_Audit.isConditionSelected = true; * } * * * if (!personId.Equals("null")) * { * if (Report_Audit.isConditionSelected) * query = query + " AND [view_rs_audit_report_1].PERSON_ID = " + personId; * else * query = query + " WHERE [view_rs_audit_report_1].PERSON_ID = " + personId; * Report_Audit.isConditionSelected = true; * } * * if (!empId.Equals("null")) * { * if (Report_Audit.isConditionSelected) * query = query + " AND [view_rs_audit_report_1].EMPLOYEE_ID = " + empId; * else * query = query + " WHERE [view_rs_audit_report_1].EMPLOYEE_ID = " + empId; * Report_Audit.isConditionSelected = true; * } * * if (!categoryId.Equals("null")) * { * if (Report_Audit.isConditionSelected) * query = query + " AND [view_rs_audit_report_1].CATEGORY_ID = " + categoryId; * else * query = query + " WHERE [view_rs_audit_report_1].CATEGORY_ID = " + categoryId; * Report_Audit.isConditionSelected = true; * } * * if (!divisionId.Equals("null")) * { * if (Report_Audit.isConditionSelected) * query = query + " AND [view_rs_audit_report_1].DIVISION_ID = " + divisionId; * else * query = query + " WHERE [view_rs_audit_report_1].DIVISION_ID = " + divisionId; * Report_Audit.isConditionSelected = true; * } * * * query = getWildCardQueryString(wildCardType, wildCardData, query); * * SqlCommand command = new SqlCommand(query, conn); * List<ReportRow> rowList = new List<ReportRow>(); * int count = 0; * using (sqlreader = command.ExecuteReader()) * { * while (sqlreader.Read()) * { * Report_Audit report = new Report_Audit(); * report.CompanyName = sqlreader.GetSqlValue(2).ToString().Trim(); * report.FirstName = sqlreader.GetSqlValue(0).ToString().Trim(); * report.LastName = sqlreader.GetSqlValue(1).ToString().Trim(); * report.Division = sqlreader.GetSqlValue(3).ToString().Trim(); * report.EmpId = sqlreader.GetSqlValue(4).ToString().Trim(); * report.Category = sqlreader.GetSqlValue(5).ToString().Trim(); * report.Badge = sqlreader.GetSqlValue(6).ToString().Trim(); * report.Name = report.FirstName + " " + report.LastName; * * 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 badgeId, String personId, String companyId, String divisionId, String empId, String categoryId, String badgeStatus, String wildCardType, String wildCardData) { Report_Audit reportObj = Report_Audit.getAccessReportObj(); SqlConnection conn = null; SqlDataReader sqlreader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query = ""; query = "SELECT distinct comp.companyId AS COMPANY_ID, comp.companyName AS COMPANY_NAME, div.divisionId AS DIVISION_ID, div.divisionName AS DIVISION, p.employee AS EMPLOYEE_ID,p.id AS PERSON_ID, p.first_name AS FIRST_NAME, p.last_name AS LAST_NAME, "; query = query + " c.description AS CATEGORY_NAME,c.id AS CATEGORY_ID, b.unique_id AS BADGE_ID, p.address3 AS ADDRESS,p.address5 AS ZIP_CODE,bsts.cond_desc as BADGE_STATUS"; query = query + " FROM person p, department d, category c, person_category pc,badge b, rs_company comp, rs_division div, badgests bsts "; query = query + " WHERE "; query = query + " b.bid is NOT NULL AND (LEN(b.bid) = 11 OR LEN(b.bid) = 12) "; query = query + " AND b.person_id is NOT NULL "; query = query + " AND c.id = pc.category_id AND p.id = pc.person_id "; query = query + " AND d.id = p.department AND b.person_id = p.id AND d.user1 Like comp.companyName AND d.user2 like div.divisionName AND bsts.id = b.status AND b.unique_id IS NOT NULL AND b.unique_id <> ''"; Report_Audit.isConditionSelected = true; if (!badgeId.Equals("null")) { if (Report_Audit.isConditionSelected) { query = query + " AND b.bid like '" + badgeId + "'"; } else { query = query + " WHERE b.bid like '" + badgeId + "'"; } Report_Audit.isConditionSelected = true; } if (!companyId.Equals("null")) { if (Report_Audit.isConditionSelected) { query = query + " AND comp.companyId = " + companyId; } else { query = query + " WHERE comp.companyId = " + companyId; } Report_Audit.isConditionSelected = true; } if (!personId.Equals("null")) { if (Report_Audit.isConditionSelected) { query = query + " AND p.employee = " + personId; } else { query = query + " WHERE p.employee = " + personId; } Report_Audit.isConditionSelected = true; } if (!empId.Equals("null")) { if (Report_Audit.isConditionSelected) { query = query + " AND p.employee = " + empId; } else { query = query + " WHERE p.employee = " + empId; } Report_Audit.isConditionSelected = true; } if (!categoryId.Equals("null")) { if (Report_Audit.isConditionSelected) { query = query + " AND c.id = " + categoryId; } else { query = query + " WHERE c.id = " + categoryId; } Report_Audit.isConditionSelected = true; } if (!divisionId.Equals("null")) { if (Report_Audit.isConditionSelected) { query = query + " AND div.divisionId = " + divisionId; } else { query = query + " WHERE div.divisionId = " + divisionId; } Report_Audit.isConditionSelected = true; } if (!badgeStatus.Equals("null")) { if (Report_Audit.isConditionSelected) { query = query + " AND b.status = " + badgeStatus; } else { query = query + " WHERE b.status = " + badgeStatus; } Report_Audit.isConditionSelected = true; } query = getWildCardquery(wildCardType, wildCardData, query); SqlCommand command = new SqlCommand(query, conn); command.CommandTimeout = 300; List <ReportRow> rowList = new List <ReportRow>(); int count = 0; using (sqlreader = command.ExecuteReader()) { while (sqlreader.Read()) { Report_Audit report = new Report_Audit(); report.CompanyName = sqlreader.GetSqlValue(1).ToString().Trim(); report.FirstName = sqlreader.GetSqlValue(6).ToString().Trim(); report.LastName = sqlreader.GetSqlValue(7).ToString().Trim(); report.Division = sqlreader.GetSqlValue(3).ToString().Trim(); report.EmpId = sqlreader.GetSqlValue(4).ToString().Trim(); report.Category = sqlreader.GetSqlValue(8).ToString().Trim(); report.Badge = sqlreader.GetSqlValue(10).ToString().Trim(); report.Name = report.FirstName + " " + report.LastName; report.Status = sqlreader.GetSqlValue(13).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 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(); } } }
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 areaId,String categoryId,String doorId) { Report_DoorCategory reportObj = Report_DoorCategory.getAccessReportObj(); SqlConnection conn = null; SqlDataReader sqlreader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query = ""; query = "SELECT [AREA],[CATEGORY],[DOOR] FROM [view_rs_door_category_report]"; Report_DoorCategory.isConditionSelected = false; if (!areaId.Equals("null")) { if (Report_DoorCategory.isConditionSelected) query = query + " AND [view_rs_door_category_report].AREA_ID = " + areaId; else query = query + " WHERE [view_rs_door_category_report].AREA_ID = " + areaId; Report_DoorCategory.isConditionSelected = true; } if (!categoryId.Equals("null")) { if (Report_DoorCategory.isConditionSelected) query = query + " AND [view_rs_door_category_report].CATEGORY_ID = " + categoryId; else query = query + " WHERE [view_rs_door_category_report].CATEGORY_ID = " + categoryId; Report_DoorCategory.isConditionSelected = true; } if (!doorId.Equals("null")) { if (Report_DoorCategory.isConditionSelected) query = query + " AND [view_rs_door_category_report].DOOR_ID = " + doorId; else query = query + " WHERE [view_rs_door_category_report].DOOR_ID = " + doorId; Report_DoorCategory.isConditionSelected = true; } SqlCommand command = new SqlCommand(query, conn); command.CommandTimeout = 300; List<ReportRow> rowList = new List<ReportRow>(); int count = 0; using (sqlreader = command.ExecuteReader()) { while (sqlreader.Read()) { Report_DoorCategory report = new Report_DoorCategory(); report.Area = sqlreader.GetSqlValue(0).ToString().Trim(); report.Category = sqlreader.GetSqlValue(1).ToString().Trim(); report.Door = sqlreader.GetSqlValue(2).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(); } } }
//Gets the report based on the parameters //Here {days} and {months} are the integer arrays. A value of 0 represents ALL. //Date Format : yyyymmdd //Time Format : hhmmss public List<ReportRow> getData(String badgeId, String personId, String companyId, String divisionId, String empId, String status, String stDate, String endDate, String daysStr, String monthsStr,String wildCardType,String wildCardText) { Report_BadgeStatus reportObj = Report_BadgeStatus.getBadgeStatusReportReportObj(); 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>(); //This flag is used for dynamic query formation. //It checks if AND or WHERE needs to be appended after the SELECT clause Report_BadgeStatus.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]); } } //Form the base query string query = "SELECT [employee],[first_name],[last_name],[user1],[user2],[BADGE_ID],[cond_desc],[ISSUE_DATETIME],[EXPIRED_DATETIME],[RETURN_DATETIME],DATENAME(dw, [EXPIRED_DATETIME]) AS DAYS,[PHY_BADGE_ID] FROM [view_rs_badge_status_report]"; //Dynamic query creation if (!stDate.Equals("null")) { Report_BadgeStatus.isConditionSelected = true; query = query + " WHERE @startDate <= [view_rs_badge_status_report].EXPIRED_DATETIME "; } if (!endDate.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) query = query + " AND @endDate >= [view_rs_badge_status_report].EXPIRED_DATETIME "; else query = query + " WHERE @endDate >= [view_rs_badge_status_report].EXPIRED_DATETIME "; Report_BadgeStatus.isConditionSelected = true; } if (!badgeId.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) query = query + " AND [view_rs_badge_status_report].PHY_BADGE_ID like '" + badgeId + "'"; else query = query + " WHERE [view_rs_badge_status_report].PHY_BADGE_ID like '" + badgeId + "'"; Report_BadgeStatus.isConditionSelected = true; } if (!companyId.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) query = query + " AND [view_rs_badge_status_report].COMPANY_ID =" + companyId; else query = query + " WHERE [view_rs_badge_status_report].COMPANY_ID =" + companyId; Report_BadgeStatus.isConditionSelected = true; } if (!personId.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) query = query + " AND [view_rs_badge_status_report].employee =" + personId ; else query = query + " WHERE [view_rs_badge_status_report].employee =" + personId; Report_BadgeStatus.isConditionSelected = true; } if (!status.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) query = query + " AND [view_rs_badge_status_report].STATUS_ID = " + status; else query = query + " WHERE [view_rs_badge_status_report].STATUS_ID = " + status; Report_BadgeStatus.isConditionSelected = true; } if (!divisionId.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) query = query + " AND [view_rs_badge_status_report].DIVISION_ID = " + divisionId; else query = query + " WHERE [view_rs_badge_status_report].DIVISION_ID = " + divisionId; Report_BadgeStatus.isConditionSelected = true; } //Append all the wild card query conditions query = getWildCardquery(wildCardType, wildCardText, query); if (days != null && days.Count() > 0) { //Forms the query string based on the days array query = Report_BadgeStatus.getDaysQueryString(days, query); } if (months != null && months.Count() > 0) { //Forms the query string based on the months array query = Report_BadgeStatus.getMonthQueryString(months, query); } //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); command.Parameters.AddWithValue("@endDate", endDateTime); } //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 Report_BadgeStatus report = new Report_BadgeStatus(); report.EmployeeID = sqlreader.GetSqlValue(0).ToString().Trim(); report.FirstName = sqlreader.GetSqlValue(1).ToString().Trim(); report.LastName = sqlreader.GetSqlValue(2).ToString().Trim(); report.Company = sqlreader.GetSqlValue(3).ToString().Trim(); report.Division = sqlreader.GetSqlValue(4).ToString().Trim(); report.Badge = sqlreader.GetSqlValue(5).ToString().Trim(); report.BadgeStatus = sqlreader.GetSqlValue(6).ToString().Trim(); report.IssueDateTime = sqlreader.GetSqlValue(7).ToString().Trim(); report.ExpiredDateTime = sqlreader.GetSqlValue(8).ToString().Trim(); report.ReturnDateTime = sqlreader.GetSqlValue(9).ToString().Trim(); if (report.ReturnDateTime.Equals("Null") || report.ReturnDateTime.Equals("null") || report.ReturnDateTime.Equals("NULL")) report.ReturnDateTime = "N/A"; report.invalidDay = sqlreader.GetSqlValue(10).ToString().Trim(); report.CardNo = sqlreader.GetSqlValue(11).ToString().Trim().Substring(4); report.Name = report.FirstName + " " + report.LastName; //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 (conn != null) { conn.Close(); } if (sqlreader != null) { sqlreader.Close(); } } }
public List <ReportRow> getData(String areaId, String categoryId, String doorId) { Report_DoorCategory reportObj = Report_DoorCategory.getAccessReportObj(); SqlConnection conn = null; SqlDataReader sqlreader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query = ""; query = "SELECT [AREA],[CATEGORY],[DOOR] FROM [view_rs_door_category_report]"; Report_DoorCategory.isConditionSelected = false; if (!areaId.Equals("null")) { if (Report_DoorCategory.isConditionSelected) { query = query + " AND [view_rs_door_category_report].AREA_ID = " + areaId; } else { query = query + " WHERE [view_rs_door_category_report].AREA_ID = " + areaId; } Report_DoorCategory.isConditionSelected = true; } if (!categoryId.Equals("null")) { if (Report_DoorCategory.isConditionSelected) { query = query + " AND [view_rs_door_category_report].CATEGORY_ID = " + categoryId; } else { query = query + " WHERE [view_rs_door_category_report].CATEGORY_ID = " + categoryId; } Report_DoorCategory.isConditionSelected = true; } if (!doorId.Equals("null")) { if (Report_DoorCategory.isConditionSelected) { query = query + " AND [view_rs_door_category_report].DOOR_ID = " + doorId; } else { query = query + " WHERE [view_rs_door_category_report].DOOR_ID = " + doorId; } Report_DoorCategory.isConditionSelected = true; } SqlCommand command = new SqlCommand(query, conn); command.CommandTimeout = 300; List <ReportRow> rowList = new List <ReportRow>(); int count = 0; using (sqlreader = command.ExecuteReader()) { while (sqlreader.Read()) { Report_DoorCategory report = new Report_DoorCategory(); report.Area = sqlreader.GetSqlValue(0).ToString().Trim(); report.Category = sqlreader.GetSqlValue(1).ToString().Trim(); report.Door = sqlreader.GetSqlValue(2).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> getDataByFilter(String badgeId, String personId, String companyId, String divisionId, String empId, String status, String areaString, String stDate, String stTime, String endDate, String endTime, String daysStr, String monthsStr, String wildCardType, String wildCardData) { Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Entry Point", Logger.logLevel.Info); Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, badgeId + "::" + personId + "::" + companyId + "::" + divisionId + "::" + empId + "::" + status + "::" + areaString + "::" + stDate + "::" + stTime + "::" + endDate + "::" + endTime + "::" + daysStr + "::" + monthsStr + "::" + wildCardType + "::" + wildCardData, Logger.logLevel.Debug); Report_Activity reportObj = Report_Activity.getAccessReportObj(); SqlConnection conn = null; List<ReportRow> rowList = new List<ReportRow>(); int[] days = null; int[] months = null; //Form the days 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]); } } //Form the months 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]); } } SqlDataReader sqlreader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); //Checks if condition is selected Report_Activity.isConditionSelected = false; //This string forms the query parameters String query = ""; //This string forms the query parameters for PP query (latest data) String ppQuery = ""; //This string forms the SELECT and FROM queryString for Reporting server query String mainSelectStatement = ""; //This string forms the SELECT and FROM queryString for PP query String mainPPSelectStatement = ""; //This sets if we need to query PP bool isLatestData = false; //Form the select query based on Area/facility selection //Criteria 1: facility //Criteria 2: area //Criteria 3: Nothing /* if (isFacilitySelected(areaString)) mainSelectStatement = getMainQueryString(1,stDate,endDate,companyId,divisionId); else if (isAreaSelected(areaString)) mainSelectStatement = getMainQueryString(2, stDate, endDate, companyId, divisionId); else*/ mainSelectStatement = getMainQueryString(0, stDate, endDate, companyId, divisionId); Report_Activity.isConditionSelected = true; //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.Date.CompareTo(DateTime.Today.Date) >= 0) { isLatestData = true; } } //Time query if (!stTime.Equals("null")) { //Pasadena query = query + " AND @startTime <= CAST([ACAMS_Import_Production_History].[dbo].[badge_history].xact_datetime AS TIME) "; // query = query + " AND @startTime <= (CAST(STR(FLOOR(dbo.badge_history.dev_xact_time / 10000), 2, 0)+ ':' + RIGHT(STR(FLOOR(dbo.badge_history.dev_xact_time / 100), 6, 0), 2)+ ':' + RIGHT(STR(dbo.badge_history.dev_xact_time), 2) AS TIME))"; } //Time query if (!endTime.Equals("null")) { //Pasadena query = query + " AND @endTime >= CAST([ACAMS_Import_Production_History].[dbo].[badge_history].xact_datetime AS TIME)"; //query = query + " AND @endTime >= (CAST(STR(FLOOR(dbo.badge_history.dev_xact_time / 10000), 2, 0)+ ':' + RIGHT(STR(FLOOR(dbo.badge_history.dev_xact_time / 100), 6, 0), 2)+ ':' + RIGHT(STR(dbo.badge_history.dev_xact_time), 2) AS TIME))"; } //Company condition if (!companyId.Equals("null")) { query = query + " AND dbo.rs_company.companyId =" + companyId; } //Name condition if (!personId.Equals("null")) { query = query + " AND [ACAMS_Import_Production_History].[dbo].[badge_history].employee = " + personId; } //Status condition if (!status.Equals("null")) { //Pasadena //query = query + " AND badge_history.xact_type = " + status; query = query + " AND [ACAMS_Import_Production_History].[dbo].[badge_history].xact_type = " + status; } //Division condition if (!divisionId.Equals("null")) { query = query + " AND dbo.rs_division.divisionId = " + divisionId; } //Append all the wild card query conditions query = getWildCardquery(wildCardType, wildCardData, query); //badge condition //support multiple badges if (!badgeId.Equals("null")) { String[] badgeArray = badgeId.Split(','); query = query + " AND ( [ACAMS_Import_Production_History].[dbo].[badge_history].badgeno = " + badgeArray[0]; for (int badgeCount = 1; badgeCount < badgeArray.Length; badgeCount++) { if (!badgeArray[badgeCount].Trim().Equals("")) query = query + " OR [ACAMS_Import_Production_History].[dbo].[badge_history].badgeno = " + badgeArray[badgeCount]; } query = query + " ) "; } //Copy all the conditions of reporting server query to PP query ppQuery = query; if (days != null && days.Count() > 0) { query = Report_Activity.getDaysqueryPasadena(days, query); } if (months != null && months.Count() > 0) { query = Report_Activity.getMonthqueryPasadena(months, query); } if (!areaString.Equals("null")) { mainSelectStatement = Report_Activity.getFacilityAreaQuery(areaString, query,stDate,endDate,companyId,divisionId); } else { mainSelectStatement = mainSelectStatement + query; } SqlCommand command = new SqlCommand(); 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); command.Parameters.AddWithValue("@endDate", endDateTime); 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); } } 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 = conn; command.CommandText = mainSelectStatement; command.CommandTimeout = 300; sqlreader = command.ExecuteReader(); DataTable myTable = new DataTable(); myTable.Load(sqlreader); int count = 0; foreach (DataRow row in myTable.Rows) { Report_Activity report = new Report_Activity(); //if (row["BADGE_ID"].ToString().Trim().Length > 6) report.Badge = row["BADGE_ID"].ToString().Trim(); // else //report.Badge = "N/A"; report.Company = row["COMPANY"].ToString().Trim(); report.FirstName = row["FIRST_NAME"].ToString().Trim(); report.LastName = row["LAST_NAME"].ToString().Trim(); report.Reader = row["READER"].ToString().Trim(); report.Status = row["STATUS"].ToString().Trim(); report.Name = row["FIRST_NAME"].ToString().Trim() + " " + row["LAST_NAME"].ToString().Trim(); report.DateHistory = row["ACCESS_DATETIME"].ToString().Trim(); report.Day = row["DAYS"].ToString().Trim(); report.AccessTime = row["ACCESS_TIME"].ToString().Trim(); report.EmpId = row["EMP_ID"].ToString().Trim(); report.PersonId = row["PERSON_ID"].ToString().Trim(); ReportRow repRow = new ReportRow(); repRow.id = (++count).ToString(); repRow.datarow = report; rowList.Add(repRow); } if (conn != null) { conn.Close(); } if (sqlreader != null) { sqlreader.Close(); } //LAXCHANGES List<ReportRow> ppDataRows = new List<ReportRow>(); //Get the latest data from PP //********************************************************************** if (isLatestData) { /*if (isFacilitySelected(areaString)) mainPPSelectStatement = getPPData(1,companyId,divisionId); else if (isAreaSelected(areaString)) mainPPSelectStatement = getPPData(2); else*/ mainPPSelectStatement = getPPData(0,companyId,divisionId); ppQuery = getPPConditionString(badgeId, personId, companyId, divisionId, "", status, stTime, endTime, wildCardType, wildCardData); ppQuery = getWildCardquery(wildCardType, wildCardData, ppQuery); if (!areaString.Equals("null")) { mainPPSelectStatement = Report_Activity.getPPFacilityAreaQuery(areaString, ppQuery,companyId,divisionId); } else { mainPPSelectStatement = mainPPSelectStatement + ppQuery; } ppDataRows = getPPDataFromView(mainPPSelectStatement, stTime, endTime); } rowList.AddRange(ppDataRows); //********************************************************************** Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Info); } catch (Exception ex) { var stackTrace = new StackTrace(ex, true); var line = stackTrace.GetFrame(0).GetFileLineNumber(); Logger.LogExceptions(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, ex.Message, line.ToString(), Logger.logLevel.Exception); } finally { if (conn != null) { conn.Close(); } if (sqlreader != null) { sqlreader.Close(); } } return rowList; }
private static List<ReportRow> getPPDataFromView(String mainPPSelectStatement, String stTime, String endTime) { Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Entry Point", Logger.logLevel.Info); Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, mainPPSelectStatement + "::" + stTime + "::" + endTime, Logger.logLevel.Debug); List<ReportRow> rowList = new List<ReportRow>(); SqlConnection connection = null; SqlDataReader sqlreader = null; try { // 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 = mainPPSelectStatement; command.CommandTimeout = 300; int count = 0; using (sqlreader = command.ExecuteReader()) { //Read from the reader while (sqlreader.Read()) { Report_Activity report = new Report_Activity(); // if (sqlreader.GetSqlValue(9).ToString().Trim().Length > 6) report.Badge = sqlreader.GetSqlValue(9).ToString().Trim(); // else //report.Badge = "N/A"; report.Company = sqlreader.GetSqlValue(4).ToString().Trim(); report.FirstName = sqlreader.GetSqlValue(2).ToString().Trim(); report.LastName = sqlreader.GetSqlValue(3).ToString().Trim(); report.Reader = sqlreader.GetSqlValue(1).ToString().Trim(); report.Status = sqlreader.GetSqlValue(0).ToString().Trim(); ; report.Name = report.FirstName + " " + report.LastName; report.DateHistory = sqlreader.GetSqlValue(7).ToString().Trim(); report.Day = sqlreader.GetSqlValue(10).ToString().Trim(); report.AccessTime = sqlreader.GetSqlValue(8).ToString().Trim(); report.EmpId = sqlreader.GetSqlValue(5).ToString().Trim(); report.PersonId = sqlreader.GetSqlValue(6).ToString().Trim(); ReportRow repRow = new ReportRow(); repRow.id = (++count).ToString(); repRow.datarow = report; rowList.Add(repRow); } } } catch (Exception ex) { var stackTrace = new StackTrace(ex, true); var line = stackTrace.GetFrame(0).GetFileLineNumber(); Logger.LogExceptions(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, ex.Message, line.ToString(), Logger.logLevel.Exception); } finally { if (connection != null) { connection.Close(); } if (sqlreader != null) { sqlreader.Close(); } } Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Entry Point", Logger.logLevel.Info); return rowList; }
//Gets the report based on the parameters //Here {days} and {months} are the integer arrays. A value of 0 represents ALL. //Date Format : yyyymmdd //Time Format : hhmmss public List <ReportRow> getData(String badgeId, String personId, String companyId, String divisionId, String empId, String status, String stDate, String endDate, String daysStr, String monthsStr, String wildCardType, String wildCardText) { Report_BadgeStatus reportObj = Report_BadgeStatus.getBadgeStatusReportReportObj(); 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>(); //This flag is used for dynamic query formation. //It checks if AND or WHERE needs to be appended after the SELECT clause Report_BadgeStatus.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]); } } //Form the base query string query = "SELECT [employee],[first_name],[last_name],[user1],[user2],[BADGE_ID],[cond_desc],[ISSUE_DATETIME],[EXPIRED_DATETIME],[RETURN_DATETIME],DATENAME(dw, [EXPIRED_DATETIME]) AS DAYS,[PHY_BADGE_ID] FROM [view_rs_badge_status_report]"; //Dynamic query creation if (!stDate.Equals("null")) { Report_BadgeStatus.isConditionSelected = true; query = query + " WHERE @startDate <= [view_rs_badge_status_report].EXPIRED_DATETIME "; } if (!endDate.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) { query = query + " AND @endDate >= [view_rs_badge_status_report].EXPIRED_DATETIME "; } else { query = query + " WHERE @endDate >= [view_rs_badge_status_report].EXPIRED_DATETIME "; } Report_BadgeStatus.isConditionSelected = true; } if (!badgeId.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) { query = query + " AND [view_rs_badge_status_report].PHY_BADGE_ID like '" + badgeId + "'"; } else { query = query + " WHERE [view_rs_badge_status_report].PHY_BADGE_ID like '" + badgeId + "'"; } Report_BadgeStatus.isConditionSelected = true; } if (!companyId.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) { query = query + " AND [view_rs_badge_status_report].COMPANY_ID =" + companyId; } else { query = query + " WHERE [view_rs_badge_status_report].COMPANY_ID =" + companyId; } Report_BadgeStatus.isConditionSelected = true; } if (!personId.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) { query = query + " AND [view_rs_badge_status_report].employee =" + personId; } else { query = query + " WHERE [view_rs_badge_status_report].employee =" + personId; } Report_BadgeStatus.isConditionSelected = true; } if (!status.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) { query = query + " AND [view_rs_badge_status_report].STATUS_ID = " + status; } else { query = query + " WHERE [view_rs_badge_status_report].STATUS_ID = " + status; } Report_BadgeStatus.isConditionSelected = true; } if (!divisionId.Equals("null")) { if (Report_BadgeStatus.isConditionSelected) { query = query + " AND [view_rs_badge_status_report].DIVISION_ID = " + divisionId; } else { query = query + " WHERE [view_rs_badge_status_report].DIVISION_ID = " + divisionId; } Report_BadgeStatus.isConditionSelected = true; } //Append all the wild card query conditions query = getWildCardquery(wildCardType, wildCardText, query); if (days != null && days.Count() > 0) { //Forms the query string based on the days array query = Report_BadgeStatus.getDaysQueryString(days, query); } if (months != null && months.Count() > 0) { //Forms the query string based on the months array query = Report_BadgeStatus.getMonthQueryString(months, query); } //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); command.Parameters.AddWithValue("@endDate", endDateTime); } //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 Report_BadgeStatus report = new Report_BadgeStatus(); report.EmployeeID = sqlreader.GetSqlValue(0).ToString().Trim(); report.FirstName = sqlreader.GetSqlValue(1).ToString().Trim(); report.LastName = sqlreader.GetSqlValue(2).ToString().Trim(); report.Company = sqlreader.GetSqlValue(3).ToString().Trim(); report.Division = sqlreader.GetSqlValue(4).ToString().Trim(); report.Badge = sqlreader.GetSqlValue(5).ToString().Trim(); report.BadgeStatus = sqlreader.GetSqlValue(6).ToString().Trim(); report.IssueDateTime = sqlreader.GetSqlValue(7).ToString().Trim(); report.ExpiredDateTime = sqlreader.GetSqlValue(8).ToString().Trim(); report.ReturnDateTime = sqlreader.GetSqlValue(9).ToString().Trim(); if (report.ReturnDateTime.Equals("Null") || report.ReturnDateTime.Equals("null") || report.ReturnDateTime.Equals("NULL")) { report.ReturnDateTime = "N/A"; } report.invalidDay = sqlreader.GetSqlValue(10).ToString().Trim(); report.CardNo = sqlreader.GetSqlValue(11).ToString().Trim().Substring(4); report.Name = report.FirstName + " " + report.LastName; //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 (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(); } } }