SQL Sorgu İşyeri Bazında Bekleyen Siparişleri Mail Gönderme

KVNCYLDZ

Yeni Üye
Katılım
19 Eki 2018
Mesajlar
9
En iyi yanıt
0
Puanları
3
Yaş
41
Konum
Mersin
Ad Soyad
Kıvanç Yıldız
Office Versiyon
Office 2016 TR
Aşağıdaki kod ile bekleyen siparişlerinizi, serverda Trigger ayarları yapıldıktan sonra otomatik olarak istediğiniz saatte sipariş mail gönderebilirsiniz.

SQL:
DECLARE @xml VARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST((
SELECT
ROW_NUMBER() OVER (ORDER BY L_CAPIDIV.NAME) AS 'td',
'',
UPPER(L_CAPIDIV.NAME) AS 'td',
'',
CONVERT(VARCHAR(50),CONVERT(MONEY,SUM((UINFO1*UINFO2)*AMOUNT-(UINFO1*UINFO2)*SHIPPEDAMOUNT)),1) AS 'td',
'',
CONVERT(VARCHAR(50),SUM (CONVERT(MONEY,((LG_069_01_ORFLINE.AMOUNT-LG_069_01_ORFLINE.SHIPPEDAMOUNT)*((LG_069_01_ORFLINE.LINENET)/(LG_069_01_ORFLINE.AMOUNT))))),1) AS 'td',
--CONVERT(VARCHAR(50),SUM (CONVERT(MONEY,(LG_069_01_ORFLINE.LINENET))),1) AS 'td',
''
FROM LG_069_01_ORFLINE
INNER JOIN LG_069_01_ORFICHE ON LG_069_01_ORFICHE.LOGICALREF = LG_069_01_ORFLINE.ORDFICHEREF
LEFT JOIN LG_069_CLCARD ON LG_069_CLCARD.LOGICALREF = LG_069_01_ORFICHE.CLIENTREF
LEFT JOIN LG_069_ITEMS ON LG_069_ITEMS.LOGICALREF = LG_069_01_ORFLINE.STOCKREF
LEFT JOIN LG_SLSMAN ON LG_069_01_ORFICHE.SALESMANREF = LG_SLSMAN.LOGICALREF AND LG_SLSMAN.FIRMNR=069
LEFT JOIN L_CAPIDIV ON LG_069_01_ORFICHE.BRANCH = L_CAPIDIV.NR AND L_CAPIDIV.FIRMNR=069
LEFT JOIN LG_069_SHIPINFO  ON LG_069_01_ORFICHE.SHIPINFOREF = LG_069_SHIPINFO.LOGICALREF

WHERE
LG_069_01_ORFLINE.TRCODE=1
AND LG_069_01_ORFLINE.LINETYPE=0
AND (LG_069_01_ORFLINE.AMOUNT-LG_069_01_ORFLINE.SHIPPEDAMOUNT)>0
AND LG_069_01_ORFLINE.STATUS=1
AND LG_069_01_ORFICHE.DATE_ > '01.01.2021'
AND LG_SLSMAN.CODE IN ('33.001','33.002','33.008')

GROUP BY L_CAPIDIV.NAME
ORDER BY L_CAPIDIV.NAME

FOR XML PATH('tr'), ELEMENTS ) AS VARCHAR(MAX))


DECLARE @msg NVARCHAR(MAX)
SET @msg=(
SELECT
+'Merhaba;'
+'<br><br>'
+'Bugün itibaiyle İşyeri bazında bekleyen sipariş bilgileri aşağıda gönderilmiştir.'
+'<br><br>'
+'Bu mail sistem tarafından otomatik olarak atılmştır.'
+ '<br><br>'
+ 'Toplam Tonaj : '
+ CONVERT(VARCHAR(50),CONVERT(MONEY,SUM((UINFO1*UINFO2)*AMOUNT-(UINFO1*UINFO2)*SHIPPEDAMOUNT)),1)
+ ' (Kg)<br>'
+ 'Toplam Tutar  : '
+ CONVERT(VARCHAR(50),SUM (CONVERT(MONEY,((LG_069_01_ORFLINE.AMOUNT-LG_069_01_ORFLINE.SHIPPEDAMOUNT)*((LG_069_01_ORFLINE.LINENET)/(LG_069_01_ORFLINE.AMOUNT))))),1)
--+ CONVERT(VARCHAR(50),SUM (CONVERT(MONEY,(LG_069_01_ORFLINE.LINENET))),1)
+ ' TL'
+ '<br><br><h3>'

FROM LG_069_01_ORFLINE
INNER JOIN LG_069_01_ORFICHE ON LG_069_01_ORFICHE.LOGICALREF = LG_069_01_ORFLINE.ORDFICHEREF
LEFT JOIN LG_069_CLCARD ON LG_069_CLCARD.LOGICALREF = LG_069_01_ORFICHE.CLIENTREF
LEFT JOIN LG_069_ITEMS ON LG_069_ITEMS.LOGICALREF = LG_069_01_ORFLINE.STOCKREF
LEFT JOIN LG_SLSMAN ON LG_069_01_ORFICHE.SALESMANREF = LG_SLSMAN.LOGICALREF AND LG_SLSMAN.FIRMNR=069
LEFT JOIN L_CAPIDIV ON LG_069_01_ORFICHE.BRANCH = L_CAPIDIV.NR AND L_CAPIDIV.FIRMNR=069
LEFT JOIN LG_069_SHIPINFO  ON LG_069_01_ORFICHE.SHIPINFOREF = LG_069_SHIPINFO.LOGICALREF

WHERE
LG_069_01_ORFLINE.TRCODE=1
AND LG_069_01_ORFLINE.LINETYPE=0
AND (LG_069_01_ORFLINE.AMOUNT-LG_069_01_ORFLINE.SHIPPEDAMOUNT)>0
AND LG_069_01_ORFLINE.STATUS=1
AND LG_069_01_ORFICHE.DATE_ > '01.01.2021'
AND LG_SLSMAN.CODE IN ('33.001','33.002','33.008'))

SET @body ='<html><body style= font-size:90%<H3>'
+@msg+
'<table align="left" style= font-size:95% border="2" cellpadding="2" cellspacing="2">
<tr>
<th>Sıra No</th>
<th>İşyeri</th>
<th>Bekleyen Miktar</th>
<th>Bekleyen Sipariş Tutarı</th>

<caption align="bottom"></tr></br><hr>
<caption align="bottom">
</tr>
<hr>
<html>
İyi Çalışmalar.
<br>
Kıvanç Yıldız.
</html>'

DECLARE @subjectx NVARCHAR(MAX) SET @subjectx=CONVERT(NVARCHAR,GETDATE(),104)+' Tarihi İtibaiyle İşyeri Bazında Bekleyen Siparişler'

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@profile_name='raporlama',
@body = @body,
@body_format ='html',
@recipients='kivanc33@hotmail.com',
@blind_copy_recipients='kivanc33@hotmail.com',
@subject=@subjectx
 

Rahle

Yeni Üye
Katılım
20 Haz 2018
Mesajlar
126
En iyi yanıt
0
Puanları
18
Yaş
49
Konum
İstanbul
Ad Soyad
Huzeyfe Yaman
Office Versiyon
Office 2010
Merhaba,
Bugünlerde tam da böyle bir şeye ihtiyaç vardı...
Teşekkürler
 
Üst Alt