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

namespace dccdc.DAL
{
    public class JianDuDal
    {
        public object save_dict(jd_dict dict)
        {
            // throw new NotImplementedException();
            string sql = "";
            if(dict.id==0)
            {
                sql = @"INSERT INTO [jd_dict]
           ([lb]
           ,[mc]
           ,[zt]
           ,[px])
     VALUES
           (@lb
           ,@mc
           ,@zt
           ,@px)

";
            }
            else
            {
                sql = @"UPDATE [dccdc].[dbo].[jd_dict]
   SET[lb] = @lb
      ,[mc] = @mc
      ,[zt] = @zt
      ,[px] = @px
 WHERE id=@id
";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    conn.Execute(sql, dict);
                    return new { State = 1, Message = "操作成功" };
                }
                catch(Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public int getwsxkcount(string key, string fr, string s_fzrq1, string s_fzrq2, string s_jyfw)
        {
            //throw new NotImplementedException();
            string sql = "select count(1) from xyl_wsxk where 1=1 and del=0";
            if(!string.IsNullOrEmpty(key))
            {
                sql += " and dwmc like @key";
            }
            if(!string.IsNullOrEmpty(fr))
            {
                sql += " and fr like @fr";
            }
            DateTime dt1 = DateTime.Now;
            DateTime dt2 = DateTime.Now;
            if (!string.IsNullOrEmpty(s_fzrq1))
            {
                if(DateTime.TryParse(s_fzrq1,out dt1))
                {
                    sql += " and fzsj>=@dt1";
                }
            }
            if (!string.IsNullOrEmpty(s_fzrq2))
            {
                if (DateTime.TryParse(s_fzrq2, out dt2))
                {
                    sql += " and fzsj<=@dt2";
                }
            }
            if(!string.IsNullOrEmpty(s_jyfw))
            {
                sql += " and jyfwid=@s_jyfw";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new {key="%"+key+"%",fr="%"+fr+"%", dt1,dt2, s_jyfw });
            }
        }

        public object delwsxk(string id)
        {
            //throw new NotImplementedException();
            string sql = "update xyl_wsxk set del=1 where id=@id";
            try
            {
                using (var conn = CommHelper.GetSqlConnection())
                {
                    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 };
            }
        }
        public object delfsxk(string id)
        {
            //throw new NotImplementedException();
            string sql = "update xyl_fsxkz set del=1 where id=@id";
            try
            {
                using (var conn = CommHelper.GetSqlConnection())
                {
                    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 };
            }
        }
        public int getfsxkcount(string key, string fr, string s_fzrq1, string s_fzrq2)
        {
            string sql = "select count(1) from xyl_fsxkz where 1=1 and del=0";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and dwmc like @key";
            }
            if (!string.IsNullOrEmpty(fr))
            {
                sql += " and fr like @fr";
            }
            DateTime dt1 = DateTime.Now;
            DateTime dt2 = DateTime.Now;
            if (!string.IsNullOrEmpty(s_fzrq1))
            {
                if (DateTime.TryParse(s_fzrq1, out dt1))
                {
                    sql += " and fzsj>=@dt1";
                }
            }
            if (!string.IsNullOrEmpty(s_fzrq2))
            {
                if (DateTime.TryParse(s_fzrq2, out dt2))
                {
                    sql += " and fzsj<=@dt2";
                }
            }
            
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { key = "%" + key + "%", fr = "%" + fr + "%", dt1, dt2 });
            }
        }

        public object getfsxklistpage(string key, string fr, string s_fzrq1, string s_fzrq2, int page, int pagesize)
        {
            string sql = "select *,row_number() over(order by id desc) as rownum from xyl_fsxkz where 1=1 and del=0";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and dwmc like @key";
            }
            if (!string.IsNullOrEmpty(fr))
            {
                sql += " and fr like @fr";
            }
            DateTime dt1 = DateTime.Now;
            DateTime dt2 = DateTime.Now;
            if (!string.IsNullOrEmpty(s_fzrq1))
            {
                if (DateTime.TryParse(s_fzrq1, out dt1))
                {
                    sql += " and fzsj>=@dt1";
                }
            }
            if (!string.IsNullOrEmpty(s_fzrq2))
            {
                if (DateTime.TryParse(s_fzrq2, out dt2))
                {
                    sql += " and fzsj<=@dt2";
                }
            }
            
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { key = "%" + key + "%", fr = "%" + fr + "%", dt1, dt2 }).ToList();
            }
        }

        public object savefsxk(fsxkz wsxk)
        {
            string sql = "";
            if (wsxk.id == 0)
            {
                sql = @"INSERT INTO [xyl_fsxkz]
           (
           [dwmc]
           ,[dwdz]
           ,[lxdh]
           ,[xkxm]
           ,[fr]
           ,[sfzh]
           ,[xydm]
           ,[sqsj]
           ,[fzsj]
           ,[bh1]
           ,[bh2]
           ,[yxq1]
           ,[yxq2]
           ,[blry]
           ,[blry_name]
           ,[blsj]
           )
     VALUES
           (
           @dwmc
           ,@dwdz
           ,@lxdh
           ,@xkxm
           ,@fr
           ,@sfzh
           ,@xydm
           ,@sqsj
           ,@fzsj
           ,@bh1
           ,@bh2
           ,@yxq1
           ,@yxq2
           ,@blry
           ,@blry_name
           ,@blsj
           )
select SCOPE_IDENTITY()
";
            }
            else
            {
                sql = @"UPDATE [xyl_fsxkz]
   SET 
      [dwmc] = @dwmc
      ,[dwdz] = @dwdz
      ,[lxdh] = @lxdh
      ,[xkxm] = @xkxm
      ,[fr] = @fr
      ,[sfzh] = @sfzh
      ,[xydm] = @xydm
      ,[sqsj] = @sqsj
      ,[fzsj] = @fzsj
      ,[bh2] = @bh2
      ,[yxq1] = @yxq1
      ,[yxq2] = @yxq2
      ,[blry] = @blry
      ,[blry_name] = @blry_name
      ,[blsj] = @blsj,bh1=@bh1
 WHERE id=@id
";

            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int id = 0;
                    if (wsxk.id == 0)
                    {
                        id = conn.ExecuteScalar<int>(sql, wsxk);
                        wsxk.id = id;
                    }
                    else
                    {
                        conn.Execute(sql, wsxk);
                    }
                    return new { State = 1, Message = "操作成功！", tag = wsxk.id };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public object getfsxkbg(string id)
        {
            //throw new NotImplementedException();
            string sql = "select * from xyl_fsxkz where id=@id";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { id }).ToList();
            }
        }

        public object getwsxkbg(string id)
        {
            //throw new NotImplementedException();
            string sql = "select * from xyl_wsxk where id=@id";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { id }).ToList();
            }
        }

        public object savewsxk(wsxkz wsxkz)
        {
            string sql = "";
            if (wsxkz.id == 0)
            {
                sql = @"INSERT INTO [xyl_wsxk]
           ([fgks]
           ,[gxqy]
           ,[dwmc]
           ,[dwdz]
           ,[lxdh]
           ,[jyxz]
           ,[jyfw]
           ,[xkxm]
           ,[fr]
           ,[sfzh]
           ,[xydm]
           ,[jymj]
           ,[sqsj]
           ,[fzsj]
           ,[bh1]
           ,[bh2]
           ,[yxq1]
           ,[yxq2]
           ,[blry]
           ,[blry_name]
           ,[blsj]
           ,[fgksid]
           ,[gxqyid]
           ,[jyxzid]
           ,[jyfwid])
     VALUES
           (@fgks
           ,@gxqy
           ,@dwmc
           ,@dwdz
           ,@lxdh
           ,@jyxz
           ,@jyfw
           ,@xkxm
           ,@fr
           ,@sfzh
           ,@xydm
           ,@jymj
           ,@sqsj
           ,@fzsj
           ,@bh1
           ,@bh2
           ,@yxq1
           ,@yxq2
           ,@blry
           ,@blry_name
           ,@blsj
           ,@fgksid
           ,@gxqyid
           ,@jyxzid
           ,@jyfwid)
select SCOPE_IDENTITY()
";
            }
            else
            {
                sql = @"UPDATE [xyl_wsxk]
   SET [fgks] = @fgks
      ,[gxqy] = @gxqy
      ,[dwmc] = @dwmc
      ,[dwdz] = @dwdz
      ,[lxdh] = @lxdh
      ,[jyxz] = @jyxz
      ,[jyfw] = @jyfw
      ,[xkxm] = @xkxm
      ,[fr] = @fr
      ,[sfzh] = @sfzh
      ,[xydm] = @xydm
      ,[jymj] = @jymj
      ,[sqsj] = @sqsj
      ,[fzsj] = @fzsj
      ,[bh2] = @bh2
      ,[yxq1] = @yxq1
      ,[yxq2] = @yxq2
      ,[blry] = @blry
      ,[blry_name] = @blry_name
      ,[blsj] = @blsj
      ,[fgksid] = @fgksid
      ,[gxqyid] = @gxqyid
      ,[jyxzid] = @jyxzid
      ,[jyfwid] = @jyfwid,bh1=@bh1
 WHERE id=@id
";
                
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int id = 0;
                    if (wsxkz.id==0)
                    {
                        id = conn.ExecuteScalar<int>(sql, wsxkz);
                        wsxkz.id = id;
                    }
                    else {
                    conn.Execute(sql, wsxkz); }
                    return new { State = 1, Message = "操作成功！",tag=wsxkz.id };
                }
                catch(Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public object getwsxklistpage(string key, string fr, string s_fzrq1, string s_fzrq2, string s_jyfw, int page, int pagesize)
        {
            string sql = "select *,row_number() over(order by id desc) as rownum from xyl_wsxk where 1=1 and del=0";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and dwmc like @key";
            }
            if (!string.IsNullOrEmpty(fr))
            {
                sql += " and fr like @fr";
            }
            DateTime dt1 = DateTime.Now;
            DateTime dt2 = DateTime.Now;
            if (!string.IsNullOrEmpty(s_fzrq1))
            {
                if (DateTime.TryParse(s_fzrq1, out dt1))
                {
                    sql += " and fzsj>=@dt1";
                }
            }
            if (!string.IsNullOrEmpty(s_fzrq2))
            {
                if (DateTime.TryParse(s_fzrq2, out dt2))
                {
                    sql += " and fzsj<=@dt2";
                }
            }
            if (!string.IsNullOrEmpty(s_jyfw))
            {
                sql += " and jyfwid=@s_jyfw";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { key = "%" + key + "%", fr = "%" + fr + "%", dt1, dt2, s_jyfw }).ToList();
            }
        }

        public object getjddict(string lb)
        {
            //throw new NotImplementedException();
            string sql = "select * from jd_dict where zt=1 and lb=@lb order by px";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.JianDu.jd_dict>(sql, new { lb }).ToList();
            }
        }

        public List<jd_dict> getdictlistpage(string key, string lb, int page, int pagesize)
        {
            string sql = "select *,row_number() over(order by px) as rownum from jd_dict where 1=1 ";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and mc like @key";
            }
            if (lb != "0")
            {
                sql += " and lb=@lb";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.JianDu.jd_dict>(sql,  new { key = "%" + key + "%", lb }).ToList();
            }
        }

        public int getdictcount(string key, string lb)
        {
            string sql = "select count(1) from jd_dict where 1=1";
            if(!string.IsNullOrEmpty(key))
            {
                sql += " and mc like @key";
            }
            if(lb!="0")
            {
                sql += " and lb=@lb";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { key="%"+key+"%", lb });
            }
        }
    }
}