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

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;