public ImportResult ImportBySchool(string key, int schoolId, string level, int marketingYear, int infoSourceId, string filename, bool isDelOldData)
{
ImportResult r = new ImportResult();
if (isDelOldData) {
this.DeleteBySchoolId(schoolId);
}
#region 初始化 Repository
this.ProvRepository = new ProvinceRepository(this.Db);
this.CityRepository = new CityRepository(this.Db);
this.DistrictRepository = new DistrictRepository(this.Db);
this.SchoolRepository = new SchoolRepository(this.Db);
this.ImportCustomerRepository = new ImportCustomerRepository(this.Db);
this.ImportDupliateRepository = new ImportDupliateRepository(this.Db);
#endregion
#region 读取初始数据
var provinces = this.ProvRepository.FindAll();
var cities = this.CityRepository.FindAll();
var districts = this.DistrictRepository.FindAll();
var schools = this.SchoolRepository.FindAll();
#endregion
ExcelStorage provider = new ExcelStorage(typeof(QuestionaryData), filename, 3, 1);
var data = provider.ExtractRecords() as QuestionaryData[];
var toImportList = new List<ImportCustomer>();
var toUpdateCustomers = new List<Customer>();
var toAddCustomers = new List<Customer>();
var duplicates = new List<ImportDupliate>();
var currentCustomers = this.FindBySchoolId(schoolId);
r.TotalQty = data.Length;
foreach (var d in data) {
#region 构建对象
var ic = new ImportCustomer {
Address = d.Address,
CityName = d.CityName,
Clazz = d.Clazz,
DistrictName = d.DistrictName,
Gender = d.Gender,
ImportKey = key,
ImportType = "按学校导入",
InfoSource = infoSourceId,
IsProcessed = false,
Level = level,
MarketYear = marketingYear,
Mobile = d.Mobile,
ProvinceName = d.ProvinceName,
QQ = d.QQ,
SchoolId = schoolId,
SchoolName = "",
Tel = d.TelPrefix + "-" + d.Telephone,
Name = d.Name
};
if (String.IsNullOrEmpty(d.TelPrefix) && String.IsNullOrEmpty(d.Telephone)) {
ic.Tel = null;
}
#endregion
toImportList.Add(ic);
#region 读取省、市、区和学校的信息
var prov = provinces.FirstOrDefault(o => o.Name.Equals(ic.ProvinceName));
if (prov == null) {
ic.ErrorMsg = "找不到省份：" + ic.ProvinceName;
r.ErrorQty++;
continue;
}
ic.ProvinceId = prov.Id;
var city = cities.FirstOrDefault(o => o.ProvinceId.Equals(ic.ProvinceId) && o.Name.Equals(ic.CityName));
if (city == null) {
ic.ErrorMsg = "在"+ic.ProvinceName+"里找不到"+ic.CityName;
r.ErrorQty++;
continue;
}
ic.CityId = city.Id;
var district = districts.FirstOrDefault(o => o.CityId.Equals(ic.CityId) && o.Name.Equals(ic.DistrictName));
if (district == null) {
ic.ErrorMsg = "在" + ic.CityName + "里找不到"+ic.DistrictName;
r.ErrorQty++;
continue;
}
ic.DistrictId = district.Id;
ic.SchoolName = schools.First(o => o.Id.Equals(ic.SchoolId)).Name;
#endregion
#region 同校有同名且同电话（或手机）的，补全数据
var customer = currentCustomers.FirstOrDefault(o => o.Name.Equals(ic.Name) &&
((o.Telephone != null && o.Telephone.Equals(ic.Tel)) || (o.Mobile != null && o.Mobile.Equals(ic.Mobile))));
if (customer != null) {
if (String.IsNullOrEmpty(customer.Address)) {
customer.Address = ic.Address;
}
if (String.IsNullOrEmpty(customer.Telephone)) {
customer.Telephone = ic.Tel;
}
if (String.IsNullOrEmpty(customer.Mobile)) {
customer.Mobile = ic.Mobile;
}
if (String.IsNullOrEmpty(customer.QQ)) {
customer.QQ = ic.QQ;
}
toUpdateCustomers.Add(customer);
ic.IsProcessed = true;
r.UpdatedQty++;
continue;
}
#endregion
#region 同样有同名或同电话或同手机的，认定为重复数据
var dCustomers = currentCustomers.Where(o => o.Name.Equals(ic.Name)
//|| (o.Telephone !=null && o.Telephone.Length>0 && ic.Tel!=null && ic.Tel.Length>0 && o.Telephone.Equals(ic.Tel))
//|| (o.Mobile != null && o.Mobile.Length>0 && ic.Mobile !=null && ic.Mobile.Length>0 && o.Mobile.Equals(ic.Mobile))
).ToList();
if (dCustomers.Count == 0) {
#region 无重复
customer = new Customer {
Address = ic.Address,
QQ = ic.QQ,
Mobile = ic.Mobile,
Telephone = ic.Tel,
Name = ic.Name,
Clazz = ic.Clazz,
DistrictId = ic.DistrictId,
EduLevel = level,
Gender = ic.Gender,
SmallInfoSourceId = infoSourceId,
IsImport = true,
MarketYear = marketingYear,
SchoolId = schoolId,
TeleSalesTimes = 0,
Important = false,
IsClosed = false,
IsDinWei = false,
IsGaoKao = false,
ConsultType = "未电访",
IsLeaderFollow = false,
IsPay=false,
IsRefund=false,
IsDropIn=false,
IsSignUp=false,
Status = "未上门"
};
toAddCustomers.Add(customer);
ic.IsProcessed = true;
r.AddedQty++;
#endregion
continue;
} else {
r.DuplicateQty++;
#region 有重复
// 处理重复的数据
var duplicate = new ImportDupliate {
ImportCustomer = ic,
ImportKey = key,
Mobile = ic.Mobile,
Name = ic.Name,
SchoolName = ic.SchoolName,
Tel = ic.Tel
};
duplicates.Add(duplicate);
dCustomers.ForEach(o => {
duplicate = new ImportDupliate {
CustomerId=o.Id,
ImportKey = key,
Mobile = o.Mobile,
Name = o.Name,
SchoolName = o.School.Name,
Tel = o.Telephone,
Score=o.GaoKaoScore
};
duplicates.Add(duplicate);
});
#endregion
}
#endregion
}
toImportList.ForEach(o => { this.ImportCustomerRepository.Add(o); });
toUpdateCustomers.ForEach(o => { this.Repository.Update(o); });
toAddCustomers.ForEach(o => { this.Repository.Add(o); });
duplicates.ForEach(o => { this.ImportDupliateRepository.Add(o); });
this.Db.Save();
r.Successful = true;
return r;
}
public ImportResult ImportByChannel(string key, string level, int marketingYear, int infoSourceId, string filename)
{
ImportResult r = new ImportResult();
#region 初始化 Repository
this.ProvRepository = new ProvinceRepository(this.Db);
this.CityRepository = new CityRepository(this.Db);
this.DistrictRepository = new DistrictRepository(this.Db);
this.SchoolRepository = new SchoolRepository(this.Db);
this.ImportCustomerRepository = new ImportCustomerRepository(this.Db);
this.ImportDupliateRepository = new ImportDupliateRepository(this.Db);
#endregion
#region 读取初始数据
var provinces = this.ProvRepository.FindAll();
var cities = this.CityRepository.FindAll();
var districts = this.DistrictRepository.FindAll();
var schools = this.SchoolRepository.FindAll();
#endregion
ExcelStorage provider = new ExcelStorage(typeof(ChannelData), filename, 2, 1);
var data = provider.ExtractRecords() as ChannelData[];
var toImportList = new List<ImportCustomer>();
var toUpdateCustomers = new List<Customer>();
var toAddCustomers = new List<Customer>();
var duplicates = new List<ImportDupliate>();
//var currentCustomers = this.FindBySchoolId(schoolId);
r.TotalQty = data.Length;
foreach (var d in data) {
#region 构建对象
var ic = new ImportCustomer {
ProvinceName = d.ProvinceName,
CityName = d.CityName,
DistrictName = d.DistrictName,
SchoolName = d.SchoolName,
Name = d.Name,
Gender = d.Gender,
Score = String.IsNullOrEmpty(d.Score) ? null : (int?)Int32.Parse(d.Score.Trim()),
Address = d.Address,
Postcode = d.Postcode,
Contact = d.Contact,
Tel = d.Telephone,
Mobile = d.Mobile,
ImportKey = key,
ImportType = "按渠道导入",
InfoSource = infoSourceId,
IsProcessed = false,
Level = level,
MarketYear = marketingYear,
QQ = d.QQ,
Clazz = d.Clazz
};
#endregion
toImportList.Add(ic);
#region 读取省、市、区和学校的信息
var prov = provinces.FirstOrDefault(o => o.Name.Equals(ic.ProvinceName));
if (prov == null) {
ic.ErrorMsg = "找不到省份：" + ic.ProvinceName;
r.ErrorQty++;
continue;
}
ic.ProvinceId = prov.Id;
var city = cities.FirstOrDefault(o => o.ProvinceId.Equals(ic.ProvinceId) && o.Name.Equals(ic.CityName));
if (city == null) {
ic.ErrorMsg = "在"+ic.ProvinceName+"里找不到"+ic.CityName;
r.ErrorQty++;
continue;
}
ic.CityId = city.Id;
var district = districts.FirstOrDefault(o => o.CityId.Equals(ic.CityId) && o.Name.Equals(ic.DistrictName));
if (district == null) {
ic.ErrorMsg = "在" + ic.CityName + "里找不到"+ic.DistrictName;
r.ErrorQty++;
continue;
}
ic.DistrictId = district.Id;
var school = schools.FirstOrDefault(o => o.DistrictId.Equals(ic.DistrictId) && o.Name.Equals(d.SchoolName));
if (school == null) {
ic.ErrorMsg = "在" + ic.DistrictName + "里找不到" + ic.SchoolName;
r.ErrorQty++;
continue;
}
ic.SchoolId = school.Id;
#endregion
#region 同校有同名且同电话（或手机）的，补全数据
var customer = this.Repository.FindByName(ic.Name).FirstOrDefault(o =>
(o.SchoolId.HasValue && o.SchoolId.Value.Equals(ic.SchoolId.Value) ) &&
((o.Telephone != null && o.Telephone.Equals(ic.Tel)) || (o.Mobile != null && o.Mobile.Equals(ic.Mobile))));
if (customer != null) {
if (String.IsNullOrEmpty(customer.Address)) {
customer.Address = ic.Address;
}
if (String.IsNullOrEmpty(customer.Telephone)) {
customer.Telephone = ic.Tel;
}
if (String.IsNullOrEmpty(customer.Mobile)) {
customer.Mobile = ic.Mobile;
}
if (!customer.GaoKaoScore.HasValue) {
customer.GaoKaoScore = ic.Score;
}
toUpdateCustomers.Add(customer);
ic.IsProcessed = true;
r.UpdatedQty++;
continue;
}
#endregion
#region 同样有同名或同电话或同手机的，认定为重复数据
var dCustomers = this.FindBySchoolId(ic.SchoolId).Where(o => o.Name.Equals(ic.Name)
|| (o.Telephone != null && o.Telephone.Length>0 && ic.Tel!=null && ic.Tel.Length>0 && o.Telephone.Equals(ic.Tel))
|| (o.Mobile != null && o.Mobile.Length>0 && ic.Mobile!=null && ic.Mobile.Length>0 && o.Mobile.Equals(ic.Mobile))
).ToList();
if (dCustomers.Count == 0) {
#region 无重复
customer = new Customer {
DistrictId = ic.DistrictId,
SchoolId = ic.SchoolId,
Name = ic.Name,
Gender = ic.Gender,
GaoKaoScore = ic.Score,
Address = ic.Address,
Postcode = ic.Postcode,
Telephone = ic.Tel,
Mobile = ic.Mobile,
EduLevel = level,
SmallInfoSourceId = infoSourceId,
IsImport = true,
MarketYear = marketingYear,
TeleSalesTimes = 0,
Important = false,
IsClosed = false,
IsDinWei = false,
IsGaoKao = false,
IsLeaderFollow = false,
IsPay = false,
IsRefund = false,
IsDropIn = false,
IsSignUp = false,
Status = "未上门",
QQ = ic.QQ,
Clazz = ic.Clazz
};
toAddCustomers.Add(customer);
ic.IsProcessed = true;
r.AddedQty++;
#endregion
continue;
} else {
//省 市 区 学校 姓名 性别 总分 地址 邮编 联系人 固定电话 移动电话
r.DuplicateQty++;
#region 有重复
//var dCustomers = this.FindBySchoolId(ic.SchoolId).Where(o => o.Name.Equals(ic.Name) ||
//(o.Telephone != null && o.Telephone.Equals(ic.Tel)) || (o.Mobile != null && o.Mobile.Equals(ic.Mobile))).ToList();
// 处理重复的数据
var duplicate = new ImportDupliate {
ImportCustomer = ic,
ImportKey = key,
Mobile = ic.Mobile,
Name = ic.Name,
SchoolName = ic.SchoolName,
Tel = ic.Tel,
Score =ic.Score,
ErrorMsg = String.Empty
};
dCustomers.ForEach(o => {
var em = String.Empty;
if (duplicate.Name.Equals(o.Name)) {
em += "姓名重复,";
}
if (duplicate.Tel != null && o.Telephone != null && duplicate.Tel.Equals(o.Telephone)) {
em += "家庭电话重复,";
}
if (duplicate.Mobile != null && o.Mobile != null && duplicate.Mobile.Equals(o.Mobile)) {
em += "手机号码重复,";
}
duplicate.ErrorMsg += em;
var dup = new ImportDupliate {
CustomerId=o.Id,
ImportKey = key,
Mobile = o.Mobile,
Name = o.Name,
SchoolName = o.School.Name,
Tel = o.Telephone,
Score=o.GaoKaoScore,
ErrorMsg = em
};
duplicates.Add(dup);
});
duplicates.Add(duplicate);
#endregion
}
#endregion
}
toImportList.ForEach(o => { this.ImportCustomerRepository.Add(o); });
toUpdateCustomers.ForEach(o => { this.Repository.Update(o); });
toAddCustomers.ForEach(o => { this.Repository.Add(o); });
duplicates.ForEach(o => { this.ImportDupliateRepository.Add(o); });
this.Db.Save();
r.Successful = true;
return r;
}
