-- 工事基本情報
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 '税込受注決定金額'
  , SalesPersonCode decimal(8,0) comment '営業担当者名'
  , ConstructionPersonCode decimal(8,0) not null comment '工事担当者'
  , ConstrSubPersonCode decimal(8,0) 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 
  , SalesPersonCode 
  , ConstructionPersonCode 
  , ConstrSubPersonCode 
  , 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 
  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')
  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')
  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')
  , EntryDate
  , UpdateDate
FROM constructionbaseinfo_OLD
;

-- 工事基本情報明細
ALTER TABLE constructionbaseinfodetail
CHANGE DetailString DetailString 
varchar(300)
comment '明細内容';
commit;