def cve_count_by_year_month(year, month):
"""
Get CVE count by year
---
tags:
- CVE
parameters:
- name: year
in: path
required: true
type: integer
description: Year with certain amount of CVEs
- name: month
in: path
required: true
type: integer
description: Month of a year with certain amount of CVEs
responses:
200:
description: Get CVE count
"""
count = CVE.query.filter(year == db.extract("year", CVE.published_date),
month == db.extract("month",
CVE.published_date)).count()
return {"cve_count": count}
def stats():
daily = db.session.query(db.func.count(models.Processed.title), models.Processed).group_by(db.extract('day', models.Processed.time)).order_by(models.Processed.time.desc()).all()
dailyJSON = list()
for day in daily:
dailyJSON.append({"y": day[0], "x": day[1].time.date().strftime("%Y-%m-%d")})
monthly = db.session.query(db.func.count(models.Processed.title), models.Processed).filter(models.Processed.time >= datetime.datetime.now() - datetime.timedelta(weeks=53)).group_by(db.extract('month', models.Processed.time)).order_by(models.Processed.time.desc()).all()
monthlyJSON = list()
for monthly in monthly:
monthlyJSON.append({"y": monthly[0], "x": monthly[1].time.date().strftime("%Y-%m")})
hourly = db.session.query(db.func.count(db.extract('hour', models.Processed.time)), models.Processed).filter(models.Processed.time >= datetime.datetime.now() - datetime.timedelta(hours=24)).group_by(db.extract('hour', models.Processed.time)).order_by(models.Processed.time.asc()).all()
hourlyJSON = list()
for hour in hourly:
hourlyJSON.append({"y": hour[0], "x": hour[1].time.strftime("%Y-%m-%d %H")})
maxhourly = db.session.query(db.func.count(db.extract('hour', models.Processed.time)), models.Processed).group_by(db.extract('hour', models.Processed.time)).order_by(db.extract('hour', models.Processed.time).desc()).all()
maxhourlyJSON = list()
for hour in maxhourly:
maxhourlyJSON.append({"y": hour[0], "x": hour[1].time.strftime("%H")})
playperuser = db.session.query(db.func.count(models.Processed.title), models.Processed).filter(models.Processed.time >= datetime.datetime.now() - datetime.timedelta(days=30)).group_by(models.Processed.user).order_by(db.func.count(models.Processed.title).desc()).all()
playperuserJSON = list()
for play in playperuser:
playperuserJSON.append({"y": play[0], "x": play[1].user})
playperuserEver = db.session.query(db.func.count(models.Processed.title), models.Processed).group_by(models.Processed.user).order_by(db.func.count(models.Processed.title).desc()).all()
playperuserEverJSON = list()
for cplay in playperuserEver:
playperuserEverJSON.append({"y": cplay[0], "x": cplay[1].user})
return render_template('stats.html', hourly=hourlyJSON, daily=dailyJSON, monthly=monthlyJSON, maxhourly=maxhourlyJSON, userplays=playperuserJSON, userplaysalltime=playperuserEverJSON, title=_('Statistics'))
def archives(self, year, month):
if not year:
return self
criteria = [db.extract('year', Article.created) == year]
if month:
criteria.append(db.extract('month', Article.created) == month)
q = reduce(db.and_, criteria)
return self.public().filter(q)
def get_birthdays(imp_id):
imp = Imports.query.get_or_404(imp_id)
response = dict(data=dict())
relatives = defaultdict(list)
query = db.session.query(Relatives).filter(
Relatives.import_id == imp_id).all()
for i in query:
relatives[i.citizen_id].append(i.relative)
for month in range(1, 13):
response['data'][str(month)] = list()
presents = defaultdict(int)
birthday_citizens = imp.citizens.filter(
db.extract('month', Citizens.birth_date) == month).all()
for c in birthday_citizens:
for r in relatives[c.citizen_id]:
presents[r] += 1
for c_id, count in presents.items():
response['data'][str(month)].append({
'citizen_id': c_id,
'presents': count
})
return jsonify(response)
def cve_last_week():
"""
Get CVEs from last week of the year
---
tags:
- CVE
responses:
200:
description: Get CVEs from last week of the year
408:
description: Request timeout
"""
# FIXME: get rid of hardcoded year
cves = CVE.query.filter(2019 == db.extract("year", CVE.published_date),
db.extract("week",
CVE.published_date) == 52).all()
return {"cves": cves_schema.dump(cves)}
def cve_last_year():
"""
Get CVEs from current year
---
tags:
- CVE
responses:
200:
description: Get CVEs from current year
408:
description: Request timeout
"""
current_time = datetime.now()
cves = CVE.query.filter(
current_time.year == db.extract("year", CVE.published_date)).all()
return {"cves": cves_schema.dump(cves)}
def cve_latest():
"""
Get 30 latest CVEs
---
tags:
- CVE
responses:
200:
description: Get CVEs from last day of the year
408:
description: Request timeout
"""
current_time = datetime.now()
cves = CVE.query.filter(
current_time.year == db.extract("year", CVE.published_date)).order_by(
CVE.published_date.desc()).limit(30).all()
return {"cves": cves_schema.dump(cves)}
def activities_in_month(self, month):
return self.activities.filter(db.extract('year', Activity.timestamp) == datetime.now().year) \
.filter(db.extract('month', Activity.timestamp) == month)
def overview(oid):
o = Object.query.filter_by(oid=oid).first()
if not o: return abort(404)
s = session_to_user(request, session, user_id=o.owner, or_admin=True)
if s.auth_error: return auth_error_return_helper(s)
# daterange & datescope
daterange = timeparse(request.args.get('range', ''))
datescope = request.args.get('scope', None)
if type(daterange) == int:
datestart = datetime.datetime.now() - datetime.timedelta(seconds=daterange)
else:
datestart = o.date_created
if datescope not in ('m', 'w', 'd'):
if datestart > (datetime.datetime.now() - datetime.timedelta(days=90)):
datescope = 'd'
elif datestart > (datetime.datetime.now() - datetime.timedelta(days=630)):
datescope = 'w'
else:
datescope = 'm'
datestart = datestart.replace(hour=0, minute=0, second=0, microsecond=0)
if datescope == 'w':
datestart = find_monday(datestart)
elif datescope == 'm':
datestart = datestart.replace(day=1)
# prepare dataset
data = { 'labels': [] }
for viewtype in View.type.type.enums:
data[viewtype] = {}
# fetch data from db
q = db.session
qc = [View.type, db.func.count('*').label('count'), db.extract('year', View.date_created).label('_year')]
qg = ['type']
# http://www.w3schools.com/sql/func_extract.asp
if datescope in ('m', 'd'):
qc.append(db.func.LPAD(db.extract('month', View.date_created), 2, '0').label('_month'))
qg.append('_month')
if datescope == 'w':
qc.append(db.func.LPAD(db.extract('week', View.date_created), 2, '0').label('_week'))
qg.append('_week')
if datescope == 'd':
qc.append(db.func.LPAD(db.extract('day', View.date_created), 2, '0').label('_day'))
qg.append('_day')
q = q.query(*qc)
q = q.group_by(*qg)
q = q.filter_by(object=o.id)
q = q.filter(View.date_created >= datestart)
q = q.all()
# prepare labels
for value in q:
# value: TYPE, COUNT, YEAR, [MONTH,] [WEEK|DAY]
# for some reason value[2]-value[4] is binary on some systems, I have no idea why
if datescope in ('m', 'w'):
label = '%s-%s' % (value[2].decode('utf-8') if type(value[2]) == bytes else value[2],
value[3].decode('utf-8') if type(value[3]) == bytes else value[3])
elif datescope == 'd':
label = '%s-%s-%s' % (value[2].decode('utf-8') if type(value[2]) == bytes else value[2],
value[3].decode('utf-8') if type(value[3]) == bytes else value[3],
value[4].decode('utf-8') if type(value[4]) == bytes else value[4])
data[value[0]][str(label)] = value[1]
# add dates without views to labels
if datescope == 'm':
dateformat = '%Y-%m'
elif datescope == 'w':
dateformat = '%Y-%W'
elif datescope == 'd':
dateformat = '%Y-%m-%d'
data['labels'] = list(sorted(set([date.strftime(dateformat) for date in make_daterange(datestart, datetime.datetime.now(), datescope)])))
# make dict to list
for viewtype in View.type.type.enums:
newData = []
for date in data['labels']:
try:
newData.append(data[viewtype][date])
except KeyError:
newData.append(0)
# empty dict if there is no view for that viewtype at all
if all(x == 0 for x in newData):
newData = []
else:
data[viewtype] = newData
return render_template('stats/overview.html', o=o, s=s, data=data)
def stats():
daily = db.session.query(db.func.count(
models.Processed.title), models.Processed).group_by(
db.extract('day', models.Processed.time)).order_by(
models.Processed.time.desc()).all()
dailyJSON = list()
for day in daily:
dailyJSON.append({
"y": day[0],
"x": day[1].time.date().strftime("%Y-%m-%d")
})
monthly = db.session.query(db.func.count(
models.Processed.title), models.Processed).filter(
models.Processed.time >= datetime.datetime.now() -
datetime.timedelta(weeks=53)).group_by(
db.extract('month', models.Processed.time)).order_by(
models.Processed.time.desc()).all()
monthlyJSON = list()
for monthly in monthly:
monthlyJSON.append({
"y": monthly[0],
"x": monthly[1].time.date().strftime("%Y-%m")
})
hourly = db.session.query(
db.func.count(db.extract('hour', models.Processed.time)),
models.Processed).filter(
models.Processed.time >= datetime.datetime.now() -
datetime.timedelta(hours=24)).group_by(
db.extract('hour', models.Processed.time)).order_by(
models.Processed.time.asc()).all()
hourlyJSON = list()
for hour in hourly:
hourlyJSON.append({
"y": hour[0],
"x": hour[1].time.strftime("%Y-%m-%d %H")
})
maxhourly = db.session.query(
db.func.count(db.extract('hour', models.Processed.time)),
models.Processed).group_by(db.extract(
'hour', models.Processed.time)).order_by(
db.extract('hour', models.Processed.time).desc()).all()
maxhourlyJSON = list()
for hour in maxhourly:
maxhourlyJSON.append({"y": hour[0], "x": hour[1].time.strftime("%H")})
playperuser = db.session.query(db.func.count(
models.Processed.title), models.Processed).filter(
models.Processed.time >= datetime.datetime.now() -
datetime.timedelta(days=30)).group_by(
models.Processed.user).order_by(
db.func.count(models.Processed.title).desc()).all()
playperuserJSON = list()
for play in playperuser:
playperuserJSON.append({"y": play[0], "x": play[1].user})
playperuserEver = db.session.query(
db.func.count(models.Processed.title),
models.Processed).group_by(models.Processed.user).order_by(
db.func.count(models.Processed.title).desc()).all()
playperuserEverJSON = list()
for cplay in playperuserEver:
playperuserEverJSON.append({"y": cplay[0], "x": cplay[1].user})
return render_template('stats.html',
hourly=hourlyJSON,
daily=dailyJSON,
monthly=monthlyJSON,
maxhourly=maxhourlyJSON,
userplays=playperuserJSON,
userplaysalltime=playperuserEverJSON,
title=_('Statistics'))