/// <summary> /// 讀取Excel文件 /// </summary> /// <param name="filePath"></param> /// <param name="channel_id"></param> /// <param name="fExtension"></param> /// <param name="isHeaderError"></param> /// <param name="errorPm"></param> /// <returns></returns> public List<ProductItemMapCustom> ReadFile(string filePath, int channel_id, string fExtension, ref bool isHeaderError, List<ProductItemMapCustom> errorPm) { try { NPOI4ExcelHelper fm = new NPOI4ExcelHelper(filePath); DataTable dt = fm.SheetData(); if (dt == null) { return null; } List<ProductItemMapCustom> result = new List<ProductItemMapCustom>(); Regex RegxProductId = new Regex("^\\d{5}$"); Regex RegxItemId = new Regex("^\\d{6}$"); Regex RegxMoney = new Regex("^\\d{1,9}$"); for (int i = 0, j = dt.Rows.Count; i < j; i++) { bool bl = true; bool el = true; bool isNull = false; bool Pro_id = false;//記錄商品編號是否為空 bool Itm_id = false;//記錄商品細項編號是否為空 PriceMaster prm = new PriceMaster(); ProductItemMapCustom pm = new ProductItemMapCustom(); pm.channel_id = Convert.ToUInt32(channel_id); for (int m = 0, n = dt.Columns.Count; m < n; m++) { string valStr = dt.Rows[i][m].ToString(); switch (dt.Columns[m].ToString().Trim()) { case "商品編號(5碼)": if (!string.IsNullOrEmpty(valStr)) { uint _ProductId = 0; uint.TryParse(valStr ?? "0", out _ProductId); pm.product_id = _ProductId; prm.product_id = _ProductId; if (!RegxProductId.IsMatch(pm.product_id.ToString())) { bl = false; } } else { Pro_id = true; isNull = true; break; } break; case "商品細項編號(6碼)": if (!string.IsNullOrEmpty(valStr)) { if (valStr.Split(',').Length > 1) { for (int k = 0; k < valStr.Split(',').Length; k++) { if (!RegxItemId.IsMatch(valStr.Split(',')[k])) { bl = false; } } pm.group_item_id = CommonFunction.Rank_ItemId(valStr); } else { uint _itemId = 0; uint.TryParse(valStr ?? "0", out _itemId); pm.item_id = _itemId; pm.group_item_id = _itemId.ToString(); if (!RegxItemId.IsMatch(pm.item_id.ToString())) { bl = false; } } } else { Itm_id = true; isNull = true; break; //bl = false; }; break; case "外站商品名稱": if (!string.IsNullOrEmpty(valStr)) { pm.product_name = valStr; } else { bl = false; }; break; case "外站商品編號": if (!string.IsNullOrEmpty(valStr)) { pm.channel_detail_id = valStr; } else { bl = false; }; break; case "外站商品成本": if (!string.IsNullOrEmpty(valStr)) { if (RegxMoney.IsMatch(valStr)) { pm.product_cost = int.Parse(valStr); } else { bl = false; } } else { bl = false; } ; break; case "外站商品售價": if (!string.IsNullOrEmpty(valStr)) { if (RegxMoney.IsMatch(valStr)) { pm.product_price = int.Parse(valStr); } else { bl = false; } } else { bl = false; } ; break; case "組合中之數量(0為照組合中之設定)": if (!string.IsNullOrEmpty(valStr)) { if (RegxMoney.IsMatch(valStr)) { pm.set_num = uint.Parse(valStr); } else { bl = false; } } else { bl = false; } break; case "user_email"://郵箱 if (!string.IsNullOrEmpty(valStr)) { if (_callerMgr.Login(valStr) != null) { prm.user_id = uint.Parse(_callerMgr.Login(valStr).user_id.ToString()); } } break; case "site_id"://站臺 if (!string.IsNullOrEmpty(valStr)) { if (RegxMoney.IsMatch(valStr)) { prm.site_id = uint.Parse(valStr); } else { bl = false; } } else { bl = false; } break; case "user_level"://站臺等級 if (!string.IsNullOrEmpty(valStr)) { if (RegxMoney.IsMatch(valStr)) { prm.user_level = uint.Parse(valStr); } else { bl = false; } } else { bl = false; } break; default: el = false; break; } //if (isNull) //{ // break; //} } //判斷Excel表頭格式 if (!el) { isHeaderError = true; break; } else { isHeaderError = false; } //若單一商品可以無商品編號,若固定組合可以無商品細項編號。其餘類型出現為空數據,則此行記錄無效,不做處理 if (Itm_id) { if (_productItemMapDao.CombinationQuery(pm).FirstOrDefault() != null && _productItemMapDao.CombinationQuery(pm).FirstOrDefault().combination == 2) { List<ProductComboMap> pMc = _productItemMapDao.QueryItemId(pm.product_id); if (pMc != null && pMc.Where(e => e.product_spec == 0).Count() == pMc.Count()) { string strItem = ""; for (int Itemi = 0, Itemj = pMc.Count(); Itemi < Itemj; Itemi++) { if (Itemi > 0) { strItem += ","; } strItem += pMc[Itemi].item_id; } pm.group_item_id = CommonFunction.Rank_ItemId(strItem); isNull = false; } } } //單一商品邏輯 add by hufeng0813w 2013/12/23 if (Pro_id) { ProductItem pIm = new ProductItem(); pIm.Item_Id = uint.Parse(pm.item_id.ToString()); //pm.product_id = _productItemDao.Query(pIm).FirstOrDefault().Product_Id; ProductItem itemResult = _productItemDao.Query(pIm).FirstOrDefault(); if (itemResult != null) { prm.product_id = pm.product_id = itemResult.Product_Id; ProductMapCustom mapcusResult = _productItemMapDao.CombinationQuery(pm).FirstOrDefault(); if (mapcusResult != null && mapcusResult.combination == 1) { isNull = false; } } } if (isNull) { continue; } //獲取price_master_id if (prm.product_id != 0) { pm.price_master_id = _priceMasterMgr.QueryPriceMasterId(prm); } else { bl = false; } //判斷內容格式 if (!bl) { pm.msg = Resource.CoreMessage.GetResource("ERROR_FORMAT"); } else { ProductMapCustom map = _productItemMapDao.CombinationQuery(pm).FirstOrDefault(); if (map != null) { uint Combination = map.combination; if (Combination != 2) { ProductItem pi = new ProductItem(); pi.Item_Id = uint.Parse(pm.item_id.ToString()); if (_productItemMgr.Query(pi).Count == 0) { pm.msg = Resource.CoreMessage.GetResource("ITEMID_ID_NOT_EXISTS"); bl = false; } else { if (_productItemMapDao.Exist(pm) > 0) { pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS"); bl = false; } else { ProductItemMapCustom existItem = result.Where(m => m.item_id == pm.item_id).FirstOrDefault(); if (existItem != null) { pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS"); bl = false; } } } } else { #region for (int l = 0; l < pm.group_item_id.Split(',').Length; l++) { if (_productItemMapDao.Comb_Compare(pm.product_id, uint.Parse(pm.group_item_id.Split(',')[l])) == 0) { pm.msg = Resource.CoreMessage.GetResource("ITEMID_ID_NOT_COMBINATION"); bl = false; } else { #region if (l == 0) { if (_productItemMapDao.Comb_Exist(pm) > 0) { pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS"); bl = false; } else { ProductItemMapCustom existItem = result.Where(m => m.group_item_id == pm.group_item_id).FirstOrDefault(); if (existItem != null) { pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS"); bl = false; } } } #endregion } } #endregion } } else { bl = false; pm.msg = Resource.CoreMessage.GetResource("PRODUCT_NOT_EXIST"); } } if (!bl) { errorPm.Add(pm); } else { result.Add(pm); } } return result; // _access.getObjByTable<ProductItemMap>(dt); } catch (Exception ex) { throw new Exception("ProductItemMapExcelMgr-->ReadFile-->" + ex.Message, ex); } }
//單一商品 public List<ProductItemMapCustom> SingleProductItemMapExc(PriceMasterCustom pmc) { IProductItemImplMgr _proItem = new ProductItemMgr(connectionString); List<ProductItem> resultList = _proItem.Query(pmc); List<ProductItemMapCustom> pimcs = new List<ProductItemMapCustom>(); int i=1; foreach (var item in resultList) { var p = new ProductItemMapCustom(); p.channel_detail_id = pmc.product_id + ((resultList.Count > 1) ? ("-" + i) : "");//如果該商品只有一個子項,則不拼接序號 p.product_name = pmc.product_name + ((item.Spec_Name_1 == "" && item.Spec_Name_2 == "") ? "" : ("-" + item.Spec_Name_1 + item.Spec_Name_2)); p.product_id = pmc.product_id; p.group_item_id = item.Item_Id.ToString() ; p.product_price = pmc.price; p.product_cost = pmc.cost; p.site_id = pmc.site_id; p.user_level = pmc.user_level; p.user_id = pmc.user_id; pimcs.Add(p); i++; } return pimcs; }
//組合商品 public List<ProductItemMapCustom> FixedComboProductItemMapExc(PriceMasterCustom pmc) { ProductItemMapMgr _mapMgr = new ProductItemMapMgr(connectionString); List<ProductItemMapCustom> pimcs = new List<ProductItemMapCustom>(); List<ProductMapCustom> resultList = _ProductItemMapDao.CombinationQuery( new ProductItemMapCustom { product_id = pmc.product_id } ); if (resultList.Count() > 0) { List<List<ProductComboMap>> itemAll = new List<List<ProductComboMap>>(); for (int i = 0, j = resultList.Count(); i < j; i++) { List<ProductComboMap> itemList = _mapMgr.ProductComboItemQuery(resultList[i].child_id, (int)pmc.product_id); if (itemList.Count > 0)//當子商品有規格時才可以建立對照 { itemAll.Add(itemList); } } if (itemAll.Count == 0) return pimcs; string s = ""; getCombo(0, itemAll, "", ref s); s = s.Remove(s.Length - 1); string[] itemstr = s.Split('&'); for (int Si = 0, Sj = itemstr.Length; Si < Sj; Si++) { ProductItemMapCustom _productitemmap = new ProductItemMapCustom(); _productitemmap.channel_detail_id = pmc.product_id + ((Sj > 1) ? ("-" + (Si + 1)) : "");//如果組合種類為多種,則加序號 _productitemmap.product_name = pmc.product_name; _productitemmap.product_id = pmc.product_id; _productitemmap.group_item_id = itemstr[Si]; _productitemmap.product_price = pmc.price; _productitemmap.product_cost = pmc.cost; _productitemmap.site_id = pmc.site_id; _productitemmap.user_level = pmc.user_level; _productitemmap.user_id = pmc.user_id; pimcs.Add(_productitemmap); } } return pimcs; }
/// <summary> /// 匯出商品對照檔 /// </summary> /// <param name="query"></param> /// <returns></returns> public MemoryStream ExportProductItemMap(QueryVerifyCondition query) { IProductImplMgr _prodMgr = new ProductMgr(connectionString); List<ProductItemMapCustom> pims = new List<ProductItemMapCustom>(); List<Model.Custom.PriceMasterCustom> pmcs = _prodMgr.Query(query); foreach (var pmc in pmcs) { switch (pmc.combination) { case 1://單一商品 var single = SingleProductItemMapExc(pmc); pims.AddRange(single); break; case 2://固定組合 var fixedCombo = FixedComboProductItemMapExc(pmc); pims.AddRange(fixedCombo); break; case 3: case 4: ProductItemMapCustom item = new ProductItemMapCustom(); item.channel_detail_id = Resource.CoreMessage.GetResource("MANUAL_OPERATING"); item.product_name = pmc.product_name; item.product_id = pmc.product_id; item.site_id = pmc.site_id; item.user_level = pmc.user_level; item.user_id = pmc.user_id; pims.Add(item); break; default: break; } } //創建excel並填入數據 MemoryStream ms = new MemoryStream(); IWorkbook workBook = new HSSFWorkbook(); ISheet sheet = workBook.CreateSheet(); IRow headerRow = sheet.CreateRow(0); headerRow.CreateCell(0).SetCellValue(Resource.CoreMessage.GetResource("OUTSITE_PRODUCT_ID")); headerRow.CreateCell(1).SetCellValue(Resource.CoreMessage.GetResource("OUTSITE_PRODUCT_NAME")); headerRow.CreateCell(2).SetCellValue(Resource.CoreMessage.GetResource("PRODUCT_ID")); headerRow.CreateCell(3).SetCellValue(Resource.CoreMessage.GetResource("ITEM_ID")); headerRow.CreateCell(4).SetCellValue(Resource.CoreMessage.GetResource("COMBO_NUM_SET")); headerRow.CreateCell(5).SetCellValue(Resource.CoreMessage.GetResource("OUTSITE_PRODUCT_PRICE")); headerRow.CreateCell(6).SetCellValue("site_id"); headerRow.CreateCell(7).SetCellValue("user_level"); headerRow.CreateCell(8).SetCellValue("user_email"); int rowIndex=1; foreach (var item in pims) { IRow dataRow = sheet.CreateRow(rowIndex); dataRow.CreateCell(0).SetCellValue(item.channel_detail_id); dataRow.CreateCell(1).SetCellValue(item.product_name); dataRow.CreateCell(2).SetCellValue(item.product_id); dataRow.CreateCell(3).SetCellValue(item.group_item_id); dataRow.CreateCell(4).SetCellValue(0);//組合中之數量(0為照組合中之設定) //dataRow.CreateCell(5).SetCellValue(item.product_cost);//外站商品成本 dataRow.CreateCell(5).SetCellValue(item.product_price); dataRow.CreateCell(6).SetCellValue(item.site_id); dataRow.CreateCell(7).SetCellValue(item.user_level); dataRow.CreateCell(8).SetCellValue(item.user_id); rowIndex++; } workBook.Write(ms); ms.Flush(); ms.Position = 0; workBook = null; return ms; }
public List<ProductMapCustom> CombinationQuery(ProductItemMapCustom p) { return _ProductItemMapDao.CombinationQuery(p); }
/// <summary> /// 根據itemmap表中的productid查找該商品是不是組合商品 /// </summary> /// <param name="p">一條商品對照信息</param> /// <returns></returns> public List<ProductMapCustom> CombinationQuery(ProductItemMapCustom p) { StringBuilder sql = new StringBuilder("select p.combination,c.child_id,c.g_must_buy,c.pile_id,c.buy_limit,s.product_spec,p.product_name,p.prod_sz,m.cost,m.price,c.s_must_buy,p.price_type"); sql.AppendFormat(" from product p left join product_combo c on p.product_id = c.parent_id"); sql.AppendFormat(" left join product s on c.child_id = s.product_id"); //sql.AppendFormat(" left join price_master m on p.product_id=m.product_id and m.site_id=1 and m.user_level=1 and m.user_id=0 and m.child_id=p.product_id"); sql.AppendFormat(" left join price_master m on p.product_id=m.product_id and m.child_id=p.product_id and m.price_master_id={0}", p.price_master_id); //edit by xiangwang0413w 根據price_master_id來查詢價格 sql.AppendFormat(" where p.product_id = {0} order by c.id", p.product_id); return _accessMySql.getDataTableForObj<ProductMapCustom>(sql.ToString()); }
/// <summary> /// 查詢商品對照是否存在(組合) /// </summary> /// <param name="p">一條商品信息</param> /// <returns></returns> public int Comb_Exist(ProductItemMapCustom p) { p.Replace4MySQL(); tempStr = string.Format("select count(rid) from product_item_map where channel_id={0} and product_id={1} and group_item_id='{2}'", p.channel_id, p.product_id, p.group_item_id); if (p.channel_detail_id != "" && p.channel_detail_id != null) { tempStr += string.Format(" and channel_detail_id='{0}' ", p.channel_detail_id); } return int.Parse(_accessMySql.getDataTable(tempStr).Rows[0][0].ToString()); }
public void Save_Comb(ProductItemMapCustom p) { p.Replace4MySQL(); MySqlCommand Mycmd = new MySqlCommand(string.Format("insert into product_item_map (`channel_id`, `channel_detail_id`, `group_item_id`, `product_name`, `product_cost`, `product_price`,product_id ,price_master_id) values ({0},'{1}','{2}','{3}',{4},{5},{6},{7});select @@identity;", p.channel_id, p.channel_detail_id, p.group_item_id, p.product_name, p.product_cost, p.product_price, p.product_id, p.price_master_id), Mycon); Mycon.Open(); int rid = int.Parse(Mycmd.ExecuteScalar().ToString()); Mycon.Close(); if (p.MapChild.Count > 0) //手動添加 { for (int i = 0, j = p.MapChild.Count; i < j; i++) { tempStr = string.Format("insert into product_map_set(map_rid,item_id,set_num)values({0},{1},{2})", rid, p.MapChild[i].item_id, p.MapChild[i].set_num); _accessMySql.execCommand(tempStr); } } else //匯入對照 { for (int i = 0; i < p.group_item_id.Split(',').Length; i++) { if (p.set_num == 0) { p.set_num = uint.Parse(_accessMySql.getDataTable(string.Format("select s_must_buy from product_combo where parent_id={0} and child_id =(select product_id from product_item where item_id={1})", p.product_id, p.group_item_id.Split(',')[i])).Rows[0][0].ToString()); } tempStr = string.Format("insert into product_map_set(map_rid,item_id,set_num)values({0},{1},{2})", rid, uint.Parse(p.group_item_id.Split(',')[i]), p.set_num); _accessMySql.execCommand(tempStr); } } }