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 для отображения масел
  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, op_id, pa_id, ba.name_brands brand, ga.name_ru Category,
  11. 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
  24. LEFT JOIN yii_doc.order_product op ON
  25. LEFT JOIN yii_doc.prices_all pa ON
  26. LEFT JOIN yii_doc.brand_all ba ON
  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
  29. LEFT JOIN yii_doc.dok_izdelie di on
  30. LEFT JOIN yii_doc.dok_podrubrica dp on
  31. LEFT JOIN yii_doc.dok_razdel_template drt ON = drt.razdel_id
  32. LEFT JOIN yii_doc.char c ON drt.modifier =
  33. LEFT JOIN yii_doc.char_text ct_name ON c.cha_name_id =
  34. LEFT JOIN yii_doc.char_text ct_unit_name ON c.cha_name_unit_id =
  35. LEFT JOIN yii_doc.char_art ca ON ( and drt.modifier=ca.ca_char_id)
  36. LEFT JOIN yii_doc.char_text ct ON
  37. LEFT JOIN yii_doc.pa_mask pm on
  38. left join yii_doc.char_art cha on
  39. LEFT JOIN yii_doc.char ch on
  40. LEFT JOIN yii_doc.char_text cht on
  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
  45. and
  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", is not null, 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