/// <summary>
/// 查詢product_ext
/// </summary>
/// <param name="pe">查詢條件</param>
/// <returns>符合條件的集合</returns>
public List<ProductExtCustom> Query(int[] ids, ProductExtCustom.Condition condition)
{
StringBuilder sb = new StringBuilder();
try
{
//edit by zhuoqin0830w 2015/10/21 判斷規格是否為空 CONCAT(IF(ps1.spec_name IS NULL,'',ps1.spec_name),' ',IF(ps2.spec_name IS NULL,'',ps2.spec_name)) AS spec_name
sb.Append(@"SELECT p.product_id,p.brand_id,p.product_name,p.prod_sz,pit.item_id,pe.pend_del,pe.cde_dt_shp,pe.pwy_dte_ctl,pe.cde_dt_incr,
pe.cde_dt_var,pe.hzd_ind,pe.cse_wid,pe.cse_wgt,pe.cse_unit,pe.cse_len,pe.cse_hgt,pe.unit_ship_cse,pe.inner_pack_wid,pe.inner_pack_wgt,
pe.inner_pack_unit,pe.inner_pack_len,pe.inner_pack_hgt,CONCAT(IF(ps1.spec_name IS NULL,'',ps1.spec_name),' ',IF(ps2.spec_name IS NULL,'',ps2.spec_name)) AS spec_name FROM product p
INNER JOIN product_item pit ON p.product_id =pit.product_id
LEFT JOIN product_spec ps1 ON ps1.spec_id = pit.spec_id_1
LEFT JOIN product_spec ps2 ON ps2.spec_id = pit.spec_id_2
LEFT JOIN product_ext pe ON pe.item_id=pit.item_id WHERE 1=1"); //add by wwei0216w 添加規格1和規格2查詢
switch (condition)
{
case ProductExtCustom.Condition.ProductId:
sb.AppendFormat(" AND p.product_id in({0}) AND p.product_id > 10000;", string.Join(",", ids));
break;
case ProductExtCustom.Condition.ItemId:
sb.AppendFormat(" AND pit.item_id in({0}) AND p.product_id > 10000;", string.Join(",", ids));
break;
case ProductExtCustom.Condition.BrandId:
sb.AppendFormat(" AND p.brand_id ={0} AND p.product_id > 10000;", string.Join(",", ids));//add by wwei0216w 2015/6/24 只查詢大於10000的商品
break;
}
return _dbAccess.getDataTableForObj<ProductExtCustom>(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductExtDao-->Query" + ex.Message, ex);
}
}
/// <summary>
/// 查詢product_ext
/// </summary>
/// <param name="pe">查詢條件</param>
/// <returns>符合條件的集合</returns>
public List<ProductExtCustom> Query(ProductExtCustom.Condition condition, params int[] ids)
{
try
{
if (ids.Length == 0) //add by wwei0216w 如果ids為空,則返回空
return null;
return pei.Query(ids, condition);
}
catch (Exception ex)
{
throw new Exception("ProductExtMgr-->Query-->" + ex.Message, ex);
}
}
/// <summary>
/// 操作商品細項
/// </summary>
/// <param name="pe">需要操作的商品條件</param>
/// <returns>操作是否成功</returns>
public string UpdateProductExt(ProductExtCustom pe)
{
try
{
StringBuilder sb = new StringBuilder();
///該段代碼由於數據庫表結構的原因,只能delete,insert操作,請勿更改 edit by wwei0216w 2015/6/30
sb.AppendFormat("DELETE FROM product_ext WHERE item_id = {0};", pe.Item_id);
sb.Append("INSERT INTO product_ext(`item_id`,`pend_del`,`cde_dt_shp`,`pwy_dte_ctl`,`cde_dt_incr`,`cde_dt_var`,`hzd_ind`,`cse_wid`,`cse_wgt`,`cse_unit`,`cse_len`,`cse_hgt`,`unit_ship_cse`,`inner_pack_wid`,`inner_pack_wgt`,`inner_pack_unit`,`inner_pack_len`,`inner_pack_hgt`) ");
sb.AppendFormat("VALUES({0},'{1}',{2},'{3}',{4},{5},'{6}',{7},{8},'{9}',", pe.Item_id, pe.Pend_del, pe.Cde_dt_shp, pe.Pwy_dte_ctl, pe.Cde_dt_incr, pe.Cde_dt_var, pe.Hzd_ind, pe.Cse_wid, pe.Cse_wgt, pe.Cse_unit);
sb.AppendFormat("{0},{1},{2},{3},{4},{5},{6},{7}) ", pe.Cse_len, pe.Cse_hgt, pe.Unit_ship_cse, pe.Inner_pack_wid, pe.Inner_pack_wgt, pe.Inner_pack_unit, pe.Inner_pack_len, pe.Inner_pack_wgt);
//array.Add(sb.ToString());
return sb.ToString();
//sb.AppendFormat(@"UPDATE product_ext SET pend_del = '{0}',cde_dt_shp = {1},pwy_dte_ctl = '{2}',cde_dt_incr = {3},",pe.Pend_del,pe.Cde_dt_shp,pe.Pwy_dte_ctl,pe.Cde_dt_incr);
//sb.AppendFormat(@"cde_dt_var = {0},hzd_ind = '{1}',cse_wid={2},cse_wgt={3},",pe.Cde_dt_var,pe.Hzd_ind,pe.Cse_wid,pe.Cse_wgt);
//sb.AppendFormat(@"cse_unit = {0},cse_len = {1},cse_hgt={2},unit_ship_cse={3},inner_pack_wid = {4},", pe.Cse_unit, pe.Cse_len, pe.Cse_hgt, pe.Unit_ship_cse,pe.Inner_pack_wid);
//sb.AppendFormat(@"inner_pack_wgt = {0},inner_pack_unit = {1},inner_pack_len = {2},inner_pack_hgt = {3} ",pe.Inner_pack_wgt,pe.Inner_pack_unit,pe.Inner_pack_len,pe.Inner_pack_hgt);
//sb.AppendFormat(@"WHERE item_id = {0}",pe.Item_id);
//return sb.ToString();
}
catch (Exception ex)
{
throw new Exception("ProductExtDao-->UpdateProductExt" + ex.Message, ex);
}
}
/// <summary>
/// 查詢商品價格歷史記錄
/// </summary>
/// <returns></returns>
//public ActionResult GetPricesHistory(string productID_5, string productID_6, string time_start, string time_end)
//{
// int[] id_5 = (from i in productID_5.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) select int.Parse(i)).ToArray();
// int[] id_6 = (from i in productID_6.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) select int.Parse(i)).ToArray();
// try
// {
// IProductExtImplMgr _prodcutExt = new ProductExtMgr(connectionString);
// return Json(_prodcutExt.QueryHistoryInfo(new ProductExtCustom { Product_id = Convert.ToUInt32(id_5), Item_id = Convert.ToUInt32(id_6) }));
// }
// 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 new EmptyResult();
// }
//}
/// <summary>
/// 歷史記錄匯出方法
/// </summary>
/// <returns></returns>
public ActionResult HistoryExcel(string productID_5, string productID_6, string time_start, string time_end, int brand_id = 0)
{
try
{
string xmlPath = "../XML/ProductItemHistory.xml";
IProductExtImplMgr _productExtMgr = new ProductExtMgr(connectionString);
ProductExtCustom pe = new ProductExtCustom { Product_id = 12306 };
int[] id_5 = (from i in productID_5.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) select int.Parse(i)).ToArray();
int[] id_6 = (from i in productID_6.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) select int.Parse(i)).ToArray();
string productIds = string.Join(",", id_5);
string itemIds = string.Join(",", id_6);
MemoryStream ms = _productExtMgr.OutToExcel(Server.MapPath(xmlPath), Convert.ToInt64(time_start), Convert.ToInt64(time_end), brand_id, itemIds, productIds);
if (ms == null)
{
return new EmptyResult();
}
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 new EmptyResult();
}
}
/// <summary>
/// 刪除結果
/// </summary>
/// <param name="pe">刪除條件</param>
/// <returns>受影響的行數</returns>
public int DeleteProductExtByCondition(ProductExtCustom pe)
{
try
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"DELETE FROM product_ext WHERE item_id = {0}", pe.Item_id);
return _dbAccess.execCommand(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductExtDao.DeleteProductExtByCondition-->" + ex.Message, ex);
}
}
/// <summary>
/// 刪除結果
/// </summary>
/// <param name="pe">刪除條件</param>
/// <returns>受影響的行數</returns>
public int DeleteProductExtByCondition(ProductExtCustom pe)
{
try
{
return pei.DeleteProductExtByCondition(pe);
}
catch (Exception ex)
{
throw new Exception("ProductExtMgr-->DeleteProductExtByCondition-->" + ex.Message, ex);
}
}
