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
