/// <summary>
/// 根据机构ID获取对应关系的用户简单对象列表
/// </summary>
/// <param name="ouID">机构ID</param>
/// <returns></returns>
public List <SimpleUserInfo> GetSimpleUsersByOUId(Int32 ouId, IsDelete isDelete = IsDelete.否)
{
string sql = string.Format(@"Select Id,Name,Password,LoginName,UserCode,MobilePhone,Email From {1}
Inner Join {0}OU_User ON {1}.Id=UserId Where (IsDelete = {3} or 0 = {3}) AND {0}OU_User.OuId = {2}", SQLServerPortal.gc._securityTablePre, tableName, ouId, (short)isDelete);
return(FillSimpleUsers(sql));
}
public List <SimpleUserInfo> GetSimpleUsersByRoleId(Int32 roleId, IsDelete isDelete = IsDelete.否)
{
string sql = string.Format(@"Select Id,Name,Password,LoginName,UserCode,MobilePhone,Email From {1}
INNER JOIN {0}User_Role ON {1}.Id=UserId Where (IsDelete = {3} or 0 = {3}) AND {0}User_Role.RoleId ={2} ", SQLServerPortal.gc._securityTablePre, tableName, roleId, (short)isDelete);
return(this.FillSimpleUsers(sql));
}
public List <UserInfo> GetUsersByOUId(Int32 ouId, IsDelete isDelete = IsDelete.否)
{
string sql = string.Format(@"SELECT * FROM {1} INNER JOIN {0}OU_User On {1}.Id={0}OU_User.UserId
WHERE (IsDelete = {3} or 0 = {3}) AND {0}OU_User.OuId ={2} ", SQLServerPortal.gc._securityTablePre, tableName, ouId, (short)isDelete);
return(GetList(sql, null));
}
public List <UserInfo> GetUsersByRoleId(Int32 roleId, IsDelete isDelete = IsDelete.否)
{
string sql = string.Format(@"SELECT * FROM {1} INNER JOIN {0}User_Role On {1}.Id={0}User_Role.UserId
WHERE (IsDelete = {3} or 0 = {3}) AND {0}User_Role.RoleId = {2}", SQLServerPortal.gc._securityTablePre, tableName, roleId, (short)isDelete);
return(this.GetList(sql, null));
}
/// <summary>
/// 获取指定机构下面的树形列表
/// </summary>
/// <param name="mainOUId">指定机构Id</param>
public List <OUNodeInfo> GetTreeById(int mainOUId, IsDelete isDelete = IsDelete.否, IsForbid isForbid = IsForbid.否)
{
string sql = string.Format("Select * From {0} WHERE (IsDelete = {1} or 0 = {1}) and (IsForbid = {2} or 0 = {2}) Order By Pid, Name ", tableName, (short)isDelete, (short)isForbid);
DataTable dt = SqlTable(sql);
return(GetRecursionOu(dt, mainOUId));
}
public List <RoleInfo> GetRolesByOUId(Int32 ouId, IsDelete isDelete = IsDelete.否, IsForbid isForbid = IsForbid.否)
{
string sql = string.Format(@"SELECT a.Id, a.RoleCode, a.Name, a.Remark, a.Seq,
a.CompanyId, a.CompanyName, a.CreatorId, a.CreatorTime, a.EditorId,
a.LastUpdateTime, a.IsDelete, a.IsForbid
FROM {0}Role a INNER JOIN {0}OU_Role b ON a.Id=b.RoleId WHERE b.OuId = {1} and (a.IsDelete = {2} or 0 = {2}) and (a.IsForbid = {3} or 0 = {3})", SQLServerPortal.gc._securityTablePre, ouId, (short)isDelete, (short)isForbid);
return(this.GetList(sql, null));
}
public Task <List <Invoice> > GetAllAsync(InvoiceStatus invStatus, IsDelete status = IsDelete.Normal, bool tracking = false)
{
IQueryable <Invoice> query = _DbContext.Invoice;
if (tracking)
{
}
else
{
query = query.AsNoTracking();
}
query = query.Where(h => h.IsDeleted == (int)status && h.Status == (int)invStatus).OrderBy(h => h.CreationDate);
return(query.ToListAsync());
}
public Task <List <ImportExportHistory> > GetAllAsync(IsDelete status = IsDelete.Normal, bool tracking = false)
{
IQueryable <ImportExportHistory> query = _DbContext.ImportExportHistory;
if (tracking)
{
}
else
{
query = query.AsNoTracking();
}
query = query.Where(h => h.IsDeleted == (int)status).OrderByDescending(h => h.CreationDate);
return(query.ToListAsync());
}
public Task <List <ItemDiscount> > GetAllAsync(IsDelete status = IsDelete.Normal, bool tracking = false)
{
IQueryable <ItemDiscount> query = _DbContext.ItemDiscount;
if (tracking)
{
}
else
{
query = query.AsNoTracking();
}
query = query.Where(h => h.IsDeleted == (int)status);
return(query.ToListAsync());
}
private OUNodeInfo GetNode(Int32 Id, DataTable dt, IsDelete isDelete = IsDelete.否, IsForbid isForbid = IsForbid.否)
{
OUInfo ouInfo = this.FindById(Id);
OUNodeInfo ouNodeInfo = new OUNodeInfo(ouInfo);
string sort = string.Format("{0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
DataRow[] dChildRows = dt.Select(string.Format(" Pid={0} and (IsDelete = {1} or 0 = {1}) and (IsForbid = {2} or 0 = {2})", Id, (short)isDelete, (short)isForbid), sort);
for (int i = 0; i < dChildRows.Length; i++)
{
Int32 childId = dChildRows[i]["Id"].ToString().ToInt32();
OUNodeInfo childNodeInfo = GetNode(childId, dt);
ouNodeInfo.Children.Add(childNodeInfo);
}
return(ouNodeInfo);
}
/// <summary>
/// 根据指定机构节点ID，获取其下面所有机构列表
/// </summary>
/// <param name="parentId">指定机构节点ID</param>
/// <returns></returns>
public List <OUInfo> GetAllOUsByParent(Int32 parentId, IsDelete isDelete = IsDelete.否, IsForbid isForbid = IsForbid.否)
{
List <OUInfo> list = new List <OUInfo>();
string sql = string.Format("Select * From {0} Where (IsDelete = {1} or 0 = {1}) and (IsForbid = {2} or 0 = {2}) Order By Pid, Name ", tableName, (short)isDelete, (short)isForbid);
DataTable dt = SqlTable(sql);
string sort = string.Format("{0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
DataRow[] dataRows = dt.Select(string.Format(" Pid = {0}", parentId), sort);
for (Int32 i = 0; i < dataRows.Length; i++)
{
Int32 id = dataRows[i]["Id"].ToString().ToInt32();
list.AddRange(GetOU(id, dt));
}
return(list);
}
public Task <List <Table> > GetTableByZoneAsync(string zoneId, IsDelete status = IsDelete.Normal, bool tracking = false)
{
IQueryable <Table> query = _DbContext.Table;
if (tracking)
{
}
else
{
query = query.AsNoTracking();
}
query = query.Where(h => h.IsDeleted == (int)status);
query = query.Where(h => h.FkZone == zoneId);
return(query.ToListAsync());
}
public Task <List <ItemDiscount> > GetAsync(string itemId, IsDelete status = IsDelete.Normal, bool tracking = true)
{
IQueryable <ItemDiscount> query = _DbContext.ItemDiscount;
if (tracking)
{
}
else
{
query = query.AsNoTracking();
}
query = query.Where(h => h.IsDeleted == (int)status);
var item = query.Where(h => h.FkItem == itemId);
return(item.ToListAsync());
}
/// <summary>
/// 获取树形结构的机构列表
/// </summary>
public List <OUNodeInfo> GetTree(IsDelete isDelete = IsDelete.否, IsForbid isForbid = IsForbid.否)
{
List <OUNodeInfo> arrReturn = new List <OUNodeInfo>();
string sql = string.Format("Select * From {0} Order By Pid, Name ", tableName);
DataTable dt = base.SqlTable(sql);
string sort = string.Format("{0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
DataRow[] dataRows = dt.Select(string.Format(" Pid = {0} and (IsDelete = {1} or 0 = {1}) and (IsForbid = {2} or 0 = {2})", -1, (short)isDelete, (short)isForbid), sort);
for (int i = 0; i < dataRows.Length; i++)
{
Int32 id = dataRows[i]["Id"].ToString().ToInt32();
OUNodeInfo menuNodeInfo = GetNode(id, dt);
arrReturn.Add(menuNodeInfo);
}
return(arrReturn);
}
public Task <ImportExportHistory> GetAsync(string id, IsDelete status = IsDelete.Normal, bool tracking = true)
{
IQueryable <ImportExportHistory> query = _DbContext.ImportExportHistory;
if (tracking)
{
}
else
{
query = query.AsNoTracking();
}
query = query.Where(h => h.IsDeleted == (int)status);
var item = query.FirstOrDefaultAsync(h => h.Id == id);
return(item);
}
private List <OUInfo> GetOU(Int32 Id, DataTable dt, IsDelete isDelete = IsDelete.否, IsForbid isForbid = IsForbid.否)
{
List <OUInfo> list = new List <OUInfo>();
OUInfo ouInfo = this.FindById(Id);
list.Add(ouInfo);
string sort = string.Format("{0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
DataRow[] dChildRows = dt.Select(string.Format(" Pid={0} and (IsDelete = {1} or 0 = {1}) and (IsForbid = {2} or 0 = {2})", Id, (short)isDelete, (short)isForbid), sort);
for (Int32 i = 0; i < dChildRows.Length; i++)
{
Int32 childId = dChildRows[i]["Id"].ToString().ToInt32();
List <OUInfo> childList = GetOU(childId, dt);
list.AddRange(childList);
}
return(list);
}
/// <summary>
/// 根据查询条件获取简单用户对象列表
/// </summary>
/// <param name="condition">查询条件</param>
/// <returns></returns>
public List <SimpleUserInfo> FindSimpleUsers(string condition, IsDelete isDelete = IsDelete.否)
{
if (HasInjectionData(condition))
{
LogHelper.WriteLog(LogLevel.LOG_LEVEL_ERR, string.Format("检测出SQL注入的恶意数据, {0}", condition), typeof(User));
throw new Exception("检测出SQL注入的恶意数据");
}
//串连条件语句为一个完整的Sql语句
string sql = string.Format("Select Id,UserCode,Name,LoginName,Password,MobilePhone,Email From {0} Where (IsDelete = {1} or 0 = {1}) ", tableName, (short)isDelete);
if (!string.IsNullOrEmpty(condition))
{
sql += string.Format(" AND {0} ", condition);
}
sql += string.Format(" Order by {0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
return(FillSimpleUsers(sql));
}
private List <OUNodeInfo> GetRecursionOu(DataTable dt, Int32 mainOUId, IsDelete isDelete = IsDelete.否, IsForbid isForbid = IsForbid.否)
{
List <OUNodeInfo> arrReturn = new List <OUNodeInfo>();
string sort = string.Format("{0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
DataRow[] dataRows = dt.Select(string.Format(" Pid = {0} and (IsDelete = {1} or 0 = {1}) and (IsForbid = {2} or 0 = {2})", mainOUId, (short)isDelete, (short)isForbid), sort);
for (int i = 0; i < dataRows.Length; i++)
{
Int32 Id = dataRows[i]["Id"].ToString().ToInt32();
OUNodeInfo menuNodeInfo = GetNode(Id, dt);
arrReturn.Add(menuNodeInfo);
// 判断是否存在子节点，如果存在递归
DataRow[] dataRowsChildNodes = dt.Select(string.Format(" Pid = {0} and (IsDelete = {1} or 0 = {1}) and (IsForbid = {2} or 0 = {2})", Id, (short)isDelete, (short)isForbid), sort);
if (dataRowsChildNodes.Length > 0)
{
menuNodeInfo.Children = GetRecursionOu(dt, Id);
}
}
return(arrReturn);
}
public List <OUInfo> GetOUsByUser(Int32 userId, IsDelete isDelete = IsDelete.否, IsForbid isForbid = IsForbid.否)
{
string sql = string.Format("SELECT * FROM {0}OU a INNER JOIN {0}OU_User On a.Id={0}OU_User.OuId WHERE UserId = {1} and (a.IsDelete = {2} or 0 = {2}) and (a.IsForbid = {3} or 0 = {3})", SQLServerPortal.gc._securityTablePre, userId, (short)isDelete, (short)isForbid);
return(this.GetList(sql, null));
}
/// <summary>
/// 根据指定的父ID获取其下面一级（仅限一级）的菜单列表
/// </summary>
/// <param name="PID">菜单父ID</param>
public List <MenuInfo> GetMenuById(string Pgid, IsVisable isVisable = IsVisable.是, IsDelete isDelete = IsDelete.否)
{
string sql = string.Format(@"Select t.*,case Pgid when '-1' then '0' else Pgid end as parentId From {1} t
Where Pgid='{0}' and (IsVisable = {2} or 0 = {2}) and (IsDelete = {3} or 0 = {3}) Order By Seq ", Pgid, tableName, (short)isVisable, (short)isDelete);
return(GetList(sql, null));
}
/// <summary>
/// 获取树形结构的菜单列表
/// </summary>
public List <MenuNodeInfo> GetTree(string systemtypeId, IsVisable isVisable = IsVisable.是, IsDelete isDelete = IsDelete.否)
{
string condition = !string.IsNullOrEmpty(systemtypeId) ? string.Format("AND SystemtypeId='{0}'", systemtypeId) : "";
List <MenuNodeInfo> arrReturn = new List <MenuNodeInfo>();
string sql = string.Format("Select * From {0} Where (IsVisable = {2} or 0 = {2}) {1} Order By Pgid, Seq ", tableName, condition, (short)isVisable);
DataTable dt = base.SqlTable(sql);
string sort = string.Format("{0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
DataRow[] dataRows = dt.Select(string.Format(" Pgid = '{0}' ", -1), sort);
for (int i = 0; i < dataRows.Length; i++)
{
string id = dataRows[i]["Gid"].ToString();
MenuNodeInfo menuNodeInfo = GetNode(id, dt);
arrReturn.Add(menuNodeInfo);
}
return(arrReturn);
}
/// <summary>
/// 获取所有的菜单列表
/// </summary>
public List <MenuInfo> GetAllMenu(string systemtypeId, IsVisable isVisable = IsVisable.是, IsDelete isDelete = IsDelete.否)
{
string condition = !string.IsNullOrEmpty(systemtypeId) ? string.Format("Where SystemtypeId='{0}' and (IsVisable = {1} or 0 = {1}) and (IsDelete = {2} or 0 = {2})", systemtypeId, (short)isVisable, (short)isDelete) : string.Format("Where (IsVisable = {0} or 0 = {0}) and (IsDelete = {1} or 0 = {1})", (short)isVisable, (short)isDelete);
string sql = string.Format("Select * From {0} {1} Order By Pgid, Seq ", tableName, condition);
return(GetList(sql, null));
}
/// <summary>
/// 设置删除标志
/// </summary>
/// <param name="id">联系人ID</param>
/// <param name="deleted">是否删除</param>
/// <param name="trans">事务对象</param>
/// <returns></returns>
public bool SetDeletedFlag(Int32 id, IsDelete isDelete = IsDelete.是, DbTransaction trans = null)
{
IContact dal = baseDal as IContact;
return(dal.SetDeletedFlag(id, isDelete, trans));
}
/// <summary>
/// 获取第一级的菜单列表
/// </summary>
public List <MenuInfo> GetTopMenu(string systemtypeId, IsVisable isVisable = IsVisable.是, IsDelete isDelete = IsDelete.否)
{
string condition = !string.IsNullOrEmpty(systemtypeId) ? string.Format("AND SystemtypeId='{0}'", systemtypeId) : "";
string sql = string.Format("Select * From {0} Where (IsVisable = {2} or 0 = {2}) and (IsDelete = {3} or 0 = {3}) and Pgid='-1' {1} Order By Seq ", tableName, condition, (short)isVisable, (short)isDelete);
return(GetList(sql, null));
}
private MenuNodeInfo GetNode(string gid, DataTable dt, IsVisable isVisable = IsVisable.是, IsDelete isDelete = IsDelete.否)
{
MenuInfo menuInfo = this.FindById(gid);
MenuNodeInfo menuNodeInfo = new MenuNodeInfo(menuInfo);
string sort = string.Format("{0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
DataRow[] dChildRows = dt.Select(string.Format(" Pgid='{0}' and (IsVisable = {1} or 0 = {1}) and (IsDelete = {2} or 0 = {2})", gid, (short)isVisable, (short)isDelete), sort);
for (int i = 0; i < dChildRows.Length; i++)
{
string childId = dChildRows[i]["Gid"].ToString();
MenuNodeInfo childNodeInfo = GetNode(childId, dt, isVisable);
menuNodeInfo.Children.Add(childNodeInfo);
}
return(menuNodeInfo);
}
/// <summary>
/// 获取指定菜单下面的树形列表
/// </summary>
/// <param name="mainMenuID">指定菜单ID</param>
public List <MenuNodeInfo> GetTreeById(string mainMenuId, IsVisable isVisable = IsVisable.是, IsDelete isDelete = IsDelete.否)
{
List <MenuNodeInfo> arrReturn = new List <MenuNodeInfo>();
string sql = string.Format("Select * From {0} Where (IsVisable = {1} or 0 = {1}) Order By Pgid, Seq ", tableName, (short)isVisable);
DataTable dt = SqlTable(sql);
string sort = string.Format("{0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
DataRow[] dataRows = dt.Select(string.Format(" Pgid = '{0}' and (IsVisable = {1} or 0 = {1}) and (IsDelete = {2} or 0 = {2})", mainMenuId, (short)isVisable, (short)isDelete), sort);
for (int i = 0; i < dataRows.Length; i++)
{
string id = dataRows[i]["Gid"].ToString();
MenuNodeInfo menuNodeInfo = GetNode(id, dt);
arrReturn.Add(menuNodeInfo);
}
return(arrReturn);
}
/// <summary>
/// 获取指定用户Id字符串的简单用户对象列表
/// </summary>
/// <param name="userIDs"></param>
/// <returns></returns>
public List <SimpleUserInfo> GetSimpleUsers(string userIds, IsDelete isDelete = IsDelete.否)
{
string condition = string.Format(" Id In ({0})", userIds);
return(this.FindSimpleUsers(condition, isDelete));
}
/// <summary>
/// 获取全部的简单用户对象列表
/// </summary>
/// <returns></returns>
public List <SimpleUserInfo> GetSimpleUsers(IsDelete isDelete = IsDelete.否)
{
return(this.FindSimpleUsers(null, isDelete));
}
/// <summary>
/// 设置删除标志
/// </summary>
/// <param name="id">记录ID</param>
/// <param name="deleted">是否删除</param>
/// <param name="trans">事务对象</param>
/// <returns></returns>
public bool SetDeletedFlag(Int32 id, IsDelete isDelete = IsDelete.是, DbTransaction trans = null)
{
return(dal.SetDeletedFlag(id, isDelete, trans));
}
/// <summary>
/// 设置删除标志
/// </summary>
/// <param name="id">客户ID</param>
/// <param name="deleted">是否删除</param>
/// <param name="trans">事务对象</param>
/// <returns></returns>
public bool SetDeletedFlag(Int32 id, IsDelete isDelete = IsDelete.是, DbTransaction trans = null)
{
string sql = string.Format("Update {0} Set IsDelete={1} Where Id = '{2}' ", tableName, (short)isDelete, id);
return(SqlExecute(sql, trans) > 0);
}
