explicar el programet per fer les reserves automàtiques per la cua de rutina i les condicions de funcionament de l'espectròmetre

Aquest es el programet que crea les reserves per la cua de rutina. S'executa tots els diumenges a les 2 de la matinada, i crea les reserves amb dues setmanes d'antelació per tal que es puguin modificar si s'escau. El programet també envia un correu electrònic indicant si ha pogut fer les reserves.

routine_nmr_bookings.sh

#!/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

El programet envia el següent correu electrònic si ha aconseguit fer les reserves,

-------- Original Message --------
Subject:  SeRMN Booking System - NMR Routine reservations
Date:     Fri, 18 Oct 2013 11:12:02 +0200
From:     root <root@sermn.uab.cat>
To:       Miquel.Cabanas@uab.cat

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 42/2013 starting:
Sat 2013/11/02.

i aquest altre missatge en cas contrari,

-------- Original Message --------
Subject:  SeRMN Booking System - NMR Routine reservations
Date:     Fri, 18 Oct 2013 11:09:27 +0200
From:     root <root@sermn.uab.cat>
To:       Miquel.Cabanas@uab.cat

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 42/2013 starting:
Sat 2013/11/02.

Please, report this issue to the system administrator.

Aquesta és l'entrada creada al programa crontab per executar automàticament el programet que crear les reserves (crontab -u root -l):

# Bumblebee 1.1.4 - Automated bookings fro Routine nmr analysis
# run every Sunday at 02:00.
#
0   2   *   *   Sun   /opt/bmbee-114-1/bin/routine_nmr_bookings.sh

Per motius de seguretat (el programet conté el nom d'usuari i clau de pas de la base de dades de reserves) la carpeta /opt/bmbee-114-1/bin/ està protegida i té l'accés restringit,

• permisos carpeta bin: 700
• permisos fitxer routine_nmr_bookings.sh: 700
• propietari (user:group): root:root

A més, per impedir el seu accés a través del servidor web, la carpeta bin conté un fitxer .htaccess amb el següent contingut:

order deny,allow
deny from all

Aquest és el codi que permet actualitzar els permisos dels usuaris i donar accés al 250-ROBOT als usuaris amb accés al 250-AUTOSERVEI que no tenen el compte suspès ni cancel·lat.

INSERT INTO permissions (userid, instrid, isadmin, 
announce, unbook, haspriority, points, pointsrecharge)
SELECT permissions.userid, 15, 0, 0, 0, 0, NULL, NULL
FROM permissions, users
WHERE permissions.userid = users.id
AND permissions.instrid = 12
AND users.deleted = 0