Программа iNETsHOP - обработка, сравнение, анализ прайс листов поставщиков, создание каталога товаров интернет магазина Форум - Программа iNETsHOP - Работа с программой - Автоматический импорт прайс-листов


http://inetshop.in.ua/index.php?p=showtopic&toid=284&pp=15&page=1&area=1&print_post=2282
07.11.2016 20:11

support


Support


Количество сообщений   1143
Зарегистрирован:   11-04-2008, 19:11:57
Скрипт удаления дубликатов товаров из прайса
Такой дополнительный SQL-запрос для схемы импорта может выглядеть вот так:

Код

DECLARE @l_sNAME varchar(8000), @l_sSID varchar(8000), @l_iID int;
DECLARE cDublicates CURSOR LOCAL FORWARD_ONLY LOCAL FOR
select distinct td.G_NAME, td.G_SID
from
(
select t2.cnt, t2.G_NAME, t2.G_SID
from
(
select count(*) as cnt, t.G_NAME, t.G_SID
from
(
select IsNull(UPPER(LTRIM(RTRIM(CAST(prc.NAME as varchar(8000))))), '') as G_NAME, IsNull(UPPER(LTRIM(RTRIM(CAST(prc.SID as varchar(8000))))), '') as G_SID
from #TMP_PRICE prc
) t
where t.G_SID = ''
group by t.G_NAME, t.G_SID
) t2
where t2.cnt > 1
) td FOR READ ONLY;

OPEN cDublicates;
FETCH NEXT FROM cDublicates INTO @l_sNAME, @l_sSID;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
select @l_iID =
(
select TOP 1 ID from
(
select ID, name, SID, case when IsNumeric(price.PRICE) = 1 then CAST(REPLACE(price.PRICE, ',', '.') as money) else 0 end as PRICE
from
(
select ID,
IsNull(UPPER(LTRIM(RTRIM(CAST(NAME as varchar(8000))))), '') as name,
IsNull(UPPER(LTRIM(RTRIM(CAST(SID as varchar(8000))))), '') as SID,
IsNull(UPPER(LTRIM(RTRIM(CAST(PRICE as varchar(8000))))), '0') as PRICE
from #TMP_PRICE
) price
where price.NAME = @l_sNAME
and price.SID = @l_sSID
) t
order by PRICE DESC
);

DELETE FROM #TMP_PRICE
WHERE (ID <> @l_iID)
and IsNull(UPPER(LTRIM(RTRIM(CAST(NAME as varchar(8000))))), '') = @l_sNAME
and IsNull(UPPER(LTRIM(RTRIM(CAST(SID as varchar(8000))))), '') = '';

FETCH NEXT FROM cDublicates INTO @l_sNAME, @l_sSID;
END

CLOSE cDublicates;
DEALLOCATE cDublicates;






DECLARE cDublicates CURSOR LOCAL FORWARD_ONLY LOCAL FOR
select distinct td.G_NAME, td.G_SID
from
(
select t2.cnt, IsNull(UPPER(LTRIM(RTRIM(CAST(t3.NAME as varchar(8000))))), '') as G_NAME, t2.G_SID
from
(
select count(*) as cnt, t.G_SID
from
(
select IsNull(UPPER(LTRIM(RTRIM(CAST(prc.SID as varchar(8000))))), '') as G_SID
from #TMP_PRICE prc
) t
group by t.G_SID
) t2, #TMP_PRICE t3
where t2.cnt > 1
and t2.G_SID <> ''
and t2.G_SID = IsNull(UPPER(LTRIM(RTRIM(CAST(t3.SID as varchar(8000))))), '')
) td FOR READ ONLY;

OPEN cDublicates;
FETCH NEXT FROM cDublicates INTO @l_sNAME, @l_sSID;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
select @l_iID =
(
select TOP 1 ID from
(
select ID, name, SID, case when IsNumeric(price.PRICE) = 1 then CAST(REPLACE(price.PRICE, ',', '.') as money) else 0 end as PRICE
from
(
select ID,
IsNull(UPPER(LTRIM(RTRIM(CAST(NAME as varchar(8000))))), '') as name,
IsNull(UPPER(LTRIM(RTRIM(CAST(SID as varchar(8000))))), '') as SID,
IsNull(UPPER(LTRIM(RTRIM(CAST(PRICE as varchar(8000))))), '0') as PRICE
from #TMP_PRICE
) price
where price.NAME = @l_sNAME
and price.SID = @l_sSID
) t
order by PRICE DESC
);

DELETE FROM #TMP_PRICE
WHERE (ID <> @l_iID)
-- and IsNull(UPPER(LTRIM(RTRIM(CAST(NAME as varchar(8000))))), '') = @l_sNAME
and IsNull(UPPER(LTRIM(RTRIM(CAST(SID as varchar(8000))))), '') = @l_sSID;

FETCH NEXT FROM cDublicates INTO @l_sNAME, @l_sSID;
END

CLOSE cDublicates;
DEALLOCATE cDublicates;


Его нужно вставить в схему импорта в поле "SQL-скрипт после чтения данных"