`tsmdata`.CREATE DATABASE `tsmdata` /*!40100 DEFAULT CHARACTER SET latin1 */;

DROP TABLE IF EXISTS `tsmdata`.`exchkeepforever`;
CREATE TABLE  `tsmdata`.`exchkeepforever` (
  `NODE_NAME` varchar(64) NOT NULL default '',
  `isKeptForever` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`NODE_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `tsmdata`.`occupancy`;
CREATE TABLE  `tsmdata`.`occupancy` (
  `ServerName` varchar(30) NOT NULL default '',
  `Date` date NOT NULL default '0000-00-00',
  `Time` time NOT NULL default '00:00:00',
  `OccuranceId` int(10) unsigned NOT NULL auto_increment,
  `NODE_NAME` varchar(64) NOT NULL default '',
  `TYPE` varchar(20) NOT NULL default '',
  `FILESPACE_NAME` varchar(64) NOT NULL default '',
  `STGPOOL_NAME` varchar(30) NOT NULL default '',
  `NUM_FILES` int(10) unsigned NOT NULL default '0',
  `PHYSICAL_MB` decimal(10,2) NOT NULL default '0.00',
  `LOGICAL_MB` decimal(10,2) NOT NULL default '0.00',
  `FILESPACE_ID` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`OccuranceId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `tsmdata`.`occupancytrend`;
CREATE TABLE  `tsmdata`.`occupancytrend` (
  `ServerName` varchar(30) NOT NULL default '',
  `NODE_NAME` varchar(64) NOT NULL default '',
  `Current_MB` decimal(15,2) NOT NULL default '0.00',
  `Current_1day_MB` decimal(15,2) NOT NULL default '0.00',
  `Current_2days_MB` decimal(15,2) NOT NULL default '0.00',
  `Current_3days_MB` decimal(15,2) NOT NULL default '0.00',
  `Current_4days_MB` decimal(15,2) NOT NULL default '0.00',
  `Current_5days_MB` decimal(15,2) NOT NULL default '0.00',
  `Current_6days_MB` decimal(15,2) NOT NULL default '0.00',
  `Current_30days_MB` decimal(15,2) NOT NULL default '0.00',
  `Current_90days_MB` decimal(15,2) NOT NULL default '0.00',
  `Current_365days_MB` decimal(15,2) NOT NULL default '0.00',
  `CurrentDate` date NOT NULL default '0000-00-00',
  `occupancytrendID` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`occupancytrendID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `tsmdata`.`odbcconnections`;
CREATE TABLE  `tsmdata`.`odbcconnections` (
  `OdbcConnectionName` varchar(45) NOT NULL default '',
  `Password` varchar(45) NOT NULL default '',
  `Userid` varchar(45) NOT NULL default '',
  `ConnectId` int(10) unsigned NOT NULL auto_increment,
  `ServerName` varchar(45) NOT NULL default '',
  `IpAddr` varchar(90) NOT NULL default '',
  `IpPort` varchar(5) NOT NULL default '',
  `TestCol` varchar(45) NOT NULL default '',
  `isActive` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`ConnectId`,`ServerName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `tsmdata`.`storagepool`;
CREATE TABLE  `tsmdata`.`storagepool` (
  `STGPOOL_NAME` varchar(30) NOT NULL default '',
  `isOffsiteStorage` tinyint(1) NOT NULL default '0',
  `PHYSICAL_MB` decimal(15,2) default '0.00',
  `LOGICAL_MB` decimal(15,2) default '0.00',
  `StoragePoolID` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`StoragePoolID`,`STGPOOL_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER $$

DROP PROCEDURE IF EXISTS `tsmdata`.`UpdateTrendTable`$$
CREATE PROCEDURE  `tsmdata`.`UpdateTrendTable`()
UpdateTrendTable: BEGIN

  DECLARE OccupServer VARCHAR(30);
  DECLARE OccupNode VARCHAR(64);              /* Nodename from Occupancy table         */
  DECLARE TrendNode VARCHAR(64);              /* Nodename from Trend table             */
  DECLARE MegaBytes DECIMAL(15,2);            /* MB summed by nodename from occupancy table */
  DECLARE ProcessDate DATE;                   /* The day that we're processing          */
  DECLARE EndDate     DATE;                   /* Last date to process                   */
  DECLARE StartDate   DATE;                   /* Beginning date to process              */

  DECLARE CurMB     DECIMAL(15,2);            /* current MB colulumn from the trend table */
  DECLARE TrendID   INT;                      /* Id number from trend tbl to quicker lookup on UPDATE statement */
  DECLARE NotFound  INT;                      /* Set to 1 for SQL not found condition */

                                              /* Cursors to determine the date range to process */
  DECLARE OccupDateCursor CURSOR FOR SELECT DATE        FROM occupancy
                                            Group By Date Order By Date desc;
  DECLARE TrendDateCursor CURSOR FOR SELECT CurrentDate FROM occupancytrend
                                            Group by CurrentDate Order by CurrentDate;

                                             /* Define select stmt to get total occupancy by node  */
                                             /*   for a specific date excluding offsite storage */
  DECLARE OccupCursor CURSOR FOR SELECT A.ServerName,
                                        A.Node_Name,
                                        Sum(A.Logical_mb),
                                        C.occupancytrendID
                                   FROM occupancy A
                                       LEFT JOIN Storagepool B
                                          ON A.StgPool_Name = B.StgPool_Name
                                       LEFT JOIN occupancytrend C
                                          ON A.ServerName = C.ServerName and A.Node_Name = C.Node_Name
                                   WHERE A.date = ProcessDate and b.isoffsitestorage = false
                                   GROUP BY A.servername, A.node_name
                                   ORDER BY A.node_name;



  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET NotFound = 1;

  OPEN OccupDateCursor;                                 /* Get resultset for all dates in the Occup table */
  OPEN TrendDateCursor;

  FETCH OccupDateCursor INTO EndDate;                   /* The 1st get will be the ending date */
  If  NotFound Then                                     /* no data in the table?       */
      CLOSE OccupDateCursor;
      CLOSE TrendDateCursor;
      Leave UpdateTrendTable;
  End If;

  FETCH TrendDateCursor INTO StartDate;                  /* Get trend date */
  If  NotFound Then                                      /* nothing in the table?  */
      SET NotFound = 0;                                  /* get the oldest entry in occupancy table for start date */
      While Not NotFound DO                              /* read until end of table        */
          FETCH OccupDateCursor INTO StartDate;          /* into StartDate */
      End While;
  Else
      If  StartDate = EndDate Then                       /* All have been processed already ? */
          CLOSE OccupDateCursor;
          CLOSE TrendDateCursor;
          Leave UpdateTrendTable;
      End If;
      Set StartDate = AddDate(StartDate,1);              /* Calculate the start date          */
  End If;

  START TRANSACTION;

  CLOSE OccupDateCursor;
  CLOSE TrendDateCursor;

  SET ProcessDate = StartDate;
  WHILE ProcessDate <= EndDate Do                      /* Process all the dates               */

      OPEN OccupCursor;                                /* Get resultset from occupancy table   */

      UPDATE occupancytrend SET Current_6days_MB = Current_5days_MB,
                                Current_5days_MB = Current_4days_MB,
                                Current_4days_MB = Current_3days_MB,
                                Current_3days_MB = Current_2days_MB,
                                Current_2days_MB = Current_1day_MB,
                                Current_1day_MB  = Current_MB,
                                Current_MB = 0,
                                CurrentDate = ProcessDate;

      ProcCurrent: LOOP
          Set NotFound = 0;
          FETCH OccupCursor INTO OccupServer, OccupNode, MegaBytes, TrendId;
          IF  NotFound Then
              LEAVE ProcCurrent;
          END IF;


          IF  TrendID IS NULL  Then
              INSERT Into occupancytrend (ServerName,  Node_Name, Current_MB, CurrentDate )
                                  Values (OccupServer, OccupNode, MegaBytes, ProcessDate );
          ELSE
              UPDATE occupancytrend SET   Current_MB       = MegaBytes
                                    where occupancytrendID = TrendId;
          END IF;
      END LOOP;
      CLOSE OccupCursor;

      SET ProcessDate = AddDate( ProcessDate, 1 );
  END WHILE;

  UPDATE occupancytrend SET Current_30days_MB  = 0;
  UPDATE occupancytrend SET Current_90days_MB  = 0;
  UPDATE occupancytrend SET Current_365days_MB = 0;

  /* Update current - 30days column */
  SET ProcessDate = SubDate( EndDate, 30 );
  OPEN OccupCursor;
  SET NotFound = 0;
  30DayUpdate: LOOP
      FETCH OccupCursor INTO OccupServer, OccupNode, MegaBytes, TrendID;
      IF  NotFound Then
          Leave 30DayUpdate;
      END IF;
      UPDATE OccupancyTrend SET Current_30days_MB = MegaBytes
                            WHERE occupancytrendID = TrendID;
  END LOOP;
  CLOSE OccupCursor;

  /* Update current - 39days column */
  SET ProcessDate = SubDate( EndDate, 90 );
  OPEN OccupCursor;
  SET NotFound = 0;

  90DayUpdate: LOOP
      FETCH OccupCursor INTO OccupServer, OccupNode, MegaBytes, TrendID;
      IF  NotFound Then
          Leave 90DayUpdate;
      END IF;
      UPDATE OccupancyTrend SET Current_90days_MB = MegaBytes
                            WHERE occupancytrendID = TrendID;
  END LOOP;
  CLOSE OccupCursor;

  /* Update current - 365ays column */
  SET ProcessDate = SubDate( EndDate, 365 );
  OPEN OccupCursor;
  SET NotFound = 0;

  365DayUpdate: LOOP
      FETCH OccupCursor INTO OccupServer, OccupNode, MegaBytes, TrendID;
      IF  NotFound Then
          Leave 365DayUpdate;
      END IF;
      UPDATE OccupancyTrend SET Current_365days_MB = MegaBytes
                            WHERE occupancytrendID = TrendID;
  END LOOP;
  CLOSE OccupCursor;

  COMMIT;

END $$

DELIMITER ;