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