tijian_tieying/web/dccdc.DAL/tj_yysjdDal.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 tj_yysjdDal
    {
        public List<tj_yysjd> GetAllList(string id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select a.*,b.BuMenName as jgmc from tj_yysjd a left join oa.dbo.erpbumen b on a.jgid=b.id where 1=1";
                if (!string.IsNullOrEmpty(id))
                {
                    sql += " and a.id=@id";
                }
                return conn.Query<tj_yysjd>(sql, new { @id = id }).ToList();
            }
        }

        public List<tj_yysjd> GetListByjg(string jgid)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from tj_yysjd where 1=1 and jgid=@jgid";
                return conn.Query<tj_yysjd>(sql, new { @jgid = jgid }).ToList();
            }
        }

        public List<WeiXinDate> canSelected(string date, string enddate)
        {
            string sql = @" select lb.adate
 ,(select sum(yyrs) from tj_yysjd ) as 'kyyrs',
 case when la.yyrs is null  then 0 else la.yyrs end yyrs from (
 
 (SELECT CONVERT(varchar(10), DateAdd(day,number,@adate),121)  adate
  FROM master..spt_values  
 WHERE type = 'p'  
   AND number <= DateDiff(day,@adate,@adate1) )lb
 left join
 (select *  from(
select convert(varchar(10),yyrq,121) adate,count(*)as 'yyrs'  from ym_yymx group by 
convert(varchar(10),yyrq,121))a  where a.adate > @adate and a.adate<@adate1 )

la
on lb.adate=la.adate)
";
            using (var conn = CommHelper.GetSqlConnection())
            {

                List<WeiXinDate> al = conn.Query<WeiXinDate>(sql, new { adate = date, adate1 = enddate }).ToList();
                List<WeiXinDate> news = new List<WeiXinDate>();
                if (al.Any())
                {
                    foreach (var m in al)
                    {
                        //WeiXinDate model = new WeiXinDate();

                        news.Add(m);


                    }
                }


                return news;
            }
        }

        public object save(tj_yysjd model)
        {
            string sql = "";
            if (model.id == 0)
            {
                sql = @"INSERT INTO [dbo].[tj_yysjd]
                               ([jgid]
                               ,[btime]
                               ,[etime]
                               ,[px]
                               ,[yyrs]
                               ,[mc])
                         VALUES
                               (@jgid
                               ,@btime
                               ,@etime
                               ,@px
                               ,@yyrs
                               ,@mc)";
            }
            else
            {
                sql = @"UPDATE [dbo].[tj_yysjd]
                       SET [jgid] = @jgid
                          ,[btime] = @btime
                          ,[etime] = @etime
                          ,[px] = @px
                          ,[yyrs] = @yyrs
                          ,[mc] = @mc
                          WHERE  id=@id";
            }

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int result = conn.Execute(sql, model);
                    if (result > 0)
                        return new { State = 1, Message = "保存成功！" };
                    else
                        return new { State = 0, Message = "保存失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public object delete(string id)
        {
            string sql = "delete from tj_yysjd where id=@id";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int result = conn.Execute(sql, new { id = id });
                    if (result > 0)
                        return new { State = 1, Message = "操作成功！" };
                    else
                        return new { State = 0, Message = "操作失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public int getCount(string key)
        {
            string sql = "select count(1) from dbo.tj_yysjd where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and lbmc like @key";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { key = "%" + key + "%" });
            }
        }

        public List<tj_yysjd> getPage(int page, int pagesize, string key)
        {
            string sql = "select *,row_number() over(order by id) as rownum from tj_yysjd where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and lbmc like @key";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.tj_yysjd>(sql, new { key = "%" + key + "%" }).ToList();
            }
        }
    }
}