from sqlalchemy import sql from sanmarco models base import db from sa

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
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