private string CreateXlsMasterCostOb(CostObModel model) { //get data var costOb = new List <CostObItem>(); costOb = GetCostOb(model.SearchView); var listData = Mapper.Map <List <CostObItem> >(costOb); var slDocument = new SLDocument(); //title slDocument.SetCellValue(1, 1, "Master Cost OB"); slDocument.MergeWorksheetCells(1, 1, 1, 11); //create style SLStyle valueStyle = slDocument.CreateStyle(); valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center); valueStyle.Font.Bold = true; valueStyle.Font.FontSize = 18; slDocument.SetCellStyle(1, 1, valueStyle); //create header slDocument = CreateHeaderExcelMasterCostOb(slDocument); //create data slDocument = CreateDataExcelMasterCostOb(slDocument, listData); var fileName = "Master_Data_CostOb" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx"; var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName); slDocument.SaveAs(path); return(path); }
public ActionResult Index() { var model = new CostObModel(); model.SearchView.Year = DateTime.Now.Year; var VehicleTypeList = _settingBLL.GetSetting().Where(x => x.IsActive && x.SettingGroup == EnumHelper.GetDescription(Enums.SettingGroup.VehicleType)).Select(x => new { x.SettingName }).Distinct().ToList(); var FunctionList = _functionGroupBll.GetGroupCenter().Where(x => x.IsActive).Select(x => new { x.FunctionName }).Distinct().ToList(); var LocationMappingList = _locationMappingBLL.GetLocationMapping().Where(x => x.IsActive).Select(x => new { x.Region }).Distinct().ToList(); model.SearchView.VehicleTypeList = new SelectList(VehicleTypeList, "SettingName", "SettingName"); model.SearchView.FunctionList = new SelectList(FunctionList, "FunctionName", "FunctionName"); model.SearchView.RegionalList = new SelectList(LocationMappingList, "Region", "Region"); var filter = new CostObParamInput(); filter.Year = DateTime.Now.Year; var data = _costObBLL.GetByFilter(filter); model.Details = Mapper.Map <List <CostObItem> >(data); model.MainMenu = _mainMenu; model.CurrentLogin = CurrentUser; model.CurrentPageAccess = CurrentPageAccess; foreach (CostObItem item in model.Details) { item.MonthS = this.SetMonthToString(item.Month == null ? 0:item.Month.Value); } return(View(model)); }
public ActionResult Upload() { var model = new CostObModel(); model.MainMenu = _mainMenu; model.CurrentLogin = CurrentUser; return(View(model)); }
public PartialViewResult ListCostOb(CostObModel model) { model.Details = new List <CostObItem>(); model.Details = GetCostOb(model.SearchView); model.CurrentLogin = CurrentUser; model.CurrentPageAccess = CurrentPageAccess; foreach (CostObItem item in model.Details) { item.MonthS = this.SetMonthToString(item.Month == null ? 0 : item.Month.Value); } return(PartialView("_ListCostOb", model)); }
public void ExportMasterCostOb(CostObModel model) { string pathFile = ""; pathFile = CreateXlsMasterCostOb(model); var newFile = new FileInfo(pathFile); var fileName = Path.GetFileName(pathFile); string attachment = string.Format("attachment; filename={0}", fileName); Response.Clear(); Response.AddHeader("content-disposition", attachment); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.WriteFile(newFile.FullName); Response.Flush(); newFile.Delete(); Response.End(); }
public ActionResult Upload(CostObModel Model) { var data = (new ExcelReader()).ReadExcel(Model.upload); var model = new List <CostObItem>(); if (data != null) { foreach (var dataRow in data.DataRows) { if (dataRow.Count <= 4) { continue; } if (dataRow[5] == "") { continue; } var item = new CostObItem(); item.ErrorMessage = ""; try { for (int i = 5; i <= data.Headers.Count() - 1; i++) { item = new CostObItem(); item.ErrorMessage = ""; item.CostCenter = dataRow[0]; if (item.CostCenter == "") { item.ErrorMessage = "Cost Center can't be empty"; } item.FunctionName = dataRow[1]; if (item.FunctionName == "") { item.ErrorMessage = "Function Can't be empty"; } item.Regional = dataRow[2]; item.VehicleType = dataRow[3]; if (item.VehicleType == "") { item.ErrorMessage = "Vehicle Type Can't be empty"; } if (data.Headers[i] == "" || data.Headers[i] == null) { continue; } var Header = data.Headers[i].Split('_'); if (Header.Count() > 1) { var Type = Header[0]; item.Type = ""; item.ObCost = null; item.Qty = null; var Time = Header[1].Split('-'); if (Time.Count() > 1) { if (Type.ToUpper() == "QTY") { item.Type = "QTY"; try { item.Qty = Convert.ToInt32(dataRow[i]); } catch (Exception) { item.ErrorMessage = "Qty must be number"; } item.Month = Convert.ToInt32(Time[0]); item.Year = Convert.ToInt32(Time[1]); var exist = _costObBLL.GetCostOb().Where(x => (x.FunctionName == null ? "" : x.FunctionName.ToUpper()) == (item.FunctionName == null ? "" : item.FunctionName.ToUpper()) && (x.CostCenter == null ? "" : x.CostCenter.ToUpper()) == (item.CostCenter == null ? "" : item.CostCenter.ToUpper()) && (x.Regional == null ? "" : x.Regional.ToUpper()) == (item.Regional == null ? "" : item.Regional.ToUpper()) && (x.VehicleType == null ? "" : x.VehicleType.ToUpper()) == (item.VehicleType == null ? "" : item.VehicleType.ToUpper()) && (x.Type == null ? "" : x.Type.ToUpper()) == (item.Type == null ? "" : item.Type.ToUpper()) && x.Month == item.Month && x.Year == item.Year && x.IsActive).FirstOrDefault(); if (exist != null) { exist.IsActive = false; exist.ModifiedBy = "SYSTEM"; exist.ModifiedDate = DateTime.Now; _costObBLL.Save(exist); } var dto = Mapper.Map <CostObDto>(item); dto.CreatedBy = CurrentUser.USER_ID; dto.CreatedDate = DateTime.Now; dto.IsActive = true; _costObBLL.Save(dto); } else { item.Type = Type; try { item.ObCost = Convert.ToDecimal(dataRow[i]); } catch (Exception) { item.ErrorMessage = "Cost OB must be number"; } item.Month = Convert.ToInt32(Time[0]); item.Year = Convert.ToInt32(Time[1]); var exist = _costObBLL.GetCostOb().Where(x => (x.FunctionName == null ? "" : x.FunctionName.ToUpper()) == (item.FunctionName == null ? "" : item.FunctionName.ToUpper()) && (x.CostCenter == null ? "" : x.CostCenter.ToUpper()) == (item.CostCenter == null ? "" : item.CostCenter.ToUpper()) && (x.Regional == null ? "" : x.Regional.ToUpper()) == (item.Regional == null ? "" : item.Regional.ToUpper()) && (x.VehicleType == null ? "" : x.VehicleType.ToUpper()) == (item.VehicleType == null ? "" : item.VehicleType.ToUpper()) && (x.Type == null ? "" : x.Type.ToUpper()) == (item.Type == null ? "" : item.Type.ToUpper()) && x.Month == item.Month && x.Year == item.Year && x.IsActive).FirstOrDefault(); if (exist != null) { exist.IsActive = false; exist.ModifiedBy = "SYSTEM"; exist.ModifiedDate = DateTime.Now; _costObBLL.Save(exist); } var dto = Mapper.Map <CostObDto>(item); dto.CreatedBy = CurrentUser.USER_ID; dto.CreatedDate = DateTime.Now; dto.IsActive = true; _costObBLL.Save(dto); } } } } } catch (Exception ex) { item.ErrorMessage = ex.Message; } } try { _costObBLL.SaveChanges(); } catch (Exception exp) { Model.ErrorMessage = exp.Message; Model.MainMenu = _mainMenu; Model.CurrentLogin = CurrentUser; return(View(Model)); } } return(RedirectToAction("Index", "MstCostOb")); }