using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using dccdc.Models.WeiXin;
using Dapper;
using dccdc.Models;
using System.Data;

namespace dccdc.DAL
{
    public class weixinDal
    {
        public List<menu> getMenu()
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from menu";
                return conn.Query<menu>(sql).ToList();
            }
        }

        public List<menu> getSJCD()
        {

            //var dt = DBUtility.DbHelperSQL.Query(sql).Tables[0];
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select id,name from menu where pid=0";
                return conn.Query<menu>(sql).ToList();
            }
        }

        public object saveCD(menu m)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                //throw new NotImplementedException();
                string sql = "";
                if (m.id == 0)
                {
                    sql = "insert into menu(name,pid,cdlx,url,[key])values(@name,@pid,@cdlx,@url,@key)";
                }
                else
                {
                    sql = "update menu set name=@name,pid=@pid,cdlx=@cdlx,url=@url,[key]=@key where id=@id";
                }
                conn.Execute(sql, m);
                return new
                {
                    State = 1,
                    Message = "成功"
                };
            }
        }



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

        

        public List<menu> getSJCDYM()
        {

            //var dt = DBUtility.DbHelperSQL.Query(sql).Tables[0];
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select id,name from ym_menu where pid=0";
                return conn.Query<menu>(sql).ToList();
            }
        }

        

        public object saveCDYM(menu m)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                //throw new NotImplementedException();
                string sql = "";
                if (m.id == 0)
                {
                    sql = "insert into ym_menu(name,pid,cdlx,url,[key])values(@name,@pid,@cdlx,@url,@key)";
                }
                else
                {
                    sql = "update ym_menu set name=@name,pid=@pid,cdlx=@cdlx,url=@url,[key]=@key where id=@id";
                }
                conn.Execute(sql, m);
                return new
                {
                    State = 1,
                    Message = "成功"
                };
            }
        }

        



        public int remarkGZ(string openid, string bz)
        {
            //throw new NotImplementedException();
            string sql = "update open_user set remark=@bz where openid=@openid";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Execute(sql, new { openid = openid, bz = bz });
            }
        }

        public int remarkGZYM(string openid, string bz)
        {
            //throw new NotImplementedException();
            string sql = "update ym_open_user set remark=@bz where openid=@openid";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Execute(sql, new { openid = openid, bz = bz });
            }
        }

        

        public openuser getopenuser(string openid)
        {
            //throw new NotImplementedException();
            string sql = "select * from open_user where openid=@openid";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<openuser>(sql, new { openid }).FirstOrDefault();
            }
        }

        public object sfkyyqy(string qy)
        {
            //throw new NotImplementedException();
            var qylist = new special_companyDal().getKYList();
            bool c = true;
            foreach (var q in qylist)
            {
                if (qy.Contains(q.company_name))
                {
                    c = false;
                    break;
                }
            }
            if (c)
            {
                return new { State = 1 };
            }
            else
            {
                return new { State = 0 };
            }

        }

        public object xgyyrq(string id, string yyrq,int userid)
        {
            //throw new NotImplementedException();
            string sql = "select count(1) from MedicalAppointment where Adate=@yyrq";
            string mryyrs = new DAL.Common().getParm_Value("mryyrs", "100", "每日最多预约人数");
            int imryyrs = 0;
            int.TryParse(mryyrs, out imryyrs);
            bool bkyy = true;
            int[] kxyry =  { 191, 32 };
            if(kxyry.Contains(userid))
            {
                bkyy = false;
            }
            //if()
            using (var conn = CommHelper.GetSqlConnection())
            {
                int c = conn.ExecuteScalar<int>(sql, new { yyrq = yyrq });
                if (bkyy&&c >= imryyrs)
                {
                    return new { State = 0, Message = "超过每日最高预约人数！" };
                }
                else
                {
                    sql = "update MedicalAppointment set Adate=@adate where id=@id";
                    c = conn.Execute(sql, new { adate = yyrq, id = id });
                    if (c > 0)
                    {
                        return new { State = 1, Message = "修改预约日期成功！" };
                    }
                    else
                    {
                        return new { State = 0, Message = "操作失败！" };
                    }
                }
            }
        }

        public object checktjjf(string tm, string lx)
        {
            //throw new NotImplementedException();
            if (tm.IndexOf(",") > -1)
            {
                tm = tm.Split(',')[1];
            }
            if (lx == "tj")
            {
                lx = "体检收费";
            }
            else if (lx == "ym")
            {
                lx = "疫苗收费";
            }
            else
            {

            }
            string sql = "select * from charge where [register_num]=@tm and [type]='" + lx + "' order by id desc";
            using (var conn = CommHelper.GetSqlConnection())
            {
                var list = conn.Query<Models.chargeModel>(sql, new { tm = tm }).ToList();
                if (list.Count > 0)
                {
                    if (list[0].status == "已缴费")
                    {
                        return new { State = 0, Message = "已经缴费不能重复缴费" };
                    }
                    else
                    {
                        return new { State = 1, Message = list[0].id };
                    }
                }
                else
                {
                    return new { State = 0, Message = "没有查到需要缴费的信息" };
                }
            }

        }

        public List<openuser> getGZList(string nc, string bz, int page, int pagesize)
        {
            //throw new NotImplementedException();
            string sql = "select *,row_number() over(order by id) as rownum from open_user where 1=1";
            if (!string.IsNullOrEmpty(nc))
            {
                sql += " and nickname like @nc";
            }
            if (!string.IsNullOrEmpty(bz))
            {
                sql += " and remark like @bz";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.WeiXin.openuser>(sql, new { nc = "%" + nc + "%", bz = "%" + bz + "%" }).ToList();
            }

        }

        public int getGZCount(string nc, string bz)
        {
            string sql = "select count(1) from open_user where 1=1";
            if (!string.IsNullOrEmpty(nc))
            {
                sql += " and nickname like @nc";
            }
            if (!string.IsNullOrEmpty(bz))
            {
                sql += " and remark like @bz";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { nc = "%" + nc + "%", bz = "%" + bz + "%" });
            }
        }




        public List<openuser> getGZListYM(string nc, string bz, int page, int pagesize)
        {
            //throw new NotImplementedException();
            string sql = "select *,row_number() over(order by id) as rownum from ym_open_user where 1=1";
            if (!string.IsNullOrEmpty(nc))
            {
                sql += " and nickname like @nc";
            }
            if (!string.IsNullOrEmpty(bz))
            {
                sql += " and remark like @bz";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.WeiXin.openuser>(sql, new { nc = "%" + nc + "%", bz = "%" + bz + "%" }).ToList();
            }

        }

        

        public int getGZCountYM(string nc, string bz)
        {
            string sql = "select count(1) from ym_open_user where 1=1";
            if (!string.IsNullOrEmpty(nc))
            {
                sql += " and nickname like @nc";
            }
            if (!string.IsNullOrEmpty(bz))
            {
                sql += " and remark like @bz";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { nc = "%" + nc + "%", bz = "%" + bz + "%" });
            }
        }

        

        public void udateOpenUser(openuser ou)
        {
            //throw new NotImplementedException();
            try
            {
                //throw new NotImplementedException();
                string sql = "select count(1) from open_user where openid='" + ou.openid + "'";
                using (var conn = CommHelper.GetSqlConnection())
                {
                    var c = conn.ExecuteScalar<int>(sql);
                    if (c > 0)
                    {
                        sql = @"UPDATE [open_user]
   SET [openid] = @openid
      ,[nickname] = @nickname
      ,[sex] = @sex
      ,[city] = @city
      ,[country] = @country
      ,[province] = @province
      ,[language] = @language
      ,[headimgurl] = @headimgurl
      ,[subscribe_time] = @subscribe_time
      ,[remark] = @remark
      ,[subscribe] = @subscribe
 WHERE openid=@openid";
                    }
                    else
                    {
                        sql = @"INSERT INTO [dbo].[open_user]
           ([openid]
           ,[nickname]
           ,[sex]
           ,[city]
           ,[country]
           ,[province]
           ,[language]
           ,[headimgurl]
           ,[subscribe_time]
           ,[remark]
           ,[subscribe])
     VALUES
           (@openid
           ,@nickname
           ,@sex
           ,@city
           ,@country
           ,@province
           ,@language
           ,@headimgurl
           ,@subscribe_time
           ,@remark
           ,@subscribe)
";
                    }


                    conn.Execute(sql, ou);
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }

        }


        public void udateOpenUserYM(openuser ou)
        {
            //throw new NotImplementedException();
            try
            {
                //throw new NotImplementedException();
                string sql = "select count(1) from ym_open_user where openid='" + ou.openid + "'";
                using (var conn = CommHelper.GetSqlConnection())
                {
                    var c = conn.ExecuteScalar<int>(sql);
                    if (c > 0)
                    {
                        sql = @"UPDATE [ym_open_user]
   SET [openid] = @openid
      ,[nickname] = @nickname
      ,[sex] = @sex
      ,[city] = @city
      ,[country] = @country
      ,[province] = @province
      ,[language] = @language
      ,[headimgurl] = @headimgurl
      ,[subscribe_time] = @subscribe_time
      ,[remark] = @remark
      ,[subscribe] = @subscribe
 WHERE openid=@openid";
                    }
                    else
                    {
                        sql = @"INSERT INTO [dbo].[ym_open_user]
           ([openid]
           ,[nickname]
           ,[sex]
           ,[city]
           ,[country]
           ,[province]
           ,[language]
           ,[headimgurl]
           ,[subscribe_time]
           ,[remark]
           ,[subscribe])
     VALUES
           (@openid
           ,@nickname
           ,@sex
           ,@city
           ,@country
           ,@province
           ,@language
           ,@headimgurl
           ,@subscribe_time
           ,@remark
           ,@subscribe)
";
                    }


                    conn.Execute(sql, ou);
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }

        }

        

        public object delCD(int id)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select count(1) from menu where pid=" + id;
                if (conn.ExecuteScalar<int>(sql) != 0)
                {
                    return new { State = 0, Message = "不能删除有下级菜单的项目！" };
                }
                sql = "delete from menu where id=" + id;
                conn.Execute(sql);
                return new { State = 1, Message = "删除成功！" };


            }
        }


        public object delCDYM(int id)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select count(1) from ym_menu where pid=" + id;
                if (conn.ExecuteScalar<int>(sql) != 0)
                {
                    return new { State = 0, Message = "不能删除有下级菜单的项目！" };
                }
                sql = "delete from ym_menu where id=" + id;
                conn.Execute(sql);
                return new { State = 1, Message = "删除成功！" };


            }
        }

        public List<Models.tjjg> getyyjgs()
        {
            //throw new NotImplementedException();
            string sql = "select 0 jgid,'请选择体检机构' jgmc union all select id jgid,bumenname jgmc from erpbumen where dirid=0 and id = 49 ";
            using (var conn = CommHelper.GetSqlConnection("OADB"))
            {
                return conn.Query<Models.tjjg>(sql).ToList();
            }
        }

        public List<Models.tjjg> getyyjg()
        {
            //throw new NotImplementedException();
            string sql = "select 0 jgid,'请选择体检机构' jgmc union all select id jgid,bumenname jgmc from erpbumen where dirid=0 ";
            using (var conn = CommHelper.GetSqlConnection("OADB"))
            {
                return conn.Query< Models.tjjg>(sql).ToList();
            }
        }

        public object getjgyyrs(string jgmc, string tjlx)
        {
            //throw new NotImplementedException();
            string sql = @"select a.BuMenName jgmc,c.medical_scheme tjlx,b.kyyrs,b.id,b.jgid,b.cylx lxid from oa..ERPBuMen a join set_jg_lx_kyyrs b on a.ID=b.jgid
join medical_scheme_maintain c on c.id=b.cylx where 1=1";
            if (jgmc != "0")
            {
                sql += " and b.jgid=@jgid";
            }
            if (tjlx != "0")
            {
                sql += " and b.cylx=@cylx";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { jgid = jgmc, cylx = tjlx }).ToList();
            }
        }

        public object saveyrs(set_jg_lx_kyyrs setyyrs)
        {
            using (var db = CommHelper.GetSqlConnection())
            {
                //throw new NotImplementedException();
                string sql = "";
                if (setyyrs.id == 0)
                {
                    sql = "select count(1) from set_jg_lx_kyyrs where jgid=@jgid and cylx=@cylx";
                    var c = db.ExecuteScalar<int>(sql, setyyrs);
                    if(c>0)
                    {
                        return new { State = 0, Message = "当前机构当前体检类型已经维护，不能添加，如果要修改请单击修改操作！" };
                    }
                    sql = "insert into set_jg_lx_kyyrs values(@jgid,@cylx,@kyyrs)";
                    db.Execute(sql, setyyrs);
                    return new { State = 1, Message = "维护成功！" };

                }
                else
                {
                    sql = "select count(1) from set_jg_lx_kyyrs where jgid=@jgid and cylx=@cylx and id<>@id";
                    var c = db.ExecuteScalar<int>(sql, setyyrs);
                    if (c > 0)
                    {
                        return new { State = 0, Message = "当前机构当前体检类型已经维护，不能对修改成已经存在的数据！" };
                    }
                    sql = "update set_jg_lx_kyyrs set jgid=@jgid, cylx=@cylx,kyyrs=@kyyrs where id=@id";
                    db.Execute(sql, setyyrs);
                    return new { State = 1, Message = "维护成功！" };
                }
            }
        }

        public tjjg getJiGouByID(int jgid)
        {
            //throw new NotImplementedException();
            string sql = " select id jgid,bumenname jgmc from erpbumen where id=@id";
            using (var conn = CommHelper.GetSqlConnection("OADB"))
            {
                return conn.Query<Models.tjjg>(sql,new { id = jgid }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 根据id删除可预约人数
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object delKyywh(string id)
        {
            string sql = @"delete set_jg_lx_kyyrs 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 };
                }
            }
        }
    }
}