I have a flat file database called data.txt , example 6 fields: SerialNumber | PartNumber | Location | Installdate | Expiredate | Email 123456H12 | 501-1234-02 | NE | 10/12/2002 | 12/10/2002 | someone@test.com 565464564 | 342-2222-32 | CA | 11/12/2002 | 12/15/2002 | hello@test.com if I want an email reminder (use crontab send at midnight) to send to anyone have Expiredate will be expire within 3 days. It will continue send mail until that person update the expiredate. One more thing, if I have 10 serial numbers which will expire in 3 days, I really don't want to have 10 emails send to me. I need only one mail to remind with 10 serial number and expire date. Do you think shell program can do it ?

Your flat file fields, as shown, are delimited by both spaces and bar characters. If I process the fields as delimited by bar characters, the spaces become part of the data and must be dealt with as regards compares, formatting etc. My awk code deletes all the spaces on the assumption that none of your data fields will ever have an embedded space. This script gets current date, but logic is needed to add 3 days to $today to produce $soon. If someone does not offer their solution, maybe I will next week. A work directory is used, which is emptied at start of each run. awk then writes each expired or soon-to-expire line into this directory. The email address is used as the filename. If someone@test.com has 10 qualifying lines, those 10 lines get written into the same file named someone@test.com. Then, each file that is created is mailed. script appends to a log file showing who was emailed on what date. #!/bin/sh # WARNING: The work directory specified below will # get emptied on each execution of this script. workdir=/u01/home/britney/expire today=`date +%Y%m%d` # Logic needed to add 3 days to $today soon=20021215 if [ -d "$workdir" ] ; then rm -f $workdir/* else print "abort: invalid workdir: $workdir" exit 1 fi awk -F"\|" -v soon=$soon -v workdir=$workdir '\ BEGIN {getline} # bypass header line { gsub(" ","") split($5,part,"/") expdate=part[3] part[1] part[2] if (expdate> fname} }' britney.txt # mail each file created for fn in `ls $workdir` do print "$today Mailing to $fn" >> britney.log mail $fn < $workdir/$fn done exit 0 The outputted lines currently look like the following, but awk can output whatever: Part: 342-2222-64 SN: 565464564 expires: 12/15/2002 Part: 342-2222-65 SN: 565464565 expires: 12/12/2002

Hi James, Please come back next week and help me :=) I am not sure how you use >>> if (expdate > fname} you mean if (expdate > fname) but you have not declare fname yet :( should it be if (expdate < $today) then do something ??? Hope to see you again. TD

Sorry about that TD. I usually check closely for mis-postings, but this one got by me. If the posted text contains a left-angle bracket followed (maybe lines later) by a right-angle bracket, all text between the two are lost. Following is the entire script again, but this time, to avoid the left-angle bracket, I switched the operands of a compare so that instead of using LE I am now using GE. The left-angle bracket on the mail command is not a problem since it is not followed by a right-angle bracket. #!/bin/sh # WARNING: The work directory specified below will # get emptied on each execution of this script. workdir=/u01/home/britney/expire today=`date +%Y%m%d` # Logic needed to add 3 days to today soon=20021215 if [ -d "$workdir" ] ; then rm -f $workdir/* else print "abort: invalid workdir: $workdir" exit 1 fi awk -F"\|" -v soon=$soon -v workdir=$workdir '\ BEGIN {getline} # bypass header line { gsub(" ","") split($5,part,"/") expdate=part[3] part[1] part[2] if (soon>=expdate) {fname=workdir "/" $6 print "Part: " $2 " SN: " $1 \ " expires: " $5 >> fname} }' britney.txt # mail each file created for fn in `ls $workdir` do print "$today Mailing to $fn" >> britney.log mail $fn < $workdir/$fn done exit 0

Hi James, How about 3 days in advance ? how do you do that ? I follow your code and it mail everything, include the one will expire in 30 days ??? It mail every records ??? Thanks

My script, as posted above, did not compute 3 days in advance, and instead used a hard-coded value for $soon. I had that set to 20021215 to make a lot of things expire for testing purposes. The following version of the script includes the future date calculation. For testing, you might want to comment out the mail command and see what messages it creates for mailing. To prevent this web page from losing my indentation, I have changed leading spaces to leading underscores, so you will have to change those back to spaces. #!/bin/ksh # WARNING: The work directory specified below will # get emptied on each execution of this script. workdir=/u01/home/britney/expire # Following code computes future date as specified by $forward, # which can be set to anything from 0 to 28. # Leap year calculation is good through 2099 (does not handle # years modulo 100 and 400). forward=3 set -A dim 0 31 28 31 30 31 30 31 31 30 31 30 31 typeset -Z2 day month date '+%m %d %Y' | read month day year ((dtm=${dim[$month]}+!($year%4))) ((day=$day+$forward)) if [ $day -gt $dtm ] ; then ___((month=$month+1)) ___((day=$day-dtm)) ___if [ $month -eq 13 ] ; then _______month=1 _______((year=$year+1)) ___fi fi soon=$year$month$day if [ -d "$workdir" ] ; then ___rm -f $workdir/* else ___print "abort: invalid workdir: $workdir" ___exit 1 fi awk -F"\|" -v soon=$soon -v workdir=$workdir '\ BEGIN {getline} # bypass header line { gsub(" ","") split($5,part,"/") expdate=part[3] part[1] part[2] if (soon>=expdate) ___{fname=workdir "/" $6 ____print "Part: " $2 " SN: " $1 \ ________" expires: " $5 >> fname} }' britney.txt # mail each file created for fn in `ls $workdir` __do __print "`date` Mailing to $fn" >> britney.log __mail $fn < $workdir/$fn __done exit 0

Sorry, but in the above future date calculation, the leap day would add too often. Please replace the line: ((dtm=${dim[$month]}+!($year%4))) with the following two lines: dtm=${dim[$month]} (($month-2)) || (($year%4)) || ((dtm=$dtm+1))