/*     
       # 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/>.
*/

﻿-- Script was generated by Devart dbForge Studio Express for MySQL, Version 5.0.97.0
-- Product home page: http://www.devart.com/dbforge/mysql/studio
-- Script date 12/14/2012 10:30:32 PM
-- Server version: 5.5.20
-- Client version: 4.1

-- 
-- Disable foreign keys
-- 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

-- 
-- Set character set the client will use to send SQL statements to the server
--
SET NAMES 'utf8';

--
-- 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;

--
-- Definition for table errorlog
--
CREATE TABLE IF NOT EXISTS errorlog (
  ErrorID SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  Message VARCHAR(800) NOT NULL,
  `TimeStamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (ErrorID)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

--
-- Definition for table language
--
CREATE TABLE IF NOT EXISTS language (
  Code VARCHAR(3) NOT NULL,
  Description VARCHAR(255) DEFAULT NULL,
  SortOrder TINYINT(4) UNSIGNED DEFAULT NULL
)
ENGINE = INNODB
AVG_ROW_LENGTH = 3276
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

--
-- Definition for table oidenumvaluelookup
--
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
AUTO_INCREMENT = 104
AVG_ROW_LENGTH = 159
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

--
-- Definition for table oidfieldnamelookup
--
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
AVG_ROW_LENGTH = 215
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

--
-- Definition for table resources
--
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
AUTO_INCREMENT = 128
AVG_ROW_LENGTH = 387
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

--
-- Definition for table thermostat
--
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
AUTO_INCREMENT = 23
AVG_ROW_LENGTH = 963
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

--
-- Definition for table timezoneoffset
--
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
AUTO_INCREMENT = 52
AVG_ROW_LENGTH = 321
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

--
-- Definition for table transactionlog
--
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 DEFAULT CURRENT_TIMESTAMP,
  TransactionUserId MEDIUMINT(8) UNSIGNED NOT NULL,
  PRIMARY KEY (TransactionId)
)
ENGINE = INNODB
AUTO_INCREMENT = 136
AVG_ROW_LENGTH = 121
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

--
-- Definition for table user
--
CREATE TABLE IF NOT EXISTS user (
  UserId SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  UserName VARCHAR(20) NOT NULL,
  MaxNumThermostats TINYINT(3) UNSIGNED DEFAULT NULL,
  LangCode VARCHAR(3) NOT NULL,
  PRIMARY KEY (UserId)
)
ENGINE = INNODB
AUTO_INCREMENT = 51
AVG_ROW_LENGTH = 2730
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

DELIMITER $$

--
-- Definition for procedure sp_Error_Insert
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_Error_Insert(pErrorMessage varchar(800))
BEGIN
		insert into errorlog(Message)
		values(pErrorMessage);
    END
$$

--
-- Definition for procedure sp_Languages_Load_All
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_Languages_Load_All()
BEGIN
  select Code, Description from iptstat.language order by SortOrder;
END
$$

--
-- Definition for procedure sp_OID_FieldName_Load
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_OID_FieldName_Load(pOID varchar(20))
BEGIN
		select FieldName from oidfieldnamelookup where OID = pOID;
    END
$$

--
-- Definition for procedure sp_OID_Load_All
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_OID_Load_All()
BEGIN
		select OID, FieldName from oidfieldnamelookup
		order by OID;
    END
$$

--
-- Definition for procedure sp_Resources_Load_All
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_Resources_Load_All()
BEGIN
  select ResourceID, Code, Value, DropDownItemValue, StyleClass, SortOrder, Page, LFT, RGT from iptstat.resources order by LFT;
END
$$

--
-- Definition for procedure sp_Resources_Load_By_Page
--
CREATE DEFINER = 'root'@'localhost'
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
$$

--
-- Definition for procedure sp_Resources_Load_By_Parent_Code
--
CREATE DEFINER = 'root'@'localhost'
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
$$

--
-- Definition for procedure sp_Resources_Translate
--
CREATE DEFINER = 'root'@'localhost'
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
$$

--
-- Definition for procedure sp_Resource_Load_By_Code
--
CREATE DEFINER = 'root'@'localhost'
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
$$

--
-- Definition for procedure sp_Thermostat_Auth_Info_Update
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_Thermostat_Auth_Info_Update(pThermostatId mediumint, pAuthString varchar(40))
BEGIN
		update thermostat set AuthString = pAuthString where ThermostatId = pThermostatId;
    END
$$

--
-- Definition for procedure sp_Thermostat_Delete
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_Thermostat_Delete(pThermostatId mediumint)
BEGIN
		update thermostat set Deleted = 1 where ThermostatId = pThermostatId;
    END
$$

--
-- Definition for procedure sp_Thermostat_Insert_Update
--
CREATE DEFINER = 'root'@'localhost'
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
$$

--
-- Definition for procedure sp_Thermostat_Load_All
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_Thermostat_Load_All(IN pUserId smallint)
BEGIN
	select t.ThermostatId, t.Name, t.Description, t.Host, t.Port, t.AuthString, t.TimeZoneOffsetID from thermostat t
	where t.UserId = pUserId and t.Deleted = 0
	ORDER BY t.Name;
    END
$$

--
-- Definition for procedure sp_Thermostat_Load_Details
--
CREATE DEFINER = 'root'@'localhost'
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
$$

--
-- Definition for procedure sp_TimeZoneOffset_Load_All
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_TimeZoneOffset_Load_All()
BEGIN
  select TimeZoneOffsetID, Description, `Offset` from iptstat.timezoneoffset order by `Offset`;
END
$$

--
-- Definition for procedure sp_TimeZoneOffset_Load_Details
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_TimeZoneOffset_Load_Details(IN pTimeZoneOffsetID TINYINT)
BEGIN
  select Description, `Offset` from iptstat.timezoneoffset where TimeZoneOffsetID = pTimeZoneOffsetID;
END
$$

--
-- Definition for procedure sp_Transaction_Log_Insert
--
CREATE DEFINER = 'root'@'localhost'
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
$$

--
-- Definition for procedure sp_Transaction_Log_Load_All
--
CREATE DEFINER = 'root'@'localhost'
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
$$

--
-- Definition for procedure sp_Transaction_Log_Load_Details
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_Transaction_Log_Load_Details(pTransactionId int)
BEGIN
		SELECT ThermostatId, OID, OldValue, NewValue, TransactionTimeStamp
		FROM transactionlog			
		WHERE TransactionId = pTransactionId;
    END
$$

--
-- Definition for procedure sp_User_Delete
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_User_Delete(pUserId mediumint)
BEGIN
		delete from `user` where UserId = pUserId;
    END
$$

--
-- Definition for procedure sp_User_Insert_Update
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_User_Insert_Update(IN pUserId MEDIUMINT, IN pUserName VARCHAR(20), IN pMaxNumThermostats TINYINT, 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
$$

--
-- Definition for procedure sp_User_Load_All
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_User_Load_All()
BEGIN
	select UserId, UserName, MaxNumThermostats, LangCode from `user` order by UserName;
    END
$$

--
-- Definition for procedure sp_User_Load_Details_By_UserID
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_User_Load_Details_By_UserID(IN pUserId mediumint)
BEGIN	
		SELECT UserName, IFNULL(MaxNumThermostats, 0) `MaxNumThermostats`, LangCode FROM `user`
		WHERE UserId = pUserId;
    END
$$

--
-- Definition for procedure sp_User_Load_Details_By_UserName
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE sp_User_Load_Details_By_UserName(IN pUserName varchar(20))
BEGIN	
		SELECT UserId, IFNULL(MaxNumThermostats, 0) `MaxNumThermostats`, LangCode FROM `user`
		WHERE UserName = pUserName;
    END
$$

DELIMITER ;

-- 
-- Insert data for table language
--
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);

-- 
-- Insert data for table oidenumvaluelookup
--
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');

-- 
-- Insert data for table oidfieldnamelookup
--
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');

-- 
-- Insert data for table resources
--
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$HLK1', 'Add', 'Hinzufügen', 'Aggiungi', 'Ajouter', 'Añadir', NULL, 'add', NULL, 'Admin', 223, 224),
  (124, 'GRCAD$HLK2', 'Edit', 'Bearbeiten', 'Modifica', 'Edit', 'Editar', NULL, 'edit', NULL, 'Admin', 225, 226),
  (125, 'GRCAD$HLK3', 'Update', 'Update', 'Aggiornamento', 'Mise à jour', 'Actualización', NULL, 'update', NULL, 'Admin', 227, 228),
  (126, 'GRCAD$HLK4', 'Cancel', 'Abbrechen', 'Annulla', 'Annuler', 'Cancelar', NULL, 'cancel', NULL, 'Admin', 229, 230),
  (127, 'GRCAD$HLK5', 'Delete', 'Löschen', 'Eliminare', 'Supprimer', 'Eliminar', NULL, 'delete', NULL, 'Admin', 231, 232);

-- 
-- Insert data for table timezoneoffset
--
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);

-- 
-- Enable foreign keys
-- 
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;