331 lines
14 KiB
C#
331 lines
14 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;
|
|||
|
|
using dccdc.Models.DTO;
|
|||
|
|
|
|||
|
|
namespace dccdc.DAL
|
|||
|
|
{
|
|||
|
|
public class PersonalCheckDal
|
|||
|
|
{
|
|||
|
|
public List<PersonalCheckModel> GetAllList(string filtrate)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (filtrate != null && filtrate != "")
|
|||
|
|
{
|
|||
|
|
param += " and (name LIKE '%" + filtrate + "%' or unit LIKE '%" + filtrate + "%' or phone = '"+filtrate+"' or ident = '"+ filtrate + "' or u.nickname ='" + filtrate + "' )";
|
|||
|
|
}
|
|||
|
|
string sql = "SELECT info.id AS id,info.name,info.phone,info.ident,info.type,u.nickname AS nickname,info.pass_time AS passTime,info.unit FROM infection_open_user_info info LEFT JOIN infection_open_user u ON u.id = info.pass_by WHERE 1 = 1 ";
|
|||
|
|
return conn.Query<PersonalCheckModel>(sql + param, new { filtrate = filtrate }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
string s = ex.Message;
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
public PersonalCheckModel getOne(string id)
|
|||
|
|
{
|
|||
|
|
string sql = "SELECT info.id AS id, info.name,info.phone,info.ident,info.type,u.nickname AS nickname,info.pass_time AS passTime," +
|
|||
|
|
" info.unit FROM infection_open_user_info info LEFT JOIN infection_open_user u ON u.id = info.pass_by WHERE info.id = @id";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<PersonalCheckModel>(sql, new { id = id }).First();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public object save(PersonalCheckModel model)
|
|||
|
|
{
|
|||
|
|
System.DateTime currentTime = new System.DateTime();
|
|||
|
|
currentTime = System.DateTime.Now;
|
|||
|
|
model.create_time = currentTime.ToString();
|
|||
|
|
string sql = "";
|
|||
|
|
if (model.id == 0)
|
|||
|
|
{
|
|||
|
|
sql = @"INSERT INTO [dbo].[infection_open_user_info]
|
|||
|
|
([name]
|
|||
|
|
,[phone]
|
|||
|
|
,[ident]
|
|||
|
|
,[type]
|
|||
|
|
,[unit]
|
|||
|
|
,[user_id]
|
|||
|
|
,[create_time]
|
|||
|
|
)
|
|||
|
|
VALUES
|
|||
|
|
(@name
|
|||
|
|
,@phone
|
|||
|
|
,@ident
|
|||
|
|
,0
|
|||
|
|
,@unit
|
|||
|
|
,@user_id
|
|||
|
|
,@create_time
|
|||
|
|
)";
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sql = @"UPDATE [dbo].[infection_open_user_info]
|
|||
|
|
SET [name] = @name
|
|||
|
|
,[phone] = @phone
|
|||
|
|
,[ident] = @ident
|
|||
|
|
,[type] = @type
|
|||
|
|
,[pass_time] = @passTime
|
|||
|
|
,[unit] = @unit
|
|||
|
|
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 updateType(string id,string type)
|
|||
|
|
{
|
|||
|
|
string sql = @"UPDATE [dbo].[infection_open_user_info]
|
|||
|
|
SET [type] = @type
|
|||
|
|
WHERE id=@id";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
int result = conn.Execute(sql, new { @id = id, @type = type});
|
|||
|
|
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 bool isExist(string num, int jgid)
|
|||
|
|
{
|
|||
|
|
//throw new NotImplementedException();
|
|||
|
|
string sql = "select count(1) from professionalExam_register where physical_num=@num";
|
|||
|
|
if (jgid != 49)
|
|||
|
|
{
|
|||
|
|
sql += " and jgid=" + jgid;
|
|||
|
|
}
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return (conn.ExecuteScalar<int>(sql, new { num = num }) != 0 ? true : false);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//; select SCOPE_IDENTITY()
|
|||
|
|
public int Add(PersonalCheckModel model)
|
|||
|
|
{
|
|||
|
|
string sql = @"INSERT INTO [dbo].[professionalExam_register]
|
|||
|
|
([health_certificate_number]
|
|||
|
|
,[physical_num]
|
|||
|
|
,[physical_num_old]
|
|||
|
|
,[person_name]
|
|||
|
|
,[sex]
|
|||
|
|
,[person_age]
|
|||
|
|
,[birth]
|
|||
|
|
,[card_number]
|
|||
|
|
,[phone]
|
|||
|
|
,[native_info_maintain_id]
|
|||
|
|
,[home_address]
|
|||
|
|
,[category]
|
|||
|
|
,[trade_type_maintain_id]
|
|||
|
|
,[work_category]
|
|||
|
|
,[person_category]
|
|||
|
|
,[person_type_maintain_id]
|
|||
|
|
,[util_name]
|
|||
|
|
,[enterprise_info_maintain_id]
|
|||
|
|
,[area]
|
|||
|
|
,[depart]
|
|||
|
|
,[physical_category]
|
|||
|
|
,[medical_scheme_maintain_id]
|
|||
|
|
,[register_type]
|
|||
|
|
,[physical_way]
|
|||
|
|
,[physical_status]
|
|||
|
|
,[register_date]
|
|||
|
|
,[phone_path]
|
|||
|
|
,[status]
|
|||
|
|
,[prescription]
|
|||
|
|
,[physial_expenses]
|
|||
|
|
,[payment]
|
|||
|
|
,[haemospasia]
|
|||
|
|
,[stool]
|
|||
|
|
,[urine]
|
|||
|
|
,[image]
|
|||
|
|
,[director_approval_status]
|
|||
|
|
,[procedure_status]
|
|||
|
|
,[main_review]
|
|||
|
|
,[main_result]
|
|||
|
|
,[result_status]
|
|||
|
|
,[is_printed]
|
|||
|
|
,[wait_print]
|
|||
|
|
,[other]
|
|||
|
|
,[printtimes]
|
|||
|
|
,[exam_type_id]
|
|||
|
|
,[exam_type]
|
|||
|
|
,[main_user_id]
|
|||
|
|
,[main_user_name]
|
|||
|
|
,[main_review_date]
|
|||
|
|
,[work_category_id]
|
|||
|
|
,[check_date]
|
|||
|
|
,[security_card_no]
|
|||
|
|
,[flag]
|
|||
|
|
,[physical_type]
|
|||
|
|
,[special_health_certificate]
|
|||
|
|
,[charging_time])
|
|||
|
|
VALUES
|
|||
|
|
(@health_certificate_number
|
|||
|
|
,@physical_num
|
|||
|
|
,@physical_num_old
|
|||
|
|
,@person_name
|
|||
|
|
,@sex
|
|||
|
|
,@person_age
|
|||
|
|
,@birth
|
|||
|
|
,@card_number
|
|||
|
|
,@phone
|
|||
|
|
,@native_info_maintain_id
|
|||
|
|
,@home_address
|
|||
|
|
;select SCOPE_IDENTITY()";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<int>(sql, model).FirstOrDefault();
|
|||
|
|
//return (conn.Query<int>(sql, model).FirstOrDefault() != 0 ? true : false);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public object Update2(PersonalCheckModel model)
|
|||
|
|
{
|
|||
|
|
// model.passTime = DateTime.Now.ToString("yyyy-MM-dd");
|
|||
|
|
|
|||
|
|
string sql = @"UPDATE [dbo].[professionalExam_register]
|
|||
|
|
SET [person_name] = @person_name
|
|||
|
|
,[sex] = @sex
|
|||
|
|
,[nation] =@nation
|
|||
|
|
,[marry_status]=@marry_status
|
|||
|
|
,[person_age] = @person_age
|
|||
|
|
,[birth] = @birth
|
|||
|
|
,[phone] = @phone
|
|||
|
|
,[native_info_maintain_id] = @native_info_maintain_id
|
|||
|
|
,[home_address] = @home_address
|
|||
|
|
,[work_category] = @work_category
|
|||
|
|
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 List<PersonalCheckModel> GetAllList(Criteria model)
|
|||
|
|
{
|
|||
|
|
string sql = "";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
switch (model.result_status)
|
|||
|
|
{
|
|||
|
|
case "1":
|
|||
|
|
sql = @"select id,health_certificate_number,enterprise_info_maintain_id,physical_num,person_name,person_age,card_number,phone,category,work_category,util_name,area,register_type,hazard_ids,job_status_id
|
|||
|
|
from professionalExam_register
|
|||
|
|
where id not in(select distinct person_id from professionalExam_project_result where qualified like '不合格' or deficiency like '缺项')
|
|||
|
|
and procedure_status like '已录入检查结果' and register_type not like '复检登记'
|
|||
|
|
and freezing_and_thawing is null and register_date >= @startDate and register_date <= @endDate and physical_type like '%职业%'";
|
|||
|
|
break;
|
|||
|
|
case "2":
|
|||
|
|
sql = @"select id,health_certificate_number,enterprise_info_maintain_id,physical_num,person_name,person_age,card_number,phone,category,work_category,util_name,area,register_type ,hazard_ids,job_status_id
|
|||
|
|
from professionalExam_register
|
|||
|
|
where id in(select distinct person_id from professionalExam_project_result where qualified like '不合格')
|
|||
|
|
and procedure_status like '已录入检查结果' and register_type not like '复检登记'
|
|||
|
|
and freezing_and_thawing is null and register_date >= @startDate and register_date <= @endDate and physical_type like '%职业%'";
|
|||
|
|
break;
|
|||
|
|
case "3":
|
|||
|
|
sql = @"select id,health_certificate_number,enterprise_info_maintain_id,physical_num,person_name,person_age,card_number,phone,category,work_category,util_name,area,register_type ,hazard_ids,job_status_id
|
|||
|
|
from professionalExam_register
|
|||
|
|
where register_type like '复检登记' and procedure_status like '已录入检查结果'
|
|||
|
|
and freezing_and_thawing is null and register_date >= @startDate and register_date <= @endDate and physical_type like '%职业%'";
|
|||
|
|
break;
|
|||
|
|
case "4":
|
|||
|
|
sql = @"select id,health_certificate_number,enterprise_info_maintain_id,physical_num,person_name,person_age,card_number,phone,category,work_category,util_name,area,register_type ,hazard_ids,job_status_id
|
|||
|
|
from professionalExam_register
|
|||
|
|
where procedure_status in('主检医生已审核','已打印健康证')
|
|||
|
|
and freezing_and_thawing is null and register_date >= @startDate and register_date <= @endDate and physical_type like '%职业%'";
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (model.person_name != "")
|
|||
|
|
{
|
|||
|
|
sql += " and person_name like @person_name ";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (model.physical_num != "")
|
|||
|
|
{
|
|||
|
|
sql += " and physical_num like @physical_num ";
|
|||
|
|
}
|
|||
|
|
if (model.check_type != "")
|
|||
|
|
{
|
|||
|
|
sql += " and exam_type = @check_type ";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (model.checkstartDate != null && model.checkstartDate != "")
|
|||
|
|
{
|
|||
|
|
sql += "and check_date >= @checkstartDate ";
|
|||
|
|
}
|
|||
|
|
if (model.checkendDate != null && model.checkendDate != "")
|
|||
|
|
{
|
|||
|
|
sql += "and check_date <= @checkendDate ";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
sql += " order by register_date";
|
|||
|
|
return conn.Query<PersonalCheckModel>(sql, new { @checkstartDate = model.checkstartDate, @checkendDate = model.checkendDate, @check_type = model.check_type, @person_name = "%" + model.person_name + "%", @physical_num = "%" + model.physical_num + "%", @startDate = model.startDate, @endDate = model.endDate }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
public PersonalCheckModel getOneByNum(string physical_num)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from professionalExam_register where physical_num=@physical_num";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<PersonalCheckModel>(sql, new { physical_num = physical_num }).First();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|