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();
            StringBuilder pushMsgSql= new StringBuilder();
            if (!string.IsNullOrEmpty(searchModel.C_PushMsgToCode))
            {
                pushMsgSql.Append("  and C_PushMsgToCode=@pushMsgToCode ");
            }
            sql.Append(@"
 select * from (
 select m.*,d.C_StoreCode,d.C_Name as C_DevName,d.C_Url,d.C_StaticUrl,c.C_Name as C_MsgTypeName,ifnull(f.cut4,0) as IsVideo 
 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,count(CASE WHEN C_Type='FILE_TYP_001' THEN 1 END) as cut1,
count(CASE WHEN C_Type='FILE_TYP_002' THEN 1 END) as cut2,count(CASE WHEN C_Type='FILE_TYP_003' THEN 1 END) as cut3,
count(CASE WHEN C_Type='FILE_TYP_004' THEN 1 END) as cut4,count(CASE WHEN C_Type='FILE_TYP_005' THEN 1 END) as cut5,
count(CASE WHEN C_Type='FILE_TYP_006' THEN 1 END) as cut6
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_MsgCreateOn, '%Y-%m-%d')>=@beginTme  ");
            }
            if (searchModel.EndTime != null && searchModel.EndTime != DateTime.MinValue)
            {
                sql.Append(" and DATE_FORMAT(D_MsgCreateOn, '%Y-%m-%d')<=@endTime  ");
            }
            if (pushMsgSql.Length>0)
            {
                sql.Append($" and C_ID in (select C_MessageCode from TMTN_PushMsgResult  where C_Status='1'  {pushMsgSql.ToString()} group by C_MessageCode) ");
            }
            sql.Append(" order by D_MsgCreateOn 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);
        }

    }
}