/// <summary>
/// 根据工号获取组织人员信息
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <DataResult <List <IHealthStaff> > > GetHealthStaffInfo(QueryData <GetHealthStaffInfoQuery> query)
{
var lr = new DataResult <List <IHealthStaff> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.StaffNo) ? string.Empty : string.Format(" and StaffNo = '{0}' ", query.Criteria.StaffNo);
condition += string.IsNullOrEmpty(query.Criteria.StaffName) ? string.Empty : string.Format(" and StaffName = '{0}' ", query.Criteria.StaffName);
string sql = string.Format(@"SELECT [Id],[StaffNo],[StaffName],[GroupType],[GroupLeader],[GroupLeaderNo],[AggLeader],[AggLeaderNo],[CommandLeader],[CommondLeaderNo],[HrLeader],[HrLeaderNo] FROM health_staff " + condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryListAsync <HealthStaff>(dbConn, sql, "StaffNo asc");
lr.Data = modelList.ToList <IHealthStaff>();
}
catch (Exception ex)
{
lr.SetErr(ex, -101);
lr.Data = null;
}
}
return(lr);
}
public async Task <DataResult <List <IHealthContentStaff> > > GetHealthContentStaffAllAsync(QueryData <HealthContentQuery> query)
{
var result = new DataResult <List <IHealthContentStaff> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.Answer) ? string.Empty : string.Format(" and Content like '%{0}%' ", query.Criteria.Answer);
condition += string.IsNullOrEmpty(query.Criteria.Creator) ? string.Empty : string.Format(" and Creator like '%{0}%' ", query.Criteria.Creator);
condition += string.IsNullOrEmpty(query.Criteria.CommondLeaderNo) ? string.Empty : string.Format(" and CommondLeaderNo = '{0}' ", query.Criteria.CommondLeaderNo);
condition += query.Criteria.StarTime == null ? string.Empty : string.Format(" and CreateTime >= '{0}' ", query.Criteria.StarTime);
condition += query.Criteria.EndTime == null ? string.Empty : string.Format(" and CreateTime <= '{0}' ", query.Criteria.EndTime);
string sql = string.Format(@"SELECT
a.[Id],[ContentId],[TitleId],[TitleType],[Answer],[Creator],[CreateTime],[CreateName]
,[StaffNo],[StaffName],[GroupType],[GroupLeader],[GroupLeaderNo],[AggLeader],[AggLeaderNo],[CommandLeader],[CommondLeaderNo],[HrLeader],[HrLeaderNo]
FROM [dbo].[health_content] a
LEFT JOIN [dbo].[health_staff] b
ON a.Creator=b.StaffNo {0} order by Id desc", condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryListAsync <HealthContentStaff>(dbConn, sql);
result.Data = modelList.ToList <IHealthContentStaff>();
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = null;
}
}
return(result);
}
public async Task <DataResult <List <IHealthStaff> > > GetHealthStaffHrAllAsync(QueryData <HealthStaffQuery> query)
{
var result = new DataResult <List <IHealthStaff> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.HrLeader) ? string.Empty : string.Format(" and HrLeader like '%{0}%' ", query.Criteria.HrLeader);
condition += string.IsNullOrEmpty(query.Criteria.HrLeaderNo) ? string.Empty : string.Format(" and HrLeaderNo = '{0}' ", query.Criteria.HrLeaderNo);
string sql = string.Format(@"select distinct(HrLeaderNo),[HrLeader] from [dbo].[health_staff] {0}", condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryListAsync <HealthStaff>(dbConn, sql);
result.Data = modelList.ToList <IHealthStaff>();
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = null;
}
}
return(result);
}
/// <summary>
/// 根据工号获取承诺书信息
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <DataResult <List <IHealth_pledge_Model> > > GetHealthPledgeInfo(QueryData <GetHealthPledgeInfoQuery> query)
{
var lr = new DataResult <List <IHealth_pledge_Model> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.StaffNo) ? string.Empty : string.Format(" and StaffNo = '{0}' ", query.Criteria.StaffNo);
string sql = "SELECT [Id],[StaffNo],[StaffName],[IsSign],[SignTime],[PledgeType] FROM health_pledge " + condition;
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryListAsync <Health_pledge_Model>(dbConn, sql, "Id asc");
lr.Data = modelList.ToList <IHealth_pledge_Model>();
}
catch (Exception ex)
{
lr.SetErr(ex, -101);
lr.Data = null;
}
}
return(lr);
}
/// <summary>
/// 获取体检内容表头子选项
///
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <DataResult <List <IHealth_title_Model> > > GetSubHealthTitle(QueryData <GetSubHealthTitleQuery> query)
{
var lr = new DataResult <List <IHealth_title_Model> >();
string condition = @" where 1=1 and TitleId != ParentId";
condition += query.Criteria.IsShow == null ? string.Empty : string.Format(" and IsShow = '{0}' ", query.Criteria.IsShow);
condition += string.IsNullOrEmpty(query.Criteria.ParentId) ? string.Empty : string.Format(" and ParentId ='{0}' ", query.Criteria.ParentId);
string sql = "SELECT [Id],[TitleId],[Content],[Type],[IsMustFill],[ParentId],[Creator] ,[CreateTime],[Updator],[UpdateTime],[Sort],[IsShow] " +
"from health_title"
+ condition;
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryListAsync <Health_title_Model>(dbConn, sql, "Sort asc");
lr.Data = modelList.ToList <IHealth_title_Model>();
}
catch (Exception ex)
{
lr.SetErr(ex, -101);
lr.Data = null;
}
}
return(lr);
}
/// <summary>
/// 获取所有组员填写
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <DataResult <List <IHealth_staff_Model> > > GetHealthStaffCount_All(QueryData <GetHealthStaffCountQuery> query)
{
var lr = new DataResult <List <IHealth_staff_Model> >();
string sql = string.Format(@"select distinct staff.StaffNo,staff.STAFFName,
case when content.Contentid is not null then 1 when content.Contentid is null then 0 else 0 end iswrite from health_staff staff
left join (select * from health_content where (CONVERT(varchar(100), CreateTime, 23) ='{0}'or CreateTime is null) ) as content on content.Creator=staff.StaffNo
where
(staff.GroupLeaderNo='{1}' or AggLeaderNo='{1}' or CommondLeaderNo='{1}' or HrLeaderNo='{1}') ", query.Criteria.date, query.Criteria.userNo);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryListAsync <Health_staff_Model>(dbConn, sql, "iswrite asc");
lr.Data = modelList.ToList <IHealth_staff_Model>();
lr.PageInfo = query.PageModel;
}
catch (Exception ex)
{
lr.SetErr(ex, -101);
lr.Data = null;
}
}
return(lr);
}
/// <summary>
/// 根据工号和日期获取体检填写内容
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <DataResult <List <IHealth_content_Model> > > GetHealthContentDetailsInfoByNoAndDate(QueryData <GetHealthStaffCountQuery> query)
{
var lr = new DataResult <List <IHealth_content_Model> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.date) ? string.Empty : string.Format(" and CONVERT(varchar(10), CreateTime, 120) = '{0}' ", query.Criteria.date);
condition += string.IsNullOrEmpty(query.Criteria.userNo) ? string.Empty : string.Format(" and Creator = '{0}' ", query.Criteria.userNo);
string sql = string.Format(@"SELECT Top 1 [Id],[ContentId],[titleId],[TitleType],[Answer],[Creator],[CreateTime],[CreateName],[IsPass],[NotPassReson] FROM [dbo].[health_content] " + condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryListAsync <Health_content_Model>(dbConn, sql, "CreateTime desc");
lr.Data = modelList.ToList <IHealth_content_Model>();
lr.PageInfo = query.PageModel;
}
catch (Exception ex)
{
lr.SetErr(ex, -500);
lr.Data = null;
}
}
return(lr);
}
/// <summary>
/// 组长查询（根据权限查看,获取所有）
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <DataResult <List <IHealth_staff_Model> > > TeamLeaderQuery_All(QueryData <GetTeamLeaderQueryModel> query)
{
var lr = new DataResult <List <IHealth_staff_Model> >();
string strWhere = " ";
strWhere += string.IsNullOrEmpty(query.Criteria.teamNO) ? string.Empty : string.Format(" and staff.StaffNo='{0}' ", query.Criteria.teamNO);
strWhere += string.IsNullOrEmpty(query.Criteria.teamName) ? string.Empty : string.Format(" and staff.StaffName='{0}' ", query.Criteria.teamName);
string sql = string.Format(@"select distinct staff.StaffNo,staff.StaffName, case when content.Contentid is not null then 1 when content.Contentid is null then 0 else 0 end iswrite
from health_user_staff uf left join health_staff staff on uf.StaffNo=staff.StaffNo
left join (select * from health_content where (CONVERT(varchar(100), CreateTime, 23) ='{0}' or CreateTime is null) ) as content
on content.Creator=staff.StaffNo where uf.UserNo='{1}' and staff.StaffNo is not null" + strWhere, query.Criteria.date, query.Criteria.userNo);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryListAsync <Health_staff_Model>(dbConn, sql, "iswrite asc");
lr.Data = modelList.ToList <IHealth_staff_Model>();
lr.PageInfo = query.PageModel;
}
catch (Exception ex)
{
lr.SetErr(ex, -101);
lr.Data = null;
}
}
return(lr);
}
public async Task <DataResult <List <IHealthTitle> > > GetHealthTitleAllAsync(QueryData <HealthTitleQuery> query)
{
var result = new DataResult <List <IHealthTitle> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.Content) ? string.Empty : string.Format(" and Content like '%{0}%' ", query.Criteria.Content);
condition += string.IsNullOrEmpty(query.Criteria.Creator) ? string.Empty : string.Format(" and Creator like '%{0}%' ", query.Criteria.Creator);
condition += query.Criteria.IsShow == null ? string.Empty : string.Format(" and IsShow = '{0}' ", query.Criteria.IsShow);
if (query.Criteria.IsParentQuery)
{
condition += string.IsNullOrEmpty(query.Criteria.ParentId) ? string.Format(" and (ParentId = '' or ParentId is null) ", query.Criteria.ParentId)
: string.Format(" and ParentId = '{0}' ", query.Criteria.ParentId);
}
string sql = string.Format(@"SELECT [Id]
,[TitleId]
,[Content]
,[Type]
,[IsMustFill]
,[ParentId]
,[Creator]
,[CreateTime]
,[Updator]
,[UpdateTime]
,[Sort]
,[IsShow]
FROM [dbo].[health_title] {0} order by Sort asc", condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryListAsync <HealthTitle>(dbConn, sql);
result.Data = modelList.ToList <IHealthTitle>();
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = null;
}
}
return(result);
}
public async Task <DataResult <List <ISysModuleModel> > > GetModulesAllAsync(QueryData <SysModuleQuery> query)
{
var lr = new DataResult <List <ISysModuleModel> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.ModuleNo) ? string.Empty : string.Format(" and ModuleNo = '{0}' ", query.Criteria.ModuleNo);
condition += string.IsNullOrEmpty(query.Criteria.ModuleName) ? string.Empty : string.Format(" and ModuleName = '{0}' ", query.Criteria.ModuleName);
condition += query.Criteria.IsDelete == null ? string.Empty : string.Format(" and IsDelete = '{0}' ", query.Criteria.IsDelete);
string sql = "select Id,ModuleNo,ModuleName,ParentNo,Icon,Url,Category,Target,IsResource,App,Creator,CreateName,CreateTime,Updator,UpdateName,UpdateTime,IsDelete,Sort " +
"from sys_module"
+ condition;
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryListAsync <SysModuleModel>(dbConn, sql, "Sort asc");
lr.Data = modelList.ToList <ISysModuleModel>();
}
catch (Exception ex)
{
lr.SetErr(ex, -101);
lr.Data = null;
}
}
//using (IDbConnection dbConn = MysqlHelper.OpenMysqlConnection(MysqlHelper.MysqlConn))
//{
// try
// {
// var modelList = await MysqlHelper.QueryListAsync<SysModuleModel>(dbConn, sql, "Sort asc");
// lr.Data = modelList.ToList<ISysModuleModel>();
// }
// catch (Exception ex)
// {
// lr.SetErr(ex, -101);
// lr.Data = null;
// }
//}
return(lr);
}
public async Task <DataResult <List <IHealthContentStaff> > > GetHealthContentUserStaffAllAsync(QueryData <HealthContentQuery> query)
{
var result = new DataResult <List <IHealthContentStaff> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.Answer) ? string.Empty : string.Format(" and Content like '%{0}%' ", query.Criteria.Answer);
condition += string.IsNullOrEmpty(query.Criteria.Creator) ? string.Empty : string.Format(" and Creator = '{0}' ", query.Criteria.Creator);
condition += string.IsNullOrEmpty(query.Criteria.CreateName) ? string.Empty : string.Format(" and CreateName like '%{0}%' ", query.Criteria.CreateName);
condition += query.Criteria.StarTime == null ? string.Empty : string.Format(" and CreateTime >= '{0}' ", query.Criteria.StarTime);
condition += query.Criteria.EndTime == null ? string.Empty : string.Format(" and CreateTime <= '{0}' ", query.Criteria.EndTime);
condition += string.IsNullOrEmpty(query.Criteria.HrLeaderNo) ? string.Empty : string.Format(" and HrLeaderNo = '{0}' ", query.Criteria.HrLeaderNo);
condition += string.IsNullOrEmpty(query.Criteria.UpStaffNo) ? string.Empty : string.Format(" and Creator in (select StaffNo from dbo.health_user_staff where UserNo='{0}') ", query.Criteria.UpStaffNo);
string sql = string.Format(@"SELECT * FROM (
SELECT ROW_NUMBER() OVER ( PARTITION BY [Creator], CONVERT(varchar(100), CreateTime, 23) ORDER BY [CreateTime] DESC ) AS num,
a.[Id],[ContentId],[TitleId],[TitleType],[Answer],[Creator],[CreateTime],[CreateName],[IsPass],[NotPassReson]
,[StaffNo],[StaffName],[GroupType],[GroupLeader],[GroupLeaderNo],[AggLeader],[AggLeaderNo],[CommandLeader],[CommondLeaderNo],[HrLeader],[HrLeaderNo]
FROM [dbo].[health_content] a
LEFT JOIN [dbo].[health_staff] b
ON a.Creator=b.StaffNo {0}) as T where num=1 ", condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryListAsync <HealthContentStaff>(dbConn, sql);
result.Data = modelList.ToList <IHealthContentStaff>();
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = null;
}
}
return(result);
}