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