Izvēlne
lv:procedures
Šī ir veca dokumenta versija!
--server DIRECTO15B
USE ocra_getz_lv
GO
ALTER PROCEDURE [dbo].[xml_core_SISSE_webshop] AS
DECLARE @klient_kood bigint, @kood bigint, @nimitmp nvarchar(255),@addrtmp nvarchar(255),@konttmp nvarchar(255), @koodid varchar(1000)
declare @ladu nvarchar(32), @seeria nvarchar(32), @objekt nvarchar(32), @s1 int, @s2 int, @webid nvarchar(32), @webtype nvarchar(32)
declare @number int, @maa int, @kmk nvarchar(32), @x int, @kinnitatud int
DECLARE @myyja nvarchar(32), @aeg datetime
declare @key nvarchar(32), @appkey nvarchar(64), @appkey_setting nvarchar(64)
declare @result1 xml, @result2 nvarchar(max)
declare @artikkel nvarchar(32), @id_exist int
declare @kl_kood nvarchar(32), @nimi nvarchar(255), @kustuta int
declare @ag datetime
declare @err nvarchar(32)
select @ag =getdate()
set @key=CONVERT(nvarchar,getdate())
update in_webshop_tell_tellimused set x=@key where x is null
update xml_in_imago_tellimused set x=@key where x is null
set @koodid=''
select @appkey_setting=setting from settings where ID='xmlcore_key'
create table #results
(number nvarchar(32), tyyp nvarchar(32), result int, descr nvarchar(255), submittype nvarchar(32))
--vastuvotmine, kogused
--SELECT * FROM in_webshop_tell_tellimused WHERE number = '2000117'
-- DELETE FROM in_webshop_tell_tellimused WHERE number = '2000117'
-- UPDATE in_webshop_tell_tellimused SET x = null WHERE number = '2000117'
-- EXEC [dbo].[xml_core_SISSE_webshop]
--sales order from E-agent [LV software]
declare tellimused cursor for select number, appkey from in_webshop_tell_tellimused with(nolock) where x=@key
open tellimused
FETCH NEXT FROM tellimused INTO @webid, @appkey
WHILE @@FETCH_STATUS = 0
BEGIN
if ISNULL(@appkey,'')!=ISNULL(@appkey_setting,'')
begin
insert into #results values (@appkey, 'ORDER', 1, 'Incorrect key','Orders')
end
else
begin
select @id_exist=count(*) from tell_tellimused with(nolock) where number=@webid
if @id_exist=0
BEGIN
set @number=@webid
select @maa=maa from kliendid with(nolock) where kood=(select klient_kood from in_webshop_tell_tellimused with(nolock) where number=@number)
insert into tell_tellimused (number, myyja, aeg, klient_kood, arvetasub, tingimus, ladu, klient_nimi, lahetusviis
, kommentaar, valuuta, kurssbv1
,esindaja, telefon, aadress1, aadress2, aadress3
,lahetusaadress1, lahetusaadress2, lahetusaadress3
,klient_nimi_lahetusel
, ts, cu, Field57, objekt
,hinnakiri, lahetusaeg, lisa_field1
)
SELECT @number
,ISNULL(myyja, 'XML')
, aeg
, klient_kood
, isnull((select top 1 arvetasub from kliendid where kood=klient_kood),arvetasub)
, isnull(tingimus,(select TOP 1 tingimus from kliendid with(nolock) where kood=klient_kood))
, isnull(ladu,'GAL')
, isnull(klient_nimi,(select TOP 1 nimi from kliendid with(nolock) where kood=klient_kood))
, isnull(tingimus,(select TOP 1 tingimus from kliendid with(nolock) where kood=klient_kood))
,kommentaar
, 'EUR', 1
, isnull(esindaja,(select TOP 1 kontakt from kliendid with(nolock) where kood=klient_kood))
, isnull(telefon,(select TOP 1 telefon from kliendid with(nolock) where kood=klient_kood))
, isnull(aadress1,(select TOP 1 aadress1 from kliendid with(nolock) where kood=klient_kood))
, isnull(aadress2,(select TOP 1 aadress2 from kliendid with(nolock) where kood=klient_kood))
, isnull(aadress3,(select TOP 1 aadress3 from kliendid with(nolock) where kood=klient_kood))
, isnull(lahaadress1,(select TOP 1 lahaadress1 from kliendid with(nolock) where kood=klient_kood))
, isnull(lahaadress2,(select TOP 1 lahaadress2 from kliendid with(nolock) where kood=klient_kood))
, isnull(lahaadress3,(select TOP 1 lahaadress3 from kliendid with(nolock) where kood=klient_kood))
, isnull(klientnimi_lahetusel,(select TOP 1 nimi_lahetusel from kliendid v where kood=klient_kood))
, GETDATE(), 'XML'
, isnull(email,(select TOP 1 email from kliendid with(nolock) where kood=klient_kood))
, isnull(objekt,(select TOP 1 objekt from kliendid with(nolock) where kood=klient_kood))
,(select TOP 1 hinnakiri from kliendid with(nolock) where kood=klient_kood)
, lahetusaeg
, isnull(lisa_field1,(select TOP 1 sisu from yld_data with(nolock) where kood='TRANSPORTER' AND KLASS='KLIENT' AND kaart=klient_kood))
FROM in_webshop_tell_tellimused with(nolock) where number=@number
-- UPDATE in_tell_tellimused_read SET artikkel = ISNULL((select artikkel from artiklid_hankija_artiklid with(nolock) where hankija_artikkel = in_tell_tellimused_read.artikkel),artikkel) WHERE NOT EXISTS(SELECT top 1 kood FROM artiklid WITH(nolock) WHERE kood = in_tell_tellimused_read.artikkel)
insert into tell_tellimused_read
(number,yhik, artikkel, variant, konto, kogus, field8, yhikuhind, summa, ostuhind, nimetus, kmkood, r_kommentaar, rn, rv)
select @number
,isnull((select top 1 yhik from artiklid where kood=artikkel),'gb')
, artikkel
,variant
, isnull((select konto_myyk from artiklid where kood=in_webshop_tell_tellimused_read.artikkel),(select TOP 1 myyk_eestis from artikliklassid with(nolock) where kood=(select TOP 1 klass from artiklid where kood=artikkel)))
, kogus
, case
when EXISTS (select top 1 ale from mr_hinnavalemid_read with(nolock) where mida='8' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and ((select aeg from tell_tellimused with(nolock) where number=@number) between aeg1 and aeg2 ) and in_webshop_tell_tellimused_read.artikkel=artikkel or mida='8' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and(aeg1 is null and aeg2 is null) and in_webshop_tell_tellimused_read.artikkel=artikkel)
then
('0')
when ((select top 1 ale from mr_hinnavalemid_read where mr_hinnavalemid_read.artikkel =in_webshop_tell_tellimused_read.artikkel and mida='0' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and ((select aeg from tell_tellimused with(nolock) where number=@number) between aeg1 and aeg2 )) is NOT null)
then (select top 1 ale from mr_hinnavalemid_read where mr_hinnavalemid_read.artikkel =in_webshop_tell_tellimused_read.artikkel and mida='0' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and ((select aeg from tell_tellimused with(nolock) where number=@number) between aeg1 and aeg2 ))
when ((select top 1 ale from mr_hinnavalemid_read where mr_hinnavalemid_read.artikkel =in_webshop_tell_tellimused_read.artikkel and mida='0' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and AEG1 IS NULL AND AEG2 IS NULL) is NOT null)
then (select top 1 ale from mr_hinnavalemid_read where mr_hinnavalemid_read.artikkel =in_webshop_tell_tellimused_read.artikkel and mida='0' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) AND aeg1 IS NULL and aeg2 IS NULL )
--when ((select top 1 ale from mr_hinnavalemid_read where mr_hinnavalemid_read.artikkel =in_webshop_tell_tellimused_read.artikkel and mida='0' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and ((select aeg from tell_tellimused with(nolock) where number=@number) between aeg1 and aeg2 )) is null
--and (select top 1 ale from mr_hinnavalemid_read where mr_hinnavalemid_read.artikkel =in_webshop_tell_tellimused_read.artikkel and mida='0' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and (aeg1 is NOT null and aeg2 is NOT null ) AND ((select top 1 AEG from tell_tellimused with(nolock) where number=@number) between AEG1 AND AEG2)) is not null)
--then (select top 1 ale from mr_hinnavalemid_read where mr_hinnavalemid_read.artikkel =in_webshop_tell_tellimused_read.artikkel and mida='0' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and (aeg1 is NOT null and aeg2 is NOT null ) AND ((select top 1 AEG from tell_tellimused with(nolock) where number=@number) between AEG1 AND AEG2))
when
((select top 1 ale from mr_hinnavalemid_read
inner join artiklid ON artiklid.kood=in_webshop_tell_tellimused_read.artikkel
inner join tell_tellimused ON tell_tellimused.number=@number
where mr_hinnavalemid_read.klass=artiklid.klass and mida='0'
and mr_hinnavalemid_read.kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number)
and
((tell_tellimused.aeg) between mr_hinnavalemid_read.aeg1 and mr_hinnavalemid_read.aeg2 )) is not null)
then (select top 1 ale from mr_hinnavalemid_read
inner join artiklid ON artiklid.kood=in_webshop_tell_tellimused_read.artikkel
inner join tell_tellimused ON tell_tellimused.number=@number
where mr_hinnavalemid_read.klass=artiklid.klass and mida='0'
and mr_hinnavalemid_read.kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number)
and
((tell_tellimused.aeg) between mr_hinnavalemid_read.aeg1 and mr_hinnavalemid_read.aeg2 ))
when ((select top 1 ale from mr_hinnavalemid_read
inner join artiklid ON artiklid.kood=in_webshop_tell_tellimused_read.artikkel
inner join tell_tellimused ON tell_tellimused.number=@number
where mr_hinnavalemid_read.klass=artiklid.klass and mida='0'
and mr_hinnavalemid_read.kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number)
and
((tell_tellimused.aeg) between mr_hinnavalemid_read.aeg1 and mr_hinnavalemid_read.aeg2 )) is null and (select top 1 ale from mr_hinnavalemid_read
inner join artiklid ON artiklid.kood=in_webshop_tell_tellimused_read.artikkel
inner join tell_tellimused ON tell_tellimused.number=@number
where mr_hinnavalemid_read.klass=artiklid.klass and mida='0'
and mr_hinnavalemid_read.kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number)
and
(mr_hinnavalemid_read.aeg1 is null and mr_hinnavalemid_read.aeg2 is null )) is not null)
then (select top 1 ale from mr_hinnavalemid_read
inner join artiklid ON artiklid.kood=in_webshop_tell_tellimused_read.artikkel
inner join tell_tellimused ON tell_tellimused.number=@number
where mr_hinnavalemid_read.klass=artiklid.klass and mida='0'
and mr_hinnavalemid_read.kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number)
and
(mr_hinnavalemid_read.aeg1 is null and mr_hinnavalemid_read.aeg2 is null )) END
-- when
-- ((select top 1 ale from mr_hinnavalemid_read
-- inner join artiklid ON artiklid.kood=in_webshop_tell_tellimused_read.artikkel
-- where mr_hinnavalemid_read.klass=artiklid.klass
-- and
-- ((select aeg from tell_tellimused with(nolock) where number=@number) between aeg1 and aeg2)) is null and (select top 1 ale from mr_hinnavalemid_read inner join artiklid ON artiklid.kood=in_webshop_tell_tellimused_read.artikkel where mr_hinnavalemid_read.klass=artiklid.klass and (aeg1 is null and aeg2 is null)) is not null)
-- then (select top 1 ale from mr_hinnavalemid_read inner join artiklid ON artiklid.kood=in_webshop_tell_tellimused_read.artikkel where mr_hinnavalemid_read.klass=artiklid.klass and (aeg1 is null and aeg2 is null))END
-- ,case when EXISTS (select top 1 ale from mr_hinnavalemid_read with(nolock) where mida='8' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and ((select aeg from tell_tellimused with(nolock) where number=@number) between aeg1 and aeg2 ) and in_webshop_tell_tellimused_read.artikkel=artikkel or mida='8' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and(aeg1 is null and aeg2 is null) and in_webshop_tell_tellimused_read.artikkel=artikkel)
-- then
-- '0'
-- ELSE
-- isnull((select top 1 ale from mr_hinnavalemid_read with(nolock) where mida='0' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and ((select aeg from tell_tellimused with(nolock) where number=@number) between aeg1 and aeg2 ) and in_webshop_tell_tellimused_read.artikkel=artikkel or mida='0' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and(aeg1 is null and aeg2 is null) and in_webshop_tell_tellimused_read.artikkel=artikkel),
-- isnull((select top 1 ale from mr_hinnavalemid_read with(nolock) where mida='0' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and ((select aeg from tell_tellimused with(nolock) where number=@number) between aeg1 and aeg2 ) and klass=(select top 1 klass from artiklid where kood=in_webshop_tell_tellimused_read.artikkel)),(select top 1 ale from mr_hinnavalemid_read with(nolock) where mida='0' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and(aeg1 is null and aeg2 is null) and klass=(select top 1 klass from artiklid where kood=in_webshop_tell_tellimused_read.artikkel))))
-- END
-- , isnull((select top 1 ale from mr_hinnavalemid_read where mida='8' and artikkel=(select top 1 artikkel from in_webshop_tell_tellimused_read where in_webshop_tell_tellimused_read.artikkel=artikkel) and (((select aeg from tell_tellimused where number=@number) between aeg1 and aeg2 ) or (aeg1 is null and aeg2 is null)) and kood=(select hinnakiri from tell_tellimused where number=@number)), 0)
-- , isnull(hind,(select dbo.get_hind6(artikkel,(SELECT top 1 hinnakiri from tell_tellimused INNER JOIN mr_hinnavalemid_read ON mr_hinnavalemid_read.artikkel=artikkel and mida='8' and (((select aeg from tell_tellimused where number=@number) between aeg1 and aeg2 ) or (aeg1 is null and aeg2 is null)) where number=@number), null, null, null,null, null,null, null, null,null,0)))
,case when EXISTS (select top 1 ale from mr_hinnavalemid_read with(nolock) where mida='8'
and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number)
and ((select aeg from tell_tellimused with(nolock) where number=@number) between aeg1 and aeg2 )
and in_webshop_tell_tellimused_read.artikkel=artikkel
or mida='8' and kood=(select top 1 hinnakiri from tell_tellimused with(nolock) where number=@number) and(aeg1 is null and aeg2 is null) and in_webshop_tell_tellimused_read.artikkel=artikkel)
then
(select dbo.get_hind6(artikkel,(SELECT top 1 hinnakiri from tell_tellimused where number=@number), null, null ,null,null, null,null, (select top 1 aeg from tell_tellimused where number=@number), null,null,0))
ELSE
(select top 1 baashind from artiklid where kood=in_webshop_tell_tellimused_read.artikkel)
END
, kogus*isnull(hind,(select top 1 baashind from artiklid with(nolock) where kood=artikkel))*(100-ISNULL(ale,0))/100
, dbo.kesk_hind(artikkel,getdate())
, isnull(nimetus,(select top 1 nimi from artiklid with(nolock) where kood=artikkel))
, isnull((select top 1 kmkood from artiklid with(nolock) where kood=artikkel),(select top 1 kmk_eesti from artikliklassid with(nolock) where kood=(select TOP 1 klass from artiklid where kood=artikkel)))
,kommentaar, ROW_NUMBER() OVER(ORDER BY rn) , ROW_NUMBER() OVER(ORDER BY rn )
FROM in_webshop_tell_tellimused_read with(nolock) where number=@number
update tell_tellimused_read
set kmkood=ISNULL((select top 1 KMkood from artiklid with(nolock) where kood=tell_tellimused_read.artikkel),(select kmk_eesti from artikliklassid with(nolock) where kood=(select top 1 klass from artiklid with(nolock) where kood=tell_tellimused_read.artikkel)))
where number=@number and kmkood is null
UPDATE tell_tellimused_read set tkkm = dbo.summaKM (yhikuhind,kmkood), rsum = dbo.summaKM(summa,kmkood) where number=@number
update tell_tellimused_read
set konto=ISNULL((select top 1 konto_myyk from artiklid with(nolock) where kood=tell_tellimused_read.artikkel),(select myyk_eestis from artikliklassid with(nolock) where kood=(select top 1 klass from artiklid with(nolock) where kood=tell_tellimused_read.artikkel)))
where number=@number and konto is null
--EXEC dbo.tell_tellimused_renum @number
EXEC dbo.arvuta_tellimus @number
update tell_tellimused_read set rv=rn where number=@number
exec hooldus_vaba 'tellimus',@number
set @koodid=@koodid+convert(nvarchar,@number)
insert into kliendid (kood, nimi, aadress1, aadress2, aadress3, nimi_lahetusel, lahaadress1, lahaadress2, lahaadress3
, kontakt, email, telefon
,klient_tyyp, klass, regnr, kliendikaart
,cu,ts)
select top 1
klient_kood, klient_nimi, aadress1, aadress2, aadress3, klientnimi_lahetusel, lahaadress1, lahaadress2, lahaadress3
, esindaja,email,telefon
,klient_tyyp, klient_klass, klient_regnr, klient_kliendikaart
,'XML',GETDATE()
FROM in_webshop_tell_tellimused with(nolock) where number=@number
and ISNULL(klient_kood,'')!=''
and klient_kood not in (select kood from kliendid with(nolock))
insert into #results values (@webid, 'ORDER', 0, 'OK','Orders')
end
ELSE
BEGIN
insert into #results values (@webid, 'ORDER', 2, 'Duplicate','Orders')
END
set @x=@x+1
end
FETCH NEXT FROM tellimused INTO @webid, @appkey
if @@FETCH_STATUS = 0 set @koodid=@koodid+';'
END
CLOSE tellimused
DEALLOCATE tellimused
--cleanup
--delete from in_webshop_tell_tellimused_klient where number in (select number from in_webshop_tell_tellimused where x=@key)
delete from in_webshop_tell_tellimused_read where number in (select number from in_webshop_tell_tellimused where x=@key)
delete from in_webshop_tell_tellimused where x=@key
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--sales orders from imago [EE software]
--vastuvotmine, kogused
declare tellimused cursor for select number, appkey from xml_in_imago_tellimused where x=@key
open tellimused
FETCH NEXT FROM tellimused INTO @webid, @appkey
WHILE @@FETCH_STATUS = 0
BEGIN
if ISNULL(@appkey,'')!=ISNULL(@appkey_setting,'')
begin
insert into #results values (@appkey, 'ORDER', 1, 'Incorrect key','Orders')
end
else
begin
select @id_exist=count(*) from tell_tellimused where kliendi_tellimus=convert(nvarchar(max),@webid)
if @id_exist=0
BEGIN
--set @number=@webid
EXEC dbo.get_dok_number @moodul='tellimus',@seeria='DOC',@NUMBER = NULL, @cu='xmlcore', @aeg=@ag, @keel = 'default', @num =@number OUTPUT, @err = @err OUTPUT
update tell_tellimused set
myyja=iif(isnull(z.myyja,'')!='',z.myyja,iif(isnull((select myyja from kliendid where kood=z.arvetasub),'')!='',(select myyja from kliendid where kood=z.arvetasub),iif(isnull((select myyja from kliendid where kood=z.klient_kood),'')!='',(select myyja from kliendid where kood=z.klient_kood),'XML'))),
aeg=z.aeg,
klient_kood=z.klient_kood,
arvetasub=z.arvetasub,
tingimus=isnull(z.tingimus,(select tingimus from kliendid where kood=z.klient_kood)),
ladu=isnull(z.ladu,'GAL'),
klient_nimi=isnull(z.klient_nimi,(select nimi from kliendid where kood=z.klient_kood)),
lahetusviis=isnull(z.tingimus,(select tingimus from kliendid where kood=z.klient_kood)),
kommentaar=z.kommentaar,
valuuta='EUR',
kurssbv1=1,
esindaja=isnull(z.esindaja,(select kontakt from kliendid where kood=z.klient_kood)),
telefon=isnull(z.telefon,(select telefon from kliendid where kood=z.klient_kood)),
aadress1=isnull(z.aadress1,(select aadress1 from kliendid where kood=z.klient_kood)),
aadress2=isnull(z.aadress2,(select aadress2 from kliendid where kood=z.klient_kood)),
aadress3=isnull(z.aadress3,(select aadress3 from kliendid where kood=z.klient_kood)),
lahetusaadress1=isnull(z.lahaadress1,(select lahaadress1 from kliendid where kood=z.klient_kood)),
lahetusaadress2=isnull(z.lahaadress2,(select lahaadress2 from kliendid where kood=z.klient_kood)),
lahetusaadress3=isnull(z.lahaadress3,(select lahaadress3 from kliendid where kood=z.klient_kood)),
klient_nimi_lahetusel=isnull(z.klientnimi_lahetusel,(select nimi_lahetusel from kliendid where kood=z.klient_kood)),
ts=getdate(),
cu='XML',
Field57=isnull(z.email,(select email from kliendid where kood=z.klient_kood)),
objekt=isnull(z.objekt,(select objekt from kliendid where kood=z.klient_kood)),
hinnakiri=(select hinnakiri from kliendid where kood=z.klient_kood),
kliendi_tellimus=convert(nvarchar(max),@webid)
from (select * from xml_in_imago_tellimused where number=@webid)z
where tell_tellimused.number=@number
select @maa=maa from kliendid where kood=(select klient_kood from xml_in_imago_tellimused where number=@number)
/*
insert into tell_tellimused (number, myyja, aeg, klient_kood, arvetasub, tingimus, ladu, klient_nimi, lahetusviis
, kommentaar, valuuta, kurssbv1
,esindaja, telefon, aadress1, aadress2, aadress3
,lahetusaadress1, lahetusaadress2, lahetusaadress3
,klient_nimi_lahetusel
, ts, cu, Field57, objekt
,hinnakiri
)
SELECT @number
,iif(isnull('myyja','')!='',myyja,iif(isnull((select myyja from kliendid where kood=xml_in_imago_tellimused.arvetasub),'')!='',(select myyja from kliendid where kood=xml_in_imago_tellimused.arvetasub),iif(isnull((select myyja from kliendid where kood=xml_in_imago_tellimused.klient_kood),'')!='',(select myyja from kliendid where kood=xml_in_imago_tellimused.klient_kood),'XML')))
, aeg
, klient_kood
, arvetasub
, isnull(tingimus,(select tingimus from kliendid where kood=klient_kood))
, isnull(ladu,'')
, isnull(klient_nimi,(select nimi from kliendid where kood=klient_kood))
, isnull(tingimus,(select tingimus from kliendid where kood=klient_kood))
,kommentaar
, 'EUR', 1
, isnull(esindaja,(select kontakt from kliendid where kood=klient_kood))
, isnull(telefon,(select telefon from kliendid where kood=klient_kood))
, isnull(aadress1,(select aadress1 from kliendid where kood=klient_kood))
, isnull(aadress2,(select aadress2 from kliendid where kood=klient_kood))
, isnull(aadress3,(select aadress3 from kliendid where kood=klient_kood))
, isnull(lahaadress1,(select lahaadress1 from kliendid where kood=klient_kood))
, isnull(lahaadress2,(select lahaadress2 from kliendid where kood=klient_kood))
, isnull(lahaadress3,(select lahaadress3 from kliendid where kood=klient_kood))
, isnull(klientnimi_lahetusel,(select nimi_lahetusel from kliendid where kood=klient_kood))
, GETDATE(), 'XML'
, isnull(email,(select email from kliendid where kood=klient_kood))
, isnull(objekt,(select objekt from kliendid where kood=klient_kood))
,(select hinnakiri from kliendid where kood=klient_kood)
FROM xml_in_imago_tellimused where number=@number
*/
select * from in_xml_log order by rn desc
insert into tell_tellimused_read
(number, artikkel, variant, konto, kogus, field8, yhikuhind, summa, ostuhind, nimetus, kmkood, r_kommentaar, rn, rv)
select @number, artikkel,variant, (select myyk_eestis from artikliklassid where kood=(select klass from artiklid where kood=artikkel)), kogus
, ale
, isnull(hind,(select baashind from artiklid where kood=artikkel))
, kogus*isnull(hind,(select baashind from artiklid where kood=artikkel))*(100-ISNULL(ale,0))/100
, dbo.kesk_hind(artikkel,getdate()), nimetus
, (select kmk_eesti from artikliklassid where kood=(select klass from artiklid where kood=artikkel))
,kommentaar, rn, rn
FROM xml_in_imago_tellimused_read where number=@webid
update tell_tellimused_read
set kmkood=ISNULL((select kmkood from artiklid where kood=tell_tellimused_read.artikkel),(select kmk_eesti from artikliklassid where kood=(select klass from artiklid where kood=tell_tellimused_read.artikkel)))
where number=@number and kmkood is null
UPDATE tell_tellimused_read set tkkm = dbo.summaKM (yhikuhind,kmkood), rsum = dbo.summaKM(summa,kmkood) where number=@number
update tell_tellimused_read
set konto=ISNULL((select konto_myyk from artiklid where kood=tell_tellimused_read.artikkel),(select myyk_eestis from artikliklassid where kood=(select klass from artiklid where kood=tell_tellimused_read.artikkel)))
where number=@number and konto is null
EXEC dbo.tell_tellimused_renum @number
EXEC dbo.arvuta_tellimus @number
update tell_tellimused_read set rv=rn where number=@number
exec hooldus_vaba 'tellimus',@number
set @koodid=@koodid+convert(nvarchar,@number)
insert into kliendid (kood, nimi, aadress1, aadress2, aadress3, nimi_lahetusel, lahaadress1, lahaadress2, lahaadress3
, kontakt, email, telefon
,klient_tyyp, klass, regnr, kliendikaart
,cu,ts)
select top 1
klient_kood, klient_nimi, aadress1, aadress2, aadress3, klientnimi_lahetusel, lahaadress1, lahaadress2, lahaadress3
, esindaja,email,telefon
,klient_tyyp, klient_klass, klient_regnr, klient_kliendikaart
,'XML',GETDATE()
FROM xml_in_imago_tellimused where number=@webid
and ISNULL(klient_kood,'')!=''
and klient_kood not in (select kood from kliendid)
insert into #results values (@webid, 'ORDER', 0, 'OK','Orders')
end
ELSE
BEGIN
insert into #results values (@webid, 'ORDER', 2, 'Duplicate','Orders')
END
set @x=@x+1
end
FETCH NEXT FROM tellimused INTO @webid, @appkey
if @@FETCH_STATUS = 0 set @koodid=@koodid+';'
END
CLOSE tellimused
DEALLOCATE tellimused
--cleanup
--delete from xml_in_imago_tellimused_klient where number in (select number from xml_in_imago_tellimused where x=@key)
delete from xml_in_imago_tellimused_read where number in (select number from xml_in_imago_tellimused where x=@key)
delete from xml_in_imago_tellimused where x=@key
---------------------------------------------------------------------------------------------------------------------------------------------------------
--tulemused valja
if (select COUNT(*) from #results)=0
insert into #results values (NULL, 'GENERAL ERROR', 99, 'No processable data found',NULL)
select @result1=
(select result as "@Type", descr as "@Desc", number as "@docid", tyyp as "@doctype", submittype as "@submit"
from #results for xml path ('Result'))
set @result2='<?xml version="1.0" encoding="UTF-8"?><results>'+CONVERT(nvarchar(max),@result1)+'</results>'
select @result2
drop table #results
delete from in_xml_log where datediff(day,ts_inserted,getdate())>=5
