#!/bin/ksh
#cd /home/john/

. sybase
. ss_pto.id

echo "****** Please do not reply to this message ******" > ss_pto.status
echo " " >> ss_pto.status
echo New PTO and Special Requests ADDED as of: `date` >> ss_pto.status
echo " " >> ss_pto.status
echo Please add to Outlook calendars: >> ss_pto.status
#export today=`date`


date '+%m %d %Y %H %M %S' | 
{ 
read MONTH DAY YEAR HOUR MINUTE SECOND
DAY=`expr "$DAY" - 1` 
case "$DAY" in 
        0) 
           MONTH=`expr "$MONTH" - 1` 
                case "$MONTH" in 
                        0) 
                           MONTH=12 
                           YEAR=`expr "$YEAR" - 1` 
                        ;; 
                esac 
        DAY=`cal $MONTH $YEAR | grep . | fmt -1 | tail -1` 
esac 
 
} 
tempdate="$YEAR"-"$MONTH"-"$DAY"" ""$HOUR":"$MINUTE":"$SECOND"


isql -U $U -P $P -S $S -w1000 -o tmp_isql.status << label1
 use ptodb_rch01_prod
go
 select RTRIM(usr.fname) "First name", convert(char(20), usr.lname) "Last name", convert(char(15), ind.pto_date, 107) "PTO Date", ind.comments "Comments"
 from ss_pto_user usr, ss_pto_index ind
 where
 	ind.user_id = usr.user_id and ind.request_date >= '$tempdate' and (ind.class_id = 7 and ind.section_id = 5 or ind.class_id = 2 and ind.section_id = 4 ) 
 order by usr.lname asc

go
label1


reccnt=`wc -l tmp_isql.status`
if [reccnt = 0]
message="no records returned"
echo $message>>ss_pto.status
else
cat tmp_isql.status>>ss_pto.status

mailx -s"SS PTO" -senders email reciepents email < ss_pto.status