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));
}
/// <summary>
/// 使用 sql 语句来查询实体。
/// </summary>
/// <param name="sql">sql 语句，返回的结果集的字段，需要保证与属性映射的字段名相同。</param>
/// <param name="paging">分页信息。</param>
/// <param name="eagerLoad">需要贪婪加载的属性。</param>
/// <returns></returns>
public EntityList QueryList(FormattedSql sql, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
var args = new SqlQueryArgs(sql);
args.SetDataLoadOptions(paging, eagerLoad);
return(this.QueryList(args));
}
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 SqlGenerator()
{
_sql = new FormattedSql();
_sql.InnerWriter = new IndentedTextWriter(_sql.InnerWriter);
this.AutoQuota = true;
this.MaxItemsInInClause = int.MaxValue;
}
public virtual UserList GetAuthUserByPermissionId(long permissionId)
{
FormattedSql sql =
@"SELECT *
FROM T_USERS T
INNER JOIN T_RBAC_ORGPERMISSIONUSER T1 ON T.ID = T1.USERID
WHERE T.DBI_ISPHANTOM = 0 AND T1.DBI_ISPHANTOM = 0 AND T1.ORGPERMISSIONID = {0}";
sql.Parameters.Add(permissionId);
return((UserList)(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));
}
protected EntityList FetchBy(int userId)
{
FormattedSql sql = @"
SELECT *
FROM OrgPosition
left outer join OrgPositionUser on OrgPosition.Id = OrgPositionUser.OrgPositionId
where OrgPositionUser.UserId = {0}
";
sql.Parameters.Add(userId);
return(this.QueryList(sql));
}
public virtual OrgPositionList GetList(int userId)
{
FormattedSql sql = @"
SELECT *
FROM OrgPosition
left outer join OrgPositionUser on OrgPosition.Id = OrgPositionUser.OrgPositionId
where OrgPositionUser.UserId = {0}
";
sql.Parameters.Add(userId);
return((OrgPositionList)(this.DataQueryer as RdbDataQueryer).QueryData(sql));
}
protected override void Generate(AddNotNullConstraint op)
{
FormattedSql sql = string.Format(@"UPDATE {0} SET {1} = {2} WHERE {1} IS NULL",
this.Quote(op.TableName), this.Quote(op.ColumnName), "{0}");
sql.Parameters.Add(DbTypeHelper.GetDefaultValue(op.DataType));
this.AddRun(new FormattedSqlMigrationRun
{
Sql = sql
});
this.AddNotNullConstraint(op);
}
public void UtilsTest_DBA_FormattedSql_DbAccesserParameter()
{
var repo = RF.ResolveInstance <BookRepository>();
using (var dba = DbAccesserFactory.Create(repo))
{
FormattedSql sql = @"SELECT * FROM BOOK WHERE UpdatedTime < {0}";
sql.Parameters.Add(new DbAccesserParameter(DateTime.Now, DbType.DateTime));
using (var reader = dba.QueryDataReader(sql, sql.Parameters.ToArray()))
{
//do nothing;
}
}
}
/// <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 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);
}
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));
}
/// <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);
}
}
EntityList IRepositoryDataQueryAPI.QueryList(FormattedSql sql, PagingInfo paging, EagerLoadOptions eagerLoad)
{
return(this.QueryList(sql, paging, eagerLoad));
}
/// <summary>
/// 使用 sql 语句来查询数据表。
/// </summary>
/// <param name="sql">Sql 语句.</param>
/// <param name="paging">分页信息。</param>
/// <returns></returns>
public LiteDataTable QueryTable(FormattedSql sql, PagingInfo paging = null)
{
return(this.QueryTable(new TableQueryArgs(sql, paging)));
}
/// <summary>
/// 使用 sql 语句来查询数据表。
/// </summary>
/// <param name="sql">Sql 语句.</param>
/// <param name="paging">分页信息。</param>
/// <returns></returns>
protected LiteDataTable QueryTable(FormattedSql sql, PagingInfo paging = null)
{
return(this.DataQueryer.QueryTable(sql, paging));
}
LiteDataTable IRepositoryDataQueryAPI.QueryTable(FormattedSql sql, PagingInfo paging)
{
return(this.QueryTable(sql, paging));
}
/// <summary>
/// 执行 Sql 并读取 DataReader 中的值到实体。
/// </summary>
/// <param name="dba">The dba.</param>
/// <param name="args">The arguments.</param>
/// <param name="readType">Type of the read.</param>
/// <param name="sql">The SQL.</param>
protected void QueryDataReader(IDbAccesser dba, IEntitySelectArgs args, ReadDataType readType, FormattedSql sql)
{
//查询数据库
using (var reader = dba.QueryDataReader(sql, sql.Parameters))
{
//填充到列表中。
this.FillDataIntoList(
reader,
readType,
args.List,
args.FetchingFirst,
args.PagingInfo,
args.MarkTreeFullLoaded
);
}
}
/// <summary>
/// 从持久层中查询数据。
/// 本方法只能由仓库中的方法来调用。本方法的返回值的类型将与仓库中方法的返回值保持一致。
/// 支持的返回值：EntityList、Entity、int、LiteDataTable。
/// </summary>
/// <param name="sql"></param>
/// <param name="paging"></param>
/// <param name="eagerLoad"></param>
/// <returns></returns>
public object QueryData(FormattedSql sql, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
return(this.DataQueryer.QueryData(sql, paging, eagerLoad));
}
/// <summary>
/// 通过一个 FormattedSql 来构造。
/// </summary>
/// <param name="sql"></param>
public TableQueryArgs(FormattedSql sql) : this(sql, sql.Parameters)
{
}
/// <summary>
/// 通过 FormattedSql 及分页信息来构造。
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="pagingInfo">The paging information.</param>
public TableQueryArgs(FormattedSql sql, PagingInfo pagingInfo)
: this(sql, sql.Parameters)
{
this.PagingInfo = pagingInfo;
}
protected SqlGenerator()
{
_sql = new FormattedSql();
_sql.InnerWriter = new IndentedTextWriter(_sql.InnerWriter);
}
public SqlGenerator()
{
_sql = new FormattedSql();
_sql.InnerWriter = new IndentedTextWriter(_sql.InnerWriter);
this.AutoQuota = true;
}
internal static FormattedSql AppendQuote(this FormattedSql sql, DbTable table, string identifier)
{
table.AppendQuote(sql.InnerWriter, identifier);
return(sql);
}
internal static FormattedSql AppendQuoteName(this FormattedSql sql, DbTable table)
{
table.AppendQuote(sql.InnerWriter, table.Name);
return(sql);
}
/// <summary>
/// 使用 sql 语句来查询实体。
/// </summary>
/// <param name="sql">sql 语句，返回的结果集的字段，需要保证与属性映射的字段名相同。</param>
/// <param name="paging">分页信息。</param>
/// <param name="eagerLoad">需要贪婪加载的属性。</param>
/// <returns></returns>
protected EntityList QueryList(FormattedSql sql, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
return(this.DataQueryer.QueryList(sql, paging, eagerLoad));
}
