using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using dccdc.Models; using Dapper; using dccdc.Models.DTO; using System.Data; namespace dccdc.DAL { public class professionalExam_project_resultDal { public List getResultCaiJi(int person_id) { using (var conn = CommHelper.GetSqlConnection()) { string sql = @"select result.project_name,project_result ,g.team_name as exam_group from professionalExam_project_result result join exam_group_maintain g on g.id=result.exam_group_maintain_id where result.person_id=@person_id"; return conn.Query(sql, new { person_id = person_id }).ToList(); } } public List getPersonResult(string person_id, string qualified) { 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=@person_id"; using (var conn = CommHelper.GetSqlConnection()) { if (!string.IsNullOrEmpty(qualified)) sql += " and qualified=@qualified"; sql += " order by qualified"; return conn.Query(sql, new { person_id = person_id, qualified = qualified }).ToList(); } } public bool Add(ProfessionalExamProjectResultModel model) { using (var conn = CommHelper.GetSqlConnection()) { string sql = @"INSERT INTO [dbo].[professionalExam_project_result] ([person_id] ,[project_id] ,[project_name] ,[project_type] ,[project_result] ,[deficiency] ,[qualified] ,[remark] ,[tested] ,[blood_number] ,[inspection_time] ,[rummager] ,[presenter] ,[commit_date] ,[complete] ,[reference_value] ,[exam_group_maintain_id] ,[model] ,[judgment_model_maintain_id] ,[review_save_time] ,[review_depositary] ,[review_commitor] ,[review_commit_time] ,[project_other] ,[electronic_signature] ,[project_alias] ,[check_man] ,[check_time] ,[physical_num] ,[project_unit] ,[project_default] ,[D] ,[M] ,[F] ,[x_d] ,[x_m] ,[x_f] ,[order_id]) VALUES (@person_id ,@project_id ,@project_name ,@project_type ,@project_result ,@deficiency ,@qualified ,@remark ,@tested ,@blood_number ,@inspection_time ,@rummager ,@presenter ,@commit_date ,@complete ,@reference_value ,@exam_group_maintain_id ,@model ,@judgment_model_maintain_id ,@review_save_time ,@review_depositary ,@review_commitor ,@review_commit_time ,@project_other ,@electronic_signature ,@project_alias ,@check_man ,@check_time ,@physical_num ,@project_unit ,@project_default ,@D ,@M ,@F ,@x_d ,@x_m ,@x_f ,@order_id)"; return (conn.Execute(sql, model) != 0 ? true : false); } } public string Jjxp(string tmh, string v) { using (var conn = CommHelper.GetSqlConnection()) { string message = ""; IDbTransaction transaction = conn.BeginTransaction(); try { string sql = "select count(1) from professionalExam_register where physical_num=@physical_num"; var c = conn.ExecuteScalar(sql, new { physical_num = tmh }, transaction, null, null); if (c != 0) { sql = "select * from professionalExam_register where physical_num=@physical_num"; var tjxms = new DAL.ExamProjectMaintainDal().GetAllListByprojectid(v); if (tjxms.Count == 0) { transaction.Rollback(); return "X光透视项不存在，请联系管理员设置！"; } var tjxm = tjxms.First(); var cb = conn.Query(sql, new { physical_num = tmh }, transaction).First(); //foreach (ProfessionalExamRegisterModel cb in personC) //{ if(cb.category == null) { transaction.Rollback(); return "添加失败：只有类型为【食品从业】【公共场所】可以添加！"; } if (cb.category.Equals("食品从业") || cb.category.Equals("公共场所")) { //判断当前体检项目里是否已经存在X光 string project_result = "select * from professionalExam_project_result where physical_num=@physical_num and project_id='" + v + "'"; List perm = conn.Query(project_result, new { physical_num = tmh }, transaction).ToList(); if (perm.Count > 0) { transaction.Rollback(); return "X光透视已经存在不能重复添加！"; } //填充体检小组表 string process = "select * from examination_process where physical_num=@physical_num and exam_group_maintain_id=" + tjxm.exam_group_maintain_id; List epm = conn.Query(process, new { physical_num = tmh }, transaction).ToList(); if (epm.Count == 0) { string processsql = @"INSERT INTO [dbo].[examination_process]([person_id] ,[exam_group_maintain_id] ,[group_name] ,[is_entry] ,[entry_date] ,[is_review],[review_date] ,[is_complete] ,[complete_date] ,[collect_doctor] ,[commit_doctor],[review_need],[physical_num] ,[is_treatment],[treatment_date],[treatment_person])VALUES(@person_id ,@exam_group_maintain_id,@group_name,@is_entry,@entry_date,@is_review,@review_date ,@is_complete ,@complete_date ,@collect_doctor,@commit_doctor,@review_need,@physical_num,@is_treatment,@treatment_date,@treatment_person)"; ExaminationProcessModel xzModel = new ExaminationProcessModel { exam_group_maintain_id = tjxm.exam_group_maintain_id, group_name = tjxm.exam_group, physical_num = tmh, person_id = Convert.ToInt32(cb.id) }; conn.Execute(processsql, xzModel, transaction, null, null); } //填充体检结果表 string resultsql = @"INSERT INTO [dbo].[professionalExam_project_result]([person_id] ,[project_id] ,[project_name] ,[project_type] ,[project_result] ,[deficiency],[qualified],[remark] ,[tested],[blood_number] ,[inspection_time] ,[rummager] ,[presenter],[commit_date] ,[complete] ,[reference_value] ,[exam_group_maintain_id],[model] ,[judgment_model_maintain_id] ,[review_save_time] ,[review_depositary],[review_commitor] ,[review_commit_time] ,[project_other],[electronic_signature] ,[project_alias] ,[check_man],[check_time] ,[physical_num],[project_unit],[project_default] ,[D],[M] ,[F] ,[x_d],[x_m],[x_f],[order_id])VALUES (@person_id , @project_id , @project_name , @project_type , @project_result , @deficiency , @qualified , @remark , @tested , @blood_number , @inspection_time , @rummager , @presenter , @commit_date , @complete , @reference_value , @exam_group_maintain_id , @model, @judgment_model_maintain_id , @review_save_time, @review_depositary, @review_commitor, @review_commit_time, @project_other , @electronic_signature, @project_alias, @check_man, @check_time, @physical_num, @project_unit , @project_default , @D , @M , @F , @x_d , @x_m, @x_f, @order_id)"; ProfessionalExamProjectResultModel resultModel = new ProfessionalExamProjectResultModel { project_id = tjxm.project_id, project_name = tjxm.project_name, project_result = tjxm.default_value, reference_value = "", exam_group_maintain_id = tjxm.exam_group_maintain_id, model = tjxm.model, judgment_model_maintain_id = tjxm.judgment_mode_maintain_id, project_alias = tjxm.project_alias, physical_num = tmh, project_default = tjxm.default_value, order_id = tjxm.order_id, deficiency = "不缺项", qualified = "合格", person_id = Convert.ToInt32(cb.id) }; conn.Execute(resultsql, resultModel, transaction, null, null); transaction.Commit(); message = "添加成功！"; } else { transaction.Rollback(); message = "添加失败：只有类型为【食品从业】【公共场所】可以添加！"; } //} } else { transaction.Rollback(); message = "请选择一条正确客户信息!"; } } catch (Exception ex) { transaction.Rollback(); message = "添加失败!"; } return message; } } static object lo = new object(); public object baocunTjjg(List peprm, ERPUser user, List personC, string is_complete, string pid, string egmId) { lock (lo) { using (var conn = CommHelper.GetSqlConnection()) { try { string sql = ""; if (personC == null && pid != "") { sql = "select * from professionalExam_register where id=" + pid; personC = conn.Query(sql).ToList(); } string complete_date = ""; string presenter = ""; //string commit_doctor = ""; if (!string.IsNullOrEmpty(is_complete)) { complete_date = DateTime.Now.ToString("yyyy-MM-dd"); presenter = user.TrueName; } //支持批量提交 20231117zyk foreach (ProfessionalExamRegisterModel pferm in personC) { //2023-11-17 xulu 修改体检结果、过程表更新顺序，并且体检结果存在空值时返回提交失败 sql = "select * from professionalExam_project_result where person_id=@person_id and exam_group_maintain_id=@exam_group_maintain_id"; List peprmlist = conn.Query(sql, new { person_id = pferm.id, exam_group_maintain_id = peprm[0].exam_group_maintain_id }).ToList(); //如果前端传递的person_id与当前的person_id不一致，则更新查询出的体检结果，peprm里只包含一个人的体检结果 if (peprm[0].person_id.ToString() != pferm.id) { foreach (ProfessionalExamProjectResultModel model in peprmlist) { if(string.IsNullOrEmpty(model.project_result)) { return new { State = 0, Message = "体检结果有空值，提交失败！" }; } model.inspection_time = DateTime.Now.ToString("yyyy-MM-dd"); model.rummager = user.TrueName; model.presenter = presenter; model.commit_date = complete_date; sql = @"UPDATE [professionalExam_project_result] SET [project_result] = @project_result,[deficiency] = " + "@deficiency,[qualified] = @qualified,[remark] = @remark,[inspection_time] = @inspection_time,[rummager] = " + "@rummager,[presenter] =@presenter,[commit_date] = @commit_date WHERE id=@id"; conn.Execute(sql, model); } } else //如果前端传递的person_id与当前的person_id一致，则更新前端传来的体检结果 { foreach (ProfessionalExamProjectResultModel model in peprm) { model.inspection_time = DateTime.Now.ToString("yyyy-MM-dd"); model.rummager = user.TrueName; model.presenter = presenter; model.commit_date = complete_date; sql = @"UPDATE [professionalExam_project_result] SET [project_result] = @project_result,[deficiency] = " + "@deficiency,[qualified] = @qualified,[remark] = @remark,[inspection_time] = @inspection_time,[rummager] = " + "@rummager,[presenter] =@presenter,[commit_date] = @commit_date WHERE id=@id"; conn.Execute(sql, model); } } sql = "update examination_process set is_entry='已录',entry_date=@entry_date,is_complete=@is_complete,complete_date=@complete_date,commit_doctor=@commit_doctor " + "where physical_num = @num and exam_group_maintain_id=@exam_group_maintain_id"; int c = conn.Execute(sql, new { entry_date = DateTime.Now.ToString("yyyy-MM-dd"), is_complete = is_complete, complete_date = complete_date, num = pferm.physical_num, exam_group_maintain_id = peprm[0].exam_group_maintain_id, commit_doctor = presenter }); sql = "select is_complete from examination_process where person_id=" + pferm.id + " group by is_complete"; List epList = conn.Query(sql).ToList(); if (epList.Count == 1 && epList[0].is_complete == "已完成") { sql = "update professionalExam_register set procedure_status='已录入检查结果' " + " where (procedure_status!='已打印健康证' and procedure_status!='主检医生已审核') and id=" + pferm.id; conn.Execute(sql); } if (c == 0) { return new { State = 0, Message = "操作失败，请联系管理员！" }; } } return new { State = 1, Message = "保存成功！" }; } catch (Exception ex) { return new { State = 0, Message = "操作失败，请联系管理员！" + ex.Message }; } } } } public int updatePft(ProfessionalExamProjectResultModel mo) { using (var conn = CommHelper.GetSqlConnection()) { string sql = "update professionalExam_project_result set project_result = @project_result where id=@id"; return conn.Execute(sql, mo); } } public List getTingyu(string id) { string sql = "select shuanger, MAX(a500HZ) as wubai, MAX(a1000HZ) as yiqian," + "MAX(a2000HZ) as liangqian, MAX(a3000HZ) as sanqian, MAX(a4000HZ) as siqian, MAX(a6000HZ) as liuqian " + " from(select case when project_alias like '%左耳%' then '左耳'" + " when project_alias like '%右耳%' then '右耳' end as shuanger" + ",case when project_alias like '%500Hz%' then project_result" + " when project_alias like '%500Hz%' then project_result end as a500HZ ,case when project_alias like '%1000Hz%' then project_result" + " when project_alias like '%1000Hz%' then project_result end as a1000HZ," + " case when project_alias like '%2000Hz%' then project_result" + " when project_alias like '%2000Hz%' then project_result end as a2000HZ," + " case when project_alias like '%3000Hz%' then project_result" + " when project_alias like '%3000Hz%' then project_result end as a3000HZ," + " case when project_alias like '%4000Hz%' then project_result" + " when project_alias like '%4000Hz%' then project_result end as a4000HZ," + " case when project_alias like '%6000Hz%' then project_result" + " when project_alias like '%6000Hz%' then project_result end as a6000HZ" + " from professionalExam_project_result where person_id = '" + id + "' ) A group by shuanger"; using (var conn = CommHelper.GetSqlConnection()) { return conn.Query(sql).ToList(); } } public object chexiaoTjjg(List peprm, List personC, string pid, string egmId) { using (var conn = CommHelper.GetSqlConnection()) { try { string sql = ""; if (personC == null && pid != "") { sql = "select * from professionalExam_register where id=" + pid; personC = conn.Query(sql).ToList(); } foreach (ProfessionalExamRegisterModel pferm in personC) { sql = "update examination_process set is_entry='',entry_date='',is_complete='',complete_date='' " + "where physical_num = @num and exam_group_maintain_id=@exam_group_maintain_id"; int c = conn.Execute(sql, new { num = pferm.physical_num, exam_group_maintain_id = peprm[0].exam_group_maintain_id }); sql = "select * from professionalExam_project_result where person_id=@person_id and exam_group_maintain_id=@exam_group_maintain_id"; List peprmlist = conn.Query(sql, new { person_id = pferm.id, exam_group_maintain_id = peprm[0].exam_group_maintain_id }).ToList(); //sql = "update professionalExam_register set procedure_status='已采集' where id=" + pferm.id; //2023-11-27 xulu 修改状态为"打印指引单" sql = "update professionalExam_register set procedure_status='打印指引单' where id=" + pferm.id; conn.Execute(sql); if (c > 0) { //zyk 20230610加注释:如果根据人员信息和分组信息获得的 登记记录的id，与参数中peprm的person_id不一致， //以“根据人员信息和分组信息获得的 登记记录”为准 if (peprm[0].person_id.ToString() != pferm.id) { foreach (ProfessionalExamProjectResultModel model in peprmlist) { sql = @"UPDATE [professionalExam_project_result] SET [project_result] = @project_result,[deficiency] = " + "@deficiency,[qualified] = @qualified,[remark] = @remark,[inspection_time] = @inspection_time,[rummager] = " + "@rummager,[presenter] ='',[commit_date] = '' WHERE id=@id"; conn.Execute(sql, model); } } else { foreach (ProfessionalExamProjectResultModel model in peprm) { sql = @"UPDATE [professionalExam_project_result] SET [project_result] = @project_result,[deficiency] = " + "@deficiency,[qualified] = @qualified,[remark] = @remark,[inspection_time] = @inspection_time,[rummager] = " + "@rummager,[presenter] ='',[commit_date] = '' WHERE id=@id"; conn.Execute(sql, model); } } } else { return new { State = 0, Message = "操作失败，请联系管理员！" }; } } return new { State = 1, Message = "撤销成功！" }; } catch (Exception ex) { return new { State = 0, Message = "操作失败，请联系管理员！" }; } } } public object quexiangTjjg(List peprm, List personC, string pid, string egmId) { using (var conn = CommHelper.GetSqlConnection()) { try { string sql = ""; if (personC == null && pid != "") { sql = "select * from professionalExam_register where id=" + pid; personC = conn.Query(sql).ToList(); } foreach (ProfessionalExamRegisterModel pferm in personC) { sql = "select * from professionalExam_project_result where person_id=@person_id and exam_group_maintain_id=@exam_group_maintain_id"; List peprmlist = conn.Query(sql, new { person_id = pferm.id, exam_group_maintain_id = peprm[0].exam_group_maintain_id }).ToList(); if (peprm[0].person_id.ToString() != pferm.id) { foreach (ProfessionalExamProjectResultModel model in peprmlist) { sql = @"UPDATE [professionalExam_project_result] SET [project_result] = '未检',[deficiency] = " + "'缺项',[qualified] = '不合格',[remark] = @remark where id=@id"; conn.Execute(sql, model); } } else { foreach (ProfessionalExamProjectResultModel model in peprm) { sql = @"UPDATE [professionalExam_project_result] SET [project_result] = '未检',[deficiency] = " + "'缺项',[qualified] = '不合格',[remark] = @remark where id=@id"; conn.Execute(sql, model); } } } return new { State = 1, Message = "操作成功！" }; } catch (Exception ex) { return new { State = 0, Message = "操作失败，请联系管理员！" }; } } } public object mrzTjjg(List peprm, List personC, string pid, string egmId) { using (var conn = CommHelper.GetSqlConnection()) { try { string sql = ""; if (personC == null && pid != "") { sql = "select * from professionalExam_register where id=" + pid; personC = conn.Query(sql).ToList(); } foreach (ProfessionalExamRegisterModel pferm in personC) { sql = "select * from professionalExam_project_result where person_id=@person_id and exam_group_maintain_id=@exam_group_maintain_id"; List peprmlist = conn.Query(sql, new { person_id = pferm.id, exam_group_maintain_id = peprm[0].exam_group_maintain_id }).ToList(); if (peprm[0].person_id.ToString() != pferm.id) { foreach (ProfessionalExamProjectResultModel model in peprmlist) { sql = @"UPDATE [professionalExam_project_result] SET [project_result] = '" + model.project_default + "',[deficiency] = " + "'不缺项',[qualified] = '合格',[remark] = @remark where id=@id"; conn.Execute(sql, model); } } else { foreach (ProfessionalExamProjectResultModel model in peprm) { sql = @"UPDATE [professionalExam_project_result] SET [project_result] = '" + model.project_default + "',[deficiency] = " + "'不缺项',[qualified] = '合格',[remark] = @remark where id=@id"; conn.Execute(sql, model); } } } return new { State = 1, Message = "操作成功！" }; } catch (Exception ex) { return new { State = 0, Message = "操作失败，请联系管理员！" }; } } } public object huoquTjjg2(List peprm, List personC, string pid, string egmId) { try { var connLisdb = CommHelper.GetSqlConnection("LISDB"); var conn = CommHelper.GetSqlConnection(); string sql = ""; string qualified = ""; if (peprm == null && personC == null && pid != "") { sql = "select * from professionalExam_register where id=" + pid; personC = conn.Query(sql).ToList(); sql = "select * from professionalExam_project_result where person_id=" + pid + " and exam_group_maintain_id=" + egmId; peprm = conn.Query(sql).ToList(); } foreach (ProfessionalExamRegisterModel pferm in personC) { sql = "select * from professionalExam_project_result where person_id=@person_id and exam_group_maintain_id=@exam_group_maintain_id"; List peprmlist = conn.Query(sql, new { person_id = pferm.id, exam_group_maintain_id = peprm[0].exam_group_maintain_id }).ToList(); if (peprm[0].person_id.ToString() != pferm.id) { foreach (ProfessionalExamProjectResultModel model in peprmlist) { sql = "select 检验结果 as testresult, 报告时间 as resulttime, 检验医生姓名 as testman , " + "审核医生姓名 as checkman, 审核时间 as lastmodify,结果标志 as resultMark from 接口视图_检验报告结果 " + "where 条码号 like '%" + pferm.physical_num + "%' and 报告项目外部编码1 like '%" + model.project_id + "%'"; List lisResultList = connLisdb.Query(sql).ToList(); if (lisResultList.Count != 0) { foreach (Models.DTO.LISDB lis in lisResultList) { if (!string.IsNullOrEmpty(lis.resultMark) && lis.resultMark == "M") { qualified = "合格"; } else { qualified = "不合格"; } sql = @"UPDATE [professionalExam_project_result] SET [project_result] = '" + lis.testresult + "',[deficiency] = " + "'不缺项',[qualified] = '" + qualified + "',[remark] = @remark where id=@id"; conn.Execute(sql, model); } } } } else { foreach (ProfessionalExamProjectResultModel model in peprmlist) { sql = "select 检验结果 as testresult, 报告时间 as resulttime, 检验医生姓名 as testman , " + "审核医生姓名 as checkman, 审核时间 as lastmodify,结果标志 as resultMark from 接口视图_检验报告结果 " + "where 条码号 like '%" + pferm.physical_num + "%' and 报告项目外部编码1 like '%" + model.project_id + "%'"; List lisResultList = connLisdb.Query(sql).ToList(); if (lisResultList.Count != 0) { foreach (Models.DTO.LISDB lis in lisResultList) { if (lis.resultMark == "M") { qualified = "合格"; } else { qualified = "不合格"; } sql = @"UPDATE [professionalExam_project_result] SET [project_result] = '" + lis.testresult + "',[deficiency] = " + "'不缺项',[qualified] = '" + qualified + "',[remark] = @remark where id=@id"; conn.Execute(sql, model); } } } } } return new { State = 1, Message = "操作成功！" }; } catch (Exception ex) { return new { State = 0, Message = "操作失败，请联系管理员！" }; } } public object huoquTjjg(List peprm, List personC, string pid, string egmId) { try { //var connLisdb = CommHelper.GetSqlConnection("LISDB"); var conn = CommHelper.GetSqlConnection(); string sql = ""; string qualified = ""; if (peprm == null && personC == null && pid != "") { sql = "select * from professionalExam_register where id=" + pid; personC = conn.Query(sql).ToList(); sql = "select * from professionalExam_project_result where person_id=" + pid + " and exam_group_maintain_id=" + egmId; peprm = conn.Query(sql).ToList(); } foreach (ProfessionalExamRegisterModel pferm in personC) { sql = "select * from professionalExam_project_result where person_id=@person_id and exam_group_maintain_id=@exam_group_maintain_id"; List peprmlist = conn.Query(sql, new { person_id = pferm.id, exam_group_maintain_id = peprm[0].exam_group_maintain_id }).ToList(); if (peprm[0].person_id.ToString() != pferm.id) { foreach (ProfessionalExamProjectResultModel model in peprmlist) { //sql = "select 检验结果 as testresult, 报告时间 as resulttime, 检验医生姓名 as testman , " + // "审核医生姓名 as checkman, 审核时间 as lastmodify,结果标志 as resultMark from 接口视图_检验报告结果 " + // "where 条码号 like '%" + pferm.physical_num + "%' and 报告项目外部编码1 like '%" + model.project_id + "%'"; sql = "select * from exam_project_maintain where project_id='" + model.project_id + "'"; ExamProjectMaintainModel emm = conn.Query(sql).FirstOrDefault(); sql = @"select c.results as testresult,c.reportdatetime as resulttime , c.reportdoctor as testman,c.reportdoctor checkman,c.reportdatetime as lastmodify, c.resultflag resultMark from lis_reqmain a join lis_reqitems b on a.testno=b.testno join lis_reqresults c on c.testno=b.testno join exam_project_maintain d on d.lis=c.reportitemno join bar_code_project_maintain e on e.exam_project_maintain_id=d.project_id and e.bar_code_maintain_id=b.itemno and b.itemno=e.bar_code_maintain_id where a.patno='" + pferm.physical_num + "' and d.lis='" + emm.lis + "' and e.project_code='" + emm.project_id + "' "; List lisResultList = conn.Query(sql).ToList(); if (lisResultList.Count != 0) { foreach (Models.DTO.LISDB lis in lisResultList) { model.tested = ""; //+- if (lis.resultMark != "H" && lis.resultMark != "L" && (!lis.testresult.Contains("+"))) { qualified = "合格"; } else { qualified = "不合格"; } if (model.model == "定量" && qualified == "不合格") { string[] aa = model.reference_value.Split('-'); if (Convert.ToDouble(lis.testresult) < Convert.ToDouble(aa[0])) { model.tested = "↓"; } else if (Convert.ToDouble(lis.testresult) > Convert.ToDouble(aa[1])) { model.tested = "↑"; } else { model.tested = ""; } } sql = @"UPDATE [professionalExam_project_result] SET [project_result] = '" + lis.testresult + "',[deficiency] = " + "'不缺项',[qualified] = '" + qualified + "',[remark] = @remark,[tested]=@tested where id=@id"; conn.Execute(sql, model); } } } } else { foreach (ProfessionalExamProjectResultModel model in peprmlist) { //sql = "select 检验结果 as testresult, 报告时间 as resulttime, 检验医生姓名 as testman , " + // "审核医生姓名 as checkman, 审核时间 as lastmodify,结果标志 as resultMark from 接口视图_检验报告结果 " + // "where 条码号 like '%" + pferm.physical_num + "%' and 报告项目外部编码1 like '%" + model.project_id + "%'"; sql = "select * from exam_project_maintain where project_id='" + model.project_id + "'"; ExamProjectMaintainModel emm = conn.Query(sql).FirstOrDefault(); sql = @"select c.results as testresult,c.reportdatetime as resulttime , c.reportdoctor as testman,c.reportdoctor checkman,c.reportdatetime as lastmodify, c.resultflag resultMark from lis_reqmain a join lis_reqitems b on a.testno=b.testno join lis_reqresults c on c.testno=b.testno join exam_project_maintain d on d.lis=c.reportitemno join bar_code_project_maintain e on e.exam_project_maintain_id=d.project_id and e.bar_code_maintain_id=b.itemno and b.itemno=e.bar_code_maintain_id where a.patno='" + pferm.physical_num + "' and d.lis='" + emm.lis + "' and e.project_code='" + emm.project_id + "' "; List lisResultList = conn.Query(sql).ToList(); if (lisResultList.Count != 0) { foreach (Models.DTO.LISDB lis in lisResultList) { model.tested = ""; if (lis.resultMark != "H" && lis.resultMark != "L" && (!lis.testresult.Contains("+"))) { qualified = "合格"; } else { qualified = "不合格"; } if (model.model == "定量" && qualified == "不合格") { string[] aa = model.reference_value.Split('-'); if (Convert.ToDouble(lis.testresult) < Convert.ToDouble(aa[0])) { model.tested = "↓"; } else if (Convert.ToDouble(lis.testresult) > Convert.ToDouble(aa[1])) { model.tested = "↑"; } else { model.tested = ""; } } sql = @"UPDATE [professionalExam_project_result] SET [project_result] = '" + lis.testresult + "',[deficiency] = " + "'不缺项',[qualified] = '" + qualified + "',[remark] = @remark,[tested]=@tested where id=@id"; conn.Execute(sql, model); } } } } } return new { State = 1, Message = "操作成功！" }; } catch (Exception ex) { return new { State = 0, Message = "操作失败，请联系管理员！" }; } } public List queryyj(Models.DTO.Criteria model) { string sql = "select * from professionalExam_project_result a join exam_group_maintain b on b.id = a.exam_group_maintain_id where a.person_id=@personid"; using (var conn = CommHelper.GetSqlConnection()) { var resultList = conn.Query(sql, new { @personid = model.person_name }); var xiaozugroup = resultList.GroupBy(t => new { t.team_name }); var reslist = new List(); foreach (var v in xiaozugroup) { string yj = ""; var itemlist = resultList.Where(t => t.team_name == v.Key.team_name).Where(m => m.qualified == "不合格"); ; foreach (var item in itemlist) { yj += item.project_result; } if (yj == "") { yj = "未见明显异常"; } reslist.Add(new { v.Key.team_name, yj }); } return reslist; } } public DataTable gettjjg(string ident) { //throw new NotImplementedException(); string sql = @"select b.project_result,b.project_id from professionalExam_register a join professionalExam_project_result b on a.id=b.person_id join examination_process c on c.person_id=a.id and c.exam_group_maintain_id=b.exam_group_maintain_id where a.medical_scheme_maintain_id=4 and a.card_number=@card_number "; using (var conn = CommHelper.GetSqlConnection()) { var dr = conn.ExecuteReader(sql, new { card_number = ident }); DataTable dt = new DataTable(); dt.Load(dr); return dt; } } public List getUnqualified(string physical_num) { string sql = "select project_name from professionalExam_project_result where qualified = '不合格' and physical_num = "+ physical_num; using (var conn = CommHelper.GetSqlConnection()) { return conn.Query(sql).ToList(); } } } }