qlhb/Ropin.Inspection.Repository/MTN/TmtnAlarmOrderRepository.cs

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

namespace Ropin.Inspection.Repository.MTN
{
    /// <summary>
    /// 报警工单
    /// </summary>
    public class TmtnAlarmOrderRepository : RepositoryBase<TMTN_AlarmOrder, string>, ITmtnAlarmOrderRepository
    {
        public TmtnAlarmOrderRepository(InspectionDbContext DbContext) : base(DbContext)
        {

        }
        public Task<IEnumerable<AlarmOrderViewModel>> GetAlarmOrderList(AlarmOrderSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("Status", searchModel.C_Status),
                new MySqlConnector.MySqlParameter("id", searchModel.C_ID),
                new MySqlConnector.MySqlParameter("messageCode", searchModel.C_MessageCode),
                new MySqlConnector.MySqlParameter("storeCode", searchModel.C_StoreCode),
                new MySqlConnector.MySqlParameter("devCode", searchModel.C_DevCode),
                new MySqlConnector.MySqlParameter("name",  "%"+searchModel.C_Name+"%"),
                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 a.*,m.C_Content,m.C_DevStoreCode,m.C_MsgTypeCode,d.C_Name as C_DevName,d.C_StoreCode,u.C_Name as C_ExamineName,c.C_Name as C_CreateName
from TMTN_AlarmOrder a
LEFT JOIN TMTN_PushMsgResult p on (a.C_PushMsgResultCode=p.C_ID)
LEFT JOIN TSYS_Message m on (a.C_MessageCode=m.C_ID)
LEFT JOIN TDEV_DevStore d on (m.C_DevStoreCode=d.C_ID)
LEFT JOIN TSYS_User u on (a.C_ExamineBy=u.C_UserID)
LEFT JOIN TSYS_User c on (a.C_CreateBy=c.C_UserID)
) tab
");
            if (!string.IsNullOrEmpty(searchModel.C_Status))
            {
                if (searchModel.C_Status.Contains(","))
                {
                    sql.Append($" where C_Status in ('{searchModel.C_Status.Replace(",","','")}')  ");
                }
                else
                {
                    sql.Append(" where C_Status=@Status  ");
                }
            }
            else
            {
                sql.Append(" where C_Status!='0' ");
            }
            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_ID))
            {
                sql.Append(" and C_ID=@id ");
            }
            if (!string.IsNullOrEmpty(searchModel.C_MessageCode))
            {
                sql.Append(" and C_MessageCode=@messageCode ");
            }
            if (!string.IsNullOrEmpty(searchModel.C_Name))
            {
                sql.Append(" and C_Name like @name ");
            }
            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<AlarmOrderViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<AlarmOrderViewModel>(DbContext.Database, sql.ToString(), parameters);           
            searchModel.TotalCount = recordItemlist.First() != null ? recordItemlist.ToList().Count : 0; 
            if (searchModel.TotalCount == 0)
            {
                recordItemlist=new List<AlarmOrderViewModel>();
            }
            return Task.FromResult(searchModel.IsPagination ? recordItemlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : recordItemlist);
        }
        public Task<RepairStatistics> GetAlarmOrderStatisticsAsync(string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("storeCode", storeCode)};
            string sql = "";
            sql = @"SELECT CASE WHEN ISNULL(SUM(if(C_Status='1',1,0))) THEN 0 ELSE SUM(if(C_Status='1',1,0)) END AS Approval,
CASE WHEN ISNULL(SUM(if(C_Status='2',1,0))) THEN 0 ELSE SUM(if(C_Status='2',1,0)) END AS Confirm ,
CASE WHEN ISNULL(SUM(if(C_Status='3',1,0))) THEN 0 ELSE SUM(if(C_Status='3',1,0)) END AS RepairOn ,
CASE WHEN ISNULL(SUM(if(C_Status='4',1,0))) THEN 0 ELSE SUM(if(C_Status='4',1,0)) END AS Complete,
CASE WHEN ISNULL(SUM(if(C_Status='5',1,0))) THEN 0 ELSE SUM(if(C_Status='5',1,0)) END AS Cancel,
CASE WHEN ISNULL(SUM(if(C_Status='6',1,0))) THEN 0 ELSE SUM(if(C_Status='6',1,0)) END AS RepairRework,  
CASE WHEN ISNULL(SUM(if(C_Status='7',1,0))) THEN 0 ELSE SUM(if(C_Status='7',1,0)) END AS RepairCompleted  
FROM (
SELECT A.D_CreateOn,A.C_Status FROM TMTN_AlarmOrder A 
LEFT JOIN TMTN_PushMsgResult p on (A.C_PushMsgResultCode=p.C_ID)
LEFT JOIN TSYS_Message m on (A.C_MessageCode=m.C_ID)
LEFT JOIN TDEV_DevStore d on (m.C_DevStoreCode=d.C_ID)
WHERE d.C_Status != '0' 
AND d.C_StoreCode =@storeCode 
AND A.D_CreateOn >= date_sub(NOW(),INTERVAL 6 MONTH)
)D
";
            RepairStatistics record;
            record = EntityFrameworkCoreExtensions.SqlQuery<RepairStatistics>(DbContext.Database, sql, parameters).FirstOrDefault();
            return Task.FromResult(record);
        }
    }
    /// <summary>
    /// 报警处理记录
    /// </summary>
    public class TmtnAlarmOrderRecordRepository : RepositoryBase<TMTN_AlarmHandleRecord, string>, ITmtnAlarmOrderRecordRepository
    {
        public TmtnAlarmOrderRecordRepository(InspectionDbContext DbContext) : base(DbContext)
        {

        }

        public Task<IEnumerable<AlarmOrderRecordViewModel>> GetAlarmOrderRecordList(AlarmOrderSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("id", searchModel.C_ID),
                new MySqlConnector.MySqlParameter("PushMsgResultCode", searchModel.C_PushMsgResultCode),
                new MySqlConnector.MySqlParameter("messageCode", searchModel.C_MessageCode)
            };
            string sql = @"select a.*,o.C_Name as C_AlarmOrderName,u.C_Name as C_CreateName
from TMTN_AlarmHandleRecord a 
LEFT JOIN TMTN_AlarmOrder o on (a.C_AlarmOrderCode=o.C_ID)
LEFT JOIN TSYS_User u on (a.C_CreateBy=u.C_UserID)  where C_AlarmOrderCode=@id order by a.D_CreateOn asc";
            if (!string.IsNullOrEmpty(searchModel.C_PushMsgResultCode))
            {
                sql = @"select a.*,o.C_Name as C_AlarmOrderName,u.C_Name as C_CreateName
from TMTN_AlarmHandleRecord a 
LEFT JOIN TMTN_AlarmOrder o on (a.C_AlarmOrderCode=o.C_ID)
LEFT JOIN TSYS_User u on (a.C_CreateBy=u.C_UserID)
where o.C_PushMsgResultCode=@PushMsgResultCode order by a.D_CreateOn asc";
            }
            if (!string.IsNullOrEmpty(searchModel.C_MessageCode))
            {
                sql = @"select a.*,o.C_Name as C_AlarmOrderName,u.C_Name as C_CreateName
from TMTN_AlarmHandleRecord a 
LEFT JOIN TMTN_AlarmOrder o on (a.C_AlarmOrderCode=o.C_ID)
LEFT JOIN TSYS_User u on (a.C_CreateBy=u.C_UserID)
where o.C_MessageCode=@messageCode order by a.D_CreateOn asc";
            }
            IEnumerable<AlarmOrderRecordViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<AlarmOrderRecordViewModel>(DbContext.Database, sql, parameters);
            return Task.FromResult(recordItemlist);
        }
    }
    /// <summary>
    /// 报警处理记录附件
    /// </summary>
    public class TmtnAlarmOrderRecordAppRepository : RepositoryBase<TMTN_AlarmHandleRecordApp, string>, ITmtnAlarmOrderRecordAppRepository
    {
        public TmtnAlarmOrderRecordAppRepository(InspectionDbContext DbContext) : base(DbContext)
        {

        }
    }
}