/// <summary>
/// 查询按钮
/// </summary>
/// <param name="bcls_rec"></param>
/// <param name="conn"></param>
/// <returns></returns>
public static DataTable QueryButtonInfo(DataSet bcls_rec, string conn)
{
//程序用变量
string strFName = "";
string strSql = " SELECT BUTT.NAME, BUTT.FNAME, BUTT.ACLID, BUTT.DESCRIPTION, BUTT.OPTYPE, BUTT.APPNAME "
+ " FROM TESBUTTONRESINFO BUTT "
+ " WHERE FNAME =@fname "
+ " ORDER BY name ASC";
try
{
strFName = bcls_rec.Tables[0].Rows[0]["fname"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
tree_inqa.SetCommandText(strSql);
tree_inqa.Parameters.Set("fname", strFName);
DataTable dt = new DataTable();
tree_inqa.ExecuteQuery(ref dt);
return(dt);
}
catch (Exception ex)
{
return(null);
}
}
public static DataTable QueryUserInfo(DataSet bcls_rec, string conn)
{
//程序用变量
string strSql = " select * from tesuserinfo where cname like @cname +'%'";
try
{
string cname = "";
if (bcls_rec.Tables.Count > 0 && bcls_rec.Tables[0].Rows.Count > 0)
{
cname = bcls_rec.Tables[0].Rows[0][0].ToString();
}
CDbCommand tree_inqa = new CDbCommand(conn);
tree_inqa.SetCommandText(strSql);
tree_inqa.Parameters.Set("cname", cname);
DataTable dt = new DataTable();
tree_inqa.ExecuteQuery(ref dt);
return(dt);
}
catch (Exception ex)
{
return(null);
}
}
internal static void UpdateButtonAccess(DataSet bcls_rec, string conn)
{
//程序用变量
string strDeleteSql = " delete from TESGROUPACCESS where aclid = @buttonid and ACCESSERID = @groupid ";
string strInsertSql = " INSERT INTO TESGROUPACCESS(ACLID, ACCESSERID, ACCESSERTYPE, ACCESSCODE) "
+ " VALUES( @buttonid, @groupid, 2, 5) ";
try
{
for (int i = 0; i < bcls_rec.Tables[0].Rows.Count; i++)
{
//string ename = bcls_rec.Tables[0].Rows[i]["username"].ToString();
string mode = bcls_rec.Tables[0].Rows[i]["mode"].ToString();
string buttonid = bcls_rec.Tables[0].Rows[i]["buttonid"].ToString();
string groupid = bcls_rec.Tables[0].Rows[i]["groupid"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
if (mode == "insert")
{
tree_inqa.SetCommandText(strInsertSql);
}
else if (mode == "delete")
{
tree_inqa.SetCommandText(strDeleteSql);
}
tree_inqa.Parameters.Set("buttonid", buttonid);
tree_inqa.Parameters.Set("groupid", groupid);
tree_inqa.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable QueryGroupInfo(DataSet bcls_rec, string conn)
{
//程序用变量
try
{
string groupname = bcls_rec.Tables[0].Rows[0]["groupname"].ToString();
string adminuser = bcls_rec.Tables[0].Rows[0]["adminuser"].ToString();
string userid = bcls_rec.Tables[0].Rows[0]["userid"].ToString();
string i_appname = bcls_rec.Tables[0].Rows[0]["appname"].ToString();
string grouptype = "1";// bcls_rec.Tables[0].Rows[0]["grouptype"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
tree_inqa.SetCommandText("select ID,NAME,GROUPDESCRIPTION FROM TESGROUPINFO where name like @groupname+'%' and grouptype = @grouptype");
tree_inqa.Parameters.Set("groupname", groupname);
//tree_inqa.Parameters.Set("adminuser", adminuser);
// tree_inqa.Parameters.Set("i_appname", i_appname);
tree_inqa.Parameters.Set("grouptype", grouptype);
DataTable dt = new DataTable();
tree_inqa.ExecuteQuery(ref dt);
return(dt);
}
catch (Exception ex)
{
return(null);
}
}
public static string InsertGroupChildUser(DataSet bcls_rec, string conn)
{
//获得输入参数
string groupid = bcls_rec.Tables[0].Rows[0]["groupid"].ToString();
string groupname = bcls_rec.Tables[0].Rows[0]["groupname"].ToString();
/*建立连接*/
CDbCommand cmd = new CDbCommand(conn);
//对输入信息循环处理
for (int i = 0; i < bcls_rec.Tables[1].Rows.Count; i++)
{
string userid = bcls_rec.Tables[1].Rows[i]["userid"].ToString();
string username = bcls_rec.Tables[1].Rows[i]["username"].ToString();
//判断该群组下是否已存在该用户
cmd.SetCommandText(" select COUNT(*) FROM TESGROUPMEMBER WHERE memberid = @memberid and groupid = @groupid and membertype = 1 "
);
cmd.Parameters.Set("memberid", userid);
cmd.Parameters.Set("groupid", groupid);
int num = Convert.ToInt32(cmd.ExecuteScalar());
if (num > 0)
{
string msg = "群组[" + groupname + "]下已存在子用户[" + username + "]!";
throw new Exception(msg);
}
cmd.SetCommandText(" INSERT INTO TESGROUPMEMBER(memberid,groupid,membertype) "
+ " VALUES (@userid, @groupid, 1) ");
cmd.Parameters.Set("userid", userid);
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
}
return("ok");
}
public static string DeleteGroupMember(DataSet bcls_rec, string conn)
{
string groupid = bcls_rec.Tables[0].Rows[0]["groupid"].ToString();
CDbCommand cmd = new CDbCommand(conn);
//删除子组
for (int i = 0; i < bcls_rec.Tables[1].Rows.Count; i++)
{
//取得单行传入信息
string subgroupid = bcls_rec.Tables[1].Rows[i][0].ToString();
cmd.SetCommandText("DELETE FROM TESGROUPMEMBER "
+ " WHERE MEMBERID = @subgroupid AND GROUPID = @groupid AND MEMBERTYPE= 2"
);
cmd.Parameters.Set("subgroupid", subgroupid);
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
}
//删除子用户
for (int i = 0; i < bcls_rec.Tables[2].Rows.Count; i++)
{
//取得单行传入信息
string subuserid = bcls_rec.Tables[2].Rows[i][0].ToString();
cmd.SetCommandText("DELETE FROM TESGROUPMEMBER WHERE MEMBERID = @subuserid "
+ " AND GROUPID = @groupid AND MEMBERTYPE=1 "
);
cmd.Parameters.Set("subuserid", subuserid);
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
}
return("ok");
}
public static string DeleteGroupMember(DataSet bcls_rec, string conn)
{
string groupid = bcls_rec.Tables[0].Rows[0]["groupid"].ToString();
CDbCommand cmd = new CDbCommand(conn);
//删除子组
for (int i = 0; i < bcls_rec.Tables[1].Rows.Count; i++)
{
//取得单行传入信息
string subgroupid = bcls_rec.Tables[1].Rows[i][0].ToString();
cmd.SetCommandText("DELETE FROM TESGROUPMEMBER "
+ " WHERE MEMBERID = @subgroupid AND GROUPID = @groupid AND MEMBERTYPE= 2"
);
cmd.Parameters.Set("subgroupid", subgroupid);
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
}
//删除子用户
for (int i = 0; i < bcls_rec.Tables[2].Rows.Count; i++)
{
//取得单行传入信息
string subuserid = bcls_rec.Tables[2].Rows[i][0].ToString();
cmd.SetCommandText("DELETE FROM TESGROUPMEMBER WHERE MEMBERID = @subuserid "
+ " AND GROUPID = @groupid AND MEMBERTYPE=1 "
);
cmd.Parameters.Set("subuserid", subuserid);
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
}
return "ok";
}
/// <summary>
/// 查询窗体
/// </summary>
/// <param name="bcls_rec"></param>
/// <param name="conn"></param>
/// <returns></returns>
public static DataTable QueryFormInfo(DataSet bcls_rec, string conn)
{
//程序用变量
string strName = "";
string strdllName = "";
string strAppname = "";
string strSql = " SELECT FORM.NAME, FORM.DESCRIPTION, FORM.DLLNAME, CAST(FORM.ACLID AS VARCHAR) AS ACLID, FORM.ABBREV, FORM.ICONNUM, FORM.FORM_CALL_MODE, FORM.APPNAME, FORM.DLLPATH "
+ " FROM TESFORMRESINFO FORM "
+ " WHERE "
+ " NAME like @name+'%' and dllname like @dllname+'%' " //and appname= @cursystem "
+ " ORDER BY name ASC ";
try
{
strName = bcls_rec.Tables[0].Rows[0]["name"].ToString();
strdllName = bcls_rec.Tables[0].Rows[0]["dllname"].ToString();
strAppname = bcls_rec.Tables[0].Rows[0]["appname"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
tree_inqa.SetCommandText(strSql);
tree_inqa.Parameters.Set("name", strName);
tree_inqa.Parameters.Set("dllname", strdllName);
DataTable dt = new DataTable();
tree_inqa.ExecuteQuery(ref dt);
return dt;
}
catch (Exception ex)
{
return null;
}
}
public static DataTable QueryGroupByMember(DataSet bcls_rec, string conn)
{
//程序用变量
try
{
string memberid = bcls_rec.Tables[0].Rows[0]["memberid"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
string sqlMemGroup = " WITH GROUP_MEMBER_N(memberid, groupid) AS"
+ " ( SELECT memberid, groupid FROM TESGROUPMEMBER"
+ " WHERE memberid IN ( SELECT GROUPID FROM TESGROUPMEMBER WHERE memberid = @memberid )"
+ " union all "
+ " SELECT np1.memberid, np1.groupid FROM GROUP_MEMBER_N n,TESGROUPMEMBER np1 WHERE n.groupid= np1.memberid"
+ " )"
+ " select * from tesgroupinfo where id in ("
+ " SELECT distinct groupid FROM GROUP_MEMBER_N"
+ " UNION ALL"
+ " SELECT GROUPID FROM TESGROUPMEMBER WHERE memberid = @memberid) ";
tree_inqa.SetCommandText(sqlMemGroup);
tree_inqa.Parameters.Set("memberid", memberid);
DataTable dt = new DataTable();
tree_inqa.ExecuteQuery(ref dt);
return(dt);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 查询按钮
/// </summary>
/// <param name="bcls_rec"></param>
/// <param name="conn"></param>
/// <returns></returns>
public static DataTable QueryButtonInfo(DataSet bcls_rec, string conn)
{
//程序用变量
string strFName = "";
string strSql = " SELECT BUTT.NAME, BUTT.FNAME, BUTT.ACLID, BUTT.DESCRIPTION, BUTT.OPTYPE, BUTT.APPNAME "
+ " FROM TESBUTTONRESINFO BUTT "
+ " WHERE FNAME =@fname "
+ " ORDER BY name ASC";
try
{
strFName = bcls_rec.Tables[0].Rows[0]["fname"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
tree_inqa.SetCommandText(strSql);
tree_inqa.Parameters.Set("fname", strFName);
DataTable dt = new DataTable();
tree_inqa.ExecuteQuery(ref dt);
return dt;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 查询窗体
/// </summary>
/// <param name="bcls_rec"></param>
/// <param name="conn"></param>
/// <returns></returns>
public static DataTable QueryFormInfo(DataSet bcls_rec, string conn)
{
//程序用变量
string strName = "";
string strdllName = "";
string strAppname = "";
string strSql = " SELECT FORM.NAME, FORM.DESCRIPTION, FORM.DLLNAME, CAST(FORM.ACLID AS VARCHAR) AS ACLID, FORM.ABBREV, FORM.ICONNUM, FORM.FORM_CALL_MODE, FORM.APPNAME, FORM.DLLPATH "
+ " FROM TESFORMRESINFO FORM "
+ " WHERE "
+ " NAME like @name+'%' and dllname like @dllname+'%' " //and appname= @cursystem "
+ " ORDER BY name ASC ";
try
{
strName = bcls_rec.Tables[0].Rows[0]["name"].ToString();
strdllName = bcls_rec.Tables[0].Rows[0]["dllname"].ToString();
strAppname = bcls_rec.Tables[0].Rows[0]["appname"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
tree_inqa.SetCommandText(strSql);
tree_inqa.Parameters.Set("name", strName);
tree_inqa.Parameters.Set("dllname", strdllName);
DataTable dt = new DataTable();
tree_inqa.ExecuteQuery(ref dt);
return(dt);
}
catch (Exception ex)
{
return(null);
}
}
//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;
}
}
/// <summary>
///
/// </summary>
/// <param name="userID"></param>
/// <param name="formName"></param>
/// <param name="conn"></param>
public static DataTable QueryAccess(int userID, string formName, string conn)
{
try
{
CDbCommand tree_inqa = new CDbCommand(conn);
string strSql = "";
//通过用户id查询 按钮信息
strSql = " WITH GROUP_MEMBER_N(memberid, groupid) "
+ "AS"
+ " ( SELECT memberid, groupid FROM TESGROUPMEMBER"
+ " WHERE memberid IN ( SELECT GROUPID"
+ " FROM TESGROUPMEMBER WHERE MEMBERID = @id"
+ " )"
+ " UNION ALL "
+ " SELECT np1.memberid, np1.groupid FROM GROUP_MEMBER_N n, TESGROUPMEMBER np1"
+ " WHERE n.groupid = np1.memberid"
+ " ) "
+ " SELECT BI.NAME, BI.DESCRIPTION,'BUTTON' type, (SELECT COUNT(*) FROM TESGROUPACCESS"
+ " WHERE ACLID = BI.ACLID AND ACCESSERID IN"
+ " ( SELECT DISTINCT groupid FROM GROUP_MEMBER_N "
+ " UNION ALL"
+ " SELECT GROUPID FROM TESGROUPMEMBER WHERE memberid = @id "
+ " ) "
+ " ) as NUM"
+ " FROM TESBUTTONRESINFO BI "
+ " WHERE BI.FNAME = @formname "
+ " UNION ALL "
+ " SELECT FI.NAME, FI.DESCRIPTION,'FORM' type, (SELECT COUNT(*) FROM TESGROUPACCESS"
+ " WHERE ACLID = FI.ACLID AND ACCESSERID IN"
+ " ( SELECT DISTINCT groupid FROM GROUP_MEMBER_N "
+ " UNION ALL"
+ " SELECT GROUPID FROM TESGROUPMEMBER WHERE memberid = @id "
+ " ) "
+ " ) as NUM"
+ " FROM TESFORMRESINFO FI "
+ " WHERE FI.NAME = @formname ";
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(strSql))
{
tree_inqa.SetCommandText(strSql);
tree_inqa.Parameters.Set("id", userID);
tree_inqa.Parameters.Set("formname", formName);
// tree_inqa.Parameters.Set("appname", i_appname);
tree_inqa.ExecuteQuery(ref dt);
return(dt);
}
return(dt);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
///
/// </summary>
/// <param name="userID"></param>
/// <param name="formName"></param>
/// <param name="conn"></param>
public static DataTable QueryAccess(int userID, string formName, string conn)
{
try
{
CDbCommand tree_inqa = new CDbCommand(conn);
string strSql = "";
//通过用户id查询 按钮信息
strSql = " WITH GROUP_MEMBER_N(memberid, groupid) "
+ "AS"
+ " ( SELECT memberid, groupid FROM TESGROUPMEMBER"
+ " WHERE memberid IN ( SELECT GROUPID"
+ " FROM TESGROUPMEMBER WHERE MEMBERID = @id"
+ " )"
+ " UNION ALL "
+ " SELECT np1.memberid, np1.groupid FROM GROUP_MEMBER_N n, TESGROUPMEMBER np1"
+ " WHERE n.groupid = np1.memberid"
+ " ) "
+ " SELECT BI.NAME, BI.DESCRIPTION,'BUTTON' type, (SELECT COUNT(*) FROM TESGROUPACCESS"
+ " WHERE ACLID = BI.ACLID AND ACCESSERID IN"
+ " ( SELECT DISTINCT groupid FROM GROUP_MEMBER_N "
+ " UNION ALL"
+ " SELECT GROUPID FROM TESGROUPMEMBER WHERE memberid = @id "
+ " ) "
+ " ) as NUM"
+ " FROM TESBUTTONRESINFO BI "
+ " WHERE BI.FNAME = @formname "
+ " UNION ALL "
+ " SELECT FI.NAME, FI.DESCRIPTION,'FORM' type, (SELECT COUNT(*) FROM TESGROUPACCESS"
+ " WHERE ACLID = FI.ACLID AND ACCESSERID IN"
+ " ( SELECT DISTINCT groupid FROM GROUP_MEMBER_N "
+ " UNION ALL"
+ " SELECT GROUPID FROM TESGROUPMEMBER WHERE memberid = @id "
+ " ) "
+ " ) as NUM"
+ " FROM TESFORMRESINFO FI "
+ " WHERE FI.NAME = @formname ";
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(strSql))
{
tree_inqa.SetCommandText(strSql);
tree_inqa.Parameters.Set("id", userID);
tree_inqa.Parameters.Set("formname", formName);
// tree_inqa.Parameters.Set("appname", i_appname);
tree_inqa.ExecuteQuery(ref dt);
return dt;
}
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public static string InsertGroupChildGroup(DataSet bcls_rec, string conn)
{
string groupid = bcls_rec.Tables[0].Rows[0][0].ToString();
bool deadLoop = false;
CDbCommand tesgroupmember_q = new CDbCommand(conn);
for (int i = 0; i < bcls_rec.Tables[1].Rows.Count; i++)
{
//取得单行传入信息
string cgroupid = bcls_rec.Tables[1].Rows[i][0].ToString();
tesgroupmember_q.SetCommandText("WITH n(memberid,groupid,membertype) AS "
+ " (SELECT memberid,groupid,membertype "
+ " FROM TESGROUPMEMBER WHERE GROUPID in(@groupid,@cgroupid) "
+ " UNION ALL "
+ " SELECT nplus1.memberid, nplus1.groupid, nplus1.membertype "
+ " FROM TESGROUPMEMBER as nplus1, n "
+ " WHERE n.memberid = nplus1.groupid ) "
+ " SELECT top 2 memberid,groupid,membertype FROM n WHERE membertype = 2 "
);
tesgroupmember_q.Parameters.Set("cgroupid", cgroupid);
tesgroupmember_q.Parameters.Set("groupid", groupid);
DataTable dtTmp = new DataTable();
tesgroupmember_q.ExecuteQuery(ref dtTmp);
if (dtTmp != null && dtTmp.Rows.Count > 0)
{
string lmemberid = dtTmp.Rows[0][0].ToString();
string lgroupid = dtTmp.Rows[0][1].ToString();
if (lmemberid == groupid || lmemberid == cgroupid)
{
deadLoop = true;
}
}
if (cgroupid == groupid)
{
deadLoop = true;
}
if (deadLoop)
{
string msg = "插入的子组号 [{0}]将引起数据库中组挂组的循环,无法插入数据库/[{1}";
throw new Exception(msg);
}
else
{
tesgroupmember_q.SetCommandText(" INSERT INTO TESGROUPMEMBER(memberid,groupid,membertype) "
+ " VALUES (@cgroupid, @groupid, '2' ) ");
tesgroupmember_q.Parameters.Set("cgroupid", cgroupid);
tesgroupmember_q.Parameters.Set("groupid", groupid);
tesgroupmember_q.ExecuteNonQuery();
}
}
return("");
}
public static string InsertGroupChildGroup(DataSet bcls_rec, string conn)
{
string groupid = bcls_rec.Tables[0].Rows[0][0].ToString();
bool deadLoop = false;
CDbCommand tesgroupmember_q = new CDbCommand(conn);
for (int i = 0; i < bcls_rec.Tables[1].Rows.Count; i++)
{
//取得单行传入信息
string cgroupid = bcls_rec.Tables[1].Rows[i][0].ToString();
tesgroupmember_q.SetCommandText("WITH n(memberid,groupid,membertype) AS "
+ " (SELECT memberid,groupid,membertype "
+ " FROM TESGROUPMEMBER WHERE GROUPID in(@groupid,@cgroupid) "
+ " UNION ALL "
+ " SELECT nplus1.memberid, nplus1.groupid, nplus1.membertype "
+ " FROM TESGROUPMEMBER as nplus1, n "
+ " WHERE n.memberid = nplus1.groupid ) "
+ " SELECT top 2 memberid,groupid,membertype FROM n WHERE membertype = 2 "
);
tesgroupmember_q.Parameters.Set("cgroupid", cgroupid);
tesgroupmember_q.Parameters.Set("groupid", groupid);
DataTable dtTmp = new DataTable();
tesgroupmember_q.ExecuteQuery(ref dtTmp);
if (dtTmp != null && dtTmp.Rows.Count > 0)
{
string lmemberid = dtTmp.Rows[0][0].ToString();
string lgroupid = dtTmp.Rows[0][1].ToString();
if (lmemberid == groupid || lmemberid == cgroupid)
{
deadLoop = true;
}
}
if (cgroupid == groupid)
{
deadLoop = true;
}
if (deadLoop)
{
string msg = "插入的子组号 [{0}]将引起数据库中组挂组的循环,无法插入数据库/[{1}";
throw new Exception(msg);
}
else
{
tesgroupmember_q.SetCommandText(" INSERT INTO TESGROUPMEMBER(memberid,groupid,membertype) "
+ " VALUES (@cgroupid, @groupid, '2' ) ");
tesgroupmember_q.Parameters.Set("cgroupid", cgroupid);
tesgroupmember_q.Parameters.Set("groupid", groupid);
tesgroupmember_q.ExecuteNonQuery();
}
}
return "";
}
public static DataSet QueryGroupChild(DataSet bcls_rec, string conn)
{
try
{
//获得输入参数
string groupid = bcls_rec.Tables[0].Rows[0]["groupid"].ToString();
CDbCommand group_inq = new CDbCommand(conn);
string sql = " SELECT U.ID, U.NAME, U.GROUPDESCRIPTION FROM TESGROUPINFO U "
+ "WHERE U.ID IN(SELECT MEMBERID FROM TESGROUPMEMBER "
+ " WHERE MEMBERTYPE = 2 AND GROUPID= @groupid )";
group_inq.SetCommandText(sql);
group_inq.Parameters.Set("groupid", groupid);
DataTable dtGroup = new DataTable();
group_inq.ExecuteQuery(ref dtGroup);
//返回子用户
CDbCommand user_inq = new CDbCommand(conn);
string cmdText = " SELECT U.ID, U.ENAME, U.CNAME FROM TESUSERINFO U "
+ " WHERE U.ID IN(SELECT MEMBERID FROM TESGROUPMEMBER "
+ " WHERE MEMBERTYPE = 1 AND GROUPID= @groupid )";
user_inq.SetCommandText(cmdText);
user_inq.Parameters.Set("groupid", groupid);
DataTable dtUser = new DataTable();
user_inq.ExecuteQuery(ref dtUser);
DataSet ds = new DataSet();
ds.Tables.Add("Group");
ds.Tables.Add("Users");
ds.Tables[0].Merge(dtGroup);
ds.Tables[1].Merge(dtUser);
return(ds);
}
catch (Exception ex)
{
throw ex;
}
}
internal static int AddTreeNode(System.Data.DataSet bcls_rec, string conn)
{
//先判断 是否会形成死循环(节点嵌套)
//然后判断名称是否重复
//进行新增
//获得输入参数
string groupid = bcls_rec.Tables[0].Rows[0]["groupid"].ToString();
string groupname = bcls_rec.Tables[0].Rows[0]["groupname"].ToString();
/*建立连接*/
CDbCommand cmd = new CDbCommand(conn);
//对输入信息循环处理
for (int i = 0; i < bcls_rec.Tables[1].Rows.Count; i++)
{
string userid = bcls_rec.Tables[1].Rows[i]["userid"].ToString();
string username = bcls_rec.Tables[1].Rows[i]["username"].ToString();
//判断该群组下是否已存在该用户
cmd.SetCommandText(" select COUNT(*) FROM TESGROUPMEMBER WHERE memberid = @memberid and groupid = @groupid and membertype = 1 "
);
cmd.Parameters.Set("memberid", userid);
cmd.Parameters.Set("groupid", groupid);
int num = Convert.ToInt32(cmd.ExecuteScalar());
if (num > 0)
{
string msg = "群组[" + groupname + "]下已存在子用户[" + username + "]!";
throw new Exception(msg);
}
cmd.SetCommandText(" INSERT INTO TESGROUPMEMBER(memberid,groupid,membertype) "
+ " VALUES (@userid, @groupid, 1) ");
cmd.Parameters.Set("userid", userid);
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
}
return 0;
}
//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;
}
}
//epesbuttauthinq
public static DataTable QueryButtonAuth(DataSet bcls_rec, string conn)
{
try
{
string subj = bcls_rec.Tables[0].Rows[0]["id"].ToString();
string i_formname = bcls_rec.Tables[0].Rows[0]["formid"].ToString();
string mode = bcls_rec.Tables[0].Rows[0]["mode"].ToString();
string i_appname = bcls_rec.Tables[0].Rows[0]["appname"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
string strSql = "";
if (mode == "1") //根据群组id查 and form name query button
{
//通过群组id查询 button
strSql = " SELECT BI.ACLID, BI.NAME, BI.DESCRIPTION, "
+ "(SELECT COUNT(*) FROM TESGROUPACCESS WHERE ACLID = BI.ACLID AND ACCESSERID = @id ) as num"
+ " FROM TESBUTTONRESINFO BI "
+ " WHERE BI.FNAME in (select name from tesformresinfo where aclid= @formname) ORDER BY BI.NAME ";
}
else if (mode == "2")
{
//通过用户id查询 按钮信息
strSql=" WITH GROUP_MEMBER_N(memberid, groupid) "
+"AS"
+" ( SELECT memberid, groupid FROM TESGROUPMEMBER"
+" WHERE memberid IN ( SELECT GROUPID"
+" FROM TESGROUPMEMBER WHERE MEMBERID = @id"
+" )"
+" UNION ALL "
+" SELECT np1.memberid, np1.groupid FROM GROUP_MEMBER_N n, TESGROUPMEMBER np1"
+" WHERE n.groupid = np1.memberid"
+" ) "
+ " SELECT BI.ACLID, BI.NAME, BI.DESCRIPTION, (SELECT COUNT(*) FROM TESGROUPACCESS"
+" WHERE ACLID = BI.ACLID AND ACCESSERID IN"
+" ( SELECT DISTINCT groupid FROM GROUP_MEMBER_N "
+" UNION ALL"
+" SELECT GROUPID FROM TESGROUPMEMBER WHERE memberid = @id "
+" ) "
+" ) as NUM"
+" FROM TESBUTTONRESINFO BI "
+ " WHERE BI.FNAME in (select name from tesformresinfo where aclid= @formname) "
+" ORDER BY BI.NAME ";
}
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(strSql))
{
tree_inqa.SetCommandText(strSql);
tree_inqa.Parameters.Set("id", subj);
tree_inqa.Parameters.Set("formname", i_formname);
tree_inqa.Parameters.Set("appname", i_appname);
tree_inqa.ExecuteQuery(ref dt);
return dt;
}
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public static string SaveGroupInfo(DataSet bcls_rec, string conn)
{
string msg = "ok";
int fetchRowCount = 0;
try
{
//CDbCommand cmdForm(conn);
CDbCommand cmd = new CDbCommand(conn);
string name = "";
string descript = "";
string appName = "";
string groupType = "1";
string sqlInsertGroup = " INSERT INTO [TESGROUPINFO]"
+ " ([NAME],[GROUPDESCRIPTION],[GROUPTYPE]"
+ " ,[APPNAME] )"
+ " VALUES"
+ " (@name,@groupdescription,@grouptype "
+ " ,@appname ) ";
string sqlUpdGroup = " UPDATE [TESGROUPINFO]"
+ " SET [NAME] = @name,[GROUPDESCRIPTION] = @description"
+ " ,[GROUPTYPE] = @grouptype,[APPNAME] =@appname"
+ " "
+ " WHERE id = @id ";
// 新增
int blkIns = bcls_rec.Tables.IndexOf("INSERT_BLOCK");
if (blkIns >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkIns].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["name"].ToString();
descript = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["groupdescription"].ToString();
// appName = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["appname"].ToString();
//判断群组名是否重复
cmd.SetCommandText(" select COUNT(*) FROM TESGROUPINFO WHERE NAME = @groupname and APPNAME = @appname ");
cmd.Parameters.Set("groupname", name);
cmd.Parameters.Set("appname", appName);
object obj = cmd.ExecuteScalar();
if (Convert.ToInt32(obj) > 0)
{
msg = "输入的群组名[" + name + "]已存在，请重新输入！";
throw new Exception(msg);
}
cmd.SetCommandText(sqlInsertGroup);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("groupdescription", descript);
cmd.Parameters.Set("grouptype", groupType);
cmd.Parameters.Set("appname", appName);
cmd.ExecuteNonQuery();
}
}
// 删除
int blkDel = bcls_rec.Tables.IndexOf("DELETE_BLOCK");
if (blkDel >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkDel].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkDel].Rows[fetchRowCount]["name"].ToString();
string groupid = bcls_rec.Tables[blkDel].Rows[fetchRowCount]["id"].ToString();
if (name == "usermanager" || name == "groupmanager" || name == "admingroup")
{
//if ( userid != "admin")
//{
// msg = "系统群组只有admin超级管理员才能操作！";
// throw new Exception(msg);
//}
}
//删除组下所有能否访问的资源
cmd.SetCommandText(" delete FROM TESGROUPACCESS WHERE ACCESSERID = @groupid ");
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
//删除所有父组是该组的记录
cmd.SetCommandText("delete FROM TESGROUPMEMBER WHERE GROUPID = @groupid ");
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
//删除所有子组是该组的记录
cmd.SetCommandText(" delete FROM TESGROUPMEMBER WHERE MEMBERID = @groupid ");
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
//删除组信息表中的记录
cmd.SetCommandText("delete from TESGROUPINFO where id = @groupid ");
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
}
}
// 修改
int blkUpd = bcls_rec.Tables.IndexOf("UPDATE_BLOCK");
if (blkUpd >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkUpd].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["name"].ToString();
//if (name == "usermanager" || name == "groupmanager" || name == "admingroup")
//{
//if ( userid != "admin")
//{
// msg = "系统群组只有admin超级管理员才能操作！";
// throw new Exception(msg);
//}
//}
descript = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["groupdescription"].ToString();
int aclid = Convert.ToInt32(bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["id"]);
//判断画面是否存在
cmd.SetCommandText(" SELECT COUNT(*) FROM TESGROUPINFO WHERE NAME = @name and APPNAME = @appname AND ID != @aclid ");
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("aclid", aclid);
cmd.Parameters.Set("appname", appName);
int formCount = Convert.ToInt32(cmd.ExecuteScalar());
if (formCount > 0)
{
msg = "操作失败！修改的画面已存在！";
throw new Exception(msg);
}
//更新群组信息表
cmd.SetCommandText(sqlUpdGroup);
cmd.Parameters.Set("id", aclid);
cmd.Parameters.Set("description", descript);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("appname", appName);
cmd.Parameters.Set("grouptype", groupType);
cmd.Parameters.Set("appname", appName);
cmd.ExecuteNonQuery();
}
}
msg = "处理成功。";
}
catch (Exception ex)
{
throw ex;
}
return(msg);
}
/// <summary>
/// 保存窗体
/// </summary>
/// <param name="bcls_rec"></param>
/// <param name="conn"></param>
/// <returns></returns>
public static string SaveFormInfo(DataSet bcls_rec, string conn)
{
string msg = "ok";
int fetchRowCount = 0;
try
{
//CDbCommand cmdForm(conn);
CDbCommand cmd = new CDbCommand(conn);
string name = "";
string descript = "";
string dllName = "";
string abbrev = "";
string fromCallMode = "0";
string appName = "";
string dllPath = "";
string sqlInsertFrm = "INSERT INTO [TESFORMRESINFO] "
+ " ([NAME],[DESCRIPTION],[DLLNAME] ,[ABBREV],[ICONNUM] ,[FORM_CALL_MODE],[APPNAME] ,[DLLPATH])"
+ " VALUES "
+ " (@name, @description, @dllname, @abbrev,@iconnum,@form_call_mode,@appname,@dllpath)";
string sqlDel = "delete from TESFORMRESINFO where aclid = @aclid";
string sqlDelBtnAuthByFrm = " delete FROM TESGROUPACCESS "
+ " WHERE ACLID in (select ACLID from TESBUTTONRESINFO "
+ " WHERE FNAME = @name ) ";
string sqlDelFrmAuth = " delete FROM TESGROUPACCESS where ACLID = @aclid ";
string sqlQueryBtn = " SELECT ACLID,NAME,DESCRIPTION FROM TESBUTTONRESINFO WHERE FNAME = @name";
string sqlDelBtnAuthById = " DELETE FROM TESGROUPACCESS WHERE ACLID = @aclid ";
string sqlDelBtn = "DELETE FROM TESBUTTONRESINFO WHERE ACLID = @aclid ";
string sqlQuerySameFrm = " SELECT COUNT(*) FROM TESFORMRESINFO WHERE NAME = @name AND ACLID != @aclid ";
string sqlUpdFrm = "UPDATE [TESFORMRESINFO]"
+ " SET [NAME] = @NAME,[DESCRIPTION]=@DESCRIPTION,[DLLNAME] = @DLLNAME "
+ " ,[ABBREV] = @ABBREV,[ICONNUM] = @ICONNUM,[FORM_CALL_MODE] = @FORM_CALL_MODE "
+ " ,[APPNAME] = @APPNAME ,[DLLPATH] = @DLLPATH "
+ " WHERE aclid = @aclid";
string sqlUpdBtnFname = " update TESBUTTONRESINFO set fname = @fname where fname=(select top 1 name from TESFORMRESINFO where aclid=@aclid) ";
// 新增
int blkIns = bcls_rec.Tables.IndexOf("INSERT_BLOCK");
if (blkIns >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkIns].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["name"].ToString();
descript = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["description"].ToString();
dllName = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["dllname"].ToString();
abbrev = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["abbrev"].ToString();
dllPath = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["dllpath"].ToString();
if (null != bcls_rec.Tables[blkIns].Rows[fetchRowCount]["form_call_mode"])
{
fromCallMode = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["form_call_mode"].ToString();
}
//判断画面名是否重复
cmd.SetCommandText(" select COUNT(*) FROM TESFORMRESINFO WHERE NAME = @name ");
cmd.Parameters.Set("name", name);
object obj = cmd.ExecuteScalar();
if (Convert.ToInt32(obj) > 0)
{
msg = "输入的画面名[" + name + "]已存在，请重新输入！";
throw new Exception(msg);
}
cmd.SetCommandText(sqlInsertFrm);
cmd.Parameters.Set("description", descript);
cmd.Parameters.Set("dllname", dllName);
cmd.Parameters.Set("abbrev", abbrev);
cmd.Parameters.Set("iconnum", 0);
cmd.Parameters.Set("form_call_mode", fromCallMode);
cmd.Parameters.Set("appname", appName);
cmd.Parameters.Set("dllpath", dllPath);
cmd.ExecuteNonQuery();
}
}
// 删除
int blkDel = bcls_rec.Tables.IndexOf("DELETE_BLOCK");
if (blkDel >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkDel].Rows.Count; ++fetchRowCount)
{
int aclidFrm = Convert.ToInt32(bcls_rec.Tables[blkDel].Rows[fetchRowCount]["aclid"]);
cmd.SetCommandText(sqlDel);
cmd.Parameters.Set("aclid", aclidFrm);
int delNum = cmd.ExecuteNonQuery();
if (delNum > 0)
{
//删除画面下所有按钮的授权信息
cmd.SetCommandText(sqlDelBtnAuthByFrm);
cmd.Parameters.Set("name", name);
cmd.ExecuteNonQuery();
//删除授权信息中所有该画面的信息
cmd.SetCommandText(sqlDelFrmAuth);
cmd.Parameters.Set("aclid", aclidFrm);
cmd.ExecuteNonQuery();
//删除画面下所有的按钮
cmd.SetCommandText(sqlQueryBtn);
cmd.Parameters.Set("name", name);
DataTable dtBtnInfo = new DataTable();
cmd.ExecuteQuery(ref dtBtnInfo);
for (int i = 0; i < dtBtnInfo.Rows.Count; i++)
{
int aclidBtn = Convert.ToInt32(dtBtnInfo.Rows[i]["aclid"]);
//删除按钮授权信息
cmd.SetCommandText(sqlDelBtnAuthById);
cmd.Parameters.Set("aclid", aclidBtn);
cmd.ExecuteNonQuery();
//从按钮表删除
cmd.SetCommandText(sqlDelBtn);
cmd.ExecuteNonQuery();
}
}
}
}
// 修改
int blkUpd = bcls_rec.Tables.IndexOf("UPDATE_BLOCK");
if (blkUpd >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkUpd].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["name"].ToString();
descript = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["description"].ToString();
dllName = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["dllname"].ToString();
abbrev = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["abbrev"].ToString();
dllPath = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["dllpath"].ToString();
if (null != bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["form_call_mode"])
{
fromCallMode = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["form_call_mode"].ToString();
}
int aclidFrm = Convert.ToInt32(bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["aclid"]);
//判断画面是否存在
cmd.SetCommandText(sqlQuerySameFrm);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("aclid", aclidFrm);
int formCount = Convert.ToInt32(cmd.ExecuteScalar());
if (formCount > 0)
{
msg = "操作失败！修改的画面已存在！";
throw new Exception(msg);
}
//更新画面名时更新按钮表的fname原画面名
cmd.SetCommandText(sqlUpdBtnFname);
cmd.Parameters.Set("aclid", aclidFrm);
cmd.Parameters.Set("fname", name);
cmd.ExecuteNonQuery();
//更新画面信息表
cmd.SetCommandText(sqlUpdFrm);
cmd.Parameters.Set("description", descript);
cmd.Parameters.Set("dllname", dllName);
cmd.Parameters.Set("abbrev", abbrev);
cmd.Parameters.Set("iconnum", 0);
cmd.Parameters.Set("form_call_mode", fromCallMode);
cmd.Parameters.Set("appname", appName);
cmd.Parameters.Set("dllpath", dllPath);
cmd.Parameters.Set("aclid", aclidFrm);
cmd.ExecuteNonQuery();
}
}
msg = "处理成功。";
}
catch (Exception ex)
{
msg = ex.Message + "[" + ex.StackTrace + "]";
}
return msg;
}
/// <summary>
/// 保存按钮
/// </summary>
/// <param name="bcls_rec"></param>
/// <param name="conn"></param>
/// <returns></returns>
public static string SaveButtonInfo(DataSet bcls_rec, string conn)
{
string msg = "ok";
int fetchRowCount = 0;
try
{
CDbCommand cmd = new CDbCommand(conn);
string name = "";
string fname = "";
string descript = "";
string optype = "";
string appName = "";
string sqlQueryBtn = " select COUNT(*) FROM TESBUTTONRESINFO WHERE NAME = @name and FNAME = @fname ";
string sqlInsertBtn = "INSERT INTO [TESBUTTONRESINFO] "
+ " ([NAME],[FNAME],[DESCRIPTION],[OPTYPE] ,[APPNAME])"
+ " VALUES "
+ " (@name,@fname, @description, @optype,@appname)";
string sqlDelBtn = "DELETE FROM TESBUTTONRESINFO WHERE ACLID = @aclid ";
string sqlDelBtnAuthById = " DELETE FROM TESGROUPACCESS WHERE ACLID = @aclid ";
string sqlQuerySameBtn = " SELECT COUNT(*) FROM TESBUTTONRESINFO WHERE NAME = @name and FNAME = @fname AND ACLID != @aclid ";
string sqlUpdBtn = "UPDATE [TESFORMRESINFO]"
+ " SET [NAME] = @name,[DESCRIPTION]=@description,[FNAME] = @fanme , "
+ " [OPTYPE] = @optype, [APPNAME] = @APPNAME "
+ " WHERE aclid = @aclid";
// 新增
int blkIns = bcls_rec.Tables.IndexOf("INSERT_BLOCK");
if (blkIns >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkIns].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["name"].ToString();
descript = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["description"].ToString();
fname = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["fname"].ToString();
optype = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["optype"].ToString();
appName = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["appName"].ToString();
//判断画面名是否重复
cmd.SetCommandText(sqlQueryBtn);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("fname", fname);
object obj = cmd.ExecuteScalar();
if (Convert.ToInt32(obj) > 0)
{
msg = "输入的按钮名[" + name + "]在画面["+fname+"]已存在，请重新输入！";
throw new Exception(msg);
}
cmd.SetCommandText(sqlInsertBtn);
cmd.Parameters.Set("description", descript);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("fname", fname);
cmd.Parameters.Set("optype", optype);
cmd.Parameters.Set("appname", appName);
cmd.ExecuteNonQuery();
}
}
// 删除
int blkDel = bcls_rec.Tables.IndexOf("DELETE_BLOCK");
if (blkDel >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkDel].Rows.Count; ++fetchRowCount)
{
int aclidBtn = Convert.ToInt32(bcls_rec.Tables[blkDel].Rows[fetchRowCount]["aclid"]);
cmd.SetCommandText(sqlDelBtn);
cmd.Parameters.Set("aclid", aclidBtn);
int delNum = cmd.ExecuteNonQuery();
if (delNum > 0)
{
//删除按钮授权信息
cmd.SetCommandText(sqlDelBtnAuthById);
cmd.Parameters.Set("aclid", aclidBtn);
cmd.ExecuteNonQuery();
//从按钮表删除
cmd.SetCommandText(sqlDelBtn);
cmd.ExecuteNonQuery();
}
}
}
// 修改
int blkUpd = bcls_rec.Tables.IndexOf("UPDATE_BLOCK");
if (blkUpd >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkUpd].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["name"].ToString();
descript = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["description"].ToString();
fname = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["fname"].ToString();
optype = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["optype"].ToString();
appName = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["appname"].ToString();
int aclidBtn = Convert.ToInt32(bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["aclid"]);
//判断画面是否存在
cmd.SetCommandText(sqlQuerySameBtn);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("fname", fname);
cmd.Parameters.Set("aclid", aclidBtn);
int formCount = Convert.ToInt32(cmd.ExecuteScalar());
if (formCount > 0)
{
msg = "操作失败！修改的按钮名已存在！";
throw new Exception(msg);
}
//更新画面信息表
cmd.SetCommandText(sqlUpdBtn);
cmd.Parameters.Set("description", descript);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("fname", fname);
cmd.Parameters.Set("optype", optype);
cmd.Parameters.Set("appname", appName);
cmd.Parameters.Set("aclid", aclidBtn);
cmd.ExecuteNonQuery();
}
}
msg = "处理成功。";
}
catch (Exception ex)
{
msg = ex.Message + "[" + ex.StackTrace + "]";
}
return msg;
}
public static string SaveGroupInfo(DataSet bcls_rec, string conn)
{
string msg = "ok";
int fetchRowCount = 0;
try
{
//CDbCommand cmdForm(conn);
CDbCommand cmd = new CDbCommand(conn);
string name = "";
string descript = "";
string appName = "";
string groupType = "1";
string sqlInsertGroup = " INSERT INTO [TESGROUPINFO]"
+" ([NAME],[GROUPDESCRIPTION],[GROUPTYPE]"
+" ,[APPNAME] )"
+" VALUES"
+" (@name,@groupdescription,@grouptype "
+" ,@appname ) ";
string sqlUpdGroup = " UPDATE [TESGROUPINFO]"
+ " SET [NAME] = @name,[GROUPDESCRIPTION] = @description"
+ " ,[GROUPTYPE] = @grouptype,[APPNAME] =@appname"
+ " "
+ " WHERE id = @id ";
// 新增
int blkIns = bcls_rec.Tables.IndexOf("INSERT_BLOCK");
if (blkIns >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkIns].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["name"].ToString();
descript = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["groupdescription"].ToString();
// appName = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["appname"].ToString();
//判断群组名是否重复
cmd.SetCommandText(" select COUNT(*) FROM TESGROUPINFO WHERE NAME = @groupname and APPNAME = @appname " );
cmd.Parameters.Set("groupname", name);
cmd.Parameters.Set("appname", appName);
object obj = cmd.ExecuteScalar();
if (Convert.ToInt32(obj) > 0)
{
msg = "输入的群组名[" + name + "]已存在，请重新输入！";
throw new Exception(msg);
}
cmd.SetCommandText(sqlInsertGroup);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("groupdescription", descript);
cmd.Parameters.Set("grouptype", groupType);
cmd.Parameters.Set("appname", appName);
cmd.ExecuteNonQuery();
}
}
// 删除
int blkDel = bcls_rec.Tables.IndexOf("DELETE_BLOCK");
if (blkDel >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkDel].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkDel].Rows[fetchRowCount]["name"].ToString();
string groupid = bcls_rec.Tables[blkDel].Rows[fetchRowCount]["id"].ToString();
if (name == "usermanager" || name == "groupmanager" || name == "admingroup")
{
//if ( userid != "admin")
//{
// msg = "系统群组只有admin超级管理员才能操作！";
// throw new Exception(msg);
//}
}
//删除组下所有能否访问的资源
cmd.SetCommandText(" delete FROM TESGROUPACCESS WHERE ACCESSERID = @groupid ");
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
//删除所有父组是该组的记录
cmd.SetCommandText("delete FROM TESGROUPMEMBER WHERE GROUPID = @groupid ");
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
//删除所有子组是该组的记录
cmd.SetCommandText(" delete FROM TESGROUPMEMBER WHERE MEMBERID = @groupid ");
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
//删除组信息表中的记录
cmd.SetCommandText("delete from TESGROUPINFO where id = @groupid ");
cmd.Parameters.Set("groupid", groupid);
cmd.ExecuteNonQuery();
}
}
// 修改
int blkUpd = bcls_rec.Tables.IndexOf("UPDATE_BLOCK");
if (blkUpd >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkUpd].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["name"].ToString();
//if (name == "usermanager" || name == "groupmanager" || name == "admingroup")
//{
//if ( userid != "admin")
//{
// msg = "系统群组只有admin超级管理员才能操作！";
// throw new Exception(msg);
//}
//}
descript = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["groupdescription"].ToString();
int aclid = Convert.ToInt32(bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["id"]);
//判断画面是否存在
cmd.SetCommandText(" SELECT COUNT(*) FROM TESGROUPINFO WHERE NAME = @name and APPNAME = @appname AND ID != @aclid ");
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("aclid", aclid);
cmd.Parameters.Set("appname", appName);
int formCount = Convert.ToInt32(cmd.ExecuteScalar());
if (formCount > 0)
{
msg = "操作失败！修改的画面已存在！";
throw new Exception(msg);
}
//更新群组信息表
cmd.SetCommandText(sqlUpdGroup);
cmd.Parameters.Set("id", aclid);
cmd.Parameters.Set("description", descript);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("appname", appName);
cmd.Parameters.Set("grouptype", groupType);
cmd.Parameters.Set("appname", appName);
cmd.ExecuteNonQuery();
}
}
msg = "处理成功。";
}
catch (Exception ex)
{
throw ex;
}
return msg;
}
internal static string UpdateFormAccess(DataSet bcls_rec, string conn)
{
//程序用变量
string strDeleteSql = " delete from TESGROUPACCESS where aclid = @formaclid and ACCESSERID = @groupid ";
string strInsertSql = " INSERT INTO TESGROUPACCESS(ACLID, ACCESSERID, ACCESSERTYPE, ACCESSCODE) "
+ " VALUES( @formaclid, @groupid, 2, 5) ";
try
{
for (int i = 0; i < bcls_rec.Tables[0].Rows.Count; i++)
{
//string ename = bcls_rec.Tables[0].Rows[i]["username"].ToString();
string mode = bcls_rec.Tables[0].Rows[i]["mode"].ToString();
string formaclid = bcls_rec.Tables[0].Rows[i]["formaclid"].ToString();
string groupid = bcls_rec.Tables[0].Rows[i]["groupid"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
if (mode == "insert")
{
tree_inqa.SetCommandText(strInsertSql);
}
else if (mode == "delete")
{
tree_inqa.SetCommandText(strDeleteSql);
}
tree_inqa.Parameters.Set("formaclid", formaclid);
tree_inqa.Parameters.Set("groupid", groupid);
tree_inqa.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
return "";
}
public static DataSet QueryGroupChild(DataSet bcls_rec, string conn)
{
try
{
//获得输入参数
string groupid = bcls_rec.Tables[0].Rows[0]["groupid"].ToString();
CDbCommand group_inq = new CDbCommand(conn);
string sql = " SELECT U.ID, U.NAME, U.GROUPDESCRIPTION FROM TESGROUPINFO U "
+ "WHERE U.ID IN(SELECT MEMBERID FROM TESGROUPMEMBER "
+ " WHERE MEMBERTYPE = 2 AND GROUPID= @groupid )";
group_inq.SetCommandText(sql);
group_inq.Parameters.Set("groupid", groupid);
DataTable dtGroup = new DataTable();
group_inq.ExecuteQuery(ref dtGroup);
//返回子用户
CDbCommand user_inq = new CDbCommand(conn);
string cmdText = " SELECT U.ID, U.ENAME, U.CNAME FROM TESUSERINFO U "
+ " WHERE U.ID IN(SELECT MEMBERID FROM TESGROUPMEMBER "
+ " WHERE MEMBERTYPE = 1 AND GROUPID= @groupid )";
user_inq.SetCommandText(cmdText);
user_inq.Parameters.Set("groupid", groupid);
DataTable dtUser = new DataTable();
user_inq.ExecuteQuery(ref dtUser);
DataSet ds = new DataSet();
ds.Tables.Add("Group");
ds.Tables.Add("Users");
ds.Tables[0].Merge(dtGroup);
ds.Tables[1].Merge(dtUser);
return ds;
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable QueryGroupInfo(DataSet bcls_rec, string conn)
{
//程序用变量
try
{
string groupname = bcls_rec.Tables[0].Rows[0]["groupname"].ToString();
string adminuser = bcls_rec.Tables[0].Rows[0]["adminuser"].ToString();
string userid = bcls_rec.Tables[0].Rows[0]["userid"].ToString();
string i_appname = bcls_rec.Tables[0].Rows[0]["appname"].ToString();
string grouptype = "1";// bcls_rec.Tables[0].Rows[0]["grouptype"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
tree_inqa.SetCommandText("select ID,NAME,GROUPDESCRIPTION FROM TESGROUPINFO where name like @groupname+'%' and grouptype = @grouptype");
tree_inqa.Parameters.Set("groupname", groupname);
//tree_inqa.Parameters.Set("adminuser", adminuser);
// tree_inqa.Parameters.Set("i_appname", i_appname);
tree_inqa.Parameters.Set("grouptype", grouptype);
DataTable dt = new DataTable();
tree_inqa.ExecuteQuery(ref dt);
return dt;
}
catch (Exception ex)
{
return null;
}
}
public static string SaveUserInfo(DataSet bcls_rec, string conn)
{
string msg = "ok";
int fetchRowCount = 0;
try
{
CDbCommand cmd = new CDbCommand(conn);
// 新增
int blkIns = bcls_rec.Tables.IndexOf("INSERT_BLOCK");
if (blkIns >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkIns].Rows.Count; ++fetchRowCount)
{
string ename = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["ename"].ToString();
string cname = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["cname"].ToString();
//新增用户
cmd.SetCommandText(" select count(*) from TESUSERINFO where ename = @ename");
cmd.Parameters.Set("ename", ename);
int num = Convert.ToInt32(cmd.ExecuteScalar());
if (num > 0)
{
throw new Exception("用户名[" + ename + "]已存在");
}
//新增用户
cmd.SetCommandText(" INSERT INTO TESUSERINFO ( ENAME, CNAME) VALUES (@ename, @cname)");
cmd.Parameters.Set("ename", ename);
cmd.Parameters.Set("cname", cname);
cmd.ExecuteNonQuery();
}
}
// 删除
int blkDel = bcls_rec.Tables.IndexOf("DELETE_BLOCK");
if (blkDel >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkDel].Rows.Count; ++fetchRowCount)
{
int id = Convert.ToInt32(bcls_rec.Tables[blkDel].Rows[fetchRowCount]["aclid"]);
cmd.SetCommandText("delete from tesuserinfo where id=@id ");
cmd.Parameters.Set("id", id);
int delNum = cmd.ExecuteNonQuery();
//删除用户挂组信息
cmd.SetCommandText(" delete FROM TESGROUPMEMBER WHERE MEMBERID = @id ");
cmd.Parameters.Set("id", id);
cmd.ExecuteNonQuery();
}
}
// 修改
int blkUpd = bcls_rec.Tables.IndexOf("UPDATE_BLOCK");
if (blkUpd >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkUpd].Rows.Count; ++fetchRowCount)
{
string ename = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["ename"].ToString();
string cname = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["cname"].ToString();
int id = Convert.ToInt32(bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["id"]);
//判断名称是否存在
cmd.SetCommandText("select count(*) from TESUSERINFO where ename = @ename and id !=@id ");
cmd.Parameters.Set("ename", ename);
cmd.Parameters.Set("id", id);
int count = Convert.ToInt32(cmd.ExecuteScalar());
if (count > 0)
{
msg = "操作失败！修改的名称已存在！";
throw new Exception(msg);
}
//更新用户信息表
cmd.SetCommandText("update TESUSERINFO set ename =@ename ,cname=@cname where id=@id");
cmd.Parameters.Set("ename", ename);
cmd.Parameters.Set("cname", cname);
cmd.Parameters.Set("id", id);
cmd.ExecuteNonQuery();
}
}
msg = "处理成功。";
}
catch (Exception ex)
{
//msg = ex.Message + "[" + ex.StackTrace + "]";
throw ex;
}
return msg;
}
//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;
}
}
public static DataTable QueryGroupByMember(DataSet bcls_rec, string conn)
{
//程序用变量
try
{
string memberid = bcls_rec.Tables[0].Rows[0]["memberid"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
string sqlMemGroup = " WITH GROUP_MEMBER_N(memberid, groupid) AS"
+" ( SELECT memberid, groupid FROM TESGROUPMEMBER"
+" WHERE memberid IN ( SELECT GROUPID FROM TESGROUPMEMBER WHERE memberid = @memberid )"
+" union all "
+" SELECT np1.memberid, np1.groupid FROM GROUP_MEMBER_N n,TESGROUPMEMBER np1 WHERE n.groupid= np1.memberid"
+" )"
+" select * from tesgroupinfo where id in ("
+" SELECT distinct groupid FROM GROUP_MEMBER_N"
+" UNION ALL"
+" SELECT GROUPID FROM TESGROUPMEMBER WHERE memberid = @memberid) ";
tree_inqa.SetCommandText(sqlMemGroup);
tree_inqa.Parameters.Set("memberid", memberid);
DataTable dt = new DataTable();
tree_inqa.ExecuteQuery(ref dt);
return dt;
}
catch (Exception ex)
{
return null;
}
}
//epesbuttauthinq
public static DataTable QueryButtonAuth(DataSet bcls_rec, string conn)
{
try
{
string subj = bcls_rec.Tables[0].Rows[0]["id"].ToString();
string i_formname = bcls_rec.Tables[0].Rows[0]["formid"].ToString();
string mode = bcls_rec.Tables[0].Rows[0]["mode"].ToString();
string i_appname = bcls_rec.Tables[0].Rows[0]["appname"].ToString();
CDbCommand tree_inqa = new CDbCommand(conn);
string strSql = "";
if (mode == "1") //根据群组id查 and form name query button
{
//通过群组id查询 button
strSql = " SELECT BI.ACLID, BI.NAME, BI.DESCRIPTION, "
+ "(SELECT COUNT(*) FROM TESGROUPACCESS WHERE ACLID = BI.ACLID AND ACCESSERID = @id ) as num"
+ " FROM TESBUTTONRESINFO BI "
+ " WHERE BI.FNAME in (select name from tesformresinfo where aclid= @formname) ORDER BY BI.NAME ";
}
else if (mode == "2")
{
//通过用户id查询 按钮信息
strSql = " WITH GROUP_MEMBER_N(memberid, groupid) "
+ "AS"
+ " ( SELECT memberid, groupid FROM TESGROUPMEMBER"
+ " WHERE memberid IN ( SELECT GROUPID"
+ " FROM TESGROUPMEMBER WHERE MEMBERID = @id"
+ " )"
+ " UNION ALL "
+ " SELECT np1.memberid, np1.groupid FROM GROUP_MEMBER_N n, TESGROUPMEMBER np1"
+ " WHERE n.groupid = np1.memberid"
+ " ) "
+ " SELECT BI.ACLID, BI.NAME, BI.DESCRIPTION, (SELECT COUNT(*) FROM TESGROUPACCESS"
+ " WHERE ACLID = BI.ACLID AND ACCESSERID IN"
+ " ( SELECT DISTINCT groupid FROM GROUP_MEMBER_N "
+ " UNION ALL"
+ " SELECT GROUPID FROM TESGROUPMEMBER WHERE memberid = @id "
+ " ) "
+ " ) as NUM"
+ " FROM TESBUTTONRESINFO BI "
+ " WHERE BI.FNAME in (select name from tesformresinfo where aclid= @formname) "
+ " ORDER BY BI.NAME ";
}
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(strSql))
{
tree_inqa.SetCommandText(strSql);
tree_inqa.Parameters.Set("id", subj);
tree_inqa.Parameters.Set("formname", i_formname);
tree_inqa.Parameters.Set("appname", i_appname);
tree_inqa.ExecuteQuery(ref dt);
return(dt);
}
return(dt);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable QueryUserInfo(DataSet bcls_rec, string conn)
{
//程序用变量
string strSql = " select * from tesuserinfo where cname like @cname +'%'";
try
{
string cname = "";
if (bcls_rec.Tables.Count > 0 && bcls_rec.Tables[0].Rows.Count > 0)
{
cname = bcls_rec.Tables[0].Rows[0][0].ToString();
}
CDbCommand tree_inqa = new CDbCommand(conn);
tree_inqa.SetCommandText(strSql);
tree_inqa.Parameters.Set("cname", cname);
DataTable dt = new DataTable();
tree_inqa.ExecuteQuery(ref dt);
return dt;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 保存按钮
/// </summary>
/// <param name="bcls_rec"></param>
/// <param name="conn"></param>
/// <returns></returns>
public static string SaveButtonInfo(DataSet bcls_rec, string conn)
{
string msg = "ok";
int fetchRowCount = 0;
try
{
CDbCommand cmd = new CDbCommand(conn);
string name = "";
string fname = "";
string descript = "";
string optype = "";
string appName = "";
string sqlQueryBtn = " select COUNT(*) FROM TESBUTTONRESINFO WHERE NAME = @name and FNAME = @fname ";
string sqlInsertBtn = "INSERT INTO [TESBUTTONRESINFO] "
+ " ([NAME],[FNAME],[DESCRIPTION],[OPTYPE] ,[APPNAME])"
+ " VALUES "
+ " (@name,@fname, @description, @optype,@appname)";
string sqlDelBtn = "DELETE FROM TESBUTTONRESINFO WHERE ACLID = @aclid ";
string sqlDelBtnAuthById = " DELETE FROM TESGROUPACCESS WHERE ACLID = @aclid ";
string sqlQuerySameBtn = " SELECT COUNT(*) FROM TESBUTTONRESINFO WHERE NAME = @name and FNAME = @fname AND ACLID != @aclid ";
string sqlUpdBtn = "UPDATE [TESFORMRESINFO]"
+ " SET [NAME] = @name,[DESCRIPTION]=@description,[FNAME] = @fanme , "
+ " [OPTYPE] = @optype, [APPNAME] = @APPNAME "
+ " WHERE aclid = @aclid";
// 新增
int blkIns = bcls_rec.Tables.IndexOf("INSERT_BLOCK");
if (blkIns >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkIns].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["name"].ToString();
descript = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["description"].ToString();
fname = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["fname"].ToString();
optype = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["optype"].ToString();
appName = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["appName"].ToString();
//判断画面名是否重复
cmd.SetCommandText(sqlQueryBtn);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("fname", fname);
object obj = cmd.ExecuteScalar();
if (Convert.ToInt32(obj) > 0)
{
msg = "输入的按钮名[" + name + "]在画面[" + fname + "]已存在，请重新输入！";
throw new Exception(msg);
}
cmd.SetCommandText(sqlInsertBtn);
cmd.Parameters.Set("description", descript);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("fname", fname);
cmd.Parameters.Set("optype", optype);
cmd.Parameters.Set("appname", appName);
cmd.ExecuteNonQuery();
}
}
// 删除
int blkDel = bcls_rec.Tables.IndexOf("DELETE_BLOCK");
if (blkDel >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkDel].Rows.Count; ++fetchRowCount)
{
int aclidBtn = Convert.ToInt32(bcls_rec.Tables[blkDel].Rows[fetchRowCount]["aclid"]);
cmd.SetCommandText(sqlDelBtn);
cmd.Parameters.Set("aclid", aclidBtn);
int delNum = cmd.ExecuteNonQuery();
if (delNum > 0)
{
//删除按钮授权信息
cmd.SetCommandText(sqlDelBtnAuthById);
cmd.Parameters.Set("aclid", aclidBtn);
cmd.ExecuteNonQuery();
//从按钮表删除
cmd.SetCommandText(sqlDelBtn);
cmd.ExecuteNonQuery();
}
}
}
// 修改
int blkUpd = bcls_rec.Tables.IndexOf("UPDATE_BLOCK");
if (blkUpd >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkUpd].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["name"].ToString();
descript = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["description"].ToString();
fname = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["fname"].ToString();
optype = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["optype"].ToString();
appName = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["appname"].ToString();
int aclidBtn = Convert.ToInt32(bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["aclid"]);
//判断画面是否存在
cmd.SetCommandText(sqlQuerySameBtn);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("fname", fname);
cmd.Parameters.Set("aclid", aclidBtn);
int formCount = Convert.ToInt32(cmd.ExecuteScalar());
if (formCount > 0)
{
msg = "操作失败！修改的按钮名已存在！";
throw new Exception(msg);
}
//更新画面信息表
cmd.SetCommandText(sqlUpdBtn);
cmd.Parameters.Set("description", descript);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("fname", fname);
cmd.Parameters.Set("optype", optype);
cmd.Parameters.Set("appname", appName);
cmd.Parameters.Set("aclid", aclidBtn);
cmd.ExecuteNonQuery();
}
}
msg = "处理成功。";
}
catch (Exception ex)
{
msg = ex.Message + "[" + ex.StackTrace + "]";
}
return(msg);
}
/// <summary>
/// 保存窗体
/// </summary>
/// <param name="bcls_rec"></param>
/// <param name="conn"></param>
/// <returns></returns>
public static string SaveFormInfo(DataSet bcls_rec, string conn)
{
string msg = "ok";
int fetchRowCount = 0;
try
{
//CDbCommand cmdForm(conn);
CDbCommand cmd = new CDbCommand(conn);
string name = "";
string descript = "";
string dllName = "";
string abbrev = "";
string fromCallMode = "0";
string appName = "";
string dllPath = "";
string sqlInsertFrm = "INSERT INTO [TESFORMRESINFO] "
+ " ([NAME],[DESCRIPTION],[DLLNAME] ,[ABBREV],[ICONNUM] ,[FORM_CALL_MODE],[APPNAME] ,[DLLPATH])"
+ " VALUES "
+ " (@name, @description, @dllname, @abbrev,@iconnum,@form_call_mode,@appname,@dllpath)";
string sqlDel = "delete from TESFORMRESINFO where aclid = @aclid";
string sqlDelBtnAuthByFrm = " delete FROM TESGROUPACCESS "
+ " WHERE ACLID in (select ACLID from TESBUTTONRESINFO "
+ " WHERE FNAME = @name ) ";
string sqlDelFrmAuth = " delete FROM TESGROUPACCESS where ACLID = @aclid ";
string sqlQueryBtn = " SELECT ACLID,NAME,DESCRIPTION FROM TESBUTTONRESINFO WHERE FNAME = @name";
string sqlDelBtnAuthById = " DELETE FROM TESGROUPACCESS WHERE ACLID = @aclid ";
string sqlDelBtn = "DELETE FROM TESBUTTONRESINFO WHERE ACLID = @aclid ";
string sqlQuerySameFrm = " SELECT COUNT(*) FROM TESFORMRESINFO WHERE NAME = @name AND ACLID != @aclid ";
string sqlUpdFrm = "UPDATE [TESFORMRESINFO]"
+ " SET [NAME] = @NAME,[DESCRIPTION]=@DESCRIPTION,[DLLNAME] = @DLLNAME "
+ " ,[ABBREV] = @ABBREV,[ICONNUM] = @ICONNUM,[FORM_CALL_MODE] = @FORM_CALL_MODE "
+ " ,[APPNAME] = @APPNAME ,[DLLPATH] = @DLLPATH "
+ " WHERE aclid = @aclid";
string sqlUpdBtnFname = " update TESBUTTONRESINFO set fname = @fname where fname=(select top 1 name from TESFORMRESINFO where aclid=@aclid) ";
// 新增
int blkIns = bcls_rec.Tables.IndexOf("INSERT_BLOCK");
if (blkIns >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkIns].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["name"].ToString();
descript = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["description"].ToString();
dllName = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["dllname"].ToString();
abbrev = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["abbrev"].ToString();
dllPath = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["dllpath"].ToString();
if (null != bcls_rec.Tables[blkIns].Rows[fetchRowCount]["form_call_mode"])
{
fromCallMode = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["form_call_mode"].ToString();
}
//判断画面名是否重复
cmd.SetCommandText(" select COUNT(*) FROM TESFORMRESINFO WHERE NAME = @name ");
cmd.Parameters.Set("name", name);
object obj = cmd.ExecuteScalar();
if (Convert.ToInt32(obj) > 0)
{
msg = "输入的画面名[" + name + "]已存在，请重新输入！";
throw new Exception(msg);
}
cmd.SetCommandText(sqlInsertFrm);
cmd.Parameters.Set("description", descript);
cmd.Parameters.Set("dllname", dllName);
cmd.Parameters.Set("abbrev", abbrev);
cmd.Parameters.Set("iconnum", 0);
cmd.Parameters.Set("form_call_mode", fromCallMode);
cmd.Parameters.Set("appname", appName);
cmd.Parameters.Set("dllpath", dllPath);
cmd.ExecuteNonQuery();
}
}
// 删除
int blkDel = bcls_rec.Tables.IndexOf("DELETE_BLOCK");
if (blkDel >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkDel].Rows.Count; ++fetchRowCount)
{
int aclidFrm = Convert.ToInt32(bcls_rec.Tables[blkDel].Rows[fetchRowCount]["aclid"]);
cmd.SetCommandText(sqlDel);
cmd.Parameters.Set("aclid", aclidFrm);
int delNum = cmd.ExecuteNonQuery();
if (delNum > 0)
{
//删除画面下所有按钮的授权信息
cmd.SetCommandText(sqlDelBtnAuthByFrm);
cmd.Parameters.Set("name", name);
cmd.ExecuteNonQuery();
//删除授权信息中所有该画面的信息
cmd.SetCommandText(sqlDelFrmAuth);
cmd.Parameters.Set("aclid", aclidFrm);
cmd.ExecuteNonQuery();
//删除画面下所有的按钮
cmd.SetCommandText(sqlQueryBtn);
cmd.Parameters.Set("name", name);
DataTable dtBtnInfo = new DataTable();
cmd.ExecuteQuery(ref dtBtnInfo);
for (int i = 0; i < dtBtnInfo.Rows.Count; i++)
{
int aclidBtn = Convert.ToInt32(dtBtnInfo.Rows[i]["aclid"]);
//删除按钮授权信息
cmd.SetCommandText(sqlDelBtnAuthById);
cmd.Parameters.Set("aclid", aclidBtn);
cmd.ExecuteNonQuery();
//从按钮表删除
cmd.SetCommandText(sqlDelBtn);
cmd.ExecuteNonQuery();
}
}
}
}
// 修改
int blkUpd = bcls_rec.Tables.IndexOf("UPDATE_BLOCK");
if (blkUpd >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkUpd].Rows.Count; ++fetchRowCount)
{
name = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["name"].ToString();
descript = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["description"].ToString();
dllName = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["dllname"].ToString();
abbrev = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["abbrev"].ToString();
dllPath = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["dllpath"].ToString();
if (null != bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["form_call_mode"])
{
fromCallMode = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["form_call_mode"].ToString();
}
int aclidFrm = Convert.ToInt32(bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["aclid"]);
//判断画面是否存在
cmd.SetCommandText(sqlQuerySameFrm);
cmd.Parameters.Set("name", name);
cmd.Parameters.Set("aclid", aclidFrm);
int formCount = Convert.ToInt32(cmd.ExecuteScalar());
if (formCount > 0)
{
msg = "操作失败！修改的画面已存在！";
throw new Exception(msg);
}
//更新画面名时更新按钮表的fname原画面名
cmd.SetCommandText(sqlUpdBtnFname);
cmd.Parameters.Set("aclid", aclidFrm);
cmd.Parameters.Set("fname", name);
cmd.ExecuteNonQuery();
//更新画面信息表
cmd.SetCommandText(sqlUpdFrm);
cmd.Parameters.Set("description", descript);
cmd.Parameters.Set("dllname", dllName);
cmd.Parameters.Set("abbrev", abbrev);
cmd.Parameters.Set("iconnum", 0);
cmd.Parameters.Set("form_call_mode", fromCallMode);
cmd.Parameters.Set("appname", appName);
cmd.Parameters.Set("dllpath", dllPath);
cmd.Parameters.Set("aclid", aclidFrm);
cmd.ExecuteNonQuery();
}
}
msg = "处理成功。";
}
catch (Exception ex)
{
msg = ex.Message + "[" + ex.StackTrace + "]";
}
return(msg);
}
//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;
}
}
public static string SaveUserInfo(DataSet bcls_rec, string conn)
{
string msg = "ok";
int fetchRowCount = 0;
try
{
CDbCommand cmd = new CDbCommand(conn);
// 新增
int blkIns = bcls_rec.Tables.IndexOf("INSERT_BLOCK");
if (blkIns >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkIns].Rows.Count; ++fetchRowCount)
{
string ename = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["ename"].ToString();
string cname = bcls_rec.Tables[blkIns].Rows[fetchRowCount]["cname"].ToString();
//新增用户
cmd.SetCommandText(" select count(*) from TESUSERINFO where ename = @ename");
cmd.Parameters.Set("ename", ename);
int num = Convert.ToInt32(cmd.ExecuteScalar());
if (num > 0)
{
throw new Exception("用户名[" + ename + "]已存在");
}
//新增用户
cmd.SetCommandText(" INSERT INTO TESUSERINFO ( ENAME, CNAME) VALUES (@ename, @cname)");
cmd.Parameters.Set("ename", ename);
cmd.Parameters.Set("cname", cname);
cmd.ExecuteNonQuery();
}
}
// 删除
int blkDel = bcls_rec.Tables.IndexOf("DELETE_BLOCK");
if (blkDel >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkDel].Rows.Count; ++fetchRowCount)
{
int id = Convert.ToInt32(bcls_rec.Tables[blkDel].Rows[fetchRowCount]["aclid"]);
cmd.SetCommandText("delete from tesuserinfo where id=@id ");
cmd.Parameters.Set("id", id);
int delNum = cmd.ExecuteNonQuery();
//删除用户挂组信息
cmd.SetCommandText(" delete FROM TESGROUPMEMBER WHERE MEMBERID = @id ");
cmd.Parameters.Set("id", id);
cmd.ExecuteNonQuery();
}
}
// 修改
int blkUpd = bcls_rec.Tables.IndexOf("UPDATE_BLOCK");
if (blkUpd >= 0)
{
for (fetchRowCount = 0; fetchRowCount < bcls_rec.Tables[blkUpd].Rows.Count; ++fetchRowCount)
{
string ename = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["ename"].ToString();
string cname = bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["cname"].ToString();
int id = Convert.ToInt32(bcls_rec.Tables[blkUpd].Rows[fetchRowCount]["id"]);
//判断名称是否存在
cmd.SetCommandText("select count(*) from TESUSERINFO where ename = @ename and id !=@id ");
cmd.Parameters.Set("ename", ename);
cmd.Parameters.Set("id", id);
int count = Convert.ToInt32(cmd.ExecuteScalar());
if (count > 0)
{
msg = "操作失败！修改的名称已存在！";
throw new Exception(msg);
}
//更新用户信息表
cmd.SetCommandText("update TESUSERINFO set ename =@ename ,cname=@cname where id=@id");
cmd.Parameters.Set("ename", ename);
cmd.Parameters.Set("cname", cname);
cmd.Parameters.Set("id", id);
cmd.ExecuteNonQuery();
}
}
msg = "处理成功。";
}
catch (Exception ex)
{
//msg = ex.Message + "[" + ex.StackTrace + "]";
throw ex;
}
return(msg);
}
