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<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 <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> > 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);
}
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);
}
/// <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 <int> > PositionRoleDeleteAsync(QueryData <SysPositionRoleDeleteQuery> query)
{
var result = new DataResult <int>();
string sqld = @"delete from sys_positionrole where PositionNo=@PositionNo and RoleNo=@RoleNo";
string sqlc = @"select Id from sys_positionrole where PositionNo=@PositionNo and RoleNo=@RoleNo";
using (IDbConnection dbConn = MysqlHelper.OpenMysqlConnection(ConfigOptions.MysqlOptConn))
{
try
{
result.Data = await MysqlHelper.QueryCountAsync(dbConn, sqlc, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("角色不存在或已被删除，请重试！", -101);
return(result);
}
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqld, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("删除失败！", -101);
return(result);
}
}
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);
}
public async Task <DataResult <int> > RoleModuleSaveOrUpdateAsync(QueryData <SysRoleModuleSaveOrUpdateQuery> query)
{
var result = new DataResult <int>();
string sqli = @"insert into sys_rolemodule(RoleNo,ModuleNo,Creator,CreateName,CreateTime)
values(@RoleNo,@ModuleNo,@Creator,@CreateName,@CreateTime)";
string sqld = @"delete from sys_rolemodule where RoleNo=@RoleNo";
using (IDbConnection dbConn = MysqlHelper.OpenMysqlConnection(ConfigOptions.MysqlOptConn))
{
IDbTransaction transaction = dbConn.BeginTransaction();
try
{
if (!string.IsNullOrEmpty(query.Criteria.RoleNo))
{
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqld, query.Criteria, transaction);
if (result.Data < 0)
{
result.SetErr("更新模块权限失败！", -101);
transaction.Rollback();
return(result);
}
var date = DateTime.Now;
foreach (var moduleNo in query.Criteria.LstModuleNo)
{
ISysRolePowerModel model = IoCContainer.Resolve <ISysRolePowerModel>();
model.RoleNo = query.Criteria.RoleNo;
model.ModuleNo = moduleNo;
model.Creator = query.Extend.UserNo;
model.CreateName = query.Extend.UserName;
model.CreateTime = date;
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqli, model, transaction);
if (result.Data <= 0)
{
result.SetErr("更新模块权限失败！", -101);
transaction.Rollback();
return(result);
}
}
}
else
{
result.SetErr("未选择角色无法更新权限！", -101);
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
result.SetErr(ex, -500);
result.Data = -1;
}
}
return(result);
}
public async Task <DataResult <int> > RoleDeleteAsync(QueryData <SysRoleDeleteQuery> query)
{
var result = new DataResult <int>();
string sqld = @"delete from sys_role where RoleNo=@RoleNo";
string sqldm = @"delete from sys_rolemodule where RoleNo=@RoleNo";
string sqldp = @"delete from sys_rolepower where RoleNo=@RoleNo";
string sqlc = @"select Id from sys_role where RoleNo=@RoleNo";
using (IDbConnection dbConn = MysqlHelper.OpenMysqlConnection(ConfigOptions.MysqlOptConn))
{
IDbTransaction transaction = dbConn.BeginTransaction();
try
{
result.Data = await MysqlHelper.QueryCountAsync(dbConn, sqlc, query.Criteria, transaction);
if (result.Data <= 0)
{
result.SetErr("角色不存在或已被删除，请重试！", -101);
transaction.Rollback();
return(result);
}
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqld, query.Criteria, transaction);
if (result.Data <= 0)
{
result.SetErr("删除角色失败！", -101);
transaction.Rollback();
return(result);
}
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqldm, query.Criteria, transaction);
if (result.Data < 0)
{
result.SetErr("删除模块权限失败！", -101);
transaction.Rollback();
return(result);
}
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqldp, query.Criteria, transaction);
if (result.Data < 0)
{
result.SetErr("删除按钮权限失败！", -101);
transaction.Rollback();
return(result);
}
transaction.Commit();
}
catch (Exception ex)
{
result.SetErr(ex, -500);
transaction.Rollback();
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 <int> > ModuleDeleteAsync(QueryData <SysModuleDeleteQuery> query)
{
var result = new DataResult <int>();
string sqld = @"delete from sys_module where ModuleNo=@ModuleNo";
string sqlu = @"update sys_module set IsDelete=@IsDelete where ModuleNo=@ModuleNo";
string sqlc = @"select Id from sys_module where ModuleNo=@ModuleNo";
using (IDbConnection dbConn = MysqlHelper.OpenMysqlConnection(ConfigOptions.MysqlOptConn))
{
try
{
result.Data = await MysqlHelper.QueryCountAsync(dbConn, sqlc, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("模块不存在或已被删除，请重试！", -101);
return(result);
}
if (query.Criteria.IsDelete)
{
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqlu, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("删除失败！", -101);
return(result);
}
}
else
{
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqld, 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_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 <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);
}
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 <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 <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 = 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);
}
/// <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_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_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);
}
public async Task <DataResult <IADUserInfo> > GetADUserInfoAsync(QueryData <WcfADUserInfoQuery> query)
{
var result = new DataResult <IADUserInfo>();
try
{
using (var chnl = WcfInvoke.CreateWCFChannel <lytechWebServiceSoap>(WcfInvoke.TlgChinaServiceUrl))
{
var proxy = chnl.CreateChannel();
var res = await proxy.GetADuserAsync(query.Criteria.UserName, query.Criteria.Password);
if (!string.IsNullOrEmpty(res))
{
var users = res.Split('|');
IADUserInfo info = new ADUserInfo();
info.UserNo = users[0];
info.UserName = users[1];
info.ADAccount = users[2];
result.Data = info;
}
}
}
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);
}
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 <string> > GetUserADGuidAsync(QueryData <WcfADUserGuidQuery> query)
{
var result = new DataResult <string>();
try
{
using (var chnl = WcfInvoke.CreateWCFChannel <lytechWebServiceSoap>(WcfInvoke.TlgChinaServiceUrl))
{
var proxy = chnl.CreateChannel();
result.Data = await proxy.GetUserADGUIDAsync(query.Criteria.UserName, query.Criteria.Password);
}
//var binding = new BasicHttpBinding();
////根据 WebService 的 URL 构建终端点对象
//var endpoint = new EndpointAddress(@"http://ws.tlgchina.com:5678/lytechWebService.asmx");
////创建调用接口的工厂，注意这里泛型只能传入接口
//var factory = new ChannelFactory<lytechWebServiceSoap>(binding, endpoint);
////从工厂获取具体的调用实例
//var callClient = factory.CreateChannel();
////调用具体的方法，这里是 HelloWorldAsync 方法
//result.Data = await callClient.GetUserADGUIDAsync(query.Criteria.UserName, query.Criteria.Password);
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = string.Empty;
}
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);
}
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> > CompanySaveOrUpdateAsync(QueryData <SysCompanySaveOrUpdateQuery> query)
{
var result = new DataResult <int>();
string sqli = @"insert into sys_company(CompanyNo,CompanyName,Abbreviation,Address,Industry,LegalPerson,Contact,Phone,Mobile,Email,Website,Access,Creator,CreateName,CreateTime,IsDelete)
values(@CompanyNo,@CompanyName,@Abbreviation,@Address,@Industry,@LegalPerson,@Contact,@Phone,@Mobile,@Email,@Website,@Access,@Creator,@CreateName,@CreateTime,@IsDelete)";
string sqlu = @"update sys_company set CompanyName=@CompanyName,Abbreviation=@Abbreviation,Address=@Address,Industry=@Industry,LegalPerson=@LegalPerson,Contact=@Contact,Phone=@Phone,
Mobile=@Mobile,Email=@Email,Website=@Website,Access=@Access,Updator=@Updator,UpdateName=@UpdateName,UpdateTime=@UpdateTime,IsDelete=@IsDelete
where CompanyNo=@CompanyNo";
string sqlc = @"select Id from sys_company where CompanyNo=@CompanyNo";
using (IDbConnection dbConn = MysqlHelper.OpenMysqlConnection(ConfigOptions.MysqlOptConn))
{
try
{
// 新增
if (string.IsNullOrEmpty(query.Criteria.CompanyNo))
{
query.Criteria.CompanyNo = Guid.NewGuid().ToString("N");
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqli, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("新增公司信息失败！", -101);
return(result);
}
else
{
result.SetErr("新增公司信息成功！", 200);
}
}
else // 更新
{
result.Data = await MysqlHelper.QueryCountAsync(dbConn, sqlc, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("公司不存在或已被删除，请重试！", -101);
return(result);
}
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqlu, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("更新公司信息失败！", -101);
return(result);
}
result.SetErr("更新公司信息成功！", 200);
}
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = -1;
}
}
return(result);
}
public async Task <DataResult <int> > ModuleSaveOrUpdateAsync(QueryData <SysModuleSaveOrUpdateQuery> query)
{
var result = new DataResult <int>();
string sqli = @"insert into sys_module(ModuleNo,ModuleName,ParentNo,Icon,Url,Category,Target,AppNo,Creator,CreateName,CreateTime,Sort,IsDelete,RouterName)
values(@ModuleNo,@ModuleName,@ParentNo,@Icon,@Url,@Category,@Target,@AppNo,@Creator,@CreateName,@CreateTime,@Sort,@IsDelete,@RouterName)";
string sqlu = @"update sys_module set ModuleName=@ModuleName,ParentNo=@ParentNo,Icon=@Icon,Url=@Url,Category=@Category,Target=@Target,
AppNo=@AppNo,Updator=@Updator,UpdateName=@UpdateName,UpdateTime=@UpdateTime,Sort=@Sort,IsDelete=@IsDelete,RouterName=@RouterName
where ModuleNo=@ModuleNo";
string sqlc = @"select Id from sys_module where ModuleNo=@ModuleNo";
using (IDbConnection dbConn = MysqlHelper.OpenMysqlConnection(ConfigOptions.MysqlOptConn))
{
try
{
// 新增
if (string.IsNullOrEmpty(query.Criteria.ModuleNo))
{
query.Criteria.ModuleNo = Guid.NewGuid().ToString("N");
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqli, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("新增模块信息失败！", -101);
return(result);
}
else
{
result.SetErr("新增模块信息成功！", 200);
}
}
else // 更新
{
result.Data = await MysqlHelper.QueryCountAsync(dbConn, sqlc, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("模块不存在或已被删除，请重试！", -101);
return(result);
}
result.Data = await MysqlHelper.ExecuteSqlAsync(dbConn, sqlu, query.Criteria);
if (result.Data <= 0)
{
result.SetErr("更新模块信息失败！", -101);
return(result);
}
result.SetErr("更新模块信息成功！", 200);
}
}
catch (Exception ex)
{
result.SetErr(ex, -500);
result.Data = -1;
}
}
return(result);
}
