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

namespace dccdc.DAL
{
    public class charge_refund_detailDal
    {
        public int getCount(string key)
        {
            string sql = "select count(1) from charge_refund_detail where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and ()";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { area_name = "%" + key + "%" });
            }
        }
        public List<charge_refund_detailModel> getList(int page, int pagesize, string key)
        {
            string sql = "select *,row_number() over(order by id) as rownum from charge_refund_detail where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and ()";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<charge_refund_detailModel>(sql, new { area_name = "%" + key + "%" }).ToList();
            }
        }

        public List<charge_refund_detailModel> GetAllList(string id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string param = "";
                if (id != "")
                {
                    param += " and id=@id";
                }

                string sql = "select * from charge_refund_detail where 1=1 ";
                return conn.Query<charge_refund_detailModel>(sql + param, new { id = id }).ToList();
            }
        }


        public object save(charge_refund_detailModel ct)
        {
            string sql = "";
            if (ct.id == 0)
            {
                sql = @"INSERT INTO [dbo].[charge_refund_detail]
                               ([tfsqid]
                               ,[ymid]
                               ,[ymsl]
                               ,[ymdj]
                               ,[ymje])
                         VALUES
                               (@tfsqid
                               ,@ymid
                               ,@ymsl
                               ,@ymdj
                               ,@ymje)";
            }
            else
            {
                sql = @"UPDATE [dbo].[charge_refund_detail]
                       SET [tfsqid] = @tfsqid
                          ,[ymid] = @ymid
                          ,[ymsl] = @ymsl
                          ,[ymdj] = @ymdj
                          ,[ymje] = @ymje
                      WHERE  id=@id";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql, ct);
                    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 save(List<charge_refund_detailModel> ct)
        {
            string sql = "";
                sql = @"INSERT INTO [dbo].[charge_refund_detail]
                               ([tfsqid]
                               ,[ymid]
                               ,[ymsl]
                               ,[ymdj]
                               ,[ymje])
                         VALUES
                               (@tfsqid
                               ,@ymid
                               ,@ymsl
                               ,@ymdj
                               ,@ymje)";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql, ct);
                    if (c > 0)
                    {
                        return new { State = 1, Message = "保存成功！" };
                    }
                    else
                    {
                        return new { State = 0, Message = "操作失败，请联系管理员！" };
                    }
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }
    }
}