public ActionResult DataForm(int partnerOrganization, string district, DateTime reportingDate, List<string> indicator, List<decimal> kpiValue)
{
//OutreachCache.RemoveUserData(User.Identity.Name);
string[] districtInfo = district.Split(new[] { "$#" }, StringSplitOptions.None);
OutreachImportViewModel vm = new OutreachImportViewModel();
vm.Dist_Id = Convert.ToInt32(districtInfo[0]);
vm.DistrictName = districtInfo[1];
vm.ReportingDate = reportingDate;
int i = 0;
foreach (string d in indicator)
{
string[] indicatorInfo = d.Split(new[] { "$#" }, StringSplitOptions.None);
vm.IndicatorID = Convert.ToInt32(indicatorInfo[0]);
vm.IndicatorName = indicatorInfo[1];
vm.SubIndicatorName = indicatorInfo[2];
vm.Value = kpiValue[i];
++i;
OutreachCache.Add(User.Identity.Name, vm);
}
dynamic viewModel = new ExpandoObject();
viewModel.PartnerOrganizations = DbHelpers.getPartnerOrganizations();
viewModel.Districts = DbHelpers.getDistricts();
viewModel.Indicators = DbHelpers.getIndicators();
viewModel.OutReachData = OutreachCache.GetAll();
return View(viewModel);
}
public ActionResult DataUpload(HttpPostedFileBase rspFile)
{
// store the file inside ~/App_Data/uploads folder
string fileName = User.Identity.Name + "-" + DateTime.UtcNow + "-" + Path.GetFileName(rspFile.FileName);
fileName = User.Identity.Name;
var path = Path.Combine(Server.MapPath("~/App_Data/uploads"), fileName);
rspFile.SaveAs(path);
//end store file
var viewData = new List<OutreachImportViewModel>();
//reading file
var existingFile = new FileInfo(path);
// Open and read the XlSX file.
ApplicationDbContext db = new ApplicationDbContext();
//Get Indicators and Districts
List<Indicator> Indicators = DbHelpers.getIndicators();
List<District> Districts = DbHelpers.getDistricts();
List<Indicator> Indicator;
List<District> District;
using (var package = new ExcelPackage(existingFile))
{
// Get the work book in the file
var workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
// Get the first worksheet
var currentWorksheet = workBook.Worksheets.First();
// read some data
object col1Header = currentWorksheet.Cells[2, 2].Value;
// var Indicator_Id=0;
ExcelWorksheet workSheet = package.Workbook.Worksheets[1];
var start = workSheet.Dimension.Start;
var end = workSheet.Dimension.End;
for (int row = start.Row; row <= end.Row; row++)
{ // Row by row...
if (row != 1)
{
string indicatorName = workSheet.Cells[row, 1].Text;
string subIndicatorName = workSheet.Cells[row, 2].Text;
string value = workSheet.Cells[row, 3].Text;
string districtName = workSheet.Cells[row, 4].Text;
Indicator = (Indicators.Where(x => (x.IndicatorName == indicatorName) && (x.SubIndicatorName == subIndicatorName))).ToList();
District = (Districts.Where(x => (x.District_Name == districtName))).ToList();
if (Indicator.Count > 0 && District.Count > 0)
{
OutreachImportViewModel vm = new OutreachImportViewModel();
if (value != String.Empty)
vm.Value = Convert.ToDecimal(value);
else
vm.Value = null;
vm.Dist_Id = District[0].Dist_Id;
vm.IndicatorID = Indicator[0].ID;
vm.ReportingDate = DateTime.UtcNow;
vm.DistrictName = districtName;
vm.IndicatorName = indicatorName;
vm.SubIndicatorName = subIndicatorName;
OutreachCache.Add(User.Identity.Name, vm);
}
else
{
//exception Indicator or District Spelling mistake maybe
continue;
}
}
}
}
}
}
dynamic viewModel = new ExpandoObject();
viewModel.OutReachData = OutreachCache.GetAll();
viewModel.PartnerOrganizations = DbHelpers.getPartnerOrganizations();
return View(viewModel);
}