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_can_be_used_in_orderby(self):
query = Query.from_(self.table1).select(
self.table1.abcde.as_("some_name")).orderby(
PseudoColumn("some_name"))
self.assertEqual(
str(query),
'SELECT "abcde" "some_name" FROM "table1" ORDER BY some_name')
def _get_values_from_table(self,
fields,
filters,
doctype,
as_dict,
debug,
order_by=None,
update=None,
for_update=False):
field_objects = []
for field in fields:
if "(" in field or " as " in field:
field_objects.append(PseudoColumn(field))
else:
field_objects.append(field)
criterion = self.query.build_conditions(table=doctype,
filters=filters,
orderby=order_by,
for_update=for_update)
if isinstance(fields, (list, tuple)):
query = criterion.select(*field_objects)
else:
if fields == "*":
query = criterion.select(fields)
as_dict = True
r = self.sql(query, as_dict=as_dict, debug=debug, update=update)
return r
def _process_date_diff_arg(arg):
date_fnc = CustomFunction("date", ["col1"])
if arg == "now":
date = PseudoColumn('current_date')
else:
if "." in arg:
arg_list = arg.split(".")
topic_name = arg_list[0].strip()
factor_name = arg_list[1].strip()
date = Table("topic_" + topic_name).as_(topic_name)[factor_name]
else:
date = date_fnc(arg)
return date
def build_product_flow_cost(process_ref_ids, time):
"""
Build a query to get the product flow costs from a list of process reference ids using a sqlite openLCA database.
:param sqlite3.Connection conn: database connection
:param list[str] process_ref_ids: list of process reference ids
:param list time: list of time labels
:return SQL string
"""
exchanges = Table('TBL_EXCHANGES')
flows = Table('TBL_FLOWS')
processes = Table('TBL_PROCESSES')
locations = Table('TBL_LOCATIONS')
# get the process ids from the ref ids
process_ids = processes.select(processes.ID).where(
processes.REF_ID.isin(process_ref_ids))
# sub-query the exchanges table to limit join
sq = Query\
.from_(exchanges) \
.select(exchanges.F_OWNER, exchanges.COST_VALUE, exchanges.F_CURRENCY, exchanges.F_FLOW,
exchanges.F_UNIT, exchanges.RESULTING_AMOUNT_VALUE) \
.where(exchanges.F_OWNER.isin(process_ids))
# join exchanges to flows, processes, locations
first_time = PseudoColumn("'" + time[0] + "'")
q = Query\
.from_(sq) \
.left_join(flows).on(flows.ID == sq.F_FLOW) \
.left_join(processes).on(processes.ID == sq.F_OWNER) \
.left_join(locations).on(pf.Cast(processes.F_LOCATION, 'int') == locations.ID) \
.select(
flows.REF_ID.as_('F'), processes.REF_ID.as_('P'), first_time.as_('T'), sq.COST_VALUE
)\
.where(flows.FlOW_TYPE == 'PRODUCT_FLOW')
return str(q)
def date_add(self, field, date_part, interval):
return _MSSQLDateAdd(PseudoColumn(date_part), interval, field)
def trunc_date(self, field, interval):
# Useful docs on this here: http://www.silota.com/docs/recipes/sql-server-date-parts-truncation.html
return self.date_add(0, interval,
DateDiff(PseudoColumn(interval), 0, field))
def q(self):
d_rng, period, model, minesite, unit = self.d_rng, self.period, self.model, self.minesite, self.unit
a, b = pk.Tables('Downtime', 'UnitID')
hrs_in_period = cfn('tblHrsInPeriod',
['d_lower', 'd_upper', 'minesite', 'period'])
period_range = cfn('period_range', ['startdate', 'enddate', 'period'])
_month = cfn('MONTH', ['date'])
_year = cfn('YEAR', ['date'])
iso_year = cfn('dbo.iso_year', ['date'])
datepart = cfn('DATEPART', ['period_type', 'date'])
month = _month(a.ShiftDate)
week = datepart(PseudoColumn('iso_week'), a.ShiftDate)
if period == 'month':
year = _year(a.ShiftDate)
_period = fn.Concat(year, '-', month) # .as_('period')
else:
year = iso_year(
a.ShiftDate
) # only use iso_year (slow custom function) when grouping by week
_period = fn.Concat(year, '-', week) # .as_('period')
# Create all week/month periods in range crossed with units
q_prd = Query.from_(period_range(d_rng[0], d_rng[1],
period)).select('period')
q_base = Query.from_(b) \
.select(q_prd.period, b.Unit) \
.cross_join(q_prd).cross() \
.where(Criterion.all([
b.MineSite == minesite,
b.model.like(model)]))
# Unit, Total, SMS, Suncor
cols_dt = [
_period.as_('period'), a.Unit,
fn.Sum(a.Duration).as_('Total'),
fn.Sum(a.SMS).as_('SMS'),
fn.Sum(a.Suncor).as_('Suncor')
]
q_dt = Query.from_(a) \
.select(*cols_dt) \
.where(Criterion.all([
a.ShiftDate.between(d_rng[0], d_rng[1]),
a.Duration > 0.01])) \
.groupby(a.Unit, _period)
# in case need historical data for single unit
if not unit is None:
q_dt = q_dt.where(a.Unit == unit)
q_base = q_base.where(b.Unit == unit)
cols1 = [q_base.period, q_base.Unit, q_dt.Total, q_dt.SMS, q_dt.Suncor]
q1 = Query.from_(q_base) \
.select(*cols1) \
.left_join(q_dt).on_field('Unit', 'Period')
q_hrs = Query.from_(hrs_in_period(d_rng[0], d_rng[1], minesite,
period)).select('*')
cols = [
b.Model, b.DeliveryDate, q1.star, q_hrs.ExcludeHours_MA,
q_hrs.ExcludeHours_PA,
Case().when(b.AHSActive == 1,
'AHS').else_('Staffed').as_('Operation')
]
return Query.from_(q1) \
.select(*cols) \
.left_join(b).on_field('Unit') \
.left_join(q_hrs).on_field('Unit', 'Period') \
.where(b.Model.like(model))
def test_can_be_used_in_a_where_clause(self):
query = (Query.from_(self.table1).where(
PseudoColumn("abcde") > 1).select(self.table1.is_active))
self.assertEqual(str(query),
'SELECT "is_active" FROM "table1" WHERE abcde>1')
def test_can_be_used_in_a_select_statement(self):
query = (Query.from_(self.table1).where(
self.table1.is_active == 1).select(PseudoColumn("abcde")))
self.assertEqual(str(query),
'SELECT abcde FROM "table1" WHERE "is_active"=1')
def current_date() -> PseudoColumn:
return PseudoColumn('current_date')