#!/bin/bash
#
# routine_nmr_bookings.sh
#
# (c) 2013, Miquel Cabanas, SeRMN, UAB
#
# Started writing:  2013/10/14
# Last updated:     2013/10/18
#
# This script runs the MySql commands needed to automatically create the
# bookings for the routine nmr analysis in the Bruker DPX-250-BACS shared
# by self-service and programmed samples.
#
# To run this script every Sunday 2am append an entry like,
#
# FIXME 0 2 * * Sun /opt/bmbee-114-1/bin/routine_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
# 'routine_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 - NMR Routine reservations"

# date related variables. This script runs on Sunday, and bookings are made
# two weeks in advance (15 days) from Monday to Friday. 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/18
mon_date_mail=`$date --date='2 week 1 day' +'%a %Y/%m/%d'`	# Sat 2013/11/02
mon_weekno=`$date --date='2 week 1 day' +'%V/%Y'`	# 44/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 routine nmr analysis */

/* Monday from 16:00 to 10:00 next day -> 18 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted)
VALUES (date_add( date_add( current_date(), interval 15 day), interval 16 hour), '18:00:00', 15, 361, 361, 68, 0, '192.168.2.2', 'Routine nmr analysis', 'Routine nmr analysis', 0);

/* Tuesday from 16:00 to 09:00 next day -> 17 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted)
VALUES (date_add( date_add( current_date(), interval 16 day), interval 16 hour), '17:00:00', 15, 361, 361, 68, 0, '192.168.2.2', 'Routine nmr analysis', 'Routine nmr analysis', 0);

/* Wednesday from 16:00 to 10:00 next day -> 18 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted)
VALUES (date_add( date_add( current_date(), interval 17 day), interval 16 hour), '18:00:00', 15, 361, 361, 68, 0, '192.168.2.2', 'Routine nmr analysis', 'Routine nmr analysis', 0);

/* Thursday from 16:00 to 10:00 next day -> 18 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted)
VALUES (date_add( date_add( current_date(), interval 18 day), interval 16 hour), '18:00:00', 15, 361, 361, 68, 0, '192.168.2.2', 'Routine nmr analysis', 'Routine nmr analysis', 0);

/* Friday from 16:00 to 10:00 next day -> 18 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted)
VALUES (date_add( date_add( current_date(), interval 19 day), interval 16 hour), '18:00:00', 15, 361, 361, 68, 0, '192.168.2.2', 'Routine nmr analysis', 'Routine nmr analysis', 0);

/* Saturday from 10:00 to 00:00 next day -> 14 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted)
VALUES (date_add( date_add( current_date(), interval 20 day), interval 10 hour), '14:00:00', 15, 361, 361, 68, 0, '192.168.2.2', 'Routine nmr analysis', 'Routine nmr analysis', 0);

/* Sunday from 00:00 to 00:00 next day -> 24 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted)
VALUES (date_add( date_add( current_date(), interval 21 day), interval 0 hour), '24:00:00', 15, 361, 361, 68, 0, '192.168.2.2', 'Routine nmr analysis', 'Routine nmr analysis', 0);

/* Monday from 00:00 to 10:00 -> 10 hours */
INSERT INTO bookings(bookwhen, duration, instrument, bookedby, userid, projectid, discount, ip, comments, log, deleted)
VALUES (date_add( date_add( current_date(), interval 22 day), interval 0 hour), '10:00:00', 15, 361, 361, 68, 0, '192.168.2.2', 'Routine nmr analysis', 'Routine nmr analysis', 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 to the Routine NMR at the
  Bruker DPX-250 spectrometer on 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 at the Bruker DPX-250 spectrometer have been
  allocated for Routine NMR on week $mon_weekno starting:
  $mon_date_mail.

EOM

fi

exit