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