Jump to content
freelancer

полезные SQL запросы

Recommended Posts

тема представляет собой cookbook полезных запросов для OpenCart.

просьба придерживаться правил форума и писать только по делу. весь оффтоп будет удален, вопросы по скриптам задавать авторам в ЛС

запросы можно выполнять в

http://opencartforum...8-sql-executor/ (начиная с версии 2 там появился экспорт)

phpMyAdmin

формат

1. название, краткое описание(опционально)

2. сам скрипт

3. пример вывода

Edited by freelancer
  • +1 1

Share this post


Link to post
Share on other sites

экспорт заказов

SELECT o.`order_id` 'Заказ', firstname 'Имя', lastname 'Фамилия', email, telephone 'Телефон', GROUP_CONCAT(`name` SEPARATOR '; ') 'Товары', sum(o.total) 'Итог' FROM `order_product` op LEFT JOIN `order` o USING(order_id) GROUP BY order_id

Заказ;Имя;Фамилия;email;Телефон;Товары;Итог

1;seo;Виктория;Victoxxxxency@mail.ru;0672826701;"iPod Nano";120.0000

2;Евген;Абрамов;lexxxxard@mail.ru;567890;iPhone;129.0800

3;qwe;qweewq;as05xxxx@ma.ru;1234531;iPhone;129.0800

Share this post


Link to post
Share on other sites

Отличная вещь! Спасибо! Еще не хватает возможности сохранять запросы чтоб каждый раз не копипастить.

Share this post


Link to post
Share on other sites

выгрузка покупателей

SELECT customer_id 'id', firstname 'Имя', lastname 'Фамилия', email 'E-mail', telephone 'Телефон', fax 'Факс', newsletter 'Подписка', ip 'Ip', cg.name 'Группа'  FROM customer c LEFT JOIN сustomer_group cg USING(customer_group_id)

id;Имя;Фамилия;E-mail;Телефон;Факс;Подписка;Ip;Группа

1;ацуцауауц;weffweefw;unxxxls@ya.ru;2234;;0;178.xxx.245;"По умолчанию"

2;111;111;test@te.st;1111111111;;0;84xxx6.7;"По умолчанию"

3;Qurban;Ali;quxxxxxxan.ali@goodlogics.com;9898989;;0;180.178.162.169;"По умолчанию"

4;awaro;awaro;wexxxxome@awaro.ru;awaro;;0;95.xxx00.44;"По умолчанию"

Share this post


Link to post
Share on other sites

UPDATE `product` SET points = round(price);

Установить кол-во бонусных баллов для покупки товара равное цене

Share this post


Link to post
Share on other sites

Обновление названий товаров в Заказах, если они изменялись в Описании.

UPDATE order_product op, product_description pd SET op.name = pd.name WHERE op.product_id = pd.product_id AND pd.language_id = 1

language_id выбрать нужный, к примеру язык по умолчанию.

  • +1 1

Share this post


Link to post
Share on other sites

Найти заказы от не авторизованного покупателя (гостевые заказы)

SELECT order_id, oc_order.customer_id order_customer, oc_customer.customer_id customer_customer, oc_order.email
FROM oc_order
LEFT JOIN oc_customer ON oc_order.email = oc_customer.email
WHERE oc_order.customer_id <> oc_customer.customer_id
ORDER BY oc_order.email

Добавить гостевые заказы в историю Покупателя

UPDATE oc_order
LEFT JOIN oc_customer ON oc_order.email = oc_customer.email
SET oc_order.customer_id = oc_customer.customer_id
WHERE oc_order.customer_id <> oc_customer.customer_id
  • +1 1

Share this post


Link to post
Share on other sites

Включить опцию "Отображать размерную сетку" во всех товарах:

update oc_product set popupsize='1'

 

Share this post


Link to post
Share on other sites

Для обновления цены и наличия кто-то может написать как выглядит запрос? Если имеет название или артикул товара

Share this post


Link to post
Share on other sites

Не написано как нужно обновить цену.

 

Например, увеличить на 25% для товаров с product_id > 1111:

update oc_product set price = price * 1.25 where product_id > 1111;

 

Или установить количество 111 для товров с product_id > 1111:

update oc_product set quantity = 111 where product_id > 1111;

 

 

Share this post


Link to post
Share on other sites

Хотелось бы обновлять обновлять цены автоматом, например, такая возможность вроде как есть в программе датакол. Или исходя из справки через нее не получится?

Share this post


Link to post
Share on other sites
On 10/1/2018 at 5:03 PM, dreemix said:

Хотелось бы обновлять обновлять цены автоматом, например, такая возможность вроде как есть в программе датакол. Или исходя из справки через нее не получится?

Можно и автоматом, причем только средствами mysql, даже без всяких cron \ php. Вы только определитесь, по какому событию\условию нужно вносить изменения?

 

В одном случае Вам стоит почитать про триггеры (настраивается на какое-то событие в БД; например, при изменении\добавлении строки в какой-либо таблице),

а в другом случае, если нужно вносить изменения по расписанию\по времени , то стоит почитать про события (CREATE EVENT syntax).

 

Например, у меня был такой костыль, который каждые полчаса мониторил новые отзывы и начислял авторам бонусные баллы, проверяя, при этом, что бы дважды бонусы не начислялись за повторный отзывы\ответы одному и тому же автору... или, помню было, автоматически менялись статусы заказов с проверкой всяких условий... и ни одной строчки на php )

Edited by 100napb

Share this post


Link to post
Share on other sites

по сабжу, в копилку полезных запросов:

 

Вот эти строчки красиво посчитают Вам количество и сумму заказов магазина в разрезе месяцев\годов. Можно потом в экселе диаграмму построить, если хотите.

Нужно только указать ВАШИ id-шники статусов заказов, которые запрос будет суммировать. В примере текста запроса ниже это 8, 9, 10, 11, 12 - замените их на свои. Например, это все id-шники оплаченных\отгруженных заказов, за которые вы УЖЕ получили денежку. Подсмотреть эти id-шники можно в таблице oc_order_status (префикс oc_ может отличаться)

 

Запрос

Spoiler

SELECT YEAR(`o`.`date_added`) AS `YEAR`,
       (CASE MONTH(`o`.`date_added`)
            WHEN 1 THEN 'Январь'
            WHEN 2 THEN 'Февраль'
            WHEN 3 THEN 'Март'
            WHEN 4 THEN 'Апрель'
            WHEN 5 THEN 'Май'
            WHEN 6 THEN 'Июнь'
            WHEN 7 THEN 'Июль'
            WHEN 8 THEN 'Август'
            WHEN 9 THEN 'Сентябрь'
            WHEN 10 THEN 'Октябрь'
            WHEN 11 THEN 'Ноябрь'
            WHEN 12 THEN 'Декабрь'
        END) AS `MONTH`,
       COUNT(0) AS `ORDERS_COUNT`,
       SUM(`o`.`total`) AS `TOTAL_SUM_WITH_SHIPPING`
FROM `oc_order` `o`
WHERE (`o`.`order_status_id` IN (8, 9, 10, 11, 12))
GROUP BY YEAR(`o`.`date_added`),
         MONTH(`o`.`date_added`)

Результат

Spoiler

1448456052_.JPG.256fa1d03dec0ae7c0d349435db0b48a.JPG

 

Share this post


Link to post
Share on other sites
Posted (edited)

скрипт предназначен для поиска клиентов, имеющих X и более заказ(ов) ранее чем N-месяцев назад
И НЕ
имеющих заказов после этого

этих клиентов можно и нужно стимулировать и выводить на новый заказ :)
например - сделать таргетированную расылку по email

Spoiler

/*############################################################################
скрипт предназначен для поиска клиентов, имеющих X и более заказ(ов) ранее чем N-месяцев назад
И НЕ
имеющих заказов после этого

этих клиентов можно и нужно стимулировать и выводить на новый заказ :)
например - сделать таргетированную расылку по email
############################################################################*/

-- указать интервал поиска заказов, кол-во месяцев
SET @interval = 3;
-- указать минимальное кол-во заказов клиента N-месяцев назад
SET @orders_count = 1;
-- указать статусы заказов, которые не будем учитывать; 0 - забытые корзинки; 9999 - просто для примера
SET @order_status_to_ignore = '0,9999';

SELECT SQL_NO_CACHE
  *
FROM (SELECT
    o.customer_id,
    o.lastname,
    o.email,
    COUNT(*) AS `orders_count_BEFORE_INTERVAL`,
    SUM(o.total) AS orders_sum_BEFORE_INTERVAL,
    MIN(o.date_added) first_order_date,
    MAX(o.date_added) AS last_order_date

  FROM oc_order o,
       oc_customer oc
  WHERE o.customer_id != 0
  AND oc.customer_id = o.customer_id
  AND NOT FIND_IN_SET(o.order_status_id, @order_status_to_ignore)
  AND o.date_added < (DATE_SUB(NOW(), INTERVAL @interval MONTH))

  AND NOT EXISTS (SELECT
      *
    FROM oc_order o2,
         oc_customer oc2
    WHERE o2.customer_id != 0
    AND oc2.customer_id = o2.customer_id
    AND o.customer_id = o2.customer_id
    AND NOT FIND_IN_SET(o2.order_status_id, @order_status_to_ignore)
    AND o2.date_added >= (DATE_SUB(NOW(), INTERVAL @interval MONTH))
    GROUP BY o2.customer_id)
  GROUP BY o.customer_id
  HAVING COUNT(*) >= @orders_count

  ORDER BY 5 DESC) t

 

 

Edited by 100napb

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
You are posting as a guest. If you have an account, please sign in.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×

Important Information

On our site, cookies are used and personal data is processed to improve the user interface. To find out what and what personal data we are processing, please go to the link. If you click "I agree," it means that you understand and accept all the conditions specified in this Privacy Notice.