def createDeliveryForRequest(db, id):
default_time = dt.datetime(2001, 1, 1)
# get list of sidekicks that are currently online
# online_sidekicks = db.session.query(Shifts.user_id)\
# .filter(Shifts.clock_in<func.now(), Shifts.clock_out==default_time)\
# .all()
# # convert db query tuple format-- [(1,), (2,)] into an array of sidekick ids (1, 2)
# print online_sidekicks
# online_sidekicks = zip(*online_sidekicks)[0]
deliveries = db.session.query(Deliveries.sidekick_id, func.count('*')\
.label('delivery_count'))\
.filter(Deliveries.completed==default_time)\
.group_by(Deliveries.sidekick_id).subquery()
sidekick_deliveries_array = db.session.query(Users, deliveries.c.delivery_count)\
.outerjoin(deliveries, Users._id==deliveries.c.sidekick_id)\
.filter(Users.type=='sidekick')\
.filter(Users.online==True)\
.order_by(nullsfirst(asc(deliveries.c.delivery_count)))
# if there is an online sidekick, assign delivery to new request
if (sidekick_deliveries_array.count() > 0):
[sk, count] = sidekick_deliveries_array[0]
print "Assigning delivery for request", id, "to sidekick", sk._id
delivery = Deliveries(id, sk._id)
db.session.add(delivery)
db.session.commit()
else:
print "No sidekicks currently online. No Delivery created for Request", id
def createDeliveryForRequest(db, id):
default_time = dt.datetime(2001, 1, 1)
# get list of sidekicks that are currently online
# online_sidekicks = db.session.query(Shifts.user_id)\
# .filter(Shifts.clock_in<func.now(), Shifts.clock_out==default_time)\
# .all()
# # convert db query tuple format-- [(1,), (2,)] into an array of sidekick ids (1, 2)
# print online_sidekicks
# online_sidekicks = zip(*online_sidekicks)[0]
deliveries = db.session.query(Deliveries.sidekick_id, func.count('*')\
.label('delivery_count'))\
.filter(Deliveries.completed==default_time)\
.group_by(Deliveries.sidekick_id).subquery()
sidekick_deliveries_array = db.session.query(Users, deliveries.c.delivery_count)\
.outerjoin(deliveries, Users._id==deliveries.c.sidekick_id)\
.filter(Users.type=='sidekick')\
.filter(Users.online==True)\
.order_by(nullsfirst(asc(deliveries.c.delivery_count)))
# if there is an online sidekick, assign delivery to new request
if (sidekick_deliveries_array.count() > 0):
[sk, count] = sidekick_deliveries_array[0]
print "Assigning delivery for request", id, "to sidekick", sk._id
delivery = Deliveries(id, sk._id)
db.session.add(delivery)
db.session.commit()
else:
print "No sidekicks currently online. No Delivery created for Request", id
def label_places(timeout):
"""Add labels to places that have no labels, or position has shifted
significantly since labeling.
Reverse geocoding api url and rate limit are read from configuration, for
example:
REVERSE_GEOCODING_URI_TEMPLATE = 'https://search.mapzen.com/v1/reverse?api_key=API_KEY&sources=osm&size=20&point.lat={lat}&point.lon={lon}'
REVERSE_GEOCODING_QUERIES_PER_SECOND = 6"""
print("label_places up to %ds" % timeout)
url_template = app.config.get('REVERSE_GEOCODING_URI_TEMPLATE')
qps = app.config.get('REVERSE_GEOCODING_QUERIES_PER_SECOND')
if None in (url_template, qps):
log.info("REVERSE_GEOCODING_URI_TEMPLATE or " +
"REVERSE_GEOCODING_QUERIES_PER_SECOND unconfigured, places will " +
"not be labeled")
return
places = db.metadata.tables["places"]
labdist = func.ST_Distance(places.c.coordinate, places.c.label_coordinate)
t0 = time.time()
for p in db.engine.execute(select(
[ places.c.id,
func.ST_AsGeoJSON(places.c.coordinate).label("geojson")],
or_(labdist == None, labdist > DEST_RADIUS_MAX), # = clust dist / 2
order_by=nullsfirst(desc(labdist)))):
lon, lat = point_coordinates(p)
url = url_template.format(lat=lat, lon=lon)
# response = json.loads(urllib.request.urlopen(url, timeout=timeout).read())
response = requests.get(url, timeout=timeout).json()
names, nameslower = [], set()
for prop in ["street", "name"]:
for feat in response["features"]:
name = feat["properties"].get(prop)
name = name and re.split(",", name)[0]
if name and name.lower() not in nameslower:
names.append(name)
nameslower.add(name.lower())
label = " / ".join(names[:2])
coordstr = "{:.4f}/{:.4f}".format(lat, lon)
label = label or coordstr # fallback
# Show progress due to rate limiting. Force encoding in case of pipe
print(coordstr, label.encode("utf-8"))
db.engine.execute(places.update(
places.c.id == p.id,
{"label": label, "label_coordinate": "POINT(%f %f)" % (lon, lat)}))
# Enforce configured API queries per second rate limit
time.sleep(1./qps)
if time.time() - t0 >= timeout:
break
def obter_documentos_externos():
query = session().query(
TB_DOCUMENTO.c.docu_nr_externo, TB_DOCUMENTO.c.docu_dk,
TB_PROCESSO.c.prtj_dt_ultima_vista).outerjoin(
TB_PROCESSO, TB_DOCUMENTO.c.docu_nr_externo ==
TB_PROCESSO.c.prtj_cd_numero_processo
).filter(TB_DOCUMENTO.c.docu_mate_dk == 4).filter(
func.length(TB_DOCUMENTO.c.docu_nr_externo) == 20).order_by(
nullsfirst(TB_PROCESSO.c.prtj_dt_ultima_vista), )
return [(doc[0], doc[1]) for doc in query]
def get_needing_update(self, quantity=10):
"""
Gets a number of capsuleers ordered by their last_checked value with
nulls coming first.
:param quantity: the number of capsuleers to be returned
"""
log.info('Finding {} capsuleers to update.'.format(quantity))
order_by = nullsfirst(self.__model__.last_checked.asc())
return self._find().order_by(order_by).limit(quantity)
def availability_for_advisor(cls, authorized_user_id, dept_code):
results = cls.query.filter_by(authorized_user_id=authorized_user_id, dept_code=dept_code).order_by(
cls.weekday,
nullsfirst(cls.date_override),
cls.start_time,
)
availability = {}
for weekday, group_by_weekday in groupby(results, lambda x: x.weekday):
availability[weekday] = {}
for date_key, group_by_date_override in groupby(group_by_weekday, lambda x: x.date_override):
if date_key is None:
date_key = 'recurring'
else:
date_key = str(date_key)
availability[weekday][date_key] = [cls.to_api_json(a.id, a.start_time, a.end_time) for a in group_by_date_override]
return availability
def build_sqlalchemy_ordering(order_params, visible_fields):
'''
returns a scalar or list of ClauseElement objects which will comprise
the ORDER BY clause of the resulting select.
@param order_params passed as list in the request.GET hash
'''
DEBUG_ORDERING = False or logger.isEnabledFor(logging.DEBUG)
if DEBUG_ORDERING:
logger.info('build sqlalchemy ordering: %s, visible fields: %s',
order_params,visible_fields.keys())
if order_params and isinstance(order_params, basestring):
# standard, convert single valued list params
order_params = [order_params]
order_clauses = []
for order_by in order_params:
field_name = order_by
order_clause = None
if order_by.startswith('-'):
field_name = order_by[1:]
order_clause = nullslast(desc(column(field_name)))
if ( field_name in visible_fields
and visible_fields[field_name]['data_type'] == 'string'):
# For string field ordering, double sort as numeric and text
order_clause = text(
"(substring({field_name}, '^[0-9]+'))::int desc " # cast to integer
",substring({field_name}, '[^0-9_].*$') " # works as text
.format(field_name=field_name))
else:
order_clause = nullsfirst(asc(column(field_name)))
if ( field_name in visible_fields
and visible_fields[field_name]['data_type'] == 'string'):
order_clause = text(
"(substring({field_name}, '^[0-9]+'))::int "
",substring({field_name}, '[^0-9_].*$') "
.format(field_name=field_name))
if field_name in visible_fields:
order_clauses.append(order_clause)
else:
logger.warn(
'order_by field %r not in visible fields, skipping: ',
order_by)
if DEBUG_ORDERING:
logger.info('order_clauses %s',order_clauses)
return order_clauses
def sort(self, keys,direction = None,explicit_nullsfirst = False):
#we sort by a single argument
if direction:
keys = ((keys,direction),)
order_bys = []
for key,direction in keys:
if direction > 0:
#when sorting in ascending direction, NULL values should come first
if explicit_nullsfirst:
direction = lambda *args,**kwargs: nullsfirst(asc(*args,**kwargs))
else:
direction = asc
else:
#when sorting in descending direction, NULL values should come last
if explicit_nullsfirst:
direction = lambda *args,**kwargs: nullslast(desc(*args,**kwargs))
else:
direction = desc
order_bys.append((key,direction))
self.order_bys = order_bys
self.objects = None
return self
def sort(self, keys,direction = None,explicit_nullsfirst = False):
#we sort by a single argument
if direction:
keys = ((keys,direction),)
order_bys = []
for key,direction in keys:
if direction > 0:
#when sorting in ascending direction, NULL values should come first
if explicit_nullsfirst:
direction = lambda *args,**kwargs: nullsfirst(asc(*args,**kwargs))
else:
direction = asc
else:
#when sorting in descending direction, NULL values should come last
if explicit_nullsfirst:
direction = lambda *args,**kwargs: nullslast(desc(*args,**kwargs))
else:
direction = desc
order_bys.append((key,direction))
self.order_bys = order_bys
self.objects = None
return self
def label_places(timeout):
"""Add labels to places that have no labels, or position has shifted
significantly since labeling.
Reverse geocoding api url and rate limit are read from configuration, for
example:
REVERSE_GEOCODING_URI_TEMPLATE = 'https://search.mapzen.com/v1/reverse?api_key=API_KEY&sources=osm&size=20&point.lat={lat}&point.lon={lon}'
REVERSE_GEOCODING_QUERIES_PER_SECOND = 6"""
print "label_places up to %ds" % timeout
url_template = app.config.get('REVERSE_GEOCODING_URI_TEMPLATE')
qps = app.config.get('REVERSE_GEOCODING_QUERIES_PER_SECOND')
if None in (url_template, qps):
log.info(
"REVERSE_GEOCODING_URI_TEMPLATE or " +
"REVERSE_GEOCODING_QUERIES_PER_SECOND unconfigured, places will " +
"not be labeled")
return
places = db.metadata.tables["places"]
labdist = func.ST_Distance(places.c.coordinate, places.c.label_coordinate)
t0 = time.time()
for p in db.engine.execute(
select(
[
places.c.id,
func.ST_AsGeoJSON(places.c.coordinate).label("geojson")
],
or_(labdist == None,
labdist > DEST_RADIUS_MAX), # = clust dist / 2
order_by=nullsfirst(desc(labdist)))):
lon, lat = point_coordinates(p)
url = url_template.format(lat=lat, lon=lon)
response = json.loads(urllib2.urlopen(url, timeout=timeout).read())
names, nameslower = [], set()
for prop in ["street", "name"]:
for feat in response["features"]:
name = feat["properties"].get(prop)
name = name and re.split(",", name)[0]
if name and name.lower() not in nameslower:
names.append(name)
nameslower.add(name.lower())
label = " / ".join(names[:2])
coordstr = "{:.4f}/{:.4f}".format(lat, lon)
label = label or coordstr # fallback
# Show progress due to rate limiting. Force encoding in case of pipe
print coordstr, label.encode("utf-8")
db.engine.execute(
places.update(places.c.id == p.id, {
"label": label,
"label_coordinate": "POINT(%f %f)" % (lon, lat)
}))
# Enforce configured API queries per second rate limit
time.sleep(1. / qps)
if time.time() - t0 >= timeout:
break
def direction(*args, **kwargs):
return nullsfirst(asc(*args, **kwargs))
