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