#!/usr/bin/env python3
from pypdf import PdfReader
from datetime import datetime
import mysql.connector
import os
import re
import sys
import tabula
import csv
import time
import pandas as pd
import openpyxl


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

# main
def checkfile(parentDir):
    if os.path.exists(parentDir+'output.csv'):
        os.remove(parentDir+'output.csv')
    files = os.listdir(parentDir)
    if len(files)==1:
        print(files)
        return files[0].split('.')
    return 0

def pdftocsv(file,parentDir):
    tabula.convert_into(parentDir+file[0]+'.'+file[1], parentDir+'output.csv', output_format="csv", pages='all')
    if os.path.exists(parentDir+'output.csv'):
        return 1
    else:
        return 0

def exceltocsv(file,parentDir):
    df = pd.read_excel(parentDir+file[0]+'.'+file[1])
    df.to_csv(parentDir+'output.csv', index=False)
    if os.path.exists(parentDir+'output.csv'):
        return 1
    else:
        return 0

def checkrecord(card,truck,date,amount):
    cursor = connection.cursor()
    select_query = "SELECT id FROM fuels WHERE card = %s and truck = %s and fueldate = %s and amount = %s"
    cursor.execute(select_query, (card,truck,date,amount,))
    result = cursor.fetchone()
    if result:
        cursor.close()
        return False
    cursor.close()
    return True

def create(name,card,truck,date,city,state,type,amount):
    attr = {
        'name': name,
        'type': str(type),
        'card': card,
        'truck': truck,
        'fueldate': date,
        'address': str(city)+', '+str(state),
        'amount': amount,
        'is_used':0,
    }
    cursor = connection.cursor()
    print(attr)
    sql_query = 'INSERT INTO fuels (name, type, card, truck, fueldate, address, amount, is_used, created_at, updated_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())'
    data_to_insert = tuple(attr.values())
    cursor.execute(sql_query, data_to_insert)
    connection.commit()

def parsecsvfile(file, type):
    display = 0
    with open(file, 'r') as csvfile:
        csvreader = csv.reader(csvfile)
        if type == 'pdf':
            for row in csvreader:
                if display:
                    print(row)
                    if row[3]:
                        print(row)
                        row[11] = row[11].replace(',','.')
                        row[1] = re.findall(r'\d+',row[1])
                        row[3] = datetime.strptime(row[3],'%d.%m.%Y').strftime('%Y-%m-%d')
                        if checkrecord(row[1][0],row[2],row[3],row[11]):
                            create(row[0],row[1][0],row[2],row[3],row[5],row[6],row[8],row[11])
                if row[0] == 'Driver Name' and row[1] == 'Card #' and row[3] == 'Tran Date':
                    display = 1
        if type == 'excel':
            for row in csvreader:
                if display:
                    print(row)
                    if row[4]:
                        row[14] = row[14].replace(',','.')
                        row[2] = re.findall(r'\d+',row[2])
                        row[4] = datetime.strptime(row[4],'%Y-%m-%d 00:00:00').strftime('%Y-%m-%d')
                        if checkrecord(row[2][0],row[3],row[4],row[14]):
                            create(row[1],row[2][0],row[3],row[4],row[8],row[9],row[11],row[14])
                if row[1] == 'Driver Name' and row[2] == 'Card #' and row[3] == 'Unit':
                    display = 1

parentDir = '/var/www/html/tms/public/uploads/fuel/'
file = checkfile(parentDir)
connection = mysql.connector.connect(**db_config)
if file:
    if file[1] == 'pdf' or file[1] == 'PDF':
        res = pdftocsv(file,parentDir)
        if res:
            parsecsvfile(parentDir+'output.csv','pdf')
            print('success', end='')
        else:
            print('CSV file not exist.', end='')
    if file[1] == 'xlsx' or file[1] == 'xls' or file[1] == 'XLSX' or file[1] == 'XLS':
        res = exceltocsv(file,parentDir)
        if res:
            parsecsvfile(parentDir+'output.csv','excel')
            print('success', end='')
else:
    print('File was NOT parsed.', end='')