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("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_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)
{
}
}
}