mirror of
https://github.com/retailcrm/legacy.git
synced 2024-11-23 13:56:02 +03:00
0
Примеры
Vitaly Artemev edited this page 2017-03-16 13:49:52 +03:00
Table of Contents
ICML
offers.sql
SELECT
item.shop_items_catalog_item_id as id,
item.shop_items_catalog_item_id as productId,
(CASE item.shop_items_catalog_is_active WHEN 0 THEN 'N' ELSE 'Y' END) as productActivity,
item.shop_groups_id as categoryId,
item.shop_items_catalog_name as name,
item.shop_items_catalog_price as price,
item.shop_items_catalog_image as picture,
item.shop_items_catalog_path as url,
item.shop_items_catalog_marking as article
FROM
shop_items_catalog_table as item
LEFT JOIN
shop_groups_table as groups
ON
(groups.shop_groups_id = item.shop_groups_id)
WHERE
groups.shop_groups_path != ''
AND
item.shop_shops_id = 1
ORDER BY
categoryId
categories.sql
SELECT
*
FROM
`shop_groups_table`
WHERE
`shop_shops_id` = 1
ORDER BY
shop_groups_id
OffersHandler.php
<?php
class OffersHandler implements HandlerInterface
{
public function prepare($offers)
{
$categories = $this->getCategories();
$container = Container::getInstance();
foreach ($offers as $k => $v) {
$picture = 'upload/shop_1/' .
substr($v['id'], 0, 1) . '/' .
substr($v['id'], 1, 1) . '/' .
substr($v['id'], 2, 1) . '/' .
'item_'. $v['id'] .'/' .
$v['picture'];
$offers[$k]['picture'] = $container->shopUrl . '/' . $picture;
$categoryId = $v['categoryId'];
$offers[$k]['url'] = $container->shopUrl . '/shop/' . $categories[$categoryId]['path'] .'/'. $v['url'];
$offers[$k]['categoryId'] = array($categoryId);
$offers[$k] = array_filter($offers[$k]);
}
return $offers;
}
private function getCategories()
{
$builder = new ExtendedOffersBuilder();
$data = $builder->buildCategories();
$categories = array();
$process = true;
foreach($data as $category) {
$categories[$category['shop_groups_id']] = array(
'parentId' => $category['shop_groups_parent_id'],
'path' => $category['shop_groups_path'],
'name' => $category['shop_groups_name']
);
}
while($process) {
$count = 0;
foreach($categories as $k => $v) {
if ($v['parentId'] != 0) {
$categories[$k]['path'] = $categories[$v['parentId']]['path'] .'/'. $v['path'];
$categories[$k]['parentId'] = $categories[$v['parentId']]['parentId'];
$count++;
}
}
if ($count <= 0) {
$process = false;
}
}
return $categories;
}
}
CategoriesHandler.php
<?php
class CategoriesHandler implements HandlerInterface
{
public function prepare($data)
{
$categories = array();
foreach ($data as $category) {
$categories[] = array(
'id' => $category['shop_groups_id'],
'parentId' => $category['shop_groups_parent_id'],
'name' => $category['shop_groups_name']
);
}
return $categories;
}
}
ExtendedOffersBuilder.php
<?php
class ExtendedOffersBuilder extends Builder
{
public function buildCategories()
{
$query = $this->rule->getSQL('categories');
$handler = $this->rule->getHandler('CategoriesHandler');
$this->sql = $this->container->db->prepare($query);
return $this->build($handler);
}
}
Выгрузка заказов
orders.sql
SELECT
o.shop_order_id AS externalId,
(
CASE
o.site_users_id
WHEN
0
THEN
NULL
ELSE
o.site_users_id
END
) AS customerId,
o.shop_order_users_name AS firstName,
o.shop_order_users_surname AS lastName,
o.shop_order_users_patronymic AS patronymic,
o.shop_order_users_email AS email,
o.shop_order_phone AS phone,
(
CASE
o.shop_order_index
WHEN
0
THEN
NULL
ELSE
o.shop_order_index
END
) AS deliveryIndex,
co.shop_country_name AS deliveryCountry,
loc.shop_location_name AS deliveryRegion,
ci.shop_city_name AS deliveryCity,
o.shop_order_address AS deliveryAddress,
delivery.shop_type_of_delivery_id AS deliveryType,
delivery.shop_cond_of_delivery_id AS deliveryService,
o.shop_system_of_pay_id AS paymentType,
(
CASE
o.shop_order_status_of_pay
WHEN
1
THEN
'paid'
WHEN
0
THEN
'not-paid'
ELSE
'more'
END
) AS paymentStatus,
o.shop_order_description AS customerComment,
o.shop_order_date_time AS createdAt,
(
SELECT
GROUP_CONCAT(
CONCAT_WS(
';',
shop_items_catalog_item_id,
shop_order_items_name,
shop_order_items_quantity,
shop_order_items_price
)
SEPARATOR '|'
)
FROM
shop_order_items_table
WHERE
shop_items_catalog_item_id != 0
AND
shop_order_id = o.shop_order_id
GROUP BY
shop_order_id
) AS items
FROM
shop_order_table AS o
LEFT JOIN
shop_city_table AS ci
ON
(ci.shop_city_id = o.shop_city_id)
LEFT JOIN
shop_country_table AS co
ON
(co.shop_country_id = o.shop_country_id)
LEFT JOIN
shop_location_table AS loc
ON
(loc.shop_location_id = o.shop_location_id)
LEFT JOIN
shop_system_of_pay_table AS payment
ON
(payment.shop_system_of_pay_id = o.shop_system_of_pay_id)
LEFT JOIN
shop_cond_of_delivery_table AS delivery
ON
(delivery.shop_cond_of_delivery_id = o.shop_cond_of_delivery_id)
WHERE
o.shop_shops_id = 1
ORDER BY
o.shop_order_id ASC
orders_last.sql
SELECT
o.shop_order_id AS externalId,
(
CASE
o.site_users_id
WHEN
0
THEN
o.shop_order_id
ELSE
o.site_users_id
END
) AS customerId,
o.shop_order_users_name AS firstName,
o.shop_order_users_surname AS lastName,
o.shop_order_users_patronymic AS patronymic,
o.shop_order_users_email AS email,
o.shop_order_phone AS phone,
(
CASE
o.shop_order_index
WHEN
0
THEN
NULL
ELSE
o.shop_order_index
END
) AS deliveryIndex,
co.shop_country_name AS deliveryCountry,
loc.shop_location_name AS deliveryRegion,
ci.shop_city_name AS deliveryCity,
o.shop_order_address AS deliveryAddress,
delivery.shop_type_of_delivery_id AS deliveryType,
delivery.shop_cond_of_delivery_id AS deliveryService,
o.shop_system_of_pay_id AS paymentType,
(
CASE
o.shop_order_status_of_pay
WHEN
1
THEN
'paid'
WHEN
0
THEN
'not-paid'
ELSE
'more'
END
) AS paymentStatus,
o.shop_order_description AS customerComment,
o.shop_order_date_time AS createdAt,
(
SELECT
GROUP_CONCAT(
CONCAT_WS(
';',
shop_items_catalog_item_id,
shop_order_items_name,
shop_order_items_quantity,
shop_order_items_price
)
SEPARATOR '|'
)
FROM
shop_order_items_table
WHERE
shop_items_catalog_item_id != 0
AND
shop_order_id = o.shop_order_id
GROUP BY
shop_order_id
) AS items
FROM
shop_order_table AS o
LEFT JOIN
shop_city_table AS ci
ON
(ci.shop_city_id = o.shop_city_id)
LEFT JOIN
shop_country_table AS co
ON
(co.shop_country_id = o.shop_country_id)
LEFT JOIN
shop_location_table AS loc
ON
(loc.shop_location_id = o.shop_location_id)
LEFT JOIN
shop_system_of_pay_table AS payment
ON
(payment.shop_system_of_pay_id = o.shop_system_of_pay_id)
LEFT JOIN
shop_cond_of_delivery_table AS delivery
ON
(delivery.shop_cond_of_delivery_id = o.shop_cond_of_delivery_id)
WHERE
o.shop_shops_id = 1
AND
o.shop_order_date_time BETWEEN :lastSync AND NOW()
ORDER BY
o.shop_order_id ASC
orders_uid.sql
SELECT
o.shop_order_id AS externalId,
(
CASE
o.site_users_id
WHEN
0
THEN
o.shop_order_id
ELSE
o.site_users_id
END
) AS customerId,
o.shop_order_users_name AS firstName,
o.shop_order_users_surname AS lastName,
o.shop_order_users_patronymic AS patronymic,
o.shop_order_users_email AS email,
o.shop_order_phone AS phone,
(
CASE
o.shop_order_index
WHEN
0
THEN
NULL
ELSE
o.shop_order_index
END
) AS deliveryIndex,
co.shop_country_name AS deliveryCountry,
loc.shop_location_name AS deliveryRegion,
ci.shop_city_name AS deliveryCity,
o.shop_order_address AS deliveryAddress,
delivery.shop_type_of_delivery_id AS deliveryType,
delivery.shop_cond_of_delivery_id AS deliveryService,
o.shop_system_of_pay_id AS paymentType,
(
CASE
o.shop_order_status_of_pay
WHEN
1
THEN
'paid'
WHEN
0
THEN
'not-paid'
ELSE
'more'
END
) AS paymentStatus,
o.shop_order_description AS customerComment,
o.shop_order_date_time AS createdAt,
(
SELECT
GROUP_CONCAT(
CONCAT_WS(
';',
shop_items_catalog_item_id,
shop_order_items_name,
shop_order_items_quantity,
shop_order_items_price
)
SEPARATOR '|'
)
FROM
shop_order_items_table
WHERE
shop_items_catalog_item_id != 0
AND
shop_order_id = o.shop_order_id
GROUP BY
shop_order_id
) AS items
FROM
shop_order_table AS o
LEFT JOIN
shop_city_table AS ci
ON
(ci.shop_city_id = o.shop_city_id)
LEFT JOIN
shop_country_table AS co
ON
(co.shop_country_id = o.shop_country_id)
LEFT JOIN
shop_location_table AS loc
ON
(loc.shop_location_id = o.shop_location_id)
LEFT JOIN
shop_system_of_pay_table AS payment
ON
(payment.shop_system_of_pay_id = o.shop_system_of_pay_id)
LEFT JOIN
shop_cond_of_delivery_table AS delivery
ON
(delivery.shop_cond_of_delivery_id = o.shop_cond_of_delivery_id)
WHERE
FIND_IN_SET(o.shop_order_id, :orderIds)
AND
o.shop_shops_id = 1
ORDER BY
o.shop_order_id ASC
OrdersHandler.php
<?php
class OrdersHandler implements HandlerInterface
{
public function prepare($data)
{
$orders = array();
foreach ($data as $record) {
$order = array();
$order['externalId'] = $record['externalId'];
if ($record['customerId']) {
$order['customerId'] = ($record['customerId'] == 0)
? $record['externalId']
: $record['customerId']
;
}
$order['firstName'] = $record['firstName'];
$order['lastName'] = $record['lastName'];
$order['patronymic'] = $record['patronymic'];
$order['email'] = $record['email'];
$order['phone'] = $record['phone'];
$order['delivery'] = array(
'address' => array(
'index' => $record['deliveryIndex'] == 0 ? '' : $record['deliveryIndex'],
'country' => $record['deliveryCountry'],
'region' => $record['deliveryRegion'],
'city' => $record['deliveryCity']
),
'code' => $record['deliveryType'],
'service' => array(
'code'=> $record['deliveryService'],
)
);
if (
!empty($record['deliveryIndex']) &&
!empty($record['deliveryCountry']) &&
!empty($record['deliveryCity']) &&
!empty($record['deliveryAddress'])
) {
$order['delivery']['address']['text'] = implode(
', ',
array(
$record['deliveryIndex'],
$record['deliveryCountry'],
$record['deliveryCity'],
$record['deliveryAddress']
)
);
}
$order['paymentType'] = $record['paymentType'];
$order['paymentStatus'] = $record['paymentStatus'];
$order['createdAt'] = $record['createdAt'];
$order['customerComment'] = $record['customerComment'];
$order['items'] = array();
$items = explode('|', $record['items']);
foreach ($items as $item) {
$data = explode(';', $item);
$item = array();
$item['productId'] = $data[0];
$item['productName'] = (isset($data[1])) ? $data[1] : 'no-name';
$item['quantity'] = (isset($data[2])) ? (int) $data[2] : 0;
$item['initialPrice'] = (isset($data[3]) && $data[3] != '') ? $data[3] : 0 ;
array_push($order['items'], $item);
}
$order = DataHelper::filterRecursive($order);
array_push($orders, $order);
}
return $orders;
}
}
Выгрузка справочников
payment_types.sql
SELECT
shop_system_of_pay_id as code,
shop_system_of_pay_name as name,
shop_system_of_pay_description as description
FROM
shop_system_of_pay_table
WHERE
shop_shops_id = 1
delivery_types.sql
SELECT
`shop_cond_of_delivery_id` as `code`,
`shop_cond_of_delivery_name` as `name`,
`shop_type_of_delivery_id` as `deliveryType`
FROM
`shop_cond_of_delivery_table`
WHERE
`shop_type_of_delivery_id`
IN
(
SELECT * FROM
(
SELECT
shop_type_of_delivery_id
FROM
shop_type_of_delivery_table
WHERE
shop_shops_id = 1
) AS subquery
)
ORDER BY
`shop_type_of_delivery_id` ASC
PaymentTypesHandler.php
<?php
class PaymentTypesHandler implements HandlerInterface
{
public function prepare($types)
{
return $types;
}
}
DeliveryTypesHandler.php
<?php
class DeliveryTypesHandler implements HandlerInterface
{
public function prepare($types)
{
return $types;
}
}
DeliveryServicesHandler.php
<?php
class DeliveryServicesHandler implements HandlerInterface
{
public function prepare($services)
{
return $services;
}
}
Получение истории
orders_history_create.sql
INSERT IGNORE INTO
`shop_order_table`
(
`shop_order_users_name`,
`shop_order_users_surname`,
`shop_order_users_patronymic`,
`shop_order_users_email`,
`shop_order_phone`,
`shop_cond_of_delivery_id`,
`shop_order_delivery_price`,
`shop_system_of_pay_id`,
`shop_order_status_of_pay`,
`shop_order_date_of_pay`,
`shop_order_address`,
`shop_order_index`,
`shop_order_description`,
`shop_order_date_time`,
`shop_order_cancel`,
`shop_country_id`,
`shop_shops_id`,
`shop_order_account_number`
)
SELECT
IF(:firstName IS NOT NULL, :firstName, ''),
IF(:lastName IS NOT NULL, :lastName, ''),
IF(:patronymic IS NOT NULL, :patronymic, ''),
IF(:email IS NOT NULL, :email, ''),
IF(:phone IS NOT NULL, :phone, ''),
IF(:deliveryType IS NOT NULL, :deliveryType, ''),
IF(:deliveryCost IS NOT NULL, :deliveryCost, ''),
IF(:paymentType IS NOT NULL, :paymentType, ''),
IF(:paymentStatus IS NOT NULL, :paymentStatus, 0),
IF(:paymentStatus = 1, NOW(), '0000-00-00 00:00:00'),
IF(:address IS NOT NULL, :address, ''),
IF(:postcode IS NOT NULL, :postcode, 0),
IF(:description IS NOT NULL, :description, ''),
IF(:createdAt IS NOT NULL, :createdAt, NOW()),
:isCanceled,
175,
1,
(MAX(shop_order_id) + 1)
FROM
`shop_order_table`
orders_history_create_items.sql
INSERT INTO
`shop_order_items_table`
(
`shop_order_id`,
`shop_items_catalog_item_id`,
`shop_order_items_name`,
`shop_order_items_quantity`,
`shop_order_items_price`
)
VALUES
(
:shop_order_id,
:shop_items_catalog_items_id,
:shop_orders_items_name,
:shop_orders_items_quantity,
:shop_orders_items_price
)
OrdersHistoryHandler.php
<?php
class OrdersHistoryHandler implements HandlerInterface
{
public function prepare($data)
{
$this->container = Container::getInstance();
$this->logger = new Logger();
$this->rule = new Rule();
$this->api = new RequestProxy(
$this->container->settings['api']['url'],
$this->container->settings['api']['key']
);
$orderGroups = $this->api->statusGroupsList();
if (!is_null($orderGroups)) {
$isCanceled = $orderGroups['statusGroups']['cancel']['statuses'];
}
$update = $this->rule->getSQL('orders_history_update');
$create = $this->rule->getSQL('orders_history_create');
foreach($data as $record) {
if (!empty($record['externalId'])) {
$this->sql = $this->container->db->prepare($update);
$this->sql->bindParam(':orderExternalId', $record['externalId']);
} else {
$this->sql = $this->container->db->prepare($create);
if (!empty($record['createdAt'])) {
$this->sql->bindParam(':createdAt', $record['createdAt']);
} else {
$this->sql->bindParam(':createdAt', $var = NULL);
}
}
if (!empty($record['firstName'])) {
$this->sql->bindParam(':firstName', $record['firstName']);
} else {
$this->sql->bindParam(':firstName', $var = NULL);
}
if (!empty($record['lastName'])) {
$this->sql->bindParam(':lastName', $record['lastName']);
} else {
$this->sql->bindParam(':lastName', $var = NULL);
}
if (!empty($record['patronymic'])) {
$this->sql->bindParam(':patronymic', $record['patronymic']);
} else {
$this->sql->bindParam(':patronymic', $var = NULL);
}
if (!empty($record['email'])) {
$this->sql->bindParam(':email', $record['email']);
} else {
$this->sql->bindParam(':email', $var = NULL);
}
if (!empty($record['phone'])) {
$this->sql->bindParam(':phone', $record['phone']);
} else {
$this->sql->bindParam(':phone', $var = NULL);
}
if (!empty($record['customerComment'])) {
$this->sql->bindParam(':description', $record['customerComment']);
} else {
$this->sql->bindParam(':description', $var = NULL);
}
if (!empty($record['delivery']['service']['code'])) {
$this->sql->bindParam(':deliveryType', $record['delivery']['service']['code']);
} else {
$this->sql->bindParam(':deliveryType', $var = NULL);
}
if (!empty($record['delivery']['cost'])) {
$this->sql->bindParam(':deliveryCost', $record['delivery']['cost']);
} else {
$this->sql->bindParam(':deliveryCost', $var = NULL);
}
if (!empty($record['paymentType'])) {
$this->sql->bindParam(':paymentType', $record['paymentType']);
} else {
$this->sql->bindParam(':paymentType', $var = NULL);
}
if (!empty($record['paymentStatus']) && $record['paymentStatus'] == 'paid') {
$this->sql->bindParam(':paymentStatus', $status = 1);
} else {
$this->sql->bindParam(':paymentStatus', $status = 0);
}
if (!empty($record['delivery']['address']['index'])) {
$this->sql->bindParam(':postcode', $record['delivery']['address']['index']);
} else {
$this->sql->bindParam(':postcode', $var = NULL);
}
if (!empty($record['delivery']['address']['text'])) {
$this->sql->bindParam(':address', $record['delivery']['address']['text']);
} else {
$this->sql->bindParam(':address', $var = NULL);
}
if (!empty($record['status']) && in_array($record['status'], $isCanceled)) {
$this->sql->bindParam(':isCanceled', $cancel = 1);
} else {
$this->sql->bindParam(':isCanceled', $cancel = 0);
}
try {
$this->sql->execute();
$this->oid = $this->container->db->lastInsertId();
if (empty($record['externalId'])) {
$response = $this->api->ordersFixExternalIds(
array(
array(
'id' => (int) $record['id'],
'externalId' => $this->oid
)
)
);
}
} catch (PDOException $e) {
$this->logger->write(
'PDO: ' . $e->getMessage(),
$this->container->errorLog
);
return false;
}
if (!empty($record['items']) && empty($record['externalId'])) {
foreach($record['items'] as $item) {
$items = $this->rule->getSQL('orders_history_create_items');
$this->query = $this->container->db->prepare($items);
$this->query->bindParam(':shop_order_id', $this->oid);
$this->query->bindParam(':shop_items_catalog_items_id', $item['offer']['externalId']);
$this->query->bindParam(':shop_orders_items_name', $item['offer']['name']);
$this->query->bindParam(':shop_orders_items_quantity', $item['quantity']);
$this->query->bindParam(':shop_orders_items_price', $item['initialPrice']);
try {
$this->query->execute();
} catch (PDOException $e) {
$this->logger->write(
'PDO: ' . $e->getMessage(),
$this->container->errorLog
);
return false;
}
}
}
}
}
}