public Sql Append(Sql sql)
{
if (_rhs != null)
_rhs.Append(sql);
else
_rhs = sql;
return this;
}
public Page<OrganGrid> GetPageList(int page = 1, int rows = 30, string typeId = null, string level = null, string organName = null, string parentIds = null, bool haveme = false)
{
var sqlstr = "SELECT o.*,b.dictname typename,d.dictname levelname,e.organname parentname from sys_organ o INNER JOIN sys_dict b on o.typeid=b.dictid INNER JOIN sys_dict d on o.level=d.dictid LEFT JOIN sys_organ e on o.parentid=e.organid";
Sql sql = new Sql(sqlstr);
if (!string.IsNullOrEmpty(typeId))
{
sql.Where("o.typeid=@0", typeId);
}
if (!string.IsNullOrEmpty(level))
{
sql.Where("o.level=@0", level);
}
if (!string.IsNullOrEmpty(parentIds) && !haveme)
{
sql.Where("regexp_split_to_array('" + parentIds + "',',')@> array[o.parentid]::text[]");
}
if (!string.IsNullOrEmpty(parentIds) && haveme)
{
var pids = parentIds.Split(',');
string or = null;
for (int i = 0; i < pids.Length; i++)
{
or += " or o.organid=@" + i;
}
sql.Where("(regexp_split_to_array('" + parentIds + "',',')@> array[o.parentid]::text[]" + or + ")", pids);
}
if (!string.IsNullOrEmpty(organName))
{
sql.Where("o.organname LIKE @0", "%" + organName.Trim() + "%");
}
sql.OrderBy("o.parentid", "o.organid");
return Context.Instance.Page<OrganGrid>(page, rows, sql);
}
public SqlJoinClause(Sql sql)
{
_sql = sql;
}
private void Build(StringBuilder sb, List<object> args, Sql lhs)
{
if (!String.IsNullOrEmpty(_sql))
{
// 添加SQL到字符串中
if (sb.Length > 0)
{
sb.Append("\n");
}
var sql = Database.ProcessParams(_sql, _args, args);
if (Is(lhs, "WHERE ") && Is(this, "WHERE "))//最上层sql对象的lhs为空，所以失败
sql = "AND " + sql.Substring(6);
if (Is(lhs, "ORDER BY ") && Is(this, "ORDER BY "))
sql = ", " + sql.Substring(9);
sb.Append(sql);
}
if (_rhs != null)
_rhs.Build(sb, args, this);
}
static bool Is(Sql sql, string sqltype)
{
return sql != null && sql._sql != null && sql._sql.StartsWith(sqltype, StringComparison.InvariantCultureIgnoreCase);
}
public Core.DBContext.Page<UserGrid> GetPageList(int page = 1, int rows = 30, string username = null, string sex = null, string states = null, string usertype = null, string regionid = null, string organid = null, string roleid = null, DateTime? starttime = null, DateTime? endtime = null)
{
var sqlstr = "SELECT a.*,b.\"name\" regionname,c.\"name\" rolename,array_to_string(ARRAY(select d.organname from sys_organ d WHERE regexp_split_to_array(a.organids,',')@> array[d.organid]::text[]),',') organname FROM sys_userinfo a LEFT JOIN sys_region b ON a.regionid=b.regionid LEFT JOIN sys_role c ON a.roleid=c.roleid";
Sql sql = new Sql(sqlstr);
if (!String.IsNullOrEmpty(username))
{
sql.Where("(a.username Like @0 or a.loginid Like @0 or a.nickname Like @0)", "%" + username.Trim() + "%");
}
if (!String.IsNullOrEmpty(sex))
{
sql.Where("a.sex=@0", sex);
}
if (!String.IsNullOrEmpty(states))
{
sql.Where("a.states=@0", states);
}
if (!String.IsNullOrEmpty(usertype))
{
sql.Where("a.usertype=@0", usertype);
}
if (!String.IsNullOrEmpty(regionid))
{
sql.Where("a.regionid=@0", regionid);
}
if (!String.IsNullOrEmpty(organid))
{
sql.Where("regexp_split_to_array(a.organids,',') @> array[" + organid + "]::text[]");
}
if (!String.IsNullOrEmpty(roleid))
{
sql.Where("a.roleid=@0", roleid);
}
if (starttime != null || endtime != null)
{
if (starttime == null)
starttime = new DateTime(1970, 1, 1);
if (endtime == null)
endtime = DateTime.Now;
sql.Where("a.lastlogintime between '" + starttime + "' and '" + endtime + "'");
}
sql.OrderBy("a.loginid");
return Context.Instance.Page<UserGrid>(page, rows, sql);
}