public void Update_Record(string parstrRemoteBackupSiteName) { //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes StringBuilder strQry = new StringBuilder(); DataSet DataSet = new DataSet(); if (parstrRemoteBackupSiteName == "") { strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME"); } else { strQry.AppendLine(" SELECT "); strQry.AppendLine(" SITE_NAME"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "RemoteBackupSiteName"); if (DataSet.Tables["RemoteBackupSiteName"].Rows.Count > 0) { strQry.Clear(); strQry.AppendLine(" UPDATE InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME"); strQry.AppendLine(" SET "); strQry.AppendLine(" SITE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parstrRemoteBackupSiteName)); } else { strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.REMOTE_BACKUP_SITE_NAME"); strQry.AppendLine(" (SITE_NAME)"); strQry.AppendLine(" VALUES "); strQry.AppendLine("(" + clsDBConnectionObjects.Text2DynamicSQL(parstrRemoteBackupSiteName) + ")"); } } clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); DataSet.Dispose(); DataSet = null; }
public int Insert_Group(Int64 parint64CompanyNo, string parstrGroupDesc, byte[] byteCompressedDataSet) { //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteCompressedDataSet); int intGroupNo = 0; StringBuilder strQry = new StringBuilder(); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" MAX(GROUP_NO) AS MAX_NO"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.GROUPS"); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp"); if (DataSet.Tables["Temp"].Rows[0].IsNull("MAX_NO") == true) { intGroupNo = 1; } else { intGroupNo = Convert.ToInt32(DataSet.Tables["Temp"].Rows[0]["MAX_NO"]) + 1; } strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.GROUPS"); strQry.AppendLine("(COMPANY_NO"); strQry.AppendLine(",GROUP_NO"); strQry.AppendLine(",GROUP_DESC)"); strQry.AppendLine(" VALUES"); strQry.AppendLine("(" + parint64CompanyNo); strQry.AppendLine("," + intGroupNo); strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(parstrGroupDesc) + ")"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); Insert_Group_Employee_Link(parint64CompanyNo, intGroupNo, DataSet); return(intGroupNo); }
public int Backup_DataBase() { int intReturnCode = 9; try { StringBuilder strQry = new StringBuilder(); string strFileDirectory = AppDomain.CurrentDomain.BaseDirectory + "Backup"; if (Directory.Exists(strFileDirectory)) { } else { Directory.CreateDirectory(strFileDirectory); } string strDataBaseName = "InteractPayrollClient"; #if (DEBUG) strDataBaseName = "InteractPayrollClient_Debug"; #endif string strBackupFileName = strDataBaseName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".bak"; strQry.Clear(); strQry.AppendLine("BACKUP DATABASE " + strDataBaseName + " TO DISK = '" + strFileDirectory + "\\" + strBackupFileName + "' WITH CHECKSUM "); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString(), 60); intReturnCode = 0; } catch (Exception ex) { WriteExceptionLog("Backup_DataBase", ex); } return(intReturnCode); }
public void Update_PayCategory_Last_Upload_DateTime(Int64 parint64CompanyNo, string strUploadDateTime, byte[] parbyteDataSet) { //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes StringBuilder strQry = new StringBuilder(); DataSet ClientDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbyteDataSet); for (int intPayCategoryRow = 0; intPayCategoryRow < ClientDataSet.Tables["PayCategoryUpload"].Rows.Count; intPayCategoryRow++) { strQry.Clear(); strQry.AppendLine(" UPDATE InteractPayrollClient.dbo.PAY_CATEGORY"); strQry.AppendLine(" SET LAST_UPLOAD_DATETIME = '" + strUploadDateTime + "'"); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND PAY_CATEGORY_NO = " + ClientDataSet.Tables["PayCategoryUpload"].Rows[intPayCategoryRow]["PAY_CATEGORY_NO"].ToString()); strQry.AppendLine(" AND PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(ClientDataSet.Tables["PayCategoryUpload"].Rows[intPayCategoryRow]["PAY_CATEGORY_TYPE"].ToString())); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } }
public byte[] Insert_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(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[] Logon_User(string parstrUserInformation) { //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes bool blnDebug = false; StringBuilder strQry = new StringBuilder(); string[] strPieces = parstrUserInformation.Split('|'); string strUserId = strPieces[0]; string strPassword = strPieces[1]; byte[] bytCompress; StreamWriter swStreamWriter; DataSet DataSet = new DataSet(); DataTable DataTable = new DataTable("ReturnValues"); DataTable.Columns.Add("USER_NO", typeof(Int64)); DataTable.Columns.Add("RESET", typeof(String)); DataTable.Columns.Add("ACCESS_IND", typeof(String)); DataTable.Columns.Add("REBOOT_IND", typeof(String)); DataTable.Columns.Add("MACHINE_NAME", typeof(String)); DataTable.Columns.Add("MACHINE_IP", typeof(String)); DataTable.Columns.Add("NO_USERS_IND", typeof(String)); DataTable.Columns.Add("DOWNLOAD_IND", typeof(String)); DataTable.Columns.Add("DB_CREATE_FOR_ENGINE", typeof(String)); DataSet.Tables.Add(DataTable); DataRow myDataRow = DataSet.Tables["ReturnValues"].NewRow(); myDataRow["USER_NO"] = -1; myDataRow["RESET"] = "N"; myDataRow["ACCESS_IND"] = ""; DataSet.Tables["ReturnValues"].Rows.Add(myDataRow); FileInfo fiFile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "Debug.txt"); if (fiFile.Exists == true) { blnDebug = true; } fiFile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "busTimeAttendanceLogonLogs.txt"); if (fiFile.Exists == true) { File.Delete(AppDomain.CurrentDomain.BaseDirectory + "busTimeAttendanceLogonLogs.txt"); } if (blnDebug == true) { swStreamWriter = fiFile.AppendText(); swStreamWriter.WriteLine("Call clsDBConnectionObjects.GetSQLConnectionString"); swStreamWriter.Close(); } DataSet.Tables["ReturnValues"].Rows[0]["DB_CREATE_FOR_ENGINE"] = clsDBConnectionObjects.GetSQLConnectionString(); if (blnDebug == true) { swStreamWriter = fiFile.AppendText(); swStreamWriter.WriteLine("After clsDBConnectionObjects.GetSQLConnectionString = " + DataSet.Tables["ReturnValues"].Rows[0]["DB_CREATE_FOR_ENGINE"].ToString()); swStreamWriter.Close(); } if (clsDBConnectionObjects.GetSQLConnectionString() != "") { clsFixInteractPayrollClientDatabase clsFixInteractPayrollClientDatabase = new clsFixInteractPayrollClientDatabase(); clsFixInteractPayrollClientDatabase.Fix_Client_Database(); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" UI.USER_NO"); strQry.AppendLine(",UI.SYSTEM_ADMINISTRATOR_IND"); strQry.AppendLine(",UI.RESET"); //A = Administrator, U = User strQry.AppendLine(",MIN(UC.COMPANY_ACCESS_IND) AS MIN_COMPANY_ACCESS_IND"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID UI"); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UC"); strQry.AppendLine(" ON UI.USER_NO = UC.USER_NO"); strQry.AppendLine(" WHERE UI.USER_ID = " + clsDBConnectionObjects.Text2DynamicSQL(strUserId)); strQry.AppendLine(" AND UI.PASSWORD = "******" GROUP BY "); strQry.AppendLine(" UI.USER_NO"); strQry.AppendLine(",UI.SYSTEM_ADMINISTRATOR_IND"); strQry.AppendLine(",UI.RESET"); try { clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp"); } catch (Exception ex) { swStreamWriter = fiFile.AppendText(); swStreamWriter.WriteLine("Exception = " + ex.Message); swStreamWriter.Close(); } if (blnDebug == true) { swStreamWriter = fiFile.AppendText(); swStreamWriter.WriteLine("After Temp = Count = " + DataSet.Tables["Temp"].Rows.Count.ToString()); swStreamWriter.Close(); } if (DataSet.Tables["Temp"].Rows.Count == 0) { DataSet.Tables.Remove(DataSet.Tables["Temp"]); strQry.Clear(); strQry.AppendLine(" SELECT"); strQry.AppendLine(" USER_NO"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_ID"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "UserTemp"); if (DataSet.Tables["UserTemp"].Rows.Count > 0) { DataSet.Tables.Remove(DataSet.Tables["UserTemp"]); if ((strUserId == "INTERACT" && strPassword == "TCARETNI") || (strUserId == "HELENALR" && strPassword == "MONSTER")) { DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = 0; DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = "S"; } else { DataSet.AcceptChanges(); //Not Found bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); } } else { DataSet.Tables.Remove(DataSet.Tables["UserTemp"]); if ((strUserId == "INTERACT" && strPassword == "TCARETNI") || (strUserId == "HELENALR" && strPassword == "MONSTER")) { DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = 0; DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = "S"; } else { DataSet.Tables["ReturnValues"].Rows[0]["NO_USERS_IND"] = "Y"; DataSet.AcceptChanges(); bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); } } } else { if (DataSet.Tables["Temp"].Rows[0]["SYSTEM_ADMINISTRATOR_IND"].ToString() == "S") { DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = DataSet.Tables["Temp"].Rows[0]["SYSTEM_ADMINISTRATOR_IND"].ToString(); } else { DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"] = DataSet.Tables["Temp"].Rows[0]["MIN_COMPANY_ACCESS_IND"].ToString(); } if (DataSet.Tables["ReturnValues"].Rows[0]["ACCESS_IND"].ToString() != "S") { //Create Temp Table for Joins strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP"); strQry.AppendLine(" WHERE USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString()); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.USER_EMPLOYEE_PAY_CATEGORY_TEMP "); strQry.AppendLine("(USER_NO"); strQry.AppendLine(",COMPANY_NO"); strQry.AppendLine(",EMPLOYEE_NO"); strQry.AppendLine(",PAY_CATEGORY_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE)"); strQry.AppendLine(" SELECT "); strQry.AppendLine(" " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString()); strQry.AppendLine(",USER_TABLE.COMPANY_NO"); strQry.AppendLine(",USER_TABLE.EMPLOYEE_NO"); strQry.AppendLine(",USER_TABLE.PAY_CATEGORY_NO"); strQry.AppendLine(",USER_TABLE.PAY_CATEGORY_TYPE"); strQry.AppendLine(" FROM "); strQry.AppendLine("(SELECT "); strQry.AppendLine(" E.COMPANY_NO"); strQry.AppendLine(",E.EMPLOYEE_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E"); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC"); strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO"); strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO "); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_PAY_CATEGORY_DEPARTMENT UPCD"); strQry.AppendLine(" ON E.COMPANY_NO = UPCD.COMPANY_NO"); strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UPCD.PAY_CATEGORY_NO "); strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UPCD.PAY_CATEGORY_TYPE "); strQry.AppendLine(" AND E.DEPARTMENT_NO = UPCD.DEPARTMENT_NO "); strQry.AppendLine(" AND UPCD.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString()); strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL"); strQry.AppendLine(" UNION "); strQry.AppendLine(" SELECT "); strQry.AppendLine(" E.COMPANY_NO"); strQry.AppendLine(",E.EMPLOYEE_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E"); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC"); strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO"); strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO "); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_EMPLOYEE UE"); strQry.AppendLine(" ON E.COMPANY_NO = UE.COMPANY_NO"); strQry.AppendLine(" AND EPC.EMPLOYEE_NO = UE.EMPLOYEE_NO "); strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UE.PAY_CATEGORY_TYPE "); strQry.AppendLine(" AND UE.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString()); strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL"); strQry.AppendLine(" UNION "); strQry.AppendLine(" SELECT "); strQry.AppendLine(" E.COMPANY_NO"); strQry.AppendLine(",E.EMPLOYEE_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E"); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC"); strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO"); strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO "); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_PAY_CATEGORY UPC"); strQry.AppendLine(" ON E.COMPANY_NO = UPC.COMPANY_NO"); strQry.AppendLine(" AND EPC.PAY_CATEGORY_NO = UPC.PAY_CATEGORY_NO "); strQry.AppendLine(" AND EPC.PAY_CATEGORY_TYPE = UPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" AND UPC.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString()); strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL "); strQry.AppendLine(" UNION "); strQry.AppendLine(" SELECT "); strQry.AppendLine(" E.COMPANY_NO"); strQry.AppendLine(",E.EMPLOYEE_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_NO"); strQry.AppendLine(",EPC.PAY_CATEGORY_TYPE"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E"); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC"); strQry.AppendLine(" ON E.COMPANY_NO = EPC.COMPANY_NO"); strQry.AppendLine(" AND E.EMPLOYEE_NO = EPC.EMPLOYEE_NO "); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.USER_COMPANY_ACCESS UCA"); strQry.AppendLine(" ON UCA.USER_NO = " + DataSet.Tables["Temp"].Rows[0]["USER_NO"].ToString()); strQry.AppendLine(" AND E.COMPANY_NO = UCA.COMPANY_NO"); //2013-07-10 strQry.AppendLine(" AND UCA.COMPANY_ACCESS_IND = 'A'"); strQry.AppendLine(" AND E.NOT_ACTIVE_IND IS NULL) AS USER_TABLE"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } DataSet.Tables["ReturnValues"].Rows[0]["USER_NO"] = Convert.ToInt64(DataSet.Tables["Temp"].Rows[0]["USER_NO"]); DataSet.Tables["ReturnValues"].Rows[0]["RESET"] = DataSet.Tables["Temp"].Rows[0]["RESET"].ToString(); //Set Time Logged On strQry.Clear(); strQry.AppendLine(" UPDATE InteractPayrollClient.dbo.USER_ID"); strQry.AppendLine(" SET LAST_TIME_ON = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "'"); strQry.AppendLine(" WHERE USER_NO = " + Convert.ToInt64(DataSet.Tables["Temp"].Rows[0]["USER_NO"])); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } //2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder) strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS"); strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); //2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder) strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS"); strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); //2013-11-01 - Cleanup Records (Used Temporary Area until File is Written to Folder) strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS_TEMP"); strQry.AppendLine(" WHERE FILE_LAYER_IND = 'S'"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" FCDD.FILE_LAYER_IND"); strQry.AppendLine(",FCDD.FILE_NAME"); strQry.AppendLine(",FCDD.FILE_SIZE"); strQry.AppendLine(",FCDD.FILE_SIZE_COMPRESSED"); strQry.AppendLine(",FCDD.FILE_LAST_UPDATED_DATE"); strQry.AppendLine(",FCDD.FILE_CRC_VALUE"); strQry.AppendLine(",ISNULL(MAX(FCDC.FILE_CHUNK_NO),0) AS MAX_FILE_CHUNK_NO"); strQry.AppendLine(",'' AS DOWNLOAD_IND"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS FCDD"); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS FCDC"); strQry.AppendLine(" ON FCDD.FILE_LAYER_IND = FCDC.FILE_LAYER_IND"); strQry.AppendLine(" AND FCDD.FILE_NAME = FCDC.FILE_NAME"); strQry.AppendLine(" WHERE FCDD.FILE_LAYER_IND = 'P'"); strQry.AppendLine(" GROUP BY "); strQry.AppendLine(" FCDD.FILE_LAYER_IND"); strQry.AppendLine(",FCDD.FILE_NAME"); strQry.AppendLine(",FCDD.FILE_SIZE"); strQry.AppendLine(",FCDD.FILE_SIZE_COMPRESSED"); strQry.AppendLine(",FCDD.FILE_LAST_UPDATED_DATE"); strQry.AppendLine(",FCDD.FILE_CRC_VALUE"); strQry.AppendLine(" ORDER BY "); strQry.AppendLine(" 2"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Files"); bytCompress = (byte[])Check_Reboot_Server(); DataSet DataSetTemp = clsDBConnectionObjects.DeCompress_Array_To_DataSet(bytCompress); if (DataSetTemp.Tables["ReturnValues"].Rows[0]["REBOOT_IND"].ToString() == "Y") { DataSet.Tables["ReturnValues"].Rows[0]["REBOOT_IND"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["REBOOT_IND"].ToString(); DataSet.Tables["ReturnValues"].Rows[0]["MACHINE_NAME"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["MACHINE_NAME"].ToString(); DataSet.Tables["ReturnValues"].Rows[0]["MACHINE_IP"] = DataSetTemp.Tables["ReturnValues"].Rows[0]["MACHINE_IP"].ToString(); } } bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public void Convert_Client_Records(Int64 parint64CompanyNo, string parstrToPayCategoryType, byte[] parbytCompressDataSet) { DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parbytCompressDataSet); StringBuilder strQry = new StringBuilder(); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" A.TABLE_NAME"); strQry.AppendLine(" FROM InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS A "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.INFORMATION_SCHEMA.COLUMNS B"); strQry.AppendLine(" ON B.TABLE_NAME = A.TABLE_NAME "); strQry.AppendLine(" AND B.COLUMN_NAME = 'PAY_CATEGORY_NO' "); strQry.AppendLine(" WHERE A.COLUMN_NAME = 'PAY_CATEGORY_TYPE'"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "TablesToFix"); for (int intRow = 0; intRow < DataSet.Tables["PayCategory"].Rows.Count; intRow++) { strQry.Clear(); strQry.AppendLine(" UPDATE UE "); strQry.AppendLine(" SET UE.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType)); strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_EMPLOYEE UE "); strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("("); strQry.AppendLine(" SELECT "); strQry.AppendLine(" PC.COMPANY_NO"); strQry.AppendLine(",EPC.EMPLOYEE_NO"); strQry.AppendLine(",PC.PAY_CATEGORY_TYPE"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC "); strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString()); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString())); strQry.AppendLine(") AS JOIN_TABLE"); strQry.AppendLine(" ON UE.COMPANY_NO = JOIN_TABLE.COMPANY_NO "); strQry.AppendLine(" AND UE.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO"); strQry.AppendLine(" AND UE.PAY_CATEGORY_TYPE = JOIN_TABLE.PAY_CATEGORY_TYPE"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" UPDATE E "); strQry.AppendLine(" SET E.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType)); strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE E "); strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("("); strQry.AppendLine(" SELECT "); strQry.AppendLine(" PC.COMPANY_NO"); strQry.AppendLine(",EPC.EMPLOYEE_NO"); strQry.AppendLine(",PC.PAY_CATEGORY_TYPE"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC "); strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString()); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString())); strQry.AppendLine(") AS JOIN_TABLE"); strQry.AppendLine(" ON E.COMPANY_NO = JOIN_TABLE.COMPANY_NO "); strQry.AppendLine(" AND E.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO"); strQry.AppendLine(" AND E.PAY_CATEGORY_TYPE = JOIN_TABLE.PAY_CATEGORY_TYPE"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); //Break strQry.Clear(); if (parstrToPayCategoryType == "W") { strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT "); } else { if (parstrToPayCategoryType == "S") { strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT "); } else { //Time Attend strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT "); } } strQry.AppendLine("(COMPANY_NO "); strQry.AppendLine(",EMPLOYEE_NO "); strQry.AppendLine(",PAY_CATEGORY_NO "); strQry.AppendLine(",BREAK_DATE "); strQry.AppendLine(",BREAK_SEQ "); strQry.AppendLine(",BREAK_TIME_IN_MINUTES "); strQry.AppendLine(",BREAK_TIME_OUT_MINUTES "); strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES "); strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES) "); strQry.AppendLine(" SELECT "); strQry.AppendLine(" A.COMPANY_NO "); strQry.AppendLine(",A.EMPLOYEE_NO "); strQry.AppendLine(",A.PAY_CATEGORY_NO "); strQry.AppendLine(",A.BREAK_DATE "); strQry.AppendLine(",A.BREAK_SEQ "); strQry.AppendLine(",A.BREAK_TIME_IN_MINUTES "); strQry.AppendLine(",A.BREAK_TIME_OUT_MINUTES "); strQry.AppendLine(",A.CLOCKED_TIME_IN_MINUTES "); strQry.AppendLine(",A.CLOCKED_TIME_OUT_MINUTES "); if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W") { strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT A"); } else { if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S") { strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT A"); } else { //Time Attend strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT A"); } } strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("("); strQry.AppendLine(" SELECT "); strQry.AppendLine(" PC.COMPANY_NO"); strQry.AppendLine(",EPC.EMPLOYEE_NO"); strQry.AppendLine(",PC.PAY_CATEGORY_NO"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC "); strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString()); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString())); strQry.AppendLine(") AS JOIN_TABLE"); strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO "); strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO"); strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" DELETE A "); if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W") { strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_BREAK_CURRENT A "); } else { if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S") { strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_BREAK_CURRENT A "); } else { //Time Attend strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_BREAK_CURRENT A "); } } strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("("); strQry.AppendLine(" SELECT "); strQry.AppendLine(" PC.COMPANY_NO"); strQry.AppendLine(",EPC.EMPLOYEE_NO"); strQry.AppendLine(",PC.PAY_CATEGORY_NO"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC "); strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString()); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString())); strQry.AppendLine(") AS JOIN_TABLE"); strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO "); strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO"); strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); //Timesheets strQry.Clear(); if (parstrToPayCategoryType == "W") { strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT "); } else { if (parstrToPayCategoryType == "S") { strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT "); } else { //Time Attend strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT "); } } strQry.AppendLine("(COMPANY_NO "); strQry.AppendLine(",EMPLOYEE_NO "); strQry.AppendLine(",PAY_CATEGORY_NO "); strQry.AppendLine(",TIMESHEET_DATE "); strQry.AppendLine(",TIMESHEET_SEQ "); strQry.AppendLine(",TIMESHEET_TIME_IN_MINUTES "); strQry.AppendLine(",TIMESHEET_TIME_OUT_MINUTES "); strQry.AppendLine(",CLOCKED_TIME_IN_MINUTES "); strQry.AppendLine(",CLOCKED_TIME_OUT_MINUTES) "); strQry.AppendLine(" SELECT "); strQry.AppendLine(" A.COMPANY_NO "); strQry.AppendLine(",A.EMPLOYEE_NO "); strQry.AppendLine(",A.PAY_CATEGORY_NO "); strQry.AppendLine(",A.TIMESHEET_DATE "); strQry.AppendLine(",A.TIMESHEET_SEQ "); strQry.AppendLine(",A.TIMESHEET_TIME_IN_MINUTES "); strQry.AppendLine(",A.TIMESHEET_TIME_OUT_MINUTES "); strQry.AppendLine(",A.CLOCKED_TIME_IN_MINUTES "); strQry.AppendLine(",A.CLOCKED_TIME_OUT_MINUTES "); if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W") { strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT A "); } else { if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S") { strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT A "); } else { //Time Attend strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT A "); } } strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("("); strQry.AppendLine(" SELECT "); strQry.AppendLine(" PC.COMPANY_NO"); strQry.AppendLine(",EPC.EMPLOYEE_NO"); strQry.AppendLine(",PC.PAY_CATEGORY_NO"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC "); strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString()); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString())); strQry.AppendLine(") AS JOIN_TABLE"); strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO "); strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO"); strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" DELETE A "); if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "W") { strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIMESHEET_CURRENT A "); } else { if (DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString() == "S") { strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_SALARY_TIMESHEET_CURRENT A "); } else { //Time Attend strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_TIME_ATTEND_TIMESHEET_CURRENT A "); } } strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("("); strQry.AppendLine(" SELECT "); strQry.AppendLine(" PC.COMPANY_NO"); strQry.AppendLine(",EPC.EMPLOYEE_NO"); strQry.AppendLine(",PC.PAY_CATEGORY_NO"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.PAY_CATEGORY PC "); strQry.AppendLine(" INNER JOIN InteractPayrollClient.dbo.EMPLOYEE_PAY_CATEGORY EPC "); strQry.AppendLine(" ON PC.COMPANY_NO = EPC.COMPANY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = EPC.PAY_CATEGORY_NO "); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = EPC.PAY_CATEGORY_TYPE "); strQry.AppendLine(" WHERE PC.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND PC.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString()); strQry.AppendLine(" AND PC.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString())); strQry.AppendLine(") AS JOIN_TABLE"); strQry.AppendLine(" ON A.COMPANY_NO = JOIN_TABLE.COMPANY_NO "); strQry.AppendLine(" AND A.EMPLOYEE_NO = JOIN_TABLE.EMPLOYEE_NO"); strQry.AppendLine(" AND A.PAY_CATEGORY_NO = JOIN_TABLE.PAY_CATEGORY_NO"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); for (int intTableRow = 0; intTableRow < DataSet.Tables["TablesToFix"].Rows.Count; intTableRow++) { strQry.Clear(); strQry.AppendLine(" UPDATE A "); strQry.AppendLine(" SET A.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(parstrToPayCategoryType)); strQry.AppendLine(" FROM InteractPayrollClient.dbo." + DataSet.Tables["TablesToFix"].Rows[intTableRow]["TABLE_NAME"].ToString() + " A "); strQry.AppendLine(" WHERE A.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND A.PAY_CATEGORY_TYPE = " + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_TYPE"].ToString())); strQry.AppendLine(" AND A.PAY_CATEGORY_NO = " + DataSet.Tables["PayCategory"].Rows[intRow]["PAY_CATEGORY_NO"].ToString()); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } } }
public void Update_Records(Int64 parint64CompanyNo, int parintDeviceNo, byte[] byteCompressedDataSet) { //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteCompressedDataSet); StringBuilder strQry = new StringBuilder(); strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_EMPLOYEE_LINK "); strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo); strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); for (int intRowCount = 0; intRowCount < DataSet.Tables["EmployeeChosen"].Rows.Count; intRowCount++) { strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_EMPLOYEE_LINK"); strQry.AppendLine("(DEVICE_NO"); strQry.AppendLine(",COMPANY_NO"); strQry.AppendLine(",EMPLOYEE_NO"); strQry.AppendLine(",PAY_CATEGORY_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE)"); strQry.AppendLine(" VALUES "); strQry.AppendLine("(" + parintDeviceNo); strQry.AppendLine("," + parint64CompanyNo); strQry.AppendLine("," + DataSet.Tables["EmployeeChosen"].Rows[intRowCount]["EMPLOYEE_NO"].ToString()); strQry.AppendLine("," + DataSet.Tables["EmployeeChosen"].Rows[intRowCount]["PAY_CATEGORY_NO"].ToString()); strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["EmployeeChosen"].Rows[intRowCount]["PAY_CATEGORY_TYPE"].ToString()) + ")"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK "); strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo); strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); for (int intRowCount = 0; intRowCount < DataSet.Tables["PayCategoryChosen"].Rows.Count; intRowCount++) { strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK"); strQry.AppendLine("(DEVICE_NO"); strQry.AppendLine(",COMPANY_NO"); strQry.AppendLine(",PAY_CATEGORY_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE)"); strQry.AppendLine(" VALUES "); strQry.AppendLine("(" + parintDeviceNo); strQry.AppendLine("," + parint64CompanyNo); strQry.AppendLine("," + DataSet.Tables["PayCategoryChosen"].Rows[intRowCount]["PAY_CATEGORY_NO"].ToString()); strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["PayCategoryChosen"].Rows[intRowCount]["PAY_CATEGORY_TYPE"].ToString()) + ")"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } strQry.Clear(); strQry.AppendLine(" DELETE DEL FROM InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK_ACTIVE DEL "); strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("(SELECT DISTINCT "); strQry.AppendLine(" DPCLA.DEVICE_NO"); strQry.AppendLine(",DPCLA.COMPANY_NO"); strQry.AppendLine(",DPCLA.PAY_CATEGORY_NO"); strQry.AppendLine(",DPCLA.PAY_CATEGORY_TYPE "); strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK_ACTIVE DPCLA "); strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_PAY_CATEGORY_LINK DPCL"); strQry.AppendLine(" ON DPCL.DEVICE_NO = DPCLA.DEVICE_NO"); strQry.AppendLine(" AND DPCL.COMPANY_NO = DPCLA.COMPANY_NO"); strQry.AppendLine(" AND DPCL.PAY_CATEGORY_NO = DPCLA.PAY_CATEGORY_NO"); strQry.AppendLine(" AND DPCL.PAY_CATEGORY_TYPE = DPCLA.PAY_CATEGORY_TYPE "); strQry.AppendLine(" WHERE DPCLA.DEVICE_NO = " + parintDeviceNo); strQry.AppendLine(" AND DPCLA.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND DPCL.PAY_CATEGORY_NO IS NULL) AS TEMP_TABLE"); strQry.AppendLine(" ON DEL.DEVICE_NO = TEMP_TABLE.DEVICE_NO"); strQry.AppendLine(" AND DEL.COMPANY_NO = TEMP_TABLE.COMPANY_NO"); strQry.AppendLine(" AND DEL.PAY_CATEGORY_NO = TEMP_TABLE.PAY_CATEGORY_NO"); strQry.AppendLine(" AND DEL.PAY_CATEGORY_TYPE = TEMP_TABLE.PAY_CATEGORY_TYPE "); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK "); strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo); strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); for (int intRowCount = 0; intRowCount < DataSet.Tables["DepartmentChosen"].Rows.Count; intRowCount++) { strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK"); strQry.AppendLine("(DEVICE_NO"); strQry.AppendLine(",COMPANY_NO"); strQry.AppendLine(",PAY_CATEGORY_NO"); strQry.AppendLine(",PAY_CATEGORY_TYPE"); strQry.AppendLine(",DEPARTMENT_NO)"); strQry.AppendLine(" VALUES "); strQry.AppendLine("(" + parintDeviceNo); strQry.AppendLine("," + parint64CompanyNo); strQry.AppendLine("," + DataSet.Tables["DepartmentChosen"].Rows[intRowCount]["PAY_CATEGORY_NO"].ToString()); strQry.AppendLine("," + clsDBConnectionObjects.Text2DynamicSQL(DataSet.Tables["DepartmentChosen"].Rows[intRowCount]["PAY_CATEGORY_TYPE"].ToString())); strQry.AppendLine("," + DataSet.Tables["DepartmentChosen"].Rows[intRowCount]["DEPARTMENT_NO"].ToString() + ")"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } strQry.Clear(); strQry.AppendLine(" DELETE DEL FROM InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK_ACTIVE DEL "); strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("(SELECT DISTINCT "); strQry.AppendLine(" DDLA.DEVICE_NO"); strQry.AppendLine(",DDLA.COMPANY_NO"); strQry.AppendLine(",DDLA.PAY_CATEGORY_NO"); strQry.AppendLine(",DDLA.PAY_CATEGORY_TYPE "); strQry.AppendLine(",DDLA.DEPARTMENT_NO "); strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK_ACTIVE DDLA "); strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_DEPARTMENT_LINK DDL"); strQry.AppendLine(" ON DDL.DEVICE_NO = DDLA.DEVICE_NO"); strQry.AppendLine(" AND DDL.COMPANY_NO = DDLA.COMPANY_NO"); strQry.AppendLine(" AND DDL.PAY_CATEGORY_NO = DDLA.PAY_CATEGORY_NO"); strQry.AppendLine(" AND DDL.PAY_CATEGORY_TYPE = DDLA.PAY_CATEGORY_TYPE "); strQry.AppendLine(" AND DDL.DEPARTMENT_NO = DDLA.DEPARTMENT_NO "); strQry.AppendLine(" WHERE DDLA.DEVICE_NO = " + parintDeviceNo); strQry.AppendLine(" AND DDLA.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND DDL.DEPARTMENT_NO IS NULL) AS TEMP_TABLE"); strQry.AppendLine(" ON DEL.DEVICE_NO = TEMP_TABLE.DEVICE_NO"); strQry.AppendLine(" AND DEL.COMPANY_NO = TEMP_TABLE.COMPANY_NO"); strQry.AppendLine(" AND DEL.PAY_CATEGORY_NO = TEMP_TABLE.PAY_CATEGORY_NO"); strQry.AppendLine(" AND DEL.PAY_CATEGORY_TYPE = TEMP_TABLE.PAY_CATEGORY_TYPE "); strQry.AppendLine(" AND DEL.DEPARTMENT_NO = TEMP_TABLE.DEPARTMENT_NO "); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.DEVICE_GROUP_LINK "); strQry.AppendLine(" WHERE DEVICE_NO = " + parintDeviceNo); strQry.AppendLine(" AND COMPANY_NO = " + parint64CompanyNo); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); for (int intRowCount = 0; intRowCount < DataSet.Tables["GroupChosen"].Rows.Count; intRowCount++) { strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DEVICE_GROUP_LINK"); strQry.AppendLine("(DEVICE_NO"); strQry.AppendLine(",COMPANY_NO"); strQry.AppendLine(",GROUP_NO)"); strQry.AppendLine(" VALUES "); strQry.AppendLine("(" + parintDeviceNo); strQry.AppendLine("," + parint64CompanyNo); strQry.AppendLine("," + DataSet.Tables["GroupChosen"].Rows[intRowCount]["GROUP_NO"].ToString() + ")"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } strQry.Clear(); strQry.AppendLine(" DELETE DEL FROM InteractPayrollClient.dbo.DEVICE_GROUP_LINK_ACTIVE DEL "); strQry.AppendLine(" INNER JOIN "); strQry.AppendLine("(SELECT DISTINCT "); strQry.AppendLine(" DGLA.DEVICE_NO"); strQry.AppendLine(",DGLA.COMPANY_NO"); strQry.AppendLine(",DGLA.GROUP_NO"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.DEVICE_GROUP_LINK_ACTIVE DGLA "); strQry.AppendLine(" LEFT JOIN InteractPayrollClient.dbo.DEVICE_GROUP_LINK DGL"); strQry.AppendLine(" ON DGLA.DEVICE_NO = DGL.DEVICE_NO"); strQry.AppendLine(" AND DGLA.COMPANY_NO = DGL.COMPANY_NO"); strQry.AppendLine(" AND DGLA.GROUP_NO = DGL.GROUP_NO"); strQry.AppendLine(" WHERE DGLA.DEVICE_NO = " + parintDeviceNo); strQry.AppendLine(" AND DGLA.COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND DGL.GROUP_NO IS NULL) AS TEMP_TABLE"); strQry.AppendLine(" ON DEL.DEVICE_NO = TEMP_TABLE.DEVICE_NO"); strQry.AppendLine(" AND DEL.COMPANY_NO = TEMP_TABLE.COMPANY_NO"); strQry.AppendLine(" AND DEL.GROUP_NO = TEMP_TABLE.GROUP_NO"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); }
public void Cleanup_Client_DataBase_Files(byte[] parByteArrayDataSet) { //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes DataSet parClientDataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(parByteArrayDataSet); StringBuilder strQry = new StringBuilder(); for (int intRow = 0; intRow < parClientDataSet.Tables["FileToDelete"].Rows.Count; intRow++) { strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_DETAILS"); strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_LAYER_IND"].ToString())); strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_NAME"].ToString())); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.FILE_CLIENT_DOWNLOAD_CHUNKS"); strQry.AppendLine(" WHERE FILE_LAYER_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_LAYER_IND"].ToString())); strQry.AppendLine(" AND FILE_NAME = " + clsDBConnectionObjects.Text2DynamicSQL(parClientDataSet.Tables["FileToDelete"].Rows[intRow]["FILE_NAME"].ToString())); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } }
public byte[] Get_Form_Records() { //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(); byte[] bytCompress; bool blnInsert = false; int intYear = DateTime.Now.Year; if (DateTime.Now.Month < 3) { intYear = intYear - 1; } DateTime dtDateTimeStart = new DateTime(intYear, 3, 1); DateTime dtDateTimeEnd = new DateTime(intYear + 1, 3, 1).AddDays(-1); DataSet DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" FINGERPRINT_SOFTWARE_IND"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_SOFTWARE_TO_USE "); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Software"); if (DataSet.Tables["Software"].Rows.Count == 0) { strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.FINGERPRINT_SOFTWARE_TO_USE"); strQry.AppendLine("(FINGERPRINT_SOFTWARE_IND)"); strQry.AppendLine(" VALUES"); strQry.AppendLine("('D')"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" IDENTIFY_THRESHOLD_VALUE"); strQry.AppendLine(",VERIFY_THRESHOLD_VALUE"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_IDENTIFY_VERIFY_THRESHOLD "); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "IdentifyVerify"); if (DataSet.Tables["IdentifyVerify"].Rows.Count == 0) { strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.FINGERPRINT_IDENTIFY_VERIFY_THRESHOLD"); strQry.AppendLine("(IDENTIFY_THRESHOLD_VALUE"); strQry.AppendLine(",VERIFY_THRESHOLD_VALUE)"); strQry.AppendLine(" VALUES"); strQry.AppendLine("(45"); strQry.AppendLine(",25)"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" READ_OPTION_NO"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.READ_OPTION "); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "ReadOption"); if (DataSet.Tables["ReadOption"].Rows.Count == 0) { //New Database Created strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.READ_OPTION "); strQry.AppendLine("(READ_OPTION_NO"); strQry.AppendLine(",READ_OPTION_DESC) "); strQry.AppendLine(" VALUES "); strQry.AppendLine("(0"); strQry.AppendLine(",'None')"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.READ_OPTION "); strQry.AppendLine("(READ_OPTION_NO"); strQry.AppendLine(",READ_OPTION_DESC) "); strQry.AppendLine(" VALUES "); strQry.AppendLine("(1"); strQry.AppendLine(",'FingerPrint')"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.READ_OPTION "); strQry.AppendLine("(READ_OPTION_NO"); strQry.AppendLine(",READ_OPTION_DESC) "); strQry.AppendLine(" VALUES "); strQry.AppendLine("(2"); strQry.AppendLine(",'RFID Card Only')"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.READ_OPTION "); strQry.AppendLine("(READ_OPTION_NO"); strQry.AppendLine(",READ_OPTION_DESC) "); strQry.AppendLine(" VALUES "); strQry.AppendLine("(3"); strQry.AppendLine(",'RFID Card And FingerPrint')"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" MAX(DAY_DATE) AS MAX_DATE"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.DATES "); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "Temp"); if (DataSet.Tables["Temp"].Rows[0]["MAX_DATE"] == System.DBNull.Value) { blnInsert = true; } else { if (Convert.ToDateTime(DataSet.Tables["Temp"].Rows[0]["MAX_DATE"]) < DateTime.Now) { blnInsert = true; dtDateTimeStart = new DateTime(Convert.ToDateTime(DataSet.Tables["Temp"].Rows[0]["MAX_DATE"]).Year, Convert.ToDateTime(DataSet.Tables["Temp"].Rows[0]["MAX_DATE"]).Month, Convert.ToDateTime(DataSet.Tables["Temp"].Rows[0]["MAX_DATE"]).Day).AddDays(1); } else { if (DateTime.Now.Month == 1 | DateTime.Now.Month == 2) { DateTime newDateTimeCheck = new DateTime(DateTime.Now.Year, 3, 1).AddDays(-1); if (newDateTimeCheck.ToString("yyyyMMdd") == Convert.ToDateTime(DataSet.Tables["Temp"].Rows[0]["MAX_DATE"]).ToString("yyyyMMdd")) { blnInsert = true; dtDateTimeStart = dtDateTimeStart.AddYears(1); dtDateTimeEnd = dtDateTimeEnd.AddYears(1); } } } } if (blnInsert == true) { while (true) { strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.DATES"); strQry.AppendLine("(DAY_DATE"); strQry.AppendLine(",DAY_NO)"); strQry.AppendLine(" VALUES "); strQry.AppendLine("('" + dtDateTimeStart.ToString("yyyy-MM-dd") + "'"); strQry.AppendLine("," + Convert.ToInt32(dtDateTimeStart.DayOfWeek) + ")"); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); if (dtDateTimeStart.Year == dtDateTimeEnd.Year & dtDateTimeStart.Month == dtDateTimeEnd.Month & dtDateTimeStart.Day == dtDateTimeEnd.Day) { break; } dtDateTimeStart = dtDateTimeStart.AddDays(1); } } DataSet = null; DataSet = new DataSet(); strQry.Clear(); strQry.AppendLine(" SELECT "); strQry.AppendLine(" FINGERPRINT_SOFTWARE_IND"); strQry.AppendLine(" FROM InteractPayrollClient.dbo.FINGERPRINT_SOFTWARE_TO_USE"); clsDBConnectionObjects.Create_DataTable_Client(strQry.ToString(), DataSet, "SoftwareToUse"); bytCompress = clsDBConnectionObjects.Compress_DataSet(DataSet); DataSet.Dispose(); DataSet = null; return(bytCompress); }
public void Update_Employee(Int64 parint64CompanyNo, Int64 parintEmployeeNo, string parstrRFIDCardNo, string parstrUseEmpNo, byte[] byteCompressedDataSet) { //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteCompressedDataSet); StringBuilder strQry = new StringBuilder(); for (int intRow = 0; intRow < DataSet.Tables["EmployeeFingerTemplate"].Rows.Count; intRow++) { if (DataSet.Tables["EmployeeFingerTemplate"].Rows[0].RowState == DataRowState.Added) { strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.EMPLOYEE_FINGERPRINT_TEMPLATE"); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo); strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo); strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["EmployeeFingerTemplate"].Rows[intRow]["FINGER_NO"].ToString()); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_FINGERPRINT_TEMPLATE"); strQry.AppendLine("(COMPANY_NO"); strQry.AppendLine(",EMPLOYEE_NO"); strQry.AppendLine(",FINGER_NO"); strQry.AppendLine(",FINGER_TEMPLATE)"); strQry.AppendLine(" VALUES "); strQry.AppendLine("(" + parint64CompanyNo); strQry.AppendLine("," + parintEmployeeNo); strQry.AppendLine("," + DataSet.Tables["EmployeeFingerTemplate"].Rows[intRow]["FINGER_NO"].ToString()); strQry.AppendLine(",@FINGER_TEMPLATE) "); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString(), (byte[])DataSet.Tables["EmployeeFingerTemplate"].Rows[intRow]["FINGER_TEMPLATE"], "@FINGER_TEMPLATE"); } else { strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.EMPLOYEE_FINGERPRINT_TEMPLATE_DELETE"); strQry.AppendLine("(COMPANY_NO"); strQry.AppendLine(",EMPLOYEE_NO"); strQry.AppendLine(",FINGER_NO"); strQry.AppendLine(",CREATION_DATETIME)"); strQry.AppendLine(" SELECT "); strQry.AppendLine(" COMPANY_NO"); strQry.AppendLine(",EMPLOYEE_NO"); strQry.AppendLine(",FINGER_NO"); strQry.AppendLine(",CREATION_DATETIME "); strQry.AppendLine(" FROM InteractPayrollClient.dbo.EMPLOYEE_FINGERPRINT_TEMPLATE"); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo.ToString()); strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo.ToString()); strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["EmployeeFingerTemplate"].Rows[intRow]["FINGER_NO", DataRowVersion.Original].ToString()); strQry.AppendLine(" AND NOT CREATION_DATETIME IS NULL "); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.EMPLOYEE_FINGERPRINT_TEMPLATE"); strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo.ToString()); strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo.ToString()); strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["EmployeeFingerTemplate"].Rows[intRow]["FINGER_NO", DataRowVersion.Original].ToString()); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } } //2017-10-05 (Moved To Internet) //if (parstrRFIDCardNo == "") //{ // strQry.Clear(); // strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.EMPLOYEE_RFID_CARD"); // strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo.ToString()); // strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo.ToString()); // clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); //} //2017-10-05 (Moved To Internet) //strQry.Clear(); //strQry.AppendLine(" UPDATE EMPLOYEE"); //strQry.AppendLine(" SET USE_EMPLOYEE_NO_IND = " + clsDBConnectionObjects.Text2DynamicSQL(parstrUseEmpNo)); //strQry.AppendLine(" WHERE COMPANY_NO = " + parint64CompanyNo.ToString()); //strQry.AppendLine(" AND EMPLOYEE_NO = " + parintEmployeeNo.ToString()); //clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); }
public void Update_Records(Int64 parint64UserNo, byte[] byteCompressedDataSet) { //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes //Stop Make Sure That Interface is Backward Compatible. Create New Function / Procedure if Parameter List Changes DataSet DataSet = clsDBConnectionObjects.DeCompress_Array_To_DataSet(byteCompressedDataSet); StringBuilder strQry = new StringBuilder(); for (int intRow = 0; intRow < DataSet.Tables["UserFingerTemplate"].Rows.Count; intRow++) { if (DataSet.Tables["UserFingerTemplate"].Rows[intRow].RowState == DataRowState.Added) { strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE"); strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo.ToString()); strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO"].ToString()); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); //There Should be only One - otherwise Error strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE"); strQry.AppendLine("(USER_NO"); strQry.AppendLine(",FINGER_NO"); strQry.AppendLine(",FINGER_TEMPLATE)"); strQry.AppendLine(" VALUES "); strQry.AppendLine("(" + parint64UserNo); strQry.AppendLine("," + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO"].ToString()); strQry.AppendLine(",@FINGER_TEMPLATE) "); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString(), (byte[])DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_TEMPLATE"], "@FINGER_TEMPLATE"); } else { strQry.Clear(); strQry.AppendLine(" INSERT INTO InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE_DELETE"); strQry.AppendLine("(USER_NO"); strQry.AppendLine(",FINGER_NO"); strQry.AppendLine(",CREATION_DATETIME)"); strQry.AppendLine(" SELECT "); strQry.AppendLine(" USER_NO"); strQry.AppendLine(",FINGER_NO"); strQry.AppendLine(",CREATION_DATETIME "); strQry.AppendLine(" FROM InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE"); strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo.ToString()); strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO", DataRowVersion.Original].ToString()); strQry.AppendLine(" AND NOT CREATION_DATETIME IS NULL "); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); strQry.Clear(); strQry.AppendLine(" DELETE FROM InteractPayrollClient.dbo.USER_FINGERPRINT_TEMPLATE"); strQry.AppendLine(" WHERE USER_NO = " + parint64UserNo.ToString()); strQry.AppendLine(" AND FINGER_NO = " + DataSet.Tables["UserFingerTemplate"].Rows[intRow]["FINGER_NO", DataRowVersion.Original].ToString()); clsDBConnectionObjects.Execute_SQLCommand_Client(strQry.ToString()); } } }