--CREATE PROCEDURE  [dbo].[P_HTML]  
--as
declare @bodyOzet varchar(max)
declare @bodyDetay varchar(max)
/*
CREATE TABLE [dbo].[HTML](
	[HTMLCODE] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
 
--DOSYAYA KAYDETMESI ICIN AYAR ACILMALI
sp_configure 'show advanced options', '1'
RECONFIGURE
GO
-- this enables xp_cmdshell
sp_configure 'xp_cmdshell', '1' 
RECONFIGURE
GO

--DOSYAYA KAYDETME KOMUTU
declare @sql varchar(8000)
select @sql = 'bcp "select * from [DATABASE].[dbo].HTML" queryout E:\SONUC.html -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

 --ISLEM SORGUSU
 exec dbo.P_HTML 
*/
set @bodyDetay = cast( (
select  td = cast(day(Tarih) as varchar)+'/'+cast(month(Tarih) as varchar)+'/'+cast(Year(Tarih) as varchar)+'</td><td>' + UrunKodu + '</td><td>' + UrunAdi + '</td><td>' + Barkod + '</td><td>' + cast( Miktar as varchar(30) ) + '</td><td>' + cast( SatirNetTutar as varchar(30) )
from (
      select top 100 percent 
	  Tarih=		(select SF.DATE FROM StockFiches SF WHERE SF.Id=SL.StockFicheId),
	  UrunKodu=		(select P.Code FROM Products P WHERE P.Id=SL.StockId),
	  UrunAdi=		(select P.ProductName FROM Products P WHERE P.Id=SL.StockId),
	  Barkod=		(select P.Barcode FROM Barcodes P WHERE P.ProductId=SL.StockId),
      Miktar=		SL.Amount,
      SatirNetTutar=SL.LineNet
      from StockLines SL 
      
      ) as d order by Tarih desc
for xml path( 'tr' ), type ) as varchar(max) )

set @bodyDetay = N'Malzeme Bazlı Satışlar ('+cast(GETDATE() as varchar)+') <table cellpadding="2" cellspacing="0" border="1">'
          + '<tr><th>Tarih</th><th>Ürün Kodu</th><th>Ürün Adı</th><th>Barkod</th><th>Miktar</th><th>Tutar</th></tr>'
          + replace( replace( @bodyDetay, '&lt;', '<' ), '&gt;', '>' )
          + '</table>' 

/*aşağısı özet*/

set @bodyOzet = cast( (
select  td = cast(day(Tarih) as varchar)+'/'+cast(month(Tarih) as varchar)+'/'+cast(Year(Tarih) as varchar)+'</td><td>' + OdemeTuru + '</td><td>' + Cast(Toplam as Varchar) + '</td><td>' + Cast(IndirimToplam as Varchar) + '</td><td>' + cast( NetToplam as varchar(30) ) 
from (
select Tarih,OdemeTuru,Sum(Toplam) Toplam, Sum(IndirimToplam) IndirimToplam,Sum(NetToplam) NetToplam from (
SELECT 
	CONVERT(DATE, F.CreatedDate) AS Tarih  
	, CASE F.IO WHEN 0 THEN L.Total ELSE 0 - L.Total END AS Toplam
	, CASE F.IO WHEN 0 THEN L.TotalDiscount ELSE 0 - L.TotalDiscount END AS IndirimToplam
	, CASE F.IO WHEN 0 THEN L.LineNet ELSE 0 - L.LineNet END AS NetToplam
	, L.VatInc KDVDH
	, L.Vat KDV
	, CASE  F.PayType  
		WHEN 0 THEN 'Nakit Kasa'
		WHEN 1 THEN 'Banka Pos' 
		WHEN 2 THEN 'Kredi Kartı' 
		WHEN 3 THEN 'İkram' 
		END OdemeTuru 
	FROM StockLines L
	Left Join StockFiches F ON F.Id = L.StockFicheId 
	WHERE  
        F.FicheType in (0,1)  
	) as t group by Tarih,OdemeTuru  
      
      ) as d order by Tarih desc
for xml path( 'tr' ), type ) as varchar(max) )

set @bodyOzet = N'Özet Bazlı Satışlar ('+cast(GETDATE() as varchar)+') <table cellpadding="2" cellspacing="0" border="1">'
          + '<tr><th>Tarih</th><th>Odeme Turu</th><th>Toplam</th><th>Indirim Toplam</th><th>NetToplam</th></tr>'
          + replace( replace( @bodyOzet, '&lt;', '<' ), '&gt;', '>' )
          + '</table><br>' + @bodyDetay

truncate table HTML
insert into HTML(HTMLCODE) VALUES(@bodyOzet)
 
GO