select distinct t3.id, t3.item_title, t3.item_number_prod, t3.item_img, t5.website_title, t6.website_subcat_title,
t4.website_catid, t4.website_subcatid, t4.website_subsubcatid, t4.website_item_id, t3.item_order,
t2.item_retail_netto, t2.item_retail_taxes, t2.item_retail_brutto,
t2.item_mayor_netto, t2.item_mayor_taxes, t2.item_mayor_brutto,
t2.item_retail_netto_discount, t2.item_retail_taxes_discount, t2.item_retail_brutto_discount,
t2.item_mayor_netto_discount, t2.item_mayor_taxes_discount, t2.item_mayor_brutto_discount,
t4.website_online_new1, t4.website_online_offer, '' AS 'color_name', '0' AS 'color_id',
t21.dsc_perc 'cat_dsc_perc', t22.dsc_perc 'subcat_dsc_perc',
(
select count(*)
from v2_item_matrix tmc
where
tmc.item_baseid = t2.item_id and
tmc.item_act = 1
) AS 'childitems',
t3.item_week_sellcount
from v2_pricelists tlx
INNER JOIN v2_pricelists_items t2 ON tlx.id = t2.lst_id
INNER JOIN item t3 ON t2.item_id = t3.id INNER JOIN website_item t4 ON t4.website_catid IN(37) and t2.item_id = t4.website_item_id
INNER JOIN website_productcats t5 ON t4.website_catid = t5.id
INNER JOIN website_subcategories t6 ON t4.website_subcatid = t6.id INNER JOIN item_productcats t20 ON t3.id = t20.item_id
LEFT OUTER JOIN v2_pricelists_onlinecats_discounts t21 ON t21.lst_id = tlx.id and t21.cat_id = t4.website_catid and t21.cat_type = 'cat' and t21.funcname = 'lst_func_daglamperu_act'
LEFT OUTER JOIN v2_pricelists_onlinecats_discounts t22 ON t22.lst_id = tlx.id and t22.cat_id = t4.website_subcatid and t22.cat_type = 'subcat' and t22.funcname = 'lst_func_daglamperu_act' where
tlx.id = 3 and
tlx.lst_status > 0 and t4.website_status = 1 and
t3.item_status > 0 and
t3.item_parent_id = 0 and
t3.item_v2_variants_colors_act = 0 and
(
select count(t3excl.lst_id)
from v2_pricelists_exclude_cats t3excl
where
t3excl.funcname = 'lst_func_daglamperu_act' and
t3excl.catid = t20.cat_id
) = 0 and
(
select count(t4excl.lst_id)
from v2_pricelists_exclude_onlinecats t4excl
where
t4excl.lst_id = tlx.id and
t4excl.catid = t4.website_catid and
t4excl.subcatid = t4.website_subcatid and
t4excl.funcname = 'lst_func_daglamperu_act'
) = 0
and tlx.lst_func_daglamperu_act = 1 and
(
(
select SUM(txsth.iss_inventory)
from item_shops_storehouses txsth
where
t3.item_v2_variants_sizes_act = 0 and
t3.item_v2_variants_colors_act = 0 and
txsth.item_id = t2.item_id and
txsth.shop_id = tlx.lst_func_daglamperu_shopstockid and
txsth.st_id IN (139,139) and
txsth.iss_inventory >= 2 ) >= 2 or
(
select SUM(txsth2.iss_inventory)
from item txi
INNER JOIN v2_item_matrix txsth1 ON txsth1.item_baseid = txi.item_parent_id and txsth1.item_id = txi.id and txsth1.item_act = 1
INNER JOIN item_shops_storehouses txsth2 ON txi.id = txsth2.item_id
where
txi.item_parent_id = t3.id and
txi.item_status > 0 and
txsth2.shop_id = tlx.lst_func_daglamperu_shopstockid and
txsth2.st_id IN (139,139) and
txsth2.iss_inventory >= 2 ) >= 2
) and t4.website_catid IN(37) and t4.website_subcatid IN(65) and t4.website_subsubcatid IN(141) UNION ALL
select distinct t3.id, t3.item_title, t3.item_number_prod, t10.item_img, t5.website_title, t6.website_subcat_title,
t4.website_catid, t4.website_subcatid, t4.website_subsubcatid, t4.website_item_id, t3.item_order,
t2.item_retail_netto, t2.item_retail_taxes, t2.item_retail_brutto,
t2.item_mayor_netto, t2.item_mayor_taxes, t2.item_mayor_brutto,
t2.item_retail_netto_discount, t2.item_retail_taxes_discount, t2.item_retail_brutto_discount,
t2.item_mayor_netto_discount, t2.item_mayor_taxes_discount, t2.item_mayor_brutto_discount,
t4.website_online_new1, t4.website_online_offer, t8.color_name, t8.id AS 'color_id',
t21.dsc_perc 'cat_dsc_perc', t22.dsc_perc 'subcat_dsc_perc',
(
select count(*)
from v2_item_matrix tmc
where
tmc.item_baseid = t2.item_id and
tmc.item_act = 1
) AS 'childitems',
t3.item_week_sellcount
from v2_pricelists tlx
INNER JOIN v2_pricelists_items t2 ON tlx.id = t2.lst_id
INNER JOIN item t3 ON t2.item_id = t3.id INNER JOIN website_item t4 ON t4.website_catid IN(37) and t2.item_id = t4.website_item_id
INNER JOIN website_productcats t5 ON t4.website_catid = t5.id
INNER JOIN website_subcategories t6 ON t4.website_subcatid = t6.id INNER JOIN item t7 ON t3.id = t7.item_parent_id
INNER JOIN v2_item_colors t8 ON t7.item_v2_variants_colorid = t8.id
INNER JOIN v2_item_matrix t9 ON t9.item_baseid = t3.id and t9.item_id = t7.id and t9.item_act = 1
INNER JOIN v2_item_matrix_colors t10 ON t10.item_baseid = t3.id and t10.item_colorid = t7.item_v2_variants_colorid
INNER JOIN item_productcats t20 ON t3.id = t20.item_id
LEFT OUTER JOIN v2_pricelists_onlinecats_discounts t21 ON t21.lst_id = tlx.id and t21.cat_id = t4.website_catid and t21.cat_type = 'cat' and t21.funcname = 'lst_func_daglamperu_act'
LEFT OUTER JOIN v2_pricelists_onlinecats_discounts t22 ON t22.lst_id = tlx.id and t22.cat_id = t4.website_subcatid and t22.cat_type = 'subcat' and t22.funcname = 'lst_func_daglamperu_act' where
tlx.id = 3 and
tlx.lst_status > 0 and t4.website_status = 1 and
t3.item_status > 0 and
t3.item_parent_id = 0 and
t3.item_v2_variants_colors_act = 1 and
t7.item_v2_isvariant_act = 1 and
t7.item_v2_variants_colorid > 0 and
(
select count(t3excl.lst_id)
from v2_pricelists_exclude_cats t3excl
where
t3excl.funcname = 'lst_func_daglamperu_act' and
t3excl.catid = t20.cat_id
) = 0 and
(
select count(t4excl.lst_id)
from v2_pricelists_exclude_onlinecats t4excl
where
t4excl.lst_id = tlx.id and
t4excl.catid = t4.website_catid and
t4excl.subcatid = t4.website_subcatid and
t4excl.funcname = 'lst_func_daglamperu_act'
) = 0
and tlx.lst_func_daglamperu_act = 1 and t4.website_catid IN(37) and t4.website_subcatid IN(65) and t4.website_subsubcatid IN(141) and
(
select SUM(txsth.iss_inventory)
from item_shops_storehouses txsth
where
txsth.item_id = t7.id and
txsth.shop_id = tlx.lst_func_daglamperu_shopstockid and
txsth.st_id IN (139,139) and
txsth.iss_inventory >= 2 ) >= 2 order by 11, 2 LIMIT 0,21