report_dilia.sh

#!/bin/bash

INSTANCE="nazev_instance_kohy"
SIGLA="sigla_knihovny"
USERNAME="vypdilia"
PASSWORD="heslo"

YEAR=$(date "+%Y")
QUARTAL=$(echo "(`date +%m` - 1)/3" | bc)
if [ "$QUARTAL" == "0" ] ; then
        QUARTAL="4"
        YEAR=$(echo "$YEAR - 1" | bc)
fi

MY_DIR="`dirname \"$0\"`"
CURR_DIR=${PWD}
cd "$MY_DIR"

TMPFILE=issues.txt
FILENAME=$SIGLA\_$YEAR\_$QUARTAL.xml

case "$QUARTAL" in
        "1" )
                DATEFROM="$YEAR-01-01"
                DATETO="$YEAR-03-31" ;;
        "2" )
                DATEFROM="$YEAR-04-01"
                DATETO="$YEAR-06-30" ;;
        "3" )
                DATEFROM="$YEAR-07-01"
                DATETO="$YEAR-09-30" ;;
        "4" )
                DATEFROM="$YEAR-10-01"
                DATETO="$YEAR-12-31" ;;
esac

header='<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>'
root='<VFPData>';
footer='</VFPData>';

echo "SELECT SUBSTRING_INDEX(REPLACE(b.isbn, '-', ''),' ',1) as isxn, COUNT(*) AS sum_pocet FROM statistics s JOIN items t ON s.itemnumber = t.itemnumber JOIN biblioitems b on t.biblionumber = b.biblionumber WHERE t.itype = 'KN' AND s.type IN ('renew', 'issue') AND datetime BETWEEN '$DATEFROM 00:00:00' AND '$DATETO 23:59:59' GROUP BY isxn HAVING isxn REGEXP '[0-9]+' ORDER BY sum_pocet DESC" | koha-mysql $INSTANCE | tee "$TMPFILE"

echo $header > $FILENAME
echo $root >> $FILENAME
while read line; do
        isbn=$(echo $line | sed 's/\([0-9X]*\)\s\([0-9]*\)/\1/')
        suma=$(echo $line | sed 's/\([0-9X]*\)\s\([0-9]*\)/\2/')
        echo "<issues isxn=\"$isbn\" sum_pocet=\"$suma\" />" >> $FILENAME
done <$TMPFILE
echo $footer >> $FILENAME

HOST=ftp.nkp.cz

cd /root/Dilia

ftp -inv $HOST <<EOF

user $USERNAME $PASSWORD

binary

passive

put ${FILENAME}

bye
EOF

cd "$CURR_DIR"