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"));
}