1  from datetime import datetime, timedelta
  2  
  3  from django.db import connection
  4  from django.db.models import CharField, Count, Max, Q, Value
  5  
  6  from telemetry.models import FastLap, Game, Lap, Track
  7  
  8  
  9  class RacingStats:
 10      def __init__(self):
 11          pass
 12  
 13      def combos(self, type="", **kwargs):
 14          return self.driver_combos(type=type, **kwargs)
 15  
 16      def driver_combos(self, driver=None, range=30, type="circuit", **kwargs):
 17          filter = {}
 18          if driver is not None:
 19              filter["session__driver__name"] = driver
 20  
 21          # Calculate the start date based on the range
 22          start_date = datetime.now() - timedelta(days=range)
 23  
 24          laps = Lap.objects.filter(**filter)
 25          # Filter laps based on the end time within the range
 26          laps = laps.filter(session__end__gte=start_date)
 27          # group by game, track, and car
 28          if type == "circuit" or type == "":
 29              laps = laps.values(
 30                  "session__game__name", "track__name", "car__name", "session__game__id", "track__id", "car__id"
 31              )
 32              # annotate with count of laps, valid laps, and latest lap end time
 33              laps = laps.annotate(
 34                  lap_count=Count("id"), valid_lap_count=Count("id", filter=Q(valid=True)), latest_lap_end=Max("end")
 35              )
 36              if type == "circuit":
 37                  # exclude all rally games: Richard Burns Rally, Dirt Rally, Dirt Rally 2.0
 38                  laps = laps.exclude(session__game__name__in=["Richard Burns Rally", "Dirt Rally", "Dirt Rally 2.0"])
 39          elif type == "rally":
 40              laps = laps.values("session__game__name", "car__name", "session__game__id", "car__id", "track__game__id")
 41              # add a field called track__name with a hardcoded value of "Multiple"
 42              laps = laps.annotate(
 43                  track__name=Value("Multiple", output_field=CharField()),
 44                  track__id=Value(0, output_field=CharField()),
 45                  lap_count=Count("id"),
 46                  valid_lap_count=Count("id", filter=Q(valid=True)),
 47                  latest_lap_end=Max("end"),
 48              )
 49              # only include rally games: Richard Burns Rally, Dirt Rally, Dirt Rally 2.0
 50              laps = laps.filter(session__game__name__in=["Richard Burns Rally", "Dirt Rally", "Dirt Rally 2.0"])
 51          # order by latest lap end time
 52          laps = laps.order_by("-latest_lap_end")
 53  
 54          # show the sql of the query
 55          # print(laps.query)
 56  
 57          return laps
 58  
 59      def known_combos_list(self, game=None, track=None, car=None, **kwargs):
 60          laps = self.known_combos(game, track, car, **kwargs)
 61          for row in laps:
 62              yield row["track__game__name"], row["car__name"], row["track__name"], row["count"]
 63  
 64      def known_combos(self, game=None, track=None, car=None, **kwargs):
 65          filter = {}
 66          if game:
 67              filter["track__game__name"] = game
 68          if track:
 69              filter["track__name"] = track
 70          if car:
 71              filter["car__name"] = car
 72  
 73          filter["valid"] = True
 74          laps = Lap.objects.filter(**filter)
 75          # group by track and car and game
 76          laps = laps.values("track__name", "car__name", "track__game__name")
 77          laps = laps.annotate(count=Count("id"))
 78          laps = laps.order_by("track__game__name", "car__name", "track__name")
 79          return laps
 80  
 81      def fast_lap_values(self, game=None, track=None, car=None, **kwargs):
 82          filter = {}
 83          if game:
 84              filter["game__name"] = game
 85          if track:
 86              filter["track__name"] = track
 87          if car:
 88              filter["car__name"] = car
 89  
 90          filter["driver"] = None
 91  
 92          laps = FastLap.objects.filter(**filter)
 93          # group by track and car and game
 94          laps = laps.values("track__name", "car__name", "game__name")
 95          # laps = laps.annotate(count=Count("id"))
 96          laps = laps.order_by("game__name", "car__name", "track__name")
 97          return laps
 98          # for row in laps:
 99          #     yield row["track__game__name"], row["car__name"], row["track__name"], row["count"]
100  
101      def fast_laps(self, game=None, track=None, car=None, **kwargs):
102          filter = {}
103          filter["game__name"] = game
104          filter["track__name"] = track
105          filter["car__name"] = car
106  
107          laps = FastLap.objects.filter(**filter)
108          return laps
109  
110      def laps(self, game=None, track=None, car=None, driver=None, valid=None, **kwargs):
111          filter = {}
112          if game:
113              filter["track__game__name"] = game
114          if track:
115              filter["track__name"] = track
116          if car:
117              filter["car__name"] = car
118  
119          if valid is not None:
120              filter["valid"] = valid
121  
122          if driver is not None:
123              filter["session__driver__name"] = driver
124  
125          laps = Lap.objects.filter(**filter)
126          laps = laps.order_by("time")
127          # limit to 10
128          # laps = laps[:10]
129          return laps
130  
131          # for lap in laps:
132          #     yield lap
133  
134      def fast_laps_cursor(self, game=None, track=None, car=None, **kwargs):
135          where = []
136          filter_game = None
137          if game:
138              filter_game = Game.objects.get(name=game)
139          if track:
140              track = Track.objects.get(name=track)
141              where.append(f" track_id={track.pk}")
142          if car:
143              # get the first car with this name
144              car = filter_game.cars.filter(name=car).first()
145              where.append(f"car_id={car.pk}")
146  
147          where_clause = ""
148          if where:
149              where_clause = "where " + " and ".join(where)
150  
151          sql = f"select count(id) as c, track_id, car_id from telemetry_lap {where_clause} group by track_id, car_id"
152  
153          with connection.cursor() as cursor:
154              cursor.execute(sql)
155              rows = cursor.fetchall()
156  
157          return rows