#!/bin/bash
#
# create_past_maintenance_bookings.sh
#
# (c) 2013, Miquel Cabanas, SeRMN, UAB
#
# Started writing:  2013/10/29
# Last updated:     2013/10/29
#
# This script runs the MySql commands needed to create the maintenance
# bookings needed to replace the past maintenance periods that were set
# in the spectrometer schedules and have been replaced now by real bookings.
#

# programs

date=`which date`
mail=`which mail`
mysql=`which mysql`

# Bumblebee booking system database where everything gets stored

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

# First Wednesday on a particular year

firstwed=`$date +%F -d "2013-01-02"`
echo "First wednesday (included): $firstwed"

# Last Wednesday on a particular year

lastwed=`$date +%F -d "2013-10-23 + 1 week"`
echo "Last wednesday (excluded): $lastwed"

# insert booking entries in the database

wed=$firstwed

while [[ "$wed" < "$lastwed" ]] ; do

echo "Creating booking for date: $wed"

$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  */

/* 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('$wed', 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('$wed', 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('$wed', 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('$wed', 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('$wed', 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('$wed', 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('$wed', 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('$wed', 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

wed=`$date +%F -d "$wed + 1 week"`

done

exit