qlhb/Ropin.Inspection.Repository/SYS/TsysMessageRepository.cs

using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Common;
using Ropin.Inspection.Model.Entities;
using Ropin.Inspection.Model.SearchModel.MTN;
using Ropin.Inspection.Model.ViewModel.MTN;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ropin.Inspection.Repository
{
    public  class TsysMessageRepository : RepositoryBase<TSYS_Message, string>, ITsysMessageRepository
    {
        public TsysMessageRepository(InspectionDbContext dbContext) : base(dbContext)
        {

        }
        public Task<bool> UpdateMsgStatus(string id,int? msgStatus)
        {
            MySqlConnector.MySqlParameter[] parameters =  new[] {
                new MySqlConnector.MySqlParameter("code", id),
                new MySqlConnector.MySqlParameter("msgStatus", msgStatus)
            };
            string sql = $" UPDATE  TSYS_Message  SET I_MsgStatus = @msgStatus  WHERE (C_ID =@code); ";
            int iResult = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, sql, parameters);
            bool result = iResult > 0;
            return Task.FromResult(result);
        }


        public Task<IEnumerable<TsysMessageViewModel>> GetList(TsysMessageSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("Status", searchModel.C_Status),
                new MySqlConnector.MySqlParameter("id", searchModel.C_ID),
                new MySqlConnector.MySqlParameter("generationType", searchModel.I_GenerationType),
                new MySqlConnector.MySqlParameter("msgTypeCode", searchModel.C_MsgTypeCode),
                new MySqlConnector.MySqlParameter("pushMsgToCode", searchModel.C_PushMsgToCode),
                new MySqlConnector.MySqlParameter("storeCode", searchModel.C_StoreCode),
                new MySqlConnector.MySqlParameter("devCode", searchModel.C_DevCode),
                new MySqlConnector.MySqlParameter("content",  "%"+searchModel.C_Content+"%"),
                new MySqlConnector.MySqlParameter("beginTme", searchModel.BeginTime?.ToString("yyyy-MM-dd")),
                new MySqlConnector.MySqlParameter("endTime ", searchModel.EndTime?.ToString("yyyy-MM-dd"))
            };
            StringBuilder sql = new StringBuilder();
            sql.Append(@"
 select * from (
 select m.*,d.C_StoreCode,d.C_Name as C_DevName,c.C_Name as C_MsgTypeName,pm.C_PushMsgToCode,ifnull(f.cut,0) as IsFile 
 from TSYS_Message m
 LEFT JOIN TDEV_DevStore d on (m.C_DevStoreCode=d.C_ID)
 LEFT JOIN TBDM_CodeDetail c on (m.C_MsgTypeCode=c.C_Code)
 LEFT JOIN 
 (select C_MessageCode,C_PushMsgToCode,count(1) cut from TMTN_PushMsgResult 
");
            if (!string.IsNullOrEmpty(searchModel.C_PushMsgToCode))
            {
                sql.Append(" where C_PushMsgToCode=@pushMsgToCode ");
            }
            sql.Append(@" group by C_MessageCode,C_PushMsgToCode) pm on(m.C_ID=pm.C_MessageCode) 
LEFT JOIN ( select C_MessageCode,Count(1) cut from TSYS_MessageFile group by C_MessageCode) f on (m.C_ID=f.C_MessageCode)
) tab ");
            if (!string.IsNullOrEmpty(searchModel.C_Status))
            {
                sql.Append(" where C_Status=@Status  ");
            }
            else
            {
                sql.Append(" where C_Status!='0' ");
            }

            if (!string.IsNullOrEmpty(searchModel.C_ID))
            {
                sql.Append(" and C_ID=@id ");
            }
            if (searchModel.I_GenerationType!=null)
            {
                sql.Append(" and I_GenerationType=@generationType  ");
            }
            if (searchModel.MsgStatus != null&& searchModel.MsgStatus.Count>0)
            {
                string msgStatus=string.Join(", ", searchModel.MsgStatus);
                sql.Append($" and I_MsgStatus in ({msgStatus})");
            }
            if (!string.IsNullOrEmpty(searchModel.C_MsgTypeCode))
            {
                sql.Append(" and C_MsgTypeCode=@msgTypeCode ");
            }
            if (searchModel.MsgTypeList != null && searchModel.MsgTypeList.Count > 0)
            {
                StringBuilder contsql = new StringBuilder();
                sql.Append($" and  C_MsgTypeCode in ('{String.Join("','", searchModel.MsgTypeList)}')  ");
            }
            if (!string.IsNullOrEmpty(searchModel.C_StoreCode))
            {
                sql.Append(" and C_StoreCode=@storeCode ");
            }
            if (!string.IsNullOrEmpty(searchModel.C_DevCode))
            {
                sql.Append(" and C_DevStoreCode=@devCode ");
            }
            if (!string.IsNullOrEmpty(searchModel.C_Content))
            {
                sql.Append(" and C_Content like @content ");
            }
            if (searchModel.BeginTime != null && searchModel.BeginTime != DateTime.MinValue)
            {
                sql.Append(" and DATE_FORMAT(D_CreateOn, '%Y-%m-%d')>=@beginTme  ");
            }
            if (searchModel.EndTime != null && searchModel.EndTime != DateTime.MinValue)
            {
                sql.Append(" and DATE_FORMAT(D_CreateOn, '%Y-%m-%d')<=@endTime  ");
            }
            sql.Append(" order by D_CreateOn desc ");
            IEnumerable<TsysMessageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TsysMessageViewModel>(DbContext.Database, sql.ToString(), parameters);
            searchModel.TotalCount = recordItemlist.First() != null ? recordItemlist.ToList().Count : 0;
            if (searchModel.TotalCount == 0)
            {
                recordItemlist = new List<TsysMessageViewModel>();
            }
            return Task.FromResult(searchModel.IsPagination ? recordItemlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : recordItemlist);
        }

    }
}