using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using dccdc.Models;
using Dapper;
namespace dccdc.DAL
{
    public class WeiDaoTiJianTuiSongDal
    {
        public List<InfectionTestModel> getSendTargetUser()
        {
            string sql = "select t1.* from  ";
            sql += "infection_test t1 join MedicalAppointment t2 ";
            sql += "on t1.ma_id = t2.id ";
            sql += "where t2.status = 0 ";
            sql += "and CONVERT(varchar(100), T2.ADate, 23) < '{0}' ";
            sql += "and (t1.send_msg_flg = 0 OR t1.send_msg_flg is null) ";

            sql = string.Format(sql, DateTime.Now.ToString("yyyy-MM-dd"));

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<InfectionTestModel>(sql).ToList();
            }
        }

        

        public object savetxsz(jkztxsz txsz)
        {
            //throw new NotImplementedException();
            string sql = "";
            if (txsz.id == 0)
            {
                sql = "insert into jkztxsz values(@bt,@txts,@wxtx,@dxtx)";
            }
            else
            {
                sql = "update jkztxsz set bt=@bt,txts=@txts,wxtx=@wxtx,dxtx=@dxtx where id=@id";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql, txsz);
                    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 object getdqlist(string dqsj, int page, int pagesize)
        {
            //throw new NotImplementedException();
            string sql = @"select * from (select row_number() over(order by id) num,physical_num bm,
person_name xm,card_number sfzh,phone lxdh,
convert(varchar(10),dateadd(yy,1, convert(datetime,register_date)),121) dqrq,
physical_category jkzlx,
case  when exists (select 1 from professionalexam_register a
where a.card_number=professionalExam_register.card_number and a.physical_category=professionalExam_register.physical_category
and a.register_date>@dqsj and a.result_status='合格' and a.exam_type_id=1
) then 1 else 0 end jkzzt
 from professionalExam_register
where register_date<=@dqsj
and exam_type_id=1
and result_status='合格') p where p.num>((" + page+"-1)*"+pagesize+") and p.num<=("+page+"*"+pagesize+")";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { page, pagesize, dqsj=Convert.ToDateTime(dqsj).AddYears(-1).ToString("yyyy-MM-dd") }).ToList();
            }
        }

        public int getdqcount(string dqsj)
        {
            // throw new NotImplementedException();
            string sql = @"select count(1) from professionalExam_register
where register_date<=@dqsj
and exam_type_id=1 and  result_status='合格'";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { dqsj= Convert.ToDateTime(dqsj).AddYears(-1).ToString("yyyy-MM-dd") });
            }
        }

        public void logtx(jkztxsz sz, ProfessionalExamRegisterModel dtx)
        {
            //throw new NotImplementedException();
            /*
             * id	int	Unchecked
txid	int	Checked
txsj	datetime	Checked
sfzh	varchar(50)	Checked
jkzlx	int	Checked
             */
            string sql = "insert into jkztxjl values(@txid,@txsj,@sfzh,@jkzlx)";
            using (var conn = CommHelper.GetSqlConnection())
            {
                conn.Execute(sql, new { txid = sz.id, txsj = DateTime.Now, sfzh = dtx.card_number, jkzlx = dtx.trade_type_maintain_id });
            }
        }

        public List<ProfessionalExamRegisterModel> gettxlist(jkztxsz sz)
        {
            //throw new NotImplementedException();
            string sql = @"select * from (
select row_number() over( partition by a.trade_type_maintain_id,a.card_number order by a.register_date desc) num,
a.trade_type_maintain_id, a.person_name,a.category,a.physical_num,b.openid,b.mobile,a.register_date 
,a.card_number
from professionalExam_register a
join MedicalAppointment b on a.card_number=b.idcard
and a.trade_type_maintain_id=b.cyfa ) tjsj
where tjsj.num=1 and tjsj.register_date<@register_date
and not exists(select 1 from jkztxjl where tjsj.trade_type_maintain_id=jkzlx and tjsj.card_number=sfzh
and txid=@txid
)
";
            var where = new { register_date = DateTime.Now.AddDays(sz.txts).AddYears(-1).ToString("yyyy-MM-dd"), txid = sz.id };
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.ProfessionalExamRegisterModel>(sql, where).ToList();
            }
        }

        public object deltxsz(string id)
        {
            //throw new NotImplementedException();
            string sql = "delete from jkztxsz where id=@id";
            using (var conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql, new { 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 };
                }
            }
        }
    }
}