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