public static Report_Activity getAccessReportObj()
{
if (accessReportObj == null)
{
accessReportObj = new Report_Activity();
}
return accessReportObj;
}
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;
}
