45 lines
2.0 KiB
C#
45 lines
2.0 KiB
C#
|
using Dapper;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Data;
|
|||
|
|
using Traffic.Data.Models;
|
|||
|
|
using Traffic.Repository.Interfaces;
|
|||
|
|
|
|||
|
|
namespace Traffic.Repository.Implements
|
|||
|
|
{
|
|||
|
|
public class MalfunctionRepository : IMalfunctionRepository
|
|||
|
|
{
|
|||
|
|
public IDbTransaction Transaction { get; }
|
|||
|
|
public IDbConnection Connection => Transaction.Connection;
|
|||
|
|
|
|||
|
|
public MalfunctionRepository(IDbTransaction transaction)
|
|||
|
|
{
|
|||
|
|
Transaction = transaction;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public IEnumerable<Malfunction> GetMalfunctions()
|
|||
|
|
{
|
|||
|
|
var sql = $@"SELECT m.siteID, m.companyCode, m.deviceType, m.deviceID, m.message, Max(m.timestamp) as timestamp
|
|||
|
|
FROM malfunction as m group by m.companyCode, m.deviceType, m.deviceID ";
|
|||
|
|
return Connection.Query<Malfunction>(sql, null, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public IEnumerable<Malfunction> GetMalfunctions(int userid)
|
|||
|
|
{
|
|||
|
|
var sql = $@"SELECT us.userId, s.siteid, m.companyCode, m.deviceType, m.deviceID, m.message, Max(m.timestamp) as timestamp
|
|||
|
|
FROM accountusersite as us
|
|||
|
|
left join siteinformation as s on us.siteid = s.id
|
|||
|
|
left join malfunction as m on s.siteid = m.siteID where us.userid = @userid group by us.userId,s.county,s.area, s.siteid, s.siteName, s.eventType, m.companyCode, m.deviceType, m.deviceID ";
|
|||
|
|
return Connection.Query<Malfunction>(sql, new { userid }, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public IEnumerable<Malfunction> GetMalfunctions(List<string> sitesId, string startTime, string endTime)
|
|||
|
|
{
|
|||
|
|
var sql = $@"SELECT s.siteid, s.siteName, m.*
|
|||
|
|
FROM siteinformation as s
|
|||
|
|
LEFT JOIN malfunction as m on s.siteid = m.siteID
|
|||
|
|
WHERE s.siteid in @sitesId and m.timestamp >= @startTime and m.timestamp <= @endTime";
|
|||
|
|
return Connection.Query<Malfunction>(sql, new { sitesId, startTime, endTime }, Transaction);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|