def SAP_process_table(table, fields, options, columns_dict, model):
session = Session()
df = SAP_get_table(table, fields, options)
df.rename(columns=columns_dict, inplace=True)
session.execute(model.__table__.insert(), df.to_dict(orient="records"))
session.commit()
session.close()
return df
def SAP_notification_process(date, end_date):
# Get all notifications from the VIQMEL
print('Processing notifications')
#df_notification = SAP_process_table('VIQMEL',['QMNUM','IWERK','QMART','AUFNR','EQUNR','QMTXT','STRMN','LTRMN','ERDAT','PRIOK','ERNAM','TPLNR','ARBPL', 'OBJNR'],\
# "IWERK EQ 'TR01' AND ERDAT >= '" + date + "' and ERDAT <= '"+end_date+"'",notification_columns, Notification)
df_notification = pd.read_sql_table("SAP_notifications", con=engine)
# Get all notifications´ types TQ80
print('Processing notifications type')
#SAP_process_table('TQ80', ['QMART','STSMA'], "MANDT EQ '020'",notification_type_columns, Notification_Type)
df_type = pd.read_sql_table("SAP_notification_type", con=engine)
# Get all notifications´ additional texts or comments QMFE
print('Processing notifications text')
#SAP_process_table('QMFE', ['QMNUM','FETXT'], "FETXT NE '' AND ERDAT >= '" + date + "'",notification_text_columns, Notification_Text)
# Get all notifications' activities from the QMMA
print('Processing notifications activities')
#SAP_process_table('QMMA', ['MANDT','QMNUM','MATXT','MNGRP','MNCOD','MNKAT'], "MANDT EQ '020' AND ERDAT >= '" + date + "'", notification_activity_columns, Notification_Activity)
# Get all notification´s activities header for the QPCD
print('Processing notifications activities header')
#SAP_process_table('QPCT', ['MANDT','CODEGRUPPE','CODE','KURZTEXT','KATALOGART'], "MANDT EQ '020' AND INAKTIV EQ ''", notification_activity_header_columns, Notification_Activity_Header)
# Get all notification´s catalog types for the TQ15
print('Processing notifications catalog')
#SAP_process_table('TQ15T', ['MANDT','KATALOGART','KATALOGTXT'], "MANDT EQ '020' AND SPRACHE EQ 'S'", notification_catalog_columns, Notification_Catalog)
# Get all notifications' causes from the QMUR
print('Processing notifications causes')
#SAP_process_table('QMUR', ['MANDT','QMNUM','URTXT'], "MANDT EQ '020' AND ERDAT >= '" + date + "'", notification_cause_columns, Notification_Cause)
# Get all TRANSELCA´s equipment (K: Equipos Transelca - L: MAF Transelca)
print('Processing equipments')
#SAP_process_table('EQUI', ['MANDT','EQUNR','EQART','HERST','TYPBZ'], "EQTYP EQ 'K' OR EQTYP EQ 'L'", equipment_columns, Equipment)
# Get all equipments´ descriptions
print('Processing equipments description')
#SAP_process_table('EQKT', ['MANDT','EQUNR','EQKTX'], "MANDT EQ '020'", equipment_text_columns, Equipment_Text)
# Get all TRANSELCA's workplaces
print('Processing workplaces')
#df_workplace_id = SAP_get_table('CRHD',['MANDT','OBJID','ARBPL'],"MANDT EQ '020' AND WERKS EQ 'TR01'")
#df_workplace_text = SAP_get_table('CRTX',['MANDT','OBJID','KTEXT'],"MANDT EQ '020' AND SPRAS EQ 'S'")
#df_workplace = pd.merge(df_workplace_id, df_workplace_text, how='inner', on=['OBJID']) # filter per values in df_system
#df_workplace.rename(columns=work_center_columns,inplace=True)
#session = Session()
#session.execute(Work_Center.__table__.insert(),df_workplace.to_dict(orient="records"))
#session.commit()
#session.close()
# Get all TRANSELCA's functional location
print('Processing functional location')
#session = Session()
#print(df_notification['func_location'].unique().shape)
#df_func = SAP_get_table_by_data('IFLOS',['TPLNR','STRNO', 'ACTVS','TPLKZ','ERDAT','VERSN','ERNAM'],"ACTVS EQ 'X' AND TPLNR EQ '#field#'",df_notification['func_location'].unique())
#df_func = df_func.drop_duplicates()
#df_func.rename(columns=func_location_columns,inplace=True)
#session.execute(Functional_Location.__table__.insert(),df_func.to_dict(orient="records"))
#session.commit()
#session.close()
# Get System Status
print('Processing System Status Header')
#df_system = SAP_process_table('TJ02T', ['ISTAT','TXT04','TXT30'], "SPRAS EQ 'S'", system_columns, System_Status)
df_system = pd.read_sql_table("SAP_system_status", con=engine)
# Get User Status Header
print('Processing User Status Header')
#df_user = SAP_process_table('TJ30T', ['STSMA','ESTAT','TXT04','TXT30'], "MANDT EQ '020' AND SPRAS EQ 'S'", user_columns, User_Status)
df_user = pd.read_sql_table("SAP_user_status", con=engine)
# Get all statuses
print(df_notification.shape)
print('GET STATUSES START')
#session = Session()
#df_status = SAP_get_table_by_data('JEST',['MANDT','OBJNR','STAT','INACT'],"MANDT EQ '020' AND OBJNR EQ '#field#'",df_notification['obj_nr'])
#df_status = df_status.drop_duplicates()
#df_status.rename(columns=status_columns,inplace=True)
#df_status_system = pd.merge(df_status, df_system, how='inner', on=['status_id']) # filter per values in df_system
#df_status_user = pd.merge(df_status, df_user['status_id'].drop_duplicates(), how='inner', on=['status_id']) # filter per values in df_user
#df_notification_2 = pd.merge(df_notification, df_type, how='inner', on=['type_n']) # filter per values in df_user
#df_status_user_x = pd.merge(df_status_user, df_notification_2[['obj_nr','status_schema']], how='inner', on=['obj_nr'])
#session.execute(Notification_System_Status.__table__.insert(),df_status_system.to_dict(orient="records"))
#session.execute(Notification_User_Status.__table__.insert(),df_status_user_x.to_dict(orient="records"))
#session.commit()
#session.close()
# Get log statuses
print('GET LOG STATUSES START')
session = Session()
df_status_system = pd.read_sql_table('SAP_notification_system_status',
con=engine)
df_status_user_x = pd.read_sql_table('SAP_notification_user_status',
con=engine)
df_log_status = SAP_get_table_by_data(
'JCDS', [
'MANDT', 'OBJNR', 'STAT', 'USNAM', 'UDATE', 'UTIME', 'TCODE',
'CDTCODE', 'INACT', 'CHIND'
], "MANDT EQ '020' AND UDATE >='" + date + "' AND OBJNR EQ '#field#'",
df_notification['obj_nr'])
df_log_status = df_log_status.drop_duplicates()
df_log_status.rename(columns=log_columns, inplace=True)
df_log_status_system = pd.merge(df_log_status,
df_status_system[['obj_nr', 'status_id']],
how='inner',
on=['obj_nr', 'status_id'])
df_log_status_user = pd.merge(
df_log_status,
df_status_user_x[['obj_nr', 'status_id', 'status_schema']],
how='inner',
on=['obj_nr', 'status_id']) # filter
session.execute(Notification_Log_User.__table__.insert(),
df_log_status_user.to_dict(orient="records"))
session.execute(Notification_Log_System.__table__.insert(),
df_log_status_system.to_dict(orient="records"))
session.commit()
session.close()
