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