/// <summary>
/// 无分页
/// </summary>
/// <param name="sw">参见模型</param>
/// <returns>参见模型</returns>
public static DataTable getDT(T_SYSSEC_IPSUSER_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM T_SYSSEC_IPSUSER a right outer join T_SYSSEC_USER b on a.userid=b.userid WHERE 1=1");
//针对护林员用户，系统用户表中需要有对应的记录
sb.AppendFormat(" AND b.USERID is not null");
//根据用户名查询
if (string.IsNullOrEmpty(sw.LOGINUSERNAME) == false)
{
//sb.AppendFormat(" AND b.LOGINUSERNAME like '%{0}%'", sw.LOGINUSERNAME);
sb.AppendFormat(" AND b.LOGINUSERNAME = '******'", sw.LOGINUSERNAME);
}
//根据用户序号查询
if (string.IsNullOrEmpty(sw.USERID) == false)
{
if (sw.USERID.Split(',').Length == 1)
{
sb.AppendFormat(" AND b.USERID ={0}", sw.USERID);
}
else
{
sb.AppendFormat(" AND b.USERID in({0})", sw.USERID);
}
}
if (string.IsNullOrEmpty(sw.curOrgNo) == false)
{
sb.AppendFormat(" AND ORGNO = '{0}'", ClsSql.EncodeSql(sw.curOrgNo));
}
if (string.IsNullOrEmpty(sw.DEPARTMENT) == false)
{
sb.AppendFormat(" AND b.DEPARTMENT = '{0}'", ClsSql.EncodeSql(sw.DEPARTMENT));
}
if (!string.IsNullOrEmpty(sw.ORGNO))
{
if (sw.ORGNO.Substring(4, 11) == "00000000000")//获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(b.ORGNO,1,4) = '{0}' or b.ORGNO is null or b.ORGNO='')", ClsSql.EncodeSql(sw.ORGNO.Substring(0, 4)));
}
else if (sw.ORGNO.Substring(6, 9) == "000000000")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(b.ORGNO,1,6) = '{0}' or b.ORGNO is null or b.ORGNO='')", ClsSql.EncodeSql(sw.ORGNO.Substring(0, 6)));
}
else if (sw.ORGNO.Substring(9, 6) == "000000")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(b.ORGNO,1,9 )= '{0}' or b.ORGNO is null or b.ORGNO=''", ClsSql.EncodeSql(sw.ORGNO.Substring(0, 9)));
}
else
{
sb.AppendFormat(" AND b.ORGNO = '{0}'", ClsSql.EncodeSql(sw.ORGNO));
}
}
string sql = "SELECT a.GID, a.SEX, a.PHONE, a.USERJOB" + ",b.USERID, b.ORGNO, b.LOGINUSERNAME, b.USERNAME, b.USERPWD, b.DEPARTMENT"
+ ", b.REGISTERTIME, b.LOGINNUM, b.LOGINIP, b.LASTTIME, b.NOTE,b.LASTOPTIME" + sb.ToString() + " order by b.ORGNO";
DataSet ds = DataBaseClass.FullDataSet(sql);
return(ds.Tables[0]);
}
/// <summary>
/// 获取数据
/// </summary>
/// <returns>参见模型</returns>
public static DataTable getDT(T_SYSSEC_IPSUSER_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT USERID, ORGNO, LOGINUSERNAME, USERNAME, USERPWD, REGISTERTIME, LOGINNUM, LOGINIP,LASTTIME, NOTE, DEPARTMENT");
sb.AppendFormat(" FROM T_SYSSEC_USER a");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.USERID) == false)
{
if (sw.USERID.Split(',').Length > 1)
{
sb.AppendFormat(" AND a.USERID in({0})", ClsSql.EncodeSql(sw.USERID));
}
else
{
sb.AppendFormat(" AND a.USERID ='{0}'", ClsSql.EncodeSql(sw.USERID));
}
}
//sb.AppendFormat(" AND USERID in( {0})",ClsSql.EncodeSql( sw.USERID));
if (string.IsNullOrEmpty(sw.LOGINUSERNAME) == false)
{
sb.AppendFormat(" AND LOGINUSERNAME = '******'", ClsSql.EncodeSql(sw.LOGINUSERNAME));
}
if (string.IsNullOrEmpty(sw.DEPARTMENT) == false)
{
sb.AppendFormat(" AND DEPARTMENT = '{0}'", ClsSql.EncodeSql(sw.DEPARTMENT));
}
if (string.IsNullOrEmpty(sw.curOrgNo) == false)
{
sb.AppendFormat(" AND ORGNO = '{0}'", ClsSql.EncodeSql(sw.curOrgNo));
}
if (!string.IsNullOrEmpty(sw.ORGNO))
{
if (sw.ORGNO.Substring(4, 11) == "00000000000")//获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNO,1,4) = '{0}' or ORGNO is null or ORGNO='')", ClsSql.EncodeSql(sw.ORGNO.Substring(0, 4)));
}
else if (sw.ORGNO.Substring(6, 9) == "000000000")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNO,1,6) = '{0}' or ORGNO is null or ORGNO='')", ClsSql.EncodeSql(sw.ORGNO.Substring(0, 6)));
}
else if (sw.ORGNO.Substring(9, 6) == "000000")//获取所有镇的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNO,1,9)", ClsSql.EncodeSql(sw.ORGNO.Substring(0, 9)));
}
else
{
sb.AppendFormat(" AND ORGNO = '{0}'", ClsSql.EncodeSql(sw.ORGNO));
}
}
sb.AppendFormat(" ORDER BY USERID DESC");
DataSet ds = DataBaseClass.FullDataSet(sb.ToString());
return(ds.Tables[0]);
}
/// <summary>
/// 判断记录是否存在
/// </summary>
/// <param name="sw">参见模型</param>
/// <returns>true存在 false不存在</returns>
public static bool isExists(T_SYSSEC_IPSUSER_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select 1 from T_SYSSEC_IPSUSER where 1=1");
if (string.IsNullOrEmpty(sw.USERID) == false)
{
sb.AppendFormat(" and USERID={0}", sw.USERID);
}
return(DataBaseClass.JudgeRecordExists(sb.ToString()));
}
/// <summary>
/// 返回某用户拥有的权限
/// </summary>
/// <param name="sw">sw.USERID 用户ID</param>
/// <returns>返回有权限的列表，可用匹配，如",001,002,003,"</returns>
public static string getRightStrByUID(T_SYSSEC_IPSUSER_SW sw)
{
DataTable dt = BaseDT.T_SYSSEC_ROLE_RIGHT.getDTByUID(new T_SYSSEC_IPSUSER_SW {
USERID = sw.USERID
});
string str = ",";
for (int i = 0; i < dt.Rows.Count; i++)
{
str += dt.Rows[i]["RIGHTID"].ToString() + ",";
}
return(str);
}
/// <summary>
/// 获取数据
/// </summary>
/// <returns>参见模型</returns>
public static DataTable getDT2(T_SYSSEC_IPSUSER_SW sw, out int total)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM T_SYSSEC_IPSUSER a right outer join T_SYSSEC_USER b on a.userid=b.userid WHERE 1=1");
//针对护林员用户，系统用户表中需要有对应的记录
sb.AppendFormat(" AND b.USERID is not null");
//根据登录用户名查询
if (string.IsNullOrEmpty(sw.LOGINUSERNAME) == false)
{
sb.AppendFormat(" AND b.LOGINUSERNAME like '%{0}%'", sw.LOGINUSERNAME);
}
//根据用户名查询
if (string.IsNullOrEmpty(sw.USERNAME) == false)
{
sb.AppendFormat(" AND b.USERNAME like '%{0}%'", sw.USERNAME);
}
//根据组织机构查询
if (!string.IsNullOrEmpty(sw.ORGNO))
{
sb.AppendFormat(" AND b.ORGNO = '{0}'", ClsSql.EncodeSql(sw.ORGNO));
}
//根据是否开通OA
if (!string.IsNullOrEmpty(sw.IsOpenOA))
{
if (sw.IsOpenOA == "1")
{
sb.AppendFormat(" AND b.IsOpenOA = '{0}'", ClsSql.EncodeSql(sw.IsOpenOA));
}
else
{
sb.AppendFormat(" AND (b.IsOpenOA = '{0}' or b.IsOpenOA is NULL )", ClsSql.EncodeSql(sw.IsOpenOA));
}
}
string sql = "SELECT a.GID,a.SEX, a.PHONE, a.USERJOB"
+ ",b.USERID,b.ORGNO, b.LOGINUSERNAME, b.USERNAME, b.USERPWD, b.REGISTERTIME, b.LOGINNUM, b.LOGINIP, b.LASTTIME, b.NOTE,b.DEPARTMENT,b.IsOpenOA"
+ sb.ToString() + " order by b.ORGNO";
string sqlC = "select count(1) " + sb.ToString();
total = int.Parse(DataBaseClass.ReturnSqlField(sqlC));
sw.curPage = PagerCls.getCurPage(new PagerSW {
curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total
});
DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a");
return(ds.Tables[0]);
}
/// <summary>
/// 返回某用户拥有的权限
/// </summary>
/// <returns>返回DataTable</returns>
public static DataTable getDTByUID(T_SYSSEC_IPSUSER_SW sw)
{
if (string.IsNullOrEmpty(sw.USERID))//如果未传递，则默认值为0，不取权限 该问题有可能为用户未登录，所以未获取到USERID
{
sw.USERID = "0";
}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT ROLEID, RIGHTID");
sb.AppendFormat(" FROM T_SYSSEC_ROLE_RIGHT");
sb.AppendFormat(" where roleid in(select roleid from T_SYSSEC_USER_ROLE where userid={0})", sw.USERID);
sb.AppendFormat(" and RIGHTID in(select RIGHTID from T_SYSSEC_RIGHT where SYSFLAG='{0}')", ConfigCls.getSystemFlag());//判断角色是否启用
sb.AppendFormat(" ORDER BY RIGHTID ");
DataSet ds = DataBaseClass.FullDataSet(sb.ToString());
return(ds.Tables[0]);
}
/// <summary>
/// 获取手机号码字符串组合
/// </summary>
/// <param name="sw"></param>
/// <returns></returns>
public static string getphone(T_SYSSEC_IPSUSER_SW sw)
{
DataTable dt = getDT(sw);
string phone = "";
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i == dt.Rows.Count - 1)
{
phone += dt.Rows[i]["PHONE"].ToString();
}
else
{
phone += dt.Rows[i]["PHONE"].ToString() + ",";
}
}
}
dt.Clear();
dt.Dispose();
return(phone);
}
