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

namespace dccdc.DAL
{
    public class WeiHuDal
    {
        public int getCount()
        {
            string sql = "select count(1) from Sz_infectiousDisease_category ";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql);
            }
        }

        public List<Sz_infectiousDisease_category> getListPage(int page, int pagesize)
        {
            string sql = "select *,row_number() over(order by infectiousDiseaseCate,px) as rownum from Sz_infectiousDisease_category";

            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.Sz_infectiousDisease_category>(sql).ToList();
            }
        }

        public List<Sz_infectiousDisease_category> GetAllList(string id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string param = "";
                if (id != "")
                {
                    param = " and id=@id";
                }
                return conn.Query<Sz_infectiousDisease_category>("select * from Sz_infectiousDisease_category where 1=1 " + param, new { @id = id }).ToList();
            }
        }

        public bool Add(Sz_infectiousDisease_category dtomodel)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = @"INSERT INTO [Sz_infectiousDisease_category]
            (
             infectiousDiseaseCate,
             infectiousDiseaseName,
             icd,
             [state],
             px,
             DiseaseCode,
             DiseaseName)
VALUES      (
             @infectiousDiseaseCate,
             @infectiousDiseaseName,
             @icd,
             @state,
             @px,
             @DiseaseCode,
             @DiseaseName)";
                return (conn.Execute(sql, dtomodel) != 0 ? true : false);
            }
        }

        public bool Update(Sz_infectiousDisease_category dtomodel)
        {
            string sql = "UPDATE [Sz_infectiousDisease_category] SET";
            if (dtomodel.infectiousDiseaseCate != 0)
                sql += " infectiousDiseaseCate=@infectiousDiseaseCate,";
            if (dtomodel.DiseaseCode != null && dtomodel.DiseaseCode != "")
                sql += " DiseaseCode=@DiseaseCode,";
            if (dtomodel.DiseaseName != null && dtomodel.DiseaseName != "")
                sql += " DiseaseName=@DiseaseName,";
            if (dtomodel.icd != null && dtomodel.icd != "")
                sql += " icd=@icd,";
            if (dtomodel.infectiousDiseaseName != null && dtomodel.infectiousDiseaseName != "")
                sql += " infectiousDiseaseName=@infectiousDiseaseName,";
            if (dtomodel.px != null)
                sql += " px = @px,";
            if (dtomodel.state != null)
                sql += " state =@state,";

            sql = sql.Substring(0, (sql.Length - 1));
            sql += " WHERE id=@id";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return (conn.Execute(sql, dtomodel) != 0 ? true : false);
            }
        }
    }
}