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> > 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> > 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);
}
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> > 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> > 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> > 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);
}