public byte[] Get_Form_Records() { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" COMPANY_NO"); strQry.AppendLine(",COMPANY_DESC"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.COMPANY C"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" COMPANY_DESC"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company"); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); 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(); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" DEVICE_NO"); strQry.AppendLine(",DEVICE_DESC"); strQry.AppendLine(",DEVICE_USAGE"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE D"); 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 byte[] Get_Restore_Files() { DataSet DataSet = new System.Data.DataSet(); DataSet.Tables.Add("RestoreFiles"); DataSet.Tables["RestoreFiles"].Columns.Add("RESTORE_DATETIME", typeof(DateTime)); DataSet.Tables["RestoreFiles"].Columns.Add("RESTORE_FILE", typeof(String)); StringBuilder strQry = new StringBuilder(); string strFileDirectory = AppDomain.CurrentDomain.BaseDirectory + "Backup"; if (Directory.Exists(strFileDirectory)) { string strDataBaseName = "InteractPayrollClient_"; #if (DEBUG) strDataBaseName = "InteractPayrollClient_Debug_"; #endif string[] filePaths = Directory.GetFiles(@strFileDirectory, strDataBaseName + @"*.bak"); if (filePaths.Length > 0) { for (int intRow = 0; intRow < filePaths.Length; intRow++) { int intDateTimeOffset = filePaths[intRow].IndexOf(strDataBaseName) + strDataBaseName.Length; if (intDateTimeOffset + 19 <= filePaths[intRow].Length) { DataRow drDataRow = DataSet.Tables["RestoreFiles"].NewRow(); drDataRow["RESTORE_FILE"] = filePaths[intRow]; try { DateTime myFileDateTime = DateTime.ParseExact(filePaths[intRow].Substring(intDateTimeOffset, 15), "yyyyMMdd_HHmmss", null); drDataRow["RESTORE_DATETIME"] = myFileDateTime; } catch { } DataSet.Tables["RestoreFiles"].Rows.Add(drDataRow); } } } } DataSet.AcceptChanges(); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parint64CompanyNo) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); Get_Employee_DataSet(DataSet, parint64CompanyNo, -1); Get_CurrentPayCategory_DataSet(DataSet, parint64CompanyNo, -1); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" P.PAY_CATEGORY_TYPE"); strQry.AppendLine(",P.PAY_CATEGORY_NO"); strQry.AppendLine(",P.PAY_CATEGORY_DESC"); //Used in Reply strQry.AppendLine(",'' AS REC_RATE"); strQry.AppendLine(",'' AS DEFAULT_IND"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY P"); strQry.AppendLine(" WHERE P.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND P.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" AND P.PAY_CATEGORY_NO > 0"); strQry.AppendLine(" AND ISNULL(P.CLOSED_IND,'N') <> 'Y'"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" P.PAY_CATEGORY_TYPE"); strQry.AppendLine(",P.PAY_CATEGORY_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategory", parint64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" PAY_CATEGORY_TYPE"); strQry.AppendLine(",LEAVE_SHIFT_NO"); strQry.AppendLine(",LEAVE_SHIFT_DESC"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT"); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" PAY_CATEGORY_TYPE"); strQry.AppendLine(",LEAVE_SHIFT_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "LeaveShift", parint64CompanyNo); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parInt64CompanyNo, Int64 parint64CurrentUserNo, string parstrCurrentUserAccess) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" PC.PAY_CATEGORY_NO"); strQry.AppendLine(",PC.PAY_CATEGORY_TYPE"); strQry.AppendLine(",PC.PAY_CATEGORY_DESC"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC"); if (parstrCurrentUserAccess == "U") { strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT"); strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo); strQry.AppendLine(" AND PC.COMPANY_NO = UEPCT.COMPANY_NO"); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO"); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE"); } strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parInt64CompanyNo); strQry.AppendLine(" AND PC.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO > 0"); strQry.AppendLine(" GROUP BY "); strQry.AppendLine(" PC.PAY_CATEGORY_NO"); strQry.AppendLine(",PC.PAY_CATEGORY_TYPE"); strQry.AppendLine(",PC.PAY_CATEGORY_DESC"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" PC.PAY_CATEGORY_TYPE"); strQry.AppendLine(",PC.PAY_CATEGORY_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategory", parInt64CompanyNo); strQry.Clear(); strQry = Get_Employee_Script(parint64CurrentUserNo, parstrCurrentUserAccess); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parInt64CompanyNo); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(string parstrCurrentUserAccess, Int64 parint64CurrentUserNo) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); if (parstrCurrentUserAccess == "S") { strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" USER_NO"); strQry.AppendLine(",USER_ID"); strQry.AppendLine(",FIRSTNAME"); strQry.AppendLine(",SURNAME"); strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID"); strQry.AppendLine(" WHERE USER_NO <> " + parint64CurrentUserNo); strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL "); } else { strQry.Clear(); strQry.AppendLine(" SELECT DISTINCT"); strQry.AppendLine(" UI.USER_NO"); strQry.AppendLine(",UI.USER_ID"); strQry.AppendLine(",UI.FIRSTNAME"); strQry.AppendLine(",UI.SURNAME"); strQry.AppendLine(" FROM "); strQry.AppendLine(" InteractPayroll.dbo.USER_ID UI"); strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA "); strQry.AppendLine(" WHERE UI.USER_NO = UCA.USER_NO "); strQry.AppendLine(" AND UI.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" AND UCA.USER_NO <> " + parint64CurrentUserNo); strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" AND UI.SYSTEM_ADMINISTRATOR_IND <> 'Y'"); strQry.AppendLine(" AND STR(UCA.COMPANY_NO) IN "); strQry.AppendLine("(SELECT STR(COMPANY_NO) "); strQry.AppendLine(" FROM "); strQry.AppendLine(" InteractPayroll.dbo.USER_COMPANY_ACCESS "); strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo); strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" AND COMPANY_ACCESS_IND = 'A') "); } clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_PayCategory_Records(Int64 parint64CompanyNo, string parstrPayrollType) { //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT DISTINCT"); strQry.AppendLine(" EPC.PAY_CATEGORY_NO"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC "); strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO "); strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.PAY_CATEGORY PC "); strQry.AppendLine(" ON E.COMPANY_NO = PC.COMPANY_NO "); strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = PC.PAY_CATEGORY_NO "); strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = PC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo.ToString()); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrPayrollType)); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "PayCategory"); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parintUserNo) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" CL.COMPANY_DESC "); strQry.AppendLine(" FROM InteractPayroll.dbo.USER_COMPANY_TO_LOAD UCTL "); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.COMPANY_LINK CL "); strQry.AppendLine(" ON UCTL.COMPANY_NO = CL.COMPANY_NO "); strQry.AppendLine(" WHERE UCTL.USER_NO = " + parintUserNo.ToString()); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "CompanyName", -1); strQry.Clear(); strQry.AppendLine(" SELECT TOP 3"); strQry.AppendLine(" PAY_PERIOD_DATE "); strQry.AppendLine(" FROM InteractPayroll_00013.dbo.PAY_CATEGORY_PERIOD_HISTORY "); strQry.AppendLine(" WHERE COMPANY_NO = 13 "); strQry.AppendLine(" AND PAY_PERIOD_DATE > '2017-11-01' "); strQry.AppendLine(" AND RUN_TYPE = 'P' "); strQry.AppendLine(" AND PAY_CATEGORY_TYPE = 'W' "); strQry.AppendLine(" GROUP BY "); strQry.AppendLine(" PAY_PERIOD_DATE "); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" PAY_PERIOD_DATE DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayDates", -1); DataSet.AcceptChanges(); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records() { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" SITE_NAME"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "RemoteBackupSiteName"); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parint64CompanyNo) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT DISTINCT"); strQry.AppendLine(" E.COMPANY_NO"); strQry.AppendLine(",E.PAY_CATEGORY_TYPE"); strQry.AppendLine(",E.EARNING_NO"); strQry.AppendLine(",E.TIE_BREAKER"); strQry.AppendLine(",E.EARNING_DESC"); strQry.AppendLine(",E.LEAVE_PERCENTAGE"); strQry.AppendLine(",E.EARNING_REPORT_HEADER1"); strQry.AppendLine(",E.EARNING_REPORT_HEADER2"); strQry.AppendLine(",E.EARNING_DEL_IND"); strQry.AppendLine(",EEC.EARNING_NO AS PAYROLL_LINK"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING E"); strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING_CURRENT EEC"); strQry.AppendLine(" ON E.COMPANY_NO = EEC.COMPANY_NO "); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EEC.PAY_CATEGORY_TYPE"); strQry.AppendLine(" AND E.EARNING_NO = EEC.EARNING_NO"); strQry.AppendLine(" AND EEC.RUN_TYPE = 'P'"); strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" AND E.EARNING_NO > 199"); //Record has Not been Deleted strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" ORDER BY"); strQry.AppendLine(" E.EARNING_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "LeaveType", parint64CompanyNo); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parint64CompanyNo, string parstrMenuId) { DataSet DataSet = new DataSet(); StringBuilder strQry = new StringBuilder(); string strTableName = ""; if (parstrMenuId == "39") { strTableName = "DEPARTMENT"; } else { strTableName = "OCCUPATION"; } strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(strTableName + "_NO "); strQry.AppendLine("," + strTableName + "_DESC "); strQry.AppendLine(" FROM "); strQry.AppendLine("InteractPayroll_#CompanyNo#.dbo." + strTableName); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(strTableName + "_DESC "); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "OccupationDepartment", parint64CompanyNo); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parint64CompanyNo) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); //Used In Calender Menu Option strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" PUBLIC_HOLIDAY_DATE"); strQry.AppendLine(",PUBLIC_HOLIDAY_DESC"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" PUBLIC_HOLIDAY_DATE"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PaidHoliday", parint64CompanyNo); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Logon_Client_DataBase() { //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 try { //2017-05-03 Fix To Make Sure Databases are In Sync clsFixInteractPayrollClientDatabase clsFixInteractPayrollClientDatabase = new clsFixInteractPayrollClientDatabase(); clsFixInteractPayrollClientDatabase.Fix_Client_Database(); } catch { } DataSet DataSet = new DataSet(); StringBuilder strQry = new StringBuilder(); try { strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" FILE_NAME"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS"); //2013-07-18 - Empty (This Can be Removed in 2 -3 Months Time (For Backkward Compatibility) strQry.AppendLine(" WHERE FILE_LAYER_IND = 'ZZ'"); //Used to Delete (Cleanup) where File is Not Downloaded from Internet Site clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "ServerFile"); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" FILE_LAYER_IND"); strQry.AppendLine(",FILE_NAME"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS"); //Empty - Used to Add Files To be Deleted from Client Database strQry.AppendLine(" WHERE FILE_LAYER_IND = 'Z'"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "FileToDelete"); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" FILE_LAYER_IND"); strQry.AppendLine(",FILE_NAME"); strQry.AppendLine(",FILE_LAST_UPDATED_DATE"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS"); //2013-06-27 strQry.AppendLine(" WHERE FILE_LAYER_IND = 'P'"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "ClientFile"); string strBaseDirectory = AppDomain.CurrentDomain.BaseDirectory; #if (DEBUG) strBaseDirectory += "bin\\"; #endif //2013-06-27 DirectoryInfo di = new DirectoryInfo(strBaseDirectory); FileInfo[] fiFiles = di.GetFiles("*.*"); DataRow DataRow; foreach (FileInfo fi in fiFiles) { DataRow = DataSet.Tables["ClientFile"].NewRow(); DataRow["FILE_LAYER_IND"] = "S"; DataRow["FILE_NAME"] = fi.Name; DataRow["FILE_LAST_UPDATED_DATE"] = fi.LastWriteTime; DataSet.Tables["ClientFile"].Rows.Add(DataRow); } DataRow[] tempDataRows = DataSet.Tables["ClientFile"].Select("FILE_LAYER_IND ='S' AND (FILE_NAME Like '%dll_' OR FILE_NAME Like '%exe_')"); foreach (DataRow dr in tempDataRows) { string strName = dr["FILE_NAME"].ToString().Replace("dll_", "dll").Replace("exe_", "exe"); //Find File with Same Name Excluding '_' DataView fiDataView = new DataView(DataSet.Tables["ClientFile"], "FILE_LAYER_IND ='S' AND FILE_NAME = '" + strName + "'", "", DataViewRowState.CurrentRows); if (fiDataView.Count == 1) { fiDataView.Delete(0); } } DataSet.AcceptChanges(); DataTable DataTable = new DataTable("ReturnValues"); DataTable.Columns.Add("MACHINE_NAME", typeof(String)); DataTable.Columns.Add("MACHINE_IP", typeof(String)); DataSet.Tables.Add(DataTable); DataRow = DataSet.Tables["ReturnValues"].NewRow(); try { DataRow["MACHINE_NAME"] = System.Net.Dns.GetHostName(); } catch { DataRow["MACHINE_NAME"] = "UNKNOWN"; } try { DataRow["MACHINE_IP"] = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList[0].ToString(); } catch { DataRow["MACHINE_IP"] = "UNKNOWN"; } DataSet.Tables["ReturnValues"].Rows.Add(DataRow); DataSet.AcceptChanges(); } catch (Exception ex) { string strInnerExceptionMessage = ""; if (ex.InnerException != null) { strInnerExceptionMessage = ex.InnerException.Message; } using (StreamWriter writeLog = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "busClientPayrollLogon_Log.txt", true)) { writeLog.WriteLine(" ********** Exception = " + ex.Message + " " + strInnerExceptionMessage); } } byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parint64CompanyNo, Int64 parint64CurrentUserNo, string parstrCurrentUserAccessInd, string parstrFromProgram) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" C.WAGE_RUN_IND"); strQry.AppendLine(",C.SALARY_RUN_IND"); strQry.AppendLine(",C.TIME_ATTENDANCE_RUN_IND"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY C"); strQry.AppendLine(" WHERE C.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parint64CompanyNo); byte[] byteArrayDataset = Get_User_Level_Records(parint64CompanyNo, parint64CurrentUserNo, parstrCurrentUserAccessInd, parstrFromProgram); DataSet TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteArrayDataset); DataSet.Merge(TempDataSet); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" LC.COMPANY_NO"); strQry.AppendLine(",LC.EMPLOYEE_NO"); strQry.AppendLine(",LC.EARNING_NO"); strQry.AppendLine(",LC.LEAVE_REC_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_NO"); strQry.AppendLine(",E.PAY_CATEGORY_TYPE"); strQry.AppendLine(",LC.LEAVE_DESC"); strQry.AppendLine(",LC.PROCESS_NO"); strQry.AppendLine(",LC.LEAVE_FROM_DATE"); strQry.AppendLine(",LC.LEAVE_TO_DATE"); strQry.AppendLine(",ISNULL(LC.LEAVE_OPTION,'D') AS LEAVE_OPTION"); strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_HOURS_DECIMAL,0),2) AS LEAVE_HOURS_DECIMAL"); strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_DAYS_DECIMAL,0),2) AS LEAVE_DAYS_DECIMAL"); strQry.AppendLine(",DATEDIFF(d,LEAVE_FROM_DATE,LEAVE_TO_DATE ) + 1 AS DATE_DIFF_NO_DAYS"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC"); //2013-09-10 if (parstrCurrentUserAccessInd == "U") { strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("(SELECT DISTINCT"); strQry.AppendLine(" EMPLOYEE_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP "); strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo.ToString()); strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo + ") AS USER_TABLE"); strQry.AppendLine(" ON LC.EMPLOYEE_NO = USER_TABLE.EMPLOYEE_NO"); strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = USER_TABLE.PAY_CATEGORY_TYPE"); } strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E"); strQry.AppendLine(" ON LC.COMPANY_NO = E.COMPANY_NO "); strQry.AppendLine(" AND LC.EMPLOYEE_NO = E.EMPLOYEE_NO "); strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE"); strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC"); strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO"); strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE"); //Default Pay Category Parameters Apply to Leave strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'"); strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" WHERE LC.COMPANY_NO = " + parint64CompanyNo); //Next Run strQry.AppendLine(" AND LC.PROCESS_NO = 0"); if (parstrFromProgram == "X") { strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = 'T'"); } else { strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE IN ('W','S')"); } strQry.AppendLine(" ORDER BY"); strQry.AppendLine(" LEAVE_FROM_DATE"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Leave", parint64CompanyNo); DataSet.Tables.Add("PayrollType"); DataTable PayrollTypeDataTable = new DataTable("PayrollType"); DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String)); if (DataSet.Tables["AuthorsiseLevel"].Rows.Count > 0) { DataView PayrollTypeDataView = new DataView(DataSet.Tables["AuthorsiseLevel"], "PAY_CATEGORY_TYPE = 'W'", "", DataViewRowState.CurrentRows); if (PayrollTypeDataView.Count > 0) { DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow(); drDataRow["PAYROLL_TYPE_DESC"] = "Wages"; DataSet.Tables["PayrollType"].Rows.Add(drDataRow); } PayrollTypeDataView = null; PayrollTypeDataView = new DataView(DataSet.Tables["AuthorsiseLevel"], "PAY_CATEGORY_TYPE = 'S'", "", DataViewRowState.CurrentRows); if (PayrollTypeDataView.Count > 0) { DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow(); drDataRow["PAYROLL_TYPE_DESC"] = "Salaries"; DataSet.Tables["PayrollType"].Rows.Add(drDataRow); } PayrollTypeDataView = null; PayrollTypeDataView = new DataView(DataSet.Tables["AuthorsiseLevel"], "PAY_CATEGORY_TYPE = 'T'", "", DataViewRowState.CurrentRows); if (PayrollTypeDataView.Count > 0) { DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow(); drDataRow["PAYROLL_TYPE_DESC"] = "Time Attendance"; DataSet.Tables["PayrollType"].Rows.Add(drDataRow); } DataSet.AcceptChanges(); } strQry.Clear(); strQry.AppendLine(" SELECT DISTINCT "); strQry.AppendLine(" EN.COMPANY_NO"); strQry.AppendLine(",EN.EARNING_NO"); strQry.AppendLine(",EN.PAY_CATEGORY_TYPE"); strQry.AppendLine(",EN.EARNING_DESC"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING EN "); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC"); strQry.AppendLine(" ON EN.COMPANY_NO = LC.COMPANY_NO "); strQry.AppendLine(" AND EN.EARNING_NO = LC.EARNING_NO "); strQry.AppendLine(" AND EN.PAY_CATEGORY_TYPE = LC.PAY_CATEGORY_TYPE"); strQry.AppendLine(" WHERE EN.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND EN.EARNING_NO >= 200"); strQry.AppendLine(" AND EN.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" EN.EARNING_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "LeaveType", parint64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" E.COMPANY_NO"); strQry.AppendLine(",E.EMPLOYEE_NO"); strQry.AppendLine(",E.EMPLOYEE_CODE"); strQry.AppendLine(",E.EMPLOYEE_NAME"); strQry.AppendLine(",E.EMPLOYEE_SURNAME"); strQry.AppendLine(",E.PAY_CATEGORY_TYPE"); //Used For Leave Totals strQry.AppendLine(",EPC.PAY_CATEGORY_NO"); strQry.AppendLine(",LS.LEAVE_PAID_ACCUMULATOR_IND"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E"); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC"); strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO"); strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE"); //Default Pay Category Parameters Apply to Leave strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'"); strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL"); if (parstrCurrentUserAccessInd == "U") { strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT "); strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString()); strQry.AppendLine(" AND EPC.COMPANY_NO = UEPCT.COMPANY_NO"); strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO"); strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO"); strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE"); } strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT LS"); strQry.AppendLine(" ON E.COMPANY_NO = LS.COMPANY_NO"); strQry.AppendLine(" AND E.LEAVE_SHIFT_NO = LS.LEAVE_SHIFT_NO"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LS.PAY_CATEGORY_TYPE"); strQry.AppendLine(" AND LS.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC"); strQry.AppendLine(" ON E.COMPANY_NO = LC.COMPANY_NO "); strQry.AppendLine(" AND E.EMPLOYEE_NO = LC.EMPLOYEE_NO "); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LC.PAY_CATEGORY_TYPE"); //Next Run strQry.AppendLine(" AND LC.PROCESS_NO = 0"); strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo); //Removed 2012-04-06 - Allow For Employees Clocked on Client That have Not been Activated to be seen //strQry.AppendLine(" AND EMPLOYEE_TAKEON_IND = 'Y'"); strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL"); strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" E.EMPLOYEE_CODE"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parint64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" PAY_CATEGORY_NO"); strQry.AppendLine(",DAY_NO"); strQry.AppendLine(",TIME_DECIMAL"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_TIME_DECIMAL"); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" ORDER BY"); strQry.AppendLine(" PAY_CATEGORY_NO"); strQry.AppendLine(",DAY_NO"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategoryTimeDecimal", parint64CompanyNo); DateTime dtDateNowAYearAgo = DateTime.Now.AddYears(-1); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" PUBLIC_HOLIDAY_DATE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY PH "); strQry.AppendLine(" WHERE PUBLIC_HOLIDAY_DATE > '" + dtDateNowAYearAgo.ToString("yyyy-MM-dd") + "'"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PublicHoliday", parint64CompanyNo); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parint64CompanyNo, string parstrCurrentUserAccess, Int64 parint64CurrentUserNo) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT"); if (parstrCurrentUserAccess == "S" | parstrCurrentUserAccess == "A") { strQry.AppendLine(" 'A' AS ACCESS_IND"); } else { strQry.AppendLine(" 'U' AS ACCESS_IND"); } strQry.AppendLine(",C.WAGE_RUN_IND"); strQry.AppendLine(",C.SALARY_RUN_IND"); strQry.AppendLine(",MAX(PCPC.PAY_PERIOD_DATE) AS WAGE_PAY_PERIOD_DATE"); strQry.AppendLine(",MAX(PCPC1.PAY_PERIOD_DATE) AS SALARY_PAY_PERIOD_DATE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY C"); strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_CURRENT PCPC"); strQry.AppendLine(" ON C.COMPANY_NO = PCPC.COMPANY_NO"); strQry.AppendLine(" AND PCPC.RUN_TYPE = 'P'"); strQry.AppendLine(" AND PCPC.PAY_CATEGORY_TYPE = 'W'"); strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_CURRENT PCPC1"); strQry.AppendLine(" ON C.COMPANY_NO = PCPC1.COMPANY_NO"); strQry.AppendLine(" AND PCPC1.RUN_TYPE = 'P'"); strQry.AppendLine(" AND PCPC1.PAY_CATEGORY_TYPE = 'S'"); strQry.AppendLine(" WHERE C.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" GROUP BY "); strQry.AppendLine(" C.WAGE_RUN_IND"); strQry.AppendLine(",C.SALARY_RUN_IND"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parint64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" LC.COMPANY_NO"); strQry.AppendLine(",LC.EMPLOYEE_NO"); strQry.AppendLine(",LC.EARNING_NO"); strQry.AppendLine(",LC.LEAVE_REC_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_NO"); strQry.AppendLine(",E.PAY_CATEGORY_TYPE"); strQry.AppendLine(",LC.LEAVE_DESC"); strQry.AppendLine(",LC.PROCESS_NO"); strQry.AppendLine(",LC.LEAVE_FROM_DATE"); strQry.AppendLine(",LC.LEAVE_TO_DATE"); //2013-09-11 strQry.AppendLine(",ISNULL(LEAVE_TEMP.AUTHORISED_IND,'N') AS AUTHORISED_IND"); strQry.AppendLine(",ISNULL(LC.LEAVE_OPTION,'D') AS LEAVE_OPTION"); strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_HOURS_DECIMAL,0),2) AS LEAVE_HOURS_DECIMAL"); strQry.AppendLine(",ROUND(ISNULL(LC.LEAVE_DAYS_DECIMAL,0),2) AS LEAVE_DAYS_DECIMAL"); strQry.AppendLine(",DATEDIFF(d,LEAVE_FROM_DATE,LEAVE_TO_DATE ) + 1 AS DATE_DIFF_NO_DAYS"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_CURRENT LC"); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E"); strQry.AppendLine(" ON LC.COMPANY_NO = E.COMPANY_NO "); strQry.AppendLine(" AND LC.EMPLOYEE_NO = E.EMPLOYEE_NO "); strQry.AppendLine(" AND LC.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE"); strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL"); //2017-01-24 (Employee Not Closed) strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL"); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC"); strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO"); strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE"); //Default Pay Category Parameters Apply to Leave strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'"); strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL"); //2013-08-29 if (parstrCurrentUserAccess == "U") { strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT"); strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo); strQry.AppendLine(" AND EPC.COMPANY_NO = UEPCT.COMPANY_NO"); strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO"); strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO"); strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE"); } strQry.AppendLine(" LEFT JOIN "); strQry.AppendLine("(SELECT DISTINCT"); strQry.AppendLine(" EPCLAC.EMPLOYEE_NO"); strQry.AppendLine(",EPCLAC.PAY_CATEGORY_NO"); strQry.AppendLine(",EPCLAC.PAY_CATEGORY_TYPE"); strQry.AppendLine(",EPCLAC.EARNING_NO"); strQry.AppendLine(",EPCLAC.LEAVE_REC_NO"); strQry.AppendLine(",EPCLAC.AUTHORISED_IND"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_LEAVE_AUTHORISE_CURRENT EPCLAC"); strQry.AppendLine(" WHERE EPCLAC.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND EPCLAC.AUTHORISED_IND = 'Y') AS LEAVE_TEMP "); strQry.AppendLine(" ON LC.EMPLOYEE_NO = LEAVE_TEMP.EMPLOYEE_NO"); strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = LEAVE_TEMP.PAY_CATEGORY_NO"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LEAVE_TEMP.PAY_CATEGORY_TYPE"); strQry.AppendLine(" AND LC.EARNING_NO = LEAVE_TEMP.EARNING_NO"); strQry.AppendLine(" AND LC.LEAVE_REC_NO = LEAVE_TEMP.LEAVE_REC_NO"); strQry.AppendLine(" WHERE LC.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" ORDER BY"); strQry.AppendLine(" LEAVE_FROM_DATE"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Leave", parint64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" PROCESS_NO"); strQry.AppendLine(",PROCESS_DESC"); strQry.AppendLine(" FROM InteractPayroll.dbo.PROCESS "); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" PROCESS_NO"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Process", parint64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" COMPANY_NO"); strQry.AppendLine(",EARNING_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE"); strQry.AppendLine(",EARNING_DESC"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING "); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND EARNING_NO >= 200"); strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" EARNING_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "LeaveType", parint64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" E.COMPANY_NO"); strQry.AppendLine(",E.EMPLOYEE_NO"); strQry.AppendLine(",E.EMPLOYEE_CODE"); strQry.AppendLine(",E.EMPLOYEE_NAME"); strQry.AppendLine(",E.EMPLOYEE_SURNAME"); strQry.AppendLine(",E.PAY_CATEGORY_TYPE"); //Used For Leave Totals strQry.AppendLine(",EPC.PAY_CATEGORY_NO"); strQry.AppendLine(",LS.LEAVE_PAID_ACCUMULATOR_IND"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E"); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY EPC"); strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO"); strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE"); //Default Pay Category Parameters Apply to Leave strQry.AppendLine(" AND EPC.DEFAULT_IND = 'Y'"); strQry.AppendLine(" AND EPC.DATETIME_DELETE_RECORD IS NULL"); //2013-09-11 if (parstrCurrentUserAccess == "U") { strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT"); strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo); strQry.AppendLine(" AND EPC.COMPANY_NO = UEPCT.COMPANY_NO"); strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO"); strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO"); strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE"); } strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT LS"); strQry.AppendLine(" ON E.COMPANY_NO = LS.COMPANY_NO"); strQry.AppendLine(" AND E.LEAVE_SHIFT_NO = LS.LEAVE_SHIFT_NO"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = LS.PAY_CATEGORY_TYPE"); strQry.AppendLine(" AND LS.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo); //Removed 2012-04-06 - Allow For Employees Clocked on Client That have Not been Activated to be seen //strQry.AppendLine(" AND EMPLOYEE_TAKEON_IND = 'Y'"); strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL"); strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" E.EMPLOYEE_CODE"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parint64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" PAY_CATEGORY_NO"); strQry.AppendLine(",DAY_NO"); strQry.AppendLine(",TIME_DECIMAL"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_TIME_DECIMAL"); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" ORDER BY"); strQry.AppendLine(" PAY_CATEGORY_NO"); strQry.AppendLine(",DAY_NO"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategoryTimeDecimal", parint64CompanyNo); DateTime dtDateNowAYearAgo = DateTime.Now.AddYears(-1); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" PUBLIC_HOLIDAY_DATE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PUBLIC_HOLIDAY PH "); strQry.AppendLine(" WHERE PUBLIC_HOLIDAY_DATE > '" + dtDateNowAYearAgo.ToString("yyyy-MM-dd") + "'"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PublicHoliday", parint64CompanyNo); DateTime dtStartTaxYear; if (DateTime.Now.Month > 2) { dtStartTaxYear = new DateTime(DateTime.Now.Year, 3, 1); } else { dtStartTaxYear = new DateTime(DateTime.Now.Year - 1, 3, 1); } strQry.Clear(); //Normal Leave And Sick Leave strQry.AppendLine(" SELECT "); strQry.AppendLine(" LH.PAY_CATEGORY_TYPE"); strQry.AppendLine(",LH.EMPLOYEE_NO"); strQry.AppendLine(",LH.EARNING_NO"); strQry.AppendLine(",ROUND(SUM(LH.LEAVE_ACCUM_DAYS - LH.LEAVE_PAID_DAYS),2) AS TOTAL_LEAVE_DAYS"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY LH"); //2013-09-10 if (parstrCurrentUserAccess == "U") { strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("(SELECT DISTINCT"); strQry.AppendLine(" EMPLOYEE_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP "); strQry.AppendLine(" WHERE USER_NO = " + parint64CurrentUserNo); strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo + ") AS USER_TABLE"); strQry.AppendLine(" ON LH.EMPLOYEE_NO = USER_TABLE.EMPLOYEE_NO"); strQry.AppendLine(" AND LH.PAY_CATEGORY_TYPE = USER_TABLE.PAY_CATEGORY_TYPE"); } strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E"); strQry.AppendLine(" ON LH.COMPANY_NO = E.COMPANY_NO "); strQry.AppendLine(" AND LH.EMPLOYEE_NO = E.EMPLOYEE_NO "); strQry.AppendLine(" AND LH.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE"); strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL"); //2017-01-24 (Employee Not Closed) strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL"); strQry.AppendLine(" WHERE LH.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND (LH.EARNING_NO = 200"); strQry.AppendLine(" OR (LH.EARNING_NO = 201"); strQry.AppendLine(" AND LH.PAY_PERIOD_DATE >= '" + dtStartTaxYear.ToString("yyyy-MM-dd") + "'))"); strQry.AppendLine(" GROUP BY "); strQry.AppendLine(" LH.PAY_CATEGORY_TYPE"); strQry.AppendLine(",LH.EMPLOYEE_NO"); strQry.AppendLine(",LH.EARNING_NO"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeeLeaveTotals", parint64CompanyNo); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 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); }
private void btnRunFix_Click(object sender, EventArgs e) { try { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); //Money Mine (Johnny) Int64 Int64CompanyNo = 11; object[] objParm = new object[1]; objParm[0] = Int64CompanyNo; bool blnAllow = (Boolean)clsISUtilities.DynamicFunction("Get_Form_Records", objParm); if (blnAllow == true) { DataSet = null; DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT "); 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(",DEPARTMENT_NO"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE "); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Employee"); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" COMPANY_NO"); strQry.AppendLine(",EMPLOYEE_NO"); strQry.AppendLine(",PAY_CATEGORY_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY "); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "EmployeePayCategory"); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); objParm = new object[2]; objParm[0] = Int64CompanyNo; objParm[1] = bytCompress; clsISUtilities.DynamicFunction("Upload_Records", objParm); MessageBox.Show("Upload Records Successful", "Communication", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("You are NOT allowed to Upload Records", "Communication", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception eException) { MessageBox.Show("Error = " + eException.Message); clsISUtilities.ErrorHandler(eException); } }
public byte[] Insert_Records(string parstrParm) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); int intTimeMinutes = 0; strQry.Clear(); strQry.AppendLine(" SELECT DISTINCT "); strQry.AppendLine(" EC.EMPLOYEE_NO"); strQry.AppendLine(" FROM ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS EC "); strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.EMPLOYEE E "); strQry.AppendLine(" ON EC.EMPLOYEE_NO = E.EMPLOYEE_3RD_PARTY_CODE "); //No Link strQry.AppendLine(" WHERE E.EMPLOYEE_3RD_PARTY_CODE IS NULL"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "LinksMissing"); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" EMPLOYEE_NO"); strQry.AppendLine(",CLOCKING_TIME"); strQry.AppendLine(",CLOCKING_TYPE"); strQry.AppendLine(" FROM ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS "); strQry.AppendLine(" WHERE ISNULL(USED_IND,0) = 0"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Clockings"); for (int intCount = 0; intCount < DataSet.Tables["Clockings"].Rows.Count; intCount++) { strQry.Clear(); intTimeMinutes = (60 * Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).Hour) + Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).Minute; strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_CLOCK_TIME"); strQry.AppendLine("(DEVICE_NO"); strQry.AppendLine(",COMPANY_NO"); strQry.AppendLine(",EMPLOYEE_NO"); strQry.AppendLine(",PAY_CATEGORY_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE"); strQry.AppendLine(",TIMESHEET_DATE"); strQry.AppendLine(",TIMESHEET_TIME_MINUTES"); strQry.AppendLine(",CLOCKED_BOUNDARY_TIME_MINUTES"); strQry.AppendLine(",IN_OUT_IND)"); strQry.AppendLine(" SELECT "); strQry.AppendLine(" 0 "); strQry.AppendLine(",EPC.COMPANY_NO"); strQry.AppendLine(",EPC.EMPLOYEE_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE"); //TIMESHEET_DATE strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd"))); //TIMESHEET_TIME_MINUTES strQry.AppendLine("," + intTimeMinutes); //CLOCKED_BOUNDARY_TIME_MINUTES Same as TIMESHEET_TIME_MINUTES strQry.AppendLine("," + intTimeMinutes); strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString())); strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC "); strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO "); strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO "); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_CLOCK_TIME DCT"); strQry.AppendLine(" ON DCT.DEVICE_NO = 0"); strQry.AppendLine(" AND DCT.COMPANY_NO = EPC.COMPANY_NO"); strQry.AppendLine(" AND DCT.EMPLOYEE_NO = EPC.EMPLOYEE_NO"); strQry.AppendLine(" AND DCT.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO"); strQry.AppendLine(" AND DCT.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE"); strQry.AppendLine(" AND DCT.TIMESHEET_DATE = " + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd"))); strQry.AppendLine(" AND DCT.TIMESHEET_TIME_MINUTES = " + intTimeMinutes); strQry.AppendLine(" AND DCT.IN_OUT_IND = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString())); strQry.AppendLine(" WHERE E.EMPLOYEE_3RD_PARTY_CODE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["EMPLOYEE_NO"].ToString())); //No Record Exists strQry.AppendLine(" AND DCT.COMPANY_NO IS NULL"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" UPDATE ShoutItNowTest.dbo.EMPLOYEE_CLOCKINGS "); strQry.AppendLine(" SET USED_IND = 1"); strQry.AppendLine(" WHERE EMPLOYEE_NO = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["EMPLOYEE_NO"].ToString())); strQry.AppendLine(" AND CLOCKING_TIME = " + clsDBConnectionObjects.Text2DynamicSQL(Convert.ToDateTime(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TIME"]).ToString("yyyy-MM-dd HH:mm:ss.fff"))); strQry.AppendLine(" AND CLOCKING_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["Clockings"].Rows[intCount]["CLOCKING_TYPE"].ToString())); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(string parstrCurrentUserAccess, Int64 parint64CurrentUserNo) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.Clear(); if (parstrCurrentUserAccess == "S") { strQry.AppendLine(" SELECT "); strQry.AppendLine(" U.USER_NO"); strQry.AppendLine(",U.USER_ID"); strQry.AppendLine(",U.FIRSTNAME"); strQry.AppendLine(",U.SURNAME"); strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U"); strQry.AppendLine(" WHERE U.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" UNION "); strQry.AppendLine(" SELECT "); strQry.AppendLine(" 0 AS USER_NO"); strQry.AppendLine(",'Admin' AS USER_ID"); strQry.AppendLine(",'Admin' AS FIRSTNAME"); strQry.AppendLine(",'Admin' AS SURNAME"); //Get 1 Row Only strQry.AppendLine(" FROM InteractPayroll.dbo.COUNTRY C"); strQry.AppendLine(" WHERE C.COUNTRY_CODE = 'ZAF' "); } else { strQry.AppendLine(" SELECT "); strQry.AppendLine(" U.USER_NO"); strQry.AppendLine(",U.USER_ID"); strQry.AppendLine(",U.FIRSTNAME"); strQry.AppendLine(",U.SURNAME"); strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U"); strQry.AppendLine(" WHERE U.SYSTEM_ADMINISTRATOR_IND = 'Y' "); strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" UNION "); strQry.AppendLine(" SELECT "); strQry.AppendLine(" 0 AS USER_NO"); strQry.AppendLine(",'Admin' AS USER_ID"); strQry.AppendLine(",'Admin' AS FIRSTNAME"); strQry.AppendLine(",'Admin' AS SURNAME"); //Get 1 Row Only strQry.AppendLine(" FROM InteractPayroll.dbo.COUNTRY C"); strQry.AppendLine(" WHERE C.COUNTRY_CODE = 'ZAF' "); } strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" 4"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parint64CompanyNo) { string strQry = ""; DataSet DataSet = new DataSet(); strQry = ""; strQry += " SELECT "; strQry += " COMPANY_NO "; strQry += ",EMPLOYEE_NO "; strQry += ",PAY_CATEGORY_TYPE "; strQry += ",EMPLOYEE_NAME "; strQry += ",EMPLOYEE_SURNAME "; strQry += ",EMPLOYEE_CODE "; strQry += " FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE"; strQry += " WHERE DATETIME_DELETE_RECORD IS NULL "; strQry += " ORDER BY "; strQry += " COMPANY_NO "; strQry += ",EMPLOYEE_SURNAME "; clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "Employee", parint64CompanyNo); strQry = ""; strQry += " SELECT "; strQry += " COMPANY_NO "; strQry += ",EMPLOYEE_NO "; strQry += ",PAY_CATEGORY_NO "; strQry += ",PAY_CATEGORY_TYPE "; strQry += " FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY"; strQry += " WHERE DATETIME_DELETE_RECORD IS NULL "; strQry += " AND DEFAULT_IND = 'Y' "; clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "EmployeePayCategory", parint64CompanyNo); strQry = ""; strQry += " SELECT "; strQry += " COMPANY_NO "; strQry += ",PAY_CATEGORY_NO "; strQry += ",PAY_CATEGORY_TYPE "; strQry += ",PAY_CATEGORY_DESC "; strQry += " FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY"; strQry += " WHERE DATETIME_DELETE_RECORD IS NULL "; strQry += " AND PAY_CATEGORY_NO > 0 "; strQry += " ORDER BY "; strQry += " COMPANY_NO "; strQry += ",PAY_CATEGORY_DESC "; clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "PayCategory", parint64CompanyNo); strQry = ""; strQry += " SELECT "; strQry += " COMPANY_NO "; strQry += ",DEPARTMENT_NO "; strQry += ",DEPARTMENT_DESC "; strQry += " FROM InteractPayroll_#CompanyNo#.dbo.DEPARTMENT"; strQry += " WHERE DATETIME_DELETE_RECORD IS NULL "; strQry += " ORDER BY "; strQry += " COMPANY_NO "; strQry += ",DEPARTMENT_DESC "; clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "Department", parint64CompanyNo); strQry = ""; strQry += " SELECT "; strQry += " COMPANY_NO "; strQry += ",OCCUPATION_NO "; strQry += ",OCCUPATION_DESC "; strQry += " FROM InteractPayroll_#CompanyNo#.dbo.OCCUPATION"; strQry += " WHERE DATETIME_DELETE_RECORD IS NULL "; strQry += " ORDER BY "; strQry += " COMPANY_NO "; strQry += ",OCCUPATION_DESC "; clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "Occupation", parint64CompanyNo); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Users(string parstrProgramFromInd, Int64 parCurrentUserNo, string parstrCurrentUserAccessInd) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" U.USER_NO"); strQry.AppendLine(",U.USER_ID"); strQry.AppendLine(",U.FIRSTNAME"); strQry.AppendLine(",U.SURNAME"); strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U"); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA"); strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO"); strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND <> 'A' "); strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL "); if (parstrCurrentUserAccessInd != "S") { strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("(SELECT "); strQry.AppendLine(" UCA.COMPANY_NO"); strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U"); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA"); strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO"); strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' "); strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo); strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE "); strQry.AppendLine(" ON UCA.COMPANY_NO = TEMP_TABLE.COMPANY_NO"); } strQry.AppendLine(" GROUP BY "); strQry.AppendLine(" U.USER_NO"); strQry.AppendLine(",U.USER_ID"); strQry.AppendLine(",U.FIRSTNAME"); strQry.AppendLine(",U.SURNAME"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" U.USER_NO"); strQry.AppendLine(",UCA.COMPANY_NO"); strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U"); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA"); strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO"); strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND <> 'A' "); strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL "); if (parstrCurrentUserAccessInd != "S") { strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("(SELECT "); strQry.AppendLine(" UCA.COMPANY_NO"); strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U"); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA"); strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO"); strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' "); strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo); strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE "); strQry.AppendLine(" ON UCA.COMPANY_NO = TEMP_TABLE.COMPANY_NO"); } strQry.AppendLine(" GROUP BY "); strQry.AppendLine(" U.USER_NO"); strQry.AppendLine(",UCA.COMPANY_NO"); strQry.AppendLine(" ORDER BY"); strQry.AppendLine(" U.USER_NO"); strQry.AppendLine(",UCA.COMPANY_NO"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "UserCompany", -1); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" CL.COMPANY_NO"); strQry.AppendLine(",CL.COMPANY_DESC"); strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK CL"); if (parstrCurrentUserAccessInd != "S") { strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("(SELECT "); strQry.AppendLine(" UCA.COMPANY_NO"); strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U"); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.USER_COMPANY_ACCESS UCA"); strQry.AppendLine(" ON U.USER_NO = UCA.USER_NO"); strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' "); strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" WHERE UCA.USER_NO = " + parCurrentUserNo); strQry.AppendLine(" AND U.DATETIME_DELETE_RECORD IS NULL) AS TEMP_TABLE "); strQry.AppendLine(" ON CL.COMPANY_NO = TEMP_TABLE.COMPANY_NO"); } strQry.AppendLine(" GROUP BY"); strQry.AppendLine(" CL.COMPANY_NO"); strQry.AppendLine(",CL.COMPANY_DESC"); strQry.AppendLine(" ORDER BY"); strQry.AppendLine(" CL.COMPANY_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", -1); if (DataSet.Tables["User"].Rows.Count > 0) { byte[] bytTempCompress = Get_User_Menus(parstrProgramFromInd, Convert.ToInt32(DataSet.Tables["User"].Rows[0]["USER_NO"])); DataSet TempDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytTempCompress); DataSet.Merge(TempDataSet); } byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parInt64CompanyNo) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); //So That Delete Will Happen string strEmployeeNoIn = "-1"; //Insert Records strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" L.PAY_CATEGORY_TYPE"); strQry.AppendLine(",L.EMPLOYEE_NO"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY L "); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E "); strQry.AppendLine(" ON L.COMPANY_NO = E.COMPANY_NO "); strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE "); strQry.AppendLine(" AND L.EMPLOYEE_NO = E.EMPLOYEE_NO "); strQry.AppendLine(" AND E.EMPLOYEE_ENDDATE IS NULL "); //Add 180 Days (Withinn 6 Months) strQry.AppendLine(" AND DATEADD(DD,180,L.PAY_PERIOD_DATE) >= GETDATE() "); strQry.AppendLine(" LEFT JOIN "); strQry.AppendLine("(SELECT "); strQry.AppendLine(" PAY_CATEGORY_TYPE"); strQry.AppendLine(",EMPLOYEE_NO"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_HISTORY "); strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo); //2013-06-21 Exclude T=Time Attendance strQry.AppendLine(" AND PAY_CATEGORY_TYPE IN ('W','S')"); //Take-On Balance / Take-On Balance (Previous Year) strQry.AppendLine(" AND PROCESS_NO IN (99,100)"); //Normal Leave / Sick Leave strQry.AppendLine(" AND EARNING_NO IN (200,201)"); strQry.AppendLine(" AND LEAVE_ACCUM_DAYS > 0) AS TEMP_LEAVE"); strQry.AppendLine(" ON L.PAY_CATEGORY_TYPE = TEMP_LEAVE.PAY_CATEGORY_TYPE "); strQry.AppendLine(" AND L.EMPLOYEE_NO = TEMP_LEAVE.EMPLOYEE_NO "); strQry.AppendLine(" WHERE L.COMPANY_NO = " + parInt64CompanyNo); //2013-06-21 Exclude T=Time Attendance strQry.AppendLine(" AND L.PAY_CATEGORY_TYPE IN ('W','S')"); //Take-On Balance strQry.AppendLine(" AND L.PROCESS_NO = 99"); //Normal Leave / Sick Leave strQry.AppendLine(" AND L.EARNING_NO IN (200,201)"); strQry.AppendLine(" AND L.LEAVE_ACCUM_DAYS = 0"); strQry.AppendLine(" AND L.LEAVE_PAID_DAYS = 0"); //No Take-On Leave Balances strQry.AppendLine(" AND TEMP_LEAVE.PAY_CATEGORY_TYPE IS NULL"); strQry.AppendLine(" GROUP BY "); strQry.AppendLine(" L.PAY_CATEGORY_TYPE "); strQry.AppendLine(",L.EMPLOYEE_NO "); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeeTemp", parInt64CompanyNo); for (int intRow = 0; intRow < DataSet.Tables["EmployeeTemp"].Rows.Count; intRow++) { if (intRow == 0) { strEmployeeNoIn = DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString(); } else { strEmployeeNoIn += "," + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString(); } strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON "); strQry.AppendLine("(COMPANY_NO"); strQry.AppendLine(",EMPLOYEE_NO"); strQry.AppendLine(",PREV_NORMAL_LEAVE_DAYS"); strQry.AppendLine(",NORMAL_LEAVE_DAYS"); strQry.AppendLine(",SICK_LEAVE_DAYS"); strQry.AppendLine(",LEAVE_EFFECTIVE_DATE)"); strQry.AppendLine(" SELECT "); strQry.AppendLine(" EIH.COMPANY_NO"); strQry.AppendLine(",EIH.EMPLOYEE_NO"); strQry.AppendLine(",0"); strQry.AppendLine(",0"); strQry.AppendLine(",0"); strQry.AppendLine(",EIH.PAY_PERIOD_DATE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY EIH"); strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON LTO "); strQry.AppendLine(" ON EIH.COMPANY_NO = LTO.COMPANY_NO "); strQry.AppendLine(" AND EIH.EMPLOYEE_NO = LTO.EMPLOYEE_NO "); strQry.AppendLine(" WHERE EIH.COMPANY_NO = " + parInt64CompanyNo); strQry.AppendLine(" AND EIH.RUN_TYPE = 'T' "); strQry.AppendLine(" AND EIH.EMPLOYEE_NO = " + DataSet.Tables["EmployeeTemp"].Rows[intRow]["EMPLOYEE_NO"].ToString()); strQry.AppendLine(" AND EIH.PAY_CATEGORY_TYPE = '" + DataSet.Tables["EmployeeTemp"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() + "'"); strQry.AppendLine(" AND LTO.COMPANY_NO IS NULL "); clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo); } //Delete any Employee That Have been on System for longer Than 180 Days strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON "); strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo); strQry.AppendLine(" AND NOT EMPLOYEE_NO IN (" + strEmployeeNoIn + ")"); clsDBConnectionObjects.Execute_SQLCommand(strQry.ToString(), parInt64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" E.COMPANY_NO"); strQry.AppendLine(",E.PAY_CATEGORY_TYPE"); strQry.AppendLine(",E.EMPLOYEE_NO"); strQry.AppendLine(",E.EMPLOYEE_CODE"); strQry.AppendLine(",E.EMPLOYEE_NAME"); strQry.AppendLine(",E.EMPLOYEE_SURNAME"); strQry.AppendLine(",LT.PREV_NORMAL_LEAVE_DAYS"); strQry.AppendLine(",LT.NORMAL_LEAVE_DAYS"); strQry.AppendLine(",LT.SICK_LEAVE_DAYS"); strQry.AppendLine(",LT.LEAVE_EFFECTIVE_DATE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E"); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_TAKE_ON LT "); strQry.AppendLine(" ON E.COMPANY_NO = LT.COMPANY_NO "); strQry.AppendLine(" AND E.EMPLOYEE_NO = LT.EMPLOYEE_NO "); strQry.AppendLine(" WHERE E.COMPANY_NO = " + parInt64CompanyNo); //2013-06-21 Exclude T=Time Attendance strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE IN ('W','S')"); strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" ORDER BY E.EMPLOYEE_CODE"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parInt64CompanyNo); DataSet.Tables.Add("PayrollType"); DataTable PayrollTypeDataTable = new DataTable("PayrollType"); DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String)); if (DataSet.Tables["Employee"].Rows.Count > 0) { DataView PayrollTypeDataView = new DataView(DataSet.Tables["Employee"], "PAY_CATEGORY_TYPE = 'W'", "", DataViewRowState.CurrentRows); if (PayrollTypeDataView.Count > 0) { DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow(); drDataRow["PAYROLL_TYPE_DESC"] = "Wages"; DataSet.Tables["PayrollType"].Rows.Add(drDataRow); } PayrollTypeDataView = null; PayrollTypeDataView = new DataView(DataSet.Tables["Employee"], "PAY_CATEGORY_TYPE = 'S'", "", DataViewRowState.CurrentRows); if (PayrollTypeDataView.Count > 0) { DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow(); drDataRow["PAYROLL_TYPE_DESC"] = "Salaries"; DataSet.Tables["PayrollType"].Rows.Add(drDataRow); } } DataSet.AcceptChanges(); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records() { string strQry = ""; DataSet DataSet = new DataSet(); //Get All Level Descriptions strQry = ""; strQry += " SELECT "; strQry += " HF.HELP_LEVEL1_NO"; strQry += ",HF.HELP_LEVEL2_NO"; strQry += ",HF.HELP_LEVEL3_NO"; strQry += ",HF.HELP_LEVEL4_NO"; strQry += ",HL1.HELP_LEVEL1_DESC"; strQry += ",HL2.HELP_LEVEL2_DESC"; strQry += ",HL3.HELP_LEVEL3_DESC"; strQry += ",HL4.HELP_LEVEL4_DESC"; strQry += " FROM InteractPayroll.dbo.HELP_FILE HF"; strQry += " INNER JOIN InteractPayroll.dbo.HELP_LEVEL1 HL1"; strQry += " ON HF.HELP_LEVEL1_NO = HL1.HELP_LEVEL1_NO"; strQry += " LEFT JOIN InteractPayroll.dbo.HELP_LEVEL2 HL2"; strQry += " ON HF.HELP_LEVEL2_NO = HL2.HELP_LEVEL2_NO"; strQry += " LEFT JOIN InteractPayroll.dbo.HELP_LEVEL3 HL3"; strQry += " ON HF.HELP_LEVEL3_NO = HL3.HELP_LEVEL3_NO"; strQry += " LEFT JOIN InteractPayroll.dbo.HELP_LEVEL4 HL4"; strQry += " ON HF.HELP_LEVEL4_NO = HL4.HELP_LEVEL4_NO"; strQry += " ORDER BY "; strQry += " HL1.HELP_LEVEL1_DESC"; strQry += ",HL2.HELP_LEVEL2_DESC"; strQry += ",HL3.HELP_LEVEL3_DESC"; strQry += ",HL4.HELP_LEVEL4_DESC"; clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "HelpLevelDesc", -1); strQry = ""; strQry += " SELECT "; strQry += " HELP_LEVEL1_NO"; strQry += ",HELP_LEVEL2_NO"; strQry += ",HELP_LEVEL3_NO"; strQry += ",HELP_LEVEL4_NO"; strQry += ",HELP_TEXT"; strQry += " FROM InteractPayroll.dbo.HELP_FILE_TEXT "; strQry += " WHERE HELP_LEVEL1_NO = -1"; clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "HelpLevelText", -1); //Empty Table - Used for DataView strQry = ""; strQry += " SELECT "; strQry += " HELP_LEVEL1_NO"; strQry += ",HELP_LEVEL2_NO"; strQry += ",HELP_LEVEL3_NO"; strQry += ",HELP_LEVEL4_NO"; strQry += ",COUNT(*) AS HELP_GRAPHIC_NUMBER_OF_CHUNKS"; strQry += " FROM InteractPayroll.dbo.HELP_FILE_GRAPHIC_CHUNK "; strQry += " WHERE HELP_LEVEL1_NO = -1"; strQry += " GROUP BY "; strQry += " HELP_LEVEL1_NO"; strQry += ",HELP_LEVEL2_NO"; strQry += ",HELP_LEVEL3_NO"; strQry += ",HELP_LEVEL4_NO"; clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "HelpLevelNumberChunks", -1); //Empty Table - Used for DataView strQry = ""; strQry += " SELECT "; strQry += " HFGC.HELP_LEVEL1_NO"; strQry += ",HFGC.HELP_LEVEL2_NO"; strQry += ",HFGC.HELP_LEVEL3_NO"; strQry += ",HFGC.HELP_LEVEL4_NO"; strQry += ",HFGD.HELP_FILE_TYPE"; strQry += ",HFGD.HELP_IMAGE_SIZE"; strQry += ",HFGC.HELP_CHUNK_NO"; strQry += ",HFGC.HELP_CHUNK_IMAGE"; strQry += " FROM InteractPayroll.dbo.HELP_FILE_GRAPHIC_CHUNK HFGC "; strQry += " INNER JOIN InteractPayroll.dbo.HELP_FILE_GRAPHIC_DETAIL HFGD "; strQry += " ON HFGC.HELP_LEVEL1_NO = HFGD.HELP_LEVEL1_NO"; strQry += " AND HFGC.HELP_LEVEL2_NO = HFGD.HELP_LEVEL2_NO"; strQry += " AND HFGC.HELP_LEVEL3_NO = HFGD.HELP_LEVEL3_NO"; strQry += " AND HFGC.HELP_LEVEL4_NO = HFGD.HELP_LEVEL4_NO"; strQry += " WHERE HFGC.HELP_LEVEL1_NO = -1"; clsDBConnectionObjects.Create_DataTable(strQry, DataSet, "HelpLevelGraphicChunk", -1); DataTable DataTable = new DataTable("Report"); DataTable.Columns.Add("LEVEL1_DESC", typeof(String)); DataTable.Columns.Add("LEVEL1_TEXT", typeof(String)); DataTable.Columns.Add("LEVEL1_IMAGE", typeof(byte[])); DataSet.Tables.Add(DataTable); DataRow drDataRow = DataSet.Tables["Report"].NewRow(); drDataRow["LEVEL1_DESC"] = "Getting Started"; drDataRow["LEVEL1_TEXT"] = "Choose either Content or Search Tab."; DataSet.Tables["Report"].Rows.Add(drDataRow); drDataRow = DataSet.Tables["Report"].NewRow(); drDataRow["LEVEL1_DESC"] = "Content"; drDataRow["LEVEL1_TEXT"] = "Click on a Node for desired Information."; DataSet.Tables["Report"].Rows.Add(drDataRow); drDataRow = DataSet.Tables["Report"].NewRow(); drDataRow["LEVEL1_DESC"] = "Search"; drDataRow["LEVEL1_TEXT"] = "Enter search criteria into Search TextBox and Click Search."; DataSet.Tables["Report"].Rows.Add(drDataRow); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet = null; return(bytCompress); }
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[] Get_Form_Records(Int64 parint64CompanyNo, string parstrCurrentUserAccess, Int64 parint64CurrentUserNo) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); if (parstrCurrentUserAccess == "S") { strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" COMPANY_NO"); strQry.AppendLine(",COMPANY_DESC"); strQry.AppendLine(",'A' AS ACCESS_IND"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.COMPANY"); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" ORDER BY"); strQry.AppendLine(" COMPANY_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parint64CompanyNo); } else { //Administrator strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" C.COMPANY_NO"); strQry.AppendLine(",C.COMPANY_DESC"); strQry.AppendLine(",'A' AS ACCESS_IND"); strQry.AppendLine(" FROM "); strQry.AppendLine(" InteractPayroll_#CompanyNo#.dbo.COMPANY C"); strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA "); strQry.AppendLine(" WHERE C.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND C.COMPANY_NO = UCA.COMPANY_NO "); strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" AND UCA.USER_NO = " + parint64CurrentUserNo.ToString()); strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' "); strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" ORDER BY"); strQry.AppendLine(" C.COMPANY_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parint64CompanyNo); } strQry.Clear(); strQry.AppendLine(" SELECT DISTINCT "); strQry.AppendLine(" LS.COMPANY_NO"); strQry.AppendLine(",LS.PAY_CATEGORY_TYPE"); strQry.AppendLine(",LS.LEAVE_SHIFT_NO"); strQry.AppendLine(",LS.LEAVE_SHIFT_DESC"); strQry.AppendLine(",LS.MIN_VALID_SHIFT_MINUTES"); strQry.AppendLine(",LS.MAX_SHIFTS_YEAR"); strQry.AppendLine(",LS.NORM_PAID_DAYS"); strQry.AppendLine(",LS.SICK_PAID_DAYS"); strQry.AppendLine(",LS.NORM_PAID_PER_PERIOD"); strQry.AppendLine(",LS.SICK_PAID_PER_PERIOD"); strQry.AppendLine(",LS.LEAVE_PAID_ACCUMULATOR_IND"); strQry.AppendLine(",LS.LEAVE_SHIFT_DEL_IND"); strQry.AppendLine(",LSC.LEAVE_SHIFT_NO AS PAYROLL_LINK"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT LS "); strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.LEAVE_SHIFT_CURRENT LSC"); strQry.AppendLine(" ON LS.COMPANY_NO = LSC.COMPANY_NO "); strQry.AppendLine(" AND LS.PAY_CATEGORY_TYPE = LSC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" AND LS.LEAVE_SHIFT_NO = LSC.LEAVE_SHIFT_NO "); strQry.AppendLine(" WHERE LS.COMPANY_NO = " + parint64CompanyNo); //Record has Not been Deleted strQry.AppendLine(" AND LS.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" LS.PAY_CATEGORY_TYPE"); strQry.AppendLine(",LS.LEAVE_SHIFT_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Leave", parint64CompanyNo); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parInt64CompanyNo, string parstrCurrentUserAccess, Int64 parint64CurrentUserNo) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); DataRow dtDataRow; DataTable DataTable = new DataTable("SubAccount"); DataTable.Columns.Add("DEDUCTION_SUB_ACCOUNT_COUNT", typeof(System.Int16)); DataSet.Tables.Add(DataTable); for (int intDay = 1; intDay < 6; intDay++) { dtDataRow = DataSet.Tables["SubAccount"].NewRow(); dtDataRow["DEDUCTION_SUB_ACCOUNT_COUNT"] = intDay; DataSet.Tables["SubAccount"].Rows.Add(dtDataRow); } if (parstrCurrentUserAccess == "S") { strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" 'A' AS ACCESS_IND"); strQry.AppendLine(" FROM InteractPayroll.dbo.COMPANY_LINK"); strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parInt64CompanyNo); } else { //Administrator strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" 'A' AS ACCESS_IND"); strQry.AppendLine(" FROM "); strQry.AppendLine(" InteractPayroll.dbo.COMPANY_LINK C"); strQry.AppendLine(",InteractPayroll.dbo.USER_COMPANY_ACCESS UCA "); strQry.AppendLine(" WHERE C.COMPANY_NO = " + parInt64CompanyNo); strQry.AppendLine(" AND C.COMPANY_NO = UCA.COMPANY_NO "); strQry.AppendLine(" AND UCA.USER_NO = " + parint64CurrentUserNo); strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A' "); strQry.AppendLine(" AND UCA.DATETIME_DELETE_RECORD IS NULL "); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Company", parInt64CompanyNo); } strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" E.COMPANY_NO"); strQry.AppendLine(",E.PAY_CATEGORY_TYPE"); strQry.AppendLine(",E.EARNING_NO"); strQry.AppendLine(",E.EARNING_DESC"); strQry.AppendLine(",E.IRP5_CODE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING E"); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.COMPANY C"); strQry.AppendLine(" ON E.COMPANY_NO = C.COMPANY_NO "); strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" WHERE E.COMPANY_NO = " + parInt64CompanyNo); //Income/Normal Time/Bonus/Paid Holiday strQry.AppendLine(" AND (E.EARNING_NO IN (1,2,7)"); //ELR - 2018-11-10 //strQry.AppendLine(" OR (E.EARNING_NO IN (9,200,201)"); strQry.AppendLine(" OR (E.EARNING_NO IN (9)"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W')"); //Public Holiday - Company Paid strQry.AppendLine(" OR (E.EARNING_NO = 8"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W')"); //Overtime Wages strQry.AppendLine(" OR (E.EARNING_NO = 3"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W'"); strQry.AppendLine(" AND C.OVERTIME1_RATE <> 0)"); strQry.AppendLine(" OR (E.EARNING_NO = 4"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W'"); strQry.AppendLine(" AND C.OVERTIME2_RATE <> 0)"); strQry.AppendLine(" OR (E.EARNING_NO = 5"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W'"); strQry.AppendLine(" AND C.OVERTIME3_RATE <> 0)"); //Overtime Salaries strQry.AppendLine(" OR (E.EARNING_NO = 3"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'S'"); strQry.AppendLine(" AND C.SALARY_OVERTIME1_RATE <> 0)"); strQry.AppendLine(" OR (E.EARNING_NO = 4"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'S'"); strQry.AppendLine(" AND C.SALARY_OVERTIME2_RATE <> 0)"); strQry.AppendLine(" OR (E.EARNING_NO = 5"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'S'"); strQry.AppendLine(" AND C.SALARY_OVERTIME3_RATE <> 0))"); strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL "); //ELR - 2018-11-10 (Leave Added to Select List) strQry.AppendLine(" UNION "); strQry.AppendLine(" SELECT "); strQry.AppendLine(" E.COMPANY_NO"); strQry.AppendLine(",E.PAY_CATEGORY_TYPE"); strQry.AppendLine(",E.EARNING_NO"); strQry.AppendLine(",E.EARNING_DESC"); strQry.AppendLine(",E.IRP5_CODE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EARNING E"); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.COMPANY C"); strQry.AppendLine(" ON E.COMPANY_NO = C.COMPANY_NO "); strQry.AppendLine(" AND C.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" WHERE E.COMPANY_NO = " + parInt64CompanyNo); //Leave (Only for Wages) strQry.AppendLine(" AND E.EARNING_NO >= 200"); strQry.AppendLine(" AND E.LEAVE_PERCENTAGE > 0"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W'"); strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL "); //Own Created Earnings strQry.AppendLine(" UNION "); strQry.AppendLine(" SELECT "); strQry.AppendLine(" EE.COMPANY_NO"); strQry.AppendLine(",E.PAY_CATEGORY_TYPE"); strQry.AppendLine(",EE.EARNING_NO"); strQry.AppendLine(",E.EARNING_DESC"); strQry.AppendLine(",E.IRP5_CODE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_EARNING EE "); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EARNING E"); strQry.AppendLine(" ON EE.COMPANY_NO = E.COMPANY_NO "); strQry.AppendLine(" AND EE.PAY_CATEGORY_TYPE = E.PAY_CATEGORY_TYPE "); strQry.AppendLine(" AND EE.EARNING_NO = E.EARNING_NO "); strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" WHERE EE.COMPANY_NO = " + parInt64CompanyNo); strQry.AppendLine(" AND EE.EMPLOYEE_NO = 0 "); strQry.AppendLine(" AND EE.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" E.PAY_CATEGORY_TYPE"); strQry.AppendLine(",E.EARNING_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EarningList", parInt64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" COMPANY_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE"); strQry.AppendLine(",DEDUCTION_NO"); strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO"); strQry.AppendLine(",EARNING_NO"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_EARNING_PERCENTAGE "); strQry.AppendLine(" WHERE COMPANY_NO = " + parInt64CompanyNo); strQry.AppendLine(" AND EMPLOYEE_NO = 0 "); strQry.AppendLine(" AND DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" PAY_CATEGORY_TYPE"); strQry.AppendLine(",DEDUCTION_NO"); strQry.AppendLine(",DEDUCTION_SUB_ACCOUNT_NO"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EarningPercentage", parInt64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT DISTINCT"); strQry.AppendLine(" D.COMPANY_NO"); strQry.AppendLine(",D.PAY_CATEGORY_TYPE"); strQry.AppendLine(",ED.EMPLOYEE_NO"); strQry.AppendLine(",D.DEDUCTION_NO"); strQry.AppendLine(",D.DEDUCTION_SUB_ACCOUNT_NO"); strQry.AppendLine(",D.DEDUCTION_DESC"); strQry.AppendLine(",D.IRP5_CODE"); strQry.AppendLine(",D.DEDUCTION_SUB_ACCOUNT_COUNT"); strQry.AppendLine(",D.DEDUCTION_REPORT_HEADER1"); strQry.AppendLine(",D.DEDUCTION_REPORT_HEADER2"); strQry.AppendLine(",D.DEDUCTION_LOAN_TYPE_IND"); strQry.AppendLine(",D.DEDUCTION_DEL_IND"); strQry.AppendLine(",ED.DEDUCTION_TYPE_IND"); strQry.AppendLine(",ED.DEDUCTION_VALUE"); strQry.AppendLine(",ED.DEDUCTION_MIN_VALUE"); strQry.AppendLine(",ED.DEDUCTION_MAX_VALUE"); strQry.AppendLine(",ED.DEDUCTION_PERIOD_IND"); strQry.AppendLine(",ED.DEDUCTION_DAY_VALUE"); strQry.AppendLine(",ED.TIE_BREAKER"); strQry.AppendLine(",EDC.DEDUCTION_NO AS PAYROLL_LINK"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.DEDUCTION D"); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION ED"); strQry.AppendLine(" ON D.COMPANY_NO = ED.COMPANY_NO "); strQry.AppendLine(" AND D.PAY_CATEGORY_TYPE = ED.PAY_CATEGORY_TYPE "); strQry.AppendLine(" AND D.DEDUCTION_NO = ED.DEDUCTION_NO "); strQry.AppendLine(" AND D.DEDUCTION_SUB_ACCOUNT_NO = ED.DEDUCTION_SUB_ACCOUNT_NO "); strQry.AppendLine(" AND ED.EMPLOYEE_NO = 0 "); strQry.AppendLine(" AND ED.DATETIME_DELETE_RECORD IS NULL "); strQry.AppendLine(" LEFT JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_DEDUCTION_CURRENT EDC"); strQry.AppendLine(" ON D.COMPANY_NO = EDC.COMPANY_NO "); strQry.AppendLine(" AND D.PAY_CATEGORY_TYPE = EDC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" AND D.DEDUCTION_NO = EDC.DEDUCTION_NO "); strQry.AppendLine(" AND EDC.RUN_TYPE = 'P'"); strQry.AppendLine(" WHERE D.COMPANY_NO = " + parInt64CompanyNo); strQry.AppendLine(" AND D.DATETIME_DELETE_RECORD IS NULL "); //Only Get First One (All Others are a Duplicate of This One) strQry.AppendLine(" AND D.DEDUCTION_SUB_ACCOUNT_NO = 1"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" D.DEDUCTION_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Deduction", parInt64CompanyNo); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parint64CompanyNo, string parstrMenuId, Int64 parint64CurrentUserNo, string parstrCurrentUserAccess) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT DISTINCT"); strQry.AppendLine(" PC.COMPANY_NO "); strQry.AppendLine(",PC.PAY_CATEGORY_DESC"); strQry.AppendLine(",PC.PAY_CATEGORY_NO"); strQry.AppendLine(",PCPH.PAY_CATEGORY_TYPE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_HISTORY PCPH"); //2013-08-30 if (parstrCurrentUserAccess == "U") { strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT"); strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString()); strQry.AppendLine(" AND PCPH.COMPANY_NO = UEPCT.COMPANY_NO"); strQry.AppendLine(" AND PCPH.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO"); strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE"); } strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY PC"); strQry.AppendLine(" ON PCPH.COMPANY_NO = PC.COMPANY_NO"); strQry.AppendLine(" AND PCPH.PAY_CATEGORY_NO = PC.PAY_CATEGORY_NO"); strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = PC.PAY_CATEGORY_TYPE"); strQry.AppendLine(" WHERE PCPH.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = 'W'"); strQry.AppendLine(" AND PCPH.RUN_TYPE = 'P'"); strQry.AppendLine(" AND PC.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" PC.COMPANY_NO "); strQry.AppendLine(",PC.PAY_CATEGORY_DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "PayCategory", parint64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" PCPH.COMPANY_NO "); strQry.AppendLine(",PCPH.PAY_CATEGORY_TYPE"); strQry.AppendLine(",PCPH.PAY_PERIOD_DATE"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_PERIOD_HISTORY PCPH"); strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_INFO_HISTORY EIH"); strQry.AppendLine(" ON PCPH.COMPANY_NO = EIH.COMPANY_NO"); strQry.AppendLine(" AND PCPH.PAY_PERIOD_DATE = EIH.PAY_PERIOD_DATE"); strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = EIH.PAY_CATEGORY_TYPE"); strQry.AppendLine(" AND PCPH.RUN_TYPE = EIH.RUN_TYPE"); strQry.AppendLine(" AND EIH.LEAVE_SHIFT_NO > 0"); //2013-08-30 if (parstrCurrentUserAccess == "U") { strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT"); strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString()); strQry.AppendLine(" AND PCPH.COMPANY_NO = UEPCT.COMPANY_NO"); strQry.AppendLine(" AND EIH.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO"); strQry.AppendLine(" AND PCPH.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO"); strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE"); } strQry.AppendLine(" WHERE PCPH.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND PCPH.PAY_CATEGORY_TYPE = 'W'"); strQry.AppendLine(" AND PCPH.RUN_TYPE = 'P'"); strQry.AppendLine(" GROUP BY "); strQry.AppendLine(" PCPH.COMPANY_NO "); strQry.AppendLine(",PCPH.PAY_CATEGORY_TYPE"); strQry.AppendLine(",PCPH.PAY_PERIOD_DATE"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" PCPH.COMPANY_NO "); strQry.AppendLine(",PCPH.PAY_CATEGORY_TYPE"); strQry.AppendLine(",PCPH.PAY_PERIOD_DATE DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Date", parint64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT DISTINCT"); strQry.AppendLine(" E.COMPANY_NO"); strQry.AppendLine(",E.EMPLOYEE_NO"); strQry.AppendLine(",E.EMPLOYEE_CODE"); strQry.AppendLine(",E.EMPLOYEE_SURNAME"); strQry.AppendLine(",E.EMPLOYEE_NAME"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE E"); //2013-08-30 if (parstrCurrentUserAccess == "U") { strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT"); strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString()); strQry.AppendLine(" AND E.COMPANY_NO = UEPCT.COMPANY_NO"); strQry.AppendLine(" AND E.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO"); //2017-01-28 (Allow Employee to Change PAY_CATEGORY_TYPE) //strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE"); } strQry.AppendLine(" WHERE E.COMPANY_NO = " + parint64CompanyNo); //2017-01-28 (Allow Employee to Change PAY_CATEGORY_TYPE) //strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = 'W'"); strQry.AppendLine(" AND E.DATETIME_DELETE_RECORD IS NULL"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" E.EMPLOYEE_NO"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "Employee", parint64CompanyNo); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" EPCH.COMPANY_NO"); strQry.AppendLine(",EPCH.PAY_PERIOD_DATE"); strQry.AppendLine(",EPCH.PAY_CATEGORY_TYPE"); strQry.AppendLine(",EPCH.EMPLOYEE_NO"); strQry.AppendLine(",EPCH.PAY_CATEGORY_NO"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.EMPLOYEE_PAY_CATEGORY_HISTORY EPCH"); //2013-08-30 if (parstrCurrentUserAccess == "U") { strQry.AppendLine(" INNER JOIN InteractPayroll_#CompanyNo#.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP UEPCT"); strQry.AppendLine(" ON UEPCT.USER_NO = " + parint64CurrentUserNo.ToString()); strQry.AppendLine(" AND EPCH.COMPANY_NO = UEPCT.COMPANY_NO"); strQry.AppendLine(" AND EPCH.EMPLOYEE_NO = UEPCT.EMPLOYEE_NO"); strQry.AppendLine(" AND EPCH.PAY_CATEGORY_NO = UEPCT.PAY_CATEGORY_NO"); strQry.AppendLine(" AND EPCH.PAY_CATEGORY_TYPE = UEPCT.PAY_CATEGORY_TYPE"); } strQry.AppendLine(" WHERE EPCH.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND EPCH.PAY_CATEGORY_TYPE = 'W'"); strQry.AppendLine(" GROUP BY"); strQry.AppendLine(" EPCH.COMPANY_NO"); strQry.AppendLine(",EPCH.PAY_PERIOD_DATE"); strQry.AppendLine(",EPCH.PAY_CATEGORY_TYPE"); strQry.AppendLine(",EPCH.EMPLOYEE_NO"); strQry.AppendLine(",EPCH.PAY_CATEGORY_NO"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "EmployeePayCategory", parint64CompanyNo); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(Int64 parint64CompanyNo, string parstrFromProgram) { DataSet DataSet = new DataSet(); StringBuilder strQry = new StringBuilder(); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" COMPANY_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE "); strQry.AppendLine(",PAY_CATEGORY_SHIFT_SCHEDULE_NO "); strQry.AppendLine(",FROM_DATETIME "); strQry.AppendLine(",TO_DATETIME "); strQry.AppendLine(",ISNULL(LOCKED_IND,0) AS LOCKED_IND"); strQry.AppendLine(" FROM InteractPayroll_#CompanyNo#.dbo.PAY_CATEGORY_SHIFT_SCHEDULE"); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo); if (parstrFromProgram == "X") { strQry.AppendLine(" AND PAY_CATEGORY_TYPE = 'T'"); } else { strQry.AppendLine(" AND PAY_CATEGORY_TYPE IN ('W','S')"); } strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" COMPANY_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE "); strQry.AppendLine(",FROM_DATETIME DESC"); strQry.AppendLine(",TO_DATETIME DESC"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "ShiftSchedule", parint64CompanyNo); DataSet.Tables.Add("PayrollType"); DataTable PayrollTypeDataTable = new DataTable("PayrollType"); DataSet.Tables["PayrollType"].Columns.Add("PAYROLL_TYPE_DESC", typeof(String)); if (parstrFromProgram == "X") { DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow(); drDataRow["PAYROLL_TYPE_DESC"] = "Time Attendance"; DataSet.Tables["PayrollType"].Rows.Add(drDataRow); } else { DataRow drDataRow = DataSet.Tables["PayrollType"].NewRow(); drDataRow["PAYROLL_TYPE_DESC"] = "Wages"; DataSet.Tables["PayrollType"].Rows.Add(drDataRow); drDataRow = DataSet.Tables["PayrollType"].NewRow(); drDataRow["PAYROLL_TYPE_DESC"] = "Salaries"; DataSet.Tables["PayrollType"].Rows.Add(drDataRow); } DataSet.AcceptChanges(); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
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(" 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 "); } strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" C.COMPANY_DESC"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Company"); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" D.DEVICE_NO"); strQry.AppendLine(",D.DEVICE_DESC"); strQry.AppendLine(",D.DEVICE_USAGE"); strQry.AppendLine(",D.TIME_ATTEND_CLOCK_FIRST_LAST_IND"); strQry.AppendLine(",D.CLOCK_IN_OUT_PARM"); strQry.AppendLine(",D.CLOCK_IN_RANGE_FROM"); strQry.AppendLine(",D.CLOCK_IN_RANGE_TO"); strQry.AppendLine(",D.LOCK_OUT_MINUTES"); strQry.AppendLine(",D.COMPANY_NO"); strQry.AppendLine(",ISNULL(D.LAN_WAN_IND,'L') AS LAN_WAN_IND"); strQry.AppendLine(",FAR_REQUESTED"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE D"); 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 = D.COMPANY_NO "); } strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" D.DEVICE_DESC"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Clock"); for (int intRow = 0; intRow < DataSet.Tables["Clock"].Rows.Count; intRow++) { if (DataSet.Tables["Clock"].Rows[intRow]["FAR_REQUESTED"] == System.DBNull.Value) { // 1/10 000 DataSet.Tables["Clock"].Rows[intRow]["FAR_REQUESTED"] = 214748; strQry.Clear(); strQry.AppendLine(" UPDATE DEVICE"); strQry.AppendLine(" SET FAR_REQUESTED = 214748"); strQry.AppendLine(" WHERE DEVICE_NO = " + DataSet.Tables["Clock"].Rows[intRow]["DEVICE_NO"].ToString()); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } } byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public byte[] Get_Form_Records(string parstrCurrentUserAccess, Int64 parint64CurrentUserNo) { StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" U.USER_NO"); strQry.AppendLine(",U.USER_ID"); strQry.AppendLine(",U.FIRSTNAME"); strQry.AppendLine(",U.SURNAME"); strQry.AppendLine(" FROM InteractPayroll.dbo.USER_ID U"); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_DETAILS FUD "); strQry.AppendLine(" ON U.USER_NO = FUD.USER_NO "); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_DETAILS_FOR_USERS FUDFU "); strQry.AppendLine(" ON FUD.USER_NO = FUDFU.USER_NO"); strQry.AppendLine(" AND FUD.FILE_NAME = FUDFU.FILE_NAME"); strQry.AppendLine(" AND FUD.UPLOAD_DATETIME = FUDFU.UPLOAD_DATETIME"); strQry.AppendLine(" WHERE U.DATETIME_DELETE_RECORD IS NULL"); if (parstrCurrentUserAccess != "S") { strQry.AppendLine(" AND FUDFU.FOR_USER_NO = " + parint64CurrentUserNo); } strQry.AppendLine(" UNION "); strQry.AppendLine(" SELECT "); strQry.AppendLine(" 0 AS USER_NO"); strQry.AppendLine(",'Admin' AS USER_ID"); strQry.AppendLine(",'Admin' AS FIRSTNAME"); strQry.AppendLine(",'Admin' AS SURNAME"); //Get 1 Row Only strQry.AppendLine(" FROM InteractPayroll.dbo.COUNTRY C"); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_DETAILS FUD "); strQry.AppendLine(" ON FUD.USER_NO = 0"); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_DETAILS_FOR_USERS FUDFU "); strQry.AppendLine(" ON FUD.USER_NO = FUDFU.USER_NO"); strQry.AppendLine(" AND FUD.FILE_NAME = FUDFU.FILE_NAME"); strQry.AppendLine(" AND FUD.UPLOAD_DATETIME = FUDFU.UPLOAD_DATETIME"); strQry.AppendLine(" WHERE C.COUNTRY_CODE = 'ZAF' "); if (parstrCurrentUserAccess != "S") { strQry.AppendLine(" AND FUDFU.FOR_USER_NO = " + parint64CurrentUserNo); } strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" 4"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "User", -1); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" FUD.USER_NO"); strQry.AppendLine(",FUD.FILE_NAME"); strQry.AppendLine(",FUD.UPLOAD_DATETIME"); strQry.AppendLine(",FUD.FILE_LAST_UPDATED_DATE"); strQry.AppendLine(",FUD.FILE_SIZE"); strQry.AppendLine(",FUD.FILE_SIZE_COMPRESSED"); strQry.AppendLine(",FUD.FILE_VERSION_NO"); strQry.AppendLine(",FUD.FILE_CRC_VALUE"); strQry.AppendLine(",ISNULL(U.USER_ID,'Admin') AS USER_ID"); strQry.AppendLine(",ISNULL(U.FIRSTNAME,'Admin') AS FIRSTNAME"); strQry.AppendLine(",ISNULL(U.SURNAME,'Admin') AS SURNAME"); strQry.AppendLine(",MAX(FUC.FILE_CHUNK_NO) AS MAX_FILE_CHUNK_NO"); strQry.AppendLine(" FROM InteractPayroll.dbo.FILE_UPLOAD_DETAILS FUD "); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_DETAILS_FOR_USERS FUDFU "); strQry.AppendLine(" ON FUD.USER_NO = FUDFU.USER_NO"); strQry.AppendLine(" AND FUD.FILE_NAME = FUDFU.FILE_NAME"); strQry.AppendLine(" AND FUD.UPLOAD_DATETIME = FUDFU.UPLOAD_DATETIME"); strQry.AppendLine(" LEFT JOIN InteractPayroll.dbo.USER_ID U"); strQry.AppendLine(" ON FUDFU.FOR_USER_NO = U.USER_NO "); strQry.AppendLine(" INNER JOIN InteractPayroll.dbo.FILE_UPLOAD_CHUNKS FUC "); strQry.AppendLine(" ON FUD.USER_NO = FUC.USER_NO"); strQry.AppendLine(" AND FUD.FILE_NAME = FUC.FILE_NAME"); strQry.AppendLine(" AND FUD.UPLOAD_DATETIME = FUC.UPLOAD_DATETIME"); if (parstrCurrentUserAccess != "S") { strQry.AppendLine(" WHERE FUDFU.FOR_USER_NO = " + parint64CurrentUserNo); } strQry.AppendLine(" GROUP BY "); strQry.AppendLine(" FUD.USER_NO"); strQry.AppendLine(",FUD.FILE_NAME"); strQry.AppendLine(",FUD.UPLOAD_DATETIME"); strQry.AppendLine(",FUD.FILE_LAST_UPDATED_DATE"); strQry.AppendLine(",FUD.FILE_SIZE"); strQry.AppendLine(",FUD.FILE_SIZE_COMPRESSED"); strQry.AppendLine(",FUD.FILE_VERSION_NO"); strQry.AppendLine(",FUD.FILE_CRC_VALUE"); strQry.AppendLine(",U.USER_ID"); strQry.AppendLine(",U.FIRSTNAME"); strQry.AppendLine(",U.SURNAME"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" FUD.USER_NO"); strQry.AppendLine(",FUD.UPLOAD_DATETIME DESC"); strQry.AppendLine(",FUD.FILE_NAME"); clsDBConnectionObjects.Create_DataTable(strQry.ToString(), DataSet, "UserFile", -1); byte[] bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }