Master.CanYinWu.DAL/MemberDAL.cs

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

using Dapper;
using Master.CanYinWu.Entity;
using Master.CanYinWu.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Master.CanYinWu.DAL
{



    public class MemberDAL
    {

        public async Task<Tuple<IEnumerable<Member>, int>> GetList(int pageIndex, int pageSize, string key, string viewtype, int storeId)
        {
            StringBuilder sb = new StringBuilder(@"select 
                (SELECT SUM(ohl.OutSumPrice) AS TotalConsumption
                FROM OutHous oh
                INNER JOIN  OutHousList ohl ON  oh.OutHousId=ohl.OutHousId
                WHERE oh.MemberId = a.memberid
                AND ohl.IsDel = 0 and OutHousType=@OutHousType ) as  TotalConsumption,
                * from(select * ,ROW_NUMBER() over (order by memberid desc) as  rownumber  from  Member aa)as a where isdel=0 and storeId=@StoreId ");
            DynamicParameters dp = new DynamicParameters();
            dp.Add("PageIndex", pageIndex);
            dp.Add("PageSize", pageSize);
            dp.Add("StoreId", storeId);
            dp.Add("OutHousType", (int)Enums.OutHousType.正常出库);
            StringBuilder sqlTotal = new StringBuilder(@"select count(1)  from Member where isdel=0 and storeId=@StoreId");
            DynamicParameters dpTotal = new DynamicParameters();
            dpTotal.Add("StoreId", storeId);
            if (!string.IsNullOrEmpty(key))
            {
                sb.Append(" and name like '%'+@Key+'%' ");
                dp.Add("Key", key);
                sqlTotal.Append(" and name like '%'+@Key+'%' ");
                dpTotal.Add("Key", key);
            }

            sb.Append(" and rownumber BETWEEN (((@PageIndex-1)*@PageSize)+1) and @PageIndex*@PageSize ");
            using (var conn = ConnectionFactory.Connection())
            {
                var query = await conn.QueryAsync<Member>(sb.ToString(), dp);
                var total = await conn.ExecuteScalarAsync<int>(sqlTotal.ToString(), dpTotal);
                return new Tuple<IEnumerable<Member>, int>(query, total);
            }
        }

        /// <summary>
        /// 根据用户id得到实体
        /// </summary>
        /// <param name="memberId">用户id</param>
        /// <returns></returns>
        public Member GetModel(int memberId)
        {
            string sql = @"select  top 1 *   from Member a where  memberid=@MemberId";