def __init__(self, **kw):
super().__init__(**kw)
a = self.select_table
isNumeric = cfn('ISNUMERIC', ['val'])
left = cfn('LEFT', ['val', 'num'])
c, d = pk.Tables('UnitSMR', 'EquipType')
days = fn.DateDiff(PseudoColumn('day'), a.DeliveryDate, fn.CurTimestamp())
remaining = Case().when(days <= 365, 365 - days).else_(0).as_('Remaining')
remaining2 = Case().when(days <= 365 * 2, 365 * 2 - days).else_(0)
ge_remaining = Case().when(isNumeric(left(a.Model, 1)) == 1, remaining2).else_(None).as_('GE_Remaining')
b = c.select(c.Unit, fn.Max(c.SMR).as_('CurrentSMR'), fn.Max(c.DateSMR).as_('DateSMR')).groupby(c.Unit).as_('b')
cols = [a.MineSite, a.Customer, d.EquipClass, a.Model, a.Serial, a.Unit,
b.CurrentSMR, b.DateSMR, a.DeliveryDate, remaining, ge_remaining]
q = Query.from_(a) \
.left_join(b).on_field('Unit') \
.left_join(d).on_field('Model') \
.orderby(a.MineSite, a.Model, a.Unit)
f.set_self(vars())
# NOTE lots of duplication with this pattern btwn avail/ac inspect/units/comp co
# can't remember how everything works and don't want to dig into it
self.stylemap_cols |= {'Model': dict(
cols=['Model'],
func=st.pipe_highlight_alternating,
da=dict(
subset=['Model'],
color='maroon',
theme=self.theme))}
def _indicator(q: QueryBuilder, indicator: ReportIndicator, column: Column) -> QueryBuilder:
if indicator.arithmetic == "sum":
return q.select(fn.Sum(parse_parameter(column.parameter)))
elif indicator.arithmetic == "avg":
return q.select(fn.Avg(parse_parameter(column.parameter)))
elif indicator.arithmetic == "max":
return q.select(fn.Max(parse_parameter(column.parameter)))
elif indicator.arithmetic == "min":
return q.select(fn.Min(parse_parameter(column.parameter)))
elif indicator.arithmetic == "count":
return q.select(fn.Count(parse_parameter(column.parameter)))
else:
return q.select(fn.Max(parse_parameter(column.parameter)))
def _indicator(q: QueryBuilder, indicator: ReportIndicator,
column: Column) -> QueryBuilder:
column_param = parse_parameter(column.parameter)
value_ = column_param.get("value")
if indicator.arithmetic == "sum":
return q.select(fn.Sum(value_))
elif indicator.arithmetic == "avg":
return q.select(fn.Avg(value_))
elif indicator.arithmetic == "max":
return q.select(fn.Max(value_))
elif indicator.arithmetic == "min":
return q.select(fn.Min(value_))
elif indicator.arithmetic == "count":
return q.select(fn.Count(value_))
else:
return q.select(fn.Max(value_))
def build_indicators(indicators: List[ReportIndicator],
dataset_columns: List[Column], dataset_query_alias):
_selects = []
_appear_in_group_by = []
columns = convent_column_list_to_dict(dataset_columns)
for indicator in indicators:
column: Column = columns.get(indicator.columnId, None)
if column is None:
continue
else:
field = Field(column.alias, None,
AliasedQuery(dataset_query_alias))
if indicator.arithmetic == "sum":
_selects.append(fn.Sum(field))
elif indicator.arithmetic == "avg":
_selects.append(fn.Avg(field))
elif indicator.arithmetic == "max":
_selects.append(fn.Max(field))
elif indicator.arithmetic == "min":
_selects.append(fn.Min(field))
elif indicator.arithmetic == "count":
_selects.append(fn.Count(field))
else:
_selects.append(field)
_appear_in_group_by.append(field)
return _selects, _appear_in_group_by
def insert_photo(self, filename):
ip = Table("indexed_photos")
q = Query.into(ip).columns('filename').insert(filename)
self.cursor.execute(str(q))
self.connection.commit()
q = Query.from_(ip).select(fn.Max(ip.idx))
self.cursor.execute(str(q))
res = self.cursor.fetchone()
return res[0]
def max_date_db(self, table=None, field=None, q=None, join_minesite=True, minesite='FortHills'):
a = T(table)
b = T('UnitID')
if q is None:
q = a.select(fn.Max(a[field]))
if join_minesite:
q = q.left_join(b).on_field('Unit') \
.where(b.MineSite == minesite)
val = self.query_single_val(q)
return f.convert_date(val)
def __init__(self, unit=None, **kw):
super().__init__(**kw)
a, b = pk.Tables('UnitSMR', 'UnitID')
_year = cfn('YEAR', ['date'])
_month = cfn('MONTH', ['date'])
year = _year(a.DateSMR)
month = _month(a.DateSMR)
_period = fn.Concat(year, '-', month)
cols = [a.Unit, _period.as_('Period'), fn.Max(a.SMR).as_('SMR')]
q = Query.from_(a) \
.left_join(b).on_field('Unit') \
.where(a.Unit == unit) \
.groupby(a.Unit, _period)
f.set_self(vars())
def make_latest_query(database: Database,
base_table: Table,
joins: Iterable[Join] = (),
dimensions: Iterable[Dimension] = ()):
query = database.query_cls.from_(base_table)
# Add joins
join_tables_needed_for_query = find_required_tables_to_join(
dimensions, base_table)
for join in find_joins_for_tables(joins, base_table,
join_tables_needed_for_query):
query = query.join(join.table, how=join.join_type).on(join.criterion)
for dimension in dimensions:
f_dimension_key = format_dimension_key(dimension.key)
query = query.select(fn.Max(dimension.definition).as_(f_dimension_key))
return query
def build_dimensions(dimensions: List[ReportDimension],
dataset_columns: List[Column], dataset_query_alias):
_selects = []
_groupbys = []
_orderbys = []
columns = convent_column_list_to_dict(dataset_columns)
for dimension in dimensions:
column: Column = columns.get(dimension.columnId, None)
if column is None:
continue
else:
field = Field(column.alias, None,
AliasedQuery(dataset_query_alias))
_selects.append(
fn.Max(field)
) # need put dimension field in select expr, and max mean first in group by
_groupbys.append(field)
_orderbys.append(field)
return _selects, _groupbys, _orderbys
def make_latest_query(
self,
base_table: Table,
joins: Iterable[Join] = (),
dimensions: Iterable[Field] = (),
):
query = self.query_cls.from_(base_table, immutable=False)
# Add joins
join_tables_needed_for_query = find_required_tables_to_join(
dimensions, base_table)
for join in find_joins_for_tables(joins, base_table,
join_tables_needed_for_query):
query = query.join(join.table,
how=join.join_type).on(join.criterion)
for dimension in dimensions:
f_dimension_key = alias_selector(dimension.alias)
query = query.select(
fn.Max(dimension.definition).as_(f_dimension_key))
return query
def get_basic_loan_info_query():
"""Get the basic query that we use for fetching a loans information"""
loans = Table('loans')
usrs = Table('users')
moneys = Table('moneys')
lenders = usrs.as_('lenders')
borrowers = usrs.as_('borrowers')
principals = moneys.as_('principals')
principal_currencies = Table('currencies').as_('principal_currencies')
principal_repayments = moneys.as_('principal_repayments')
repayment_events = Table('loan_repayment_events')
latest_repayments = Table('latest_repayments')
query = (Query.with_(
Query.from_(repayment_events).select(
repayment_events.loan_id,
ppfns.Max(
repayment_events.created_at).as_('latest_created_at')).groupby(
repayment_events.loan_id),
'latest_repayments').from_(loans).select(
lenders.username, borrowers.username, principal_currencies.code,
principal_currencies.symbol, principal_currencies.symbol_on_left,
principal_currencies.exponent, principals.amount,
principal_repayments.amount, loans.created_at,
latest_repayments.latest_created_at, loans.repaid_at,
loans.unpaid_at, loans.deleted_at).join(lenders).on(
lenders.id == loans.lender_id).join(borrowers).on(
borrowers.id == loans.borrower_id).join(principals).on(
principals.id ==
loans.principal_id).join(principal_currencies).on(
principal_currencies.id == principals.currency_id).
join(principal_repayments).on(
principal_repayments.id ==
loans.principal_repayment_id).left_join(latest_repayments).on(
latest_repayments.loan_id == loans.id))
return query
def _dimension(q: QueryBuilder, dimension: ReportDimension, column: Column):
column_param = parse_parameter(column.parameter)
value_ = column_param.get("value")
return q.select(fn.Max(value_))
def _dimension(q: QueryBuilder, dimension: ReportDimension, column: Column):
return q.select(fn.Max(parse_parameter(column.parameter)))
def max(param):
return Column(fn.Max(_ensure_col(param).term))
def max_date(self):
a = T('viewPLM')
q = a.select(fn.Max(a.DateTime)) \
.where(a.Unit == self.unit)
return db.max_date_db(q=q)
def test__max(self):
q = Q.from_('abc').select(fn.Max(F('foo')))
self.assertEqual('SELECT MAX(\"foo\") FROM \"abc\"', str(q))
def test__max(self):
q = Q.from_("abc").select(fn.Max(F("foo")))
self.assertEqual('SELECT MAX("foo") FROM "abc"', str(q))
