/// <summary>
/// 查詢語句
/// </summary>
/// <param name="productId">商品id</param>
/// <returns>符合條件的集合</returns>
public List<ProdNameExtendCustom> Query(ProdNameExtendCustom pec,string ids)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT DISTINCT pe.rid, pm.product_id,pm.price_master_id,pm.product_name,pm.site_id,st.site_name,pm.user_level as level_name ,pe.product_prefix,pe.product_suffix,");
sb.Append(@"pm.user_level,pm.user_id,pe.event_start,pe.event_end,pe.kuser,pe.kdate,pe.flag,pu.`type`,pe.apply_id FROM price_master pm ");
sb.Append(" LEFT JOIN site st on st.site_id = pm.site_id ");
sb.Append(" LEFT JOIN product_extend pe ON pm.price_master_id = pe.price_master_id ");
//sb.Append(" LEFT JOIN (select parametername,parametercode from t_parametersrc where parametertype='userlevel') g on pm.user_level=g.parametercode");
sb.Append(" LEFT JOIN price_update_apply_history pu ON pu.apply_id = pe.apply_id AND (pu.`type`=2 OR pu.`type`=3)");
sb.Append(" WHERE 1=1 ");
if (ids != "")
{
sb.AppendFormat(" AND pm.product_id in ({0}) AND (pm.child_id = 0 OR pm.child_id =pm.product_id)", ids);
}
if (pec.Site_Id != 0)
{
sb.AppendFormat(" AND pm.site_id ={0} ", pec.Site_Id);
}
//edit by zhuoqin0830w 2015/05/18
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("UserLevel");
List<ProdNameExtendCustom> list = _access.getDataTableForObj<ProdNameExtendCustom>(sb.ToString());
foreach (ProdNameExtendCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.User_Level.ToString());
if (alist != null)
{
q.Level_Name = alist.parameterName;
}
}
return list;
//return _access.getDataTableForObj<ProdNameExtendCustom>(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("ProdNameExtendDao.Query-->" + ex.Message, ex);
}
}
/// <summary>
/// 查詢語句
/// </summary>
/// <param name="productId">商品id</param>
/// <returns>符合條件的集合</returns>
public List<ProdNameExtendCustom> Query(ProdNameExtendCustom pec, string ids)
{
List<ProdNameExtendCustom> list = peDao.Query(pec, ids);
List<ProdNameExtendCustom> listTemp = new List<ProdNameExtendCustom>();
/// 如果商品前後綴時間過期,則重新添加一個可編輯的行賦予該商品,但是如果商品已在作用中(2)或者出於審核狀態(1)則不進行添加
try
{
//獲取 前一天 時間的時間戳 edit by zhuoqin0830w 2015/05/08
long time = BLL.gigade.Common.CommonFunction.GetPHPTime(Convert.ToString(DateTime.Now)) - 86400;///將當前時間-24小時(既時間戳中86400)
//var prodExts = list.FindAll(p => p.Flag == 1);
//foreach (var item in prodExts) edit by wwei0216w 2015/6/30 將只判斷審核狀態改為所有狀態都判斷,防止其它操作造成的flag狀態錯誤
foreach (var item in list)
{
if (item.Product_Prefix == "" && item.Product_Suffix == "")///前后綴都為空,此種情況可編輯
{
item.Flag = 0;
}
else if ((item.Event_End > time) && ((item.Product_Name.Contains(PriceMaster.L_HKH + item.Product_Prefix + PriceMaster.R_HKH) && item.Product_Prefix.Length > 0) || (item.Product_Name.Contains(PriceMaster.L_HKH + item.Product_Suffix + PriceMaster.R_HKH) && item.Product_Suffix.Length > 0))) //擁有前後綴的情況
{
item.Flag = 2; ///Flag==2 為商品作用中
}
else if (item.Type == 3)///Type為3表示申請被駁回///駁回后商品應該顯示可編輯,所以將flag設置為0
{
item.Flag = 0;///Flag ==0 表示商品可編輯
}
else if (item.Event_End <= time && item.Event_End != 0) ///add by wwei0215/8/25 添加item.Event_End!=0
{
item.Flag = 3;///Flag ==3 表示過期
}
else if (item.Flag == 2)
{
item.Flag = 3;
}
else
{
item.Flag = item.Flag;
}
}
Update(list.Select(p => (ProdNameExtend)p).ToList());
//排除重複的可編輯行
var a = (from obj in list
where obj.Flag == 3
select new
{
obj.Site_Id,
obj.User_Level,
obj.User_Id,
obj.Product_Id,
obj.Site_Name,
obj.Level_Name,
obj.Price_Master_Id,
obj.Product_Name,
obj.Flag
}).Distinct();
foreach (var p in a)
{
ProdNameExtendCustom p_temp = new ProdNameExtendCustom();
p_temp.Product_Id = p.Product_Id;
p_temp.Site_Name = p.Site_Name;
p_temp.Site_Id = p.Site_Id;
p_temp.User_Level = p.User_Level;
p_temp.User_Id = p.User_Id;
p_temp.Level_Name = p.Level_Name;
//刪除 Apply_id 保證 商品名稱過期后只有一行數據
//p_temp.Apply_id = p.Apply_id;
p_temp.Price_Master_Id = p.Price_Master_Id;
p_temp.Product_Name = p.Product_Name;
p_temp.Event_End = 0;
var count = list.Find(m => (m.Flag == 2 || m.Flag == 0 || m.Flag == 1) && m.Price_Master_Id == p.Price_Master_Id);//如果存在作用中的商品就不向前臺傳遞編輯列
if (count == null)
{
listTemp.Add(p_temp); //添加可編輯行
}
}
list.InsertRange(0, listTemp);
list.ForEach(p =>
{
p.Product_Name = ClearPreSuName(ClearPreSuName(p.Product_Name, PriceMaster.L_HKH.ToString(), PriceMaster.R_HKH.ToString()), PriceMaster.L_HKH.ToString(), PriceMaster.R_HKH.ToString());///在前臺顯示時,不需要添加了商品前後綴的名字,所以在這裡去掉前後綴
});
return list;
}
catch (Exception ex)
{
throw new Exception("ProdNameExtendMgr-->Query-->" + ex.Message, ex);
}
}
public ActionResult QueryProdname(ProdNameExtendCustom pron, bool isOverdue)
{
string ids = Request["Ids"];
try
{
int state = 0;//判斷是否需要重新查詢的依據
_prodnameExtendMgr = new ProdNameExtendMgr(connectionString);
IPriceMasterImplMgr pmMgr = new PriceMasterMgr(connectionString);
MySqlDao _mySqlDao = new MySqlDao(connectionString);
ArrayList sqlList = new ArrayList();
List<ProdNameExtendCustom> list = _prodnameExtendMgr.Query(pron, ids);//根據條件查詢商品信息
//判斷過期商品 和 審核商品 中是否存在錯誤數據 edit by zhuoqin0830w 2015/05/07
List<ProdNameExtendCustom> listDelete = list.FindAll(m => m.Flag == 3 || m.Flag == 1); //查詢過程中,為防止未去掉前後綴,卻到期的商品,進行一次遍歷,去掉前後綴 edit by wwei 0216w 2014/12/22
//獲取 前一天 時間的時間戳 edit by zhuoqin0830w 2015/05/07
long time = CommonFunction.GetPHPTime(Convert.ToString(DateTime.Now)) - 86400;
foreach (ProdNameExtendCustom pec in listDelete)
{
pec.Product_Name = pec.Product_Name.Replace(PriceMaster.L_HKH + pec.Product_Prefix + PriceMaster.R_HKH, "").Replace(PriceMaster.L_HKH + pec.Product_Suffix + PriceMaster.R_HKH, "");
PriceMaster p = new PriceMaster();
p.price_master_id = pec.Price_Master_Id;
p.product_name = pec.Product_Name;
switch (pec.Flag)
{
case 1://審核商品
//判斷結束時間 是否 小於 前一天 時間的時間戳 edit by zhuoqin0830w 2015/05/07
if (pec.Event_End <= time)
{
sqlList.Add(pmMgr.UpdateName(p));
state = 1;//如果審核商品中結束時間小於前一天的時間，則賦值為1;
_mySqlDao.ExcuteSqls(sqlList);
break;
}
break;
case 3://過期商品
//如果在過期的商品名稱中能找到前綴後綴~則調用刪除前後綴的方法
if ((pec.Product_Name.Contains(PriceMaster.L_HKH + pec.Product_Prefix + PriceMaster.R_HKH) || pec.Product_Name.Contains(PriceMaster.L_HKH + pec.Product_Suffix + PriceMaster.R_HKH)))
{
_prodnameExtendMgr.ResetExtendName(new Caller());
state = 1;//如果有過期商品包含前後綴,則賦值為1;
break;
}
break;
}
}
if (state == 1)
{
list = _prodnameExtendMgr.Query(pron, ids);//替換掉之前包含前後綴的商品名稱
}
for (int i = 0; i < list.Count; i++)
{
list[i].Key_Id = i;
}//add by wwei0216w 2014/12/30 為key_id賦值
if (isOverdue)//查詢過期
{
list.RemoveAll(p => p.Flag != 3);
}
else //查詢未過期
{
list.RemoveAll(p => p.Flag == 3);
}
return Json(new { item = list });
}
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 Json(new List<ProdNameExtendCustom>());
}