#!/bin/sh
# get Blood Glucose Levels
echo "Content-type: text/html"
echo ""
DODBG="false"; export DODBG;
RODS_BGLEVEL_TICKETNUMBER="1730"
tmpdir=`pwd`"/tmp"
echo "tmpdir: $tmpdir"
tmpfile1="/$tmpdir/tmp1_${ANOTETAG}_$$.tmp"
tmpfile2="/$tmpdir/tmp2_${ANOTETAG}_$$.tmp"
rm -f "$tmpfile1"
rm -f "$tmpfile2"
##############################################
## Testing
#SDATE="01/08/2014"
#EDATE="31/08/2014"
#MONTH="08/2014"
#TICKETNUMBER="$RODS_BGLEVEL_TICKETNUMBER"
##ORGANIZATION="ALL"
#NOTETAG="BGLevel:"
##SITEID=ALL
###############################################
myprogname=`basename "$0"`
mytitle="Extracting data from osTicket"
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
$DODBG && echo "
"
###################################
# 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]
"
$DODBG && echo "
"
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 [ -z "$TICKETNUMBER" ]; then
TICKETNUMBER="$RODS_BGLEVEL_TICKETNUMBER"
fi
if [ -z "$ORGANIZATION" ]; then
ORGANIZATION="ALL"
fi
if [ -z "$NOTETAG" ]; then
NOTETAG="BGLevel:"
fi
########
if [ "$SITEID" = "ALL" ]; then
SITEBIT=""
else
SITEBIT="and siteid = $SITEID"
fi
if [ "$TICKETNUMBER" = "" ]; then
TICKETNUMBERBIT=""
else
TICKETNUMBERBIT="and ost_ticket.number = \"${TICKETNUMBER}\""
fi
if [ "$ORGANIZATION" = "ALL" -o "$ORGANIZATION" = "" ]; then
ORGANIZATIONBIT=""
else
ORGANIZATIONBIT="and UPPER(ost_organization.name) = UPPER('$ORGANIZATION')"
fi
if [ "$MONTH" != "" ]; then
DATERANGESQLBIT="MONTH(ost_ticket_thread.created)=$mm and YEAR(ost_ticket_thread.created)=$yyyy"
else
DATERANGESQLBIT="DATE_FORMAT(ost_ticket.created, '%d/%m/%Y') >= \"$SDATE\""
if [ "$EDATE" != "" ]; then
DATERANGESQLBIT=" and DATE_FORMAT(ost_ticket.created, '%d/%m/%Y') <= \"EDATE\""
fi
fi
##################################################################
getBGLog()
{
ANOTETAG="$1"
ADESCRIPTION="$2"
AID="$3"
SQLCMD0=`cat << MYSQL0
SELECT
ost_ticket.number,
DATE_FORMAT(ost_ticket.created, '%d/%m/%Y'),
ost_ticket__cdata.subject,
ost_user.name,
--# ost_organization.name,
--# ost_staff.username,
ost_help_topic.topic,
ost_ticket_thread.created,
ost_ticket_thread.body
FROM ( ost_ticket
INNER JOIN ost_ticket__cdata
ON ost_ticket.ticket_id = ost_ticket__cdata.ticket_id
INNER JOIN ost_ticket_thread
ON ost_ticket.ticket_id = ost_ticket_thread.ticket_id
AND ost_ticket_thread.thread_type = "N"
AND LOWER(ost_ticket_thread.body) REGEXP "$ANOTETAG *[0-9]*"
)
LEFT OUTER JOIN (
ost_user LEFT OUTER JOIN ost_organization
ON ost_user.org_id = ost_organization.id
) ON ost_user.id = ost_ticket.user_id
LEFT OUTER JOIN
ost_staff ON ost_staff.staff_id = ost_ticket.staff_id
LEFT OUTER JOIN
ost_help_topic ON ost_help_topic.topic_id = ost_ticket.topic_id
WHERE $DATERANGESQLBIT
$TICKETNUMBERBIT
$ORGANIZATIONBIT
ORDER BY ost_ticket_thread.created, ost_ticket.number
MYSQL0
`
##################################################################
echo "SITEID=[$SITEID] MONTH=[$MONTH] SDATE=[$SDATE] EDATE=[$EDATE]
"
echo "
"
echo "TICKETNUMBER=[$TICKETNUMBER]"
echo "
"
echo "ORGANIZATION=[$ORGANIZATION]"
echo "
"
echo "ANOTETAG=[$ANOTETAG]"
echo "
"
echo "
"
echo "SQLCMD0=[$SQLCMD0]"
echo "
"
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 "id: "`id`
echo "
"
echo "pwd: "`pwd`
echo "
"
echo "tmpfile1=[$tmpfile1]"
echo "
"
echo "tmpfile2=[$tmpfile2]"
echo "
"
# remove tmp files
rm -f "$tmpfile1" 2>&1
rm -f "$tmpfile2" 2>&1
#(echo "unload to $tmpfile1 $SQLCMD0" | runisql 2>&1) >$tmpfile2
(
echo "$SQLCMD0
INTO OUTFILE '$tmpfile1'
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n';
" | mysql -u apache -ptimsnart osticket 2>&1
) >$tmpfile2
#echo " DEBUG: --------------- tmpfile1 -------------------"
#echo "
"
#cat "$tmpfile1" | sed 's/$/
/'
#echo " DEBUG: --------------------------------------------"
#echo "
"
#cp "$tmpfile1" /tmp/1
#chmod 664 "$tmpfile1" 2>&1
#chmod 664 "$tmpfile2" 2>&1
##strip tempfile header for improting into excel
#sed -i '1d' $tmpfile1
# show errors
if [ -s $tmpfile2 ]; then
echo "
"
echo "SQL Status:"
cat $tmpfile2
echo "
"
fi
##################################
if [ ! -s $tmpfile1 ]; then
echo "NO OUTPUT|SQL:[$SQLCMD0]|" >$tmpfile1
echo "ERROR:|$tmpfile2|" >>$tmpfile1
fi
cat $tmpfile1 | sed -e 's/|$//' | awk -F'|' \
-v "SDATE=$SDATE" \
-v "EDATE=$EDATE" \
-v "MONTH=$MONTH" \
-v "TICKETNUMBER=$TICKETNUMBER" \
-v "ORGANIZATION=$ORGANIZATION" \
-v "ANOTETAG=$ANOTETAG" \
-v "ADESCRIPTION=$ADESCRIPTION" \
-v "AID=$AID" \
'
function trim(s)
{
sub(/^[ \t]*/,"",s)
sub(/[ \t]*$/,"",s)
gsub(/\,/,";",s)
return s
}
function ceil(n)
{
return (int(n) == n ? n : (int(n) + 1))
}
BEGIN {
NROWS = 0
prev_rowid = ""
#print ""
}
# ussumes data is in datetime order for the Internal Time:/Quote: threads
{
print "$0: [" $0 "]"
print "
"
#print ""
#printf("| %d | ", ++cnt)
#for ( i=1; i <= NF; ++i ) {
# printf("%s | ", $i)
#}
#print "\n"
#print "
"
TICKETNUMBER = trim($1)
TICKETNUMBERdate = trim($2)
ticketDesc = trim($3)
RequestedBy = trim($4)
ServiceType = trim($5)
TICKETNUMBERthreaddatetime = trim($6)
threadtext = trim($7)
CAT = ServiceType
CATDesc = ServiceType
CATDesc_arr[CAT] = CATDesc
rowid = TICKETNUMBER "|" TICKETNUMBERthreaddatetime
TICKETNUMBER_arr[rowid] = TICKETNUMBER
TICKETNUMBERcreateddate_arr[TICKETNUMBER] = TICKETNUMBERdate
ticketDesc_arr[TICKETNUMBER] = ticketDesc
RequestedBy_arr[TICKETNUMBER] = RequestedBy
ServiceType_arr[TICKETNUMBER] = ServiceType
TICKETNUMBERthreaddatetime_arr[rowid] = TICKETNUMBERthreaddatetime
threadtext_arr[rowid] = threadtext
# get BG level fromfrom after Note TAG
i = index(tolower(threadtext),tolower(ANOTETAG))
j = 0
if ( i > 0 ) {
j = match(substr(threadtext, length(ANOTETAG)), / *[0-9]*\.*[0-9]*/)
}
if ( j > 0 ) {
print " DEBUG: threadtext=[" threadtext "]"
print "
"
#s1 = substr(threadtext, RSTART, RLENGTH)
s1 = substr(threadtext, i)
#print " s1=[" s1 "]"
#print "
"
split(s1, time_sub, ":")
#print " time_sub[1]=[" time_sub[1] "]"
#print "
"
#print " time_sub[2]=[" time_sub[2] "]"
#print "
"
bglevel = 0 + sprintf("%0.2f", time_sub[2])
printf(" bglevel=[%0.2f]", bglevel)
print "
"
}
else {
bglevel = ""
}
if ( bglevel == "" )
printf("bglevel not found")
print "
"
# store BGLevel
TICKETNUMBERthreaddatetime_bglevel_arr[rowid] = bglevel
if ( rowid != prev_rowid ) {
proc_order_arr[++NROWS] = rowid
#print "DEBUG: proc_order_arr[" NROWS "]=[" proc_order_arr[NROWS] "]"
#print "
"
}
prev_rowid = rowid
}
END {
#print "
"
#print "
"
cols = 6
# plus one bglevel spanning 2 cols.
cols += 2
print ""
if ( ADESCRIPTION != "" ) {
printf("")
printf("| | ")
printf("%s | ", ADESCRIPTION)
printf(" | ")
printf(" | ")
printf("
")
}
printf("")
printf("| _# | ")
printf("Reference | ")
printf("Date Time | ")
printf("Description | ")
printf("Requested By | ")
printf("Product/Service Type | ")
printf("BG Level | ")
printf("
")
print ""
for ( row = 1; row <= NROWS; ++row ) {
rowid = proc_order_arr[row]
TICKETNUMBER = TICKETNUMBER_arr[rowid]
TICKETNUMBERdate = TICKETNUMBERcreateddate_arr[TICKETNUMBER]
ticketDesc = ticketDesc_arr[TICKETNUMBER]
RequestedBy = RequestedBy_arr[TICKETNUMBER]
ServiceType = ServiceType_arr[TICKETNUMBER]
TICKETNUMBERthreaddatetime = TICKETNUMBERthreaddatetime_arr[rowid]
threadtext = threadtext_arr[rowid]
# set description from thread body text
#Description = ticketDesc "
" threadtext
# >>>BGLevel: 7.8
Got up 2:05pm test before breakfast, 2 x Toast with Marg<<<
#Description = threadtext
#gsub(/
/,", ",s)
length_tt_arr = split(threadtext, tt_arr, "
")
#print "length_tt_arr=[" length_tt_arr "]" "
"
#print "tt_arr[1]=[" tt_arr[1] "]" "
"
#print "tt_arr[2]=[" tt_arr[2] "]" "
"
#print "tt_arr[3]=[" tt_arr[3] "]" "
"
#print "tt_arr[4]=[" tt_arr[4] "]" "
"
Description = ""
for (i=2; i <= length_tt_arr; ++ i ) {
if ( tt_arr[i] == "" )
continue
if (Description != "" )
Description = Description ", "
Description = Description tt_arr[i]
#print "..i=[" i "]....Description=[" Description "]"
}
#Description = ">>>" Description "<<<"
#x = index(threadtext, "
")
#if ( x > 0 )
# x += 6
#else
# x = 1
#Description = substr(threadtext, x)
#x = 1
#if ( index(tolower(threadtext),tolower(ANOTETAG)) ) {
# if ( i = match(threadtext, / *[0-9]*\.*[0-9]*/) ) {
# #x = RSTART + RLENGTH
# x = i
# }
#}
#subDescription = substr(threadtext, x)
#Description = "RSTART=[" RSTART "] RLENGTH=[" RLENGTH "] x=[" x "]" " {" subDescription "}"
CAT = ServiceType
TICKETNUMBER_list_arr[TICKETNUMBER] = 1
CAT_list_arr[CAT] = 1
#print " TICKETNUMBERthreaddatetime_bglevel_arr[" rowid "]=[" TICKETNUMBERthreaddatetime_bglevel_arr[rowid] "]"
bglevel = TICKETNUMBERthreaddatetime_bglevel_arr[rowid]
printf("")
printf("| %d | ", row)
printf("%s | ", TICKETNUMBER)
printf("%s | ", TICKETNUMBERthreaddatetime)
printf("%s | ", Description)
printf("%s | ", RequestedBy)
printf("%s | ", ServiceType)
if ( bglevel == "" )
printf(" | ")
else
printf("%0.2f | ", bglevel)
printf("
")
print ""
}
print "
"
print "
"
}
'
rm -f "$tmpfile1" 2>&1
rm -f "$tmpfile2" 2>&1
}
getBGLog "$NOTETAG" "Blood Glucose Level Log" "BGLog"
#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
rm -f "$tmpfile1"
rm -f "$tmpfile2"
###############################
# remove lock
#rm -f $LOCKFILE
###############################
echo ""
echo "Done."
echo ""
echo ""
exit 0