#! /usr/bin/python3
# pass in a month, get expense report for that month
# return csv
# ** TODO ** pass in date range rather than defaulting to now
#   ** this current method will fail between 1/1 - 1/31
# ** TODO ** support last known exp ID
def get_expense_report():
    import requests
    import datetime

    now = datetime.datetime.now()
    session = requests.Session()
    session.auth = (username, password)
    auth = session.post('https://manage2.cpanel.net/login.cgi')

    params = dict(
        user="all",
        card="",
        company="",
        dept="----",
        justified="",
        hasimage="",
        smonth="1",
        sday="1",
        syear=str(now.year),
        emonth=str(now.month),
        eday=str(now.day),
        eyear=str(now.year),
        submit="Filter",
        download=1
    )
    url = 'https://manage2.cpanel.net/old_list_txn.cgi'

    print("Requesting ", url, params)
    response = session.get(
        url, params=params, stream=True, cookies=auth.cookies)

    return response


# pass in csv, return clean list of lists, each row a top level list
def sanitize_expense_report(report):
    import csv
    data = [row for row in csv.reader(report.content.splitlines())]
    return data


# pass in list of lists, each top level a row
def append_report_to_budget(sanitized_report):
    from openpyxl import load_workbook
    from openpyxl import Workbook
    from pprint import pprint as pp

    wb = load_workbook(
        path + 'Product_Dev_Budget.xlsx')

    sheet = wb['RAW DATA']

    # delete test sheet
    wb.remove(sheet)

    wb.save(path + "Product_Dev_Budget.xlsx")

 # create new sheet

    wb.create_sheet('RAW DATA')

    active_sheet = wb["RAW DATA"]
    # active_sheet = wb.get_sheet_by_name("RAW DATA")

    print(active_sheet)

    for row in sanitized_report:

    # exclude the overall total row
        # removed "Marketing"
        if (row[0] != 'Overall Total: ' and row[3] in ["Documentation", "Product Development", "Quality Assurance"]):
            active_sheet.append(row)
            pp(row[0])

            # adjust column widths
            for col in active_sheet.columns:
                 max_length = 0
                 column = col[0].column # Get the column name
                 for cell in col:
                     try: # Necessary to avoid error on empty cells
                         if len(str(cell.value)) > max_length:
                             max_length = len(cell.value)
                     except:
                         pass
                 adjusted_width = (max_length + 2) * 1.2
                 active_sheet.column_dimensions[column].width = adjusted_width

                 # apply Wrap Text style and set widths for columns D, E, and G
                 from openpyxl.styles import Alignment
                 wrap_alignment = Alignment(wrap_text=True)

                 cell.alignment = wrap_alignment

                 active_sheet.column_dimensions['D'].width = 30
                 active_sheet.column_dimensions['E'].width = 30
                 active_sheet.column_dimensions['G'].width = 30

                 # hide active_sheet
                 active_sheet.sheet_state = 'hidden'

    wb.save(path + "Product_Dev_Budget.xlsx")

if __name__ == '__main__':
    import ConfigParser
    data_dir = "/Users/justinehernandez/Desktop/Python/Phil_Python/"
    config = ConfigParser.ConfigParser()
    config.read(data_dir + "auth.config")
    username = config.get("auth", "username")
    password = config.get("auth", "password")
    path = config.get("general", "path")

    response = get_expense_report()
    sanitized_report = sanitize_expense_report(response)
    append_report_to_budget(sanitized_report)