//epesformlistinq
internal static DataTable QueryFormList(DataSet bcls_rec, string conn)
{
//程序用变量
string strSql = " SELECT FI.ACLID, FI.NAME, FI.DESCRIPTION,"
+ " (SELECT COUNT(*) AS Expr1 FROM TESGROUPACCESS WHERE ACLID = FI.ACLID AND ACCESSERID in ( [@groupid]) ) AS CNT"
+ " FROM TESFORMRESINFO AS FI"
+ " WHERE (FI.NAME LIKE @ename + '%') AND (FI.DESCRIPTION LIKE @cname+ '%')"
+ " ORDER BY FI.NAME ";
try
{
string ename = bcls_rec.Tables[0].Rows[0]["name"].ToString();
string descript = bcls_rec.Tables[0].Rows[0]["descript"].ToString();
string mode = bcls_rec.Tables[0].Rows[0]["mode"].ToString();
string groupid = bcls_rec.Tables[0].Rows[0]["groupid"].ToString();
string appname = bcls_rec.Tables[0].Rows[0]["appname"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
if (mode == "1")//query group form
{
strSql = strSql.Replace("[@groupid]", groupid);
}
else if (mode == "2")//query user
{
DataSet ds = new DataSet();
ds.Tables.Add();
ds.Tables[0].Columns.Add("memberid");
ds.Tables[0].Rows.Add(groupid);//groupid为userid
DataTable dtGroup = DbUserInfo.QueryGroupByMember(ds, conn);
string groupIdList = "";
for (int i = 0; i < dtGroup.Rows.Count; i++)
{
groupIdList = groupIdList + dtGroup.Rows[i]["ID"].ToString() + ",";
}
groupIdList = groupIdList.TrimEnd(',');
strSql = strSql.Replace("[@groupid]", groupid);
}
else
{
throw new Exception("mode只能是1[根据组]或者2[根据用户]");
}
tree_inqa.SetCommandText(strSql);
tree_inqa.Parameters.Set("ename", ename);
tree_inqa.Parameters.Set("cname", descript);
DataTable dt = new DataTable();
tree_inqa.ExecuteQuery(ref dt);
return(dt);
}
catch (Exception ex)
{
throw ex;
}
}
//epesformauthinq
public static DataTable QueryFormAuth(DataSet bcls_rec, string conn)
{
try
{
string subj = bcls_rec.Tables[0].Rows[0]["id"].ToString();//id
;
string mode = bcls_rec.Tables[0].Rows[0]["mode"].ToString();
string formlist = "";
for (int i = 0; i < bcls_rec.Tables[0].Rows.Count; i++)
{
formlist = formlist + bcls_rec.Tables[0].Rows[i]["formname"].ToString() + ",";
}
formlist = formlist.TrimEnd(',');
CDbCommand tree_inqa = new CDbCommand(conn);
string strSql = " SELECT FORM.ACLID, FORM.NAME FORMNAME,(SELECT COUNT(*) FROM TESGROUPACCESS WHERE ACCESSERID in ([@id]) AND ACLID = FORM.ACLID) FORMCOUNT, "
+ " COALESCE(BUTTON.NAME, ' ') BUTTNAME,COALESCE(BUTTON.ACLID, '0') BUTTID, (SELECT COUNT(*) FROM TESGROUPACCESS WHERE ACCESSERID in ([@id]) AND ACLID =BUTTON.ACLID) BUTTCOUNT"
+ " "
+ "FROM (SELECT BI.ACLID, BI.NAME, BI.FNAME, BI.DESCRIPTION FROM TESBUTTONRESINFO BI) BUTTON"
+ " RIGHT JOIN (SELECT FI.ACLID, FI.NAME, FI.DESCRIPTION FROM TESFORMRESINFO FI)FORM "
+ "ON FORM.NAME = BUTTON.FNAME WHERE FORM.ACLID IN (" + formlist + ") ORDER BY FORMNAME, BUTTNAME ";
if (mode == "1") //根据群组id查按钮
{
//通过群组id查询 button
strSql = strSql.Replace("[@id]", subj);
}
else if (mode == "2")
{
//通过用户id查询 按钮信息
DataSet ds = new DataSet();
ds.Tables.Add();
ds.Tables[0].Columns.Add("memberid");
ds.Tables[0].Rows.Add(subj);//subj为userid
DataTable dtGroup = DbUserInfo.QueryGroupByMember(ds, conn);
if (dtGroup == null || dtGroup.Rows.Count == 0)
{
//不在任何组,返回空表
return(new DataTable());
}
string groupIdList = "";
for (int i = 0; i < dtGroup.Rows.Count; i++)
{
groupIdList = groupIdList + dtGroup.Rows[i]["ID"].ToString() + ",";
}
groupIdList = groupIdList.TrimEnd(',');
strSql = strSql.Replace("[@id]", groupIdList);
}
else
{
throw new Exception("mode只能是1[根据组]或者2[根据用户]");
}
DataTable dt = new DataTable();
tree_inqa.SetCommandText(strSql);
tree_inqa.ExecuteQuery(ref dt);
return(dt);
}
catch (Exception ex)
{
throw ex;
}
}