-- 工事基本情報
DROP TABLE IF EXISTS constructionbaseinfo_OLD;
CREATE TABLE constructionbaseinfo_OLD As Select * from constructionbaseinfo;

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

Insert INTO constructionbaseinfo
Select 
  ConstructionCode
  , TyingFlg
  , JoinFlg
  , ConstructionYear
  , ConstructionPeriod
  , RequestedDate
  , EstimatesSubmitDeadline
  , EstimatesSubmittedDate
  , ProvisionalOrderDate
  , OrderDate
  , OrderStartingDate
  , OrderCompletionDate
  , PreparationStartDate
  , ConstructionStartingDate
  , ConstructionCompletionDate
  , NonOrderDate
  , OrdersDecisionPrice
  , OrdersDecisionPriceInTax
  , SalesDepCode
  , SalesPersonCode
  , 0
  , 0
  , ConstrDepCode
  , ConstructionPersonCode
  , ConstrSubDepCode
  , ConstrSubPersonCode
  , ConstrInstrDepCode
  , ConstructionInstructor
  , TransferConstruction
  , TransferConstructionDate
  , OrderersDivision
  , OrderersCode
  , EstimatesExpirationDate
  , ConstructionPeriodStart
  , ConstructionPeriodEnd
  , ConstructionPeriodStart2
  , ConstructionPeriodEnd2
  , StartDate
  , EndDate
  , PurchaseOrderMailingDate
  , PurchaseOrderReturnDate
  , PurchaseOrderReturnCheckDate
  , ConsumptionTaxFlg
  , PrimeContractorFlg
  , SalesCostFlg
  , ConstructionStatusFlg
  , ConstructionType
  , EstimateType
  , BillingStartDate
  , BillingDate
  , BillingSplitFlg
  , BillingSendDate
  , LedgerComplateDate
  , InspectPlanningDate
  , InspectExecuteDate
  , RevCompleteDate
  , EntryDate
  , UpdateDate
From constructionbaseinfo_OLD;

-- 年マスタ
DROP TABLE IF EXISTS yearmaster_OLD;
CREATE TABLE yearmaster_OLD As Select * from yearmaster;

drop table if exists yearmaster cascade;

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

Insert INTO yearmaster
Select * From yearmaster_OLD;


-- 月マスタ
DROP TABLE IF EXISTS monthmaster_OLD;
CREATE TABLE monthmaster_OLD As Select * from monthmaster;

drop table if exists monthmaster cascade;

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

Insert INTO monthmaster
Select * From monthmaster_OLD;


-- 日マスタ
DROP TABLE IF EXISTS daymaster_OLD;
CREATE TABLE daymaster_OLD As Select * from daymaster;

drop table if exists daymaster cascade;

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

Insert INTO daymaster
Select * From daymaster_OLD;

commit;