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

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

    On C.PersonCode = A.ConstructionPersonCode 

    AND ( 

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

      AND ( 

        DATE ('2017/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 ('2018/03/31') 

      AND ( 

        DATE ('2017/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 ('2018/03/31') 

      AND ( 

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

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

      )

    ) 

Where A.ConstructionPeriod = 17

;

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 ('2019/03/31') 

      AND ( 

        DATE ('2018/04/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 ('2019/03/31') 

      AND ( 

        DATE ('2018/04/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 ('2019/03/31') 

      AND ( 

        DATE ('2018/04/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 ('2019/03/31') 

      AND ( 

        DATE ('2018/04/01') <= DATE (E.CompDate) 

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

      )

    ) 

Where A.ConstructionPeriod = 18

;

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

DROP TABLE IF EXISTS constructionbaseinfodetail_OLD;

CREATE TABLE constructionbaseinfodetail_OLD As Select * from constructionbaseinfodetail;

drop table if exists constructionbaseinfodetail cascade;

create table constructionbaseinfodetail (

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

  , DetailNo decimal(3,0) not null comment '??????'

  , DetailString varchar(300) comment '??????e'

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

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

  , constraint constructionbaseinfodetail_PKC primary key (ConstructionCode,DetailNo)

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

create index BaseInfoDetail_Idx1

  on constructionbaseinfodetail(ConstructionCode);

create index BaseInfoDetail_Idx2

  on constructionbaseinfodetail(DetailNo);

Insert INTO constructionbaseinfodetail

SELECT * From constructionbaseinfodetail_OLD;

Insert 

INTO constructionbaseinfodetail 

SELECT

  Base.ConstructionCode

  , 101

  , Dep.DepartmentString

  , Now()

  , Now() 

From

  constructionbaseinfo As Base 

  Inner Join departmentmaster as Dep 

    On Dep.DepartmentCode = Base.SalesDepCode; 

Insert 

INTO constructionbaseinfodetail 

SELECT

  Base.ConstructionCode

  , 102

  , ifnull(Dep.DepartmentString,'')

  , Now()

  , Now() 

From

  constructionbaseinfo As Base 

  Inner Join departmentmaster as Dep 

    On Dep.DepartmentCode = Base.ConstrDepCode; 

Insert 

INTO constructionbaseinfodetail 

SELECT

  Base.ConstructionCode

  , 103

  , ifnull(Dep.DepartmentString,'')

  , Now()

  , Now() 

From

  constructionbaseinfo As Base 

  Inner Join departmentmaster as Dep 

    On Dep.DepartmentCode = Base.ConstrSubDepCode; 

Insert 

INTO constructionbaseinfodetail 

SELECT

  Base.ConstructionCode

  , 104

  , ifnull(Dep.DepartmentString,'')

  , Now()

  , Now() 

From

  constructionbaseinfo As Base 

  Inner Join departmentmaster as Dep 

    On Dep.DepartmentCode = Base.ConstrInstrDepCode; 

UPDATE constructionbaseinfodetail AS BD 

SET 

    BD.DetailString = '?R?{?g'

WHERE

    BD.ConstructionCode IN (SELECT 

            ConstructionCode

        FROM

            constructionbaseinfo AS B

        WHERE

            ConstructionPeriod < 17)

        AND BD.DetailNo > 100

        AND DetailString = '?????';

-- ??????o?^?\???f?[?^

drop table if exists costomerregist cascade;

create table costomerregist (

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

  , CreateDate date not null comment '????'

  , SeqNo decimal(2,0) not null comment '?L?[?A??'

  , DataMode decimal(1,0) not null comment '?f?[?^??'

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

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

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

  , OrderFlg decimal(1,0) not null comment '?@?l?c??E??l?t???O'

  , CorporateStatusName varchar(100) comment '?@?l?i????'

  , CorporateStatusPoint decimal(1,0) comment '?@?l?i?????u'

  , OrderersName1 varchar(60) not null comment '???????1'

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

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

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

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

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

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

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

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

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

  , MailAddress varchar(257) comment '???[???A?h???X'

  , Note varchar(300) comment '???l'

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

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

  , OrderNo decimal(2,0) unsigned not null comment '?\????t???'

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

  , CreateDepartmentName varchar(60) comment '?\????????'

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

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

  , constraint costomerregist_PKC primary key (CreatePersonCode,CreateDate,SeqNo)

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

create index CostomerRegist_Index1

  on costomerregist(PetitionPeriod);

create index CostomerRegist_Index2

  on costomerregist(CreatePersonCode);

create index CostomerRegist_Index3

  on costomerregist(CreateDepartmentCode);

commit;