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