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 { /// /// 报警工单 /// public class TmtnAlarmOrderRepository : RepositoryBase, ITmtnAlarmOrderRepository { public TmtnAlarmOrderRepository(InspectionDbContext DbContext) : base(DbContext) { } public Task> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql.ToString(), parameters); searchModel.TotalCount = recordItemlist.First() != null ? recordItemlist.ToList().Count : 0; if (searchModel.TotalCount == 0) { recordItemlist=new List(); } return Task.FromResult(searchModel.IsPagination ? recordItemlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : recordItemlist); } public Task 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(DbContext.Database, sql, parameters).FirstOrDefault(); return Task.FromResult(record); } } /// /// 报警处理记录 /// public class TmtnAlarmOrderRecordRepository : RepositoryBase, ITmtnAlarmOrderRecordRepository { public TmtnAlarmOrderRecordRepository(InspectionDbContext DbContext) : base(DbContext) { } public Task> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); return Task.FromResult(recordItemlist); } } /// /// 报警处理记录附件 /// public class TmtnAlarmOrderRecordAppRepository : RepositoryBase, ITmtnAlarmOrderRecordAppRepository { public TmtnAlarmOrderRecordAppRepository(InspectionDbContext DbContext) : base(DbContext) { } } }