/// <summary>
/// p.s mapping name maybe need to change
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <ItemCatalog> _ItemCatalog = this._itemCatalogService.GetAll().ToList();
List <Budget_Scrap> _Budget_ScrapList = new List <Budget_Scrap>();
IQueryable <Scrap> _ScrapList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <Scrap>(d => d.ScrapType, "ScrapType");
_Excel.AddMapping <Scrap>(d => d.PartNumber, "PartNumber");
_Excel.AddMapping <Scrap>(d => d.Reason, "Reason");
_Excel.AddMapping <Scrap>(d => d.Month, "Month");
_Excel.AddMapping <Scrap>(d => d.Quantity, "Quantity");
_Excel.AddMapping <Scrap>(d => d.PurchasePrice, "PurchasePrice");
_Excel.AddMapping <Scrap>(d => d.BookValue, "BookValue");
// every time get first sheet data
_ScrapList = from x in _Excel.Worksheet <Scrap>(0)
select x;
int _Start = 2;
foreach (Scrap c in _ScrapList)
{
if (_Start == 2 || _Start == 3 || c.PartNumber == null)
{
_Start++;
continue;
}
try
{
Budget_Scrap _Budget_Scrap = new Budget_Scrap();
_Budget_Scrap.ItemId_ScrapType = _ItemCatalog.Where(x => x.ClassName == "ScrapType" && x.Name.Trim() == c.ScrapType.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "ScrapType" && x.Name.Trim() == c.ScrapType.Trim()).First().ItemId : "";
_Budget_Scrap.PartNumber = c.PartNumber;
_Budget_Scrap.Reason = c.Reason;
_Budget_Scrap.Month = c.Month;
_Budget_Scrap.Quantity = this._commonFileService.GetExcelMinusNumber(c.Quantity);
_Budget_Scrap.PurchasePrice = this._commonFileService.GetExcelMinusNumber(c.PurchasePrice);
_Budget_Scrap.BookValue = this._commonFileService.GetExcelMinusNumber(c.BookValue);
_Budget_Scrap.Date = (DateTime.Now.Year + 1).ToString();
_Budget_Scrap.DepartmentId = base.DepartmentId;
_Budget_Scrap.Version = base.Version;
_Budget_Scrap.Factory = base.Factory;
_Budget_ScrapList.Add(_Budget_Scrap);
}
catch (Exception ex)
{
string _c = JsonConvert.SerializeObject(c);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _c);
}
_Start++;
}
_Budget.Scrap = _Budget_ScrapList;
return(_Budget);
}
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <ItemCatalog> _ItemCatalogList = new List <ItemCatalog>();
IQueryable <ItemCatalog> _CustomItemCatalogList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <ItemCatalog>(d => d.ClassName, "ClassName");
_Excel.AddMapping <ItemCatalog>(d => d.ItemId, "ItemId");
_Excel.AddMapping <ItemCatalog>(d => d.Name, "Name");
// every time get first sheet data
_CustomItemCatalogList = from x in _Excel.Worksheet <ItemCatalog>(0)
select x;
int _Start = 2;
foreach (ItemCatalog ic in _CustomItemCatalogList)
{
try
{
ItemCatalog _ItemCatalog = new ItemCatalog();
_ItemCatalog.ClassName = ic.ClassName;
_ItemCatalog.ItemId = ic.ItemId;
_ItemCatalog.Name = ic.Name;
_ItemCatalog.IsDel = false;
_ItemCatalogList.Add(_ItemCatalog);
}
catch (Exception ex)
{
string _Ic = JsonConvert.SerializeObject(ic);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _Ic);
}
_Start++;
}
_Budget.ItemCatalog = _ItemCatalogList;
return(_Budget);
}
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <ItemCatalog> _ItemCatalog = this._itemCatalogService.GetAll().ToList();
List <Budget_HeadCountHR> _Budget_HeadCountHRList = new List <Budget_HeadCountHR>();
IQueryable <CustomHeadcount> _CustomHeadcountList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <CustomHeadcount>(d => d.Account, "Employee Code");
_Excel.AddMapping <CustomHeadcount>(d => d.AltwName, "Employee Name");
_Excel.AddMapping <CustomHeadcount>(d => d.HR, "銷/管/研");
_Excel.AddMapping <CustomHeadcount>(d => d.DirectType, "DL/IDL");
_Excel.AddMapping <CustomHeadcount>(d => d.Func, "Func");
_Excel.AddMapping <CustomHeadcount>(d => d.Title, "Title");
// every time get first sheet data
_CustomHeadcountList = from x in _Excel.Worksheet <CustomHeadcount>(0)
select x;
int _Start = 2;
string _JobFunction = base.Factory == "LT" ? "JobFunction" : "JobFunctionKSZ";
string _Title = base.Factory == "LT" ? "Title" : "TitleKSZ";
foreach (CustomHeadcount ch in _CustomHeadcountList)
{
try
{
Budget_HeadCountHR _Budget_HeadCountHR = new Budget_HeadCountHR();
_Budget_HeadCountHR.Account = ch.Account;
_Budget_HeadCountHR.AltwName = ch.AltwName;
string _ItemId_HR = "";
if (ch.HR != null && ch.HR != "")
{
_ItemId_HR = _ItemCatalog.Where(x => x.ClassName == "HR" && x.Name.Trim() == ch.HR.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "HR" && x.Name == ch.HR).First().ItemId : "";
}
_Budget_HeadCountHR.ItemId_HR = _ItemId_HR;
_Budget_HeadCountHR.ItemId_DirectType = _ItemCatalog.Where(x => x.ClassName == "DirectType" && x.Name.Trim() == ch.DirectType.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "DirectType" && x.Name.Trim() == ch.DirectType.Trim()).First().ItemId : "";
_Budget_HeadCountHR.ItemId_JobFunction = _ItemCatalog.Where(x => x.ClassName == _JobFunction && x.Name.Trim() == ch.Func.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == _JobFunction && x.Name.Trim() == ch.Func.Trim()).First().ItemId : "";
_Budget_HeadCountHR.ItemId_Title = _ItemCatalog.Where(x => x.ClassName == _Title && x.Name.Trim() == ch.Title.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == _Title && x.Name.Trim() == ch.Title.Trim()).First().ItemId : "";
_Budget_HeadCountHR.IsDel = false;
_Budget_HeadCountHRList.Add(_Budget_HeadCountHR);
}
catch (Exception ex)
{
string _Ch = JsonConvert.SerializeObject(ch);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _Ch);
}
_Start++;
}
_Budget.HeadCountHR = _Budget_HeadCountHRList;
return(_Budget);
}
/// <summary>
/// p.s mapping name maybe need to change
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <Budget_DeptKPI> _Budget_DeptKPIList = new List <Budget_DeptKPI>();
IQueryable <Budget_DeptKPI> _DeptKPIList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <Budget_DeptKPI>(d => d.GoalName, " GoalName");
_Excel.AddMapping <Budget_DeptKPI>(d => d.PIC_Name, "PIC_Name");
_Excel.AddMapping <Budget_DeptKPI>(d => d.LastYear, "LastYear");
_Excel.AddMapping <Budget_DeptKPI>(d => d.YTD, "YTD");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Jan, "Jan");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Feb, "Feb");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Mar, "Mar");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Apr, "Apr");
_Excel.AddMapping <Budget_DeptKPI>(d => d.May, "May");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Jun, "Jun");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Jul, "Jul");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Aug, "Aug");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Sep, "Sep");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Oct, "Oct");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Nov, "Nov");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Dec, "Dec");
// every time get first sheet data
_DeptKPIList = from x in _Excel.Worksheet <Budget_DeptKPI>("Upload")
select x;
int _Start = 2;
foreach (Budget_DeptKPI c in _DeptKPIList)
{
if (_Start == 2 || c.GoalName == null)
{
_Start++;
continue;
}
try
{
Budget_DeptKPI _Budget_DeptKPI = new Budget_DeptKPI();
_Budget_DeptKPI.GoalName = c.GoalName;
_Budget_DeptKPI.PIC_Name = c.PIC_Name;
_Budget_DeptKPI.LastYear = c.LastYear;
_Budget_DeptKPI.YTD = c.YTD;
_Budget_DeptKPI.Jan = c.Jan;
_Budget_DeptKPI.Feb = c.Feb;
_Budget_DeptKPI.Mar = c.Mar;
_Budget_DeptKPI.Apr = c.Apr;
_Budget_DeptKPI.May = c.May;
_Budget_DeptKPI.Jun = c.Jun;
_Budget_DeptKPI.Jul = c.Jul;
_Budget_DeptKPI.Aug = c.Aug;
_Budget_DeptKPI.Sep = c.Sep;
_Budget_DeptKPI.Oct = c.Oct;
_Budget_DeptKPI.Nov = c.Nov;
_Budget_DeptKPI.Dec = c.Dec;
_Budget_DeptKPI.Date = (DateTime.Now.Year + 1).ToString();
_Budget_DeptKPI.DepartmentId = base.DepartmentId;
_Budget_DeptKPI.Version = base.Version;
_Budget_DeptKPI.Factory = base.Factory;
_Budget_DeptKPIList.Add(_Budget_DeptKPI);
}
catch (Exception ex)
{
string _C = JsonConvert.SerializeObject(c);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _C);
}
_Start++;
}
_Budget.DeptKPI = _Budget_DeptKPIList;
return(_Budget);
}
/// <summary>
/// if import part time hr excel need get factor departmentId column
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <ItemCatalog> _ItemCatalog = this._itemCatalogService.GetAll().ToList();
List <Budget_HeadCount> _Budget_HeadCountList = new List <Budget_HeadCount>();
IQueryable <Headcount> _HeadcountList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <Headcount>(d => d.AltwName, "AltwName");
_Excel.AddMapping <Headcount>(d => d.JobFunction, "JobFunction");
_Excel.AddMapping <Headcount>(d => d.Title, "Title");
_Excel.AddMapping <Headcount>(d => d.DirectType, "DirectType");
_Excel.AddMapping <Headcount>(d => d.Current, "Current");
_Excel.AddMapping <Headcount>(d => d.JulLast, "JulLast");
_Excel.AddMapping <Headcount>(d => d.AugLast, "AugLast");
_Excel.AddMapping <Headcount>(d => d.SepLast, "SepLast");
_Excel.AddMapping <Headcount>(d => d.OctLast, "OctLast");
_Excel.AddMapping <Headcount>(d => d.NovLast, "NovLast");
_Excel.AddMapping <Headcount>(d => d.DecLast, "DecLast");
_Excel.AddMapping <Headcount>(d => d.Jan, "Jan");
_Excel.AddMapping <Headcount>(d => d.Feb, "Feb");
_Excel.AddMapping <Headcount>(d => d.Mar, "Mar");
_Excel.AddMapping <Headcount>(d => d.Apr, "Apr");
_Excel.AddMapping <Headcount>(d => d.May, "May");
_Excel.AddMapping <Headcount>(d => d.Jun, "Jun");
_Excel.AddMapping <Headcount>(d => d.Jul, "Jul");
_Excel.AddMapping <Headcount>(d => d.Aug, "Aug");
_Excel.AddMapping <Headcount>(d => d.Sep, "Sep");
_Excel.AddMapping <Headcount>(d => d.Oct, "Oct");
_Excel.AddMapping <Headcount>(d => d.Nov, "Nov");
_Excel.AddMapping <Headcount>(d => d.Dec, "Dec");
_Excel.AddMapping <Headcount>(d => d.Reason, "Reason");
_Excel.AddMapping <Headcount>(d => d.Remark, "Remark");
if (base.PartTime)
{
_Excel.AddMapping <DeptExpense>(d => d.Factory, "Factory");
_Excel.AddMapping <DeptExpense>(d => d.DepartmentId, "DepartmentId");
}
// every time get first sheet data
_HeadcountList = from x in _Excel.Worksheet <Headcount>(0)
select x;
int _Start = 2;
foreach (Headcount h in _HeadcountList)
{
if (_Start == 2 || _Start == 3 || h.AltwName == null && !base.PartTime)
{
_Start++;
continue;
}
try
{
Budget_HeadCount _Budget_HeadCount = new Budget_HeadCount();
string _FactoryType = base.Factory == "LT" ? "L" : "K";
string _NewEmployee = _FactoryType + Guid.NewGuid().ToString("N").Substring(0, 18);
_Budget_HeadCount.Account = this._budget_HeadCountHRService.GetAll().Where(x => x.AltwName == h.AltwName).ToList().Count > 0 ? this._budget_HeadCountHRService.GetAll().Where(x => x.AltwName == h.AltwName).First().Account : _NewEmployee;
_Budget_HeadCount.JulLast = h.JulLast == "" ? 0 : h.JulLast == "(1)" ? -1 : Convert.ToInt32(h.JulLast);
_Budget_HeadCount.AugLast = h.AugLast == "" ? 0 : h.AugLast == "(1)" ? -1 : Convert.ToInt32(h.AugLast);
_Budget_HeadCount.SepLast = h.SepLast == "" ? 0 : h.SepLast == "(1)" ? -1 : Convert.ToInt32(h.SepLast);
_Budget_HeadCount.OctLast = h.OctLast == "" ? 0 : h.OctLast == "(1)" ? -1 : Convert.ToInt32(h.OctLast);
_Budget_HeadCount.NovLast = h.NovLast == "" ? 0 : h.NovLast == "(1)" ? -1 : Convert.ToInt32(h.NovLast);
_Budget_HeadCount.DecLast = h.DecLast == "" ? 0 : h.DecLast == "(1)" ? -1 : Convert.ToInt32(h.DecLast);
_Budget_HeadCount.Jan = h.Jan == "" ? 0 : h.Jan == "(1)" ? -1 : Convert.ToInt32(h.Jan);
_Budget_HeadCount.Feb = h.Feb == "" ? 0 : h.Feb == "(1)" ? -1 : Convert.ToInt32(h.Feb);
_Budget_HeadCount.Mar = h.Mar == "" ? 0 : h.Mar == "(1)" ? -1 : Convert.ToInt32(h.Mar);
_Budget_HeadCount.Apr = h.Apr == "" ? 0 : h.Apr == "(1)" ? -1 : Convert.ToInt32(h.Apr);
_Budget_HeadCount.May = h.May == "" ? 0 : h.May == "(1)" ? -1 : Convert.ToInt32(h.May);
_Budget_HeadCount.Jun = h.Jun == "" ? 0 : h.Jun == "(1)" ? -1 : Convert.ToInt32(h.Jun);
_Budget_HeadCount.Jul = h.Jul == "" ? 0 : h.Jul == "(1)" ? -1 : Convert.ToInt32(h.Jul);
_Budget_HeadCount.Aug = h.Aug == "" ? 0 : h.Aug == "(1)" ? -1 : Convert.ToInt32(h.Aug);
_Budget_HeadCount.Sep = h.Sep == "" ? 0 : h.Sep == "(1)" ? -1 : Convert.ToInt32(h.Sep);
_Budget_HeadCount.Oct = h.Oct == "" ? 0 : h.Oct == "(1)" ? -1 : Convert.ToInt32(h.Oct);
_Budget_HeadCount.Nov = h.Nov == "" ? 0 : h.Nov == "(1)" ? -1 : Convert.ToInt32(h.Nov);
_Budget_HeadCount.Dec = h.Dec == "" ? 0 : h.Dec == "(1)" ? -1 : Convert.ToInt32(h.Dec);
_Budget_HeadCount.Date = (DateTime.Now.Year + 1).ToString();
_Budget_HeadCount.Version = base.Version;
_Budget_HeadCount.DepartmentId = base.PartTime ? h.DepartmentId : base.DepartmentId;
_Budget_HeadCount.Factory = base.PartTime ? h.Factory : base.Factory;
_Budget_HeadCount.PartTime = base.PartTime;
_Budget_HeadCount.CurrentHC = h.Current == "1" ? true : false;
string _ItemId_Reason = "";
if (!base.PartTime)
{
if (h.Reason != "Pls Choose 請選擇")
{
_ItemId_Reason = _ItemCatalog.Where(x => x.ClassName == "Reason" && x.Name.Trim() == h.Reason.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "Reason" && x.Name.Trim() == h.Reason.Trim()).First().ItemId : "";
}
_Budget_HeadCount.ItemId_Reason = _ItemId_Reason;
}
_Budget_HeadCount.Remark = h.Remark;
_Budget_HeadCountList.Add(_Budget_HeadCount);
if (h.Current == null && !base.PartTime)
{
insertHeadCountHR(h, _ItemCatalog, _NewEmployee);
}
}
catch (Exception ex)
{
string _h = JsonConvert.SerializeObject(h);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _h);
}
_Start++;
}
_Budget.HeadCount = _Budget_HeadCountList;
return(_Budget);
}
/// <summary>
/// p.s mapping name maybe need to change
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <ItemCatalog> _ItemCatalog = this._itemCatalogService.GetAll().ToList();
List <Budget_Traveling> _Budget_TravelingList = new List <Budget_Traveling>();
IQueryable <Traveling> _TravelingList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <Traveling>(d => d.AltwName, "AltwName");
_Excel.AddMapping <Traveling>(d => d.Country, "Country");
_Excel.AddMapping <Traveling>(d => d.Days, "Days");
_Excel.AddMapping <Traveling>(d => d.Purpose, "Purpose");
_Excel.AddMapping <Traveling>(d => d.Type, "Type");
_Excel.AddMapping <Traveling>(d => d.Remark, "Remark");
_Excel.AddMapping <Traveling>(d => d.Jan, "Jan");
_Excel.AddMapping <Traveling>(d => d.Feb, "Feb");
_Excel.AddMapping <Traveling>(d => d.Mar, "Mar");
_Excel.AddMapping <Traveling>(d => d.Apr, "Apr");
_Excel.AddMapping <Traveling>(d => d.May, "May");
_Excel.AddMapping <Traveling>(d => d.Jun, "Jun");
_Excel.AddMapping <Traveling>(d => d.Jul, "Jul");
_Excel.AddMapping <Traveling>(d => d.Aug, "Aug");
_Excel.AddMapping <Traveling>(d => d.Sep, "Sep");
_Excel.AddMapping <Traveling>(d => d.Oct, "Oct");
_Excel.AddMapping <Traveling>(d => d.Nov, "Nov");
_Excel.AddMapping <Traveling>(d => d.Dec, "Dec");
// every time get first sheet data
_TravelingList = from x in _Excel.Worksheet <Traveling>(0)
select x;
int _Start = 2;
foreach (Traveling c in _TravelingList)
{
if (_Start == 2 || _Start == 3 || c.AltwName == null)
{
_Start++;
continue;
}
try
{
Budget_Traveling _Budget_Traveling = new Budget_Traveling();
_Budget_Traveling.ItemId_TravelingPurpose = _ItemCatalog.Where(x => x.ClassName == "TravelingPurpose" && x.Name.Trim() == c.Purpose.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "TravelingPurpose" && x.Name.Trim() == c.Purpose.Trim()).First().ItemId : "";
// _Budget_Traveling.Account = this._budget_HeadCountHRService.GetAll().Where(x => x.AltwName == c.AltwName).First().Account;
_Budget_Traveling.AltwName = c.AltwName;
_Budget_Traveling.Country = c.Country;
_Budget_Traveling.Days = c.Days == null ? 0 : int.Parse(c.Days, System.Globalization.NumberStyles.AllowThousands);
_Budget_Traveling.ItemId_TravelingType = _ItemCatalog.Where(x => x.ClassName == "TravelingType" && x.Name.Trim() == c.Type.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "TravelingType" && x.Name.Trim() == c.Type.Trim()).First().ItemId : "";
_Budget_Traveling.Remark = c.Remark;
_Budget_Traveling.Jan = this._commonFileService.GetExcelMinusNumber(c.Jan);
_Budget_Traveling.Feb = this._commonFileService.GetExcelMinusNumber(c.Feb);
_Budget_Traveling.Mar = this._commonFileService.GetExcelMinusNumber(c.Mar);
_Budget_Traveling.Apr = this._commonFileService.GetExcelMinusNumber(c.Apr);
_Budget_Traveling.May = this._commonFileService.GetExcelMinusNumber(c.May);
_Budget_Traveling.Jun = this._commonFileService.GetExcelMinusNumber(c.Jun);
_Budget_Traveling.Jul = this._commonFileService.GetExcelMinusNumber(c.Jul);
_Budget_Traveling.Aug = this._commonFileService.GetExcelMinusNumber(c.Aug);
_Budget_Traveling.Sep = this._commonFileService.GetExcelMinusNumber(c.Sep);
_Budget_Traveling.Oct = this._commonFileService.GetExcelMinusNumber(c.Oct);
_Budget_Traveling.Nov = this._commonFileService.GetExcelMinusNumber(c.Nov);
_Budget_Traveling.Dec = this._commonFileService.GetExcelMinusNumber(c.Dec);
_Budget_Traveling.Date = (DateTime.Now.Year + 1).ToString();
_Budget_Traveling.DepartmentId = base.DepartmentId;
_Budget_Traveling.Version = base.Version;
_Budget_Traveling.Factory = base.Factory;
_Budget_TravelingList.Add(_Budget_Traveling);
}
catch (Exception ex)
{
string _c = JsonConvert.SerializeObject(c);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _c);
}
_Start++;
}
if (_Budget_TravelingList.Count > 0)
{
_Budget.DeptExpense = setDeptExpense(_Budget_TravelingList);
}
_Budget.Traveling = _Budget_TravelingList;
return(_Budget);
}
public override IQueryable <Edi_Pos> ParseEdiPos(string filePath)
{
IQueryable <Edi_Pos> _Edi_PosQuery;
List <Edi_Pos> _Edi_PosList = new List <Edi_Pos>();
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <Edi_Pos>(d => d.Type, "Type");
_Excel.AddMapping <Edi_Pos>(d => d.Distributor, "DIST");
_Excel.AddMapping <Edi_Pos>(d => d.Address, "Address");
_Excel.AddMapping <Edi_Pos>(d => d.City, "CITY");
_Excel.AddMapping <Edi_Pos>(d => d.State, "STATE");
_Excel.AddMapping <Edi_Pos>(d => d.ZIP, "ZIP");
_Excel.AddMapping <Edi_Pos>(d => d.Series, "Series");
_Excel.AddMapping <Edi_Pos>(d => d.PartNo, "PARTNO");
_Excel.AddMapping <Edi_Pos>(d => d.Quantity, "QTY");
_Excel.AddMapping <Edi_Pos>(d => d.Cost, "COST");
_Excel.AddMapping <Edi_Pos>(d => d.Price, "PRICE");
_Excel.AddMapping <Edi_Pos>(d => d.ResellingExt, "RESEXT");
_Excel.AddMapping <Edi_Pos>(d => d.ACCT, "ACCT");
_Excel.AddMapping <Edi_Pos>(d => d.CustomerName, "NAME");
_Excel.AddMapping <Edi_Pos>(d => d.MarketCode, "Corporate Market");
_Excel.AddMapping <Edi_Pos>(d => d.MarketCode, "Market Code");
_Excel.AddMapping <Edi_Pos>(d => d.Market, "Market Segment");
_Excel.AddMapping <Edi_Pos>(d => d.SubSegmentCode, "Sub-Segment Code");
_Excel.AddMapping <Edi_Pos>(d => d.SubSegment, "Sub-Segment");
_Excel.AddMapping <Edi_Pos>(d => d.Remarks, "Remarks");
_Excel.AddMapping <Edi_Pos>(d => d.ShipDate, "SHIP DATE");
_Excel.AddMapping <Edi_Pos>(d => d.ShipDate2, "SHIP DATE 2");
_Excel.AddMapping <Edi_Pos>(d => d.ShipMonth, "SHIP MONTH");
_Excel.AddMapping <Edi_Pos>(d => d.ShipQuarter, "SHIP QUARTER");
_Excel.AddMapping <Edi_Pos>(d => d.YY, "YY");
_Excel.AddMapping <Edi_Pos>(d => d.CountryCode, "COUNTRY CODE");
_Excel.AddMapping <Edi_Pos>(d => d.Country, "COUNTRY ");
_Excel.AddMapping <Edi_Pos>(d => d.Region, "Region");
int _Start = 1;
// _Edi_PosQuery = from x in _Excel.Worksheet<Edi_Pos>(0)
// select x;
_Edi_PosQuery = from x in _Excel.Worksheet <Edi_Pos>("Data")
select x;
foreach (Edi_Pos c in _Edi_PosQuery)
{
try
{
c.ShipDate2 = c.ShipDate2 != null?DateTime.Parse(c.ShipDate2).ToString("yyyy/MM/dd") : "";
c.Status = "T";
_Edi_PosList.Add(c);
}
catch (Exception ex)
{
string _c = JsonConvert.SerializeObject(c);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _c);
}
_Start++;
}
_Edi_PosQuery = _Edi_PosList.AsQueryable();
return(_Edi_PosQuery);
}
public override IQueryable <PosData> Parse(int _Model, string filePath)
{
IQueryable <PosData> _PosList;
PosColumnMap _PosColumnMap = _posColumnMapService.GetByID(_Model);
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <PosData>(d => d.Year, _PosColumnMap.Year);
_Excel.AddMapping <PosData>(d => d.MonthYear, _PosColumnMap.MonthYear);
_Excel.AddMapping <PosData>(d => d.Distributor, _PosColumnMap.Distributor);
_Excel.AddMapping <PosData>(d => d.Customer, _PosColumnMap.Customer);
_Excel.AddMapping <PosData>(d => d.ISOCountryCode, _PosColumnMap.ISOCountryCode);
_Excel.AddMapping <PosData>(d => d.Country, _PosColumnMap.Country);
_Excel.AddMapping <PosData>(d => d.SalesArea, _PosColumnMap.SalesArea);
_Excel.AddMapping <PosData>(d => d.SalesManager, _PosColumnMap.SalesManager);
_Excel.AddMapping <PosData>(d => d.City, _PosColumnMap.City);
_Excel.AddMapping <PosData>(d => d.PostCode, _PosColumnMap.PostCode);
_Excel.AddMapping <PosData>(d => d.PartNo, _PosColumnMap.PartNo);
_Excel.AddMapping <PosData>(d => d.BaseCurrency, _PosColumnMap.BaseCurrency);
_Excel.AddMapping <PosData>(d => d.Qty, _PosColumnMap.Qty.ToString());
_Excel.AddMapping <PosData>(d => d.TotalSalesBaseCurreny, _PosColumnMap.TotalSalesBaseCurrency);
_Excel.AddMapping <PosData>(d => d.TotalSalesEUR, _PosColumnMap.TotalSalesEUR);
_Excel.AddMapping <PosData>(d => d.ProductSeries, _PosColumnMap.ProductSeries);
// every time get first sheet data
_PosList = from x in _Excel.Worksheet <PosData>(0)
select x;
return(_PosList);
}
/// <summary>
/// p.s mapping name maybe need to change
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <ItemCatalog> _ItemCatalog = this._itemCatalogService.GetAll().ToList();
List <Budget_Capex> _Budget_CapexList = new List <Budget_Capex>();
IQueryable <Capex> _CapexList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <Capex>(d => d.AssetExp, "AssetExp");
_Excel.AddMapping <Capex>(d => d.ProjectName, "ProjectName");
_Excel.AddMapping <Capex>(d => d.ProjectRemark, "ProjectRemark");
_Excel.AddMapping <Capex>(d => d.AssetExpType, "AssetExpType");
_Excel.AddMapping <Capex>(d => d.JulLast, "JulLast");
_Excel.AddMapping <Capex>(d => d.AugLast, "AugLast");
_Excel.AddMapping <Capex>(d => d.Purpose, "Purpose");
_Excel.AddMapping <Capex>(d => d.SepLast, "SepLast");
_Excel.AddMapping <Capex>(d => d.OctLast, "OctLast");
_Excel.AddMapping <Capex>(d => d.NovLast, "NovLast");
_Excel.AddMapping <Capex>(d => d.DecLast, "DecLast");
_Excel.AddMapping <Capex>(d => d.Jan, "Jan");
_Excel.AddMapping <Capex>(d => d.Feb, "Feb");
_Excel.AddMapping <Capex>(d => d.Mar, "Mar");
_Excel.AddMapping <Capex>(d => d.Apr, "Apr");
_Excel.AddMapping <Capex>(d => d.May, "May");
_Excel.AddMapping <Capex>(d => d.Jun, "Jun");
_Excel.AddMapping <Capex>(d => d.Jul, "Jul");
_Excel.AddMapping <Capex>(d => d.Aug, "Aug");
_Excel.AddMapping <Capex>(d => d.Sep, "Sep");
_Excel.AddMapping <Capex>(d => d.Oct, "Oct");
_Excel.AddMapping <Capex>(d => d.Nov, "Nov");
_Excel.AddMapping <Capex>(d => d.Dec, "Dec");
_Excel.AddMapping <Capex>(d => d.InternalOrder, "InternalOrder");
_Excel.AddMapping <Capex>(d => d.ProjectAmount, "ProjectAmount");
// every time get first sheet data
_CapexList = from x in _Excel.Worksheet <Capex>(0)
select x;
int _Start = 2;
foreach (Capex c in _CapexList)
{
if (_Start == 2 || _Start == 3 || c.ProjectName == null)
{
_Start++;
continue;
}
try
{
Budget_Capex _Budget_Capex = new Budget_Capex();
_Budget_Capex.ItemId_AssetExp = _ItemCatalog.Where(x => x.ClassName == "AssetExp" && x.Name.Trim() == c.AssetExp.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "AssetExp" && x.Name.Trim() == c.AssetExp.Trim()).First().ItemId : "";
if (Encoding.Default.GetByteCount(c.ProjectName) > 40)
{
throw new Exception("project name exceed 40 words");
}
_Budget_Capex.ProjectName = c.ProjectName;
_Budget_Capex.ProjectRemark = c.ProjectRemark;
_Budget_Capex.ItemId_AssetExpType = _ItemCatalog.Where(x => x.ClassName == "AssetExpType" && x.Name.Trim() == c.AssetExpType.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "AssetExpType" && x.Name.Trim() == c.AssetExpType.Trim()).First().ItemId : "";
_Budget_Capex.ItemId_Purpose = _ItemCatalog.Where(x => x.ClassName == "AssetExpPurpose" && x.Name.Trim() == c.Purpose.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "AssetExpPurpose" && x.Name.Trim() == c.Purpose.Trim()).First().ItemId : "";
_Budget_Capex.JulLast = this._commonFileService.GetExcelMinusNumber(c.JulLast);
_Budget_Capex.AugLast = this._commonFileService.GetExcelMinusNumber(c.AugLast);
_Budget_Capex.SepLast = this._commonFileService.GetExcelMinusNumber(c.SepLast);
_Budget_Capex.OctLast = this._commonFileService.GetExcelMinusNumber(c.OctLast);
_Budget_Capex.NovLast = this._commonFileService.GetExcelMinusNumber(c.NovLast);
_Budget_Capex.DecLast = this._commonFileService.GetExcelMinusNumber(c.DecLast);
_Budget_Capex.Jan = this._commonFileService.GetExcelMinusNumber(c.Jan);
_Budget_Capex.Feb = this._commonFileService.GetExcelMinusNumber(c.Feb);
_Budget_Capex.Mar = this._commonFileService.GetExcelMinusNumber(c.Mar);
_Budget_Capex.Apr = this._commonFileService.GetExcelMinusNumber(c.Apr);
_Budget_Capex.May = this._commonFileService.GetExcelMinusNumber(c.May);
_Budget_Capex.Jun = this._commonFileService.GetExcelMinusNumber(c.Jun);
_Budget_Capex.Jul = this._commonFileService.GetExcelMinusNumber(c.Jul);
_Budget_Capex.Aug = this._commonFileService.GetExcelMinusNumber(c.Aug);
_Budget_Capex.Sep = this._commonFileService.GetExcelMinusNumber(c.Sep);
_Budget_Capex.Oct = this._commonFileService.GetExcelMinusNumber(c.Oct);
_Budget_Capex.Nov = this._commonFileService.GetExcelMinusNumber(c.Nov);
_Budget_Capex.Dec = this._commonFileService.GetExcelMinusNumber(c.Dec);
_Budget_Capex.Date = (DateTime.Now.Year + 1).ToString();
_Budget_Capex.DepartmentId = base.DepartmentId;
_Budget_Capex.Version = base.Version;
_Budget_Capex.Factory = base.Factory;
//add two column
_Budget_Capex.InternalOrder = c.InternalOrder;
_Budget_Capex.ProjectAmount = this._commonFileService.GetExcelMinusNumber(c.ProjectAmount);
_Budget_CapexList.Add(_Budget_Capex);
}
catch (Exception ex)
{
string _c = JsonConvert.SerializeObject(c);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _c);
}
_Start++;
}
_Budget.Capex = _Budget_CapexList;
return(_Budget);
}
public override SapMiddleData Parse(string filePath)
{
List <SapRate> _SapRateList = initSapRate();
SapMiddleData _SapMiddleData = new SapMiddleData();
List <SAP_PriceList> _SAP_PriceList = new List <SAP_PriceList>();
IQueryable <PriceList> _PriceList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <PriceList>(d => d.KUNNR, "KUNNR");
_Excel.AddMapping <PriceList>(d => d.NAME1, "NAME1");
if (base.PriceType == "終端客戶價格表A501")
{
_Excel.AddMapping <PriceList>(d => d.ZZENDCUST, "ZZENDCUST");
_Excel.AddMapping <PriceList>(d => d.ZNAME1, "ZNAME1");
}
_Excel.AddMapping <PriceList>(d => d.MATNR, "MATNR");
_Excel.AddMapping <PriceList>(d => d.KONWA, "KONWA");
_Excel.AddMapping <PriceList>(d => d.KSTBM, "KSTBM");
_Excel.AddMapping <PriceList>(d => d.KBETR, "KBETR");
_Excel.AddMapping <PriceList>(d => d.KLFN1, "KLFN1");
_Excel.AddMapping <PriceList>(d => d.KPEIN, "KPEIN");
_Excel.AddMapping <PriceList>(d => d.KNUMH, "KNUMH");
_Excel.AddMapping <PriceList>(d => d.KSCHL, "KSCHL");
// every time get first sheet data
_PriceList = from x in _Excel.Worksheet <PriceList>(0)
select x;
int _Start = 1, _Order = 1;
string _KNUMH = "";
SAP_PriceList _Sap_Price = new SAP_PriceList();
foreach (PriceList c in _PriceList)
{
try
{
int _KSTBM = 0;
decimal?_KBETR = 0, _Price = 0, _UnitPrice;
if (c.KNUMH != _KNUMH)
{
_Order = 1;
if (_Start != 1)
{
_SAP_PriceList.Add(_Sap_Price);
}
_Sap_Price = new SAP_PriceList();
_KNUMH = c.KNUMH;//group price
_Sap_Price.CustomerId = c.KUNNR;
_Sap_Price.CustomerName = c.NAME1;
_Sap_Price.EndCustomerId = c.ZZENDCUST;
_Sap_Price.EndCustomerName = c.ZNAME1;
_Sap_Price.PartNumber = c.MATNR;
_Sap_Price.Currency = c.KONWA;
_Sap_Price.Date = DateTime.Now.Date.ToString("yyyyMMdd");
_Sap_Price.Rate = Convert.ToDecimal(_SapRateList.Where(x => x.Currency == c.KONWA.Trim()).First().Rate);
_Sap_Price.Per = c.KPEIN == null ? 0 : int.Parse(c.KPEIN, System.Globalization.NumberStyles.AllowThousands);
_Sap_Price.TaxType = c.KSCHL;
}
_KSTBM = c.KSTBM == null ? 0 : int.Parse(c.KSTBM, System.Globalization.NumberStyles.AllowThousands);
if (c.KONWA == "TWD" || c.KONWA == "JPY")
{
_KBETR = Convert.ToDecimal(c.KBETR) * 100;
}
else
{
_KBETR = Convert.ToDecimal(c.KBETR);
}
_UnitPrice = Convert.ToDecimal(_KBETR / _Sap_Price.Per);
_Price = _UnitPrice * _Sap_Price.Rate;
if (_Order == 1)
{
_Sap_Price.MOQ1 = _KSTBM;
_Sap_Price.Price1 = _Price;
}
else if (_Order == 2)
{
_Sap_Price.MOQ2 = _KSTBM;
_Sap_Price.Price2 = _Price;
}
else if (_Order == 3)
{
_Sap_Price.MOQ3 = _KSTBM;
_Sap_Price.Price3 = _Price;
}
else if (_Order == 4)
{
_Sap_Price.MOQ4 = _KSTBM;
_Sap_Price.Price4 = _Price;
}
else if (_Order == 5)
{
_Sap_Price.MOQ5 = _KSTBM;
_Sap_Price.Price5 = _Price;
}
else if (_Order == 6)
{
_Sap_Price.MOQ6 = _KSTBM;
_Sap_Price.Price6 = _Price;
}
else if (_Order == 7)
{
_Sap_Price.MOQ7 = _KSTBM;
_Sap_Price.Price7 = _Price;
}
else if (_Order == 8)
{
_Sap_Price.MOQ8 = _KSTBM;
_Sap_Price.Price8 = _Price;
}
else if (_Order == 9)
{
_Sap_Price.MOQ9 = _KSTBM;
_Sap_Price.Price9 = _Price;
}
else if (_Order == 10)
{
_Sap_Price.MOQ10 = _KSTBM;
_Sap_Price.Price10 = _Price;
}
_Order++;
if (_Start == _PriceList.Count())
{
_SAP_PriceList.Add(_Sap_Price);
}
}
catch (Exception ex)
{
string _c = JsonConvert.SerializeObject(c);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _c);
}
_Start++;
}
_SapMiddleData.PriceList = _SAP_PriceList;
return(_SapMiddleData);
}
/// <summary>
/// p.s mapping name maybe need to change
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <Budget_Cost> _Budget_Cost = this._budget_CostService.GetAll().ToList();
List <Budget_DeptExpense> _Budget_DeptExpenseList = new List <Budget_DeptExpense>();
IQueryable <DeptExpense> _DeptExpenseList;
List <Budget_DeptExpense> _Budget_DeptExpenseList_Common = new List <Budget_DeptExpense>();
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <DeptExpense>(d => d.ItemId_CostElement, "CostCode");
_Excel.AddMapping <DeptExpense>(d => d.YTD, "YTD");
_Excel.AddMapping <DeptExpense>(d => d.Jan, "Jan");
_Excel.AddMapping <DeptExpense>(d => d.Feb, "Feb");
_Excel.AddMapping <DeptExpense>(d => d.Mar, "Mar");
_Excel.AddMapping <DeptExpense>(d => d.Apr, "Apr");
_Excel.AddMapping <DeptExpense>(d => d.May, "May");
_Excel.AddMapping <DeptExpense>(d => d.Jun, "Jun");
_Excel.AddMapping <DeptExpense>(d => d.Jul, "Jul");
_Excel.AddMapping <DeptExpense>(d => d.Aug, "Aug");
_Excel.AddMapping <DeptExpense>(d => d.Sep, "Sep");
_Excel.AddMapping <DeptExpense>(d => d.Oct, "Oct");
_Excel.AddMapping <DeptExpense>(d => d.Nov, "Nov");
_Excel.AddMapping <DeptExpense>(d => d.Dec, "Dec");
if (base.CommonBudget)
{
_Excel.AddMapping <DeptExpense>(d => d.Factory, "Factory");
_Excel.AddMapping <DeptExpense>(d => d.DepartmentId, "DepartmentId");
}
// every time get first sheet data
_DeptExpenseList = from x in _Excel.Worksheet <DeptExpense>("Upload")
select x;
int _Start = 2;
foreach (DeptExpense c in _DeptExpenseList)
{
if (_Start == 2 || _Start == 3 || c.ItemId_CostElement == null)
{
_Start++;
continue;
}
try
{
Budget_DeptExpense _Budget_DeptExpense = new Budget_DeptExpense();
_Budget_DeptExpense.CostCode = c.ItemId_CostElement;
_Budget_DeptExpense.YTD = this._commonFileService.GetExcelMinusNumber(c.YTD);
_Budget_DeptExpense.Jan = this._commonFileService.GetExcelMinusNumber(c.Jan);
_Budget_DeptExpense.Feb = this._commonFileService.GetExcelMinusNumber(c.Feb);
_Budget_DeptExpense.Mar = this._commonFileService.GetExcelMinusNumber(c.Mar);
_Budget_DeptExpense.Apr = this._commonFileService.GetExcelMinusNumber(c.Apr);
_Budget_DeptExpense.May = this._commonFileService.GetExcelMinusNumber(c.May);
_Budget_DeptExpense.Jun = this._commonFileService.GetExcelMinusNumber(c.Jun);
_Budget_DeptExpense.Jul = this._commonFileService.GetExcelMinusNumber(c.Jul);
_Budget_DeptExpense.Aug = this._commonFileService.GetExcelMinusNumber(c.Aug);
_Budget_DeptExpense.Sep = this._commonFileService.GetExcelMinusNumber(c.Sep);
_Budget_DeptExpense.Oct = this._commonFileService.GetExcelMinusNumber(c.Oct);
_Budget_DeptExpense.Nov = this._commonFileService.GetExcelMinusNumber(c.Nov);
_Budget_DeptExpense.Dec = this._commonFileService.GetExcelMinusNumber(c.Dec);
_Budget_DeptExpense.Date = (DateTime.Now.Year + 1).ToString();
_Budget_DeptExpense.DepartmentId = base.CommonBudget ? c.DepartmentId : base.DepartmentId;
_Budget_DeptExpense.Factory = base.CommonBudget ? c.Factory : base.Factory;
_Budget_DeptExpense.Version = base.Version;
//00120000:部門費用
_Budget_DeptExpense.ItemId_CostCommon = base.CommonBudget ? base.ItemId_CostCommon : "00120000";
_Budget_DeptExpenseList.Add(_Budget_DeptExpense);
//6136010000:訓練費
if (c.ItemId_CostElement == "6136010000" && base.CommonBudget)
{
_Budget_DeptExpenseList_Common.Add(_Budget_DeptExpense);
}
}
catch (Exception ex)
{
string _c = JsonConvert.SerializeObject(c);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _c);
}
_Start++;
}
if (this.ListError.Count == 0)
{
foreach (Budget_DeptExpense budget_DeptExpense in _Budget_DeptExpenseList_Common)
{
updateDeptExpense(budget_DeptExpense);
}
}
_Budget.DeptExpense = _Budget_DeptExpenseList;
return(_Budget);
}