public virtual UserList GetUserListByRoleCodeAndSaleInfoID(string roleCode, long saleInfoID)
{
if (string.IsNullOrEmpty(roleCode))
{
throw new ArgumentNullException("角色编码不能为空！");
}
if (saleInfoID == 0)
{
throw new ArgumentNullException("销方的主键不能为空！");
}
//查找消息的接收人，接收人必须满足2个条件
//1、接受用户必须是认证管理员
//2、接受用户必须是该销方下的用户
FormattedSql sql =
@"select * from t_users t inner join (select t1.UserID from (select UserID from t_rbac_organizationuser
where organizationid =(select id from t_rbac_organization
where id = (select Organizationid from t_saleinfo where";
sql.Append(" id = " + saleInfoID.ToString());
sql.Append(@"and wf_approvalstatus = 300 and dbi_isphantom = 0) and wf_approvalstatus = 300 and dbi_isphantom = 0)
and dbi_isphantom = 0) t1 inner join(select UserID from t_rbac_userrole where RoleID in(select id from t_rbac_role where ");
sql.Append("Code = '" + roleCode + "'");
sql.Append("and dbi_isphantom = 0) and dbi_isphantom = 0) t2 on t1.UserID = t2.UserID) t3 on t.id = t3.UserID");
return((UserList)(this.DataQueryer as RdbDataQueryer).QueryData(sql));
}
public virtual List <OrganizationNotTree> GetByUserName(string userName)
{
FormattedSql sql =
@"SELECT org.Id,org.Name
FROM T_RBAC_Organization org
LEFT JOIN T_RBAC_OrganizationUser ou on (org.id = ou.organizationid and ou.dbi_isphantom = 0)
LEFT JOIN T_Users u on u.id = ou.userid and u.dbi_isphantom = 0 and u.wf_approvalstatus = 300
where org.wf_approvalstatus = 300 and(org.DBI_IsPhantom = 0 or org.DBI_IsPhantom is null) ";
if (!string.IsNullOrEmpty(userName))
{
sql.Append(string.Format("and u.username = '******' ", userName));
}
sql.Append(" order by org.treeindex ");
LiteDataTable dt = (this.DataQueryer as RdbDataQueryer).QueryTable(sql);
List <OrganizationNotTree> result = new List <OrganizationNotTree>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (var row in dt.Rows)
{
result.Add(new OrganizationNotTree {
Id = Convert.ToInt64(row["Id"]), Name = Convert.ToString(row["Name"])
});
}
}
return(result);
//return (OrganizationList)(this.DataQueryer as RdbDataQueryer).QueryData(sql);
}
public virtual UserRelationList GetRelationByUserId(long userId, PagingInfo pagingInfo = null)
{
/*********************** 修改描述 *****************
* 2016-10-09
* 赵朋
* 注释掉169至178行，增加180至202行
* 被修改的临时用户数据，也会有查看操作，下面169至178行的SQL只能查询出已生效的用户，
* 所以在选中临时用户数据点击查看按钮时,弹出窗体的组织列表加载不到数据。
********************************************************/
//FormattedSql sql =
// @" SELECT A.EmployeeNumber, A.UserName,A.RealName,A.WF_ApprovedTime,
// E.Name AS OrganizationName,E.Code AS OrganizationCode,
// NULL AS DBI_CreatedTime, NULL AS DBI_UpdatedTime,NULL AS DBI_CreatedUser,NULL AS DBI_UpdatedUser,
// 0 AS DBI_IsPhantom,ROW_NUMBER() over(order by UserName) AS Id,
// NULL AS RoleName , NULL AS SaleInfoName
// FROM T_Users A
// LEFT JOIN T_RBAC_OrganizationUser D ON A.Id=D.UserId AND (D.DBI_IsPhantom=0 OR D.DBI_IsPhantom IS NULL)
// LEFT JOIN T_RBAC_Organization E ON D.OrganizationId=E.Id AND E.WF_ApprovalStatus=300 AND (E.DBI_IsPhantom=0 OR E.DBI_IsPhantom IS NULL)
// WHERE A.DBI_IsPhantom=0 AND A.WF_ApprovalStatus=300 ";
FormattedSql sql = @"SELECT T2.EmployeeNumber,
T2.UserName,
T2.RealName,
T2.WF_ApprovedTime,
T.Name AS OrganizationName,
T.Code AS OrganizationCode,
NULL AS DBI_CreatedTime,
NULL AS DBI_UpdatedTime,
NULL AS DBI_CreatedUser,
NULL AS DBI_UpdatedUser,
0 AS DBI_IsPhantom,
ROW_NUMBER() over(order by UserName) AS Id,
NULL AS RoleName,
NULL AS SaleInfoName
FROM T_RBAC_ORGANIZATION T
INNER JOIN T_RBAC_OrganizationUser T1 ON T.ID = T1.ORGANIZATIONID
AND (T1.DBI_IsPhantom = 0 OR
T1.DBI_IsPhantom IS NULL)
INNER JOIN T_USERS T2 ON T2.ID = T1.USERID
AND (T2.DBI_IsPhantom = 0 OR T2.DBI_IsPhantom IS NULL)
WHERE T.DBI_IsPhantom = 0
AND T.WF_ApprovalStatus = 300
AND (T.DBI_IsPhantom = 0 OR T.DBI_IsPhantom IS NULL) ";
if (userId != 0)
{
sql.Append(" AND T2.ID =" + userId + "");
}
sql.Append(" ORDER BY T2.UserName, T.Name ");
return((UserRelationList)(this.DataQueryer as RdbDataQueryer).QueryData(sql, pagingInfo));
}
/// <summary>
/// 子类重写此方法实现批量删除逻辑。
/// </summary>
/// <param name="batch"></param>
protected virtual void ImportDelete(EntityBatch batch)
{
foreach (var section in this.EnumerateAllBatches(batch.DeleteBatch, 1000))
{
FormattedSql sqlDelete = null;
if (batch.Repository.EntityMeta.IsPhantomEnabled)
{
sqlDelete = "UPDATE " + batch.Table.Name + " SET ISPHANTOM = {0} WHERE ID IN (";
sqlDelete.Parameters.Add(BooleanBoxes.True);
}
else
{
sqlDelete = "DELETE FROM " + batch.Table.Name + " WHERE ID IN (";
}
bool needDelimiter = false;
for (int i = 0, c = section.Count; i < c; i++)
{
var item = section[i];
if (i > 0)
{
sqlDelete.Append(',');
}
else
{
var keyType = item.KeyProvider.KeyType;
needDelimiter = keyType.IsClass;
}
if (needDelimiter)
{
sqlDelete.Append('\'').Append(item.Id).Append('\'');
}
else
{
sqlDelete.Append(item.Id);
}
}
sqlDelete.Append(')');
batch.DBA.ExecuteText(sqlDelete, sqlDelete.Parameters);
}
}
public virtual List <OrganizationNotTree> GetByOrgIdAndUserNameContainChild(long orgId, string userName = "")
{
FormattedSql sql = new FormattedSql();
var dbSettingName = RBACPlugin.DbSettingName;
var dbSetting = DbSetting.FindOrCreate(dbSettingName);
if (DbSetting.IsOracleProvider(dbSetting))
{
sql =
@"select oorg.Id,oorg.Name from T_RBAC_ORGANIZATION oorg
LEFT JOIN T_RBAC_OrganizationUser ou on(oorg.id = ou.organizationid and ou.dbi_isphantom = 0)
LEFT JOIN T_Users u on(u.id = ou.userid and u.dbi_isphantom = 0)
where(oorg.dbi_isphantom = 0 or oorg.dbi_isphantom is null) and oorg.wf_approvalstatus = 300
and oorg.treeindex like (select treeindex || '%' from T_RBAC_ORGANIZATION org where org.id = {0}) ";
}
else
{
sql =
@"select oorg.Id,oorg.Name from T_RBAC_ORGANIZATION oorg
LEFT JOIN T_RBAC_OrganizationUser ou on(oorg.id = ou.organizationid and ou.dbi_isphantom = 0)
LEFT JOIN T_Users u on(u.id = ou.userid and u.dbi_isphantom = 0)
where(oorg.dbi_isphantom = 0 or oorg.dbi_isphantom is null) and oorg.wf_approvalstatus = 300
and oorg.treeindex like (select treeindex + '%' from T_RBAC_ORGANIZATION org where org.id = {0}) ";
}
if (!string.IsNullOrEmpty(userName))
{
sql.Append(string.Format("and u.username = '******' ", userName));
}
sql.Parameters.Add(orgId);
LiteDataTable dt = (this.DataQueryer as RdbDataQueryer).QueryTable(sql);
List <OrganizationNotTree> result = new List <OrganizationNotTree>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (var row in dt.Rows)
{
result.Add(new OrganizationNotTree {
Id = Convert.ToInt64(row["Id"]), Name = Convert.ToString(row["Name"])
});
}
}
return(result);
//return (OrganizationList)(this.DataQueryer as RdbDataQueryer).QueryData(sql);
}
/// <summary>
/// 子类重写此方法实现批量删除逻辑。
/// </summary>
/// <param name="batch"></param>
protected virtual void ImportDelete(EntityBatch batch)
{
foreach (var section in this.EnumerateAllBatches(batch.DeleteBatch, 1000))
{
var sqlDelete = new FormattedSql();
if (batch.Repository.EntityMeta.IsPhantomEnabled)
{
var isPhantomColumn = batch.Table.FindByPropertyName(EntityConvention.Property_IsPhantom.Name).Name;
var value = this.SqlGenerator.DbTypeCoverter.ToDbParameterValue(BooleanBoxes.True);
sqlDelete.Append("UPDATE ").Append(batch.Table.Name).Append(" SET ")
.Append(isPhantomColumn).Append(" = ").AppendParameter(value)
.Append(" WHERE ID IN (");
}
else
{
sqlDelete.Append("DELETE FROM ").Append(batch.Table.Name).Append(" WHERE ID IN (");
}
bool needDelimiter = false;
for (int i = 0, c = section.Count; i < c; i++)
{
var item = section[i];
if (i > 0)
{
sqlDelete.Append(',');
}
else
{
var keyType = (item as IEntity).IdProvider.KeyType;
needDelimiter = keyType.IsClass;
}
if (needDelimiter)
{
sqlDelete.Append('\'').Append(item.Id).Append('\'');
}
else
{
sqlDelete.Append(item.Id);
}
}
sqlDelete.Append(')');
batch.DBA.ExecuteText(sqlDelete, sqlDelete.Parameters);
}
}
public virtual UserRelationList GetRelationBy(long userId, string orgName, string saleInfoName, string userName, string roleName, PagingInfo pagingInfo = null)
{
FormattedSql sql =
@" SELECT A.EmployeeNumber, A.UserName,A.RealName,A.WF_ApprovedTime,
C.Name AS RoleName,
E.Name AS OrganizationName,E.Code AS OrganizationCode,
F.Name AS SaleInfoName,
NULL AS DBI_CreatedTime, NULL AS DBI_UpdatedTime,NULL AS DBI_CreatedUser,NULL AS DBI_UpdatedUser,
0 AS DBI_IsPhantom,ROW_NUMBER() over(order by UserName) AS Id
FROM T_Users A
LEFT JOIN T_RBAC_UserRole B ON A.Id=B.UserId AND (B.DBI_IsPhantom=0 OR B.DBI_IsPhantom IS NULL)
LEFT JOIN T_RBAC_Role C ON B.RoleId=C.Id AND (C.DBI_IsPhantom=0 OR C.DBI_IsPhantom IS NULL) AND C.WF_APPROVALSTATUS = 300
LEFT JOIN T_RBAC_OrganizationUser D ON A.Id=D.UserId AND (D.DBI_IsPhantom=0 OR D.DBI_IsPhantom IS NULL)
LEFT JOIN T_RBAC_Organization E ON D.OrganizationId=E.Id AND (E.DBI_IsPhantom=0 OR E.DBI_IsPhantom IS NULL) AND E.WF_APPROVALSTATUS = 300
LEFT JOIN T_SaleInfo F ON F.OrganizationId=E.Id AND (F.DBI_IsPhantom=0 OR F.DBI_IsPhantom IS NULL) and F.WF_APPROVALSTATUS = 300
WHERE A.DBI_IsPhantom=0 AND A.WF_ApprovalStatus=300 ";
if (userId != 0)
{
sql.Append(" AND A.Id =" + userId + "");
}
if (!String.IsNullOrEmpty(orgName))
{
sql.Append(" AND E.Name LIKE '%" + orgName + "%' ");
}
if (!String.IsNullOrEmpty(saleInfoName))
{
sql.Append(" AND F.Name LIKE '%" + saleInfoName + "%' ");
}
if (!String.IsNullOrEmpty(userName))
{
sql.Append(" AND UserName LIKE '%" + userName + "%' ");
}
if (!String.IsNullOrEmpty(roleName))
{
sql.Append(" AND C.Name LIKE '%" + roleName + "%' ");
}
sql.Append(" ORDER BY A.UserName,C.Name,E.Name,F.Name ");
return((UserRelationList)(this.DataQueryer as RdbDataQueryer).QueryData(sql, pagingInfo));
}
protected override SqlLiteral VisitSqlLiteral(SqlLiteral sqlLiteral)
{
if (sqlLiteral.Parameters != null && sqlLiteral.Parameters.Length > 0)
{
sqlLiteral.FormattedSql = Regex.Replace(sqlLiteral.FormattedSql, @"\{(?<index>\d+)\}", m =>
{
var index = Convert.ToInt32(m.Groups["index"].Value);
var value = sqlLiteral.Parameters[index];
index = _sql.Parameters.Add(value);
return("{" + index + "}");
});
}
_sql.Append(sqlLiteral.FormattedSql);
return(sqlLiteral);
}
