Small script for Naumen Phone Outsourcing

There is a task at the beginning of each month to unload from the depths of Naumen Phone Outsourcing (for simplicity, I will call it further NPO) call records for the previous month with sorting by projects and by month. How to implement it?
In NPO, all this stuff is stored at "/ opt / naumen / nauphone / spool / naubuddyd / mp3 / YYYY / MM /". The only "but" - all this is stored in one heap. We come to the aid of the tbl_gl_call_info table in which we select the names of files with records of conversations on a specific project (for this there is a sessionid field for the name of the file to which we add the extension ".wav", and the parentuid field in which the project ID is stored). The time interval we need is stored in the incomingtime field. In this case, we will be interested only in calls to which the operator answered ( finalstage = 'operator' ) or which the operator forwarded ( finalstage = 'redirect' ). Everything is done with one simple select:

select distinct a.sessionid||'.wav' from tbl_gl_call_info a where a.parentuid='__project_ID' and to_number(to_char(a.incomingtime, 'YYYY'))=2013 and to_number(to_char(a.incomingtime, 'MM'))=04 and (a.finalstage='operator' or a.finalstage='redirect') 

At the exit of the script we get the desired list of files. Now all we have to do is find these files and write them to the directory we need.
Then my natural laziness turns on, and I decide to entrust all the routine work to the computer. First, create a file in which we will match the project name with its ID in the NPO system:

 Project_1 project1code Project_2 project2code 

and leave at the end an empty line.
Then we entrust all the work to this script, indicating at launch the year and month for which you need to collect files:
')

 #!/bin/bash ################################################## # (cc) 2013 by Sergey Kirgizov (skirgizov@ya.ru) # ################################################## #     year=`date +%Y` if [[ ! "$1" || ! "$2" ]]; then echo ": report.sh YYYY MM" exit fi if [[ "$1" -lt "2012" || "$1" -gt "$year" ]]; then echo "      2012  $year" exit fi if [[ "$2" -lt "01" || "$2" -gt "12" ]]; then echo "   .    01  12" exit fi #   wdir="/home/user/report" #   user=user #        NPO pass=password #  dbase="url.to.database:port/databasename" # URL     conf_lines=`cat $wdir/etc/projects.conf | wc -l` let "conf_lines += 1" # wc       ,   0 . i=0 #     rm -rf $wdir/data/* rm -rf $wdir/export/* #    while [ "$i" -lt "$conf_lines" ] do let "i += 1" project=`cat $wdir/etc/projects.conf | head -n$i | tail -n1 | awk '{print $1}'` code=`cat $wdir/etc/projects.conf | head -n$i | tail -n1 | awk '{print $2}'` echo "select distinct a.sessionid||'.wav' from tbl_gl_call_info a where a.parentuid='$code'" > $wdir/data/waw.sql echo "and to_number(to_char(a.incomingtime, 'YYYY'))=$1 and to_number(to_char(a.incomingtime, 'MM'))=$2" >> $wdir/data/waw.sql echo "and (a.finalstage='operator' or a.finalstage='redirect');" >> $wdir/data/waw.sql echo "exit;" >> $wdir/data/waw.sql sqlplus -S $user/$pass@$dbase @$wdir/data/waw.sql | grep nauss > $wdir/data/"$project".lst #  "grep nauss",     sqlplus'a done i=1 #   while [ "$i" -lt "$conf_lines" ] do project=`cat $wdir/etc/projects.conf | head -n$i | tail -n1 | awk '{print $1}'` code=`cat $wdir/etc/projects.conf | head -n$i | tail -n1 | awk '{print $2}'` FILES=`cat $wdir/data/"$project".lst` mkdir $wdir/export/$project echo -n "  $project..." n=0 for file in $FILES do let "n += 1" find /opt/naumen/nauphone/spool/naubuddyd/mp3/$1/$2 -name $file -exec cp {} --parents --target-directory=$wdir/export/$project/ \; done mv $wdir/export/$project/opt/naumen/nauphone/spool/naubuddyd/mp3/$1/$2/* $wdir/export/$project/ #         rm -rf $wdir/export/$project/opt #    echo " $n ." let "i += 1" done 

To complete the picture, create a script that will run the previous script once a month with the necessary parameters, and send the result to us by mail:

 #!/bin/bash #   wdir=/home/user/report #   year=`date +%Y` #   month=`date --date "1 month ago" +%m` #   SUBJECT="  $month ." #   EMAIL="mail@mailserver.com" #  ( ) EMAILMESSAGE="$wdir/msg.txt" #       #  echo "  $month  $year ." > $EMAILMESSAGE $wdir/report.sh $year $month >> $EMAILMESSAGE #          /bin/mail -s "$SUBJECT" "$EMAIL" < $EMAILMESSAGE #   

And feed him cron'u

 0 1 1 * * /home/user/report/report.sh 

That's all. I hope someone will come in handy.