public static String GetValue(LinqToExcel.Row record, Dictionary <String, String> map, String name)
{
var columnName = name;
if (map.ContainsKey(name))
{
columnName = map[name];
}
if (record.ColumnNames.Contains(columnName))
{
return(record[columnName]);
}
else
{
return(null);
}
}
public static List <ImportFeedBack> ValidateImportRecord <T>(LinqToExcel.Row record, int num, Dictionary <String, String> map, ref T personal)
{
List <ImportFeedBack> list = new List <ImportFeedBack>();
ImportFeedBack feedBack = null;
//非空验证
feedBack = new ImportFeedBack();
feedBack.ExceptionType = "空值";
var requiredProps = personal.GetType().GetProperties().Where(
prop => Attribute.IsDefined(prop, typeof(System.ComponentModel.DataAnnotations.RequiredAttribute)));
var otherProperties = personal.GetType().GetProperties().Where(prop => !requiredProps.Contains(prop));
foreach (var property in requiredProps)
{
if (String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name)))
{
feedBack.ExceptionContent.Add(String.Format("第{0}行记录 {1}为空！", num, map[property.Name]));
}
else
{
SetValue(property, personal, ImportUtil.GetValue(record, map, property.Name));
}
}
foreach (var property in otherProperties)
{
switch (property.Name)
{
case "Id":
case "DT_RowId":
break;
case "UpdateDate":
if (!String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name)))
{
SetValue(property, personal, ImportUtil.GetValue(record, map, property.Name));
}
else
{
SetValue(property, personal, DateTime.Now.ToString());
}
break;
default:
{
if (!String.IsNullOrEmpty(ImportUtil.GetValue(record, map, property.Name)))
{
SetValue(property, personal, ImportUtil.GetValue(record, map, property.Name));
}
}
break;
}
}
if (feedBack.ExceptionContent.Count > 0)
{
list.Add(feedBack);
}
return(list);
}
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);
}
