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