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_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 void Update_User_Last_Company_No(Int64 parint64CurrentUserNo, Int64 parInt64CompanyNo)
{
string strQry = "";
strQry = "";
strQry += " UPDATE InteractPayroll.dbo.USER_ID ";
strQry += " SET LAST_COMPANY_NO = " + parInt64CompanyNo;
strQry += " WHERE USER_NO = " + parint64CurrentUserNo;
clsDBConnectionObjects.Execute_SQLCommand(strQry, parInt64CompanyNo);
}
public byte[] Insert_Eti_Records(Int64 parInt64CompanyNo, string parstrCurrentUserAccess, Int64 parint64CurrentUserNo, string parstrDateTime, byte[] parbyteDataSet)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
for (int intRow = 0; intRow < parDataSet.Tables["EtiEmployeeSave"].Rows.Count; intRow++)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_ETI");
strQry.AppendLine("(ETI_RUN_DATE");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",ETI_MONTH");
strQry.AppendLine(",TOTAL_HOURS");
strQry.AppendLine(",FACTOR");
strQry.AppendLine(",TOTAL_EARNINGS");
strQry.AppendLine(",ETI_EARNINGS");
strQry.AppendLine(",ETI_VALUE)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("('" + parstrDateTime + "'");
strQry.AppendLine("," + parInt64CompanyNo);
strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["ETI_MONTH"].ToString());
strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["TOTAL_HOURS"].ToString());
strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["FACTOR"].ToString());
strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["TOTAL_EARNINGS"].ToString());
strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["ETI_EARNINGS"].ToString());
strQry.AppendLine("," + parDataSet.Tables["EtiEmployeeSave"].Rows[intRow]["ETI_VALUE"].ToString() + ")");
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_Form_Records(parInt64CompanyNo, parstrCurrentUserAccess, parint64CurrentUserNo);
return(bytCompress);
}
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 Update_Employee_Link(Int64 parint64CompanyNo, Int64 parint64CurrentUserNo, string parstrMenuId, byte[] bytCompressedDataSet)
{
DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytCompressedDataSet);
StringBuilder strQry = new StringBuilder();
string strTableName = "";
if (parstrMenuId == "3E")
{
strTableName = "DEPARTMENT";
}
else
{
strTableName = "OCCUPATION";
}
for (int intRow = 0; intRow < DataSet.Tables["Employee"].Rows.Count; intRow++)
{
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE");
strQry.AppendLine(" SET " + strTableName + "_NO = " + DataSet.Tables["Employee"].Rows[intRow][strTableName + "_NO"].ToString());
strQry.AppendLine(",USER_NO_RECORD = " + parint64CurrentUserNo);
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + DataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND DATETIME_DELETE_RECORD 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);
}
public void Insert_Dates(string parstrType)
{
string strQry;
DateTime dtDateTimeStart;
DateTime dtDateTimeEnd;
if (parstrType == "N")
{
dtDateTimeStart = new DateTime(DateTime.Now.Year, 03, 01);
dtDateTimeEnd = new DateTime(DateTime.Now.Year + 1, 3, 1).AddDays(-1);
}
else
{
int intYear = DateTime.Now.Year;
if (DateTime.Now.Month < 3)
{
intYear = intYear - 1;
}
dtDateTimeStart = new DateTime(intYear, 3, 1);
dtDateTimeEnd = new DateTime(intYear + 1, 3, 1).AddDays(-1);
}
while (true)
{
strQry = "";
strQry += " INSERT INTO InteractPayroll.dbo.DATES";
strQry += "(DAY_DATE";
strQry += ",DAY_NO)";
strQry += " VALUES ";
strQry += "('" + dtDateTimeStart.ToString("yyyy-MM-dd") + "'";
strQry += "," + Convert.ToInt32(dtDateTimeStart.DayOfWeek) + ")";
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
if (dtDateTimeStart.Year == dtDateTimeEnd.Year
& dtDateTimeStart.Month == dtDateTimeEnd.Month
& dtDateTimeStart.Day == dtDateTimeEnd.Day)
{
break;
}
dtDateTimeStart = dtDateTimeStart.AddDays(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 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 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);
}
public byte[] Insert_New_Record(Int64 parint64CurrentUserNo, byte[] parbyteDataSet)
{
StringBuilder strQry = new StringBuilder();
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
DataSet DataSet = new DataSet();
//Reset Value
parDataSet.Tables["User"].Rows[0]["RETURN_CODE"] = "";
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" USER_ID");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID");
strQry.AppendLine(" WHERE USER_ID = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["USER_ID"].ToString()));
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "UserId", -1);
if (DataSet.Tables["UserId"].Rows.Count > 0)
{
parDataSet.Tables["User"].Rows[0]["RETURN_CODE"] = 9999;
goto Insert_New_Record_Continue;
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" USER_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID");
strQry.AppendLine(" WHERE USER_CLOCK_PIN = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["USER_CLOCK_PIN"].ToString()));
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), parDataSet, "UserPinCheck", -1);
if (parDataSet.Tables["UserPinCheck"].Rows.Count > 0)
{
goto Insert_New_Record_Continue;
}
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" MAX(USER_NO) AS MAX_USER_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", -1);
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_USER_NO") == true)
{
parDataSet.Tables["User"].Rows[0]["USER_NO"] = 1;
}
else
{
parDataSet.Tables["User"].Rows[0]["USER_NO"] = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_USER_NO"]) + 1;
}
System.Random RandomNumber = new System.Random();
parDataSet.Tables["User"].Rows[0]["PASSWORD"] = RandomNumber.Next(1000, 999999).ToString();
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.USER_ID ");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",USER_ID");
strQry.AppendLine(",FIRSTNAME");
strQry.AppendLine(",SURNAME");
strQry.AppendLine(",PASSWORD");
strQry.AppendLine(",RESET");
strQry.AppendLine(",LAST_TIME_ON");
strQry.AppendLine(",EMAIL");
//2017-05-09
strQry.AppendLine(",USER_CLOCK_PIN");
strQry.AppendLine(",SYSTEM_ADMINISTRATOR_IND");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parDataSet.Tables["User"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["USER_ID"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["FIRSTNAME"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["SURNAME"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["PASSWORD"].ToString()));
strQry.AppendLine(",'Y'");
strQry.AppendLine(",Null");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["EMAIL"].ToString()));
//2017-05-09
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["User"].Rows[0]["USER_CLOCK_PIN"].ToString()));
strQry.AppendLine(",'N'");
strQry.AppendLine(",GETDATE()");
strQry.AppendLine("," + parint64CurrentUserNo + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
for (int intRow = 0; intRow < parDataSet.Tables["CompanyAccess"].Rows.Count; intRow++)
{
parDataSet.Tables["CompanyAccess"].Rows[intRow]["USER_NO"] = parDataSet.Tables["User"].Rows[0]["USER_NO"].ToString();
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.USER_COMPANY_ACCESS ");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",COMPANY_ACCESS_IND");
strQry.AppendLine(",ACCESS_LAYER_IND");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parDataSet.Tables["User"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["CompanyAccess"].Rows[intRow]["COMPANY_NO"].ToString());
strQry.AppendLine(",1");
//No Link to An Employee (Used For User Number = Employee Number)
strQry.AppendLine(",-1");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["CompanyAccess"].Rows[intRow]["COMPANY_ACCESS_IND"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["CompanyAccess"].Rows[intRow]["ACCESS_LAYER_IND"].ToString()));
strQry.AppendLine(",GETDATE()");
strQry.AppendLine("," + parint64CurrentUserNo + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
if (parDataSet.Tables["UserFingerTemplate"].Rows.Count > 0)
{
Save_User_FingerTemplate(parDataSet.Tables["UserFingerTemplate"], Convert.ToInt64(parDataSet.Tables["User"].Rows[0]["USER_NO"]));
}
Insert_New_Record_Continue:
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(parDataSet);
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);
int intLeaveTypeNo;
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" MAX(EARNING_NO) AS MAX_LEAVE_TYPE_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parDataSet.Tables["LeaveType"].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine(" AND EARNING_NO > 199");
DataSet DataSet = new DataSet();
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", parInt64CompanyNo);
if (DataSet.Tables[0].Rows[0].IsNull("MAX_LEAVE_TYPE_NO") == true)
{
//NB 200 = Normal LeaveType 201 = Sick LeaveType
intLeaveTypeNo = 202;
}
else
{
intLeaveTypeNo = Convert.ToInt32(DataSet.Tables[0].Rows[0]["MAX_LEAVE_TYPE_NO"]) + 1;
}
DataSet.Dispose();
DataSet = null;
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(",LEAVE_PERCENTAGE");
strQry.AppendLine(",EARNING_REPORT_HEADER1");
strQry.AppendLine(",EARNING_REPORT_HEADER2");
strQry.AppendLine(",IRP5_CODE");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",EARNING_DEL_IND)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parDataSet.Tables["LeaveType"].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["LeaveType"].Rows[0]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine("," + intLeaveTypeNo);
strQry.AppendLine("," + parDataSet.Tables["LeaveType"].Rows[0]["TIE_BREAKER"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["LeaveType"].Rows[0]["EARNING_DESC"].ToString()));
strQry.AppendLine("," + parDataSet.Tables["LeaveType"].Rows[0]["LEAVE_PERCENTAGE"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["LeaveType"].Rows[0]["EARNING_REPORT_HEADER1"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["LeaveType"].Rows[0]["EARNING_REPORT_HEADER2"].ToString()));
//20170418 - Fix IRP5Code
strQry.AppendLine(",3601");
strQry.AppendLine(",GETDATE()");
strQry.AppendLine("," + parint64CurrentUserNo);
strQry.AppendLine(",'Y')");
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(intLeaveTypeNo);
}
private void btnOK_Click(object sender, EventArgs e)
{
if (this.btnOK.Text == "Restore")
{
if (pvtDataSet.Tables["DataBaseName"] != null)
{
pvtDataSet.Tables.Remove("DataBaseName");
}
if (pvtDataSet.Tables["Check"] != null)
{
pvtDataSet.Tables.Remove("Check");
}
string strDatabaseName = "InteractPayroll";
string strDatabaseNo = this.dgvFilesDataGridView[1, dgvFilesDataGridView.CurrentRow.Index].Value.ToString();
if (strDatabaseNo != "")
{
strDatabaseName = strDatabaseName + "_" + strDatabaseNo;
}
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" NAME");
strQry.AppendLine(" FROM MASTER.dbo.SYSDATABASES ");
strQry.AppendLine(" WHERE NAME = " + clsDBConnectionObjects.Text2DynamicSQL(strDatabaseName));
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), pvtDataSet, "DataBaseName", -1);
if (pvtDataSet.Tables["DataBaseName"].Rows.Count == 0)
{
DialogResult myDialogResult = MessageBox.Show("Database " + strDatabaseName + " needs to be Created.\n\nWould you like to Continue?", "Create DB", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (myDialogResult == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
strQry.Clear();
strQry.AppendLine(" CREATE DATABASE " + strDatabaseName + " COLLATE SQL_Latin1_General_CP1_CI_AS");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
string parstrFileName = pvtstrFileDirectory + "\\" + this.dgvFilesDataGridView[2, dgvFilesDataGridView.CurrentRow.Index].Value.ToString();
strQry.Clear();
strQry.AppendLine("RESTORE DATABASE " + strDatabaseName + " FROM DISK = '" + parstrFileName + "' WITH REPLACE");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO ");
strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" WHERE COMPANY_NO = " + Convert.ToInt32(strDatabaseNo));
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), pvtDataSet, "Check", -1);
if (pvtDataSet.Tables["Check"].Rows.Count == 0)
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",COMPANY_DESC");
strQry.AppendLine(",DATE_FORMAT)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO ");
strQry.AppendLine(",COMPANY_DESC ");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL("dd-MM-yyyy"));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY");
strQry.AppendLine(" WHERE COMPANY_NO = " + Convert.ToInt32(strDatabaseNo));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Convert.ToInt32(strDatabaseNo));
}
}
}
else
{
this.Cursor = Cursors.WaitCursor;
this.dgvFilesDataGridView.Cursor = Cursors.WaitCursor;
this.Refresh();
string strDownloadFile = this.dgvFilesDataGridView[2, dgvFilesDataGridView.CurrentRow.Index].Value.ToString();
GetObjectRequest request = new GetObjectRequest();
request.BucketName = pvtstrBucketName;
request.Key = strDownloadFile;
GetObjectResponse response = iAmazonS3.GetObject(request);
response.WriteResponseStreamToFile(pvtstrFileDirectory + "\\" + strDownloadFile);
this.dgvFilesDataGridView.Rows.Remove(dgvFilesDataGridView.CurrentRow);
this.Cursor = Cursors.Default;
this.Refresh();
MessageBox.Show("File Download Successful");
}
}
public byte[] Update_Records(Int64 parint64CompanyNo, string parstrEmployeeWageNoIn, string parstrEmployeeSalaryNoIn, string parstrPayCategoryWageNoIn, string parstrPayCategorySalaryNoIn, string parstrDepartmentNoIn, string parstrOccupationNoIn, string parstrCostCentrePayCategoryNo, string parstrCostCentrePayCategoryType, string parstrCostCentreEmployeeNoIN, Int64 parint64CurrentUserNo)
{
DataSet DataSet = new System.Data.DataSet();
string strQry = "";
if (parstrEmployeeWageNoIn != ""
| parstrEmployeeSalaryNoIn != "")
{
strQry = "";
strQry += " SELECT ";
strQry += " C1.TABLE_NAME";
strQry += ",C1.COLUMN_NAME AS COLUMN1_NAME";
strQry += ",C2.COLUMN_NAME AS COLUMN2_NAME";
strQry += " FROM InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.TABLES T";
strQry += " INNER JOIN InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.COLUMNS C1";
strQry += " ON T.TABLE_NAME = C1.TABLE_NAME";
strQry += " AND C1.COLUMN_NAME = 'EMPLOYEE_NO'";
strQry += " LEFT JOIN InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.COLUMNS C2";
strQry += " ON T.TABLE_NAME = C2.TABLE_NAME";
strQry += " AND C2.COLUMN_NAME = 'PAY_CATEGORY_TYPE'";
strQry += " WHERE T.TABLE_TYPE = 'BASE TABLE'";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "ColumnName", parint64CompanyNo);
for (int intRow1 = 0; intRow1 < DataSet.Tables["ColumnName"].Rows.Count; intRow1++)
{
strQry = "";
strQry += " DELETE ";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo." + DataSet.Tables["ColumnName"].Rows[intRow1]["TABLE_NAME"].ToString();
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
if (DataSet.Tables["ColumnName"].Rows[intRow1]["COLUMN2_NAME"] != System.DBNull.Value)
{
if (parstrEmployeeWageNoIn != ""
& parstrEmployeeSalaryNoIn != "")
{
strQry += " AND ((EMPLOYEE_NO IN " + parstrEmployeeWageNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'W') ";
strQry += " OR (EMPLOYEE_NO IN " + parstrEmployeeSalaryNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'S')) ";
}
else
{
if (parstrEmployeeWageNoIn != "")
{
strQry += " AND (EMPLOYEE_NO IN " + parstrEmployeeWageNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'W') ";
}
else
{
strQry += " AND (EMPLOYEE_NO IN " + parstrEmployeeSalaryNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'S') ";
}
}
}
else
{
if (parstrEmployeeWageNoIn != ""
& parstrEmployeeSalaryNoIn != "")
{
strQry += " AND (EMPLOYEE_NO IN " + parstrEmployeeWageNoIn;
strQry += " OR EMPLOYEE_NO IN " + parstrEmployeeSalaryNoIn + ")";
}
else
{
if (parstrEmployeeWageNoIn != "")
{
strQry += " AND EMPLOYEE_NO IN " + parstrEmployeeWageNoIn;
}
else
{
strQry += " AND EMPLOYEE_NO IN " + parstrEmployeeSalaryNoIn;
}
}
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
}
strQry = "";
strQry += " DELETE ";
strQry += " FROM InteractPayroll.dbo.USER_EMPLOYEE";
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
if (parstrEmployeeWageNoIn != ""
& parstrEmployeeSalaryNoIn != "")
{
strQry += " AND ((EMPLOYEE_NO IN " + parstrEmployeeWageNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'W') ";
strQry += " OR (EMPLOYEE_NO IN " + parstrEmployeeSalaryNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'S'))";
}
else
{
if (parstrEmployeeWageNoIn != "")
{
strQry += " AND (EMPLOYEE_NO IN " + parstrEmployeeWageNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'W') ";
}
else
{
strQry += " AND (EMPLOYEE_NO IN " + parstrEmployeeSalaryNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'S') ";
}
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
}
if (parstrPayCategoryWageNoIn != ""
| parstrPayCategorySalaryNoIn != "")
{
strQry = "";
strQry += " SELECT DISTINCT";
strQry += " COMPANY_NO";
strQry += ",EMPLOYEE_NO";
strQry += ",PAY_CATEGORY_TYPE";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY ";
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
if (parstrPayCategoryWageNoIn != ""
& parstrPayCategorySalaryNoIn != "")
{
strQry += " AND ((PAY_CATEGORY_NO IN " + parstrPayCategoryWageNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'W')";
strQry += " OR (PAY_CATEGORY_NO IN " + parstrPayCategorySalaryNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'S'))";
}
else
{
if (parstrPayCategoryWageNoIn != "")
{
strQry += " AND PAY_CATEGORY_NO IN " + parstrPayCategoryWageNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'W'";
}
else
{
strQry += " AND PAY_CATEGORY_NO IN " + parstrPayCategorySalaryNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'S'";
}
}
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "EmployeePayCategory", parint64CompanyNo);
string strEmployeeWageNoIn = "";
string strEmployeeSalaryNoIn = "";
for (int intRow1 = 0; intRow1 < DataSet.Tables["EmployeePayCategory"].Rows.Count; intRow1++)
{
if (DataSet.Tables["EmployeePayCategory"].Rows[intRow1]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
if (strEmployeeWageNoIn == "")
{
strEmployeeWageNoIn = "(" + DataSet.Tables["EmployeePayCategory"].Rows[intRow1]["EMPLOYEE_NO"].ToString();
}
else
{
strEmployeeWageNoIn += "," + DataSet.Tables["EmployeePayCategory"].Rows[intRow1]["EMPLOYEE_NO"].ToString();
}
}
else
{
if (strEmployeeSalaryNoIn == "")
{
strEmployeeSalaryNoIn = "(" + DataSet.Tables["EmployeePayCategory"].Rows[intRow1]["EMPLOYEE_NO"].ToString();
}
else
{
strEmployeeSalaryNoIn += "," + DataSet.Tables["EmployeePayCategory"].Rows[intRow1]["EMPLOYEE_NO"].ToString();
}
}
}
if (strEmployeeWageNoIn != "")
{
strEmployeeWageNoIn += ")";
}
if (strEmployeeSalaryNoIn != "")
{
strEmployeeSalaryNoIn += ")";
}
if (strEmployeeWageNoIn != ""
| strEmployeeSalaryNoIn != "")
{
if (DataSet.Tables["ColumnName"] != null)
{
DataSet.Tables.Remove("ColumnName");
}
strQry = "";
strQry += " SELECT ";
strQry += " C1.TABLE_NAME";
strQry += ",C1.COLUMN_NAME AS COLUMN1_NAME";
strQry += ",C2.COLUMN_NAME AS COLUMN2_NAME";
strQry += " FROM InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.TABLES T";
strQry += " INNER JOIN InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.COLUMNS C1";
strQry += " ON T.TABLE_NAME = C1.TABLE_NAME";
strQry += " AND C1.COLUMN_NAME = 'EMPLOYEE_NO'";
strQry += " LEFT JOIN InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.COLUMNS C2";
strQry += " ON T.TABLE_NAME = C2.TABLE_NAME";
strQry += " AND C2.COLUMN_NAME = 'PAY_CATEGORY_TYPE'";
strQry += " WHERE T.TABLE_TYPE = 'BASE TABLE'";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "ColumnName", parint64CompanyNo);
for (int intRow1 = 0; intRow1 < DataSet.Tables["ColumnName"].Rows.Count; intRow1++)
{
strQry = "";
strQry += " DELETE ";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo." + DataSet.Tables["ColumnName"].Rows[intRow1]["TABLE_NAME"].ToString();
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
if (DataSet.Tables["ColumnName"].Rows[intRow1]["COLUMN2_NAME"] != System.DBNull.Value)
{
if (strEmployeeWageNoIn != ""
& strEmployeeSalaryNoIn != "")
{
strQry += " AND ((EMPLOYEE_NO IN " + strEmployeeWageNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'W') ";
strQry += " OR (EMPLOYEE_NO IN " + strEmployeeSalaryNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'S')) ";
}
else
{
if (strEmployeeWageNoIn != "")
{
strQry += " AND (EMPLOYEE_NO IN " + strEmployeeWageNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'W') ";
}
else
{
strQry += " AND (EMPLOYEE_NO IN " + strEmployeeSalaryNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'S') ";
}
}
}
else
{
if (strEmployeeWageNoIn != ""
& strEmployeeSalaryNoIn != "")
{
strQry += " AND (EMPLOYEE_NO IN " + strEmployeeWageNoIn;
strQry += " OR EMPLOYEE_NO IN " + strEmployeeSalaryNoIn + ")";
}
else
{
if (strEmployeeWageNoIn != "")
{
strQry += " AND EMPLOYEE_NO IN " + strEmployeeWageNoIn;
}
else
{
strQry += " AND EMPLOYEE_NO IN " + strEmployeeSalaryNoIn;
}
}
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
}
strQry = "";
strQry += " DELETE ";
strQry += " FROM InteractPayroll.dbo.USER_EMPLOYEE";
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
if (strEmployeeWageNoIn != ""
& strEmployeeSalaryNoIn != "")
{
strQry += " AND ((EMPLOYEE_NO IN " + strEmployeeWageNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'W') ";
strQry += " OR (EMPLOYEE_NO IN " + strEmployeeSalaryNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'S'))";
}
else
{
if (strEmployeeWageNoIn != "")
{
strQry += " AND (EMPLOYEE_NO IN " + strEmployeeWageNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'W') ";
}
else
{
strQry += " AND (EMPLOYEE_NO IN " + strEmployeeSalaryNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'S') ";
}
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
if (DataSet.Tables["ColumnName"] != null)
{
DataSet.Tables.Remove("ColumnName");
}
strQry = "";
strQry += " SELECT ";
strQry += " C1.TABLE_NAME";
strQry += ",C1.COLUMN_NAME AS COLUMN1_NAME";
strQry += ",C2.COLUMN_NAME AS COLUMN2_NAME";
strQry += " FROM InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.TABLES T";
strQry += " INNER JOIN InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.COLUMNS C1";
strQry += " ON T.TABLE_NAME = C1.TABLE_NAME";
strQry += " AND C1.COLUMN_NAME = 'PAY_CATEGORY_NO'";
strQry += " LEFT JOIN InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.COLUMNS C2";
strQry += " ON T.TABLE_NAME = C2.TABLE_NAME";
strQry += " AND C2.COLUMN_NAME = 'PAY_CATEGORY_TYPE'";
strQry += " WHERE T.TABLE_TYPE = 'BASE TABLE'";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "ColumnName", parint64CompanyNo);
for (int intRow1 = 0; intRow1 < DataSet.Tables["ColumnName"].Rows.Count; intRow1++)
{
if (DataSet.Tables["ColumnName"].Rows[intRow1]["COLUMN2_NAME"] == System.DBNull.Value)
{
string stop = "";
}
if (parstrPayCategoryWageNoIn != "")
{
strQry = "";
strQry += " DELETE ";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo." + DataSet.Tables["ColumnName"].Rows[intRow1]["TABLE_NAME"].ToString();
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
strQry += " AND PAY_CATEGORY_NO IN " + parstrPayCategoryWageNoIn;
if (DataSet.Tables["ColumnName"].Rows[intRow1]["COLUMN2_NAME"] != System.DBNull.Value)
{
strQry += " AND PAY_CATEGORY_TYPE = 'W'";
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
}
if (parstrPayCategorySalaryNoIn != "")
{
strQry = "";
strQry += " DELETE ";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo." + DataSet.Tables["ColumnName"].Rows[intRow1]["TABLE_NAME"].ToString();
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
strQry += " AND PAY_CATEGORY_NO IN " + parstrPayCategorySalaryNoIn;
if (DataSet.Tables["ColumnName"].Rows[intRow1]["COLUMN2_NAME"] != System.DBNull.Value)
{
strQry += " AND PAY_CATEGORY_TYPE = 'S'";
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
}
}
if (parstrPayCategoryWageNoIn != "")
{
strQry = "";
strQry += " DELETE ";
strQry += " FROM InteractPayroll.dbo.USER_PAY_CATEGORY";
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
strQry += " AND PAY_CATEGORY_NO IN " + parstrPayCategoryWageNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'W'";
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
}
if (parstrPayCategorySalaryNoIn != "")
{
strQry = "";
strQry += " DELETE ";
strQry += " FROM InteractPayroll.dbo.USER_PAY_CATEGORY";
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
strQry += " AND PAY_CATEGORY_NO IN " + parstrPayCategorySalaryNoIn;
strQry += " AND PAY_CATEGORY_TYPE = 'S'";
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
}
}
}
if (parstrDepartmentNoIn != "")
{
if (DataSet.Tables["ColumnName"] != null)
{
DataSet.Tables.Remove("ColumnName");
}
strQry = "";
strQry += " SELECT ";
strQry += " C1.TABLE_NAME";
strQry += ",C1.COLUMN_NAME AS COLUMN1_NAME";
strQry += " FROM InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.TABLES T";
strQry += " INNER JOIN InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.COLUMNS C1";
strQry += " ON T.TABLE_NAME = C1.TABLE_NAME";
strQry += " AND C1.COLUMN_NAME = 'DEPARTMENT_NO'";
strQry += " WHERE T.TABLE_TYPE = 'BASE TABLE'";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "ColumnName", parint64CompanyNo);
for (int intRow1 = 0; intRow1 < DataSet.Tables["ColumnName"].Rows.Count; intRow1++)
{
if (DataSet.Tables["ColumnName"].Rows[intRow1]["TABLE_NAME"].ToString() == "DEPARTMENT")
{
strQry = "";
strQry += " DELETE ";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo." + DataSet.Tables["ColumnName"].Rows[intRow1]["TABLE_NAME"].ToString();
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
strQry += " AND DEPARTMENT_NO IN " + parstrDepartmentNoIn;
}
else
{
strQry = "";
strQry += " UPDATE InteractPayroll_#CompanyNo#.dbo." + DataSet.Tables["ColumnName"].Rows[intRow1]["TABLE_NAME"].ToString();
strQry += " SET DEPARTMENT_NO = 0 ";
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
strQry += " AND DEPARTMENT_NO IN " + parstrDepartmentNoIn;
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
}
strQry = "";
strQry += " DELETE ";
strQry += " FROM InteractPayroll.dbo.USER_DEPARTMENT";
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
strQry += " AND DEPARTMENT_NO IN " + parstrDepartmentNoIn;
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
}
if (parstrOccupationNoIn != "")
{
if (DataSet.Tables["ColumnName"] != null)
{
DataSet.Tables.Remove("ColumnName");
}
strQry = "";
strQry += " SELECT ";
strQry += " C1.TABLE_NAME";
strQry += ",C1.COLUMN_NAME AS COLUMN1_NAME";
strQry += " FROM InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.TABLES T";
strQry += " INNER JOIN InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.COLUMNS C1";
strQry += " ON T.TABLE_NAME = C1.TABLE_NAME";
strQry += " AND C1.COLUMN_NAME = 'OCCUPATION_NO'";
strQry += " WHERE T.TABLE_TYPE = 'BASE TABLE'";
clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "ColumnName", parint64CompanyNo);
for (int intRow1 = 0; intRow1 < DataSet.Tables["ColumnName"].Rows.Count; intRow1++)
{
if (DataSet.Tables["ColumnName"].Rows[intRow1]["TABLE_NAME"].ToString() == "OCCUPATION")
{
strQry = "";
strQry += " DELETE ";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo." + DataSet.Tables["ColumnName"].Rows[intRow1]["TABLE_NAME"].ToString();
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
strQry += " AND OCCUPATION_NO IN " + parstrOccupationNoIn;
}
else
{
strQry = "";
strQry += " UPDATE InteractPayroll_#CompanyNo#.dbo." + DataSet.Tables["ColumnName"].Rows[intRow1]["TABLE_NAME"].ToString();
strQry += " SET OCCUPATION_NO = 0 ";
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
strQry += " AND OCCUPATION_NO IN " + parstrOccupationNoIn;
}
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
}
}
if (parstrCostCentreEmployeeNoIN != "")
{
strQry = "";
strQry += " DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY";
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
strQry += " AND PAY_CATEGORY_NO = " + parstrCostCentrePayCategoryNo;
strQry += " AND PAY_CATEGORY_TYPE = '" + parstrCostCentrePayCategoryType + "'";
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
strQry = "";
strQry += " INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY";
strQry += "(COMPANY_NO";
strQry += ",EMPLOYEE_NO";
strQry += ",PAY_CATEGORY_NO";
strQry += ",PAY_CATEGORY_TYPE";
strQry += ",TIE_BREAKER";
strQry += ",HOURLY_RATE";
strQry += ",DEFAULT_IND";
strQry += ",USER_NO_NEW_RECORD";
strQry += ",DATETIME_NEW_RECORD";
strQry += ",LEAVE_DAY_RATE_DECIMAL)";
strQry += " SELECT ";
strQry += " COMPANY_NO";
strQry += ",EMPLOYEE_NO";
strQry += "," + parstrCostCentrePayCategoryNo;
strQry += ",PAY_CATEGORY_TYPE";
strQry += ",1";
strQry += ",0";
strQry += ",'Y'";
strQry += "," + parint64CurrentUserNo;
strQry += ",GETDATE()";
strQry += ",0";
strQry += " FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE";
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
strQry += " AND EMPLOYEE_NO IN " + parstrCostCentreEmployeeNoIN;
strQry += " AND PAY_CATEGORY_TYPE = '" + parstrCostCentrePayCategoryType + "'";
strQry += " AND DATETIME_DELETE_RECORD IS NULL ";
clsDBConnectionObjects.Execute_SQLCommand(strQry, parint64CompanyNo);
}
strQry = "";
strQry += " UPDATE InteractPayroll.dbo.COMPANY_LINK";
strQry += " SET BACKUP_DB_IND = 1";
strQry += " WHERE COMPANY_NO = " + parint64CompanyNo;
clsDBConnectionObjects.Execute_SQLCommand(strQry, -1);
byte[] byteArray = Get_Form_Records(parint64CompanyNo);
return(byteArray);
}
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 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 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 int Insert_New_Record(Int64 parint64CompanyNo, Int64 parint64CurrentUserNo, string parstrMenuId, string parstrOccupationDepartmentDesc)
{
DataSet DataSet = new DataSet();
int intOccupationDepartmentNo = 1;
StringBuilder strQry = new StringBuilder();
string strTableName = "";
if (parstrMenuId == "39")
{
strTableName = "DEPARTMENT";
}
else
{
strTableName = "OCCUPATION";
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(" + strTableName + "_NO) AS MAX_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo." + strTableName);
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", parint64CompanyNo);
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
{
intOccupationDepartmentNo = 1;
}
else
{
intOccupationDepartmentNo = Convert.ToInt32(DataSet.Tables[0].Rows[0]["MAX_NO"]) + 1;
}
DataSet.Dispose();
DataSet = null;
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo." + strTableName);
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine("," + strTableName + "_NO");
strQry.AppendLine("," + strTableName + "_DESC");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + parint64CompanyNo);
strQry.AppendLine("," + intOccupationDepartmentNo);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrOccupationDepartmentDesc));
strQry.AppendLine(",GETDATE()");
strQry.AppendLine("," + parint64CurrentUserNo + ")");
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);
return(intOccupationDepartmentNo);
}
public int Convert_Records(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(" A.TABLE_NAME");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.INFORMATION_SCHEMA.COLUMNS A ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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'");
strQry.AppendLine(" AND NOT A.TABLE_NAME LIKE '%_TEMP%'");
strQry.AppendLine(" AND NOT A.TABLE_NAME LIKE '%PRINT%'");
strQry.AppendLine(" AND NOT A.TABLE_NAME LIKE '%CLOCK_TIME%'");
strQry.AppendLine(" AND NOT A.TABLE_NAME = 'SDL_REPORT'");
strQry.AppendLine(" AND NOT A.TABLE_NAME LIKE 'EMPLOYEE_EARNING_%'");
strQry.AppendLine(" AND NOT A.TABLE_NAME LIKE '%_HISTORY'");
strQry.AppendLine(" AND NOT A.TABLE_NAME LIKE '%_CURRENT'");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "TablesToFix", parint64CompanyNo);
for (int intRow = 0; intRow < DataSet.Tables["PayCategory"].Rows.Count; intRow++)
{
//Loans
strQry.Clear();
strQry.AppendLine(" UPDATE LN ");
strQry.AppendLine(" SET LN.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LOANS LN ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" L.COMPANY_NO ");
strQry.AppendLine(",E.EMPLOYEE_NO ");
strQry.AppendLine(",L.DEDUCTION_NO ");
strQry.AppendLine(",L.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(",L.LOAN_REC_NO");
strQry.AppendLine(",L.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LOANS L ");
strQry.AppendLine(" ON E.COMPANY_NO = L.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = L.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = L.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
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 InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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 ");
strQry.AppendLine(") AS NEW_TABLE");
strQry.AppendLine(" ON NEW_TABLE.COMPANY_NO = LN.COMPANY_NO ");
strQry.AppendLine(" AND NEW_TABLE.EMPLOYEE_NO = LN.EMPLOYEE_NO ");
strQry.AppendLine(" AND NEW_TABLE.DEDUCTION_NO = LN.DEDUCTION_NO ");
strQry.AppendLine(" AND NEW_TABLE.DEDUCTION_SUB_ACCOUNT_NO = LN.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND NEW_TABLE.LOAN_REC_NO = LN.LOAN_REC_NO ");
strQry.AppendLine(" AND NEW_TABLE.PAY_CATEGORY_TYPE = LN.PAY_CATEGORY_TYPE ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
//Leave Current
strQry.Clear();
strQry.AppendLine(" UPDATE LCN ");
strQry.AppendLine(" SET LCN.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LCN ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" LC.COMPANY_NO ");
strQry.AppendLine(",E.EMPLOYEE_NO ");
strQry.AppendLine(",LC.EARNING_NO ");
strQry.AppendLine(",LC.PAY_CATEGORY_TYPE");
strQry.AppendLine(",LC.LEAVE_REC_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC ");
strQry.AppendLine(" ON E.COMPANY_NO = LC.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = LC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
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 InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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 ");
strQry.AppendLine(") AS NEW_TABLE");
strQry.AppendLine(" ON NEW_TABLE.COMPANY_NO = LCN.COMPANY_NO ");
strQry.AppendLine(" AND NEW_TABLE.EMPLOYEE_NO = LCN.EMPLOYEE_NO ");
strQry.AppendLine(" AND NEW_TABLE.EARNING_NO = LCN.EARNING_NO ");
strQry.AppendLine(" AND NEW_TABLE.PAY_CATEGORY_TYPE = LCN.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND NEW_TABLE.LEAVE_REC_NO = LCN.LEAVE_REC_NO ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
//Employee Earnings
strQry.Clear();
strQry.AppendLine(" UPDATE EEN ");
strQry.AppendLine(" SET EEN.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EEN ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EE.COMPANY_NO ");
strQry.AppendLine(",E.EMPLOYEE_NO ");
strQry.AppendLine(",EE.EARNING_NO ");
strQry.AppendLine(",EE.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EE.TIE_BREAKER");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");
strQry.AppendLine(" ON E.COMPANY_NO = EE.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EE.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EE.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
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 InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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 ");
strQry.AppendLine(") AS NEW_TABLE");
strQry.AppendLine(" ON NEW_TABLE.COMPANY_NO = EEN.COMPANY_NO ");
strQry.AppendLine(" AND NEW_TABLE.EMPLOYEE_NO = EEN.EMPLOYEE_NO ");
strQry.AppendLine(" AND NEW_TABLE.EARNING_NO = EEN.EARNING_NO ");
strQry.AppendLine(" AND NEW_TABLE.PAY_CATEGORY_TYPE = EEN.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND NEW_TABLE.TIE_BREAKER = EEN.TIE_BREAKER ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
//Employee Deductions
strQry.Clear();
strQry.AppendLine(" UPDATE EDN ");
strQry.AppendLine(" SET EDN.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION EDN ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" ED.COMPANY_NO ");
strQry.AppendLine(",E.EMPLOYEE_NO ");
strQry.AppendLine(",ED.DEDUCTION_NO ");
strQry.AppendLine(",ED.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(",ED.PAY_CATEGORY_TYPE");
strQry.AppendLine(",ED.TIE_BREAKER");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED ");
strQry.AppendLine(" ON E.COMPANY_NO = ED.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = ED.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = ED.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
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 InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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 ");
strQry.AppendLine(") AS NEW_TABLE");
strQry.AppendLine(" ON NEW_TABLE.COMPANY_NO = EDN.COMPANY_NO ");
strQry.AppendLine(" AND NEW_TABLE.EMPLOYEE_NO = EDN.EMPLOYEE_NO ");
strQry.AppendLine(" AND NEW_TABLE.DEDUCTION_NO = EDN.DEDUCTION_NO ");
strQry.AppendLine(" AND NEW_TABLE.DEDUCTION_SUB_ACCOUNT_NO = EDN.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND NEW_TABLE.PAY_CATEGORY_TYPE = EDN.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND NEW_TABLE.TIE_BREAKER = EDN.TIE_BREAKER ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE EDEPN ");
strQry.AppendLine(" SET EDEPN.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE EDEPN ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EDEP.COMPANY_NO ");
strQry.AppendLine(",E.EMPLOYEE_NO ");
strQry.AppendLine(",EDEP.DEDUCTION_NO ");
strQry.AppendLine(",EDEP.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(",EDEP.EARNING_NO ");
strQry.AppendLine(",EDEP.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EDEP.TIE_BREAKER");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE EDEP ");
strQry.AppendLine(" ON E.COMPANY_NO = EDEP.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EDEP.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EDEP.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
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 InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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 ");
strQry.AppendLine(") AS NEW_TABLE");
strQry.AppendLine(" ON NEW_TABLE.COMPANY_NO = EDEPN.COMPANY_NO ");
strQry.AppendLine(" AND NEW_TABLE.EMPLOYEE_NO = EDEPN.EMPLOYEE_NO ");
strQry.AppendLine(" AND NEW_TABLE.DEDUCTION_NO = EDEPN.DEDUCTION_NO ");
strQry.AppendLine(" AND NEW_TABLE.DEDUCTION_SUB_ACCOUNT_NO = EDEPN.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND NEW_TABLE.EARNING_NO = EDEPN.EARNING_NO ");
strQry.AppendLine(" AND NEW_TABLE.PAY_CATEGORY_TYPE = EDEPN.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND NEW_TABLE.TIE_BREAKER = EDEPN.TIE_BREAKER ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE UE ");
strQry.AppendLine(" SET UE.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.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 InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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(strQry.ToString(), parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE E ");
strQry.AppendLine(" SET E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.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 InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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(strQry.ToString(), parint64CompanyNo);
//Break
strQry.Clear();
if (parstrToPayCategoryType == "W")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT ");
}
else
{
if (parstrToPayCategoryType == "S")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT ");
}
else
{
//Time Attend
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(" 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 InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT A");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT A");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.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 InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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(strQry.ToString(), parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" DELETE A ");
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT A ");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT A ");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.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 InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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(strQry.ToString(), parint64CompanyNo);
//Timesheets
strQry.Clear();
if (parstrToPayCategoryType == "W")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT ");
}
else
{
if (parstrToPayCategoryType == "S")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT ");
}
else
{
//Time Attend
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(" 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 InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT A ");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT A ");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.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 InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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(strQry.ToString(), parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" DELETE A ");
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT A ");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT A ");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.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 InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.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(strQry.ToString(), parint64CompanyNo);
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 InteractPayroll_#CompanyNo#.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(strQry.ToString(), parint64CompanyNo);
}
}
Convert_Records_Continue:
DataSet.Dispose();
DataSet = null;
return intReturnCode;
}
public byte[] Get_Form_Records(Int64 parInt64CompanyNo)
{
DataSet DataSet = new DataSet();
DataSet TempDataSet;
string strEmployeeNoIn = "-1";
StringBuilder strFieldNamesInitialised = new StringBuilder();
StringBuilder strQry = new StringBuilder();
//Insert Records
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EDH.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EDH.EMPLOYEE_NO");
strQry.AppendLine(",EDH.PAY_PERIOD_DATE");
strQry.AppendLine(",SUM(EDH.TOTAL)");
strQry.AppendLine(",SUM(EEH.TOTAL)");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_HISTORY EDH ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_HISTORY EEH ");
strQry.AppendLine(" ON EDH.COMPANY_NO = EEH.COMPANY_NO ");
strQry.AppendLine(" AND EDH.PAY_PERIOD_DATE = EEH.PAY_PERIOD_DATE ");
strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE = EEH.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EDH.EMPLOYEE_NO = EEH.EMPLOYEE_NO ");
strQry.AppendLine(" AND EDH.RUN_TYPE = EEH.RUN_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
strQry.AppendLine(" ON EDH.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE ");
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE IN ('W','S') ");
strQry.AppendLine(" AND EDH.EMPLOYEE_NO = E.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",MAX(PAY_PERIOD_DATE) AS MAX_PAY_PERIOD_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND PAY_CATEGORY_TYPE IN ('W','S') ");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO) AS TEMP_EMPLOYEE_DATE ");
strQry.AppendLine(" ON EDH.PAY_CATEGORY_TYPE = TEMP_EMPLOYEE_DATE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EDH.EMPLOYEE_NO = TEMP_EMPLOYEE_DATE.EMPLOYEE_NO ");
//Add 180 Days (Withinn 6 Months)
strQry.AppendLine(" AND DATEADD(DD,180,EDH.PAY_PERIOD_DATE) >= MAX_PAY_PERIOD_DATE ");
strQry.AppendLine(" WHERE EDH.COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE IN ('W','S') ");
strQry.AppendLine(" AND EDH.RUN_TYPE = 'T'");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" EDH.PAY_CATEGORY_TYPE ");
strQry.AppendLine(",EDH.EMPLOYEE_NO ");
strQry.AppendLine(",EDH.PAY_PERIOD_DATE");
//Exclude where Already Have Take-On Values
strQry.AppendLine(" HAVING SUM(EDH.TOTAL) = 0");
strQry.AppendLine(" AND SUM(EEH.TOTAL) = 0 ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeeTemp", parInt64CompanyNo);
for (int intRow = 0; intRow < DataSet.Tables["EmployeeTemp"].Rows.Count; intRow++)
{
if (intRow == 0)
{
strEmployeeNoIn = DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString();
}
else
{
strEmployeeNoIn += "," + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString();
}
strQry.Clear();
strQry.AppendLine("INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",RUN_NO");
strFieldNamesInitialised.Clear();
//Return strings for field that need to be Initialised to Zero
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_DEDUCTION_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT");
strQry.AppendLine(" ED.COMPANY_NO");
strQry.AppendLine(",ED.PAY_CATEGORY_TYPE");
strQry.AppendLine(",ED.EMPLOYEE_NO");
strQry.AppendLine(",'T'");
strQry.AppendLine(",ED.DEDUCTION_NO ");
strQry.AppendLine(",ED.DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",1");
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT EDC");
strQry.AppendLine(" ON ED.COMPANY_NO = EDC.COMPANY_NO ");
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = EDC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND ED.EMPLOYEE_NO = EDC.EMPLOYEE_NO ");
strQry.AppendLine(" AND ED.DEDUCTION_NO = EDC.DEDUCTION_NO ");
strQry.AppendLine(" AND ED.DEDUCTION_SUB_ACCOUNT_NO = EDC.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND EDC.RUN_TYPE = 'T' ");
strQry.AppendLine(" WHERE ED.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND ED.EMPLOYEE_NO = " + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");
//No EMPLOYEE_DEDUCTION_HISTORY Record Exists
strQry.AppendLine(" AND EDC.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine("INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
//NB PAY_CATEGORY_NO will be Set to Zero
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",EARNING_NO");
//ELR 2014-05-24
strQry.AppendLine(",EARNING_TYPE_IND");
strQry.AppendLine(",RUN_NO");
strFieldNamesInitialised.Clear();
//Return strings for field that need to be Initialised to Zero
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_EARNING_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT");
strQry.AppendLine(" EN.COMPANY_NO");
strQry.AppendLine(",EN.PAY_CATEGORY_TYPE");
strQry.AppendLine("," + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(",'T'");
strQry.AppendLine(",EN.EARNING_NO ");
//ELR 2014-05-24
strQry.AppendLine(",'U'");
strQry.AppendLine(",1");
//Append Initialised Numeric Fields Names
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING EN ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.COMPANY C ");
strQry.AppendLine(" ON EN.COMPANY_NO = C.COMPANY_NO ");
strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT EEC");
strQry.AppendLine(" ON EN.COMPANY_NO = EEC.COMPANY_NO ");
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = EEC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EN.EARNING_NO = EEC.EARNING_NO ");
strQry.AppendLine(" AND EEC.RUN_TYPE = 'T' ");
strQry.AppendLine(" WHERE EN.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine(" AND (EN.EARNING_NO IN (1,2,7,8,9)");
if (DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
//Normal Leave / Sick Leave
strQry.AppendLine(" OR (EN.EARNING_NO IN (200,201))");
strQry.AppendLine(" OR (EN.EARNING_NO = 3 AND C.OVERTIME1_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 4 AND C.OVERTIME2_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 5 AND C.OVERTIME3_RATE > 0))");
}
else
{
strQry.AppendLine(" OR (EN.EARNING_NO = 3 AND C.SALARY_OVERTIME1_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 4 AND C.SALARY_OVERTIME2_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 5 AND C.SALARY_OVERTIME3_RATE > 0))");
}
strQry.AppendLine(" AND EN.DATETIME_DELETE_RECORD IS NULL");
//No EMPLOYEE_EARNING_HISTORY Record Exists
strQry.AppendLine(" AND EEC.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine("INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",EARNING_NO");
//ELR 2014-05-24
strQry.AppendLine(",EARNING_TYPE_IND");
strQry.AppendLine(",RUN_NO");
strFieldNamesInitialised.Clear();
//Return strings for field that need to be Initialised to Zero
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_EARNING_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT");
strQry.AppendLine(" EE.COMPANY_NO");
strQry.AppendLine(",EE.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EE.EMPLOYEE_NO");
strQry.AppendLine(",'T'");
strQry.AppendLine(",EE.EARNING_NO ");
//ELR 2014-05-24
strQry.AppendLine(",EE.EARNING_TYPE_IND");
strQry.AppendLine(",1");
//Append Initialised Numeric Fields Names
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT EEC");
strQry.AppendLine(" ON EE.COMPANY_NO = EEC.COMPANY_NO ");
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = EEC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EE.EMPLOYEE_NO = EEC.EMPLOYEE_NO ");
strQry.AppendLine(" AND EE.EARNING_NO = EEC.EARNING_NO ");
strQry.AppendLine(" AND EEC.RUN_TYPE = 'T' ");
strQry.AppendLine(" WHERE EE.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EE.EMPLOYEE_NO = " + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL");
//No EMPLOYEE_EARNING_HISTORY Record Exists
strQry.AppendLine(" AND EEC.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
//First Delete Records That are older than 180 Days
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND RUN_TYPE = 'T' ");
strQry.AppendLine(" AND EMPLOYEE_NO NOT IN (" + strEmployeeNoIn + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
//First Delete Records That are Older than 180 Days
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND RUN_TYPE = 'T' ");
strQry.AppendLine(" AND EMPLOYEE_NO NOT IN (" + strEmployeeNoIn + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
//EERORL CARRY HERE
DataSet.Tables.Add("PayrollType");
DataTable PayrollTypeDataTable = new DataTable("PayrollType");
DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));
if (DataSet.Tables["EmployeeTemp"].Rows.Count > 0)
{
DataView PayrollTypeDataView = new DataView(DataSet.Tables["EmployeeTemp"],
"PAY_CATEGORY_TYPE = 'W'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Wages";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
PayrollTypeDataView = null;
PayrollTypeDataView = new DataView(DataSet.Tables["EmployeeTemp"],
"PAY_CATEGORY_TYPE = 'S'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Salaries";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
DataSet.Tables.Remove("EmployeeTemp");
DataSet.AcceptChanges();
byte[] bytTempCompress = Get_Company_Records(parInt64CompanyNo, DataSet.Tables["PayrollType"].Rows[0]["PAYROLL_TYPE_DESC"].ToString().Substring(0, 1));
TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
DataSet.Merge(TempDataSet);
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] 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_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 void Load_New_Run(Int64 parintUserNo, string parstrDate)
{
StringBuilder strQry = new StringBuilder();
StringBuilder strQryCol = new StringBuilder();
DataSet DataSet = new DataSet();
DateTime EndOfMonthDateTime = DateTime.ParseExact(parstrDate.Substring(0, 8) + "01", "yyyy-MM-dd", null).AddMonths(1).AddDays(-1);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO ");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_COMPANY_TO_LOAD ");
strQry.AppendLine(" WHERE USER_NO = " + parintUserNo.ToString());
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "UserCompanyNo", -1);
string strTableNoToLoad = "InteractPayroll_" + Convert.ToInt32(DataSet.Tables["UserCompanyNo"].Rows[0]["COMPANY_NO"]).ToString("00000");
strQry.Clear();
strQry.AppendLine(" SELECT TABLE_NAME");
strQry.AppendLine(" FROM " + strTableNoToLoad + ".INFORMATION_SCHEMA.TABLES ");
strQry.AppendLine(" WHERE TABLE_NAME LIKE '%CURRENT%'");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "CurrentTables", -1);
for (int intRow = 0; intRow < DataSet.Tables["CurrentTables"].Rows.Count; intRow++)
{
strQry.Clear();
strQry.AppendLine(" DELETE FROM " + strTableNoToLoad + ".dbo." + DataSet.Tables["CurrentTables"].Rows[intRow]["TABLE_NAME"].ToString());
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
strQry.Clear();
strQry.AppendLine(" INSERT INTO " + strTableNoToLoad + ".dbo.LEAVE_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",LEAVE_DESC");
strQry.AppendLine(",PROCESS_NO");
strQry.AppendLine(",LEAVE_FROM_DATE");
strQry.AppendLine(",LEAVE_TO_DATE");
strQry.AppendLine(",LEAVE_OPTION");
strQry.AppendLine(",LEAVE_DAYS_DECIMAL");
strQry.AppendLine(",LEAVE_HOURS_DECIMAL)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" 18");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",LEAVE_DESC");
strQry.AppendLine(",PROCESS_NO");
strQry.AppendLine(",LEAVE_FROM_DATE");
strQry.AppendLine(",LEAVE_TO_DATE");
strQry.AppendLine(",LEAVE_OPTION");
strQry.AppendLine(",LEAVE_DAYS_DECIMAL");
strQry.AppendLine(",LEAVE_HOURS_DECIMAL");
strQry.AppendLine(" FROM InteractPayroll_00013.dbo.LEAVE_HISTORY");
strQry.AppendLine(" WHERE COMPANY_NO = 13");
strQry.AppendLine(" AND (PAY_PERIOD_DATE = '" + parstrDate + "'");
strQry.AppendLine(" OR PAY_PERIOD_DATE = '" + EndOfMonthDateTime.ToString("yyyy-MM-dd") + "')");
strQry.AppendLine(" AND PROCESS_NO = 0");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
//2018-02-03
strQry.Clear();
strQry.AppendLine(" INSERT INTO " + strTableNoToLoad + ".dbo.EMPLOYEE_TIMESHEET_BREAK_DAY_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",TIMESHEET_DATE");
strQry.AppendLine(",DAY_NO");
strQry.AppendLine(",DAY_PAID_MINUTES");
strQry.AppendLine(",BREAK_ACCUM_MINUTES");
strQry.AppendLine(",INDICATOR");
strQry.AppendLine(",BREAK_INDICATOR)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" 18");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",TIMESHEET_DATE");
strQry.AppendLine(",DAY_NO");
strQry.AppendLine(",DAY_PAID_MINUTES");
strQry.AppendLine(",BREAK_ACCUM_MINUTES");
strQry.AppendLine(",INDICATOR");
strQry.AppendLine(",BREAK_INDICATOR");
strQry.AppendLine(" FROM InteractPayroll_00013.dbo.EMPLOYEE_TIMESHEET_BREAK_DAY_HISTORY");
strQry.AppendLine(" WHERE COMPANY_NO = 13");
strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + parstrDate + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
//Disable Trigger
strQry.Clear();
strQry.AppendLine(" ALTER TABLE " + strTableNoToLoad + ".dbo.EMPLOYEE_BREAK_CURRENT ");
strQry.AppendLine(" DISABLE TRIGGER tgr_EMPLOYEE_BREAK_CURRENT_Maintain_EMPLOYEE_TIMESHEET_BREAK_DAY_CURRENT_Table ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
strQry.Clear();
strQry.AppendLine(" INSERT INTO " + strTableNoToLoad + ".dbo.EMPLOYEE_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(",INCLUDED_IN_RUN_IND");
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(" 18");
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(",NULL");
strQry.AppendLine(",INDICATOR");
strQry.AppendLine(",BREAK_ACCUM_MINUTES");
strQry.AppendLine(",USER_NO_TIME_IN");
strQry.AppendLine(",USER_NO_TIME_OUT");
strQry.AppendLine(" FROM InteractPayroll_00013.dbo.EMPLOYEE_BREAK_HISTORY");
strQry.AppendLine(" WHERE COMPANY_NO = 13");
strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + parstrDate + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
//Enable Trigger
strQry.Clear();
strQry.AppendLine(" ALTER TABLE " + strTableNoToLoad + ".dbo.EMPLOYEE_BREAK_CURRENT ");
strQry.AppendLine(" ENABLE TRIGGER tgr_EMPLOYEE_BREAK_CURRENT_Maintain_EMPLOYEE_TIMESHEET_BREAK_DAY_CURRENT_Table ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
//Disable Trigger
strQry.Clear();
strQry.AppendLine(" ALTER TABLE " + strTableNoToLoad + ".dbo.EMPLOYEE_TIMESHEET_CURRENT ");
strQry.AppendLine(" DISABLE TRIGGER tgr_EMPLOYEE_TIMESHEET_CURRENT_Maintain_EMPLOYEE_TIMESHEET_BREAK_DAY_CURRENT_Table ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
strQry.Clear();
strQry.AppendLine(" INSERT INTO " + strTableNoToLoad + ".dbo.EMPLOYEE_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(",INCLUDED_IN_RUN_IND");
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(" 18");
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(",NULL");
strQry.AppendLine(",INDICATOR");
strQry.AppendLine(",TIMESHEET_ACCUM_MINUTES");
strQry.AppendLine(",USER_NO_TIME_IN");
strQry.AppendLine(",USER_NO_TIME_OUT");
strQry.AppendLine(" FROM InteractPayroll_00013.dbo.EMPLOYEE_TIMESHEET_HISTORY");
strQry.AppendLine(" WHERE COMPANY_NO = 13");
strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + parstrDate + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
//Enable Trigger
strQry.Clear();
strQry.AppendLine(" ALTER TABLE " + strTableNoToLoad + ".dbo.EMPLOYEE_TIMESHEET_CURRENT ");
strQry.AppendLine(" ENABLE TRIGGER tgr_EMPLOYEE_TIMESHEET_CURRENT_Maintain_EMPLOYEE_TIMESHEET_BREAK_DAY_CURRENT_Table ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
//Public Holiday
strQry.Clear();
strQry.AppendLine(" DELETE FROM " + strTableNoToLoad + ".dbo.PUBLIC_HOLIDAY");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
strQry.Clear();
strQry.AppendLine(" SET IDENTITY_INSERT " + strTableNoToLoad + ".dbo.PUBLIC_HOLIDAY ON");
strQry.AppendLine(" INSERT INTO " + strTableNoToLoad + ".dbo.PUBLIC_HOLIDAY");
strQry.AppendLine("(PUBLIC_HOLIDAY_NO");
strQry.AppendLine(",PUBLIC_HOLIDAY_DESC");
strQry.AppendLine(",PUBLIC_HOLIDAY_DATE)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PUBLIC_HOLIDAY_NO");
strQry.AppendLine(",PUBLIC_HOLIDAY_DESC");
strQry.AppendLine(",PUBLIC_HOLIDAY_DATE ");
strQry.AppendLine(" FROM InteractPayroll_00013.dbo.PUBLIC_HOLIDAY");
strQry.AppendLine(" SET IDENTITY_INSERT " + strTableNoToLoad + ".dbo.PUBLIC_HOLIDAY OFF");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
strQry.Clear();
strQry.AppendLine(" SELECT TABLE_NAME");
strQry.AppendLine(" FROM " + strTableNoToLoad + ".INFORMATION_SCHEMA.TABLES ");
strQry.AppendLine(" WHERE TABLE_NAME IN (");
strQry.AppendLine(" 'PUBLIC_HOLIDAY_HISTORY'");
strQry.AppendLine(",'LEAVE_HISTORY'");
strQry.AppendLine(",'EMPLOYEE_BREAK_HISTORY'");
strQry.AppendLine(",'OCCUPATION_HISTORY'");
strQry.AppendLine(",'EMPLOYEE_INFO_HISTORY'");
strQry.AppendLine(",'EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE_HISTORY'");
strQry.AppendLine(",'EMPLOYEE_DEDUCTION_HISTORY'");
strQry.AppendLine(",'EMPLOYEE_EARNING_WEEK_HISTORY'");
strQry.AppendLine(",'EMPLOYEE_PAY_CATEGORY_HISTORY'");
strQry.AppendLine(",'EMPLOYEE_EARNING_HISTORY'");
strQry.AppendLine(",'EMPLOYEE_TIMESHEET_BREAK_DAY_HISTORY'");
strQry.AppendLine(",'EMPLOYEE_TIMESHEET_HISTORY'");
strQry.AppendLine(",'LEAVE_SHIFT_HISTORY'");
strQry.AppendLine(",'PAY_CATEGORY_BREAK_HISTORY'");
strQry.AppendLine(",'PAY_CATEGORY_PERIOD_HISTORY'");
strQry.AppendLine(",'PAY_CATEGORY_WEEK_HISTORY')");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Tables", -1);
for (int intRow = 0; intRow < DataSet.Tables["Tables"].Rows.Count; intRow++)
{
strQry.Clear();
strQry.AppendLine(" DELETE FROM " + strTableNoToLoad + ".dbo." + DataSet.Tables["Tables"].Rows[intRow]["TABLE_NAME"].ToString());
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
if (DataSet.Tables["Columns"] != null)
{
DataSet.Tables.Remove("Columns");
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COLUMN_NAME ");
strQry.AppendLine(",DATA_TYPE ");
strQry.AppendLine(" FROM " + strTableNoToLoad + ".INFORMATION_SCHEMA.COLUMNS ");
strQry.AppendLine(" WHERE TABLE_NAME = '" + DataSet.Tables["Tables"].Rows[intRow]["TABLE_NAME"].ToString() + "'");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" ORDINAL_POSITION ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Columns", -1);
strQryCol.Clear();
for (int intCol = 0; intCol < DataSet.Tables["Columns"].Rows.Count; intCol++)
{
if (intCol == 0)
{
strQryCol.AppendLine(" " + DataSet.Tables["Columns"].Rows[intCol]["COLUMN_NAME"].ToString());
}
else
{
strQryCol.AppendLine("," + DataSet.Tables["Columns"].Rows[intCol]["COLUMN_NAME"].ToString());
}
}
strQry.Clear();
if (DataSet.Tables["Tables"].Rows[intRow]["TABLE_NAME"].ToString() == "LEAVE_HISTORY")
{
strQry.AppendLine("SET IDENTITY_INSERT " + strTableNoToLoad + ".dbo." + DataSet.Tables["Tables"].Rows[intRow]["TABLE_NAME"].ToString() + " ON");
}
strQry.AppendLine(" INSERT INTO " + strTableNoToLoad + ".dbo." + DataSet.Tables["Tables"].Rows[intRow]["TABLE_NAME"].ToString());
strQry.AppendLine("(");
strQry.Append(strQryCol.ToString());
strQry.AppendLine(")");
strQry.AppendLine(" SELECT ");
strQry.Append(strQryCol.ToString().Replace("COMPANY_NO", "18"));
strQry.AppendLine(" FROM InteractPayroll_00013.dbo." + DataSet.Tables["Tables"].Rows[intRow]["TABLE_NAME"].ToString());
strQry.AppendLine(" WHERE PAY_PERIOD_DATE < '" + parstrDate + "'");
if (DataSet.Tables["Tables"].Rows[intRow]["TABLE_NAME"].ToString() == "LEAVE_HISTORY")
{
strQry.AppendLine("SET IDENTITY_INSERT " + strTableNoToLoad + ".dbo." + DataSet.Tables["Tables"].Rows[intRow]["TABLE_NAME"].ToString() + " OFF");
}
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
strQry.Clear();
strQry.AppendLine(" UPDATE EN");
strQry.AppendLine(" SET ");
strQry.AppendLine(" EMPLOYEE_LAST_RUNDATE = ");
strQry.AppendLine(" CASE ");
strQry.AppendLine(" WHEN E.EMPLOYEE_LAST_RUNDATE < TEMP_TABLE.MAX_PAY_PERIOD_DATE ");
strQry.AppendLine(" THEN E.EMPLOYEE_LAST_RUNDATE");
strQry.AppendLine(" ELSE ");
strQry.AppendLine(" CASE ");
strQry.AppendLine(" WHEN E.PAY_CATEGORY_TYPE = 'W' ");
strQry.AppendLine(" THEN TEMP_TABLE.MAX_PAY_PERIOD_DATE ");
strQry.AppendLine(" ELSE '" + EndOfMonthDateTime.AddDays(1).AddMonths(-1).AddDays(-1).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" END ");
strQry.AppendLine(" END, ");
strQry.AppendLine(" EMPLOYEE_ENDDATE = ");
strQry.AppendLine(" CASE ");
strQry.AppendLine(" WHEN E.EMPLOYEE_ENDDATE IS NULL ");
strQry.AppendLine(" THEN E.EMPLOYEE_ENDDATE");
strQry.AppendLine(" WHEN E.EMPLOYEE_ENDDATE < TEMP_TABLE.MAX_PAY_PERIOD_DATE ");
strQry.AppendLine(" THEN E.EMPLOYEE_ENDDATE");
strQry.AppendLine(" ELSE ");
strQry.AppendLine(" CASE ");
strQry.AppendLine(" WHEN E.PAY_CATEGORY_TYPE = 'W' ");
strQry.AppendLine(" THEN TEMP_TABLE.MAX_PAY_PERIOD_DATE ");
strQry.AppendLine(" ELSE '" + EndOfMonthDateTime.AddDays(1).AddMonths(-1).AddDays(-1).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" END ");
strQry.AppendLine(" END ");
strQry.AppendLine(" FROM " + strTableNoToLoad + ".dbo.EMPLOYEE EN");
strQry.AppendLine(" INNER JOIN InteractPayroll_00013.dbo.EMPLOYEE E");
strQry.AppendLine(" ON EN.EMPLOYEE_NO = E.EMPLOYEE_NO ");
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" COMPANY_NO ");
strQry.AppendLine(",MAX(PAY_PERIOD_DATE) AS MAX_PAY_PERIOD_DATE ");
strQry.AppendLine(" FROM InteractPayroll_00013.dbo.PAY_CATEGORY_PERIOD_HISTORY ");
strQry.AppendLine(" WHERE COMPANY_NO = 13 ");
strQry.AppendLine(" AND PAY_PERIOD_DATE < '" + parstrDate + "'");
strQry.AppendLine(" AND RUN_TYPE = 'P' ");
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" COMPANY_NO ) AS TEMP_TABLE ");
strQry.AppendLine(" ON E.COMPANY_NO = TEMP_TABLE.COMPANY_NO ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
//2018-02-03
strQry.Clear();
strQry.AppendLine(" UPDATE " + strTableNoToLoad + ".dbo.PAY_CATEGORY");
strQry.AppendLine(" SET LAST_UPLOAD_DATETIME = '" + DateTime.ParseExact(parstrDate, "yyyy-MM-dd", null).AddDays(35).AddMinutes(15).ToString("yyyy-MM-dd HH:mm:ss") + "'");
//strQry.AppendLine(" WHERE NOT PAY_CATEGORY_TYPE = 'S'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
public void RunBackupJob()
{
bool blnBackupCompletedSuccessful = false;
string strEmail = "";
string strEmailPassword = "";
try
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(pvtstrLogFileDirectory + "DailyDBBackup_Info.txt", true))
{
file.WriteLine("RunBackupJob ... " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
clsDBConnectionObjects = null;
clsDBConnectionObjects = new InteractPayroll.clsDBConnectionObjects();
DataSet DataSet = new System.Data.DataSet();
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" BACKUP_DATABASE_PATH");
strQry.AppendLine(",BACKUP_GMAIL_ACCOUNT");
strQry.AppendLine(",BACKUP_GMAIL_PASSWORD");
strQry.AppendLine(",BACKUP_S3_BUCKET_NAME");
strQry.AppendLine(",BACKUP_DB_IND");
strQry.AppendLine(" FROM InteractPayroll.dbo.BACKUP_DATABASE_PATH");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Directory", -1);
string strFileDirectory = DataSet.Tables["Directory"].Rows[0]["BACKUP_DATABASE_PATH"].ToString();
strEmail = DataSet.Tables["Directory"].Rows[0]["BACKUP_GMAIL_ACCOUNT"].ToString();
strEmailPassword = DataSet.Tables["Directory"].Rows[0]["BACKUP_GMAIL_PASSWORD"].ToString();
pvtstrBucketName = DataSet.Tables["Directory"].Rows[0]["BACKUP_S3_BUCKET_NAME"].ToString();
string strDatabaseName = "InteractPayroll";
string strBackupFileName = strDatabaseName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + "_Daily.bak";
//Move Files Offshore
client = new AmazonS3Client();
string[] fileInteractPayrollPaths;
FileInfo fileinfo;
bool blnFound = false;
if (DataSet.Tables["Directory"].Rows[0]["BACKUP_DB_IND"].ToString() == "Y" ||
DateTime.Now.DayOfWeek == DayOfWeek.Saturday)
{
//Backup InteractPayroll Master
fileInteractPayrollPaths = Directory.GetFiles(@strFileDirectory, @"*InteractPayroll_" + DateTime.Now.ToString("yyyyMMdd") + "_*.bak");
if (fileInteractPayrollPaths.Length == 0)
{
strQry.Clear();
strQry.AppendLine("BACKUP DATABASE " + strDatabaseName + " TO DISK = '" + strFileDirectory + "\\" + strBackupFileName + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.BACKUP_DATABASE_PATH");
strQry.AppendLine(" SET BACKUP_DB_IND = 'N'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
}
fileInteractPayrollPaths = Directory.GetFiles(@strFileDirectory, @"*InteractPayroll_" + DateTime.Now.ToString("yyyyMMdd") + "_*.bak");
if (fileInteractPayrollPaths.Length > 0)
{
fileinfo = new FileInfo(fileInteractPayrollPaths[0]);
blnFound = FindFile(fileinfo.Name);
if (blnFound == false)
{
#if (DEBUG)
//Don't Want To Write Test DBs to Backup Bucket
#else
TransferUtilityUploadRequest fileTransferUtilityRequest = new TransferUtilityUploadRequest
{
BucketName = pvtstrBucketName,
FilePath = fileInteractPayrollPaths[0]
};
fileTransferUtilityRequest.Metadata.Add("fileName", fileinfo.Name);
fileTransferUtilityRequest.Metadata.Add("fileDesc", fileinfo.Name);
TransferUtility fileTransferUtility = new TransferUtility(client);
fileTransferUtility.Upload(fileTransferUtilityRequest);
#endif
}
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO ");
strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" WHERE BACKUP_DB_IND = 1");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "BackUpInfo", -1);
//First Complete Todays Backups
for (int intRow = 0; intRow < DataSet.Tables["BackUpInfo"].Rows.Count; intRow++)
{
strDatabaseName = "InteractPayroll_" + Convert.ToInt32(DataSet.Tables["BackUpInfo"].Rows[intRow]["COMPANY_NO"]).ToString("00000");
strBackupFileName = strDatabaseName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + "_Daily.bak";
strQry.Clear();
strQry.AppendLine("BACKUP DATABASE " + strDatabaseName + " TO DISK = '" + strFileDirectory + "\\" + strBackupFileName + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" SET BACKUP_DB_IND = 0");
strQry.AppendLine(" WHERE COMPANY_NO = " + DataSet.Tables["BackUpInfo"].Rows[intRow]["COMPANY_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
string[] fileOldDBPaths = Directory.GetFiles(strFileDirectory, strDatabaseName + "_*_Daily.bak");
if (fileOldDBPaths.Length > 5)
{
//Delete Old
Array.Sort(fileOldDBPaths);
for (int intFileRow = 0; intFileRow < fileOldDBPaths.Length - 5; intFileRow++)
{
File.Delete(fileOldDBPaths[intFileRow]);
}
}
}
//Complete Todays Offsite DB Backups
string[] fileTodaysPaths = Directory.GetFiles(@strFileDirectory, @"*_" + DateTime.Now.ToString("yyyyMMdd") + "*.bak");
if (fileTodaysPaths.Length > 0)
{
for (int intFileCount = 0; intFileCount < fileTodaysPaths.Length; intFileCount++)
{
fileinfo = new FileInfo(fileTodaysPaths[intFileCount]);
blnFound = FindFile(fileinfo.Name);
if (blnFound == false)
{
#if (DEBUG)
//Don't Want To Write Test DBs to Backup Bucket
#else
TransferUtilityUploadRequest fileTransferUtilityRequest = new TransferUtilityUploadRequest
{
BucketName = pvtstrBucketName,
FilePath = fileTodaysPaths[intFileCount]
};
//LifecycleConfiguration a = new LifecycleConfiguration();
fileTransferUtilityRequest.Metadata.Add("fileName", fileinfo.Name);
fileTransferUtilityRequest.Metadata.Add("fileDesc", fileinfo.Name);
/////fileTransferUtilityRequest.Metadata.Add("expires","Tue, 16 Feb 2016 16:00:00 GMT");
TransferUtility fileTransferUtility = new TransferUtility(client);
fileTransferUtility.Upload(fileTransferUtilityRequest);
#endif
}
}
}
//Check Yesterdays DB Backups
string[] fileYesterdaysPaths = Directory.GetFiles(@strFileDirectory, @"*_" + DateTime.Now.AddDays(-1).ToString("yyyyMMdd") + "*.bak");
if (fileYesterdaysPaths.Length > 0)
{
for (int intFileCount = 0; intFileCount < fileYesterdaysPaths.Length; intFileCount++)
{
fileinfo = new FileInfo(fileYesterdaysPaths[intFileCount]);
blnFound = FindFile(fileinfo.Name);
if (blnFound == false)
{
#if (DEBUG)
//Don't Want To Write Test DBs to Backup Bucket
#else
TransferUtilityUploadRequest fileTransferUtilityRequest = new TransferUtilityUploadRequest
{
BucketName = pvtstrBucketName,
FilePath = fileYesterdaysPaths[intFileCount]
};
fileTransferUtilityRequest.Metadata.Add("fileName", fileinfo.Name);
fileTransferUtilityRequest.Metadata.Add("fileDesc", fileinfo.Name);
TransferUtility fileTransferUtility = new TransferUtility(client);
fileTransferUtility.Upload(fileTransferUtilityRequest);
#endif
}
}
}
bool blnFirstPass = true;
string[] fileOldDaysPaths;
RunBackupJob_Continue:
if (blnFirstPass == true)
{
//Clean Up Old Backups Older than 1 Month Old
fileOldDaysPaths = Directory.GetFiles(@strFileDirectory, @"*_" + DateTime.Now.AddMonths(-1).ToString("yyyyMM") + "*.bak");
}
else
{
fileOldDaysPaths = Directory.GetFiles(@strFileDirectory, @"*_" + DateTime.Now.AddMonths(-2).ToString("yyyyMM") + "*.bak");
}
foreach (string file in fileOldDaysPaths)
{
int intOffset = file.IndexOf("InteractPayroll_");
if (intOffset > -1)
{
if (file.Length > intOffset + 30)
{
try
{
//Client Databases
DateTime myFileDateTime = DateTime.ParseExact(file.Substring(intOffset + 22, 8), "yyyyMMdd", null).AddMonths(1);
if (myFileDateTime < DateTime.Now)
{
File.Delete(file);
}
}
catch
{
//InteractPayroll Master
try
{
DateTime myFileDateTime = DateTime.ParseExact(file.Substring(intOffset + 16, 8), "yyyyMMdd", null).AddMonths(1);
if (myFileDateTime < DateTime.Now)
{
File.Delete(file);
}
}
catch
{
}
}
}
}
}
if (blnFirstPass == true)
{
blnFirstPass = false;
goto RunBackupJob_Continue;
}
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.BACKUP_DATABASE_PATH");
strQry.AppendLine(" SET BACKUP_LAST_DATETIME = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
blnBackupCompletedSuccessful = true;
}
catch (Exception ex)
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(pvtstrLogFileDirectory + "DailyDBBackup_Error.txt", true))
{
file.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " Error = " + ex.Message);
}
}
finally
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(pvtstrLogFileDirectory + "DailyDBBackup_Info.txt", true))
{
file.WriteLine("RunBackupJob Setting Timer ... " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
//Set Next Time for Timer to Fire
double tickTime = Calculate_Next_Time_Timer_Fires(blnBackupCompletedSuccessful);
tmrTimer.Interval = tickTime;
tmrTimer.Start();
if (client != null)
{
client.Dispose();
client = null;
}
if (blnBackupCompletedSuccessful == true ||
DateTime.Now.Hour == 7)
{
var smtp = new SmtpClient();
try
{
//Email
var fromAddress = new MailAddress(strEmail, "DB Backup Server");
var toAddress = new MailAddress(strEmail, "Errol Le Roux");
string subject = "Daily DB Backups - " + DateTime.Now.ToString("dd MMMM yyyy");
string body = "Backup Successful";
if (blnBackupCompletedSuccessful == false)
{
body = "Backup UNSUCCESSFUL";
}
smtp.Host = "smtp.gmail.com";
smtp.Port = 587;
smtp.EnableSsl = true;
smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
smtp.UseDefaultCredentials = false;
smtp.Credentials = new NetworkCredential(fromAddress.Address, strEmailPassword);
var message = new MailMessage(fromAddress, toAddress);
message.Subject = subject;
message.Body = body;
smtp.Send(message);
using (System.IO.StreamWriter file = new System.IO.StreamWriter(pvtstrLogFileDirectory + "DailyDBBackup_Info.txt", true))
{
file.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " Email Sent Successfully");
}
}
catch (Exception ex)
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(pvtstrLogFileDirectory + "DailyDBBackup_Error.txt", true))
{
file.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " Email Error = " + ex.Message);
}
}
finally
{
smtp.Dispose();
smtp = null;
}
}
}
}
public 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[] Get_Form_Records(Int64 parInt64CompanyNo)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
//So That Delete Will Happen
string strEmployeeNoIn = "-1";
//Insert Records
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" L.PAY_CATEGORY_TYPE");
strQry.AppendLine(",L.EMPLOYEE_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY L ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
strQry.AppendLine(" ON L.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND L.EMPLOYEE_NO = E.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL ");
//Add 180 Days (Withinn 6 Months)
strQry.AppendLine(" AND DATEADD(DD,180,L.PAY_PERIOD_DATE) >= GETDATE() ");
strQry.AppendLine(" LEFT JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND PAY_CATEGORY_TYPE IN ('W','S')");
//Take-On Balance / Take-On Balance (Previous Year)
strQry.AppendLine(" AND PROCESS_NO IN (99,100)");
//Normal Leave / Sick Leave
strQry.AppendLine(" AND EARNING_NO IN (200,201)");
strQry.AppendLine(" AND LEAVE_ACCUM_DAYS > 0) AS TEMP_LEAVE");
strQry.AppendLine(" ON L.PAY_CATEGORY_TYPE = TEMP_LEAVE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND L.EMPLOYEE_NO = TEMP_LEAVE.EMPLOYEE_NO ");
strQry.AppendLine(" WHERE L.COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE IN ('W','S')");
//Take-On Balance
strQry.AppendLine(" AND L.PROCESS_NO = 99");
//Normal Leave / Sick Leave
strQry.AppendLine(" AND L.EARNING_NO IN (200,201)");
strQry.AppendLine(" AND L.LEAVE_ACCUM_DAYS = 0");
strQry.AppendLine(" AND L.LEAVE_PAID_DAYS = 0");
//No Take-On Leave Balances
strQry.AppendLine(" AND TEMP_LEAVE.PAY_CATEGORY_TYPE IS NULL");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" L.PAY_CATEGORY_TYPE ");
strQry.AppendLine(",L.EMPLOYEE_NO ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeeTemp", parInt64CompanyNo);
for (int intRow = 0; intRow < DataSet.Tables["EmployeeTemp"].Rows.Count; intRow++)
{
if (intRow == 0)
{
strEmployeeNoIn = DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString();
}
else
{
strEmployeeNoIn += "," + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString();
}
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PREV_NORMAL_LEAVE_DAYS");
strQry.AppendLine(",NORMAL_LEAVE_DAYS");
strQry.AppendLine(",SICK_LEAVE_DAYS");
strQry.AppendLine(",LEAVE_EFFECTIVE_DATE)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EIH.COMPANY_NO");
strQry.AppendLine(",EIH.EMPLOYEE_NO");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",EIH.PAY_PERIOD_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY EIH");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON LTO ");
strQry.AppendLine(" ON EIH.COMPANY_NO = LTO.COMPANY_NO ");
strQry.AppendLine(" AND EIH.EMPLOYEE_NO = LTO.EMPLOYEE_NO ");
strQry.AppendLine(" WHERE EIH.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EIH.RUN_TYPE = 'T' ");
strQry.AppendLine(" AND EIH.EMPLOYEE_NO = " + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND EIH.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine(" AND LTO.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
//Delete any Employee That Have been on System for longer Than 180 Days
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND NOT EMPLOYEE_NO IN (" + strEmployeeNoIn + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",E.EMPLOYEE_CODE");
strQry.AppendLine(",E.EMPLOYEE_NAME");
strQry.AppendLine(",E.EMPLOYEE_SURNAME");
strQry.AppendLine(",LT.PREV_NORMAL_LEAVE_DAYS");
strQry.AppendLine(",LT.NORMAL_LEAVE_DAYS");
strQry.AppendLine(",LT.SICK_LEAVE_DAYS");
strQry.AppendLine(",LT.LEAVE_EFFECTIVE_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON LT ");
strQry.AppendLine(" ON E.COMPANY_NO = LT.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = LT.EMPLOYEE_NO ");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE IN ('W','S')");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY E.EMPLOYEE_CODE");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parInt64CompanyNo);
DataSet.Tables.Add("PayrollType");
DataTable PayrollTypeDataTable = new DataTable("PayrollType");
DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));
if (DataSet.Tables["Employee"].Rows.Count > 0)
{
DataView PayrollTypeDataView = new DataView(DataSet.Tables["Employee"],
"PAY_CATEGORY_TYPE = 'W'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Wages";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
PayrollTypeDataView = null;
PayrollTypeDataView = new DataView(DataSet.Tables["Employee"],
"PAY_CATEGORY_TYPE = 'S'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Salaries";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
}
DataSet.AcceptChanges();
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] 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 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 byte[] Insert_TakeOn_Records(Int64 parint64CompanyNo, string parstrPayrollType, string parstrTimeAttendInd, DateTime parDateTime, string parstrEmployeeNoIn, Int64 parint64CurrentUserNo)
{
StringBuilder strQry = new StringBuilder();
StringBuilder strFieldNamesInitialised = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_HISTORY");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_PERIOD_DATE");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",RUN_NO");
strQry.AppendLine(",PAY_PERIOD_DATE_FROM");
//Return strings for field that need to be Initialised to Zero
strFieldNamesInitialised.Clear();
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("PAY_CATEGORY_PERIOD_HISTORY", ref strQry, ref strFieldNamesInitialised, parint64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" EPC.COMPANY_NO");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",'T'");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(",1");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
//Append Initialised Numeric Fields
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_HISTORY PCPH");
strQry.AppendLine(" ON EPC.COMPANY_NO = PCPH.COMPANY_NO ");
strQry.AppendLine(" AND PCPH.PAY_PERIOD_DATE = '" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = PCPH.PAY_CATEGORY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = PCPH.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND PCPH.RUN_TYPE = 'T'");
strQry.AppendLine(" AND PCPH.RUN_NO = 1");
strQry.AppendLine(" WHERE EPC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EPC.EMPLOYEE_NO IN (" + parstrEmployeeNoIn + ")");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y' ");
strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL");
//PAY_CATEGORY_PERIOD_HISTORY Record does Not Exist
strQry.AppendLine(" AND PCPH.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
//Initialise Tax StartDate
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE");
strQry.AppendLine(" SET ");
strQry.AppendLine(" EMPLOYEE_TAKEON_IND = 'Y'");
strQry.AppendLine(",EMPLOYEE_LAST_RUNDATE = '" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",EMPLOYEE_TAX_STARTDATE = '" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND EMPLOYEE_NO IN (" + parstrEmployeeNoIn + ")");
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_HISTORY");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_PERIOD_DATE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",RUN_NO");
strQry.AppendLine(",HOURLY_RATE");
strQry.AppendLine(",OVERTIME_VALUE_BF");
strQry.AppendLine(",OVERTIME_VALUE_CF");
strQry.AppendLine(",DEFAULT_IND)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",'T'");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",1");
strQry.AppendLine(",HOURLY_RATE");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",DEFAULT_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO IN (" + parstrEmployeeNoIn + ")");
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND DEFAULT_IND = 'Y' ");
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_PERIOD_DATE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",RUN_NO");
strQry.AppendLine(",CLOSE_IND");
strQry.AppendLine(",PAYSLIP_IND");
//Return strings for field that need to be Initialised to Zero
strFieldNamesInitialised.Clear();
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_INFO_HISTORY", ref strQry, ref strFieldNamesInitialised, parint64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",'T'");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(",1");
strQry.AppendLine(",'N'");
strQry.AppendLine(",'Y'");
//Append Initialised Numeric Fields
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE ");
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(" AND EMPLOYEE_NO IN (");
strQry.AppendLine(parstrEmployeeNoIn);
strQry.AppendLine(")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_HISTORY");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_PERIOD_DATE");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",RUN_NO");
//Return strings for field that need to be Initialised to Zero
strFieldNamesInitialised.Clear();
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_DEDUCTION_HISTORY", ref strQry, ref strFieldNamesInitialised, parint64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" ED.COMPANY_NO");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(",ED.EMPLOYEE_NO");
strQry.AppendLine(",'T'");
strQry.AppendLine(",ED.DEDUCTION_NO");
strQry.AppendLine(",ED.DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",1");
//Append Initialised Numeric Fields
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED");
strQry.AppendLine(",InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" WHERE ED.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND ED.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND ED.EMPLOYEE_NO = E.EMPLOYEE_NO");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" AND ED.EMPLOYEE_NO IN (");
strQry.AppendLine(parstrEmployeeNoIn);
strQry.AppendLine(")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_HISTORY");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_PERIOD_DATE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",RUN_NO");
//Return strings for field that need to be Initialised to Zero
strFieldNamesInitialised.Clear();
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_EARNING_HISTORY", ref strQry, ref strFieldNamesInitialised, parint64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",'T'");
strQry.AppendLine(",EN.EARNING_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(",1");
//Append Initialised Numeric Fields
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING EN");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON EN.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" ON EN.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");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" AND E.EMPLOYEE_NO IN (");
strQry.AppendLine(parstrEmployeeNoIn);
strQry.AppendLine(")");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.COMPANY C");
strQry.AppendLine(" ON EN.COMPANY_NO = C.COMPANY_NO");
strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" LEFT JOIN ");
strQry.AppendLine("(SELECT DISTINCT ");
strQry.AppendLine(" PAY_CATEGORY_NO ");
strQry.AppendLine(",PAY_PUBLIC_HOLIDAY_IND ");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL) AS PUBLIC_HOLIDAY_TABLE ");
strQry.AppendLine(" ON EPC.PAY_CATEGORY_NO = PUBLIC_HOLIDAY_TABLE.PAY_CATEGORY_NO");
strQry.AppendLine(" WHERE EN.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND EN.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" AND ((EN.EARNING_NO IN (1,2,3,4,5,6,7,9))");
//Public Holiday - Company Paid
strQry.AppendLine(" OR (EN.EARNING_NO = 8");
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = 'W'");
strQry.AppendLine(" AND PUBLIC_HOLIDAY_TABLE.PAY_PUBLIC_HOLIDAY_IND = 'Y')");
//LEAVE_PAID_IND = 'Y' for all Earning Records Except Leave Marked as UnPaid
strQry.AppendLine(" OR (EN.EARNING_NO >= 200 AND EN.LEAVE_PERCENTAGE > 0))");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
//All Linked Records that are NOT Default Records
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_HISTORY");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_PERIOD_DATE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",RUN_NO");
//Return strings for field that need to be Initialised to Zero
strFieldNamesInitialised.Clear();
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_EARNING_HISTORY", ref strQry, ref strFieldNamesInitialised, parint64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",EE.EMPLOYEE_NO");
strQry.AppendLine(",'T'");
strQry.AppendLine(",EE.EARNING_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(",1");
//Append Initialised Numeric Fields
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE");
strQry.AppendLine(",InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(",InteractPayroll_#CompanyNo#.dbo.EARNING E");
strQry.AppendLine(" WHERE EE.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND EE.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND EPC.EMPLOYEE_NO = EE.EMPLOYEE_NO");
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" AND EE.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND EE.EARNING_NO = E.EARNING_NO ");
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" AND EE.EMPLOYEE_NO IN (" + parstrEmployeeNoIn + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
//Normal Leave
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_PERIOD_DATE");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",LEAVE_DESC");
strQry.AppendLine(",LEAVE_ACCUM_DAYS");
strQry.AppendLine(",LEAVE_FROM_DATE");
strQry.AppendLine(",LEAVE_TO_DATE");
strQry.AppendLine(",LEAVE_PAID_DAYS");
//Errol Added 2012-01-10
strQry.AppendLine(",LEAVE_OPTION");
strQry.AppendLine(",LEAVE_DAYS_DECIMAL");
strQry.AppendLine(",LEAVE_HOURS_DECIMAL");
strQry.AppendLine(",PROCESS_NO)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",200");
strQry.AppendLine(",'Take-On Balance'");
strQry.AppendLine(",0");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",0");
//Errol Added 2012-01-10
strQry.AppendLine(",'D'");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",99");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" AND E.EMPLOYEE_NO IN (" + parstrEmployeeNoIn + ")");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parint64CompanyNo);
//Sick Leave
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_PERIOD_DATE");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",LEAVE_DESC");
strQry.AppendLine(",LEAVE_ACCUM_DAYS");
strQry.AppendLine(",LEAVE_FROM_DATE");
strQry.AppendLine(",LEAVE_TO_DATE");
strQry.AppendLine(",LEAVE_PAID_DAYS");
//Errol Added 2012-01-10
strQry.AppendLine(",LEAVE_OPTION");
strQry.AppendLine(",LEAVE_DAYS_DECIMAL");
strQry.AppendLine(",LEAVE_HOURS_DECIMAL");
strQry.AppendLine(",PROCESS_NO)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",201");
strQry.AppendLine(",'Take-On Balance'");
strQry.AppendLine(",0");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",'" + parDateTime.ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(",0");
//Errol Added 2012-01-10
strQry.AppendLine(",'D'");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",99");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" AND E.EMPLOYEE_NO IN (" + parstrEmployeeNoIn + ")");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
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_Form_Records(parint64CompanyNo, parstrTimeAttendInd);
return(bytCompress);
}