from sqlalchemy import sql
from sanmarco.models.base import db
from sanmarco.models import goods
from sanmarco.models import catalog
from sanmarco.lib.mapping import get_idents, mapping_one_to_one, mapping_many_to_one
from sanmarco.models.catalog.files import FileNode
from sanmarco.models.ordering.orders import q_order_item_price
from sanmarco.queries.goods.statistics import q_products_statistic
def q_products_active():
q = q_products_all()
q = q.filter(goods.Product.active == True)
return q
def q_as_complect(q, complect_id, complect_type):
q = q.join(
goods.Complect, sql.and_(
goods.Complect.active==True,
goods.Complect.link_id == goods.Product.id,
goods.Complect.complect_type == complect_type,
goods.Complect.from_id == complect_id
))
return q
def q_as_complect_free(q, complect_id, complect_type):
q = q.outerjoin(
goods.Complect, sql.and_(
goods.Complect.link_id == goods.Product.id,
goods.Complect.complect_type == complect_type,
goods.Complect.from_id == complect_id
))
q = q.filter(goods.Complect.id == None)
return q
def q_products_all():
subq = q_products_statistic().subquery()
q = db.query(
goods.Product,
goods.Brand,
goods.Kind,
goods.Country,
catalog.Category,
goods.Material,
subq.c.no_images,
subq.c.has_images,
subq.c.no_options,
subq.c.has_options,
subq.c.no_prices,
subq.c.has_prices
)
q = q.outerjoin(subq, subq.c.pid == goods.Product.id)
q = q.join(goods.Brand, goods.Brand.id == goods.Product.brand_id)
q = q.join(goods.Kind, goods.Kind.id == goods.Product.kind_id)
q = q.join(goods.Country, goods.Country.id == goods.Product.country_id)
q = q.join(catalog.Category, catalog.Category.id == goods.Product.category_id)
q = q.join(goods.Material, goods.Material.id == goods.Product.material_id)
q.subqueries = [subq]
return q
def q_products_covers(products_list, as_ids=False):
if as_ids:
idents = products_list
else:
idents = get_idents(products_list)
q = db.query(FileNode, goods.VariantImage.product_id)
q = q.join(goods.VariantImage, goods.VariantImage.file_id == FileNode.id)
q = q.filter(sql.and_(
goods.VariantImage.product_id.in_(idents),
goods.VariantImage.active == True
))
return q
def q_products_variants(products_list, as_ids):
subq = q_order_item_price().subquery()
if as_ids:
idents = products_list
else:
idents = get_idents(products_list)
q = db.query(goods.Variant)
q = q.join(subq, subq.c.vid == goods.Variant.id)
q = q.filter(goods.Variant.product_id.in_(idents))
q = q.order_by(sql.asc(goods.Variant.id))
return q
def q_variants_images(variants_list, as_ids=False):
if as_ids:
idents = variants_list
else:
idents = get_idents(variants_list)
q = db.query(FileNode, goods.VariantImage.variant_id)
q = q.join(goods.VariantImage, goods.VariantImage.file_id == FileNode.id)
q = q.filter(sql.and_(
goods.VariantImage.variant_id.in_(idents)
))
q.order_by(sql.desc(goods.VariantImage.ordering))
return q
def q_variant_images(products_list, as_ids):
if as_ids:
idents = products_list
else:
idents = get_idents(products_list)
q = db.query(FileNode, goods.VariantImage.variant_id)
q = q.join(goods.VariantImage, goods.VariantImage.file_id == FileNode.id)
q = q.filter(sql.and_(
goods.VariantImage.product_id.in_(idents)
))
q.order_by(sql.desc(goods.VariantImage.ordering))
return q
def q_product_variants(product_ids):
imq = db.query(goods.VariantImage.variant_id.label('vid'), sql.func.count(goods.VariantImage.id).label('cnt'))
imq = imq.filter(goods.VariantImage.product_id.in_(product_ids))
imq = imq.group_by(goods.VariantImage.variant_id)
imq = imq.subquery()
q = db.query(goods.Variant, goods.Colour, goods.Material)
q = q.join(goods.Colour, goods.Colour.id == goods.Variant.colour_id)
q = q.join(goods.Material, goods.Material.id == goods.Variant.material_id)
if len(product_ids) > 1:
q = q.filter(goods.Variant.product_id.in_(product_ids))
elif len(product_ids) == 1:
q = q.filter(goods.Variant.product_id == product_ids[0])
q = q.join(imq, imq.c.vid == goods.Variant.id)
q = q.filter(sql.and_(
imq.c.cnt > 0
))
return q
def q_variants_detail_process(product_id):
q = q_product_variants([product_id])
variants = q.all()
variants = mapping_one_to_one(variants, ['colour', 'material'])
ind_vars = get_idents(variants, as_hash=True)
q = db.query(FileNode, goods.VariantImage.variant_id)
q = q.join(goods.VariantImage, goods.VariantImage.file_id == FileNode.id)
q = q.filter(goods.VariantImage.variant_id.in_(ind_vars.keys()))
result = mapping_one_to_one(q.all(), ['variant_id'])
mapping_many_to_one(ind_vars, result, 'variant_id', 'images')
q = q_order_item_price().filter(goods.VariantPrice.product_id == product_id)
for variant_id, price in q.all():
if not variant_id in ind_vars: continue
ind_vars[variant_id].price = price
return variants