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