public int Insert_Record(byte[] byteCompressedDataSet)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteCompressedDataSet);
StringBuilder strQry = new StringBuilder();
int intDeviceNo = 0;
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" MAX(DEVICE_NO) AS MAX_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true)
{
intDeviceNo = 1;
}
else
{
intDeviceNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1;
}
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE");
strQry.AppendLine("(DEVICE_NO");
strQry.AppendLine(",DEVICE_DESC");
strQry.AppendLine(",DEVICE_USAGE");
strQry.AppendLine(",TIME_ATTEND_CLOCK_FIRST_LAST_IND");
strQry.AppendLine(",LOCK_OUT_MINUTES");
strQry.AppendLine(",CLOCK_IN_OUT_PARM");
strQry.AppendLine(",CLOCK_IN_RANGE_FROM");
strQry.AppendLine(",CLOCK_IN_RANGE_TO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",LAN_WAN_IND");
strQry.AppendLine(",FAR_REQUESTED)");
strQry.AppendLine(" VALUES");
strQry.AppendLine("(" + intDeviceNo);
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["DEVICE_DESC"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["DEVICE_USAGE"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["TIME_ATTEND_CLOCK_FIRST_LAST_IND"].ToString()));
strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["LOCK_OUT_MINUTES"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["CLOCK_IN_OUT_PARM"].ToString()));
strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["CLOCK_IN_RANGE_FROM"].ToString());
strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["CLOCK_IN_RANGE_TO"].ToString());
strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["COMPANY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clock"].Rows[0]["LAN_WAN_IND"].ToString()));
strQry.AppendLine("," + DataSet.Tables["Clock"].Rows[0]["FAR_REQUESTED"].ToString() + ")");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
return(intDeviceNo);
}
public byte[] Get_Form_Records(int parintCurrentUserNo, string parstrAccessInd)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
DataSet DataSet = new DataSet();
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" DEVICE_NO");
strQry.AppendLine(",DEVICE_DESC");
strQry.AppendLine(",DEVICE_USAGE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" DEVICE_DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Device");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" C.COMPANY_NO");
strQry.AppendLine(",C.COMPANY_DESC");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");
if (parstrAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UC ");
strQry.AppendLine(" ON UC.USER_NO = " + parintCurrentUserNo);
strQry.AppendLine(" AND UC.COMPANY_NO = C.COMPANY_NO ");
//2013-07-10
strQry.AppendLine(" AND UC.COMPANY_ACCESS_IND = 'A'");
}
strQry.AppendLine(" ORDER BY C.COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company");
if (DataSet.Tables["Company"].Rows.Count > 0)
{
byte[] bytTempCompress = Get_Company_Records(Convert.ToInt64(DataSet.Tables["Company"].Rows[0]["COMPANY_NO"]));
DataSet TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
DataSet.Merge(TempDataSet);
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public void Update_Record(Int64 parint64CompanyNo, byte[] parbyteDataSet)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
for (int intRow = 0; intRow < parDataSet.Tables["Employee"].Rows.Count; intRow++)
{
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON");
strQry.AppendLine(" SET ");
strQry.AppendLine(" PREV_NORMAL_LEAVE_DAYS = " + Convert.ToDouble(parDataSet.Tables["Employee"].Rows[intRow]["PREV_NORMAL_LEAVE_DAYS"]));
strQry.AppendLine(",NORMAL_LEAVE_DAYS = " + Convert.ToDouble(parDataSet.Tables["Employee"].Rows[intRow]["NORMAL_LEAVE_DAYS"]));
strQry.AppendLine(",SICK_LEAVE_DAYS = " + Convert.ToDouble(parDataSet.Tables["Employee"].Rows[intRow]["SICK_LEAVE_DAYS"]));
strQry.AppendLine(",LEAVE_EFFECTIVE_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Employee"].Rows[intRow]["LEAVE_EFFECTIVE_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" WHERE COMPANY_NO = " + Convert.ToInt32(parDataSet.Tables["Employee"].Rows[intRow]["COMPANY_NO"]));
strQry.AppendLine(" AND EMPLOYEE_NO = " + Convert.ToInt32(parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"]));
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 Cleanup_Client_DataBase_Files(byte[] parByteArrayDataSet)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
DataSet parClientDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parByteArrayDataSet);
StringBuilder strQry = new StringBuilder();
for (int intRow = 0; intRow < parClientDataSet.Tables["FileToDelete"].Rows.Count; intRow++)
{
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_NAME"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_NAME"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
}
public void Update_PayCategory_Last_Upload_DateTime(Int64 parint64CompanyNo, string strUploadDateTime, byte[] parbyteDataSet)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
StringBuilder strQry = new StringBuilder();
DataSet ClientDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
for (int intPayCategoryRow = 0; intPayCategoryRow < ClientDataSet.Tables["PayCategoryUpload"].Rows.Count; intPayCategoryRow++)
{
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayrollClient.dbo.PAY_CATEGORY");
strQry.AppendLine(" SET LAST_UPLOAD_DATETIME = '" + strUploadDateTime + "'");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + ClientDataSet.Tables["PayCategoryUpload"].Rows[intPayCategoryRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(ClientDataSet.Tables["PayCategoryUpload"].Rows[intPayCategoryRow]["PAY_CATEGORY_TYPE"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
}
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 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 byte[] Get_Users(string parstrProgramFromInd, Int64 parCurrentUserNo, string parstrCurrentUserAccessInd)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",U.USER_ID");
strQry.AppendLine(",U.FIRSTNAME");
strQry.AppendLine(",U.SURNAME");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND <> 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
if (parstrCurrentUserAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" UCA.COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo);
strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE ");
strQry.AppendLine(" ON UCA.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
}
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",U.USER_ID");
strQry.AppendLine(",U.FIRSTNAME");
strQry.AppendLine(",U.SURNAME");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",UCA.COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND <> 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
if (parstrCurrentUserAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" UCA.COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo);
strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE ");
strQry.AppendLine(" ON UCA.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
}
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",UCA.COMPANY_NO");
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",UCA.COMPANY_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "UserCompany", -1);
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" CL.COMPANY_NO");
strQry.AppendLine(",CL.COMPANY_DESC");
strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK CL");
if (parstrCurrentUserAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" UCA.COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo);
strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE ");
strQry.AppendLine(" ON CL.COMPANY_NO = TEMP_TABLE.COMPANY_NO");
}
strQry.AppendLine(" GROUP BY");
strQry.AppendLine(" CL.COMPANY_NO");
strQry.AppendLine(",CL.COMPANY_DESC");
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" CL.COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", -1);
if (DataSet.Tables["User"].Rows.Count > 0)
{
byte[] bytTempCompress = Get_User_Menus(parstrProgramFromInd, Convert.ToInt32(DataSet.Tables["User"].Rows[0]["USER_NO"]));
DataSet TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
DataSet.Merge(TempDataSet);
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Update_Records(Int64 parInt64CompanyNo, byte[] parbyteDataSet, string parstrFromProgram)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
DataSet DataSet = new DataSet();
StringBuilder strQry = new StringBuilder();
if (parInt64CompanyNo == 999999)
{
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Companies", -1);
}
strQry.Clear();
for (int intRow = 0; intRow < parDataSet.Tables["PaidHoliday"].Rows.Count; intRow++)
{
if (parDataSet.Tables["PaidHoliday"].Rows[intRow].RowState == DataRowState.Added)
{
strQry.Clear();
if (parInt64CompanyNo == 999999)
{
strQry.AppendLine(" INSERT INTO InteractPayroll.dbo.PUBLIC_HOLIDAY");
}
else
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY ");
}
strQry.AppendLine("(PUBLIC_HOLIDAY_DATE");
strQry.AppendLine(",PUBLIC_HOLIDAY_DESC)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("('" + Convert.ToDateTime(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DESC"].ToString()) + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
if (parInt64CompanyNo == 999999)
{
for (int intCompanyRow = 0; intCompanyRow < DataSet.Tables["Companies"].Rows.Count; intCompanyRow++)
{
Int64 Int64CompanyNo = Convert.ToInt64(DataSet.Tables["Companies"].Rows[intCompanyRow]["COMPANY_NO"]);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY ");
strQry.AppendLine("(PUBLIC_HOLIDAY_DATE");
strQry.AppendLine(",PUBLIC_HOLIDAY_DESC)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("('" + Convert.ToDateTime(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DESC"].ToString()) + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Int64CompanyNo);
}
}
}
else
{
if (parDataSet.Tables["PaidHoliday"].Rows[intRow].RowState == DataRowState.Modified)
{
strQry.Clear();
if (parInt64CompanyNo == 999999)
{
strQry.AppendLine(" UPDATE InteractPayroll.dbo.PUBLIC_HOLIDAY");
}
else
{
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY");
}
strQry.AppendLine(" SET ");
strQry.AppendLine(" PUBLIC_HOLIDAY_DESC = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DESC"].ToString()));
strQry.AppendLine(",PUBLIC_HOLIDAY_DATE = '" + Convert.ToDateTime(parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" WHERE PUBLIC_HOLIDAY_NO = " + parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
else
{
//Deleted
strQry.Clear();
if (parInt64CompanyNo == 999999)
{
strQry.AppendLine(" DELETE FROM InteractPayroll.dbo.PUBLIC_HOLIDAY");
}
else
{
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY");
}
strQry.AppendLine(" WHERE PUBLIC_HOLIDAY_NO = " + parDataSet.Tables["PaidHoliday"].Rows[intRow]["PUBLIC_HOLIDAY_NO", System.Data.DataRowVersion.Original].ToString());
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
}
}
byte[] bytCompress = Get_Form_Records(parInt64CompanyNo, parstrFromProgram);
DataSet.Dispose();
DataSet = 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(bytCompress);
}
public byte[] Logon_User(string parstrUserInformation)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
bool blnDebug = false;
StringBuilder strQry = new StringBuilder();
string[] strPieces = parstrUserInformation.Split('|');
string strUserId = strPieces[0];
string strPassword = strPieces[1];
byte[] bytCompress;
StreamWriter swStreamWriter;
DataSet DataSet = new DataSet();
DataTable DataTable = new DataTable("ReturnValues");
DataTable.Columns.Add("USER_NO", typeof(Int64));
DataTable.Columns.Add("RESET", typeof(String));
DataTable.Columns.Add("ACCESS_IND", typeof(String));
DataTable.Columns.Add("REBOOT_IND", typeof(String));
DataTable.Columns.Add("MACHINE_NAME", typeof(String));
DataTable.Columns.Add("MACHINE_IP", typeof(String));
DataTable.Columns.Add("NO_USERS_IND", typeof(String));
DataTable.Columns.Add("DOWNLOAD_IND", typeof(String));
DataTable.Columns.Add("DB_CREATE_FOR_ENGINE", typeof(String));
DataSet.Tables.Add(DataTable);
DataRow myDataRow = DataSet.Tables["ReturnValues"].NewRow();
myDataRow["USER_NO"] = -1;
myDataRow["RESET"] = "N";
myDataRow["ACCESS_IND"] = "";
DataSet.Tables["ReturnValues"].Rows.Add(myDataRow);
FileInfo fiFile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "Debug.txt");
if (fiFile.Exists == true)
{
blnDebug = true;
}
fiFile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "busTimeAttendanceLogonLogs.txt");
if (fiFile.Exists == true)
{
File.Delete(AppDomain.CurrentDomain.BaseDirectory + "busTimeAttendanceLogonLogs.txt");
}
if (blnDebug == true)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("Call clsDBConnectionObjects.GetSQLConnectionString");
swStreamWriter.Close();
}
DataSet.Tables["ReturnValues"].Rows[0]["DB_CREATE_FOR_ENGINE"] = clsDBConnectionObjects.GetSQLConnectionString();
if (blnDebug == true)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("After clsDBConnectionObjects.GetSQLConnectionString = " + DataSet.Tables["ReturnValues"].Rows[0]["DB_CREATE_FOR_ENGINE"].ToString());
swStreamWriter.Close();
}
if (clsDBConnectionObjects.GetSQLConnectionString() != "")
{
clsFixInteractPayrollClientDatabase clsFixInteractPayrollClientDatabase = new clsFixInteractPayrollClientDatabase();
clsFixInteractPayrollClientDatabase.Fix_Client_Database();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" UI.USER_NO");
strQry.AppendLine(",UI.SYSTEM_ADMINISTRATOR_IND");
strQry.AppendLine(",UI.RESET");
//A = Administrator, U = User
strQry.AppendLine(",MIN(UC.COMPANY_ACCESS_IND) AS MIN_COMPANY_ACCESS_IND");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID UI");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UC");
strQry.AppendLine(" ON UI.USER_NO = UC.USER_NO");
strQry.AppendLine(" WHERE UI.USER_ID = " + clsDBConnectionObjects.Text2DynamicSQL(strUserId));
strQry.AppendLine(" AND UI.PASSWORD = "******" GROUP BY ");
strQry.AppendLine(" UI.USER_NO");
strQry.AppendLine(",UI.SYSTEM_ADMINISTRATOR_IND");
strQry.AppendLine(",UI.RESET");
try
{
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
}
catch (Exception ex)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("Exception = " + ex.Message);
swStreamWriter.Close();
}
if (blnDebug == true)
{
swStreamWriter = fiFile.AppendText();
swStreamWriter.WriteLine("After Temp = Count = " + DataSet.Tables["Temp"].Rows.Count.ToString());
swStreamWriter.Close();
}
if (DataSet.Tables["Temp"].Rows.Count == 0)
{
DataSet.Tables.Remove(DataSet.Tables["Temp"]);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" USER_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "UserTemp");
if (DataSet.Tables["UserTemp"].Rows.Count > 0)
{
DataSet.Tables.Remove(DataSet.Tables["UserTemp"]);
if ((strUserId == "INTERACT" &&
strPassword == "TCARETNI") ||
(strUserId == "HELENALR" &&
strPassword == "MONSTER"))
{
DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = 0;
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = "S";
}
else
{
DataSet.AcceptChanges();
//Not Found
bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
}
else
{
DataSet.Tables.Remove(DataSet.Tables["UserTemp"]);
if ((strUserId == "INTERACT" &&
strPassword == "TCARETNI") ||
(strUserId == "HELENALR" &&
strPassword == "MONSTER"))
{
DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = 0;
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = "S";
}
else
{
DataSet.Tables["ReturnValues"].Rows[0]["NO_USERS_IND"] = "Y";
DataSet.AcceptChanges();
bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
}
}
else
{
if (DataSet.Tables["Temp"].Rows[0]["SYSTEM_ADMINISTRATOR_IND"].ToString() == "S")
{
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = DataSet.Tables["Temp"].Rows[0]["SYSTEM_ADMINISTRATOR_IND"].ToString();
}
else
{
DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = DataSet.Tables["Temp"].Rows[0]["MIN_COMPANY_ACCESS_IND"].ToString();
}
if (DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"].ToString() != "S")
{
//Create Temp Table for Joins
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP");
strQry.AppendLine(" WHERE USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP ");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(",USER_TABLE.COMPANY_NO");
strQry.AppendLine(",USER_TABLE.EMPLOYEE_NO");
strQry.AppendLine(",USER_TABLE.PAY_CATEGORY_NO");
strQry.AppendLine(",USER_TABLE.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_PAY_CATEGORY_DEPARTMENT UPCD");
strQry.AppendLine(" ON E.COMPANY_NO = UPCD.COMPANY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UPCD.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UPCD.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND E.DEPARTMENT_NO = UPCD.DEPARTMENT_NO ");
strQry.AppendLine(" AND UPCD.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL");
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_EMPLOYEE UE");
strQry.AppendLine(" ON E.COMPANY_NO = UE.COMPANY_NO");
strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UE.EMPLOYEE_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND UE.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL");
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_PAY_CATEGORY UPC");
strQry.AppendLine(" ON E.COMPANY_NO = UPC.COMPANY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND UPC.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL ");
strQry.AppendLine(" UNION ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON UCA.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString());
strQry.AppendLine(" AND E.COMPANY_NO = UCA.COMPANY_NO");
//2013-07-10
strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A'");
strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL) AS USER_TABLE");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = Convert.ToInt64(DataSet.Tables["Temp"].Rows[0]["USER_NO"]);
DataSet.Tables["ReturnValues"].Rows[0]["RESET"] = DataSet.Tables["Temp"].Rows[0]["RESET"].ToString();
//Set Time Logged On
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayrollClient.dbo.USER_ID");
strQry.AppendLine(" SET LAST_TIME_ON = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "'");
strQry.AppendLine(" WHERE USER_NO = " + Convert.ToInt64(DataSet.Tables["Temp"].Rows[0]["USER_NO"]));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
//2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder)
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS_TEMP");
strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FCDD.FILE_LAYER_IND");
strQry.AppendLine(",FCDD.FILE_NAME");
strQry.AppendLine(",FCDD.FILE_SIZE");
strQry.AppendLine(",FCDD.FILE_SIZE_COMPRESSED");
strQry.AppendLine(",FCDD.FILE_LAST_UPDATED_DATE");
strQry.AppendLine(",FCDD.FILE_CRC_VALUE");
strQry.AppendLine(",ISNULL(MAX(FCDC.FILE_CHUNK_NO),0) AS MAX_FILE_CHUNK_NO");
strQry.AppendLine(",'' AS DOWNLOAD_IND");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS FCDD");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS FCDC");
strQry.AppendLine(" ON FCDD.FILE_LAYER_IND = FCDC.FILE_LAYER_IND");
strQry.AppendLine(" AND FCDD.FILE_NAME = FCDC.FILE_NAME");
strQry.AppendLine(" WHERE FCDD.FILE_LAYER_IND = 'P'");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" FCDD.FILE_LAYER_IND");
strQry.AppendLine(",FCDD.FILE_NAME");
strQry.AppendLine(",FCDD.FILE_SIZE");
strQry.AppendLine(",FCDD.FILE_SIZE_COMPRESSED");
strQry.AppendLine(",FCDD.FILE_LAST_UPDATED_DATE");
strQry.AppendLine(",FCDD.FILE_CRC_VALUE");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" 2");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Files");
bytCompress = (byte[])Check_Reboot_Server();
DataSet DataSetTemp = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytCompress);
if (DataSetTemp.Tables["ReturnValues"].Rows[0]["REBOOT_IND"].ToString() == "Y")
{
DataSet.Tables["ReturnValues"].Rows[0]["REBOOT_IND"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["REBOOT_IND"].ToString();
DataSet.Tables["ReturnValues"].Rows[0]["MACHINE_NAME"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["MACHINE_NAME"].ToString();
DataSet.Tables["ReturnValues"].Rows[0]["MACHINE_IP"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["MACHINE_IP"].ToString();
}
}
bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] 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);
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 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[] Get_Form_Records(Int64 parInt64CompanyNo)
{
DataSet DataSet = new DataSet();
DataSet TempDataSet;
string strEmployeeNoIn = "-1";
StringBuilder strFieldNamesInitialised = new StringBuilder();
StringBuilder strQry = new StringBuilder();
//Insert Records
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EDH.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EDH.EMPLOYEE_NO");
strQry.AppendLine(",EDH.PAY_PERIOD_DATE");
strQry.AppendLine(",SUM(EDH.TOTAL)");
strQry.AppendLine(",SUM(EEH.TOTAL)");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_HISTORY EDH ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_HISTORY EEH ");
strQry.AppendLine(" ON EDH.COMPANY_NO = EEH.COMPANY_NO ");
strQry.AppendLine(" AND EDH.PAY_PERIOD_DATE = EEH.PAY_PERIOD_DATE ");
strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE = EEH.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EDH.EMPLOYEE_NO = EEH.EMPLOYEE_NO ");
strQry.AppendLine(" AND EDH.RUN_TYPE = EEH.RUN_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E ");
strQry.AppendLine(" ON EDH.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE ");
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE IN ('W','S') ");
strQry.AppendLine(" AND EDH.EMPLOYEE_NO = E.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT ");
strQry.AppendLine(" PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",MAX(PAY_PERIOD_DATE) AS MAX_PAY_PERIOD_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND PAY_CATEGORY_TYPE IN ('W','S') ");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO) AS TEMP_EMPLOYEE_DATE ");
strQry.AppendLine(" ON EDH.PAY_CATEGORY_TYPE = TEMP_EMPLOYEE_DATE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EDH.EMPLOYEE_NO = TEMP_EMPLOYEE_DATE.EMPLOYEE_NO ");
//Add 180 Days (Withinn 6 Months)
strQry.AppendLine(" AND DATEADD(DD,180,EDH.PAY_PERIOD_DATE) >= MAX_PAY_PERIOD_DATE ");
strQry.AppendLine(" WHERE EDH.COMPANY_NO = " + parInt64CompanyNo);
//2013-06-21 Exclude T=Time Attendance
strQry.AppendLine(" AND EDH.PAY_CATEGORY_TYPE IN ('W','S') ");
strQry.AppendLine(" AND EDH.RUN_TYPE = 'T'");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" EDH.PAY_CATEGORY_TYPE ");
strQry.AppendLine(",EDH.EMPLOYEE_NO ");
strQry.AppendLine(",EDH.PAY_PERIOD_DATE");
//Exclude where Already Have Take-On Values
strQry.AppendLine(" HAVING SUM(EDH.TOTAL) = 0");
strQry.AppendLine(" AND SUM(EEH.TOTAL) = 0 ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeeTemp", parInt64CompanyNo);
for (int intRow = 0; intRow < DataSet.Tables["EmployeeTemp"].Rows.Count; intRow++)
{
if (intRow == 0)
{
strEmployeeNoIn = DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString();
}
else
{
strEmployeeNoIn += "," + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString();
}
strQry.Clear();
strQry.AppendLine("INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",RUN_NO");
strFieldNamesInitialised.Clear();
//Return strings for field that need to be Initialised to Zero
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_DEDUCTION_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT");
strQry.AppendLine(" ED.COMPANY_NO");
strQry.AppendLine(",ED.PAY_CATEGORY_TYPE");
strQry.AppendLine(",ED.EMPLOYEE_NO");
strQry.AppendLine(",'T'");
strQry.AppendLine(",ED.DEDUCTION_NO ");
strQry.AppendLine(",ED.DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",1");
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT EDC");
strQry.AppendLine(" ON ED.COMPANY_NO = EDC.COMPANY_NO ");
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = EDC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND ED.EMPLOYEE_NO = EDC.EMPLOYEE_NO ");
strQry.AppendLine(" AND ED.DEDUCTION_NO = EDC.DEDUCTION_NO ");
strQry.AppendLine(" AND ED.DEDUCTION_SUB_ACCOUNT_NO = EDC.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND EDC.RUN_TYPE = 'T' ");
strQry.AppendLine(" WHERE ED.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND ED.EMPLOYEE_NO = " + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");
//No EMPLOYEE_DEDUCTION_HISTORY Record Exists
strQry.AppendLine(" AND EDC.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine("INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
//NB PAY_CATEGORY_NO will be Set to Zero
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",EARNING_NO");
//ELR 2014-05-24
strQry.AppendLine(",EARNING_TYPE_IND");
strQry.AppendLine(",RUN_NO");
strFieldNamesInitialised.Clear();
//Return strings for field that need to be Initialised to Zero
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_EARNING_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT");
strQry.AppendLine(" EN.COMPANY_NO");
strQry.AppendLine(",EN.PAY_CATEGORY_TYPE");
strQry.AppendLine("," + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(",'T'");
strQry.AppendLine(",EN.EARNING_NO ");
//ELR 2014-05-24
strQry.AppendLine(",'U'");
strQry.AppendLine(",1");
//Append Initialised Numeric Fields Names
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING EN ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.COMPANY C ");
strQry.AppendLine(" ON EN.COMPANY_NO = C.COMPANY_NO ");
strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT EEC");
strQry.AppendLine(" ON EN.COMPANY_NO = EEC.COMPANY_NO ");
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = EEC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EN.EARNING_NO = EEC.EARNING_NO ");
strQry.AppendLine(" AND EEC.RUN_TYPE = 'T' ");
strQry.AppendLine(" WHERE EN.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine(" AND (EN.EARNING_NO IN (1,2,7,8,9)");
if (DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
//Normal Leave / Sick Leave
strQry.AppendLine(" OR (EN.EARNING_NO IN (200,201))");
strQry.AppendLine(" OR (EN.EARNING_NO = 3 AND C.OVERTIME1_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 4 AND C.OVERTIME2_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 5 AND C.OVERTIME3_RATE > 0))");
}
else
{
strQry.AppendLine(" OR (EN.EARNING_NO = 3 AND C.SALARY_OVERTIME1_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 4 AND C.SALARY_OVERTIME2_RATE > 0)");
strQry.AppendLine(" OR (EN.EARNING_NO = 5 AND C.SALARY_OVERTIME3_RATE > 0))");
}
strQry.AppendLine(" AND EN.DATETIME_DELETE_RECORD IS NULL");
//No EMPLOYEE_EARNING_HISTORY Record Exists
strQry.AppendLine(" AND EEC.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine("INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",EARNING_NO");
//ELR 2014-05-24
strQry.AppendLine(",EARNING_TYPE_IND");
strQry.AppendLine(",RUN_NO");
strFieldNamesInitialised.Clear();
//Return strings for field that need to be Initialised to Zero
clsDBConnectionObjects.Initialise_DataSet_Numeric_Fields("EMPLOYEE_EARNING_CURRENT", ref strQry, ref strFieldNamesInitialised, parInt64CompanyNo);
strQry.AppendLine(")");
strQry.AppendLine(" SELECT");
strQry.AppendLine(" EE.COMPANY_NO");
strQry.AppendLine(",EE.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EE.EMPLOYEE_NO");
strQry.AppendLine(",'T'");
strQry.AppendLine(",EE.EARNING_NO ");
//ELR 2014-05-24
strQry.AppendLine(",EE.EARNING_TYPE_IND");
strQry.AppendLine(",1");
//Append Initialised Numeric Fields Names
strQry.Append(strFieldNamesInitialised);
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT EEC");
strQry.AppendLine(" ON EE.COMPANY_NO = EEC.COMPANY_NO ");
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = EEC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EE.EMPLOYEE_NO = EEC.EMPLOYEE_NO ");
strQry.AppendLine(" AND EE.EARNING_NO = EEC.EARNING_NO ");
strQry.AppendLine(" AND EEC.RUN_TYPE = 'T' ");
strQry.AppendLine(" WHERE EE.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EE.EMPLOYEE_NO = " + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'");
strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL");
//No EMPLOYEE_EARNING_HISTORY Record Exists
strQry.AppendLine(" AND EEC.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
//First Delete Records That are older than 180 Days
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND RUN_TYPE = 'T' ");
strQry.AppendLine(" AND EMPLOYEE_NO NOT IN (" + strEmployeeNoIn + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
//First Delete Records That are Older than 180 Days
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND RUN_TYPE = 'T' ");
strQry.AppendLine(" AND EMPLOYEE_NO NOT IN (" + strEmployeeNoIn + ")");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
//EERORL CARRY HERE
DataSet.Tables.Add("PayrollType");
DataTable PayrollTypeDataTable = new DataTable("PayrollType");
DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));
if (DataSet.Tables["EmployeeTemp"].Rows.Count > 0)
{
DataView PayrollTypeDataView = new DataView(DataSet.Tables["EmployeeTemp"],
"PAY_CATEGORY_TYPE = 'W'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Wages";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
PayrollTypeDataView = null;
PayrollTypeDataView = new DataView(DataSet.Tables["EmployeeTemp"],
"PAY_CATEGORY_TYPE = 'S'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Salaries";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
DataSet.Tables.Remove("EmployeeTemp");
DataSet.AcceptChanges();
byte[] bytTempCompress = Get_Company_Records(parInt64CompanyNo, DataSet.Tables["PayrollType"].Rows[0]["PAYROLL_TYPE_DESC"].ToString().Substring(0, 1));
TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
DataSet.Merge(TempDataSet);
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Local_Current_Download_Files(byte[] parbyteDataSet)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
StringBuilder strQry = new StringBuilder();
DataSet ClientDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
DataView ClientDataSetDataview = new DataView(ClientDataSet.Tables["Files"]
, ""
, "FILE_LAYER_IND,FILE_NAME"
, DataViewRowState.CurrentRows);
int intFindRow = -1;
object[] objFind = new object[2];
DataSet DataSet = new DataSet();
strQry.Append(clsDBConnectionObjects.Get_Local_Client_Download_SQL());
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Files");
for (int intRow = 0; intRow < DataSet.Tables["Files"].Rows.Count; intRow++)
{
objFind[0] = DataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString();
objFind[1] = DataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString();
intFindRow = ClientDataSetDataview.Find(objFind);
if (intFindRow == -1)
{
//DELETE Records on InteractPayrollClient IF Not in Internet Download DataSet
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS");
strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString()));
strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString()));
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
DataSet.Tables["Files"].Rows.RemoveAt(intRow);
intRow -= 1;
}
}
DataSet.AcceptChanges();
DataView DataSetDataview = new DataView(DataSet.Tables["Files"]
, ""
, "FILE_LAYER_IND,FILE_NAME"
, DataViewRowState.CurrentRows);
//Go Through Internet Download to see If Any Files Must be Downloaded
for (int intRow = 0; intRow < ClientDataSet.Tables["Files"].Rows.Count; intRow++)
{
objFind[0] = ClientDataSet.Tables["Files"].Rows[intRow]["FILE_LAYER_IND"].ToString();
objFind[1] = ClientDataSet.Tables["Files"].Rows[intRow]["FILE_NAME"].ToString();
intFindRow = DataSetDataview.Find(objFind);
if (intFindRow > -1)
{
if (Convert.ToDateTime(ClientDataSet.Tables["Files"].Rows[intRow]["FILE_LAST_UPDATED_DATE"]) == Convert.ToDateTime(DataSetDataview[intFindRow]["FILE_LAST_UPDATED_DATE"]))
{
ClientDataSet.Tables["Files"].Rows.RemoveAt(intRow);
intRow -= 1;
}
}
}
ClientDataSet.AcceptChanges();
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(ClientDataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] Get_Form_Records(int parintCurrentUserNo, string parstrAccessInd)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
DataSet DataSet = new DataSet();
StringBuilder strQry = new StringBuilder();
string strSoftwareToUse = "D";
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" IDENTIFY_THRESHOLD_VALUE");
strQry.AppendLine(",VERIFY_THRESHOLD_VALUE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_IDENTIFY_VERIFY_THRESHOLD");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "FingerprintThreshold");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" FINGERPRINT_SOFTWARE_IND");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_SOFTWARE_TO_USE");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "SoftwareToUse");
if (DataSet.Tables["SoftwareToUse"].Rows.Count > 0)
{
strSoftwareToUse = DataSet.Tables["SoftwareToUse"].Rows[0]["FINGERPRINT_SOFTWARE_IND"].ToString();
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" READ_OPTION_NO");
strQry.AppendLine(",READ_OPTION_DESC");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.READ_OPTION");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" READ_OPTION_NO");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "ReadOption");
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine("'" + clsDBConnectionObjects.Get_ClientConnectionString().Replace("InteractPayrollClient_Debug", "InteractPayrollClient") + "' AS DB_CONNECTION_STRING");
//Will Always be Only 1 Row
strQry.AppendLine(" FROM InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS ");
strQry.AppendLine(" WHERE TABLE_NAME = 'COMPANY'");
strQry.AppendLine(" AND COLUMN_NAME = 'COMPANY_NO'");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Connection");
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" PC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.PAY_CATEGORY PC");
strQry.AppendLine(" ON C.COMPANY_NO = PC.COMPANY_NO");
if (parstrAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" ON UCA.USER_NO = " + parintCurrentUserNo);
strQry.AppendLine(" AND C.COMPANY_NO = UCA.COMPANY_NO");
}
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp");
DataSet.Tables.Add("PayrollType");
DataTable PayrollTypeDataTable = new DataTable("PayrollType");
DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));
DataView PayrollTypeDataView = new DataView(DataSet.Tables["Temp"],
"PAY_CATEGORY_TYPE = 'W'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Wages";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
PayrollTypeDataView = null;
PayrollTypeDataView = new DataView(DataSet.Tables["Temp"],
"PAY_CATEGORY_TYPE = 'S'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Salaries";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
PayrollTypeDataView = null;
PayrollTypeDataView = new DataView(DataSet.Tables["Temp"],
"PAY_CATEGORY_TYPE = 'T'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Time Attendance";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
DataSet.AcceptChanges();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" C.COMPANY_NO");
strQry.AppendLine(",C.COMPANY_DESC");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C");
if (parstrAccessInd != "S")
{
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UC ");
strQry.AppendLine(" ON UC.USER_NO = " + parintCurrentUserNo);
strQry.AppendLine(" AND UC.COMPANY_NO = C.COMPANY_NO ");
//2013-07-10
strQry.AppendLine(" AND UC.COMPANY_ACCESS_IND = 'A'");
}
strQry.AppendLine(" ORDER BY C.COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company");
if (DataSet.Tables["Company"].Rows.Count > 0)
{
byte[] bytTempCompress = Get_Company_Records_New(Convert.ToInt64(DataSet.Tables["Company"].Rows[0]["COMPANY_NO"]), strSoftwareToUse);
DataSet TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
DataSet.Merge(TempDataSet);
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public byte[] 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 void Update_Records(Int64 parint64UserNo, byte[] byteCompressedDataSet)
{
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
//Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes
DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteCompressedDataSet);
StringBuilder strQry = new StringBuilder();
for (int intRow = 0; intRow < DataSet.Tables["UserFingerTemplate"].Rows.Count; intRow++)
{
if (DataSet.Tables["UserFingerTemplate"].Rows[intRow].RowState == DataRowState.Added)
{
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE");
strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo.ToString());
strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//There Should be only One - otherwise Error
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",FINGER_NO");
strQry.AppendLine(",FINGER_TEMPLATE)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parint64UserNo);
strQry.AppendLine("," + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO"].ToString());
strQry.AppendLine(",@FINGER_TEMPLATE) ");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString(), (byte[])DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_TEMPLATE"], "@FINGER_TEMPLATE");
}
else
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE_DELETE");
strQry.AppendLine("(USER_NO");
strQry.AppendLine(",FINGER_NO");
strQry.AppendLine(",CREATION_DATETIME)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" USER_NO");
strQry.AppendLine(",FINGER_NO");
strQry.AppendLine(",CREATION_DATETIME ");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE");
strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo.ToString());
strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO", DataRowVersion.Original].ToString());
strQry.AppendLine(" AND NOT CREATION_DATETIME IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE");
strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo.ToString());
strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO", DataRowVersion.Original].ToString());
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
}
}
public void Upload_Records(Int64 parInt64CompanyNo, byte[] parbyteDataSet)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
if (parDataSet.Tables["Employee"].Rows.Count > 0)
{
for (int intRow = 0; intRow < parDataSet.Tables["Employee"].Rows.Count; intRow++)
{
if (DataSet.Tables["Employee"] != null)
{
DataSet.Tables.Remove("Employee");
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EMPLOYEE_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString());
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parInt64CompanyNo);
if (DataSet.Tables["Employee"].Rows.Count == 0)
{
//Insert
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",EMPLOYEE_CODE");
strQry.AppendLine(",EMPLOYEE_NAME");
strQry.AppendLine(",EMPLOYEE_SURNAME");
strQry.AppendLine(",EMPLOYEE_LAST_RUNDATE");
strQry.AppendLine(",EMPLOYEE_TAX_STARTDATE");
strQry.AppendLine(",DEPARTMENT_NO");
strQry.AppendLine(",FIRST_RUN_COMPLETED_IND");
strQry.AppendLine(",EMPLOYEE_TAKEON_IND");
strQry.AppendLine(",UPLOAD_FROM_CLIENT_IND");
strQry.AppendLine(",ANNUAL_SALARY");
strQry.AppendLine(",EMPLOYEE_TAX_OFFICE_NO");
strQry.AppendLine(",TAX_DIRECTIVE_PERCENTAGE");
strQry.AppendLine(",LEAVE_SHIFT_NO");
strQry.AppendLine(",EMPLOYEE_NUMBER_CHEQUES");
strQry.AppendLine(",NUMBER_MEDICAL_AID_DEPENDENTS");
strQry.AppendLine(",OCCUPATION_NO");
strQry.AppendLine(",BANK_ACCOUNT_TYPE_NO");
strQry.AppendLine(",BRANCH_CODE");
strQry.AppendLine(",ACCOUNT_NO");
strQry.AppendLine(",MARITAL_STATUS_NO");
strQry.AppendLine(",NATURE_PERSON_NO");
strQry.AppendLine(",RACE_NO");
strQry.AppendLine(",OCCUPATION_LEVEL_NO");
strQry.AppendLine(",OCCUPATION_CATEGORY_NO");
strQry.AppendLine(",OCCUPATION_FUNCTION_NO");
strQry.AppendLine(",SKILLS_EQUITY_PROVINCE_NO");
strQry.AppendLine(",USER_NO_ACTIVATE_RECORD");
strQry.AppendLine(",BANK_NO)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parDataSet.Tables["Employee"].Rows[intRow]["COMPANY_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Employee"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_CODE"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NAME"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_SURNAME"].ToString()));
if (parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_LAST_RUNDATE"] != System.DBNull.Value)
{
strQry.AppendLine(",'" + Convert.ToDateTime(parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_LAST_RUNDATE"]).ToString("yyyy-MM-dd") + "'");
}
else
{
strQry.AppendLine(",'2015-02-06'");
}
strQry.AppendLine(",'2015-02-06'");
if (parDataSet.Tables["Employee"].Rows[intRow]["DEPARTMENT_NO"] != System.DBNull.Value)
{
strQry.AppendLine("," + parDataSet.Tables["Employee"].Rows[intRow]["DEPARTMENT_NO"].ToString());
}
else
{
strQry.AppendLine(",NULL");
}
strQry.AppendLine(",'Y'");
strQry.AppendLine(",'Y'");
strQry.AppendLine(",'Y'");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0)");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Convert.ToInt64(parDataSet.Tables["Employee"].Rows[intRow]["COMPANY_NO"]));
}
}
for (int intRow = 0; intRow < parDataSet.Tables["EmployeePayCategory"].Rows.Count; intRow++)
{
if (DataSet.Tables["EmployeePayCategory"] != null)
{
DataSet.Tables.Remove("EmployeePayCategory");
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EMPLOYEE_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeePayCategory", parInt64CompanyNo);
if (DataSet.Tables["EmployeePayCategory"].Rows.Count == 0)
{
//Insert
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(",DEFAULT_IND");
strQry.AppendLine(",HOURLY_RATE");
strQry.AppendLine(",LEAVE_DAY_RATE_DECIMAL)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" C.COMPANY_NO");
strQry.AppendLine("," + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(",ISNULL(EPC.TIE_BREAKER,0) + 1");
strQry.AppendLine(",'Y'");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY C");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON C.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND EPC.EMPLOYEE_NO = " + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = " + parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" WHERE C.COMPANY_NO = " + parInt64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), Convert.ToInt64(parDataSet.Tables["EmployeePayCategory"].Rows[intRow]["COMPANY_NO"]));
}
}
}
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 byte[] Update_Record(Int64 parInt64CompanyNo, Int64 parint64CurrentUserNo, byte[] parbyteDataSet, string parstrPayCategoryType)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
for (int intRow = 0; intRow < parDataSet.Tables["EmployeeShiftSchedule"].Rows.Count; intRow++)
{
if (intRow == 0)
{
//Delete all Records For PAY_CATEGORY_SHIFT_SCHEDULE_NO
strQry.Clear();
strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_SHIFT_SCHEDULE");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND PAY_CATEGORY_SHIFT_SCHEDULE_NO = " + parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_SHIFT_SCHEDULE_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_SHIFT_SCHEDULE ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",PAY_CATEGORY_SHIFT_SCHEDULE_NO");
strQry.AppendLine(",SHIFT_SCHEDULE_DATETIME");
strQry.AppendLine(",FROM_HHMM");
strQry.AppendLine(",TO_HHMM");
strQry.AppendLine(",FORECOLOR_HEX");
strQry.AppendLine(",BACKCOLOR_HEX)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parInt64CompanyNo.ToString());
strQry.AppendLine("," + parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine("," + parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["PAY_CATEGORY_SHIFT_SCHEDULE_NO"].ToString());
strQry.AppendLine(",'" + Convert.ToDateTime(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["SHIFT_SCHEDULE_DATETIME"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["FROM_HHMM"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["TO_HHMM"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["FORECOLOR_HEX"].ToString()));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["EmployeeShiftSchedule"].Rows[intRow]["BACKCOLOR_HEX"].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 = clsDBConnectionObjects.Compress_DataSet(parDataSet);
parDataSet.Dispose();
parDataSet = null;
return(bytCompress);
}
public byte[] Update_Employee(Int64 parInt64CompanyNo, string parstrPayrollType, string parstrFromPayrollType, int parintEmployeeNo, int parintLeaveShiftNo, byte[] parbyteDataSet)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
//Remove Current EMPLOYEE_PAY_CATEGORY Link
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY ");
strQry.AppendLine(" SET DATETIME_DELETE_RECORD = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "'");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
//Relink LEAVE_SHIFT_NO
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE ");
strQry.AppendLine(" SET ");
strQry.AppendLine(" PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(",LEAVE_SHIFT_NO = " + parintLeaveShiftNo);
if (parstrPayrollType == "S")
{
strQry.AppendLine(",EMPLOYEE_NUMBER_CHEQUES = 12 ");
}
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" AND EMPLOYEE_ENDDATE IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
for (int intRow = 0; intRow < parDataSet.Tables["PayCategory"].Rows.Count; intRow++)
{
if (DataSet.Tables["Temp"] != null)
{
DataSet.Tables.Remove("Temp");
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" ISNULL(MAX(TIE_BREAKER),0) + 1 AS MAX_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parDataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Temp", parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",HOURLY_RATE");
strQry.AppendLine(",DEFAULT_IND");
strQry.AppendLine(",LEAVE_DAY_RATE_DECIMAL");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_NEW_RECORD)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(parInt64CompanyNo.ToString());
strQry.AppendLine("," + parintEmployeeNo);
strQry.AppendLine("," + Convert.ToInt32(parDataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"]));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine("," + Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]));
strQry.AppendLine("," + Convert.ToDouble(parDataSet.Tables["PayCategory"].Rows[intRow]["REC_RATE"]));
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["PayCategory"].Rows[intRow]["DEFAULT_IND"].ToString()));
strQry.AppendLine(",LEAVE_DAY_RATE_DECIMAL = ");
strQry.AppendLine(" CASE ");
strQry.AppendLine(" WHEN PC.PAY_CATEGORY_TYPE = 'W' THEN");
strQry.AppendLine(" ROUND(SUM(PCTD.TIME_DECIMAL) / COUNT(*),2)");
strQry.AppendLine(" ELSE ROUND(PC.SALARY_MINUTES_PAID_PER_DAY / 60,2)");
strQry.AppendLine(" END ");
strQry.AppendLine(",GETDATE()");
//Current User
strQry.AppendLine(",0");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_TIME_DECIMAL PCTD");
strQry.AppendLine(" ON PC.COMPANY_NO = PCTD.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = PCTD.PAY_CATEGORY_NO ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + parDataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" AND PC.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" GROUP BY ");
strQry.AppendLine(" PC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(",PC.SALARY_MINUTES_PAID_PER_DAY");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EARNING ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",EARNING_DESC");
strQry.AppendLine(",SPREAD_SHEET_TYPE_IND");
strQry.AppendLine(",LEAVE_PERCENTAGE");
strQry.AppendLine(",EARNING_REPORT_HEADER1");
strQry.AppendLine(",EARNING_REPORT_HEADER2");
strQry.AppendLine(",IRP5_CODE");
strQry.AppendLine(",TAX_PERCENTAGE");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_RECORD");
strQry.AppendLine(",DATETIME_DELETE_RECORD");
strQry.AppendLine(",EARNING_DEL_IND)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EARNING_NO");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(",E.TIE_BREAKER");
strQry.AppendLine(",E.EARNING_DESC");
strQry.AppendLine(",E.SPREAD_SHEET_TYPE_IND");
strQry.AppendLine(",E.LEAVE_PERCENTAGE");
strQry.AppendLine(",E.EARNING_REPORT_HEADER1");
strQry.AppendLine(",E.EARNING_REPORT_HEADER2");
strQry.AppendLine(",E.IRP5_CODE");
strQry.AppendLine(",E.TAX_PERCENTAGE");
strQry.AppendLine(",E.USER_NO_NEW_RECORD");
strQry.AppendLine(",E.DATETIME_NEW_RECORD");
strQry.AppendLine(",E.USER_NO_RECORD");
strQry.AppendLine(",E.DATETIME_DELETE_RECORD");
strQry.AppendLine(",E.EARNING_DEL_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING E");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");
strQry.AppendLine(" ON E.COMPANY_NO = EE.COMPANY_NO ");
strQry.AppendLine(" AND EE.EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND E.EARNING_NO = EE.EARNING_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EE.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EARNING EARN");
strQry.AppendLine(" ON E.COMPANY_NO = EARN.COMPANY_NO ");
strQry.AppendLine(" AND E.EARNING_DESC = EARN.EARNING_DESC ");
strQry.AppendLine(" AND EARN.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
//Not Defaults and Not Leave
strQry.AppendLine(" AND E.EARNING_NO > 9");
strQry.AppendLine(" AND E.EARNING_NO < 200");
//Record Does NOT Exist
strQry.AppendLine(" AND EARN.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE EE ");
strQry.AppendLine(" SET ");
strQry.AppendLine(" PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE ");
strQry.AppendLine(" WHERE EE.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EE.EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.DEDUCTION ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",DEDUCTION_DESC");
strQry.AppendLine(",IRP5_CODE");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_COUNT");
strQry.AppendLine(",DEDUCTION_REPORT_HEADER1");
strQry.AppendLine(",DEDUCTION_REPORT_HEADER2");
strQry.AppendLine(",DEDUCTION_LOAN_TYPE_IND");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_RECORD");
strQry.AppendLine(",DATETIME_DELETE_RECORD)");
strQry.AppendLine(" SELECT DISTINCT ");
strQry.AppendLine(" D.COMPANY_NO");
strQry.AppendLine(",D.DEDUCTION_NO");
strQry.AppendLine(",D.DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(",D.DEDUCTION_DESC");
strQry.AppendLine(",D.IRP5_CODE");
strQry.AppendLine(",D.DEDUCTION_SUB_ACCOUNT_COUNT");
strQry.AppendLine(",D.DEDUCTION_REPORT_HEADER1");
strQry.AppendLine(",D.DEDUCTION_REPORT_HEADER2");
strQry.AppendLine(",D.DEDUCTION_LOAN_TYPE_IND");
strQry.AppendLine(",D.USER_NO_NEW_RECORD");
strQry.AppendLine(",D.DATETIME_NEW_RECORD");
strQry.AppendLine(",D.USER_NO_RECORD");
strQry.AppendLine(",D.DATETIME_DELETE_RECORD");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.DEDUCTION D");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED ");
strQry.AppendLine(" ON D.COMPANY_NO = ED.COMPANY_NO ");
strQry.AppendLine(" AND ED.EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND D.DEDUCTION_NO = ED.DEDUCTION_NO ");
strQry.AppendLine(" AND D.DEDUCTION_SUB_ACCOUNT_NO = ED.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND D.PAY_CATEGORY_TYPE = ED.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
strQry.AppendLine(" ON D.COMPANY_NO = DED.COMPANY_NO ");
strQry.AppendLine(" AND D.DEDUCTION_NO = DED.DEDUCTION_NO ");
strQry.AppendLine(" AND DED.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE D.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND D.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND D.DATETIME_DELETE_RECORD IS NULL");
//Record Does NOT Exist
strQry.AppendLine(" AND DED.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",DEDUCTION_TYPE_IND");
strQry.AppendLine(",DEDUCTION_VALUE");
strQry.AppendLine(",DEDUCTION_MIN_VALUE");
strQry.AppendLine(",DEDUCTION_MAX_VALUE");
strQry.AppendLine(",DEDUCTION_PERIOD_IND");
strQry.AppendLine(",DEDUCTION_DAY_VALUE");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_RECORD");
strQry.AppendLine(",DATETIME_DELETE_RECORD)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" ED.COMPANY_NO");
strQry.AppendLine(",ED.EMPLOYEE_NO");
strQry.AppendLine(",ED.DEDUCTION_NO");
strQry.AppendLine(",ED.DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(",ED.TIE_BREAKER");
strQry.AppendLine(",ED.DEDUCTION_TYPE_IND");
strQry.AppendLine(",ED.DEDUCTION_VALUE");
strQry.AppendLine(",ED.DEDUCTION_MIN_VALUE");
strQry.AppendLine(",ED.DEDUCTION_MAX_VALUE");
strQry.AppendLine(",ED.DEDUCTION_PERIOD_IND");
strQry.AppendLine(",ED.DEDUCTION_DAY_VALUE");
strQry.AppendLine(",ED.USER_NO_NEW_RECORD");
strQry.AppendLine(",ED.DATETIME_NEW_RECORD");
strQry.AppendLine(",ED.USER_NO_RECORD");
strQry.AppendLine(",ED.DATETIME_DELETE_RECORD");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED_Compare");
strQry.AppendLine(" ON ED.COMPANY_NO = ED_Compare.COMPANY_NO");
strQry.AppendLine(" AND ED.EMPLOYEE_NO = ED_Compare.EMPLOYEE_NO");
strQry.AppendLine(" AND ED.DEDUCTION_NO = ED_Compare.DEDUCTION_NO");
strQry.AppendLine(" AND ED.DEDUCTION_SUB_ACCOUNT_NO = ED_Compare.DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(" AND ED_Compare.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE ED.COMPANY_NO = " + parInt64CompanyNo);
//Default
strQry.AppendLine(" AND ED.EMPLOYEE_NO = 0");
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");
//Record Does NOT Exist
strQry.AppendLine(" AND ED_Compare.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE ED ");
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
strQry.AppendLine(" ON ED.COMPANY_NO = DED.COMPANY_NO ");
strQry.AppendLine(" AND ED.DEDUCTION_NO = DED.DEDUCTION_NO ");
strQry.AppendLine(" AND ED.DEDUCTION_SUB_ACCOUNT_NO = DED.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = DED.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE ED.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND ED.EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND ED.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE EDEP ");
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE EDEP ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EARNING EARN");
strQry.AppendLine(" ON EDEP.COMPANY_NO = EARN.COMPANY_NO ");
strQry.AppendLine(" AND EDEP.EARNING_NO = EARN.EARNING_NO ");
strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = EARN.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
strQry.AppendLine(" ON EDEP.COMPANY_NO = DED.COMPANY_NO ");
strQry.AppendLine(" AND EDEP.DEDUCTION_NO = DED.DEDUCTION_NO ");
strQry.AppendLine(" AND EDEP.DEDUCTION_SUB_ACCOUNT_NO = DED.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = DED.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE EDEP.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EDEP.EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND EDEP.DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
if (parstrPayrollType == "W")
{
strQry.Clear();
strQry.AppendLine(" UPDATE EDEP ");
//2=Normal Time
strQry.AppendLine(" SET EARNING_NO = 2 ");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE EDEP ");
strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE EDEP_Check ");
strQry.AppendLine(" ON EDEP.COMPANY_NO = EDEP_Check.COMPANY_NO ");
strQry.AppendLine(" AND EDEP.EMPLOYEE_NO = EDEP_Check.EMPLOYEE_NO ");
strQry.AppendLine(" AND EDEP_Check.EARNING_NO = 2 ");
strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = EDEP_Check.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE EDEP.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EDEP.EMPLOYEE_NO = " + parintEmployeeNo);
//PAY_CATEGORY_TYPE already Changed
strQry.AppendLine(" AND EDEP.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
//1=Income
strQry.AppendLine(" AND EDEP.EARNING_NO = 1 ");
strQry.AppendLine(" AND EDEP.DATETIME_DELETE_RECORD IS NULL");
//Records do NOT already Exist
strQry.AppendLine(" AND EDEP_Check.COMPANY_NO IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
else
{
if (parstrPayrollType == "S")
{
strQry.Clear();
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE ");
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",EARNING_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_RECORD");
strQry.AppendLine(",DATETIME_DELETE_RECORD)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
//Income
strQry.AppendLine(",1");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",TIE_BREAKER");
strQry.AppendLine(",USER_NO_NEW_RECORD");
strQry.AppendLine(",DATETIME_NEW_RECORD");
strQry.AppendLine(",USER_NO_RECORD");
strQry.AppendLine(",DATETIME_DELETE_RECORD");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
//PAY_CATEGORY_TYPE already Changed
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
//2=Normal Time
strQry.AppendLine(" AND EARNING_NO = 2 ");
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
}
strQry.Clear();
strQry.AppendLine(" UPDATE L ");
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LOANS L ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.DEDUCTION DED");
strQry.AppendLine(" ON L.COMPANY_NO = DED.COMPANY_NO ");
strQry.AppendLine(" AND L.DEDUCTION_NO = DED.DEDUCTION_NO ");
strQry.AppendLine(" AND L.DEDUCTION_SUB_ACCOUNT_NO = DED.DEDUCTION_SUB_ACCOUNT_NO ");
strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE = DED.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE L.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND L.EMPLOYEE_NO = " + parintEmployeeNo);
strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
strQry.AppendLine(" AND L.DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
if (parstrPayrollType == "W")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT ");
}
else
{
if (parstrPayrollType == "S")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT ");
}
else
{
if (parstrPayrollType == "T")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT ");
}
}
}
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",TIMESHEET_DATE");
strQry.AppendLine(",TIMESHEET_SEQ");
strQry.AppendLine(",TIMESHEET_TIME_IN_MINUTES");
strQry.AppendLine(",TIMESHEET_TIME_OUT_MINUTES");
strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES");
strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES");
strQry.AppendLine(",INDICATOR");
strQry.AppendLine(",TIMESHEET_ACCUM_MINUTES");
strQry.AppendLine(",USER_NO_TIME_IN");
strQry.AppendLine(",USER_NO_TIME_OUT)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EBC.COMPANY_NO");
strQry.AppendLine(",EBC.EMPLOYEE_NO");
//New PAY_CATEGORY_NO
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EBC.TIMESHEET_DATE");
strQry.AppendLine(",EBC.TIMESHEET_SEQ");
strQry.AppendLine(",EBC.TIMESHEET_TIME_IN_MINUTES");
strQry.AppendLine(",EBC.TIMESHEET_TIME_OUT_MINUTES");
strQry.AppendLine(",EBC.CLOCKED_TIME_IN_MINUTES");
strQry.AppendLine(",EBC.CLOCKED_TIME_OUT_MINUTES");
strQry.AppendLine(",EBC.INDICATOR");
strQry.AppendLine(",EBC.TIMESHEET_ACCUM_MINUTES");
strQry.AppendLine(",EBC.USER_NO_TIME_IN");
strQry.AppendLine(",EBC.USER_NO_TIME_OUT");
if (parstrFromPayrollType == "W")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT EBC ");
}
else
{
if (parstrFromPayrollType == "S")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT EBC ");
}
else
{
if (parstrFromPayrollType == "T")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT EBC ");
}
}
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON EBC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND EBC.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
//New EMPLOYEE_PAY_CATEGORY
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE EBC.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EBC.EMPLOYEE_NO = " + parintEmployeeNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
if (parstrFromPayrollType == "W")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIMESHEET_CURRENT ");
}
else
{
if (parstrFromPayrollType == "S")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT ");
}
else
{
if (parstrFromPayrollType == "T")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT ");
}
}
}
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
if (parstrPayrollType == "W")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT ");
}
else
{
if (parstrPayrollType == "S")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT ");
}
else
{
if (parstrPayrollType == "T")
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT ");
}
}
}
strQry.AppendLine("(COMPANY_NO");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_NO");
strQry.AppendLine(",BREAK_DATE");
strQry.AppendLine(",BREAK_SEQ");
strQry.AppendLine(",BREAK_TIME_IN_MINUTES");
strQry.AppendLine(",BREAK_TIME_OUT_MINUTES");
strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES");
strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES");
strQry.AppendLine(",INDICATOR");
strQry.AppendLine(",BREAK_ACCUM_MINUTES");
strQry.AppendLine(",USER_NO_TIME_IN");
strQry.AppendLine(",USER_NO_TIME_OUT)");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" EBC.COMPANY_NO");
strQry.AppendLine(",EBC.EMPLOYEE_NO");
//New PAY_CATEGORY_NO
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",EBC.BREAK_DATE");
strQry.AppendLine(",EBC.BREAK_SEQ");
strQry.AppendLine(",EBC.BREAK_TIME_IN_MINUTES");
strQry.AppendLine(",EBC.BREAK_TIME_OUT_MINUTES");
strQry.AppendLine(",EBC.CLOCKED_TIME_IN_MINUTES");
strQry.AppendLine(",EBC.CLOCKED_TIME_OUT_MINUTES");
strQry.AppendLine(",EBC.INDICATOR");
strQry.AppendLine(",EBC.BREAK_ACCUM_MINUTES");
strQry.AppendLine(",EBC.USER_NO_TIME_IN");
strQry.AppendLine(",EBC.USER_NO_TIME_OUT");
if (parstrFromPayrollType == "W")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT EBC ");
}
else
{
if (parstrFromPayrollType == "S")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT EBC ");
}
else
{
if (parstrFromPayrollType == "T")
{
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT EBC ");
}
}
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON EBC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND EBC.EMPLOYEE_NO = EPC.EMPLOYEE_NO ");
//New EMPLOYEE_PAY_CATEGORY
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE EBC.COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EBC.EMPLOYEE_NO = " + parintEmployeeNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
if (parstrFromPayrollType == "W")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_BREAK_CURRENT ");
}
else
{
if (parstrFromPayrollType == "S")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_SALARY_BREAK_CURRENT ");
}
else
{
if (parstrFromPayrollType == "T")
{
strQry.AppendLine("DELETE FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT ");
}
}
}
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT ");
//1=Income
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
//PAY_CATEGORY_TYPE already Changed
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
//Batch Processing Earning
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_BATCH_TEMP ");
//1=Income
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
//PAY_CATEGORY_TYPE already Changed
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
//Batch Processing Deduction
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_BATCH_TEMP ");
//1=Income
strQry.AppendLine(" SET PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType));
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo);
//PAY_CATEGORY_TYPE already Changed
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrFromPayrollType));
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
Get_Employee_DataSet(DataSet, parInt64CompanyNo, parintEmployeeNo);
Get_CurrentPayCategory_DataSet(DataSet, parInt64CompanyNo, parintEmployeeNo);
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll.dbo.COMPANY_LINK");
strQry.AppendLine(" SET BACKUP_DB_IND = 1");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), -1);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public int Check_Run(Int64 parint64CompanyNo, string parstrToPayCategoryType, byte[] parbytCompressDataSet)
{
int intReturnCode = 0;
DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbytCompressDataSet);
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND ISNULL(ALLOW_COST_CENTRE_CONVERT_IND,0) = 1");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "AllowConvertCheck", parint64CompanyNo);
if (DataSet.Tables["AllowConvertCheck"].Rows.Count == 0)
{
intReturnCode = 9;
}
else
{
//Check That there is No Current Payroll Run on the Go
for (int intRow = 0; intRow < DataSet.Tables["PayCategory"].Rows.Count; intRow++)
{
if (DataSet.Tables["RunCheck"] != null)
{
DataSet.Tables.Remove("RunCheck");
}
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" COMPANY_NO");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_CURRENT ");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND RUN_TYPE = 'P' ");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "RunCheck", parint64CompanyNo);
if (DataSet.Tables["RunCheck"].Rows.Count > 0)
{
intReturnCode = 1;
break;
}
}
}
DataSet.Dispose();
DataSet = null;
return intReturnCode;
}
public byte[] Get_Form_Records(Int64 parint64CurrentUserNo, string parstrAccessInd)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
//DISTINCT Because bug caused Duplicates
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT");
strQry.AppendLine(" U.USER_NO");
strQry.AppendLine(",UCA.COMPANY_NO");
strQry.AppendLine(",U.USER_ID");
strQry.AppendLine(",U.FIRSTNAME");
strQry.AppendLine(",U.SURNAME");
strQry.AppendLine(" FROM ");
strQry.AppendLine(" InteractPayroll.dbo.USER_ID U");
strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
if (parstrAccessInd == "A")
{
strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA1");
}
strQry.AppendLine(" WHERE UCA.COMPANY_ACCESS_IND <> 'A' ");
strQry.AppendLine(" AND U.USER_NO = UCA.USER_NO");
strQry.AppendLine(" AND U.USER_NO <> " + parint64CurrentUserNo);
strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL ");
strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL ");
if (parstrAccessInd == "A")
{
strQry.AppendLine(" AND UCA1.USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" AND UCA1.COMPANY_NO = UCA.COMPANY_NO ");
strQry.AppendLine(" AND UCA1.DATETIME_DELETE_RECORD IS NULL ");
}
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" USER_ID");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" C.COMPANY_NO");
strQry.AppendLine(",C.COMPANY_DESC");
strQry.AppendLine(" FROM ");
strQry.AppendLine(" InteractPayroll.dbo.COMPANY_LINK C");
if (parstrAccessInd == "A")
{
strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA");
strQry.AppendLine(" WHERE UCA.COMPANY_ACCESS_IND = 'A' ");
strQry.AppendLine(" AND UCA.USER_NO = " + parint64CurrentUserNo);
strQry.AppendLine(" AND UCA.COMPANY_NO = C.COMPANY_NO ");
}
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" C.COMPANY_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", -1);
if (DataSet.Tables["User"].Rows.Count > 0)
{
byte[] bytTempCompress = Get_Company_CostCentres_Departments_Employees(Convert.ToInt32(DataSet.Tables["User"].Rows[0]["COMPANY_NO"]));
DataSet pvtTempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress);
DataSet.Merge(pvtTempDataSet);
}
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public void Update_Employee_Earnings_Deductions(Int64 parInt64CompanyNo, int parPayCategoryNo, string parNormPaidPerPeriod, string parSickPaidPerPeriod, byte[] patDataSetArray)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(patDataSetArray);
StringBuilder strQry = new StringBuilder();
for (int intRow = 0; intRow < parDataSet.Tables["Earning"].Rows.Count; intRow++)
{
if (parDataSet.Tables["Earning"].Rows[intRow].RowState == DataRowState.Added)
{
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");
strQry.AppendLine(",MINUTES");
strQry.AppendLine(",MINUTES_ROUNDED");
strQry.AppendLine(",HOURS_DECIMAL");
strQry.AppendLine(",HOURS_DECIMAL_OTHER_VALUE");
strQry.AppendLine(",HOURS_DECIMAL_ORIGINAL");
strQry.AppendLine(",DAY_DECIMAL_OTHER_VALUE");
strQry.AppendLine(",TOTAL)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parInt64CompanyNo);
strQry.AppendLine(",'" + Convert.ToDateTime(parDataSet.Tables["Earning"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine("," + parDataSet.Tables["Earning"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(",'P'");
strQry.AppendLine("," + parDataSet.Tables["Earning"].Rows[intRow]["EARNING_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["Earning"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Earning"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(",1");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine(",0");
strQry.AppendLine("," + parDataSet.Tables["Earning"].Rows[intRow]["TOTAL"].ToString() + ")");
}
else
{
//Updated
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_HISTORY");
strQry.AppendLine(" SET ");
strQry.AppendLine(" TOTAL = " + parDataSet.Tables["Earning"].Rows[intRow]["TOTAL"].ToString());
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Earning"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Earning"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND RUN_TYPE = 'P'");
strQry.AppendLine(" AND EARNING_NO = " + parDataSet.Tables["Earning"].Rows[intRow]["EARNING_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parDataSet.Tables["Earning"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Earning"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND RUN_NO = 1");
}
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
}
for (int intRow = 0; intRow < parDataSet.Tables["Deduction"].Rows.Count; intRow++)
{
strQry.Clear();
if (parDataSet.Tables["Deduction"].Rows[intRow].RowState == DataRowState.Added)
{
strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_HISTORY");
strQry.AppendLine("(COMPANY_NO ");
strQry.AppendLine(",PAY_PERIOD_DATE");
strQry.AppendLine(",EMPLOYEE_NO");
strQry.AppendLine(",RUN_TYPE");
strQry.AppendLine(",DEDUCTION_NO");
strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(",RUN_NO");
strQry.AppendLine(",TOTAL");
strQry.AppendLine(",TOTAL_ORIGINAL)");
strQry.AppendLine(" VALUES ");
strQry.AppendLine("(" + parInt64CompanyNo);
strQry.AppendLine(",'" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(",'P'");
strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_NO"].ToString());
strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_SUB_ACCOUNT_NO"].ToString());
strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(",1");
strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());
strQry.AppendLine("," + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString() + ")");
}
else
{
//Updated
if (parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_NO"].ToString() == "500")
{
if (parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_HISTORY");
strQry.AppendLine(" SET HOURLY_RATE = " + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND RUN_TYPE = 'P'");
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parPayCategoryNo);
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND RUN_NO = 1");
}
else
{
//Salaries
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY");
strQry.AppendLine(" SET SALARY_MONTH_PAYMENT = " + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND RUN_TYPE = 'P'");
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND RUN_NO = 1");
}
}
else
{
if (parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_NO"].ToString() == "600")
{
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY");
strQry.AppendLine(" SET LEAVE_ACCUM_DAYS = ROUND(" + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString() + " * " + parNormPaidPerPeriod + ",2)");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
//Normal Leave
strQry.AppendLine(" AND EARNING_NO = 200 ");
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND PROCESS_NO = 98");
strQry.AppendLine(" AND LEAVE_FROM_DATE > = '2015-03-01'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY");
strQry.AppendLine(" SET LEAVE_ACCUM_DAYS = ROUND(" + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString() + " * " + parSickPaidPerPeriod + ",2)");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
//Sick Leave
strQry.AppendLine(" AND EARNING_NO = 201 ");
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND PROCESS_NO = 98");
strQry.AppendLine(" AND LEAVE_FROM_DATE > = '2015-03-01'");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY");
strQry.AppendLine(" SET CURRENT_YEAR_LEAVE_SHIFTS_PER_RUN = " + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND RUN_TYPE = 'P'");
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND RUN_NO = 1");
}
else
{
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_HISTORY");
strQry.AppendLine(" SET ");
strQry.AppendLine(" TOTAL = " + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());
strQry.AppendLine(",TOTAL_ORIGINAL = " + parDataSet.Tables["Deduction"].Rows[intRow]["TOTAL"].ToString());
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo);
strQry.AppendLine(" AND PAY_PERIOD_DATE = '" + Convert.ToDateTime(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_PERIOD_DATE"]).ToString("yyyy-MM-dd") + "'");
strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND RUN_TYPE = 'P'");
strQry.AppendLine(" AND DEDUCTION_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_NO"].ToString());
strQry.AppendLine(" AND DEDUCTION_SUB_ACCOUNT_NO = " + parDataSet.Tables["Deduction"].Rows[intRow]["DEDUCTION_SUB_ACCOUNT_NO"].ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parDataSet.Tables["Deduction"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND RUN_NO = 1");
}
}
}
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 byte[] Get_Form_Records(Int64 parint64CompanyNo, Int64 parint64CurrentUserNo, string parstrCurrentUserAccessInd, string parstrFromProgram)
{
StringBuilder strQry = new StringBuilder();
DataSet DataSet = new DataSet();
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" C.WAGE_RUN_IND");
strQry.AppendLine(",C.SALARY_RUN_IND");
strQry.AppendLine(",C.TIME_ATTENDANCE_RUN_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY C");
strQry.AppendLine(" WHERE C.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parint64CompanyNo);
byte[] byteArrayDataset = Get_User_Level_Records(parint64CompanyNo, parint64CurrentUserNo, parstrCurrentUserAccessInd, parstrFromProgram);
DataSet TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteArrayDataset);
DataSet.Merge(TempDataSet);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" LC.COMPANY_NO");
strQry.AppendLine(",LC.EMPLOYEE_NO");
strQry.AppendLine(",LC.EARNING_NO");
strQry.AppendLine(",LC.LEAVE_REC_NO");
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
strQry.AppendLine(",LC.LEAVE_DESC");
strQry.AppendLine(",LC.PROCESS_NO");
strQry.AppendLine(",LC.LEAVE_FROM_DATE");
strQry.AppendLine(",LC.LEAVE_TO_DATE");
strQry.AppendLine(",ISNULL(LC.LEAVE_OPTION,'D') AS LEAVE_OPTION");
strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_HOURS_DECIMAL,0),2) AS LEAVE_HOURS_DECIMAL");
strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_DAYS_DECIMAL,0),2) AS LEAVE_DAYS_DECIMAL");
strQry.AppendLine(",DATEDIFF(d,LEAVE_FROM_DATE,LEAVE_TO_DATE ) + 1 AS DATE_DIFF_NO_DAYS");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC");
//2013-09-10
if (parstrCurrentUserAccessInd == "U")
{
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(SELECT DISTINCT");
strQry.AppendLine(" EMPLOYEE_NO");
strQry.AppendLine(",PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP ");
strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo + ") AS USER_TABLE");
strQry.AppendLine(" ON LC.EMPLOYEE_NO = USER_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = USER_TABLE.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" ON LC.COMPANY_NO = E.COMPANY_NO ");
strQry.AppendLine(" AND LC.EMPLOYEE_NO = E.EMPLOYEE_NO ");
strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
//Default Pay Category Parameters Apply to Leave
strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" WHERE LC.COMPANY_NO = " + parint64CompanyNo);
//Next Run
strQry.AppendLine(" AND LC.PROCESS_NO = 0");
if (parstrFromProgram == "X")
{
strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = 'T'");
}
else
{
strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE IN ('W','S')");
}
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" LEAVE_FROM_DATE");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Leave", parint64CompanyNo);
DataSet.Tables.Add("PayrollType");
DataTable PayrollTypeDataTable = new DataTable("PayrollType");
DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String));
if (DataSet.Tables["AuthorsiseLevel"].Rows.Count > 0)
{
DataView PayrollTypeDataView = new DataView(DataSet.Tables["AuthorsiseLevel"],
"PAY_CATEGORY_TYPE = 'W'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Wages";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
PayrollTypeDataView = null;
PayrollTypeDataView = new DataView(DataSet.Tables["AuthorsiseLevel"],
"PAY_CATEGORY_TYPE = 'S'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Salaries";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
PayrollTypeDataView = null;
PayrollTypeDataView = new DataView(DataSet.Tables["AuthorsiseLevel"],
"PAY_CATEGORY_TYPE = 'T'",
"",
DataViewRowState.CurrentRows);
if (PayrollTypeDataView.Count > 0)
{
DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow();
drDataRow["PAYROLL_TYPE_DESC"] = "Time Attendance";
DataSet.Tables["PayrollType"].Rows.Add(drDataRow);
}
DataSet.AcceptChanges();
}
strQry.Clear();
strQry.AppendLine(" SELECT DISTINCT ");
strQry.AppendLine(" EN.COMPANY_NO");
strQry.AppendLine(",EN.EARNING_NO");
strQry.AppendLine(",EN.PAY_CATEGORY_TYPE");
strQry.AppendLine(",EN.EARNING_DESC");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING EN ");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC");
strQry.AppendLine(" ON EN.COMPANY_NO = LC.COMPANY_NO ");
strQry.AppendLine(" AND EN.EARNING_NO = LC.EARNING_NO ");
strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = LC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" WHERE EN.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND EN.EARNING_NO >= 200");
strQry.AppendLine(" AND EN.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" EN.EARNING_DESC");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "LeaveType", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" E.COMPANY_NO");
strQry.AppendLine(",E.EMPLOYEE_NO");
strQry.AppendLine(",E.EMPLOYEE_CODE");
strQry.AppendLine(",E.EMPLOYEE_NAME");
strQry.AppendLine(",E.EMPLOYEE_SURNAME");
strQry.AppendLine(",E.PAY_CATEGORY_TYPE");
//Used For Leave Totals
strQry.AppendLine(",EPC.PAY_CATEGORY_NO");
strQry.AppendLine(",LS.LEAVE_PAID_ACCUMULATOR_IND");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC");
strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO");
strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE");
//Default Pay Category Parameters Apply to Leave
strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'");
strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL");
if (parstrCurrentUserAccessInd == "U")
{
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT ");
strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString());
strQry.AppendLine(" AND EPC.COMPANY_NO = UEPCT.COMPANY_NO");
strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO");
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE");
}
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT LS");
strQry.AppendLine(" ON E.COMPANY_NO = LS.COMPANY_NO");
strQry.AppendLine(" AND E.LEAVE_SHIFT_NO = LS.LEAVE_SHIFT_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LS.PAY_CATEGORY_TYPE");
strQry.AppendLine(" AND LS.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC");
strQry.AppendLine(" ON E.COMPANY_NO = LC.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = LC.EMPLOYEE_NO ");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LC.PAY_CATEGORY_TYPE");
//Next Run
strQry.AppendLine(" AND LC.PROCESS_NO = 0");
strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo);
//Removed 2012-04-06 - Allow For Employees Clocked on Client That have Not been Activated to be seen
//strQry.AppendLine(" AND EMPLOYEE_TAKEON_IND = 'Y'");
strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL");
strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL");
strQry.AppendLine(" ORDER BY ");
strQry.AppendLine(" E.EMPLOYEE_CODE");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parint64CompanyNo);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" PAY_CATEGORY_NO");
strQry.AppendLine(",DAY_NO");
strQry.AppendLine(",TIME_DECIMAL");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_TIME_DECIMAL");
strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" ORDER BY");
strQry.AppendLine(" PAY_CATEGORY_NO");
strQry.AppendLine(",DAY_NO");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategoryTimeDecimal", parint64CompanyNo);
DateTime dtDateNowAYearAgo = DateTime.Now.AddYears(-1);
strQry.Clear();
strQry.AppendLine(" SELECT");
strQry.AppendLine(" PUBLIC_HOLIDAY_DATE");
strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY PH ");
strQry.AppendLine(" WHERE PUBLIC_HOLIDAY_DATE > '" + dtDateNowAYearAgo.ToString("yyyy-MM-dd") + "'");
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PublicHoliday", parint64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = null;
return(bytCompress);
}
public void Convert_Client_Records(Int64 parint64CompanyNo, string parstrToPayCategoryType, byte[] parbytCompressDataSet)
{
DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbytCompressDataSet);
StringBuilder strQry = new StringBuilder();
strQry.Clear();
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" A.TABLE_NAME");
strQry.AppendLine(" FROM InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS A ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS B");
strQry.AppendLine(" ON B.TABLE_NAME = A.TABLE_NAME ");
strQry.AppendLine(" AND B.COLUMN_NAME = 'PAY_CATEGORY_NO' ");
strQry.AppendLine(" WHERE A.COLUMN_NAME = 'PAY_CATEGORY_TYPE'");
clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "TablesToFix");
for (int intRow = 0; intRow < DataSet.Tables["PayCategory"].Rows.Count; intRow++)
{
strQry.Clear();
strQry.AppendLine(" UPDATE UE ");
strQry.AppendLine(" SET UE.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_EMPLOYEE UE ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON UE.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND UE.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND UE.PAY_CATEGORY_TYPE = JOIN_TABLE.PAY_CATEGORY_TYPE");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" UPDATE E ");
strQry.AppendLine(" SET E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E ");
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_TYPE");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON E.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND E.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = JOIN_TABLE.PAY_CATEGORY_TYPE");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//Break
strQry.Clear();
if (parstrToPayCategoryType == "W")
{
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT ");
}
else
{
if (parstrToPayCategoryType == "S")
{
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT ");
}
else
{
//Time Attend
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT ");
}
}
strQry.AppendLine("(COMPANY_NO ");
strQry.AppendLine(",EMPLOYEE_NO ");
strQry.AppendLine(",PAY_CATEGORY_NO ");
strQry.AppendLine(",BREAK_DATE ");
strQry.AppendLine(",BREAK_SEQ ");
strQry.AppendLine(",BREAK_TIME_IN_MINUTES ");
strQry.AppendLine(",BREAK_TIME_OUT_MINUTES ");
strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES ");
strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES) ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" A.COMPANY_NO ");
strQry.AppendLine(",A.EMPLOYEE_NO ");
strQry.AppendLine(",A.PAY_CATEGORY_NO ");
strQry.AppendLine(",A.BREAK_DATE ");
strQry.AppendLine(",A.BREAK_SEQ ");
strQry.AppendLine(",A.BREAK_TIME_IN_MINUTES ");
strQry.AppendLine(",A.BREAK_TIME_OUT_MINUTES ");
strQry.AppendLine(",A.CLOCKED_TIME_IN_MINUTES ");
strQry.AppendLine(",A.CLOCKED_TIME_OUT_MINUTES ");
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT A");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT A");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT A");
}
}
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE A ");
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT A ");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT A ");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT A ");
}
}
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
//Timesheets
strQry.Clear();
if (parstrToPayCategoryType == "W")
{
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT ");
}
else
{
if (parstrToPayCategoryType == "S")
{
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT ");
}
else
{
//Time Attend
strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT ");
}
}
strQry.AppendLine("(COMPANY_NO ");
strQry.AppendLine(",EMPLOYEE_NO ");
strQry.AppendLine(",PAY_CATEGORY_NO ");
strQry.AppendLine(",TIMESHEET_DATE ");
strQry.AppendLine(",TIMESHEET_SEQ ");
strQry.AppendLine(",TIMESHEET_TIME_IN_MINUTES ");
strQry.AppendLine(",TIMESHEET_TIME_OUT_MINUTES ");
strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES ");
strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES) ");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" A.COMPANY_NO ");
strQry.AppendLine(",A.EMPLOYEE_NO ");
strQry.AppendLine(",A.PAY_CATEGORY_NO ");
strQry.AppendLine(",A.TIMESHEET_DATE ");
strQry.AppendLine(",A.TIMESHEET_SEQ ");
strQry.AppendLine(",A.TIMESHEET_TIME_IN_MINUTES ");
strQry.AppendLine(",A.TIMESHEET_TIME_OUT_MINUTES ");
strQry.AppendLine(",A.CLOCKED_TIME_IN_MINUTES ");
strQry.AppendLine(",A.CLOCKED_TIME_OUT_MINUTES ");
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT A ");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT A ");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT A ");
}
}
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
strQry.Clear();
strQry.AppendLine(" DELETE A ");
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT A ");
}
else
{
if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S")
{
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT A ");
}
else
{
//Time Attend
strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT A ");
}
}
strQry.AppendLine(" INNER JOIN ");
strQry.AppendLine("(");
strQry.AppendLine(" SELECT ");
strQry.AppendLine(" PC.COMPANY_NO");
strQry.AppendLine(",EPC.EMPLOYEE_NO");
strQry.AppendLine(",PC.PAY_CATEGORY_NO");
strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC ");
strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC ");
strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO ");
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE ");
strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(") AS JOIN_TABLE");
strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO ");
strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO");
strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO");
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
for (int intTableRow = 0; intTableRow < DataSet.Tables["TablesToFix"].Rows.Count; intTableRow++)
{
strQry.Clear();
strQry.AppendLine(" UPDATE A ");
strQry.AppendLine(" SET A.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType));
strQry.AppendLine(" FROM InteractPayrollClient.dbo." + DataSet.Tables["TablesToFix"].Rows[intTableRow]["TABLE_NAME"].ToString() + " A ");
strQry.AppendLine(" WHERE A.COMPANY_NO = " + parint64CompanyNo);
strQry.AppendLine(" AND A.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString()));
strQry.AppendLine(" AND A.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString());
clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString());
}
}
}
public byte[] Update_Records(Int64 parInt64CompanyNo, Int64 parint64CurrentUserNo, string parstrCurrentUserAccess, int parintPaycategoryNo, string parstrPayrollType, string parstrType, double pardblIncrease, byte[] parbyteDataSet)
{
DataSet parDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet);
StringBuilder strQry = new StringBuilder();
double dblCurrentValue = 0;
double dblNewValue = 0;
string strEmployeeNos = "(";
//Remove Rows from DataTable
for (int intRow = 0; intRow < parDataSet.Tables["Employee"].Rows.Count; intRow++)
{
if (parstrType == "P")
{
dblCurrentValue = Convert.ToDouble(parDataSet.Tables["Employee"].Rows[intRow]["HOURLY_RATE"]);
dblNewValue = dblCurrentValue + Math.Round((dblCurrentValue * pardblIncrease) / 100, 2);
}
strQry.Clear();
strQry.AppendLine(" UPDATE InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY");
strQry.AppendLine(" SET ");
strQry.AppendLine(" OLD_HOURLY_RATE = HOURLY_RATE");
if (parstrType == "P")
{
strQry.AppendLine(",HOURLY_RATE = " + dblNewValue);
strQry.AppendLine(",SALARY_WAGE_INCREASE = " + pardblIncrease);
}
else
{
strQry.AppendLine(",HOURLY_RATE = " + parDataSet.Tables["Employee"].Rows[intRow]["HOURLY_RATE"].ToString());
strQry.AppendLine(",SALARY_WAGE_INCREASE = " + parDataSet.Tables["Employee"].Rows[intRow]["SALARY_WAGE_INCREASE"].ToString());
}
strQry.AppendLine(",SALARY_WAGE_INCREASE_DATETIME = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'");
strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo.ToString());
strQry.AppendLine(" AND PAY_CATEGORY_NO = " + parintPaycategoryNo.ToString());
strQry.AppendLine(" AND PAY_CATEGORY_TYPE = '" + parstrPayrollType + "'");
strQry.AppendLine(" AND EMPLOYEE_NO = " + parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString());
strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL ");
clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo);
if (intRow == 0)
{
strEmployeeNos += parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString();
}
else
{
strEmployeeNos += "," + parDataSet.Tables["Employee"].Rows[intRow]["EMPLOYEE_NO"].ToString();
}
}
strEmployeeNos += ")";
DataSet DataSet = new System.Data.DataSet();
strQry = Get_Employee_Script(parint64CurrentUserNo, parstrCurrentUserAccess);
strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = " + parintPaycategoryNo);
strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = '" + parstrPayrollType + "'");
strQry.AppendLine(" AND EPC.EMPLOYEE_NO IN " + strEmployeeNos);
clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parInt64CompanyNo);
byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet);
DataSet.Dispose();
DataSet = 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(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);
}