Sql Server SMS ve Mail Gönderimi SP-Trigger

SQL Server şirketlerin yoğun kullandığı veri tabanı ürünlerinden birisidir. Öncelikle muhasebe gibi ticaretin temel ürünleri için kullanılan SQL veya başka bir deyişle veri tabanları gün geçtikçe değişen ve gelişen şirket ihtiyaçları doğrultusunda pek çok farklı ürün için kullanılmaya başlandı.

Personel takip, müşteri takip, satış, pazarlama, performans, stok, sipariş derken aklınıza gelebilecek hemen hemen her ürün ve servis için aslında küçük veya büyük bir veri tabanı kullanılmaya başlandı. Durum böyle olunca da bu servis veya hizmetler için pek çok farklı bilgilendirme iş ihtiyaçları doğuyor. Bu nedenle bende bu yazımda sizlere sql server için sp ile sms ve mail gönderiminin nasıl yapıldığını anlatacağım.

Mail için öncelikle Sql server da Managament – Database Mail üzerinde sağ tıklıyoruz Configure Database Mail seçiyoruz.

Next seçiyoruz

İlk seçenekte database mail için profil oluşturmayı seçiyoruz.
Diğer seçenekler daha önce oluşturduğumuz profil ve hesabı yeniden düzenlememize olanak sağlıyor.

Profil ismi verip açıklama ekliyoruz. Add diyoruz.

New Account ile hesap oluşturuyoruz.

Mail ayarlarını kullandığımız mail servis bilgilerine göre dolduruyoruz.

Eklediğimiz mail bilgilerini public olarak işaretleyip, default olarak seçiyoruz.

Next ve Finish ile kurulumu tamamlıyoruz.

SMS Gönderimi için öncelikle GSM servis sağlayıcınızdan api bilgileriniz almanız gerekiyor.

Sp hazırlıyoruz, sp adını database shema nıza göre değiştirebilirsiniz.

CREATE PROCEDURE [Hasta].[usp_GetSQLSMS]
@TelNo VARCHAR (12) ,
@SmsAciklama AS NVARCHAR (MAX)
AS
BEGIN
DECLARE
@Kayit INT ,
@sucu INT ;
DECLARE @Url AS NVARCHAR (200) ;
EXEC @sucu = sp_OACreate
‘Microsoft.XMLHTTP’ ,
@Kayit OUT ;

IF @sucu <> 0
    RAISERROR( 'sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1 ) ;

SET @Url
    = 'https://api.servissaglayici.com.tr/sms/send/get/?usercode=kullanıcı&password=sifre&gsmno=#GsmNo#&message=#Msg#&msgheader=baslik';
SET @Url = REPLACE( @Url, '#GsmNo#', @TelNo ) ;
SET @Url = REPLACE( @Url, '#Msg#', @SmsAciklama ) ;

EXEC @sucu = sp_OAMethod
    @Kayit ,
    'Open' ,
    NULL ,
    'GET' ,
    @Url ,
    true ;

IF @sucu <> 0
    RAISERROR( 'sp_OAMethod Open FAILED!', 16, 1 ) ;

EXEC @sucu = sp_OAMethod
    @Kayit ,
    'send' ;

END ;
EXEC [Hasta].[usp_GetSQLSMS] @TelNo = ‘905355555555’ ,
@SmsAciklama = ‘SQL Database Send SMS – Mehmet SUCU’

Telefon numarasını 12 haneli olarak yazıyoruz. Sms Açıklaması max 74 karakter olması gerekiyor. Api 74 karaktere kadar mesaj göndermemize olanak sağlıyor. Burada türkçe karakterleri function ile ingilizce ye çevirip karakteri arttırabilirsiniz.

Trigger

Sağlık söktöründe çalıştığım için trigger da doğum hizmeti hasta protokolüne eklendiğinde hasta bilgileri sp lerdeki kişilere sms ve mail olarak gidecektir.

CREATE OR ALTER TRIGGER [Hasta].[ProtokolIslem_DogumMailSMS] ON [Hasta].[ProtokolIslem] FOR INSERT
AS
BEGIN
DECLARE @BilgiSMS VARCHAR (74) ;
DECLARE @BilgiMail VARCHAR (300) ;

    IF EXISTS
        (   SELECT
                    1
            FROM    inserted HPI
            WHERE HPI.State = 2
                    AND HPI.HizmetId IN
                             (   SELECT
                                         OH.Id
                                 FROM    Ortak.Hizmet OH
                                 WHERE OH.Id = HPI.HizmetId
                                         AND OH.HizmetAltTipiDinamikId IN
                                                  ( 298, 299 )))
        BEGIN
            SET @BilgiSMS =
                (   SELECT
                            HH.Adi + ' ' + HH.Soyadi + ' ' + HH.GSM + ' '
                            + [Tedavi].[ufns_GetServisBilgisiByProtokolId]( HP.Id ) + ' ' + 'Doğum'
                    FROM    Inserted HPI (NOLOCK)
                            INNER JOIN Hasta.Protokol HP (NOLOCK)
                                      ON HP.Id = HPI.ProtokolId

                            INNER JOIN Tedavi.Yatis TY
                                      ON TY.ProtokolId = HP.Id

                            INNER JOIN Hasta.Hasta HH (NOLOCK)
                                      ON HH.Id = HP.HastaId
                    WHERE HP.State > 0
                            AND TY.State > 0
                            AND HPI.State = 2
                            AND HP.ProtokolTipiId = 10
                            AND EXISTS
                        (   SELECT
                                    *
                            FROM    Ortak.Hizmet OH
                            WHERE OH.Id = HPI.HizmetId
                                    AND OH.HizmetAltTipiDinamikId IN
                                             ( 298, 299 ))) ;
            SET @BilgiMail =
                (   SELECT
                            'HASTA: ' + HH.Adi + ' ' + HH.Soyadi + ', GSM: ' + HH.GSM + ', KAT-ODA: '
                            + [Tedavi].[ufns_GetServisBilgisiByProtokolId]( HP.Id ) + ', AMELİYAT: ' +
                                (   SELECT
                                            OH.Adi
                                    FROM    Ortak.Hizmet OH
                                    WHERE OH.Id = HPI.HizmetId
                                            AND OH.HizmetTipiId = 6
                                            AND OH.HizmetAltTipiDinamikId IN
                                                     ( 298, 299 ))
                    FROM    Inserted HPI (NOLOCK)
                            INNER JOIN Hasta.Protokol HP (NOLOCK)
                                      ON HP.Id = HPI.ProtokolId

                            INNER JOIN Tedavi.Yatis TY
                                      ON TY.ProtokolId = HP.Id

                            INNER JOIN Hasta.Hasta HH (NOLOCK)
                                      ON HH.Id = HP.HastaId
                    WHERE HP.State > 0
                            AND TY.State > 0
                            AND HPI.State = 2
                            AND HP.ProtokolTipiId = 10
                            AND EXISTS
                        (   SELECT
                                    *
                            FROM    Ortak.Hizmet OH
                            WHERE OH.Id = HPI.HizmetId
                                    AND OH.HizmetAltTipiDinamikId IN
                                             ( 298, 299 ))) ;

            EXEC [Hasta].[usp_GetSQLSMS]
                @TelNo = '905353370078' ,
                @SmsAciklama = @BilgiSMS ; --@@BilgiSMS max 74 karakter

            EXEC msdb.dbo.sp_send_dbmail
                @recipients = '[email protected]' ,
                @profile_name = 'SQLMail' ,
                @subject = 'Doğum Hastaları' ,
                @body = @BilgiMail ;
        END ;
END ;

Hazırlamış olduğum trigger da belirtilen tabloya doğum hizmetleri girildiğinde Hasta Adı-Soyadı, telefon numarası, hangi kat-oda ve yatakta kaldığı ve yapılan ameliyat bilgisi ps de belirttiğim telefon numarasına ve mail bilgisine veriler gönderilmektedir.

Api 74 karakter kabul ettiği için sms ve mail için iki parametre verip karakter sınırlamasını ekliyoruz.

Tekrar görüşmek dileğiyle, umarım faydalı olmuştur.