def __call__(self):
query = self._query.order_by(*self.order_by())
query = query.slice(self.batch_start, self.batch_end)
hebergements = []
for heb in query.all():
if isinstance(heb, tuple) and isinstance(heb[0], Hebergement):
hebergement = heb[0]
for key in heb.keys()[1:]:
value = getattr(heb, key)
setattr(hebergement, key, value)
hebergements.append(hebergement)
elif isinstance(heb, tuple):
directlyProvides(heb, IHebergementInSearch)
hebergements.append(heb)
else:
hebergements.append(heb)
reference = self.data.get('reference')
if len(hebergements) == 1 and reference and self.selected_page() != 0:
# if searching by name and finding only one gite, redirect to its
# description
hebPk = hebergements[0].heb_pk
hebergement = session().query(Hebergement).get(hebPk)
hebURL = getMultiAdapter((hebergement.__of__(self.context.hebergement),
self.request), name="url")
self.request.response.redirect(str(hebURL))
return ''
return hebergements
def filters(self):
filters = []
query = session().query(MetadataType)
query = query.order_by(MetadataType.met_typ_sort_ord)
metadataTypes = query.all()
for metadataType in metadataTypes:
metadataTypeId = metadataType.met_typ_id
query = session().query(Metadata)
query = query.options(FromCache('gdw'))
query = query.filter(Metadata.met_filterable == True,
Metadata.metadata_type_id == metadataType.met_typ_id)
metadata = query.all()
if metadata:
filters.append({'id': metadataTypeId,
'metadata': metadata})
return filters
def hebs_with_active_calendar(self):
""" Returns the heb_pk for the heb who have an active calendar """
query = session().query(mappers.Hebergement.heb_pk)
query = query.filter(sa.and_(
mappers.Hebergement.heb_pk.in_(self.heb_pks),
mappers.Hebergement.heb_calendrier_proprio == 'actif'))
return [heb.heb_pk for heb in query.all()]
def test_simple_get(self):
PK = 99
heb = Hebergement(heb_pk=PK)
sess = session()
sess.add(heb)
sess.flush()
self.assertEqual(heb.heb_pk, Hebergement.first(heb_pk=PK).heb_pk)
def add_tarifs_columns(self):
query = session().query(mappers.TarifsType)
# Query Gite
querygite = query.filter(mappers.TarifsType.gite == True)
querygite = querygite.filter(mappers.TarifsType.chambre == False)
# Query Chambre
querychambre = query.filter(mappers.TarifsType.chambre == True)
querychambre = querychambre.filter(mappers.TarifsType.gite == False)
# Query Both
queryboth = query.filter(mappers.TarifsType.gite == True)
queryboth = queryboth.filter(mappers.TarifsType.chambre == True)
for tarifstype in querygite.all():
self.comparison_list.add_element(ComparisonColumn(
u'%s_%s' % (tarifstype.type, tarifstype.subtype),
u'%s (%s)' % (self.translate(tarifstype.type),
self.translate(tarifstype.subtype)),
type="gites"))
for tarifstype in querychambre.all():
self.comparison_list.add_element(ComparisonColumn(
u'%s_%s' % (tarifstype.type, tarifstype.subtype),
u'%s (%s)' % (self.translate(tarifstype.type),
self.translate(tarifstype.subtype)),
type="chambres"))
for tarifstype in queryboth.all():
self.comparison_list.add_element(ComparisonColumn(
u'%s_%s' % (tarifstype.type, tarifstype.subtype),
u'%s (%s)' % (self.translate(tarifstype.type),
self.translate(tarifstype.subtype))))
def getCommuneForLocalite(self, localite):
query = session().query(Commune.com_nom)
query = query.join('relatedHebergement')
query = query.options(FromCache('gdw'))
query = query.filter(Hebergement.heb_localite == localite)
query = query.limit(1)
return query.scalar()
def heb_types(self):
query = session().query(mappers.Hebergement.heb_pk,
mappers.TypeHebergement.type_heb_code)
query = query.options(FromCache('gdw'))
query = query.join('type')
query = query.filter(
mappers.Hebergement.heb_pk.in_(self.table.heb_pks))
return [self.table._hosting_type(h.type_heb_code) for h in query.all()]
def update(self):
query = session().query(mappers.Hebergement.heb_pk)
query = query.join('proprio')
query = query.filter(sa.and_(
mappers.Hebergement.heb_pk.in_(self.request.get('heb_pk')),
mappers.Hebergement.heb_site_public == '1',
mappers.Proprio.pro_etat == True))
self.heb_pks = set([heb.heb_pk for heb in query.all()])
def add_metadata_columns(self):
query = session().query(mappers.Metadata).filter(
mappers.Metadata.met_filterable == True)
query = query.order_by(mappers.Metadata.metadata_type_id)
for metadata in query.all():
self.comparison_list.add_element(ComparisonColumn(
u'met_%s' % metadata.met_pk, metadata.getTitre(self.language),
table="metadata"))
def cities(self):
query = session().query(distinct(Commune.com_nom).label('com_nom'))
query = query.join('relatedHebergement', 'proprio')
query = query.options(FromCache('gdw'))
query = query.filter(and_(Commune.com_nom != None,
Commune.com_nom != 'None'))
query = query.filter(Hebergement.heb_site_public == '1')
query = query.filter(Proprio.pro_etat == True)
return [city.com_nom for city in query.all()]
def localities(self):
query = session().query(distinct(Hebergement.heb_localite).label('heb_localite'))
query = query.join('proprio')
query = query.options(FromCache('gdw'))
query = query.filter(and_(Hebergement.heb_localite != None,
Hebergement.heb_localite != 'None'))
query = query.filter(Hebergement.heb_site_public == '1')
query = query.filter(Proprio.pro_etat == True)
return [heb.heb_localite for heb in query.all()]
def _get_metadata(self, metadata_id):
from gites.db import session
from gites.db.content.hebergement.linkhebergementmetadata import LinkHebergementMetadata
session = session()
query = session.query(LinkHebergementMetadata.link_met_value)
query = query.options(FromCache('gdw'))
query = query.join('hebergement').join('metadata_info')
query = query.filter(Hebergement.heb_pk == self.context.heb_pk)
return query.filter(Metadata.met_id == metadata_id).scalar()
def get_metadatas(self, heb_pk):
""" Adds the metadatas for the given heb pk """
query = session().query(mappers.LinkHebergementMetadata.metadata_fk,
mappers.LinkHebergementMetadata.link_met_value)
query = query.join('metadata_info')
query = query.filter(sa.and_(
mappers.LinkHebergementMetadata.heb_fk == heb_pk,
mappers.Metadata.met_filterable == True))
return dict([('met_%s' % m.metadata_fk, m.link_met_value) for m \
in query.all()])
def test_proprio_link(self):
PK = 98
heb = Hebergement(heb_pk=PK, heb_pro_fk=PK)
pro = Proprio(pro_pk=PK)
sess = session()
sess.add(heb)
sess.add(pro)
sess.flush()
self.assertEqual(heb.proprio.pro_pk, Proprio.first(pro_pk=PK).pro_pk)
heb = sess.query(Hebergement).first()
heb.proprio
def count_classifications(self, classification):
fetcher = component.getMultiAdapter((self.context, self.view,
self.request),
IHebergementsFetcher)
subquery = fetcher._classifications_query.subquery()
from gites.db.content import LinkHebergementEpis
query = session().query(LinkHebergementEpis.heb_pk)
query = query.options(FromCache('gdw'))
query = query.filter(LinkHebergementEpis.heb_nombre_epis == int(classification))
query = query.filter(LinkHebergementEpis.heb_pk == subquery.c.heb_pk)
return query.count()
def query(self):
query = session().query(mappers.Proprio.pro_langue,
mappers.TypeHebergement.type_heb_code,
mappers.LinkHebergementEpis.heb_nombre_epis)
query = query.options(FromCache('gdw'))
query = query.join('hebergements', 'type').outerjoin('hebergements', 'epis')
for c in self.heb_columns + self.heb_add_columns:
query = query.add_column(getattr(mappers.Hebergement, c))
query = query.filter(
mappers.Hebergement.heb_pk.in_(self.table.heb_pks))
return query.order_by(mappers.Hebergement.heb_pk)
def _query(self):
query = session().query(Hebergement,
TypeHebergement.type_heb_code.label('heb_type_code'),
).join('type').join('commune').outerjoin('epis').join('proprio').join('app')
query = query.options(
FromCache('gdw'))
typeHeb = self.context
query = query.filter(Hebergement.heb_typeheb_fk == typeHeb.type_heb_pk)
query = query.filter(sa.and_(Hebergement.heb_site_public == '1',
Proprio.pro_etat == True))
return query
def count_hebs(self, metadata_id):
fetcher = component.getMultiAdapter((self.context, self.view,
self.request),
IHebergementsFetcher)
subquery = fetcher._query.subquery()
from gites.db.content import LinkHebergementMetadata
query = session().query(LinkHebergementMetadata.heb_fk)
query = query.options(FromCache('gdw'))
query = query.filter(LinkHebergementMetadata.link_met_value == True)
query = query.filter(LinkHebergementMetadata.metadata_fk == metadata_id)
query = query.filter(LinkHebergementMetadata.heb_fk == subquery.c.heb_pk)
return query.count()
def get_table_hote(self, heb_pk):
""" Returns the host tables metadatas for the given heb pk """
query = session().query(mappers.LinkHebergementMetadata)
query = query.join('metadata_info')
query = query.filter(sa.and_(
mappers.LinkHebergementMetadata.heb_fk == heb_pk,
mappers.LinkHebergementMetadata.link_met_value == True,
mappers.Metadata.met_id.in_(['heb_tabhot_repas_familial',
'heb_tabhot_gourmand',
'heb_tabhot_gastronomique'])))
result = query.first()
if result:
return result.metadata_info.getTitre(self.language)
def _query(self):
point = None
if hasattr(self.context, 'is_geolocalized') and self.context.is_geolocalized:
geomarker = IMarker(self.context)
user_range = self.context.getRange()
point = 'POINT(%s %s)' % (geomarker.longitude, geomarker.latitude)
point = geoalchemy.base.WKTSpatialElement(point, srid=3447)
if point is not None:
query = session().query(Hebergement,
Hebergement.heb_location.distance_sphere(point).label('distance'),
TypeHebergement.type_heb_code.label('heb_type_code'),
TypeHebergement.type_heb_type.label('heb_type_type')
)
else:
query = session().query(Hebergement,
TypeHebergement.type_heb_code.label('heb_type_code'),
TypeHebergement.type_heb_type.label('heb_type_type')
)
query = query.join('type').join('commune').outerjoin('epis').join('proprio').join('app')
query = query.options(
FromCache('gdw'))
subquery = session().query(LinkHebergementMetadata.heb_fk)
criteria = set()
criteria.update(
self.getCriteria(),
self.selected_keywords())
if criteria:
subquery = subquery.filter(LinkHebergementMetadata.metadata_fk.in_(criteria))
subquery = subquery.filter(LinkHebergementMetadata.link_met_value == True)
subquery = subquery.group_by(LinkHebergementMetadata.heb_fk)
subquery = subquery.having(sa.func.count() == len(criteria))
subquery = subquery.subquery()
query = query.filter(Hebergement.heb_pk == subquery.c.heb_fk)
if hasattr(self.context, 'is_geolocalized') and self.context.is_geolocalized:
query = query.filter(Hebergement.heb_location.distance_sphere(point) < 1000 * user_range)
query = query.filter(sa.and_(Hebergement.heb_site_public == '1',
Proprio.pro_etat == True))
return query
def update(self):
""" Adds css classes for the hosting type """
super(HebComparisonTable, self).update()
query = session().query(mappers.Hebergement.heb_pk,
mappers.TypeHebergement.type_heb_code)
query = query.join('type')
query = query.filter(mappers.Hebergement.heb_pk.in_(self.heb_pks))
query = query.order_by(mappers.Hebergement.heb_pk)
for idx, hosting in enumerate(query.all()):
column = self.columns[idx + 1]
classes = [self._hosting_type(hosting.type_heb_code)]
if column.cssClasses.get('td'):
classes.append(column.cssClasses.get('td'))
column.cssClasses['td'] = ' '.join(classes)
def get_tarifs(self, heb_pk):
""" Adds the tarifs for the given heb pk """
query = session().query(mappers.Tarifs)
query = query.filter(sa.and_(
mappers.Tarifs.heb_pk == heb_pk,
mappers.Tarifs.valid == True))
dict = {}
for tarif in query.all():
type = u'%s_%s' % (tarif.type, tarif.subtype)
prix = []
prix.extend(self.render_tarif_field(tarif, 'min', after=u' €', default=u'-').split())
prix.extend(self.render_tarif_field(tarif, 'max', before=u' / ', after=u' €', default=u'-').split())
prix.extend(self.render_tarif_field(tarif, 'cmt').split())
prix = u' '.join(prix)
if tarif.cmt == 'CHECKED':
prix = True
dict[type] = prix
return dict
def __str__(self):
if isinstance(self.context, tuple):
if hasattr(self.context, 'heb_pk'):
context = Hebergement.first(heb_pk=self.context.heb_pk)
else:
return ''
else:
context = aq_inner(self.context)
portal = api.portal.get()
container = portal.hebergement
from gites.db import session
session = session()
commune = session.query(Commune.com_id).options(FromCache('gdw')).filter_by(com_pk=context.heb_com_fk).first()
language = self.request.get('LANGUAGE', 'en')
typeHeb = session.query(TypeHebergement).options(FromCache('gdw')).filter_by(type_heb_pk=context.heb_typeheb_fk).first()
hebId = context.heb_id
return "%s/%s/%s/%s" % (container.absolute_url(),
#self.getHebType(self.context.heb_typeheb_fk, language),
typeHeb.getId(language),
commune.com_id,
hebId,
)
def test_simple_get(self):
prov = Province(prov_pk=1)
sess = session()
sess.add(prov)
sess.flush()
def apply_filters(self, query, group=False, ignore_classification=False):
reference = self.data.get('reference')
capacity = self.data.get('capacityMin') or self.data.get('form.widgets.capacityMin')
heb_type = self.data.get('form.widgets.hebergementType') or self.data.get('form.widgets.hebergementType[]')
show_gites = heb_type and 'gite-meuble' in heb_type
show_chambres = heb_type and 'chambre-hote' in heb_type
# collective.z3cform.datepicker DateTimePickerWidget extract a string but not a date
# Actual format date is in gites.core.widget.datepicker_input.pt
from_date = self.data.get('fromDateAvancee')\
or self.data.get('form.widgets.fromDate')\
and datetime.strptime(self.data.get('form.widgets.fromDate'),
'%d/%m/%Y').date()
to_date = self.data.get('toDateAvancee')\
or self.data.get('form.widgets.toDate')\
and datetime.strptime(self.data.get('form.widgets.toDate'),
'%d/%m/%Y').date()
smokers = self.data.get('form.widgets.smokers')
animals = self.data.get('form.widgets.animals')
roomAmount = self.data.get('form.widgets.roomAmount')
classifications = self.selected_classifications()
#11 = Animal
#12 = Fumeur
if animals:
subquery = session().query(LinkHebergementMetadata.heb_fk)
subquery = subquery.filter(LinkHebergementMetadata.metadata_fk == 11)
subquery = subquery.filter(LinkHebergementMetadata.link_met_value == True)
subquery = subquery.group_by(LinkHebergementMetadata.heb_fk)
subquery = subquery.subquery()
query = query.filter(Hebergement.heb_pk == subquery.c.heb_fk)
if smokers:
subquery = session().query(LinkHebergementMetadata.heb_fk)
subquery = subquery.filter(LinkHebergementMetadata.metadata_fk == 12)
subquery = subquery.filter(LinkHebergementMetadata.link_met_value == True)
subquery = subquery.group_by(LinkHebergementMetadata.heb_fk)
subquery = subquery.subquery()
query = query.filter(Hebergement.heb_pk == subquery.c.heb_fk)
if classifications and not ignore_classification:
query = query.filter(sa.and_(LinkHebergementEpis.heb_nombre_epis.in_(classifications),
Hebergement.heb_pk == LinkHebergementEpis.heb_pk))
if roomAmount:
query = query.filter(Hebergement.heb_cgt_nbre_chmbre >= roomAmount)
if reference:
reference = reference.strip()
query = query.filter(sa.or_(sa.func.unaccent(Hebergement.heb_nom).ilike("%%%s%%" % reference),
Hebergement.heb_nom.ilike("%%%s%%" % reference)))
if show_gites != show_chambres: # XOR
query = self.filter_heb_type(show_gites, show_chambres, query)
if capacity and not group:
query = self.filter_capacity(capacity, query)
if from_date or to_date:
# Don't search in the past
today = date.today()
if from_date and from_date < today:
from_date = today
if to_date and to_date < today:
to_date = today
query = self.filter_available_date(from_date, to_date, query)
query = query.filter(sa.and_(Hebergement.heb_site_public == '1',
Proprio.pro_etat == True))
return query
def getHebType(self, typeheb, language):
from gites.db import session
session = session()
typeHeb = session.query(TypeHebergement).filter_by(type_heb_pk=typeheb).first()
return typeHeb.getId(language)
def _query(self):
sess = session()
query = self._query_non_grouped_heb(sess)
query = query.options(
FromCache('gdw'))
return query.union(self._query_grouped_heb(sess))
def filters(self):
userCriteria = self.context.userCriteria
query = session().query(Metadata)
query = query.options(FromCache('gdw'))
query = query.filter(Metadata.met_pk.in_(userCriteria))
return query.all()
def connect(self):
initialize_declarative_mappers(DeclarativeBase, self.pg.metadata)
initialize_defered_mappers(self.pg.metadata)
self.session = session()
def _session(self):
return session()
