#!/bin/bash
DT=`date +"%d%m%y-%H%M%S"`
####################################################
# Copyright (C) 2012 
# Program: vbmailqueue.sh
# by George Liu (eva2000) vbtechsupport.com
# Updated: June 3rd, 2012 AEST
SCRIPTNAME='vbmailqueue.sh'
SCRIPTURL='vbtechsupport.com'
SCRIPTAUTHOR='George Liu (eva2000)'
VER='0.0.3'
####################################################
# bash script to see how large vB 4.2.x new cron based mail queue is 
# https://www.vbulletin.com/forum/content.php/775-What-is-Cron-Based-Email-Sending
# and provide some stats to better manage settings at
# AdminCP -> vBulletin Options -> Email Options
# for
# Use Cron Based Sending = Yes
# Number of Emails to Send Per Batch =
# Email Send Delay =
####################################################
# [ How to use script ]
#    There 5 primary config variables to edit:
#    1. vB database name
#    2. vB table prefix if you set one in config.php
#    3. MySQL username
#    4. MySQL password
#    5. MySQL host leave at localhost if MySQL on same server other wise need to set
#    remote IP to your MySQL remote server
####################################################
# Primary Configuration Variables
DBNAME='vb420'
TABLEPREFIX=''

USER='root'
PASS=''
MYSQLHOST='localhost'

####################################################
# Optional Configuration Variables
LOGDIR='/root/vbmailqueuelogs'
VBMAILIDSTATSFILE="${LOGDIR}/mailqueue_$DT.log"

####################################################
MAILQUEUETABLE="${TABLEPREFIX}mailqueue"

if [ -z $PASS ]; then
MYSQLADMINOPT=''
else
MYSQLADMINOPT="-u $USER -p$PASS -h $MYSQLHOST"
fi

if [ ! -d "$LOGDIR" ]; then 
mkdir $LOGDIR 
fi

#if [ ! -f "$VBMAILIDSTATSFILE" ]; then 
#touch $VBMAILIDSTATSFILE 
#fi
####################################################

byline() {
echo "------------------------------------------------------------------"
echo "$SCRIPTNAME $VER - $SCRIPTAUTHOR $SCRIPTURL"
}

listsaved() {
echo -e "Stats Output saved at:\n ${LOGDIR}/$(ls -rt ${LOGDIR} | grep $DT)"
echo "------------------------------------------------------------------"
ls -lhrt ${LOGDIR}
echo "------------------------------------------------------------------"
}

viewmailqueue() {
byline | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
echo "As at: `date`" | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
mysql $MYSQLADMINOPT -t -e "select mailqueueid, dateline from $MAILQUEUETABLE order by mailqueueid ASC;" $DBNAME | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
listsaved | tee -a $VBMAILIDSTATSFILE
}

countqueue() {
byline | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
echo "As at: `date`" | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
#echo "vBulletin Mail Queue IDs currently at: " | tee -a $VBMAILIDSTATSFILE
MQIDQ=$(mysql $MYSQLADMINOPT -N -t -e "select mailqueueid, dateline from $MAILQUEUETABLE order by mailqueueid ASC;" $DBNAME | grep -Ev '+--' | awk '{print $2}')

declare -a arrayc
arrayc=(${MQIDQ})

echo "First Mail Queue ID: ${arrayc[0]}" | tee -a $VBMAILIDSTATSFILE
echo -n "Last Mail Queue ID: " | tee -a $VBMAILIDSTATSFILE
echo $MQIDQ | awk '{ print $NF }' | tee -a $VBMAILIDSTATSFILE

echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
echo -n "vBulletin Mail Queue row size is: " | tee -a $VBMAILIDSTATSFILE
COUNTQUEUE=${#arrayc[@]}
echo "$COUNTQUEUE" | tee -a $VBMAILIDSTATSFILE

echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
echo "vBulletin email settings are set as follows:"
mysql $MYSQLADMINOPT -t -e "SELECT varname, grouptitle, value, defaultvalue  FROM ${TABLEPREFIX}setting WHERE grouptitle LIKE 'email';"  $DBNAME | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
listsaved | tee -a $VBMAILIDSTATSFILE
}

debug() {
MQIDTEST=$(mysql $MYSQLADMINOPT -N -t -e "select mailqueueid, dateline from $MAILQUEUETABLE order by mailqueueid ASC;" $DBNAME | grep -Ev '+--' | awk '{print $2}') 

declare -a array
array=(${MQIDTEST})

echo "${#array[@]}"
echo $MQIDTEST | awk '{ print $NF }'
}
####################################################

case "$1" in
view)
viewmailqueue
;;
count)
countqueue
;;
debug)
debug
;;
*)
echo "$0 view"
echo "$0 count"
echo "$0 debug"
;;
esac