#!/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("", ++cnt) #for ( i=1; i <= NF; ++i ) { # printf("", $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 "
%d%s
" #print "
" cols = 6 # plus one bglevel spanning 2 cols. cols += 2 print "" if ( ADESCRIPTION != "" ) { printf("") printf("") printf("", ADESCRIPTION) printf("") printf("") printf("") } printf("") printf("") printf("") printf("") printf("") printf("") printf("") printf("") 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("", row) printf("", TICKETNUMBER) printf("", TICKETNUMBERthreaddatetime) printf("", Description) printf("", RequestedBy) printf("", ServiceType) if ( bglevel == "" ) printf("") else printf("", bglevel) printf("") print "" } print "
 

%s

  
_#ReferenceDate TimeDescriptionRequested ByProduct/Service TypeBG Level
%d%s%s%s%s%s %0.2f
" 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