public byte[] Get_Form_Records(int parintCurrentUserNo, string parstrAccessInd)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
DataSet DataSet = new DataSet();
StringBuilder strQry = new StringBuilder();
string strSoftwareToUse = "D";
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" IDENTIFY_THRESHOLD_VALUE");
strQry.AppendLine(",VERIFY_THRESHOLD_VALUE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_IDENTIFY_VERIFY_THRESHOLD");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "FingerprintThreshold");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FINGERPRINT_SOFTWARE_IND");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_SOFTWARE_TO_USE");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "SoftwareToUse");
if (DataSet.Tables["SoftwareToUse"].Rows.Count > 0)
{
strSoftwareToUse = DataSet.Tables["SoftwareToUse"].Rows[0]["FINGERPRINT_SOFTWARE_IND"].ToString();
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" READ_OPTION_NO");
strQry.AppendLine(",READ_OPTION_DESC");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.READ_OPTION");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" READ_OPTION_NO");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "ReadOption");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine("'" + clsDBConnectionObjects.Get_ClientConnectionString().Replace("InteractPayrollClient_Debug", "InteractPayrollClient") + "' AS DB_CONNECTION_STRING");
//Will Always be Only 1 Row
strQry.AppendLine(" FROM InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS ");
strQry.AppendLine(" WHERE TABLE_NAME = 'COMPANY'");
strQry.AppendLine(" AND COLUMN_NAME = 'COMPANY_NO'");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Connection");
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" PC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.PAY_CATEGORY PC");
strQry.AppendLine(" ON C.COMPANY_NO = PC.COMPANY_NO");
if (parstrAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON UCA.USER_NO = " + parintCurrentUserNo);
strQry.AppendLine(" AND C.COMPANY_NO = UCA.COMPANY_NO");
}
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
DataSet.Tables.Add("PayrollType");
DataTable PayrollTypeDataTable = new DataTable("PayrollType");
DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));
DataView PayrollTypeDataView = new DataView(DataSet.Tables["Temp"],
"PAY_CATEGORY_TYPE = 'W'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Wages";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
PayrollTypeDataView = null;
PayrollTypeDataView = new DataView(DataSet.Tables["Temp"],
"PAY_CATEGORY_TYPE = 'S'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Salaries";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
PayrollTypeDataView = null;
PayrollTypeDataView = new DataView(DataSet.Tables["Temp"],
"PAY_CATEGORY_TYPE = 'T'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Time Attendance";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
DataSet.AcceptChanges();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" C.COMPANY_NO");
strQry.AppendLine(",C.COMPANY_DESC");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");
if (parstrAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UC ");
strQry.AppendLine(" ON UC.USER_NO = " + parintCurrentUserNo);
strQry.AppendLine(" AND UC.COMPANY_NO = C.COMPANY_NO ");
//2013-07-10
strQry.AppendLine(" AND UC.COMPANY_ACCESS_IND = 'A'");
}
strQry.AppendLine(" ORDER BY C.COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company");
if (DataSet.Tables["Company"].Rows.Count > 0)
{
byte[] bytTempCompress = Get_Company_Records_New(Convert.ToInt64(DataSet.Tables["Company"].Rows[0]["COMPANY_NO"]), strSoftwareToUse);
DataSet TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
DataSet.Merge(TempDataSet);
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(int parintCurrentUserNo, string parstrAccessInd)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
DataSet DataSet = new DataSet();
StringBuilder strQry = new StringBuilder();
string strSoftwareToUse = "D";
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" C.COMPANY_NO");
strQry.AppendLine(",C.COMPANY_DESC");
strQry.AppendLine(",'" + clsDBConnectionObjects.Get_ClientConnectionString().Replace("InteractPayrollClient_Debug", "InteractPayrollClient") + "' AS DB_CONNECTION_STRING");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine("'" + clsDBConnectionObjects.Get_ClientConnectionString().Replace("InteractPayrollClient_Debug", "InteractPayrollClient") + "' AS DB_CONNECTION_STRING");
//Will Always be Only 1 Row
strQry.AppendLine(" FROM InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS ");
strQry.AppendLine(" WHERE TABLE_NAME = 'COMPANY'");
strQry.AppendLine(" AND COLUMN_NAME = 'COMPANY_NO'");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Connection");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FIVT.IDENTIFY_THRESHOLD_VALUE");
strQry.AppendLine(",FIVT.VERIFY_THRESHOLD_VALUE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_IDENTIFY_VERIFY_THRESHOLD FIVT");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "FingerprintThreshold");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FINGERPRINT_SOFTWARE_IND");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_SOFTWARE_TO_USE");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "SoftwareToUse");
if (DataSet.Tables["SoftwareToUse"].Rows.Count > 0)
{
strSoftwareToUse = DataSet.Tables["SoftwareToUse"].Rows[0]["FINGERPRINT_SOFTWARE_IND"].ToString();
}
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT ");
strQry.AppendLine(" UI.USER_NO");
strQry.AppendLine(",UI.USER_ID");
strQry.AppendLine(",UI.FIRSTNAME");
strQry.AppendLine(",UI.SURNAME");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID UI");
if (parstrAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON UI.USER_NO = UCA.USER_NO");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA1");
strQry.AppendLine(" ON UCA.COMPANY_NO = UCA1.COMPANY_NO");
strQry.AppendLine(" AND UCA1.USER_NO = " + parintCurrentUserNo);
strQry.AppendLine(" AND UCA1.COMPANY_ACCESS_IND = 'A'");
}
strQry.AppendLine(" WHERE UI.SYSTEM_ADMINISTRATOR_IND <> 'Y'");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" UI.USER_ID");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "User");
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" UFT.USER_NO");
strQry.AppendLine(",UFT.FINGER_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE UFT");
if (parstrAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON UFT.USER_NO = UCA.USER_NO");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA1");
strQry.AppendLine(" ON UCA.COMPANY_NO = UCA1.COMPANY_NO");
strQry.AppendLine(" AND UCA1.USER_NO = " + parintCurrentUserNo);
strQry.AppendLine(" AND UCA1.COMPANY_ACCESS_IND = 'A'");
}
strQry.AppendLine(" ORDER BY UFT.FINGER_NO");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "UserFingerTemplate");
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}