プロジェクト

全般

プロフィール

リビジョン 322

堀内 がほぼ7年前に追加

工事基本情報：検査日予定日・検査実行日・検査是正完了日追加
工事基本情報明細：データ300文字に増やす
支払明細データ：インデックス追加

差分を表示:

branches/ddl/20171221_SQL.txt
1 
-- ?H????{???
2 
DROP TABLE IF EXISTS constructionbaseinfo_OLD;
3 
CREATE TABLE constructionbaseinfo_OLD As Select * from constructionbaseinfo;
4 

  		




  
  5
  
    
drop table if exists constructionbaseinfo cascade;


  		




  
  6
  
    

  		




  
  7
  
    
create table constructionbaseinfo (


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




  
  16
  
    
  , ProvisionalOrderDate date comment '?????'


  		




  
  17
  
    
  , OrderDate date comment '???'


  		




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


  		




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


  		




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


  		




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


  		




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


  		




  
  23
  
    
  , NonOrderDate date comment '????'


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




  
  60
  
    
  , constraint constructionbaseinfo_PKC primary key (ConstructionCode)


  		




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


  		




  
  62
  
    

  		




  
  63
  
    
create index BaseInfo_Index1


  		




  
  64
  
    
  on constructionbaseinfo(ConstructionStatusFlg);


  		




  
  65
  
    

  		




  
  66
  
    
create index BaseInfo_Index2


  		




  
  67
  
    
  on constructionbaseinfo(EstimateType);


  		




  
  68
  
    

  		




  
  69
  
    
create index BaseInfo_Index3


  		




  
  70
  
    
  on constructionbaseinfo(SalesPersonCode);


  		




  
  71
  
    

  		




  
  72
  
    
create index BaseInfo_Index4


  		




  
  73
  
    
  on constructionbaseinfo(ConstructionPersonCode);


  		




  
  74
  
    

  		




  
  75
  
    
create index BaseInfo_Index5


  		




  
  76
  
    
  on constructionbaseinfo(ConstructionInstructor);


  		




  
  77
  
    

  		




  
  78
  
    
create index BaseInfo_Index6


  		




  
  79
  
    
  on constructionbaseinfo(ConstructionPeriod);


  		




  
  80
  
    

  		




  
  81
  
    
Insert INTO constructionbaseinfo


  		




  
  82
  
    
SELECT


  		




  
  83
  
    
  ConstructionCode


  		




  
  84
  
    
  , TyingFlg


  		




  
  85
  
    
  , JoinFlg 


  		




  
  86
  
    
  , ConstructionYear 


  		




  
  87
  
    
  , ConstructionPeriod 


  		




  
  88
  
    
  , RequestedDate 


  		




  
  89
  
    
  , EstimatesSubmitDeadline 


  		




  
  90
  
    
  , EstimatesSubmittedDate 


  		




  
  91
  
    
  , ProvisionalOrderDate 


  		




  
  92
  
    
  , OrderDate 


  		




  
  93
  
    
  , OrderStartingDate 


  		




  
  94
  
    
  , OrderCompletionDate 


  		




  
  95
  
    
  , PreparationStartDate 


  		




  
  96
  
    
  , ConstructionStartingDate 


  		




  
  97
  
    
  , ConstructionCompletionDate 


  		




  
  98
  
    
  , NonOrderDate 


  		




  
  99
  
    
  , OrdersDecisionPrice 


  		




  
  100
  
    
  , OrdersDecisionPriceInTax 


  		




  
  101
  
    
  , SalesPersonCode 


  		




  
  102
  
    
  , ConstructionPersonCode 


  		




  
  103
  
    
  , ConstrSubPersonCode 


  		




  
  104
  
    
  , ConstructionInstructor 


  		




  
  105
  
    
  , TransferConstruction 


  		




  
  106
  
    
  , TransferConstructionDate 


  		




  
  107
  
    
  , OrderersDivision 


  		




  
  108
  
    
  , OrderersCode 


  		




  
  109
  
    
  , EstimatesExpirationDate 


  		




  
  110
  
    
  , ConstructionPeriodStart 


  		




  
  111
  
    
  , ConstructionPeriodEnd 


  		




  
  112
  
    
  , ConstructionPeriodStart2 


  		




  
  113
  
    
  , ConstructionPeriodEnd2 


  		




  
  114
  
    
  , StartDate 


  		




  
  115
  
    
  , EndDate 


  		




  
  116
  
    
  , PurchaseOrderMailingDate 


  		




  
  117
  
    
  , PurchaseOrderReturnDate 


  		




  
  118
  
    
  , PurchaseOrderReturnCheckDate 


  		




  
  119
  
    
  , ConsumptionTaxFlg 


  		




  
  120
  
    
  , PrimeContractorFlg 


  		




  
  121
  
    
  , SalesCostFlg 


  		




  
  122
  
    
  , ConstructionStatusFlg 


  		




  
  123
  
    
  , ConstructionType 


  		




  
  124
  
    
  , EstimateType 


  		




  
  125
  
    
  , BillingStartDate 


  		




  
  126
  
    
  , BillingDate 


  		




  
  127
  
    
  , BillingSplitFlg 


  		




  
  128
  
    
  , BillingSendDate 


  		




  
  129
  
    
  , LedgerComplateDate 


  		




  
  130
  
    
  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')


  		




  
  131
  
    
  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')


  		




  
  132
  
    
  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')


  		




  
  133
  
    
  , EntryDate


  		




  
  134
  
    
  , UpdateDate


  		




  
  135
  
    
FROM constructionbaseinfo_OLD


  		




  
  136
  
    
;


  		




  
  137
  
    

  		




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


  		




  
  139
  
    
ALTER TABLE constructionbaseinfodetail


  		




  
  140
  
    
CHANGE DetailString DetailString 


  		




  
  141
  
    
varchar(300)


  		




  
  142
  
    
comment '??????e';


  		




  
  143
  
    
commit;


  		












  

    
      branches/ddl/20180118_SQL.txt
    
  





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


  		




  
  2
  
    
DROP TABLE IF EXISTS constructionbaseinfo_OLD;


  		




  
  3
  
    
CREATE TABLE constructionbaseinfo_OLD As Select * from constructionbaseinfo;


  		




  
  4
  
    

  		




  
  5
  
    
drop table if exists constructionbaseinfo cascade;


  		




  
  6
  
    

  		




  
  7
  
    
create table constructionbaseinfo (


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




  
  16
  
    
  , ProvisionalOrderDate date comment '?????'


  		




  
  17
  
    
  , OrderDate date comment '???'


  		




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


  		




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


  		




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


  		




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


  		




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


  		




  
  23
  
    
  , NonOrderDate date comment '????'


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




  
  60
  
    
  , constraint constructionbaseinfo_PKC primary key (ConstructionCode)


  		




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


  		




  
  62
  
    

  		




  
  63
  
    
create index BaseInfo_Index1


  		




  
  64
  
    
  on constructionbaseinfo(ConstructionStatusFlg);


  		




  
  65
  
    

  		




  
  66
  
    
create index BaseInfo_Index2


  		




  
  67
  
    
  on constructionbaseinfo(EstimateType);


  		




  
  68
  
    

  		




  
  69
  
    
create index BaseInfo_Index3


  		




  
  70
  
    
  on constructionbaseinfo(SalesPersonCode);


  		




  
  71
  
    

  		




  
  72
  
    
create index BaseInfo_Index4


  		




  
  73
  
    
  on constructionbaseinfo(ConstructionPersonCode);


  		




  
  74
  
    

  		




  
  75
  
    
create index BaseInfo_Index5


  		




  
  76
  
    
  on constructionbaseinfo(ConstructionInstructor);


  		




  
  77
  
    

  		




  
  78
  
    
create index BaseInfo_Index6


  		




  
  79
  
    
  on constructionbaseinfo(ConstructionPeriod);


  		




  
  80
  
    

  		




  
  81
  
    
Insert INTO constructionbaseinfo


  		




  
  82
  
    
SELECT


  		




  
  83
  
    
  ConstructionCode


  		




  
  84
  
    
  , TyingFlg


  		




  
  85
  
    
  , JoinFlg 


  		




  
  86
  
    
  , ConstructionYear 


  		




  
  87
  
    
  , ConstructionPeriod 


  		




  
  88
  
    
  , RequestedDate 


  		




  
  89
  
    
  , EstimatesSubmitDeadline 


  		




  
  90
  
    
  , EstimatesSubmittedDate 


  		




  
  91
  
    
  , ProvisionalOrderDate 


  		




  
  92
  
    
  , OrderDate 


  		




  
  93
  
    
  , OrderStartingDate 


  		




  
  94
  
    
  , OrderCompletionDate 


  		




  
  95
  
    
  , PreparationStartDate 


  		




  
  96
  
    
  , ConstructionStartingDate 


  		




  
  97
  
    
  , ConstructionCompletionDate 


  		




  
  98
  
    
  , NonOrderDate 


  		




  
  99
  
    
  , OrdersDecisionPrice 


  		




  
  100
  
    
  , OrdersDecisionPriceInTax 


  		




  
  101
  
    
  , SalesPersonCode 


  		




  
  102
  
    
  , ConstructionPersonCode 


  		




  
  103
  
    
  , ConstrSubPersonCode 


  		




  
  104
  
    
  , ConstructionInstructor 


  		




  
  105
  
    
  , TransferConstruction 


  		




  
  106
  
    
  , TransferConstructionDate 


  		




  
  107
  
    
  , OrderersDivision 


  		




  
  108
  
    
  , OrderersCode 


  		




  
  109
  
    
  , EstimatesExpirationDate 


  		




  
  110
  
    
  , ConstructionPeriodStart 


  		




  
  111
  
    
  , ConstructionPeriodEnd 


  		




  
  112
  
    
  , ConstructionPeriodStart2 


  		




  
  113
  
    
  , ConstructionPeriodEnd2 


  		




  
  114
  
    
  , StartDate 


  		




  
  115
  
    
  , EndDate 


  		




  
  116
  
    
  , PurchaseOrderMailingDate 


  		




  
  117
  
    
  , PurchaseOrderReturnDate 


  		




  
  118
  
    
  , PurchaseOrderReturnCheckDate 


  		




  
  119
  
    
  , ConsumptionTaxFlg 


  		




  
  120
  
    
  , PrimeContractorFlg 


  		




  
  121
  
    
  , SalesCostFlg 


  		




  
  122
  
    
  , ConstructionStatusFlg 


  		




  
  123
  
    
  , ConstructionType 


  		




  
  124
  
    
  , EstimateType 


  		




  
  125
  
    
  , BillingStartDate 


  		




  
  126
  
    
  , BillingDate 


  		




  
  127
  
    
  , BillingSplitFlg 


  		




  
  128
  
    
  , BillingSendDate 


  		




  
  129
  
    
  , LedgerComplateDate 


  		




  
  130
  
    
  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')


  		




  
  131
  
    
  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')


  		




  
  132
  
    
  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')


  		




  
  133
  
    
  , EntryDate


  		




  
  134
  
    
  , UpdateDate


  		




  
  135
  
    
FROM constructionbaseinfo_OLD


  		




  
  136
  
    
;


  		




  
  137
  
    

  		




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


  		




  
  139
  
    
ALTER TABLE constructionbaseinfodetail


  		




  
  140
  
    
CHANGE DetailString DetailString 


  		




  
  141
  
    
varchar(300)


  		




  
  142
  
    
comment '??????e';


  		




  
  143
  
    
commit;


  		












  

    
      branches/ddl/20180203_SQL.txt
    
  





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


  		




  
  2
  
    
create index PayDataDetail_Index1


  		




  
  3
  
    
  on paymentdatadetail(TARGETDATE);


  		




  
  4
  
    
create index PayDataDetail_Index2


  		




  
  5
  
    
  on paymentdatadetail(APPROVALPERSONCODE);


  		












  

    
      branches/ddl/ProcessManagement.sql
    
  





  1
  1
  
    
-- Project Name : noname


  		




  2
  
  
    
-- Date/Time    : 2017/12/12 10:48:17


  		




  
  2
  
    
-- Date/Time    : 2018/02/27 8:14:07


  		




  3
  3
  
    
-- Author       : Horiuchi


  		




  4
  4
  
    
-- RDBMS Type   : MySQL


  		




  5
  5
  
    
-- Application  : A5:SQL Mk-2


  		




  ......




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




  475
  478
  
    
  , constraint constructionbaseinfo_PKC primary key (ConstructionCode)


  		




  ......




  499
  502
  
    
create table constructionbaseinfodetail (


  		




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


  		




  501
  504
  
    
  , DetailNo decimal(2,0) not null comment '??????'


  		




  502
  
  
    
  , DetailString varchar(120) comment '??????e'


  		




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


  		




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


  		




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


  		




  505
  508
  
    
  , constraint constructionbaseinfodetail_PKC primary key (ConstructionCode,DetailNo)


  		




  ......




  1509
  1512
  
    
  , constraint paymentdatadetail_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO,LINECOUNT)


  		




  1510
  1513
  
    
) comment '?x??????f?[?^' ;


  		




  1511
  1514
  
    

  		




  
  1515
  
    
create index PayDataDetail_Index1


  		




  
  1516
  
    
  on paymentdatadetail(TARGETDATE);


  		




  
  1517
  
    

  		




  
  1518
  
    
create index PayDataDetail_Index2


  		




  
  1519
  
    
  on paymentdatadetail(APPROVALPERSONCODE);


  		




  
  1520
  
    

  		




  1512
  1521
  
    
-- ?H??????????f?[?^


  		




  1513
  1522
  
    
drop table if exists periodavoidance cascade;


  		




  1514
  1523
  
    

  		












他の形式にエクスポート:  Unified diff