<?php

class PacketRepository extends ModelRepository
{

    private static $_singletonInstance = null;

    public function __construct()
    {
        parent::__construct('Packet');
    }

    /**
     * Returnes an initiated PacketRepository
     *
     * @return PacketRepository
     */
    public static function getInstance()
    {
        if (self::$_singletonInstance === null) {
            self::$_singletonInstance = new PacketRepository();
        }

        return self::$_singletonInstance;
    }

    /**
     * Get object by id
     *
     * @param  int $id - ID of the sending (orignating) station
     * @param  int $timestamp
     * @return Packet
     */
    public function getObjectById($id, $timestamp)
    {
        if (!isInt($id) || !isInt($timestamp)) {
            return new Packet(0);
        }
        return $this->getObjectFromSql('select * from packet where id = ? and timestamp = ?', [$id, $timestamp]);
    }

    /**
     * Get objects by id array
     *
     * @param  array[int] $valArray - [[id,timestamp],[id,timestamp],...]
     * @return array Packet
     */
    public function getObjectsByIdArray($valArray)
    {
      $params = [];
      $placeholders = [];

      // Check each value in idArray
      foreach ($valArray as $val)
      {
        if (!isInt($val[0]) || !isInt($val[1]))
        {
          return new Packet(0);
        }
        else
        {
          $placeholders[] = '(id = ? AND timestamp = ?)';
          $params[] = $val[0];
          $params[] = $val[1];
        }
      }

      $placeholderString = implode(' OR ', $placeholders);
      return $this->getObjectListFromSql('select * from packet where ' . $placeholderString, $params, 'id');
    }

    /**
     * Get object list with raw by station id for the latest 24 hours
     *
     * @param  int $stationId - ID of the sending (orignating) station
     * @param  int $limit - Number of receords to return
     * @param  int $offset - Offet where to start (for pagination)
     * @param  int $startAt - Tinestamp of the earliest packet to retreive (default 24 hours old)
     * @param  int $endAt - Timestamp of the latest packet to retreive (default now)
     * @return array
     */
    public function getObjectListWithRawByStationId($stationId, $limit, $offset, $startAt=null, $endAt=null)
    {
        if (!isInt($stationId) || !isInt($limit) || !isInt($offset)) {
            return [];
        }
        $startTime = $startAt ?? (time() - 24*60*60);
        $endTime = $endAt ?? time();

        $sql = 'select packet.* from packet packet where station_id = ? and timestamp > ?  and timestamp < ? and raw is not null order by timestamp desc, id desc limit ? offset ?';
        $parameters = [$stationId, $startTime, $endTime, $limit, $offset];
        return $this->getObjectListFromSql($sql, $parameters);
    }

    /**
     * Get object list with raw by receiver id for the latest 24 hours
     *
     * @param  mixed $receiverId - ID of the receiver (destination) station (or array of IDs)
     * @param  int $limit - Number of receords to return
     * @param  int $offset - Offet where to start (for pagination)
     * @param  int $startAt - Tinestamp of the earliest packet to retreive (default 24 hours old)
     * @param  int $endAt - Timestamp of the latest packet to retreive (default now)
     * @param  mixed $filterSource - Data source (or array of sources) to filter on (1 = APRS, 2 = CWOP, 4 = CBAPRS, 5 = OGN)
     * @return array
     */
    public function getObjectListWithRawByReceiverId($receiverId, $limit, $offset, $startAt=null, $endAt=null, $filterSource=null)
    {
      return $this->_getPacketListWithRawByReceiverId(true, $receiverId, $limit, $offset, $startAt, $endAt, $filterSource);
    }


    /**
     * Get object list with raw by receiver id for the latest 24 hours
     *
     * @param  mixed $receiverId - ID of the receiver (destination) station (or array of IDs)
     * @param  int $limit - Number of receords to return
     * @param  int $offset - Offet where to start (for pagination)
     * @param  int $startAt - Tinestamp of the earliest packet to retreive (default 24 hours old)
     * @param  int $endAt - Timestamp of the latest packet to retreive (default now)
     * @param  mixed $filterSource - Data source (or array of sources) to filter on (1 = APRS, 2 = CWOP, 4 = CBAPRS, 5 = OGN)
     * @return array
     */
    public function getAssocListWithRawByReceiverId($receiverId, $limit, $offset, $startAt=null, $endAt=null, $filterSource=null)
    {
      return $this->_getPacketListWithRawByReceiverId(false, $receiverId, $limit, $offset, $startAt, $endAt, $filterSource);
    }


    /**
     * Helper to return either object or packet list with raw by receiver id for the latest 24 hours
     *
     * @param  mixed $receiverId - ID of the receiver (destination) station (or array of IDs)
     * @param  int $limit - Number of receords to return
     * @param  int $offset - Offet where to start (for pagination)
     * @param  int $startAt - Tinestamp of the earliest packet to retreive (default 24 hours old)
     * @param  int $endAt - Timestamp of the latest packet to retreive (default now)
     * @param  mixed $filterSource - Data source (or array of sources) to filter on (1 = APRS, 2 = CWOP, 4 = CBAPRS, 5 = OGN)
     * @return array
     */
    private function _getPacketListWithRawByReceiverId($returnObject, $receiverId, $limit, $offset, $startAt=null, $endAt=null, $filterSource=null)
    {
      if ((!is_array($receiverId) && (!is_array($receiverId) && !isInt($receiverId))) || !isInt($limit) || !isInt($offset))
      {
          return [];
      }

      if (is_array($receiverId)) $receiverIdList = implode(',', $receiverId);
      else $receiverIdList = $receiverId;

      $startTime = $startAt ?? (time() - 4*60*60);
      $endTime = $endAt ?? time();

      // Filter sources
      $filter_query = null;
      $filter_data = null;
      if (!is_null($filterSource))
      {
        if (is_array($filterSource))
        {
          $filter_query = 'and source_id IN (' . implode(',', $filterSource) . ')';
        }
        else if (isInt($filterSource) && $filterSource > 0)
        {
          $filter_query = 'and source_id = ' . $filterSource;
        }
      }

      $sql = 'select packet.* from packet packet where packet.receiver_id IN('.$receiverIdList.') and packet.timestamp > ?  and packet.timestamp < ? ' . $filter_query . ' and packet.raw is not null order by packet.timestamp desc, packet.id desc limit ? offset ?';
      $parameters = [$startTime, $endTime, $limit, $offset];

      if ($returnObject) return $this->getObjectListFromSql($sql, $parameters);
      else
      {
        $pdo = PDOConnection::getInstance();
        $stmt = $pdo->prepareAndExec($sql, $parameters);
        $records = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (is_array($records) && !empty($records))
        {
            return $records;
        }

        // Nothing found, return empty array
        return [];
      }
    }


    /**
     * Get latest associative list by packet type id for the latest $maxDays days
     *
     * @param  int $packetTypeId - ID of the packet type to return
     * @param  int $limit - Number of receords to return
     * @param  int $startAt - Tinestamp of the earliest packet to retreive (default 24 hours old)
     * @param  int $endAt - Timestamp of the latest packet to retreive (default now)
     * @param  mixed $filterSource - Data source (or array of sources) to filter on (1 = APRS, 2 = CWOP, 4 = CBAPRS, 5 = OGN)
     * @return array
     */
    public function getLatestAssocListByPacketTypeId($packetTypeId, $limit=50, $startAt=null, $endAt=null, $filterSource=null)
    {
        if (!isInt($packetTypeId) || !isInt($limit))
        {
            return [];
        }

        $startTime = $startAt ?? (time() - 4*60*60);
        $endTime = $endAt ?? time();

        // Filter sources
        $filter_query = null;
        $filter_data = null;
        if (!is_null($filterSource))
        {
          if (is_array($filterSource))
          {
            $filter_query = 'and p.source_id IN (' . implode(',', $filterSource) . ')';
          }
          else if (isInt($filterSource) && $filterSource > 0)
          {
            $filter_query = 'and p.source_id = ' . $filterSource;
          }
        }

        // Packet type 13 are invalid and may not be fully parsed so we attribute it to the actual station id
        if ($packetTypeId == 13)
        {
          $sql = "select p.* from packet p where p.map_sector is null and p.timestamp > ? and p.timestamp < ? and p.packet_type_id = ? $filter_query order by p.timestamp desc, p.id desc limit ?";
        }
        else
        {
          $sql = "select p.*, s.name AS sender_name, st.id AS sender_station_id from packet p LEFT JOIN sender s ON(p.sender_id = s.id) LEFT JOIN station st ON(p.sender_id = st.latest_sender_id AND s.name = st.name) where p.map_sector is null and p.timestamp > ? and p.timestamp < ? and p.packet_type_id = ? $filter_query and p.comment is not null order by p.timestamp desc, p.id desc limit ?";
        }
        $parameters = [$startTime, $endTime, $packetTypeId, $limit];

        $pdo = PDOConnection::getInstance();
        $stmt = $pdo->prepareAndExec($sql, $parameters);
        $records = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (is_array($records) && !empty($records))
        {
            return $records;
        }

        // Nothing found, return empty array
        return [];
    }


    /**
     * Get latest message associative list by station id and to call sign for the latest $maxDays days
     *
     * @param  int $limit - Number of receords to return
     * @param  int $startAt - Tinestamp of the earliest packet to retreive (default 24 hours old)
     * @param  int $endAt - Timestamp of the latest packet to retreive (default now)
     * @param  mixed $filterSource - Data source (or array of sources) to filter on (1 = APRS, 2 = CWOP, 4 = CBAPRS, 5 = OGN)
     * @return array
     */
    public function getLatestMessageAssocList($limit=50, $startAt=null, $endAt=null, $filterSource=null)
    {
        if (!isInt($limit))
        {
            return [];
        }

        $startTime = $startAt ?? (time() - 4*60*60);
        $endTime = $endAt ?? time();

        // Filter sources
        $filter_query = null;
        $filter_data = null;
        if (!is_null($filterSource))
        {
          if (is_array($filterSource))
          {
            $filter_query = 'and source_id IN (' . implode(',', $filterSource) . ')';
          }
          else if (isInt($filterSource) && $filterSource > 0)
          {
            $filter_query = 'and source_id = ' . $filterSource;
          }
        }

        $sql = "select packet.* from packet packet where map_sector is null and timestamp > ? and timestamp < ? and packet_type_id = 7 $filter_query and addresse not like 'BLN%' and comment is not null order by timestamp desc, id desc limit ?";
        $parameters = [$startTime, $endTime, $limit];

        $pdo = PDOConnection::getInstance();
        $stmt = $pdo->prepareAndExec($sql, $parameters);
        $records = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (is_array($records) && !empty($records))
        {
            return $records;
        }

        // Nothing found, return empty array
        return [];
    }


    /**
     * Get message object list by station id and to call sign for the latest $maxDays days
     *
     * @param  mixed $stationIdArray - ID or Array of ID's to query
     * @param  int $maxDays - Optional number of days (prior to now) of data to return
     * @return array
     */
    public function getAllMessagesObjectList($stationIdArray, $maxDays=30)
    {
        if (!isInt($maxDays) || !is_null($stationIdArray))
        {
            return [];
        }

        if (is_array($stationIdArray))
        {
          $stationIds = implode(',', $stationIdArray);
        }
        else
        {
          $stationIds = $stationIdArray;
        }

        $sql = "select packet.* from packet p where packet_type_id = 7 and timestamp > :ts and station_id IN(:ids) and addresse NOT LIKE '%BLN%' and comment is not null order by timestamp desc, id desc";

        $stmt = $pdo->prepare($sql);
        $stmt->bindValue(':ts', time() - (86400 * $maxDays), PDO::PARAM_INT);
        $stmt->bindValue(':ids', $stationIds, PDO::PARAM_STR);

        $stmt->execute();
        $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (is_array($records) && !empty($records))
        {
          return $this->_getObjectListFromRecords($records);
        }

        // No object found, return empty array
        return [];
    }


    /**
     * Get message object list by station id and to call sign for the latest $maxDays days
     *
     * @param  int $stationId - ID of the sending (orignating) station
     * @param  string $toStationCall - Call sign of the station
     * @param  int $limit - Number of receords to return
     * @param  int $offset - Offet where to start (for pagination)
     * @param  int $maxDays - Optioanl number of days (prior to now) of data to return
     * @return array
     */
    public function getMessageObjectListByStationIdAndCall($stationId, $toStationCall, $limit, $offset=0, $maxDays=7)
    {
        if (!isInt($stationId) || !isInt($limit) || !isInt($offset) || !isInt($maxDays) || empty($toStationCall)) {
            return [];
        }

        $sql = "select packet.* from packet packet where packet_type_id = 7 and timestamp > ? and ((station_id = ? and addresse NOT LIKE '%BLN%') or addresse = ?) and comment is not null order by timestamp desc, id desc limit ? offset ?";
        $parameters = [time() - (86400*$maxDays), $stationId, $toStationCall, $limit, $offset];
        return $this->getObjectListFromSql($sql, $parameters);
    }


    /**
     * Get the number of messages by station id and to call sign for the latest $maxDays days
     *
     * @param  int $stationId - ID of the sending (orignating) station
     * @param  string $toStationCall - Call sign of the station
     * @param  int $maxDays - Optioanl number of days (prior to now) of data to return
     * @return int
     */
    public function getNumberOfMessagesByStationIdAndCall($stationId, $toStationCall, $maxDays = 7)
    {
        if (!isInt($stationId) || !isInt($maxDays) || empty($toStationCall)) {
            return 0;
        }

        $sql = "select count(*) c from packet where packet_type_id = 7 and timestamp > ? and ((station_id = ? and addresse NOT LIKE '%BLN%') or addresse = ?) and comment is not null";
        $parameters = [time() - (86400*$maxDays), $stationId, $toStationCall];

        $pdo = PDOConnection::getInstance();
        $stmt = $pdo->prepareAndExec($sql, $parameters);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $sum = 0;
        foreach($rows as $row) {
            $sum += $row['c'];
        }

        return $sum;
    }

    /**
     * Get bulletin object list by station id for the latest $maxDays days
     *
     * @param  int $stationId - ID of the sending (orignating) station
     * @param  int $limit - Number of receords to return
     * @param  int $offset - Offet where to start (for pagination)
     * @param  int $maxDays - Optioanl number of days (prior to now) of data to return
     * @param  int $bulletinType - 0 -> ALL, '2' -> BLN0[-9], '1' -> BLNA[-Z], '3'->Any other value beginning with BLN
     * @return array
     */
    public function getBulletinObjectListByStationId($stationId, $limit, $offset=0, $maxDays=7, $bulletinType=0)
    {
        if (!isInt($stationId) || !isInt($limit) || !isInt($offset) || !isInt($maxDays) || !isInt($bulletinType)) {
            return [];
        }

        $startTime = $startAt ?? (time() - 4*60*60);
        $endTime = $endAt ?? time();

        // Handle Bulletin/Announcement Type
        $bulletinSQL = '';
        if ($bulletinType == 0)
        {
          $bulletinSQL = " and addresse LIKE '%BLN%'";
        }
        else if ($bulletinType == 2)
        {
          $bulletinSQL = " and addresse ~ '^BLN[0-9]$'";
        }
        else if ($bulletinType == 1)
        {
          $bulletinSQL = " and addresse ~ '^BLN[A-Z]$'";
        }
        else if ($bulletinType == 3)
        {
          $bulletinSQL = " and addresse !~ '^BLN[A-Z]$' and addresse !~ '^BLN[0-9]$' and addresse LIKE 'BLN%'";
        }

        $sql = "select packet.* from packet packet where station_id = ? and map_sector is null and timestamp > ? and packet_type_id = 7 $bulletinSQL and comment is not null order by timestamp desc, id desc limit ? offset ?";
        $parameters = [$stationId, time() - (86400*$maxDays), $limit, $offset];

        return $this->getObjectListFromSql($sql, $parameters);
    }

    /**
     * Get object list with raw by station id for the latest $maxDays days
     *
     * @param  int $stationId - ID of the sending (orignating) station
     * @param  int $maxDays - Optioanl number of days (prior to now) of data to return
     * @return int
     */
    public function getNumberOfBulletinsByStationId($stationId, $maxDays = 7)
    {
        if (!isInt($stationId) || !isInt($maxDays)) {
            return 0;
        }

        $sql = "select count(*) c from packet where station_id = ? and packet_type_id = 7 and timestamp > ? and addresse LIKE '%BLN%'";
        $parameters = [$stationId, time() - (86400*$maxDays)];

        $pdo = PDOConnection::getInstance();
        $stmt = $pdo->prepareAndExec($sql, $parameters);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $sum = 0;
        foreach($rows as $row) {
            $sum += $row['c'];
        }

        return $sum;
    }


    /**
     * Get message object list by station id and to call sign for the latest $maxDays days
     *
     * @param  int $limit - Number of receords to return
     * @param  int $startAt - Tinestamp of the earliest packet to retreive (default 24 hours old)
     * @param  int $endAt - Timestamp of the latest packet to retreive (default now)
     * @param  int $bulletinType - 0 -> ALL, '2' -> BLN0[-9], '1' -> BLNA[-Z], '3'->Any other value beginning with BLN
     * @return array
     */
    public function getLatestBulletinAssocList($limit=50, $startAt=null, $endAt=null, $bulletinType=0)
    {
        if (!isInt($limit) || !isInt($bulletinType))
        {
            return [];
        }

        $startTime = $startAt ?? (time() - 4*60*60);
        $endTime = $endAt ?? time();

        // Handle Bulletin/Announcement Type
        $bulletinSQL = '';
        if ($bulletinType == 0)
        {
          $bulletinSQL = " and addresse LIKE '%BLN%'";
        }
        else if ($bulletinType == 2)
        {
          $bulletinSQL = " and addresse ~ '^BLN[0-9]$'";
        }
        else if ($bulletinType == 1)
        {
          $bulletinSQL = " and addresse ~ '^BLN[A-Z]$'";
        }
        else if ($bulletinType == 3)
        {
          $bulletinSQL = " and addresse !~ '^BLN[A-Z]$' and addresse !~ '^BLN[0-9]$' and addresse LIKE 'BLN%'";
        }

        $sql = "select packet.* from packet packet where map_sector is null and timestamp > ? and timestamp < ? and packet_type_id = 7 $bulletinSQL and comment is not null order by timestamp desc, id desc limit ?";
        $parameters = [$startTime, $endTime, $limit];

        $pdo = PDOConnection::getInstance();
        $stmt = $pdo->prepareAndExec($sql, $parameters);
        $records = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (is_array($records) && !empty($records))
        {
            return $records;
        }

        // Nothing found, return empty array
        return [];
    }


    /**
     * Get number of packets with raw by station id for the latest 24 hours
     *
     * @param  int $stationId - ID of the sending (orignating) station
     * @param  int $startAt - Tinestamp of the earliest packet to retreive (default 24 hours old)
     * @param  int $endAt - Timestamp of the latest packet to retreive (default now)
     * @return int
     */
    public function getNumberOfPacketsWithRawByStationId($stationId, $startAt=null, $endAt=null)
    {
        if (!isInt($stationId)) {
            return 0;
        }
        $startTime = $startAt ?? (time() - 24*60*60);
        $endTime = $endAt ?? time();

        $sql = 'select count(*) c from packet where station_id = ? and timestamp > ? and timestamp < ? and raw is not null';
        $parameters = [$stationId, $startTime, $endTime];

        $pdo = PDOConnection::getInstance();
        $stmt = $pdo->prepareAndExec($sql, $parameters);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $sum = 0;
        foreach($rows as $row) {
            $sum += $row['c'];
        }

        return $sum;
    }

    /**
     * Get object list with raw by sender station id for the latest 24 hours
     *
     * @param  int $stationId - ID of the sending (orignating) station
     * @param  int $limit - Number of receords to return
     * @param  int $offset - Offet where to start (for pagination)
     * @param  int $startAt - Tinestamp of the earliest packet to retreive (default 24 hours old)
     * @param  int $endAt - Timestamp of the latest packet to retreive (default now)
     * @return array
     */
    public function getObjectListWithRawBySenderStationId($stationId, $limit, $offset, $startAt=null, $endAt=null)
    {
        if (!isInt($stationId) || !isInt($limit) || !isInt($offset)) {
            return [];
        }
        $startTime = $startAt ?? (time() - 24*60*60);
        $endTime = $endAt ?? time();

        $sql = 'select packet.* from packet where sender_id in (select latest_sender_id from station where id = ?) and timestamp > ? and timestamp < ? and raw is not null order by timestamp desc, id desc limit ? offset ?';
        $parameters = [$stationId, $startTime, $endTime, $limit, $offset];
        return $this->getObjectListFromSql($sql, $parameters);
    }

    /**
     * Get number of packets with raw by sender station id for the latest 24 hours
     *
     * @param  int $stationId - ID of the sending (orignating) station
     * @param  int $startAt - Tinestamp of the earliest packet to retreive (default 24 hours old)
     * @param  int $endAt - Timestamp of the latest packet to retreive (default now)
     * @return int
     */
    public function getNumberOfPacketsWithRawBySenderStationId($stationId, $startAt=null, $endAt=null)
    {
        if (!isInt($stationId)) {
            return 0;
        }
        $startTime = $startAt ?? (time() - 24*60*60);
        $endTime = $endAt ?? time();

        $sql = 'select count(*) c from packet where sender_id in (select latest_sender_id from station where id = ?) and timestamp > ? and timestamp < ? and raw is not null';
        $parameters = [$stationId, $startTime, $endTime];

        $pdo = PDOConnection::getInstance();
        $stmt = $pdo->prepareAndExec($sql, $parameters);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $sum = 0;
        foreach($rows as $row) {
            $sum += $row['c'];
        }
        return $sum;
    }

    /**
     * Get latest confirmed position packet object by station id
     *
     * @param  int $stationId - ID of the sending (orignating) station
     * @return Packet
     */
    public function getLatestConfirmedObjectByStationId($stationId)
    {
        if (!isInt($stationId)) {
            return new Packet(0);
        }
        $station = StationRepository::getInstance()->getObjectById($stationId);
        if ($station->isExistingObject()) {
            return $this->getObjectById($station->latestConfirmedPacketId, $station->latestConfirmedPacketTimestamp);
        }
        return new Packet(null);
    }

    /**
     * Get latest packet data list by station id  (useful for creating a chart)
     *
     * @param  int $stationId - ID of the sending (orignating) station
     * @param  int   $numberOfHours
     * @param  array $columns
     * @return Array
     */
    public function getLatestDataListByStationId($stationId, $numberOfHours, $columns)
    {
        $result = Array();
        if (!isInt($stationId) || !isInt($numberOfHours)) {
            return $result;
        }

        if (!in_array('timestamp', $columns)) {
            // Just to be sure
            $columns[] = 'timestamp';
        }

        $startTimestamp = time() - $numberOfHours*60*60;
        $sql = 'select ' . implode(',', $columns) . ', position_timestamp from packet
            where station_id = ?
                and timestamp >= ?
                and (speed is not null or altitude is not null)
                and map_id in (1,12,5,7,9)
            order by timestamp';
        $arg = [$stationId, $startTimestamp];

        $pdo = PDOConnection::getInstance();
        $stmt = $pdo->prepareAndExec($sql, $arg);
        $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($records as $record) {

            // Add value for position start if position start is within interval
            if ($record['position_timestamp'] != null && $record['position_timestamp'] < $record['timestamp'] && $record['position_timestamp'] >= $startTimestamp) {
                $posRecord = $record;
                $posRecord['timestamp'] = $posRecord['position_timestamp'];
                unset($posRecord['position_timestamp']);
                $result[] = $posRecord;
            }

            // Add value from found packet
            unset($record['position_timestamp']);
            $result[] = $record;
        }

        return $result;
    }

    /**
     * Get object list (only confirmed)
     * @param array $stationIdList
     * @param int $startTimestamp
     * @param int $endTimestamp
     * @return array
     */
    public function getConfirmedObjectListByStationIdList($stationIdList, $startTimestamp, $endTimestamp) {
        if (!isInt($startTimestamp) || !isInt($endTimestamp)) {
            return $result;
        }

        $sql = 'select * from packet where station_id in (' . implode(',', $stationIdList) . ') and timestamp > ? and timestamp < ? and map_id = 1 order by timestamp';
        $parameters = [$startTimestamp, $endTimestamp];
        return $this->getObjectListFromSql($sql, $parameters);
    }

    /**
     * Super-basic utility function to get object list from raw SQL query
     *
     * @param  int $sqlQuery
     * @param  array $queryParams
     * @return array
     */
    public function getObjectListWithByRawSQLQuery($sqlQuery, $queryParams=[])
    {
        if (empty($sqlQuery)) {
            return [];
        }

        return $this->getObjectListFromSql($sqlQuery, $queryParams);
    }


    /**
     * Get the number of invalid packets by station id for the latest $maxDays days
     *
     * @param  int $stationId - ID of the sending (orignating) station
     * @param  int $maxDays - Optioanl number of days (prior to now) of data to count
     * @return int
     */
    public function getNumberOfInvalidPacketsByStationId($stationId, $maxDays = 7)
    {
        if (!isInt($stationId) || !isInt($maxDays)) {
            return 0;
        }

        $sql = "select count(*) c from packet where station_id = ? and packet_type_id = 13 and timestamp > ?";
        $parameters = [$stationId, time() - (86400*$maxDays)];

        $pdo = PDOConnection::getInstance();
        $stmt = $pdo->prepareAndExec($sql, $parameters);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $sum = 0;
        foreach($rows as $row) {
            $sum += $row['c'];
        }

        return $sum;
    }
}
