def do_job(self):
"""
SELECT *, coalesce(end_time,now)-start_time
FROM task t, subject s
WHERE t.subject_id=s.id AND start_time > GetDate() - @days
ORDER BY start_time
:return:
"""
session = DBSession()
now = datetime.now()
time_worked = (func.julianday(func.coalesce(Task.end_time,now)) - func.julianday(Task.start_time)) * 86400
query = session.query(Task.start_time,
func.coalesce(Task.end_time, now),
time_worked,
Subject.title,
Task.title) \
.filter(Subject.id==Task.subject_id) \
.filter(func.date(Task.start_time) > func.date('now', '-%s day' % self.args.days)) \
.order_by(Task.start_time)
print '\n'
table = PrettyTable(['Start', 'End', 'Time', 'Subject', 'Title'])
table.align["Title"] = "l"
total_time = 0
day_total = 0
last_date = None
for row in query:
if last_date == None:
last_date = row[0].date()
if row[0].date() != last_date:
table.add_row([
'', '', timedelta(seconds=round(day_total)), '', ''
])
last_date = row[0].date()
day_total = 0
day_total += row[2]
total_time += row[2]
table.add_row([
row[0],
row[1],
timedelta(seconds=round(row[2])),
row[3],
row[4],
])
if day_total > 0:
table.add_row([
'', '', timedelta(seconds=round(day_total)), '', ''
])
print table
print 'Total Work time: %s' % timedelta(seconds=total_time)
print
def _get_dep_statuses(self, ti, session, dep_context):
TI = airflow.models.TaskInstance
TR = airflow.models.TriggerRule
# Checking that all upstream dependencies have succeeded
if not ti.task.upstream_list:
yield self._passing_status(
reason="The task instance did not have any upstream tasks.")
raise StopIteration
if ti.task.trigger_rule == TR.DUMMY:
yield self._passing_status(reason="The task had a dummy trigger rule set.")
raise StopIteration
# TODO(unknown): this query becomes quite expensive with dags that have many
# tasks. It should be refactored to let the task report to the dag run and get the
# aggregates from there.
qry = (
session
.query(
func.coalesce(func.sum(
case([(TI.state == State.SUCCESS, 1)], else_=0)), 0),
func.coalesce(func.sum(
case([(TI.state == State.EXCLUDED, 1)], else_=0)), 0),
func.coalesce(func.sum(
case([(TI.state == State.SKIPPED, 1)], else_=0)), 0),
func.coalesce(func.sum(
case([(TI.state == State.FAILED, 1)], else_=0)), 0),
func.coalesce(func.sum(
case([(TI.state == State.UPSTREAM_FAILED, 1)], else_=0)), 0),
func.count(TI.task_id),
)
.filter(
TI.dag_id == ti.dag_id,
TI.task_id.in_(ti.task.upstream_task_ids),
TI.execution_date == ti.execution_date,
TI.state.in_([
State.SUCCESS, State.FAILED, State.EXCLUDED,
State.UPSTREAM_FAILED, State.SKIPPED]),
)
)
successes, excluded, skipped, failed, upstream_failed, done = qry.first()
# Add excluded tasks into successful tasks as they are equivalent for
# dependency purposes. This is done in this way, not using the
# state_for_dependents function, due to the constraints of SQLAlchemy
# queries.
successes = successes + excluded
for dep_status in self._evaluate_trigger_rule(
ti=ti,
successes=successes,
skipped=skipped,
failed=failed,
upstream_failed=upstream_failed,
done=done,
flag_upstream_failed=dep_context.flag_upstream_failed,
session=session):
yield dep_status
def _total(self, balance_uid):
model = request.environ["sqlalchemy.model"]
db = request.environ["sqlalchemy.session"]
incomes = db.execute(
select(
[func.coalesce(func.sum(model.BalanceChange.c.amount), 0)],
and_(model.BalanceChange.c.balance_uid == balance_uid, model.BalanceChange.c.is_income == True),
from_obj=[model.balance_changes_table],
)
).fetchone()[0]
expenses = db.execute(
select(
[func.coalesce(func.sum(model.BalanceChange.c.amount), 0)],
and_(model.BalanceChange.c.balance_uid == balance_uid, model.BalanceChange.c.is_income == False),
from_obj=[model.balance_changes_table],
)
).fetchone()[0]
try:
return incomes - expenses
except:
log.error("", exc_info=1)
return 0
def get_info_op_bphtb(cls, p_kode, p_tahun):
pkey = FixLength(NOP)
pkey.set_raw(p_kode)
q = pbb_DBSession.query(cls.luas_bumi_sppt, cls.luas_bng_sppt,
cls.njop_bumi_sppt, cls.njop_bng_sppt, DatObjekPajak.jalan_op,
DatObjekPajak.blok_kav_no_op, DatObjekPajak.rt_op, DatObjekPajak.rw_op,
cls.nm_wp_sppt.label('nm_wp'),
func.coalesce(SpptOpBersama.luas_bumi_beban_sppt,0).label('luas_bumi_beban'),
func.coalesce(SpptOpBersama.luas_bng_beban_sppt,0).label('luas_bng_beban'),
func.coalesce(SpptOpBersama.njop_bumi_beban_sppt,0).label('njop_bumi_beban'),
func.coalesce(SpptOpBersama.njop_bng_beban_sppt,0).label('njop_bng_beban'))
q = q.filter(
cls.kd_propinsi == DatObjekPajak.kd_propinsi,
cls.kd_dati2 == DatObjekPajak.kd_dati2,
cls.kd_kecamatan == DatObjekPajak.kd_kecamatan,
cls.kd_kelurahan == DatObjekPajak.kd_kelurahan,
cls.kd_blok == DatObjekPajak.kd_blok,
cls.no_urut == DatObjekPajak.no_urut,
cls.kd_jns_op == DatObjekPajak.kd_jns_op)
q = q.outerjoin(SpptOpBersama)
return q.filter(cls.kd_propinsi == pkey['kd_propinsi'],
cls.kd_dati2 == pkey['kd_dati2'],
cls.kd_kecamatan == pkey['kd_kecamatan'],
cls.kd_kelurahan == pkey['kd_kelurahan'],
cls.kd_blok == pkey['kd_blok'],
cls.no_urut == pkey['no_urut'],
cls.kd_jns_op == pkey['kd_jns_op'],
cls.thn_pajak_sppt == p_tahun)
def _ops_for_date_range(self, balance_uids, start_date, end_date, tags = [], change_categories = []):
model = self.get_sa_model()
db = self.get_sa_session()
conditions = [or_(*[model.BalanceChange.balance_uid == balance_uid for balance_uid in balance_uids])]
if start_date:
conditions.append(model.BalanceChange.occurred_on >= start_date)
if end_date:
conditions.append(model.BalanceChange.occurred_on <= end_date)
if isinstance(tags, list) and len(tags) > 0:
conditions.extend([model.BalanceChange.tags.any(tag=tag.strip().lower()) for tag in tags if tag is not None and tag.strip() != ''])
if isinstance(change_categories, list) and len(change_categories) > 0:
conditions.extend([model.BalanceChange.change_category_uid == value.strip() for value in change_categories if value is not None and value.strip() != ''])
try:
summary = db.execute(select([
func.coalesce(func.sum(
case([[model.BalanceChange.amount<0, model.BalanceChange.amount]], else_=0).label("expenses"))),
func.coalesce(func.sum(
case([[model.BalanceChange.amount>0, model.BalanceChange.amount]], else_=0).label("incomes")))
],
and_(*conditions),
from_obj=[model.balance_changes_table])).fetchone()
return {
"expenses": summary[0],
"incomes": summary[1],
}
except:
log.error(_("Can't get summary"), exc_info=1)
return 0
def packages_all_licenses_in_files(package_id):
pac = db.packages.alias()
pfi = db.packages_files.alias()
fil = db.files.alias()
fli = db.files_licenses.alias()
lic1 = license_approved_names().alias()
lic2 = license_approved_names().alias()
return (select([
pac.c.package_id,
pac.c.name .label('package_name'),
func.coalesce(lic1.c.short_name, lic2.c.short_name).label('license_short_name'),
func.count() .label('license_found_count')
])
.select_from(
pac
.join(pfi, pac.c.package_id == pfi.c.package_id)
.join(fil, pfi.c.file_id == fil.c.file_id)
.join(lic1, pfi.c.concluded_license_id == lic1.c.license_id, isouter=True)
.join(fli, fil.c.file_id == fli.c.file_id, isouter=True)
.join(lic2, fli.c.license_id == lic2.c.license_id)
)
.where(pac.c.package_id == package_id)
.group_by(
pac.c.package_id,
pac.c.name,
func.coalesce(lic1.c.short_name, lic2.c.short_name)
)
)
def results_by_district(self):
""" Returns the results aggregated by the distict. """
counted = func.coalesce(func.bool_and(BallotResult.counted), False)
yeas = func.sum(BallotResult.yeas)
nays = func.sum(BallotResult.nays)
yeas_percentage = 100 * yeas / (
cast(func.coalesce(func.nullif(yeas + nays, 0), 1), Float)
)
nays_percentage = 100 - yeas_percentage
accepted = case({True: yeas > nays}, counted)
results = self.results.with_entities(
BallotResult.district.label('name'),
counted.label('counted'),
accepted.label('accepted'),
yeas.label('yeas'),
nays.label('nays'),
yeas_percentage.label('yeas_percentage'),
nays_percentage.label('nays_percentage'),
func.sum(BallotResult.empty).label('empty'),
func.sum(BallotResult.invalid).label('invalid'),
func.sum(BallotResult.eligible_voters).label('eligible_voters'),
func.array_agg(BallotResult.entity_id).label('entity_ids')
)
results = results.group_by(BallotResult.district)
results = results.order_by(None).order_by(BallotResult.district)
return results
def advanced_search(self, **kwargs):
super(TurnoversQueryBuilder, self).advanced_search(**kwargs)
self._filter_account(kwargs.get('account_id'))
if 'date_from' in kwargs or 'date_to' in kwargs:
self._filter_date(
kwargs.get('date_from'), kwargs.get('date_to')
)
self._cashflows_from_sub = self._cashflows_from_sub.subquery()
self._cashflows_to_sub = self._cashflows_to_sub.subquery()
self.query = (
self.query
.outerjoin(
self._cashflows_from_sub,
self._cashflows_from_sub.c.account_item_id == AccountItem.id
)
.outerjoin(
self._cashflows_to_sub,
self._cashflows_to_sub.c.account_item_id == AccountItem.id
)
)
balance_condition = or_(
self._cashflows_to_sub.c.revenue != None,
self._cashflows_from_sub.c.expenses != None
)
balance_expression = (
func.coalesce(self._cashflows_to_sub.c.revenue, 0)
- func.coalesce(self._cashflows_from_sub.c.expenses, 0)
)
balance_case = (balance_condition, balance_expression)
self.update_fields({
'expenses': self._cashflows_from_sub.c.expenses,
'revenue': self._cashflows_to_sub.c.revenue,
'balance': case([balance_case,], else_=None),
})
ResourcesQueryBuilder.build_query(self)
def test_functional_ix_two(self):
m1 = MetaData()
m2 = MetaData()
t1 = Table(
'foo', m1,
Column('id', Integer, primary_key=True),
Column('email', String(50)),
Column('name', String(50))
)
Index(
"email_idx",
func.coalesce(t1.c.email, t1.c.name).desc(), unique=True)
t2 = Table(
'foo', m2,
Column('id', Integer, primary_key=True),
Column('email', String(50)),
Column('name', String(50))
)
Index(
"email_idx",
func.coalesce(t2.c.email, t2.c.name).desc(), unique=True)
with assertions.expect_warnings(
"Skipped unsupported reflection",
"autogenerate skipping functional index"
):
diffs = self._fixture(m1, m2)
eq_(diffs, [])
def _filter_price(self, price_from, price_to):
if price_from:
self.query = self.query.filter(
func.coalesce(LeadItem.price_from, 0) >= price_from,
)
if price_to:
self.query = self.query.filter(
func.coalesce(LeadItem.price_to, 0) <= price_to,
)
def _filter_payment(self, payment_from, payment_to):
if payment_from:
self.query = self.query.filter(
func.coalesce(self._sum_payments.c.payments, 0) >= payment_from
)
if payment_to:
self.query = self.query.filter(
func.coalesce(self._sum_payments.c.payments, 0) <= payment_to
)
def percentage_by_district(self):
""" Returns the percentage of votes aggregated by the distict. Includes
uncounted districts and districts with no results available.
"""
results = self.election.results
results = results.join(ElectionResult.candidate_results)
results = results.filter(CandidateResult.candidate_id == self.id)
results = results.with_entities(
ElectionResult.district.label('name'),
func.array_agg(ElectionResult.entity_id).label('entities'),
func.coalesce(
func.bool_and(ElectionResult.counted), False
).label('counted'),
func.sum(ElectionResult.accounted_ballots).label('total'),
func.sum(CandidateResult.votes).label('votes'),
)
results = results.group_by(ElectionResult.district)
results = results.order_by(None)
results = results.all()
percentage = {
r.name: {
'counted': r.counted,
'entities': r.entities,
'percentage': 100 * (r.votes / r.total) if r.total else 0.0
} for r in results
}
empty = self.election.results
empty = empty.with_entities(
ElectionResult.district.label('name'),
func.array_agg(ElectionResult.entity_id).label('entities'),
func.coalesce(
func.bool_and(ElectionResult.counted), False
).label('counted')
)
empty = empty.group_by(ElectionResult.district)
empty = empty.order_by(None)
for result in empty:
update = (
result.name not in percentage
or (
set(percentage[result.name]['entities'])
!= set(result.entities)
)
)
if update:
percentage[result.name] = {
'counted': result.counted,
'entities': result.entities,
'percentage': 0.0
}
return percentage
def index(self, format='html'):
"""GET /rdfdocs: All items in the collection"""
# url('rdfdocs')
rdfdocs = Session.query(model.RDFDoc).filter(model.RDFDoc.active==True).join(model.RDFDoc.current_stats)
c.query_string = '?'
c.search = ''
if request.GET.has_key('search'):
rdfdocs = rdfdocs.filter(model.RDFDoc.name.ilike("%%%s%%" % request.GET['search']))
c.query_string += 'search=%s&' % request.GET['search']
c.search = request.GET['search']
if request.GET.has_key('errors'):
rdfdocs = rdfdocs.filter(model.StatResult.errors!=None)
c.query_string += 'errors=1&'
if request.GET.has_key('valid'):
rdfdocs = rdfdocs.filter(model.StatResult.errors==None)
c.query_string += 'valid=1&'
if request.GET.has_key('sparql'):
rdfdocs = rdfdocs.filter(model.RDFDoc.format=='sparql')
c.query_string += 'sparql=1&'
if request.GET.has_key('dumps'):
rdfdocs = rdfdocs.filter(model.RDFDoc.format!='sparql')
c.query_string += 'dumps=1&'
c.sort_order = request.GET.get('sort')
if request.GET.has_key('sort'):
if request.GET['sort'] == 'triples':
c.rdfdocs = rdfdocs.order_by(desc(func.coalesce(model.StatResult.triples, '0')))
elif request.GET['sort'] == 'warnings':
c.rdfdocs = rdfdocs.order_by(desc(func.coalesce(model.StatResult.warnings, '0')))
elif request.GET['sort'] == 'format':
c.rdfdocs = rdfdocs.order_by(func.coalesce(model.RDFDoc.format, '0'))
elif request.GET['sort'] == 'issue':
c.rdfdocs = rdfdocs.order_by(model.StatResult.errors)
elif request.GET['sort'] == 'update':
c.rdfdocs = rdfdocs.order_by(model.RDFDoc.last_updated.desc())
else:
c.rdfdocs = rdfdocs.order_by(model.RDFDoc.worked_on.desc(), model.RDFDoc.name, model.RDFDoc.last_updated.desc())
else:
c.rdfdocs = rdfdocs.order_by(model.RDFDoc.worked_on.desc(), model.RDFDoc.name, model.RDFDoc.last_updated.desc())
if request.GET.has_key('page'):
page = request.GET['page']
else:
page = 1
page_url = PageURL_WebOb(request)
c.rdfdocs_page = Page(c.rdfdocs, page=page, items_per_page=50, url=page_url)
c.rdfdoc_count = c.rdfdocs.count()
c.workers = Session.query(model.WorkerProc).order_by(model.WorkerProc.started.desc()).all()
if format=='json' or 'application/json' in request.headers.get('accept', ''):
response.content_type = 'application/json'
json_rdfdocs = []
for r in rdfdocs:
json_rdfdocs.append(r.name)
return json.dumps(json_rdfdocs)
return render('/rdfdoc/index.html')
def get_bayar(cls, p_kode):
pkey = FixLength(NOP)
pkey.set_raw(p_kode)
query = pbb_DBSession.query(
func.concat(cls.kd_propinsi, '.').concat(cls.kd_dati2).concat('-').\
concat(cls.kd_kecamatan).concat('.').concat(cls.kd_kelurahan).concat('-').\
concat(cls.kd_blok).concat('.').concat(cls.no_urut).concat('-').\
concat(cls.kd_jns_op).label('nop'), cls.thn_pajak_sppt,
cls.nm_wp_sppt, cls.jln_wp_sppt, cls.blok_kav_no_wp_sppt,
cls.rw_wp_sppt, cls.rt_wp_sppt, cls.kelurahan_wp_sppt,
cls.kota_wp_sppt, cls.kd_pos_wp_sppt, cls.npwp_sppt,
cls.kd_kls_tanah, cls.kd_kls_bng,
cls.luas_bumi_sppt, cls.luas_bng_sppt,
cls.njop_bumi_sppt, cls.njop_bng_sppt, cls.njop_sppt,
cls.njoptkp_sppt, cls.pbb_terhutang_sppt, cls.faktor_pengurang_sppt,
cls.status_pembayaran_sppt,
cls.tgl_jatuh_tempo_sppt,
cls.pbb_yg_harus_dibayar_sppt.label('pokok'),
func.max(PembayaranSppt.tgl_pembayaran_sppt).label('tgl_pembayaran_sppt'),
func.sum(func.coalesce(PembayaranSppt.jml_sppt_yg_dibayar,0)).label('bayar'),
func.sum(func.coalesce(PembayaranSppt.denda_sppt,0)).label('denda_sppt'),).\
outerjoin(PembayaranSppt,and_(
cls.kd_propinsi==PembayaranSppt.kd_propinsi,
cls.kd_dati2==PembayaranSppt.kd_dati2,
cls.kd_kecamatan==PembayaranSppt.kd_kecamatan,
cls.kd_kelurahan==PembayaranSppt.kd_kelurahan,
cls.kd_blok==PembayaranSppt.kd_blok,
cls.no_urut==PembayaranSppt.no_urut,
cls.kd_jns_op==PembayaranSppt.kd_jns_op,
cls.thn_pajak_sppt==PembayaranSppt.thn_pajak_sppt
)).\
group_by(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan, cls.kd_kelurahan,
cls.kd_blok, cls.no_urut, cls.kd_jns_op, cls.thn_pajak_sppt,
cls.nm_wp_sppt, cls.jln_wp_sppt, cls.blok_kav_no_wp_sppt,
cls.rw_wp_sppt, cls.rt_wp_sppt, cls.kelurahan_wp_sppt,
cls.kota_wp_sppt, cls.kd_pos_wp_sppt, cls.npwp_sppt,
cls.kd_kls_tanah, cls.kd_kls_bng,
cls.luas_bumi_sppt, cls.luas_bng_sppt,
cls.njop_bumi_sppt, cls.njop_bng_sppt, cls.njop_sppt,
cls.njoptkp_sppt, cls.pbb_terhutang_sppt, cls.faktor_pengurang_sppt,
cls.status_pembayaran_sppt,
cls.tgl_jatuh_tempo_sppt,
cls.pbb_yg_harus_dibayar_sppt.label('pokok'),)
return query.filter(cls.kd_propinsi == pkey['kd_propinsi'],
cls.kd_dati2 == pkey['kd_dati2'],
cls.kd_kecamatan == pkey['kd_kecamatan'],
cls.kd_kelurahan == pkey['kd_kelurahan'],
cls.kd_blok == pkey['kd_blok'],
cls.no_urut == pkey['no_urut'],
cls.kd_jns_op == pkey['kd_jns_op'],)
def _ops_for_date_range(self, balance_uids, start_date, end_date, tags = [], incomes = True, expenses = False):
"""
Will be removed in next release.
"""
model = self.get_sa_model()
db = self.get_sa_session()
conditions = [or_(*[model.BalanceChange.balance_uid == balance_uid for balance_uid in balance_uids])]
if incomes:
conditions.append(model.BalanceChange.amount >= 0)
if expenses:
conditions.append(model.BalanceChange.amount < 0)
if start_date:
conditions.append(model.BalanceChange.occurred_on >= start_date)
if end_date:
conditions.append(model.BalanceChange.occurred_on <= end_date)
if isinstance(tags, list) and len(tags) > 0:
conditions.extend([model.BalanceChange.tags.any(tag=tag.strip().lower()) for tag in tags if tag is not None and tag.strip() != ''])
try:
return db.execute(select([func.coalesce(func.sum(model.BalanceChange.amount), 0).label("summary")],
and_(*conditions),
from_obj=[model.balance_changes_table])).fetchone()[0]
except:
log.error("", exc_info=1)
return 0
def build_query_to_populate(self, query, full_table, aggregate_table):
insert_columns = [aggregate_table.c.join_key]
fk = Column(self.key, Integer)
geom = Column(self.geometry_column, Geometry())
bins_table = Table(self.table, full_table.metadata, fk, geom)
if self.join_custom_data:
extra_data = Table("extra_data", full_table.metadata,
Column("verified", Boolean),
Column("timestamp", DateTime),
Column("client_ip", Integer),
Column("server_ip", Integer),
Column("location", Geometry("Point", srid=4326)),
keep_existing = True)
joining = join(full_table, extra_data,
and_(extra_data.c.client_ip == full_table.c.client_ip,
extra_data.c.server_ip == full_table.c.server_ip,
extra_data.c.timestamp == full_table.c.time),
isouter = True)
query = query.select_from(joining)
location = case([(extra_data.c.verified, func.coalesce(extra_data.c.location, full_table.c.location))], else_ = full_table.c.location)
else:
location = full_table.c.location
select_query = (query.select_from(bins_table)
.where(ST_Intersects(location, geom))
.column(fk)
.group_by(fk))
return insert_columns, select_query
def find_matches(dataset, text, filter=None, exclude=None):
entities = Entity.__table__
match_text = normalize(text, dataset)[:254]
# select text column and apply necesary transformations
text_field = entities.c.name
if dataset.normalize_text:
text_field = entities.c.normalized
if dataset.ignore_case:
text_field = func.lower(text_field)
text_field = func.left(text_field, 254)
# calculate the difference percentage
l = func.greatest(1.0, func.least(len(match_text), func.length(text_field)))
score = func.greatest(0.0, ((l - func.levenshtein(text_field, match_text)) / l) * 100.0)
score = func.max(score).label('score')
# coalesce the canonical identifier
id_ = func.coalesce(entities.c.canonical_id, entities.c.id).label('id')
# apply filters
filters = [entities.c.dataset_id==dataset.id,
entities.c.invalid==False]
if not dataset.match_aliases:
filters.append(entities.c.canonical_id==None)
if exclude is not None:
filters.append(entities.c.id!=exclude)
if filter is not None:
filters.append(text_field.ilike('%%%s%%' % filter))
q = select([id_, score], and_(*filters), [entities],
group_by=[id_], order_by=[score.desc()])
return Matches(q)
def build_query_to_populate(self, query, full_table, aggregate_table):
insert_columns = [aggregate_table.c.join_key]
fk = Column(self.key, Integer)
geom = Column(self.geometry_column, Geometry())
bins_table = Table(self.table, full_table.metadata, fk, geom)
if self.join_custom_data:
extra_data = Table("extra_data", full_table.metadata,
Column("timestamp", DateTime),
Column("verified", Boolean),
Column("bigquery_key", String),
Column("connection_type", String),
Column("advertised_download", Integer),
Column("actual_download", Float),
Column("advertised_upload", Integer),
Column("actual_upload", Float),
Column("min_rtt", Integer),
Column("location_type", String),
Column("cost_of_service", Integer),
Column("location", Geometry("Point", srid=4326)),
keep_existing = True)
joining = join(full_table, extra_data,
and_(extra_data.c.bigquery_key == full_table.c.bigquery_key),
isouter = True)
query = query.select_from(joining)
location = case([(extra_data.c.verified, func.coalesce(extra_data.c.location, full_table.c.location))], else_ = full_table.c.location)
else:
location = full_table.c.location
select_query = (query.select_from(bins_table)
.where(ST_Intersects(location, geom))
.column(fk)
.group_by(fk))
return insert_columns, select_query
def export_csv(self):
request = self.request
query = SipkdDBSession.query(Sp2dAdviceDet.advno,
(func.right(Sp2d.sp2dno,5)+
func.coalesce(Sp2d.infix,Sp2d.infix,'')+'/'+
Sp2d.sp2dtype+'/'+
func.left(Sp2d.sp2dno,4) ).label('sp2dno'),
Sp2d.sp2ddate, Sp2d.paymentfor, Sp2d.sp2damount,
Sp2d.ppnamount, Sp2d.pphamount,
(Sp2d.pot1num+Sp2d.pot2num+Sp2d.pot3num+Sp2d.pot4num+Sp2d.pot5num).label("potongan"),
Sp2d.sp2dnetto, Sp2d.bknama, Sp2d.bankposnm, Sp2d.bankaccount,
Sp2d.npwp).\
join(Sp2d).\
filter(Sp2d.sp2dno==Sp2dAdviceDet.sp2dno,
Sp2d.sp2dno.in_(request.params['data'].split(',')))
r = query.first()
header = r.keys()
query = query.all()
rows = []
for item in query:
rows.append(list(item))
# override attributes of response
filename = 'sp2d%s.csv' % datetime.now().strftime('%Y%m%d%H%M%S')
self.request.response.content_disposition = 'attachment;filename=' + filename
return {
'header': header,
'rows': rows,
}
def get_device_tokens(login_id):
'''
Get device tokens for a given login. Removes duplicates per provider.
'''
with session_scope() as session:
result = session.query(model.Device.platform_id,
func.coalesce(model.Device.device_token_new, model.Device.device_token).label('device_token')).\
filter(model.Device.login_id == login_id).filter(model.Device.unregistered_ts.is_(None)).all()
# only return unique device tokens per provider (gcm, apn) to avoid sending duplicates
devices = set()
provider_tokens = set()
for device in sorted(result): # sorting to make unit tests easier
platform_id, device_token = device
provider_id = (constants.PLATFORM_BY_PROVIDER.get(platform_id, 0)
or platform_id)
# NOTE: Use unique tokens per *provider* only for known providers,
# and unique tokens per *platform* in other cases, since
# it is hard to verify providers for custom senders
provider_token = (provider_id, device_token)
if provider_token not in provider_tokens:
devices.add(device)
provider_tokens.add(provider_token)
return list(devices)
def _set_orderby_desc(self, query, model, limit,
last_id, offset, descending, orderby):
"""Return an updated query with the proper orderby and desc."""
if orderby == 'fav_user_ids':
n_favs = func.coalesce(func.array_length(model.fav_user_ids, 1), 0).label('n_favs')
query = query.add_column(n_favs)
if orderby in ['created', 'updated', 'finish_time']:
if descending:
query = query.order_by(desc(
cast(getattr(model,
orderby),
TIMESTAMP)))
else:
query = query.order_by(cast(getattr(model, orderby), TIMESTAMP))
else:
if orderby != 'fav_user_ids':
if descending:
query = query.order_by(desc(getattr(model, orderby)))
else:
query = query.order_by(getattr(model, orderby))
else:
if descending:
query = query.order_by(desc("n_favs"))
else:
query = query.order_by("n_favs")
if last_id:
query = query.limit(limit)
else:
query = query.limit(limit).offset(offset)
return query
def issue_new(self):
new_number = DBSession.query(
func.coalesce(func.max(Issue.issue_number), 0) + 1
).filter(
(Issue.comic_id==self.context.comic.comic_id)
).scalar()
form = Form(
self.request,
defaults={
'comic_id': self.context.comic.comic_id,
'issue_number': new_number,
'created': self.utcnow,
},
schema=IssueSchema,
variable_decode=True)
if form.validate():
issue = form.bind(Issue())
DBSession.add(issue)
self.request.session.flash(
'Created issue #%d of %s' % (
issue.issue_number,
self.context.comic.title,
))
DBSession.flush()
return HTTPFound(location=
self.request.route_url('issues', comic=self.context.comic.comic_id)
if self.context.comic.comic_id != 'blog' else
self.request.route_url('blog_index', comic='blog')
)
return dict(
create=True,
form=FormRendererFoundation(form),
)
def _ops_for_date_range(self, balance_uids, start_date, end_date, incomes = True, expenses = False):
"""
Will be removed in next release.
"""
model = request.environ["sqlalchemy.model"]
db = request.environ["sqlalchemy.session"]
conditions = [or_(*[model.BalanceChange.balance_uid == balance_uid for balance_uid in balance_uids])]
if incomes:
conditions.append(model.BalanceChange.amount >= 0)
if expenses:
conditions.append(model.BalanceChange.amount < 0)
if start_date:
conditions.append(model.BalanceChange.occurred_on >= start_date)
if end_date:
conditions.append(model.BalanceChange.occurred_on <= end_date)
try:
return db.execute(select([func.coalesce(func.sum(model.BalanceChange.amount), 0).label("summary")],
and_(*conditions),
from_obj=[model.balance_changes_table])).fetchone()[0]
except:
log.error("", exc_info=1)
return 0
def get_colour_map(cls):
colour = func.coalesce(Category.colour, ParentCategory.colour)
return dict(
Category.query.outerjoin(ParentCategory, ParentCategory.id == Category.parent)
.filter(colour.isnot(None))
.values(Category.id, colour)
)
def update_canonicals(canonicals):
'''
Update canonical data for android devices.
'''
global ENGINE
binding = [{"p_{}".format(k): v for k, v in canonical.items()} for canonical in canonicals]
device_table = model.metadata.tables['device']
stmt = update(device_table).\
values(device_token_new=bindparam('p_new_token')).\
where(and_(device_table.c.login_id == bindparam('p_login_id'),
func.coalesce(device_table.c.device_token_new, device_table.c.device_token) == bindparam('p_old_token')))
ENGINE.execute(stmt, binding)
with session_scope() as session:
query = text('SELECT keep_max_users_per_device( \
(:platform_id)::int2, :device_token, (:max_users_per_device)::int2)')
for canonical in canonicals:
session.execute(query,
{'platform_id': constants.PLATFORM_ANDROID,
'device_token': canonical['new_token'],
'max_users_per_device': config.max_users_per_device
})
session.execute(query,
{'platform_id': constants.PLATFORM_ANDROID_TABLET,
'device_token': canonical['new_token'],
'max_users_per_device': config.max_users_per_device
})
session.commit()
def get_reposters_for_track(args):
user_results = []
current_user_id = args.get('current_user_id')
repost_track_id = args.get('repost_track_id')
limit = args.get('limit')
offset = args.get('offset')
db = get_db_read_replica()
with db.scoped_session() as session:
# Ensure Track exists for provided repost_track_id.
track_entry = session.query(Track).filter(
Track.track_id == repost_track_id,
Track.is_current == True).first()
if track_entry is None:
raise exceptions.NotFoundError(
'Resource not found for provided track id')
# Subquery to get all (user_id, follower_count) entries from Follows table.
follower_count_subquery = (session.query(
Follow.followee_user_id,
func.count(Follow.followee_user_id).label(
response_name_constants.follower_count)).filter(
Follow.is_current == True,
Follow.is_delete == False).group_by(
Follow.followee_user_id).subquery())
# Get all Users that reposted track, ordered by follower_count desc & paginated.
query = (
session.query(
User,
# Replace null values from left outer join with 0 to ensure sort works correctly.
(func.coalesce(follower_count_subquery.c.follower_count, 0)
).label(response_name_constants.follower_count))
# Left outer join to associate users with their follower count.
.outerjoin(
follower_count_subquery,
follower_count_subquery.c.followee_user_id ==
User.user_id).filter(
User.is_current == True,
# Only select users that reposted given track.
User.user_id.in_(
session.query(Repost.user_id).filter(
Repost.repost_item_id == repost_track_id,
Repost.repost_type == RepostType.track,
Repost.is_current == True,
Repost.is_delete == False)
)).order_by(desc(response_name_constants.follower_count)))
user_results = add_query_pagination(query, limit, offset).all()
# Fix format to return only Users objects with follower_count field.
if user_results:
users, _ = zip(*user_results)
user_results = helpers.query_result_to_list(users)
# bundle peripheral info into user results
user_ids = [user['user_id'] for user in user_results]
user_results = populate_user_metadata(session, user_ids,
user_results,
current_user_id)
return user_results
def get_device_tokens(login_id):
'''
Get device tokens for a given login.
'''
session = get_session()
return session.query(model.Device.platform_id,
func.coalesce(model.Device.device_token_new, model.Device.device_token).label('device_token')).\
filter(model.Device.login_id == login_id)
def __enter__(self):
self.last_capture = (self.session.query(UndoAction).filter_by(
object_type=self.object_type,
stack_id=self.stack_id).with_entities(
func.coalesce(func.max(UndoAction.capture_id), 0)).scalar())
event.listen(self.app_engine, "before_execute", self.before_exec)
event.listen(self.app_engine, "after_execute", self.after_exec)
def check_stock_consistency() -> list[int]:
return [
item[0]
for item in db.session.query(Stock.id).outerjoin(Stock.bookings).
group_by(Stock.id).having(Stock.dnBookedQuantity != func.coalesce(
func.sum(Booking.quantity).filter(
Booking.isCancelled == False), 0)).all()
]
def get_piutang(cls, p_kode, p_tahun, p_count):
#Digunakan untuk menampilkan sppt dan pembayarannya
pkey = FixLength(NOP)
pkey.set_raw(p_kode)
p_tahun_awal = str(int(p_tahun) - p_count + 1)
q1 = pbb_DBSession.query(cls.thn_pajak_sppt,(cls.pbb_yg_harus_dibayar_sppt).label('pokok'),
cls.tgl_jatuh_tempo_sppt, cls.nm_wp_sppt,
func.sum(PembayaranSppt.denda_sppt).label('denda_sppt'),
func.sum(PembayaranSppt.jml_sppt_yg_dibayar).label('bayar'),
(cls.pbb_yg_harus_dibayar_sppt - func.sum(
(func.coalesce(PembayaranSppt.jml_sppt_yg_dibayar,0)-
func.coalesce(PembayaranSppt.denda_sppt,0)))).label('sisa')
).\
outerjoin(PembayaranSppt, and_(
cls.kd_propinsi==PembayaranSppt.kd_propinsi,
cls.kd_dati2==PembayaranSppt.kd_dati2,
cls.kd_kecamatan==PembayaranSppt.kd_kecamatan,
cls.kd_kelurahan==PembayaranSppt.kd_kelurahan,
cls.kd_blok==PembayaranSppt.kd_blok,
cls.no_urut==PembayaranSppt.no_urut,
cls.kd_jns_op==PembayaranSppt.kd_jns_op,
cls.thn_pajak_sppt==PembayaranSppt.thn_pajak_sppt
)).\
filter(
cls.kd_propinsi == pkey['kd_propinsi'],
cls.kd_dati2 == pkey['kd_dati2'],
cls.kd_kecamatan == pkey['kd_kecamatan'],
cls.kd_kelurahan == pkey['kd_kelurahan'],
cls.kd_blok == pkey['kd_blok'],
cls.no_urut == pkey['no_urut'],
cls.kd_jns_op == pkey['kd_jns_op']).\
filter(cls.thn_pajak_sppt.between(p_tahun_awal,p_tahun)
).\
group_by(cls.thn_pajak_sppt, cls.pbb_yg_harus_dibayar_sppt,
cls.tgl_jatuh_tempo_sppt, cls.nm_wp_sppt).subquery()
query = pbb_DBSession.query(
func.sum(q1.c.pokok).label('pokok'),
func.sum(q1.c.denda_sppt).label('denda_sppt'),
func.sum(q1.c.bayar).label('bayar'),
func.sum(q1.c.sisa).label('sisa'),
)
return query
def _compile_array_agg(element, compiler, **kw):
compiled = "%s(%s)" % (element.name, compiler.process(element.clauses))
if element.default is None:
return compiled
return str(func.coalesce(
text(compiled),
cast(postgresql.array(element.default), element.type)
).compile(compiler))
def counted(cls):
expr = select([
func.coalesce(func.bool_and(ElectionResult.counted), False)
])
expr = expr.where(ElectionResult.election_id == cls.id)
expr = expr.label('counted')
return expr
def _get_answered_and_talk_time(self, session, agent_id, **filters):
query = (session.query(
func.count(StatCallOnQueue.id),
func.coalesce(func.sum(StatCallOnQueue.talktime), 0),
).filter(StatAgent.agent_id == agent_id).filter(
StatCallOnQueue.status == 'answered').join(StatAgent))
query = self._add_interval_query(StatCallOnQueue, query, **filters)
return query.first() or (0, 0)
def build_count_subquery(counter_type):
return db.session.query(DeviceCounters.device_id, func.coalesce(func.sum(DeviceCounters.value), 0).label('count')).\
join(RowProcessed, RowProcessed.analyzer == 'packet_analyzer').\
join(Packet, Packet.id == RowProcessed.last_row).\
filter(DeviceCounters.counter_type == counter_type).\
filter(DeviceCounters.last_update + func.make_interval(0,0,0,1) > Packet.date).\
group_by(DeviceCounters.device_id).\
subquery()
def retrieve_url(self, short_representation: str):
try:
seed = self.encoder.decode(short_representation)
except ValueError:
return None
url = URL.query.filter(
func.coalesce(URL.seed, URL.id) == seed).one_or_none()
return url
def model_fetch_task(task_id):
task = Task.query.filter_by(user_id=current_user.id).order_by(
func.coalesce(Task.start_time, Task.end_time), Task.end_time)
'''task = Task.query.filter_by(user_id=current_user.id).order_by(
case((Task.start_time is None, Task.end_time), Task.start_time))'''
task_schema = TaskSchema(many=True)
output = task_schema.dump(task)
return jsonify({'task': output})
def stalenessRating(self):
""" Return the Queryable staleness rating of the mastery """
return func.coalesce(
cast(
func.floor(
func.extract('epoch',
func.now() - self.lastCorrectAnswer) / 86400),
db.Integer) / StalenessPeriod.days, 0)
def _compile_array_agg(element, compiler, **kw):
compiled = "%s(%s)" % (element.name, compiler.process(element.clauses))
if element.default is None:
return compiled
return str(
func.coalesce(text(compiled),
cast(postgresql.array(element.default),
element.type)).compile(compiler))
def total_spent(self):
results = db.session.query(func.coalesce(func.sum(distinct(Output.value)), 0)) \
.select_from(Input) \
.join(Output, Output.address == Input.prevout_address) \
.filter(Input.prevout_address == self.address) \
.first()
return results[0]
def test_orm_expression(dburl):
with S(dburl, echo=ECHO) as s:
key = func.coalesce(Book.a, 0) + Book.b
q = s.query(Book).order_by(key, Book.id)
check_paging_orm(q=q)
q = s.query(Book).order_by(key.label("sort_by_me"), Book.id)
check_paging_orm(q=q)
def main():
parser = make_parser()
args = parser.parse_args()
if args.setup_db:
create_tables()
return 0
logging.info("Starting database session")
session = create_session()
try:
logging.info("Starting scraping")
ents = scrape()
except Exception:
logging.exception("Scraping failed")
return 1
logging.info("Scraped %s inmates", len(ents))
try:
previous_batch, *_ = session.query(
func.coalesce(func.max(Batch.id), 0)
).one()
current_batch = previous_batch + 1
batch = Batch(id=current_batch)
session.add(batch)
session.flush()
for ent in ents:
inmate = Inmate(
batch_id=batch.id,
booking_id=ent["booking_id"],
name=ent["name"],
race=ent["race"],
sex=ent["sex"],
height=ent["height"],
weight=ent["weight"],
hair=ent["hair"],
eyes=ent["eyes"],
booking_date=ent["date_booked"],
next_court_date=ent["court_date"],
bail=ent["bail"],
)
inmate.charges = [
Charge(**charge, batch_id=batch.id)
for charge in ent["charges"]
]
session.add(inmate)
except Exception:
logging.exception("Persisting data failed")
return 1
session.commit()
logging.info("Persisted scraped data")
return 0
def generate_live_status_table(arch):
averagesql = select([
func.coalesce(func.avg(cast(stats_build.c.build_duration, Integer)), 0)
]).where(
and_(
stats_build.c.status.in_(('reproducible', 'unreproducible')),
stats_build.c.name == sources.c.name,
stats_build.c.suite == sources.c.suite,
stats_build.c.architecture == sources.c.architecture,
)
).as_scalar()
query = select([
sources.c.id,
sources.c.suite,
sources.c.architecture,
sources.c.name,
sources.c.version,
schedule.c.date_build_started,
results.c.status,
results.c.build_duration,
averagesql,
schedule.c.job,
]).select_from(
sources.join(schedule).join(results, isouter=True)
).where(
and_(
schedule.c.date_build_started != None,
sources.c.architecture == bindparam('arch'),
)
).order_by(
schedule.c.date_scheduled
)
html = ''
rows = query_db(query.params({'arch': arch}))
html += '<p><table class="scheduled">\n' + tab
html += '<tr><th class="center">#</th><th class="center">src pkg id</th><th class="center">suite</th><th class="center">arch</th>'
html += '<th class=\"center\">source package</th><th class=\"center\">version</th></th>'
html += '<th class=\"center\">build started</th><th class=\"center\">previous build status</th>'
html += '<th class=\"center\">previous build duration</th><th class=\"center\">average build duration</th><th class=\"center\">builder job</th>'
html += '</tr>\n'
counter = 0
for row in rows:
counter += 1
suite = row[1]
arch = row[2]
pkg = row[3]
duration = convert_into_hms_string(row[7])
avg_duration = convert_into_hms_string(row[8])
html += tab + '<tr><td> </td><td>' + str(row[0]) + '</td>'
html += '<td>' + suite + '</td><td>' + arch + '</td>'
html += '<td><code>' + link_package(pkg, suite, arch) + '</code></td>'
html += '<td>' + str(row[4]) + '</td><td>' + str(row[5]) + '</td>'
html += '<td>' + convert_into_status_html(str(row[6])) + '</td><td>' + duration + '</td><td>' + avg_duration + '</td>'
html += '<td><a href="https://tests.reproducible-builds.org/cgi-bin/nph-logwatch?' + str(row[9]) + '">' + str(row[9]) + '</a></td>'
html += '</tr>\n'
html += '</table></p>\n'
return html
def tas_gtas_combo(session, period, year):
""" Creates a combined list of TAS and GTAS data filtered by the given period/year
Args:
session: DB session
period: The period for which to get GTAS data
year: The year for which to get GTAS data
Returns:
A WITH clause to use with other queries
"""
query = session.query(
gtas_model.allocation_transfer_agency.label('allocation_transfer_agency'),
gtas_model.agency_identifier.label('agency_identifier'),
gtas_model.beginning_period_of_availa.label('beginning_period_of_availa'),
gtas_model.ending_period_of_availabil.label('ending_period_of_availabil'),
gtas_model.availability_type_code.label('availability_type_code'),
gtas_model.main_account_code.label('main_account_code'),
gtas_model.sub_account_code.label('sub_account_code'),
gtas_model.amount.label('amount'),
gtas_model.line.label('line'),
tas_model.financial_indicator2.label('financial_indicator2'),
tas_model.fr_entity_type.label('fr_entity_type')).\
join(tas_model, gtas_model.tas == func.concat(func.coalesce(tas_model.allocation_transfer_agency, '000'),
func.coalesce(tas_model.agency_identifier, '000'),
func.coalesce(tas_model.beginning_period_of_availa, '0000'),
func.coalesce(tas_model.ending_period_of_availabil, '0000'),
func.coalesce(tas_model.availability_type_code, ' '),
func.coalesce(tas_model.main_account_code, '0000'),
func.coalesce(tas_model.sub_account_code, '000'))).\
filter(gtas_model.period == period).\
filter(gtas_model.fiscal_year == year)
return query.cte('tas_gtas')
def _query_for_billing_data(table, notification_type, start_date, end_date, service_id):
billable_type_list = {
SMS_TYPE: NOTIFICATION_STATUS_TYPES_BILLABLE,
EMAIL_TYPE: NOTIFICATION_STATUS_TYPES_BILLABLE,
LETTER_TYPE: NOTIFICATION_STATUS_TYPES_BILLABLE_FOR_LETTERS
}
sent_by_func = func.coalesce(table.sent_by, case(
[(table.notification_type == 'letter', 'dvla'),
(table.notification_type == 'sms', 'unknown'),
(table.notification_type == 'email', 'ses')]), )
letter_page_count = case([(table.notification_type == 'letter', table.billable_units), ])
query = db.session.query(
table.template_id,
table.service_id,
table.notification_type,
sent_by_func.label('sent_by'),
func.coalesce(table.rate_multiplier, 1).cast(Integer).label('rate_multiplier'),
func.coalesce(table.international, False).label('international'),
letter_page_count.label('letter_page_count'),
func.sum(table.billable_units).label('billable_units'),
func.count().label('notifications_sent'),
Service.crown,
func.coalesce(table.postage, 'none').label('postage')
).filter(
table.status.in_(billable_type_list[notification_type]),
table.key_type != KEY_TYPE_TEST,
table.created_at >= start_date,
table.created_at < end_date,
table.notification_type == notification_type,
table.service_id == service_id
).group_by(
table.template_id,
table.service_id,
table.notification_type,
sent_by_func,
letter_page_count,
table.rate_multiplier,
table.international,
Service.crown,
table.postage,
).join(
Service
)
return query.all()
def total_spent(self):
results = db.session.query(func.coalesce(func.sum(Output.value), 0)) \
.join(Input, Input.prevout_address == Output.address, isouter=True) \
.filter(Output.address == self.address) \
.filter(Output.covenant_action != 'BID') \
.filter(Input.prevout_address != None) \
.first()
return results[0]
def invalid_query(self, session, **kw):
cte = session.query(Languoid.pk, Languoid.father_pk, Languoid.level)\
.filter(Languoid.father_pk != None).cte(recursive=True) # noqa
parent = orm.aliased(Languoid)
cte = cte.union_all(session.query(cte.c.pk, parent.father_pk, cte.c.level)\
.join(parent, cte.c.father_pk == parent.pk)\
.filter(parent.father_pk != None)) # noqa
return session.query(Languoid)\
.outerjoin(cte, Languoid.pk == cte.c.father_pk)\
.group_by(Language.pk, Languoid.pk)\
.having(or_(
func.coalesce(Languoid.child_family_count, -1) !=
func.count(func.nullif(cte.c.level != LanguoidLevel.family, True)),
func.coalesce(Languoid.child_language_count, -1) !=
func.count(func.nullif(cte.c.level != LanguoidLevel.language, True)),
func.coalesce(Languoid.child_dialect_count, -1) !=
func.count(func.nullif(cte.c.level != LanguoidLevel.dialect, True))))\
.order_by((Languoid.id))
def common_entites(self):
return [
func.rtrim(Teacher.full_name).label("teacher"),
func.rtrim(Discipline.title).label("discipline"),
func.rtrim(
func.coalesce(Potoklist.title, Kontgrp.title,
Kontkurs.title)).label("kont"),
Normtime.id.label("nt"),
]
def get_total_amount_spent(departement_code: str = None) -> float:
query = db.session.query(func.coalesce(func.sum(Booking.amount * Booking.quantity), 0))
if departement_code:
query = query.join(User).filter(User.departementCode == departement_code)
return float(query \
.filter(Booking.isCancelled == False) \
.scalar())
def is_in_digest_window(self, exclude_first_day=False):
from indico.modules.rb import settings as rb_settings
digest_start = round_up_month(date.today(), from_day=2)
days_until_next_digest = cast(digest_start, Date) - cast(func.now(), Date)
digest_window = func.coalesce(self.notification_before_days, rb_settings.get('notification_before_days'))
if exclude_first_day:
return days_until_next_digest < digest_window
else:
return days_until_next_digest <= digest_window
def order_query(self, query):
""" Orders the given query by the state of the collection. """
direction = desc if self.direction == 'desc' else asc
if self.order in inspect(self.model_class).columns.keys():
attribute = getattr(self.model_class, self.order)
elif self.order == 'group.name':
attribute = func.coalesce(UserGroup.name, '')
elif self.order == 'user.realname':
attribute = func.coalesce(User.realname, '')
elif self.order == 'user.username':
attribute = func.coalesce(User.username, '')
elif self.order == 'user.name':
attribute = func.coalesce(User.realname, User.username, '')
else:
attribute = self.model_class.first_issue
return query.order_by(None).order_by(direction(attribute))
def fetch_billing_details_for_all_services():
billing_details = db.session.query(
Service.id.label('service_id'),
func.coalesce(
Service.purchase_order_number,
Organisation.purchase_order_number).label('purchase_order_number'),
func.coalesce(
Service.billing_contact_names,
Organisation.billing_contact_names).label('billing_contact_names'),
func.coalesce(Service.billing_contact_email_addresses,
Organisation.billing_contact_email_addresses).label(
'billing_contact_email_addresses'),
func.coalesce(
Service.billing_reference,
Organisation.billing_reference).label('billing_reference'),
).outerjoin(Service.organisation).all()
return billing_details
def find_income_before_date(u, t):
dd = db.session.query(
(func.coalesce(func.sum(Income.num),0)).label('num'))\
.filter(Income.uid == u, Income.date <= t)\
.first()
rr = db.session.query(
(func.coalesce(func.sum(Incomego.num),0)).label('num'))\
.filter(Incomego.uid == u, Incomego.date <= t)\
.first()
if dd == None or len(dd) == 0:
dd = [
0,
]
if rr == None or len(rr) == 0:
rr = [
0,
]
return dd[0] - rr[0]
def is_in_digest_window(self, exclude_first_day=False):
from indico.modules.rb import settings as rb_settings
digest_start = round_up_month(date.today(), from_day=2)
days_until_next_digest = cast(digest_start, Date) - cast(func.now(), Date)
digest_window = func.coalesce(self.notification_before_days, rb_settings.get('notification_before_days'))
if exclude_first_day:
return days_until_next_digest < digest_window
else:
return days_until_next_digest <= digest_window
def find_by_pro_user(
user: User,
booking_period: tuple[date, date],
event_date: Optional[datetime] = None,
venue_id: Optional[int] = None,
page: int = 1,
per_page_limit: int = 1000,
) -> BookingsRecapPaginated:
# TODO: remove this block when IMPROVE_BOOKINGS_PERF is definitely adopted
if not FeatureToggle.IMPROVE_BOOKINGS_PERF.is_active():
sorted_booking_period = sorted(booking_period)
bookings_recap_subquery = _filter_bookings_recap_subquery(
user, sorted_booking_period, event_date, venue_id)
if page == 1:
total_bookings_recap = db.session.query(
func.coalesce(func.sum(bookings_recap_subquery.c.quantity),
0)).scalar()
else:
total_bookings_recap = 0
bookings_recap_query = _build_bookings_recap_query(
bookings_recap_subquery)
bookings_recap_query_with_duplicates = _duplicate_booking_when_quantity_is_two_legacy(
bookings_recap_query, bookings_recap_subquery)
paginated_bookings = (bookings_recap_query_with_duplicates.order_by(
text('"bookingDate" DESC')).offset(
(page - 1) * per_page_limit).limit(per_page_limit).all())
return _paginated_bookings_sql_entities_to_bookings_recap(
paginated_bookings=paginated_bookings,
page=page,
per_page_limit=per_page_limit,
total_bookings_recap=total_bookings_recap,
serializer=_serialize_booking_recap_legacy,
)
total_bookings_recap = _get_filtered_bookings_count(
user, booking_period, event_date, venue_id)
bookings_query = _get_filtered_booking_pro(
pro_user=user,
period=booking_period,
event_date=event_date,
venue_id=venue_id,
)
bookings_query = _duplicate_booking_when_quantity_is_two(bookings_query)
bookings_page = (bookings_query.order_by(text('"bookedAt" DESC')).offset(
(page - 1) * per_page_limit).limit(per_page_limit).all())
return _paginated_bookings_sql_entities_to_bookings_recap(
paginated_bookings=bookings_page,
page=page,
per_page_limit=per_page_limit,
total_bookings_recap=total_bookings_recap,
)
def table_with_variants_same_c_copy(self, select_columns: Optional[list]):
"""
Returns a table of variants of the same type of the ones contained in RegionAttrs.with_variants_same_c_copy and only
form the individuals that own all of them on the same chromosome copy.
:param select_columns: the list of column names to select from the result. If None, all the columns are taken.
"""
if len(self.region_attrs.with_variants_same_c_copy) < 2:
raise ValueError(
'You must provide at least two Mutation instances in order to use this method.'
)
# selects only the mutations to be on the same chromosome copies (this set will be used two times) from all individuals
# we will enforce the presence of all the given mutations in all the individuals later...
interm_select_column_names = None # means all columns
if select_columns is not None: # otherwise pick select_columns + minimum required
interm_select_column_names = set(select_columns)
interm_select_column_names.update(['item_id', 'al1', 'al2'])
intermediate_table = self._table_with_any_of_mutations(
interm_select_column_names, self.my_meta_t,
*self.region_attrs.with_variants_same_c_copy)
# groups mutations by owner in the intermediate table, and take only the owners for which sum(al1) or sum(al2)
# equals to the number of the given mutations. That condition automatically implies the presence of all the
# given mutations in the same individual.
# for those owner, take all the given mutations
result_columns = [intermediate_table] # means all columns
if select_columns is not None: # otherwise pick the columns from select_columns
result_columns = [
intermediate_table.c[col_name] for col_name in select_columns
]
stmt_as = \
select(result_columns) \
.where(intermediate_table.c.item_id.in_(
select([intermediate_table.c.item_id])
.group_by(intermediate_table.c.item_id)
.having(
(func.sum(intermediate_table.c.al1) == len(
self.region_attrs.with_variants_same_c_copy)) | # the ( ) around each condition are mandatory
(func.sum(func.coalesce(intermediate_table.c.al2, 0)) == len(
self.region_attrs.with_variants_same_c_copy)))
))
target_t_name = utils.random_t_name_w_prefix('with_var_same_c_copy')
stmt = utils.stmt_create_table_as(target_t_name, stmt_as,
default_schema_to_use_name)
if self.log_sql_commands:
utils.show_stmt(
self.connection, stmt, self.logger.debug,
'INDIVIDUALS (+ THE GIVEN MUTATIONS) HAVING ALL THE SPECIFIED MUTATIONS ON THE SAME CHROMOSOME COPY'
)
self.connection.execute(stmt)
if self.log_sql_commands:
self.logger.debug('DROP TABLE ' + intermediate_table.name)
intermediate_table.drop(self.connection)
return Table(target_t_name,
db_meta,
autoload=True,
autoload_with=self.connection,
schema=default_schema_to_use_name)
def next_dagruns_to_examine(
cls,
state: DagRunState,
session: Session,
max_number: Optional[int] = None,
):
"""
Return the next DagRuns that the scheduler should attempt to schedule.
This will return zero or more DagRun rows that are row-level-locked with a "SELECT ... FOR UPDATE"
query, you should ensure that any scheduling decisions are made in a single transaction -- as soon as
the transaction is committed it will be unlocked.
:rtype: list[airflow.models.DagRun]
"""
from airflow.models.dag import DagModel
if max_number is None:
max_number = cls.DEFAULT_DAGRUNS_TO_EXAMINE
# TODO: Bake this query, it is run _A lot_
query = (
session.query(cls)
.filter(cls.state == state, cls.run_type != DagRunType.BACKFILL_JOB)
.join(
DagModel,
DagModel.dag_id == cls.dag_id,
)
.filter(
DagModel.is_paused == expression.false(),
DagModel.is_active == expression.true(),
)
)
if state == State.QUEUED:
# For dag runs in the queued state, we check if they have reached the max_active_runs limit
# and if so we drop them
running_drs = (
session.query(DagRun.dag_id, func.count(DagRun.state).label('num_running'))
.filter(DagRun.state == DagRunState.RUNNING)
.group_by(DagRun.dag_id)
.subquery()
)
query = query.outerjoin(running_drs, running_drs.c.dag_id == DagRun.dag_id).filter(
func.coalesce(running_drs.c.num_running, 0) < DagModel.max_active_runs
)
query = query.order_by(
nulls_first(cls.last_scheduling_decision, session=session),
cls.execution_date,
)
if not settings.ALLOW_FUTURE_EXEC_DATES:
query = query.filter(DagRun.execution_date <= func.now())
return with_row_locks(
query.limit(max_number), of=cls, session=session, **skip_locked(session=session)
)
def test_twelve(self):
t = self.tables.t
actual_ts = self.bind.scalar(
func.current_timestamp()).replace(tzinfo=None) - \
datetime.datetime(2012, 5, 10, 12, 15, 25)
self._test(
func.current_timestamp() -
func.coalesce(t.c.dtme, func.current_timestamp()),
{"day": actual_ts.days})
def investments_amount():
res = db.session.query(func.coalesce(func.sum(Investment.amount), 0))
time_from, time_to = get_timeframes()
if time_from > 0:
res = res.filter(Investment.time > time_from)
if time_to > 0:
res = res.filter(Investment.time < time_to)
return jsonify({"coins": str(res.scalar())})
