def get_report():
config = parse_config()
config = config['FILEPATH']
# setting up path to the template
filepath_template = update_path(config["template"])
# setting up path to the data source
filepath_data = update_path(config["data"])
filepath_data_output = update_path(config["data_output"])
filepath_excel = update_path(config["excel"])
dc = set_dates() # dc = date_content
dc["filepath_output"] = update_path(dc["filepath_output"])
# copy template
# (to not lose the template)
shutil.copy(filepath_template, dc["filepath_output"])
# convert file
filepath_data = convert_file(filepath_excel, filepath_data)
# copy data file
shutil.move(filepath_data, filepath_data_output)
# creating workbook objects
wb_template = openpyxl.load_workbook(filepath_template, read_only=True)
wb_output = openpyxl.load_workbook(dc["filepath_output"])
wb_data = openpyxl.load_workbook(filepath_data_output)
# worksheet objects
source = wb_data["Sheet1"]
source = cleanup_file(source)
for i in range (1, 8, 2):
if wb_output.sheetnames[i+1] == "Job Analysis_COPAK" :
pattern = "^[C]"
elif wb_output.sheetnames[i+1] == "Job Analysis_PRINT":
pattern = "^[JMDB]"
else:
pattern = ".*"
target=wb_output.worksheets[i]
max_row = copy_data(source, target, pattern)
if (i < 6):
max_row = add_blank_lines(wb_output.worksheets[i], max_row)
cleanup_jca(wb_output.worksheets[i], wb_output.worksheets[i+1])
wb_output.worksheets[i+1].delete_rows(6+max_row, 500)
# saving the output file
wb_output.save(dc["filepath_output"])
wb_data.save(filepath_data_output)
return dc, source
def clean_home_folder():
filepath = update_path("../../*")
filepaths = []
for file in config:
filepaths.append(update_path(config[file]))
for file in glob.glob(filepath):
if file in filepaths:
if os.path.exists(file):
os.remove(file)
def check_file():
filepath = config["jca_report"]
filepath_output = update_path(config["excel"])
for file in glob.glob(filepath):
shutil.move(file, filepath_output)
if os.path.exists(filepath_output):
return True
def send_email(content_email, rec):
recipients = {'to': [], 'cc': []}
recipients_config = config[rec] # comment this line for testing purposes
subject = content_email['subject']
content = content_email['content']
if content_email['filepath_output'] != "":
attachment = update_path(content_email['filepath_output'])
else:
attachment = ""
yag = yagmail.SMTP(outlook_config['username'],
base64.b64decode(
outlook_config['password']).decode('utf-8'),
host=outlook_config['host'],
port=outlook_config['port'],
smtp_starttls=True,
smtp_ssl=False)
for item in recipients_config:
addresses = recipients_config[item].split(',')
for addresse in addresses:
recipients[item].append(addresse)
# sending email (comment yag.send() to perform tests without sending email)
#print(recipients, subject, content)
yag.send(to=recipients['to'],
subject=subject,
cc=recipients['cc'],
contents=[content, attachment])
on il.item = i.item
left outer join whse w
on il.whse = w.whse
left outer join location l
on il.loc = l.loc
left outer join (select il.item, il.loc, il.whse, (SELECT SUM (itemwhse.qty_on_hand)FROM itemwhse WHERE itemwhse.item = il.item) AS qty_sum from itemloc il
left outer join itemwhse iw
on il.item = iw.item
AND il.whse = iw.whse
where (SELECT SUM (itemwhse.qty_on_hand) as sum_qty FROM itemwhse WHERE itemwhse.item = il.item) < 0
group by il.item, il.loc, il.whse) r
on r.item = il.item
and r.loc = il.loc
and r.whse = il.whse
where r.qty_sum < 0
order by il.whse, il.item, il.NewRank
"""
return select_query
if __name__ == '__main__':
result, headlines = connect_to_db(get_query(), 'guest')
if result:
filename = f"../../negative_stock_report_{get_dates_today()}.xlsx"
filename = update_path(filename)
workbook, filename = create_new_workbook(filename, 'ItemStockRoomLocation')
worksheet = insert_data(workbook.worksheets[0], result, headlines)
get_sum(worksheet)
save_new_workbook(workbook, filename)
send_email(set_date_weekly(), 'Larry')