KVNCYLDZ
Yeni Üye
- Katılım
- 19 Eki 2018
- Mesajlar
- 11
- En iyi yanıt
- 1
- Puanları
- 3
- Yaş
- 45
- Konum
- Mersin
- Ad Soyad
- Kıvanç Yıldız
- Office Vers.
- 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
