/// <summary>
/// 分页查询
/// </summary>
/// <param name="paging">分页参数</param>
/// <returns></returns>
public async Task <PagedResults <SystemUserOutput> > PagingUserQuery(SystemUserPagingInput paging)
{
if (paging.DataSql.IsNullOrEmpty())
{
return(new PagedResults <SystemUserOutput>());
}
var data = await _userInfoRepository.PagingUserQuery(paging);
var allOrgs = (await _organizationLogic.GetAllEnumerableAsync()).ToList();
foreach (var user in data.Data)
{
var organization = allOrgs.FirstOrDefault(w => w.OrganizationId == user.OrganizationId);
if (organization != null && !organization.ParentIds.IsNullOrEmpty())
{
foreach (var parent in organization.ParentIds.Split(','))
{
//查找上级
var dicinfo = allOrgs.FirstOrDefault(w => w.OrganizationId.ToString() == parent);
if (dicinfo != null)
{
user.OrganizationNames += dicinfo.Name + ">";
}
}
if (!user.OrganizationNames.IsNullOrEmpty())
{
user.OrganizationNames = user.OrganizationNames.TrimEnd('>');
}
}
}
return(data);
}
public FileResult ExportExcel(SystemUserPagingInput paging)
{
ExcelReportDto excelReportDto = new ExcelReportDto()
{
TemplatePath = Server.MapPath("/") + "Templates/System/User/用户导出模版.xlsx",
DownTemplatePath = "用户信息" + string.Format("{0:yyyyMMddHHmmssffff}", DateTime.Now) + ".xlsx",
Title = "用户信息.xlsx"
};
_userInfoLogic.ReportExcelUserQuery(paging, excelReportDto);
return(File(new FileStream(excelReportDto.DownPath, FileMode.Open), "application/octet-stream", Server.UrlEncode(excelReportDto.Title)));
}
/// <summary>
/// 复杂查询分页方式
/// </summary>
/// <param name="paging">查询参数</param>
/// <returns>分页</returns>
public Task <PagedResults <SystemUserOutput> > PagingUserQuery(SystemUserPagingInput paging)
{
StringBuilder sql = new StringBuilder(2000);
if (paging.PrivilegeMaster == EnumPrivilegeMaster.组织机构)
{
sql.Append(
string.Format(@"SELECT {1}, @rowNumber, @recordCount
FROM [System_UserInfo] u
LEFT JOIN System_PermissionUser pu ON pu.PrivilegeMasterUserId=u.UserId
@where AND pu.PrivilegeMaster='{0}' AND u.IsAdmin='false' ", (int)paging.PrivilegeMaster, paging.FiledSql));
}
else
{
sql.Append(
string.Format(@"SELECT u.UserId,
u.Code,
u.Name,
u.Mobile,
u.FirstVisitTime,
u.LastVisitTime,
u.IsFreeze,
u.Remark,
u.State,
u.CreateTime,
u.CreateUserName,
u.UpdateTime,
u.UpdateUserName,
--组织机构名称
(SELECT Name FROM dbo.System_Organization org WHERE
org.OrganizationId=(SELECT PrivilegeMasterValue FROM dbo.System_PermissionUser
WHERE PrivilegeMaster=1 AND PrivilegeMasterUserId=u.UserId))
OrganizationName,
--组织机构Id
(SELECT OrganizationId FROM dbo.System_Organization org WHERE
org.OrganizationId=(SELECT PrivilegeMasterValue FROM dbo.System_PermissionUser
WHERE PrivilegeMaster=1 AND PrivilegeMasterUserId=u.UserId)) OrganizationId,
@rowNumber, @recordCount
FROM [System_UserInfo] u
LEFT JOIN System_PermissionUser pu ON pu.PrivilegeMasterUserId=u.UserId
@where AND pu.PrivilegeMaster='{0}' AND u.IsAdmin='false' AND pu.PrivilegeMasterValue='{1}' ", (int)paging.PrivilegeMaster,
paging.PrivilegeMasterValue));
}
if (!paging.DataSql.IsNullOrEmpty())
{
sql.Append(string.Format(" AND {0}", paging.DataSql));
}
//获取对应字段信息并进行对应替换
return(PagingQueryAsync <SystemUserOutput>(sql.ToString(), paging));
}
/// <summary>
/// Excel导出方式
/// </summary>
/// <param name="paging">查询参数</param>
/// <param name="excelReportDto"></param>
/// <returns></returns>
public async Task <OperateStatus> ReportExcelUserQuery(SystemUserPagingInput paging,
ExcelReportDto excelReportDto)
{
var operateStatus = new OperateStatus();
try
{
//组装数据
IList <SystemUserOutput> dtos = (await _userInfoRepository.PagingUserQuery(paging)).Data.ToList();
var tables = new Dictionary <string, DataTable>(StringComparer.OrdinalIgnoreCase);
//组装需要导出数据
var dt = new DataTable("User");
dt.Columns.Add("Num");
dt.Columns.Add("Code");
dt.Columns.Add("Name");
dt.Columns.Add("OrganizationName");
dt.Columns.Add("Mobile");
dt.Columns.Add("IsFreeze");
dt.Columns.Add("CreatTime");
dt.Columns.Add("FirstVisitTime");
dt.Columns.Add("LastVisitTime");
dt.Columns.Add("Remark");
var num = 1;
if (dtos.Any())
{
foreach (var dto in dtos)
{
var row = dt.NewRow();
dt.Rows.Add(row);
row[0] = num;
row[1] = dto.Code;
row[2] = dto.Name;
row[3] = dto.OrganizationName;
row[4] = dto.Mobile;
row[5] = dto.IsFreeze ? "是" : "否";
row[7] = dto.FirstVisitTime;
row[8] = dto.LastVisitTime;
row[9] = dto.Remark;
num++;
}
}
tables.Add(dt.TableName, dt);
OpenXmlExcel.ExportExcel(excelReportDto.TemplatePath, excelReportDto.DownPath, tables);
operateStatus.ResultSign = ResultSign.Successful;
}
catch (Exception)
{
operateStatus.ResultSign = ResultSign.Error;
}
return(operateStatus);
}
public async Task <JsonResult> GetPagingUser(SystemUserPagingInput paging)
{
#region 获取权限控制器信息
SystemPermissionSqlInput input = new SystemPermissionSqlInput
{
PrincipalUser = _currentUser,
EnumPermissionRoteConvert = EnumPermissionRoteConvert.人员数据权限
};
paging.DataSql = await _permissionLogic.GetDataPermissionSql(input);
#endregion
var users = await _userInfoLogic.PagingUserQuery(paging);
return(JsonForGridPaging(users));
}
public async Task <JsonResult> GetPagingUser(SystemUserPagingInput paging)
{
#region 获取权限控制器信息
SystemPermissionSqlInput input = new SystemPermissionSqlInput()
{
PrincipalUser = CurrentUser,
EnumPermissionRoteConvert = EnumPermissionRoteConvert.用户字段数据权限
};
paging.FiledSql = await _permissionLogic.GetFieldPermissionSql(input);
paging.DataSql = await _permissionLogic.GetDataPermissionSql(input);
#endregion
paging.PrivilegeMaster = EnumPrivilegeMaster.组织机构;
var users = await _userInfoLogic.PagingUserQuery(paging);
return(JsonForGridPaging(users));
}
/// <summary>
/// Excel导出方式
/// </summary>
/// <param name="paging">查询参数</param>
/// <param name="excelReportDto"></param>
/// <returns></returns>
public async Task <OperateStatus> ReportExcelUserQuery(SystemUserPagingInput paging,
ExcelReportDto excelReportDto)
{
var operateStatus = new OperateStatus();
try
{
//组装数据
IList <SystemUserOutput> dtos = (await _userInfoRepository.PagingUserQuery(paging)).Data.ToList();
var tables = new Dictionary <string, DataTable>(StringComparer.OrdinalIgnoreCase);
//组装需要导出数据
operateStatus.ResultSign = ResultSign.Successful;
}
catch (Exception)
{
operateStatus.ResultSign = ResultSign.Error;
}
return(operateStatus);
}
/// <summary>
/// 复杂查询分页方式
/// </summary>
/// <param name="paging">查询参数</param>
/// <returns>分页</returns>
public Task <PagedResults <SystemUserOutput> > PagingUserQuery(SystemUserPagingInput paging)
{
StringBuilder sql = new StringBuilder(2000);
sql.Append(
string.Format(@"SELECT u.UserId,u.OrganizationId,u.Code,u.Name,u.Mobile,u.Email,u.LastVisitTime,u.IsFreeze,u.Remark,u.Status,u.CreateTime,u.CreateUserName,u.UpdateTime,u.UpdateUserName,org.Name OrganizationName,
@rowNumber, @recordCount
FROM [System_UserInfo] u
LEFT JOIN System_PermissionUser pu ON pu.PrivilegeMasterUserId=u.UserId
LEFT JOIN System_Organization org on org.OrganizationId=u.OrganizationId
@where AND pu.PrivilegeMaster='{0}' AND u.IsAdmin='false' ", paging.PrivilegeMaster.GetHashCode()));
if (paging.PrivilegeMasterValue != null)
{
switch (paging.PrivilegeMaster)
{
case EnumPrivilegeMaster.组织机构:
sql.Append(@" AND " + paging.DataSql + "");
break;
}
}
return(PagingQueryAsync <SystemUserOutput>(sql.ToString(), paging));
}
public async Task <JsonResult> GetPagingUser(SystemUserPagingInput paging)
{
paging.PrivilegeMaster = EnumPrivilegeMaster.岗位;
return(JsonForGridPaging(await _userInfoLogic.PagingUserQuery(paging)));
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="paging">分页参数</param>
/// <returns></returns>
public async Task <PagedResults <SystemUserOutput> > PagingUserQuery(SystemUserPagingInput paging)
{
return(await _userInfoRepository.PagingUserQuery(paging));
}
