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 ExamGroupDoctorMatintainDal
    {
        public List<Models.ERPUser> getAllUser()
        {
            using (var conn = CommHelper.GetSqlConnection("OADB"))
            {
                string sql = "select * from erpuser";
                return conn.Query<ERPUser>(sql).ToList();
            }
        }

        public List<Models.DTO.Result_SQXM> getSQXM(Models.ERPUser erpUser, string pid)
        {

            string sql2 = " and a.exam_group_maintain_id in (select distinct exam_group_maintain_id " +
                          "from professionalExam_project_result where person_id in (select id from " +
                          "professionalExam_register where physical_num = '" + pid + "'))";

            string sql = "select distinct a.exam_group,a.exam_group_maintain_id from exam_group_doctor_matintain as a left join exam_group_maintain as b " +
                         "on a.exam_group_maintain_id = b.id  where a.entry like '是' and b.status = '是'" +
                         "and b.is_collect like '否' and a.status like '是' and a.user_name like '" + erpUser.UserName + "'";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                if (!string.IsNullOrEmpty(pid))
                {
                    return conn.Query<Models.DTO.Result_SQXM>(sql + sql2).ToList();
                }
                return conn.Query<Models.DTO.Result_SQXM>(sql).ToList();
            }
        }

        public List<Models.DTO.LiShi> getLishi(string sfzh)
        {
            string sql = "select a.register_date,b.physical_num,b.project_name,b.reference_value,b.project_result,b.qualified from professionalExam_project_result b," +
                         "professionalExam_register a where a.id=b.person_id and a.card_number like '" + sfzh + "'";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.DTO.LiShi>(sql).ToList();
            }
        }

        public List<Models.ExaminationProcessModel> getTjxm(string id)
        {
            string sql = "select * from examination_process where person_id=" + id;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.ExaminationProcessModel>(sql).ToList();
            }
        }

        public List<Models.DTO.LiShi> getDanXiang(string sfzh, string xmbm, string pid)
        {
            //单项历史
            string sql = "select a.physical_num,b.project_id,b.project_name,b.reference_value,b.project_result, " +
                         "b.qualified,a.register_date from " +
                         "professionalExam_register as a " +
                         "left join professionalExam_project_result as b " +
                         "on a.id = b.person_id " +
                         "where a.card_number like '" + sfzh + "' " +
                         "and b.project_id = '" + xmbm + "' and a.id != " + pid + " order by a.physical_num";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.DTO.LiShi>(sql).ToList();
            }
        }

        public List<Models.DTO.ZiLiao> getZyjjz(string zgzt, string yhyslbIDs)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                //zyk 20230610没搞明白 职业禁忌症接害因素关系表中的字段harmful_factors_type_maintain_id，是接害因素id还是接害因素类型ID？？？？
                string sql = "select a.harmful_factor_name as yhys_Name,b.contraindicat_name as zyjjz_Name " +
                         "from harmful_factors_maintain a,contraindicat_factor_maintain b " +
                         "where a.id = b.harmful_factors_type_maintain_id and b.jobs_state_maintain_id = '" + zgzt + "' and " +
                         "b.harmful_factors_type_maintain_id in (" + yhyslbIDs + ")";
                return conn.Query<Models.DTO.ZiLiao>(sql).ToList();
            }
        }

        public List<Models.notice_factor_maintainModel> getTiXing(string zgzt, string yhyslbIDs)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select exam_group,bussiness_notice from notice_factor_maintain " +
                        "where jobs_state_maintain_id = " + zgzt + " and harmful_factors_type_maintain_id in (" + yhyslbIDs + ")";
                return conn.Query<Models.notice_factor_maintainModel>(sql).ToList();
            }
        }

        public List<Models.DTO.ZiLiao> getYszyb(string zgzt, string yhyslbIDs)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                //也是同样的问题，harmful_factors_type_maintain_id存储的实际是 接害因素id
                string sql = "select a.harmful_factor_name as yhys_Name,b.disease_name as zyjjz_Name " +
                             "from harmful_factors_maintain a,target_factor_maintain b " +
                             "where a.id = b.harmful_factors_type_maintain_id and b.jobs_state_maintain_id  = '" + zgzt + "' and " +
                             "b.harmful_factors_type_maintain_id in (" + yhyslbIDs + ")";
                return conn.Query<Models.DTO.ZiLiao>(sql).ToList();
            }
        }

        public List<Models.DTO.JglrChaLou> getChaLouWJ(string id, string djrq, string djrqEnd,int jgid)
        {
            string sql = "select a.physical_num,a.person_name,a.util_name,a.register_date,b.commit_doctor from " +
                         "professionalExam_register as a " +
                         "left join examination_process as b " +
                         "on a.id = b.person_id where " +
                         "b.is_complete is null and " +
                         "a.freezing_and_thawing is null " +
                         "and b.exam_group_maintain_id = '" + id + "' and a.register_date between '" + djrq + "' and '" + djrqEnd + "'";
            if(jgid!=49)
            {
                sql += " and a.jgid=" + jgid;
            }

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.DTO.JglrChaLou>(sql).ToList();
            }
        }

        public List<Models.DTO.JglrChaLou> getChaLouYJ(string id, string tjrq, string djrqEnd,int jgid)
        {
            string sql = "select a.physical_num,a.person_name,a.util_name,a.register_date,b.commit_doctor from " +
                         "professionalExam_register as a " +
                         "left join examination_process as b " +
                         "on a.id = b.person_id where " +
                         "b.is_complete like '已完成' and " +
                         "freezing_and_thawing is null and " +
                         "b.exam_group_maintain_id = '" + id + "' and " +
                         "a.register_date between '" + tjrq + "' and '" + djrqEnd + "'";
            if (jgid != 49)
            {
                sql += " and a.jgid=" + jgid;
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.DTO.JglrChaLou>(sql).ToList();
            }
        }

        public List<ExamGroupDoctorMatintainModel> GetAllList(string id, string stauts)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string param = "";
                if (id != "")
                {
                    param = " and id =@id";
                }
                if (stauts != "")
                {
                    param += " and status=@stauts";
                }
                return conn.Query<ExamGroupDoctorMatintainModel>("select * from exam_group_doctor_matintain where 1=1" + param, new { @id = id, @stauts = stauts }).ToList();
            }
        }

        public int getCount(string key)
        {
            string sql = "select count(1) from dbo.exam_group_doctor_matintain where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and exam_group like @exam_group";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { exam_group = "%" + key + "%" });
            }
        }

        public List<ExamGroupDoctorMatintainModel> getList(int page, int pagesize, string key)
        {
            //throw new NotImplementedException();
            string sql = "select *,row_number() over(order by id) as rownum from exam_group_doctor_matintain where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and exam_group_maintain_id = @exam_group_maintain_id";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.ExamGroupDoctorMatintainModel>(sql, new { exam_group_maintain_id = key }).ToList();
            }
        }

        public object save(ExamGroupDoctorMatintainModel cpm, ERPUser user)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                //throw new NotImplementedException();
                cpm.creator = user.TrueName;
                cpm.create_time = DateTime.Now.ToString("yyyy-MM-dd");
                string sql = "";
                sql = "select count(1) from exam_group_doctor_matintain where exam_group_maintain_id=@exam_group_maintain_id and user_name=@user_name and id!=@id";
                var hc = conn.ExecuteScalar<int>(sql, cpm);
                if(hc>0)
                {
                    return new { State = 0, Message ="不能添加重复的项目！" };
                }

            if (cpm.id == 0)
                {
                    sql = @"INSERT INTO [dbo].[exam_group_doctor_matintain]
           (
[exam_group],
[exam_group_maintain_id],
[check_doctor],
[user_name],
[collect],
[entry],
[review],
[status],
[creator],
[create_time]
)
     VALUES
           (
@exam_group,
@exam_group_maintain_id,
@check_doctor,
@user_name,
@collect,
@entry,
@review,
@status,
@creator,
@create_time
)
";
                }
                else
                {
                    sql = @"UPDATE [dbo].[exam_group_doctor_matintain]
   SET
[exam_group]=@exam_group,
[exam_group_maintain_id]=@exam_group_maintain_id,
[check_doctor]=@check_doctor,
[user_name]=@user_name,
[collect]=@collect,
[entry]=@entry,
[review]=@review,
[status]=@status,
[creator]=@creator,
[create_time]=@create_time

 WHERE id=@id
";
                }

                try
                {
                    int c = conn.Execute(sql, cpm);
                    if (c > 0)
                    {
                        return new { State = 1, Message = "保存成功！" };
                    }
                    else
                    {
                        return new { State = 0, Message = "操作失败，请联系管理员！" };
                    }
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public List<string> getDoctor()
        {
            string sql = "select distinct check_doctor from exam_group_doctor_matintain";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<string>(sql).ToList();
            }
        }

        public object del(string id)
        {
            string sql = @"delete exam_group_doctor_matintain where id=@id";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql, new { id = id });
                    if (c > 0)
                    {
                        return new { State = 1, Message = "保存成功！" };
                    }
                    else
                    {
                        return new { State = 0, Message = "操作失败，请联系管理员！" };
                    }
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }
    }
}