45 lines
3.2 KiB
C#
45 lines
3.2 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 ProjectFactorMaintainDal
|
|
{
|
|
//根据有害因素ID 获取关联的项目
|
|
public List<DTO_TjfnTjxmModel> GetListBytjxmgzzt(string yhysid, string gzzt)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql1 = "select distinct c.harmful_factor_type from ( select a.id, b.harmful_factor_type from( select id, [harmful_factor_type] = CONVERT(xml,'<root><v>' + REPLACE([harmful_factor_type], ',', '</v><v>') + '</v></root>') from harmful_factors_maintain where id in @yhysid )a OUTER APPLY( select harmful_factor_type = N.v.value('.', 'varchar(200)') from a.[harmful_factor_type].nodes('/root/v') N(v) )b )c";
|
|
string[] d = conn.Query<string>(sql1, new { @yhysid = yhysid.Split(',').ToArray() }).ToArray();
|
|
|
|
//没看没明白这个sql…………
|
|
string sql = @"select * from (
|
|
select fact.is_check, fact.exam_project_maintain_id as 'id', fact.project_name,
|
|
fact.exam_group, proj.project_id, proj.project_alias, proj.project_type,
|
|
proj.standard_value, proj.unit, proj.exam_group_maintain_id, proj.model, proj.judgment_mode_maintain_id,
|
|
proj.exam_prepose_condition_maintain_id from exam_project_maintain proj left join project_factor_maintain fact
|
|
on fact.project_code = proj.project_id
|
|
where proj.status='是' and proj.exam_group not like '%未分组%' and fact.factor_code in @harmful_factors_type_maintain_id and fact.jobs_state_maintain_id = @jobs_state_maintain_id
|
|
group by fact.is_check, fact.exam_project_maintain_id, fact.project_name, fact.exam_group, proj.id, proj.project_id, proj.project_alias, proj.project_type,
|
|
proj.standard_value, proj.unit, proj.exam_group_maintain_id, proj.model, proj.judgment_mode_maintain_id, proj.exam_prepose_condition_maintain_id )as a where 1>(
|
|
select count(*) from (select fact.is_check, fact.exam_project_maintain_id, fact.project_name, fact.exam_group, proj.project_id, proj.id, proj.project_alias,
|
|
proj.project_type, proj.standard_value, proj.unit, proj.exam_group_maintain_id, proj.model, proj.judgment_mode_maintain_id, proj.exam_prepose_condition_maintain_id
|
|
from exam_project_maintain proj left join project_factor_maintain fact on fact.exam_project_maintain_id = proj.id where proj.status='是' and
|
|
proj.exam_group not like '%未分组%' and fact.factor_code in @harmful_factors_type_maintain_id and fact.jobs_state_maintain_id = @jobs_state_maintain_id group by fact.is_check,
|
|
fact.exam_project_maintain_id, fact.project_name, fact.exam_group, proj.id, proj.project_id, proj.project_alias, proj.project_type, proj.standard_value,
|
|
proj.unit, proj.exam_group_maintain_id, proj.model, proj.judgment_mode_maintain_id, proj.exam_prepose_condition_maintain_id ) as b
|
|
where b.project_name = a.project_name and b.is_check > a.is_check )order by exam_group, project_id, project_name";
|
|
|
|
return conn.Query<DTO_TjfnTjxmModel>(sql, new { @harmful_factors_type_maintain_id = d, jobs_state_maintain_id = gzzt }).ToList();
|
|
}
|
|
}
|
|
}
|
|
}
|