674 lines
27 KiB
C#
674 lines
27 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 System.Net.Http.Headers;
|
|||
|
|
using Microsoft.Win32.SafeHandles;
|
|||
|
|
using System.Data.SqlClient;
|
|||
|
|
using static Dapper.SqlMapper;
|
|||
|
|
using System.Configuration;
|
|||
|
|
using System.Reflection;
|
|||
|
|
using System.Text.RegularExpressions;
|
|||
|
|
|
|||
|
|
namespace dccdc.DAL
|
|||
|
|
{
|
|||
|
|
public class ExamProjectMaintainDal
|
|||
|
|
{
|
|||
|
|
/**
|
|||
|
|
* 战晓杰 查询项目 传入条件为非ID(not in)
|
|||
|
|
*/
|
|||
|
|
public List<ExamProjectMaintainModel> GetAllList(string[] data,string cxtj="")
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (data.Length > 0)
|
|||
|
|
{
|
|||
|
|
string strs = "(";
|
|||
|
|
for (int i = 0; i < data.Length; i++)
|
|||
|
|
{
|
|||
|
|
string str = data[i];
|
|||
|
|
if (i == 0)
|
|||
|
|
{
|
|||
|
|
strs += str;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
strs += ("," + str);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
strs += ")";
|
|||
|
|
param = " and project_id not in " + strs;
|
|||
|
|
}
|
|||
|
|
if(!string.IsNullOrEmpty(cxtj))
|
|||
|
|
{
|
|||
|
|
param += " and project_name like @cxtj";
|
|||
|
|
}
|
|||
|
|
string sql = "select * from exam_project_maintain where 1=1" + param;
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql,new { cxtj="%"+cxtj+"%" }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据id获取体检项目
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="id"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ExamProjectMaintainModel> GetAllList(string id)
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (id != "")
|
|||
|
|
{
|
|||
|
|
param = " and id=@id";
|
|||
|
|
}
|
|||
|
|
string sql = "select * from exam_project_maintain where 1=1" + param;
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql, new { @id = id }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<ExamProjectMaintainModel> GetAllList2(string id, string teamid,string personid)
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (!string.IsNullOrEmpty(id))
|
|||
|
|
{
|
|||
|
|
param += " and a.id=@id";
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(teamid))
|
|||
|
|
{
|
|||
|
|
param += " and b.id=@teamid";
|
|||
|
|
}
|
|||
|
|
string sql = "select a.*,b.team_name from exam_project_maintain a left join exam_group_maintain b on a.exam_group_maintain_id=b.id where 1=1 and a.project_id not in (select project_id from professionalExam_project_result where person_id=@personid and qualified='不合格')" + param;
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql, new { @id = id, @teamid = teamid, @personid = personid }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据personid获取异常项目
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="id"></param>
|
|||
|
|
/// <param name="personid"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ProfessionalExamProjectResultModel> GetAllList3(string id, string personid)
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (!string.IsNullOrEmpty(id))
|
|||
|
|
{
|
|||
|
|
param += " and a.id=@id";
|
|||
|
|
}
|
|||
|
|
//string sql = "select a.*,b.team_name from exam_project_maintain a left join exam_group_maintain b on a.exam_group_maintain_id=b.id where 1=1 and a.project_id in (select project_id from professionalExam_project_result where person_id=@personid and qualified='不合格')" + param;
|
|||
|
|
string sql = "select a.*,b.exam_group as team_name,b.id as exam_id from professionalExam_project_result a,exam_project_maintain b \r\nwhere a.person_id =@personid and qualified='不合格' and a.project_id = b.project_id and a.exam_group_maintain_id = b.exam_group_maintain_id" + param + " order by qualified";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ProfessionalExamProjectResultModel>(sql, new { @id = id, @personid = personid }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据personid/teamid获取其体检项目
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="id"></param>
|
|||
|
|
/// <param name="personid"></param>
|
|||
|
|
public List<ProfessionalExamProjectResultModel> GetAllList4(string id, string teamid, string personid)
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (!string.IsNullOrEmpty(id))
|
|||
|
|
{
|
|||
|
|
param += " and a.id=@id";
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(teamid))
|
|||
|
|
{
|
|||
|
|
param += " and b.exam_group_maintain_id=@teamid";
|
|||
|
|
}
|
|||
|
|
string sql = "select a.*,b.exam_group as team_name,b.id as exam_id from professionalExam_project_result a,exam_project_maintain b \r\nwhere a.person_id =@personid and a.project_id = b.project_id and a.exam_group_maintain_id = b.exam_group_maintain_id" + param + " order by qualified";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ProfessionalExamProjectResultModel>(sql, new { @id = id, @teamid = teamid, @personid = personid }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/// <returns></returns>
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据项目编号/项目名称/项目拼音编码查询
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="where"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ExamProjectMaintainModel> GetAllList4(string where)
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (where != "")
|
|||
|
|
{
|
|||
|
|
param = " and( project_id like @where or project_name like @where or pinyin_code like @where) ";
|
|||
|
|
}
|
|||
|
|
string sql = "select * from exam_project_maintain where 1=1" + param;
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql, new { where = "%" + where + "%" }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据项目名称查询
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="where"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ExamProjectMaintainModel> GetAllListByName(string where)
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (where != "")
|
|||
|
|
{
|
|||
|
|
param = " and project_name like @where ";
|
|||
|
|
}
|
|||
|
|
string sql = "select * from exam_project_maintain where 1=1" + param;
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql, new { where = "%" + where + "%" }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据拼音码或体检小组查询列表
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="id"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ExamProjectMaintainModel> GetAllBypytjxz(string groupid, string py,string name,bool status)
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (groupid != "")
|
|||
|
|
{
|
|||
|
|
param += " and exam_group_maintain_id=@groupid";
|
|||
|
|
}
|
|||
|
|
if (py != "")
|
|||
|
|
{
|
|||
|
|
param += " and pinyin_code like @py";
|
|||
|
|
}
|
|||
|
|
if(status)
|
|||
|
|
{
|
|||
|
|
param += " and status = '是'";
|
|||
|
|
}
|
|||
|
|
if(name !="")
|
|||
|
|
{
|
|||
|
|
param += " and project_name like @name";
|
|||
|
|
}
|
|||
|
|
string sql = "select * from exam_project_maintain where 1=1 " + param;
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql, new { @groupid = groupid, @py = "%" + py + "%", @name = "%" + name + "%" }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据体检方案里的体检项目列查询明细
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="id"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ExamProjectMaintainModel> GetTjfnmxByIDs(string id)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from exam_project_maintain where status='是' and id in @ids order by exam_group_maintain_id ";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql, new { @ids = id.TrimStart(',').TrimEnd(',').Split(',') }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<ExamProjectMaintainModel> GetAllListByprojectid(string id)
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (id != "")
|
|||
|
|
{
|
|||
|
|
param = " and project_id = @id";
|
|||
|
|
}
|
|||
|
|
string sql = "select * from exam_project_maintain where 1 = 1" + param;
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql, new { @id = id }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<ExamProjectMaintainModel> GetAllListByprojectids(string id)
|
|||
|
|
{
|
|||
|
|
string param = "";
|
|||
|
|
if (id != "")
|
|||
|
|
{
|
|||
|
|
param = " and project_id in @id";
|
|||
|
|
}
|
|||
|
|
string sql = "select * from exam_project_maintain where 1=1" + param;
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql, new { @id = id.TrimStart(',').TrimEnd(',').Split(',') }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据体检项目查询该体检项目的检查科室
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="ids"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<DtoRoom> GetRooms(string ids)
|
|||
|
|
{
|
|||
|
|
string sql = "select p.id,l.team_name,p.project_name,l.check_room from exam_group_local_maintain l left join exam_group_maintain g on g.id=l.exam_group_maintain_id left join exam_project_maintain p on p.exam_group_maintain_id=g.id where p.id in @id";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.DtoRoom>(sql, new { @id = ids.TrimStart(',').TrimEnd(',').Split(',') }).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool Update(ExamProjectMaintainModel model)
|
|||
|
|
{
|
|||
|
|
string sql = @"UPDATE [exam_project_maintain]
|
|||
|
|
SET [project_id] = @project_id
|
|||
|
|
,[project_name] = @project_name
|
|||
|
|
,[project_alias] = @project_alias
|
|||
|
|
,[charge_project] = @charge_project
|
|||
|
|
,[charge_project_maintain_id] = @charge_project_maintain_id
|
|||
|
|
,[exam_group] = @exam_group
|
|||
|
|
,[exam_group_maintain_id] = @exam_group_maintain_id
|
|||
|
|
,[project_type_id] = @project_type_id
|
|||
|
|
,[project_type] = @project_type
|
|||
|
|
,[prepose_condition] = @prepose_condition
|
|||
|
|
,[exam_prepose_condition_maintain_id] = @exam_prepose_condition_maintain_id
|
|||
|
|
,[model] = @model
|
|||
|
|
,[judgment_mode_maintain_id] = @judgment_mode_maintain_id
|
|||
|
|
,[exam_project_result] = @exam_project_result
|
|||
|
|
,[exam_project_result_id] = @exam_project_result_id
|
|||
|
|
,[max] = @max
|
|||
|
|
,[min] = @min
|
|||
|
|
,[critical_max] = @critical_max
|
|||
|
|
,[critical_min] = @critical_min
|
|||
|
|
,[standard_value] = @standard_value
|
|||
|
|
,[default_value] = @default_value
|
|||
|
|
,[unit] = @unit
|
|||
|
|
,[bar_code] = @bar_code
|
|||
|
|
,[multiple_code] = @multiple_code
|
|||
|
|
,[sex] = @sex
|
|||
|
|
,[is_lab] = @is_lab
|
|||
|
|
,[is_show] = @is_show
|
|||
|
|
,[is_print] = @is_print
|
|||
|
|
,[status] = @status
|
|||
|
|
,[is_general] = @is_general
|
|||
|
|
,[is_onSite] = @is_onSite
|
|||
|
|
,[pinyin_code] = @pinyin_code
|
|||
|
|
,[order_id] = @order_id
|
|||
|
|
,[lis]=@lis
|
|||
|
|
,[special_conf]=@special_conf
|
|||
|
|
WHERE id=@id
|
|||
|
|
";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return (conn.Execute(sql, model) != 0 ? true : false);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 增加体检项目
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="model"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
//lxlx
|
|||
|
|
public List<string> Add(ExamProjectMaintainModel model)
|
|||
|
|
{
|
|||
|
|
#region 老代码
|
|||
|
|
//string sql = @"INSERT INTO [exam_project_maintain]
|
|||
|
|
// ([project_id]
|
|||
|
|
// ,[project_name]
|
|||
|
|
// ,[project_alias]
|
|||
|
|
// ,[charge_project]
|
|||
|
|
// ,[charge_project_maintain_id]
|
|||
|
|
// ,[exam_group]
|
|||
|
|
// ,[exam_group_maintain_id]
|
|||
|
|
// ,[project_type_id]
|
|||
|
|
// ,[project_type]
|
|||
|
|
// ,[prepose_condition]
|
|||
|
|
// ,[exam_prepose_condition_maintain_id]
|
|||
|
|
// ,[model]
|
|||
|
|
// ,[judgment_mode_maintain_id]
|
|||
|
|
// ,[exam_project_result]
|
|||
|
|
// ,[exam_project_result_id]
|
|||
|
|
// ,[max]
|
|||
|
|
// ,[min]
|
|||
|
|
// ,[critical_max]
|
|||
|
|
// ,[critical_min]
|
|||
|
|
// ,[standard_value]
|
|||
|
|
// ,[default_value]
|
|||
|
|
// ,[unit]
|
|||
|
|
// ,[bar_code]
|
|||
|
|
// ,[multiple_code]
|
|||
|
|
// ,[sex]
|
|||
|
|
// ,[is_lab]
|
|||
|
|
// ,[is_show]
|
|||
|
|
// ,[is_print]
|
|||
|
|
// ,[status]
|
|||
|
|
// ,[is_general]
|
|||
|
|
// ,[is_onSite]
|
|||
|
|
// ,[pinyin_code]
|
|||
|
|
// ,[order_id],[lis],[special_conf])
|
|||
|
|
//VALUES
|
|||
|
|
// (@project_id
|
|||
|
|
// ,@project_name
|
|||
|
|
// ,@project_alias
|
|||
|
|
// ,@charge_project
|
|||
|
|
// ,@charge_project_maintain_id
|
|||
|
|
// ,@exam_group
|
|||
|
|
// ,@exam_group_maintain_id
|
|||
|
|
// ,@project_type_id
|
|||
|
|
// ,@project_type
|
|||
|
|
// ,@prepose_condition
|
|||
|
|
// ,@exam_prepose_condition_maintain_id
|
|||
|
|
// ,@model
|
|||
|
|
// ,@judgment_mode_maintain_id
|
|||
|
|
// ,@exam_project_result
|
|||
|
|
// ,@exam_project_result_id
|
|||
|
|
// ,@max
|
|||
|
|
// ,@min
|
|||
|
|
// ,@critical_max
|
|||
|
|
// ,@critical_min
|
|||
|
|
// ,@standard_value
|
|||
|
|
// ,@default_value
|
|||
|
|
// ,@unit
|
|||
|
|
// ,@bar_code
|
|||
|
|
// ,@multiple_code
|
|||
|
|
// ,@sex
|
|||
|
|
// ,@is_lab
|
|||
|
|
// ,@is_show
|
|||
|
|
// ,@is_print
|
|||
|
|
// ,@status
|
|||
|
|
// ,@is_general
|
|||
|
|
// ,@is_onSite
|
|||
|
|
// ,@pinyin_code
|
|||
|
|
// ,@order_id,@lis,@special_conf)
|
|||
|
|
//";
|
|||
|
|
//using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
//{
|
|||
|
|
// string aa = conn.ConnectionString;
|
|||
|
|
// return (conn.Execute(sql, model) != 0 ? true : false);
|
|||
|
|
//}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
List<string> list = new List<string>();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
//新：要返回ID
|
|||
|
|
string sql = @"INSERT INTO [exam_project_maintain]
|
|||
|
|
([project_id],
|
|||
|
|
[project_name],
|
|||
|
|
[project_alias],
|
|||
|
|
[charge_project],
|
|||
|
|
[charge_project_maintain_id],
|
|||
|
|
[exam_group],
|
|||
|
|
[exam_group_maintain_id],
|
|||
|
|
[project_type_id],
|
|||
|
|
[project_type],
|
|||
|
|
[prepose_condition],
|
|||
|
|
[exam_prepose_condition_maintain_id],
|
|||
|
|
[model],
|
|||
|
|
[judgment_mode_maintain_id],
|
|||
|
|
[exam_project_result],
|
|||
|
|
[exam_project_result_id],
|
|||
|
|
[max],
|
|||
|
|
[min],
|
|||
|
|
[critical_max],
|
|||
|
|
[critical_min],
|
|||
|
|
[standard_value],
|
|||
|
|
[default_value],
|
|||
|
|
[unit],
|
|||
|
|
[bar_code],
|
|||
|
|
[multiple_code],
|
|||
|
|
[sex],
|
|||
|
|
[is_lab],
|
|||
|
|
[is_show],
|
|||
|
|
[is_print],
|
|||
|
|
[status],
|
|||
|
|
[is_general],
|
|||
|
|
[is_onSite],
|
|||
|
|
[pinyin_code],
|
|||
|
|
[order_id],
|
|||
|
|
[lis],
|
|||
|
|
[special_conf])
|
|||
|
|
VALUES
|
|||
|
|
('" + model.project_id + "','" + model.project_name + "','" + model.project_alias + "','" + model.charge_project + "','" + model.charge_project_maintain_id + "','" + model.exam_group + "','" + model.exam_group_maintain_id + "','" + model.project_type_id + "','" + model.project_type + "','" + model.prepose_condition + "','" + model.exam_prepose_condition_maintain_id + "','" + model.model + "','" + model.judgment_mode_maintain_id + "','" + model.exam_project_result + "','" + model.exam_project_result_id + "','" + model.max + "','" + model.min + "','" + model.critical_max + "','" + model.critical_min + "','" + model.standard_value + "','" + model.default_value + "','" + model.unit + "','" + model.bar_code + "','" + model.multiple_code + "','" + model.sex + "','" + model.is_lab + "','" + model.is_show + "','" + model.is_print + "','" + model.status + "','" + model.is_general + "','" + model.is_onSite + "','" + model.pinyin_code + "','" + model.order_id + "','" + model.lis + "','" + model.special_conf + "') select @@identity as 'ID' ";
|
|||
|
|
|
|||
|
|
string connection1 = CommHelper.GetSqlConnection1("TJDB");
|
|||
|
|
|
|||
|
|
using (SqlConnection connection = new SqlConnection(connection1))
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
connection.Open();
|
|||
|
|
SqlDataAdapter query = new SqlDataAdapter(sql, connection1);
|
|||
|
|
query.Fill(ds, "ds");//“ds”可以参数化为表名称；
|
|||
|
|
int rowsCount = ds.Tables[0].Rows.Count;
|
|||
|
|
if (rowsCount > 0)
|
|||
|
|
{
|
|||
|
|
for (int n = 0; n < rowsCount; n++)
|
|||
|
|
{
|
|||
|
|
int a = DataRowToModel(ds.Tables[0].Rows[n]);
|
|||
|
|
list.Add(a.ToString());
|
|||
|
|
list.Add(model.project_name);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception)
|
|||
|
|
{
|
|||
|
|
//ZWL.Common.SaveLog.Logs("C:\\AppExe", "刷新", "出错了：" + e.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return list;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//lxlx
|
|||
|
|
private int DataRowToModel(DataRow dataRow)
|
|||
|
|
{
|
|||
|
|
if (dataRow != null)
|
|||
|
|
{
|
|||
|
|
if (dataRow["ID"] != null)
|
|||
|
|
{
|
|||
|
|
return Convert.ToInt32(dataRow["ID"]);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//lxlx
|
|||
|
|
public bool Delete(int ID)
|
|||
|
|
{
|
|||
|
|
string sql = "DELETE exam_project_maintain where id ="+ID+"";
|
|||
|
|
|
|||
|
|
string conString = CommHelper.GetSqlConnection1("TJDB");
|
|||
|
|
|
|||
|
|
using (SqlConnection connection = new SqlConnection(conString))//Connection
|
|||
|
|
{
|
|||
|
|
using (SqlCommand cmd = new SqlCommand(sql, connection))//Command
|
|||
|
|
{
|
|||
|
|
connection.Open();
|
|||
|
|
int rows = cmd.ExecuteNonQuery();//ExecuteNonQuery
|
|||
|
|
if (rows > 0)
|
|||
|
|
{
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据person_id查询检查项目
|
|||
|
|
/// 2023-11-22 xulu
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="tjxz"></param>
|
|||
|
|
/// <param name="py"></param>
|
|||
|
|
/// <param name="name"></param>
|
|||
|
|
/// <param name="person_id"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ExamProjectMaintainModel> getCheckItemsByPersonId(string tjxz, string py, string name, string person_id)
|
|||
|
|
{
|
|||
|
|
string sql1 = "select check_item_ids from professionalExam_register where id=" + person_id;
|
|||
|
|
string sql = "select * from exam_project_maintain";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
string ids = conn.Query(sql1).ToList()[0].check_item_ids;
|
|||
|
|
if (!string.IsNullOrEmpty(ids))
|
|||
|
|
{
|
|||
|
|
if (ids.LastIndexOf(',') == ids.Length - 1)
|
|||
|
|
{
|
|||
|
|
ids = ids.Substring(0, ids.Length - 1);
|
|||
|
|
}
|
|||
|
|
sql += " where id in(" + ids + ")";
|
|||
|
|
}
|
|||
|
|
if(!string.IsNullOrEmpty(tjxz))
|
|||
|
|
{
|
|||
|
|
sql += " and exam_group_maintain_id="+tjxz;
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(py))
|
|||
|
|
{
|
|||
|
|
sql += " and pinyin_code like '%"+py+"%'";
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(name))
|
|||
|
|
{
|
|||
|
|
sql += " and project_name like '%" + name + "%'";
|
|||
|
|
}
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据person_id获取打印项目
|
|||
|
|
/// 2023-11-22 xulu
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="person_id"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ExamProjectMaintainModel> getPrintItemsByPersonId(string person_id)
|
|||
|
|
{
|
|||
|
|
string sql1 = "select print_item_ids from professionalExam_register where id=" + person_id;
|
|||
|
|
string sql = "select * from exam_project_maintain";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
string ids = conn.Query(sql1).ToList()[0].print_item_ids;
|
|||
|
|
if (!string.IsNullOrEmpty(ids))
|
|||
|
|
{
|
|||
|
|
sql += " where project_id in(" + ids + ")";
|
|||
|
|
}
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据scheme_id获取检查项目
|
|||
|
|
/// 2023-11-22 xulu
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="tjxz"></param>
|
|||
|
|
/// <param name="py"></param>
|
|||
|
|
/// <param name="name"></param>
|
|||
|
|
/// <param name="scheme_id"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ExamProjectMaintainModel> getCheckItemsBySchemeId(string tjxz, string py, string name, string scheme_id,string check_item_ids)
|
|||
|
|
{
|
|||
|
|
string sql1 = "select check_item_ids from occupation_exam_scheme_maintain where id=" + scheme_id;
|
|||
|
|
string sql = "select * from exam_project_maintain";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
string ids = check_item_ids;
|
|||
|
|
if(string.IsNullOrEmpty(ids))
|
|||
|
|
{
|
|||
|
|
ids = conn.Query(sql1).ToList()[0].check_item_ids;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (!string.IsNullOrEmpty(ids))
|
|||
|
|
{
|
|||
|
|
if (ids.LastIndexOf(',') == ids.Length - 1)
|
|||
|
|
{
|
|||
|
|
ids = ids.Substring(0, ids.Length - 1);
|
|||
|
|
}
|
|||
|
|
sql += " where id in(" + ids + ")";
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(tjxz))
|
|||
|
|
{
|
|||
|
|
sql += " and exam_group_maintain_id=" + tjxz;
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(py))
|
|||
|
|
{
|
|||
|
|
sql += " and pinyin_code like '%" + py + "%'";
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(name))
|
|||
|
|
{
|
|||
|
|
sql += " and project_name like '%" + name + "%'";
|
|||
|
|
}
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据scheme_id获取打印项目
|
|||
|
|
/// 2023-11-22 xulu
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="scheme_id"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ExamProjectMaintainModel> getPrintItemsBySchemeId(string scheme_id)
|
|||
|
|
{
|
|||
|
|
string sql1 = "select print_item_ids from occupation_exam_scheme_maintain where id=" + scheme_id;
|
|||
|
|
string sql = "select * from exam_project_maintain";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
string ids = conn.Query(sql1).ToList()[0].print_item_ids;
|
|||
|
|
if (!string.IsNullOrEmpty(ids))
|
|||
|
|
{
|
|||
|
|
sql += " where project_id in(" + ids + ")";
|
|||
|
|
}
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据print_item_ids获取体检项目
|
|||
|
|
/// 2023-11-22 xulu
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="print_item_ids"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public object GetTjfnByPrintItemIds(string print_item_ids)
|
|||
|
|
{
|
|||
|
|
string sql = "select * from exam_project_maintain where status='是' and project_id in @ids order by exam_group_maintain_id ";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql, new { @ids = print_item_ids.TrimStart(',').TrimEnd(',').Split(',') }).ToList();
|
|||
|
|
}
|
|||
|
|
/* string sql = "select * from exam_project_maintain";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
if (!string.IsNullOrEmpty(print_item_ids))
|
|||
|
|
{
|
|||
|
|
sql += " where project_id in(" + print_item_ids + ")";
|
|||
|
|
}
|
|||
|
|
sql += " order by exam_group_maintain_id ";
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql).ToList();
|
|||
|
|
}*/
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据在岗状态、有害因素获取其体检项目列表
|
|||
|
|
/// 2023-11-24 xulu
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="job_status_id"></param>
|
|||
|
|
/// <param name="yhys_id"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public List<ExamProjectMaintainModel> GetAllByyhys(string job_status_id, string yhys_id)
|
|||
|
|
{
|
|||
|
|
string param = "select project_code from project_factor_maintain where jobs_state_maintain_id ="+ job_status_id+" and factor_code="+ yhys_id;
|
|||
|
|
string sql = "select * from exam_project_maintain where project_id in(" + param+")";
|
|||
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|||
|
|
{
|
|||
|
|
return conn.Query<Models.ExamProjectMaintainModel>(sql).ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|