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); }