USE [GURMEN] GO /****** Object: StoredProcedure [dbo].[proc_SendMailForOKC] Script Date: 25.8.2016 08:42:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[proc_SendMailForOKC] @MailProfile VARCHAR(30) , @ToMailAddresses VARCHAR(255) , @DatabaseName NVARCHAR(128) AS DECLARE @sql VARCHAR(8000) DECLARE @CompanySQL VARCHAR(255) SET @CompanySQL = ( SELECT CompanyName + '.txt' AS CompanyName FROM dfCompanyDefault WHERE CompanyCode = 1 ); SET @sql = 'SELECT MasterCompanyName,CompanyName AS SubCompanyName,InvoiceDate,StoreCode,CurrAccDescription,MAX(Entegre) AS OkcliIslemAdedi,MAX(EntegreDegil) AS OkcsizIslemAdedi FROM ( SELECT CompanyName , InvoiceDate , StoreCode , CurrAccDescription , Entegre = CASE WHEN FiscalPrintedState = 1 THEN count(FiscalPrintedState) ELSE 0 END , EntegreDegil = CASE WHEN FiscalPrintedState = 0 THEN count(FiscalPrintedState) ELSE 0 END FROM trInvoiceHeader WITH (NOLOCK) INNER JOIN cdCurrAccDesc WITH (NOLOCK) ON cdCurrAccDesc.CurrAccTypeCode = 5 AND cdCurrAccDesc.CurrAccCode=trInvoiceHeader.StoreCode AND LangCode = ''TR'' INNER JOIN dfCompanyDefault WITH (NOLOCK) ON dfCompanyDefault.CompanyCode = trInvoiceHeader.CompanyCode WHERE ProcessCode=N''R'' AND IsReturn = 0 AND InvoiceDate BETWEEN GETDATE()-30 AND GETDATE() GROUP BY CompanyName,InvoiceDate,StoreCode,CurrAccDescription,FiscalPrintedState ) AS Source CROSS JOIN (SELECT CompanyName AS MasterCompanyName FROM dfCompanyDefault WHERE CompanyCode = 1) AS MasterComp GROUP BY MasterCompanyName,CompanyName,InvoiceDate,StoreCode,CurrAccDescription ORDER BY MasterCompanyName,CompanyName,InvoiceDate '; EXEC msdb.dbo.sp_send_dbmail @profile_name = @MailProfile , @recipients = @ToMailAddresses , @subject = @CompanySQL, @body = 'Ökc Kayıt Kontrol' , @attach_query_result_as_file = 1 , @query_result_header = 1 , @query_result_no_padding = 1 , @execute_query_database = @DatabaseName , @query = @sql , @query_attachment_filename = @CompanySQL , @query_result_separator = ';';
