daily.py
3.14 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
from typing import List
from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session, aliased
from sqlalchemy import func
from presupuestov1 import model, schema, schemas, models
router = APIRouter(
prefix='/diario',
tags=['diario'],
)
###############################################################################
# GET
###############################################################################
@router.get('/status', response_model=schemas.daily.UpdateStatus)
def fetch_status(
db: Session = Depends(model.get_db),
):
Daily = models.daily.UpdateStatus
return db.query(Daily).order_by(
Daily.fecha_actualizacion.desc()
).first()
@router.get('/clasificadores', response_model=List[schemas.daily.EjecucionClasificadores])
def fetch_clasificadores(
db: Session = Depends(model.get_db),
query: schema.DailyParams = Depends(),
):
Daily = models.daily.EjecucionClasificadores
filters = []
if query.transferencias is not None:
filters.append(Daily.transferencias == query.transferencias)
if query.dias is not None:
filters.append(Daily.dias == query.dias)
return db.query(Daily).filter(*filters).order_by(Daily.devengado.desc()).all()
@router.get('/entidades', response_model=List[schemas.daily.EjecucionEntidades])
def fetch_entidades(
db: Session = Depends(model.get_db),
query: schema.DailyParams = Depends(),
):
Daily = models.daily.EjecucionEntidades
transferencias = query.transferencias or False # :O
page = query.page or 1
page_size = 10
filters = []
if not transferencias:
filters.append(Daily.transferencias == query.transferencias)
if query.dias is not None:
filters.append(Daily.dias == query.dias)
if query.entidad is not None:
filters.append(Daily.entidad == query.entidad)
return db.query(Daily).filter(*filters).order_by(Daily.devengado.desc()).offset(
(page - 1) * page_size
).limit(page_size).all()
@router.get('/timeline', response_model=List[schemas.daily.Timeline])
def fetch_timeline(
db: Session = Depends(model.get_db),
query: schema.DailyParams = Depends(),
):
Daily = models.daily.Timeline
page = query.page or 1
page_size = 10
if query.entidad is not None:
return db.query(Daily).filter(
Daily.entidad == query.entidad
).order_by(
Daily.devengado.desc()
).offset(
(page - 1) * page_size
).limit(page_size).all()
# top 10 entidades
top_entities = db.query(
Daily.entidad
).group_by(
Daily.entidad
).order_by(
func.sum(Daily.devengado).desc()
).limit(10).subquery()
# ordena las filas por devengado desc por cada una de las top 10 entidades
ranked = db.query(
Daily,
func.row_number().over(
partition_by=Daily.entidad,
order_by=Daily.devengado.desc()
).label('row_num')
).filter(
Daily.entidad.in_(top_entities)
).subquery()
# top 10 filas por cada una de las top 10 entidades
RankedTimeline = aliased(Daily, ranked)
return db.query(RankedTimeline).filter(
ranked.c.row_num <= page_size
).order_by(
RankedTimeline.entidad,
RankedTimeline.devengado.desc()
).all()
@router.get('/timeseries', response_model=List[schemas.daily.TimeSeries])
def fetch_timeseries(
db: Session = Depends(model.get_db),
):
return db.query(models.daily.TimeSeries).all()