def get_product_by_product_def_id(self, product_def_id):
return db.session.query(
TagOfert.tag_product_def_id.label('product_id'),
Ofert.id.label('ofert_id'), Ofert.url, Ofert.title, Image.image,
Ofert.price, Ofert.currency, Image.control_sum,
Brand.name.label('brand_name'),
func.string_agg(Tag.value, ';').label('tags')).join(
Ofert, Ofert.id == TagOfert.ofert_id
).join(Image, Image.image == Ofert.image).join(
TagProductDef,
TagProductDef.id == TagOfert.tag_product_def_id,
).join(TagProduct,
TagProduct.tag_product_def_id == TagProductDef.id).join(
Tag, Tag.id == TagProduct.tag_id, isouter=True).join(
Brand,
Brand.id == TagProductDef.brand_id,
isouter=True).filter(
and_(
TagOfert.tag_product_def_id ==
product_def_id,
TagOfert.creation_date.cast(Date) ==
func.current_date())).group_by(
TagOfert.tag_product_def_id, Ofert.id,
Ofert.url, Ofert.title, Image.image,
Ofert.price, Ofert.currency,
Image.control_sum, Brand.name).order_by(
asc(Ofert.price)).all()
def get_product_for_catgeory_view(self, category_id_):
log.info('\n\n Jestem tutaj, szukam dla Category ID: %r', category_id_)
product = db.session.query(
TagProduct.tag_product_def_id.label('product_id'),
func.string_agg(Tag.value, ' ').label('title')).join(
TagProduct, TagProduct.tag_id == Tag.id).group_by(
TagProduct.tag_product_def_id).cte('product')
max_date = db.session.query(
func.max(Ofert.creation_date.cast(Date)).cast(Date))
return db.session.query(
product.c.product_id, product.c.title,
func.max(Ofert.price).label('max_price'),
func.min(Ofert.price).label('min_price'),
func.count(Ofert.id).label('count'),
func.max(Image.image).label('image'),
Brand.name.label('brand')).join(
TagOfert, TagOfert.tag_product_def_id == product.c.product_id
).join(Ofert, Ofert.id == TagOfert.ofert_id).join(
Image, Image.image == Ofert.image).join(
TagProductDef,
and_(TagProductDef.id == TagOfert.tag_product_def_id,
TagProductDef.category_id == category_id_)).join(
Brand, Brand.id == TagProductDef.brand_id).filter(
and_(
TagOfert.creation_date.cast(Date) ==
max_date, # func.current_date(),
Ofert.creation_date.cast(Date) ==
max_date # func.current_date()
)).group_by(product.c.product_id,
product.c.title,
Brand.name).order_by(
desc(func.count(Ofert.id)))
def _build_sip_pickup_query(session):
pickup_members = (
session.query(
PickupMember.category,
PickupMember.pickupid.label('pickup_id'),
UserSIP.id.label('sip_id')
)
.join(UserFeatures,
and_(PickupMember.membertype == 'user',
PickupMember.memberid == UserFeatures.id))
.join(UserLine,
UserLine.user_id == UserFeatures.id)
.join(LineFeatures,
UserLine.line_id == LineFeatures.id)
.join(UserSIP,
and_(LineFeatures.protocol == 'sip',
LineFeatures.protocolid == UserSIP.id))
).cte(name="pickup_members")
pickup_groups = (
session.query(
pickup_members.c.sip_id,
func.string_agg(
cast(pickup_members.c.pickup_id, String),
','
).label('pickup_ids')
)
.filter(pickup_members.c.category == 'member')
.group_by(pickup_members.c.sip_id)
).cte(name="pickup_groups")
call_groups = (
session.query(
pickup_members.c.sip_id,
func.string_agg(
cast(pickup_members.c.pickup_id, String),
','
).label('pickup_ids')
)
.filter(pickup_members.c.category == 'pickup')
.group_by(pickup_members.c.sip_id)
).cte(name="pickup_category")
return pickup_groups, call_groups
def get_product_by_tag(self, tag):
products = db.session.query(
Tag.id, TagProductDef.id.label('product_id'),
Ofert.id.label('ofert_id'), Ofert.title, Ofert.url, Ofert.price,
Ofert.currency, Image.image, Image.control_sum,
func.coalesce(Ofert.manufacturer, '').label('brand_name'),
Category.name.label('category'), Tag.value.label('tags')).join(
TagProduct, TagProduct.tag_id == Tag.id).join(
TagProductDef,
TagProductDef.id == TagProduct.tag_product_def_id).join(
TagOfert, TagOfert.tag_product_def_id ==
TagProduct.tag_product_def_id).join(
Ofert, Ofert.id == TagOfert.ofert_id).join(
Image, Image.image == Ofert.image).join(
Category,
Category.id == TagProductDef.category_id,
isouter=True).filter(
and_(
Tag.value == tag,
Ofert.creation_date.cast(Date) ==
func.current_date())).order_by(
Ofert.price.asc()).cte(
'products')
subcategory = db.bindparam('str_tworzacy', '').label('subcategory')
colortags = db.bindparam('str_tworzacy', '').label('colortags')
return db.session.query(
products.c.product_id, products.c.ofert_id, products.c.title,
products.c.url, products.c.price, products.c.currency,
products.c.image, products.c.control_sum, products.c.brand_name,
products.c.category, products.c.tags,
func.string_agg(Tag.value, ';').label('all_tags'), subcategory,
colortags).join(
TagProduct,
TagProduct.tag_product_def_id == products.c.product_id,
isouter=True).join(Tag,
Tag.id == TagProduct.tag_id,
isouter=True).group_by(
products.c.product_id,
products.c.ofert_id,
products.c.title,
products.c.url,
products.c.price,
products.c.currency,
products.c.image,
products.c.control_sum,
products.c.brand_name,
products.c.category,
products.c.tags,
).order_by(products.c.price.asc()).all()
def get_zprospect():
"""
Retourne toutes les zones de prospection du module
"""
id_type_commune = blueprint.config["id_type_commune"]
parameters = request.args
q = (DB.session.query(TZprospect, Taxref,
func.string_agg(LAreas.area_name, ", ")).outerjoin(
Taxref,
TZprospect.cd_nom == Taxref.cd_nom).outerjoin(
CorZpArea,
CorZpArea.indexzp == TZprospect.indexzp).
outerjoin(CorZpObs, CorZpObs.indexzp == TZprospect.indexzp).outerjoin(
User, User.id_role == CorZpObs.id_role).outerjoin(
BibOrganismes,
BibOrganismes.id_organisme == User.id_organisme).outerjoin(
LAreas,
and_(LAreas.id_area == CorZpArea.id_area,
LAreas.id_type == id_type_commune),
).group_by(TZprospect, Taxref))
if "indexzp" in parameters:
q = q.filter(TZprospect.indexzp == parameters["indexzp"])
if "cd_nom" in parameters:
q = q.filter(Taxref.cd_nom == parameters["cd_nom"])
if "commune" in parameters:
q = q.filter(LAreas.area_name == parameters["commune"])
if "organisme" in parameters:
q = q.filter(BibOrganismes.nom_organisme == parameters["organisme"])
if "year" in parameters:
q = q.filter(
func.date_part("year", TZprospect.date_min) == parameters["year"])
data = q.all()
features = []
for d in data:
feature = d[0].get_geofeature(
recursif=False,
columns=["indexzp", "date_min", "date_max", "cd_nom"])
id_zp = feature["properties"]["indexzp"]
feature["properties"]["taxon"] = d[1].as_dict(["nom_valide"])
features.append(feature)
return FeatureCollection(features)
def get_all_transects():
'''
Retourne tous les transects
'''
parameters = request.args
q = (
DB.session.query(
TTransect, func.max(TBaseVisits.visit_date_min),
HabrefSHS.lb_hab_fr_complet,
func.count(distinct(TBaseVisits.id_base_visit)),
func.string_agg(distinct(
BibOrganismes.nom_organisme), ', ')).outerjoin(
TBaseVisits,
TBaseVisits.id_base_site == TTransect.id_base_site)
# get habitat
.outerjoin(HabrefSHS, TTransect.cd_hab == HabrefSHS.cd_hab)
# get organisme
.outerjoin(
corVisitObserver, corVisitObserver.c.id_base_visit ==
TBaseVisits.id_base_visit).outerjoin(
User, User.id_role == corVisitObserver.c.id_role).outerjoin(
BibOrganismes,
BibOrganismes.id_organisme == User.id_organisme).group_by(
TTransect, HabrefSHS.lb_hab_fr_complet))
if 'filterHab' in parameters:
q = q.filter(TTransect.cd_hab == parameters['filterHab'])
if ('date_low' in parameters) and ('date_up' in parameters):
q_date = (DB.session.query(
TTransect.id_base_site,
func.max(TBaseVisits.visit_date_min),
).outerjoin(
TBaseVisits,
TBaseVisits.id_base_site == TTransect.id_base_site).group_by(
TTransect.id_base_site).all())
q = q.filter(
and_(TBaseVisits.visit_date_min <= parameters['date_up'],
TBaseVisits.visit_date_min >= parameters['date_low']))
page = request.args.get('page', 1, type=int)
items_per_page = blueprint.config['items_per_page']
pagination_serverside = blueprint.config['pagination_serverside']
pagination = q.paginate(page, items_per_page, False)
totalItmes = pagination.total
if (pagination_serverside):
data = pagination.items
else:
data = q.all()
pageInfo = {
'totalItmes': totalItmes,
'items_per_page': items_per_page,
}
features = []
if data:
for d in data:
feature = d[0].get_geofeature(True)
id_site = feature['properties']['id_base_site']
base_site_code = feature['properties']['t_base_site'][
'base_site_code']
base_site_description = feature['properties']['t_base_site'][
'base_site_description'] or 'Aucune description'
base_site_name = feature['properties']['t_base_site'][
'base_site_name']
if feature['properties']['t_base_site']:
del feature['properties']['t_base_site']
if 'year' in parameters:
for dy in q_date:
# récupérer la bonne date max du site si on filtre sur année
if id_site == dy[0]:
feature['properties']['date_max'] = str(d[1])
else:
feature['properties']['date_max'] = str(d[1])
if d[1] == None:
feature['properties']['date_max'] = 'Aucune visite'
feature['properties']['nom_habitat'] = str(d[2])
feature['properties']['nb_visit'] = str(d[3])
if d[4] == None:
feature['properties']['organisme'] = 'Aucun'
feature['properties']['organisme'] = 'Aucun'
feature['properties']['base_site_code'] = base_site_code
feature['properties'][
'base_site_description'] = base_site_description
feature['properties']['base_site_name'] = base_site_name
features.append(feature)
return [pageInfo, FeatureCollection(features)]
return None
def get_transect(id_site):
'''
Retourne un transect à l'aide de son id_site
'''
id_type_commune = blueprint.config['id_type_commune']
data = DB.session.query(
TTransect, TNomenclatures,
func.string_agg(distinct(LAreas.area_name), ', '),
func.string_agg(distinct(BibOrganismes.nom_organisme), ', '),
HabrefSHS.lb_hab_fr_complet).filter_by(id_base_site=id_site).outerjoin(
TBaseVisits,
TBaseVisits.id_base_site == TTransect.id_base_site).outerjoin(
TNomenclatures, TTransect.id_nomenclature_plot_position ==
TNomenclatures.id_nomenclature
# get habitat
).outerjoin(
HabrefSHS, TTransect.cd_hab == HabrefSHS.cd_hab
# get organisme
).outerjoin(
corVisitObserver, corVisitObserver.c.id_base_visit ==
TBaseVisits.id_base_visit).outerjoin(
User,
User.id_role == corVisitObserver.c.id_role).outerjoin(
BibOrganismes,
BibOrganismes.id_organisme == User.id_organisme
# get municipalities of a site
).outerjoin(
corSiteArea, corSiteArea.c.id_base_site ==
TTransect.id_base_site).outerjoin(
LAreas,
and_(LAreas.id_area == corSiteArea.c.id_area,
LAreas.id_type == id_type_commune)).group_by(
TTransect.id_transect,
TNomenclatures.id_nomenclature,
HabrefSHS.lb_hab_fr_complet).first()
if data:
transect = data[0].get_geofeature(True)
plot_position = data[1].as_dict()
transect['properties']['plot_position'] = plot_position
if data[2]:
transect['properties']['nom_commune'] = str(data[2])
if data[3]:
transect['properties']['observers'] = str(data[3])
if data[4]:
transect['properties']['nom_habitat'] = str(data[4])
base_site_code = transect['properties']['t_base_site'][
'base_site_code']
base_site_description = transect['properties']['t_base_site'][
'base_site_description'] or 'Aucune description'
base_site_name = transect['properties']['t_base_site'][
'base_site_name']
if transect['properties']['t_base_site']:
del transect['properties']['t_base_site']
transect['properties']['base_site_code'] = base_site_code
transect['properties']['base_site_description'] = base_site_description
transect['properties']['base_site_name'] = base_site_name
return transect
return None
def get_sites_zp():
'''
Retourne la liste des ZP
'''
parameters = request.args
id_type_commune = blueprint.config['id_type_commune']
# grâce au fichier config
q = (
DB.session.query(
TInfoSite, func.max(TBaseVisits.visit_date_min),
Taxonomie.nom_complet,
func.count(distinct(TBaseVisits.id_base_visit)),
func.string_agg(distinct(BibOrganismes.nom_organisme), ', '),
func.string_agg(LAreas.area_name, ', ')).outerjoin(
TBaseVisits, TBaseVisits.id_base_site == TInfoSite.id_base_site
# get taxonomy lb_nom
).outerjoin(
Taxonomie, TInfoSite.cd_nom == Taxonomie.cd_nom
# get organisms of a site
).outerjoin(
corVisitObserver, corVisitObserver.c.id_base_visit ==
TBaseVisits.id_base_visit).outerjoin(
User,
User.id_role == corVisitObserver.c.id_role).outerjoin(
BibOrganismes,
BibOrganismes.id_organisme == User.id_organisme)
# get municipalities of a site
.outerjoin(
corSiteArea,
corSiteArea.c.id_base_site == TInfoSite.id_base_site).outerjoin(
LAreas,
and_(LAreas.id_area == corSiteArea.c.id_area,
LAreas.id_type == id_type_commune)).group_by(
TInfoSite, Taxonomie.nom_complet))
if 'id_base_site' in parameters:
q = q.filter(TInfoSite.id_base_site == parameters['id_base_site'])
if 'cd_nom' in parameters:
q = q.filter(TInfoSite.cd_nom == parameters['cd_nom'])
if 'organisme' in parameters:
q = q.filter(BibOrganismes.nom_organisme == parameters['organisme'])
if 'commune' in parameters:
q = q.filter(LAreas.area_name == parameters['commune'])
if 'year' in parameters:
# relance la requête pour récupérer la date_max exacte si on filtre sur l'année
q_year = (DB.session.query(
TInfoSite.id_base_site,
func.max(TBaseVisits.visit_date_min),
).outerjoin(
TBaseVisits,
TBaseVisits.id_base_site == TInfoSite.id_base_site).group_by(
TInfoSite.id_base_site))
data_year = q_year.all()
q = q.filter(
func.date_part('year', TBaseVisits.visit_date_min) ==
parameters['year'])
data = q.all()
features = []
for d in data:
feature = d[0].get_geofeature()
id_site = feature['properties']['base_site']['id_base_site']
if 'year' in parameters:
for dy in data_year:
# récupérer la bonne date max du site si on filtre sur année
if id_site == dy[0]:
feature['properties']['date_max'] = str(dy[1])
else:
feature['properties']['date_max'] = str(d[1])
if d[1] == None:
feature['properties']['date_max'] = 'Aucune visite'
feature['properties']['nom_taxon'] = str(d[2])
feature['properties']['nb_visit'] = str(d[3])
feature['properties']['organisme'] = str(d[4])
feature['properties']['nom_commune'] = str(d[5])
if d[4] == None:
feature['properties']['organisme'] = 'Aucun'
features.append(feature)
return FeatureCollection(features)
def get_all_sites(info_role):
'''
Retourne tous les sites
'''
parameters = request.args
id_type_commune = blueprint.config['id_type_commune']
q = (
DB.session.query(
TInfosSite,
func.max(TBaseVisits.visit_date_min),
Habref.lb_hab_fr_complet,
func.count(distinct(TBaseVisits.id_base_visit)),
func.string_agg(distinct(BibOrganismes.nom_organisme), ', '),
func.string_agg(distinct(LAreas.area_name), ', ')
).outerjoin(
TBaseVisits, TBaseVisits.id_base_site == TInfosSite.id_base_site
# get habitat cd_hab
).outerjoin(
Habref, TInfosSite.cd_hab == Habref.cd_hab
# get organisms of a site
).outerjoin(
corVisitObserver, corVisitObserver.c.id_base_visit == TBaseVisits.id_base_visit
).outerjoin(
User, User.id_role == corVisitObserver.c.id_role
).outerjoin(
BibOrganismes, BibOrganismes.id_organisme == User.id_organisme
)
# get municipalities of a site
.outerjoin(
corSiteArea, corSiteArea.c.id_base_site == TInfosSite.id_base_site
).outerjoin(
LAreas, and_(LAreas.id_area == corSiteArea.c.id_area, LAreas.id_type == id_type_commune)
)
.group_by(
TInfosSite, Habref.lb_hab_fr_complet
)
)
if 'cd_hab' in parameters:
q = q.filter(TInfosSite.cd_hab == parameters['cd_hab'])
if 'id_base_site' in parameters:
q = q.filter(TInfosSite.id_base_site == parameters['id_base_site'])
if 'organisme' in parameters:
q = q.filter(BibOrganismes.id_organisme == parameters['organisme'])
if 'commune' in parameters:
q = q.filter(LAreas.area_name == parameters['commune'])
if 'year' in parameters:
# relance la requête pour récupérer la date_max exacte si on filtre sur l'année
q_year = (
DB.session.query(
TInfosSite.id_base_site,
func.max(TBaseVisits.visit_date_min),
).outerjoin(
TBaseVisits, TBaseVisits.id_base_site == TInfosSite.id_base_site
).group_by(TInfosSite.id_base_site)
)
data_year = q_year.all()
q = q.filter(func.date_part('year', TBaseVisits.visit_date_min) == parameters['year'])
page = request.args.get('page', 1, type=int)
items_per_page = blueprint.config['items_per_page']
pagination_serverside = blueprint.config['pagination_serverside']
if (pagination_serverside):
pagination = q.paginate(page, items_per_page, False)
data = pagination.items
totalItmes = pagination.total
else:
totalItmes = 0
data = q.all()
pageInfo= {
'totalItmes' : totalItmes,
'items_per_page' : items_per_page,
}
features = []
if data:
for d in data:
feature = d[0].get_geofeature()
id_site = feature['properties']['id_base_site']
base_site_code = feature['properties']['t_base_site']['base_site_code']
base_site_description = feature['properties']['t_base_site']['base_site_description'] or 'Aucune description'
base_site_name = feature['properties']['t_base_site']['base_site_name']
if feature['properties']['t_base_site']:
del feature['properties']['t_base_site']
if 'year' in parameters:
for dy in data_year:
# récupérer la bonne date max du site si on filtre sur année
if id_site == dy[0]:
feature['properties']['date_max'] = str(d[1])
else:
feature['properties']['date_max'] = str(d[1])
if d[1] == None:
feature['properties']['date_max'] = 'Aucune visite'
feature['properties']['nom_habitat'] = str(d[2])
feature['properties']['nb_visit'] = str(d[3])
feature['properties']['organisme'] = str(d[4])
feature['properties']['nom_commune'] = str(d[5])
if d[4] == None:
feature['properties']['organisme'] = 'Aucun'
feature['properties']['base_site_code'] = base_site_code
feature['properties']['base_site_description'] = base_site_description
feature['properties']['base_site_name'] = base_site_name
features.append(feature)
return [pageInfo,FeatureCollection(features)]
return None