: /bin/sh
#################################################################
# batch file for finding calls
# CUSTOMDESC 
# CUSTOMDEF 

progname=$0

internalcost="0.05"

EXCL_PAR_LIST='"UNA", "TLM"'

EXCL_SITEID_LIST='201'

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

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

echo "SDATE=$SDATE"
echo "EDATE=$EDATE"
echo "STIME=$STIME"
echo "ETIME=$ETIME"
echo "SITEID=$SITEID"

if [ -n "$6" ]; then
        P6=$6
else
        P6=""
fi

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

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

TMPSTABLE="cdr2$LOGNAME"

tmpfile=/tmp/cdr_$$
tmpfile0=/tmp/cdr0_$$
tmpfile1=/tmp/cdr1_$$
tmpfile2=/tmp/cdr2_$$

rm -f $tmpfile
rm -f $tmpfile0
rm -f $tmpfile1
rm -f $tmpfile2

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

########
#Testing
#SDATE="02/06/1996"
#EDATE="03/06/1996"

#SDATE="01/05/2002"
#EDATE="31/05/2002"
########

#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 [ "$SITEID" = "ALL" ]; then
        SITEBIT=""
else
        SITEBIT="and siteid = $SITEID"
fi

# send file to user
#exec 1>$tmpfile 2>&1
#echo
#echo "          Calls Report    `date`"
#echo
#echo "                  From: $SDATE       To: $EDATE"
#echo
#echo "          -----------------------------------------------------------          "


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>&1 <<SQLCMD

drop table $TMPSTABLE;

create table $TMPSTABLE (
	calltype char(1) not null,
	extn integer not null,
	siteid smallint not null,
	eggrid char(8) not null,
	gggrid char(8) not null
);
create index i_$TMPSTABLE on $TMPSTABLE (siteid, extn, calltype);

insert into $TMPSTABLE
select unique trk_extsum.calltype, inoutno, trk_extsum.siteid, trk_extsum.grid, gengroup.parentgroupid
from trk_extsum, gengroup
where inouttype matches "[EP]" and sumdate = "$dodate"
	and gengroup.childgroupid = trk_extsum.grid;

unload to $tmpfile1 DELIMITER ","

# out calls
select gggrid, eggrid, inletno, calldate, endtime, $cdrtable.siteid, outletno, "out", dialledno,  callcategory, calltypedesc, $cdrtable.duration, $cdrtable.callcost
from $cdrtable, calltypedef, $TMPSTABLE
where inlettype matches "[EP]"
  and $cdrtable.calltype = calltypecode
  and inletno = $TMPSTABLE.extn
  and $cdrtable.siteid = $TMPSTABLE.siteid
  and $cdrtable.calltype = $TMPSTABLE.calltype
  and acccodeanswflag != "N"
  and $cdrtable.calltype != "J"
  and gggrid not in ($EXCL_PAR_LIST)
  and $cdrtable.siteid not in ($EXCL_SITEID_LIST)
union ALL
select gggrid, eggrid, inletno, calldate, endtime, $cdrtable.siteid, outletno, "out", outletno || "",  callcategory, calltypedesc, $cdrtable.duration, $cdrtable.callcost
from $cdrtable, calltypedef, $TMPSTABLE, groupname
where inlettype matches "[EP]"
  and $cdrtable.calltype = calltypecode
  and inletno = $TMPSTABLE.extn
  and $cdrtable.siteid = $TMPSTABLE.siteid
  and $cdrtable.calltype = $TMPSTABLE.calltype
  and acccodeanswflag != "N"
  and $cdrtable.calltype = "J"
  and groupname.grid = $TMPSTABLE.eggrid
  and groupname.grtype = "E"
  and groupname.tarifftype != "D"
  and gggrid not in ($EXCL_PAR_LIST)
  and $cdrtable.siteid not in ($EXCL_SITEID_LIST)
union ALL
select gggrid, eggrid, inletno, calldate, endtime, $cdrtable.siteid, outletno, "out", outletno || "",  callcategory, calltypedesc, $cdrtable.duration, $cdrtable.callcost
from $cdrtable, calltypedef, $TMPSTABLE, groupname
where inlettype matches "[EP]"
  and $cdrtable.calltype = calltypecode
  and inletno = $TMPSTABLE.extn
  and $cdrtable.siteid = $TMPSTABLE.siteid
  and $cdrtable.calltype = $TMPSTABLE.calltype
  and acccodeanswflag != "N"
  and $cdrtable.calltype = "J"
  and groupname.grid = $TMPSTABLE.eggrid
  and groupname.grtype = "E"
  and groupname.tarifftype = "D"
  and $cdrtable.duration <= 0
  and gggrid not in ($EXCL_PAR_LIST)
  and $cdrtable.siteid not in ($EXCL_SITEID_LIST)
union ALL
select gggrid, eggrid, inletno, calldate, endtime, $cdrtable.siteid, outletno, "out", outletno || "",  callcategory, calltypedesc, $cdrtable.duration, $internalcost
from $cdrtable, calltypedef, $TMPSTABLE, groupname
where inlettype matches "[EP]"
  and $cdrtable.calltype = calltypecode
  and inletno = $TMPSTABLE.extn
  and $cdrtable.siteid = $TMPSTABLE.siteid
  and $cdrtable.calltype = $TMPSTABLE.calltype
  and acccodeanswflag != "N"
  and $cdrtable.calltype = "J"
  and groupname.grid = $TMPSTABLE.eggrid
  and groupname.grtype = "E"
  and groupname.tarifftype = "D"
  and $cdrtable.duration > 0
  and gggrid not in ($EXCL_PAR_LIST)
  and $cdrtable.siteid not in ($EXCL_SITEID_LIST)
union ALL

# in calls
select gggrid, eggrid, outletno, calldate, endtime, $cdrtable.siteid, inletno, "in", dialledno,  callcategory, calltypedesc, $cdrtable.duration, $cdrtable.callcost
from $cdrtable, calltypedef, $TMPSTABLE
where outlettype matches "[EP]"
  and $cdrtable.calltype = calltypecode
  and outletno = $TMPSTABLE.extn
  and $cdrtable.siteid = $TMPSTABLE.siteid
  and ($cdrtable.calltype != "J" and $cdrtable.calltype = $TMPSTABLE.calltype)
  and gggrid not in ($EXCL_PAR_LIST)
  and $cdrtable.siteid not in ($EXCL_SITEID_LIST)
union ALL
select gggrid, eggrid, outletno, calldate, endtime, $cdrtable.siteid, inletno, "in", inletno || "",  "A", "Incoming Internal", $cdrtable.duration, $cdrtable.callcost
from $cdrtable, calltypedef, $TMPSTABLE
where outlettype matches "[EP]"
  and $cdrtable.calltype = calltypecode
  and outletno = $TMPSTABLE.extn
  and $cdrtable.siteid = $TMPSTABLE.siteid
  and ($cdrtable.calltype = "J" and "A" = $TMPSTABLE.calltype)
  and gggrid not in ($EXCL_PAR_LIST)
  and $cdrtable.siteid not in ($EXCL_SITEID_LIST)

order by 1, 2, 3, 4, 5;

SQLCMD

	cat $tmpfile1 >>$tmpfile2

	rm -f $tmpfile1

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

# add in extension from directory to get 0 totals for extns without calls
runisql 2>&1 <<SQLCMD2
unload to $tmpfile1 DELIMITER ","
select unique gengroup.parentgroupid, gengroup.childgroupid, extension, "$EDATE", "000000", site.siteid, 0, "in", "", "X", "", "0", "0.00", -1
from directtemplate, gengroup, site
where  extgroupunique = gengroup.childgroupid
   and site.sitenm1[1,4] = directtemplate.site[1,4]
  and gengroup.parentgroupid not in ($EXCL_PAR_LIST)
  and site.siteid not in ($EXCL_SITEID_LIST)
  and gengroup.parentgroupid != "VAC"
order by 1, 2, 3, 4, 5;
SQLCMD2
cat $tmpfile1 >>$tmpfile2


# sort calls so they are in par,eggrid,extn,date,time order
sort $tmpfile2 >$tmpfile0

#sed -e 's/,$//' <$tmpfile1 >>$tmpfile0
#awk -f c9.awk -F ',' -v "sdate=$SDATE" -v "edate=$EDATE" < $tmpfile0 >$tmpfile1

# send file to user
#cat $tmpfile | pr_rep system cdr_
cp $tmpfile0 c9.in
rm -f $tmpfile
rm -f $tmpfile0
rm -f $tmpfile1
rm -f $tmpfile2

exit 0