csci-backend/app/db/repository/report.py

from datetime import datetime
from fastapi.responses import StreamingResponse
from sqlalchemy.orm import Session
from db.models.drivetask import DriveTask

from db.models.user import User
from db.models.fuelingtask import FuelingTask
from db.models.maintenancejob import MaintenanceJob
from db.repository.maintenancejob import calculate_total_cost

from reportlab.lib import colors
from reportlab.lib.pagesizes import letter
from reportlab.platypus import (
    SimpleDocTemplate,
    Table,
    TableStyle,
    Paragraph,
    Image,
    PageBreak,
)
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.lib.units import inch
from reportlab.graphics.shapes import Drawing
from reportlab.graphics.charts.linecharts import HorizontalLineChart
from reportlab.graphics.widgets.markers import makeMarker
from reportlab.platypus import Spacer
from reportlab.graphics.charts.axes import CategoryAxis
from dateutil import parser


def get_repot_jobsdone_by_driver(driver_id: int, db: Session):
    driver = db.query(User).filter(User.Id == driver_id).first()
    if not driver:
        return "notdriver"
    if driver.Role != "Driver":
        return "notdriver"
    tasks = (
        db.query(DriveTask)
        .filter((DriveTask.DriverId == driver_id) & (DriveTask.Status == "Completed"))
        .all()
    )
    # order tasks by completion date
    tasks.sort(key=lambda x: x.EndDateTime)
    # for each task, add TOTAL distance driven at the time of completion
    dist = 0
    taskslist = []
    for task in tasks:
        dist += task.DistanceCovered
        taskslist.append(task.__dict__)
        taskslist[-1]["DistanceAtTime"] = dist
    res = {}
    res["tasks"] = taskslist
    res["Driver"] = driver.__dict__
    res["Driver"]["AssignedVehicle"] = driver.vehicle
    dist = 0
    for task in tasks:
        dist += task.DistanceCovered
    res["TotalDistance"] = dist
    timespent = 0.0

    for task in tasks:
        edate = task.EndDateTime
        sdate = task.StartDateTime
        if edate is None or sdate is None:
            continue
        if type(edate) == str:
            edate = datetime.strptime(edate, "%Y-%m-%d %H:%M:%S.%f")
        timetaken = edate - sdate
        timespent += timetaken.total_seconds()
    # time spent in hours and minutes
    hours = timespent // 3600
    minutes = (timespent % 3600) // 60
    res["TotalTime"] = str(hours) + " hours " + str(minutes) + " minutes"
    # add fuel expenditures
    fuel = db.query(FuelingTask).filter(FuelingTask.DriverId == driver_id).all()
    fuel.sort(key=lambda x: x.Date)
    totalfuelspent = 0
    fuelspent = []
    for f in fuel:
        fuelpoint = {}
        totalfuelspent += f.FuelRefilled
        fuelpoint["fuelrefilled"] = f.FuelRefilled
        fuelpoint["total"] = totalfuelspent
        fuelpoint["date"] = f.Date
        fuelspent.append(fuelpoint)
    res["FuelSpent"] = fuelspent
    res["TotalFuelSpent"] = totalfuelspent

    # add maintenance costs
    mainten = (
        db.query(MaintenanceJob)
        .filter(MaintenanceJob.VehicleDriverId == driver_id)
        .all()
    )
    mainten.sort(key=lambda x: x.Date)
    totalmaintenspent = 0
    maintenspent = []
    for m in mainten:
        maintenpoint = {}
        cost = calculate_total_cost(m.CarParts)
        totalmaintenspent += cost
        maintenpoint["cost"] = cost
        maintenpoint["total"] = totalmaintenspent
        maintenpoint["date"] = m.Date
        maintenspent.append(maintenpoint)
    res["MaintenanceSpent"] = maintenspent
    res["TotalMaintenanceSpent"] = totalmaintenspent
    return res


def get_pdf(driver_id: int, db: Session):
    report_data = get_repot_jobsdone_by_driver(driver_id, db)
    if report_data == "notdriver":
        return "notdriver"

    # Assuming your report data is stored in a variable named 'report_data'

    # Create a PDF document
    pdf_filename = "report1.pdf"
    document = SimpleDocTemplate(pdf_filename, pagesize=letter)

    # Create a list to hold the contents of the PDF
    content = []

    # Add a title to the PDF
    title_style = getSampleStyleSheet()["Title"]
    title = Paragraph("Driver Report", title_style)
    content.append(title)
    content.append(Paragraph("<br/>", title_style))

    # Add detailed information about the driver
    driver_info = [
        ["Driver Name", report_data["Driver"]["Name"]],
        ["Last Name", report_data["Driver"]["LastName"]],
        ["Government ID", report_data["Driver"]["GovernmentId"]],
        ["Email", report_data["Driver"]["Email"]],
        ["Contact Number", report_data["Driver"]["ContactNumber"]],
        ["Address", report_data["Driver"]["Address"]],
        ["Role", report_data["Driver"]["Role"]],
    ]

    driver_table_style = TableStyle(
        [
            ("ALIGN", (0, 0), (-1, -1), "LEFT"),
            ("BOX", (0, 0), (-1, -1), 1, colors.black),
        ]
    )

    driver_table = Table(driver_info, style=driver_table_style)
    content.append(Paragraph("<br/><br/>", title_style))

    vehicle = report_data["Driver"]["AssignedVehicle"].__dict__
    if (
        vehicle["CurrentLocation"] is None
        or vehicle["CurrentLocation"] == ""
        or vehicle["CurrentLocation"] == False
        or len(vehicle["CurrentLocation"]) == 0
    ):
        vehicle["CurrentLocation"] = ["Unknown", "Unknown"]
    loc = vehicle["CurrentLocation"][0]
    if len(vehicle["CurrentLocation"]) > 1:
        loc += ", " + vehicle["CurrentLocation"][1]
    # Add vehicle details
    vehicle_info = [
        ["Vehicle Type", vehicle["Type"]],
        ["Vehicle Model", vehicle["Model"]],
        ["Year", vehicle["Year"]],
        ["License Plate", vehicle["LicensePlate"]],
        ["Current Location", loc],
        ["Mileage", vehicle["Mileage"]],
        ["Capacity", vehicle["Capacity"]],
    ]

    vehicle_table_style = TableStyle(
        [
            ("ALIGN", (0, 0), (-1, -1), "LEFT"),
            ("BOX", (0, 0), (-1, -1), 1, colors.black),
        ]
    )

    vehicle_table = Table(vehicle_info, style=vehicle_table_style)
    content.append(Spacer(1, 12))  # Add space between the tables
    content.append(Table([[driver_table, vehicle_table]]))
    content.append(Paragraph("<br/><br/>", title_style))

    total_info = [
        ["Total Tasks Done", report_data["tasks"].__len__()],
        ["Total Fuel Spent", report_data["TotalFuelSpent"]],
        ["Total Time Spent", report_data["TotalTime"]],
        ["Total Distance Driven", report_data["TotalDistance"]],
        ["Total Money Spent", report_data["TotalMaintenanceSpent"]],
    ]

    total_table_style = TableStyle(
        [
            ("ALIGN", (0, 0), (-1, -1), "LEFT"),
            ("BOX", (0, 0), (-1, -1), 1, colors.black),
        ]
    )

    total_table = Table(total_info, style=total_table_style)
    content.append(total_table)
    content.append(Paragraph("<br/><br/>", title_style))

    content.append(PageBreak())

    title_style = getSampleStyleSheet()["Heading1"]
    content.append(Paragraph("Driving Tasks", title_style))

    title_style = getSampleStyleSheet()["Heading2"]
    content.append(Paragraph("Distance Driven over Time", title_style))
    tasks_data = report_data["tasks"]
    if tasks_data.__len__() == 0:
        content.append(Paragraph("No tasks done.", title_style))
    else:
        task_values = [entry["DistanceAtTime"] for entry in tasks_data]
        task_dates = [
            # if type(entry["EndDateTime"]) == str parse, else just use
            parser.parse(entry["EndDateTime"]).strftime("%m/%d/%Y, %H:%M:%S")
            if type(entry["EndDateTime"]) == str
            else entry["EndDateTime"].strftime("%m/%d/%Y, %H:%M:%S")
            for entry in tasks_data
        ]

        drawing = Drawing(width=400, height=200)
        chart = HorizontalLineChart()
        chart.x = 0
        chart.y = 50
        chart.width = 500
        chart.height = 125
        chart.data = [task_values]
        chart.categoryAxis.labels.boxAnchor = "e"
        chart.valueAxis.valueMin = 0
        chart.valueAxis.valueMax = max(task_values) + 10
        chart.lines[0].strokeColor = colors.blue
        chart.lines[0].strokeWidth = 2
        chart.categoryAxis.categoryNames = [str(date) for date in task_dates]
        chart.categoryAxis.labels.angle = 90
        drawing.add(chart)

        # Add space between the tables
        content.append(drawing)
        content.append(Paragraph("<br/><br/>", title_style))
        content.append(Paragraph("<br/><br/>", title_style))
        content.append(Paragraph("<br/><br/>", title_style))

        # Add tasks information
        title_style = getSampleStyleSheet()["Heading2"]
        content.append(Paragraph("All DriveTasks", title_style))
        tasks_header = [
            "Task ID",
            "Description",
            "Start Location",
            "End Location",
            "Distance Covered",
            "Status",
            "Start DateTime",
            "End DateTime",
        ]
        tasks_info = []

        for task in tasks_data:
            if task.get("EndDateTime") is None or task["EndDateTime"] == "":
                continue
            if task.get("StartDateTime") is None or task["StartDateTime"] == "":
                continue
            task_info = [
                f"{task['Id']}",
                task["Description"],
                f"{task['StartLocation'][0]}, {task['StartLocation'][1]}",
                f"{task['EndLocation'][0]}, {task['EndLocation'][1]}",
                "{} km".format(task["DistanceCovered"]),
                task["Status"],
                task["StartDateTime"].strftime("%m/%d/%Y, %H:%M:%S"),
                parser.parse(task["EndDateTime"]).strftime("%m/%d/%Y, %H:%M:%S")
                if type(task["EndDateTime"]) == str
                else task["EndDateTime"].strftime("%m/%d/%Y, %H:%M:%S"),
            ]
            tasks_info.append(task_info)

        # Define the styles
        styles = getSampleStyleSheet()
        normal_style = styles["Normal"]

        # Convert strings to Paragraphs to allow line breaks
        for row in tasks_info:
            for i, cell in enumerate(row):
                row[i] = Paragraph(cell, normal_style)

        tasks_table_style = TableStyle(
            [
                ("BACKGROUND", (0, 0), (-1, 0), colors.grey),
                ("TEXTCOLOR", (0, 0), (-1, 0), colors.whitesmoke),
                ("ALIGN", (0, 0), (-1, -1), "CENTER"),
                ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
                ("BOTTOMPADDING", (0, 0), (-1, 0), 12),
                ("BACKGROUND", (0, 1), (-1, -1), colors.beige),
                ("GRID", (0, 0), (-1, -1), 1, colors.black),
            ]
        )

        tasks_table = Table([tasks_header] + tasks_info, style=tasks_table_style)
        content.append(tasks_table)

    content.append(PageBreak())

    title_style = getSampleStyleSheet()["Heading1"]
    content.append(Paragraph("Fuel Spent Over Time", title_style))
    # Add chart of fuel spendings
    fuel_spent_data = report_data["FuelSpent"]
    if fuel_spent_data.__len__() == 0:
        content.append(Paragraph("No fuel spent.", title_style))
    else:
        fuel_values = [entry["total"] for entry in fuel_spent_data]
        fuel_dates = [entry["date"] for entry in fuel_spent_data]

        drawing = Drawing(width=400, height=200)
        chart = HorizontalLineChart()
        chart.x = 0
        chart.y = 50
        chart.width = 500
        chart.height = 125
        chart.data = [fuel_values]
        chart.categoryAxis.labels.boxAnchor = "e"
        chart.valueAxis.valueMin = 0
        chart.valueAxis.valueMax = max(fuel_values) + 10
        chart.lines[0].strokeColor = colors.blue
        chart.lines[0].strokeWidth = 2
        chart.categoryAxis.categoryNames = [str(date) for date in fuel_dates]
        chart.categoryAxis.labels.angle = 90
        drawing.add(chart)

        # Add space between the tables
        content.append(drawing)
        content.append(Paragraph("<br/><br/>", title_style))

        fuel_header = ["Date", "Fuel Refilled", "Total Fuel Spent"]
        fuel_info = []

        for fuel in fuel_spent_data:
            fuel_info1 = [
                fuel["date"].strftime("%m/%d/%Y, %H:%M:%S"),
                "{} L".format(fuel["fuelrefilled"]),
                "{} L".format(fuel["total"]),
            ]
            fuel_info.append(fuel_info1)

        # Define the styles
        styles = getSampleStyleSheet()
        normal_style = styles["Normal"]

        # Convert strings to Paragraphs to allow line breaks
        for row in fuel_info:
            for i, cell in enumerate(row):
                row[i] = Paragraph(cell, normal_style)

        fuel_table_style = TableStyle(
            [
                ("BACKGROUND", (0, 0), (-1, 0), colors.grey),
                ("TEXTCOLOR", (0, 0), (-1, 0), colors.whitesmoke),
                ("ALIGN", (0, 0), (-1, -1), "CENTER"),
                ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
                ("BOTTOMPADDING", (0, 0), (-1, 0), 12),
                ("BACKGROUND", (0, 1), (-1, -1), colors.beige),
                ("GRID", (0, 0), (-1, -1), 1, colors.black),
            ]
        )

        fuel_table = Table([fuel_header] + fuel_info, style=fuel_table_style)
        content.append(fuel_table)

    content.append(PageBreak())

    title_style = getSampleStyleSheet()["Heading1"]
    content.append(Paragraph("Maintenance Over Time", title_style))
    # Add chart of fuel spendings
    maintenance_data = report_data["MaintenanceSpent"]
    if maintenance_data.__len__() == 0:
        content.append(Paragraph("No maintenance done.", title_style))
    else:
        maintenance_values = [entry["total"] for entry in maintenance_data]
        maintenance_dates = [entry["date"] for entry in maintenance_data]

        drawing = Drawing(width=400, height=200)
        chart = HorizontalLineChart()
        chart.x = 0
        chart.y = 50
        chart.width = 500
        chart.height = 125
        chart.data = [maintenance_values]
        chart.categoryAxis.labels.boxAnchor = "e"
        chart.valueAxis.valueMin = 0
        chart.valueAxis.valueMax = max(maintenance_values) + 10
        chart.lines[0].strokeColor = colors.blue
        chart.lines[0].strokeWidth = 2
        chart.categoryAxis.categoryNames = [str(date) for date in maintenance_dates]
        chart.categoryAxis.labels.angle = 90
        drawing.add(chart)

        # Add space between the tables
        content.append(drawing)
        content.append(Paragraph("<br/><br/>", title_style))

        maintenance_header = ["Date", "Maintenance Cost", "Total Maintenance Cost"]
        maintenance_info = []

        for fuel in maintenance_data:
            fuel_info1 = [
                fuel["date"].strftime("%m/%d/%Y, %H:%M:%S"),
                "{} L".format(fuel["cost"]),
                "{} L".format(fuel["total"]),
            ]
            maintenance_info.append(fuel_info1)

        # Define the styles
        styles = getSampleStyleSheet()
        normal_style = styles["Normal"]

        # Convert strings to Paragraphs to allow line breaks
        for row in maintenance_info:
            for i, cell in enumerate(row):
                row[i] = Paragraph(cell, normal_style)

        maintenance_table_style = TableStyle(
            [
                ("BACKGROUND", (0, 0), (-1, 0), colors.grey),
                ("TEXTCOLOR", (0, 0), (-1, 0), colors.whitesmoke),
                ("ALIGN", (0, 0), (-1, -1), "CENTER"),
                ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
                ("BOTTOMPADDING", (0, 0), (-1, 0), 12),
                ("BACKGROUND", (0, 1), (-1, -1), colors.beige),
                ("GRID", (0, 0), (-1, -1), 1, colors.black),
            ]
        )

        maintenance_table = Table(
            [maintenance_header] + maintenance_info, style=maintenance_table_style
        )
        content.append(maintenance_table)

    document.build(content)

    print(f"PDF generated successfully: {pdf_filename}")

    return pdf_filename