public int Delete(ProductDeliverySet pds,uint[] productIds)
 {
     try
     {
         if (productIds.Length == 0) return 0;
         StringBuilder sb = new StringBuilder();
         sb.AppendFormat(@"set sql_safe_updates=0; DELETE FROM product_delivery_set WHERE product_id in ({0}) and freight_big_area={1} AND  freight_type = {2};set sql_safe_updates=1;"
             ,string.Join(",",productIds), pds.Freight_big_area, pds.Freight_type);
         return _dbAccess.execCommand(sb.ToString());
     }
     catch (Exception ex)
     {
         throw new Exception("ProductDao.DeleteProduct_Delivery-->" + ex.Message, ex);
     }
 }
        public List<ProductDeliverySet> Query(uint[] productIds,ProductDeliverySet deliverySet)
        {
            try
            {
                if (productIds.Length == 0) return new List<ProductDeliverySet>();
                string prodIds = "";
                foreach (uint prodId in productIds)
                {
                    prodIds += prodId + ",";
                }
                string strSql = string.Format("select distinct product_id,freight_big_area,freight_type from product_delivery_set where freight_big_area={0} and freight_type={1} and product_id in({2});",deliverySet.Freight_big_area,deliverySet.Freight_type,prodIds.Remove(prodIds.Length - 1));
                return _dbAccess.getDataTableForObj<ProductDeliverySet>(strSql);

            }
            catch (Exception ex)
            {
                throw new Exception("ProductDao.Query-->" + ex.Message, ex);
            }
        }
 /// <summary>
 /// 下載物流配送模式
 /// </summary>
 /// <param name="delivSet"></param>
 /// <returns></returns>
 public ActionResult DownloadProductDeliverySet(ProductDeliverySet delivSet)
 {
     try
     {
         _prodDeliSetMgr = new ProductDeliverySetMgr(connectionString);
         MemoryStream ms = _prodDeliSetMgr.ExportProdDeliverySet(delivSet);
         return File(ms.ToArray(), "application/-excel", DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls");
     }
     catch (Exception ex)
     {
         Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
         logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
         logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
         log.Error(logMessage);
         return Content("export errer");
     }
 }
 //add by wwei0216w 2015/1/12
 /// <summary>
 /// 根據條件獲得相關product物流設定的信息
 /// </summary>
 /// <param name="deliverySet">Condition</param>
 /// <returns>List<ProductDeliverySetCustom></returns>
 public List<ProductDeliverySetCustom> QueryProductDeliveryByCondition(ProductDeliverySet deliverySet)
 {
     List<ProductDeliverySetCustom> list = new List<ProductDeliverySetCustom>();
     try
     {
         StringBuilder sb = new StringBuilder();
         //添加 品牌  Brand_name   add by zhuoqin0830w  2015/04/24
         //LEFT JOIN (SELECT parameterCode,parameterName,remark,topValue,kdate,kuser FROM t_parametersrc WHERE parameterType='freight_type') t ON t.parameterCode = pd.freight_type 
         sb.Append(@"SELECT pd.product_id,p.product_name,vb.brand_name AS Brand_name,pd.freight_type,pd.freight_type AS Freight_type_name FROM product_delivery_set pd INNER JOIN product p ON pd.product_id = p.product_id");
         sb.Append(@" LEFT JOIN vendor_brand vb ON p.brand_id = vb.brand_id WHERE 1=1 ");
         if (deliverySet.Freight_big_area != 0 && deliverySet.Freight_type != 0)
         {
             sb.AppendFormat(" AND pd.freight_big_area={0} AND pd.freight_type = {1}", deliverySet.Freight_big_area, deliverySet.Freight_type);
             //edit by zhuoqin0830w  2015/05/18
             IParametersrcImplDao _parameterDao = new ParametersrcDao(strConn);
             List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("freight_type");
             list = _dbAccess.getDataTableForObj<ProductDeliverySetCustom>(sb.ToString());
             foreach (ProductDeliverySetCustom q in list)
             {
                 var alist = parameterList.Find(m => m.ParameterType == "freight_type" && m.ParameterCode == q.Freight_type.ToString());
                 if (alist != null)
                 {
                     q.Freight_type_name = alist.parameterName;
                 }
             }
         }
         return list;
     }
     catch (Exception ex)
     {
         throw new Exception("ProductDao.QueryProductDeliveryByCondition-->" + ex.Message, ex);
     }
 }
        public ActionResult QuantityTransportSet(int flag = 1)
        {
            try
            {
                if (!string.IsNullOrEmpty(Request.Form["transport"]) && Request.Files["file"] != null && Request.Files["file"].ContentLength > 0)
                {
                    HttpPostedFileBase excelFile = Request.Files["file"];
                    string filePath = "";
                    if (UpLoadFile(excelFile, excelPath, ref filePath))
                    {
                        _prodDeliSetMgr = new ProductDeliverySetMgr(connectionString);
                        string[] transport = Request.Form["transport"].Split('|');
                        var prodDeliSet = new ProductDeliverySet { Freight_big_area = int.Parse(transport[0]), Freight_type = int.Parse(transport[1]) };

                        switch (flag)
                        {
                            case 1://上傳
                                string resultPath = "";
                                var results = _prodDeliSetMgr.Save(filePath, out resultPath, prodDeliSet);
                                string resultStr = JsonConvert.SerializeObject(results);
                                return Content("{success:true,results:" + resultStr + ",resultPath:\"" + Path.Combine(excelPath, Path.GetFileName(resultPath)) + "\"}");
                            case 2://刪除
                                if (_prodDeliSetMgr.Delete(filePath, prodDeliSet))
                                    return Content("{success:true}");
                                else
                                {
                                    return Content("{success:false,msg:'刪除失敗~'}");
                                }
                        }
                    }
                }
                else
                {
                    return Content("{success:false,msg:'上傳文檔格式錯誤~'}");
                }
                return Content("{success:false}");
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                return Content("{success:false,msg:'程式出錯,請聯繫開發人員~'}");
            }
        }
        public List<ProdDeliverySetImport> Save(string filePath,out string resultPath,ProductDeliverySet deliverySet)
        {
            try
            {
                _productDao = new ProductDao(connectionString);
                List<ProdDeliverySetImport> productAll = ExcelHelperXhf.ReadExcel<ProdDeliverySetImport>(filePath);
                //查詢已經存在的
                List<ProductDeliverySet> existProds = ds.Query(productAll.Select(p => uint.Parse(p.ProductId)).Distinct().ToArray(), deliverySet);

                foreach (var prod in productAll)
                {
                    if (existProds.Find(p => p.Product_id == uint.Parse(prod.ProductId)) != null)
                        prod.Status = 2;//2為存在
                }
                
                List<Product> products = _productDao.Query((from p in productAll where p.Status == 0 select uint.Parse(p.ProductId)).Distinct().ToArray());

                foreach (var prod in productAll.FindAll(p => p.Status == 0))
                {
                    var findProd = products.Find(p => p.Product_Id == uint.Parse(prod.ProductId));
                    if (findProd == null)
                    {
                        prod.Status = 4;//該商品不存在
                    }
                    else if (!findProd.CheckdStoreFreight())
                    {
                        prod.Status = 3;//3為不符合条件
                    }
                }

                //符合條件的
                var fitPords = (from p in productAll
                               where p.Status == 0
                               select new ProductDeliverySet
                                      {
                                          Product_id = int.Parse(p.ProductId),
                                          Freight_big_area = deliverySet.Freight_big_area,
                                          Freight_type = deliverySet.Freight_type
                                      }).Distinct();
                if (fitPords.Count()>0&&Save(fitPords.ToList(), 0))
                {
                    productAll.FindAll(p=>p.Status==0).ForEach(p => p.Status = 1);//1保存成功
                }

                #region 將結果保存到excel
                Dictionary<string, string> columns = new Dictionary<string, string>();
                columns.Add("ProductId", "商品ID");
                columns.Add("BrandName", "品牌名稱");
                columns.Add("ProductName", "商品名稱");
                columns.Add("Msg", "結果");
                
                MemoryStream ms = ExcelHelperXhf.ExportExcel(productAll, columns);
                resultPath=Path.GetDirectoryName(filePath)+"\\result.xls";
                FileStream fs = new FileStream(resultPath, FileMode.Create);
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Dispose(); 
                #endregion

                return productAll;
            }
            catch (Exception ex)
            {

                throw new Exception("ProductDeliverySetMgr-->Save-->" + ex.Message, ex);
            }
        }
 /// <summary>
 /// 刪除物流配送模式
 /// </summary>
 /// <param name="productId">商品id</param>
 /// <returns></returns>
 public bool Delete(ProductDeliverySet prodDeliSet,params uint[] productIds)
 {
     try
     {
         return ds.Delete(prodDeliSet, productIds) >= 0;
     }
     catch (Exception ex)
     {
         throw new Exception("ProductDeliverySetMgr-->Delete-->" + ex.Message, ex);
     }
 }
 public MemoryStream ExportProdDeliverySet(ProductDeliverySet query)
 {
     try
     {
         Dictionary<string, string> columns = new Dictionary<string, string>();
         //edit by zhuoqin0830w  2015/04/24  使頁眉顯示為英文字符
         columns.Add("ProductId", "ProductId");
         columns.Add("BrandName", "BrandName");
         columns.Add("ProductName", "ProductName");
         columns.Add("FreightTypeName", "FreightTypeName");
         List<ProductDeliverySetCustom> items = ds.QueryProductDeliveryByCondition(query);
         //edit by zhuoqin0830w  2015/04/24  通過遍歷循環查出的數據進行賦值
         List<ProdDeliverySetImport> list = new List<ProdDeliverySetImport>()
     {
         new ProdDeliverySetImport{
          ProductId = "商品編號",
          BrandName = "品牌",
          ProductName = "商品名稱",
          FreightTypeName = "物流配送模式"
         }
     };
         foreach (var item in items)
         {
             list.Add(new ProdDeliverySetImport
             {
                 ProductId = item.Product_id.ToString(),
                 BrandName = item.Brand_name,
                 ProductName = item.Product_name,
                 FreightTypeName = item.Freight_type_name,
             });
         }
         return ExcelHelperXhf.ExportExcel(list, columns);
     }
     catch (Exception ex)
     {
         throw new Exception("ProductDeliverySetMgr-->ExportProdDeliverySet-->" + ex.Message, ex);
     }
 }
 /// <summary>
 /// 根據上傳的excel批量刪除配送模式
 /// </summary>
 /// <param name="filePath">excel路徑</param>
 /// <param name="deliverySet">要刪除的配送模式</param>
 /// <returns></returns>
 public bool Delete(string filePath,ProductDeliverySet deliverySet)
 {
     try
     {
         _productDao = new ProductDao(connectionString);
         List<ProdDeliverySetImport> productAll = ExcelHelperXhf.ReadExcel<ProdDeliverySetImport>(filePath);
         return Delete(deliverySet, productAll.Select(p => uint.Parse(p.ProductId)).ToArray());
     }
     catch (Exception ex)
     {
         throw new Exception("ProductDeliverySetMgr-->Delete-->" + ex.Message, ex);
     }
 }