public List<ElementDetailQuery> QueryAll(ElementDetailQuery query, out int totalCount) { try { query.Replace4MySQL(); StringBuilder TempCol = new StringBuilder("SELECT p.product_id,ap.element_type,ap.packet_status,p.product_name,p.product_status,ap.packet_name, bd.element_id,ap.element_type,");//c.parametername as element_type_name, TempCol.Append(" bd.element_content,bd.packet_id,bd.element_name,bd.element_link_url,bd.element_link_mode,bd.element_remark,bd.element_sort,bd.element_status, bd.element_link_mode,");//d.parametername as element_linkmode, TempCol.Append(" bd.element_start,bd.element_end,bd.element_createdate,bd.element_updatedate,bd.category_id,bd.category_name,bd.element_img_big "); StringBuilder tempCount = new StringBuilder("select count(bd.element_id) as totalCount "); StringBuilder mainSql = new StringBuilder(" FROM element_detail bd "); mainSql.Append(" join area_packet ap on ap.packet_id=bd.packet_id"); mainSql.Append(" LEFT JOIN product p ON p.product_id = bd.element_content "); // mainSql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_type') c on ap.element_type=c.parametercode"); // mainSql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_link_mode') d on bd.element_link_mode=d.parametercode"); StringBuilder condi = new StringBuilder(); condi.Append(" and element_status!=2 "); if (query.packet_id != 0) { condi.AppendFormat(" and ap.packet_id={0} ", query.packet_id); } if (query.element_type != 0) { condi.AppendFormat(" and ap.element_type={0} ", query.element_type); } if (!string.IsNullOrEmpty(query.key)) { condi.AppendFormat(" and ( bd.element_name like N'%{0}%' ", query.key); condi.AppendFormat(" or bd.element_remark like N'%{0}%' ", query.key); condi.AppendFormat(" or ap.packet_name like N'%{0}%' ", query.key); condi.AppendFormat(" or bd.element_content like N'%{0}%') ", query.key); } if (!string.IsNullOrEmpty(query.searchcate)) { condi.AppendFormat(" and bd.category_name like N'%{0}%' ", query.searchcate); } if (query.product_status != 0 && query.element_type == 3) { if (query.product_status == 5) { condi.Append(" and p.product_status=5 "); } else { condi.AppendFormat(" and p.product_status <> 5 "); } } if (condi.Length > 0) { mainSql.Append(" where "); mainSql.Append(condi.ToString().TrimStart().Remove(0, 3)); } mainSql.AppendFormat(" order by bd.element_id desc "); totalCount = 0; if (query.IsPage) { sql.Append(tempCount.ToString() + mainSql.ToString()); System.Data.DataTable _dt = _access.getDataTable(sql.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]); } mainSql.AppendFormat(" limit {0},{1}", query.Start, query.Limit); } IParametersrcImplDao _paradao = new ParametersrcDao(connStr); List<Parametersrc> parameterList = _paradao.QueryParametersrcByTypes("element_type", "element_link_mode"); List<ElementDetailQuery> list = _access.getDataTableForObj<ElementDetailQuery>(TempCol.ToString() + mainSql.ToString()); foreach (ElementDetailQuery q in list) { var alist = parameterList.Find(m => m.ParameterType == "element_type" && m.ParameterCode == q.element_type.ToString()); var blist = parameterList.Find(m => m.ParameterType == "element_link_mode" && m.ParameterCode == q.element_link_mode.ToString()); if (alist != null) { q.element_type_name = alist.parameterName; } if (blist != null) { q.element_linkmode = blist.parameterName; } } return list; } catch (Exception ex) { throw new Exception("ElementDetailDao-->QueryAll-->" + ex.Message + sql.ToString(), ex); } }
public List<ElementDetailQuery> QueryAllWares(ElementDetailQuery query, out int totalCount) { query.Replace4MySQL(); StringBuilder sql = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); StringBuilder sqlContent = new StringBuilder(); StringBuilder sqlCount = new StringBuilder(); try { sqlCount.AppendFormat(" select count(ed.element_id) as totalCount "); sqlContent.AppendFormat("select p.product_id, c.parametername as element_type,ed.element_id,ed.element_name,ed.category_id,ed.category_name,ed.element_content,ed.element_link_url,d.parametername as element_link_mode,ed.element_sort,ed.element_status,ed.element_start,ed.element_end,ed.element_remark "); sql.Append(" from element_detail ed "); sql.Append(" join area_packet ap on ap.packet_id=ed.packet_id "); sql.Append(" LEFT JOIN product p ON p.product_id = ed.element_content "); sql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_type') c on ap.element_type=c.parametercode"); sql.Append(" left join ( select parametername,parametercode from t_parametersrc where parametertype='element_link_mode') d on ed.element_link_mode=d.parametercode"); sqlWhere.Append(" where ap.element_type=3 "); if (query.packet_id != 0) { sqlWhere.AppendFormat(" and packet_id={0}", query.packet_id); } totalCount = 0; if (query.IsPage) { DataTable _dt = _access.getDataTable(sqlCount.ToString() + sql.ToString() + sqlWhere.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]); } sqlWhere.AppendFormat(" limit {0},{1} ;", query.Start, query.Limit); } return _access.getDataTableForObj<ElementDetailQuery>(sqlContent.ToString() + sql.ToString() + sqlWhere.ToString()); } catch (Exception ex) { throw new Exception("ElementDetailDao-->QueryAllWares-->" + sqlContent.ToString() + sql.ToString() + sqlWhere.ToString(), ex); } }
public int UpdateStatus(ElementDetailQuery model) { model.Replace4MySQL(); try { sql.AppendFormat(@"UPDATE element_detail set element_status='{1}',element_updatedate='{2}' ,update_userid='{3}' where element_id='{0}' ", model.element_id, model.element_status, CommonFunction.DateTimeToString(model.element_updatedate), model.update_userid); return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("ElementDetailDao-->UpdateStatus-->" + ex.Message + sql.ToString(), ex); } }
/// <summary> /// 更改活動使用狀態 /// </summary> /// <returns>數據庫操作結果</returns> public JsonResult UpdateDetailStatus() { string jsonStr = string.Empty; try { _detailMgr = new ElementDetailMgr(mySqlConnectionString); int id = Convert.ToInt32(Request.Params["id"]); int statusValue = Convert.ToInt32(Request.Params["status"]); ElementDetailQuery model = new ElementDetailQuery(); model.element_id = id; model.element_status = statusValue; model.update_userid = int.Parse((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString()); model.element_updatedate = DateTime.Now; if (_detailMgr.UpdateStatus(model) > 0) { return Json(new { success = "true" }); } else { return Json(new { 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 Json(new { success = "false" }); } }
public HttpResponseBase GetDetailList() { string json = string.Empty; try { List<ElementDetailQuery> detailStore = new List<ElementDetailQuery>(); ElementDetailQuery query = new ElementDetailQuery(); #region 獲取query對象數據 query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25"); if (!string.IsNullOrEmpty(Request.Params["product_status"])) { query.product_status = uint.Parse(Request.Params["product_status"]); query.element_type = 3; } if (!string.IsNullOrEmpty(Request.Params["packet_id"])) { query.packet_id = int.Parse(Request.Params["packet_id"]); } if (!string.IsNullOrEmpty(Request.Params["serchcontent"])) { query.key = Request.Params["serchcontent"].Trim(); } if (!string.IsNullOrEmpty(Request.Params["searchCate"])) { query.searchcate = Request.Params["searchCate"].Trim(); } if (!string.IsNullOrEmpty(Request.Params["search_type"])) { query.element_type = int.Parse(Request.Params["search_type"]); } #endregion _detailMgr = new ElementDetailMgr(mySqlConnectionString); query.create_userid = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; int totalCount = 0; detailStore = _detailMgr.QueryAll(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; foreach (var item in detailStore) { if (item.element_type == 1) { if (item.element_content != "") { item.element_content = imgServerPath + ElementPath + item.element_content; } if (item.element_img_big != "") { item.element_img_big = imgServerPath + ElementPath + item.element_img_big; } else { item.element_img_big = defaultImg; } } if (item.element_type == 2) { if (item.element_content != "") { item.kendo_editor = Server.HtmlDecode(Server.HtmlDecode(item.element_content)); } } if (item.element_type == 3) { if (item.element_img_big != "") { item.element_img_big = imgServerPath + ElementPath + item.element_img_big; } else { item.element_img_big = defaultImg; } } if (item.category_name != "") { item.category_name = Server.HtmlDecode(Server.HtmlDecode(item.category_name)); } } json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(detailStore, Formatting.Indented, timeConverter) + "}";//返回json數據 } 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); json = "{success:false,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public List<ElementDetailQuery> QueryAllWares(ElementDetailQuery query, out int totalCount) { try { return _detaildao.QueryAllWares(query,out totalCount); } catch (Exception ex) { throw new Exception("ElementDetailMgr-->QueryAllWares-->" + ex.Message, ex); } }