wiki-feedback-last30.sh

#!/bin/bash

DB=$1
PREFIX=$2

if [ -z "$DB" -o -z "$PREFIX" ]
then
    echo "wiki-feedback-last30.sh databasename tableprefix"
    exit 1
fi

TODAY=`date --utc +%Y%m%d`

QUERY="
select feedback.af_id,
       page.page_title,
       opt.afo_name,
       knownuser.user_name,
       feedback.af_user_ip,
       answer_comment.aa_response_text,
       feedback.af_created
  from ${PREFIX}aft_article_feedback feedback
  -- join the text comment
  inner join ${PREFIX}aft_article_answer answer_comment on feedback.af_id = answer_comment.aa_feedback_id
  inner join ${PREFIX}aft_article_field comment_field on answer_comment.aa_field_id = comment_field.afi_id and comment_field.afi_name = 'comment'
  -- join the type of comment (suggestion/praise/problem/etc.)
  inner join ${PREFIX}aft_article_answer answer_option on feedback.af_id = answer_option.aa_feedback_id
  inner join ${PREFIX}aft_article_field_option opt on answer_option.aa_field_id = opt.afo_field_id and answer_option.aa_response_option_id = opt.afo_option_id
  -- join on the wiki page that the feedback was made for
  inner join ${PREFIX}page page on feedback.af_page_id = page.page_id
  -- join (maybe) on the logged-in user who made the feedback
  left outer join ${PREFIX}user knownuser on feedback.af_user_id = knownuser.user_id
  -- restrict to new feedback; from the past month
  where feedback.af_created >= adddate($TODAY, interval -30 day)
  ;
"

RESULT=$(mysql $DB --xml -e "$QUERY")

echo $RESULT
exit 0