Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- using System;
- using System.Collections.Generic;
- using System.Data.SqlClient;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- using System.Xml.Linq;
- using Waypoint.Models;
- using Waypoint.Models.Queries;
- using Dapper;
- using Dapper.Contrib;
- using System.Data;
- using Waypoint.Models.Contracts;
- namespace Waypoint.DAL
- {
- public class FleetStatusActiveTriggeredAlertsQueryHandler :
- IQueryHandler<FleetStatusTriggeredAlertsQuery, List<FleetStatusTriggeredAlertResult>>
- {
- public string ConnectionString { get; set; }
- public FleetStatusActiveTriggeredAlertsQueryHandler()
- {
- }
- public FleetStatusActiveTriggeredAlertsQueryHandler(string connectionString)
- {
- ConnectionString = connectionString;
- }
- public async Task<List<FleetStatusTriggeredAlertResult>> Handle(FleetStatusTriggeredAlertsQuery query)
- {
- using (var sqlConnection = new SqlConnection(ConnectionString))
- {
- sqlConnection.Open();
- var sqlTemplate = BuildSql(query, false);
- List<FleetStatusTriggeredAlertResult> list = null;
- list = await sqlConnection.QueryAsync<FleetStatusTriggeredAlertResult>
- (sql: sqlTemplate.Sql, param: sqlTemplate.Template.Parameters)
- .ContinueWith(t => t.Result.ToList());
- return await Task.FromResult(list);
- }
- }
- private SqlTemplate BuildSql(FleetStatusTriggeredAlertsQuery query, bool isCount)
- {
- var builder = new Dapper.SqlBuilder();
- var sql = "";
- var template = builder.AddTemplate(
- @"
- ;with cte as(
- select ROW_NUMBER() OVER (ORDER BY ta.FirstTime desc ,a.Priority desc) RowNo,
- ta.TriggeredAlertId, a.AlertId, ISNULL(CAST(v.VehicleId as VARCHAR(20)), ta.VehiclePlateNo) VehicleId,ta.FirstDID , ta.LastDID ,ta.DriverPhone ,ta.IsBreachedSla as IsBreachedSla,a.SlaBreachedHoursCount as AlertSlaBreachedHoursCount,
- a.Name AlertName, ta.VehiclePlateNo VehicleName, ta.VehiclePlateNo PlateNo, ta.Status, FirstTime, LastTime,
- u.FirstName +' ' + u.LastName ProcessedByName,ta.ProcessedDate,ta.Resolution, ta.AlertAction , ta.WfStatus , ta.WfLevel, ta.WfCounter,
- ta.FirstLat,ta.FirstLng,ta.LastLat,ta.LastLng,ta.CreationDate,ta.TenantId,ta.VehiclePlateNo,ta.FirstId,ta.FirstFleet,ta.FirstSignOnTime,ta.FirstSignOffTime,ta.FirstShiftTrips,
- ta.FirstShiftIncome,ta.FirstLastPickUpDate,ta.FirstLastDropOffDate,(select Name From DtcLookupAreas where ta.FirstPickUpArea = AreaCode) as FirstPickUpAreaName,ta.FirstDropOffArea,ta.FirstEngaged,ta.FirstTimeEvent,
- (select Name from DtcLookupCarTypes where CarType=ta.FirstTaxiType and Fleet=ta.FirstFleet) as FirstTaxiTypeName,(select Name from DtcLookupServiceStatus where Code=ta.FirstServiceStatus) FirstServiceStatusName,ta.FirstRejections,
- (select Name from DtcLookupShifts where Code=ta.FirstShiftType) FirstShiftTypeName,ta.FirstStandBy,
- ta.FirstPaidDistance,ta.FirstUnpaidDistance,ta.FirstLateSignOn,ta.FirstSpeedingEvents,ta.FirstAvgTripsInterval,ta.FirstTimeUntilFirstTrip,ta.FirstTimeAfterLastTrip,ta.FirstAirportTrips,ta.FirstSharjahEvents,ta.LastId,ta.LastFleet,ta.LastSignOnTime,
- ta.LastSignOffTime,ta.LastShiftTrips,ta.LastShiftIncome,ta.LastLastPickUpDate,ta.LastLastDropOffDate,ta.LastPickUpArea,(select Name From DtcLookupAreas where ta.LastPickUpArea = AreaCode) as LastPickUpAreaName,(select Name From DtcLookupAreas where ta.LastDropOffArea = AreaCode) as LastDropOffAreaName,ta.LastEngaged,ta.LastTimeEvent,(select Name from DtcLookupCarTypes where CarType=ta.LastaxiType and Fleet=ta.LastFleet) LastaxiTypeName,
- (select Name from DtcLookupServiceStatus where Code=ta.LastServiceStatus) LastServiceStatusName ,ta.LastRejections,
- (select Name from DtcLookupShifts where Code=ta.LastShiftType) LastShiftTypeName,ta.LastStandBy,ta.LastPaidDistance,ta.LastUnpaidDistance,ta.LastLateSignOn,ta.LastSpeedingEvents,ta.LastAvgTripsInterval,ta.LastTimeUntilFirstTrip,ta.LastTimeAfterLastTrip,ta.LastAirportTrips,ta.LastSharjahEvents,
- (CASE WHEN v.VehicleId IS NULL THEN 0 ELSE 1 END) VehicleExists,
- (CASE WHEN LastTime < (SELECT DATEADD(DAY, -2, GETDATE())) THEN 1 ELSE 0 END) as Old
- from FleetStatusTriggeredAlerts ta with(nolock)
- join FleetStatusAlerts a with(nolock) on ta.AlertId = a.AlertId
- join Vehicles vh on ta.VehicleId = vh.VehicleId
- join DtcFleetStatus dfs on vh.PlateNo = dfs.VehiclePlate
- left join ContractVehicleView v with(nolock) on ta.VehiclePlateNo = v.PlateNo and v.TenantId = @tenantId
- left join Users u with(nolock) on ta.ProcessedById = u.UserId
- /**where**/
- )
- select * from cte
- OPTION(RECOMPILE,MAXDOP 1)
- ",
- parameters: new { tenantId = query.TenantId, page = query.Page, pagesize = query.PageSize }
- );
- if (query.TriggeredAlertId != null)
- {
- builder.Where("ta.TriggeredAlertId = @triggeredAlertId", new { triggeredAlertId = query.TriggeredAlertId });
- builder.Where("ta.Status <> @Status", new { Status = 2 }); // not closed alerts
- }
- else
- {
- var weekAgo = DateTime.UtcNow.AddDays(-90);
- builder.Where("a.TenantId = @tenantId and FirstTime >= @weekAgo and (VehiclePlateNo is NOT NULL)", new { weekAgo });
- builder.Where("ta.Status <> @Status", new { Status = 2 }); // not closed alerts
- if (query.VehicleState == VehicleState.Active)
- {
- var utcNow = DateTime.UtcNow.AddHours(-1);
- builder.Where("dfs.LynxAddedTime >= @utcNow", new { utcNow });
- }
- if (!string.IsNullOrWhiteSpace(query.VehicleFilter))
- {
- //builder.Where("(v.Name like @vehicleFilter OR v.PlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
- builder.Where("(ta.VehiclePlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
- }
- if (query.Alerts != null && query.Alerts.Count > 0)
- {
- var alertIdsJoined = string.Join(",", query.Alerts.Select(x => x.ToString()).ToArray());
- builder.Where($"a.AlertId in ({alertIdsJoined})");
- }
- if (!string.IsNullOrWhiteSpace(query.FleetType))
- {
- builder.Where("ta.LastFleet = @fleetType", new { fleetType = query.FleetType });
- }
- if (query.WorkflowStatus != null)
- {
- builder.Where("ta.WfStatus = @workflowStatus", new { workflowStatus = query.WorkflowStatus });
- }
- if (!string.IsNullOrWhiteSpace(query.TaxiType))
- {
- builder.Where("ta.LastaxiType = @taxiType", new { taxiType = query.TaxiType });
- }
- if (!string.IsNullOrWhiteSpace(query.ShiftType))
- {
- builder.Where("ta.LastShiftType = @shiftType", new { shiftType = query.ShiftType });
- }
- if (query.showAlert == false)
- {
- builder.Where("ta.IsVisible = @showAlert", new { showAlert = !query.showAlert });
- }
- if (query.BreachedOnly == true)
- {
- builder.Where("ta.IsBreachedSla = 1");
- }
- }
- sql = template.ResolveSql();
- return new SqlTemplate
- {
- Sql = sql,
- Template = template
- };
- }
- }
- public class FleetStatusTriggeredAlertsQueryHandler :
- IQueryHandler<FleetStatusTriggeredAlertsQuery, List<FleetStatusTriggeredAlertResult>>
- {
- public string ConnectionString { get; set; }
- public FleetStatusTriggeredAlertsQueryHandler()
- {
- }
- public FleetStatusTriggeredAlertsQueryHandler(string connectionString)
- {
- ConnectionString = connectionString;
- }
- public async Task<List<FleetStatusTriggeredAlertResult>> Handle(FleetStatusTriggeredAlertsQuery query)
- {
- using (var sqlConnection = new SqlConnection(ConnectionString))
- {
- sqlConnection.Open();
- var sqlTemplate = BuildSql(query, false);
- List<FleetStatusTriggeredAlertResult> list = null;
- list = await sqlConnection.QueryAsync<FleetStatusTriggeredAlertResult>
- (sql: sqlTemplate.Sql, param: sqlTemplate.Template.Parameters,commandTimeout:180)
- .ContinueWith(t => {
- if (t.Exception?.InnerException?.Message == "No columns were selected")
- return new List<FleetStatusTriggeredAlertResult>();
- return t.Result.ToList(); });
- return await Task.FromResult(list);
- }
- }
- private SqlTemplate BuildSql(FleetStatusTriggeredAlertsQuery query, bool isCount)
- {
- var builder = new Dapper.SqlBuilder();
- var sql = "";
- var template = builder.AddTemplate(
- @"declare @totalCount int = (SELECT count(*) FROM FleetStatusTriggeredAlerts as ta
- /**where**/
- )
- ------------------------------------
- declare @lastPage int ;
- if @totalCount - @pagesize * (@page - 1) < @pagesize
- begin
- set @lastPage = @totalCount - @pagesize * (@page - 1)
- end
- else
- begin
- set @lastPage = @pagesize;
- end;
- -----------------------------------
- if @pagesize * (@page - 1) < @totalCount
- begin
- ; WITH ta AS
- (
- SELECT ta.* FROM FleetStatusTriggeredAlerts as ta with(nolock)
- /**where**/
- ORDER BY FirstTime desc
- OFFSET @pagesize * (@page - 1) ROWS
- FETCH NEXT @lastPage ROWS ONLY
- )
- SELECT
- ta.TriggeredAlertId, ta.AlertId, a.Name AlertName,ta.IsBreachedSla as IsBreachedSla,a.SlaBreachedHoursCount as AlertSlaBreachedHoursCount,
- ta.VehiclePlateNo VehicleName, ta.VehiclePlateNo PlateNo, ta.Status, FirstTime, LastTime,
- ta.ProcessedDate,ta.Resolution, ta.AlertAction , ta.WfStatus , ta.WfLevel, ta.WfCounter,
- ta.FirstLat,ta.FirstLng,ta.LastLat,ta.LastLng,ta.CreationDate,ta.TenantId,ta.VehiclePlateNo,ta.FirstId,ta.FirstFleet,ta.FirstSignOnTime,ta.FirstSignOffTime,ta.FirstShiftTrips,
- ta.FirstShiftIncome,ta.FirstLastPickUpDate,ta.FirstLastDropOffDate,(select Name From DtcLookupAreas where ta.FirstPickUpArea = AreaCode) as FirstPickUpAreaName,ta.FirstDropOffArea,ta.FirstEngaged,ta.FirstTimeEvent,
- (select Name from DtcLookupCarTypes where CarType=ta.FirstTaxiType and Fleet=ta.FirstFleet) as FirstTaxiTypeName,(select Name from DtcLookupServiceStatus where Code=ta.FirstServiceStatus) FirstServiceStatusName,ta.FirstRejections,
- (select Name from DtcLookupShifts where Code=ta.FirstShiftType) FirstShiftTypeName,ta.FirstStandBy,
- ta.FirstPaidDistance,ta.FirstUnpaidDistance,ta.FirstLateSignOn,ta.FirstSpeedingEvents,ta.FirstAvgTripsInterval,ta.FirstTimeUntilFirstTrip,ta.FirstTimeAfterLastTrip,ta.FirstAirportTrips,ta.FirstSharjahEvents,ta.LastId,ta.LastFleet,ta.LastSignOnTime,
- ta.LastSignOffTime,ta.LastShiftTrips,ta.LastShiftIncome,ta.LastLastPickUpDate,ta.LastLastDropOffDate,ta.LastPickUpArea,(select Name From DtcLookupAreas where ta.LastPickUpArea = AreaCode) as LastPickUpAreaName,(select Name From DtcLookupAreas where ta.LastDropOffArea = AreaCode) as LastDropOffAreaName,ta.LastEngaged,ta.LastTimeEvent,(select Name from DtcLookupCarTypes where CarType=ta.LastaxiType and Fleet=ta.LastFleet) LastaxiTypeName,
- (select Name from DtcLookupServiceStatus where Code=ta.LastServiceStatus) LastServiceStatusName ,ta.LastRejections,
- (select Name from DtcLookupShifts where Code=ta.LastShiftType) LastShiftTypeName,ta.LastStandBy,ta.LastPaidDistance,ta.LastUnpaidDistance,ta.LastLateSignOn,ta.LastSpeedingEvents,ta.LastAvgTripsInterval,ta.LastTimeUntilFirstTrip,ta.LastTimeAfterLastTrip,ta.LastAirportTrips,ta.LastSharjahEvents,
- ISNULL(CAST(v.VehicleId as VARCHAR(20)), ta.VehiclePlateNo) VehicleId,
- (CASE WHEN v.VehicleId IS NULL THEN 0 ELSE 1 END) VehicleExists
- ,u.FirstName +' ' + u.LastName ProcessedByName
- FROM ta
- join FleetStatusAlerts a with(nolock) on ta.AlertId = a.AlertId
- left join ContractVehicleView v with(nolock) on ta.VehiclePlateNo = v.PlateNo and v.TenantId = @tenantId
- left join Users u with(nolock) on ta.ProcessedById = u.UserId
- OPTION(RECOMPILE,MAXDOP 1)
- end
- ;
- ",
- parameters: new { tenantId = query.TenantId, page = query.Page, pagesize = query.PageSize }
- );
- if (query.TriggeredAlertId != null)
- {
- builder.Where("ta.TriggeredAlertId = @triggeredAlertId", new { triggeredAlertId = query.TriggeredAlertId });
- }
- else
- {
- //var weekAgo = DateTime.UtcNow.AddDays(-90);
- //and FirstTime >= @weekAgo
- builder.Where("ta.TenantId = @tenantId and (VehiclePlateNo is NOT NULL)");
- if (!string.IsNullOrWhiteSpace(query.VehicleFilter))
- {
- //builder.Where("(v.Name like @vehicleFilter OR v.PlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
- builder.Where("(ta.VehiclePlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
- }
- if (query.Alerts != null && query.Alerts.Count > 0)
- {
- var alertIdsJoined = string.Join(",", query.Alerts.Select(x => x.ToString()).ToArray());
- builder.Where($"ta.AlertId in ({alertIdsJoined})");
- }
- if (!string.IsNullOrWhiteSpace(query.FleetType))
- {
- builder.Where("ta.LastFleet = @fleetType", new { fleetType = query.FleetType });
- }
- if (query.WorkflowStatus != null)
- {
- builder.Where("ta.WfStatus = @workflowStatus", new { workflowStatus = query.WorkflowStatus });
- }
- if (!string.IsNullOrWhiteSpace(query.TaxiType))
- {
- builder.Where("ta.LastaxiType = @taxiType", new { taxiType = query.TaxiType });
- }
- if (!string.IsNullOrWhiteSpace(query.ShiftType))
- {
- builder.Where("ta.LastShiftType = @shiftType", new { shiftType = query.ShiftType });
- }
- if (query.Status.HasValue)
- {
- builder.Where("ta.Status = @Status", new { Status = query.Status });
- }
- if (query.BreachedOnly == true)
- {
- builder.Where("ta.IsBreachedSla = 1");
- }
- if (query.From.HasValue && query.To.HasValue)
- {
- builder.Where("ta.FirstTime >= @From", new { From = query.From });
- builder.Where("ta.FirstTime <= @To", new { To = query.To });
- }
- }
- sql = template.ResolveSql();
- return new SqlTemplate
- {
- Sql = sql,
- Template = template
- };
- }
- }
- public class FleetStatusActiveTriggeredAlertsCountQueryHandler :
- IQueryHandler<FleetStatusTriggeredAlertsCountQuery, int>
- {
- public string ConnectionString { get; set; }
- public FleetStatusActiveTriggeredAlertsCountQueryHandler()
- {
- }
- public FleetStatusActiveTriggeredAlertsCountQueryHandler(string connectionString)
- {
- ConnectionString = connectionString;
- }
- public async Task<int> Handle(FleetStatusTriggeredAlertsCountQuery query)
- {
- using (var sqlConnection = new SqlConnection(ConnectionString))
- {
- sqlConnection.Open();
- var sqlTemplate = BuildSql(query, true);
- var count = await sqlConnection.ExecuteScalarAsync<int>
- (sql: sqlTemplate.Sql, param: sqlTemplate.Template.Parameters);
- return await Task.FromResult(count);
- }
- }
- private SqlTemplate BuildSql(FleetStatusTriggeredAlertsCountQuery query, bool isCount)
- {
- var builder = new Dapper.SqlBuilder();
- var sql = "";
- var template = builder.AddTemplate(
- @"
- select count(*)
- from FleetStatusTriggeredAlerts ta with(nolock)
- join FleetStatusAlerts a with(nolock) on ta.AlertId = a.AlertId
- --join Vehicles v with(nolock) on ta.VehicleId = v.VehicleId
- /**where**/
- OPTION(RECOMPILE,MAXDOP 1)
- ",
- parameters: new { tenantId = query.TenantId }
- );
- var weekAgo = DateTime.UtcNow.AddDays(-80);
- builder.Where("a.TenantId = @tenantId and FirstTime >= @weekAgo and (VehiclePlateNo is NOT NULL)", new { weekAgo });
- builder.Where("ta.Status <> @Status", new { Status = 2 }); // not closed alerts
- if (!string.IsNullOrWhiteSpace(query.VehicleFilter))
- {
- //builder.Where("(v.Name like @vehicleFilter OR v.PlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
- builder.Where("ta.VehiclePlateNo like @vehicleFilter", new { vehicleFilter = query.VehicleFilter.SqlLike() });
- }
- if (query.Alerts != null && query.Alerts.Count > 0)
- {
- var alertIdsJoined = string.Join(",", query.Alerts.Select(x => x.ToString()).ToArray());
- builder.Where($"a.AlertId in ({alertIdsJoined})");
- }
- if (!string.IsNullOrWhiteSpace(query.FleetType))
- {
- builder.Where("ta.LastFleet = @fleetType", new { fleetType = query.FleetType });
- }
- if (!string.IsNullOrWhiteSpace(query.TaxiType))
- {
- builder.Where("ta.LastaxiType = @taxiType", new { taxiType = query.TaxiType });
- }
- if (!string.IsNullOrWhiteSpace(query.ShiftType))
- {
- builder.Where("ta.LastShiftType = @shiftType", new { shiftType = query.ShiftType });
- }
- if (query.WorkflowStatus != null)
- {
- builder.Where("ta.WfStatus = @workflowStatus", new { workflowStatus = query.WorkflowStatus });
- }
- if (query.ShowAlert == false)
- {
- builder.Where("ta.IsVisible = @showAlert", new { showAlert = !query.ShowAlert });
- }
- if (query.BreachedOnly == true)
- {
- builder.Where("ta.IsBreachedSla = 1");
- }
- sql = template.ResolveSql();
- return new SqlTemplate
- {
- Sql = sql,
- Template = template
- };
- }
- }
- public class FleetStatusTriggeredAlertsCountQueryHandler :
- IQueryHandler<FleetStatusTriggeredAlertsCountQuery, int>
- {
- public string ConnectionString { get; set; }
- public FleetStatusTriggeredAlertsCountQueryHandler()
- {
- }
- public FleetStatusTriggeredAlertsCountQueryHandler(string connectionString)
- {
- ConnectionString = connectionString;
- }
- public async Task<int> Handle(FleetStatusTriggeredAlertsCountQuery query)
- {
- using (var sqlConnection = new SqlConnection(ConnectionString))
- {
- sqlConnection.Open();
- SqlTemplate sqlTemplate;
- sqlTemplate = BuildSql(query, false);
- var count = await sqlConnection.ExecuteScalarAsync<int>
- (sql: sqlTemplate.Sql, param: sqlTemplate.Template.Parameters);
- return await Task.FromResult(count);
- }
- }
- private SqlTemplate BuildSql(FleetStatusTriggeredAlertsCountQuery query, bool isCount)
- {
- var builder = new Dapper.SqlBuilder();
- var sql = "";
- var template = builder.AddTemplate(
- @"
- select count(*)
- from FleetStatusTriggeredAlerts ta with(nolock)
- join FleetStatusAlerts a with(nolock) on ta.AlertId = a.AlertId
- --join Vehicles v with(nolock) on ta.VehicleId = v.VehicleId
- /**where**/
- OPTION(RECOMPILE,MAXDOP 1)
- ",
- parameters: new { tenantId = query.TenantId }
- );
- //var weekAgo = DateTime.UtcNow.AddDays(-80);
- builder.Where("ta.TenantId = @tenantId and (VehiclePlateNo is NOT NULL)");
- if (!string.IsNullOrWhiteSpace(query.VehicleFilter))
- {
- //builder.Where("(v.Name like @vehicleFilter OR v.PlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
- builder.Where("ta.VehiclePlateNo like @vehicleFilter", new { vehicleFilter = query.VehicleFilter.SqlLike() });
- }
- if (query.Alerts != null && query.Alerts.Count > 0)
- {
- var alertIdsJoined = string.Join(",", query.Alerts.Select(x => x.ToString()).ToArray());
- builder.Where($"ta.AlertId in ({alertIdsJoined})");
- }
- if (!string.IsNullOrWhiteSpace(query.FleetType))
- {
- builder.Where("ta.LastFleet = @fleetType", new { fleetType = query.FleetType });
- }
- if (!string.IsNullOrWhiteSpace(query.TaxiType))
- {
- builder.Where("ta.LastaxiType = @taxiType", new { taxiType = query.TaxiType });
- }
- if (!string.IsNullOrWhiteSpace(query.ShiftType))
- {
- builder.Where("ta.LastShiftType = @shiftType", new { shiftType = query.ShiftType });
- }
- if (query.WorkflowStatus != null)
- {
- builder.Where("ta.WfStatus = @workflowStatus", new { workflowStatus = query.WorkflowStatus });
- }
- if (query.Status.HasValue)
- {
- builder.Where("ta.Status = @Status", new { Status = query.Status });
- }
- if (query.BreachedOnly == true)
- {
- builder.Where("ta.IsBreachedSla = 1");
- }
- if (query.From.HasValue && query.To.HasValue)
- {
- builder.Where("ta.FirstTime >= @From", new { From = query.From });
- builder.Where("ta.FirstTime <= @To", new { To = query.To });
- }
- sql = template.ResolveSql();
- return new SqlTemplate
- {
- Sql = sql,
- Template = template
- };
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement