#!/bin/ksh
. /opt/db/scripts/setpath.sh

generate_batch ()
{
sqlplus -S $DBUSER/$DBPASSWD@$ORACLE_SID <<EOF > /opt/db/scripts/tools/delete_connection/batchrun/batchrun.$(/bin/date '+%d%m%Y.%Hh')

set echo Off
set term On
set pages 0
set head off
set ver off
set feed off
set trims on
set linesize 20000

WITH data
    AS (SELECT user_id,
               jc_name,
               upd_time,
               RANK () OVER (PARTITION BY user_id ORDER BY upd_time ASC)
                  rk
          FROM user_jc
         WHERE user_id IN (  SELECT user_id
                               FROM user_jc
                              WHERE JC_NAME LIKE 'CFF\_S\_%' ESCAPE '\'
                           GROUP BY user_id
                             HAVING COUNT (user_id) > 1)
               AND JC_NAME LIKE 'CFF\_S\_%' ESCAPE '\')
SELECT    'DISCONNECT ent_user  FROM job_code WITH user_id = "'
   || user_id
   || '", jc_name = "'
   || jc_name
   || '";'
  FROM data
 WHERE rk = 1;

exit
EOF
}

sanity_check ()
{
line_nr=$(wc -l /opt/db/scripts/tools/delete_connection/batchrun/batchrun.$(/bin/date '+%d%m%Y.%Hh') | awk ' { print $1 } ')
if [ $line_nr -gt 400 ]; then
        (cat /opt/db/scripts/tools/delete_connection/mail_body.txt) | mailx -s "Alert: please manually execute /opt/db/scripts/tools/delete_connection/batchrun/batchrun.$DATE" -r test@example.com test2@example.com
        exit 1
        fi
}

run_batch ()
{
/opt/bmchome/bin/ess batchrun -A -i /opt/db/scripts/tools/delete_connection/batchrun/batchrun.$(/bin/date '+%d%m%Y.%Hh')
}

generate_batch && sanity_check && run_batch