use workdb
go
set nocount on
go

-- workdb.dbo.pclist MUST EXIST PRIOR TO THIS SCRIPT RUNNING -------------------------------
--select count(*) from workdb.dbo.pcslist
-- exec dbo.refresh_pcslist
-- exec dbo.refreshChartA


------------------------ SERVO LOOP DILIGENCE QUERY ----------------------
if object_id('tempdb.dbo.#servo') is not null
    drop table #servo
go
create table #servo (
    fldMeterType varchar(1) null,
    txModeRaw tinyint null,
    txModeText varchar(50) null,
    txRateMinutes int null,
    fsk varchar(5) null,
    Slot varchar(3) null,
    countOfBuddyRepeatEnabled int null,
    meterCount int null,
    avgRaw decimal(5,1) null,
    avgCommStatsSNR decimal(5,1) null,
    avgSNR_Corr decimal(5,1) null,
    percentStale24 decimal(5,1) null,
    percentStale48 decimal(5,1) null,
    countOfStale24 int null,
    countOfStale48 int null,
    region varchar(25) null,
    servoBucket int null,
    reportBucket varchar(25) null
)
go

;with
excludedMeters as
(
    select distinct fldRepId
    from workdb.dbo.chartAMeters
    --where section in ('imA','imB','imC','imD','badMetroRIS0','badMetroKWH0','inPF-TMP-96','stale999')
   where section in ('notone')
),
groupedResults as
(
    select p.fldMeterType, p.region, p.txMode, p.txRateMinutes, p.using13fsk, p.isPCS,
        sum(case when p.rxMode<3 then 1 else 0 end) 'buddyRepeatEnabled',
        avg(p.commstatsSNR) 'avgCommStatsSNR', avg(p.avgSNR_Corr) 'avgSNRCorr',
        count(*) 'meterCount', avg(p.rawtput) 'avgRaw',
        sum(case when p.hoursReadStale>24 then 1 else 0 end) 'countOfStale24',
        sum(case when p.hoursReadStale>48 then 1 else 0 end) 'countOfStale48'
    from workdb.dbo.pcslist p
    left join excludedMeters em on (p.fldRepId=em.fldRepId)
    where em.fldRepId is null
    group by p.fldMeterType, p.region, p.txMode, p.txRateMinutes, p.using13fsk, p.isPCS
)
insert into #servo (meterCount, fldMeterType, region, txModeRaw, txModeText, txRateMinutes,
    fsk, Slot, countOfBuddyRepeatEnabled, avgCommStatsSNR, avgSNR_Corr, avgRaw,
    percentStale24, percentStale48, countOfStale24, countOfStale48)
select meterCount, fldMeterType, region, txMode 'txModeRaw',
    CASE txMode
		WHEN 0 THEN 'Normal Mode'
		WHEN 1 THEN 'Message Pass'
		WHEN 2 THEN 'Boost Mode'
		WHEN 3 THEN 'Normal, 1/2 Baud Rate'
		WHEN 4 THEN 'mPass / Normal Mix (1:1)'
		WHEN 5 THEN 'mPass / Normal Mix (1:2)'
		WHEN 6 THEN 'mPass / Normal Mix (1:3)'
		WHEN 7 THEN 'mPass / Normal Mix (1:4)'
		WHEN 8 THEN 'mPass / Normal Mix (1:5)'
		WHEN 9 THEN 'mPass / Normal Mix (1:8)'
		WHEN 10 THEN 'mPass / Normal Mix (1:16)'
		WHEN 11 THEN 'Boost / Normal Mix (1:1)'
		WHEN 12 THEN 'Boost / Normal Mix (1:2)'
		WHEN 13 THEN 'Boost / Normal Mix (1:4)'
		WHEN 14 THEN 'Boost / Normal Mix (1:8)'
		WHEN 15 THEN 'Boost / Normal Mix (1:16)'
        else 'undefined' end 'txModeText',
    txRateMinutes,
    case when using13fsk=0 then '7fsk' else '13fsk' end 'fsk',
    case when isPCS=0 then 'MAS' else 'PCS' end 'Slot',
    buddyRepeatEnabled, cast(avgCommStatsSNR as decimal(5,1)) 'avgCommStatsSNR',
    cast(avgSNRCorr as decimal(5,1)) 'avgSNR_Corr', cast(avgRaw as decimal(5,1)) 'avgRaw',
    cast((cast(countOfStale24 as float)/meterCount)*100 as decimal(5,1)) 'percentStale24',
    cast((cast(countOfStale48 as float)/meterCount)*100 as decimal(5,1)) 'percentStale48',
    countOfStale24, countOfStale48
from groupedResults
--order by isPCS, fldMeterType, txMode, txRateMinutes

--select * from #servo

-- assign buckets per Servo Loop Document
update #servo
set servoBucket=1 where txModeRaw=0 and txRateMinutes=720 and fsk='13fsk' and Slot='MAS'

update #servo
set servoBucket=2 where txModeRaw=0 and txRateMinutes=360 and fsk='13fsk' and Slot='MAS'

update #servo
set servoBucket=3 where txModeRaw=0 and txRateMinutes=240 and fsk='7fsk' and Slot='MAS'

update #servo
set servoBucket=4 where txModeRaw=4 and txRateMinutes=720 and fsk='7fsk' and Slot='MAS'

update #servo
set servoBucket=5 where txModeRaw=7 and txRateMinutes=240 and fsk='7fsk' and Slot='MAS'

update #servo
set servoBucket=6 where txModeRaw=7 and txRateMinutes=120 and fsk='7fsk' and Slot='MAS'

update #servo
set servoBucket=7 where txModeRaw=7 and txRateMinutes=60 and fsk='7fsk' and Slot='MAS'

update #servo
set servoBucket=50 where Slot='PCS'

update #servo
set servoBucket=51 where servoBucket is null

update #servo
set reportBucket = case when servoBucket in (1,2) then 'I & II'
                        when servoBucket = 3 then 'III'
                        when servoBucket = 4 then 'IV'
                        when servoBucket = 5 then 'V'
                        when servoBucket in (6,7) then 'VI & VII'
                        when servoBucket = 50 then 'PCS'
                        else 'Non-Compliant' end

-- combined report buckets per Britton
declare @reportBuckets table (
    reportBucket varchar(25),
    fldMeterType varchar(1),
    region varchar(10),
    reportBucketSort tinyint,
    fldMeterTypeSort tinyint,
    regionSort tinyint
)

insert into @reportBuckets (reportBucketSort, reportBucket, fldMeterTypeSort, fldMeterType, regionSort, region)
select *
from
(
select 1 as reportBucketSort,'I & II' as reportBucket
union all
select 2,'III'
union all
select 3,'IV'
union all
select 4,'V'
union all
select 5,'VI & VII'
union all
select 6,'PCS'
union all
select 7,'Non-Compliant'
) reportBuckets,
(
select 1 as fldMeterTypeSort,'E' as fldMeterType
union all
select 2,'A'
) meterTypes,
(
select 1 as regionSort, 'Central' as region
union all
select 3,'Southern'
union all
select 2,'Eastern'
union all
select 4,'Western'
) regions

--select * from @reportBuckets



/*
;with
perServoBucketStats as
(
    select fldMeterType, region, servoBucket, sum(meterCount) 'meterCount',
        sum(countOfStale24) 'countOfStale24', sum(countOfStale48) 'countOfStale48',
        avg(avgRaw) 'avgRaw', avg(avgCommStatsSNR) 'avgCommStatsSNR',
        avg(avgSNR_Corr) 'avgSNR_Corr', sum(countOfBuddyRepeatEnabled) 'countOfBuddyRepeatEnabled'
    from #servo
    group by fldMeterType, region, servoBucket
)
select s.fldMeterType, region, s.servoBucket, s.meterCount,
    cast(cast(countOfStale48 as float)/meterCount*100 as decimal(5,1)) 'percentStale48'
    from perServoBucketStats s
*/

;with
perReportBucketStats as
(
    select fldMeterType, region, reportBucket, sum(meterCount) 'meterCount',
        sum(countOfStale24) 'countOfStale24', sum(countOfStale48) 'countOfStale48',
        avg(avgRaw) 'avgRaw', avg(avgCommStatsSNR) 'avgCommStatsSNR',
        avg(avgSNR_Corr) 'avgSNR_Corr', sum(countOfBuddyRepeatEnabled) 'countOfBuddyRepeatEnabled'
    from #servo
    group by fldMeterType, region, reportBucket
),
regionalResults as
(
    select s.fldMeterType, region, s.reportBucket, s.meterCount,
        cast((meterCount-cast(countOfStale48 as float))/meterCount*100 as decimal(5,1)) 'percentStaleLT48'
        from perReportBucketStats s
    where region is not null
      and region != ''
)
select rb.fldMeterType, rb.region, rb.reportBucket, isnull(rr.meterCount,0) 'meterCount',
    case when percentStaleLT48 is null then 'N/A' else cast(percentStaleLT48 as varchar(10)) end 'percentStaleLT48'
from @reportBuckets rb
left join regionalResults rr
on rb.reportBucket=rr.reportBucket and rb.region=rr.region and rb.fldMeterType=rr.fldMeterType
order by rb.fldMeterTypeSort, rb.regionSort, rb.reportBucketSort

-- pulled from chartA results
-- see the dbo.refreshChartA procedure for definition of all these categories
select 'MissingSetup' as ExceptionCategory, count(*) 'meterCount'
    from workdb.dbo.chartAMeters
    where section='missingSetup'
union all
select '999 Hours Stale', count(*)
    from workdb.dbo.chartAMeters
    where section='stale999'
union all
select 'TamperOrPFandStale96hr', count(*)
    from workdb.dbo.chartAMeters
    where section='inPF-TMP-96'
union all
select 'ChartA_HWIssues',
    (select count(distinct fldRepId)
     from workdb.dbo.chartAMeters
     where section in ('imA','imB','imC','imD','badMetroRIS0','badMetroKWH0'))