def test_date_cols(self):
global server, pwd, user
print("==> test_date_cols")
try:
host_params1 = {'host': server,
'port': 9200, 'use_ssl': True} # RPINUM
es = ES([host_params1], connection_class=RC, http_auth=(
user, pwd), use_ssl=True, verify_certs=False)
print(es.info())
try:
es.indices.delete('test_date_cols')
except elasticsearch.NotFoundError:
pass
doc = {
'attr1': 'test'
}
es.index(index="test_date_cols", id="t1", doc_type='_doc', body=doc)
time.sleep(1)
res = es_helper.elastic_to_dataframe(es, index="test_date_cols", datecolumns=["date1"])
print(len(res))
print(res.columns)
self.assertTrue("date1" in res.columns)
doc = {
'attr1': 'test',
'date1': datetime.now()
}
es.index(index="test_date_cols", id="t2", doc_type='_doc', body=doc)
time.sleep(1)
res = es_helper.elastic_to_dataframe(es, index="test_date_cols", datecolumns=["date1"])
self.assertTrue("date1" in res.columns)
doc = {
'attr1': 'test',
'date2': datetime.now()
}
es.index(index="test_date_cols", id="t3", doc_type='_doc', body=doc)
time.sleep(1)
res = es_helper.elastic_to_dataframe(es, index="test_date_cols", datecolumns=["date1", "date2"])
self.assertTrue("date1" in res.columns)
self.assertTrue("date2" in res.columns)
es.indices.delete('test_date_cols')
finally:
pass
def getTagWeather(es, city,table,tag,time):
query='cpost: '+city
value = 0
if table == 'pred':
start = datetime.now()
end = start+timedelta(hours=time)
df = es_helper.elastic_to_dataframe(es, 'weather_prediction', start=start, end=end, query=query)
df = df.sort_values(by="@timestamp", ascending=False).reset_index(drop=True)
logger.info(df)
value = df.at[0, tag]
logger.info(value)
else:
end = datetime.now()
start = end-timedelta(hours = 3)
df = pd.DataFrame()
if time == 0:
df = es_helper.elastic_to_dataframe(es, 'weather_real', start=start, end=end, query=query)
df = df.sort_values('@timestamp', ascending=False).reset_index(drop=True)
logger.info(df)
value = df.at[0, tag]
else:
start = end-timedelta(hours=time)
df = es_helper.elastic_to_dataframe(es, 'weather_real', start=start, end=end, query=query)
df = df.sort_values('@timestamp').reset_index(drop=True)
value = df.at[0, tag]
return str(value)
def test_size(self):
global server, pwd
print("==> test_size")
try:
host_params1 = {
'host': server,
'port': 9200,
'use_ssl': True
} # RPINUM
es = ES([host_params1],
connection_class=RC,
http_auth=("user", pwd),
use_ssl=True,
verify_certs=False)
print(es.info())
res2 = es_helper.elastic_to_dataframe(
es,
index="docker_stats*",
size=10,
timestampfield="read",
start=datetime.datetime.now() - datetime.timedelta(hours=1),
end=datetime.datetime.now())
print(len(res2))
self.assertTrue(res2 is not None)
self.assertTrue((len(res2) >= 0) and (len(res2) <= 10))
finally:
pass
def get(self, user=None):
logger.info("schamps - get products tree")
df = es_helper.elastic_to_dataframe(es, index="products_parameters_new", query="display: true")
objClass = {}
lvl1 = df .sortLvl1.unique()
for i in lvl1:
objClass[i] = {}
subDF = df.loc[df['sortLvl1'] == i]
lvlObjects = subDF.sortLvl2.unique()
for j in lvlObjects:
objClass[i][j] = {}
subSubDF = subDF.loc[subDF['sortLvl2'] == j]
subLvlObjectsLvl3 = subSubDF.sortLvl3.unique()
objClass[i][j]['sortLvl3'] = subLvlObjectsLvl3.tolist()
subLvlObjectsLvl4 = subSubDF.sortLvl4.unique()
objClass[i][j]['sortLvl4'] = subLvlObjectsLvl4.tolist()
subLvlObjectsLvl5 = subSubDF.sortLvl5.unique()
objClass[i][j]['sortLvl5'] = subLvlObjectsLvl5.tolist()
subLvlObjectsLvl6 = subSubDF.sortLvl6.unique()
objClass[i][j]['sortLvl6'] = subLvlObjectsLvl6.tolist()
subLvlObjectsLvl7 = subSubDF.sortLvl7.unique()
objClass[i][j]['sortLvl7'] = subLvlObjectsLvl7.tolist()
subLvlObjectsLvl8 = subSubDF.sortLvl8.unique()
objClass[i][j]['sortLvl8'] = subLvlObjectsLvl8.tolist()
subLvlObjectsLvl9 = subSubDF.sortLvl9.unique()
objClass[i][j]['sortLvl9'] = subLvlObjectsLvl9.tolist()
subLvlObjectsLvl10 = subSubDF.sortLvl10.unique()
objClass[i][j]['sortLvl10'] = subLvlObjectsLvl10.tolist()
#for k in subLvlObjects:
# objClass[i][j][k] = {}
req = {'results': False, 'reccords': objClass}
logger.info(objClass)
return {'error':"",'status':'ok', 'data': json.dumps(req)}
def test_elastic_to_panda(self):
"""
Send Receive
"""
global server, pwd, user
print("==> test_elastic_to_panda")
try:
host_params1 = {'host': server,
'port': 9200, 'use_ssl': True} # RPINUM
es = ES([host_params1], connection_class=RC, http_auth=(
user, pwd), use_ssl=True, verify_certs=False)
print(es.info())
res2 = es_helper.elastic_to_dataframe(es, index="docker_stats*", scrollsize=1000, datecolumns=[
"read"], timestampfield="read", start=datetime.now()-timedelta(hours=1), end=datetime.now())
res2["read"].describe()
print(len(res2))
self.assertTrue(res2 is not None)
self.assertTrue(len(res2) > 0)
finally:
pass
def getLastUpdate(es):
end = datetime.now()
start = end-timedelta(days=3)
df = es_helper.elastic_to_dataframe(es, 'weather_real', start=start, end=end)
df = df.sort_values(by="@timestamp", ascending=False).reset_index(drop=True)
value = df.at[0, "@timestamp"]
return int(value/60000)
def getECPowerCogens():
global es
cogens = es_helper.elastic_to_dataframe(es, 'cogen_parameters', query="source: ecpower")
cogens = cogens [['ecid', 'name']]
cogensTab = []
for key, row in cogens.iterrows():
rec = {"id": row['ecid'], "name": row['name']}
cogensTab.append(rec)
return cogensTab
def getLogoWeather(es, city,table,time):
query='cpost: '+city
value = 1
if table == 'pred':
start = datetime.now()
end = start+timedelta(hours=time)
df = es_helper.elastic_to_dataframe(es, 'weather_prediction', start=start, end=end, query=query)
df = df.sort_values(by="@timestamp", ascending=False).reset_index(drop=True)
logger.info(df)
row = df.iloc[0]
logger.info(row)
radiance = int(row['radiance'])
symbol_cover = int(row['symbol_cover'])
wind = int(row['wind_speed_to'])
rain = float(row['qpcp'])
temp = float(row['temp'])
if radiance <= 750 and radiance > 350 and symbol_cover in [6,7]:
value = 2
if wind >= 50:
value = 4
elif radiance > 20 and symbol_cover in [4,5]:
value = 3
elif symbol_cover in [6,7] and rain >=4 and rain < 7:
value = 5
if temp < 2:
value = 7
elif rain > 7:
value = 6
if temp < 2:
value = 8
elif radiance == 0 and symbol_cover in [6,7]:
value = 9
elif radiance == 0 and symbol_cover in [4,5,6,7]:
value = 10
if rain > 4:
value = 12
if wind >= 50:
value = 11
elif radiance == 0 and symbol_cover in [6,7] and temp < 2 and rain > 4:
value = 13
if rain > 7:
value = 14
return value
def calcCogen():
global es
logger.info("Calc Cogen ...")
end = datetime.now()
start = end - timedelta(days=30)
cogens = es_helper.elastic_to_dataframe(es,
'cogen_computed',
query='*',
start=start,
end=end)
cogengrouped = cogens.groupby(['Contract', 'client', 'name']).agg({
'@timestamp':
'max',
'Hours_Index':
'max',
'Starts_Index':
'max'
}).reset_index()
cogengrouped = cogengrouped.set_index('name')
params = es.search("cogen_parameters", size='10000')
for cog in params['hits']['hits']:
name = cog['_source']['name']
if name in cogengrouped.index:
cogstats = cogengrouped.loc[name, :]
cog['_source']['Hours'] = cogstats['Hours_Index']
cog['_source']['Starts'] = cogstats['Starts_Index']
cog['_source']['LastUpdate'] = int(time.time() * 1000)
cog['_source']['modifyBy'] = 'GTC'
bulkbody = ''
action = {}
for cog in params['hits']['hits']:
action["index"] = {
"_index": cog['_index'],
"_type": "doc",
"_id": cog['_id']
}
# print(action)
newrec = cog['_source']
# print(newrec)
bulkbody += json.dumps(action) + "\r\n"
bulkbody += json.dumps(newrec) + "\r\n"
res = es.bulk(body=bulkbody)
def retrieve_raw_data(day):
start_dt = datetime(day.year, day.month, day.day)
end_dt = datetime(start_dt.year, start_dt.month, start_dt.day, 23, 59, 59)
df_raw = es_helper.elastic_to_dataframe(es,
index='opt_cleaned_data*',
query='*',
start=start_dt,
end=end_dt,
scrollsize=10000,
size=1000000)
containertimezone = pytz.timezone(get_localzone().zone)
df_raw['@timestamp'] = pd.to_datetime(df_raw['@timestamp'], \
unit='ms', utc=True).dt.tz_convert(containertimezone)
df_raw = df_raw.sort_values('@timestamp')
return df_raw
def retrieve_dict_old(es):
today_time = datetime.min.time()
today_datetime = datetime.combine(datetime.now().date(), today_time)
dataframe = es_helper.elastic_to_dataframe(
es,
index="parl_kizeo*",
scrollsize=1000,
timestampfield="update_time_dt",
query='wo_state:Underway',
end=today_datetime,
_source=['update_time_dt', 'wo_state', 'user_name'])
#return dataframe
dict_old = {}
for key, value in dataframe.groupby(
'user_name').count()['_id'].iteritems():
try:
dict_old[key.split(' ')[0]] = value
except:
print('unable to split this key: ' + str(key))
return dict_old
def retrieve_raw_data(day):
start_dt = datetime(day.year, day.month, day.day, 14, 30, 0)
end_dt = datetime(start_dt.year, start_dt.month, start_dt.day, 20, 59, 59)
df_raw = es_helper.elastic_to_dataframe(
es,
index='opt_sites_data*',
query='client: COGLTS AND area_name: *CH4*',
start=start_dt,
end=end_dt,
size=1000000)
containertimezone = pytz.timezone(get_localzone().zone)
try:
df_raw['@timestamp'] = pd.to_datetime(df_raw['@timestamp'], \
unit='ms', utc=True).dt.tz_convert(containertimezone)
except:
pass
df_raw = df_raw.sort_values('@timestamp')
logger.info(df_raw)
return df_raw
def compute_kpi105_monthly(start, end):
"""
Compute KPI 105 monthy records (Records used for the dashboard)
Parameters
----------
start
Date of the first imported record
end
Date of the last imported record
"""
logger.info("====> compute_kpi105_monthly")
starttime = time.time()
logger.info(start)
logger.info(end)
start = mkFirstOfMonth(start)
end = end.replace(day=calendar.monthrange(end.year, end.month)[1])
logger.info(start)
logger.info(end)
# df_kpi105 = etp.genericIntervalSearch(es, 'biac_kpi105', query='*', start=start, end=end)
df_kpi105 = es_helper.elastic_to_dataframe(es,
'biac_kpi105',
query='*',
start=start,
end=end)
df_kpi105['month'] = pd.to_datetime(df_kpi105['@timestamp'],
unit='ms').dt.strftime('%Y-%m')
df_kpi105 = df_kpi105.groupby(['lot', 'month', 'ronde_number'
]).count()[['@timestamp'
]].rename(columns={
'@timestamp': 'count'
}).reset_index()
records_number = 0
for lot in df_kpi105['lot'].unique():
logger.info(">>>>> Working for lot:" + str(lot))
df_lot = df_kpi105[df_kpi105['lot'] == lot]
min_date = datetime.strptime(min(df_lot['month']), '%Y-%m')
max_date = datetime.strptime(max(df_lot['month']), '%Y-%m')
months = []
current = min_date
while current <= max_date:
months.append(current.strftime('%Y-%m'))
current += relativedelta(months=1)
df_months = pd.DataFrame(months)
df_months['join'] = 1
df_months.columns = ['month', 'join']
rondes = [1]
if int(lot) == 2:
rondes = [1, 2, 3]
df_rondes = pd.DataFrame(rondes)
df_rondes['join'] = 1
df_rondes.columns = ['ronde_number', 'join']
df_default = df_months.merge(df_rondes,
left_on='join',
right_on='join')
del df_default['join']
df_default['number_of_days'] = df_default['month'].apply(
lambda x: get_days_already_passed(x))
df_default['_id'] = df_default['month'] + '_' + df_default[
'ronde_number'].astype(str)
df_lot['_id'] = df_lot['month'] + '_' + df_lot['ronde_number'].astype(
str)
del df_lot['month']
del df_lot['ronde_number']
df_merged = df_default.merge(df_lot,
left_on='_id',
right_on='_id',
how="outer")
df_merged['lot'] = df_merged['lot'].fillna(lot)
df_merged['count'] = df_merged['count'].fillna(0)
df_merged['percent'] = round(
100 * df_merged['count'] / df_merged['number_of_days'], 2)
df_merged['_index'] = 'biac_month_kpi105'
df_merged['_timestamp'] = pd.to_datetime(df_merged['month'],
format='%Y-%m')
df_merged.columns = [
'month', 'ronde_number', 'number_of_days', '_id', 'lot',
'ronde_done', 'percent', '_index', '_timestamp'
]
df_merged['ronde_done'] = df_merged['ronde_done'].astype(int)
df_merged['_id'] = df_merged['_id'].apply(
lambda x: 'lot' + str(int(lot)) + '_' + str(x))
logger.info("Storing " * 20)
# pte.pandas_to_elastic(es, df_merged)
es_helper.dataframe_to_elastic(es, df_merged)
records_number += len(df_merged)
endtime = time.time()
log_message(
"Compute monthly KPI105 (process biac_import_kpi105.py) finished. Duration: %d Records: %d."
% (endtime - starttime, records_number))
def compute_kpi103_monthly(start, end):
"""
Called three seconds after an import with the first day and last day of the previous import.
Parameters
----------
start
Date of the first imported record
end
Date of the last imported record
"""
starttime = time.time()
logger.info(start)
logger.info(end)
start = mkFirstOfMonth(start)
end = end.replace(day=calendar.monthrange(end.year, end.month)[1])
logger.info(start)
logger.info(end)
# df_kpi103 = etp.genericIntervalSearch(es, 'biac_kpi103', query='*', start=start, end=end,timestampfield="date")
df_kpi103 = es_helper.elastic_to_dataframe(es, 'biac_kpi103', query='*', start=start, end=end,timestampfield="date")
#logger.info(df_kpi103['date'].dt)
df_kpi103['month'] = pd.to_datetime(df_kpi103['date2']).dt.strftime('%Y-%m')
df_kpi103=df_kpi103.groupby(['month', 'ronde_number']).count()[['date']].rename(columns={'date': 'count'}).reset_index()
min_date = datetime.strptime(min(df_kpi103['month']), '%Y-%m')
max_date = datetime.strptime(max(df_kpi103['month']), '%Y-%m')
months = []
current = min_date
while current <= max_date:
months.append(current.strftime('%Y-%m'))
current += relativedelta(months=1)
df_months = pd.DataFrame(months)
df_months['join']=1
df_months.columns=['month', 'join']
rondes = [1, 2]
df_rondes = pd.DataFrame(rondes)
df_rondes['join']=1
df_rondes.columns=['ronde_number', 'join']
df_default=df_months.merge(df_rondes, left_on='join', right_on='join')
del df_default['join']
df_default['number_of_days'] = df_default['month'].apply(lambda x: get_days_already_passed(x))
df_default['_id'] = df_default['month'] + '_' + df_default['ronde_number'].astype(str)
df_kpi103['_id'] = df_kpi103['month'] + '_' + df_kpi103['ronde_number'].astype(str)
del df_kpi103['month']
del df_kpi103['ronde_number']
df_merged = df_default.merge(df_kpi103, left_on='_id', right_on='_id', how="outer")
df_merged = df_merged.fillna(0)
df_merged['percent'] = round(100*df_merged['count'] / df_merged['number_of_days'], 2)
df_merged['_index'] = 'biac_month_kpi103'
df_merged['_timestamp'] = pd.to_datetime(df_merged['month'], format='%Y-%m')
df_merged.columns=['month', 'ronde_number', 'number_of_days', '_id', 'ronde_done', 'percent', '_index', '_timestamp']
df_merged['ronde_done'] = df_merged['ronde_done'].astype(int)
es_helper.dataframe_to_elastic(es, df_merged)
endtime = time.time()
log_message("Compute monthly KPI103 (process biac_import_kpi103.py) finished. Duration: %d Records: %d." % (endtime-starttime, df_merged.shape[0]))
def messageReceived(destination, message, headers):
global es, basefile
records = 0
starttime = time.time()
logger.info("==> " * 10)
logger.info("Message Received %s" % destination)
logger.info(headers)
now = datetime.now()
local_timezone = tzlocal.get_localzone()
if "CamelSplitAttachmentId" in headers:
headers["file"] = headers["CamelSplitAttachmentId"]
if "file" in headers:
logger.info("File:%s" % headers["file"])
log_message("Import of file [%s] started." % headers["file"])
else:
headers["file"] = "From_Rest_API"
dfconfig = es_helper.elastic_to_dataframe(es, index="nyx_config_telephony")
dfconfig = dfconfig.set_index(["DnisNr"])
dfconfight = dfconfig.to_dict('index')
mess = base64.b64decode(message)
df = None
mesin = mess.decode("utf-8", "ignore")
# te=pd.read_fwf(StringIO(full)
## , names=["Date","Hour","Duration","A4","Code","A6","A7","Called","Caller","A10","Desk","A12","A13","A14","A15","A16","A17"]
# ,delim_whitespace=True, header=None,converters={"Date":str,"Hour":str,"Called":str,"Caller":str,"Desk":str})
# colspecs=[[0, 6],
# [6, 13],
# [13, 19],
# [20, 24],#A4
# [25, 27],#COde
# [27, 30],#A6
# [30, 37],#A7
# [37, 57],#CALLED
# [58, 88],#CALLER
# [89, 96],#Rings
# [97, 107],#DESK
# [108, 123],#A12
# [124, 133],#A13
# [134, 138],#A14
# [137, 143],#A15
# [144, 155],
# [156, 159]]
colspecs = [
[0, 6],
[6, 13],
[13, 19],
[20, 24], #A4
[25, 27], #COde
[27, 30], #A6
[30, 37], #A7
[37, 60], #CALLED
[61, 88], #CALLER
[89, 96], #Rings
[97, 107], #DESK
[108, 123], #A12
[124, 135], #A13
[137, 140], #A14
[139, 145], #A15
[146, 156],
[157, 161]
]
logger.info("Remove LIFE SIGNS")
mesin = mesin.split("\n")
mesin = [_.strip() for _ in mesin if "Cofely" not in _]
mesin = "\n".join(mesin)
logger.info("Panda read...")
te = pd.read_fwf(StringIO(mesin),
header=None,
colspecs=colspecs,
converters={
"A13": str,
"A4": str,
"A15": str,
"A16": str,
"Date": str,
"Hour": str,
"Called": str,
"Caller": str,
"Desk": str
},
names=[
"Date", "Hour", "Duration", "A4", "Code", "A6", "A7",
"Called", "Caller", "Rings", "Desk", "A12", "A13",
"A14", "A15", "A16", "A17"
])
logger.info("Done")
#te=te[89:] # IMPORTANT get rid of the base template file
te["Caller"] = te["Caller"].fillna("")
te["Code"] = te["Code"].fillna("")
te["Desk"] = te["Desk"].fillna(0)
te['InternalCalled1'] = te['Called'].str.replace(' ', '')
te['InternalCalled'] = (te['InternalCalled1'].str.len() < 6)
te['InternalCaller1'] = te['Caller'].str.replace(' ', '')
te['InternalCaller'] = (te['InternalCaller1'].str.len() < 6)
te['SolidusCalled'] = (te['InternalCalled1'].str.match("93901"))
te['Desk2'] = pd.to_numeric(te['Desk'],
errors='coerce',
downcast='integer')
te['DeskCaller'] = pd.to_numeric(te['Caller'],
errors='coerce',
downcast='integer')
del te['InternalCalled1']
del te['InternalCaller1']
calltype = []
for index, row in te.iterrows():
if (row["Caller"].find("91931") >= 0):
calltype.append("Test")
elif (row["Desk2"] > 76800) and (row["Desk2"] < 76810) and (
row["DeskCaller"] > 76900) and (row["DeskCaller"] < 76910):
calltype.append("Transfer")
elif (row["DeskCaller"] > 76800) and (row["DeskCaller"] < 76810) and (
row["Desk2"] > 76900) and (row["Desk2"] < 76910):
calltype.append("Transfer")
elif (row["Desk2"] > 76900) and (row["Desk2"] < 76910):
calltype.append("InDispa")
elif (row["Desk2"] > 76800) and (row["Desk2"] < 76810):
calltype.append("InDesk")
elif (row["SolidusCalled"]):
if (not (row["InternalCaller"])):
calltype.append("In")
else:
calltype.append("InOther")
else:
if (row["InternalCaller"]):
if (row["DeskCaller"] > 76900) and (row["DeskCaller"] < 76910):
calltype.append("OutDispa")
elif (row["DeskCaller"] > 76800) and (row["DeskCaller"] <
76810):
calltype.append("OutDesk")
else:
calltype.append("Out")
else:
calltype.append("Other")
te['CallType'] = calltype
te['DurationSecond'] = te['Duration'] % 100
te['DurationMinute'] = te['Duration'] / 100
te['DurationMinute2'] = te['DurationMinute'].astype(int)
te['Duration'] = te['DurationMinute2'] * 60 + te['DurationSecond']
#logger.info(te)
messagebody = ""
action = {}
te2 = te
del te2["A4"]
del te2["A6"]
#del te2["A10"]
#del te2["A11"]
del te2["A12"]
del te2["A13"]
del te2["A14"]
#del te2["A15"]
#del te2["A16"]
#del te2["A17"]
del te2["A7"]
te2["A15"].fillna(0, inplace=True)
te2["A16"].fillna(0, inplace=True)
te2["A17"].fillna(0, inplace=True)
te2["timestamp"] = te2["Date"] + te2["Hour"]
te2["Date2"] = pd.to_datetime(te2["timestamp"], format="%d%m%y%H%M%S")
te2["Date"] = pd.to_datetime(te2["timestamp"], format="%d%m%y%H%M%S")
te2["Date"] = te2['Date'].dt.tz_localize(tz='Europe/Paris', ambiguous=True)
del te2["timestamp"]
del te2["Hour"]
te2
for index, row in te2.iterrows():
obj = {}
#obj["@timestamp"]=int(row["Date"].timestamp())*1000
obj["@timestamp"] = row['Date'].isoformat()
obj["Duration"] = row["Duration"]
obj["Code"] = row["Code"].replace(' ', '')
obj["Called"] = str(row["Called"]).replace(' ', '')
try:
if int(obj["Called"]) in dfconfight:
obj["Client"] = dfconfight[int(obj["Called"])]["Name"]
except:
pass
obj["Caller"] = row["Caller"].replace(' ', '')
try:
obj["Desk"] = int(row["Desk"])
except:
obj["Desk"] = row["Desk"]
try:
obj["DeskCaller"] = int(row["DeskCaller"])
except:
obj["DeskCaller"] = row["DeskCaller"]
if str(obj["DeskCaller"]) == 'nan':
obj["DeskCaller"] = ""
obj["InternalCaller"] = row["InternalCaller"]
obj["InternalCalled"] = row["InternalCalled"]
obj["SolidusCalled"] = row["SolidusCalled"]
obj["CallType"] = row["CallType"]
obj["Rings"] = row["Rings"]
obj["A15"] = row["A15"]
obj["A16"] = row["A16"]
obj["A17"] = row["A17"]
if "nan" not in obj["Called"] + '_' + obj["Caller"]:
action["index"] = {
"_index":
"telephony",
"_type":
"doc",
"_id":
str(int(row["Date2"].timestamp()) * 1000) + '_' +
obj["Called"] + '_' + obj["Caller"]
}
messagebody += json.dumps(action) + "\r\n"
messagebody += json.dumps(obj) + "\r\n"
# if "NaN" in messagebody:
# print("BAD")
# pass
if (len(messagebody) > 50000):
logger.info("BULK")
try:
resbulk = es.bulk(messagebody)
#print(resbulk["errors"])
if resbulk["errors"]:
logger.error("BULK ERROR")
for item in resbulk["items"]:
for key in item:
if "error" in item[key]:
logger.error(item)
logger.info(messagebody)
except:
logger.error("Unable to bulk", exc_info=True)
logger.info(resbulk)
messagebody = ""
try:
if len(messagebody) > 0:
resbulk = es.bulk(messagebody)
except:
logger.error("Unable to bulk", exc_info=True)
logger.error(resbulk)
#print (messagebody)
logger.info("FINISHED")
endtime = time.time()
try:
log_message("Import of file [%s] finished. Duration: %d Records: %d." %
(headers["file"], (endtime - starttime), df.shape[0]))
except:
log_message("Import of file [%s] finished. Duration: %d." %
(headers["file"], (endtime - starttime)))
def messageReceived(destination, message, headers):
global es
starttime = time.time()
logger.info("==> " * 10)
logger.info("Message Received %s" % destination)
logger.info(headers)
local_timezone = tzlocal.get_localzone()
try:
logger.info(
'waiting 5 sec before doing the request to be sure date are correctly inserted by biac_import_kizeo.py'
)
time.sleep(5)
logger.info('message: ' + str(message))
obj = json.loads(message)
last_update = datetime.fromtimestamp(obj['end_ts'])
interval = define_interval(last_update)
start_dt = interval['dt_start']
end_dt = interval['dt_end']
logger.info(start_dt)
logger.info(end_dt)
df = es_helper.elastic_to_dataframe(es,
index="biac_kizeo",
scrollsize=1000,
start=start_dt,
end=end_dt,
datecolumns=["@timestamp"])
df['month'] = df['@timestamp'].dt.strftime('%Y-%m')
df_grouped = df.groupby(['lot', 'kpi', 'contract', 'screen_name', 'month']) \
.agg({'check_conform':'sum', 'check_no_conform':'sum', 'check_number':'sum', '@timestamp':'max'}) \
.reset_index()
df_grouped2 = df[df['contract']=='BACFIR'].groupby(['lot', 'kpi', 'contract', 'month']) \
.agg({'check_conform':'sum', 'check_no_conform':'sum', 'check_number':'sum', '@timestamp':'max'}) \
.reset_index()
df_grouped2['screen_name'] = 'BACFIR'
df_grouped = df_grouped.append(df_grouped2)
df_lot4 = pd.DataFrame()
try:
#handling lot4
df_lot4 = es_helper.elastic_to_dataframe(
es,
index="biac_spot_lot4",
query="kpi:302",
scrollsize=1000,
start=start_dt,
end=end_dt,
datecolumns=["@timestamp"])
except elasticsearch.NotFoundError:
logger.warn('Index biac_spot_lot4 does not exist')
if len(df_lot4) == 0:
obj = {
'lot': '4',
'kpi': 302,
'contract': 'DNBBA',
'screen_name': 'DNBBA',
'month': '2019-01',
'conform': 0,
'not_conform': 0,
'check': 0,
'@timestamp': datetime(2019, 1, 1, tzinfo=local_timezone),
}
df_lot4 = pd.DataFrame.from_dict({0: obj.values()},
orient='index',
columns=obj.keys())
else:
df_lot4['screen_name'] = 'BACDNB'
df_lot4['month'] = df_lot4['@timestamp'].dt.strftime('%Y-%m')
df_lot4['lot'] = df_lot4['lot'].astype(str)
df_grouped_lot4 = df_lot4.groupby(['lot', 'kpi', 'contract', 'screen_name', 'month']) \
.agg({'conform':'sum', 'not_conform':'sum', 'check':'sum', '@timestamp':'max'}) \
.reset_index()
df_grouped_lot4 = df_grouped_lot4.rename(
columns={
"conform": "check_conform",
"not_conform": "check_no_conform",
"check": "check_number"
})
df_grouped = df_grouped.append(df_grouped_lot4)
df_grouped['_index'] = 'biac_month_2_kizeo'
df_grouped['_id'] = df_grouped.apply(lambda row: row['lot'] + '_' + str(row['kpi']) + '_' + \
row['contract'].lower() + '_' + row['screen_name'].lower() + '_' + \
str(int(row['@timestamp'].timestamp())), axis=1)
df_grouped = df_grouped.rename(columns={"@timestamp": "last_update"})
df_grouped['_id'] = df_grouped['_id'].str.replace(' ', '_')
df_grouped['_id'] = df_grouped['_id'].str.replace('/', '_')
df_grouped['percentage_conform'] = round(
100 * (df_grouped['check_conform'] / df_grouped['check_number']),
2).fillna(100)
df_grouped['percentage_conform'] = df_grouped[
'percentage_conform'].apply(lambda x:
('%f' % x).rstrip('0').rstrip('.'))
df_grouped['percentage_no_conform'] = round(
100 *
(df_grouped['check_no_conform'] / df_grouped['check_number']),
2).fillna(0)
df_grouped['percentage_no_conform'] = df_grouped[
'percentage_no_conform'].apply(lambda x:
('%f' % x).rstrip('0').rstrip('.'))
df_grouped['percentage_conform'] = df_grouped[
'percentage_conform'].fillna(100)
df_grouped['percentage_no_conform'] = df_grouped[
'percentage_no_conform'].fillna(0)
logger.info(df_grouped)
es_helper.dataframe_to_elastic(es, df_grouped)
except Exception as e:
endtime = time.time()
logger.error(e, exc_info=True)
log_message("Process month Kizeo failed. Duration: %d Exception: %s." %
((endtime - starttime), str(e)))
endtime = time.time()
try:
log_message("Process month Kizeo finished. Duration: %d Records: %d." %
((endtime - starttime), df_grouped.shape[0]))
except:
log_message("Process month Kizeo finished. Duration: %d." %
((endtime - starttime)))
logger.info("<== " * 10)
def compute502barchart_v3(reporttype):
print('==============> KPI502 bar graph')
cur_active = es_helper.elastic_to_dataframe(
es, "biac_kpi502", "active:1 AND key: \"" + reporttype + "\"")
startdate = datetime.strptime(cur_active["filedate"].iloc[0], "%Y-%m")
start = startdate #-timedelta(days=2)
start_overdue_4 = start + relativedelta(months=-13)
start_overdue_4 = str(start_overdue_4.year) + "-" + (str(
start_overdue_4.month)).zfill(2)
start_overdue_5 = start + relativedelta(months=-7)
start_overdue_5 = str(start_overdue_5.year) + "-" + (str(
start_overdue_5.month)).zfill(2)
print(start_overdue_5)
# startminusonemonth=start-timedelta(days=32)
startminusonemonth = start - relativedelta(months=1)
filedate = str(start.year) + "-" + (str(start.month)).zfill(2)
filedateminusonemonth = str(startminusonemonth.year) + "-" + (str(
startminusonemonth.month)).zfill(2)
print("FileDate: %s" % (filedate))
queryadd = " AND key: \"" + reporttype.replace("Lot4 (DNB)",
"Lot4 (BACDNB)") + "\""
if "All" in reporttype:
queryadd = " AND Lot: \"Lot 2\""
print(queryadd)
query = '(ShortStatusFU: Actievereist OR ValueCount:0) AND filedate:' + filedate + queryadd
queryminusonemonth = '(ShortStatusFU: Actievereist OR ValueCount:0) AND filedate:' + filedateminusonemonth + queryadd
print(query)
print(queryminusonemonth)
cur_df = es_helper.elastic_to_dataframe(es, "biac_kpi502", query)
prev_df = es_helper.elastic_to_dataframe(es, "biac_kpi502",
queryminusonemonth)
cur_df_4 = cur_df[cur_df["ShortStatus"] == 4].copy()
cur_df_4_gr = cur_df_4.groupby("MonthFU").agg({"ValueCount": ["sum"]})
cur_df_4_gr.columns = ["value"]
prev_df_4 = prev_df[prev_df["ShortStatus"] == 4].copy()
prev_df_4_gr = prev_df_4.groupby("MonthFU").agg({"ValueCount": ["sum"]})
prev_df_4_gr.columns = ["value"]
merge_4_gr = cur_df_4_gr.merge(prev_df_4_gr,
how="left",
left_index=True,
right_index=True)
merge_4_gr.columns = ["Cur", "Prev"]
merge_4_gr.fillna(0, inplace=True)
merge_4_gr["Good"] = merge_4_gr["Prev"] - merge_4_gr["Cur"]
merge_4_gr["Good"] = merge_4_gr["Good"].apply(lambda x: 0 if x < 0 else x)
merge_4_gr["Month"] = merge_4_gr.index
merge_4_gr["Month"] = merge_4_gr["Month"].apply(lambda x: " " + str(x)
if x == "OVERDUE" else x)
merge_4_gr = merge_4_gr.sort_values("Month",
ascending=False).reset_index(drop=True)
merge_4_gr.loc[merge_4_gr["Month"].str.contains("OVERDUE") > 0, "Good"] = 0
merge_4_gr["Total"] = merge_4_gr["Cur"] + merge_4_gr["Good"]
cur_df_4[(cur_df_4["MonthFU"] == "OVERDUE")
& (cur_df_4["Month"] == start_overdue_4)]
newoverdue_4 = cur_df_4[(cur_df_4["MonthFU"] == "OVERDUE")
& (cur_df_4["Month_"] == start_overdue_4)].shape[0]
merge_4_gr["NewOverdue"] = 0
merge_4_gr.loc[merge_4_gr["Month"].str.contains("OVERDUE"),
"NewOverdue"] = newoverdue_4
merge_4_gr.loc[
merge_4_gr["Month"].str.contains("OVERDUE"),
"Cur"] = merge_4_gr.loc[merge_4_gr["Month"].str.contains("OVERDUE"),
"Cur"] - newoverdue_4
cur_df_5 = cur_df[cur_df["ShortStatus"] == 5].copy()
cur_df_5_gr = cur_df_5.groupby("MonthFU").agg({"ValueCount": ["sum"]})
cur_df_5_gr.columns = ["value"]
prev_df_5 = prev_df[prev_df["ShortStatus"] == 5].copy()
prev_df_5_gr = prev_df_5.groupby("MonthFU").agg({"ValueCount": ["sum"]})
prev_df_5_gr.columns = ["value"]
merge_5_gr = cur_df_5_gr.merge(prev_df_5_gr,
how="left",
left_index=True,
right_index=True)
merge_5_gr.columns = ["Cur", "Prev"]
merge_5_gr.fillna(0, inplace=True)
merge_5_gr["Good"] = merge_5_gr["Prev"] - merge_5_gr["Cur"]
merge_5_gr["Good"] = merge_5_gr["Good"].apply(lambda x: 0 if x < 0 else x)
merge_5_gr["Month"] = merge_5_gr.index
merge_5_gr["Month"] = merge_5_gr["Month"].apply(lambda x: " " + str(x)
if x == "OVERDUE" else x)
merge_5_gr = merge_5_gr.sort_values("Month",
ascending=False).reset_index(drop=True)
merge_5_gr.loc[merge_5_gr["Month"].str.contains("OVERDUE") > 0, "Good"] = 0
merge_5_gr["Total"] = merge_5_gr["Cur"] + merge_5_gr["Good"]
newoverdue_5 = cur_df_5[(cur_df_5["MonthFU"] == "OVERDUE")
& (cur_df_5["Month_"] == start_overdue_5)].shape[0]
merge_5_gr["NewOverdue"] = 0
merge_5_gr.loc[merge_5_gr["Month"].str.contains("OVERDUE"),
"NewOverdue"] = newoverdue_5
merge_5_gr.loc[
merge_5_gr["Month"].str.contains("OVERDUE"),
"Cur"] = merge_5_gr.loc[merge_5_gr["Month"].str.contains("OVERDUE"),
"Cur"] - newoverdue_5
generate_kibana_dash(merge_4_gr, 4, reporttype)
generate_kibana_dash(merge_5_gr, 5, reporttype)
def messageReceived(destination, message, headers):
global es, goodmonth
records = 0
starttime = time.time()
logger.info("==> " * 10)
logger.info("Message Received %s" % destination)
if destination == "/topic/RECOMPUTE_502":
recomputeKPI502()
return
logger.info(headers)
filepath = ""
file = ""
if "CamelSplitAttachmentId" in headers:
headers["file"] = headers["CamelSplitAttachmentId"]
if "file" in headers:
logger.info("File:%s" % headers["file"])
log_message("Import of file [%s] started." % headers["file"])
file = headers["file"]
#file= './tmp/Safety Register - KPI502 - Lots_1_2_3 - 2019-04.xlsx'
#file="Safety Register - KPI502 - Lots_1_2_3 - 2019-0.xlsx"
logger.info("FILE :" + file)
filename = file
logger.info("FILE NAME :" + filename)
filedate = file[1:].split(".")[0][-7:]
goodmonth = filedate.split("-")[1] + "-" + filedate.split("-")[0]
logger.info("MONTH(BEF) :" + goodmonth)
gm = datetime.strptime(goodmonth, "%m-%Y")
gm = gm - relativedelta(months=1)
goodmonth = datetime.strftime(gm, "%m-%Y")
xlsbytes = base64.b64decode(message)
f = open('./tmp/excel.xlsx', 'wb')
f.write(xlsbytes)
f.close()
filename = './tmp/excel.xlsx'
# exit()
#filename = './tmp/KPI502 - Lot2- 2019-01.xlsx'
#file="KPI502 - Lot2- 2019-01.xlsx"
#filename = './tmp/KPI502 - Lot2 - 2019-03.xlsx'
# logger.info("MONTH(AFT) :"+goodmonth)
try:
logger.info("Opening XLS...")
dfdata = pd.DataFrame()
for sheet in ["Lot 1", "Lot 2", "Lot 3", "Lot 4"]:
logger.info(">>>>>>>>>> LOADING:" + sheet)
try:
dfd = pd.read_excel(filename,
sheet_name=sheet,
skiprows=2,
index_col=0)
except:
logger.error("Unable to read sheet.")
continue
dfd["Sheet"] = sheet
#logger.info(dfd.describe())
dfdata = dfdata.append(dfd) #,ignore_index = True)
#break
#dfdata=dfdata.reset_index()
logger.info("Done.")
#dfdata = dfdata.drop('Unnamed: 0', axis=1)
dfdata = dfdata.reset_index()
del dfdata["index"]
#print(dfdata.columns)
logger.info(dfdata.columns)
#newcols=['Month','SRid','Status','Opm. number','Definition','Building','Floor','Place','Technic','Sub-technic','materials','AssetCode','Device','BACid','Frequency','Control','Report','Report Date','Reference Date','Reference Year','Reference source date','Last shipment','Repeat','Point of interest','KPI timing','GroupNum','Type','FB Name','FB date','FB','Orig label','Orig Definition','Control organism','To','Cc 1','Cc 2','Cc 3','Cc 4','Cc 5','Cc 6','Cc 7','Cc 8','Cc 9','BAC Dept','BAC Sub Dept','BAC Service','Group','Contractor','Lot','KPI Type','ShortStatus','ShortStatusFU','ConcatShortStatus&OrigStatus','LongStatus','Classification nr (O/I)','Classification (O/I)','Show graph','Report link','Status (M-1)','Report Date (M-1)','FB Date (M-1)','Deleted vs M-1','MonthFU','Sheet']
newcols = [
'Month', 'SRid', 'Status', 'Opm. number', 'Definition', 'Building',
'Floor', 'Place', 'Technic', 'Sub-technic', 'materials',
'AssetCode', 'Device', 'BACid', 'Frequency', 'Control', 'Report',
'Report Date', 'Reference Date', 'Reference Year',
'Reference source date', 'Last shipment', 'Repeat',
'Point of interest', 'KPI timing', 'GroupNum', 'Type', 'FB Name',
'FB date', 'FB', 'Orig label', 'Orig Definition',
'Control organism', 'To', 'Cc 1', 'Cc 2', 'Cc 3', 'Cc 4', 'Cc 5',
'Cc 6', 'Cc 7', 'Cc 8', 'Cc 9', 'BAC Dept', 'BAC Sub Dept',
'BAC Service', 'Group', 'Contractor', 'Lot', 'KPI Type',
'ShortStatus', 'ShortStatusFU', 'ConcatShortStatus&OrigStatus',
'LongStatus', 'Classification nr (O/I)', 'Classification (O/I)',
'Show graph', 'Report link', 'Status (M-1)', 'Report Date (M-1)',
'FB Date (M-1)', 'Deleted vs M-1', "KPI Type Nr", "CheckArchived",
'Sheet'
]
logger.info(newcols)
dfdata.columns = newcols
dfdata["KPI Type Nr"] = 502
dfdata["MonthFU"] = dfdata.apply(computeOverdue, axis=1)
dfdata2 = dfdata.reset_index(drop=True)
dfdata2.fillna('', inplace=True)
dfdata2 = dfdata2.drop(dfdata2[dfdata2["Month"] == ''].index)
dfdata2['_index'] = "biac_kpi502"
#dfdata2['_timestamp'] = dfdata2['Reference Date'].apply(lambda x: getTimestamp(x)*1000)
#dfdata2['_timestamp'] = dfdata2['Reference Date'].apply(lambda x: int(x.timestamp()*1000))
dfdata2['_timestamp'] = dfdata2.apply(lambda row: fixDate(row), axis=1)
dfdata2['_id'] = dfdata2.apply(
lambda row: get_id(row['Month'], row['SRid']), axis=1)
dfdata2['_id'] = dfdata2['_id'].apply(lambda x: goodmonth + "-" + x)
logger.info(dfdata2)
filedate = file[1:].split(".")[0][-7:]
dfdata2["filedate"] = filedate
dfdata2['key'] = dfdata2.apply(computeReport, axis=1)
dfdata2["ShortStatus"] = dfdata2["LongStatus"].apply(
computeShortStatus)
logger.info(len(dfdata2))
dfdata2 = dfdata2[(dfdata2["ShortStatus"] == 4) |
(dfdata2["ShortStatus"] == 5)]
logger.info(len(dfdata2))
dfdata2.drop_duplicates('_id', inplace=True)
dfdata2["Month_"] = dfdata2["Month"]
logger.info("BEFORE CLEANING")
logger.info(dfdata2.shape)
for x in [str(x) for x in range(2010, 2018)]:
dfdata2 = dfdata2[~dfdata2["Month_"].str.contains(x)]
dfdata2 = dfdata2[~dfdata2["Month_"].
isin(["2018-01", "2018-02", "2018-03", "2018-04"])]
logger.info("AFTER CLEANING")
logger.info(dfdata2.shape)
dfdata2["Month"] = goodmonth
dfdata2["ValueCount"] = 1
logger.info(filedate)
#res4=compute_previous_months(int(filedate.split("-")[1]),int(filedate.split("-")[0]),12,skip=0)
res4 = compute_previous_months(int(goodmonth.split("-")[0]),
int(goodmonth.split("-")[1]),
12,
skip=0)
res4lot4 = compute_previous_months(int(goodmonth.split("-")[0]),
int(goodmonth.split("-")[1]),
6,
skip=0)
res4table = []
for key in dfdata2['key'].unique():
print("===>" * 30)
print(key)
if key.startswith("Lot4"):
res4touse = res4lot4
print("LOT4" * 100)
else:
res4touse = res4
for rec in res4touse:
res4table.append({
"Month":
goodmonth,
"MonthFU":
rec,
"ShortStatus":
4,
"Status":
4,
"ValueCount":
0,
"_id":
goodmonth + rec + "-S4-" + str(hash(key)),
"_index":
"biac_kpi502",
"key":
key,
"filedate":
filedate
})
res4tabledf = pd.DataFrame(res4table)
#print(res4tabledf)
es_helper.dataframe_to_elastic(es, res4tabledf)
#A/0
#res5=compute_previous_months(int(filedate.split("-")[1]),int(filedate.split("-")[0]),6,skip=0)
res5 = compute_previous_months(int(goodmonth.split("-")[0]),
int(goodmonth.split("-")[1]),
6,
skip=0)
res5lot4 = compute_previous_months(int(goodmonth.split("-")[0]),
int(goodmonth.split("-")[1]),
3,
skip=0)
res5table = []
for key in dfdata2['key'].unique():
if key.startswith("Lot4"):
res5touse = res5lot4
print("LOT4" * 100)
else:
res5touse = res5
for rec in res5touse:
res5table.append({
"Month":
goodmonth,
"MonthFU":
rec,
"ShortStatus":
5,
"Status":
5,
"ValueCount":
0,
"_id":
goodmonth + rec + "-S5-" + str(hash(key)),
"_index":
"biac_kpi502",
"key":
key,
"filedate":
filedate
})
# DELETE OLD DATA
deletequery = {
"query": {
"bool": {
"must": [{
"query_string": {
"query": "Month: \"" + goodmonth + "\""
}
}]
}
}
}
logger.info(
">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Deleting ORIGINALS records")
logger.info(deletequery)
try:
resdelete = es.delete_by_query(body=deletequery,
index="biac_kpi502")
logger.info(resdelete)
except Exception as e3:
logger.info(e3)
logger.info("Unable to delete records.")
logger.info("Waiting for deletion to finish")
time.sleep(3)
# WRITE DATA
res5tabledf = pd.DataFrame(res5table)
es_helper.dataframe_to_elastic(es, res5tabledf)
es_helper.dataframe_to_elastic(es, dfdata2)
## NOW COMPUTE MONTH
logger.info("Waiting for records to be written...")
time.sleep(3)
deletequery = {
"query": {
"bool": {
"must": [{
"query_string": {
"query": "Month: \"" + goodmonth + "\""
}
}]
}
}
}
logger.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Deleting records")
logger.info(deletequery)
try:
resdelete = es.delete_by_query(body=deletequery,
index="biac_month_kpi502")
logger.info(resdelete)
except Exception as e3:
logger.info(e3)
logger.info("Unable to delete records.")
logger.info("Waiting for deletion to finish")
time.sleep(3)
goodmonth_dt = datetime.strptime(goodmonth, '%m-%Y')
df_kpi502 = es_helper.elastic_to_dataframe(
es,
"biac_kpi502",
query='ShortStatusFU: Actievereist AND Month: ' + goodmonth)
df_kpi502_4 = df_kpi502[df_kpi502["ShortStatus"] == 4]
df_kpi502_4_overdue = df_kpi502_4[df_kpi502_4["MonthFU"].str.contains(
"OVERDUE")].shape[0]
logger.info("4 overdue:%d" % (df_kpi502_4_overdue))
kpi502_4 = df_kpi502_4_overdue / df_kpi502_4.shape[0]
kpi502_4
df_kpi502_5 = df_kpi502[df_kpi502["ShortStatus"] == 5]
df_kpi502_5_overdue = df_kpi502_5[df_kpi502_5["MonthFU"].str.contains(
"OVERDUE")].shape[0]
kpi502_5 = df_kpi502_5_overdue / df_kpi502_5.shape[0]
logger.info("5 overdue:%d" % (df_kpi502_5_overdue))
#df_kpi502_4_overdue
df_kpi502.shape[0]
df_kpi502_4_obj = {
"total": df_kpi502_4.shape[0],
"overdue": df_kpi502_4_overdue
}
df_kpi502_5_obj = {
"total": df_kpi502_5.shape[0],
"overdue": df_kpi502_5_overdue
}
df_kpi502 = dfdata2.copy()
recs = []
recsfire = []
recsokko = []
for key in df_kpi502.key.unique():
#logger.info(key)
#goodmonth=filedate.split('-')[1]+"-"+filedate.split('-')[0]
newrec = {
"key": key,
"type": "summary",
"_id": goodmonth + "_" + key,
"_index": "biac_month_kpi502",
"filedate": filedate,
"Month": goodmonth
}
if key.startswith("Lot4"):
limitmonth4 = (goodmonth_dt -
relativedelta(months=6)).strftime('%Y-%m')
limitmonth5 = (goodmonth_dt -
relativedelta(months=3)).strftime('%Y-%m')
else:
limitmonth4 = (goodmonth_dt -
relativedelta(months=12)).strftime('%Y-%m')
limitmonth5 = (goodmonth_dt -
relativedelta(months=6)).strftime('%Y-%m')
logger.info(">>>>>KEY:" + key)
logger.info(">>>>>LIMIT4:" + limitmonth4)
logger.info(">>>>>LIMIT5:" + limitmonth5)
df_kpi502_4 = df_kpi502[(df_kpi502["ShortStatus"] == 4)
& (df_kpi502["key"] == key)]
newoverdues_4 = 0
oldoverdues_4 = 0
for index, row in df_kpi502_4.iterrows():
newrec_okko = {
"key": key,
"type": "stat",
"_index": "biac_month_kpi502",
"filedate": filedate,
"Month": goodmonth
}
if "OVERDUE" in row["MonthFU"]:
if row["Month_"] == limitmonth4:
newrec_okko["sub_type"] = "new_overdue_remarks"
newoverdues_4 += 1
else:
newrec_okko["sub_type"] = "overdue_remarks"
oldoverdues_4 += 1
else:
newrec_okko["sub_type"] = "ok_remarks"
newrec_okko["@timestamp"] = datetime.utcnow().isoformat()
recsokko.append(newrec_okko)
logger.info(">>>>>" + str(df_kpi502_4[df_kpi502_4["Month_"] ==
limitmonth4].shape[0]))
#df_kpi502_4_overdue=df_kpi502_4[df_kpi502_4["MonthFU"].str.contains("OVERDUE")].shape[0]
df_kpi502_4_overdue = newoverdues_4
kpi502_4_percentage = 1
if df_kpi502_4.shape[0] - oldoverdues_4 > 0:
kpi502_4_percentage = (df_kpi502_4.shape[0] - oldoverdues_4 -
newoverdues_4) / (df_kpi502_4.shape[0] -
oldoverdues_4)
else:
kpi502_4_percentage = 1
df_kpi502_4_obj = {
"total": df_kpi502_4.shape[0],
"overdue": df_kpi502_4_overdue,
"percentage": round(kpi502_4_percentage * 100, 2)
}
newrec["Total_Remarks"] = df_kpi502_4.shape[0]
newrec["Overdues_Remarks"] = df_kpi502_4_overdue
newrec["Old_Overdues_Remarks"] = oldoverdues_4
newrec["New_Overdues_Remarks"] = newoverdues_4
newrec["KPI_Remarks"] = round(kpi502_4_percentage * 100, 2)
df_kpi502_5 = df_kpi502[(df_kpi502["ShortStatus"] == 5)
& (df_kpi502["key"] == key)]
newoverdues_5 = 0
oldoverdues_5 = 0
for index, row in df_kpi502_5.iterrows():
newrec_okko = {
"key": key,
"type": "stat",
"_index": "biac_month_kpi502",
"filedate": filedate,
"Month": goodmonth
}
if "OVERDUE" in row["MonthFU"]:
if row["Month_"] == limitmonth5:
newrec_okko["sub_type"] = "new_overdue_breaches"
newoverdues_5 += 1
else:
newrec_okko["sub_type"] = "overdue_breaches"
oldoverdues_5 += 1
else:
newrec_okko["sub_type"] = "ok_breaches"
newrec_okko["@timestamp"] = datetime.utcnow().isoformat()
recsokko.append(newrec_okko)
#df_kpi502_5_overdue=df_kpi502_5[df_kpi502_5["MonthFU"].str.contains("OVERDUE")].shape[0]
df_kpi502_5_overdue = newoverdues_5
kpi502_5_percentage = 1
if df_kpi502_5.shape[0] - oldoverdues_5 > 0:
kpi502_5_percentage = (df_kpi502_5.shape[0] - oldoverdues_5 -
newoverdues_5) / (df_kpi502_5.shape[0] -
oldoverdues_5)
else:
kpi502_5_percentage = 1
# logger.info(df_kpi502_5)
newrec["Total_Breaches"] = df_kpi502_5.shape[0]
newrec["Overdues_Breaches"] = df_kpi502_5_overdue
newrec["KPI_Breaches"] = round(kpi502_5_percentage * 100, 2)
newrec["Old_Overdues_Breaches"] = oldoverdues_5
newrec["New_Overdues_Breaches"] = newoverdues_5
newrec["Total"] = df_kpi502_4.shape[0] + df_kpi502_5.shape[0]
newrec["Overdues"] = df_kpi502_4_overdue + df_kpi502_5_overdue
percentage = 0
if df_kpi502_4.shape[0] + df_kpi502_5.shape[0] > 0:
percentage = (df_kpi502_4_overdue + df_kpi502_5_overdue) / (
df_kpi502_4.shape[0] + df_kpi502_5.shape[0])
newrec["KPI"] = round(percentage * 100, 2)
if "BACFIR" not in key:
recs.append(newrec)
else:
recsfire.append(newrec)
pdfire = pd.DataFrame(recsfire)
firerec = json.loads(pdfire.sum().to_json())
firerec["filedate"] = filedate
firerec["Month"] = goodmonth
firerec["type"] = "summary"
firerec["_index"] = "biac_month_kpi502"
firerec["key"] = "ALL (BACFIR)"
if firerec["Total_Breaches"] - firerec["Old_Overdues_Breaches"] > 0:
firerec["KPI_Breaches"] = round(
100 *
(firerec["Total_Breaches"] - firerec["Old_Overdues_Breaches"] -
firerec["New_Overdues_Breaches"]) /
(firerec["Total_Breaches"] - firerec["Old_Overdues_Breaches"]),
2)
else:
firerec["KPI_Breaches"] = 100
if firerec["Total_Remarks"] > 0:
firerec["KPI_Remarks"] = round(
100 *
(firerec["Total_Remarks"] - firerec["Old_Overdues_Remarks"] -
firerec["New_Overdues_Remarks"]) /
(firerec["Total_Remarks"] - firerec["Old_Overdues_Remarks"]),
2)
else:
firerec["KPI_Remarks"] = 100
df_month_kpi502 = pd.DataFrame(recs + [firerec])
es_helper.dataframe_to_elastic(es, df_month_kpi502)
df_month_kpi502_stats = pd.DataFrame(recsokko)
es_helper.dataframe_to_elastic(es, df_month_kpi502_stats)
except Exception as e:
endtime = time.time()
logger.error(e, exc_info=True)
log_message("Import of file [%s] failed. Duration: %d Exception: %s." %
(file, (endtime - starttime), str(e)))
logger.info(">>>>Set Active Fields")
logger.info("Reset active records ")
logger.info("=====================")
time.sleep(3)
updatequery = {
"script": {
"inline": "ctx._source.active=0",
"lang": "painless"
},
'query': {
'bool': {
'must': [{
'query_string': {
'query': '*'
}
}]
}
}
}
logger.info("*=" * 30)
logger.info(json.dumps(updatequery))
try:
resupdate = es.update_by_query(body=updatequery, index="biac_kpi502")
logger.info(resupdate)
except Exception as e3:
logger.error(e3)
logger.error("Unable to update records biac_month_kpi502.")
try:
resupdate = es.update_by_query(body=updatequery,
index="biac_month_kpi502")
logger.info(resupdate)
except Exception as e3:
logger.error(e3)
logger.error("Unable to update records biac_month_kpi502.")
# Activating current month
logger.info("Waiting for update...")
time.sleep(3)
logger.info("Done")
updatequery = {
"script": {
"inline": "ctx._source.active=1",
"lang": "painless"
},
'query': {
'bool': {
'must': [{
'query_string': {
'query': 'Month: "' + goodmonth + '"'
}
}]
}
}
}
logger.info("Update active records")
logger.info(updatequery)
try:
resupdate = es.update_by_query(body=updatequery, index="biac_kpi502")
logger.info(resupdate)
except Exception as e3:
logger.error(e3)
logger.error("Unable to update records biac_month_kpi502.")
try:
resupdate = es.update_by_query(body=updatequery,
index="biac_month_kpi502")
logger.info(resupdate)
except Exception as e3:
logger.error(e3)
logger.error("Unable to update records biac_month_kpi502.")
recomputeKPI502()
endtime = time.time()
try:
log_message("Import of file [%s] finished. Duration: %d Records: %d." %
(file, (endtime - starttime), df.shape[0]))
except:
log_message("Import of file [%s] finished. Duration: %d." %
(file, (endtime - starttime)))
logger.info("<== " * 10)
def update_kpi101_monthly(es,
month,
number_of_call_1=-1,
number_of_call_2=-1,
number_of_call_3=-1):
#logger.info('update_kpi101_monthly date: '+str(month))
start_dt = mkFirstOfMonth(month)
end_dt = mkLastOfMonth(month)
logger.info('**' * 100)
logger.info(start_dt)
logger.info(end_dt)
logger.info(number_of_call_1)
logger.info(number_of_call_2)
logger.info(number_of_call_3)
df = es_helper.elastic_to_dataframe(es,
index="biac_kpi101_call*",
query='*',
start=start_dt,
end=end_dt,
timestampfield='datetime')
df_group = None
if len(df) > 0:
df_group = df.groupby('lot').agg({'_id': 'count'})
obj = {
'not_timely_answer': 0,
'not_timely_answer_1': 0,
'not_timely_answer_2': 0,
'not_timely_answer_3': 0,
'number_of_call_1': 0,
'number_of_call_2': 0,
'number_of_call_3': 0,
'percentage': 0,
'percentage_1': 0,
'percentage_2': 0,
'percentage_3': 0,
}
try:
obj['not_timely_answer_1'] = df_group.loc[1, '_id']
except:
logger.info('no value for 1')
try:
obj['not_timely_answer_2'] = df_group.loc[2, '_id']
except:
logger.info('no value for 2')
try:
obj['not_timely_answer_3'] = df_group.loc[3, '_id']
except:
logger.info('no value for 3')
df_month = None
if number_of_call_1 == -1 or number_of_call_2 == -1 or number_of_call_3 == -1:
df_month = es_helper.elastic_to_dataframe(es,
index="biac_kpi101_monthly*",
query='*',
start=start_dt,
end=end_dt,
timestampfield='datetime')
if number_of_call_1 == -1:
try:
obj['number_of_call_1'] = df_month.iloc[0]['number_of_call_1']
except:
obj['number_of_call_1'] = 0
else:
obj['number_of_call_1'] = number_of_call_1
if number_of_call_2 == -1:
try:
obj['number_of_call_2'] = df_month.iloc[0]['number_of_call_2']
except:
obj['number_of_call_2'] = 0
else:
obj['number_of_call_2'] = number_of_call_2
if number_of_call_3 == -1:
try:
obj['number_of_call_3'] = df_month.iloc[0]['number_of_call_3']
except:
obj['number_of_call_3'] = 0
else:
obj['number_of_call_3'] = number_of_call_3
obj['not_timely_answer'] = obj['not_timely_answer_1'] + obj[
'not_timely_answer_2'] + obj['not_timely_answer_3']
obj['number_of_call'] = obj['number_of_call_1'] + obj[
'number_of_call_2'] + obj['number_of_call_3']
if obj['number_of_call'] != 0:
obj['percentage'] = round(
((obj['number_of_call'] - obj['not_timely_answer']) /
obj['number_of_call']) * 100, 2)
if obj['number_of_call_1'] != 0:
obj['percentage_1'] = round(
((obj['number_of_call_1'] - obj['not_timely_answer_1']) /
obj['number_of_call_1']) * 100, 2)
if obj['number_of_call_2'] != 0:
obj['percentage_2'] = round(
((obj['number_of_call_2'] - obj['not_timely_answer_2']) /
obj['number_of_call_2']) * 100, 2)
if obj['number_of_call_3'] != 0:
obj['percentage_3'] = round(
((obj['number_of_call_3'] - obj['not_timely_answer_3']) /
obj['number_of_call_3']) * 100, 2)
local_timezone = tzlocal.get_localzone()
obj['datetime'] = local_timezone.localize(start_dt)
_id = int(obj['datetime'].timestamp()) * 1000
res = es.index(index="biac_kpi101_monthly",
doc_type='doc',
id=_id,
body=json.dumps(obj, cls=DateTimeEncoder))
logger.info(res)
return obj
def compute305():
logger.info(">>> COMPUTE 305 STARTED")
global goodmonth
time.sleep(3)
orgdf = es_helper.elastic_to_dataframe(
es,
"biac_kpi305",
query="*",
start=datetime.now() - timedelta(days=365),
end=datetime.now() + timedelta(days=365),
sort=None,
timestampfield="CheckMonth")
# orgdf=etp.genericIntervalSearch(es,"biac_kpi305",query="*",doctype="doc",start=datetime.now()-timedelta(days=365),end=datetime.now()+timedelta(days=365),sort=None,timestampfield="CheckMonth")
months = orgdf["Month"].unique()
keys = orgdf["key"].unique()
logger.info(keys)
alllot = {}
for month in months:
for key in keys:
regex = r"\(([A-Z]*)\)"
result = re.search(regex, key, re.MULTILINE)
if result:
lot = result.group(1)
else:
lot = "NA"
if not lot + month in alllot:
alllot[lot + month] = {
"Positive": 0,
"Negative": 0,
"Month": month,
"key": lot
}
logger.info("Loading " + month + " Key " + key)
onekeydf = orgdf[(orgdf["key"] == key) & (orgdf["Month"] == month)]
good = onekeydf["CountPositives"].sum()
bad = onekeydf["CountNC"].sum()
alllot[lot + month]["Positive"] += int(good)
alllot[lot + month]["Negative"] += int(bad)
logger.info("==> %d %d" % (bad, good))
# alllot[key+month]={"Positive":int(good),"Negative":int(bad),"Month":month,"key":key}
bulkbody = ""
es_index = "biac_month_kpi305"
for key in alllot:
action = {}
action["index"] = {
"_index":
es_index,
"_type":
"doc",
"_id": (alllot[key]["key"] + alllot[key]["Month"]).replace(
"(", "").replace(")", "").replace(" ", "").lower()
}
alllot[key][
"Total"] = alllot[key]["Positive"] + alllot[key]["Negative"]
if alllot[key]["Total"] > 0:
alllot[key]["KPI"] = round(
alllot[key]["Positive"] * 100 / alllot[key]["Total"], 2)
else:
alllot[key]["KPI"] = 100
newrec = json.dumps(alllot[key])
bulkbody += json.dumps(action) + "\r\n"
bulkbody += newrec + "\r\n"
res = es.bulk(bulkbody)
if res["errors"]:
logger.error("Error in bulk")
logger.info(res)
logger.info(">>>>Set Active Fields")
logger.info("Reset active records ")
logger.info("=====================")
time.sleep(3)
updatequery = {
"script": {
"inline": "ctx._source.active=0",
"lang": "painless"
},
'query': {
'bool': {
'must': [{
'query_string': {
'query': '*'
}
}]
}
}
}
logger.info("*=" * 30)
logger.info(json.dumps(updatequery))
try:
resupdate = es.update_by_query(body=updatequery, index="biac_kpi305")
logger.info(resupdate)
except Exception as e3:
logger.error(e3)
logger.error("Unable to update records biac_month_kpi305.")
try:
resupdate = es.update_by_query(body=updatequery,
index="biac_month_kpi305")
logger.info(resupdate)
except Exception as e3:
logger.error(e3)
logger.error("Unable to update records biac_month_kpi305.")
# Activating current month
logger.info("Waiting for update...")
time.sleep(3)
logger.info("Done")
updatequery = {
"script": {
"inline": "ctx._source.active=1",
"lang": "painless"
},
'query': {
'bool': {
'must': [{
'query_string': {
'query': 'Month: "' + goodmonth + '"'
}
}]
}
}
}
logger.info("Update active records")
logger.info(updatequery)
try:
resupdate = es.update_by_query(body=updatequery, index="biac_kpi305")
logger.info(resupdate)
except Exception as e3:
logger.error(e3)
logger.error("Unable to update records biac_month_kpi305.")
try:
resupdate = es.update_by_query(body=updatequery,
index="biac_month_kpi305")
logger.info(resupdate)
except Exception as e3:
logger.error(e3)
logger.error("Unable to update records biac_month_kpi305.")
logger.info(">>> COMPUTE 305 FINISHED")
def checkCommentsStatus():
logger.info(">>>>>>>> CHECKING FEEDBACK STATUS")
start_dt, end_dt = get_month_day_range(datetime.now() - timedelta(days=60))
end_dt = datetime.now()
df = es_helper.elastic_to_dataframe(
es,
query="xlsx: true AND docx: true AND ((-_exists_:sent) OR sent:false)",
index="biac_feedback_status",
start=start_dt,
end=end_dt,
timestampfield="reportdate",
datecolumns=['xlsx_date', 'docx_date', 'reportdate'])
if not df.empty:
now = datetime.now(pytz.timezone('Europe/Paris'))
date_window = (now - timedelta(hours=3))
if 'xlsx_date' not in df.columns:
df['xlsx_date'] = None
df['xlsx_date'] = df['xlsx_date'].fillna(date_window)
if 'docx_date' not in df.columns:
df['docx_date'] = None
df['docx_date'] = df['docx_date'].fillna(date_window)
df['last_update'] = df.apply(
lambda row: max(row['docx_date'], row['xlsx_date']), axis=1)
df = df[df['last_update'] <= date_window]
if not df.empty:
for index, row in df.iterrows():
id = row["_id"]
logger.info("Executing record:" + id)
rec = es.get(index="biac_feedback_status",
doc_type="doc",
id=id)["_source"]
logger.info(rec)
rec["sent"] = True
creds = {
"token": "feedbackdispatcher",
"user": {
"firstname": "Feedback",
"lastname": "Dispatcher",
"id": "FeedbackDispatcher",
"login": "******",
"user": "******",
"language": "en",
"privileges": ["admin"]
}
}
report = {
"description":
"Generates the report of the",
"title":
"Feedback KPI report ",
"exec":
"./reports/pythondef/Lot2KPI.py",
"icon":
"plane-departure",
"output": ["docx"],
"parameters": [{
"name": "param1",
"title": "Date",
"type": "date",
"value": row['reportdate'].isoformat()
}, {
"name": "param2",
"title": "Contract / Technic",
"type": "combo",
"value": row["reporttype"]
}, {
"name": "param3",
"title": "Type",
"type": "text",
"value": "Final"
}]
}
parameters_4567 = [{
"name": "param1",
"title": "Date",
"type": "date",
"value": row['reportdate'].isoformat()
}, {
"name": "param2",
"title": "Type",
"type": "text",
"value": "Final"
}]
report['title'] += row['reporttype']
report['description'] += row['reporttype']
if row['reporttype'] == 'Lot4 (BACDNB)':
report['exec'] = './reports/pythondef/Lot4KPI.py'
report['parameters'] = parameters_4567
elif row['reporttype'] == 'Lot5':
report['exec'] = './reports/pythondef/Lot5KPI.py'
report['parameters'] = parameters_4567
elif row['reporttype'] == 'Lot6':
report['exec'] = './reports/pythondef/Lot6KPI.py'
report['parameters'] = parameters_4567
elif row['reporttype'] == 'Lot7':
report['exec'] = './reports/pythondef/Lot7KPI.py'
report['parameters'] = parameters_4567
maanden = [
'Januari', 'Februari', 'Maart', 'April', 'Mei', 'Juni',
'Juli', 'Augustus', 'September', 'Oktober', 'November',
'December'
]
task = {
"mailAttachmentName": "KPI rapport ${KPI} ${DATE}-val",
"attachmentName": "KPI rapport ${KPI} ${DATE}-val",
"icon": "file-excel",
"mailSubject":
"SLA-KPI gevalideerde rapport ${DATE} ${YEAR} ${KPI}",
"mailTemplate": """
<body>
<h2>KPI gevalideerde rapport ${KPI} </h2>
<br/>
Goedemorgen,<br/>
<br/>
Hierbij het gevalideerde rapport van de KPI voor ${KPI}, met inbegrip van de commentaren en de nieuwe berekende scores.<br/>
Percentagewijzigingen, zoals deze tijdens de maandelijkse vergadering door BAC geaccepteerd werden, zijn in de overzichtstabel aangepast.
De titel werd door 'Gevalideerd' vervangen en de naam van het rapport werd in "val" veranderd.
Het gevalideerde rapport moet behouden worden.
<br/>
<br/>
Mvg,<br/>
<br/>
<br/>
<img border="0" width="81" height="42" style="width:.8437in;height:.4375in" id="CofelyLogo" src="cid:cofely_logo.png" alt="cofely_logo.png">
<br/>
</body>
""",
"mailingList": [],
}
entity = getEntityObj(es, row["reporttype"])
for dest in entity["header"]["list"]:
if "mail" in dest:
task["mailingList"].append(dest["mail"])
#task=json.loads(json.dumps(task).replace("${KPI}",entity["title"]).replace("${DATE}",start_dt.strftime("%B")))
task = json.loads(
json.dumps(task).replace(
"${KPI}", entity["title"]).replace(
"${DATE}",
maanden[row['reportdate'].month - 1]).replace(
"${YEAR}", str(row['reportdate'].year)))
message = {
"id": "id_" + str(uuid.uuid4()),
"creds": creds,
"report": report,
"privileges": ["admin"],
"task": task,
"entity": entity,
"mailAttachmentName": task["mailAttachmentName"],
"mailSubject": task["mailSubject"]
}
logger.info(message)
conn.send_message("/queue/NYX_REPORT_STEP1",
json.dumps(message))
es.index(index="biac_feedback_status",
doc_type="doc",
id=id,
body=rec)
logger.info("<<<<<<<< CHECKING FEEDBACK STATUS")
def proceed_computation(start):
logger.info('**********************************************************')
logger.info('PROCEED COMPUTATION: ' + str(start))
bulkbody = ''
flag = True
end = start + timedelta(hours=2)
while flag:
if end > datetime.now(containertimezone):
flag = False
#bulkbody = ''
df_alarms = es_helper.elastic_to_dataframe(es,
index='opt_sites_data*',
query=QUERY,
start=start,
end=end)
if len(df_alarms) == 0:
logger.info('no alarms on this period -> add 2 hours to end')
end = end + timedelta(hours=2)
else:
logger.info('-' + str(start))
logger.info('-' + str(end))
logger.info('-------')
df_alarms['datetime'] = pd.to_datetime(df_alarms['@timestamp'], unit="ms", utc=True) \
.dt.tz_convert('Europe/Paris')
if len(df_alarms) == 0:
logger.info('NO DATA')
df_alarms = df_alarms.sort_values('@timestamp')
df = df_alarms[[
'@timestamp', 'datetime', 'client', 'name', 'value'
]]
for alarm in df['name'].unique():
#logger.info(alarm)
df_filtered = df[df['name'] == alarm]
if len(df_filtered) > 0:
bulkbody += compute_alarms(alarm, df_filtered)
if start == max(df['datetime']).to_pydatetime():
logger.info('big hole')
obj = {
'start': str(start),
'end': str(end),
}
conn.send_message('/topic/ALARMS_MISSING_DATA',
json.dumps(obj))
end = end + timedelta(hours=2)
else:
start = max(df['datetime']).to_pydatetime()
end = start + timedelta(hours=2)
if bulkbody != '':
bulkres = es.bulk(bulkbody)
if (not (bulkres["errors"])):
logger.info("BULK done without errors.")
else:
for item in bulkres["items"]:
if "error" in item["index"]:
logger.info(item["index"]["error"])
return start
def update_month_kpi104(es, month):
logger.info(month)
local_timezone = tzlocal.get_localzone()
start_dt = month
end_dt = datetime(month.year, month.month,
calendar.monthrange(month.year, month.month)[1])
logger.info('-------------')
logger.info(start_dt)
logger.info(end_dt)
df = es_helper.elastic_to_dataframe(es,
index="biac_kpi104_check*",
query='*',
start=start_dt,
end=end_dt)
logger.info('res len %d' % len(df))
max_dt = start_dt.astimezone(local_timezone)
obj_past = None
try:
obj_past = es.get(index="biac_kpi104_monthly",
doc_type='doc',
id=start_dt.astimezone(local_timezone))['_source']
except elasticsearch.NotFoundError:
logger.error("Unable to retrive past data")
logger.error(error)
obj_past = {
'@timestamp': start_dt.astimezone(local_timezone),
'last_update': start_dt.astimezone(local_timezone),
'shift_number': 0,
'shift_presence': 0,
'percentage': 100
}
es.index(index="biac_kpi104_monthly",
doc_type='doc',
id=obj_past['@timestamp'],
body=json.dumps(obj_past, cls=DateTimeEncoder))
logger.info(obj_past)
shift_presence = 0
if len(df) == 0:
logger.info('empty data frame')
else:
df['dt'] = pd.to_datetime(df['@timestamp'], unit='ms', utc=True)
shift_number = 0
shift_presence = 0
percentage = 100
try:
shift_presence = df[df['value']]['value'].count()
max_dt = max(df[df['value']]['dt']).to_pydatetime().astimezone(
local_timezone)
shift_number = max_dt.day * 6
percentage = 0
except:
logger.info('shift_presence to 0')
if obj_past['shift_number'] != 0:
shift_number = 6
shift_presence = 0
percentage = 0
logger.info(max_dt)
logger.info('shift_number %d ' % shift_number)
logger.info('shift_presence %d ' % shift_presence)
obj = {
'@timestamp': start_dt.astimezone(local_timezone),
'last_update': max_dt,
'shift_number': shift_number,
'shift_presence': shift_presence,
'percentage': percentage
}
if shift_number != 0:
obj['percentage'] = round((shift_presence * 100) / shift_number, 1)
logger.info(json.dumps(obj, cls=DateTimeEncoder))
res = es.index(index="biac_kpi104_monthly",
doc_type='doc',
id=obj['@timestamp'],
body=json.dumps(obj, cls=DateTimeEncoder))
logger.info(res)
def create_obj(df_raw, start):
#DISPO DEBIT ENTREE THIOPAQ + ZOOM
obj = compute_avail_debit_entry_thiopac(df_raw)
obj_report_cogen = {}
obj_report_cogen['entry_biogas_thiopaq_Nm3'] = obj['value']
obj_report_cogen['entry_biogas_thiopaq_kWh'] = round(
obj['value'] * 6.1656, 2)
obj_report_cogen['entry_biogas_thiopaq_MWh'] = round(
obj['value'] * 6.1656 / 1000, 2)
obj_report_cogen['entry_biogas_thiopaq_minus_120_Nm3'] = obj[
'value_minus_120']
obj_report_cogen['entry_biogas_thiopaq_minus_120_kWh'] = round(
obj['value_minus_120'] * 6.1656, 2)
obj_report_cogen['entry_biogas_thiopaq_minus_120_MWh'] = round(
obj['value_minus_120'] * 6.1656 / 1000, 2)
obj_report_cogen['entry_biogas_thiopaq_120_220_Nm3'] = obj['value_120_220']
obj_report_cogen['entry_biogas_thiopaq_120_220_kWh'] = round(
obj['value_120_220'] * 6.1656, 2)
obj_report_cogen['entry_biogas_thiopaq_120_220_MWh'] = round(
obj['value_120_220'] * 6.1656 / 1000, 2)
obj_report_cogen['entry_biogas_thiopaq_220_330_Nm3'] = obj['value_220_330']
obj_report_cogen['entry_biogas_thiopaq_220_330_kWh'] = round(
obj['value_220_330'] * 6.1656, 2)
obj_report_cogen['entry_biogas_thiopaq_220_330_MWh'] = round(
obj['value_220_330'] * 6.1656 / 1000, 2)
obj_report_cogen['entry_biogas_thiopaq_330_600_Nm3'] = obj['value_330_600']
obj_report_cogen['entry_biogas_thiopaq_330_600_kWh'] = round(
obj['value_330_600'] * 6.1656, 2)
obj_report_cogen['entry_biogas_thiopaq_330_600_MWh'] = round(
obj['value_330_600'] * 6.1656 / 1000, 2)
#GASNAT ENRTREE COGEN
entry_gasnat = compute_gasnat_entry(df_raw)
obj_report_cogen['entry_gasnat_cogen_Nm3'] = entry_gasnat
obj_report_cogen['entry_gasnat_cogen_kWh'] = round(entry_gasnat * 10.42, 2)
obj_report_cogen['entry_gasnat_cogen_MWh'] = round(
entry_gasnat * 10.42 / 1000, 2)
#ENTREE BIOGASCOGEN ET CHAUDIERE
entry_biogas_cogen, df_entree_biogas = compute_entry_biogas_cogen(df_raw)
obj_report_cogen['entry_biogas_cogen_Nm3'] = entry_biogas_cogen
obj_report_cogen['entry_biogas_cogen_kWh'] = round(
entry_biogas_cogen * 6.1656, 2)
obj_report_cogen['entry_biogas_cogen_MWh'] = round(
entry_biogas_cogen * 6.1656 / 1000, 2)
obj_report_cogen['entry_biogas_boiler_Nm3'] = round(
obj_report_cogen['entry_biogas_thiopaq_Nm3'] - entry_biogas_cogen, 2)
obj_report_cogen['entry_biogas_boiler_kWh'] = round(
obj_report_cogen['entry_biogas_thiopaq_kWh'] -
obj_report_cogen['entry_biogas_cogen_kWh'], 2)
obj_report_cogen['entry_biogas_boiler_MWh'] = round(
obj_report_cogen['entry_biogas_thiopaq_MWh'] -
obj_report_cogen['entry_biogas_cogen_MWh'], 2)
obj_report_cogen['entry_total_cogen_kWh'] = round(
obj_report_cogen['entry_biogas_cogen_kWh'] +
obj_report_cogen['entry_gasnat_cogen_kWh'], 2)
obj_report_cogen['entry_total_cogen_MWh'] = round(
obj_report_cogen['entry_biogas_cogen_MWh'] +
obj_report_cogen['entry_gasnat_cogen_MWh'], 2)
#CALENDAR OPEN DAYS FOR RATIOS
start = containertimezone.localize(start)
start_year, end_year = datetime(start.year, 1,
1), datetime(start.year, 12, 31, 23, 59,
59)
df_calendar = es_helper.elastic_to_dataframe(es,
start=start_year,
end=end_year,
index='nyx_calendar',
query='type:LUTOSA',
datecolumns=['date'],
timestampfield='date')
open_days = (end_year - start_year).days + 1
logger.info('size df_calendar: ' + str(len(df_calendar)))
if len(df_calendar) > 0:
open_days = df_calendar.loc[df_calendar['on'], 'on'].count()
logger.info('opening days: ' + str(open_days))
target_prod_biogaz = ch.get_targets(es)['biogas']
target_prod_elec = ch.get_targets(es)['elec']
target_prod_heat = ch.get_targets(es)['heat']
target_runtime_cogen = ch.get_targets(es)['runtime']
daily_target_prod_biogaz = target_prod_biogaz / open_days
daily_target_prod_elec = target_prod_elec / open_days
daily_target_prod_heat = target_prod_heat / open_days
daily_target_runtime_cogen = target_runtime_cogen / open_days
day_on = True
try:
day_on = bool(df_calendar[df_calendar['date'] == start]['on'].iloc[0])
except:
pass
obj_report_cogen['on'] = day_on
#HEURES DE FCT (DE ROTATION) COGEN
percent_value = compute_avail_moteur(df_raw)
obj_report_cogen['daily_avail_motor'] = round(percent_value, 2)
obj_report_cogen['daily_avail_motor_hour'] = round(percent_value * 24, 2)
if day_on:
obj_report_cogen[
'daily_avail_motor_target_hour'] = daily_target_runtime_cogen
obj_report_cogen['daily_avail_motor_ratio_target'] = round(
(obj_report_cogen['daily_avail_motor_hour'] /
daily_target_runtime_cogen), 2)
else:
obj_report_cogen['daily_avail_motor_target_hour'] = 0
#PROD ELEC COGEN
prod_elec_cogen = compute_prod_elec_cogen(df_raw)
obj_report_cogen['out_elec_cogen_kWh'] = round(prod_elec_cogen, 2)
obj_report_cogen['out_elec_cogen_MWh'] = round(prod_elec_cogen / 1000, 2)
if day_on:
obj_report_cogen['out_elec_cogen_target_MWh'] = daily_target_prod_elec
obj_report_cogen['out_elec_cogen_ratio_target'] = round(
obj_report_cogen['out_elec_cogen_MWh'] /
obj_report_cogen['out_elec_cogen_target_MWh'], 2)
else:
obj_report_cogen['out_elec_cogen_target_MWh'] = 0
#PROD THERM COGEN
prod_therm_cogen_ht, prod_therm_cogen_drycooler = compute_prod_therm_cogen(
df_raw)
prod_therm_cogen = prod_therm_cogen_ht + prod_therm_cogen_drycooler
obj_report_cogen['out_therm_cogen_kWh'] = round(prod_therm_cogen, 2)
obj_report_cogen['out_therm_cogen_MWh'] = round(prod_therm_cogen / 1000, 2)
obj_report_cogen['out_therm_cogen_ht_kWh'] = round(prod_therm_cogen_ht, 2)
obj_report_cogen['out_therm_cogen_ht_MWh'] = round(
prod_therm_cogen_ht / 1000, 2)
obj_report_cogen['out_therm_cogen_drycooler_kWh'] = round(
prod_therm_cogen_drycooler, 2)
obj_report_cogen['out_therm_cogen_drycooler_MWh'] = round(
prod_therm_cogen_drycooler / 1000, 2)
if day_on:
obj_report_cogen['out_therm_cogen_target_MWh'] = daily_target_prod_heat
obj_report_cogen['out_therm_cogen_ratio_target'] = round(
obj_report_cogen['out_therm_cogen_MWh'] /
obj_report_cogen['out_therm_cogen_target_MWh'], 2)
else:
obj_report_cogen['out_therm_cogen_target_MWh'] = 0
obj_report_cogen['out_total_cogen_kWh'] = round(
obj_report_cogen['out_therm_cogen_kWh'] +
obj_report_cogen['out_elec_cogen_kWh'], 2)
obj_report_cogen['out_total_cogen_MWh'] = round(
obj_report_cogen['out_therm_cogen_MWh'] +
obj_report_cogen['out_elec_cogen_MWh'], 2)
if day_on:
obj_report_cogen[
'entry_biogas_thiopaq_target_MWh'] = daily_target_prod_biogaz
obj_report_cogen['entry_biogas_thiopaq_ratio_target'] = round(
(obj_report_cogen['entry_biogas_thiopaq_MWh'] /
obj_report_cogen['entry_biogas_thiopaq_target_MWh']), 2)
else:
obj_report_cogen['entry_biogas_thiopaq_target_MWh'] = 0
#RENDEMENTS
if obj_report_cogen['entry_total_cogen_kWh'] == 0:
obj_report_cogen['total_efficiency'] = 0
obj_report_cogen['elec_efficiency'] = 0
obj_report_cogen['therm_efficiency'] = 0
obj_report_cogen['gasnat_ratio'] = 0
obj_report_cogen['biogas_ratio'] = 0
else:
obj_report_cogen['total_efficiency'] = round(
obj_report_cogen['out_total_cogen_kWh'] /
obj_report_cogen['entry_total_cogen_kWh'], 2)
obj_report_cogen['elec_efficiency'] = round(
obj_report_cogen['out_elec_cogen_kWh'] /
obj_report_cogen['entry_total_cogen_kWh'], 2)
obj_report_cogen['therm_efficiency'] = round(
obj_report_cogen['out_therm_cogen_kWh'] /
obj_report_cogen['entry_total_cogen_kWh'], 2)
obj_report_cogen['gasnat_ratio'] = round(
obj_report_cogen['entry_gasnat_cogen_kWh'] /
obj_report_cogen['entry_total_cogen_kWh'], 2)
obj_report_cogen['biogas_ratio'] = round(
obj_report_cogen['entry_biogas_cogen_kWh'] /
obj_report_cogen['entry_total_cogen_kWh'], 2)
obj_report_cogen['total_efficiency_wo_zero'] = obj_report_cogen[
'total_efficiency']
obj_report_cogen['therm_efficiency_wo_zero'] = obj_report_cogen[
'therm_efficiency']
obj_report_cogen['elec_efficiency_wo_zero'] = obj_report_cogen[
'elec_efficiency']
obj_report_cogen['gasnat_ratio_wo_zero'] = obj_report_cogen[
'gasnat_ratio']
obj_report_cogen['biogas_ratio_wo_zero'] = obj_report_cogen[
'biogas_ratio']
#DISPO THIOPAQ
obj = compute_dispo_thiopaq(df_raw)
obj_report_cogen['max_theorical_avail_thiopaq_hour'] = obj[
'max_theorical_avail_thiopaq_hour']
obj_report_cogen['avail_thiopaq_hour'] = obj['avail_thiopaq_hour']
if day_on:
obj_report_cogen['avail_thiopaq_ratio'] = obj['avail_thiopaq_ratio']
#DISPO COGEN
obj = compute_dispo_cogen(df_raw)
obj_report_cogen['max_theorical_avail_cogen_hour'] = obj[
'max_theorical_avail_cogen_hour']
obj_report_cogen['avail_cogen_hour'] = obj['avail_cogen_hour']
if day_on:
obj_report_cogen['avail_cogen_ratio'] = obj['avail_cogen_ratio']
#STARTS AND STOPS
starts, stops = compute_starts_and_stops(df_raw)
obj_report_cogen['starts'] = int(starts)
obj_report_cogen['stops'] = int(stops)
return obj_report_cogen
def get_kpi304_values(es, lot, tec, date):
query = 'lot:' + lot
if lot == '2':
query += ' AND tec:' + tec
print(query)
containertimezone = pytz.timezone(tzlocal.get_localzone().zone)
start_dt = containertimezone.localize(datetime(date.year, date.month, 1))
end_dt = containertimezone.localize(
datetime(date.year, date.month,
calendar.monthrange(date.year, date.month)[1], 23, 59, 59))
print(start_dt)
print(end_dt)
dataframe = es_helper.elastic_to_dataframe(es,
index="biac_kpi304",
datecolumns=["@timestamp"],
query=query,
start=start_dt,
end=end_dt)
if len(dataframe) == 0:
print('dataframe empty we create in DB')
default_df = pd.DataFrame(pd.date_range(start=start_dt, end=end_dt),
columns=['_timestamp'])
if lot == '1' or lot == '2':
default_df['tech'] = 2
default_df['tech1'] = 1
default_df['tech2'] = 1
else:
default_df['tech'] = 1
default_df['hoofd'] = 1
default_df['dayofweek'] = default_df['_timestamp'].dt.dayofweek
default_df.loc[default_df['dayofweek'] >= 5, 'tech'] = 0
default_df.loc[default_df['dayofweek'] >= 5, 'hoofd'] = 0
default_df['total'] = default_df['tech'] + default_df['hoofd']
default_df['tec'] = tec
default_df['lot'] = lot
default_df['_index'] = 'biac_kpi304'
default_df['_id'] = default_df['lot'] + '_' + default_df[
'tec'] + '_' + default_df['_timestamp'].astype(str)
del default_df['dayofweek']
es_helper.dataframe_to_elastic(es, default_df)
default_df['_timestamp'] = default_df['_timestamp'].dt.date.astype(str)
logger.info('query' * 100)
thr = Timer(5, update_kib_kpi304, (es, lot, tec, date))
thr.start()
return default_df.rename({
'_timestamp': '@timestamp'
}, axis='columns').to_json(orient='records')
else:
dataframe.sort_values('@timestamp', inplace=True)
dataframe['@timestamp'] = dataframe['@timestamp'].dt.date.astype(str)
return dataframe.to_json(orient='records')
def messageReceived(destination,message,headers):
"""
Main function that reads the Excel file.
"""
global es
starttime = time.time()
logger.info("==> "*10)
logger.info("Message Received %s" % destination)
logger.info(headers)
file_name = 'default'
if "CamelSplitAttachmentId" in headers:
headers["file"] = headers["CamelSplitAttachmentId"]
if "file" in headers:
file_name = headers["file"]
logger.info("File:%s" %file_name)
log_message("Import of file [%s] started." %file_name)
histo_date = datetime.now().date()
flag_histo = False
regex = r'_([0-9]{8})\.'
z = re.findall(regex, file_name)
if z:
flag_histo = True
histo_date = datetime.strptime(z[0], "%Y%m%d").date()
flag_already_histo_for_today = False
try:
dataframe=es_helper.elastic_to_dataframe(es,index="biac_histo_maximo"
,size=1
,query='histo_date:'+str(histo_date))
if len(dataframe) > 0:
flag_already_histo_for_today=True
except:
pass
logger.info('Histo flag: '+str(flag_histo))
logger.info('Histo date : '+str(histo_date))
logger.info('Already histo for date: '+str(histo_date)+' -> '+str(flag_already_histo_for_today))
xlsbytes = base64.b64decode(message)
f = open('./tmp/excel.xlsx', 'wb')
f.write(xlsbytes)
f.close()
df = None
try:
sheet = 'WorkOrdersListBAC.rdl'
df = pd.read_excel('./tmp/excel.xlsx', sheetname=sheet)
newheaders = df.iloc[0]
df = df[1:]
df.columns = newheaders
bulkbody = ''
bulkres = ''
reserrors= []
df.fillna(0,inplace=True)
if not flag_histo:
es.indices.delete(index="biac_maximo", ignore=[400, 404])
es.indices.delete(index="biac_503maximo", ignore=[400, 404])
time.sleep(3)
df = df[df['Contract'] !=0]
df['Contract'] = df['Contract'].apply(lambda x: x.upper())
df['lot'] = df['Contract'].apply(lambda x: set_lot(x))
df['technic'] = df.apply(lambda row: set_technic(row['Contract'], row['Pm execution team']), axis=1)
df['screen_name'] = df.apply(extract_screen_name, axis=1)
df[['lot', 'technic', 'Contract', 'Pm execution team']]
now = datetime.now()
displayStart = getDisplayStart(now)
displayStop = getDisplayStop(now)
start_dt_503, stop_dt_503 = getDisplayDate503()
for index, row in df.iterrows():
woid = row[0]
contract= row[1]
worktype = row[2]
wosummary = row[3]
status = row[4]
getTsDate = lambda cpltd: getTimestamp(cpltd) if cpltd != None and cpltd != np.nan else 0
completedDate = getTsDate(row[5])
pmExecutionTeam = row['Pm execution team']
woExecutionTeam = row['Wo execution team']
displaykpi302Start = getDisplayKPI302(now)
nowts = int(now.timestamp())
display = 0
displaykpi302 = 0
row['ScheduledStart'] = row['ScheduledStart'].replace(hour=12, minute=0)
if woExecutionTeam == np.nan:
woExecutionTeam = 0
targetStart = getTsDate(row['TargetStart'])
scheduledStart = getTsDate(row['ScheduledStart'])
actualStartMax = getTsDate(row['ActualStart max'])
actualStart = getTsDate(row['ActualStart'])
KPI301 = row['KPI301']
assetDescription = row['Asset description']
routeDescription = row['Route description']
actualFinishMax = getTsDate(row['ActualFinish max'])
actualFinish = getTsDate(row['ActualFinish'])
KPI302 = row['KPI302']
KPI503 = ""
KPI503computed =" "
if "KPI503" in row:
KPI503 = row['KPI503']
KPI503computed = row['KPI503']
overdue = 0
ispm=(worktype=="PM")
issafe=(worktype=="SAFE" or worktype=="GRIS")
try:
if ispm or issafe:
if displayStart <= scheduledStart < displayStop:
display = 1
elif scheduledStart < displayStart:
overdue = 1
else:
if start_dt_503.timestamp() <= scheduledStart < stop_dt_503.timestamp():
display = 1
KPI503computed = 'New overdue'
elif scheduledStart < start_dt_503.timestamp():
overdue = 1
KPI503computed = 'Overdue'
except Exception as e:
logger.warning('Scheduled start not int : ' + str(scheduledStart) + '-' + str(row['ScheduledStart']))
logger.info(e)
try:
if displaykpi302Start <= actualStart < nowts:
displaykpi302 = 1
except Exception as e:
logger.info(e)
difference = now - row['ScheduledStart']
days = int(difference /timedelta(days=1))
strDays = '+'+str(days)+'j'
sevendays = getTimestamp(now - timedelta(days=7))
fourthteendays = getTimestamp(now - timedelta(days=14))
if displayStart <= scheduledStart < fourthteendays:
strDays = '+14j'
if fourthteendays <= scheduledStart < sevendays:
if KPI301 == 'Not Started NOK' or KPI301 == 'Started NOK':
strDays ='+14j'
else:
strDays = '+7j'
if strDays == '+14j' and KPI301 == 'Started OK':
strDays = '+15j'
future = 1
if nowts < scheduledStart:
future = 0
newrec = {
"woid": woid,
"contract": contract,
"worktype": worktype,
"wosummary": wosummary,
"status": status,
"completedDate": completedDate*1000,
"pmExecutionTeam": pmExecutionTeam,
"woExecutionTeam": woExecutionTeam,
"targetStart": targetStart*1000,
"scheduledStart": scheduledStart*1000,
"Scheduled": scheduledStart*1000,
"actualStartMax": actualStartMax*1000,
"Started": actualStart*1000,
"future": future,
"actualStart": actualStart*1000,
"displayKPI301": display,
"displayKPI302": displaykpi302,
"technic": row.technic,
"lot": row.lot,
"screen_name": row.screen_name,
"strDays": strDays,
"KPI301": KPI301,
"assetDescription": assetDescription,
"routeDescription": routeDescription,
"overdue": overdue,
"actualFinishMax": actualFinishMax*1000,
"actualFinish": actualFinish*1000,
"KPI302": KPI302,
"KPI503": KPI503,
"KPI503computed": KPI503computed,
}
if not ispm or not issafe:
dt = datetime.fromtimestamp(scheduledStart).date()
newrec['strMonths'] = compute_str_months(dt)
if not flag_histo:
es_id = woid+'_'+str(scheduledStart)
action = {}
if ispm:
action["index"] = {"_index": 'biac_maximo',
"_type": "doc", "_id": es_id}
elif issafe:
action["index"] = {"_index": 'biac_safemaximo',
"_type": "doc", "_id": es_id}
else:
action["index"] = {"_index": 'biac_503maximo',
"_type": "doc", "_id": es_id}
bulkbody += json.dumps(action)+"\r\n"
bulkbody += json.dumps(newrec) + "\r\n"
if not flag_already_histo_for_today:
es_id = woid+'_'+str(scheduledStart)+'_'+(histo_date.strftime('%Y-%m-%d'))
action = {}
if ispm:
action["index"] = {"_index": 'biac_histo_maximo',
"_type": "doc", "_id": es_id}
elif issafe:
action["index"] = {"_index": 'biac_safehisto_maximo',
"_type": "doc", "_id": es_id}
else:
action["index"] = {"_index": 'biac_503histo_maximo',
"_type": "doc", "_id": es_id}
newrec['histo_date_dt'] = str(histo_date)
newrec['histo_date'] = histo_date.strftime('%Y-%m-%d')
bulkbody += json.dumps(action)+"\r\n"
bulkbody += json.dumps(newrec) + "\r\n"
if len(bulkbody) > 512000:
logger.info("BULK READY:" + str(len(bulkbody)))
bulkres = es.bulk(bulkbody, request_timeout=30)
logger.info("BULK DONE")
bulkbody = ''
if(not(bulkres["errors"])):
logger.info("BULK done without errors.")
else:
for item in bulkres["items"]:
if "error" in item["index"]:
logger.info(item["index"]["error"])
reserrors.append(
{"error": item["index"]["error"], "id": item["index"]["_id"]})
if len(bulkbody) > 0:
logger.info("BULK READY FINAL:" + str(len(bulkbody)))
bulkres = es.bulk(bulkbody)
logger.info("BULK DONE FINAL")
if(not(bulkres["errors"])):
logger.info("BULK done without errors.")
else:
for item in bulkres["items"]:
if "error" in item["index"]:
logger.info(item["index"]["error"])
reserrors.append(
{"error": item["index"]["error"], "id": item["index"]["_id"]})
time.sleep(3)
first_alarm_ts = getTimestamp(df['ScheduledStart'].min())
last_alarm_ts = getTimestamp(df['ScheduledStart'].max())
obj = {
'start_ts': int(first_alarm_ts),
'end_ts': int(last_alarm_ts)
}
logger.info(obj)
if not flag_histo:
conn.send_message('/topic/BIAC_MAXIMO_IMPORTED', json.dumps(obj))
conn.send_message('/topic/BIAC_MAXIMOBIS_IMPORTED', json.dumps(obj))
except Exception as e:
endtime = time.time()
logger.error(e)
log_message("Import of file [%s] failed. Duration: %d Exception: %s." % (headers["file"],(endtime-starttime),str(e)))
endtime = time.time()
try:
log_message("Import of file [%s] finished. Duration: %d Records: %d." % (headers["file"],(endtime-starttime),df.shape[0]))
except:
log_message("Import of file [%s] finished. Duration: %d." % (headers["file"],(endtime-starttime)))
logger.info("<== "*10)
def update_kib_kpi304(es, lot, tec, date):
containertimezone = pytz.timezone(tzlocal.get_localzone().zone)
start_dt = containertimezone.localize(datetime(date.year, date.month, 1))
end_dt = containertimezone.localize(
datetime(date.year, date.month,
calendar.monthrange(date.year, date.month)[1], 23, 59, 59))
query = 'lot:' + str(lot) + ' AND tec:' + tec
logger.info('query' * 100)
logger.info(query)
logger.info(start_dt)
logger.info(end_dt)
logger.info(query)
df = es_helper.elastic_to_dataframe(es, index='biac_kpi304',datecolumns=["@timestamp"]\
, query=query, start=start_dt, end=end_dt)
if 'off' not in df:
df['off'] = 0
df['off'] = df['off'].fillna(0)
df['week_day'] = df['@timestamp'].dt.weekday
logger.info(df.shape)
df.head()
new_arr = []
for index, row in df.iterrows():
flag_off = False
if row['week_day'] == 5 or row['week_day'] == 6 or int(
row['off']) == 1:
flag_off = True
type_list = ['hoofd', 'tech1', 'tech2']
if 'tech1' not in row or row['tech1'] != row['tech1']:
type_list = ['hoofd', 'tech']
for i in type_list:
obj = {
'type': i,
'lot': row['lot'],
'kpi304_technic': row['tec'],
'@timestamp': row['@timestamp'],
}
if flag_off:
obj['value'] = -1
else:
obj['value'] = row[i]
obj['_id'] = 'lot' + str(
row['lot']) + '_' + row['tec'] + '_' + i + '_' + str(
int(obj['@timestamp'].timestamp() * 1000))
if obj['type'] == 'hoofd':
obj['type_nl'] = 'Verantwoordelijke'
elif obj['type'] == 'tech':
obj['type_nl'] = 'Technieker'
elif obj['type'] == 'tech1':
obj['type_nl'] = 'Technieker 1'
elif obj['type'] == 'tech2':
obj['type_nl'] = 'Technieker 2'
new_arr.append(obj)
df_to_push = pd.DataFrame(new_arr)
df_to_push['_index'] = 'biac_kib_kpi304'
logger.info(df_to_push.shape)
es_helper.dataframe_to_elastic(es, df_to_push)
def compute_targets(year):
global es
target_prod_biogaz = ch.get_targets(es)['biogas']
target_prod_elec = ch.get_targets(es)['elec']
target_prod_heat = ch.get_targets(es)['heat']
target_runtime_cogen = ch.get_targets(es)['runtime']
start, end = datetime(year, 1, 1), datetime(year, 12, 31, 23, 59, 59)
logger.info(start)
logger.info(end)
df_calendar = es_helper.elastic_to_dataframe(es,
start=start,
end=end,
index='nyx_calendar',
query='type:LUTOSA',
datecolumns=['date'],
timestampfield='date')
open_days = (datetime(year, 12, 31) - datetime(year, 1, 1)).days + 1
logger.info('size df_calendar: ' + str(len(df_calendar)))
if len(df_calendar) > 0:
open_days = df_calendar.loc[df_calendar['on'], 'on'].count()
# df_calendar['date']=pd.to_datetime(df_calendar['date'], utc=True).dt.tz_convert('Europe/Paris').dt.date
df_calendar['date'] = pd.to_datetime(df_calendar['date']).dt.date
del df_calendar['_id']
del df_calendar['_index']
logger.info('opening days: ' + str(open_days))
daily_target_prod_biogaz = target_prod_biogaz / open_days
daily_target_prod_elec = target_prod_elec / open_days
daily_target_prod_heat = target_prod_heat / open_days
daily_target_runtime_cogen = target_runtime_cogen / open_days
df_daily_lutosa = es_helper.elastic_to_dataframe(
es,
start=start,
end=end,
index='daily_cogen_lutosa',
datecolumns=['@timestamp'],
query='*')
if len(df_daily_lutosa) == 0:
return None
if 'on' in df_daily_lutosa.columns:
del df_daily_lutosa['on']
df_daily_lutosa['date'] = pd.to_datetime(
df_daily_lutosa['@timestamp']).dt.date
if len(df_calendar) > 0:
logger.info('merge calendar')
df_daily_lutosa = df_daily_lutosa.merge(df_calendar[['date', 'on']],
left_on='date',
right_on='date',
how='left')
df_daily_lutosa['entry_biogas_thiopaq_target_MWh'] = 0
df_daily_lutosa.loc[
df_daily_lutosa['on'],
'entry_biogas_thiopaq_target_MWh'] = daily_target_prod_biogaz
df_daily_lutosa.loc[
df_daily_lutosa['on'], 'entry_biogas_thiopaq_ratio_target'] = round(
(df_daily_lutosa.loc[df_daily_lutosa['on'],
'entry_biogas_thiopaq_MWh'] /
df_daily_lutosa.loc[df_daily_lutosa['on'],
'entry_biogas_thiopaq_target_MWh']), 2)
df_daily_lutosa['daily_avail_motor_target_hour'] = 0
df_daily_lutosa.loc[
df_daily_lutosa['on'],
'daily_avail_motor_target_hour'] = daily_target_runtime_cogen
df_daily_lutosa.loc[
df_daily_lutosa['on'], 'daily_avail_motor_ratio_target'] = round(
(df_daily_lutosa.loc[df_daily_lutosa['on'],
'daily_avail_motor_hour'] /
df_daily_lutosa.loc[df_daily_lutosa['on'],
'daily_avail_motor_target_hour']), 2)
df_daily_lutosa['out_elec_cogen_target_MWh'] = 0
df_daily_lutosa.loc[df_daily_lutosa['on'],
'out_elec_cogen_target_MWh'] = daily_target_prod_elec
df_daily_lutosa.loc[df_daily_lutosa['on'],
'out_elec_cogen_ratio_target'] = round(
(df_daily_lutosa.loc[df_daily_lutosa['on'],
'out_elec_cogen_MWh'] /
df_daily_lutosa.loc[df_daily_lutosa['on'],
'out_elec_cogen_target_MWh']),
2)
df_daily_lutosa['out_therm_cogen_target_MWh'] = 0
df_daily_lutosa.loc[df_daily_lutosa['on'],
'out_therm_cogen_target_MWh'] = daily_target_prod_heat
df_daily_lutosa.loc[df_daily_lutosa['on'],
'out_therm_cogen_ratio_target'] = round((
df_daily_lutosa.loc[df_daily_lutosa['on'],
'out_therm_cogen_MWh'] /
df_daily_lutosa.loc[df_daily_lutosa['on'],
'out_therm_cogen_target_MWh']),
2)
if 'on_x' in df_daily_lutosa.columns:
del df_daily_lutosa['on_x']
if 'on_y' in df_daily_lutosa.columns:
del df_daily_lutosa['on_y']
if 'weekday' in df_daily_lutosa.columns:
del df_daily_lutosa['weekday']
if 'date' in df_daily_lutosa.columns:
del df_daily_lutosa['date']
if 'type' in df_daily_lutosa.columns:
del df_daily_lutosa['type']
df_daily_lutosa.loc[df_daily_lutosa['on'] == False,
'out_therm_cogen_ratio_target'] = np.nan
df_daily_lutosa.loc[df_daily_lutosa['on'] == False,
'out_elec_cogen_ratio_target'] = np.nan
df_daily_lutosa.loc[df_daily_lutosa['on'] == False,
'daily_avail_motor_ratio_target'] = np.nan
df_daily_lutosa.loc[df_daily_lutosa['on'] == False,
'entry_biogas_thiopaq_ratio_target'] = np.nan
df_daily_lutosa.loc[df_daily_lutosa['on'] == False,
'avail_cogen_ratio'] = np.nan
df_daily_lutosa.loc[df_daily_lutosa['on'] == False,
'avail_thiopaq_ratio'] = np.nan
es_helper.dataframe_to_elastic(es, df_daily_lutosa)
