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 OccupationDataImportObjectDal
    {
        public object GetList(string name, string sitename,string idno)
        {

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string where = "";
                if (!string.IsNullOrEmpty(idno))
                {
                    where += " and a.identity_card =@idno ";
                }
                    string sql = "select a.*, b.jobs_state from occupation_data_import_object a left join jobs_state_maintain b on a.work_state= b.id where process='已完成导入' and a.name like @name and a.enterprise like @enterprise"+where+ " order by  a.id desc, a.import_date desc,a.enterprise, b.jobs_state, a.dept, a.sex asc";
                

                return conn.Query(
                    sql,
                    new { name = "%" + name + "%", enterprise = "%" + sitename + "%",idno=idno }).ToList();
            }
        }

        /// <summary>
        /// 根据姓名/身份证/企业体检方案/通用体检方案查询职业导入数据
        /// 2023-10-27 xulu
        /// </summary>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <param name="name"></param>
        /// <param name="idcard"></param>
        /// <param name="tjfn"></param>
        /// <param name="tytjfn"></param>
        /// <returns></returns>
        public List<Models.OccupationDataImportObjectModel> getList(int page, int pagesize, string name, string idcard, string tjfn, string tytjfn, string utilName,string startdate,string enddate)
        {

            string where = "";
            //2023-11-23 xulu 添加筛选条件，存在重复导入情况，目前根据一定条件内筛选出的身份证号去重
            string strSql = " and identity_card not in (select distinct card_number from professionalExam_register";
            if (!string.IsNullOrEmpty(name))
            {
                where += " and name like @name";
                if(strSql.IndexOf("where")!=-1)
                {
                    strSql += " and name like @name";
                }
                else
                {
                    strSql += " where name like @name";
                }
            }
            if (!string.IsNullOrEmpty(idcard))
            {
                where += " and identity_card like @idcard";
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and identity_card like @idcard";
                }
                else
                {
                    strSql += " where identity_card like @idcard";
                }
            }
            if (!string.IsNullOrEmpty(tjfn))
            {
                where += "  and util_scheme_id =" + Convert.ToInt32(tjfn);
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and util_scheme_id =" + Convert.ToInt32(tjfn);
                }
                else
                {
                    strSql += " where util_scheme_id =" + Convert.ToInt32(tjfn);
                }
            }
            else if (!string.IsNullOrEmpty(tytjfn))
            {
                where += "  and common_scheme_id =" + Convert.ToInt32(tytjfn);
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and common_scheme_id =" + Convert.ToInt32(tytjfn);
                }
                else
                {
                    strSql += " where common_scheme_id =" + Convert.ToInt32(tytjfn);
                }
            }
            if (!string.IsNullOrEmpty(utilName))
            {
                where += " and enterprise ='" + utilName + "'";
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and enterprise ='" + utilName + "'";
                }
                else
                {
                    strSql += " where enterprise ='" + utilName + "'";
                }
            }
            if (!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate))
            {
                //2023-11-23 xulu 增加时间段查询
                where += " and import_date between '" + startdate + "' and '" + enddate + "'";
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and import_date between '" + startdate + "' and '" + enddate + "'";
                }
                else
                {
                    strSql += " where import_date between '" + startdate + "' and '" + enddate + "'";
                }
            }

            string sql = "select *,row_number() over(order by id) as rownum  from occupation_data_import_object where process='已完成导入'" + where ;
            sql = sql + strSql + ")";
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.OccupationDataImportObjectModel>(sql, new { name = "%" + name + "%", idcard = "%" + idcard + "%" }).ToList();
            }
        }

        /// <summary>
        /// 根据条件获取职业导入列表
        /// 2023-11-07 xulu
        /// </summary>
        /// <param name="name"></param>
        /// <param name="idcard"></param>
        /// <param name="tjfn"></param>
        /// <param name="tytjfn"></param>
        /// <param name="utilName"></param>
        /// <returns></returns>
        public DataTable getList(string name, string idcard, string tjfn, string tytjfn, string utilName,string startdate,string enddate)
        {

            string where = "";
            //2023-11-23 xulu 添加筛选条件，存在重复导入情况，目前根据一定条件内筛选出的身份证号去重
            string strSql = " and identity_card not in (select distinct card_number from professionalExam_register";
            if (!string.IsNullOrEmpty(name))
            {
                where += " and name like @name";
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and name like @name";
                }
                else
                {
                    strSql += " where name like @name";
                }
            }
            if (!string.IsNullOrEmpty(idcard))
            {
                where += " and identity_card like @idcard";
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and identity_card like @idcard";
                }
                else
                {
                    strSql += " where identity_card like @idcard";
                }
            }
            if (!string.IsNullOrEmpty(tjfn))
            {
                where += "  and util_scheme_id =" + Convert.ToInt32(tjfn);
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and util_scheme_id =" + Convert.ToInt32(tjfn);
                }
                else
                {
                    strSql += " where util_scheme_id =" + Convert.ToInt32(tjfn);
                }
            }
            else if (!string.IsNullOrEmpty(tytjfn))
            {
                where += "  and common_scheme_id =" + Convert.ToInt32(tytjfn);
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and common_scheme_id =" + Convert.ToInt32(tytjfn);
                }
                else
                {
                    strSql += " where common_scheme_id =" + Convert.ToInt32(tytjfn);
                }
            }
            if (!string.IsNullOrEmpty(utilName))
            {
                where += " and enterprise ='" + utilName + "'";
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and enterprise ='" + utilName + "'";
                }
                else
                {
                    strSql += " where enterprise ='" + utilName + "'";
                }
            }
            if (!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate))
            {
                //2023-11-23 xulu 增加时间段查询
                where += " and import_date between '" + startdate + "' and '" + enddate + "'";
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and import_date between '" + startdate + "' and '" + enddate + "'";
                }
                else
                {
                    strSql += " where import_date between '" + startdate + "' and '" + enddate + "'";
                }
            }

            string sql = "select name as person_name,sex,identity_card as card_number,telephone as phone,enterprise as util_name from occupation_data_import_object where process='已完成导入'" + where;
            sql = sql + strSql + ")";
            //sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                var dr = conn.ExecuteReader(sql, new { name = "%" + name + "%", idcard = "%" + idcard + "%" });

                DataTable dt = new DataTable();
                dt.Load(dr);
                return dt;
                //return conn.Query<Models.OccupationDataImportObjectModel>(sql, new { name = "%" + name + "%", idcard = "%" + idcard + "%" }).ToList();
            }
        }
        public int getListCount(string name, string idcard, string tjfn, string tytjfn, string utilName,string startdate,string enddate)
        {

            string where = "";
            //2023-11-23 xulu 添加筛选条件，存在重复导入情况，目前根据一定条件内筛选出的身份证号去重
            string strSql = " and identity_card not in (select distinct card_number from professionalExam_register";
            if (!string.IsNullOrEmpty(name))
            {
                where += " and name like @name";
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and name like @name";
                }
                else
                {
                    strSql += " where name like @name";
                }
            }
            if (!string.IsNullOrEmpty(idcard))
            {
                where += " and identity_card like @idcard";
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and identity_card like @idcard";
                }
                else
                {
                    strSql += " where identity_card like @idcard";
                }
            }
            if (!string.IsNullOrEmpty(tjfn))
            {
                where += "  and util_scheme_id =" + Convert.ToInt32(tjfn);
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and util_scheme_id =" + Convert.ToInt32(tjfn);
                }
                else
                {
                    strSql += " where util_scheme_id =" + Convert.ToInt32(tjfn);
                }
            }
            else if (!string.IsNullOrEmpty(tytjfn))
            {
                where += "  and common_scheme_id =" + Convert.ToInt32(tytjfn);
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and common_scheme_id =" + Convert.ToInt32(tytjfn);
                }
                else
                {
                    strSql += " where common_scheme_id =" + Convert.ToInt32(tytjfn);
                }
            }
            if (!string.IsNullOrEmpty(utilName))
            {
                where += " and enterprise ='" + utilName + "'";
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and enterprise ='" + utilName + "'";
                }
                else
                {
                    strSql += " where enterprise ='" + utilName + "'";
                }
            }
            if (!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate))
            {
                //2023-11-23 xulu 增加时间段查询
                where += " and import_date between '" + startdate + "' and '" + enddate + "'";
                if (strSql.IndexOf("where") != -1)
                {
                    strSql += " and import_date between '" + startdate + "' and '" + enddate + "'";
                }
                else
                {
                    strSql += " where import_date between '" + startdate + "' and '" + enddate + "'";
                }
            }

            string sql = "select count(1) from occupation_data_import_object where process='已完成导入'" + where ;
            sql = sql + strSql + ")";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { name = "%" + name + "%", idcard = "%" + idcard + "%" });
            }
        }

        /// <summary>
        /// 获取一条导入的记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public OccupationDataImportObjectModel GetModel(string id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<OccupationDataImportObjectModel>(
                    "select * from occupation_data_import_object where id=@id ",
                    new { id = id }).First();
            }
        }

        public object getLsrkry(string idno)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(
                    "select a.*, b.jobs_state from occupation_data_import_object a left join jobs_state_maintain b on a.work_state= b.id where process='已完成导入' and identity_card =@idno order by a.enterprise, b.jobs_state, a.dept, a.sex, a.import_date ",
                    new { idno= idno }).ToList();
            }
        }

        /// <summary>
        /// 登记成功后修改为已登记状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Updateprocess(string id)
        {
            string sql = "update occupation_data_import_object set process ='已登记' where id = @id";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Execute(sql, new { @id = id }) == 1 ? true : false;
            }
        }

        public bool Add(OccupationDataImportObjectModel model)
        {
            string sql = @"INSERT INTO [dbo].[occupation_data_import_object]
           ([enterprise],
[enterprise_id],
[name],
[sex],
[born_date],
[identity_card],
[is_merry],
[telephone],
[dept],
[work_num],
[work_type],
[nation],
[nationality],
[factor],
[work_age_year],
[work_age_month],
[factor_age_year],
[factor_age_month],
[comment],
[person_type],
[check_type],
[radiological_code],
[check_unit],
[train_unit],
[social_card],
[check_no],
[personal_no],
[start_end_date],
[process],
[import_date],
[scheme_name],
[work_state],
[data_import_cache_id],
[physical_way],
[hazard_names],
[hazard_alias],
[hazard_ids],
[hazards_type],
[hazards_type_codes],
[check_item_ids],
[exam_type],
[exam_type_maintain_id],
[util_scheme_id],
[common_scheme_id],
[charge])
     VALUES
           (@enterprise,
@enterprise_id,
@name,
@sex,
@born_date,
@identity_card,
@is_merry,
@telephone,
@dept,
@work_num,
@work_type,
@nation,
@nationality,
@factor,
@work_age_year,
@work_age_month,
@factor_age_year,
@factor_age_month,
@comment,
@person_type,
@check_type,
@radiological_code,
@check_unit,
@train_unit,
@social_card,
@check_no,
@personal_no,
@start_end_date,
@process,
@import_date,
@scheme_name,
@work_state,
@data_import_cache_id,
@physical_way,
@hazard_names,
@hazard_alias,
@hazard_ids,
@hazards_type,
@hazards_type_codes,
@check_item_ids,
@exam_type,
@exam_type_maintain_id,
@util_scheme_id,
@common_scheme_id,
@charge)";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return (conn.Execute(sql, model) != 0 ? true : false);
            }
        }

        /// <summary>
        /// 更新职业导入的体检项目
        /// 2023-10-30 xulu
        /// </summary>
        /// <param name="id"></param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool updateItems(string id, string ids)
        {
            string sql = "update occupation_data_import_object set check_item_ids ='"+ ids + "' where id ="+ id;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Execute(sql) == 1 ? true : false;
            }
        }
    }
}