#!/bin/bash
#
# This script will read MS Project™ data from a MySQL/MariaDB database and create a script file for graphviz dot.
# Precondition is an installed and populated database.
# The script is not optimized for runtime.
# The script is does not consider cybersecurity aspects and must therefore not be used on a production system.
# The script is for educational and demonstration purposes only.
#
# 2021-Aug-09, Gabriel Rüeck <gabriel@rueeck.de>, <gabriel@caipirinha.spdns.org>
#

# CONSTANTS
readonly NUM_COLSTEPS=7
readonly COLSCHEME="ylorrd${NUM_COLSTEPS}"
readonly MYSQL_DATABASE='projects'
readonly MYSQL_SERVER='localhost'
readonly MYSQL_USER='gabriel'
readonly TERM_LRED='\e[91m'
readonly TERM_LYELLOW='\e[93m'
readonly TERM_LGREEN='\e[92m'
readonly TERM_RESET='\e[0m'
readonly TERM_LINE='\e[4m'
readonly TERM_NOLINE='\e[24m'

# VARIABLES
declare -i color_step color_code proj_uid task_uid task_duration task_work task_slack task_completeness pred_task succ_task

# FUNCTIONS
function sqrt {
  # This function computes the quare root of the given argument.
  # Outputs: sqrt(${1})
  # Errors: 1 if the input is not a positive integer; 0 otherwise
  ! [[ "${1}" =~ ^[+]{0,1}[0-9]+$ ]] && return 1
  echo $(echo "scale=0; sqrt(${1})" | bc)
  return 0
}

function printerr {
  # This function prints an error message, and if available, also the optional argument.
  # Inputs: $1=Error Code  [$2=Argument]
  # Outputs: n/a
  case ${1} in
    1)  echo -e "${TERM_LRED}There are no projects in the database.${TERM_RESET}"
        exit 1;;
    2)  echo -e "${TERM_LRED}Incorrect option entered. Program terminated.${TERM_RESET}"
        exit 1;;
    3)  echo -e "${TERM_LRED}You must specify an output directory.${TERM_RESET}\n"
        exit 1;;
    4)  echo -e "${TERM_LRED}The directory ${TERM_LINE}${2}${TERM_NOLINE} is not accessible from the current location.${TERM_RESET}\n"
        exit 1;;
    5)  echo -e "${TERM_LRED}NUM_COLSTEPS must be set to a value of 3 at least.${TERM_RESET}\n"
        exit 1;;
    *)  echo -e "${TERM_LRED}The function ${TERM_LINE}printerr()${TERM_NOLINE} has reached an incorrect state due to unknown reasons.${TERM_RESET}\n"
        exit 1;;
  esac
}

# PROGRAM CODE
[[ $# -lt 1 ]] && printerr 3
path="${1}"
[[ ${path: -1} != '/' ]] && path="${path}/"
[[ ! -d "${path}" ]] && printerr 4 "${path}"
[[ ${NUM_COLSTEPS} -lt 3 ]] && printerr 5

[[ $(mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT COUNT(*) FROM projects;") -eq 0 ]] && printerr 1
echo -e "${TERM_LYELLOW}Select a project to work on:\n${TERM_RESET}"
mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT proj_uid,proj_name FROM projects ORDER BY proj_uid ASC;" | (while read proj_uid proj_name; do
  echo -e "  ${TERM_LGREEN}${proj_uid})${TERM_RESET}  ${proj_name}"
done)
echo -e "  ${TERM_LGREEN}0)  Exit${TERM_RESET}\n"
read -p'> '
[[ "${REPLY}" == "0" ]] && exit 0
[[ $(mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT COUNT(*) FROM projects WHERE proj_uid=CAST(\"${REPLY}\" AS UNSIGNED);") -eq 0 ]] && printerr 2
proj_uid=$(mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT proj_uid FROM projects WHERE proj_uid=CAST(\"${REPLY}\" AS UNSIGNED);")
proj_name=$(mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT proj_name FROM projects WHERE proj_uid=CAST(\"${REPLY}\" AS UNSIGNED);")

echo -e "${TERM_LYELLOW}Select the visualization that shall be generated:\n${TERM_RESET}"
echo -e "  ${TERM_LGREEN}1)${TERM_RESET}  PERT chart colored according to slack"
echo -e "  ${TERM_LGREEN}2)${TERM_RESET}  PERT chart colored according to duration"
echo -e "  ${TERM_LGREEN}3)${TERM_RESET}  PERT chart colored according to work"
echo -e "  ${TERM_LGREEN}4)${TERM_RESET}  PERT chart colored according to criticality"
echo -e "  ${TERM_LGREEN}0)  Exit${TERM_RESET}\n"
read -p'> ' MODE
case "${MODE}" in
	1)  color_step=$(mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT CAST((SQRT(MAX(task_slack))/${NUM_COLSTEPS}) AS UNSIGNED) FROM tasks WHERE is_summary_task=FALSE AND is_milestone=FALSE AND proj_uid=${proj_uid};")
	    TMPFILE="${path}${proj_name} [slack]";;
	2)  color_step=$(mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT CAST((SQRT(MAX(task_duration))/${NUM_COLSTEPS}) AS UNSIGNED) FROM tasks WHERE is_summary_task=FALSE AND is_milestone=FALSE AND proj_uid=${proj_uid};")
	    TMPFILE="${path}${proj_name} [duration]";;
	3)  color_step=$(mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT CAST((SQRT(MAX(task_work))/${NUM_COLSTEPS}) AS UNSIGNED) FROM tasks WHERE is_summary_task=FALSE AND is_milestone=FALSE AND proj_uid=${proj_uid};")
	    TMPFILE="${path}${proj_name} [work]";;
	4)  TMPFILE="${path}${proj_name} [criticality]";;
	0)  exit 0;;
	*)  printerr 2;;
esac
# If ${color_step}=0, this means that either:
# 1) no task has slack.
# 2) all tasks have zero duration
# 3) all tasks have zero work
# In that case, set color_step=1 so that there is no "division by zero" error. As the numerator of the fraction will be zero, the denominator does not matter.
[[ ${color_step} -eq 0 ]] && color_step=1

cat > "${TMPFILE}.gv" << EOT
digraph COLORED_PERT {
/* This graph has been generated with the script mysql2dot.sh; developed by Gabriel Rüeck <gabriel@rueeck.de> */

/* General Settings */
rankdir="LR";
graph    [dpi=150, nodesep="1.2", ranksep="1.2", splines=polyline, concentrate=true, charset="UTF-8"];
node     [shape=Mrecord, style=filled, fontname="Noto Sans"];
edge     [style=bold, color=black, fontname="Helvetica"];

/* Milestones */
EOT

# Write milestones
echo -e "${TERM_LYELLOW}Creating milestones.${TERM_RESET}"
mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT task_uid,task_name,\`%_complete\` FROM tasks WHERE proj_uid=${proj_uid} AND is_summary_task=FALSE AND is_milestone=TRUE ORDER BY task_uid ASC;" | (IFS=$'\t'; while read task_uid task_name task_completeness; do
  echo "T${task_uid}  [fillcolor=gray, label=\"{UID=${task_uid}|${task_completeness}%}|${task_name}\"];" >> "${TMPFILE}.gv"
done)

# Write complete tasks
echo -e "${TERM_LYELLOW}Creating complete tasks.${TERM_RESET}"
echo -e '\n/* Complete Tasks */' >> "${TMPFILE}.gv"
mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT task_uid,task_name,task_duration,task_work,task_slack FROM tasks WHERE proj_uid=${proj_uid} AND is_summary_task=FALSE AND is_milestone=FALSE AND \`%_complete\`>=100 ORDER BY task_uid ASC;" | (IFS=$'\t'; while read task_uid task_name task_duration task_work task_slack; do
  echo "T${task_uid}  [fillcolor=yellowgreen, label=\"{UID=${task_uid}|100%}|${task_name}|{D:${task_duration}|W:${task_work}|S:${task_slack}}\"];" >> "${TMPFILE}.gv"
done)

# Write incomplete tasks
echo -e "${TERM_LYELLOW}Creating incomplete tasks.${TERM_RESET}"
echo -e '\n/* Incomplete Tasks */' >> "${TMPFILE}.gv"
mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT (2*LN(MAX(task_slack/task_duration))/(${NUM_COLSTEPS}-1)) INTO @ln_step FROM tasks WHERE is_summary_task=FALSE AND is_milestone=FALSE AND task_duration>0 AND proj_uid=${proj_uid}; SELECT task_uid,task_name,task_duration,task_work,task_slack,\`%_complete\`,LEAST(IFNULL(CAST(((${NUM_COLSTEPS}+1)/2-LN(task_slack/task_duration)/@ln_step) AS UNSIGNED),${NUM_COLSTEPS}),${NUM_COLSTEPS}) as criticality FROM tasks WHERE proj_uid=${proj_uid} AND is_summary_task=FALSE AND is_milestone=FALSE AND \`%_complete\`<100 ORDER BY task_uid ASC;" | (IFS=$'\t'; while read task_uid task_name task_duration task_work task_slack task_completeness color_code; do
  case "${MODE}" in
    1)  if [ ${task_slack} -gt 0 ]; then
          color_code=$((${NUM_COLSTEPS}-$(sqrt ${task_slack})/${color_step}))
        else
          color_code=${NUM_COLSTEPS}
        fi
        [[ ${color_code} -eq 0 ]] && color_code=1
        echo "T${task_uid}  [colorscheme=${COLSCHEME}, color=\"/${COLSCHEME}/${color_code}\", label=\"{UID=${task_uid}|${task_completeness}%}|${task_name}|{D:${task_duration}|W:${task_work}|S:${task_slack}}\"];" >> "${TMPFILE}.gv";;
    2)  color_code=$((1+$(sqrt ${task_duration})/${color_step}))
        [[ ${color_code} -gt ${NUM_COLSTEPS} ]] && color_code=${NUM_COLSTEPS}
        echo "T${task_uid}  [colorscheme=${COLSCHEME}, color=\"/${COLSCHEME}/${color_code}\", label=\"{UID=${task_uid}|${task_completeness}%}|${task_name}|{D:${task_duration}|W:${task_work}|S:${task_slack}}\"];" >> "${TMPFILE}.gv";;
    3)  color_code=$((1+$(sqrt ${task_work})/${color_step}))
        [[ ${color_code} -gt ${NUM_COLSTEPS} ]] && color_code=${NUM_COLSTEPS}
        echo "T${task_uid}  [colorscheme=${COLSCHEME}, color=\"/${COLSCHEME}/${color_code}\", label=\"{UID=${task_uid}|${task_completeness}%}|${task_name}|{D:${task_duration}|W:${task_work}|S:${task_slack}}\"];" >> "${TMPFILE}.gv";;
    4)  echo "T${task_uid}  [colorscheme=${COLSCHEME}, color=\"/${COLSCHEME}/${color_code}\", label=\"{UID=${task_uid}|${task_completeness}%}|${task_name}|{D:${task_duration}|W:${task_work}|S:${task_slack}}\"];" >> "${TMPFILE}.gv";;
  esac
done)

echo -e "${TERM_LYELLOW}Creating link table.${TERM_RESET}"
echo -e '\n/* Links */' >> "${TMPFILE}.gv"
mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT predecessor,successor FROM task_links WHERE proj_uid=${proj_uid} ORDER BY predecessor ASC,successor ASC;" | (IFS=$'\t'; while read pred_task succ_task; do
  echo "T${pred_task} -> T${succ_task};" >> "${TMPFILE}.gv"
done)
echo "}" >> "${TMPFILE}.gv"

echo -e "${TERM_LYELLOW}Creating graph.${TERM_RESET}"
dot -Tpng "${TMPFILE}.gv" -o "${TMPFILE}.png"
