public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsShowDescription, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); ////if (IsIncludeInternalID) //// tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_AVCPLAN, typeof(string)); if (IsIncludeSyncID) tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpAVCPlan.db.select(dbConn, filter); foreach (EEmpAVCPlan empAVCPlan in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(empAVCPlan.EmpAVCID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_FROM] = empAVCPlan.EmpAVCEffFr; row[FIELD_TO] = empAVCPlan.EmpAVCEffTo; EAVCPlan avcPlan = new EAVCPlan(); avcPlan.AVCPlanID = empAVCPlan.AVCPlanID; if (EAVCPlan.db.select(dbConn, avcPlan)) row[FIELD_AVCPLAN] = IsShowDescription ? avcPlan.AVCPlanDesc : avcPlan.AVCPlanCode; if (IsIncludeSyncID) row[FIELD_SYNC_ID] = empAVCPlan.SynID; tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); return tmpDataTable; }
protected void Import_Click(object sender, EventArgs e) { ImportEmpPersonalInfoProcess empImport = new ImportEmpPersonalInfoProcess(dbConn, Session.SessionID); WebUtils.StartFunction(Session, FUNCTION_CODE); empImport.ImportToDatabase(); WebUtils.EndFunction(dbConn); empImport.ClearTempTable(); //loadData(info, db, Repeater); PageErrors.getErrors(db, Page).addError(HROne.Translation.PageMessage.IMPORT_SUCCESSFUL); }
public DataTable ExportStandardBonusTemplate(bool IsIncludeCurrentPositionInfo) { DataTable tmpDataTable = new DataTable(TABLE_NAME_S); tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_TARGET_SALARY, typeof(double)); tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_STD_RATE, typeof(double)); tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_YEAR_OF_SERVICE, typeof(double)); tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_BONUS_AMOUNT, typeof(double)); DBFilter m_filter = new DBFilter(); m_filter.add(new Match("BonusProcessID", m_BonusProcessID)); m_filter.add(new Match("EmpBonusProcessType", "S")); m_filter.add("EmpID", true); EBonusProcess m_bonusProcess = EBonusProcess.GetObject(dbConn, m_BonusProcessID); foreach (EEmpBonusProcess m_empBonusProcess in EEmpBonusProcess.db.select(dbConn, m_filter)) { EEmpPersonalInfo empInfo = EEmpPersonalInfo.GetObject(dbConn, m_empBonusProcess.EmpID); DataRow row = tmpDataTable.NewRow(); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_TARGET_SALARY] = m_empBonusProcess.EmpBonusProcessTargetSalary; row[FIELD_STD_RATE] = m_bonusProcess.BonusProcessStdRate; row[FIELD_YEAR_OF_SERVICE] = Math.Round(m_empBonusProcess.EmpBonusProcessBonusProportion, 4); row[FIELD_BONUS_AMOUNT] = Math.Round(m_empBonusProcess.EmpBonusProcessBonusAmount, 2); tmpDataTable.Rows.Add(row); } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public static DataTable ExportTemplate(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo) { DataTable tmpDataTable = new DataTable(TABLE_NAME); tmpDataTable.Columns.Add(HROne.Import.ImportCommissionAchievementProcess.FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(HROne.Import.ImportCommissionAchievementProcess.FIELD_BASIC_SALARY, typeof(double)); tmpDataTable.Columns.Add(HROne.Import.ImportCommissionAchievementProcess.FIELD_FPS, typeof(double)); tmpDataTable.Columns.Add(HROne.Import.ImportCommissionAchievementProcess.FIELD_EFF_DATE, typeof(DateTime)); tmpDataTable.Columns.Add(HROne.Import.ImportCommissionAchievementProcess.FIELD_PERCENT, typeof(double)); foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter m_recurringPaymentFilter = new DBFilter(); m_recurringPaymentFilter.add(new Match("EmpID", empInfo.EmpID)); m_recurringPaymentFilter.add(new NullTerm("EmpRPEffTo")); m_recurringPaymentFilter.add(AppUtils.GetPayemntCodeDBTermByPaymentType(dbConn, "PayCodeID", "BASICSAL")); foreach (EEmpRecurringPayment m_rpInfo in EEmpRecurringPayment.db.select(dbConn, m_recurringPaymentFilter)) { DataRow row = tmpDataTable.NewRow(); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_BASIC_SALARY] = m_rpInfo.EmpRPBasicSalary; row[FIELD_FPS] = m_rpInfo.EmpRPFPS; tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public DataView loadData(ListInfo info, DBManager db, DataList repeater) { //filter.add(new Match("EmpPayrollID",EmpPayrollID.Value)); ImportEmpPersonalInfoProcess empImport = new ImportEmpPersonalInfoProcess(dbConn, Session.SessionID); DataTable table = empImport.GetImportDataFromTempDatabase(info); if (info != null) { if (!string.IsNullOrEmpty(info.orderby)) { if (info.orderby.Equals("EmpEngFullName", StringComparison.CurrentCultureIgnoreCase)) { if (!table.Columns.Contains("EmpEngFullName")) { table.Columns.Add("EmpEngFullName", typeof(string)); foreach (System.Data.DataRow row in table.Rows) { EUploadEmpPersonalInfo empInfo = new EUploadEmpPersonalInfo(); empInfo.UploadEmpID = (int)row["UploadEmpID"]; if (EUploadEmpPersonalInfo.db.select(dbConn, empInfo)) { row["EmpEngFullName"] = empInfo.EmpEngFullName; } } } } } } table = WebUtils.DataTableSortingAndPaging(table, info); view = new DataView(table); if (repeater != null) { repeater.DataSource = view; repeater.DataBind(); } if (table.Rows.Count > 0) { ImportSection.Visible = true; } else { ImportSection.Visible = false; } return(view); }
public static DataTable ExportTemplate(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo) { DataTable tmpDataTable = new DataTable(TABLE_NAME); tmpDataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_EFFECTIVE_DATE, typeof(DateTime)); tmpDataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_CODE, typeof(string)); tmpDataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_METHOD, typeof(string)); tmpDataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_BANK_ACCOUNT_NO, typeof(string)); tmpDataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_AMOUNT, typeof(double)); tmpDataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_NUM_OF_DAY_ADJUST, typeof(double)); tmpDataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_REST_PAYMENT, typeof(string)); tmpDataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_COST_CENTER, typeof(string)); tmpDataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_REMARK, typeof(string)); foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DataRow row = tmpDataTable.NewRow(); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } tmpDataTable.Rows.Add(row); } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public static DataTable ExportTemplate(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo) { DataTable tmpDataTable = new DataTable(TABLE_NAME); tmpDataTable.Columns.Add(HROne.Import.ImportDoublePayAdjustmentProcess.FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(HROne.Import.ImportDoublePayAdjustmentProcess.FIELD_EFF_DATE, typeof(DateTime)); tmpDataTable.Columns.Add(HROne.Import.ImportDoublePayAdjustmentProcess.FIELD_PERCENT, typeof(double)); foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DataRow row = tmpDataTable.NewRow(); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } tmpDataTable.Rows.Add(row); } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsShowDescription, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_PAYMENT_CODE, typeof(string)); if (ESystemParameter.getParameter(dbConn, ESystemParameter.PARAM_CODE_PAYSCALE_POINT_SYSTEM) == "Y") // for WaiJe { tmpDataTable.Columns.Add(FIELD_SCHEME_CODE, typeof(string)); tmpDataTable.Columns.Add(FIELD_CAPACITY, typeof(string)); tmpDataTable.Columns.Add(FIELD_POINT, typeof(decimal)); } if (ESystemParameter.getParameter(dbConn, ESystemParameter.PARAM_CODE_MONTHLY_ACHIEVEMENT_COMMISSION) == "Y") // for F&V { tmpDataTable.Columns.Add(FIELD_BASIC_SALARY, typeof(decimal)); tmpDataTable.Columns.Add(FIELD_FPS, typeof(decimal)); } tmpDataTable.Columns.Add(FIELD_AMOUNT, typeof(double)); tmpDataTable.Columns.Add(FIELD_UNIT, typeof(string)); tmpDataTable.Columns.Add(FIELD_METHOD, typeof(string)); tmpDataTable.Columns.Add(FIELD_ACCOUNT_NO, typeof(string)); tmpDataTable.Columns.Add(FIELD_IS_NON_PAYROLL_ITEM, typeof(string)); tmpDataTable.Columns.Add(FIELD_COST_CENTER, typeof(string)); tmpDataTable.Columns.Add(FIELD_REMARK, typeof(string)); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpRecurringPayment.db.select(dbConn, filter); foreach (EEmpRecurringPayment empRecurringPayment in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(empRecurringPayment.EmpRPID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_FROM] = empRecurringPayment.EmpRPEffFr; row[FIELD_TO] = empRecurringPayment.EmpRPEffTo; EPaymentCode paymentCode = new EPaymentCode(); paymentCode.PaymentCodeID = empRecurringPayment.PayCodeID; if (EPaymentCode.db.select(dbConn, paymentCode)) { row[FIELD_PAYMENT_CODE] = IsShowDescription ? paymentCode.PaymentCodeDesc : paymentCode.PaymentCode; } if (ESystemParameter.getParameter(dbConn, ESystemParameter.PARAM_CODE_PAYSCALE_POINT_SYSTEM) == "Y") // for WaiJe { row[FIELD_SCHEME_CODE] = empRecurringPayment.SchemeCode; row[FIELD_CAPACITY] = empRecurringPayment.Capacity; row[FIELD_POINT] = empRecurringPayment.Point; } if (ESystemParameter.getParameter(dbConn, ESystemParameter.PARAM_CODE_MONTHLY_ACHIEVEMENT_COMMISSION) == "Y") // for F&V { row[FIELD_BASIC_SALARY] = empRecurringPayment.EmpRPBasicSalary; row[FIELD_FPS] = empRecurringPayment.EmpRPFPS; } row[FIELD_AMOUNT] = empRecurringPayment.EmpRPAmount; if (empRecurringPayment.EmpRPUnit.Equals("H")) { row[FIELD_UNIT] = "Hourly"; } else if (empRecurringPayment.EmpRPUnit.Equals("D")) { row[FIELD_UNIT] = "Daily"; } else if (empRecurringPayment.EmpRPUnit.Equals("P")) { row[FIELD_UNIT] = "Once per payroll cycle"; } if (empRecurringPayment.EmpRPMethod.Equals("A")) { row[FIELD_METHOD] = "Autopay"; EEmpBankAccount empBankAccount = new EEmpBankAccount(); empBankAccount.EmpBankAccountID = empRecurringPayment.EmpAccID; if (EEmpBankAccount.db.select(dbConn, empBankAccount)) { row[FIELD_ACCOUNT_NO] = empBankAccount.EmpBankCode + "-" + empBankAccount.EmpBranchCode + "-" + empBankAccount.EmpAccountNo; } } else if (empRecurringPayment.EmpRPMethod.Equals("Q")) { row[FIELD_METHOD] = "Cheque"; } else if (empRecurringPayment.EmpRPMethod.Equals("C")) { row[FIELD_METHOD] = "Cash"; } else { row[FIELD_METHOD] = "Others"; } row[FIELD_IS_NON_PAYROLL_ITEM] = empRecurringPayment.EmpRPIsNonPayrollItem ? "Yes" : "No"; ECostCenter costCenter = new ECostCenter(); costCenter.CostCenterID = empRecurringPayment.CostCenterID; if (ECostCenter.db.select(dbConn, costCenter)) { row[FIELD_COST_CENTER] = IsShowDescription ? costCenter.CostCenterDesc : costCenter.CostCenterCode; } row[FIELD_REMARK] = empRecurringPayment.EmpRPRemark; if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = empRecurringPayment.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public static DataTable ExportTemplate(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo) { DataTable tmpDataTable = new DataTable(TABLE_NAME); tmpDataTable.Columns.Add(HROne.Import.ImportHitRateBasedPaymentProcess.FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } //tmpDataTable.Columns.Add(HROne.Import.ImportHitRateBasedPaymentProcess.FIELD_EFF_DATE, typeof(DateTime)); tmpDataTable.Columns.Add(HROne.Import.ImportHitRateBasedPaymentProcess.FIELD_PAYMENT_CODE, typeof(string)); tmpDataTable.Columns.Add(HROne.Import.ImportHitRateBasedPaymentProcess.FIELD_PERCENT, typeof(double)); foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter m_empRPFilter = new DBFilter(); m_empRPFilter.add(new Match("EmpID", empInfo.EmpID)); m_empRPFilter.add(new NullTerm("EmpRPEffTo")); m_empRPFilter.add("EmpRPEffFr", true); foreach (EEmpRecurringPayment m_rpInfo in EEmpRecurringPayment.db.select(dbConn, m_empRPFilter)) { EPaymentCode m_paymentCode = EPaymentCode.GetObject(dbConn, m_rpInfo.PayCodeID); if (m_paymentCode.PaymentCodeIsHitRateBased) { DataRow row = tmpDataTable.NewRow(); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_PAYMENT_CODE] = m_paymentCode.PaymentCode; int m_monthDiff = AppUtils.ServerDateTime().Year * 12 + AppUtils.ServerDateTime().Month - (empInfo.EmpServiceDate.Year * 12 + empInfo.EmpServiceDate.Month); if (m_paymentCode.PaymentCodeDefaultRateAtMonth1 > 0 && m_monthDiff == 0) // service start in current month { row[FIELD_PERCENT] = m_paymentCode.PaymentCodeDefaultRateAtMonth1; } else if (m_paymentCode.PaymentCodeDefaultRateAtMonth2 > 0 && m_monthDiff == 1) // emp service start in last month { row[FIELD_PERCENT] = m_paymentCode.PaymentCodeDefaultRateAtMonth2; } else if (m_paymentCode.PaymentCodeDefaultRateAtMonth2 > 0 && m_monthDiff == 2) // emp service start in 2 months before this month { row[FIELD_PERCENT] = m_paymentCode.PaymentCodeDefaultRateAtMonth3; } tmpDataTable.Rows.Add(row); } } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsShowDescription, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_LEAVE_CODE, typeof(string)); tmpDataTable.Columns.Add(FIELD_DAY_TAKEN, typeof(double)); tmpDataTable.Columns.Add(FIELD_HOURS_CLAIM, typeof(double)); tmpDataTable.Columns.Add(FIELD_TYPE, typeof(string)); tmpDataTable.Columns.Add(FIELD_REMARK, typeof(string)); tmpDataTable.Columns.Add(FIELD_NOPAYROLLPROCESS, typeof(string)); tmpDataTable.Columns.Add(FIELD_MEDICIAL_CERTIFICATE, typeof(string)); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = ELeaveApplication.db.select(dbConn, filter); foreach (ELeaveApplication empLeaveApplication in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(empLeaveApplication.LeaveAppID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_FROM] = empLeaveApplication.LeaveAppDateFrom; row[FIELD_TO] = empLeaveApplication.LeaveAppDateTo; ELeaveCode leaveCode = new ELeaveCode(); leaveCode.LeaveCodeID = empLeaveApplication.LeaveCodeID; if (ELeaveCode.db.select(dbConn, leaveCode)) { row[FIELD_LEAVE_CODE] = IsShowDescription ? leaveCode.LeaveCodeDesc : leaveCode.LeaveCode; } row[FIELD_DAY_TAKEN] = empLeaveApplication.LeaveAppDays; row[FIELD_HOURS_CLAIM] = empLeaveApplication.LeaveAppHours; if (empLeaveApplication.LeaveAppUnit.Equals("H")) { row[FIELD_TYPE] = "Hour"; } else if (empLeaveApplication.LeaveAppUnit.Equals("D")) { row[FIELD_TYPE] = "Day"; } else if (empLeaveApplication.LeaveAppUnit.Equals("A")) { row[FIELD_TYPE] = "AM"; } else if (empLeaveApplication.LeaveAppUnit.Equals("P")) { row[FIELD_TYPE] = "PM"; } row[FIELD_MEDICIAL_CERTIFICATE] = empLeaveApplication.LeaveAppHasMedicalCertificate ? "Yes" : "No"; row[FIELD_REMARK] = empLeaveApplication.LeaveAppRemark; row[FIELD_NOPAYROLLPROCESS] = empLeaveApplication.LeaveAppNoPayProcess?"Yes":"No"; if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = empLeaveApplication.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public virtual DataTable UploadToTempDatabase(DataTable rawDataTable, int UserID) { if (rawDataTable == null) { return(GetImportDataFromTempDatabase(null)); } int rowCount = 1; foreach (DataRow row in rawDataTable.Rows) { if (rawDataTable == null) { return(GetImportDataFromTempDatabase(null)); } rowCount++; EUploadEmpRecurringPayment uploadEmpPay = new EUploadEmpRecurringPayment(); EEmpRecurringPayment lastEmpPay = null; string EmpNo = row[FIELD_EMP_NO].ToString().Trim(); uploadEmpPay.EmpID = Parse.GetEmpID(dbConn, EmpNo, UserID); if (uploadEmpPay.EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } try { uploadEmpPay.EmpRPEffFr = Parse.toDateTimeObject(row[FIELD_FROM]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_FROM + "=" + row[FIELD_FROM].ToString(), EmpNo, rowCount.ToString() }); } try { uploadEmpPay.EmpRPEffTo = Parse.toDateTimeObject(row[FIELD_TO]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_TO + "=" + row[FIELD_TO].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpPay.PayCodeID = Parse.GetPaymentCodeID(dbConn, row[FIELD_PAYMENT_CODE].ToString()); if (uploadEmpPay.PayCodeID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + row[FIELD_PAYMENT_CODE].ToString(), EmpNo, rowCount.ToString() }); } double amount = 0; if (double.TryParse(row[FIELD_AMOUNT].ToString(), out amount)) { uploadEmpPay.EmpRPAmount = amount; } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_AMOUNT + "=" + row[FIELD_AMOUNT].ToString(), EmpNo, rowCount.ToString() }); } if (ESystemParameter.getParameter(dbConn, ESystemParameter.PARAM_CODE_PAYSCALE_POINT_SYSTEM) == "Y") { if (amount > 0) { decimal m_point = 0; if (decimal.TryParse(row[FIELD_POINT].ToString(), out m_point)) { uploadEmpPay.Point = m_point; } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_POINT + "=" + row[FIELD_POINT].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpPay.SchemeCode = row[FIELD_SCHEME_CODE].ToString().Trim(); if (uploadEmpPay.SchemeCode == "") { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_SCHEME_CODE + "=" + row[FIELD_SCHEME_CODE].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpPay.Capacity = row[FIELD_CAPACITY].ToString().Trim(); if (uploadEmpPay.Capacity == "") { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_CAPACITY + "=" + row[FIELD_CAPACITY].ToString(), EmpNo, rowCount.ToString() }); } } } if (ESystemParameter.getParameter(dbConn, ESystemParameter.PARAM_CODE_MONTHLY_ACHIEVEMENT_COMMISSION) == "Y") // for F&V { decimal m_fps; decimal m_basicSalary; if (decimal.TryParse(row[FIELD_FPS].ToString(), out m_fps)) { uploadEmpPay.EmpRPFPS = System.Convert.ToDouble(m_fps); } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_FPS + "=" + row[FIELD_FPS].ToString(), EmpNo, rowCount.ToString() }); } if (decimal.TryParse(row[FIELD_BASIC_SALARY].ToString(), out m_basicSalary)) { uploadEmpPay.EmpRPBasicSalary = System.Convert.ToDouble(m_basicSalary); } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_BASIC_SALARY + "=" + row[FIELD_BASIC_SALARY].ToString(), EmpNo, rowCount.ToString() }); } } uploadEmpPay.CurrencyID = HROne.Lib.ExchangeCurrency.DefaultCurrency(); uploadEmpPay.EmpRPUnit = Parse.toRecurringPaymentUnit(row[FIELD_UNIT].ToString()); uploadEmpPay.EmpRPMethod = Parse.toPaymentMethodCode(row[FIELD_METHOD].ToString()); string BankCode = row[FIELD_ACCOUNT_NO].ToString(); uploadEmpPay.EmpAccID = HROne.Import.Parse.GetEmpAccID(dbConn, BankCode, uploadEmpPay.EmpID); uploadEmpPay.UploadEmpID = ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID); if (uploadEmpPay.UploadEmpID == 0) { if (uploadEmpPay.EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else { uploadEmpPay.UploadEmpID = ImportEmpPersonalInfoProcess.CreateDummyUploadEmployeeInfo(dbConn, uploadEmpPay.EmpID, m_SessionID, UploadDateTime); } } // Check if the bank account no does not exist in database if (uploadEmpPay.EmpAccID == 0 && !BankCode.Trim().Equals(string.Empty)) { uploadEmpPay.UploadEmpAccID = ParseTemp.GetUploadEmpAccID(dbConn, BankCode, uploadEmpPay.UploadEmpID); if (uploadEmpPay.UploadEmpAccID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_ACCOUNT_NO + "=" + BankCode, EmpNo, rowCount.ToString() }); } } if ((uploadEmpPay.EmpAccID > 0 || uploadEmpPay.UploadEmpAccID > 0) && !uploadEmpPay.EmpRPMethod.Equals("A")) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_METHOD + "=" + row[FIELD_METHOD].ToString(), EmpNo, rowCount.ToString() }); } if (uploadEmpPay.EmpRPMethod.Equals("A") && uploadEmpPay.EmpAccID == 0 && uploadEmpPay.UploadEmpAccID == 0) { EUploadEmpBankAccount uploadBankAccount = EUploadEmpBankAccount.GetDefaultBankAccount(dbConn, uploadEmpPay.EmpID, ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID), m_SessionID); if (uploadBankAccount != null) { //if (uploadBankAccount.EmpBankAccountID > 0) // uploadEmpPay.EmpAccID = uploadBankAccount.EmpBankAccountID; //else // uploadEmpPay.UploadEmpAccID = uploadBankAccount.UploadEmpBankAccountID; } else if (uploadEmpPay.EmpID > 0) { EEmpBankAccount bankAccount = EEmpBankAccount.GetDefaultBankAccount(dbConn, uploadEmpPay.EmpID); if (bankAccount != null) { //uploadEmpPay.EmpAccID = bankAccount.EmpBankAccountID; } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_ACCOUNT_NO + "=" + BankCode, EmpNo, rowCount.ToString() }); } } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_ACCOUNT_NO + "=" + BankCode, EmpNo, rowCount.ToString() }); } } if (rawDataTable.Columns.Contains(FIELD_IS_NON_PAYROLL_ITEM)) { uploadEmpPay.EmpRPIsNonPayrollItem = row[FIELD_IS_NON_PAYROLL_ITEM].ToString().Equals("Yes", StringComparison.CurrentCultureIgnoreCase) || row[FIELD_IS_NON_PAYROLL_ITEM].ToString().Equals("Y", StringComparison.CurrentCultureIgnoreCase); } if (rawDataTable.Columns.Contains(FIELD_COST_CENTER)) { string CostCenter = row[FIELD_COST_CENTER].ToString(); if (!string.IsNullOrEmpty(CostCenter)) { uploadEmpPay.CostCenterID = HROne.Import.Parse.GetCostCenterID(dbConn, CostCenter, false, UserID); if (uploadEmpPay.CostCenterID <= 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_COST_CENTER + "=" + CostCenter, EmpNo, rowCount.ToString() }); } } else { uploadEmpPay.CostCenterID = 0; } } uploadEmpPay.EmpRPRemark = row[FIELD_REMARK].ToString().Trim(); uploadEmpPay.SessionID = m_SessionID; uploadEmpPay.TransactionDate = UploadDateTime; if (uploadEmpPay.EmpID != 0 && errors.List.Count <= 0) { //if (rawDataTable.Columns.Contains(FIELD_INTERNAL_ID)) //{ // try // { // if (!row.IsNull(FIELD_INTERNAL_ID)) // { // int tmpID = FromHexDecWithCheckDigit((string)row[FIELD_INTERNAL_ID]); // EEmpRecurringPayment tmpObj = new EEmpRecurringPayment(); // tmpObj.EmpRPID = tmpID; // if (EEmpRecurringPayment.db.select(dbConn, tmpObj)) // uploadEmpPay.EmpRPID = tmpID; // else // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } // } // } // catch (Exception ex) // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } //} if (rawDataTable.Columns.Contains(FIELD_SYNC_ID)) { if (!row.IsNull(FIELD_SYNC_ID)) { string strSynID = row[FIELD_SYNC_ID].ToString(); uploadEmpPay.SynID = strSynID; if (!string.IsNullOrEmpty(strSynID)) { DBFilter synIDFilter = new DBFilter(); synIDFilter.add(new Match("SynID", strSynID)); ArrayList objSameSynIDList = EEmpRecurringPayment.db.select(dbConn, synIDFilter); if (objSameSynIDList.Count > 0) { uploadEmpPay.EmpRPID = ((EEmpRecurringPayment)objSameSynIDList[0]).EmpRPID; } } } } if (uploadEmpPay.EmpRPID == 0) { AND andTerms = new AND(); andTerms.add(new Match("EmpRPEffFr", "<=", uploadEmpPay.EmpRPEffFr)); andTerms.add(new Match("PayCodeID", uploadEmpPay.PayCodeID)); lastEmpPay = (EEmpRecurringPayment)AppUtils.GetLastObj(dbConn, uploadDB, "EmpRPEffFr", uploadEmpPay.EmpID, andTerms); if (lastEmpPay != null) { if (uploadEmpPay.EmpAccID == lastEmpPay.EmpAccID && Math.Abs(uploadEmpPay.EmpRPAmount - lastEmpPay.EmpRPAmount) < 0.01 && uploadEmpPay.EmpRPMethod == lastEmpPay.EmpRPMethod && uploadEmpPay.EmpRPUnit == lastEmpPay.EmpRPUnit && uploadEmpPay.PayCodeID == lastEmpPay.PayCodeID && uploadEmpPay.EmpRPEffFr == lastEmpPay.EmpRPEffFr && uploadEmpPay.EmpRPEffTo == lastEmpPay.EmpRPEffTo && uploadEmpPay.SchemeCode == lastEmpPay.SchemeCode && uploadEmpPay.Capacity == lastEmpPay.Capacity && uploadEmpPay.Point == lastEmpPay.Point && uploadEmpPay.CostCenterID == lastEmpPay.CostCenterID && uploadEmpPay.EmpRPRemark == lastEmpPay.EmpRPRemark && uploadEmpPay.EmpRPIsNonPayrollItem == lastEmpPay.EmpRPIsNonPayrollItem ) { continue; } else { // add postion terms with new ID if (lastEmpPay.EmpRPEffFr.Equals(uploadEmpPay.EmpRPEffFr)) { uploadEmpPay.EmpRPID = lastEmpPay.EmpRPID; if (uploadEmpPay.EmpRPEffTo.Ticks == 0 && lastEmpPay.EmpRPEffTo.Ticks != 0) { AND andNextTerms = new AND(); andNextTerms.add(new Match("EmpRPEffFr", ">", lastEmpPay.EmpRPEffTo)); andNextTerms.add(new Match("PayCodeID", uploadEmpPay.PayCodeID)); EEmpRecurringPayment afterEmpRP = (EEmpRecurringPayment)AppUtils.GetLastObj(dbConn, uploadDB, "EmpRPEffFr", uploadEmpPay.EmpID, andNextTerms); if (afterEmpRP != null) { uploadEmpPay.EmpRPEffTo = afterEmpRP.EmpRPEffFr.AddDays(-1); } } } else { AND lastObjAndTerms = new AND(); lastObjAndTerms.add(new Match("EmpRPEffFr", ">", uploadEmpPay.EmpRPEffFr)); if (!uploadEmpPay.EmpRPEffTo.Ticks.Equals(0)) { lastObjAndTerms.add(new Match("EmpRPEffFr", "<=", uploadEmpPay.EmpRPEffTo)); } lastObjAndTerms.add(new Match("PayCodeID", uploadEmpPay.PayCodeID)); EEmpRecurringPayment lastObj = (EEmpRecurringPayment)AppUtils.GetLastObj(dbConn, uploadDB, "EmpRPEffFr", uploadEmpPay.EmpID, lastObjAndTerms); if (lastObj != null) { if (!lastObj.EmpRPEffTo.Ticks.Equals(0)) { errors.addError(ImportErrorMessage.ERROR_DATE_FROM_OVERLAP, new string[] { uploadEmpPay.EmpRPEffFr.ToString("yyyy-MM-dd"), rowCount.ToString() }); continue; } } } } } } } if (uploadEmpPay.EmpRPID <= 0) { uploadEmpPay.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT; } else { uploadEmpPay.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; } Hashtable values = new Hashtable(); tempDB.populate(uploadEmpPay, values); PageErrors pageErrors = new PageErrors(tempDB); tempDB.validate(pageErrors, values); if (pageErrors.errors.Count == 0) { tempDB.insert(dbConn, uploadEmpPay); } else { pageErrors.addError(rawDataTable.TableName); throw new HRImportException(pageErrors.getPrompt() + "(line " + rowCount.ToString() + ")"); } } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(rawDataTable.TableName + "\r\n" + errors.Message())); } return(GetImportDataFromTempDatabase(null)); }
protected void Upload_Click(object sender, EventArgs e) { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); if (EmpImportFile.HasFile) { //DataTable dataTable = HROne.CSVProcess.CSVReader.parse(CNDImportFile.PostedFile.InputStream); string strTmpFolder = HROne.Common.Folder.GetOrCreateSessionTempFolder(Session.SessionID).FullName;; //Environment.GetFolderPath(Environment.SpecialFolder.InternetCache); string strTmpFile = System.IO.Path.Combine(strTmpFolder, AppUtils.ServerDateTime().ToString("~yyyyMMddHHmmss_") + EmpImportFile.FileName); EmpImportFile.SaveAs(strTmpFile); if (FileFormatList.SelectedValue.Equals("EZ-Pay", StringComparison.CurrentCultureIgnoreCase)) { // Not supported } else { ImportEmpPersonalInfoProcess empImport = new ImportEmpPersonalInfoProcess(dbConn, Session.SessionID); //DataTable dataTable = HROne.Import.ExcelImport.parse(strTmpFile); //using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\csv\;Extended Properties='Text;'")) WebUtils.StartFunction(Session, FUNCTION_CODE); //dbConn.BeginTransaction(); try { DataTable table = empImport.UploadToTempDatabase(strTmpFile, WebUtils.GetCurUser(Session).UserID, ZipPassword.Text, chkAutoCreateCode.Checked); table = WebUtils.DataTableSortingAndPaging(table, info); //Repeater.DataSource = table; //Repeater.DataBind(); ImportSection.Visible = true; } catch (HRImportException ex) { //if (empImport.errors.List.Count > 0) // foreach (string errorString in empImport.errors.List) // errors.addError(errorString); //else errors.addError(ex.Message); } catch (Exception ex) { errors.addError(ex.ToString()); } if (errors.isEmpty()) { //dbConn.CommitTransaction(); } else { //dbConn.RollbackTransaction(); empImport.ClearTempTable(); } WebUtils.EndFunction(dbConn); } try { System.IO.File.Delete(strTmpFile); } catch { } } else { errors.addError(HROne.Translation.PageErrorMessage.ERROR_INVALID_FILE); } }
public DataTable UploadToTempDatabase(DataTable rawDataTable, int UserID, bool CreateCodeIfNotExists) { if (rawDataTable == null) { return(GetImportDataFromTempDatabase(null)); } int rowCount = 1; foreach (DataRow row in rawDataTable.Rows) { rowCount++; EUploadEmpQualification uploadEmpQualification = new EUploadEmpQualification(); //EEmpQualification lastEmpQualification = null; string EmpNo = row[FIELD_EMP_NO].ToString().Trim(); uploadEmpQualification.EmpID = Parse.GetEmpID(dbConn, EmpNo, UserID); if (uploadEmpQualification.EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } try { uploadEmpQualification.EmpQualificationFrom = Parse.toDateTimeObject(row[FIELD_FROM]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_FROM + "=" + row[FIELD_FROM].ToString(), EmpNo, rowCount.ToString() }); } try { uploadEmpQualification.EmpQualificationTo = Parse.toDateTimeObject(row[FIELD_TO]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_TO + "=" + row[FIELD_TO].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpQualification.QualificationID = Parse.GetQualificationID(dbConn, row[FIELD_QUALIFICATION].ToString(), CreateCodeIfNotExists, UserID); if (uploadEmpQualification.QualificationID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_QUALIFICATION + "=" + row[FIELD_QUALIFICATION].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpQualification.EmpQualificationInstitution = row[FIELD_INSTITUTION].ToString(); if (rawDataTable.Columns.Contains(FIELD_LEARNING_METHOD)) { uploadEmpQualification.EmpQualificationLearningMethod = Import.Parse.toQualificationLearningMethod(row[FIELD_LEARNING_METHOD].ToString()); } uploadEmpQualification.EmpQualificationRemark = row[FIELD_REMARK].ToString().Trim(); uploadEmpQualification.SessionID = m_SessionID; uploadEmpQualification.TransactionDate = UploadDateTime; // Remove the checking. Allow to more than 1 QualificationID per record. //if (uploadEmpQualification.EmpID != 0 && !uploadEmpQualification.EmpQualificationFrom.Ticks.Equals(0)) //{ // AND andTerms = new AND(); // andTerms.add(new Match("EmpQualificationFrom", "<=", uploadEmpQualification.EmpQualificationFrom)); // andTerms.add(new Match("QualificationID", uploadEmpQualification.QualificationID)); // lastEmpQualification = (EEmpQualification)AppUtils.GetLastObj(dbConn, uploadDB, "EmpQualificationFrom", uploadEmpQualification.EmpID, andTerms); // if (lastEmpQualification != null) // { // if (uploadEmpQualification.EmpQualificationInstitution == lastEmpQualification.EmpQualificationInstitution // && uploadEmpQualification.EmpQualificationRemark == lastEmpQualification.EmpQualificationRemark // ) // continue; // else // { // // add postion terms with new ID // if (lastEmpQualification.EmpQualificationFrom.Equals(uploadEmpQualification.EmpQualificationFrom)) // { // uploadEmpQualification.EmpQualificationID = lastEmpQualification.EmpQualificationID; // if (uploadEmpQualification.EmpQualificationTo.Ticks == 0) // uploadEmpQualification.EmpQualificationTo = lastEmpQualification.EmpQualificationTo; // } // else // { // AND lastObjAndTerms = new AND(); // lastObjAndTerms.add(new Match("EmpQualificationFrom", "<=", uploadEmpQualification.EmpQualificationFrom)); // lastObjAndTerms.add(new Match("QualificationID", uploadEmpQualification.QualificationID)); // EEmpQualification lastObj = (EEmpQualification)AppUtils.GetLastObj(dbConn, uploadDB, "EmpQualificationFrom", uploadEmpQualification.EmpID, lastObjAndTerms); // if (lastObj != null && uploadEmpQualification.EmpQualificationFrom <= lastObj.EmpQualificationFrom) // { // errors.addError(ImportErrorMessage.ERROR_DATE_FROM_OVERLAP, new string[] { uploadEmpQualification.EmpQualificationFrom.ToString("yyyy-MM-dd"), rowCount.ToString() }); // continue; // } // } // } // } //} //if (rawDataTable.Columns.Contains(FIELD_INTERNAL_ID)) //{ // try // { // if (!row.IsNull(FIELD_INTERNAL_ID)) // { // int tmpID = FromHexDecWithCheckDigit((string)row[FIELD_INTERNAL_ID]); // EEmpQualification tmpObj = new EEmpQualification(); // tmpObj.EmpQualificationID = tmpID; // if (EEmpQualification.db.select(dbConn, tmpObj)) // uploadEmpQualification.EmpQualificationID = tmpID; // else // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } // } // } // catch (Exception ex) // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } //} if (rawDataTable.Columns.Contains(FIELD_SYNC_ID)) { if (!row.IsNull(FIELD_SYNC_ID)) { string strSynID = row[FIELD_SYNC_ID].ToString(); uploadEmpQualification.SynID = strSynID; if (!string.IsNullOrEmpty(strSynID)) { DBFilter synIDFilter = new DBFilter(); synIDFilter.add(new Match("SynID", strSynID)); ArrayList objSameSynIDList = EEmpQualification.db.select(dbConn, synIDFilter); if (objSameSynIDList.Count > 0) { uploadEmpQualification.EmpQualificationID = ((EEmpQualification)objSameSynIDList[0]).EmpQualificationID; } } } } if (uploadEmpQualification.EmpQualificationID <= 0) { uploadEmpQualification.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT; } else { uploadEmpQualification.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; } uploadEmpQualification.UploadEmpID = ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID); if (uploadEmpQualification.UploadEmpID == 0) { if (uploadEmpQualification.EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else { uploadEmpQualification.UploadEmpID = ImportEmpPersonalInfoProcess.CreateDummyUploadEmployeeInfo(dbConn, uploadEmpQualification.EmpID, m_SessionID, UploadDateTime); } } Hashtable values = new Hashtable(); tempDB.populate(uploadEmpQualification, values); PageErrors pageErrors = new PageErrors(tempDB); tempDB.validate(pageErrors, values); if (pageErrors.errors.Count == 0) { tempDB.insert(dbConn, uploadEmpQualification); } else { pageErrors.addError(rawDataTable.TableName); throw new HRImportException(pageErrors.getPrompt() + "(line " + rowCount.ToString() + ")"); } } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(rawDataTable.TableName + "\r\n" + errors.Message())); } return(GetImportDataFromTempDatabase(null)); }
public DataTable UploadToTempDatabase(DataTable rawDataTable, int UserID, bool CreateCodeIfNotExists) { if (rawDataTable == null) { return(GetImportDataFromTempDatabase(null)); } int rowCount = 1; foreach (DataRow row in rawDataTable.Rows) { rowCount++; EUploadEmpCostCenter uploadEmpCostCenter = new EUploadEmpCostCenter(); ArrayList uploadEmpCostCenterDetailList = new ArrayList(); ArrayList lastEmpCostCenterDetailList = null; string EmpNo = row[FIELD_EMP_NO].ToString().Trim(); uploadEmpCostCenter.EmpID = Parse.GetEmpID(dbConn, EmpNo, UserID); if (uploadEmpCostCenter.EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } try { uploadEmpCostCenter.EmpCostCenterEffFr = Parse.toDateTimeObject(row[FIELD_FROM]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_FROM + "=" + row[FIELD_FROM].ToString(), EmpNo, rowCount.ToString() }); } try { uploadEmpCostCenter.EmpCostCenterEffTo = Parse.toDateTimeObject(row[FIELD_TO]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_TO + "=" + row[FIELD_TO].ToString(), EmpNo, rowCount.ToString() }); } int iFieldCount = 1; double totalPercentage = 0; while (row.Table.Columns.Contains(FIELD_COST_CENTER + iFieldCount) && row.Table.Columns.Contains(FIELD_PERCENTAGE + iFieldCount)) { if (!row.IsNull(FIELD_COST_CENTER + iFieldCount) && !row.IsNull(FIELD_PERCENTAGE + iFieldCount)) { if (!string.IsNullOrEmpty(row[FIELD_COST_CENTER + iFieldCount].ToString().Trim()) && !string.IsNullOrEmpty(row[FIELD_PERCENTAGE + iFieldCount].ToString().Trim())) { EUploadEmpCostCenterDetail uploadEmpCostCenterDetail = new EUploadEmpCostCenterDetail(); uploadEmpCostCenterDetail.CostCenterID = Parse.GetCostCenterID(dbConn, row[FIELD_COST_CENTER + iFieldCount].ToString(), CreateCodeIfNotExists, UserID); try { uploadEmpCostCenterDetail.EmpCostCenterPercentage = double.Parse(row[FIELD_PERCENTAGE + iFieldCount].ToString().Trim()); if (uploadEmpCostCenterDetail.CostCenterID > 0) { totalPercentage += uploadEmpCostCenterDetail.EmpCostCenterPercentage; uploadEmpCostCenterDetailList.Add(uploadEmpCostCenterDetail); } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_COST_CENTER + iFieldCount + "=" + row[FIELD_COST_CENTER + iFieldCount].ToString(), EmpNo, rowCount.ToString() }); } } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PERCENTAGE + iFieldCount + "=" + row[FIELD_PERCENTAGE + iFieldCount].ToString(), EmpNo, rowCount.ToString() }); } } } iFieldCount++; } if (Math.Abs(Math.Round(totalPercentage, 2) - 100) >= 0.01) { errors.addError(ImportErrorMessage.ERROR_TOTAL_PERCENTAGE_NOT_100, new string[] { rowCount.ToString() }); } uploadEmpCostCenter.SessionID = m_SessionID; uploadEmpCostCenter.TransactionDate = UploadDateTime; if (uploadEmpCostCenter.EmpID != 0 && errors.List.Count <= 0) { if (rawDataTable.Columns.Contains(FIELD_SYNC_ID)) { if (!row.IsNull(FIELD_SYNC_ID)) { string strSynID = row[FIELD_SYNC_ID].ToString(); uploadEmpCostCenter.SynID = strSynID; if (!string.IsNullOrEmpty(strSynID)) { DBFilter synIDFilter = new DBFilter(); synIDFilter.add(new Match("SynID", strSynID)); ArrayList objSameSynIDList = EEmpCostCenter.db.select(dbConn, synIDFilter); if (objSameSynIDList.Count > 0) { uploadEmpCostCenter.EmpCostCenterID = ((EEmpCostCenter)objSameSynIDList[0]).EmpCostCenterID; } } if (uploadEmpCostCenter.EmpCostCenterID != 0) { DBFilter empCostCenterSetailFilter = new DBFilter(); empCostCenterSetailFilter.add(new Match("EmpCostCenterID", uploadEmpCostCenter.EmpCostCenterID)); lastEmpCostCenterDetailList = EEmpCostCenterDetail.db.select(dbConn, empCostCenterSetailFilter); bool IsSameDetail = true; if ( lastEmpCostCenterDetailList.Count == uploadEmpCostCenterDetailList.Count && uploadEmpCostCenter.EmpCostCenterEffFr == uploadEmpCostCenter.EmpCostCenterEffFr && uploadEmpCostCenter.EmpCostCenterEffTo == uploadEmpCostCenter.EmpCostCenterEffTo ) { foreach (EEmpCostCenterDetail empCostCenterDetail in lastEmpCostCenterDetailList) { foreach (EUploadEmpCostCenterDetail uploadEmpCostCenterDetail in uploadEmpCostCenterDetailList) { if (uploadEmpCostCenterDetail.CostCenterID == empCostCenterDetail.CostCenterID) { if (uploadEmpCostCenterDetail.EmpCostCenterPercentage - empCostCenterDetail.EmpCostCenterPercentage >= 0.01) { uploadEmpCostCenterDetail.EmpCostCenterDetailID = empCostCenterDetail.EmpCostCenterDetailID; IsSameDetail = false; } else { uploadEmpCostCenterDetail.EmpCostCenterID = empCostCenterDetail.EmpCostCenterID; uploadEmpCostCenterDetail.EmpCostCenterDetailID = empCostCenterDetail.EmpCostCenterDetailID; } break; } if (uploadEmpCostCenterDetailList[uploadEmpCostCenterDetailList.Count - 1] == uploadEmpCostCenterDetail) { IsSameDetail = false; } } } } else { IsSameDetail = false; } if (IsSameDetail) { continue; } else { foreach (EUploadEmpCostCenterDetail uploadEmpCostCenterDetail in uploadEmpCostCenterDetailList) { uploadEmpCostCenterDetail.EmpCostCenterID = uploadEmpCostCenter.EmpCostCenterID; //uploadEmpHierarchy.EmpHierarchyID = empHierarchy.EmpHierarchyID; } } } } } if (uploadEmpCostCenter.EmpCostCenterID == 0) { AND andTerms = new AND(); andTerms.add(new Match("EmpCostCenterEffFr", "<=", uploadEmpCostCenter.EmpCostCenterEffFr)); EEmpCostCenter lastEmpCostCenter = (EEmpCostCenter)AppUtils.GetLastObj(dbConn, uploadDB, "EmpCostCenterEffFr", uploadEmpCostCenter.EmpID, andTerms); if (lastEmpCostCenter != null) { DBFilter empCostCenterSetailFilter = new DBFilter(); empCostCenterSetailFilter.add(new Match("EmpCostCenterID", lastEmpCostCenter.EmpCostCenterID)); lastEmpCostCenterDetailList = EEmpCostCenterDetail.db.select(dbConn, empCostCenterSetailFilter); bool IsSameDetail = true; if ( lastEmpCostCenterDetailList.Count == uploadEmpCostCenterDetailList.Count && uploadEmpCostCenter.EmpCostCenterEffFr == lastEmpCostCenter.EmpCostCenterEffFr && uploadEmpCostCenter.EmpCostCenterEffTo == lastEmpCostCenter.EmpCostCenterEffTo ) { foreach (EEmpCostCenterDetail empCostCenterDetail in lastEmpCostCenterDetailList) { foreach (EUploadEmpCostCenterDetail uploadEmpCostCenterDetail in uploadEmpCostCenterDetailList) { if (uploadEmpCostCenterDetail.CostCenterID == empCostCenterDetail.CostCenterID) { if (uploadEmpCostCenterDetail.EmpCostCenterPercentage - empCostCenterDetail.EmpCostCenterPercentage >= 0.01) { uploadEmpCostCenterDetail.EmpCostCenterDetailID = empCostCenterDetail.EmpCostCenterDetailID; IsSameDetail = false; } else { uploadEmpCostCenterDetail.EmpCostCenterID = empCostCenterDetail.EmpCostCenterID; uploadEmpCostCenterDetail.EmpCostCenterDetailID = empCostCenterDetail.EmpCostCenterDetailID; } break; } if (uploadEmpCostCenterDetailList[uploadEmpCostCenterDetailList.Count - 1] == uploadEmpCostCenterDetail) { IsSameDetail = false; } } } } else { IsSameDetail = false; } if (IsSameDetail) { continue; } else { // add postion terms with new ID if (lastEmpCostCenter.EmpCostCenterEffFr.Equals(uploadEmpCostCenter.EmpCostCenterEffFr)) { uploadEmpCostCenter.EmpCostCenterID = lastEmpCostCenter.EmpCostCenterID; if (uploadEmpCostCenter.EmpCostCenterEffTo.Ticks == 0 && lastEmpCostCenter.EmpCostCenterEffTo.Ticks != 0) { EEmpCostCenter afterEmpCostCenter = (EEmpCostCenter)AppUtils.GetLastObj(dbConn, uploadDB, "EmpCostCenterEffFr", uploadEmpCostCenter.EmpID, new Match("EmpCostCenterEffFr", ">", lastEmpCostCenter.EmpCostCenterEffTo)); if (afterEmpCostCenter != null) { uploadEmpCostCenter.EmpCostCenterEffTo = afterEmpCostCenter.EmpCostCenterEffFr.AddDays(-1); } } foreach (EUploadEmpCostCenterDetail uploadEmpCostCenterDetail in uploadEmpCostCenterDetailList) { uploadEmpCostCenterDetail.EmpCostCenterID = uploadEmpCostCenter.EmpCostCenterID; //uploadEmpHierarchy.EmpHierarchyID = empHierarchy.EmpHierarchyID; } } else { AND lastObjAndTerms = new AND(); lastObjAndTerms.add(new Match("EmpCostCenterEffFr", ">", uploadEmpCostCenter.EmpCostCenterEffFr)); if (!uploadEmpCostCenter.EmpCostCenterEffTo.Ticks.Equals(0)) { lastObjAndTerms.add(new Match("EmpCostCenterEffFr", "<=", uploadEmpCostCenter.EmpCostCenterEffTo)); } EEmpCostCenter lastObj = (EEmpCostCenter)AppUtils.GetLastObj(dbConn, uploadDB, "EmpCostCenterEffFr", uploadEmpCostCenter.EmpID, lastObjAndTerms); if (lastObj != null) { if (!lastObj.EmpCostCenterEffTo.Ticks.Equals(0)) { errors.addError(ImportErrorMessage.ERROR_DATE_FROM_OVERLAP, new string[] { uploadEmpCostCenter.EmpCostCenterEffFr.ToString("yyyy-MM-dd"), rowCount.ToString() }); continue; } } } } } } } if (uploadEmpCostCenter.EmpCostCenterID <= 0) { uploadEmpCostCenter.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT; } else { uploadEmpCostCenter.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; } uploadEmpCostCenter.UploadEmpID = ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID); if (uploadEmpCostCenter.UploadEmpID == 0) { if (uploadEmpCostCenter.EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else { uploadEmpCostCenter.UploadEmpID = ImportEmpPersonalInfoProcess.CreateDummyUploadEmployeeInfo(dbConn, uploadEmpCostCenter.EmpID, m_SessionID, UploadDateTime); } } Hashtable values = new Hashtable(); tempDB.populate(uploadEmpCostCenter, values); PageErrors pageErrors = new PageErrors(EUploadEmpPersonalInfo.db); tempDB.validate(pageErrors, values); if (pageErrors.errors.Count == 0) { tempDB.insert(dbConn, uploadEmpCostCenter); foreach (EUploadEmpCostCenterDetail uploadCostCenterDetail in uploadEmpCostCenterDetailList) { uploadCostCenterDetail.UploadEmpCostCenterID = uploadEmpCostCenter.UploadEmpCostCenterID; tempDetailDB.insert(dbConn, uploadCostCenterDetail); } } else { pageErrors.addError(rawDataTable.TableName); throw new HRImportException(pageErrors.getPrompt() + "(line " + rowCount.ToString() + ")"); //if (EmpID == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); //else if (PayCodeID == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + PaymentCode, EmpNo, rowCount.ToString() }); //else if (PayCodeID == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + PaymentCode, EmpNo, rowCount.ToString() }); //else if (EffDate.Ticks == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_EFFECTIVE_DATE + "=" + EffDateString, EmpNo, rowCount.ToString() }); //else if (double.TryParse(amountString, out amount)) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_AMOUNT + "=" + amountString, EmpNo, rowCount.ToString() }); } } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(rawDataTable.TableName + "\r\n" + errors.Message())); } return(GetImportDataFromTempDatabase(null)); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsShowDescription, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_QUALIFICATION, typeof(string)); tmpDataTable.Columns.Add(FIELD_INSTITUTION, typeof(string)); tmpDataTable.Columns.Add(FIELD_LEARNING_METHOD, typeof(string)); tmpDataTable.Columns.Add(FIELD_REMARK, typeof(string)); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpQualification.db.select(dbConn, filter); foreach (EEmpQualification empQualification in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(empQualification.EmpQualificationID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_FROM] = empQualification.EmpQualificationFrom; row[FIELD_TO] = empQualification.EmpQualificationTo; EQualification qualification = new EQualification(); qualification.QualificationID = empQualification.QualificationID; if (EQualification.db.select(dbConn, qualification)) { row[FIELD_QUALIFICATION] = IsShowDescription ? qualification.QualificationDesc : qualification.QualificationCode; } row[FIELD_INSTITUTION] = empQualification.EmpQualificationInstitution; if (!string.IsNullOrEmpty(empQualification.EmpQualificationLearningMethod)) { if (empQualification.EmpQualificationLearningMethod.Equals(EEmpQualification.LEARNING_METHOD_CODE_ONCAMPUS)) { row[FIELD_LEARNING_METHOD] = EEmpQualification.LEARNING_METHOD_DESC_ONCAMPUS; } else if (empQualification.EmpQualificationLearningMethod.Equals(EEmpQualification.LEARNING_METHOD_CODE_DISTANCE_LEARNING)) { row[FIELD_LEARNING_METHOD] = EEmpQualification.LEARNING_METHOD_DESC_DISTANCE_LEARNING; } } row[FIELD_REMARK] = empQualification.EmpQualificationRemark; if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = empQualification.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public DataTable UploadToTempDatabase(DataTable rawDataTable, int UserID) { if (rawDataTable == null) { return(GetImportDataFromTempDatabase(null)); } decimal m_share; int rowCount = 1; foreach (DataRow row in rawDataTable.Rows) { rowCount++; EUploadEmpBeneficiaries uploadEmpBeneficiaries = new EUploadEmpBeneficiaries(); string EmpNo = row[FIELD_EMP_NO].ToString().Trim(); uploadEmpBeneficiaries.EmpID = Parse.GetEmpID(dbConn, EmpNo, UserID); if (uploadEmpBeneficiaries.EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } if (rawDataTable.Columns.Contains(FIELD_NAME)) { uploadEmpBeneficiaries.EmpBeneficiariesName = row[FIELD_NAME].ToString().Trim(); } if (decimal.TryParse(row[FIELD_SHARE].ToString(), out m_share)) { uploadEmpBeneficiaries.EmpBeneficiariesShare = System.Convert.ToDouble(m_share); } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_SHARE + "=" + row[FIELD_SHARE].ToString(), EmpNo, rowCount.ToString() }); } if (rawDataTable.Columns.Contains(FIELD_HKID)) { uploadEmpBeneficiaries.EmpBeneficiariesHKID = row[FIELD_HKID].ToString().Trim(); } if (rawDataTable.Columns.Contains(FIELD_RELATION)) { uploadEmpBeneficiaries.EmpBeneficiariesRelation = row[FIELD_RELATION].ToString().Trim(); } if (rawDataTable.Columns.Contains(FIELD_ADDRESS)) { uploadEmpBeneficiaries.EmpBeneficiariesAddress = row[FIELD_ADDRESS].ToString().Trim(); } if (rawDataTable.Columns.Contains(FIELD_DISTRICT)) { uploadEmpBeneficiaries.EmpBeneficiariesDistrict = row[FIELD_DISTRICT].ToString().Trim(); } if (rawDataTable.Columns.Contains(FIELD_AREA)) { uploadEmpBeneficiaries.EmpBeneficiariesArea = row[FIELD_AREA].ToString().Trim(); } if (rawDataTable.Columns.Contains(FIELD_COUNTRY)) { uploadEmpBeneficiaries.EmpBeneficiariesCountry = row[FIELD_COUNTRY].ToString().Trim(); } uploadEmpBeneficiaries.SessionID = m_SessionID; uploadEmpBeneficiaries.TransactionDate = UploadDateTime; if (uploadEmpBeneficiaries.EmpID != 0) { if (rawDataTable.Columns.Contains(FIELD_SYNC_ID)) { if (!row.IsNull(FIELD_SYNC_ID)) { string strSynID = row[FIELD_SYNC_ID].ToString(); uploadEmpBeneficiaries.SynID = strSynID; if (!string.IsNullOrEmpty(strSynID)) { DBFilter synIDFilter = new DBFilter(); synIDFilter.add(new Match("SynID", strSynID)); ArrayList objSameSynIDList = EEmpBeneficiaries.db.select(dbConn, synIDFilter); if (objSameSynIDList.Count > 0) { uploadEmpBeneficiaries.EmpBeneficiariesID = ((EEmpBeneficiaries)objSameSynIDList[0]).EmpBeneficiariesID; } } } } if (uploadEmpBeneficiaries.EmpBeneficiariesID == 0) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", uploadEmpBeneficiaries.EmpID)); ArrayList list = uploadDB.select(dbConn, filter); bool IsSameEntryExists = false; foreach (EEmpBeneficiaries currentEmpBeneficiaries in list) { if (uploadEmpBeneficiaries.EmpBeneficiariesHKID == currentEmpBeneficiaries.EmpBeneficiariesHKID) { uploadEmpBeneficiaries.EmpBeneficiariesID = currentEmpBeneficiaries.EmpBeneficiariesID; } if (uploadEmpBeneficiaries.EmpBeneficiariesName == currentEmpBeneficiaries.EmpBeneficiariesName && uploadEmpBeneficiaries.EmpBeneficiariesShare == currentEmpBeneficiaries.EmpBeneficiariesShare && uploadEmpBeneficiaries.EmpBeneficiariesHKID == currentEmpBeneficiaries.EmpBeneficiariesHKID && uploadEmpBeneficiaries.EmpBeneficiariesRelation == currentEmpBeneficiaries.EmpBeneficiariesRelation && uploadEmpBeneficiaries.EmpBeneficiariesAddress == currentEmpBeneficiaries.EmpBeneficiariesAddress && uploadEmpBeneficiaries.EmpBeneficiariesDistrict == currentEmpBeneficiaries.EmpBeneficiariesDistrict && uploadEmpBeneficiaries.EmpBeneficiariesArea == currentEmpBeneficiaries.EmpBeneficiariesArea && uploadEmpBeneficiaries.EmpBeneficiariesCountry == currentEmpBeneficiaries.EmpBeneficiariesCountry ) { IsSameEntryExists = true; break; } } if (IsSameEntryExists) { continue; } } } if (uploadEmpBeneficiaries.EmpBeneficiariesID <= 0) { uploadEmpBeneficiaries.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT; } else { uploadEmpBeneficiaries.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; } uploadEmpBeneficiaries.UploadEmpID = ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID); if (uploadEmpBeneficiaries.UploadEmpID == 0) { if (uploadEmpBeneficiaries.EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else { uploadEmpBeneficiaries.UploadEmpID = ImportEmpPersonalInfoProcess.CreateDummyUploadEmployeeInfo(dbConn, uploadEmpBeneficiaries.EmpID, m_SessionID, UploadDateTime); } } Hashtable values = new Hashtable(); tempDB.populate(uploadEmpBeneficiaries, values); PageErrors pageErrors = new PageErrors(EUploadEmpPersonalInfo.db); tempDB.validate(pageErrors, values); if (pageErrors.errors.Count == 0) { tempDB.insert(dbConn, uploadEmpBeneficiaries); } else { pageErrors.addError(rawDataTable.TableName); throw new HRImportException(pageErrors.getPrompt() + "(line " + rowCount.ToString() + ")"); } } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(rawDataTable.TableName + "\r\n" + errors.Message())); } return(GetImportDataFromTempDatabase(null)); }
public DataTable UploadToTempDatabase(DataTable rawDataTable, int UserID) { if (rawDataTable == null) { return(GetImportDataFromTempDatabase(null)); } int rowCount = 1; foreach (DataRow row in rawDataTable.Rows) { rowCount++; EUploadEmpPlaceOfResidence uploadEmpPoR = new EUploadEmpPlaceOfResidence(); EEmpPlaceOfResidence lastEmpPoR = null; string EmpNo = row[FIELD_EMP_NO].ToString().Trim(); uploadEmpPoR.EmpID = Parse.GetEmpID(dbConn, EmpNo, UserID); if (uploadEmpPoR.EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } try { uploadEmpPoR.EmpPoRFrom = Parse.toDateTimeObject(row[FIELD_FROM]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_FROM + "=" + row[FIELD_FROM].ToString(), EmpNo, rowCount.ToString() }); } try { uploadEmpPoR.EmpPoRTo = Parse.toDateTimeObject(row[FIELD_TO]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_TO + "=" + row[FIELD_TO].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpPoR.EmpPoRNature = row[FIELD_NATURE].ToString(); uploadEmpPoR.EmpPoRLandLord = row[FIELD_LANDLORD].ToString(); uploadEmpPoR.EmpPoRPropertyAddr = row[FIELD_ADDRESS].ToString(); uploadEmpPoR.EmpPoRLandLordAddr = row[FIELD_LANDLORD_ADDRESS].ToString(); double amount = 0; if (double.TryParse(row[FIELD_ER_RENT_PAID].ToString(), out amount)) { uploadEmpPoR.EmpPoRPayToLandER = amount; } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_ER_RENT_PAID + "=" + row[FIELD_ER_RENT_PAID].ToString(), EmpNo, rowCount.ToString() }); } if (double.TryParse(row[FIELD_EE_RENT_PAID].ToString(), out amount)) { uploadEmpPoR.EmpPoRPayToLandEE = amount; } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_EE_RENT_PAID + "=" + row[FIELD_EE_RENT_PAID].ToString(), EmpNo, rowCount.ToString() }); } if (double.TryParse(row[FIELD_EE_RENT_REFUND].ToString(), out amount)) { uploadEmpPoR.EmpPoRRefundToEE = amount; } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_EE_RENT_REFUND + "=" + row[FIELD_EE_RENT_REFUND].ToString(), EmpNo, rowCount.ToString() }); } if (double.TryParse(row[FIELD_EE_RENT_PAID_TO_ER].ToString(), out amount)) { uploadEmpPoR.EmpPoRPayToERByEE = amount; } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_EE_RENT_PAID_TO_ER + "=" + row[FIELD_EE_RENT_PAID_TO_ER].ToString(), EmpNo, rowCount.ToString() }); } //uploadEmpPoR.EmpPoRPayToLandER = double.Parse(row[FIELD_ER_RENT_PAID].ToString()); //uploadEmpPoR.EmpPoRPayToLandEE = double.Parse(row[FIELD_EE_RENT_PAID].ToString()); //uploadEmpPoR.EmpPoRRefundToEE = double.Parse(row[FIELD_EE_RENT_REFUND].ToString()); //uploadEmpPoR.EmpPoRPayToERByEE = double.Parse(row[FIELD_EE_RENT_PAID_TO_ER].ToString()); uploadEmpPoR.SessionID = m_SessionID; uploadEmpPoR.TransactionDate = UploadDateTime; if (uploadEmpPoR.EmpID != 0) { AND andTerms = new AND(); andTerms.add(new Match("EmpPoRFrom", "<=", uploadEmpPoR.EmpPoRFrom)); lastEmpPoR = (EEmpPlaceOfResidence)AppUtils.GetLastObj(dbConn, uploadDB, "EmpPoRFrom", uploadEmpPoR.EmpID, andTerms); if (lastEmpPoR != null) { if (uploadEmpPoR.EmpPoRNature == lastEmpPoR.EmpPoRNature && uploadEmpPoR.EmpPoRLandLord == lastEmpPoR.EmpPoRLandLord && uploadEmpPoR.EmpPoRPropertyAddr == lastEmpPoR.EmpPoRPropertyAddr && uploadEmpPoR.EmpPoRLandLordAddr == lastEmpPoR.EmpPoRLandLordAddr && uploadEmpPoR.EmpPoRTo == lastEmpPoR.EmpPoRTo ) { continue; } else { // add postion terms with new ID if (lastEmpPoR.EmpPoRFrom.Equals(uploadEmpPoR.EmpPoRFrom)) { uploadEmpPoR.EmpPoRID = lastEmpPoR.EmpPoRID; if (uploadEmpPoR.EmpPoRTo.Ticks == 0 && lastEmpPoR.EmpPoRTo.Ticks != 0) { EEmpPlaceOfResidence afterEmPoR = (EEmpPlaceOfResidence)AppUtils.GetLastObj(dbConn, uploadDB, "EmpPoRFrom", uploadEmpPoR.EmpID, new Match("EmpPoRFrom", ">", lastEmpPoR.EmpPoRTo)); if (afterEmPoR != null) { uploadEmpPoR.EmpPoRTo = afterEmPoR.EmpPoRFrom.AddDays(-1); } } } else { EEmpPlaceOfResidence lastObj = (EEmpPlaceOfResidence)AppUtils.GetLastObj(dbConn, uploadDB, "EmpPoRFrom", uploadEmpPoR.EmpID); if (lastObj != null && uploadEmpPoR.EmpPoRFrom <= lastObj.EmpPoRFrom) { errors.addError(ImportErrorMessage.ERROR_DATE_FROM_OVERLAP, new string[] { uploadEmpPoR.EmpPoRFrom.ToString("yyyy-MM-dd"), rowCount.ToString() }); continue; } } } } } //if (rawDataTable.Columns.Contains(FIELD_INTERNAL_ID)) //{ // try // { // if (!row.IsNull(FIELD_INTERNAL_ID)) // { // int tmpID = FromHexDecWithCheckDigit((string)row[FIELD_INTERNAL_ID]); // EEmpPlaceOfResidence tmpObj = new EEmpPlaceOfResidence(); // tmpObj.EmpPoRID = tmpID; // if (EEmpPlaceOfResidence.db.select(dbConn, tmpObj)) // uploadEmpPoR.EmpPoRID = tmpID; // else // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } // } // } // catch (Exception ex) // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } //} if (rawDataTable.Columns.Contains(FIELD_SYNC_ID)) { if (!row.IsNull(FIELD_SYNC_ID)) { string strSynID = row[FIELD_SYNC_ID].ToString(); uploadEmpPoR.SynID = strSynID; if (!string.IsNullOrEmpty(strSynID)) { DBFilter synIDFilter = new DBFilter(); synIDFilter.add(new Match("SynID", strSynID)); ArrayList objSameSynIDList = EEmpPlaceOfResidence.db.select(dbConn, synIDFilter); if (objSameSynIDList.Count > 0) { uploadEmpPoR.EmpPoRID = ((EEmpPlaceOfResidence)objSameSynIDList[0]).EmpPoRID; } } } } if (uploadEmpPoR.EmpPoRID <= 0) { uploadEmpPoR.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT; } else { uploadEmpPoR.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; } uploadEmpPoR.UploadEmpID = ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID); if (uploadEmpPoR.UploadEmpID == 0) { if (uploadEmpPoR.EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else { uploadEmpPoR.UploadEmpID = ImportEmpPersonalInfoProcess.CreateDummyUploadEmployeeInfo(dbConn, uploadEmpPoR.EmpID, m_SessionID, UploadDateTime); } } Hashtable values = new Hashtable(); tempDB.populate(uploadEmpPoR, values); PageErrors pageErrors = new PageErrors(EUploadEmpPersonalInfo.db); tempDB.validate(pageErrors, values); if (pageErrors.errors.Count == 0) { tempDB.insert(dbConn, uploadEmpPoR); } else { pageErrors.addError(rawDataTable.TableName); throw new HRImportException(pageErrors.getPrompt() + "(line " + rowCount.ToString() + ")"); //if (EmpID == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); //else if (PayCodeID == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + PaymentCode, EmpNo, rowCount.ToString() }); //else if (PayCodeID == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + PaymentCode, EmpNo, rowCount.ToString() }); //else if (EffDate.Ticks == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_EFFECTIVE_DATE + "=" + EffDateString, EmpNo, rowCount.ToString() }); //else if (double.TryParse(amountString, out amount)) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_AMOUNT + "=" + amountString, EmpNo, rowCount.ToString() }); } } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(rawDataTable.TableName + "\r\n" + errors.Message())); } return(GetImportDataFromTempDatabase(null)); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsShowDescription, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_SKILL); tmpDataTable.Columns.Add(FIELD_SKILL_LEVEL); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpSkill.db.select(dbConn, filter); foreach (EEmpSkill empSkill in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(empSkill.EmpSkillID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } ESkill skill = new ESkill(); skill.SkillID = empSkill.SkillID; if (ESkill.db.select(dbConn, skill)) { row[FIELD_SKILL] = IsShowDescription ? skill.SkillDesc : skill.SkillCode; } ESkillLevel skillLevel = new ESkillLevel(); skillLevel.SkillLevelID = empSkill.SkillLevelID; if (ESkillLevel.db.select(dbConn, skillLevel)) { row[FIELD_SKILL_LEVEL] = IsShowDescription ? skillLevel.SkillLevelDesc : skillLevel.SkillLevelCode; } if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = empSkill.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_SURNAME, typeof(string)); tmpDataTable.Columns.Add(FIELD_OTHER_NAME, typeof(string)); tmpDataTable.Columns.Add(FIELD_CHINESE_NAME, typeof(string)); tmpDataTable.Columns.Add(FIELD_DATE_OF_BIRTH, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_HKID, typeof(string)); tmpDataTable.Columns.Add(FIELD_GENDER, typeof(string)); tmpDataTable.Columns.Add(FIELD_RELATIONSHIP, typeof(string)); tmpDataTable.Columns.Add(FIELD_PASSPORT_NUMBER, typeof(string)); tmpDataTable.Columns.Add(FIELD_PASSPORT_ISSUE_COUNTRY, typeof(string)); //Start 0000190, Miranda, 2015-05-18 tmpDataTable.Columns.Add(FIELD_MEDICAL_SCHEME_INSURED, typeof(string)); tmpDataTable.Columns.Add(FIELD_MEDICAL_EFFECTIVE_DATE, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_EXPIRY_DATE, typeof(DateTime)); //End 0000190, Miranda, 2015-05-18 if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpDependant.db.select(dbConn, filter); foreach (EEmpDependant empDependant in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(empDependant.EmpDependantID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_SURNAME] = empDependant.EmpDependantSurname; row[FIELD_OTHER_NAME] = empDependant.EmpDependantOtherName; row[FIELD_CHINESE_NAME] = empDependant.EmpDependantChineseName; row[FIELD_DATE_OF_BIRTH] = empDependant.EmpDependantDateOfBirth; row[FIELD_HKID] = empDependant.EmpDependantHKID; row[FIELD_GENDER] = empDependant.EmpDependantGender; row[FIELD_RELATIONSHIP] = empDependant.EmpDependantRelationship; row[FIELD_PASSPORT_NUMBER] = empDependant.EmpDependantPassportNo; row[FIELD_PASSPORT_ISSUE_COUNTRY] = empDependant.EmpDependantPassportIssuedCountry; // Start 0000190, Miranda, 2015-05-18 row[FIELD_MEDICAL_SCHEME_INSURED] = (empDependant.EmpDependantMedicalSchemeInsured) ? "Y" : "N"; row[FIELD_MEDICAL_EFFECTIVE_DATE] = empDependant.EmpDependantMedicalEffectiveDate; row[FIELD_EXPIRY_DATE] = empDependant.EmpDependantExpiryDate; // End 0000190, Miranda, 2014-05-18 if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = empDependant.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public DataTable UploadToTempDatabase(DataTable rawDataTable, int UserID) { if (rawDataTable == null) { return(GetImportDataFromTempDatabase(null)); } int rowCount = 1; foreach (DataRow row in rawDataTable.Rows) { rowCount++; EUploadEmpDependant uploadEmpDependant = new EUploadEmpDependant(); //EEmpDependant lastEmpDependant = null; string EmpNo = row[FIELD_EMP_NO].ToString().Trim(); uploadEmpDependant.EmpID = Parse.GetEmpID(dbConn, EmpNo, UserID); if (uploadEmpDependant.EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } uploadEmpDependant.EmpDependantSurname = row[FIELD_SURNAME].ToString(); uploadEmpDependant.EmpDependantOtherName = row[FIELD_OTHER_NAME].ToString(); uploadEmpDependant.EmpDependantChineseName = row[FIELD_CHINESE_NAME].ToString(); try { // support old version template if (rawDataTable.Columns.Contains(FIELD_DATE_OF_BIRTH)) { uploadEmpDependant.EmpDependantDateOfBirth = Import.Parse.toDateTimeObject(row[FIELD_DATE_OF_BIRTH]); } } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_DATE_OF_BIRTH + "=" + row[FIELD_DATE_OF_BIRTH].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpDependant.EmpDependantHKID = row[FIELD_HKID].ToString().Trim(); string tempString = row[FIELD_GENDER].ToString(); if (!String.IsNullOrEmpty(tempString)) { if (tempString.Equals("Male", StringComparison.CurrentCultureIgnoreCase) || tempString.Equals("M", StringComparison.CurrentCultureIgnoreCase)) { uploadEmpDependant.EmpDependantGender = "M"; } else if (tempString.Equals("Female", StringComparison.CurrentCultureIgnoreCase) || tempString.Equals("F", StringComparison.CurrentCultureIgnoreCase)) { uploadEmpDependant.EmpDependantGender = "F"; } else { uploadEmpDependant.EmpDependantGender = string.Empty; } } //else // errors.addError(ImportErrorMessage.ERROR_INVALID_GENDER, new string[] { tempString, rowCount.ToString() }); uploadEmpDependant.EmpDependantRelationship = row[FIELD_RELATIONSHIP].ToString(); //Start 0000190, Miranda, 2015-05-18 uploadEmpDependant.EmpDependantMedicalSchemeInsured = Parse.toBoolean(row[FIELD_MEDICAL_SCHEME_INSURED].ToString()); DateTime m_EffectiveDate = new DateTime(); DateTime m_ExpiryDate = new DateTime(); try { if (rawDataTable.Columns.Contains(FIELD_MEDICAL_EFFECTIVE_DATE)) { m_EffectiveDate = Import.Parse.toDateTimeObject(row[FIELD_MEDICAL_EFFECTIVE_DATE]); } } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_MEDICAL_EFFECTIVE_DATE + "=" + row[FIELD_MEDICAL_EFFECTIVE_DATE].ToString(), EmpNo, rowCount.ToString() }); } try { if (rawDataTable.Columns.Contains(FIELD_EXPIRY_DATE)) { m_ExpiryDate = Import.Parse.toDateTimeObject(row[FIELD_EXPIRY_DATE]); } } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_EXPIRY_DATE + "=" + row[FIELD_EXPIRY_DATE].ToString(), EmpNo, rowCount.ToString() }); } if (uploadEmpDependant.EmpDependantMedicalSchemeInsured) { if (m_EffectiveDate.Ticks == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_MEDICAL_EFFECTIVE_DATE + "=" + row[FIELD_MEDICAL_EFFECTIVE_DATE].ToString(), EmpNo, rowCount.ToString() }); } // Start 0000190, Miranda, 2015-06-18 //if (m_ExpiryDate.Ticks == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_EXPIRY_DATE + "=" + row[FIELD_EXPIRY_DATE].ToString(), EmpNo, rowCount.ToString() }); // End 0000190, Miranda, 2015-06-18 } else { if (m_EffectiveDate.Ticks > 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_MEDICAL_EFFECTIVE_DATE + "=" + row[FIELD_MEDICAL_EFFECTIVE_DATE].ToString(), EmpNo, rowCount.ToString() }); } if (m_ExpiryDate.Ticks > 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_EXPIRY_DATE + "=" + row[FIELD_EXPIRY_DATE].ToString(), EmpNo, rowCount.ToString() }); } } uploadEmpDependant.EmpDependantMedicalEffectiveDate = m_EffectiveDate; uploadEmpDependant.EmpDependantExpiryDate = m_ExpiryDate; //End 0000190, Miranda, 2015-05-18 uploadEmpDependant.EmpDependantPassportNo = row[FIELD_PASSPORT_NUMBER].ToString(); uploadEmpDependant.EmpDependantPassportIssuedCountry = row[FIELD_PASSPORT_ISSUE_COUNTRY].ToString(); //if (rawDataTable.Columns.Contains(FIELD_SYNID)) //{ // uploadEmpDependant.SynID = row[FIELD_SYNID].ToString(); //} uploadEmpDependant.SessionID = m_SessionID; uploadEmpDependant.TransactionDate = UploadDateTime; if (uploadEmpDependant.EmpID != 0) { //if (rawDataTable.Columns.Contains(FIELD_INTERNAL_ID)) //{ // try // { // if (!row.IsNull(FIELD_INTERNAL_ID)) // { // int tmpID = FromHexDecWithCheckDigit((string)row[FIELD_INTERNAL_ID]); // EEmpDependant tmpObj = new EEmpDependant(); // tmpObj.EmpDependantID = tmpID; // if (EEmpDependant.db.select(dbConn, tmpObj)) // uploadEmpDependant.EmpDependantID = tmpID; // else // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } // } // } // catch (Exception ex) // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } //} if (rawDataTable.Columns.Contains(FIELD_SYNC_ID)) { if (!row.IsNull(FIELD_SYNC_ID)) { string strSynID = row[FIELD_SYNC_ID].ToString(); uploadEmpDependant.SynID = strSynID; if (!string.IsNullOrEmpty(strSynID)) { DBFilter synIDFilter = new DBFilter(); synIDFilter.add(new Match("SynID", strSynID)); ArrayList objSameSynIDList = EEmpDependant.db.select(dbConn, synIDFilter); if (objSameSynIDList.Count > 0) { uploadEmpDependant.EmpDependantID = ((EEmpDependant)objSameSynIDList[0]).EmpDependantID; } } } } if (uploadEmpDependant.EmpDependantID == 0) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", uploadEmpDependant.EmpID)); ArrayList list = uploadDB.select(dbConn, filter); bool IsSameEntryExists = false; foreach (EEmpDependant currentEmpDependant in list) { if (uploadEmpDependant.EmpDependantHKID == currentEmpDependant.EmpDependantHKID && uploadEmpDependant.EmpDependantSurname == currentEmpDependant.EmpDependantSurname && uploadEmpDependant.EmpDependantOtherName == currentEmpDependant.EmpDependantOtherName && uploadEmpDependant.EmpDependantChineseName == currentEmpDependant.EmpDependantChineseName && uploadEmpDependant.EmpDependantRelationship == currentEmpDependant.EmpDependantRelationship && uploadEmpDependant.EmpDependantPassportNo == currentEmpDependant.EmpDependantPassportNo && uploadEmpDependant.EmpDependantPassportIssuedCountry == currentEmpDependant.EmpDependantPassportIssuedCountry && uploadEmpDependant.EmpDependantDateOfBirth == currentEmpDependant.EmpDependantDateOfBirth //Start 0000190, Miranda, 2015-05-18 && uploadEmpDependant.EmpDependantMedicalSchemeInsured == currentEmpDependant.EmpDependantMedicalSchemeInsured && uploadEmpDependant.EmpDependantMedicalEffectiveDate == currentEmpDependant.EmpDependantMedicalEffectiveDate && uploadEmpDependant.EmpDependantExpiryDate == currentEmpDependant.EmpDependantExpiryDate //End 0000190, Miranda, 2015-05-18 ) { IsSameEntryExists = true; } //if (!string.IsNullOrEmpty(uploadEmpDependant.SynID) && !string.IsNullOrEmpty(currentEmpDependant.SynID)) // if (currentEmpDependant.SynID.Equals(uploadEmpDependant.SynID, StringComparison.CurrentCultureIgnoreCase)) // { // uploadEmpDependant.EmpDependantID = currentEmpDependant.EmpDependantID; // IsSameEntryExists = false; // break; // } } if (IsSameEntryExists) { continue; } } } if (uploadEmpDependant.EmpDependantID <= 0) { uploadEmpDependant.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT; } else { uploadEmpDependant.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; } uploadEmpDependant.UploadEmpID = ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID); if (uploadEmpDependant.UploadEmpID == 0) { if (uploadEmpDependant.EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else { uploadEmpDependant.UploadEmpID = ImportEmpPersonalInfoProcess.CreateDummyUploadEmployeeInfo(dbConn, uploadEmpDependant.EmpID, m_SessionID, UploadDateTime); } } Hashtable values = new Hashtable(); tempDB.populate(uploadEmpDependant, values); PageErrors pageErrors = new PageErrors(EUploadEmpPersonalInfo.db); tempDB.validate(pageErrors, values); if (pageErrors.errors.Count == 0) { tempDB.insert(dbConn, uploadEmpDependant); } else { pageErrors.addError(rawDataTable.TableName); throw new HRImportException(pageErrors.getPrompt() + "(line " + rowCount.ToString() + ")"); } } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(rawDataTable.TableName + "\r\n" + errors.Message())); } return(GetImportDataFromTempDatabase(null)); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_COMPANYNAME, typeof(string)); tmpDataTable.Columns.Add(FIELD_COMPANYADDRESS, typeof(string)); tmpDataTable.Columns.Add(FIELD_CONTRACT_NO, typeof(string)); tmpDataTable.Columns.Add(FIELD_GRATUITY, typeof(double)); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpContractTerms.db.select(dbConn, filter); foreach (EEmpContractTerms empContract in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(empContract.EmpContractID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_FROM] = empContract.EmpContractEmployedFrom; row[FIELD_TO] = empContract.EmpContractEmployedTo; row[FIELD_COMPANYNAME] = empContract.EmpContractCompanyName; row[FIELD_COMPANYADDRESS] = empContract.EmpContractCompanyAddr; row[FIELD_CONTRACT_NO] = empContract.EmpContractCompanyContactNo; row[FIELD_GRATUITY] = empContract.EmpContractGratuity; if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = empContract.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public DataTable UploadToTempDatabase(DataTable rawDataTable, int UserID) { if (rawDataTable == null) { return(GetImportDataFromTempDatabase(null)); } int rowCount = 1; foreach (DataRow row in rawDataTable.Rows) { rowCount++; EUploadEmpContractTerms uploadEmpContract = new EUploadEmpContractTerms(); //EEmpContractTerms lastEmpContact = null; string EmpNo = row[FIELD_EMP_NO].ToString().Trim(); uploadEmpContract.EmpID = Parse.GetEmpID(dbConn, EmpNo, UserID); if (uploadEmpContract.EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } try { uploadEmpContract.EmpContractEmployedFrom = Parse.toDateTimeObject(row[FIELD_FROM]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_FROM + "=" + row[FIELD_FROM].ToString(), EmpNo, rowCount.ToString() }); } try { uploadEmpContract.EmpContractEmployedTo = Parse.toDateTimeObject(row[FIELD_TO]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_TO + "=" + row[FIELD_TO].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpContract.EmpContractCompanyName = row[FIELD_COMPANYNAME].ToString(); uploadEmpContract.EmpContractCompanyAddr = row[FIELD_COMPANYADDRESS].ToString(); uploadEmpContract.EmpContractCompanyContactNo = row[FIELD_CONTRACT_NO].ToString(); double amount = 0; if (row[FIELD_GRATUITY] != null) { if (!row[FIELD_GRATUITY].ToString().Equals(string.Empty)) { if (double.TryParse(row[FIELD_GRATUITY].ToString(), out amount)) { uploadEmpContract.EmpContractGratuity = amount; } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_GRATUITY + "=" + row[FIELD_GRATUITY].ToString(), EmpNo, rowCount.ToString() }); } } } uploadEmpContract.SessionID = m_SessionID; uploadEmpContract.TransactionDate = UploadDateTime; //if (uploadEmpContact.EmpID != 0) //{ // AND andTerms = new AND(); // andTerms.add(new Match("EmpContractEmployedFrom", "<=", uploadEmpContact.EmpContractEmployedFrom)); // lastEmpContact = (EEmpContractTerms)AppUtils.GetLastObj(dbConn, uploadDB, "EmpContractEmployedFrom", uploadEmpContact.EmpID, andTerms); // if (lastEmpContact != null) // { // if (uploadEmpPay.EmpPoRNature == lastEmpPay.EmpPoRNature // && uploadEmpPay.EmpPoRLandLord == lastEmpPay.EmpPoRLandLord // && uploadEmpPay.EmpPoRPropertyAddr == lastEmpPay.EmpPoRPropertyAddr // && uploadEmpPay.EmpPoRLandLordAddr == lastEmpPay.EmpPoRLandLordAddr // ) // { // continue; // } // else // { // // add postion terms with new ID // if (lastEmpContact.EmpPoRFrom.Equals(uploadEmpContact.EmpPoRFrom)) // { // uploadEmpContact.EmpPoRID = lastEmpContact.EmpPoRID; // if (uploadEmpContact.EmpPoRTo.Ticks == 0) // uploadEmpContact.EmpPoRTo = lastEmpContact.EmpPoRTo; // } // else // { // EEmpContractTerms lastObj = (EEmpContractTerms)AppUtils.GetLastObj(dbConn, uploadDB, "EmpPoRFrom", uploadEmpContact.EmpID); // if (lastObj != null && uploadEmpContact.EmpPoRFrom <= lastObj.EmpPoRFrom) // { // errors.addError(HROne.Translation.PageErrorMessage.ERROR_DATE_FROM_OVERLAP, new string[] { uploadEmpContact.EmpPoRFrom.ToString("yyyy-MM-dd"), rowCount.ToString() }); // continue; // } // } // } // } //} //if (rawDataTable.Columns.Contains(FIELD_INTERNAL_ID)) //{ // try // { // if (!row.IsNull(FIELD_INTERNAL_ID)) // { // int tmpID = FromHexDecWithCheckDigit((string)row[FIELD_INTERNAL_ID]); // EEmpContractTerms tmpObj = new EEmpContractTerms(); // tmpObj.EmpContractID = tmpID; // if (EEmpContractTerms.db.select(dbConn, tmpObj)) // uploadEmpContact.EmpContractID = tmpID; // else // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } // } // } // catch (Exception ex) // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } //} if (rawDataTable.Columns.Contains(FIELD_SYNC_ID)) { if (!row.IsNull(FIELD_SYNC_ID)) { string strSynID = row[FIELD_SYNC_ID].ToString(); uploadEmpContract.SynID = strSynID; if (!string.IsNullOrEmpty(strSynID)) { DBFilter synIDFilter = new DBFilter(); synIDFilter.add(new Match("SynID", strSynID)); ArrayList objSameSynIDList = EEmpContractTerms.db.select(dbConn, synIDFilter); if (objSameSynIDList.Count > 0) { uploadEmpContract.EmpContractID = ((EEmpContractTerms)objSameSynIDList[0]).EmpContractID; } } } } if (uploadEmpContract.EmpContractID <= 0) { uploadEmpContract.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT; } else { uploadEmpContract.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; } uploadEmpContract.UploadEmpID = ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID); if (uploadEmpContract.UploadEmpID == 0) { if (uploadEmpContract.EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else { uploadEmpContract.UploadEmpID = ImportEmpPersonalInfoProcess.CreateDummyUploadEmployeeInfo(dbConn, uploadEmpContract.EmpID, m_SessionID, UploadDateTime); } } Hashtable values = new Hashtable(); tempDB.populate(uploadEmpContract, values); PageErrors pageErrors = new PageErrors(EUploadEmpPersonalInfo.db); tempDB.validate(pageErrors, values); if (pageErrors.errors.Count == 0) { tempDB.insert(dbConn, uploadEmpContract); } else { pageErrors.addError(rawDataTable.TableName); throw new HRImportException(pageErrors.getPrompt() + "(line " + rowCount.ToString() + ")"); //if (EmpID == 0) // errors.addError(HROne.Translation.PageErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); //else if (PayCodeID == 0) // errors.addError(HROne.Translation.PageErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + PaymentCode, EmpNo, rowCount.ToString() }); //else if (PayCodeID == 0) // errors.addError(HROne.Translation.PageErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + PaymentCode, EmpNo, rowCount.ToString() }); //else if (EffDate.Ticks == 0) // errors.addError(HROne.Translation.PageErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_EFFECTIVE_DATE + "=" + EffDateString, EmpNo, rowCount.ToString() }); //else if (double.TryParse(amountString, out amount)) // errors.addError(HROne.Translation.PageErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_AMOUNT + "=" + amountString, EmpNo, rowCount.ToString() }); } } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(rawDataTable.TableName + "\r\n" + errors.Message())); } return(GetImportDataFromTempDatabase(null)); }
public DataTable UploadToTempDatabase(DataTable rawDataTable, int UserID, bool CreateCodeIfNotExists) { if (rawDataTable == null) { return(GetImportDataFromTempDatabase(null)); } int rowCount = 1; foreach (DataRow row in rawDataTable.Rows) { rowCount++; EUploadEmpSkill uploadEmpSkill = new EUploadEmpSkill(); string EmpNo = row[FIELD_EMP_NO].ToString().Trim(); uploadEmpSkill.EmpID = Parse.GetEmpID(dbConn, EmpNo, UserID); if (uploadEmpSkill.EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } uploadEmpSkill.SkillID = Parse.GetSkillID(dbConn, row[FIELD_SKILL].ToString(), CreateCodeIfNotExists, UserID); uploadEmpSkill.SkillLevelID = Parse.GetSkillLevelID(dbConn, row[FIELD_SKILL_LEVEL].ToString(), CreateCodeIfNotExists, UserID); if (uploadEmpSkill.SkillLevelID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_SKILL_LEVEL + "=" + row[FIELD_SKILL_LEVEL].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpSkill.SessionID = m_SessionID; uploadEmpSkill.TransactionDate = UploadDateTime; if (uploadEmpSkill.EmpID != 0) { //if (rawDataTable.Columns.Contains(FIELD_INTERNAL_ID)) //{ // try // { // if (!row.IsNull(FIELD_INTERNAL_ID)) // { // int tmpID = FromHexDecWithCheckDigit((string)row[FIELD_INTERNAL_ID]); // EEmpSkill tmpObj = new EEmpSkill(); // tmpObj.EmpSkillID = tmpID; // if (EEmpSkill.db.select(dbConn, tmpObj)) // uploadEmpSkill.EmpSkillID = tmpID; // else // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } // } // } // catch (Exception ex) // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } //} if (rawDataTable.Columns.Contains(FIELD_SYNC_ID)) { if (!row.IsNull(FIELD_SYNC_ID)) { string strSynID = row[FIELD_SYNC_ID].ToString(); uploadEmpSkill.SynID = strSynID; if (!string.IsNullOrEmpty(strSynID)) { DBFilter synIDFilter = new DBFilter(); synIDFilter.add(new Match("SynID", strSynID)); ArrayList objSameSynIDList = EEmpSkill.db.select(dbConn, synIDFilter); if (objSameSynIDList.Count > 0) { uploadEmpSkill.EmpSkillID = ((EEmpSkill)objSameSynIDList[0]).EmpSkillID; } } } } if (uploadEmpSkill.EmpSkillID == 0) { AND andTerms = new AND(); andTerms.add(new Match("SkillID", uploadEmpSkill.SkillID)); EEmpSkill lastEmpSkill = (EEmpSkill)AppUtils.GetLastObj(dbConn, uploadDB, "EmpSkillID", uploadEmpSkill.EmpID, andTerms); if (lastEmpSkill != null) { if (uploadEmpSkill.SkillLevelID == lastEmpSkill.SkillLevelID) { continue; } else { uploadEmpSkill.EmpSkillID = lastEmpSkill.EmpSkillID; } } } } if (uploadEmpSkill.EmpSkillID <= 0) { uploadEmpSkill.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT; } else { uploadEmpSkill.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; } uploadEmpSkill.UploadEmpID = ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID); if (uploadEmpSkill.UploadEmpID == 0) { if (uploadEmpSkill.EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else { uploadEmpSkill.UploadEmpID = ImportEmpPersonalInfoProcess.CreateDummyUploadEmployeeInfo(dbConn, uploadEmpSkill.EmpID, m_SessionID, UploadDateTime); } } Hashtable values = new Hashtable(); tempDB.populate(uploadEmpSkill, values); PageErrors pageErrors = new PageErrors(EUploadEmpPersonalInfo.db); tempDB.validate(pageErrors, values); if (pageErrors.errors.Count == 0) { tempDB.insert(dbConn, uploadEmpSkill); } else { pageErrors.addError(rawDataTable.TableName); throw new HRImportException(pageErrors.getPrompt() + "(line " + rowCount.ToString() + ")"); } } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(rawDataTable.TableName + "\r\n" + errors.Message())); } return(GetImportDataFromTempDatabase(null)); }
public DataTable UploadToTempDatabase(DataTable rawDataTable, int UserID) { if (rawDataTable == null) { return(GetImportDataFromTempDatabase(null)); } int rowCount = 1; foreach (DataRow row in rawDataTable.Rows) { rowCount++; EUploadEmpRosterTableGroup uploadEmpRosterTableGroup = new EUploadEmpRosterTableGroup(); EEmpRosterTableGroup lastEmpRosterTableGroup = null; ArrayList uploadHierarchyList = new ArrayList(); string EmpNo = row[FIELD_EMP_NO].ToString().Trim(); uploadEmpRosterTableGroup.EmpID = Parse.GetEmpID(dbConn, EmpNo, UserID); if (uploadEmpRosterTableGroup.EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } try { uploadEmpRosterTableGroup.EmpRosterTableGroupEffFr = Parse.toDateTimeObject(row[FIELD_FROM]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_FROM + "=" + row[FIELD_FROM].ToString(), EmpNo, rowCount.ToString() }); } try { uploadEmpRosterTableGroup.EmpRosterTableGroupEffTo = Parse.toDateTimeObject(row[FIELD_TO]); } catch { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_TO + "=" + row[FIELD_TO].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpRosterTableGroup.RosterTableGroupID = Parse.GetRosterTableGroupID(dbConn, row[FIELD_ROSTERTABLEGROUP].ToString()); uploadEmpRosterTableGroup.EmpRosterTableGroupIsSupervisor = row[FIELD_ROSTERTABLEGROUP_IS_SUPERVISOR].ToString().Equals("Yes", StringComparison.CurrentCultureIgnoreCase) || row[FIELD_ROSTERTABLEGROUP_IS_SUPERVISOR].ToString().Equals("Y", StringComparison.CurrentCultureIgnoreCase); if (uploadEmpRosterTableGroup.RosterTableGroupID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_ROSTERTABLEGROUP + "=" + row[FIELD_ROSTERTABLEGROUP].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpRosterTableGroup.SessionID = m_SessionID; uploadEmpRosterTableGroup.TransactionDate = UploadDateTime; if (uploadEmpRosterTableGroup.EmpID != 0 && errors.List.Count <= 0) { //if (rawDataTable.Columns.Contains(FIELD_INTERNAL_ID)) //{ // try // { // if (!row.IsNull(FIELD_INTERNAL_ID)) // { // int tmpID = FromHexDecWithCheckDigit((string)row[FIELD_INTERNAL_ID]); // EEmpRosterTableGroup tmpObj = new EEmpRosterTableGroup(); // tmpObj.EmpRosterTableGroupID = tmpID; // if (EEmpRosterTableGroup.db.select(dbConn, tmpObj)) // uploadEmpRosterTableGroup.EmpRosterTableGroupID = tmpID; // else // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } // } // } // catch (Exception ex) // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } //} if (rawDataTable.Columns.Contains(FIELD_SYNC_ID)) { if (!row.IsNull(FIELD_SYNC_ID)) { string strSynID = row[FIELD_SYNC_ID].ToString(); uploadEmpRosterTableGroup.SynID = strSynID; if (!string.IsNullOrEmpty(strSynID)) { DBFilter synIDFilter = new DBFilter(); synIDFilter.add(new Match("SynID", strSynID)); ArrayList objSameSynIDList = EEmpRosterTableGroup.db.select(dbConn, synIDFilter); if (objSameSynIDList.Count > 0) { uploadEmpRosterTableGroup.EmpRosterTableGroupID = ((EEmpRosterTableGroup)objSameSynIDList[0]).EmpRosterTableGroupID; } } } } if (uploadEmpRosterTableGroup.EmpRosterTableGroupID == 0) { // allow multiple group on same date // do NOT allow same group id appear on same date AND lastEmpRosterTableGroupAndTerms = new AND(); lastEmpRosterTableGroupAndTerms.add(new Match("EmpRosterTableGroupEffFr", "<=", uploadEmpRosterTableGroup.EmpRosterTableGroupEffFr)); lastEmpRosterTableGroupAndTerms.add(new Match("RosterTableGroupID", uploadEmpRosterTableGroup.RosterTableGroupID)); lastEmpRosterTableGroup = (EEmpRosterTableGroup)AppUtils.GetLastObj(dbConn, uploadDB, "EmpRosterTableGroupEffFr", uploadEmpRosterTableGroup.EmpID, lastEmpRosterTableGroupAndTerms); if (lastEmpRosterTableGroup != null) { if (uploadEmpRosterTableGroup.RosterTableGroupID == lastEmpRosterTableGroup.RosterTableGroupID && uploadEmpRosterTableGroup.EmpRosterTableGroupIsSupervisor == lastEmpRosterTableGroup.EmpRosterTableGroupIsSupervisor && uploadEmpRosterTableGroup.EmpRosterTableGroupEffTo == lastEmpRosterTableGroup.EmpRosterTableGroupEffTo) { continue; } else { // add postion terms with new ID if (lastEmpRosterTableGroup.EmpRosterTableGroupEffFr.Equals(uploadEmpRosterTableGroup.EmpRosterTableGroupEffFr) && uploadEmpRosterTableGroup.RosterTableGroupID == lastEmpRosterTableGroup.RosterTableGroupID && uploadEmpRosterTableGroup.EmpRosterTableGroupIsSupervisor == lastEmpRosterTableGroup.EmpRosterTableGroupIsSupervisor) { uploadEmpRosterTableGroup.EmpRosterTableGroupID = lastEmpRosterTableGroup.EmpRosterTableGroupID; //if (uploadEmpRosterTableGroup.EmpRosterTableGroupEffTo.Ticks == 0 && lastEmpRosterTableGroup.EmpRosterTableGroupEffTo.Ticks != 0) //{ // EEmpRosterTableGroup afterEmpRosterTableGroup = (EEmpRosterTableGroup)AppUtils.GetLastObj(dbConn, uploadDB, "EmpRosterTableGroupEffFr", uploadEmpRosterTableGroup.EmpID, new Match("EmpRosterTableGroupEffFr", ">", lastEmpRosterTableGroup.EmpRosterTableGroupEffTo)); // if (afterEmpRosterTableGroup != null) // uploadEmpRosterTableGroup.EmpRosterTableGroupEffTo = afterEmpRosterTableGroup.EmpRosterTableGroupEffFr.AddDays(-1); //} } //else //{ // AND lastObjAndTerms = new AND(); // lastObjAndTerms.add(new Match("EmpRosterTableGroupEffFr", ">", uploadEmpRosterTableGroup.EmpRosterTableGroupEffFr)); // if (!uploadEmpRosterTableGroup.EmpRosterTableGroupEffTo.Ticks.Equals(0)) // lastObjAndTerms.add(new Match("EmpRosterTableGroupEffFr", "<=", uploadEmpRosterTableGroup.EmpRosterTableGroupEffTo)); // lastObjAndTerms.add(new Match("RosterTableGroupID", uploadEmpRosterTableGroup.RosterTableGroupID)); // EEmpRosterTableGroup lastObj = (EEmpRosterTableGroup)AppUtils.GetLastObj(dbConn, uploadDB, "EmpRosterTableGroupEffFr", uploadEmpRosterTableGroup.EmpID, lastObjAndTerms); // if (lastObj != null) // if (!lastObj.EmpRosterTableGroupEffTo.Ticks.Equals(0)) // { // errors.addError(ImportErrorMessage.ERROR_DATE_FROM_OVERLAP, new string[] { uploadEmpRosterTableGroup.EmpRosterTableGroupEffFr.ToString("yyyy-MM-dd"), rowCount.ToString() }); // continue; // } //} } } } } if (uploadEmpRosterTableGroup.EmpRosterTableGroupID <= 0) { uploadEmpRosterTableGroup.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT; } else { uploadEmpRosterTableGroup.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; } uploadEmpRosterTableGroup.UploadEmpID = ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID); if (uploadEmpRosterTableGroup.UploadEmpID == 0) { if (uploadEmpRosterTableGroup.EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else { uploadEmpRosterTableGroup.UploadEmpID = ImportEmpPersonalInfoProcess.CreateDummyUploadEmployeeInfo(dbConn, uploadEmpRosterTableGroup.EmpID, m_SessionID, UploadDateTime); } } Hashtable values = new Hashtable(); tempDB.populate(uploadEmpRosterTableGroup, values); PageErrors pageErrors = new PageErrors(EUploadEmpPersonalInfo.db); tempDB.validate(pageErrors, values); if (pageErrors.errors.Count == 0) { tempDB.insert(dbConn, uploadEmpRosterTableGroup); } else { pageErrors.addError(rawDataTable.TableName); throw new HRImportException(pageErrors.getPrompt() + "(line " + rowCount.ToString() + ")"); //if (EmpID == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); //else if (PayCodeID == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + PaymentCode, EmpNo, rowCount.ToString() }); //else if (PayCodeID == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + PaymentCode, EmpNo, rowCount.ToString() }); //else if (EffDate.Ticks == 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_EFFECTIVE_DATE + "=" + EffDateString, EmpNo, rowCount.ToString() }); //else if (double.TryParse(amountString, out amount)) // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_AMOUNT + "=" + amountString, EmpNo, rowCount.ToString() }); } } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(rawDataTable.TableName + "\r\n" + errors.Message())); } return(GetImportDataFromTempDatabase(null)); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsShowDescription, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_NAME, typeof(string)); tmpDataTable.Columns.Add(FIELD_SHARE, typeof(decimal)); tmpDataTable.Columns.Add(FIELD_HKID, typeof(string)); tmpDataTable.Columns.Add(FIELD_RELATION, typeof(string)); tmpDataTable.Columns.Add(FIELD_ADDRESS, typeof(string)); tmpDataTable.Columns.Add(FIELD_DISTRICT, typeof(string)); tmpDataTable.Columns.Add(FIELD_AREA, typeof(string)); tmpDataTable.Columns.Add(FIELD_COUNTRY, typeof(string)); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpBeneficiaries.db.select(dbConn, filter); foreach (EEmpBeneficiaries empBeneficiaries in list) { DataRow row = tmpDataTable.NewRow(); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_NAME] = empBeneficiaries.EmpBeneficiariesName; row[FIELD_SHARE] = empBeneficiaries.EmpBeneficiariesShare; row[FIELD_HKID] = empBeneficiaries.EmpBeneficiariesHKID; row[FIELD_RELATION] = empBeneficiaries.EmpBeneficiariesRelation; row[FIELD_ADDRESS] = empBeneficiaries.EmpBeneficiariesAddress; row[FIELD_DISTRICT] = empBeneficiaries.EmpBeneficiariesDistrict; row[FIELD_AREA] = empBeneficiaries.EmpBeneficiariesArea; row[FIELD_COUNTRY] = empBeneficiaries.EmpBeneficiariesCountry; if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = empBeneficiaries.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsShowDescription, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_ROSTERTABLEGROUP, typeof(string)); tmpDataTable.Columns.Add(FIELD_ROSTERTABLEGROUP_IS_SUPERVISOR, typeof(string)); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpRosterTableGroup.db.select(dbConn, filter); foreach (EEmpRosterTableGroup EmpRosterTableGroup in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(EmpRosterTableGroup.EmpRosterTableGroupID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_FROM] = EmpRosterTableGroup.EmpRosterTableGroupEffFr; row[FIELD_TO] = EmpRosterTableGroup.EmpRosterTableGroupEffTo; ERosterTableGroup rosterTableGroup = new ERosterTableGroup(); rosterTableGroup.RosterTableGroupID = EmpRosterTableGroup.RosterTableGroupID; if (ERosterTableGroup.db.select(dbConn, rosterTableGroup)) { row[FIELD_ROSTERTABLEGROUP] = IsShowDescription ? rosterTableGroup.RosterTableGroupDesc : rosterTableGroup.RosterTableGroupCode; } row[FIELD_ROSTERTABLEGROUP_IS_SUPERVISOR] = EmpRosterTableGroup.EmpRosterTableGroupIsSupervisor ? "Yes" : "No"; //row[FIELD_IS_ROSTER_TABLE_GROUP_SUPERVISOR] = empPositionInfo.EmpPosIsRosterTableGroupSupervisor ? "Yes" : "No"; if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = EmpRosterTableGroup.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_NATURE, typeof(string)); tmpDataTable.Columns.Add(FIELD_LANDLORD, typeof(string)); tmpDataTable.Columns.Add(FIELD_ADDRESS, typeof(string)); tmpDataTable.Columns.Add(FIELD_LANDLORD_ADDRESS, typeof(string)); tmpDataTable.Columns.Add(FIELD_ER_RENT_PAID, typeof(double)); tmpDataTable.Columns.Add(FIELD_EE_RENT_PAID, typeof(double)); tmpDataTable.Columns.Add(FIELD_EE_RENT_REFUND, typeof(double)); tmpDataTable.Columns.Add(FIELD_EE_RENT_PAID_TO_ER, typeof(double)); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpPlaceOfResidence.db.select(dbConn, filter); foreach (EEmpPlaceOfResidence empPlaceOfResidence in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(empPlaceOfResidence.EmpPoRID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_FROM] = empPlaceOfResidence.EmpPoRFrom; row[FIELD_TO] = empPlaceOfResidence.EmpPoRTo; row[FIELD_NATURE] = empPlaceOfResidence.EmpPoRNature; row[FIELD_LANDLORD] = empPlaceOfResidence.EmpPoRLandLord; row[FIELD_ADDRESS] = empPlaceOfResidence.EmpPoRPropertyAddr; row[FIELD_LANDLORD_ADDRESS] = empPlaceOfResidence.EmpPoRLandLordAddr; row[FIELD_ER_RENT_PAID] = empPlaceOfResidence.EmpPoRPayToLandER; row[FIELD_EE_RENT_PAID] = empPlaceOfResidence.EmpPoRPayToLandEE; row[FIELD_EE_RENT_REFUND] = empPlaceOfResidence.EmpPoRRefundToEE; row[FIELD_EE_RENT_PAID_TO_ER] = empPlaceOfResidence.EmpPoRPayToERByEE; if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = empPlaceOfResidence.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public DataTable UploadToTempDatabase(DataTable rawDataTable, int UserID) { if (rawDataTable == null) { return(GetImportDataFromTempDatabase(null)); } int rowCount = 1; foreach (DataRow row in rawDataTable.Rows) { rowCount++; EUploadEmpFinalPayment uploadEmpFinalPay = new EUploadEmpFinalPayment(); //EEmpFinalPayment lastEmpFinalPay; string EmpNo = row[FIELD_EMP_NO].ToString().Trim(); uploadEmpFinalPay.EmpID = Parse.GetEmpID(dbConn, EmpNo, UserID); if (uploadEmpFinalPay.EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } uploadEmpFinalPay.PayCodeID = Parse.GetPaymentCodeID(dbConn, row[FIELD_PAYMENT_CODE].ToString()); if (uploadEmpFinalPay.PayCodeID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + row[FIELD_PAYMENT_CODE].ToString(), EmpNo, rowCount.ToString() }); } double amount = 0; if (double.TryParse(row[FIELD_AMOUNT].ToString(), out amount)) { uploadEmpFinalPay.EmpFinalPayAmount = amount; } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_AMOUNT + "=" + row[FIELD_AMOUNT].ToString(), EmpNo, rowCount.ToString() }); } uploadEmpFinalPay.CurrencyID = HROne.Lib.ExchangeCurrency.DefaultCurrency(); uploadEmpFinalPay.EmpFinalPayMethod = Parse.toPaymentMethodCode(row[FIELD_METHOD].ToString()); string BankCode = row[FIELD_ACCOUNT_NO].ToString(); uploadEmpFinalPay.EmpAccID = HROne.Import.Parse.GetEmpAccID(dbConn, BankCode, uploadEmpFinalPay.EmpID); // Check if the bank account no does not exist in database if (uploadEmpFinalPay.EmpAccID == 0 && !BankCode.Trim().Equals(string.Empty)) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_ACCOUNT_NO + "=" + BankCode, EmpNo, rowCount.ToString() }); } if (uploadEmpFinalPay.EmpAccID > 0 && !uploadEmpFinalPay.EmpFinalPayMethod.Equals("A")) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_METHOD + "=" + row[FIELD_METHOD].ToString(), EmpNo, rowCount.ToString() }); } if (uploadEmpFinalPay.EmpFinalPayMethod.Equals("A") && uploadEmpFinalPay.EmpAccID == 0) { EUploadEmpBankAccount uploadBankAccount = EUploadEmpBankAccount.GetDefaultBankAccount(dbConn, uploadEmpFinalPay.EmpID, ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID), m_SessionID); if (uploadBankAccount != null) { //if (uploadBankAccount.EmpBankAccountID > 0) // uploadEmpFinalPay.EmpAccID = uploadBankAccount.EmpBankAccountID; //else // uploadEmpFinalPay.UploadEmpAccID = uploadBankAccount.UploadEmpBankAccountID; } else if (uploadEmpFinalPay.EmpID > 0) { EEmpBankAccount bankAccount = EEmpBankAccount.GetDefaultBankAccount(dbConn, uploadEmpFinalPay.EmpID); if (bankAccount != null) { //uploadEmpFinalPay.EmpAccID = bankAccount.EmpBankAccountID; } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_ACCOUNT_NO + "=" + BankCode, EmpNo, rowCount.ToString() }); } } else { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_ACCOUNT_NO + "=" + BankCode, EmpNo, rowCount.ToString() }); } } if (rawDataTable.Columns.Contains(FIELD_COST_CENTER)) { string CostCenter = row[FIELD_COST_CENTER].ToString(); if (!string.IsNullOrEmpty(CostCenter)) { uploadEmpFinalPay.CostCenterID = HROne.Import.Parse.GetCostCenterID(dbConn, CostCenter, false, UserID); if (uploadEmpFinalPay.CostCenterID <= 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_COST_CENTER + "=" + CostCenter, EmpNo, rowCount.ToString() }); } } else { uploadEmpFinalPay.CostCenterID = 0; } } uploadEmpFinalPay.EmpFinalPayRemark = row[FIELD_REMARK].ToString().Trim(); double NumOfDayAdjust = 0; if (rawDataTable.Columns.Contains(FIELD_NUM_OF_DAY_ADJUST)) { string NumOfDayAdjustString = row[FIELD_NUM_OF_DAY_ADJUST].ToString(); if (!string.IsNullOrEmpty(NumOfDayAdjustString)) { if (!double.TryParse(NumOfDayAdjustString, out NumOfDayAdjust)) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_NUM_OF_DAY_ADJUST + "=" + NumOfDayAdjustString, EmpNo, rowCount.ToString() }); } else { uploadEmpFinalPay.EmpFinalPayNumOfDayAdj = NumOfDayAdjust; } } else { uploadEmpFinalPay.EmpFinalPayNumOfDayAdj = 0; } } uploadEmpFinalPay.EmpFinalPayIsAutoGen = false; uploadEmpFinalPay.SessionID = m_SessionID; uploadEmpFinalPay.TransactionDate = UploadDateTime; // Final Payment do NOT have unique constraint, skip checking duplicate record //if (uploadEmpFinalPay.EmpID != 0) //{ // AND andTerms = new AND(); // andTerms.add(new Match("PayCodeID", uploadEmpFinalPay.PayCodeID)); // lastEmpFinalPay = (EEmpFinalPayment)AppUtils.GetLastObj(dbConn, uploadDB, "EmpFinalPayID", uploadEmpFinalPay.EmpID, andTerms); // if (lastEmpFinalPay != null) // { // if (uploadEmpFinalPay.EmpAccID == lastEmpFinalPay.EmpAccID // && Math.Abs(uploadEmpFinalPay.EmpFinalPayAmount - lastEmpFinalPay.EmpFinalPayAmount) < 0.01 // && uploadEmpFinalPay.EmpFinalPayMethod == lastEmpFinalPay.EmpFinalPayMethod // && uploadEmpFinalPay.PayCodeID == lastEmpFinalPay.PayCodeID // ) // continue; // else // { // // add postion terms with new ID // uploadEmpFinalPay.EmpFinalPayID = lastEmpFinalPay.EmpFinalPayID; // } // } //} //if (rawDataTable.Columns.Contains(FIELD_INTERNAL_ID)) //{ // try // { // if (!row.IsNull(FIELD_INTERNAL_ID)) // { // int tmpID = FromHexDecWithCheckDigit((string)row[FIELD_INTERNAL_ID]); // EEmpFinalPayment tmpObj = new EEmpFinalPayment(); // tmpObj.EmpFinalPayID = tmpID; // if (EEmpFinalPayment.db.select(dbConn, tmpObj)) // uploadEmpFinalPay.EmpFinalPayID = tmpID; // else // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } // } // } // catch (Exception ex) // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_INTERNAL_ID + "=" + row[FIELD_INTERNAL_ID].ToString(), EmpNo, rowCount.ToString() }); // continue; // } //} if (rawDataTable.Columns.Contains(FIELD_SYNC_ID)) { if (!row.IsNull(FIELD_SYNC_ID)) { string strSynID = row[FIELD_SYNC_ID].ToString(); uploadEmpFinalPay.SynID = strSynID; if (!string.IsNullOrEmpty(strSynID)) { DBFilter synIDFilter = new DBFilter(); synIDFilter.add(new Match("SynID", strSynID)); ArrayList objSameSynIDList = EEmpFinalPayment.db.select(dbConn, synIDFilter); if (objSameSynIDList.Count > 0) { uploadEmpFinalPay.EmpFinalPayID = ((EEmpFinalPayment)objSameSynIDList[0]).EmpFinalPayID; } } } } if (uploadEmpFinalPay.EmpFinalPayID <= 0) { uploadEmpFinalPay.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT; } else { uploadEmpFinalPay.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; } uploadEmpFinalPay.UploadEmpID = ParseTemp.GetUploadEmpID(dbConn, EmpNo, m_SessionID); if (uploadEmpFinalPay.UploadEmpID == 0) { if (uploadEmpFinalPay.EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else { uploadEmpFinalPay.UploadEmpID = ImportEmpPersonalInfoProcess.CreateDummyUploadEmployeeInfo(dbConn, uploadEmpFinalPay.EmpID, m_SessionID, UploadDateTime); } } Hashtable values = new Hashtable(); tempDB.populate(uploadEmpFinalPay, values); PageErrors pageErrors = new PageErrors(tempDB); tempDB.validate(pageErrors, values); if (pageErrors.errors.Count == 0) { tempDB.insert(dbConn, uploadEmpFinalPay); } else { pageErrors.addError(rawDataTable.TableName); throw new HRImportException(pageErrors.getPrompt() + "(line " + rowCount.ToString() + ")"); } } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(rawDataTable.TableName + "\r\n" + errors.Message())); } return(GetImportDataFromTempDatabase(null)); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsShowDescription, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime)); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } tmpDataTable.Columns.Add(FIELD_COST_CENTER + "1", typeof(string)); tmpDataTable.Columns.Add(FIELD_PERCENTAGE + "1", typeof(double)); tmpDataTable.Columns.Add(FIELD_COST_CENTER + "2", typeof(string)); tmpDataTable.Columns.Add(FIELD_PERCENTAGE + "2", typeof(double)); foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpCostCenter.db.select(dbConn, filter); foreach (EEmpCostCenter empCostCenter in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(empCostCenter.EmpCostCenterID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_FROM] = empCostCenter.EmpCostCenterEffFr; row[FIELD_TO] = empCostCenter.EmpCostCenterEffTo; DBFilter costCenterDetailFilter = new DBFilter(); costCenterDetailFilter.add(new Match("EmpCostCenterID", empCostCenter.EmpCostCenterID)); ArrayList costCenterDetailList = EEmpCostCenterDetail.db.select(dbConn, costCenterDetailFilter); for (int i = 1; i <= costCenterDetailList.Count; i++) { EEmpCostCenterDetail empCostCenterDetail = (EEmpCostCenterDetail)costCenterDetailList[i - 1]; if (!tmpDataTable.Columns.Contains(FIELD_COST_CENTER + i)) { tmpDataTable.Columns.Add(FIELD_COST_CENTER + i, typeof(string)); } if (!tmpDataTable.Columns.Contains(FIELD_PERCENTAGE + i)) { tmpDataTable.Columns.Add(FIELD_PERCENTAGE + i, typeof(double)); } ECostCenter costCenter = new ECostCenter(); costCenter.CostCenterID = empCostCenterDetail.CostCenterID; if (ECostCenter.db.select(dbConn, costCenter)) { row[FIELD_COST_CENTER + i] = IsShowDescription ? costCenter.CostCenterDesc : costCenter.CostCenterCode; row[FIELD_PERCENTAGE + i] = empCostCenterDetail.EmpCostCenterPercentage; } } if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = empCostCenter.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsShowDescription, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_PAYMENT_CODE, typeof(string)); tmpDataTable.Columns.Add(FIELD_AMOUNT, typeof(double)); tmpDataTable.Columns.Add(FIELD_METHOD, typeof(string)); tmpDataTable.Columns.Add(FIELD_ACCOUNT_NO, typeof(string)); tmpDataTable.Columns.Add(FIELD_COST_CENTER, typeof(string)); tmpDataTable.Columns.Add(FIELD_NUM_OF_DAY_ADJUST, typeof(double)); tmpDataTable.Columns.Add(FIELD_REMARK, typeof(string)); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpFinalPayment.db.select(dbConn, filter); foreach (EEmpFinalPayment empFinalPayment in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(empFinalPayment.EmpFinalPayID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } EPaymentCode paymentCode = new EPaymentCode(); paymentCode.PaymentCodeID = empFinalPayment.PayCodeID; if (EPaymentCode.db.select(dbConn, paymentCode)) { row[FIELD_PAYMENT_CODE] = IsShowDescription ? paymentCode.PaymentCodeDesc : paymentCode.PaymentCode; } row[FIELD_AMOUNT] = empFinalPayment.EmpFinalPayAmount; if (empFinalPayment.EmpFinalPayMethod.Equals("A")) { row[FIELD_METHOD] = "Autopay"; EEmpBankAccount empBankAccount = new EEmpBankAccount(); empBankAccount.EmpBankAccountID = empFinalPayment.EmpAccID; if (EEmpBankAccount.db.select(dbConn, empBankAccount)) { row[FIELD_ACCOUNT_NO] = empBankAccount.EmpBankCode + "-" + empBankAccount.EmpBranchCode + "-" + empBankAccount.EmpAccountNo; } } else if (empFinalPayment.EmpFinalPayMethod.Equals("Q")) { row[FIELD_METHOD] = "Cheque"; } else if (empFinalPayment.EmpFinalPayMethod.Equals("C")) { row[FIELD_METHOD] = "Cash"; } else { row[FIELD_METHOD] = "Others"; } ECostCenter costCenter = new ECostCenter(); costCenter.CostCenterID = empFinalPayment.CostCenterID; if (ECostCenter.db.select(dbConn, costCenter)) { row[FIELD_COST_CENTER] = IsShowDescription ? costCenter.CostCenterDesc : costCenter.CostCenterCode; } row[FIELD_NUM_OF_DAY_ADJUST] = empFinalPayment.EmpFinalPayNumOfDayAdj; row[FIELD_REMARK] = empFinalPayment.EmpFinalPayRemark; if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = empFinalPayment.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }