#!/usr/bin/env python
"""
get_exel_report.py

Will generate an excel file from serveral different files. 

Usage:
    {app_name} [-s SUMARYFILE] 
    {app_name} --help

-h --help           show this help
-s SUMARYFILE       file where all the tests result has been added [default: ~/DCO_TEST_SCRIPTS/test-postgresql/product_ingestion_test/test_implementation/summary.txt]

"""
from subprocess import Popen, PIPE
from docopt import docopt
import os
import sys
import csv 
from xlsxwriter.workbook import Workbook

def create_header(workbook, header, worksheet):
    headerformat = workbook.add_format({'bold': True, 'border':7, 'bg_color' :'A1D1E7'})
    column = 0
    for head in header:
        worksheet.write(0,column,head,headerformat)
        column +=1

def format_result_cells(workbook, worksheet):
    # Light red fill with dark red text.
    red_format = workbook.add_format({'bg_color':   '#FFC7CE',
                                   'font_color': '#9C0006'})
    red_format.set_border(7)

    # Green fill with dark green text.
    green_format = workbook.add_format({'bg_color':   '#C6EFCE',
                                   'font_color': '#006100'})
    green_format.set_border(7)
    worksheet.conditional_format('B2:B100', {'type':     'text',
                                             'criteria': 'containing',
                                             'value':    'PASSED',
                                             'format':   green_format})
    worksheet.conditional_format('B2:B100', {'type':     'text',
                                             'criteria': 'containing',
                                             'value':    'FAILED',
                                             'format':   red_format})


def add_worksheet(workbook,csvfile,text_wrap_format):
    
    # create Test case sheet
    worksheet = workbook.add_worksheet(os.path.basename(csvfile[:-4]))

    # Format columns
    worksheet.set_column(0, 0, 40)
    worksheet.set_column(1, 1, 80)
    worksheet.set_column(2, 2, 80)
    worksheet.set_column(3, 3, 80)
    worksheet.set_column(4, 4, 80)
    
    # create headers
    headers = ['Row ID', 'Differences ...']
    create_header(workbook,headers,worksheet)
    with open(csvfile, 'rt') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                if len(row) == 1 :
                    worksheet.merge_range(r+1, c,r+1, c+5,col.decode('utf-8'),workbook.add_format({'bold': True}))
                else:
                    worksheet.write(r+1, c, col.strip().decode('utf-8'))

def main():
    args = docopt(__doc__.format(app_name=os.path.basename(sys.argv[0])))
    
    with open(args['-s'],'r') as summary:
        workbook = Workbook(args['-s'][:-4] + '.xlsx')
        text_wrap_format = workbook.add_format()
        text_wrap_format.set_text_wrap()
        link_format = workbook.add_format({'color': 'blue', 'underline': 1})
        
        # create Summary sheet
        worksheet = workbook.add_worksheet('Summary')
        
        # Format columns
        worksheet.set_column(0, 0, 40)
        worksheet.set_column(1, 1, 15)
        worksheet.set_column(2, 2, 15)
        worksheet.set_column(3, 3, 20)
        worksheet.set_column(4, 4, 15)
        worksheet.set_column(5, 5, 15)
        worksheet.set_column(6, 6, 15)
        worksheet.set_column(7, 7, 15)
        worksheet.set_column(8, 8, 15)
        worksheet.set_column(9, 9, 15)
        worksheet.set_column(10, 10, 35)
        
        # create headers
        headers = ['Test ID', 'Test result', 'Rows in Oracle','Not found in Postgres','Found but different','Found and equal','Rows in Postgres','Not found in Oracle','Found but different','Found and equal', 'More information']
        create_header(workbook,headers,worksheet)
        
        # Populate the summary sheet
        reader = csv.reader(summary)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                if os.path.exists(col):
                    # Write a link to another sheet where the whole report will be added
                    newsheetname = os.path.basename(col[:-4])
                    worksheet.write(r+1, c, newsheetname, link_format)
                    add_worksheet(workbook,col,text_wrap_format)
                    worksheet.write(r+1, c,"internal:'{}'!A1".format(newsheetname),link_format)
                else:
                    worksheet.write(r+1, c, col.decode('utf-8'))
                    
        format_result_cells(workbook, worksheet)
        workbook.close()
    
if __name__ == '__main__':
    main()