public async Task <DataResult <List <IHealthUser> > > GetHealthUserPageAsync(QueryData <HealthUserQuery> query)
{
var result = new DataResult <List <IHealthUser> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.AdAccount) ? string.Empty : string.Format(" and AdAccount = '{0}' ", query.Criteria.AdAccount);
condition += string.IsNullOrEmpty(query.Criteria.UserNo) ? string.Empty : string.Format(" and UserNo like '%{0}%' ", query.Criteria.UserNo);
condition += string.IsNullOrEmpty(query.Criteria.UserName) ? string.Empty : string.Format(" and UserName like '%{0}%' ", query.Criteria.UserName);
string sql = string.Format(@"SELECT [Id]
,[UserNo]
,[UserName]
,[AdAccount]
,[Creator]
,[CreateTime]
,[IsAdmin]
FROM [dbo].[health_user] {0}", condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryPageAsync <HealthUser>(dbConn, "Id asc", sql, query.PageModel);
result.Data = modelList.ToList <IHealthUser>();
result.PageInfo = query.PageModel;
}
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_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);
}
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 <IHealthPowerStaff> > > GetHealthPowerStaffPageAsync(QueryData <HealthPowerStaffQuery> query)
{
var result = new DataResult <List <IHealthPowerStaff> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.UserNo) ? string.Empty : string.Format(" and UserNo = '{0}' ", query.Criteria.UserNo);
condition += string.IsNullOrEmpty(query.Criteria.StaffName) ? string.Empty : string.Format(" and StaffName like '%{0}%' ", query.Criteria.StaffName);
condition += string.IsNullOrEmpty(query.Criteria.Creator) ? string.Empty : string.Format(" and Creator = '{0}' ", query.Criteria.Creator);
condition += query.Extend.IsAdmin ? string.Empty : string.Format(" or UserNo = '{0}' ", query.Criteria.Creator);
string sql = string.Format(@"select distinct(UserNo),StaffName
from [dbo].[health_user_staff] a
left join dbo.health_staff b
on a.UserNo=b.StaffNo {0}", condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryPageAsync <HealthPowerStaff>(dbConn, "UserNo desc", sql, query.PageModel);
result.Data = modelList.ToList <IHealthPowerStaff>();
result.PageInfo = query.PageModel;
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = null;
}
}
return(result);
}
public async Task <DataResult <List <IHealthUserStaffInfo> > > GetHealthUserStaffInfoPageAsync(QueryData <HealthUserStaffInfoQuery> query)
{
var result = new DataResult <List <IHealthUserStaffInfo> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.UserNo) ? string.Empty : string.Format(" and UserNo = '{0}' ", query.Criteria.UserNo);
condition += string.IsNullOrEmpty(query.Criteria.StaffNo) ? string.Empty : string.Format(" and a.[StaffNo] = '{0}' ", query.Criteria.StaffNo);
condition += string.IsNullOrEmpty(query.Criteria.StaffName) ? string.Empty : string.Format(" and StaffName like '%{0}%' ", query.Criteria.StaffName);
string sql = string.Format(@"select a.[Id],[UserNo],a.[StaffNo],[Creator],[CreateName],[CreateTime],
[StaffName],[GroupType],[GroupLeader],[GroupLeaderNo],[AggLeader],[AggLeaderNo],[CommandLeader],[CommondLeaderNo],[HrLeader],[HrLeaderNo]
from dbo.health_user_staff a left join dbo.health_staff b on a.StaffNo=b.StaffNo {0}", condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryPageAsync <HealthUserStaffInfo>(dbConn, "Id desc", sql, query.PageModel);
result.Data = modelList.ToList <IHealthUserStaffInfo>();
result.PageInfo = query.PageModel;
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = null;
}
}
return(result);
}
public async Task <DataResult <int> > HealthTitleSaveAsync(QueryData <HealthTitleSaveQuery> query)
{
var result = new DataResult <int>();
string sql = @"insert into dbo.health_title([TitleId],[Content],[Type],[IsMustFill],[ParentId],[Creator],[CreateTime],[Sort],[IsShow])
values(@TitleId,@Content,@Type,@IsMustFill,@ParentId,@Creator,getdate(),@Sort,@IsShow)";
string sqlc = @"select * from dbo.health_title where Content=@Content and ParentId=''";
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
result.Data = await MssqlHelper.QueryCountAsync(dbConn, sqlc, new { Content = query.Criteria.Content });
if (result.Data > 0)
{
result.SetErr("标题已存在！", -101);
return(result);
}
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sql, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("数据保存失败！", -101);
}
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = -1;
}
}
return(result);
}
public async Task <DataResult <int> > HealthStaffSaveOrUpdateAsync(QueryData <HealthStaffQuery> query)
{
var result = new DataResult <int>();
string sqli = @"insert into [dbo].[health_staff]([StaffNo],[StaffName],[GroupType],[GroupLeader],[GroupLeaderNo],[AggLeader],[AggLeaderNo],[CommandLeader],[CommondLeaderNo],[HrLeader],[HrLeaderNo])
values(@StaffNo,@StaffName,@GroupType,@GroupLeader,@GroupLeaderNo,@AggLeader,@AggLeaderNo,@CommandLeader,@CommondLeaderNo,@HrLeader,@HrLeaderNo)";
string sqlu = @"update [dbo].[health_staff] set [StaffNo]=@StaffNo,[StaffName]=@StaffName,[GroupType]=@GroupType,[GroupLeader]=@GroupLeader,[GroupLeaderNo]=@GroupLeaderNo,
[AggLeader]=@AggLeader,[AggLeaderNo]=@AggLeaderNo,[CommandLeader]=@CommandLeader,[CommondLeaderNo]=@CommondLeaderNo,[HrLeader]=@HrLeader,[HrLeaderNo]=@HrLeaderNo
where Id=@Id";
string sqlis = @"select * from [dbo].[health_staff] where [StaffNo]=@StaffNo";
string sqlus = @"select * from [dbo].[health_staff] where [StaffNo]=@StaffNo and Id!=@Id";
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
if (query.Criteria.Id <= 0)//新增
{
result.Data = await MssqlHelper.QueryCountAsync(dbConn, sqlis, new { StaffNo = query.Criteria.StaffNo });
if (result.Data > 0)
{
result.SetErr("该工号已存在，请重试！", -101);
return(result);
}
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sqli, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("添加失败，请重试！", -101);
return(result);
}
}
else//修改
{
result.Data = await MssqlHelper.QueryCountAsync(dbConn, sqlus, new { Id = query.Criteria.Id, StaffNo = query.Criteria.StaffNo });
if (result.Data > 0)
{
result.SetErr("该工号已存在，请重试！", -101);
return(result);
}
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sqlu, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("修改失败，请重试！", -101);
return(result);
}
}
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = -1;
}
}
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 <int> > InsertHealthPledgeInfo(QueryData <InsertHealthPledgeInfoQuery> query)
{
var lr = new DataResult <int>();
string condition = string.Format(@"insert into health_pledge(StaffNo,StaffName,IsSign,SignTime,PledgeType) values(@StaffNo,@StaffName,@IsSign,@SignTime,@PledgeType)");
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
lr.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, condition, query.Criteria);
if (lr.Data < 0)
{
lr.SetErr("保存确认承诺书异常", lr.Data);
}
if (lr.Data == 0)
{
lr.SetErr("保存确认承诺书失败", -102);
}
}
catch (Exception ex)
{
lr.SetErr(ex, -500);
}
}
return(lr);
}
public async Task <DataResult <List <IHealthContent> > > GetHealthContentPageAsync(QueryData <HealthContentQuery> query)
{
var result = new DataResult <List <IHealthContent> >();
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);
string sql = string.Format(@"SELECT [Id]
,[ContentId]
,[TitleId]
,[TitleType]
,[Answer]
,[Creator]
,[CreateTime]
,[CreateName]
FROM [dbo].[health_content] {0}", condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryPageAsync <HealthContent>(dbConn, "Id asc", sql, query.PageModel);
result.Data = modelList.ToList <IHealthContent>();
result.PageInfo = query.PageModel;
}
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 <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 <int> > HealthUserStaffDeleteAsync(QueryData <HealthUserStaffDeleteQuery> query)
{
var result = new DataResult <int>();
string sql = @"delete from dbo.health_user_staff where UserNo=@UserNo and StaffNo=@StaffNo";
string sqlc = @"select * from dbo.health_user_staff where UserNo=@UserNo and StaffNo=@StaffNo";
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
result.Data = await MssqlHelper.QueryCountAsync(dbConn, sqlc, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("数据不存在！", -101);
return(result);
}
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sql, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("数据删除失败！", -101);
}
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = -1;
}
}
return(result);
}
public async Task <DataResult <int> > HealthTitleUpdateAsync(QueryData <HealthTitleUpdateQuery> query)
{
var result = new DataResult <int>();
string sql = @"update dbo.health_title set [Content]=@Content,[Type]=@Type,[IsMustFill]=@IsMustFill,[Updator]=@Updator,[UpdateTime]=getdate(),[Sort]=@Sort,[IsShow]=@IsShow
where [TitleId]=@TitleId";
string sqlc = @"select * from dbo.health_title where Content=@Content and TitleId!=@TitleId";
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
result.Data = await MssqlHelper.QueryCountAsync(dbConn, sqlc, new { Content = query.Criteria.Content, TitleId = query.Criteria.TitleId });
if (result.Data > 0)
{
result.SetErr("标题已存在！", -101);
return(result);
}
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sql, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("数据更新失败！", -101);
}
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = -1;
}
}
return(result);
}
/// <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<IHealth_staff_Model>>> GetHealthStaffCount_All(QueryData<GetTeamLeaderQueryModel> query)
//{
// var lr = new DataResult<List<IHealth_staff_Model>>();
// string strWhere = " where 1= 1 ";
// strWhere += string.IsNullOrEmpty(query.Criteria.teamNO) ? string.Empty : string.Format(" and K.StaffNo = '{0}' ", query.Criteria.teamNO);
// strWhere += string.IsNullOrEmpty(query.Criteria.teamName) ? string.Empty : string.Format(" and K.STAFFName = '{0}' ", query.Criteria.teamName);
// string sql = string.Format(@"select * from (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}')) K " + 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;
//}
/// <summary>
/// 保存体检详细信息
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <DataResult <int> > InsertHealthContent(QueryData <InsertHealthContentQuery> query)
{
var lr = new DataResult <int>();
string condition = string.Format(@"insert into health_content(ContentId,titleId,TitleType,Answer,Creator,CreateTime,CreateName,IsPass,NotPassReson) values(@ContentId,@titleId,@TitleType,@Answer,@Creator,@CreateTime,@CreateName,@IsPass,@NotPassReson)");
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
lr.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, condition, query.Criteria);
if (lr.Data < 0)
{
lr.SetErr("保存体检详情异常", lr.Data);
}
if (lr.Data == 0)
{
lr.SetErr("保存体检详情失败", -102);
}
}
catch (Exception ex)
{
lr.SetErr(ex, -500);
}
}
return(lr);
}
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_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);
}
public async Task <DataResult <int> > HealthTitleDeleteAsync(QueryData <HealthTitleDeleteQuery> query)
{
var result = new DataResult <int>();
string sql = @"delete from dbo.health_title where [TitleId]=@TitleId";
string sqlc = @"select * from dbo.health_title where TitleId=@TitleId";
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
result.Data = await MssqlHelper.QueryCountAsync(dbConn, sqlc, new { TitleId = query.Criteria.TitleId });
if (result.Data <= 0)
{
result.SetErr("标题不存在！", -101);
return(result);
}
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sql, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("数据删除失败！", -101);
}
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = -1;
}
}
return(result);
}
public async Task <DataResult <int> > HealthStaffBatchDeleteAsync(QueryData <HealthStaffBatchDeleteQuery> query)
{
var result = new DataResult <int>();
string sql = @"delete from [dbo].[health_staff] where StaffNo=@StaffNo";
string sqls = @"select * from [dbo].[health_staff] where StaffNo=@StaffNo";
string sqlt = @"delete from dbo.health_user_staff where UserNo=@StaffNo or StaffNo=@StaffNo";
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
IDbTransaction transaction = dbConn.BeginTransaction();
try
{
foreach (var staffNo in query.Criteria.LstStaffNo)
{
result.Data = await MssqlHelper.QueryCountAsync(dbConn, sqls, new { StaffNo = staffNo }, transaction);
if (result.Data <= 0)
{
transaction.Rollback();
result.SetErr("员工数据已被删除，请重试！", -101);
return(result);
}
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sqlt, new { StaffNo = staffNo }, transaction);
if (result.Data < 0)
{
transaction.Rollback();
result.SetErr("删除员工权限失败，请重试！", -101);
return(result);
}
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sql, new { StaffNo = staffNo }, transaction);
if (result.Data <= 0)
{
transaction.Rollback();
result.SetErr("删除员工资料失败，请重试！", -101);
return(result);
}
}
transaction.Commit();
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = -1;
}
}
return(result);
}
public async Task <DataResult <int> > HealthUserStaffSaveAsync(QueryData <HealthUserStaffSaveQuery> query)
{
var result = new DataResult <int>();
string sql = @"insert into dbo.health_user_staff([UserNo],[StaffNo],[Creator],[CreateName],[CreateTime])
values(@UserNo,@StaffNo,@Creator,@CreateName,getdate())";
string sqlc = @"select * from dbo.health_user_staff where UserNo=@UserNo and StaffNo=@StaffNo";
string sqll = @"select * from dbo.health_staff where StaffNo=@UserNo";
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
IDbTransaction transaction = dbConn.BeginTransaction();
try
{
foreach (var item in query.Criteria.LstUserStaff)
{
result.Data = await MssqlHelper.QueryCountAsync(dbConn, sqll, new { UserNo = item.UserNo }, transaction);
if (result.Data <= 0)
{
transaction.Rollback();
result.SetErr("保存失败，上级工号不存在！", -101);
return(result);
}
result.Data = await MssqlHelper.QueryCountAsync(dbConn, sqlc, new { UserNo = item.UserNo, StaffNo = item.StaffNo }, transaction);
if (result.Data > 0)
{
continue;
}
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sql, item, transaction);
if (result.Data <= 0)
{
transaction.Rollback();
result.SetErr("数据保存失败！", -101);
return(result);
}
}
transaction.Commit();
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = -1;
transaction.Rollback();
}
}
return(result);
}
public async Task <DataResult <List <IHealthStaff> > > GetHealthStaffPageAsync(QueryData <HealthStaffQuery> query)
{
var result = new DataResult <List <IHealthStaff> >();
string condition = @" where 1=1 ";
condition += string.IsNullOrEmpty(query.Criteria.StaffName) ? string.Empty : string.Format(" and StaffName like '%{0}%' ", query.Criteria.StaffName);
condition += string.IsNullOrEmpty(query.Criteria.StaffNo) ? string.Empty : string.Format(" and StaffNo = '{0}' ", query.Criteria.StaffNo);
condition += string.IsNullOrEmpty(query.Criteria.GroupLeader) ? string.Empty : string.Format(" and GroupLeader like '%{0}%' ", query.Criteria.GroupLeader);
condition += string.IsNullOrEmpty(query.Criteria.GroupLeaderNo) ? string.Empty : string.Format(" and GroupLeaderNo = '{0}' ", query.Criteria.GroupLeaderNo);
condition += string.IsNullOrEmpty(query.Criteria.AggLeader) ? string.Empty : string.Format(" and AggLeader like '%{0}%' ", query.Criteria.AggLeader);
condition += string.IsNullOrEmpty(query.Criteria.AggLeaderNo) ? string.Empty : string.Format(" and AggLeaderNo = '{0}' ", query.Criteria.AggLeaderNo);
condition += string.IsNullOrEmpty(query.Criteria.CommandLeader) ? string.Empty : string.Format(" and CommandLeader like '%{0}%' ", query.Criteria.CommandLeader);
condition += string.IsNullOrEmpty(query.Criteria.CommondLeaderNo) ? string.Empty : string.Format(" and CommondLeaderNo = '{0}' ", query.Criteria.CommondLeaderNo);
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);
condition += string.IsNullOrEmpty(query.Criteria.GroupType) ? string.Empty : string.Format(" and GroupType = '{0}' ", query.Criteria.GroupType);
string sql = string.Format(@"SELECT [Id]
,[StaffNo]
,[StaffName]
,[GroupType]
,[GroupLeader]
,[GroupLeaderNo]
,[AggLeader]
,[AggLeaderNo]
,[CommandLeader]
,[CommondLeaderNo]
,[HrLeader]
,[HrLeaderNo]
FROM [dbo].[health_staff] {0}", condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryPageAsync <HealthStaff>(dbConn, "Id desc", sql, query.PageModel);
result.Data = modelList.ToList <IHealthStaff>();
result.PageInfo = query.PageModel;
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = null;
}
}
return(result);
}
public async Task <DataResult <List <IHealthTitle> > > GetHealthTitlePageAsync(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}", condition);
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
try
{
var modelList = await MssqlHelper.QueryPageAsync <HealthTitle>(dbConn, "Id asc", sql, query.PageModel);
result.Data = modelList.ToList <IHealthTitle>();
result.PageInfo = query.PageModel;
}
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> > > GetHealthContentStaffPageAsync(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.QueryPageAsync <HealthContentStaff>(dbConn, "Id asc", sql, query.PageModel);
result.Data = modelList.ToList <IHealthContentStaff>();
result.PageInfo = query.PageModel;
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = null;
}
}
return(result);
}
public async Task <DataResult <int> > HealthStaffImportAsync(QueryData <HealthStaffImportQuery> query)
{
var result = new DataResult <int>();
string sqlsi = @"insert into [dbo].[health_staff]([StaffNo],[StaffName],[GroupType],[GroupLeader],[GroupLeaderNo],[AggLeader],[AggLeaderNo],[CommandLeader],[CommondLeaderNo],[HrLeader],[HrLeaderNo])
values(@StaffNo,@StaffName,@GroupType,@GroupLeader,@GroupLeaderNo,@AggLeader,@AggLeaderNo,@CommandLeader,@CommondLeaderNo,@HrLeader,@HrLeaderNo)";
string sqlui = @"insert into dbo.health_user_staff([UserNo],[StaffNo],[Creator],[CreateName],[CreateTime])
values(@UserNo,@StaffNo,@Creator,@CreateName,getdate())";
string sqlss = @"select * from [dbo].[health_staff] where [StaffNo]=@StaffNo";
using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn))
{
IDbTransaction transaction = dbConn.BeginTransaction();
try
{
foreach (var item in query.Criteria.LstStaff)//保存员工信息
{
result.Data = await MssqlHelper.QueryCountAsync(dbConn, sqlss, new { StaffNo = item.StaffNo }, transaction);
if (result.Data > 0)
{
transaction.Rollback();
result.SetErr(string.Format("员工工号{0}已存在，请重新检查！", item.StaffNo), -101);
return(result);
}
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sqlsi, item, transaction);
if (result.Data <= 0)
{
transaction.Rollback();
result.SetErr(string.Format("员工工号{0}保存失败！", item.StaffNo), -101);
return(result);
}
}
#region 添加组长权限
foreach (var item in query.Criteria.LstGroupStaff)
{
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sqlui, item, transaction);
if (result.Data <= 0)
{
transaction.Rollback();
result.SetErr(string.Format("组长权限{0}|{1}保存失败！", item.UserNo, item.StaffNo), -101);
return(result);
}
}
#endregion
#region 添加集合组组长权限
foreach (var item in query.Criteria.LstAggStaff)
{
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sqlui, item, transaction);
if (result.Data <= 0)
{
transaction.Rollback();
result.SetErr(string.Format("集合组长权限{0}|{1}保存失败！", item.UserNo, item.StaffNo), -101);
return(result);
}
}
#endregion
#region 添加HR负责人权限
foreach (var item in query.Criteria.LstHrStaff)
{
result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sqlui, item, transaction);
if (result.Data <= 0)
{
transaction.Rollback();
result.SetErr(string.Format("HR负责人权限{0}|{1}保存失败！", item.UserNo, item.StaffNo), -101);
return(result);
}
}
#endregion
transaction.Commit();
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = -1;
}
}
return(result);
}