Reading Email

#!/usr/bin/python
import sys
import re
import os
import win32com.client
import win32api
import win32con
import datetime as dt
import cx_Oracle
from selenium import webdriver
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import TimeoutException
from selenium.webdriver.common.by import By

outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")

inbox = outlook.GetDefaultFolder(6) # "6" refers to the index of a folder - in this case,
                                    # the inbox. You can change that number to reference
                                    # any other folder
messages = inbox.Items
message = messages.GetLast()
body_content = message.body
subject_content = message.subject

print (subject_content)
print (body_content)
paymentmsg = subject_content + body_content

#Example of email to scrape:

From: Lai, Jeffrey (DK - Copenhagen)
Sent: 21. juni 2017 16:13
To: Lai, Jeffrey (DK - Copenhagen)
Subject: indbetaling
Sent: 21. juni 2017 16:13To: Lai, Jeffrey (DK - Copenhagen) Subject: indbetaling Hej Jeffrey,
 Jeg vil gerne bed om en indbetalinger. Jeg arbejder på Test01.
  

(identifikations nr.)	Beløb	Dato for fordring*	Filtype
581623450085428	500	15.01.2017	Cremul

 Med Venlig Hilsen
Jeffrey Lai

#Clean the message for easier identifying elements
paymentmsg = paymentmsg.lower()
#date
paymentmsg = paymentmsg.replace('. januar ', '.01.')
paymentmsg = paymentmsg.replace('. februar ', '.02.')
paymentmsg = paymentmsg.replace('. marts ', '.03.')
paymentmsg = paymentmsg.replace('. april ', '.04.')
paymentmsg = paymentmsg.replace('. maj ', '.05.')
paymentmsg = paymentmsg.replace('. juni ', '.06.')
paymentmsg = paymentmsg.replace('. juli ', '.07.')
paymentmsg = paymentmsg.replace('. august ', '.08.')
paymentmsg = paymentmsg.replace('. september ', '.09.')
paymentmsg = paymentmsg.replace('. oktober ', '.10.')
paymentmsg = paymentmsg.replace('. november ', '.11.')
paymentmsg = paymentmsg.replace('. december ', '.12.')

paymentmsg = paymentmsg.replace('januar ', '01.')
paymentmsg = paymentmsg.replace('februar ', '02.')
paymentmsg = paymentmsg.replace('marts ', '03.')
paymentmsg = paymentmsg.replace('april ', '04.')
paymentmsg = paymentmsg.replace('maj ', '05.')
paymentmsg = paymentmsg.replace('juni ', '06.')
paymentmsg = paymentmsg.replace('juli ', '07.')
paymentmsg = paymentmsg.replace('august ', '08.')
paymentmsg = paymentmsg.replace('september ', '09.')
paymentmsg = paymentmsg.replace('oktober ', '10.')
paymentmsg = paymentmsg.replace('november ', '11.')
paymentmsg = paymentmsg.replace('december ', '12.')
#Clean the message for easier identifying elements
#paymentmsg = paymentmsg.replace(" ", "")

print (paymentmsg)

#This is what it could look like after the cleaning
#paymentmsg = """
fw:indbetaling
from:lai,jeffrey()
sent:21.06.201716:13
to:lai,jeffrey(dk-copenhagen)<>
subject:indbetaling
hejjeffrey,
jegvilgernebedomenindbetalinger.
jegarbejderpåtest01.
(identifikationsnr.)
beløb
datoforfordring*
filtype
581623450085428
500
15.01.2017
cremul
medvenlighilsen
jeffreylai"""

#In the email part we are scraping for the following parameters:
# Payment amount : e.g. 1000 kr
# Date: 22-05-17
# TestEnvironment : Test02
# OCR line : 123456789012345
# Special keywords to identify payment type. In case of "Nets", then use another macro + batchjob
# Special keywords to identify if OCR is needed ."Intet" if that keywords appear, dont use a OCR.

 #format 10000,11kr
#does not support 1.000.000,22 format max 99.999,99
amount = re.search("(\d+[,\.]\d+[,\.]\d+)\s?kr", paymentmsg)
if amount:
    print (amount.groups()[0])
    print ("A")
    #Store Beløb
    amountStr = (amount.groups()[0])
else:
    #format 10000,11kr #format 10.000kr
    amount0 = re.search("(\d+[,\.]\d+)\s?kr", paymentmsg)
    if amount0:
        print (amount0.groups()[0])
        print ("B")
        #Store Beløb
        amountStr = (amount0.groups()[0])
    else:
        #1000kr
        amount2 = re.search("(\d+)\s?kr", paymentmsg)
        if amount2:
            print (amount2.groups()[0])
            print ("C")
            #Store Beløb
            amountStr = (amount2.groups()[0])
        else:
            #any amount if 2 to 4 digits in its own line
            amount3 = re.search("\s(\d{2,4})\s", paymentmsg)
            if amount3:
                print (amount3.groups()[0])
                #Store Beløb
                amountStr = (amount3.groups()[0])
     
#OCR linie  
OCR = re.search("\\+71<(\d+)", paymentmsg)
if OCR:
    print (OCR.groups()[0])
    #Store OCR
    ocrStr = (OCR.groups()[0])
else:
    #Identify 15 digit number in a string, assuming it's an OCR
    OCR2 = re.search("\D(\d{15})\D", paymentmsg)
    if OCR2:
        print (OCR2.groups()[0])
        #Store OCR
        ocrStr = (OCR2.groups()[0])
     
#Accepts format dd-mm-yyyy, dd/mm/yyyy , dd.mm.yyyy  , dd-mm-yy, dd/mm/yy , dd.mm.yy, d/m/yy
#Accepts dateformat with 1 digits to two digits for dd and mm, and between 2 and 4 digits for year
paymentdate = re.search("(dato?\s)?(\d{1,2}[-\.,]\d{1,2}[-\.,]\d{2,4})", paymentmsg)
if paymentdate:
    print ("Dato: " + paymentdate.groups()[1])
    #Store Dato
    paymentdateStr = (paymentdate.groups()[1])
else:
    paymentdate2 = re.search("(d.d)", paymentmsg)
    if paymentdate2:
        print (paymentdate2.groups()[0])
        #Store Dato with today's date
        paymentdateStr = dt.datetime.today().strftime("%d-%m-%Y")  

#Identify environment
env = re.search("(val\d+)", paymentmsg)
if env:
    print (env.groups()[0])
    #Store
    envStr = (env.groups()[0])
else:
    if env:
        env = re.search("(test\d+)", paymentmsg)
        print (env.groups()[0])
        #Store Miljø
        envStr = (env.groups()[0])
     
#Identify ventekonoto eller keyword Ingen OCR linie
Ingen = re.search("(Ingen)", paymentmsg)
VenteKonto = re.search("(ventekonto)", paymentmsg)
if Ingen or VenteKonto:
    print (Ingen.groups()[0])
    print (VenteKonto.groups()[0])
    #if ingen ocr, consider action required from user to validate before posting
    #os.system("The keyword 'Ingen' has been identified, please review the mail")

    result = win32api.MessageBox(None,'Should the following payment have incorrect OCR?','The keyword "Ingen" has been identified, please review the mail',win32con.MB_YESNO)
    if result == win32con.IDYES:
        print('Yes')
        #Set incorrect ocr line
        ocrStr = "999998888877777"
    else:
        print('No')
        result2 = win32api.MessageBox(None,'Continue payment by rewriting OCR Line?','The keyword "Ingen" has been identified, please review the mail',win32con.MB_YESNO)
        if result2 == win32con.IDYES:
            print('Yes2')
            #ask user to input ocr in shell
            ocrStr = input('Please write the new OCR line: ')
        else:
            print('Script has been stopped')
            #Exit script
            sys.exit()
         
netsPayment = re.search("(nets)", paymentmsg)
if netsPayment:
    print (netsPayment.groups()[0])
    #Store Beløb
    netsPaymentStr = (netsPayment.groups()[0])

path = "C:\\Betalingsfil Generator V4.xlsm"

if os.path.exists(path):
    xl=win32com.client.Dispatch("Excel.Application")
    #xl.Workbooks.Open(Filename=path, ReadOnly=1)
    xl.Workbooks.Open(Filename=path)
    ws = xl.Worksheets("MENU")
    ws.Range("B3").Value = amountStr
    ws.Range("C3").Value = paymentdateStr.replace('.','-')
    ws.Range("D3").Value = "'" + ocrStr
    #Run macro

    if netsPayment == "nets":
        #nets payment
        ws.Range("E13").value = "NETS"
        ws.Range("E15").value = "Betalingsservice"
        xl.Application.Run("'Betalingsfil Generator V4.xlsm'!GogoM602")
    else:
        #cremul
        xl.Application.Run("'Betalingsfil Generator V4.xlsm'!Module3.GogoCREMUL")


    #sqlCode = ws.Range("G1").value
    sqlCode = ""
    #identifying last used row
    lastRow = ws.UsedRange.Rows.Count
 
    col = ws.Range("G1:G" + str(lastRow))
    #Loop through each cell in sql statement
    for c in col:
        #cell.Offset(1,2).Value = "W0000%s" % cell.Value
        #Do not include empty cells #and do not include the first sqlblankline code, not needed and cannot execute if not on it's own line
        if str(c.value) != "SET SQLBLANKLINES ON;":
            if str(c.value) != "COMMIT;": #We commit at the end of the connection string
                if str(c.value) != "None":
                    sqlCode = sqlCode + str(c.Value) + ""

    sqlCode = sqlCode.replace(';', '') #It cannot handle ; for some reason

    print (sqlCode)
  #  xl.ActiveWorkbook.Save() # if you want to save then uncomment this line and change delete the ", ReadOnly=1" part from the open function.
 #   xl.Quit() # Comment this out if your excel script closes
    del xl

env = envStr

if env == "test01" or env == "test1":
    ip = 'localhost1'
    port = 0001
    SID = 'SID'
    dsn_tns = cx_Oracle.makedsn(ip, port, SID)
    username = 'USERNAME'
    password= 'PASSWORD'
    BawSite = "https://localhost/batchJobs"
    bawServer = "https://localhost/"
elif env == "test02" or env == "test2":
    ip = 'localhost2'
    port = 0001
    SID = 'SID'
    dsn_tns = cx_Oracle.makedsn(ip, port, SID)
    username = 'USERNAME'
    password= 'PASSWORD'
    BawSite = "https://localhost/batchJobs"
    bawServer = "https://localhost2"

#db = cx_Oracle.connect(username, password, dsn_tns)
#This can also be done maually
# You can set these in system variables but just in case you didnt
os.putenv('ORACLE_HOME', 'C:\Python\instantclient_11_2')
#os.putenv('LD_LIBRARY_PATH', '/oracle/product/10.2.0/db_1/lib')

connection = cx_Oracle.connect(username, password, dsn_tns)
cursor = connection.cursor()
querystring = sqlCode #"select * from Integration_File"
cursor.execute(querystring)
connection.commit()

#Only works for select statements, do not include in update and insert
#for row in cursor.fetchall():
#    print (row)
 
#Running batchjobs in baw
chromedriver = "C:\Python\chromedriver"
os.environ["webdriver.chrome.driver"] = chromedriver
#if browser == 'chrome':
options = webdriver.ChromeOptions()

#options.add_argument("--start-maximized")
options.add_argument('--ignore-certificate-errors')
prefs = {
        "profile.default_content_settings.popups": 0,
        "download.default_directory": r"C:\Users\user_dir\Desktop\\", # IMPORTANT - ENDING SLASH V IMPORTANT
        "directory_upgrade": True,
        "download.prompt_for_download": False
        }
options.add_experimental_option("prefs", prefs)
driver = webdriver.Chrome(executable_path=chromedriver, chrome_options=options)
delay = 3 # seconds
#Batchjob ACC settings
username = "USERNAME"
password = "PASSWORD"

#Baw batchjob summary settings - will be used later
StartTime = dt.datetime.now().strftime('%Y-%m-%d' + 'T')
StartHour = dt.datetime.now().strftime('%H')
StartMin = dt.datetime.now().strftime('%M')
StartSec = dt.datetime.now().strftime('%S')

if netsPayment == "nets":
    #nets payment
    driver.get('https://' + username + ':' + password + '@' + BawSite.replace('https://',''))
    driver.find_element_by_xpath("//td[text()='DKNP']/following-sibling::td//button[text()='Execute']").click()
    print ("Batch job: DKNP")
else:
    #cremul payment skb
    driver.get('https://' + username + ':' + password + '@' + BawSite.replace('https://',''))
    driver.find_element_by_xpath("//td[text()='DKSKBP']/following-sibling::td//button[text()='Execute']").click()
    print ("Batch job: DKSKBP")
#PEPLS Batchjobs
time.sleep(5) # delays for 5 seconds
driver.get('https://' + username + ':' + password + '@' + BawSite.replace('https://',''))
driver.find_element_by_xpath("//td[text()='C1-PEPL1']/following-sibling::td//button[text()='Execute']").click()
print ("Batch job: PEPL1")
time.sleep(5) # delays for 5 seconds
driver.get('https://' + username + ':' + password + '@' + BawSite.replace('https://',''))
driver.find_element_by_xpath("//td[text()='C1-PEPL2']/following-sibling::td//button[text()='Execute']").click()
print ("Batch job: PEPL2")

#Get summary status on baw
EndMin = dt.datetime.now().strftime('%M')
driver.get(bawServer + "/batch-administration-website/executedBatchJobs?start=" + StartTime + StartHour +"%3A" +StartMin + "%3A" + StartSec + "%2B02%3A00&end=" + StartTime + StartHour +"%3A" + EndMin + "%3A" + StartSec + "%2B02%3A00")

try:
    #Wait to load the dropdown as an indication of the page is "fully loaded". The dropsdown html id = datetimepicker1
    myElem = WebDriverWait(driver, delay).until(EC.presence_of_element_located((By.ID, 'datetimepicker1')))
    #element_present = EC.presence_of_element_located((By.ID, 'datetimepicker1'))
    #WebDriverWait(driver, timeout).until(element_present)
    print ("Page is ready!")
except TimeoutException:
    print ("Loading took too much time!")
#Exit the Selenium driver
driver.quit()