リビジョン 332
工事担当者履歴テーブル追加
工事基本情報：部署項目追加
工事基本情報明細：明細番号2桁->3桁（部署名追加）
発注者登録申請テーブル：申請部署追加
| branches/ddl/20180323_SQL.txt | ||
|---|---|---|
| 1 |
-- ?S????????????f?[?^ |
|
| 2 |
drop table if exists ChgChargeDep cascade; |
|
| 3 |
|
|
| 4 |
create table ChgChargeDep ( |
|
| 5 |
PersonCode decimal(8,0) not null comment '?S????R?[?h' |
|
| 6 |
, StartDate date not null comment '?J?n?N????' |
|
| 7 |
, CompDate date not null comment '?I???N????' |
|
| 8 |
, DepartmentCode decimal(5,0) unsigned not null comment '?????R?[?h' |
|
| 9 |
, DepartmentName varchar(60) comment '??????' |
|
| 10 |
, EntryDate datetime not null comment '?o?^???t' |
|
| 11 |
, UpdateDate datetime not null comment '?X?V???t' |
|
| 12 |
, constraint ChgChargeDep_PKC primary key (PersonCode, StartDate) |
|
| 13 |
) comment '?S????????????f?[?^' ; |
|
| 14 |
|
|
| 15 |
Insert INTO ChgChargeDep |
|
| 16 |
select |
|
| 17 |
A.PersonCode |
|
| 18 |
, A.StartDate |
|
| 19 |
, A.EndDate |
|
| 20 |
, A.DepartmentCode |
|
| 21 |
, B.DepartmentString |
|
| 22 |
, Now() |
|
| 23 |
, Now() |
|
| 24 |
From |
|
| 25 |
personinchargemaster As A |
|
| 26 |
Left Join departmentmaster As B |
|
| 27 |
On B.DepartmentCode = A.DepartmentCode |
|
| 28 |
; |
|
| 29 |
|
|
| 30 |
update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 2, DepartmentName = '?c?U??' Where PersonCode = 20150701 And DATE(StartDate) = DATE('2015/07/01');
|
|
| 31 |
Insert chgchargedep Value(20150701, DATE('2017/08/01'), DATE('0001/01/01'), 1, '?H????', NOW(), NOW());
|
|
| 32 |
|
|
| 33 |
update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 1, DepartmentName = '?H????' Where PersonCode = 20160602 And DATE(StartDate) = DATE('2016/06/20');
|
|
| 34 |
Insert chgchargedep Value(20160602, DATE('2017/08/01'), DATE('0001/01/01'), 3, '?c???', NOW(), NOW());
|
|
| 35 |
|
|
| 36 |
update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 1, DepartmentName = '?H????' Where PersonCode = 20160701 And DATE(StartDate) = DATE('2016/07/01');
|
|
| 37 |
Insert chgchargedep Value(20160701, DATE('2017/08/01'), DATE('0001/01/01'), 2, '?c?U??', NOW(), NOW());
|
|
| 38 |
commit; |
|
| branches/ddl/20180324_SQL.txt | ||
|---|---|---|
| 1 |
|
|
| 2 |
-- ?H???S???????f?[?^ |
|
| 3 |
drop table if exists ConstrChgCharge cascade; |
|
| 4 |
|
|
| 5 |
create table ConstrChgCharge ( |
|
| 6 |
ConstructionCode decimal(10,0) unsigned not null comment '?H???R?[?h' |
|
| 7 |
, Division decimal(1,0) not null comment '????' |
|
| 8 |
, SeqNo smallint(5) unsigned not null comment '?A??' |
|
| 9 |
, DepartmentCode decimal(5,0) unsigned not null comment '?????R?[?h' |
|
| 10 |
, PersonCode decimal(8,0) not null comment '?S????R?[?h' |
|
| 11 |
, StartDate date comment '?S???J?n?N????' |
|
| 12 |
, CompDate date comment '?S???I???N????' |
|
| 13 |
, EntryDate datetime not null comment '?o?^???t' |
|
| 14 |
, UpdateDate datetime not null comment '?X?V???t' |
|
| 15 |
, constraint ConstrChgCharge_PKC primary key (ConstructionCode, Division, SeqNo) |
|
| 16 |
) comment '?H???S???????f?[?^' ; |
|
| 17 |
|
|
| 18 |
-- ?H????{???
|
|
| 19 |
DROP TABLE IF EXISTS constructionbaseinfo_OLD; |
|
| 20 |
CREATE TABLE constructionbaseinfo_OLD As Select * from constructionbaseinfo; |
|
| 21 |
|
|
| 22 |
drop table if exists constructionbaseinfo cascade; |
|
| 23 |
|
|
| 24 |
create table constructionbaseinfo ( |
|
| 25 |
ConstructionCode decimal(10,0) unsigned not null comment '?H???R?[?h' |
|
| 26 |
, TyingFlg decimal(1,0) not null comment '?R?t?f?[?^?t???O' |
|
| 27 |
, JoinFlg decimal(1,0) not null comment '?????_??H???t???O' |
|
| 28 |
, ConstructionYear smallint(5) unsigned not null comment '?H???N?x' |
|
| 29 |
, ConstructionPeriod smallint(5) unsigned not null comment '?H??????' |
|
| 30 |
, RequestedDate date comment '??????' |
|
| 31 |
, EstimatesSubmitDeadline date comment '?????o????' |
|
| 32 |
, EstimatesSubmittedDate date comment '?????o??' |
|
| 33 |
, ProvisionalOrderDate date comment '?????' |
|
| 34 |
, OrderDate date comment '???' |
|
| 35 |
, OrderStartingDate date comment '?J?n?\???' |
|
| 36 |
, OrderCompletionDate date comment '?????\???' |
|
| 37 |
, PreparationStartDate date comment '?{?H?????J?n??'
|
|
| 38 |
, ConstructionStartingDate date comment '?{?H?J?n??'
|
|
| 39 |
, ConstructionCompletionDate date comment '?{?H??????'
|
|
| 40 |
, NonOrderDate date comment '????' |
|
| 41 |
, OrdersDecisionPrice decimal(12,0) not null comment '??????????z' |
|
| 42 |
, OrdersDecisionPriceInTax decimal(12,0) not null comment '??????????z' |
|
| 43 |
, SalesDepCode decimal(5,0) unsigned not null comment '?c??S????????R?[?h' |
|
| 44 |
, SalesPersonCode decimal(8,0) unsigned not null comment '?c??S????R?[?h' |
|
| 45 |
, ConstrDepCode decimal(5,0) unsigned not null comment '?H???S????????R?[?h' |
|
| 46 |
, ConstructionPersonCode decimal(8,0) not null comment '?H???S????R?[?h' |
|
| 47 |
, ConstrSubDepCode decimal(5,0) unsigned not null comment '?H?????S????????R?[?h' |
|
| 48 |
, ConstrSubPersonCode decimal(8,0) not null comment '?H?????S????R?[?h' |
|
| 49 |
, ConstrInstrDepCode decimal(5,0) unsigned not null comment '?H???w?????????R?[?h' |
|
| 50 |
, ConstructionInstructor decimal(8,0) not null comment '?H???w?????R?[?h' |
|
| 51 |
, TransferConstruction decimal(8,0) not null comment '?H??????R?[?h' |
|
| 52 |
, TransferConstructionDate date comment '?H??????' |
|
| 53 |
, OrderersDivision decimal(5,0) not null comment '????????R?[?h' |
|
| 54 |
, OrderersCode decimal(5,0) unsigned not null comment '??????R?[?h' |
|
| 55 |
, EstimatesExpirationDate date comment '????L??????' |
|
| 56 |
, ConstructionPeriodStart date comment '?_??H???J?n??' |
|
| 57 |
, ConstructionPeriodEnd date comment '?_??H??????' |
|
| 58 |
, ConstructionPeriodStart2 date comment '?_??H???J?n?i???????j' |
|
| 59 |
, ConstructionPeriodEnd2 date comment '?_??H???????i???????j' |
|
| 60 |
, StartDate date comment '?H???J?n??' |
|
| 61 |
, EndDate date comment '?H???I????' |
|
| 62 |
, PurchaseOrderMailingDate date comment '???????X????' |
|
| 63 |
, PurchaseOrderReturnDate date comment '???????????' |
|
| 64 |
, PurchaseOrderReturnCheckDate date comment '??????????m?F??' |
|
| 65 |
, ConsumptionTaxFlg decimal(1,0) not null comment '?????t???O' |
|
| 66 |
, PrimeContractorFlg decimal(1,0) not null comment '?????t???O' |
|
| 67 |
, SalesCostFlg decimal(1,0) not null comment '?c??o?????t???O' |
|
| 68 |
, ConstructionStatusFlg decimal(2,0) not null comment '?H?????t???O' |
|
| 69 |
, ConstructionType decimal(5,0) not null comment '?H?????R?[?h' |
|
| 70 |
, EstimateType decimal(1,0) not null comment '??????' |
|
| 71 |
, BillingStartDate date not null comment '?????????J?n??' |
|
| 72 |
, BillingDate date not null comment '??????' |
|
| 73 |
, BillingSplitFlg decimal(1,0) not null comment '??????t???O' |
|
| 74 |
, BillingSendDate date comment '???????X??/??n????' |
|
| 75 |
, LedgerComplateDate date not null comment '?H????????????????t' |
|
| 76 |
, InspectPlanningDate date not null comment '?????\???' |
|
| 77 |
, InspectExecuteDate date not null comment '???????s??' |
|
| 78 |
, RevCompleteDate date not null comment '??????????????' |
|
| 79 |
, EntryDate datetime not null comment '?o?^???t' |
|
| 80 |
, UpdateDate datetime not null comment '?X?V???t' |
|
| 81 |
, constraint constructionbaseinfo_PKC primary key (ConstructionCode) |
|
| 82 |
) comment '?H????{???' ;
|
|
| 83 |
|
|
| 84 |
create index BaseInfo_Index1 |
|
| 85 |
on constructionbaseinfo(ConstructionStatusFlg); |
|
| 86 |
|
|
| 87 |
create index BaseInfo_Index2 |
|
| 88 |
on constructionbaseinfo(EstimateType); |
|
| 89 |
|
|
| 90 |
create index BaseInfo_Index3 |
|
| 91 |
on constructionbaseinfo(SalesPersonCode); |
|
| 92 |
|
|
| 93 |
create index BaseInfo_Index4 |
|
| 94 |
on constructionbaseinfo(ConstructionPersonCode); |
|
| 95 |
|
|
| 96 |
create index BaseInfo_Index5 |
|
| 97 |
on constructionbaseinfo(ConstructionInstructor); |
|
| 98 |
|
|
| 99 |
create index BaseInfo_Index6 |
|
| 100 |
on constructionbaseinfo(ConstructionPeriod); |
|
| 101 |
|
|
| 102 |
Insert INTO constructionbaseinfo |
|
| 103 |
SELECT |
|
| 104 |
A.ConstructionCode |
|
| 105 |
, A.TyingFlg |
|
| 106 |
, A.JoinFlg |
|
| 107 |
, A.ConstructionYear |
|
| 108 |
, A.ConstructionPeriod |
|
| 109 |
, A.RequestedDate |
|
| 110 |
, A.EstimatesSubmitDeadline |
|
| 111 |
, A.EstimatesSubmittedDate |
|
| 112 |
, A.ProvisionalOrderDate |
|
| 113 |
, A.OrderDate |
|
| 114 |
, A.OrderStartingDate |
|
| 115 |
, A.OrderCompletionDate |
|
| 116 |
, A.PreparationStartDate |
|
| 117 |
, A.ConstructionStartingDate |
|
| 118 |
, A.ConstructionCompletionDate |
|
| 119 |
, A.NonOrderDate |
|
| 120 |
, A.OrdersDecisionPrice |
|
| 121 |
, A.OrdersDecisionPriceInTax |
|
| 122 |
, B.DepartmentCode |
|
| 123 |
, A.SalesPersonCode |
|
| 124 |
, ifnull(C.DepartmentCode, 0) |
|
| 125 |
, A.ConstructionPersonCode |
|
| 126 |
, ifnull(D.DepartmentCode, 0) |
|
| 127 |
, A.ConstrSubPersonCode |
|
| 128 |
, ifnull(E.DepartmentCode, 0) |
|
| 129 |
, A.ConstructionInstructor |
|
| 130 |
, A.TransferConstruction |
|
| 131 |
, A.TransferConstructionDate |
|
| 132 |
, A.OrderersDivision |
|
| 133 |
, A.OrderersCode |
|
| 134 |
, A.EstimatesExpirationDate |
|
| 135 |
, A.ConstructionPeriodStart |
|
| 136 |
, A.ConstructionPeriodEnd |
|
| 137 |
, A.ConstructionPeriodStart2 |
|
| 138 |
, A.ConstructionPeriodEnd2 |
|
| 139 |
, A.StartDate |
|
| 140 |
, A.EndDate |
|
| 141 |
, A.PurchaseOrderMailingDate |
|
| 142 |
, A.PurchaseOrderReturnDate |
|
| 143 |
, A.PurchaseOrderReturnCheckDate |
|
| 144 |
, A.ConsumptionTaxFlg |
|
| 145 |
, A.PrimeContractorFlg |
|
| 146 |
, A.SalesCostFlg |
|
| 147 |
, A.ConstructionStatusFlg |
|
| 148 |
, A.ConstructionType |
|
| 149 |
, A.EstimateType |
|
| 150 |
, A.BillingStartDate |
|
| 151 |
, A.BillingDate |
|
| 152 |
, A.BillingSplitFlg |
|
| 153 |
, A.BillingSendDate |
|
| 154 |
, A.LedgerComplateDate |
|
| 155 |
, InspectPlanningDate |
|
| 156 |
, InspectExecuteDate |
|
| 157 |
, RevCompleteDate |
|
| 158 |
, A.EntryDate |
|
| 159 |
, A.UpdateDate |
|
| 160 |
FROM |
|
| 161 |
constructionbaseinfo_OLD As A |
|
| 162 |
Left Join chgchargedep As B |
|
| 163 |
On B.PersonCode = A.SalesPersonCode |
|
| 164 |
AND ( |
|
| 165 |
DATE (B.StartDate) <= DATE ('2016/07/31')
|
|
| 166 |
AND ( |
|
| 167 |
DATE ('2015/08/01') <= DATE (B.CompDate)
|
|
| 168 |
OR DATE ('0001/01/01') = DATE (B.CompDate)
|
|
| 169 |
) |
|
| 170 |
) |
|
| 171 |
Left Join chgchargedep As C |
|
| 172 |
On C.PersonCode = A.ConstructionPersonCode |
|
| 173 |
AND ( |
|
| 174 |
DATE (C.StartDate) <= DATE ('2016/07/31')
|
|
| 175 |
AND ( |
|
| 176 |
DATE ('2015/08/01') <= DATE (C.CompDate)
|
|
| 177 |
OR DATE ('0001/01/01') = DATE (C.CompDate)
|
|
| 178 |
) |
|
| 179 |
) |
|
| 180 |
Left Join chgchargedep As D |
|
| 181 |
On D.PersonCode = A.ConstrSubPersonCode |
|
| 182 |
AND ( |
|
| 183 |
DATE (D.StartDate) <= DATE ('2016/07/31')
|
|
| 184 |
AND ( |
|
| 185 |
DATE ('2015/08/01') <= DATE (D.CompDate)
|
|
| 186 |
OR DATE ('0001/01/01') = DATE (D.CompDate)
|
|
| 187 |
) |
|
| 188 |
) |
|
| 189 |
Left Join chgchargedep As E |
|
| 190 |
On E.PersonCode = A.ConstructionInstructor |
|
| 191 |
AND ( |
|
| 192 |
DATE (D.StartDate) <= DATE ('2016/07/31')
|
|
| 193 |
AND ( |
|
| 194 |
DATE ('2015/08/01') <= DATE (D.CompDate)
|
|
| 195 |
OR DATE ('0001/01/01') = DATE (D.CompDate)
|
|
| 196 |
) |
|
| 197 |
) |
|
| 198 |
Where A.ConstructionPeriod = 15 |
|
| 199 |
; |
|
| 200 |
|
|
| 201 |
Insert INTO constructionbaseinfo |
|
| 202 |
SELECT |
|
| 203 |
A.ConstructionCode |
|
| 204 |
, A.TyingFlg |
|
| 205 |
, A.JoinFlg |
|
| 206 |
, A.ConstructionYear |
|
| 207 |
, A.ConstructionPeriod |
|
| 208 |
, A.RequestedDate |
|
| 209 |
, A.EstimatesSubmitDeadline |
|
| 210 |
, A.EstimatesSubmittedDate |
|
| 211 |
, A.ProvisionalOrderDate |
|
| 212 |
, A.OrderDate |
|
| 213 |
, A.OrderStartingDate |
|
| 214 |
, A.OrderCompletionDate |
|
| 215 |
, A.PreparationStartDate |
|
| 216 |
, A.ConstructionStartingDate |
|
| 217 |
, A.ConstructionCompletionDate |
|
| 218 |
, A.NonOrderDate |
|
| 219 |
, A.OrdersDecisionPrice |
|
| 220 |
, A.OrdersDecisionPriceInTax |
|
| 221 |
, B.DepartmentCode |
|
| 222 |
, A.SalesPersonCode |
|
| 223 |
, ifnull(C.DepartmentCode, 0) |
|
| 224 |
, A.ConstructionPersonCode |
|
| 225 |
, ifnull(D.DepartmentCode, 0) |
|
| 226 |
, A.ConstrSubPersonCode |
|
| 227 |
, ifnull(E.DepartmentCode, 0) |
|
| 228 |
, A.ConstructionInstructor |
|
| 229 |
, A.TransferConstruction |
|
| 230 |
, A.TransferConstructionDate |
|
| 231 |
, A.OrderersDivision |
|
| 232 |
, A.OrderersCode |
|
| 233 |
, A.EstimatesExpirationDate |
|
| 234 |
, A.ConstructionPeriodStart |
|
| 235 |
, A.ConstructionPeriodEnd |
|
| 236 |
, A.ConstructionPeriodStart2 |
|
| 237 |
, A.ConstructionPeriodEnd2 |
|
| 238 |
, A.StartDate |
|
| 239 |
, A.EndDate |
|
| 240 |
, A.PurchaseOrderMailingDate |
|
| 241 |
, A.PurchaseOrderReturnDate |
|
| 242 |
, A.PurchaseOrderReturnCheckDate |
|
| 243 |
, A.ConsumptionTaxFlg |
|
| 244 |
, A.PrimeContractorFlg |
|
| 245 |
, A.SalesCostFlg |
|
| 246 |
, A.ConstructionStatusFlg |
|
| 247 |
, A.ConstructionType |
|
| 248 |
, A.EstimateType |
|
| 249 |
, A.BillingStartDate |
|
| 250 |
, A.BillingDate |
|
| 251 |
, A.BillingSplitFlg |
|
| 252 |
, A.BillingSendDate |
|
| 253 |
, A.LedgerComplateDate |
|
| 254 |
, InspectPlanningDate |
|
| 255 |
, InspectExecuteDate |
|
| 256 |
, RevCompleteDate |
|
| 257 |
, A.EntryDate |
|
| 258 |
, A.UpdateDate |
|
| 259 |
FROM |
|
| 260 |
constructionbaseinfo_OLD As A |
|
| 261 |
Left Join chgchargedep As B |
|
| 262 |
On B.PersonCode = A.SalesPersonCode |
|
| 263 |
AND ( |
|
| 264 |
DATE (B.StartDate) <= DATE ('2017/07/31')
|
|
| 265 |
AND ( |
|
| 266 |
DATE ('2016/08/01') <= DATE (B.CompDate)
|
|
| 267 |
OR DATE ('0001/01/01') = DATE (B.CompDate)
|
|
| 268 |
) |
|
| 269 |
) |
|
| 270 |
Left Join chgchargedep As C |
|
| 271 |
On C.PersonCode = A.ConstructionPersonCode |
|
| 272 |
AND ( |
|
| 273 |
DATE (C.StartDate) <= DATE ('2017/07/31')
|
|
| 274 |
AND ( |
|
| 275 |
DATE ('2016/08/01') <= DATE (C.CompDate)
|
|
| 276 |
OR DATE ('0001/01/01') = DATE (C.CompDate)
|
|
| 277 |
) |
|
| 278 |
) |
|
| 279 |
Left Join chgchargedep As D |
|
| 280 |
On D.PersonCode = A.ConstrSubPersonCode |
|
| 281 |
AND ( |
|
| 282 |
DATE (D.StartDate) <= DATE ('2017/07/31')
|
|
| 283 |
AND ( |
|
| 284 |
DATE ('2016/08/01') <= DATE (D.CompDate)
|
|
| 285 |
OR DATE ('0001/01/01') = DATE (D.CompDate)
|
|
| 286 |
) |
|
| 287 |
) |
|
| 288 |
Left Join chgchargedep As E |
|
| 289 |
On E.PersonCode = A.ConstructionInstructor |
|
| 290 |
AND ( |
|
| 291 |
DATE (E.StartDate) <= DATE ('2017/07/31')
|
|
| 292 |
AND ( |
|
| 293 |
DATE ('2016/08/01') <= DATE (E.CompDate)
|
|
| 294 |
OR DATE ('0001/01/01') = DATE (E.CompDate)
|
|
| 295 |
) |
|
| 296 |
) |
|
| 297 |
Where A.ConstructionPeriod = 16 |
|
| 298 |
; |
|
| 299 |
|
|
| 300 |
Insert INTO constructionbaseinfo |
|
| 301 |
SELECT |
|
| 302 |
A.ConstructionCode |
|
| 303 |
, A.TyingFlg |
|
| 304 |
, A.JoinFlg |
|
| 305 |
, A.ConstructionYear |
|
| 306 |
, A.ConstructionPeriod |
|
| 307 |
, A.RequestedDate |
|
| 308 |
, A.EstimatesSubmitDeadline |
|
| 309 |
, A.EstimatesSubmittedDate |
|
| 310 |
, A.ProvisionalOrderDate |
|
| 311 |
, A.OrderDate |
|
| 312 |
, A.OrderStartingDate |
|
| 313 |
, A.OrderCompletionDate |
|
| 314 |
, A.PreparationStartDate |
|
| 315 |
, A.ConstructionStartingDate |
|
| 316 |
, A.ConstructionCompletionDate |
|
| 317 |
, A.NonOrderDate |
|
| 318 |
, A.OrdersDecisionPrice |
|
| 319 |
, A.OrdersDecisionPriceInTax |
|
| 320 |
, B.DepartmentCode |
|
| 321 |
, A.SalesPersonCode |
|
| 322 |
, ifnull(C.DepartmentCode, 0) |
|
| 323 |
, A.ConstructionPersonCode |
|
| 324 |
, ifnull(D.DepartmentCode, 0) |
|
| 325 |
, A.ConstrSubPersonCode |
|
| 326 |
, ifnull(E.DepartmentCode, 0) |
|
| 327 |
, A.ConstructionInstructor |
|
| 328 |
, A.TransferConstruction |
|
| 329 |
, A.TransferConstructionDate |
|
| 330 |
, A.OrderersDivision |
|
| 331 |
, A.OrderersCode |
|
| 332 |
, A.EstimatesExpirationDate |
|
| 333 |
, A.ConstructionPeriodStart |
|
| 334 |
, A.ConstructionPeriodEnd |
|
| 335 |
, A.ConstructionPeriodStart2 |
|
| 336 |
, A.ConstructionPeriodEnd2 |
|
| 337 |
, A.StartDate |
|
| 338 |
, A.EndDate |
|
| 339 |
, A.PurchaseOrderMailingDate |
|
| 340 |
, A.PurchaseOrderReturnDate |
|
| 341 |
, A.PurchaseOrderReturnCheckDate |
|
| 342 |
, A.ConsumptionTaxFlg |
|
| 343 |
, A.PrimeContractorFlg |
|
| 344 |
, A.SalesCostFlg |
|
| 345 |
, A.ConstructionStatusFlg |
|
| 346 |
, A.ConstructionType |
|
| 347 |
, A.EstimateType |
|
| 348 |
, A.BillingStartDate |
|
| 349 |
, A.BillingDate |
|
| 350 |
, A.BillingSplitFlg |
|
| 351 |
, A.BillingSendDate |
|
| 352 |
, A.LedgerComplateDate |
|
| 353 |
, InspectPlanningDate |
|
| 354 |
, InspectExecuteDate |
|
| 355 |
, RevCompleteDate |
|
| 356 |
, A.EntryDate |
|
| 357 |
, A.UpdateDate |
|
| 358 |
FROM |
|
| 359 |
constructionbaseinfo_OLD As A |
|
| 360 |
Left Join chgchargedep As B |
|
| 361 |
On B.PersonCode = A.SalesPersonCode |
|
| 362 |
AND ( |
|
| 363 |
DATE (B.StartDate) <= DATE ('2018/03/31')
|
|
| 364 |
AND ( |
|
| 365 |
DATE ('2017/08/01') <= DATE (B.CompDate)
|
|
| 366 |
OR DATE ('0001/01/01') = DATE (B.CompDate)
|
|
| 367 |
) |
|
| 368 |
) |
|
| 369 |
Left Join chgchargedep As C |
|
| 370 |
On C.PersonCode = A.ConstructionPersonCode |
|
| 371 |
AND ( |
|
| 372 |
DATE (C.StartDate) <= DATE ('2018/03/31')
|
|
| 373 |
AND ( |
|
| 374 |
DATE ('2017/08/01') <= DATE (C.CompDate)
|
|
| 375 |
OR DATE ('0001/01/01') = DATE (C.CompDate)
|
|
| 376 |
) |
|
| 377 |
) |
|
| 378 |
Left Join chgchargedep As D |
|
| 379 |
On D.PersonCode = A.ConstrSubPersonCode |
|
| 380 |
AND ( |
|
| 381 |
DATE (D.StartDate) <= DATE ('2018/03/31')
|
|
| 382 |
AND ( |
|
| 383 |
DATE ('2017/08/01') <= DATE (D.CompDate)
|
|
| 384 |
OR DATE ('0001/01/01') = DATE (D.CompDate)
|
|
| 385 |
) |
|
| 386 |
) |
|
| 387 |
Left Join chgchargedep As E |
|
| 388 |
On E.PersonCode = A.ConstructionInstructor |
|
| 389 |
AND ( |
|
| 390 |
DATE (E.StartDate) <= DATE ('2018/03/31')
|
|
| 391 |
AND ( |
|
| 392 |
DATE ('2017/08/01') <= DATE (E.CompDate)
|
|
| 393 |
OR DATE ('0001/01/01') = DATE (E.CompDate)
|
|
| 394 |
) |
|
| 395 |
) |
|
| 396 |
Where A.ConstructionPeriod = 17 |
|
| 397 |
; |
|
| 398 |
|
|
| 399 |
Insert INTO constructionbaseinfo |
|
| 400 |
SELECT |
|
| 401 |
A.ConstructionCode |
|
| 402 |
, A.TyingFlg |
|
| 403 |
, A.JoinFlg |
|
| 404 |
, A.ConstructionYear |
|
| 405 |
, A.ConstructionPeriod |
|
| 406 |
, A.RequestedDate |
|
| 407 |
, A.EstimatesSubmitDeadline |
|
| 408 |
, A.EstimatesSubmittedDate |
|
| 409 |
, A.ProvisionalOrderDate |
|
| 410 |
, A.OrderDate |
|
| 411 |
, A.OrderStartingDate |
|
| 412 |
, A.OrderCompletionDate |
|
| 413 |
, A.PreparationStartDate |
|
| 414 |
, A.ConstructionStartingDate |
|
| 415 |
, A.ConstructionCompletionDate |
|
| 416 |
, A.NonOrderDate |
|
| 417 |
, A.OrdersDecisionPrice |
|
| 418 |
, A.OrdersDecisionPriceInTax |
|
| 419 |
, B.DepartmentCode |
|
| 420 |
, A.SalesPersonCode |
|
| 421 |
, ifnull(C.DepartmentCode, 0) |
|
| 422 |
, A.ConstructionPersonCode |
|
| 423 |
, ifnull(D.DepartmentCode, 0) |
|
| 424 |
, A.ConstrSubPersonCode |
|
| 425 |
, ifnull(E.DepartmentCode, 0) |
|
| 426 |
, A.ConstructionInstructor |
|
| 427 |
, A.TransferConstruction |
|
| 428 |
, A.TransferConstructionDate |
|
| 429 |
, A.OrderersDivision |
|
| 430 |
, A.OrderersCode |
|
| 431 |
, A.EstimatesExpirationDate |
|
| 432 |
, A.ConstructionPeriodStart |
|
| 433 |
, A.ConstructionPeriodEnd |
|
| 434 |
, A.ConstructionPeriodStart2 |
|
| 435 |
, A.ConstructionPeriodEnd2 |
|
| 436 |
, A.StartDate |
|
| 437 |
, A.EndDate |
|
| 438 |
, A.PurchaseOrderMailingDate |
|
| 439 |
, A.PurchaseOrderReturnDate |
|
| 440 |
, A.PurchaseOrderReturnCheckDate |
|
| 441 |
, A.ConsumptionTaxFlg |
|
| 442 |
, A.PrimeContractorFlg |
|
| 443 |
, A.SalesCostFlg |
|
| 444 |
, A.ConstructionStatusFlg |
|
| 445 |
, A.ConstructionType |
|
| 446 |
, A.EstimateType |
|
| 447 |
, A.BillingStartDate |
|
| 448 |
, A.BillingDate |
|
| 449 |
, A.BillingSplitFlg |
|
| 450 |
, A.BillingSendDate |
|
| 451 |
, A.LedgerComplateDate |
|
| 452 |
, InspectPlanningDate |
|
| 453 |
, InspectExecuteDate |
|
| 454 |
, RevCompleteDate |
|
| 455 |
, A.EntryDate |
|
| 456 |
, A.UpdateDate |
|
| 457 |
FROM |
|
| 458 |
constructionbaseinfo_OLD As A |
|
| 459 |
Left Join chgchargedep As B |
|
| 460 |
On B.PersonCode = A.SalesPersonCode |
|
| 461 |
AND ( |
|
| 462 |
DATE (B.StartDate) <= DATE ('2019/03/31')
|
|
| 463 |
AND ( |
|
| 464 |
DATE ('2018/04/01') <= DATE (B.CompDate)
|
|
| 465 |
OR DATE ('0001/01/01') = DATE (B.CompDate)
|
|
| 466 |
) |
|
| 467 |
) |
|
| 468 |
Left Join chgchargedep As C |
|
| 469 |
On C.PersonCode = A.ConstructionPersonCode |
|
| 470 |
AND ( |
|
| 471 |
DATE (C.StartDate) <= DATE ('2019/03/31')
|
|
| 472 |
AND ( |
|
| 473 |
DATE ('2018/04/01') <= DATE (C.CompDate)
|
|
| 474 |
OR DATE ('0001/01/01') = DATE (C.CompDate)
|
|
| 475 |
) |
|
| 476 |
) |
|
| 477 |
Left Join chgchargedep As D |
|
| 478 |
On D.PersonCode = A.ConstrSubPersonCode |
|
| 479 |
AND ( |
|
| 480 |
DATE (D.StartDate) <= DATE ('2019/03/31')
|
|
| 481 |
AND ( |
|
| 482 |
DATE ('2018/04/01') <= DATE (D.CompDate)
|
|
| 483 |
OR DATE ('0001/01/01') = DATE (D.CompDate)
|
|
| 484 |
) |
|
| 485 |
) |
|
| 486 |
Left Join chgchargedep As E |
|
| 487 |
On E.PersonCode = A.ConstructionInstructor |
|
| 488 |
AND ( |
|
| 489 |
DATE (E.StartDate) <= DATE ('2019/03/31')
|
|
| 490 |
AND ( |
|
| 491 |
DATE ('2018/04/01') <= DATE (E.CompDate)
|
|
| 492 |
OR DATE ('0001/01/01') = DATE (E.CompDate)
|
|
| 493 |
) |
|
| 494 |
) |
|
| 495 |
Where A.ConstructionPeriod = 18 |
|
| 496 |
; |
|
| 497 |
|
|
| 498 |
-- ?H????{????
|
|
| 499 |
DROP TABLE IF EXISTS constructionbaseinfodetail_OLD; |
|
| 500 |
|
|
| 501 |
CREATE TABLE constructionbaseinfodetail_OLD As Select * from constructionbaseinfodetail; |
|
| 502 |
|
|
| 503 |
drop table if exists constructionbaseinfodetail cascade; |
|
| 504 |
|
|
| 505 |
create table constructionbaseinfodetail ( |
|
| 506 |
ConstructionCode decimal(10,0) unsigned not null comment '?H???R?[?h' |
|
| 507 |
, DetailNo decimal(3,0) not null comment '??????' |
|
| 508 |
, DetailString varchar(300) comment '??????e' |
|
| 509 |
, EntryDate datetime not null comment '?o?^???t' |
|
| 510 |
, UpdateDate datetime not null comment '?X?V???t' |
|
| 511 |
, constraint constructionbaseinfodetail_PKC primary key (ConstructionCode,DetailNo) |
|
| 512 |
) comment '?H????{????' ;
|
|
| 513 |
|
|
| 514 |
create index BaseInfoDetail_Idx1 |
|
| 515 |
on constructionbaseinfodetail(ConstructionCode); |
|
| 516 |
|
|
| 517 |
create index BaseInfoDetail_Idx2 |
|
| 518 |
on constructionbaseinfodetail(DetailNo); |
|
| 519 |
|
|
| 520 |
Insert INTO constructionbaseinfodetail |
|
| 521 |
SELECT * From constructionbaseinfodetail_OLD; |
|
| 522 |
|
|
| 523 |
Insert |
|
| 524 |
INTO constructionbaseinfodetail |
|
| 525 |
SELECT |
|
| 526 |
Base.ConstructionCode |
|
| 527 |
, 101 |
|
| 528 |
, Dep.DepartmentString |
|
| 529 |
, Now() |
|
| 530 |
, Now() |
|
| 531 |
From |
|
| 532 |
constructionbaseinfo As Base |
|
| 533 |
Inner Join departmentmaster as Dep |
|
| 534 |
On Dep.DepartmentCode = Base.SalesDepCode; |
|
| 535 |
|
|
| 536 |
Insert |
|
| 537 |
INTO constructionbaseinfodetail |
|
| 538 |
SELECT |
|
| 539 |
Base.ConstructionCode |
|
| 540 |
, 102 |
|
| 541 |
, ifnull(Dep.DepartmentString,'') |
|
| 542 |
, Now() |
|
| 543 |
, Now() |
|
| 544 |
From |
|
| 545 |
constructionbaseinfo As Base |
|
| 546 |
Inner Join departmentmaster as Dep |
|
| 547 |
On Dep.DepartmentCode = Base.ConstrDepCode; |
|
| 548 |
|
|
| 549 |
Insert |
|
| 550 |
INTO constructionbaseinfodetail |
|
| 551 |
SELECT |
|
| 552 |
Base.ConstructionCode |
|
| 553 |
, 103 |
|
| 554 |
, ifnull(Dep.DepartmentString,'') |
|
| 555 |
, Now() |
|
| 556 |
, Now() |
|
| 557 |
From |
|
| 558 |
constructionbaseinfo As Base |
|
| 559 |
Inner Join departmentmaster as Dep |
|
| 560 |
On Dep.DepartmentCode = Base.ConstrSubDepCode; |
|
| 561 |
|
|
| 562 |
Insert |
|
| 563 |
INTO constructionbaseinfodetail |
|
| 564 |
SELECT |
|
| 565 |
Base.ConstructionCode |
|
| 566 |
, 104 |
|
| 567 |
, ifnull(Dep.DepartmentString,'') |
|
| 568 |
, Now() |
|
| 569 |
, Now() |
|
| 570 |
From |
|
| 571 |
constructionbaseinfo As Base |
|
| 572 |
Inner Join departmentmaster as Dep |
|
| 573 |
On Dep.DepartmentCode = Base.ConstrInstrDepCode; |
|
| 574 |
|
|
| 575 |
UPDATE constructionbaseinfodetail AS BD |
|
| 576 |
SET |
|
| 577 |
BD.DetailString = '?R?{?g'
|
|
| 578 |
WHERE |
|
| 579 |
BD.ConstructionCode IN (SELECT |
|
| 580 |
ConstructionCode |
|
| 581 |
FROM |
|
| 582 |
constructionbaseinfo AS B |
|
| 583 |
WHERE |
|
| 584 |
ConstructionPeriod < 17) |
|
| 585 |
AND BD.DetailNo > 100 |
|
| 586 |
AND DetailString = '?????'; |
|
| 587 |
|
|
| 588 |
-- ??????o?^?\???f?[?^ |
|
| 589 |
drop table if exists costomerregist cascade; |
|
| 590 |
|
|
| 591 |
create table costomerregist ( |
|
| 592 |
CreatePersonCode decimal(8,0) unsigned not null comment '?\??????' |
|
| 593 |
, CreateDate date not null comment '????' |
|
| 594 |
, SeqNo decimal(2,0) not null comment '?L?[?A??' |
|
| 595 |
, DataMode decimal(1,0) not null comment '?f?[?^??' |
|
| 596 |
, SourceCotegory decimal(5,0) unsigned not null comment '???????' |
|
| 597 |
, SourceCode decimal(5,0) unsigned not null comment '??????R?[?h' |
|
| 598 |
, PetitionPeriod decimal(4,0) unsigned not null comment '?????' |
|
| 599 |
, OrderFlg decimal(1,0) not null comment '?@?l?c??E??l?t???O' |
|
| 600 |
, CorporateStatusName varchar(100) comment '?@?l?i????' |
|
| 601 |
, CorporateStatusPoint decimal(1,0) comment '?@?l?i?????u' |
|
| 602 |
, OrderersName1 varchar(60) not null comment '???????1' |
|
| 603 |
, OrderersName2 varchar(60) comment '????????Q' |
|
| 604 |
, DepartmentName varchar(60) comment '??????' |
|
| 605 |
, PersonName varchar(60) comment '?S?????' |
|
| 606 |
, ZipCode varchar(8) comment '?X????' |
|
| 607 |
, Address1 varchar(60) comment '?Z???P' |
|
| 608 |
, Address2 varchar(60) comment '?Z??2' |
|
| 609 |
, Address3 varchar(60) comment '?Z??3' |
|
| 610 |
, PhoneNumber varchar(13) comment '?d?b???' |
|
| 611 |
, FaxNumber varchar(13) comment 'FAX???' |
|
| 612 |
, MailAddress varchar(257) comment '???[???A?h???X' |
|
| 613 |
, Note varchar(300) comment '???l' |
|
| 614 |
, OrderCotegory decimal(5,0) unsigned not null comment '???????' |
|
| 615 |
, OrderDate date not null comment '?\????' |
|
| 616 |
, OrderNo decimal(2,0) unsigned not null comment '?\????t???' |
|
| 617 |
, CreateDepartmentCode decimal(5,0) unsigned not null comment '?\???????R?[?h' |
|
| 618 |
, CreateDepartmentName varchar(60) comment '?\????????' |
|
| 619 |
, EntryDate datetime not null comment '?o?^???t' |
|
| 620 |
, UpdateDate datetime not null comment '?X?V???t' |
|
| 621 |
, constraint costomerregist_PKC primary key (CreatePersonCode,CreateDate,SeqNo) |
|
| 622 |
) comment '??????o?^?\???f?[?^' ; |
|
| 623 |
|
|
| 624 |
create index CostomerRegist_Index1 |
|
| 625 |
on costomerregist(PetitionPeriod); |
|
| 626 |
|
|
| 627 |
create index CostomerRegist_Index2 |
|
| 628 |
on costomerregist(CreatePersonCode); |
|
| 629 |
|
|
| 630 |
create index CostomerRegist_Index3 |
|
| 631 |
on costomerregist(CreateDepartmentCode); |
|
| 632 |
|
|
| 633 |
commit; |
|
| branches/ddl/ProcessManagement.sql | ||
|---|---|---|
| 1 | 1 |
-- Project Name : noname |
| 2 |
-- Date/Time : 2018/03/01 9:40:48
|
|
| 2 |
-- Date/Time : 2018/03/28 9:13:42
|
|
| 3 | 3 |
-- Author : Horiuchi |
| 4 | 4 |
-- RDBMS Type : MySQL |
| 5 | 5 |
-- Application : A5:SQL Mk-2 |
| ... | ... | |
| 323 | 323 |
create index BusinessTypeMaster_Index1 |
| 324 | 324 |
on businesstypemaster(DisplayOrder); |
| 325 | 325 |
|
| 326 |
-- ?S????????????f?[?^ |
|
| 327 |
drop table if exists chgchargedep cascade; |
|
| 328 |
|
|
| 329 |
create table chgchargedep ( |
|
| 330 |
PersonCode decimal(8,0) not null comment '?S????R?[?h' |
|
| 331 |
, StartDate date not null comment '?J?n?N????' |
|
| 332 |
, CompDate date not null comment '?I???N????' |
|
| 333 |
, DepartmentCode decimal(5,0) unsigned not null comment '?????R?[?h' |
|
| 334 |
, DepartmentName varchar(60) comment '??????' |
|
| 335 |
, EntryDate datetime not null comment '?o?^???t' |
|
| 336 |
, UpdateDate datetime not null comment '?X?V???t' |
|
| 337 |
, constraint chgchargedep_PKC primary key (PersonCode,StartDate) |
|
| 338 |
) comment '?S????????????f?[?^' ; |
|
| 339 |
|
|
| 326 | 340 |
-- ????????}?X?^ |
| 327 | 341 |
drop table if exists commoncostlarge cascade; |
| 328 | 342 |
|
| ... | ... | |
| 432 | 446 |
create index ComponentToTypeMaster_Index2 |
| 433 | 447 |
on componenttotypemaster(TypeCode); |
| 434 | 448 |
|
| 449 |
-- ?H???S???????f?[?^ |
|
| 450 |
drop table if exists constrchgcharge cascade; |
|
| 451 |
|
|
| 452 |
create table constrchgcharge ( |
|
| 453 |
ConstructionCode decimal(10,0) unsigned not null comment '?H???R?[?h' |
|
| 454 |
, Division decimal(1,0) not null comment '????' |
|
| 455 |
, SeqNo smallint(5) unsigned not null comment '?A??' |
|
| 456 |
, DepartmentCode decimal(5,0) unsigned not null comment '?????R?[?h' |
|
| 457 |
, PersonCode decimal(8,0) not null comment '?S????R?[?h' |
|
| 458 |
, StartDate date comment '?S???J?n?N????' |
|
| 459 |
, CompDate date comment '?S???I???N????' |
|
| 460 |
, EntryDate datetime not null comment '?o?^???t' |
|
| 461 |
, UpdateDate datetime not null comment '?X?V???t' |
|
| 462 |
, constraint constrchgcharge_PKC primary key (ConstructionCode,Division,SeqNo) |
|
| 463 |
) comment '?H???S???????f?[?^' ; |
|
| 464 |
|
|
| 435 | 465 |
-- ?H????{???
|
| 436 | 466 |
drop table if exists constructionbaseinfo cascade; |
| 437 | 467 |
|
| ... | ... | |
| 454 | 484 |
, NonOrderDate date comment '????' |
| 455 | 485 |
, OrdersDecisionPrice decimal(12,0) not null comment '??????????z' |
| 456 | 486 |
, OrdersDecisionPriceInTax decimal(12,0) not null comment '??????????z' |
| 457 |
, SalesPersonCode decimal(8,0) comment '?c??S?????' |
|
| 458 |
, ConstructionPersonCode decimal(8,0) not null comment '?H???S????' |
|
| 459 |
, ConstrSubPersonCode decimal(8,0) not null comment '?H?????S????' |
|
| 460 |
, ConstructionInstructor decimal(8,0) not null comment '?H???w????' |
|
| 487 |
, SalesDepCode decimal(5,0) unsigned not null comment '?c??S????????R?[?h' |
|
| 488 |
, SalesPersonCode decimal(8,0) unsigned not null comment '?c??S????R?[?h' |
|
| 489 |
, ConstrDepCode decimal(5,0) unsigned not null comment '?H???S????????R?[?h' |
|
| 490 |
, ConstructionPersonCode decimal(8,0) not null comment '?H???S????R?[?h' |
|
| 491 |
, ConstrSubDepCode decimal(5,0) unsigned not null comment '?H?????S????????R?[?h' |
|
| 492 |
, ConstrSubPersonCode decimal(8,0) not null comment '?H?????S????R?[?h' |
|
| 493 |
, ConstrInstrDepCode decimal(5,0) unsigned not null comment '?H???w?????????R?[?h' |
|
| 494 |
, ConstructionInstructor decimal(8,0) not null comment '?H???w?????R?[?h' |
|
| 461 | 495 |
, TransferConstruction decimal(8,0) not null comment '?H??????R?[?h' |
| 462 | 496 |
, TransferConstructionDate date comment '?H??????' |
| 463 |
, OrderersDivision decimal(5,0) not null comment '???????' |
|
| 497 |
, OrderersDivision decimal(5,0) not null comment '????????R?[?h'
|
|
| 464 | 498 |
, OrderersCode decimal(5,0) unsigned not null comment '??????R?[?h' |
| 465 | 499 |
, EstimatesExpirationDate date comment '????L??????' |
| 466 | 500 |
, ConstructionPeriodStart date comment '?_??H???J?n??' |
| ... | ... | |
| 514 | 548 |
|
| 515 | 549 |
create table constructionbaseinfodetail ( |
| 516 | 550 |
ConstructionCode decimal(10,0) unsigned not null comment '?H???R?[?h' |
| 517 |
, DetailNo decimal(2,0) not null comment '??????'
|
|
| 551 |
, DetailNo decimal(3,0) not null comment '??????'
|
|
| 518 | 552 |
, DetailString varchar(300) comment '??????e' |
| 519 | 553 |
, EntryDate datetime not null comment '?o?^???t' |
| 520 | 554 |
, UpdateDate datetime not null comment '?X?V???t' |
| ... | ... | |
| 827 | 861 |
drop table if exists costomerregist cascade; |
| 828 | 862 |
|
| 829 | 863 |
create table costomerregist ( |
| 830 |
PersonCode decimal(8,0) unsigned not null comment '?\??????' |
|
| 864 |
CreatePersonCode decimal(8,0) unsigned not null comment '?\??????'
|
|
| 831 | 865 |
, CreateDate date not null comment '????' |
| 832 | 866 |
, SeqNo decimal(2,0) not null comment '?L?[?A??' |
| 833 | 867 |
, DataMode decimal(1,0) not null comment '?f?[?^??' |
| ... | ... | |
| 840 | 874 |
, OrderersName1 varchar(60) not null comment '???????1' |
| 841 | 875 |
, OrderersName2 varchar(60) comment '????????Q' |
| 842 | 876 |
, DepartmentName varchar(60) comment '??????' |
| 843 |
, ChargePersonName varchar(60) comment '?S?????'
|
|
| 877 |
, PersonName varchar(60) comment '?S?????' |
|
| 844 | 878 |
, ZipCode varchar(8) comment '?X????' |
| 845 | 879 |
, Address1 varchar(60) comment '?Z???P' |
| 846 | 880 |
, Address2 varchar(60) comment '?Z??2' |
| ... | ... | |
| 852 | 886 |
, OrderCotegory decimal(5,0) unsigned not null comment '???????' |
| 853 | 887 |
, OrderDate date not null comment '?\????' |
| 854 | 888 |
, OrderNo decimal(2,0) unsigned not null comment '?\????t???' |
| 889 |
, CreateDepartmentCode decimal(5,0) unsigned not null comment '?\???????R?[?h' |
|
| 890 |
, CreateDepartmentName varchar(60) comment '?\????????' |
|
| 855 | 891 |
, EntryDate datetime not null comment '?o?^???t' |
| 856 | 892 |
, UpdateDate datetime not null comment '?X?V???t' |
| 857 |
, constraint costomerregist_PKC primary key (PersonCode,CreateDate,SeqNo) |
|
| 893 |
, constraint costomerregist_PKC primary key (CreatePersonCode,CreateDate,SeqNo)
|
|
| 858 | 894 |
) comment '??????o?^?\???f?[?^' ; |
| 859 | 895 |
|
| 860 | 896 |
create index CostomerRegist_Index1 |
| 861 | 897 |
on costomerregist(PetitionPeriod); |
| 862 | 898 |
|
| 899 |
create index CostomerRegist_Index2 |
|
| 900 |
on costomerregist(CreatePersonCode); |
|
| 901 |
|
|
| 902 |
create index CostomerRegist_Index3 |
|
| 903 |
on costomerregist(CreateDepartmentCode); |
|
| 904 |
|
|
| 863 | 905 |
-- ????f?[?^ |
| 864 | 906 |
drop table if exists dailydataconstruction cascade; |
| 865 | 907 |
|
他の形式にエクスポート: Unified diff