-- 業者請求明細データ
DROP TABLE IF EXISTS billingdatadetail_OLD;
CREATE TABLE billingdatadetail_OLD As Select * from billingdatadetail;

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


Insert INTO billingdatadetail
SELECT
  COMPANYCODE
  , TARGETDATE
  , SEQNO
  , LINECOUNT
  , CONSTRUCTIONCODE
  , 0
  , 0
  , FIELDNAME
  , BILLPRICE
  , HIGHWPRICE
  , HARDWPRICE
  , INDSWASTETAX
  , NOTE
  , ENTRYDATE
  , UPDATEDATE
FROM billingdatadetail_OLD
;
commit;


-- 支払明細データ更新
update paymentdatadetail AS A set A.CNSTRPRICEEXIST = -1 where A.CNSTRPRICEEXIST is null;
commit;


-- 出勤日報データ
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 '明細行番号'
  , ActionResult varchar(30) not null comment '行動実績'
  , StartTime datetime comment '開始時間'
  , CompTime datetime comment '終了時間'
  , DayTimes decimal(4,1) not null comment '日稼働合計時間'
  , WorkingComment varchar(120) comment 'コメント'
  , NightFlg 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(ActionResult);

create index AttendDaily_Index2
  on attendancedailydata(AttendanceDate);


-- 工事施工予算データ
DROP TABLE IF EXISTS constructionbudget_OLD;
CREATE TABLE constructionbudget_OLD As Select * from constructionbudget;

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

Insert INTO constructionbudget
SELECT
  ConstructionCode
  , CreatorCode
  , CreatorName
  , CreatorCosts
  , AssistantCode
  , AssistantName
  , AssistantCosts
  , InstructorCode
  , InstructorName
  , InstructorCosts
  , CreateDate
  , ConstructionTimes
  , ConstructionStart
  , ConstructionEnd
  , InstructorTimes
  , SalaryFlg
  , SalaryDays
  , A_SalaryFlg
  , A_SalaryDays
  , I_SalaryFlg
  , I_SalaryDays
  , 0
  , EntryDate
  , UpdateDate
FROM constructionbudget_OLD
;
commit;