-- DROP TABLE IF EXISTS `sys_score`;
CREATE TABLE IF NOT EXISTS `sys_score` (
  `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT,
  `account` varchar(30) NOT NULL,
  `dept` mediumint(8) unsigned NOT NULL,
  `type` enum('common', 'share', 'bonus') NOT NULL DEFAULT 'common',
  `module` varchar(30) NOT NULL DEFAULT '',
  `method` varchar(30) NOT NULL,
  `rule` mediumint(8) unsigned NOT NULL,
  `actionDate` date NOT NULL,
  `desc` varchar(250) NOT NULL DEFAULT '',
  `before` int(11) unsigned NOT NULL DEFAULT '0',
  `score` int(11) NOT NULL DEFAULT '0',
  `after` int(11) unsigned NOT NULL DEFAULT '0',
  `giver` varchar(30) NOT NULL,
  `receiver` varchar(30) NOT NULL,
  `reviewer` varchar(30) NOT NULL,
  `time` datetime NOT NULL,
  `status` enum('normal','wait','rejected') NOT NULL DEFAULT 'normal',
  `rejectReason` varchar(250) NOT NULL DEFAULT '',
  `createdBy` varchar(30) NOT NULL,
  `createdDate` datetime NOT NULL,
  `editedBy` varchar(30) NOT NULL,
  `editedDate` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `account` (`account`),
  KEY `rule` (`rule`),
  KEY `module` (`module`),
  KEY `method` (`method`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS `sys_scorerule`;
CREATE TABLE IF NOT EXISTS `sys_scorerule` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(30) NOT NULL,
  `name` varchar(30) NOT NULL,
  `score` int(11) NOT NULL DEFAULT '0',
  `desc` varchar(250) NOT NULL DEFAULT '',
  `order` mediumint(8) unsigned NOT NULL,
  `buildin` enum('0', '1') NOT NULL DEFAULT '0',
  `createdBy` varchar(30) NOT NULL,
  `createdDate` datetime NOT NULL,
  `editedBy` varchar(30) NOT NULL,
  `editedDate` datetime NOT NULL,
  `deleted` enum('0', '1') NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS `sys_exchange`;
CREATE TABLE IF NOT EXISTS `sys_exchange` (
  `id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `giftID` mediumint(8) unsigned NOT NULL DEFAULT 0,
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `score` smallint(5) unsigned NOT NULL DEFAULT 0,
  `appliedBy` varchar(30) NOT NULL,
  `appliedDate` datetime NOT NULL,
  `grantedBy` datetime NOT NULL,
  `grantedDate` datetime NOT NULL,
  `canceledBy` datetime NOT NULL,
  `canceledDate` datetime NOT NULL,
  `finishedDate` datetime NOT NULL,
  `status` enum('normal', 'granted', 'canceled', 'finished') NOT NULL DEFAULT 'normal',
  `cancelReason` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `appliedBy` (`appliedBy`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `sys_scorerule` (`id`, `type`, `name`, `score`, `order`, `buildin`) VALUES
('1','system', '工龄积分', '50', '1', '1'),
('2','system', '全勤积分', '10',  '2', '1'),
('3','system', '绩效积分', '50', '3', '1'),
('4','system', '分享积分', '5',  '4', '1');

ALTER TABLE `cash_depositor` ADD `bank` varchar(10) NOT NULL AFTER `type`;
ALTER TABLE `cash_depositor` ADD `customerNo` varchar(20) NOT NULL AFTER `bank`;
ALTER TABLE `cash_depositor` ADD `clearingBankNo` varchar(30) NOT NULL AFTER `bankcode`;
ALTER TABLE `cash_depositor` CHANGE `bankcode` `unionBankNo` varchar(30) NOT NULL;

ALTER TABLE `sys_user` ADD `score` int(11) unsigned NOT NULL DEFAULT '0' AFTER `join`;
ALTER TABLE `sys_user` ADD `levelScore` int(11) unsigned NOT NULL DEFAULT '0' AFTER `score`;
ALTER TABLE `sys_user` ADD `giftScore` smallint(5) unsigned NOT NULL DEFAULT '0' AFTER `levelScore`;
ALTER TABLE `sys_user` ADD `lastService` datetime NOT NULL AFTER `giftScore`;
ALTER TABLE `sys_user` ADD `createdBy` char(30) NOT NULL AFTER `lastService`;
ALTER TABLE `sys_user` ADD `createdDate` datetime NOT NULL AFTER `createdBy`;
ALTER TABLE `sys_user` ADD `editedBy` char(30) NOT NULL AFTER `createdDate`;
ALTER TABLE `sys_user` ADD `editedDate` char(30) NOT NULL AFTER `editedBy`;

ALTER TABLE `sys_product` ADD `gift` enum('0', '1') NOT NULL DEFAULT '0' AFTER `subStatus`;
ALTER TABLE `sys_product` ADD `score` smallint(5) unsigned NOT NULL AFTER `gift`;
ALTER TABLE `sys_product` ADD `limit` smallint(5) unsigned NOT NULL AFTER `score`;
ALTER TABLE `sys_product` ADD `amount` decimal(15,2) NOT NULL AFTER `score`;

UPDATE `sys_user` SET `createdDate` = `join`;
UPDATE `sys_grouppriv` SET `method` = 'waitReview' where `module` = 'attend' and `method` = 'browseReview';

ALTER TABLE `sys_category` ADD `score` smallint(5) unsigned NOT NULL AFTER `refund`;

INSERT INTO `sys_cron` (`m`, `h`, `dom`, `mon`, `dow`, `command`, `remark`, `type`, `buildin`, `status`) VALUES
('5',  '0', '1',  '12', '*', 'appName=sys&moduleName=score&methodName=clearCommon',    '定时清零通用积分', 'zdoo', 1, 'normal'),
('15', '0', '1',  '12', '*', 'appName=sys&moduleName=score&methodName=giveBonus',      '定时发放绩效积分', 'zdoo', 1, 'normal'),
('20', '0', '1',  '12', '*', 'appName=sys&moduleName=score&methodName=giveService',    '定时发放工龄积分', 'zdoo', 1, 'normal'),
('10', '0', '1',  '*',  '*', 'appName=sys&moduleName=score&methodName=giveShare',      '定时发放分享积分', 'zdoo', 1, 'normal'),
('5',  '0', '10', '*',  '*', 'appName=sys&moduleName=score&methodName=giveAttendance', '定时发放全勤积分', 'zdoo', 1, 'normal');
