from openpyxl.styles import Font
from openpyxl.styles import PatternFill
# from openpyxl.styles import numbers
from openpyxl.utils import get_column_letter
bold = Font(bold=True)


def Write_Report(y, columnFormat, SSheader, sheet):
    results = []

# Loops through the report xml, sends the value down to the Format Check to determine what the column should be setup as and writes the results to the spreedsheet
    count = 0
    for child in y.report:
        for childs in child:
            if childs.text is not "":
                asdf = Format_Check(columnFormat, SSheader, childs, count)
            else:
                asdf = childs.text
            results.append(asdf)
            count = count + 1
        count = 0
        if results[0] != "":
            sheet.append(results)
        results = []


def Format_Check(columnFormat, SSheader, childs, count):
    check = columnFormat[SSheader[count]]
    results = []

#Checks what the column format is and sets the value accordingly.
    if check == "None":
        return childs.text
    elif check == "Currency":
        if '$' in childs.text:
            convert = childs.text
            apply = convert[1:]
            results.append(float(apply.replace(',', '')))
            return results[0]
        else:
            return float(childs.text)
    elif check == "Integer":
        return int(childs.text)
    else:
        return childs.text


def Format_Report(sheet, SSheader, columnFormat):

    count = 0
    rowcount = 2

#Sets any currency related fields to the correct format. Note that this is currently only checking the headers from the config file. Anything added after the headers are loaded will cause issues. Hence the or statement in the if.
    #I'm working on a better way to handle this
    count = 0
    rowcount = 1
    for column in SSheader:
        check = columnFormat[SSheader[count]]
        if check == "Currency" or sheet['H1'].value == "Balance Due":
            for row in sheet.rows:
                if rowcount != 1:
                    sheet.cell(column=count+1, row=rowcount).number_format = '$#,##0.00_);($#,##0.00)'
                # if rowcount < sheet.max_row:
                rowcount += 1
        count += 1
        rowcount = 1

def Save_Report(wb, sheet, sheet2, sheet3, SSheader, detail_header, columnFormat):

# adds total fields at bottom of report
    summarylength = sheet.max_row
    itemlength = sheet2.max_row
    productlength = sheet3.max_row

    PrintQuantity = 0
    for x in detail_header:
        if x != "Printing Option: PrintingQuantity":
            PrintQuantity += 1
        else:
            break
    PrintQuantity += 1

    ItemPrice = 0
    for x in detail_header:
        if x != "Item Price":
            ItemPrice += 1
        else:
            break
    ItemPrice += 1

    PrintQuantityColumn = get_column_letter(PrintQuantity)
    ItemPriceColumn = get_column_letter(ItemPrice)

    lightGreen = 'ADFF2F'
    darkGreen = '006400'

    sheet.cell(column = 5, row=summarylength+1, value="=SUM(E2:E"+str(summarylength)+")")
    sheet.cell(column = 6, row=summarylength+1, value="=SUM(F2:F"+str(summarylength)+")")
    sheet.cell(column = 7, row=summarylength+1, value="=SUM(G2:G"+str(summarylength)+")")
    sheet.cell(column = 8, row=summarylength+1, value="=SUM(H2:H"+str(summarylength)+")")
    sheet.cell(column = 8, row = summarylength+1).fill = PatternFill(start_color=lightGreen, end_color=darkGreen, fill_type='solid')
    sheet.cell(column = 8, row= summarylength+1).font = Font(color=darkGreen)

    sheet2.cell(column=PrintQuantity, row=itemlength + 1, value="=SUM(" + PrintQuantityColumn + "2:" + PrintQuantityColumn + str(itemlength) + ")")
    sheet2.cell(column=ItemPrice, row=itemlength + 1, value="=SUM(" + ItemPriceColumn + "2:" + ItemPriceColumn + str(itemlength) + ")")




    # sheet2.cell(column=PrintQuantity, row=itemlength + 1, value="=SUM(G2:G" + str(itemlength)+")")
    # sheet2.cell(column=ItemPrice, row=itemlength + 1, value="=SUM(H2:H" + str(itemlength)+")")

    # Format_Report misses this one. Whoops
    sheet2.cell(column=8, row=itemlength + 1).number_format = '$#,##0.00_);($#,##0.00)'


    sheet3.cell(column=2, row=productlength + 1, value="=SUM(B2:B" + str(productlength)+")")
    sheet3.cell(column=3, row=productlength + 1, value="=SUM(C2:C" + str(productlength)+")")




    count = 0
    rowcount = 2

#Formats a few of the hardcoded currency fields
    count = 0
    rowcount = 1
    for column in SSheader:
        check = columnFormat[SSheader[count]]
        for row in sheet.rows:
            if rowcount != 1:
                # print("max", sheet.max_row)
                # print("count", count)
                sheet.cell(column=5, row=rowcount).number_format = '$#,##0.00_);($#,##0.00)'
                sheet.cell(column=6, row=rowcount).number_format = '$#,##0.00_);($#,##0.00)'
                sheet.cell(column=7, row=rowcount).number_format = '$#,##0.00_);($#,##0.00)'
                sheet.cell(column=8, row=rowcount).number_format = '$#,##0.00_);($#,##0.00)'
                if rowcount < sheet.max_row:
                    rowcount += 1
        count += 1
        rowcount = 2
    count = 1
    rowcount = 2

    for row in sheet3.rows:
        if count != 1:
            sheet3.cell(column=3, row=count).number_format = '$#,##0.00_);($#,##0.00)'
        count += 1

    #Makes the header rows Bold
    for cell in sheet["1:1"]:
        cell.font = bold
    for cell in sheet2["1:1"]:
        cell.font = bold
    for cell in sheet3["1:1"]:
        cell.font = bold


    # print("Enter a filename for the report: ")
    # filename = input()
    wb.save('report.xlsx')