Izvēlne
lv:procedures
Šī ir veca dokumenta versija!
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
