public static List <RightUser> QueryRightUser(QueryCondition queryCondition)
{
var sql = @"
SELECT T_RIGHT_USER.SYSID AS Sysid,T_RIGHT_USER.USER_ID AS UserId,
T_RIGHT_USER.USER_NAME AS UserName,T_RIGHT_USER.USER_PWD AS UserPwd,
T_RIGHT_USER.DEPARTMENT AS Department,T_RIGHT_USER.EMAIL AS Email,
T_RIGHT_USER.CREATED_ON AS CreatedOn,T_RIGHT_USER.CREATED_BY AS CreatedBy,
T_RIGHT_USER.MODIFIED_ON AS ModifiedOn,T_RIGHT_USER.MODIFIED_BY AS ModifiedBy,
T_RIGHT_USER.RECORD_STATUS AS RecordStatus,
T_RIGHT_USER.RESV01 AS Resv01,T_RIGHT_USER.RESV02 AS Resv02,T_RIGHT_USER.RESV03 AS Resv03,T_RIGHT_USER.RESV04 AS Resv04,
T_RIGHT_USER.RESV05 AS Resv05,T_RIGHT_USER.RESV06 AS Resv06,T_RIGHT_USER.RESV07 AS Resv07,
T_RIGHT_USER.RESV08 AS Resv08,T_RIGHT_USER.RESV09 AS Resv09,T_RIGHT_USER.RESV10 AS Resv10,
T_DICTIONARY_ITEM.NAME DepartmentName
FROM T_RIGHT_USER WITH(NOLOCK)
LEFT JOIN T_DICTIONARY_ITEM WITH(NOLOCK) ON
T_RIGHT_USER.DEPARTMENT=T_DICTIONARY_ITEM.CODE
AND T_DICTIONARY_ITEM.DICTIONARY_CODE = 'Department'
WHERE 1 = 1 ";
var dict = new Dictionary <QueryConditionField, string>
{
{ QueryConditionField.DepartmentCode, " AND T_RIGHT_USER.DEPARTMENT = " },
{ QueryConditionField.UserId, " AND T_RIGHT_USER.USER_ID = " }
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
sql += " ORDER BY T_RIGHT_USER.RECORD_STATUS DESC,T_RIGHT_USER.MODIFIED_ON DESC ";
return(Dapper.Query <RightUser>(sql, queryCondition));
}
public static bool IsAdmin(QueryCondition queryCondition)
{
var isAdmin = false;
var sql = @"
SELECT A.SYSID AS Sysid,A.USER_ID AS UserId,A.USER_NAME AS UserName,A.USER_PWD AS UserPwd,A.DEPARTMENT AS Department,A.EMAIL AS Email,A.CREATED_ON AS CreatedOn,A.CREATED_BY AS CreatedBy,A.MODIFIED_ON AS ModifiedOn,A.MODIFIED_BY AS ModifiedBy,A.RECORD_STATUS AS RecordStatus,A.RESV01 AS Resv01,A.RESV02 AS Resv02,A.RESV03 AS Resv03,A.RESV04 AS Resv04,A.RESV05 AS Resv05,A.RESV06 AS Resv06,A.RESV07 AS Resv07,A.RESV08 AS Resv08,A.RESV09 AS Resv09,A.RESV10 AS Resv10
FROM T_RIGHT_USER A WITH(NOLOCK)
INNER JOIN T_RIGHT_LK_USER_GROUP B WITH(NOLOCK) ON A.SYSID = B.USER_SYSID
INNER JOIN T_RIGHT_USER_GROUP C WITH(NOLOCK) ON B.GROUP_SYSID = C.SYSID
WHERE C.GROUP_NAME = '系统管理员'
";
var dict = new Dictionary <QueryConditionField, string>
{
{ QueryConditionField.UserId, " AND A.USER_ID = " }
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
var userlist = Dapper.Query <RightUser>(sql, queryCondition);
if (userlist.Count > 0)
{
isAdmin = true;
}
return(isAdmin);
}
public static List <DictionaryItem> QueryDictionaryItem(QueryCondition queryCondition)
{
var sql = @"
SELECT A.SYSID AS Sysid,A.DICTIONARY_CODE AS DictionaryCode,A.CODE AS Code
,A.NAME AS Name,A.SEQUENCE AS Sequence,A.WORKSHOP AS Workshop
,A.STEP AS Step,A.REMARK AS Remark,A.CREATED_ON AS CreatedOn
,A.CREATED_BY AS CreatedBy,A.MODIFIED_ON AS ModifiedOn
,A.MODIFIED_BY AS ModifiedBy,A.RECORD_STATUS AS RecordStatus
,A.RESV01 AS Resv01,A.RESV02 AS Resv02,A.RESV03 AS Resv03
,A.RESV04 AS Resv04,A.RESV05 AS Resv05,A.RESV06 AS Resv06
,A.RESV07 AS Resv07,A.RESV08 AS Resv08,A.RESV09 AS Resv09
,A.RESV10 AS Resv10,B.NAME AS DictionaryName
FROM T_DICTIONARY_ITEM AS A WITH(NOLOCK)
INNER JOIN T_DICTIONARY_INFO AS B WITH(NOLOCK) ON
A.DICTIONARY_CODE = B.CODE
WHERE 1 = 1
";
var dict = new Dictionary <QueryConditionField, string>
{
{ QueryConditionField.Code, " AND A.DICTIONARY_CODE = " },
{ QueryConditionField.ReasonCategory, " AND B.CATEGORY = " }
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
sql += " ORDER BY A.SEQUENCE ";
return(Dapper.Query <DictionaryItem>(sql, queryCondition));
}
public static List<RightLkFunctionCommand> QueryRightLkFunctionCommand(QueryCondition queryCondition)
{
var sql = @"
SELECT FUNCTION_SYSID AS FunctionSysid,COMMAND_SYSID AS CommandSysid
FROM T_RIGHT_LK_FUNCTION_COMMAND WITH(NOLOCK)
WHERE 1 = 1 ";
var dict = new Dictionary<QueryConditionField, string>
{
{QueryConditionField.Resv01, " AND FUNCTION_SYSID = "}
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
return Dapper.Query<RightLkFunctionCommand>(sql, queryCondition);
}
public static List <RightLkFunctionCommand> QueryRightLkFunctionCommand(QueryCondition queryCondition)
{
var sql = @"
SELECT FUNCTION_SYSID AS FunctionSysid,COMMAND_SYSID AS CommandSysid
FROM T_RIGHT_LK_FUNCTION_COMMAND WITH(NOLOCK)
WHERE 1 = 1 ";
var dict = new Dictionary <QueryConditionField, string>
{
{ QueryConditionField.Resv01, " AND FUNCTION_SYSID = " }
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
return(Dapper.Query <RightLkFunctionCommand>(sql, queryCondition));
}
public static List <ViewRight> QueryViewRight(QueryCondition queryCondition)
{
var sql = @"
SELECT RFG.GROUP_NAME FunctionGroupName
,RF.FUNC_CODE FunctionCode
,RF.FUNC_NAME FunctionName
,RF.FUNC_PARAM FunctionParam
,DI.CODE CommandCode
,DI.NAME CommandName
FROM T_RIGHT R WITH(NOLOCK)
INNER JOIN T_RIGHT_LK_USER_GROUP LUG WITH(NOLOCK) ON
R.USER_GROUP_SYSID = LUG.GROUP_SYSID
INNER JOIN T_RIGHT_USER RU WITH(NOLOCK) ON
LUG.USER_SYSID = RU.SYSID
INNER JOIN T_RIGHT_FUNCTION_GROUP RFG WITH(NOLOCK) ON
R.FUNCTION_GROUP_SYSID = RFG.SYSID
INNER JOIN T_RIGHT_LK_FUNCTION_GROUP RLFG WITH(NOLOCK) ON
RFG.SYSID = RLFG.GROUP_SYSID
INNER JOIN T_RIGHT_FUNCTION RF WITH(NOLOCK) ON
R.FUNCTION_SYSID = RF.SYSID
AND RF.SYSID = RLFG.FUNCTION_SYSID
INNER JOIN T_RIGHT_LK_FUNCTION_COMMAND RLFC WITH(NOLOCK) ON
RF.SYSID = RLFC.FUNCTION_SYSID
LEFT JOIN T_DICTIONARY_ITEM DI WITH(NOLOCK) ON
R.COMMAND_SYSID = DI.SYSID
AND DI.SYSID = RLFC.COMMAND_SYSID
AND DI.DICTIONARY_CODE = 'FunctionCommand'
WHERE 1 = 1 ";
var dict = new Dictionary <QueryConditionField, string>
{
{ QueryConditionField.UserId, " AND RU.USER_ID = " }
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
sql += " ORDER BY RFG.SEQUENCE,RLFG.SEQUENCE ";
return(Dapper.Query <ViewRight>(sql, queryCondition));
}
public static List <RightFunctionGroup> QueryRightFunctionGroup(QueryCondition queryCondition)
{
var sql = @"
SELECT SYSID AS Sysid,GROUP_NAME AS GroupName,SEQUENCE AS Sequence,CREATED_ON AS CreatedOn,CREATED_BY AS CreatedBy,MODIFIED_ON AS ModifiedOn,MODIFIED_BY AS ModifiedBy,RECORD_STATUS AS RecordStatus
FROM T_RIGHT_FUNCTION_GROUP WITH(NOLOCK)
WHERE 1=1
";
var dict = new Dictionary <QueryConditionField, string>
{
{ QueryConditionField.Name, " AND GROUP_NAME = " }
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
sql += " ORDER BY RECORD_STATUS DESC,MODIFIED_ON DESC ";
return(Dapper.Query <RightFunctionGroup>(sql, queryCondition));
}
public static List<ViewRight> QueryViewRight(QueryCondition queryCondition)
{
var sql = @"
SELECT RFG.GROUP_NAME FunctionGroupName
,RF.FUNC_CODE FunctionCode
,RF.FUNC_NAME FunctionName
,RF.FUNC_PARAM FunctionParam
,DI.CODE CommandCode
,DI.NAME CommandName
FROM T_RIGHT R WITH(NOLOCK)
INNER JOIN T_RIGHT_LK_USER_GROUP LUG WITH(NOLOCK) ON
R.USER_GROUP_SYSID = LUG.GROUP_SYSID
INNER JOIN T_RIGHT_USER RU WITH(NOLOCK) ON
LUG.USER_SYSID = RU.SYSID
INNER JOIN T_RIGHT_FUNCTION_GROUP RFG WITH(NOLOCK) ON
R.FUNCTION_GROUP_SYSID = RFG.SYSID
INNER JOIN T_RIGHT_LK_FUNCTION_GROUP RLFG WITH(NOLOCK) ON
RFG.SYSID = RLFG.GROUP_SYSID
INNER JOIN T_RIGHT_FUNCTION RF WITH(NOLOCK) ON
R.FUNCTION_SYSID = RF.SYSID
AND RF.SYSID = RLFG.FUNCTION_SYSID
INNER JOIN T_RIGHT_LK_FUNCTION_COMMAND RLFC WITH(NOLOCK) ON
RF.SYSID = RLFC.FUNCTION_SYSID
LEFT JOIN T_DICTIONARY_ITEM DI WITH(NOLOCK) ON
R.COMMAND_SYSID = DI.SYSID
AND DI.SYSID = RLFC.COMMAND_SYSID
AND DI.DICTIONARY_CODE = 'FunctionCommand'
WHERE 1 = 1 ";
var dict = new Dictionary<QueryConditionField, string>
{
{QueryConditionField.UserId, " AND RU.USER_ID = "}
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
sql += " ORDER BY RFG.SEQUENCE,RLFG.SEQUENCE ";
return Dapper.Query<ViewRight>(sql, queryCondition);
}
public static List <RightFunction> QueryRightFunction(QueryCondition queryCondition)
{
var sql = @"
SELECT SYSID AS Sysid,FUNC_CODE AS FuncCode,FUNC_NAME AS FuncName,FUNC_PARAM AS FuncParam
,CREATED_ON AS CreatedOn,CREATED_BY AS CreatedBy,MODIFIED_ON AS ModifiedOn
,MODIFIED_BY AS ModifiedBy,RECORD_STATUS AS RecordStatus
FROM T_RIGHT_FUNCTION WITH(NOLOCK)
WHERE 1=1
";
var dict = new Dictionary <QueryConditionField, string>
{
{ QueryConditionField.Code, " AND FUNC_CODE = " },
{ QueryConditionField.Name, " AND FUNC_NAME = " }
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
sql += " ORDER BY RECORD_STATUS DESC,MODIFIED_ON DESC ";
return(Dapper.Query <RightFunction>(sql, queryCondition));
}
public static List<DictionaryItem> QueryDictionaryItem(QueryCondition queryCondition)
{
var sql = @"
SELECT A.SYSID AS Sysid,A.DICTIONARY_CODE AS DictionaryCode,A.CODE AS Code
,A.NAME AS Name,A.SEQUENCE AS Sequence,A.WORKSHOP AS Workshop
,A.STEP AS Step,A.REMARK AS Remark,A.CREATED_ON AS CreatedOn
,A.CREATED_BY AS CreatedBy,A.MODIFIED_ON AS ModifiedOn
,A.MODIFIED_BY AS ModifiedBy,A.RECORD_STATUS AS RecordStatus
,A.RESV01 AS Resv01,A.RESV02 AS Resv02,A.RESV03 AS Resv03
,A.RESV04 AS Resv04,A.RESV05 AS Resv05,A.RESV06 AS Resv06
,A.RESV07 AS Resv07,A.RESV08 AS Resv08,A.RESV09 AS Resv09
,A.RESV10 AS Resv10,B.NAME AS DictionaryName
FROM T_DICTIONARY_ITEM AS A WITH(NOLOCK)
INNER JOIN T_DICTIONARY_INFO AS B WITH(NOLOCK) ON
A.DICTIONARY_CODE = B.CODE
WHERE 1 = 1
";
var dict = new Dictionary<QueryConditionField, string>
{
{QueryConditionField.Code, " AND A.DICTIONARY_CODE = "},
{QueryConditionField.ReasonCategory, " AND B.CATEGORY = "}
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
sql += " ORDER BY A.SEQUENCE ";
return Dapper.Query<DictionaryItem>(sql, queryCondition);
}
public static List<RightUserGroup> QueryRightUserGroup(QueryCondition queryCondition)
{
var sql = @"
SELECT SYSID AS Sysid,GROUP_NAME AS GroupName,WORKSHOP AS Workshop,CREATED_ON AS CreatedOn,CREATED_BY AS CreatedBy,MODIFIED_ON AS ModifiedOn,MODIFIED_BY AS ModifiedBy,RECORD_STATUS AS RecordStatus
FROM T_RIGHT_USER_GROUP WITH(NOLOCK)
WHERE 1=1
";
var dict = new Dictionary<QueryConditionField, string>
{
{QueryConditionField.WORKSHOP, " AND WORKSHOP = "},
{QueryConditionField.Name, " AND GROUP_NAME = "}
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
sql += " ORDER BY RECORD_STATUS DESC,MODIFIED_ON DESC ";
return Dapper.Query<RightUserGroup>(sql, queryCondition);
}
public static bool IsAdmin(QueryCondition queryCondition)
{
var isAdmin = false;
var sql = @"
SELECT A.SYSID AS Sysid,A.USER_ID AS UserId,A.USER_NAME AS UserName,A.USER_PWD AS UserPwd,A.DEPARTMENT AS Department,A.EMAIL AS Email,A.CREATED_ON AS CreatedOn,A.CREATED_BY AS CreatedBy,A.MODIFIED_ON AS ModifiedOn,A.MODIFIED_BY AS ModifiedBy,A.RECORD_STATUS AS RecordStatus,A.RESV01 AS Resv01,A.RESV02 AS Resv02,A.RESV03 AS Resv03,A.RESV04 AS Resv04,A.RESV05 AS Resv05,A.RESV06 AS Resv06,A.RESV07 AS Resv07,A.RESV08 AS Resv08,A.RESV09 AS Resv09,A.RESV10 AS Resv10
FROM T_RIGHT_USER A WITH(NOLOCK)
INNER JOIN T_RIGHT_LK_USER_GROUP B WITH(NOLOCK) ON A.SYSID = B.USER_SYSID
INNER JOIN T_RIGHT_USER_GROUP C WITH(NOLOCK) ON B.GROUP_SYSID = C.SYSID
WHERE C.GROUP_NAME = '系统管理员'
";
var dict = new Dictionary<QueryConditionField, string>
{
{QueryConditionField.UserId, " AND A.USER_ID = "}
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
var userlist = Dapper.Query<RightUser>(sql, queryCondition);
if (userlist.Count > 0)
isAdmin = true;
return isAdmin;
}
public static List<RightUser> QueryRightUser(QueryCondition queryCondition)
{
var sql = @"
SELECT T_RIGHT_USER.SYSID AS Sysid,T_RIGHT_USER.USER_ID AS UserId,
T_RIGHT_USER.USER_NAME AS UserName,T_RIGHT_USER.USER_PWD AS UserPwd,
T_RIGHT_USER.DEPARTMENT AS Department,T_RIGHT_USER.EMAIL AS Email,
T_RIGHT_USER.CREATED_ON AS CreatedOn,T_RIGHT_USER.CREATED_BY AS CreatedBy,
T_RIGHT_USER.MODIFIED_ON AS ModifiedOn,T_RIGHT_USER.MODIFIED_BY AS ModifiedBy,
T_RIGHT_USER.RECORD_STATUS AS RecordStatus,
T_RIGHT_USER.RESV01 AS Resv01,T_RIGHT_USER.RESV02 AS Resv02,T_RIGHT_USER.RESV03 AS Resv03,T_RIGHT_USER.RESV04 AS Resv04,
T_RIGHT_USER.RESV05 AS Resv05,T_RIGHT_USER.RESV06 AS Resv06,T_RIGHT_USER.RESV07 AS Resv07,
T_RIGHT_USER.RESV08 AS Resv08,T_RIGHT_USER.RESV09 AS Resv09,T_RIGHT_USER.RESV10 AS Resv10,
T_DICTIONARY_ITEM.NAME DepartmentName
FROM T_RIGHT_USER WITH(NOLOCK)
LEFT JOIN T_DICTIONARY_ITEM WITH(NOLOCK) ON
T_RIGHT_USER.DEPARTMENT=T_DICTIONARY_ITEM.CODE
AND T_DICTIONARY_ITEM.DICTIONARY_CODE = 'Department'
WHERE 1 = 1 ";
var dict = new Dictionary<QueryConditionField, string>
{
{QueryConditionField.DepartmentCode, " AND T_RIGHT_USER.DEPARTMENT = "},
{QueryConditionField.UserId, " AND T_RIGHT_USER.USER_ID = "}
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
sql += " ORDER BY T_RIGHT_USER.RECORD_STATUS DESC,T_RIGHT_USER.MODIFIED_ON DESC ";
return Dapper.Query<RightUser>(sql, queryCondition);
}
public static List<RightFunction> QueryRightFunction(QueryCondition queryCondition)
{
var sql = @"
SELECT SYSID AS Sysid,FUNC_CODE AS FuncCode,FUNC_NAME AS FuncName,FUNC_PARAM AS FuncParam
,CREATED_ON AS CreatedOn,CREATED_BY AS CreatedBy,MODIFIED_ON AS ModifiedOn
,MODIFIED_BY AS ModifiedBy,RECORD_STATUS AS RecordStatus
FROM T_RIGHT_FUNCTION WITH(NOLOCK)
WHERE 1=1
";
var dict = new Dictionary<QueryConditionField, string>
{
{QueryConditionField.Code, " AND FUNC_CODE = "},
{QueryConditionField.Name, " AND FUNC_NAME = "}
};
var builder = new QueryConditionBuilder(queryCondition, dict);
sql += builder.Build();
sql += " ORDER BY RECORD_STATUS DESC,MODIFIED_ON DESC ";
return Dapper.Query<RightFunction>(sql, queryCondition);
}
