: !/bin/sh
# usage: gett21WBDL SDATE EDATE STIME ETIME [DUMMYSITEID] [OUTFILE]
echo "$0: $@"

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

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

if [ -n "$6" ]; then
	OUTFILE="$6"
else
	OUTFILE="t21WBDL.unl"
fi

#################################
## setup site sql
#if [ "$SITEID" = "ALL" ]; then
#	SITEBIT=""
#else
#	SITEBIT=" and site.siteid = $SITEID"
#fi

if [ -z "$retroBDL" ]; then
	retroBDL="0"
	export retroBDL
fi

##############################
# set environment variables.
    . /u/catcom/catcom_vars

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

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

dec_mm_yyyy()
{
        mm=`expr $mm - 1 | awk '{printf("%02d\n",$1)}'` 
        if [ $mm -lt 1 ]; then
		mm=12
		yyyy=`expr $yyyy - 1 | awk '{printf("%04d\n",$1)}'`     
        fi
	edd=`lastdayinmonth $mm $yyyy`
        #echo "dec_mm_yyy $sdd-$edd $mm/$yyyy"
}

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

MMYYYY=`SDATEtoMMYYYY $SDATE`
echo "MMYYYY = [$MMYYYY]"

billperiod=`MMYYYYtobillperiod $MMYYYY _`
echo "$MMYYYY - billperiod = [$billperiod]"

################################
# get previous billperiod

mm=`echo "$MMYYYY" | awk '{printf("%02d\n",substr($1,1,2))}'`     
yyyy=`echo "$MMYYYY" | awk '{printf("%04d\n",substr($1,3,4))}'`     
echo "mm=[$mm]  yyyy=[$yyyy]"
dec_mm_yyyy
echo "mm=[$mm]  yyyy=[$yyyy]"
prev_billperiod=`MMYYYYtobillperiod ${mm}${yyyy} _`
echo "prev_billperiod=[$prev_billperiod]"

# cache local copy of tPerson
copytPerson

###################################
thispwd=`pwd`
tmpfile1="/tmp/get21WBDL.$$"
rm -f "$tmpfile1"

(
if [ "$retroBDL" != "1" ]; then		# normal run
	runisql <<SQLCMD0
unload to "$tmpfile1" DELIMITER "|"
select wbdconf.recordno, wbdconf.distid, wbdconf.distiddesc, wbdconf.lastdist,
	wbdconf.lastdistdate, wbdconf.altemail, wbdconf.altemailyn,
	wbdconf.wbd_method, wbdconf.wbd_type,
	tPerson.emailaddress, wbdconf.passwd,
	wbdconf.sdate, wbdconf.edate,
	wbdlist.grid, tPerson.emailaddress
from wbdconf, wbdlist, tPerson
where wbdconf.distid = wbdlist.distid
  and wbdconf.userid = "email"
  and (tPerson.emailaddress is not null and tPerson.emailaddress != " ")
  and wbdconf.recordno = tPerson.PersonID
  and wbdconf.sdate <= "$EDATE"
  and (wbdconf.edate IS NULL or
       (wbdconf.edate IS NOT NULL and wbdconf.edate > "$EDATE")
      )
UNION
select wbdconf.recordno, wbdconf.distid, wbdconf.distiddesc, wbdconf.lastdist,
	wbdconf.lastdistdate, wbdconf.altemail, wbdconf.altemailyn,
	wbdconf.wbd_method, wbdconf.wbd_type,
	wbdconf.userid, wbdconf.passwd,
	wbdconf.sdate, wbdconf.edate,
	wbdlist.grid, tPerson.emailaddress
from wbdconf, wbdlist, tPerson
where wbdconf.distid = wbdlist.distid
  and wbdconf.userid != "email"
  and wbdconf.recordno = tPerson.PersonID
  and wbdconf.sdate <= "$EDATE"
  and (wbdconf.edate IS NULL or
       (wbdconf.edate IS NOT NULL and wbdconf.edate > "$EDATE")
      )
order by 1, 2
SQLCMD0
else		# retro	- get userid from emailaddress in current tPerson
		# 	- ie. don't allow wb4.awk to use the tPerson
		#       -     from the month being regenerated, to get userid
	runisql <<SQLCMD1
unload to "$tmpfile1" DELIMITER "|"
select wbdconf.recordno, wbdconf.distid, wbdconf.distiddesc, wbdconf.lastdist,
	wbdconf.lastdistdate, wbdconf.altemail, wbdconf.altemailyn,
	wbdconf.wbd_method, wbdconf.wbd_type,
	tPerson.emailaddress, wbdconf.passwd,
	wbdconf.sdate, wbdconf.edate,
	wbdlist.grid, tPerson.emailaddress,
	wbdcreate.createfrom,
	wbdcreate.created, wbdcreate.createddate,
	wbdcreate.completeddate
from wbdconf, wbdlist, tPerson, wbdcreate
where wbdconf.distid = wbdlist.distid
  and wbdconf.userid = "email"
  and (tPerson.emailaddress is not null and tPerson.emailaddress != " ")
  and wbdconf.recordno = tPerson.PersonID
  and (wbdconf.edate IS NULL or
       (wbdconf.edate IS NOT NULL and wbdconf.edate > "$EDATE")
      )
  and ( wbdconf.distid = wbdcreate.distid
	  and wbdcreate.completeddate is null
	  and ( ((wbdcreate.created is null or wbdcreate.created = " ")
		and wbdcreate.createfrom = "$billperiod"
		)
	     or (wbdcreate.created is not null
		 and wbdcreate.created = "$prev_billperiod"
		)
	      )
      )
UNION
select wbdconf.recordno, wbdconf.distid, wbdconf.distiddesc, wbdconf.lastdist,
	wbdconf.lastdistdate, wbdconf.altemail, wbdconf.altemailyn,
	wbdconf.wbd_method, wbdconf.wbd_type,
	wbdconf.userid, wbdconf.passwd,
	wbdconf.sdate, wbdconf.edate,
	wbdlist.grid, tPerson.emailaddress,
	wbdcreate.createfrom,
	wbdcreate.created, wbdcreate.createddate,
	wbdcreate.completeddate
from wbdconf, wbdlist, tPerson, wbdcreate
where wbdconf.distid = wbdlist.distid
  and wbdconf.userid != "email"
  and wbdconf.recordno = tPerson.PersonID
  and (wbdconf.edate IS NULL or
       (wbdconf.edate IS NOT NULL and wbdconf.edate > "$EDATE")
      )
  and ( wbdconf.distid = wbdcreate.distid
	  and wbdcreate.completeddate is null
	  and ( ((wbdcreate.created is null or wbdcreate.created = " ")
		and wbdcreate.createfrom = "$billperiod"
		)
	     or (wbdcreate.created is not null
		 and wbdcreate.created = "$prev_billperiod"
		)
	      )
      )
order by 1, 2
SQLCMD1
fi
) 2>&1

tr "," ";" <$tmpfile1 >"$thispwd/$OUTFILE"

rm -f "$tmpfile1"

exit 0