Küçük, orta ve büyük ölçekli işletmelerin kullandığı Logo ERP programlarında, ihtiyaca göre farklı döviz türleri ile işlem yapılabilmesi sağlanmıştır. Bu kapsamda, yapılan işlemlerde, işlem tarihine göre otomatik veya manuel olarak döviz kuru bilgisi girilerek işlemler gerçekleştirilir. Bu işlemlerin gerçekleşmesi, girilen tutarın döviz karşılığını veya dövizli tutarın Türk Lirası cinsinden karşılığının görülebilmesi için TCMB tarafından açıklanan döviz kurların sisteme aktarılması gerekmektedir.
Bu yazıda, SQL Server üzerinden Job tanımlayarak, her gün verilen tarihte, TCMB’nin paylaştığı döviz kurlarını, logonun L_DAILYEXCHANGES tablosuna aktarıp, program içerisinden ekstra bir işlem yapmadan döviz kurlarını nasıl kullanacağımızı anlatacağız.
1- SQL Server Yapılandırması
SQL Server’da http nesnemizi oluşturabilmemiz için Ole Automation Procedures değerini 1 yapıp reconfigure yapmamız gerekiyor. Bunun için aşağıdaki sorguyu çalıştırın;
use master
go
sp_configure 'show advanced options', 1
go
sp_configure 'Ole Automation Procedures', 1
go
sp_configure 'show advanced options', 1
go
reconfigure;
Çalıştırdıktan sonra veya çalıştırırken hata alırsanız, SQL Server servislerini restart edip tekrar deneyebilirsiniz.
2- Prosedürlerin Hazırlanması
Bu işlem için 2 tane prosedür oluşturacağız. Birincisi, web sitelerine sorgularımızı yapabilmek ve ihtiyaçlarımıza cevap verebilecek şekilde geri dönüş almak için, ikincisi de ilk oluşturduğumuz prosedürü kullanarak edindiğimiz bilgileri Logo döviz kurları tablosu olan L_DAILYEXCHANGES tablosuna basabilmek için.
Yukarıdaki sorguyu “master” veritabanında çalıştırdığınız için seçili olan veritabanı “master” olmuştur. Aşağıdaki sorguları “master” veritabanında değil Logo veritabanını seçerek çalıştırınız.2.1- sp_GetHttpRequest Prosedürü
create proc [dbo].[sp_GetHttpRequest]
@httpUrlAddress nvarchar(256),
@httpMethod nvarchar(10),
@contentType nvarchar(100),
@authorization nvarchar(max),
@headerKey nvarchar(max),
@headerValue nvarchar(max),
@httpBody nvarchar(max),
@responseText nvarchar(max) out
as
begin
Declare @objectId int
Declare @hResult int
Declare @statusCode nvarchar(10)
Declare @statusText nvarchar(max)
exec @hResult = sp_OACreate 'Msxml2.ServerXMLHTTP.6.0', @objectId out, 1
if @hResult <> 0 exec sp_OAGetErrorInfo @objectId
exec @hResult = sp_OAMethod @objectId, 'open', null, @httpMethod, @httpUrlAddress, 0
if @hResult <> 0 exec sp_OAGetErrorInfo @objectId
if (Not @contentType is null)
begin
exec @hResult = sp_OAMethod @objectId, 'setRequestHeader', null, 'Content-Type', @contentType
if @hResult <> 0 exec sp_OAGetErrorInfo @objectId
end
if (Not @authorization is null)
begin
exec @hResult = sp_OAMethod @objectId, 'setRequestHeader', null, 'Authorization', @authorization
if @hResult <> 0 exec sp_OAGetErrorInfo @objectId
end
if ((Not @headerKey is null ) and (@headerValue is null))
begin
exec @hResult = sp_OAMethod @objectId, 'setRequestHeader', null, @headerKey, @headerValue
if @hResult <> 0 exec sp_OAGetErrorInfo @objectId
end
exec @hResult = sp_OAMethod @objectId, 'send', null, @httpBody
if @hResult <> 0 exec sp_OAGetErrorInfo @objectId
exec sp_OAGetProperty @objectId, 'status', @statusCode out
exec sp_OAGetProperty @objectId, 'statusText', @statusText out
create table #responseTable(ResponseText nvarchar(max))
Insert Into #responseTable(ResponseText)
exec sp_OAGetProperty @objectId, 'ResponseText'
select @responseText = ResponseText
from #responseTable
drop table #responseTable
exec sp_OADestroy @objectId
end
GO
2.2- sp_GetTCMBExchangeRates Prosedürü
create proc [dbo].[sp_GetTCMBExchangeRates]
as
begin
SET TEXTSIZE -1
declare @urlAddress nvarchar(max)
declare @responseText nvarchar(max)
declare @LDate int
declare @LControl smallint
set @urlAddress = 'https://www.tcmb.gov.tr/kurlar/today.xml'
exec sp_GetHttpRequest @urlAddress, 'GET', 'text/xml', null, null, null, null, @responseText out
Create table ##TempTable(ResponseXML xml)
Insert Into ##TempTable(ResponseXML)
Select Replace(Replace(@responseText,'<!--?xml version="1.0" encoding="UTF-8"?-->',''),'<!--?xml-stylesheet type="text/xsl" href="isokur.xsl"?-->','')
set @LDate = DATEPART(DAY,(Convert(datetime, GETDATE(),103))) + DATEPART(MONTH,(Convert(datetime, GETDATE(),103))) * 256 + DATEPART(YEAR,(Convert(datetime, GETDATE(),103))) * 65536
set @LControl = (SELECT COUNT(*) FROM L_DAILYEXCHANGES WHERE DATE_=@LDate)
IF (@LControl!=0)
BEGIN
DELETE FROM L_DAILYEXCHANGES WHERE DATE_=@LDate
END
INSERT INTO L_DAILYEXCHANGES select
DATEPART(DAY,(Convert(datetime, GETDATE(),103))) + DATEPART(MONTH,(Convert(datetime, GETDATE(),103))) * 256 + DATEPART(YEAR,(Convert(datetime, GETDATE(),103))) * 65536 AS DATE_,
CASE (b.Data.value('@Kod', 'nvarchar(5)')) WHEN 'USD' THEN 1 WHEN 'AUD' THEN 3 WHEN 'DKK' THEN 6 WHEN 'EUR' THEN 20 WHEN 'GBP' THEN 17 WHEN 'CHF' THEN 11 WHEN 'SEK' THEN 10 WHEN 'CAD' THEN 14 WHEN 'KWD' THEN 15 WHEN 'NOK' THEN 16 WHEN 'SAR' THEN 18 WHEN 'JPY' THEN 13 WHEN 'BGN' THEN 23 WHEN 'RON' THEN 161 WHEN 'RUB' THEN 51 WHEN 'IRR' THEN 31 WHEN 'CNY' THEN 25 WHEN 'PKR' THEN 124 WHEN 'QAR' THEN 126 WHEN 'KRW' THEN 101 WHEN 'AZN' THEN 162 WHEN 'AED' THEN 59 ELSE 999 END AS CRTYPE,
--b.Data.value('(CurrencyName/text())[1]', 'nvarchar(50)') CurrencyName,
b.Data.value('(ForexBuying/text())[1]', 'float') RATES1,
b.Data.value('(ForexSelling/text())[1]', 'float') RATES2,
b.Data.value('(BanknoteBuying/text())[1]', 'float') RATES3,
b.Data.value('(BanknoteSelling/text())[1]', 'float') RATES4,
(Convert(datetime, GETDATE(),103)) [EDATE],'',0,0
From ##TempTable
Cross apply ##TempTable.[ResponseXML].nodes('/Tarih_Date//Currency') b(Data)
drop table ##TempTable
enD
GO
3- Prosedürlerin Job ile Günlük Otomatik Çalıştırılması
Job oluşturmak için SQL Server Agent sağ tık→New→Job sekmesine tıklıyoruz.
Aşağıdaki New Job penceresi açılacaktır. Biz burada ihtiyacımız olan bir SP yi, günlük çalışacak şekilde düzenleyeceğiz. Jobumuzu Name kısmında isimlendiriyoruz, Owner kısmına da sa kullanıcısı girilmelidir. Owner kısmında aslında varsayılan olarak jobu oluşturan kişinin kullanıcısı gelir ancak burada sa kullanıcısı tercih edilir çünkü bir başka kullanıcının logini devre dışı bırakıldığında ya da silindiğinde jobun owner ını güncellemezsek jobun çalışmasını engelleyebilir, bu gibi sorunların önüne geçebilmek için sa tercih edilir. Gerekli olmadıkça belirli kullanıcılara bağlı işler yapılmamalıdır. Ancak jobu oluşturan kullanıcı sysadmin olmayıp SQLAgentUserRole rolüne sahipse Owner alanına müdahalede bulunamaz, job kendi kullanıcısı ile oluşturulur.
Category kısmında da varsayılan olarak gelen kategorize edilmemiş bir iş olarak bırakabiliriz. Kategorilerin tek nedeni jobları organize etmenize yardımcı olmaktır, böylece bir kategoriye göre sıralayabilirsiniz ve birbiriyle ilişkili tüm jobları görebilirsiniz.
Description kısmında da yapılan işin hakkında bilgilendirme içerikli bir açıklama yazılabilir.
Steps sayfasında bu job için gerekli adımı/adımları belirtiriz. Mevcut adımlara Insert ile istenilen aralığa bir adım eklenebilir, Edit ile bu adımlar üzerinde değişiklikler yapılabilir veya istenmeyen bir adım var ise Delete deyip bu adımı kaldırabiliriz. New deyip bir adım oluşturmakla başlıyoruz:
New Job Step penceresi açılıyor. Oluşturacağımız adımı isimlendiriyoruz. Type kısmında job adımı için birçok farklı alana olanak sağlanıldığını görebiliriz. Burada yapacağımız işlem SP çalıştırmak olduğu için type seçeneğini varsayılan olarak da gelen Transact-SQL script(T-SQL) den yana kullanıyoruz. Job un bize sağlamış olduğu diğer type lar aşağıdaki gibidir:
-
ActiveX Script
-
Operating System (cmdExec)
-
PowerShell
-
Replication Distributor
-
Replication Merge
-
Replication Queue Reader
-
Replication Transaction-Load Reader
-
Replication Snapshot
-
SQL Server Analysis Services Command
-
SQL Server Analysis Services Query
-
SQL Server Integration Services Package
Command kısmında ilgili komutu yazıyoruz.
Burada dikkat etmemiz gereken bir diğer konu Database kısmında varsayılan olarak master veri tabanı geldiği için çalıştırmak istediğimiz SP nin veri tabanını seçmemiz gerekiyor ya da Command kısmında script içinde veri tabanı adını da belirtmeliyiz.
Advanced sayfasında oluşturduğumuz bu adım bittiğinde ne yapmasını istediğimi belirlerim. Eğer ikinci bir adım oluşturacaksak Go to the next step deyip bir başarılı çalışma sonrası bir sonraki adıma geç diyebilirim. Ancak tek adımlı bir jobumuz olacağı için başarılı çalışma sonucu jobun başarılı olarak raporlanmasını isteyip jobu bitirmek istiyorum.
T-SQL Komut Dosyası olduğundan, herhangi bir çıktısı varsa bu çıktıyı bir dosyaya koymak,bir tabloya veya history ye kaydetmek istediğimizde Output file, Log to table, Include step output in history kısımlarını işaretleyebiliriz. Bunları varsayılan modunda yani dahil edilmeden bırakıyorum.
Run as user kısmında eğer sysadmin rolündeysek, bu job adımını çalıştırmak için başka bir SQL Logini de seçebiliriz.
On success action
kısmının hemen altında bu adım başarısız olursa ne kadar daha çalıştırmayı deneyebileceğimizi ve bu denemeler arasında kaçar dakika beklemek istediğimizi belirliyoruz.
Bu adımın başarısız olması durumunda alacağı aksiyonu belirlediğimiz kısımdır. Başarısız çalışma sonucu jobun başarısız olarak raporlanmasını isteyip jobu bitirmek istiyorum.
Step kısmını OK deyip bitiriyoruz. Başka bir adım eklemek istediğimizde yine aynı şekilde ekleyebiliriz. Altta Start Step kısmında bu Job hangi adım ile çalışmaya başlayacak onu belirleyebiliyoruz.
Step kısmından sonra, Schedule sayfasına tıklıyoruz bu alan step sayfasında belirlediğimiz adımların ne zaman hangi aralıklarla çalışacağı bilgilerini barındırır.
New deyip bir Schedule oluşturuyoruz:
Jobun her gün çalışmasını istiyorum. Jobumuzu isimlendirip Schedule Type kısmında varsayılan olarak da gelen Recurring olarak bırakıyoruz, bu type ile bu jobun tekrar tekrar çalışmasını istiyorum buna bağlı olarak da aşağıdaki periyot bilgilerini doldurmalıyız.
Start automatically when SQL Server Agent starts: SQL Server Agent servisi başlatıldığında otomatik olarak jobu da başlatır.
Start whenever the CPUs become idle to start: Jobu CPU’lar boşta kaldığında başlatmayı sağlar.
One time: Zamanlamanın yalnızca bir kez çalışmasını istiyorsak tercih ederiz, bunun tercih edilmesi sonucu One-time occurrence alanı aktif olacaktır. Buradan da tek seferlik çalışmanın tarih ve saat bilgilerini girmemiz gerekir.
Frequency kısmında schedule ın hangi aralıklarla çalıştığını belirtiriz. Varsayılan olarak yukarıdaki gibi Weekly gelir ancak jobun her gün çalışmasını istiyorum. Recurs every alanında, belirlediğim Occurs alanına göre kaç günde/haftada/ayda bir tekrarlanacağını belirleyebiliriz.
Daily frequency alanında hangi saatte ya da gün içinde ne kadar sıklıkla çalıştığını belirtiriz. Günde bir kere saat 11:00’da çalışmasını istiyorum o yüzden Occurs once at işaretlenmeli. Gün içinde birkaç kere çalışmasını istiyorsak Occurs every deyip kaç saat/dakika/saniyede bir çalışması gerektiğini belirtmeliyiz.
Duration alanında Schedule ın başlangıç ve bitiş tarihlerini sınırlandırabiliriz. Start date alanı varsayılan olarak bugünün tarihi gelir, End date kısmı da varsayılan olarak No end date olarak gelir. Jobumuzun daimi olarak çalışmasını istediğim için bitiş tarihi belirlemiyorum.
Summary alanı da belirlediğimiz bu şartların bir özetini bizlere göstermektedir.
Schedule ı düzenlemeyi bitirdik OK deyip oluşturuyoruz.
Alerts sayfası Jobun çalışma durumunu takibini yapabilmek için alarm oluşturabilme imkanı tanır.
Notification sayfasında job tamamlandığında alınmasını istediğimiz aksiyonları belirtebiliriz.
Targets sayfası jobun hedef sunucularını yönetmek için kullanılan alandır. Kayıtlı herhangi bir server yoksa ve kendi localimde bu jobu çalıştırmak istiyorsam varsayılan olarak gelen Target local server işaretli olması gerekir.
Tüm aşamaları tamamladıktan sonra OK deyip jobumuzu Create ediyoruz.
Jobumuzun loglarını takip edebilmek için Job üzerine sağ tık→View History sekmesine tıklamamız yeterli olacaktır.