: !/bin/sh
# extncalls7.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="01/07/2013"
EDATE="31/07/2013"
SITEID="ALL"
DIALLEDNO="ALL"
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\""


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

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


#################################################################
# MAIN

tmpfile=/tmp/calls_$$
tmp1file=/tmp/calls_1_$$

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=", directtemplate"
	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,duration,callcost,expandedcalltype"
else
	echo "siteid,calldate,endtime,inlettype,inletno,outlettype,outletno,calltype,calltypedesc,callcategory,dialledno,duration,callcost,expandedcalltype"
fi


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'`

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, 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, 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 || "", 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, inletno || "", 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
		rm -f "$tmp1file"
	fi

#	# format output
#	awk -F '|' '
#	{
#		siteid = $1
#		calldate = $2
#		endtime = $3
#		inlettype = $4
#		inletno = $5
#		outetno = $6
#		outlettype = $7
#		calltype = $8
#		calltypedesc = $9
#		callcategory = $10
#		dialled_cli = $11
#		duration = $12
#		callcost = $13
#		expandedcalltype = $14
#		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 %6d %6.2f\n",
#			siteid, calldate, endtime, inlettype, inletno, outetno, outlettype, calltype, calltypedesc, callcategory, expandedcalltype, dialled_cli, duration, callcost)
#	}' <"$tmp1file"

        # 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"

exit 0