public ActionResult SearchWholeSaler()
{
List <WholeSaler_Inventory> lst = wholeSalerRepository.List.ToList();
DataTable Dt = ExcelPackageExtensions.ToDataTable(lst);
return(View(Dt));
}
public ActionResult ImportExcel(HttpPostedFileBase file)
{
try
{
if (Path.GetExtension(file.FileName) == ".xlsx" || Path.GetExtension(file.FileName) == ".xls")
{
ExcelPackage package = new ExcelPackage(file.InputStream);
DataTable Dt = ExcelPackageExtensions.ToDataTable(package);
var employee = new Employee();
for (int i = 0; i < Dt.Rows.Count; i++)
{
employee.EmployeeName = Dt.Rows[i][1].ToString();
employee.Position = Dt.Rows[i][2].ToString();
var x = Dt.Rows[i][3].ToString();
employee.DepartmentId = Int32.Parse(x);
db.Employees.Add(employee);
db.SaveChanges();
}
}
}
catch { }
return(RedirectToAction("Index"));
}//end import excel file to data base
public ActionResult GetAllChildren(FormCollection form)
{
SalesForcVIewModel obj = new SalesForcVIewModel();
List <string> selectedList = new List <string>();
obj.StateModel = new List <Parent>();
obj.StateModel = GetAllParrent();
int level = 3;
///
var value = Request.Form["SalesForce.StateModel"] ?? Request.Form["StateModel"];
selectedList.Add(form["dd1"]);
selectedList.Add(form["dd2"]);
selectedList.Add(form["dd3"]);
if (!string.IsNullOrEmpty(value))
{
level = 2;
}
var value1 = Request.Form["ddlcity"];
if (!string.IsNullOrEmpty(Request.Form["dd2"]))
{
level = 3;
}
string strDDLValue = value.ToString();
//strDDLValue = "MB-RSM-BTB";
var lstEmp = db.sp_GetAllChildrenForParent(strDDLValue, level).Select(i => new MT_SellOut
{
Day = i.Day,
SalesOrg = i.SalesOrg,
CustomerCode = i.CustomerCode,
SalesRouteCode = i.SalesRouteCode,
ID = i.ID,
Name = i.Name,
Store = i.Store,
Target = i.Target,
Perform = i.Perform,
Rate = i.Rate,
LineID = i.LineID,
SalesForceLevel = i.SalesForceLevel,
ParentCode = i.ParentCode,
SalesForceCode = i.SalesForceCode
}
).OrderBy(x => x.LineID)
.ToList();;
DataTable Dt = ExcelPackageExtensions.ToDataTable(lstEmp);
MultiModel model = new MultiModel
{
Dt = Dt,
SalesForce = obj,
SelectedList = selectedList
};
return(View(model));
}
public ActionResult ReadExcel(HttpPostedFileBase upload)
{
if (Path.GetExtension(upload.FileName) == ".xlsx" || Path.GetExtension(upload.FileName) == ".xls")
{
ExcelPackage package = new ExcelPackage(upload.InputStream);
DataTable Dt = ExcelPackageExtensions.ToDataTable(package);
// Getting those error because we don't have any ReadExcel view . you can use this datatatable where you need it.
}
return(View());
}
public ActionResult SearchResultSellIn(DateTime bday)
{
//string date = bday.Date.ToShortDateString();
string date = String.Format("{0:dd/MM/yyyy}", bday);
var searchResults = sellInRepository.Search(p => p.Day == date);
List <MT_SellIn> tab1 = searchResults.Where(x => x.Tab == "1").ToList();
List <MT_SellIn> tab2 = searchResults.Where(x => x.Tab == "2").ToList();
DataTable Dt1 = ExcelPackageExtensions.ToDataTable(tab1);
DataTable Dt2 = ExcelPackageExtensions.ToDataTable(tab2);
DataTable[] TotalDT = { Dt1, Dt2 };
return(View(TotalDT));
}
public ActionResult ImportExcel(HttpPostedFileBase file)//(out int count)//
{
//if (file != null)
//{
if (Path.GetExtension(file.FileName) == ".xlsx" || Path.GetExtension(file.FileName) == ".xls")
{
ExcelPackage package = new ExcelPackage(file.InputStream);
DataTable Dt = ExcelPackageExtensions.ToDataTable(package);
// DataTable dtExcel = new DataTable();
var employee = new Employee();
//employee.FirstName.Add("First Name", typeof(String));
//dtExcel.Columns.Add("Last Name", typeof(String));
//dtExcel.Columns.Add("Position", typeof(String));
//dtExcel.Columns.Add("Departmnet ID", typeof(Int32));
int d;
for (int i = 0; i < Dt.Rows.Count; i++)
{
employee.FirstName = Dt.Rows[i][1].ToString();
employee.LastName = Dt.Rows[i][2].ToString();
employee.Position = Dt.Rows[i][3].ToString();
var x = Dt.Rows[i][4].ToString();
employee.DepartmentID = Int32.Parse(x);
db.Employees.Add(employee);
db.SaveChanges();
}
return(RedirectToAction("Index"));
}
return(View());
}
public ActionResult ImportExcelFileToUserDataTable()
{
objResponse Response = new objResponse();
session = new SessionHelper();
List <Users> users = new List <Users>();
DataTable dt = new DataTable();
// string Result="";
try
{
string fname;
if (Request.Files.Count > 0)
{
HttpFileCollectionBase files = Request.Files;
for (int i = 0; i < files.Count; i++)
{
HttpPostedFileBase file = files[i];
// Checking for Internet Explorer
if (Request.Browser.Browser.ToUpper() == "IE" || Request.Browser.Browser.ToUpper() == "INTERNETEXPLORER")
{
string[] testfiles = file.FileName.Split(new char[] { '\\' });
fname = testfiles[testfiles.Length - 1];
}
else
{
fname = file.FileName;
}
if ((file != null) && (file.ContentLength != 0) && !string.IsNullOrEmpty(file.FileName))
{
string fileName = file.FileName;
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
}
var excel = new ExcelPackage(file.InputStream);
dt = ExcelPackageExtensions.ToDataTable(excel);
int count = 0;
foreach (DataRow dr in dt.Rows)
{
Users objUser = new Users();
objUser.Username = dr[0].ToString();
objUser.FName = dr[1].ToString();
objUser.LName = dr[2].ToString();
objUser.Mobile = dr[3].ToString();
objUser.Email = dr[4].ToString();
objUser.URole = dr[5].ToString();
objUser.Group = dr[6].ToString();
objUser.TimeZone = dr[7].ToString();
objUser.Agent_App_Link = dr[8].ToString();
objUser.Processer = dr[9].ToString();
objUser.Sales_No = dr[10].ToString();
objUser.Sales_Id = dr[11].ToString();
objUser.BirthDay = dr[12].ToString();
objUser.Password = BAL.Helper.Helper.GenerateRandomPassword();
objUser.CratedBy_ID = Convert.ToInt64(session.UserSession.UserId);
Response = objUserManager.AddUser(objUser);
if (Response.ErrorCode == 0)
{
if (Response.ErrorMessage != "User Already Exists")
{
count++;
}
}
else
{
return(Json("Fail", JsonRequestBehavior.AllowGet));
}
}
return(Json("Success," + count.ToString(), JsonRequestBehavior.AllowGet));
}
return(Json("fail", JsonRequestBehavior.AllowGet));
}
else
{
return(Json("fail", JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex)
{
BAL.Common.LogManager.LogError("ImportExcelFileToUserDataTable", 1, Convert.ToString(ex.Source), Convert.ToString(ex.Message), Convert.ToString(ex.StackTrace));
return(Json("fail", JsonRequestBehavior.AllowGet));
}
}
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));
}
public ActionResult ImportPerform(HttpPostedFileBase chooseFile)
{
if (chooseFile == null)
{
TempData["message"] = "Vui lòng chọn file";
return(RedirectToAction("ImportSellOut", "Home", ViewBag.message));
}
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("ImportSellOut", "Home", ViewBag.message));
}
ExcelPackage package = new ExcelPackage(chooseFile.InputStream);
DataTable Dt = ExcelPackageExtensions.ConvertToDataTable(package);
//if (Dt.Columns.Count > 8)
//{
// TempData["message"] = "Vui lòng chọn templete sell out để có thể import!";
// return RedirectToAction("ImportSellOut", "Home", ViewBag.message);
//}
List <MT_SellOut> lstTarget = Dt.DataTableToListBaseHeader <MT_SellOut>();
String salesOrg = lstTarget.ElementAt(0).SalesOrg;
if (salesOrg != "1100" && salesOrg != "1500")
{
TempData["message"] = "Vui lòng chọn templete out in để có thể import!";
return(RedirectToAction("ImportSellOut", "Home", ViewBag.message));
}
//var db = new DemoEntities1();
//var lstEmp = db.sp_Employee_GetAll();
//List<SellOutViewModel> empViewModel = lstEmp.Select(c => new SellOutViewModel
//{
// EmployeeCode = c.EmployeeCode,
// EmployeeName = c.EmployeeName,
// SalesForceCode = c.SalesForceCode,
// Parentcode = c.Parentcode,
// SalesForceName = c.SalesForceName,
// SalesForceLevel = c.SalesForceLevel
//}).ToList();
//DataTable Dt11 = ExcelPackageExtensions.ToDataTable(empViewModel);
//--------------------------------------------------------
foreach (var item in lstTarget)
{
sellOutRepository.Update(item, x => x.Perform);
}
lstTarget = sellOutRepository.List.ToList();
foreach (var item in lstTarget)
{
item.Rate = Math.Round((Double.Parse(item.Perform) / Double.Parse(item.Target) * 100)).ToString();
sellOutRepository.Update(item, x => x.Rate);
}
var lstSalesForce = salesForceRepository.List.ToList();
string day = DateTime.Now.ToString("dd/MM/yyyy");
var result = lstSalesForce.Join(lstTarget,
dep => dep.EmployeeCode,
e => e.ID,
(e, dep) => new { e, dep })
.Select(i => new MT_SellOut
{
Day = day,
SalesOrg = i.dep.SalesOrg,
CustomerCode = i.dep.CustomerCode,
SalesRouteCode = i.dep.SalesRouteCode,
ID = i.dep.ID,
Name = i.dep.Name,
Store = i.dep.Store,
Target = i.dep.Target,
Perform = i.dep.Perform,
Rate = i.dep.Rate,
LineID = i.dep.LineID,
CompanyCode = DMSEnum.CompanyCode,
SalesForceLevel = i.e.SalesForceLevel,
ParentCode = i.e.ParentCode,
SalesForceCode = i.e.SalesForceCode
}
).OrderBy(x => x.LineID)
.ToList();
foreach (var item in result)
{
sellOutRepository.InsertOrUpdate(item);
}
DataTable Dt1 = ExcelPackageExtensions.ToDataTable(result);
return(View(Dt1));
}
public ActionResult ImportExcelFileToLeadDataTable()
{
objResponse Response = new objResponse();
session = new SessionHelper();
List <Project.Entity.Leads> leads = new List <Entity.Leads>();
BAL.Leads.LeadsManager objLeadsManager = new LeadsManager();
DataTable dt = new DataTable();
// string Result="";
try
{
string fname;
if (Request.Files.Count > 0)
{
HttpFileCollectionBase files = Request.Files;
for (int i = 0; i < files.Count; i++)
{
HttpPostedFileBase file = files[i];
// Checking for Internet Explorer
if (Request.Browser.Browser.ToUpper() == "IE" || Request.Browser.Browser.ToUpper() == "INTERNETEXPLORER")
{
string[] testfiles = file.FileName.Split(new char[] { '\\' });
fname = testfiles[testfiles.Length - 1];
}
else
{
fname = file.FileName;
}
if ((file != null) && (file.ContentLength != 0) && !string.IsNullOrEmpty(file.FileName))
{
string fileName = file.FileName;
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
}
var excel = new ExcelPackage(file.InputStream);
dt = ExcelPackageExtensions.ToDataTable(excel);
int count = 0;
foreach (DataRow dr in dt.Rows)
{
Project.Entity.Leads objLead = new Entity.Leads();
objLead.Name = dr[0].ToString();
objLead.CompanyName = dr[1].ToString();
objLead.Email = dr[2].ToString();
objLead.Alternate_Email = dr[3].ToString();
objLead.ContactNo = dr[4].ToString();
objLead.SkypeNo = dr[5].ToString();
objLead.AddressLine1 = dr[6].ToString();
objLead.AddressLine2 = dr[7].ToString();
objLead.City = dr[8].ToString();
objLead.State = dr[9].ToString();
objLead.Country = dr[10].ToString();
objLead.ZipCode = dr[11].ToString();
objLead.Source = dr[12].ToString();
Response = objLeadsManager.ImportLead(objLead, session.UserSession.Username, Convert.ToInt64(session.UserSession.PIN));
if (Response.ErrorCode == 0)
{
if (Response.ErrorMessage != "Lead with same Email Already Exists")
{
count++;
}
}
else
{
return(Json("Fail", JsonRequestBehavior.AllowGet));
}
}
return(Json("Success," + count.ToString(), JsonRequestBehavior.AllowGet));
}
return(Json("fail", JsonRequestBehavior.AllowGet));
}
else
{
return(Json("fail", JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex)
{
BAL.Common.LogManager.LogError("ImportExcelFileToLeadDataTable", 1, Convert.ToString(ex.Source), Convert.ToString(ex.Message), Convert.ToString(ex.StackTrace));
return(Json("fail", JsonRequestBehavior.AllowGet));
}
}