def extract_vbak(con, min_extr_date="2020-01-01 00:00:00", mandt="800"):
vbak = {}
try:
vbak = con.prepare_and_execute_query(
"VBAK", ["VBELN", "ERDAT", "ERZET"],
additional_query_part=" WHERE MANDT = '" + mandt + "'")
timestamp_column_from_dt_tm.apply(vbak, "ERDAT", "ERZET",
"event_timestamp")
vbak = vbak[vbak["event_timestamp"] > min_extr_date]
vbak = vbak[["VBELN", "event_timestamp"]].to_dict("r")
vbak = {x["VBELN"]: x["event_timestamp"] for x in vbak}
except:
pass
return vbak
def extract_bkpf(con, gjahr="1997", mandt="800", bukrs="1000"):
additional_query_part = " WHERE GJAHR = '" + gjahr + "' AND MANDT = '" + mandt + "' AND BUKRS = '" + bukrs + "'"
bkpf = con.prepare_and_execute_query(
"BKPF",
["BELNR", "BLART", "CPUDT", "CPUTM", "USNAM", "TCODE", "AWKEY"],
additional_query_part=additional_query_part)
bkpf = bkpf.dropna(subset=["BELNR", "TCODE", "BLART"], how="any")
transactions = set(bkpf["TCODE"].unique())
doc_types = set(bkpf["BLART"].unique())
tcodes = extract_tstct.apply_static(con, transactions=transactions)
blart = extract_blart.apply_static(con, doc_types=doc_types)
cols = {x: "event_" + x for x in bkpf.columns}
bkpf = bkpf.rename(columns=cols)
bkpf["event_ONLYACT"] = bkpf["event_TCODE"].map(tcodes)
bkpf["event_BLART"] = bkpf["event_BLART"].map(blart)
bkpf = bkpf.dropna(subset=["event_ONLYACT", "event_BLART"], how="any")
bkpf["INVOLVED_DOCUMENTS"] = bkpf["event_BELNR"].astype(str)
bkpf["INVOLVED_DOCUMENTS"] = bkpf["INVOLVED_DOCUMENTS"].apply(
constants.set_documents)
bkpf = timestamp_column_from_dt_tm.apply(bkpf, "event_CPUDT",
"event_CPUTM", "event_timestamp")
bkpf_first = bkpf.groupby("event_BELNR").first().reset_index()
first_stream = bkpf_first[[
"event_BELNR", "event_timestamp", "event_BLART"
]].to_dict("records")
doc_first_dates = {
x["event_BELNR"]: x["event_timestamp"]
for x in first_stream
}
doc_types = {x["event_BELNR"]: x["event_BLART"] for x in first_stream}
return bkpf, doc_first_dates, doc_types
def read_cdhdr(con, objectclas=None, mandt="800", ap=""):
additional_query_part = " WHERE OBJECTCLAS = '" + objectclas + "' AND MANDANT = '" + mandt + "'" if objectclas is not None else "WHERE MANDANT = '" + mandt + "'"
if ap:
additional_query_part += " AND OBJECTID IN (" + ap + ")"
df = con.prepare_and_execute_query(
"CDHDR", ["CHANGENR", "USERNAME", "UDATE", "UTIME", "TCODE"],
additional_query_part=additional_query_part)
df.columns = ["event_" + x for x in df.columns]
if len(df) > 0:
df = timestamp_column_from_dt_tm.apply(df, "event_UDATE",
"event_UTIME",
"event_timestamp")
#df["event_timestamp"] = pd.to_datetime(df["event_UDATE"])
df = df.sort_values("event_timestamp")
transactions = set(df["event_TCODE"].unique())
tstct = extract_tstct.apply_static(con, transactions=transactions)
df["event_ONLYACT"] = df["event_TCODE"].map(tstct)
return df
def goods_receipt(con,
gjahr=None,
mandt="800",
bukrs="1000",
extra_els_query=None):
additional_query_part = " WHERE VGABE = '1' AND MANDT = '" + mandt + "'"
if gjahr is not None:
additional_query_part += " AND GJAHR = '" + gjahr + "'"
if "EKBE" in extra_els_query:
additional_query_part += " " + extra_els_query["EKBE"]
ekbe = con.prepare_and_execute_query(
"EKBE", [
"EBELN", "EBELP", "BELNR", "BUZEI", "BUDAT", "GJAHR", "CPUDT",
"CPUTM", "ERNAM"
],
additional_query_part=additional_query_part)
ekbe_nodes_types = {}
if len(ekbe) > 0:
ekbe["OBJECTID"] = ekbe["EBELN"] + ekbe["EBELP"]
ekbe.columns = ["event_" + x for x in ekbe.columns]
ekbe = timestamp_column_from_dt_tm.apply(ekbe, "event_CPUDT",
"event_CPUTM",
"event_timestamp")
#ekbe["event_CPUDTTM"] = ekbe["event_CPUDT"] + " " + ekbe["event_CPUTM"]
#ekbe["event_timestamp"] = pd.to_datetime(ekbe["event_CPUDT"].dt.strftime(con.DATE_FORMAT_INTERNAL) + " " + ekbe["event_CPUTM"], errors="coerce", format=con.DATE_FORMAT_INTERNAL + " " + con.HOUR_FORMAT_INTERNAL)
#ekbe["event_timestamp"] = pd.to_datetime(ekbe["event_BUDAT"], errors="coerce", format=con.DATE_FORMAT)
ekbe = ekbe.dropna(subset=["event_timestamp"])
if len(ekbe) > 0:
ekbe["event_FROMTABLE"] = "EKBE"
ekbe["event_node"] = "EKBEGR_" + ekbe["event_BELNR"] + ekbe[
"event_GJAHR"]
ekbe["event_activity"] = "Goods Receipt"
ekbe["event_timestamp"] = ekbe["event_timestamp"] + pd.Timedelta(
"1 second")
ekbe["event_USERNAME"] = ekbe["event_ERNAM"]
ekbe_nodes_types = {
x: "EKBEGR"
for x in ekbe["event_node"].unique()
}
return ekbe, ekbe_nodes_types
def apply(con,
keep_first=True,
min_extr_date="2020-01-01 00:00:00",
mandt="800"):
# RFMNG, MEINS, RFWRT, WAERS, MATNR, BWART
try:
vbfa = con.prepare_and_execute_query(
"VBFA", [
"ERDAT", "ERZET", "VBELN", "VBELV", "VBTYP_N", "VBTYP_V",
"RFMNG", "MEINS", "RFWRT", "WAERS", "MATNR", "BWART", "VRKME",
"FKTYP"
],
additional_query_part=" WHERE MANDT = '" + mandt + "'")
except:
vbfa = con.prepare_and_execute_query(
"VBFA", ["ERDAT", "ERZET", "VBELN", "VBELV", "VBTYP_N", "VBTYP_V"],
additional_query_part=" WHERE MANDT = '" + mandt + "'")
timestamp_column_from_dt_tm.apply(vbfa, "ERDAT", "ERZET",
"event_timestamp")
min_extr_date = parser.parse(min_extr_date)
vbfa = vbfa[vbfa["event_timestamp"] > min_extr_date]
doc_types = set(vbfa["VBTYP_N"].unique()).union(
set(vbfa["VBTYP_V"].unique()))
vbtyp = extract_vbtyp.apply_static(con, doc_types=doc_types)
vbfa["VBTYP_N"] = vbfa["VBTYP_N"].map(vbtyp)
vbfa["VBTYP_V"] = vbfa["VBTYP_V"].map(vbtyp)
vbfa = vbfa_closure(con, vbfa, min_extr_date, mandt=mandt)
vbfa = vbfa[vbfa["event_timestamp"] >= min_extr_date]
vbfa = vbfa.reset_index()
vbfa["event_id"] = vbfa.index.astype(str)
cols = {}
for x in vbfa.columns:
if x.startswith("event_"):
cols[x] = x
else:
cols[x] = "event_" + x
vbfa = vbfa.rename(columns=cols)
vbfa["event_activity"] = "Create " + vbfa["event_VBTYP_N"]
if not keep_first:
vbfa["event_activity"] = vbfa["event_activity"] + " Item"
vbfa["event_id"] = vbfa["event_id"].astype(int)
vbfa = vbfa.sort_values("event_id")
#vbfa["INVOLVED_DOCUMENTS"] = vbfa["event_VBELV"].astype(str) + constants.DOC_SEP + vbfa["event_VBELN"].astype(str)
#vbfa["INVOLVED_DOCUMENTS"] = vbfa["INVOLVED_DOCUMENTS"].apply(constants.set_documents)
#vbfa.to_csv("prova1.csv", index=False)
#del vbfa["INVOLVED_DOCUMENTS"]
doctypes_n = vbfa["event_VBTYP_N"].unique()
doctypes_v = vbfa["event_VBTYP_V"].unique()
list_dfs = []
for value in doctypes_n:
df = vbfa[vbfa["event_VBTYP_N"] == value]
df["DOCTYPE_" + str(value)] = df["event_VBELN"]
list_dfs.append(df)
for value in doctypes_v:
df = vbfa[vbfa["event_VBTYP_V"] == value]
df["DOCTYPE_" + str(value)] = df["event_VBELV"]
list_dfs.append(df)
if list_dfs:
vbfa = pd.concat(list_dfs)
vbfa = exploded_mdl_to_succint_mdl.apply(vbfa)
vbfa = vbfa.reset_index()
vbfa = vbfa.sort_values("event_id")
vbfa["event_id"] = vbfa["event_id"].astype(str)
else:
vbfa = pd.DataFrame({"case:concept:name": [], "VBELN": []})
#vbfa.to_csv("prova2.csv", index=False)
return vbfa