Advertisement
Guest User

Untitled

a guest
Mar 30th, 2020
134
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 23.14 KB | None | 0 0
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data.SqlClient;
  4. using System.Linq;
  5. using System.Text;
  6. using System.Threading.Tasks;
  7. using System.Xml.Linq;
  8. using Waypoint.Models;
  9. using Waypoint.Models.Queries;
  10. using Dapper;
  11. using Dapper.Contrib;
  12. using System.Data;
  13. using Waypoint.Models.Contracts;
  14.  
  15. namespace Waypoint.DAL
  16. {
  17. public class FleetStatusActiveTriggeredAlertsQueryHandler :
  18. IQueryHandler<FleetStatusTriggeredAlertsQuery, List<FleetStatusTriggeredAlertResult>>
  19.  
  20. {
  21. public string ConnectionString { get; set; }
  22.  
  23. public FleetStatusActiveTriggeredAlertsQueryHandler()
  24. {
  25. }
  26. public FleetStatusActiveTriggeredAlertsQueryHandler(string connectionString)
  27. {
  28. ConnectionString = connectionString;
  29. }
  30.  
  31. public async Task<List<FleetStatusTriggeredAlertResult>> Handle(FleetStatusTriggeredAlertsQuery query)
  32. {
  33. using (var sqlConnection = new SqlConnection(ConnectionString))
  34. {
  35. sqlConnection.Open();
  36.  
  37. var sqlTemplate = BuildSql(query, false);
  38. List<FleetStatusTriggeredAlertResult> list = null;
  39.  
  40. list = await sqlConnection.QueryAsync<FleetStatusTriggeredAlertResult>
  41. (sql: sqlTemplate.Sql, param: sqlTemplate.Template.Parameters)
  42. .ContinueWith(t => t.Result.ToList());
  43.  
  44. return await Task.FromResult(list);
  45. }
  46. }
  47.  
  48. private SqlTemplate BuildSql(FleetStatusTriggeredAlertsQuery query, bool isCount)
  49. {
  50. var builder = new Dapper.SqlBuilder();
  51. var sql = "";
  52. var template = builder.AddTemplate(
  53. @"
  54. ;with cte as(
  55. select ROW_NUMBER() OVER (ORDER BY ta.FirstTime desc ,a.Priority desc) RowNo,
  56. 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,
  57. a.Name AlertName, ta.VehiclePlateNo VehicleName, ta.VehiclePlateNo PlateNo, ta.Status, FirstTime, LastTime,
  58. u.FirstName +' ' + u.LastName ProcessedByName,ta.ProcessedDate,ta.Resolution, ta.AlertAction , ta.WfStatus , ta.WfLevel, ta.WfCounter,
  59. ta.FirstLat,ta.FirstLng,ta.LastLat,ta.LastLng,ta.CreationDate,ta.TenantId,ta.VehiclePlateNo,ta.FirstId,ta.FirstFleet,ta.FirstSignOnTime,ta.FirstSignOffTime,ta.FirstShiftTrips,
  60. ta.FirstShiftIncome,ta.FirstLastPickUpDate,ta.FirstLastDropOffDate,(select Name From DtcLookupAreas where ta.FirstPickUpArea = AreaCode) as FirstPickUpAreaName,ta.FirstDropOffArea,ta.FirstEngaged,ta.FirstTimeEvent,
  61. (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,
  62. (select Name from DtcLookupShifts where Code=ta.FirstShiftType) FirstShiftTypeName,ta.FirstStandBy,
  63.  
  64. ta.FirstPaidDistance,ta.FirstUnpaidDistance,ta.FirstLateSignOn,ta.FirstSpeedingEvents,ta.FirstAvgTripsInterval,ta.FirstTimeUntilFirstTrip,ta.FirstTimeAfterLastTrip,ta.FirstAirportTrips,ta.FirstSharjahEvents,ta.LastId,ta.LastFleet,ta.LastSignOnTime,
  65.  
  66. 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,
  67. (select Name from DtcLookupServiceStatus where Code=ta.LastServiceStatus) LastServiceStatusName ,ta.LastRejections,
  68.  
  69. (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,
  70.  
  71.  
  72. (CASE WHEN v.VehicleId IS NULL THEN 0 ELSE 1 END) VehicleExists,
  73. (CASE WHEN LastTime < (SELECT DATEADD(DAY, -2, GETDATE())) THEN 1 ELSE 0 END) as Old
  74.  
  75.  
  76. from FleetStatusTriggeredAlerts ta with(nolock)
  77. join FleetStatusAlerts a with(nolock) on ta.AlertId = a.AlertId
  78. join Vehicles vh on ta.VehicleId = vh.VehicleId
  79. join DtcFleetStatus dfs on vh.PlateNo = dfs.VehiclePlate
  80. left join ContractVehicleView v with(nolock) on ta.VehiclePlateNo = v.PlateNo and v.TenantId = @tenantId
  81. left join Users u with(nolock) on ta.ProcessedById = u.UserId
  82. /**where**/
  83. )
  84. select * from cte
  85.  
  86.  
  87. OPTION(RECOMPILE,MAXDOP 1)
  88. ",
  89. parameters: new { tenantId = query.TenantId, page = query.Page, pagesize = query.PageSize }
  90. );
  91. if (query.TriggeredAlertId != null)
  92. {
  93. builder.Where("ta.TriggeredAlertId = @triggeredAlertId", new { triggeredAlertId = query.TriggeredAlertId });
  94. builder.Where("ta.Status <> @Status", new { Status = 2 }); // not closed alerts
  95. }
  96. else
  97. {
  98. var weekAgo = DateTime.UtcNow.AddDays(-90);
  99. builder.Where("a.TenantId = @tenantId and FirstTime >= @weekAgo and (VehiclePlateNo is NOT NULL)", new { weekAgo });
  100. builder.Where("ta.Status <> @Status", new { Status = 2 }); // not closed alerts
  101.  
  102. if (query.VehicleState == VehicleState.Active)
  103. {
  104. var utcNow = DateTime.UtcNow.AddHours(-1);
  105. builder.Where("dfs.LynxAddedTime >= @utcNow", new { utcNow });
  106. }
  107.  
  108. if (!string.IsNullOrWhiteSpace(query.VehicleFilter))
  109. {
  110. //builder.Where("(v.Name like @vehicleFilter OR v.PlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
  111. builder.Where("(ta.VehiclePlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
  112. }
  113.  
  114. if (query.Alerts != null && query.Alerts.Count > 0)
  115. {
  116. var alertIdsJoined = string.Join(",", query.Alerts.Select(x => x.ToString()).ToArray());
  117.  
  118. builder.Where($"a.AlertId in ({alertIdsJoined})");
  119. }
  120.  
  121. if (!string.IsNullOrWhiteSpace(query.FleetType))
  122. {
  123. builder.Where("ta.LastFleet = @fleetType", new { fleetType = query.FleetType });
  124. }
  125. if (query.WorkflowStatus != null)
  126. {
  127. builder.Where("ta.WfStatus = @workflowStatus", new { workflowStatus = query.WorkflowStatus });
  128. }
  129. if (!string.IsNullOrWhiteSpace(query.TaxiType))
  130. {
  131. builder.Where("ta.LastaxiType = @taxiType", new { taxiType = query.TaxiType });
  132. }
  133. if (!string.IsNullOrWhiteSpace(query.ShiftType))
  134. {
  135. builder.Where("ta.LastShiftType = @shiftType", new { shiftType = query.ShiftType });
  136. }
  137. if (query.showAlert == false)
  138. {
  139. builder.Where("ta.IsVisible = @showAlert", new { showAlert = !query.showAlert });
  140. }
  141. if (query.BreachedOnly == true)
  142. {
  143. builder.Where("ta.IsBreachedSla = 1");
  144. }
  145. }
  146.  
  147.  
  148. sql = template.ResolveSql();
  149. return new SqlTemplate
  150. {
  151. Sql = sql,
  152. Template = template
  153. };
  154. }
  155. }
  156.  
  157. public class FleetStatusTriggeredAlertsQueryHandler :
  158. IQueryHandler<FleetStatusTriggeredAlertsQuery, List<FleetStatusTriggeredAlertResult>>
  159.  
  160. {
  161. public string ConnectionString { get; set; }
  162.  
  163. public FleetStatusTriggeredAlertsQueryHandler()
  164. {
  165. }
  166. public FleetStatusTriggeredAlertsQueryHandler(string connectionString)
  167. {
  168. ConnectionString = connectionString;
  169. }
  170.  
  171. public async Task<List<FleetStatusTriggeredAlertResult>> Handle(FleetStatusTriggeredAlertsQuery query)
  172. {
  173. using (var sqlConnection = new SqlConnection(ConnectionString))
  174. {
  175. sqlConnection.Open();
  176.  
  177. var sqlTemplate = BuildSql(query, false);
  178.  
  179. List<FleetStatusTriggeredAlertResult> list = null;
  180.  
  181. list = await sqlConnection.QueryAsync<FleetStatusTriggeredAlertResult>
  182. (sql: sqlTemplate.Sql, param: sqlTemplate.Template.Parameters,commandTimeout:180)
  183. .ContinueWith(t => {
  184. if (t.Exception?.InnerException?.Message == "No columns were selected")
  185. return new List<FleetStatusTriggeredAlertResult>();
  186. return t.Result.ToList(); });
  187.  
  188. return await Task.FromResult(list);
  189. }
  190. }
  191.  
  192.  
  193.  
  194. private SqlTemplate BuildSql(FleetStatusTriggeredAlertsQuery query, bool isCount)
  195. {
  196. var builder = new Dapper.SqlBuilder();
  197. var sql = "";
  198.  
  199. var template = builder.AddTemplate(
  200. @"declare @totalCount int = (SELECT count(*) FROM FleetStatusTriggeredAlerts as ta
  201. /**where**/
  202. )
  203.  
  204. ------------------------------------
  205. declare @lastPage int ;
  206.  
  207. if @totalCount - @pagesize * (@page - 1) < @pagesize
  208. begin
  209. set @lastPage = @totalCount - @pagesize * (@page - 1)
  210. end
  211. else
  212. begin
  213. set @lastPage = @pagesize;
  214. end;
  215. -----------------------------------
  216.  
  217.  
  218. if @pagesize * (@page - 1) < @totalCount
  219. begin
  220.  
  221. ; WITH ta AS
  222. (
  223. SELECT ta.* FROM FleetStatusTriggeredAlerts as ta with(nolock)
  224.  
  225. /**where**/
  226. ORDER BY FirstTime desc
  227. OFFSET @pagesize * (@page - 1) ROWS
  228. FETCH NEXT @lastPage ROWS ONLY
  229. )
  230. SELECT
  231.  
  232. ta.TriggeredAlertId, ta.AlertId, a.Name AlertName,ta.IsBreachedSla as IsBreachedSla,a.SlaBreachedHoursCount as AlertSlaBreachedHoursCount,
  233. ta.VehiclePlateNo VehicleName, ta.VehiclePlateNo PlateNo, ta.Status, FirstTime, LastTime,
  234. ta.ProcessedDate,ta.Resolution, ta.AlertAction , ta.WfStatus , ta.WfLevel, ta.WfCounter,
  235. ta.FirstLat,ta.FirstLng,ta.LastLat,ta.LastLng,ta.CreationDate,ta.TenantId,ta.VehiclePlateNo,ta.FirstId,ta.FirstFleet,ta.FirstSignOnTime,ta.FirstSignOffTime,ta.FirstShiftTrips,
  236. ta.FirstShiftIncome,ta.FirstLastPickUpDate,ta.FirstLastDropOffDate,(select Name From DtcLookupAreas where ta.FirstPickUpArea = AreaCode) as FirstPickUpAreaName,ta.FirstDropOffArea,ta.FirstEngaged,ta.FirstTimeEvent,
  237. (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,
  238. (select Name from DtcLookupShifts where Code=ta.FirstShiftType) FirstShiftTypeName,ta.FirstStandBy,
  239.  
  240. ta.FirstPaidDistance,ta.FirstUnpaidDistance,ta.FirstLateSignOn,ta.FirstSpeedingEvents,ta.FirstAvgTripsInterval,ta.FirstTimeUntilFirstTrip,ta.FirstTimeAfterLastTrip,ta.FirstAirportTrips,ta.FirstSharjahEvents,ta.LastId,ta.LastFleet,ta.LastSignOnTime,
  241.  
  242. 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,
  243. (select Name from DtcLookupServiceStatus where Code=ta.LastServiceStatus) LastServiceStatusName ,ta.LastRejections,
  244.  
  245. (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,
  246.  
  247.  
  248. ISNULL(CAST(v.VehicleId as VARCHAR(20)), ta.VehiclePlateNo) VehicleId,
  249. (CASE WHEN v.VehicleId IS NULL THEN 0 ELSE 1 END) VehicleExists
  250. ,u.FirstName +' ' + u.LastName ProcessedByName
  251.  
  252. FROM ta
  253. join FleetStatusAlerts a with(nolock) on ta.AlertId = a.AlertId
  254. left join ContractVehicleView v with(nolock) on ta.VehiclePlateNo = v.PlateNo and v.TenantId = @tenantId
  255. left join Users u with(nolock) on ta.ProcessedById = u.UserId
  256.  
  257.  
  258. OPTION(RECOMPILE,MAXDOP 1)
  259. end
  260.  
  261. ;
  262. ",
  263. parameters: new { tenantId = query.TenantId, page = query.Page, pagesize = query.PageSize }
  264. );
  265. if (query.TriggeredAlertId != null)
  266. {
  267. builder.Where("ta.TriggeredAlertId = @triggeredAlertId", new { triggeredAlertId = query.TriggeredAlertId });
  268. }
  269. else
  270. {
  271. //var weekAgo = DateTime.UtcNow.AddDays(-90);
  272. //and FirstTime >= @weekAgo
  273. builder.Where("ta.TenantId = @tenantId and (VehiclePlateNo is NOT NULL)");
  274.  
  275. if (!string.IsNullOrWhiteSpace(query.VehicleFilter))
  276. {
  277. //builder.Where("(v.Name like @vehicleFilter OR v.PlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
  278. builder.Where("(ta.VehiclePlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
  279. }
  280.  
  281. if (query.Alerts != null && query.Alerts.Count > 0)
  282. {
  283. var alertIdsJoined = string.Join(",", query.Alerts.Select(x => x.ToString()).ToArray());
  284.  
  285. builder.Where($"ta.AlertId in ({alertIdsJoined})");
  286. }
  287.  
  288. if (!string.IsNullOrWhiteSpace(query.FleetType))
  289. {
  290. builder.Where("ta.LastFleet = @fleetType", new { fleetType = query.FleetType });
  291. }
  292. if (query.WorkflowStatus != null)
  293. {
  294. builder.Where("ta.WfStatus = @workflowStatus", new { workflowStatus = query.WorkflowStatus });
  295. }
  296. if (!string.IsNullOrWhiteSpace(query.TaxiType))
  297. {
  298. builder.Where("ta.LastaxiType = @taxiType", new { taxiType = query.TaxiType });
  299. }
  300. if (!string.IsNullOrWhiteSpace(query.ShiftType))
  301. {
  302. builder.Where("ta.LastShiftType = @shiftType", new { shiftType = query.ShiftType });
  303. }
  304. if (query.Status.HasValue)
  305. {
  306. builder.Where("ta.Status = @Status", new { Status = query.Status });
  307. }
  308.  
  309. if (query.BreachedOnly == true)
  310. {
  311. builder.Where("ta.IsBreachedSla = 1");
  312. }
  313.  
  314. if (query.From.HasValue && query.To.HasValue)
  315. {
  316. builder.Where("ta.FirstTime >= @From", new { From = query.From });
  317. builder.Where("ta.FirstTime <= @To", new { To = query.To });
  318. }
  319. }
  320. sql = template.ResolveSql();
  321. return new SqlTemplate
  322. {
  323. Sql = sql,
  324. Template = template
  325. };
  326. }
  327. }
  328.  
  329. public class FleetStatusActiveTriggeredAlertsCountQueryHandler :
  330. IQueryHandler<FleetStatusTriggeredAlertsCountQuery, int>
  331.  
  332. {
  333. public string ConnectionString { get; set; }
  334.  
  335. public FleetStatusActiveTriggeredAlertsCountQueryHandler()
  336. {
  337. }
  338. public FleetStatusActiveTriggeredAlertsCountQueryHandler(string connectionString)
  339. {
  340. ConnectionString = connectionString;
  341. }
  342.  
  343. public async Task<int> Handle(FleetStatusTriggeredAlertsCountQuery query)
  344. {
  345. using (var sqlConnection = new SqlConnection(ConnectionString))
  346. {
  347. sqlConnection.Open();
  348.  
  349. var sqlTemplate = BuildSql(query, true);
  350.  
  351. var count = await sqlConnection.ExecuteScalarAsync<int>
  352. (sql: sqlTemplate.Sql, param: sqlTemplate.Template.Parameters);
  353.  
  354. return await Task.FromResult(count);
  355. }
  356.  
  357. }
  358.  
  359. private SqlTemplate BuildSql(FleetStatusTriggeredAlertsCountQuery query, bool isCount)
  360. {
  361. var builder = new Dapper.SqlBuilder();
  362. var sql = "";
  363. var template = builder.AddTemplate(
  364. @"
  365. select count(*)
  366. from FleetStatusTriggeredAlerts ta with(nolock)
  367. join FleetStatusAlerts a with(nolock) on ta.AlertId = a.AlertId
  368. --join Vehicles v with(nolock) on ta.VehicleId = v.VehicleId
  369. /**where**/
  370.  
  371.  
  372. OPTION(RECOMPILE,MAXDOP 1)
  373. ",
  374. parameters: new { tenantId = query.TenantId }
  375. );
  376.  
  377. var weekAgo = DateTime.UtcNow.AddDays(-80);
  378. builder.Where("a.TenantId = @tenantId and FirstTime >= @weekAgo and (VehiclePlateNo is NOT NULL)", new { weekAgo });
  379. builder.Where("ta.Status <> @Status", new { Status = 2 }); // not closed alerts
  380. if (!string.IsNullOrWhiteSpace(query.VehicleFilter))
  381. {
  382. //builder.Where("(v.Name like @vehicleFilter OR v.PlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
  383. builder.Where("ta.VehiclePlateNo like @vehicleFilter", new { vehicleFilter = query.VehicleFilter.SqlLike() });
  384. }
  385.  
  386. if (query.Alerts != null && query.Alerts.Count > 0)
  387. {
  388. var alertIdsJoined = string.Join(",", query.Alerts.Select(x => x.ToString()).ToArray());
  389.  
  390. builder.Where($"a.AlertId in ({alertIdsJoined})");
  391. }
  392.  
  393. if (!string.IsNullOrWhiteSpace(query.FleetType))
  394. {
  395. builder.Where("ta.LastFleet = @fleetType", new { fleetType = query.FleetType });
  396. }
  397. if (!string.IsNullOrWhiteSpace(query.TaxiType))
  398. {
  399. builder.Where("ta.LastaxiType = @taxiType", new { taxiType = query.TaxiType });
  400. }
  401. if (!string.IsNullOrWhiteSpace(query.ShiftType))
  402. {
  403. builder.Where("ta.LastShiftType = @shiftType", new { shiftType = query.ShiftType });
  404. }
  405.  
  406. if (query.WorkflowStatus != null)
  407. {
  408. builder.Where("ta.WfStatus = @workflowStatus", new { workflowStatus = query.WorkflowStatus });
  409. }
  410.  
  411. if (query.ShowAlert == false)
  412. {
  413. builder.Where("ta.IsVisible = @showAlert", new { showAlert = !query.ShowAlert });
  414. }
  415. if (query.BreachedOnly == true)
  416. {
  417. builder.Where("ta.IsBreachedSla = 1");
  418. }
  419. sql = template.ResolveSql();
  420. return new SqlTemplate
  421. {
  422. Sql = sql,
  423. Template = template
  424. };
  425. }
  426. }
  427.  
  428.  
  429. public class FleetStatusTriggeredAlertsCountQueryHandler :
  430. IQueryHandler<FleetStatusTriggeredAlertsCountQuery, int>
  431.  
  432. {
  433. public string ConnectionString { get; set; }
  434.  
  435. public FleetStatusTriggeredAlertsCountQueryHandler()
  436. {
  437. }
  438. public FleetStatusTriggeredAlertsCountQueryHandler(string connectionString)
  439. {
  440. ConnectionString = connectionString;
  441. }
  442.  
  443. public async Task<int> Handle(FleetStatusTriggeredAlertsCountQuery query)
  444. {
  445. using (var sqlConnection = new SqlConnection(ConnectionString))
  446. {
  447. sqlConnection.Open();
  448.  
  449. SqlTemplate sqlTemplate;
  450. sqlTemplate = BuildSql(query, false);
  451.  
  452. var count = await sqlConnection.ExecuteScalarAsync<int>
  453. (sql: sqlTemplate.Sql, param: sqlTemplate.Template.Parameters);
  454.  
  455. return await Task.FromResult(count);
  456. }
  457. }
  458.  
  459. private SqlTemplate BuildSql(FleetStatusTriggeredAlertsCountQuery query, bool isCount)
  460. {
  461. var builder = new Dapper.SqlBuilder();
  462. var sql = "";
  463. var template = builder.AddTemplate(
  464. @"
  465. select count(*)
  466. from FleetStatusTriggeredAlerts ta with(nolock)
  467. join FleetStatusAlerts a with(nolock) on ta.AlertId = a.AlertId
  468. --join Vehicles v with(nolock) on ta.VehicleId = v.VehicleId
  469. /**where**/
  470.  
  471.  
  472. OPTION(RECOMPILE,MAXDOP 1)
  473. ",
  474. parameters: new { tenantId = query.TenantId }
  475. );
  476.  
  477. //var weekAgo = DateTime.UtcNow.AddDays(-80);
  478.  
  479. builder.Where("ta.TenantId = @tenantId and (VehiclePlateNo is NOT NULL)");
  480.  
  481. if (!string.IsNullOrWhiteSpace(query.VehicleFilter))
  482. {
  483. //builder.Where("(v.Name like @vehicleFilter OR v.PlateNo like @vehicleFilter)", new { vehicleFilter = query.VehicleFilter.SqlLike() });
  484. builder.Where("ta.VehiclePlateNo like @vehicleFilter", new { vehicleFilter = query.VehicleFilter.SqlLike() });
  485. }
  486.  
  487. if (query.Alerts != null && query.Alerts.Count > 0)
  488. {
  489. var alertIdsJoined = string.Join(",", query.Alerts.Select(x => x.ToString()).ToArray());
  490.  
  491. builder.Where($"ta.AlertId in ({alertIdsJoined})");
  492. }
  493.  
  494. if (!string.IsNullOrWhiteSpace(query.FleetType))
  495. {
  496. builder.Where("ta.LastFleet = @fleetType", new { fleetType = query.FleetType });
  497. }
  498. if (!string.IsNullOrWhiteSpace(query.TaxiType))
  499. {
  500. builder.Where("ta.LastaxiType = @taxiType", new { taxiType = query.TaxiType });
  501. }
  502. if (!string.IsNullOrWhiteSpace(query.ShiftType))
  503. {
  504. builder.Where("ta.LastShiftType = @shiftType", new { shiftType = query.ShiftType });
  505. }
  506.  
  507. if (query.WorkflowStatus != null)
  508. {
  509. builder.Where("ta.WfStatus = @workflowStatus", new { workflowStatus = query.WorkflowStatus });
  510. }
  511.  
  512. if (query.Status.HasValue)
  513. {
  514. builder.Where("ta.Status = @Status", new { Status = query.Status });
  515. }
  516. if (query.BreachedOnly == true)
  517. {
  518. builder.Where("ta.IsBreachedSla = 1");
  519. }
  520. if (query.From.HasValue && query.To.HasValue)
  521. {
  522. builder.Where("ta.FirstTime >= @From", new { From = query.From });
  523. builder.Where("ta.FirstTime <= @To", new { To = query.To });
  524. }
  525. sql = template.ResolveSql();
  526. return new SqlTemplate
  527. {
  528. Sql = sql,
  529. Template = template
  530. };
  531. }
  532.  
  533. }
  534.  
  535. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement