-- 工事詳細台帳明細データ
DROP TABLE IF EXISTS constructionledgerdetail_OLD;
CREATE TABLE constructionledgerdetail_OLD As Select * from constructionledgerdetail;

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

Insert INTO constructionledgerdetail
SELECT
  ConstructionCode
  , GroupCount
  , LineCount
  , ComponentCode
  , ItemCode
  , FirstString
  , SecondString
  , CompanyType
  , CompanyCode
  , CompanyName
  , EstimatePrice
  , ExecutionAmount
  , AmountConfigRate
  , PaymentBurden
  , FixDataFlg
  , IndependentFlg
  , FluctuationFlg
  , SalaryFlg
  , SalaryDays
  , OperatingFlg
  , SourceCode
  , JoinTitleFlg
  , SalaryOnRegist
  , PurchaseOrderFlg
  , 0
  , EntryDate
  , UpdateDate
FROM constructionledgerdetail_OLD
;

ALTER TABLE purchaseorderdetail 
CHANGE SourceCode SourceCode decimal(10,0) unsigned not null
COMMENT '台帳工事番号';
ALTER TABLE purchaseorderdetail 
CHANGE SourceLineCnt SourceDetailCnt decimal(4,0) unsigned not null
COMMENT '台帳明細番号';

ALTER TABLE constructionledgerexcute 
CHANGE LineCount LineCount decimal(4,0) unsigned not null
comment '行番号';

Truncate table paymentapprovalinfo;
Truncate table paymentdatadetail;
Truncate table billingdata;
Truncate table billingdatadetail;
Truncate table billingpaymentsummary;
commit;