884 lines
39 KiB
C#
884 lines
39 KiB
C#
|
using CYQ.Data;
|
|||
|
|
using Dapper;
|
|||
|
|
using dccdc.Models;
|
|||
|
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Threading.Tasks;
|
|||
|
|
|
|||
|
|
namespace dccdc.DAL
|
|||
|
|
{
|
|||
|
|
public class occupation_reportDal
|
|||
|
|
{
|
|||
|
|
public List<occupation_reportModel> GetAllList(string id)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from occupation_report where 1=1";
|
|||
|
|
if (!string.IsNullOrEmpty(id))
|
|||
|
|
{
|
|||
|
|
sql += " and id=@id";
|
|||
|
|
}
|
|||
|
|
sql += " order by id desc";
|
|||
|
|
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<occupation_reportModel>(sql, new { @id = id }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
public List<occupation_reportModel> GetAllList(string start, string end, string where, string sort)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from occupation_report where 1=1 and report_date>=@start and report_date<=@end";
|
|||
|
|
switch (sort)
|
|||
|
|
{
|
|||
|
|
case "1":
|
|||
|
|
sql += " and report_status = '已编制'";
|
|||
|
|
break;
|
|||
|
|
case "2":
|
|||
|
|
sql += " and report_status = '已提交'";
|
|||
|
|
break;
|
|||
|
|
case "3":
|
|||
|
|
sql += " and report_status = '已审核'";
|
|||
|
|
break;
|
|||
|
|
case "4":
|
|||
|
|
sql += " and report_status = '已签发'";
|
|||
|
|
break;
|
|||
|
|
case "5":
|
|||
|
|
sql += " and report_status is not null";
|
|||
|
|
break;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (where != "")
|
|||
|
|
{
|
|||
|
|
sql += " and (report_num like @where or subject_company like @where)";
|
|||
|
|
}
|
|||
|
|
sql += " order by report_date";
|
|||
|
|
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<occupation_reportModel>(sql, new { @where = "%" + where + "%", @start = start, @end = end }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<ProfessionalExamRegisterModel> GetRegesite(string start, string end, string where, string sort)
|
|||
|
|
{
|
|||
|
|
string sql = @"select * from professionalExam_register where register_date>=@start and register_date<=@end
|
|||
|
|
and(status =@sort) and(util_name like @where) and (procedure_status = '主检医生已审核' or procedure_status = '已打印健康证') and(physical_type like '职业体检')
|
|||
|
|
and(physical_num like '2%') and(freezing_and_thawing is null)";
|
|||
|
|
//20240106 xulu 不根据在岗状态查询
|
|||
|
|
if (string.IsNullOrEmpty(sort))
|
|||
|
|
{
|
|||
|
|
sql = @"select * from professionalExam_register where register_date>=@start and register_date<=@end
|
|||
|
|
and(util_name like @where) and (procedure_status = '主检医生已审核' or procedure_status = '已打印健康证') and(physical_type like '职业体检')
|
|||
|
|
and(physical_num like '2%') and(freezing_and_thawing is null)";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<ProfessionalExamRegisterModel>(sql, new { @where = where, @sort = sort, @start = start, @end = end }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取登记记录
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="personids"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ProfessionalExamRegisterModel> GetRegesite(string personids)
|
|||
|
|
{
|
|||
|
|
string sql = @"select * from professionalExam_register where id in @personids";
|
|||
|
|
string[] ids = personids.Split(',');
|
|||
|
|
List<int> ids2 = new List<int>();
|
|||
|
|
foreach (string id in ids)
|
|||
|
|
{
|
|||
|
|
ids2.Add(Convert.ToInt32(id));
|
|||
|
|
}
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
//获取登记记录
|
|||
|
|
var result = conn.Query<ProfessionalExamRegisterModel>(sql, new { personids = ids2.ToArray() }).ToList();
|
|||
|
|
//获取 对应的复检 登记记录
|
|||
|
|
var result2 = getfjlist(result).ToList();
|
|||
|
|
//合并
|
|||
|
|
result = result.Concat(result2).ToList();
|
|||
|
|
var list1 = new List<ProfessionalExamRegisterModel>();
|
|||
|
|
//去重
|
|||
|
|
foreach (var v in result)
|
|||
|
|
if (!list1.Where(t => t.id == v.id).Any())
|
|||
|
|
{
|
|||
|
|
list1.Add(v);
|
|||
|
|
}
|
|||
|
|
return list1;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private List<ProfessionalExamRegisterModel> getfjlist(List<ProfessionalExamRegisterModel> result)
|
|||
|
|
{
|
|||
|
|
//throw new NotImplementedException();
|
|||
|
|
List<string> olds = new List<string>();
|
|||
|
|
foreach (var r in result)
|
|||
|
|
{
|
|||
|
|
if (!string.IsNullOrEmpty(r.physical_num))
|
|||
|
|
olds.Add(r.physical_num);
|
|||
|
|
}
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
string sql = "select * from professionalExam_register where physical_num_old is not null and physical_num_old in @olds";
|
|||
|
|
var result2 = conn.Query<ProfessionalExamRegisterModel>(sql, new { olds = olds }).ToList();
|
|||
|
|
if (result2.Count == 0)
|
|||
|
|
{
|
|||
|
|
return result2;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return result2.Concat(getfjlist(result2)).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public int GetJobId(string job)
|
|||
|
|
{
|
|||
|
|
string sql = @"select id from [jobs_state_maintain] where jobs_state>=@job";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query(sql, new { @job = job }).FirstOrDefault().id;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取检验项目 结果列表
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="personids"></param>
|
|||
|
|
/// <param name="JYXM"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ProfessionalExamProjectResultModel> GetRegesitePR(string personids, bool JYXM)
|
|||
|
|
{
|
|||
|
|
string sql = @"select a.*,b.team_name from professionalExam_project_result a
|
|||
|
|
join exam_group_maintain b on b.id=a.exam_group_maintain_id
|
|||
|
|
where a.person_id in @personids";
|
|||
|
|
if (JYXM)
|
|||
|
|
sql += " and a.exam_group_maintain_id in(select id from exam_group_maintain where up_level='化验检查')";
|
|||
|
|
string[] ids = personids.Split(',');
|
|||
|
|
List<int> ids2 = new List<int>();
|
|||
|
|
foreach (string id in ids)
|
|||
|
|
{
|
|||
|
|
ids2.Add(Convert.ToInt32(id));
|
|||
|
|
}
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<ProfessionalExamProjectResultModel>(sql, new { personids = ids2.ToArray() }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 查询检查项目的参考值
|
|||
|
|
/// 2023-12-12 xulu
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="personids"></param>
|
|||
|
|
/// <param name="JYXM"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ProfessionalExamProjectResultModel> GetRegesitePR1(string personids, bool JYXM)
|
|||
|
|
{
|
|||
|
|
string sql = @"select distinct a.project_name,a.reference_value,a.project_unit,b.team_name from exam_group_maintain b
|
|||
|
|
join professionalExam_project_result a on b.id=a.exam_group_maintain_id
|
|||
|
|
where a.person_id in @personids";
|
|||
|
|
if (JYXM)
|
|||
|
|
sql += " and a.exam_group_maintain_id in(select id from exam_group_maintain where up_level='化验检查')";
|
|||
|
|
string[] ids = personids.Split(',');
|
|||
|
|
List<int> ids2 = new List<int>();
|
|||
|
|
foreach (string id in ids)
|
|||
|
|
{
|
|||
|
|
ids2.Add(Convert.ToInt32(id));
|
|||
|
|
}
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<ProfessionalExamProjectResultModel>(sql, new { personids = ids2.ToArray() }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//public List<contraindicat_factor_maintainDal> getHazard1(string hazardids, string jobid)
|
|||
|
|
//{
|
|||
|
|
// if (string.IsNullOrEmpty(hazards))
|
|||
|
|
// return Json(null);
|
|||
|
|
// var bll = new occupation_reportBll();
|
|||
|
|
// var result = bll.GetRegesitePR(personids.TrimStart(',').TrimEnd(','), true);
|
|||
|
|
// return Json(new { Rows = result });
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
//public JsonResult getHazard1(string hazards, string jobs)
|
|||
|
|
//{
|
|||
|
|
// if (string.IsNullOrEmpty(hazards))
|
|||
|
|
// return Json(null);
|
|||
|
|
// var bll = new occupation_reportBll();
|
|||
|
|
// var result = bll.GetRegesitePR(personids.TrimStart(',').TrimEnd(','), true);
|
|||
|
|
// return Json(new { Rows = result });
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
public string GetGroups(string idsP)
|
|||
|
|
{
|
|||
|
|
//personids，命名有问题，此处应该是项目的IDs:check_item_ids
|
|||
|
|
string sql = @"select distinct exam_group from exam_project_maintain where id in @personids";
|
|||
|
|
string[] ids = idsP.Split(',');
|
|||
|
|
List<int> ids2 = new List<int>();
|
|||
|
|
foreach (string id in ids)
|
|||
|
|
{
|
|||
|
|
ids2.Add(Convert.ToInt32(id));
|
|||
|
|
}
|
|||
|
|
string result = "";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
var models = conn.Query<ExamProjectMaintainModel>(sql, new { personids = ids2.ToArray() }).ToList();
|
|||
|
|
foreach (ExamProjectMaintainModel m in models)
|
|||
|
|
{
|
|||
|
|
result += m.exam_group + ",";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取结果记录
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="personids"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ProfessionalExamProjectResultModel> GetRegesiteResult(string personids)
|
|||
|
|
{
|
|||
|
|
string sql = @"select * from professionalExam_project_result where person_id in @personids";
|
|||
|
|
string[] ids = personids.Split(',');
|
|||
|
|
List<int> ids2 = new List<int>();
|
|||
|
|
foreach (string id in ids)
|
|||
|
|
{
|
|||
|
|
ids2.Add(Convert.ToInt32(id));
|
|||
|
|
}
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<ProfessionalExamProjectResultModel>(sql, new { personids = ids2.ToArray() }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public object save(occupation_reportModel model)
|
|||
|
|
{
|
|||
|
|
string sql = "";
|
|||
|
|
if (model.id == 0)
|
|||
|
|
{
|
|||
|
|
var rep = GetAllList("").Where(t => t.report_num.StartsWith(DateTime.Now.Year.ToString())).OrderByDescending(t => t.report_num).FirstOrDefault();
|
|||
|
|
string num = "";
|
|||
|
|
if (rep == null)
|
|||
|
|
num = DateTime.Now.Year + "0001";
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
num = rep.report_num.Substring(0, 4) + (int.Parse(rep.report_num.Substring(4)) + 1).ToString().PadLeft(4, '0');
|
|||
|
|
}
|
|||
|
|
model.report_num = num;
|
|||
|
|
|
|||
|
|
sql = @"INSERT INTO [dbo].[occupation_report]
|
|||
|
|
([physical_num]
|
|||
|
|
,[report_num]
|
|||
|
|
,[check_start_time]
|
|||
|
|
,[check_end_time]
|
|||
|
|
,[subject_company]
|
|||
|
|
,[entrust_company]
|
|||
|
|
,[count_unit]
|
|||
|
|
,[evaluation_basic]
|
|||
|
|
,[diagnostic_standard]
|
|||
|
|
,[status]
|
|||
|
|
,[descript]
|
|||
|
|
,[expersion_disease]
|
|||
|
|
,[other_disease]
|
|||
|
|
,[result_description]
|
|||
|
|
,[per_factory_analysis]
|
|||
|
|
,[treatment_opinion]
|
|||
|
|
,[person_ids]
|
|||
|
|
,[report_user_name]
|
|||
|
|
,[report_person]
|
|||
|
|
,[report_date]
|
|||
|
|
,[report_status]
|
|||
|
|
,[approve_user_name]
|
|||
|
|
,[approve_person]
|
|||
|
|
,[approve_date]
|
|||
|
|
,[approve_back_desc]
|
|||
|
|
,[issue_user_name]
|
|||
|
|
,[issue_person]
|
|||
|
|
,[issue_date]
|
|||
|
|
,[issue_back_desc]
|
|||
|
|
,[print_date]
|
|||
|
|
,[print_person]
|
|||
|
|
,[report_edit_type]
|
|||
|
|
,[report_url]
|
|||
|
|
,[hazards_text],[check_items],[target_disease])
|
|||
|
|
VALUES
|
|||
|
|
(@physical_num
|
|||
|
|
,@report_num
|
|||
|
|
,@check_start_time
|
|||
|
|
,@check_end_time
|
|||
|
|
,@subject_company
|
|||
|
|
,@entrust_company
|
|||
|
|
,@count_unit
|
|||
|
|
,@evaluation_basic
|
|||
|
|
,@diagnostic_standard
|
|||
|
|
,@status
|
|||
|
|
,@descript
|
|||
|
|
,@expersion_disease
|
|||
|
|
,@other_disease
|
|||
|
|
,@result_description
|
|||
|
|
,@per_factory_analysis
|
|||
|
|
,@treatment_opinion
|
|||
|
|
,@person_ids
|
|||
|
|
,@report_user_name
|
|||
|
|
,@report_person
|
|||
|
|
,@report_date
|
|||
|
|
,@report_status
|
|||
|
|
,@approve_user_name
|
|||
|
|
,@approve_person
|
|||
|
|
,@approve_date
|
|||
|
|
,@approve_back_desc
|
|||
|
|
,@issue_user_name
|
|||
|
|
,@issue_person
|
|||
|
|
,@issue_date
|
|||
|
|
,@issue_back_desc
|
|||
|
|
,@print_date
|
|||
|
|
,@print_person
|
|||
|
|
,@report_edit_type
|
|||
|
|
,@report_url
|
|||
|
|
,@hazards_text,@check_items,@target_disease)";
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sql = @"UPDATE [dbo].[occupation_report]
|
|||
|
|
SET [physical_num] = @physical_num
|
|||
|
|
,[report_num] = @report_num
|
|||
|
|
,[check_start_time] = @check_start_time
|
|||
|
|
,[check_end_time] = @check_end_time
|
|||
|
|
,[subject_company] = @subject_company
|
|||
|
|
,[entrust_company] = @entrust_company
|
|||
|
|
,[count_unit] = @count_unit
|
|||
|
|
,[evaluation_basic] = @evaluation_basic
|
|||
|
|
,[diagnostic_standard] = @diagnostic_standard
|
|||
|
|
,[status] = @status
|
|||
|
|
,[descript] = @descript
|
|||
|
|
,[expersion_disease] = @expersion_disease
|
|||
|
|
,[other_disease] = @other_disease
|
|||
|
|
,[result_description] = @result_description
|
|||
|
|
,[per_factory_analysis] = @per_factory_analysis
|
|||
|
|
,[treatment_opinion] = @treatment_opinion
|
|||
|
|
,[person_ids] = @person_ids
|
|||
|
|
,[report_user_name] = @report_user_name
|
|||
|
|
,[report_person] = @report_person
|
|||
|
|
,[report_date] = @report_date
|
|||
|
|
,[report_status] = @report_status
|
|||
|
|
,[approve_user_name] = @approve_user_name
|
|||
|
|
,[approve_person] = @approve_person
|
|||
|
|
,[approve_date] = @approve_date
|
|||
|
|
,[approve_back_desc] = @approve_back_desc
|
|||
|
|
,[issue_user_name] = @issue_user_name
|
|||
|
|
,[issue_person] = @issue_person
|
|||
|
|
,[issue_date] = @issue_date
|
|||
|
|
,[issue_back_desc] = @issue_back_desc
|
|||
|
|
,[print_date] = @print_date
|
|||
|
|
,[print_person] = @print_person
|
|||
|
|
,[report_edit_type] = @report_edit_type
|
|||
|
|
,[report_url] = @report_url
|
|||
|
|
,[hazards_text]=@hazards_text
|
|||
|
|
,[check_items]=@check_items
|
|||
|
|
,[target_disease]=@target_disease
|
|||
|
|
WHERE id=@id";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
int result = conn.Execute(sql, model);
|
|||
|
|
if (result > 0)
|
|||
|
|
return new { State = 1, Message = "保存成功！" };
|
|||
|
|
else
|
|||
|
|
return new { State = 0, Message = "保存失败！" };
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
return new { State = 0, Message = ex.Message };
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public object delete(string id)
|
|||
|
|
{
|
|||
|
|
string sql = "delete from occupation_report where id=@id";
|
|||
|
|
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
int result = conn.Execute(sql, new { id = id });
|
|||
|
|
if (result > 0)
|
|||
|
|
return new { State = 1, Message = "操作成功！" };
|
|||
|
|
else
|
|||
|
|
return new { State = 0, Message = "操作失败！" };
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
return new { State = 0, Message = ex.Message };
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public object changeStatus(string id, string sort, string descript, ERPUser user)
|
|||
|
|
{
|
|||
|
|
string sql = "";
|
|||
|
|
string now = DateTime.Now.ToString("yyyy-MM-dd");
|
|||
|
|
switch (sort)
|
|||
|
|
{
|
|||
|
|
case "4": sql = "update occupation_report set report_status = '已提交' where id=@id"; break;
|
|||
|
|
case "5": sql = "update occupation_report set report_status = '已审核' where id=@id"; break;
|
|||
|
|
case "51": sql = "update occupation_report set report_status = '已编制',approve_user_name=@userid," +
|
|||
|
|
"approve_person=@username,approve_date=@now,approve_back_desc=@descript where id=@id"; break;
|
|||
|
|
case "6": sql = "update occupation_report set report_status = '已签发' where id = @id"; break;
|
|||
|
|
case "61": sql = "update occupation_report set report_status = '已提交',issue_user_name=@userid," +
|
|||
|
|
"issue_person=@username,issue_date=@now,issue_back_desc=@descript where id = @id"; break;
|
|||
|
|
}
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
int result = conn.Execute(sql, new { id = id, descript = descript, userid = user.ID, username = user.UserName, now = now });
|
|||
|
|
if (result > 0)
|
|||
|
|
return new { State = 1, Message = "操作成功！" };
|
|||
|
|
else
|
|||
|
|
return new { State = 0, Message = "操作失败！" };
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
return new { State = 0, Message = ex.Message };
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//根据报表编码获取汇总报告数据
|
|||
|
|
public List<occupation_reportModel> getReportByNum(string report_num)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from occupation_report where report_num=" + report_num;
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<occupation_reportModel>(sql).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//根据人员ID获取异常的体检结果
|
|||
|
|
public List<ProfessionalExamProjectResultModel> getTjjgById(string pid)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from professionalExam_project_result where qualified='不合格' and person_id=" + pid;
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<ProfessionalExamProjectResultModel>(sql).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//根据人员编码获取人员列表
|
|||
|
|
public List<ProfessionalExamRegisterModel> getRegisterById(string ids)
|
|||
|
|
{
|
|||
|
|
List<Models.ExamProjectMaintainModel> projs = new DAL.ExamProjectMaintainDal().GetAllList4("");
|
|||
|
|
/*
|
|||
|
|
* string sql = @"select * from professionalExam_register where id in (" + ids + @") union all select * from professionalExam_register
|
|||
|
|
where physical_num_old is not null
|
|||
|
|
and physical_num is not null
|
|||
|
|
and physical_num_old in ( select physical_num from professionalExam_register where id in (" + ids + @") )";
|
|||
|
|
*/
|
|||
|
|
string sql = "select * from professionalExam_register where id in (" + ids + ")";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
//当前注册记录以及对应的复检记录 并集
|
|||
|
|
var list = GetRegesite(ids);// conn.Query<ProfessionalExamRegisterModel>(sql).ToList();
|
|||
|
|
string tjjg = "";
|
|||
|
|
var list1 = new List<ProfessionalExamRegisterModel>();
|
|||
|
|
foreach (var v in list)
|
|||
|
|
{
|
|||
|
|
//存储父记录 的 体检结果
|
|||
|
|
tjjg = "";
|
|||
|
|
string pid = "";
|
|||
|
|
//递归获取父ID
|
|||
|
|
pid = getpid(list, v);
|
|||
|
|
sql = "select * from professionalExam_project_result where qualified='不合格' and person_id=" + pid;
|
|||
|
|
var list2 = conn.Query<ProfessionalExamProjectResultModel>(sql).ToList();
|
|||
|
|
if (list2 != null)
|
|||
|
|
{
|
|||
|
|
foreach (var vv in list2)
|
|||
|
|
{
|
|||
|
|
var h = projs.Where(t => t.project_id == vv.project_id).FirstOrDefault();
|
|||
|
|
string unit = "";
|
|||
|
|
if (h != null)
|
|||
|
|
unit = h.unit;
|
|||
|
|
tjjg += vv.project_name + ":" + vv.project_result + unit + "\r\n";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (tjjg == "" || (v.main_review != null && v.main_review.Contains("未见")))
|
|||
|
|
{
|
|||
|
|
if (string.IsNullOrEmpty(v.physical_num_old))
|
|||
|
|
tjjg = "本次所检项目未见明显异常。";
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
tjjg = "复检未见明显异常";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//父记录 的 体检结果 (不明白为什么不 一并判断 复检项的体检结果 ,只有一种可能，复检项结论写到了其父记录的结论中 zyk2023022)
|
|||
|
|
if(string.IsNullOrEmpty(v.check_result))
|
|||
|
|
{
|
|||
|
|
v.main_result = tjjg;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
v.main_result = v.check_result;
|
|||
|
|
}
|
|||
|
|
if (!list1.Where(t => t.id == v.id).Any())
|
|||
|
|
{
|
|||
|
|
list1.Add(v);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//HashSet<string> hs = new HashSet<ProfessionalExamRegisterModel>(list);
|
|||
|
|
return list1;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 递归方式获取 父ID
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="list"></param>
|
|||
|
|
/// <param name="v"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string getpid(List<ProfessionalExamRegisterModel> list, ProfessionalExamRegisterModel v)
|
|||
|
|
{
|
|||
|
|
//throw new NotImplementedException();
|
|||
|
|
if (string.IsNullOrEmpty(v.physical_num_old))
|
|||
|
|
return v.id;
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
var v1 = list.Where(t => t.physical_num == v.physical_num_old).FirstOrDefault();
|
|||
|
|
if (v1 == null)
|
|||
|
|
{
|
|||
|
|
return v.id;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return getpid(list, v1);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//获取化验项目
|
|||
|
|
public List<ProfessionalExamProjectResultModel> getHyxm(string ids)
|
|||
|
|
{
|
|||
|
|
string sql = "select distinct project_name,project_other,reference_value,project_unit " +
|
|||
|
|
"from professionalExam_project_result re,exam_group_maintain ex " +
|
|||
|
|
"where re.exam_group_maintain_id = ex.id and re.person_id in (" + ids + ") and ex.up_level like '%化验%'";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<ProfessionalExamProjectResultModel>(sql).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//获取 检查项目分组与接害因素 对应关系
|
|||
|
|
public string getJcxm(string str1, string str2)
|
|||
|
|
{
|
|||
|
|
if(str1 == "")
|
|||
|
|
{
|
|||
|
|
return "";
|
|||
|
|
}
|
|||
|
|
string jcxmStr = "";
|
|||
|
|
string jcxmBm = "";
|
|||
|
|
string jcxmName = "";
|
|||
|
|
int num = 1;
|
|||
|
|
string strConProject = "";
|
|||
|
|
var h = str1.Substring(0,str1.Length-1).Split(',').ToArray();
|
|||
|
|
//dic<有害因素，体检项目列表>
|
|||
|
|
Dictionary<string, List<string>> dicProjects = new Dictionary<string, List<string>>();
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
foreach (var hh in h)
|
|||
|
|
{
|
|||
|
|
if (hh != "")
|
|||
|
|
{
|
|||
|
|
string sql = "select a.harmful_factor_name,b.exam_group from harmful_factors_maintain a,project_factor_maintain b , " +
|
|||
|
|
"jobs_state_maintain c " +
|
|||
|
|
"where a.harmful_factor_type = b.factor_code and c.id = b.jobs_state_maintain_id " +
|
|||
|
|
"and a.harmful_factor_name in ('" + hh + "') and c.jobs_state like '%" + str2 + "%' group by " +
|
|||
|
|
//"and a.harmful_factor_type_name in ('" + hh + "') and c.jobs_state like '%" + str2 + "%' group by " + //应查找对应的harmful_factor_name
|
|||
|
|
"a.harmful_factor_name,b.exam_group";
|
|||
|
|
var a = conn.Query(sql).ToList();
|
|||
|
|
List<string> list = new List<string>();
|
|||
|
|
foreach (var aa in a)
|
|||
|
|
{
|
|||
|
|
/* if (jcxmBm == "")
|
|||
|
|
{
|
|||
|
|
//jcxmBm = num.ToString() + "." + aa.harmful_factor_name + "--症状询问、";
|
|||
|
|
}
|
|||
|
|
jcxmName += aa.exam_group + "、";*/
|
|||
|
|
list.Add(aa.exam_group);
|
|||
|
|
}
|
|||
|
|
/*if (jcxmName.Length > 0)
|
|||
|
|
jcxmName = jcxmName.Substring(0, jcxmName.Length - 1);
|
|||
|
|
jcxmStr += jcxmBm + jcxmName + ";" + "\r\n";
|
|||
|
|
jcxmBm = "";
|
|||
|
|
jcxmName = "";
|
|||
|
|
num++;*/
|
|||
|
|
dicProjects.Add(hh, list);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (dicProjects.Count != 0)
|
|||
|
|
{
|
|||
|
|
//提取共同项目
|
|||
|
|
List<string> listConProjects = dicProjects.First().Value;
|
|||
|
|
List<string> keys = new List<string>(dicProjects.Keys);
|
|||
|
|
for (int i = 0; i < dicProjects.Count - 1; i++)
|
|||
|
|
{
|
|||
|
|
listConProjects = listConProjects.Intersect(dicProjects[keys[i + 1]]).ToList();
|
|||
|
|
}
|
|||
|
|
//每种危害因素体检项目中去除共同项目
|
|||
|
|
for (int i = 0; i < dicProjects.Count; i++)
|
|||
|
|
{
|
|||
|
|
dicProjects[keys[i]] = dicProjects[keys[i]].Except(listConProjects).ToList();
|
|||
|
|
}
|
|||
|
|
//组合体检项目
|
|||
|
|
strConProject += String.Join("、", listConProjects.ToArray());
|
|||
|
|
|
|||
|
|
for (int i = 0; i < dicProjects.Count; i++)
|
|||
|
|
{
|
|||
|
|
if (jcxmBm == "")
|
|||
|
|
{
|
|||
|
|
jcxmBm = "接触" + keys[i] + "作业人员增加项目：";
|
|||
|
|
}
|
|||
|
|
jcxmName += String.Join("、", dicProjects[keys[i]].ToArray());
|
|||
|
|
if(jcxmName != "")
|
|||
|
|
jcxmStr += jcxmBm + jcxmName + ";" + "\r\n";
|
|||
|
|
jcxmBm = "";
|
|||
|
|
jcxmName = "";
|
|||
|
|
}
|
|||
|
|
if(strConProject !="")
|
|||
|
|
{
|
|||
|
|
jcxmStr = strConProject + "；\r\n" + jcxmStr;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return jcxmStr;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 数字转文字(最多2位数)
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="numberStr"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public string NumberToChinese(int num)
|
|||
|
|
{
|
|||
|
|
string numberStr = num.ToString();
|
|||
|
|
char[] c = numberStr.ToCharArray();
|
|||
|
|
string returnValue = "";
|
|||
|
|
for (int i = 0; i < c.Length; i++)
|
|||
|
|
{
|
|||
|
|
int index = numberStr.IndexOf(c[i]);
|
|||
|
|
returnValue += OneBitNumberToChinese(c[i].ToString());
|
|||
|
|
if (c.Length == 2 && i == 0)
|
|||
|
|
{//十
|
|||
|
|
if (c[i] == '1')
|
|||
|
|
{
|
|||
|
|
returnValue = "十";
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
returnValue += "十";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
returnValue = string.IsNullOrEmpty(returnValue) ? "零" : returnValue;
|
|||
|
|
return returnValue;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//数字1-9转换为中文数字
|
|||
|
|
public string OneBitNumberToChinese(string num)
|
|||
|
|
{
|
|||
|
|
string numStr = "123456789";
|
|||
|
|
string chineseStr = "一二三四五六七八九";
|
|||
|
|
string result = "";
|
|||
|
|
int numIndex = numStr.IndexOf(num);
|
|||
|
|
if (numIndex > -1)
|
|||
|
|
{
|
|||
|
|
result = chineseStr.Substring(numIndex, 1);
|
|||
|
|
}
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//获取本次职业健康检查的目的
|
|||
|
|
public string getZyjkjc(string str1, string str2)
|
|||
|
|
{
|
|||
|
|
var h = str1.Split(',').ToArray();
|
|||
|
|
string zymd = "";
|
|||
|
|
string mbjb = "";
|
|||
|
|
string zyjjz = "";
|
|||
|
|
string zyb = "";
|
|||
|
|
string strAll = "";
|
|||
|
|
int num1 = 1;
|
|||
|
|
int num2 = 1;
|
|||
|
|
switch (str2)
|
|||
|
|
{
|
|||
|
|
case "上岗前":
|
|||
|
|
str1 = "1";
|
|||
|
|
zymd = "上岗前健康检查，其主要目的是发现有无职业禁忌证，建立接触职业病危害因素人员的基础健康档案。" + "\r\n";
|
|||
|
|
break;
|
|||
|
|
case "在岗期间":
|
|||
|
|
str1 = "2";
|
|||
|
|
zymd = "在岗期间健康检查 ，其主要目的是早期发现职业病或疑似职业病患者或劳动者的其他健康异常改变，及时发现有职业禁忌的劳动者，评价职工健康变化与职业病危害因素的关系。" + "\r\n";
|
|||
|
|
break;
|
|||
|
|
case "离岗时":
|
|||
|
|
str1 = "5";
|
|||
|
|
zymd = "离岗时健康检查 ，其主要目的是确实其在停止接触职业病危害因素时的健康状况。" + "\r\n";
|
|||
|
|
break;
|
|||
|
|
case "离岗后":
|
|||
|
|
str1 = "8";
|
|||
|
|
break;
|
|||
|
|
default:
|
|||
|
|
str1 = "1";
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
int num = 1;
|
|||
|
|
foreach (var hh in h)
|
|||
|
|
{
|
|||
|
|
zyjjz = "";
|
|||
|
|
zyb = "";
|
|||
|
|
num1 = 1;
|
|||
|
|
num2 = 1;
|
|||
|
|
if (hh != "")
|
|||
|
|
{
|
|||
|
|
//mbjb = hh + str2 + "健康检查目标疾病" + "\r\n";
|
|||
|
|
mbjb ="（"+ NumberToChinese(num)+ "）"+ hh + "作业：\r\n"+ str2 + "\r\n";
|
|||
|
|
string sql = "select * from contraindicat_factor_maintain where jobs_state_maintain_id=" + str1 + " and " +
|
|||
|
|
"harmful_factors_type_maintain_id in (select id from harmful_factors_type_maintain where code in (" +
|
|||
|
|
"select harmful_factor_type from harmful_factors_maintain where harmful_factor_name in ('" + hh + "')))";
|
|||
|
|
var a = conn.Query(sql).ToList();
|
|||
|
|
foreach (var aa in a)
|
|||
|
|
{
|
|||
|
|
zyjjz += num1.ToString() + aa.contraindicat_name+";";
|
|||
|
|
num1++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
string sql1 = "select * from target_factor_maintain where jobs_state_maintain_id=" + str1 + " and " +
|
|||
|
|
"harmful_factors_type_maintain_id in (select id from harmful_factors_type_maintain where code in (" +
|
|||
|
|
"select harmful_factor_type from harmful_factors_maintain where harmful_factor_name in ('" + hh + "')))";
|
|||
|
|
var b = conn.Query(sql1).ToList();
|
|||
|
|
foreach (var bb in b)
|
|||
|
|
{
|
|||
|
|
zyb += num2.ToString() + bb.disease_name+";";
|
|||
|
|
num2++;
|
|||
|
|
}
|
|||
|
|
//strAll += mbjb + "职业禁忌证:" + zyjjz + "\r\n" + "职业病:" + zyb + "\r\n";
|
|||
|
|
strAll += mbjb + " 1 职业病：" + zyb + "\r\n"+ " 2 职业禁忌证：" + zyjjz + "\r\n" ;
|
|||
|
|
}
|
|||
|
|
num++;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//return zymd + strAll;
|
|||
|
|
return strAll;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//获取单位信息
|
|||
|
|
public List<EnterpriceInfoMaintainModel> getDw(string dwmc)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from enterprise_info_maintain where enterprise_name like '%" + dwmc + "%' and attribution='职业'";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<EnterpriceInfoMaintainModel>(sql).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<DTO_target_factor_maintainModel> GetTargetFactor(string personids)
|
|||
|
|
{
|
|||
|
|
string sql = "select disease_name,harmful_factor_name,jobs_state from target_factor_maintain a,harmful_factors_maintain b ,jobs_state_maintain c" +
|
|||
|
|
" where b.harmful_factor_type in (select distinct SUBSTRING(hazard_type_ids,1,CHARINDEX(',', hazard_type_ids) - 1) from professionalExam_register where id in("+ personids + "))"+
|
|||
|
|
" and c.jobs_state in (select distinct status from professionalExam_register where id in("+ personids + "))"+
|
|||
|
|
" and a.harmful_factors_type_maintain_id = b.id and a.jobs_state_maintain_id = c.id ";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<DTO_target_factor_maintainModel>(sql).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 通过体检人员的id，获取其体检小组并去重返回
|
|||
|
|
/// 2023-11-1 xulu
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="ids"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ExamGroupMaintainModel> getExamGroupByIds(string ids)
|
|||
|
|
{
|
|||
|
|
string sql = "select distinct check_item_ids from professionalExam_register where id in(" + ids + ")";
|
|||
|
|
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
List<ProfessionalExamRegisterModel> list= conn.Query<ProfessionalExamRegisterModel>(sql).ToList();
|
|||
|
|
string strItems = "";
|
|||
|
|
string str1 = "";
|
|||
|
|
foreach(ProfessionalExamRegisterModel m in list)
|
|||
|
|
{
|
|||
|
|
strItems += m.check_item_ids ;
|
|||
|
|
}
|
|||
|
|
var sss = strItems.Split(',').Distinct().ToArray();
|
|||
|
|
foreach (var o in sss)
|
|||
|
|
{
|
|||
|
|
str1 += o + ',';
|
|||
|
|
}
|
|||
|
|
str1= str1.Substring(0, str1.Length - 2);
|
|||
|
|
string sql1 = "select distinct a.team_name from exam_group_maintain a,exam_project_maintain b where a.id = b.exam_group_maintain_id\r\n and b.id in("+ str1 + ")";
|
|||
|
|
return conn.Query<ExamGroupMaintainModel>(sql1).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<DTO_contraindicat_factor_maintainModel> GetContraindicatFactor(string personids)
|
|||
|
|
{
|
|||
|
|
string sql = "select contraindicat_name,harmful_factor_name,jobs_state from contraindicat_factor_maintain a,harmful_factors_maintain b ,jobs_state_maintain c" +
|
|||
|
|
" where b.harmful_factor_type in (select distinct SUBSTRING(hazard_type_ids,1,CHARINDEX(',', hazard_type_ids) - 1) from professionalExam_register where id in(" + personids + "))" +
|
|||
|
|
" and c.jobs_state in (select distinct status from professionalExam_register where id in(" + personids + "))" +
|
|||
|
|
" and a.harmful_factors_type_maintain_id = b.id and a.jobs_state_maintain_id = c.id ";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<DTO_contraindicat_factor_maintainModel>(sql).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 分页查询登记信息
|
|||
|
|
/// 2023-12-11 xulu
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="page"></param>
|
|||
|
|
/// <param name="pagesize"></param>
|
|||
|
|
/// <param name="personids"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ProfessionalExamRegisterModel> GetRegesite(int page, int pagesize, string personids)
|
|||
|
|
{
|
|||
|
|
string sql = @"select *,row_number() over(order by id desc) as rownum from professionalExam_register where id in @personids";
|
|||
|
|
string[] ids = personids.Split(',');
|
|||
|
|
List<int> ids2 = new List<int>();
|
|||
|
|
foreach (string id in ids)
|
|||
|
|
{
|
|||
|
|
ids2.Add(Convert.ToInt32(id));
|
|||
|
|
}
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
//获取登记记录
|
|||
|
|
sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
|
|||
|
|
var result = conn.Query<ProfessionalExamRegisterModel>(sql, new { personids = ids2.ToArray() }).ToList();
|
|||
|
|
//获取 对应的复检 登记记录
|
|||
|
|
var result2 = getfjlist(result).ToList();
|
|||
|
|
//合并
|
|||
|
|
result = result.Concat(result2).ToList();
|
|||
|
|
var list1 = new List<ProfessionalExamRegisterModel>();
|
|||
|
|
//去重
|
|||
|
|
foreach (var v in result)
|
|||
|
|
if (!list1.Where(t => t.id == v.id).Any())
|
|||
|
|
{
|
|||
|
|
list1.Add(v);
|
|||
|
|
}
|
|||
|
|
return list1;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|