Exemplo n.º 1
0
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
Exemplo n.º 2
0
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
Exemplo n.º 3
0
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)
Exemplo n.º 4
0
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)
Exemplo n.º 5
0
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
Exemplo n.º 6
0
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
Exemplo n.º 7
0
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
Exemplo n.º 8
0
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
Exemplo n.º 9
0
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
Exemplo n.º 10
0
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
Exemplo n.º 11
0
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
Exemplo n.º 12
0
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
Exemplo n.º 13
0
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
Exemplo n.º 14
0
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
Exemplo n.º 15
0
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
Exemplo n.º 16
0
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