from terminaltables import AsciiTable
import pymysql.cursors
import time

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='--',
                             password='--',
                             db='olap_test',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
start = time.time()
try:
    with connection.cursor() as cursor:
        sql = "select country, customer_name, round(sum(amount),2) as amount, year(date1) as year from olap_data where country in ('Brazil', 'Venezuela', 'Argentina', 'Mexico') group by country, customer_name, year(date1)"
        cursor.execute(sql)
        result = cursor.fetchall()
finally:
    connection.close()

def addRowTable(table, col1, col2, col3, col4, col5):
    table.append([str(col1), str(col2), str(col3), str(col4), str(col5)])

table_data = [['Land', 'Kunde', '%10s' % 'Umsatz 1994', '%10s' % 'Umsatz 1995', '%10s' % 'Umsatz 1996']]
country = ''
customer = ''
for row in result:
    if country != row['country']:
        country = row['country']
        addRowTable(table_data, country, '', '', '', '')
    if customer != row['customer_name']:
        customer = row['customer_name']
        # get revenues from years 1994,1995 and 1996
        revenue_years = [item for item in result if item['country'] == country and item['customer_name'] == customer]
        revenue_per_year = [0.0, 0.0, 0.0]
        for revenue_year in revenue_years:
            if 1994 == revenue_year['year']:
                revenue_per_year[0] = revenue_year['amount']
            if 1995 == revenue_year['year']:
                revenue_per_year[1] = revenue_year['amount']
            if 1996 == revenue_year['year']:
                revenue_per_year[2] = revenue_year['amount']
        addRowTable(table_data, '', customer, "%10.2f" % revenue_per_year[0], "%10.2f" % revenue_per_year[1], "%10.2f" % revenue_per_year[2])
table = AsciiTable(table_data)
print(table.table)
end = time.time()
print("Time taken: %5.2f seconds" %(end - start))