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

namespace dccdc.DAL
{
    public class JfJkxxDal
    {
        public List<JfJkxx> getJfJkxxOne(JfJkxx jfjkxx)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select a.* "
                    + " from jf_jkxx a where id=@id";
                return conn.Query<JfJkxx>(strsql, jfjkxx).ToList();
            }
        }

        public int countJfJkxx(JfJkxx jfJkxx)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select count(1) from jf_jkxx  where  1 = 1";
                if (jfJkxx.zt != null)
                {
                    strsql += " and zt=@zt ";
                }
                if (jfJkxx.jkrid != 0)
                {
                    strsql += " and jkrid=@jkrid";
                }
                return conn.ExecuteScalar<int>(strsql, jfJkxx);
            }
        }

        public bool updJfJkxx(JfJkxx info)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = @"update [jf_jkxx]
           set [jkrid] =@jkrid
            ,[jkrxm] =@jkrxm
            ,[jkbm] =@jkbm
            ,[jkrq] =@jkrq
            ,[jkyy]=@jkyy
            ,[jkje]=@jkje
           ,[bz]=@bz
           ,[zt]=@zt

            ,[ksldspyj]=@ksldspyj
            ,[ksldspsj] =@ksldspsj
            ,[fgldspyj] =@fgldspyj
            ,[fgldspsj] =@fgldspsj
            ,[cwfzspyj] =@cwfzspyj
            ,[cwfzspsj] =@cwfzspsj
           ,[zxldspyj]=@zxldspyj
           ,[zxldspsj]=@zxldspsj where id = @id";
                return (conn.Execute(sql, info) != 0 ? true : false);
            }
        }

        public bool addJfJkxx(JfJkxx model)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = @"INSERT INTO [jf_jkxx]
           ([jkrid]
           ,[jkrxm]
           ,[jkbm]
           ,[jkrq]
           ,[jkyy]
           ,[jkje]
           ,[bz]
           ,[zt]
           ,[ksid]
        ,[fgldspsj]
)
            VALUES(
           @jkrid
           ,@jkrxm
           ,@jkbm
           ,@jkrq
           ,@jkyy
           ,@jkje
           ,@bz
           ,@zt
           ,@ksid
        ,@fgldspsj)";
                model.fgldspsj = DateTime.Now;
                return (conn.Execute(sql, model) != 0 ? true : false);
            }
        }



        public List<JfJkxx> GetJfJkxxbyxm(string key, int page, int pagesize)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select a.*,row_number() over(order by id) as rownum "
                  + " from jf_jkxx a where   zt >4";
           
                if (key != null&&key!="")
                {
                    strsql += " and jkrxm like @jkrxm";
                }
                strsql = "select * from (" + strsql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
                return conn.Query<JfJkxx>(strsql, new { @jkrxm = "%"+key+"%" }).ToList();
            }
        }


        public int countJfJkxxbyxm(string key)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select count(1) from jf_jkxx  where  zt >4";
                if (key != null && key != "")
                {
                    strsql += " and jkrxm like @jkrxm";
                }
                return conn.ExecuteScalar<int>(strsql, new { @jkrxm = "%" + key + "%" });
            }
        }

        public int countspJfJkxx(string ksidstr, string ztstr)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select count(1) from jf_jkxx  where  1=1";
                if (ztstr != null && ztstr != "")
                {
                    strsql += ztstr;
                }
                else
                {
                    strsql += "AND 1=2";
                }
                return conn.ExecuteScalar<int>(strsql);
            }
        }

        public List<JfJkxx> getspJfJkxx(string ksidstr, string ztstr, int page, int pagesize)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select a.*,row_number() over(order by id) as rownum "
                  + " from jf_jkxx a where  1 = 1";

                if (ztstr != null && ztstr != "")
                {
                    strsql += ztstr;
                }
                else
                {
                    strsql += "AND 1=2";
                }
                strsql = "select * from (" + strsql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
                return conn.Query<JfJkxx>(strsql).ToList();
            }
        }

        public List<JfJkxx> getJfJkxx(JfJkxx jfJkxx, int page, int pagesize)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select a.*,row_number() over(order by id) as rownum "
                  +  " from jf_jkxx a where  1 = 1";
                if (jfJkxx.zt != null)
                {
                    strsql += " and zt=@zt ";
                }
                if (jfJkxx.jkrid != 0)
                {
                    strsql += " and jkrid=@jkrid";
                }

                if (jfJkxx.jkbm != null&& jfJkxx.jkbm !="")
                {
                    strsql += " and jkbm=@jkbm";
                }
                if (jfJkxx.jkrq != null)
                {
                    strsql += " and jkrq=@jkrq";
                }
                strsql = "select * from (" + strsql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
                return conn.Query<JfJkxx>(strsql, jfJkxx).ToList();
            }
        }

        public bool delJfJkxx(JfJkxx delinfo)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String sql = "delete  from jf_jkxx where id=@id";
                return (conn.Execute(sql, delinfo) != 0 ? true : false);
            }
        }


        #region  款项支付
        public bool delJfKxzf(JfKxzf delinfo)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String sql = "delete  from jf_kxzf where id=@id";
                return (conn.Execute(sql, delinfo) != 0 ? true : false);
            }
        }
        public bool updJfKxzf(JfKxzf info)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = @"update [jf_kxzf]
           set [zffs] =@zffs
            ,[zfje] =@zfje
            ,[zfrid] =@zfrid
            ,[zfrq]=@zfrq
            ,[jkdid]=@jkdid
           where id = @id";
                return (conn.Execute(sql, info) != 0 ? true : false);
            }
        }
        public bool addJfKxzf(JfKxzf model)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = @"INSERT INTO [jf_kxzf]
           ([zffs]
           ,[zfje]
           ,[zfrid]
           ,[zfrq]
           ,[jkdid]
          
)
            VALUES
           (@zffs
           ,@zfje
           ,@zfrid
           ,@zfrq
           ,@jkdid
          )";
                return (conn.Execute(sql, model) != 0 ? true : false);
            }
        }
        public JfKxzf getJfKxzfOne(JfKxzf model)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select a.* "
                    + ",(select TOP 1 U.TrueName FROM OA..ERPUser U WHERE U.ID = A.zfrid) zfr"
                    + " from jf_kxzf a where id=@id";
                return conn.Query<JfKxzf>(strsql, model).ToList().FirstOrDefault();
            }
        }

        public List<JfKxzf> getJfKxzf(JfKxzf jfKxzf, int page, int pagesize)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select a.*,row_number() over(order by id) as rownum "

                  + " from jf_kxzf a where  1 = 1";

                strsql = "select * from (" + strsql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
                return conn.Query<JfKxzf>(strsql, jfKxzf).ToList();
            }
        }
        public int countJfKxzf(JfKxzf jfKxzf)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select count(1) from jf_kxzf  where  1 = 1";
                //if (jfJkxx.zt != null)
                //{
                //    strsql += " and zt=@zt ";
                //}
                //if (jfJkxx.jkrid != 0)
                //{
                //    strsql += " and jkrid=@jkrid";
                //}
                return conn.ExecuteScalar<int>(strsql, jfKxzf);
            }
        }

        #endregion
        #region 借款扣除

        public bool delJfJkkc(JfJkkc delinfo)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String sql = "delete  from jf_jkkc where id=@id";
                return (conn.Execute(sql, delinfo) != 0 ? true : false);
            }
        }
        public bool updJfJkkc(JfJkkc info)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = @"update [jf_jkkc]
           set [jkkcfs] =@jkkcfs
            ,[ghrid] =@ghrid
            ,[ghje] =@ghje
            ,[jkdid]=@jkdid
            ,[bxdid]=@bxdid
            ,[dkje]=@dkje
           where id = @id";
                return (conn.Execute(sql, info) != 0 ? true : false);
            }
        }
        public bool addJfJkkc(JfJkkc model)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = @"INSERT INTO [jf_jkkc]
           ([jkkcfs]
           ,[ghrid]
           ,[ghje]
           ,[jkdid]
           ,[bxdid]
           ,[dkje]
)
            VALUES
           (@jkkcfs
           ,@ghrid
           ,@ghje
           ,@jkdid
           ,@bxdid
           ,@dkje
          )";
                return (conn.Execute(sql, model) != 0 ? true : false);
            }
        }
        public JfJkkc getJfJkkcOne(JfJkkc model)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select a.* " 
                    + ",(select TOP 1 U.TrueName FROM OA..ERPUser U WHERE U.ID = A.ghrid) ghr"
                    + " from jf_jkkc a where id=@id";
                return conn.Query<JfJkkc>(strsql, model).ToList().FirstOrDefault();
            }
        }


        public List<JfJkkc> getJfJkkc(JfJkkc jfJkkc, int page, int pagesize)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select a.*,row_number() over(order by id) as rownum "+
                   ",(select TOP 1 U.TrueName FROM OA..ERPUser U WHERE U.ID = A.ghrid) ghr" 
                  + " from jf_jkkc a where  1 = 1";
               
                strsql = "select * from (" + strsql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
                return conn.Query<JfJkkc>(strsql, jfJkkc).ToList();
            }
        }
        public int countJfJkkc(JfJkkc jfJkkc)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select count(1) from jf_jkkc  where  1 = 1";
                //if (jfJkxx.zt != null)
                //{
                //    strsql += " and zt=@zt ";
                //}
                //if (jfJkxx.jkrid != 0)
                //{
                //    strsql += " and jkrid=@jkrid";
                //}
                return conn.ExecuteScalar<int>(strsql, jfJkkc);
            }
        }

        #endregion


        public int countuserlist(string key)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select count(1)"
                  + " from [OA].[dbo].[ERPUser] a where  1 = 1";
                if (key != null)
                {
                    strsql += " and truename like @truename ";
                }
                return conn.ExecuteScalar<int>(strsql, new { @truename ="%"+ key+"%" });
            }
        }

        public List<ERPUser> getuserlist(string key, int page, int pagesize)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                String strsql = "select a.*,row_number() over(order by id) as rownum "
                  + " from [OA].[dbo].[ERPUser] a where  1 = 1";
                if (key != null)
                {
                    strsql += " and truename like @truename ";
                }
               //--
                strsql = "select * from (" + strsql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
                return conn.Query<ERPUser>(strsql, new { @truename = "%" + key + "%" }).ToList();
            }
        }


        public List<JfJkxx> getPageLevel(int page, int pagesize, string where)
        {
            string sql = "select *,row_number() over(order by id desc) as rownum from jf_jkxx where 1=1";
            if (!string.IsNullOrEmpty(where))
            {
                sql += where;
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.JfJkxx>(sql).ToList();
            }
        }

        public int getCountLevel(string where)
        {
            string sql = "select count(1) from dbo.jf_jkxx where 1=1";
            if (!string.IsNullOrEmpty(where))
            {
                sql += where;
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql);
            }
        }
    }
}