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