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);
}
private ServiceResponse HandleUploadRequest(HttpContextBase context)
{
if (context.Request.Form.Files.Count != 1)
{
throw new ArgumentOutOfRangeException("files");
}
var file = context.Request.Form.Files[0];
if (file == null)
{
throw new ArgumentNullException("file");
}
if (file.FileName.IsEmptyOrNull())
{
throw new ArgumentNullException("filename");
}
var processor = new UploadProcessor
{
};
if (processor.ProcessStream(file.OpenReadStream(), Path.GetExtension(file.FileName)))
{
var temporaryFile = "temporary/" + Path.GetFileName(processor.FilePath);
using (var sw = new StreamWriter(System.IO.File.OpenWrite(Path.ChangeExtension(UploadHelper.DbFilePath(temporaryFile), ".orig"))))
sw.WriteLine(file.FileName);
return(new UploadResponse()
{
TemporaryFile = temporaryFile,
Size = processor.FileSize,
IsImage = processor.IsImage,
});
}
else
{
return(new UploadResponse()
{
Error = new ServiceError()
{
Code = "Exception",
Message = processor.ErrorMessage
}
});
}
}
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 = ProductsRow.Fields;
var c = CategoriesRow.Fields;
var m = MeasuresRow.Fields;
var cp = CounterpartiesRow.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, 4].Value ?? "");
if (productName.IsTrimmedEmpty())
{
continue;
}
var product = uow.Connection.TryFirst <ProductsRow>(q => q
.Select(p.ProductId)
.Where(p.ProductName == productName));
if (product == null)
{
product = new ProductsRow
{
ProductName = productName
}
}
;
else
{
product.TrackWithChecks = false;
}
#region Category
var categoryName = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
if (!string.IsNullOrWhiteSpace(categoryName))
{
var category = uow.Connection.TryFirst <CategoriesRow>(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;
}
#endregion Category
#region Measure
var measureName = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
if (!string.IsNullOrWhiteSpace(measureName))
{
var measure = uow.Connection.TryFirst <MeasuresRow>(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;
}
product.MeasureId = measure.MeasureId.Value;
}
else
{
product.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;
// }
// product.CounterpartyID = counterparty.CounterpartyID.ToString();
//}
//else
//{
// product.CounterpartyID = null;
//}
#endregion Counterparty
product.ProductCode = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
product.ProductBarcode = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
product.ProductLabel = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
product.Discontinued = Convert.ToBoolean(worksheet.Cells[row, 5].Value ?? "");
product.QuantityPerUnit = Convert.ToString(worksheet.Cells[row, 8].Value ?? "");
product.UnitPrice = Convert.ToDecimal(worksheet.Cells[row, 9].Value ?? 0);
product.UnitsInStock = Convert.ToInt16(worksheet.Cells[row, 10].Value ?? 0);
product.UnitsOnOrder = Convert.ToInt16(worksheet.Cells[row, 11].Value ?? 0);
product.CounterpartyId = Convert.ToString(worksheet.Cells[row, 13].Value ?? 0);
if (product.ProductId == null)
{
new ProductsRepository().Create(uow, new SaveWithLocalizationRequest <MyRow>
{
Entity = product
});
response.Inserted = response.Inserted + 1;
}
else
{
new ProductsRepository().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);
}
private void DeleteOldFile(FilesToDelete filesToDelete, string oldFilename)
{
if (!oldFilename.IsEmptyOrNull())
{
var actualOldFile = (attr.SubFolder.IsEmptyOrNull() ? "" : (attr.SubFolder + "/")) + oldFilename;
filesToDelete.RegisterOldFile(actualOldFile);
if (attr.CopyToHistory)
{
var oldFilePath = UploadHelper.ToPath(actualOldFile);
string date = DateTime.UtcNow.ToString("yyyyMM", Invariants.DateTimeFormat);
string historyFile = "history/" + date + "/" + Path.GetFileName(oldFilePath);
if (File.Exists(UploadHelper.DbFilePath(oldFilePath)))
{
UploadHelper.CopyFileAndRelated(UploadHelper.DbFilePath(oldFilePath), UploadHelper.DbFilePath(historyFile), overwrite: true);
}
}
}
}
public static void CheckUploadedImageAndCreateThumbs(ImageUploadEditorAttribute attr, ref string temporaryFile)
{
ImageCheckResult[] supportedFormats = null;
if (!attr.AllowNonImage)
{
supportedFormats = new ImageCheckResult[] {
ImageCheckResult.JPEGImage,
ImageCheckResult.GIFImage,
ImageCheckResult.PNGImage
}
}
;
UploadHelper.CheckFileNameSecurity(temporaryFile);
var checker = new ImageChecker();
checker.MinWidth = attr.MinWidth;
checker.MaxWidth = attr.MaxWidth;
checker.MinHeight = attr.MinHeight;
checker.MaxHeight = attr.MaxHeight;
checker.MaxDataSize = attr.MaxSize;
Image image = null;
try
{
var temporaryPath = UploadHelper.DbFilePath(temporaryFile);
using (var fs = new FileStream(temporaryPath, FileMode.Open))
{
if (attr.MinSize != 0 && fs.Length < attr.MinSize)
{
throw new ValidationError(String.Format(Texts.Controls.ImageUpload.UploadFileTooSmall,
UploadHelper.FileSizeDisplay(attr.MinSize)));
}
if (attr.MaxSize != 0 && fs.Length > attr.MaxSize)
{
throw new ValidationError(String.Format(Texts.Controls.ImageUpload.UploadFileTooBig,
UploadHelper.FileSizeDisplay(attr.MaxSize)));
}
ImageCheckResult result;
if (Path.GetExtension(temporaryFile).ToLowerInvariant() == ".swf")
{
result = ImageCheckResult.FlashMovie;
// validate swf file somehow!
}
else
{
result = checker.CheckStream(fs, true, out image);
}
if (result == ImageCheckResult.InvalidImage &&
attr.AllowNonImage)
{
return;
}
if (result > ImageCheckResult.UnsupportedFormat ||
(supportedFormats != null && Array.IndexOf(supportedFormats, result) < 0))
{
string error = checker.FormatErrorMessage(result);
throw new ValidationError(error);
}
if (result >= ImageCheckResult.FlashMovie)
{
return;
}
string basePath = UploadHelper.TemporaryPath;
string baseFile = Path.GetFileNameWithoutExtension(Path.GetFileName(temporaryPath));
TemporaryFileHelper.PurgeDirectoryDefault(basePath);
if ((attr.ScaleWidth > 0 || attr.ScaleHeight > 0) &&
((attr.ScaleWidth > 0 && (attr.ScaleSmaller || checker.Width > attr.ScaleWidth)) ||
(attr.ScaleHeight > 0 && (attr.ScaleSmaller || checker.Height > attr.ScaleHeight))))
{
using (Image scaledImage = ThumbnailGenerator.Generate(
image, attr.ScaleWidth, attr.ScaleHeight, attr.ScaleMode, Color.Empty))
{
temporaryFile = baseFile + ".jpg";
fs.Close();
scaledImage.Save(Path.Combine(basePath, temporaryFile), System.Drawing.Imaging.ImageFormat.Jpeg);
temporaryFile = "temporary/" + temporaryFile;
}
}
var thumbSizes = attr.ThumbSizes.TrimToNull();
if (thumbSizes == null)
{
return;
}
foreach (var sizeStr in thumbSizes.Replace(";", ",").Split(new char[] { ',' }))
{
var dims = sizeStr.ToLowerInvariant().Split(new char[] { 'x' });
int w, h;
if (dims.Length != 2 ||
!Int32.TryParse(dims[0], out w) ||
!Int32.TryParse(dims[1], out h) ||
w < 0 ||
h < 0 ||
(w == 0 && h == 0))
{
throw new ArgumentOutOfRangeException("thumbSizes");
}
using (Image thumbImage = ThumbnailGenerator.Generate(image, w, h, attr.ThumbMode, Color.Empty))
{
string thumbFile = Path.Combine(basePath,
baseFile + "_t" + w.ToInvariant() + "x" + h.ToInvariant() + ".jpg");
thumbImage.Save(thumbFile);
}
}
}
}
finally
{
if (image != null)
{
image.Dispose();
}
}
}
}
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 static AttachmentList GetAttachmentList(MimeMessage message,
bool forReply, FilesToDelete filesToDelete)
{
var uploadHelper = new UploadHelper("Nortwind/Mail/{3:yyyyMM}/{4}");
var result = new AttachmentList();
result.ByIndex = new Dictionary <int, AttachmentInfo>();
result.InOrder = new List <AttachmentInfo>();
var attachmentNumber = 0;
var visitor = new EmailPreviewVisitor(message, (mime, embedUrl) =>
{
var sameAttachment = result.InOrder.FirstOrDefault(x => x.Mime == mime);
if (sameAttachment != null)
{
sameAttachment.IsEmbedded = sameAttachment.IsEmbedded || embedUrl != null;
return(sameAttachment.Url);
}
++attachmentNumber;
string fileName;
string downUrl = null;
byte[] buffer;
using (var output = new MemoryStream())
{
(mime as MimePart).Content.DecodeTo(output);
buffer = output.GetBuffer();
}
if (mime is MessagePart)
{
fileName = mime.ContentDisposition?.FileName ?? (mime.ContentType.Name ?? "attached" + attachmentNumber + ".eml");
}
else
{
fileName = ((MimePart)mime).FileName ?? "file" + attachmentNumber + ".dat";
}
if (embedUrl != null)
{
downUrl = string.Format("data:{0};base64,{1}",
mime.ContentType.MimeType, Convert.ToBase64String(buffer, 0, buffer.Length));
}
else
{
var processor = new UploadProcessor
{
ThumbWidth = 128,
ThumbHeight = 96
};
if (processor.ProcessStream(new MemoryStream(buffer), Path.GetExtension(fileName)))
{
var temporaryFile = "temporary/" + Path.GetFileName(processor.FilePath);
using (var sw = new StreamWriter(Path.ChangeExtension(UploadHelper.DbFilePath(temporaryFile), ".orig")))
sw.WriteLine(fileName);
var copyResult = uploadHelper.CopyTemporaryFile(temporaryFile, -1, filesToDelete);
downUrl = "/upload/" + copyResult.DbFileName;
}
else
{
downUrl = "?";
}
}
var info = new AttachmentInfo
{
Key = attachmentNumber,
Filename = fileName,
Mime = mime,
Url = downUrl,
IsEmbedded = embedUrl != null,
Size = buffer.Length
};
result.ByIndex[attachmentNumber] = info;
result.InOrder.Add(info);
return(downUrl);
}, forReply);
message.Accept(visitor);
result.HtmlBody = visitor.HtmlBody;
return(result);
}
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 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 override void OnBeforeSave(ISaveRequestHandler handler)
{
var filesToDelete = new FilesToDelete();
UploadHelper.RegisterFilesToDelete(handler.UnitOfWork, filesToDelete);
handler.StateBag[this.GetType().FullName + "_FilesToDelete"] = filesToDelete;
var filename = (StringField)(handler.Row.FindField(this.fileNameField) ?? handler.Row.FindFieldByPropertyName(fileNameField));
var oldFilename = handler.IsCreate ? null : filename[handler.Old];
var newFilename = filename[handler.Row] = filename[handler.Row].TrimToNull();
if (oldFilename.IsTrimmedSame(newFilename))
{
filename[handler.Row] = oldFilename;
return;
}
if (!oldFilename.IsEmptyOrNull())
{
var actualOldFile = ((subFolder != null && !storeSubFolderInDB) ? (subFolder + "/") : "") + oldFilename;
filesToDelete.RegisterOldFile(actualOldFile);
if (copyFileToHistory)
{
var oldFilePath = UploadHelper.ToPath(actualOldFile);
string date = DateTime.UtcNow.ToString("yyyyMM", Invariants.DateTimeFormat);
string historyFile = "history/" + date + "/" + Path.GetFileName(oldFilePath);
if (File.Exists(UploadHelper.DbFilePath(oldFilePath)))
{
UploadHelper.CopyFileAndRelated(UploadHelper.DbFilePath(oldFilePath), UploadHelper.DbFilePath(historyFile), overwrite: true);
}
}
}
if (newFilename == null)
{
if (oldFilename.IsTrimmedEmpty())
{
return;
}
filename[handler.Row] = null;
return;
}
if (!newFilename.ToLowerInvariant().StartsWith("temporary/"))
{
throw new InvalidOperationException("For security reasons, only temporary files can be used in uploads!");
}
if (handler.IsUpdate)
{
var copyResult = CopyTemporaryFile(handler, filesToDelete);
filename[handler.Row] = copyResult.DbFileName;
}
}
public ExcelImportResponse StudentExcelImport(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 s = MyRow.Fields;
var c = CourseRow.Fields;
var b = TextbookRow.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
{
//StudentWholeData
var batchId = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (batchId.IsTrimmedEmpty())
{
continue;
}
var declaration = uow.Connection.TryFirst <MyRow>(q => q
.Select(s.DeclarationId)
.Where(s.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 ?? 0);
declaration.TermName = Convert.ToString(worksheet.Cells[row, 2].Value ?? 0);
declaration.SchoolId = Convert.ToString(worksheet.Cells[row, 4].Value ?? 0);
declaration.SchoolName = Convert.ToString(worksheet.Cells[row, 5].Value ?? 0);
declaration.DepartmentId = Convert.ToString(worksheet.Cells[row, 6].Value ?? 0);
declaration.DepartmentName = Convert.ToString(worksheet.Cells[row, 7].Value ?? 0);
declaration.EducationalType = Convert.ToString(worksheet.Cells[row, 9].Value ?? 0);
declaration.ApprovedAmount = Convert.ToInt32(worksheet.Cells[row, 21].Value ?? 0);
declaration.Priority = Convert.ToString(worksheet.Cells[row, 22].Value ?? 0);
declaration.Phone = Convert.ToString(worksheet.Cells[row, 23].Value ?? 0);
declaration.Remarks = Convert.ToString(worksheet.Cells[row, 25].Value ?? 0);
declaration.CheckState = Convert.ToString(worksheet.Cells[row, 24].Value ?? 0);
declaration.ClassName = Convert.ToString(worksheet.Cells[row, 26].Value ?? 0);
declaration.ClassSize = Convert.ToString(worksheet.Cells[row, 27].Value ?? 0);
declaration.DataSign = Convert.ToString(worksheet.Cells[row, 28].Value ?? 0);
declaration.CourseNum = course.CourseNum.Value;
declaration.TextbookNum2 = textbook.TextbookNum2.Value;
if (declaration.DeclarationId == null)
{
new StudentWholeDataRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = declaration
});
response.Inserted = response.Inserted + 1;
}
else
{
new StudentWholeDataRepository().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 override void OnBeforeSave(ISaveRequestHandler handler)
{
var field = (StringField)(handler.Row.FindField(this.filesField) ?? handler.Row.FindFieldByPropertyName(filesField));
if (!handler.Row.IsAssigned(field))
{
return;
}
var oldFilesJSON = (handler.IsCreate ? null : field[handler.Old]).TrimToNull();
var newFilesJSON = field[handler.Row] = field[handler.Row].TrimToNull();
if (oldFilesJSON.IsTrimmedSame(newFilesJSON))
{
field[handler.Row] = oldFilesJSON;
return;
}
var oldFileList = ParseAndValidate(oldFilesJSON, "oldFiles");
var newFileList = ParseAndValidate(newFilesJSON, "newFiles");
var filesToDelete = new FilesToDelete();
UploadHelper.RegisterFilesToDelete(handler.UnitOfWork, filesToDelete);
handler.StateBag[this.GetType().FullName + "_FilesToDelete"] = filesToDelete;
foreach (var file in oldFileList)
{
var filename = file.Filename.Trim();
if (newFileList.Any(x => String.Compare(x.Filename.Trim(), filename, StringComparison.OrdinalIgnoreCase) == 0))
{
continue;
}
var actualOldFile = ((subFolder != null && !storeSubFolderInDB) ? (subFolder + "/") : "") + filename;
filesToDelete.RegisterOldFile(actualOldFile);
if (copyFilesToHistory)
{
var oldFilePath = UploadHelper.ToPath(actualOldFile);
string date = DateTime.UtcNow.ToString("yyyyMM", Invariants.DateTimeFormat);
string historyFile = "history/" + date + "/" + Path.GetFileName(oldFilePath);
if (File.Exists(UploadHelper.DbFilePath(oldFilePath)))
{
UploadHelper.CopyFileAndRelated(UploadHelper.DbFilePath(oldFilePath), UploadHelper.DbFilePath(historyFile), overwrite: true);
}
}
}
if (newFileList.IsEmptyOrNull())
{
field[handler.Row] = null;
return;
}
if (handler.IsUpdate)
{
field[handler.Row] = CopyTemporaryFiles(handler, oldFileList, newFileList, filesToDelete);
}
}
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)
{
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);
}