#!/bin/sh

#echo "Content-type: text/csv\n"

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

###################################
if [ -n "$1" ]; then
	outfile="$1"
else
	outfile="T21Eghistd.csv"
fi

###################################
#echo "<hr>"
#echo "Process QUERY_STRING=$QUERY_STRING"
#echo "<p>"
#for qtok in `echo "$QUERY_STRING" | tr "&" " "`
#do
#	echo "qtok=[$qtok]"
#	eval $qtok
#done

###################################
# set working dir
#rjs cd /u/catcom/t21web/webdirenq

###################################
# get lock
#LOCKFILE="/tmp/t21websql.lok"
#while true
#do
#	if [ ! -f "$LOCKFILE" ]; then
#		break;
#	fi
#	sleep 2
#done
#touch $LOCKFILE

###################################
tmpfile1="/tmp/webdirtmp$$.unl"

rm -f $tmpfile1

# test if we are to use diretcory history tables
if [ "$2" = "nohist" ]; then

	# use current directory tables
	#runisql <<SQLCMDnohist 1>/dev/null 2>&1
	runisql <<SQLCMDnohist
database catcom;

select unique directoryid
from sidconf
where sidconf.directoryid is not null
  and ( (edate IS NULL and sdate <= "$EDATE")
     or (edate IS NOT NULL and sdate <= "$EDATE" and edate >= "$SDATE")
      )
  and sidconf.directoryid not in (
		select unique recordno from directtemplate
		) into temp baddirid;

select directoryid, "$HDATE" lasthdate from baddirid, directtemplate
where baddirid.directoryid = directtemplate.recordno
group by directoryid
into temp diridhdate;

insert into diridhdate
select unique recordno, "$HDATE"
from directtemplate;

unload to "$tmpfile1" DELIMITER "|"
select dt.recordno, dc.surname, dc.firstname, dt.extension, mc.Mobile, mc.CarrierCode, dc.title, dt.branch, dc.location, dt.extgroupunique, dc.email, dc.fax, dc.telephone, dc.college, dc.department_school, dc.empid, dc.master, "$HDATE"
from directtemplate dt, directcustom dc, diridhdate, outer mobconf mc
where dt.recordno = mc.DirectoryID
  and dt.recordno = dc.recordno
  and dt.recordno = diridhdate.directoryid
  and ( (mc.edate IS NULL and mc.sdate <= "$EDATE")
     or (mc.edate IS NOT NULL and mc.sdate <= "$EDATE" and mc.edate >= "$SDATE")
      )
order by 1, 2, 3, 4, 10;
SQLCMDnohist

else	

	# use directory history tables
	#runisql <<SQLCMD0 1>/dev/null 2>&1
	runisql <<SQLCMD0
database catcom;

select unique directoryid
from sidconf
where sidconf.directoryid is not null
  and ( (edate IS NULL and sdate <= "$EDATE")
     or (edate IS NOT NULL and sdate <= "$EDATE" and edate >= "$SDATE")
      )
  and sidconf.directoryid not in (
		select unique recordno from hist_dt
		where hist_dt.hdate = "$HDATE"
		) into temp baddirid;

select directoryid, max(hdate) lasthdate from baddirid, hist_dt
where baddirid.directoryid = hist_dt.recordno
group by directoryid
into temp diridhdate;

insert into diridhdate
select unique recordno, "$HDATE"
from hist_dt
where hist_dt.hdate = "$HDATE";

unload to "$tmpfile1" DELIMITER "|"
select dt.recordno, dc.surname, dc.firstname, dt.extension, mc.Mobile, mc.CarrierCode, dc.title, dt.branch, dc.location, dt.extgroupunique, dc.email, dc.fax, dc.telephone, dc.college, dc.department_school, dc.empid, dc.master, dc.hdate
from hist_dt dt, hist_dc dc, diridhdate, outer mobconf mc
where dt.recordno = mc.DirectoryID
  and dt.recordno = dc.recordno
  and dt.recordno = diridhdate.directoryid
  and dt.hdate = dc.hdate
  and dt.hdate = diridhdate.lasthdate
  and ( (mc.edate IS NULL and mc.sdate <= "$EDATE")
     or (mc.edate IS NOT NULL and mc.sdate <= "$EDATE" and mc.edate >= "$SDATE")
      )
  and dt.hdate > ("$HDATE" - 90)
order by 1, 2, 3, 4, 10;
SQLCMD0

fi

(
###############################################################
# csv header
echo "RecordNo,Surname_______________________,Firstname______,#eXtension,#Mobile__________,Carrier,Title_________________________,Campus,Location____________,ExtnGroup,Email_____________________________________________,#Fax_Number______,#Telephone_______,Division_College________________________,Department_School_______________________,EmpID_____,Master,HDate_____"

# process directory entrys
sed -e 's/,/\;/g;s/\"/\\\"/g;s/ *\|/\|/g' <$tmpfile1 | \
awk -F '|' '{
	f = 1
	RecordNo = $f++
	Surname = $f++
	Firstname = $f++
	eXtension = $f++
	Mobile = $f++
	Carrier = $f++
	Title = $f++
	Campus = $f++
	Location = $f++
	ExtnGroup = $f++
	Email = $f++
	Fax = $f++
	Telephone = $f++
	Division = $f++
	Department = $f++
	EmpID = $f++
	Master = $f++
	HDate = $f++

	recordno = RecordNo
	ID = recordno

	# renumber recs dups
	# so ID is unique
	# (re: directtemplate to mobconf JOIN)
	# NOTE: data must be ordered by recordno (see above SQL)
	if ( recordno == last_recordno ) {	# dup recordno
		dupcnt = dupcnt + 1
		ID = (recordno * 10000) + (dupcnt % 10000)
	}
	else {
		dupcnt = 0
	}
	last_recordno = recordno

	printf("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
		ID,
		Surname,
		Firstname,
		eXtension,
		Mobile,
		Carrier,
		Title,
		Campus,
		Location,
		ExtnGroup,
		Email,
		Fax,
		Telephone,
		Division,
		Department,
		EmpID,
		Master,
		HDate)

}'

rm -f $tmpfile1

###############################
# hit file
#
#hitsfile="hits"
#lasthit=`ls -altr $hitsfile | cut -c42-53`
#touch $hitsfile
#hits=`cat $hitsfile`
#if [ -z "$hits" ]; then
#	hits="0"
#fi
#hits=`expr $hits + 1`
#echo "$hits" > $hitsfile
#echo "99999,~HITS,$hits,LAST $lasthit,,,,,,,"

###############################
# add Sync record at end
#echo "999999,ZZZZSync,,,,,,,,,"

###############################################################
) >"$outfile"

###############################
# remove lock
#rm -f $LOCKFILE
###############################

exit 0