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)