#!/bin/sh

echo "<META http-equiv='Expires' content='-1'>"
echo "<META http-equiv=Content-Type content='text/csv'>"
echo "Content-type: text/csv\n"

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

OPDIRCSVFILE="TF202aopdir.csv"

###################################
# set working dir
#cd /u/catcom/t21web/direnq

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

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

rm -f $tmpfile1
cp test.unl $tmpfile1

#runisql <<SQLCMD0
#database catcom;
#unload to "$tmpfile1" DELIMITER ","
#
#select dt.recordno,
# dc.surname, dc.firstname, dt.extension,
# dc.title, dc.section, dc.college,
# dc.telephone, dc.faxno, dc.mobile, dc.pager,
# dc.location, dc.temp_casual, dt.equip_type[1,6], dc.empid,
# dc.comments, dc.email,
# dt.site[1,4], dt.extgroupunique, gg.parentgroupid,
# "", "",
# dm.msg1, dm.msg2, dm.msg3, dm.msg4, dm.msg5, dm.msg6, dm.msg7, dm.msg8, dm.msg9
#from directtemplate dt, directcustom dc, outer dirmesg dm, outer gengroup gg
#where dt.recordno = dc.recordno
#    and dt.recordno = dm.recordno
#    and dt.extgroupunique = gg.childgroupid
#    and dt.secure_code = "Y"
#order by 2, 3, 5
#SQLCMD0

(
# csv header

echo "\
RecordNo,Surname__________________,Firstname______,#Extn__,^]2~01Title_________________________________________,^2]2~04Section_______________________________________,^3]2~07College_______________________________________,^4]#~02Telephone___,^4]~05#Fax_Number__,^4]~08#Mobile______,^4]2~11#Pager_______,^5]2~10Location____________,^5]~03TempCasual,^5]~06Equip_,^5]~09#EmpID_____,[2~12Comments________________________________,[2~14#Email___________________________________,[~15Site,[11~13GroupID_,[~16ParentID,Picture,Document,Msg1,Msg2,Msg3,Msg4,Msg5,Msg6,Msg7,Msg8,Msg9"

# csv data
cat $tmpfile1 | sed -e 's/,$//;s/\"/\\\"/g;s/ *\,/\,/g'

# tack on test rows
cat <<TESTROWS
99998,SELBY,JOHN,3819,TTTTTTTTTTTTTTTTTTTTTTTTTT01234567890123456789,SSSSSSSSSSSSSSSSSSSSSSSSSS01234567890123456789,CCCCCCCCCCCCCCCCCCCCCCCCCC01234567890123456789,TT0123456789,FF0123456789,MM0123456789,AA0123456789,LLLLLLLLLL0123456789,TT23456789,E1E789,E2E789,CCCCCCCCCCCCCCCCCCCC01234567890123456789,EEEE.EEEEE@EEEE.EEEE01234567890123456789,SITE,GG456789,PP456789,roster1.jpg,roster1.htm,This is a sample message,You can have 9 lines,This is line 3,The following line has 50 characters,01234567890123456789012345678901234567890123456789,This is the sixth line,line7,second last line,            and the last LINE.
99999,SHEPPARDSSSSSSS0123456789,RODNEYFF3456789,123456,TTTTTTTTTTTTTTTTTTTTTTTTTT01234567890123456789,SSSSSSSSSSSSSSSSSSSSSSSSSS01234567890123456789,CCCCCCCCCCCCCCCCCCCCCCCCCC01234567890123456789,TT0123456789,FF0123456789,MM0123456789,AA0123456789,LLLLLLLLLL0123456789,TT23456789,E1E789,E2E789,CCCCCCCCCCCCCCCCCCCC01234567890123456789,EEEE.EEEEE@EEEE.EEEE01234567890123456789,SITE,GG456789,PP456789,telegirl.gif,,This is a sample message,You can have 9 lines,This is line 3,The following line has 50 characters,01234567890123456789012345678901234567890123456789,This is the sixth line,line 7,second last line,            and the last LINE.
TESTROWS


) >$OPDIRCSVFILE

rm -f $tmpfile1

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

cat $OPDIRCSVFILE

exit 0