-- Project Name : noname
-- Date/Time    : 2018/07/24 8:34:39
-- Author       : Horiuchi
-- RDBMS Type   : MySQL
-- Application  : A5:SQL Mk-2

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

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);

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

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 '承認経路マスタ' ;

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

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) unsigned not null comment '工事コード'
  , ActionResult varchar(30) not null comment '行動実績'
  , StartTime datetime comment '開始時間'
  , CompTime datetime comment '終了時間'
  , DayTimes decimal(4,1) not null comment '日稼働合計時間'
  , WorkingComment varchar(120) comment 'コメント'
  , WorkKindFlg decimal(1,0) 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);

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

create table attendancedata (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , AttendanceMonth date not null comment '出勤管理年月'
  , LastDays decimal(2,0) unsigned not null comment '当月最終日'
  , AttendDayCount decimal(2,0) unsigned not null comment '出勤日数'
  , TotalOverTimes decimal(4,1) not null comment '残業時間数'
  , OrderDate date 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 '出勤管理データ' ;

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

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 '銀行営業日マスタ' ;

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

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 '業者請求データ' ;

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

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 '行番号'
  , 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 '現場名'
  , BILLPRICE decimal(10,0) comment '請求金額'
  , HIGHWPRICE decimal(10,0) comment '高速代'
  , HARDWPRICE decimal(10,0) comment '金物代'
  , INDSWASTETAX decimal(10,0) comment '産廃税'
  , NOTE varchar(120) comment '備考'
  , ENTRYDATE datetime comment '登録年月日'
  , UPDATEDATE datetime comment '更新年月日'
  , constraint billingdatadetail_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO,LINECOUNT)
) comment '業者請求明細データ' ;

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

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 '業者請求支払サマリーデータ' ;

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

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 '運営期履歴情報' ;

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

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 '工事詳細台帳データ【退避用】' ;

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

create table bk_constructionledgerdetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
  , LineCount decimal(5,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 '注文書発行フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint bk_constructionledgerdetail_PKC primary key (ConstructionCode,GroupCount,LineCount)
) comment '工事詳細台帳明細データ【退避用】' ;

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

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 '工事詳細台帳実行データ【退避用】' ;

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

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 '積算見積データ【退避用】' ;

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

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 '積算見積ページデータ【退避用】' ;

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

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(3,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 '積算見積データ明細【退避用】' ;

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

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 '予算書集計コードマスタ' ;

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

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);

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

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 '担当者部署履歴データ' ;

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

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 '工事担当変更履歴データ' ;

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

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 '共通費大項目マスタ' ;

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

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 '共通費工種関連マスタ' ;

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

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 '共通費中項目マスタ' ;

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

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 '共通費中項目キーマスタ' ;

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

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);

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

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);

-- 構成工事種別関連マスタ
drop table if exists componenttotypemaster cascade;

create table componenttotypemaster (
  ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , TypeCode decimal(5,0) unsigned not null comment '工事種別コード'
  , Dummy decimal(1,0) not null comment 'ダミー項目'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint componenttotypemaster_PKC primary key (ComponentCode,TypeCode)
) comment '構成工事種別関連マスタ' ;

create index ComponentToTypeMaster_Index1
  on componenttotypemaster(ComponentCode);

create index ComponentToTypeMaster_Index2
  on componenttotypemaster(TypeCode);

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

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);

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

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 '検査是正完了日'
  , 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);

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

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);

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

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 '工事施工予算データ' ;

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

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 '工事施工予算データ明細' ;

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

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 '工種マスタ' ;

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

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);

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

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 '明細番号'
  , 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);

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

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);

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

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);

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

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 '工事資材情報' ;

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

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);

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

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);

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

create table constructionspecunitprice (
  ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , SpecCode decimal(5,0) unsigned not null comment '作業キー'
  , ConstructionTypeCode mediumint(8) 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 (ComponentCode,ItemCode,SpecCode,ConstructionTypeCode)
) comment '作業単価マスタ' ;

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

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 '経費使用区分'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructiontypemaster_PKC primary key (TypeCode)
) comment '工事種別マスタ' ;

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

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 '部署出納データ' ;

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

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);

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

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 date 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);

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

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

create index DailyDataConstruction_Index1
  on dailydataconstruction(PersonCode);

create index DailyDataConstruction_Index2
  on dailydataconstruction(DailyDataDate);

create index DailyDataConstruction_Index3
  on dailydataconstruction(ConstructionCode);

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

create table dailydatadetail (
  PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , DailyDataDate date not null comment '日報作成日'
  , ConstructionCode decimal(10,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 (PersonCode,DailyDataDate,ConstructionCode,SeqNo)
) comment '日報データ (明細)' ;

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

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

create table dailydatafield (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , DailyDataDate date not null comment '日報作成日'
  , ConstructionCode decimal(10,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 (PersonCode,DailyDataDate,ConstructionCode,SeqNo)
) comment '日報データ (現場巡回)' ;

create index DailyDataField_Index
  on dailydatafield(PersonCode,DailyDataDate,ConstructionCode);

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

create table dailydatamaterials (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , DailyDataDate date not null comment '日報作成日'
  , ConstructionCode decimal(10,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 (PersonCode,DailyDataDate,ConstructionCode,SeqNo)
) comment '日報データ (資材)' ;

create index DailyDataMaterials_Index
  on dailydatamaterials(PersonCode,DailyDataDate,ConstructionCode);

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

create table dailydatasubcontractors (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , DailyDataDate date not null comment '日報作成日'
  , ConstructionCode decimal(10,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 (PersonCode,DailyDataDate,ConstructionCode,SeqNo)
) comment '日報データ (協力業者指示)' ;

create index DailyDataSubcontractors_Index
  on dailydatasubcontractors(PersonCode,DailyDataDate,ConstructionCode);

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

create table dailydatavehicles (
  PersonCode decimal(8,0) unsigned not null comment '作成者コード'
  , DailyDataDate date not null comment '日報作成日'
  , ConstructionCode decimal(10,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 (PersonCode,DailyDataDate,ConstructionCode,SeqNo)
) comment '日報データ (車両)' ;

create index DailyDataVehicles_Index
  on dailydatavehicles(PersonCode,DailyDataDate,ConstructionCode);

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

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

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

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 '締日マスタ' ;

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

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 '部署経費率マスタ' ;

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

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);

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

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 '入金確認承認データ' ;

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

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 '入金金額（手形）'
  , 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 '入金データ' ;

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

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) comment '受付番号'
  , DEPOSITAMOUNT 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)
) comment '入金明細データ' ;

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

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 '区分マスタ' ;

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

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 '積算予算書データ' ;

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

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 '積算予算内訳データ明細' ;

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

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);

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

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);

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

create table estimatedatadetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , PageCount decimal(4,0) unsigned not null comment 'ページ番号'
  , LineCount decimal(3,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 estimatedatadetail_PKC primary key (ConstructionCode,PageCount,LineCount)
) comment '積算見積データ明細' ;

create index EstimateDataDetail_Index1
  on estimatedatadetail(ConstructionCode,PageCount);

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

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 '経費率マスタ' ;

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

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);

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

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);

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

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 '完了フラグ'
  , 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) ;

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

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 '資材情報' ;

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

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 '資材品目マスタ' ;

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

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 '資材種類マスタ' ;

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

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 '資材履歴情報' ;

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

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);

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

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);

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

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 '掲示板メッセージ閲覧履歴' ;

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

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

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

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番号'
  , MailAddress varchar(257) comment 'メールアドレス'
  , Note varchar(300) 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 '発注者マスタ' ;

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

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 '受注金額データ' ;

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

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 '支払承認情報データ' ;

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

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 '行番号'
  , 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)
) comment '支払明細データ' ;

create index PayDataDetail_Index1
  on paymentdatadetail(TARGETDATE);

create index PayDataDetail_Index2
  on paymentdatadetail(APPROVALPERSONCODE);

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

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);

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

create table personapproval (
  PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
  , OrderDate date 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 '担当者承認データ' ;

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

create table personapprovalcomment (
  PersonCode decimal(8,0) unsigned not null comment '担当者コード'
  , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
  , OrderDate date 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 '担当者承認コメントデータ' ;

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

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);

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

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 '担当者名'
  , 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 '通勤交通費'
  , 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);

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

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);

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

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 '指示事項データ' ;

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

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 '発注者打ち合わせ議事録データ' ;

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

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 '発注者打ち合わせ議事録データ（出席者）' ;

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

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 '発注者打ち合わせ議事録データ（明細）' ;

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

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 '承認データ' ;

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

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 '承認コメントデータ' ;

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

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 '承認明細データ' ;

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

create table processexcutemaster (
  SecCode decimal(2,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);

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

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 '印刷年月日'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint purchaseorder_PKC primary key (ConstructionCode,SeqNo)
) comment '注文書データ' ;

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

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 '注文書日付データ' ;

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

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);

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

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 'レコード番号管理' ;

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

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 '請求データ' ;

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

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 '請求データ明細' ;

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

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'
  , 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) ;

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

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 '請求書日付データ' ;

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

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);

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

create table securitymaster (
  SecCode decimal(2,0) unsigned not null comment '保護区分番号'
  , DisplayOrder decimal(2,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 '選択背景色'
  , 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);

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

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);

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

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 '安全パトロール明細データ' ;

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

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 '協力業者施工工種登録申請データ' ;

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

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 '協力業者職種リンク登録申請データ' ;

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

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 date 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 '協力業者登録申請データ' ;

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

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 '協力会社工種マスタ' ;

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

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 '協力会社職種マスタ' ;

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

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);

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

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);

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

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 '協力会社名'
  , 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 '支払い通常・手間区分'
  , 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 '協力会社マスタ' ;

create index SubContractorMaster_Index1
  on subcontractormaster(JapaneseSyllabary);

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

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 '仕入先マスタ' ;

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

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 'システム起動確認データ' ;

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

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'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint systemmaster_PKC primary key (SystemCode)
) comment '管理マスタ' ;

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

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);

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

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);

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

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 '工事種別経費率マスタ' ;

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

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);

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

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 '車両マスタ' ;

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

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 '車両記録データ' ;

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

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);

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

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