create or replace procedure nzfsdata."MONTHLYREPORT_LOADDATA"
as
counter pls_integer;
StartDate date;
EndDate date;
CPN pls_integer;
PFA pls_integer;
MonthYear varchar2(20);
PDA pls_integer;
Beatlist pls_integer;
DeploymentData pls_integer;
v_Callsigns pls_integer;
v_vehicles pls_integer;
v_PagerEntries pls_integer;
v_PagerGroups pls_integer;
Callsigns pls_integer;
DataSpatialZone pls_integer;
DeploymentZone pls_integer;
SOP_HtmlPages pls_integer;
SOP_TablePages pls_integer;
WebStatic pls_integer;
TheZone pls_integer;
MySql varchar2(4000);
ColumnName varchar2(50);
i pls_integer;
WhereGoes pls_integer;
WhereFrom pls_integer;
TheDate date;
TheMonth pls_integer;
YearMonth varchar2(7);
begin
MySql := 'Truncate table nzfsdata.MonthlyReport';
execute immediate (MySql);
for counter in 1 .. 14
loop
--------------------------------------------------
-- Get the dates
TheDate:= sysdate;
StartDate:= trunc(add_months(TheDate,-counter), 'MONTH');
EndDate:= trunc(last_day(add_months(TheDate,-counter))+1);
MonthYear:= to_char(StartDate,'mm/YYYY');
--------------------------------------------------
-- CPN data
select
coalesce(count(*),0)
into CPN
from E3BOB2.ACTIONED_CHANGE_REQUESTS
where
to_Datetime >= StartDate
and to_datetime < EndDate
and upper(Datatype) IN ('COMMONPLACE','COMMONALIAS')
and upper(RequestType) IN ('NEW DATA','MODIFY EXISTING','DELETION');
--------------------------------------------------
-- PFA data
select
coalesce(count(*),0)
into PFA
from E3BOB2.ACTIONED_CHANGE_REQUESTS
where
to_Datetime >= StartDate
and to_datetime < EndDate
and upper(Requesttype) IN ('NEW PFA','NEW LINK PFA','MODIFY PFA','DELETION PFA');
--------------------------------------------------
-- Deployment data
-- PDA data
select
coalesce(count(*),0)
into PDA
from
(
select distinct
list_id
,modified_date
,modified_time
from
nzfsdata.pda_hist
where
Action <> 'DELETE'
and modified_date >= StartDate
and modified_date < EndDate
);
-- Get Beatlists
select
coalesce(count(*),0)
into BeatList
from nzfsdata.Beatlists_hist
where
substr(Action,1,6) <> 'DELETE'
and tycod = 'STRU'
and Beatorder = 0
and modified_date >= StartDate
and modified_date < EndDate;
-- Add together
DeploymentData := PDA + BeatList;
--------------------------------------------------
-- Callsigns and Paging
-- Callsigns
select
coalesce(count(*),0)
into v_callsigns
from nzfsdata.callsigns
where
(
effectivefrom between startdate and enddate
and effectiveto between startdate and enddate
)
or
(
effectivefrom between startdate and enddate
and effectiveto is null
);
-- Vehicles
select
coalesce(count(*),0)
into v_vehicles
from nzfsdata.vehicles
where
(
effectivefrom between startdate and enddate
and effectiveto between startdate and enddate
)
or
(
effectivefrom between startdate and enddate
and effectiveto is null
);
-- PagerEntries
select
coalesce(count(*),0)
into v_PagerEntries
from nzfsdata.pagerentries
where
(
effectivefrom between startdate and enddate
and effectiveto between startdate and enddate
)
or
(
effectivefrom between startdate and enddate
and effectiveto is null
);
-- PagerGroups
select
coalesce(count(*),0)
into v_PagerGroups
from nzfsdata.pagergroups
where
(
effectivefrom between startdate and enddate
and effectiveto between startdate and enddate
)
or
( effectivefrom between startdate and enddate
and effectiveto is null
);
-- Add together
Callsigns:= v_callsigns + v_vehicles + v_PagerEntries + v_PagerGroups;
--------------------------------------------------
-- ZONES
select
coalesce(sum(coalesce(new_zone,0)+coalesce(mod_zone,0)+coalesce(del_zone,0)),0)
into DataSpatialZone
from e3bob2.zone_version_history
where
LiveDate >= StartDate
and LiveDate < EndDate;
select
coalesce(count(*),0)
into DeploymentZone
from nzfsdata.zone_hist
where
modified_date >= StartDate
and modified_date < EndDate
and action <> 'UPDATE - NEW';
-- Add together
TheZone:= DataSpatialZone + DeploymentZone;
--------------------------------------------------
-- WEB - STATIC - PAGES
-- This is where the number of SOP pages that have changed is recorded.
-- Robocopy copies the files changed from Prod to Stage.
-- A Robocopy log file shows the number of files that have changed.
-- Sql Loader puts the log file into table MONTHLYREPORT_ROBOCOPY
-- This is done by a daily job.
select
coalesce(sum(copied),0)
into SOP_HtmlPages
from nzfsdata.monthlyreport_robocopy
where
to_date(processed,'yyyy-mm-dd') >= StartDate
and to_date(processed,'yyyy-mm-dd') < EndDate;
select
count(*)
into SOP_TablePages
from sys.dba_audit_trail dat
where
dat.owner='HAZOWN'
and dat.obj_name in ('BL_BUTTON_LIST','SOP','Z_ZONE','I_INFO','N_NOTIFY','R_RESPOND','FA_FIREAUTH','GA_GASAUTH','LA_LOCALAUTH','PA_POWERAUTH')
and dat.timestamp >= StartDate
and dat.timestamp < EndDate;
-- Add together the SOP counts
webstatic:= SOP_HtmlPages + SOP_TablePages;
--------------------------------------------------
-- INSERT INTO TABLE MONTHLREPORT
-- Insert ID and Description names
if counter = 1 then
insert into MonthlyReport(ID,Description) values (1,'Date Headers');
insert into MonthlyReport(ID,Description) values (2,'CPN data');
insert into MonthlyReport(ID,Description) values (3,'PFA data');
insert into MonthlyReport(ID,Description) values (4,'Deployment data');
insert into MonthlyReport(ID,Description) values (5,'Callsign '||'&'||' Paging');
insert into MonthlyReport(ID,Description) values (6,'Zones');
insert into MonthlyReport(ID,Description) values (7,'Web - Static Pages');
end if;
-- The ColumnName to use
ColumnName:= 'M'||to_char(counter);
-- Date headers
mySql := 'update MonthlyReport set '||ColumnName||' = '''||MonthYear||''' where ID = 1';
execute immediate (mySql);
-- CPN Data
mySql := 'update MonthlyReport set '||ColumnName||' = '''||CPN||''' where ID = 2';
execute immediate (mySql);
-- PFA Data
mySql := 'update MonthlyReport set '||ColumnName||' = '''||PFA||''' where ID = 3';
execute immediate (mySql);
-- Deployment Data
mySql := 'update MonthlyReport set '||ColumnName||' = '''||DeploymentData||''' where ID = 4';
execute immediate (mySql);
-- Callsign and Paging
mySql := 'update MonthlyReport set '||ColumnName||' = '''||Callsigns||''' where ID = 5';
execute immediate (mySql);
-- Zones
mySql := 'update MonthlyReport set '||ColumnName||' = '''||TheZone||''' where ID = 6';
execute immediate (mySql);
-- Web - Static Pages
mySql := 'update MonthlyReport set '||ColumnName||' = '''||Webstatic||''' where ID = 7';
execute immediate (mysql);
end loop;
commit;
end;
/
