public ActionResult UploadDistrictDataSheet(HttpPostedFileBase rspFile)
{
// store the file inside ~/App_Data/uploads folder
string fileName = "book4.xlsx";
// 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<PartnerOrganization> Partners = DbHelpers.getPartnerOrganizations();
List<Indicator> Indicator;
List<District> District;
List<PartnerOrganization> PartnerOrganization;
EmptyUserCache();
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;
List<string> unknownDistricts = new List<string>();
decimal ruralPeriUrban, rspUcs, totalhhs, organizedhhs, cosformed, districtOffice, fieldUnits;
string rspName;
RSPOutreach o = new RSPOutreach();
o.CreatedBy = User.Identity.Name;
o.DateCreated = DateTime.Today;
o.DateModified = DateTime.Today;
o.ModifiedBy = User.Identity.Name;
o.ReportingDate = new DateTime(2014, 09, 15); ;
o.IsCumulative = false ;
for (int row = 5; row <= end.Row; row++)
{ // Row by row...
string districtName = workSheet.Cells[row, 2].Text;
if (districtName != "" && districtName != "TOTAL" && districtName != "Sub Total" && districtName != "Grand Total" && districtName != "G. Total")
{
districtName = districtName.Replace("(overlapping)", "");
districtName = districtName.Replace("(Overlapping)", "");
districtName = districtName.Replace("*", "");
District = (Districts.Where(x => (x.AlternateName == districtName.ToUpper().Trim()) || (x.District_Name == districtName.ToUpper().Trim()))).ToList();
rspName = workSheet.Cells[row, 18].Text;
PartnerOrganization = (Partners.Where(x => (x.Abbr == rspName.ToUpper()))).ToList();
if (District.Count > 0)
{
Decimal.TryParse(workSheet.Cells[row, 3].Text, out ruralPeriUrban);
Decimal.TryParse(workSheet.Cells[row, 5].Text, out rspUcs);
Decimal.TryParse(workSheet.Cells[row, 8].Text, out totalhhs);
Decimal.TryParse(workSheet.Cells[row, 10].Text, out organizedhhs);
Decimal.TryParse(workSheet.Cells[row, 14].Text, out cosformed);
// districtOffice = workSheet.Cells[row, 14].Text == "Yes" ? 1 : 0;
// Decimal.TryParse(workSheet.Cells[row, 14].Text, out fieldUnits);
o.Dist_Id = District[0].Dist_Id;
o.PartnerOrganizationID = PartnerOrganization.Count() > 0 ? PartnerOrganization[0].ID : (int?)null;
o.IndicatorID = 1;
o.Value = Convert.ToDecimal(ruralPeriUrban);
db.RSPOutreachs.Add(o);
db.SaveChanges();
o.IndicatorID = 2;
o.Value = Convert.ToDecimal(rspUcs);
db.RSPOutreachs.Add(o);
db.SaveChanges();
o.IndicatorID = 4;
o.Value = Convert.ToDecimal(totalhhs);
db.RSPOutreachs.Add(o);
db.SaveChanges();
o.IndicatorID = 5;
o.Value = Convert.ToDecimal(organizedhhs);
db.RSPOutreachs.Add(o);
db.SaveChanges();
o.IndicatorID = 39;
o.Value = Convert.ToDecimal(cosformed);
db.RSPOutreachs.Add(o);
db.SaveChanges();
//o.IndicatorID = 40;
//o.Value = Convert.ToDecimal(districtOffice);
//db.RSPOutreachs.Add(o);
//db.SaveChanges();
//o.IndicatorID = 41;
//o.Value = Convert.ToDecimal(fieldUnits);
//db.RSPOutreachs.Add(o);
//db.SaveChanges();
}
else
{
unknownDistricts.Add(districtName);
continue;
}
}
}
}
}
}
dynamic viewModel = new ExpandoObject();
viewModel.OutReachData = OutreachCache.GetAll();
viewModel.PartnerOrganizations = DbHelpers.getPartnerOrganizations();
return View(viewModel);
}
public ActionResult Index(HttpPostedFileBase rspFile)
{
// store the file inside ~/App_Data/uploads folder
string fileName = Path.GetFileName(rspFile.FileName);
var path = Path.Combine(Server.MapPath("~/App_Data/uploads"), fileName);
rspFile.SaveAs(path);
//end store file
//reading file
var existingFile = new FileInfo(path);
// Open and read the XlSX file.
using (var package = new ExcelPackage(existingFile))
{
ApplicationDbContext db = new ApplicationDbContext();
RSPOutreach o = new RSPOutreach();
o.CreatedBy = "Abdullah";
o.DateCreated = DateTime.Today;
o.DateModified = DateTime.Today;
o.ModifiedBy = "Abdullah";
o.UC_Id = 1;
o.ReportingDate = DateTime.Today;
// 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)
{
if (workSheet.Cells[row, 3].Text != "")
{
string indicatorName = workSheet.Cells[row, 1].Text;
string subIndicatorName = workSheet.Cells[row, 2].Text;
//Get Indicator Id
IQueryable<Indicator> Indicators = db.Indicators.Where(x => (x.IndicatorName == indicatorName) && (x.SubIndicatorName == subIndicatorName));
Indicator[] IndicatorsArray = Indicators.ToArray();
o.IndicatorID = IndicatorsArray[0].ID;
//Get District Id
string districtName = workSheet.Cells[row, 4].Text;
IQueryable<District> Districts = db.Districts.Where(x => x.District_Name == districtName);
District[] DistrictsArray = Districts.ToArray();
o.Dist_Id = DistrictsArray[0].Dist_Id;
//Get Value from work sheet
o.Value = Decimal.Parse(workSheet.Cells[row, 3].Text);
}
else
{
o.Value = 0;
}
db.RSPOutreachs.Add(o);
db.SaveChanges();
}
}
}
}
}
return View();
}
public ActionResult SubmitTransposedData(DateTime reportingDate)
{
ApplicationDbContext db = new ApplicationDbContext();
List<OutreachImportViewModel> rspCachedData = new List<OutreachImportViewModel>();
rspCachedData = OutreachCache.GetAll();
RSPOutreach o = new RSPOutreach();
o.CreatedBy = User.Identity.Name;
o.DateCreated = DateTime.Today;
o.DateModified = DateTime.Today;
o.ModifiedBy = User.Identity.Name;
o.ReportingDate = reportingDate;
o.UC_Id = 1;
foreach (var d in rspCachedData)
{
o.IndicatorID = d.IndicatorID;
o.Dist_Id = d.Dist_Id;
o.Value = d.Value;
o.PartnerOrganizationID = d.PartnerOrganizationId;
db.RSPOutreachs.Add(o);
db.SaveChanges();
}
OutreachCache.RemoveUserData(User.Identity.Name);
//var controller = Request.UrlReferrer.Segments.Skip(1).Take(1).SingleOrDefault() ?? "Home").Trim('/');
// Home is default controller
var action = (Request.UrlReferrer.Segments.Skip(2).Take(1).SingleOrDefault() ?? "Index").Trim('/');
return RedirectToAction(action);
}