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 InfectionQuestionDal
    {
        public List<InfectionQuestionModel> GetAllDataList()
        {
            string sql = "Select q.id,q.text,q.check_date,q.create_by,q.create_time,sum(case  when (a.answer='是') then 1 else 0 end) as answer_yes,sum(case  when (a.answer='否') then 1 else 0 end) as answer_no,sum(case  when (a.answer='不知道') then 1 else 0 end) as answer_unknow From infection_question_answer a right join infection_question q on a.question_id=q.id where q.type=4 group by q.id,q.text,q.check_date,q.create_by,q.create_time order by q.id ";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<InfectionQuestionModel>(sql).ToList();
            }
        }

        public object saveData(InfectionQuestionModel model)
        {
            string sql = string.Empty;
            if (model.id == 0)
            {
                sql = @"Insert Into infection_question(sheet_id,type,text,sort,create_by,create_time,check_date) Values (1,4,@text,0,@create_by,@create_time,@check_date)";
            }
            else
            {
                sql = @"Update infection_question Set text=@text,check_date=@check_date Where id=@id";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql, model);
                    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 deleteData(string id)
        {
            string sql = string.Format("Delete From infection_question Where id = {0}", id);
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql);
                    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 InfectionQuestionModel GetTodayQuestion()
        {
            try
            {
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string sql = "Select * From infection_question where type=4 and check_date='" + today + "'";
                using (IDbConnection conn = CommHelper.GetSqlConnection())
                {
                    return conn.QueryFirst<InfectionQuestionModel>(sql);
                }
            }
            catch (Exception ex){
                log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType).Info("exception:" + ex.Message);
                return new InfectionQuestionModel();
            }
        }

    }
}