def dbTemp(WithdrawDeposit, Company, Date, Amount):
print("dbTemp works!")
query = (
"INSERT INTO tTemporary (WithdrawDeposit, Company, Date, Amount) VALUES (%s, %s, %s, %s)"
)
data = (WithdrawDeposit, Company, Date, Amount)
cur = Tlbx.dbConnect()
try:
cur.execute(query, data)
except:
print("PNC Database insert Failed! Sending email...")
body = "IMAP failed. Subject was " + WithdrawDeposit + " Company was " + Company + " Date was " + Date + " Amount was " + str(
Amount)
Tlbx.SendEmail(body)
def dbTrans(Name, Description, WithdrawDeposit, Company, Date, Amount,
Comments, Receipt):
print("dbTrans works!")
cur = Tlbx.dbConnect()
query = "INSERT INTO tTransaction (Name, Description, WithdrawDeposit, Company, Date, Amount, Comments, Receipt) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
print(Date)
data = (Name, Description, WithdrawDeposit, Company, Date, Amount,
Comments, Receipt)
try:
cur.execute(query, data)
except:
body = "IMAP failed. Name was %s, Description was %s, Subject was %s, Company was %s, Date was %s Amount was %s Comments was %s Receipt was %s", (
Name, Description, WithdrawDeposit, Company, Date, Amount,
Comments, Receipt)
Tlbx.SendEmail(body)
def IMAP():
#Global variable to use inside of the class above
global body
#Connect to gmails imap service
try:
mail = imaplib.IMAP4_SSL('imap.gmail.com')
mail.login('*****@*****.**', 'Momrocks38')
mail.select('inbox')
mail.list()
except:
print("Could not connect to Gmail!")
body = "IMAP failed. Could not connect to Gmail."
Tlbx.SendEmail(body)
#this will search the email and give me all unread emails.
response = mail.search(None, '(UNSEEN)')
response = response[0].split()
#For each email we will parse out the data and commit to the database
for e_id in response:
#reset variables for each loop
body = []
raw_email = ''
#Fetch one email and decode as utf-8
_, response = mail.fetch(e_id, 'RFC822')
try:
raw_email = response[0][1].decode("utf-8")
except:
print("Email cannot be decoded")
continue
email_message = email.message_from_string(raw_email)
#these pull each heading and place into variables
from_ = email_message['From']
subject_ = str(email_message['Subject'])
date_ = str(email_message['date'])
#set up array for date
date = []
try:
date = date_.split()
Day = (date[0])
Month = (date[1])
Year = (date[2])
Date = Tlbx.dateConvert(Day, Month, Year)
except:
print("Date Failed!")
body = "IMAP Date parsing failed. %s was the date pulled in" % date_
Tlbx.SendEmail(body)
#If this data from pncalerts, we run it through this parsing group
if (from_ == "PNC Alerts <*****@*****.**>"):
#An alert for using the debit card
if ("ATM" in subject_):
print("There should be no ATM usage!")
body = "Someone pulled money out of the ATM using Andrew's card."
Tlbx.SendEmail(body)
continue
#Compares account balance to the current database sum
elif ("Your Checking Account Balance" in subject_):
parser = MyCheckParser()
parser.feed(str(email_message))
PNCBalance = body[0]
PNCBalance = PNCBalance.split(":")
PNCBalance = PNCBalance[1]
PNCBalance = PNCBalance.replace("$", "")
PNCBalance = PNCBalance.replace(",", "")
PNCBalance = PNCBalance.strip()
PNCBalance = float(PNCBalance)
db = pymysql.connect(host='192.168.0.20',
port=3306,
user='******',
password='******',
db='mydb',
autocommit=True)
cur = db.cursor()
query = ("Select SUM(Amount) FROM tTemporary")
cur.execute(query)
TempAmount = cur.fetchone()[0]
query = ("Select SUM(Amount) FROM tTransaction")
cur.execute(query)
TransAmount = cur.fetchone()[0]
try:
balance = TransAmount + TempAmount
except:
balance = TransAmount
if balance is not None:
balance = ('%.2f' % balance)
balance = float(balance)
if (balance != PNCBalance):
PNCBalance = str(PNCBalance)
balance = str(balance)
print("Balances do not match!")
body = "PNC and app balances do not match. PNC balance is " + PNCBalance + " and app balance is " + balance + "."
Tlbx.SendEmail(body)
#If a check is used
elif ("Check(s)" in subject_):
parser = MyCheckParser()
parser.feed(str(email_message))
if ("Deducted" in subject_):
WithdrawDeposit = "Withdraw"
else:
WithdrawDeposit = "Deposit"
Company = 'Check'
Amount = body[2]
Amount = Amount.strip()
Amount = Amount.split(":")
Amount = Amount[1]
Amount = Amount.replace("$", "")
Amount = Amount.strip()
Day = date[1]
Month = date[2]
Year = date[3]
#Fix date to Y/M/D
Date = Tlbx.dateConvert(Day, Month, Year)
#Function to connect to database and commit data to tTemporary
dbTemp(WithdrawDeposit, Company, Date, Amount)
else:
#This sets up our html parser which is the class above and feeds it in.
parser = MyHTMLParser()
parser.feed(str(email_message))
Company = ''
Amount = ''
#The global variable body we parse down for the company and amount
try:
Company = body[1]
Company = Company.strip('\t')
Company = Company.strip('\n')
Company = Company.strip()
#pull how much the amount was for and strip excess
Amount = body[2]
Amount = Amount.strip('\t')
Amount = Amount.strip('\n')
Amount = Amount.strip()
Amount = re.findall(r"\d+\.\d+", Amount)
#Send e-mail if parsing fails
except:
print("Company and Amount parsing Failed!")
body = "IMAP Company/Amount parsing failed. %s was the Company pulled in, and %s was the the Amount pulled in" % (
Company, Amount)
Tlbx.SendEmail(body)
continue
#try to convert Amount for database commit
try:
Amount = Amount[0]
Amount = float(Amount)
except:
print("no money I guess")
#Date comes in differently, parse again. Could be avoided with external function
if ('Payment' in subject_ or 'Withdraw' in subject_):
subject_ = 'Withdraw'
Day = date[1]
Month = date[2]
Year = date[3]
Date = Tlbx.dateConvert(Day, Month, Year)
#Fix to not parse e-mail with no data
elif ('Available Balance' in subject_):
continue
#Fix to not parse e-mail with no data
elif ('Online Banking alerts' in subject_):
continue
else:
subject_ = 'Deposit'
Day = date[1]
Month = date[2]
Year = date[3]
#Converts the date to Y/M/D
Date = Tlbx.dateConvert(Day, Month, Year)
# switch the Amount to negative if it is a withdraw
if ('Withdraw' in subject_):
Amount = -Amount
#Strip excess data
Company = Company.replace('Payment', '')
Company = Company.replace('From:', '')
Company = Company.replace('1', '')
Company = Company.replace('To:', '')
Company = Company.strip()
#If we fit this logic, we will correct the naming and automatically commit to tTransaction
if ("DUKE ENERGY OH" in Company
or "RUMPKE RESIDENTI" in Company
or "CLERMONT COUNTY" in Company
or "SSI TREAS 30 9" in Company
or "US BANK HOME MTG" in Company):
Name = ''
Description = ''
Comments = ''
Receipt = ''
if ("CLERMONT COUNTY" in Company):
Company = "Clermont County Utilities"
Description = "Monthly Water Bill Payment"
Name = "Clermont County Water"
if ("RUMPKE RESIDENTI" in Company):
Company = "Rumpke"
Description = "Monthly Payment for Waste Disposal"
Name = "Trash Removal"
if ("DUKE ENERGY OH" in Company):
Company = "Duke Energy"
Description = "Monthly Electric Bill"
Name = "Duke Energy"
if ("SSI TREAS 30 9" in Company):
Company = "SSI"
Description = "Monthly Social Security Deposit"
Name = "SSI Check Deposit"
if ("US BANK HOME MTG" in Company):
Company = "US Bank"
Description = "Monthly House Payment"
Name = "Housing"
#Function to connect to our database and insert into tTransaction
dbTrans(Name, Description, subject_, Company, Date, Amount,
Comments, Receipt)
#commit data to our tTemporary table
else:
#Function to connect to our database and insert into tTemporary
dbTemp(subject_, Company, Date, Amount)
#If the email is from [email protected] it will run through here
elif (from_ == '*****@*****.**'):
#Pull email and decode because it is in base64
ascil = email_message.get_payload(decode=base64).decode("utf-8")
#Here we split the array and place them into variables. From there we will strip them down.
array = (ascil.split("\n", 1))
startType = 'Your'
endType = 'specified'
start = 'Amount:'
end = 'USD'
Start1 = 'Name:'
end1 = 'Merchant'
s = array[1]
WithdrawDeposit = ((s.split(startType))[1].split(endType)[0])
if ("purchase" in WithdrawDeposit):
WithdrawDeposit = "Withdraw"
if ("deposit" in WithdrawDeposit):
WithdrawDeposit = "Deposit"
if ("recently used" in WithdrawDeposit):
WithdrawDeposit = "Withdraw"
#Setup our amount to commit to the database
Amount = ''
#Attempt to strip down the Amount and convert to a float.
try:
Amount = ((s.split(start))[1].split(end)[0])
Amount = Amount.strip('\t')
Amount = Amount.strip('\n')
Amount = Amount.strip()
Amount = Amount.replace('\'', '')
Amount = Amount.replace("'", '')
Amount = float(Amount)
#Set amount to negative if this transaction is a withdraw
if (WithdrawDeposit == "Withdraw"):
Amount = Amount * -1
#A catch in case the amount does not exist in the e-mail
except:
print("No Amount I Guess")
#remove excess data in company to commit into the database
Company = ((s.split(Start1))[1].split(end1)[0])
Company = Company.strip('\t')
Company = Company.strip('\n')
Company = Company.strip()
#if the scraped data fits in this logic, commit directly to tTransaction
if ("ORDER" in Company or "BALANC" in Company or "TIP" in Company
or "Northshore Care Supply" in Company):
Name = ''
Description = ''
Comments = ''
Receipt = ''
if ("ORDER" in Company):
Company = "Shipt"
Name = "Groceries"
Description = "Monthly Groceries"
if ("BALANC" in Company):
Company = "Shipt"
Name = "Groceries"
Description = "Balance differential for adding an item"
if ("TIP" in Company):
Company = "Shipt"
Name = "Groceries"
Description = "Added tip for Delivery"
if ("Northshore Care Supply" in Company):
Company = "Northshore Care Supply"
Name = "Diapers"
Description = "Tranquility All Night Through Adult Disposable Briefs"
#Function call to instantiate database connection and insert scraped data into the tTransaction table.
dbTrans(Name, Description, WithdrawDeposit, Company, Date,
Amount, Comments, Receipt)
#commit to tTemporary table
else:
#Parse company names to reduce later work when completing the transaction
if ("CW BOTANICALS" in Company or "MEIJER" in Company
or "FIVE BELOW" in Company or "LOWE'S" in Company
or "KROGER" in Company or "MCDONALD'S" in Company
or "WALGREENS" in Company
or "AMZN MKTP US AMZN.COM/" in Company
or "AMAZON" in Company or "AMZN Mktp" in Company
or "Amazon" in Company or "amazon" in Company
or "THORNTONS" in Company or "KFC" in Company
or "TARGET" in Company):
if ("CW BOTANICALS" in Company):
Company = "CW Botanicals"
if ("MEIJER" in Company):
Company = "Meijer"
if ("FIVE BELOW" in Company):
Company = "Five Below"
if ("LOWE'S" in Company):
Company = "Lowe's"
if ("KROGER" in Company):
Company = "Kroger"
if ("WALGREENS" in Company):
Company = "Walgreens"
if ("AMAZON" in Company
or "AMZN MKTP US AMZN.COM/" in Company
or "Amazon" in Company or "amazon" in Company):
Company = "Amazon"
if ("THORNTONS" in Company):
Company = "Thorntons"
if ("KFC" in Company):
Company = "KFC"
if ("TARGET" in Company):
Company = "Target"
#Function call to instantiate database connection and insert scraped data into the tTemporary table
dbTemp(WithdrawDeposit, Company, Date, Amount)
