public void OrdersWrite()
{
FileHelperEngine engine = new FileHelperEngine(typeof(OrdersExcelType));
OrdersExcelType[] resFile = (OrdersExcelType[]) Common.ReadTest(engine, @"Good\OrdersWithOutDates.txt");
ExcelStorage provider = new ExcelStorage(typeof (OrdersExcelType));
provider.StartRow = 1;
provider.StartColumn = 1;
provider.FileName = @"c:\tempex.xls";
provider.OverrideFile = true;
provider.InsertRecords(resFile);
OrdersExcelType[] res = (OrdersExcelType[]) provider.ExtractRecords();
if (File.Exists(@"c:\tempex.xls")) File.Delete(@"c:\tempex.xls");
Assert.AreEqual(resFile.Length, res.Length);
for(int i =0; i < res.Length; i++)
{
Assert.AreEqual(resFile[i].CustomerID, res[i].CustomerID);
Assert.AreEqual(resFile[i].EmployeeID, res[i].EmployeeID);
Assert.AreEqual(resFile[i].Freight, res[i].Freight);
Assert.AreEqual(resFile[i].OrderID, res[i].OrderID);
Assert.AreEqual(resFile[i].ShipVia, res[i].ShipVia);
}
}
public string[] GetSequencesFromMaster(string filename)
{
var dataProvider = new ExcelStorage(typeof(UIDataRaw));
dataProvider.FileName = filename;
dataProvider.StartRow = 1;
dataProvider.StartColumn = 1;
var testSequences = new List<string>();
foreach (var sheetName in Utility.GetExcelSheetNames(filename))
{
dataProvider.SheetName = sheetName;
var data = (UIDataRaw[])dataProvider.ExtractRecords();
var props = data.FirstOrDefault().GetType().GetProperties();
for (int i = 0; i < data.Length; i++)
{
//get the list of test sequences
for (int x = 0; x < props.Count(); x++)
{
object val = data[i].GetType().GetProperty(props[x].Name).GetValue(data[i], null);
if (i == 0 && x > 1)
{
if (val != null)
{
testSequences.Add(val.ToString());
}
}
}
}
}
return testSequences.ToArray();
}
public void CustomersFileError()
{
ExcelStorage provider = new ExcelStorage(typeof (CustomersVerticalBar));
provider.FileName = @"t:\SureThatThisCanExistInAnyMachine.xls";
provider.ExtractRecords();
}
/// <summary>Create a new instance of the class.</summary>
/// <param name="provider">The <see cref="DataLink.DataStorage"/> used to performs the transformation.</param>
public ExcelDataLink(DataStorage provider)
{
mProvider = provider;
if (mProvider != null)
mExcelStorage = new ExcelStorage(provider.RecordType);
else
throw new ArgumentException("provider can�t be null", "provider");
}
public void CustomersWithSheetNameError()
{
ExcelStorage provider = new ExcelStorage(typeof (CustomersVerticalBar));
provider.SheetName = "SheetNotExists";
provider.FileName = @"..\data\Excel\Customers.xls";
provider.ExtractRecords();
}
public void CustomersFileError()
{
ExcelStorage provider = new ExcelStorage(typeof (CustomersVerticalBar));
provider.FileName = @"t:\SureThatThisCanExistInAnyMachine.xls";
Assert.Throws<FileNotFoundException>(()
=> provider.ExtractRecords());
}
public void NoTemplate()
{
ExcelStorage provider = new ExcelStorage(typeof (CustomersVerticalBar), 1, 1);
provider.TemplateFile = @"..\the template is not there.xls";
provider.FileName = @"output.xls";
Assert.Throws<ExcelBadUsageException>(()
=> provider.InsertRecords(new object[] {new CustomersVerticalBar()}));
}
public void CustomersWithSheetNameError()
{
ExcelStorage provider = new ExcelStorage(typeof (CustomersVerticalBar));
provider.SheetName = "SheetNotExists";
provider.FileName = @"..\data\Excel\Customers.xls";
Assert.Throws<ExcelBadUsageException>(()
=> provider.ExtractRecords());
}
public void ReadExcelStorageWithCustomSheets_ReturnsAllSheets()
{
var provider = new ExcelStorage(typeof (ExcelXlsType));
provider.FileName = TestCommon.GetPath("Excel", "ExcelWithCustomSheets.xlsx");
Assert.AreEqual(3, provider.Sheets.Count);
Assert.AreEqual("TestSheet1", provider.Sheets[0]);
Assert.AreEqual("Test-Sheet-2", provider.Sheets[1]);
Assert.AreEqual("Test Sheet 3", provider.Sheets[2]);
}
public void OneColumn()
{
ExcelStorage provider = new ExcelStorage(typeof (OneColumnType), 1, 1);
provider.FileName = @"..\data\Excel\OneColumn.xls";
object[] res = provider.ExtractRecords();
Assert.AreEqual(50, res.Length);
}
public void OrdersRead()
{
ExcelStorage provider = new ExcelStorage(typeof (OrdersExcelType), 1, 1);
provider.FileName = @"..\data\Excel\Orders.xls";
object[] res = provider.ExtractRecords();
Assert.AreEqual(830, res.Length);
}
public void Write(List<Event> results, string filename)
{
ExcelStorage storage = new ExcelStorage(typeof(Event));
storage.StartColumn = 1;
storage.StartRow = 1;
storage.FileName = filename;
storage.InsertRecords(results.ToArray());
}
public void OneColumnIgnore()
{
ExcelStorage provider = new ExcelStorage(typeof (OneColumnType), 1, 1);
provider.ErrorManager.ErrorMode = ErrorMode.IgnoreAndContinue;
provider.FileName = @"..\data\Excel\OneColumnError.xls";
object[] res = provider.ExtractRecords();
Assert.AreEqual(36, res.Length);
Assert.AreEqual(0, provider.ErrorManager.ErrorCount);
}
/// <summary>Create a new instance of the class.</summary>
/// <param name="provider">The <see cref="DataLink.DataStorage"/> used to performs the transformation.</param>
public ExcelDataLink(DataStorage provider)
{
mProvider = provider;
if (mProvider != null)
{
mExcelStorage = new ExcelStorage(provider.RecordType);
}
else
{
throw new ArgumentException("provider can´t be null", "provider");
}
}
private static ExcelXlsType[] ReadFromExcelStorage(string fileName, int stopAfterEmptyRows)
{
var provider = new ExcelStorage(typeof(ExcelXlsType));
provider.FileName = TestCommon.GetPath("Excel", fileName);
provider.SheetName = "Sheet1";
provider.StartRow = 1;
provider.ExcelReadStopAfterEmptyRows = stopAfterEmptyRows;
return (ExcelXlsType[])provider.ExtractRecords();
}
public void CustomersRead2()
{
ExcelStorage provider = new ExcelStorage(typeof (CustomersVerticalBar));
provider.StartRow = 3;
provider.StartColumn = 2;
provider.FileName = @"..\data\Excel\Customers2.xls";
object[] res = provider.ExtractRecords();
Assert.AreEqual(91, res.Length);
}
public void OrdersDateRead()
{
ExcelStorage provider = new ExcelStorage(typeof (OrdersDateExcelType), 1, 1);
provider.FileName = @"..\data\Excel\OrdersDate.xls";
OrdersDateExcelType[] res = (OrdersDateExcelType[]) provider.ExtractRecords();
Assert.AreEqual(830, res.Length);
Assert.AreEqual(new DateTime(2006, 1, 1), res[0].OrderDate);
Assert.AreEqual(new DateTime(2006, 3, 21), res[79].OrderDate);
Assert.AreEqual(new DateTime(2007, 2, 4), res[399].OrderDate);
Assert.AreEqual(new DateTime(2008, 4, 9), res[829].OrderDate);
}
public void EnumConverter()
{
ExcelStorage provider = new ExcelStorage(typeof(SmallEnumType), 1, 1);
provider.FileName = @"..\data\Excel\OneColumnEnum.xls";
SmallEnumType[] res = (SmallEnumType[]) provider.ExtractRecords();
Assert.AreEqual(10, res.Length);
Assert.AreEqual(NetVisibility.Public, res[0].Visibility);
Assert.AreEqual(NetVisibility.Private, res[1].Visibility);
Assert.AreEqual(NetVisibility.Protected, res[9].Visibility);
}
public void CustomersWithSheetName2()
{
ExcelStorage provider = new ExcelStorage(typeof (CustomersVerticalBar));
provider.SheetName = "SheetWith10";
provider.StartRow = 1;
provider.StartColumn = 1;
provider.FileName = @"..\data\Excel\Customers.xls";
object[] res = provider.ExtractRecords();
Assert.AreEqual(10, res.Length);
}
public void OneColumnSave()
{
ExcelStorage provider = new ExcelStorage(typeof (OneColumnType), 1, 1);
provider.ErrorManager.ErrorMode = ErrorMode.SaveAndContinue;
provider.FileName = @"..\data\Excel\OneColumnError.xls";
object[] res = provider.ExtractRecords();
Assert.AreEqual(36, res.Length);
Assert.AreEqual(4, provider.ErrorManager.ErrorCount);
Assert.AreEqual(8, provider.ErrorManager.Errors[0].LineNumber);
Assert.AreEqual(16, provider.ErrorManager.Errors[1].LineNumber);
Assert.AreEqual(20, provider.ErrorManager.Errors[2].LineNumber);
Assert.AreEqual(28, provider.ErrorManager.Errors[3].LineNumber);
}
public void CustomersRead()
{
ExcelStorage provider = new ExcelStorage(typeof (CustomersVerticalBar));
provider.StartRow = 3;
provider.StartColumn = 2;
provider.FileName = @"..\data\Excel\Customers.xls";
long start = DateTime.Now.Ticks;
object[] res = provider.ExtractRecords();
TimeSpan ts = new TimeSpan(DateTime.Now.Ticks - start);
Console.WriteLine(ts.TotalSeconds);
Assert.AreEqual(91, res.Length);
}
public SequenceGroup GetCommandSequences(string filename, string[] sequences)
{
var sg = new SequenceGroup();
ExcelStorage dataProvider = new ExcelStorage(typeof(UICommand));
dataProvider.StartRow = 2;
dataProvider.StartColumn = 1;
dataProvider.FileName = filename;
foreach (var sheetName in sequences)
{
dataProvider.SheetName = sheetName;
var commands = (UICommand[])dataProvider.ExtractRecords();
sg.Sequences.Add(sheetName, new UICommandContainer(sheetName, commands.Where(x=>x.Enabled.ToLower()=="y" || x.Enabled.ToLower()=="yes").ToArray()));
}
return sg;
}
public void EnumConverterBad()
{
ExcelStorage provider = new ExcelStorage(typeof(SmallEnumType), 4, 2);
provider.FileName = @"..\data\Excel\OneColumnEnumBad.xls";
provider.ErrorManager.ErrorMode = ErrorMode.SaveAndContinue;
SmallEnumType[] res = (SmallEnumType[]) provider.ExtractRecords();
Assert.AreEqual(9, res.Length);
Assert.AreEqual(NetVisibility.Public, res[0].Visibility);
Assert.AreEqual(NetVisibility.Private, res[1].Visibility);
Assert.AreEqual(NetVisibility.Protected, res[8].Visibility);
Assert.AreEqual(1, provider.ErrorManager.ErrorCount);
Assert.AreEqual(8, provider.ErrorManager.Errors[0].LineNumber);
Assert.AreEqual("BadValue", provider.ErrorManager.Errors[0].RecordString);
}
public DataBucket GetData(string filename)
{
var dataBucket = new DataBucket();
if (!string.IsNullOrEmpty(filename))
{
foreach (var sheetName in Utility.GetExcelSheetNames(filename))
{
if (IsDataTable(filename, sheetName))
{
var dataProvider = new ExcelStorage(typeof(UIDataRaw));
dataProvider.FileName = filename;
dataProvider.StartRow = 1;
dataProvider.StartColumn = 1;
dataProvider.SheetName = sheetName;
var d1 = new UIData(dataProvider.SheetName, (UIDataRaw[])dataProvider.ExtractRecords());
if (!dataBucket.DataTables.ContainsKey(d1.DataName.ToLower()))
{
dataBucket.DataTables.Add(d1.DataName.ToLower(), d1);
}
}
else
{
var dataProvider = new ExcelStorage(typeof(UIMappingValueRaw));
dataProvider.SheetName = sheetName;
dataProvider.FileName = filename;
dataProvider.StartRow = 2;
dataProvider.StartColumn = 1;
dataBucket.DataValues.Add(dataProvider.SheetName.ToLower(), new Dictionary<string, string>());
var d1 = (UIMappingValueRaw[])dataProvider.ExtractRecords();
foreach (var uiMappingValueRaw in d1)
{
string key = uiMappingValueRaw.Key.ToLower().TrimEnd();
if (!dataBucket.DataValues[dataProvider.SheetName.ToLower()].ContainsKey(key))
dataBucket.DataValues[dataProvider.SheetName.ToLower()].Add(key, uiMappingValueRaw.Value);
}
}
}
}
return dataBucket;
}
/// <summary>
/// Maakt excel file met alle stories van de projectbacklog en geeft filename terug
/// </summary>
/// <param name="project">Project</param>
public string ExportProjectBacklog(Project project)
{
SetCultureInfo();
string fileName = String.Format("ProductBacklog_{0}_{1}.xls", DateTime.Now.ToString("yyyy-MM-dd") ,project.Name);
ExcelStorage provider = new ExcelStorage(typeof(ProductBackLog));
provider.StartRow = 2;
provider.StartColumn = 1;
provider.FileName = ConfigurationManager.AppSettings["exportLocation"] + fileName;
provider.TemplateFile = ConfigurationManager.AppSettings["productBackLogTemplate"];
List<ProductBackLog> res = new List<ProductBackLog>();
foreach (Story story in project.Stories)
{
ProductBackLog row = new ProductBackLog();
row.Id = story.Id.ToString();
row.Title = story.Title;
row.Priority = Enum.GetName(typeof(Priority), story.ProductBacklogPriority);
row.Type = Enum.GetName(typeof(StoryType), story.StoryType);
row.Description = story.Description;
row.Points = Enum.GetName(typeof(StoryPoint), story.StoryPoints);
row.Estimation = TimeSpanInMinuten(story.Estimation).ToString();
row.Tasks = story.Tasks.Count.ToString();
res.Add(row);
}
provider.InsertRecords(res.ToArray());
RestoreCultureInfo();
return fileName;
}
/// <summary>
/// Maakt excel file met alle stories van de sprintbacklog en geeft filename terug
/// </summary>
/// <param name="sprint">Sprint</param>
public string ExportSprintBacklog(Sprint sprint)
{
SetCultureInfo();
string fileName = String.Format("SprintBacklog_{0}_{1}.xls", DateTime.Now.ToString("yyyy-MM-dd"), sprint.Doel);
ExcelStorage provider = new ExcelStorage(typeof(SprintBackLog));
provider.StartRow = 2;
provider.StartColumn = 1;
provider.FileName = ConfigurationManager.AppSettings["exportLocation"] + fileName;
provider.TemplateFile = ConfigurationManager.AppSettings["sprintBackLogTemplate"];
List<SprintBackLog> res = new List<SprintBackLog>();
foreach (SprintStory sprintStory in sprint.SprintStories)
{
SprintBackLog row = new SprintBackLog();
row.Id = sprintStory.Id.ToString();
row.Title = sprintStory.Story.Titel;
row.Sprintprio = Enum.GetName(typeof(Prioriteit), sprintStory.SprintBacklogPrioriteit);
row.Projectprio = Enum.GetName(typeof(Prioriteit), sprintStory.Story.ProductBacklogPrioriteit);
row.Type = Enum.GetName(typeof(StoryType), sprintStory.Story.StoryType);
row.Omschrijving = sprintStory.Story.Omschrijving;
row.Punten = Enum.GetName(typeof(StoryPoint), sprintStory.Story.StoryPoints);
row.Schatting = TimeSpanInMinuten(sprintStory.Story.Schatting).ToString();
row.Taken = sprintStory.Story.Tasks.Count.ToString();
res.Add(row);
}
provider.InsertRecords(res.ToArray());
RestoreCultureInfo();
return fileName;
}
public void OrdersReadWithErrors()
{
ExcelStorage provider = new ExcelStorage(typeof (OrdersExcelType), 1, 1);
provider.FileName = @"..\data\Excel\Orders.xls";
provider.ErrorManager.ErrorMode = ErrorMode.SaveAndContinue;
object[] res = provider.ExtractRecords();
Assert.AreEqual(830, res.Length);
}
/// <summary>
/// Maakt excel file met alle stories en taken van de sprint cardwall en geeft filename terug
/// </summary>
/// <param name="sprint">Sprint</param>
public string ExportSprintCardwall(Sprint sprint)
{
SetCultureInfo();
string fileName = String.Format("SprintCardwall_{0}_{1}.xls", DateTime.Now.ToString("yyyy-MM-dd"), sprint.Goal);
ExcelStorage provider = new ExcelStorage(typeof(Cardwall));
provider.StartRow = 3;
provider.StartColumn = 1;
provider.FileName = ConfigurationManager.AppSettings["exportLocation"] + fileName;
provider.TemplateFile = ConfigurationManager.AppSettings["sprintCardwallTemplate"];
List<Cardwall> res = new List<Cardwall>();
foreach (SprintStory sprintStory in sprint.SprintStories)
{
Cardwall storyRow = new Cardwall();
storyRow.StoryId = sprintStory.Id.ToString();
storyRow.StoryTitle = sprintStory.Story.Title;
storyRow.StoryPriority = Enum.GetName(typeof(Priority), sprintStory.Story.ProductBacklogPriority);
storyRow.StoryEstimatedHours = TimeSpanInMinuten(sprintStory.Story.Estimation).ToString();
res.Add(storyRow);
foreach (Task task in sprintStory.Story.Tasks)
{
Cardwall taskRow = new Cardwall();
switch(task.State)
{
case State.Open:
taskRow.TaskOpenId = task.Id.ToString();
taskRow.TaskOpenTitle = task.Title;
taskRow.TaskOpenAssignee = task.AssignedUserName;
taskRow.TaskOpenTimeSpent = TimeSpanInMinuten(task.TotalTimeSpent()).ToString();
break;
case State.Taken:
taskRow.TaskInProgressId = task.Id.ToString();
taskRow.TaskInProgressTitle = task.Title;
taskRow.TaskInProgressAssignee = task.AssignedUserName;
taskRow.TaskInProgressTimeSpent = TimeSpanInMinuten(task.TotalTimeSpent()).ToString();
break;
case State.Closed:
taskRow.TaskDoneId = task.Id.ToString();
taskRow.TaskDoneTitle = task.Title;
taskRow.TaskDoneAssignee = task.AssignedUserName;
taskRow.TaskDoneTimeSpent = TimeSpanInMinuten(task.TotalTimeSpent()).ToString();
break;
}
res.Add(taskRow);
}
}
provider.InsertRecords(res.ToArray());
RestoreCultureInfo();
return fileName;
}
public void CustomersWrite()
{
ExcelStorage provider = new ExcelStorage(typeof (CustomersVerticalBar));
provider.StartRow = 3;
provider.StartColumn = 2;
provider.FileName = @"..\data\Excel\Customers.xls";
object[] res = provider.ExtractRecords();
long start = DateTime.Now.Ticks;
provider.FileName = @"salida.xls";
provider.StartRow = 10;
provider.StartColumn = 5;
provider.InsertRecords(res);
TimeSpan ts = new TimeSpan(DateTime.Now.Ticks - start);
Console.WriteLine(ts.TotalSeconds);
if (File.Exists(@"salida.xls")) File.Delete(@"salida.xls");
}
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;
}
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;
}
