def __init__(self, table_name: str, filter_vals: dict = None, **kw): super().__init__(select_tablename=table_name, **kw) # get dbtable obj to inspect for keys keys = dbt.get_dbtable_keys(table_name) keys = [key.lower() for key in keys] a = T(table_name) q = Query.from_(a) \ .select(*keys) f.set_self(vars()) # helper to add filter vals eg {'unit': 'F301'} if isinstance(filter_vals, dict): field = list(filter_vals.keys())[0] val = filter_vals[field] field_ = a.field(field) if isinstance(val, (list, tuple)): # key: list of vals self.fltr.add(ct=field_.isin(val)) else: # single key: val self.fltr.add(vals=filter_vals)
def test_mssql_query_uses_double_quote_chars(self): q = MSSQLQuery.from_(self.table_abc).select( self.table_abc.foo).groupby( self.table_abc.foo).having(self.table_abc.buz == 'fiz') self.assertEqual( "SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'", str(q))
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 test_mssql_query_uses_double_quote_chars(self): q = (MSSQLQuery.from_(self.table_abc).select( self.table_abc.foo).groupby( self.table_abc.foo).having(self.table_abc.buz == "fiz")) self.assertEqual( 'SELECT "foo" FROM "abc" GROUP BY "foo" HAVING "buz"=\'fiz\'', str(q))
def test_groupby__no_alias_mssql(self): bar = self.t.bar.as_("bar01") q = MSSQLQuery.from_(self.t).select(fn.Sum(self.t.foo), bar).groupby(bar) self.assertEqual( 'SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar"', str(q))
def __init__(self, d_rng, minesite): super().__init__() a, b, d = self.a, self.b, self.d # get a groupby of all fcnumbers where unit.minesite = forthills q2 = Query.from_(a) \ .select(a.FCNumber) \ .select(fn.Count(a.FCNumber).as_('Count')) \ .left_join(b).on_field('FCNumber') \ .left_join(d).on_field('Unit') \ .where((d.MineSite == minesite) & (b.ReleaseDate.between(*d_rng))) \ .groupby(a.FCNumber) self.cols = [b.FCNumber, b.SubjectShort, b.Subject.as_( 'Info'), b.Classification, q2.Count, b.ReleaseDate, b.ExpiryDate] self.q = Query.from_(b) \ .inner_join(q2).on_field('FCNumber')
def __init__(self, theme='dark'): super().__init__(theme=theme) a, b = pk.Tables('viewFactoryCampaign', 'UnitID') cols = [a.FCNumber, a.Unit, b.MineSite, a.Subject, a.Complete, a.Classification, a.ReleaseDate, a.ExpiryDate] q = Query.from_(a).select(*cols) \ .left_join(b).on_field('Unit') f.set_self(vars())
def __init__(self, parent=None, **kw): super().__init__(parent=parent, **kw) a = self.select_table cols = [a.UserName, a.Email, a.LastLogin, a.Ver, a.Domain, a.UserGroup, a.MineSite, a.odbc_driver, a.install_dir] q = Query.from_(a) \ .orderby(a.LastLogin, order=Order.desc) f.set_self(vars())
def test_mssql_query_uses_double_quote_chars(self): q = MSSQLQuery.from_(self.table_abc).select( self.table_abc.foo ).groupby( self.table_abc.foo ).having( self.table_abc.buz == 'fiz' ) self.assertEqual("SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'", str(q))
def wrapper_query(self, q: Query) -> Query: """Call to wrap query before final call, keeps refreshtable args with base query""" a, b, e = self.a, self.b, self.e q._orderbys = [ ] # this is a pk attr, need to remove initial orderbys and redo # original cols already selected, just add rn c = q \ .select( (RowNumber() .over(a.unit, a.floc, a.dateadded) .orderby(e.model, e.model_base, e.equip_class, order=Order.desc)).as_('rn')) \ .as_('sq0') # use process_df to drop "rn" return Query.from_(c) \ .select(c.star) \ .where(c.rn == 1) \ .orderby(c.Unit, c.DateAdded, c.Modifier, c.GroupCO)
def __init__(self, **kw): super().__init__(**kw) a, b = Tables('Parts', 'EquipType') cols = [a.star, b.ModelBase] cols = [b.ModelBase, a.Model, a.PartNo, a.PartName, a.PartNameAlt] q = Query.from_(a) \ .select(*cols) \ .left_join(b).on_field('Model') f.set_self(vars())
def __init__(self, **kw): """Full table for app display/editing, NOT single list for emailing""" super().__init__(**kw) a = self.select_table cols = [a.UserGroup, a.MineSite, a.Email, a.Passover, a.WORequest, a.FCCancelled, a.PicsDLS, a.PRP, a.FCSummary, a.TSI, a.RAMP, a.Service, a.Parts, a.AvailDaily, a.AvailReports, a.FleetReport, a.SMRReport] q = Query.from_(a) \ .orderby(a.UserGroup, a.MineSite, a.Email) f.set_self(vars())
def get_column_definitions(self, schema, table, connection=None): columns = Table('COLUMNS', schema='INFORMATION_SCHEMA') columns_query = (MSSQLQuery.from_(columns, immutable=False).select( columns.COLUMN_NAME, columns.DATA_TYPE).where( columns.TABLE_SCHEMA == Parameter('%(schema)s')).where( columns.field('TABLE_NAME') == Parameter( '%(table)s')).distinct().orderby(columns.column_name)) return self.fetch(str(columns_query), connection=connection, parameters=dict(schema=schema, table=table))
def __init__(self, da=None, **kw): super().__init__(da=da, **kw) a, b, c, d = self.select_table, T('UnitID'), T('UserSettings'), T('EquipType') date_col = 'DateAdded' q = Query.from_(a) \ .left_join(b).on_field('Unit') \ .left_join(c).on(a.CreatedBy == c.UserName) \ .left_join(d).on(b.Model == d.Model) \ f.set_self(vars()) self.default_dtypes |= \ f.dtypes_dict('Int64', ['SMR', 'Unit SMR', 'Comp SMR', 'Part SMR', 'Pics']) | \ f.dtypes_dict('bool', ['Comp CO'])
def __init__(self, d: dt, minesite='FortHills', **kw): super().__init__(**kw) a, b = pk.Tables('UnitID', 'UnitSMR') d_lower = dt(d.year, d.month, 1) dates = (d_lower, d_lower + relativedelta(months=1) ) # (2020-12-01, 2021-01-01) cols = [a.Unit, b.DateSMR, b.SMR] q = Query.from_(a).select(*cols) \ .left_join(b).on_field('Unit') \ .where((a.MineSite == minesite) & (b.DateSMR.isin(dates) & (a.ExcludeMA.isnull()))) f.set_self(vars())
def __init__(self, unit, d_rng=None, **kw): super().__init__(**kw) a = T('UnitSMR') if d_rng is None: d_upper = dt.now() d_lower = d_upper + delta(days=-60) d_rng = (d_lower, d_upper) cols = ['Unit', 'DateSMR', 'SMR'] q = Query.from_(a) \ .where(a.Unit == unit) \ .where(a.DateSMR.between(d_rng[0], d_rng[1])) f.set_self(vars())
def __init__(self, recent_days=-120, da=None): super().__init__(da=da) a, b = self.a, self.b # subquery for ordering with row_number c = Query.from_(a).select( a.star, (RowNumber() .over(a.unit, a.component_id, a.modifier) .orderby(a.sample_date, order=Order.desc)).as_('rn')) \ .left_join(b).on_field('Unit') \ .where(a.sample_date >= dt.now() + delta(days=recent_days)) \ .as_('sq0') cols = [c.star] sq0 = c f.set_self(vars())
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 __init__(self, **kw): super().__init__(**kw) a = self.select_table cols = [ a.MineSite, a.Model, a.Unit, a.Component, a.Modifier, a.bench_smr, a.CurrentUnitSMR, a.SMRLastCO, a.CurrentComponentSMR, a.PredictedCODate, a.LifeRemaining, a.SNInstalled ] q = Query.from_(a) \ .orderby(a.MineSite, a.Unit) f.set_self(vars()) self.default_dtypes |= f.dtypes_dict('Int64', [ 'Bench SMR', 'Curr Unit SMR', 'SMR Last CO', 'Curr Comp SMR', 'Life Remaining' ])
def __init__(self, da=None, **kw): super().__init__(da=da, **kw) a = self.select_table b, c, d, e = pk.Tables('FCSummary', 'FCSummaryMineSite', 'UnitID', 'EquipType') self.default_dtypes.update( **f.dtypes_dict('Int64', ['SMR', 'Pics'])) self.formats.update({ 'SMR': '{:,.0f}', 'Pics': '{:,.0f}'}) q = Query.from_(a) \ .left_join(b).on_field('FCNumber') \ .left_join(d).on(d.Unit == a.Unit) \ .left_join(c).on((c.FCNumber == a.FCNumber) & (c.MineSite == d.MineSite)) \ .left_join(e).on(d.Model == e.Model) f.set_self(vars())
def __init__(self, d_rng: Tuple[dt, dt], minesite: str): super().__init__() self.use_cached_df = True a, b = pk.Tables('EventLog', 'UnitID') date = a.DateAdded.as_('Failure Date') cols = [date, a.TSINumber, a.Unit, b.Model, a.Title, a.SMR, a.ComponentSMR, a.PartNumber, a.FailureCause] q = Query.from_(a) \ .select(*cols) \ .left_join(b).on_field('Unit') \ .where(a.StatusTSI == 'Closed') \ .where(a.DateTSISubmission.between(*d_rng)) \ .where(b.MineSite == minesite) \ .where(~a.Title.like('fc %')) \ .orderby(a.DateAdded, a.Unit) f.set_self(vars())
def __init__(self, d_upper: dt, minesite: str = 'FortHills'): super().__init__() a, b = self.a, self.b # make full year range d_lower = d_upper + relativedelta(years=-1) + delta(days=1) _year_month = cfn('FORMAT', ['date', 'format']) # year_month(a.DateAdded, 'yyyy-MM') year_month = _year_month(a.DateTSISubmission, 'yyyy-MM') cols = [year_month.as_('period'), fn.Count(pk.terms.Star()).as_('num')] q = Query.from_(a) \ .select(*cols) \ .left_join(b).on_field('Unit') \ .where(a.StatusTSI == 'Closed') \ .where(b.MineSite == minesite) \ .where(~a.Title.like('fc %')) \ .where(a.DateTSISubmission.between(d_lower, d_upper)) \ .groupby(year_month) f.set_self(vars())
def __init__(self, **kw): super().__init__(**kw) limit_top = None self.default_dtypes |= f.dtypes_dict('Int64', ['unit_smr', 'component_smr']) a, b, c = self.select_table, T('UnitId'), T('EquipType') # cols = [a.star] cols = [ a.unit, a.component_id, a.modifier, a.sample_date, a.unit_smr, a.oil_changed, a.sample_rank, a.test_results, a.test_flags ] q = Query.from_(a) \ .left_join(b).on_field('Unit') \ .left_join(c).on(b.Model == c.Model) \ .orderby(a.unit, a.component_id, a.modifier) \ .orderby(a.sample_date, order=Order.desc) f.set_self(vars())
def __init__(self, col_name: str, minesite: str, usergroup: str = 'SMS', **kw): """Just the list we actually want to email Parameters --- name : str, column name to filter for 'x' minesite : str usergroup : str, default SMS Examples --- >>> email_list = EmailListShort(col_name='Passover', minesite='FortHills', usergroup='SMS').emails >>> ['*****@*****.**', '*****@*****.**'] """ super().__init__(**kw) a = self.a cols = [a.Email] q = Query.from_(a) f.set_self(vars())
def __init__(self, unit: str, d_upper: dt = None, d_lower: dt = None, **kw): """Select PLM report data for single unit. Parameters ---------- unit : str d_upper : dt d_lower : dt If None, default to d_upper - 6 months Not needed if just using max_date """ super().__init__(select_tablename='viewPLM') # use_cached_df = True # hmmm dont actually need this a = self.select_table cols = [a.star] if d_lower is None: # always start at first day of month if d_upper is None: d_upper = dt.now() d_lower = first_last_month(d_upper + delta(days=-180))[0] if not d_upper is None: d_rng = (d_lower, d_upper + delta(days=1) ) # between cuts off at T00:00:00 q = Query.from_(a) \ .orderby(a.datetime) f.set_self(vars()) self.set_default_args( ) # NOTE not sure if need this or if report always does it
def test_mssql_query_uses_double_quote_chars(self): q = MSSQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo) self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_mssql_query_uses_double_quote_chars(self): q = MSSQLQuery.from_("abc").select("foo", "bar") self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q))
def test_mssql_query_uses_double_quote_chars(self): q = MSSQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo) self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_mssql_query_uses_double_quote_chars(self): q = MSSQLQuery.from_('abc').select('foo', 'bar') self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q))
def get_query(self): c = self.sq0 return Query.from_(c) \ .where(c.rn == 1) \ .orderby(c.unit, c.component_id, c.modifier)