import win32com.client
import pythoncom
import re
import os
import bptRelease
import readExcel
import generateHtml
#import sshConnection

class Handler_Class(object):
    def OnNewMailEx(self, receivedItemsIDs):
        # RecrivedItemIDs е колекция от ID-та мейли, разделени със запетая, тъй като понякога може да получим повече от един имейл едновременно

        for ID in receivedItemsIDs.split(","):
            mail = outlook.Session.GetItemFromID(ID)
            subject = mail.Subject # заглавие на мейла
            has_bpt_match = re.match(r"^([\s&,]*BPT\d{4}\.\d{2}\.\d{2}-HF\d{3}[\s&,]*)+$", subject, re.M|re.I) # От заглавието на мейла вади build, номер на hotfix и цялото име на hotfix
            print(has_bpt_match)
            mail_answer = outlook.CreateItem(0)
            if has_bpt_match:
                print("PROCESSING BPT RELEASE...")
                release_details = bptRelease.read_mail(mail) #build_id, hotfix_id, full hotfix name
                hotfix_names = []
                builds = []
                print(release_details)
                for hotfix in release_details:
                    hotfix_names.append(hotfix[0])
                    if hotfix[1] not in builds:
                        builds.append(hotfix[1])
                for hotfix in release_details:
                    correct_version = bptRelease.read_json_hf(hotfix[1], hotfix[2]) #проверява в своеобразната базичка дали всичко е наред с версиите и след това записва hotfixa в нея
                    if not correct_version:
                        print("Something wrong with hotfix version")
                        #Изпраща отговор в зависимост от това какво не е наред
                        return
                #sshConnection.ssh_connection(hotfix_names, "BPT1003.10.50")
                excel_filename = bptRelease.connect_bpt_upload()[0]
                version = bptRelease.connect_bpt_upload()[1]
                hotfixes_info = readExcel.read_xlsx_file(excel_filename, release_details)
                html_body = generateHtml.generate_html_file(hotfixes_info, builds, version)
                mail_answer.Subject = 'HFs on Top of build '
                for build in builds:
                     mail_answer.Subject += 'BPT' + build + ' '
                mail_answer.To = 'Borislav_Stankulov@a1.bg'
                #mail_answer.GetInspector
                mail_answer.HTMLBody = html_body
                attachment  = r"C:\BPTWorkingFolder\\" + excel_filename
                mail_answer.Attachments.Add(attachment)
                mail_answer.Display(True)
                #mail_answer.Send()
                print('Listening for new emails.....')

outlook = win32com.client.DispatchWithEvents("Outlook.Application", Handler_Class)
print("LISTENING FOR EMAILS")
#and then an infinite loop that waits for events.
pythoncom.PumpMessages()





import os
import sys
import json
import re
import datetime
import paramiko
from ftplib import FTP

name = 'b_stankulov'

json_path = r'\\192.168.121.112\bobi-test' # must be mapped as network drive

'''
S0 : After we received a mail, which is handled by an event listener which uses the current script's functions.
     The very first thing this script does is it reads the Subject and the Body of the mail, and since there is a problem
     with the excel file and bunch of libraries, such as openpyxl, xlrd(i have tried converting the .xlsx to .xls) and so on,
     won't read the color background of the cells,so through this scrip just accept the hotfixes, defined in the mail body. Thus, the script
     makes some additional verifications: Does the version of the RN increments with one from the previous; Does the hotfix id
     increment with one from the previous of the current build (E.g. Build 08.50 has prev hf with id 011, so this one must have 012) and
     if it is first one, is the id 001? (We check this through connecting to the shared machine, which contains a .json file, where we store
     all the previously accepted releases and compare them to the ones in the mail body); Lastly, it compares the ID-s in the excel and the ones
     in the mail body. If there are changes on top of old hf, described in the body, sry bro but handle that manually :( . Also after there is
     match in the defect id between the mail body and the RN manually check it in QC, SharePoint or Remedy if WO, REQ, etc.
'''

def rollback(hotfix_details):
    print("rollback")

def read_mail(mail):
    all_matches = re.findall('(BPT(\d{4}\.\d{2}\.\d{2})-HF(\d{3}))', mail.Subject)
    return all_matches

def write_json_hf(data):
    with open('log.json', 'w') as json_file:
        json.dump(data, json_file, indent=4) #допълнителните опции са за 'разкрасяването' на json файла

#it also checks if the hf hasnt already been taken
def read_json_hf(build_id, hf_id):
    print('Connecting to Version/HF Database...') #build id is for eg 08.50, hf_id is the id for each hotfixb given in the mail
    os.chdir(json_path)
    data = ''
    num_id = int(hf_id)

    with open('log.json', 'r') as json_file:
        data = json.load(json_file)

        if build_id not in data:
            print('Build does not exists!')
            return False

        prev_hf = int(data[build_id][-1]["id"]) #we take the last id in the current build

        if prev_hf + 1 != num_id:
            print('HF id is not incremented right! Somebody may have already taken it or there is discrepancy in the mail!')
            return False
        else:
            print('all good')
            data[build_id].append({'id': hf_id, 'author': name, 'date_acceptance': 1})
            write_json_hf(data)
            return True

#we will check if there is a xlsx file in the bpt_upload (RN) which version increments the last one
def get_prev_RNversion():
     print('Retrieving last RN version...')
     os.chdir(json_path)

     with open('versionsBPT.json', 'r') as jsonFile:
        data = json.load(jsonFile)
        prev_version = int(data[-1]) #we take the last RN verson
        return prev_version

'''
S1 : in the end BPT files with xlxs extension have
     current date in this particular format -
     {current year of delivery}{current month of delivery}{current day of delivery}
     with padding zeros on the left, so we format time now it to check it later when retrieving files
'''
def get_date():
    now = datetime.datetime.now()
    current_year = str(now.year)
    current_month = str(now.month).zfill(2)
    current_day = str(now.day).zfill(2)
    date_format = current_year + current_month + current_day
    return date_format

'''
#S2 : we connect to the ftp using ip address as login() method can contain two params: user and pass
'''
def connect_bpt_upload():
    prev_version = get_prev_RNversion() + 1
    date_format = get_date()
    os.chdir(r"C:\BPTWorkingFolder")
    username = "bptupload"
    password = "BPTMast@r"
    ip="192.168.121.6"
    directory = "/BPT_UPLOAD" #the directory, where hotfixes+RN are uploaded, sometimes RN are inside of the HF-s, we shouldnt put the RN on the install foler
    tp = FTP(ip)
    tp.login(username,password) #you can login without passing params, but you won't be able to delete the RN or move them if they are inside the release folder
    tp.cwd(directory)
    xlsx_files = tp.nlst('*xlsx')

    if len(xlsx_files) == 0:
        print('The RN may be inside the hotfix directory. Please check it!')
        return False

    #iterate over all files with .xlsx extension in BPT_UPLOAD
    for filename in xlsx_files:
        mk = re.match(r'(A1\sBPT_Releases_V'+ re.escape(str(prev_version)) + r'_%s.xlsx)' % date_format, filename)
        if mk:
            fhandle = open(filename, 'wb')
            print('Retrieving ' + filename + '...')
            tp.retrbinary('RETR ' + filename, fhandle.write)
            '''
            if ФАЙЛЪТ Е ВЪВ ТЕКУЩА ПАПКА:
                tp.delete(filename)
            '''
            fhandle.close()
            return (filename, prev_version)

#proverka za golemina na sql scripta - stat --printf="%s\n" *.sql // (/1048576) za MB





import os
import openpyxl

def read_xlsx_file(file, hotfix_details):
    os.chdir(r"C:\BPTWorkingFolder")
    wb = openpyxl.load_workbook(file, read_only=True)
    sheets = wb.sheetnames
    versions = wb[sheets[1]]
    bpt = wb[sheets[2]]
    max_row=bpt.max_row #last changed row and last changed column in the excel sheet
    addedBPTs = []
    while bpt.cell(row = max_row, column = 2).fill.start_color.index == "FFFF0000":
        #проверка дали фонът е червен, green - 92d050; red - ff0000 по hexadecimal
        strRow = str(max_row)
        bptId = bpt["B"+ strRow].value

        if bptId == bpt["M"+ strRow].value and bptId in bpt["L"+ strRow].value:
            addedBPTs.append({
                "name" : bptId,
                "apl" : bpt["D"+ strRow].value,
                "fixType" : bpt["E"+ strRow].value,
                "idDefect" : bpt["H"+ strRow].value,
                "description" : bpt["I"+ strRow].value,
                "notes" : bpt["L"+ strRow].value,
                "location" : bpt["M"+ strRow].value #"/install/BPT/BPT1003.11.00/HF/" + bptId
             })
        else:
            print("none") #трябва да хвърли подходяща грешка
        max_row-=1
    #print(addedBPTs)

    versions_max_row = str(versions.max_row)
    bpt_versions = versions["I" + versions_max_row].value #онази втората таблица
    #print(bpt_versions)
    bpt_versions = bpt_versions.replace('\n', "<br />") #за html-а, който пращаме по мейла
    return (bpt_versions, addedBPTs)


from yattag import Doc

def generate_html_file(data, builds, version):
   print(builds)
   print(version)
   doc, tag, text = Doc().tagtext()

   style = '''<html><style>
      div { padding-left: 50px; }
      table, th, td { border-collapse: collapse; }
      td, th { padding-top:10px;padding-bottom:10px;text-align: center;border:1px solid black;}
      tr td:last-child { width:1%; white-space:nowrap; }</style><body>
      <p>Dear All,</p><p>Please be informed that the BPT team has sent new Release Notes (<span><strong>v.'''+ str(version) +'''</strong></span>), and the following deliverables have been accepted by the RBD team:</p>
      <div>
      <p style="font-size:110%;">
      <b>New HFs on Top of build '''+ builds[0] +''':</b>
      </p>'''

   with tag('table', style = "width: 100%;"):
      with tag('tr', ):
         with tag('th'):
            text('HF ID')
         with tag('th'):
            text('Appl.')
         with tag('th'):
            text('Fix Type')
         with tag('th'):
            text('ID / Defect')
         with tag('th'):
            text('Description')
         with tag('th'):
            text('Notes')
         with tag('th'):
            text('Location')
      for hotfix in reversed(data[1]):
         with tag('tr'):
            with tag('td'):
               text(hotfix['name'])
            with tag('td'):
               text(hotfix['apl'])
            with tag('td'):
               text(hotfix['fixType'])
            with tag('td'):
               text(hotfix['idDefect'])
            with tag('td'):
               text(hotfix['description'])
            with tag('td'):
               text(hotfix['notes'])
            with tag('td'):
               text(hotfix['location'])
   doc.stag('br')
   with tag('p', style = "white-space: pre-line"):
      text('\n')
      doc.asis(data[0])



   table = str(doc.getvalue())
   return style + table + "\n</body></html>"

#print(generate_html_file(readExcel.read_xlsx_file("A1 BPT_Releases_V4197_20181213.xlsx",1)))