def pul_id_prt_no(pul_id): sql_exp = f'SELECT prt_no ' \ f'FROM purchase_order_line ' \ f'WHERE pul_id = {pul_id}' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) prt_no = scalar_data(result_set) return prt_no
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 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 puh_no_puh_reference(puh_no): sql_exp = f"SELECT puh_reference " \ f"FROM purchase_order_header " \ f"WHERE puh_no = {puh_no}" result_set = sql_query(sql_exp, c.config.sigm_db_cursor) puh_reference = scalar_data(result_set) return puh_reference
def plq_id_cli_no(plq_id): sql_exp = f'SELECT cli_no FROM planning_lot_quantity ' \ f'JOIN order_line USING(orl_id) ' \ f'JOIN order_header USING(ord_id) ' \ f'JOIN client USING(cli_id) ' \ f'WHERE plq_id = {plq_id}' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) cli_no = scalar_data(result_set) return cli_no
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 orl_id_ord_no(orl_id): sql_exp = f'SELECT ord_no FROM order_line WHERE orl_id = {orl_id}' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) ord_no = scalar_data(result_set) return ord_no
def puh_no_sup_name1(puh_no): sql_exp = f'SELECT sup_name1 FROM purchase_order_header JOIN supplier USING(sup_id) WHERE puh_no = {puh_no}' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) sup_name1 = scalar_data(result_set) return sup_name1
def puh_no_usr_no(puh_no): sql_exp = f'SELECT usr_no FROM purchase_order_header WHERE puh_no = {puh_no}' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) usr_no = scalar_data(result_set) return usr_no
def empty_query_test(): sql_exp = r"SELECT prt_no FROM part WHERE prt_no = 'F191S'" log(sql_exp) result_set = sql_query(sql_exp, c.config.sigm_db_cursor) plimp_lines = scalar_data(result_set) return plimp_lines
def get_sup_name1(sup_id): sql_exp = f"SELECT sup_name1 FROM supplier WHERE sup_id = {sup_id}" log(sql_exp) result_set = sql_query(sql_exp, c.config.sigm_db_cursor) return scalar_data(result_set)
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 ord_no_cli_id(ord_no): sql_exp = f'SELECT cli_id FROM order_header WHERE ord_no = {ord_no}' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) cli_id = scalar_data(result_set) return cli_id
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 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 orl_id_prt_no(config, orl_id): sql_exp = f'SELECT prt_no FROM order_line WHERE orl_id = {orl_id}' result_set = sql_query(sql_exp, config.sigm_db_cursor) prt_no = scalar_data(result_set) return prt_no
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 get_company_report_dir(): sql_exp = 'SELECT cie_report_path FROM company_config' log(sql_exp) result_set = sql_query(sql_exp, c.config.sigm_db_cursor) company_report_dir = scalar_data(result_set) return company_report_dir
def cli_id_cli_name1(cli_id): sql_exp = f'SELECT cli_name1 FROM client WHERE cli_id = {cli_id}' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) cli_name1 = scalar_data(result_set) return cli_name1
def planning_lot_prt_no(plq_lot_no): sql_exp = f'SELECT prt_no FROM planning_lot_quantity WHERE plq_lot_no = {plq_lot_no}' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) prt_no = scalar_data(result_set) return prt_no
def invoice_ord_no(inv_no): sql_exp = f'SELECT ord_no FROM invoicing WHERE inv_no = {inv_no}' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) ord_no = scalar_data(result_set) return ord_no
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 transaction_ptn_desc(ptn_id): sql_exp = f'SELECT ptn_desc FROM part_transaction WHERE ptn_id = {ptn_id}' result_set = sql_query(sql_exp, c.config.sigm_db_cursor) ptn_desc = scalar_data(result_set) return ptn_desc
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 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_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 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