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

        }