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

-- Date/Time    : 2018/09/20 10:20:05

-- ?H?????E?\???????N?}?X?^

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

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

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

  , constraint componenttotypemaster_PKC primary key (TypeCode,ComponentCode)

) comment '?H?????E?\???????N?}?X?^' ;

  on componenttotypemaster(TypeCode);

  on componenttotypemaster(ComponentCode);

  ConstructionTypeCode mediumint(8) unsigned not null comment '?H?????R?[?h'

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

  , constraint constructionspecunitprice_PKC primary key (ConstructionTypeCode,ComponentCode,ItemCode,SpecCode)

create index constructionspecunitprice_Index1

  on constructionspecunitprice(ConstructionTypeCode,ComponentCode,ItemCode);

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

  , OperationFlg decimal(2,0) unsigned not null comment '???F?^?p?t???O'

-- ?H?????E?\???????N?}?X?^

drop table if exists componenttotypemaster cascade;

create table componenttotypemaster (

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

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

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

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

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

  , constraint componenttotypemaster_PKC primary key (TypeCode, ComponentCode)

) comment '?H?????E?\???????N?}?X?^' ;

create index ComponentToTypeMaster_Index1

  on componenttotypemaster(TypeCode);

create index ComponentToTypeMaster_Index2

  on componenttotypemaster(ComponentCode);

-- ???P???}?X?^

drop table if exists constructionspecunitprice_OLD cascade;

CREATE TABLE constructionspecunitprice_OLD As Select * from constructionspecunitprice;

drop table if exists constructionspecunitprice cascade;

create table constructionspecunitprice (

  ConstructionTypeCode mediumint(8) unsigned not null comment '?H?????R?[?h'

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

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

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

  , UnitPrice decimal(9,2) not null comment '?P??'

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

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

  , constraint constructionspecunitprice_PKC primary key (ConstructionTypeCode,ComponentCode,ItemCode,SpecCode)

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

create index constructionspecunitprice_Index1

  on constructionspecunitprice(ConstructionTypeCode,ComponentCode,ItemCode);

create index constructionspecunitprice_Index2

  on constructionspecunitprice(ItemCode,SpecCode);

Insert INTO constructionspecunitprice

SELECT

  ConstructionTypeCode

  , ComponentCode

  , ItemCode

  , SpecCode

  , UnitPrice

  , EntryDate

  , UpdateDate

FROM constructionspecunitprice_OLD

;

commit;

-- ????}?X?^

drop table if exists systemmaster_OLD cascade;

CREATE TABLE systemmaster_OLD As Select * from systemmaster;

drop table if exists systemmaster cascade;

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'

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

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

  , BusinessBeginDate datetime not null comment '?c????????'

  , ConstrBeginDate datetime not null comment '?H???N?x?????'

  , BusinessCompDate datetime not null comment '?c?????????'

  , ConstrCompDate datetime 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'

  , OperationFlg decimal(2,0) unsigned not null comment '???F?^?p?t???O'

  , 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

  , ConsumptionTax

  , CooperationRate

  , StatutoryWelfareRate

  , FuelPrice

  , AreaDistance

  , ExcelSavePath

  , BusinessPeriod

  , ConstrYear

  , BusinessBeginDate

  , ConstrBeginDate

  , BusinessCompDate

  , ConstrCompDate

  , ConstructionNoBase

  , SloganString1

  , SloganString2

  , SloganString3

  , 0

  , EntryDate

  , UpdateDate

FROM systemmaster_OLD

;

commit;