#!/bin/bash
#
# This script will parse an MS Project™ XML file partially and write the extracted data into a MySQL/MariaDB database.
# Precondition is an installed and configured database.
# The script is not optimized for runtime, nor does it parse XML data in a formally correct way.
# 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.
#
# V2; 2022-Jan-05, Gabriel Rüeck <gabriel@rueeck.de>, <gabriel@caipirinha.spdns.org>
#

# CONSTANTS
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 state task_uid task_duration task_work free_slack total_slack finish_slack task_rem_duration task_rem_work pred_uid link_lag task_is_summary task_is_milestone task_is_critical task_completeness
declare -i calendar_uid resource_uid
declare -i assignment_uid assignment_work assignment_data_work

# FUNCTIONS
function iso2msp {
  # This function converts an ISO 8601 duration into a traditional M$ Project duration (whereby 1 unit equals 6 s).
  # The function does NOT check for a valid syntax of the ISO 8601 duration.
  # The function ONLY caters for an evaluation of hours, minutes, seconds, not for other values like "days", "weeks", "months".
  # Inputs: $1 = ISO 8601 duration
  # Outputs: MS Project duration value (1 unit = 6 seconds)
  # Errors: 1 for format error; 0 for no error
  declare -i hours minutes seconds msp_value
  if [ "${1:0:2}" == 'PT' ]; then
    hours=$(sed -n 's/^PT\([[:digit:]]\+\)H.*$/\1/p' <<< "${1}")
    minutes=$(sed -n 's/^PT.*H\([[:digit:]]\+\)M.*$/\1/p' <<< "${1}")
    seconds=$(sed -n 's/^PT.*M\([[:digit:]]\+\)S$/\1/p' <<< "${1}")
    msp_value=$((hours*600+minutes*10+seconds/6))
    echo ${msp_value}
    return 0
  else
    return 1
  fi
}

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}The project \"${2}\" exists already.${TERM_RESET}"
        exit 1;;
    2)  echo -e "${TERM_LRED}The function ${TERM_LINE}iso2msp()${TERM_NOLINE} aborted with an error when processing the argument \"${2}\".${TERM_RESET}"
        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
if [ $# -lt 1 ]; then
   echo -e "${TERM_LRED}You must specify an XML source file.${TERM_RESET}\n"
   exit 1
fi

state=0
echo -e "${TERM_LYELLOW}Parsing the MS Project™ XML file.${TERM_RESET}"
while read LINE; do
  # Remove possible Carriage Returns (Windows files...)
  LINE=$(echo -e "${LINE}" | tr -d '\r' | tr -d '\t')
  # Extract XML Tag and XML Attribute
  xml_tag=$(sed -n 's/^<\([^>]*\)>.*$/\1/p' <<< "${LINE}")
  xml_attribute=$(sed -n 's/<.*>\(.*\)<\/.*>$/\1/p' <<< "${LINE}")
  # State Machine
  case "${state}:${xml_tag}" in
  	'0:Project xmlns="http://schemas.microsoft.com/project"')  state=1;;
  	 0:*)                    ;;
     1:Title)                proj_name="${xml_attribute}"
                             [[ $(mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT COUNT(proj_uid) FROM projects WHERE proj_name LIKE \"${proj_name}\";") -gt 0 ]] && printerr 1 "${proj_name}"
                             mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "INSERT INTO projects (proj_name) VALUES (\"${proj_name}\");"
                             proj_uid=$(mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "SELECT proj_uid FROM projects WHERE proj_name LIKE \"${proj_name}\";")
                             echo -e "${TERM_LGREEN}Project name \"${proj_name}\" captured with UID ${proj_uid}.${TERM_RESET}";;
     1:Tasks)                echo -e "${TERM_LYELLOW}Started parsing tasks.${TERM_RESET}"
                             state=2;;
     1:Resources)            echo -e "${TERM_LYELLOW}Started parsing resources.${TERM_RESET}"
                             state=5;;
     1:Assignments)          echo -e "${TERM_LYELLOW}Started parsing assignments.${TERM_RESET}"
                             state=8;;
     1:*)                    ;;
     1:/Project)             state=0;;
     2:Task)                 state=3;;
     2:/Tasks)               echo -e "${TERM_LYELLOW}Finished parsing tasks.${TERM_RESET}"
                             state=1;;
     2:*)                    ;;
     3:Critical)             task_is_critical="${xml_attribute}";;
     3:Duration)             task_duration=$(iso2msp "${xml_attribute}")
                             [[ $? -ne 0 ]] && printerr 2 "${xml_attribute}";;
     3:Finish)               task_finish="${xml_attribute}";;
     3:FinishSlack)          finish_slack="${xml_attribute}";;
     3:FreeSlack)            free_slack="${xml_attribute}";;
     3:Milestone)            task_is_milestone="${xml_attribute}";;
     3:Name)                 task_name=$(sed 's/\&amp;/\&/g' <<< "${xml_attribute}");;
     3:PercentComplete)      task_completeness="${xml_attribute}";;
     3:PredecessorLink)      state=4;;
     3:RemainingDuration)    task_rem_duration=$(iso2msp "${xml_attribute}")
                             [[ $? -ne 0 ]] && printerr 2 "${xml_attribute}";;
     3:RemainingWork)        task_rem_work=$(iso2msp "${xml_attribute}")
                             [[ $? -ne 0 ]] && printerr 2 "${xml_attribute}";;
     3:Start)                task_start="${xml_attribute}";;
     3:Summary)              task_is_summary="${xml_attribute}";;
     3:TotalSlack)           total_slack="${xml_attribute}";;
     3:UID)                  task_uid="${xml_attribute}";;
     3:Work)                 task_work=$(iso2msp "${xml_attribute}")
                             [[ $? -ne 0 ]] && printerr 2 "${xml_attribute}";;
     3:/Task)                state=2
                             # Discard the task with UID=0 and all summary tasks
                             if [ ${task_uid} -ne 0 ]; then
                               mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "INSERT INTO tasks VALUES (${proj_uid},${task_uid},\"${task_name}\",${task_is_summary},${task_is_milestone},${task_is_critical},\"${task_start}\",\"${task_finish}\",${task_duration},${task_work},${free_slack},${total_slack},${finish_slack},${task_rem_duration},${task_rem_work},${task_completeness});"
                               echo -e "${TERM_LGREEN}Task \"${task_name}\" captured with UID ${task_uid}.${TERM_RESET}"
                             fi;;
     3:*)                    ;;
     4:LinkLag)              link_lag="${xml_attribute}";;
     4:PredecessorUID)       pred_uid="${xml_attribute}";;
     4:/PredecessorLink)     state=3
                             mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "INSERT INTO task_links VALUES (${proj_uid},${pred_uid},${task_uid},${link_lag});";;
     4:*)                    ;;
     5:Resource)             state=6;;
     5:/Resources)           echo -e "${TERM_LYELLOW}Finished parsing resources.${TERM_RESET}"
                             state=1;;
     5:*)                    ;;
     6:CalendarUID)          calendar_uid="${xml_attribute}";;
     6:MaxUnits)             resource_units="${xml_attribute}";;
     6:Name)                 resource_name=$(sed 's/\&amp;/\&/g' <<< "${xml_attribute}");;
     6:UID)                  resource_uid="${xml_attribute}";;
     6:/Resource)            state=5
                             # Discard the resource with UID=0
                             if [ ${resource_uid} -ne 0 ]; then
                               mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "INSERT INTO resources VALUES (${proj_uid},${resource_uid},${calendar_uid},${resource_units},\"${resource_name}\");"
                               echo -e "${TERM_LGREEN}Resource \"${resource_name}\" captured with UID ${resource_uid}.${TERM_RESET}"
                             fi;;
     6:*)                    ;;
     8:Assignment)           state=9;;
     8:/Assignments)         echo -e "${TERM_LYELLOW}Finished parsing assignments.${TERM_RESET}"
                             state=1;;
     8:*)                    ;;
     9:Finish)               assignment_finish="${xml_attribute}";;
     9:ResourceUID)          resource_uid="${xml_attribute}";;
     9:Start)                assignment_start="${xml_attribute}";;
     9:TaskUID)              task_uid="${xml_attribute}";;
     9:TimephasedData)       state=10;;
     9:UID)                  assignment_uid="${xml_attribute}";;
     9:Work)                 assignment_work=$(iso2msp "${xml_attribute}")
                             [[ $? -ne 0 ]] && printerr 2 "${xml_attribute}";;
     9:/Assignment)          state=8
                             if [ ${resource_uid} -gt 0 ]; then
                               mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "INSERT INTO assignments VALUES (${proj_uid},${task_uid},${resource_uid},${assignment_uid},\"${assignment_start}\",\"${assignment_finish}\",${assignment_work});"
                               echo -e "${TERM_LGREEN}Assignment captured with UID ${assignment_uid}.${TERM_RESET}"
                             fi;;
     9:*)                    ;;
    10:Finish)               assignment_data_finish="${xml_attribute}";;
    10:Start)                assignment_data_start="${xml_attribute}";;
    10:Value)                assignment_data_work=$(iso2msp "${xml_attribute}");;
    10:/TimephasedData)      [[ ${resource_uid} -gt 0 ]] && mysql -B -N -r -D "${MYSQL_DATABASE}" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e "INSERT INTO assignment_data VALUES (${proj_uid},${assignment_uid},\"${assignment_data_start}\",\"${assignment_data_finish}\",${assignment_data_work});"
                             state=9;;
    10:*)                    ;;
     *:*)                    echo -e "${TERM_LRED}The program has reached an incorrect state due to unknown reasons.${TERM_RESET}\n"
                             exit 1;;
  esac
done < <(cat "${1}")