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))