Для того, чтобы перенести данные о товарах достаточно выгрузить данные из Каяла-софт в MS Excel и загрузить их в MiDays UNO. Если по каким-то причинам выгрузка данных в Excel невозможна, то можно выполнить SQL запрос к базе данных Каяла-софт в MS SQL Management Studio (для MS SQL и LocalDB). PGAdmin (для PostgreSQL).
Перенос товаров.
Для переноса товаров скопируйте SQL запрос и выполните его.
SQL Code Tabs (Goods)
SQL
PostgreSQL
SQLite
--ГруппыWITH GroupHierarchy AS (
SELECT code AS ID, Name AS NameChild, CAST(name AS nvarchar(max)) AS Name, ISNULL(parent1, 0) AS parent1
FROM lst_goods
WHERE isgroup = 'true'ANDISNULL(parent1, 0) = 0
UNION ALLSELECT gg.code AS ID, gg.Name, CAST(g.Name + '/' + gg.name AS nvarchar(max)) AS Name, gg.parent1
FROM lst_goods gg
INNER JOIN GroupHierarchy g ON gg.parent1 = g.ID
WHERE isgroup = 'true'
),
--Штрихкоды
cte AS (
SELECT owner, name, ROW_NUMBER() OVER (PARTITION BY owner ORDER BY code) AS rn
FROM lst_barcodes
)
--товарыSELECT
lst_goods.code AS Code,
lst_goods.name AS DisplayName,
GroupHierarchy.Name AS GoodGroup,
CASEWHEN GoodType = 0 THEN 1
WHEN GoodType = 1 THEN 3
ELSE 1
ENDAS GoodType,
lst_units.name AS MeasureMain,
ISNULL(cte.name,'') AS Barcode,
ISNULL(STUFF((
SELECT',' + c2.name
FROM cte c2
WHERE c2.owner = cte.owner AND c2.name <> cte.name
FOR XMLPATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),'') AS OtherBarcodes,
ISNULL(price1.price, 0) AS PriceIN,
ISNULL(price2.price, 0) AS PriceOut,
CASEWHEN markedgood = 'true'THEN 3 ELSE 0 ENDAS TypeMark
FROM lst_goods
LEFT JOIN lst_units ON lst_goods.unit = lst_units.code
LEFT JOIN GroupHierarchy ON GroupHierarchy.ID = lst_goods.parent1
LEFT JOIN cte ON cte.owner = lst_goods.code AND cte.rn = 1
LEFT JOIN reg_prices_income_last price1 ON price1.good = lst_goods.code
LEFT JOIN (
SELECT good, price
FROM reg_prices_outcome_last t1
WHERE maxdate = (
SELECTMAX(maxdate)
FROM reg_prices_outcome_last t2
WHERE t2.good = t1.good
)
) price2 ON price2.good = lst_goods.code
WHERE lst_goods.isgroup = 'false';
-- ГруппыWITH RECURSIVE GroupHierarchy AS (
SELECT
code AS id,
name AS namechild,
name::textAS name,
COALESCE(parent1, 0) AS parent1
FROM lst_goods
WHERE isgroup = TRUEANDCOALESCE(parent1, 0) = 0
UNION ALLSELECT
gg.code AS id,
gg.name AS namechild,
(g.name || '/' || gg.name)::textAS name,
gg.parent1
FROM lst_goods gg
INNER JOIN GroupHierarchy g ON gg.parent1 = g.id
WHERE gg.isgroup = TRUE),
-- Штрихкоды
cte AS (
SELECT
owner,
name,
ROW_NUMBER() OVER (PARTITION BY owner ORDER BY code) AS rn
FROM lst_barcodes)
-- ТоварыSELECT
lst_goods.code AS code,
lst_goods.name AS displayname,
gh.name AS goodgroup,
CASEWHEN goodtype = 0 THEN 1
WHEN goodtype = 1 THEN 3
ELSE 1
ENDAS goodtype,
lst_units.name AS measuremain,
COALESCE(cte.name, '') AS barcode,
COALESCE((
SELECTstring_agg(c2.name, ',')
FROM cte c2
WHERE c2.owner = cte.owner AND c2.name <> cte.name
), '') AS otherbarcodes,
COALESCE(price1.price, 0) AS pricein,
COALESCE(price2.price, 0) AS priceout,
CASEWHEN markedgood = TRUETHEN 3 ELSE 0 ENDAS typemark
FROM lst_goods
LEFT JOIN lst_units ON lst_goods.unit = lst_units.code
LEFT JOIN GroupHierarchy gh ON gh.id = lst_goods.parent1
LEFT JOIN cte ON cte.owner = lst_goods.code AND cte.rn = 1
LEFT JOIN reg_prices_income_last price1 ON price1.good = lst_goods.code
LEFT JOIN (
SELECT good, price
FROM reg_prices_outcome_last t1
WHERE maxdate = (
SELECTMAX(t2.maxdate)
FROM reg_prices_outcome_last t2
WHERE t2.good = t1.good
)) price2 ON price2.good = lst_goods.code
WHERE lst_goods.isgroup = FALSE;
-- ГруппыWITH RECURSIVE GroupHierarchy(id, namechild, name, parent1) AS (
SELECT
code AS id,
name AS namechild,
name AS name,
COALESCE(parent1, 0) AS parent1
FROM lst_goods
WHERE isgroup = 1 ANDCOALESCE(parent1, 0) = 0
UNION ALLSELECT
gg.code AS id,
gg.name AS namechild,
g.name || '/' || gg.name AS name,
gg.parent1
FROM lst_goods gg
JOIN GroupHierarchy g ON gg.parent1 = g.id
WHERE gg.isgroup = 1),
-- Штрихкоды
cte AS (
SELECT
owner,
name,
ROW_NUMBER() OVER (PARTITION BY owner ORDER BY code) AS rn
FROM lst_barcodes)
-- ТоварыSELECT
lst_goods.code AS code,
lst_goods.name AS displayname,
gh.name AS goodgroup,
CASEWHEN goodtype = 0 THEN 1
WHEN goodtype = 1 THEN 3
ELSE 1
ENDAS goodtype,
lst_units.name AS measuremain,
IFNULL(cte.name, '') AS barcode,
IFNULL((
SELECTGROUP_CONCAT(c2.name, ',')
FROM cte c2
WHERE c2.owner = cte.owner AND c2.name <> cte.name
), '') AS otherbarcodes,
IFNULL(price1.price, 0) AS pricein,
IFNULL(price2.price, 0) AS priceout,
CASEWHEN markedgood = 1 THEN 3 ELSE 0 ENDAS typemark
FROM lst_goods
LEFT JOIN lst_units ON lst_goods.unit = lst_units.code
LEFT JOIN GroupHierarchy gh ON gh.id = lst_goods.parent1
LEFT JOIN cte ON cte.owner = lst_goods.code AND cte.rn = 1
LEFT JOIN reg_prices_income_last price1 ON price1.good = lst_goods.code
LEFT JOIN (
SELECT t1.good, t1.price
FROM reg_prices_outcome_last t1
WHERE t1.maxdate = (
SELECT MAX(t2.maxdate)
FROM reg_prices_outcome_last t2
WHERE t2.good = t1.good
)) price2 ON price2.good = lst_goods.code
WHERE lst_goods.isgroup = 0;
В результате выполнения запроса у вас будет таблица. Скачайте шаблон Excel файла. Скопируйте всю таблицу и как есть вставьте в Excel файл на страницу Main. На странице Options укажите номер последней строки. Импортируйте товары в MiDays UNO (Подробнее о том, как загрузить данные из Excel читайте в статье Импорт данных из Excel)
Перенос объектов
Для переноса объектов скопируйте и выполните запрос. MS SQL. В результате этого запроса у вас будет таблица с новыми запросами. Скопируйте их и выполните. PostgreSQL, SQLite В результате выполнения этого запроса на панели сообщений вы увидите несколько запросов. Скопируйте и выполните их.
SQL Code Tabs (Objects)
SQL
PostgreSQL
SQLite
SELECT'INSERT INTO Objects ([Code], [DisplayName], [PrintName], [AddressCode], [Address], [TaxNumber], [UniqueCode], [DocPrefix], [Note], [OrganizationID], [GroupID], [PriceGroupID], [Status]) VALUES ('''
+ CAST(Code AS nvarchar(50))
+ ''', ''' + Name
+ ''', ''' + Name
+ ''', '''', '''', '''', '''', '''
+ 'K' + RIGHT('0' + CAST
(ROW_NUMBER() OVER
(ORDER BY code) + 1 AS nvarchar(2)), 2) + ''', '''', 1, 1, 2, 1)'FROM lst_subdivisions
WHERE CODE <> 1
SELECT'INSERT INTO Objects ("Code", "DisplayName", "PrintName", "AddressCode", "Address", "TaxNumber", "UniqueCode", "DocPrefix", "Note", "OrganizationID", "GroupID", "PriceGroupID", "Status") VALUES (''' ||
code || ''', ''' || name || ''', ''' || name || ''', '''', '''', '''', '''', ''' ||'K' || LPAD((ROW_NUMBER() OVER (ORDER BY code) + 1)::text, 2, '0') ||
''', '''', 1, 1, 2, 1);'FROM lst_subdivisions
WHERE code <> 1;
SELECT'INSERT INTO Objects ("Code", "DisplayName", "PrintName", "AddressCode", "Address", "TaxNumber", "UniqueCode", "DocPrefix", "Note", "OrganizationID", "GroupID", "PriceGroupID", "Status") VALUES (''' ||
code || ''', ''' || name || ''', ''' || name || ''', '''', '''', '''', '''', ''' ||'K' || printf('%02d', ROW_NUMBER() OVER (ORDER BY code) + 1) ||
''', '''', 1, 1, 2, 1);'FROM lst_subdivisions
WHERE code <> 1;
Перенос остатков
Для переноса остатков скопируйте и выполните запрос. MS SQL. В результате выполнения этого запроса у вас будет одна (или несколько) таблиц (зависит от количества объектов в базе данных. PostgreSQL, SQLite В результате выполнения этого запроса на панели сообщений вы увидите один или несколько новых запросов. Выполните их по отдельности, получите таблицы с данными. Скачайте шаблон файла. Каждую таблицу скопируйте в Excel файл на страницу Main. На странице Options укажите номер последней строки. Импортируйте как операцию (сокращённо). Тип операции выберите "Приход". Обратите внимание! Каждую таблицу импортируйте отдельно, выбирая нужные объекты!
SQL Code Tabs (Operations)
SQL
PostgreSQL
SQLite
DECLARE@sql NVARCHAR(MAX) = N'';
SELECT@sql = @sql +
'SELECT g.code, g.name, amount/quantity as PriceIN, MAX(gp.price) as priceout, quantity FROM reg_goods_rests_view storeleft join reg_prices_outcome_last gp on gp.good = store.goodLeft join lst_goods g on g.code = store.goodWHERE Store = ' + CAST(Store AS NVARCHAR(10)) + ' group by g.code, g.name, amount/quantity, quantity;'FROM (SELECT DISTINCT Store FROM reg_goods_rests_view) s;
PRINT@sql; -- можно посмотреть, что получитсяEXEC sp_executesql @sql;
DO$$DECLARE
rec RECORD;
sql TEXT := '';
BEGINFOR rec INSELECT DISTINCT store FROM reg_goods_rests_view LOOP
sql := sql || format(
'SELECT g.code, g.name, amount/quantity AS pricein, MAX(gp.price) AS priceout, quantity
FROM reg_goods_rests_view store
LEFT JOIN reg_prices_outcome_last gp ON gp.good = store.good
LEFT JOIN lst_goods g ON g.code = store.good
WHERE store.store = %s
GROUP BY g.code, g.name, amount/quantity, quantity;
WITH stores AS (
SELECT DISTINCT Store
FROM reg_goods_rests_view)
SELECT'SELECT g.code, g.name, amount/quantity AS PriceIN, MAX(gp.price) AS PriceOut, quantity ' ||
'FROM reg_goods_rests_view store ' ||
'LEFT JOIN reg_prices_outcome_last gp ON gp.good = store.good ' ||
'LEFT JOIN lst_goods g ON g.code = store.good ' ||
'WHERE store.Store = ' || Store || ' ' ||
'GROUP BY g.code, g.name, amount/quantity, quantity;'AS sql_text
FROM stores;
Перенос контрагентов
Для переноса контрагентов скопируйте SQL запрос и выполните его. В результате выполнения запроса у вас будет таблица. Скачайте шаблон файла. Скопируйте всю таблицу и как есть вставьте в Excel файл на страницу Main. На странице Options укажите номер последней строки.
SQL Code Tabs (Partners)
SQL
PostgreSQL
SQLite
SELECT
lst1.code,
lst1.Name,
lst1.full_name,
ISNULL(lst1.address_fact, ''),
ISNULL(lst1.address_legal, ''),
ISNULL(lst1.inn, ''),
ISNULL(lst1.cpp, ''),
ISNULL(lst1.phone, ''),
ISNULL(lst1.email, ''),
ISNULL(lst1.card_number, ''),
ISNULL(lst1.comment, ''),
ISNULL(lst2.name, 'Партнёры')
FROM lst_contractors lst1
LEFT JOIN lst_contractors lst2
ON lst2.code = lst1.parent1 AND lst2.isgroup = 'true'
WHERE lst1.isgroup = 'false';
SELECT
lst1.code,
lst1.name,
lst1.full_name,
COALESCE(lst1.address_fact, '') AS address_fact,
COALESCE(lst1.address_legal, '') AS address_legal,
COALESCE(lst1.inn, '') AS inn,
COALESCE(lst1.cpp, '') AS cpp,
COALESCE(lst1.phone, '') AS phone,
COALESCE(lst1.email, '') AS email,
COALESCE(lst1.card_number, '') AS card_number,
COALESCE(lst1.comment, '') AS comment,
COALESCE(lst2.name, 'Партнёры') AS parent_name
FROM lst_contractors lst1
LEFT JOIN lst_contractors lst2
ON lst2.code = lst1.parent1 AND lst2.isgroup = 'true'
WHERE lst1.isgroup = 'false';
SELECT
lst1.code,
lst1.name,
lst1.full_name,
COALESCE(lst1.address_fact, '') AS address_fact,
COALESCE(lst1.address_legal, '') AS address_legal,
COALESCE(lst1.inn, '') AS inn,
COALESCE(lst1.cpp, '') AS cpp,
COALESCE(lst1.phone, '') AS phone,
COALESCE(lst1.email, '') AS email,
COALESCE(lst1.card_number, '') AS card_number,
COALESCE(lst1.comment, '') AS comment,
COALESCE(lst2.name, 'Партнёры') AS parent_name
FROM lst_contractors AS lst1
LEFT JOIN lst_contractors AS lst2
ON lst2.code = lst1.parent1 AND lst2.isgroup = 1
WHERE lst1.isgroup = 0;
Обратите внимание, перед выполнением запросов рекомендуется закрыть смену в Каяла-Софт, чтобы данные об остатках товаров были актуальными.
Если возникли трудности с переносом данных, обратитесь к нашим партнёрам.