public ServiceResponse SendBulkAction(IUnitOfWork uow, SendBulkActionRequest request)
{
List <int> CustomerIds = request.CustomerIds;
var p = Entities.CustomerRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
for (var row = 0; row < CustomerIds.Count; row++)
{
try
{
var customerId = CustomerIds[row];
var customer = uow.Connection.TryFirst <Entities.CustomerRow>(q => q
.Select(p.CustomerId)
.Where(p.CustomerId == customerId));
if (customer != null)
{
// avoid assignment errors
customer.TrackWithChecks = false;
}
customer.SaleUserId = request.SaleId;
customer.IsLeaderSent = true;
if (customer.CustomerId == null)
{
new Repositories.CustomerRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = customer
});
response.Inserted = response.Inserted + 1;
}
else
{
new Repositories.CustomerRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = customer,
EntityId = customer.CustomerId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var p = PiezaRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 1; row <= worksheet.Dimension.End.Row; row += 8)
{
try
{
var prueba = new PiezaRow
{
};
prueba.Pieza = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
prueba.GrosTab = Convert.ToDecimal(worksheet.Cells[row + 2, 1].Value ?? 0);
prueba.Enchapado = Convert.ToString(worksheet.Cells[row + 3, 1].Value ?? "");
prueba.MtsEnchapado = Convert.ToDecimal(worksheet.Cells[row + 5, 1].Value ?? 0);
prueba.MtsCorte = Convert.ToDecimal(worksheet.Cells[row + 7, 1].Value ?? 0);
new PiezaRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = prueba
});
response.Inserted = response.Inserted + 1;
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var l = LQASRow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
var provName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
var lotNo = Convert.ToInt16(worksheet.Cells[row, 5].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var LQAS = uow.Connection.TryFirst <LQASRow>(q => q
.Select(l.LqasId)
.Where(l.DistrictDcode == districtName & l.RoundName == roundName1 & l.Province == provName & l.LotNo == lotNo));
if (LQAS == null)
{
LQAS = new LQASRow
{
DistrictDcode = districtName,
RoundName = roundName1,
Province = provName,
LotNo = lotNo
}
}
;
else
{
// avoid assignment errors
LQAS.TrackWithChecks = false;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
LQAS.RoundId = round.RoundId.Value;
}
else
{
LQAS.RoundId = null;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
var provincename = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName));
if (district == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
districtName + "' is not found!");
continue;
}
LQAS.DistrictId = district.DistrictId.Value;
}
else
{
LQAS.DistrictId = null;
}
LQAS.LotNo = Convert.ToInt16(worksheet.Cells[row, 5].Value ?? 0);
LQAS.NoVaccChildren = Convert.ToInt16(worksheet.Cells[row, 6].Value ?? 0);
LQAS.Comments = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
LQAS.TenantId = Users.TenantId.Value;
}
else
{
LQAS.TenantId = null;
}
if (LQAS.LqasId == null)
{
new LQASRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = LQAS
});
response.Inserted = response.Inserted + 1;
}
else
{
new LQASRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = LQAS,
EntityId = LQAS.LqasId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.StackTrace);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
throw new ArgumentOutOfRangeException("filename");
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var p = ProductRow.Fields;
var s = SupplierRow.Fields;
var c = CategoryRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List<string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var productName = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
if (productName.IsTrimmedEmpty())
continue;
var product = uow.Connection.TryFirst<ProductRow>(q => q
.Select(p.ProductID)
.Where(p.ProductName == productName));
if (product == null)
product = new ProductRow
{
ProductName = productName
};
else
{
// avoid assignment errors
product.TrackWithChecks = false;
}
var supplierName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(supplierName))
{
var supplier = uow.Connection.TryFirst<SupplierRow>(q => q
.Select(s.SupplierID)
.Where(s.CompanyName == supplierName));
if (supplier == null)
{
response.ErrorList.Add("Error On Row " + row + ": Supplier with name '" +
supplierName + "' is not found!");
continue;
}
product.SupplierID = supplier.SupplierID.Value;
}
else
product.SupplierID = null;
var categoryName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (!string.IsNullOrWhiteSpace(categoryName))
{
var category = uow.Connection.TryFirst<CategoryRow>(q => q
.Select(c.CategoryID)
.Where(c.CategoryName == categoryName));
if (category == null)
{
response.ErrorList.Add("Error On Row " + row + ": Category with name '" +
categoryName + "' is not found!");
continue;
}
product.CategoryID = category.CategoryID.Value;
}
else
product.CategoryID = null;
product.QuantityPerUnit = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
product.UnitPrice = Convert.ToDecimal(worksheet.Cells[row, 5].Value ?? 0);
product.UnitsInStock = Convert.ToInt16(worksheet.Cells[row, 6].Value ?? 0);
product.UnitsOnOrder = Convert.ToInt16(worksheet.Cells[row, 7].Value ?? 0);
product.ReorderLevel = Convert.ToInt16(worksheet.Cells[row, 8].Value ?? 0);
if (product.ProductID == null)
{
new ProductRepository().Create(uow, new SaveWithLocalizationRequest<MyRow>
{
Entity = product
});
response.Inserted = response.Inserted + 1;
}
else
{
new ProductRepository().Update(uow, new SaveWithLocalizationRequest<MyRow>
{
Entity = product,
EntityId = product.ProductID.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return response;
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var w = WaresRow.Fields;
var c = CategoryRow.Fields;
var m = MeasureRow.Fields;
var cp = CounterpartyRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var waresName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (waresName.IsTrimmedEmpty())
{
continue;
}
var wares = uow.Connection.TryFirst <WaresRow>(q => q
.Select(w.WaresID)
.Where(w.WaresName == waresName));
if (wares == null)
{
wares = new WaresRow
{
WaresName = waresName
}
}
;
else
{
wares.TrackWithChecks = false;
}
#region Category
var categoryName = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
if (!string.IsNullOrWhiteSpace(categoryName))
{
var category = uow.Connection.TryFirst <CategoryRow>(q => q
.Select(c.CategoryID)
.Where(c.CategoryName == categoryName));
if (category == null)
{
response.ErrorList.Add("Error On Row" + row + ": Category with name '" +
categoryName + "' is not found!");
continue;
}
wares.CategoryID = category.CategoryID.Value;
}
else
{
wares.CategoryID = null;
}
#endregion Category
#region Measure
var measureName = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
if (!string.IsNullOrWhiteSpace(measureName))
{
var measure = uow.Connection.TryFirst <MeasureRow>(q => q
.Select(m.MeasureID)
.Where(m.MeasureName == measureName));
if (measure == null)
{
response.ErrorList.Add("Error On Row" + row + ": Measure with name '" +
measureName + "' is not found!");
continue;
}
wares.MeasureID = measure.MeasureID.Value;
}
else
{
wares.MeasureID = null;
}
#endregion Measure
#region Counterparty
//var counterpartyName = Convert.ToString(worksheet.Cells[row, 13].Value ?? 0);
//if (!string.IsNullOrWhiteSpace(counterpartyName))
//{
// var counterparty = uow.Connection.TryFirst<CounterpartyRow>(q => q
// .Select(cp.CounterpartyID)
// .Where(cp.CompanyName == counterpartyName));
// if(counterparty == null)
// {
// response.ErrorList.Add("Error On Row" + row + ": Counterparty with name '" +
// counterpartyName + "' is not found!");
// continue;
// }
// wares.CounterpartyID = counterparty.CounterpartyID.ToString();
//}
//else
//{
// wares.CounterpartyID = null;
//}
#endregion Counterparty
wares.WaresCode = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
wares.WaresBarcode = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
wares.WaresLabel = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
wares.Discontinued = Convert.ToBoolean(worksheet.Cells[row, 5].Value ?? "");
wares.QuantityPerUnit = Convert.ToInt32(worksheet.Cells[row, 8].Value ?? "");
wares.UnitPrice = Convert.ToDecimal(worksheet.Cells[row, 9].Value ?? 0);
wares.UnitsInStock = Convert.ToDecimal(worksheet.Cells[row, 10].Value ?? 0);
wares.UnitsOnOrder = Convert.ToDecimal(worksheet.Cells[row, 11].Value ?? 0);
wares.CounterpartyID = Convert.ToString(worksheet.Cells[row, 12].Value ?? 0);
if (wares.WaresID == null)
{
new WaresRepository().Create(uow, new SaveWithLocalizationRequest <MyRow>
{
Entity = wares
});
response.Inserted = response.Inserted + 1;
}
else
{
new WaresRepository().Update(uow, new SaveWithLocalizationRequest <MyRow>
{
Entity = wares,
EntityId = wares.WaresID.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var m = MobileTeamRow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var provName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var mobileteam = uow.Connection.TryFirst <MobileTeamRow>(q => q
.Select(m.MobileTeamId)
.Where(m.DistrictDcode == districtName & m.RoundName == roundName1 & m.Province == provName));
if (mobileteam == null)
{
mobileteam = new MobileTeamRow
{
Province = provName,
DistrictDcode = districtName,
RoundName = roundName1
}
}
;
else
{
// avoid assignment errors
mobileteam.TrackWithChecks = false;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName & d.Pname == provName));
if (district == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
districtName + "' is not found!");
continue;
}
mobileteam.DistrictId = district.DistrictId.Value;
}
else
{
mobileteam.DistrictId = null;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
mobileteam.RoundId = round.RoundId.Value;
}
else
{
mobileteam.RoundId = null;
}
mobileteam.Nomads = Convert.ToInt16(worksheet.Cells[row, 5].Value ?? 0);
mobileteam.Gypsis = Convert.ToInt16(worksheet.Cells[row, 6].Value ?? 0);
mobileteam.BlueMosque = Convert.ToInt16(worksheet.Cells[row, 7].Value ?? 0);
mobileteam.IDPs = Convert.ToInt16(worksheet.Cells[row, 8].Value ?? 0);
mobileteam.Returnees = Convert.ToInt16(worksheet.Cells[row, 9].Value ?? 0);
mobileteam.Kindergarden = Convert.ToInt16(worksheet.Cells[row, 10].Value ?? 0);
mobileteam.Madrasa = Convert.ToInt16(worksheet.Cells[row, 11].Value ?? 0);
mobileteam.EPICenters = Convert.ToInt16(worksheet.Cells[row, 12].Value ?? 0);
mobileteam.BusStation = Convert.ToInt16(worksheet.Cells[row, 13].Value ?? 0);
mobileteam.Prison = Convert.ToInt16(worksheet.Cells[row, 14].Value ?? 0);
mobileteam.MobileTeams = Convert.ToInt16(worksheet.Cells[row, 15].Value ?? 0);
mobileteam.CheckPost = Convert.ToInt16(worksheet.Cells[row, 16].Value ?? 0);
mobileteam.PrivateClinics = Convert.ToInt16(worksheet.Cells[row, 17].Value ?? 0);
mobileteam.Daramsal = Convert.ToInt16(worksheet.Cells[row, 18].Value ?? 0);
mobileteam.HotelGuestHouses = Convert.ToInt16(worksheet.Cells[row, 19].Value ?? 0);
mobileteam.Crosborder = Convert.ToInt16(worksheet.Cells[row, 20].Value ?? 0);
mobileteam.School = Convert.ToInt16(worksheet.Cells[row, 21].Value ?? 0);
mobileteam.Others = Convert.ToInt16(worksheet.Cells[row, 22].Value ?? 0);
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
mobileteam.TenantId = Users.TenantId.Value;
}
else
{
mobileteam.TenantId = null;
}
if (mobileteam.MobileTeamId == null)
{
new MobileTeamRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = mobileteam
});
response.Inserted = response.Inserted + 1;
}
else
{
new MobileTeamRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = mobileteam,
EntityId = mobileteam.MobileTeamId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse TeacherExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var t = MyRow.Fields;
var b = TextbookRow.Fields;
var c = CourseRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
//TeacherWholeData
var batchId = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (batchId.IsTrimmedEmpty())
{
continue;
}
var declaration = uow.Connection.TryFirst <MyRow>(q1 => q1
.Select(t.DeclarationId)
.Where(t.DeclarationId == -1));
if (declaration == null)
{
declaration = new MyRow
{
BatchId = batchId
};
}
else
{
declaration.TrackWithChecks = false;
}
//Textbook
var textbookID = Convert.ToString(worksheet.Cells[row, 12].Value ?? "");
if (textbookID.IsTrimmedEmpty())
{
continue;
}
var textbook = uow.Connection.TryFirst <TextbookRow>(q => q
.Select(b.TextbookNum2)
.Where(b.TextbookId == textbookID));
if (textbook == null)
{
textbook = new TextbookRow
{
TextbookId = textbookID
};
}
else
{
textbook.TrackWithChecks = false;
}
textbook.TextbookName = Convert.ToString(worksheet.Cells[row, 13].Value ?? "");
textbook.Author = Convert.ToString(worksheet.Cells[row, 14].Value ?? "");
textbook.Isbn = Convert.ToString(worksheet.Cells[row, 15].Value ?? "");
textbook.Press = Convert.ToString(worksheet.Cells[row, 16].Value ?? "");
textbook.Edition = Convert.ToString(worksheet.Cells[row, 17].Value ?? "");
textbook.PrintingCount = Convert.ToString(worksheet.Cells[row, 18].Value ?? "");
textbook.TextbookType = Convert.ToString(worksheet.Cells[row, 19].Value ?? "");
textbook.Price = Convert.ToString(worksheet.Cells[row, 20].Value ?? "");
textbook.IsSelfCompile = Convert.ToString("0");
if (textbook.TextbookNum2 == null)
{
new TextbookRepository().Create(uow, new SaveRequest <TextbookRow>
{
Entity = textbook
});
}
else
{
new TextbookRepository().Update(uow, new SaveRequest <TextbookRow>
{
Entity = textbook,
EntityId = textbook.TextbookNum2.Value
});
}
//Course
var courseId = Convert.ToString(worksheet.Cells[row, 8].Value ?? "");
if (courseId.IsTrimmedEmpty())
{
continue;
}
var course = uow.Connection.TryFirst <CourseRow>(q => q
.Select(c.CourseNum)
.Where(c.CourseId == courseId));
if (course == null)
{
course = new CourseRow
{
CourseId = courseId
};
}
else
{
course.TrackWithChecks = false;
}
course.CourseCode = Convert.ToString(worksheet.Cells[row, 10].Value ?? "空");
course.CourseName = Convert.ToString(worksheet.Cells[row, 11].Value ?? "");
if (course.CourseNum == null)
{
new CourseRepository().Create(uow, new SaveRequest <CourseRow>
{
Entity = course
});
}
else
{
new CourseRepository().Update(uow, new SaveRequest <CourseRow>
{
Entity = course,
EntityId = course.CourseNum.Value
});
}
declaration.TermCode = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
declaration.TermName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
declaration.SchoolId = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
declaration.SchoolName = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
declaration.DepartmentId = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
declaration.DepartmentName = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
declaration.EducationalType = Convert.ToString(worksheet.Cells[row, 9].Value ?? "");
declaration.ApprovedAmount = Convert.ToInt32(worksheet.Cells[row, 21].Value ?? 0);
declaration.Priority = Convert.ToString(worksheet.Cells[row, 22].Value ?? "");
declaration.Phone = Convert.ToString(worksheet.Cells[row, 23].Value ?? "");
declaration.Remarks = Convert.ToString(worksheet.Cells[row, 24].Value ?? "");
declaration.CheckState = Convert.ToString(worksheet.Cells[row, 25].Value ?? "");
declaration.DataSign = Convert.ToString(worksheet.Cells[row, 26].Value ?? "");
declaration.CourseNum = course.CourseNum.Value;
declaration.TextbookNum2 = textbook.TextbookNum2.Value;
if (declaration.DeclarationId == null)
{
new TeacherWholeDataRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = declaration
});
response.Inserted = response.Inserted + 1;
}
else
{
new TeacherWholeDataRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = declaration,
EntityId = declaration.DeclarationId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var pcm = PcmRow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var clc = ClusterRow.Fields;
var cll = ClusterLevelRow.Fields;
var cmp = CampaignTypeRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
var clusterName = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
var levelName = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
var campType = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var pcmEnt = uow.Connection.TryFirst <PcmRow>(q => q
.Select(pcm.PcmId)
.Where(pcm.DistrictDcode == districtName & pcm.RoundName == roundName1 & pcm.ClusterName == clusterName & pcm.LevelName == levelName & pcm.CampaignTypeName == campType));
if (pcmEnt == null)
{
pcmEnt = new PcmRow
{
DistrictDcode = districtName,
RoundName = roundName1,
ClusterName = clusterName,
LevelName = levelName,
CampaignTypeName = campType
}
}
;
else
{
// avoid assignment errors
pcmEnt.TrackWithChecks = false;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
pcmEnt.RoundId = round.RoundId.Value;
}
else
{
pcmEnt.RoundId = null;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName));
if (dName == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
dName + "' is not found!");
continue;
}
pcmEnt.DistrictId = district.DistrictId.Value;
}
else
{
pcmEnt.DistrictId = null;
}
var Cluster = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
if (!string.IsNullOrWhiteSpace(Cluster))
{
var round = uow.Connection.TryFirst <ClusterRow>(q => q
.Select(clc.ClusterId)
.Where(clc.DistrictDcode == districtName & clc.Cname == Cluster));
if (Cluster == null)
{
response.ErrorList.Add("Error On Row " + row + ": Cluster with name '" +
Cluster + "' is not found!");
continue;
}
pcmEnt.ClusterId = round.ClusterId.Value;
}
else
{
pcmEnt.ClusterId = null;
}
var ClusterLevelName = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
if (!string.IsNullOrWhiteSpace(ClusterLevelName))
{
var round = uow.Connection.TryFirst <ClusterLevelRow>(q => q
.Select(cll.ClusterLevelId)
.Where(cll.ClusterName == ClusterLevelName));
if (pcmEnt == null)
{
response.ErrorList.Add("Error On Row " + row + ": Cluster Level with name '" +
ClusterLevelName + "' is not found!");
continue;
}
pcmEnt.ClusterLevelId = round.ClusterLevelId.Value;
}
else
{
pcmEnt.ClusterLevelId = null;
}
var cmpType = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
if (!string.IsNullOrWhiteSpace(cmpType))
{
var round = uow.Connection.TryFirst <CampaignTypeRow>(q => q
.Select(cmp.CampaignTypeId)
.Where(cmp.CampaignName == cmpType));
if (pcmEnt == null)
{
response.ErrorList.Add("Error On Row " + row + ": Campaign Type Level with name '" +
cmpType + "' is not found!");
continue;
}
pcmEnt.CampaignTypeId = round.CampaignTypeId.Value;
}
else
{
pcmEnt.CampaignTypeId = null;
}
pcmEnt.ClusterCode = Convert.ToString(worksheet.Cells[row, 8].Value ?? "");
pcmEnt.Village = Convert.ToString(worksheet.Cells[row, 9].Value ?? "");
pcmEnt.VistedHouses = Convert.ToInt16(worksheet.Cells[row, 10].Value ?? 0);
pcmEnt.T059m = Convert.ToInt16(worksheet.Cells[row, 11].Value ?? 0);;
pcmEnt.V059m = Convert.ToInt16(worksheet.Cells[row, 12].Value ?? 0);
pcmEnt.T011Seen = Convert.ToInt16(worksheet.Cells[row, 13].Value ?? 0);
pcmEnt.Fm011HvFm = Convert.ToInt16(worksheet.Cells[row, 14].Value ?? 0);
pcmEnt.T1259Seen = Convert.ToInt16(worksheet.Cells[row, 15].Value ?? 0);
pcmEnt.Fm1259HvFm = Convert.ToInt16(worksheet.Cells[row, 16].Value ?? 0);
pcmEnt.TChildrenNoFm = Convert.ToInt16(worksheet.Cells[row, 17].Value ?? 0);
pcmEnt.R1TeamNoVisit = Convert.ToInt16(worksheet.Cells[row, 18].Value ?? 0);
pcmEnt.R21 = Convert.ToInt16(worksheet.Cells[row, 19].Value ?? 0);
pcmEnt.R22 = Convert.ToInt16(worksheet.Cells[row, 20].Value ?? 0);
pcmEnt.R23 = Convert.ToInt16(worksheet.Cells[row, 21].Value ?? 0);
pcmEnt.R24 = Convert.ToInt16(worksheet.Cells[row, 22].Value ?? 0);
pcmEnt.R31 = Convert.ToInt16(worksheet.Cells[row, 23].Value ?? 0);
pcmEnt.R32 = Convert.ToInt16(worksheet.Cells[row, 24].Value ?? 0);
pcmEnt.R33 = Convert.ToInt16(worksheet.Cells[row, 25].Value ?? 0);
pcmEnt.R4NewBorn = Convert.ToInt16(worksheet.Cells[row, 26].Value ?? 0);
pcmEnt.R5Sleep = Convert.ToInt16(worksheet.Cells[row, 27].Value ?? 0);
pcmEnt.R6Sick = Convert.ToInt16(worksheet.Cells[row, 28].Value ?? 0);
pcmEnt.R7Other = Convert.ToInt16(worksheet.Cells[row, 29].Value ?? 0);
pcmEnt.DmCorrect = Convert.ToInt16(worksheet.Cells[row, 30].Value ?? 0);
pcmEnt.DmIncorrect = Convert.ToInt16(worksheet.Cells[row, 31].Value ?? 0);
pcmEnt.NoDm = Convert.ToInt16(worksheet.Cells[row, 32].Value ?? 0);
pcmEnt.FmHeard = Convert.ToInt16(worksheet.Cells[row, 33].Value ?? 0);
pcmEnt.FmNotHeard = Convert.ToInt16(worksheet.Cells[row, 34].Value ?? 0);
pcmEnt.Radio = Convert.ToInt16(worksheet.Cells[row, 35].Value ?? 0);
pcmEnt.Tv = Convert.ToInt16(worksheet.Cells[row, 36].Value ?? 0);
pcmEnt.MullahElders = Convert.ToInt16(worksheet.Cells[row, 37].Value ?? 0);
pcmEnt.Teacher = Convert.ToInt16(worksheet.Cells[row, 38].Value ?? 0);
pcmEnt.Chw = Convert.ToInt16(worksheet.Cells[row, 39].Value ?? 0);
pcmEnt.CElders = Convert.ToInt16(worksheet.Cells[row, 40].Value ?? 0);
pcmEnt.PbLeaflet = Convert.ToInt16(worksheet.Cells[row, 41].Value ?? 0);
pcmEnt.Sm = Convert.ToInt16(worksheet.Cells[row, 42].Value ?? 0);
pcmEnt.Other = Convert.ToInt16(worksheet.Cells[row, 43].Value ?? 0);
pcmEnt.ChVacByMonitor = Convert.ToInt16(worksheet.Cells[row, 44].Value ?? 0);
pcmEnt.RCallCoverage = Convert.ToInt16(worksheet.Cells[row, 45].Value ?? 0);
pcmEnt.Fm059Coverage = Convert.ToInt16(worksheet.Cells[row, 46].Value ?? 0);
pcmEnt.Fm011Coverage = Convert.ToInt16(worksheet.Cells[row, 47].Value ?? 0);
//pcmEnt.InAcesChildren = Convert.ToInt16(worksheet.Cells[row, 48].Value ?? 0);
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
pcmEnt.TenantId = Users.TenantId.Value;
}
else
{
pcmEnt.TenantId = null;
}
if (pcmEnt.PcmId == null)
{
new PcmRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = pcmEnt
});
response.Inserted = response.Inserted + 1;
}
else
{
new PcmRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = pcmEnt,
EntityId = pcmEnt.PcmId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var p = Entities.CustomerRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
string[] ViettelArray = { "086", "096", "097", "098", "032", "033", "034", "035", "036", "037", "038", "039" };
string[] VinaphoneArray = { "091", "094", "088", "083", "084", "085", "081", "082" };
string[] MobiphoneArray = { "089", "090", "093", "070", "079", "077", "076", "078" };
string[] VnmobileArray = { "092", "056", "058" };
string[] GmobileArray = { "099", "059" };
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var carCode = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (carCode.IsTrimmedEmpty())
{
continue;
}
var customer = uow.Connection.TryFirst <Entities.CustomerRow>(q => q
.Select(p.CustomerId, p.StatusId)
.Where(p.CarCode == carCode));
if (customer == null)
{
customer = new Entities.CustomerRow
{
CarCode = carCode
}
}
;
else
{
// avoid assignment errors
customer.TrackWithChecks = false;
}
customer.Name = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
customer.Mobile = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
customer.Address = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!String.IsNullOrEmpty(customer.Mobile))
{
string dauSo = customer.Mobile.Substring(0, 3);
if (ContainAnyOf(dauSo, ViettelArray))
{
customer.NetworkName = "1";
}
if (ContainAnyOf(dauSo, VinaphoneArray))
{
customer.NetworkName = "2";
}
if (ContainAnyOf(dauSo, MobiphoneArray))
{
customer.NetworkName = "3";
}
if (ContainAnyOf(dauSo, VnmobileArray))
{
customer.NetworkName = "4";
}
if (ContainAnyOf(dauSo, GmobileArray))
{
customer.NetworkName = "5";
}
}
//fix khi import du lieu vao mac dinh la Cho goi
if (customer.StatusId == null)
{
customer.StatusId = 5;
}
try
{
if (Convert.ToDateTime(worksheet.Cells[row, 5].Value) != null)
{
customer.DateRegistry = Convert.ToDateTime(worksheet.Cells[row, 5].Value);
}
}
catch { }
if (customer.CustomerId == null)
{
new Repositories.CustomerRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = customer
});
response.Inserted = response.Inserted + 1;
}
else
{
new Repositories.CustomerRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = customer,
EntityId = customer.CustomerId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var Hs = IcmHouseholdRow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var par = PartRow.Fields;
var clc = ClusterRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
var partName = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
var clusterName = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
var village = Convert.ToString(worksheet.Cells[row, 8].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var IcmHousehold = uow.Connection.TryFirst <IcmHouseholdRow>(q => q
.Select(Hs.IcmHouseholdId)
.Where(Hs.DistrictDcode == districtName & Hs.RoundName == roundName1 & Hs.PartPartName == partName & Hs.Cname == clusterName & Hs.Village == village));
if (IcmHousehold == null)
{
IcmHousehold = new IcmHouseholdRow
{
DistrictDname = districtName,
RoundName = roundName1,
PartPartName = partName,
Cname = clusterName,
Village = village
}
}
;
else
{
// avoid assignment errors
IcmHousehold.TrackWithChecks = false;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
IcmHousehold.RoundId = round.RoundId.Value;
}
else
{
IcmHousehold.RoundId = null;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName));
if (dName == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
dName + "' is not found!");
continue;
}
IcmHousehold.DistrictId = district.DistrictId.Value;
}
else
{
IcmHousehold.DistrictId = null;
}
var Part = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
if (!string.IsNullOrWhiteSpace(Part))
{
var round = uow.Connection.TryFirst <PartRow>(q => q
.Select(par.PartId)
.Where(par.PartName == Part));
if (Part == null)
{
response.ErrorList.Add("Error On Row " + row + ": Part Name with name '" +
Part + "' is not found!");
continue;
}
IcmHousehold.PartId = round.PartId.Value;
}
else
{
IcmHousehold.PartId = null;
}
var Cluster = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
if (!string.IsNullOrWhiteSpace(Cluster))
{
var round = uow.Connection.TryFirst <ClusterRow>(q => q
.Select(clc.ClusterId)
.Where(clc.Cname == Cluster & clc.DistrictDcode == districtName));
if (Cluster == null)
{
response.ErrorList.Add("Error On Row " + row + ": Cluster with name '" +
Cluster + "' is not found!");
continue;
}
IcmHousehold.ClusterId = round.ClusterId.Value;
}
else
{
IcmHousehold.ClusterId = null;
}
IcmHousehold.ReportDate = DateTime.Now;
IcmHousehold.Village = Convert.ToString(worksheet.Cells[row, 8].Value ?? "");
IcmHousehold.Supervisor = Convert.ToString(worksheet.Cells[row, 9].Value ?? 0);
IcmHousehold.NoHhVisited = Convert.ToInt16(worksheet.Cells[row, 10].Value ?? 0);
IcmHousehold.NoChildrenInVisitedHh = Convert.ToInt16(worksheet.Cells[row, 11].Value ?? 0);
IcmHousehold.NoVaccInRecall = Convert.ToInt16(worksheet.Cells[row, 12].Value ?? 0);
IcmHousehold.MissedChildren = Convert.ToInt16(worksheet.Cells[row, 13].Value ?? 0);
IcmHousehold.TeamNotCome = Convert.ToInt16(worksheet.Cells[row, 14].Value ?? 0);
IcmHousehold.ChildrenAbsent = Convert.ToInt16(worksheet.Cells[row, 15].Value ?? 0);
IcmHousehold.NewbornSsChildren = Convert.ToInt16(worksheet.Cells[row, 16].Value ?? 0);
IcmHousehold.NoRefused = Convert.ToInt16(worksheet.Cells[row, 17].Value ?? 0);
IcmHousehold.NoIgnoredbyTeam = Convert.ToInt16(worksheet.Cells[row, 18].Value ?? 0);
IcmHousehold.TotUn5SeenByMonit = Convert.ToInt16(worksheet.Cells[row, 19].Value ?? 0);
IcmHousehold.NoSeenWithFingerMark = Convert.ToInt16(worksheet.Cells[row, 20].Value ?? 0);
IcmHousehold.NoCorrect = Convert.ToInt16(worksheet.Cells[row, 21].Value ?? 0);
IcmHousehold.NoIncorrect = Convert.ToInt16(worksheet.Cells[row, 22].Value ?? 0);
IcmHousehold.NoNotMarked = Convert.ToInt16(worksheet.Cells[row, 23].Value ?? 0);
IcmHousehold.NoChildFoundMissedRecall = Convert.ToInt16(worksheet.Cells[row, 24].Value ?? 0);
IcmHousehold.NoChildRecBackOfTally = Convert.ToInt16(worksheet.Cells[row, 25].Value ?? 0);
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
IcmHousehold.TenantId = Users.TenantId.Value;
}
else
{
IcmHousehold.TenantId = null;
}
if (IcmHousehold.IcmHouseholdId == null)
{
new IcmHouseholdRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = IcmHousehold
});
response.Inserted = response.Inserted + 1;
}
else
{
new IcmHouseholdRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = IcmHousehold,
EntityId = IcmHousehold.IcmHouseholdId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, MyExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (request.ApiId == 0)
{
throw new ArgumentOutOfRangeException("filename");
}
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var p = MyRow.Fields;
var response = new ExcelImportResponse {
ErrorList = new List <string>()
};
if (ep.Workbook.Worksheets.Count == 0)
{
response.ErrorList.Add("The Excel file doesn't cantain any sheet");
return(response);
}
var orderSheet = ep.Workbook.Worksheets[1];
//先遍历获取所有的订单明细信息
var idCards = new List <MyRow>();
for (var row = 2; row <= orderSheet.Dimension.End.Row; row++)
{
var name = Convert.ToString(orderSheet.Cells[row, 1].Value ?? string.Empty);
var cardNo = Convert.ToString(orderSheet.Cells[row, 2].Value ?? string.Empty);
if (name.IsTrimmedEmpty() || cardNo.IsTrimmedEmpty())
{
continue;
}
var tmpRow = new MyRow
{
Name = name,
CardNo = cardNo
};
idCards.Add(tmpRow);
}
var idCardRepository = new IdCardRepository();
var apiRepository = new ApiRepository();
var apiEntity = apiRepository.Retrieve(uow.Connection, new RetrieveRequest
{
EntityId = request.ApiId
});
if (apiEntity == null || apiEntity.Entity == null)
{
throw new ArgumentOutOfRangeException("API接口不存在");
}
var api = apiEntity.Entity;
foreach (var idCard in idCards)
{
//验证是否已经存在于数据库中，如果存在则直接用数据库中
var lst = idCardRepository.List(uow.Connection, new ListRequest
{
Criteria = new Criteria("Name") == idCard.Name & new Criteria("CardNo") == idCard.CardNo
});
if (lst != null && lst.Entities != null && lst.Entities.Count > 0)
{
var first = lst.Entities.First();
//继续重复利用数据中已经存在的数据
if (api.ReuseCount > first.ReusedCount)
{
first.RequestIp = Request.UserHostAddress;
first.RequestDate = DateTime.Now;
first.Price = api.Price;
first.ReusedCount = first.ReusedCount + 1;
//更新数据库
idCardRepository.Update(uow, new SaveRequest <MyRow>
{
Entity = first,
EntityId = first.Id
});
response.Updated = response.Updated + 1;
}
else
{
//去调用API接口进行验证 更新本地的数据
var result = RequestApi(api, first);
if (result.Data.Isok == 1)
{
if (result.Data.Data.Err == 0)
{
first.AuthenticationType = "实名认证";
first.CheckResult = GetMessage(result.Data.Code);
first.RequestDate = DateTime.Now;
first.RequestIp = Request.UserHostAddress;
first.Price = api.Price;
first.ReusedCount = 0;
//更新数据库
idCardRepository.Update(uow, new SaveRequest <IdCardRow>
{
Entity = first,
EntityId = first.Id
});
response.Updated = response.Updated + 1;
}
else
{
//这种情况由于会存在个别身份证格式不正确的情况，所有一个有错误，后面的还是可以继续进行的
response.ErrorList.Add($"{idCard.Name} 验证失败, {GetMessage(result.Data.Code)}");
}
}
else
{
//在这种情况就说明之后的验证也不会通过，那么就直接退出，没有必要进行后面的验证
response.ErrorList.Add($"验证失败, {GetMessage(result.Data.Code)}");
break;
}
}
}
else
{
//去调用API接口进行验证 新增本地的数据，因为不存在
var result = RequestApi(api, idCard);
if (result.Data.Isok == 1)
{
if (result.Data.Data.Err == 0)
{
idCard.AuthenticationType = "实名认证";
idCard.CheckResult = GetMessage(result.Data.Code);
idCard.RequestDate = DateTime.Now;
idCard.RequestIp = Request.UserHostAddress;
idCard.Price = api.Price;
idCard.ReusedCount = 0;
//更新数据库
idCardRepository.Create(uow, new SaveRequest <IdCardRow>
{
Entity = idCard
});
response.Inserted = response.Inserted + 1;
}
else
{
//这种情况由于会存在个别身份证格式不正确的情况，所有一个有错误，后面的还是可以继续进行的
response.ErrorList.Add($"{idCard.Name} 验证失败, {GetMessage(result.Data.Code)}");
}
}
else
{
//在这种情况就说明之后的验证也不会通过，那么就直接退出，没有必要进行后面的验证
response.ErrorList.Add($"验证失败, {GetMessage(result.Data.Code)}");
break;
}
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var InAc = InAccessibleRow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
//var c = CampaignTypeRow.Fields;
var ac = InAcsCategoryRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var InAccess = uow.Connection.TryFirst <InAccessibleRow>(q => q
.Select(InAc.InAccessibleId)
.Where(InAc.DistrictDcode == districtName & InAc.RoundName == roundName1));
if (InAccess == null)
{
InAccess = new InAccessibleRow
{
DistrictDcode = districtName,
RoundName = roundName1
}
}
;
else
{
// avoid assignment errors
InAccess.TrackWithChecks = false;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
InAccess.RoundId = round.RoundId.Value;
}
else
{
InAccess.RoundId = null;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName));
if (district == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
districtName + "' is not found!");
continue;
}
InAccess.DistrictId = district.DistrictId.Value;
}
else
{
InAccess.DistrictId = null;
}
InAccess.ReportDate = DateTime.Now;
InAccess.Target = Convert.ToInt32(worksheet.Cells[row, 6].Value ?? 0);
InAccess.MissedClusters = Convert.ToInt16(worksheet.Cells[row, 7].Value ?? 0);
InAccess.MissedChildren = Convert.ToInt32(worksheet.Cells[row, 8].Value ?? 0);
InAccess.PartiallyMissed = Convert.ToInt16(worksheet.Cells[row, 9].Value ?? 0);
InAccess.PartiallyMissedName = Convert.ToString(worksheet.Cells[row, 10].Value ?? "");
InAccess.FullyMissed = Convert.ToInt16(worksheet.Cells[row, 11].Value ?? 0);
InAccess.FullyMissedName = Convert.ToString(worksheet.Cells[row, 12].Value ?? "");
var Reason = Convert.ToString(worksheet.Cells[row, 13].Value ?? "");
if (!string.IsNullOrWhiteSpace(Reason))
{
var reasonCat = uow.Connection.TryFirst <InAcsCategoryRow>(q => q
.Select(ac.InAcsCategoryId)
.Where(ac.Description == Reason));
if (Reason == null)
{
response.ErrorList.Add("Error On Row " + row + ": In Access Category Type with name '" +
Reason + "' is not found!");
continue;
}
InAccess.InAcsCategoryId = reasonCat.InAcsCategoryId.Value;
}
else
{
InAccess.InAcsCategoryId = null;
}
InAccess.PlanToReduce = Convert.ToString(worksheet.Cells[row, 14].Value ?? "");
InAccess.Remarks = Convert.ToString(worksheet.Cells[row, 15].Value ?? "");
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
InAccess.TenantId = Users.TenantId.Value;
}
else
{
InAccess.TenantId = null;
}
if (InAccess.InAccessibleId == null)
{
new InAccessibleRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = InAccess
});
response.Inserted = response.Inserted + 1;
}
else
{
new InAccessibleRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = InAccess,
EntityId = InAccess.InAccessibleId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var l = AdminDataRow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
var c = ClusterRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
var cluster = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var AdminDa = uow.Connection.TryFirst <AdminDataRow>(q => q
.Select(l.AdminDataId)
.Where(l.DistrictDcode == districtName & l.Round == roundName1 & l.Cluster == cluster));
if (AdminDa == null)
{
AdminDa = new AdminDataRow
{
DistrictDcode = districtName,
Round = roundName1,
Cluster = cluster
}
}
;
else
{
// avoid assignment errors
AdminDa.TrackWithChecks = false;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
AdminDa.RoundId = round.RoundId.Value;
}
else
{
AdminDa.RoundId = null;
}
var dName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName));
if (district == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
districtName + "' is not found!");
continue;
}
AdminDa.DistrictId = district.DistrictId.Value;
}
else
{
AdminDa.DistrictId = null;
}
var ClusterName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(ClusterName))
{
var clusters = uow.Connection.TryFirst <ClusterRow>(q => q
.Select(c.ClusterId)
.Where(c.Cname == ClusterName & c.DistrictDcode == districtName));
if (clusters == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
ClusterName + "' is not found!");
continue;
}
AdminDa.ClusterId = clusters.ClusterId.Value;
}
else
{
AdminDa.ClusterId = null;
}
AdminDa.DateOfCampaign = DateTime.Now;
AdminDa.PemtremtManager = Convert.ToString(worksheet.Cells[row, 5].Value ?? 0);
AdminDa.TeamNo = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
//Day 1
AdminDa.D1VialsRecieved = Convert.ToInt32(worksheet.Cells[row, 7].Value ?? 0);
AdminDa.D1VialsReturned = Convert.ToInt32(worksheet.Cells[row, 8].Value ?? 0);
AdminDa.D1VaccByTransit = Convert.ToInt32(worksheet.Cells[row, 9].Value ?? 0);
AdminDa.D1NoOfHhsVisited = Convert.ToInt32(worksheet.Cells[row, 10].Value ?? 0);
AdminDa.D1Ch05resident = Convert.ToInt32(worksheet.Cells[row, 11].Value ?? 0);
AdminDa.D1Ch05guest = Convert.ToInt32(worksheet.Cells[row, 12].Value ?? 0);
AdminDa.D1Ch05VaccInHouse = Convert.ToInt32(worksheet.Cells[row, 13].Value ?? 0);
AdminDa.D1Ch05VaccOutHouse = Convert.ToInt32(worksheet.Cells[row, 14].Value ?? 0);
AdminDa.D1Ch05NomadVacc = Convert.ToInt32(worksheet.Cells[row, 15].Value ?? 0);
AdminDa.D1AbsentRecordDuring = Convert.ToInt32(worksheet.Cells[row, 16].Value ?? 0);
AdminDa.D1AbsentFoundVaccDuring = Convert.ToInt32(worksheet.Cells[row, 17].Value ?? 0);
AdminDa.D1AbsentVaccDuring = Convert.ToInt32(worksheet.Cells[row, 18].Value ?? 0);
AdminDa.D1AbsentRemainDuring = Convert.ToInt32(worksheet.Cells[row, 19].Value ?? 0);
AdminDa.D1AbsentRecordAfter = Convert.ToInt32(worksheet.Cells[row, 20].Value ?? 0);
AdminDa.D1AbsentFoundVaccAfter = Convert.ToInt32(worksheet.Cells[row, 21].Value ?? 0);
AdminDa.D1AbsentVaccAfter = Convert.ToInt32(worksheet.Cells[row, 22].Value ?? 0);
AdminDa.D1AbsentRemainAfter = Convert.ToInt32(worksheet.Cells[row, 23].Value ?? 0);
AdminDa.D1NssRecord = Convert.ToInt32(worksheet.Cells[row, 24].Value ?? 0);
AdminDa.D1NssFoundVacc = Convert.ToInt32(worksheet.Cells[row, 25].Value ?? 0);
AdminDa.D1NssVaccinated = Convert.ToInt32(worksheet.Cells[row, 26].Value ?? 0);
AdminDa.D1NssReamining = Convert.ToInt32(worksheet.Cells[row, 27].Value ?? 0);
AdminDa.D1RefusalRecord = Convert.ToInt32(worksheet.Cells[row, 28].Value ?? 0);
AdminDa.D1RefusalFoundVacc = Convert.ToInt32(worksheet.Cells[row, 29].Value ?? 0);
AdminDa.D1RefusalVacc = Convert.ToInt32(worksheet.Cells[row, 30].Value ?? 0);
AdminDa.D1RefusalRemaining = Convert.ToInt32(worksheet.Cells[row, 31].Value ?? 0);
//Day 2
AdminDa.D2VialsRecieved = Convert.ToInt32(worksheet.Cells[row, 32].Value ?? 0);
AdminDa.D2VialsReturned = Convert.ToInt32(worksheet.Cells[row, 33].Value ?? 0);
AdminDa.D2VaccByTransit = Convert.ToInt32(worksheet.Cells[row, 34].Value ?? 0);
AdminDa.D2NoOfHhsVisited = Convert.ToInt32(worksheet.Cells[row, 35].Value ?? 0);
AdminDa.D2Ch05resident = Convert.ToInt32(worksheet.Cells[row, 36].Value ?? 0);
AdminDa.D2Ch05guest = Convert.ToInt32(worksheet.Cells[row, 37].Value ?? 0);
AdminDa.D2Ch05VaccInHouse = Convert.ToInt32(worksheet.Cells[row, 38].Value ?? 0);
AdminDa.D2Ch05VaccOutHouse = Convert.ToInt32(worksheet.Cells[row, 39].Value ?? 0);
AdminDa.D2Ch05NomadVacc = Convert.ToInt32(worksheet.Cells[row, 40].Value ?? 0);
AdminDa.D2AbsentRecordDuring = Convert.ToInt32(worksheet.Cells[row, 41].Value ?? 0);
AdminDa.D2AbsentFoundVaccDuring = Convert.ToInt32(worksheet.Cells[row, 42].Value ?? 0);
AdminDa.D2AbsentVaccDuring = Convert.ToInt32(worksheet.Cells[row, 43].Value ?? 0);
AdminDa.D2AbsentRemainDuring = Convert.ToInt32(worksheet.Cells[row, 44].Value ?? 0);
AdminDa.D2AbsentRecordAfter = Convert.ToInt32(worksheet.Cells[row, 45].Value ?? 0);
AdminDa.D2AbsentFoundVaccAfter = Convert.ToInt32(worksheet.Cells[row, 46].Value ?? 0);
AdminDa.D2AbsentVaccAfter = Convert.ToInt32(worksheet.Cells[row, 47].Value ?? 0);
AdminDa.D2AbsentRemainAfter = Convert.ToInt32(worksheet.Cells[row, 48].Value ?? 0);
AdminDa.D2NssRecord = Convert.ToInt32(worksheet.Cells[row, 49].Value ?? 0);
AdminDa.D2NssFoundVacc = Convert.ToInt32(worksheet.Cells[row, 50].Value ?? 0);
AdminDa.D2NssVaccinated = Convert.ToInt32(worksheet.Cells[row, 51].Value ?? 0);
AdminDa.D2NssReamining = Convert.ToInt32(worksheet.Cells[row, 52].Value ?? 0);
AdminDa.D2RefusalRecord = Convert.ToInt32(worksheet.Cells[row, 53].Value ?? 0);
AdminDa.D2RefusalFoundVacc = Convert.ToInt32(worksheet.Cells[row, 54].Value ?? 0);
AdminDa.D2RefusalVacc = Convert.ToInt32(worksheet.Cells[row, 55].Value ?? 0);
AdminDa.D2RefusalRemaining = Convert.ToInt32(worksheet.Cells[row, 56].Value ?? 0);
//Day 3
AdminDa.D3VialsRecieved = Convert.ToInt32(worksheet.Cells[row, 57].Value ?? 0);
AdminDa.D3VialsReturned = Convert.ToInt32(worksheet.Cells[row, 58].Value ?? 0);
AdminDa.D3VaccByTransit = Convert.ToInt32(worksheet.Cells[row, 59].Value ?? 0);
AdminDa.D3NoOfHhsVisited = Convert.ToInt32(worksheet.Cells[row, 60].Value ?? 0);
AdminDa.D3Ch05resident = Convert.ToInt32(worksheet.Cells[row, 61].Value ?? 0);
AdminDa.D3Ch05guest = Convert.ToInt32(worksheet.Cells[row, 62].Value ?? 0);
AdminDa.D3Ch05VaccInHouse = Convert.ToInt32(worksheet.Cells[row, 63].Value ?? 0);
AdminDa.D3Ch05VaccOutHouse = Convert.ToInt32(worksheet.Cells[row, 64].Value ?? 0);
AdminDa.D3Ch05NomadVacc = Convert.ToInt32(worksheet.Cells[row, 65].Value ?? 0);
AdminDa.D3AbsentRecordDuring = Convert.ToInt32(worksheet.Cells[row, 66].Value ?? 0);
AdminDa.D3AbsentFoundVaccDuring = Convert.ToInt32(worksheet.Cells[row, 67].Value ?? 0);
AdminDa.D3AbsentVaccDuring = Convert.ToInt32(worksheet.Cells[row, 68].Value ?? 0);
AdminDa.D3AbsentRemainDuring = Convert.ToInt32(worksheet.Cells[row, 69].Value ?? 0);
AdminDa.D3AbsentRecordAfter = Convert.ToInt32(worksheet.Cells[row, 70].Value ?? 0);
AdminDa.D3AbsentFoundVaccAfter = Convert.ToInt32(worksheet.Cells[row, 71].Value ?? 0);
AdminDa.D3AbsentVaccAfter = Convert.ToInt32(worksheet.Cells[row, 72].Value ?? 0);
AdminDa.D3AbsentRemainAfter = Convert.ToInt32(worksheet.Cells[row, 73].Value ?? 0);
AdminDa.D3NssRecord = Convert.ToInt32(worksheet.Cells[row, 74].Value ?? 0);
AdminDa.D3NssFoundVacc = Convert.ToInt32(worksheet.Cells[row, 75].Value ?? 0);
AdminDa.D3NssVaccinated = Convert.ToInt32(worksheet.Cells[row, 76].Value ?? 0);
AdminDa.D3NssReamining = Convert.ToInt32(worksheet.Cells[row, 77].Value ?? 0);
AdminDa.D3RefusalRecord = Convert.ToInt32(worksheet.Cells[row, 78].Value ?? 0);
AdminDa.D3RefusalFoundVacc = Convert.ToInt32(worksheet.Cells[row, 79].Value ?? 0);
AdminDa.D3RefusalVacc = Convert.ToInt32(worksheet.Cells[row, 80].Value ?? 0);
AdminDa.D3RefusalRemaining = Convert.ToInt32(worksheet.Cells[row, 81].Value ?? 0);
//Day 5
AdminDa.D5VialsRecieved = Convert.ToInt32(worksheet.Cells[row, 82].Value ?? 0);
AdminDa.D5VialsReturned = Convert.ToInt32(worksheet.Cells[row, 83].Value ?? 0);
AdminDa.D5RemainAbsentDuring = Convert.ToInt32(worksheet.Cells[row, 84].Value ?? 0);
AdminDa.D5AbsentFoundVaccDuring5 = Convert.ToInt32(worksheet.Cells[row, 85].Value ?? 0);
AdminDa.D5AbsentVaccDuring5 = Convert.ToInt32(worksheet.Cells[row, 86].Value ?? 0);
AdminDa.D5AbsentRemainDuring5 = Convert.ToInt32(worksheet.Cells[row, 87].Value ?? 0);
AdminDa.D5RemainAbsentAfter = Convert.ToInt32(worksheet.Cells[row, 88].Value ?? 0);
AdminDa.D5AbsentFoundVaccAfter5 = Convert.ToInt32(worksheet.Cells[row, 89].Value ?? 0);
AdminDa.D5AbsentVaccAfter5 = Convert.ToInt32(worksheet.Cells[row, 90].Value ?? 0);
AdminDa.D5AbsentRemainAfter5 = Convert.ToInt32(worksheet.Cells[row, 91].Value ?? 0);
AdminDa.D5RemainNss = Convert.ToInt32(worksheet.Cells[row, 92].Value ?? 0);
AdminDa.D5FoundVaccNss5 = Convert.ToInt32(worksheet.Cells[row, 93].Value ?? 0);
AdminDa.D5VaccNss5 = Convert.ToInt32(worksheet.Cells[row, 94].Value ?? 0);
AdminDa.D5RemainNss5 = Convert.ToInt32(worksheet.Cells[row, 95].Value ?? 0);
AdminDa.D5RemainRefusal = Convert.ToInt32(worksheet.Cells[row, 96].Value ?? 0);
AdminDa.D5FoundVaccRefusal5 = Convert.ToInt32(worksheet.Cells[row, 97].Value ?? 0);
AdminDa.D5VaccRefusal5 = Convert.ToInt32(worksheet.Cells[row, 98].Value ?? 0);
AdminDa.D5RemainRefusal5 = Convert.ToInt32(worksheet.Cells[row, 99].Value ?? 0);
AdminDa.D5VaccOutofHouse = Convert.ToInt32(worksheet.Cells[row, 100].Value ?? 0);
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
AdminDa.TenantId = Users.TenantId.Value;
}
else
{
AdminDa.TenantId = null;
}
if (AdminDa.AdminDataId == null)
{
new AdminDataRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = AdminDa
});
response.Inserted = response.Inserted + 1;
}
else
{
new AdminDataRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = AdminDa,
EntityId = AdminDa.AdminDataId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.StackTrace);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var o = OutOfHouseSurveyRow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
var c = CampaignTypeRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var provName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var outHouse = uow.Connection.TryFirst <OutOfHouseSurveyRow>(q => q
.Select(o.OutOfHouseSurveyId)
.Where(o.DistrictDcode == districtName & o.RoundName == roundName1));
if (outHouse == null)
{
outHouse = new OutOfHouseSurveyRow
{
DistrictDcode = districtName,
RoundName = roundName1,
Province = provName
}
}
;
else
{
// avoid assignment errors
outHouse.TrackWithChecks = false;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
outHouse.RoundId = round.RoundId.Value;
}
else
{
outHouse.RoundId = null;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName));
if (district == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
districtName + "' is not found!");
continue;
}
outHouse.DistrictId = district.DistrictId.Value;
}
else
{
outHouse.DistrictId = null;
}
outHouse.ReportDate = DateTime.Now;
outHouse.T059m = Convert.ToInt16(worksheet.Cells[row, 6].Value ?? 0);
outHouse.Vac059m = Convert.ToInt16(worksheet.Cells[row, 7].Value ?? 0);
outHouse.FM059m = Convert.ToInt16(worksheet.Cells[row, 8].Value ?? 0);
outHouse.T059f = Convert.ToInt16(worksheet.Cells[row, 9].Value ?? 0);
outHouse.Vac059f = Convert.ToInt16(worksheet.Cells[row, 10].Value ?? 0);
outHouse.FM059f = Convert.ToInt16(worksheet.Cells[row, 11].Value ?? 0);
outHouse.MissTmNotCome = Convert.ToInt16(worksheet.Cells[row, 12].Value ?? 0);
outHouse.MissAbsent = Convert.ToInt16(worksheet.Cells[row, 13].Value ?? 0);
outHouse.MissRefused = Convert.ToInt16(worksheet.Cells[row, 14].Value ?? 0);
outHouse.MissNewBornSick = Convert.ToInt16(worksheet.Cells[row, 15].Value ?? 0);
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
outHouse.TenantId = Users.TenantId.Value;
}
else
{
outHouse.TenantId = null;
}
if (outHouse.OutOfHouseSurveyId == null)
{
new OutOfHouseSurveyRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = outHouse
});
response.Inserted = response.Inserted + 1;
}
else
{
new OutOfHouseSurveyRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = outHouse,
EntityId = outHouse.OutOfHouseSurveyId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
//-------------------------- Gather Excel File Data ------------------------------------------------------------//
request.CheckNotNull();
var fName = request.FileName;
Check.NotNullOrWhiteSpace(fName, "filename");
UploadHelper.CheckFileNameSecurity(fName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(fName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var response = new ExcelImportResponse();
var myErrors = response.ErrorList = new List <string>();
/*Read the first Excell sheet and then gather the headers of the import file*/
var worksheet = ep.Workbook.Worksheets.First();
var wsStart = worksheet.Dimension.Start;
var wsEnd = worksheet.Dimension.End;
var headers = worksheet.Cells[wsStart.Row, wsStart.Column, 1, wsEnd.Column];
//-------------------------- Gather Mapping nformation ------------------------------------------------------------//
/*A few variables to make our life easier*/
var myConnection = uow.Connection;
var myFields = MyRow.Fields;
List <string> importedHeaders = new List <string>(); //Headers from Imported File
List <object> importedValues = new List <object>(); //Values being Imported
List <string> systemHeaders = new List <string>(); //Headers currently in system
List <string> sysHeader = new List <string>(); //System Header to import
List <string> exceptionHeaders = new List <string>(); //Haders to not check for during import.
object obj = null; //Object container for value being imported
dynamic a = null; //Handled object to assign to system
string fieldTitle = ""; //Title of field being imported
jImpHelp.entryType entType; //Type of handler to use.
/*Add Imported file headers to proper list*/
foreach (var q in headers)
{
importedHeaders.Add(q.Text);
}
/* Add system headers to proper list while also adding 'ID' to the list. 'ID'
* is the key field from exported files and needs to be mapped manually */
systemHeaders.Add("ID");
foreach (var t in myFields)
{
systemHeaders.Add(t.Title);
}
;
/* Not all columns will be expected to be imported. To avoid unnecesary error messages
* we add the titles of the fields we want ignored here.*/
exceptionHeaders.Add(myFields.AddressLogId.Title);
/* Using the systemHeaders to compare against the importedHeaders, we build an index with
* the column location and match it to the system header using a Dictionary<string, int>. */
Dictionary <string, int> headerMap = myImpHelp.myExcelHeaderMap(importedHeaders, systemHeaders, myErrors, exceptionHeaders);
for (var row = 2; row <= wsEnd.Row; row++)
{
try
{
/* This instance checks the ID field as to whether the row exists or not. if the
* ID key exists, it will use it to update the row with the imported fields but if
* it does not exist, it creates a new entry. */
var sysKey = myFields.CustomerId;
obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, "ID").Value, worksheet);
var wsKeyField = Convert.ToInt32(obj);
var currentRow = myConnection.TryFirst <MyRow>(q => q.Select(sysKey).Where(sysKey == wsKeyField));
if (currentRow == null) //Create New if Row doesnt' exist
{
currentRow = new MyRow()
{
}
}
;
else
{
currentRow.TrackWithChecks = false;
}
/* We now need to handle how we want to manage the imported fields. We list the fields
* being imported using the same code set but we update the entType, fieldTitle and then
* designate what field will be updated. You handle specialty case handlers such as joint
* fields in another file. Do note that I use importedValues and sysHeader to pass on the
* values to the handler. I did this on purpose so that you can pass multiple values if
* neccesary. For example, if your joint field requires 2 values to be entered, you can
* simply capture the value with an additional .Add()
*
* Example :
*
* -------Simple Field
* entType = jImpHelp.entryType.String; //designate the type of item
* fieldTitle = myFields.firstField.Title; //designate the field to be looked at
* obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, fieldTitle).Value, worksheet);
* ----ADD OBJECT TO NEW CONTAINER--
* var newContainer = obj;
* ----------------------
* if (obj != null)
* {
* importedValues.Add(obj);
* sysHeader.Add(fieldTitle);
* a = jImpHelp.myImportEntry(importedValues, myErrors, sysHeader, row, entType, myConnection);
* if (a != null)
* {
* currentRow.CustomerAddress = a; //designate the field to be updated in the system
* }
* sysHeader.Clear();
* importedValues.Clear();
* }
* -----Joint Field
* entType = jImpHelp.entryType.jointField; //<--Update Me according to type of field to merge with
* fieldTitle = myFields.secondFieldCustomerName.Title;//<--Update Me
* obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, fieldTitle).Value, worksheet);
* if (obj != null)
* {
* importedValues.Add(obj);
* -----ADD CAPTURED CONTAINER TO VALUES LIST-----
* importedValue.Add(newContainer)
* -------------------------------------------
* sysHeader.Add(fieldTitle);
* a = jImpHelp.myImportEntry(importedValues, myErrors, sysHeader, row, entType, myConnection);
* if (a != null)
* {
* currentRow.AddressLogId = a; ////<--Update Me. *Special Case. Notice: Not the same as field to match
* }
* sysHeader.Clear();
* importedValues.Clear();
* }
*/
//--------------------------Merge Imported Fields ------------------------------------------------------------//
entType = jImpHelp.entryType.String; //designate the type of item
fieldTitle = myFields.CustomerAddress.Title; //designate the field to be looked at
obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, fieldTitle).Value, worksheet);
if (obj != null)
{
importedValues.Add(obj);
sysHeader.Add(fieldTitle);
a = jImpHelp.myImportEntry(importedValues, myErrors, sysHeader, row, entType, myConnection);
if (a != null)
{
currentRow.CustomerAddress = a; //designate the field to be updated in the system
}
sysHeader.Clear();
importedValues.Clear();
}
/*Same as above, just updated for the next field. */
entType = jImpHelp.entryType.String; //<--Update Me according to type of field to merge with
fieldTitle = myFields.CustomerName.Title; //<--Update Me
obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, fieldTitle).Value, worksheet);
if (obj != null)
{
importedValues.Add(obj);
sysHeader.Add(fieldTitle);
a = jImpHelp.myImportEntry(importedValues, myErrors, sysHeader, row, entType, myConnection);
if (a != null)
{
currentRow.CustomerName = a; //<--Update Me
}
sysHeader.Clear();
importedValues.Clear();
}
entType = jImpHelp.entryType.CategoryJoin; //<--Update Me *Special Case Joint Field
fieldTitle = myFields.AddressFloor.Title; //<--Update Me *Special Case. Notice: Field to match in import file
obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, fieldTitle).Value, worksheet);
if (obj != null)
{
importedValues.Add(obj);
sysHeader.Add(fieldTitle);
a = jImpHelp.myImportEntry(importedValues, myErrors, sysHeader, row, entType, myConnection);
if (a != null)
{
currentRow.AddressLogId = a; ////<--Update Me. *Special Case. Notice: Not the same as field to match
}
sysHeader.Clear();
importedValues.Clear();
}
//----------------------------------------Run Object Entries with Create or Update ------------------------------------//
if (currentRow.CustomerId == null)
{
new CustomerRepository().Create(uow, new SaveWithLocalizationRequest <MyRow>
{
Entity = currentRow
});
response.Inserted = response.Inserted + 1;
}
else
{
new CustomerRepository().Update(uow, new SaveWithLocalizationRequest <MyRow>
{
Entity = currentRow,
EntityId = currentRow.CustomerId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
myErrors.Add(myImpHelp.eMessage3(row, ex.Message));
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var TempCamp = TempCampaignRow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var clc = ClusterRow.Fields;
var prov = ProvinceRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
var clusterName = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
var provName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var TempC = uow.Connection.TryFirst <TempCampaignRow>(q => q
.Select(TempCamp.TempCampaignId)
.Where(TempCamp.DistrictName == districtName & TempCamp.RoundName == roundName1 & TempCamp.ClusterName == clusterName & TempCamp.ProvinceName == provName));
if (TempC == null)
{
TempC = new TempCampaignRow
{
DistrictName = districtName,
RoundName = roundName1,
ClusterName = clusterName,
ProvinceName = provName
}
}
;
else
{
// avoid assignment errors
TempC.TrackWithChecks = false;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
TempC.RoundId = round.RoundId.Value;
}
else
{
TempC.RoundId = null;
}
var Province = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (!string.IsNullOrWhiteSpace(Province))
{
var round = uow.Connection.TryFirst <ProvinceRow>(q => q
.Select(prov.ProvinceId)
.Where(prov.Pname == Province));
if (TempC == null)
{
response.ErrorList.Add("Error On Row " + row + ": Province with name '" +
Province + "' is not found!");
continue;
}
TempC.ProvinceId = round.ProvinceId.Value;
}
else
{
TempC.ProvinceId = null;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Pname == Province & d.Dcode == dName));
if (dName == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
dName + "' is not found!");
continue;
}
TempC.DistrictId = district.DistrictId.Value;
}
else
{
TempC.DistrictId = null;
}
var Cluster = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
if (!string.IsNullOrWhiteSpace(Cluster))
{
var round = uow.Connection.TryFirst <ClusterRow>(q => q
.Select(clc.ClusterId)
.Where(clc.Cname == Cluster & clc.DistrictDcode == districtName & clc.Province == Province));
if (Cluster == null)
{
response.ErrorList.Add("Error On Row " + row + ": Cluster with name '" +
Cluster + "' is not found!");
continue;
}
TempC.ClusterId = round.ClusterId.Value;
}
else
{
TempC.ClusterId = null;
}
TempC.LDPTypes = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
TempC.PemtremtManager = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
TempC.DateOfCampaign = Convert.ToDateTime(worksheet.Cells[row, 8].Value ?? "");
TempC.TargetU5Case = Convert.ToInt32(worksheet.Cells[row, 9].Value ?? 0);
TempC.TeamNo = Convert.ToString(worksheet.Cells[row, 10].Value ?? "");
TempC.HhNo = Convert.ToInt16(worksheet.Cells[row, 11].Value ?? 0);
//Day 1
TempC.D1VialDist = Convert.ToInt16(worksheet.Cells[row, 12].Value ?? 0);
TempC.D1VialUse = Convert.ToInt16(worksheet.Cells[row, 13].Value ?? 0);
TempC.D1Vac011Months = Convert.ToInt16(worksheet.Cells[row, 14].Value ?? 0);
TempC.D1Vac1259months = Convert.ToInt16(worksheet.Cells[row, 15].Value ?? 0);
TempC.D1RecAbsChildren = Convert.ToInt16(worksheet.Cells[row, 16].Value ?? 0);
TempC.D1VacAbsChildren = Convert.ToInt16(worksheet.Cells[row, 17].Value ?? 0);
TempC.D1RecNewbornSsc = Convert.ToInt16(worksheet.Cells[row, 18].Value ?? 0);
TempC.D1VacNewbornSsc = Convert.ToInt16(worksheet.Cells[row, 19].Value ?? 0);
TempC.D1RecRefusedChildren = Convert.ToInt16(worksheet.Cells[row, 20].Value ?? 0);
TempC.D1RevacRefusedChildren = Convert.ToInt16(worksheet.Cells[row, 21].Value ?? 0);
TempC.D1AfpCase = Convert.ToInt16(worksheet.Cells[row, 22].Value ?? 0);
//Day 2
TempC.D2VialDist = Convert.ToInt16(worksheet.Cells[row, 23].Value ?? 0);
TempC.D2VialUse = Convert.ToInt16(worksheet.Cells[row, 24].Value ?? 0);
TempC.D2Vac011Months = Convert.ToInt16(worksheet.Cells[row, 25].Value ?? 0);
TempC.D2Vac1259months = Convert.ToInt16(worksheet.Cells[row, 26].Value ?? 0);
TempC.D2RecAbsChildren = Convert.ToInt16(worksheet.Cells[row, 27].Value ?? 0);
TempC.D2VacAbsChildren = Convert.ToInt16(worksheet.Cells[row, 28].Value ?? 0);
TempC.D2RecNewbornSsc = Convert.ToInt16(worksheet.Cells[row, 29].Value ?? 0);
TempC.D2VacNewbornSsc = Convert.ToInt16(worksheet.Cells[row, 30].Value ?? 0);
TempC.D2RecRefusedChildren = Convert.ToInt16(worksheet.Cells[row, 31].Value ?? 0);
TempC.D2RevacRefusedChildren = Convert.ToInt16(worksheet.Cells[row, 32].Value ?? 0);
TempC.D2AfpCase = Convert.ToInt16(worksheet.Cells[row, 33].Value ?? 0);
//Day 3
TempC.D3VialDist = Convert.ToInt16(worksheet.Cells[row, 34].Value ?? 0);
TempC.D3VialUse = Convert.ToInt16(worksheet.Cells[row, 35].Value ?? 0);
TempC.D3Vac011Months = Convert.ToInt16(worksheet.Cells[row, 36].Value ?? 0);
TempC.D3Vac1259months = Convert.ToInt16(worksheet.Cells[row, 37].Value ?? 0);
TempC.D3RecAbsChildren = Convert.ToInt16(worksheet.Cells[row, 38].Value ?? 0);
TempC.D3VacAbsChildren = Convert.ToInt16(worksheet.Cells[row, 39].Value ?? 0);
TempC.D3RecNewbornSsc = Convert.ToInt16(worksheet.Cells[row, 40].Value ?? 0);
TempC.D3VacNewbornSsc = Convert.ToInt16(worksheet.Cells[row, 41].Value ?? 0);
TempC.D3RecRefusedChildren = Convert.ToInt16(worksheet.Cells[row, 42].Value ?? 0);
TempC.D3RevacRefusedChildren = Convert.ToInt16(worksheet.Cells[row, 43].Value ?? 0);
TempC.D3AfpCase = Convert.ToInt16(worksheet.Cells[row, 44].Value ?? 0);
//Day 5
TempC.D5VialDist = Convert.ToInt16(worksheet.Cells[row, 45].Value ?? 0);
TempC.D5VialUse = Convert.ToInt16(worksheet.Cells[row, 46].Value ?? 0);
TempC.D5Vac011Months = Convert.ToInt16(worksheet.Cells[row, 47].Value ?? 0);
TempC.D5Vac1259months = Convert.ToInt16(worksheet.Cells[row, 48].Value ?? 0);
TempC.D5RecAbsChildren = Convert.ToInt16(worksheet.Cells[row, 49].Value ?? 0);
TempC.D5VacAbsChildren = Convert.ToInt16(worksheet.Cells[row, 50].Value ?? 0);
TempC.D5RecNewbornSsc = Convert.ToInt16(worksheet.Cells[row, 51].Value ?? 0);
TempC.D5VacNewbornSsc = Convert.ToInt16(worksheet.Cells[row, 52].Value ?? 0);
TempC.D5RecRefusedChildren = Convert.ToInt16(worksheet.Cells[row, 53].Value ?? 0);
TempC.D5RevacRefusedChildren = Convert.ToInt16(worksheet.Cells[row, 54].Value ?? 0);
TempC.D5AfpCase = Convert.ToInt16(worksheet.Cells[row, 55].Value ?? 0);
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
TempC.TenantId = Users.TenantId.Value;
}
else
{
TempC.TenantId = null;
}
if (TempC.TempCampaignId == null)
{
new TempCampaignRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = TempC
});
response.Inserted = response.Inserted + 1;
}
else
{
new TempCampaignRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = TempC,
EntityId = TempC.TempCampaignId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var c = CommunicationRow.Fields;
var lng = Administration.Entities.LanguageRow.Fields;
var md = MediaRow.Fields;
var mdt = MediaTypeRow.Fields;
var part = DayPartRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var language = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
var mediatype = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var media = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
var daypart = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
if (language.IsTrimmedEmpty())
{
continue;
}
var COM = uow.Connection.TryFirst <CommunicationRow>(q => q
.Select(c.CommunicationId)
.Where(c.LanguageLanguageName == language & c.MediaMediaName == media & c.MediaTypeMediaTypeName == mediatype & c.Daypart == daypart));
if (COM == null)
{
COM = new CommunicationRow
{
LanguageLanguageName = language,
MediaMediaName = media,
MediaTypeMediaTypeName = mediatype,
Daypart = daypart
}
}
;
else
{
// avoid assignment errors
COM.TrackWithChecks = false;
}
if (!string.IsNullOrWhiteSpace(language))
{
var lang = uow.Connection.TryFirst <Administration.Entities.LanguageRow>(q => q
.Select(lng.Id)
.Where(lng.LanguageName == language));
if (lang == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
language + "' is not found!");
continue;
}
COM.LanguageId = lang.Id.Value;
}
else
{
COM.LanguageId = null;
}
if (!string.IsNullOrWhiteSpace(media))
{
var medias = uow.Connection.TryFirst <MediaRow>(q => q
.Select(md.MediaId)
.Where(md.MediaName == media));
if (medias == null)
{
response.ErrorList.Add("Error On Row " + row + ": Media with name '" +
media + "' is not found!");
continue;
}
COM.MediaId = medias.MediaId.Value;
}
else
{
COM.MediaId = null;
}
if (!string.IsNullOrWhiteSpace(mediatype))
{
var medias = uow.Connection.TryFirst <MediaTypeRow>(q => q
.Select(mdt.MediaTypeId)
.Where(mdt.MediaTypeName == mediatype));
if (medias == null)
{
response.ErrorList.Add("Error On Row " + row + ": Media Type with name '" +
mediatype + "' is not found!");
continue;
}
COM.MediaTypeId = medias.MediaTypeId.Value;
}
else
{
COM.MediaTypeId = null;
}
if (!string.IsNullOrWhiteSpace(daypart))
{
var dayspart = uow.Connection.TryFirst <DayPartRow>(q => q
.Select(part.DaypartId)
.Where(part.Daypart == daypart));
if (dayspart == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
daypart + "' is not found!");
continue;
}
COM.DaypartId = dayspart.DaypartId.Value;
}
else
{
COM.DaypartId = null;
}
object dateField = Convert.ToString(worksheet.Cells[row, 4].Value ?? 0);
if (dateField != null)
{
if (dateField is double)
{
COM.BroadCastDate = DateTime.FromOADate((double)dateField);
}
else
{
DateTime.TryParse((string)dateField, out DateTime dt);
}
}
String datetimefield = Convert.ToString(worksheet.Cells[row, 5].Value ?? 0);
double dtime = double.Parse(datetimefield);
//COM.BroadCastTime = TimeSpan.TicksPerMillisecond
COM.Duration = Convert.ToInt32(worksheet.Cells[row, 6].Value ?? 0);
COM.Source = Convert.ToString(worksheet.Cells[row, 8].Value ?? "");
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
COM.TenantId = Users.TenantId.Value;
}
else
{
COM.TenantId = null;
}
if (COM.CommunicationId == null)
{
new CommunicationRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = COM
});
response.Inserted = response.Inserted + 1;
}
else
{
new CommunicationRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = COM,
EntityId = COM.CommunicationId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.StackTrace);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var p = ProductRow.Fields;
var s = SupplierRow.Fields;
var c = CategoryRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
// var product = uow.Connection.TryFirst < ProductRow >(q => q.Select(p.ProductID)
// .Where(p.ProductName == productName));
// var product = new ProductRow
// {
// };
var product = new ProductRow();
var supplierName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(supplierName))
{
var supplier = uow.Connection.TryFirst <SupplierRow>(q => q
.Select(s.SupplierID)
.Where(s.CompanyName == supplierName));
if (supplier == null)
{
var suppliernew = new SupplierRow();
suppliernew.CompanyName = supplierName;
new SupplierRepository().Create(uow, new SaveRequest <SupplierRow>
{
Entity = suppliernew,
});
// product.CategoryID = categorynew.CategoryID.Value;
var suppl = uow.Connection.TryFirst <SupplierRow>(q => q
.Select(s.SupplierID)
.Where(s.CompanyName == suppliernew.CompanyName));
product.SupplierID = supplier.SupplierID.Value;
}
product.SupplierID = supplier.SupplierID.Value;
}
else
{
product.SupplierID = null;
}
var categoryName = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
if (!string.IsNullOrWhiteSpace(categoryName))
{
var category = uow.Connection.TryFirst <CategoryRow>(q => q
.Select(c.CategoryID)
.Where(c.CategoryName == categoryName));
if (category == null)
{
var categorynew = new CategoryRow();
categorynew.CategoryName = categoryName;
new CategoryRepository().Create(uow, new SaveRequest <CategoryRow>
{
Entity = categorynew,
});
// product.CategoryID = categorynew.CategoryID.Value;
var categoryy = uow.Connection.TryFirst <CategoryRow>(q => q
.Select(c.CategoryID)
.Where(c.CategoryName == categorynew.CategoryName));
product.CategoryID = categoryy.CategoryID.Value;
}
else
{
product.CategoryID = category.CategoryID.Value;
}
}
else
{
product.CategoryID = null;
}
product.ProductName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
product.UnitPrice = Convert.ToDecimal(worksheet.Cells[row, 6].Value ?? 0);
product.UnitsInStock = Convert.ToDecimal(worksheet.Cells[row, 7].Value ?? 0);
product.Product2ID = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
product.ProductID = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
var producto = uow.Connection.TryFirst <ProductRow>(q => q
.Select(p.ProductID)
.Where(p.ProductID == product.ProductID));
if (producto == null)
{
new ProductRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = product,
});
response.Inserted = response.Inserted + 1;
}
else
{
new ProductRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = product,
EntityId = product.ProductID
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ValidationError("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var name = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (string.IsNullOrWhiteSpace(name))
{
continue;
}
var flds = MyRow.Fields;
var specialty = uow.Connection.TryFirst <MyRow>(flds.Name == name);
if (specialty == null)
{
specialty = new MyRow()
{
}
}
;
else
{
specialty.TrackWithChecks = false;
}
if (specialty.SpecialtyId == null)
{
specialty.Name = name;
specialty.InsertDate = DateTime.Now;
specialty.InsertUserId = Int32.Parse(Authorization.UserId);
specialty.IsActive = 1;
new MyRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = specialty
});
response.Inserted = response.Inserted + 1;
}
//else
//{
// new MyRepository().Update(uow, new SaveRequest<MyRow>
// {
// Entity = rowReaded,
// EntityId = rowReaded.RowId.Value
// });
// response.Updated = response.Updated + 1;
//}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var Sup = IcmTeamRow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var clc = ClusterRow.Fields;
var part = PartRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
var partName = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
var clusterName = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
var monitname = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
var teamMember = Convert.ToString(worksheet.Cells[row, 8].Value ?? "");
var teamno = Convert.ToString(worksheet.Cells[row, 9].Value ?? "");
var time = Convert.ToString(worksheet.Cells[row, 11].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var IcmTeam = uow.Connection.TryFirst <IcmTeamRow>(q => q
.Select(Sup.IcmTeamId)
.Where(Sup.DistrictDcode == districtName & Sup.RoundName == roundName1 & Sup.Cname == clusterName & Sup.PartName == partName & Sup.TeamNo == teamno & Sup.MonitorName == monitname & Sup.MonitVisitTime == time & Sup.TeamMemberName == teamMember));
if (IcmTeam == null)
{
IcmTeam = new IcmTeamRow
{
DistrictDcode = districtName,
RoundName = roundName1,
Cname = clusterName,
PartName = partName,
MonitorName = monitname,
TeamMemberName = teamMember,
TeamNo = teamno,
MonitVisitTime = time
}
}
;
else
{
// avoid assignment errors
IcmTeam.TrackWithChecks = false;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
IcmTeam.RoundId = round.RoundId.Value;
}
else
{
IcmTeam.RoundId = null;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName));
if (dName == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
dName + "' is not found!");
continue;
}
IcmTeam.DistrictId = district.DistrictId.Value;
}
else
{
IcmTeam.DistrictId = null;
}
var Cluster = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
if (!string.IsNullOrWhiteSpace(Cluster))
{
var round = uow.Connection.TryFirst <ClusterRow>(q => q
.Select(clc.ClusterId)
.Where(clc.Cname == Cluster & clc.DistrictDcode == districtName));
if (Cluster == null)
{
response.ErrorList.Add("Error On Row " + row + ": Cluster with name '" +
Cluster + "' is not found!");
continue;
}
IcmTeam.ClusterId = round.ClusterId.Value;
}
else
{
IcmTeam.ClusterId = null;
}
var PartName = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
if (!string.IsNullOrWhiteSpace(PartName))
{
var round = uow.Connection.TryFirst <PartRow>(q => q
.Select(part.PartId)
.Where(part.PartName == PartName));
if (PartName == null)
{
response.ErrorList.Add("Error On Row " + row + ": Part with name '" +
PartName + "' is not found!");
continue;
}
IcmTeam.PartId = round.PartId.Value;
}
else
{
IcmTeam.PartId = null;
}
IcmTeam.MonitorName = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
IcmTeam.TeamMemberName = Convert.ToString(worksheet.Cells[row, 8].Value ?? "");
if (string.IsNullOrEmpty(Convert.ToString(worksheet.Cells[row, 9].Value ?? "")))
{
IcmTeam.TeamNo = "UN";
}
else
{
IcmTeam.TeamNo = Convert.ToString(worksheet.Cells[row, 9].Value ?? "");
}
string ddate = worksheet.Cells[row, 10].Text;
if (string.IsNullOrEmpty(ddate))
{
IcmTeam.ReportDate = DateTime.Now;
}
else
{
//IcmTeam.ReportDate = DateTime.TryParseExact(ddate, "yyyy/MM/dd", CultureInfo.InvariantCulture);
IcmTeam.ReportDate = DateTime.Now;
}
if (string.IsNullOrEmpty(Convert.ToString(worksheet.Cells[row, 11].Value ?? "")))
{
IcmTeam.MonitVisitTime = "UN";
}
else
{
IcmTeam.MonitVisitTime = Convert.ToString(worksheet.Cells[row, 11].Value ?? "");
}
IcmTeam.IsTeamResident = Convert.ToBoolean(worksheet.Cells[row, 12].Value ?? false);
IcmTeam.BothTrained = Convert.ToBoolean(worksheet.Cells[row, 13].Value ?? false);
IcmTeam.HaveClearMap = Convert.ToBoolean(worksheet.Cells[row, 14].Value ?? false);
IcmTeam.AbleKnowArea = Convert.ToBoolean(worksheet.Cells[row, 15].Value ?? false);
IcmTeam.TeamGHtoH = Convert.ToBoolean(worksheet.Cells[row, 16].Value ?? false);
IcmTeam.OpvVialsKeptDry = Convert.ToBoolean(worksheet.Cells[row, 17].Value ?? false);
IcmTeam.UsingOpvStage3or4 = Convert.ToBoolean(worksheet.Cells[row, 18].Value ?? false);
IcmTeam.AskingAbout011C = Convert.ToBoolean(worksheet.Cells[row, 19].Value ?? false);
IcmTeam.AskingforGnssc = Convert.ToBoolean(worksheet.Cells[row, 20].Value ?? false);
IcmTeam.RecNameOfAbsentRefC = Convert.ToBoolean(worksheet.Cells[row, 21].Value ?? false);
IcmTeam.AskingAfpCases = Convert.ToBoolean(worksheet.Cells[row, 22].Value ?? false);
IcmTeam.VistedBySupervisor = Convert.ToBoolean(worksheet.Cells[row, 23].Value ?? false);
IcmTeam.TeamHasAchw = Convert.ToBoolean(worksheet.Cells[row, 24].Value ?? false);
IcmTeam.TeamHasFemale = Convert.ToBoolean(worksheet.Cells[row, 25].Value ?? false);
IcmTeam.SocMobAccompanyTeam = Convert.ToBoolean(worksheet.Cells[row, 26].Value ?? false);
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
IcmTeam.TenantId = Users.TenantId.Value;
}
else
{
IcmTeam.TenantId = null;
}
if (IcmTeam.IcmTeamId == null)
{
new IcmTeamRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = IcmTeam
});
response.Inserted = response.Inserted + 1;
}
else
{
new IcmTeamRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = IcmTeam,
EntityId = IcmTeam.IcmTeamId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var rowF = RowsRow.Fields;
var proj = ProjectorsRow.Fields;
var phas = PhasesRow.Fields;
var clie = ClientsRow.Fields;
var part = PartsRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var rowReaded = uow.Connection.TryFirst <RowsRow>(q => q
.Select(rowF.RowId)
.Where(
rowF.ClientName == Convert.ToString(worksheet.Cells[row, 2].Value ?? "") &&
rowF.ClassifierNumber == Convert.ToInt16(worksheet.Cells[row, 3].Value ?? 0) &&
rowF.Number == Convert.ToInt16(worksheet.Cells[row, 4].Value ?? 0)));
if (rowReaded == null)
{
rowReaded = new RowsRow {
}
}
;
else
{
// avoid assignment errors
rowReaded.TrackWithChecks = false;
}
var projectorName = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
if (!string.IsNullOrWhiteSpace(projectorName))
{
var project = uow.Connection.TryFirst <ProjectorsRow>(q => q
.Select(proj.ProjectorId)
.Where(proj.Name == projectorName));
if (project == null)
{
project = new ProjectorsRow
{
Name = projectorName
};
var phaseId = Convert.ToInt32(uow.Connection.InsertAndGetID(project));
rowReaded.ProjectorId = phaseId;
}
else
{
rowReaded.ProjectorId = project.ProjectorId.Value;
}
}
else
{
response.ErrorList.Add("Error On Row " + row + ", Projector Name can't be empty");
continue;
}
var phaseName = Convert.ToString(worksheet.Cells[row, 10].Value ?? "");
if (!string.IsNullOrWhiteSpace(phaseName))
{
var phase = uow.Connection.TryFirst <PhasesRow>(q => q
.Select(phas.PhaseId)
.Where(phas.Name == phaseName));
if (phase == null)
{
phase = new PhasesRow
{
Name = phaseName
};
var phaseId = Convert.ToInt32(uow.Connection.InsertAndGetID(phase));
rowReaded.PhaseId = phaseId;
}
else
{
rowReaded.PhaseId = phase.PhaseId.Value;
}
}
else
{
response.ErrorList.Add("Error On Row " + row + ", Phase Name can't be empty");
continue;
}
var clientName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(clientName))
{
var client = uow.Connection.TryFirst <ClientsRow>(q => q
.Select(clie.ClientsId)
.Where(clie.Name == clientName));
if (client == null)
{
client = new ClientsRow
{
Name = clientName
};
var clientId = Convert.ToInt32(uow.Connection.InsertAndGetID(client));
rowReaded.ClientId = clientId;
}
else
{
rowReaded.ClientId = client.ClientsId.Value;
}
}
else
{
response.ErrorList.Add("Error On Row " + row + ", Client Name can't be empty");
continue;
}
var partName = Convert.ToString(worksheet.Cells[row, 9].Value ?? "");
if (!string.IsNullOrWhiteSpace(partName))
{
var parts = uow.Connection.TryFirst <PartsRow>(q => q
.Select(part.PartId)
.Where(part.Name == partName));
if (parts == null)
{
parts = new PartsRow()
{
Name = partName
};
var partId = Convert.ToInt32(uow.Connection.InsertAndGetID(parts));
rowReaded.PartId = partId;
}
else
{
rowReaded.PartId = parts.PartId.Value;
}
}
else
{
response.ErrorList.Add("Error On Row " + row + ", Part Name can't be empty");
continue;
}
rowReaded.ClassifierNumber = Convert.ToInt16(worksheet.Cells[row, 3].Value ?? 0);
rowReaded.Number = Convert.ToInt16(worksheet.Cells[row, 4].Value ?? 0);
rowReaded.Object = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
rowReaded.SubObject = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
rowReaded.Date = DateTime.Parse(worksheet.Cells[row, 8].Value.ToString(), System.Globalization.CultureInfo.CreateSpecificCulture("bg-BG"));
rowReaded.FoldersCount = Convert.ToString(worksheet.Cells[row, 11].Value ?? "");
if (rowReaded.RowId == null)
{
new MyRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = rowReaded
});
response.Inserted = response.Inserted + 1;
}
else
{
new MyRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = rowReaded,
EntityId = rowReaded.RowId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var Sup = IcmSupervisorRow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var clc = ClusterRow.Fields;
var vehi = VehicletypeRow.Fields;
var part = PartRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
var partName = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
var clusterName = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var IcmSupervisor = uow.Connection.TryFirst <IcmSupervisorRow>(q => q
.Select(Sup.IcmSupervisorId)
.Where(Sup.DistrictDcode == districtName & Sup.RoundName == roundName1 & Sup.Cname == clusterName & Sup.PartPartName == partName));
if (IcmSupervisor == null)
{
IcmSupervisor = new IcmSupervisorRow
{
DistrictDcode = districtName,
RoundName = roundName1,
Cname = clusterName,
PartPartName = partName
}
}
;
else
{
// avoid assignment errors
IcmSupervisor.TrackWithChecks = false;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
IcmSupervisor.RoundId = round.RoundId.Value;
}
else
{
IcmSupervisor.RoundId = null;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName));
if (dName == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
dName + "' is not found!");
continue;
}
IcmSupervisor.DistrictId = district.DistrictId.Value;
}
else
{
IcmSupervisor.DistrictId = null;
}
var partname = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
if (!string.IsNullOrWhiteSpace(partname))
{
var parts = uow.Connection.TryFirst <PartRow>(q => q
.Select(part.PartId)
.Where(part.PartName == partname));
if (dName == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
dName + "' is not found!");
continue;
}
IcmSupervisor.PartId = parts.PartId.Value;
}
else
{
IcmSupervisor.PartId = null;
}
var Cluster = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
if (!string.IsNullOrWhiteSpace(Cluster))
{
var round = uow.Connection.TryFirst <ClusterRow>(q => q
.Select(clc.ClusterId)
.Where(clc.Cname == Cluster & clc.DistrictDcode == districtName));
if (Cluster == null)
{
response.ErrorList.Add("Error On Row " + row + ": Cluster with name '" +
Cluster + "' is not found!");
continue;
}
IcmSupervisor.ClusterId = round.ClusterId.Value;
}
else
{
IcmSupervisor.ClusterId = null;
}
IcmSupervisor.ReportDate = DateTime.Now;
IcmSupervisor.Supervisor = Convert.ToString(worksheet.Cells[row, 8].Value ?? "");
IcmSupervisor.MonitorName = Convert.ToString(worksheet.Cells[row, 9].Value ?? "");
IcmSupervisor.IsSuperResidentOfArea = Convert.ToBoolean(worksheet.Cells[row, 10].Value ?? false);
IcmSupervisor.IsSuperTrained = Convert.ToBoolean(worksheet.Cells[row, 11].Value ?? false);
IcmSupervisor.IsSuperCarryOpv = Convert.ToBoolean(worksheet.Cells[row, 12].Value ?? false);
IcmSupervisor.IsSuperHasClearMap = Convert.ToBoolean(worksheet.Cells[row, 13].Value ?? false);
IcmSupervisor.IsSuperFillingHhChecklist = Convert.ToBoolean(worksheet.Cells[row, 14].Value ?? false);
IcmSupervisor.IsTransProviddToSuper = Convert.ToBoolean(worksheet.Cells[row, 15].Value ?? false);
var vehicle = Convert.ToString(worksheet.Cells[row, 16].Value ?? "");
if (!string.IsNullOrWhiteSpace(vehicle))
{
var round = uow.Connection.TryFirst <VehicletypeRow>(q => q
.Select(vehi.VehicletypeId)
.Where(vehi.Typename == vehicle));
if (vehicle == null)
{
response.ErrorList.Add("Error On Row " + row + ": Vehicle with name '" +
vehicle + "' is not found!");
continue;
}
IcmSupervisor.VehicletypeId = round.VehicletypeId.Value;
}
else
{
IcmSupervisor.VehicletypeId = null;
}
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
IcmSupervisor.TenantId = Users.TenantId.Value;
}
else
{
IcmSupervisor.TenantId = null;
}
if (IcmSupervisor.IcmSupervisorId == null)
{
new IcmSupervisorRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = IcmSupervisor
});
response.Inserted = response.Inserted + 1;
}
else
{
new IcmSupervisorRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = IcmSupervisor,
EntityId = IcmSupervisor.IcmSupervisorId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var TempVitA = TempVitaminARow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var clc = ClusterRow.Fields;
var prov = ProvinceRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
var clusterName = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
var provName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var TempA = uow.Connection.TryFirst <TempVitaminARow>(q => q
.Select(TempVitA.TempVitaminAId)
.Where(TempVitA.DistrictName == districtName & TempVitA.RoundName == roundName1 & TempVitA.ClusterName == clusterName & TempVitA.ProvinceName == provName));
if (TempA == null)
{
TempA = new TempVitaminARow
{
DistrictName = districtName,
RoundName = roundName1,
ClusterName = clusterName,
ProvinceName = provName
}
}
;
else
{
// avoid assignment errors
TempA.TrackWithChecks = false;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
TempA.RoundId = round.RoundId.Value;
}
else
{
TempA.RoundId = null;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName & d.Pname == provName));
if (dName == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
dName + "' is not found!");
continue;
}
TempA.DistrictId = district.DistrictId.Value;
}
else
{
TempA.DistrictId = null;
}
var Cluster = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
if (!string.IsNullOrWhiteSpace(Cluster))
{
var round = uow.Connection.TryFirst <ClusterRow>(q => q
.Select(clc.ClusterId)
.Where(clc.DistrictDcode == districtName & clc.Cname == Cluster));
if (Cluster == null)
{
response.ErrorList.Add("Error On Row " + row + ": Cluster with name '" +
Cluster + "' is not found!");
continue;
}
TempA.ClusterId = round.ClusterId.Value;
}
else
{
TempA.ClusterId = null;
}
var Province = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (!string.IsNullOrWhiteSpace(Province))
{
var round = uow.Connection.TryFirst <ProvinceRow>(q => q
.Select(prov.ProvinceId)
.Where(prov.Pname == Province));
if (TempA == null)
{
response.ErrorList.Add("Error On Row " + row + ": Province with name '" +
Province + "' is not found!");
continue;
}
TempA.ProvinceId = round.ProvinceId.Value;
}
else
{
TempA.ProvinceId = null;
}
TempA.TargetU5Cases = Convert.ToInt16(worksheet.Cells[row, 6].Value ?? 0);
TempA.D1VitADist = Convert.ToInt16(worksheet.Cells[row, 7].Value ?? 0);
TempA.D1VitAUse = Convert.ToInt16(worksheet.Cells[row, 8].Value ?? 0);
TempA.D1C611Months = Convert.ToInt16(worksheet.Cells[row, 9].Value ?? 0);;
TempA.D1C1259Months = Convert.ToInt16(worksheet.Cells[row, 10].Value ?? 0);
TempA.D2VitADist = Convert.ToInt16(worksheet.Cells[row, 11].Value ?? 0);
TempA.D2VitAUse = Convert.ToInt16(worksheet.Cells[row, 12].Value ?? 0);
TempA.D2C611Months = Convert.ToInt16(worksheet.Cells[row, 13].Value ?? 0);
TempA.D2C1259Months = Convert.ToInt16(worksheet.Cells[row, 14].Value ?? 0);
TempA.D3VitADist = Convert.ToInt16(worksheet.Cells[row, 15].Value ?? 0);
TempA.D3VitAUse = Convert.ToInt16(worksheet.Cells[row, 16].Value ?? 0);
TempA.D3C611Months = Convert.ToInt16(worksheet.Cells[row, 17].Value ?? 0);
TempA.D3C1259Months = Convert.ToInt16(worksheet.Cells[row, 18].Value ?? 0);
TempA.D5VitADist = Convert.ToInt16(worksheet.Cells[row, 19].Value ?? 0);
TempA.D5VitAUse = Convert.ToInt16(worksheet.Cells[row, 20].Value ?? 0);
TempA.D5C611Months = Convert.ToInt16(worksheet.Cells[row, 21].Value ?? 0);
TempA.D5C1259Months = Convert.ToInt16(worksheet.Cells[row, 22].Value ?? 0);
TempA.PemtremtManager = Convert.ToString(worksheet.Cells[row, 23].Value ?? "");
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
TempA.TenantId = Users.TenantId.Value;
}
else
{
TempA.TenantId = null;
}
if (TempA.TempVitaminAId == null)
{
new TempVitaminARepository().Create(uow, new SaveRequest <MyRow>
{
Entity = TempA
});
response.Inserted = response.Inserted + 1;
}
else
{
new TempVitaminARepository().Update(uow, new SaveRequest <MyRow>
{
Entity = TempA,
EntityId = TempA.TempVitaminAId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var d = DistrictRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var target = Lookup.Entities.TargetPopRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var districtId = Convert.ToString(worksheet.Cells[row, 4].Value ?? 0);
var year = Convert.ToInt32(worksheet.Cells[row, 5].Value ?? 0);
if (districtId.IsTrimmedEmpty())
{
continue;
}
var targ = uow.Connection.TryFirst <TargetPopRow>(q => q
.Select(target.TargetId)
.Where(target.DistrictDcode == districtId & target.YearId == year));
if (targ == null)
{
targ = new TargetPopRow
{
DistrictDcode = districtId,
YearId = year
}
}
;
else
{
// avoid assignment errors
targ.TrackWithChecks = false;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
var Yearm = Convert.ToInt32(worksheet.Cells[row, 5].Value ?? 0);
if (!string.IsNullOrEmpty(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName));
if (district == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with code '" +
dName + "' is not found!");
continue;
}
targ.DistrictId = district.DistrictId.Value;
}
else
{
targ.DistrictId = null;
}
targ.YearId = Convert.ToInt16(worksheet.Cells[row, 5].Value ?? 0);
targ.Target = Convert.ToInt64(worksheet.Cells[row, 6].Value ?? 0);
targ.LpDs = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
if (targ.TargetId == null)
{
new TargetPopRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = targ
});
response.Inserted = response.Inserted + 1;
}
else
{
new TargetPopRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = targ,
EntityId = targ.TargetId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.StackTrace);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "Filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var c = CategoryRow.Fields;
var t = CategoryTypeRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var categoryName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (categoryName.IsTrimmedEmpty())
{
continue;
}
var category = uow.Connection.TryFirst <CategoryRow>(q => q
.Select(c.CategoryID)
.Where(c.CategoryName == categoryName));
if (category == null)
{
category = new CategoryRow
{
CategoryName = categoryName
}
}
;
else
{
category.TrackWithChecks = false;
}
#region Type
var typeName = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
if (!string.IsNullOrWhiteSpace(typeName))
{
var type = uow.Connection.TryFirst <CategoryTypeRow>(q => q
.Select(t.CategoryTypeID)
.Where(t.CategoryType == typeName));
if (type == null)
{
response.ErrorList.Add("Error On Row" + row + ": Category with name '" +
typeName + "' is not found!");
continue;
}
category.CategoryTypeID = Convert.ToInt16(type.CategoryTypeID.Value);
}
else
{
category.CategoryTypeID = null;
}
#endregion Type
category.CategoryCode = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
category.Description = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (category.CategoryID == null)
{
new CategoryRepository().Create(uow, new SaveWithLocalizationRequest <MyRow>
{
Entity = category
});
response.Inserted = response.Inserted + 1;
}
else
{
new CategoryRepository().Update(uow, new SaveWithLocalizationRequest <MyRow>
{
Entity = category,
EntityId = category.CategoryID.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
if (Path.GetExtension(request.FileName) != ".xlsx")
{
throw new Exception("文件格式不对，只支持Excel2007及以上版本");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
{
ep.Load(fs);
}
var p = MyRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
bool isCreat = false;
var user = (UserDefinition)Authorization.UserDefinition;
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
// every circulation should reset the flag
isCreat = false;
// check the productno is empty or not
var masterNo = worksheet.Cells[row, 2].GetValue <string>();
var no = worksheet.Cells[row, 3].GetValue <string>();
if (masterNo.IsTrimmedEmpty() || no.IsTrimmedEmpty())
{
continue;
}
// search the productno whether existes in the database
no = no.Trim();
masterNo = masterNo.Trim();
var yundan = uow.Connection.TryFirst <MyRow>(q => q
.Select(p.SubAwb, p.MasterAwb, p.Id)
.Where(p.SubAwb == no && p.MasterAwb == masterNo && p.TenantId == user.TenantId));
if (yundan == null)
{
yundan = new MyRow
{
SubAwb = no,
MasterAwb = masterNo
};
isCreat = true;
}
else
{
isCreat = false;
}
//yundan.ApplicationUnit = worksheet.Cells[row, 1].GetValue<string>();
yundan.Flight = worksheet.Cells[row, 1].GetValue <string>();
int.TryParse(worksheet.Cells[row, 4].GetValue <string>(), out int amount);
yundan.Amount = amount;
double.TryParse(worksheet.Cells[row, 5].GetValue <string>(), out double weight);
yundan.Weight = weight;
yundan.Description = worksheet.Cells[row, 6].GetValue <string>();
yundan.Status = worksheet.Cells[row, 7].GetValue <string>();
yundan.IsChecked = Entities.StateKind.NoChecked;
//yundan.TenantId = user.TenantId;
if (isCreat)
{
new DeclarationDataRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = yundan
});
response.Inserted = response.Inserted + 1;
}
else
{
new DeclarationDataRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = yundan,
EntityId = yundan.Id
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
ex.Log();
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var p = ProductRow.Fields;
var s = SupplierRow.Fields;
var c = CategoryRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var productName = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
if (productName.IsTrimmedEmpty())
{
continue;
}
var product = uow.Connection.TryFirst <ProductRow>(q => q
.Select(p.ProductID)
.Where(p.ProductName == productName));
if (product == null)
{
product = new ProductRow
{
ProductName = productName
}
}
;
else
{
// avoid assignment errors
product.TrackWithChecks = false;
}
var supplierName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(supplierName))
{
var supplier = uow.Connection.TryFirst <SupplierRow>(q => q
.Select(s.SupplierID)
.Where(s.CompanyName == supplierName));
if (supplier == null)
{
response.ErrorList.Add("Error On Row " + row + ": Supplier with name '" +
supplierName + "' is not found!");
continue;
}
product.SupplierID = supplier.SupplierID.Value;
}
else
{
product.SupplierID = null;
}
var categoryName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (!string.IsNullOrWhiteSpace(categoryName))
{
var category = uow.Connection.TryFirst <CategoryRow>(q => q
.Select(c.CategoryID)
.Where(c.CategoryName == categoryName));
if (category == null)
{
response.ErrorList.Add("Error On Row " + row + ": Category with name '" +
categoryName + "' is not found!");
continue;
}
product.CategoryID = category.CategoryID.Value;
}
else
{
product.CategoryID = null;
}
product.QuantityPerUnit = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
product.UnitPrice = Convert.ToDecimal(worksheet.Cells[row, 5].Value ?? 0);
product.UnitsInStock = Convert.ToInt16(worksheet.Cells[row, 6].Value ?? 0);
product.UnitsOnOrder = Convert.ToInt16(worksheet.Cells[row, 7].Value ?? 0);
product.ReorderLevel = Convert.ToInt16(worksheet.Cells[row, 8].Value ?? 0);
if (product.ProductID == null)
{
new ProductRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = product
});
response.Inserted = response.Inserted + 1;
}
else
{
new ProductRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = product,
EntityId = product.ProductID.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var Icn = IcnRow.Fields;
var d = DistrictRow.Fields;
var r = RoundRow.Fields;
var usr = Administration.Entities.UserRow.Fields;
var clc = ClusterRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 3; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var roundName1 = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
var provName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
var districtName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
var clusterName = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
var areaName = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
if (districtName.IsTrimmedEmpty())
{
continue;
}
var IcnRecord = uow.Connection.TryFirst <IcnRow>(q => q
.Select(Icn.IcnId)
.Where(Icn.DistrictDcode == districtName & Icn.RoundName == roundName1 & Icn.Cname == clusterName & Icn.Province == provName & Icn.Area == areaName));
if (IcnRecord == null)
{
IcnRecord = new IcnRow
{
DistrictDcode = districtName,
RoundName = roundName1,
Cname = clusterName,
Province = provName,
Area = areaName
}
}
;
else
{
// avoid assignment errors
IcnRecord.TrackWithChecks = false;
}
var roundName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(roundName))
{
var round = uow.Connection.TryFirst <RoundRow>(q => q
.Select(r.RoundId)
.Where(r.RoundName == roundName));
if (round == null)
{
response.ErrorList.Add("Error On Row " + row + ": Round with name '" +
roundName + "' is not found!");
continue;
}
IcnRecord.RoundId = round.RoundId.Value;
}
else
{
IcnRecord.RoundId = null;
}
var dName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (!string.IsNullOrWhiteSpace(dName))
{
var district = uow.Connection.TryFirst <DistrictRow>(q => q
.Select(d.DistrictId)
.Where(d.Dcode == dName));
if (dName == null)
{
response.ErrorList.Add("Error On Row " + row + ": District with name '" +
dName + "' is not found!");
continue;
}
IcnRecord.DistrictId = district.DistrictId.Value;
}
else
{
IcnRecord.DistrictId = null;
}
var Cluster = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
if (!string.IsNullOrWhiteSpace(Cluster))
{
var round = uow.Connection.TryFirst <ClusterRow>(q => q
.Select(clc.ClusterId)
.Where(clc.Cname == Cluster & clc.DistrictDcode == districtName));
if (Cluster == null)
{
response.ErrorList.Add("Error On Row " + row + ": Cluster with name '" +
Cluster + "' is not found!");
continue;
}
IcnRecord.ClusterId = round.ClusterId.Value;
}
else
{
IcnRecord.ClusterId = null;
}
IcnRecord.Area = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
IcnRecord.RegAbsent = Convert.ToInt16(worksheet.Cells[row, 7].Value ?? 0);
IcnRecord.VacAbsent = Convert.ToInt16(worksheet.Cells[row, 8].Value ?? 0);
IcnRecord.RegNss = Convert.ToInt16(worksheet.Cells[row, 9].Value ?? 0);
IcnRecord.VacNss = Convert.ToInt16(worksheet.Cells[row, 10].Value ?? 0);
IcnRecord.RegRefusal = Convert.ToInt16(worksheet.Cells[row, 11].Value ?? 0);
IcnRecord.VacRefusal = Convert.ToInt16(worksheet.Cells[row, 12].Value ?? 0);
IcnRecord.RegUnrecorded = Convert.ToInt16(worksheet.Cells[row, 13].Value ?? 0);
IcnRecord.VacUnrecorded = Convert.ToInt16(worksheet.Cells[row, 14].Value ?? 0);
var user = User.Identity.Name;
if (!string.IsNullOrWhiteSpace(user))
{
var Users = uow.Connection.TryFirst <Administration.Entities.UserRow>(q => q
.Select(usr.TenantId)
.Where(usr.Username == user));
if (Users == null)
{
response.ErrorList.Add("Error On Row " + row + ": TenantID with name '" +
Users + "' is not found!");
continue;
}
IcnRecord.TenantId = Users.TenantId.Value;
}
else
{
IcnRecord.TenantId = null;
}
if (IcnRecord.IcnId == null)
{
new IcnRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = IcnRecord
});
response.Inserted = response.Inserted + 1;
}
else
{
new IcnRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = IcnRecord,
EntityId = IcnRecord.IcnId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
