forstie/Generating spreadsheets with SQL.sql

## Generating spreadsheets with SQL.sql
-- =================================================================
-- Author: Scott Forstie
-- Email : forstie@us.ibm.com
-- Date  : January 10, 2020
-- =================================================================

--
-- Setup:
-- 1) create and populate the spreadsheet generator table
-- 2) change the procedure source code:
--   2a) Use ACS to upload acsbundle.jar to /home/acs/acsbundle.jar
--   2b) Change forstie@us.ibm.com to your email address. <==== Please don't email me your spreadsheets :)
--   2c) Change /system=COMMON1 to use the name of your IBM i
--   2d) Change the ADDUSRSMTP user profile to be your user profile
--
cl: STRTCPSVR SERVER(*SMTP)  ;
cl: ADDUSRSMTP USRPRF(SCOTTF);

create schema coolstuff;

create table coolstuff.spreadsheet_generator
 (IFS_PATH varchar(1000),
  Spreadsheet_query varchar(10000)
  );

truncate coolstuff.spreadsheet_generator;
insert into coolstuff.spreadsheet_generator
 values ('/home/SCOTTF/systemnameGroupPTFCurrency',
         'SELECT * FROM systools.group_ptf_currency');
insert into coolstuff.spreadsheet_generator
 values ('/home/SCOTTF/top10spool',
         'select user_name, sum(size) as total_spool_space from qsys2.output_queue_entries_basic group by user_name order by total_spool_space desc limit 10');
insert into coolstuff.spreadsheet_generator
 values ('/home/SCOTTF/ALLOBJusers',
         'select authorization_name,status,no_password_indicator,previous_signon,text_description
  from qsys2.user_info where special_authorities like ''''%*ALLOBJ%'''' or authorization_name in (select user_profile_name
  from qsys2.group_profile_entries where group_profile_name in (select authorization_name
  from qsys2.user_info where special_authorities like ''''%*ALLOBJ%'''')) order by authorization_name');

select * from coolstuff.spreadsheet_generator;
stop;

--
-- 1 time setup:
-- =============
-- a) Locate ACS (acsbundle.jar) in the IFS at /home/acs/acsbundle.jar
-- b) Update the code below to use the name of your system
--    change COMMON1 to your system name
--    If you're on IBM i 7.3 or higher, with a current Db2 PTF group, just query QSYS2.SYSTEM_STATUS_INFO.
--    select partition_name from qsys2.system_status_info
--
create or replace procedure coolstuff.generate_spreadsheets()
begin
  declare cmdtext clob(2k);
  declare v_cmdstmt varchar(2000);
  declare v_ifs_path varchar(1000);
  declare v_ifs_full_path varchar(2000);
  declare v_spreadsheet_query varchar(10000);
  declare not_found condition for '02000';
  declare at_end integer default 0;
  declare spreadsheets cursor for
    select ifs_path, spreadsheet_query
      from coolstuff.spreadsheet_generator;
  declare continue handler for sqlexception set at_end = 1;
  declare continue handler for not_found set at_end = 1;
  open spreadsheets;
  fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
  while (at_end = 0) do
    set v_ifs_full_path = v_ifs_path concat lpad(month(current date), 2, 0) concat
          lpad(day(current date), 2, 0) concat year(current date) concat '.xlsx';
    set cmdtext =
  'STRQSH CMD(''java -Dcom.ibm.iaccess.ActLikeExternal=true -jar /home/acs/acsbundle.jar '
          concat '/plugin=cldownload /system=COMMON1 /clientfile=' concat v_ifs_full_path
          concat ' /sql="' concat v_spreadsheet_query concat '"'')';
    --    call systools.lprintf('Speadsheet being generated: ' concat v_ifs_full_path);
    call qsys2.qcmdexc(cmdtext);
    set v_cmdstmt =
        'SNDSMTPEMM RCP((''forstie@us.ibm.com'' *pri)) SUBJECT(''COMMON1 report:' concat
          v_ifs_full_path concat ''') NOTE(''Common1 report ' concat
          lpad(month(current date), 2, 0) concat lpad(day(current date), 2, 0) concat
          year(current date) concat ''') ATTACH(''' concat v_ifs_full_path concat ''')';
    --    call systools.lprintf('Speadsheet being emailed: ' concat v_ifs_full_path);
    call qsys2.qcmdexc(v_cmdstmt);
    fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
  end while;
  close spreadsheets;
end;
stop;

call coolstuff.generate_spreadsheets();

stop;
create or replace procedure coolstuff.email_spreadsheets ()
begin
  declare cmdtext clob(2k);
  declare v_cmdstmt varchar(2000);
  declare v_ifs_path varchar(1000);
  declare v_ifs_full_path varchar(2000);
  declare v_spreadsheet_query varchar(10000);
  declare not_found condition for '02000';
  declare at_end integer default 0;
  declare spreadsheets cursor for
    select ifs_path, spreadsheet_query
      from coolstuff.spreadsheet_generator;
  declare continue handler for sqlexception set at_end = 1;
  declare continue handler for not_found set at_end = 1;
  open spreadsheets;
  fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
  while (at_end = 0) do
    set v_ifs_full_path = v_ifs_path concat lpad(month(current date), 2, 0) concat
          lpad(day(current date), 2, 0) concat year(current date) concat '.xlsx';
    call systools.lprintf('Speadsheet being emailed: ' concat v_ifs_full_path);
    set v_cmdstmt =
                  'SNDSMTPEMM RCP((''forstie@us.ibm.com'' *pri)) SUBJECT(''COMMON1 report'') NOTE(''Common1 report '
                    concat lpad(month(current date), 2, 0) concat
                    lpad(day(current date), 2, 0) concat year(current date) concat
          ''') ATTACH(''' concat v_ifs_full_path concat ''')';
    call qsys2.qcmdexc(v_cmdstmt);
    fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
  end while;
  close spreadsheets;
end;
stop;

call coolstuff.email_spreadsheets();

stop;

-- Create the spreadsheets on a schedule
cl: ADDJOBSCDE JOB(SSHEETS) CMD(RUNSQL SQL('call coolstuff.generate_spreadsheets()') COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME(235500)   ;

stop;

--
-- description: Review the setup
--
select *
  from qsys2.scheduled_job_info
  where scheduled_job_name = 'SSHEETS';
	-- =================================================================
	-- Author: Scott Forstie
	-- Email : forstie@us.ibm.com
	-- Date : January 10, 2020
	-- =================================================================

	--
	-- Setup:
	-- 1) create and populate the spreadsheet generator table
	-- 2) change the procedure source code:
	-- 2a) Use ACS to upload acsbundle.jar to /home/acs/acsbundle.jar
	-- 2b) Change forstie@us.ibm.com to your email address. <==== Please don't email me your spreadsheets :)
	-- 2c) Change /system=COMMON1 to use the name of your IBM i
	-- 2d) Change the ADDUSRSMTP user profile to be your user profile
	--
	cl: STRTCPSVR SERVER(*SMTP) ;
	cl: ADDUSRSMTP USRPRF(SCOTTF);

	create schema coolstuff;

	create table coolstuff.spreadsheet_generator
	(IFS_PATH varchar(1000),
	Spreadsheet_query varchar(10000)
	);

	truncate coolstuff.spreadsheet_generator;
	insert into coolstuff.spreadsheet_generator
	values ('/home/SCOTTF/systemnameGroupPTFCurrency',
	'SELECT * FROM systools.group_ptf_currency');
	insert into coolstuff.spreadsheet_generator
	values ('/home/SCOTTF/top10spool',
	'select user_name, sum(size) as total_spool_space from qsys2.output_queue_entries_basic group by user_name order by total_spool_space desc limit 10');
	insert into coolstuff.spreadsheet_generator
	values ('/home/SCOTTF/ALLOBJusers',
	'select authorization_name,status,no_password_indicator,previous_signon,text_description
	from qsys2.user_info where special_authorities like ''''%*ALLOBJ%'''' or authorization_name in (select user_profile_name
	from qsys2.group_profile_entries where group_profile_name in (select authorization_name
	from qsys2.user_info where special_authorities like ''''%*ALLOBJ%'''')) order by authorization_name');

	select * from coolstuff.spreadsheet_generator;
	stop;

	--
	-- 1 time setup:
	-- =============
	-- a) Locate ACS (acsbundle.jar) in the IFS at /home/acs/acsbundle.jar
	-- b) Update the code below to use the name of your system
	-- change COMMON1 to your system name
	-- If you're on IBM i 7.3 or higher, with a current Db2 PTF group, just query QSYS2.SYSTEM_STATUS_INFO.
	-- select partition_name from qsys2.system_status_info
	--
	create or replace procedure coolstuff.generate_spreadsheets()
	begin
	declare cmdtext clob(2k);
	declare v_cmdstmt varchar(2000);
	declare v_ifs_path varchar(1000);
	declare v_ifs_full_path varchar(2000);
	declare v_spreadsheet_query varchar(10000);
	declare not_found condition for '02000';
	declare at_end integer default 0;
	declare spreadsheets cursor for
	select ifs_path, spreadsheet_query
	from coolstuff.spreadsheet_generator;
	declare continue handler for sqlexception set at_end = 1;
	declare continue handler for not_found set at_end = 1;
	open spreadsheets;
	fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
	while (at_end = 0) do
	set v_ifs_full_path = v_ifs_path concat lpad(month(current date), 2, 0) concat
	lpad(day(current date), 2, 0) concat year(current date) concat '.xlsx';
	set cmdtext =
	'STRQSH CMD(''java -Dcom.ibm.iaccess.ActLikeExternal=true -jar /home/acs/acsbundle.jar '
	concat '/plugin=cldownload /system=COMMON1 /clientfile=' concat v_ifs_full_path
	concat ' /sql="' concat v_spreadsheet_query concat '"'')';
	-- call systools.lprintf('Speadsheet being generated: ' concat v_ifs_full_path);
	call qsys2.qcmdexc(cmdtext);
	set v_cmdstmt =
	'SNDSMTPEMM RCP((''forstie@us.ibm.com'' *pri)) SUBJECT(''COMMON1 report:' concat
	v_ifs_full_path concat ''') NOTE(''Common1 report ' concat
	lpad(month(current date), 2, 0) concat lpad(day(current date), 2, 0) concat
	year(current date) concat ''') ATTACH(''' concat v_ifs_full_path concat ''')';
	-- call systools.lprintf('Speadsheet being emailed: ' concat v_ifs_full_path);
	call qsys2.qcmdexc(v_cmdstmt);
	fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
	end while;
	close spreadsheets;
	end;
	stop;

	call coolstuff.generate_spreadsheets();

	stop;
	create or replace procedure coolstuff.email_spreadsheets ()
	begin
	declare cmdtext clob(2k);
	declare v_cmdstmt varchar(2000);
	declare v_ifs_path varchar(1000);
	declare v_ifs_full_path varchar(2000);
	declare v_spreadsheet_query varchar(10000);
	declare not_found condition for '02000';
	declare at_end integer default 0;
	declare spreadsheets cursor for
	select ifs_path, spreadsheet_query
	from coolstuff.spreadsheet_generator;
	declare continue handler for sqlexception set at_end = 1;
	declare continue handler for not_found set at_end = 1;
	open spreadsheets;
	fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
	while (at_end = 0) do
	set v_ifs_full_path = v_ifs_path concat lpad(month(current date), 2, 0) concat
	lpad(day(current date), 2, 0) concat year(current date) concat '.xlsx';
	call systools.lprintf('Speadsheet being emailed: ' concat v_ifs_full_path);
	set v_cmdstmt =
	'SNDSMTPEMM RCP((''forstie@us.ibm.com'' *pri)) SUBJECT(''COMMON1 report'') NOTE(''Common1 report '
	concat lpad(month(current date), 2, 0) concat
	lpad(day(current date), 2, 0) concat year(current date) concat
	''') ATTACH(''' concat v_ifs_full_path concat ''')';
	call qsys2.qcmdexc(v_cmdstmt);
	fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
	end while;
	close spreadsheets;
	end;
	stop;

	call coolstuff.email_spreadsheets();

	stop;

	-- Create the spreadsheets on a schedule
	cl: ADDJOBSCDE JOB(SSHEETS) CMD(RUNSQL SQL('call coolstuff.generate_spreadsheets()') COMMIT(NONE) NAMING(SQL)) FRQ(WEEKLY) SCDDATE(NONE) SCDDAY(*ALL) SCDTIME(235500) ;

	stop;

	--
	-- description: Review the setup
	--
	select *
	from qsys2.scheduled_job_info
	where scheduled_job_name = 'SSHEETS';