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