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