return to PRS Technologies website
<![CDATA[
################################################################################
# Delete (or recycle) all Elite reports for a selected user over a certain age
# that has a NULL filename or where the ascii file pointed to by elite is missing.
# for a specific user id
rm -f delete_id.unl
rm -f delete_rpt.out
rm -f winoutstat_old.unl
rm -f winoutstat_delete.unl
if [ ! -d RECYCLE_BIN1 ]; then
mkdir RECYCLE_BIN1
fi
echo "enter USER id (or * for all users)"
read USER
echo
echo "enter how many days old to delete"
read AGE
echo "backup the winoutstat table."
dbaccess son_db <<-EOF
--unload to "winoutstat.unl" select * from winoutstat;
unload to "winoutstat_old.unl" select * from winoutstat where (today - wodate0) > $AGE and wofile2 IS NULL and wouser matches "*${USER}*";
EOF
echo
echo "Unloading older rows from table..."
IFS="|"
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
echo "NO FILENAME for $WOUSER on $WODATE0 $WOREPORT " | tee -a delete_rpt.out
echo "$WOINDEX" >> delete_id.unl
continue
fi
if [ -s $WOFILE ]; then
continue
else
echo "$WOFILE for $WOUSER on $WODATE0 is missing $WOREPORT " | tee -a delete_rpt.out
echo "$WOINDEX" >> delete_id.unl
fi
done < winoutstat_old.unl
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.unl" insert into peter1;
!echo "Unload records to be deleted into ascii file"
unload to "winoutstat_delete.unl" 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 "%-8s %-10s %s %s\n",WOUSER,WODATE0,WOFILE,WOREPORT
}
' winoutstat_delete.unl > delete_rpt.out
pg delete_rpt.out
echo
echo "Press <Enter> to delete records ( <INTERRUPT> 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.unl" insert into peter1;
!echo "delete rows..."
delete from winoutstat where woindex in (select f1 from peter1);
EOF
echo
echo "Removing empty 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 [ -s $WOFILE ]; then
continue
else
if [ -f $WOFILE ]; then
ls -ls $WOFILE | tee -a delete_rpt.out
mv $WOFILE RECYCLE_BIN1
fi
fi
done < winoutstat_old.unl
echo
echo "Completed"
################################################################################
]]>