/// <summary> /// 查询GroupAuthMap的所有数据,或根据条件查询 /// </summary> /// <param name="m">查询的条件</param> /// <returns>List</returns> public List<GroupAuthMapQuery> QueryAll(GroupAuthMapQuery m, out int totalCount) { StringBuilder sbSql = new StringBuilder(); sbSql.AppendFormat(@"select content_id,table_name,table_alias_name,column_name,`value`,`status`,gm.group_id,groupName from group_auth_map gm "); sbSql.AppendFormat(@"left JOIN t_fgroup on gm.group_id=t_fgroup.rowid where 1=1 "); //sbSql.Append(@" where 1=1 "); if (m.group_id != 0) { sbSql.AppendFormat(" and gm.group_id='{0}' ", m.group_id); } if (!string.IsNullOrEmpty(m.table_name)) { sbSql.AppendFormat(" and gm.table_name like '%{0}%' ", m.table_name); } sbSql.Append(@" order by content_id desc"); totalCount = 0; string sql = "select COUNT(gm.content_id) as search_total from group_auth_map gm "; if (m.IsPage) { System.Data.DataTable _dt = _access.getDataTable(sql); if (_dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["search_total"]); } sbSql.AppendFormat(" limit {0},{1}", m.Start, m.Limit); } try { return _access.getDataTableForObj<GroupAuthMapQuery>(sbSql.ToString()); } catch (Exception ex) { throw new Exception("GroupAuthMapDao-->QueryAll-->" + ex.Message + sbSql.ToString(), ex); } }
public string Query(GroupAuthMapQuery query) { DataTable dt = _groupAuthMapImplDao.Query(query); StringBuilder sbResult = new StringBuilder(); string column = String.Empty; for (int i = 0; i < dt.Rows.Count; i++) { if (0 == i) { sbResult.AppendFormat(" and ({0}.{1}='{2}' ", dt.Rows[i]["table_alias_name"].ToString(), dt.Rows[i]["column_name"].ToString(), dt.Rows[i]["value"].ToString()); } else { if (column == dt.Rows[i]["column_name"].ToString()) { sbResult.AppendFormat(" or {0}.{1}='{2}' ", dt.Rows[i]["table_alias_name"].ToString(), dt.Rows[i]["column_name"].ToString(), dt.Rows[i]["value"].ToString()); } else { sbResult.Append(")"); sbResult.AppendFormat(" and( {0}.{1}='{2}' ", dt.Rows[i]["table_alias_name"].ToString(), dt.Rows[i]["column_name"].ToString(), dt.Rows[i]["value"].ToString()); } } column = dt.Rows[i]["column_name"].ToString(); if (i == dt.Rows.Count - 1) { sbResult.Append(")"); } } return sbResult.ToString(); }
public HttpResponseBase AddAuthMap() { string json = string.Empty; try { GroupAuthMapQuery query = new GroupAuthMapQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 _groupAuthMap = new GroupAuthMapMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["group_id"])) { query.group_id = Convert.ToInt32(Request.Params["group_id"]); } if (!string.IsNullOrEmpty(Request.Params["table_name"])) { query.table_name = Request.Params["table_name"]; } if (!string.IsNullOrEmpty(Request.Params["table_alias_name"])) { query.table_alias_name = Request.Params["table_alias_name"]; } if (!string.IsNullOrEmpty(Request.Params["column_name"])) { query.column_name= Request.Params["column_name"]; } if (!string.IsNullOrEmpty(Request.Params["value"])) { query.value = Request.Params["value"]; } if(!string.IsNullOrEmpty(Request.Params["content_id"]))//修改 { query.content_id=Convert.ToInt32(Request.Params["content_id"]); _groupAuthMap.UpGroupAuthMapQuery(query); } else//添加 { query.create_date = DateTime.Now; query.create_user_id =(System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString(); _groupAuthMap.AddGroupAuthMapQuery(query); } IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; json = "{success:true,msg:\"" + "" + "\"}"; } 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:true,msg:\"" + ex.Message + "\"}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public int UpGroupAuthMapQuery(GroupAuthMapQuery query) { try { return _groupAuthMapImplDao.UpGroupAuthMapQuery(query); } catch (Exception ex) { throw new Exception("GroupAuthMapMgr-->UpGroupAuthMapQuery-->" + ex.Message, ex); } }
public List<GroupAuthMapQuery> QueryAll(GroupAuthMapQuery m, out int totalCount) { try { return _groupAuthMapImplDao.QueryAll(m, out totalCount); } catch (Exception ex) { throw new Exception("GroupAuthMapMgr-->QueryAll-->" + ex.Message, ex); } }
/// <summary> /// chaojie_zz添加於2014/10/14 /// </summary> /// <param name="site"></param> /// <param name="totalCount"></param> #region site站臺列表 /// <summary> /// site站臺列表 /// </summary> /// <param name="site"></param> /// <param name="totalCount"></param> /// <returns>List</returns> public List<SiteQuery> QuerryAll(SiteQuery site, out int totalCount) { StringBuilder stb = new StringBuilder(); StringBuilder sqlCondi = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); site.Replace4MySQL(); try { sqlCondi.AppendFormat(@"select site.site_id,site.site_name,site.domain,site.cart_delivery,st.site_name as csitename,site.online_user,"); sqlCondi.AppendFormat(@"site.max_user,site.page_location,site.site_status,site.site_createdate,site.site_updatedate,site.create_userid,site.update_userid "); stb.AppendFormat(@"from site left join site st on site.cart_delivery=st.site_id "); GroupAuthMapQuery groupAuthMapModel = new GroupAuthMapQuery(); groupAuthMapModel.table_name = "site"; groupAuthMapModel.user_id = site.create_userid; stb.Append(_groupAuthMapMgr.Query(groupAuthMapModel)); //if (site.site_id != 0) //{ // strcondition.AppendFormat(@" and site.site_id='{0}' ", site.site_id); //} if (!string.IsNullOrEmpty(site.site_name)) { sqlWhere.AppendFormat(" and site.site_name like N'%{0}%' ", site.site_name); } totalCount = 0; if (sqlWhere.Length != 0) { stb.Append(" WHERE "); stb.Append(sqlWhere.ToString().TrimStart().Remove(0, 3)); } if (site.IsPage) { System.Data.DataTable _dt = _access.getDataTable(" select count(site.site_id) as totalcount " + stb.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalcount"]); } stb.AppendFormat(" order by site.site_id desc limit {0},{1}", site.Start, site.Limit); } return _access.getDataTableForObj<SiteQuery>(sqlCondi.ToString() + stb.ToString()); } catch (Exception ex) { throw new Exception("SiteDao-->QuerryAll-->" + ex.Message + sqlCondi.ToString() + stb.ToString(), ex); } }
/// <summary> ///权限设定列表 /// </summary> /// <returns>Store</returns> public HttpResponseBase GroupAuthMapList() { List<GroupAuthMapQuery> stores = new List<GroupAuthMapQuery>(); string json = string.Empty; try { GroupAuthMapQuery query = new GroupAuthMapQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 _groupAuthMap = new GroupAuthMapMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["group_id"])) { query.group_id = Convert.ToInt32(Request.Params["group_id"]); } if (!string.IsNullOrEmpty(Request.Params["table_name"])) { query.table_name = Request.Params["table_name"]; } int totalCount = 0; stores = _groupAuthMap.QueryAll(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(stores, 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; }
/// <summary> ///查詢GroupAuthMap數據 /// </summary> /// <param name="m"></param> /// <returns></returns> /// public DataTable Query(GroupAuthMapQuery m) { StringBuilder sbSql = new StringBuilder(); StringBuilder sbKeySql = new StringBuilder(); try { sbSql.AppendFormat(@"select mu.user_id,gam.table_name,gam.column_name,gam.value,gam.table_alias_name from group_auth_map gam "); sbSql.AppendFormat(" LEFT JOIN t_groupcaller t_g on t_g.groupId= gam.group_id LEFT JOIN manage_user mu on mu.user_email=t_g.callid "); sbSql.AppendFormat(" where gam.table_name='{0}' and mu.user_id={1} and status=1 ", m.table_name, m.user_id); if (!String.IsNullOrEmpty(m.column_name)) { sbSql.AppendFormat("and gam.column_name={0}", m.column_name); } sbSql.Append(" order by gam.column_name"); return _access.getDataTable(sbSql.ToString()); } catch (Exception ex) { throw new Exception("GroupAuthMapDao-->Query-->" + ex.Message + sbSql.ToString(), ex); } }
public List<Site> GetSite(Site query) { query.Replace4MySQL(); StringBuilder sql = new StringBuilder(); sql.AppendFormat("select site_id,site_name from site where site_status=1 "); GroupAuthMapQuery groupAuthMapModel = new GroupAuthMapQuery(); groupAuthMapModel.table_name = "site"; groupAuthMapModel.user_id = query.Create_Userid; sql.Append(_groupAuthMapMgr.Query(groupAuthMapModel)); try { return _access.getDataTableForObj<Site>(sql.ToString()); } catch (Exception ex) { throw new Exception("SiteDao-->GetSite-->" + ex.Message + sql.ToString(), ex); } }
/// <summary> /// 新增GroupAuthMap /// </summary> /// <param name="query">实体类</param> /// <returns>返回受影响行数</returns> public int AddGroupAuthMapQuery(GroupAuthMapQuery query) { StringBuilder sbSql = new StringBuilder(); try { sbSql.AppendFormat(@"insert into group_auth_map(group_id,table_name,column_name,`value`,"); sbSql.AppendFormat("`status`,create_user_id,create_date,table_alias_name)VALUES("); sbSql.AppendFormat(" '{0}','{1}','{2}','{3}',", query.group_id, query.table_name, query.column_name, query.value); sbSql.AppendFormat("'{0}','{1}','{2}','{3}')", query.status, query.create_user_id, query.create_date.ToString("yyyy-MM-dd HH:mm:ss"), query.table_alias_name); return _access.execCommand(sbSql.ToString()); } catch (Exception ex) { throw new Exception("GroupAuthMapDao-->AddGroupAuthMapQuery-->" + ex.Message + sbSql.ToString(), ex); } }
/// <summary> /// 修改GroupAuthMap /// </summary> /// <param name="query">实体</param> /// <returns></returns> public int UpGroupAuthMapQuery(GroupAuthMapQuery query) { StringBuilder sbSql = new StringBuilder(); try { sbSql.AppendFormat(@"update group_auth_map set group_id='{0}',table_name='{1}',column_name='{2}', ", query.group_id, query.table_name, query.column_name); sbSql.AppendFormat("value='{0}',table_alias_name='{1}' where content_id='{2}'", query.value, query.table_alias_name, query.content_id); return _access.execCommand(sbSql.ToString()); } catch (Exception ex) { throw new Exception("GroupAuthMapDao-->UpGroupAuthMapQuery-->" + ex.Message + sbSql.ToString(), ex); } }