工事管理システム製品化対応

-- ?H????????

DROP TABLE IF EXISTS constructionmaterialinfo_OLD;

CREATE TABLE constructionmaterialinfo_OLD As Select * from constructionmaterialinfo ;

drop table if exists constructionmaterialinfo cascade;

create table constructionmaterialinfo (

  CONSTRUCTIONCODE int(10) unsigned default 0 not null comment '?H?????'

  , MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '????i??R?[?h'

  , PROCESSDATE	datetime comment '?????N????'

  , RENTCOUNT smallint(5) unsigned comment '??o??'

  , REPAYCOUNT smallint(5) unsigned comment '??p??'

  , JUNKCOUNT smallint(5) unsigned comment '?j????'

  , COMPLETEFLG tinyint(3) unsigned comment '?????t???O'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint constructionmaterialinfo_PKC primary key (CONSTRUCTIONCODE,MATERIALITEMCODE,PROCESSDATE)

) comment '?H????????' ;

Insert INTO constructionmaterialinfo

SELECT

  CONSTRUCTIONCODE

  , MATERIALITEMCODE

  , DATE(ENTRYDATE)

  , RENTCOUNT

  , REPAYCOUNT

  , 0

  , COMPLETEFLG

  , ENTRYDATE

  , UPDATEDATE

FROM constructionmaterialinfo_OLD

;

commit;

-- ?H??????????f?[?^

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 '?H???R?[?h'

  , GroupCount decimal(2,0) unsigned not null comment '?O???[?v???'

  , LineCount decimal(4,0) unsigned not null comment '?s???'

  , ComponentCode decimal(5,0) unsigned not null comment '?\???L?['

  , ItemCode decimal(5,0) unsigned not null comment '?H??L?['

  , FirstString varchar(120) comment '???????'

  , SecondString varchar(120) comment '?H?????e'

  , CompanyType decimal(1,0) not null comment '??????R?[?h?^?C?v'

  , CompanyCode decimal(8,0) not null comment '??????R?[?h'

  , CompanyName varchar(120) comment '?????????'

  , EstimatePrice decimal(12,0) not null comment '?\?Z?i????j???z'

  , ExecutionAmount decimal(12,0) not null comment '???s???z'

  , AmountConfigRate decimal(5,2) not null comment '???z?\????'

  , PaymentBurden decimal(12,0) not null comment '?x????U?z'

  , FixDataFlg decimal(1,0) not null comment '???f?[?^?t???O'

  , IndependentFlg decimal(1,0) not null comment '????f?[?^?t???O'

  , FluctuationFlg decimal(1,0) not null comment '?????f?[?^?t???O'

  , SalaryFlg decimal(1,0) not null comment '???^?U????'

  , SalaryDays decimal(4,0) not null comment '???^?U??????'

  , OperatingFlg decimal(1,0) not null comment '?S?????t???O'

  , SourceCode decimal(10,0) unsigned not null comment '???H?????'

  , JoinTitleFlg decimal(1,0) not null comment '?H??????^?C?g??'

  , SalaryOnRegist decimal(12,0) not null comment '?o?^?????z???^'

  , PurchaseOrderFlg decimal(2,0) not null comment '?????????s?t???O'

  , DetailCount decimal(4,0) unsigned not null comment '??????'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint constructionledgerdetail_PKC primary key (ConstructionCode,GroupCount,LineCount)

) comment '?H??????????f?[?^' ;

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 '???H?????';

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 '?s???';

Truncate table paymentapprovalinfo;

Truncate table paymentdatadetail;

Truncate table billingdata;

Truncate table billingdatadetail;

Truncate table billingpaymentsummary;

commit;

-- ?H??????????f?[?^

drop table if exists PeriodAvoidance cascade;

create table PeriodAvoidance (

  ConstructionCode decimal(10,0) unsigned not null comment '?H???R?[?h'

  , ConstructionStatusFlg decimal(2,0) not null comment '?H?????t???O'

  , FieldNo decimal(2,0) not null comment '?`?F?b?N???t?B?[???h???'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint PeriodAvoidance_PKC primary key (ConstructionCode,ConstructionStatusFlg,FieldNo)

) comment '?H??????????f?[?^' ;

create index PeriodAvoidance_Index1

  on PeriodAvoidance(ConstructionCode);

create index PeriodAvoidance_Index2

  on PeriodAvoidance(ConstructionStatusFlg);

create index PeriodAvoidance_Index3

  on PeriodAvoidance(FieldNo);

-- ?x??????f?[?^

create index PayDataDetail_Index1

  on paymentdatadetail(TARGETDATE);

create index PayDataDetail_Index2

  on paymentdatadetail(APPROVALPERSONCODE);

-- ?H????{???

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 '?H???R?[?h'

  , TyingFlg decimal(1,0) not null comment '?R?t?f?[?^?t???O'

  , JoinFlg decimal(1,0) not null comment '?????_??H???t???O'

  , ConstructionYear smallint(5) unsigned not null comment '?H???N?x'

  , ConstructionPeriod smallint(5) unsigned not null comment '?H??????'

  , RequestedDate date comment '??????'

  , EstimatesSubmitDeadline date comment '?????o????'

  , EstimatesSubmittedDate date comment '?????o??'

  , ProvisionalOrderDate date comment '?????'

  , OrderDate date comment '???'

  , OrderStartingDate date comment '?J?n?\???'

  , OrderCompletionDate date comment '?????\???'

  , PreparationStartDate date comment '?{?H?????J?n??'

  , ConstructionStartingDate date comment '?{?H?J?n??'

  , ConstructionCompletionDate date comment '?{?H??????'

  , NonOrderDate date comment '????'

  , OrdersDecisionPrice decimal(12,0) not null comment '??????????z'

  , OrdersDecisionPriceInTax decimal(12,0) not null comment '??????????z'

  , SalesPersonCode decimal(8,0) comment '?c??S?????'

  , ConstructionPersonCode decimal(8,0) not null comment '?H???S????'

  , ConstrSubPersonCode decimal(8,0) not null comment '?H?????S????'

  , ConstructionInstructor decimal(8,0) not null comment '?H???w????'

  , TransferConstruction decimal(8,0) not null comment '?H??????R?[?h'

  , TransferConstructionDate date comment '?H??????'

  , OrderersDivision decimal(5,0) not null comment '???????'

  , OrderersCode decimal(5,0) unsigned not null comment '??????R?[?h'

  , EstimatesExpirationDate date comment '????L??????'

  , ConstructionPeriodStart date comment '?_??H???J?n??'

  , ConstructionPeriodEnd date comment '?_??H??????'

  , ConstructionPeriodStart2 date comment '?_??H???J?n?i???????j'

  , ConstructionPeriodEnd2 date comment '?_??H???????i???????j'

  , StartDate date comment '?H???J?n??'

  , EndDate date comment '?H???I????'

  , PurchaseOrderMailingDate date comment '???????X????'

  , PurchaseOrderReturnDate date comment '???????????'

  , PurchaseOrderReturnCheckDate date comment '??????????m?F??'

  , ConsumptionTaxFlg decimal(1,0) not null comment '?????t???O'

  , PrimeContractorFlg decimal(1,0) not null comment '?????t???O'

  , SalesCostFlg decimal(1,0) not null comment '?c??o?????t???O'

  , ConstructionStatusFlg decimal(2,0) not null comment '?H?????t???O'

  , ConstructionType decimal(5,0) not null comment '?H?????R?[?h'

  , EstimateType decimal(1,0) not null comment '??????'

  , BillingStartDate date not null comment '?????????J?n??'

  , BillingDate date not null comment '??????'

  , BillingSplitFlg decimal(1,0) not null comment '??????t???O'

  , BillingSendDate date comment '???????X??/??n????'

  , LedgerComplateDate date not null comment '?H????????????????t'

  , InspectPlanningDate date not null comment '?????\???'

  , InspectExecuteDate date not null comment '???????s??'

  , RevCompleteDate date not null comment '??????????????'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint constructionbaseinfo_PKC primary key (ConstructionCode)

) comment '?H????{???' ;

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

;

-- ?H????{????

ALTER TABLE constructionbaseinfodetail

CHANGE DetailString DetailString 

varchar(300)

comment '??????e';

commit;

-- ????i?????t?f?[?^

DROP TABLE IF EXISTS constructionprogressdate_OLD; 

CREATE TABLE constructionprogressdate_OLD As 

Select

  * 

from

  constructionprogressdate; 

drop table if exists constructionprogressdate cascade; 

create table constructionprogressdate( 

  ConstructionCode decimal (10, 0) not null comment '?H?????'

  , ConstructionStatusFlg decimal (2, 0) not null comment '???H?????'

  , PreviousStatusFlg decimal (2, 0) comment '??X?O???'

  , ChangeDate datetime not null comment '?????t'

  , ChangePersonCode decimal (8, 0) not null comment '???S????'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint constructionprogressdate_PKC primary key (ConstructionCode, ChangeDate)

) comment '????i?????t?f?[?^'; 

create index ConstrProg_Index1 

  on constructionprogressdate(ConstructionCode); 

create index ConstrProg_Index2 

  on constructionprogressdate(ConstructionStatusFlg); 

create index ConstrProg_Index3 

  on constructionprogressdate(ChangeDate); 

Insert 

INTO constructionprogressdate 

Select

  * 

from

  constructionprogressdate_OLD; 

-- ?H???????o??f?[?^

drop table if exists ConstrLedgerExpenses cascade;

create table ConstrLedgerExpenses (

  ConstructionCode decimal(10,0) unsigned not null comment '?H???R?[?h'

  , SeqNo decimal(5,0) not null comment '?}??'

  , NameCode decimal(5,0) unsigned not null comment '?o??R?[?h'

  , ExpensesRaito decimal(4,2) not null comment '?o??'

  , ExpensesValue decimal(10,0) not null comment '?o????z'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint ConstrLedgerExpenses_PKC primary key (ConstructionCode, SeqNo, NameCode)

) comment '?H???????o??f?[?^' ;

create index ConstrLedgerExpenses_Index1

  on ConstrLedgerExpenses(ConstructionCode);

create index ConstrLedgerExpenses_Index2

  on ConstrLedgerExpenses(SeqNo);

create index ConstrLedgerExpenses_Index3

  on ConstrLedgerExpenses(NameCode);

Insert 

INTO constrledgerexpenses 

select

  A.ConstructionCode

  , 1

  , A.ComponentCode

  , A.AmountConfigRate

  , A.ExecutionAmount 

  , A.EntryDate

  , A.UpdateDate

from

  constructionledgerdetail As A 

where

  A.GroupCount = 1 

  And A.ComponentCode = 3; 

Insert 

INTO constrledgerexpenses 

select

  A.ConstructionCode

  , 2

  , A.ComponentCode

  , A.AmountConfigRate

  , A.ExecutionAmount 

  , A.EntryDate

  , A.UpdateDate

from

  constructionledgerdetail As A 

where

  A.GroupCount = 1 

  And A.ComponentCode = 1; 

Insert 

INTO constrledgerexpenses 

select

  A.ConstructionCode

  , 3

  , A.ComponentCode

  , A.AmountConfigRate

  , A.ExecutionAmount 

  , A.EntryDate

  , A.UpdateDate

from

  constructionledgerdetail As A 

where

  A.GroupCount = 1 

  And A.ComponentCode = 2; 

-- ?????o???}?X?^

DROP TABLE IF EXISTS departmentexpensesmaster_OLD; 

CREATE TABLE departmentexpensesmaster_OLD As 

Select

  * 

from

  departmentexpensesmaster; 

drop table if exists departmentexpensesmaster cascade;

create table departmentexpensesmaster (

  DepartmentCode decimal(5,0) unsigned not null comment '?????R?[?h'

  , ExpensesPeriod smallint(6) not null comment '?????'

  , NameCode decimal(5,0) unsigned not null comment '?o??R?[?h'

  , NameString varchar(100) not null comment '????'

  , DisplayOrder decimal(5,0) unsigned not null comment '?\????'

  , ExpensesRaito decimal(4,2) not null comment '?o??'

  , DeleteFlg decimal(1,0) not null comment '???t???O'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint departmentexpensesmaster_PKC primary key (DepartmentCode,ExpensesPeriod,NameCode)

) comment '?????o???}?X?^' ;

Insert 

INTO departmentexpensesmaster 

select

  A.DepartmentCode

  , A.ExpensesPeriod

  , A.NameCode

  , B.NameString

  , A.DisplayOrder

  , A.ExpensesRaito

  , A.DeleteFlg

  , A.EntryDate

  , A.UpdateDate 

from

  departmentexpensesmaster_OLD As A 

  LEFT JOIN divisionmaster AS B 

    ON B.DivisionCode = 3 

    And B.NameCode = A.NameCode; 

commit;

create index constructionledger_Index1

  on constructionledger(ConstructionStart);

create index constructionledger_Index2

  on constructionledger(ConstructionEnd);

create index LedgerDetail_Index06

  on constructionledgerdetail(SalaryFlg);

create index LedgerDetail_Index07

  on constructionledgerdetail(CompanyCode);

-- ???????f?[?^

DROP TABLE IF EXISTS billingdata_OLD;

CREATE TABLE billingdata_OLD As Select * from billingdata;

drop table if exists billingdata cascade;

create table billingdata (

  COMPANYCODE decimal(8,0) default '0' not null comment '??????R?[?h'

  , TARGETDATE decimal(6,0) default '0' not null comment '???N??'

  , PAYMENTKIND decimal(1,0) default '0' not null comment '?x????'

  , SEQNO decimal(3,0) default '0' not null comment '?A??'

  , BILLPRICE decimal(10,0) comment '???????z'

  , ENTRYDATE datetime comment '?o?^?N????'

  , UPDATEDATE datetime comment '?X?V?N????'

  , constraint billingdata_PKC primary key (COMPANYCODE,TARGETDATE,PAYMENTKIND,SEQNO)

) comment '???????f?[?^' ;

Insert INTO billingdata

SELECT

  COMPANYCODE

  , TARGETDATE

  , 0

  , SEQNO

  , BILLPRICE

  , ENTRYDATE

  , UPDATEDATE

FROM billingdata_OLD

;

commit;

-- ??????????f?[?^

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 '??????R?[?h'

  , TARGETDATE decimal(6,0) default '0' not null comment '???N??'

  , PAYMENTKIND decimal(1,0) default '0' not null comment '?x????'

  , SEQNO decimal(3,0) default '0' not null comment '?A??'

  , LINECOUNT decimal(3,0) default '0' not null comment '?s???'

  , CONSTRUCTIONCODE decimal(10,0) comment '?H?????'

  , CONSTRUCTIONROWCNT decimal(3,0) default '0' not null comment '?s???'

  , CONSTRUCTIONCOLCNT decimal(3,0) default '0' not null comment '????'

  , FIELDNAME varchar(120) comment '????'

  , BILLPRICE decimal(10,0) comment '???????z'

  , HIGHWPRICE decimal(10,0) comment '??????'

  , HARDWPRICE decimal(10,0) comment '??????'

  , INDSWASTETAX decimal(10,0) comment '?Y?p??'

  , NOTE varchar(120) comment '???l'

  , ENTRYDATE datetime comment '?o?^?N????'

  , UPDATEDATE datetime comment '?X?V?N????'

  , constraint billingdatadetail_PKC primary key (COMPANYCODE,TARGETDATE,PAYMENTKIND,SEQNO,LINECOUNT)

) comment '??????????f?[?^' ;

Insert INTO billingdatadetail

SELECT

  COMPANYCODE

  , TARGETDATE

  , 0

  , SEQNO

  , LINECOUNT

  , CONSTRUCTIONCODE

  , CONSTRUCTIONROWCNT

  , CONSTRUCTIONCOLCNT

  , FIELDNAME

  , BILLPRICE

  , HIGHWPRICE

  , HARDWPRICE

  , INDSWASTETAX

  , NOTE

  , ENTRYDATE

  , UPDATEDATE

FROM billingdatadetail_OLD

;

commit;

DROP TABLE IF EXISTS PurchaseOrderDate;

-- ?????????t?f?[?^

CREATE TABLE PurchaseOrderDate

(

	ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '?H???R?[?h',

	SeqNo decimal(3) unsigned NOT NULL COMMENT '???????}??',

	CompanyCode decimal(8,0) NOT NULL COMMENT '??????R?[?h',

	SendDate date NOT NULL COMMENT '???????X????',

	ReturnDate date NOT NULL COMMENT '???????????',

	ReturnCheckDate date NOT NULL COMMENT '??????????m?F??',

	EntryDate datetime NOT NULL COMMENT '?o?^???t',

	UpdateDate datetime NOT NULL COMMENT '?X?V???t',

	PRIMARY KEY (ConstructionCode, SeqNo)

) COMMENT = '?????????t?f?[?^';

DROP TABLE IF EXISTS RequestOrderDate;

-- ?????????t?f?[?^

CREATE TABLE RequestOrderDate

(

	ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '?H???R?[?h',

	InvoiceNo decimal(9,0) NOT NULL COMMENT '??????No',

	SendDate date NOT NULL COMMENT '???????????X????',

	EntryDate datetime NOT NULL COMMENT '?o?^???t',

	UpdateDate datetime NOT NULL COMMENT '?X?V???t'

) COMMENT = '?????????t?f?[?^';

-- ?S????????????f?[?^

drop table if exists ChgChargeDep cascade;

create table ChgChargeDep (

  PersonCode  decimal(8,0) not null comment '?S????R?[?h'

  , StartDate  date not null comment '?J?n?N????'

  , CompDate  date not null comment '?I???N????'

  , DepartmentCode decimal(5,0) unsigned not null comment '?????R?[?h'

  , DepartmentName varchar(60) comment '??????'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint ChgChargeDep_PKC primary key (PersonCode, StartDate)

) comment '?S????????????f?[?^' ;

Insert INTO ChgChargeDep

select

  A.PersonCode

  , A.StartDate

  , A.EndDate

  , A.DepartmentCode

  , B.DepartmentString

  , Now()

  , Now() 

From

  personinchargemaster As A

  Left Join departmentmaster As B

  On B.DepartmentCode = A.DepartmentCode

;

update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 2, DepartmentName = '?c?U??' Where PersonCode = 20150701 And DATE(StartDate) = DATE('2015/07/01');

Insert chgchargedep Value(20150701, DATE('2017/08/01'), DATE('0001/01/01'), 1, '?H????', NOW(), NOW());

update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 1, DepartmentName = '?H????' Where PersonCode = 20160602 And DATE(StartDate) = DATE('2016/06/20');

Insert chgchargedep Value(20160602, DATE('2017/08/01'), DATE('0001/01/01'), 3, '?c???', NOW(), NOW());

update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 1, DepartmentName = '?H????' Where PersonCode = 20160701 And DATE(StartDate) = DATE('2016/07/01');

Insert chgchargedep Value(20160701, DATE('2017/08/01'), DATE('0001/01/01'), 2, '?c?U??', NOW(), NOW());

commit;

-- ??????????f?[?^

DROP TABLE IF EXISTS purchaseorderdetail_OLD;

CREATE TABLE purchaseorderdetail_OLD As Select * from purchaseorderdetail;

drop table if exists purchaseorderdetail cascade;

create table purchaseorderdetail (

  ConstructionCode decimal(10,0) unsigned not null comment '?H???R?[?h'

  , SeqNo decimal(3,0) unsigned not null comment '???????}??'

  , GroupCount decimal(1,0) not null comment '?O???[?v???'

  , LineCount decimal(3,0) unsigned not null comment '?s???'

  , ComponentCode decimal(5,0) unsigned not null comment '?\???L?['

  , ItemCode decimal(5,0) unsigned not null comment '?H??L?['

  , FirstString varchar(120) comment '?H??E?i??'

  , SecondString varchar(120) comment '???e????'

  , UnitCount decimal(8,2) not null comment '????'

  , UnitName varchar(30) comment '?P??'

  , UnitPrice decimal(10,0) not null comment '?P??'

  , OrdersLinePrice decimal(12,0) comment '???z'

  , SourceCode decimal(10,0) unsigned not null comment '?\?Z?????H?????'

  , SourceLineCnt decimal(3,0) unsigned not null comment '?????????????s???'

  , DataTypeFlag decimal(1,0) unsigned not null comment '?f?[?^?^?C?v?t???O'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint purchaseorderdetail_PKC primary key (ConstructionCode,SeqNo,GroupCount,LineCount)

) comment '??????????f?[?^' ;

create index POrderDetail_Index1

  on purchaseorderdetail(SourceCode);

Insert INTO purchaseorderdetail

SELECT

  ConstructionCode

  , SeqNo

  , GroupCount

  , LineCount

  , ComponentCode

  , ItemCode

  , FirstString

  , SecondString

  , UnitCount

  , UnitName

  , UnitPrice

  , OrdersLinePrice

  , SourceCode

  , SourceLineCnt

  , 0

  , EntryDate

  , UpdateDate

FROM purchaseorderdetail_OLD

;

update purchaseorderdetail set DataTypeFlag = 1 where FirstString like '%?@??%';

commit;

-- ?H????{???

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 '?H???R?[?h'

  , TyingFlg decimal(1,0) not null comment '?R?t?f?[?^?t???O'

  , JoinFlg decimal(1,0) not null comment '?????_??H???t???O'

  , ConstructionYear smallint(5) unsigned not null comment '?H???N?x'

  , ConstructionPeriod smallint(5) unsigned not null comment '?H??????'

  , RequestedDate date comment '??????'

  , EstimatesSubmitDeadline date comment '?????o????'

  , EstimatesSubmittedDate date comment '?????o??'

  , ProvisionalOrderDate date comment '?????'

  , OrderDate date comment '???'

  , OrderStartingDate date comment '?J?n?\???'

  , OrderCompletionDate date comment '?????\???'

  , PreparationStartDate date comment '?{?H?????J?n??'

  , ConstructionStartingDate date comment '?{?H?J?n??'

  , ConstructionCompletionDate date comment '?{?H??????'

  , NonOrderDate date comment '????'

  , OrdersDecisionPrice decimal(12,0) not null comment '??????????z'

  , OrdersDecisionPriceInTax decimal(12,0) not null comment '??????????z'

  , SalesDepCode decimal(5,0) unsigned not null comment '?c??S????????R?[?h'

  , SalesPersonCode decimal(8,0) unsigned not null comment '?c??S????R?[?h'

  , SalesSubDepCode decimal(5,0) unsigned not null comment '?c????S????????R?[?h'

  , SalesSubPersonCode decimal(8,0) unsigned not null comment '?c????S????R?[?h'

  , ConstrDepCode decimal(5,0) unsigned not null comment '?H???S????????R?[?h'

  , ConstructionPersonCode decimal(8,0) not null comment '?H???S????R?[?h'

  , ConstrSubDepCode decimal(5,0) unsigned not null comment '?H?????S????????R?[?h'

  , ConstrSubPersonCode decimal(8,0) not null comment '?H?????S????R?[?h'

  , ConstrInstrDepCode decimal(5,0) unsigned not null comment '?H???w?????????R?[?h'

  , ConstructionInstructor decimal(8,0) not null comment '?H???w?????R?[?h'

  , TransferConstruction decimal(8,0) not null comment '?H??????R?[?h'

  , TransferConstructionDate date comment '?H??????'

  , OrderersDivision decimal(5,0) not null comment '????????R?[?h'

  , OrderersCode decimal(5,0) unsigned not null comment '??????R?[?h'

  , EstimatesExpirationDate date comment '????L??????'

  , ConstructionPeriodStart date comment '?_??H???J?n??'

  , ConstructionPeriodEnd date comment '?_??H??????'

  , ConstructionPeriodStart2 date comment '?_??H???J?n?i???????j'

  , ConstructionPeriodEnd2 date comment '?_??H???????i???????j'

  , StartDate date comment '?H???J?n??'

  , EndDate date comment '?H???I????'

  , PurchaseOrderMailingDate date comment '???????X????'

  , PurchaseOrderReturnDate date comment '???????????'

  , PurchaseOrderReturnCheckDate date comment '??????????m?F??'

  , ConsumptionTaxFlg decimal(1,0) not null comment '?????t???O'

  , PrimeContractorFlg decimal(1,0) not null comment '?????t???O'

  , SalesCostFlg decimal(1,0) not null comment '?c??o?????t???O'

  , ConstructionStatusFlg decimal(2,0) not null comment '?H?????t???O'

  , ConstructionType decimal(5,0) not null comment '?H?????R?[?h'

  , EstimateType decimal(1,0) not null comment '??????'

  , BillingStartDate date not null comment '?????????J?n??'

  , BillingDate date not null comment '??????'

  , BillingSplitFlg decimal(1,0) not null comment '??????t???O'

  , BillingSendDate date comment '???????X??/??n????'

  , LedgerComplateDate date not null comment '?H????????????????t'

  , InspectPlanningDate date not null comment '?????\???'

  , InspectExecuteDate date not null comment '???????s??'

  , RevCompleteDate date not null comment '??????????????'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint constructionbaseinfo_PKC primary key (ConstructionCode)

) comment '?H????{???' ;

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;

-- ?N?}?X?^

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 '?N'

  , constraint yearmaster_PKC primary key (years)

) comment '?N?}?X?^' ;

Insert INTO yearmaster

Select * From yearmaster_OLD;

-- ???}?X?^

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 '???}?X?^' ;

Insert INTO monthmaster

Select * From monthmaster_OLD;

-- ???}?X?^

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 '???}?X?^' ;

Insert INTO daymaster

Select * From daymaster_OLD;

commit;

DROP TABLE IF EXISTS personinchargemaster_OLD;

CREATE TABLE personinchargemaster_OLD As Select * from personinchargemaster;

drop index PersonInChargeMaster_Index1 on personinchargemaster;

drop index PersonInChargeMaster_Index2 on personinchargemaster;

drop table if exists personinchargemaster cascade;

create table personinchargemaster (

  PersonCode decimal(8,0) unsigned not null comment '?S????R?[?h'

  , DisplayOrder decimal(8,0) unsigned not null comment '?\????'

  , PersonName varchar(60) not null comment '?S?????'

  , StartDate date not null comment '?g?p?J?n?N????'

  , EndDate date comment '?g?p?I???N????'

  , PassWord varchar(16) comment '?p?X???[?h'

  , SecurityManagement decimal(1,0) not null comment '?@????'

  , SecCode decimal(2,0) unsigned not null comment '???????'

  , MsgBackColor varchar(8) comment '???b?Z?[?W?w?i?F'

  , DisplayString varchar(20) comment '?\???E???????'

  , DepartmentCode decimal(5,0) unsigned comment '?????R?[?h'

  , MonthlySalary decimal(11,0) not null comment '???????z'

  , YearSalary decimal(12,0) not null comment '?N????z'

  , Qualification varchar(120) comment '???i?????'

  , SealPrintName varchar(5) comment '???\????'

  , EmployeeClassFlg decimal(1,0) not null comment '?????'

  , LedgerFlg decimal(1,0) not null comment '???v?Z???t???O'

  , CommutingDistance decimal(5,2) not null comment '??????'

  , CommuteCosts decimal(5,0) not null comment '??????'

  , DeleteFlg decimal(1,0) not null comment '???t???O'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint personinchargemaster_PKC primary key (PersonCode)

) comment '?S????}?X?^' ;

create index PersonInChargeMaster_Index1

  on personinchargemaster(StartDate);

create index PersonInChargeMaster_Index2

  on personinchargemaster(DepartmentCode);

Insert INTO personinchargemaster

SELECT

  PersonCode

  , DisplayOrder

  , PersonName

  , StartDate

  , EndDate

  , PassWord

  , SecurityManagement

  , SecCode

  , MsgBackColor

  , DisplayString

  , DepartmentCode

  , MonthlySalary

  , YearSalary

  , Qualification

  , SealPrintName

  , EmployeeClassFlg

  , LedgerFlg

  , 0

  , 0

  , DeleteFlg

  , EntryDate

  , UpdateDate

FROM personinchargemaster_OLD

;

commit;

DROP TABLE IF EXISTS SystemMaster_OLD;

CREATE TABLE SystemMaster_OLD As Select * from SystemMaster;

DROP TABLE IF EXISTS SystemMaster;

-- ????}?X?^

CREATE TABLE SystemMaster

(

  SystemCode decimal(3,0) unsigned not null comment '???R?[?h'

  , CompanyName1 varchar(60) not null comment '?????P'

  , CompanyName2 varchar(60) comment '?????Q'

  , CEOName varchar(60) not null comment '??\?????'

  , CEOPositionName varchar(60) comment '??\???E??'

  , ZipCode varchar(8) not null comment '?X????'

  , Address1 varchar(60) not null comment '?Z???P'

  , Address2 varchar(60) comment '?Z??2'

  , Address3 varchar(60) comment '?Z??3'

  , PhoneNumber varchar(13) not null comment '?d?b???'

  , FaxNumber varchar(13) comment 'FAX???'

  , HomePageURL varchar(120) comment '?z?[???y?[?WURL'

  , ConstructionExpenses decimal(4,2) not null comment '?H?????o??'

  , OfficeExpenses decimal(4,2) not null comment '???????o??'

  , OperatingExpenses decimal(4,2) not null comment '?c??o??'

  , 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 '?R???L???P??'

  , AreaDistance decimal(5,2) NOT NULL COMMENT '????u????????'

  , ExcelSavePath varchar(120) comment 'Excel????p?X'

  , BusinessPeriod smallint(6) not null comment '????c?????'

  , ConstructionYear smallint(6) not null comment '????H???N?x'

  , BusinessBeginningDate varchar(5) not null comment '?c????????'

  , ConstructionBeginningDate varchar(5) not null comment '?H???N?x?????'

  , ConstructionNoBase decimal(1,0) not null comment '?H????????l'

  , SloganString1 varchar(100) comment '?W??1'

  , SloganString2 varchar(100) comment '?W??2'

  , SloganString3 varchar(100) comment '?W??3'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint systemmaster_PKC primary key (SystemCode)

) COMMENT = '????}?X?^';

Insert INTO SystemMaster

SELECT

  SystemCode

  , CompanyName1

  , CompanyName2

  , CEOName

  , CEOPositionName

  , ZipCode

  , Address1

  , Address2

  , Address3

  , PhoneNumber

  , FaxNumber

  , HomePageURL

  , ConstructionExpenses

  , OfficeExpenses

  , OperatingExpenses

  , ConsumptionTax

  , CooperationRate

  , StatutoryWelfareRate

  , 0

  , 0

  , ExcelSavePath

  , BusinessPeriod

  , ConstructionYear

  , BusinessBeginningDate

  , ConstructionBeginningDate

  , ConstructionNoBase

  , SloganString1

  , SloganString2

  , SloganString3

  , EntryDate

  , UpdateDate

FROM SystemMaster_OLD

;

commit;

insert 

into approvalmaster( 

  SELECT

    11

    , DepartmentCode

    , SeqNo

    , DisplayOrder

    , ApprovalPerson

    , ApprovalAuthority

    , EntryDate

    , UpdateDate 

  FROM

    approvalmaster 

  where

    ApprovalCode = 99

); 

insert 

into approvalmaster( 

  SELECT

    12

    , DepartmentCode

    , SeqNo

    , DisplayOrder

    , ApprovalPerson

    , ApprovalAuthority

    , EntryDate

    , UpdateDate 

  FROM

    approvalmaster 

  where

    ApprovalCode = 99

); 

delete 

from

  approvalmaster 

where

  ApprovalCode >= 98; 

commit; 

-- ?H???S???????f?[?^

drop table if exists ConstrChgCharge cascade;

create table ConstrChgCharge (

  ConstructionCode decimal(10,0) unsigned not null comment '?H???R?[?h'

  , Division decimal(1,0) not null comment '????'

  , SeqNo smallint(5) unsigned not null comment '?A??'

  , DepartmentCode decimal(5,0) unsigned not null comment '?????R?[?h'

  , PersonCode  decimal(8,0) not null comment '?S????R?[?h'

  , StartDate  date comment '?S???J?n?N????'

  , CompDate  date comment '?S???I???N????'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint ConstrChgCharge_PKC primary key (ConstructionCode, Division, SeqNo)

) comment '?H???S???????f?[?^' ;

-- ?H????{???

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 '?H???R?[?h'

  , TyingFlg decimal(1,0) not null comment '?R?t?f?[?^?t???O'

  , JoinFlg decimal(1,0) not null comment '?????_??H???t???O'

  , ConstructionYear smallint(5) unsigned not null comment '?H???N?x'

  , ConstructionPeriod smallint(5) unsigned not null comment '?H??????'

  , RequestedDate date comment '??????'

  , EstimatesSubmitDeadline date comment '?????o????'

  , EstimatesSubmittedDate date comment '?????o??'

  , ProvisionalOrderDate date comment '?????'

  , OrderDate date comment '???'

  , OrderStartingDate date comment '?J?n?\???'

  , OrderCompletionDate date comment '?????\???'

  , PreparationStartDate date comment '?{?H?????J?n??'

  , ConstructionStartingDate date comment '?{?H?J?n??'

  , ConstructionCompletionDate date comment '?{?H??????'

  , NonOrderDate date comment '????'

  , OrdersDecisionPrice decimal(12,0) not null comment '??????????z'

  , OrdersDecisionPriceInTax decimal(12,0) not null comment '??????????z'

  , SalesDepCode decimal(5,0) unsigned not null comment '?c??S????????R?[?h'

  , SalesPersonCode decimal(8,0) unsigned not null comment '?c??S????R?[?h'

  , ConstrDepCode decimal(5,0) unsigned not null comment '?H???S????????R?[?h'

  , ConstructionPersonCode decimal(8,0) not null comment '?H???S????R?[?h'

  , ConstrSubDepCode decimal(5,0) unsigned not null comment '?H?????S????????R?[?h'

  , ConstrSubPersonCode decimal(8,0) not null comment '?H?????S????R?[?h'

  , ConstrInstrDepCode decimal(5,0) unsigned not null comment '?H???w?????????R?[?h'

  , ConstructionInstructor decimal(8,0) not null comment '?H???w?????R?[?h'

  , TransferConstruction decimal(8,0) not null comment '?H??????R?[?h'

  , TransferConstructionDate date comment '?H??????'

  , OrderersDivision decimal(5,0) not null comment '????????R?[?h'

  , OrderersCode decimal(5,0) unsigned not null comment '??????R?[?h'

  , EstimatesExpirationDate date comment '????L??????'

  , ConstructionPeriodStart date comment '?_??H???J?n??'

  , ConstructionPeriodEnd date comment '?_??H??????'

  , ConstructionPeriodStart2 date comment '?_??H???J?n?i???????j'

  , ConstructionPeriodEnd2 date comment '?_??H???????i???????j'

  , StartDate date comment '?H???J?n??'

  , EndDate date comment '?H???I????'

  , PurchaseOrderMailingDate date comment '???????X????'

  , PurchaseOrderReturnDate date comment '???????????'

  , PurchaseOrderReturnCheckDate date comment '??????????m?F??'

  , ConsumptionTaxFlg decimal(1,0) not null comment '?????t???O'

  , PrimeContractorFlg decimal(1,0) not null comment '?????t???O'

  , SalesCostFlg decimal(1,0) not null comment '?c??o?????t???O'

  , ConstructionStatusFlg decimal(2,0) not null comment '?H?????t???O'

  , ConstructionType decimal(5,0) not null comment '?H?????R?[?h'

  , EstimateType decimal(1,0) not null comment '??????'

  , BillingStartDate date not null comment '?????????J?n??'

  , BillingDate date not null comment '??????'

  , BillingSplitFlg decimal(1,0) not null comment '??????t???O'

  , BillingSendDate date comment '???????X??/??n????'

  , LedgerComplateDate date not null comment '?H????????????????t'

  , InspectPlanningDate date not null comment '?????\???'

  , InspectExecuteDate date not null comment '???????s??'

  , RevCompleteDate date not null comment '??????????????'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint constructionbaseinfo_PKC primary key (ConstructionCode)

) comment '?H????{???' ;

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

  A.ConstructionCode

  , A.TyingFlg

  , A.JoinFlg

  , A.ConstructionYear

  , A.ConstructionPeriod

  , A.RequestedDate

  , A.EstimatesSubmitDeadline

  , A.EstimatesSubmittedDate

  , A.ProvisionalOrderDate

  , A.OrderDate

  , A.OrderStartingDate

  , A.OrderCompletionDate

  , A.PreparationStartDate

  , A.ConstructionStartingDate

  , A.ConstructionCompletionDate

  , A.NonOrderDate

  , A.OrdersDecisionPrice

  , A.OrdersDecisionPriceInTax

  , B.DepartmentCode

  , A.SalesPersonCode

  , ifnull(C.DepartmentCode, 0)

  , A.ConstructionPersonCode

  , ifnull(D.DepartmentCode, 0)

  , A.ConstrSubPersonCode

  , ifnull(E.DepartmentCode, 0)

  , A.ConstructionInstructor

  , A.TransferConstruction

  , A.TransferConstructionDate

  , A.OrderersDivision

  , A.OrderersCode

  , A.EstimatesExpirationDate

  , A.ConstructionPeriodStart

  , A.ConstructionPeriodEnd

  , A.ConstructionPeriodStart2

  , A.ConstructionPeriodEnd2

  , A.StartDate

  , A.EndDate

  , A.PurchaseOrderMailingDate

  , A.PurchaseOrderReturnDate

  , A.PurchaseOrderReturnCheckDate

  , A.ConsumptionTaxFlg

  , A.PrimeContractorFlg

  , A.SalesCostFlg

  , A.ConstructionStatusFlg

  , A.ConstructionType

  , A.EstimateType

  , A.BillingStartDate

  , A.BillingDate

  , A.BillingSplitFlg

  , A.BillingSendDate

  , A.LedgerComplateDate

  , InspectPlanningDate

  , InspectExecuteDate

  , RevCompleteDate

  , A.EntryDate

  , A.UpdateDate 

FROM

  constructionbaseinfo_OLD As A 

  Left Join chgchargedep As B 

    On B.PersonCode = A.SalesPersonCode

    AND ( 

      DATE (B.StartDate) <= DATE ('2016/07/31') 

      AND ( 

        DATE ('2015/08/01') <= DATE (B.CompDate) 

        OR DATE ('0001/01/01') = DATE (B.CompDate)

      )

    ) 

  Left Join chgchargedep As C 

    On C.PersonCode = A.ConstructionPersonCode 

    AND ( 

      DATE (C.StartDate) <= DATE ('2016/07/31') 

      AND ( 

        DATE ('2015/08/01') <= DATE (C.CompDate) 

        OR DATE ('0001/01/01') = DATE (C.CompDate)

      )

    ) 

  Left Join chgchargedep As D 

    On D.PersonCode = A.ConstrSubPersonCode 

    AND ( 

      DATE (D.StartDate) <= DATE ('2016/07/31') 

      AND ( 

        DATE ('2015/08/01') <= DATE (D.CompDate) 

        OR DATE ('0001/01/01') = DATE (D.CompDate)

      )

    ) 

  Left Join chgchargedep As E 

    On E.PersonCode = A.ConstructionInstructor

    AND ( 

      DATE (D.StartDate) <= DATE ('2016/07/31') 

      AND ( 

        DATE ('2015/08/01') <= DATE (D.CompDate) 

        OR DATE ('0001/01/01') = DATE (D.CompDate)

      )

    ) 

Where A.ConstructionPeriod = 15

;

Insert INTO constructionbaseinfo

SELECT

  A.ConstructionCode

  , A.TyingFlg

  , A.JoinFlg

  , A.ConstructionYear

  , A.ConstructionPeriod

  , A.RequestedDate

  , A.EstimatesSubmitDeadline

  , A.EstimatesSubmittedDate

  , A.ProvisionalOrderDate

  , A.OrderDate

  , A.OrderStartingDate

  , A.OrderCompletionDate

  , A.PreparationStartDate

  , A.ConstructionStartingDate

  , A.ConstructionCompletionDate

  , A.NonOrderDate

  , A.OrdersDecisionPrice

  , A.OrdersDecisionPriceInTax

  , B.DepartmentCode

  , A.SalesPersonCode

  , ifnull(C.DepartmentCode, 0)

  , A.ConstructionPersonCode

  , ifnull(D.DepartmentCode, 0)

  , A.ConstrSubPersonCode

  , ifnull(E.DepartmentCode, 0)

  , A.ConstructionInstructor

  , A.TransferConstruction

  , A.TransferConstructionDate

  , A.OrderersDivision

  , A.OrderersCode

  , A.EstimatesExpirationDate

  , A.ConstructionPeriodStart

  , A.ConstructionPeriodEnd

  , A.ConstructionPeriodStart2

  , A.ConstructionPeriodEnd2

  , A.StartDate

  , A.EndDate

  , A.PurchaseOrderMailingDate

  , A.PurchaseOrderReturnDate

  , A.PurchaseOrderReturnCheckDate

  , A.ConsumptionTaxFlg

  , A.PrimeContractorFlg

  , A.SalesCostFlg

  , A.ConstructionStatusFlg

  , A.ConstructionType

  , A.EstimateType

  , A.BillingStartDate

  , A.BillingDate

  , A.BillingSplitFlg

  , A.BillingSendDate

  , A.LedgerComplateDate

  , InspectPlanningDate

  , InspectExecuteDate

  , RevCompleteDate

  , A.EntryDate

  , A.UpdateDate 

FROM

  constructionbaseinfo_OLD As A 

  Left Join chgchargedep As B 

    On B.PersonCode = A.SalesPersonCode

    AND ( 

      DATE (B.StartDate) <= DATE ('2017/07/31') 

      AND ( 

        DATE ('2016/08/01') <= DATE (B.CompDate) 

        OR DATE ('0001/01/01') = DATE (B.CompDate)

      )

    ) 

  Left Join chgchargedep As C 

    On C.PersonCode = A.ConstructionPersonCode 

    AND ( 

      DATE (C.StartDate) <= DATE ('2017/07/31') 

      AND ( 

        DATE ('2016/08/01') <= DATE (C.CompDate) 

        OR DATE ('0001/01/01') = DATE (C.CompDate)

      )

    ) 

  Left Join chgchargedep As D 

    On D.PersonCode = A.ConstrSubPersonCode 

    AND ( 

      DATE (D.StartDate) <= DATE ('2017/07/31') 

      AND ( 

        DATE ('2016/08/01') <= DATE (D.CompDate) 

        OR DATE ('0001/01/01') = DATE (D.CompDate)

      )

    ) 

  Left Join chgchargedep As E 

    On E.PersonCode = A.ConstructionInstructor

    AND ( 

      DATE (E.StartDate) <= DATE ('2017/07/31') 

      AND ( 

        DATE ('2016/08/01') <= DATE (E.CompDate) 

        OR DATE ('0001/01/01') = DATE (E.CompDate)

      )

    ) 

Where A.ConstructionPeriod = 16

;

Insert INTO constructionbaseinfo

SELECT

  A.ConstructionCode

  , A.TyingFlg

  , A.JoinFlg

  , A.ConstructionYear

  , A.ConstructionPeriod

  , A.RequestedDate

  , A.EstimatesSubmitDeadline

  , A.EstimatesSubmittedDate

  , A.ProvisionalOrderDate

  , A.OrderDate

  , A.OrderStartingDate

  , A.OrderCompletionDate

  , A.PreparationStartDate

  , A.ConstructionStartingDate

  , A.ConstructionCompletionDate

  , A.NonOrderDate

  , A.OrdersDecisionPrice

  , A.OrdersDecisionPriceInTax

  , B.DepartmentCode

  , A.SalesPersonCode

  , ifnull(C.DepartmentCode, 0)

  , A.ConstructionPersonCode

  , ifnull(D.DepartmentCode, 0)

  , A.ConstrSubPersonCode

  , ifnull(E.DepartmentCode, 0)

  , A.ConstructionInstructor

  , A.TransferConstruction

  , A.TransferConstructionDate

  , A.OrderersDivision

  , A.OrderersCode

  , A.EstimatesExpirationDate

  , A.ConstructionPeriodStart

  , A.ConstructionPeriodEnd

  , A.ConstructionPeriodStart2

  , A.ConstructionPeriodEnd2

  , A.StartDate

  , A.EndDate

  , A.PurchaseOrderMailingDate

  , A.PurchaseOrderReturnDate

  , A.PurchaseOrderReturnCheckDate

  , A.ConsumptionTaxFlg

  , A.PrimeContractorFlg

  , A.SalesCostFlg

  , A.ConstructionStatusFlg

  , A.ConstructionType

  , A.EstimateType

  , A.BillingStartDate

  , A.BillingDate

  , A.BillingSplitFlg

  , A.BillingSendDate

  , A.LedgerComplateDate

  , InspectPlanningDate

  , InspectExecuteDate

  , RevCompleteDate

  , A.EntryDate

  , A.UpdateDate 

FROM

  constructionbaseinfo_OLD As A 

  Left Join chgchargedep As B 

    On B.PersonCode = A.SalesPersonCode

    AND ( 

      DATE (B.StartDate) <= DATE ('2018/03/31') 

      AND ( 

        DATE ('2017/08/01') <= DATE (B.CompDate) 

        OR DATE ('0001/01/01') = DATE (B.CompDate)

      )

    ) 

  Left Join chgchargedep As C