-- Project Name : noname
-- Date/Time    : 2020/08/17 9:32:32
-- Author       : Horiuchi
-- RDBMS Type   : MySQL
-- Application  : A5:SQL Mk-2

/*
  BackupToTempTable, RestoreFromTempTable疑似命令が付加されています。
  これにより、drop table, create table 後もデータが残ります。
  この機能は一時的に $$TableName のような一時テーブルを作成します。
*/

-- 担当者行動予定データ
--* BackupToTempTable
drop table if exists actionscheduledata cascade;

--* RestoreFromTempTable
create table actionscheduledata (
  TargetDate date not null comment '対象日'
  , PersonCode decimal(8,0) not null comment '担当者コード'
  , ActionSchedule varchar(30) comment '行動予定'
  , TargetYear year(4) not null comment '該当年'
  , TargetMonth year(4) not null comment '該当月'
  , TargetDay year(4) not null comment '該当日'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint actionscheduledata_PKC primary key (TargetDate,PersonCode)
) comment '担当者行動予定データ' ;

create index ActionScheduleData_Index1
  on actionscheduledata(TargetYear);

create index ActionScheduleData_Index2
  on actionscheduledata(TargetMonth);

create index ActionScheduleData_Index3
  on actionscheduledata(TargetDay);

-- 承認経路マスタ
--* BackupToTempTable
drop table if exists approvalmaster cascade;

--* RestoreFromTempTable
create table approvalmaster (
  ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
  , DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
  , SeqNo decimal(2,0) not null comment '枝番'
  , DisplayOrder decimal(2,0) not null comment '表示順'
  , ApprovalPerson decimal(8,0) unsigned not null comment '承認者コード'
  , ApprovalAuthority decimal(1,0) not null comment '承認権限区分'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint approvalmaster_PKC primary key (ApprovalCode,DepartmentCode,SeqNo)
) comment '承認経路マスタ' ;

-- 出勤日報データ
--* BackupToTempTable
drop table if exists attendancedailydata cascade;

--* RestoreFromTempTable
create table attendancedailydata (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , AttendanceDate date not null comment '日報作成日'
  , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
  , ConstructionCode decimal(10,0) not null comment '工事コード'
  , ActionResult varchar(100) not null comment '行動実績'
  , StartTime datetime comment '開始時間'
  , CompTime datetime comment '終了時間'
  , DayTimes decimal(5,2) not null comment '稼働時間'
  , Overtime decimal(5,2) not null comment '時間外就業'
  , OvertimeWA decimal(5,2) not null comment '残業認可時間'
  , WorkingComment varchar(120) comment 'コメント'
  , WorkKindFlg decimal(2,0) not null comment '作業区分フラグ'
  , WorkingCode decimal(3,0) unsigned not null comment '就業コード'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint attendancedailydata_PKC primary key (PersonCode,AttendanceDate,SeqNo)
) comment '出勤日報データ' ;

create index AttendDaily_Index1
  on attendancedailydata(ConstructionCode);

create index AttendDaily_Index2
  on attendancedailydata(AttendanceDate);

-- 出勤管理データ
--* BackupToTempTable
drop table if exists attendancedata cascade;

--* RestoreFromTempTable
create table attendancedata (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , AttendanceMonth decimal(6,0) unsigned not null comment '出勤管理年月'
  , LastDays decimal(2,0) unsigned not null comment '当月最終日'
  , AttendDayCount decimal(2,0) unsigned not null comment '出勤日数'
  , NormalTimes decimal(5,2) not null comment '通常勤務時間数'
  , OutEarlyTimes decimal(5,2) not null comment '早出時間数'
  , OverTimes decimal(5,2) not null comment '残業時間数'
  , HolidayWorkTimes decimal(5,2) not null comment '休日出勤時間数'
  , TransferHolidayTimes decimal(5,2) not null comment '振替休日回数'
  , CompensatoryHolidayTimes decimal(5,2) not null comment '代休回数'
  , PaidHolidaysTimes decimal(5,2) not null comment '有給休暇回数'
  , AbsenceTimes decimal(5,2) not null comment '欠勤時間数'
  , LateTimes decimal(5,2) not null comment '遅刻時間数'
  , LeaveEarlyTimes decimal(5,2) not null comment '早退時間数'
  , GoOutTimes decimal(5,2) not null comment '外出時間数'
  , OrderDate decimal(6,0) unsigned not null comment '申請日'
  , OrderNo decimal(2,0) unsigned not null comment '申請受付番号'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint attendancedata_PKC primary key (PersonCode,AttendanceMonth)
) comment '出勤管理データ' ;

-- 勤怠届出データ
--* BackupToTempTable
drop table if exists attendancenotice cascade;

--* RestoreFromTempTable
create table attendancenotice (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , AttendanceNo decimal(2,0) unsigned not null comment '勤怠区分'
  , AttendanceMonth decimal(6,0) unsigned not null comment '出勤管理年月'
  , OrderNo decimal(3,0) unsigned not null comment '届番号'
  , DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
  , HolidayReason decimal(1,0) unsigned not null comment '休み区分'
  , FromDate datetime not null comment '開始日時'
  , ToDate datetime not null comment '終了日時'
  , NoticePeriod decimal(5,2) unsigned not null comment '勤怠期間日'
  , TransferDay datetime not null comment '振替日'
  , Reason varchar(300) comment '事由'
  , Note varchar(300) comment '備考'
  , ApprovalDate decimal(6,0) unsigned not null comment '承認日'
  , ApprovalNo decimal(2,0) unsigned not null comment '承認受付番号'
  , WorkingCode decimal(3,0) unsigned not null comment '就業コード'
  , DeleteFlg decimal(1,0) unsigned not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint attendancenotice_PKC primary key (PersonCode,AttendanceNo,AttendanceMonth,OrderNo)
) comment '勤怠届出データ' ;

create index AttendanceNotice_Index1
  on attendancenotice(PersonCode);

create index AttendanceNotice_Index2
  on attendancenotice(AttendanceNo);

create index AttendanceNotice_Index3
  on attendancenotice(AttendanceMonth);

-- 勤怠届出紐付けデータ
--* BackupToTempTable
drop table if exists attendancenoticelink cascade;

--* RestoreFromTempTable
create table attendancenoticelink (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , AttendanceNo decimal(2,0) unsigned not null comment '勤怠区分'
  , AttendanceMonth decimal(6,0) unsigned not null comment '出勤管理年月'
  , OrderNo decimal(3,0) unsigned not null comment '届番号'
  , DailyDate date not null comment '日報作成日'
  , SeqNo decimal(3,0) unsigned not null comment '日報明細行番号'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint attendancenoticelink_PKC primary key (PersonCode,AttendanceNo,AttendanceMonth,OrderNo,DailyDate,SeqNo)
) comment '勤怠届出紐付けデータ' ;

create index AttendanceNoticeLink_Index1
  on attendancenoticelink(PersonCode);

create index AttendanceNoticeLink_Index2
  on attendancenoticelink(AttendanceNo);

create index AttendanceNoticeLink_Index3
  on attendancenoticelink(AttendanceMonth);

create index AttendanceNoticeLink_Index4
  on attendancenoticelink(OrderNo);

create index AttendanceNoticeLink_Index5
  on attendancenoticelink(DailyDate);

create index AttendanceNoticeLink_Index6
  on attendancenoticelink(SeqNo);

-- 勤怠届出工事データ
--* BackupToTempTable
drop table if exists attnoticeconstrction cascade;

--* RestoreFromTempTable
create table attnoticeconstrction (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , AttendanceNo decimal(2,0) unsigned not null comment '勤怠区分'
  , AttendanceMonth decimal(6,0) unsigned not null comment '出勤管理年月'
  , OrderNo decimal(3,0) unsigned not null comment '届番号'
  , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ConstructionName varchar(300) comment '工事名称'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint attnoticeconstrction_PKC primary key (PersonCode,AttendanceNo,AttendanceMonth,OrderNo)
) comment '勤怠届出工事データ' ;

-- 有給休暇消化紐付データ
--* BackupToTempTable
drop table if exists attnpaidholidyalink cascade;

--* RestoreFromTempTable
create table attnpaidholidyalink (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , AttendanceNo decimal(2,0) unsigned not null comment '勤怠区分'
  , AttendanceMonth decimal(6,0) unsigned not null comment '出勤管理年月'
  , OrderNo decimal(3,0) unsigned not null comment '届番号'
  , PreviousYear decimal(3,1) not null comment '前年度有給取得数'
  , CurrentYear decimal(3,1) not null comment '今年度有給取得数'
  , Completed decimal(1,0) unsigned not null comment '削除時戻し済フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint attnpaidholidyalink_PKC primary key (PersonCode,AttendanceNo,AttendanceMonth,OrderNo)
) comment '有給休暇消化紐付データ' ;

-- 銀行営業日マスタ
--* BackupToTempTable
drop table if exists bankbusinessdaymaster cascade;

--* RestoreFromTempTable
create table bankbusinessdaymaster (
  YEARMONTH decimal(6,0) default '0' not null comment '年月'
  , BANKBUSINESSDAY decimal(2,0) default '0' not null comment '銀行営業日'
  , NOTE varchar(120) comment '備考'
  , DELETEFLG decimal(1,0) comment '削除フラグ'
  , ENTRYDATE datetime comment '登録年月日'
  , UPDATEDATE datetime comment '更新年月日'
  , constraint bankbusinessdaymaster_PKC primary key (YEARMONTH,BANKBUSINESSDAY)
) comment '銀行営業日マスタ' ;

-- 工事詳細台帳作成前データ
--* BackupToTempTable
drop table if exists beforecreatingledger cascade;

--* RestoreFromTempTable
create table beforecreatingledger (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
  , TargetMonth decimal(6,0) unsigned not null comment '対象年月'
  , PaymentAmount decimal(12,0) not null comment '請求金額'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint beforecreatingledger_PKC primary key (ConstructionCode,GroupCount,TargetMonth)
) comment '工事詳細台帳作成前データ' ;

-- 業者請求部署出納リンクデータ
--* BackupToTempTable
drop table if exists billingcostdatalink cascade;

--* RestoreFromTempTable
create table billingcostdatalink (
  CompanyCode decimal(8,0) default '0' not null comment '協力会社コード'
  , TargetDate decimal(6,0) default '0' not null comment '対象年月'
  , SeqNoB decimal(3,0) default '0' not null comment '連番'
  , LineCount decimal(3,0) default '0' not null comment '行番号'
  , LabourKind decimal(1,0) not null comment '支払い通常・手間区分'
  , LinkType decimal(1,0) not null comment 'リンク先データタイプ'
  , YearNumber smallint(5) unsigned not null comment '年度'
  , CostType decimal(1,0) not null comment '出納タイプフラグ'
  , TargetType decimal(1,0) not null comment '対象先コードタイプ'
  , TargetCode decimal(10,0) not null comment '対象先コード'
  , SeqNoC decimal(3,0) not null comment 'データ番号'
  , PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , ActionDate date not null comment '対象日付'
  , DataType decimal(1,0) not null comment 'データ区分'
  , DataAddCount decimal(5,0) unsigned not null comment 'データ追加番号'
  , EntryDate datetime comment '登録年月日'
  , UpdateDate datetime comment '更新年月日'
  , constraint billingcostdatalink_PKC primary key (CompanyCode,TargetDate,SeqNoB,LineCount,LabourKind)
) comment '業者請求部署出納リンクデータ' ;

create index BillingCostDataLink_Index1
  on billingcostdatalink(YearNumber,CostType,TargetType,TargetCode,SeqNoC);

create index BillingCostDataLink_Index2
  on billingcostdatalink(PersonCode,ActionDate,DataType,DataAddCount);

-- 業者請求データ
--* BackupToTempTable
drop table if exists billingdata cascade;

--* RestoreFromTempTable
create table billingdata (
  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'
  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'
  , SEQNO decimal(3,0) default '0' not null comment '連番'
  , BILLPRICE decimal(10,0) comment '請求金額'
  , ENTRYDATE datetime comment '登録年月日'
  , UPDATEDATE datetime comment '更新年月日'
  , constraint billingdata_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO)
) comment '業者請求データ' ;

-- 業者請求明細データ
--* BackupToTempTable
drop table if exists billingdatadetail cascade;

--* RestoreFromTempTable
create table billingdatadetail (
  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'
  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'
  , SEQNO decimal(3,0) default '0' not null comment '連番'
  , LINECOUNT decimal(3,0) default '0' not null comment '行番号'
  , LABOURKIND decimal(1,0) not null comment '支払い通常・手間区分'
  , CONSTRUCTIONCODE decimal(10,0) comment '工事番号'
  , CONSTRUCTIONROWCNT decimal(3,0) default '0' not null comment '台帳明細番号'
  , CONSTRUCTIONCOLCNT decimal(3,0) default '0' not null comment '台帳列番号'
  , LinkDataType decimal(1,0) not null comment '関連付けデータタイプ'
  , FIELDNAME varchar(120) comment '現場名'
  , BILLPRICE decimal(10,0) comment '請求金額'
  , HIGHWPRICE decimal(10,0) comment '高速代'
  , HARDWPRICE decimal(10,0) comment '金物代'
  , INDSWASTETAX decimal(10,0) comment '産廃税'
  , OverFlg decimal(1,0) comment '支払超過フラグ'
  , NOTE varchar(120) comment '備考'
  , DELETEFLG decimal(1,0) comment '削除フラグ'
  , ENTRYDATE datetime comment '登録年月日'
  , UPDATEDATE datetime comment '更新年月日'
  , constraint billingdatadetail_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO,LINECOUNT,LABOURKIND)
) comment '業者請求明細データ' ;

create index billingdatadetail_Index1
  on billingdatadetail(COMPANYCODE);

create index billingdatadetail_Index2
  on billingdatadetail(TARGETDATE);

create index billingdatadetail_Index3
  on billingdatadetail(SEQNO);

create index billingdatadetail_Index4
  on billingdatadetail(LINECOUNT);

create index billingdatadetail_Index5
  on billingdatadetail(LABOURKIND);

create index billingdatadetail_Index6
  on billingdatadetail(CONSTRUCTIONCODE);

create index billingdatadetail_Index7
  on billingdatadetail(CONSTRUCTIONROWCNT);

create index billingdatadetail_Index8
  on billingdatadetail(CONSTRUCTIONCOLCNT);

-- 業者請求支払サマリーデータ
--* BackupToTempTable
drop table if exists billingpaymentsummary cascade;

--* RestoreFromTempTable
create table billingpaymentsummary (
  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'
  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'
  , CHARGEPAYMENTKIND decimal(1,0) default '0' not null comment '請求支払区分'
  , PAYDAY date comment '支払日'
  , BILLPRICESTOTAL decimal(10,0) comment '請求金額小計'
  , CONSUMPTIONTAXRATE decimal(6,3) comment '消費税率'
  , CONSUMPTIONTAXPRICE decimal(10,0) comment '消費税額'
  , HIGHWPRICESTOTAL decimal(10,0) comment '高速代小計'
  , HARDWPRICESTOTAL decimal(10,0) comment '金物代小計'
  , INDSWASTETAXSTOTAL decimal(10,0) comment '産廃税小計'
  , CNSTRPRICERATE decimal(6,3) comment '協力金比率'
  , CNSTRPRICESTOTAL decimal(10,0) comment '協力金小計'
  , BILLINGPRICETOTAL decimal(10,0) comment '請求金額合計'
  , ENTRYDATE datetime comment '登録年月日'
  , UPDATEDATE datetime comment '更新年月日'
  , constraint billingpaymentsummary_PKC primary key (COMPANYCODE,TARGETDATE,CHARGEPAYMENTKIND)
) comment '業者請求支払サマリーデータ' ;

-- 運営期履歴情報
--* BackupToTempTable
drop table if exists bizperiodhistory cascade;

--* RestoreFromTempTable
create table bizperiodhistory (
  PeriodYear smallint(6) not null comment '営業期数・工事年度'
  , PeriodFlag smallint(1) not null comment '営業期数・工事年度フラグ'
  , BeginDate datetime not null comment '期首日'
  , CompleteDate datetime not null comment '期末日'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint bizperiodhistory_PKC primary key (PeriodYear,PeriodFlag)
) comment '運営期履歴情報' ;

-- 工事詳細台帳データ【退避用】
--* BackupToTempTable
drop table if exists bk_constructionledger cascade;

--* RestoreFromTempTable
create table bk_constructionledger (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ConstructionPeriod decimal(4,0) unsigned not null comment '事業期'
  , CreatorCode decimal(8,0) comment '作成者コード'
  , CreatorName varchar(60) comment '作成者名'
  , CreateDate date not null comment '作成日'
  , ConstructionTimes decimal(5,2) not null comment '工期（単位・月）'
  , ConstructionStart date not null comment '契約工期開始'
  , ConstructionEnd date not null comment '契約工期完了'
  , OrdersDecisionPrice decimal(14,0) not null comment '税抜受注決定金額'
  , CompanyExpenses decimal(10,0) not null comment '会社経費合計'
  , DepartmentExpenses decimal(10,0) not null comment '部署経費合計'
  , SalesExpenses decimal(10,0) not null comment '営業経費合計'
  , TotalPayment decimal(10,0) not null comment '総支払額'
  , GrossProfit decimal(10,0) not null comment '粗利'
  , Allowance decimal(10,0) not null comment '給与'
  , NetProfit decimal(10,0) not null comment '純利益'
  , ComplateFlg decimal(1,0) not null comment '入力完了フラグ'
  , IntegrationFlg decimal(1,0) not null comment '受注金額積算フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint bk_constructionledger_PKC primary key (ConstructionCode)
) comment '工事詳細台帳データ【退避用】' ;

-- 工事詳細台帳明細データ【退避用】
--* BackupToTempTable
drop table if exists bk_constructionledgerdetail cascade;

--* RestoreFromTempTable
create table bk_constructionledgerdetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
  , LineCount decimal(4,0) unsigned not null comment '行番号'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , FirstString varchar(120) comment '項目名称'
  , SecondString varchar(120) comment '工事内容'
  , CompanyType decimal(1,0) not null comment '協力会社コードタイプ'
  , CompanyCode decimal(8,0) not null comment '協力会社コード'
  , CompanyName varchar(120) comment '協力会社名称'
  , EstimatePrice decimal(12,0) not null comment '予算（見積）金額'
  , ExecutionAmount decimal(12,0) not null comment '実行金額'
  , AmountConfigRate decimal(5,2) not null comment '金額構成率'
  , PaymentBurden decimal(12,0) not null comment '支払補填額'
  , FixDataFlg decimal(1,0) not null comment '固定データフラグ'
  , IndependentFlg decimal(1,0) not null comment '独立データフラグ'
  , FluctuationFlg decimal(1,0) not null comment '増減データフラグ'
  , SalaryFlg decimal(1,0) not null comment '給与振分区分'
  , SalaryDays decimal(4,0) not null comment '給与振分日数'
  , OperatingFlg decimal(1,0) not null comment '担当中フラグ'
  , SourceCode decimal(10,0) unsigned not null comment '元工事番号'
  , JoinTitleFlg decimal(1,0) not null comment '工事名称タイトル'
  , SalaryOnRegist decimal(12,0) not null comment '登録時月額給与'
  , PurchaseOrderFlg decimal(2,0) not null comment '注文書発行フラグ'
  , DetailCount decimal(4,0) unsigned not null comment '明細番号'
  , InvalidLine decimal(1,0) not null comment '無効行'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint bk_constructionledgerdetail_PKC primary key (ConstructionCode,GroupCount,LineCount)
) comment '工事詳細台帳明細データ【退避用】' ;

-- 工事詳細台帳実行データ【退避用】
--* BackupToTempTable
drop table if exists bk_constructionledgerexcute cascade;

--* RestoreFromTempTable
create table bk_constructionledgerexcute (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
  , LineCount decimal(5,0) unsigned not null comment '行番号'
  , ColumnCount decimal(3,0) not null comment '列番号'
  , PaymentAmount decimal(12,0) not null comment '支払金額'
  , TargetMonth date not null comment '対象年月'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint bk_constructionledgerexcute_PKC primary key (ConstructionCode,GroupCount,LineCount,ColumnCount)
) comment '工事詳細台帳実行データ【退避用】' ;

-- 積算見積データ【退避用】
--* BackupToTempTable
drop table if exists bk_estimatedata cascade;

--* RestoreFromTempTable
create table bk_estimatedata (
  SaveDate datetime not null comment '退避日付'
  , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , LineCount decimal(3,0) unsigned not null comment '行番号'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , FixedItemCode varchar(1) comment '固定項目キー'
  , ItemName varchar(120) comment '作業名称'
  , SpecName varchar(120) comment '作業／品質・形状・寸法'
  , PriceValue decimal(14,0) not null comment '金額'
  , note varchar(60) comment '備考'
  , MyExpensesFlg decimal(1,0) not null comment '自社経費フラグ'
  , InputFlg decimal(1,0) not null comment '入力フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint bk_estimatedata_PKC primary key (SaveDate,ConstructionCode,LineCount)
) comment '積算見積データ【退避用】' ;

-- 積算見積ページデータ【退避用】
--* BackupToTempTable
drop table if exists bk_estimatedatabody cascade;

--* RestoreFromTempTable
create table bk_estimatedatabody (
  SaveDate datetime not null comment '退避日付'
  , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , PageCount decimal(4,0) unsigned not null comment 'ページ番号'
  , Category decimal(1,0) not null comment 'ページ区分'
  , UnionComponentCode decimal(5,0) unsigned not null comment '所属構成キー'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , SelectComponent decimal(5,0) unsigned not null comment '選択構成キー'
  , PageTitle varchar(120) not null comment 'ページ表示名'
  , DetailLineCount decimal(3,0) unsigned not null comment '明細行番号'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint bk_estimatedatabody_PKC primary key (SaveDate,ConstructionCode,PageCount)
) comment '積算見積ページデータ【退避用】' ;

-- 積算見積データ明細【退避用】
--* BackupToTempTable
drop table if exists bk_estimatedatadetail cascade;

--* RestoreFromTempTable
create table bk_estimatedatadetail (
  SaveDate datetime not null comment '退避日付'
  , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , PageCount decimal(4,0) unsigned not null comment 'ページ番号'
  , LineCount decimal(4,0) unsigned not null comment '行番号'
  , DataType decimal(1,0) not null comment 'データ種別'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , SpecCode decimal(5,0) unsigned not null comment '作業キー'
  , ItemName varchar(120) comment '作業名称'
  , SpecName varchar(120) comment '作業／品質・形状・寸法'
  , Unitcount decimal(8,2) not null comment '数量'
  , UnitName varchar(30) comment '単位名称'
  , UnitPrice decimal(9,2) not null comment '単価'
  , LineTotal decimal(12,0) not null comment '金額'
  , note varchar(60) comment '備考'
  , CompanyCode decimal(8,0) not null comment '協力会社コード'
  , MyExpensesFlg decimal(1,0) not null comment '自社経費フラグ'
  , InputFlg decimal(1,0) comment '入力フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint bk_estimatedatadetail_PKC primary key (SaveDate,ConstructionCode,PageCount,LineCount)
) comment '積算見積データ明細【退避用】' ;

-- 予算書集計コードマスタ
--* BackupToTempTable
drop table if exists budgetsumcode cascade;

--* RestoreFromTempTable
create table budgetsumcode (
  BlockCode decimal(2,0) unsigned not null comment 'ブロック番号'
  , GroupCode decimal(2,0) unsigned not null comment 'グループ番号'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , ElementsName varchar(100) not null comment '要素名称'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint budgetsumcode_PKC primary key (BlockCode,GroupCode,ComponentCode,ItemCode)
) comment '予算書集計コードマスタ' ;

-- 法人格マスタ
--* BackupToTempTable
drop table if exists businesstypemaster cascade;

--* RestoreFromTempTable
create table businesstypemaster (
  BusinessTypeCode decimal(5,0) unsigned not null comment '法人格コード'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , BusinessTypeName varchar(100) not null comment '業種名'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint businesstypemaster_PKC primary key (BusinessTypeCode)
) comment '法人格マスタ' ;

create index BusinessTypeMaster_Index1
  on businesstypemaster(DisplayOrder);

-- 計算係数保持テーブル
--* BackupToTempTable
drop table if exists calccoefficient cascade;

--* RestoreFromTempTable
create table calccoefficient (
  TypeCode decimal(2,0) unsigned not null comment '係数タイプコード'
  , SeqNo decimal(2,0) unsigned not null comment '順序'
  , CalcCoefficient01 decimal(15,5) not null comment '係数値1'
  , CalcCoefficient02 decimal(15,5) not null comment '係数値2'
  , CalcCoefficient03 decimal(15,5) not null comment '係数値3'
  , CalcCoefficient04 decimal(15,5) not null comment '係数値4'
  , CalcCoefficient05 decimal(15,5) not null comment '係数値5'
  , CalcCoefficient06 decimal(15,5) not null comment '係数値6'
  , CalcCoefficient07 decimal(15,5) not null comment '係数値7'
  , CalcCoefficient08 decimal(15,5) not null comment '係数値8'
  , CalcCoefficient09 decimal(15,5) not null comment '係数値9'
  , CalcCoefficient10 decimal(15,5) not null comment '係数値10'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint calccoefficient_PKC primary key (TypeCode,SeqNo)
) comment '計算係数保持テーブル' ;

-- 担当者部署履歴データ
--* BackupToTempTable
drop table if exists chgchargedep cascade;

--* RestoreFromTempTable
create table chgchargedep (
  PersonCode decimal(8,0) not null comment '担当者コード'
  , StartDate date not null comment '開始年月日'
  , CompDate date not null comment '終了年月日'
  , DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
  , DepartmentName varchar(60) comment '部署名'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint chgchargedep_PKC primary key (PersonCode,StartDate)
) comment '担当者部署履歴データ' ;

-- 工事担当変更履歴データ
--* BackupToTempTable
drop table if exists chgconstrcharge cascade;

--* RestoreFromTempTable
create table chgconstrcharge (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , Division decimal(1,0) not null comment '設定区分'
  , SeqNo smallint(5) unsigned not null comment '連番'
  , DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
  , PersonCode decimal(8,0) not null comment '担当者コード'
  , StartDate date comment '担当開始年月日'
  , CompDate date comment '担当終了年月日'
  , SourceCode decimal(10,0) unsigned not null comment '元工事コード'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint chgconstrcharge_PKC primary key (ConstructionCode,Division,SeqNo)
) comment '工事担当変更履歴データ' ;

-- Web-EDI締め日データ
--* BackupToTempTable
drop table if exists closingdateofmonth cascade;

--* RestoreFromTempTable
create table closingdateofmonth (
  TARGETDATE decimal(6,0) unsigned default '0' not null comment '対象年月'
  , SEQNO decimal(3,0) default '0' not null comment '連番'
  , StartDate date comment '入力開始日'
  , CompDate date comment '入力完了日'
  , PayPlansDate date comment '支払予定日'
  , ENTRYDATE datetime comment '登録年月日'
  , UPDATEDATE datetime comment '更新年月日'
  , constraint closingdateofmonth_PKC primary key (TARGETDATE,SEQNO)
) comment 'Web-EDI締め日データ' ;

-- 共通費大項目マスタ
--* BackupToTempTable
drop table if exists commoncostlarge cascade;

--* RestoreFromTempTable
create table commoncostlarge (
  LargeCode decimal(5,0) unsigned not null comment '大項目キー'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , LargeName varchar(50) not null comment '大項目名称'
  , CommentText varchar(120) comment '説明書き'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint commoncostlarge_PKC primary key (LargeCode)
) comment '共通費大項目マスタ' ;

-- 共通費工種関連マスタ
--* BackupToTempTable
drop table if exists commoncostlinkms cascade;

--* RestoreFromTempTable
create table commoncostlinkms (
  LargeCode decimal(5,0) unsigned not null comment '大項目キー'
  , MiddleCode decimal(5,0) unsigned not null comment '中項目キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , EntryDate datetime comment '登録日付'
  , UpdateDate datetime comment '更新日付'
  , constraint commoncostlinkms_PKC primary key (LargeCode,MiddleCode,ItemCode)
) comment '共通費工種関連マスタ' ;

-- 共通費中項目マスタ
--* BackupToTempTable
drop table if exists commoncostmiddle cascade;

--* RestoreFromTempTable
create table commoncostmiddle (
  LargeCode decimal(5,0) unsigned not null comment '大項目キー'
  , MiddleCode decimal(5,0) unsigned not null comment '中項目キー'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , MiddleName varchar(50) not null comment '中項目名称'
  , CommentText varchar(120) comment '説明書き'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint commoncostmiddle_PKC primary key (LargeCode,MiddleCode)
) comment '共通費中項目マスタ' ;

-- 共通費中項目キーマスタ
--* BackupToTempTable
drop table if exists commoncostmiddlesearchword cascade;

--* RestoreFromTempTable
create table commoncostmiddlesearchword (
  LargeCode decimal(5,0) unsigned not null comment '大項目キー'
  , MiddleCode decimal(5,0) unsigned not null comment '中項目キー'
  , SeqNo decimal(5,0) unsigned not null comment '順序'
  , SearchWord varchar(50) not null comment '検索項目'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint commoncostmiddlesearchword_PKC primary key (LargeCode,MiddleCode,SeqNo)
) comment '共通費中項目キーマスタ' ;

-- 構成工種関連マスタ
--* BackupToTempTable
drop table if exists componentlinkmaster cascade;

--* RestoreFromTempTable
create table componentlinkmaster (
  ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , DisplayOrder decimal(5,0) unsigned comment '表示順'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint componentlinkmaster_PKC primary key (ComponentCode,ItemCode)
) comment '構成工種関連マスタ' ;

create index ComponentLinkMaster_Index1
  on componentlinkmaster(ComponentCode);

create index ComponentLinkMaster_Index2
  on componentlinkmaster(ItemCode);

-- 構成マスタ
--* BackupToTempTable
drop table if exists componentmaster cascade;

--* RestoreFromTempTable
create table componentmaster (
  ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , ComponentName varchar(120) not null comment '構成名称'
  , SubItemsFlg decimal(1,0) comment '下位項目フラグ'
  , DirectInputFlg decimal(1,0) comment '直接入力フラグ'
  , MyExpensesFlg decimal(1,0) comment '自社経費フラグ'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint componentmaster_PKC primary key (ComponentCode)
) comment '構成マスタ' ;

create index ComponentMaster_Index1
  on componentmaster(DisplayOrder);

-- 工事種別・構成リンクマスタ
--* BackupToTempTable
drop table if exists componenttotypemaster cascade;

--* RestoreFromTempTable
create table componenttotypemaster (
  TypeCode decimal(5,0) unsigned not null comment '工事種別コード'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint componenttotypemaster_PKC primary key (TypeCode,ComponentCode)
) comment '工事種別・構成リンクマスタ' ;

create index ComponentToTypeMaster_Index1
  on componenttotypemaster(TypeCode);

create index ComponentToTypeMaster_Index2
  on componenttotypemaster(ComponentCode);

-- 工事詳細台帳経費データ
--* BackupToTempTable
drop table if exists constrledgerexpenses cascade;

--* RestoreFromTempTable
create table constrledgerexpenses (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , SeqNo decimal(5,0) not null comment '枝番'
  , NameCode decimal(5,0) unsigned not null comment '経費コード'
  , ExpensesRaito decimal(4,2) not null comment '経費率'
  , ExpensesValue decimal(10,0) not null comment '経費金額'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constrledgerexpenses_PKC primary key (ConstructionCode,SeqNo,NameCode)
) comment '工事詳細台帳経費データ' ;

create index ConstrLedgerExpenses_Index1
  on constrledgerexpenses(ConstructionCode);

create index ConstrLedgerExpenses_Index2
  on constrledgerexpenses(SeqNo);

create index ConstrLedgerExpenses_Index3
  on constrledgerexpenses(NameCode);

-- 工事基本情報
--* BackupToTempTable
drop table if exists constructionbaseinfo cascade;

--* RestoreFromTempTable
create table constructionbaseinfo (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , TyingFlg decimal(1,0) not null comment '紐付データフラグ'
  , JoinFlg decimal(1,0) not null comment '結合契約工事フラグ'
  , ConstructionYear smallint(5) unsigned not null comment '工事年度'
  , ConstructionPeriod smallint(5) unsigned not null comment '工事期数'
  , RequestedDate date comment '依頼受け日'
  , EstimatesSubmitDeadline date comment '見積提出期限'
  , EstimatesSubmittedDate date comment '見積提出日'
  , ProvisionalOrderDate date comment '仮受注日'
  , OrderDate date comment '受注日'
  , OrderStartingDate date comment '開始予定日'
  , OrderCompletionDate date comment '完了予定日'
  , PreparationStartDate date comment '施工準備開始日'
  , ConstructionStartingDate date comment '施工開始日'
  , ConstructionCompletionDate date comment '施工完了日'
  , NonOrderDate date comment '非受注日'
  , OrdersDecisionPrice decimal(12,0) not null comment '税抜受注決定金額'
  , OrdersDecisionPriceInTax decimal(12,0) not null comment '税込受注決定金額'
  , SalesDepCode decimal(5,0) unsigned not null comment '営業担当者部署コード'
  , SalesPersonCode decimal(8,0) unsigned not null comment '営業担当者コード'
  , SalesSubDepCode decimal(5,0) unsigned not null comment '営業副担当者部署コード'
  , SalesSubPersonCode decimal(8,0) unsigned not null comment '営業副担当者コード'
  , ConstrDepCode decimal(5,0) unsigned not null comment '工事担当者部署コード'
  , ConstructionPersonCode decimal(8,0) not null comment '工事担当者コード'
  , ConstrSubDepCode decimal(5,0) unsigned not null comment '工事副担当者部署コード'
  , ConstrSubPersonCode decimal(8,0) not null comment '工事副担当者コード'
  , ConstrInstrDepCode decimal(5,0) unsigned not null comment '工事指導員部署コード'
  , ConstructionInstructor decimal(8,0) not null comment '工事指導員コード'
  , TransferConstruction decimal(8,0) not null comment '工事移管先コード'
  , TransferConstructionDate date comment '工事移管日'
  , OrderersDivision decimal(5,0) not null comment '発注者区分コード'
  , OrderersCode decimal(5,0) unsigned not null comment '発注者コード'
  , EstimatesExpirationDate date comment '見積有効期限'
  , ConstructionPeriodStart date comment '契約工期開始日'
  , ConstructionPeriodEnd date comment '契約工期完了'
  , ConstructionPeriodStart2 date comment '契約工期開始（延長時）'
  , ConstructionPeriodEnd2 date comment '契約工期完了（延長時）'
  , StartDate date comment '工事開始日'
  , EndDate date comment '工事終了日'
  , PurchaseOrderMailingDate date comment '注文書郵送日'
  , PurchaseOrderReturnDate date comment '注文書返送日'
  , PurchaseOrderReturnCheckDate date comment '注文書返送確認日'
  , ConsumptionTaxFlg decimal(1,0) not null comment '消費税フラグ'
  , PrimeContractorFlg decimal(1,0) not null comment '元請フラグ'
  , SalesCostFlg decimal(1,0) not null comment '営業経費発生フラグ'
  , ConstructionStatusFlg decimal(2,0) not null comment '工事状態フラグ'
  , ConstructionType decimal(5,0) not null comment '工事種別コード'
  , EstimateType decimal(1,0) not null comment '見積種別'
  , BillingStartDate date not null comment '請求準備開始日'
  , BillingDate date not null comment '請求日'
  , BillingSplitFlg decimal(1,0) not null comment '請求回数フラグ'
  , BillingSendDate date comment '請求書郵送/手渡し日'
  , LedgerComplateDate date not null comment '工事詳細台帳入力完了日付'
  , InspectPlanningDate date not null comment '検査予定日'
  , InspectExecuteDate date not null comment '検査実行日'
  , RevCompleteDate date not null comment '検査是正完了日'
  , ExpectedOrder decimal(3,0) not null comment '受注期待度'
  , EvaluationScore decimal(5,2) unsigned not null comment '評価点数'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionbaseinfo_PKC primary key (ConstructionCode)
) comment '工事基本情報' ;

create index BaseInfo_Index1
  on constructionbaseinfo(ConstructionStatusFlg);

create index BaseInfo_Index2
  on constructionbaseinfo(EstimateType);

create index BaseInfo_Index3
  on constructionbaseinfo(SalesPersonCode);

create index BaseInfo_Index4
  on constructionbaseinfo(ConstructionPersonCode);

create index BaseInfo_Index5
  on constructionbaseinfo(ConstructionInstructor);

create index BaseInfo_Index6
  on constructionbaseinfo(ConstructionPeriod);

-- 工事基本情報明細
--* BackupToTempTable
drop table if exists constructionbaseinfodetail cascade;

--* RestoreFromTempTable
create table constructionbaseinfodetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , DetailNo decimal(3,0) not null comment '明細番号'
  , DetailString varchar(300) comment '明細内容'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionbaseinfodetail_PKC primary key (ConstructionCode,DetailNo)
) comment '工事基本情報明細' ;

create index BaseInfoDetail_Idx1
  on constructionbaseinfodetail(ConstructionCode);

create index BaseInfoDetail_Idx2
  on constructionbaseinfodetail(DetailNo);

-- 工事施工予算データ
--* BackupToTempTable
drop table if exists constructionbudget cascade;

--* RestoreFromTempTable
create table constructionbudget (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , CreatorCode decimal(8,0) not null comment '作成者コード'
  , CreatorName varchar(60) comment '作成者名'
  , CreatorCosts decimal(11,0) not null comment '作成者給与'
  , AssistantCode decimal(8,0) not null comment '副担当者コード'
  , AssistantName varchar(60) comment '副担当者名'
  , AssistantCosts decimal(11,0) not null comment '副担当者給与'
  , InstructorCode decimal(8,0) not null comment '工事指導員コード'
  , InstructorName varchar(60) comment '工事指導員名'
  , InstructorCosts decimal(11,0) not null comment '工事指導員給与'
  , CreateDate date not null comment '作成日'
  , ConstructionTimes decimal(5,2) not null comment '工期（単位・月）'
  , ConstructionStart date not null comment '契約工期開始'
  , ConstructionEnd date not null comment '契約工期完了'
  , InstructorTimes decimal(5,2) not null comment '指導員稼働月数'
  , SalaryFlg decimal(1,0) not null comment '給与振分区分'
  , SalaryDays decimal(4,0) not null comment '振分日数'
  , A_SalaryFlg decimal(1,0) not null comment '副担当者給与振分区分'
  , A_SalaryDays decimal(4,0) not null comment '副担当者振分日数'
  , I_SalaryFlg decimal(1,0) not null comment '指導員給与振分区分'
  , I_SalaryDays decimal(4,0) not null comment '指導員振分日数'
  , OrdersDecisionPrice decimal(12,0) not null comment '税抜受注決定金額'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionbudget_PKC primary key (ConstructionCode)
) comment '工事施工予算データ' ;

-- 工事施工予算データ明細
--* BackupToTempTable
drop table if exists constructionbudgetdetail cascade;

--* RestoreFromTempTable
create table constructionbudgetdetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
  , LineCount decimal(3,0) unsigned not null comment '行番号'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , FirstString varchar(120) comment '項目名称'
  , SecondString varchar(120) comment '工事内容'
  , CompanyType decimal(1,0) not null comment '協力会社コードタイプ'
  , CompanyCode decimal(8,0) not null comment '協力会社コード'
  , CompanyName varchar(120) comment '協力会社名称'
  , EstimatePrice decimal(12,0) not null comment '積算時見積金額'
  , NegotiationPrice decimal(12,0) not null comment '積算時交渉金額'
  , OrderDesiredAmount decimal(12,0) not null comment '発注希望金額'
  , ExecutionAmount decimal(12,0) not null comment '実行金額'
  , AmountConfigRate decimal(5,2) not null comment '金額構成率'
  , NegotiateFlg decimal(1,0) not null comment '協力業者交渉フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionbudgetdetail_PKC primary key (ConstructionCode,GroupCount,LineCount)
) comment '工事施工予算データ明細' ;

-- 工種マスタ
--* BackupToTempTable
drop table if exists constructionitemmaster cascade;

--* RestoreFromTempTable
create table constructionitemmaster (
  ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , ItemName varchar(120) not null comment '工種名称'
  , SubItemsFlg decimal(1,0) comment '下位項目フラグ'
  , MyExpensesFlg decimal(1,0) not null comment '自社経費フラグ'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionitemmaster_PKC primary key (ItemCode)
) comment '工種マスタ' ;

-- 工事詳細台帳データ
--* BackupToTempTable
drop table if exists constructionledger cascade;

--* RestoreFromTempTable
create table constructionledger (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ConstructionPeriod decimal(4,0) unsigned not null comment '事業期'
  , CreatorCode decimal(8,0) comment '作成者コード'
  , CreatorName varchar(60) comment '作成者名'
  , CreateDate date not null comment '作成日'
  , ConstructionTimes decimal(5,2) not null comment '工期（単位・月）'
  , ConstructionStart date not null comment '契約工期開始'
  , ConstructionEnd date not null comment '契約工期完了'
  , OrdersDecisionPrice decimal(14,0) not null comment '税抜受注決定金額'
  , CompanyExpenses decimal(10,0) not null comment '会社経費合計'
  , DepartmentExpenses decimal(10,0) not null comment '部署経費合計'
  , SalesExpenses decimal(10,0) not null comment '営業経費合計'
  , TotalPayment decimal(10,0) not null comment '総支払額'
  , GrossProfit decimal(10,0) not null comment '粗利'
  , Allowance decimal(10,0) not null comment '給与'
  , NetProfit decimal(10,0) not null comment '純利益'
  , ComplateFlg decimal(1,0) not null comment '入力完了フラグ'
  , IntegrationFlg decimal(1,0) not null comment '受注金額積算フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionledger_PKC primary key (ConstructionCode)
) comment '工事詳細台帳データ' ;

create index constructionledger_Index1
  on constructionledger(ConstructionStart);

create index constructionledger_Index2
  on constructionledger(ConstructionEnd);

-- 工事詳細台帳明細データ
--* BackupToTempTable
drop table if exists constructionledgerdetail cascade;

--* RestoreFromTempTable
create table constructionledgerdetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
  , LineCount decimal(4,0) unsigned not null comment '行番号'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , FirstString varchar(120) comment '項目名称'
  , SecondString varchar(120) comment '工事内容'
  , CompanyType decimal(1,0) not null comment '協力会社コードタイプ'
  , CompanyCode decimal(8,0) not null comment '協力会社コード'
  , CompanyName varchar(120) comment '協力会社名称'
  , EstimatePrice decimal(12,0) not null comment '予算（見積）金額'
  , ExecutionAmount decimal(12,0) not null comment '実行金額'
  , AmountConfigRate decimal(5,2) not null comment '金額構成率'
  , PaymentBurden decimal(12,0) not null comment '支払補填額'
  , FixDataFlg decimal(1,0) not null comment '固定データフラグ'
  , IndependentFlg decimal(1,0) not null comment '独立データフラグ'
  , FluctuationFlg decimal(1,0) not null comment '増減データフラグ'
  , SalaryFlg decimal(1,0) not null comment '給与振分区分'
  , SalaryDays decimal(4,0) not null comment '給与振分日数'
  , OperatingFlg decimal(1,0) not null comment '担当中フラグ'
  , SourceCode decimal(10,0) unsigned not null comment '元工事番号'
  , JoinTitleFlg decimal(1,0) not null comment '工事名称タイトル'
  , SalaryOnRegist decimal(12,0) not null comment '登録時月額給与'
  , PurchaseOrderFlg decimal(2,0) not null comment '注文書発行フラグ'
  , DetailCount decimal(4,0) unsigned not null comment '明細番号'
  , InvalidLine decimal(1,0) not null comment '無効行'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionledgerdetail_PKC primary key (ConstructionCode,GroupCount,LineCount)
) comment '工事詳細台帳明細データ' ;

create index LedgerDetail_Index01
  on constructionledgerdetail(ConstructionCode);

create index LedgerDetail_Index02
  on constructionledgerdetail(GroupCount);

create index LedgerDetail_Index03
  on constructionledgerdetail(LineCount);

create index LedgerDetail_Index04
  on constructionledgerdetail(SourceCode);

create index LedgerDetail_Index05
  on constructionledgerdetail(DetailCount);

create index LedgerDetail_Index06
  on constructionledgerdetail(SalaryFlg);

create index LedgerDetail_Index07
  on constructionledgerdetail(CompanyCode);

-- 工事詳細台帳実行データ
--* BackupToTempTable
drop table if exists constructionledgerexcute cascade;

--* RestoreFromTempTable
create table constructionledgerexcute (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
  , LineCount decimal(4,0) unsigned not null comment '行番号'
  , ColumnCount decimal(3,0) not null comment '列番号'
  , PaymentAmount decimal(12,0) not null comment '支払金額'
  , TargetMonth date not null comment '対象年月'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionledgerexcute_PKC primary key (ConstructionCode,GroupCount,LineCount,ColumnCount)
) comment '工事詳細台帳実行データ' ;

create index LedgerExcute_Index1
  on constructionledgerexcute(ConstructionCode,TargetMonth,GroupCount,LineCount);

create index LedgerExcute_Index2
  on constructionledgerexcute(ConstructionCode);

create index LedgerExcute_Index3
  on constructionledgerexcute(GroupCount);

create index LedgerExcute_Index4
  on constructionledgerexcute(LineCount);

create index LedgerExcute_Index5
  on constructionledgerexcute(ColumnCount);

create index LedgerExcute_Index6
  on constructionledgerexcute(TargetMonth);

-- 工事リンク情報
--* BackupToTempTable
drop table if exists constructionlink cascade;

--* RestoreFromTempTable
create table constructionlink (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , FluctuationCode decimal(10,0) not null comment '子工事番号'
  , LinkType decimal(1,0) not null comment 'リンク種別'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionlink_PKC primary key (ConstructionCode,FluctuationCode)
) comment '工事リンク情報' ;

create index ConstructionLink_Index1
  on constructionlink(ConstructionCode);

create index ConstructionLink_Index2
  on constructionlink(FluctuationCode);

create index ConstructionLink_Index3
  on constructionlink(LinkType);

-- 工事資材情報
--* BackupToTempTable
drop table if exists constructionmaterialinfo cascade;

--* RestoreFromTempTable
create table constructionmaterialinfo (
  CONSTRUCTIONCODE int(10) unsigned default 0 not null comment '工事番号'
  , MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '資材品目コード'
  , PROCESSDATE datetime default '0000-00-00 00:00:00' not null comment '処理年月日'
  , RENTCOUNT smallint(5) unsigned comment '貸出数'
  , REPAYCOUNT smallint(5) unsigned comment '返却数'
  , JUNKCOUNT smallint(5) unsigned comment '破棄数'
  , COMPLETEFLG tinyint(3) unsigned comment '完了フラグ'
  , ENTRYDATE datetime comment '登録日付'
  , UPDATEDATE datetime comment '更新日付'
  , constraint constructionmaterialinfo_PKC primary key (CONSTRUCTIONCODE,MATERIALITEMCODE,PROCESSDATE)
) comment '工事資材情報' ;

-- 案件進捗日付データ
--* BackupToTempTable
drop table if exists constructionprogressdate cascade;

--* RestoreFromTempTable
create table constructionprogressdate (
  ConstructionCode decimal(10,0) not null comment '工事番号'
  , ConstructionStatusFlg decimal(2,0) not null comment '設定工事状態'
  , PreviousStatusFlg decimal(2,0) comment '変更前状態'
  , ChangeDate datetime not null comment '設定日付'
  , ChangePersonCode decimal(8,0) not null comment '設定担当者'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionprogressdate_PKC primary key (ConstructionCode,ChangeDate)
) comment '案件進捗日付データ' ;

create index ConstrProg_Index1
  on constructionprogressdate(ConstructionCode);

create index ConstrProg_Index2
  on constructionprogressdate(ConstructionStatusFlg);

create index ConstrProg_Index3
  on constructionprogressdate(ChangeDate);

-- 作業マスタ
--* BackupToTempTable
drop table if exists constructionspecmaster cascade;

--* RestoreFromTempTable
create table constructionspecmaster (
  ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , SpecCode decimal(5,0) unsigned not null comment '作業キー'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , ItemName varchar(120) comment '作業名称'
  , SpecName varchar(120) comment '作業名称'
  , UnitName varchar(30) comment '単位名称'
  , UnitPrice decimal(9,2) not null comment '単価'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionspecmaster_PKC primary key (ItemCode,SpecCode)
) comment '作業マスタ' ;

create index ConstructionSpecMaster_Index1
  on constructionspecmaster(DisplayOrder);

-- 作業単価マスタ
--* BackupToTempTable
drop table if exists constructionspecunitprice cascade;

--* RestoreFromTempTable
create table constructionspecunitprice (
  ConstructionTypeCode mediumint(8) unsigned not null comment '工事種別コード'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , SpecCode decimal(5,0) unsigned not null comment '作業キー'
  , UnitPrice decimal(9,2) not null comment '単価'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionspecunitprice_PKC primary key (ConstructionTypeCode,ComponentCode,ItemCode,SpecCode)
) comment '作業単価マスタ' ;

create index constructionspecunitprice_Index1
  on constructionspecunitprice(ConstructionTypeCode,ComponentCode,ItemCode);

-- 工事種別マスタ
--* BackupToTempTable
drop table if exists constructiontypemaster cascade;

--* RestoreFromTempTable
create table constructiontypemaster (
  TypeCode decimal(5,0) unsigned not null comment '工事種別コード'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , NameString varchar(100) not null comment '名称'
  , PublicFlg decimal(1,0) not null comment '公共フラグ'
  , RepairFlg decimal(1,0) not null comment '新築・改修フラグ'
  , SecRank decimal(1,0) not null comment '保護区分ランク'
  , ExpensesLink decimal(1,0) not null comment '経費使用区分'
  , CompanyProp decimal(1,0) not null comment '自社工事区分'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructiontypemaster_PKC primary key (TypeCode)
) comment '工事種別マスタ' ;

-- 消費税データ
--* BackupToTempTable
drop table if exists consumptiontax cascade;

--* RestoreFromTempTable
create table consumptiontax (
  StartDate date not null comment '使用開始年月日'
  , ComplateDate date comment '使用終了年月日'
  , TaxRate decimal(4,2) default '0.00' not null comment '消費税率'
  , DeleteFlg decimal(1,0) comment '削除フラグ'
  , EntryDate datetime comment '登録年月日'
  , UpdateDate datetime comment '更新年月日'
  , constraint consumptiontax_PKC primary key (StartDate)
) comment '消費税データ' ;

-- 部署出納データ
--* BackupToTempTable
drop table if exists costdataofcompensation cascade;

--* RestoreFromTempTable
create table costdataofcompensation (
  YearNumber smallint(5) unsigned not null comment '年度'
  , CostType decimal(1,0) not null comment '出納タイプフラグ'
  , TargetType decimal(1,0) not null comment '対象先コードタイプ'
  , TargetCode decimal(10,0) not null comment '対象先コード'
  , SeqNo decimal(3,0) not null comment 'データ番号'
  , PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , FromCodeType decimal(1,0) not null comment '出納元コードタイプ'
  , FromCode decimal(10,0) not null comment '出納元コード'
  , SendDate date not null comment '対象日付'
  , LinkCode decimal(10,0) not null comment 'リンク先工事コード'
  , EntryPrice decimal(10,0) not null comment '金額（税抜）'
  , EntryTax decimal(10,0) not null comment '金額（税込）'
  , Content varchar(120) comment '支払内容'
  , BikoComment varchar(120) comment '備考'
  , LockFlg decimal(1,0) not null comment 'データロック'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint costdataofcompensation_PKC primary key (YearNumber,CostType,TargetType,TargetCode,SeqNo)
) comment '部署出納データ' ;

create index CostDataOfCompensation_Index1
  on costdataofcompensation(YearNumber);

create index CostDataOfCompensation_Index2
  on costdataofcompensation(CostType);

create index CostDataOfCompensation_Index3
  on costdataofcompensation(TargetType);

create index CostDataOfCompensation_Index4
  on costdataofcompensation(TargetCode);

create index CostDataOfCompensation_Index5
  on costdataofcompensation(PersonCode);

create index CostDataOfCompensation_Index6
  on costdataofcompensation(FromCode);

create index CostDataOfCompensation_Index7
  on costdataofcompensation(SendDate);

-- 部署出納データ
--* BackupToTempTable
drop table if exists costdataofdepartment cascade;

--* RestoreFromTempTable
create table costdataofdepartment (
  DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
  , ConstrYear smallint(5) unsigned not null comment '工事期数・年数'
  , SeqNo decimal(5,0) unsigned not null comment 'データ番号'
  , CostDate date not null comment '対象日付'
  , DepartmentName varchar(100) not null comment '部署名'
  , SuppliersCode decimal(8,0) unsigned not null comment '出納先コード'
  , SuppliersName varchar(120) comment '出納先名'
  , PaymentContent varchar(100) comment '出納内容'
  , SendReceiveFlg decimal(1,0) not null comment '出納フラグ'
  , EntryPrice decimal(12,0) not null comment '金額（税抜）'
  , EntryTax decimal(12,0) not null comment '金額（税込）'
  , Coment varchar(100) comment '備考'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint costdataofdepartment_PKC primary key (DepartmentCode,ConstrYear,SeqNo)
) comment '部署出納データ' ;

-- 担当者毎経費データ
--* BackupToTempTable
drop table if exists costdataofperson cascade;

--* RestoreFromTempTable
create table costdataofperson (
  PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , ActionDate date not null comment '対象日付'
  , DataType decimal(1,0) not null comment 'データ区分'
  , DataAddCount decimal(5,0) unsigned not null comment 'データ追加番号'
  , TargetMonth varchar(7) not null comment '対象年月'
  , ConstructionCode decimal(10,0) unsigned comment '工事コード'
  , SuppliersCode decimal(5,0) unsigned not null comment '支払先コード'
  , SuppliersName varchar(120) comment '支払先名称'
  , PaymentContent varchar(60) comment '支払内容'
  , PaymentType decimal(1,0) not null comment '支払方法フラグ'
  , EntryPrice decimal(12,0) not null comment '金額（税抜）'
  , EntryPriceInTax decimal(12,0) not null comment '金額（税込）'
  , SlipNumber varchar(12) comment '伝票番号'
  , ComplateFlg decimal(1,0) not null comment '引込済みフラグ'
  , ApprovalFlg decimal(1,0) not null comment '承認フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint costdataofperson_PKC primary key (PersonCode,ActionDate,DataType,DataAddCount)
) comment '担当者毎経費データ' ;

create index CostDataOfPerson_Index1
  on costdataofperson(TargetMonth);

create index CostDataOfPerson_Index2
  on costdataofperson(ConstructionCode);

-- 出納対象経費対応マスタ
--* BackupToTempTable
drop table if exists costdatatargettypeexpenses cascade;

--* RestoreFromTempTable
create table costdatatargettypeexpenses (
  CostType decimal(1,0) not null comment '出納タイプフラグ'
  , TargetType decimal(1,0) not null comment '出納先コードタイプ'
  , ExpensesCode decimal(6,0) not null comment '対象経費コード'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint costdatatargettypeexpenses_PKC primary key (CostType,TargetType,ExpensesCode)
) comment '出納対象経費対応マスタ' ;

create index CostDataTargetTypeExpenses_Index1
  on costdatatargettypeexpenses(CostType);

create index CostDataTargetTypeExpenses_Index2
  on costdatatargettypeexpenses(TargetType);

create index CostDataTargetTypeExpenses_Index3
  on costdatatargettypeexpenses(ExpensesCode);

-- 出納タイプ名称マスタ
--* BackupToTempTable
drop table if exists costdatatype cascade;

--* RestoreFromTempTable
create table costdatatype (
  CostType decimal(1,0) not null comment '出納タイプコード'
  , TargetType decimal(1,0) not null comment '出納先コードタイプ'
  , CostDataTypeName varchar(60) comment '出納タイプ名称'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint costdatatype_PKC primary key (CostType,TargetType)
) comment '出納タイプ名称マスタ' ;

create index CostDataType_Index1
  on costdatatype(CostType);

create index CostDataType_Index2
  on costdatatype(TargetType);

-- 発注者登録申請データ
--* BackupToTempTable
drop table if exists costomerregist cascade;

--* RestoreFromTempTable
create table costomerregist (
  CreatePersonCode decimal(8,0) unsigned not null comment '申請者番号'
  , CreateDate date not null comment '作成日'
  , SeqNo decimal(2,0) not null comment 'キー連番'
  , DataMode decimal(1,0) not null comment 'データ区分'
  , SourceCotegory decimal(5,0) unsigned not null comment '発注者区分'
  , SourceCode decimal(5,0) unsigned not null comment '発注者コード'
  , PetitionPeriod decimal(4,0) unsigned not null comment '事業期'
  , OrderFlg decimal(1,0) not null comment '法人団体・個人フラグ'
  , CorporateStatusName varchar(100) comment '法人格名称'
  , CorporateStatusPoint decimal(1,0) comment '法人格名称位置'
  , OrderersName1 varchar(60) not null comment '発注者名1'
  , OrderersName2 varchar(60) comment '発注者名２'
  , DepartmentName varchar(60) comment '部署名'
  , PersonName varchar(60) comment '担当者名'
  , ZipCode varchar(8) comment '郵便番号'
  , Address1 varchar(60) comment '住所１'
  , Address2 varchar(60) comment '住所2'
  , Address3 varchar(60) comment '住所3'
  , PhoneNumber varchar(13) comment '電話番号'
  , FaxNumber varchar(13) comment 'FAX番号'
  , MailAddress varchar(257) comment 'メールアドレス'
  , Note varchar(300) comment '備考'
  , OrderCotegory decimal(5,0) unsigned not null comment '発注者区分'
  , OrderDate decimal(6,0) unsigned not null comment '申請日'
  , OrderNo decimal(2,0) unsigned not null comment '申請受付番号'
  , CreateDepartmentCode decimal(5,0) unsigned not null comment '申請部署コード'
  , CreateDepartmentName varchar(60) comment '申請部署名'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint costomerregist_PKC primary key (CreatePersonCode,CreateDate,SeqNo)
) comment '発注者登録申請データ' ;

create index CostomerRegist_Index1
  on costomerregist(PetitionPeriod);

create index CostomerRegist_Index2
  on costomerregist(CreatePersonCode);

create index CostomerRegist_Index3
  on costomerregist(CreateDepartmentCode);

-- 日報データ
--* BackupToTempTable
drop table if exists dailydataconstruction cascade;

--* RestoreFromTempTable
create table dailydataconstruction (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , DailyDataDate date not null comment '日報作成日'
  , PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , Weather varchar(60) comment '天気'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint dailydataconstruction_PKC primary key (ConstructionCode,DailyDataDate,PersonCode)
) comment '日報データ' ;

create index DailyDataConstruction_Index1
  on dailydataconstruction(ConstructionCode);

create index DailyDataConstruction_Index2
  on dailydataconstruction(DailyDataDate);

-- 日報データ (明細)
--* BackupToTempTable
drop table if exists dailydatadetail cascade;

--* RestoreFromTempTable
create table dailydatadetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , DailyDataDate date not null comment '日報作成日'
  , PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
  , JobCategoryCode decimal(5,0) not null comment '職種キー'
  , CompanyCode decimal(8,0) not null comment '協力会社コード'
  , TodayHeadCount decimal(4,1) not null comment '当日人員数'
  , TotalHeadCount decimal(5,1) not null comment '累計人員数'
  , TodayWork varchar(100) comment '当日作業内容'
  , NextdayWork varchar(100) comment '翌日作業内容'
  , NextdayHeadCount decimal(4,1) not null comment '翌日人員数'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint dailydatadetail_PKC primary key (ConstructionCode,DailyDataDate,PersonCode,SeqNo)
) comment '日報データ (明細)' ;

create index DailyDataDetail_Index
  on dailydatadetail(ConstructionCode,DailyDataDate,PersonCode);

-- 日報データ (現場巡回)
--* BackupToTempTable
drop table if exists dailydatafield cascade;

--* RestoreFromTempTable
create table dailydatafield (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , DailyDataDate date not null comment '日報作成日'
  , PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
  , PatrolTime datetime not null comment '巡回時間'
  , ContentsText varchar(200) comment '内容'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint dailydatafield_PKC primary key (ConstructionCode,DailyDataDate,PersonCode,SeqNo)
) comment '日報データ (現場巡回)' ;

create index DailyDataField_Index
  on dailydatafield(ConstructionCode,DailyDataDate);

-- 日報データ (資材)
--* BackupToTempTable
drop table if exists dailydatamaterials cascade;

--* RestoreFromTempTable
create table dailydatamaterials (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , DailyDataDate date not null comment '日報作成日'
  , PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
  , Materials varchar(100) comment '資材名'
  , Quantity varchar(100) comment '数量'
  , Production varchar(100) comment '制作会社'
  , Delivery varchar(100) comment '納入業者'
  , QualityControl varchar(100) comment '品質管理事項'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint dailydatamaterials_PKC primary key (ConstructionCode,DailyDataDate,PersonCode,SeqNo)
) comment '日報データ (資材)' ;

create index DailyDataMaterials_Index
  on dailydatamaterials(ConstructionCode,DailyDataDate);

-- 日報データ (協力業者指示)
--* BackupToTempTable
drop table if exists dailydatasubcontractors cascade;

--* RestoreFromTempTable
create table dailydatasubcontractors (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , DailyDataDate date not null comment '日報作成日'
  , PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
  , Attendee varchar(100) comment '出席者'
  , ContentsText varchar(200) comment '内容'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint dailydatasubcontractors_PKC primary key (ConstructionCode,DailyDataDate,PersonCode,SeqNo)
) comment '日報データ (協力業者指示)' ;

create index DailyDataSubcontractors_Index
  on dailydatasubcontractors(ConstructionCode,DailyDataDate);

-- 日報データ (車両)
--* BackupToTempTable
drop table if exists dailydatavehicles cascade;

--* RestoreFromTempTable
create table dailydatavehicles (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , DailyDataDate date not null comment '日報作成日'
  , PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
  , Model varchar(100) comment '機種'
  , Performance varchar(100) comment '性能'
  , Owner varchar(100) comment '所有会社'
  , Driver varchar(100) comment '当日運転者'
  , StartWorkingTime datetime not null comment '作業時間開始'
  , EndWorkingTime datetime not null comment '作業時間終了'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint dailydatavehicles_PKC primary key (ConstructionCode,DailyDataDate,PersonCode,SeqNo)
) comment '日報データ (車両)' ;

create index DailyDataVehicles_Index
  on dailydatavehicles(ConstructionCode,DailyDataDate);

-- 日マスタ
--* BackupToTempTable
drop table if exists daymaster cascade;

--* RestoreFromTempTable
create table daymaster (
  days smallint(6) not null comment '日'
  , constraint daymaster_PKC primary key (days)
) comment '日マスタ' ;

-- 締日マスタ
--* BackupToTempTable
drop table if exists deadlinemaster cascade;

--* RestoreFromTempTable
create table deadlinemaster (
  LABOURKIND decimal(1,0) default '0' not null comment '手間業者区分'
  , DEADLINE decimal(2,0) comment '締日'
  , PAYDAY decimal(2,0) comment '支払日'
  , NOTE varchar(120) comment '備考'
  , DELETEFLG decimal(1,0) comment '削除フラグ'
  , ENTRYDATE datetime comment '登録日付'
  , UPDATEDATE datetime comment '更新日付'
  , constraint deadlinemaster_PKC primary key (LABOURKIND)
) comment '締日マスタ' ;

-- 部署経費率マスタ
--* BackupToTempTable
drop table if exists departmentexpensesmaster cascade;

--* RestoreFromTempTable
create table departmentexpensesmaster (
  DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
  , ExpensesPeriod smallint(6) not null comment '事業期'
  , NameCode decimal(5,0) unsigned not null comment '経費コード'
  , NameString varchar(100) not null comment '名称'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , ExpensesRaito decimal(4,2) not null comment '経費率'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint departmentexpensesmaster_PKC primary key (DepartmentCode,ExpensesPeriod,NameCode)
) comment '部署経費率マスタ' ;

-- 部署マスタ
--* BackupToTempTable
drop table if exists departmentmaster cascade;

--* RestoreFromTempTable
create table departmentmaster (
  DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , DepartmentString varchar(100) not null comment '部署名'
  , ActionScheduleFlg decimal(1,0) not null comment '行動予定表示フラグ'
  , StaffAssignFlg decimal(1,0) not null comment '人員配置表示フラグ'
  , StartDate datetime not null comment '開始日付'
  , CompDate datetime not null comment '終了日付'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint departmentmaster_PKC primary key (DepartmentCode)
) comment '部署マスタ' ;

create index DepartmentMaster_index1
  on departmentmaster(DisplayOrder);

-- 入金確認承認データ
--* BackupToTempTable
drop table if exists depositapprovalinfo cascade;

--* RestoreFromTempTable
create table depositapprovalinfo (
  ORDERERSDIVISION decimal(5,0) not null comment '発注者区分'
  , ORDERERSCODE decimal(5,0) not null comment '発注者コード'
  , TARGETDATE decimal(6,0) not null comment '対象年月'
  , SEQNO decimal(3,0) not null comment '連番'
  , LINECOUNT decimal(3,0) not null comment '行番号'
  , APPROVALNO decimal(3,0) not null comment '承認順序'
  , APPROVALPERSON decimal(8,0) comment '承認者コード'
  , APPROVALPERSONNAME varchar(20) comment '承認者名'
  , APPROVALAUTHORITY decimal(1,0) comment '承認権限区分'
  , APPROVALDATE date comment '承認日付'
  , ENTRYDATE datetime comment '登録日付'
  , UPDATEDATE datetime comment '更新日付'
  , constraint depositapprovalinfo_PKC primary key (ORDERERSDIVISION,ORDERERSCODE,TARGETDATE,SEQNO,LINECOUNT,APPROVALNO)
) comment '入金確認承認データ' ;

-- 入金データ
--* BackupToTempTable
drop table if exists depositdata cascade;

--* RestoreFromTempTable
create table depositdata (
  ORDERERSDIVISION decimal(5,0) not null comment '発注者区分'
  , ORDERERSCODE decimal(5,0) not null comment '発注者コード'
  , TARGETDATE decimal(6,0) not null comment '対象年月'
  , SEQNO decimal(3,0) not null comment '連番'
  , BUSINESSPERIOD decimal(4,0) comment '営業期'
  , DEPOSITDATE date comment '入金日'
  , DEPOSITAMOUNT decimal(12,0) comment '入金金額'
  , DEPOSITAMOUNTCASH decimal(10,0) comment '入金金額（現金）'
  , DEPOSITAMOUNTBILL decimal(10,0) comment '入金金額（手形）'
  , FEES decimal(10,0) comment '手数料'
  , OTHERADJUSTMENTS decimal(10,0) comment '送料'
  , CNSTRPRICE decimal(10,0) comment '協力金'
  , REMAININGCLEARING decimal(10,0) comment '消込残金額'
  , DEPOSITPERSONCODE decimal(8,0) not null comment '入金担当者コード'
  , NOTE varchar(120) comment '備考'
  , ENTRYDATE datetime comment '登録日付'
  , UPDATEDATE datetime comment '更新日付'
  , constraint depositdata_PKC primary key (ORDERERSDIVISION,ORDERERSCODE,TARGETDATE,SEQNO)
) comment '入金データ' ;

-- 入金明細データ
--* BackupToTempTable
drop table if exists depositdatadetail cascade;

--* RestoreFromTempTable
create table depositdatadetail (
  ORDERERSDIVISION decimal(5,0) not null comment '発注者区分'
  , ORDERERSCODE decimal(5,0) not null comment '発注者コード'
  , TARGETDATE decimal(6,0) not null comment '対象年月'
  , REQUESTNO decimal(10,0) not null comment '請求No'
  , ORDERNO decimal(2,0) not null comment '受付番号'
  , DEPOSITMONTH decimal(6,0) not null comment '入金年月'
  , SEQNO decimal(3,0) not null comment '入金連番'
  , DEPOSITAMOUNT decimal(10,0) comment '入金金額（税抜き）'
  , TAXAMOUNT decimal(10,0) comment '消費税'
  , DISCOUNTAMOUNT decimal(10,0) comment '値引き金額'
  , CNSTRPRICE decimal(10,0) comment '協力金'
  , FEES decimal(10,0) comment '手数料'
  , OTHERADJUSTMENTS decimal(10,0) comment 'その他'
  , DIFFERENCEAMOUNT decimal(10,0) comment '差分'
  , CONFIRMATIONPERSONCODE decimal(8,0) comment '確認担当者コード'
  , CONFIRMATIONDATE date comment '担当者確認日付'
  , CONFIRMATIONENDFLG decimal(1,0) comment '確認完了フラグ'
  , NOTE varchar(120) comment '備考'
  , ENTRYDATE datetime comment '登録日付'
  , UPDATEDATE datetime comment '更新日付'
  , constraint depositdatadetail_PKC primary key (ORDERERSDIVISION,ORDERERSCODE,TARGETDATE,REQUESTNO,ORDERNO)
) comment '入金明細データ' ;

create index depositdatadetail_Index1
  on depositdatadetail(TARGETDATE);

create index depositdatadetail_Index2
  on depositdatadetail(ORDERERSDIVISION,ORDERERSCODE,DEPOSITMONTH,SEQNO);

-- 区分マスタ
--* BackupToTempTable
drop table if exists divisionmaster cascade;

--* RestoreFromTempTable
create table divisionmaster (
  DivisionCode decimal(5,0) unsigned not null comment '区分コード'
  , NameCode decimal(5,0) unsigned not null comment '名称コード'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , NameString varchar(100) not null comment '名称'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint divisionmaster_PKC primary key (DivisionCode,NameCode)
) comment '区分マスタ' ;

-- 積算予算書データ
--* BackupToTempTable
drop table if exists estimatebudget cascade;

--* RestoreFromTempTable
create table estimatebudget (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ConstructionTime decimal(5,2) unsigned not null comment '工期'
  , IntegratedCode decimal(8,0) not null comment '積算者'
  , DataCreateDate datetime not null comment 'データ引込日'
  , RetValue1 decimal(12,0) not null comment '予定価格'
  , RetValue2 decimal(12,0) not null comment '直接工事費'
  , RetValue3 decimal(12,0) not null comment '積み上げ共通仮設費'
  , RetValue4 decimal(12,0) not null comment '積み上げ現場管理費'
  , RetPercent decimal(5,2) not null comment '一般管理費率'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint estimatebudget_PKC primary key (ConstructionCode)
) comment '積算予算書データ' ;

-- 積算予算内訳データ明細
--* BackupToTempTable
drop table if exists estimatebudgetdetail cascade;

--* RestoreFromTempTable
create table estimatebudgetdetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
  , LineCount decimal(3,0) unsigned not null comment '行番号'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned comment '工種キー'
  , CompanyCode decimal(8,0) not null comment '協力会社コード'
  , FirstString varchar(120) comment '大項目名称'
  , SecondString varchar(120) comment '工種名称・協力会社名'
  , Content varchar(120) comment '内容'
  , EstimatePrice decimal(12,0) not null comment '見積時金額'
  , NegotiationPrice decimal(12,0) not null comment '交渉時金額'
  , Notes varchar(120) comment '注意事項'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint estimatebudgetdetail_PKC primary key (ConstructionCode,GroupCount,LineCount)
) comment '積算予算内訳データ明細' ;

-- 積算見積データ
--* BackupToTempTable
drop table if exists estimatedata cascade;

--* RestoreFromTempTable
create table estimatedata (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , LineCount decimal(3,0) unsigned not null comment '行番号'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , FixedItemCode varchar(1) comment '固定項目キー'
  , ItemName varchar(120) comment '作業名称'
  , SpecName varchar(120) comment '作業／品質・形状・寸法'
  , PriceValue decimal(14,0) not null comment '金額'
  , note varchar(60) comment '備考'
  , MyExpensesFlg decimal(1,0) not null comment '自社経費フラグ'
  , InputFlg decimal(1,0) not null comment '入力フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint estimatedata_PKC primary key (ConstructionCode,LineCount)
) comment '積算見積データ' ;

create index EstimateData_Index1
  on estimatedata(ConstructionCode,FixedItemCode);

-- 積算見積ページデータ
--* BackupToTempTable
drop table if exists estimatedatabody cascade;

--* RestoreFromTempTable
create table estimatedatabody (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , PageCount decimal(4,0) unsigned not null comment 'ページ番号'
  , Category decimal(1,0) not null comment 'ページ区分'
  , UnionComponentCode decimal(5,0) unsigned not null comment '所属構成キー'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , SelectComponent decimal(5,0) unsigned not null comment '選択構成キー'
  , PageTitle varchar(120) not null comment 'ページ表示名'
  , DetailLineCount decimal(3,0) unsigned not null comment '明細行番号'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint estimatedatabody_PKC primary key (ConstructionCode,PageCount)
) comment '積算見積ページデータ' ;

create index EstimateDataBody_Index1
  on estimatedatabody(ConstructionCode,ComponentCode,ItemCode);

-- 積算見積データ明細
--* BackupToTempTable
drop table if exists estimatedatadetail cascade;

--* RestoreFromTempTable
create table estimatedatadetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , PageCount decimal(4,0) unsigned not null comment 'ページ番号'
  , LineCount decimal(4,0) unsigned not null comment '行番号'
  , DataType decimal(1,0) not null comment 'データ種別'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , SpecCode decimal(5,0) unsigned not null comment '作業キー'
  , ItemName varchar(120) comment '作業名称'
  , SpecName varchar(120) comment '作業／品質・形状・寸法'
  , Unitcount decimal(9,3) not null comment '数量'
  , UnitName varchar(30) comment '単位名称'
  , UnitPrice decimal(11,2) not null comment '単価'
  , LineTotal decimal(12,0) not null comment '金額'
  , note varchar(60) comment '備考'
  , CompanyCode decimal(8,0) not null comment '協力会社コード'
  , MyExpensesFlg decimal(1,0) not null comment '自社経費フラグ'
  , InputFlg decimal(1,0) comment '入力フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint estimatedatadetail_PKC primary key (ConstructionCode,PageCount,LineCount)
) comment '積算見積データ明細' ;

create index EstimateDataDetail_Index1
  on estimatedatadetail(ConstructionCode,PageCount);

-- 経費率マスタ
--* BackupToTempTable
drop table if exists expensesmaster cascade;

--* RestoreFromTempTable
create table expensesmaster (
  DivisionCode smallint(6) not null comment '工事種別コード'
  , NameCode smallint(6) not null comment '経費名称コード'
  , DisplayOrder smallint(6) not null comment '表示順'
  , ExpensesRatio decimal(4,2) not null comment '経費率'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint expensesmaster_PKC primary key (DivisionCode,NameCode)
) comment '経費率マスタ' ;

-- 会社休日マスタ
--* BackupToTempTable
drop table if exists holidaycalendermaster cascade;

--* RestoreFromTempTable
create table holidaycalendermaster (
  Holiday date not null comment '休日'
  , BusinessYear smallint(5) unsigned not null comment '営業年度'
  , TargetMonth tinyint(3) unsigned not null comment '該当月'
  , TargetDay tinyint(3) unsigned not null comment '該当日'
  , constraint holidaycalendermaster_PKC primary key (Holiday)
) comment '会社休日マスタ' ;

create index HolidayCalender_Index1
  on holidaycalendermaster(BusinessYear);

create index HolidayCalenderMaster_Index2
  on holidaycalendermaster(TargetMonth);

create index HolidayCalenderMaster_Index3
  on holidaycalendermaster(TargetDay);

-- 業者請求入力データ
--* BackupToTempTable
drop table if exists inputbillingdata cascade;

--* RestoreFromTempTable
create table inputbillingdata (
  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'
  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'
  , SEQNO decimal(3,0) default '0' not null comment '連番'
  , CONSTRUCTIONCODE decimal(10,0) comment '工事番号'
  , FIELDNAME varchar(120) comment '現場名'
  , PERSONNAME varchar(60) comment '担当者名'
  , BILLPRICE decimal(10,0) comment '請求金額'
  , CONSUMPTIONTAX decimal(10,0) comment '消費税'
  , OTHERPRICE decimal(10,0) comment '金物代'
  , DELETEFLG decimal(1,0) comment '削除フラグ'
  , ENTRYDATE datetime comment '登録年月日'
  , UPDATEDATE datetime comment '更新年月日'
  , constraint inputbillingdata_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO)
) comment '業者請求入力データ' ;

-- 業者請求入力明細データ
--* BackupToTempTable
drop table if exists inputbillingdatadetail cascade;

--* RestoreFromTempTable
create table inputbillingdatadetail (
  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'
  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'
  , SEQNO decimal(3,0) default '0' not null comment '連番'
  , LINECOUNT decimal(3,0) default '0' not null comment '行番号'
  , LABOURKIND decimal(1,0) not null comment '支払い通常・手間区分'
  , CONSTRUCTIONCODE decimal(10,0) comment '工事番号'
  , CONSTRUCTIONROWCNT decimal(3,0) default '0' not null comment '台帳明細番号'
  , CONSTRUCTIONCOLCNT decimal(3,0) default '0' not null comment '台帳列番号'
  , FIELDNAME varchar(120) comment '現場名'
  , SECONDSTRING varchar(120) comment '工事内容'
  , BILLPRICE decimal(10,0) comment '請求金額'
  , HIGHWPRICE decimal(10,0) comment '高速代'
  , HARDWPRICE decimal(10,0) comment '金物代'
  , INDSWASTETAX decimal(10,0) comment '産廃税'
  , NOTE varchar(120) comment '備考'
  , DELETEFLG decimal(1,0) comment '削除フラグ'
  , ENTRYDATE datetime comment '登録年月日'
  , UPDATEDATE datetime comment '更新年月日'
  , constraint inputbillingdatadetail_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO,LINECOUNT)
) comment '業者請求入力明細データ' ;

-- 業者請求入力状態
--* BackupToTempTable
drop table if exists inputbillingstatus cascade;

--* RestoreFromTempTable
create table inputbillingstatus (
  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'
  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'
  , SEQNO decimal(3,0) default '0' not null comment '連番'
  , STATUS decimal(1,0) default '0' comment '請求状態'
  , ENTRYDATE datetime comment '登録年月日'
  , UPDATEDATE datetime comment '更新年月日'
  , constraint inputbillingstatus_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO)
) comment '業者請求入力状態' ;

-- 入力時検索履歴
--* BackupToTempTable
drop table if exists inputsearchlogdata cascade;

--* RestoreFromTempTable
create table inputsearchlogdata (
  ConstructionCode decimal(10,0) not null comment '工事コード'
  , UsedProcessNo decimal(4,0) not null comment '使用機能番号'
  , DisplayOrder decimal(4,0) unsigned not null comment '表示順'
  , DataType decimal(1,0) not null comment 'データ種別'
  , InputCode decimal(10,0) comment '入力コード'
  , constraint inputsearchlogdata_PKC primary key (ConstructionCode,UsedProcessNo,DisplayOrder)
) comment '入力時検索履歴' ;

create index InputSearchLogData_Index1
  on inputsearchlogdata(ConstructionCode,UsedProcessNo,InputCode);

-- 請求書データ
--* BackupToTempTable
drop table if exists invoicedata cascade;

--* RestoreFromTempTable
create table invoicedata (
  INVOICENO decimal(9,0) not null comment '請求書No'
  , ORDERERSDIVISION decimal(5,0) not null comment '発注者区分'
  , ORDERERSCODE decimal(5,0) not null comment '発注者コード'
  , REQUESTMONTH decimal(6,0) not null comment '請求月'
  , SEQNO decimal(2,0) not null comment '連番'
  , CRETATEDATE date not null comment '作成日'
  , REQUESTDATE date not null comment '請求日'
  , REQUESTNAME varchar(120) comment '請求先名'
  , TOTALAMOUNT decimal(10,0) comment '税込み合計金額'
  , TAXAMOUNT decimal(10,0) comment '消費税金額'
  , COMMENT1 varchar(60) comment 'コメント1'
  , COMMENT2 varchar(60) comment 'コメント2'
  , COMMENT3 varchar(60) not null comment 'コメント3'
  , COMMENT4 varchar(60) not null comment 'コメント4'
  , COMMENT5 varchar(60) not null comment 'コメント5'
  , COMPLETEFLG decimal(1,0) default '0' not null comment '完了フラグ'
  , DELETEFLG decimal(1,0) comment '削除フラグ'
  , ENTRYDATE datetime not null comment '登録年月日'
  , UPDATEDATE datetime not null comment '更新年月日'
  , constraint invoicedata_PKC primary key (INVOICENO)
) comment '請求書データ' ;

alter table invoicedata add unique ORDERERSDIVISION (ORDERERSDIVISION,ORDERERSCODE,REQUESTMONTH,SEQNO) ;

-- ログイン履歴
--* BackupToTempTable
drop table if exists loginlog cascade;

--* RestoreFromTempTable
create table loginlog (
  PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , TargetMonth decimal(6,0) unsigned not null comment '対象年月'
  , LastLogin datetime not null comment '月内最終ログイン時間'
  , LastLogout datetime not null comment '月内最終ログアウト時間'
  , TotalTime decimal(10,0) not null comment '累計ログイン時間'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint loginlog_PKC primary key (PersonCode,TargetMonth)
) comment 'ログイン履歴' ;

-- メールコンテンツマスタ
--* BackupToTempTable
drop table if exists mailcontentsmaster cascade;

--* RestoreFromTempTable
create table mailcontentsmaster (
  MailDelivery decimal(3,0) not null comment '送信先設定番号'
  , MailTitle varchar(30) comment 'メールタイトル'
  , MailContent varchar(400) comment 'メール本文'
  , constraint mailcontentsmaster_PKC primary key (MailDelivery)
) comment 'メールコンテンツマスタ' ;

-- 資材情報
--* BackupToTempTable
drop table if exists materialinfo cascade;

--* RestoreFromTempTable
create table materialinfo (
  MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '資材品目コード'
  , MATERIALCOUNT smallint(5) unsigned comment '資材数'
  , RENTCOUNT smallint(5) unsigned comment '貸出可能数'
  , DELETEFLG smallint(5) unsigned comment '削除フラグ'
  , ENTRYDATE datetime comment '登録日付'
  , UPDATEDATE datetime comment '更新日付'
  , constraint materialinfo_PKC primary key (MATERIALITEMCODE)
) comment '資材情報' ;

-- 資材品目マスタ
--* BackupToTempTable
drop table if exists materialitemmaster cascade;

--* RestoreFromTempTable
create table materialitemmaster (
  MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '資材品目コード'
  , MATERIALKINDCODE smallint(5) unsigned comment '資材種類コード'
  , MATERIALITEMNAME varchar(40) comment '資材品目名称'
  , DISPLAYORDER smallint(5) unsigned comment '表示順'
  , DELETEFLG tinyint(3) unsigned comment '削除フラグ'
  , ENTRYDATE datetime comment '登録日付'
  , UPDATEDATE datetime comment '更新日付'
  , VERSIONNO decimal(8,0) default '0' not null comment 'バージョン番号'
  , constraint materialitemmaster_PKC primary key (MATERIALITEMCODE)
) comment '資材品目マスタ' ;

-- 資材種類マスタ
--* BackupToTempTable
drop table if exists materialkindmaster cascade;

--* RestoreFromTempTable
create table materialkindmaster (
  MATERIALKINDCODE smallint(5) unsigned default 0 not null comment '資材種類コード'
  , MATERIALKINDNAME varchar(40) comment '資材種類名称'
  , DISPLAYORDER smallint(5) unsigned comment '表示順'
  , DELETEFLG tinyint(3) unsigned comment '削除フラグ'
  , ENTRYDATE datetime comment '登録日付'
  , UPDATEDATE datetime comment '更新日付'
  , constraint materialkindmaster_PKC primary key (MATERIALKINDCODE)
) comment '資材種類マスタ' ;

-- 資材履歴情報
--* BackupToTempTable
drop table if exists materialrecordinfo cascade;

--* RestoreFromTempTable
create table materialrecordinfo (
  MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '資材品目コード'
  , PROCESSDATE date default '0000-00-00' not null comment '処理年月日'
  , SEQNO smallint(5) unsigned default 0 not null comment '連番'
  , RECKIND tinyint(3) unsigned comment 'レコード区分'
  , CONSTRUCTIONCODE int(10) unsigned comment '工事番号'
  , PERSONCODE int(10) unsigned comment '担当者コード'
  , MATERIALCOUNT smallint(5) unsigned comment '資材数'
  , REPAYPLANDATE date comment '返却予定年月日'
  , COMMENTTEXT varchar(80) comment 'コメント'
  , ENTRYDATE datetime comment '登録日付'
  , UPDATEDATE datetime comment '更新日付'
  , constraint materialrecordinfo_PKC primary key (MATERIALITEMCODE,PROCESSDATE,SEQNO)
) comment '資材履歴情報' ;

-- 掲示板メッセージ
--* BackupToTempTable
drop table if exists messageboarddata cascade;

--* RestoreFromTempTable
create table messageboarddata (
  RecordNumber decimal(6,0) unsigned not null comment 'レコード番号'
  , BranchNumber decimal(3,0) unsigned not null comment 'レコード枝番'
  , FromCode decimal(8,0) unsigned not null comment '書込み者コード'
  , FromName varchar(60) not null comment '書込み者'
  , MessageTitile varchar(100) comment '伝言タイトル'
  , MessageContent varchar(1333) not null comment '伝言内容'
  , LinkType decimal(2,0) not null comment 'リンク動作タイプ'
  , LinkMessage varchar(200) comment 'リンク文字列'
  , LinkCode varchar(30) comment 'リンクキー'
  , WritingDate datetime not null comment '書込み日時'
  , PersonCode decimal(8,0) not null comment '書込み担当者コード'
  , ShareFlag decimal(2,0) not null comment '管理者対象フラグ'
  , MessageColor varchar(8) comment '文字色'
  , BackColor varchar(8) comment 'バックカラー'
  , MessageFlag decimal(1,0) not null comment 'メッセージフラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint messageboarddata_PKC primary key (RecordNumber,BranchNumber)
) comment '掲示板メッセージ' ;

create index MessageBoardData_Index1
  on messageboarddata(WritingDate);

create index MessageBoardData_Index2
  on messageboarddata(MessageFlag);

-- 掲示板対象者テーブル
--* BackupToTempTable
drop table if exists messageboardterget cascade;

--* RestoreFromTempTable
create table messageboardterget (
  RecordNumber decimal(6,0) unsigned not null comment 'レコード番号'
  , BranchNumber decimal(3,0) unsigned not null comment 'レコード枝番'
  , SeqNum decimal(3,0) unsigned not null comment '順番'
  , ToCode decimal(8,0) not null comment '対象者コード'
  , ToName varchar(60) not null comment '対象者'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint messageboardterget_PKC primary key (RecordNumber,BranchNumber,SeqNum)
) comment '掲示板対象者テーブル' ;

create index MessageBoardTerget_Index1
  on messageboardterget(RecordNumber,BranchNumber);

-- 掲示板メッセージ閲覧履歴
--* BackupToTempTable
drop table if exists messagebrowsinghistory cascade;

--* RestoreFromTempTable
create table messagebrowsinghistory (
  RecordNumber decimal(6,0) unsigned not null comment 'レコード番号'
  , BranchNumber decimal(3,0) unsigned not null comment 'レコード枝番'
  , SeqNum decimal(3,0) unsigned not null comment '順番'
  , BrowsingCode decimal(8,0) not null comment '閲覧者コード'
  , BrowsingDate datetime not null comment '閲覧日付'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint messagebrowsinghistory_PKC primary key (RecordNumber,BranchNumber,SeqNum)
) comment '掲示板メッセージ閲覧履歴' ;

-- 月マスタ
--* BackupToTempTable
drop table if exists monthmaster cascade;

--* RestoreFromTempTable
create table monthmaster (
  month smallint(6) not null comment '月'
  , constraint monthmaster_PKC primary key (month)
) comment '月マスタ' ;

-- お知らせ情報
--* BackupToTempTable
drop table if exists notificationmessage cascade;

--* RestoreFromTempTable
create table notificationmessage (
  MessageDate datetime not null comment '表示年月日'
  , SeqNo decimal(3,0) unsigned not null comment '連番'
  , MessageTitle varchar(100) comment 'お知らせタイトル'
  , MessageContent varchar(1333) not null comment 'お知らせ内容'
  , PersonCode decimal(8,0) not null comment '書込み担当者コード'
  , ShareFlag decimal(2,0) not null comment '管理者対象フラグ'
  , MessageColor varchar(8) comment '文字色'
  , BackColor varchar(8) comment 'バックカラー'
  , QueSeqNo decimal(8,0) default '0' not null comment '送信キュー連番'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint notificationmessage_PKC primary key (MessageDate,SeqNo)
) comment 'お知らせ情報' ;

-- お知らせ閲覧情報
--* BackupToTempTable
drop table if exists notificationmessagebrowsing cascade;

--* RestoreFromTempTable
create table notificationmessagebrowsing (
  MessageDate datetime not null comment '表示年月日'
  , SeqNo decimal(4,0) unsigned not null comment '連番'
  , CompanyCode decimal(8,0) not null comment '協力会社コード'
  , BrowsingFlag decimal(1,0) unsigned default '0' not null comment '閲覧フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint notificationmessagebrowsing_PKC primary key (MessageDate,SeqNo,CompanyCode)
) comment 'お知らせ閲覧情報' ;

create index NotificationMessageBrowsing_Index1
  on notificationmessagebrowsing(CompanyCode);

-- お知らせ対象業者選択情報
--* BackupToTempTable
drop table if exists notificationmessageselect cascade;

--* RestoreFromTempTable
create table notificationmessageselect (
  MessageDate datetime not null comment '表示年月日'
  , SeqNo decimal(4,0) unsigned not null comment '連番'
  , CompanyCode decimal(8,0) not null comment '協力会社コード'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint notificationmessageselect_PKC primary key (MessageDate,SeqNo,CompanyCode)
) comment 'お知らせ対象業者選択情報' ;

-- 発注者マスタ
--* BackupToTempTable
drop table if exists orderersmaster cascade;

--* RestoreFromTempTable
create table orderersmaster (
  OrderCotegory decimal(5,0) unsigned not null comment '発注者区分'
  , OrderersCode decimal(5,0) unsigned not null comment '発注者コード'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , OrderFlg decimal(1,0) not null comment '法人団体・個人フラグ'
  , CorporateStatusName varchar(100) comment '法人格名称'
  , CorporateStatusPoint decimal(1,0) comment '法人格名称位置'
  , OrderersName1 varchar(60) not null comment '発注者名1'
  , OrderersName2 varchar(60) comment '発注者名２'
  , DepartmentName varchar(60) comment '部署名'
  , ChargePersonName varchar(60) comment '担当者名'
  , ZipCode varchar(8) comment '郵便番号'
  , Address1 varchar(60) comment '住所１'
  , Address2 varchar(60) comment '住所2'
  , Address3 varchar(60) comment '住所3'
  , PhoneNumber varchar(13) comment '電話番号'
  , FaxNumber varchar(13) comment 'FAX番号'
  , JapaneseSyllabary varchar(2) comment '50音読み'
  , MailAddress varchar(257) comment 'メールアドレス'
  , Note varchar(300) comment '備考'
  , PersonCode decimal(8,0) not null comment '登録担当者コード'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint orderersmaster_PKC primary key (OrderCotegory,OrderersCode)
) comment '発注者マスタ' ;

create index orderersmaster_Index1
  on orderersmaster(JapaneseSyllabary);

-- 受注金額データ
--* BackupToTempTable
drop table if exists orderspricedata cascade;

--* RestoreFromTempTable
create table orderspricedata (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ChangeNo decimal(3,0) unsigned not null comment '変更番号'
  , ChangeDate date not null comment '変更日'
  , ChangePersonCode decimal(8,0) unsigned not null comment '変更者コード'
  , ChangePersonName varchar(60) comment '変更者名'
  , BeforeValue decimal(14,0) not null comment '変更前税抜受注金額'
  , BeforeValueInTax decimal(14,0) not null comment '変更前税込受注金額'
  , AfterValue decimal(14,0) not null comment '変更後税抜受注金額'
  , AfterValueInTax decimal(14,0) not null comment '変更後税込受注金額'
  , ChangeComment varchar(200) comment '変更理由'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint orderspricedata_PKC primary key (ConstructionCode,ChangeNo)
) comment '受注金額データ' ;

-- 支払承認情報データ
--* BackupToTempTable
drop table if exists paymentapprovalinfo cascade;

--* RestoreFromTempTable
create table paymentapprovalinfo (
  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'
  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'
  , SEQNO decimal(3,0) default '0' not null comment '連番'
  , LINECOUNT decimal(3,0) default '0' not null comment '行番号'
  , APPROVALNO decimal(3,0) default '0' not null comment '承認順序'
  , APPROVALPERSON decimal(8,0) comment '承認者コード'
  , APPROVALPERSONNAME varchar(20) comment '承認者名'
  , APPROVALAUTHORITY decimal(1,0) comment '承認権限区分'
  , APPROVALDATE date comment '承認日付'
  , ENTRYDATE datetime comment '登録日付'
  , UPDATEDATE datetime comment '更新日付'
  , constraint paymentapprovalinfo_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO,LINECOUNT,APPROVALNO)
) comment '支払承認情報データ' ;

-- 支払明細データ
--* BackupToTempTable
drop table if exists paymentdatadetail cascade;

--* RestoreFromTempTable
create table paymentdatadetail (
  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'
  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'
  , SEQNO decimal(3,0) default '0' not null comment '連番'
  , LINECOUNT decimal(3,0) default '0' not null comment '行番号'
  , LABOURKIND decimal(1,0) not null comment '支払い通常・手間区分'
  , BILLPRICE decimal(10,0) comment '請求金額'
  , DISCOUNTPRICE decimal(10,0) comment '値引き金額'
  , OFFSETPRICE decimal(10,0) comment '相殺金額'
  , NEXTCOPRICE decimal(10,0) comment '次回繰越'
  , HIGHWPRICE decimal(10,0) comment '高速代'
  , HARDWPRICE decimal(10,0) comment '金物代'
  , INDSWASTETAX decimal(10,0) comment '産廃税'
  , CNSTRPRICE decimal(10,0) comment '協力金'
  , CNSTRPRICEEXIST decimal(1,0) comment '協力金有無'
  , APPROVALPERSONCODE decimal(8,0) comment '申請担当者コード'
  , APPROVALDATE date comment '担当者申請日付'
  , APPROVALENDFLG decimal(1,0) comment '承認完了フラグ'
  , ENTRYDATE datetime comment '登録日付'
  , UPDATEDATE datetime comment '更新日付'
  , constraint paymentdatadetail_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO,LINECOUNT,LABOURKIND)
) comment '支払明細データ' ;

create index PayDataDetail_Index1
  on paymentdatadetail(TARGETDATE);

create index PayDataDetail_Index2
  on paymentdatadetail(APPROVALPERSONCODE);

-- 工事期限回避データ
--* BackupToTempTable
drop table if exists periodavoidance cascade;

--* RestoreFromTempTable
create table periodavoidance (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ConstructionStatusFlg decimal(2,0) not null comment '工事状態フラグ'
  , FieldNo decimal(2,0) not null comment 'チェック対象フィールド番号'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint periodavoidance_PKC primary key (ConstructionCode,ConstructionStatusFlg,FieldNo)
) comment '工事期限回避データ' ;

create index PeriodAvoidance_Index1
  on periodavoidance(ConstructionCode);

create index PeriodAvoidance_Index2
  on periodavoidance(ConstructionStatusFlg);

create index PeriodAvoidance_Index3
  on periodavoidance(FieldNo);

-- 担当者承認データ
--* BackupToTempTable
drop table if exists personapproval cascade;

--* RestoreFromTempTable
create table personapproval (
  PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
  , OrderDate decimal(6,0) unsigned not null comment '受付日'
  , OrderNo decimal(2,0) unsigned not null comment '受付番号'
  , SeqNo decimal(2,0) not null comment '枝番'
  , PersonCodeApproval decimal(8,0) unsigned not null comment '申請・承認者コード'
  , PetitionApprovalDate datetime not null comment '申請・承認日付'
  , ApprovalLimitDates datetime not null comment '承認希望日'
  , ApprovalStatus decimal(1,0) not null comment '承認状態'
  , InputComment varchar(300) comment 'コメント'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint personapproval_PKC primary key (PersonCode,ApprovalCode,OrderDate,OrderNo,SeqNo)
) comment '担当者承認データ' ;

create index personapproval_Index1
  on personapproval(PersonCode);

create index personapproval_Index2
  on personapproval(ApprovalCode);

create index personapproval_Index3
  on personapproval(OrderDate);

create index personapproval_Index4
  on personapproval(OrderNo);

create index personapproval_Index5
  on personapproval(PersonCodeApproval);

-- 担当者承認コメントデータ
--* BackupToTempTable
drop table if exists personapprovalcomment cascade;

--* RestoreFromTempTable
create table personapprovalcomment (
  PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
  , OrderDate decimal(6,0) unsigned not null comment '受付日'
  , OrderNo decimal(2,0) unsigned not null comment '受付番号'
  , CommentNo decimal(2,0) not null comment 'コメント番号'
  , ParentNo decimal(2,0) not null comment '親コメント番号'
  , ApproValComment varchar(300) comment '指示・連絡コメント'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint personapprovalcomment_PKC primary key (PersonCode,ApprovalCode,OrderDate,OrderNo,CommentNo)
) comment '担当者承認コメントデータ' ;

-- 担当者対象部署マスタ
--* BackupToTempTable
drop table if exists persondepartmentmaster cascade;

--* RestoreFromTempTable
create table persondepartmentmaster (
  PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
  , AffiliationFlg decimal(1,0) not null comment '所属フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint persondepartmentmaster_PKC primary key (PersonCode,DepartmentCode)
) comment '担当者対象部署マスタ' ;

create index PersonDepartmentMaster_Index1
  on persondepartmentmaster(PersonCode);

create index PersonDepartmentMaster_Index2
  on persondepartmentmaster(DepartmentCode);

-- 担当者マスタ
--* BackupToTempTable
drop table if exists personinchargemaster cascade;

--* RestoreFromTempTable
create table personinchargemaster (
  PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , DisplayOrder decimal(8,0) unsigned not null comment '表示順'
  , PersonName varchar(60) not null comment '担当者名'
  , PersonNameKana varchar(60) comment 'フリガナ'
  , Sex decimal(1,0) default '0' comment '性別'
  , BloodType varchar(6) comment '血液型'
  , Birthday date not null comment '生年月日'
  , ZipCode varchar(8) comment '郵便番号'
  , Address1 varchar(60) comment '現住所1'
  , Address2 varchar(60) comment '現住所2'
  , Address3 varchar(60) comment '現住所3'
  , PhoneNumber varchar(13) comment '連絡先電話番号'
  , EmgPhoneNumber varchar(13) comment '緊急連絡先電話番号'
  , EmgContact varchar(60) comment '緊急連絡先名'
  , StartDate date not null comment '入社年月日'
  , EndDate date comment '退社年月日'
  , PassWord varchar(16) comment 'パスワード'
  , SecurityManagement decimal(1,0) not null comment '機密区分'
  , SecCode decimal(2,0) unsigned not null comment '保護区分番号'
  , MsgBackColor varchar(8) comment 'メッセージ背景色'
  , DisplayString varchar(20) comment '役職名称'
  , DepartmentCode decimal(5,0) unsigned comment '部署コード'
  , MonthlySalary decimal(11,0) not null comment '月給金額'
  , YearSalary decimal(12,0) not null comment '年俸金額'
  , Qualification varchar(120) comment '資格入力欄'
  , SealPrintName varchar(5) comment '印鑑表示名'
  , EmployeeClassFlg decimal(1,0) not null comment '社員区分'
  , LedgerFlg decimal(1,0) not null comment '台帳計算対象フラグ'
  , CommutingDistance decimal(5,2) not null comment '通勤距離'
  , CommuteCosts decimal(5,0) not null comment '通勤交通費'
  , PaidVacationDays decimal(4,1) not null comment '今年度有給休暇日数'
  , PaidVacationDaysPrev decimal(4,1) not null comment '前年度有給休暇日数'
  , WorkingCode decimal(3,0) unsigned not null comment '就業コード'
  , PaidDayOffFlg decimal(1,0) unsigned not null comment '有給取得フラグ'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint personinchargemaster_PKC primary key (PersonCode)
) comment '担当者マスタ' ;

create index PersonInChargeMaster_Index1
  on personinchargemaster(StartDate);

create index PersonInChargeMaster_Index2
  on personinchargemaster(DepartmentCode);

-- 担当者資格データ
--* BackupToTempTable
drop table if exists personqualification cascade;

--* RestoreFromTempTable
create table personqualification (
  PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , SeqNo decimal(3,0) unsigned not null comment 'データ順番'
  , DCode decimal(5,0) unsigned not null comment '分類区分'
  , Qcode decimal(5,0) unsigned not null comment '資格コード'
  , DateOfAcquisition datetime not null comment '資格取得日付'
  , Qualification varchar(120) comment 'その他資格名'
  , Notes varchar(120) comment '備考'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint personqualification_PKC primary key (PersonCode,SeqNo)
) comment '担当者資格データ' ;

-- 担当者給与マスタ
--* BackupToTempTable
drop table if exists personsalarymaster cascade;

--* RestoreFromTempTable
create table personsalarymaster (
  PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , StartDate date not null comment '使用開始年月日'
  , MonthlySalary decimal(11,0) not null comment '月給金額'
  , YearSalary decimal(12,0) not null comment '年俸金額'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint personsalarymaster_PKC primary key (PersonCode,StartDate)
) comment '担当者給与マスタ' ;

create index PersonSalaryMaster_Index1
  on personsalarymaster(PersonCode);

create index PersonSalaryMaster_Index2
  on personsalarymaster(StartDate);

create index PersonSalaryMaster_Index3
  on personsalarymaster(MonthlySalary);

-- 指示事項データ
--* BackupToTempTable
drop table if exists pointingoutcomment cascade;

--* RestoreFromTempTable
create table pointingoutcomment (
  ConstructionCode decimal(10,0) not null comment '工事コード'
  , ProcessNo smallint(6) not null comment '処理番号（画面番号）'
  , SeqNo smallint(6) not null comment '順序'
  , PageCount smallint(6) not null comment 'ページ番号'
  , PersonCode decimal(8,0) not null comment '記入者コード'
  , DrowColor varchar(8) comment '描画色'
  , CommentMessage varchar(1000) comment 'メッセージ'
  , StartPointX smallint(6) not null comment '指摘項目位置Ｘ'
  , StartPointY smallint(6) not null comment '指摘項目位置Ｙ'
  , CurrentPointX smallint(6) not null comment '初期位置Ｘ'
  , CurrentPointY smallint(6) not null comment '初期位置Ｙ'
  , CurrentWidth smallint(6) not null comment '表示サイズWidth'
  , CurrentHeight smallint(6) not null comment '表示サイズHeigth'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint pointingoutcomment_PKC primary key (ConstructionCode,ProcessNo,SeqNo)
) comment '指示事項データ' ;

-- 発注者打ち合わせ議事録データ
--* BackupToTempTable
drop table if exists proceedingsdata cascade;

--* RestoreFromTempTable
create table proceedingsdata (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ProceedingsDataDate date not null comment '議事録作成日'
  , StartMeetingTime decimal(4,0) unsigned not null comment '会議時間開始'
  , EndMeetingTime decimal(4,0) unsigned not null comment '会議時間終了'
  , CreatorCode decimal(8,0) not null comment '作成者コード'
  , PalceFlag decimal(1,0) not null comment '現地フラグ'
  , MeetingPalce varchar(120) comment '打合せ場所'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint proceedingsdata_PKC primary key (ConstructionCode,ProceedingsDataDate,StartMeetingTime)
) comment '発注者打ち合わせ議事録データ' ;

-- 発注者打ち合わせ議事録データ（出席者）
--* BackupToTempTable
drop table if exists proceedingsdataattend cascade;

--* RestoreFromTempTable
create table proceedingsdataattend (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ProceedingsDataDate date not null comment '議事録作成日'
  , StartMeetingTime decimal(4,0) unsigned not null comment '会議時間開始'
  , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
  , Department varchar(20) comment '出席者所属'
  , AttendName varchar(10) comment '出席者氏名'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint proceedingsdataattend_PKC primary key (ConstructionCode,ProceedingsDataDate,StartMeetingTime,SeqNo)
) comment '発注者打ち合わせ議事録データ（出席者）' ;

-- 発注者打ち合わせ議事録データ（明細）
--* BackupToTempTable
drop table if exists proceedingsdatadetail cascade;

--* RestoreFromTempTable
create table proceedingsdatadetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ProceedingsDataDate date not null comment '議事録作成日'
  , StartMeetingTime decimal(4,0) unsigned not null comment '会議時間開始'
  , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
  , TitleNo varchar(5) comment '議題番号'
  , Title varchar(20) comment '議題'
  , ContentsText varchar(50) comment '内容'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint proceedingsdatadetail_PKC primary key (ConstructionCode,ProceedingsDataDate,StartMeetingTime,SeqNo)
) comment '発注者打ち合わせ議事録データ（明細）' ;

-- 承認データ
--* BackupToTempTable
drop table if exists processapproval cascade;

--* RestoreFromTempTable
create table processapproval (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
  , OrderNo decimal(2,0) unsigned not null comment '受付番号'
  , SeqNo decimal(2,0) not null comment '枝番'
  , PersonCode decimal(8,0) unsigned not null comment '申請・承認者コード'
  , PetitionApprovalDate datetime not null comment '申請・承認日付'
  , ApprovalLimitDates datetime not null comment '承認希望日'
  , ApprovalStatus decimal(1,0) not null comment '承認状態'
  , InputComment varchar(300) comment 'コメント'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint processapproval_PKC primary key (ConstructionCode,ApprovalCode,OrderNo,SeqNo)
) comment '承認データ' ;

-- 承認コメントデータ
--* BackupToTempTable
drop table if exists processapprovalcomment cascade;

--* RestoreFromTempTable
create table processapprovalcomment (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
  , OrderNo decimal(2,0) unsigned not null comment '受付番号'
  , CommentNo decimal(2,0) not null comment 'コメント番号'
  , ParentNo decimal(2,0) not null comment '親コメント番号'
  , ApproValComment varchar(300) comment '指示・連絡コメント'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint processapprovalcomment_PKC primary key (ConstructionCode,ApprovalCode,OrderNo,CommentNo)
) comment '承認コメントデータ' ;

-- 承認明細データ
--* BackupToTempTable
drop table if exists processapprovaldetail cascade;

--* RestoreFromTempTable
create table processapprovaldetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
  , OrderNo decimal(2,0) unsigned not null comment '受付番号'
  , SerialNo decimal(2,0) unsigned not null comment '枝番'
  , LinkCode decimal(10,0) not null comment 'リンク情報'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint processapprovaldetail_PKC primary key (ConstructionCode,ApprovalCode,OrderNo,SerialNo)
) comment '承認明細データ' ;

create index processapprovaldetail_Index1
  on processapprovaldetail(ConstructionCode,ApprovalCode,LinkCode);

-- 起動対象マスタ
--* BackupToTempTable
drop table if exists processexcutemaster cascade;

--* RestoreFromTempTable
create table processexcutemaster (
  SecCode decimal(4,0) unsigned not null comment '保護区分番号'
  , ExecCode decimal(4,0) unsigned not null comment '起動処理番号'
  , EditFlg decimal(1,0) not null comment '編集フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint processexcutemaster_PKC primary key (SecCode,ExecCode)
) comment '起動対象マスタ' ;

create index ProcessExcuteMaster_Index1
  on processexcutemaster(SecCode);

create index ProcessExcuteMaster_Index2
  on processexcutemaster(ExecCode);

-- 注文書データ
--* BackupToTempTable
drop table if exists purchaseorder cascade;

--* RestoreFromTempTable
create table purchaseorder (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , SeqNo decimal(3,0) unsigned not null comment '注文書枝番'
  , CompanyCode decimal(8,0) not null comment '協力会社コード'
  , CreateDate date comment '作成日付'
  , OrderPrice decimal(12,0) not null comment '発注金額'
  , LastRowStringDate date not null comment '最終行表示日付'
  , OrderSheetType decimal(1,0) not null comment '簡易・本式フラグ'
  , PaymentTerms decimal(1,0) not null comment '支払い条件'
  , CashRatio decimal(4,1) not null comment '現金割合（％）'
  , BillPeriod decimal(4,0) not null comment '手形期間'
  , PrePayments decimal(1,0) not null comment '前金払'
  , PrePaymentsDays decimal(3,0) not null comment '前金払日数'
  , PrePaymentsPrice decimal(12,0) not null comment '前金払金額'
  , PartialPayment decimal(1,0) not null comment '部分払'
  , PartialPaymentMounth decimal(2,0) unsigned not null comment '部分払締切月'
  , PartialPaymentDay1 decimal(2,0) unsigned not null comment '部分払締切日'
  , PartialPaymentDay2 decimal(2,0) unsigned not null comment '部分払支払日'
  , PartialPaymentPrice decimal(12,0) not null comment '部分払金額'
  , CompletionPayment decimal(1,0) not null comment '引渡し時の支払'
  , CompletionPaymentDays decimal(3,0) unsigned not null comment '引渡し時の支払日数'
  , CompletionPaymentPrice decimal(12,0) not null comment '引渡し時の支払金額'
  , LeadEngineerCode decimal(8,0) comment '主任技術者コード'
  , LeadEngineerName varchar(60) comment '主任技術者名'
  , SafetyOfficerCode decimal(8,0) comment '安全管理者コード'
  , SafetyOfficerName varchar(60) comment '安全管理者名'
  , WorkersCount decimal(3,0) unsigned comment '作業員数'
  , PrintoutDate date not null comment '印刷年月日'
  , RePrintFlag decimal(1,0) default '0' not null comment '再印刷可能フラグ（未使用）'
  , OrderDate date not null comment '受注・キャンセル年月日'
  , OrderStatus decimal(1,0) default '0' not null comment '受注状態'
  , OrderStDate date not null comment '着工日'
  , OrderEdDate date not null comment '完了日'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint purchaseorder_PKC primary key (ConstructionCode,SeqNo)
) comment '注文書データ' ;

-- 注文書日付データ
--* BackupToTempTable
drop table if exists purchaseorderdate cascade;

--* RestoreFromTempTable
create table purchaseorderdate (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , SeqNo decimal(3,0) unsigned not null comment '注文書枝番'
  , CompanyCode decimal(8,0) not null comment '協力会社コード'
  , SendDate date not null comment '注文書郵送日'
  , ReturnDate date not null comment '注文書返送日'
  , ReturnCheckDate date not null comment '注文書返送確認日'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint purchaseorderdate_PKC primary key (ConstructionCode,SeqNo)
) comment '注文書日付データ' ;

-- 注文書明細データ
--* BackupToTempTable
drop table if exists purchaseorderdetail cascade;

--* RestoreFromTempTable
create table purchaseorderdetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , SeqNo decimal(3,0) unsigned not null comment '注文書枝番'
  , GroupCount decimal(1,0) not null comment 'グループ番号'
  , LineCount decimal(3,0) unsigned not null comment '行番号'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , FirstString varchar(120) comment '工種・品名'
  , SecondString varchar(120) comment '内容内訳'
  , UnitCount decimal(8,2) not null comment '数量'
  , UnitName varchar(30) comment '単位'
  , UnitPrice decimal(10,0) not null comment '単価'
  , OrdersLinePrice decimal(12,0) comment '金額'
  , SourceCode decimal(10,0) unsigned not null comment '台帳工事番号'
  , SourceDetailCnt decimal(4,0) unsigned not null comment '台帳明細番号'
  , DataTypeFlag decimal(1,0) unsigned not null comment 'データタイプフラグ'
  , StatutoryWelfareRate decimal(4,2) not null comment '法定福利費比率'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint purchaseorderdetail_PKC primary key (ConstructionCode,SeqNo,GroupCount,LineCount)
) comment '注文書明細データ' ;

create index POrderDetail_Index1
  on purchaseorderdetail(SourceCode);

-- 資格マスタ
--* BackupToTempTable
drop table if exists qualificationmaster cascade;

--* RestoreFromTempTable
create table qualificationmaster (
  DCode decimal(5,0) unsigned not null comment '分類区分'
  , Qcode decimal(5,0) unsigned not null comment '資格コード'
  , Qualification varchar(120) comment '分類・資格名'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , DeleteFlg decimal(1,0) default '0' not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint qualificationmaster_PKC primary key (DCode,Qcode)
) comment '資格マスタ' ;

create index QualificationMaster_Index1
  on qualificationmaster(DCode);

create index QualificationMaster_Index2
  on qualificationmaster(Qcode);

create index QualificationMaster_Index3
  on qualificationmaster(DisplayOrder);

-- ４半期別売上データ
--* BackupToTempTable
drop table if exists quarterlysales cascade;

--* RestoreFromTempTable
create table quarterlysales (
  YearCode decimal(5,0) unsigned not null comment '対象年度'
  , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , OrderNo decimal(4,0) unsigned not null comment 'データ順'
  , ConstructionStart date comment '契約工期開始日'
  , ConstructionEnd date comment '契約工期完了'
  , OrdersPrice decimal(12,0) not null comment '税抜受注金額'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint quarterlysales_PKC primary key (YearCode,ConstructionCode,OrderNo)
) comment '４半期別売上データ' ;

create index QuarterlySales_Index1
  on quarterlysales(YearCode);

create index QuarterlySales_Index2
  on quarterlysales(ConstructionCode);

create index QuarterlySales_Index3
  on quarterlysales(OrderNo);

-- ４半期別売上明細データ
--* BackupToTempTable
drop table if exists quarterlysalesdetail cascade;

--* RestoreFromTempTable
create table quarterlysalesdetail (
  YearCode decimal(5,0) unsigned not null comment '対象年度'
  , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , OrderNo decimal(4,0) unsigned not null comment 'データ順'
  , SeqNo decimal(2,0) not null comment 'データ番号'
  , DataType decimal(1,0) unsigned not null comment 'データタイプ'
  , DataYM decimal(6,0) unsigned not null comment '対象年月'
  , PriceValue decimal(12,0) not null comment '対象金額'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint quarterlysalesdetail_PKC primary key (YearCode,ConstructionCode,OrderNo,SeqNo,DataType)
) comment '４半期別売上明細データ' ;

create index QuarterlySalesDetail_Index1
  on quarterlysalesdetail(YearCode);

create index QuarterlySalesDetail_Index2
  on quarterlysalesdetail(ConstructionCode);

create index QuarterlySalesDetail_Index3
  on quarterlysalesdetail(OrderNo);

create index QuarterlySalesDetail_Index4
  on quarterlysalesdetail(SeqNo);

create index QuarterlySalesDetail_Index5
  on quarterlysalesdetail(DataType);

create index QuarterlySalesDetail_Index6
  on quarterlysalesdetail(DataYM);

-- レコード番号管理
--* BackupToTempTable
drop table if exists recordkey cascade;

--* RestoreFromTempTable
create table recordkey (
  KeyNo decimal(2,0) unsigned not null comment 'キーコード'
  , RecordNumber decimal(6,0) unsigned not null comment 'レコード番号'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint recordkey_PKC primary key (KeyNo,RecordNumber)
) comment 'レコード番号管理' ;

-- 請求書削除データ
--* BackupToTempTable
drop table if exists reminvoicedata cascade;

--* RestoreFromTempTable
create table reminvoicedata (
  InvoiceNo decimal(9,0) not null comment '請求書No'
  , RemPerson decimal(8,0) not null comment '削除担当者コード'
  , RemDate date not null comment '削除日付'
  , RemComment varchar(300) comment '削除理由'
  , EntryDate datetime not null comment '登録年月日'
  , UpdateDate datetime not null comment '更新年月日'
  , constraint reminvoicedata_PKC primary key (InvoiceNo)
) comment '請求書削除データ' ;

-- 請求書削除明細データ
--* BackupToTempTable
drop table if exists reminvoicedatadetail cascade;

--* RestoreFromTempTable
create table reminvoicedatadetail (
  InvoiceNo decimal(9,0) not null comment '請求書No'
  , SeqNo smallint(5) unsigned not null comment '連番'
  , RequestNo decimal(9,0) not null comment '請求No'
  , EntryDate datetime not null comment '登録年月日'
  , UpdateDate datetime not null comment '更新年月日'
  , constraint reminvoicedatadetail_PKC primary key (InvoiceNo,SeqNo)
) comment '請求書削除明細データ' ;

-- 請求取消データ
--* BackupToTempTable
drop table if exists removerequest cascade;

--* RestoreFromTempTable
create table removerequest (
  RemoveNo decimal(10,0) not null comment '取消No'
  , RemoveDate decimal(8,0) not null comment '請求取消日'
  , RemovePersonCode decimal(8,0) unsigned not null comment '請求取消担当者コード'
  , RemoveComment varchar(500) comment '明細内容'
  , RequestNo decimal(9,0) not null comment '請求No'
  , ReqConstructionCode decimal(10,0) not null comment '請求工事番号'
  , ORDERNO decimal(2,0) not null comment '受付番号'
  , RequestMonth decimal(6,0) not null comment '請求月'
  , OrderersDivision decimal(5,0) not null comment '発注者区分'
  , OrderersCode decimal(5,0) not null comment '発注者コード'
  , OrderersName varchar(120) not null comment '発注者先名'
  , ReqConstructionName varchar(120) not null comment '請求工事件名'
  , RequestTotalAmount decimal(10,0) not null comment '請求金額合計'
  , RequestAmount decimal(10,0) not null comment '請求金額'
  , TaxAmount decimal(10,0) not null comment '消費税'
  , UnPaidAmount decimal(10,0) comment '未入金'
  , Note varchar(120) comment '備考'
  , AssignedFlg decimal(1,0) comment '割当済フラグ'
  , InvoiceNo decimal(9,0) not null comment '請求書no'
  , ENTRYDATE datetime not null comment '登録年月日'
  , UPDATEDATE datetime not null comment '更新年月日'
  , constraint removerequest_PKC primary key (RemoveNo,RemoveDate)
) comment '請求取消データ' ;

create index RemoveRequest_Index1
  on removerequest(RequestNo);

create index RemoveRequest_Index2
  on removerequest(ReqConstructionCode);

create index RemoveRequest_Index3
  on removerequest(ORDERNO);

-- 請求取消データ明細
--* BackupToTempTable
drop table if exists removerequestdetail cascade;

--* RestoreFromTempTable
create table removerequestdetail (
  RemoveNo decimal(10,0) not null comment '取消No'
  , SeqNo decimal(9,0) not null comment '受付no'
  , RequestNo decimal(9,0) not null comment '請求no'
  , MainConstructionCode decimal(10,0) not null comment '本工事番号'
  , ConstructionCode decimal(10,0) not null comment '工事番号'
  , ConstructionKind decimal(1,0) not null comment '工事区分'
  , RequestAmount decimal(10,0) not null comment '請求金額'
  , TaxAmount decimal(10,0) not null comment '消費税'
  , ENTRYDATE datetime not null comment '登録年月日'
  , UPDATEDATE datetime not null comment '更新年月日'
  , constraint removerequestdetail_PKC primary key (RemoveNo,SeqNo)
) comment '請求取消データ明細' ;

-- 請求データ
--* BackupToTempTable
drop table if exists requestdata cascade;

--* RestoreFromTempTable
create table requestdata (
  REQUESTNO decimal(9,0) not null comment '請求No'
  , MAINCONSTRUCTIONCODE decimal(10,0) not null comment '本工事番号'
  , CONSTRUCTIONNAME varchar(120) not null comment '工事件名'
  , CONTRACTAMOUNT decimal(10,0) not null comment '請負金額'
  , PAIDAMOUNT decimal(10,0) not null comment '請求済金額'
  , REQUESTAMOUNT0 decimal(10,0) not null comment '請求金額０'
  , REQUESTAMOUNT1 decimal(10,0) not null comment '請求金額１'
  , REQUESTAMOUNT2 decimal(10,0) comment '請求金額２'
  , REQUESTAMOUNT3 decimal(10,0) comment '請求金額３'
  , REQUESTAMOUNT4 decimal(10,0) comment '請求金額４'
  , REQUESTAMOUNT5 decimal(10,0) comment '請求金額５'
  , REQUESTAMOUNT6 decimal(10,0) comment '請求金額６'
  , UNCLAIMEDAMOUNT decimal(10,0) not null comment '請求残金額'
  , UNPAIDAMOUNT decimal(10,0) not null comment '未入金金額'
  , TAXAMOUNT decimal(10,0) not null comment '消費税'
  , NOTE varchar(120) not null comment '備考'
  , ENTRYDATE datetime not null comment '登録年月日'
  , UPDATEDATE datetime not null comment '更新年月日'
  , constraint requestdata_PKC primary key (REQUESTNO,MAINCONSTRUCTIONCODE)
) comment '請求データ' ;

-- 請求データ明細
--* BackupToTempTable
drop table if exists requestdatadetail cascade;

--* RestoreFromTempTable
create table requestdatadetail (
  REQUESTNO decimal(9,0) not null comment '請求No'
  , MAINCONSTRUCTIONCODE decimal(10,0) not null comment '本工事番号'
  , CONSTRUCTIONCODE decimal(10,0) not null comment '工事番号'
  , CONSTRUCTIONKIND decimal(1,0) not null comment '工事区分'
  , REQUESTAMOUNT decimal(10,0) not null comment '請求金額'
  , TAXAMOUNT decimal(10,0) not null comment '消費税'
  , ENTRYDATE datetime not null comment '登録年月日'
  , UPDATEDATE datetime not null comment '更新年月日'
  , constraint requestdatadetail_PKC primary key (REQUESTNO,MAINCONSTRUCTIONCODE,CONSTRUCTIONCODE,CONSTRUCTIONKIND)
) comment '請求データ明細' ;

-- 請求ヘッダ
--* BackupToTempTable
drop table if exists requesthead cascade;

--* RestoreFromTempTable
create table requesthead (
  REQUESTNO decimal(9,0) not null comment '請求No'
  , REQCONSTRUCTIONCODE decimal(10,0) not null comment '請求工事番号'
  , ORDERNO decimal(2,0) not null comment '受付番号'
  , REQUESTMONTH decimal(6,0) not null comment '請求月'
  , ORDERERSDIVISION decimal(5,0) not null comment '発注者区分'
  , ORDERERSCODE decimal(5,0) not null comment '発注者コード'
  , ORDERERSNAME varchar(120) not null comment '発注者先名'
  , REQCONSTRUCTIONNAME varchar(120) not null comment '請求工事件名'
  , REQUESTTOTALAMOUNT decimal(10,0) not null comment '請求金額合計'
  , REQUESTAMOUNT decimal(10,0) not null comment '請求金額'
  , TAXAMOUNT decimal(10,0) not null comment '消費税'
  , UNPAIDAMOUNT decimal(10,0) comment '未入金'
  , NOTE varchar(120) comment '備考'
  , ASSIGNEDFLG decimal(1,0) comment '割当済フラグ'
  , INVOICENO decimal(9,0) not null comment '請求書No'
  , BILLINGDATE datetime not null comment '請求日'
  , BILLCONFIRMDATE datetime not null comment '請求確認日'
  , DELETEFLG decimal(1,0) comment '削除フラグ'
  , ENTRYDATE datetime not null comment '登録年月日'
  , UPDATEDATE datetime not null comment '更新年月日'
  , constraint requesthead_PKC primary key (REQUESTNO)
) comment '請求ヘッダ' ;

alter table requesthead add unique REQCONSTRUCTIONCODE (REQCONSTRUCTIONCODE,ORDERNO) ;

-- 請求書日付データ
--* BackupToTempTable
drop table if exists requestorderdate cascade;

--* RestoreFromTempTable
create table requestorderdate (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , InvoiceNo decimal(9,0) not null comment '請求書No'
  , SendDate date not null comment '請求書発給郵送日'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint requestorderdate_PKC primary key (ConstructionCode,InvoiceNo)
) comment '請求書日付データ' ;

-- 売上グラフデータ
--* BackupToTempTable
drop table if exists salesgraphdata cascade;

--* RestoreFromTempTable
create table salesgraphdata (
  GraphDataCode decimal(8,0) not null comment 'グラフデータコード'
  , ConstructionPeriod decimal(4,0) unsigned not null comment '事業期'
  , SalesDataDays date not null comment '売上年月'
  , DisplayOrder decimal(8,0) unsigned not null comment '表示順'
  , DataName varchar(100) not null comment 'データ表示名称'
  , SalesAmount decimal(10,0) not null comment '売上データ'
  , AdministrativeExpense decimal(10,0) not null comment '販管・部署経費'
  , GrossProfit decimal(10,0) not null comment '粗利益'
  , NetIncome decimal(10,0) not null comment '純利益(損益)'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint salesgraphdata_PKC primary key (GraphDataCode,ConstructionPeriod,SalesDataDays)
) comment '売上グラフデータ' ;

create index SalesGraphData_Idx1
  on salesgraphdata(DisplayOrder);

create index SalesGraphData_idx2
  on salesgraphdata(ConstructionPeriod);

-- 保護区分マスタ
--* BackupToTempTable
drop table if exists securitymaster cascade;

--* RestoreFromTempTable
create table securitymaster (
  SecCode decimal(4,0) unsigned not null comment '保護区分番号'
  , DisplayOrder decimal(4,0) not null comment '表示順'
  , SecName varchar(60) not null comment '保護区分名称'
  , SecRank decimal(1,0) not null comment '保護区分ランク'
  , SecRange decimal(1,0) not null comment '保護区分範囲'
  , GeneralAffairs decimal(1,0) not null comment '総務業務フラグ'
  , SelectBackColor varchar(8) not null comment '選択背景色'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint securitymaster_PKC primary key (SecCode)
) comment '保護区分マスタ' ;

create index SecurityMaster_Index1
  on securitymaster(DisplayOrder);

create index SecurityMaster_Index2
  on securitymaster(SecRank);

create index SecurityMaster_Index3
  on securitymaster(SecRange);

-- 安全パトロールデータ
--* BackupToTempTable
drop table if exists sfpdata cascade;

--* RestoreFromTempTable
create table sfpdata (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , DataCount decimal(2,0) unsigned not null comment 'パトロール回数'
  , RequestDate datetime not null comment 'パトロール要請日時'
  , PersonCode decimal(8,0) unsigned not null comment '申請者コード'
  , StringValue varchar(300) comment 'コメント'
  , OrderNo decimal(2,0) unsigned not null comment '申請受付番号'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint sfpdata_PKC primary key (ConstructionCode,DataCount)
) comment '安全パトロールデータ' ;

create index SFPData_Index1
  on sfpdata(RequestDate);

-- 安全パトロール明細データ
--* BackupToTempTable
drop table if exists sfpdatadetail cascade;

--* RestoreFromTempTable
create table sfpdatadetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , DataCount decimal(2,0) unsigned not null comment 'パトロール回数'
  , SeqNo decimal(2,0) unsigned not null comment 'データ枝番'
  , CompanyCode decimal(8,0) unsigned not null comment '協力会社コード'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint sfpdatadetail_PKC primary key (ConstructionCode,DataCount,SeqNo)
) comment '安全パトロール明細データ' ;

-- 協力会社メール送信キュー
--* BackupToTempTable
drop table if exists subconmailqueue cascade;

--* RestoreFromTempTable
create table subconmailqueue (
  SeqNo decimal(8,0) default '0' not null comment '連番'
  , CompanyCode decimal(8,0) default '0' not null comment '協力会社コード'
  , MailDelivery decimal(3,0) default '0' not null comment '送信先設定番号'
  , Argument0 varchar(32) comment 'メール挿入文字０'
  , Argument1 varchar(32) comment 'メール挿入文字１'
  , Argument2 varchar(32) comment 'メール挿入文字２'
  , Argument3 varchar(32) comment 'メール挿入文字３'
  , Argument4 varchar(32) comment 'メール挿入文字４'
  , Status decimal(1,0) default '0' not null comment 'ステータス'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint subconmailqueue_PKC primary key (SeqNo,CompanyCode)
) comment '協力会社メール送信キュー' ;

create index subconmailqueue_Index1
  on subconmailqueue(CompanyCode);

create index subconmailqueue_Index2
  on subconmailqueue(Argument0);

-- 協力業者施工工種登録申請データ
--* BackupToTempTable
drop table if exists subconstrjobitemregist cascade;

--* RestoreFromTempTable
create table subconstrjobitemregist (
  PersonCode decimal(8,0) unsigned not null comment '申請者番号'
  , CreateDate date not null comment '作成日'
  , SeqNo decimal(2,0) unsigned not null comment '申請連番'
  , DisplayOrder decimal(8,0) unsigned not null comment '表示順'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint subconstrjobitemregist_PKC primary key (PersonCode,CreateDate,SeqNo,DisplayOrder)
) comment '協力業者施工工種登録申請データ' ;

-- 協力業者職種リンク登録申請データ
--* BackupToTempTable
drop table if exists subconstrjoblinkregist cascade;

--* RestoreFromTempTable
create table subconstrjoblinkregist (
  PersonCode decimal(8,0) unsigned not null comment '申請者番号'
  , CreateDate date not null comment '作成日'
  , SeqNo decimal(2,0) unsigned not null comment '申請連番'
  , DisplayOrder decimal(8,0) unsigned not null comment '表示順'
  , JobCategoryCode decimal(5,0) not null comment '職種キー'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint subconstrjoblinkregist_PKC primary key (PersonCode,CreateDate,SeqNo,DisplayOrder)
) comment '協力業者職種リンク登録申請データ' ;

-- 協力業者登録申請データ
--* BackupToTempTable
drop table if exists subconstrregist cascade;

--* RestoreFromTempTable
create table subconstrregist (
  PersonCode decimal(8,0) unsigned not null comment '申請者番号'
  , CreateDate date not null comment '作成日'
  , SeqNo decimal(2,0) unsigned not null comment '申請連番'
  , DataMode decimal(1,0) comment 'データ区分'
  , SourceCode decimal(8,0) not null comment '協力会社コード'
  , PetitionPeriod decimal(4,0) unsigned not null comment '申請年度'
  , CorporateStatusName varchar(40) comment '法人格名称'
  , CorporateStatusPoint decimal(1,0) not null comment '法人格名称位置'
  , CompanyName varchar(100) not null comment '協力会社名'
  , CEOPositionName varchar(60) comment '代表者役職名'
  , CEOName varchar(60) comment '代表者氏名'
  , ContactPersonName varchar(60) comment '担当者氏名'
  , ZipCode varchar(8) comment '郵便番号'
  , Address1 varchar(60) comment '住所１'
  , Address2 varchar(60) comment '住所2'
  , Address3 varchar(60) comment '住所3'
  , CellPhoneNumber varchar(13) comment '携帯電話番号'
  , MailAddress varchar(257) comment 'メールアドレス'
  , PhoneNumber varchar(13) comment '電話番号'
  , FaxNumber varchar(13) comment 'FAX番号'
  , JapaneseSyllabary varchar(2) comment '50音読み'
  , StartDate date not null comment '取引開始年月日'
  , Note varchar(300) comment '備考'
  , labourKind decimal(1,0) not null comment '支払い通常・手間区分'
  , OrderDate decimal(6,0) unsigned comment '申請日'
  , OrderNo decimal(2,0) unsigned not null comment '申請受付番号'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint subconstrregist_PKC primary key (PersonCode,CreateDate,SeqNo)
) comment '協力業者登録申請データ' ;

-- 協力会社工種マスタ
--* BackupToTempTable
drop table if exists subcontractoritemmaster cascade;

--* RestoreFromTempTable
create table subcontractoritemmaster (
  ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , ItemName varchar(120) not null comment '工種名称'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint subcontractoritemmaster_PKC primary key (ItemCode)
) comment '協力会社工種マスタ' ;

-- 協力会社職種マスタ
--* BackupToTempTable
drop table if exists subcontractorjobcategory cascade;

--* RestoreFromTempTable
create table subcontractorjobcategory (
  JobCategoryCode decimal(5,0) not null comment '職種キー'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , JobCategoryName varchar(120) not null comment '職種名称'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint subcontractorjobcategory_PKC primary key (JobCategoryCode)
) comment '協力会社職種マスタ' ;

-- 協力会社施工工種データ
--* BackupToTempTable
drop table if exists subcontractorjobitem cascade;

--* RestoreFromTempTable
create table subcontractorjobitem (
  CompanyCode decimal(8,0) not null comment '協力会社コード'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint subcontractorjobitem_PKC primary key (CompanyCode,ItemCode)
) comment '協力会社施工工種データ' ;

create index SubContractorJobItem_Index1
  on subcontractorjobitem(CompanyCode);

create index SubContractorJobItem_Index2
  on subcontractorjobitem(ItemCode);

-- 協力会社職種リンクマスタ
--* BackupToTempTable
drop table if exists subcontractorjoblink cascade;

--* RestoreFromTempTable
create table subcontractorjoblink (
  CompanyCode decimal(8,0) not null comment '協力会社コード'
  , JobCategoryCode decimal(5,0) not null comment '職種キー'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint subcontractorjoblink_PKC primary key (CompanyCode,JobCategoryCode)
) comment '協力会社職種リンクマスタ' ;

create index SubContractorJobLink_Index1
  on subcontractorjoblink(CompanyCode);

create index SubContractorJobLink_Index2
  on subcontractorjoblink(JobCategoryCode);

-- 協力会社メールデータ
--* BackupToTempTable
drop table if exists subcontractormail cascade;

--* RestoreFromTempTable
create table subcontractormail (
  CompanyCode decimal(8,0) default '0' not null comment '協力会社コード'
  , SeqNo decimal(3,0) default '0' not null comment '連番'
  , MailAddress varchar(257) comment 'メールアドレス'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint subcontractormail_PKC primary key (CompanyCode,SeqNo)
) comment '協力会社メールデータ' ;

-- 協力会社マスタ
--* BackupToTempTable
drop table if exists subcontractormaster cascade;

--* RestoreFromTempTable
create table subcontractormaster (
  CompanyCode decimal(8,0) not null comment '協力会社コード'
  , DisplayOrder decimal(8,0) unsigned not null comment '表示順'
  , CorporateStatusName varchar(40) comment '法人格名称'
  , CorporateStatusPoint decimal(1,0) not null comment '法人格名称位置'
  , CompanyName varchar(100) not null comment '協力会社名'
  , CompanyNameKana varchar(100) not null comment '協力会社名（カナ）'
  , CEOPositionName varchar(60) comment '代表者役職名'
  , CEOName varchar(60) comment '代表者氏名'
  , CEONameKana varchar(60) comment '代表者氏名（カナ）'
  , ContactPersonName varchar(60) comment '担当者氏名'
  , ContactPersonNameKana varchar(60) comment '担当者氏名（カナ）'
  , ZipCode varchar(8) comment '郵便番号'
  , Address1 varchar(60) comment '住所１'
  , Address2 varchar(60) comment '住所2'
  , Address3 varchar(60) comment '住所3'
  , CellPhoneNumber varchar(13) comment '携帯電話番号'
  , MailAddress varchar(257) comment 'メールアドレス'
  , PhoneNumber varchar(13) comment '電話番号'
  , FaxNumber varchar(13) comment 'FAX番号'
  , JapaneseSyllabary varchar(2) comment '50音読み'
  , StartDate date not null comment '取引開始年月日'
  , Note varchar(300) comment '備考'
  , labourKind decimal(1,0) not null comment '支払い通常・手間区分'
  , RequestImputId varchar(8) not null comment '請求入力ID'
  , Password varchar(20) comment 'パスワード'
  , BankCode decimal(4,0) unsigned not null comment '銀行コード'
  , BankName varchar(20) comment '銀行名'
  , BankBranchCode decimal(3,0) unsigned not null comment '支店コード'
  , BankBranchName varchar(20) comment '支店名'
  , BankAccountKind decimal(1,0) not null comment '口座種類'
  , BankAccountNo decimal(7,0) unsigned not null comment '口座番号'
  , BankAccountName varchar(30) comment '口座名義(受取人名)'
  , TransferFees decimal(4,0) unsigned not null comment '振込手数料'
  , ConfirmedDate datetime not null comment 'データ確認日'
  , DeleteFlg decimal(1,0) default '0' not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint subcontractormaster_PKC primary key (CompanyCode)
) comment '協力会社マスタ' ;

alter table subcontractormaster add unique SubContractorMaster_Index2 (RequestImputId) ;

create index SubContractorMaster_Index1
  on subcontractormaster(JapaneseSyllabary);

-- 協力会社メール送信設定
--* BackupToTempTable
drop table if exists subcontrmaildelivery cascade;

--* RestoreFromTempTable
create table subcontrmaildelivery (
  CompanyCode decimal(8,0) default '0' not null comment '協力会社コード'
  , SeqNo decimal(3,0) default '0' not null comment '連番'
  , MailDelivery decimal(3,0) default '0' not null comment '送信先設定番号'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint subcontrmaildelivery_PKC primary key (CompanyCode,SeqNo,MailDelivery)
) comment '協力会社メール送信設定' ;

-- 仕入先マスタ
--* BackupToTempTable
drop table if exists suppliersmaster cascade;

--* RestoreFromTempTable
create table suppliersmaster (
  SuppliersCode decimal(5,0) unsigned not null comment '仕入先コード'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , SuppliersName1 varchar(60) not null comment '仕入先名1'
  , SuppliersName2 varchar(60) comment '仕入先名２'
  , ZipCode varchar(8) comment '郵便番号'
  , Address1 varchar(60) comment '住所１'
  , Address2 varchar(60) comment '住所2'
  , Address3 varchar(60) comment '住所3'
  , PhoneNumber varchar(13) comment '電話番号'
  , FaxNumber varchar(13) comment 'FAX番号'
  , Note varchar(300) comment '備考'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint suppliersmaster_PKC primary key (SuppliersCode)
) comment '仕入先マスタ' ;

-- システム起動確認データ
--* BackupToTempTable
drop table if exists systemexecute cascade;

--* RestoreFromTempTable
create table systemexecute (
  PrimaryCode tinyint(4) not null comment 'キー項目'
  , TargetDate date not null comment '起動日付'
  , SystemVersion int(11) not null comment '本体・環境バージョン'
  , UpDateCopyVersion int(11) not null comment 'コピーバージョン'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint systemexecute_PKC primary key (PrimaryCode)
) comment 'システム起動確認データ' ;

-- 管理マスタ
--* BackupToTempTable
drop table if exists systemmaster cascade;

--* RestoreFromTempTable
create table systemmaster (
  SystemCode decimal(3,0) unsigned not null comment '会社コード'
  , CompanyName1 varchar(60) not null comment '会社名１'
  , CompanyName2 varchar(60) comment '会社名２'
  , CEOName varchar(60) not null comment '代表者氏名'
  , CEOPositionName varchar(60) comment '代表者役職名'
  , ZipCode varchar(8) not null comment '郵便番号'
  , Address1 varchar(60) not null comment '住所１'
  , Address2 varchar(60) comment '住所2'
  , Address3 varchar(60) comment '住所3'
  , PhoneNumber varchar(13) not null comment '電話番号'
  , FaxNumber varchar(13) comment 'FAX番号'
  , HomePageURL varchar(120) comment 'ホームページURL'
  , ConsumptionTax decimal(4,2) not null comment '消費税'
  , CooperationRate decimal(4,2) not null comment '協力金比率'
  , StatutoryWelfareRate decimal(4,2) not null comment '法定福利費比率'
  , FuelPrice decimal(5,2) not null comment '燃料キロ単価'
  , AreaDistance decimal(5,2) not null comment '資材置場移動距離'
  , ExcelSavePath varchar(120) comment 'Excel保存パス'
  , BusinessPeriod smallint(6) not null comment '現在営業期数'
  , ConstrYear smallint(6) not null comment '現在工事年度'
  , BusinessBeginDate datetime not null comment '営業期期首日'
  , ConstrBeginDate datetime not null comment '工事年度期首日'
  , BusinessCompDate datetime not null comment '営業期期末日'
  , ConstrCompDate datetime not null comment '工事年度期末日'
  , ConstructionNoBase decimal(1,0) not null comment '工事番号基準値'
  , SloganString1 varchar(100) comment '標語1'
  , SloganString2 varchar(100) comment '標語2'
  , SloganString3 varchar(100) comment '標語3'
  , OperationFlg decimal(2,0) unsigned not null comment '承認運用フラグ'
  , SystemOpenTime datetime not null comment '運用開始時間'
  , SystemCloseTime datetime not null comment '運用終了時間'
  , PesrsonCost1 decimal(7,0) unsigned not null comment '固定人件費１（指導員コスト）'
  , PesrsonCost2 decimal(7,0) unsigned not null comment '固定人件費２（副担当者コスト）'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint systemmaster_PKC primary key (SystemCode)
) comment '管理マスタ' ;

-- 期限マスタ
--* BackupToTempTable
drop table if exists termmaster cascade;

--* RestoreFromTempTable
create table termmaster (
  ConstructionStatusFlg decimal(2,0) not null comment '工事状態フラグ'
  , FieldNo decimal(2,0) not null comment 'チェック対象フィールド番号'
  , SeqNo decimal(2,0) not null comment '枝番'
  , DisplayOrder decimal(2,0) not null comment '表示順'
  , CheckSchdule decimal(1,0) not null comment 'チェックタイミング'
  , TermDays decimal(3,0) not null comment '経過日'
  , SendTitile varchar(100) comment 'タイトル'
  , SendMessage varchar(100) comment 'メッセージ'
  , BackColor varchar(8) comment '背景色'
  , ForeColor varchar(8) comment '文字色'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint termmaster_PKC primary key (ConstructionStatusFlg,FieldNo,SeqNo)
) comment '期限マスタ' ;

create index TermMaster_Index1
  on termmaster(DisplayOrder);

-- 出勤交通費データ
--* BackupToTempTable
drop table if exists tranceportdailydata cascade;

--* RestoreFromTempTable
create table tranceportdailydata (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , AttendanceDate date not null comment '日報作成日'
  , StartDistance decimal(7,1) not null comment '当日出発キロ数'
  , ComplateDistance decimal(7,1) not null comment '当日終了キロ数'
  , TotalDistance decimal(6,1) not null comment '当日合計キロ数'
  , OfficeGoFlg decimal(1,0) not null comment '事務所出勤フラグ'
  , OfficeOutFlg decimal(1,0) not null comment '事務所帰宅フラグ'
  , TrancePayGoFlg decimal(1,0) not null comment '有料通勤出勤フラグ'
  , TrancePayOutFlg decimal(1,0) not null comment '有料通勤帰宅フラグ'
  , WareHouseFlg decimal(1,0) not null comment '資材置場フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint tranceportdailydata_PKC primary key (PersonCode,AttendanceDate)
) comment '出勤交通費データ' ;

create index TranceportDailyData_Index1
  on tranceportdailydata(AttendanceDate);

-- 工事種別経費率マスタ
--* BackupToTempTable
drop table if exists typeexpensesmaster cascade;

--* RestoreFromTempTable
create table typeexpensesmaster (
  TypeCode decimal(5,0) unsigned not null comment '工事種別コード'
  , ExpensesPeriod smallint(6) not null comment '事業期'
  , NameCode decimal(5,0) unsigned not null comment '経費名称コード'
  , ExpensesRaito decimal(4,2) not null comment '経費率'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint typeexpensesmaster_PKC primary key (TypeCode,ExpensesPeriod,NameCode)
) comment '工事種別経費率マスタ' ;

-- 単位マスタ
--* BackupToTempTable
drop table if exists unitmaster cascade;

--* RestoreFromTempTable
create table unitmaster (
  UnitKey decimal(5,0) unsigned not null comment '単位キー'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , UnitName varchar(30) not null comment '単位名称'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint unitmaster_PKC primary key (UnitKey)
) comment '単位マスタ' ;

create index UnitMaster_Index1
  on unitmaster(DisplayOrder);

-- 車両マスタ
--* BackupToTempTable
drop table if exists vehiclemaster cascade;

--* RestoreFromTempTable
create table vehiclemaster (
  VehicleCode decimal(5,0) unsigned not null comment '車両コード'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , VehicleName varchar(100) not null comment '車両名称'
  , RegistrationNumber varchar(30) comment '登録番号'
  , IdentificationNumber varchar(30) comment '車体番号'
  , ModelCode varchar(30) comment '型式'
  , DepartmentCode decimal(5,0) unsigned not null comment '管理部署'
  , VehicleScheduleFlg decimal(1,0) not null comment '車両予約表示フラグ'
  , PurchaseDate date comment '購入日'
  , PurchaseName varchar(30) comment '購入先'
  , PurchaseContactPersons varchar(30) comment '購入先担当者'
  , PurchasePhone varchar(13) comment '購入先電話番号'
  , LeaseDate date comment 'リース開始日'
  , LeaseDestination varchar(30) comment 'リース先'
  , LeaseContactPersons varchar(30) comment 'リース先担当者'
  , LeasePhone varchar(13) comment 'リース先電話番号'
  , InsuranceCompany varchar(30) comment '任意保険会社'
  , InsuranceContactPersons varchar(30) comment '任意保険会社担当者'
  , InsurancePhone varchar(13) comment '任意保険会社電話番号'
  , Note varchar(300) comment '備考'
  , EntryDate datetime comment '登録日付'
  , UpdateDate datetime comment '更新日付'
  , constraint vehiclemaster_PKC primary key (VehicleCode)
) comment '車両マスタ' ;

-- 車両記録データ
--* BackupToTempTable
drop table if exists vehiclerecorddata cascade;

--* RestoreFromTempTable
create table vehiclerecorddata (
  VehicleCode decimal(5,0) unsigned not null comment '車両コード'
  , DivisionCode decimal(2,0) unsigned not null comment 'データ区分'
  , SeqNo decimal(2,0) not null comment '枝番'
  , RecordDate date comment '実地日'
  , Mileage mediumint(9) comment '走行距離'
  , Content varchar(100) comment '内容'
  , PaymentCost decimal(10,0) comment '費用'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint vehiclerecorddata_PKC primary key (VehicleCode,DivisionCode,SeqNo)
) comment '車両記録データ' ;

-- 車両予約データ
--* BackupToTempTable
drop table if exists vehiclescheduledata cascade;

--* RestoreFromTempTable
create table vehiclescheduledata (
  TargetDate date not null comment '対象日'
  , VehicleCode decimal(5,0) unsigned not null comment '車両コード'
  , MorningAfternoon decimal(1,0) not null comment '午前・午後'
  , PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , TargetYear year(4) not null comment '該当年'
  , TargetMonth tinyint(3) unsigned not null comment '該当月'
  , TargetDay year(4) not null comment '該当日'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint vehiclescheduledata_PKC primary key (TargetDate,VehicleCode,MorningAfternoon)
) comment '車両予約データ' ;

create index VehicleScheduleData_Index1
  on vehiclescheduledata(TargetYear);

create index VehicleScheduleData_Index2
  on vehiclescheduledata(TargetMonth);

create index VehicleScheduleData_Index3
  on vehiclescheduledata(TargetDay);

-- 就業時間マスタ
--* BackupToTempTable
drop table if exists workingtime cascade;

--* RestoreFromTempTable
create table workingtime (
  WorkingCode decimal(3,0) unsigned not null comment '就業コード'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , WorkingName varchar(60) comment '就業時間名称'
  , StartTime datetime not null comment '開始時間'
  , CompTime datetime not null comment '終了時間'
  , OverStartTime datetime not null comment '残業申請開始時間'
  , OnCalender decimal(1,0) not null comment 'カレンダー通りフラグ'
  , MonValue decimal(1,0) not null comment '月曜日休フラグ'
  , TueValue decimal(1,0) not null comment '火曜日休フラグ'
  , WedValue decimal(1,0) not null comment '水曜日休フラグ'
  , ThuValue decimal(1,0) not null comment '木曜日休フラグ'
  , FriValue decimal(1,0) not null comment '金曜日休フラグ'
  , SatValue decimal(1,0) not null comment '土曜日休フラグ'
  , SunValue decimal(1,0) not null comment '日曜日休フラグ'
  , Note varchar(300) comment '備考'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint workingtime_PKC primary key (WorkingCode)
) comment '就業時間マスタ' ;

-- 就業休憩時間マスタ
--* BackupToTempTable
drop table if exists workingtimebreak cascade;

--* RestoreFromTempTable
create table workingtimebreak (
  WorkingCode decimal(3,0) unsigned not null comment '就業コード'
  , BreakNo decimal(2,0) unsigned not null comment '休憩番号'
  , StartTime datetime not null comment '開始時間'
  , CompTime datetime not null comment '終了時間'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint workingtimebreak_PKC primary key (WorkingCode,BreakNo)
) comment '就業休憩時間マスタ' ;

-- 年マスタ
--* BackupToTempTable
drop table if exists yearmaster cascade;

--* RestoreFromTempTable
create table yearmaster (
  years smallint(6) not null comment '年'
  , constraint yearmaster_PKC primary key (years)
) comment '年マスタ' ;