using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Dapper;
using dccdc.Models;

namespace dccdc.DAL
{
    public class xgDal
    {
        //var conn = CommHelper.GetSqlConnection()


        public xg_yyxx getlastxgydd(string openid)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<xg_yyxx>("select top 1 * from xg_yyxx where openid=@openid and djType='my' and(djzt=0  or( djsj>=@djsjb and djsj<@djsje)) order by id desc", new { openid, djsjb = DateTime.Now.Date, djsje = DateTime.Now.Date.AddDays(1) }).FirstOrDefault();
            }
        }

        public bool checkjryy(xg_yyxx yyxx)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select count(1) from xg_yyxx where sfzh=@sfzh and yyrq=@yyrq";
                return conn.ExecuteScalar<int>(sql, yyxx) > 0;
            }
        }

        public void savedj(xg_yyxx yyxx, string scks)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = @"INSERT INTO [xg_yyxx]
           ([xm]
           ,[sfzh]
           ,[sjh]
           ,[yysj]
           ,[djzt]
           ,[xlh]
           ,[openid]
           ,[yyrq]
           ,[native_province]
           ,[native_city]
           ,[native_area]
           ,[native_jd]
           ,[sszj]
           ,[jccs]
           ,[native_detailed],djType,rylx,cydwmc


)
     VALUES
           (@xm
           ,@sfzh
           ,@sjh
           ,@yysj
           ,@djzt
           ,@xlh
           ,@openid
           ,@yyrq
           ,@native_province
           ,@native_city
           ,@native_area
           ,@native_jd
           ,@sszj
           ,@jccs
           ,@native_detailed,@djType,@rylx,@cydwmc) select SCOPE_IDENTITY() 
";
                int id = conn.ExecuteScalar<int>(sql, yyxx);
                string[] mxids = scks.Trim(',').Split(',');
                foreach (var s in mxids)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        sql = "insert into xg_sqmx(yyid,xmid) values(@yyid,@xmid)";
                        conn.Execute(sql, new { yyid = id, xmid = s });
                    }
                }
            }
        }

        public List<xg_yyxx> getjryysjh(string sjh)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select  * from xg_yyxx where sjh=@sfzh and yyrq=@yyrq and djzt=0 order by id desc";
                var yys = conn.Query<xg_yyxx>(sql, new { sfzh = sjh, yyrq = DateTime.Now.Date }).ToList();
                sql = "select * from xg_sqmx where yyid=@yyid";
                yys.ForEach(yy =>
                {
                    List<xg_yymx> yymxs = conn.Query<xg_yymx>(sql, new { yyid = yy.id }).ToList();
                    yy.yymx = yymxs.ToArray();
                });

                return yys;
            }
        }

        public int getyyCount(string xm, string yyrq)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select count(1) from xg_yyxx where 1=1";
                if (!string.IsNullOrEmpty(xm))
                {
                    sql += " and xm like @xm";
                }
                if (!string.IsNullOrEmpty(yyrq))
                {
                    DateTime dt;
                    if (DateTime.TryParse(yyrq, out dt))
                    {
                        sql += " and yyrq=@yyrq";
                    }
                }
                return conn.ExecuteScalar<int>(sql, new { xm = "%" + xm + "%", yyrq = yyrq });
            }
        }

        public object getyyList(string xm, string yyrq, int page, int pagesize)
        {
            string sql = "select *, row_number() over(order by id desc) as rownum  from xg_yyxx where 1=1";
            if (!string.IsNullOrEmpty(xm))
            {
                sql += " and xm like @xm";
            }
            if (!string.IsNullOrEmpty(yyrq))
            {
                DateTime dt;
                if (DateTime.TryParse(yyrq, out dt))
                {
                    sql += " and yyrq=@yyrq";
                }
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                sql = "select * from (" + sql + ") t where t.rownum > (" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;

                return conn.Query<Models.xg_yyxx>(sql, new { xm = "%" + xm + "%", yyrq = yyrq }).ToList();

            }
        }

        public xg_yyxx gethavenodjyy(string openid)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<xg_yyxx>("select top 1 * from xg_yyxx where openid=@openid and djType='my' and (djzt=0 or (djsj>=@djsjb and djsj<@djsje)) order by id desc", new { openid , djsjb = DateTime.Now.Date, djsje = DateTime.Now.Date.AddDays(1) }).FirstOrDefault();
            }
        }

        public int getdjCount(string xm, string yyrq,string jd,string rylx)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select count(1) from xg_djxx a join xg_yyxx b on a.yyid=b.id where 1=1";
                if (!string.IsNullOrEmpty(xm))
                {
                    sql += " and a.xm like @xm";
                }
                if (!string.IsNullOrEmpty(jd))
                {
                    sql += " and b.sszj = @jd";
                }
                if (!string.IsNullOrEmpty(rylx))
                {
                    sql += " and b.rylx = @rylx";
                }
                if (!string.IsNullOrEmpty(yyrq))
                {
                    DateTime dt;
                    if (DateTime.TryParse(yyrq, out dt))
                    {
                        sql += " and a.jcrq=@yyrq";
                    }
                }
                return conn.ExecuteScalar<int>(sql, new { xm = "%" + xm + "%", yyrq = yyrq ,jd, rylx });
            }
        }

        public object getdjList(string xm, string cygtm, string yyrq, int page, int pagesize, string jd, string rylx)
        {
            string sql = "select a.*, row_number() over(order by a.id desc) as rownum  from xg_djxx a left join xg_yyxx b on a.yyid=b.id where 1=1";
            if (!string.IsNullOrEmpty(xm))
            {
                sql += " and a.xm like @xm";
            }
            if (!string.IsNullOrEmpty(cygtm))
            {
                sql += " and a.xlh = @cygtm";
            }
            if (!string.IsNullOrEmpty(jd))
            {
                sql += " and b.sszj = @jd";
            }
            if (!string.IsNullOrEmpty(rylx))
            {
                sql += " and b.rylx = @rylx";
            }
            if (!string.IsNullOrEmpty(yyrq))
            {
                DateTime dt;
                if (DateTime.TryParse(yyrq, out dt))
                {
                    sql += " and a.jcrq=@yyrq";
                }
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;

                return conn.Query<Models.xgDjxx>(sql, new { xm = "%" + xm + "%", cygtm = cygtm, yyrq = yyrq, jd, rylx }).ToList();

            }
        }

        public xg_yyxx getjryyid(string yyid)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select  * from xg_yyxx where id=@id";
                var yy = conn.Query<xg_yyxx>(sql, new { id = yyid}).FirstOrDefault();
                if (yy == null)
                    return yy;
                sql = "select * from xg_sqmx where yyid=@yyid";
                List<xg_yymx> yymxs = conn.Query<xg_yymx>(sql, new { yyid = yy.id }).ToList();
                yy.yymx = yymxs.ToArray();
                return yy;
            }
        }

        public string getczyname(int czyid)
        {
            using (var conn = CommHelper.GetSqlConnection("OADB"))
            {
                string sql = "select TrueName  from erpuser where id=@id";

                return conn.Query<string>(sql, new {id=czyid }).FirstOrDefault();
            }
        }

        public int gettmyycs(string tm)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
               string  sql = "select count(1) from xg_djxx where xlh=@xlh";

                return conn.ExecuteScalar<int>(sql, new { xlh = tm });

            }
        }

        public xg_ydytm getydytm(string tm)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
               string sql = "select * from xg_ydytm where barcode=@barcode";
               return conn.Query<xg_ydytm>(sql, new { barcode = tm }).FirstOrDefault();
            }
        }

        public bool getbgbysfzsjh(string mobile, string iDCard)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select count(1) from xg_djxx where sfzh=@sfzh and sjh=@sjh and bgzt=1 and bgrq>=@bgrq";
                return conn.ExecuteScalar<int>(sql, new { sfzh = iDCard, sjh = mobile,bgrq=DateTime.Now.Date.AddDays(-7) }) > 0;
            }
        }

        public void gxtmsysj(xg_ydytm vtm)
        {
            using(var conn = CommHelper.GetSqlConnection()) { 
            ///throw new NotImplementedException();
            string sql = "update xg_ydytm set bdsj=@bdsj where barcode=@barcode";
                conn.Execute(sql, vtm);
            }

        }

        public xg_yyxx getjryy(string sfzh)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select top 1 * from xg_yyxx where sfzh=@sfzh and yyrq=@yyrq order by id desc";
                var yy = conn.Query<xg_yyxx>(sql, new { sfzh = sfzh, yyrq = DateTime.Now.Date }).FirstOrDefault();
                if (yy == null)
                    return yy;
                sql = "select * from xg_sqmx where yyid=@yyid";
                List<xg_yymx> yymxs = conn.Query<xg_yymx>(sql, new { yyid = yy.id }).ToList();
                yy.yymx = yymxs.ToArray();
                return yy;
            }
        }

        public xg_xm getyp(string yp)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from xg_xm where id=@yp ";
                return conn.Query<xg_xm>(sql, new { yp = yp }).FirstOrDefault();
            }
        }

        public int savedjxx(xgDjxx djxx, string[] jcyps)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = @"
INSERT INTO [xg_djxx]
           ([xm]
           ,[sfzh]
           ,[jcrq]
           ,[bgbh]
           ,[ypmc]
           ,[jcxm]
           ,[yyid]
           ,[bgzt]
           ,[sjh]
           ,[djid]
           ,[djxm]
           ,[djsj],guid,xlh)
     VALUES
           (@xm
           ,@sfzh
           ,@jcrq
           ,@bgbh
           ,@ypmc
           ,@jcxm
           ,@yyid
           ,@bgzt
           ,@sjh
           ,@djid
           ,@djxm
           ,@djsj,NEWID(),@xlh)
select SCOPE_IDENTITY()
";
                int id = conn.ExecuteScalar<int>(sql, djxx);
                foreach (var s in jcyps)
                {
                    sql = "insert into xg_djmx(dj_id,xmid)values(@dj_id,@xmid)";
                    conn.Execute(sql, new { dj_id = id, xmid = s });
                }
                return id;
            }
        }

        public void reportid(string id, int uid)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "update xg_djxx set bgzt=1,bgrq='" + DateTime.Now.ToString("yyyy-MM-dd") + "',jyid=" + uid + " where id=" + id + " and bgzt=0";
                conn.Execute(sql);
                sql = "update xg_djmx set jg='阴性' where dj_id=" + id;
                conn.Execute(sql);

            }
        }

        public void deletedj(string id)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                conn.Execute("delete from xg_djmx where dj_id=@id", new { id });
                conn.Execute("delete from xg_djxx where id=@id", new { id });
            }
        }

        public object getxgtm(string id)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query("select c.bgbh tm,b.bmxm yp,c.xm from xg_djmx a join xg_xm b on a.xmid=b.id join xg_djxx c on c.id=a.dj_id where a.dj_id=@id", new { id }).ToList();
            }
        }

        public bool jrydj(string sfzh)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>("select count(1) from xg_djxx where sfzh=@sfzh and jcrq=@jcrq", new { sfzh, jcrq = DateTime.Now.ToString("yyyy-MM-dd") }) > 0;
            }
        }

        public void gxyyxx(xg_yyxx yyxx)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                /*
                 * yyxx.djzt = 1;
                yyxx.djrid = user.ID;
                yyxx.djrxm = user.TrueName;
                yyxx.djsj = DateTime.Now;
                 */
                string sql = "update xg_yyxx set djzt=@djzt,djrid=@djrid,djrxm=@djrxm,djsj=@djsj where id=@id";
                conn.Execute(sql, yyxx);
            }
        }

        public xg_yyxx getjryyxlh(string xlh)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select top 1 * from xg_yyxx where xlh=@xlh and yyrq=@yyrq order by id desc";
                var yy = conn.Query<xg_yyxx>(sql, new { xlh = xlh, yyrq = DateTime.Now.Date }).FirstOrDefault();
                if (yy == null)
                    return yy;
                sql = "select * from xg_sqmx where yyid=@yyid";
                List<xg_yymx> yymxs = conn.Query<xg_yymx>(sql, new { yyid = yy.id }).ToList();
                yy.yymx = yymxs.ToArray();
                return yy;
            }
        }

        public List<xgDjxx> getdjListbysfz(string v)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                /*
                 * yyxx.djzt = 1;
                yyxx.djrid = user.ID;
                yyxx.djrxm = user.TrueName;
                yyxx.djsj = DateTime.Now;
                 */
                string sql = "select * from xg_djxx where sfzh=@sfzh order by id desc";
                return conn.Query<xgDjxx>(sql, new { sfzh = v }).ToList();
            }
        }

        public xgDjxx getJYSQD(int v, string guid)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                /*
                 * yyxx.djzt = 1;
                yyxx.djrid = user.ID;
                yyxx.djrxm = user.TrueName;
                yyxx.djsj = DateTime.Now;
                 */
                string sql = "select * from xg_djxx where id=@sfzh and guid=@guid order by id desc";
                return conn.Query<xgDjxx>(sql, new { sfzh = v, guid }).FirstOrDefault();
            }
        }

        public string getjg(int id, int v)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                /*
                 * yyxx.djzt = 1;
                yyxx.djrid = user.ID;
                yyxx.djrxm = user.TrueName;
                yyxx.djsj = DateTime.Now;
                 */
                string sql = "select * from xg_djmx where dj_id=@id and xmid=@xmid";
                var mx = conn.Query<xg_djxm>(sql, new { id, xmid = v }).FirstOrDefault();
                if (mx == null)
                {
                    return "未检测";
                }
                else
                {
                    return mx.jg;

                }
            }
        }

        public bool addydytm(string barcode)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                /*
                 * yyxx.djzt = 1;
                yyxx.djrid = user.ID;
                yyxx.djrxm = user.TrueName;
                yyxx.djsj = DateTime.Now;
                 */
                //string sql = "select * from xg_djxx where id=@sfzh and guid=@guid order by id desc";
                //return conn.Query<xgDjxx>(sql, new { sfzh = v, guid }).FirstOrDefault();
                string sql = "select * from xg_ydytm where barcode=@barcode";
                var ydy = conn.Query<Models.xg_ydytm>(sql, new { barcode }).FirstOrDefault();
                if (ydy == null)
                {
                    sql = "insert into xg_ydytm(barcode,printtime) values(@barcode,getdate())";
                    conn.Execute(sql, new { barcode });
                    return true;
                }
                else
                    return false;
            }
        }

        public int getkytmcount()
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select count(1) from xg_ydytm where lysj is null";
                return conn.ExecuteScalar<int>(sql);
            }
        }

        public string getmin()
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select top 1 barcode  from xg_ydytm where lysj is null order by barcode";
                return conn.ExecuteScalar<string>(sql);
            }
        }

        public string getmax()
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select top 1 barcode  from xg_ydytm where lysj is null order by barcode desc";
                return conn.ExecuteScalar<string>(sql);
            }
        }

        public object getczys(string key)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection("OADB"))
            {
                string sql = "select id,TrueName name from erpuser where TrueName like @tn and ifLogin='是'";

                return conn.Query(sql, new { tn = "%" + key + "%" });
            }
        }

        public bool lytm(int iD, string v, string lyrid)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "update xg_ydytm set czyid=@czyid,lysj=getdate(),fpr=@fpr where barcode=@barcode and lysj is null";
                int c = conn.Execute(sql, new { fpr = iD, barcode = v, czyid = lyrid });
                if (c > 0)
                    return true;
                else
                    return false;
            }
        }

        public object tmlyjm(int page, int pagesize, string s_lyr, string s_lysj, out int c)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = @"select a.barcode, b.TrueName 'lyr_name' ,c.TrueName 'ffr_name',
a.lysj,a.bdsj
from xg_ydytm a join oa..erpuser b on a.czyid=b.id
join oa..erpuser c on a.fpr=c.id where 1=1";

                string sqlc = @"select count(1) from xg_ydytm a join oa..erpuser b on a.czyid=b.id
join oa..erpuser c on a.fpr = c.id where 1 = 1";
                string wheresql = "";
                if (!string.IsNullOrEmpty(s_lyr))
                {
                    wheresql += " and a.czyid=@czyid";

                }
                if(!string.IsNullOrEmpty(s_lysj))
                {
                    DateTime dt;
                    if(DateTime.TryParse(s_lysj,out dt))
                    {
                        wheresql += " and convert(varchar(10), a.lysj,121)=@lysj";
                    }
                }
                sqlc += wheresql;
                sql += wheresql;
                sql += " order by a.barcode  offset (" + page + "-1)*" + pagesize + " row fetch next " + pagesize + " row only";
                c = conn.ExecuteScalar<int>(sqlc, new { czyid = s_lyr, lysj = s_lysj });
                return conn.Query(sql, new { czyid = s_lyr, lysj = s_lysj });
            }
        }

        public xgDjxx getdjxxbyyyid(int id)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from xg_djxx where yyid=@yyid";

                return conn.Query<xgDjxx>(sql, new { yyid = id }).FirstOrDefault();
            }
        }

        public List<xg_yyxx> getjryyother(string v)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from xg_yyxx where openid=@openid and (yyrq=@yyrq or( djsj>=@djsjb and djsj<@djsje))  and djtype='other' ";

                return conn.Query<xg_yyxx>(sql, new { openid = v,yyrq=DateTime.Now.Date,djsjb= DateTime.Now.Date,djsje= DateTime.Now.Date.AddDays(1) }).ToList();
            }
        }
        //export
        public DataTable getdjList(string xm, string cygtm, string yyrq, string jd, string rylx)
        {
            //throw new NotImplementedException();
            string sql = @"select  row_number() over(order by a.id desc) as 行号,a.xlh 采样管条码,a.xm 姓名,a.sfzh 身份证号,a.sjh 手机号 , a.jcrq 采样日期,
c.flmc 人员类型,
b.sszj 所属镇街, b.cydwmc 单位名称
from xg_djxx a left join xg_yyxx b on a.yyid = b.id
left join xg_ryfl c on b.rylx=c.id
where 1 =1";
            if (!string.IsNullOrEmpty(xm))
            {
                sql += " and a.xm like @xm";
            }
            if (!string.IsNullOrEmpty(cygtm))
            {
                sql += " and a.xlh = @cygtm";
            }
            if (!string.IsNullOrEmpty(jd))
            {
                sql += " and b.sszj = @jd";
            }
            if (!string.IsNullOrEmpty(rylx))
            {
                sql += " and b.rylx = @rylx";
            }
            if (!string.IsNullOrEmpty(yyrq))
            {
                DateTime dt;
                if (DateTime.TryParse(yyrq, out dt))
                {
                    sql += " and a.jcrq=@yyrq";
                }
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                //sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;

                var dr = conn.ExecuteReader(sql, new { xm = "%" + xm + "%", cygtm = cygtm, yyrq = yyrq, jd, rylx });

                DataTable dt = new DataTable();
                dt.Load(dr);
                return dt;
            }
        }

        public List<xg_rylx> getrylxs()
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from xg_ryfl";
                return conn.Query<xg_rylx>(sql).ToList();
            }
            }
    }
}