http://www.erpne.org/ Copyright (c) 2006-2013 Web Wiz Forums - All Rights Reserved. Sun, 19 Apr 2026 14:45:15 +0000 Tue, 21 Jul 2015 15:20:30 +0000 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 11.10 360 www.erpne.org/RSS_post_feed.asp?TID=4167 http://www.erpne.org/forum_images/logotype.png http://www.erpne.org/ http://www.erpne.org/otomatik-kur-guncelleme-mssql_topic4167_post22066.html#22066 Yazar: rhan
Konu: 4167
Gönderim Zamanı: 21 Temmuz 2015 Saat 15:20

INNOVA_GUNLUK_KURU_GETIR prosedürünü joblara ekleyip sabah çaly?tyrabilirsiniz.

create PROCEDURE INNOVA_GUNLUK_KURU_GETIR
AS
BEGIN
-- RSS FEED
DECLARE @docHandle   INT;
DECLARE @xmlData     XML;
DECLARE @URL         NVARCHAR(255);
DECLARE @file        NVARCHAR(255);
DECLARE @cmd         NVARCHAR(255);
DECLARE @sql         NVARCHAR(255);
DECLARE @tXML        TABLE(data XML);

--SET @URL  = '‪http://www.tcmb.gov.tr/kurlar/201507/20072015.xml';
SET @URL  = 'http://www.tcmb.gov.tr/kurlar/today.xml'
SET @URL = REPLACE(@URL, '?','')
SET @file = 'D:\temp.xml';

-- Downloading the data
SET @cmd = 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell (new-object System.Net.WebClient).DownloadFile( ''' + @URL + ''',''' + @file + '''  )'
EXEC master.dbo.xp_cmdshell @cmd, no_output

-- Loading the Downloaded File into the XML variable
SET @sql = 'SELECT BulkColumn FROM OPENROWSET( BULK ''' + @file + ''', SINGLE_BLOB ) AS a'
INSERT @tXML EXEC(@sql);
SELECT @xmlData = data from @tXML

-- Preparing the Relational Table from the XML variable
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlData;

DELETE FROM NETSIS..DOVIZ WHERE TARIH = CONVERT(nvarchar, GETDATE() - 1 ,102)

INSERT INTO NETSIS..DOVIZ
(TARIH, SIRA, DOV_ALIS, DOV_SATIS, EFF_ALIS, EFF_SATIS)
SELECT
    CONVERT(nvarchar, cast(Tarihi as datetime),102) as Tarih, NK.SIRA
    , TCMB.Alis, TCMB.Satis
    , TCMB.EfAlis, TCMB.EfSatis
FROM OPENXML(@docHandle, N'//Tarih_Date/Currency')
WITH (
   Tarihi    VARCHAR(100) '../@Date'
   ,CurrencyCode    VARCHAR(10) '@CurrencyCode'
   ,CrossOrder    VARCHAR(10) '@CrossOrder'
   ,Unit    VARCHAR(100) 'Unit'
   ,Isim    VARCHAR(100) 'Isim'
   ,Alis    VARCHAR(100) 'ForexBuying'
   ,Satis    VARCHAR(100) 'ForexSelling'
   ,EfAlis    VARCHAR(100) 'BanknoteBuying'
   ,EfSatis    VARCHAR(100) 'BanknoteSelling'
  ) AS TCMB
  LEFT OUTER JOIN NETSIS..KUR NK ON
    (CASE WHEN NK.NETSISSIRA = 1 THEN 0 ELSE NULL END) = TCMB.CrossOrder
    OR (CASE WHEN NK.NETSISSIRA = 20 THEN 9 ELSE NULL END) = TCMB.CrossOrder
where  TCMB.CurrencyCode <> 'XDR'
AND NK.SIRA IS NOT NULL
;

EXEC sp_xml_removedocument @docHandle;

END]]> Tue, 21 Jul 2015 15:20:30 +0000 http://www.erpne.org/otomatik-kur-guncelleme-mssql_topic4167_post22066.html#22066