def check_inc_table(table_name, timestamp): sql_exp = f'SELECT * FROM {table_name} WHERE time_stamp::DATE = \'{timestamp}\'::DATE' result_set = sql_query(sql_exp, c.config.log_db_cursor) table = tabular_data(result_set) if table: ref_name = snap_log_ref(table_name) return ref_name
def pending_ord_nos(): sql_exp = f'SELECT * FROM old_pending_orders()' result_set = sql_query(sql_exp, c.config.log_db_cursor) if result_set: ord_nos = tabular_data(result_set) return ord_nos return
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 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 order_missing_component_prt_no(ord_no): sql_exp = f'SELECT * FROM order_component_parents({ord_no})' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) kits = [] for row in result_set: parent = [] index = row[0] prt_no = row[1].strip() orl_kitmaster_id = row[2] parent.append(index) parent.append(prt_no) sql_exp = f'SELECT * FROM order_missing_components({ord_no}, {orl_kitmaster_id})' c.config.sigm_db_cursor.execute(sql_exp) result_set = c.config.sigm_db_cursor.fetchall() lines = tabular_data(result_set) parent.append(lines) kits.append(parent) return kits
def order_existing_blankets(ord_no): sql_exp = f'SELECT * FROM order_existing_blankets({ord_no})' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) blankets = tabular_data(result_set) return blankets
def all_ord_nos(): sql_exp = f'SELECT ord_no FROM order_header' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) ord_nos = tabular_data(result_set) return ord_nos
def all_sup_nos(): sql_exp = f'SELECT sup_no FROM supplier' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) sup_nos = tabular_data(result_set) return sup_nos
def all_cli_nos(): sql_exp = f'SELECT cli_no FROM client' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) cli_nos = tabular_data(result_set) return cli_nos
def ord_no_orl_id(config, ord_no): sql_exp = f'SELECT orl_id FROM order_line WHERE ord_no = {ord_no} AND prt_no <> \'\'' result_set = sql_query(sql_exp, config.sigm_db_cursor) orl_ids = tabular_data(result_set) return orl_ids
def critical_parts(config): sql_exp = f"SELECT prt_no FROM critical_quantities WHERE source IN ('PROD', 'ORDER')" result_set = sql_query(sql_exp, config.sigm_db_cursor) prt_nos = tabular_data(result_set) return prt_nos
def prt_no_children(config, prt_no): sql_exp = f"SELECT child_no FROM bom WHERE parent_no = '{prt_no}'" result_set = sql_query(sql_exp, config.sigm_db_cursor) children = tabular_data(result_set) return children
def get_plimp_lines(): sql_exp = r"SELECT * FROM plimp" log(sql_exp) result_set = sql_query(sql_exp, c.config.sigm_db_cursor) plimp_lines = tabular_data(result_set) return plimp_lines
def table_names(): sql_exp = f'SELECT * FROM table_names()' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) tables = tabular_data(result_set) return tables
def whole_table(table_name): sql_exp = f'SELECT * FROM {table_name}' result_set = sql_query(sql_exp, c.config.log_db_cursor) table = tabular_data(result_set) return table
def table_columns(table): sql_exp = f'SELECT * FROM table_columns(\'{table}\')' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) columns = tabular_data(result_set) return columns