public bool Get_Form_Records(Int64 parInt64CompanyNo)
{
bool blnCanUpload = false;
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" ALLOW_UPLOAD_OF_EMPLOYEES");
strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", parInt64CompanyNo);
if (DataSet.Tables["Temp"].Rows.Count > 0)
{
if (DataSet.Tables["Temp"].Rows[0]["ALLOW_UPLOAD_OF_EMPLOYEES"] == System.DBNull.Value)
{
}
else
{
if (Convert.ToBoolean(DataSet.Tables["Temp"].Rows[0]["ALLOW_UPLOAD_OF_EMPLOYEES"]) == true)
{
blnCanUpload = true;
}
}
}
return(blnCanUpload);
}
public string Check_If_Busy_With_Run(Int64 parInt64CompanyNo, string parstrFromProgram)
{
string strPayrollRun = "N";
DataSet DataSet = new DataSet();
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_CURRENT");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND RUN_TYPE = 'P'");
if (parstrFromProgram == "X")
{
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = 'T'");
}
else
{
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = 'W'");
}
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", parInt64CompanyNo);
if (DataSet.Tables["Temp"].Rows.Count > 0)
{
strPayrollRun = "Y";
}
return(strPayrollRun);
}
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);
}
private void frmCreateDBFromBackups_Load(object sender, EventArgs e)
{
try
{
clsDBConnectionObjects = new InteractPayroll.clsDBConnectionObjects();
clsISUtilities = new clsISUtilities();
iAmazonS3 = new AmazonS3Client();
this.lblFilesHeader.Paint += new System.Windows.Forms.PaintEventHandler(clsISUtilities.Label_Paint);
pvtDataSet = new System.Data.DataSet();
strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" BACKUP_DATABASE_PATH");
strQry.AppendLine(",BACKUP_GMAIL_ACCOUNT");
strQry.AppendLine(",BACKUP_GMAIL_PASSWORD");
strQry.AppendLine(",BACKUP_S3_BUCKET_NAME");
strQry.AppendLine(" FROM InteractPayroll.dbo.BACKUP_DATABASE_PATH");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), pvtDataSet, "Directory", -1);
pvtstrFileDirectory = pvtDataSet.Tables["Directory"].Rows[0]["BACKUP_DATABASE_PATH"].ToString();
pvtstrEmail = pvtDataSet.Tables["Directory"].Rows[0]["BACKUP_GMAIL_ACCOUNT"].ToString();
pvtstrEmailPassword = pvtDataSet.Tables["Directory"].Rows[0]["BACKUP_GMAIL_PASSWORD"].ToString();
pvtstrBucketName = pvtDataSet.Tables["Directory"].Rows[0]["BACKUP_S3_BUCKET_NAME"].ToString();
strQry.Clear();
//Create Table For Compare to Remote Files (via Dataview)
strQry.AppendLine(" SELECT");
strQry.AppendLine(" BACKUP_DATABASE_PATH AS LOCAL_FILE");
strQry.AppendLine(" FROM InteractPayroll.dbo.BACKUP_DATABASE_PATH");
strQry.AppendLine(" WHERE BACKUP_DATABASE_PATH = 'ZZZZ'");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), pvtDataSet, "LocalBackupFiles", -1);
Get_Local_Backup_Files();
}
catch (Exception ex)
{
}
}
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(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(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_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 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(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[] Get_Form_Records(Int64 parint64CompanyNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
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(" 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(" AND (LT.PREV_NORMAL_LEAVE_DAYS <> 0 ");
strQry.AppendLine(" OR LT.NORMAL_LEAVE_DAYS <> 0 ");
strQry.AppendLine(" OR LT.SICK_LEAVE_DAYS <> 0) ");
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.EMPLOYEE_TAKEON_IND = 'Y' ");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
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 void RunBackupJob()
{
bool blnBackupCompletedSuccessful = false;
string strEmail = "";
string strEmailPassword = "";
try
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(pvtstrLogFileDirectory + "DailyDBBackup_Info.txt", true))
{
file.WriteLine("RunBackupJob ... " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
clsDBConnectionObjects = null;
clsDBConnectionObjects = new InteractPayroll.clsDBConnectionObjects();
DataSet DataSet = new System.Data.DataSet();
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" BACKUP_DATABASE_PATH");
strQry.AppendLine(",BACKUP_GMAIL_ACCOUNT");
strQry.AppendLine(",BACKUP_GMAIL_PASSWORD");
strQry.AppendLine(",BACKUP_S3_BUCKET_NAME");
strQry.AppendLine(",BACKUP_DB_IND");
strQry.AppendLine(" FROM InteractPayroll.dbo.BACKUP_DATABASE_PATH");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Directory", -1);
string strFileDirectory = DataSet.Tables["Directory"].Rows[0]["BACKUP_DATABASE_PATH"].ToString();
strEmail = DataSet.Tables["Directory"].Rows[0]["BACKUP_GMAIL_ACCOUNT"].ToString();
strEmailPassword = DataSet.Tables["Directory"].Rows[0]["BACKUP_GMAIL_PASSWORD"].ToString();
pvtstrBucketName = DataSet.Tables["Directory"].Rows[0]["BACKUP_S3_BUCKET_NAME"].ToString();
string strDatabaseName = "InteractPayroll";
string strBackupFileName = strDatabaseName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + "_Daily.bak";
//Move Files Offshore
client = new AmazonS3Client();
string[] fileInteractPayrollPaths;
FileInfo fileinfo;
bool blnFound = false;
if (DataSet.Tables["Directory"].Rows[0]["BACKUP_DB_IND"].ToString() == "Y" ||
DateTime.Now.DayOfWeek == DayOfWeek.Saturday)
{
//Backup InteractPayroll Master
fileInteractPayrollPaths = Directory.GetFiles(@strFileDirectory, @"*InteractPayroll_" + DateTime.Now.ToString("yyyyMMdd") + "_*.bak");
if (fileInteractPayrollPaths.Length == 0)
{
strQry.Clear();
strQry.AppendLine("BACKUP DATABASE " + strDatabaseName + " TO DISK = '" + strFileDirectory + "\\" + strBackupFileName + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.BACKUP_DATABASE_PATH");
strQry.AppendLine(" SET BACKUP_DB_IND = 'N'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
fileInteractPayrollPaths = Directory.GetFiles(@strFileDirectory, @"*InteractPayroll_" + DateTime.Now.ToString("yyyyMMdd") + "_*.bak");
if (fileInteractPayrollPaths.Length > 0)
{
fileinfo = new FileInfo(fileInteractPayrollPaths[0]);
blnFound = FindFile(fileinfo.Name);
if (blnFound == false)
{
#if (DEBUG)
//Don't Want To Write Test DBs to Backup Bucket
#else
TransferUtilityUploadRequest fileTransferUtilityRequest = new TransferUtilityUploadRequest
{
BucketName = pvtstrBucketName,
FilePath = fileInteractPayrollPaths[0]
};
fileTransferUtilityRequest.Metadata.Add("fileName", fileinfo.Name);
fileTransferUtilityRequest.Metadata.Add("fileDesc", fileinfo.Name);
TransferUtility fileTransferUtility = new TransferUtility(client);
fileTransferUtility.Upload(fileTransferUtilityRequest);
#endif
}
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO ");
strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" WHERE BACKUP_DB_IND = 1");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "BackUpInfo", -1);
//First Complete Todays Backups
for (int intRow = 0; intRow < DataSet.Tables["BackUpInfo"].Rows.Count; intRow++)
{
strDatabaseName = "InteractPayroll_" + Convert.ToInt32(DataSet.Tables["BackUpInfo"].Rows[intRow]["COMPANY_NO"]).ToString("00000");
strBackupFileName = strDatabaseName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + "_Daily.bak";
strQry.Clear();
strQry.AppendLine("BACKUP DATABASE " + strDatabaseName + " TO DISK = '" + strFileDirectory + "\\" + strBackupFileName + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" SET BACKUP_DB_IND = 0");
strQry.AppendLine(" WHERE COMPANY_NO = " + DataSet.Tables["BackUpInfo"].Rows[intRow]["COMPANY_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
string[] fileOldDBPaths = Directory.GetFiles(strFileDirectory, strDatabaseName + "_*_Daily.bak");
if (fileOldDBPaths.Length > 5)
{
//Delete Old
Array.Sort(fileOldDBPaths);
for (int intFileRow = 0; intFileRow < fileOldDBPaths.Length - 5; intFileRow++)
{
File.Delete(fileOldDBPaths[intFileRow]);
}
}
}
//Complete Todays Offsite DB Backups
string[] fileTodaysPaths = Directory.GetFiles(@strFileDirectory, @"*_" + DateTime.Now.ToString("yyyyMMdd") + "*.bak");
if (fileTodaysPaths.Length > 0)
{
for (int intFileCount = 0; intFileCount < fileTodaysPaths.Length; intFileCount++)
{
fileinfo = new FileInfo(fileTodaysPaths[intFileCount]);
blnFound = FindFile(fileinfo.Name);
if (blnFound == false)
{
#if (DEBUG)
//Don't Want To Write Test DBs to Backup Bucket
#else
TransferUtilityUploadRequest fileTransferUtilityRequest = new TransferUtilityUploadRequest
{
BucketName = pvtstrBucketName,
FilePath = fileTodaysPaths[intFileCount]
};
//LifecycleConfiguration a = new LifecycleConfiguration();
fileTransferUtilityRequest.Metadata.Add("fileName", fileinfo.Name);
fileTransferUtilityRequest.Metadata.Add("fileDesc", fileinfo.Name);
/////fileTransferUtilityRequest.Metadata.Add("expires","Tue, 16 Feb 2016 16:00:00 GMT");
TransferUtility fileTransferUtility = new TransferUtility(client);
fileTransferUtility.Upload(fileTransferUtilityRequest);
#endif
}
}
}
//Check Yesterdays DB Backups
string[] fileYesterdaysPaths = Directory.GetFiles(@strFileDirectory, @"*_" + DateTime.Now.AddDays(-1).ToString("yyyyMMdd") + "*.bak");
if (fileYesterdaysPaths.Length > 0)
{
for (int intFileCount = 0; intFileCount < fileYesterdaysPaths.Length; intFileCount++)
{
fileinfo = new FileInfo(fileYesterdaysPaths[intFileCount]);
blnFound = FindFile(fileinfo.Name);
if (blnFound == false)
{
#if (DEBUG)
//Don't Want To Write Test DBs to Backup Bucket
#else
TransferUtilityUploadRequest fileTransferUtilityRequest = new TransferUtilityUploadRequest
{
BucketName = pvtstrBucketName,
FilePath = fileYesterdaysPaths[intFileCount]
};
fileTransferUtilityRequest.Metadata.Add("fileName", fileinfo.Name);
fileTransferUtilityRequest.Metadata.Add("fileDesc", fileinfo.Name);
TransferUtility fileTransferUtility = new TransferUtility(client);
fileTransferUtility.Upload(fileTransferUtilityRequest);
#endif
}
}
}
bool blnFirstPass = true;
string[] fileOldDaysPaths;
RunBackupJob_Continue:
if (blnFirstPass == true)
{
//Clean Up Old Backups Older than 1 Month Old
fileOldDaysPaths = Directory.GetFiles(@strFileDirectory, @"*_" + DateTime.Now.AddMonths(-1).ToString("yyyyMM") + "*.bak");
}
else
{
fileOldDaysPaths = Directory.GetFiles(@strFileDirectory, @"*_" + DateTime.Now.AddMonths(-2).ToString("yyyyMM") + "*.bak");
}
foreach (string file in fileOldDaysPaths)
{
int intOffset = file.IndexOf("InteractPayroll_");
if (intOffset > -1)
{
if (file.Length > intOffset + 30)
{
try
{
//Client Databases
DateTime myFileDateTime = DateTime.ParseExact(file.Substring(intOffset + 22, 8), "yyyyMMdd", null).AddMonths(1);
if (myFileDateTime < DateTime.Now)
{
File.Delete(file);
}
}
catch
{
//InteractPayroll Master
try
{
DateTime myFileDateTime = DateTime.ParseExact(file.Substring(intOffset + 16, 8), "yyyyMMdd", null).AddMonths(1);
if (myFileDateTime < DateTime.Now)
{
File.Delete(file);
}
}
catch
{
}
}
}
}
}
if (blnFirstPass == true)
{
blnFirstPass = false;
goto RunBackupJob_Continue;
}
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.BACKUP_DATABASE_PATH");
strQry.AppendLine(" SET BACKUP_LAST_DATETIME = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
blnBackupCompletedSuccessful = true;
}
catch (Exception ex)
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(pvtstrLogFileDirectory + "DailyDBBackup_Error.txt", true))
{
file.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " Error = " + ex.Message);
}
}
finally
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(pvtstrLogFileDirectory + "DailyDBBackup_Info.txt", true))
{
file.WriteLine("RunBackupJob Setting Timer ... " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
//Set Next Time for Timer to Fire
double tickTime = Calculate_Next_Time_Timer_Fires(blnBackupCompletedSuccessful);
tmrTimer.Interval = tickTime;
tmrTimer.Start();
if (client != null)
{
client.Dispose();
client = null;
}
if (blnBackupCompletedSuccessful == true ||
DateTime.Now.Hour == 7)
{
var smtp = new SmtpClient();
try
{
//Email
var fromAddress = new MailAddress(strEmail, "DB Backup Server");
var toAddress = new MailAddress(strEmail, "Errol Le Roux");
string subject = "Daily DB Backups - " + DateTime.Now.ToString("dd MMMM yyyy");
string body = "Backup Successful";
if (blnBackupCompletedSuccessful == false)
{
body = "Backup UNSUCCESSFUL";
}
smtp.Host = "smtp.gmail.com";
smtp.Port = 587;
smtp.EnableSsl = true;
smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
smtp.UseDefaultCredentials = false;
smtp.Credentials = new NetworkCredential(fromAddress.Address, strEmailPassword);
var message = new MailMessage(fromAddress, toAddress);
message.Subject = subject;
message.Body = body;
smtp.Send(message);
using (System.IO.StreamWriter file = new System.IO.StreamWriter(pvtstrLogFileDirectory + "DailyDBBackup_Info.txt", true))
{
file.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " Email Sent Successfully");
}
}
catch (Exception ex)
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(pvtstrLogFileDirectory + "DailyDBBackup_Error.txt", true))
{
file.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " Email Error = " + ex.Message);
}
}
finally
{
smtp.Dispose();
smtp = null;
}
}
}
}
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 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);
}
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 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)
{
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 int Check_Run(Int64 parint64CompanyNo, string parstrToPayCategoryType, byte[] parbytCompressDataSet)
{
int intReturnCode = 0;
DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbytCompressDataSet);
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND ISNULL(ALLOW_COST_CENTRE_CONVERT_IND,0) = 1");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "AllowConvertCheck", parint64CompanyNo);
if (DataSet.Tables["AllowConvertCheck"].Rows.Count == 0)
{
intReturnCode = 9;
}
else
{
//Check That there is No Current Payroll Run on the Go
for (int intRow = 0; intRow < DataSet.Tables["PayCategory"].Rows.Count; intRow++)
{
if (DataSet.Tables["RunCheck"] != null)
{
DataSet.Tables.Remove("RunCheck");
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_CURRENT ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND RUN_TYPE = 'P' ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "RunCheck", parint64CompanyNo);
if (DataSet.Tables["RunCheck"].Rows.Count > 0)
{
intReturnCode = 1;
break;
}
}
}
DataSet.Dispose();
DataSet = null;
return intReturnCode;
}
public byte[] Get_Form_Records(Int64 parInt64CompanyNo)
{
DataSet DataSet = new DataSet();
DataSet TempDataSet;
string strEmployeeNoIn = "-1";
StringBuilder strFieldNamesInitialised = new StringBuilder();
StringBuilder strQry = new StringBuilder();
//Insert Records
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EDH.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EDH.EMPLOYEE_NO");
strQry.AppendLine(",EDH.PAY_PERIOD_DATE");
strQry.AppendLine(",SUM(EDH.TOTAL)");
strQry.AppendLine(",SUM(EEH.TOTAL)");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_HISTORY EDH ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_HISTORY EEH ");
strQry.AppendLine(" ON EDH.COMPANY_NO = EEH.COMPANY_NO ");
strQry.AppendLine(" AND EDH.PAY_PERIOD_DATE = EEH.PAY_PERIOD_DATE ");
strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE = EEH.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EDH.EMPLOYEE_NO = EEH.EMPLOYEE_NO ");
strQry.AppendLine(" AND EDH.RUN_TYPE = EEH.RUN_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
strQry.AppendLine(" ON EDH.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE ");
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE IN ('W','S') ");
strQry.AppendLine(" AND EDH.EMPLOYEE_NO = E.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",MAX(PAY_PERIOD_DATE) AS MAX_PAY_PERIOD_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND PAY_CATEGORY_TYPE IN ('W','S') ");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO) AS TEMP_EMPLOYEE_DATE ");
strQry.AppendLine(" ON EDH.PAY_CATEGORY_TYPE = TEMP_EMPLOYEE_DATE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EDH.EMPLOYEE_NO = TEMP_EMPLOYEE_DATE.EMPLOYEE_NO ");
//Add 180 Days (Withinn 6 Months)
strQry.AppendLine(" AND DATEADD(DD,180,EDH.PAY_PERIOD_DATE) >= MAX_PAY_PERIOD_DATE ");
strQry.AppendLine(" WHERE EDH.COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE IN ('W','S') ");
strQry.AppendLine(" AND EDH.RUN_TYPE = 'T'");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" EDH.PAY_CATEGORY_TYPE ");
strQry.AppendLine(",EDH.EMPLOYEE_NO ");
strQry.AppendLine(",EDH.PAY_PERIOD_DATE");
//Exclude where Already Have Take-On Values
strQry.AppendLine(" HAVING SUM(EDH.TOTAL) = 0");
strQry.AppendLine(" AND SUM(EEH.TOTAL) = 0 ");
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.EMPLOYEE_DEDUCTION_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",RUN_NO");
strFieldNamesInitialised.Clear();
//Return strings for field that need to be Initialised to Zero
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_DEDUCTION_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT");
strQry.AppendLine(" ED.COMPANY_NO");
strQry.AppendLine(",ED.PAY_CATEGORY_TYPE");
strQry.AppendLine(",ED.EMPLOYEE_NO");
strQry.AppendLine(",'T'");
strQry.AppendLine(",ED.DEDUCTION_NO ");
strQry.AppendLine(",ED.DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",1");
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT EDC");
strQry.AppendLine(" ON ED.COMPANY_NO = EDC.COMPANY_NO ");
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = EDC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND ED.EMPLOYEE_NO = EDC.EMPLOYEE_NO ");
strQry.AppendLine(" AND ED.DEDUCTION_NO = EDC.DEDUCTION_NO ");
strQry.AppendLine(" AND ED.DEDUCTION_SUB_ACCOUNT_NO = EDC.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND EDC.RUN_TYPE = 'T' ");
strQry.AppendLine(" WHERE ED.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND ED.EMPLOYEE_NO = " + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");
//No EMPLOYEE_DEDUCTION_HISTORY Record Exists
strQry.AppendLine(" AND EDC.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine("INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
//NB PAY_CATEGORY_NO will be Set to Zero
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",EARNING_NO");
//ELR 2014-05-24
strQry.AppendLine(",EARNING_TYPE_IND");
strQry.AppendLine(",RUN_NO");
strFieldNamesInitialised.Clear();
//Return strings for field that need to be Initialised to Zero
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_EARNING_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT");
strQry.AppendLine(" EN.COMPANY_NO");
strQry.AppendLine(",EN.PAY_CATEGORY_TYPE");
strQry.AppendLine("," + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(",'T'");
strQry.AppendLine(",EN.EARNING_NO ");
//ELR 2014-05-24
strQry.AppendLine(",'U'");
strQry.AppendLine(",1");
//Append Initialised Numeric Fields Names
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING EN ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.COMPANY C ");
strQry.AppendLine(" ON EN.COMPANY_NO = C.COMPANY_NO ");
strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT EEC");
strQry.AppendLine(" ON EN.COMPANY_NO = EEC.COMPANY_NO ");
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = EEC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EN.EARNING_NO = EEC.EARNING_NO ");
strQry.AppendLine(" AND EEC.RUN_TYPE = 'T' ");
strQry.AppendLine(" WHERE EN.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine(" AND (EN.EARNING_NO IN (1,2,7,8,9)");
if (DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
//Normal Leave / Sick Leave
strQry.AppendLine(" OR (EN.EARNING_NO IN (200,201))");
strQry.AppendLine(" OR (EN.EARNING_NO = 3 AND C.OVERTIME1_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 4 AND C.OVERTIME2_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 5 AND C.OVERTIME3_RATE > 0))");
}
else
{
strQry.AppendLine(" OR (EN.EARNING_NO = 3 AND C.SALARY_OVERTIME1_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 4 AND C.SALARY_OVERTIME2_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 5 AND C.SALARY_OVERTIME3_RATE > 0))");
}
strQry.AppendLine(" AND EN.DATETIME_DELETE_RECORD IS NULL");
//No EMPLOYEE_EARNING_HISTORY Record Exists
strQry.AppendLine(" AND EEC.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine("INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",EARNING_NO");
//ELR 2014-05-24
strQry.AppendLine(",EARNING_TYPE_IND");
strQry.AppendLine(",RUN_NO");
strFieldNamesInitialised.Clear();
//Return strings for field that need to be Initialised to Zero
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_EARNING_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT");
strQry.AppendLine(" EE.COMPANY_NO");
strQry.AppendLine(",EE.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EE.EMPLOYEE_NO");
strQry.AppendLine(",'T'");
strQry.AppendLine(",EE.EARNING_NO ");
//ELR 2014-05-24
strQry.AppendLine(",EE.EARNING_TYPE_IND");
strQry.AppendLine(",1");
//Append Initialised Numeric Fields Names
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT EEC");
strQry.AppendLine(" ON EE.COMPANY_NO = EEC.COMPANY_NO ");
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = EEC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EE.EMPLOYEE_NO = EEC.EMPLOYEE_NO ");
strQry.AppendLine(" AND EE.EARNING_NO = EEC.EARNING_NO ");
strQry.AppendLine(" AND EEC.RUN_TYPE = 'T' ");
strQry.AppendLine(" WHERE EE.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EE.EMPLOYEE_NO = " + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL");
//No EMPLOYEE_EARNING_HISTORY Record Exists
strQry.AppendLine(" AND EEC.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
//First Delete Records That are older than 180 Days
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND RUN_TYPE = 'T' ");
strQry.AppendLine(" AND EMPLOYEE_NO NOT IN (" + strEmployeeNoIn + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
//First Delete Records That are Older than 180 Days
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND RUN_TYPE = 'T' ");
strQry.AppendLine(" AND EMPLOYEE_NO NOT IN (" + strEmployeeNoIn + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
//EERORL CARRY HERE
DataSet.Tables.Add("PayrollType");
DataTable PayrollTypeDataTable = new DataTable("PayrollType");
DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));
if (DataSet.Tables["EmployeeTemp"].Rows.Count > 0)
{
DataView PayrollTypeDataView = new DataView(DataSet.Tables["EmployeeTemp"],
"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["EmployeeTemp"],
"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.Tables.Remove("EmployeeTemp");
DataSet.AcceptChanges();
byte[] bytTempCompress = Get_Company_Records(parInt64CompanyNo, DataSet.Tables["PayrollType"].Rows[0]["PAYROLL_TYPE_DESC"].ToString().Substring(0, 1));
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(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);
}
private void ProcessOpenRunQueue()
{
DataSet DataSetProcessOpenRunQueue = new DataSet();
int intWhere = 0;
try
{
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT TOP 2 ");
strQry.AppendLine(" USER_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",PAY_PERIOD_DATE");
strQry.AppendLine(",PAY_CATEGORY_NUMBERS");
strQry.AppendLine(" FROM InteractPayroll.dbo.OPEN_RUN_QUEUE");
strQry.AppendLine(" WHERE OPEN_RUN_QUEUE_IND IS NULL");
intWhere = 1;
clsDBConnectionObjectsProcessOpenRunQueue.Create_DataTable(strQry.ToString(), DataSetProcessOpenRunQueue, "OpenRunQueue", -1);
intWhere = 2;
if (DataSetProcessOpenRunQueue.Tables["OpenRunQueue"].Rows.Count > 0)
{
List <OpenRunQueue> openRunQueueList = new List <OpenRunQueue>();
intWhere = 3;
for (int intRow = 0; intRow < DataSetProcessOpenRunQueue.Tables["OpenRunQueue"].Rows.Count; intRow++)
{
OpenRunQueue openRunQueue = new OpenRunQueue();
openRunQueue.UserNo = Convert.ToInt64(DataSetProcessOpenRunQueue.Tables["OpenRunQueue"].Rows[intRow]["USER_NO"]);
openRunQueue.CompanyNo = Convert.ToInt64(DataSetProcessOpenRunQueue.Tables["OpenRunQueue"].Rows[intRow]["COMPANY_NO"]);
openRunQueue.PayCategoryType = DataSetProcessOpenRunQueue.Tables["OpenRunQueue"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString();
openRunQueue.PayCategoryNumberArray = DataSetProcessOpenRunQueue.Tables["OpenRunQueue"].Rows[intRow]["PAY_CATEGORY_NUMBERS"].ToString();
openRunQueue.PayPeriodDate = Convert.ToDateTime(DataSetProcessOpenRunQueue.Tables["OpenRunQueue"].Rows[intRow]["PAY_PERIOD_DATE"]);
intWhere = 4;
openRunQueueList.Add(openRunQueue);
}
intWhere = 5;
ProcessOpenRunQueueParallel(openRunQueueList);
}
}
catch (Exception ex)
{
WriteExceptionErrorLog("ProcessOpenRunQueue " + intWhere.ToString(), ex);
}
finally
{
DataSetProcessOpenRunQueue.Dispose();
}
}
private void ProcessEmailPayslipQueue()
{
DataSet DataSetProcessEmailQueue = new DataSet();
int intWhere = 0;
try
{
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT TOP 2 ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",USER_NO");
strQry.AppendLine(",PAY_PERIOD_DATE");
strQry.AppendLine(",PAYSLIP_EMAIL_QUEUE_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.PAYSLIP_EMAIL_QUEUE");
strQry.AppendLine(" WHERE START_RUN_DATE IS NULL");
intWhere = 1;
clsDBConnectionObjectsProcessEmailPayslipQueue.Create_DataTable(strQry.ToString(), DataSetProcessEmailQueue, "EmailPayslipQueue", -1);
if (DataSetProcessEmailQueue.Tables["EmailPayslipQueue"].Rows.Count > 0)
{
intWhere = 2;
List <EmailPayslipQueue> emailPayslipQueueList = new List <EmailPayslipQueue>();
intWhere = 3;
for (int intRow = 0; intRow < DataSetProcessEmailQueue.Tables["EmailPayslipQueue"].Rows.Count; intRow++)
{
intWhere = 4;
EmailPayslipQueue emailPayslipQueue = new EmailPayslipQueue();
emailPayslipQueue.CompanyNo = Convert.ToInt64(DataSetProcessEmailQueue.Tables["EmailPayslipQueue"].Rows[intRow]["COMPANY_NO"]);
emailPayslipQueue.UserNo = Convert.ToInt64(DataSetProcessEmailQueue.Tables["EmailPayslipQueue"].Rows[intRow]["USER_NO"]);
emailPayslipQueue.PayPeriodDate = Convert.ToDateTime(DataSetProcessEmailQueue.Tables["EmailPayslipQueue"].Rows[intRow]["PAY_PERIOD_DATE"]);
emailPayslipQueue.PayslipEmailQueueNo = Convert.ToInt32(DataSetProcessEmailQueue.Tables["EmailPayslipQueue"].Rows[intRow]["PAYSLIP_EMAIL_QUEUE_NO"]);
intWhere = 5;
emailPayslipQueueList.Add(emailPayslipQueue);
}
intWhere = 6;
ProcessEmailPayslipQueueParallel(emailPayslipQueueList);
}
}
catch (Exception ex)
{
WriteExceptionErrorLog("ProcessEmailPayslipQueue " + intWhere.ToString(), ex);
}
finally
{
DataSetProcessEmailQueue.Dispose();
}
}
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, 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(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 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);
}
private void ProcessCloseQueue()
{
DataSet DataSetProcessCloseQueue = new DataSet();
int intWhere = 0;
try
{
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT TOP 2 ");
strQry.AppendLine(" USER_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",WAGE_PAY_PERIOD_DATE");
strQry.AppendLine(",SALARY_PAY_PERIOD_DATE");
strQry.AppendLine(",WAGES_PAY_CATEGORY_NUMBERS");
strQry.AppendLine(",SALARIES_PAY_CATEGORY_NUMBERS");
strQry.AppendLine(" FROM InteractPayroll.dbo.CLOSE_RUN_QUEUE");
strQry.AppendLine(" WHERE CLOSE_RUN_QUEUE_IND IS NULL");
intWhere = 1;
clsDBConnectionObjectsProcessCloseQueue.Create_DataTable(strQry.ToString(), DataSetProcessCloseQueue, "CloseQueue", -1);
if (DataSetProcessCloseQueue.Tables["CloseQueue"].Rows.Count > 0)
{
intWhere = 2;
List <CloseQueue> closeQueueList = new List <CloseQueue>();
intWhere = 3;
for (int intRow = 0; intRow < DataSetProcessCloseQueue.Tables["CloseQueue"].Rows.Count; intRow++)
{
intWhere = 4;
CloseQueue closeQueue = new CloseQueue();
closeQueue.UserNo = Convert.ToInt64(DataSetProcessCloseQueue.Tables["CloseQueue"].Rows[intRow]["USER_NO"]);
closeQueue.CompanyNo = Convert.ToInt64(DataSetProcessCloseQueue.Tables["CloseQueue"].Rows[intRow]["COMPANY_NO"]);
closeQueue.RunType = DataSetProcessCloseQueue.Tables["CloseQueue"].Rows[intRow]["RUN_TYPE"].ToString();
closeQueue.WagesPayCategoryNumberArray = DataSetProcessCloseQueue.Tables["CloseQueue"].Rows[intRow]["WAGES_PAY_CATEGORY_NUMBERS"].ToString();
closeQueue.SalariesPayCategoryNumberArray = DataSetProcessCloseQueue.Tables["CloseQueue"].Rows[intRow]["SALARIES_PAY_CATEGORY_NUMBERS"].ToString();
if (closeQueue.RunType != "S")
{
closeQueue.WagePayPeriodDate = Convert.ToDateTime(DataSetProcessCloseQueue.Tables["CloseQueue"].Rows[intRow]["WAGE_PAY_PERIOD_DATE"]);
}
if (closeQueue.RunType == "B" ||
closeQueue.RunType == "S")
{
closeQueue.SalaryPayPeriodDate = Convert.ToDateTime(DataSetProcessCloseQueue.Tables["CloseQueue"].Rows[intRow]["SALARY_PAY_PERIOD_DATE"]);
}
intWhere = 5;
closeQueueList.Add(closeQueue);
}
intWhere = 6;
ProcessCloseRunQueueParallel(closeQueueList);
}
}
catch (Exception ex)
{
WriteExceptionErrorLog("ProcessCloseQueue " + intWhere.ToString(), ex);
}
finally
{
DataSetProcessCloseQueue.Dispose();
}
}
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);
}