//Gets the current object public static Report_BadgeStatus getBadgeStatusReportReportObj() { //First time create an object if (badgeStatusReportReportObj == null) { badgeStatusReportReportObj = new Report_BadgeStatus(); } return(badgeStatusReportReportObj); }
//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(); } } }
//Gets the current object public static Report_BadgeStatus getBadgeStatusReportReportObj() { //First time create an object if (badgeStatusReportReportObj == null) { badgeStatusReportReportObj = new Report_BadgeStatus(); } return badgeStatusReportReportObj; }
//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(); } } }