'''Created Jun 4, 2021 Levi'''
import warnings
import sqlite3
# from sqlite3 import (
#     Connection as con,
#     Cursor as cur
#     )
import shutil
# import os
# import openpyxl as xl
# import xlsxwriter as xlwt
# from xlsxwriter.worksheet import (
#     Worksheet as worksheet
#     )
import pandas as pd
from pandas import DataFrame as df  # @UnusedImport

warnings.filterwarnings('ignore', module='xlsxwriter')
con = sqlite3.connect('file:Revisal.db?mode=ro', uri=True)
cur = con.cursor()
with open('revisal.sql') as sql_query:
    query = sql_query.read()

# rows = cur.execute(query)
df = pd.read_sql_query(
    query,
    con,
    parse_dates=['DataContract', 'DataInceput', 'DataConsemn', 'DataIncetare']
)
print(df.head())
fname = 'rvs.xlsx'
writer = pd.ExcelWriter(
    fname,
    engine='xlsxwriter',
    date_format='mmmm dd yy',
    datetime_format='dd-mmm-yy'
)
df.to_excel(writer, sheet_name='Sheet1', index=True)
workbook = writer.book
workbook.add_vba_project('./vbaProject.bin')

worksheet = writer.sheets['Sheet1']
# worksheet = workbook.add_worksheet()
worksheet.freeze_panes(1, 0)
worksheet.autofilter('A1:M500')
set_columns = {
    'A:A': 5,
    'B:B': 23,
    'C:C': 5,
    'D:D': 12,
    'E:E': 12,
    # 'F:F': 12,
    # 'I:I': 12,
    'H:H': 12,
    'L:L': 12
}
for column, width in set_columns.items():
    worksheet.set_column(column, width)
writer.save()
writer.close()
shutil.move('rvs.xlsx', 'rvs.xlsm')