#!/bin/bash
#
# Author:
#
# This script will check the free vlans in a rack using the 
# http://wiki.abiquo.com/display/ABI20/VLAN+Allocation+Algorithm
#
#

MYSQLCOMMAND="mysql -u root kinton -e"
for rack in $($MYSQLCOMMAND 'select idRack from rack' | grep -v ^$ | grep -v ^idRack); do
  nameRack="$($MYSQLCOMMAND "select name from rack where idRack = $rack;" | grep -v ^$ | tail -n 1)"
  NUMVLANS=$($MYSQLCOMMAND "select vlan_id_max-vlan_id_min from rack;" | grep -v ^$ | tail -n 1)
  VPVE=$($MYSQLCOMMAND "select vlan_per_vdc_expected from rack where idRack = $rack" | grep -v ^$ | tail -n 1)
  nrsq=$($MYSQLCOMMAND "select nrsq from rack where idRack = $rack" | grep -v ^$ | tail -n 1)
  exclvlans=0
  numofvdc=$($MYSQLCOMMAND "select count(*) from virtualdatacenter vdc, vlan_network_assignment vna where vdc.default_vlan_network_id = vna.vlan_network_id and vna.idRack = $rack;" | grep -v ^$ | tail -n 1)
  echo "Rack: $nameRack"
  echo "  id: $rack"
  echo "  num VLANS: $NUMVLANS"
  echo "  vlan_per_vdc_expected: $VPVE"
  echo "  nrsq: $nrsq"
  echo "  number of vdc assigned: $numofvdc"
  # Calculate excluded vlans if there are any
  VLANSAVOID=0
  if [ $($MYSQLCOMMAND "select vlans_id_avoided from rack where idRack = $rack;" | grep -v ^$ | wc -l) -gt 1 ]; then
    VLANSAVOIDVAL=$($MYSQLCOMMAND "select vlans_id_avoided from rack where idRack = $rack;" | grep -v ^$ | tail -n 1 | tr "," "\n")
    for vlan_range in $VLANSAVOIDVAL; do
      if [ $(echo $vlan_range | wc -c) -eq 2 ]; then
        VLANSAVOID=$(expr $VLANSAVOID + 1)
      else 
        A=$(echo $vlan_range | cut -d'-' -f1)
        B=$(echo $vlan_range | cut -d'-' -f2)
        C=$(expr $B - $A + 1)
        VLANSAVOID=$(expr $VLANSAVOID + $C)
      fi
    done
  fi  
echo "  num excluded vlans: $VLANSAVOID"
  # Calculate free vlans
  A=$(expr $NUMVLANS \* $nrsq)
  B=$(expr $A / 100)
  C=$(expr $VPVE \* $numofvdc)
  AVLANS=$(expr $NUMVLANS - $B - $C - $VLANSAVOID)
  echo "  free vlans: $AVLANS"
done
