API tools faq
paste
Advertisement
akozhomka

Untitled

akozhomka
Jul 23rd, 2021
79
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 23.20 KB | None | 0 0
raw download clone embed print report
  1. SELECT DISTINCT
  2.     (@sort_row:=@sort_row + 1) AS sort_row
  3.     , (@row_number:=@row_number + 1) AS product_position
  4.     , pa.id AS product_id
  5.     , IF(
  6.         pa.pa_ga_id = 854
  7.         , IFNULL(
  8.             (
  9.                 SELECT CONCAT(UPPER(LEFT(ct.text, 1)), SUBSTRING(ct.text, 2))
  10.                 FROM yii_doc.char_text ct
  11.                 INNER JOIN yii_doc.char_art ca ON ca.ca_char_text_id = ct.id
  12.                 WHERE ca.ca_char_id = 44
  13.                     AND ca.ca_pa_id = pa.id  LIMIT 1
  14.             )
  15.             , IFNULL(pm.mask_for_id, png.nameNew)
  16.         )
  17.         , IFNULL(pm.mask_for_id, png.nameNew)
  18.     ) AS product_title
  19.     , IF(pm.id IS NULL, '', pm.mask_for_ga) AS product_image_alt
  20.     , IFNULL(a.ART_ARTICLE_NR, pa.art_num) AS product_art_num
  21.     , IF(
  22.         drt_real.is_tecdoc = TRUE
  23.         , IFNULL(a.ART_ARTICLE_NR, pa.art_num)
  24.         , ''
  25.      ) AS tecdoc_art_num
  26.     , pa.pa_ga_id AS product_ga_id
  27.     , ba.NAME_BRANDS AS brand_name
  28.     , dg.name_ru AS product_name
  29.     , pa.avalaible_gen AS available
  30.     , CONCAT_WS(
  31.         ' '
  32.         , IF(
  33.             pa.pa_ga_id = 854
  34.             , IFNULL(
  35.                 (
  36.                     SELECT CONCAT(UPPER(LEFT(ct.text, 1)), SUBSTRING(ct.text, 2))
  37.                     FROM yii_doc.char_text ct
  38.                     INNER JOIN yii_doc.char_art ca ON ca.ca_char_text_id = ct.id
  39.                     WHERE ca.ca_char_id = 44
  40.                         AND ca.ca_pa_id = pa.id  LIMIT 1
  41.                 )
  42.                 , dg.name_ru
  43.             )
  44.             , dg.name_ru
  45.         )
  46.         , ba.NAME_BRANDS
  47.         , IFNULL(a.ART_ARTICLE_NR, pa.art_num)
  48.     ) AS link_title
  49.     , pa.pod_zakaz AS pod_zakaz
  50.     , pa.id AS canonical_id
  51.     , pa.id AS group_ids
  52.     , '' AS modification
  53.     , (
  54.         SELECT
  55.         GROUP_CONCAT(
  56.             DISTINCT CONCAT(
  57.                 IFNULL(ct4.text, ct1.text), ':', IFNULL(
  58.                     ct2.text, IFNULL(ca_char_int, ca_char_decimal)
  59.                 ), IFNULL(CONCAT(' ', ct3.text), '')
  60.             ) ORDER BY cd.tile SEPARATOR '\/'
  61.         ) AS tile
  62.         FROM char_art ca
  63.         LEFT JOIN `char` c ON ca.ca_char_id = c.id
  64.         LEFT JOIN char_dga cd ON c.id = cd.char_id
  65.         LEFT JOIN dok_razdel dr ON dr.id_group = cd.dr_id AND dr.id = 494
  66.         LEFT JOIN char_text ct1 ON c.cha_name_id = ct1.id
  67.         LEFT JOIN char_text ct2 ON IFNULL(ca.is_collapse, ca.ca_char_text_id) = ct2.id
  68.         LEFT JOIN char_text ct3 ON c.cha_name_unit_id = ct3.id
  69.         LEFT JOIN char_text ct4 ON c.short_tile_text_id = ct4.id
  70.         WHERE ca.ca_pa_id = pa.id  AND cd.tile IS NOT NULL
  71.     ) AS tile
  72.     , (
  73.         SELECT
  74.         GROUP_CONCAT(
  75.             DISTINCT CONCAT(
  76.                 IFNULL(ct4.text, ct1.text), ':', IFNULL(
  77.                     ct2.text, IFNULL(ca_char_int, ca_char_decimal)
  78.                 ), IFNULL(CONCAT(' ', ct3.text), '')
  79.             ) ORDER BY cd.char_id SEPARATOR '\/'
  80.         ) AS tile
  81.         FROM char_art ca
  82.         LEFT JOIN `char` c ON ca.ca_char_id = c.id
  83.         LEFT JOIN char_dga cd ON c.id = cd.char_id
  84.         LEFT JOIN dok_razdel dr ON dr.id_group = cd.dr_id AND dr.id = 494
  85.         LEFT JOIN char_text ct1 ON c.cha_name_id = ct1.id
  86.         LEFT JOIN char_text ct2 ON IFNULL(ca.is_collapse, ca.ca_char_text_id) = ct2.id
  87.         LEFT JOIN char_text ct3 ON c.cha_name_unit_id = ct3.id
  88.         LEFT JOIN char_text ct4 ON c.short_tile_text_id = ct4.id
  89.         WHERE ca.ca_pa_id = pa.id  AND cd.tile_advanced = 1
  90.     ) AS tile_advanced
  91.     , links.url_direct
  92.     , links.url_canonical
  93.     , links.url_marka_model
  94.     , IF(pbr.typeId IN (2, 3), pbr.remark, '') AS recommended_message
  95.     , IFNULL(pm.mask_for_ga, '') AS alt
  96.     , drt_real.is_tecdoc AS is_tecdoc
  97.     ,
  98.  IF(ptg.ptg_sort IS NULL || 1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4, pa_dr_review.pdr_rating_count, pa_dr_review.pdr_rating_count + 1) as rating_count
  99.  , IF(
  100. 0.75 <= IF(
  101. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  102. , IF(
  103.     1 = 0
  104.     , 1
  105.     , IF(
  106.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  107.         , rating.ptgr_rating
  108.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  109.     )
  110. )
  111. , IF(
  112.     ptg.ptg_sort IS NOT NULL
  113.     , IF(
  114.         1 = 0
  115.         , 1
  116.         , IF(
  117.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  118.             , 0
  119.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  120.         )
  121.     )
  122.     , IF(
  123.         rating.ptgr_rating != 0
  124.         , rating.ptgr_rating
  125.         , 0
  126.     )
  127. )
  128. ) - FLOOR(IF(
  129. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  130. , IF(
  131.     1 = 0
  132.     , 1
  133.     , IF(
  134.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  135.         , rating.ptgr_rating
  136.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  137.     )
  138. )
  139. , IF(
  140.     ptg.ptg_sort IS NOT NULL
  141.     , IF(
  142.         1 = 0
  143.         , 1
  144.         , IF(
  145.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  146.             , 0
  147.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  148.         )
  149.     )
  150.     , IF(
  151.         rating.ptgr_rating != 0
  152.         , rating.ptgr_rating
  153.         , 0
  154.     )
  155. )
  156. ))
  157. , FLOOR(IF(
  158. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  159. , IF(
  160.     1 = 0
  161.     , 1
  162.     , IF(
  163.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  164.         , rating.ptgr_rating
  165.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  166.     )
  167. )
  168. , IF(
  169.     ptg.ptg_sort IS NOT NULL
  170.     , IF(
  171.         1 = 0
  172.         , 1
  173.         , IF(
  174.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  175.             , 0
  176.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  177.         )
  178.     )
  179.     , IF(
  180.         rating.ptgr_rating != 0
  181.         , rating.ptgr_rating
  182.         , 0
  183.     )
  184. )
  185. )) + 1
  186. , IF(
  187.     0.25 <= IF(
  188. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  189. , IF(
  190.     1 = 0
  191.     , 1
  192.     , IF(
  193.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  194.         , rating.ptgr_rating
  195.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  196.     )
  197. )
  198. , IF(
  199.     ptg.ptg_sort IS NOT NULL
  200.     , IF(
  201.         1 = 0
  202.         , 1
  203.         , IF(
  204.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  205.             , 0
  206.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  207.         )
  208.     )
  209.     , IF(
  210.         rating.ptgr_rating != 0
  211.         , rating.ptgr_rating
  212.         , 0
  213.     )
  214. )
  215. ) - FLOOR(IF(
  216. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  217. , IF(
  218.     1 = 0
  219.     , 1
  220.     , IF(
  221.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  222.         , rating.ptgr_rating
  223.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  224.     )
  225. )
  226. , IF(
  227.     ptg.ptg_sort IS NOT NULL
  228.     , IF(
  229.         1 = 0
  230.         , 1
  231.         , IF(
  232.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  233.             , 0
  234.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  235.         )
  236.     )
  237.     , IF(
  238.         rating.ptgr_rating != 0
  239.         , rating.ptgr_rating
  240.         , 0
  241.     )
  242. )
  243. )) AND IF(
  244. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  245. , IF(
  246.     1 = 0
  247.     , 1
  248.     , IF(
  249.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  250.         , rating.ptgr_rating
  251.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  252.     )
  253. )
  254. , IF(
  255.     ptg.ptg_sort IS NOT NULL
  256.     , IF(
  257.         1 = 0
  258.         , 1
  259.         , IF(
  260.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  261.             , 0
  262.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  263.         )
  264.     )
  265.     , IF(
  266.         rating.ptgr_rating != 0
  267.         , rating.ptgr_rating
  268.         , 0
  269.     )
  270. )
  271. ) - FLOOR(IF(
  272. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  273. , IF(
  274.     1 = 0
  275.     , 1
  276.     , IF(
  277.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  278.         , rating.ptgr_rating
  279.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  280.     )
  281. )
  282. , IF(
  283.     ptg.ptg_sort IS NOT NULL
  284.     , IF(
  285.         1 = 0
  286.         , 1
  287.         , IF(
  288.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  289.             , 0
  290.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  291.         )
  292.     )
  293.     , IF(
  294.         rating.ptgr_rating != 0
  295.         , rating.ptgr_rating
  296.         , 0
  297.     )
  298. )
  299. )) < 0.75
  300.     ,  FLOOR(IF(
  301. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  302. , IF(
  303.     1 = 0
  304.     , 1
  305.     , IF(
  306.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  307.         , rating.ptgr_rating
  308.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  309.     )
  310. )
  311. , IF(
  312.     ptg.ptg_sort IS NOT NULL
  313.     , IF(
  314.         1 = 0
  315.         , 1
  316.         , IF(
  317.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  318.             , 0
  319.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  320.         )
  321.     )
  322.     , IF(
  323.         rating.ptgr_rating != 0
  324.         , rating.ptgr_rating
  325.         , 0
  326.     )
  327. )
  328. )) + 0.5
  329.     , FLOOR(IF(
  330. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  331. , IF(
  332.     1 = 0
  333.     , 1
  334.     , IF(
  335.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  336.         , rating.ptgr_rating
  337.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  338.     )
  339. )
  340. , IF(
  341.     ptg.ptg_sort IS NOT NULL
  342.     , IF(
  343.         1 = 0
  344.         , 1
  345.         , IF(
  346.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  347.             , 0
  348.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  349.         )
  350.     )
  351.     , IF(
  352.         rating.ptgr_rating != 0
  353.         , rating.ptgr_rating
  354.         , 0
  355.     )
  356. )
  357. ))
  358. )
  359. ) as rating
  360.  , SUBSTRING_INDEX(GROUP_CONCAT(pa.id ORDER BY pa.price_torg ASC SEPARATOR ','), ',', 1) as min_prices_all_id
  361.  , rating.ptgr_positive_rating_percentage AS positive_rating_percentage
  362.  , CASE
  363.         WHEN IF(
  364. 0.75 <= IF(
  365. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  366. , IF(
  367.     1 = 0
  368.     , 1
  369.     , IF(
  370.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  371.         , rating.ptgr_rating
  372.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  373.     )
  374. )
  375. , IF(
  376.     ptg.ptg_sort IS NOT NULL
  377.     , IF(
  378.         1 = 0
  379.         , 1
  380.         , IF(
  381.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  382.             , 0
  383.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  384.         )
  385.     )
  386.     , IF(
  387.         rating.ptgr_rating != 0
  388.         , rating.ptgr_rating
  389.         , 0
  390.     )
  391. )
  392. ) - FLOOR(IF(
  393. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  394. , IF(
  395.     1 = 0
  396.     , 1
  397.     , IF(
  398.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  399.         , rating.ptgr_rating
  400.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  401.     )
  402. )
  403. , IF(
  404.     ptg.ptg_sort IS NOT NULL
  405.     , IF(
  406.         1 = 0
  407.         , 1
  408.         , IF(
  409.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  410.             , 0
  411.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  412.         )
  413.     )
  414.     , IF(
  415.         rating.ptgr_rating != 0
  416.         , rating.ptgr_rating
  417.         , 0
  418.     )
  419. )
  420. ))
  421. , FLOOR(IF(
  422. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  423. , IF(
  424.     1 = 0
  425.     , 1
  426.     , IF(
  427.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  428.         , rating.ptgr_rating
  429.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  430.     )
  431. )
  432. , IF(
  433.     ptg.ptg_sort IS NOT NULL
  434.     , IF(
  435.         1 = 0
  436.         , 1
  437.         , IF(
  438.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  439.             , 0
  440.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  441.         )
  442.     )
  443.     , IF(
  444.         rating.ptgr_rating != 0
  445.         , rating.ptgr_rating
  446.         , 0
  447.     )
  448. )
  449. )) + 1
  450. , IF(
  451.     0.25 <= IF(
  452. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  453. , IF(
  454.     1 = 0
  455.     , 1
  456.     , IF(
  457.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  458.         , rating.ptgr_rating
  459.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  460.     )
  461. )
  462. , IF(
  463.     ptg.ptg_sort IS NOT NULL
  464.     , IF(
  465.         1 = 0
  466.         , 1
  467.         , IF(
  468.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  469.             , 0
  470.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  471.         )
  472.     )
  473.     , IF(
  474.         rating.ptgr_rating != 0
  475.         , rating.ptgr_rating
  476.         , 0
  477.     )
  478. )
  479. ) - FLOOR(IF(
  480. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  481. , IF(
  482.     1 = 0
  483.     , 1
  484.     , IF(
  485.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  486.         , rating.ptgr_rating
  487.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  488.     )
  489. )
  490. , IF(
  491.     ptg.ptg_sort IS NOT NULL
  492.     , IF(
  493.         1 = 0
  494.         , 1
  495.         , IF(
  496.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  497.             , 0
  498.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  499.         )
  500.     )
  501.     , IF(
  502.         rating.ptgr_rating != 0
  503.         , rating.ptgr_rating
  504.         , 0
  505.     )
  506. )
  507. )) AND IF(
  508. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  509. , IF(
  510.     1 = 0
  511.     , 1
  512.     , IF(
  513.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  514.         , rating.ptgr_rating
  515.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  516.     )
  517. )
  518. , IF(
  519.     ptg.ptg_sort IS NOT NULL
  520.     , IF(
  521.         1 = 0
  522.         , 1
  523.         , IF(
  524.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  525.             , 0
  526.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  527.         )
  528.     )
  529.     , IF(
  530.         rating.ptgr_rating != 0
  531.         , rating.ptgr_rating
  532.         , 0
  533.     )
  534. )
  535. ) - FLOOR(IF(
  536. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  537. , IF(
  538.     1 = 0
  539.     , 1
  540.     , IF(
  541.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  542.         , rating.ptgr_rating
  543.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  544.     )
  545. )
  546. , IF(
  547.     ptg.ptg_sort IS NOT NULL
  548.     , IF(
  549.         1 = 0
  550.         , 1
  551.         , IF(
  552.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  553.             , 0
  554.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  555.         )
  556.     )
  557.     , IF(
  558.         rating.ptgr_rating != 0
  559.         , rating.ptgr_rating
  560.         , 0
  561.     )
  562. )
  563. )) < 0.75
  564.     ,  FLOOR(IF(
  565. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  566. , IF(
  567.     1 = 0
  568.     , 1
  569.     , IF(
  570.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  571.         , rating.ptgr_rating
  572.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  573.     )
  574. )
  575. , IF(
  576.     ptg.ptg_sort IS NOT NULL
  577.     , IF(
  578.         1 = 0
  579.         , 1
  580.         , IF(
  581.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  582.             , 0
  583.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  584.         )
  585.     )
  586.     , IF(
  587.         rating.ptgr_rating != 0
  588.         , rating.ptgr_rating
  589.         , 0
  590.     )
  591. )
  592. )) + 0.5
  593.     , FLOOR(IF(
  594. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  595. , IF(
  596.     1 = 0
  597.     , 1
  598.     , IF(
  599.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  600.         , rating.ptgr_rating
  601.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  602.     )
  603. )
  604. , IF(
  605.     ptg.ptg_sort IS NOT NULL
  606.     , IF(
  607.         1 = 0
  608.         , 1
  609.         , IF(
  610.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  611.             , 0
  612.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  613.         )
  614.     )
  615.     , IF(
  616.         rating.ptgr_rating != 0
  617.         , rating.ptgr_rating
  618.         , 0
  619.     )
  620. )
  621. ))
  622. )
  623. ) >= 4.25 THEN IF(
  624. 0.75 <= IF(
  625. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  626. , IF(
  627.     1 = 0
  628.     , 1
  629.     , IF(
  630.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  631.         , rating.ptgr_rating
  632.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  633.     )
  634. )
  635. , IF(
  636.     ptg.ptg_sort IS NOT NULL
  637.     , IF(
  638.         1 = 0
  639.         , 1
  640.         , IF(
  641.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  642.             , 0
  643.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  644.         )
  645.     )
  646.     , IF(
  647.         rating.ptgr_rating != 0
  648.         , rating.ptgr_rating
  649.         , 0
  650.     )
  651. )
  652. ) - FLOOR(IF(
  653. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  654. , IF(
  655.     1 = 0
  656.     , 1
  657.     , IF(
  658.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  659.         , rating.ptgr_rating
  660.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  661.     )
  662. )
  663. , IF(
  664.     ptg.ptg_sort IS NOT NULL
  665.     , IF(
  666.         1 = 0
  667.         , 1
  668.         , IF(
  669.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  670.             , 0
  671.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  672.         )
  673.     )
  674.     , IF(
  675.         rating.ptgr_rating != 0
  676.         , rating.ptgr_rating
  677.         , 0
  678.     )
  679. )
  680. ))
  681. , FLOOR(IF(
  682. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  683. , IF(
  684.     1 = 0
  685.     , 1
  686.     , IF(
  687.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  688.         , rating.ptgr_rating
  689.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  690.     )
  691. )
  692. , IF(
  693.     ptg.ptg_sort IS NOT NULL
  694.     , IF(
  695.         1 = 0
  696.         , 1
  697.         , IF(
  698.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  699.             , 0
  700.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  701.         )
  702.     )
  703.     , IF(
  704.         rating.ptgr_rating != 0
  705.         , rating.ptgr_rating
  706.         , 0
  707.     )
  708. )
  709. )) + 1
  710. , IF(
  711.     0.25 <= IF(
  712. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  713. , IF(
  714.     1 = 0
  715.     , 1
  716.     , IF(
  717.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  718.         , rating.ptgr_rating
  719.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  720.     )
  721. )
  722. , IF(
  723.     ptg.ptg_sort IS NOT NULL
  724.     , IF(
  725.         1 = 0
  726.         , 1
  727.         , IF(
  728.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  729.             , 0
  730.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  731.         )
  732.     )
  733.     , IF(
  734.         rating.ptgr_rating != 0
  735.         , rating.ptgr_rating
  736.         , 0
  737.     )
  738. )
  739. ) - FLOOR(IF(
  740. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  741. , IF(
  742.     1 = 0
  743.     , 1
  744.     , IF(
  745.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  746.         , rating.ptgr_rating
  747.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  748.     )
  749. )
  750. , IF(
  751.     ptg.ptg_sort IS NOT NULL
  752.     , IF(
  753.         1 = 0
  754.         , 1
  755.         , IF(
  756.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  757.             , 0
  758.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  759.         )
  760.     )
  761.     , IF(
  762.         rating.ptgr_rating != 0
  763.         , rating.ptgr_rating
  764.         , 0
  765.     )
  766. )
  767. )) AND IF(
  768. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  769. , IF(
  770.     1 = 0
  771.     , 1
  772.     , IF(
  773.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  774.         , rating.ptgr_rating
  775.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  776.     )
  777. )
  778. , IF(
  779.     ptg.ptg_sort IS NOT NULL
  780.     , IF(
  781.         1 = 0
  782.         , 1
  783.         , IF(
  784.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  785.             , 0
  786.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  787.         )
  788.     )
  789.     , IF(
  790.         rating.ptgr_rating != 0
  791.         , rating.ptgr_rating
  792.         , 0
  793.     )
  794. )
  795. ) - FLOOR(IF(
  796. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  797. , IF(
  798.     1 = 0
  799.     , 1
  800.     , IF(
  801.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  802.         , rating.ptgr_rating
  803.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  804.     )
  805. )
  806. , IF(
  807.     ptg.ptg_sort IS NOT NULL
  808.     , IF(
  809.         1 = 0
  810.         , 1
  811.         , IF(
  812.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  813.             , 0
  814.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  815.         )
  816.     )
  817.     , IF(
  818.         rating.ptgr_rating != 0
  819.         , rating.ptgr_rating
  820.         , 0
  821.     )
  822. )
  823. )) < 0.75
  824.     ,  FLOOR(IF(
  825. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  826. , IF(
  827.     1 = 0
  828.     , 1
  829.     , IF(
  830.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  831.         , rating.ptgr_rating
  832.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  833.     )
  834. )
  835. , IF(
  836.     ptg.ptg_sort IS NOT NULL
  837.     , IF(
  838.         1 = 0
  839.         , 1
  840.         , IF(
  841.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  842.             , 0
  843.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  844.         )
  845.     )
  846.     , IF(
  847.         rating.ptgr_rating != 0
  848.         , rating.ptgr_rating
  849.         , 0
  850.     )
  851. )
  852. )) + 0.5
  853.     , FLOOR(IF(
  854. ptg.ptg_sort IS NOT NULL AND rating.ptgr_rating != 0
  855. , IF(
  856.     1 = 0
  857.     , 1
  858.     , IF(
  859.         1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  860.         , rating.ptgr_rating
  861.         , (8 * rating.ptgr_rating + 2 * 1.7600211202534 * ptg.ptg_sort + 0.022132265587187) / 10
  862.     )
  863. )
  864. , IF(
  865.     ptg.ptg_sort IS NOT NULL
  866.     , IF(
  867.         1 = 0
  868.         , 1
  869.         , IF(
  870.             1.7600211202534 * ptg.ptg_sort + 0.022132265587187 <= 4
  871.             , 0
  872.             , 1.7600211202534 * ptg.ptg_sort + 0.022132265587187
  873.         )
  874.     )
  875.     , IF(
  876.         rating.ptgr_rating != 0
  877.         , rating.ptgr_rating
  878.         , 0
  879.     )
  880. )
  881. ))
  882. )
  883. )
  884.         ELSE 0
  885.    END AS sort_rating
  886.  , pa.price_torg AS price
  887.  
  888. FROM yii_doc.prices_all pa
  889. LEFT JOIN tecdoc_2016q1_europe.ARTICLES a ON pa.pa_art_id = a.ART_ID
  890. LEFT JOIN yii_doc.dok_ga dg ON pa.pa_ga_id_search = dg.GA_ID
  891. LEFT JOIN yii_doc.dok_razdel_template drt ON drt.razdel_id = 494
  892. LEFT JOIN yii_doc.brand_all ba ON pa.brand_id = ba.id
  893. LEFT JOIN yii_doc.brand_text bt ON ba.id = bt.brand_id
  894. LEFT JOIN yii_doc.pa_mask pm ON pm.id = pa.id
  895. LEFT JOIN dok_system_tables.product_link_all links ON links.pa_id = pa.id
  896. LEFT JOIN yii_doc.ProductNameGa AS png ON png.GA_ID = pa.pa_ga_id_search
  897. LEFT JOIN yii_doc.dok_razdel_template drt_real ON drt_real.razdel_id = dg.parent_id
  898. LEFT JOIN yii_doc.ProductBrandRecommended AS pbr ON pbr.brandId = pa.brand_id
  899.     AND pbr.productNameId = png.productNameId
  900. LEFT JOIN PA_TYP_GA_RATING AS rating ON rating.ptgr_pa_id = pa.id AND rating.ptgr_typ_id = 29481
  901. LEFT JOIN PA_DR_RATING AS pa_dr_review ON pa_dr_review.pdr_pa_id = pa.id
  902. INNER JOIN PA_TYP_GA as ptg ON ptg.ptg_pa_id = pa.id
  903.     AND ptg.ptg_dr_id = 494
  904.     AND ptg.ptg_typ_id = 29481
  905. WHERE 1=1
  906.     AND pa.id IN (167065,68221,554169,125035,553317,868585,553356,198824,200689,968595,2044257,86688)
  907. GROUP BY pa.id
  908. ORDER BY FIELD(pa.id, 167065,68221,554169,125035,553317,868585,553356,198824,200689,968595,2044257,86688)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!