XYY.Core/2.Data/XYY.Data.Standard.User/IUserCustomerRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard;
using Dapper;

namespace XYY.Data.Standard
{
    public interface IUserCustomerRepository : IBaseRepository<User_Customer>
    {
        Task<IEnumerable<User_Customer>> GetAllFreezeSoonCustomer(int inDays);
        Task<int> NoAlertFreezedUntil(string customerIds, int days);
        Task<int> DelayFreezed(string customerIds, int days);
        Task<List<int>> GetNSMPCustomer();
    }
    public class UserCustomerRepository : BaseRepository<User_Customer>, IUserCustomerRepository
    {
        public UserCustomerRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
        {
        }

        public async Task<IEnumerable<User_Customer>> GetAllFreezeSoonCustomer(int inDays)
        {
            var lastReceiveTime = DateTime.Now.AddDays(inDays).AddDays(-30);
            string from = lastReceiveTime.ToString("yyyy-MM-dd 00:00:00");
            string to = lastReceiveTime.ToString("yyyy-MM-dd 23:59:59");
            string beginTime = DateTime.Now.AddMonths(-6).ToString("yyyy-MM-dd");

            string sql = $@"with T as(
                        select MAX(ReceiveTime) as LastReceiveTime,CustomerId from Order_Order nolock where ReceiveTime>'{beginTime}' and OrderStatus>=2   group by CustomerId
                        )
                        select c.* from User_Customer c inner join T on c.Id=t.CustomerId where IsActive=1 and (NextAlertFreezeTime is null or NextAlertFreezeTime<=getdate()) and LastReceiveTime>'{from}' and LastReceiveTime<'{to}'";

            var result = await base._unitOfWork.Connection.QueryAsync<User_Customer>(sql, transaction: null, commandTimeout: 900);
            return result;
        }


        public async Task<int> NoAlertFreezedUntil(string customerIds, int days)
        {
            DateTime nextTime = DateTime.Now.AddDays(days);
            var cids = customerIds.SplitToArray<int>(',');
            string sql = $"update User_Customer set NextAlertFreezeTime=@NextTime where Id in @ids";
            int count = await base._unitOfWork.Connection.ExecuteAsync(sql, param: new { NextTime = nextTime, ids = cids }, transaction: base._unitOfWork.Transaction);
            return count;
        }

        public async Task<int> DelayFreezed(string customerIds, int days)
        {
            var cids = customerIds.SplitToArray<int>(',');
            string sql = $"update User_Customer set UpdateTime= getdate() where Id in @ids";
            int count = await _unitOfWork.Connection.ExecuteAsync(sql, new { ids = cids }, transaction: base._unitOfWork.Transaction);
            return count;
        }

        public async Task<List<int>> GetNSMPCustomer()
        {
            string sql = @"select Id from User_Customer(nolock) where DeductionNode = 3";
            return (await _unitOfWork.Connection.QueryAsync<int>(sql)).ToList();
        }
    }
}