: !/bin/sh
########################################################################
# T21BATCHNAME tqauto
# T21BATCHDESC auto Monthly Extn Group Reports and SW calls and etc.

# ALLOWBLANKDATES
# CUSTOMDESC
# CUSTOMDEF
# T21BATCHVAR1DESC
# T21BATCHVAR1VALU
# T21BATCHVAR2DESC 
# T21BATCHVAR2VALU 
# T21BATCHVAR3DESC 
# T21BATCHVAR3VALU 
# T21BATCHVAR4DESC 
# T21BATCHVAR4VALU 
# T21BATCHVAR5DESC 
# T21BATCHVAR5VALU 

# T21BATCHENDARGS
########################################################################
# command line args
SDATE=$1; EDATE=$2;  STIME=$3; ETIME=$4

##############################
tmpfile="/tmp/tqauto_$$"
tmp0file="/tmp/tqauto0_$$"
tmp1file="/tmp/tqauto1_$$"
tmp2file="/tmp/tqauto2_$$"
tmp3file="/tmp/tqauto3_$$"

VT_EMAILLIST="robyn.douglas@victrack.com.au michael.bridges@victrack.com.au steve.moodie@victrack.com.au rod@transmit.com.au"
#VT_EMAILLIST="tqnew"
#VT_EMAILLIST="rod@transmit.com.au"


# Testing
SDATE="01/10/2015"
EDATE="31/10/2015"
DODBG="true"

###########################################################################
###########################################################################
# send output to file
exec >$tmpfile 2>&1

(
echo
echo "--------------------------------------------------------------"
echo "List of extension groups in groupname that are not in gengroup"
runisql 2>&1 <<SQLCMD2
select unique grid from groupname
where grtype = "E"
and grid not in ( select childgroupid from gengroup )
SQLCMD2
) > $tmp2file

(
echo
echo "-------------------------------------------------------------------"
echo "List of extension groups in the directory that are not in groupname"
runisql 2>&1 <<SQLCMD3
select unique extgroupunique from directtemplate
where 1=1
and extgroupunique not in ( select grid from groupname where grtype = "E" )
SQLCMD3
) >> $tmp2file


########################################################################

## output group comparison
#cat $tmp2file

############################################

# Add left margin
cat $tmp2file | \
while   read aline
do
        echo "               $aline"
done >$tmp3file


############################################################################
## move *specials* extg files
#mv $outdir/*n* $extg_mv_dir
#mv $outdir/VAC* $extg_mv_dir
#mv $outdir/UNAUN* $extg_mv_dir

# these have been replaced with individual sql reports
# that query the trk_extsum and Directory

#...

extg_none_fname="extg_none.csv"
extg_VAC_fname="extg_VAC.csv"
extg_UNAUN_fname="extg_UNAUN.csv"
extg_noext_fname="extg_noext.csv"

cp /dev/null "$extg_none_fname"
cp /dev/null "$extg_VAC_fname"
cp /dev/null "$extg_UNAUN_fname"
cp /dev/null "$extg_noext_fname"


txsql -h -F',' -o "$extg_none_fname" <<EXTGNONESQL1
select unique
       site.siteid, site.sitenm1,
       directtemplate.extension,
       hist_gg.parentgroupid,
       hist_gn.grtype, hist_gn.grid, hist_gn.grname1, hist_gn.grname2
from hist_gn, hist_gg, site, directtemplate
where hist_gn.grtype = "E"
  and hist_gn.grname1[1,7] != "RETIRED"
  and hist_gn.siteid = site.siteid
  and hist_gn.hdate between "$SDATE" and "$EDATE"
  and hist_gn.hdate = hist_gg.hdate
  and hist_gn.grid = hist_gg.childgroupid
  and hist_gn.grid = directtemplate.extgroupunique
  and hist_gn.grid NOT in (
        select unique trk_extsum.grid from trk_extsum
        where trk_extsum.grid is NOT NULL
	  and sumdate between "$SDATE" and "$EDATE"
	)
order by 1, 3, 4, 6;
EXTGNONESQL1


txsql -h -F',' -o "$extg_VAC_fname" <<EXTGVACSQL1
select unique
       site.siteid, site.sitenm1,
       trk_extsum.inoutno extension,
       hist_gg.parentgroupid,
       hist_gn.grtype, hist_gn.grid, hist_gn.grname1, hist_gn.grname2,
       SUM(trk_extsum.callcount) Total_Call_Count,
       SUM(trk_extsum.callcost) Total_Call_Cost
from hist_gn, hist_gg, site, outer trk_extsum
where hist_gn.grtype = "E"
  and hist_gg.parentgroupid = "VAC"
  and hist_gn.grname1[1,7] != "RETIRED"
  and hist_gn.hdate between "$SDATE" and "$EDATE"
  and hist_gn.hdate = hist_gg.hdate
  and hist_gn.hdate = trk_extsum.sumdate
  and hist_gn.siteid = site.siteid
  and hist_gn.grid = hist_gg.childgroupid
  and hist_gn.grid = trk_extsum.grid
  and trk_extsum.siteid = site.siteid
  and trk_extsum.inouttype matches "[EP]"
group by
       site.siteid, site.sitenm1,
       trk_extsum.inoutno,
       hist_gg.parentgroupid,
       hist_gn.grtype, hist_gn.grid, hist_gn.grname1, hist_gn.grname2
order by
       site.siteid, trk_extsum.inoutno,
       hist_gg.parentgroupid, hist_gn.grid;
EXTGVACSQL1


txsql -h -F',' -o "$extg_UNAUN_fname" <<EXTGUNAUNSQL1
select unique
       site.siteid, site.sitenm1,
       trk_extsum.inoutno extension,
       hist_gg.parentgroupid,
       hist_gn.grtype, hist_gn.grid, hist_gn.grname1, hist_gn.grname2,
       SUM(trk_extsum.callcount) Total_Call_Count,
       SUM(trk_extsum.callcost) Total_Call_Cost
from hist_gn, hist_gg, site, outer trk_extsum
where hist_gn.grtype = "E"
  and hist_gg.parentgroupid = "UNA"
  and hist_gn.grname1[1,7] != "RETIRED"
  and hist_gn.hdate between "$SDATE" and "$EDATE"
  and hist_gn.hdate = hist_gg.hdate
  and hist_gn.hdate = trk_extsum.sumdate
  and hist_gn.siteid = site.siteid
  and hist_gn.grid = hist_gg.childgroupid
  and hist_gn.grid = trk_extsum.grid
  and trk_extsum.siteid = site.siteid
  and trk_extsum.inouttype matches "[EP]"
group by
       site.siteid, site.sitenm1,
       trk_extsum.inoutno,
       hist_gg.parentgroupid,
       hist_gn.grtype, hist_gn.grid, hist_gn.grname1, hist_gn.grname2
order by
       site.siteid, trk_extsum.inoutno,
       hist_gg.parentgroupid, hist_gn.grid;
EXTGUNAUNSQL1


txsql -h -F',' -o "$extg_noext_fname" <<EXTGNOEXTSQL1
select unique
       site.siteid, site.sitenm1,
       gengroup.parentgroupid,
       groupname.grtype, groupname.grid, groupname.grname1, groupname.grname2
from groupname, gengroup, site
where groupname.grtype = "E"
  and groupname.grname1[1,7] != "RETIRED"
  and groupname.grid = gengroup.childgroupid
  and groupname.siteid = site.siteid
  and groupname.grid NOT in (
        select unique directtemplate.extgroupunique from directtemplate
	)
order by 1, 3, 5;
EXTGNOEXTSQL1



##########################
# output to user
#cat $tmp3file | pr_rep system tqauto_
##cat $tmp3file >1

## email to robyn
# Force mail to pass DATE as GMT
#TZ=''; export TZ
#cat $tmp3file | mail -s "tqauto output" $VT_EMAILLIST

# email tqauto results...
emaillist="$VT_EMAILLIST"
attfiles="$extg_none_fname $extg_VAC_fname $extg_UNAUN_fname $extg_noext_fname"

subject="tqauto results"
emcontent="TQ auto results"
t21em_content=`cat <<SOMETEXT1

Please find the attached Reports
which replace the *special* extension group reports moved to stephen
## move specials extg files
#mv $outdir/*n* $extg_mv_dir
#mv $outdir/VAC* $extg_mv_dir
#mv $outdir/UNAUN* $extg_mv_dir

$extg_none_fname     - List of Extensions / Groups with no calls data
$extg_VAC_fname      -  List of Extensions / Groups bellonging to Paraent VAC
$extg_UNAUN_fname - List of Extensions / Groups bellonging to Paraent UNA
$extg_noext_fname - List of Extension Groups not in the Directory

Also
General Group Summary reports that are emailed separately 
gengroupsum "$SDATE" "$EDATE" "VAC"
gengroupsum "$SDATE" "$EDATE" "UNA"

health user reports that are emailed separately and saved in the
telmax21 health user work folder
and then archive to /u/catcom/database/health folder

and the remaining original tqnew report which is

Telmax21 Hierarchy/Directory Comparison
=======================================

SOMETEXT1
`
t21em_content_file="$tmp3file"
export emcontent t21em_content t21em_content_file

ematt "$attfiles" "$subject" $emaillist

emaillist="$VT_EMAILLIST"
attfiles="$extg_none_fname $extg_VAC_fname $extg_UNAUN_fname $extg_noext_fname"

# clear for next email
subject=""; export subject
emcontent=""; export emcontent
t21em_content=""; export t21em_content
t21em_content_file=""; export t21em_content_file

# save reports to system folder
mv "$extg_none_fname" $HOME/system
mv "$extg_VAC_fname" $HOME/system
mv "$extg_UNAUN_fname" $HOME/system
mv "$extg_noext_fname" $HOME/system

## Testing
#exit 0

#########################################################################
#########################################################################
#########################################################################

###########################################################################
###########################################################################
###########################################################################
# setup for remaining reports

# prepare for report output setting to be controlled within  this script

# disable rpt options set if spooled
if [ "$spoolt21rptopts" = "1" ]; then
        spoolt21rptopts=0
        export spoolt21rptopts
        runont21_pid=$rptque_pid
fi

# make sure runont21_pid is set for srct21rptopts script to find temp rpt opts
if [ -z "$runont21_pid" ]; then
        runont21_pid="$$"
fi
export runont21_pid

# must be set so pr_rep sets uue_pid
dobatch_pid=$runont21_pid; export dobatch_pid

# set pr_rep env file name for concat/email ALL-IN-ONE
if [ -z "$pr_rep_env_file" ]; then
        pr_rep_env_file="$HOME/pr_rep.env$runont21_pid"
        export pr_rep_env_file
fi


###########################################################################
###########################################################################
###########################################################################
###########################################################################
# set report output options for gen group reports

RPTOPTSTEMP="tmp/.t21rptopts.tmp$runont21_pid"

cat > $HOME/$RPTOPTSTEMP <<RPTOPTS1
#DOEDITT21RPTOPTS='1'
#t21usetextedit='0'
t21fileformat='1'
#t21rtftype='2'
t21filename=''
t21fileextension='rtf'
t21filetype='2'
#t21printeryn='Y'
t21printeryn='N'
t21printername='default'
t21filteryn='N'
t21filtercmd=''
t21emailyn='Y'
t21emailaddr="$VT_EMAILLIST"
t21emailuuencode='3'
t21emailnotify='N'
t21fileview='4'
t21hostsave='Y'
t21dirpath=''
t21clientcopy='N'
t21clientdir='C:/tmp'
t21keeptemp='N'
t21fileconcat='N'
t21hyperlink='N'
RPTOPTS1

# set above options
. srct21rptopts

# debug
#echo "----------------"
#ls -altr $HOME/tmp
#echo "-------"
#echo "---- RPTOPTSTEMP=$RPTOPTSTEMP ----"
#cat $HOME/$RPTOPTSTEMP
#echo "----------------"

#######################################
# generate and email gengroup reports

gengroupsum "$SDATE" "$EDATE" "VAC"
gengroupsum "$SDATE" "$EDATE" "UNA"

t21em_subject="tqauto: UNA and VAC General Group Summary Reports"
export t21em_subject

############################################################
############################################################
############################################################
############################################################
############################################################
# handle concat option
# must do it here because  rptque will have spoolt21rptopts set
# and therefore srct21rptopts will not get RPTOPTSTEMP from above
if [ -n "$pr_rep_env_file" -a -f "$pr_rep_env_file" ]; then
    # handle concat and email all in one options
    if [ "$t21fileconcat" = "Y" -o \
         \( "$t21emailyn" = "Y" -a "$t21emailuuencode" = "3" \) ]
    then
        $DODBG && echo "tqauto: handle concat/email-ALL-IN-ONE option(s)" 2>&1
        handlet21rpt
echo "here"
    fi
fi

# remove temp rptopts file
rm -f $HOME/$RPTOPTSTEMP

############################################################


###########################################################################
###########################################################################
###########################################################################
###########################################################################
# generate and email health check reports
# set report output options for remaining HEALTH CHECK reports below

RPTOPTSTEMP="tmp/.t21rptopts.tmp$runont21_pid"

cat > $HOME/$RPTOPTSTEMP <<RPTOPTS2
#DOEDITT21RPTOPTS='1'
#t21usetextedit='0'
t21fileformat='1'
#t21rtftype='2'
t21filename=''
t21fileextension='rtf'
t21filetype='2'
#t21printeryn='Y'
t21printeryn='N'
t21printername='default'
t21filteryn='N'
t21filtercmd=''
t21emailyn='Y'
t21emailaddr="$VT_EMAILLIST"
t21emailuuencode='3'
t21emailnotify='N'
t21fileview='4'
t21hostsave='Y'
t21dirpath='health/work'
t21clientcopy='N'
t21clientdir='C:/tmp'
t21keeptemp='N'
t21fileconcat='N'
t21hyperlink='N'
RPTOPTS2

# set above options
. srct21rptopts

HUSERDIR="/u/catcom/reports/health"

$DODBG && echo "tqauto: callstats" 2>&1
$HUSERDIR/system/batch/callstats "$SDATE" "$EDATE" 00:00 24:00 ALL 1

$DODBG && echo "tqauto: dupdstat" 2>&1
dupdstat "$SDATE" "$EDATE" Nulls S

$DODBG && echo "tqauto: swtot" 2>&1
$HUSERDIR/system/batch/swtot "$SDATE" "$EDATE" 00:00 24:00 ALL

$DODBG && echo "tqauto: tariffchk" 2>&1
$HUSERDIR/system/batch/tariffchk "$SDATE" "$EDATE" 00:00 24:00 ALL

$DODBG && echo "tqauto: swdbadextn.t21batch" 2>&1
$HUSERDIR/system/batch/swdbadextn.t21batch "$SDATE" "$EDATE" 00:00 24:00 ALL

$DODBG && echo "tqauto: uextndiv.t21batch" 2>&1
$HUSERDIR/system/batch/uextndiv.t21batch "$SDATE" "$EDATE" 00:00 24:00 ALL

$DODBG && echo "tqauto: prtnowarn.t21batch" 2>&1
$HUSERDIR/system/batch/prtnowarn.t21batch "$SDATE" "$EDATE" 00:00 24:00 ALL

$DODBG && echo "tqauto: gcat1.t21batch" 2>&1
$HUSERDIR/system/batch/gcat1.t21batch "$SDATE" "$EDATE" 00:00 24:00 ALL "131"

#######################################
# set NO PRINT for - fiddis health report
#                  - rejects
#                  - tpcnf T
#                  - tpcnf ALL (comparison)
#                  - long calls
cat > $HOME/$RPTOPTSTEMP <<RPTOPTS3
#DOEDITT21RPTOPTS='1'
#t21usetextedit='0'
t21fileformat='1'
#t21rtftype='2'
t21filename=''
t21fileextension='rtf'
t21filetype='2'
t21printeryn='N'
t21printername='default'
t21filteryn='N'
t21filtercmd=''
t21emailyn='Y'
t21emailaddr="$VT_EMAILLIST"
t21emailuuencode='3'
t21emailnotify='N'
t21fileview='4'
t21hostsave='Y'
t21dirpath='health/work'
t21clientcopy='N'
t21clientdir='C:/tmp'
t21keeptemp='N'
t21fileconcat='N'
t21hyperlink='N'
RPTOPTS3

# set above options
. srct21rptopts

$DODBG && echo "tqauto: fiddis.t21batch" 2>&1
$HUSERDIR/system/batch/fiddis.t21batch "$SDATE" "$EDATE" 00:00 24:00 ALL

T21BATCHVAR1VALU="T";export T21BATCHVAR1VALU
T21BATCHVAR2VALU="ALL";export T21BATCHVAR2VALU
T21BATCHVAR3VALU="N";export T21BATCHVAR3VALU
T21BATCHVAR4VALU="";export T21BATCHVAR4VALU
T21BATCHVAR5VALU="";export T21BATCHVAR5VALU
$DODBG && echo "tqauto: tpcnf.t21batch Telstra" 2>&1
$HUSERDIR/system/batch/tpcnf ALL ALL 00:00 24:00 ALL

T21BATCHVAR1VALU="ALL";export T21BATCHVAR1VALU
T21BATCHVAR2VALU="ALL";export T21BATCHVAR2VALU
T21BATCHVAR3VALU="Y";export T21BATCHVAR3VALU
T21BATCHVAR4VALU="";export T21BATCHVAR4VALU
T21BATCHVAR5VALU="";export T21BATCHVAR5VALU
$DODBG && echo "tqauto: tpcnf.t21batch changes" 2>&1
$HUSERDIR/system/batch/tpcnf ALL ALL 00:00 24:00 ALL

$DODBG && echo "tqauto: clrrejects.t21batch" 2>&1
$HUSERDIR/system/batch/clrrejects.t21batch "$SDATE" "$EDATE" 00:00 24:00 ALL

# dialledno
DIAL="ALL"; export DIAL
# min duration
T21BATCHVAR1VALU="7200"; export T21BATCHVAR1VALU
# min callcost
T21BATCHVAR2VALU="0.01"; export T21BATCHVAR2VALU
$DODBG && echo "tqauto: calls.t21batch" 2>&1
$HUSERDIR/system/batch/calls.t21batch "$SDATE" "$EDATE" 00:00 24:00 ALL "$DIAL"

$DODBG && echo "tqauto: uTEC.t21batch" 2>&1
$HUSERDIR/system/batch/uTEC.t21batch "$SDATE" "$EDATE" 00:00 24:00 ALL ""

$DODBG && echo "tqauto: xxTEC.t21batch" 2>&1
$HUSERDIR/system/batch/xxTEC.t21batch "$SDATE" "$EDATE" 00:00 24:00 ALL ""

#----------------------------------
t21em_subject="tqauto: health/work batch Reports"
export t21em_subject

############################################################
############################################################
############################################################
############################################################
############################################################
# handle concat option
# must do it here because  rptque will have spoolt21rptopts set
# and therefore srct21rptopts will not get RPTOPTSTEMP from above
if [ -n "$pr_rep_env_file" -a -f "$pr_rep_env_file" ]; then
    # handle concat and email all in one options
    if [ "$t21fileconcat" = "Y" -o \
         \( "$t21emailyn" = "Y" -a "$t21emailuuencode" = "3" \) ]
    then
        $DODBG && echo "tqauto: handle concat/email-ALL-IN-ONE option(s)" 2>&1
        handlet21rpt
    fi
fi

# remove temp rptopts file
rm -f $HOME/$RPTOPTSTEMP

############################################################
# archive health check reports
cd $HOME/health
./doarchealth `date "+tqauto%Y%m%d"`

###########################################################################
## email people that tqauto is complete
#
#TQNEW_DONE_EMAILLIST='david.cain@victrack.com.au'
#
## Force mail to pass DATE as GMT
#TZ=''; export TZ
#echo "tqauto: Complete." | \
#     mail -s "tqauto complete" $TQNEW_DONE_EMAILLIST
#
##########################
rm -f $tmpfile
rm -f $tmp0file
rm -f $tmp1file
rm -f $tmp2file
rm -f $tmp3file

# end