return to PRS Technologies website
################################################################################
#
# Module: fix_winoutstat3.sh
#
# Description:
# Delete (or recycle) all Elite reports for a selected user over a certain age.
#
# Author: Peter R. Schmidt
#
# Change Log
#
# Date Person Description
#
# 03/28/2002 Peter Schmidt Start program
#
################################################################################
. /elite/custom/run.elite
echo "enter USER id (or * for all users)"
read USER
echo
echo "enter how many days old to delete"
read AGE
echo
echo "Recycle or Delete files ? (r/d)"
read OPTION1
if [ $USER = * ]; then
RECYCLE=RECYCLE_BIN3
else
RECYCLE=RECYCLE_BIN3_${USER}
fi
if [ $OPTION1 != d ]; then
if [ ! -d $RECYCLE ]; then
mkdir $RECYCLE
fi
fi
cd $RECYCLE
STAT_OLD=${USER}_winoutstat_old.unl
DELETE_ID_FILE=${USER}_delete_id.unl
DELETE_RPT=${USER}_delete_rpt.out
WINOUTSTAT=${USER}_winoutstat.unl
echo "backup rows to be deleted from the winoutstat table."
dbaccess son_db <<-EOF
unload to "$STAT_OLD"
select * from winoutstat where (today - wodate0) > $AGE and wouser matches "*${USER}*";
EOF
if [ ! -s $STAT_OLD ]; then
echo "Note: no rows older then $AGE days old"
dbaccess son_db <<-EOF
select wodate0, (today - wodate0) age, count(*) from winoutstat where
wouser matches "*${USER}*"
group by 1,2
order by 1,2
EOF
exit 1
fi
echo
echo "Unloading older rows from table..."
IFS="|"
CNT=0
while read WOINDEX WOUSER WOUSER1 WOREPORT WOTITLE WOFILE WODATE0 WOTIME0 WOSTAT1 WODESC1 WODATE1 WOTIME1 WOSTAT2 WODESC2 WODATE2 WOTIME2 WOFILE2 WOPID
do
CNT=`expr $CNT + 1`
if [ "${WOFILE}x" = "x" ]; then
echo "$CNT NO FILENAME for $WOUSER on $WODATE0 $WOREPORT "
echo "$WOINDEX" >> $DELETE_ID_FILE
continue
fi
if [ -s $WOFILE ]; then
echo "$CNT $WOFILE for $WOUSER on $WODATE0 exists $WOREPORT "
echo "$WOINDEX" >> $DELETE_ID_FILE
else
echo "$CNT $WOFILE for $WOUSER on $WODATE0 is missing $WOREPORT "
echo "$WOINDEX" >> $DELETE_ID_FILE
fi
done < $STAT_OLD
echo
echo "select records to be deleted from winoutstat."
dbaccess son_db <<-EOF
create temp table peter1 (f1 integer);
!echo "load id's to be deleted into temp table"
load from "$DELETE_ID_FILE" insert into peter1;
!echo "Unload records to be deleted into ascii file"
unload to "$WINOUTSTAT"
select * from winoutstat where woindex in (select f1 from peter1) order by wouser, wodate0;
EOF
echo "Printing Report"
awk ' \
BEGIN {
FS="|"
}
{
WOINDEX=$1
WOUSER=$2
WOUSER1=$3
WOREPORT=$4
WOTITLE=$5
WOFILE=$6
WODATE0=$7
WOTIME0=$8
WOSTAT1=$9
WODESC1=$10
WODATE1=$11
WOTIME1=$12
WOSTAT2=$13
WODESC2=$14
WODATE2=$15
WOTIME2=$16
WOFILE2=$17
WOPID=$18
printf "%-4d %-8s %-10s %s %s\n",NR, WOUSER,WODATE0,WOFILE,WOREPORT
}
' $WINOUTSTAT > $DELETE_RPT
pg $DELETE_RPT
echo
echo "Press to recycle or delete records ( to quit )"
read answer
echo
echo "delete records from winoutstat..."
dbaccess son_db <<-EOF
create temp table peter1 (f1 integer);
!echo "load id's to be deleted into temp table"
load from "$DELETE_ID_FILE" insert into peter1;
!echo "delete rows from winoutstat..."
delete from winoutstat where woindex in (select f1 from peter1);
EOF
echo
echo "Recycle or delete Unix files..."
echo
while read WOINDEX WOUSER WOUSER1 WOREPORT WOTITLE WOFILE WODATE0 WOTIME0 WOSTAT1 WODESC1 WODATE1 WOTIME1 WOSTAT2 WODESC2 WODATE2 WOTIME2 WOFILE2 WOPID
do
if [ "${WOFILE}x" = "x" ]; then
continue
fi
if [ -f $WOFILE ]; then
ls -ls $WOFILE | tee -a $DELETE_RPT
if [ $OPTION1 = d ]; then
rm $WOFILE
else
mv $WOFILE ../$RECYCLE
fi
fi
done < $STAT_OLD
echo
echo "Completed"
################################################################################