public int Backup_DataBase(Int64 parInt64CompanyNo, string parstrPayrollType, string parstrOption)
{
int intReturnCode = 9;
try
{
DataSet pvtDataSet = new DataSet();
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" BACKUP_DATABASE_PATH");
strQry.AppendLine(" FROM InteractPayroll.dbo.BACKUP_DATABASE_PATH");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), pvtDataSet, "Directory", -1);
string strFileDirectory = pvtDataSet.Tables["Directory"].Rows[0]["BACKUP_DATABASE_PATH"].ToString();
string strDataBaseName = "InteractPayroll_" + parInt64CompanyNo.ToString("00000");
string strBackupFileName = strDataBaseName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + "_BeforeEmployeeLeaveActivation.bak";
if (parstrOption == "A")
{
strBackupFileName = strDataBaseName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + "_AfterEmployeeLeaveActivation.bak";
}
strQry.Clear();
strQry.AppendLine("BACKUP DATABASE " + strDataBaseName + " TO DISK = '" + strFileDirectory + "\\" + strBackupFileName + "' WITH CHECKSUM");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
strQry.Clear();
strQry.AppendLine("INSERT INTO InteractPayroll.dbo.BACKUP_DATABASE_DATETIME");
strQry.AppendLine("(BACKUP_DATABASE_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",BACKUP_DATABASE_NAME");
strQry.AppendLine(",PAYROLL_RUN_DATETIME");
strQry.AppendLine(",BACKUP_DATETIME");
strQry.AppendLine(",BACKUP_FILE_NAME)");
strQry.AppendLine(" SELECT");
strQry.AppendLine(" ISNULL(MAX(BACKUP_DATABASE_NO),0) + 1");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(strDataBaseName));
strQry.AppendLine(",GETDATE()");
strQry.AppendLine(",GETDATE()");
strQry.AppendLine(",'" + strFileDirectory + "\\" + strBackupFileName + "'");
strQry.AppendLine(" FROM InteractPayroll.dbo.BACKUP_DATABASE_DATETIME");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
intReturnCode = 0;
}
catch (Exception ex)
{
string strStop = "";
}
return(intReturnCode);
}
public void Delete_Record(Int64 parint64UserNo, string parstrFileUploadDatetime, string parstrFileName)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll.dbo.FILE_UPLOAD_DETAILS ");
strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo);
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFileName));
strQry.AppendLine(" AND UPLOAD_DATETIME = '" + parstrFileUploadDatetime + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll.dbo.FILE_UPLOAD_DETAILS_FOR_USERS ");
strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo);
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFileName));
strQry.AppendLine(" AND UPLOAD_DATETIME = '" + parstrFileUploadDatetime + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll.dbo.FILE_UPLOAD_CHUNKS ");
strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo);
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFileName));
strQry.AppendLine(" AND UPLOAD_DATETIME = '" + parstrFileUploadDatetime + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
public int Insert_Record(byte[] byteCompressedDataSet)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteCompressedDataSet);
StringBuilder strQry = new StringBuilder();
int intDeviceNo = 0;
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(DEVICE_NO) AS MAX_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
{
intDeviceNo = 1;
}
else
{
intDeviceNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",DEVICE_DESC");
strQry.AppendLine(",DEVICE_USAGE");
strQry.AppendLine(",TIME_ATTEND_CLOCK_FIRST_LAST_IND");
strQry.AppendLine(",LOCK_OUT_MINUTES");
strQry.AppendLine(",CLOCK_IN_OUT_PARM");
strQry.AppendLine(",CLOCK_IN_RANGE_FROM");
strQry.AppendLine(",CLOCK_IN_RANGE_TO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",LAN_WAN_IND");
strQry.AppendLine(",FAR_REQUESTED)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + intDeviceNo);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["DEVICE_DESC"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["DEVICE_USAGE"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["TIME_ATTEND_CLOCK_FIRST_LAST_IND"].ToString()));
strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["LOCK_OUT_MINUTES"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["CLOCK_IN_OUT_PARM"].ToString()));
strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["CLOCK_IN_RANGE_FROM"].ToString());
strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["CLOCK_IN_RANGE_TO"].ToString());
strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["LAN_WAN_IND"].ToString()));
strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["FAR_REQUESTED"].ToString() + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
return(intDeviceNo);
}
public int Insert_New_Record(Int64 parint64CompanyNo, string parstrPayCategoryType, string strFromDate, string strToDate, bool blnLocked)
{
DataSet DataSet = new DataSet();
int intPayCategoryShiftScheduleNo = 1;
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_SHIFT_SCHEDULE");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",FROM_DATETIME");
strQry.AppendLine(",TO_DATETIME");
strQry.AppendLine(",LOCKED_IND)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parint64CompanyNo);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayCategoryType));
strQry.AppendLine(",'" + strFromDate + "'");
strQry.AppendLine(",'" + strToDate + "'");
if (blnLocked == true)
{
strQry.AppendLine(",1)");
}
else
{
strQry.AppendLine(",0)");
}
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(PAY_CATEGORY_SHIFT_SCHEDULE_NO) AS MAX_PAY_CATEGORY_SHIFT_SCHEDULE_NO ");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_SHIFT_SCHEDULE");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayCategoryType));
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "ShiftSchedule", parint64CompanyNo);
intPayCategoryShiftScheduleNo = Convert.ToInt32(DataSet.Tables["ShiftSchedule"].Rows[0]["MAX_PAY_CATEGORY_SHIFT_SCHEDULE_NO"]);
DataSet.Dispose();
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" SET BACKUP_DB_IND = 1");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
return(intPayCategoryShiftScheduleNo);
}
public int Insert_Group(Int64 parint64CompanyNo, string parstrGroupDesc, byte[] byteCompressedDataSet)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteCompressedDataSet);
int intGroupNo = 0;
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(GROUP_NO) AS MAX_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.GROUPS");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
{
intGroupNo = 1;
}
else
{
intGroupNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.GROUPS");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",GROUP_NO");
strQry.AppendLine(",GROUP_DESC)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parint64CompanyNo);
strQry.AppendLine("," + intGroupNo);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrGroupDesc) + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
Insert_Group_Employee_Link(parint64CompanyNo, intGroupNo, DataSet);
return(intGroupNo);
}
public void Update_Record(string parstrRemoteBackupSiteName)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
if (parstrRemoteBackupSiteName == "")
{
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME");
}
else
{
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" SITE_NAME");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "RemoteBackupSiteName");
if (DataSet.Tables["RemoteBackupSiteName"].Rows.Count > 0)
{
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME");
strQry.AppendLine(" SET ");
strQry.AppendLine(" SITE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parstrRemoteBackupSiteName));
}
else
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME");
strQry.AppendLine(" (SITE_NAME)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + clsDBConnectionObjects.Text2DynamicSQL(parstrRemoteBackupSiteName) + ")");
}
}
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
DataSet.Dispose();
DataSet = null;
}
public void Update_User_Last_Pay_Category_Type(Int64 parint64CurrentUserNo, string parstrLastPayCategoryType)
{
string strQry = "";
strQry = "";
strQry += " UPDATE InteractPayroll.dbo.USER_ID ";
strQry += " SET LAST_PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrLastPayCategoryType);
strQry += " WHERE USER_NO = " + parint64CurrentUserNo;
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
}
public string Update_Password(Int64 parint64UserNo, Int64 parint64CurrentUserNo)
{
StringBuilder strQry = new StringBuilder();
System.Random RandomNumber = new System.Random();
string strPassword = RandomNumber.Next(1000, 999999).ToString();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.USER_ID");
strQry.AppendLine(" SET ");
strQry.AppendLine(" RESET = 'Y'");
strQry.AppendLine(",PASSWORD = "******",USER_NO_RECORD = " + parint64CurrentUserNo);
strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
return(strPassword);
}
public void Update_Password(Int64 parint64CurrentUserNo, string parstrPassword)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.USER_ID");
strQry.AppendLine(" SET ");
strQry.AppendLine(" PASSWORD = "******",RESET = 'N'");
strQry.AppendLine(",USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
public byte[] Get_File_Chunk(string parstrFileName, int parintFileChunkNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FILE_CHUNK");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_UPLOAD_CHUNKS");
strQry.AppendLine(" WHERE FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFileName));
strQry.AppendLine(" AND FILE_CHUNK_NO = " + parintFileChunkNo);
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
return((byte[])DataSet.Tables["Temp"].Rows[0]["FILE_CHUNK"]);
}
public byte[] Insert_New_Record(Int64 parInt64CurrentUserNo, byte[] parbyteDataSet)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
byte[] bytCompress;
int intEarningNo = -1;
Int64 Int64CompanyNo = Convert.ToInt64(parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"]);
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT ");
strQry.AppendLine(" 0 AS RETURN_CODE ");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Check", Int64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT ");
strQry.AppendLine(" EARNING_NO ");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables[0].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables[0].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
//Not a Take-On
strQry.AppendLine(" AND PAY_CATEGORY_NO > 0 ");
strQry.AppendLine(" AND RUN_TYPE = 'P'");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", Int64CompanyNo);
if (DataSet.Tables["Temp"].Rows.Count > 0)
{
DataSet.Tables["Check"].Rows[0]["RETURN_CODE"] = 9999;
goto Insert_New_Record_Continue;
}
DataSet.Tables.Remove("Temp");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(EARNING_NO) AS MAX_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING");
strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine(" AND EARNING_NO > 149");
//Leave Range
strQry.AppendLine(" AND EARNING_NO < 200");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", Int64CompanyNo);
if (DataSet.Tables["Temp"].Rows[0]["MAX_NO"] == System.DBNull.Value)
{
intEarningNo = 150;
}
else
{
intEarningNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"] = intEarningNo;
parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_TYPE_DEFAULT"] = "Y";
//Returned DataTable
DataSet.Tables.Add(parDataSet.Tables["EarningSelected"].Clone());
DataSet.Tables["EarningSelected"].ImportRow(parDataSet.Tables["EarningSelected"].Rows[0]);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EARNING");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",EARNING_DESC");
strQry.AppendLine(",IRP5_CODE");
strQry.AppendLine(",LEAVE_PERCENTAGE");
strQry.AppendLine(",EARNING_REPORT_HEADER1");
strQry.AppendLine(",EARNING_REPORT_HEADER2");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",EARNING_DEL_IND)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"].ToString());
strQry.AppendLine(",1");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_DESC"].ToString()));
strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["EarningSelected"].Rows[0]["IRP5_CODE"]));
strQry.AppendLine(",100");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER1"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER2"].ToString()));
strQry.AppendLine("," + parInt64CurrentUserNo);
strQry.AppendLine(",GETDATE()");
strQry.AppendLine(",'Y')");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);
//Create For Opposite PAY_CATEGORY_TYPE
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EARNING");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",EARNING_DESC");
strQry.AppendLine(",IRP5_CODE");
strQry.AppendLine(",LEAVE_PERCENTAGE");
strQry.AppendLine(",EARNING_REPORT_HEADER1");
strQry.AppendLine(",EARNING_REPORT_HEADER2");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",EARNING_DEL_IND)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());
if (parDataSet.Tables["EarningSelected"].Rows[0]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(",'S'");
}
else
{
strQry.AppendLine(",'W'");
}
strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"].ToString());
strQry.AppendLine(",1");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_DESC"].ToString()));
strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["EarningSelected"].Rows[0]["IRP5_CODE"]));
strQry.AppendLine(",100");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER1"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER2"].ToString()));
strQry.AppendLine("," + parInt64CurrentUserNo);
strQry.AppendLine(",GETDATE()");
strQry.AppendLine(",'Y')");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",AMOUNT");
strQry.AppendLine(",EARNING_TYPE_IND");
strQry.AppendLine(",EARNING_PERIOD_IND");
strQry.AppendLine(",EARNING_DAY_VALUE");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
//Default EMPLOYEE_NO
strQry.AppendLine(",0");
strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"].ToString());
strQry.AppendLine(",1");
strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["AMOUNT"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_TYPE_IND"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_PERIOD_IND"].ToString()));
strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_DAY_VALUE"].ToString());
strQry.AppendLine("," + parInt64CurrentUserNo);
strQry.AppendLine(",GETDATE())");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);
strQry.Clear();
//Create For Opposite PAY_CATEGORY_TYPE
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",AMOUNT");
strQry.AppendLine(",EARNING_TYPE_IND");
strQry.AppendLine(",EARNING_PERIOD_IND");
strQry.AppendLine(",EARNING_DAY_VALUE");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString());
if (parDataSet.Tables["EarningSelected"].Rows[0]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(",'S'");
}
else
{
strQry.AppendLine(",'W'");
}
//Default EMPLOYEE_NO
strQry.AppendLine(",0");
strQry.AppendLine("," + parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"].ToString());
strQry.AppendLine(",1");
strQry.AppendLine(",0");
strQry.AppendLine(",'U'");
strQry.AppendLine(",'E'");
strQry.AppendLine(",0");
strQry.AppendLine("," + parInt64CurrentUserNo);
strQry.AppendLine(",GETDATE())");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);
//Create Record for Opposite PAY_CATEGORY_TYPE
DataRow myDataRow = DataSet.Tables["EarningSelected"].NewRow();
myDataRow["COMPANY_NO"] = parDataSet.Tables["EarningSelected"].Rows[0]["COMPANY_NO"].ToString();
if (parDataSet.Tables["EarningSelected"].Rows[0]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
myDataRow["PAY_CATEGORY_TYPE"] = "S";
}
else
{
myDataRow["PAY_CATEGORY_TYPE"] = "W";
}
myDataRow["EMPLOYEE_NO"] = 0;
myDataRow["EARNING_NO"] = Convert.ToInt16(parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_NO"]);
myDataRow["EARNING_DESC"] = parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_DESC"].ToString();
myDataRow["AMOUNT"] = 0;
myDataRow["IRP5_CODE"] = parDataSet.Tables["EarningSelected"].Rows[0]["IRP5_CODE"].ToString();
myDataRow["EARNING_REPORT_HEADER1"] = parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER1"].ToString();
myDataRow["EARNING_REPORT_HEADER2"] = parDataSet.Tables["EarningSelected"].Rows[0]["EARNING_REPORT_HEADER2"].ToString();
myDataRow["EARNING_TYPE_IND"] = "U";
myDataRow["EARNING_TYPE_DEFAULT"] = "Y";
myDataRow["EARNING_PERIOD_IND"] = "E";
myDataRow["EARNING_DAY_VALUE"] = 0;
myDataRow["EARNING_DEL_IND"] = "Y";
myDataRow["TIE_BREAKER"] = 1;
DataSet.Tables["EarningSelected"].Rows.Add(myDataRow);
Insert_New_Record_Continue:
DataSet.Tables.Remove("Temp");
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" SET BACKUP_DB_IND = 1");
strQry.AppendLine(" WHERE COMPANY_NO = " + Int64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
DataSet.AcceptChanges();
bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
parDataSet.Dispose();
parDataSet = null;
return(bytCompress);
}
public byte[] Update_Record(Int64 parint64CompanyNo, string parstrPayrollType, int parintAuthoriseLevel, Int64 parint64CurrentUserNo, string parstrCurrentUserAccessInd, string parstrFromProgram, byte[] parbyteDataSet)
{
DataSet DataSet = new System.Data.DataSet();
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
for (int intRow = 0; intRow < parDataSet.Tables["EmployeePayCategoryLevel"].Rows.Count; intRow++)
{
strQry.Clear();
strQry.AppendLine(" UPDATE EPCLAC ");
strQry.AppendLine(" SET ");
strQry.AppendLine(" EPCLAC.AUTHORISED_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeePayCategoryLevel"].Rows[intRow]["AUTHORISED_IND"].ToString()));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT EPCLAC ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT EPCLAC_T ");
strQry.AppendLine(" ON EPCLAC.COMPANY_NO = EPCLAC_T.COMPANY_NO ");
strQry.AppendLine(" AND EPCLAC.EMPLOYEE_NO = EPCLAC_T.EMPLOYEE_NO ");
strQry.AppendLine(" AND EPCLAC.PAY_CATEGORY_NO = EPCLAC_T.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND EPCLAC.PAY_CATEGORY_TYPE = EPCLAC_T.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EPCLAC.EARNING_NO = EPCLAC_T.EARNING_NO ");
strQry.AppendLine(" AND EPCLAC.LEVEL_NO = EPCLAC_T.LEVEL_NO ");
strQry.AppendLine(" AND EPCLAC.LEAVE_REC_NO = EPCLAC_T.LEAVE_REC_NO ");
strQry.AppendLine(" AND EPCLAC.USER_NO = EPCLAC_T.USER_NO ");
strQry.AppendLine(" AND EPCLAC_T.AUTHORISED_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeePayCategoryLevel"].Rows[intRow]["AUTHORISED_IND"].ToString()));
strQry.AppendLine(" WHERE EPCLAC.COMPANY_NO = " + parDataSet.Tables["EmployeePayCategoryLevel"].Rows[intRow]["COMPANY_NO"].ToString());
strQry.AppendLine(" AND EPCLAC.EMPLOYEE_NO = " + parDataSet.Tables["EmployeePayCategoryLevel"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND EPCLAC.PAY_CATEGORY_NO = " + parDataSet.Tables["EmployeePayCategoryLevel"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND EPCLAC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeePayCategoryLevel"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND EPCLAC.EARNING_NO = " + parDataSet.Tables["EmployeePayCategoryLevel"].Rows[intRow]["EARNING_NO"].ToString());
strQry.AppendLine(" AND EPCLAC.LEAVE_REC_NO = " + parDataSet.Tables["EmployeePayCategoryLevel"].Rows[intRow]["LEAVE_REC_NO"].ToString());
if (parDataSet.Tables["EmployeePayCategoryLevel"].Rows[intRow]["AUTHORISED_IND"].ToString() == "Y")
{
strQry.AppendLine(" AND EPCLAC.LEVEL_NO = " + parDataSet.Tables["EmployeePayCategoryLevel"].Rows[intRow]["LEVEL_NO"].ToString());
strQry.AppendLine(" AND EPCLAC.USER_NO = " + parint64CurrentUserNo.ToString());
}
else
{
//Set All Higher Levels Also to NOT Authorised
strQry.AppendLine(" AND EPCLAC.LEVEL_NO >= " + parDataSet.Tables["EmployeePayCategoryLevel"].Rows[intRow]["LEVEL_NO"].ToString());
}
//Row is Different
strQry.AppendLine(" AND EPCLAC_T.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
}
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" SET BACKUP_DB_IND = 1");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
byte[] bytCompress = Get_User_Level_Records(parint64CompanyNo, parint64CurrentUserNo, parstrCurrentUserAccessInd, parstrFromProgram);
return(bytCompress);
}
public int Update_Record(string parstrCurrentUserAccess, Int64 parint64CurrentUserNo, string parstrPayrollType, byte[] parbyteDataSet, Int64 parint64CompanyNo)
{
int intReturnCode = 0;
DataSet DataSet = new DataSet();
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT");
if (parstrPayrollType == "W")
{
strQry.AppendLine(" WAGE_RUN_IND AS RUN_IND");
}
else
{
if (parstrPayrollType == "S")
{
strQry.AppendLine(" SALARY_RUN_IND AS RUN_IND ");
}
}
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "CompanyCheck", parint64CompanyNo);
if (DataSet.Tables["CompanyCheck"].Rows[0]["RUN_IND"].ToString() == "")
{
for (int intRow = 0; intRow < parDataSet.Tables["Leave"].Rows.Count; intRow++)
{
if (parDataSet.Tables["Leave"].Rows[intRow].RowState == DataRowState.Deleted)
{
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT");
strQry.AppendLine(" WHERE COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["COMPANY_NO", System.Data.DataRowVersion.Original]));
strQry.AppendLine(" AND EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EMPLOYEE_NO", System.Data.DataRowVersion.Original]));
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["PAY_CATEGORY_TYPE", System.Data.DataRowVersion.Original].ToString()));
strQry.AppendLine(" AND EARNING_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO", System.Data.DataRowVersion.Original]));
strQry.AppendLine(" AND LEAVE_REC_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO", System.Data.DataRowVersion.Original]));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT");
strQry.AppendLine(" WHERE COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["COMPANY_NO", System.Data.DataRowVersion.Original]));
strQry.AppendLine(" AND EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EMPLOYEE_NO", System.Data.DataRowVersion.Original]));
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["PAY_CATEGORY_TYPE", System.Data.DataRowVersion.Original].ToString()));
strQry.AppendLine(" AND EARNING_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO", System.Data.DataRowVersion.Original]));
strQry.AppendLine(" AND LEAVE_REC_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO", System.Data.DataRowVersion.Original]));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
}
else
{
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT");
strQry.AppendLine(" SET ");
strQry.AppendLine(" PROCESS_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["PROCESS_NO"]));
strQry.AppendLine(",LEAVE_DESC = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_DESC"].ToString()));
strQry.AppendLine(",LEAVE_FROM_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_FROM_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",LEAVE_TO_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_TO_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",LEAVE_DAYS_DECIMAL = " + Convert.ToDouble(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_DAYS_DECIMAL"]));
strQry.AppendLine(",LEAVE_OPTION = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_OPTION"].ToString()));
if (parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_HOURS_DECIMAL"] != System.DBNull.Value)
{
strQry.AppendLine(",LEAVE_HOURS_DECIMAL = " + Convert.ToDouble(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_HOURS_DECIMAL"]));
}
strQry.AppendLine(" WHERE COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["COMPANY_NO"]));
strQry.AppendLine(" AND EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EMPLOYEE_NO"]));
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND EARNING_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO"]));
strQry.AppendLine(" AND LEAVE_REC_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO"]));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
if (Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["PROCESS_NO"]) == 0)
{
//Insert
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",LEVEL_NO");
strQry.AppendLine(",LEAVE_REC_NO");
strQry.AppendLine(",USER_NO");
strQry.AppendLine(",AUTHORISED_IND)");
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" EPC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO"]));
strQry.AppendLine(",PCA.LEVEL_NO");
strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO"]));
strQry.AppendLine(",PCA.USER_NO");
strQry.AppendLine(",'N'");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" ON EPC.COMPANY_NO = E.COMPANY_NO");
strQry.AppendLine(" AND EPC.EMPLOYEE_NO = E.EMPLOYEE_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE");
//Employee Has Been Activates (Taken-On)
strQry.AppendLine(" AND E.EMPLOYEE_TAKEON_IND = 'Y'");
//Employee NOT Closed
strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_AUTHORISE PCA");
strQry.AppendLine(" ON EPC.COMPANY_NO = PCA.COMPANY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = PCA.PAY_CATEGORY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = PCA.PAY_CATEGORY_TYPE");
//T = Timesheet, L = Leave
strQry.AppendLine(" AND PCA.AUTHORISE_TYPE_IND = 'L'");
strQry.AppendLine(" AND PCA.DATETIME_DELETE_RECORD IS NULL");
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 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 InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT EPCLAC");
strQry.AppendLine(" ON EPC.COMPANY_NO = EPCLAC.COMPANY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = EPCLAC.PAY_CATEGORY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = EPCLAC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND EPCLAC.EARNING_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO"]));
strQry.AppendLine(" AND PCA.LEVEL_NO = EPCLAC.LEVEL_NO");
strQry.AppendLine(" AND EPCLAC.LEAVE_REC_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO"]));
strQry.AppendLine(" AND EPCLAC.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" WHERE EPC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EPC.EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EMPLOYEE_NO"]));
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
//Default is Link for LEAVE
strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL");
//No Record Currently Exists
strQry.AppendLine(" AND EPCLAC.COMPANY_NO IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
}
else
{
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT");
strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables["Leave"].Rows[intRow]["COMPANY_NO"].ToString());
strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Leave"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parDataSet.Tables["Leave"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Leave"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND EARNING_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["EARNING_NO"]));
strQry.AppendLine(" AND LEAVE_REC_NO = " + Convert.ToInt32(parDataSet.Tables["Leave"].Rows[intRow]["LEAVE_REC_NO"]));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
}
}
}
}
else
{
intReturnCode = 1;
}
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" SET BACKUP_DB_IND = 1");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
DataSet.Dispose();
DataSet = null;
return(intReturnCode);
}
public byte[] Update_User_PayCategory_Employee(Int64 parint64CurrentUserNo, byte[] parbyteDataSet)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
DataSet DataSet = new DataSet();
DataTable myDataTable = parDataSet.Tables["UserPayCategory"].Clone();
DataSet.Tables.Add(myDataTable);
myDataTable = parDataSet.Tables["UserDepartment"].Clone();
DataSet.Tables.Add(myDataTable);
myDataTable = parDataSet.Tables["UserEmployee"].Clone();
DataSet.Tables.Add(myDataTable);
StringBuilder strQry = new StringBuilder();
int intTieBreaker = -1;
object[] objAddOther = new object[6];
object[] objAdd = new object[5];
string[] strQryArray = new string[parDataSet.Tables["UserPayCategory"].Rows.Count + parDataSet.Tables["UserDepartment"].Rows.Count + parDataSet.Tables["UserEmployee"].Rows.Count];
for (int intRow = 0; intRow < parDataSet.Tables["UserPayCategory"].Rows.Count; intRow++)
{
if (parDataSet.Tables["UserPayCategory"].Rows[intRow].RowState == DataRowState.Deleted)
{
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.USER_PAY_CATEGORY");
strQry.AppendLine(" SET ");
strQry.AppendLine(" USER_NO_RECORD = " + parint64CurrentUserNo);
strQry.AppendLine(",DATETIME_DELETE_RECORD = GETDATE()");
strQry.AppendLine(" WHERE USER_NO = " + Convert.ToInt32(parDataSet.Tables["UserPayCategory"].Rows[intRow]["USER_NO", DataRowVersion.Original]));
strQry.AppendLine(" AND COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["UserPayCategory"].Rows[intRow]["COMPANY_NO", DataRowVersion.Original]));
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + Convert.ToInt32(parDataSet.Tables["UserPayCategory"].Rows[intRow]["PAY_CATEGORY_NO", DataRowVersion.Original]));
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = '" + parDataSet.Tables["UserPayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE", DataRowVersion.Original].ToString() + "'");
strQry.AppendLine(" AND TIE_BREAKER = " + Convert.ToInt32(parDataSet.Tables["UserPayCategory"].Rows[intRow]["TIE_BREAKER", DataRowVersion.Original]));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
else
{
if (parDataSet.Tables["UserPayCategory"].Rows[intRow].RowState == DataRowState.Added)
{
if (DataSet.Tables["Temp"] != null)
{
DataSet.Tables.Remove("Temp");
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(TIE_BREAKER) AS MAX_NO");
strQry.AppendLine(" FROM ");
strQry.AppendLine(" InteractPayroll.dbo.USER_PAY_CATEGORY");
strQry.AppendLine(" WHERE USER_NO = " + Convert.ToInt32(parDataSet.Tables["UserPayCategory"].Rows[intRow]["USER_NO"]));
strQry.AppendLine(" AND COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["UserPayCategory"].Rows[intRow]["COMPANY_NO"]));
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + Convert.ToInt32(parDataSet.Tables["UserPayCategory"].Rows[intRow]["PAY_CATEGORY_NO"]));
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = '" + parDataSet.Tables["UserPayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", -1);
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
{
intTieBreaker = 1;
}
else
{
intTieBreaker = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
//Insert if Doesn't Exist
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.USER_PAY_CATEGORY");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parDataSet.Tables["UserPayCategory"].Rows[intRow]["USER_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["UserPayCategory"].Rows[intRow]["COMPANY_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["UserPayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(",'" + parDataSet.Tables["UserPayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine("," + intTieBreaker);
strQry.AppendLine(",GETDATE()");
strQry.AppendLine("," + parint64CurrentUserNo + ")");
objAdd[0] = Convert.ToInt32(parDataSet.Tables["UserPayCategory"].Rows[intRow]["COMPANY_NO"]);
objAdd[1] = Convert.ToInt32(parDataSet.Tables["UserPayCategory"].Rows[intRow]["USER_NO"]);
objAdd[2] = Convert.ToInt32(parDataSet.Tables["UserPayCategory"].Rows[intRow]["PAY_CATEGORY_NO"]);
objAdd[3] = parDataSet.Tables["UserPayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString();
objAdd[4] = intTieBreaker;
DataSet.Tables["UserPayCategory"].Rows.Add(objAdd);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
}
}
for (int intRow = 0; intRow < parDataSet.Tables["UserDepartment"].Rows.Count; intRow++)
{
if (parDataSet.Tables["UserDepartment"].Rows[intRow].RowState == DataRowState.Deleted)
{
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.USER_PAY_CATEGORY_DEPARTMENT");
strQry.AppendLine(" SET ");
strQry.AppendLine(" USER_NO_RECORD = " + parint64CurrentUserNo);
strQry.AppendLine(",DATETIME_DELETE_RECORD = GETDATE()");
strQry.AppendLine(" WHERE USER_NO = " + Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["USER_NO", DataRowVersion.Original]));
strQry.AppendLine(" AND COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["COMPANY_NO", DataRowVersion.Original]));
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["PAY_CATEGORY_NO", DataRowVersion.Original]));
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["UserDepartment"].Rows[intRow]["PAY_CATEGORY_TYPE", DataRowVersion.Original].ToString()));
strQry.AppendLine(" AND DEPARTMENT_NO = " + Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["DEPARTMENT_NO", DataRowVersion.Original]));
strQry.AppendLine(" AND TIE_BREAKER = " + Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["TIE_BREAKER", DataRowVersion.Original]));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
else
{
if (parDataSet.Tables["UserDepartment"].Rows[intRow].RowState == DataRowState.Added)
{
if (DataSet.Tables["Temp"] != null)
{
DataSet.Tables.Remove("Temp");
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(TIE_BREAKER) AS MAX_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_PAY_CATEGORY_DEPARTMENT");
strQry.AppendLine(" WHERE USER_NO = " + Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["USER_NO"]));
strQry.AppendLine(" AND COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["COMPANY_NO"]));
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["PAY_CATEGORY_NO"]));
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["UserDepartment"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND DEPARTMENT_NO = " + Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["DEPARTMENT_NO"]));
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", -1);
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
{
intTieBreaker = 1;
}
else
{
intTieBreaker = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
//Insert if Doesn't Exist
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.USER_PAY_CATEGORY_DEPARTMENT");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",DEPARTMENT_NO");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parDataSet.Tables["UserDepartment"].Rows[intRow]["USER_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["UserDepartment"].Rows[intRow]["COMPANY_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["UserDepartment"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["UserDepartment"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine("," + parDataSet.Tables["UserDepartment"].Rows[intRow]["DEPARTMENT_NO"].ToString());
strQry.AppendLine("," + intTieBreaker);
strQry.AppendLine(",GETDATE()");
strQry.AppendLine("," + parint64CurrentUserNo + ")");
objAddOther[0] = Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["COMPANY_NO"]);
objAddOther[1] = Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["USER_NO"]);
objAddOther[2] = Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["PAY_CATEGORY_NO"]);
objAddOther[3] = parDataSet.Tables["UserDepartment"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString();
objAddOther[4] = Convert.ToInt32(parDataSet.Tables["UserDepartment"].Rows[intRow]["DEPARTMENT_NO"]);
objAddOther[5] = intTieBreaker;
DataSet.Tables["UserDepartment"].Rows.Add(objAddOther);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
}
}
for (int intRow = 0; intRow < parDataSet.Tables["UserEmployee"].Rows.Count; intRow++)
{
if (parDataSet.Tables["UserEmployee"].Rows[intRow].RowState == DataRowState.Deleted)
{
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.USER_EMPLOYEE");
strQry.AppendLine(" SET ");
strQry.AppendLine(" USER_NO_RECORD = " + parint64CurrentUserNo);
strQry.AppendLine(",DATETIME_DELETE_RECORD = GETDATE()");
strQry.AppendLine(" WHERE USER_NO = " + Convert.ToInt32(parDataSet.Tables["UserEmployee"].Rows[intRow]["USER_NO", DataRowVersion.Original]));
strQry.AppendLine(" AND COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["UserEmployee"].Rows[intRow]["COMPANY_NO", DataRowVersion.Original]));
strQry.AppendLine(" AND EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["UserEmployee"].Rows[intRow]["EMPLOYEE_NO", DataRowVersion.Original]));
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = '" + parDataSet.Tables["UserEmployee"].Rows[intRow]["PAY_CATEGORY_TYPE", DataRowVersion.Original].ToString() + "'");
strQry.AppendLine(" AND TIE_BREAKER = " + Convert.ToInt32(parDataSet.Tables["UserEmployee"].Rows[intRow]["TIE_BREAKER", DataRowVersion.Original]));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
else
{
if (parDataSet.Tables["UserEmployee"].Rows[intRow].RowState == DataRowState.Added)
{
if (DataSet.Tables["Temp"] != null)
{
DataSet.Tables.Remove("Temp");
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(TIE_BREAKER) AS MAX_NO");
strQry.AppendLine(" FROM ");
strQry.AppendLine(" InteractPayroll.dbo.USER_EMPLOYEE");
strQry.AppendLine(" WHERE USER_NO = " + Convert.ToInt32(parDataSet.Tables["UserEmployee"].Rows[intRow]["USER_NO"]));
strQry.AppendLine(" AND COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["UserEmployee"].Rows[intRow]["COMPANY_NO"]));
strQry.AppendLine(" AND EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["UserEmployee"].Rows[intRow]["EMPLOYEE_NO"]));
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = '" + parDataSet.Tables["UserEmployee"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", -1);
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
{
intTieBreaker = 1;
}
else
{
intTieBreaker = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
//Insert if Doesn't Exist
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.USER_EMPLOYEE");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parDataSet.Tables["UserEmployee"].Rows[intRow]["USER_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["UserEmployee"].Rows[intRow]["COMPANY_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["UserEmployee"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(",'" + parDataSet.Tables["UserEmployee"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine("," + intTieBreaker);
strQry.AppendLine(",GETDATE()");
strQry.AppendLine("," + parint64CurrentUserNo + ")");
objAdd[0] = Convert.ToInt32(parDataSet.Tables["UserEmployee"].Rows[intRow]["COMPANY_NO"]);
objAdd[1] = Convert.ToInt32(parDataSet.Tables["UserEmployee"].Rows[intRow]["USER_NO"]);
objAdd[2] = Convert.ToInt32(parDataSet.Tables["UserEmployee"].Rows[intRow]["EMPLOYEE_NO"]);
objAdd[3] = parDataSet.Tables["UserEmployee"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString();
objAdd[4] = intTieBreaker;
DataSet.Tables["UserEmployee"].Rows.Add(objAdd);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
}
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
parDataSet.Dispose();
parDataSet = null;
return(bytCompress);
}
public byte[] Get_Company_Records(Int64 parInt64CompanyNo, string parstrPayrollType)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
DateTime dtStartTaxYear;
object[] objFind = new object[2];
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO ");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",MAX(EMPLOYEE_LAST_RUNDATE) AS PAY_PERIOD_DATE ");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" COMPANY_NO ");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parInt64CompanyNo);
if (DataSet.Tables["Company"].Rows.Count > 0)
{
if (Convert.ToDateTime(DataSet.Tables["Company"].Rows[0]["PAY_PERIOD_DATE"]).Month > 2)
{
dtStartTaxYear = new DateTime(Convert.ToDateTime(DataSet.Tables["Company"].Rows[0]["PAY_PERIOD_DATE"]).Year, 3, 1);
}
else
{
dtStartTaxYear = new DateTime(Convert.ToDateTime(DataSet.Tables["Company"].Rows[0]["PAY_PERIOD_DATE"]).Year - 1, 3, 1);
}
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
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_SURNAME");
strQry.AppendLine(",E.EMPLOYEE_NAME");
strQry.AppendLine(",E.EMPLOYEE_TAX_STARTDATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER 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.EMPLOYEE_NO = EEC.EMPLOYEE_NO");
strQry.AppendLine(" AND EEC.RUN_TYPE = 'T'");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", 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(",DEDUCTION_SUB_ACCOUNT_COUNT");
strQry.AppendLine(",DEDUCTION_DESC");
strQry.AppendLine(",IRP5_CODE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.DEDUCTION ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "DeductionDesc", parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EN.COMPANY_NO");
strQry.AppendLine(",EN.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EN.EARNING_NO");
strQry.AppendLine(",EN.EARNING_DESC");
strQry.AppendLine(",EN.IRP5_CODE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING EN ");
strQry.AppendLine(" INNER 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(" ORDER BY ");
strQry.AppendLine(" EN.COMPANY_NO");
strQry.AppendLine(",EN.EARNING_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EarningDesc", parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TOTAL");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND RUN_TYPE = 'T'");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeeDeduction", parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TOTAL");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND RUN_TYPE = 'T'");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeeEarning", parInt64CompanyNo);
}
DataSet.AcceptChanges();
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public int Insert_New_Record(Int64 parInt64CompanyNo, Int64 parint64CurrentUserNo, byte[] parbyteDataSet)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
int intLeaveNo = -1;
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(LEAVE_SHIFT_NO) AS MAX_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT");
strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables[0].Rows[0]["COMPANY_NO"].ToString());
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", parInt64CompanyNo);
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
{
intLeaveNo = 1;
}
else
{
intLeaveNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
DataSet.Dispose();
DataSet = null;
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",LEAVE_SHIFT_NO");
strQry.AppendLine(",LEAVE_SHIFT_DESC");
strQry.AppendLine(",MIN_VALID_SHIFT_MINUTES");
strQry.AppendLine(",MAX_SHIFTS_YEAR");
strQry.AppendLine(",NORM_PAID_DAYS");
strQry.AppendLine(",SICK_PAID_DAYS");
strQry.AppendLine(",NORM_PAID_PER_PERIOD");
strQry.AppendLine(",SICK_PAID_PER_PERIOD");
strQry.AppendLine(",LEAVE_PAID_ACCUMULATOR_IND");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",LEAVE_SHIFT_DEL_IND)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parDataSet.Tables[0].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables[0].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine("," + intLeaveNo);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables[0].Rows[0]["LEAVE_SHIFT_DESC"].ToString()));
strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["MIN_VALID_SHIFT_MINUTES"].ToString());
strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["MAX_SHIFTS_YEAR"].ToString());
strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["NORM_PAID_DAYS"].ToString());
strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["SICK_PAID_DAYS"].ToString());
strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["NORM_PAID_PER_PERIOD"].ToString());
strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["SICK_PAID_PER_PERIOD"].ToString());
strQry.AppendLine("," + parDataSet.Tables[0].Rows[0]["LEAVE_PAID_ACCUMULATOR_IND"].ToString());
strQry.AppendLine(",GETDATE()");
strQry.AppendLine("," + parint64CurrentUserNo);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables[0].Rows[0]["LEAVE_SHIFT_DEL_IND"].ToString()) + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
parDataSet.Dispose();
parDataSet = null;
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" SET BACKUP_DB_IND = 1");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
return(intLeaveNo);
}
public void Update_Records(Int64 parint64CompanyNo, int parintDeviceNo, byte[] byteCompressedDataSet)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteCompressedDataSet);
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_EMPLOYEE_LINK ");
strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
for (int intRowCount = 0; intRowCount < DataSet.Tables["EmployeeChosen"].Rows.Count; intRowCount++)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_EMPLOYEE_LINK");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parintDeviceNo);
strQry.AppendLine("," + parint64CompanyNo);
strQry.AppendLine("," + DataSet.Tables["EmployeeChosen"].Rows[intRowCount]["EMPLOYEE_NO"].ToString());
strQry.AppendLine("," + DataSet.Tables["EmployeeChosen"].Rows[intRowCount]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["EmployeeChosen"].Rows[intRowCount]["PAY_CATEGORY_TYPE"].ToString()) + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK ");
strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
for (int intRowCount = 0; intRowCount < DataSet.Tables["PayCategoryChosen"].Rows.Count; intRowCount++)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parintDeviceNo);
strQry.AppendLine("," + parint64CompanyNo);
strQry.AppendLine("," + DataSet.Tables["PayCategoryChosen"].Rows[intRowCount]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategoryChosen"].Rows[intRowCount]["PAY_CATEGORY_TYPE"].ToString()) + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
strQry.Clear();
strQry.AppendLine(" DELETE DEL FROM InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK_ACTIVE DEL ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT DISTINCT ");
strQry.AppendLine(" DPCLA.DEVICE_NO");
strQry.AppendLine(",DPCLA.COMPANY_NO");
strQry.AppendLine(",DPCLA.PAY_CATEGORY_NO");
strQry.AppendLine(",DPCLA.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK_ACTIVE DPCLA ");
strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK DPCL");
strQry.AppendLine(" ON DPCL.DEVICE_NO = DPCLA.DEVICE_NO");
strQry.AppendLine(" AND DPCL.COMPANY_NO = DPCLA.COMPANY_NO");
strQry.AppendLine(" AND DPCL.PAY_CATEGORY_NO = DPCLA.PAY_CATEGORY_NO");
strQry.AppendLine(" AND DPCL.PAY_CATEGORY_TYPE = DPCLA.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE DPCLA.DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND DPCLA.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND DPCL.PAY_CATEGORY_NO IS NULL) AS TEMP_TABLE");
strQry.AppendLine(" ON DEL.DEVICE_NO = TEMP_TABLE.DEVICE_NO");
strQry.AppendLine(" AND DEL.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
strQry.AppendLine(" AND DEL.PAY_CATEGORY_NO = TEMP_TABLE.PAY_CATEGORY_NO");
strQry.AppendLine(" AND DEL.PAY_CATEGORY_TYPE = TEMP_TABLE.PAY_CATEGORY_TYPE ");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK ");
strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
for (int intRowCount = 0; intRowCount < DataSet.Tables["DepartmentChosen"].Rows.Count; intRowCount++)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",DEPARTMENT_NO)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parintDeviceNo);
strQry.AppendLine("," + parint64CompanyNo);
strQry.AppendLine("," + DataSet.Tables["DepartmentChosen"].Rows[intRowCount]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["DepartmentChosen"].Rows[intRowCount]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine("," + DataSet.Tables["DepartmentChosen"].Rows[intRowCount]["DEPARTMENT_NO"].ToString() + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
strQry.Clear();
strQry.AppendLine(" DELETE DEL FROM InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK_ACTIVE DEL ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT DISTINCT ");
strQry.AppendLine(" DDLA.DEVICE_NO");
strQry.AppendLine(",DDLA.COMPANY_NO");
strQry.AppendLine(",DDLA.PAY_CATEGORY_NO");
strQry.AppendLine(",DDLA.PAY_CATEGORY_TYPE ");
strQry.AppendLine(",DDLA.DEPARTMENT_NO ");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK_ACTIVE DDLA ");
strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK DDL");
strQry.AppendLine(" ON DDL.DEVICE_NO = DDLA.DEVICE_NO");
strQry.AppendLine(" AND DDL.COMPANY_NO = DDLA.COMPANY_NO");
strQry.AppendLine(" AND DDL.PAY_CATEGORY_NO = DDLA.PAY_CATEGORY_NO");
strQry.AppendLine(" AND DDL.PAY_CATEGORY_TYPE = DDLA.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND DDL.DEPARTMENT_NO = DDLA.DEPARTMENT_NO ");
strQry.AppendLine(" WHERE DDLA.DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND DDLA.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND DDL.DEPARTMENT_NO IS NULL) AS TEMP_TABLE");
strQry.AppendLine(" ON DEL.DEVICE_NO = TEMP_TABLE.DEVICE_NO");
strQry.AppendLine(" AND DEL.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
strQry.AppendLine(" AND DEL.PAY_CATEGORY_NO = TEMP_TABLE.PAY_CATEGORY_NO");
strQry.AppendLine(" AND DEL.PAY_CATEGORY_TYPE = TEMP_TABLE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND DEL.DEPARTMENT_NO = TEMP_TABLE.DEPARTMENT_NO ");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_GROUP_LINK ");
strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
for (int intRowCount = 0; intRowCount < DataSet.Tables["GroupChosen"].Rows.Count; intRowCount++)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_GROUP_LINK");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",GROUP_NO)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parintDeviceNo);
strQry.AppendLine("," + parint64CompanyNo);
strQry.AppendLine("," + DataSet.Tables["GroupChosen"].Rows[intRowCount]["GROUP_NO"].ToString() + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
strQry.Clear();
strQry.AppendLine(" DELETE DEL FROM InteractPayrollClient.dbo.DEVICE_GROUP_LINK_ACTIVE DEL ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT DISTINCT ");
strQry.AppendLine(" DGLA.DEVICE_NO");
strQry.AppendLine(",DGLA.COMPANY_NO");
strQry.AppendLine(",DGLA.GROUP_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE_GROUP_LINK_ACTIVE DGLA ");
strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_GROUP_LINK DGL");
strQry.AppendLine(" ON DGLA.DEVICE_NO = DGL.DEVICE_NO");
strQry.AppendLine(" AND DGLA.COMPANY_NO = DGL.COMPANY_NO");
strQry.AppendLine(" AND DGLA.GROUP_NO = DGL.GROUP_NO");
strQry.AppendLine(" WHERE DGLA.DEVICE_NO = " + parintDeviceNo);
strQry.AppendLine(" AND DGLA.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND DGL.GROUP_NO IS NULL) AS TEMP_TABLE");
strQry.AppendLine(" ON DEL.DEVICE_NO = TEMP_TABLE.DEVICE_NO");
strQry.AppendLine(" AND DEL.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
strQry.AppendLine(" AND DEL.GROUP_NO = TEMP_TABLE.GROUP_NO");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
public byte[] Print_Report(Int64 parint64CompanyNo, string parstrEmployeeIn, string parstrPayCategoryNoIn, string parstrFromDate, Int64 parint64CurrentUserNo, string parstrCurrentUserAccess)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" C.COMPANY_DESC");
strQry.AppendLine(",C.LEAVE_BEGIN_MONTH");
strQry.AppendLine(",CL.DATE_FORMAT");
strQry.AppendLine(",'' AS REPORT_DATETIME");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY C");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.COMPANY_LINK CL");
strQry.AppendLine(" ON C.COMPANY_NO = CL.COMPANY_NO ");
strQry.AppendLine(" WHERE C.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "ReportHeader", parint64CompanyNo);
if (DataSet.Tables["ReportHeader"].Rows.Count > 0)
{
DataSet.Tables["ReportHeader"].Rows[0]["REPORT_DATETIME"] = "Printed " + DateTime.Now.ToString(DataSet.Tables["ReportHeader"].Rows[0]["DATE_FORMAT"].ToString() + " HH:mm");
}
DateTime dtRunDateTime = DateTime.ParseExact(parstrFromDate, "yyyy-MM-dd", null);
DateTime dtBeginFinancialYear = DateTime.Now;
DateTime dtEndFinancialYear = DateTime.Now;
if (dtRunDateTime.Month >= Convert.ToInt32(DataSet.Tables["ReportHeader"].Rows[0]["LEAVE_BEGIN_MONTH"]))
{
dtBeginFinancialYear = new DateTime(dtRunDateTime.Year, Convert.ToInt32(DataSet.Tables["ReportHeader"].Rows[0]["LEAVE_BEGIN_MONTH"]), 1);
}
else
{
dtBeginFinancialYear = new DateTime(dtRunDateTime.Year - 1, Convert.ToInt32(DataSet.Tables["ReportHeader"].Rows[0]["LEAVE_BEGIN_MONTH"]), 1);
}
//Last Day Of Fiscal Year
dtEndFinancialYear = dtBeginFinancialYear.AddYears(1).AddDays(-1);
strQry.Clear();
if (dtRunDateTime.Month == Convert.ToInt32(DataSet.Tables["ReportHeader"].Rows[0]["LEAVE_BEGIN_MONTH"]))
{
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" ETH.PAY_CATEGORY_NO");
strQry.AppendLine(",ETH.EMPLOYEE_NO ");
strQry.AppendLine(",LEAVE_TO_DATE = " + dtBeginFinancialYear.AddDays(-1).ToString("yyyyMMdd"));
strQry.AppendLine(",ETH.TIMESHEET_DATE");
strQry.AppendLine(",LSH.NORM_PAID_PER_PERIOD");
strQry.AppendLine(",LSH.SICK_PAID_PER_PERIOD");
strQry.AppendLine(",LSH.MIN_VALID_SHIFT_MINUTES");
strQry.AppendLine(",SUM(ETH.TIMESHEET_TIME_OUT_MINUTES - ETH.TIMESHEET_TIME_IN_MINUTES) AS DAY_TIMESHEET_ACCUM_MINUTES ");
strQry.AppendLine(",ACCUM_COUNT = ");
strQry.AppendLine(" CASE ");
strQry.AppendLine(" WHEN SUM(ETH.TIMESHEET_TIME_OUT_MINUTES - ETH.TIMESHEET_TIME_IN_MINUTES) > LSH.MIN_VALID_SHIFT_MINUTES");
strQry.AppendLine(" THEN 1");
strQry.AppendLine(" ELSE 0");
strQry.AppendLine(" END");
//2014-04-12
strQry.AppendLine(",ASTERISK_COL = ''");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_HISTORY ETH");
//2013-08-30
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
strQry.AppendLine(" WHERE UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND UEPCT.COMPANY_NO = " + parint64CompanyNo);
if (parstrEmployeeIn != "")
{
strQry.AppendLine(" AND UEPCT.EMPLOYEE_NO IN " + parstrEmployeeIn);
}
if (parstrPayCategoryNoIn != "")
{
strQry.AppendLine(" AND UEPCT.PAY_CATEGORY_NO IN " + parstrPayCategoryNoIn);
}
strQry.AppendLine(" AND UEPCT.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL("W"));
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO) AS TEMP_TABLE");
strQry.AppendLine(" ON ETH.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
strQry.AppendLine(" AND ETH.EMPLOYEE_NO = TEMP_TABLE.EMPLOYEE_NO ");
strQry.AppendLine(" AND ETH.PAY_CATEGORY_NO = TEMP_TABLE.PAY_CATEGORY_NO");
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY EIH");
strQry.AppendLine(" ON ETH.COMPANY_NO = EIH.COMPANY_NO");
strQry.AppendLine(" AND ETH.PAY_PERIOD_DATE = EIH.PAY_PERIOD_DATE");
strQry.AppendLine(" AND ETH.EMPLOYEE_NO = EIH.EMPLOYEE_NO");
if (parstrEmployeeIn != "")
{
strQry.AppendLine(" AND EIH.EMPLOYEE_NO IN " + parstrEmployeeIn);
}
strQry.AppendLine(" AND EIH.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND EIH.RUN_TYPE = 'P'");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT_HISTORY LSH");
strQry.AppendLine(" ON ETH.COMPANY_NO = LSH.COMPANY_NO");
strQry.AppendLine(" AND ETH.PAY_PERIOD_DATE = LSH.PAY_PERIOD_DATE");
strQry.AppendLine(" AND ETH.PAY_CATEGORY_NO = LSH.PAY_CATEGORY_NO");
if (parstrPayCategoryNoIn != "")
{
strQry.AppendLine(" AND LSH.PAY_CATEGORY_NO IN " + parstrPayCategoryNoIn);
}
strQry.AppendLine(" WHERE ETH.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND ETH.PAY_PERIOD_DATE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromDate));
//Previous Fiscal Year
strQry.AppendLine(" AND ETH.TIMESHEET_DATE <= '" + dtBeginFinancialYear.AddDays(-1).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" ETH.PAY_CATEGORY_NO");
strQry.AppendLine(",ETH.EMPLOYEE_NO ");
strQry.AppendLine(",ETH.TIMESHEET_DATE");
strQry.AppendLine(",LSH.NORM_PAID_PER_PERIOD");
strQry.AppendLine(",LSH.SICK_PAID_PER_PERIOD");
strQry.AppendLine(",LSH.MIN_VALID_SHIFT_MINUTES");
strQry.AppendLine(" UNION ");
}
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" ETH.PAY_CATEGORY_NO");
strQry.AppendLine(",ETH.EMPLOYEE_NO ");
//strQry.AppendLine(",LEAVE_TO_DATE = " + dtEndFinancialYear.ToString("yyyyMMdd"));
strQry.AppendLine(",LEAVE_TO_DATE = " + parstrFromDate.Replace("-", ""));
strQry.AppendLine(",ETH.TIMESHEET_DATE");
strQry.AppendLine(",LSH.NORM_PAID_PER_PERIOD");
strQry.AppendLine(",LSH.SICK_PAID_PER_PERIOD");
strQry.AppendLine(",LSH.MIN_VALID_SHIFT_MINUTES");
strQry.AppendLine(",SUM(ETH.TIMESHEET_TIME_OUT_MINUTES - ETH.TIMESHEET_TIME_IN_MINUTES) AS DAY_TIMESHEET_ACCUM_MINUTES ");
strQry.AppendLine(",ACCUM_COUNT = ");
strQry.AppendLine(" CASE ");
strQry.AppendLine(" WHEN SUM(ETH.TIMESHEET_TIME_OUT_MINUTES - ETH.TIMESHEET_TIME_IN_MINUTES) > LSH.MIN_VALID_SHIFT_MINUTES");
strQry.AppendLine(" THEN 1");
strQry.AppendLine(" ELSE 0");
strQry.AppendLine(" END");
//2014-04-12
strQry.AppendLine(",ASTERISK_COL = ''");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_HISTORY ETH");
//2013-08-30
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
strQry.AppendLine(" WHERE UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND UEPCT.COMPANY_NO = " + parint64CompanyNo);
if (parstrEmployeeIn != "")
{
strQry.AppendLine(" AND UEPCT.EMPLOYEE_NO IN " + parstrEmployeeIn);
}
if (parstrPayCategoryNoIn != "")
{
strQry.AppendLine(" AND UEPCT.PAY_CATEGORY_NO IN " + parstrPayCategoryNoIn);
}
strQry.AppendLine(" AND UEPCT.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL("W"));
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO) AS TEMP_TABLE");
strQry.AppendLine(" ON ETH.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
strQry.AppendLine(" AND ETH.EMPLOYEE_NO = TEMP_TABLE.EMPLOYEE_NO ");
strQry.AppendLine(" AND ETH.PAY_CATEGORY_NO = TEMP_TABLE.PAY_CATEGORY_NO");
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY EIH");
strQry.AppendLine(" ON ETH.COMPANY_NO = EIH.COMPANY_NO");
strQry.AppendLine(" AND ETH.PAY_PERIOD_DATE = EIH.PAY_PERIOD_DATE");
strQry.AppendLine(" AND ETH.EMPLOYEE_NO = EIH.EMPLOYEE_NO");
if (parstrEmployeeIn != "")
{
strQry.AppendLine(" AND EIH.EMPLOYEE_NO IN " + parstrEmployeeIn);
}
strQry.AppendLine(" AND EIH.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND EIH.RUN_TYPE = 'P'");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT_HISTORY LSH");
strQry.AppendLine(" ON ETH.COMPANY_NO = LSH.COMPANY_NO");
strQry.AppendLine(" AND ETH.PAY_PERIOD_DATE = LSH.PAY_PERIOD_DATE");
strQry.AppendLine(" AND ETH.PAY_CATEGORY_NO = LSH.PAY_CATEGORY_NO");
if (parstrPayCategoryNoIn != "")
{
strQry.AppendLine(" AND LSH.PAY_CATEGORY_NO IN " + parstrPayCategoryNoIn);
}
strQry.AppendLine(" WHERE ETH.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND ETH.PAY_PERIOD_DATE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromDate));
strQry.AppendLine(" AND ETH.TIMESHEET_DATE >= '" + dtBeginFinancialYear.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" AND ETH.TIMESHEET_DATE <= '" + dtEndFinancialYear.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" ETH.PAY_CATEGORY_NO");
strQry.AppendLine(",ETH.EMPLOYEE_NO ");
strQry.AppendLine(",ETH.TIMESHEET_DATE");
strQry.AppendLine(",LSH.NORM_PAID_PER_PERIOD");
strQry.AppendLine(",LSH.SICK_PAID_PER_PERIOD");
strQry.AppendLine(",LSH.MIN_VALID_SHIFT_MINUTES");
this.clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Report", parint64CompanyNo);
strQry.Clear();
if (dtRunDateTime.Month == Convert.ToInt32(DataSet.Tables["ReportHeader"].Rows[0]["LEAVE_BEGIN_MONTH"]))
{
//Build Dummy Record where does not Exist
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" LH.EMPLOYEE_NO ");
//Go Back 45 Days - Should be enough
strQry.AppendLine(",'" + dtBeginFinancialYear.AddDays(-45).ToString("yyyy-MM-dd") + "' AS LEAVE_FROM_DATE");
strQry.AppendLine(",'" + dtBeginFinancialYear.AddDays(-1).ToString("yyyy-MM-dd") + "' AS LEAVE_TO_DATE");
strQry.AppendLine(",0 AS LEAVE_DAYS_DECIMAL");
strQry.AppendLine(",0 AS NORMAL_LEAVE_ACCUM_DAYS");
strQry.AppendLine(",0 AS SICK_LEAVE_ACCUM_DAYS");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY LH");
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
strQry.AppendLine(" WHERE UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND UEPCT.COMPANY_NO = " + parint64CompanyNo);
if (parstrEmployeeIn != "")
{
strQry.AppendLine(" AND UEPCT.EMPLOYEE_NO IN " + parstrEmployeeIn);
}
if (parstrPayCategoryNoIn != "")
{
strQry.AppendLine(" AND UEPCT.PAY_CATEGORY_NO IN " + parstrPayCategoryNoIn);
}
strQry.AppendLine(" AND UEPCT.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL("W"));
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE) AS TEMP_TABLE");
strQry.AppendLine(" ON LH.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
strQry.AppendLine(" AND LH.EMPLOYEE_NO = TEMP_TABLE.EMPLOYEE_NO ");
strQry.AppendLine(" AND LH.PAY_CATEGORY_TYPE = TEMP_TABLE.PAY_CATEGORY_TYPE");
}
if (parstrPayCategoryNoIn != "")
{
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_HISTORY EPCH");
strQry.AppendLine(" ON LH.COMPANY_NO = EPCH.COMPANY_NO");
strQry.AppendLine(" AND LH.PAY_PERIOD_DATE = EPCH.PAY_PERIOD_DATE");
strQry.AppendLine(" AND LH.EMPLOYEE_NO = EPCH.EMPLOYEE_NO");
strQry.AppendLine(" AND LH.PAY_CATEGORY_TYPE = EPCH.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND EPCH.RUN_TYPE = 'P'");
strQry.AppendLine(" AND EPCH.PAY_CATEGORY_NO IN " + parstrPayCategoryNoIn);
}
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY LH1");
strQry.AppendLine(" ON LH.COMPANY_NO = LH1.COMPANY_NO");
strQry.AppendLine(" AND LH.PAY_PERIOD_DATE = LH1.PAY_PERIOD_DATE");
strQry.AppendLine(" AND LH.EMPLOYEE_NO = LH1.EMPLOYEE_NO");
strQry.AppendLine(" AND LH.EARNING_NO = LH1.EARNING_NO");
strQry.AppendLine(" AND LH.PAY_CATEGORY_TYPE = LH1.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND LH1.LEAVE_TO_DATE = '" + dtBeginFinancialYear.AddDays(-1).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" WHERE LH.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND LH.PAY_PERIOD_DATE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromDate));
strQry.AppendLine(" AND LH.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND LH.EARNING_NO IN (200,201)");
if (parstrEmployeeIn != "")
{
strQry.AppendLine(" AND LH.EMPLOYEE_NO IN " + parstrEmployeeIn);
}
strQry.AppendLine(" AND LH.PROCESS_NO = 98 ");
strQry.AppendLine(" AND LH.LEAVE_FROM_DATE = '" + dtBeginFinancialYear.ToString("yyyy-MM-dd") + "'");
//Record JOIN does NOT Exist
strQry.AppendLine(" AND LH1.COMPANY_NO IS NULL ");
strQry.AppendLine(" UNION ");
}
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" TEMP_TABLE.EMPLOYEE_NO ");
strQry.AppendLine(",TEMP_TABLE.LEAVE_FROM_DATE ");
strQry.AppendLine(",TEMP_TABLE.LEAVE_TO_DATE ");
strQry.AppendLine(",MAX(TEMP_TABLE.LEAVE_DAYS_DECIMAL) AS LEAVE_DAYS_DECIMAL");
strQry.AppendLine(",SUM(TEMP_TABLE.NORMAL_LEAVE_ACCUM_DAYS) AS NORMAL_LEAVE_ACCUM_DAYS");
strQry.AppendLine(",SUM(TEMP_TABLE.SICK_LEAVE_ACCUM_DAYS) AS SICK_LEAVE_ACCUM_DAYS");
strQry.AppendLine(" FROM ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" LH.EMPLOYEE_NO ");
strQry.AppendLine(",LH.LEAVE_FROM_DATE ");
strQry.AppendLine(",LH.LEAVE_TO_DATE ");
strQry.AppendLine(",LH.LEAVE_DAYS_DECIMAL");
strQry.AppendLine(",NORMAL_LEAVE_ACCUM_DAYS = ");
strQry.AppendLine(" CASE ");
strQry.AppendLine(" WHEN LH.EARNING_NO = 200");
strQry.AppendLine(" THEN LH.LEAVE_ACCUM_DAYS");
strQry.AppendLine(" ELSE 0 ");
strQry.AppendLine(" END ");
strQry.AppendLine(",SICK_LEAVE_ACCUM_DAYS = ");
strQry.AppendLine(" CASE ");
strQry.AppendLine(" WHEN LH.EARNING_NO = 201");
strQry.AppendLine(" THEN LH.LEAVE_ACCUM_DAYS");
strQry.AppendLine(" ELSE 0 ");
strQry.AppendLine(" END ");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY LH");
//2013-08-30
if (parstrCurrentUserAccess == "U")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT");
strQry.AppendLine(" WHERE UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND UEPCT.COMPANY_NO = " + parint64CompanyNo);
if (parstrEmployeeIn != "")
{
strQry.AppendLine(" AND UEPCT.EMPLOYEE_NO IN " + parstrEmployeeIn);
}
if (parstrPayCategoryNoIn != "")
{
strQry.AppendLine(" AND UEPCT.PAY_CATEGORY_NO IN " + parstrPayCategoryNoIn);
}
strQry.AppendLine(" AND UEPCT.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL("W"));
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE) AS TEMP_TABLE");
strQry.AppendLine(" ON LH.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
strQry.AppendLine(" AND LH.EMPLOYEE_NO = TEMP_TABLE.EMPLOYEE_NO ");
strQry.AppendLine(" AND LH.PAY_CATEGORY_TYPE = TEMP_TABLE.PAY_CATEGORY_TYPE");
}
if (parstrPayCategoryNoIn != "")
{
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_HISTORY EPCH");
strQry.AppendLine(" ON LH.COMPANY_NO = EPCH.COMPANY_NO");
strQry.AppendLine(" AND LH.PAY_PERIOD_DATE = EPCH.PAY_PERIOD_DATE");
strQry.AppendLine(" AND LH.EMPLOYEE_NO = EPCH.EMPLOYEE_NO");
strQry.AppendLine(" AND LH.PAY_CATEGORY_TYPE = EPCH.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND EPCH.RUN_TYPE = 'P'");
strQry.AppendLine(" AND EPCH.PAY_CATEGORY_NO IN " + parstrPayCategoryNoIn);
}
strQry.AppendLine(" WHERE LH.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND LH.PAY_PERIOD_DATE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromDate));
strQry.AppendLine(" AND LH.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND LH.EARNING_NO IN (200,201)");
if (parstrEmployeeIn != "")
{
strQry.AppendLine(" AND LH.EMPLOYEE_NO IN " + parstrEmployeeIn);
}
strQry.AppendLine(" AND LH.PROCESS_NO = 98) AS TEMP_TABLE");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" TEMP_TABLE.EMPLOYEE_NO ");
strQry.AppendLine(",TEMP_TABLE.LEAVE_FROM_DATE ");
strQry.AppendLine(",TEMP_TABLE.LEAVE_TO_DATE ");
this.clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "ReportLeave", parint64CompanyNo);
//2014-04-12
//Check for Leave Accumulation Exceeding Max Accum Day
Double dblAccumNormalLeave = 0;
bool blnFirstErrorRecord = true;
for (int intRow = 0; intRow < DataSet.Tables["ReportLeave"].Rows.Count; intRow++)
{
dblAccumNormalLeave = 0;
blnFirstErrorRecord = true;
double dblNormLeaveAccum = Math.Round(Convert.ToDouble(DataSet.Tables["ReportLeave"].Rows[intRow]["NORMAL_LEAVE_ACCUM_DAYS"]), 2);
DataView ReportDataView = new DataView(DataSet.Tables["Report"],
"EMPLOYEE_NO = " + DataSet.Tables["ReportLeave"].Rows[intRow]["EMPLOYEE_NO"].ToString() + " AND TIMESHEET_DATE >= '" + Convert.ToDateTime(DataSet.Tables["ReportLeave"].Rows[intRow]["LEAVE_FROM_DATE"]).ToString("yyyy-MM-dd") + "' AND TIMESHEET_DATE <= '" + Convert.ToDateTime(DataSet.Tables["ReportLeave"].Rows[intRow]["LEAVE_TO_DATE"]).ToString("yyyy-MM-dd") + "'",
"",
DataViewRowState.CurrentRows);
for (int intDataViewRow = 0; intDataViewRow < ReportDataView.Count; intDataViewRow++)
{
dblAccumNormalLeave += Math.Round(Convert.ToDouble(ReportDataView[0]["NORM_PAID_PER_PERIOD"]), 3);
double dblAccumNormalLeaveCheck = Math.Round(dblAccumNormalLeave, 2, MidpointRounding.AwayFromZero);
if (dblNormLeaveAccum < dblAccumNormalLeaveCheck)
{
if (blnFirstErrorRecord == true)
{
blnFirstErrorRecord = false;
if (dblNormLeaveAccum < dblAccumNormalLeaveCheck)
{
ReportDataView[intDataViewRow]["ASTERISK_COL"] = "*";
if (dblNormLeaveAccum == 0)
{
ReportDataView[intDataViewRow]["ACCUM_COUNT"] = 0;
}
}
}
else
{
ReportDataView[intDataViewRow]["ACCUM_COUNT"] = 0;
ReportDataView[intDataViewRow]["ASTERISK_COL"] = "*";
}
}
else
{
//
}
}
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Insert_Records(string parstrParm)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
int intTimeMinutes = 0;
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT ");
strQry.AppendLine(" EC.EMPLOYEE_NO");
strQry.AppendLine(" FROM ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS EC ");
strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.EMPLOYEE E ");
strQry.AppendLine(" ON EC.EMPLOYEE_NO = E.EMPLOYEE_3RD_PARTY_CODE ");
//No Link
strQry.AppendLine(" WHERE E.EMPLOYEE_3RD_PARTY_CODE IS NULL");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "LinksMissing");
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" EMPLOYEE_NO");
strQry.AppendLine(",CLOCKING_TIME");
strQry.AppendLine(",CLOCKING_TYPE");
strQry.AppendLine(" FROM ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS ");
strQry.AppendLine(" WHERE ISNULL(USED_IND,0) = 0");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Clockings");
for (int intCount = 0; intCount < DataSet.Tables["Clockings"].Rows.Count; intCount++)
{
strQry.Clear();
intTimeMinutes = (60 * Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).Hour) + Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).Minute;
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_CLOCK_TIME");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIMESHEET_DATE");
strQry.AppendLine(",TIMESHEET_TIME_MINUTES");
strQry.AppendLine(",CLOCKED_BOUNDARY_TIME_MINUTES");
strQry.AppendLine(",IN_OUT_IND)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" 0 ");
strQry.AppendLine(",EPC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
//TIMESHEET_DATE
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd")));
//TIMESHEET_TIME_MINUTES
strQry.AppendLine("," + intTimeMinutes);
//CLOCKED_BOUNDARY_TIME_MINUTES Same as TIMESHEET_TIME_MINUTES
strQry.AppendLine("," + intTimeMinutes);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString()));
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_CLOCK_TIME DCT");
strQry.AppendLine(" ON DCT.DEVICE_NO = 0");
strQry.AppendLine(" AND DCT.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND DCT.EMPLOYEE_NO = EPC.EMPLOYEE_NO");
strQry.AppendLine(" AND DCT.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO");
strQry.AppendLine(" AND DCT.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND DCT.TIMESHEET_DATE = " + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd")));
strQry.AppendLine(" AND DCT.TIMESHEET_TIME_MINUTES = " + intTimeMinutes);
strQry.AppendLine(" AND DCT.IN_OUT_IND = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString()));
strQry.AppendLine(" WHERE E.EMPLOYEE_3RD_PARTY_CODE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["EMPLOYEE_NO"].ToString()));
//No Record Exists
strQry.AppendLine(" AND DCT.COMPANY_NO IS NULL");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" UPDATE ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS ");
strQry.AppendLine(" SET USED_IND = 1");
strQry.AppendLine(" WHERE EMPLOYEE_NO = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["EMPLOYEE_NO"].ToString()));
strQry.AppendLine(" AND CLOCKING_TIME = " + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd HH:mm:ss.fff")));
strQry.AppendLine(" AND CLOCKING_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public void Cleanup_Client_DataBase_Files(byte[] parByteArrayDataSet)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
DataSet parClientDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parByteArrayDataSet);
StringBuilder strQry = new StringBuilder();
for (int intRow = 0; intRow < parClientDataSet.Tables["FileToDelete"].Rows.Count; intRow++)
{
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_NAME"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_NAME"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
}
public byte[] Update_User_Menus(string parstrProgramFromInd, Int64 parint64CurrentUserNo, byte[] parbyteDataSet)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
int intTieBreaker = 0;
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
Int64 int64UserNo = -1;
DateTime DateTimeNew = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second);
for (int intRow = 0; intRow < parDataSet.Tables[0].Rows.Count; intRow++)
{
if (parDataSet.Tables[0].Rows[intRow].RowState == System.Data.DataRowState.Added)
{
int64UserNo = Convert.ToInt64(parDataSet.Tables[0].Rows[intRow]["USER_NO"]);
if (DataSet.Tables["Temp"] != null)
{
DataSet.Tables.Remove("Temp");
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(TIE_BREAKER) AS MAX_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_MENU");
strQry.AppendLine(" WHERE FROM_PROGRAM_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parstrProgramFromInd));
strQry.AppendLine(" AND USER_NO = " + parDataSet.Tables[0].Rows[intRow]["USER_NO"].ToString());
strQry.AppendLine(" AND COMPANY_NO = " + parDataSet.Tables[0].Rows[intRow]["COMPANY_NO"].ToString());
strQry.AppendLine(" AND MENU_ITEM_ID = " + this.clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables[0].Rows[intRow]["MENU_ITEM_ID"].ToString()));
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", -1);
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
{
intTieBreaker = 1;
}
else
{
intTieBreaker = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.USER_MENU ");
strQry.AppendLine("(FROM_PROGRAM_IND");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",USER_NO");
strQry.AppendLine(",MENU_ITEM_ID");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",ACCESS_IND");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + clsDBConnectionObjects.Text2DynamicSQL(parstrProgramFromInd));
strQry.AppendLine("," + parDataSet.Tables[0].Rows[intRow]["COMPANY_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables[0].Rows[intRow]["USER_NO"].ToString());
strQry.AppendLine("," + this.clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables[0].Rows[intRow]["MENU_ITEM_ID"].ToString()));
strQry.AppendLine("," + intTieBreaker);
strQry.AppendLine("," + this.clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables[0].Rows[intRow]["ACCESS_IND"].ToString()));
strQry.AppendLine(",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'");
strQry.AppendLine("," + parint64CurrentUserNo + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
else
{
if (parDataSet.Tables[0].Rows[intRow].RowState == System.Data.DataRowState.Deleted)
{
int64UserNo = Convert.ToInt64(parDataSet.Tables[0].Rows[intRow]["USER_NO", DataRowVersion.Original]);
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.USER_MENU ");
strQry.AppendLine(" SET ");
strQry.AppendLine(" USER_NO_RECORD = " + parint64CurrentUserNo);
strQry.AppendLine(",DATETIME_DELETE_RECORD = GETDATE() ");
strQry.AppendLine(" WHERE FROM_PROGRAM_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parstrProgramFromInd));
strQry.AppendLine(" AND USER_NO = " + parDataSet.Tables[0].Rows[intRow]["USER_NO", DataRowVersion.Original].ToString());
strQry.AppendLine(" AND COMPANY_NO = " + parDataSet.Tables[0].Rows[intRow]["COMPANY_NO", DataRowVersion.Original].ToString());
strQry.AppendLine(" AND MENU_ITEM_ID = " + parDataSet.Tables[0].Rows[intRow]["MENU_ITEM_ID", DataRowVersion.Original].ToString());
strQry.AppendLine(" AND TIE_BREAKER = " + parDataSet.Tables[0].Rows[intRow]["TIE_BREAKER", DataRowVersion.Original].ToString());
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
}
}
byte[] bytCompress = Get_User_Menus(parstrProgramFromInd, int64UserNo);
parDataSet.Dispose();
parDataSet = null;
return(bytCompress);
}
public int Update_Structure(string parstrUpdateType, int parintLevelNo, int parintLevel1No, int parintLevel2No, int parintLevel3No, int parintLevel4No, string parstrNodeDesc, string parstrNodeDetailDesc)
{
DataSet DataSet = new DataSet();
string strQry = "";
int intKey = -1;
if (parstrUpdateType == "A")
{
if (parintLevelNo > 1)
{
//Delete Link Where Node Has No Chilren
strQry = "";
strQry += " DELETE FROM InteractPayroll.dbo.HELP_FILE";
strQry += " WHERE HELP_LEVEL1_NO = " + parintLevel1No;
if (parintLevelNo == 2)
{
strQry += " AND HELP_LEVEL2_NO = 0 ";
}
else
{
if (parintLevelNo == 3)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No;
strQry += " AND HELP_LEVEL3_NO = 0 ";
}
else
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No;
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No;
strQry += " AND HELP_LEVEL4_NO = 0 ";
}
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
}
strQry = "";
strQry += " SELECT ";
strQry += " MAX(HELP_LEVEL" + parintLevelNo.ToString() + "_NO) AS MAX_NO";
strQry += " FROM InteractPayroll.dbo.HELP_LEVEL" + parintLevelNo.ToString();
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "Temp", -1);
if (DataSet.Tables["Temp"].Rows[0]["MAX_NO"] == System.DBNull.Value)
{
intKey = 1;
}
else
{
intKey = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
strQry = "";
strQry += " INSERT INTO InteractPayroll.dbo.HELP_FILE";
strQry += "(HELP_LEVEL1_NO";
strQry += ",HELP_LEVEL2_NO";
strQry += ",HELP_LEVEL3_NO";
strQry += ",HELP_LEVEL4_NO)";
strQry += " VALUES ";
if (parintLevelNo == 1)
{
strQry += "(" + intKey.ToString();
}
else
{
strQry += "(" + parintLevel1No.ToString();
}
if (parintLevelNo == 2)
{
strQry += "," + intKey.ToString();
}
else
{
strQry += "," + parintLevel2No.ToString();
}
if (parintLevelNo == 3)
{
strQry += "," + intKey.ToString();
}
else
{
strQry += "," + parintLevel3No.ToString();
}
if (parintLevelNo == 4)
{
strQry += "," + intKey.ToString() + ")";
}
else
{
strQry += "," + parintLevel4No.ToString() + ")";
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
strQry = "";
strQry += " INSERT INTO InteractPayroll.dbo.HELP_LEVEL" + parintLevelNo.ToString();
strQry += "(HELP_LEVEL" + parintLevelNo.ToString() + "_NO";
strQry += ",HELP_LEVEL" + parintLevelNo.ToString() + "_DESC)";
strQry += " VALUES ";
strQry += "(" + intKey.ToString();
strQry += "," + clsDBConnectionObjects.Text2DynamicSQL(parstrNodeDesc) + ")";
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
if (parstrNodeDetailDesc != "")
{
strQry = "";
strQry += " INSERT INTO InteractPayroll.dbo.HELP_FILE_TEXT";
strQry += "(HELP_LEVEL1_NO";
strQry += ",HELP_LEVEL2_NO";
strQry += ",HELP_LEVEL3_NO";
strQry += ",HELP_LEVEL4_NO";
strQry += ",HELP_TEXT)";
strQry += " VALUES ";
if (parintLevelNo == 1)
{
strQry += "(" + intKey.ToString();
}
else
{
strQry += "(" + parintLevel1No.ToString();
}
if (parintLevelNo == 2)
{
strQry += "," + intKey.ToString();
}
else
{
strQry += "," + parintLevel2No.ToString();
}
if (parintLevelNo == 3)
{
strQry += "," + intKey.ToString();
}
else
{
strQry += "," + parintLevel3No.ToString();
}
if (parintLevelNo == 4)
{
strQry += "," + intKey.ToString();
}
else
{
strQry += "," + parintLevel4No.ToString();
}
strQry += "," + clsDBConnectionObjects.Text2DynamicSQL(parstrNodeDetailDesc) + ")";
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
}
}
else
{
if (parstrUpdateType == "U")
{
strQry = "";
strQry += " UPDATE InteractPayroll.dbo.HELP_LEVEL" + parintLevelNo.ToString();
strQry += " SET HELP_LEVEL" + parintLevelNo.ToString() + "_DESC = " + clsDBConnectionObjects.Text2DynamicSQL(parstrNodeDesc);
strQry += " WHERE HELP_LEVEL" + parintLevelNo.ToString() + "_NO = ";
if (parintLevelNo == 1)
{
strQry += parintLevel1No;
}
else
{
if (parintLevelNo == 2)
{
strQry += parintLevel2No;
}
else
{
if (parintLevelNo == 3)
{
strQry += parintLevel3No;
}
else
{
strQry += parintLevel3No;
}
}
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
strQry = "";
strQry += " DELETE FROM InteractPayroll.dbo.HELP_FILE_TEXT";
strQry += " WHERE HELP_LEVEL1_NO = " + parintLevel1No;
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No;
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No;
strQry += " AND HELP_LEVEL4_NO = " + parintLevel4No;
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
if (parstrNodeDetailDesc != "")
{
strQry = "";
strQry += " INSERT INTO InteractPayroll.dbo.HELP_FILE_TEXT";
strQry += "(HELP_LEVEL1_NO";
strQry += ",HELP_LEVEL2_NO";
strQry += ",HELP_LEVEL3_NO";
strQry += ",HELP_LEVEL4_NO";
strQry += ",HELP_TEXT)";
strQry += " VALUES ";
strQry += "(" + parintLevel1No.ToString();
strQry += "," + parintLevel2No.ToString();
strQry += "," + parintLevel3No.ToString();
strQry += "," + parintLevel4No.ToString();
strQry += "," + clsDBConnectionObjects.Text2DynamicSQL(parstrNodeDetailDesc) + ")";
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
}
}
else
{
strQry = "";
strQry += " SELECT ";
strQry += " HELP_LEVEL1_NO";
strQry += ",HELP_LEVEL2_NO";
strQry += ",HELP_LEVEL3_NO";
strQry += ",HELP_LEVEL4_NO";
strQry += " FROM InteractPayroll.dbo.HELP_FILE";
strQry += " WHERE HELP_LEVEL1_NO = " + parintLevel1No;
if (parintLevelNo >= 2)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
}
if (parintLevelNo >= 3)
{
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No.ToString();
}
if (parintLevelNo >= 4)
{
strQry += " AND HELP_LEVEL4_NO = " + parintLevel4No.ToString();
}
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "Temp", -1);
for (int intRow = 0; intRow < DataSet.Tables["Temp"].Rows.Count; intRow++)
{
if (intRow == 0
& parintLevelNo > 1)
{
//Go Up A level To see if There Is Only 1 Record - Then Update
strQry = "";
strQry += " SELECT ";
strQry += " HELP_LEVEL1_NO";
strQry += " FROM InteractPayroll.dbo.HELP_FILE";
strQry += " WHERE HELP_LEVEL1_NO = " + parintLevel1No;
if (parintLevelNo == 3)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
}
else
{
if (parintLevelNo == 4)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No.ToString();
}
}
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "Test", -1);
if (DataSet.Tables["Test"].Rows.Count == 1)
{
strQry = " UPDATE InteractPayroll.dbo.HELP_FILE";
strQry += " SET ";
if (parintLevelNo == 4)
{
strQry += " HELP_LEVEL4_NO = 0 ";
}
else
{
if (parintLevelNo == 3)
{
strQry += " HELP_LEVEL3_NO = 0 ";
strQry += ",HELP_LEVEL4_NO = 0 ";
}
else
{
if (parintLevelNo == 2)
{
strQry += " HELP_LEVEL2_NO = 0 ";
strQry += ",HELP_LEVEL3_NO = 0 ";
strQry += ",HELP_LEVEL4_NO = 0 ";
}
}
}
strQry += " WHERE HELP_LEVEL1_NO = " + DataSet.Tables["Temp"].Rows[intRow]["HELP_LEVEL1_NO"].ToString();
strQry += " AND HELP_LEVEL2_NO = " + DataSet.Tables["Temp"].Rows[intRow]["HELP_LEVEL2_NO"].ToString();
strQry += " AND HELP_LEVEL3_NO = " + DataSet.Tables["Temp"].Rows[intRow]["HELP_LEVEL3_NO"].ToString();
strQry += " AND HELP_LEVEL3_NO = " + DataSet.Tables["Temp"].Rows[intRow]["HELP_LEVEL4_NO"].ToString();
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
}
}
if (parintLevelNo == 1)
{
strQry = "";
strQry += " DELETE FROM InteractPayroll.dbo.HELP_LEVEL1";
strQry += " WHERE HELP_LEVEL1_NO = " + DataSet.Tables["Temp"].Rows[intRow]["HELP_LEVEL1_NO"].ToString();
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
}
if (parintLevelNo <= 2)
{
strQry = "";
strQry += " DELETE FROM InteractPayroll.dbo.HELP_LEVEL2";
strQry += " WHERE HELP_LEVEL2_NO = " + DataSet.Tables["Temp"].Rows[intRow]["HELP_LEVEL2_NO"].ToString();
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
}
if (parintLevelNo <= 3)
{
strQry = "";
strQry += " DELETE FROM InteractPayroll.dbo.HELP_LEVEL3";
strQry += " WHERE HELP_LEVEL3_NO = " + DataSet.Tables["Temp"].Rows[intRow]["HELP_LEVEL3_NO"].ToString();
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
}
if (parintLevelNo <= 4)
{
strQry = "";
strQry += " DELETE FROM InteractPayroll.dbo.HELP_LEVEL4";
strQry += " WHERE HELP_LEVEL4_NO = " + DataSet.Tables["Temp"].Rows[intRow]["HELP_LEVEL4_NO"].ToString();
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
}
}
strQry = "";
strQry += " DELETE FROM InteractPayroll.dbo.HELP_FILE";
strQry += " WHERE HELP_LEVEL1_NO = " + parintLevel1No.ToString();
if (parintLevelNo == 2)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
}
else
{
if (parintLevelNo == 3)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No.ToString();
}
else
{
if (parintLevelNo == 4)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No.ToString();
strQry += " AND HELP_LEVEL4_NO = " + parintLevel4No.ToString();
}
}
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
strQry = "";
strQry += " DELETE FROM InteractPayroll.dbo.HELP_FILE_TEXT";
strQry += " WHERE HELP_LEVEL1_NO = " + parintLevel1No.ToString();
if (parintLevelNo == 2)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
}
else
{
if (parintLevelNo == 3)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No.ToString();
}
else
{
if (parintLevelNo == 4)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No.ToString();
strQry += " AND HELP_LEVEL4_NO = " + parintLevel4No.ToString();
}
}
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
strQry = "";
strQry += " DELETE FROM InteractPayroll.dbo.HELP_FILE_GRAPHIC_CHUNK";
strQry += " WHERE HELP_LEVEL1_NO = " + parintLevel1No.ToString();
if (parintLevelNo == 2)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
}
else
{
if (parintLevelNo == 3)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No.ToString();
}
else
{
if (parintLevelNo == 4)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No.ToString();
strQry += " AND HELP_LEVEL4_NO = " + parintLevel4No.ToString();
}
}
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
strQry = "";
strQry += " DELETE FROM InteractPayroll.dbo.HELP_FILE_GRAPHIC_DETAIL";
strQry += " WHERE HELP_LEVEL1_NO = " + parintLevel1No.ToString();
if (parintLevelNo == 2)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
}
else
{
if (parintLevelNo == 3)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No.ToString();
}
else
{
if (parintLevelNo == 4)
{
strQry += " AND HELP_LEVEL2_NO = " + parintLevel2No.ToString();
strQry += " AND HELP_LEVEL3_NO = " + parintLevel3No.ToString();
strQry += " AND HELP_LEVEL4_NO = " + parintLevel4No.ToString();
}
}
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
}
}
return(intKey);
}
public byte[] Logon_User(string parstrUserInformation)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
bool blnDebug = false;
StringBuilder strQry = new StringBuilder();
string[] strPieces = parstrUserInformation.Split('|');
string strUserId = strPieces[0];
string strPassword = strPieces[1];
byte[] bytCompress;
StreamWriter swStreamWriter;
DataSet DataSet = new DataSet();
DataTable DataTable = new DataTable("ReturnValues");
DataTable.Columns.Add("USER_NO", typeof(Int64));
DataTable.Columns.Add("RESET", typeof(String));
DataTable.Columns.Add("ACCESS_IND", typeof(String));
DataTable.Columns.Add("REBOOT_IND", typeof(String));
DataTable.Columns.Add("MACHINE_NAME", typeof(String));
DataTable.Columns.Add("MACHINE_IP", typeof(String));
DataTable.Columns.Add("NO_USERS_IND", typeof(String));
DataTable.Columns.Add("DOWNLOAD_IND", typeof(String));
DataTable.Columns.Add("DB_CREATE_FOR_ENGINE", typeof(String));
DataSet.Tables.Add(DataTable);
DataRow myDataRow = DataSet.Tables["ReturnValues"].NewRow();
myDataRow["USER_NO"] = -1;
myDataRow["RESET"] = "N";
myDataRow["ACCESS_IND"] = "";
DataSet.Tables["ReturnValues"].Rows.Add(myDataRow);
FileInfo fiFile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "Debug.txt");
if (fiFile.Exists == true)
{
blnDebug = true;
}
fiFile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "busTimeAttendanceLogonLogs.txt");
if (fiFile.Exists == true)
{
File.Delete(AppDomain.CurrentDomain.BaseDirectory + "busTimeAttendanceLogonLogs.txt");
}
if (blnDebug == true)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("Call clsDBConnectionObjects.GetSQLConnectionString");
swStreamWriter.Close();
}
DataSet.Tables["ReturnValues"].Rows[0]["DB_CREATE_FOR_ENGINE"] = clsDBConnectionObjects.GetSQLConnectionString();
if (blnDebug == true)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("After clsDBConnectionObjects.GetSQLConnectionString = " + DataSet.Tables["ReturnValues"].Rows[0]["DB_CREATE_FOR_ENGINE"].ToString());
swStreamWriter.Close();
}
if (clsDBConnectionObjects.GetSQLConnectionString() != "")
{
clsFixInteractPayrollClientDatabase clsFixInteractPayrollClientDatabase = new clsFixInteractPayrollClientDatabase();
clsFixInteractPayrollClientDatabase.Fix_Client_Database();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" UI.USER_NO");
strQry.AppendLine(",UI.SYSTEM_ADMINISTRATOR_IND");
strQry.AppendLine(",UI.RESET");
//A = Administrator, U = User
strQry.AppendLine(",MIN(UC.COMPANY_ACCESS_IND) AS MIN_COMPANY_ACCESS_IND");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID UI");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UC");
strQry.AppendLine(" ON UI.USER_NO = UC.USER_NO");
strQry.AppendLine(" WHERE UI.USER_ID = " + clsDBConnectionObjects.Text2DynamicSQL(strUserId));
strQry.AppendLine(" AND UI.PASSWORD = "******" GROUP BY ");
strQry.AppendLine(" UI.USER_NO");
strQry.AppendLine(",UI.SYSTEM_ADMINISTRATOR_IND");
strQry.AppendLine(",UI.RESET");
try
{
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
}
catch (Exception ex)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("Exception = " + ex.Message);
swStreamWriter.Close();
}
if (blnDebug == true)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("After Temp = Count = " + DataSet.Tables["Temp"].Rows.Count.ToString());
swStreamWriter.Close();
}
if (DataSet.Tables["Temp"].Rows.Count == 0)
{
DataSet.Tables.Remove(DataSet.Tables["Temp"]);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" USER_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "UserTemp");
if (DataSet.Tables["UserTemp"].Rows.Count > 0)
{
DataSet.Tables.Remove(DataSet.Tables["UserTemp"]);
if ((strUserId == "INTERACT" &&
strPassword == "TCARETNI") ||
(strUserId == "HELENALR" &&
strPassword == "MONSTER"))
{
DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = 0;
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = "S";
}
else
{
DataSet.AcceptChanges();
//Not Found
bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
}
else
{
DataSet.Tables.Remove(DataSet.Tables["UserTemp"]);
if ((strUserId == "INTERACT" &&
strPassword == "TCARETNI") ||
(strUserId == "HELENALR" &&
strPassword == "MONSTER"))
{
DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = 0;
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = "S";
}
else
{
DataSet.Tables["ReturnValues"].Rows[0]["NO_USERS_IND"] = "Y";
DataSet.AcceptChanges();
bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
}
}
else
{
if (DataSet.Tables["Temp"].Rows[0]["SYSTEM_ADMINISTRATOR_IND"].ToString() == "S")
{
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = DataSet.Tables["Temp"].Rows[0]["SYSTEM_ADMINISTRATOR_IND"].ToString();
}
else
{
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = DataSet.Tables["Temp"].Rows[0]["MIN_COMPANY_ACCESS_IND"].ToString();
}
if (DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"].ToString() != "S")
{
//Create Temp Table for Joins
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP");
strQry.AppendLine(" WHERE USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP ");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(",USER_TABLE.COMPANY_NO");
strQry.AppendLine(",USER_TABLE.EMPLOYEE_NO");
strQry.AppendLine(",USER_TABLE.PAY_CATEGORY_NO");
strQry.AppendLine(",USER_TABLE.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_PAY_CATEGORY_DEPARTMENT UPCD");
strQry.AppendLine(" ON E.COMPANY_NO = UPCD.COMPANY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UPCD.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UPCD.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND E.DEPARTMENT_NO = UPCD.DEPARTMENT_NO ");
strQry.AppendLine(" AND UPCD.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL");
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_EMPLOYEE UE");
strQry.AppendLine(" ON E.COMPANY_NO = UE.COMPANY_NO");
strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UE.EMPLOYEE_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND UE.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL");
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_PAY_CATEGORY UPC");
strQry.AppendLine(" ON E.COMPANY_NO = UPC.COMPANY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND UPC.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL ");
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON UCA.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.COMPANY_NO = UCA.COMPANY_NO");
//2013-07-10
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A'");
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL) AS USER_TABLE");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = Convert.ToInt64(DataSet.Tables["Temp"].Rows[0]["USER_NO"]);
DataSet.Tables["ReturnValues"].Rows[0]["RESET"] = DataSet.Tables["Temp"].Rows[0]["RESET"].ToString();
//Set Time Logged On
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayrollClient.dbo.USER_ID");
strQry.AppendLine(" SET LAST_TIME_ON = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "'");
strQry.AppendLine(" WHERE USER_NO = " + Convert.ToInt64(DataSet.Tables["Temp"].Rows[0]["USER_NO"]));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
//2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS_TEMP");
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FCDD.FILE_LAYER_IND");
strQry.AppendLine(",FCDD.FILE_NAME");
strQry.AppendLine(",FCDD.FILE_SIZE");
strQry.AppendLine(",FCDD.FILE_SIZE_COMPRESSED");
strQry.AppendLine(",FCDD.FILE_LAST_UPDATED_DATE");
strQry.AppendLine(",FCDD.FILE_CRC_VALUE");
strQry.AppendLine(",ISNULL(MAX(FCDC.FILE_CHUNK_NO),0) AS MAX_FILE_CHUNK_NO");
strQry.AppendLine(",'' AS DOWNLOAD_IND");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS FCDD");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS FCDC");
strQry.AppendLine(" ON FCDD.FILE_LAYER_IND = FCDC.FILE_LAYER_IND");
strQry.AppendLine(" AND FCDD.FILE_NAME = FCDC.FILE_NAME");
strQry.AppendLine(" WHERE FCDD.FILE_LAYER_IND = 'P'");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" FCDD.FILE_LAYER_IND");
strQry.AppendLine(",FCDD.FILE_NAME");
strQry.AppendLine(",FCDD.FILE_SIZE");
strQry.AppendLine(",FCDD.FILE_SIZE_COMPRESSED");
strQry.AppendLine(",FCDD.FILE_LAST_UPDATED_DATE");
strQry.AppendLine(",FCDD.FILE_CRC_VALUE");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" 2");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Files");
bytCompress = (byte[])Check_Reboot_Server();
DataSet DataSetTemp = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytCompress);
if (DataSetTemp.Tables["ReturnValues"].Rows[0]["REBOOT_IND"].ToString() == "Y")
{
DataSet.Tables["ReturnValues"].Rows[0]["REBOOT_IND"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["REBOOT_IND"].ToString();
DataSet.Tables["ReturnValues"].Rows[0]["MACHINE_NAME"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["MACHINE_NAME"].ToString();
DataSet.Tables["ReturnValues"].Rows[0]["MACHINE_IP"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["MACHINE_IP"].ToString();
}
}
bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public void Convert_Client_Records(Int64 parint64CompanyNo, string parstrToPayCategoryType, byte[] parbytCompressDataSet)
{
DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbytCompressDataSet);
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" A.TABLE_NAME");
strQry.AppendLine(" FROM InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS A ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS B");
strQry.AppendLine(" ON B.TABLE_NAME = A.TABLE_NAME ");
strQry.AppendLine(" AND B.COLUMN_NAME = 'PAY_CATEGORY_NO' ");
strQry.AppendLine(" WHERE A.COLUMN_NAME = 'PAY_CATEGORY_TYPE'");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "TablesToFix");
for (int intRow = 0; intRow < DataSet.Tables["PayCategory"].Rows.Count; intRow++)
{
strQry.Clear();
strQry.AppendLine(" UPDATE UE ");
strQry.AppendLine(" SET UE.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_EMPLOYEE UE ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON UE.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND UE.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND UE.PAY_CATEGORY_TYPE = JOIN_TABLE.PAY_CATEGORY_TYPE");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" UPDATE E ");
strQry.AppendLine(" SET E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON E.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = JOIN_TABLE.PAY_CATEGORY_TYPE");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//Break
strQry.Clear();
if (parstrToPayCategoryType == "W")
{
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT ");
}
else
{
if (parstrToPayCategoryType == "S")
{
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT ");
}
else
{
//Time Attend
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT ");
}
}
strQry.AppendLine("(COMPANY_NO ");
strQry.AppendLine(",EMPLOYEE_NO ");
strQry.AppendLine(",PAY_CATEGORY_NO ");
strQry.AppendLine(",BREAK_DATE ");
strQry.AppendLine(",BREAK_SEQ ");
strQry.AppendLine(",BREAK_TIME_IN_MINUTES ");
strQry.AppendLine(",BREAK_TIME_OUT_MINUTES ");
strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES ");
strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES) ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" A.COMPANY_NO ");
strQry.AppendLine(",A.EMPLOYEE_NO ");
strQry.AppendLine(",A.PAY_CATEGORY_NO ");
strQry.AppendLine(",A.BREAK_DATE ");
strQry.AppendLine(",A.BREAK_SEQ ");
strQry.AppendLine(",A.BREAK_TIME_IN_MINUTES ");
strQry.AppendLine(",A.BREAK_TIME_OUT_MINUTES ");
strQry.AppendLine(",A.CLOCKED_TIME_IN_MINUTES ");
strQry.AppendLine(",A.CLOCKED_TIME_OUT_MINUTES ");
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT A");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT A");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT A");
}
}
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE A ");
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT A ");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT A ");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT A ");
}
}
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//Timesheets
strQry.Clear();
if (parstrToPayCategoryType == "W")
{
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT ");
}
else
{
if (parstrToPayCategoryType == "S")
{
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT ");
}
else
{
//Time Attend
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT ");
}
}
strQry.AppendLine("(COMPANY_NO ");
strQry.AppendLine(",EMPLOYEE_NO ");
strQry.AppendLine(",PAY_CATEGORY_NO ");
strQry.AppendLine(",TIMESHEET_DATE ");
strQry.AppendLine(",TIMESHEET_SEQ ");
strQry.AppendLine(",TIMESHEET_TIME_IN_MINUTES ");
strQry.AppendLine(",TIMESHEET_TIME_OUT_MINUTES ");
strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES ");
strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES) ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" A.COMPANY_NO ");
strQry.AppendLine(",A.EMPLOYEE_NO ");
strQry.AppendLine(",A.PAY_CATEGORY_NO ");
strQry.AppendLine(",A.TIMESHEET_DATE ");
strQry.AppendLine(",A.TIMESHEET_SEQ ");
strQry.AppendLine(",A.TIMESHEET_TIME_IN_MINUTES ");
strQry.AppendLine(",A.TIMESHEET_TIME_OUT_MINUTES ");
strQry.AppendLine(",A.CLOCKED_TIME_IN_MINUTES ");
strQry.AppendLine(",A.CLOCKED_TIME_OUT_MINUTES ");
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT A ");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT A ");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT A ");
}
}
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE A ");
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT A ");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT A ");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT A ");
}
}
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
for (int intTableRow = 0; intTableRow < DataSet.Tables["TablesToFix"].Rows.Count; intTableRow++)
{
strQry.Clear();
strQry.AppendLine(" UPDATE A ");
strQry.AppendLine(" SET A.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayrollClient.dbo." + DataSet.Tables["TablesToFix"].Rows[intTableRow]["TABLE_NAME"].ToString() + " A ");
strQry.AppendLine(" WHERE A.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND A.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND A.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
}
}
public byte[] Get_Local_Current_Download_Files(byte[] parbyteDataSet)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
StringBuilder strQry = new StringBuilder();
DataSet ClientDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
DataView ClientDataSetDataview = new DataView(ClientDataSet.Tables["Files"]
, ""
, "FILE_LAYER_IND,FILE_NAME"
, DataViewRowState.CurrentRows);
int intFindRow = -1;
object[] objFind = new object[2];
DataSet DataSet = new DataSet();
strQry.Append(clsDBConnectionObjects.Get_Local_Client_Download_SQL());
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Files");
for (int intRow = 0; intRow < DataSet.Tables["Files"].Rows.Count; intRow++)
{
objFind[0] = DataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString();
objFind[1] = DataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString();
intFindRow = ClientDataSetDataview.Find(objFind);
if (intFindRow == -1)
{
//DELETE Records on InteractPayrollClient IF Not in Internet Download DataSet
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
DataSet.Tables["Files"].Rows.RemoveAt(intRow);
intRow -= 1;
}
}
DataSet.AcceptChanges();
DataView DataSetDataview = new DataView(DataSet.Tables["Files"]
, ""
, "FILE_LAYER_IND,FILE_NAME"
, DataViewRowState.CurrentRows);
//Go Through Internet Download to see If Any Files Must be Downloaded
for (int intRow = 0; intRow < ClientDataSet.Tables["Files"].Rows.Count; intRow++)
{
objFind[0] = ClientDataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString();
objFind[1] = ClientDataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString();
intFindRow = DataSetDataview.Find(objFind);
if (intFindRow > -1)
{
if (Convert.ToDateTime(ClientDataSet.Tables["Files"].Rows[intRow]["FILE_LAST_UPDATED_DATE"]) == Convert.ToDateTime(DataSetDataview[intFindRow]["FILE_LAST_UPDATED_DATE"]))
{
ClientDataSet.Tables["Files"].Rows.RemoveAt(intRow);
intRow -= 1;
}
}
}
ClientDataSet.AcceptChanges();
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(ClientDataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public int Insert_New_Record(Int64 parint64CurrentUserNo, byte[] parbyteDataSet)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
int intDeductionNo = -1;
Int64 Int64CompanyNo = Convert.ToInt64(parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"]);
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(DEDUCTION_NO) AS MAX_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.DEDUCTION");
strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"].ToString());
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", Int64CompanyNo);
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
{
intDeductionNo = 200;
}
else
{
if (Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) < 199)
{
intDeductionNo = 200;
}
else
{
intDeductionNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
}
for (int intRow = 0; intRow < parDataSet.Tables["EarningPercentage"].Rows.Count; intRow++)
{
parDataSet.Tables["EarningPercentage"].Rows[intRow]["DEDUCTION_NO"] = intDeductionNo;
}
DataSet.Dispose();
DataSet = null;
for (int intRow = 1; intRow <= Convert.ToInt32(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_SUB_ACCOUNT_COUNT"]); intRow++)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.DEDUCTION");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",DEDUCTION_DESC");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_COUNT");
strQry.AppendLine(",DEDUCTION_LOAN_TYPE_IND");
strQry.AppendLine(",DEDUCTION_REPORT_HEADER1");
strQry.AppendLine(",DEDUCTION_REPORT_HEADER2");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",DEDUCTION_DEL_IND)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine("," + intDeductionNo);
strQry.AppendLine("," + intRow);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_DESC"].ToString()));
strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_SUB_ACCOUNT_COUNT"]));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_LOAN_TYPE_IND"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_REPORT_HEADER1"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_REPORT_HEADER2"].ToString()));
strQry.AppendLine("," + parint64CurrentUserNo);
strQry.AppendLine(",GETDATE()");
strQry.AppendLine(",'Y')");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);
strQry.Clear();
//2017-02-24 Insert Record for opposite PAY_CATEGORY_TYPE
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.DEDUCTION");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",DEDUCTION_DESC");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_COUNT");
strQry.AppendLine(",DEDUCTION_LOAN_TYPE_IND");
strQry.AppendLine(",DEDUCTION_REPORT_HEADER1");
strQry.AppendLine(",DEDUCTION_REPORT_HEADER2");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",DEDUCTION_DEL_IND)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"].ToString());
if (parDataSet.Tables["Deduction"].Rows[0]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(",'S'");
}
else
{
strQry.AppendLine(",'W'");
}
strQry.AppendLine("," + intDeductionNo);
strQry.AppendLine("," + intRow);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_DESC"].ToString()));
strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_SUB_ACCOUNT_COUNT"]));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_LOAN_TYPE_IND"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_REPORT_HEADER1"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_REPORT_HEADER2"].ToString()));
strQry.AppendLine("," + parint64CurrentUserNo);
strQry.AppendLine(",GETDATE()");
strQry.AppendLine(",'Y')");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",DEDUCTION_TYPE_IND");
strQry.AppendLine(",DEDUCTION_VALUE");
strQry.AppendLine(",DEDUCTION_PERIOD_IND");
strQry.AppendLine(",DEDUCTION_DAY_VALUE");
strQry.AppendLine(",DEDUCTION_MIN_VALUE");
strQry.AppendLine(",DEDUCTION_MAX_VALUE");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(",0");
strQry.AppendLine("," + intDeductionNo);
strQry.AppendLine("," + intRow);
strQry.AppendLine(",1");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_TYPE_IND"].ToString()));
strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_VALUE"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_PERIOD_IND"].ToString()));
strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_DAY_VALUE"].ToString());
strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_MIN_VALUE"].ToString());
strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_MAX_VALUE"].ToString());
strQry.AppendLine(",GETDATE()");
strQry.AppendLine("," + parint64CurrentUserNo + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);
strQry.Clear();
//2017-02-24 Insert Record for opposite PAY_CATEGORY_TYPE
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",DEDUCTION_TYPE_IND");
strQry.AppendLine(",DEDUCTION_VALUE");
strQry.AppendLine(",DEDUCTION_PERIOD_IND");
strQry.AppendLine(",DEDUCTION_DAY_VALUE");
strQry.AppendLine(",DEDUCTION_MIN_VALUE");
strQry.AppendLine(",DEDUCTION_MAX_VALUE");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parDataSet.Tables["Deduction"].Rows[0]["COMPANY_NO"].ToString());
if (parDataSet.Tables["Deduction"].Rows[0]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(",'S'");
}
else
{
strQry.AppendLine(",'W'");
}
strQry.AppendLine(",0");
strQry.AppendLine("," + intDeductionNo);
strQry.AppendLine("," + intRow);
strQry.AppendLine(",1");
strQry.AppendLine(",'U'");
strQry.AppendLine(",0");
strQry.AppendLine(",'E'");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",GETDATE()");
strQry.AppendLine("," + parint64CurrentUserNo + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);
}
if (parDataSet.Tables["EarningPercentage"].Rows.Count > 0)
{
Save_Deduction_Earning_Link(Int64CompanyNo, parint64CurrentUserNo, parDataSet.Tables["EarningPercentage"], Convert.ToInt32(parDataSet.Tables["Deduction"].Rows[0]["DEDUCTION_SUB_ACCOUNT_COUNT"]));
}
parDataSet.Dispose();
parDataSet = null;
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" SET BACKUP_DB_IND = 1");
strQry.AppendLine(" WHERE COMPANY_NO = " + Int64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
return(intDeductionNo);
}
public bool Upload_File(Int64 parint64CurrentUserNo, string dtFileUploadDateTime, int intBlockNumber, string parstrFileName, byte[] parbytesCompressed,
string dtFileLastUpdated, int intFileSize, int intCompressedSize, string strVersionNumber, bool blnComplete, string strFileCRC32Value,
string parstrUsers)
{
string[] strUser = parstrUsers.Split(',');
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
bool blnUploadSuccessful = false;
if (intBlockNumber == 1)
{
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll.dbo.FILE_UPLOAD_CHUNKS_TEMP");
strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo.ToString());
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString().ToString(), -1);
}
if (blnComplete == true)
{
string strCRC32Value = "";
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FILE_CHUNK_NO ");
strQry.AppendLine(",FILE_CHUNK");
strQry.AppendLine(" FROM InteractPayroll.dbo.FILE_UPLOAD_CHUNKS_TEMP");
strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFileName));
strQry.AppendLine(" AND UPLOAD_DATETIME = '" + dtFileUploadDateTime + "'");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" FILE_CHUNK_NO ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "FileCRC", -1);
byte[] bytBytes = new byte[intCompressedSize];
byte[] bytTempBytes;
long pvtlngDestinationFileStartIndex = 0;
for (int intRow = 0; intRow < DataSet.Tables["FileCRC"].Rows.Count; intRow++)
{
bytTempBytes = (byte[])DataSet.Tables["FileCRC"].Rows[intRow]["FILE_CHUNK"];
Array.Copy(bytTempBytes, 0, bytBytes, pvtlngDestinationFileStartIndex, bytTempBytes.Length);
pvtlngDestinationFileStartIndex += bytTempBytes.Length;
}
//Add Last Block To Byte Array
Array.Copy(parbytesCompressed, 0, bytBytes, pvtlngDestinationFileStartIndex, parbytesCompressed.Length);
byte[] pvtbytDecompressedBytes = new byte[intFileSize];
//Open Memory Stream with Compressed Data
MemoryStream msMemoryStream = new MemoryStream(bytBytes);
System.IO.Compression.GZipStream GZipStreamDecompress = new GZipStream(msMemoryStream, CompressionMode.Decompress);
//Decompress Bytes
BinaryReader pvtbrBinaryReader = new BinaryReader(GZipStreamDecompress);
pvtbytDecompressedBytes = pvtbrBinaryReader.ReadBytes(Convert.ToInt32(intFileSize));
//CRC32 Value
strCRC32Value = "";
foreach (byte b in clsCrc32.ComputeHash(pvtbytDecompressedBytes))
{
strCRC32Value += b.ToString("x2").ToLower();
}
if (strCRC32Value == strFileCRC32Value)
{
//Move Chunks to Master
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.FILE_UPLOAD_CHUNKS ");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",FILE_NAME");
strQry.AppendLine(",UPLOAD_DATETIME");
strQry.AppendLine(",FILE_CHUNK_NO");
strQry.AppendLine(",FILE_CHUNK)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" USER_NO");
strQry.AppendLine(",FILE_NAME");
strQry.AppendLine(",UPLOAD_DATETIME");
strQry.AppendLine(",FILE_CHUNK_NO");
strQry.AppendLine(",FILE_CHUNK");
strQry.AppendLine(" FROM InteractPayroll.dbo.FILE_UPLOAD_CHUNKS_TEMP");
strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFileName));
strQry.AppendLine(" AND UPLOAD_DATETIME = '" + dtFileUploadDateTime + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
//Delete Temp Chunks
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll.dbo.FILE_UPLOAD_CHUNKS_TEMP");
strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFileName));
strQry.AppendLine(" AND UPLOAD_DATETIME = '" + dtFileUploadDateTime + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
strQry.Clear();
//Insert Last Block
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.FILE_UPLOAD_CHUNKS");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",FILE_NAME");
strQry.AppendLine(",UPLOAD_DATETIME");
strQry.AppendLine(",FILE_CHUNK_NO");
strQry.AppendLine(",FILE_CHUNK)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parint64CurrentUserNo.ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrFileName));
strQry.AppendLine(",'" + dtFileUploadDateTime + "'");
strQry.AppendLine("," + intBlockNumber.ToString());
strQry.AppendLine(",@FILE_CHUNK)");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parbytesCompressed, "@FILE_CHUNK");
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.FILE_UPLOAD_DETAILS");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",FILE_NAME");
strQry.AppendLine(",UPLOAD_DATETIME");
strQry.AppendLine(",FILE_LAST_UPDATED_DATE");
strQry.AppendLine(",FILE_SIZE");
strQry.AppendLine(",FILE_SIZE_COMPRESSED");
strQry.AppendLine(",FILE_VERSION_NO");
strQry.AppendLine(",FILE_CRC_VALUE)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parint64CurrentUserNo.ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrFileName));
strQry.AppendLine(",'" + dtFileUploadDateTime + "'");
strQry.AppendLine(",'" + dtFileLastUpdated + "'");
strQry.AppendLine("," + intFileSize);
strQry.AppendLine("," + intCompressedSize);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(strVersionNumber));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(strFileCRC32Value) + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
for (int intUserCount = 0; intUserCount < strUser.Length; intUserCount++)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.FILE_UPLOAD_DETAILS_FOR_USERS");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",FILE_NAME");
strQry.AppendLine(",UPLOAD_DATETIME");
strQry.AppendLine(",FOR_USER_NO)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parint64CurrentUserNo.ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrFileName));
strQry.AppendLine(",'" + dtFileUploadDateTime + "'");
strQry.AppendLine("," + strUser[intUserCount].ToString() + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
blnUploadSuccessful = true;
}
}
else
{
strQry.Clear();
strQry.AppendLine("INSERT INTO InteractPayroll.dbo.FILE_UPLOAD_CHUNKS_TEMP ");
strQry.AppendLine("(USER_NO ");
strQry.AppendLine(",FILE_NAME");
strQry.AppendLine(",UPLOAD_DATETIME");
strQry.AppendLine(",FILE_CHUNK_NO");
strQry.AppendLine(",FILE_CHUNK)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parint64CurrentUserNo.ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrFileName));
strQry.AppendLine(",'" + dtFileUploadDateTime + "'");
strQry.AppendLine("," + intBlockNumber.ToString());
strQry.AppendLine(",@FILE_CHUNK)");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parbytesCompressed, "@FILE_CHUNK");
blnUploadSuccessful = true;
}
return(blnUploadSuccessful);
}
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_PayCategory_Records(Int64 parint64CompanyNo, string parstrPayrollType)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" EPC.PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" ON E.COMPANY_NO = PC.COMPANY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = PC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = PC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo.ToString());
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "PayCategory");
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Update_Employee(Int64 parInt64CompanyNo, string parstrPayrollType, string parstrFromPayrollType, int parintEmployeeNo, int parintLeaveShiftNo, byte[] parbyteDataSet)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
//Remove Current EMPLOYEE_PAY_CATEGORY Link
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY ");
strQry.AppendLine(" SET DATETIME_DELETE_RECORD = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "'");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
//Relink LEAVE_SHIFT_NO
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE ");
strQry.AppendLine(" SET ");
strQry.AppendLine(" PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(",LEAVE_SHIFT_NO = " + parintLeaveShiftNo);
if (parstrPayrollType == "S")
{
strQry.AppendLine(",EMPLOYEE_NUMBER_CHEQUES = 12 ");
}
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" AND EMPLOYEE_ENDDATE IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
for (int intRow = 0; intRow < parDataSet.Tables["PayCategory"].Rows.Count; intRow++)
{
if (DataSet.Tables["Temp"] != null)
{
DataSet.Tables.Remove("Temp");
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" ISNULL(MAX(TIE_BREAKER),0) + 1 AS MAX_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parDataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",HOURLY_RATE");
strQry.AppendLine(",DEFAULT_IND");
strQry.AppendLine(",LEAVE_DAY_RATE_DECIMAL");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(parInt64CompanyNo.ToString());
strQry.AppendLine("," + parintEmployeeNo);
strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"]));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine("," + Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]));
strQry.AppendLine("," + Convert.ToDouble(parDataSet.Tables["PayCategory"].Rows[intRow]["REC_RATE"]));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["PayCategory"].Rows[intRow]["DEFAULT_IND"].ToString()));
strQry.AppendLine(",LEAVE_DAY_RATE_DECIMAL = ");
strQry.AppendLine(" CASE ");
strQry.AppendLine(" WHEN PC.PAY_CATEGORY_TYPE = 'W' THEN");
strQry.AppendLine(" ROUND(SUM(PCTD.TIME_DECIMAL) / COUNT(*),2)");
strQry.AppendLine(" ELSE ROUND(PC.SALARY_MINUTES_PAID_PER_DAY / 60,2)");
strQry.AppendLine(" END ");
strQry.AppendLine(",GETDATE()");
//Current User
strQry.AppendLine(",0");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_TIME_DECIMAL PCTD");
strQry.AppendLine(" ON PC.COMPANY_NO = PCTD.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = PCTD.PAY_CATEGORY_NO ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + parDataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND PC.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" PC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(",PC.SALARY_MINUTES_PAID_PER_DAY");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EARNING ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",EARNING_DESC");
strQry.AppendLine(",SPREAD_SHEET_TYPE_IND");
strQry.AppendLine(",LEAVE_PERCENTAGE");
strQry.AppendLine(",EARNING_REPORT_HEADER1");
strQry.AppendLine(",EARNING_REPORT_HEADER2");
strQry.AppendLine(",IRP5_CODE");
strQry.AppendLine(",TAX_PERCENTAGE");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_RECORD");
strQry.AppendLine(",DATETIME_DELETE_RECORD");
strQry.AppendLine(",EARNING_DEL_IND)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EARNING_NO");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(",E.TIE_BREAKER");
strQry.AppendLine(",E.EARNING_DESC");
strQry.AppendLine(",E.SPREAD_SHEET_TYPE_IND");
strQry.AppendLine(",E.LEAVE_PERCENTAGE");
strQry.AppendLine(",E.EARNING_REPORT_HEADER1");
strQry.AppendLine(",E.EARNING_REPORT_HEADER2");
strQry.AppendLine(",E.IRP5_CODE");
strQry.AppendLine(",E.TAX_PERCENTAGE");
strQry.AppendLine(",E.USER_NO_NEW_RECORD");
strQry.AppendLine(",E.DATETIME_NEW_RECORD");
strQry.AppendLine(",E.USER_NO_RECORD");
strQry.AppendLine(",E.DATETIME_DELETE_RECORD");
strQry.AppendLine(",E.EARNING_DEL_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING E");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");
strQry.AppendLine(" ON E.COMPANY_NO = EE.COMPANY_NO ");
strQry.AppendLine(" AND EE.EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND E.EARNING_NO = EE.EARNING_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EARNING EARN");
strQry.AppendLine(" ON E.COMPANY_NO = EARN.COMPANY_NO ");
strQry.AppendLine(" AND E.EARNING_DESC = EARN.EARNING_DESC ");
strQry.AppendLine(" AND EARN.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
//Not Defaults and Not Leave
strQry.AppendLine(" AND E.EARNING_NO > 9");
strQry.AppendLine(" AND E.EARNING_NO < 200");
//Record Does NOT Exist
strQry.AppendLine(" AND EARN.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE EE ");
strQry.AppendLine(" SET ");
strQry.AppendLine(" PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");
strQry.AppendLine(" WHERE EE.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EE.EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.DEDUCTION ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",DEDUCTION_DESC");
strQry.AppendLine(",IRP5_CODE");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_COUNT");
strQry.AppendLine(",DEDUCTION_REPORT_HEADER1");
strQry.AppendLine(",DEDUCTION_REPORT_HEADER2");
strQry.AppendLine(",DEDUCTION_LOAN_TYPE_IND");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_RECORD");
strQry.AppendLine(",DATETIME_DELETE_RECORD)");
strQry.AppendLine(" SELECT DISTINCT ");
strQry.AppendLine(" D.COMPANY_NO");
strQry.AppendLine(",D.DEDUCTION_NO");
strQry.AppendLine(",D.DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
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.USER_NO_NEW_RECORD");
strQry.AppendLine(",D.DATETIME_NEW_RECORD");
strQry.AppendLine(",D.USER_NO_RECORD");
strQry.AppendLine(",D.DATETIME_DELETE_RECORD");
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 ED.EMPLOYEE_NO = " + parintEmployeeNo);
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 D.PAY_CATEGORY_TYPE = ED.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
strQry.AppendLine(" ON D.COMPANY_NO = DED.COMPANY_NO ");
strQry.AppendLine(" AND D.DEDUCTION_NO = DED.DEDUCTION_NO ");
strQry.AppendLine(" AND DED.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE D.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND D.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND D.DATETIME_DELETE_RECORD IS NULL");
//Record Does NOT Exist
strQry.AppendLine(" AND DED.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",DEDUCTION_TYPE_IND");
strQry.AppendLine(",DEDUCTION_VALUE");
strQry.AppendLine(",DEDUCTION_MIN_VALUE");
strQry.AppendLine(",DEDUCTION_MAX_VALUE");
strQry.AppendLine(",DEDUCTION_PERIOD_IND");
strQry.AppendLine(",DEDUCTION_DAY_VALUE");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_RECORD");
strQry.AppendLine(",DATETIME_DELETE_RECORD)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" ED.COMPANY_NO");
strQry.AppendLine(",ED.EMPLOYEE_NO");
strQry.AppendLine(",ED.DEDUCTION_NO");
strQry.AppendLine(",ED.DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(",ED.TIE_BREAKER");
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.USER_NO_NEW_RECORD");
strQry.AppendLine(",ED.DATETIME_NEW_RECORD");
strQry.AppendLine(",ED.USER_NO_RECORD");
strQry.AppendLine(",ED.DATETIME_DELETE_RECORD");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED_Compare");
strQry.AppendLine(" ON ED.COMPANY_NO = ED_Compare.COMPANY_NO");
strQry.AppendLine(" AND ED.EMPLOYEE_NO = ED_Compare.EMPLOYEE_NO");
strQry.AppendLine(" AND ED.DEDUCTION_NO = ED_Compare.DEDUCTION_NO");
strQry.AppendLine(" AND ED.DEDUCTION_SUB_ACCOUNT_NO = ED_Compare.DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(" AND ED_Compare.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE ED.COMPANY_NO = " + parInt64CompanyNo);
//Default
strQry.AppendLine(" AND ED.EMPLOYEE_NO = 0");
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");
//Record Does NOT Exist
strQry.AppendLine(" AND ED_Compare.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE ED ");
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
strQry.AppendLine(" ON ED.COMPANY_NO = DED.COMPANY_NO ");
strQry.AppendLine(" AND ED.DEDUCTION_NO = DED.DEDUCTION_NO ");
strQry.AppendLine(" AND ED.DEDUCTION_SUB_ACCOUNT_NO = DED.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = DED.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE ED.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND ED.EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE EDEP ");
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE EDEP ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EARNING EARN");
strQry.AppendLine(" ON EDEP.COMPANY_NO = EARN.COMPANY_NO ");
strQry.AppendLine(" AND EDEP.EARNING_NO = EARN.EARNING_NO ");
strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = EARN.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
strQry.AppendLine(" ON EDEP.COMPANY_NO = DED.COMPANY_NO ");
strQry.AppendLine(" AND EDEP.DEDUCTION_NO = DED.DEDUCTION_NO ");
strQry.AppendLine(" AND EDEP.DEDUCTION_SUB_ACCOUNT_NO = DED.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = DED.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE EDEP.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EDEP.EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND EDEP.DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
if (parstrPayrollType == "W")
{
strQry.Clear();
strQry.AppendLine(" UPDATE EDEP ");
//2=Normal Time
strQry.AppendLine(" SET EARNING_NO = 2 ");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE EDEP ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE EDEP_Check ");
strQry.AppendLine(" ON EDEP.COMPANY_NO = EDEP_Check.COMPANY_NO ");
strQry.AppendLine(" AND EDEP.EMPLOYEE_NO = EDEP_Check.EMPLOYEE_NO ");
strQry.AppendLine(" AND EDEP_Check.EARNING_NO = 2 ");
strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = EDEP_Check.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE EDEP.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EDEP.EMPLOYEE_NO = " + parintEmployeeNo);
//PAY_CATEGORY_TYPE already Changed
strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
//1=Income
strQry.AppendLine(" AND EDEP.EARNING_NO = 1 ");
strQry.AppendLine(" AND EDEP.DATETIME_DELETE_RECORD IS NULL");
//Records do NOT already Exist
strQry.AppendLine(" AND EDEP_Check.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
else
{
if (parstrPayrollType == "S")
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_RECORD");
strQry.AppendLine(",DATETIME_DELETE_RECORD)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
//Income
strQry.AppendLine(",1");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_RECORD");
strQry.AppendLine(",DATETIME_DELETE_RECORD");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
//PAY_CATEGORY_TYPE already Changed
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
//2=Normal Time
strQry.AppendLine(" AND EARNING_NO = 2 ");
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
}
strQry.Clear();
strQry.AppendLine(" UPDATE L ");
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LOANS L ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
strQry.AppendLine(" ON L.COMPANY_NO = DED.COMPANY_NO ");
strQry.AppendLine(" AND L.DEDUCTION_NO = DED.DEDUCTION_NO ");
strQry.AppendLine(" AND L.DEDUCTION_SUB_ACCOUNT_NO = DED.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE = DED.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE L.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND L.EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND L.DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
if (parstrPayrollType == "W")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT ");
}
else
{
if (parstrPayrollType == "S")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT ");
}
else
{
if (parstrPayrollType == "T")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT ");
}
}
}
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",TIMESHEET_DATE");
strQry.AppendLine(",TIMESHEET_SEQ");
strQry.AppendLine(",TIMESHEET_TIME_IN_MINUTES");
strQry.AppendLine(",TIMESHEET_TIME_OUT_MINUTES");
strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES");
strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES");
strQry.AppendLine(",INDICATOR");
strQry.AppendLine(",TIMESHEET_ACCUM_MINUTES");
strQry.AppendLine(",USER_NO_TIME_IN");
strQry.AppendLine(",USER_NO_TIME_OUT)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EBC.COMPANY_NO");
strQry.AppendLine(",EBC.EMPLOYEE_NO");
//New PAY_CATEGORY_NO
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EBC.TIMESHEET_DATE");
strQry.AppendLine(",EBC.TIMESHEET_SEQ");
strQry.AppendLine(",EBC.TIMESHEET_TIME_IN_MINUTES");
strQry.AppendLine(",EBC.TIMESHEET_TIME_OUT_MINUTES");
strQry.AppendLine(",EBC.CLOCKED_TIME_IN_MINUTES");
strQry.AppendLine(",EBC.CLOCKED_TIME_OUT_MINUTES");
strQry.AppendLine(",EBC.INDICATOR");
strQry.AppendLine(",EBC.TIMESHEET_ACCUM_MINUTES");
strQry.AppendLine(",EBC.USER_NO_TIME_IN");
strQry.AppendLine(",EBC.USER_NO_TIME_OUT");
if (parstrFromPayrollType == "W")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT EBC ");
}
else
{
if (parstrFromPayrollType == "S")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT EBC ");
}
else
{
if (parstrFromPayrollType == "T")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT EBC ");
}
}
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON EBC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND EBC.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
//New EMPLOYEE_PAY_CATEGORY
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE EBC.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EBC.EMPLOYEE_NO = " + parintEmployeeNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
if (parstrFromPayrollType == "W")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT ");
}
else
{
if (parstrFromPayrollType == "S")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT ");
}
else
{
if (parstrFromPayrollType == "T")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT ");
}
}
}
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
if (parstrPayrollType == "W")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT ");
}
else
{
if (parstrPayrollType == "S")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT ");
}
else
{
if (parstrPayrollType == "T")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT ");
}
}
}
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",BREAK_DATE");
strQry.AppendLine(",BREAK_SEQ");
strQry.AppendLine(",BREAK_TIME_IN_MINUTES");
strQry.AppendLine(",BREAK_TIME_OUT_MINUTES");
strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES");
strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES");
strQry.AppendLine(",INDICATOR");
strQry.AppendLine(",BREAK_ACCUM_MINUTES");
strQry.AppendLine(",USER_NO_TIME_IN");
strQry.AppendLine(",USER_NO_TIME_OUT)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EBC.COMPANY_NO");
strQry.AppendLine(",EBC.EMPLOYEE_NO");
//New PAY_CATEGORY_NO
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EBC.BREAK_DATE");
strQry.AppendLine(",EBC.BREAK_SEQ");
strQry.AppendLine(",EBC.BREAK_TIME_IN_MINUTES");
strQry.AppendLine(",EBC.BREAK_TIME_OUT_MINUTES");
strQry.AppendLine(",EBC.CLOCKED_TIME_IN_MINUTES");
strQry.AppendLine(",EBC.CLOCKED_TIME_OUT_MINUTES");
strQry.AppendLine(",EBC.INDICATOR");
strQry.AppendLine(",EBC.BREAK_ACCUM_MINUTES");
strQry.AppendLine(",EBC.USER_NO_TIME_IN");
strQry.AppendLine(",EBC.USER_NO_TIME_OUT");
if (parstrFromPayrollType == "W")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT EBC ");
}
else
{
if (parstrFromPayrollType == "S")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT EBC ");
}
else
{
if (parstrFromPayrollType == "T")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT EBC ");
}
}
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON EBC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND EBC.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
//New EMPLOYEE_PAY_CATEGORY
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE EBC.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EBC.EMPLOYEE_NO = " + parintEmployeeNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
if (parstrFromPayrollType == "W")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT ");
}
else
{
if (parstrFromPayrollType == "S")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT ");
}
else
{
if (parstrFromPayrollType == "T")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT ");
}
}
}
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT ");
//1=Income
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
//PAY_CATEGORY_TYPE already Changed
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
//Batch Processing Earning
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_BATCH_TEMP ");
//1=Income
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
//PAY_CATEGORY_TYPE already Changed
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
//Batch Processing Deduction
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_BATCH_TEMP ");
//1=Income
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
//PAY_CATEGORY_TYPE already Changed
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
Get_Employee_DataSet(DataSet, parInt64CompanyNo, parintEmployeeNo);
Get_CurrentPayCategory_DataSet(DataSet, parInt64CompanyNo, parintEmployeeNo);
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" SET BACKUP_DB_IND = 1");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}