def __create_table_query(self, columns):
self.logger.info(
"Dropping existing table - {schema}.{table_name}".format(
schema=self.schema, table_name=self.table))
q = "DROP TABLE IF EXISTS {schema}.{table_name};".format(
schema=self.schema, table_name=self.table)
run_query(self.conn, q)
q = "CREATE SCHEMA IF NOT EXISTS {schema};CREATE TABLE {schema}.{table_name} (".format(
schema=self.schema, table_name=self.table)
for column in columns:
q += "{column_name} VARCHAR(8000),".format(column_name=column)
q = q[:-1] + ");"
return q
def get_operating_hours_by_month(start_date, end_date, trainId):
query = (
" SELECT HISTOGRAM(loggedAt, INTERVAL 1 hour) as h, vobcid from dlr_train_move"
" where loggedAt >= '{}' and loggedAt < '{}' and vobcid ={} group by vobcid, h"
).format(start_date, end_date, trainId)
L = util.run_query(query)
return len(L.index)
def get_switch_linechart_data(switchId, start_date, end_date):
query = (
"SELECT switchCommand, switchCommandDesc,statusDesc,positionDesc, loggedAt, switchId from dlr_switch_move"
" where switchId = {} and loggedAt >= '{}' and loggedAt < '{}'"
" order by loggedAt ").format(switchId, start_date, end_date)
L = util.run_query(query)
return L
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_fault_name(faultCode):
query = ("SELECT faultName FROM dlr_vobc_fault "
" where faultCode = {} group by faultName").format(faultCode)
L = util.run_query(query)
if L.empty == False:
return L["faultName"].tolist()
return []
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_train_mileage_by_loop(vobcid, start_date, end_date):
query = (
"SELECT HISTOGRAM(loggedAt, INTERVAL 30 MINUTES) as time, loopName, SUM(distance) as Distance FROM dlr_train_move "
" where vobcid = {} and loggedAt >= '{}' and loggedAt < '{}' group by time, loopName"
).format(vobcid, start_date, end_date)
L = util.run_query(query)
return L
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 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_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_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 __import_file(self):
content = pd.read_csv(self.file_path)
columns = list(content.columns)
columns = [c.replace(' ', '_') for c in columns]
create_table_query = self.__create_table_query(columns)
self.logger.info("Creating table {schema}.{table_name}".format(
schema=self.schema, table_name=self.table))
run_query(self.conn, create_table_query)
self.logger.info("Importing file...")
run_query(
self.conn,
" COPY {schema}.{table} FROM '{file_path}' DELIMITER ',' CSV HEADER;"
.format(schema=self.schema,
table=self.table,
file_path=os.path.abspath(self.file_path)))
self.logger.info("Import Completed!")
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_posts_ids(limit, offset):
key = create_posts_keys(limit, offset)
latest = memcache.get(key)
if latest is None:
keys = util.run_query(create_posts_query(), limit, offset=offset, keys_only=True)
latest = ','.join([str(k.id()) for k in keys])
memcache.add(key, latest, 86400)
return [long(k) for k in latest.split(',')]
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_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_unlock_count(start_date, end_date):
query = (
"SELECT switchId, COUNT(*) as count from dlr_switch_move"
" where interval > 0 and loggedAt >= '{}' and loggedAt < '{}'"
" and intervalDesc in ('Moving Time to Left' , 'Moving Time to Right' ) "
" and duration >= 10 group by switchId").format(start_date, end_date)
L = util.run_query(query)
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 index(page):
tag = util.param('tag')
y = util.param('year')
m = util.param('month')
if tag or y or m:
return abort(404)
posts = create_posts_query()
title = ''
if page > 1:
title = u" {0}-я страница".format(page)
all_posts = util.run_query(create_posts_query())
archieve = {}
for year_and_month, group in itertools.groupby(all_posts, key=lambda post: (post.created.year, post.created.month)):
if year_and_month[0] not in archieve:
archieve[year_and_month[0]] = []
# iterate each month in the year and month group
for m, months in itertools.groupby(group, key=lambda p: p.created.month):
posts_count = len(filter(None, months))
item = (m, posts_count)
archieve[year_and_month[0]].append(item)
# all posts for each year counting
for y in archieve:
total = 0
for year_and_month in archieve[y]:
total += year_and_month[1]
item = (y, total)
# all posts for a year fake item to output year's statistic
archieve[y].insert(0, item)
posts = get_paginator(util.run_query(posts), page)
return render_template(
'news/index.html',
title=title,
posts=posts,
archieve=archieve,
tags=create_tag_rank(all_posts),
)
def get_switch_data(switchId, start_date, end_date):
query = (
"SELECT switchCommand, switchCommandDesc,statusDesc,positionDesc, loggedAt, switchId from dlr_switch_move"
" where switchId = {} and loggedAt >= '{}' and loggedAt < '{}'"
" order by loggedAt ").format(switchId, start_date, end_date)
df = util.run_query(query)
if df.empty == True:
return
df = df.set_index("loggedAt")
df = df.between_time("6:00", "00:00")
df = df.reset_index()
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 get_first_fault_time(op_date, fault_code, vobc_id):
op_date = util.str2date1(op_date)
query = ("SELECT min(loggedAt) as fcStart"
" from dlr_vobc_fault"
" where faultCode = {} and vobcid ={} and loggedDate = '{}'").format(fault_code, vobc_id, op_date.date())
df = util.run_query(query)
if (df is None):
return op_date
dt_str = numpy.datetime_as_string(df['fcStart'][0].to_datetime64(), unit='s')
return dateparser.parse(dt_str)
def posts_json():
year = util.param('year', int)
month = util.param('month', int)
q = create_posts_query()
if year:
if year and month:
current_month = datetime.datetime(year, month, 1)
next_month = util.add_months(datetime.datetime(year, month, 1), 1)
else:
current_month = datetime.datetime(year, 1, 1)
next_month = datetime.datetime(year+1, 1, 1)
q = q.filter(Post.created >= current_month, Post.created < next_month)
q = util.run_query(q)
else:
tag = util.param('tag')
if tag:
q = q.filter(Post.tags.IN([tag]))
q = util.run_query(q)
else:
offset = util.param('offset', int) or 0
limit = util.param('limit', int) or config.ATOM_FEED_LIMIT
q = util.run_query(q, limit, offset=offset)
return util.jsonify_model_dbs(q)
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 recent_feed():
feed = AtomFeed('{0} feed'.format(config.CONFIG_DB.brand_name),
feed_url=flask.request.url, url=flask.request.url_root)
limit = config.ATOM_FEED_LIMIT
if util.param('limit'):
limit = util.param('limit', int)
articles = util.run_query(create_posts_query(), limit)
make_external = lambda url: urljoin(flask.request.url_root, url)
for article in articles:
feed.add(article.title, unicode(article.short_text),
content_type='html',
author="Alexander Egorov",
url=make_external(flask.url_for('news.post', key_id=article.key.id())),
updated=article.modified,
published=article.created)
return feed.get_response()
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 page(self, number):
"Returns a Page object for the given 1-based page number."
number = self.validate_number(number)
bottom = (number - 1) * self.per_page
# Get one more entity than requested to see if
# we have one more page
if type(self.object_list) == list:
top = bottom + self.per_page
page_items = self.object_list[bottom:top+1]
else:
page_items = run_query(self.object_list, self.per_page, offset=bottom)
if not page_items:
if number == 1 and self.allow_empty_first_page:
pass
else:
raise EmptyPage('That page contains no results')
# Check if there is a next page
has_next = number < self.num_pages
return Page(page_items[:self.per_page], number, self, has_next)
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_trend(fault_code, start_date, end_date, vobcid, velocity = None, apstatus = None):
fault_condition = ''
vobc_condition = ''
veld = ''
apstat = ''
if (fault_code != -1 ):
fault_condition = " and faultCode = {}".format(fault_code)
if (vobcid != -1 ):
vobc_condition = " and vobcid = {}".format(vobcid)
if velocity is 0:
veld = " and velocity = 0"
if velocity 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 faultName, faultCode, loggedDate as LoggedDate, count(*) as FaultCount"
" from dlr_vobc_fault"
" where vobcid <=300 and faultCodeSet = True and loggedAt >= '{}' and loggedAt < '{}' {} {} {} {}"
" group by faultName, faultCode, loggedDate LIMIT 5000").format(start_date, end_date, fault_condition, vobc_condition, veld, apstat)
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
def test_runquery():
df = util.run_query(
"SELECT faultName, loggedAt, velocity from dlr_vobc_fault where loggedAt >= '2014-01-01T00:00:00' and loggedAt < '2015-04-25T00:13:26.017995' LIMIT 2000 "
)
assert df['loggedAt'].count() > 100
def get_all_fault():
query = "SELECT faultName, faultCode, count(*) as FaultCount from dlr_vobc_fault group by faultName, faultCode LIMIT 50"
df = util.run_query(query)
return df
