: !/bin/sh
# extncalls8.t21batch
#################################################################
# T21BATCHDESC  Extension Call Detail
# ALLOWBLANKDATES false
# CUSTOMDESC Enter Dialled Number (e. 001* or ALL for all numbers)
# CUSTOMDEF ALL
# T21BATCHVAR1DESC Enter Minimum Duration in seconds (def. 0)
# T21BATCHVAR1VALU 0
# T21BATCHVAR2DESC Enter Minimum Call Cost in dollars and cents
# T21BATCHVAR2VALU 0.0
# T21BATCHVAR3DESC Enter Extension list ("," delimited)
# T21BATCHVAR3VALU ALL
# T21BATCHVAR4DESC calltype
# T21BATCHVAR4VALU ALL
# T21BATCHVAR5DESC expandedcalltype
# T21BATCHVAR5VALU ALL
# T21BATCHENDARGS
########################################################################
########################################################################

DEFDIALLEDNO="ALL"
DEFDURATION="0"
DEFCALLCOST="0.0"

progname=$0

SDATE=$1; EDATE=$2; STIME=$3; ETIME=$4

if [ -n "$5" ]; then
        SITEID=$5
fi

if [ -n "$6" ]; then
        DIALLEDNO=$6
#else
#        DIALLEDNO=$DEFDIALLEDNO
fi

if [ -n "$T21BATCHVAR1VALU" ]; then
        DURATION=$T21BATCHVAR1VALU
#else
#        DURATION=$DEFDURATION
fi

if [ -n "$T21BATCHVAR2VALU" ]; then
        CALLCOST=$T21BATCHVAR2VALU
#else
#        CALLCOST=$DEFCALLCOST
fi

if [ -n "$T21BATCHVAR3VALU" ]; then
        EXTNLIST="$T21BATCHVAR3VALU"
else
        EXTNLIST=""
fi

if [ -n "$T21BATCHVAR4VALU" ]; then
        CALLTYPE="$T21BATCHVAR4VALU"
else
        CALLTYPE=""
fi

if [ -n "$T21BATCHVAR5VALU" ]; then
        EXPANDEDCALLTYPE="$T21BATCHVAR5VALU"
else
        EXPANDEDCALLTYPE=""
fi

## Testing
#SDATE="20/11/2015"
#EDATE="20/11/2015"

#SDATE="01/06/2015"
#EDATE="30/11/2015"
#SITEID="ALL"
#DIALLEDNO="ALL"
#DIALLEDNO="*417002877"
#DURATION=ALL
#CALLCOST=ALL
#EXTNLIST="ALL"
##EXTNLIST="12345,54321"
##EXTNLIST="SQL"
##EXTNSQLBIT=" = directtemplate.extension \
## and directcustom.surname matches \"*FAX*\" \
##"
#CALLTYPE="ALL"
#EXPANDEDCALLTYPE="ALL"
#EXTRASQLBIT=""
### SHB (Shell House), NAU (Nauru House), BCI (Burnley C&I)
##EXTRASQLBIT="$EXRASQLBIT and siteid in( 95, 106, 102 )"
### Mobile calls only
##EXTRASQLBIT="$EXTRASQLBIT and calltypedef.callcategory = \"M\""


SDATE="17/05/2017"
EDATE="17/05/2017"
SITEID="ALL"
DIALLEDNO="ALL"
DURATION=ALL
CALLCOST=ALL
#EXTNLIST="ALL"
EXTNLIST="4100"
SEPARATEFILES="0"
CALLTYPE="ALL"
EXPANDEDCALLTYPE="ALL"
EXTRASQLBIT=""

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

lastdayinmonth()        # Returns the last day in month, Inputs: $1=mm $2=yyyy
{
        cal $1 $2 | awk '{ if ( NF > 0 ) x=$NF } END { print x }'
}


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

do_extncalls8()
{
tmpfile=/tmp/calls_$$
tmp1file=/tmp/calls_1_$$
tmp2file=/tmp/calls_2_$$

rm -f "$tmpfile"
rm -f "$tmp1file"
rm -f "$tmp2file"

if [ -z "$SITEID" ]; then
        echo "Enter   siteid (Enter for ALL):\c"; read SITEID
fi
if [ -z "$SITEID" ]; then
        SITEID="ALL"
fi
if [ -z "$SDATE" ]; then
        echo "Enter start date:\c"; read SDATE
fi
if [ -z "$EDATE" ]; then
        echo "Enter   end date:\c"; read EDATE
fi
#if [ -z "$STIME" ]; then
#        echo "Enter start time:\c"; read STIME
#fi
#if [ -z "$ETIME" ]; then
#        echo "Enter   end time:\c"; read ETIME
#fi

if [ -z "$DIALLEDNO" ]; then
        echo "Enter   dialledno(Enter for [$DEFDIALLEDNO];or ALL):\c"; read DIALLEDNO
fi
if [ -z "$DIALLEDNO" ]; then
        DIALLEDNO=$DEFDIALLEDNO
fi

if [ -z "$DURATION" ]; then
        echo "Enter   min duration(secs,Enter for $DEFDURATION):\c"; read DURATION
fi
if [ -z "$DURATION" ]; then
        DURATION=$DEFDURATION
fi

if [ -z "$CALLCOST" ]; then
        echo "Enter   min callcost(dollars and cents,Enter for $DEFCALLCOST):\c"; read CALLCOST
fi
if [ -z "$CALLCOST" ]; then
        CALLCOST=$DEFCALLCOST
fi

if [ -z "$EXTNLIST" ]; then
        echo "Enter   extension list(, delimited - Enter for ALL):\c"; read EXTNLIST
fi
if [ -z "$EXTNLIST" ]; then
        EXTNLIST="ALL"
fi

if [ -z "$CALLTYPE" ]; then
        echo "Enter   calltype list(Ie. EBA or Enter to ignore):\c"; read CALLTYPE
fi
if [ -z "$CALLTYPE" ]; then
        CALLTYPE="ALL"
fi

if [ -z "$EXPANDEDCALLTYPE" ]; then
        echo "Enter   expandedcalltype list(Ie. EBA or Enter to ignore):\c"; read EXPANDEDCALLTYPE
fi
if [ -z "$EXPANDEDCALLTYPE" ]; then
        EXPANDEDCALLTYPE="ALL"
fi


#------------------------------------------
if [ "$SITEID" = "ALL" ]; then
        SITEBIT=""
else
        SITEBIT="and siteid = $SITEID"
fi

if [ "$DIALLEDNO" = "ALL" ]; then
        DIALLEDNOBIT=""
else
        DIALLEDNOBIT=" and dialledno matches \"$DIALLEDNO\""
fi

if [ "$DURATION" = "ALL" ]; then
        DURATIONBIT=""
else
        DURATIONBIT=" and duration >= $DURATION"
fi

if [ "$CALLCOST" = "ALL" ]; then
        CALLCOSTBIT=""
else
        CALLCOSTBIT=" and callcost >= $CALLCOST"
fi

FROMBIT=""
SELECTBIT=""
if [ "$EXTNLIST" = "ALL" ]; then
        INEXTNLISTBIT=""
        OUTEXTNLISTBIT=""
else
	if [ "$EXTNLIST" = "SQL" ]; then
		INEXTNLISTBIT=" and inletno $EXTNSQLBIT and directtemplate.recordno = directcustom.recordno"
		OUTEXTNLISTBIT=" and outletno $EXTNSQLBIT and directtemplate.recordno = directcustom.recordno"
		SELECTBIT="directcustom.surname, directcustom.firstname"
		FROMBIT=", directtemplate, directcustom"
	else
		INEXTNLISTBIT=" and inletno in ($EXTNLIST)"
		OUTEXTNLISTBIT=" and outletno in ($EXTNLIST)"
		FROMBIT=""
	fi
fi


if [ "$CALLTYPE" = "ALL" ]; then
        CALLTYPEBIT=""

else
        CALLTYPEBIT=" and calltype matches \"[${CALLTYPE}]\""
fi

if [ "$EXPANDEDCALLTYPE" = "ALL" ]; then
        EXPANDEDCALLTYPEBIT=""
else
        EXPANDEDCALLTYPEBIT=" and expandedcalltype matches \"[${EXPANDEDCALLTYPE}]\""
fi

###########################################################
# send file to user
#exec 1>$tmpfile 2>&1
#echo
#echo "          Calldetail Report    `date`"
#echo
#echo "                  From: $SDATE       To: $EDATE"
#echo
#echo "             With Dialled Numbers matching $DIALLEDNO"
#echo "                  Durations greater than or equal to $DURATION seconds"
#echo "                  Costs greater than or equal to $COST cents"
#echo "                  Extension list ($EXTNLIST)"
#echo "                  calltype list ($CALLTYPE)"
#echo "                  expandedcalltype list ($EXPANDEDCALLTYPE)"
#echo "          -----------------------------------------------------------          "
# csv header
if [ "$EXTNLIST" = "SQL" ]; then
	echo "surname,firstname,siteid,calldate,endtime,inlettype,inletno,outlettype,outletno,calltype,calltypedesc,callcategory,dialledno,accode,duration,callcost,expandedcalltype"
else
	echo "siteid,calldate,endtime,inlettype,inletno,outlettype,outletno,calltype,calltypedesc,callcategory,expandedcalltype,dialledno,accode,duration,callcost"
fi


dd=$sdd; mm=$smm; yyyy=$syyyy
while true
do
        dodate="$dd/$mm/$yyyy"
        tbldate=`echo $dodate | sed -e 's/\//_/g'`
        cdrtable="c$tbldate"
        #echo "dodate=$dodate  cdrtable=$cdrtable  dd=$dd  mm=$mm  yyyy=$yyyy"
        #echo
        #echo "          For $dodate"
        runisql 2>/dev/null <<SQLCMD0
unload to $tmp1file DELIMITER "|"
--# INCOMING
select $SELECTBIT siteid, calldate, endtime, inlettype, inletno, outlettype, outletno, calltype, calltypedesc, callcategory, dialledno, accesscode, duration, callcost, expandedcalltype
from $cdrtable, outer calltypedef $FROMBIT
where inlettype not matches "[EP]" and outlettype matches "[EP]"
 $SITEBIT $OUTEXTNLISTBIT $DIALLEDNOBIT $DURATIONBIT $CALLCOSTBIT $CALLTYPEBIT $EXPANDEDCALLTYPEBIT $EXTRASQLBIT
and $cdrtable.calltype = calltypedef.calltypecode
UNION
--# OUTGOING
select $SELECTBIT siteid, calldate, endtime, inlettype, inletno, outlettype, outletno, calltype, calltypedesc, callcategory, dialledno, accesscode, duration, callcost, expandedcalltype
from $cdrtable, outer calltypedef $FROMBIT
where inlettype matches "[EP]" and outlettype not matches "[EP]"
 $SITEBIT $INEXTNLISTBIT $DIALLEDNOBIT $DURATIONBIT $CALLCOSTBIT $CALLTYPEBIT $EXPANDEDCALLTYPEBIT $EXTRASQLBIT
and $cdrtable.calltype = calltypedef.calltypecode
UNION
--# INTERNAL
select $SELECTBIT siteid, calldate, endtime, inlettype, inletno, outlettype, outletno, calltype, calltypedesc, callcategory, inletno || "", accesscode, duration, callcost, expandedcalltype
from $cdrtable, outer calltypedef $FROMBIT
where inlettype matches "[EP]" and outlettype matches "[EP]"
 $SITEBIT $INEXTNLISTBIT $DIALLEDNOBIT $DURATIONBIT $CALLTYPEBIT $EXPANDEDCALLTYPEBIT $EXTRASQLBIT
and $cdrtable.calltype = calltypedef.calltypecode
UNION
--# TRANSIT
select $SELECTBIT siteid, calldate, endtime, inlettype, inletno, outlettype, outletno, calltype, calltypedesc, callcategory, dialledno, accesscode, duration, callcost, expandedcalltype
from $cdrtable, outer calltypedef $FROMBIT
where inlettype not matches "[EP]" and outlettype not matches "[EP]"
 $SITEBIT $INEXTNLISTBIT $DIALLEDNOBIT $DURATIONBIT $CALLTYPEBIT $EXPANDEDCALLTYPEBIT $EXTRASQLBIT
and $cdrtable.calltype = calltypedef.calltypecode
order by 2
SQLCMD0

	# make into csv
	if [ -f "$tmp1file" ]; then
		sed -e "s/,/;/g; s/|$//; s/|/,/g" < $tmp1file >$tmp2file
		rm -f "$tmp1file"
	fi

	# format output
	awk -F ',' -v"EXTNLIST=$EXTNLIST" '
	{
		f = 0
		if ( EXTNLIST == "SQL" ) {
			surname = $(++f)
			firstname = $(++f)
		}
		siteid = $(++f)
		calldate = $(++f)
		endtime = $(++f)
		inlettype = $(++f)
		inletno = $(++f)
		outetno = $(++f)
		outlettype = $(++f)
		calltype = $(++f)
		calltypedesc = $(++f)
		callcategory = $(++f)
		dialled_cli = $(++f)
		accesscode = $(++f)
		duration = $(++f)
		callcost = $(++f)
		expandedcalltype = $(++f)
		#printf("%-6.6s %-10.10s %6.6s %1.1s %-10.10s %-1.1s %-10.10s %-1.1s %-20.20s %-1.1s %-1.1s %-20.20s %12d %6d %6.2f %-1.1s\n",
		if ( EXTNLIST == "SQL" ) {
			printf("%s,%s", surname, firstname)
		}
		printf("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,+\"%s\",%s,%s,%s\n",
			siteid, calldate, endtime, inlettype, inletno, outetno, outlettype, calltype, calltypedesc, callcategory, expandedcalltype, dialled_cli, accesscode, duration, callcost)

	}' <"$tmp2file"

        # increment date variables
        dd=`expr $dd + 1 | awk '{printf("%02d\n",$1)}'` 
        if [ $dd -gt `lastdayinmonth $mm $yyyy` ]; then
                dd=01
                mm=`expr $mm + 1 | awk '{printf("%02d\n",$1)}'` 
                if [ $mm -gt 12 ]; then
                        mm=01
                        yyyy=`expr $yyyy + 1 | awk '{printf("%04d\n",$1)}'`     
                fi
        fi

        # break out if we are past end date
        if [ $yyyy$mm$dd -gt $eyyyy$emm$edd ]; then
                break;
        fi
        #echo
done

#echo
#echo "                                   End\f"

# send file to user
#cat $tmpfile | pr_rep system extncalls_
rm -f "$tmpfile"
rm -f "$tmp1file"
rm -f "$tmp2file"

}

#####################################################
# main

sdd=`echo $SDATE | sed -e 's/\/..\/....$//g'`
smm=`echo $SDATE | sed -e 's/^..\///g;s/\/....$//g'`
syyyy=`echo $SDATE | sed -e 's/^..\/..\///g'`

edd=`echo $EDATE | sed -e 's/\/..\/....$//g'`
emm=`echo $EDATE | sed -e 's/^..\///g;s/\/....$//g'`
eyyyy=`echo $EDATE | sed -e 's/^..\/..\///g'`

if [ "$SEPARATEFILES" = "1" ]; then
	ALL_EXTNLIST="$EXTNLIST"
        for EXTN in `echo "$ALL_EXTNLIST" | tr "," " "`
        do
		outfile="extncalls_${EXTN}_${sdd}${smm}${syyyy}_to_${edd}${emm}${eyyyy}.csv"

		EXTNLIST="$EXTN"
		echo "running: EXTNLIST=[$EXTNLIST] do_extncalls8 outfile=[$outfile]"
        	do_extncalls8 >"$outfile"
        done
else
	outfile="extncalls8_$$.csv"
	echo "running: EXTNLIST=[$EXTNLIST] do_extncalls8 outfile=[$outfile]"
        do_extncalls8 >"$outfile"
fi

exit 0