qlhb/Ropin.Inspection.Repository/TdevDevOpeAccountRepository.cs

using Google.Protobuf.WellKnownTypes;
using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ropin.Inspection.Repository
{
    public class TdevDevOpeAccountRepository : RepositoryBase<TDEV_DevOpeAccount, Guid>, ITdevDevOpeAccountRepository
    {
        public TdevDevOpeAccountRepository(InspectionDbContext DbContext) : base(DbContext)
        {

        }
        public Task<int> DeleteByDevIdAsync(string devId)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("devId", devId) };
            int result = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, "DELETE from TDEV_DevOpeAccount WHERE C_DevStoreCode = @devId", parameters);
            return Task.FromResult(result);
        }
        public Task<List<DevOpeAccountConfigSelect>> GetDevOpeAccountConfigSelectAsync(TdevDevOpeAccountSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { 
                new MySqlConnector.MySqlParameter("DevStoreCode", searchModel.C_DevStoreCode),
                new MySqlConnector.MySqlParameter("Start", searchModel.D_Start?.ToString("yyyy-MM-dd")),
                new MySqlConnector.MySqlParameter("End", searchModel.D_End==null?null:searchModel.D_End.Value.AddDays(1).ToString("yyyy-MM-dd")),
            };

            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT
		t1.C_DevOpeAccountConfigCode AS code,
        CONCAT(date_format(MIN(t1.D_CreateOn), '%Y-%m-%d %H:%i'),' 至 ',date_format(MAX(t1.D_CreateOn), '%Y-%m-%d %H:%i')) as name
	FROM
		TDEV_DevOpeAccount t1
	INNER JOIN (
		SELECT DISTINCT C_DevOpeAccountConfigCode FROM TDEV_DevOpeAccount
) t2 ON t1.C_DevOpeAccountConfigCode = t2.C_DevOpeAccountConfigCode
WHERE 1=1
");
            if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode))
            {
                sql.Append(" and t1.C_DevStoreCode=@DevStoreCode ");
            }
            if (searchModel.D_Start != null && searchModel.D_End != null)
            {
                sql.Append(" and LEFT (t1.D_CreateOn,10)>=@Start and LEFT (t1.D_CreateOn,10)<=@End ");
            }
            sql.Append(@"	
	GROUP BY t1.C_DevOpeAccountConfigCode
    order by t1.D_CreateOn desc ");
            var result = EntityFrameworkCoreExtensions.SqlQuery<DevOpeAccountConfigSelect>(DbContext.Database, sql.ToString(), parameters).ToList();
            return Task.FromResult(result);
        }

    }
}