371 lines
18 KiB
C#
371 lines
18 KiB
C#
using Dapper;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using Traffic.Data.Models;
|
|
using Traffic.Data.ViewModels;
|
|
using Traffic.Repository.Interfaces;
|
|
|
|
namespace Traffic.Repository.Implements
|
|
{
|
|
public class EventRepository : IEventRepository
|
|
{
|
|
public IDbTransaction Transaction { get; }
|
|
public IDbConnection Connection => Transaction.Connection;
|
|
|
|
public EventRepository(IDbTransaction transaction)
|
|
{
|
|
Transaction = transaction;
|
|
}
|
|
|
|
public int GetEventCount(string term)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(term))
|
|
{
|
|
var sql = $"SELECT Count(*) FROM {nameof(Event)}";
|
|
return Connection.QueryFirst<int>(sql, null, Transaction);
|
|
}
|
|
else
|
|
{
|
|
var sql = $"SELECT Count(*) FROM {nameof(Event)} WHERE number = @number";
|
|
return Connection.QueryFirst<int>(sql, new { number = term }, Transaction);
|
|
}
|
|
}
|
|
|
|
public int GetEventBySitesCount(string siteId, string term)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(term))
|
|
{
|
|
var sql = $"SELECT Count(*) FROM {nameof(Event)} WHERE siteId = @siteId";
|
|
return Connection.QueryFirst<int>(sql, new { siteId, number = term }, Transaction);
|
|
}
|
|
else
|
|
{
|
|
var sql = $"SELECT Count(*) FROM {nameof(Event)} WHERE siteId = @siteId and number = @number";
|
|
return Connection.QueryFirst<int>(sql, new { siteId, number = term }, Transaction);
|
|
}
|
|
}
|
|
|
|
public int GetThisYearEventsCount(string startTime, string endTime, string account)
|
|
{
|
|
var sql = $"SELECT Count(*) FROM {nameof(Event)} WHERE punished = 1 and uploadTime >= @startTime and uploadTime >= @endTime and account = @account";
|
|
return Connection.QueryFirst<int>(sql, new { startTime, endTime, account }, Transaction);
|
|
}
|
|
|
|
public int GetThisMonthEventsCount(string startTime, string endTime, string account)
|
|
{
|
|
var sql = $"SELECT Count(*) FROM {nameof(Event)} WHERE punished = 1 and uploadTime >= @startTime and uploadTime >= @endTime and account = @account";
|
|
return Connection.QueryFirst<int>(sql, new { startTime, endTime, account }, Transaction);
|
|
}
|
|
|
|
public int GetEventByEventTypeCount(string eventType)
|
|
{
|
|
var sql = $"SELECT Count(*) FROM {nameof(Event)} WHERE eventType = @eventType";
|
|
return Connection.QueryFirst<int>(sql, new { eventType }, Transaction);
|
|
}
|
|
|
|
public IEnumerable<ExceptionNumber> GetExceptionNumbers()
|
|
{
|
|
var sql = $"SELECT * FROM {nameof(ExceptionNumber)} ";
|
|
return Connection.Query<ExceptionNumber>(sql, null, Transaction);
|
|
}
|
|
public IEnumerable<Event> GetEvents()
|
|
{
|
|
var sql = $"SELECT * FROM {nameof(Event)} ";
|
|
return Connection.Query<Event>(sql, null, Transaction);
|
|
}
|
|
|
|
public IEnumerable<Event> GetEventsByCar(string term)
|
|
{
|
|
var sql = $@"SELECT {nameof(Event.Id)},
|
|
{nameof(Event.Number)},
|
|
{nameof(Event.SiteID)},
|
|
{nameof(Event.EventType)},
|
|
{nameof(Event.InTime)},
|
|
{nameof(Event.CheckPunish)},
|
|
{nameof(Event.Punished)},
|
|
{nameof(Event.Rejected)}
|
|
FROM {nameof(Event)} WHERE number like @term";
|
|
return Connection.Query<Event>(sql, new { term }, Transaction);
|
|
}
|
|
|
|
public IEnumerable<Event> GetEventBySites(string siteId, int pageNumber, int pageSize)
|
|
{
|
|
var sql = $"SELECT * FROM {nameof(Event)} WHERE siteId = @siteId limit @pageSize offset @offsetIndex";
|
|
return Connection.Query<Event>(sql, new { siteId, pageSize, offsetIndex = pageSize * (pageNumber - 1) }, Transaction);
|
|
}
|
|
|
|
public IEnumerable<JoinEventSite> GetEventBySitesAndEventType(List<string> sitesId, List<string> eventTypes, string startTime, string endTime)
|
|
{
|
|
var sql = $@"SELECT s.*, e.checkPunish, e.punished, e.rejected, e.ps, e.speed, e.inTime, e.outTime, e.outTime, e.number, e.vehicleType, e.Ton
|
|
FROM event as e
|
|
LEFT JOIN siteinformation s on s.siteID = e.siteID
|
|
WHERE s.siteId in @sitesId and
|
|
s.eventType in @eventTypes and
|
|
e.alarmTime >= @startTime and e.alarmTime <= @endTime";
|
|
return Connection.Query<JoinEventSite>(sql, new { sitesId, eventTypes, startTime, endTime }, Transaction);
|
|
}
|
|
|
|
public IEnumerable<Event> GetEventByEventType(string eventType, int pageNumber, int pageSize)
|
|
{
|
|
var sql = $"SELECT * FROM {nameof(Event)} WHERE eventType = @eventType limit @pageSize offset @offsetIndex";
|
|
return Connection.Query<Event>(sql, new { eventType, pageSize, offsetIndex = pageSize * (pageNumber - 1) }, Transaction);
|
|
}
|
|
|
|
public IEnumerable<Event> GetEvents(string startDatetime, string endDatetime)
|
|
{
|
|
var sql = $"SELECT * FROM {nameof(Event)} WHERE alarmTime >= @startDatetime AND alarmTime <= @endDatetime";
|
|
return Connection.Query<Event>(sql, new { startDatetime, endDatetime }, Transaction);
|
|
}
|
|
|
|
public IEnumerable<JoinEventSiteAndUser> GetEventJoinSiteAndUsers(int userId, string eventType, string startTime, string endTime)
|
|
{
|
|
var sql = $@"SELECT
|
|
e.id,
|
|
a.userId,
|
|
s.county,
|
|
s.area,
|
|
s.siteid,
|
|
s.siteName,
|
|
s.eventType,
|
|
s.laws,
|
|
e.*
|
|
FROM accountusersite as a
|
|
LEFT JOIN siteinformation as s on s.Id = a.siteid
|
|
LEFT JOIN event as e on s.siteID = e.siteID
|
|
WHERE a.userid = @userid and s.eventType = @eventType";
|
|
|
|
return Connection.Query<JoinEventSiteAndUser>(sql,
|
|
new
|
|
{
|
|
userId,
|
|
eventType,
|
|
startTime,
|
|
endTime
|
|
}, Transaction);
|
|
}
|
|
|
|
public int GetEventByTimeCount(string startTime, string endTime)
|
|
{
|
|
var sql = $@"SELECT Count(*)
|
|
FROM event
|
|
WHERE alarmTime >= @startTime and alarmTime <= @endTime ";
|
|
|
|
return Connection.QueryFirst<int>(sql,
|
|
new
|
|
{
|
|
startTime,
|
|
endTime
|
|
}, Transaction);
|
|
}
|
|
|
|
public IEnumerable<Event> GetEventByTime(string startTime, string endTime)
|
|
{
|
|
var sql = $@"SELECT *
|
|
FROM event
|
|
WHERE alarmTime >= @startTime and alarmTime <= @endTime ";
|
|
|
|
return Connection.Query<Event>(sql,
|
|
new
|
|
{
|
|
startTime,
|
|
endTime
|
|
}, Transaction);
|
|
}
|
|
|
|
public IEnumerable<VehicleTypeTable> GetVehicleType()
|
|
{
|
|
var sql = $"SELECT * FROM vehicletype";
|
|
return Connection.Query<VehicleTypeTable>(sql, null, Transaction);
|
|
}
|
|
|
|
public VehicleTypeTable GetVehicleTypebyType(string vehicletype)
|
|
{
|
|
var sql = $"SELECT * FROM vehicletype WHERE vehicletype = @vehicletype";
|
|
return Connection.QueryFirstOrDefault<VehicleTypeTable>(sql, new { vehicletype }, Transaction);
|
|
}
|
|
|
|
public Event GetEventById(int id)
|
|
{
|
|
var sql = $"SELECT * FROM {nameof(Event)} Where {nameof(Event.Id)} = @{nameof(Event.Id)}";
|
|
return Connection.QueryFirstOrDefault<Event>(sql, new { id }, Transaction);
|
|
}
|
|
|
|
public bool InsertEvent(Event data)
|
|
{
|
|
var sql = $@"INSERT INTO {nameof(Event)} (
|
|
{nameof(Event.SiteID)},
|
|
{nameof(Event.EventID)},
|
|
{nameof(Event.Channel)},
|
|
{nameof(Event.Slot)},
|
|
{nameof(Event.EventType)},
|
|
{nameof(Event.VehicleType)},
|
|
{nameof(Event.Distance)},
|
|
{nameof(Event.Speed)},
|
|
{nameof(Event.TotalPassTime)},
|
|
{nameof(Event.LimitTon)},
|
|
{nameof(Event.Ton)},
|
|
{nameof(Event.InTime)},
|
|
{nameof(Event.AlarmTime)},
|
|
{nameof(Event.OutTime)},
|
|
{nameof(Event.LPRTime)},
|
|
{nameof(Event.IMGPath1)},
|
|
{nameof(Event.IMGPath2)},
|
|
{nameof(Event.IMGPath3)},
|
|
{nameof(Event.IMGPath4)},
|
|
{nameof(Event.IMGPath5)},
|
|
{nameof(Event.IMGPath6)},
|
|
{nameof(Event.VideoPath1)},
|
|
{nameof(Event.VideoPath2)},
|
|
{nameof(Event.IsStay)},
|
|
{nameof(Event.Unsure)},
|
|
{nameof(Event.CheckPunish)},
|
|
{nameof(Event.Punished)},
|
|
{nameof(Event.CompanyCode)},
|
|
{nameof(Event.Ps)},
|
|
{nameof(Event.HandlingTime)},
|
|
{nameof(Event.UploadTime)})
|
|
VALUES(@{nameof(Event.SiteID)},
|
|
@{nameof(Event.EventID)},
|
|
@{nameof(Event.Channel)},
|
|
@{nameof(Event.Slot)},
|
|
@{nameof(Event.Number)},
|
|
@{nameof(Event.PlateNumber)},
|
|
@{nameof(Event.EventType)},
|
|
@{nameof(Event.VehicleType)},
|
|
@{nameof(Event.Distance)},
|
|
@{nameof(Event.Speed)},
|
|
@{nameof(Event.TotalPassTime)},
|
|
@{nameof(Event.LimitTon)},
|
|
@{nameof(Event.Ton)},
|
|
@{nameof(Event.InTime)},
|
|
@{nameof(Event.AlarmTime)},
|
|
@{nameof(Event.OutTime)},
|
|
@{nameof(Event.LPRTime)},
|
|
@{nameof(Event.IMGPath1)},
|
|
@{nameof(Event.IMGPath2)},
|
|
@{nameof(Event.IMGPath3)},
|
|
@{nameof(Event.IMGPath4)},
|
|
@{nameof(Event.IMGPath5)},
|
|
@{nameof(Event.IMGPath6)},
|
|
@{nameof(Event.VideoPath1)},
|
|
@{nameof(Event.VideoPath2)},
|
|
@{nameof(Event.IsStay)},
|
|
@{nameof(Event.Unsure)},
|
|
@{nameof(Event.CheckPunish)},
|
|
@{nameof(Event.Punished)},
|
|
@{nameof(Event.CompanyCode)},
|
|
@{nameof(Event.Ps)},
|
|
@{nameof(Event.HandlingTime)},
|
|
@{nameof(Event.UploadTime)})";
|
|
|
|
var result = Connection.Execute(sql, data, Transaction);
|
|
return result == 1;
|
|
}
|
|
|
|
public bool UpdateEvent(Event data)
|
|
{
|
|
var sql = $@"UPDATE {nameof(Event)}
|
|
SET {nameof(Event.SiteID)} = @{nameof(Event.SiteID)},
|
|
{nameof(Event.EventID)} = @{nameof(Event.EventID)},
|
|
{nameof(Event.Channel)} = @{nameof(Event.Channel)},
|
|
{nameof(Event.Slot)} = @{nameof(Event.Slot)},
|
|
{nameof(Event.Number)} = @{nameof(Event.Number)},
|
|
{nameof(Event.PlateNumber)} = @{nameof(Event.PlateNumber)},
|
|
{nameof(Event.EventType)} = @{nameof(Event.EventType)},
|
|
{nameof(Event.VehicleType)} = @{nameof(Event.VehicleType)},
|
|
{nameof(Event.Distance)} = @{nameof(Event.Distance)},
|
|
{nameof(Event.Speed)} = @{nameof(Event.Speed)},
|
|
{nameof(Event.TotalPassTime)} = @{nameof(Event.TotalPassTime)},
|
|
{nameof(Event.LimitTon)} = @{nameof(Event.LimitTon)},
|
|
{nameof(Event.Ton)} = @{nameof(Event.Ton)},
|
|
{nameof(Event.InTime)} = @{nameof(Event.InTime)},
|
|
{nameof(Event.AlarmTime)} = @{nameof(Event.AlarmTime)},
|
|
{nameof(Event.OutTime)} = @{nameof(Event.OutTime)},
|
|
{nameof(Event.LPRTime)} = @{nameof(Event.LPRTime)},
|
|
{nameof(Event.IMGPath1)} = @{nameof(Event.IMGPath1)},
|
|
{nameof(Event.IMGPath2)} = @{nameof(Event.IMGPath2)},
|
|
{nameof(Event.IMGPath3)} = @{nameof(Event.IMGPath3)},
|
|
{nameof(Event.IMGPath4)} = @{nameof(Event.IMGPath4)},
|
|
{nameof(Event.IMGPath5)} = @{nameof(Event.IMGPath5)},
|
|
{nameof(Event.IMGPath6)} = @{nameof(Event.IMGPath6)},
|
|
{nameof(Event.VideoPath1)} = @{nameof(Event.VideoPath1)},
|
|
{nameof(Event.VideoPath2)} = @{nameof(Event.VideoPath2)},
|
|
{nameof(Event.IsStay)} = @{nameof(Event.IsStay)},
|
|
{nameof(Event.Unsure)} = @{nameof(Event.Unsure)},
|
|
{nameof(Event.CheckPunish)} = @{nameof(Event.CheckPunish)},
|
|
{nameof(Event.Punished)} = @{nameof(Event.Punished)},
|
|
{nameof(Event.CompanyCode)} = @{nameof(Event.CompanyCode)},
|
|
{nameof(Event.Ps)} = @{nameof(Event.Ps)},
|
|
{nameof(Event.HandlingTime)} = @{nameof(Event.HandlingTime)},
|
|
{nameof(Event.UploadTime)} = @{nameof(Event.UploadTime)}
|
|
WHERE {nameof(Event.Id)} = @{nameof(Event.Id)}";
|
|
var result = Connection.Execute(sql, data, Transaction);
|
|
return result == 1;
|
|
}
|
|
|
|
public bool UpdatEventCheckPunish(string account, int eventId, string checkPunish, string law)
|
|
{
|
|
var sql = $@"UPDATE {nameof(Event)}
|
|
SET {nameof(Event.CheckPunish)} = @{nameof(Event.CheckPunish)},
|
|
{nameof(Event.Ps)} = @law,
|
|
{nameof(Event.Account)} = @{nameof(Event.Account)}
|
|
WHERE {nameof(Event.Id)} = @{nameof(Event.Id)}";
|
|
var result = Connection.Execute(sql, new { Id = eventId, account, checkPunish, law }, Transaction);
|
|
return result == 1;
|
|
}
|
|
|
|
public bool UpdatEventNoPunish(string account, int eventId, string ps)
|
|
{
|
|
var sql = $@"UPDATE {nameof(Event)}
|
|
SET {nameof(Event.Ps)} = @{nameof(Event.Ps)}, {nameof(Event.Account)} = @{nameof(Event.Account)}
|
|
WHERE {nameof(Event.Id)} = @{nameof(Event.Id)}";
|
|
var result = Connection.Execute(sql, new { Id = eventId, Ps = ps, account }, Transaction);
|
|
return result == 1;
|
|
}
|
|
|
|
public bool UpdateEventVehicleType(int id, string vehicleType)
|
|
{
|
|
var sql = $@"UPDATE {nameof(Event)}
|
|
SET {nameof(Event.VehicleType)} = @{nameof(Event.VehicleType)}
|
|
WHERE {nameof(Event.Id)} = @{nameof(Event.Id)}";
|
|
var result = Connection.Execute(sql, new { id, vehicleType }, Transaction);
|
|
return result == 1;
|
|
}
|
|
|
|
public bool UpdateEventVehicleNumber(int id, string vehicleNumber)
|
|
{
|
|
var sql = $@"UPDATE {nameof(Event)}
|
|
SET {nameof(Event.Number)} = @{nameof(Event.Number)}
|
|
WHERE {nameof(Event.Id)} = @{nameof(Event.Id)}";
|
|
var result = Connection.Execute(sql, new { id, Number = vehicleNumber }, Transaction);
|
|
return result == 1;
|
|
}
|
|
|
|
public bool DeleteEvent(int id)
|
|
{
|
|
var sql = $@"DELETE FROM {nameof(Event)} WHERE {nameof(Event.Id)} = @{nameof(Event.Id)}";
|
|
var result = Connection.Execute(sql, new { id }, Transaction);
|
|
return result == 1;
|
|
}
|
|
|
|
public bool UpdateEventVehicle(int id, string vehicleType, string vehicleNumber)
|
|
{
|
|
var sql = $@"UPDATE {nameof(Event)}
|
|
SET {nameof(Event.Number)} = @{nameof(Event.Number)},
|
|
{nameof(Event.VehicleType)} = @{nameof(Event.VehicleType)}
|
|
WHERE {nameof(Event.Id)} = @{nameof(Event.Id)}";
|
|
var result = Connection.Execute(sql, new { id, Number = vehicleNumber, vehicleType }, Transaction);
|
|
return result == 1;
|
|
}
|
|
|
|
public int GetEventJoinSiteAndUsersCount(int userId, string eventType, string startTime, string endTime)
|
|
{
|
|
throw new NotImplementedException();
|
|
}
|
|
}
|
|
}
|