ALTER TABLE user ADD COLUMN   `initTime` DATETIME DEFAULT NULL COMMENT '初始化时间' AFTER `update_by` ;  -- 需要程序添加这个功能（取loginLog的第一次时间）
 ALTER TABLE security_person ADD COLUMN   `identityAuthTime` DATETIME DEFAULT NULL COMMENT '身份认证时间' AFTER `identityAuthState` ;  -- 需要程序添加这个功能（取loginLog的第一次时间）
-- 更新企业初始化时间

UPDATE user e,
(SELECT a.update_Time ,
        a.userName
  FROM  loginlog a ,(SELECT MIN(id) AS id,
                            username
                       FROM loginlog
                      WHERE type = '1'
                      GROUP BY username ) b
 where a.id = b.id ) f
  SET e.initTime = f.update_Time
WHERE e.init = '1'
  AND e.active = 1
  AND e.userName = f.userName

-- 更新人员实名认证时间

UPDATE security_person e,
(SELECT a.update_Time ,
        c.id
  FROM  loginlog a ,(SELECT MIN(id) AS id,
                            username
                       FROM loginlog
                      WHERE type = '1'
                      GROUP BY username ) b, user c
 WHERE a.id = b.id
   AND a.userName = c.userName
   AND c.active = 1 ) f
  SET e.identityAuthTime = f.update_Time
WHERE e.identityAuthState = '1'
  AND e.user_id = f.id ;
CREATE TABLE `config_calendar` (
  `years` int(4) DEFAULT NULL COMMENT '年度',
  `dates` date NOT NULL COMMENT '日期',
  `weekDay` int(1) DEFAULT NULL COMMENT '星期几',
  `whatWeek` int(1) DEFAULT NULL COMMENT '第几周',
  `isLegalHoliday` varchar(1) DEFAULT NULL COMMENT '是否法定假日',
  `holidayName` varchar(6) DEFAULT NULL COMMENT '节假日名称',
  PRIMARY KEY (`dates`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='日历' ;

INSERT INTO config_calendar(years,dates,weekDay,whatWeek)
SELECT  YEAR(m.rq) AS '年度',
        m.rq AS '日期',
       (CASE WHEN DAYOFWEEK(m.rq) = 1
             THEN 7
             ELSE DAYOFWEEK(m.rq)-1
             END ) AS '星期几',
       WEEKOFYEAR(m.rq) AS '第几周'
  FROM
(SELECT (@rn := DATE_ADD(@rn,INTERVAL 1 DAY)) AS rq
  FROM sysarea a ,(SELECT @rn :='2015-12-31') as b
 LIMIT 1461) m ;


 ALTER TABLE security_person ADD COLUMN  `leaveDate` date DEFAULT NULL COMMENT '离职日期' AFTER `incumbencyStatus`;
 ALTER TABLE sys_approval ADD INDEX index_sys_approval_create_time( `create_time` );
 ALTER TABLE sys_approval ADD INDEX index_sys_approval_approvalDate( `approvalDate` );
 ALTER TABLE sys_approval ADD INDEX index_sys_approval_areaCode( `areaCode` );
 ALTER TABLE sysarea ADD INDEX index_sysarea_code( `code` );
 ALTER TABLE sb_personcertificate ADD INDEX index_sb_personcertificate_create_time( `create_time` );
 ALTER TABLE security_company_person ADD INDEX index_security_company_person_create_time( `create_time` );
 ALTER TABLE user ADD INDEX index_user_create_time( `create_time` );
 ALTER TABLE sysarea ADD COLUMN areaLevel varchar(1) DEFAULT NULL COMMENT '区域级别(1:省;2:市;3:区)' AFTER `code` ;
 ALTER TABLE security_person ADD INDEX security_person_id_number( `id_number` );

UPDATE sysarea SET areaLevel = '1' WHERE parentId IS NULL  ;

UPDATE sysarea ,
 (SELECT id
    FROM sysarea
   WHERE areaLevel = '1'  ) m
   SET areaLevel = '2'
WHERE parentid = m.id ;



UPDATE sysarea ,
 (SELECT id
    FROM sysarea
   WHERE areaLevel = '2'  ) m
   SET areaLevel = '3'
WHERE parentid = m.id ;

-- 把在职状态为NULL的更新为0
UPDATE security_person
   SET incumbencyStatus = '0'
 WHERE incumbencyStatus IS NULL  ;

 -- 把不为在职的人员更新为离职
UPDATE security_person
  SET incumbencyStatus = '0'
WHERE id NOT IN (SELECT m.id
                  FROM (SELECT  c.id
                          FROM security_company a , security_company_person b  , security_person c ,  user d
                         WHERE a.id = b.company_id
                           AND c.id = b.person_id
                           AND c.user_id = d.id
                           AND a.active = 1
                           AND b.active = 1
                           AND d.active = 1)m )
  AND EXISTS (SELECT 1
                FROM user
               WHERE id = security_person.user_id
                 AND active = '1'  )   ;


UPDATE security_person
   SET leaveDate = DATE(update_time)
 WHERE incumbencyStatus = '0' ;