--
-- Table structure for table `tb_csvDuplicate`
--

CREATE TABLE `tb_csvDuplicate` (
  `mid` int(10) NOT NULL auto_increment,
  `iid` int(10) unsigned NOT NULL default '0',
  `content` mediumtext NOT NULL,
  PRIMARY KEY  (`mid`),
  KEY `i_iid` (`iid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

--
-- Table structure for table `tb_csvImport`
--

CREATE TABLE `tb_csvImport` (
  `iid` int(10) unsigned NOT NULL auto_increment,
  `sid` int(10) unsigned default '0',
  `lid` int(10) unsigned default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `sCode` varchar(50) default NULL,
  `lCode` varchar(50) default NULL,
  `itemType` varchar(50) default NULL,
  `filename` varchar(255) default NULL,
  `data` mediumtext,
  `header` mediumtext,
  `mapHeader` mediumtext,
  `dataDup` mediumtext,
  `cMerge` tinyint(1) unsigned default '1',
  `countImported` int(10) unsigned default '0',
  `countMerged` int(10) unsigned default '0',
  `countProcessed` int(10) unsigned default '0',
  `countTotal` int(10) unsigned NOT NULL default '0',
  `countTotalHolding` int(10) default NULL,
  `dateImport` datetime default NULL,
  `dateUpload` datetime NOT NULL default '0000-00-00 00:00:00',
  `mesgDigest` varchar(128) default NULL,
  `status` enum('uploaded','rejected','processed','accepted','done') default NULL,
  `addToNewitemList` tinyint(1) unsigned default '0',
  `newItemExpDate` datetime default NULL,
  PRIMARY KEY  (`iid`),
  KEY `i_uid` (`uid`),
  KEY `i_lid` (`lid`),
  KEY `i_msgDigest` (`mesgDigest`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

--
-- Table structure for table `tb_marcDuplicate`
--

CREATE TABLE `tb_marcDuplicate` (
  `mid` int(10) unsigned NOT NULL auto_increment,
  `iid` int(10) unsigned NOT NULL default '0',
  `content` mediumtext,
  PRIMARY KEY  (`mid`),
  KEY `i_iid` (`iid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

--
-- Table structure for table `tb_marcImport`
--

CREATE TABLE `tb_marcImport` (
  `iid` int(10) unsigned NOT NULL auto_increment,
  `sid` int(10) unsigned default '0',
  `lid` int(10) unsigned default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `encoding` enum('UTF-8','MARC-8') NOT NULL default 'UTF-8',
  `sCode` varchar(50) default NULL,
  `lCode` varchar(50) default NULL,
  `itemType` varchar(50) default NULL,
  `cMerge` tinyint(1) unsigned default '1',
  `countImported` int(10) unsigned default '0',
  `countMerged` int(10) unsigned default '0',
  `countProcessed` int(10) unsigned default '0',
  `countTotal` int(10) unsigned NOT NULL default '0',
  `dateImport` datetime default NULL,
  `dateUpload` datetime NOT NULL default '0000-00-00 00:00:00',
  `mesgDigest` varchar(128) default '',
  `status` enum('uploaded','rejected','accepted','indexing','done') NOT NULL default 'uploaded',
  `addToNewitemList` tinyint(1) unsigned default '0',
  `newItemExpDate` datetime default NULL,
  PRIMARY KEY  (`iid`),
  KEY `i_uid` (`uid`),
  KEY `i_lid` (`lid`),
  KEY `i_mesgDigest` (`mesgDigest`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


CREATE TABLE `opl_fineRate_tmp` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `itemType` varchar(100) default 0,
  `overDue` float(10,2) default 0,
  `maxOverdue` float(10,2) default 0 ,
  `damage` float(10,2) default 0,
  `toDel` tinyint(3) unsigned default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

insert into opl_fineRate_tmp(itemType,overDue,maxOverdue,damage) select id,0,0,0 from opl_itemType where id <>'';

update opl_fineRate_tmp set overDue=(select rate from opl_fineMaterials m inner join opl_fineRate r on r.mid=m.id where m.material='book' && fineType='overdue');
update opl_fineRate_tmp set maxOverDue=(select max from opl_fineMaterials m inner join opl_fineRate r on r.mid=m.id where m.material='book' && fineType='overdue');
update opl_fineRate_tmp set damage=(select rate from opl_fineMaterials m inner join opl_fineRate r on r.mid=m.id where m.material='book' && fineType='damaged');

update opl_fineRate_tmp r inner join opl_fineMaterials m on r.itemType=m.material inner join opl_fineRate b on b.mid=m.id set r.overdue=b.rate where b.fineType='overdue';
update opl_fineRate_tmp r inner join opl_fineMaterials m on r.itemType=m.material inner join opl_fineRate b on b.mid=m.id set r.maxOverdue=b.max where b.fineType='overdue';
update opl_fineRate_tmp r inner join opl_fineMaterials m on r.itemType=m.material inner join opl_fineRate b on b.mid=m.id set r.damage=b.rate where b.fineType='damaged';

update opl_fineRate_tmp set overDue =0 where overDue is null;
update opl_fineRate_tmp set maxOverdue =0 where maxOverdue is null;
update opl_fineRate_tmp set damage =0 where damage is null;

rename table opl_fineRate to opl_fineRate_bk;
rename table opl_fineRate_tmp to opl_fineRate;

update opl_fineRate r inner join opl_fineMaterials m on r.itemType=m.material inner join opl_fineRate_bk b on b.mid=m.id set r.overdue=b.rate where b.fineType='overdue';
update opl_fineRate r inner join opl_fineMaterials m on r.itemType=m.material inner join opl_fineRate_bk b on b.mid=m.id set r.maxOverdue=b.max where b.fineType='overdue';
update opl_fineRate r inner join opl_fineMaterials m on r.itemType=m.material inner join opl_fineRate_bk b on b.mid=m.id set r.damage=b.rate where b.fineType='damaged';

