-- 
-- MosP - Mind Open Source Project    http://www.mosp.jp/
-- Copyright (C) MIND Co., Ltd.       http://www.e-mind.co.jp/
-- 
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License
-- as published by the Free Software Foundation; either version 2
-- 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 General Public License for more details.
-- 
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
-- 
DROP TABLE IF EXISTS `CT_HOLIDAYREQUEST`;
DROP TABLE IF EXISTS `PM_HOLIDAYREQUEST`;

DROP TABLE IF EXISTS `CT_HOLIDAY_REQUEST`;
DROP TABLE IF EXISTS `CT_TIMECARD`;

DROP TABLE IF EXISTS `CM_AGREEMENT_OVER_TIME`;
CREATE TABLE IF NOT EXISTS `CM_AGREEMENT_OVER_TIME` (
  `OVER_TIME_ID` int(10) unsigned zerofill NOT NULL default '0000000000' COMMENT '時間外協定ID',
  `OVER_TIME_CODE` varchar(10) NOT NULL default '' COMMENT '時間外協定コード',
  `WORK_TYPE_NAME` varchar(50) NOT NULL COMMENT '業務の種類',
  `CAUSE` text NOT NULL COMMENT '具体的事由',
  `AGREE_TYPE` varchar(10) NOT NULL default '' COMMENT '協定種類',
  `LIMIT_CODE` varchar(10) NOT NULL default '' COMMENT '限度基準コード',
  `OVER_TIME_RATE` int(10) unsigned NOT NULL default '0' COMMENT '時間外割増賃金率',
  `CAUTION_MONTH` int(10) unsigned NOT NULL default '0' COMMENT '月60時間時間外警告時間',
  `MONTHLY_OVER_TIME_RATE` int(10) unsigned NOT NULL default '0' COMMENT '月60時間時間外割増賃金率',
  `SUB_ACQUIRE_RATE` int(10) unsigned NOT NULL default '0' COMMENT '代替休暇取得割増賃金率',
  `SUB_ACQUIRE_HOLIDAY_RATE` int(10) unsigned NOT NULL default '0' COMMENT '代替休暇取得休日割増賃金率',
  `SUB_QUIT_RATE` int(10) unsigned NOT NULL default '0' COMMENT '代替休暇放棄割増賃金率',
  `START_DATE` date NOT NULL COMMENT '開始年月日',
  `DELETE_FLAG` int(10) unsigned NOT NULL default '0' COMMENT '削除フラグ',
  `INSERT_DATE` datetime NOT NULL COMMENT '作成日',
  `INSERT_USER` varchar(10) NOT NULL default '' COMMENT '作成者',
  `UPDATE_DATE` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新日',
  `UPDATE_USER` varchar(10) NOT NULL default '' COMMENT '更新者',
  PRIMARY KEY  (`OVER_TIME_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932 COMMENT='時間外協定マスタ';

DROP TABLE IF EXISTS `CM_AGREEMENT_WORK_ON`;
CREATE TABLE IF NOT EXISTS `CM_AGREEMENT_WORK_ON` (
  `WORK_ON_ID` int(10) unsigned zerofill NOT NULL default '0000000000' COMMENT '休出協定ID',
  `WORK_ON_CODE` varchar(10) NOT NULL default '' COMMENT '休出協定コード',
  `WORK_TYPE_NAME` varchar(50) NOT NULL COMMENT '業務の種類',
  `CAUSE` text NOT NULL COMMENT '具体的事由',
  `STANDARD_WEEK_HOLIDAY` varchar(14) NOT NULL COMMENT '所定休日',
  `OFFICIAL_HOLIDAY` varchar(1) NOT NULL COMMENT '法定休日',
  `JAPANESE_HOLIDAY_FLAG` varchar(1) NOT NULL COMMENT '国民の祝日フラグ',
  `HOLIDAY_MASTER_FLAG` varchar(1) NOT NULL default '' COMMENT '祝祭日マスタフラグ',
  `STANDARD_HOLIDAY_RATE` int(10) unsigned NOT NULL default '0' COMMENT '所定休日割増賃金率',
  `OFFICIAL_HOLIDAY_RATE` int(10) unsigned NOT NULL default '0' COMMENT '法定休日割増賃金率',
  `NUMBER_OF_WORK_ON` int(10) unsigned NOT NULL default '0' COMMENT '休出日数',
  `START_TIME` int(10) unsigned NOT NULL default '0' COMMENT '開始時刻',
  `END_TIME` int(10) unsigned NOT NULL default '0' COMMENT '終了時刻',
  `START_DATE` date NOT NULL COMMENT '開始年月日',
  `DELETE_FLAG` int(10) unsigned NOT NULL default '0' COMMENT '削除フラグ',
  `INSERT_DATE` datetime NOT NULL COMMENT '作成日',
  `INSERT_USER` varchar(10) NOT NULL default '' COMMENT '作成者',
  `UPDATE_DATE` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新日',
  `UPDATE_USER` varchar(10) NOT NULL default '' COMMENT '更新者',
  PRIMARY KEY  (`WORK_ON_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932 COMMENT='休出協定マスタ';

DROP TABLE IF EXISTS `CM_LABOR_AGREEMENT`;
CREATE TABLE IF NOT EXISTS `CM_LABOR_AGREEMENT` (
  `AGREE_CODE` varchar(10) NOT NULL default '' COMMENT '協定コード',
  `AGREE_TYPE` varchar(15) NOT NULL default '' COMMENT '協定種類',
  `OVER_TIME_CODE` varchar(10) NOT NULL default '' COMMENT '時間外協定コード',
  `WORK_ON_CODE` varchar(10) NOT NULL default '' COMMENT '休出協定コード',
  `DELETE_FLAG` int(10) unsigned NOT NULL default '0' COMMENT '削除フラグ',
  `INSERT_DATE` datetime NOT NULL COMMENT '作成日',
  `INSERT_USER` varchar(10) NOT NULL default '' COMMENT '作成者',
  `UPDATE_DATE` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新日',
  `UPDATE_USER` varchar(10) NOT NULL default '' COMMENT '更新者',
  PRIMARY KEY  (`AGREE_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932 COMMENT='労使協定マスタ';

DROP TABLE IF EXISTS `CM_LIMIT_STANDARD`;
CREATE TABLE IF NOT EXISTS `CM_LIMIT_STANDARD` (
  `LIMIT_CODE` varchar(10) NOT NULL default '' COMMENT '限度基準コード',
  `LIMIT_NAME` varchar(50) NOT NULL default '' COMMENT '限度基準名称',
  `TERM_TYPE` varchar(10) NOT NULL default '' COMMENT '期間の種類',
  `PRESCRIBED_TIME` int(10) unsigned NOT NULL default '0' COMMENT '所定労働時間',
  `CAUTION_DAY` int(10) unsigned NOT NULL default '0' COMMENT '1日の警告時間',
  `CAUTION_TERM` int(10) unsigned NOT NULL default '0' COMMENT '指定期間の警告時間',
  `CAUTION_YEAR` int(10) unsigned NOT NULL default '0' COMMENT '1年の警告時間',
  `LIMIT_DAY` int(10) unsigned NOT NULL default '0' COMMENT '一日の限度時間',
  `LIMIT_TERM` int(10) unsigned NOT NULL default '0' COMMENT '指定期間の限度時間',
  `LIMIT_YEAR` int(10) unsigned NOT NULL default '0' COMMENT '1年の限度時間',
  `RATE_TERM` int(10) unsigned NOT NULL default '0' COMMENT '指定期間の割増率',
  `RATE_YEAR` int(10) unsigned NOT NULL default '0' COMMENT '1年の割増率',
  `DELETE_FLAG` int(10) unsigned NOT NULL default '0' COMMENT '削除フラグ',
  `INSERT_DATE` datetime NOT NULL COMMENT '作成日',
  `INSERT_USER` varchar(10) NOT NULL default '' COMMENT '作成者',
  `UPDATE_DATE` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新日',
  `UPDATE_USER` varchar(10) NOT NULL default '' COMMENT '更新者',
  PRIMARY KEY  (`LIMIT_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932 COMMENT='限度基準マスタ';

DROP TABLE IF EXISTS `KD_PAID_HOLIDAY`;
CREATE TABLE IF NOT EXISTS `KD_PAID_HOLIDAY` (
  `PAID_ID` bigint(20) unsigned zerofill NOT NULL default '00000000000000000000' COMMENT '有休ID',
  `K_CODE` varchar(10) NOT NULL default '' COMMENT '社員コード',
  `PROCESS_TYPE` varchar(10) NOT NULL default '' COMMENT '処理コード',
  `PROCESS_DATE` date NOT NULL COMMENT '支給日',
  `LAST_REMAIN_DAYS` double unsigned NOT NULL default '0' COMMENT '前年度残日数',
  `LAST_REMAIN_HOUR` int(10) unsigned NOT NULL default '0' COMMENT '前年度残時間',
  `REMAIN_DAYS` double unsigned NOT NULL default '0' COMMENT '今年度残日数',
  `REMAIN_HOUR` int(10) unsigned NOT NULL default '0' COMMENT '今年度残時間',
  `POSSIBLE` int(10) unsigned NOT NULL default '0' COMMENT '時間単位取得可能日数',
  `DELETE_FLAG` int(10) unsigned NOT NULL default '0' COMMENT '削除フラグ',
  `INSERT_DATE` datetime NOT NULL COMMENT '作成日',
  `INSERT_USER` varchar(10) NOT NULL default '' COMMENT '作成者',
  `UPDATE_DATE` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新日',
  `UPDATE_USER` varchar(10) NOT NULL default '' COMMENT '更新者',
  PRIMARY KEY  (`PAID_ID`,`K_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932 COMMENT='有休管理テーブル';

DROP TABLE IF EXISTS `KD_SUBSTITUTE_HOLIDAY`;
CREATE TABLE IF NOT EXISTS `KD_SUBSTITUTE_HOLIDAY` (
  `SUBSTITUTE_ID` bigint(20) unsigned zerofill NOT NULL default '00000000000000000000' COMMENT '管理ID',
  `K_CODE` varchar(10) NOT NULL COMMENT '社員コード',
  `OCCUR_YEAR` int(10) unsigned NOT NULL default '0' COMMENT '発生年',
  `OCCUR_MONTH` int(10) unsigned NOT NULL default '0' COMMENT '発生月',
  `CLOSE_DATE` date NOT NULL COMMENT '締切日',
  `LIMIT_START_DATE` date NOT NULL COMMENT '期限開始日',
  `LIMIT_END_DATE` date NOT NULL COMMENT '期限終了日',
  `ATTITUDE_STATUS` varchar(10) NOT NULL COMMENT '意向状況',
  `PAYMENT_ACQUIRE_TIME` int(10) unsigned NOT NULL default '0' COMMENT '代替休暇取得時間外',
  `PAYMENT_ACQUIRE_HOLIDAY` int(10) unsigned NOT NULL default '0' COMMENT '代替休暇取得休日時間外',
  `PAYMENT_QUIT_TIME` int(10) unsigned NOT NULL default '0' COMMENT '代替休暇放棄時間外',
  `USE_HOLIDAY_TIME` int(10) unsigned NOT NULL default '0' COMMENT '利用時間',
  `ACQUISITION_STATUS` varchar(10) NOT NULL COMMENT '取得状況',
  `LIQUIDATION_OVER_TIME` int(11) NOT NULL default '0' COMMENT '代休決済による時間外清算時間',
  `LIQUIDATION_WEEK_HOLIDAY` int(11) NOT NULL default '0' COMMENT '代休決済による休日時間外清算時間',
  `DECISION_STATUS` varchar(10) NOT NULL COMMENT '決済状況',
  `DELETE_FLAG` int(10) unsigned NOT NULL default '0' COMMENT '削除フラグ',
  `INSERT_DATE` datetime NOT NULL COMMENT '作成日',
  `INSERT_USER` varchar(10) NOT NULL default '' COMMENT '作成者',
  `UPDATE_DATE` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新日',
  `UPDATE_USER` varchar(10) NOT NULL default '' COMMENT '更新者',
  PRIMARY KEY  (`SUBSTITUTE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932 COMMENT='代替休暇管理テーブル';

DROP TABLE IF EXISTS `KD_WORK`;
CREATE TABLE IF NOT EXISTS `KD_WORK` (
  `K_CODE` varchar(10) NOT NULL COMMENT '社員コード',
  `SIKYU_YM` varchar(8) NOT NULL COMMENT '支給年月',
  `S_NEN` int(4) NOT NULL default '0' COMMENT '支給年',
  `S_TUKI` int(2) NOT NULL default '0' COMMENT '支給月',
  `S_HI` int(2) NOT NULL default '0' COMMENT '支給日',
  `S_NISSU` double NOT NULL default '0' COMMENT '出勤日数',
  `S_KAISU` int(4) NOT NULL default '0' COMMENT '出勤回数',
  `KINMU_JI` int(4) NOT NULL default '0' COMMENT '勤務時間',
  `KYUKEI_JI` int(4) NOT NULL default '0' COMMENT '休憩時間',
  `YUKYU` double NOT NULL default '0' COMMENT '有給',
  `TOKUKYU` double NOT NULL default '0' COMMENT '特別休暇',
  `KEKKIN` double NOT NULL default '0' COMMENT '一般欠勤日数',
  `S_KEKKIN` double NOT NULL default '0' COMMENT '傷病欠勤日数',
  `DAIKYU` double NOT NULL default '0' COMMENT '代替休暇',
  `FURIKAE` double NOT NULL default '0' COMMENT '振替休暇',
  `KK_RIYUU_6` double NOT NULL default '0' COMMENT '休暇理由6',
  `KK_RIYUU_7` double NOT NULL default '0' COMMENT '休暇理由7',
  `KK_RIYUU_8` double NOT NULL default '0' COMMENT '休暇理由8',
  `KK_RIYUU_9` double NOT NULL default '0' COMMENT '休暇理由9',
  `SONOTA` double NOT NULL default '0' COMMENT 'その他休暇',
  `TI_SO_KAI` int(4) NOT NULL default '0' COMMENT '遅刻早退回数',
  `TI_SO_JIKAN` int(4) NOT NULL default '0' COMMENT '遅刻早退時間',
  `ZA_JIKAN` int(4) NOT NULL default '0' COMMENT '普通残業時間',
  `KS_JIKAN` int(4) NOT NULL default '0' COMMENT '休日出勤時間',
  `SN_JIKAN` int(4) NOT NULL default '0' COMMENT '深夜勤務時間',
  `KAIKIN` int(1) NOT NULL default '0' COMMENT '精皆勤手当支給',
  `SYOKUJI_KAISU` int(4) NOT NULL default '0' COMMENT '食事手当支給回数',
  `CYOKU_KAISU_1` int(4) NOT NULL default '0' COMMENT '1直手当支給回数',
  `CYOKU_KAISU_2` int(4) NOT NULL default '0' COMMENT '2直手当支給回数',
  `CYOKU_KAISU_3` int(4) NOT NULL default '0' COMMENT '3直手当支給回数',
  `HOKA_KAISU_1` int(4) NOT NULL default '0' COMMENT 'その他支給回数1',
  `HOKA_KAISU_2` int(4) NOT NULL default '0' COMMENT 'その他支給回数2',
  `HOKA_KAISU_3` int(4) NOT NULL default '0' COMMENT 'その他支給回数3',
  `HOKA_KAISU_4` int(4) NOT NULL default '0' COMMENT 'その他支給回数4',
  `HOKA_KAISU_5` int(4) NOT NULL default '0' COMMENT 'その他支給回数5',
  `SINSEI_GAKU` int(8) NOT NULL default '0' COMMENT '支払申請額',
  `WARIMASI_1` int(11) NOT NULL default '0' COMMENT '1.00割増',
  `WEEK_DAY_OVER_TIME` int(11) NOT NULL default '0' COMMENT '平日時間外',
  `WEEK_HOLIDAY_OVER_TIME` int(11) NOT NULL default '0' COMMENT '所定休日時間外',
  `MONTHLY_OVER_TIME` int(11) NOT NULL default '0' COMMENT '月60時間超過平日時間外',
  `MONTHLY_WEEK_HOLIDAY` int(11) NOT NULL default '0' COMMENT '月60時間超過所定休日時間外',
  `HOUR_HOLIDAY` int(11) NOT NULL default '0' COMMENT '時間単位年休取得時間',
  `SUBSTITUTE_HOLIDAY` double NOT NULL default '0' COMMENT '時間外時間代替休暇取得日数',
  `AMEND_FLAG` int(11) NOT NULL default '0' COMMENT '修正フラグ',
  `INSERT_DATE` datetime NOT NULL COMMENT '作成日',
  `INSERT_USER` varchar(10) NOT NULL default '' COMMENT '作成者',
  `UPDATE_DATE` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新日',
  `UPDATE_USER` varchar(10) NOT NULL default '' COMMENT '更新者',
  PRIMARY KEY  (`K_CODE`,`SIKYU_YM`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932 COMMENT='勤怠集計';

DROP TABLE IF EXISTS `KT_HOLIDAY_REQUEST`;
CREATE TABLE IF NOT EXISTS `KT_HOLIDAY_REQUEST` (
  `VOUCHER` int(10) unsigned zerofill NOT NULL COMMENT '休暇申請伝票番号',
  `WORKFLOW` bigint(20) unsigned zerofill NOT NULL COMMENT 'ワークフロー番号',
  `K_CODE` varchar(10) NOT NULL COMMENT '社員コード',
  `HOLIDAY_DATE` date NOT NULL COMMENT '休暇日',
  `START_TIME` int(11) NOT NULL COMMENT '休暇開始時間',
  `END_TIME` int(11) NOT NULL COMMENT '休暇終了時間',
  `REASON` varchar(10) NOT NULL COMMENT '休暇理由',
  `HOLIDAY_TYPE` varchar(10) NOT NULL COMMENT '有給区分',
  `OCCUR_DATE_1` varchar(10) NOT NULL default '' COMMENT '発生年月日1',
  `USE_TIME_1` int(11) NOT NULL default '0' COMMENT '利用時間1',
  `OCCUR_DATE_2` varchar(10) NOT NULL default '' COMMENT '発生年月日2',
  `USE_TIME_2` int(11) NOT NULL default '0' COMMENT '利用時間2',
  `SUBSTITUTE_DATE` varchar(10) NOT NULL COMMENT '充当日',
  `CAUSE_COMMENT` text NOT NULL COMMENT '事由',
  `DELETE_FLAG` int(10) unsigned NOT NULL default '0' COMMENT '削除フラグ',
  `INSERT_DATE` datetime NOT NULL COMMENT '作成日',
  `INSERT_USER` varchar(10) NOT NULL default '' COMMENT '作成者',
  `UPDATE_DATE` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新日',
  `UPDATE_USER` varchar(10) NOT NULL default '' COMMENT '更新者',
  PRIMARY KEY  (`VOUCHER`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932 COMMENT='休暇申請テーブル';

DELETE FROM `M_CODE` WHERE 

TID ='給与支給名' OR 
TID ='給与控除名' OR 
TID ='賞与支給名' OR 
TID ='賞与控除名'
;

INSERT INTO `M_CODE` VALUES
('限度基準期間', '0', '1週間', '1,week', NOW(), 'admin', NOW(), 'admin'),
('限度基準期間', '1', '2週間', '2,week', NOW(), 'admin', NOW(), 'admin'),
('限度基準期間', '2', '4週間', '4,week', NOW(), 'admin', NOW(), 'admin'),
('限度基準期間', '3', '1ヶ月', '1,month', NOW(), 'admin', NOW(), 'admin'),
('限度基準期間', '4', '2ヶ月', '2,month', NOW(), 'admin', NOW(), 'admin'),
('限度基準期間', '5', '3ヶ月', '3,month', NOW(), 'admin', NOW(), 'admin'),

('協定種類', '0', '１年単位の変形労働時間制に該当しない労働者', '', NOW(), 'admin', NOW(), 'admin'),
('協定種類', '1', '１年単位の変形労働時間制により労働する労働者', '', NOW(), 'admin', NOW(), 'admin'),
('協定種類', '2', '育児・介護休業法上の時間外労働の制限を請求した労働者', '', NOW(), 'admin', NOW(), 'admin'),

('勤怠管理', '丸め処理', '', '0', NOW(), 'admin', NOW(), 'admin'),
('勤怠管理', '計算処理', '', '0', NOW(), 'admin', NOW(), 'admin'),
('勤怠管理', '月60時間時間外機能', '', '1', NOW(), 'admin', NOW(), 'admin'),
('勤怠管理', '代休情報修正', '', '1', NOW(), 'admin', NOW(), 'admin'),
('勤怠管理', '代替休暇機能', '', '1', NOW(), 'admin', NOW(), 'admin'),

('有給休暇', '時間単位年休', '', '1', NOW(), 'admin', NOW(), 'admin'),
('有給休暇', '年休単位時間', '', '', NOW(), 'admin', NOW(), 'admin'),

('承認理由', '5', '下書き', '', NOW(), 'admin', NOW(), 'admin')

;
