##########################################################################################################################################
#       AUTHOR  : MAHENDRA B. PATIL                                                                                                      #
#       DATE    : 03-DEC-2012                                                                                                            #
#       PURPOSE : To get mail server analytics                                                                                           #
##########################################################################################################################################

#Naaptol Mail Server Database
NT_MAIL_MYSQL_IP="192.168.58.9"
NT_MAIL_MYSQL_DB="mail"
NT_MAIL_MYSQL_USER="mail"
NT_MAIL_MYSQL_PASSWORD="mail"

#Cinema TV Mail Server Database
CT_MAIL_MYSQL_IP="172.16.16.5"
CT_MAIL_MYSQL_DB="mail"
CT_MAIL_MYSQL_USER="mail"
CT_MAIL_MYSQL_PASSWORD="mail"

#Mail Archive Mail Server Database
MA_MAIL_MYSQL_IP="127.0.0.1"
MA_MAIL_MYSQL_DB="mail"
MA_MAIL_MYSQL_USER="mail"
MA_MAIL_MYSQL_PASSWORD="mail@n@@pt0l.c0m"


ALERT_EMAIL_TO="naaptoltech@gmail.com;monitoring.alert@naaptol.com"
ALERT_EMAIL_SUBJECT="MAIL SERVER ANALYTICS"
ALERT_EMAIL_FROM="tech@naaptol.com"

#############################################ALL Required Functions defined Here START#####################################
ALERT_ROOT_PATH="/opt/anm/scripts/alert/"

function print_start_header()
{
echo -e "\n\n######################MAIL SERVER ANALYTICS STARTED ON DATE - $(date +%Y-%m-%d-%H-%M-%S)##############################################\n\n"
}

function print_end_header()
{
cleanup

echo -e "\n\n#######################MAIL SERVER ANALYTICS COMPLETED ON DATE - $(date +%Y-%m-%d-%H-%M-%S)##############################################\n\n"
}

#Any clean up activity which must happen at the last should go here
function cleanup()
{
    echo -e "Clean up!!!"
}

function inform_error()
{
  echo -e "${ERROR_MSG}"

  ALERT_EMAIL_MESSAGE="Error while ${ALERT_EMAIL_SUBJECT} ON  $(date +%Y-%m-%d-%H-%M-%S) <BR/> Error : ${ERROR_MSG}<BR>Please check the logs for further analysis."
  ALERT_EMAIL_SUBJECT="Error :: ${ALERT_EMAIL_SUBJECT}"

  inform

  print_end_header

  exit 0
}


function inform()
{
   ALERT_MESSAGE_FILE="${1}"

   ALERT_EMAIl_SUBJECT="$ALERT_EMAIL_SUBJECT ON $(date +%Y-%m-%d-%H-%M-%S)"

   ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE <BR> <i>Note : If any issues or suggetions,"
   ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE then inform us at <a href='mailto:mahendra.patil@naaptol.com'>mahendra.patil@naaptol.com</a></i> "
   ##################################################SEND EMAIL OF STATUS START######################

   if [ ! -d "${ALERT_ROOT_PATH}" -o ! -f ${ALERT_ROOT_PATH}/alert.conf  ]; then
        echo -e "\n\nalert configuration not exist so not able to triggeer mail\n"
   else
        . ${ALERT_ROOT_PATH}/alert.conf
        if [ -z "${ALERT_MESSAGE_FILE}" ]; then
        ${ALERT_ROOT_PATH}/sendEmail -s "${MAIL_SERVER}"  -f "${ALERT_EMAIL_FROM}" -t "${ALERT_EMAIL_TO}" -u "${ALERT_EMAIl_SUBJECT}"  -o  message-content-type=html  -m "${ALERT_EMAIL_MESSAGE}"
        else

          IS_ALERT_MESSAGE_FILE_ATTACHMENT="`ls ${ALERT_MESSAGE_FILE}| grep -i -e "\.xls" -e "\.xlsx" |wc -l`"
          if [ ${IS_ALERT_MESSAGE_FILE_ATTACHMENT} -gt 0 ]; then

             ALERT_EMAIL_MESSAGE="Attachment contains $ALERT_EMAIl_SUBJECT $ALERT_EMAIL_MESSAGE"
             ${ALERT_ROOT_PATH}/sendEmail -s "${MAIL_SERVER}"  -f "${ALERT_EMAIL_FROM}" -t "${ALERT_EMAIL_TO}" -u "${ALERT_EMAIl_SUBJECT}"  -o  message-content-type=html  -m "${ALERT_EMAIL_MESSAGE}" -a ${ALERT_MESSAGE_FILE}
          else
              ${ALERT_ROOT_PATH}/sendEmail -s "${MAIL_SERVER}"  -f "${ALERT_EMAIL_FROM}" -t "${ALERT_EMAIL_TO}" -u "${ALERT_EMAIl_SUBJECT}"  -o  message-content-type=html  -o message-file=${ALERT_MESSAGE_FILE}
          fi

        fi
   fi

   ##################################################SEND EMAIL OF STATUS END######################

}


#############################################ALL Required Functions defined Here END#####################################


#Changing Directory to Current Shell Directory
CURRENT_SHELL_HOME="$(dirname $0)"
cd "${CURRENT_SHELL_HOME}"

print_start_header


SQL=" select "
SQL=" $SQL (select count(*) from mailbox) as mailid , "
SQL=" $SQL (select count(*) from alias where address not in (select username from mailbox) ) as alias , "
SQL=" $SQL (select count(*) from alias where address in (select username from mailbox) ) as forwarder "

echo -e "Executing Query for Naaptol Mail Server Database"
NT_DATA=`mysql -h ${NT_MAIL_MYSQL_IP} -u ${NT_MAIL_MYSQL_USER} -p${NT_MAIL_MYSQL_PASSWORD} -D ${NT_MAIL_MYSQL_DB} -N -e "${SQL}"`
if [ $? -ne 0 ]; then
   ERROR_MSG="Error in Executing Query for Naaptol Mail Server Database"
   inform_error
fi

NT_MAILID=`echo $NT_DATA|awk '{print $1}'`
NT_ALIAS=`echo $NT_DATA|awk '{print $2}'`
NT_FORWARDER=`echo $NT_DATA|awk '{print $3}'`

echo "Naaptol Mail Server Database Detail :: MAILID = $NT_MAILID , ALIAS = $NT_ALIAS , FORWARDER = $NT_FORWARDER"


echo -e "Executing Query for Cinema TV Mail Server Database"
CT_DATA=`mysql -h ${CT_MAIL_MYSQL_IP} -u ${CT_MAIL_MYSQL_USER} -p${CT_MAIL_MYSQL_PASSWORD} -D ${CT_MAIL_MYSQL_DB} -N -e "${SQL}"`
if [ $? -ne 0 ]; then
   ERROR_MSG="Error in Executing Query for Cinema TV Mail Server Database"
   inform_error
fi

CT_MAILID=`echo $CT_DATA|awk '{print $1}'`
CT_ALIAS=`echo $CT_DATA|awk '{print $2}'`
CT_FORWARDER=`echo $CT_DATA|awk '{print $3}'`

echo "Cinema TV Mail Server Database Detail :: MAILID = $CT_MAILID , ALIAS = $CT_ALIAS , FORWARDER = $CT_FORWARDER"


echo -e "Executing Query for Mail Archive Mail Server Database"
MA_DATA=`mysql -h ${MA_MAIL_MYSQL_IP} -u ${MA_MAIL_MYSQL_USER} -p${MA_MAIL_MYSQL_PASSWORD} -D ${MA_MAIL_MYSQL_DB} -N -e "${SQL}"`
if [ $? -ne 0 ]; then
   ERROR_MSG="Error in Executing Query for Mail Archive Mail Server Database"
   inform_error
fi

MA_MAILID=`echo $MA_DATA|awk '{print $1}'`
MA_ALIAS=`echo $MA_DATA|awk '{print $2}'`
MA_FORWARDER=`echo $MA_DATA|awk '{print $3}'`

echo "Mail Archive Mail Server Database Detail :: MAILID = $MA_MAILID , ALIAS = $MA_ALIAS , FORWARDER = $MA_FORWARDER"


ERROR_NOTE=""
if [ ${NT_MAILID} -ne ${NT_FORWARDER} ]; then
   ERROR_NOTE="${ERROR_NOTE} <LI>Naaptol Mail Id & Forwarder count mismatch</LI>"   
fi
if [ ${CT_MAILID} -ne ${CT_FORWARDER} ]; then
   ERROR_NOTE="${ERROR_NOTE} <LI>Cinema TV Mail Id & Forwarder count mismatch</LI>"   
fi
if [ ${MA_MAILID} -ne ${MA_FORWARDER} ]; then
   ERROR_NOTE="${ERROR_NOTE} <LI>Mail Archive Mail Id & Forwarder count mismatch</LI>"   
fi
#if [ $(($NT_MAILID+$CT_MAILID)) -ne ${MA_MAILID} ]; then
#   ERROR_NOTE="${ERROR_NOTE} <LI>Mail Archive Mail Id total count mismatch</LI>"   
#fi
#if [ $(($NT_ALIAS+$CT_ALIAS)) -ne ${MA_ALIAS} ]; then
#   ERROR_NOTE="${ERROR_NOTE} <LI>Mail Archive Alias total count mismatch</LI>"   
#fi
#if [ $(($NT_FORWARDER+$CT_FORWARDER)) -ne ${MA_FORWARDER} ]; then
#   ERROR_NOTE="${ERROR_NOTE} <LI>Mail Archive Forwarder total count mismatch</LI>"   
#fi


#Preparing Mail Message Start

ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE <style>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE table.detail {border-left: 1px solid #000000;border-top: 1px solid #000000;text-align:center}"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE table.detail td {border-right: 1px solid #000000;border-bottom: 1px solid #000000;}"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE </style>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE <h4>Mail Server Analytics</h4>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE <TABLE class='detail' cellspacing='0' cellpadding='0'>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE <TR>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD width="40%">Mail Server Name</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD width="20%">Mail Id</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD width="20%">Forwrder</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD width="20%">Alias</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE </TR>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE <TR>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>Naaptol</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>$NT_MAILID</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>$NT_FORWARDER</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>$NT_ALIAS</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE </TR>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE <TR>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>Cinema TV</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>$CT_MAILID</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>$CT_FORWARDER</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>$MA_ALIAS</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE </TR>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE <TR>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>Mail Archieve</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>$MA_MAILID</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>$CT_ALIAS</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE 	<TD>$MA_FORWARDER</TD>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE </TR>"
ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE </TABLE>"

if [ ! -z "${ERROR_NOTE}" ]; then
   ALERT_EMAIL_MESSAGE="$ALERT_EMAIL_MESSAGE <BR/><BR/><FONT COLOR='RED'><B>Following Errors found in Mail Server Analytics</B></FONT>$ERROR_NOTE<BR/><BR/>"   
fi

#Preparing Mail Message End

inform

print_end_header