Переход с Каяла-Софт

Для того, чтобы перенести данные о товарах достаточно выгрузить данные из Каяла-софт в 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' AND ISNULL(parent1, 0) = 0 UNION ALL SELECT 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, CASE WHEN GoodType = 0 THEN 1 WHEN GoodType = 1 THEN 3 ELSE 1 END AS 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 XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),'') AS OtherBarcodes, ISNULL(price1.price, 0) AS PriceIN, ISNULL(price2.price, 0) AS PriceOut, CASE WHEN markedgood = 'true' THEN 3 ELSE 0 END AS 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 = ( SELECT MAX(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::text AS name, COALESCE(parent1, 0) AS parent1 FROM lst_goods WHERE isgroup = TRUE AND COALESCE(parent1, 0) = 0 UNION ALL SELECT gg.code AS id, gg.name AS namechild, (g.name || '/' || gg.name)::text AS 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, CASE WHEN goodtype = 0 THEN 1 WHEN goodtype = 1 THEN 3 ELSE 1 END AS goodtype, lst_units.name AS measuremain, COALESCE(cte.name, '') AS barcode, COALESCE(( SELECT string_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, CASE WHEN markedgood = TRUE THEN 3 ELSE 0 END AS 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 = ( 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 = 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 AND COALESCE(parent1, 0) = 0 UNION ALL SELECT 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, CASE WHEN goodtype = 0 THEN 1 WHEN goodtype = 1 THEN 3 ELSE 1 END AS goodtype, lst_units.name AS measuremain, IFNULL(cte.name, '') AS barcode, IFNULL(( SELECT GROUP_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, CASE WHEN markedgood = 1 THEN 3 ELSE 0 END AS 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 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 = ' + 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 := ''; BEGIN FOR rec IN SELECT 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;

', rec.store ); END LOOP; RAISE NOTICE '%', sql; EXECUTE sql; END $$;

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;

Обратите внимание, перед выполнением запросов рекомендуется закрыть смену в Каяла-Софт, чтобы данные об остатках товаров были актуальными.

Если возникли трудности с переносом данных, обратитесь к нашим партнёрам.