tijian_tieying/web/dccdc.DAL/InfectionQuestionRecordDal.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 InfectionQuestionRecordDal
    {
        public List<InfectionQuestionRecordModel> getDataListByUserID(string userID)
        {
            string sql = string.Format("SELECT TOP 5 qr.id, qr.info_id,qr.record FROM infection_question_record qr WHERE qr.info_id = '{0}' ORDER BY qr.CREATE_TIME DESC", userID);
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<InfectionQuestionRecordModel>(sql).ToList();
            }
        }

        public object saveData(InfectionQuestionRecordModel model)
        {
            string deleteSQL = string.Format("Delete From infection_question_answer Where user_id = {0}", model.user_id);
            string insertSQL1 = string.Format("Insert Into infection_question_answer(question_id,user_id,answer,create_time) Values ((Select id From infection_question Where sort = {0}),{1},'{2}','{3}')", 1,model.user_id,model.answer_One,DateTime.Now.ToString());
            string insertSQL2 = string.Format("Insert Into infection_question_answer(question_id,user_id,answer,create_time) Values ((Select id From infection_question Where sort = {0}),{1},'{2}','{3}')", 2, model.user_id, model.answer_Two, DateTime.Now.ToString());
            string insertSQL3 = string.Format("Insert Into infection_question_answer(question_id,user_id,answer,create_time) Values ((Select id From infection_question Where sort = {0}),{1},'{2}','{3}')", 3, model.user_id, model.answer_Three, DateTime.Now.ToString());

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int i = 0;
                    conn.Execute(deleteSQL);
                    if (conn.Execute(insertSQL1) > 0)
                    {
                        i++;
                    }
                    if (conn.Execute(insertSQL2) > 0)
                    {
                        i++;
                    }
                    if (conn.Execute(insertSQL3) > 0)
                    {
                        i++;
                    }
                    if (i == 3)
                    {
                        return new { State = 1, Message = "保存成功！" };
                    }
                    else if (i == 0)
                    {
                        return new { State = 0, Message = "保存失败！" };
                    }
                    else
                    {
                        return new { State = 0, Message = "部分保存失败！" };
                    }
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public bool saveQuestionData(InfectionQuestionRecordModel model)
        {
            //string delete = string.Format("delete from infection_question_record where info = {0}", model.info_id);
            string insert = string.Format("Insert Into infection_question_record (info_id,sheet_id,record,create_time) Values ({0},{1},'{2}','{3}')", model.info_id, model.sheet_id, model.record, model.create_time);

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    //if (conn.Execute(delete) > 0)
                    //{
                        if (conn.Execute(insert) > 0)
                        {
                            string sql2 = string.Format("Update infection_open_user Set state=3 Where id={0}", model.info_id);
                            using (IDbConnection conn2 = CommHelper.GetSqlConnection())
                            {
                                try
                                {
                                    int result2 = conn.Execute(sql2);
                                    if (result2 > 0)
                                    {
                                        return true;
                                    }
                                    else
                                    {
                                        return false;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    string s = ex.Message;
                                    return false;
                                }
                            }
                        }
                        else
                        {
                            return false;
                        }
                    //}
                    //else {
                    //    return false;
                    //}
                }
                catch (Exception ex)
                {
                    return false;
                }
            }

        }

        public InfectionQuestionRecordModel getDataById(string id)
        {
            string sql = string.Format("SELECT qr.id,qr.info_id,qr.record FROM infection_question_record qr WHERE qr.id = {0}", id);
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<InfectionQuestionRecordModel>(sql, new { id = id }).First();
            }
        }

        public string delete(string id)
        {

            string sql = string.Format("DELETE FROM infection_question_record WHERE id = '{0}'", id);
            string returnValue = string.Empty;
            try
            {
                using (IDbConnection conn = CommHelper.GetSqlConnection())
                {
                    int result = conn.Execute(sql, id);
                    if (result > 0)
                        returnValue = "True";
                    else
                        returnValue = "False";
                }
            }
            catch (Exception e)
            {
                returnValue = "False";
            }
            return returnValue;
        }

        public string getSheetId()
        {

            string sql = string.Format("select top 1 sheet_id FROM infection_question_record order by create_time desc ");
            string returnValue = string.Empty;
            try
            {
                using (IDbConnection conn = CommHelper.GetSqlConnection())
                {
                    InfectionQuestionRecordModel result = conn.QueryFirst<InfectionQuestionRecordModel>(sql);
                    if (result == null)
                    {
                        returnValue = "";
                    }
                    else {
                        returnValue = result.sheet_id;
                    }
                }
            }
            catch (Exception e)
            {
                returnValue = e.Message;
            }
            return returnValue;
        }

        
    }
}