
CREATE TABLE IF NOT EXISTS `opl_genre` (
  `gId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `author` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `introduction` text CHARACTER SET latin1,
  `createdDate` datetime DEFAULT NULL,
  `modDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`gId`),
  KEY `key1` (`title`,`author`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE  IF NOT EXISTS `pf_bookRs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rid` int(10) unsigned NOT NULL DEFAULT '0',
  `pfId` int(10) unsigned NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `author` varchar(255) DEFAULT NULL,
  `pubName` varchar(255) DEFAULT NULL,
  `pubPlace` varchar(255) DEFAULT NULL,
  `pubDate` varchar(50) DEFAULT NULL,
  `callNumber` varchar(100) DEFAULT NULL,
  `isbn` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`,`pfId`),
  KEY `Index_4` (`title`) USING BTREE
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `pf_webRs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pfId` int(10) unsigned NOT NULL,
  `title` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `author` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `url` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`,`pfId`),
  KEY `Index_4` (`title`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `pf_group` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `groupId` int(10) unsigned NOT NULL DEFAULT '0',
  `pfId` int(10) unsigned NOT NULL,
  `header` varchar(255) DEFAULT NULL,
  `description` text,
  `deleted` tinyint(3) unsigned DEFAULT '0',
  `rsType` varchar(100) DEFAULT '',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

CREATE TABLE `pf_file` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pfId` int(10) unsigned NOT NULL,
  `groupId` tinyint(3) unsigned DEFAULT '1',
  `title` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `fName` varchar(255) DEFAULT NULL,
  `fRealName` varchar(255) NOT NULL,
  `url` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`,`pfId`),
  KEY `Index_4` (`title`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



INSERT INTO `opl_prefFormParam` 
    VALUES 
    ('allowSelfRenewal',0,'On','1'),
    ('allowSelfRenewal',1,'Off','0'),
    ('anonymizeLoan',0,'off',0),
    ('anonymizeLoan',1,'after 30 days',30),
    ('anonymizeLoan',2,'after 60 days',60),
    ('anonymizeLoan',3,'after 90 days',90),
    ('anonymizeLoan',4,'after 120 days',120),
    ('anonymizeLoan',5,'after 150 days',150),
    ('anonymizeLoan',6,'after 180 days',180),
    ('anonymizeLoan',7,'after 210 days',210),
    ('anonymizeLoan',8,'after 240 days',240),
    ('anonymizeLoan',9,'after 270 days',270),
    ('anonymizeLoan',10,'after 300 days',300),
    ('anonymizeLoan',11,'after 330 days',330),
    ('anonymizeLoan',12,'after 1 year',365),
    ('anonymizeLoan',13,'after 2 years',730),
    ('anonymizeLoan',14,'after 3 years',1095);
    
insert into  opl_preference values
    ('anonymizeLoan','0' ,'off',NULL,'select','', 0,2,6),('allowSelfRenewal','1','On','0 | 1','radio','Allow self-renewal',0,2,8),
    
    ('custRptFields','{"itemStatusReport-11":[{"sortField":"rid","show":1,"inc":"1","msgId":"recIdTxt","field":"rid"},{"sortField":"author","show":1,"inc":"1","msgId":"authorTxt","field":"author"},{"sortField":"titleSort","show":1,"inc":"1","msgId":"titleTxt","field":"title"},{"sortField":"isbn","show":1,"inc":"1","msgId":"isbnTxt","field":"isbn"},{"sortField":"callNumber","show":1,"inc":"1","msgId":"callNumTxt","field":"callNumber"},{"sortField":"pubDateSort","show":1,"inc":"1","msgId":"pubDateTxt","field":"pubDate"},{"sortField":"price","show":1,"inc":"1","msgId":"priceTxt","field":"price"},{"sortField":"barcode","show":1,"inc":"1","msgId":"barcodeTxt","field":"barcode"},{"sortField":"ondate","show":1,"inc":"1","msgId":"dateTxt","field":"ondate"},{"sortField":"status","incRpt":[0],"show":0,"inc":"1","msgId":"curItemStatusTxt","field":"status"},{"sortField":"firstname","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"firstnameTxt","field":"firstname"},{"sortField":"lastname","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"lastnameTxt","field":"lastname"},{"sortField":"userbarcode","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"userBarcodeTxt","field":"userbarcode"}],"itemStatusReport-5":[{"sortField":"rid","show":1,"inc":"1","msgId":"recIdTxt","field":"rid"},{"sortField":"author","show":1,"inc":"1","msgId":"authorTxt","field":"author"},{"sortField":"titleSort","show":1,"inc":"1","msgId":"titleTxt","field":"title"},{"sortField":"isbn","show":1,"inc":"1","msgId":"isbnTxt","field":"isbn"},{"sortField":"callNumber","show":1,"inc":"1","msgId":"callNumTxt","field":"callNumber"},{"sortField":"pubDateSort","show":1,"inc":"1","msgId":"pubDateTxt","field":"pubDate"},{"sortField":"price","show":1,"inc":"1","msgId":"priceTxt","field":"price"},{"sortField":"barcode","show":1,"inc":"1","msgId":"barcodeTxt","field":"barcode"},{"sortField":"ondate","show":1,"inc":"1","msgId":"dateTxt","field":"ondate"},{"sortField":"status","incRpt":[0],"show":0,"inc":"1","msgId":"curItemStatusTxt","field":"status"},{"sortField":"firstname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"firstnameTxt","field":"firstname"},{"sortField":"lastname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"lastnameTxt","field":"lastname"},{"sortField":"userbarcode","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"userBarcodeTxt","field":"userbarcode"}],"itemStatusReport-0":[{"sortField":"rid","show":1,"inc":"1","msgId":"recIdTxt","field":"rid"},{"sortField":"author","show":1,"inc":"1","msgId":"authorTxt","field":"author"},{"sortField":"titleSort","show":1,"inc":"1","msgId":"titleTxt","field":"title"},{"sortField":"isbn","show":1,"inc":"1","msgId":"isbnTxt","field":"isbn"},{"sortField":"callNumber","show":1,"inc":"1","msgId":"callNumTxt","field":"callNumber"},{"sortField":"pubDateSort","show":1,"inc":"1","msgId":"pubDateTxt","field":"pubDate"},{"sortField":"price","show":1,"inc":"1","msgId":"priceTxt","field":"price"},{"sortField":"barcode","show":1,"inc":"1","msgId":"barcodeTxt","field":"barcode"},{"sortField":"ondate","show":1,"inc":"1","msgId":"dateTxt","field":"ondate"},{"sortField":"status","incRpt":[0],"show":1,"inc":"1","msgId":"curItemStatusTxt","field":"status"},{"sortField":"firstname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"firstnameTxt","field":"firstname"},{"sortField":"lastname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"lastnameTxt","field":"lastname"},{"sortField":"userbarcode","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"userBarcodeTxt","field":"userbarcode"}],"itemStatusReport-10":[{"sortField":"rid","show":1,"inc":"1","msgId":"recIdTxt","field":"rid"},{"sortField":"author","show":1,"inc":"1","msgId":"authorTxt","field":"author"},{"sortField":"titleSort","show":1,"inc":"1","msgId":"titleTxt","field":"title"},{"sortField":"isbn","show":1,"inc":"1","msgId":"isbnTxt","field":"isbn"},{"sortField":"callNumber","show":1,"inc":"1","msgId":"callNumTxt","field":"callNumber"},{"sortField":"pubDateSort","show":1,"inc":"1","msgId":"pubDateTxt","field":"pubDate"},{"sortField":"price","show":1,"inc":"1","msgId":"priceTxt","field":"price"},{"sortField":"barcode","show":1,"inc":"1","msgId":"barcodeTxt","field":"barcode"},{"sortField":"ondate","show":1,"inc":"1","msgId":"dateTxt","field":"ondate"},{"sortField":"status","incRpt":[0],"show":0,"inc":"1","msgId":"curItemStatusTxt","field":"status"},{"sortField":"firstname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"firstnameTxt","field":"firstname"},{"sortField":"lastname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"lastnameTxt","field":"lastname"},{"sortField":"userbarcode","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"userBarcodeTxt","field":"userbarcode"}],"itemStatusReport-3":[{"sortField":"rid","show":1,"inc":"1","msgId":"recIdTxt","field":"rid"},{"sortField":"author","show":1,"inc":"1","msgId":"authorTxt","field":"author"},{"sortField":"titleSort","show":1,"inc":"1","msgId":"titleTxt","field":"title"},{"sortField":"isbn","show":1,"inc":"1","msgId":"isbnTxt","field":"isbn"},{"sortField":"callNumber","show":1,"inc":"1","msgId":"callNumTxt","field":"callNumber"},{"sortField":"pubDateSort","show":1,"inc":"1","msgId":"pubDateTxt","field":"pubDate"},{"sortField":"price","show":1,"inc":"1","msgId":"priceTxt","field":"price"},{"sortField":"barcode","show":1,"inc":"1","msgId":"barcodeTxt","field":"barcode"},{"sortField":"ondate","show":1,"inc":"1","msgId":"dateTxt","field":"ondate"},{"sortField":"status","incRpt":[0],"show":0,"inc":"1","msgId":"curItemStatusTxt","field":"status"},{"sortField":"firstname","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"firstnameTxt","field":"firstname"},{"sortField":"lastname","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"lastnameTxt","field":"lastname"},{"sortField":"userbarcode","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"userBarcodeTxt","field":"userbarcode"}],"itemStatusReport-9":[{"sortField":"rid","show":1,"inc":"1","msgId":"recIdTxt","field":"rid"},{"sortField":"author","show":1,"inc":"1","msgId":"authorTxt","field":"author"},{"sortField":"titleSort","show":1,"inc":"1","msgId":"titleTxt","field":"title"},{"sortField":"isbn","show":1,"inc":"1","msgId":"isbnTxt","field":"isbn"},{"sortField":"callNumber","show":1,"inc":"1","msgId":"callNumTxt","field":"callNumber"},{"sortField":"pubDateSort","show":1,"inc":"1","msgId":"pubDateTxt","field":"pubDate"},{"sortField":"price","show":1,"inc":"1","msgId":"priceTxt","field":"price"},{"sortField":"barcode","show":1,"inc":"1","msgId":"barcodeTxt","field":"barcode"},{"sortField":"ondate","show":1,"inc":"1","msgId":"dateTxt","field":"ondate"},{"sortField":"status","incRpt":[0],"show":0,"inc":"1","msgId":"curItemStatusTxt","field":"status"},{"sortField":"firstname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"firstnameTxt","field":"firstname"},{"sortField":"lastname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"lastnameTxt","field":"lastname"},{"sortField":"userbarcode","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"userBarcodeTxt","field":"userbarcode"}],"itemStatusReport-8":[{"sortField":"rid","show":1,"inc":"1","msgId":"recIdTxt","field":"rid"},{"sortField":"author","show":1,"inc":"1","msgId":"authorTxt","field":"author"},{"sortField":"titleSort","show":1,"inc":"1","msgId":"titleTxt","field":"title"},{"sortField":"isbn","show":1,"inc":"1","msgId":"isbnTxt","field":"isbn"},{"sortField":"callNumber","show":1,"inc":"1","msgId":"callNumTxt","field":"callNumber"},{"sortField":"pubDateSort","show":1,"inc":"1","msgId":"pubDateTxt","field":"pubDate"},{"sortField":"price","show":1,"inc":"1","msgId":"priceTxt","field":"price"},{"sortField":"barcode","show":1,"inc":"1","msgId":"barcodeTxt","field":"barcode"},{"sortField":"ondate","show":1,"inc":"1","msgId":"dateTxt","field":"ondate"},{"sortField":"status","incRpt":[0],"show":0,"inc":"1","msgId":"curItemStatusTxt","field":"status"},{"sortField":"firstname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"firstnameTxt","field":"firstname"},{"sortField":"lastname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"lastnameTxt","field":"lastname"},{"sortField":"userbarcode","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"userBarcodeTxt","field":"userbarcode"}],"itemStatusReport-13":[{"sortField":"rid","show":1,"inc":"1","msgId":"recIdTxt","field":"rid"},{"sortField":"author","show":1,"inc":"1","msgId":"authorTxt","field":"author"},{"sortField":"titleSort","show":1,"inc":"1","msgId":"titleTxt","field":"title"},{"sortField":"isbn","show":1,"inc":"1","msgId":"isbnTxt","field":"isbn"},{"sortField":"callNumber","show":1,"inc":"1","msgId":"callNumTxt","field":"callNumber"},{"sortField":"pubDateSort","show":1,"inc":"1","msgId":"pubDateTxt","field":"pubDate"},{"sortField":"price","show":1,"inc":"1","msgId":"priceTxt","field":"price"},{"sortField":"barcode","show":1,"inc":"1","msgId":"barcodeTxt","field":"barcode"},{"sortField":"ondate","show":1,"inc":"1","msgId":"dateTxt","field":"ondate"},{"sortField":"status","incRpt":[0],"show":0,"inc":"1","msgId":"curItemStatusTxt","field":"status"},{"sortField":"firstname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"firstnameTxt","field":"firstname"},{"sortField":"lastname","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"lastnameTxt","field":"lastname"},{"sortField":"userbarcode","incRpt":[2,3,11,12],"show":0,"inc":"1","msgId":"userBarcodeTxt","field":"userbarcode"}],"itemStatusReport-2":[{"sortField":"rid","show":1,"inc":"1","msgId":"recIdTxt","field":"rid"},{"sortField":"author","show":1,"inc":"1","msgId":"authorTxt","field":"author"},{"sortField":"titleSort","show":1,"inc":"1","msgId":"titleTxt","field":"title"},{"sortField":"isbn","show":1,"inc":"1","msgId":"isbnTxt","field":"isbn"},{"sortField":"callNumber","show":1,"inc":"1","msgId":"callNumTxt","field":"callNumber"},{"sortField":"pubDateSort","show":1,"inc":"1","msgId":"pubDateTxt","field":"pubDate"},{"sortField":"price","show":1,"inc":"1","msgId":"priceTxt","field":"price"},{"sortField":"barcode","show":1,"inc":"1","msgId":"barcodeTxt","field":"barcode"},{"sortField":"ondate","show":1,"inc":"1","msgId":"dateTxt","field":"ondate"},{"sortField":"status","incRpt":[0],"show":0,"inc":"1","msgId":"curItemStatusTxt","field":"status"},{"sortField":"firstname","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"firstnameTxt","field":"firstname"},{"sortField":"lastname","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"lastnameTxt","field":"lastname"},{"sortField":"userbarcode","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"userBarcodeTxt","field":"userbarcode"}],"itemStatusReport-12":[{"sortField":"rid","show":1,"inc":"1","msgId":"recIdTxt","field":"rid"},{"sortField":"author","show":1,"inc":"1","msgId":"authorTxt","field":"author"},{"sortField":"titleSort","show":1,"inc":"1","msgId":"titleTxt","field":"title"},{"sortField":"isbn","show":1,"inc":"1","msgId":"isbnTxt","field":"isbn"},{"sortField":"callNumber","show":1,"inc":"1","msgId":"callNumTxt","field":"callNumber"},{"sortField":"pubDateSort","show":1,"inc":"1","msgId":"pubDateTxt","field":"pubDate"},{"sortField":"price","show":1,"inc":"1","msgId":"priceTxt","field":"price"},{"sortField":"barcode","show":1,"inc":"1","msgId":"barcodeTxt","field":"barcode"},{"sortField":"ondate","show":1,"inc":"1","msgId":"dateTxt","field":"ondate"},{"sortField":"status","incRpt":[0],"show":0,"inc":"1","msgId":"curItemStatusTxt","field":"status"},{"sortField":"firstname","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"firstnameTxt","field":"firstname"},{"sortField":"lastname","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"lastnameTxt","field":"lastname"},{"sortField":"userbarcode","incRpt":[2,3,11,12],"show":1,"inc":"1","msgId":"userBarcodeTxt","field":"userbarcode"}]}','',null,'text','',1,null,null);

-- equipment
--

CREATE TABLE IF NOT EXISTS eq_bcmBc (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rid` int(10) unsigned DEFAULT NULL,
  `barcode` varchar(50) NOT NULL DEFAULT '',
  `status` varchar(25) NOT NULL DEFAULT 'available',
  `pending` int(10) DEFAULT '-1',
  `pendingFor` varchar(25) DEFAULT '',
  `sessionid` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rid_bc` (`rid`,`barcode`),
  KEY `barcode` (`barcode`),
  KEY `status` (`status`(1)),
  KEY `index_1` (`sessionid`,`pending`,`pendingFor`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS eq_bcmRange (
  `rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vid` int(10) unsigned DEFAULT NULL,
  `bcLen` tinyint(4) DEFAULT NULL,
  `preFix` varchar(10) DEFAULT NULL,
  `rangeFr` int(10) unsigned DEFAULT NULL,
  `rangeTo` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`rid`),
  UNIQUE KEY `range` (`vid`,`preFix`,`bcLen`,`rangeFr`,`rangeTo`),
  KEY `vid` (`vid`),
  KEY `preFix` (`preFix`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS eq_bcmVendor (
  `vid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor` varchar(40) NOT NULL DEFAULT 'Local',
  PRIMARY KEY (`vid`),
  UNIQUE KEY `vendor` (`vendor`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS  `opl_database` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` enum('library','union') DEFAULT 'library',
  `host` varchar(255) NOT NULL DEFAULT 'window.location.host',
  `name` varchar(255) NOT NULL DEFAULT 'Local DB',
  `recommended` enum('true','false') NOT NULL DEFAULT 'false',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

alter table opl_requestReserve change status status enum('cancel','ready','reject','reserve','hold','waiting','loan') DEFAULT 'waiting';
alter table opl_requestReserve change status status enum('cancel','ready','reject','reserve','hold','waiting','loan') DEFAULT 'waiting';
