ОПТ¶

RP1C¶

разделы 1С - справочники и 1С - документы пилилась для строителей PowerBI дашборда. В итоге ребята замучались собирать это лего и был добавлен метод "http Продажи и возвраты". Обновление данных по крону с 67 машины.

1C - справочники¶

Менеджеры ReplacingMergeTree

-- RP1c.CatalogManager definition

CREATE TABLE RP1c.CatalogManager
(
    `Ref_Key` String,
    `Description` String,
    `Phone` String,
    `Email` String
)
ENGINE = ReplacingMergeTree
ORDER BY Ref_Key

Партнеры ReplacingMergeTree

-- RP1c.CatalogPartner definition

CREATE TABLE RP1c.CatalogPartner
(
    `Date_create` DateTime,
    `Date_activity` DateTime,
    `Ref_Key` String,
    `Parent_Key` String,
    `Parent_Calculated_Key` String ALIAS if(Parent_Key = '', Ref_Key, Parent_Key),
    `Manager_Key` String,
    `Busines_region_Key` String,
    `Description` String,
    `Koordinator_name` String,
    `Client` UInt8,
    `Supplier` UInt8,
    `Carrier` UInt8,
    `Group` String
)
ENGINE = ReplacingMergeTree
ORDER BY Ref_Key

Склады ReplacingMergeTree

-- RP1c.CatalogSklad definition

CREATE TABLE RP1c.CatalogSklad
(
    `Ref_Key` String,
    `Description` String,
    `IsFolder` UInt8
)
ENGINE = ReplacingMergeTree
ORDER BY Ref_Key

Производитель ReplacingMergeTree

-- RP1c.CatalogVendor definition

CREATE TABLE RP1c.CatalogVendor
(
    `Ref_Key` String,
    `Description` String,
    `IsFolder` UInt8,
    `Collectibillity` UInt8,
    `About` String
)
ENGINE = ReplacingMergeTree
ORDER BY Ref_Key

Марки / Коллекции ReplacingMergeTree

-- RP1c.CatalogCollection definition

CREATE TABLE RP1c.CatalogCollection
(
    `Ref_Key` String,
    `Vendor_Key` String,
    `Description` String,
    `IsFolder` UInt8,
    `About` String
)
ENGINE = ReplacingMergeTree
ORDER BY Ref_Key

Товары ReplacingMergeTree

категория товара выгружается примитивно

    В 1С существует регистр категории товара в котором хранится состояние с привязкой ко времени.
    Данная выгрузка отображает только текущее состояние товара и текущую категорию соответственно.

    Речь о категориях "Хит, Новинка и т.п."

-- RP1c.CatalogProduct definition

CREATE TABLE RP1c.CatalogProduct
(
    `Date_create` DateTime,
    `Date_first_in` DateTime,
    `Ref_Key` String,
    `Parent_Key` String,
    `Vendor_Key` String,
    `Collection_Key` String,
    `Type_of_product` String,
    `Article` String,
    `Description` String,
    `Vendor` String,
    `Collection` String,
    `Category` String,
    `IsFolder` UInt8
)
ENGINE = ReplacingMergeTree
ORDER BY Ref_Key

Бизнес регионы ReplacingMergeTree

    -- RP1c.CatalogBusinesRegion definition

    CREATE TABLE RP1c.CatalogBusinesRegion
    (
        `Ref_Key` String,
        `Parent_Key` String,
        `Description` String
    )
    ENGINE = ReplacingMergeTree
    ORDER BY Ref_Key

1C - документы¶

Возврат ReplacingMergeTree

-- RP1c.DocumentReturn definition

CREATE TABLE RP1c.DocumentReturn
(
    `Ref_Key` String,
    `Number` String,
    `Organization_Key` String,
    `Realiz_Key` String,
    `Partner_Key` String,
    `Kontragent_Key` String,
    `Document_type` String,
    `Status` String,
    `Store_Key` String,
    `Date` DateTime,
    `Date_year` UInt16 ALIAS toYear(Date),
    `Date_month` UInt8 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` UInt8 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 Ref_Key

Возврат - таблица товаров ReplacingMergeTree

-- RP1c.DocumentReturnProduct definition

CREATE TABLE RP1c.DocumentReturnProduct
(
    `Return_Key` String,
    `Product_Key` String,
    `String_Key` UInt16,
    `Count` UInt16,
    `Price` Float64,
    `Sum_positive` Float64,
    `Sum_nds_positive` Float64,
    `Sum` Float64 ALIAS Sum_positive * -1,
    `Sum_nds` Float64 ALIAS Sum_nds_positive * -1
)
ENGINE = ReplacingMergeTree
ORDER BY (Return_Key,
String_Key)

Реализация ReplacingMergeTree

-- RP1c.DocumentSale definition

CREATE TABLE RP1c.DocumentSale
(
    `Ref_Key` String,
    `Number` String,
    `Organization_Key` String,
    `Zakaz_Key` String,
    `Partner_Key` String,
    `Kontragent_Key` String,
    `Manager_Key` String,
    `Store_Key` String,
    `Status` String,
    `Document_type` String,
    `Date` DateTime,
    `Date_year` UInt16 ALIAS toYear(Date),
    `Date_month` UInt8 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` UInt8 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 Ref_Key

Реализация - таблица товаров ReplacingMergeTree

-- RP1c.DocumentSaleProduct definition

CREATE TABLE RP1c.DocumentSaleProduct
(
    `Realiz_Key` String,
    `Product_Key` String,
    `Zakaz_Key` String,
    `String_Key` UInt16,
    `Count` UInt16,
    `Price` Float64,
    `Sum` Float64,
    `Sum_nds` Float64
)
ENGINE = ReplacingMergeTree
ORDER BY (Realiz_Key,
String_Key)

1C - http методы¶

Логистика распределение стоимости доставки по реализациям ReplacingMergeTree

-- RP1c.HttpLogistics definition

CREATE TABLE RP1c.HttpLogistics
(
    `Partner_Key` String,
    `Organization_Key` String,
    `Document_Key` String,
    `Document_type` String,
    `Document_number` String,
    `Document_sum` Float64,
    `Document_delivery_sum_by_sum` Float64,
    `Document_delivery_sum_by_weight` Float64,
    `Document_weight` Float64,
    `Document_volume` Float64,
    `Km` UInt32,
    `KgKm` UInt32,
    `KgKm_sum` Float64,
    `Region` String,
    `Delivery_date` DateTime,
    `Delivery_sum_overall` Float64,
    `Delivery_type` String,
    `Delivery_description` String
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(Delivery_date)
ORDER BY Document_Key

Остатки по складу ReplacingMergeTree

-- RP1c.HttpStoreBalance definition

CREATE TABLE RP1c.HttpStoreBalance
(
    `Product_Key` String,
    `Store_Key` String,
    `Balance` Float32,
    `Reserved` Float32,
    `Cost_price` Float32
)
ENGINE = ReplacingMergeTree
ORDER BY (Product_Key,
Store_Key)

Остатки по партиям ReplacingMergeTree

-- RP1c.HttpPartyBalance definition

CREATE TABLE RP1c.HttpPartyBalance
(
    `Product_Key` String,
    `Store_Key` String,
    `Organization_Key` String,
    `Document_name` String,
    `Balance` Float32,
    `Reserved` Float32,
    `Cost_price_sum` Float32,
    `Cost_price` Float32 ALIAS Cost_price_sum / Balance,
    `Date_in` DateTime
)
ENGINE = ReplacingMergeTree
ORDER BY (Product_Key,
Store_Key,
Organization_Key,
Document_name,
Balance,
Reserved,
Cost_price_sum,
Date_in)

ПДЗ просроченая дебиторская задолженность ReplacingMergeTree

-- RP1c.HttpPdz definition

CREATE TABLE RP1c.HttpPdz
(
    `Document_Key` String,
    `Organization_Key` String,
    `Partner_Key` String,
    `Document_date` DateTime,
    `Number` String,
    `Debt_date` DateTime,
    `Debt_day` UInt16,
    `Debt_sum` Float64,
    `Before_sum` Float64
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(Document_date)
ORDER BY (Document_Key,
Debt_date)

График поступления оплат ReplacingMergeTree

-- RP1c.HttpPdzMoneyPlan definition

CREATE TABLE RP1c.HttpPdzMoneyPlan
(
    `Organization_Key` String,
    `Partner_Key` String,
    `Date` Date,
    `Debt_sum` Float64
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(Date)
ORDER BY (Organization_Key,
Partner_Key,
Date)

Продажи и возвраты ReplacingMergeTree

-- RP1c.HttpSaleAndReturn definition

CREATE TABLE RP1c.HttpSaleAndReturn
(
    `Store_Key` String,
    `Partner_Key` String,
    `Organization_Key` String,
    `Product_Key` String,
    `Document_Key` String,
    `Number` String,
    `Document_type` String,
    `Document_osn` String,
    `Realiz_type` String,
    `Date` DateTime,
    `Count` Float64,
    `Price` Float64,
    `Sum` Float64,
    `Cost_price_sum` Float64,
    `Cost_price` Float64 ALIAS Cost_price_sum / Count
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(Date)
ORDER BY (Document_Key,
Product_Key,
Count,
Price)

1C - регистры¶

Себестоимость ReplacingMergeTree

-- RP1c.RegisterCostprice definition

CREATE TABLE RP1c.RegisterCostprice
(
    `Product_Key` String,
    `Cost` Float64,
    `Date` DateTime
)
ENGINE = ReplacingMergeTree
ORDER BY (Product_Key,
Date)

amocrm - mongodb¶

План продаж ReplacingMergeTree

План сохраняется проектом "Чарт звёзд"

-- RP1c.MongoPlan definition

CREATE TABLE RP1c.MongoPlan
(
    `Manager_amoID` String,
    `Partner_Key` String,
    `Manager_email` String,
    `Manager_name` String,
    `Month` UInt8,
    `Year` UInt16,
    `Plan_money` UInt32
)
ENGINE = ReplacingMergeTree
ORDER BY (Partner_Key,
Month,
Year)

Views¶

Товары с папками до 3го родителя View

-- RP1c.CatalogProductView source

CREATE VIEW RP1c.CatalogProductView
(
    `Date_create` DateTime,
    `Date_first_in` DateTime,
    `Ref_Key` String,
    `Parent_Key` String,
    `Vendor_Key` String,
    `Collection_Key` String,
    `Type_of_product` String,
    `Article` String,
    `Description` String,
    `Vendor` String,
    `Collection` String,
    `Category` String,
    `IsFolder` UInt8,
    `FolderNameL1` String,
    `FolderNameL2` String,
    `FolderNameL3` String
) AS
SELECT *
FROM
(
    SELECT *
    FROM
    (
        SELECT *
        FROM
        (
            SELECT
                *,

                Parent_Key AS Parent_Key_L1
            FROM RP1c.CatalogProduct
        ) AS Product
        LEFT JOIN
        (
            SELECT
                Ref_Key AS Parent_Key_L1,
                Parent_Key AS Parent_Key_L2,
                Description AS FolderNameL1
            FROM RP1c.CatalogProduct
        ) AS Product1 USING (Parent_Key_L1)
    ) AS Folder1
    LEFT JOIN
    (
        SELECT
            Ref_Key AS Parent_Key_L2,
            Parent_Key AS Parent_Key_L3,
            Description AS FolderNameL2
        FROM RP1c.CatalogProduct
    ) AS Product2 USING (Parent_Key_L2)
) AS Folder2
LEFT JOIN
(
    SELECT
        Ref_Key AS Parent_Key_L3,
        Parent_Key AS Parent_Key_L4,
        Description AS FolderNameL3
    FROM RP1c.CatalogProduct
) AS Product3 USING (Parent_Key_L3)
WHERE IsFolder = 0;

Возвраты - документ + товары View

-- RP1c.DocumentReturnView source

CREATE VIEW RP1c.DocumentReturnView
(
    `Realiz_Key` String,
    `Return_Key` String,
    `Product_Key` String,
    `String_Key` UInt16,
    `Count` UInt16,
    `Price` Float64,
    `Sum` Float64,
    `Sum_nds` Float64,
    `Status` String,
    `Document_type` String,
    `Date` DateTime,
    `Organization_Key` String,
    `Partner_Key` String,
    `Kontragent_Key` String,
    `Store_Key` String
) AS
SELECT *
FROM
(
    SELECT
        *,
        Sum,
        Sum_nds
    FROM RP1c.DocumentReturnProduct
) AS Product
INNER JOIN
(
    SELECT
        Ref_Key AS Return_Key,
        *
    FROM RP1c.DocumentReturn
) AS Document USING (Return_Key);

Продажи - документ + товары View

-- RP1c.DocumentSaleView source

CREATE VIEW RP1c.DocumentSaleView
(
    `Realiz_Key` String,
    `Product_Key` String,
    `Zakaz_Key` String,
    `String_Key` UInt16,
    `Count` UInt16,
    `Price` Float64,
    `Sum` Float64,
    `Sum_nds` Float64,
    `Status` String,
    `Document_type` String,
    `Date` DateTime,
    `Number` String,
    `Organization_Key` String,
    `Partner_Key` String,
    `Kontragent_Key` String,
    `Store_Key` String
) AS
SELECT *
FROM
(
    SELECT *
    FROM RP1c.DocumentSaleProduct
) AS Product
INNER JOIN
(
    SELECT
        Ref_Key AS Realiz_Key,

        *
    FROM RP1c.DocumentSale
) AS Document USING (Realiz_Key);

API_RP¶

Не API - данные берутся из различных БД

mongo_SOTRUDNIKI ReplacingMergeTree

-- API_RP.mongo_SOTRUDNIKI definition

CREATE TABLE API_RP.mongo_SOTRUDNIKI
(
    `FIO_FI` String,
    `WORK_DEPARTMENT` String,
    `WORK_BOSS_NAME` String,
    `KONTAKT_PHONE_DOB` String,
    `KONTAKT_PHONE_MOB` String
)
ENGINE = ReplacingMergeTree
ORDER BY FIO_FI

архив 2023-01-13

-- API_RP.mongo_SOTRUDNIKI definition

CREATE TABLE API_RP.mongo_SOTRUDNIKI
(
    `FIO_FI` String,
    `WORK_DEPARTMENT` String,
    `KONTAKT_PHONE_DOB` String,
    `KONTAKT_PHONE_MOB` String
)
ENGINE = MongoDB('10.55.55.5:27017',
                'API',
                'SOTRUDNIKI',
                'user',
                'password');

orion_work_time ReplacingMergeTree

-- API_RP.orion_work_time definition

CREATE TABLE API_RP.orion_work_time
(
    `Date` Date,
    `UserName` String,
    `MinTime` String,
    `MaxTime` String,
    `DeltaTime` String
)
ENGINE = ReplacingMergeTree
ORDER BY (UserName,
Date)

sklad_crpt_RP¶

склад опта. создавалась для честного знака

CHECK MongoDB

-- sklad_crpt_RP.`CHECK` definition

CREATE TABLE sklad_crpt_RP.CHECK
(
    `date` DateTime,
    `datamatrix` String,
    `productName` String,
    `status` String,
    `ownerName` String
)
ENGINE = MongoDB('10.55.55.61:27017',
'061-SKLAD',
'LOG_CRPT',
'user',
'password');

LOG MongoDB

-- sklad_crpt_RP.LOG definition

CREATE TABLE sklad_crpt_RP.LOG
(
    `date` DateTime,
    `article` String,
    `barcode` String,
    `vendor` String,
    `name` String,
    `company` String,
    `datamatrix` String,
    `status` String,
    `order_number` String,
    `sale_order_number` String
)
ENGINE = MongoDB('10.55.55.61:27017',
'061-SKLAD',
'LOG',
'user',
'password');

asterisk_RP¶

телефония опта

cdr MySQL

-- asterisk_RP.cdr definition

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

rp Views¶

для опта прочее

Bug Возвраты - артикулы посточно не работает. таблица нужна? View

-- rp.View_ReturnGood source

CREATE VIEW rp.View_ReturnGood
(
    `return_Key` String,
    `string_Key` UInt16,
    `count` UInt16 DEFAULT 1,
    `price` Float64 DEFAULT 0,
    `sum` Float64 DEFAULT 0,
    `sum_with_nds` Float64 DEFAULT 0,
    `sum_nds` Float64 DEFAULT 0,
    `date_year` UInt16,
    `date_month` UInt8
) AS
SELECT *
FROM
(
    SELECT *
    FROM rp.Document_ReturnGood
) AS ReturnGood

INNER JOIN

(
    SELECT
        Ref_Key AS return_Key,
        date_year,
        date_month
    FROM rp.Document_Return
) AS Return USING (return_Key);

Bug Продажи - артикулы посточно не работает. таблица нужна? View

-- rp.View_SaleGood source

CREATE VIEW rp.View_SaleGood
(
    `realiz_Key` String,
    `good_Key` String,
    `string_Key` UInt16,
    `zakaz_Key` String,
    `count` UInt16 DEFAULT 1,
    `price` Float64 DEFAULT 0,
    `sum` Float64 DEFAULT 0,
    `sum_with_nds` Float64 DEFAULT 0,
    `sum_nds` Float64 DEFAULT 0,
    `date_year` UInt16,
    `date_month` UInt8
) AS
SELECT *
FROM
(
    SELECT *
    FROM rp.Document_SaleGood
) AS SaleGood

INNER JOIN

(
    SELECT
        Ref_Key AS realiz_Key,
        date_year,
        date_month
    FROM rp.Document_Sale
) AS Sale USING (realiz_Key);