usp_Email_AutomationScorecard

use BusinessSuite
go

if object_id('dbo.tbl_Email_Report') is not null drop table dbo.tbl_Email_Report
 if object_id('dbo.tbl_Email_Report_List') is not null drop table dbo.tbl_Email_Report_List
 if object_id('dbo.usp_Email_AutomationScorecard') is not null drop proc dbo.usp_Email_AutomationScorecard
 create table dbo.tbl_Email_Report(rptID int identity,ReportID int, Report nvarchar(max),Subject nvarchar(255),EmailTo nvarchar(2000),EmailDate datetime default getdate(),constraint PK_tbl_Email_Report primary key clustered(rptID))
 create table dbo.tbl_Email_Report_List(eid int identity,Name nvarchar(255),Email nvarchar(255),ReportID int,Active bit,UpdateDate datetime default getdate(),constraint PK_tbl_Email_Report_List primary key clustered(eid))
 go
 -- =============================================
 -- Author: Albert He
 -- Create date: 2018-05-04
 -- Description: Automation Scorecard
 -- example: dbo.usp_Email_AutomationScorecard @PRM_LAN_ID=''
 --report id = 161, report name = 'Automation Scorecard'
 -- =============================================
 create PROC [dbo].[usp_Email_AutomationScorecard]
 @PRM_WEEKEND DATE=''
 AS
 SET NOCOUNT ON
if @PRM_WEEKEND='' set @PRM_WEEKEND=getdate();

declare @startDate date;
 select @startDate = DATEADD(dd, -(DATEPART(dw, @PRM_WEEKEND)-1), @PRM_WEEKEND)
 declare @mon varchar(10),@tue varchar(10),@wed varchar(10),@thu varchar(10),@fri varchar(10),@sat varchar(10),@sun varchar(10);
 select @sun=@startDate,@mon=dateadd(dd,1,@startdate),@tue=dateadd(dd,2,@startdate),@wed=dateadd(dd,3,@startdate),@thu=dateadd(dd,4,@startdate),@fri=dateadd(dd,5,@startdate),@sat=dateadd(dd,6,@startdate)
 --select @sun,@mon,@tue,@wed,@thu,@fri,@sat

declare @reportName nvarchar(255),@reportID int,@report nvarchar(max),@emailList VARCHAR(2000),@filename nvarchar(255),@subject nvarchar(255);

select @reportID=ReportID,@reportname='Scorecard' from tbl_BS_Reports_Configuration where Reportname='automation scorecard'
 select @subject = replace(@reportName+'('+@sun+'~'+@sat+')','-','');
 set @filename = '\\BREPL2.BELLDEV.DEV.BCE.CA\Backup\'+@subject+'.xls';

select @emailList=replace(replace(STUFF((
 select ';"'+case when coalesce(Name,'')='' then email else Name end+'" <'+email+'>' from [dbo].[tbl_Email_Report_List] where ReportID=@reportID and Active=1 for xml path('')
 ),1,1,''),'<','<'),'>','>')

declare @sql varchar(max);
 set @sql = CAST('' AS varchar(MAX)) + '
 if object_id(''tempdb..#t0'' ) is not null drop table #t0
 if object_id(''tempdb..#t1'' ) is not null drop table #t1
 if object_id(''tempdb..#t2'' ) is not null drop table #t2
 if object_id(''tempdb..#t1a'') is not null drop table #t1a
 if object_id(''tempdb..#t2a'') is not null drop table #t2a
 if object_id(''tempdb..#t3'' ) is not null drop table #t3
 if object_id(''tempdb..#tmp'') is not null drop table #tmp
 if object_id(''tempdb..#out'') is not null drop table #out
 if object_id(''tempdb..#rpt'') is not null drop table #rpt
 if object_id(''tempdb..#loadedXTD'') is not null drop table #loadedXTD
 if object_id(''tempdb..#completedXTD'') is not null drop table #completedXTD

create table #t0 (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255),['+@sun+'] float,['+@mon+'] float,['+@tue+'] float,['+@wed+'] float,['+@thu+'] float,['+@fri+'] float,['+@sat+'] float,WeekSum float,MTD float,YTD float)
 create table #t1 (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255),['+@sun+'] float,['+@mon+'] float,['+@tue+'] float,['+@wed+'] float,['+@thu+'] float,['+@fri+'] float,['+@sat+'] float,WeekSum float,MTD float,YTD float)
 create table #t2 (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255),['+@sun+'] float,['+@mon+'] float,['+@tue+'] float,['+@wed+'] float,['+@thu+'] float,['+@fri+'] float,['+@sat+'] float,WeekSum float,MTD float,YTD float)
 create table #t1a (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255),['+@sun+'] float,['+@mon+'] float,['+@tue+'] float,['+@wed+'] float,['+@thu+'] float,['+@fri+'] float,['+@sat+'] float,WeekSum float,MTD float,YTD float)
 create table #t2a (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255),['+@sun+'] float,['+@mon+'] float,['+@tue+'] float,['+@wed+'] float,['+@thu+'] float,['+@fri+'] float,['+@sat+'] float,WeekSum float,MTD float,YTD float)
 create table #t3 (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255),['+@sun+'] float,['+@mon+'] float,['+@tue+'] float,['+@wed+'] float,['+@thu+'] float,['+@fri+'] float,['+@sat+'] float,WeekSum float,MTD float,YTD float)
 create table #tmp (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255),['+@sun+'] float,['+@mon+'] float,['+@tue+'] float,['+@wed+'] float,['+@thu+'] float,['+@fri+'] float,['+@sat+'] float,WeekSum float,MTD float,YTD float)
 create table #out (lvl varchar(5) default '''',LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(55),['+@sun+'] varchar(50),['+@mon+'] varchar(50),['+@tue+'] varchar(50),['+@wed+'] varchar(50),['+@thu+'] varchar(50),['+@fri+'] varchar(50),['+@sat+'] varchar(50),WeekSum varchar(50),MTD varchar(50),YTD varchar(50))
 create table #rpt (id int identity(1,10),lvl varchar(5) default '''',LOB varchar(50) default '''',PROCESS_NAME varchar(255) default '''',seq varchar(55) default '''',['+@sun+'] varchar(50) default '''',['+@mon+'] varchar(50) default '''',['+@tue+'] varchar(50) default '''',['+@wed+'] varchar(50) default '''',['+@thu+'] varchar(50) default '''',['+@fri+'] varchar(50) default '''',['+@sat+'] varchar(50) default '''',WeekSum varchar(50) default '''',MTD varchar(50) default '''',YTD varchar(50) default '''')

--#loadedXTD seq=1
 select process_name,lob,1 seq,loadedMTD MTD,loadedYTD YTD into #loadedXTD from ( --seq=1
 select ytd.process_name,ytd.lob,mtd.loadedMTD,ytd.loadedYTD from
 (
 select process_name,lob,count(loaded) loadedYTD FROM tbl_AutomationScorecard
 where year(loaded) = year('''+@sat+''') and loaded<='''+@sat+''' and coalesce(loaded,'''')!=''''
 group by process_name,lob--,loaded
 ) ytd full outer join (
 select process_name,lob,count(loaded) loadedMTD FROM tbl_AutomationScorecard
 where year(loaded) = year('''+@sat+''') and month(loaded) = month('''+@sat+''') and loaded<='''+@sat+''' and coalesce(loaded,'''')!=''''
 group by process_name,lob--,loaded
 ) mtd on ytd.process_name=mtd.process_name and ytd.lob=mtd.lob
 )t

--#completedXTD seq=2
 select process_name,lob,2 seq,completedMTD MTD,completedYTD YTD into #completedXTD from ( --seq=2
 select ytd.process_name,ytd.lob,mtd.completedMTD,ytd.completedYTD from
 (
 select process_name,lob,count(completed) completedYTD FROM tbl_AutomationScorecard
 where year(loaded) = year('''+@sat+''') and loaded<='''+@sat+''' and coalesce(completed,'''')!=''''
 group by process_name,lob--,loaded
 ) ytd full outer join (
 select process_name,lob,count(completed) completedMTD FROM tbl_AutomationScorecard
 where year(loaded) = year('''+@sat+''') and month(loaded) = month('''+@sat+''') and loaded<='''+@sat+''' and coalesce(completed,'''')!=''''
 group by process_name,lob--,loaded
 ) mtd on ytd.process_name=mtd.process_name and ytd.lob=mtd.lob
 )t

insert into #t1a
 SELECT LOB,PROCESS_NAME,1 seq,['+@sun+'],['+@mon+'],['+@tue+'],['+@wed+'],['+@thu+'],['+@fri+'],['+@sat+'],0,0,0 from (
 SELECT PROCESS_NAME,LOB,loaded--,completed
 FROM tbl_AutomationScorecard
 WHERE loaded in ('''+@sun+''','''+@mon+''','''+@tue+''','''+@wed+''','''+@thu+''','''+@fri+''','''+@sat+''')
 ) as s
 PIVOT (count(loaded) for loaded in (['+@sun+'],['+@mon+'],['+@tue+'],['+@wed+'],['+@thu+'],['+@fri+'],['+@sat+'])
 ) as pvt

insert into #t2a
 SELECT LOB,PROCESS_NAME,2 seq,['+@sun+'],['+@mon+'],['+@tue+'],['+@wed+'],['+@thu+'],['+@fri+'],['+@sat+'],0,0,0 from (
 SELECT PROCESS_NAME,LOB,completed--,completed
 FROM tbl_AutomationScorecard
 WHERE loaded in ('''+@sun+''','''+@mon+''','''+@tue+''','''+@wed+''','''+@thu+''','''+@fri+''','''+@sat+''')
 ) as s
 PIVOT (count(completed) for completed in (['+@sun+'],['+@mon+'],['+@tue+'],['+@wed+'],['+@thu+'],['+@fri+'],['+@sat+'])
 ) as pvt

insert #t1
 select x.LOB,'' ''+x.PROCESS_NAME,x.seq,['+@sun+'],['+@mon+'],['+@tue+'],['+@wed+'],['+@thu+'],['+@fri+'],['+@sat+'],t.WeekSum,x.MTD,x.YTD
 from #t1a t
 right join #loadedXTD x on t.PROCESS_NAME=x.PROCESS_NAME

insert #t2
 select x.LOB,'' ''+x.PROCESS_NAME,x.seq,['+@sun+'],['+@mon+'],['+@tue+'],['+@wed+'],['+@thu+'],['+@fri+'],['+@sat+'],t.WeekSum,x.MTD,x.YTD
 from #t2a t
 right join #completedXTD x on t.PROCESS_NAME=x.PROCESS_NAME

--update #t1 set MTD=x.MTD,YTD=x.YTD
 --from #t1 t join #loadedXTD x on t.PROCESS_NAME=x.PROCESS_NAME

--update #t2 set MTD=x.MTD,YTD=x.YTD
 --from #t2 t join #completedXTD x on t.PROCESS_NAME=x.PROCESS_NAME

--t1/t2 weeksum
 --update #t0 set WeekSum = ['+@sun+']+['+@mon+']+['+@tue+']+['+@wed+']+['+@thu+']+['+@fri+']+['+@sat+'];
 update #t1 set WeekSum = ['+@sun+']+['+@mon+']+['+@tue+']+['+@wed+']+['+@thu+']+['+@fri+']+['+@sat+'];
 update #t2 set WeekSum = ['+@sun+']+['+@mon+']+['+@tue+']+['+@wed+']+['+@thu+']+['+@fri+']+['+@sat+'];

insert into #t0
 select t1.LOB,t1.PROCESS_NAME,0 seq,
 case t1.['+@sun+'] when 0 then '''' else cast(t2.['+@sun+']*1.0/t1.['+@sun+']*1.0 as varchar) end ['+@sun+'],
 case t1.['+@mon+'] when 0 then '''' else cast(t2.['+@mon+']*1.0/t1.['+@mon+']*1.0 as varchar) end ['+@mon+'],
 case t1.['+@tue+'] when 0 then '''' else cast(t2.['+@tue+']*1.0/t1.['+@tue+']*1.0 as varchar) end ['+@tue+'],
 case t1.['+@wed+'] when 0 then '''' else cast(t2.['+@wed+']*1.0/t1.['+@wed+']*1.0 as varchar) end ['+@wed+'],
 case t1.['+@thu+'] when 0 then '''' else cast(t2.['+@thu+']*1.0/t1.['+@thu+']*1.0 as varchar) end ['+@thu+'],
 case t1.['+@fri+'] when 0 then '''' else cast(t2.['+@fri+']*1.0/t1.['+@fri+']*1.0 as varchar) end ['+@fri+'],
 case t1.['+@sat+'] when 0 then '''' else cast(t2.['+@sat+']*1.0/t1.['+@sat+']*1.0 as varchar) end ['+@sat+'],
 case t1.WeekSum when 0 then '''' else cast(t2.WeekSum*1.0/t1.WeekSum*1.0 as varchar) end WeekSum,
 case t1.MTD when 0 then '''' else cast(t2.MTD*1.0/t1.MTD*1.0 as varchar) end MTD,
 case t1.YTD when 0 then '''' else cast(t2.YTD*1.0/t1.YTD*1.0 as varchar) end YTD
 from #t1 T1 full outer join #t2 T2 on t1.LOB=t2.LOB and t1.PROCESS_NAME=t2.PROCESS_NAME;

insert into #tmp
 select * from (
 select * from #t1
 union
 select * from #t2
 union
 select * from #t0
 )t
 --update #tmp set WeekSum = ['+@sun+']+['+@mon+']+['+@tue+']+['+@wed+']+['+@thu+']+['+@fri+']+['+@sat+'];

--insert #out(lob,process_name,seq) select ''-'' LOB,''Total Back Office'' process_name,''-'' seq
 --sum all

-- generate report table start -----------------------------------------------
 --Total Back Office
 --total t1
 --lvl,LOB ,PROCESS_NAME ,seq ,[2018-04-29] ,[2018-04-30] ,[2018-05-01] ,[2018-05-02] ,[2018-05-03] ,[2018-05-04] ,[2018-05-05] ,WeekSum ,MTD ,YTD
 insert #out
 select 1 lvl,'''' LOB,'''' PROCESS_NAME,1 seq,
 cast(sum(['+@sun+']) as varchar) ['+@sun+'],
 cast(sum(['+@mon+']) as varchar) ['+@mon+'],
 cast(sum(['+@tue+']) as varchar) ['+@tue+'],
 cast(sum(['+@wed+']) as varchar) ['+@wed+'],
 cast(sum(['+@thu+']) as varchar) ['+@thu+'],
 cast(sum(['+@fri+']) as varchar) ['+@fri+'],
 cast(sum(['+@sat+']) as varchar) ['+@sat+'],
 cast(sum(WeekSum) as varchar) WeekSum,
 cast(sum(MTD) as varchar) MTD,
 cast(sum(YTD) as varchar) YTD
 from #t1
 --total t2
 insert #out
 select 1 lvl,'''' LOB,'''' PROCESS_NAME,2 seq,
 cast(sum(['+@sun+']) as varchar) ['+@sun+'],
 cast(sum(['+@mon+']) as varchar) ['+@mon+'],
 cast(sum(['+@tue+']) as varchar) ['+@tue+'],
 cast(sum(['+@wed+']) as varchar) ['+@wed+'],
 cast(sum(['+@thu+']) as varchar) ['+@thu+'],
 cast(sum(['+@fri+']) as varchar) ['+@fri+'],
 cast(sum(['+@sat+']) as varchar) ['+@sat+'],
 cast(sum(WeekSum) as varchar) WeekSum,
 cast(sum(MTD) as varchar) MTD,
 cast(sum(YTD) as varchar) YTD
 from #t2

insert #out
 --total percent
 select 1 lvl,'''' LOB,'''' PROCESS_NAME,3 seq,

case sum(t1.['+@sun+']) when 0 then '''' else cast(sum(t2.['+@sun+'])*1.0/sum(t1.['+@sun+'])*1.0 as varchar) end ['+@sun+'],
 case sum(t1.['+@mon+']) when 0 then '''' else cast(sum(t2.['+@mon+'])*1.0/sum(t1.['+@mon+'])*1.0 as varchar) end ['+@mon+'],
 case sum(t1.['+@tue+']) when 0 then '''' else cast(sum(t2.['+@tue+'])*1.0/sum(t1.['+@tue+'])*1.0 as varchar) end ['+@tue+'],
 case sum(t1.['+@wed+']) when 0 then '''' else cast(sum(t2.['+@wed+'])*1.0/sum(t1.['+@wed+'])*1.0 as varchar) end ['+@wed+'],
 case sum(t1.['+@thu+']) when 0 then '''' else cast(sum(t2.['+@thu+'])*1.0/sum(t1.['+@thu+'])*1.0 as varchar) end ['+@thu+'],
 case sum(t1.['+@fri+']) when 0 then '''' else cast(sum(t2.['+@fri+'])*1.0/sum(t1.['+@fri+'])*1.0 as varchar) end ['+@fri+'],
 case sum(t1.['+@sat+']) when 0 then '''' else cast(sum(t2.['+@sat+'])*1.0/sum(t1.['+@sat+'])*1.0 as varchar) end ['+@sat+'],
 case sum(t1.WeekSum) when 0 then '''' else cast(sum(t2.WeekSum)*1.0/sum(t1.WeekSum)*1.0 as varchar) end WeekSum,
 case sum(t1.MTD) when 0 then '''' else cast(sum(t2.MTD)*1.0/sum(t1.MTD)*1.0 as varchar) end MTD,
 case sum(t1.YTD) when 0 then '''' else cast(sum(t2.YTD)*1.0/sum(t1.YTD)*1.0 as varchar) end YTD
 from #t1 T1 join #t2 T2 on t1.LOB=t2.LOB and t1.PROCESS_NAME=t2.PROCESS_NAME
 group by t1.seq,t2.seq;

--------------------
 --sub total LOB
 insert #out
 --sub total LOB 1
 select 2 lvl,LOB,'''' PROCESS_NAME,1 seq,
 cast(sum(['+@sun+']) as varchar) ['+@sun+'],
 cast(sum(['+@mon+']) as varchar) ['+@mon+'],
 cast(sum(['+@tue+']) as varchar) ['+@tue+'],
 cast(sum(['+@wed+']) as varchar) ['+@wed+'],
 cast(sum(['+@thu+']) as varchar) ['+@thu+'],
 cast(sum(['+@fri+']) as varchar) ['+@fri+'],
 cast(sum(['+@sat+']) as varchar) ['+@sat+'],
 cast(sum(WeekSum) as varchar) WeekSum,
 cast(sum(MTD) as varchar) MTD,
 cast(sum(YTD) as varchar) YTD
 from #t1 group by LOB
 union
 --sub total LOB 2
 select 2 lvl,LOB,'''' PROCESS_NAME,2 seq,
 cast(sum(['+@sun+']) as varchar) ['+@sun+'],
 cast(sum(['+@mon+']) as varchar) ['+@mon+'],
 cast(sum(['+@tue+']) as varchar) ['+@tue+'],
 cast(sum(['+@wed+']) as varchar) ['+@wed+'],
 cast(sum(['+@thu+']) as varchar) ['+@thu+'],
 cast(sum(['+@fri+']) as varchar) ['+@fri+'],
 cast(sum(['+@sat+']) as varchar) ['+@sat+'],
 cast(sum(WeekSum) as varchar) WeekSum,
 cast(sum(MTD) as varchar) MTD,
 cast(sum(YTD) as varchar) YTD
 from #t2 group by LOB
 union
 --sub total LOB percent
 select 2 lvl,t1.LOB,'''' PROCESS_NAME,3 seq,
 case t1.['+@sun+'] when 0 then '''' else cast(t2.['+@sun+']*1.0/t1.['+@sun+']*1.0 as varchar) end ['+@sun+'],
 case t1.['+@mon+'] when 0 then '''' else cast(t2.['+@mon+']*1.0/t1.['+@mon+']*1.0 as varchar) end ['+@mon+'],
 case t1.['+@tue+'] when 0 then '''' else cast(t2.['+@tue+']*1.0/t1.['+@tue+']*1.0 as varchar) end ['+@tue+'],
 case t1.['+@wed+'] when 0 then '''' else cast(t2.['+@wed+']*1.0/t1.['+@wed+']*1.0 as varchar) end ['+@wed+'],
 case t1.['+@thu+'] when 0 then '''' else cast(t2.['+@thu+']*1.0/t1.['+@thu+']*1.0 as varchar) end ['+@thu+'],
 case t1.['+@fri+'] when 0 then '''' else cast(t2.['+@fri+']*1.0/t1.['+@fri+']*1.0 as varchar) end ['+@fri+'],
 case t1.['+@sat+'] when 0 then '''' else cast(t2.['+@sat+']*1.0/t1.['+@sat+']*1.0 as varchar) end ['+@sat+'],
 case t1.WeekSum when 0 then '''' else cast(t2.WeekSum/t1.WeekSum as varchar) end WeekSum,
 case t1.MTD when 0 then '''' else cast(t2.MTD/t1.MTD as varchar) end MTD,
 case t1.YTD when 0 then '''' else cast(t2.YTD/t1.YTD as varchar) end YTD
 from (
 select LOB,sum(['+@sun+'])['+@sun+'],sum(['+@mon+'])['+@mon+'],sum(['+@tue+'])['+@tue+'],sum(['+@wed+'])['+@wed+'],sum(['+@thu+'])['+@thu+'],sum(['+@fri+'])['+@fri+'],sum(['+@sat+'])['+@sat+'],sum(WeekSum)WeekSum,sum(MTD)MTD,sum(YTD)YTD
 from #t1 group by LOB
 ) t1 join (
 select LOB,sum(['+@sun+'])['+@sun+'],sum(['+@mon+'])['+@mon+'],sum(['+@tue+'])['+@tue+'],sum(['+@wed+'])['+@wed+'],sum(['+@thu+'])['+@thu+'],sum(['+@fri+'])['+@fri+'],sum(['+@sat+'])['+@sat+'],sum(WeekSum)WeekSum,sum(MTD)MTD,sum(YTD)YTD
 from #t2 group by LOB
 ) t2 on t1.LOB=t2.LOB

insert #out (lvl,LOB,PROCESS_NAME,seq,['+@sun+'] ,['+@mon+'] ,['+@tue+'] ,['+@wed+'] ,['+@thu+'] ,['+@fri+'] ,['+@sat+'],WeekSum ,MTD ,YTD)
 select 3,LOB,PROCESS_NAME,seq,['+@sun+'] ,['+@mon+'] ,['+@tue+'] ,['+@wed+'] ,['+@thu+'] ,['+@fri+'] ,['+@sat+'],WeekSum ,MTD ,YTD
 from #tmp
 order by LOB desc,PROCESS_NAME,seq;

insert into #rpt (lvl,LOB,PROCESS_NAME,seq,['+@sun+'] ,['+@mon+'] ,['+@tue+'] ,['+@wed+'] ,['+@thu+'] ,['+@fri+'] ,['+@sat+'] ,WeekSum ,MTD ,YTD)
 select lvl,LOB,PROCESS_NAME,seq,['+@sun+'] ,['+@mon+'] ,['+@tue+'] ,['+@wed+'] ,['+@thu+'] ,['+@fri+'] ,['+@sat+'] ,WeekSum ,MTD ,YTD
 from #out order by LOB,PROCESS_NAME,SEQ

-------------------------------------------------------------

declare @minid int,@lob varchar(255),@process varchar(255);
 --select @lob=LOB from #rpt where id=40

SET identity_insert #rpt on

insert into #rpt (id,PROCESS_NAME)
 select id-1 id,
 case when lob='''' then ''Total Back Office'' else '' ''+lob+'' Back Office'' end
 from (select min(id) id,lob from #rpt group by lob)t

insert into #rpt (id,PROCESS_NAME)
 select id+25 id,
 case when lob='''' then ''Total Back Office'' else '' ''+lob+'' Processes'' end
 from (select min(id) id,lob from #rpt group by lob having lob!='''')t

----table header
 --insert into #rpt (id,lvl,LOB,PROCESS_NAME,seq,['+@sun+'] ,['+@mon+'] ,['+@tue+'] ,['+@wed+'] ,['+@thu+'] ,['+@fri+'] ,['+@sat+'] ,WeekSum ,MTD ,YTD) values
 --(-1,''Level'',''LOB'',''PROCESS_NAME'',''seq'',''['+@sun+']'' ,''['+@mon+']'',''['+@tue+']'',''['+@wed+']'',''['+@thu+']'',''['+@fri+']'',''['+@sat+']'',''WeekSum'',''MTD'' ,''YTD'')

SET identity_insert #rpt off

update #rpt set process_name='' Volume sent to robot'' where seq=''1''
 update #rpt set process_name='' Volume worked by robot'' where seq=''2''
 update #rpt set process_name='' % automated'' where seq=''3''

update #rpt set ['+@sun+'] = case when ['+@sun+']!='''' then REPLACE(CONVERT(varchar, (CAST(['+@sun+'] AS money)), 1), ''.00'', '''') end where seq in (''1'',''2'')
 update #rpt set ['+@mon+'] = case when ['+@mon+']!='''' then REPLACE(CONVERT(varchar, (CAST(['+@mon+'] AS money)), 1), ''.00'', '''') end where seq in (''1'',''2'')
 update #rpt set ['+@tue+'] = case when ['+@tue+']!='''' then REPLACE(CONVERT(varchar, (CAST(['+@tue+'] AS money)), 1), ''.00'', '''') end where seq in (''1'',''2'')
 update #rpt set ['+@wed+'] = case when ['+@wed+']!='''' then REPLACE(CONVERT(varchar, (CAST(['+@wed+'] AS money)), 1), ''.00'', '''') end where seq in (''1'',''2'')
 update #rpt set ['+@thu+'] = case when ['+@thu+']!='''' then REPLACE(CONVERT(varchar, (CAST(['+@thu+'] AS money)), 1), ''.00'', '''') end where seq in (''1'',''2'')
 update #rpt set ['+@fri+'] = case when ['+@fri+']!='''' then REPLACE(CONVERT(varchar, (CAST(['+@fri+'] AS money)), 1), ''.00'', '''') end where seq in (''1'',''2'')
 update #rpt set ['+@sat+'] = case when ['+@sat+']!='''' then REPLACE(CONVERT(varchar, (CAST(['+@sat+'] AS money)), 1), ''.00'', '''') end where seq in (''1'',''2'')
 update #rpt set WeekSum = case when WeekSum!='''' then REPLACE(CONVERT(varchar, (CAST(WeekSum AS money)), 1), ''.00'', '''') end where seq in (''1'',''2'')
 update #rpt set MTD = case when MTD!='''' then REPLACE(CONVERT(varchar, (CAST(MTD AS money)), 1), ''.00'', '''') end where seq in (''1'',''2'')
 update #rpt set YTD = case when YTD!='''' then REPLACE(CONVERT(varchar, (CAST(YTD AS money)), 1), ''.00'', '''') end where seq in (''1'',''2'')

update #rpt set ['+@sun+'] = case when ['+@sun+']!='''' then Cast(Cast(Cast(['+@sun+'] as decimal(12,4))*100.0 as decimal(18,2)) as varchar(15)) + '' %'' end where seq=''0'' or seq=''3''
 update #rpt set ['+@mon+'] = case when ['+@mon+']!='''' then Cast(Cast(Cast(['+@mon+'] as decimal(12,4))*100.0 as decimal(18,2)) as varchar(15)) + '' %'' end where seq=''0'' or seq=''3''
 update #rpt set ['+@tue+'] = case when ['+@tue+']!='''' then Cast(Cast(Cast(['+@tue+'] as decimal(12,4))*100.0 as decimal(18,2)) as varchar(15)) + '' %'' end where seq=''0'' or seq=''3''
 update #rpt set ['+@wed+'] = case when ['+@wed+']!='''' then Cast(Cast(Cast(['+@wed+'] as decimal(12,4))*100.0 as decimal(18,2)) as varchar(15)) + '' %'' end where seq=''0'' or seq=''3''
 update #rpt set ['+@thu+'] = case when ['+@thu+']!='''' then Cast(Cast(Cast(['+@thu+'] as decimal(12,4))*100.0 as decimal(18,2)) as varchar(15)) + '' %'' end where seq=''0'' or seq=''3''
 update #rpt set ['+@fri+'] = case when ['+@fri+']!='''' then Cast(Cast(Cast(['+@fri+'] as decimal(12,4))*100.0 as decimal(18,2)) as varchar(15)) + '' %'' end where seq=''0'' or seq=''3''
 update #rpt set ['+@sat+'] = case when ['+@sat+']!='''' then Cast(Cast(Cast(['+@sat+'] as decimal(12,4))*100.0 as decimal(18,2)) as varchar(15)) + '' %'' end where seq=''0'' or seq=''3''
 update #rpt set WeekSum = case when WeekSum!='''' then Cast(Cast(Cast(WeekSum as decimal(12,4))*100.0 as decimal(18,2)) as varchar(15)) + '' %'' end where seq=''0'' or seq=''3''
 update #rpt set MTD = case when MTD!='''' then Cast(Cast(Cast(MTD as decimal(12,4))*100.0 as decimal(18,2)) as varchar(15)) + '' %'' end where seq=''0'' or seq=''3''
 update #rpt set YTD = case when YTD!='''' then Cast(Cast(Cast(YTD as decimal(12,4))*100.0 as decimal(18,2)) as varchar(15)) + '' %'' end where seq=''0'' or seq=''3''

--select id,lvl,LOB,PROCESS_NAME,seq,['+@sun+'] ,['+@mon+'] ,['+@tue+'] ,['+@wed+'] ,['+@thu+'] ,['+@fri+'] ,['+@sat+'] ,WeekSum ,MTD ,YTD
 --from #rpt order by id

--select LOB,PROCESS_NAME,['+@sun+'] ,['+@mon+'] ,['+@tue+'] ,['+@wed+'] ,['+@thu+'] ,['+@fri+'] ,['+@sat+'] ,WeekSum ,MTD ,YTD
 --from #rpt order by id

insert into dbo.tbl_Email_Report(ReportID,EmailTo,Subject, Report)
 select '''+cast(@reportID as varchar)+''','''+@emailList+''','''+@subject+''', replace(stuff(
 (
 select char(13)+PROCESS_NAME+char(9)+['+@sun+']+char(9)+['+@mon+']+char(9)+['+@tue+']+char(9)+['+@wed+']+char(9)+['+@thu+']+char(9)+['+@fri+']+char(9)+['+@sat+']+char(9)+WeekSum+char(9)+MTD+char(9)+YTD
 from
 (
 select ''-1'' id,''Level'' lvl,''LOB'' LOB,''BackOffice Automation''PROCESS_NAME,''seq''seq,''['+@sun+']''['+@sun+'],''['+@mon+']''['+@mon+'],''['+@tue+']''['+@tue+'],''['+@wed+']''['+@wed+'],''['+@thu+']''['+@thu+'],''['+@fri+']''['+@fri+'],''['+@sat+']''['+@sat+'],''WeekSum''WeekSum,''MTD''MTD,''YTD''YTD
 union
 select id,lvl,LOB,PROCESS_NAME,seq,['+@sun+'] ,['+@mon+'] ,['+@tue+'] ,['+@wed+'] ,['+@thu+'] ,['+@fri+'] ,['+@sat+'] ,WeekSum ,MTD ,YTD
 --select id,lvl,LOB,replace(PROCESS_NAME,'','','';''),seq,['+@sun+'] ,['+@mon+'] ,['+@tue+'] ,['+@wed+'] ,['+@thu+'] ,['+@fri+'] ,['+@sat+'] ,WeekSum ,MTD ,YTD
 from #rpt
 ) rpt
 order by id for xml path('''')
 )
 ,1,6,''''),'' '',char(13))
 '
 --select @sql
 exec(@sql)

/* Added Sample Email Body*/
 SET @report = 'Hello Team,     Please find the attached Automation Scorecard report for the period '+ @sun +' to '+ @sat + '. Thanks, OPT RPA SUPPORT  '

declare @bcp nvarchar(2000)
 set @bcp ='bcp "select top 1 Report from BusinessSuite.dbo.tbl_Email_Report where ReportID='''+cast(@reportID as varchar)+''' order by rptid desc" queryout '+@filename+' -c -t -T'
 exec xp_cmdshell @bcp

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = null,
 @recipients = @emailList,
 @subject = @subject,
 @body = @report,
 @importance = 'HIGH',
 @body_format ='HTML',
 @file_attachments = @filename
 GO

--usp_Email_AutomationScorecard
 --select * from tbl_Email_Report

 157 total views

Author: Albert

Leave a Reply