public ActionResult Create(CreateTallySheetInput input, HttpPostedFileBase file)
{
if (ModelState.IsValid)
{
if (file != null && file.ContentLength > 0)
{
//ExcelDataReader works on binary excel file
Stream stream = file.InputStream;
//We need to written the Interface.
IExcelDataReader reader = null;
if (file.FileName.EndsWith(".xls"))
{
//reads the excel file with .xls extension
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (file.FileName.EndsWith(".xlsx"))
{
//reads excel file with .xlsx extension
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
//Shows error if uploaded file is not Excel file
ModelState.AddModelError("File", "This file format is not supported");
var specieCategories = _specieCategoryAppService.GetSpecieCategories().Select(c => new SelectListItem {
Value = c.Id.ToString(), Text = c.Name
});
ViewBag.SpecieCategoryId = specieCategories;
return(View(input));
}
//treats the first row of excel file as Coluymn Names
reader.IsFirstRowAsColumnNames = true;
//Adding reader data to DataSet()
DataSet result = reader.AsDataSet();
reader.Close();
//Sending result data to database
_tallySheetAppService.UploadTallySheet(input, result.Tables[0]);
//return View();
}
}
else
{
ModelState.AddModelError("File", "Please upload your file");
}
return(RedirectToAction("Create", new { id = input.PlotId }));
}
public void UploadTallySheet(CreateTallySheetInput input, DataTable table)
{
//Iterate data row from excel tally sheet
var category = _specieCategoryRepository.FirstOrDefault(input.SpecieCategoryId);
var plot = _plotRepository.FirstOrDefault(input.PlotId);
foreach (DataRow row in table.Rows)
{
if (row["DBH"] != DBNull.Value)
{
double tarifValue;
int dbh = Convert.ToInt32(row["DBH"].ToString());
var gnAmount = _gnRepository.FirstOrDefault(i => i.Dbh == dbh); //GN amount for each DBH
var tarif = _tariffRepository.FirstOrDefault(i => i.DBH == dbh); // tariff value for each DBH
// determine row value of each dbh from the selected compartment tarifff number
tarifValue = 0;
switch (input.TariffNumber)
{
case 40:
tarifValue = tarif.T40;
break;
case 41:
tarifValue = tarif.T41;
break;
case 42:
tarifValue = tarif.T42;
break;
case 43:
tarifValue = tarif.T43;
break;
case 44:
tarifValue = tarif.T44;
break;
case 45:
tarifValue = tarif.T45;
break;
case 46:
tarifValue = tarif.T46;
break;
case 47:
tarifValue = tarif.T47;
break;
case 48:
tarifValue = tarif.T48;
break;
case 49:
tarifValue = tarif.T49;
break;
case 50:
tarifValue = tarif.T50;
break;
case 51:
tarifValue = tarif.T51;
break;
case 52:
tarifValue = tarif.T52;
break;
case 53:
tarifValue = tarif.T53;
break;
case 54:
tarifValue = tarif.T54;
break;
case 55:
tarifValue = tarif.T55;
break;
case 56:
tarifValue = tarif.T56;
break;
case 57:
tarifValue = tarif.T57;
break;
case 58:
tarifValue = tarif.T58;
break;
case 59:
tarifValue = tarif.T59;
break;
case 60:
tarifValue = tarif.T60;
break;
case 61:
tarifValue = tarif.T61;
break;
case 62:
tarifValue = tarif.T62;
break;
case 63:
tarifValue = tarif.T63;
break;
case 64:
tarifValue = tarif.T64;
break;
case 65:
tarifValue = tarif.T65;
break;
case 66:
tarifValue = tarif.T66;
break;
case 67:
tarifValue = tarif.T67;
break;
case 68:
tarifValue = tarif.T68;
break;
case 69:
tarifValue = tarif.T69;
break;
case 70:
tarifValue = tarif.T70;
break;
case 71:
tarifValue = tarif.T71;
break;
case 72:
tarifValue = tarif.T72;
break;
}
//Distribution calculations
double volume = (tarifValue * Convert.ToInt32(row["NO_OF_TREES"].ToString()));
double lmda = (volume * category.Amount);
double loyality1 = (volume * gnAmount.Amount);
double tff = (0.03 * loyality1);
double loyality = (0.97 * loyality1);
double vat = (0.18 * loyality);
double cess = (0.05 * loyality);
double tp = (volume * 7500) / 12.234;
double total = loyality + lmda + vat + cess + tff + tp;
if (plot != null)
{
var sheet = new TallySheet
{
DBH = Convert.ToInt32(row["DBH"].ToString()),
PlotId = input.PlotId,
SpecieCategoryId = input.SpecieCategoryId,
TreesNumber = Convert.ToInt32(row["NO_OF_TREES"].ToString()),
GnAmount = gnAmount.Amount,
Volume = volume,
Loyality = loyality,
LMDA = lmda,
VAT = vat,
CESS = cess,
TFF = tff,
TP = tp,
TOTAL = total
};
_tallySheetRepository.Insert(sheet);
}
}
}
}
public ActionResult Talling(int CompartmentId, int SpecieCategoryId, int TariffNumber, HttpPostedFileBase file)
{
try
{
if (file != null && file.ContentLength > 0)
{
//ExcelDataReader works on binary excel file
Stream stream = file.InputStream;
var reader = string.Equals(Path.GetExtension(file.FileName), ".xls") ? ExcelReaderFactory.CreateBinaryReader(stream)
: ExcelReaderFactory.CreateOpenXmlReader(stream);
int plotId;
//Loop through data sheet
do
{
while (reader.Read())
{
CreatePlotInput input = new CreatePlotInput();
CreateTallySheetInput tallyInput = new CreateTallySheetInput();
input.CompartmentId = CompartmentId;
input.Name = reader.Name;
string name = reader.Name;
plotId = _plotAppService.CreatePlot(input);
tallyInput.PlotId = plotId;
tallyInput.SpecieCategoryId = SpecieCategoryId;
tallyInput.TariffNumber = TariffNumber;
//treats the first row of excel file as Coluymn Names
reader.IsFirstRowAsColumnNames = true;
//Adding reader data to DataSet()
DataSet result1 = reader.AsDataSet();
//Sending result data to database
_tallySheetAppService.UploadTallySheet(tallyInput, result1.Tables[reader.Name]);
reader.Close();
}
} while (reader.NextResult());
return(RedirectToAction("Index"));
}
else
{
ModelState.AddModelError("File", "Please upload your file");
return(RedirectToAction("Index"));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
