public async Task <ToraObject> Upload([FromForm] UploadToraDocumentViewModel document)
{
SchemeController sc = new SchemeController(_context, _hostingEnvironment, _sLogger);
var schemeModel = await sc.GetAllAsync();
var toraDocumentDirectoryPath = Path.Combine(_hostingEnvironment.WebRootPath, "tora", "document");
var toraDocumentFilePath = Path.Combine(toraDocumentDirectoryPath, document.RegionId, document.File.FileName);
var regionId = document.RegionId;
using (var stream = document.File.OpenReadStream())
using (ExcelPackage package = new ExcelPackage(stream))
{
ExcelWorkbook workbook = package.Workbook;
ToraObject to = new ToraObject();
if (workbook.Worksheets.Count > 0)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
List <ToraObject> toraObjects = new List <ToraObject>();
for (int i = 1; i <= rowCount; i += 22)
{
if (worksheet.Cells[i, 1].Value != null)
{
if (worksheet.Cells[i, 1].Value.ToString().Trim().ToLower() == "no")
{
to = new ToraObject();
to.FkRegionId = regionId;
to.Name = worksheet.Cells[(i + 2), 4].Value != null ? worksheet.Cells[(i + 2), 4].Value.ToString().Trim() : "";
if (worksheet.Cells[(i + 6), 4].Value != null)
{
if (decimal.TryParse(worksheet.Cells[(i + 6), 4].Value.ToString().Trim().Split(" ")[0].Replace(",", "."), out decimal size))
{
to.Size = size;
}
else
{
to.Size = 0;
}
}
else
{
to.Size = 0;
}
//to.TotalTenants = worksheet.Cells[(i + 7), 4].Value != null ? worksheet.Cells[(i + 7), 4].Value.ToString().Trim().Split(" ")[0] : "";
if (worksheet.Cells[(i + 8), 4].Value != null)
{
if (worksheet.Cells[(i + 8), 4].Value.ToString().Trim() != "-")
{
if (worksheet.Cells[(i + 8), 4].Value.ToString().ToLower().Trim() == "hutan")
{
to.RegionalStatus = RegionalStatus.Forest;
}
else if (worksheet.Cells[(i + 8), 4].Value.ToString().ToLower().Trim() == "non hutan")
{
to.RegionalStatus = RegionalStatus.NonForest;
}
else
{
to.RegionalStatus = RegionalStatus.NotSpecified;
}
}
else
{
to.RegionalStatus = RegionalStatus.NotSpecified;
}
}
else
{
to.RegionalStatus = RegionalStatus.NotSpecified;
}
to.LandType = worksheet.Cells[(i + 9), 4].Value != null ? worksheet.Cells[(i + 9), 4].Value.ToString().Trim() : "";
to.Livelihood = worksheet.Cells[(i + 10), 4].Value != null ? worksheet.Cells[(i + 10), 4].Value.ToString().Trim() : "";
if (worksheet.Cells[(i + 11), 4].Value != null)
{
foreach (var item in schemeModel)
{
if (item.Name.ToLower() == worksheet.Cells[(i + 11), 4].Value.ToString().ToLower().Trim())
{
to.FkSchemeId = item.Id;
}
}
}
else
{
to.FkSchemeId = 1;
}
if (worksheet.Cells[(i + 13), 4].Value != null)
{
if (worksheet.Cells[(i + 13), 4].Value.ToString().Trim() != "-")
{
if (worksheet.Cells[(i + 13), 4].Value.ToString().ToLower().Contains("negara"))
{
to.LandStatus = LandStatus.Government;
}
else if (worksheet.Cells[(i + 13), 4].Value.ToString().ToLower().Contains("swasta"))
{
to.LandStatus = LandStatus.Private;
}
else
{
to.LandStatus = LandStatus.Others;
}
}
else
{
to.LandStatus = LandStatus.Others;
}
}
else
{
to.LandStatus = LandStatus.Others;
}
to.LandTenureHistory = worksheet.Cells[(i + 15), 3].Value != null ? worksheet.Cells[(i + 15), 3].Value.ToString().Trim() : "";
to.ConflictChronology = worksheet.Cells[(i + 18), 4].Value != null ? worksheet.Cells[(i + 18), 4].Value.ToString().Trim() : "";
to.FormalAdvocacyProgress = worksheet.Cells[(i + 20), 4].Value != null ? worksheet.Cells[(i + 20), 4].Value.ToString().Trim() : "";
to.NonFormalAdvocacyProgress = worksheet.Cells[(i + 22), 4].Value != null ? worksheet.Cells[(i + 22), 4].Value.ToString().Trim() : "";
await PostAsync(to);
toraObjects.Add(to);
}
}
}
if (workbook.Worksheets.Count > 1)
{
ToraSubjectController ts = new ToraSubjectController(_context, _hostingEnvironment, _tsLogger);
await ts.Upload(toraObjects, package);
}
await IOHelper.StreamCopyAsync(toraDocumentFilePath, document.File);
}
return(to);
}
}
public async Task <FileStreamResult> Download(int id)
{
var objectModel = await dbContext.Set <ToraObject>().FirstOrDefaultAsync(t => t.Id == id);
SchemeController sc = new SchemeController(_context, _hostingEnvironment, _sLogger);
var schemeModel = await sc.GetAllAsync();
var region = await dbContext.Set <Region>()
.Include(r => r.Parent)
.Include(r => r.Parent.Parent)
.FirstOrDefaultAsync(r => r.Id == objectModel.FkRegionId);
List <ToraSubject> subjectModel = await dbContext.Set <ToraSubject>()
.Where(r => r.FkToraObjectId == objectModel.Id)
.ToListAsync();
var templateFileName = @"Template_Object_Subject_Tora.xlsx";
var templateFilePath = Path.Combine(_hostingEnvironment.WebRootPath, "template", templateFileName);
var outputStream = new MemoryStream();
using (var templateStream = new MemoryStream())
using (var fileStream = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read, FileShare.Read))
{
await fileStream.CopyToAsync(templateStream);
templateStream.Position = 0;
using (ExcelPackage package = new ExcelPackage(templateStream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
//Add values
worksheet.Cells["D3"].Value = objectModel.Name;
worksheet.Cells["D4"].Value = region.Name;
worksheet.Cells["D5"].Value = region.Parent.Name;
worksheet.Cells["D6"].Value = region.Parent.Parent.Name;
worksheet.Cells["D7"].Value = 0;
worksheet.Cells["D8"].Value = subjectModel.Count;
worksheet.Cells["D9"].Value = TranslateHelper.Translate(objectModel.RegionalStatus.ToString());
worksheet.Cells["D10"].Value = TranslateHelper.Translate(objectModel.LandType);
worksheet.Cells["D11"].Value = objectModel.Livelihood;
if (worksheet.Cells["D12"].Value != null || worksheet.Cells["D12"].Value.ToString() != "")
{
foreach (var item in schemeModel)
{
if (item.Id == objectModel.FkSchemeId)
{
worksheet.Cells["D12"].Value = item.Name;
break;
}
}
}
worksheet.Cells["D14"].Value = TranslateHelper.Translate(objectModel.LandStatus.ToString());
worksheet.Cells["C16"].Value = objectModel.LandTenureHistory;
worksheet.Cells["D19"].Value = objectModel.ConflictChronology;
worksheet.Cells["D21"].Value = objectModel.FormalAdvocacyProgress;
worksheet.Cells["D22"].Value = objectModel.NonFormalAdvocacyProgress;
ExcelWorksheet worksheet2 = package.Workbook.Worksheets[2];
for (int i = 0; i < subjectModel.Count; i++)
{
int row = 2;
worksheet2.Cells["A" + (row + i).ToString()].Value = i + 1;
worksheet2.Cells["B" + (row + i).ToString()].Value = subjectModel[i].Name;
worksheet2.Cells["C" + (row + i).ToString()].Value = TranslateHelper.Translate(subjectModel[i].MaritalStatus.ToString());
worksheet2.Cells["D" + (row + i).ToString()].Value = subjectModel[i].Address.ToString();
worksheet2.Cells["E" + (row + i).ToString()].Value = TranslateHelper.Translate(subjectModel[i].Gender.ToString());
worksheet2.Cells["F" + (row + i).ToString()].Value = subjectModel[i].Age;
worksheet2.Cells["G" + (row + i).ToString()].Value = TranslateHelper.Translate(subjectModel[i].EducationalAttainment.ToString());
worksheet2.Cells["H" + (row + i).ToString()].Value = subjectModel[i].TotalFamilyMembers;
worksheet2.Cells["I" + (row + i).ToString()].Value = TranslateHelper.Translate(subjectModel[i].LandStatus.ToString());
worksheet2.Cells["J" + (row + i).ToString()].Value = subjectModel[i].LandLocation;
worksheet2.Cells["K" + (row + i).ToString()].Value = subjectModel[i].Size;
worksheet2.Cells["L" + (row + i).ToString()].Value = subjectModel[i].PlantTypes;
worksheet2.Cells["M" + (row + i).ToString()].Value = subjectModel[i].Notes;
}
package.SaveAs(outputStream);
}
}
outputStream.Position = 0;
return(File(outputStream, "application/xlsx", "tora.xlsx"));
}
