public void ThrowsWhenJustDoubleDot()
{
Assert.Throws <MyException>(() =>
{
UploadHelper.CheckFileNameSecurity(" .. ");
});
}
public void ThrowsWhenEndsWithBackSlashSpace()
{
Assert.Throws <MyException>(() =>
{
UploadHelper.CheckFileNameSecurity("b\\ ");
});
}
public void ThrowsWhenDoubleDotSlashAtStart()
{
Assert.Throws <MyException>(() =>
{
UploadHelper.CheckFileNameSecurity("../some");
});
}
public void ThrowsWhenNull()
{
Assert.Throws <MyException>(() =>
{
UploadHelper.CheckFileNameSecurity(null);
});
}
public void ThrowsWhenContainsColon()
{
Assert.Throws <MyException>(() =>
{
UploadHelper.CheckFileNameSecurity("a:b");
});
}
public void ThrowsWhenDoubleDotSlashEnd()
{
Assert.Throws <MyException>(() =>
{
UploadHelper.CheckFileNameSecurity("a/b../");
});
}
public ActionResult Read(string pathInfo)
{
UploadHelper.CheckFileNameSecurity(pathInfo);
var filePath = UploadHelper.DbFilePath(pathInfo);
var mimeType = UploadHelper.GetMimeType(filePath);
// can't create thumbnail in .NET CORE
if (filePath.EndsWith("_t.jpg", StringComparison.OrdinalIgnoreCase) &&
!System.IO.File.Exists(filePath))
{
var f = filePath.Substring(0, filePath.Length - 6);
foreach (var ext in new string[] { ".png", ".gif", ".jpg" })
{
if (System.IO.File.Exists(f + ext))
{
filePath = f + ext;
mimeType = UploadHelper.GetMimeType(filePath);
return(new PhysicalFileResult(f + ext, mimeType));
}
}
}
return(new PhysicalFileResult(filePath, mimeType));
}
public void ThrowsWhenStartsWithSlash()
{
Assert.Throws <MyException>(() =>
{
UploadHelper.CheckFileNameSecurity("/a");
});
}
public string GetReportDesignPath(string reportKey, string designId)
{
if (reportKey.IsEmptyOrNull())
{
throw new ArgumentNullException("reportKey");
}
UploadHelper.CheckFileNameSecurity(designId);
if (reportsRootPath == null)
{
var configuredPath = ConfigurationManager.AppSettings["ReportsRootPath"].TrimToNull() ?? "~/Reports";
if (configuredPath != null && configuredPath.StartsWith("~/"))
{
configuredPath = HostingEnvironment.MapPath(configuredPath);
}
reportsRootPath = configuredPath;
}
var baseFileName = Path.Combine(reportsRootPath, reportKey + "." + designId);
var fileName = baseFileName + ".rdl";
if (!File.Exists(fileName) &&
File.Exists(baseFileName + ".trdx"))
{
return(baseFileName + ".trdx");
}
return(fileName);
}
public void ThrowsWhenEmpty()
{
Assert.Throws <MyException>(() =>
{
UploadHelper.CheckFileNameSecurity(String.Empty);
});
}
public void ThrowsWhenWhitespace()
{
Assert.Throws <MyException>(() =>
{
UploadHelper.CheckFileNameSecurity(" ");
});
}
public void OkForBasicFilenames()
{
UploadHelper.CheckFileNameSecurity("test.jpg");
UploadHelper.CheckFileNameSecurity("x");
UploadHelper.CheckFileNameSecurity("x.y");
UploadHelper.CheckFileNameSecurity("a.txt");
UploadHelper.CheckFileNameSecurity("xyz.png");
}
public ActionResult Read(string pathInfo)
{
UploadHelper.CheckFileNameSecurity(pathInfo);
var filePath = UploadHelper.DbFilePath(pathInfo);
var mimeType = UploadHelper.GetMimeType(filePath);
return(new FilePathResult(filePath, mimeType));
}
private static List <Entities.MailAttachmentsRow> AttachFiles(BodyBuilder builder, string attachments,
FilesToDelete filesToDelete, string fileNameFormat)
{
var result = new List <Entities.MailAttachmentsRow>();
if (string.IsNullOrEmpty(attachments))
{
return(result);
}
var list = JSON.Parse <UploadedFile[]>(attachments);
foreach (var att in list)
{
if (!att.Filename.ToLowerInvariant().StartsWith("temporary/"))
{
throw new InvalidOperationException("Only temporary files can be used for upload!");
}
UploadHelper.CheckFileNameSecurity(att.Filename);
var path = UploadHelper.DbFilePath(att.Filename);
string originalName;
var origFile = System.IO.Path.ChangeExtension(path, ".orig");
if (System.IO.File.Exists(origFile))
{
using (var sr = new System.IO.StreamReader(System.IO.File.OpenRead(origFile)))
originalName = sr.ReadLine();
}
else
{
originalName = "att" + (builder.Attachments.Count + 1) + System.IO.Path.GetExtension(path);
}
var bytes = System.IO.File.ReadAllBytes(path);
var attachment = builder.Attachments.Add(originalName, bytes);
MimeKit.Parameter param;
if (attachment.ContentDisposition.Parameters.TryGetValue("filename", out param))
{
param.EncodingMethod = ParameterEncodingMethod.Rfc2047;
}
var copyResult = new UploadHelper(fileNameFormat).CopyTemporaryFile(
att.Filename, -1, filesToDelete);
result.Add(new Entities.MailAttachmentsRow
{
MimeType = attachment.ContentType.MimeType,
Size = bytes.Length,
OriginalFileName = originalName,
FilePath = copyResult.DbFileName
});
}
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 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 void OkForBasicFilenamesWithSubFolders()
{
UploadHelper.CheckFileNameSecurity("a/b/test.jpg");
UploadHelper.CheckFileNameSecurity("x/x");
UploadHelper.CheckFileNameSecurity("z/u/x.y");
UploadHelper.CheckFileNameSecurity("a-b/c.txt");
UploadHelper.CheckFileNameSecurity("a/test folder/xyz.png");
UploadHelper.CheckFileNameSecurity("a\\b\\test.jpg");
UploadHelper.CheckFileNameSecurity("x\\x");
UploadHelper.CheckFileNameSecurity("z\\u\\x.y");
UploadHelper.CheckFileNameSecurity("a-b\\c.txt");
UploadHelper.CheckFileNameSecurity("a\\test folder\\xyz.png");
}
private static string AttachEmbeddedImages(BodyBuilder builder, string body)
{
int start = 0;
int index;
var src = "src=\"";
string search = src + VirtualPathUtility.ToAbsolute("~/upload/");
while ((index = body.IndexOf(search + "temporary/", start)) >= 0)
{
var end = body.IndexOf('"', index + search.Length);
var url = HttpUtility.HtmlDecode(body.Substring(index + search.Length, end - index - search.Length));
UploadHelper.CheckFileNameSecurity(url);
var path = UploadHelper.DbFilePath(url);
if (!System.IO.File.Exists(path))
{
throw new Exception(url + " temporary file not found!");
}
string originalName;
var origFile = System.IO.Path.ChangeExtension(path, ".orig");
if (System.IO.File.Exists(origFile))
{
using (var sr = new System.IO.StreamReader(System.IO.File.OpenRead(origFile)))
originalName = sr.ReadLine();
}
else
{
originalName = "embed" + (builder.LinkedResources.Count + 1) + System.IO.Path.GetExtension(path);
}
var image = builder.LinkedResources.Add(originalName, System.IO.File.ReadAllBytes(path));
image.ContentId = MimeUtils.GenerateMessageId();
url = "cid:" + image.ContentId;
url = HttpUtility.HtmlEncode(url);
body = body.Substring(0, index + src.Length) + url + body.Substring(end);
start = index + url.Length + 1;
}
return(body);
}
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, 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 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 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 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");
}
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 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 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 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 ExcelMassUploadImportResponse SynchResi(IUnitOfWork uow, ExcelMassUploadImportRequest request)
{
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 response = new ExcelMassUploadImportResponse();
response.ErrorList = new List <string>();
// Get batch of row number/index
var numberList = request.TotalRecord;
var p = MyRow.Fields;
int receivedCount = 0;
/////////////////////////////////////////////////////////////
//ITEM INFO UPLOAD
/////////////////////////////////////////////////////////////
var wsItemInfo = ep.Workbook.Worksheets[1];
if (request.Expedisi == "JNE")
{
for (var currRow = 4; currRow <= (numberList + 4); currRow++)
{
try
{
var noResi = new NoResiRow();
try
{
var dateCode = DateTime.Parse(wsItemInfo.Cells[currRow, 4].Value.ToString()).Year.ToString() +
DateTime.Parse(wsItemInfo.Cells[currRow, 4].Value.ToString()).Month.ToString() +
DateTime.Parse(wsItemInfo.Cells[currRow, 4].Value.ToString()).Day.ToString();
noResi.CustomerName = wsItemInfo.Cells[currRow, 11].Value.ToString();
noResi.Handphone = "";
noResi.Expedisi = "JNE";
noResi.ExpedisiService = wsItemInfo.Cells[currRow, 5].Value.ToString();
noResi.Destination = wsItemInfo.Cells[currRow, 8].Value.ToString();
noResi.Ongkir = decimal.Parse(wsItemInfo.Cells[currRow, 12].Value.ToString());
noResi.NoResi = "0" + wsItemInfo.Cells[currRow, 3].Value.ToString();
noResi.SendDate = dateCode + "_" + noResi.CustomerName.Trim();
noResi.JNEDate = DateTime.Parse(wsItemInfo.Cells[currRow, 4].Value.ToString());
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
ListRequest req = new ListRequest();
req.EqualityFilter = new Dictionary <string, object>
{
{ "CustomerName", noResi.CustomerName },
{ "OrderStatus", "Printed" }
};
var cust = new ViewOrderRepository().List(uow.Connection, req);
if (cust.Entities.Count > 0)
{
RetrieveRequest retReq = new RetrieveRequest();
retReq.EntityId = cust.Entities[0].OrderId;
var order = new OrderRepository().Retrieve(uow.Connection, retReq);
order.Entity.NoResi = noResi.NoResi;
order.Entity.OrderStatus = "Delivery";
order.Entity.Ongkir = noResi.Ongkir;
new OrderRepository().Update(uow, new SaveRequest <OrderRow>()
{
EntityId = order.Entity.OrderId, Entity = order.Entity
});
new NoResiRepository().Create(uow, new SaveRequest <NoResiRow>()
{
Entity = noResi
});
receivedCount++;
}
}
catch (Exception x)
{
string msg = "Row:" + currRow.ToString() + " - " + x.Message;
response.ErrorList.Add(x.Message);
}
}
}
else
{
for (var currRow = 2; currRow <= (numberList); currRow++)
{
try
{
var noResi = new NoResiRow();
try
{
//var dateCode = DateTime.Parse(wsItemInfo.Cells[currRow, 8].Value.ToString()).Year.ToString() +
// //DateTime.Parse(wsItemInfo.Cells[currRow, 8].Value.ToString()).Month.ToString() +
// //DateTime.Parse(wsItemInfo.Cells[currRow, 8].Value.ToString()).Day.ToString();
noResi.CustomerName = wsItemInfo.Cells[currRow, 6].Value.ToString();
noResi.Handphone = wsItemInfo.Cells[currRow, 7].Value.ToString();
noResi.Expedisi = request.Expedisi;
noResi.ExpedisiService = "Standart";
//noResi.Destination = wsItemInfo.Cells[currRow, 8].Value.ToString();
//noResi.Ongkir = decimal.Parse(wsItemInfo.Cells[currRow, 12].Value.ToString());
noResi.NoResi = wsItemInfo.Cells[currRow, 1].Value.ToString();
noResi.SendDate = wsItemInfo.Cells[currRow, 8].Value.ToString();
noResi.NINJADate = wsItemInfo.Cells[currRow, 2].Value.ToString();
noResi.StatusPengiriman = wsItemInfo.Cells[currRow, 3].Value.ToString();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
ListRequest req = new ListRequest();
req.EqualityFilter = new Dictionary <string, object>
{
{ "Handphone", noResi.Handphone.Replace("+", "") },
{ "OrderStatus", "Printed" }
};
var cust = new ViewOrderRepository().List(uow.Connection, req);
ListRequest req1 = new ListRequest();
req1.EqualityFilter = new Dictionary <string, object>
{
{ "Handphone", noResi.Handphone.Replace("+", "") },
{ "OrderStatus", "NewOrder" }
};
var cust1 = new ViewOrderRepository().List(uow.Connection, req1);
if (cust.Entities.Count > 0)
{
RetrieveRequest retReq = new RetrieveRequest();
retReq.EntityId = cust.Entities[0].OrderId;
var order = new OrderRepository().Retrieve(uow.Connection, retReq);
order.Entity.NoResi = noResi.NoResi;
if (noResi.StatusPengiriman == "Lengkap")
{
order.Entity.OrderStatus = "Received";
}
//order.Entity.Ongkir = noResi.Ongkir;
new OrderRepository().Update(uow, new SaveRequest <OrderRow>()
{
EntityId = order.Entity.OrderId,
Entity = order.Entity
});
new NoResiRepository().Create(uow, new SaveRequest <NoResiRow>()
{
Entity = noResi
});
receivedCount++;
}
if (cust1.Entities.Count > 0)
{
RetrieveRequest retReq = new RetrieveRequest();
retReq.EntityId = cust1.Entities[0].OrderId;
var order = new OrderRepository().Retrieve(uow.Connection, retReq);
order.Entity.NoResi = noResi.NoResi;
if (noResi.StatusPengiriman == "Lengkap")
{
order.Entity.OrderStatus = "Received";
}
//order.Entity.Ongkir = noResi.Ongkir;
new OrderRepository().Update(uow, new SaveRequest <OrderRow>()
{
EntityId = order.Entity.OrderId,
Entity = order.Entity
});
new NoResiRepository().Create(uow, new SaveRequest <NoResiRow>()
{
Entity = noResi
});
receivedCount++;
}
}
catch (Exception x)
{
string msg = "Row:" + currRow.ToString() + " - " + x.Message;
response.ErrorList.Add(x.Message);
}
}
}
response.Inserted = receivedCount;
return(response);
}
public ExcelMassUploadImportResponse UploadDataClosing(IUnitOfWork uow, ExcelMassUploadImportRequest request)
{
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 response = new ExcelMassUploadImportResponse();
response.ErrorList = new List <string>();
// Get batch of row number/index
var numberList = request.TotalRecord;
var p = MyRow.Fields;
int receivedCount = 0;
/////////////////////////////////////////////////////////////
//ITEM INFO UPLOAD
/////////////////////////////////////////////////////////////
var wsItemInfo = ep.Workbook.Worksheets[1];
for (var currRow = 4; currRow <= (numberList + 4); currRow++)
{
try
{
var odr = new OrderRow();
try
{
CustomerRow custNew = new CustomerRow();
custNew.CustomerName = wsItemInfo.Cells[currRow, 2].Value.ToString();
custNew.Address = "Bekasi";
SaveResponse savedCust = new CustomerRepository().Create(uow, new SaveRequest <CustomerRow>()
{
Entity = custNew
});
odr.CustomerId = int.Parse(savedCust.EntityId.ToString());
odr.Ongkir = 9000;
odr.OrderStatus = "NewOrder";
odr.DetailList = new List <OrderDetailRow>();
odr.DetailList.Add(new OrderDetailRow()
{
ProductId = 1, Quantity = 1, Price = 125000
});
SaveResponse savedOdr = new OrderRepository().Create(uow, new SaveRequest <OrderRow>()
{
Entity = odr
});
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
catch (Exception x)
{
string msg = "Row:" + currRow.ToString() + " - " + x.Message;
response.ErrorList.Add(x.Message);
}
}
response.Inserted = receivedCount;
return(response);
}
public ExcelMassUploadImportResponse ExcelImport(IUnitOfWork uow, ExcelMassUploadImportRequest request)
{
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 response = new ExcelMassUploadImportResponse();
response.ErrorList = new List <string>();
// Get batch of row number/index
var numberList = request.TotalRecord;
var p = MyRow.Fields;
/////////////////////////////////////////////////////////////
//ITEM INFO UPLOAD
/////////////////////////////////////////////////////////////
var wsItemInfo = ep.Workbook.Worksheets[1];
for (var currRow = 2; currRow <= (numberList + 2); currRow++)
{
try
{
var order = new MyRow();
try
{
order.CustomerCustomerName = wsItemInfo.Cells[currRow, 1].Value.ToString();
order.CustomerHandphone = wsItemInfo.Cells[currRow, 2].Value.ToString();
order.CustomerAddress = wsItemInfo.Cells[currRow, 3].Value.ToString();
order.OrderStatus = "New Order";
new OrderRepository().Create(uow, new SaveRequest <MyRow>()
{
Entity = order
});
response.Inserted = response.Inserted + 1;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
catch (Exception x)
{
string msg = "Row:" + currRow.ToString() + " - " + x.Message;
response.ErrorList.Add(x.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);
}