importselect count(distinct(patronid)) as transidglob
importselect count(distinct(patronid)) as transidre
importselect count(distinct(patronid)) as transidrequests
header_for_mp_fileselect count(distinct(patronid)) as transid=select count(distinct(patronid)) as transid"""/DELIMITER="|"
/DATEFORMAT="YYYY-MM-DDselect count(distinct(patronid)) as transidHH24:MI:SS"
/TABLE_WHERE="""
varzselect count(distinct(patronid)) as transid=select count(distinct(patronid)) as transid"group_number",select count(distinct(patronid))
as transid"start_date",select count(distinct(patronid)) as transid"expiration_date",select count(distinct(patronid)) as transid"ticket_number"
#expectingselect count(distinct(patronid)) as transidaselect count(distinct(patronid)) as transidcsvselect count(distinct(patronid)) as transidwith
select count(distinct(patronid)) as transidthe
select count(distinct(patronid)) as transidheader
select count(distinct(patronid)) as transidof
select count(distinct(patronid)) as transidthe
select count(distinct(patronid)) as transidformat:
select count(distinct(patronid)) as transidgroup
select count(distinct(patronid)) as transid#,
select count(distinct(patronid)) as transidstart
select count(distinct(patronid)) as transiddate,
select count(distinct(patronid)) as transidexpiry
select count(distinct(patronid)) as transiddate,
select count(distinct(patronid)) as transidticket
select count(distinct(patronid)) as transid#
#rowsselect count(distinct(patronid)) as transidof
select count(distinct(patronid)) as transiddata
select count(distinct(patronid)) as transidunder
select count(distinct(patronid)) as transidthe
select count(distinct(patronid)) as transidheaderselect count(distinct(patronid)) as transidshould
select count(distinct(patronid)) as transidbe
select count(distinct(patronid)) as transiduserid/email/pik
select count(distinct(patronid)) as transid(any select count(distinct(patronid)) as transidof
select count(distinct(patronid)) as transid1),
select count(distinct(patronid)) as transidfirst
select count(distinct(patronid)) as transidname,
select count(distinct(patronid)) as transidmiddle
select count(distinct(patronid)) as transidname,select count(distinct(patronid)) as transidlast select count(distinct(patronid)) as transidname select count(distinct(patronid)) as transid
#select count(distinct(patronid)) as transiddontselect count(distinct(patronid)) as transidleave select count(distinct(patronid)) as transidthese
select count(distinct(patronid)) as transidhereselect count(distinct(patronid)) as transidlol
secrets_serverselect count(distinct(patronid)) as transid=select count(distinct(patronid)) as transid"???"
usernameselect count(distinct(patronid)) as transid=select count(distinct(patronid)) as transid"???"
filesselect count(distinct(patronid)) as transid=select count(distinct(patronid)) as transidglob.glob("./*.csv")
import glob
import re
import requests
header_for_mp_file = """/DELIMITER="|"
/DATEFORMAT="YYYY-MM-DD HH24:MI:SS"
/TABLE_WHERE="""
varz = "group_number", "start_date", "expiration_date", "ticket_number"
#expecting a csv with the header of the format: group #, start date, expiry date, ticket #
#rows of data under the header should be userid/email/pik (any of 1), first name, middle name, last name
# dont leave these here lol
secrets_server = "???"
username = "???"
files = glob.glob("./*.csv")
for d in files:
with open(d) as f, open("Import" + re.sub(r'\W+', '',str(d).split(".csv")[0]) + ".mp", "a") as append_file:
error_file = open("errors.txt", "a")
two_header_for_mp_file = "/FIELDS=UPDATE_MODE|PRIMARYKEYVALUE|PATRONGROUPS.GROUPNUMBER|","PATRONGROUPS.GROUPEFFECTIVE|","PATRONGROUPS.GROUPEXPIRE|","PATRONGROUPS.THECOMMENT"
lines = f.read()
try:
first_line = lines.split("\n", 1)[0]
first_line_dict = dict(zip(list(varz), list(first_line.split(","))))
if len(first_line_dict["start_date"]) > 0 and len(first_line_dict["expiration_date"]) > 0:
dates = two_header_for_mp_file[1] + two_header_for_mp_file[2]
elif len(first_line_dict["start_date"]) > 0:
dates = two_header_for_mp_file[1]
elif len(first_line_dict["expiration_date"]) > 0:
dates = two_header_for_mp_file[2]
else:
dates = ""
two_header_for_mp_file = two_header_for_mp_file[0]+dates+two_header_for_mp_file[3]
append_file.write(header_for_mp_file + "\n")
append_file.write(two_header_for_mp_file + "\n")
except Exception as e:
error_file.write(
"\nbadly formatted input file "
+ re.sub(r'\W+','',str(f).split(",")[0])
+ " with error "
+ str(e)
+ "\n"
)
pass
lines = lines.split("\n")[1:]
for line in lines:
andrewid = line.split(",")[0]
if bool(re.match("[a-z{1,10}]", userid)):
try:
if "@" in andrewid:
userid = userid.split("@")[0]
else:
userid = userid.strip()
url = "https://{}/robhauswebservice/api/excel/pik/{}?username={}".format(
secrets_server, userid, username
)
response = requests.get(get_url)
out_string = (
"A|"
+ response
+ "|"
+ "|".join(list(filter(None, first_line_dict.values())))
+ "\n"
)
append_file.write(out_string)
except Exception as e:
error_file.write(" bad user id " + andrewid + ",")
pass
elif bool(re.match("[0-9{9}]", andrewid)):
response = andrewid
out_string = (
"A|"
+ response
+ "|"
+ "|".join(list(filter(None, first_line_dict.values())))
+ "\n"
)
append_file.write(out_string)
else:
error_file.write(" bad user id " + andrewid + ",")
pass
try:
error_file.write("\n")
error_file.close()
except:
pass
# Import smtplib for the actual sending function
import smtplib
import os
import csv
# Here are the email package modules we'll need
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
# Create the outer email message.
sender = '"Gold Team" '
bcc = "archives@rob.haus"
cfy = "24"
nfy = "25"
ccost = "35"
ncost = "36.75"
# Change Percentage - this is not calculated so as to use whatever rounding we decide looks best
change = "1.05"
rows = []
with open("FY" + cfy + "Billing.csv", encoding="utf_8_sig") as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
rows.append(row)
e = 1
for row in rows:
# Skip Housing Email
# if (e == 42):
# continue
to = []
erows = dict(row)
print(erows)
# Append up to three email address contacts to email sending list, comment out until ready for real sending
for i in range(2):
if f"CONTACT1EMAIL" in erows:
email = erows[f"CONTACT{i+1}EMAIL"]
name = erows[f"CONTACT{i+1}"]
to.append(f'"{name}" <{email}>')
dept = erows["DEPARTMENT"]
print(dept)
deptfilename = f"{dept.replace(': ','-')}.csv"
# Generate EmailGroup/Department CSV
with open(deptfilename, "a", newline="") as deptcsvfile:
to = "|".join(to)
deptwriter = csv.DictWriter(
deptcsvfile,
fieldnames=[
"To",
"Location",
"Location Description",
"Extra Description",
"Group",
"Group Description",
"Oracle Charge String",
],
)
deptwriter.writerow(
{
"To": to,
"Location": erows["LOCATION"],
"Location Description": erows["LOCATION_DESCRIPTION"],
"Extra Description": erows["NODE_DESCRIPTION"],
"Group": erows["BILLING_GROUP_NAME"],
"Group Description": erows["BILLING_GROUP_DESCRIPTION"],
"Oracle Charge String": str(erows["CHARGE_FUNDING_SOURCE"])
+ str(erows["CHARGE_ACTIVITY"])
+ str(erows["CHARGE_FUNCTION"])
+ str(erows["CHARGE_ORG"])
+ str(erows["CHARGE_ENTITY"]),
}
)
directory = os.getcwd()
for filename in os.listdir(directory):
if filename.endswith(".csv"):
csv_path = os.path.join(directory, filename)
with open(csv_path, "r") as f:
reader = csv.reader(f)
to = next(reader)[0]
devices = sum(1 for _ in f)
dept = str([filename])
msg = MIMEMultipart("alternative")
msg[
"Subject"
] = f"{dept} FY{cfy}/{nfy} Charges for Physical Access Devices (CSGold)"
msg["From"] = sender
to = to.split("|")
msg["To"] = ",".join(to)
with open(csv_path, "r") as f:
attachment = MIMEApplication(f.read())
attachment.add_header(
"Content-Disposition", "text/csv", filename="Billed Locations.csv"
)
msg.attach(attachment)
# textfile='Billing.txt'
# with open(textfile, 'rb') as fp:
# text = fp.read()
htmlfile = "FY" + cfy + "Billing.html"
with open(htmlfile, "rb") as fp:
html = fp.read()
tablerows = ""
html = html.replace(b"{tablerows}", bytes(tablerows, "us-ascii"))
html = html.replace(b"{cfy}", bytes(cfy, "us-ascii"))
html = html.replace(b"{nfy}", bytes(nfy, "us-ascii"))
html = html.replace(b"{ccost}", bytes(ccost, "us-ascii"))
html = html.replace(b"{ncost}", bytes(ncost, "us-ascii"))
html = html.replace(b"{change}", bytes(change, "us-ascii"))
html = html.replace(b"{devices}", bytes(str(devices), "us-ascii"))
html = html.replace(
b"{ccost*devices}", bytes(str(float(ccost) * devices), "us-ascii")
)
html = html.replace(
b"{ncost*devices}", bytes(str(float(ncost) * devices), "us-ascii")
)
# msg.attach(MIMEText(text, "plain", 'us-ascii'))
msg.attach(MIMEText(html, "html", "us-ascii"))
# Send the email via our own SMTP server.
s = smtplib.SMTP("SECRET")
# The below (usually commented) line is how you override the actual email send. You can use this to see how every email looks before they actually get sent to customers.
# s.sendmail(sender, "goodeve@rob.haus", msg.as_string())
s.sendmail(sender, to + [bcc], msg.as_string())
s.quit()
