tijian_tieying/web/dccdc.DAL/InfectionDaysignQuestionDal.cs

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 InfectionDaysignQuestionDal
    {
        public List<InfectionDaysignQuestionModel> GetAllDataList()
        {
            string sql = "Select q.id,q.text,q.answer,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_daysign_question_answer a right join infection_daysign_question q on a.question_id=q.id where q.type=4 group by q.id,q.text,q.answer,q.check_date,q.create_by,q.create_time order by q.id ";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<InfectionDaysignQuestionModel>(sql).ToList();
            }
        }

        public object saveData(InfectionDaysignQuestionModel model)
        {
            try
            {
                int c = 0;
                string sql = string.Empty;
                string sql2 = string.Empty;
                string sql3 = string.Empty;
                int id = 0;
                using (IDbConnection conn = CommHelper.GetSqlConnection())
                {
                    if (model.id == 0)
                    {
                        sql = @"Insert Into infection_daysign_question(sheet_id,type,text,answer,sort,create_by,create_time,check_date) Values (1,4,@text,@answer,0,@create_by,@create_time,@check_date)Select @@Identity";
                        id = conn.ExecuteScalar<int>(sql, model);
                        sql2 = @"delete from infection_daysign_question_options where question_id=" + id;
                        c = conn.Execute(sql2);
                        string[] strlist = model.options.Split(',');
                        for (int i=0;i<strlist.Length;i++) {
                            sql3 = @"Insert into infection_daysign_question_options(question_id,options,create_time) values ("+id+",'"+strlist[i]+ "',@create_time)";
                            c = conn.Execute(sql3,model);
                        }
                    }
                    else
                    {
                        sql = @"Update infection_daysign_question Set text=@text,answer=@answer,check_date=@check_date Where id=@id";
                        c = conn.Execute(sql, model);
                        id = model.id;
                        sql2 = @"delete from infection_daysign_question_options where question_id=" + id;
                        c = conn.Execute(sql2);
                        string[] strlist = model.options.Split(',');
                        for (int i = 0; i < strlist.Length; i++)
                        {
                            sql3 = @"Insert into infection_daysign_question_options(question_id,options,create_time) values (" + id + ",'" + strlist[i] + "',@create_time)";
                            c = conn.Execute(sql3,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_daysign_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 InfectionDaysignQuestionModel GetTodayQuestion()
        {
            try
            {
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string sql = "Select * From infection_daysign_question where type=4 and check_date='" + today + "'";
                using (IDbConnection conn = CommHelper.GetSqlConnection())
                {
                    return conn.QueryFirst<InfectionDaysignQuestionModel>(sql);
                }
            }
            catch (Exception ex)
            {
                log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType).Info("exception:" + ex.Message);
                return new InfectionDaysignQuestionModel();
            }
        }

        public InfectionDaysignQuestionModel GetOneRandomQuestion()
        {
            try
            {
                string sql = "Select top 1 * From infection_daysign_question order by NEWID() ";
                using (IDbConnection conn = CommHelper.GetSqlConnection())
                {
                    return conn.QueryFirst<InfectionDaysignQuestionModel>(sql);
                }
            }
            catch (Exception ex)
            {
                log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType).Info("exception:" + ex.Message);
                return new InfectionDaysignQuestionModel();
            }
        }
    }
}