SHARE
TWEET

Untitled

a guest Nov 20th, 2019 104 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET @date_A = '2019-10-01';
  2. SET @date_B = '2019-10-31';
  3. SET @Category = ''; -- Для отчета по конкретной категории, вводить полное название одной категорий или часть названия в формате %фильтр%
  4. SET @Categories = 'old'; -- all, new, old для отображения Всех, Новых или старых категорий.
  5. SET @Motornoe_maslo = 'no';-- yes или no для отображения масел
  6.  
  7. 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,
  8. count(distinct o_id) orders, sum(count) Qnt, round(sum(total_price),0) Revenue, round(sum(margin),0) Profit
  9. FROM
  10. (SELECT o.id o_id, op.id op_id, pa.id pa_id, ba.name_brands brand, ga.name_ru Category,
  11. c.id char_id,
  12. ct_name.text char_name,
  13. ct_unit_name.text char_unit_name,
  14. ct.text char_value,
  15. pa.price_torg,
  16. 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,
  17. if(pm.mask_for_id is not null,pm.mask_for_id,CONCAT_WS(" ",ga.name_ru,ba.NAME_BRANDS,pa.art_num)) mask,
  18. op.count,
  19. op.out_price_for_1,
  20. op.count*op.out_price_for_1 total_price,
  21. 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
  22. FROM yii_doc.orders o
  23. LEFT JOIN yii_doc.order_cars oc ON o.id=oc.order_id
  24. LEFT JOIN yii_doc.order_product op ON oc.id=op.car_id
  25. LEFT JOIN yii_doc.prices_all pa ON op.price_all_id=pa.id
  26. LEFT JOIN yii_doc.brand_all ba ON pa.brand_id=ba.id
  27. LEFT JOIN yii_doc.dok_ga ga ON pa.pa_ga_id=ga.GA_ID
  28. LEFT JOIN yii_doc.dok_razdel dr on ga.parent_id=dr.id
  29. LEFT JOIN yii_doc.dok_izdelie di on dr.parent_id=di.id
  30. LEFT JOIN yii_doc.dok_podrubrica dp on di.parent_id=dp.id
  31. LEFT JOIN yii_doc.dok_razdel_template drt ON dr.id = drt.razdel_id
  32. LEFT JOIN yii_doc.char c ON drt.modifier = c.id
  33. LEFT JOIN yii_doc.char_text ct_name ON c.cha_name_id = ct_name.id
  34. LEFT JOIN yii_doc.char_text ct_unit_name ON c.cha_name_unit_id = ct_unit_name.id
  35. LEFT JOIN yii_doc.char_art ca ON (pa.id=ca.ca_pa_id and drt.modifier=ca.ca_char_id)
  36. LEFT JOIN yii_doc.char_text ct ON ca.ca_char_text_id=ct.id
  37. LEFT JOIN yii_doc.pa_mask pm on pa.id=pm.id
  38. left join yii_doc.char_art cha on pa.id=cha.ca_pa_id
  39. LEFT JOIN yii_doc.char ch on cha.ca_char_id=ch.id
  40. LEFT JOIN yii_doc.char_text cht on cha.ca_char_text_id=cht.id
  41. where date(o.date_create) between @date_A AND @date_B
  42. and o.order_status_id IN(5,14)
  43. and o.domain_id IN(2,3,4)
  44. and op.active=1
  45. and dr.active=1
  46. and if(@Categories LIKE "new",dp.parent_id=9,if(@Categories LIKE "old",dp.parent_id<>9,dp.parent_id is not null))
  47. and if(@Motornoe_maslo LIKE "yes", dr.id is not null, dr.id not in(521,562))
  48. and if(LENGTH(@Category)>1,ga.name_ru LIKE @Category, ga.name_ru is not null)
  49. group by o_id, op_id, pa_id, ba.name_ru, Category
  50. order by o_id, op_id, pa_id) a
  51. group by Category, pa_id, sku, brand, char_name, char_unit_name
  52. order by Category, brand, mask, char_value
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top