#!/usr/bin/env python3
from docx import Document
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.enum.style import WD_STYLE_TYPE
from docx.shared import Cm, Inches, Pt, RGBColor
from docx.oxml import OxmlElement
from docx.oxml.ns import qn
import time
import os
from datetime import datetime, timedelta
import mysql.connector
import sys
import json
import re
import subprocess

def getstatement(user,period):
    cursor = connection.cursor()
    select_query = "SELECT id, statedate, fullname, period_start, period_end, loadlist, creditlist, deductionlist, schdeductionlist, fuellist, total, isowner FROM statements WHERE driver_id = %s AND statedate = %s"
    cursor.execute(select_query, (user, period, ))
    result = cursor.fetchall()
    statement = None
    if result:
        for res in result:
            statement = {
                'id': res[0],
                'date': res[1].strftime("%m.%d.%y"),
                'fullname': res[2],
                'period': res[3].strftime("%m.%d.%y")+' - '+res[4].strftime("%m.%d.%y"),
                'loads': json.loads(res[5]) if res[5] else None,
                'credits': json.loads(res[6]) if res[6] else None,
                'deductions': json.loads(res[7]) if res[7] else None,
                'scheduled': json.loads(res[8]) if res[8] else None,
                'fuels': json.loads(res[9]) if res[9] else None,
                'total': res[10],
                'owner': res[11] if res[11] else None,
            }
    return statement

def gettruckinfo(user):
    unit = None
    cursor = connection.cursor()
    select_query = "SELECT trucks.number FROM trucks JOIN drivers ON trucks.id = drivers.truck_id WHERE drivers.user_id = %s"
    cursor.execute(select_query, (user,))
    result = cursor.fetchall()
    if result:
        unit = result[0][0]
    return unit

def getuserinfo(userid):
    user = None
    cursor = connection.cursor()
    select_query = "SELECT id, role FROM users WHERE id = %s"
    cursor.execute(select_query, (userid,))
    result = cursor.fetchall()
    if result:
        user = {
            'id':result[0][0],
            'role':result[0][1],
        }
    return user

def formatTableCells(table, tablerow, tablecells):
    for c in range(tablecells):
        propertyXml = table.rows[tablerow].cells[c]._tc.get_or_add_tcPr()
        shade_obj = OxmlElement('w:shd')
        shade_obj.set(qn('w:fill'), 'D3D3D3')
        propertyXml.append(shade_obj)

def convert_docx_to_pdf(parentDir, period, userId):
    try:
        env_vars = {'HOME': '/var/www/html/tms/public/py'}
        subprocess.run(['libreoffice', '--headless', '--convert-to', 'pdf', parentDir+'/temp/tempstatement.docx', '--outdir', parentDir+period+'/'+str(userId)], env=env_vars)
        os.rename(parentDir+period+'/'+str(userId)+'/tempstatement.pdf', parentDir+period+'/'+str(userId)+'/StatementParentCustomReport.pdf')
    except Exception as e:
        sys.exit()

def set_column_width(column, width):
    column.width = width
    for cell in column.cells:
        cell.width = width

def createPDF(statement, userId, period, parentDir, unit):
    tloads, tcredits, tdeductions, tschdeductions, tfuels, tloadsamount, checkamount = 0, 0, 0, 0, 0, 0, 0
    doc = Document()
    doc.styles['Normal'].font.name = "Tahoma"
    doc.styles['Normal'].font.size = Pt(8.25)
    sections = doc.sections
    section = sections[0]
    section.top_margin = Inches(0.5)
    section.left_margin = Inches(0.7)
    section.right_margin = Inches(0.7)

    #header
    tableHeader = doc.add_table(rows=1, cols=2)
    pR = tableHeader.rows[0].cells[0].paragraphs[0]
    pR.add_run().add_picture(str(parentDir) + 'temp/biglogo.jpg')
    pL = tableHeader.rows[0].cells[1].paragraphs[0]
    if unit:
        para = pL.add_run('Statement #'+str(statement['id'])+'\n\n' + str(statement['date']) + '\nUnit #' + str(unit) + '\n(' + str(statement['fullname']) + ')')
    else:
        para = pL.add_run('\n\nStatement #\n\n' + str(statement['date']) + '\n(' + str(statement['fullname'])+')')
    font = para.font
    font.name = "Calibri"
    font.size = Pt(16)
    pL.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.RIGHT

    # Trips
    if statement['loads']:
        heading1 = doc.add_paragraph().add_run('Trips:')
        heading1.bold = True
        font = heading1.font
        font.size = Pt(12)
        tripTable = doc.add_table(rows=0, cols=6)
        row = tripTable.add_row()
        row.cells[0].paragraphs[0].add_run('Trip No.').bold = True
        row.cells[1].paragraphs[0].add_run('Description').bold = True
        row.cells[2].paragraphs[0].add_run('Mileage').bold = True
        row.cells[3].paragraphs[0].add_run('Freight Amt.').bold = True
        row.cells[4].paragraphs[0].add_run('Date').bold = True
        row.cells[5].paragraphs[0].add_run('Amount').bold = True
        row.cells[0].paragraphs[0].paragraph_format.alignment = 1
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row.cells[3].paragraphs[0].paragraph_format.alignment = 1
        row.cells[4].paragraphs[0].paragraph_format.alignment = 1
        row.cells[5].paragraphs[0].paragraph_format.alignment = 1
        for load in statement['loads']:
            tloads = tloads + load['freightamt']
            tloadsamount = tloadsamount + load['amount']
            row = tripTable.add_row()
            row.cells[0].text = str(load['id'])
            row.cells[1].text = str(load['route'])
            row.cells[2].text = str(load['distance']) if load['distance'] else ''
            row.cells[3].text = '${:,.2f}'.format(float(load['freightamt']))
            row.cells[4].text = load['date']
            row.cells[5].text = '${:,.2f}'.format(float(load['amount']))
            row.cells[0].paragraphs[0].paragraph_format.alignment = 1
            row.cells[1].paragraphs[0].paragraph_format.alignment = 1
            row.cells[2].paragraphs[0].paragraph_format.alignment = 1
            row.cells[3].paragraphs[0].paragraph_format.alignment = 1
            row.cells[4].paragraphs[0].paragraph_format.alignment = 1
            row.cells[5].paragraphs[0].paragraph_format.alignment = 1
        row = tripTable.add_row()
        row.cells[2].paragraphs[0].add_run('Total:').bold = True
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row.cells[3].paragraphs[0].add_run('${:,.2f}'.format(tloads))
        row.cells[3].paragraphs[0].paragraph_format.alignment = 1
        coloredTotal = row.cells[5].paragraphs[0].add_run('${:,.2f}'.format(tloadsamount))
        coloredTotal.bold = True
        font = coloredTotal.font
        if tloadsamount > 0:
            font.color.rgb = RGBColor(34, 139, 34)
        else:
            font.color.rgb = RGBColor(255, 0, 0)
        row.cells[5].paragraphs[0].paragraph_format.alignment = 1
        formatTableCells(tripTable, 0, 6)
        tripTable.style = 'Table Grid'
        tripTable.allow_autofit = False
        tripTable.autofit = False
        set_column_width(tripTable.columns[0], Inches(0.8))
        set_column_width(tripTable.columns[1], Inches(3.0))
        set_column_width(tripTable.columns[2], Inches(0.8))
        set_column_width(tripTable.columns[3], Inches(1.0))
        set_column_width(tripTable.columns[4], Inches(1.0))
        set_column_width(tripTable.columns[5], Inches(1.0))
        doc.add_paragraph()
        checkamount = checkamount + tloadsamount
        del row

    # Credit
    if statement['credits']:
        heading2 = doc.add_paragraph().add_run('Credits:')
        heading2.bold = True
        font = heading2.font
        font.size = Pt(12)
        CashTable = doc.add_table(0, 3)
        row = CashTable.add_row()
        row.cells[0].paragraphs[0].add_run('Description:').bold = True
        row.cells[1].paragraphs[0].add_run('Date').bold = True
        row.cells[2].paragraphs[0].add_run('Amount').bold = True
        row.cells[0].paragraphs[0].paragraph_format.alignment = 1
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        for credit in statement['credits']:
            tcredits = tcredits + credit['amount']
            row = CashTable.add_row()
            row.cells[0].text = str(credit['description'])
            row.cells[1].text = str(credit['date'])
            row.cells[2].text = str('${:,.2f}'.format(float(credit['amount'])))
            row.cells[0].paragraphs[0].paragraph_format.alignment = 1
            row.cells[1].paragraphs[0].paragraph_format.alignment = 1
            row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row = CashTable.add_row()
        row.cells[1].paragraphs[0].add_run('Total:').bold = True
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        coloredTotal = row.cells[2].paragraphs[0].add_run('${:,.2f}'.format(tcredits))
        coloredTotal.bold = True
        font = coloredTotal.font
        font.color.rgb = RGBColor(34, 139, 34)
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        formatTableCells(CashTable, 0, 3)
        CashTable.style = 'Table Grid'
        CashTable.allow_autofit = False
        CashTable.autofit = False
        set_column_width(CashTable.columns[0], Inches(5.6))
        set_column_width(CashTable.columns[1], Inches(1.0))
        set_column_width(CashTable.columns[2], Inches(1.0))
        doc.add_paragraph()
        checkamount = checkamount + tcredits
        del row

    # Deduction
    if statement['deductions']:
        heading3 = doc.add_paragraph().add_run('Advances and Deductions:')
        heading3.bold = True
        font = heading3.font
        font.size = Pt(12)
        DeductionTable = doc.add_table(0, 3)
        row = DeductionTable.add_row()
        row.cells[0].paragraphs[0].add_run('Description:').bold = True
        row.cells[1].paragraphs[0].add_run('Date').bold = True
        row.cells[2].paragraphs[0].add_run('Amount').bold = True
        row.cells[0].paragraphs[0].paragraph_format.alignment = 1
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        for deduction in statement['deductions']:
            tdeductions = tdeductions + deduction['amount']
            row = DeductionTable.add_row()
            row.cells[0].text = str(deduction['description'])
            row.cells[1].text = str(deduction['date'])
            row.cells[2].text = str('$-{:,.2f}'.format(float(deduction['amount'])))
            row.cells[0].paragraphs[0].paragraph_format.alignment = 1
            row.cells[1].paragraphs[0].paragraph_format.alignment = 1
            row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row = DeductionTable.add_row()
        row.cells[1].paragraphs[0].add_run('Total:').bold = True
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        coloredTotal = row.cells[2].paragraphs[0].add_run('$-{:,.2f}'.format(tdeductions))
        coloredTotal.bold = True
        font = coloredTotal.font
        font.color.rgb = RGBColor(255, 0, 0)
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        formatTableCells(DeductionTable, 0, 3)
        DeductionTable.style = 'Table Grid'
        DeductionTable.allow_autofit = False
        DeductionTable.autofit = False
        set_column_width(DeductionTable.columns[0], Inches(5.6))
        set_column_width(DeductionTable.columns[1], Inches(1.0))
        set_column_width(DeductionTable.columns[2], Inches(1.0))
        doc.add_paragraph()
        checkamount = checkamount - tdeductions
        del row

    # Scheduled Deduction
    if statement['scheduled']:
        heading4 = doc.add_paragraph().add_run('Scheduled Deductions:')
        heading4.bold = True
        font = heading4.font
        font.size = Pt(12)
        SchDeductionTable = doc.add_table(0, 3)
        row = SchDeductionTable.add_row()
        row.cells[0].paragraphs[0].add_run('Description:').bold = True
        row.cells[1].paragraphs[0].add_run('Date').bold = True
        row.cells[2].paragraphs[0].add_run('Amount').bold = True
        row.cells[0].paragraphs[0].paragraph_format.alignment = 1
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        for schdeduction in statement['scheduled']:
            tschdeductions = tschdeductions + schdeduction['amount']
            row = SchDeductionTable.add_row()
            row.cells[0].text = str(schdeduction['description'])
            row.cells[1].text = str(schdeduction['date'])
            row.cells[2].text = str('$-{:,.2f}'.format(float(schdeduction['amount'])))
            row.cells[0].paragraphs[0].paragraph_format.alignment = 1
            row.cells[1].paragraphs[0].paragraph_format.alignment = 1
            row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row = SchDeductionTable.add_row()
        row.cells[1].paragraphs[0].add_run('Total:').bold = True
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        coloredTotal = row.cells[2].paragraphs[0].add_run('$-{:,.2f}'.format(tschdeductions))
        coloredTotal.bold = True
        font = coloredTotal.font
        font.color.rgb = RGBColor(255, 0, 0)
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        formatTableCells(SchDeductionTable, 0, 3)
        SchDeductionTable.style = 'Table Grid'
        SchDeductionTable.allow_autofit = False
        SchDeductionTable.autofit = False
        set_column_width(SchDeductionTable.columns[0], Inches(5.6))
        set_column_width(SchDeductionTable.columns[1], Inches(1.0))
        set_column_width(SchDeductionTable.columns[2], Inches(1.0))
        doc.add_paragraph()
        checkamount = checkamount - tschdeductions
        del row

    # Fuels
    if statement['fuels']:
        heading5 = doc.add_paragraph().add_run('Fuels:')
        heading5.bold = True
        font = heading5.font
        font.size = Pt(12)
        FuelTable = doc.add_table(0, 3)
        row = FuelTable.add_row()
        row.cells[0].paragraphs[0].add_run('Description:').bold = True
        row.cells[1].paragraphs[0].add_run('Date').bold = True
        row.cells[2].paragraphs[0].add_run('Amount').bold = True
        row.cells[0].paragraphs[0].paragraph_format.alignment = 1
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        for fuel in statement['fuels']:
            tfuels = tfuels + fuel['amount']
            row = FuelTable.add_row()
            row.cells[0].text = str(fuel['description'])
            row.cells[1].text = str(fuel['date'])
            row.cells[2].text = str('$-{:,.2f}'.format(float(fuel['amount'])))
            row.cells[0].paragraphs[0].paragraph_format.alignment = 1
            row.cells[1].paragraphs[0].paragraph_format.alignment = 1
            row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row = FuelTable.add_row()
        row.cells[1].paragraphs[0].add_run('Total:').bold = True
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        coloredTotal = row.cells[2].paragraphs[0].add_run('$-{:,.2f}'.format(tfuels))
        coloredTotal.bold = True
        font = coloredTotal.font
        font.color.rgb = RGBColor(255, 0, 0)
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        formatTableCells(FuelTable, 0, 3)
        FuelTable.style = 'Table Grid'
        FuelTable.allow_autofit = False
        FuelTable.autofit = False
        set_column_width(FuelTable.columns[0], Inches(5.6))
        set_column_width(FuelTable.columns[1], Inches(1.0))
        set_column_width(FuelTable.columns[2], Inches(1.0))
        doc.add_paragraph()
        checkamount = checkamount - tfuels
        del row

    # Summary
    checkAmount = doc.add_table(1, 2)
    col1 = checkAmount.rows[0].cells[0].paragraphs[0].add_run('Check Amount')
    col1.bold = True
    checkAmount.rows[0].cells[0].paragraphs[0].paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER
    col2 = checkAmount.rows[0].cells[1].paragraphs[0].add_run(str('${:,.2f}'.format(float(checkamount))))
    col2.bold = True
    checkAmount.rows[0].cells[1].paragraphs[0].paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER
    font = col2.font
    if checkamount > 0:
        font.color.rgb = RGBColor(34, 139, 34)
    else:
        font.color.rgb = RGBColor(255, 0, 0)
    checkAmount.cell(0, 0).width = Inches(1.0)
    checkAmount.cell(0, 1).width = Inches(1.0)
    checkAmount.style = 'Table Grid'
    checkAmount.allow_autofit = False
    checkAmount.autofit = False
    set_column_width(checkAmount.columns[0], Inches(1.0))
    set_column_width(checkAmount.columns[1], Inches(1.0))
    checkAmount.alignment = WD_TABLE_ALIGNMENT.RIGHT

    if os.path.isdir(parentDir + period) == False:
        os.mkdir(parentDir + period)
        if os.path.isdir(parentDir + period + "/" + str(userId)) == False:
            os.mkdir(parentDir + period + "/" + str(userId))
    #
    doc.save(parentDir + 'temp/tempstatement.docx')
    os.chmod(parentDir + 'temp/tempstatement.docx', 0o755)
    time.sleep(1)
    convert_docx_to_pdf(parentDir,period,userId)
    time.sleep(1)
    os.remove(parentDir + "temp/tempstatement.docx")
    if os.path.isfile(parentDir + period + "/" + str(userId)+"/StatementParentCustomReport.pdf"):
        return True
    else:
        return None

def createOwnerPDF(sorted_loads, sorted_credits, sorted_deductions, sorted_schdeductions, sorted_fuels, userId, period, parentDir):
    tloads = tcredits = tdeductions = tschdeductions = tfuels = tloadsamount = checkamount = 0
    doc = Document()
    doc.styles['Normal'].font.name = "Tahoma"
    doc.styles['Normal'].font.size = Pt(8.25)
    sections = doc.sections
    section = sections[0]
    section.top_margin = Inches(0.5)
    section.left_margin = Inches(0.7)
    section.right_margin = Inches(0.7)

    #header
    tableHeader = doc.add_table(rows=1, cols=2)
    pR = tableHeader.rows[0].cells[0].paragraphs[0]
    pR.add_run().add_picture(str(parentDir) + 'temp/biglogo.jpg')
    pL = tableHeader.rows[0].cells[1].paragraphs[0]
    para = pL.add_run('\n\nStatement #\n\n' + str(statement['date']) + '\n(' + str(statement['fullname'])+')')
    font = para.font
    font.name = "Calibri"
    font.size = Pt(16)
    pL.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.RIGHT

    # Trips
    driver_sum_loads = {}
    total_drivers = []
    if sorted_loads:
        heading1 = doc.add_paragraph().add_run('Trips:')
        heading1.bold = True
        font = heading1.font
        font.size = Pt(12)
        tripTable = doc.add_table(rows=0, cols=7)
        row = tripTable.add_row()
        row.cells[0].paragraphs[0].add_run('Trip No.').bold = True
        row.cells[1].paragraphs[0].add_run('Driver').bold = True
        row.cells[2].paragraphs[0].add_run('Description').bold = True
        row.cells[3].paragraphs[0].add_run('Mileage').bold = True
        row.cells[4].paragraphs[0].add_run('Freight Amt.').bold = True
        row.cells[5].paragraphs[0].add_run('Date').bold = True
        row.cells[6].paragraphs[0].add_run('Amount').bold = True
        row.cells[0].paragraphs[0].paragraph_format.alignment = 1
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row.cells[3].paragraphs[0].paragraph_format.alignment = 1
        row.cells[4].paragraphs[0].paragraph_format.alignment = 1
        row.cells[5].paragraphs[0].paragraph_format.alignment = 1
        row.cells[6].paragraphs[0].paragraph_format.alignment = 1
        for load in sorted_loads:
            driver = load['driver'].split()[0]
            amount = load['amount']
            if driver in driver_sum_loads:
                driver_sum_loads[driver] += amount
            else:
                driver_sum_loads[driver] = amount
            total_drivers.append(load['driver'])
            tloads = tloads + load['freightamt']
            tloadsamount = tloadsamount + load['amount']
            row = tripTable.add_row()
            row.cells[0].text = str(load['id'])
            row.cells[1].text = str(load['driver'])
            row.cells[2].text = str(load['route'])
            row.cells[3].text = str(load['distance']) if load['distance'] else ''
            row.cells[4].text = '${:,.2f}'.format(float(load['freightamt']))
            row.cells[5].text = load['date']
            row.cells[6].text = '${:,.2f}'.format(float(load['amount']))
            row.cells[0].paragraphs[0].paragraph_format.alignment = 1
            row.cells[1].paragraphs[0].paragraph_format.alignment = 1
            row.cells[2].paragraphs[0].paragraph_format.alignment = 1
            row.cells[3].paragraphs[0].paragraph_format.alignment = 1
            row.cells[4].paragraphs[0].paragraph_format.alignment = 1
            row.cells[5].paragraphs[0].paragraph_format.alignment = 1
            row.cells[6].paragraphs[0].paragraph_format.alignment = 1
        row = tripTable.add_row()
        row.cells[3].paragraphs[0].add_run('Total:').bold = True
        row.cells[3].paragraphs[0].paragraph_format.alignment = 1
        row.cells[4].paragraphs[0].add_run('${:,.2f}'.format(tloads))
        row.cells[4].paragraphs[0].paragraph_format.alignment = 1
        coloredTotal = row.cells[6].paragraphs[0].add_run('${:,.2f}'.format(tloadsamount))
        coloredTotal.bold = True
        font = coloredTotal.font
        if tloadsamount > 0:
            font.color.rgb = RGBColor(34, 139, 34)
        else:
            font.color.rgb = RGBColor(255, 0, 0)
        row.cells[6].paragraphs[0].paragraph_format.alignment = 1
        formatTableCells(tripTable, 0, 7)
        tripTable.style = 'Table Grid'
        tripTable.allow_autofit = False
        tripTable.autofit = False
        set_column_width(tripTable.columns[0], Inches(0.5))
        set_column_width(tripTable.columns[1], Inches(1.0))
        set_column_width(tripTable.columns[2], Inches(2.5))
        set_column_width(tripTable.columns[3], Inches(0.8))
        set_column_width(tripTable.columns[4], Inches(0.8))
        set_column_width(tripTable.columns[5], Inches(1.0))
        set_column_width(tripTable.columns[6], Inches(1.0))
        doc.add_paragraph()
        checkamount = checkamount + tloadsamount
        del row, driver, amount

    # Credit
    driver_sum_credits = {}
    if sorted_credits:
        heading2 = doc.add_paragraph().add_run('Credits:')
        heading2.bold = True
        font = heading2.font
        font.size = Pt(12)
        CashTable = doc.add_table(0, 3)
        row = CashTable.add_row()
        row.cells[0].paragraphs[0].add_run('Description:').bold = True
        row.cells[1].paragraphs[0].add_run('Date').bold = True
        row.cells[2].paragraphs[0].add_run('Amount').bold = True
        row.cells[0].paragraphs[0].paragraph_format.alignment = 1
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        for credit in sorted_credits:
            driver = credit['description'].split()[0]
            amount = credit['amount']
            if driver in driver_sum_credits:
                driver_sum_credits[driver] += amount
            else:
                driver_sum_credits[driver] = amount
            tcredits = tcredits + credit['amount']
            row = CashTable.add_row()
            row.cells[0].text = str(credit['description'])
            row.cells[1].text = str(credit['date'])
            row.cells[2].text = str('${:,.2f}'.format(float(credit['amount'])))
            row.cells[0].paragraphs[0].paragraph_format.alignment = 1
            row.cells[1].paragraphs[0].paragraph_format.alignment = 1
            row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row = CashTable.add_row()
        row.cells[1].paragraphs[0].add_run('Total:').bold = True
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        coloredTotal = row.cells[2].paragraphs[0].add_run('${:,.2f}'.format(tcredits))
        coloredTotal.bold = True
        font = coloredTotal.font
        font.color.rgb = RGBColor(34, 139, 34)
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        formatTableCells(CashTable, 0, 3)
        CashTable.style = 'Table Grid'
        CashTable.allow_autofit = False
        CashTable.autofit = False
        set_column_width(CashTable.columns[0], Inches(5.6))
        set_column_width(CashTable.columns[1], Inches(1.0))
        set_column_width(CashTable.columns[2], Inches(1.0))
        doc.add_paragraph()
        checkamount = checkamount + tcredits
        del row, driver, amount

    # Deduction
    driver_sum_deductions = {}
    if sorted_deductions:
        heading3 = doc.add_paragraph().add_run('Advances and Deductions:')
        heading3.bold = True
        font = heading3.font
        font.size = Pt(12)
        DeductionTable = doc.add_table(0, 3)
        row = DeductionTable.add_row()
        row.cells[0].paragraphs[0].add_run('Description:').bold = True
        row.cells[1].paragraphs[0].add_run('Date').bold = True
        row.cells[2].paragraphs[0].add_run('Amount').bold = True
        row.cells[0].paragraphs[0].paragraph_format.alignment = 1
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        for deduction in sorted_deductions:
            driver = deduction['description'].split()[0]
            amount = deduction['amount']
            if driver in driver_sum_deductions:
                driver_sum_deductions[driver] += amount
            else:
                driver_sum_deductions[driver] = amount
            tdeductions = tdeductions + deduction['amount']
            row = DeductionTable.add_row()
            row.cells[0].text = str(deduction['description'])
            row.cells[1].text = str(deduction['date'])
            row.cells[2].text = str('$-{:,.2f}'.format(float(deduction['amount'])))
            row.cells[0].paragraphs[0].paragraph_format.alignment = 1
            row.cells[1].paragraphs[0].paragraph_format.alignment = 1
            row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row = DeductionTable.add_row()
        row.cells[1].paragraphs[0].add_run('Total:').bold = True
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        coloredTotal = row.cells[2].paragraphs[0].add_run('$-{:,.2f}'.format(tdeductions))
        coloredTotal.bold = True
        font = coloredTotal.font
        font.color.rgb = RGBColor(255, 0, 0)
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        formatTableCells(DeductionTable, 0, 3)
        DeductionTable.style = 'Table Grid'
        DeductionTable.allow_autofit = False
        DeductionTable.autofit = False
        set_column_width(DeductionTable.columns[0], Inches(5.6))
        set_column_width(DeductionTable.columns[1], Inches(1.0))
        set_column_width(DeductionTable.columns[2], Inches(1.0))
        doc.add_paragraph()
        checkamount = checkamount - tdeductions
        del row, driver, amount

    # Scheduled Deduction
    driver_sum_schdeductions = {}
    if sorted_schdeductions:
        heading4 = doc.add_paragraph().add_run('Scheduled Deductions:')
        heading4.bold = True
        font = heading4.font
        font.size = Pt(12)
        SchDeductionTable = doc.add_table(0, 3)
        row = SchDeductionTable.add_row()
        row.cells[0].paragraphs[0].add_run('Description:').bold = True
        row.cells[1].paragraphs[0].add_run('Date').bold = True
        row.cells[2].paragraphs[0].add_run('Amount').bold = True
        row.cells[0].paragraphs[0].paragraph_format.alignment = 1
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        for schdeduction in sorted_schdeductions:
            driver = schdeduction['description'].split()[0]
            amount = schdeduction['amount']
            if driver in driver_sum_schdeductions:
                driver_sum_schdeductions[driver] += amount
            else:
                driver_sum_schdeductions[driver] = amount
            tschdeductions = tschdeductions + schdeduction['amount']
            row = SchDeductionTable.add_row()
            row.cells[0].text = str(schdeduction['description'])
            row.cells[1].text = str(schdeduction['date'])
            row.cells[2].text = str('$-{:,.2f}'.format(float(schdeduction['amount'])))
            row.cells[0].paragraphs[0].paragraph_format.alignment = 1
            row.cells[1].paragraphs[0].paragraph_format.alignment = 1
            row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row = SchDeductionTable.add_row()
        row.cells[1].paragraphs[0].add_run('Total:').bold = True
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        coloredTotal = row.cells[2].paragraphs[0].add_run('$-{:,.2f}'.format(tschdeductions))
        coloredTotal.bold = True
        font = coloredTotal.font
        font.color.rgb = RGBColor(255, 0, 0)
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        formatTableCells(SchDeductionTable, 0, 3)
        SchDeductionTable.style = 'Table Grid'
        SchDeductionTable.allow_autofit = False
        SchDeductionTable.autofit = False
        set_column_width(SchDeductionTable.columns[0], Inches(5.6))
        set_column_width(SchDeductionTable.columns[1], Inches(1.0))
        set_column_width(SchDeductionTable.columns[2], Inches(1.0))
        doc.add_paragraph()
        checkamount = checkamount - tschdeductions
        del row, driver, amount

    # Fuels
    driver_sum_fuels = {}
    if sorted_fuels:
        heading5 = doc.add_paragraph().add_run('Fuels:')
        heading5.bold = True
        font = heading5.font
        font.size = Pt(12)
        FuelTable = doc.add_table(0, 4)
        row = FuelTable.add_row()
        row.cells[0].paragraphs[0].add_run('Driver:').bold = True
        row.cells[1].paragraphs[0].add_run('Description:').bold = True
        row.cells[2].paragraphs[0].add_run('Date').bold = True
        row.cells[3].paragraphs[0].add_run('Amount').bold = True
        row.cells[0].paragraphs[0].paragraph_format.alignment = 1
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row.cells[3].paragraphs[0].paragraph_format.alignment = 1
        for fuel in sorted_fuels:
            driver = fuel['driver'].split()[0]
            amount = fuel['amount']
            if driver in driver_sum_fuels:
                driver_sum_fuels[driver] += amount
            else:
                driver_sum_fuels[driver] = amount
            tfuels = tfuels + fuel['amount']
            row = FuelTable.add_row()
            row.cells[0].text = str(fuel['driver'])
            row.cells[1].text = str(fuel['description'])
            row.cells[2].text = str(fuel['date'])
            row.cells[3].text = str('$-{:,.2f}'.format(float(fuel['amount'])))
            row.cells[0].paragraphs[0].paragraph_format.alignment = 1
            row.cells[1].paragraphs[0].paragraph_format.alignment = 1
            row.cells[2].paragraphs[0].paragraph_format.alignment = 1
            row.cells[3].paragraphs[0].paragraph_format.alignment = 1
        row = FuelTable.add_row()
        row.cells[2].paragraphs[0].add_run('Total:').bold = True
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        coloredTotal = row.cells[3].paragraphs[0].add_run('$-{:,.2f}'.format(tfuels))
        coloredTotal.bold = True
        font = coloredTotal.font
        font.color.rgb = RGBColor(255, 0, 0)
        row.cells[3].paragraphs[0].paragraph_format.alignment = 1
        formatTableCells(FuelTable, 0, 4)
        FuelTable.style = 'Table Grid'
        FuelTable.allow_autofit = False
        FuelTable.autofit = False
        set_column_width(FuelTable.columns[0], Inches(3.6))
        set_column_width(FuelTable.columns[1], Inches(2.0))
        set_column_width(FuelTable.columns[2], Inches(1.0))
        set_column_width(FuelTable.columns[3], Inches(1.0))
        doc.add_paragraph()
        checkamount = checkamount - tfuels
        del row, driver, amount

    # Trucks analise
    total_drivers = list(set(total_drivers))
    if len(total_drivers) > 1:
        totalDrivers = set(driver_sum_loads.keys()) | set(driver_sum_credits.keys()) | set(driver_sum_deductions.keys()) | set(driver_sum_schdeductions.keys()) | set(driver_sum_fuels.keys())

        heading6 = doc.add_paragraph().add_run('Trucks:')
        heading6.bold = True
        font = heading6.font
        font.size = Pt(12)
        TruckTable = doc.add_table(0, 7)
        row = TruckTable.add_row()
        row.cells[0].paragraphs[0].add_run('Number').bold = True
        row.cells[1].paragraphs[0].add_run('Amount').bold = True
        row.cells[2].paragraphs[0].add_run('Credits').bold = True
        row.cells[3].paragraphs[0].add_run('Deductions').bold = True
        row.cells[4].paragraphs[0].add_run('Sch.Deductions').bold = True
        row.cells[5].paragraphs[0].add_run('Fuels').bold = True
        row.cells[6].paragraphs[0].add_run('Total').bold = True
        row.cells[0].paragraphs[0].paragraph_format.alignment = 1
        row.cells[1].paragraphs[0].paragraph_format.alignment = 1
        row.cells[2].paragraphs[0].paragraph_format.alignment = 1
        row.cells[3].paragraphs[0].paragraph_format.alignment = 1
        row.cells[4].paragraphs[0].paragraph_format.alignment = 1
        row.cells[5].paragraphs[0].paragraph_format.alignment = 1
        row.cells[6].paragraphs[0].paragraph_format.alignment = 1
        for truck in totalDrivers:
            row = TruckTable.add_row()
            row.cells[0].text = truck
            row.cells[1].text = '${:,.2f}'.format(float(driver_sum_loads.get(truck, 0))) if float(driver_sum_loads.get(truck, 0)) != 0 else ''
            row.cells[2].text = '${:,.2f}'.format(float(driver_sum_credits.get(truck, 0))) if float(driver_sum_credits.get(truck, 0)) != 0 else ''
            row.cells[3].text = '$-{:,.2f}'.format(float(driver_sum_deductions.get(truck, 0))) if float(driver_sum_deductions.get(truck, 0)) != 0 else ''
            row.cells[4].text = '$-{:,.2f}'.format(float(driver_sum_schdeductions.get(truck, 0))) if float(driver_sum_schdeductions.get(truck, 0)) != 0 else ''
            row.cells[5].text = '$-{:,.2f}'.format(float(driver_sum_fuels.get(truck, 0))) if float(driver_sum_fuels.get(truck, 0)) != 0 else ''
            row.cells[6].text = '${:,.2f}'.format(float(driver_sum_loads.get(truck, 0))+float(driver_sum_credits.get(truck, 0))-float(driver_sum_deductions.get(truck, 0))-float(driver_sum_schdeductions.get(truck, 0))-float(driver_sum_fuels.get(truck, 0)))
            row.cells[0].paragraphs[0].paragraph_format.alignment = 1
            row.cells[1].paragraphs[0].paragraph_format.alignment = 1
            row.cells[2].paragraphs[0].paragraph_format.alignment = 1
            row.cells[3].paragraphs[0].paragraph_format.alignment = 1
            row.cells[4].paragraphs[0].paragraph_format.alignment = 1
            row.cells[5].paragraphs[0].paragraph_format.alignment = 1
            row.cells[6].paragraphs[0].paragraph_format.alignment = 1
        formatTableCells(TruckTable, 0, 7)
        TruckTable.style = 'Table Grid'
        TruckTable.allow_autofit = False
        TruckTable.autofit = False
        set_column_width(TruckTable.columns[0], Inches(1.0))
        set_column_width(TruckTable.columns[1], Inches(1.0))
        set_column_width(TruckTable.columns[2], Inches(1.0))
        set_column_width(TruckTable.columns[3], Inches(1.2))
        set_column_width(TruckTable.columns[4], Inches(1.4))
        set_column_width(TruckTable.columns[5], Inches(1.0))
        set_column_width(TruckTable.columns[6], Inches(1.0))
        doc.add_paragraph()

    # Summary
    checkAmount = doc.add_table(1, 2)
    col1 = checkAmount.rows[0].cells[0].paragraphs[0].add_run('Check Amount')
    col1.bold = True
    checkAmount.rows[0].cells[0].paragraphs[0].paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER
    col2 = checkAmount.rows[0].cells[1].paragraphs[0].add_run(str('${:,.2f}'.format(float(checkamount))))
    col2.bold = True
    checkAmount.rows[0].cells[1].paragraphs[0].paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER
    font = col2.font
    if checkamount > 0:
        font.color.rgb = RGBColor(34, 139, 34)
    else:
        font.color.rgb = RGBColor(255, 0, 0)
    checkAmount.cell(0, 0).width = Inches(1.0)
    checkAmount.cell(0, 1).width = Inches(1.0)
    checkAmount.style = 'Table Grid'
    checkAmount.allow_autofit = False
    checkAmount.autofit = False
    set_column_width(checkAmount.columns[0], Inches(1.0))
    set_column_width(checkAmount.columns[1], Inches(1.0))
    checkAmount.alignment = WD_TABLE_ALIGNMENT.RIGHT

    if os.path.isdir(parentDir + period) == False:
        os.mkdir(parentDir + period)
        if os.path.isdir(parentDir + period + "/" + str(userId)) == False:
            os.mkdir(parentDir + period + "/" + str(userId))
    #
    doc.save(parentDir + 'temp/tempstatement.docx')
    os.chmod(parentDir + 'temp/tempstatement.docx', 0o755)
    time.sleep(1)
    convert_docx_to_pdf(parentDir,period,userId)
    time.sleep(1)
    os.remove(parentDir + "temp/tempstatement.docx")
    if os.path.isfile(parentDir + period + "/" + str(userId)+"/StatementParentCustomReport.pdf"):
        return True
    else:
        return None

db_config = {
   "host": "localhost",
   "user": "rsghihjbaoh",
   "password": "dD8X/p99JY1w",
   "database": "asexpresstms"
}

connection = mysql.connector.connect(**db_config)

parentDir = '/var/www/html/tms/public/statementsfile/'

date_pattern = re.compile(r'^\d{4}-\d{2}-\d{2}$')
pass1 = pass2 = 0

if len(sys.argv)>1:
    userId = sys.argv[1]
    period = sys.argv[2]

    if userId:
        pass1 = 1

    if date_pattern.match(period):
        pass2 = 1

    if pass1 == 1 and pass2 == 1:
        user = getuserinfo(userId)
        statement = getstatement(userId,period)
        unit = None
        if user['role'] == 'driver':
            unit = gettruckinfo(userId)
            if statement:
                res = createPDF(statement, userId, period, parentDir, unit)
                if res:
                    print('ok', end='')
        if user['role'] == 'owner':
            if statement:
                sorted_loads = sorted_credits = sorted_deductions = sorted_schdeductions = sorted_fuels = None
                if statement['loads']:
                    sorted_loads = sorted(statement['loads'], key=lambda x: x['driver'])
                if statement['credits']:
                    sorted_credits = sorted(statement['credits'], key=lambda x: x['description'])
                if statement['deductions']:
                    sorted_deductions = sorted(statement['deductions'], key=lambda x: x['description'])
                if statement['scheduled']:
                    sorted_schdeductions = sorted(statement['scheduled'], key=lambda x: x['description'])
                if statement['fuels']:
                    sorted_fuels = sorted(statement['fuels'], key=lambda x: x['driver'])
                res = createOwnerPDF(sorted_loads, sorted_credits, sorted_deductions, sorted_schdeductions, sorted_fuels, userId, period, parentDir)
                if res:
                    print('ok', end='')
            else:
                print('Statement cannot be saved.', end='')
        else:
            print('Statement was NOT exists.', end='')
    else:
        print('Statement was NOT founded.', end='')