API tools faq
paste
miholeus

goods module sql

miholeus
Mar 15th, 2013
118
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 8.78 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2.     p.products_id,
  3.     p.products_id as product_id,
  4.     p.products_model,
  5.     si.server_path as image_url,
  6.     smprod.url as prod_url,
  7.     p.manufacturers_id,
  8.     p.manual_price,
  9.     p.reliz_firm_id,
  10.     prfirm.man_id as reliz_man_id,
  11.     IF(prp.id IS NOT NULL
  12.             AND pdo.id IS NOT NULL,
  13.         prfirm_do.otgruzka,
  14.         prfirm.otgruzka) as reliz_otgruzka,
  15.     IF(prp.id IS NOT NULL
  16.             AND pdo.id IS NOT NULL,
  17.         IF(prfirm_do.date_next_order > NOW(),
  18.             prfirm_do.date_next_order,
  19.             null),
  20.         IF(prfirm.date_next_order > NOW(),
  21.             prfirm.date_next_order,
  22.             null)) as date_next_order,
  23.     sprfa.incoming_date as reliz_incoming,
  24.     IF(prp.id IS NOT NULL
  25.             AND pdo.id IS NOT NULL,
  26.         prfirm_do.id,
  27.         prfirm.id) as reliz_otgruzka_id,
  28.     IF(prp.id IS NOT NULL
  29.             AND pdo.id IS NOT NULL,
  30.         prfirm_do.man_id,
  31.         prfirm.man_id) as reliz_otgruzka_man_id,
  32.     smprod.categories_id,
  33.     smprod.yandex_kart,
  34.     p.yandex_status,
  35.     p.yandex_price,
  36.     p.yandex_price_prior,
  37.     p.yandex_shop,
  38.     smprod.product_name as products_name,
  39.     pd.short_desc_for_manager as `desc`,
  40.     pd.komplekt as komplekt,
  41.     IFNULL(prsklad.num_wdem_tovar, 0) as num_wdem_tovar,
  42.     IFNULL(prsklad.num_otpravlen, 0) as num_otpravlen,
  43.     IFNULL(prsklad.num_ozidaet, 0) as num_ozidaet,
  44.     IFNULL(prsklad.num_payed, 0) as num_payed,
  45.     IFNULL(prsklad.num_in_gorod, 0) as num_in_gorod,
  46.     IFNULL(prsklad.num_transit_tovar, 0) as num_transit_tovar,
  47.     IFNULL(prsklad.num_bron, 0) as num_bron,
  48.     IFNULL(prsklad.num_prihod, 0) as num_prihod,
  49.     prsklad.num as num_sklad,
  50.     p.num_sklad as all_sklad,
  51.     p.products_date_inven,
  52.     p.option_use,
  53.     p.products_date_added,
  54.     p.products_last_modified,
  55.     prsklad_all.date_last_prihod as products_last_prihod,
  56.     pdate_last_buy.date as products_date_last_buy,
  57.     pdate_last_reliz.date as products_date_last_reliz,
  58.     p.products_date_market_price,
  59.     prsklad.date_prihod as date_available,
  60.     prsklad_all.last_up as products_date_1c,
  61.     p.num_wait_prih,
  62.     smprod.model as model_tel,
  63.     IFNULL(pdo.num_reserv, p.num_reserv) AS num_reserv,
  64.     p.num_reserv AS all_reserv,
  65.     p.num_average_day,
  66.     p.num_price,
  67.     smprod.product_status as products_status,
  68.     smprod.zvanie,
  69.     p.to_1c,
  70.     IFNULL(prsklad_all.num, 0) as all_sklad,
  71.     IFNULL(prsklad_all.num_prihod, 0) as all_prihod,
  72.     (IFNULL(prsklad_all.num, 0) - IFNULL(prsklad_all.num_otpravlen, 0) - IFNULL(prsklad_all.num_payed, 0) - IFNULL(prsklad_all.num_bron, 0)) as all_ostatok,
  73.     p.num_other_sklad,
  74.     IF(pdo.price IS NULL OR pdo.price = 0,
  75.         p.products_price,
  76.         pdo.price) as products_price,
  77.     IF(smprod.product_status != 'sklad',
  78.         IFNULL(r.zak_price, p.products_price - 300),
  79.         p.zak_price) as products_zak_price,
  80.     IF(pdo.price IS NULL OR pdo.price = 0,
  81.         p.products_price - IF(smprod.product_status != 'sklad',
  82.             IFNULL(r.zak_price, p.products_price - 300),
  83.             p.zak_price) + IFNULL(IF(smprod.product_status != 'sklad',
  84.                     IFNULL(r.zak_price, p.products_price - 300),
  85.                     p.zak_price) / 1.18 * bonus.percent_nds / 100 + IF(smprod.product_status != 'sklad',
  86.                     IFNULL(r.zak_price, p.products_price - 300),
  87.                     p.zak_price) * bonus.percent / 100 + bonus.sum_add,
  88.                 0),
  89.         pdo.price - IF(smprod.product_status != 'sklad',
  90.             IFNULL(r.zak_price, p.products_price - 300),
  91.             p.zak_price) + IFNULL(IF(smprod.product_status != 'sklad',
  92.                     IFNULL(r.zak_price, p.products_price - 300),
  93.                     p.zak_price) / 1.18 * bonus.percent_nds / 100 + IF(smprod.product_status != 'sklad',
  94.                     IFNULL(r.zak_price, p.products_price - 300),
  95.                     p.zak_price) * bonus.percent / 100 + bonus.sum_add,
  96.                 0)) as price_profit,
  97.     IFNULL(IF(smprod.product_status != 'sklad',
  98.                 IFNULL(r.zak_price, p.products_price - 300),
  99.                 p.zak_price) / 1.18 * bonus.percent_nds / 100 + IF(smprod.product_status != 'sklad',
  100.                 IFNULL(r.zak_price, p.products_price - 300),
  101.                 p.zak_price) * bonus.percent / 100 + bonus.sum_add,
  102.             0) as bonus,
  103.     IF(sps.display_in_product_list IS NULL
  104.             OR sps.display_in_product_list = '1',
  105.         IF(prp.id IS NOT NULL
  106.                 AND pdo.id IS NOT NULL,
  107.             IF(sps.product_status_id IN (3 , 8),
  108.                 2,
  109.                 1),
  110.             1),
  111.         2) as options_status,
  112.     pdo.products_status as options_status_id,
  113.     IF(prp.id IS NOT NULL
  114.             AND pdo.id IS NOT NULL,
  115.         1,
  116.         0) as reliz_firm_show,
  117.     IF(prp.id IS NOT NULL
  118.             AND pdo.id IS NOT NULL
  119.             AND sps.status_code IN ('none' , 'off', 'deleted'),
  120.         1,
  121.         0) as options_inactive,
  122.     sps.title as options_status_comment,
  123.     p.is_analog as analog_id,
  124.     p.id1c as products_id_1c,
  125.     pdo.id as products_do_id,
  126.     pdo.id as do_id,
  127.     pdo.color as do_color,
  128.     pdo.name as do_name,
  129.     pdo.val as do_val,
  130.     pdo.qua as do_qua,
  131.     IFNULL(cat.sort_order, 999) as cat_sort_order,
  132.     sps.product_status_order as product_status_option_order,
  133.     IFNULL(smprod.products_sort_order, 999) as sort_order,
  134.     IF(p.num_sklad < 1
  135.             AND smprod.product_status <> 'sklad',
  136.         IF(p.reliz_firm_id IS NULL,
  137.             IF(p.products_date_market_succes IS NOT NULL
  138.                     AND DATE_ADD(CURRENT_DATE, INTERVAL - 7 DAY) <= p.products_date_market_succes,
  139.                 1,
  140.                 0),
  141.             1),
  142.         0) as allow_reliz,
  143.     IF(p.num_sklad < 1
  144.             AND p.products_date_market_succes IS NOT NULL
  145.             AND DATE_ADD(CURRENT_DATE, INTERVAL - 5 DAY) <= p.products_date_market_succes,
  146.         IF(p.yandex_price IS NOT NULL,
  147.             CEIL((p.yandex_price + 600) * 1.03),
  148.             0),
  149.         0) as allow_market_price,
  150.     IF(p.products_date_market_succes IS NOT NULL
  151.             AND DATE_ADD(CURRENT_DATE, INTERVAL - 5 DAY) <= p.products_date_market_succes,
  152.         1,
  153.         IF(p.yandex_status IN (1 , 2), 1, 0)) as allow_order_market
  154. FROM
  155.     `products` as p
  156.         INNER JOIN
  157.     sotm_products smprod ON (smprod.products_id = p.products_id)
  158.         LEFT JOIN
  159.     products_description AS pd ON (pd.products_id = p.products_id)
  160.         LEFT JOIN
  161.     products_sklad AS prsklad_all ON (prsklad_all.sklad = '000000001'
  162.         AND prsklad_all.option_id = 0
  163.         AND prsklad_all.id1c = p.id1c)
  164.         LEFT JOIN
  165.     categories cat ON (cat.categories_id = smprod.categories_id)
  166.         LEFT JOIN
  167.     sotm_product_x_image as def_img ON def_img.product_id = p.products_id
  168.         AND def_img.is_default = 1
  169.         LEFT JOIN
  170.     sotm_images si ON (si.id = def_img.image_id)
  171.         LEFT JOIN
  172.     products_reliz r ON (r.id = p.reliz_id)
  173.         LEFT JOIN
  174.     products_reliz_firm prfirm ON (prfirm.id = p.reliz_firm_id
  175.         AND prfirm.status = '1')
  176.         LEFT JOIN
  177.     products_property prp ON (prp.id1c = p.id1c
  178.         AND prp.param = 'reliz_firm_show'
  179.         AND prp.value = '1')
  180.         LEFT JOIN
  181.     products_do as pdo ON (pdo.id1c = p.id1c)
  182.         LEFT JOIN
  183.     sotm_product_status sps ON (sps.product_status_id = pdo.products_status)
  184.         LEFT JOIN
  185.     products_reliz_firm prfirm_do ON (prp.id IS NOT NULL
  186.         AND prfirm_do.status = '1'
  187.         AND prfirm_do.id = pdo.reliz_firm_id)
  188.         LEFT JOIN
  189.     products_sklad AS prsklad ON (`prsklad`.`sklad` IN ('000000001')
  190.         AND prsklad.option_id = IFNULL(pdo.id, 0)
  191.         AND prsklad.id1c = p.id1c)
  192.         LEFT JOIN
  193.     products_dates as pdate_last_reliz ON (pdate_last_reliz.id1c = p.id1c
  194.         AND pdate_last_reliz.`field` = 'last_reliz')
  195.         LEFT JOIN
  196.     products_dates as pdate_last_buy ON (pdate_last_buy.id1c = p.id1c
  197.         AND pdate_last_buy.`field` = 'last_buy')
  198.         LEFT JOIN
  199.     sotm_product_reliz_firm_available sprfa ON (sprfa.product_reliz_firm_id = IF(prp.id IS NOT NULL
  200.             AND pdo.id IS NOT NULL,
  201.         prfirm_do.id,
  202.         prfirm.id))
  203.         LEFT JOIN
  204.     sotm_product_bonus AS bonus ON (bonus.product_id = p.products_id
  205.         AND IF(bonus.date_from != '0000-00-00',
  206.         CURDATE() > bonus.date_from,
  207.         1)
  208.         AND IF(bonus.date_to != '0000-00-00',
  209.         CURDATE() < bonus.date_to,
  210.         1))
  211. WHERE
  212.     1 = 1 AND 0 = 0
  213. GROUP BY products_id , products_do_id
  214. ORDER BY IF(CAST(prsklad.num AS SIGNED) - CAST(prsklad.num_otpravlen AS SIGNED) - CAST(prsklad.num_payed AS SIGNED) - CAST(prsklad.num_bron AS SIGNED) > 0,
  215.     1,
  216.     0) DESC , cat.sort_order ASC , prsklad.num DESC , sort_order ASC , p.id1c ASC , sps.product_status_order ASC
  217. LIMIT 0 , 100
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
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!