/*---------------------------------------------------------------------------*/
/*                                                                           */
/* ADSspace                                                                  */
/*                                                                           */
/* This macro return a summary of tables in schema ADSDATA                   */
/*                                                                           */
/* Maintenance Log:                                                          */
/* 2014-05-14                                                                */
/*---------------------------------------------------------------------------*/

%macro adsspace (
    order     = ,       /*--- 'time, size or both/all' output options     ---*/
    excel     = NO,     /*--- 'YES' to create a excel ouput               ---*/
    email     = NO,     /*--- 'YES' to genarate an email                  ---*/
    maintenance = NO
    );

/*----------------------------------------------------------------------------*/
/* Set ASDP connection to database if you did not set ODBC info in ProdSAS    */
/*----------------------------------------------------------------------------*/

  /*
    %let ODBC_User    = <your-asdp-username>;
    %let ODBC_pw      = <your-asdp-password>;

    %let ODBC_PROD    = ASDP;
  */

/*----------------------------------------------------------------------------*/
/* Initialize parameters                                                      */
/*----------------------------------------------------------------------------*/

%let order = %QTRIM(%upcase(&order)) ;
  %if &order ^=  and
      &order ^= TIME and
	  &order ^= SIZE and
	  &order ^= BOTH and
	  &order ^= ALL
    %then %do;
      %put **************** invalid order option;
      %let order =;
	  %end;

  %if &order =  %then %do;
  options nomprint ;
  options nonotes ;
  %end;


%let excel = %upcase(&excel) ;
%let email = %upcase(&email) ;
%let maintenance = %upcase(&maintenance) ;
%let ODBC_User = %upcase(&ODBC_User) ;

%let adsdata_total_space=225;     /*Total space capacity in schema ADSDATA    */
%let adsdata_space_delimiter=5;   /*Space reserved for emergency uses         */

/*----------------------------------------------------------------------------*/
/* Start Job                                                                  */
/*----------------------------------------------------------------------------*/
%StartJob(client=ADSdata,job=ADSDATA_space_summary,storesaslog=no); run;

/*----------------------------------------------------------------------------*/
/*  Create required directories and allocate required files                   */
/*----------------------------------------------------------------------------*/
%let Out_data = &Databasesroot\&GBClient\&GBJobNam\;

/*----------------------------------------------------------------------------*/
/* Create output directory if it does not exist                               */
/*----------------------------------------------------------------------------*/
%CreateDirectory(&Out_data); run;

%alloclib(OUT_Data,&Out_Data.);

/*----------------------------------------------------------------------------*/
/* Load Qurey in Macro                                                        */
/*----------------------------------------------------------------------------*/
%macro SQLCode1;

select a.tabschema as schema_name,
       a.tabname as table_name,
       a.owner, a.type, a.create_time, a.stats_time, a.colcount,
	   card as rows_in_table,
       (float(a.fpages)*PAGESIZE/1024) as size_Kb,
       decimal(float(a.fpages)*PAGESIZE/(power(1024,3)),8,2) as size_Gb
    from syscat.tables a,
         syscat.tablespaces b
    where a.TBSPACEID=b.TBSPACEID and
          tabschema='ADSDATA'
    order by a.owner, a.tabname

%mend sqlcode1;


/*----------------------------------------------------------------------------*/
/* Run Qurey and creat SAS dataset with results                               */
/*----------------------------------------------------------------------------*/

proc sql;
    connect to DB2 (USER=&ODBC_User password= "&ODBC_pw." database=&ODBC_PROD );

      create table out_data.adsdata_spacesummary as
          select * from connection to db2
      (%sqlcode1);

      disconnect from db2;
quit;

/*----------------------------------------------------------------------------*/
/* Display necessary outputs                                                  */
/*----------------------------------------------------------------------------*/

proc sql noprint;
   select count(*), sum(size_gb), &adsdata_total_space -&adsdata_space_delimiter -sum(size_gb) into :ntable, :totalsize, :leftsize
   from out_data.adsdata_spacesummary;
quit;

proc sql noprint;
   select count(*), sum(size_gb) into :user_ntable, :user_totalsize
   from out_data.adsdata_spacesummary
   where owner = "&ODBC_User";
quit;



%put -----> A total of %QTRIM(&ntable) tables/views in Schema ADSDATA;
%put -----> User &ODBC_User has %QTRIM(&user_ntable) tables/views, uses %QTRIM(&user_totalsize) Gb space;
%put -----> Schema ASDDATA: %QTRIM(&totalsize)/&adsdata_total_space Gb is used, %QTRIM(&leftsize) Gb is free (&adsdata_space_delimiter Gb reserved);

/*----------------------------------------------------------------------------*/
/* Display opional outputs                                                    */
/*----------------------------------------------------------------------------*/


/* ADSDATA summary order by create time                                       */
%macro SQLCode2;
   select *
     from out_data.adsdata_spacesummary
       order by create_time
%mend SQLCode2;


%if  &order=TIME or  &order=BOTH or &order=ALL  %then %do;
title 'ADSDATA summary order by table created time';
  proc sql;
   %SQLCode2;
  quit;
%end;

/* ADSDATA summary order by size in kb                                        */
%macro SQLCode3;
   select *
     from out_data.adsdata_spacesummary
       order by size_kb desc
%mend SQLCode3;

%if  &order=SIZE or  &order=BOTH or &order=ALL  %then %do;
title 'ADSDATA summary order by table size';
proc sql;
  %SQLCode3;
  quit;
%end;

/* ADSDATA summary grouped by owner                                           */
%macro SQLCode4;
    select owner, total_tables, space_used_gb, free_space, last_time_used format=datetime20.  from (
     (select 1 as order_no,
          owner,
          count(*) as total_tables,
          sum(size_gb) as space_used_gb,
		  . as free_space,
		  max (stats_time) as last_time_used
     from out_data.adsdata_spacesummary
     group by owner
     having owner = "&ODBC_User"
     ) union all
	 (
     select 2 as order_no,
          owner,
          count(*) as total_tables,
          sum(size_gb) as space_used_gb,
		  . as free_space,
		  max (stats_time) as last_time_used
     from out_data.adsdata_spacesummary
     group by owner
     having owner ^= "&ODBC_User"
     ) union all
	 (
     select 3 as order_no,
          'ALL USERS' as owner,
          count(*) as total_tables,
          sum(size_gb) as space_used_gb,
		  225 - sum(size_gb) as free_space,
		  max (stats_time) as last_time_used
     from out_data.adsdata_spacesummary
     ) )
  order by order_no, owner
%mend SQLCode4;

%if  &order ^=   %then %do;
title 'ADSDATA table owner summary';
  proc sql;
  %SQLCode4;
  quit;
%end;

/*--------------------------------------------------------------------*/
/* Creat copy of Template for loading                                 */
/*--------------------------------------------------------------------*/
%if  &excel = YES or &email = YES or &maintenance = YES  %then %do;

%mfiledelete(&outputroot\ADSdata_summary.xlsx); run;
%mfilecopy(&prodsasroot\&GBClient\Templates\ADSdata_template.xlsx,
           &outputroot\ADSdata_summary.xlsx); run;
/*----------------------------------------------------------------------------*/
/* Create Excel output                                                        */
/*----------------------------------------------------------------------------*/

libname xls pcfiles path="&outputroot\ADSdata_summary.xlsx";

proc datasets lib = xls nolist;
    delete CREATE SIZE OWNER;
quit;

proc sql;

create table xls.CREATE as
%SQLCode2;

create table xls.SIZE as
%SQLCode3;

create table xls.OWNER as
%SQLCode4;

quit;

libname xls clear;
%end;


/*----------------------------------------------------------------------------*/
/* Create Automatic Email                                                     */
/*----------------------------------------------------------------------------*/

%if  &email = YES  %then %do;


%let emailsubject = ADSDATA Schema Summary;
%let emailfrom = &ODBC_User.@<****.ca>;
%let emailto = &ODBC_User.@<****.ca>;

%allocfile(msg,Email_msg.txt,o); run;

DATA _NULL_;
FILE "&MSG" NOTITLES RECFM=V lrecl=200;
    put "ADSDATA Schema Summary:";
    put " ";
    put "A total of %QTRIM(&ntable) tables/views in Schema ADSDATA ";
    put "User &ODBC_User has %QTRIM(&user_ntable) tables/views, uses %QTRIM(&user_totalsize) Gb space ";
    put "Schema ASDDATA: %QTRIM(&totalsize)/&adsdata_total_space Gb is used, %QTRIM(&leftsize) Gb is free (&adsdata_space_delimiter Gb reserved) ";
	put " ";
    put "Attached is spreadsheet containing the ADSDATA Schema Summary";
    put " ";
   	put "Please review the attached.";
   	put " ";
   	put " ";
   	put "********************************************************************************************";
   	put "Please do not respond to this email address as it is not monitored.                         ";
   	put "For inquiries, please contact me at rzhao@transunion.ca                                  ";
   	put "********************************************************************************************";
   	put " ";
   	put "%sysfunc(date(),weekdate17.) %sysfunc(time(),time8.0)";
   	put "Thank you.";
   	put "&ODBC_User. ";
RUN;

%emailmessage(subject = &emailsubject,
              to      = &emailto,
			  from    = &emailfrom,
       		  messagefile=&msg,
       		  attach   = &outputroot\ADSdata_summary.xlsx); run;


%end;

/*----------------------------------------------------------------------------*/
/* Create Database Maintenance Email                                          */
/*----------------------------------------------------------------------------*/

%if  &maintenance = YES  %then %do;

proc sql;
create table out_data.adsdata_owners as
select owner, count(*)+1 as count_tables from out_data.adsdata_spacesummary group by owner;
quit;

proc sql noprint;
select count(*) into :count_owner from out_data.adsdata_owners;
quit;

%do i=1 %to &count_owner;
data _null_;
      set out_data.adsdata_owners;
      if _n_= &i then do;
      call symput ('owner',trim(left(owner)));
      call symput ('count_tables',trim(left(count_tables)));
      end;
run;

proc sql;
create table out_data.adsdata_part as
select * from out_data.adsdata_spacesummary where owner = "&owner";
quit;

%let email_user = &owner;

%if &owner = <people do not want emails>  %then %let email_user = <macro owner>;

/* %let email_user = rzhao;*/ *for test purpose;

%let emailsubject = ADS Data Warehouse Maintenance;
%let emailfrom = &ODBC_User.@<****.ca>;
%let emailto = &email_user.@<****.ca>;

%allocfile(msg,Email_msg.txt,o); run;

DATA _NULL_;
FILE "&MSG" NOTITLES RECFM=V lrecl=200;
    put "Dear User &owner";
    put " ";
    put "Please note that the ADS Team is currently performing data warehouse maintenance.";
    put "If your following tables are no longer in use, please drop them for better perfomance of data warehouse";
	put " ";
    put "Attached is spreadsheet containing the ADSDATA Schema Summary";
   	put " ";
   	put "%sysfunc(date(),weekdate17.) %sysfunc(time(),time8.0)";
   	put "Thank you.";
   	put "&ODBC_User. ";
	put " ";
	put " ";
   	put "------------------------- List of tables you created: -------------------------";
    put " ";
	put "TABLE_NAME                              SIZE_GB   CREATE_TIME      ";
    put " ";
DO n=1 to &count_tables;
 SET out_data.adsdata_part;
PUT @1'ADSDATA.' table_name @41 size_gb @51 create_time;
END;
RUN;

%emailmessage(subject = &emailsubject,
              to      = &emailto,
			  from    = &emailfrom,
       		  messagefile=&msg,
       		  attach   = &outputroot\ADSdata_summary.xlsx); run;

%end;
%end;

/*----------------------------------------------------------------------------*/
/* Set SAS option back to default                                             */
/*----------------------------------------------------------------------------*/

title;
options notes mprint;



%mend adsspace;