OBOIRU¶

oboiru¶

своднные данные oboi.ru. розничная 1С, СУП

1C¶

Catalog_KontrAgent ReplacingMergeTree

-- oboiru.Catalog_KontrAgent definition

CREATE TABLE oboiru.Catalog_KontrAgent
(
    `Ref_Key` String,
    `partner_Key` String,
    `name` String,
    `type` String,
    `inn` UInt64
)
ENGINE = ReplacingMergeTree
ORDER BY Ref_Key

Catalog_LoyaltyCard ReplacingMergeTree

-- oboiru.Catalog_LoyaltyCard definition

CREATE TABLE oboiru.Catalog_LoyaltyCard
(
    `Ref_Key` String,
    `partner_Key` String,
    `name` String,
    `barcode` String,
    `status` String
)
ENGINE = ReplacingMergeTree
ORDER BY Ref_Key

Catalog_Partner ReplacingMergeTree

-- oboiru.Catalog_Partner definition

CREATE TABLE oboiru.Catalog_Partner
(
    `Ref_Key` String,
    `manager_Key` String,
    `name` String,
    `is_client` UInt8,
    `is_supplier` UInt8,
    `is_carrier` UInt8,
    `is_other` UInt8
)
ENGINE = ReplacingMergeTree
ORDER BY Ref_Key

SUP¶

Sup_Selling ReplacingMergeTree

-- oboiru.Sup_Sellings definition

CREATE TABLE oboiru.Sup_Sellings
(
    `ShopId` String,
    `Domain` String,
    `Document` String,
    `Date` DateTime,
    `ManagerName` String,
    `Summ` Float64
)
ENGINE = ReplacingMergeTree
ORDER BY (ManagerName,
            ShopId,
            Date,
            Domain,
            Document)

Sup_Traffic ReplacingMergeTree

-- oboiru.Sup_Traffic definition

CREATE TABLE oboiru.Sup_Traffic
(
    `ShopId` String,
    `Domain` String,
    `Date` DateTime,
    `Traffic` UInt16
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(Date)
ORDER BY (ShopId,
            Date,
            Domain)

Views¶

Звонки - Астериск органичение на глубину - 6 месяцев View

-- oboiru.View_Calls_Asterisk source

CREATE VIEW oboiru.View_Calls_Asterisk
(
    `calldate` DateTime,
    `src` String,
    `src_name` Nullable(String),
    `src_full` Nullable(String),
    `dst` String,
    `dst_name` Nullable(String),
    `dst_full` Nullable(String),
    `src_dst` Nullable(String),
    `src_dst_full` Nullable(String),
    `duration` Float32,
    `billsec` Float32,
    `disposition` String,
    `recordingfile` String,
    `src_is_ours` UInt8 DEFAULT 0,
    `dst_is_ours` UInt8 DEFAULT 0
) AS
SELECT
    *,
    (src_name IS NULL, '-', src_name) AS src_name,
    concat(src, ' - ', dst) AS src_dst,
    concat(src_full, ' - ', dst_full) AS src_dst_full,
    concat(dst, if(dst_name IS NULL, '', concat(' 🐼', dst_name))) AS dst_full
FROM
(
    SELECT
        *,
        concat(src, if(src_name IS NULL, '', concat(' 🐼', src_name))) AS src_full
    FROM
    (
        SELECT
            calldate,
            src,
            dst,
            duration,
            billsec,
            disposition,
            replaceRegexpOne(recordingfile, '/home/user', 'https://ast.shopstenova.ru/ast09') AS recordingfile
        FROM asterisk_OBOIRU.cdr
        WHERE calldate > date_sub(MONTH, 6, now())
    ) AS cdr
    LEFT JOIN
    (
        SELECT
            number AS src,
            name AS src_name,
            is_ours AS src_is_ours
        FROM oboiru.View_Phone_Book
    ) AS src_phone_book USING (src)
) AS cdr_src

LEFT JOIN

(
    SELECT
        number AS dst,
        name AS dst_name,
        is_ours AS dst_is_ours
    FROM oboiru.View_Phone_Book
) AS dst_phone_book USING (dst)

SETTINGS join_use_nulls = 0;

Партнеры View

-- oboiru.View_Partner source

CREATE VIEW oboiru.View_Partner
(
    `Ref_Key` String,
    `manager_Key` String,
    `name` String,
    `is_client` UInt8,
    `is_supplier` UInt8,
    `is_carrier` UInt8,
    `is_other` UInt8,
    `barcode` String
) AS
SELECT *
FROM
(
    SELECT *
    FROM oboiru.Catalog_Partner
) AS partner

RIGHT JOIN

(
    SELECT
        partner_Key AS Ref_Key,
        barcode
    FROM oboiru.Catalog_LoyaltyCard
) AS loyalty USING (Ref_Key);
WHERE name <> ''

Абонентская книга - Астериск oboi.ru + Сотрудники опта из монги View

-- oboiru.View_Phone_Book source

CREATE VIEW oboiru.View_Phone_Book
(
    `number` String,
    `name` String,
    `is_ours` UInt8
) AS
SELECT
    number,
    multiIf(notEmpty(ast_name),
    ast_name,
    notEmpty(bitrix_name),
    bitrix_name, '') AS name,
    if(notEmpty(ast_name) OR notEmpty(bitrix_name), 1, 0) AS is_ours
FROM
(
    SELECT
        barcode AS number,
        name AS name_1c
    FROM oboiru.View_Partner
) AS `1c`

FULL OUTER JOIN

(
    SELECT *
    FROM
    (
        SELECT
            NUMBER AS number,
            name AS ast_name
        FROM asterisk_OBOIRU.phone_book
    ) AS asterisk
    FULL OUTER JOIN
    (
        SELECT
            KONTAKT_PHONE_MOB AS number,
            FIO_FI AS bitrix_name
        FROM API_RP.mongo_SOTRUDNIKI
    ) AS bitrix USING (number)
) AS asterisk_bitrix USING (number)

SETTINGS join_use_nulls = 0;

API_OBOIRU¶

api сайта oboi.ru

mongo_PRODUCTS MongoDB

-- API_OBOIRU.mongo_PRODUCTS definition

CREATE TABLE API_OBOIRU.mongo_PRODUCTS
(
    `category` String,
    `vendor` String,
    `collection` String,
    `article` String
)
ENGINE = MongoDB('10.55.55.61:27017',
                'API2',
                'PRODUCTS',
                'user',
                'password');

tapeti¶

данные из СУПа для Тапети

mongo_ACCOUNTS MongoDB

-- tapeti.mongo_ACCOUNTS definition

CREATE TABLE tapeti.mongo_ACCOUNTS
(
    `id` Int16,
    `name` String
)
ENGINE = MongoDB('10.55.55.62:27017',
'SHOPSTENOVA',
'ACCOUNTS',
'user',
'password');

mongo_SELLING ReplacingMergeTree

-- tapeti.mongo_SELLING definition

CREATE TABLE tapeti.mongo_SELLING
(
    `_id` String,
    `email` String,
    `user_id` Int16 DEFAULT 0,
    `article` String,
    `document` String,
    `document_key` String,
    `document_type` String ALIAS if(quantity >= 0, 'продажа', 'возврат'),
    `price` Decimal(10, 2) DEFAULT 0,
    `price_sum` Decimal(10, 2) ALIAS price * quantity,
    `profit_sum` Decimal(10, 2) ALIAS price_sum - costprice_sum,
    `quantity` Decimal(10, 2) DEFAULT 0,
    `costprice` Decimal(10, 2) DEFAULT 0,
    `costprice_sum` Decimal(10, 2) ALIAS costprice * quantity,
    `date` DateTime,
    `date_date` Date ALIAS toDate(date),
    `date_year` UInt16 ALIAS toYear(date),
    `date_month` UInt16 ALIAS toMonth(date),
    `date_month_h` String ALIAS multiIf(date_month = 1, 'янв',
                                        date_month = 2, 'фeв',
                                        date_month = 3, 'мар',
                                        date_month = 4, 'апр',
                                        date_month = 5, 'май',
                                        date_month = 6, 'июн',
                                        date_month = 7, 'июл',
                                        date_month = 8, 'авг',
                                        date_month = 9, 'сен',
                                        date_month = 10, 'окт',
                                        date_month = 11, 'ноя',
                                        'дек'),
    `date_weekday` UInt16 ALIAS toDayOfWeek(date),
    `date_weekday_h` String ALIAS multiIf(date_weekday = 1, 'пн',
                                        date_weekday = 2, 'вт',
                                        date_weekday = 3, 'ср',
                                        date_weekday = 4, 'чт',
                                        date_weekday = 5, 'пт',
                                        date_weekday = 6, 'сб',
                                        'вс')
)
ENGINE = ReplacingMergeTree
ORDER BY _id

mongo_SHOPS MongoDB

-- tapeti.mongo_SHOPS definition

CREATE TABLE tapeti.mongo_SHOPS
(
    `email` String,
    `region` String ALIAS substring(email, 1, 2),
    `region_h` String ALIAS multiIf(region = '37', 'Ивановская обл',
                                    region = '33', 'Владимирская обл',
                                    region = '52', 'Нижегородская обл',
                                    region = '62', 'Рязанская обл',
                                    region = '67', 'Смоленская обл',
                                    region = '76', 'Ярославская обл',
                                    'Не определен'),
    `name` String,
    `address` String,
    `format` String,
    `tochkabu` Int32
)
ENGINE = MongoDB('10.55.55.62:27017',
                'SHOPSTENOVA',
                'SHOPS',
                'user',
                'password');

mongo_tapeti_nps ReplacingMergeTree

-- tapeti.mongo_tapeti_nps definition

CREATE TABLE tapeti.mongo_tapeti_nps
(
    `id` String,
    `phone` String,
    `comment` String,
    `complete_date` DateTime,
    `user` String,
    `nps` Nullable(UInt8),
    `source` String,
    `shop_ID` String
)
ENGINE = ReplacingMergeTree
ORDER BY id

архив 23-01

-- tapeti.mongo_tapeti_nps definition

CREATE TABLE tapeti.mongo_tapeti_nps
(
    `phone` String,
    `comment` String,
    `complete_date` DateTime,
    `user` String,
    `nps` UInt8,
    `source` String,
    `shop_ID` String
)
ENGINE = MongoDB('10.55.55.62:27017',
                'SHOPSTENOVA',
                'NPS',
                'user',
                'password');

asterisk_OBOIRU¶

телефония розницы

cdr MySQL

-- asterisk_OBOIRU.cdr definition

CREATE TABLE asterisk_OBOIRU.cdr
(
    `recordingfile` String,
    `calldate` DateTime,
    `src` String,
    `dst` String,
    `billsec` Float32,
    `duration` Float32,
    `disposition` String
)
ENGINE = MySQL('10.55.55.40:3306',
                'asteriskcdrdb',
                'cdr',
                'user',
                'password');

phone_book таблица заполняется вручную MySQL

-- asterisk_OBOIRU.phone_book definition

CREATE TABLE asterisk_OBOIRU.phone_book
(
    `NUMBER` String,
    `name` String,
    `description` String
)
ENGINE = MySQL('10.55.55.40:3306',
                'asteriskcdrdb',
                'phone_book',
                'user',
                'password');