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();
            }
        }
    }
}