/*     
       # Copyright (C) 2012-2013 GNU remotecontrol authors.
       #
       # This program is free software: you can redistribute it and/or modify
       # it under the terms of the GNU Affero General Public License as
       # published by the Free Software Foundation, either version 3 of the
       # License, or (at your option) any later version.
       #
       # This program is distributed in the hope that it will be useful,
       # but WITHOUT ANY WARRANTY; without even the implied warranty of
       # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
       # GNU Affero General Public License for more details.
       #
       # You should have received a copy of the GNU Affero General Public License
       # along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

-- 
-- Set character set the client will use to send SQL statements to the server
--

--
-- Definition for database iptstat
--
DROP DATABASE IF EXISTS iptstat;
CREATE DATABASE IF NOT EXISTS iptstat
	CHARACTER SET latin1
	COLLATE latin1_swedish_ci;

-- 
-- Set default database
--
USE iptstat;



DELIMITER $$
CREATE PROCEDURE `sp_Error_Insert`(pErrorMessage varchar(800))
BEGIN
		insert into errorlog(Message)
		values(pErrorMessage);
    END$$

CREATE PROCEDURE `sp_Languages_Load_All`()
BEGIN
  select Code, Description from iptstat.language order by SortOrder;
END$$

CREATE PROCEDURE `sp_OID_FieldName_Load`(pOID varchar(20))
BEGIN
		select FieldName from oidfieldnamelookup where OID = pOID;
    END$$

CREATE PROCEDURE `sp_OID_Load_All`()
BEGIN
		select OID, FieldName from oidfieldnamelookup
		order by OID;
    END$$

CREATE PROCEDURE `sp_Resources_Load_All`()
BEGIN
  select ResourceID, Code, Value, DropDownItemValue, StyleClass, SortOrder, Page, LFT, RGT from iptstat.resources order by LFT;
END$$

CREATE PROCEDURE `sp_Resources_Load_By_Page`(IN pPage VARCHAR(255))
BEGIN
  select ResourceID, Code, Value, DropDownItemValue, StyleClass, SortOrder, LFT, RGT from iptstat.resources
  where Page = pPage
  order by LFT;
END$$

CREATE PROCEDURE `sp_Resources_Load_By_Parent_Code`(IN pLangCode VARCHAR(3), IN pParentCode VARCHAR(125), IN pUseSortOrder BIT, IN pDepth TINYINT, IN pOnlyAtLevel BIT)
BEGIN
  create temporary table tbl
  (
    ResourceID int,
    Code varchar(125),
    Value varchar(255),
    DropDownItemValue varchar(50),
    StyleClass varchar(50),
    SortOrder tinyint,
    LFT smallint,
    RGT smallint,
    Depth tinyint
  );

  if pDepth is not null then    
      if pOnlyAtLevel = 1 then
        insert into tbl
        (
          ResourceID,
          Code,
          Value,
          DropDownItemValue,
          StyleClass,
          SortOrder,
          LFT,
          RGT,
          Depth
        )
        select
          r1.ResourceID,
          r1.Code,
          r1.Value,
          r1.DropDownItemValue,
          r1.StyleClass,
          r1.SortOrder,
          r1.LFT,
          r1.RGT,
          (count(r2.Code) - (sub.Depth + 1)) as "Depth"
        from iptstat.resources r1,
          iptstat.resources r2,
          iptstat.resources r3,
          (
            select r4.Code, (count(r5.Code) - 1) as "Depth"
            from iptstat.resources r4,
              iptstat.resources r5
            where r4.LFT between r5.LFT and r5.RGT
              and r4.Code = pParentCode
            group by r4.Code
            order by r4.LFT
          ) as sub
        where
          r1.LFT between r2.LFT and r2.RGT
          and r1.LFT between r3.LFT and r3.RGT
          and r3.Code = sub.Code
        group by r1.Code
        having Depth = pDepth
        order by r1.LFT;
      else
        insert into tbl
        (
          ResourceID,
          Code,
          Value,
          DropDownItemValue,
          StyleClass,
          SortOrder,
          LFT,
          RGT,
          Depth
        )
        select
          r1.ResourceID,
          r1.Code,
          r1.Value,
          r1.DropDownItemValue,
          r1.StyleClass,
          r1.SortOrder,
          r1.LFT,
          r1.RGT,
          (count(r2.Code) - (sub.Depth + 1)) as "Depth"
        from iptstat.resources r1,
          iptstat.resources r2,
          iptstat.resources r3,
          (
            select r4.Code, (count(r5.Code) - 1) as "Depth"
            from iptstat.resources r4,
              iptstat.resources r5
            where r4.LFT between r5.LFT and r5.RGT
              and r4.Code = pParentCode
            group by r4.Code
            order by r4.LFT
          ) as sub
        where
          r1.LFT between r2.LFT and r2.RGT
          and r1.LFT between r3.LFT and r3.RGT
          and r3.Code = sub.Code
        group by r1.Code
        having Depth <= pDepth
        order by r1.LFT;
      end if;
  else
    insert into tbl
    (
      ResourceID,
      Code,
      Value,
      DropDownItemValue,
      StyleClass,
      SortOrder,
      LFT,
      RGT,
      Depth
    )
    select
      r1.ResourceID,
      r1.Code,
      r1.Value,
      r1.DropDownItemValue,
      r1.StyleClass,
      r1.SortOrder,
      r1.LFT,
      r1.RGT,
      (count(r2.Code) - (sub.Depth + 1)) as "Depth"
    from iptstat.resources r1,
      iptstat.resources r2,
      iptstat.resources r3,
      (
        select r4.Code, (count(r5.Code) - 1) as "Depth"
        from iptstat.resources r4,
          iptstat.resources r5
        where r4.LFT between r5.LFT and r5.RGT
          and r4.Code = pParentCode
        group by r4.Code
        order by r4.LFT
      ) as sub
    where
      r1.LFT between r2.LFT and r2.RGT
      and r1.LFT between r3.LFT and r3.RGT
      and r3.Code = sub.Code
    group by r1.Code
    order by r1.LFT;
  end if;

  if pLangCode != 'en' then
    set @updstmt = concat('update tbl as tbl1, iptstat.resources as tbl2 set tbl1.Value = tbl2.Value_',pLangCode,' where tbl1.ResourceID = tbl2.ResourceID');
    prepare stmt from @updstmt;
    execute stmt;
  end if;

  if pUseSortOrder is not null and pUseSortOrder = 1 then
    select
      ResourceID,
      Code,
      Value,
      DropDownItemValue,
      StyleClass,
      SortOrder,
      LFT,
      RGT
    from tbl
    order by SortOrder;
  else
    select
      ResourceID,
      Code,
      Value,
      DropDownItemValue,
      StyleClass,
      SortOrder,
      LFT,
      RGT
    from tbl;
  end if;

  drop table tbl;
END$$

CREATE PROCEDURE `sp_Resources_Translate`(IN pLangCode VARCHAR(3), IN pResourceCode VARCHAR(125), IN pTranslatedValue VARCHAR(255))
BEGIN
  declare newColumn varchar(50);
  declare newColumnExists tinyint;

  if pLangCode = 'en' then
    set newColumn = 'Value';
  else
    set newColumn = concat('Value_',pLangCode);
  end if;

  set newColumnExists = ifnull((select 1 from information_schema.`COLUMNS`
                                  where COLUMN_NAME = newColumn
                                    and TABLE_SCHEMA = 'iptstat'
                                    and TABLE_NAME = 'resources'), 0);

  if pLangCode != 'en' then
    if newColumnExists = 0 then
      set @updstmt = concat('alter table iptstat.resources add column ',newColumn,' varchar(255) not null after Value');
      prepare stmt from @updstmt;
      execute stmt;
    end if;
  end if;
  
  set @updstmt = concat('update iptstat.resources set ',newColumn,' = \'',pTranslatedValue,'\' where Code = \'',pResourceCode,'\'');  
  prepare stmt from @updstmt;
  execute stmt;
  
END$$

CREATE PROCEDURE `sp_Resource_Load_By_Code`(IN pResourceCode VARCHAR(125), IN pLangCode VARCHAR(3))
BEGIN
  
  if pLangCode = 'en' then
    select ResourceID, Code, Value, DropDownItemValue, StyleClass, SortOrder, Page, LFT, RGT from iptstat.resources where Code = pResourceCode;
  else
    set @updstmt = concat('select ResourceID, Code, Value_', pLangCode, ' as Value, DropDownItemValue, StyleClass, SortOrder, Page, LFT, RGT from iptstat.resources where Code = \'', pResourceCode, '\'');
    prepare stmt from @updstmt;
    execute stmt;
  end if;

END$$

CREATE PROCEDURE `sp_Thermostat_Auth_Info_Update`(pThermostatId mediumint, pAuthString varchar(40))
BEGIN
		update thermostat set AuthString = pAuthString where ThermostatId = pThermostatId;
    END$$

CREATE PROCEDURE `sp_Thermostat_Delete`(pThermostatId mediumint)
BEGIN
		update thermostat set Deleted = 1 where ThermostatId = pThermostatId;
    END$$

CREATE PROCEDURE `sp_Thermostat_Insert_Update`(IN pThermostatId MEDIUMINT, IN pName VARCHAR(40), IN pDescription VARCHAR(400), IN pHost VARCHAR(255), IN pPort MEDIUMINT, IN pTimeZoneOffsetID TINYINT, IN pUserId SMALLINT)
BEGIN
		if pThermostatId is null then			
			insert into thermostat(`Name`, Description, `Host`, `Port`, TimeZoneOffsetID, UserId)
			values(pName, pDescription, pHost, pPort, pTimeZoneOffsetID, pUserId);
			
			select last_insert_id();
		Else
			update thermostat set
				`Name` = pName,
				Description = pDescription,
				`Host` = pHost,
				`Port` = pPort,
        TimeZoneOffsetID = pTimeZoneOffsetID
			where ThermostatId = pThermostatId;
		end if;
    END$$

CREATE PROCEDURE `sp_Thermostat_Load_All`(IN pUserId smallint, IN pThermostatSort VARCHAR(20))
BEGIN
	CASE pThermostatSort
        WHEN 'Name_Desc' THEN
            select ThermostatId, Name, Description, Host, Port, AuthString, TimeZoneOffsetID from thermostat where 
            UserId = pUserId and Deleted = 0 ORDER BY Name Desc;
        WHEN 'Name_Asc' THEN
            select ThermostatId, Name, Description, Host, Port, AuthString, TimeZoneOffsetID from thermostat where 
            UserId = pUserId and Deleted = 0 ORDER BY Name Asc;
        WHEN 'Description_Desc' THEN
            select ThermostatId, Name, Description, Host, Port, AuthString, TimeZoneOffsetID from thermostat where 
            UserId = pUserId and Deleted = 0 ORDER BY Description Desc;
        WHEN 'Description_Asc' THEN
            select ThermostatId, Name, Description, Host, Port, AuthString, TimeZoneOffsetID from thermostat where 
            UserId = pUserId and Deleted = 0 ORDER BY Description Asc;
        WHEN 'Host_Desc' THEN
            select ThermostatId, Name, Description, Host, Port, AuthString, TimeZoneOffsetID from thermostat where 
            UserId = pUserId and Deleted = 0 ORDER BY Host Desc;
        WHEN 'Host_Asc' THEN
            select ThermostatId, Name, Description, Host, Port, AuthString, TimeZoneOffsetID from thermostat where 
            UserId = pUserId and Deleted = 0 ORDER BY Host Asc;
        WHEN 'Port_Desc' THEN
            select ThermostatId, Name, Description, Host, Port, AuthString, TimeZoneOffsetID from thermostat where 
            UserId = pUserId and Deleted = 0 ORDER BY Port Desc;
        WHEN 'Port_Asc' THEN
            select ThermostatId, Name, Description, Host, Port, AuthString, TimeZoneOffsetID from thermostat where 
            UserId = pUserId and Deleted = 0 ORDER BY Port Asc;
        WHEN 'TimeZone_Desc' THEN
            select ThermostatId, Name, Description, Host, Port, AuthString, TimeZoneOffsetID from thermostat where 
            UserId = pUserId and Deleted = 0 ORDER BY TimeZoneOffsetID Desc;
        WHEN 'TimeZone_Asc' THEN
            select ThermostatId, Name, Description, Host, Port, AuthString, TimeZoneOffsetID from thermostat where 
            UserId = pUserId and Deleted = 0 ORDER BY TimeZoneOffsetID Asc;
        END CASE;
	
	
END$$

CREATE PROCEDURE `sp_Thermostat_Load_Details`(IN pThermostatId mediumint)
BEGIN
	select t.Name, t.Description, t.Host, t.Port, t.AuthString, t.TimeZoneOffsetID
	from thermostat t
	where t.ThermostatId = pThermostatId;
    END$$

CREATE PROCEDURE `sp_TimeZoneOffset_Load_All`()
BEGIN
  select TimeZoneOffsetID, Description, `Offset` from iptstat.timezoneoffset order by `Offset`;
END$$

CREATE PROCEDURE `sp_TimeZoneOffset_Load_Details`(IN pTimeZoneOffsetID TINYINT)
BEGIN
  select Description, `Offset` from iptstat.timezoneoffset where TimeZoneOffsetID = pTimeZoneOffsetID;
END$$

CREATE PROCEDURE `sp_Transaction_Log_Insert`(pThermstatId mediumint, pOID varchar(20), pOldValue varchar(20),
		pNewValue varchar(20), pUserId smallInt)
BEGIN
		INSERT INTO transactionlog(ThermostatId, OID, OldValue, NewValue, TransactionUserId)
		VALUES(pThermstatId, pOID, pOldValue, pNewValue, pUserId);
    END$$

CREATE PROCEDURE `sp_Transaction_Log_Load_All`(IN pUserId smallint)
BEGIN
		select tl.TransactionId, t.Name, ofl.FieldName, 
			case ofl.FieldType
				when 'Enum' then (select oevl1.Name from oidenumvaluelookup oevl1 where oevl1.OID = tl.OID and oevl1.Value = tl.OldValue)
				when 'Temp' then (tl.OldValue / 10)
				when 'Date' then Date_format(from_unixtime(tl.OldValue), '%a, %b %e, %Y %h:%i %p')
				when 'Period' then time_format(sec_to_time(tl.OldValue * 60), '%h:%i %p')
			end as OldValue,
			case ofl.FieldType
				when 'Enum' then (select oevl2.Name from oidenumvaluelookup oevl2 WHERE oevl2.OID = tl.OID and oevl2.Value = tl.NewValue)
				WHEN 'Temp' THEN (tl.NewValue / 10)
				when 'Date' then DATE_FORMAT(from_unixtime(tl.NewValue), '%a, %b %e, %Y %h:%i %p')
				WHEN 'Period' THEN TIME_FORMAT(SEC_TO_TIME(tl.NewValue * 60), '%h:%i %p')
			end as NewValue,
			DATE_FORMAT(tl.TransactionTimeStamp, '%a, %b %e, %Y %h:%i %p') as TransactionTimeStamp
		from transactionlog tl
			join thermostat t using(ThermostatId)
			join oidfieldnamelookup ofl using(OID)
		where tl.TransactionUserId = pUserId
    order by 6 desc;
    END$$

CREATE PROCEDURE `sp_Transaction_Log_Load_Details`(pTransactionId int)
BEGIN
		SELECT ThermostatId, OID, OldValue, NewValue, TransactionTimeStamp
		FROM transactionlog			
		WHERE TransactionId = pTransactionId;
    END$$

CREATE PROCEDURE `sp_User_Delete`(pUserId mediumint)
BEGIN
		delete from `user` where UserId = pUserId;
    END$$

CREATE PROCEDURE `sp_User_Insert_Update`(IN pUserId MEDIUMINT, IN pUserName VARCHAR(20), IN pMaxNumThermostats MEDIUMINT, IN pLangCode VARCHAR(3))
BEGIN
		if pUserId is null then
			insert into `user`(UserName, MaxNumThermostats, LangCode) values(pUserName, pMaxNumThermostats, pLangCode);
			select last_insert_id();
		else
		
			update `user` set
			UserName = pUserName,
			MaxNumThermostats = pMaxNumThermostats,
			LangCode = pLangCode
			where UserId = pUserId;
		end if;
    END$$

CREATE PROCEDURE sp_User_Update_Thermostat_Sort_Field(IN pUserId MEDIUMINT, IN pThermostatSort VARCHAR(20))
BEGIN
        UPDATE user set ThermostatSort = pThermostatSort WHERE userId = pUserId;
END $$

CREATE PROCEDURE `sp_User_Load_All`()
BEGIN
	select UserId, UserName, MaxNumThermostats, LangCode from `user` 
	order by UserName;
    END$$

CREATE PROCEDURE `sp_User_Load_Details_By_UserID`(IN pUserId mediumint)
BEGIN	
		SELECT UserName, IFNULL(MaxNumThermostats, 0) `MaxNumThermostats`, LangCode, ThermostatSort
		FROM `user`
		WHERE UserId = pUserId;
    END$$

CREATE PROCEDURE `sp_User_Load_Details_By_UserName`(IN pUserName varchar(20))
BEGIN	
		SELECT UserId, IFNULL(MaxNumThermostats, 0) `MaxNumThermostats`, LangCode, ThermostatSort
		FROM `user`
		WHERE UserName = pUserName;
    END$$

DELIMITER ;

CREATE TABLE IF NOT EXISTS `errorlog` (
  `ErrorID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Message` varchar(800) NOT NULL,
  `TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ErrorID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `language` (
  `Code` varchar(3) NOT NULL,
  `Description` varchar(255) DEFAULT NULL,
  `SortOrder` tinyint(4) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=3276;

INSERT INTO `language` (`Code`, `Description`, `SortOrder`) VALUES
('en', 'English', 1),
('es', 'Espa\0ñol', 2),
('fr', 'fran\0çais', 3),
('it', 'Italiano', 4),
('de', 'Deutsch', 5);

CREATE TABLE IF NOT EXISTS `oidenumvaluelookup` (
  `EnumId` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Value` tinyint(4) NOT NULL,
  `OID` varchar(20) NOT NULL,
  PRIMARY KEY (`EnumId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=159 AUTO_INCREMENT=104 ;

INSERT INTO `oidenumvaluelookup` (`EnumId`, `Name`, `Value`, `OID`) VALUES
(1, 'Off', 1, '4.1.1'),
(2, 'Heat', 2, '4.1.1'),
(3, 'Cool', 3, '4.1.1'),
(4, 'Auto', 4, '4.1.1'),
(5, 'Auto', 1, '4.1.3'),
(6, 'On', 2, '4.1.3'),
(7, 'Schedule', 3, '4.1.3'),
(8, 'Normal', 1, '4.1.9'),
(9, 'Hold', 2, '4.1.9'),
(10, 'Override', 3, '4.1.9'),
(11, 'Disabled', 1, '4.3.6.1'),
(12, 'Enabled', 2, '4.3.6.1'),
(13, 'Disabled', 1, '4.3.6.2'),
(14, 'Enabled', 2, '4.3.6.2'),
(15, 'Disabled', 1, '4.3.6.3'),
(16, 'Enabled', 2, '4.3.6.3'),
(17, 'Disabled', 1, '4.3.8.1'),
(18, 'Enabled', 2, '4.3.8.1'),
(19, 'Disabled', 1, '4.3.8.2'),
(20, 'Enabled', 2, '4.3.8.2'),
(21, 'Disabled', 1, '4.3.8.3'),
(22, 'Enabled', 2, '4.3.8.3'),
(23, 'Disable', 0, '4.4.1.6.1.1'),
(24, '15 Minutes', 15, '4.4.1.6.1.1'),
(25, '30 Minutes', 30, '4.4.1.6.1.1'),
(26, '45 Minutes', 45, '4.4.1.6.1.1'),
(27, 'On', 60, '4.4.1.6.1.1'),
(28, 'Disable', 0, '4.4.1.6.1.2'),
(29, '15 Minutes', 15, '4.4.1.6.1.2'),
(30, '30 Minutes', 30, '4.4.1.6.1.2'),
(31, '45 Minutes', 45, '4.4.1.6.1.2'),
(32, 'On', 60, '4.4.1.6.1.2'),
(33, 'Disable', 0, '4.4.1.6.1.3'),
(34, '15 Minutes', 15, '4.4.1.6.1.3'),
(35, '30 Minutes', 30, '4.4.1.6.1.3'),
(36, '45 Minutes', 45, '4.4.1.6.1.3'),
(37, 'On', 60, '4.4.1.6.1.3'),
(38, 'Disable', 0, '4.4.1.6.1.4'),
(39, '15 Minutes', 15, '4.4.1.6.1.4'),
(40, '30 Minutes', 30, '4.4.1.6.1.4'),
(41, '45 Minutes', 45, '4.4.1.6.1.4'),
(42, 'On', 60, '4.4.1.6.1.4'),
(43, 'Disable', 0, '4.4.1.6.2.1'),
(44, '15 Minutes', 15, '4.4.1.6.2.1'),
(45, '30 Minutes', 30, '4.4.1.6.2.1'),
(46, '45 Minutes', 45, '4.4.1.6.2.1'),
(47, 'On', 60, '4.4.1.6.2.1'),
(48, 'Disable', 0, '4.4.1.6.2.2'),
(49, '15 Minutes', 15, '4.4.1.6.2.2'),
(50, '30 Minutes', 30, '4.4.1.6.2.2'),
(51, '45 Minutes', 45, '4.4.1.6.2.2'),
(52, 'On', 60, '4.4.1.6.2.2'),
(53, 'Disable', 0, '4.4.1.6.2.3'),
(54, '15 Minutes', 15, '4.4.1.6.2.3'),
(55, '30 Minutes', 30, '4.4.1.6.2.3'),
(56, '45 Minutes', 45, '4.4.1.6.2.3'),
(57, 'On', 60, '4.4.1.6.2.3'),
(58, 'Disable', 0, '4.4.1.6.2.4'),
(59, '15 Minutes', 15, '4.4.1.6.2.4'),
(60, '30 Minutes', 30, '4.4.1.6.2.4'),
(61, '45 Minutes', 45, '4.4.1.6.2.4'),
(62, 'On', 60, '4.4.1.6.2.4'),
(63, 'Disable', 0, '4.4.1.6.3.1'),
(64, '15 Minutes', 15, '4.4.1.6.3.1'),
(65, '30 Minutes', 30, '4.4.1.6.3.1'),
(66, '45 Minutes', 45, '4.4.1.6.3.1'),
(67, 'On', 60, '4.4.1.6.3.1'),
(68, 'Disable', 0, '4.4.1.6.3.2'),
(69, '15 Minutes', 15, '4.4.1.6.3.2'),
(70, '30 Minutes', 30, '4.4.1.6.3.2'),
(71, '45 Minutes', 45, '4.4.1.6.3.2'),
(72, 'On', 60, '4.4.1.6.3.2'),
(73, 'Disable', 0, '4.4.1.6.3.3'),
(74, '15 Minutes', 15, '4.4.1.6.3.3'),
(75, '30 Minutes', 30, '4.4.1.6.3.3'),
(76, '45 Minutes', 45, '4.4.1.6.3.3'),
(77, 'On', 60, '4.4.1.6.3.3'),
(78, 'Disable', 0, '4.4.1.6.3.4'),
(79, '15 Minutes', 15, '4.4.1.6.3.4'),
(80, '30 Minutes', 30, '4.4.1.6.3.4'),
(81, '45 Minutes', 45, '4.4.1.6.3.4'),
(82, 'On', 60, '4.4.1.6.3.4'),
(83, 'Occupied', 1, '4.4.3.2.1'),
(84, 'Unoccupied', 2, '4.4.3.2.1'),
(85, 'Other', 3, '4.4.3.2.1'),
(86, 'Occupied', 1, '4.4.3.2.2'),
(87, 'Unoccupied', 2, '4.4.3.2.2'),
(88, 'Other', 3, '4.4.3.2.2'),
(89, 'Occupied', 1, '4.4.3.2.3'),
(90, 'Unoccupied', 2, '4.4.3.2.3'),
(91, 'Other', 3, '4.4.3.2.3'),
(92, 'Occupied', 1, '4.4.3.2.4'),
(93, 'Unoccupied', 2, '4.4.3.2.4'),
(94, 'Other', 3, '4.4.3.2.4'),
(95, 'Occupied', 1, '4.4.3.2.5'),
(96, 'Unoccupied', 2, '4.4.3.2.5'),
(97, 'Other', 3, '4.4.3.2.5'),
(98, 'Occupied', 1, '4.4.3.2.6'),
(99, 'Unoccupied', 2, '4.4.3.2.6'),
(100, 'Other', 3, '4.4.3.2.6'),
(101, 'Occupied', 1, '4.4.3.2.7'),
(102, 'Unoccupied', 2, '4.4.3.2.7'),
(103, 'Other', 3, '4.4.3.2.7');

CREATE TABLE IF NOT EXISTS `oidfieldnamelookup` (
  `OID` varchar(20) NOT NULL,
  `FieldName` varchar(255) NOT NULL,
  `FieldType` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`OID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=215;

INSERT INTO `oidfieldnamelookup` (`OID`, `FieldName`, `FieldType`) VALUES
('1.10.9', 'Site Name', 'Free'),
('1.13.2.1', 'Low Temp Alarm', 'ReadOnly'),
('1.13.2.2', 'High Temp Alarm', 'ReadOnly'),
('1.13.2.3', 'Filter Reminder', 'ReadOnly'),
('1.13.2.4', 'High Humidity Alarm', 'ReadOnly'),
('1.2', 'Device Name', 'Free'),
('2.5.1', 'Date/Time', 'Date'),
('2.7.1', 'Model Number', 'Free'),
('4.1.1', 'HVAC Mode', 'Enum'),
('4.1.3', 'Fan Mode', 'Enum'),
('4.1.5', 'Setback Heat', 'Temp'),
('4.1.6', 'Setback Cool', 'Temp'),
('4.1.9', 'Setback Status', 'Enum'),
('4.3.4.2', 'Remote Sensor #1 Correction', 'Temp'),
('4.3.4.3', 'Remote Sensor #2 Correction', 'Temp'),
('4.3.6.1', 'Local Sensor State', 'Enum'),
('4.3.6.2', 'Remote Sensor #1 State', 'Enum'),
('4.3.6.3', 'Remote Sensor #2 State', 'Enum'),
('4.3.8.1', 'Local Sensor Averaging', 'Enum'),
('4.3.8.2', 'Remote Sensor #1 Averaging', 'Enum'),
('4.3.8.3', 'Remote Sensor #2 Averaging', 'Enum'),
('4.4.1.3.1.1', 'Setback Sched Occ Morn Start Time', 'Period'),
('4.4.1.3.1.2', 'Setback Sched Occ Day Start Time', 'Period'),
('4.4.1.3.1.3', 'Setback Sched Occ Eve Start Time', 'Period'),
('4.4.1.3.1.4', 'Setback Sched Occ Night Start Time', 'Period'),
('4.4.1.3.2.1', 'Setback Sched Unocc Morn Start Time', 'Period'),
('4.4.1.3.2.2', 'Setback Sched Unocc Day Start Time', 'Period'),
('4.4.1.3.2.3', 'Setback Sched Unocc Eve Start Time', 'Period'),
('4.4.1.3.2.4', 'Setback Sched Unocc Night Start Time', 'Period'),
('4.4.1.3.3.1', 'Setback Sched Other Morn Start Time', 'Period'),
('4.4.1.3.3.2', 'Setback Sched Other Day Start Time', 'Period'),
('4.4.1.3.3.3', 'Setback Sched Other Eve Start Time', 'Period'),
('4.4.1.3.3.4', 'Setback Sched Other Night Start Time', 'Period'),
('4.4.1.4.1.1', 'Setback Sched Occ Morn Heat', 'Temp'),
('4.4.1.4.1.2', 'Setback Sched Occ Day Heat', 'Temp'),
('4.4.1.4.1.3', 'Setback Sched Occ Eve Heat', 'Temp'),
('4.4.1.4.1.4', 'Setback Sched Occ Night Heat', 'Temp'),
('4.4.1.4.2.1', 'Setback Sched Unocc Morn Heat', 'Temp'),
('4.4.1.4.2.2', 'Setback Sched Unocc Day Heat', 'Temp'),
('4.4.1.4.2.3', 'Setback Sched Unocc Eve Heat', 'Temp'),
('4.4.1.4.2.4', 'Setback Sched Unocc Night Heat', 'Temp'),
('4.4.1.4.3.1', 'Setback Sched Other Morn Heat', 'Temp'),
('4.4.1.4.3.2', 'Setback Sched Other Day Heat', 'Temp'),
('4.4.1.4.3.3', 'Setback Sched Other Eve Heat', 'Temp'),
('4.4.1.4.3.4', 'Setback Sched Other Night Heat', 'Temp'),
('4.4.1.5.1.1', 'Setback Sched Occ Morn Cool', 'Temp'),
('4.4.1.5.1.2', 'Setback Sched Occ Day Cool', 'Temp'),
('4.4.1.5.1.3', 'Setback Sched Occ Eve Cool', 'Temp'),
('4.4.1.5.1.4', 'Setback Sched Occ Night Cool', 'Temp'),
('4.4.1.5.2.1', 'Setback Sched Unocc Morn Cool', 'Temp'),
('4.4.1.5.2.2', 'Setback Sched Unocc Day Cool', 'Temp'),
('4.4.1.5.2.3', 'Setback Sched Unocc Eve Cool', 'Temp'),
('4.4.1.5.2.4', 'Setback Sched Unocc Night Cool', 'Temp'),
('4.4.1.5.3.1', 'Setback Sched Other Morn Cool', 'Temp'),
('4.4.1.5.3.2', 'Setback Sched Other Day Cool', 'Temp'),
('4.4.1.5.3.3', 'Setback Sched Other Eve Cool', 'Temp'),
('4.4.1.5.3.4', 'Setback Sched Other Night Cool', 'Temp'),
('4.4.1.6.1.1', 'Setback Sched Occ Morn Fan', 'Enum'),
('4.4.1.6.1.2', 'Setback Sched Occ Day Fan', 'Enum'),
('4.4.1.6.1.3', 'Setback Sched Occ Eve Fan', 'Enum'),
('4.4.1.6.1.4', 'Setback Sched Occ Night Fan', 'Enum'),
('4.4.1.6.2.1', 'Setback Sched Unocc Morn Fan', 'Enum'),
('4.4.1.6.2.2', 'Setback Sched Unocc Day Fan', 'Enum'),
('4.4.1.6.2.3', 'Setback Sched Unocc Eve Fan', 'Enum'),
('4.4.1.6.2.4', 'Setback Sched Unocc Night Fan', 'Enum'),
('4.4.1.6.3.1', 'Setback Sched Other Morn Fan', 'Enum'),
('4.4.1.6.3.2', 'Setback Sched Other Day Fan', 'Enum'),
('4.4.1.6.3.3', 'Setback Sched Other Eve Fan', 'Enum'),
('4.4.1.6.3.4', 'Setback Sched Other Night Fan', 'Enum'),
('4.4.3.2.1', 'Setback Sched Default Class Sun', 'Enum'),
('4.4.3.2.2', 'Setback Sched Default Class Mon', 'Enum'),
('4.4.3.2.3', 'Setback Sched Default Class Tue', 'Enum'),
('4.4.3.2.4', 'Setback Sched Default Class Wed', 'Enum'),
('4.4.3.2.5', 'Setback Sched Default Class Thu', 'Enum'),
('4.4.3.2.6', 'Setback Sched Default Class Fri', 'Enum'),
('4.4.3.2.7', 'Setback Sched Default Class Sat', 'Enum');

CREATE TABLE IF NOT EXISTS `resources` (
  `ResourceID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Code` varchar(125) NOT NULL,
  `Value` varchar(255) NOT NULL,
  `Value_de` varchar(255) DEFAULT NULL,
  `Value_it` varchar(255) DEFAULT NULL,
  `Value_fr` varchar(255) DEFAULT NULL,
  `Value_es` varchar(255) DEFAULT NULL,
  `DropDownItemValue` varchar(50) DEFAULT NULL,
  `StyleClass` varchar(50) DEFAULT NULL,
  `SortOrder` tinyint(4) unsigned DEFAULT NULL,
  `Page` varchar(255) DEFAULT NULL,
  `LFT` smallint(6) unsigned NOT NULL,
  `RGT` smallint(6) unsigned NOT NULL,
  PRIMARY KEY (`ResourceID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=387 AUTO_INCREMENT=128 ;

INSERT INTO `resources` (`ResourceID`, `Code`, `Value`, `Value_de`, `Value_it`, `Value_fr`, `Value_es`, `DropDownItemValue`, `StyleClass`, `SortOrder`, `Page`, `LFT`, `RGT`) VALUES
(1, 'GRCHM', 'Home', 'Startseite', 'Casa', 'Accueil', 'Home', NULL, NULL, NULL, 'Home', 6, 199),
(2, 'GRCHM$LBL1', 'Display Language:', 'Display-Sprache:', 'Lingua di visualizzazione:', 'Langue d''affichage :', 'Idioma de la pantalla:', NULL, NULL, NULL, 'Home', 7, 8),
(3, 'GRCHM$LBL2', 'Highlighted fields are required.', 'Hervorgehobene Felder müssen ausgefüllt werden.', 'Campi evidenziati sono obbligatori.', 'Les champs en surbrillance sont obligatoires.', 'Los campos resaltados son obligatorios.', NULL, 'requirednote', NULL, 'Home', 9, 10),
(4, 'GRCHM$LBL3', 'Welcome back, {0}', 'Willkommen Back, {0}', 'Benvenuto back,: {0}', 'Bienvenue back, {0}', 'Bienvenido back, {0}', NULL, NULL, NULL, 'Home', 11, 12),
(5, 'GRCHM$LBL4', 'You are allowed a maximum of <b>{0}</b> thermostats.', 'Sie dürfen ein Maximum von <b>{0}</b> Thermostate.', 'Sono ammessi un massimo di <b>{0}</b> termostati.', 'Vous avez droit à un maximum de <b>{0}</b> thermostats.', 'Se permite un máximo de <b>{0}</b> termostatos.', NULL, NULL, NULL, 'Home', 13, 14),
(6, 'GRCHM$HLK1', 'Transaction Log', 'Transaktionsprotokoll', 'Log delle transazioni', 'Journal des transactions', 'Registro de transacciones', NULL, NULL, NULL, 'Home', 35, 36),
(7, 'GRCHM$HLK2', 'Add', 'Hinzufügen', 'Aggiungi', 'Ajouter', 'Añadir', NULL, 'add', NULL, 'Home', 37, 38),
(8, 'GRCHM$CLM1', 'Device Name', 'Gerätename', 'Nome del dispositivo', 'Nom du périphérique', 'Nombre del dispositivo', NULL, NULL, NULL, 'Home', 51, 52),
(9, 'GRCHM$CLM2', 'Site Name', 'Site-Name', 'Nome del sito', 'Nom du site', 'Nombre del sitio', NULL, NULL, NULL, 'Home', 53, 54),
(10, 'GRCHM$CLM3', 'Model #', 'Modell #', 'Modello #', 'Modèle #', 'Modelo #', NULL, NULL, NULL, 'Home', 55, 56),
(11, 'GRCHM$CLM4', 'Custom Name', 'Benutzerdefinierten Namen', 'Nome personalizzato', 'Nom personnalisé', 'Nombre personalizado', NULL, NULL, NULL, 'Home', 57, 58),
(12, 'GRCHM$CLM5', 'Description', 'Beschreibung', 'Descrizione', 'Description', 'Descripción', NULL, NULL, NULL, 'Home', 59, 60),
(13, 'GRCHM$CLM6', 'Domain Name/IP', 'Domain-Namen/IP', 'IP o nome dominio', 'IP/nom de domaine', 'Nombre de dominio/IP', NULL, NULL, NULL, 'Home', 61, 62),
(14, 'GRCHM$CLM7', 'Port', 'Port', 'Porta', 'Port', 'Puerto', NULL, NULL, NULL, 'Home', 63, 64),
(15, 'GRCHM$CLM8', 'Login', 'Login', 'Login', 'Ouverture de session', 'Inicio de sesión', NULL, NULL, NULL, 'Home', 65, 78),
(16, 'GRCHM$HLK3', 'Edit', 'Bearbeiten', 'Modifica', 'Edit', 'Editar', NULL, 'edit', NULL, 'Home', 39, 40),
(17, 'GRCHM$HLK4', 'Update', 'Update', 'Aggiornamento', 'Mise à jour', 'Actualización', NULL, 'update', NULL, 'Home', 41, 42),
(18, 'GRCHM$HLK5', 'Cancel', 'Abbrechen', 'Annulla', 'Annuler', 'Cancelar', NULL, 'cancel', NULL, 'Home', 43, 44),
(21, 'GRCHM$HLK6', 'View Settings', 'Ansichtseinstellungen', 'Impostazioni di visualizzazione', 'Paramètres d''affichage', 'Configuración de vista', NULL, 'viewsettings', NULL, 'Home', 45, 46),
(22, 'GRCHM$HLK7', 'Currently Viewing', 'Z.z. ansehend', 'Attualmente la visualizzazione', 'Currently Viewing', 'Viendo', NULL, 'currentlyviewing', NULL, 'Home', 47, 48),
(23, 'GRCHM$HLK8', 'Delete', 'Löschen', 'Eliminare', 'Supprimer', 'Eliminar', NULL, 'delete', NULL, 'Home', 49, 50),
(24, 'GRCHM$LBL5', 'Done', 'Getan', 'Fatto', 'Fait', 'Hecho', NULL, 'success', NULL, 'Home', 15, 16),
(25, 'GRCHM$LBL6', 'Error', 'Fehler', 'Errore', 'Erreur', 'Error', NULL, 'failure', NULL, 'Home', 17, 18),
(26, 'GRCHM$LBL7', 'NoTrans', 'NoTrans', 'NoTrans', 'NoTrans', 'NoTrans', NULL, 'notrans', NULL, 'Home', 19, 20),
(27, 'GRCHM$CLM8$HLK1', 'Change', 'Änderung', 'Cambiamento', 'Changement', 'Cambio', NULL, 'change', NULL, 'Home', 66, 67),
(28, 'GRCHM$CLM8$HLK2', 'Set', 'Satz', 'Set', 'Jeu', 'Conjunto', NULL, 'set', NULL, 'Home', 68, 69),
(29, 'GRCHM$TTP1', 'Select All/Deselect All', 'Alle auswählen / Auswahl zurücksetzen', 'Seleziona tutto / Deseleziona tutto', 'Sélectionner tout / désélectionner tout', 'Seleccionar todo / deseleccionar todo', NULL, NULL, NULL, 'Home', 83, 84),
(30, 'GRCHM$MSG1', 'Some required fields have been left blank. Custom Name, Domain Name/IP, and Port are required.', 'Einige erforderliche Felder leer gelassen worden sind. Benutzerdefinierte Namen, Domain-Namen/IP und Port sind erforderlich.', 'Alcuni tenuti campi sono state lasciate vuote. Nome personalizzato, IP o nome dominio e porto sono necessari.', 'Certains requis zones ont été laissées en blanc. Nom, nom du domaine/IP et le Port personnalisé sont nécessaires.', 'Algunas requieren campos se han dejado en blanco. Nombre, nombre de dominio/IP y puerto personalizado se requieren.', NULL, NULL, NULL, 'Home', 23, 24),
(31, 'GRCHM$MSG2', 'You must enter a password.', 'Sie müssen ein Kennwort eingeben.', 'È necessario immettere una password.', 'Vous devez entrer un mot de passe.', 'Debe introducir una contraseña.', NULL, NULL, NULL, 'Home', 25, 26),
(32, 'GRCHM$MSG3', 'At least one thermostat must be selected before submitting.', 'Vor dem Absenden muss mindestens ein Thermostat ausgewählt werden.', 'Prima della presentazione, è necessario selezionare almeno un termostato.', 'Au moins un thermostat doit être sélectionnée avant de la soumettre.', 'Al menos un termostato debe seleccionarse antes de enviar.', NULL, NULL, NULL, 'Home', 27, 28),
(33, 'GRCHM$TTP2', 'No transaction occurred. All submitted values are the same as current values.', 'Keine Transaktion aufgetreten ist. Alle eingereichten, dass Werte, die die aktuellen Werte identisch sind.', 'Nessuna transazione avvenuta. Tutti dei valori sono gli stessi valori correnti.', 'Aucune transaction n''a eu lieu. Tout a fait valoir les valeurs sont les mêmes que les valeurs actuelles.', 'Se ha producido ninguna transacción. Todos presentaron valores son los mismos que los valores actuales.', NULL, NULL, NULL, 'Home', 85, 86),
(34, 'GRCHM$TTP3', 'The following error occurred during the transaction: {0}', 'Der folgende Fehler trat während der Transaction: {0}', 'Il seguente errore si è verificato durante il transaction: {0}', 'L''erreur suivante s''est produite lors de la transaction: : {0}', 'Ocurrió el siguiente error durante la transaction: {0}', NULL, NULL, NULL, 'Home', 87, 88),
(35, 'GRCHM$TTP4', 'Transaction completed successfully.', 'Transaktion erfolgreich abgeschlossen.', 'Transazione completata con successo.', 'Opération terminée avec succès.', 'Transacción completada con éxito.', NULL, NULL, NULL, 'Home', 89, 90),
(36, 'GRCHM$MSG4', 'Setback Heat and Setback Cool are within 2 degrees of each other. This is not allowed when HVAC Mode is Auto.', 'Rückschlag-Hitze und Rückschlag Cool sind innerhalb von 2 Grad voneinander. Dies ist nicht zulässig, wenn der HVAC-Modus Auto ist.', 'Battuta d''arresto termico e battuta d''arresto Cool sono entro i 2 gradi uno da altro. Questo non è consentito quando la modalità di HVAC è automatico.', 'Chaleur revers et revers Cool se trouvent 2 degrés de l''autre. Ce n''est pas autorisé lorsque le Mode de CVC est automatique.', 'Revés calor y frío de retroceso están dentro de los 2 grados entre sí. Esto no está permitido cuando el modo de climatización es Auto.', NULL, NULL, NULL, 'Home', 29, 30),
(37, 'GRCHM$MSG5', 'Setback Scheduling Period Times cannot overlap. Please check your Time selections in the Setback Scheduling section.', 'Rückschlag Scheduling Zeitraum Mal dürfen sich nicht überschneiden. Bitte überprüfen Sie Ihre Zeit-Auswahl im Abschnitt Rückschlag zu planen.', 'Battuta d''arresto di programmazione tempi di periodo non possono sovrapporsi. Si prega di controllare le selezioni di tempo nella sezione pianificazione della battuta d''arresto.', 'Revers calendrier période de temps ne peuvent pas se chevaucher. S''il vous plaît vérifier vos sélections de temps dans la section planification des revers.', 'Revés programación período veces no pueden solaparse. Por favor revise sus selecciones de tiempo en la sección de programación de revés.', NULL, NULL, NULL, 'Home', 31, 32),
(38, 'GRCHM$HDR1', 'HVAC Settings', 'HVAC-Einstellungen', 'Impostazioni di HVAC', 'Paramètres de CVC', 'Configuración de HVAC', NULL, 'sectionheader', NULL, 'Home', 91, 122),
(39, 'GRCHM$HDR1$LBL1', 'HVAC Mode', 'HVAC-Modus', 'Modalità HVAC', 'Mode de chauffage ', 'Modo HVAC', NULL, 'fieldlabel', NULL, 'Home', 92, 101),
(40, 'GRCHM$HDR1$LBL1$DDI1', 'Off', 'Ausschalten', 'Fuori', 'Hors', 'Apagado', '1', NULL, 1, 'Home', 93, 94),
(41, 'GRCHM$HDR1$LBL1$DDI2', 'Heat', 'Wärme', 'Calore', 'Chaleur', 'Calor', '2', 'heat', 2, 'Home', 95, 96),
(42, 'GRCHM$HDR1$LBL1$DDI3', 'Cool', 'Cool', 'Cool', 'Cool', 'Cool', '3', 'cool', 3, 'Home', 97, 98),
(43, 'GRCHM$HDR1$LBL1$DDI4', 'Auto', 'Auto', 'Auto', 'Voiture', 'Coche', '4', NULL, 4, 'Home', 99, 100),
(44, 'GRCHM$HDR1$LBL2', 'Fan Mode', 'Lüfter-Modus', 'Modalità fan', 'Mode ventilateur ', 'Modo de ventilación', NULL, 'fieldlabel', NULL, 'Home', 102, 109),
(45, 'GRCHM$HDR1$LBL2$DDI1', 'Auto', 'Auto', 'Auto', 'Voiture', 'Coche', '1', NULL, 1, 'Home', 103, 104),
(46, 'GRCHM$HDR1$LBL2$DDI2', 'On', 'Auf', 'Su', 'Sur', 'En', '2', NULL, 2, 'Home', 105, 106),
(47, 'GRCHM$HDR1$LBL2$DDI3', 'Schedule', 'Zeitplan', 'Calendario', 'Annexe', 'Horario', '3', NULL, 3, 'Home', 107, 108),
(48, 'GRCHM$HDR1$LBL3', 'Setback Status', 'Rückschlag-Status', 'Stato di transizione', 'Statut de revers ', 'Estado de retroceso', NULL, 'fieldlabel', NULL, 'Home', 110, 117),
(49, 'GRCHM$HDR1$LBL3$DDI1', 'Normal', 'Normal', 'Normale', 'Normal', 'Normal', '1', NULL, 1, 'Home', 111, 112),
(50, 'GRCHM$HDR1$LBL3$DDI2', 'Hold', 'Halten', 'Tenere', 'Cale', 'Mantenga', '2', NULL, 2, 'Home', 113, 114),
(51, 'GRCHM$HDR1$LBL3$DDI3', 'Override', 'Überschreiben', 'Eseguire l''override', 'Substituez', 'Anulación', '3', NULL, 3, 'Home', 115, 116),
(52, 'GRCHM$HDR1$LBL4', 'Setback Heat', 'Rückschlag-Hitze', 'Battuta d''arresto calore', 'Chaleur de revers ', 'Calor de revés', NULL, 'fieldlabel', NULL, 'Home', 118, 119),
(53, 'GRCHM$HDR1$LBL5', 'Setback Cool', 'Rückschlag Cool', 'Battuta d''arresto Cool', 'Revers Cool ', 'Cool de revés', NULL, 'fieldlabel', NULL, 'Home', 120, 121),
(54, 'GRCHM$HDR2', 'Setback Scheduling', 'Rückschlag zu planen', 'Battuta d''arresto di pianificazione', 'Planification des revers', 'Programación de revés', NULL, 'sectionheader', NULL, 'Home', 123, 176),
(55, 'GRCHM$HDR2$CPT1', 'Day Class Schedules', 'Tag Stundenpläne', 'Giorno orari di classe', 'Jour horaires de classe', 'Horarios día', NULL, 'subsectionheader', NULL, 'Home', 124, 125),
(56, 'GRCHM$HDR2$CPT2', 'Default Weekly Schedule', 'Standard-Wochenplan', 'Pianificazione settimanale predefinita', 'Horaire hebdomadaire par défaut', 'Programación semanal de predeterminada', NULL, 'subsectionheader', NULL, 'Home', 126, 127),
(57, 'GRCHM$HDR2$CLM1', 'Occupied', 'Besetzt', 'Occupato', 'Occupé', 'Ocupada', NULL, 'occupied', NULL, 'Home', 136, 137),
(58, 'GRCHM$HDR2$CLM2', 'Unoccupied', 'Unbesetzt', 'Non occupato', 'Inoccupé', 'Desocupada', NULL, 'unoccupied', NULL, 'Home', 138, 139),
(59, 'GRCHM$HDR2$CLM3', 'Other', 'Andere', 'Altri', 'Autres', 'Otros', NULL, 'other', NULL, 'Home', 140, 141),
(60, 'GRCHM$HDR2$SCL1', 'Period', 'Zeitraum', 'Periodo', 'Période', 'Período', NULL, NULL, NULL, 'Home', 156, 157),
(61, 'GRCHM$HDR2$SCL2', 'Time', 'Zeit', 'Tempo', 'Temps', 'Tiempo', NULL, NULL, NULL, 'Home', 158, 159),
(62, 'GRCHM$HDR2$SCL3', 'Heat', 'Wärme', 'Calore', 'Chaleur', 'Calor', NULL, 'heat', NULL, 'Home', 160, 161),
(63, 'GRCHM$HDR2$SCL4', 'Cool', 'Cool', 'Cool', 'Cool', 'Cool', NULL, 'cool', NULL, 'Home', 162, 163),
(64, 'GRCHM$HDR2$SCL5', 'Fan', 'Lüfter', 'Ventilatore', 'Ventilateur', 'Ventilador', NULL, NULL, NULL, 'Home', 164, 169),
(65, 'GRCHM$HDR2$SCL5$DDI1', 'Off', 'Ausschalten', 'Fuori', 'Hors', 'Apagado', '0', NULL, 1, 'Home', 165, 166),
(66, 'GRCHM$HDR2$SCL5$DDI2', 'On', 'Auf', 'Su', 'Sur', 'En', '60', NULL, 2, 'Home', 167, 168),
(67, 'GRCHM$HDR2$CLM4', 'Sunday', 'Sonntag', 'Domenica', 'Dimanche', 'Domingo', NULL, NULL, NULL, 'Home', 142, 143),
(68, 'GRCHM$HDR2$CLM5', 'Monday', 'Montag', 'Lunedì', 'Lundi', 'Lunes', NULL, NULL, NULL, 'Home', 144, 145),
(69, 'GRCHM$HDR2$CLM6', 'Tuesday', 'Dienstag', 'Martedì', 'Mardi', 'Martes', NULL, NULL, NULL, 'Home', 146, 147),
(70, 'GRCHM$HDR2$CLM7', 'Wednesday', 'Mittwoch', 'Mercoledì', 'Mercredi', 'Miércoles', NULL, NULL, NULL, 'Home', 148, 149),
(71, 'GRCHM$HDR2$CLM8', 'Thursday', 'Donnerstag', 'Giovedì', 'Jeudi', 'Jueves', NULL, NULL, NULL, 'Home', 150, 151),
(72, 'GRCHM$HDR2$CLM9', 'Friday', 'Freitag', 'Venerdì', 'Vendredi', 'Viernes', NULL, NULL, NULL, 'Home', 152, 153),
(73, 'GRCHM$HDR2$CLM10', 'Saturday', 'Samstag', 'Sabato', 'Samedi', 'Sábado', NULL, NULL, NULL, 'Home', 154, 155),
(74, 'GRCHM$HDR2$LBL1', 'Morning', 'Morgen', 'Mattina', 'Matin', 'Por la mañana', NULL, 'periodlabelcell', NULL, 'Home', 128, 129),
(75, 'GRCHM$HDR2$LBL2', 'Day', 'Tag', 'Giorno', 'Journée', 'Día', NULL, 'periodlabelcell', NULL, 'Home', 130, 131),
(76, 'GRCHM$HDR2$LBL3', 'Evening', '''Nabend', 'Sera', 'Soirée', 'Por la noche', NULL, 'periodlabelcell', NULL, 'Home', 132, 133),
(77, 'GRCHM$HDR2$LBL4', 'Night', 'Nacht', 'Notte', 'Nuit', 'Noche', NULL, 'periodlabelcell', NULL, 'Home', 134, 135),
(78, 'GRCHM$HDR2$DDI1', 'Occupied', 'Besetzt', 'Occupato', 'Occupé', 'Ocupada', '1', 'occupied', 1, 'Home', 170, 171),
(79, 'GRCHM$HDR2$DDI2', 'Unoccupied', 'Unbesetzt', 'Non occupato', 'Inoccupé', 'Desocupada', '2', 'unoccupied', 2, 'Home', 172, 173),
(80, 'GRCHM$HDR2$DDI3', 'Other', 'Andere', 'Altri', 'Autres', 'Otros', '3', 'other', 3, 'Home', 174, 175),
(81, 'GRCHM$HDR3', 'Sensor Settings', 'Sensoreinstellungen', 'Impostazioni sensore', 'Paramètres de capteur', 'Configuración del sensor', NULL, 'sectionheader', NULL, 'Home', 177, 194),
(82, 'GRCHM$HDR3$LBL1', 'Local Sensor', 'Lokaler Sensor', 'Sensore locale', 'Capteur local', 'Sensor local', NULL, 'sensorlabelcell', NULL, 'Home', 178, 179),
(83, 'GRCHM$HDR3$LBL2', 'Remote Sensor 1', 'Fernfühler 1', 'Sensore remoto 1', '1 Capteur de télécommande', 'Sensor remoto 1', NULL, 'sensorlabelcell', NULL, 'Home', 180, 181),
(84, 'GRCHM$HDR3$LBL3', 'Remote Sensor 2', 'Fernfühler 2', 'Sensore remoto 2', 'Capteur de télécommande 2', 'Sensor remoto 2', NULL, 'sensorlabelcell', NULL, 'Home', 182, 183),
(85, 'GRCHM$HDR3$CLM1', 'State', 'Zustand', 'Stato', 'État', 'Estado', NULL, NULL, NULL, 'Home', 184, 185),
(86, 'GRCHM$HDR3$CLM2', 'Averaging', 'Mit durchschnittlich', 'Media', 'Avec une moyenne', 'Con un promedio de', NULL, NULL, NULL, 'Home', 186, 187),
(87, 'GRCHM$HDR3$CLM3', 'Correction', 'Korrektur', 'Correzione', 'Correction', 'Corrección', NULL, NULL, NULL, 'Home', 188, 189),
(88, 'GRCHM$HDR3$DDI1', 'Disabled', 'Behinderte', 'Disabili', 'Handicapés', 'Discapacitados', '1', NULL, 1, 'Home', 190, 191),
(89, 'GRCHM$HDR3$DDI2', 'Enabled', 'Aktiviert', 'Abilitato', 'Activé', 'Habilitado', '2', NULL, 2, 'Home', 192, 193),
(91, 'GRCHM$BTN1', 'Submit', 'Senden', 'Invia', 'Envoyer', 'Enviar', NULL, 'commandbutton', NULL, 'Home', 195, 196),
(92, 'GRCHM$CLM8$LBL1', 'Username:', 'Benutzername:', 'Nome utente:', 'Nom d''utilisateur :', 'Nombre de usuario:', NULL, 'fieldlabel', NULL, 'Home', 70, 71),
(93, 'GRCHM$CLM8$LBL2', 'Password:', 'Passwort:', 'Password:', 'Mot de passe :', 'Contraseña:', NULL, 'fieldlabel', NULL, 'Home', 72, 73),
(94, 'GRCHM$CLM8$BTN1', 'Save', 'Speichern', 'Salvare', 'Enregistrer', 'Guardar', NULL, 'commandbutton', NULL, 'Home', 74, 75),
(95, 'GRCHM$CLM8$BTN2', 'Cancel', 'Abbrechen', 'Annulla', 'Annuler', 'Cancelar', NULL, 'commandbutton', NULL, 'Home', 76, 77),
(96, 'GRCTL', 'Transaction Log', 'Transaktionsprotokoll', 'Log delle transazioni', 'Journal des transactions', 'Registro de transacciones', NULL, NULL, NULL, 'Transaction Log', 200, 217),
(97, 'GRCTL$HLK1', 'Main Page', 'Main Page', 'Principale pagina', 'Main Page', 'Principal página', NULL, NULL, NULL, 'Transaction Log', 203, 204),
(98, 'GRCTL$HLK2', 'Clear Search', 'Suche löschen', 'Ricerca chiara', 'Effacer la recherche', 'Borrar búsqueda', NULL, NULL, NULL, 'Transaction Log', 205, 206),
(99, 'GRCTL$CLM1', 'Thermostat', 'Thermostat', 'Termostato', 'Thermostat', 'Termostato', NULL, NULL, NULL, 'Transaction Log', 207, 208),
(100, 'GRCTL$CLM2', 'Field', 'Feld', 'Campo', 'Champ', 'Campo', NULL, NULL, NULL, 'Transaction Log', 209, 210),
(101, 'GRCTL$CLM3', 'Old Value', 'Der alte Wert', 'Valore precedente', 'Ancienne valeur', 'Valor antiguo', NULL, NULL, NULL, 'Transaction Log', 211, 212),
(102, 'GRCTL$CLM4', 'New Value', 'Neuen Wert', 'Nuovo valore', 'Nouvelle valeur', 'Nuevo valor', NULL, NULL, NULL, 'Transaction Log', 213, 214),
(103, 'GRCTL$CLM5', 'Timestamp', 'Timestamp', 'Timestamp', 'Timestamp', 'Fecha y hora', NULL, NULL, NULL, 'Transaction Log', 215, 216),
(104, 'GRCTL$LBL1', 'Search:', 'Suche:', 'Ricerca:', 'Rechercher :', 'Búsqueda:', NULL, NULL, NULL, 'Transaction Log', 201, 202),
(105, 'GRCHM$MSG6', 'Are you sure you want to delete this thermostat?', 'Sind Sie sicher, dass Sie dieses Thermostat löschen möchten?', 'Sei sicuro di che voler eliminare questo termostato?', 'Êtes-vous sûr de que vouloir supprimer ce thermostat ?', '¿Está seguro que desea eliminar este termostato?', NULL, NULL, NULL, 'Home', 33, 34),
(106, 'GRC', 'GNU Remote Control', 'GNU Remote Control', 'GNU Remote Control', 'GNU Remote Control', 'GNU Remote Control', NULL, NULL, NULL, '*', 1, 238),
(112, 'GRC$MSG1', 'Could not create XMLHttpRequest object', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '*', 2, 3),
(113, 'GRC$MSG2', 'An error occurred, but the error message cannot be displayed. This is probably because of your browser''s pop-up blocker. \n Please allow pop-ups from this web site if you want to see the full error messages. \n\n Status Code: {0}\n Status Description: {1}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '*', 4, 5),
(114, 'GRCHM$CLM9', 'Time Zone Offset', 'Zeitzonenoffset', 'Offset del fuso orario', 'Décalage de fuseau horaire', 'Zona horaria', NULL, NULL, NULL, 'Home', 79, 82),
(115, 'GRCHM$BTN2', 'Set Time on Selected Thermostats', 'Eingestellten Zeit auf ausgewählten Thermostate', 'Tempo impostato su termostati selezionati', 'Durée réglée sur certains Thermostats', 'Tiempo en termostatos seleccionados', NULL, 'commandbutton', NULL, 'Home', 197, 198),
(116, 'GRCHM$LBL8', '{0} (at page load)', '{0} (bei Laden der Seite)', '{0} (al caricamento della pagina)', '{0} (au chargement de la page)', '{0} (en la carga de la página)', NULL, NULL, NULL, 'Home', 21, 22),
(117, 'GRCHM$CLM9$DDI1', '-- Select Offset --', '-Wählen Sie Offset-', '-- Selezionare Offset-', '--Sélectionnez Offset--', '--Seleccione desvío--', '', NULL, 1, 'Home', 80, 81),
(118, 'GRCAD', 'Administration', 'Verwaltung', 'Amministrazione', 'Administration', 'Administración', NULL, NULL, NULL, 'Admin', 218, 237),
(119, 'GRCAD$LBL1', 'Total # of user accounts:', 'Gesamtanzahl der Benutzer-Accounts:', '# Totale degli account utente:', 'Nombre total de comptes d''utilisateurs :', 'Número total de cuentas de usuario:', NULL, NULL, NULL, 'Admin', 219, 220),
(120, 'GRCAD$LBL2', 'Total # of thermostats:', 'Gesamtanzahl der Thermostate:', '# Totale di termostati:', 'Nbre total de thermostats :', 'Número total de termostatos:', NULL, NULL, NULL, 'Admin', 221, 222),
(121, 'GRCAD$CLM1', 'UserName', 'Benutzername', 'UserName', 'Nom d''utilisateur', 'Nombre de usuario', NULL, NULL, NULL, 'Admin', 233, 234),
(122, 'GRCAD$CLM2', 'Maximum # Thermostats', 'Maximum Thermostate', 'Massimo # termostati', 'Nombre maximal de Thermostats', 'Número máximo de termostatos', NULL, NULL, NULL, 'Admin', 235, 236),
(123, 'GRCAD$CLM3', 'Language', 'Sprache', 'Lingua', 'Langue', 'Idioma', NULL, NULL, NULL, 'Admin', 237, 238),
(124, 'GRCAD$HLK1', 'Add', 'Hinzufügen', 'Aggiungi', 'Ajouter', 'Añadir', NULL, 'add', NULL, 'Admin', 223, 224),
(125, 'GRCAD$HLK2', 'Edit', 'Bearbeiten', 'Modifica', 'Edit', 'Editar', NULL, 'edit', NULL, 'Admin', 225, 226),
(126, 'GRCAD$HLK3', 'Update', 'Update', 'Aggiornamento', 'Mise à jour', 'Actualización', NULL, 'update', NULL, 'Admin', 227, 228),
(127, 'GRCAD$HLK4', 'Cancel', 'Abbrechen', 'Annulla', 'Annuler', 'Cancelar', NULL, 'cancel', NULL, 'Admin', 229, 230),
(128, 'GRCAD$HLK5', 'Delete', 'Löschen', 'Eliminare', 'Supprimer', 'Eliminar', NULL, 'delete', NULL, 'Admin', 231, 232);

CREATE TABLE IF NOT EXISTS `thermostat` (
  `ThermostatId` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(40) NOT NULL,
  `Description` varchar(400) DEFAULT NULL,
  `Host` varchar(255) NOT NULL,
  `Port` mediumint(8) unsigned NOT NULL,
  `AuthString` varchar(40) DEFAULT NULL,
  `TimeZoneOffsetID` tinyint(4) unsigned NOT NULL,
  `UserId` smallint(5) unsigned NOT NULL,
  `Deleted` tinyint(4) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ThermostatId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=963 AUTO_INCREMENT=14 ;

INSERT INTO `thermostat` (`ThermostatId`, `Name`, `Description`, `Host`, `Port`, `AuthString`, `TimeZoneOffsetID`, `UserId`, `Deleted`) VALUES
(1, 'customname1', 'desc1', '126.125.123.32', 25, NULL, 1, 3, 1),
(2, 'customname2', 'desc2', '128.125.132.12', 26, NULL, 1, 3, 1),
(3, 'custom3', 'desc3', '165.178.164.198', 27, NULL, 8, 3, 1),
(4, 'custom3', 'desc54', '1233', 25, 'admin:123456', 9, 3, 0),
(5, 'custom5', 'dec4', '129.168.124.132', 27, NULL, 3, 3, 1),
(6, 'csacsac', 'asdasd', '123123', 25, NULL, 2, 3, 0),
(7, 'cdacd', 'cadc', '13213', 23, NULL, 6, 3, 0),
(8, 'csads', 'dewer', '123123', 26, NULL, 2, 3, 0),
(9, '123', '456', '789', 765, NULL, 1, 3, 0),
(10, '123', '132', '312', 42, NULL, 1, 3, 0),
(11, 'rew', 'rew', '123', 132, NULL, 2, 3, 1),
(12, 'wqe', 'qwqwe', '31', 23, NULL, 1, 3, 1),
(13, 'weqqwe', 'eqwqwe', '123', 123, NULL, 2, 3, 0);

CREATE TABLE IF NOT EXISTS `timezoneoffset` (
  `TimeZoneOffsetID` tinyint(4) unsigned NOT NULL AUTO_INCREMENT,
  `Description` varchar(20) DEFAULT NULL,
  `Offset` decimal(4,1) DEFAULT NULL,
  PRIMARY KEY (`TimeZoneOffsetID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=321 AUTO_INCREMENT=50 ;

INSERT INTO `timezoneoffset` (`TimeZoneOffsetID`, `Description`, `Offset`) VALUES
(1, 'GMT -12:00', -12.0),
(2, 'GMT -11:30', -11.5),
(3, 'GMT -11:00', -11.0),
(4, 'GMT -10:30', -10.5),
(5, 'GMT -10:00', -10.0),
(6, 'GMT -9:30', -9.5),
(7, 'GMT -9:00', -9.0),
(8, 'GMT -8:30', -8.5),
(9, 'GMT -8:00', -8.0),
(10, 'GMT -7:30', -7.5),
(11, 'GMT -7:00', -7.0),
(12, 'GMT -6:30', -6.5),
(13, 'GMT -6:00', -6.0),
(14, 'GMT -5:30', -5.5),
(15, 'GMT -5:00', -5.0),
(16, 'GMT -4:30', -4.5),
(17, 'GMT -4:00', -4.0),
(18, 'GMT -3:30', -3.5),
(19, 'GMT -3:00', -3.0),
(20, 'GMT -2:30', -2.5),
(21, 'GMT -2:00', -2.0),
(22, 'GMT -1:30', -1.5),
(23, 'GMT -1:00', -1.0),
(24, 'GMT -0:30', -0.5),
(25, 'GMT 0:00', 0.0),
(26, 'GMT 0:30', 0.5),
(27, 'GMT 1:00', 1.0),
(28, 'GMT 1:30', 1.5),
(29, 'GMT 2:00', 2.0),
(30, 'GMT 2:30', 2.5),
(31, 'GMT 3:00', 3.0),
(32, 'GMT 3:30', 3.5),
(33, 'GMT 4:00', 4.0),
(34, 'GMT 4:30', 4.5),
(35, 'GMT 5:00', 5.0),
(36, 'GMT 5:30', 5.5),
(37, 'GMT 6:00', 6.0),
(38, 'GMT 6:30', 6.5),
(39, 'GMT 7:00', 7.0),
(40, 'GMT 7:30', 7.5),
(41, 'GMT 8:00', 8.0),
(42, 'GMT 8:30', 8.5),
(43, 'GMT 9:00', 9.0),
(44, 'GMT 9:30', 9.5),
(45, 'GMT 10:00', 10.0),
(46, 'GMT 10:30', 10.5),
(47, 'GMT 11:00', 11.0),
(48, 'GMT 11:30', 11.5),
(49, 'GMT 12:00', 12.0);

CREATE TABLE IF NOT EXISTS `transactionlog` (
  `TransactionId` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `ThermostatId` mediumint(9) unsigned NOT NULL,
  `OID` varchar(20) NOT NULL,
  `OldValue` varchar(20) DEFAULT NULL,
  `NewValue` varchar(20) DEFAULT NULL,
  `TransactionTimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `TransactionUserId` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`TransactionId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=121 AUTO_INCREMENT=2 ;

INSERT INTO `transactionlog` (`TransactionId`, `ThermostatId`, `OID`, `OldValue`, `NewValue`, `TransactionTimeStamp`, `TransactionUserId`) VALUES
(1, 1, '2', 'qwe', 'ewq', '2013-06-05 21:19:19', 0);

CREATE TABLE IF NOT EXISTS `user` (
  `UserId` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `UserName` varchar(20) NOT NULL,
  `MaxNumThermostats` mediumint(8) unsigned DEFAULT NULL,
  `LangCode` varchar(3) NOT NULL,
  `ThermostatSort` varchar(20),
  PRIMARY KEY (`UserId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=2730 AUTO_INCREMENT=14 ;

INSERT INTO `user` (`UserId`, `UserName`, `MaxNumThermostats`, `LangCode`, `ThermostatSort`) VALUES
(1, 'tstat_test_user_1', 10, 'en', null),
(2, 'tstat_test_user_2 ', 25, 'en', null),
(3, 'tstat_admin', 1156, 'en', 'Name DESC');

