#!/usr/bin/env python
# -*- coding: utf-8 -*-
#
#  Copyright 2011, De Verkeersonderneming <rits@verkeersonderneming.nl>
#
#  This file is part of PyRITS - A tool for processing and analyzing transport
#  management system data.
#
#  PyRITS is free software: you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation, either version 3 of the License, or
#  (at your option) any later version.
#
#  PyRITS is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.

"""Calculate driving times and driving delays for database Erniesoft.

The following type codes are used in the driving times algorithm. The
description of each type code is described in the table below::

     typecode |                    description
    ----------+---------------------------------------------------
            0 | Basic Sign Of Life
            1 | User Login
            2 | User Logout
            3 | Driver Switch
           10 | Activity Started
           11 | Activity Cancelled
           12 | Activity Report
           13 | Activity End
           14 | Activity Join
           15 | Activity Leave
           16 | Activity Switch
           17 | PTO alarm
           18 | PTO start
           19 | PTO end
           20 | Task Received
           21 | Task Accepted
           22 | Task Refused
           23 | Task Busy
           24 | Task Cancelled
           25 | Task Finished
           30 | Outgoing Phone Started
           31 | Incoming Phone Started
           32 | Outgoing Phone Finished
           33 | Incoming Phone Finished
           40 | Navigation Started
           41 | Navigation Cancelled
           42 | Navigation ETA update
           43 | Navigation Destination Reached
           50 | Start of Speed Limit Violation
           51 | Start of Peak RPM violation
           52 | Start of Average RPM violation
           53 | Acceleration Limit Violation
           54 | Acceleration Limit Violation End
           55 | Engine Idle Violation
           56 | Enter Geofence
           57 | Exit Geofence
           58 | Panic Alert
           59 | Maximum Activity Duration Exceeded
           60 | End of Speed Limit Violation
           61 | End of Peak RPM Lmit Violation
           62 | End of Average RPM Limit Violation
           65 | Update of Speed Limit Violation
           66 | Update of Peak RPM Limit Violation
           67 | Update of Average RPM Limit Violation
           71 | Contact ON
           72 | Contact OFF
           73 | System Shutdown
           75 | Violation of Engine Lock
           76 | Motion Alert (Vehicle Moves while contact is off)
           77 | Vehicle Immobilization
           78 | Power Disconnect (Run on Battery)
           79 | Battery Low
           82 | Driving Times State Event
           83 | Driving Times Driving Warning
           84 | Driving Times Driving Violation
           85 | Driving Times Total Driving Warning
           90 | Temperature Sensor Violation
           91 | Temperature Sensor Violation Reminder
           92 | Coolant Temperature Violation
           93 | Coolant Temperature Violation Reminder
           97 | Wrong Trailer Tethered
           98 | Trailer Tethered
           99 | Trailer Untethered
          200 | GPRS Status Info
"""

import sys
import os
import math
import datetime
import logging
import csv

import psycopg2.extras

import pyrits.config
import pyrits.std
import pyrits.HTML
import pyrits.erniesoft.query

__author__ = "Serrano Pereira"
__copyright__ = "Copyright 2011, De Verkeersonderneming"
__credits__ = ["Serrano Pereira <serrano.pereira@gmail.com>"]
__license__ = "GPL3"
__version__ = "0.1.2"
__maintainer__ = "Serrano Pereira"
__email__ = "serrano.pereira@gmail.com"
__status__ = "Production"
__date__ = "2011/11/24"


DEBUG = 0

class Standard(object):
    """Super class for :class:`DrivingDelays` and :class:`DrivingTimes`."""

    def get_vehicles_from_date_range(self, date_start=None, date_end=None):
        """Return all vehicle codes that occur in a date range."""
        cursor = self.connection.cursor()
        if not date_start:
            cursor.execute("""SELECT DISTINCT terminalcode
            FROM hist_bcacties
            WHERE terminalcode IS NOT NULL;""")
        else:
            cursor.execute("""SELECT DISTINCT terminalcode
            FROM hist_bcacties
            WHERE tijd BETWEEN '%s' AND '%s'
            AND terminalcode IS NOT NULL;""" % (date_start, date_end))
        vehicle_codes = []
        for t in cursor:
            vehicle_codes.append(t[0])
        cursor.close()
        return vehicle_codes

class DrivingDelays(Standard):
    """Driving delays calculator.

    It calculates the clean driving times, the planned driving delays, and the
    realized driving delays.
    """

    def __init__(self, connection):
        self.connection = connection
        self.location_combinations = {}
        self.reset()

    def reset(self):
        """Set variables with default values."""
        self.task_routes = {}
        self.task_delays = {}
        self.clean_driving_times = {}
        self.route_distances = {}
        self.static_tasks = []

    def __set_static_tasks(self):
        """Set a variable with all static tasks id's.

        Static tasks are tasks for which the vehicle has not moved. The task
        id's for static tasks are saved to variable `self.static_tasks`. The
        static tasks are required for method :meth:`__set_clean_driving_times`.
        """
        cursor = self.connection.cursor()
        cursor.execute("SELECT task_id FROM driving_times WHERE static = 't';")
        for task_id in cursor:
            self.static_tasks.append(task_id[0])
        cursor.close()

    def __set_clean_driving_times(self, vehicle_code):
        """Calculate the clean driving times.

        In order to calculate the clean driving times, first the driving
        distance for each route is retrieved from the database. Than this
        distances and the average driving time for a given distance is used to
        calculate the clean driving time.

        If the clean driving time is already present in the database, it will
        not calculate the clean driving time.

        It will set the clean driving time for static tasks to 0.

        .. note::

            Usage of method :meth:`__set_clean_driving_times_from_modelit` is
            favored over this method. This method was removed from the default
            routines.
        """
        # First set the static tasks.
        self.__set_static_tasks()

        # This query lists the visited locations in the realized order. This
        # is used to determine the route for each task.
        cursor = self.connection.cursor()
        cursor.execute("""SELECT r.ritregelnr, r.locatiecode
        FROM tbl_ritregels AS r
        INNER JOIN hist_bcacties AS a ON a.elipsref = r.ritregelnr
        INNER JOIN task_to_vehicle as v ON v.task_id = r.ritregelnr
        WHERE v.vehicle_code='%s'
        GROUP BY r.ritregelnr,r.locatiecode
        ORDER BY MIN(a.tijd);""" % (vehicle_code))

        # Define location A and location B for each task.
        location_a = None
        for task_id,location_b in cursor:
            # Convert location names consisting of spaces to an empty string.
            if location_b:
                location_b = location_b.strip()

            # Add a location tuple to the tuples list if both locations are set.
            if bool(location_a) and bool(location_b):
                tuple = [location_a,location_b]
                # By sorting the tuple, retrieval from the database will be
                # faster if to be retrieved tuples are sorted as well.
                tuple.sort()
                # Save the tuple.
                self.task_routes[task_id] = tuple

            # For the next loop location A is the current location.
            if location_b:
                location_a = location_b
            else:
                location_a = None

        # Set the route distance for each task from the database.
        for task_id, route in self.task_routes.iteritems():
            cursor.execute(pyrits.erniesoft.query.sget('get_route_info',
                {':location_a': route[0],
                ':location_b': route[1]}
                ))
            tuple = cursor.fetchone()
            if tuple:
                driving_time, distance = tuple
                # Save clean driving time in seconds for each task.
                if driving_time != None:
                    self.clean_driving_times[task_id] = driving_time
                # Save route distance in meters for each task.
                if distance != None:
                    self.route_distances[task_id] = distance

        # Close database cursor.
        cursor.close()

        # Calculate the clean driving time for each task based on the route
        # distances.
        for task_id, distance in self.route_distances.iteritems():
            # Set the clean driving time to 0 if it's a static task.
            if task_id in self.static_tasks:
                self.clean_driving_times[task_id] = 0
                continue
            # Skip if the clean driving time is already known.
            if task_id in self.clean_driving_times:
                continue

            # Calculate the clean driving time from the distance.
            # Convert distance to kilimeters (from meters).
            distance = distance / 1000.0
            # Calculate clean driving time in seconds (from hours).
            driving_time = pyrits.std.driving_time_from_distance(distance) * 3600.0
            # Save the clean driving time for each task to a variable.
            self.clean_driving_times[task_id] = driving_time

    def __set_clean_driving_times_from_modelit(self):
        """Sets clean driving times and planned delays using Modelit data.

        This method replaces methods :ref:`__set_clean_driving_times` and
        :ref:`calculate_planned_delays`.

        In the Erniesoft database, two values are calculated by Modelit:
            * freeflowmin
            * delaymin

        Sometimes freeflowmin is set to 0, while delaymin has a positive
        value. This means that delaymin could not be calculated, and in this
        case the value for freeflowmin is set in field delaymin.

        Sometimes the value for delaymin is negative. The cause of this is
        unknown and such values should be ignored.

        This methods uses these two values to set `clean_driving_time` and
        `planned_delay` in table driving_times.
        """
        cursor = self.connection.cursor()
        cursor2 = self.connection.cursor()

        # Set the static tasks.
        self.__set_static_tasks()

        # Get Modelit data from the database.
        cursor.execute("""SELECT r.ritregelnr, r.freeflowmin, r.delaymin
            FROM tbl_ritregels r
            WHERE r.freeflowmin IS NOT NULL
            -- It's unclear why some delays are negative, so skip these records.
            AND r.delaymin > 0;""")

        # Before saving clean driving times (minutes) and planned delays (minutes)
        # from Modelit to the driving_times table, some preprocessing needs
        # to be done.
        for task_id,freeflowmin,delaymin in cursor:
            # Sometimes freeflowmin is set to 0, while delaymin has a positive
            # value. This means that delaymin could not be calculated, and in
            # this case the value for freeflowmin is set in field delaymin.
            if freeflowmin == 0 and delaymin > 0:
                freeflowmin = delaymin
                delaymin = 'NULL'

            # Set the clean driving time to 0 if the vehicle hasn't moved.
            if task_id in self.static_tasks:
                freeflowmin = 0

            # Convert minutes to seconds.
            if isinstance(freeflowmin, float):
                freeflowmin *= 60
            if isinstance(delaymin, float):
                delaymin *= 60

            # Save clean driving times (seconds) and planned delays (seconds)
            # to table driving_times.
            cursor2.execute("""UPDATE driving_times
                SET (clean_driving_time, planned_delay) = (%s, %s)
                WHERE task_id = %d;""" % (freeflowmin, delaymin, task_id))

        self.connection.commit()

    def calculate_realized_delays(self, vehicle_code):
        """Calculate the realized driving delays.

        In order to calculate the realized delays, the clean driving times are
        defined as well and both are saved to the ``driving_times`` table in
        the database.

        .. note::

            Usage of method :meth:`calculate_delays_using_modelit` is
            favored over this method. This method was removed from the default
            routines.
        """
        # Reset all values.
        self.reset()

        # Only vehicles with a code that start with a "W" have a board computer
        # on board. Other vehicles lack the required information and are skipped.
        if not vehicle_code.startswith("W"):
            logging.info("Vehicle with code %s is skipped; codes must start with 'W'" % vehicle_code)
            return 1

        # First define clean driving times which are required for calculating
        # the delays.
        logging.info("Calculating clean driving times for vehicle %s..." % vehicle_code)
        self.__set_clean_driving_times(vehicle_code)

        logging.info("Calculating realized delays for vehicle %s..." % vehicle_code)

        # Get database cursor.
        cursor = self.connection.cursor()
        cursor2 = self.connection.cursor()

        # Calculate the delay for each task and save the delay to the database.
        cursor.execute(pyrits.erniesoft.query.sget('driving_times_from_tasks',
                {':task_ids':  str(self.task_routes.keys()).strip('[]')}))
        for task_id,real_time in cursor:
            # Get the clean driving time.
            if task_id not in self.clean_driving_times:
                continue
            clean_time = self.clean_driving_times[task_id]
            # Get the route distance.
            distance = self.route_distances[task_id]
            # Calculate the delay in seconds.
            delay = real_time - clean_time
            # If the realized delay is less than 3 minutes, set it to 0.
            if -180 < delay < 180: delay = 0
            # Save delay to database.
            cursor2.execute("""UPDATE driving_times
            SET (clean_driving_time,route_distance,delay) = (%d,%d,%d)
            WHERE task_id=%d;""" % (clean_time,distance,delay,task_id))

        # Commit the database transaction.
        self.connection.commit()

        # Close database cursor.
        cursor.close()
        cursor2.close()

        return 0

    def calculate_delays_using_modelit(self):
        """Calculate the realized driving delays.

        In order to calculate the realized delays, the clean driving times (and
        the planned delays) are first defined with method :meth:`__set_clean_driving_times_from_modelit`.
        Then the realized delays are calculated and saved to the
        ``driving_times`` table in the database.
        """
        # Reset all values.
        self.reset()

        # First define clean driving times which are required for calculating
        # the delays.
        logging.info("Setting clean driving times and planned delays...")
        self.__set_clean_driving_times_from_modelit()

        logging.info("Calculating realized delays...")

        # Get database cursor.
        cursor = self.connection.cursor()
        cursor2 = self.connection.cursor()

        # Calculate the delay for each task and save the delay to the database.
        cursor.execute("""SELECT task_id, clean_driving_time, driving_time
            FROM driving_times
            WHERE clean_driving_time IS NOT NULL
            AND driving_time IS NOT NULL;""")

        for task_id, clean_driving_time, driving_time in cursor:
            # Calculate the realized delay in seconds.
            delay = driving_time - clean_driving_time
            # If the realized delay is less than 3 minutes, set it to 0.
            if -180 < delay < 180: delay = 0
            # Save delay to database.
            cursor2.execute("""UPDATE driving_times
            SET (delay) = (%d)
            WHERE task_id = %d;""" % (delay,task_id))

        # Commit the database transaction.
        self.connection.commit()

        # Close database cursor.
        cursor.close()
        cursor2.close()

        # Finally, populate table driving_times_rides.
        logging.info("Setting driving times and delays per ride...")
        self.set_driving_times_rides()

        return 0

    def set_driving_times_rides(self):
        """Populate table :ref:`driving_times_rides <db-erniesoft-driving_times_rides>`."""
        cursor = self.connection.cursor()

        # Purge the table.
        cursor.execute("DELETE FROM driving_times_rides;")
        self.connection.commit()

        # Populate the table.
        query = """INSERT INTO driving_times_rides
            SELECT ride_id,
                MIN(task_start),
                MAX(task_end),
                SUM(clean_driving_time),
                SUM(driving_time),
                SUM(planned_delay),
                SUM(delay),
                BOOL_OR(overnight),
                BOOL_OR(static)
            FROM driving_times
            GROUP BY ride_id;"""
        cursor.execute(query)
        self.connection.commit()
        cursor.close()

    def calculate_planned_delays(self):
        """Calculate the planned driving delays.

        A task can be visualized as follows::

                        DR                DL       TT
             |-------------------------|------|----------|
            ETA                                         ETA
            Previous task                               Current task

            DR  = Driving time without delay*
            DL  = Planned delay
            TT  = Terminal time; estimated time for loading/unloading/etc.*
            ETA = Estimated Time of Arrival; these mark the estimated beginning and
                  end of a task.*

            * Value is obtained from the database.

        The formula used for calculating the planned driving delay is::

            DL = ETA(curr. task) - ETA(prev. task) - TT - DR

        The clean driving times that are required for this methods are
        obtained from the database. Hence it is required to run
        :meth:`calculate_realized_delays` before running this methods as that
        method saves the clean driving times to the database.

        .. note::

            This method is no longer part of the default routine because method
            :meth:`__set_clean_driving_times_from_modelit` saves both the
            clean driving times and the planned delays to the database.
        """
        # Reset all values.
        self.reset()

        logging.info("Calculating planned delays...")

        # Get database cursor.
        cursor = self.connection.cursor()

        # Get ride and order information, sorted by ride so that we can
        # perform calculations per ride. We need to list each task, hence the
        # "LEFT OUTER" joins.
        # Marco said that we could just sort by [ritlink,ETA], but this is not
        # reliable as the ETA's sometimes contain errors which would result in
        # wrongly ordered tasks and thus wrongly calculated planned delays.
        # 'r.volgorde' denotes the planned order, and that is what the ETA's
        # are based on.
        cursor.execute("""SELECT r.ritlink,r.ritregelnr,r.actiecode,r.locatiecode,r.eta,o.opdrachttype,c.defaultlengteuurexport,c.defaultlengteuurimport,t.clean_driving_time
        FROM tbl_ritregels AS r
        LEFT OUTER JOIN tbl_orders AS o ON r.orderlink = o.ordernr
        LEFT OUTER JOIN stm_actiecodes AS c ON c.actiecode = r.actiecode
        LEFT OUTER JOIN driving_times AS t ON t.task_id = r.ritregelnr
        ORDER BY r.ritlink, r.volgorde;""")

        # Calculate the planned delay for each ride.
        eta_prev = None
        previous_ride = None
        previous_task_str = None
        previous_location = None
        for ride_id,task_id,task_str,location,eta_current,task_type,term_time_export, \
        term_time_import,clean_driving_time in cursor:
            # Check if we entered a new ride. If so, do not use the last ETA.
            if previous_ride != ride_id:
                eta_prev = None

            # Check for required values and calculate the delay.
            if eta_prev and eta_current and clean_driving_time and task_type in ("EXPORT","IMPORT"):
                # Sometimes, the current date precedes the previous date
                # because of incorrectly defined ETA's. Handle this by either
                # skipping the task or raising an error.
                if eta_prev > eta_current:
                    #raise ValueError("The previous ETA (%s) does not precede the current ETA (%s) for task #%d." % (eta_prev,eta_current,task_id))
                    logging.info("Task #%d is skipped because the previous ETA does not precede the current ETA." % task_id)
                    # Continue with the next task.
                    eta_prev = eta_current
                    previous_ride = ride_id
                    previous_task_str = task_str
                    previous_location = location
                    continue

                # Calculate the time difference in seconds between the two ETA's.
                eta_delta = pyrits.std.total_seconds(eta_current - eta_prev)

                # There are some situation that we need to correct for if both
                # ETA's are the same.
                if eta_delta == 0:
                    # If the ETA's are the same, this will for sure result in
                    # a negative delay. So just set the delay to 0.
                    self.task_delays[task_id] = 0
                    # Continue with the next task.
                    eta_prev = eta_current
                    previous_ride = ride_id
                    previous_task_str = task_str
                    previous_location = location
                    continue

                # Calculate the planned terminal time in seconds.
                if task_type == "EXPORT":
                    # Convert hours to seconds
                    terminal_time = term_time_export * 3600.0
                elif task_type == "IMPORT":
                    # Convert hours to seconds
                    terminal_time = term_time_import * 3600.0

                # Calculate planned delay.
                delay = eta_delta - terminal_time - clean_driving_time

                # Debugging
                if DEBUG and task_id == 125229:
                    print "%s - %s - %s - %s = %s" % (eta_current, eta_prev, terminal_time, clean_driving_time, delay)
                    print "%s - %s - %s = %s" % (eta_delta/3600.0, terminal_time/3600.0, clean_driving_time/3600.0, delay/3600.0)

                # Save the delay.
                self.task_delays[task_id] = delay

            # Set variables for the next loop.
            eta_prev = eta_current
            previous_ride = ride_id
            previous_task_str = task_str
            previous_location = location

        # Calculate the planned delay for each task and save the delay to the
        # database.
        for task_id,delay in self.task_delays.iteritems():
            # Save delay to database.
            cursor.execute("UPDATE driving_times SET (planned_delay) = (%d) WHERE task_id=%d;" % (delay,task_id))
        # Commit the database transaction.
        self.connection.commit()

        # Close database cursor.
        cursor.close()

        return 0


class DrivingTimes(Standard):
    """realized driving times calculator."""

    def __init__(self, connection):
        self.connection = connection
        self.datetime_format = "%Y-%m-%d %H:%M:%S"
        self.field_names = ('ritregelnr','terminalcode','type','tijd','speed',
            'kmstand','startactie','actie','actiecode','sessionseq')
        self.reset()

    def reset(self):
        """Set variables with default values."""
        self.in_queue = False
        self.data = None
        self.tasks = []
        self.task_tuples = []
        self.driving_tuples = []
        self.driving_tuples_queue = []
        self.driving_tuple = [None,None]
        self.driving_times = {}
        self.overnight_tasks = []
        self.arrivals = []
        self.static_tasks = []

    def mark_value(self, value):
        """Returns a marked version of a variable.

        Marked variables are colored yellow in the HTML output.
        """
        return "::"+str(value)

    def unmark_value(self, value):
        """Returns the unmarked version of a marked variable."""
        if not isinstance(value, str) or not value.startswith('::'):
            return value
        value = value[2:]
        if len(value) == 19:
            try:
                value = datetime.datetime.strptime(value, self.datetime_format)
            except:
                pass
        return value

    def purge(self):
        """Purge the driving times table."""
        cursor = self.connection.cursor()
        cursor.execute("DELETE FROM driving_times;")
        self.connection.commit()
        cursor.close()

    def calculate_vehicle(self, vehicle_code, date_start=None, date_end=None):
        """Run all methods necessary for calculating the driving times.

        Calculates the realized driving times for a specific vehicle. If the
        date range is also set, it will only calculate the driving times for
        that date range.
        """
        # Reset all values.
        self.reset()

        # Create database cursor.
        cursor = self.connection.cursor(cursor_factory=psycopg2.extras.DictCursor)

        # Only vehicles with a code that start with a "W" have a board computer
        # on board. Other vehicles lack the required information and should be
        # skipped.
        if not vehicle_code.startswith("W"):
            logging.info("Vehicle with code %s is skipped; codes must start with 'W'" % vehicle_code)
            return 1

        if not (date_start and date_end):
            logging.info("Vehicle: %s" % (vehicle_code))
        else:
            logging.info("Date range: %s to %s; Vehicle: %s" % (date_start, date_end, vehicle_code))

        logging.info("Obtaining data...")

        # Select the right query and get all ride actions from the database.
        if not (date_start and date_end):
            query = pyrits.erniesoft.query.sget('actions_from_vehicle',
                {':vehicle_code': vehicle_code})
        else:
            query = pyrits.erniesoft.query.sget('actions_from_date_range',
                {':date_start': date_start,
                ':date_end': date_end,
                ':vehicle_code': vehicle_code})
        cursor.execute(query)

        logging.info("Processing data...")

        # Load all data into memory.
        self.data = pyrits.std.DataDict(cursor, self.field_names)
        cursor.close()

        # Check if any traces were found. If not, abort.
        if len(self.data) == 0:
            logging.info("No ride actions found. Nothing to do.")
            return 1

        logging.info("Calculating driving times...")

        # Define tasks.
        self.set_tasks(self.data)
        # Check if any tasks were found. Sometimes we do find actions, but
        # they are not linked to any task.
        if len(self.tasks) == 0:
            logging.info("No tasks found. Nothing to do.")
            return 1
        # Set the driving tuples.
        self.set_driving_tuples(self.data)
        # Set task tuples.
        self.set_task_tuples(self.data, self.tasks)
        # Calculate driving time for each task.
        self.set_driving_times(self.data, self.driving_tuples, self.task_tuples)
        # Set tasks for which the vehicle has not moved.
        self.set_static_tasks(self.arrivals, self.task_tuples, self.data)

        # Succesful termination.
        return 0

    def calculate_ride(self, ride_number):
        """Run all methods necessary for calculating the driving times.

        Calculates the realized driving times for a specific ride ID.
        """

        # Reset all values.
        self.reset()

        logging.info("Calculating driving times for ride #%s" % ride_number)
        logging.info("Accessing database...")

        # Connect to the database and execute the query.
        cursor = self.connection.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cursor.execute(pyrits.erniesoft.query.sget('actions_from_ride', {':ridenum': ride_number}))

        logging.info("Processing data...")

        # Load all drive data into memory.
        self.data = pyrits.std.DataDict(cursor, self.field_names)
        cursor.close()

        # Check if any actions were found. If not, abort.
        if len(self.data) == 0:
            logging.info("No actions for this ride number. Nothing to do.")
            return 1

        logging.info("Calculating driving times...")

        # Define tasks.
        self.set_tasks(self.data)
        # Check if any tasks were found. Sometimes we do find actions, but
        # they are not linked to any task.
        if len(self.tasks) == 0:
            logging.info("No tasks found. Nothing to do.")
            return 1
        # Set the driving tuples.
        self.set_driving_tuples(self.data)
        # Set task tuples.
        self.set_task_tuples(self.data, self.tasks)
        # Calculate driving times.
        self.set_driving_times(self.data, self.driving_tuples, self.task_tuples)
        # Set tasks for which the vehicle has not moved.
        self.set_static_tasks(self.arrivals, self.task_tuples, self.data)

        # Succesful termination.
        return 0

    def save_to_database(self):
        """Save the driving times to the database.

        Previously calculated driving times will be saved to a table
        ``driving_times`` in the database.
        """
        cursor = self.connection.cursor()
        cursor2 = self.connection.cursor()

        logging.info("Saving driving times to database...")
        for task_id, task_name in self.get_tasks(unique=1):
            # Check if this task has been overnight.
            overnight = 'f'
            if task_id in self.overnight_tasks:
                overnight = 't'

            # Check if this is a static task.
            static = 'f'
            if task_id in self.static_tasks:
                static = 't'

            # Get the ride number for this task.
            query = "SELECT ritlink FROM tbl_ritregels WHERE ritregelnr = %d;" % (task_id)
            cursor2.execute(query)
            ride_id = cursor2.fetchone()

            # Construct insert query.
            columns = {
                'task_start': self.task_times[task_id][0],
                'task_end': self.task_times[task_id][1],
                'driving_time': self.driving_times[task_id],
                'overnight': overnight,
                'static': static,
            }
            query = "INSERT INTO driving_times (task_id,ride_id,%%s) VALUES (%d,%d,%%s);" % (task_id, ride_id[0])
            query = pyrits.erniesoft.query.construct_insert_query(query, columns)

            # Insert the driving time into the database. Note that each task
            # should have a corresponding driving time in self.driving_times.
            # If this is not the case, the tasks are computed wrongly.
            #try:
            cursor.execute(query)
            #except:
            #    raise KeyError(task_id)

        # Commit the transaction.
        self.connection.commit()
        cursor.close()
        cursor2.close()

    def output_html(self, filename, open_file=0):
        """Save the realized driving times to a HTML file `filename`.

        If `open_file` is set to 1, the HTML file is automatically opened in
        the default web browser.
        """
        logging.info("Writing results to %s" % filename)

        # Process data for output.
        marked_data = self.mark_driving_times(self.data, self.driving_tuples, self.task_tuples, self.tasks)
        formatted_driving_times = pyrits.std.driving_times_formatted(self.tasks, self.driving_times)

        # Open output file for writing.
        f = open(filename, 'w')

        # Write HTML header.
        f_header = open(os.path.join(pyrits.std.module_path(), 'include/header.html'), 'r')
        f.writelines(f_header)
        f_header.close()
        # Write driving times to output file.
        html = pyrits.HTML.Table(formatted_driving_times, header_row = ('Taak ID','Taak','Reistijd (s)','Reistijd (h:m:s)'))
        f.writelines(html)
        # Write marked data to output file.
        field_names = list(self.field_names)
        field_names.append('taak')
        html = pyrits.HTML.Table(marked_data, header_row = field_names)
        f.writelines(html)
        # Write HTML footer.
        f_footer = open(os.path.join(pyrits.std.module_path(), 'include/footer.html'), 'r')
        f.writelines(f_footer)
        f_footer.close()

        # Close output file.
        f.close()

        # Open the HTML file in a browser.
        if open_file:
            logging.info("Opening results in web browser.")
            pyrits.std.open_in_browser(filename)

    # -----------------------------------
    # Setters

    def set_driving_tuples(self, data):
        """Define the start and end positions of actual driving.

        Driving tuples will be calculated and saved to a list
        `self.driving_tuples`. A driving tuple is a list of two integers.
        The two integers represent the start and end position respectively.
        The integers indicate row numbers of the actions list `data`.
        """
        contact_off = 0
        parking = 0
        washing = 0
        resting = 0
        departed = None
        arrived = None
        moved = 0
        driving = 0
        driving_state = None
        last_km_position = None
        last_driving_time = None
        drive_state = None
        finished_tasks = []
        # Trace types to be ignored (as indicated by T. Verduijn).
        ignored_types = (82,56,42,57)

        for i,row in enumerate(data):
            # Reset values.
            skip_current = 0

            # First we set some variables which can tell us whether the
            # vehicle is driving or not.

            # Are we driving right now? If were are not driving, this is set
            # to None.
            is_driving = self.driving_tuple[0]

            # Find out if the vehicle has moved.
            if last_km_position:
                if row['kmstand'] > last_km_position:
                    moved = 1
                else:
                    moved = 0

            # Sometimes, a started "PA" action is never ended. To account for
            # this, if parking, and we come across an action which is neither
            # "UN" or "PA", then unset parking.
            if parking:
                if row['actie'] not in ("", "UN", "PA"):
                    parking = 0

            # Find out if the vehicle has arrived.
            if row['actie'] == "AankAdres":
                if row['type'] == 10:
                    # From this point, put driving time tuples in the queue
                    # because we don't know if the driver is going to cancel
                    # this activity.
                    self.in_queue = True
                # Arrived at destination.
                elif row['type'] == 13:
                    # Arrived at destination.
                    arrived = 1
                    # Keep track of arrival actions. This is needed later by
                    # method set_static_tasks().
                    self.arrivals.append(i)
                    # The driver didn't cancel the activity, so the driving
                    # times in the queue can be cleared.
                    self.driving_tuples_queue = []
                    # Reset values.
                    departed = 0
                    self.in_queue = False
                    if is_driving:
                        # If driving, reset driving tuple. Because
                        # self.in_queue is set to False now, current driving
                        # times will be saved anyway. This is to prevent that.
                        self.driving_tuple = [None,None]
                        is_driving = False
                # Arrival cancelled.
                elif row['type'] == 11:
                    # The driver didn't arrive after all. So put the driving
                    # times from the queue in the actual list.
                    self.driving_tuples.extend(self.driving_tuples_queue)
                    # Reset values.
                    self.driving_tuples_queue = []
                    self.in_queue = False
            # Find out if the vehicle has departed.
            elif row['actie'] == "VertrAdres":
                # Departure has started.
                if row['type'] == 13:
                    if row['ritregelnr'] not in finished_tasks:
                        # We've offcially departed.
                        departed = 1
                        # We need to keep track of finished tasks because
                        # sometimes the driver tells the board computer that
                        # he departed more than once.
                        finished_tasks.append(row['ritregelnr'])
                        # Reset values.
                        arrived = 0
                        driving_state = None
            # Find out if the driver is using the driving button.
            elif row['actie'] == "DR":
                # The driver seems to use the drive button. Whenever
                # driving_state is not set to None, the driving_state has more
                # control of deciding when the vehicle is driving.
                if row['type'] == 10:
                    driving_state = 1
                elif row['type'] in (13,11):
                    driving_state = 0
            # Find out if the vehicle is parking.
            elif row['actie'] == "PA":
                if row['type'] == 10:
                    parking = 1
                elif row['type'] in (13,11):
                    parking = 0
            # Washing the vehicle?
            elif row['actie'] == "Wassen":
                if row['type'] == 10:
                    washing = 1
                elif row['type'] in (13,11):
                    washing = 0
            # Find out if the driver has decided to take a nap.
            elif row['actie'] == "NachtRust":
                if row['type'] == 10:
                    resting = 1
                elif row['type'] in (13,11):
                    resting = 0
            # "Action" is set to something else.
            else:
                # Find out if the contact is off.
                if row['type'] == 72:
                    contact_off = 1
                elif row['type'] == 71:
                    contact_off = 0
                    # The contact was just turned on, so do not log any
                    # movements for this round.
                    skip_current = 1

            # Decide when the vehicle is driving based on the set variables.
            if skip_current:
                pass
            elif is_driving and row['type'] in ignored_types:
                # Ignore these types when already driving.
                pass
            elif not contact_off and not parking and not resting and not washing:
                # The vehicle may be driving.
                if departed or departed == None:
                    # The vehicle has departed. It's now clear to decide if the
                    # vehicle is driving.
                    if driving_state:
                        # If driving_state is not set to None (the driver has
                        # used the driving button), give this variable more
                        # control over the driving boolean.
                        self.set_driving_tuple(1, i)
                    elif driving_state == 0:
                        # Check if the driver marked the end of driving.
                        self.set_driving_tuple(0, i)
                        driving_state = None
                    # The driver is not using the driving button. So decide
                    # when the vehicle is moving based on other variables.
                    elif moved:
                        # The vehicle has moved. It must be driving.
                        self.set_driving_tuple(1, i, offset=-1)
                        # Set the last driving time.
                        last_driving_time = row['tijd']
                    else:
                        # The vehicle hasn't moved. It's probably not driving.
                        self.set_driving_tuple(0, i, offset=-1)
                elif not departed:
                    # Don't ever set to driving when the vehicle has not even
                    # departed.
                    self.set_driving_tuple(0, i, offset=-1)
            else:
                # Either the contact is off, it's parking, or the driver is
                # resting. So it can't be driving.
                self.set_driving_tuple(0, i, offset=-1)

            # What's the current position of the kilometer counter?
            last_km_position = row['kmstand']

    def set_driving_tuple(self, driving, index, offset=0):
        """Construct and save a single driving tuple."""
        if driving:
            if self.driving_tuple[0] == None:
                self.driving_tuple[0] = index+offset
        else:
            if self.driving_tuple[0] == None:
                return

            if self.driving_tuple[1] == None:
                self.driving_tuple[1] = index+offset
                if self.in_queue:
                    self.driving_tuples_queue.append(self.driving_tuple)
                else:
                    self.driving_tuples.append(self.driving_tuple)
                self.driving_tuple = [None,None]
            else:
                raise ValueError

    def set_tasks(self, data):
        """Returns the tasks list (actual order).

        This method uses the order as defined in the "actions" table. The
        order defined in this table is how the tasks were actually started.

        Returns list of tuples (task_no,task_name).
        """
        self.tasks = []
        last_task = None
        for row in data:
            if row['ritregelnr'] and row['ritregelnr'] != last_task:
                last_task = row['ritregelnr']
                tuple = (row['ritregelnr'],row['actiecode'])
                self.tasks.append(tuple)

    def set_task_tuples(self, data, tasks):
        """Set the task tuples which define start and end of a task.

        Each tuple is a list [task_id, task_start_id, task_end_id]. The start
        and end id's are indexes of the main data object `data`.

        The end of the current task and the start of the next task is defined
        as the departure from an address.

        This method also keeps a list `self.overnight_tasks` to which all task
        numbers marked with an overnight are saved. By default, a task is
        marked 'overnight' if the time between contact on and contact off has
        been longer than five hours (setting 'time-diff-for-overnight').
        """
        # List which will hold all the task tuples.
        self.task_tuples = []
        # Dictionary which will hold the start and end time for each task.
        self.task_times = {}
        # List which will hold all the task for which an overnight occurred.
        self.overnight_tasks = []
        # Keep track of the last handled task.
        last_finished_task = None
        # The index of the current task in tasks list.
        current_task_index = 0
        # Set the first task to the first task ID (ritregelnr). Task ID's
        # are always linked to a single task and are unique within a ride.
        current_task = tasks[current_task_index][0]
        # Assume that every drive starts with the first task.
        task_tuple = [current_task,0,None]
        # Set the start time for the first task.
        self.task_times[current_task] = [data[0]['tijd'], None]
        # Get total number of tasks.
        n_tasks = len(tasks)
        # Last time that the contact was turned off. Required for defining
        # overnight tasks.
        last_contact_off = None

        i = None
        for i, row in enumerate(data):
            # Find out if the vehicle has departed.
            if self.has_departed(row, tasks, n_tasks, current_task_index, last_finished_task):
                # We've officially ended a task.

                # We need to keep track of the last finished task because
                # sometimes the driver tells the board computer that
                # he left the address twice or more in a row.
                last_finished_task = current_task

                # Set the end time for this task.
                if current_task in self.task_times:
                    self.task_times[current_task][1] = row['tijd']
                else:
                    # In the rare situation where a tasks is ended, but was
                    # never started, only set the end time.
                    self.task_times[current_task] = [None, row['tijd']]

                # Set the current task to the next task, if any.
                if current_task_index+1 < n_tasks:
                    # Set to next task.
                    current_task_index += 1
                    current_task = tasks[current_task_index][0]
                    # Set the start time for the next task.
                    if current_task not in self.task_times:
                        self.task_times[current_task] = [data[i+1]['tijd'], None]
                else:
                    # There is no follow up task in tasks, so set to
                    # undefined.
                    current_task = None

                # The previous task has ended, so set the end row for
                # the task tuple and add the tuple to the task tuples.
                task_tuple[2] = i
                self.task_tuples.append(task_tuple)

                # As the last task has ended, a new task is started.
                task_tuple = [current_task,i+1,None]

            # We are inside a task.
            if current_task:
                # Keep track of tasks for which there has been an overnight.
                if row['type'] == 72:
                    last_contact_off = row['tijd']
                elif row['type'] == 71:
                    if last_contact_off:
                        # Decide whether there has been an overnight (the
                        # time difference between contact on and contact
                        # off is > 5 hours).
                        time_delta = row['tijd'] - last_contact_off
                        if pyrits.std.total_seconds(time_delta) > pyrits.config.cfg.get('time-diff-for-overnight'):
                            self.overnight_tasks.append(current_task)
                        # Reset values.
                        last_contact_off = None

        if i:
            # The very last task has ended, so set the end row for the task tuple.
            task_tuple[2] = i
            self.task_tuples.append(task_tuple)

    def has_departed(self, row, tasks, len_tasks, current_task_index, last_finished_task):
        """Returns True if the vehicle has departed from a location.

        Departure marks the end of the current task and the beginning of the
        next task.
        """
        # Check if this trace is set as departure.
        if row['actie'] == "VertrAdres" and row['type'] == 13 and row['ritregelnr'] != last_finished_task:
            return True
        # If multiple rides are analyzed, it is possible that a new tasks
        # is entered, without leaving an address. In this case, end the current
        # task.
        elif row['ritregelnr'] and current_task_index+1 < len_tasks and row['ritregelnr'] == tasks[current_task_index+1][0]:
            return True
        return False

    def set_static_tasks(self, arrivals, task_tuples, data):
        """Set tasks for which the vehicle has not moved between departure and
        arrival.

        Sometimes the vehicle has not even moved before arrival at desitnation.
        For these tasks we want and expected driving time of 0.

        If the kilometer counter position at departure is the same as for
        arrival, then we assume the vehicle has not moved. Departure is defined
        as the start of a task obtained from `task_tuples` set by
        :meth:`set_task_tuples`. Arrival of each task `arrivals` was set by
        :meth:`set_driving_tuples` in variable `self.arrivals`.
        """

        # Check each arrival id.
        for arrival_id in arrivals:
            # First get the departure and task id to which this arrival id
            # belongs.
            departure_id = None
            for task,start,end in task_tuples:
                if start <= arrival_id <= end:
                    task_id = task
                    departure_id = start
                    break

            # Continue with the next arrival id if no task id was found.
            if departure_id == None:
                continue

            # If we have a departure id, we can get the kilometer counter
            # position of both departure and arrival.
            km_departure = data[departure_id]['kmstand']
            km_arrival = data[arrival_id]['kmstand']

            # Now determine if the vehicle has moved. If not, add it to our
            # list of tasks for which the vehicle has not moved.
            if km_departure == km_arrival:
                self.static_tasks.append(task_id)

    def set_driving_times(self, data, driving_tuples, task_tuples):
        """Set the driving time for each task."""
        self.driving_times = {}

        # Set driving time for all tasks (that are present in task_tuples) to 0.
        # The first item in each tuple is the task number/ritregelnr.
        # Note that we do not use self.tasks for this, but this shouldn't
        # matter because if the tasks tuples were calculated correctly, all
        # tasks should be present in task_tuples as well.
        for t in task_tuples:
            self.driving_times[t[0]] = 0

        # Calculate total driving time (in seconds) for all tasks.
        for t in driving_tuples:
            # Get the current task.
            task = self.get_taskno_from_driving_tuple(t, task_tuples)
            # If there is no task for this driving tuple, skip it.
            if not task:
                continue
            # Get the duration for the current driving time.
            time_delta = data[t[1]]['tijd'] - data[t[0]]['tijd']
            # Set driving time.
            self.driving_times[task] += pyrits.std.total_seconds(time_delta)

    # -----------------------------------
    # Getters

    def get_data(self):
        """Returns the main data object."""
        return self.data

    def get_driving_times(self):
        """Returns a list of all driving times."""
        return self.driving_times

    def get_driving_tuples(self):
        """Returns a list of all driving tuples."""
        return self.driving_tuples

    def get_tasks(self, unique=0):
        """Returns a list of all task id's.

        If `unique` is set to True, a task id's is only returned once.
        """
        if not unique:
            return self.tasks
        else:
            seen = []
            result = []
            for tuple in self.tasks:
                if tuple[0] in seen: continue
                seen.append(tuple[0])
                result.append(tuple)
            return result

    def get_task_tuples(self):
        """Returns a list of all task tuples."""
        return self.task_tuples

    def get_driving_indexes(self, driving_tuples):
        """Return a list of indexes where the vehicle was driving.

        Indexes are indexes of the `self.data` object.
        """
        indexes = []
        for start,end in driving_tuples:
            for x in range(start, end+1):
                indexes.append(x)
        return indexes

    def get_taskno_from_driving_tuple(self, driving_tuple, task_tuples):
        """Return the task id that belongs to a driving tuple.

        Note that all id's used here are indexes of the main data object.
        """
        for task,start,end in task_tuples:
            if driving_tuple[0] >= start and driving_tuple[1] <= end:
                return task
        return None

    def get_taskno_from_arrival_id(self, arrival_id, task_tuples):
        """Return the task id that belongs to an arrival id.

        Note that all id's used here are indexes of the main data object.
        """
        for task,start,end in task_tuples:
            if start <= arrival_id <= end:
                return task
        return None

    def get_taskstr_from_taskno(self, tasks, n):
        """Return task name from a task number.

        The task number must be a ride rule number.
        """
        for task_id,name in tasks:
            if task_id == n:
                return name
        return None

    # -----------------------------------
    # Generators

    def mark_driving_times(self, data, driving_tuples, task_tuples, tasks):
        """Marks certain items in the main data object `data`.

        Some items need to be colored in the HTML object and are thus marked
        for coloring by this method.
        """
        driving_indexes = self.get_driving_indexes(driving_tuples)

        for i,row in enumerate(data):
            # Get the current task.
            for task_id, start, end in task_tuples:
                if start <= i <= end:
                    current_task = task_id
                    break

            # Decide which cells to color.
            for key, value in row.iteritems():
                if key == 'tijd' and i in driving_indexes:
                    row[key] = self.mark_value(value)
                if key == 'actie' and value in ('AankAdres','VertrAdres'):
                    row[key] = self.mark_value(value)

            # Add the current task to the row.
            row['taak'] = "%s_%s" % (self.get_taskstr_from_taskno(tasks, current_task), current_task)
            # Color the task if it's an overnight task.
            if current_task in self.overnight_tasks:
                row['taak'] = self.mark_value(row['taak'])

            yield row

class Preprocess(object):
    """Perform some precalculations on the database.

    These precalculations must be performed before calculating driving times,
    delays, etc.
    """

    def __init__(self, connection):
        self.connection = connection

    def start(self):
        self.task_to_vehicle()
        self.task_to_route()
        logging.info("Done")

    def task_to_vehicle(self):
        """Fill the table `task_to_vehicle`.

        Table `task_to_vehicle` is required for calculating the driving times.
        Sometimes more vehicles are found for a single task in the activities
        table. Table `task_to_vehicle` is created to define the vehicle actually
        used for each task.

        Warning: After execution of this function there may still be tasks
        missing from the `task_to_vehicle` table because some `hist_bcacties`
        records lack a vehicle code (this is an inconsistency in the database).
        """
        logging.info("Defining vehicle for each task...")
        cursor = self.connection.cursor()
        cursor2 = self.connection.cursor()

        # First purge the table.
        cursor.execute("DELETE FROM task_to_vehicle;")
        self.connection.commit()

        # First set one vehicle code per task (doesn't matter which one, but
        # this is so tasks that are excluded from the next query have a
        # vehicle code set as well). This also means that tasks for
        # which no task was finished, the vehicle code is set anyway. This
        # is probably not desired as this might lead to incorrect driving times
        # for such tasks. Is there a better way to do this?
        cursor.execute("""INSERT INTO task_to_vehicle (task_id,vehicle_code)
            SELECT elipsref, MAX(terminalcode)
            FROM hist_bcacties
            WHERE elipsref IS NOT NULL
            AND terminalcode IS NOT NULL
            GROUP BY elipsref;""")
        self.connection.commit()

        # Finally update the vehicle code for each task returned by the
        # following query. This query only shows vehicles for which a task
        # was actually finished (typecode=13).
        # NOTE: Notice the MAX() in that query. This means that if we still
        # find more than one vehicle code for a task (this is not supposed to
        # happen), we pick the last one just so the program doesn't crash.
        cursor.execute("""SELECT r.ritregelnr, MAX(a.terminalcode)
            FROM hist_bcacties a
            INNER JOIN tbl_ritregels r ON (a.elipsref = r.ritregelnr)
            WHERE a.typecode=13
            AND a.actie = r.actiecode
            AND a.terminalcode IS NOT NULL
            GROUP BY r.ritregelnr;""")

        for task, vehicle in cursor:
            cursor2.execute("UPDATE task_to_vehicle SET (vehicle_code) = ('%s') WHERE task_id=%d;" % (vehicle,task))
        self.connection.commit()

        # Warning: At this point, there may still be tasks missing from the
        # task_to_vehicle table because some `hist_bcacties` records lack
        # a vehicle code (this is an inconsistency in the database).

        cursor2.close()
        cursor.close()

    def task_to_route(self):
        """Fill the table `task_to_route`.

        Table `task_to_route` is used to easily obtain the from and to location
        for a task.
        """
        logging.info("Defining route for each task...")
        cursor = self.connection.cursor()
        cursor2 = self.connection.cursor()

        # First purge the table.
        cursor.execute("DELETE FROM task_to_route;")
        self.connection.commit()

        # Define routes.
        cursor.execute("""SELECT r.ritlink,
            r.ritregelnr,
            r.locatiecode,
            r.locatiepostcode,
            r.locatieplaats,
            r.locatieland,
            v.vehicle_code
        FROM tbl_ritregels r
            INNER JOIN hist_bcacties a ON a.elipsref = r.ritregelnr
            INNER JOIN task_to_vehicle v ON v.task_id = r.ritregelnr
        GROUP BY r.ritlink,r.ritregelnr,r.locatiepostcode,v.vehicle_code
        ORDER BY v.vehicle_code, MIN(a.tijd);""")

        previous_vehicle = None
        from_task = None
        from_code = None
        from_postcode = None
        from_city = None
        from_country = None
        for ride_id,task_id,to_code,to_postcode,to_city,to_country,vehicle in cursor:
            # Skip unsupported vehicles.
            if not vehicle.startswith("W"):
                previous_vehicle = None
                from_task = None
                from_code = None
                from_postcode = None
                from_city = None
                from_country = None
                continue

            # If a new vehicle is encountered, do not count this as a route.
            # Reset "from" location so that the "to" location is saved for the
            # next loop.
            if previous_vehicle != vehicle:
                from_postcode = None

            # Convert post codes consisting of spaces to an empty string.
            if to_postcode:
                to_postcode = to_postcode.strip()

            # Save route to the database.
            if from_postcode and to_postcode:
                columns = {'from_task': from_task,
                    'from_code': from_code,
                    'from_postcode': from_postcode,
                    'from_city': from_city,
                    'from_country': from_country,
                    'from_country': from_country,
                    'to_code': to_code,
                    'to_postcode': to_postcode,
                    'to_city': to_city,
                    'to_country': to_country
                    }
                query = "INSERT INTO task_to_route (task_id,ride_id,%%s) VALUES (%d,%d,%%s);" % (task_id,ride_id)
                query = pyrits.erniesoft.query.construct_insert_query(query, columns)
                cursor2.execute(query)

            # Set values for the next loop.
            from_task = task_id
            from_code = to_code
            from_postcode = to_postcode
            from_city = to_city
            from_country = to_country
            previous_vehicle = vehicle

        # Commit changes.
        self.connection.commit()

        cursor2.close()
        cursor.close()
