SET @date_A = '2019-10-01';
SET @date_B = '2019-10-31';
SET @Category = ''; -- Для отчета по конкретной категории, вводить полное название одной категорий или часть названия в формате %фильтр%
SET @Categories = 'old'; -- all, new, old для отображения Всех, Новых или старых категорий.
SET @Motornoe_maslo = 'no';-- yes или no для отображения масел

SELECT Category, pa_id, sku, brand, mask, char_name, char_unit_name, char_value, ceil(avg(price_torg)) price, round(avg(out_price_for_1),2) avg_price,
count(distinct o_id) orders, sum(count) Qnt, round(sum(total_price),0) Revenue, round(sum(margin),0) Profit
FROM
(SELECT o.id o_id, op.id op_id, pa.id pa_id, ba.name_brands brand, ga.name_ru Category,
c.id char_id,
ct_name.text char_name,
ct_unit_name.text char_unit_name,
ct.text char_value,
pa.price_torg,
if(max(if(ch.cha_type="t",cht.text,NULL)) is null, pa.art_num,max(if(ch.cha_type="t",cht.text,NULL))) sku,
if(pm.mask_for_id is not null,pm.mask_for_id,CONCAT_WS(" ",ga.name_ru,ba.NAME_BRANDS,pa.art_num)) mask,
op.count,
op.out_price_for_1,
op.count*op.out_price_for_1 total_price,
IF(in_price_for_1 = 0, op.out_price_for_1*0.3*count, (op.out_price_for_1-op.in_price_for_1)*op.count) margin
FROM yii_doc.orders o
LEFT JOIN yii_doc.order_cars oc ON o.id=oc.order_id
LEFT JOIN yii_doc.order_product op ON oc.id=op.car_id
LEFT JOIN yii_doc.prices_all pa ON op.price_all_id=pa.id
LEFT JOIN yii_doc.brand_all ba ON pa.brand_id=ba.id
LEFT JOIN yii_doc.dok_ga ga ON pa.pa_ga_id=ga.GA_ID
LEFT JOIN yii_doc.dok_razdel dr on ga.parent_id=dr.id
LEFT JOIN yii_doc.dok_izdelie di on dr.parent_id=di.id
LEFT JOIN yii_doc.dok_podrubrica dp on di.parent_id=dp.id
LEFT JOIN yii_doc.dok_razdel_template drt ON dr.id = drt.razdel_id
LEFT JOIN yii_doc.char c ON drt.modifier = c.id
LEFT JOIN yii_doc.char_text ct_name ON c.cha_name_id = ct_name.id
LEFT JOIN yii_doc.char_text ct_unit_name ON c.cha_name_unit_id = ct_unit_name.id
LEFT JOIN yii_doc.char_art ca ON (pa.id=ca.ca_pa_id and drt.modifier=ca.ca_char_id)
LEFT JOIN yii_doc.char_text ct ON ca.ca_char_text_id=ct.id
LEFT JOIN yii_doc.pa_mask pm on pa.id=pm.id
left join yii_doc.char_art cha on pa.id=cha.ca_pa_id
LEFT JOIN yii_doc.char ch on cha.ca_char_id=ch.id
LEFT JOIN yii_doc.char_text cht on cha.ca_char_text_id=cht.id
where date(o.date_create) between @date_A AND @date_B
and o.order_status_id IN(5,14)
and o.domain_id IN(2,3,4)
and op.active=1
and dr.active=1
and if(@Categories LIKE "new",dp.parent_id=9,if(@Categories LIKE "old",dp.parent_id<>9,dp.parent_id is not null))
and if(@Motornoe_maslo LIKE "yes", dr.id is not null, dr.id not in(521,562))
and if(LENGTH(@Category)>1,ga.name_ru LIKE @Category, ga.name_ru is not null)
group by o_id, op_id, pa_id, ba.name_ru, Category
order by o_id, op_id, pa_id) a
group by Category, pa_id, sku, brand, char_name, char_unit_name
order by Category, brand, mask, char_value