#!/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
SHOWACTUALHOURS="N"
##############################################
## Testing
##SDATE="01/06/1996"
##EDATE="03/06/1996"
#MONTH="07/2014"
#CUSTOMER="Victrack"
#TIMETAG="Time:"
#SHOWACTUALHOURS="Y"
#SHOWTOTALS="Y"
###SITEID=ALL
##############################################
myprogname=`basename "$0"`
mytitle="Extracting data from Telmax21"
echo "<html>"
echo "<head>"
echo "<title>$mytitle</title>"
echo "</head>"
echo "<body>"
echo "<h2>$myprogname</h2>"
echo "<h3>$mytitle...</h3>"
# 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]<br>" >>"$dbgfile"
for cmdtok in `echo "$cmd"`
do
$DODBG && echo "cmdtok=[$cmdtok]<br>" >>"$dbgfile"
fixcmdtok=`echo "$cmdtok" | ./wbt_fixqstr`
$DODBG && echo "fixcmdtok=[$fixcmdtok]<br>" >>"$dbgfile"
eval "$fixcmdtok"
done
$DODBG && echo "<hr>" >>"$dbgfile"
fi
# process query string
if [ ! -z "$QUERY_STRING" ]; then
$DODBG && echo "QUERY_STRING=[$QUERY_STRING]<br>" >>"$dbgfile"
qstr=`echo "$QUERY_STRING" | awk -f wbt_splitqstr.awk`
$DODBG && echo "qstr=[$qstr]<br>" >>"$dbgfile"
for qtok in `echo "$qstr"`
do
$DODBG && echo "qtok=[$qtok]<br>" >>"$dbgfile"
#echo "qtok=[$qtok]" >>/tmp/1
fixqtok=`echo "$qtok" | ./wbt_fixqstr`
$DODBG && echo "fixqtok=[$fixqtok]<br>" >>"$dbgfile"
#echo "fixqtok=[$fixqtok]" >>/tmp/1
eval $fixqtok
done
$DODBG && echo "<hr>" >>"$dbgfile"
fi
# handle cookies
if [ ! -z "$HTTP_COOKIE" ]; then
cookie=`echo "$HTTP_COOKIE" | awk -f wbt_splitqstr.awk`
$DODBG && echo "cookie=[$cookie]<br>" >>"$dbgfile"
for cookietok in `echo "$cookie"`
do
$DODBG && echo "cookietok=[$cookietok]<br>" >>"$dbgfile"
fixcookietok=`echo "$cookietok" | ./wbt_fixqstr`
fixcookietok="conf_$fixcookietok"
$DODBG && echo "fixcookietok=[$fixcookietok]<br>" >>"$dbgfile"
eval $fixcookietok
done
$DODBG && echo "<hr>" >>"$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]<br>"
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 "<br>"
# exit 1
#fi
if [ -z "$SITEID" ]; then
SITEID="ALL"
fi
if [ -z "$CUSTOMER" ]; then
CUSTOMER="ALL"
fi
if [ -z "$TIMETAG" ]; then
TIMETAG="Time:"
fi
########
if [ "$SITEID" = "ALL" ]; then
SITEBIT=""
else
SITEBIT="and siteid = $SITEID"
fi
if [ "$CUSTOMER" = "ALL" -o "$CUSTOMER" = "" ]; then
CUSTOMERBIT=""
else
CUSTOMERBIT="and UPPER(ost_organization.name) = UPPER('$CUSTOMER')"
fi
##################################################################
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,
DATE_FORMAT(ost_ticket_thread.created, '%d/%m/%Y'),
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 "$TIMETAG *[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 MONTH(ost_ticket_thread.created)=$mm and YEAR(ost_ticket_thread.created)=$yyyy
$CUSTOMERBIT
ORDER BY DATE_FORMAT(ost_ticket_thread.created, '%d/%m/%Y'), ost_ticket.number
MYSQL0
`
##################################################################
echo "SITEID=[$SITEID] MONTH=[$MONTH] SDATE=[$SDATE] EDATE=[$EDATE]<br>"
echo "CUSTOMER=[$CUSTOMER]"
echo "TIMETAG=[$TIMETAG]"
echo "SHOWACTUALHOURS=[$SHOWACTUALHOURS]"
echo "SHOWTOTALS=[$SHOWTOTALS]"
echo "SQLCMD0=[$SQLCMD0]"
echo "<br>"
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
INTO OUTFILE '$tmpfile1'
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n';
" | mysql -u apache -ptimsnart osticket 2>&1
) >$tmpfile2
##strip tempfile header for improting into excel
#sed -i '1d' $tmpfile1
##################################
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 "CUSTOMER=$CUSTOMER" \
-v "TIMETAG=$TIMETAG" \
-v "SHOWACTUALHOURS=$SHOWACTUALHOURS" \
-v "SHOWTOTALS=$SHOWTOTALS" \
'
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 = ""
if ( substr(toupper(SHOWACTUALHOURS),1,1) == "N" )
doshowactualhours = 0
else
doshowactualhours = 1
if ( substr(toupper(SHOWTOTALS),1,1) == "N" )
doshowtotals = 0
else
doshowtotals = 1
#print "<table border=1>"
}
# ussumes data is in datetime order for the Internal Time:/Quote: threads
{
#print "<tr>"
#printf("<td>%d</td>", ++cnt)
#for ( i=1; i <= NF; ++i ) {
# printf("<td>%s</td>", $i)
#}
#print "\n"
#print "</tr>"
NCR = trim($1)
NCRdate = trim($2)
ticketDesc = trim($3)
RequestedBy = trim($4)
ServiceType = trim($5)
NCRthreaddate = trim($6)
threadtext = trim($7)
##if ( NCR != "1722" )
## next
#print $0 "<br>"
rowid = NCR "|" NCRthreaddate
NCR_arr[rowid] = NCR
NCRcreateddate_arr[NCR] = NCRdate
ticketDesc_arr[NCR] = ticketDesc
RequestedBy_arr[NCR] = RequestedBy
ServiceType_arr[NCR] = ServiceType
NCRthreaddate_arr[rowid] = NCRthreaddate
threadtext_arr[rowid] = threadtext
# add up timese for each day
###i = match(tolower(threadtext), /time: *[0-9]*\.*[0-9]*/)
##re = "/" TIMETAG " *[0-9]*\.*[0-9]*/"
##i = match(tolower(threadtext), re)
#if ( tolower(TIMETAG) == "quote:" )
# i = match(tolower(threadtext), /quote: *[0-9]*\.*[0-9]*/)
#else
# i = match(tolower(threadtext), /time: *[0-9]*\.*[0-9]*/)
i = index(tolower(threadtext),tolower(TIMETAG))
if ( i > 0 ) {
#print ">>>>" substr(s1, length(TIMETAG)) "<<<<"
j = match(substr(s1, length(TIMETAG)), / *[0-9]*\.*[0-9]*/)
}
if ( j > 0 ) {
#print " DEBUG: threadtext=[" threadtext "]"
#print "<br>"
#s1 = substr(threadtext, RSTART, RLENGTH)
s1 = substr(threadtext, i)
#print " s1=[" s1 "]"
#print "<br>"
split(s1, time_sub, ":")
#print " time_sub[1]=[" time_sub[1] "]"
#print "<br>"
#print " time_sub[2]=[" time_sub[2] "]"
#print "<br>"
hours = 0 + sprintf("%0.2f", time_sub[2])
#printf(" hours=[%0.2f]", hours)
#print "<br>"
}
else {
hours = 0
}
#printf("hours = [%f]<br>", hours)
hours_daily_total_arr[rowid] += hours
hours_NCR_total_arr[NCR] += hours
if ( rowid != prev_rowid ) {
proc_order_arr[++NROWS] = rowid
#print "DEBUG: proc_order_arr[" NROWS "]=[" proc_order_arr[NROWS] "]"
#print "<br>"
}
prev_rowid = rowid
}
END {
#print "</table>"
#print "<br>"
print "<table border=1>"
printf("<tr>")
printf("<th>#</th>")
printf("<th>Reference</th>")
printf("<th>Date</th>")
printf("<th>Task Description</th>")
printf("<th>Requested By</th>")
printf("<th>Product/Service Type</th>")
if ( doshowactualhours ) {
printf("<th>Daily Ceil Hours Used</th>")
printf("<th> </th>")
printf("<th>Actual Hours</th>")
}
else {
printf("<th>Hours Used</th>")
}
printf("</tr>")
print ""
ceil_grandtotal = 0
hours_grandtotal = 0
for ( row = 1; row <= NROWS; ++row ) {
rowid = proc_order_arr[row]
NCR = NCR_arr[rowid]
NCRdate = NCRcreateddate_arr[NCR]
ticketDesc = ticketDesc_arr[NCR]
RequestedBy = RequestedBy_arr[NCR]
ServiceType = ServiceType_arr[NCR]
NCRthreaddate = NCRthreaddate_arr[rowid]
threadtext = threadtext_arr[rowid]
NCR_list_arr[NCR] = 1
hours_daily_total = hours_daily_total_arr[rowid]
# ceil hours to next whole number for the day
ceil_ticket_hours_for_day = ceil(hours_daily_total)
# keep running totla fof dailyn ceil s for NCR
daily_ceil_NCR_total_arr[NCR] += ceil_ticket_hours_for_day
printf("<tr>")
printf("<td>%d</td>", row)
printf("<td>%s</td>", NCR)
printf("<td>%s</td>", NCRthreaddate)
printf("<td>%s</td>", ticketDesc)
printf("<td>%s</td>", RequestedBy)
printf("<td>%s</td>", ServiceType)
printf("<td>%d</td>", ceil_ticket_hours_for_day)
if ( doshowactualhours ) {
printf("<td> </td>")
printf("<td>%0.2f</td>", hours_daily_total)
}
printf("</tr>")
print ""
ceil_grandtotal += ceil_ticket_hours_for_day
hours_grandtotal += hours_daily_total
}
if ( doshowtotals ) {
# Grand Total
printf("<tr>")
printf("<td> </td>")
printf("<td> </td>")
printf("<td> </td>")
printf("<td> </td>")
printf("<td> </td>")
printf("<td> </td>")
printf("<td> </td>")
if ( doshowactualhours ) {
printf("<td> </td>")
printf("<td> </td>")
}
printf("</tr>")
print ""
printf("<tr>")
printf("<td> </td>")
printf("<td> </td>")
printf("<td> </td>")
printf("<td> </td>")
printf("<td> </td>")
printf("<td>Total</td>")
printf("<td>" ceil_grandtotal "</td>")
if ( doshowactualhours ) {
printf("<td> </td>")
printf("<td>" hours_grandtotal "</td>")
}
printf("</tr>")
print ""
}
print "</table>"
print "<br>"
print "<br>"
# NCR Totals
print "<table border=1>"
printf("<tr>")
printf("<th>Summary</th>")
printf("<th>NCR</th>")
printf("<th>Created</th>")
printf("<th>Description</th>")
printf("<th>Requested By</th>")
printf("<th>Type</th>")
if ( doshowactualhours ) {
printf("<th>NCR Ceil Hours</th>")
printf("<th>Actual Hours</th>")
printf("<th>NCR Total For Daily Ceil Hours Used</th>")
}
else {
printf("<th>NCR Total For Hours Used</th>")
}
printf("</tr>")
print ""
print "<tr>"
for ( NCR in NCR_list_arr ) {
NCRdate = NCRcreateddate_arr[NCR]
ticketDesc = ticketDesc_arr[NCR]
RequestedBy = RequestedBy_arr[NCR]
ServiceType = ServiceType_arr[NCR]
# total for NCR ceiled daily
daily_ceil_hours_NCR_total = daily_ceil_NCR_total_arr[NCR]
# ceil only for NCR
hours_NCR_total = hours_NCR_total_arr[NCR]
ceil_hours_NCR_total = ceil(hours_NCR_total)
printf("<tr>")
printf("<td> </td>")
printf("<td>%s</td>", NCR)
printf("<td>%s</td>", NCRdate)
printf("<td>%s</td>", ticketDesc)
printf("<td>%s</td>", RequestedBy)
printf("<td>%s</td>", ServiceType)
printf("<td>%d</td>", daily_ceil_hours_NCR_total)
if ( doshowactualhours ) {
printf("<td>%d</td>", ceil_hours_NCR_total)
printf("<td>%0.2f</td>", hours_NCR_total)
}
printf("</tr>")
print ""
}
print "</table>"
}
'
#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 "<br>"
echo "SQL Status:"
cat $tmpfile2
echo "<br>"
fi
rm -f $tmpfile1
rm -f $tmpfile2
###############################
# remove lock
#rm -f $LOCKFILE
###############################
echo "<p>"
echo "Done."
echo "</body>"
echo "</html>"
exit 0