public IEnumerable <T> PurviewcodeGetList <T>(int?R_ID = null, int?U_Id = null) where T : class
{
var client = DBProxy.CreateClient();
var sql = new StringBuilder(@"SELECT
DISTINCT
smp.MPC_ID
,smp.MPC_NAME
,smp.MPC_NAME_C
,smp.MPC_NAME_J
,smp.MPC_CODE
,sm.M_NAME
,sm.M_NAME_C
FROM SS_MENU_PURVIEWCODE smp
LEFT JOIN SS_ROLE_MENU_PURVIEWCODE srmp
ON smp.MPC_CODE = srmp.MPC_CODE
LEFT JOIN SS_MENU sm
ON smp.M_ID = sm.M_ID
WHERE smp.MPC_DISABLED = 0
AND sm.M_DISABLED = 0
");
var param = new DynamicParameters();
if (R_ID.HasValue)
{
sql.Append(" AND srmp.R_ID = @R_ID");
param.Add("@R_ID", R_ID.Value);
}
if (U_Id.HasValue)
{
var rIds = RepoBase.Instance.GetWhere <SS_ROLE_USER>(x => x.U_ID == U_Id.Value).Select(x => x.R_ID);
sql.Append(" AND srmp.R_ID IN @R_ID");
param.Add("@R_ID", rIds);
}
return(client.Query <T>(sql.ToString(), param));
}
public T GetModelByUserName <T>(UserInfoGetRequest req) where T : class
{
var client = DBProxy.CreateClient();
var sql = new StringBuilder(@"SELECT * FROM SS_USER su WHERE 1=1 ");
var param = new DynamicParameters();
if (req.U_ID.HasValue)
{
sql.Append(" AND U_ID=@U_ID");
param.Add("@U_ID", req.U_ID);
}
return(client.Query <T>(sql.ToString(), param).FirstOrDefault());
}
internal virtual Tuple <int, List <TOut> > QueryPage <TIn, TOut>(StringBuilder sql, DynamicParameters param, string orderSql = null, int?startSize = null, int?pageSize = null, EnumDBType?dbType = null)
where TIn : EntityBase
where TOut : class
{
var dbClient = DBProxy.CreateClient(dbType);
//计数
var countSql = $"select count(*) from ({sql.ToString()})tCount;";
var totalCount = dbClient.ExecuteScalar(countSql, param);
//分页
if (pageSize.HasValue)
{
sql = new StringBuilder($"{sql.ToString()} {orderSql} OFFSET {startSize} ROW FETCH NEXT {pageSize} ROW ONLY ;");
}
var lst = dbClient.Query <TOut>(sql.ToString(), param).ToList();
return(new Tuple <int, List <TOut> >(Convert.ToInt32(totalCount), lst));
}
public int Delete(int?R_ID, string MPC_CODE = null)
{
var client = DBProxy.CreateClient();
var sql = new StringBuilder(@"DELETE FROM SS_ROLE_MENU_PURVIEWCODE WHERE 1=1 ");
var param = new DynamicParameters();
if (R_ID.HasValue)
{
sql.Append(" AND R_ID=@R_ID");
param.Add("@R_ID", R_ID);
}
if (!string.IsNullOrEmpty(MPC_CODE))
{
sql.Append(" AND MPC_CODE=@MPC_CODE");
param.Add("@MPC_CODE", MPC_CODE);
}
return(client.Execute(sql.ToString(), param));
}
public IEnumerable <T> GetList <T>(RoleGetListRequest req) where T : class
{
var client = DBProxy.CreateClient();
var sql = new StringBuilder(@"SELECT
*
FROM SS_ROLE sr
WHERE 1 = 1
");
var param = new DynamicParameters();
if (!string.IsNullOrEmpty(req.R_NAME))
{
sql.Append(" AND sr.R_NAME LIKE @R_NAME");
param.Add("@R_NAME", $"%{req.R_NAME}%");
}
sql.Append(" ORDER BY sr.R_ORDERID");
return(client.Query <T>(sql.ToString(), param));
}
public int Delete(int?uid = null, int?rid = null)
{
var client = DBProxy.CreateClient();
var sql = new StringBuilder(@"DELETE FROM SS_ROLE_USER WHERE 1=1");
var param = new DynamicParameters();
if (uid.HasValue)
{
sql.Append(" AND U_ID=@U_ID");
param.Add("@U_ID", uid);
}
if (rid.HasValue)
{
sql.Append(" AND R_ID=@R_ID");
param.Add("@R_ID", rid);
}
return(client.Execute(sql.ToString(), param));
}
public IEnumerable <T> GetList <T>(UserInfoGetListRequest req) where T : class
{
var client = DBProxy.CreateClient();
var sql = new StringBuilder(@"SELECT * FROM SS_USER su WHERE 1=1 ");
var param = new DynamicParameters();
if (!string.IsNullOrEmpty(req.U_NAME))
{
sql.Append(" AND su.U_NAME LIKE @U_NAME");
param.Add("@U_NAME", $"%{req.U_NAME}%");
}
if (!string.IsNullOrEmpty(req.U_NAME))
{
sql.Append(" AND su.U_REALNAME LIKE @U_REALNAME");
param.Add("@U_REALNAME", $"%{req.U_REALNAME}%");
}
return(client.Query <T>(sql.ToString(), param));
}
public IEnumerable <T> MemberGetList <T>(MemberGetListRequest req) where T : class
{
var client = DBProxy.CreateClient();
var sql = new StringBuilder(@"SELECT
*
FROM SS_USER su
LEFT JOIN SS_ROLE_USER sru
ON su.U_ID = sru.U_ID
WHERE 1 = 1
");
var param = new DynamicParameters();
if (req.R_ID.HasValue)
{
sql.Append(" AND sru.R_ID = @R_ID");
param.Add("@R_ID", req.R_ID);
}
return(client.Query <T>(sql.ToString(), param));
}
public IEnumerable <T> GetListByUid <T>(UserInfoGetRequest req) where T : class
{
var client = DBProxy.CreateClient();
var sql = new StringBuilder(@"SELECT
sr.R_ID,
sr.R_NAME,
sr.R_DESC,
sr.Branch_ID,
sr.R_ORDERID
FROM SS_ROLE sr
LEFT JOIN SS_ROLE_USER sru
ON sr.R_ID = sru.R_ID
WHERE 1 = 1
");
var param = new DynamicParameters();
if (req.U_ID.HasValue)
{
sql.Append(" AND sru.U_ID = @U_ID");
param.Add("@U_ID", req.U_ID);
}
return(client.Query <T>(sql.ToString(), param));
}
