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

namespace dccdc.DAL
{
    public class vaccineDal
    {
        public int getCount(string name,string type)
        {
            string sql = "select count(1) from dbo.vaccine where 1=1 ";
            if (!string.IsNullOrEmpty(name))
            {
                sql += " and name like @name";
            }
            if (!string.IsNullOrEmpty(type))
            {
                sql += " and type = @type";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { name = "'%" + name + "%'",type=type });
            }
        }
        

        public List<vaccine> getAllList(string name)
        {
            //throw new NotImplementedException();
            string sql = "select *,row_number() over(order by id) as rownum from vaccine where 1=1 and status = '是'";
            if (!string.IsNullOrEmpty(name))
            {
                sql += " and name like @name";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.vaccine>(sql, new { name = "%" + name + "%"}).ToList();
            }
        }
        public List<vaccine> getList(int page, int pagesize, string name,string type)
        {
            //throw new NotImplementedException();
            string sql = "select *,row_number() over(order by id) as rownum from vaccine where 1=1";
            if (!string.IsNullOrEmpty(name))
            {
                sql += " and name like @name";
            }
            if (!string.IsNullOrEmpty(type))
            {
                sql += " and type = @type";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.vaccine>(sql, new { name = "%" + name + "%",type=type }).ToList();
            }
        }
        
        public object save(vaccine cpm)
        {
            //throw new NotImplementedException();
            string sql = "";
            if (cpm.id == 0)
            {
                sql = @"INSERT INTO [dbo].[vaccine]
           ([name],
[firm],
[spec],
[type],
[isCharge],
[chargeId],
[status]
)
     VALUES
           (@name,
@firm,
@spec,
@type,
@isCharge,
@chargeId,
@status
)
";
            }
            else
            {
                sql = @"UPDATE [dbo].[vaccine]
   SET [name]=@name,
[firm]=@firm,
[spec]=@spec,
[type]=@type,
[isCharge]=@isCharge,
[chargeId]=@chargeId,
[status]=@status
 WHERE id=@id
";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql, cpm);
                    if (c > 0)
                    {
                        return new { State = 1, Message = "保存成功！" };
                    }
                    else
                    {
                        return new { State = 0, Message = "操作失败，请联系管理员！" };
                    }
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }
    }
}