#!/bin/bash
#
# maintenance_bookings_2022.sh
#
# (c) 2013, Miquel Cabanas, SeRMN, UAB
#
# Started writing:  2013/10/28
# Last updated:     2013/10/28
#
# This script runs the MySql commands needed to automatically create the
# bookings for the spectrometers maintenance tasks, mainly liquid nitrogen
# refills.
#
# To run this script every Sunday 2am append an entry like,
#
# FIXME 0 3 * * Sun /opt/bmbee-114-1/bin/maintenance_nmr_bookings.sh
#
# to crontab. This entry means, at minute 0, hour 2, on every (*) day of
# the month and on every (*) month of the year, if that day happens to be
# a Sunday (day 0), execute the commands in the script file
# 'maintenance_nmr_bookings.sh' (this file).


# VARIABLES ---------------------------------------------------------------

# programs
#
date=`which date`
mail=`which mail`
mysql=`which mysql`

# email recipients and subject

to_address="miquel.cabanas@uab.cat"
# cc_address="miriam.perez@uab.cat,pau.nolis@uab.cat,eva.monteagudo@uab.cat"
cc_address="miquel.cabanas@gmail.com,miquel.cabanas@uab.es"
subject="SeRMN Booking System - Weekly maintenance reservations"

# date related variables. This script runs on Sunday, and bookings are made
# eight weeks in advance (56 days) on Wednesday from 09:00 to 13:00. Hence,
# we need to find those dates so that we can use them later when booking time-slots.
#
# NOTE: these date variables are not currently used for booking commands,
# only for the reporting email messages.

today_date=`$date --date='Today' +'%Y/%m/%d'`	# 2013/10/28
mon_date_mail=`$date --date='8 week 3 day' +'%a %Y/%m/%d'`	# Thu 2013/12/06
mon_weekno=`$date --date='8 week 3 day' +'%V/%Y'`	# 52/2013

# Bumblebee booking system database where everything gets stored

myuser='*****'
mypass='*****'
mydb='*****'


# INSERT BOOKING ENTRIES IN THE DATABAE -----------------------------------------

$mysql --database=$mydb --user=$myuser --password=$mypass << EOF
/* begin transaction */
BEGIN;
/* Insert bookings for weekly maintenance tasks in all vertical magnets. */
/* By default, book Wednesday morning from 09:00 to 13:00, 8 weeks in advance */
/* (i.e. 59 days)   */

/* Avance DPX-250-QNP (250-AUTO) - Wednesday from 09:00 to 13:00 -> 4 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted) VALUES (date_add( date_add( current_date(), interval 59 day), interval 9 hour), '04:00:00', 12, 7, 7, 1, 0, '192.168.2.2', 'Weekly maintenance tasks', 'Weekly maintenance tasks', 0);

/* Avance DPX-250-BACS (250-ROBOT) - Wednesday from 09:00 to 13:00 -> 4 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted) VALUES (date_add( date_add( current_date(), interval 59 day), interval 9 hour), '04:00:00', 15, 7, 7, 1, 0, '192.168.2.2', 'Weekly maintenance tasks', 'Weekly maintenance tasks', 0);

/* Avance DPX-360 - Wednesday from 09:00 to 13:00 -> 4 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted) VALUES (date_add( date_add( current_date(), interval 59 day), interval 9 hour), '04:00:00', 1, 7, 7, 1, 0, '192.168.2.2', 'Weekly maintenance tasks', 'Weekly maintenance tasks', 0);

/* AvanceIII 400SB - Wednesday from 09:00 to 13:00 -> 4 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted) VALUES (date_add( date_add( current_date(), interval 59 day), interval 9 hour), '04:00:00', 6, 7, 7, 1, 0, '192.168.2.2', 'Weekly maintenance tasks', 'Weekly maintenance tasks', 0);

/* AvanceII 400WB - Wednesday from 09:00 to 13:00 -> 4 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted) VALUES (date_add( date_add( current_date(), interval 59 day), interval 9 hour), '04:00:00', 9, 7, 7, 1, 0, '192.168.2.2', 'Weekly maintenance tasks', 'Weekly maintenance tasks', 0);

/* Avance DRX-500 - Wednesday from 09:00 to 13:00 -> 4 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted) VALUES (date_add( date_add( current_date(), interval 59 day), interval 9 hour), '04:00:00', 2, 7, 7, 1, 0, '192.168.2.2', 'Weekly maintenance tasks', 'Weekly maintenance tasks', 0);

/* AvanceII+ 600 - Wednesday from 09:00 to 13:00 -> 4 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted) VALUES (date_add( date_add( current_date(), interval 59 day), interval 9 hour), '04:00:00', 7, 7, 7, 1, 0, '192.168.2.2', 'Weekly maintenance tasks', 'Weekly maintenance tasks', 0);

/* Hypersense DNP - Wednesday from 09:00 to 13:00 -> 4 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted) VALUES (date_add( date_add( current_date(), interval 59 day), interval 9 hour), '04:00:00', 17, 7, 7, 1, 0, '192.168.2.2', 'Weekly maintenance tasks', 'Weekly maintenance tasks', 0);

/* commit transaction */
COMMIT;
EOF


# MAIL THE SYSTEM AND INSTRUMENT ADMINISTRATORS - -----------------------
#
# Report to the system and instrument administrators whether Routine NMR
# bookings have been created.
#
status=$?

if [ $status -ne 0 ]; then
  # mysql command failed. Send an email reporting the failure
  #
  $mail -s "$subject" -c $cc_address $to_address << EOM

  AUTOMATED EMAIL MESSAGE. DO NOT REPLY

  SeRMN - Universitat Autonoma de Barcelona

  Failed to allocate time slots for weekly maintenance tasks
  week $mon_weekno starting: $mon_date_mail.

  Please, report this issue to the system administrator.

EOM

else
  # mysql command suceeded. Send an email reporting that routine
  # bookings have been created
  #
  $mail -s "$subject" -c $cc_address $to_address << EOM

  AUTOMATED EMAIL MESSAGE. DO NOT REPLY

  SeRMN - Universitat Autonoma de Barcelona

  Time slots have been allocated for weekly maintenance tasks
  on all SeRMN spectrometers for week $mon_weekno starting:
  $mon_date_mail.

EOM

fi

exit