274 lines
12 KiB
C#
274 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;
|
|||
|
|
using dccdc.Models.DTO;
|
|||
|
|
|
|||
|
|
namespace dccdc.DAL
|
|||
|
|
{
|
|||
|
|
public class PhysicalQueryDal
|
|||
|
|
{
|
|||
|
|
public PhysicalQueryModel getOn;
|
|||
|
|
|
|||
|
|
public List<PhysicalQueryModel> GetAllList(string start, string end)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (start != null && start != "")
|
|||
|
|
{
|
|||
|
|
param += "and order_date >= @start and order_date <= @end";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
string sql = "SELECT t.id,t.name,t.phone,t.test_org,t.ident,t.order_date,t.state,t.birth,t.unit FROM infection_test t WHERE 1 = 1 ";
|
|||
|
|
return conn.Query<PhysicalQueryModel>(sql + param, new { start = start,end=end }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
string s = ex.Message;
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
public PhysicalQueryModel getOne(string id)
|
|||
|
|
{
|
|||
|
|
string sql = "SELECT * FROM infection_test WHERE id = @id";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<PhysicalQueryModel>(sql, new { id = id }).First();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<PhysicalQueryModel> GetAllList2()
|
|||
|
|
{
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<PhysicalQueryModel>("select * from infection_test where 1=1 ").ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public object save(PhysicalQueryModel model)
|
|||
|
|
{
|
|||
|
|
string sql = "";
|
|||
|
|
if (model.id == 0)
|
|||
|
|
{
|
|||
|
|
sql = @"INSERT INTO [dbo].[infection_test]
|
|||
|
|
,[user_id]
|
|||
|
|
,[test_type]
|
|||
|
|
,[test_org]
|
|||
|
|
,[name]
|
|||
|
|
,[ident]
|
|||
|
|
,[phone]
|
|||
|
|
,[order_date]
|
|||
|
|
,[order_place]
|
|||
|
|
,[test_date]
|
|||
|
|
,[cancel_reason]
|
|||
|
|
,[state]
|
|||
|
|
,[create_time]
|
|||
|
|
)
|
|||
|
|
VALUES
|
|||
|
|
(,@user_id
|
|||
|
|
,@test_type
|
|||
|
|
,@test_org
|
|||
|
|
,@name
|
|||
|
|
,@ident
|
|||
|
|
,@phone
|
|||
|
|
,@order_date
|
|||
|
|
,@order_place
|
|||
|
|
,@test_date
|
|||
|
|
,@cancel_reason
|
|||
|
|
,@state
|
|||
|
|
,@create_time
|
|||
|
|
)";
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sql = @"UPDATE [dbo].[infection_test]
|
|||
|
|
SET [user_id] = @user_id
|
|||
|
|
,[test_type] = @test_type
|
|||
|
|
,[test_org] = @test_org
|
|||
|
|
,[name] = @name
|
|||
|
|
,[ident] = @ident
|
|||
|
|
,[phone] = @phone
|
|||
|
|
,[order_date] = @order_date
|
|||
|
|
,[test_date] = @test_date
|
|||
|
|
,[cancel_reason] = @cancel_reason
|
|||
|
|
,[state] = @state
|
|||
|
|
,[birth] = @birth
|
|||
|
|
,[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 abolishOrder(PhysicalQueryModel model)
|
|||
|
|
{
|
|||
|
|
string sql = @"UPDATE [dbo].[infection_test]
|
|||
|
|
SET [cancel_reason] = @cancel_reason
|
|||
|
|
,[state] = 2
|
|||
|
|
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<PhysicalQueryModel> downFile(string start, string end)
|
|||
|
|
{
|
|||
|
|
StringBuilder u = new StringBuilder("select * from infection_test where 1=1");
|
|||
|
|
|
|||
|
|
if (start != null && end != null && start != "" && end != "")
|
|||
|
|
{
|
|||
|
|
u.Append(" and order_date >= @start and order_date <= @end");
|
|||
|
|
}
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
var list = conn.Query<PhysicalQueryModel>(u.ToString(), new { start = start, end = end }).ToList();
|
|||
|
|
return list;
|
|||
|
|
}
|
|||
|
|
catch (Exception e) {
|
|||
|
|
string err = e.Message;
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public object Update2(PhysicalQueryModel 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<PhysicalQueryModel> 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<PhysicalQueryModel>(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();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|