318 lines
12 KiB
C#
318 lines
12 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
using dccdc.Models;
|
||
using Dapper;
|
||
|
||
namespace dccdc.DAL
|
||
{
|
||
public class ExamGroupDoctorMatintainDal
|
||
{
|
||
public List<Models.ERPUser> getAllUser()
|
||
{
|
||
using (var conn = CommHelper.GetSqlConnection("OADB"))
|
||
{
|
||
string sql = "select * from erpuser";
|
||
return conn.Query<ERPUser>(sql).ToList();
|
||
}
|
||
}
|
||
|
||
public List<Models.DTO.Result_SQXM> getSQXM(Models.ERPUser erpUser, string pid)
|
||
{
|
||
|
||
string sql2 = " and a.exam_group_maintain_id in (select distinct exam_group_maintain_id " +
|
||
"from professionalExam_project_result where person_id in (select id from " +
|
||
"professionalExam_register where physical_num = '" + pid + "'))";
|
||
|
||
string sql = "select distinct a.exam_group,a.exam_group_maintain_id from exam_group_doctor_matintain as a left join exam_group_maintain as b " +
|
||
"on a.exam_group_maintain_id = b.id where a.entry like '是' and b.status = '是'" +
|
||
"and b.is_collect like '否' and a.status like '是' and a.user_name like '" + erpUser.UserName + "'";
|
||
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
if (!string.IsNullOrEmpty(pid))
|
||
{
|
||
return conn.Query<Models.DTO.Result_SQXM>(sql + sql2).ToList();
|
||
}
|
||
return conn.Query<Models.DTO.Result_SQXM>(sql).ToList();
|
||
}
|
||
}
|
||
|
||
public List<Models.DTO.LiShi> getLishi(string sfzh)
|
||
{
|
||
string sql = "select a.register_date,b.physical_num,b.project_name,b.reference_value,b.project_result,b.qualified from professionalExam_project_result b," +
|
||
"professionalExam_register a where a.id=b.person_id and a.card_number like '" + sfzh + "'";
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
return conn.Query<Models.DTO.LiShi>(sql).ToList();
|
||
}
|
||
}
|
||
|
||
public List<Models.ExaminationProcessModel> getTjxm(string id)
|
||
{
|
||
string sql = "select * from examination_process where person_id=" + id;
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
return conn.Query<Models.ExaminationProcessModel>(sql).ToList();
|
||
}
|
||
}
|
||
|
||
public List<Models.DTO.LiShi> getDanXiang(string sfzh, string xmbm, string pid)
|
||
{
|
||
//单项历史
|
||
string sql = "select a.physical_num,b.project_id,b.project_name,b.reference_value,b.project_result, " +
|
||
"b.qualified,a.register_date from " +
|
||
"professionalExam_register as a " +
|
||
"left join professionalExam_project_result as b " +
|
||
"on a.id = b.person_id " +
|
||
"where a.card_number like '" + sfzh + "' " +
|
||
"and b.project_id = '" + xmbm + "' and a.id != " + pid + " order by a.physical_num";
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
return conn.Query<Models.DTO.LiShi>(sql).ToList();
|
||
}
|
||
}
|
||
|
||
public List<Models.DTO.ZiLiao> getZyjjz(string zgzt, string yhyslbIDs)
|
||
{
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
//zyk 20230610没搞明白 职业禁忌症接害因素关系表中的字段harmful_factors_type_maintain_id，是接害因素id还是接害因素类型ID？？？？
|
||
string sql = "select a.harmful_factor_name as yhys_Name,b.contraindicat_name as zyjjz_Name " +
|
||
"from harmful_factors_maintain a,contraindicat_factor_maintain b " +
|
||
"where a.id = b.harmful_factors_type_maintain_id and b.jobs_state_maintain_id = '" + zgzt + "' and " +
|
||
"b.harmful_factors_type_maintain_id in (" + yhyslbIDs + ")";
|
||
return conn.Query<Models.DTO.ZiLiao>(sql).ToList();
|
||
}
|
||
}
|
||
|
||
public List<Models.notice_factor_maintainModel> getTiXing(string zgzt, string yhyslbIDs)
|
||
{
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
string sql = "select exam_group,bussiness_notice from notice_factor_maintain " +
|
||
"where jobs_state_maintain_id = " + zgzt + " and harmful_factors_type_maintain_id in (" + yhyslbIDs + ")";
|
||
return conn.Query<Models.notice_factor_maintainModel>(sql).ToList();
|
||
}
|
||
}
|
||
|
||
public List<Models.DTO.ZiLiao> getYszyb(string zgzt, string yhyslbIDs)
|
||
{
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
//也是同样的问题，harmful_factors_type_maintain_id存储的实际是 接害因素id
|
||
string sql = "select a.harmful_factor_name as yhys_Name,b.disease_name as zyjjz_Name " +
|
||
"from harmful_factors_maintain a,target_factor_maintain b " +
|
||
"where a.id = b.harmful_factors_type_maintain_id and b.jobs_state_maintain_id = '" + zgzt + "' and " +
|
||
"b.harmful_factors_type_maintain_id in (" + yhyslbIDs + ")";
|
||
return conn.Query<Models.DTO.ZiLiao>(sql).ToList();
|
||
}
|
||
}
|
||
|
||
public List<Models.DTO.JglrChaLou> getChaLouWJ(string id, string djrq, string djrqEnd,int jgid)
|
||
{
|
||
string sql = "select a.physical_num,a.person_name,a.util_name,a.register_date,b.commit_doctor from " +
|
||
"professionalExam_register as a " +
|
||
"left join examination_process as b " +
|
||
"on a.id = b.person_id where " +
|
||
"b.is_complete is null and " +
|
||
"a.freezing_and_thawing is null " +
|
||
"and b.exam_group_maintain_id = '" + id + "' and a.register_date between '" + djrq + "' and '" + djrqEnd + "'";
|
||
if(jgid!=49)
|
||
{
|
||
sql += " and a.jgid=" + jgid;
|
||
}
|
||
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
return conn.Query<Models.DTO.JglrChaLou>(sql).ToList();
|
||
}
|
||
}
|
||
|
||
public List<Models.DTO.JglrChaLou> getChaLouYJ(string id, string tjrq, string djrqEnd,int jgid)
|
||
{
|
||
string sql = "select a.physical_num,a.person_name,a.util_name,a.register_date,b.commit_doctor from " +
|
||
"professionalExam_register as a " +
|
||
"left join examination_process as b " +
|
||
"on a.id = b.person_id where " +
|
||
"b.is_complete like '已完成' and " +
|
||
"freezing_and_thawing is null and " +
|
||
"b.exam_group_maintain_id = '" + id + "' and " +
|
||
"a.register_date between '" + tjrq + "' and '" + djrqEnd + "'";
|
||
if (jgid != 49)
|
||
{
|
||
sql += " and a.jgid=" + jgid;
|
||
}
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
return conn.Query<Models.DTO.JglrChaLou>(sql).ToList();
|
||
}
|
||
}
|
||
|
||
public List<ExamGroupDoctorMatintainModel> GetAllList(string id, string stauts)
|
||
{
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
string param = "";
|
||
if (id != "")
|
||
{
|
||
param = " and id =@id";
|
||
}
|
||
if (stauts != "")
|
||
{
|
||
param += " and status=@stauts";
|
||
}
|
||
return conn.Query<ExamGroupDoctorMatintainModel>("select * from exam_group_doctor_matintain where 1=1" + param, new { @id = id, @stauts = stauts }).ToList();
|
||
}
|
||
}
|
||
|
||
public int getCount(string key)
|
||
{
|
||
string sql = "select count(1) from dbo.exam_group_doctor_matintain where 1=1";
|
||
if (!string.IsNullOrEmpty(key))
|
||
{
|
||
sql += " and exam_group like @exam_group";
|
||
}
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
return conn.ExecuteScalar<int>(sql, new { exam_group = "%" + key + "%" });
|
||
}
|
||
}
|
||
|
||
public List<ExamGroupDoctorMatintainModel> getList(int page, int pagesize, string key)
|
||
{
|
||
//throw new NotImplementedException();
|
||
string sql = "select *,row_number() over(order by id) as rownum from exam_group_doctor_matintain where 1=1";
|
||
if (!string.IsNullOrEmpty(key))
|
||
{
|
||
sql += " and exam_group_maintain_id = @exam_group_maintain_id";
|
||
}
|
||
sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
return conn.Query<Models.ExamGroupDoctorMatintainModel>(sql, new { exam_group_maintain_id = key }).ToList();
|
||
}
|
||
}
|
||
|
||
public object save(ExamGroupDoctorMatintainModel cpm, ERPUser user)
|
||
{
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
//throw new NotImplementedException();
|
||
cpm.creator = user.TrueName;
|
||
cpm.create_time = DateTime.Now.ToString("yyyy-MM-dd");
|
||
string sql = "";
|
||
sql = "select count(1) from exam_group_doctor_matintain where exam_group_maintain_id=@exam_group_maintain_id and user_name=@user_name and id!=@id";
|
||
var hc = conn.ExecuteScalar<int>(sql, cpm);
|
||
if(hc>0)
|
||
{
|
||
return new { State = 0, Message ="不能添加重复的项目！" };
|
||
}
|
||
|
||
if (cpm.id == 0)
|
||
{
|
||
sql = @"INSERT INTO [dbo].[exam_group_doctor_matintain]
|
||
(
|
||
[exam_group],
|
||
[exam_group_maintain_id],
|
||
[check_doctor],
|
||
[user_name],
|
||
[collect],
|
||
[entry],
|
||
[review],
|
||
[status],
|
||
[creator],
|
||
[create_time]
|
||
)
|
||
VALUES
|
||
(
|
||
@exam_group,
|
||
@exam_group_maintain_id,
|
||
@check_doctor,
|
||
@user_name,
|
||
@collect,
|
||
@entry,
|
||
@review,
|
||
@status,
|
||
@creator,
|
||
@create_time
|
||
)
|
||
";
|
||
}
|
||
else
|
||
{
|
||
sql = @"UPDATE [dbo].[exam_group_doctor_matintain]
|
||
SET
|
||
[exam_group]=@exam_group,
|
||
[exam_group_maintain_id]=@exam_group_maintain_id,
|
||
[check_doctor]=@check_doctor,
|
||
[user_name]=@user_name,
|
||
[collect]=@collect,
|
||
[entry]=@entry,
|
||
[review]=@review,
|
||
[status]=@status,
|
||
[creator]=@creator,
|
||
[create_time]=@create_time
|
||
|
||
WHERE id=@id
|
||
";
|
||
}
|
||
|
||
try
|
||
{
|
||
int c = conn.Execute(sql, cpm);
|
||
if (c > 0)
|
||
{
|
||
return new { State = 1, Message = "保存成功！" };
|
||
}
|
||
else
|
||
{
|
||
return new { State = 0, Message = "操作失败，请联系管理员！" };
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
return new { State = 0, Message = ex.Message };
|
||
}
|
||
}
|
||
}
|
||
|
||
public List<string> getDoctor()
|
||
{
|
||
string sql = "select distinct check_doctor from exam_group_doctor_matintain";
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
return conn.Query<string>(sql).ToList();
|
||
}
|
||
}
|
||
|
||
public object del(string id)
|
||
{
|
||
string sql = @"delete exam_group_doctor_matintain where id=@id";
|
||
|
||
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
||
{
|
||
try
|
||
{
|
||
int c = conn.Execute(sql, new { id = id });
|
||
if (c > 0)
|
||
{
|
||
return new { State = 1, Message = "保存成功！" };
|
||
}
|
||
else
|
||
{
|
||
return new { State = 0, Message = "操作失败，请联系管理员！" };
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
return new { State = 0, Message = ex.Message };
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|