import openpyxl
from tqdm import tqdm
import smtplib
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.utils import COMMASPACE, formatdate
from os.path import basename

'''
This script takes all loans in the "Master Wire" excel file and splits them into different worksheets
by their funding cost center. Then, it takes those new worksheets and emails them to the appropriate
manager based on the funding cost centers they manage.
'''

filename = 'test_master_wire.xlsx'
wb = openpyxl.load_workbook(filename=filename, data_only=True, keep_vba=True)
all_sheets = wb.get_sheet_names()
funding_cost_center_dict= {}
all_fcc = []
all_loan_dict = {}
headers = []
gmail_user = 'xxxxxxx@guildmortgage.net'
gmail_password = 'xxxxxxxxx'

print ('getting emails...')
#create dict with funding cost centers and emails from email list sheet
def get_emails(funding_cost_center_dict):
    email_list_sheet = wb.get_sheet_by_name('Wire File Contacts')
    for rownum in range(2, email_list_sheet.max_row+1):
        fcc = email_list_sheet.cell(row=rownum, column=1).value
        funding_cost_center_dict[fcc] = []
        ops_mgr_emails = email_list_sheet.cell(row=rownum, column=2).value
        funding_mgr_emails = email_list_sheet.cell(row=rownum, column=3).value
        def split_semis(emails):
            if ';' in emails:
                emails = emails.split(';')
                for email in emails:
                    email = email.strip()
                    funding_cost_center_dict[fcc].append(email)
            else:
                funding_cost_center_dict[fcc].append(emails)
        split_semis(ops_mgr_emails)
        split_semis(funding_mgr_emails)
    return funding_cost_center_dict
email_dict = get_emails(funding_cost_center_dict)

#############################################################################

print ('creating new worksheets based off funding cost center...')
#split loans from master wire sheet into separate excel files based on fcc
def split_loans(all_fcc, all_loan_dict, headers):
    master_wire_sheet = wb.get_sheet_by_name('Super Wire File')
    print ('getting unique funding cost centers...')
    pbar = tqdm(total=master_wire_sheet.max_row)
    for rownum in range(2, master_wire_sheet.max_row+1):
        pbar.update(1)
        current_loan = []
        fcc = master_wire_sheet.cell(row=rownum, column=26).value
        all_fcc.append(fcc)
    pbar.close()
    all_fcc = set(all_fcc)
    for fcc in all_fcc:
        all_loan_dict[fcc] = []
    #get headers into list
    for col in range(1,526):
        headers.append(master_wire_sheet.cell(row=1, column=col).value)
    print ('adding all loans to python dict...')
    pbar = tqdm(total=master_wire_sheet.max_row)
    for rownum in range(2, master_wire_sheet.max_row+1):
        pbar.update(1)
        fcc = master_wire_sheet.cell(row=rownum, column=26).value
        for col in range(1,525+1):
            current_loan.append(master_wire_sheet.cell(row=rownum, column=col).value)
        all_loan_dict[fcc].append(current_loan)
        current_loan = []
    pbar.close()
    wb.close()
split_loans(all_fcc, all_loan_dict, headers)

#create worksheets with headers for every FCC
def create_worksheets(all_fcc):
    for fcc in all_fcc:
        new_filename = str(fcc) + ' Workbook.xlsx'
        try:
            new_wb = openpyxl.load_workbook(filename=new_filename, data_only=True)
        except:
            new_wb = openpyxl.Workbook()
        new_ws = new_wb.active
        for h in range(0, len(headers)):
            new_ws.cell(row=1, column=h+1).value = headers[h]
        new_wb.save(new_filename)
        new_wb.close()
all_fcc = set(all_fcc)
create_worksheets(all_fcc)

#get row incrementor to paste loans
def count_rows(all_fcc):
    row_counter_dict = {}
    for fcc in all_fcc:
        row_counter_dict[fcc] = 2
    return row_counter_dict
row_counter_dict = count_rows(all_fcc)

print ('updating all new workbooks...')
#adding all to dictionary
def add_loans_to_workbooks(all_loan_dict, row_counter_dict):
    total = 0
    for key,val in all_loan_dict.items():
        for v in val:
            total += 1
    pbar = tqdm(total=total)
    for key, val in all_loan_dict.items():
        #update spreadsheet
        new_fcc = key
        new_filename = str(new_fcc) + ' Workbook.xlsx'
        new_wb = openpyxl.load_workbook(filename=new_filename, data_only=True)
        for v in val:
            pbar.update(1)
            new_ws = new_wb.active
            new_max_row = row_counter_dict[new_fcc]

            for col in range(1,526):
                new_ws.cell(row=new_max_row, column=col).value = v[col-1]
            row_counter_dict[new_fcc] += 1

        new_wb.save(new_filename)
        new_wb.close()
    pbar.close()
add_loans_to_workbooks(all_loan_dict, row_counter_dict)

print ('Sending emails...')
#send an email to each manager responsible for the fccs
def send_emails(funding_cost_center_dict, gmail_user, gmail_password):
    pbar = tqdm(total=len(funding_cost_center_dict))
    for fcc, email_list in funding_cost_center_dict.items():
        pbar.update(1)
        for email in email_list:
            msg = MIMEMultipart()
            msg['From'] = 'xxxxxxx@guildmortgage.net'
            msg['To'] = email
            msg['Subject'] = 'Tester Message'
            text = 'Hello, please review and send back asap'

            msg.attach(MIMEText(text))
            attach_file = str(fcc) + ' Workbook.xlsx'
            with open(attach_file, "rb") as fil:
                part = MIMEApplication(
                    fil.read(),
                    Name=basename(attach_file)
                )
            # After the file is closed
            part['Content-Disposition'] = 'attachment; filename="%s"' % basename(attach_file)
            msg.attach(part)

            server = smtplib.SMTP_SSL('smtp.gmail.com', 465)
            server.ehlo()
            server.login(gmail_user, gmail_password)
            server.sendmail('xxxxxxx@guildmortgage.net', email, msg.as_string())
            server.close()
    pbar.close()
send_emails(funding_cost_center_dict, gmail_user, gmail_password)

print ('Complete.')