#!/bin/sh
echo "Content-type: text/html"
echo ""
DODBG="false"; export DODBG;
tmpfile1="/tmp/tmp1_$$.tmp"
tmpfile2="/tmp/tmp2_$$.tmp"
rm -f $tmpfile1
rm -f $tmpfile2
##############################################
# TESTING
#SDATE="01/06/1996"
#EDATE="03/06/1996"
MONTH="07/2014"
#SITEID=ALL
##############################################
myprogname=`basename "$0"`
mytitle="Extracting data from Telmax21"
echo ""
echo ""
echo "$mytitle"
echo ""
echo ""
echo "$myprogname
"
echo "$mytitle...
"
# set environment variables.
#. /u/catcom/catcom_vars
#################################################################
lastdayinmonth() # Returns the last day in month, Inputs: $1=mm $2=yyyy
{
cal $1 $2 | awk '{ if ( NF > 0 ) x=$NF } END { print x }'
}
###################################
# handle params
# stdin post
if [ "$REQUEST_METHOD" = "POST" ]; then
cmd=`echo "$stdinput" | awk -f wbt_splitqstr.awk`
$DODBG && echo "cmd=[$cmd]
" >>"$dbgfile"
for cmdtok in `echo "$cmd"`
do
$DODBG && echo "cmdtok=[$cmdtok]
" >>"$dbgfile"
fixcmdtok=`echo "$cmdtok" | ./wbt_fixqstr`
$DODBG && echo "fixcmdtok=[$fixcmdtok]
" >>"$dbgfile"
eval "$fixcmdtok"
done
$DODBG && echo "
" >>"$dbgfile"
fi
# process query string
if [ ! -z "$QUERY_STRING" ]; then
$DODBG && echo "QUERY_STRING=[$QUERY_STRING]
" >>"$dbgfile"
qstr=`echo "$QUERY_STRING" | awk -f wbt_splitqstr.awk`
$DODBG && echo "qstr=[$qstr]
" >>"$dbgfile"
for qtok in `echo "$qstr"`
do
$DODBG && echo "qtok=[$qtok]
" >>"$dbgfile"
#echo "qtok=[$qtok]" >>/tmp/1
fixqtok=`echo "$qtok" | ./wbt_fixqstr`
$DODBG && echo "fixqtok=[$fixqtok]
" >>"$dbgfile"
#echo "fixqtok=[$fixqtok]" >>/tmp/1
eval $fixqtok
done
$DODBG && echo "
" >>"$dbgfile"
fi
# handle cookies
if [ ! -z "$HTTP_COOKIE" ]; then
cookie=`echo "$HTTP_COOKIE" | awk -f wbt_splitqstr.awk`
$DODBG && echo "cookie=[$cookie]
" >>"$dbgfile"
for cookietok in `echo "$cookie"`
do
$DODBG && echo "cookietok=[$cookietok]
" >>"$dbgfile"
fixcookietok=`echo "$cookietok" | ./wbt_fixqstr`
fixcookietok="conf_$fixcookietok"
$DODBG && echo "fixcookietok=[$fixcookietok]
" >>"$dbgfile"
eval $fixcookietok
done
$DODBG && echo "
" >>"$dbgfile"
fi
###################################
# get lock
#LOCKFILE="/tmp/t21websql.lok"
#while true
#do
# if [ ! -f "$LOCKFILE" ]; then
# break;
# fi
# sleep 2
#done
#touch $LOCKFILE
###################################
if [ -n "$MONTH" ]; then
mm=`echo $MONTH | sed -e 's/\/....$//g'`
yyyy=`echo $MONTH | sed -e 's/^..\///g'`
ldd=`lastdayinmonth $mm $yyyy`
SDATE="01/$mm/$yyyy"
EDATE="$ldd/$mm/$yyyy"
$DODBG && echo "MONTH=[$MONTH] ldd=[$ldd] SDATE=[$SDATE] EDATE=[$EDATE]
"
fi
#if [ -z "$SITEID" -z "$SDATE" -o -z "$EDATE" ]; then
# echo "You Must set SITEID, MONTH (mm/yyyy) or SDATE and EDATE (dd/mm/yyyy)"
# echo "
"
# exit 1
#fi
if [ -z "$SITEID" ]; then
SITEID="ALL"
fi
########
if [ "$SITEID" = "ALL" ]; then
SITEBIT=""
else
SITEBIT="and siteid = $SITEID"
fi
##################################################################
SQLCMD0=`cat << MYSQL0
select ticket_id, body from ost_ticket_thread where thread_type='N' and MONTH(created)=7 ORDER BY ticket_id;
MYSQL0
`
##################################################################
echo "SITEID=[$SITEID] MONTH=[$MONTH] SDATE=[$SDATE] EDATE=[$EDATE]
"
echo "SQLCMD0=[$SQLCMD0]"
echo "
"
sdd=`echo $SDATE | sed -e 's/\/..\/....$//g'`
smm=`echo $SDATE | sed -e 's/^..\///g;s/\/....$//g'`
syyyy=`echo $SDATE | sed -e 's/^..\/..\///g'`
edd=`echo $EDATE | sed -e 's/\/..\/....$//g'`
emm=`echo $EDATE | sed -e 's/^..\///g;s/\/....$//g'`
eyyyy=`echo $EDATE | sed -e 's/^..\/..\///g'`
#(echo "unload to $tmpfile1 $SQLCMD0" | runisql 2>&1) >$tmpfile2
(
echo "$SQLCMD0" | mysql -u apache -ptimsnart osticket >$tmpfile1
) >$tmpfile2
if [ ! -s $tmpfile1 ]; then
echo "NO OUTPUT|SQL:[$SQLCMD0]|" >$tmpfile1
echo "ERROR:|$tmpfile2|" >$tmpfile1
fi
cat $tmpfile1 | sed -e 's/|$//' | awk -F'|' ' \
BEGIN { \
print ""
} \
{ \
print ""
for ( i=1; i <= NF; ++i ) {
printf("| %s | ", $i)
}
print "\n"
print "
"
} \
END { \
print "
"
}'
#dd=$sdd; mm=$smm; yyyy=$syyyy
#while true
#do
# dodate="$dd/$mm/$yyyy"
# tbldate=`echo $dodate | sed -e 's/\//_/g'`
# cdrtable="c$tbldate"
# $DODBG && echo "dodate=$dodate cdrtable=$cdrtable dd=$dd mm=$mm yyyy=$yyyy"
#
###############
###############
#
# # increment date variables
# dd=`expr $dd + 1 | awk '{printf("%02d\n",$1)}'`
# if [ $dd -gt `lastdayinmonth $mm $yyyy` ]; then
# dd=01
# mm=`expr $mm + 1 | awk '{printf("%02d\n",$1)}'`
# if [ $mm -gt 12 ]; then
# mm=01
# yyyy=`expr $yyyy + 1 | awk '{printf("%04d\n",$1)}'`
# fi
# fi
#
# # break out if we are past end date
# if [ $yyyy$mm$dd -gt $eyyyy$emm$edd ]; then
# break;
# fi
# #echo
#done
# show errors
if [ -s $tmpfile2 ]; then
echo "
"
echo "SQL Status:"
cat $tmpfile2
echo "
"
fi
rm -f $tmpfile1
rm -f $tmpfile2
###############################
# remove lock
#rm -f $LOCKFILE
###############################
echo ""
echo "Done."
echo ""
echo ""
exit 0