/// <summary> /// 根据用户ID查询用户操作权限 /// </summary> /// <param name="user"></param> /// <param name="errMsg"></param> /// <returns></returns> public DataTable Competence(User user, out string errMsg) { errMsg = string.Empty; string strSql = string.Empty; strSql = string.Format(@" --判断是否为账套主管:一个为账套主管组,一个为功能编码admin,功能名称:账套主管 SELECT CASE WHEN COUNT(1)>0 THEN 1 ELSE 0 END FROM (SELECT * FROM UFSystem..UA_Role WHERE cUser_Id='{0}' ) r INNER JOIN (SELECT * FROM UFSystem..UA_HoldAuth WHERE cAuth_Id='admin' AND iYear='{1}' AND cAcc_Id='{2}') ha ON ha.cUser_Id= r.cUser_Id OR ha.cUser_Id = r.cGroup_Id", user.UserID, user.Year, user.AccID); //如果为1,说明为账套主管 if (DBHelperSQL.ExecuteScalar(user.ConnectionString, strSql).ToString() == "1") { //销售出库、盘点 strSql = @"SELECT 1 AS XSCK,1 AS PD,1 AS Admin"; return(DBHelperSQL.QueryTable(user.ConnectionString, strSql)); } //否则为普通操作人员 else { strSql = string.Format(@" --普通操作人员 DECLARE @XSCK INT,--销售出库(红字)(ASM0202:销售出库单录入,ASM0203:销售出库单审核) @PD INT --盘点(ST010202:盘点单录入) --销售出库(包括红字) SELECT @XSCK = CASE WHEN COUNT(1)>0 THEN 1 ELSE 0 END FROM (SELECT cUser_Id,cGroup_Id FROM UFSystem..UA_Role WHERE cUser_Id='{0}' UNION ALL SELECT cUser_Id,NULL as cGroup_Id FROM UFSystem..UA_User WHERE cUser_Id='{0}') r INNER JOIN (SELECT cUser_Id FROM UFSystem..UA_HoldAuth WHERE cAuth_Id IN('ASM0202' ,'ASM0203') AND iYear='{1}' AND cAcc_Id='{2}' GROUP BY cUser_Id HAVING COUNT(1)=2 ) ha ON ha.cUser_Id= r.cUser_Id OR ha.cUser_Id = r.cGroup_Id --盘点 SELECT @PD = CASE WHEN COUNT(1)>0 THEN 1 ELSE 0 END FROM (SELECT cUser_Id,cGroup_Id FROM UFSystem..UA_Role WHERE cUser_Id='{0}' UNION ALL SELECT cUser_Id,NULL as cGroup_Id FROM UFSystem..UA_User WHERE cUser_Id='{0}') r INNER JOIN (SELECT cUser_Id FROM UFSystem..UA_HoldAuth WHERE cAuth_Id='ST010202' AND iYear='{1}' AND cAcc_Id='{2}') ha ON ha.cUser_Id= r.cUser_Id OR ha.cUser_Id = r.cGroup_Id SELECT @XSCK AS XSCK,@PD AS PD,0 AS Admin", user.UserID, user.Year, user.AccID); return(DBHelperSQL.QueryTable(user.ConnectionString, strSql)); } }
/// <summary> /// 根据存货编码与批次查询标签流水号(打印小标签时需要流水号) /// </summary> /// <param name="cInvCode"></param> /// <param name="cBatch"></param> /// <param name="errMsg"></param> /// <returns></returns> /// <remarks>tianzhenyun 2013-06-17 20:10</remarks> public int GetLabelSerialNumber(string connectionString, string cInvCode, string cBatch, out string errMsg) { errMsg = string.Empty; int result; string strSql = string.Format("SELECT Number FROM UFSystem..SerialNumber WHERE cInvCode='{0}' AND cBatch ='{1}'", cInvCode, cBatch); try { object obj = DBHelperSQL.ExecuteScalar(connectionString, strSql); result = Cast.ToInteger(obj); } catch (Exception ex) { errMsg = ex.Message; result = -1; } return(result); }
/// <summary> /// 标签打印数据库连接测试 /// </summary> /// <param name="accid">账套</param> /// <param name="year">年度</param> /// <param name="info">数据库信息</param> /// <param name="connectionString">如何连接成功,返回连接字符串</param> /// <param name="errMsg">错误信息</param> /// <returns></returns> /// <remarks>tianzhenyun 2013-06-14</remarks> public bool ConnectionTest(string accid, string year, DBInfo info, out string connectionString, out string errMsg) { errMsg = string.Empty; connectionString = string.Empty; bool flag = false; if (info == null) { errMsg = "数据库配置信息错误!"; return(flag); } //组装连接字符串 connectionString = string.Format(@"user id={0};password={1};data source={2};persist security info=True;initial catalog=UFDATA_{3}_{4};Connection Timeout=30", info.SqlUser, info.SqlPassword, info.DBServer, accid, year); string strSql = "select count(1) from inventory where 1=2;"; try { object obj = DBHelperSQL.ExecuteScalar(connectionString, strSql); if (obj == null && obj == DBNull.Value) { errMsg = "测试连接错误!"; if (Common.flag) { Common.log.Error(errMsg + connectionString); } connectionString = string.Empty; return(flag); } if (obj.ToString().Equals("0")) { flag = true; } } catch (Exception ex) { errMsg = ex.Message; } return(flag); }
/// <summary> /// 登录成功后,查询该用户权限下所有的仓库 /// </summary> /// <param name="dtWarehouse"></param> /// <param name="errMsg"></param> /// <returns></returns> public bool GetWarehouse(User user, out DataTable dtWarehouse, out string errMsg) { dtWarehouse = null; errMsg = string.Empty; try { ///2012-10-24 ///仓库权限:如果用户是账套主管或所在角色为账套主管UA_holdauth ///或者如果用户是权限主管或所有角色为权限主管AA_holdBusobject ///那么显示所有的仓库 ///如果不是那就进行普通权限分配查询,用户的仓库权限或所有角色仓库权限 string strSql = string.Format(@"IF EXISTS( --查询用户是否为账套主管 SELECT 1 FROM UFSystem.dbo.UA_HoldAuth WHERE cAuth_Id ='admin' AND cAcc_Id='{0}' AND iYear='{1}' AND cUser_Id ='{2}' UNION ALL --查询用户所有角色是否为账套主管 SELECT 1 FROM (SELECT cUser_Id FROM UFSystem.dbo.UA_HoldAuth WHERE cAuth_Id ='admin' AND cAcc_Id='{0}' AND iYear='{1}') h INNER JOIN (SELECT cGroup_Id FROM UFSystem.dbo.UA_Role WHERE cUser_Id='{2}') r ON h.cUser_Id= r.cGroup_Id UNION ALL --查询用户是否为仓库权限主管 SELECT 1 FROM AA_holdbusobject WHERE iAdmin =1 AND CBusObId ='warehouse' AND cUserId= '{2}' UNION ALL --查询用户所在角色是否仓库权限主管 SELECT 1 FROM (SELECT cUserId FROM AA_holdbusobject WHERE iAdmin =1 AND CBusObId ='warehouse' ) h INNER JOIN (SELECT cGroup_Id FROM UFSystem.dbo.UA_Role WHERE cUser_Id='{2}') r ON h.cUserId = r.cGroup_Id ) --若是返回1 SELECT 1 AS flag ELSE --否则返回0 SELECT 0 AS flag ", user.AccID, user.Year, user.UserID); int flag = Convert.ToInt32(DBHelperSQL.ExecuteScalar(user.ConnectionString, strSql)); //如果是账套主管显示所有仓库 if (flag == 1) { strSql = "Select cwhname,cwhcode,bWhPos from Warehouse"; } else { ///根据用户名查询权限分配表 查找该用户有哪些仓库可用 ///cBusObId业务对象标识 这里为'仓库' strSql = string.Format(@"SELECT wh.* FROM (Select cwhname,cwhcode,bWhPos from Warehouse) wh INNER JOIN ( --查询该用户的仓库权限 SELECT cACCode FROM aa_holdauth WHERE cBusObId='warehouse' AND cUserId='{0}' UNION ALL --查询该用户所在角色的仓库权限 SELECT ha.cACCode FROM (SELECT cACCode,cUserId FROM dbo.AA_HoldAuth WHERE cBusObId ='warehouse' AND isUserGroup=1 ) ha INNER JOIN (SELECT cGroup_Id,cUser_Id FROM UFSystem.dbo.UA_Role WHERE cUser_Id='{0}') r ON ha.cUserId = r.cGroup_Id ) temp ON wh.cwhcode =temp.cACCode", user.UserID); } dtWarehouse = DBHelperSQL.QueryTable(user.ConnectionString, strSql); return(true); } catch (Exception ex) { errMsg = ex.Message; return(false); } }