def drop_triggers():
tables = table_names()
for column in tables:
for table in column:
sql_exp = f'DROP TRIGGER IF EXISTS logging_notify on {table} CASCADE'
c.config.sigm_db_cursor.execute(sql_exp)
log(f'{table} log trigger dropped.')
def listen_task(notify):
raw_payload = notify.payload
log(f'Alert Triggered : {raw_payload}')
timestamp, alert, ref_type, ref, user, station = payload_handler(
raw_payload)
statements.duplicate_alert_check(timestamp, alert, ref_type, ref, user,
station)
def file_generator():
log('Generating HTML and XLSX files for critical parts report')
plimp_generator()
vbs_file = r'\files\vba\ASSEMBLIES.vbs'
vbs_path = f'{c.config.parent_dir}{vbs_file}'
call(f'"{vbs_path}"', shell=True)
log('HTML/XLSX file generation complete')
def salesman_emails(cc_override=None, pending_orders=False):
log('Starting salesmen emails')
for salesman in c.config.SALESMEN:
email_body = ''
for grouping in c.config.GROUPINGS:
file_name = f'{salesman} {grouping}'
email_body = files.email_body_generator(email_body, file_name, header=grouping)
if email_body != '':
# email_pdf = files.pdf_generator(email_body)
# attachments = [email_pdf]
time_stamp = files.time_stamp_generator()
subject = f'{salesman} {time_stamp}'
if salesman == 'MARK STACHOWSKI':
cc_list = cc_override if cc_override is not None else ['*****@*****.**']
send_email(email_body, ['*****@*****.**'], cc_list,
# attachments,
subject=subject)
elif salesman == 'GREG PHILLIPS':
if pending_orders is False:
send_email(email_body, ['*****@*****.**'], ['*****@*****.**'],
# attachments,
subject=subject)
# files.delete_pdf_file(email_pdf)
else:
if datetime.datetime.today().weekday() not in (5, 6):
email_body = 'No orders entered for current report time frame.'
if salesman == 'MARK STACHOWSKI':
send_email(email_body, ['*****@*****.**'], [''])
elif salesman == 'GREG PHILLIPS':
if pending_orders is False:
send_email(email_body, ['*****@*****.**'], ['*****@*****.**'])
def main():
c.config = Config(__file__)
init_app_log_dir()
log(f'Starting {__file__}')
c.config.sql_connections()
add_sql_files()
listen(listen_task)
def main():
c.config = Config(__file__)
init_app_log_dir()
log(f'Starting {__file__}')
c.config.sql_connections()
add_sql_files()
start_scheduler(scheduler_task)
def listen_task(config, notify):
raw_payload = notify.payload
record_type, reference, user, station = data.payload_handler(raw_payload)
try:
if not any(i in reference for i in ('<', '>', ':', '"', '/', '\\', '|', '?', '*')):
if record_type not in ['PRT RENAME', 'CLI RENAME']:
directory = files.create_dir(record_type, reference)
# Additional files/folders for parts
if record_type == 'PRT':
prt_no = reference
prt_folder = directory
files.extend_dir(prt_no, prt_folder)
elif record_type == 'PRT RENAME':
old_prt_no = reference.split("}, {")[0][1:]
new_prt_no = reference.split("}, {")[1][:-1]
files.rename_prt_no(old_prt_no, new_prt_no)
elif record_type == 'CLI RENAME':
old_cli_no = reference.split("}, {")[0][1:]
new_cli_no = reference.split("}, {")[1][:-1]
files.rename_cli_no(old_cli_no, new_cli_no)
elif record_type == 'SUP RENAME':
old_sup_no = reference.split("}, {")[0][1:]
new_sup_no = reference.split("}, {")[1][:-1]
files.rename_sup_no(old_sup_no, new_sup_no)
else:
log(f'{record_type} {reference} includes illegal character')
except:
pass
def main():
c.config = Config(__file__)
init_app_log_dir()
log(f'Starting {__file__}')
c.config.sql_connections()
add_sql_files()
# daily_orders_task()
weekly_pending_task()
def pdf_generator(email_body, time_stamp):
log('Generating PDF file')
pdf_name = f'Assemblies ({time_stamp}).pdf'
pdf_file = f'{c.config.parent_dir}\\files\\pdf\\{pdf_name}'
pdfkit.from_string(email_body, pdf_file, configuration=c.config.PDF_CONFIG)
pdf = {'name': pdf_name, 'file': pdf_file}
log('Done generating PDF file')
return pdf
def main():
init_app_log_dir()
log(f'Starting {__file__}')
folders_config = config.Config()
quatro.add_sql_files(folders_config)
files.init_ord_directories(folders_config)
files.init_cli_directories(folders_config)
quatro.listen(folders_config, tasks.listen_task)
def pdf_generator(config, email_body, time_stamp):
log('Generating PDF file')
pdf_name = f'Critical Parts ({time_stamp}).pdf'
pdf_file = f'{config.PARENT_DIR}\\files\\pdf\\{pdf_name}'
pdfkit.from_string(email_body, pdf_file, configuration=config.PDF_CONFIG)
pdf = {'name': pdf_name, 'file': pdf_file}
log('Done generating PDF file')
return pdf
def main():
c.config = Config(__file__)
init_app_log_dir()
log(f'Starting {__file__}')
c.config.sql_connections()
add_sql_files()
get_company_report_dir()
scheduler_task()
def drop_tables():
for table_type, table_type_names in c.config.TABLES.items():
for table_name in table_type_names:
if table_type == 'snap':
table_name = table_name + '_snap'
sql_exp = f'DROP TABLE IF EXISTS {table_name} CASCADE'
c.config.log_db_cursor.execute(sql_exp)
log(f'{table_name} log table dropped.')
def payload_handler(raw_payload):
log(raw_payload)
split_payload = raw_payload.split("], [")
record_type = split_payload[0][1:]
reference = split_payload[1]
sigm_string = split_payload[-1][:-1]
user = re.findall(r'(?<=aSIGMWIN\.EXE u)(.*)(?= m)', sigm_string)[0]
station = re.findall(r'(?<= w)(.*)$', sigm_string)[0]
return record_type, reference, user, station
def format_html(html_path):
log(f'Formatting HTML file ({html_path})')
with open(html_path, "r") as file:
file_data = file.read()
file_data = file_data.replace('align=center', 'align=left')
with open(html_path, "w") as file:
file.write(file_data)
log(f'Done formatting HTML file ({html_path})')
def main():
c.config = Config(__file__)
init_app_log_dir()
log(f'Starting {__file__}')
c.config.sql_connections()
add_sql_files()
start_scheduler(daily_orders_task)
start_scheduler(weekly_pending_task)
listen(listen_task)
def dymo_print(payload):
log(f"Printing {payload['qty']} {payload['label_path']} on {payload['printer']}"
)
c.config.DYMO_COM.SelectPrinter(payload['printer'])
c.config.DYMO_COM.Open(payload['label_path'])
c.config.DYMO_COM.StartPrintJob()
c.config.DYMO_COM.Print(payload['qty'], False)
c.config.DYMO_COM.EndPrintJob()
log(f'Print job completed')
def pending_orders_email():
ord_nos = statements.pending_ord_nos()
if ord_nos:
body = 'The following orders have been pending for 3 or more days: \n\n'
for ord_no in ord_nos:
body += f' {ord_no[0]}\n'
return body
log('No old pending orders for this period.')
return
def add_triggers():
for table in c.config.TABLES['inc']:
sql_exp = f'DROP TRIGGER IF EXISTS logging_notify ON {table}; ' \
f'CREATE TRIGGER logging_notify ' \
f' AFTER UPDATE OR INSERT OR DELETE ' \
f' ON {table} ' \
f' FOR EACH ROW ' \
f' EXECUTE PROCEDURE logging_notify()'
c.config.sigm_db_cursor.execute(sql_exp)
log(f'{table} log trigger added.')
def main():
c.config = Config(__file__)
c.config.set_directories()
init_app_log_dir()
log(f'Starting {__file__}')
c.config.sql_connections()
add_sql_files()
init_ord_directories()
init_cli_directories()
init_sup_directories()
listen(listen_task)
def outstanding_pos_ninja(po_number=None):
report_path = c.config.parent_dir + "\\files\\crystal reports\\GABON001 - Bon d'achat - Quatro Bilingual.rpt"
output_name = f'\\{po_number}.pdf'
output_path = c.config.pdf_dir + output_name
command = f'"{c.config.ninja}" -D QuatroAir -U SIGM -F "{report_path}" -O "{output_path}" ' \
f'-a "NoFormat:0" ' \
f'-a NoBonAchat:({po_number},{po_number})"'
log(f'Sending following command to shell : {command} \n')
check_output(command, shell=True).decode()
report_pdf = {'file': output_path, 'name': output_name}
return report_pdf
def payload_handler(raw_payload):
log(raw_payload)
split_payload = raw_payload.split(', ')
sigm_string = split_payload[-1]
payload = {
'db_ref': split_payload[0],
'db_ref_type': split_payload[1],
'label_type': split_payload[2],
'qty_ref': split_payload[3],
'label_name': split_payload[4] if split_payload[2] == 'GENERIC' else '',
'station': re.findall(r'(?<= w)(.*)$', sigm_string)[0]
}
log(repr(payload))
return payload
def main():
c.config = Config(__file__)
init_app_log_dir()
log(f'Starting {__file__}')
applications = []
while 1:
for app_name in c.config.APPLICATIONS:
app_running = False
app_path = f'{c.config.grandparent_dir}\\{app_name}\\run.py'
for app_process in applications:
if app_process.args[1] == app_path:
poll = app_process.poll()
if poll is None:
app_running = True
else:
stdout, stderr = app_process.communicate()
log('Error detected, terminating ' + app_path)
log('Error message : ' + repr(stderr))
app_process.terminate()
applications.remove(app_process)
send_email('Error Message : ' + repr(stderr),
['*****@*****.**'], [''],
subject='[APP ERROR] ' + app_name)
if not app_running:
app_process = subprocess.Popen([sys.executable, app_path],
stdout=subprocess.PIPE,
stderr=subprocess.PIPE,
shell=True)
applications.append(app_process)
log('Started ' + app_path)
def outstanding_purchase_order_purchasers():
sql_exp = f"""
SELECT DISTINCT usr_name
FROM purchase_order_line
JOIN purchase_order_header USING(puh_id)
WHERE pul_prch_qty > pul_rcvd_qty
AND pul_req_dt IS NOT NULL
AND pul_req_dt = now()::DATE + '2 days'::INTERVAL
"""
log(sql_exp)
result_set = sql_query(sql_exp, c.config.sigm_db_cursor)
return tabular_data(result_set)
def duplicate_alert_check(timestamp, alert, ref_type, ref, user, station):
sql_exp = f'SELECT * FROM duplicate_alert_check(' \
f'\'{timestamp}\', \'{alert}\', \'{ref_type}\', \'{ref}\', \'{user}\', \'{station}\')'
c.config.log_db_cursor.execute(sql_exp)
result_set = c.config.log_db_cursor.fetchall()
log_message = f'{alert} on {ref_type} {ref} by {user} on workstation {station} at {timestamp}\n'
for row in result_set:
for cell in row:
check = cell
if check == 1:
log(f'Error Logged : {log_message}')
log_handler(timestamp, alert, ref_type, ref, user, station)
data.alert_handler(alert, ref, user)
else:
log(f'Duplicate Error : {log_message}')
def extend_tables():
for table_type, table_type_names in c.config.TABLES.items():
for table_name in table_type_names:
if table_type == 'snap':
table_name += '_snap'
for column_pair in c.config.COLUMNS[table_type]:
column = column_pair[0]
attribute = column_pair[1]
try:
sql_exp = f'ALTER TABLE IF EXISTS {table_name} ' \
f'ADD COLUMN {column} {attribute};'
c.config.log_db_cursor.execute(sql_exp)
log(f'Added {column} column to {table_name} table.')
except:
log(f'{column} column already exists on {table_name} table.'
)
def write_snap_log(table_name, ref_name, timestamp):
str_columns = column_name_str(table_name)
sql_exp = f'SELECT DISTINCT {ref_name} FROM {table_name} WHERE time_stamp::DATE = \'{timestamp}\'::DATE'
result_set = sql_query(sql_exp, c.config.log_db_cursor)
ref_table = tabular_data(result_set)
for ref_row in ref_table:
ref = ref_row[0]
sql_exp = f'SELECT * FROM {table_name} WHERE {ref_name} = {ref}'
result_set = sql_query(sql_exp, c.config.sigm_db_cursor)
table_data = tabular_data(result_set)
rows = len(table_data)
log(f'Writing {rows} rows to {table_name}_snap for {ref_name} {ref}')
for row in table_data:
str_values = data.row_value_str(row)
sql_exp = f'INSERT INTO {table_name}_snap ({str_columns}, time_stamp) ' \
f'VALUES ({str_values}, \'{timestamp}\')'
c.config.log_db_cursor.execute(sql_exp)
def copy_table(source_table_name, dest_table_name, source_db='log'):
if source_db == 'log':
source_cursor = c.config.log_db_cursor
elif source_db == 'sigm':
source_cursor = c.config.sigm_db_cursor
str_columns = column_name_str(source_table_name)
source_table = whole_table(source_table_name)
rows = len(source_table)
log(f'Copying {rows} rows from table {source_table_name} '
f'using {source_cursor} to {dest_table_name} using {source_cursor}')
for row in source_table:
str_values = data.row_value_str(row)
sql_exp = fr'INSERT INTO {dest_table_name} ({str_columns}) VALUES ({str_values})'
c.config.log_db_cursor.execute(sql_exp)
log(f'Copying table {source_table_name} to {dest_table_name} complete.')
def main():
c.config = Config(__file__)
init_app_log_dir()
log(f'Starting {__file__}')
c.config.sql_connections()
add_sql_files()
# drop_tables(logging_config)
# drop_triggers(logging_config)
add_triggers()
add_tables()
extend_tables()
init_snap_tables()
start_scheduler(scheduler_task)
listen(listen_task)
def run_ninja(start_date, end_date, salesman):
ninja = c.config.parent_dir + '\\CrystalReportsNinja.exe'
report = c.config.parent_dir + '\\files\\crystal reports\\Sales Order Summary.rpt'
date_stamp = f'{start_date.month}-{start_date.day} to {end_date.month}-{end_date.day}'
output_dir = c.config.parent_dir + '\\files\\pdf'
output_name = f'\\Weekly Sales Report ({date_stamp}).pdf'
output_path = output_dir + output_name
rep = salesman['name']
curr = salesman['currency']
command = f'"{ninja}" -D QuatroAir -U SIGM -F "{report}" -O "{output_path}" ' \
f'-a "Salesman:{rep}" ' \
f'-a "Date Range:({start_date}, {end_date})" ' \
f'-a "Currency:{curr}"'
log(f'Sending following command to shell : {command} \n')
check_output(command, shell=True).decode()
report_pdf = {'file': output_path, 'name': output_name}
return report_pdf
