public ActionResult ReadExcel(HttpPostedFileBase chooseFile)
{
if (chooseFile == null)
{
TempData["message"] = "Vui lòng chọn file";
return(RedirectToAction("ImportReport"));
}
if (Path.GetExtension(chooseFile.FileName) != ".xlsx" && Path.GetExtension(chooseFile.FileName) != ".xls")
{
TempData["message"] = "Định dạng file excel không hợp lệ";
return(RedirectToAction("ImportReport"));
}
ExcelPackage package = new ExcelPackage(chooseFile.InputStream);
DataTable[] Dt = ExcelPackageExtensions.ToDataTable(package);
//List<Account> ls = sellInRepository.List.ToList();
//List<object> lst = Dt.AsEnumerable().ToList<object>();
//List<Person> employeeList = Dt.DataTableToList<Person>();
//if (Dt[0].Columns.Count != 8)
//{
// TempData["message"] = "Vui lòng chọn templete sell in để có thể import!";
// return RedirectToAction("ImportReport", "Home", ViewBag.message);
//}
List <MT_SellIn> tab1 = Dt[0].DataTableToList <MT_SellIn>();
List <MT_SellIn> tab2 = Dt[1].DataTableToList <MT_SellIn>();
String date = tab1.ElementAt(tab1.Count - 1).Day; // 07/22/2013
int numberOfWork = 0;
int month = 0;
int year = 0;
if (date.Length != 9 && date.Length != 10)
{
TempData["message"] = "Vui lòng chọn templete sell in để có thể import!";
return(RedirectToAction("ImportReport"));
}
if (date.Length == 9) //7/22/2013
{
month = int.Parse(date.Substring(0, 1));
year = int.Parse(date.Substring(5, 4));
}
else //07/22/2013
{
month = int.Parse(date.Substring(0, 2));
year = int.Parse(date.Substring(6, 4));
}
numberOfWork = ExcelPackageExtensions.CountWorkingDay(year, month);
for (int i = 1; i < tab1.Count; i++)
{
var day = tab1.ElementAt(i).Day.ToString();
if (day.Length == 9)
{
day = "0" + day;
tab1.ElementAt(i).Day = DateTime.ParseExact(day, "MM/dd/yyyy", CultureInfo.InvariantCulture)
.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
}
else if (day.Length == 10)
{
tab1.ElementAt(i).Day = DateTime.ParseExact(day, "MM/dd/yyyy", CultureInfo.InvariantCulture)
.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
}
if (tab1.ElementAt(i).TargetMonth.Trim() == "0" || tab1.ElementAt(i).TargetMonth.Trim() == "-")
{
tab1.ElementAt(i).TargetDate = "0";
}
else
{
var targetDate = (double.Parse(tab1.ElementAt(i).TargetMonth) / numberOfWork);
tab1.ElementAt(i).TargetDate = String.Format("{0:0.00}", targetDate);
}
if (tab1.ElementAt(i).Archive.Trim() == "0" || tab1.ElementAt(i).Archive.Trim() == "-" ||
tab1.ElementAt(i).Actual.Trim() == "0" || tab1.ElementAt(i).Actual.Trim() == "-")
{
tab1.ElementAt(i).Growth = "0";
}
else
{
var growth = (double.Parse(tab1.ElementAt(i).Actual) / double.Parse(tab1.ElementAt(i).Archive)) * 100;
tab1.ElementAt(i).Growth = String.Format("{0:0.00}", growth);
}
if (tab1.ElementAt(i).LastMonth.Trim() == "0" || tab1.ElementAt(i).LastMonth.Trim() == "-" ||
tab1.ElementAt(i).Actual.Trim() == "0" || tab1.ElementAt(i).Actual.Trim() == "-")
{
tab1.ElementAt(i).GrowthLastMonth = "0";
}
else
{
var growthLastMonth = (double.Parse(tab1.ElementAt(i).Actual) / double.Parse(tab1.ElementAt(i).LastMonth)) * 100;
tab1.ElementAt(i).GrowthLastMonth = String.Format("{0:0.00}", growthLastMonth);
}
if (tab1.ElementAt(i).TargetMonth.Trim() == "0" || tab1.ElementAt(i).TargetMonth.Trim() == "-" ||
tab1.ElementAt(i).Actual.Trim() == "0" || tab1.ElementAt(i).Actual.Trim() == "-")
{
tab1.ElementAt(i).PercentTarget = "0";
}
else
{
var percentTarget = (double.Parse(tab1.ElementAt(i).Actual) / double.Parse(tab1.ElementAt(i).TargetMonth)) * 100;
tab1.ElementAt(i).PercentTarget = String.Format("{0:0.00}", percentTarget);
}
if (tab1.ElementAt(i).TargetWeek.Trim() == "0" || tab1.ElementAt(i).TargetWeek.Trim() == "-" ||
tab1.ElementAt(i).ActualWeek.Trim() == "0" || tab1.ElementAt(i).ActualWeek.Trim() == "-")
{
tab1.ElementAt(i).PercentWeek = "0";
}
else
{
var percentWeek = (double.Parse(tab1.ElementAt(i).ActualWeek) / double.Parse(tab1.ElementAt(i).TargetWeek)) * 100;
tab1.ElementAt(i).PercentWeek = String.Format("{0:0.00}", percentWeek);
}
tab1.ElementAt(i).LastUpdated = DateTime.Now.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
tab1.ElementAt(i).Tab = "1";
tab1.ElementAt(i).CompanyCode = DMSEnum.CompanyCode;
tab1.ElementAt(i).SalesOrg = DMSEnum.MTSalesOrg;
}
for (int i = 1; i < tab2.Count; i++)
{
var day = tab2.ElementAt(i).Day.ToString();
if (day.Length == 9)
{
day = "0" + day;
}
tab2.ElementAt(i).Day = DateTime.ParseExact(day, "MM/dd/yyyy", CultureInfo.InvariantCulture)
.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
if (tab2.ElementAt(i).TargetMonth.Trim() == "0" || tab2.ElementAt(i).TargetMonth.Trim() == "-")
{
tab2.ElementAt(i).TargetDate = "0";
}
else
{
var targetDate = (double.Parse(tab2.ElementAt(i).TargetMonth) / numberOfWork);
tab2.ElementAt(i).TargetDate = String.Format("{0:0.00}", targetDate);
}
if (tab2.ElementAt(i).Archive.Trim() == "0" || tab2.ElementAt(i).Archive.Trim() == "-" ||
tab2.ElementAt(i).Actual.Trim() == "0" || tab2.ElementAt(i).Actual.Trim() == "-")
{
tab2.ElementAt(i).Growth = "0";
}
else
{
var growth = (double.Parse(tab2.ElementAt(i).Actual) / double.Parse(tab2.ElementAt(i).Archive)) * 100;
tab2.ElementAt(i).Growth = String.Format("{0:0.00}", growth);
}
if (tab2.ElementAt(i).LastMonth.Trim() == "0" || tab2.ElementAt(i).LastMonth.Trim() == "-" ||
tab2.ElementAt(i).Actual.Trim() == "0" || tab2.ElementAt(i).Actual.Trim() == "-")
{
tab2.ElementAt(i).GrowthLastMonth = "0";
}
else
{
var growthLastMonth = (double.Parse(tab2.ElementAt(i).Actual) / double.Parse(tab2.ElementAt(i).LastMonth)) * 100;
tab2.ElementAt(i).GrowthLastMonth = String.Format("{0:0.00}", growthLastMonth);
}
if (tab2.ElementAt(i).TargetMonth.Trim() == "0" || tab2.ElementAt(i).TargetMonth.Trim() == "-" ||
tab2.ElementAt(i).Actual.Trim() == "0" || tab2.ElementAt(i).Actual.Trim() == "-")
{
tab2.ElementAt(i).PercentTarget = "0";
}
else
{
var percentTarget = (double.Parse(tab2.ElementAt(i).Actual) / double.Parse(tab2.ElementAt(i).TargetMonth)) * 100;
tab2.ElementAt(i).PercentTarget = String.Format("{0:0.00}", percentTarget);
}
if (tab2.ElementAt(i).TargetWeek.Trim() == "0" || tab2.ElementAt(i).TargetWeek.Trim() == "-" ||
tab2.ElementAt(i).ActualWeek.Trim() == "0" || tab2.ElementAt(i).ActualWeek.Trim() == "-")
{
tab2.ElementAt(i).PercentWeek = "0";
}
else
{
var percentWeek = (double.Parse(tab2.ElementAt(i).ActualWeek) / double.Parse(tab2.ElementAt(i).TargetWeek)) * 100;
tab2.ElementAt(i).PercentWeek = String.Format("{0:0.00}", percentWeek);
}
tab2.ElementAt(i).LastUpdated = DateTime.Now.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
tab2.ElementAt(i).Tab = "2";
tab2.ElementAt(i).CompanyCode = DMSEnum.CompanyCode;
tab2.ElementAt(i).SalesOrg = DMSEnum.MTSalesOrg;
}
//sellInRepository.BatchInsert(tab1);
DataTable Dt1 = ExcelPackageExtensions.ToDataTable(tab1);
DataTable Dt2 = ExcelPackageExtensions.ToDataTable(tab2);
DataRow recRow1 = Dt1.Rows[0];
recRow1[0] = string.Empty;
recRow1.Delete();
Dt1.AcceptChanges();
DataRow recRow2 = Dt2.Rows[0];
recRow2[0] = string.Empty;
recRow2.Delete();
Dt2.AcceptChanges();
DataTable[] TotalDT = { Dt1, Dt2 };
//ModelDt[1].DataTableToList<SellinFirstTab>();State.AddModelError("Error", "Ex: This login failed " + employeeList.ElementAt(0).Fullname);
//ModelState.AddModelError("Error", "Ex: This login failed 1");
var searchResults = sellInRepository.Search(p => p.Growth == "0");
//sellInRepository.BatchInsert(tab1);
//sellInRepository.BatchInsert(tab2);
List <MT_SellIn> ls = sellInRepository.List.ToList();
foreach (var item in tab1)
{
sellInRepository.InsertOrUpdate(item);
}
foreach (var item in tab2)
{
sellInRepository.InsertOrUpdate(item);
}
if (TotalDT != null)
{
TempData["success"] = DMSEnum.Success;
}
return(View(TotalDT));
}
