public OperationResult Import(String fileName, Service.Excel.ImportData importData) { try { var columns = importData == null ? null : importData.Columns; var maps = ImportUtil.GetColumns(columns, new TaxBaseEveryMonth()); var items = ExcelService.GetObjects(fileName, columns); if (importData != null) { int num = 1; foreach (var item in items) { TaxBaseEveryMonth record = new TaxBaseEveryMonth(); List <ImportFeedBack> errors = ImportUtil.ValidateImportRecord(item, num++, maps, ref record); if (errors.Count > 0) { return(new OperationResult(OperationResultType.Error, "导入数据失败", ImportUtil.ParseToHtml(errors))); } //插入或更新数据 _TaxBaseEveryMonthRepository.InsertOrUpdate(record); } } return(new OperationResult(OperationResultType.Success, "导入数据成功!")); } catch (Exception ex) { _log.Error(ex); ImportFeedBack feedBack = new ImportFeedBack(); feedBack.ExceptionType = "未知错误"; feedBack.ExceptionContent.Add(ex.Message); List <ImportFeedBack> erros = new List <ImportFeedBack>(); return(new OperationResult(OperationResultType.Error, "导入数据失败!", ImportUtil.ParseToHtml(new List <ImportFeedBack>() { feedBack }))); } }
private List <ImportFeedBack> ValidatePersonalRecord(LinqToExcel.Row record, int num, Dictionary <String, String> map, ref PersonalRecord personal) { List <ImportFeedBack> list = new List <ImportFeedBack>(); ImportFeedBack feedBack = null; //非空验证 feedBack = new ImportFeedBack(); feedBack.ExceptionType = "空值"; if (personal.PaymentType.Equals("银行转账")) { var properties = personal.GetType().GetProperties(); foreach (var property in properties) { switch (property.Name) { case "Name": case "CertificateType": case "CertificateID": case "Company": case "Tele": case "PersonType": case "Nationality": case "Title": case "TaxOrNot": case "AccountNumber": if (String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name))) { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}为空!", num, map[property.Name])); } else { property.SetValue(personal, ImportUtil.GetValue(record, map, property.Name)); } break; case "BankDetailName": if (!ImportUtil.GetValue(record, map, "Bank").Equals("工商银行")) { if (String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name))) { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}为空!", num, map[property.Name])); } else { property.SetValue(personal, ImportUtil.GetValue(record, map, property.Name)); } } break; case "Gender": if (!ImportUtil.GetValue(record, map, "CertificateType").Equals("居民身份证")) { if (String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name))) { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}为空!", num, map[property.Name])); } else { property.SetValue(personal, ImportUtil.GetValue(record, map, property.Name)); } } break; case "Birth": if (!ImportUtil.GetValue(record, map, "CertificateType").Equals("居民身份证")) { if (String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name))) { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}为空!", num, map[property.Name])); } else { property.SetValue(personal, ImportUtil.GetValue(record, map, property.Name)); } } break; case "Amount": try { var value = Convert.ToDouble(ImportUtil.GetValue(record, map, property.Name)); property.SetValue(personal, value); } catch { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}格式不正确!", num, map[property.Name])); } break; case "ProvinceCity": if (!ImportUtil.GetValue(record, map, "Bank").Equals("工商银行")) { if (String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name))) { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}为空!", num, map[property.Name])); } else { property.SetValue(personal, ImportUtil.GetValue(record, map, property.Name)); } } break; case "CityField": if (!ImportUtil.GetValue(record, map, "Bank").Equals("工商银行")) { if (String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name))) { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}为空!", num, map[property.Name])); } else { property.SetValue(personal, ImportUtil.GetValue(record, map, property.Name)); } } break; default: break; } } } else if (personal.PaymentType.Equals("现金支付")) { var properties = personal.GetType().GetProperties(); foreach (var property in properties) { switch (property.Name) { case "Name": case "CertificateType": case "CertificateID": case "Company": case "Tele": case "PersonType": case "Nationality": case "Title": case "TaxOrNot": if (String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name))) { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}为空!", num, map[property.Name])); } else { property.SetValue(personal, ImportUtil.GetValue(record, map, property.Name)); } break; case "Gender": if (!ImportUtil.GetValue(record, map, "CertificateType").Equals("居民身份证")) { if (String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name))) { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}为空!", num, map[property.Name])); } else { property.SetValue(personal, ImportUtil.GetValue(record, map, property.Name)); } } break; case "Birth": if (!ImportUtil.GetValue(record, map, "CertificateType").Equals("居民身份证")) { if (String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name))) { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}为空!", num, map[property.Name])); } else { property.SetValue(personal, ImportUtil.GetValue(record, map, property.Name)); } } break; case "Amount": if (String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name))) { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}为空!", num, map[property.Name])); } else { try { var value = Convert.ToDouble(ImportUtil.GetValue(record, map, property.Name)); property.SetValue(personal, value); } catch { feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}格式不正确!", num, map[property.Name])); } } break; default: break; } } } if (feedBack.ExceptionContent.Count > 0) { list.Add(feedBack); } //格式错误验证 feedBack = new ImportFeedBack(); feedBack.ExceptionType = "格式错误"; //证件类型 String[] regCerficateType = { "居民身份证", "中国护照", "外国护照", "港澳居民来往内地通行证", "港澳居民居住证", "台湾居民来往大陆通行证", "台湾居民居住证", "外国人永久居留身份证", "外国人工作许可证(A类)", "外国人工作许可证(B类)", "外国人工作许可证(C类)" }; if (!regCerficateType.Contains(personal.CertificateType)) { feedBack.ExceptionContent.Add("第" + num + "行记录 证件类型格式有误,必须是【居民身份证,中国护照,外国护照,港澳居民来往内地通行证,港澳居民居住证,台湾居民来往大陆通行证,台湾居民居住证,外国人永久居留身份证,外国人工作许可证(A类),外国人工作许可证(B类),外国人工作许可证(C类)】之一!"); } //人员类型 String[] regPersonType = { "所内", "所外" }; if (!regPersonType.Contains(personal.PersonType)) { feedBack.ExceptionContent.Add("第" + num + "行记录 人员类型格式有误,必须是【所内、所外】之一!"); } //是否含税 String[] regTaxOrNot = { "含税", "不含税" }; if (!regTaxOrNot.Contains(personal.TaxOrNot)) { feedBack.ExceptionContent.Add("第" + num + "行记录 是否含税格式有误,必须是【含税、不含税】之一!"); } //证件号码 // Regex regCerficateID = new Regex("^\\d{15}|\\d{18}$"); //Regex regCerficateID = new Regex("^[1-9]\\d{5}[1-9]9\\d{4}3[0-1]\\d{4}$|^[1-9]\\d{5}[1-9]9\\d{4}[0-2][0-9]\\d{4}$|^[1-9]\\d{5}[1-9]9\\d{4}3[0-1]\\d{3}X$|^[1-9]\\d{5}[1-9]9\\d{4}[0-2][0-9]\\d{3}X$|^[1-9]\\d{5}\\d{4}3[0-1]\\d{4}$|^[1-9]\\d{5}\\d{4}[0-2][0-9]\\d{4}$|^[1-9]\\d{5}\\d{4}3[0-1]\\d{3}X$|^[1-9]\\d{5}\\d{4}[0-2][0-9]\\d{3}X$"); //^[1-9]\d{5}[1-9]9\d{4}3[0-1]\d{4}$|^[1-9]\d{5}[1-9]9\d{4}[0-2][0-9]\d{4}$|^[1-9]\d{5}[1-9]9\d{4}3[0-1]\d{3}X$|^[1-9]\d{5}[1-9]9\d{4}[0-2][0-9]\d{3}X$|^[1-9]\d{5}\d{4}3[0-1]\d{4}$|^[1-9]\d{5}\d{4}[0-2][0-9]\d{4}$|^[1-9]\d{5}\d{4}3[0-1]\d{3}X$|^[1-9]\d{5}\d{4}[0-2][0-9]\d{3}X$ if (!String.IsNullOrEmpty(personal.CertificateType) && personal.CertificateType.Equals("居民身份证")) { if (personal.CertificateID.Length == 18) { if (CheckIDCard18(personal.CertificateID) == false && CheckIDCardFormat(personal.CertificateID) == false) { feedBack.ExceptionContent.Add("第" + num + "行记录 证件号码格式有误!"); } } else if (personal.CertificateID.Length == 15) { if (CheckIDCard15(personal.CertificateID) == false && CheckIDCardFormat(personal.CertificateID) == false) { feedBack.ExceptionContent.Add("第" + num + "行记录 证件号码格式有误!"); } } else if (personal.CertificateID.Length != 15 && personal.CertificateID.Length != 18) { feedBack.ExceptionContent.Add("第" + num + "行记录 证件号码格式有误!"); } else if (personal.CertificateID.Length == 18) { string tmp = personal.CertificateID.Substring(6, 2); if (!tmp.Equals("19") && !tmp.Equals("20")) { feedBack.ExceptionContent.Add("第" + num + "行记录 证件号码格式有误!"); } } //验证身份证号码后四位是否为0000 if (personal.CertificateID.Substring(personal.CertificateID.Length - 4).Equals("0000")) { feedBack.ExceptionContent.Add("第" + num + "行记录 请检查证件号码后四位!"); } } //出生日期格式验证 Regex regBirth = new Regex("^(\\d{4})(-)(\\d{2})(-)(\\d{2})$"); if (!String.IsNullOrEmpty(personal.Birth)) { if (!regBirth.IsMatch(personal.Birth)) { feedBack.ExceptionContent.Add("第" + num + "行记录 出生日期格式不正确,格式为1970-01-01,居民身份证可不填"); } } //联系电话 //Regex regPhone = new Regex("^0\\d{2,3}-?\\d{7,8}$"); //Regex regMobile = new Regex("^1[34578]\\d{9}$"); // if (!regPhone.IsMatch(personal.Tele) && !regMobile.IsMatch(personal.Tele)) //if (String.IsNullOrEmpty(personal.Tele) || System.Text.RegularExpressions.Regex.IsMatch(personal.Tele, @"^(\d{3,4}-)?\d{6,8}$")) //{ // feedBack.ExceptionContent.Add("第" + num + "行记录 联系电话格式有误!若为座机请填写区号,如01082306380"); //} //Regex regPhone = new Regex("^0\\d{2,3}-?\\d{7,8}$"); //Regex regMobile = new Regex("^1[34578]\\d{9}$"); Regex regMobile = new Regex("^1(3|4|5|6|7|8|9)\\d{9}$"); // if (!regPhone.IsMatch(personal.Tele) && !regMobile.IsMatch(personal.Tele)) if (String.IsNullOrEmpty(personal.Tele) || !regMobile.IsMatch(personal.Tele)) { feedBack.ExceptionContent.Add("第" + num + "行记录 联系电话格式有误!请填写11位手机号码"); } //银行名称是否包含在开户行详细名称中 if (!String.IsNullOrEmpty(personal.Bank) && !String.IsNullOrEmpty(personal.BankDetailName)) { if (personal.Bank.Equals("工商银行")) { if (!personal.BankDetailName.Contains(personal.Bank)) { feedBack.ExceptionContent.Add("第" + num + "行记录 所选银行与开户行详细名称不符,工商银行可不填写开户行详细名称"); } } if (!personal.Bank.Equals("工商银行")) { if (!personal.BankDetailName.Contains(personal.Bank)) { feedBack.ExceptionContent.Add("第" + num + "行记录 所选银行与开户行详细名称不符,开户行详细名称应包含所选银行名称"); } } } //银行卡号 Regex regAccountNumber1 = new Regex("^(\\d{4}[\\s\\-]?){4,5}\\d{3}$"); Regex regAccountNumber2 = new Regex("^(\\d{16}|\\d{17}|\\d{18}|\\d{19}|\\d{20}|\\d{21})$"); if (!String.IsNullOrEmpty(personal.PaymentType) && personal.PaymentType.Equals("银行转账")) { if (String.IsNullOrEmpty(personal.AccountNumber) || !regAccountNumber1.IsMatch(personal.AccountNumber)) { if (String.IsNullOrEmpty(personal.AccountNumber) || !regAccountNumber2.IsMatch(personal.AccountNumber)) { feedBack.ExceptionContent.Add("第" + num + "行记录 银行卡号格式有误!"); } } //验证号码后三位是否为000 if (personal.AccountNumber.Substring(personal.AccountNumber.Length - 3).Equals("000")) { feedBack.ExceptionContent.Add("第" + num + "行记录 请检查银行卡号后三位!"); } } if (feedBack.ExceptionContent.Count > 0) { list.Add(feedBack); } //若为所内:姓名、身份证验证 feedBack = new ImportFeedBack(); feedBack.ExceptionType = "所内所外姓名验证"; String nameAndStatus = _TaxBaseByMonthRepository.GetNameByCerID(personal.CertificateID); string[] names = nameAndStatus.Split(','); if (names[1].Equals("upper")) { feedBack.ExceptionContent.Add("第" + num + "行记录 该人员证件号码中的字母需从小写修改为大写!"); } else if (names[1].Equals("lower")) { feedBack.ExceptionContent.Add("第" + num + "行记录 该人员证件号码中的字母需从大写修改为小写!"); } if (String.IsNullOrEmpty(names[0]) && !String.IsNullOrEmpty(personal.PersonType)) { if (personal.PersonType.Equals("所内") && names[0].Equals("")) { feedBack.ExceptionContent.Add("第" + num + "行记录 该人员不是所内人员,人员类型需要填写为【所外】!"); } } else { if (!String.IsNullOrEmpty(personal.PersonType)) { if (personal.PersonType.Equals("所内") && !names[0].Equals(personal.Name.Trim())) { feedBack.ExceptionContent.Add("第" + num + "行记录 该证件号码与人员不符,请检查!"); } else { if (personal.PersonType.Equals("所外")) { feedBack.ExceptionContent.Add("第" + num + "行记录 该人员是所内人员,人员类型需要填写为【所内】!"); } } } } if (feedBack.ExceptionContent.Count > 0) { list.Add(feedBack); } //现金发放次数验证 /* * feedBack = new ImportFeedBack(); * feedBack.ExceptionType = "现金发放次数超过3次"; * int count = _TaxPerOrderRepository.GetCashCount(personal.CertificateID); * if (count >= 3) * { * feedBack.ExceptionContent.Add("第" + num + "行记录 该人员本月已发放3次现金,若需再次发放,需在支付方式为【银行转账】的申请单中填报!"); * } * if (feedBack.ExceptionContent.Count > 0) * { * list.Add(feedBack); * } * */ return(list); }
public OperationResult Import(String fileName, Service.Excel.ImportData importData) { try { string userid = ((System.Web.Security.FormsIdentity)(System.Web.HttpContext.Current.User.Identity)).Ticket.UserData; var columns = importData == null ? null : importData.Columns; var maps = ImportUtil.GetColumns(columns, new PersonalRecord()); var items = ExcelService.GetObjects(fileName, columns); if (importData != null) { String serialNumber = importData.Parameters[0].Value; String paymentType = importData.Parameters[1].Value; int num = 1; foreach (var item in items) { if (CheckAllNull(item[0]) && CheckAllNull(item[1]) && CheckAllNull(item[2]) && CheckAllNull(item[3]) && CheckAllNull(item[4]) && CheckAllNull(item[5]) && CheckAllNull(item[6]) && CheckAllNull(item[7]) && CheckAllNull(item[8])) { continue; } PersonalRecord record = new PersonalRecord(); record.SerialNumber = serialNumber; record.PaymentType = paymentType; if (paymentType.Equals("银行转账")) { if (item.Count < 16) { return(new OperationResult(OperationResultType.Error, "导入数据失败", "<li>请增加银行账户等相关信息或修改为现金支付类型</li>")); } record.AccountName = item[0]; record.Bank = item[10]; record.AccountNumber = item[11]; record.BankDetailName = item[12]; record.ProvinceCity = item[13]; record.CityField = item[14]; } List <ImportFeedBack> errors = ValidatePersonalRecord(item, num++, maps, ref record); if (errors.Count > 0) { return(new OperationResult(OperationResultType.Error, "导入数据失败", ImportUtil.ParseToHtml(errors))); } //若姓名为英文字母,不需去除字符串中的空格,若为中文,需去除空格 String nameFormat = ""; if (IsEnCh(record.Name.Trim())) { nameFormat = record.Name.Trim().Replace("\n", "").Replace("\t", "").Replace("\r", ""); } else { nameFormat = GetReplaceString(record.Name); } //插入或更新数据 _PersonalRecordRepository.InsertOrUpdate(record); //保存时同步向常用领款人表TopContacts中保存 TopContacts contact = new TopContacts(); contact.UserID = userid; contact.Name = nameFormat; contact.CertificateType = GetReplaceString(record.CertificateType); contact.CertificateID = GetReplaceString(record.CertificateID); contact.Nationality = GetReplaceString(record.Nationality); contact.PersonType = GetReplaceString(record.PersonType); contact.Tele = GetReplaceString(record.Tele); contact.Title = GetReplaceString(record.Title); contact.Company = GetReplaceString(record.Company); if (!String.IsNullOrEmpty(record.Bank)) { contact.Bank = GetReplaceString(record.Bank); } if (!String.IsNullOrEmpty(record.BankDetailName)) { contact.BankDetailName = GetReplaceString(record.BankDetailName); } if (!String.IsNullOrEmpty(record.ProvinceCity)) { contact.ProvinceCity = GetReplaceString(record.ProvinceCity); } if (!String.IsNullOrEmpty(record.CityField)) { contact.CityField = GetReplaceString(record.CityField); } if (!String.IsNullOrEmpty(record.AccountNumber)) { contact.AccountNumber = GetReplaceString(record.AccountNumber); } if (!String.IsNullOrEmpty(record.Gender)) { contact.Gender = GetReplaceString(record.Gender); } if (!String.IsNullOrEmpty(record.Birth)) { contact.Birth = GetReplaceString(record.Birth); } _TopContactsService.Insert(contact); } } return(new OperationResult(OperationResultType.Success, "导入数据成功!")); } catch (Exception ex) { _log.Error(ex); ImportFeedBack feedBack = new ImportFeedBack(); feedBack.ExceptionType = "未知错误"; feedBack.ExceptionContent.Add(ex.Message); List <ImportFeedBack> erros = new List <ImportFeedBack>(); return(new OperationResult(OperationResultType.Error, "导入数据失败!", ImportUtil.ParseToHtml(new List <ImportFeedBack>() { feedBack }))); } }