public byte[] Get_Form_Records()
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",COMPANY_DESC");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company");
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();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" DEVICE_NO");
strQry.AppendLine(",DEVICE_DESC");
strQry.AppendLine(",DEVICE_USAGE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE D");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" DEVICE_DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Device");
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(Convert.ToInt64(DataSet.Tables["Company"].Rows[0]["COMPANY_NO"]));
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_Restore_Files()
{
DataSet DataSet = new System.Data.DataSet();
DataSet.Tables.Add("RestoreFiles");
DataSet.Tables["RestoreFiles"].Columns.Add("RESTORE_DATETIME", typeof(DateTime));
DataSet.Tables["RestoreFiles"].Columns.Add("RESTORE_FILE", typeof(String));
StringBuilder strQry = new StringBuilder();
string strFileDirectory = AppDomain.CurrentDomain.BaseDirectory + "Backup";
if (Directory.Exists(strFileDirectory))
{
string strDataBaseName = "InteractPayrollClient_";
#if (DEBUG)
strDataBaseName = "InteractPayrollClient_Debug_";
#endif
string[] filePaths = Directory.GetFiles(@strFileDirectory, strDataBaseName + @"*.bak");
if (filePaths.Length > 0)
{
for (int intRow = 0; intRow < filePaths.Length; intRow++)
{
int intDateTimeOffset = filePaths[intRow].IndexOf(strDataBaseName) + strDataBaseName.Length;
if (intDateTimeOffset + 19 <= filePaths[intRow].Length)
{
DataRow drDataRow = DataSet.Tables["RestoreFiles"].NewRow();
drDataRow["RESTORE_FILE"] = filePaths[intRow];
try
{
DateTime myFileDateTime = DateTime.ParseExact(filePaths[intRow].Substring(intDateTimeOffset, 15), "yyyyMMdd_HHmmss", null);
drDataRow["RESTORE_DATETIME"] = myFileDateTime;
}
catch
{
}
DataSet.Tables["RestoreFiles"].Rows.Add(drDataRow);
}
}
}
}
DataSet.AcceptChanges();
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parint64CompanyNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
Get_Employee_DataSet(DataSet, parint64CompanyNo, -1);
Get_CurrentPayCategory_DataSet(DataSet, parint64CompanyNo, -1);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" P.PAY_CATEGORY_TYPE");
strQry.AppendLine(",P.PAY_CATEGORY_NO");
strQry.AppendLine(",P.PAY_CATEGORY_DESC");
//Used in Reply
strQry.AppendLine(",'' AS REC_RATE");
strQry.AppendLine(",'' AS DEFAULT_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY P");
strQry.AppendLine(" WHERE P.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND P.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" AND P.PAY_CATEGORY_NO > 0");
strQry.AppendLine(" AND ISNULL(P.CLOSED_IND,'N') <> 'Y'");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" P.PAY_CATEGORY_TYPE");
strQry.AppendLine(",P.PAY_CATEGORY_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategory", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PAY_CATEGORY_TYPE");
strQry.AppendLine(",LEAVE_SHIFT_NO");
strQry.AppendLine(",LEAVE_SHIFT_DESC");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" PAY_CATEGORY_TYPE");
strQry.AppendLine(",LEAVE_SHIFT_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "LeaveShift", parint64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parInt64CompanyNo, Int64 parint64CurrentUserNo, string parstrCurrentUserAccess)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.PAY_CATEGORY_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_TYPE");
strQry.AppendLine(",PC.PAY_CATEGORY_DESC");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC");
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" AND PC.COMPANY_NO = UEPCT.COMPANY_NO");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PC.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO > 0");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" PC.PAY_CATEGORY_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_TYPE");
strQry.AppendLine(",PC.PAY_CATEGORY_DESC");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" PC.PAY_CATEGORY_TYPE");
strQry.AppendLine(",PC.PAY_CATEGORY_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategory", parInt64CompanyNo);
strQry.Clear();
strQry = Get_Employee_Script(parint64CurrentUserNo, parstrCurrentUserAccess);
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parInt64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(string parstrCurrentUserAccess, Int64 parint64CurrentUserNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
if (parstrCurrentUserAccess == "S")
{
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" USER_NO");
strQry.AppendLine(",USER_ID");
strQry.AppendLine(",FIRSTNAME");
strQry.AppendLine(",SURNAME");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID");
strQry.AppendLine(" WHERE USER_NO <> " + parint64CurrentUserNo);
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL ");
}
else
{
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 ");
strQry.AppendLine(" InteractPayroll.dbo.USER_ID UI");
strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA ");
strQry.AppendLine(" WHERE UI.USER_NO = UCA.USER_NO ");
strQry.AppendLine(" AND UI.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" AND UCA.USER_NO <> " + parint64CurrentUserNo);
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" AND UI.SYSTEM_ADMINISTRATOR_IND <> 'Y'");
strQry.AppendLine(" AND STR(UCA.COMPANY_NO) IN ");
strQry.AppendLine("(SELECT STR(COMPANY_NO) ");
strQry.AppendLine(" FROM ");
strQry.AppendLine(" InteractPayroll.dbo.USER_COMPANY_ACCESS ");
strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" AND COMPANY_ACCESS_IND = 'A') ");
}
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_PayCategory_Records(Int64 parint64CompanyNo, string parstrPayrollType)
{
//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
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" EPC.PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" ON E.COMPANY_NO = PC.COMPANY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = PC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = PC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo.ToString());
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "PayCategory");
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parintUserNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" CL.COMPANY_DESC ");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_COMPANY_TO_LOAD UCTL ");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.COMPANY_LINK CL ");
strQry.AppendLine(" ON UCTL.COMPANY_NO = CL.COMPANY_NO ");
strQry.AppendLine(" WHERE UCTL.USER_NO = " + parintUserNo.ToString());
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "CompanyName", -1);
strQry.Clear();
strQry.AppendLine(" SELECT TOP 3");
strQry.AppendLine(" PAY_PERIOD_DATE ");
strQry.AppendLine(" FROM InteractPayroll_00013.dbo.PAY_CATEGORY_PERIOD_HISTORY ");
strQry.AppendLine(" WHERE COMPANY_NO = 13 ");
strQry.AppendLine(" AND PAY_PERIOD_DATE > '2017-11-01' ");
strQry.AppendLine(" AND RUN_TYPE = 'P' ");
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = 'W' ");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" PAY_PERIOD_DATE ");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" PAY_PERIOD_DATE DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayDates", -1);
DataSet.AcceptChanges();
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records()
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" SITE_NAME");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "RemoteBackupSiteName");
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parint64CompanyNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",E.EARNING_NO");
strQry.AppendLine(",E.TIE_BREAKER");
strQry.AppendLine(",E.EARNING_DESC");
strQry.AppendLine(",E.LEAVE_PERCENTAGE");
strQry.AppendLine(",E.EARNING_REPORT_HEADER1");
strQry.AppendLine(",E.EARNING_REPORT_HEADER2");
strQry.AppendLine(",E.EARNING_DEL_IND");
strQry.AppendLine(",EEC.EARNING_NO AS PAYROLL_LINK");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING E");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT EEC");
strQry.AppendLine(" ON E.COMPANY_NO = EEC.COMPANY_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EEC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND E.EARNING_NO = EEC.EARNING_NO");
strQry.AppendLine(" AND EEC.RUN_TYPE = 'P'");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" AND E.EARNING_NO > 199");
//Record has Not been Deleted
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" E.EARNING_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "LeaveType", parint64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parint64CompanyNo, string parstrMenuId)
{
DataSet DataSet = new DataSet();
StringBuilder strQry = new StringBuilder();
string strTableName = "";
if (parstrMenuId == "39")
{
strTableName = "DEPARTMENT";
}
else
{
strTableName = "OCCUPATION";
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(strTableName + "_NO ");
strQry.AppendLine("," + strTableName + "_DESC ");
strQry.AppendLine(" FROM ");
strQry.AppendLine("InteractPayroll_#CompanyNo#.dbo." + strTableName);
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(strTableName + "_DESC ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "OccupationDepartment", parint64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parint64CompanyNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
//Used In Calender Menu Option
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" PUBLIC_HOLIDAY_DATE");
strQry.AppendLine(",PUBLIC_HOLIDAY_DESC");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" PUBLIC_HOLIDAY_DATE");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PaidHoliday", parint64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Logon_Client_DataBase()
{
//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
try
{
//2017-05-03 Fix To Make Sure Databases are In Sync
clsFixInteractPayrollClientDatabase clsFixInteractPayrollClientDatabase = new clsFixInteractPayrollClientDatabase();
clsFixInteractPayrollClientDatabase.Fix_Client_Database();
}
catch
{
}
DataSet DataSet = new DataSet();
StringBuilder strQry = new StringBuilder();
try
{
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FILE_NAME");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
//2013-07-18 - Empty (This Can be Removed in 2 -3 Months Time (For Backkward Compatibility)
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'ZZ'");
//Used to Delete (Cleanup) where File is Not Downloaded from Internet Site
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "ServerFile");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FILE_LAYER_IND");
strQry.AppendLine(",FILE_NAME");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
//Empty - Used to Add Files To be Deleted from Client Database
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'Z'");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "FileToDelete");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FILE_LAYER_IND");
strQry.AppendLine(",FILE_NAME");
strQry.AppendLine(",FILE_LAST_UPDATED_DATE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
//2013-06-27
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'P'");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "ClientFile");
string strBaseDirectory = AppDomain.CurrentDomain.BaseDirectory;
#if (DEBUG)
strBaseDirectory += "bin\\";
#endif
//2013-06-27
DirectoryInfo di = new DirectoryInfo(strBaseDirectory);
FileInfo[] fiFiles = di.GetFiles("*.*");
DataRow DataRow;
foreach (FileInfo fi in fiFiles)
{
DataRow = DataSet.Tables["ClientFile"].NewRow();
DataRow["FILE_LAYER_IND"] = "S";
DataRow["FILE_NAME"] = fi.Name;
DataRow["FILE_LAST_UPDATED_DATE"] = fi.LastWriteTime;
DataSet.Tables["ClientFile"].Rows.Add(DataRow);
}
DataRow[] tempDataRows = DataSet.Tables["ClientFile"].Select("FILE_LAYER_IND ='S' AND (FILE_NAME Like '%dll_' OR FILE_NAME Like '%exe_')");
foreach (DataRow dr in tempDataRows)
{
string strName = dr["FILE_NAME"].ToString().Replace("dll_", "dll").Replace("exe_", "exe");
//Find File with Same Name Excluding '_'
DataView fiDataView = new DataView(DataSet.Tables["ClientFile"], "FILE_LAYER_IND ='S' AND FILE_NAME = '" + strName + "'", "", DataViewRowState.CurrentRows);
if (fiDataView.Count == 1)
{
fiDataView.Delete(0);
}
}
DataSet.AcceptChanges();
DataTable DataTable = new DataTable("ReturnValues");
DataTable.Columns.Add("MACHINE_NAME", typeof(String));
DataTable.Columns.Add("MACHINE_IP", typeof(String));
DataSet.Tables.Add(DataTable);
DataRow = DataSet.Tables["ReturnValues"].NewRow();
try
{
DataRow["MACHINE_NAME"] = System.Net.Dns.GetHostName();
}
catch
{
DataRow["MACHINE_NAME"] = "UNKNOWN";
}
try
{
DataRow["MACHINE_IP"] = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList[0].ToString();
}
catch
{
DataRow["MACHINE_IP"] = "UNKNOWN";
}
DataSet.Tables["ReturnValues"].Rows.Add(DataRow);
DataSet.AcceptChanges();
}
catch (Exception ex)
{
string strInnerExceptionMessage = "";
if (ex.InnerException != null)
{
strInnerExceptionMessage = ex.InnerException.Message;
}
using (StreamWriter writeLog = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "busClientPayrollLogon_Log.txt", true))
{
writeLog.WriteLine(" ********** Exception = " + ex.Message + " " + strInnerExceptionMessage);
}
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parint64CompanyNo, Int64 parint64CurrentUserNo, string parstrCurrentUserAccessInd, string parstrFromProgram)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" C.WAGE_RUN_IND");
strQry.AppendLine(",C.SALARY_RUN_IND");
strQry.AppendLine(",C.TIME_ATTENDANCE_RUN_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY C");
strQry.AppendLine(" WHERE C.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parint64CompanyNo);
byte[] byteArrayDataset = Get_User_Level_Records(parint64CompanyNo, parint64CurrentUserNo, parstrCurrentUserAccessInd, parstrFromProgram);
DataSet TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteArrayDataset);
DataSet.Merge(TempDataSet);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" LC.COMPANY_NO");
strQry.AppendLine(",LC.EMPLOYEE_NO");
strQry.AppendLine(",LC.EARNING_NO");
strQry.AppendLine(",LC.LEAVE_REC_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",LC.LEAVE_DESC");
strQry.AppendLine(",LC.PROCESS_NO");
strQry.AppendLine(",LC.LEAVE_FROM_DATE");
strQry.AppendLine(",LC.LEAVE_TO_DATE");
strQry.AppendLine(",ISNULL(LC.LEAVE_OPTION,'D') AS LEAVE_OPTION");
strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_HOURS_DECIMAL,0),2) AS LEAVE_HOURS_DECIMAL");
strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_DAYS_DECIMAL,0),2) AS LEAVE_DAYS_DECIMAL");
strQry.AppendLine(",DATEDIFF(d,LEAVE_FROM_DATE,LEAVE_TO_DATE ) + 1 AS DATE_DIFF_NO_DAYS");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC");
//2013-09-10
if (parstrCurrentUserAccessInd == "U")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT DISTINCT");
strQry.AppendLine(" EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP ");
strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo + ") AS USER_TABLE");
strQry.AppendLine(" ON LC.EMPLOYEE_NO = USER_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = USER_TABLE.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" ON LC.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND LC.EMPLOYEE_NO = E.EMPLOYEE_NO ");
strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
//Default Pay Category Parameters Apply to Leave
strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" WHERE LC.COMPANY_NO = " + parint64CompanyNo);
//Next Run
strQry.AppendLine(" AND LC.PROCESS_NO = 0");
if (parstrFromProgram == "X")
{
strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = 'T'");
}
else
{
strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE IN ('W','S')");
}
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" LEAVE_FROM_DATE");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Leave", parint64CompanyNo);
DataSet.Tables.Add("PayrollType");
DataTable PayrollTypeDataTable = new DataTable("PayrollType");
DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));
if (DataSet.Tables["AuthorsiseLevel"].Rows.Count > 0)
{
DataView PayrollTypeDataView = new DataView(DataSet.Tables["AuthorsiseLevel"],
"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["AuthorsiseLevel"],
"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["AuthorsiseLevel"],
"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 DISTINCT ");
strQry.AppendLine(" EN.COMPANY_NO");
strQry.AppendLine(",EN.EARNING_NO");
strQry.AppendLine(",EN.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EN.EARNING_DESC");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING EN ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC");
strQry.AppendLine(" ON EN.COMPANY_NO = LC.COMPANY_NO ");
strQry.AppendLine(" AND EN.EARNING_NO = LC.EARNING_NO ");
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = LC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" WHERE EN.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EN.EARNING_NO >= 200");
strQry.AppendLine(" AND EN.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" EN.EARNING_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "LeaveType", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",E.EMPLOYEE_CODE");
strQry.AppendLine(",E.EMPLOYEE_NAME");
strQry.AppendLine(",E.EMPLOYEE_SURNAME");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
//Used For Leave Totals
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",LS.LEAVE_PAID_ACCUMULATOR_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
//Default Pay Category Parameters Apply to Leave
strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL");
if (parstrCurrentUserAccessInd == "U")
{
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT ");
strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND EPC.COMPANY_NO = UEPCT.COMPANY_NO");
strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT LS");
strQry.AppendLine(" ON E.COMPANY_NO = LS.COMPANY_NO");
strQry.AppendLine(" AND E.LEAVE_SHIFT_NO = LS.LEAVE_SHIFT_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LS.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND LS.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC");
strQry.AppendLine(" ON E.COMPANY_NO = LC.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = LC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LC.PAY_CATEGORY_TYPE");
//Next Run
strQry.AppendLine(" AND LC.PROCESS_NO = 0");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo);
//Removed 2012-04-06 - Allow For Employees Clocked on Client That have Not been Activated to be seen
//strQry.AppendLine(" AND EMPLOYEE_TAKEON_IND = 'Y'");
strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" E.EMPLOYEE_CODE");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" PAY_CATEGORY_NO");
strQry.AppendLine(",DAY_NO");
strQry.AppendLine(",TIME_DECIMAL");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_TIME_DECIMAL");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" PAY_CATEGORY_NO");
strQry.AppendLine(",DAY_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategoryTimeDecimal", parint64CompanyNo);
DateTime dtDateNowAYearAgo = DateTime.Now.AddYears(-1);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" PUBLIC_HOLIDAY_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY PH ");
strQry.AppendLine(" WHERE PUBLIC_HOLIDAY_DATE > '" + dtDateNowAYearAgo.ToString("yyyy-MM-dd") + "'");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PublicHoliday", parint64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parint64CompanyNo, string parstrCurrentUserAccess, Int64 parint64CurrentUserNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT");
if (parstrCurrentUserAccess == "S"
| parstrCurrentUserAccess == "A")
{
strQry.AppendLine(" 'A' AS ACCESS_IND");
}
else
{
strQry.AppendLine(" 'U' AS ACCESS_IND");
}
strQry.AppendLine(",C.WAGE_RUN_IND");
strQry.AppendLine(",C.SALARY_RUN_IND");
strQry.AppendLine(",MAX(PCPC.PAY_PERIOD_DATE) AS WAGE_PAY_PERIOD_DATE");
strQry.AppendLine(",MAX(PCPC1.PAY_PERIOD_DATE) AS SALARY_PAY_PERIOD_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY C");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_CURRENT PCPC");
strQry.AppendLine(" ON C.COMPANY_NO = PCPC.COMPANY_NO");
strQry.AppendLine(" AND PCPC.RUN_TYPE = 'P'");
strQry.AppendLine(" AND PCPC.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_CURRENT PCPC1");
strQry.AppendLine(" ON C.COMPANY_NO = PCPC1.COMPANY_NO");
strQry.AppendLine(" AND PCPC1.RUN_TYPE = 'P'");
strQry.AppendLine(" AND PCPC1.PAY_CATEGORY_TYPE = 'S'");
strQry.AppendLine(" WHERE C.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" C.WAGE_RUN_IND");
strQry.AppendLine(",C.SALARY_RUN_IND");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" LC.COMPANY_NO");
strQry.AppendLine(",LC.EMPLOYEE_NO");
strQry.AppendLine(",LC.EARNING_NO");
strQry.AppendLine(",LC.LEAVE_REC_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",LC.LEAVE_DESC");
strQry.AppendLine(",LC.PROCESS_NO");
strQry.AppendLine(",LC.LEAVE_FROM_DATE");
strQry.AppendLine(",LC.LEAVE_TO_DATE");
//2013-09-11
strQry.AppendLine(",ISNULL(LEAVE_TEMP.AUTHORISED_IND,'N') AS AUTHORISED_IND");
strQry.AppendLine(",ISNULL(LC.LEAVE_OPTION,'D') AS LEAVE_OPTION");
strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_HOURS_DECIMAL,0),2) AS LEAVE_HOURS_DECIMAL");
strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_DAYS_DECIMAL,0),2) AS LEAVE_DAYS_DECIMAL");
strQry.AppendLine(",DATEDIFF(d,LEAVE_FROM_DATE,LEAVE_TO_DATE ) + 1 AS DATE_DIFF_NO_DAYS");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" ON LC.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND LC.EMPLOYEE_NO = E.EMPLOYEE_NO ");
strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
//2017-01-24 (Employee Not Closed)
strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
//Default Pay Category Parameters Apply to Leave
strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL");
//2013-08-29
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" AND EPC.COMPANY_NO = UEPCT.COMPANY_NO");
strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" LEFT JOIN ");
strQry.AppendLine("(SELECT DISTINCT");
strQry.AppendLine(" EPCLAC.EMPLOYEE_NO");
strQry.AppendLine(",EPCLAC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPCLAC.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EPCLAC.EARNING_NO");
strQry.AppendLine(",EPCLAC.LEAVE_REC_NO");
strQry.AppendLine(",EPCLAC.AUTHORISED_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT EPCLAC");
strQry.AppendLine(" WHERE EPCLAC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EPCLAC.AUTHORISED_IND = 'Y') AS LEAVE_TEMP ");
strQry.AppendLine(" ON LC.EMPLOYEE_NO = LEAVE_TEMP.EMPLOYEE_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = LEAVE_TEMP.PAY_CATEGORY_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LEAVE_TEMP.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND LC.EARNING_NO = LEAVE_TEMP.EARNING_NO");
strQry.AppendLine(" AND LC.LEAVE_REC_NO = LEAVE_TEMP.LEAVE_REC_NO");
strQry.AppendLine(" WHERE LC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" LEAVE_FROM_DATE");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Leave", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" PROCESS_NO");
strQry.AppendLine(",PROCESS_DESC");
strQry.AppendLine(" FROM InteractPayroll.dbo.PROCESS ");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" PROCESS_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Process", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EARNING_DESC");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EARNING_NO >= 200");
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" EARNING_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "LeaveType", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",E.EMPLOYEE_CODE");
strQry.AppendLine(",E.EMPLOYEE_NAME");
strQry.AppendLine(",E.EMPLOYEE_SURNAME");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
//Used For Leave Totals
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",LS.LEAVE_PAID_ACCUMULATOR_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
//Default Pay Category Parameters Apply to Leave
strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL");
//2013-09-11
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" AND EPC.COMPANY_NO = UEPCT.COMPANY_NO");
strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT LS");
strQry.AppendLine(" ON E.COMPANY_NO = LS.COMPANY_NO");
strQry.AppendLine(" AND E.LEAVE_SHIFT_NO = LS.LEAVE_SHIFT_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LS.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND LS.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo);
//Removed 2012-04-06 - Allow For Employees Clocked on Client That have Not been Activated to be seen
//strQry.AppendLine(" AND EMPLOYEE_TAKEON_IND = 'Y'");
strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" E.EMPLOYEE_CODE");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" PAY_CATEGORY_NO");
strQry.AppendLine(",DAY_NO");
strQry.AppendLine(",TIME_DECIMAL");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_TIME_DECIMAL");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" PAY_CATEGORY_NO");
strQry.AppendLine(",DAY_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategoryTimeDecimal", parint64CompanyNo);
DateTime dtDateNowAYearAgo = DateTime.Now.AddYears(-1);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" PUBLIC_HOLIDAY_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY PH ");
strQry.AppendLine(" WHERE PUBLIC_HOLIDAY_DATE > '" + dtDateNowAYearAgo.ToString("yyyy-MM-dd") + "'");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PublicHoliday", parint64CompanyNo);
DateTime dtStartTaxYear;
if (DateTime.Now.Month > 2)
{
dtStartTaxYear = new DateTime(DateTime.Now.Year, 3, 1);
}
else
{
dtStartTaxYear = new DateTime(DateTime.Now.Year - 1, 3, 1);
}
strQry.Clear();
//Normal Leave And Sick Leave
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" LH.PAY_CATEGORY_TYPE");
strQry.AppendLine(",LH.EMPLOYEE_NO");
strQry.AppendLine(",LH.EARNING_NO");
strQry.AppendLine(",ROUND(SUM(LH.LEAVE_ACCUM_DAYS - LH.LEAVE_PAID_DAYS),2) AS TOTAL_LEAVE_DAYS");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY LH");
//2013-09-10
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT DISTINCT");
strQry.AppendLine(" EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP ");
strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo + ") AS USER_TABLE");
strQry.AppendLine(" ON LH.EMPLOYEE_NO = USER_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND LH.PAY_CATEGORY_TYPE = USER_TABLE.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" ON LH.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND LH.EMPLOYEE_NO = E.EMPLOYEE_NO ");
strQry.AppendLine(" AND LH.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
//2017-01-24 (Employee Not Closed)
strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL");
strQry.AppendLine(" WHERE LH.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND (LH.EARNING_NO = 200");
strQry.AppendLine(" OR (LH.EARNING_NO = 201");
strQry.AppendLine(" AND LH.PAY_PERIOD_DATE >= '" + dtStartTaxYear.ToString("yyyy-MM-dd") + "'))");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" LH.PAY_CATEGORY_TYPE");
strQry.AppendLine(",LH.EMPLOYEE_NO");
strQry.AppendLine(",LH.EARNING_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeeLeaveTotals", parint64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parint64CurrentUserNo, string parstrAccessInd)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
//DISTINCT Because bug caused Duplicates
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",UCA.COMPANY_NO");
strQry.AppendLine(",U.USER_ID");
strQry.AppendLine(",U.FIRSTNAME");
strQry.AppendLine(",U.SURNAME");
strQry.AppendLine(" FROM ");
strQry.AppendLine(" InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
if (parstrAccessInd == "A")
{
strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA1");
}
strQry.AppendLine(" WHERE UCA.COMPANY_ACCESS_IND <> 'A' ");
strQry.AppendLine(" AND U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND U.USER_NO <> " + parint64CurrentUserNo);
strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
if (parstrAccessInd == "A")
{
strQry.AppendLine(" AND UCA1.USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" AND UCA1.COMPANY_NO = UCA.COMPANY_NO ");
strQry.AppendLine(" AND UCA1.DATETIME_DELETE_RECORD IS NULL ");
}
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" USER_ID");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" C.COMPANY_NO");
strQry.AppendLine(",C.COMPANY_DESC");
strQry.AppendLine(" FROM ");
strQry.AppendLine(" InteractPayroll.dbo.COMPANY_LINK C");
if (parstrAccessInd == "A")
{
strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" WHERE UCA.COMPANY_ACCESS_IND = 'A' ");
strQry.AppendLine(" AND UCA.USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" AND UCA.COMPANY_NO = C.COMPANY_NO ");
}
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" C.COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", -1);
if (DataSet.Tables["User"].Rows.Count > 0)
{
byte[] bytTempCompress = Get_Company_CostCentres_Departments_Employees(Convert.ToInt32(DataSet.Tables["User"].Rows[0]["COMPANY_NO"]));
DataSet pvtTempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
DataSet.Merge(pvtTempDataSet);
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
private void btnRunFix_Click(object sender, EventArgs e)
{
try
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
//Money Mine (Johnny)
Int64 Int64CompanyNo = 11;
object[] objParm = new object[1];
objParm[0] = Int64CompanyNo;
bool blnAllow = (Boolean)clsISUtilities.DynamicFunction("Get_Form_Records", objParm);
if (blnAllow == true)
{
DataSet = null;
DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_CODE");
strQry.AppendLine(",EMPLOYEE_NAME");
strQry.AppendLine(",EMPLOYEE_SURNAME");
strQry.AppendLine(",EMPLOYEE_LAST_RUNDATE");
strQry.AppendLine(",DEPARTMENT_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE ");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Employee");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY ");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "EmployeePayCategory");
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
objParm = new object[2];
objParm[0] = Int64CompanyNo;
objParm[1] = bytCompress;
clsISUtilities.DynamicFunction("Upload_Records", objParm);
MessageBox.Show("Upload Records Successful", "Communication", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("You are NOT allowed to Upload Records", "Communication", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception eException)
{
MessageBox.Show("Error = " + eException.Message);
clsISUtilities.ErrorHandler(eException);
}
}
public byte[] Insert_Records(string parstrParm)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
int intTimeMinutes = 0;
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT ");
strQry.AppendLine(" EC.EMPLOYEE_NO");
strQry.AppendLine(" FROM ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS EC ");
strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.EMPLOYEE E ");
strQry.AppendLine(" ON EC.EMPLOYEE_NO = E.EMPLOYEE_3RD_PARTY_CODE ");
//No Link
strQry.AppendLine(" WHERE E.EMPLOYEE_3RD_PARTY_CODE IS NULL");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "LinksMissing");
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" EMPLOYEE_NO");
strQry.AppendLine(",CLOCKING_TIME");
strQry.AppendLine(",CLOCKING_TYPE");
strQry.AppendLine(" FROM ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS ");
strQry.AppendLine(" WHERE ISNULL(USED_IND,0) = 0");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Clockings");
for (int intCount = 0; intCount < DataSet.Tables["Clockings"].Rows.Count; intCount++)
{
strQry.Clear();
intTimeMinutes = (60 * Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).Hour) + Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).Minute;
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_CLOCK_TIME");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIMESHEET_DATE");
strQry.AppendLine(",TIMESHEET_TIME_MINUTES");
strQry.AppendLine(",CLOCKED_BOUNDARY_TIME_MINUTES");
strQry.AppendLine(",IN_OUT_IND)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" 0 ");
strQry.AppendLine(",EPC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
//TIMESHEET_DATE
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd")));
//TIMESHEET_TIME_MINUTES
strQry.AppendLine("," + intTimeMinutes);
//CLOCKED_BOUNDARY_TIME_MINUTES Same as TIMESHEET_TIME_MINUTES
strQry.AppendLine("," + intTimeMinutes);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString()));
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_CLOCK_TIME DCT");
strQry.AppendLine(" ON DCT.DEVICE_NO = 0");
strQry.AppendLine(" AND DCT.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND DCT.EMPLOYEE_NO = EPC.EMPLOYEE_NO");
strQry.AppendLine(" AND DCT.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO");
strQry.AppendLine(" AND DCT.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND DCT.TIMESHEET_DATE = " + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd")));
strQry.AppendLine(" AND DCT.TIMESHEET_TIME_MINUTES = " + intTimeMinutes);
strQry.AppendLine(" AND DCT.IN_OUT_IND = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString()));
strQry.AppendLine(" WHERE E.EMPLOYEE_3RD_PARTY_CODE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["EMPLOYEE_NO"].ToString()));
//No Record Exists
strQry.AppendLine(" AND DCT.COMPANY_NO IS NULL");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" UPDATE ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS ");
strQry.AppendLine(" SET USED_IND = 1");
strQry.AppendLine(" WHERE EMPLOYEE_NO = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["EMPLOYEE_NO"].ToString()));
strQry.AppendLine(" AND CLOCKING_TIME = " + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd HH:mm:ss.fff")));
strQry.AppendLine(" AND CLOCKING_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(string parstrCurrentUserAccess, Int64 parint64CurrentUserNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
if (parstrCurrentUserAccess == "S")
{
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",U.USER_ID");
strQry.AppendLine(",U.FIRSTNAME");
strQry.AppendLine(",U.SURNAME");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" WHERE U.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" 0 AS USER_NO");
strQry.AppendLine(",'Admin' AS USER_ID");
strQry.AppendLine(",'Admin' AS FIRSTNAME");
strQry.AppendLine(",'Admin' AS SURNAME");
//Get 1 Row Only
strQry.AppendLine(" FROM InteractPayroll.dbo.COUNTRY C");
strQry.AppendLine(" WHERE C.COUNTRY_CODE = 'ZAF' ");
}
else
{
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",U.USER_ID");
strQry.AppendLine(",U.FIRSTNAME");
strQry.AppendLine(",U.SURNAME");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" WHERE U.SYSTEM_ADMINISTRATOR_IND = 'Y' ");
strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" 0 AS USER_NO");
strQry.AppendLine(",'Admin' AS USER_ID");
strQry.AppendLine(",'Admin' AS FIRSTNAME");
strQry.AppendLine(",'Admin' AS SURNAME");
//Get 1 Row Only
strQry.AppendLine(" FROM InteractPayroll.dbo.COUNTRY C");
strQry.AppendLine(" WHERE C.COUNTRY_CODE = 'ZAF' ");
}
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" 4");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parint64CompanyNo)
{
string strQry = "";
DataSet DataSet = new DataSet();
strQry = "";
strQry += " SELECT ";
strQry += " COMPANY_NO ";
strQry += ",EMPLOYEE_NO ";
strQry += ",PAY_CATEGORY_TYPE ";
strQry += ",EMPLOYEE_NAME ";
strQry += ",EMPLOYEE_SURNAME ";
strQry += ",EMPLOYEE_CODE ";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE";
strQry += " WHERE DATETIME_DELETE_RECORD IS NULL ";
strQry += " ORDER BY ";
strQry += " COMPANY_NO ";
strQry += ",EMPLOYEE_SURNAME ";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "Employee", parint64CompanyNo);
strQry = "";
strQry += " SELECT ";
strQry += " COMPANY_NO ";
strQry += ",EMPLOYEE_NO ";
strQry += ",PAY_CATEGORY_NO ";
strQry += ",PAY_CATEGORY_TYPE ";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY";
strQry += " WHERE DATETIME_DELETE_RECORD IS NULL ";
strQry += " AND DEFAULT_IND = 'Y' ";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "EmployeePayCategory", parint64CompanyNo);
strQry = "";
strQry += " SELECT ";
strQry += " COMPANY_NO ";
strQry += ",PAY_CATEGORY_NO ";
strQry += ",PAY_CATEGORY_TYPE ";
strQry += ",PAY_CATEGORY_DESC ";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY";
strQry += " WHERE DATETIME_DELETE_RECORD IS NULL ";
strQry += " AND PAY_CATEGORY_NO > 0 ";
strQry += " ORDER BY ";
strQry += " COMPANY_NO ";
strQry += ",PAY_CATEGORY_DESC ";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "PayCategory", parint64CompanyNo);
strQry = "";
strQry += " SELECT ";
strQry += " COMPANY_NO ";
strQry += ",DEPARTMENT_NO ";
strQry += ",DEPARTMENT_DESC ";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo.DEPARTMENT";
strQry += " WHERE DATETIME_DELETE_RECORD IS NULL ";
strQry += " ORDER BY ";
strQry += " COMPANY_NO ";
strQry += ",DEPARTMENT_DESC ";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "Department", parint64CompanyNo);
strQry = "";
strQry += " SELECT ";
strQry += " COMPANY_NO ";
strQry += ",OCCUPATION_NO ";
strQry += ",OCCUPATION_DESC ";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo.OCCUPATION";
strQry += " WHERE DATETIME_DELETE_RECORD IS NULL ";
strQry += " ORDER BY ";
strQry += " COMPANY_NO ";
strQry += ",OCCUPATION_DESC ";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "Occupation", parint64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Users(string parstrProgramFromInd, Int64 parCurrentUserNo, string parstrCurrentUserAccessInd)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",U.USER_ID");
strQry.AppendLine(",U.FIRSTNAME");
strQry.AppendLine(",U.SURNAME");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND <> 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
if (parstrCurrentUserAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" UCA.COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo);
strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE ");
strQry.AppendLine(" ON UCA.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
}
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",U.USER_ID");
strQry.AppendLine(",U.FIRSTNAME");
strQry.AppendLine(",U.SURNAME");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",UCA.COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND <> 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
if (parstrCurrentUserAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" UCA.COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo);
strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE ");
strQry.AppendLine(" ON UCA.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
}
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",UCA.COMPANY_NO");
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",UCA.COMPANY_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "UserCompany", -1);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" CL.COMPANY_NO");
strQry.AppendLine(",CL.COMPANY_DESC");
strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK CL");
if (parstrCurrentUserAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" UCA.COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo);
strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE ");
strQry.AppendLine(" ON CL.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
}
strQry.AppendLine(" GROUP BY");
strQry.AppendLine(" CL.COMPANY_NO");
strQry.AppendLine(",CL.COMPANY_DESC");
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" CL.COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", -1);
if (DataSet.Tables["User"].Rows.Count > 0)
{
byte[] bytTempCompress = Get_User_Menus(parstrProgramFromInd, Convert.ToInt32(DataSet.Tables["User"].Rows[0]["USER_NO"]));
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(Int64 parInt64CompanyNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
//So That Delete Will Happen
string strEmployeeNoIn = "-1";
//Insert Records
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" L.PAY_CATEGORY_TYPE");
strQry.AppendLine(",L.EMPLOYEE_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY L ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
strQry.AppendLine(" ON L.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND L.EMPLOYEE_NO = E.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL ");
//Add 180 Days (Withinn 6 Months)
strQry.AppendLine(" AND DATEADD(DD,180,L.PAY_PERIOD_DATE) >= GETDATE() ");
strQry.AppendLine(" LEFT JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND PAY_CATEGORY_TYPE IN ('W','S')");
//Take-On Balance / Take-On Balance (Previous Year)
strQry.AppendLine(" AND PROCESS_NO IN (99,100)");
//Normal Leave / Sick Leave
strQry.AppendLine(" AND EARNING_NO IN (200,201)");
strQry.AppendLine(" AND LEAVE_ACCUM_DAYS > 0) AS TEMP_LEAVE");
strQry.AppendLine(" ON L.PAY_CATEGORY_TYPE = TEMP_LEAVE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND L.EMPLOYEE_NO = TEMP_LEAVE.EMPLOYEE_NO ");
strQry.AppendLine(" WHERE L.COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE IN ('W','S')");
//Take-On Balance
strQry.AppendLine(" AND L.PROCESS_NO = 99");
//Normal Leave / Sick Leave
strQry.AppendLine(" AND L.EARNING_NO IN (200,201)");
strQry.AppendLine(" AND L.LEAVE_ACCUM_DAYS = 0");
strQry.AppendLine(" AND L.LEAVE_PAID_DAYS = 0");
//No Take-On Leave Balances
strQry.AppendLine(" AND TEMP_LEAVE.PAY_CATEGORY_TYPE IS NULL");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" L.PAY_CATEGORY_TYPE ");
strQry.AppendLine(",L.EMPLOYEE_NO ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeeTemp", parInt64CompanyNo);
for (int intRow = 0; intRow < DataSet.Tables["EmployeeTemp"].Rows.Count; intRow++)
{
if (intRow == 0)
{
strEmployeeNoIn = DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString();
}
else
{
strEmployeeNoIn += "," + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString();
}
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PREV_NORMAL_LEAVE_DAYS");
strQry.AppendLine(",NORMAL_LEAVE_DAYS");
strQry.AppendLine(",SICK_LEAVE_DAYS");
strQry.AppendLine(",LEAVE_EFFECTIVE_DATE)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EIH.COMPANY_NO");
strQry.AppendLine(",EIH.EMPLOYEE_NO");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",EIH.PAY_PERIOD_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY EIH");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON LTO ");
strQry.AppendLine(" ON EIH.COMPANY_NO = LTO.COMPANY_NO ");
strQry.AppendLine(" AND EIH.EMPLOYEE_NO = LTO.EMPLOYEE_NO ");
strQry.AppendLine(" WHERE EIH.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EIH.RUN_TYPE = 'T' ");
strQry.AppendLine(" AND EIH.EMPLOYEE_NO = " + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND EIH.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine(" AND LTO.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
//Delete any Employee That Have been on System for longer Than 180 Days
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND NOT EMPLOYEE_NO IN (" + strEmployeeNoIn + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",E.EMPLOYEE_CODE");
strQry.AppendLine(",E.EMPLOYEE_NAME");
strQry.AppendLine(",E.EMPLOYEE_SURNAME");
strQry.AppendLine(",LT.PREV_NORMAL_LEAVE_DAYS");
strQry.AppendLine(",LT.NORMAL_LEAVE_DAYS");
strQry.AppendLine(",LT.SICK_LEAVE_DAYS");
strQry.AppendLine(",LT.LEAVE_EFFECTIVE_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON LT ");
strQry.AppendLine(" ON E.COMPANY_NO = LT.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = LT.EMPLOYEE_NO ");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE IN ('W','S')");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY E.EMPLOYEE_CODE");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parInt64CompanyNo);
DataSet.Tables.Add("PayrollType");
DataTable PayrollTypeDataTable = new DataTable("PayrollType");
DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));
if (DataSet.Tables["Employee"].Rows.Count > 0)
{
DataView PayrollTypeDataView = new DataView(DataSet.Tables["Employee"],
"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["Employee"],
"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);
}
}
DataSet.AcceptChanges();
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records()
{
string strQry = "";
DataSet DataSet = new DataSet();
//Get All Level Descriptions
strQry = "";
strQry += " SELECT ";
strQry += " HF.HELP_LEVEL1_NO";
strQry += ",HF.HELP_LEVEL2_NO";
strQry += ",HF.HELP_LEVEL3_NO";
strQry += ",HF.HELP_LEVEL4_NO";
strQry += ",HL1.HELP_LEVEL1_DESC";
strQry += ",HL2.HELP_LEVEL2_DESC";
strQry += ",HL3.HELP_LEVEL3_DESC";
strQry += ",HL4.HELP_LEVEL4_DESC";
strQry += " FROM InteractPayroll.dbo.HELP_FILE HF";
strQry += " INNER JOIN InteractPayroll.dbo.HELP_LEVEL1 HL1";
strQry += " ON HF.HELP_LEVEL1_NO = HL1.HELP_LEVEL1_NO";
strQry += " LEFT JOIN InteractPayroll.dbo.HELP_LEVEL2 HL2";
strQry += " ON HF.HELP_LEVEL2_NO = HL2.HELP_LEVEL2_NO";
strQry += " LEFT JOIN InteractPayroll.dbo.HELP_LEVEL3 HL3";
strQry += " ON HF.HELP_LEVEL3_NO = HL3.HELP_LEVEL3_NO";
strQry += " LEFT JOIN InteractPayroll.dbo.HELP_LEVEL4 HL4";
strQry += " ON HF.HELP_LEVEL4_NO = HL4.HELP_LEVEL4_NO";
strQry += " ORDER BY ";
strQry += " HL1.HELP_LEVEL1_DESC";
strQry += ",HL2.HELP_LEVEL2_DESC";
strQry += ",HL3.HELP_LEVEL3_DESC";
strQry += ",HL4.HELP_LEVEL4_DESC";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "HelpLevelDesc", -1);
strQry = "";
strQry += " SELECT ";
strQry += " HELP_LEVEL1_NO";
strQry += ",HELP_LEVEL2_NO";
strQry += ",HELP_LEVEL3_NO";
strQry += ",HELP_LEVEL4_NO";
strQry += ",HELP_TEXT";
strQry += " FROM InteractPayroll.dbo.HELP_FILE_TEXT ";
strQry += " WHERE HELP_LEVEL1_NO = -1";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "HelpLevelText", -1);
//Empty Table - Used for DataView
strQry = "";
strQry += " SELECT ";
strQry += " HELP_LEVEL1_NO";
strQry += ",HELP_LEVEL2_NO";
strQry += ",HELP_LEVEL3_NO";
strQry += ",HELP_LEVEL4_NO";
strQry += ",COUNT(*) AS HELP_GRAPHIC_NUMBER_OF_CHUNKS";
strQry += " FROM InteractPayroll.dbo.HELP_FILE_GRAPHIC_CHUNK ";
strQry += " WHERE HELP_LEVEL1_NO = -1";
strQry += " GROUP BY ";
strQry += " HELP_LEVEL1_NO";
strQry += ",HELP_LEVEL2_NO";
strQry += ",HELP_LEVEL3_NO";
strQry += ",HELP_LEVEL4_NO";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "HelpLevelNumberChunks", -1);
//Empty Table - Used for DataView
strQry = "";
strQry += " SELECT ";
strQry += " HFGC.HELP_LEVEL1_NO";
strQry += ",HFGC.HELP_LEVEL2_NO";
strQry += ",HFGC.HELP_LEVEL3_NO";
strQry += ",HFGC.HELP_LEVEL4_NO";
strQry += ",HFGD.HELP_FILE_TYPE";
strQry += ",HFGD.HELP_IMAGE_SIZE";
strQry += ",HFGC.HELP_CHUNK_NO";
strQry += ",HFGC.HELP_CHUNK_IMAGE";
strQry += " FROM InteractPayroll.dbo.HELP_FILE_GRAPHIC_CHUNK HFGC ";
strQry += " INNER JOIN InteractPayroll.dbo.HELP_FILE_GRAPHIC_DETAIL HFGD ";
strQry += " ON HFGC.HELP_LEVEL1_NO = HFGD.HELP_LEVEL1_NO";
strQry += " AND HFGC.HELP_LEVEL2_NO = HFGD.HELP_LEVEL2_NO";
strQry += " AND HFGC.HELP_LEVEL3_NO = HFGD.HELP_LEVEL3_NO";
strQry += " AND HFGC.HELP_LEVEL4_NO = HFGD.HELP_LEVEL4_NO";
strQry += " WHERE HFGC.HELP_LEVEL1_NO = -1";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "HelpLevelGraphicChunk", -1);
DataTable DataTable = new DataTable("Report");
DataTable.Columns.Add("LEVEL1_DESC", typeof(String));
DataTable.Columns.Add("LEVEL1_TEXT", typeof(String));
DataTable.Columns.Add("LEVEL1_IMAGE", typeof(byte[]));
DataSet.Tables.Add(DataTable);
DataRow drDataRow = DataSet.Tables["Report"].NewRow();
drDataRow["LEVEL1_DESC"] = "Getting Started";
drDataRow["LEVEL1_TEXT"] = "Choose either Content or Search Tab.";
DataSet.Tables["Report"].Rows.Add(drDataRow);
drDataRow = DataSet.Tables["Report"].NewRow();
drDataRow["LEVEL1_DESC"] = "Content";
drDataRow["LEVEL1_TEXT"] = "Click on a Node for desired Information.";
DataSet.Tables["Report"].Rows.Add(drDataRow);
drDataRow = DataSet.Tables["Report"].NewRow();
drDataRow["LEVEL1_DESC"] = "Search";
drDataRow["LEVEL1_TEXT"] = "Enter search criteria into Search TextBox and Click Search.";
DataSet.Tables["Report"].Rows.Add(drDataRow);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet = null;
return(bytCompress);
}
public byte[] Logon_User(string parstrUserInformation)
{
//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
bool blnDebug = false;
StringBuilder strQry = new StringBuilder();
string[] strPieces = parstrUserInformation.Split('|');
string strUserId = strPieces[0];
string strPassword = strPieces[1];
byte[] bytCompress;
StreamWriter swStreamWriter;
DataSet DataSet = new DataSet();
DataTable DataTable = new DataTable("ReturnValues");
DataTable.Columns.Add("USER_NO", typeof(Int64));
DataTable.Columns.Add("RESET", typeof(String));
DataTable.Columns.Add("ACCESS_IND", typeof(String));
DataTable.Columns.Add("REBOOT_IND", typeof(String));
DataTable.Columns.Add("MACHINE_NAME", typeof(String));
DataTable.Columns.Add("MACHINE_IP", typeof(String));
DataTable.Columns.Add("NO_USERS_IND", typeof(String));
DataTable.Columns.Add("DOWNLOAD_IND", typeof(String));
DataTable.Columns.Add("DB_CREATE_FOR_ENGINE", typeof(String));
DataSet.Tables.Add(DataTable);
DataRow myDataRow = DataSet.Tables["ReturnValues"].NewRow();
myDataRow["USER_NO"] = -1;
myDataRow["RESET"] = "N";
myDataRow["ACCESS_IND"] = "";
DataSet.Tables["ReturnValues"].Rows.Add(myDataRow);
FileInfo fiFile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "Debug.txt");
if (fiFile.Exists == true)
{
blnDebug = true;
}
fiFile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "busTimeAttendanceLogonLogs.txt");
if (fiFile.Exists == true)
{
File.Delete(AppDomain.CurrentDomain.BaseDirectory + "busTimeAttendanceLogonLogs.txt");
}
if (blnDebug == true)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("Call clsDBConnectionObjects.GetSQLConnectionString");
swStreamWriter.Close();
}
DataSet.Tables["ReturnValues"].Rows[0]["DB_CREATE_FOR_ENGINE"] = clsDBConnectionObjects.GetSQLConnectionString();
if (blnDebug == true)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("After clsDBConnectionObjects.GetSQLConnectionString = " + DataSet.Tables["ReturnValues"].Rows[0]["DB_CREATE_FOR_ENGINE"].ToString());
swStreamWriter.Close();
}
if (clsDBConnectionObjects.GetSQLConnectionString() != "")
{
clsFixInteractPayrollClientDatabase clsFixInteractPayrollClientDatabase = new clsFixInteractPayrollClientDatabase();
clsFixInteractPayrollClientDatabase.Fix_Client_Database();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" UI.USER_NO");
strQry.AppendLine(",UI.SYSTEM_ADMINISTRATOR_IND");
strQry.AppendLine(",UI.RESET");
//A = Administrator, U = User
strQry.AppendLine(",MIN(UC.COMPANY_ACCESS_IND) AS MIN_COMPANY_ACCESS_IND");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID UI");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UC");
strQry.AppendLine(" ON UI.USER_NO = UC.USER_NO");
strQry.AppendLine(" WHERE UI.USER_ID = " + clsDBConnectionObjects.Text2DynamicSQL(strUserId));
strQry.AppendLine(" AND UI.PASSWORD = "******" GROUP BY ");
strQry.AppendLine(" UI.USER_NO");
strQry.AppendLine(",UI.SYSTEM_ADMINISTRATOR_IND");
strQry.AppendLine(",UI.RESET");
try
{
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
}
catch (Exception ex)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("Exception = " + ex.Message);
swStreamWriter.Close();
}
if (blnDebug == true)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("After Temp = Count = " + DataSet.Tables["Temp"].Rows.Count.ToString());
swStreamWriter.Close();
}
if (DataSet.Tables["Temp"].Rows.Count == 0)
{
DataSet.Tables.Remove(DataSet.Tables["Temp"]);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" USER_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "UserTemp");
if (DataSet.Tables["UserTemp"].Rows.Count > 0)
{
DataSet.Tables.Remove(DataSet.Tables["UserTemp"]);
if ((strUserId == "INTERACT" &&
strPassword == "TCARETNI") ||
(strUserId == "HELENALR" &&
strPassword == "MONSTER"))
{
DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = 0;
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = "S";
}
else
{
DataSet.AcceptChanges();
//Not Found
bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
}
else
{
DataSet.Tables.Remove(DataSet.Tables["UserTemp"]);
if ((strUserId == "INTERACT" &&
strPassword == "TCARETNI") ||
(strUserId == "HELENALR" &&
strPassword == "MONSTER"))
{
DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = 0;
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = "S";
}
else
{
DataSet.Tables["ReturnValues"].Rows[0]["NO_USERS_IND"] = "Y";
DataSet.AcceptChanges();
bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
}
}
else
{
if (DataSet.Tables["Temp"].Rows[0]["SYSTEM_ADMINISTRATOR_IND"].ToString() == "S")
{
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = DataSet.Tables["Temp"].Rows[0]["SYSTEM_ADMINISTRATOR_IND"].ToString();
}
else
{
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = DataSet.Tables["Temp"].Rows[0]["MIN_COMPANY_ACCESS_IND"].ToString();
}
if (DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"].ToString() != "S")
{
//Create Temp Table for Joins
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP");
strQry.AppendLine(" WHERE USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP ");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(",USER_TABLE.COMPANY_NO");
strQry.AppendLine(",USER_TABLE.EMPLOYEE_NO");
strQry.AppendLine(",USER_TABLE.PAY_CATEGORY_NO");
strQry.AppendLine(",USER_TABLE.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_PAY_CATEGORY_DEPARTMENT UPCD");
strQry.AppendLine(" ON E.COMPANY_NO = UPCD.COMPANY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UPCD.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UPCD.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND E.DEPARTMENT_NO = UPCD.DEPARTMENT_NO ");
strQry.AppendLine(" AND UPCD.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL");
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_EMPLOYEE UE");
strQry.AppendLine(" ON E.COMPANY_NO = UE.COMPANY_NO");
strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UE.EMPLOYEE_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND UE.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL");
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_PAY_CATEGORY UPC");
strQry.AppendLine(" ON E.COMPANY_NO = UPC.COMPANY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND UPC.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL ");
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON UCA.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.COMPANY_NO = UCA.COMPANY_NO");
//2013-07-10
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A'");
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL) AS USER_TABLE");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = Convert.ToInt64(DataSet.Tables["Temp"].Rows[0]["USER_NO"]);
DataSet.Tables["ReturnValues"].Rows[0]["RESET"] = DataSet.Tables["Temp"].Rows[0]["RESET"].ToString();
//Set Time Logged On
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayrollClient.dbo.USER_ID");
strQry.AppendLine(" SET LAST_TIME_ON = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "'");
strQry.AppendLine(" WHERE USER_NO = " + Convert.ToInt64(DataSet.Tables["Temp"].Rows[0]["USER_NO"]));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
//2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS_TEMP");
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FCDD.FILE_LAYER_IND");
strQry.AppendLine(",FCDD.FILE_NAME");
strQry.AppendLine(",FCDD.FILE_SIZE");
strQry.AppendLine(",FCDD.FILE_SIZE_COMPRESSED");
strQry.AppendLine(",FCDD.FILE_LAST_UPDATED_DATE");
strQry.AppendLine(",FCDD.FILE_CRC_VALUE");
strQry.AppendLine(",ISNULL(MAX(FCDC.FILE_CHUNK_NO),0) AS MAX_FILE_CHUNK_NO");
strQry.AppendLine(",'' AS DOWNLOAD_IND");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS FCDD");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS FCDC");
strQry.AppendLine(" ON FCDD.FILE_LAYER_IND = FCDC.FILE_LAYER_IND");
strQry.AppendLine(" AND FCDD.FILE_NAME = FCDC.FILE_NAME");
strQry.AppendLine(" WHERE FCDD.FILE_LAYER_IND = 'P'");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" FCDD.FILE_LAYER_IND");
strQry.AppendLine(",FCDD.FILE_NAME");
strQry.AppendLine(",FCDD.FILE_SIZE");
strQry.AppendLine(",FCDD.FILE_SIZE_COMPRESSED");
strQry.AppendLine(",FCDD.FILE_LAST_UPDATED_DATE");
strQry.AppendLine(",FCDD.FILE_CRC_VALUE");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" 2");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Files");
bytCompress = (byte[])Check_Reboot_Server();
DataSet DataSetTemp = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytCompress);
if (DataSetTemp.Tables["ReturnValues"].Rows[0]["REBOOT_IND"].ToString() == "Y")
{
DataSet.Tables["ReturnValues"].Rows[0]["REBOOT_IND"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["REBOOT_IND"].ToString();
DataSet.Tables["ReturnValues"].Rows[0]["MACHINE_NAME"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["MACHINE_NAME"].ToString();
DataSet.Tables["ReturnValues"].Rows[0]["MACHINE_IP"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["MACHINE_IP"].ToString();
}
}
bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parint64CompanyNo, string parstrCurrentUserAccess, Int64 parint64CurrentUserNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
if (parstrCurrentUserAccess == "S")
{
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",COMPANY_DESC");
strQry.AppendLine(",'A' AS ACCESS_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parint64CompanyNo);
}
else
{
//Administrator
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" C.COMPANY_NO");
strQry.AppendLine(",C.COMPANY_DESC");
strQry.AppendLine(",'A' AS ACCESS_IND");
strQry.AppendLine(" FROM ");
strQry.AppendLine(" InteractPayroll_#CompanyNo#.dbo.COMPANY C");
strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA ");
strQry.AppendLine(" WHERE C.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND C.COMPANY_NO = UCA.COMPANY_NO ");
strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" AND UCA.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" C.COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parint64CompanyNo);
}
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT ");
strQry.AppendLine(" LS.COMPANY_NO");
strQry.AppendLine(",LS.PAY_CATEGORY_TYPE");
strQry.AppendLine(",LS.LEAVE_SHIFT_NO");
strQry.AppendLine(",LS.LEAVE_SHIFT_DESC");
strQry.AppendLine(",LS.MIN_VALID_SHIFT_MINUTES");
strQry.AppendLine(",LS.MAX_SHIFTS_YEAR");
strQry.AppendLine(",LS.NORM_PAID_DAYS");
strQry.AppendLine(",LS.SICK_PAID_DAYS");
strQry.AppendLine(",LS.NORM_PAID_PER_PERIOD");
strQry.AppendLine(",LS.SICK_PAID_PER_PERIOD");
strQry.AppendLine(",LS.LEAVE_PAID_ACCUMULATOR_IND");
strQry.AppendLine(",LS.LEAVE_SHIFT_DEL_IND");
strQry.AppendLine(",LSC.LEAVE_SHIFT_NO AS PAYROLL_LINK");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT LS ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT_CURRENT LSC");
strQry.AppendLine(" ON LS.COMPANY_NO = LSC.COMPANY_NO ");
strQry.AppendLine(" AND LS.PAY_CATEGORY_TYPE = LSC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND LS.LEAVE_SHIFT_NO = LSC.LEAVE_SHIFT_NO ");
strQry.AppendLine(" WHERE LS.COMPANY_NO = " + parint64CompanyNo);
//Record has Not been Deleted
strQry.AppendLine(" AND LS.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" LS.PAY_CATEGORY_TYPE");
strQry.AppendLine(",LS.LEAVE_SHIFT_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Leave", parint64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parInt64CompanyNo, string parstrCurrentUserAccess, Int64 parint64CurrentUserNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
DataRow dtDataRow;
DataTable DataTable = new DataTable("SubAccount");
DataTable.Columns.Add("DEDUCTION_SUB_ACCOUNT_COUNT", typeof(System.Int16));
DataSet.Tables.Add(DataTable);
for (int intDay = 1; intDay < 6; intDay++)
{
dtDataRow = DataSet.Tables["SubAccount"].NewRow();
dtDataRow["DEDUCTION_SUB_ACCOUNT_COUNT"] = intDay;
DataSet.Tables["SubAccount"].Rows.Add(dtDataRow);
}
if (parstrCurrentUserAccess == "S")
{
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" 'A' AS ACCESS_IND");
strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parInt64CompanyNo);
}
else
{
//Administrator
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" 'A' AS ACCESS_IND");
strQry.AppendLine(" FROM ");
strQry.AppendLine(" InteractPayroll.dbo.COMPANY_LINK C");
strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA ");
strQry.AppendLine(" WHERE C.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND C.COMPANY_NO = UCA.COMPANY_NO ");
strQry.AppendLine(" AND UCA.USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parInt64CompanyNo);
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",E.EARNING_NO");
strQry.AppendLine(",E.EARNING_DESC");
strQry.AppendLine(",E.IRP5_CODE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING E");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.COMPANY C");
strQry.AppendLine(" ON E.COMPANY_NO = C.COMPANY_NO ");
strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parInt64CompanyNo);
//Income/Normal Time/Bonus/Paid Holiday
strQry.AppendLine(" AND (E.EARNING_NO IN (1,2,7)");
//ELR - 2018-11-10
//strQry.AppendLine(" OR (E.EARNING_NO IN (9,200,201)");
strQry.AppendLine(" OR (E.EARNING_NO IN (9)");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W')");
//Public Holiday - Company Paid
strQry.AppendLine(" OR (E.EARNING_NO = 8");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W')");
//Overtime Wages
strQry.AppendLine(" OR (E.EARNING_NO = 3");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND C.OVERTIME1_RATE <> 0)");
strQry.AppendLine(" OR (E.EARNING_NO = 4");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND C.OVERTIME2_RATE <> 0)");
strQry.AppendLine(" OR (E.EARNING_NO = 5");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND C.OVERTIME3_RATE <> 0)");
//Overtime Salaries
strQry.AppendLine(" OR (E.EARNING_NO = 3");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'S'");
strQry.AppendLine(" AND C.SALARY_OVERTIME1_RATE <> 0)");
strQry.AppendLine(" OR (E.EARNING_NO = 4");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'S'");
strQry.AppendLine(" AND C.SALARY_OVERTIME2_RATE <> 0)");
strQry.AppendLine(" OR (E.EARNING_NO = 5");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'S'");
strQry.AppendLine(" AND C.SALARY_OVERTIME3_RATE <> 0))");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL ");
//ELR - 2018-11-10 (Leave Added to Select List)
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",E.EARNING_NO");
strQry.AppendLine(",E.EARNING_DESC");
strQry.AppendLine(",E.IRP5_CODE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING E");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.COMPANY C");
strQry.AppendLine(" ON E.COMPANY_NO = C.COMPANY_NO ");
strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parInt64CompanyNo);
//Leave (Only for Wages)
strQry.AppendLine(" AND E.EARNING_NO >= 200");
strQry.AppendLine(" AND E.LEAVE_PERCENTAGE > 0");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL ");
//Own Created Earnings
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EE.COMPANY_NO");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EE.EARNING_NO");
strQry.AppendLine(",E.EARNING_DESC");
strQry.AppendLine(",E.IRP5_CODE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EARNING E");
strQry.AppendLine(" ON EE.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EE.EARNING_NO = E.EARNING_NO ");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" WHERE EE.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EE.EMPLOYEE_NO = 0 ");
strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",E.EARNING_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EarningList", parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = 0 ");
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" PAY_CATEGORY_TYPE");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EarningPercentage", parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" D.COMPANY_NO");
strQry.AppendLine(",D.PAY_CATEGORY_TYPE");
strQry.AppendLine(",ED.EMPLOYEE_NO");
strQry.AppendLine(",D.DEDUCTION_NO");
strQry.AppendLine(",D.DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",D.DEDUCTION_DESC");
strQry.AppendLine(",D.IRP5_CODE");
strQry.AppendLine(",D.DEDUCTION_SUB_ACCOUNT_COUNT");
strQry.AppendLine(",D.DEDUCTION_REPORT_HEADER1");
strQry.AppendLine(",D.DEDUCTION_REPORT_HEADER2");
strQry.AppendLine(",D.DEDUCTION_LOAN_TYPE_IND");
strQry.AppendLine(",D.DEDUCTION_DEL_IND");
strQry.AppendLine(",ED.DEDUCTION_TYPE_IND");
strQry.AppendLine(",ED.DEDUCTION_VALUE");
strQry.AppendLine(",ED.DEDUCTION_MIN_VALUE");
strQry.AppendLine(",ED.DEDUCTION_MAX_VALUE");
strQry.AppendLine(",ED.DEDUCTION_PERIOD_IND");
strQry.AppendLine(",ED.DEDUCTION_DAY_VALUE");
strQry.AppendLine(",ED.TIE_BREAKER");
strQry.AppendLine(",EDC.DEDUCTION_NO AS PAYROLL_LINK");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.DEDUCTION D");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED");
strQry.AppendLine(" ON D.COMPANY_NO = ED.COMPANY_NO ");
strQry.AppendLine(" AND D.PAY_CATEGORY_TYPE = ED.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND D.DEDUCTION_NO = ED.DEDUCTION_NO ");
strQry.AppendLine(" AND D.DEDUCTION_SUB_ACCOUNT_NO = ED.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND ED.EMPLOYEE_NO = 0 ");
strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT EDC");
strQry.AppendLine(" ON D.COMPANY_NO = EDC.COMPANY_NO ");
strQry.AppendLine(" AND D.PAY_CATEGORY_TYPE = EDC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND D.DEDUCTION_NO = EDC.DEDUCTION_NO ");
strQry.AppendLine(" AND EDC.RUN_TYPE = 'P'");
strQry.AppendLine(" WHERE D.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND D.DATETIME_DELETE_RECORD IS NULL ");
//Only Get First One (All Others are a Duplicate of This One)
strQry.AppendLine(" AND D.DEDUCTION_SUB_ACCOUNT_NO = 1");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" D.DEDUCTION_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Deduction", parInt64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parint64CompanyNo, string parstrMenuId, Int64 parint64CurrentUserNo, string parstrCurrentUserAccess)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" PC.COMPANY_NO ");
strQry.AppendLine(",PC.PAY_CATEGORY_DESC");
strQry.AppendLine(",PC.PAY_CATEGORY_NO");
strQry.AppendLine(",PCPH.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_HISTORY PCPH");
//2013-08-30
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND PCPH.COMPANY_NO = UEPCT.COMPANY_NO");
strQry.AppendLine(" AND PCPH.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO");
strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC");
strQry.AppendLine(" ON PCPH.COMPANY_NO = PC.COMPANY_NO");
strQry.AppendLine(" AND PCPH.PAY_CATEGORY_NO = PC.PAY_CATEGORY_NO");
strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = PC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" WHERE PCPH.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND PCPH.RUN_TYPE = 'P'");
strQry.AppendLine(" AND PC.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" PC.COMPANY_NO ");
strQry.AppendLine(",PC.PAY_CATEGORY_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategory", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PCPH.COMPANY_NO ");
strQry.AppendLine(",PCPH.PAY_CATEGORY_TYPE");
strQry.AppendLine(",PCPH.PAY_PERIOD_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_HISTORY PCPH");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY EIH");
strQry.AppendLine(" ON PCPH.COMPANY_NO = EIH.COMPANY_NO");
strQry.AppendLine(" AND PCPH.PAY_PERIOD_DATE = EIH.PAY_PERIOD_DATE");
strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = EIH.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND PCPH.RUN_TYPE = EIH.RUN_TYPE");
strQry.AppendLine(" AND EIH.LEAVE_SHIFT_NO > 0");
//2013-08-30
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND PCPH.COMPANY_NO = UEPCT.COMPANY_NO");
strQry.AppendLine(" AND EIH.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO");
strQry.AppendLine(" AND PCPH.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO");
strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" WHERE PCPH.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND PCPH.RUN_TYPE = 'P'");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" PCPH.COMPANY_NO ");
strQry.AppendLine(",PCPH.PAY_CATEGORY_TYPE");
strQry.AppendLine(",PCPH.PAY_PERIOD_DATE");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" PCPH.COMPANY_NO ");
strQry.AppendLine(",PCPH.PAY_CATEGORY_TYPE");
strQry.AppendLine(",PCPH.PAY_PERIOD_DATE DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Date", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",E.EMPLOYEE_CODE");
strQry.AppendLine(",E.EMPLOYEE_SURNAME");
strQry.AppendLine(",E.EMPLOYEE_NAME");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
//2013-08-30
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND E.COMPANY_NO = UEPCT.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO");
//2017-01-28 (Allow Employee to Change PAY_CATEGORY_TYPE)
//strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo);
//2017-01-28 (Allow Employee to Change PAY_CATEGORY_TYPE)
//strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" E.EMPLOYEE_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EPCH.COMPANY_NO");
strQry.AppendLine(",EPCH.PAY_PERIOD_DATE");
strQry.AppendLine(",EPCH.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EPCH.EMPLOYEE_NO");
strQry.AppendLine(",EPCH.PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_HISTORY EPCH");
//2013-08-30
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND EPCH.COMPANY_NO = UEPCT.COMPANY_NO");
strQry.AppendLine(" AND EPCH.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO");
strQry.AppendLine(" AND EPCH.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO");
strQry.AppendLine(" AND EPCH.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" WHERE EPCH.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EPCH.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" GROUP BY");
strQry.AppendLine(" EPCH.COMPANY_NO");
strQry.AppendLine(",EPCH.PAY_PERIOD_DATE");
strQry.AppendLine(",EPCH.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EPCH.EMPLOYEE_NO");
strQry.AppendLine(",EPCH.PAY_CATEGORY_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeePayCategory", parint64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(Int64 parint64CompanyNo, string parstrFromProgram)
{
DataSet DataSet = new DataSet();
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE ");
strQry.AppendLine(",PAY_CATEGORY_SHIFT_SCHEDULE_NO ");
strQry.AppendLine(",FROM_DATETIME ");
strQry.AppendLine(",TO_DATETIME ");
strQry.AppendLine(",ISNULL(LOCKED_IND,0) AS LOCKED_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_SHIFT_SCHEDULE");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
if (parstrFromProgram == "X")
{
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = 'T'");
}
else
{
strQry.AppendLine(" AND PAY_CATEGORY_TYPE IN ('W','S')");
}
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE ");
strQry.AppendLine(",FROM_DATETIME DESC");
strQry.AppendLine(",TO_DATETIME DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "ShiftSchedule", parint64CompanyNo);
DataSet.Tables.Add("PayrollType");
DataTable PayrollTypeDataTable = new DataTable("PayrollType");
DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));
if (parstrFromProgram == "X")
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Time Attendance";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
else
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Wages";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Salaries";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
DataSet.AcceptChanges();
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();
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 ");
}
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" C.COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" D.DEVICE_NO");
strQry.AppendLine(",D.DEVICE_DESC");
strQry.AppendLine(",D.DEVICE_USAGE");
strQry.AppendLine(",D.TIME_ATTEND_CLOCK_FIRST_LAST_IND");
strQry.AppendLine(",D.CLOCK_IN_OUT_PARM");
strQry.AppendLine(",D.CLOCK_IN_RANGE_FROM");
strQry.AppendLine(",D.CLOCK_IN_RANGE_TO");
strQry.AppendLine(",D.LOCK_OUT_MINUTES");
strQry.AppendLine(",D.COMPANY_NO");
strQry.AppendLine(",ISNULL(D.LAN_WAN_IND,'L') AS LAN_WAN_IND");
strQry.AppendLine(",FAR_REQUESTED");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE D");
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 = D.COMPANY_NO ");
}
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" D.DEVICE_DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Clock");
for (int intRow = 0; intRow < DataSet.Tables["Clock"].Rows.Count; intRow++)
{
if (DataSet.Tables["Clock"].Rows[intRow]["FAR_REQUESTED"] == System.DBNull.Value)
{
// 1/10 000
DataSet.Tables["Clock"].Rows[intRow]["FAR_REQUESTED"] = 214748;
strQry.Clear();
strQry.AppendLine(" UPDATE DEVICE");
strQry.AppendLine(" SET FAR_REQUESTED = 214748");
strQry.AppendLine(" WHERE DEVICE_NO = " + DataSet.Tables["Clock"].Rows[intRow]["DEVICE_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(string parstrCurrentUserAccess, Int64 parint64CurrentUserNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",U.USER_ID");
strQry.AppendLine(",U.FIRSTNAME");
strQry.AppendLine(",U.SURNAME");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_DETAILS FUD ");
strQry.AppendLine(" ON U.USER_NO = FUD.USER_NO ");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_DETAILS_FOR_USERS FUDFU ");
strQry.AppendLine(" ON FUD.USER_NO = FUDFU.USER_NO");
strQry.AppendLine(" AND FUD.FILE_NAME = FUDFU.FILE_NAME");
strQry.AppendLine(" AND FUD.UPLOAD_DATETIME = FUDFU.UPLOAD_DATETIME");
strQry.AppendLine(" WHERE U.DATETIME_DELETE_RECORD IS NULL");
if (parstrCurrentUserAccess != "S")
{
strQry.AppendLine(" AND FUDFU.FOR_USER_NO = " + parint64CurrentUserNo);
}
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" 0 AS USER_NO");
strQry.AppendLine(",'Admin' AS USER_ID");
strQry.AppendLine(",'Admin' AS FIRSTNAME");
strQry.AppendLine(",'Admin' AS SURNAME");
//Get 1 Row Only
strQry.AppendLine(" FROM InteractPayroll.dbo.COUNTRY C");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_DETAILS FUD ");
strQry.AppendLine(" ON FUD.USER_NO = 0");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_DETAILS_FOR_USERS FUDFU ");
strQry.AppendLine(" ON FUD.USER_NO = FUDFU.USER_NO");
strQry.AppendLine(" AND FUD.FILE_NAME = FUDFU.FILE_NAME");
strQry.AppendLine(" AND FUD.UPLOAD_DATETIME = FUDFU.UPLOAD_DATETIME");
strQry.AppendLine(" WHERE C.COUNTRY_CODE = 'ZAF' ");
if (parstrCurrentUserAccess != "S")
{
strQry.AppendLine(" AND FUDFU.FOR_USER_NO = " + parint64CurrentUserNo);
}
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" 4");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FUD.USER_NO");
strQry.AppendLine(",FUD.FILE_NAME");
strQry.AppendLine(",FUD.UPLOAD_DATETIME");
strQry.AppendLine(",FUD.FILE_LAST_UPDATED_DATE");
strQry.AppendLine(",FUD.FILE_SIZE");
strQry.AppendLine(",FUD.FILE_SIZE_COMPRESSED");
strQry.AppendLine(",FUD.FILE_VERSION_NO");
strQry.AppendLine(",FUD.FILE_CRC_VALUE");
strQry.AppendLine(",ISNULL(U.USER_ID,'Admin') AS USER_ID");
strQry.AppendLine(",ISNULL(U.FIRSTNAME,'Admin') AS FIRSTNAME");
strQry.AppendLine(",ISNULL(U.SURNAME,'Admin') AS SURNAME");
strQry.AppendLine(",MAX(FUC.FILE_CHUNK_NO) AS MAX_FILE_CHUNK_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.FILE_UPLOAD_DETAILS FUD ");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_DETAILS_FOR_USERS FUDFU ");
strQry.AppendLine(" ON FUD.USER_NO = FUDFU.USER_NO");
strQry.AppendLine(" AND FUD.FILE_NAME = FUDFU.FILE_NAME");
strQry.AppendLine(" AND FUD.UPLOAD_DATETIME = FUDFU.UPLOAD_DATETIME");
strQry.AppendLine(" LEFT JOIN InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" ON FUDFU.FOR_USER_NO = U.USER_NO ");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_CHUNKS FUC ");
strQry.AppendLine(" ON FUD.USER_NO = FUC.USER_NO");
strQry.AppendLine(" AND FUD.FILE_NAME = FUC.FILE_NAME");
strQry.AppendLine(" AND FUD.UPLOAD_DATETIME = FUC.UPLOAD_DATETIME");
if (parstrCurrentUserAccess != "S")
{
strQry.AppendLine(" WHERE FUDFU.FOR_USER_NO = " + parint64CurrentUserNo);
}
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" FUD.USER_NO");
strQry.AppendLine(",FUD.FILE_NAME");
strQry.AppendLine(",FUD.UPLOAD_DATETIME");
strQry.AppendLine(",FUD.FILE_LAST_UPDATED_DATE");
strQry.AppendLine(",FUD.FILE_SIZE");
strQry.AppendLine(",FUD.FILE_SIZE_COMPRESSED");
strQry.AppendLine(",FUD.FILE_VERSION_NO");
strQry.AppendLine(",FUD.FILE_CRC_VALUE");
strQry.AppendLine(",U.USER_ID");
strQry.AppendLine(",U.FIRSTNAME");
strQry.AppendLine(",U.SURNAME");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" FUD.USER_NO");
strQry.AppendLine(",FUD.UPLOAD_DATETIME DESC");
strQry.AppendLine(",FUD.FILE_NAME");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "UserFile", -1);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
