tijian_tieying/web/dccdc.DAL/PaiDuiJiaoHaoDAL.cs

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

namespace dccdc.DAL
{
    public class PaiDuiJiaoHaoDAL
    {
        /// <summary>
        /// 获取排队人数
        /// </summary>
        /// <param name="v">1上午2下午</param>
        /// <returns></returns>
        public int getpdrs(int v)
        {
            //throw new NotImplementedException();
            string sql = "select count(1) from pd_xx where rq>@rq and rq<@rq2";
            DateTime rq = new DateTime();
            DateTime rq2 = new DateTime();
            if(v==1)
            {
                rq = DateTime.Now.Date;
                rq2 = DateTime.Now.Date.AddHours(12); 
            }
            else
            {
                rq = DateTime.Now.Date.AddHours(12);
                rq2 = DateTime.Now.Date.AddHours(24);
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql,new { rq, rq2 });
            }
        }

        public int add(PD_XX pdxx)
        {
            //throw new NotImplementedException();
            string sql = @"INSERT INTO [PD_XX]
           ([rq]
           ,[hm]
           ,[jhbz]
           ,[jhsj]
           ,[jhck]
           ,[jhckmc]
           ,[etid])
     VALUES
           (@rq
           ,@hm
           ,@jhbz
           ,@jhsj
           ,@jhck
           ,@jhckmc
           ,@etid)
";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Execute(sql, pdxx);
            }
        }

        public object getpdrs()
        {
            //throw new NotImplementedException();
            string sql = "select count(1) from pd_xx where rq>@rq and jhbz=0";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { rq = DateTime.Now.Date });
            }
        }

        public Models.PD_XX getnext()
        {
            //throw new NotImplementedException();
            string sql = "select top 1 * from pd_xx where jhbz=0 and rq>@rq order by hm";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<PD_XX>(sql, new { rq = DateTime.Now.Date }).FirstOrDefault();
            }
        }

        public void update(PD_XX pdxx)
        {
            //throw new NotImplementedException();
            string sql = @"UPDATE [PD_XX]
   SET
      [jhbz] = @jhbz
      ,[jhsj] = @jhsj
      ,[jhck] = @jhck
      ,[jhckmc] = @jhckmc
 WHERE id=@id";

            using (var conn = CommHelper.GetSqlConnection())
            {
                conn.Execute(sql, pdxx);
            }
        }

        public Models.PD_XX getmbyhm(string hm)
        {
            //throw new NotImplementedException();
            string sql = "select top 1 * from pd_xx where hm=@hm and rq>@rq order by hm";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<PD_XX>(sql, new {hm, rq = DateTime.Now.Date }).FirstOrDefault();
            }
        }

        public List<PD_XX> getWJH()
        {
            //throw new NotImplementedException();
            string sql = "select * from pd_xx where rq>@rq and jhbz=0 order by hm";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<PD_XX>(sql, new {  rq = DateTime.Now.Date }).ToList();
            }
        }

        public List<PD_XX> getYJH()
        {
            string sql = "select * from pd_xx where (jhbz=1 or jhbz=2) and rq>@rq order by hm";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<PD_XX>(sql, new {  rq = DateTime.Now.Date }).ToList();
            }
        }

        public object qxpd(string pdid)
        {
            //throw new NotImplementedException();
            string sql = "update pd_xx set jhbz=-1 where id=@id";
            using (var conn = CommHelper.GetSqlConnection())
            {
                try
                {

               
                 conn.Execute(sql, new { id = pdid });
                    return new { State = 1 };
                }
                catch(Exception ex)
                {
                    return new { State = 1, Message = ex.Message };
                }


            }
        }

        public PD_XX getpdxx(string session)
        {
            //throw new NotImplementedException();
            string sql = @"select a.* from PD_XX a join jiezhongmianyi_tiaomadayin b on a.etid=b.id join bdxx c on c.etid=b.id where (a.jhbz=0 or a.jhbz=1) and c.openid=@openid 
                        and a.rq>@rq order by a.hm desc";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<PD_XX>(sql, new { openid = session, rq = DateTime.Now.Date }).FirstOrDefault();
            }
        }
    }
}