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 medical_scheme_project_maintainDal
    {
        public List<medical_scheme_project_maintainModel> GetAllList(string id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                var param = "";
                if (id != "")
                {
                    param = " and id=@id";
                }
                return conn.Query<medical_scheme_project_maintainModel>("select * from medical_scheme_project_maintain where 1=1 " + param, new { @id = id }).ToList();
            }
        }
        /// <summary>
        /// 根据体检方案id获取体检项目
        /// </summary>
        /// <param name="medicalschememaintainid"></param>
        /// <returns></returns>
        public List<medical_scheme_project_maintainModel> GetListBymedicalschememaintainid(string medicalschememaintainid)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<medical_scheme_project_maintainModel>("select * from medical_scheme_project_maintain where medical_scheme_maintain_id =@id ", new { @id = medicalschememaintainid }).ToList();
            }
        }
        public int getCount(string key)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                var param = "";
                if (!string.IsNullOrEmpty(key))
                {
                    param = " and (medical_scheme like @where or project_name like @where  or team_name like @where)";
                }
                return conn.ExecuteScalar<int>("select count(1) from medical_scheme_project_maintain where 1=1 " + param, new { where = "%" + key + "%" });
            }
        }
        public List<medical_scheme_project_maintainModel> getList(int page, int pagesize, string key)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select *,row_number() over(order by id desc) as rownum from medical_scheme_project_maintain where 1=1";
                if (!string.IsNullOrEmpty(key))
                {
                    sql += " and (medical_scheme like @where or project_name like @where  or team_name like @where)";
                }
                sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
                return conn.Query<medical_scheme_project_maintainModel>(sql, new { where = "%" + key + "%" }).ToList();
            }
        }

        public bool Add(medical_scheme_project_maintainModel model)
        {
            string sql = @"INSERT INTO [medical_scheme_project_maintain]
                           ([medical_scheme]
                           ,[medical_scheme_maintain_id]
                           ,[project_name]
                           ,[team_name]
                           ,[exam_project_maintain_project_id])
                     VALUES
                           (@medical_scheme,
                           @medical_scheme_maintain_id,
                           @project_name,
                           @team_name,
                           @exam_project_maintain_project_id)";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return (conn.Execute(sql, model) != 0 ? true : false);
            }
        }

        public bool Update(medical_scheme_project_maintainModel model)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = @"UPDATE [medical_scheme_project_maintain]
                               SET [medical_scheme] = @medical_scheme
                                  ,[medical_scheme_maintain_id] = @medical_scheme_maintain_id
                                  ,[project_name] = @project_name
                                  ,[team_name] = @team_name
                                  ,[exam_project_maintain_project_id] = @exam_project_maintain_project_id
                                  WHERE  id =@id";
                return (conn.Execute(sql, model) != 0 ? true : false);
            }
        }

        public List<MedicalSchemeMaintainModel> getEnableScheme()
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = @"select * from medical_scheme_maintain where status=@status";
                return conn.Query<MedicalSchemeMaintainModel>(sql, new { @status = "是" }).ToList();
            }
        }

        public List<ExamProjectMaintainModel> getEnableProject()
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = @"select * from exam_project_maintain";
                return conn.Query<ExamProjectMaintainModel>(sql).ToList();
            }
        }


        public string getTeamName(string project_id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = @"select * from exam_project_maintain where project_id=@project_id";
                return conn.Query<ExamProjectMaintainModel>(sql, new { @project_id = project_id }).First().exam_group;
            }
        }

        public object del(string id)
        {
            string sql = @"delete medical_scheme_project_maintain where id=@id";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    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 };
                }
            }
        }
    }
}