tycg_carviolation_BE/Traffic.Repository/Implements/EventRepository.cs

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();
        }
    }
}