def get_faultcount_by_vobcid_loc(start_date, end_date,fault_code, velocity_dropdown = None, apstatus = None ):
start_date,end_date = util.date2str2(start_date,end_date)
faults = ''
veld = ''
apstat = ''
if (fault_code != -1 ):
faults = " and faultCode = {}".format(fault_code)
if velocity_dropdown is 0:
veld = " and velocity = 0"
if velocity_dropdown is 1:
veld = " and NOT(velocity = 0)"
if apstatus is 1:
apstat = " and activePassiveStatus = true"
if apstatus is 0:
apstat = " and activePassiveStatus = false"
query = ("SELECT vobcid, locationName, count(vobcid) as FaultCount from dlr_vobc_fault"
" where loggedAt >= '{}' and loggedAt < '{}' and faultCodeSet = true {} {} {}"
" group by vobcid, locationName"
" order by FaultCount desc"
" LIMIT 300").format(start_date, end_date, faults, veld, apstat)
df = util.run_query(query)
return df
def get_commLoss_by_vobcid_loc(start_date,
end_date,
velocity_dropdown=None,
apstatus=None,
commtype=None):
start_date, end_date = util.date2str2(start_date, end_date)
veld = ''
apstat = ''
commT = ''
if velocity_dropdown is 0:
veld = " and velocity = 0"
if velocity_dropdown is 1:
veld = " and NOT(velocity = 0)"
if apstatus is 1:
apstat = " and activePassiveStatus = true"
if apstatus is 0:
apstat = " and activePassiveStatus = false"
if commtype is not None and commtype != -1:
commT = " and commType = {}".format(commtype)
query = (
"SELECT vobcid, locationName, count(commType) as commLossCount from dlr_vobc_comloss"
" where commType > 0 and commType < 6 and loggedAt >= '{}' and loggedAt < '{}' {} {} {}"
" group by vobcid, locationName"
" order by commLossCount desc"
" LIMIT 300").format(start_date, end_date, veld, apstat, commT)
df = util.run_query(query)
if df is None:
df = pd.DataFrame()
return df
def get_faultcount_by_vobcid_loc_date(start_date, end_date, vobcid, fault_code, location, velocity_dropdown = None, apstatus = None):
faults = ''
vobcs = ''
loc = ''
veld = ''
apstat = ''
if (fault_code != -1 ):
faults = " and faultCode = {}".format(fault_code)
if (vobcid != -1 ):
vobcs = " and vobcid = {}".format(vobcid)
if (location is not None ):
loc = " and locationName = '{}'".format(location)
if velocity_dropdown is 0:
veld = " and velocity = 0"
if velocity_dropdown is 1:
veld = " and NOT(velocity = 0)"
if apstatus is 1:
apstat = " and activePassiveStatus = true"
if apstatus is 0:
apstat = " and activePassiveStatus = false"
start_date,end_date = util.date2str2(start_date,end_date)
query = ("SELECT HISTOGRAM(loggedAt, INTERVAL 1 DAY) as date, count(loggedAt) as FaultCount"
" from dlr_vobc_fault"
" where loggedAt >= '{}' and loggedAt < '{}' and faultCodeSet = true {} {} {} {} {}"
" GROUP BY date").format(start_date, end_date, vobcs, faults, loc, veld, apstat)
df = util.run_query(query)
return df
def get_commLoss_list(start_date,
end_date,
vobc_id=None,
location=None,
velocity=None,
apstatus=None,
commtype=None):
start_date, end_date = util.date2str2(start_date, end_date)
query = "SELECT vobcid, parentTrainId, loggedAt, velocity, activePassiveStatus, locationName, commType from dlr_vobc_comloss where commType > 0 and commType < 6 and loggedAt >= '{}' and loggedAt < '{}'".format(
start_date, end_date)
if vobc_id is not None and vobc_id != -1:
query += " and vobcid = {}".format(vobc_id)
if (location is not None):
query += " and locationName = '{}'".format(location)
if velocity is 0:
query += " and velocity = 0"
if velocity is 1:
query += " and NOT(velocity = 0)"
if apstatus is 1:
query += " and activePassiveStatus = true"
if apstatus is 0:
query += " and activePassiveStatus = false"
if commtype is not None and commtype != -1:
query += " and commType = {}".format(commtype)
df = util.run_query(query)
if df is None:
df = pd.DataFrame()
return df
def gen_train_mileage_table(vobcid, start_date):
start_date = util.str2date1(start_date)
end_date = start_date + timedelta(hours=24)
start_date, end_date = util.date2str2(start_date, end_date)
df = get_train_mileage_by_30min(vobcid, start_date, end_date)
df = df.dropna()
s = df["Distance"].sum()
df2 = get_train_mileage_by_loop(vobcid, start_date, end_date)
df2 = df2.dropna()
df = df.append(df2, ignore_index=True, sort=False)
if df.empty:
return df
df = df.sort_values(by='time')
df["vobcid"] = vobcid
df["vobcid"] = df["vobcid"].astype(str)
df2 = pd.DataFrame({
"vobcid": ["Total:"],
"loopName": [""],
"time": [""],
"Distance": [s]
})
df2 = df2.append(df, ignore_index=True, sort=False)
data = df2.to_dict('rows')
return data
def get_mileage_by_train(start_date, end_date):
start_date, end_date = util.date2str2(start_date, end_date)
query = (
"SELECT vobcid, SUM(distance)/1000 as Distance FROM dlr_train_move "
" where loggedAt >= '{}' and loggedAt < '{}' group by vobcid").format(
start_date, end_date)
L = util.run_query(query)
return L
def get_trainId(start_date, end_date):
start_date, end_date = util.date2str2(start_date, end_date)
query = (
"SELECT vobcid from dlr_train_move where loggedAt >= '{}' and loggedAt < '{}' "
" group by vobcid").format(start_date, end_date)
L = util.run_query(query)
L = L["vobcid"].tolist()
return L
def get_vobc_fault_list(start_date, end_date,faultCode):
start_date, end_date = util.date2str2(start_date, end_date)
query = ("SELECT loggedAt, faultCodeSet, faultCode,parentTrainId, vehicleName,"
" vobcid, activePassiveStatus as VobcStatus, locationName, loopName, velocity, "
" faultDescription FROM dlr_vobc_fault where loggedAt >= '{}' and loggedAt < '{}' and faultCode = {}"
" and faultCodeSet = true").format(start_date, end_date, faultCode)
L = util.run_query(query)
return L
def get_fleet_daily_mileage(start_date, end_date):
start_date, end_date = util.date2str2(start_date, end_date)
query = (
"SELECT loggedDate, SUM(distance)/1000 as daily_distance FROM dlr_train_move "
" where loggedAt >= '{}' and loggedAt < '{}' group by loggedDate"
).format(start_date, end_date)
L = util.run_query(query)
return L
def get_fault_names(start_date, end_date):
start_date, end_date = util.date2str2(start_date, end_date)
query = ("SELECT faultName FROM dlr_vobc_fault "
" where faultCode > 0 and loggedAt >= '{}' and loggedAt < '{}' group by faultName").format(start_date, end_date)
L = util.run_query(query)
if L.empty == False:
return L["faultName"].tolist()
return []
def get_switchId(start_date, end_date):
start_date, end_date = util.date2str2(start_date, end_date)
query = (
"SELECT switchId from dlr_switch_move where loggedAt >= '{}' and loggedAt < '{}' "
" group by switchId").format(start_date, end_date)
L = util.run_query(query)
L = L["switchId"].tolist()
return L
def get_fault_types_per_day(start_date, end_date, vobcid = None):
start_date, end_date = util.date2str2(start_date, end_date)
if vobcid is not None:
x = ("and vobcid = {}").format(vobcid)
else:
x = ""
query = ("SELECT HISTOGRAM(loggedAt, INTERVAL 1 Day) as loggedDay, faultName, count(*) as faultcount FROM dlr_vobc_fault "
" where faultCode > 0 and loggedAt >= '{}' and loggedAt < '{}' {} group by faultName, loggedDay").format(start_date, end_date, x)
L = util.run_query(query)
return L
def gen_box_df(start_date, end_date):
start_date, end_date = util.date2str2(start_date, end_date)
df = gen_bx_df_(start_date, end_date)
df2 = get_unlock_count(start_date, end_date)
if not df2.empty:
df = df.set_index('switchId').join(df2.set_index('switchId'))
df = df.fillna(value=0)
else:
df["count"] = 0
return df
def test_datecheck():
start_date = dt(2014, 1, 1)
end_date = dt(2020, 1, 1)
start_date, end_date = util.date2str2(start_date, end_date)
start_date, end_date = vm.datecheck(start_date, end_date)
start_date1, end_date1 = vm.datecheck(end_date, start_date)
start_date2, end_date2 = vm.datecheck(None, None)
assert start_date == start_date1
assert end_date == end_date1
assert start_date2 is not None
assert end_date2 is not None
def get_op_hours(startDate, endDate, trainId):
month = []
hours = []
while startDate < endDate:
end = startDate + relativedelta(months=+1)
start_, end_ = util.date2str2(startDate, end)
df = get_operating_hours_by_month(start_, end_, trainId)
hours.append(df)
month.append(startDate)
startDate = end
d = {"loggedMonth": month, "op_time": hours}
df = pd.DataFrame(data=d)
df["vobcid"] = trainId
return df
def get_unique_vobcid_list(start_date, end_date, trainId):
start_date, end_date = util.date2str2(start_date, end_date)
if trainId == None or end_date == None or start_date == None:
return []
query = ("SELECT vobcid from dlr_train_move"
" where trainId = '{}' and loggedAt >= '{}' and loggedAt < '{}'"
" group by vobcid").format(trainId, start_date, end_date)
df = util.run_query(query)
if df is None or df.empty:
df = pd.DataFrame()
return df
df = df['vobcid'].unique()
return df
def gen_box_date_df(switchId, start_date, end_date):
start_date, end_date = util.date2str2(start_date, end_date)
df = gen_bx_date_df_(switchId, start_date, end_date)
df2 = get_unlock_count_by_date(switchId, start_date, end_date)
df["loggedDate"] = pd.to_datetime(df["loggedDate"])
# df["loggedDate"] = df["loggedDate"].tz_localize(None)
df = df.set_index('loggedDate').tz_localize(None)
if not df2.empty:
df2 = df2.set_index('loggedDate').tz_localize(None)
df = df.join(df2)
df = df.fillna(value=0)
else:
df["count"] = 0
df.index = df.index.astype(str)
return df
def create_fig_by_trend(fault_code,
start_date,
end_date,
vobc_id,
velocity=None,
apstatus=None):
start_date, end_date = util.date2str2(start_date, end_date)
title = 'vobc={}, fault={}'.format(vobc_id, fault_code)
fig = go.Figure() # make_subplots(rows=1, cols=2)
df = vobcfault_m.get_count_trend(fault_code, start_date, end_date, vobc_id,
velocity, apstatus)
if (not df.empty):
y_max = df.groupby(['LoggedDate']).max().max() * 1.01
for fc_code in sorted(df['faultCode'].unique()):
df_fc = df[df['faultCode'] == fc_code]
fig.add_trace(
go.Scatter(x=df_fc['LoggedDate'],
y=df_fc['FaultCount'],
showlegend=False,
line_color=cfg.vobc_fault_color_dict[fc_code],
stackgroup='one'))
fig.update_xaxes(title_text=title) #, type='category')
fig.update_yaxes(range=[0, y_max], title_text='fault count by date')
# df = vobcfault_m.get_count_location(fault_code, start_date, end_date, vobc_id)
# if (not df.empty):
# y_max = df.groupby(['LocationName']).max().max() * 1.01
# for fc_code in sorted(df['faultCode'].unique()):
# df_fc = df[df['faultCode']==fc_code]
# fig.add_trace(go.Bar(x=df_fc['LocationName'], y=df_fc['FaultCount'],
# showlegend = False,
# marker=dict(color=cfg.vobc_fault_color_dict[fault_code])
# ),
# row=1,col=2)
# #fig.update_xaxes(row = 1, col = 2, title_text=title)#, type='category')
# fig.update_yaxes(row = 1, col = 2, range=[0,y_max], title_text='fault count by location')
fig.update_layout(barmode='stack') #, row = 2, col = 1)
fig.update_layout(height=300,
margin=dict(l=2, r=10, t=30, b=2),
hovermode='closest')
return fig
def get_count_location(fault_code, start_date, end_date, vobcid):
fault_condition = ''
vobc_condition = ''
if (fault_code != -1 ):
fault_condition = " and faultCode = {}".format(fault_code)
if (vobcid != -1 ):
vobc_condition = " and vobcid = {}".format(vobcid)
start_date,end_date = util.date2str2(start_date,end_date)
query = ("SELECT faultName, faultCode, locationName as LocationName, count(*) as FaultCount"
" from dlr_vobc_fault"
" where vobcid <=300 and loggedAt >= '{}' and loggedAt < '{}' {} {}"
" group by faultName, faultCode, locationName LIMIT 5000").format(start_date, end_date, fault_condition, vobc_condition)
df = util.run_query(query)
return df
def get_commLoss(start_date, end_date, vobcid):
if vobcid == None or end_date == None or start_date == None:
return pd.DataFrame()
start_date, end_date = util.date2str2(start_date, end_date)
query = ("SELECT commLossCount, loggedAt, velocity from dlr_train_move"
" where vobcid = '{}' and loggedAt >= '{}' and loggedAt < '{}'"
).format(vobcid, start_date, end_date)
df = util.run_query(query)
if df is None:
df = pd.DataFrame()
return df
def create_fig_by_vobc(fault_code,
start_date,
end_date,
velocity=None,
apstatus=None):
df_res = vobcfault_m.get_count_by(fault_code, start_date, end_date,
velocity, apstatus)
df_list = []
df_list.append(
df_res[(df_res['VOBCID'] <= 150)].sort_values(by=['VOBCID']))
df_list.append(df_res[(df_res['VOBCID'] > 150)].sort_values(by=['VOBCID']))
fig = make_subplots(rows=2, cols=1, shared_yaxes=True)
i = 1
for df in df_list:
j = 0
for fault_code in sorted(df['faultCode'].unique()):
df_fc = df[df['faultCode'] == fault_code]
fig.add_trace(go.Bar(
name=vobcfault_m.get_fault_name(fault_code),
x=df_fc['VOBCID'],
y=df_fc['FaultCount'],
legendgroup=vobcfault_m.get_fault_name(fault_code),
showlegend=i == 1,
marker=dict(color=cfg.vobc_fault_color_dict[fault_code])),
row=i,
col=1)
j += 1
i += 1
y_max = df.groupby(['VOBCID']).sum().FaultCount.max() * 1.01
start_date, end_date = util.date2str2(start_date, end_date)
fig.update_layout(barmode='stack',
height=500,
hovermode='closest',
margin=dict(l=2, r=2, t=30, b=2))
fig.update_xaxes(row=1, col=1, dtick=4,
title_text='vobc id') #, type='category')
fig.update_xaxes(row=2, col=1, dtick=4,
title_text='vobc id') #, type='category')
fig.update_yaxes(range=[0, y_max], title_text='fault count')
return fig
def gen_bx_date_df_(switchId, start_date, end_date):
start_date, end_date = util.date2str2(start_date, end_date)
result = query_interval_by_date(switchId, start_date, end_date)
Ldate = []
L = []
d0 = []
mean = []
d1 = []
D98 = []
D985 = []
D99 = []
D995 = []
D999 = []
D = []
for i in range(len(result['aggregations']['opDate']['buckets'])):
Ldate.append(
result['aggregations']['opDate']['buckets'][i]["key_as_string"])
L.append(result['aggregations']['opDate']['buckets'][i]['box_interval']
['values']['1.0'])
d0.append(result['aggregations']['opDate']['buckets'][i]
['box_interval']['values']['25.0'])
mean.append(result['aggregations']['opDate']['buckets'][i]
['box_interval']['values']['50.0'])
d1.append(result['aggregations']['opDate']['buckets'][i]
['box_interval']['values']['75.0'])
D98.append(result['aggregations']['opDate']['buckets'][i]
['box_interval']['values']['98.0'])
D985.append(result['aggregations']['opDate']['buckets'][i]
['box_interval']['values']['98.5'])
D99.append(result['aggregations']['opDate']['buckets'][i]
['box_interval']['values']['99.0'])
D995.append(result['aggregations']['opDate']['buckets'][i]
['box_interval']['values']['99.5'])
D999.append(result['aggregations']['opDate']['buckets'][i]
['box_interval']['values']['99.9'])
D.append(result['aggregations']['opDate']['buckets'][i]['box_interval']
['values']['100.0'])
df = pd.DataFrame(list(
zip(Ldate, L, d0, mean, d1, D98, D985, D99, D995, D999, D)),
columns=[
"loggedDate", "min", "d0", "mean", "d1", "98%",
"98.5%", "99%", "99.5%", "99.9%", "100%"
])
return df
def gen_bx_df_(start_date, end_date):
start_date, end_date = util.date2str2(start_date, end_date)
result = query_interval_by_switch(start_date, end_date)
Swid = []
L = []
d0 = []
mean = []
d1 = []
U98 = []
U985 = []
U99 = []
U995 = []
U999 = []
U = []
for i in range(len(result['aggregations']['switchId']['buckets'])):
Swid.append(result['aggregations']['switchId']['buckets'][i]['key'])
L.append(result['aggregations']['switchId']['buckets'][i]
['box_interval']['values']['1.0'])
d0.append(result['aggregations']['switchId']['buckets'][i]
['box_interval']['values']['25.0'])
mean.append(result['aggregations']['switchId']['buckets'][i]
['box_interval']['values']['50.0'])
d1.append(result['aggregations']['switchId']['buckets'][i]
['box_interval']['values']['75.0'])
U98.append(result['aggregations']['switchId']['buckets'][i]
['box_interval']['values']['98.0'])
U985.append(result['aggregations']['switchId']['buckets'][i]
['box_interval']['values']['98.5'])
U99.append(result['aggregations']['switchId']['buckets'][i]
['box_interval']['values']['99.0'])
U995.append(result['aggregations']['switchId']['buckets'][i]
['box_interval']['values']['99.5'])
U999.append(result['aggregations']['switchId']['buckets'][i]
['box_interval']['values']['99.9'])
U.append(result['aggregations']['switchId']['buckets'][i]
['box_interval']['values']['100.0'])
df = pd.DataFrame(list(
zip(Swid, L, d0, mean, d1, U98, U985, U99, U995, U999, U)),
columns=[
'switchId', "min", "d0", "mean", "d1", "98%",
"98.5%", "99%", "99.5%", "99.9%", "100%"
])
return df
def get_switch_line_df(switchId, start_date, end_date):
start_date, end_date = util.date2str2(start_date, end_date)
df = get_switch_linechart_data(switchId, start_date, end_date)
mapping = {
'Left': 1,
'Moving': 2,
'Right': 3,
"Locked": 5,
"UnLocked": 6,
"Move Left": 8,
"No Command": 9,
"Move Right": 10
}
df = df.replace({
'positionDesc': mapping,
'statusDesc': mapping,
'switchCommandDesc': mapping
})
return df
def get_trainmove(parent_id, start_date, end_date):
start_date, end_date = util.date2str2(start_date, end_date)
if (parent_id == None or parent_id == -1):
return None
query = (
"SELECT activePassiveStatus, loggedAt, loggedDate, loopName, velocity, vobcid, trainId, maximumVelocity, doorCmd, doorStatus"
" from dlr_train_move "
" where trainId = {} and loggedAt >= '{}' and loggedAt < '{}'"
" order by loggedAt LIMIT 10000 ").format(parent_id, start_date,
end_date)
df = util.run_query(query)
if df is not None and not df.empty:
df['doorStatus'] = df['doorStatus'].apply(lambda x: x * 10 - 35)
df['doorCmd'] = df['doorCmd'].apply(lambda x: x * 10 - 35)
df['loggedAt'] = pd.to_datetime(df['loggedAt'])
return df
def gen_cmd_to_switch_by_dates(switchId, startDate, endDate, l):
mapping = {'Left': 1, 'Right': 2}
data = pd.DataFrame()
while startDate < endDate:
end = startDate + timedelta(days=2)
start_, end_ = util.date2str2(startDate, end)
l.acquire()
try:
df = get_switch_data(switchId, start_, end_)
finally:
l.release()
#df = get_switch_data(switchId, start_, end_)
if df is not None:
if df.empty == False:
df = df.replace({'positionDesc': mapping})
data = data.append(getcmd_switch_times(df))
startDate = end
data["switchId"] = switchId
return data
def get_commLoss_by_vobcid_loc_date(start_date,
end_date,
vobcid,
location,
velocity_dropdown=None,
apstatus=None,
commtype=None):
vobcs = ''
loc = ''
veld = ''
apstat = ''
commT = ''
if (vobcid != -1):
vobcs = " and vobcid = {}".format(vobcid)
if (location is not None):
loc = " and locationName = '{}'".format(location)
if velocity_dropdown is 0:
veld = " and velocity = 0"
if velocity_dropdown is 1:
veld = " and NOT(velocity = 0)"
if apstatus is 1:
apstat = " and activePassiveStatus = true"
if apstatus is 0:
apstat = " and activePassiveStatus = false"
if commtype is not None and commtype != -1:
commT = " and commType = {}".format(commtype)
start_date, end_date = util.date2str2(start_date, end_date)
query = (
"SELECT HISTOGRAM(loggedAt, INTERVAL 1 DAY) as date, count(*) as commLossCount"
" from dlr_vobc_comloss"
" where commType > 0 and commType < 6 and loggedAt >= '{}' and loggedAt < '{}' {} {} {} {} {}"
" GROUP BY date").format(start_date, end_date, vobcs, loc, veld,
apstat, commT)
df = util.run_query(query)
return df
def get_fault_list(start_date,end_date, vobc_id = None, faultCode = None, location = None, velocity= None, apstatus = None):
start_date,end_date = util.date2str2(start_date,end_date)
query = "SELECT vobcid, parentTrainId, faultName, faultCode, loggedAt, velocity, faultCodeSet, activePassiveStatus, locationName from dlr_vobc_fault where loggedAt >= '{}' and loggedAt < '{}'".format(start_date,end_date)
if vobc_id is not None and vobc_id != -1:
query += " and vobcid = {}".format(vobc_id)
if faultCode is not None and faultCode != 0 and faultCode != -1:
query += " and faultCode = {}".format(faultCode)
if (location is not None ):
query += " and locationName = '{}'".format(location)
if velocity is 0:
query += " and velocity = 0"
if velocity is 1:
query += " and NOT(velocity = 0)"
if apstatus is 1:
query += " and activePassiveStatus = true"
if apstatus is 0:
query += " and activePassiveStatus = false"
df = util.run_query(query)
return df
def get_count_by(fault_code, start_date, end_date, velocity = None, apstatus = None):
fault_condition = ''
apstat =''
vel = ''
if (fault_code != -1):
fault_condition = " and faultCode = {}".format(fault_code)
if velocity is 0:
vel += " and velocity = 0"
if velocity is 1:
vel += " and NOT(velocity = 0)"
if apstatus is 1:
apstat += " and activePassiveStatus = true"
if apstatus is 0:
apstat += " and activePassiveStatus = false"
start_date,end_date = util.date2str2(start_date,end_date)
query = ("SELECT faultName, faultCode, vobcid as VOBCID, count(*) as FaultCount"
" from dlr_vobc_fault "
" where vobcid <= 300 and faultCodeSet = True and loggedAt >= '{}' and loggedAt < '{}' {} {} {} "
" group by faultName, faultCode, vobcid "
" LIMIT 10000 ").format( start_date, end_date, fault_condition, vel, apstat)
df = util.run_query(query)
return df
from modules import module_switch_self_move as ms
import pandas as pd
from datetime import datetime as dt
from datetime import timedelta
import util as util
import config as cfg
import pytest
start_date = dt(2014, 1, 1)
end_date = dt(2020, 1, 1)
start_date, end_date = util.date2str2(start_date, end_date)
def test_get_switch_amts():
df = ms.get_switch_amts(101)
assert df is not None
assert isinstance(df, pd.DataFrame)
assert df.empty == False
def test_get_switch_count():
x = ms.get_switch_count()
assert x is not None
assert x is not 0
def test_gen_3d_df():
df = ms.gen_3d_df()
assert df is not None
assert isinstance(df, pd.DataFrame)
