Index: branches/ddl/20180323_SQL.txt
===================================================================
--- branches/ddl/20180323_SQL.txt	(nonexistent)
+++ branches/ddl/20180323_SQL.txt	(revision 332)
@@ -0,0 +1,38 @@
+-- ’S“–ŽÒ•”��—š—ðƒf�[ƒ^
+drop table if exists ChgChargeDep cascade;
+
+create table ChgChargeDep (
+  PersonCode  decimal(8,0) not null comment '’S“–ŽÒƒR�[ƒh'
+  , StartDate  date not null comment 'ŠJŽn”NŒŽ“ú'
+  , CompDate  date not null comment '�I—¹”NŒŽ“ú'
+  , DepartmentCode decimal(5,0) unsigned not null comment '•”��ƒR�[ƒh'
+  , DepartmentName varchar(60) comment '•”��–¼'
+  , EntryDate datetime not null comment '“o˜^“ú•t'
+  , UpdateDate datetime not null comment '�X�V“ú•t'
+  , constraint ChgChargeDep_PKC primary key (PersonCode, StartDate)
+) comment '’S“–ŽÒ•”��—š—ðƒf�[ƒ^' ;
+
+Insert INTO ChgChargeDep
+select
+  A.PersonCode
+  , A.StartDate
+  , A.EndDate
+  , A.DepartmentCode
+  , B.DepartmentString
+  , Now()
+  , Now() 
+From
+  personinchargemaster As A
+  Left Join departmentmaster As B
+  On B.DepartmentCode = A.DepartmentCode
+;
+
+update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 2, DepartmentName = '‰c‘U•”' Where PersonCode = 20150701 And DATE(StartDate) = DATE('2015/07/01');
+Insert chgchargedep Value(20150701, DATE('2017/08/01'), DATE('0001/01/01'), 1, '�HŽ–•”', NOW(), NOW());
+
+update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 1, DepartmentName = '�HŽ–•”' Where PersonCode = 20160602 And DATE(StartDate) = DATE('2016/06/20');
+Insert chgchargedep Value(20160602, DATE('2017/08/01'), DATE('0001/01/01'), 3, '‰c‹Æ•”', NOW(), NOW());
+
+update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 1, DepartmentName = '�HŽ–•”' Where PersonCode = 20160701 And DATE(StartDate) = DATE('2016/07/01');
+Insert chgchargedep Value(20160701, DATE('2017/08/01'), DATE('0001/01/01'), 2, '‰c‘U•”', NOW(), NOW());
+commit;
Index: branches/ddl/20180324_SQL.txt
===================================================================
--- branches/ddl/20180324_SQL.txt	(nonexistent)
+++ branches/ddl/20180324_SQL.txt	(revision 332)
@@ -0,0 +1,633 @@
+
+-- �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;
Index: branches/ddl/ProcessManagement.sql
===================================================================
--- branches/ddl/ProcessManagement.sql	(revision 331)
+++ branches/ddl/ProcessManagement.sql	(revision 332)
@@ -1,5 +1,5 @@
 -- Project Name : noname
--- Date/Time    : 2018/03/01 9:40:48
+-- Date/Time    : 2018/03/28 9:13:42
 -- Author       : Horiuchi
 -- RDBMS Type   : MySQL
 -- Application  : A5:SQL Mk-2
@@ -323,6 +323,20 @@
 create index BusinessTypeMaster_Index1
   on businesstypemaster(DisplayOrder);
 
+-- ’S“–ŽÒ•”��—š—ðƒf�[ƒ^
+drop table if exists chgchargedep cascade;
+
+create table chgchargedep (
+  PersonCode decimal(8,0) not null comment '’S“–ŽÒƒR�[ƒh'
+  , StartDate date not null comment 'ŠJŽn”NŒŽ“ú'
+  , CompDate date not null comment '�I—¹”NŒŽ“ú'
+  , DepartmentCode decimal(5,0) unsigned not null comment '•”��ƒR�[ƒh'
+  , DepartmentName varchar(60) comment '•”��–¼'
+  , EntryDate datetime not null comment '“o˜^“ú•t'
+  , UpdateDate datetime not null comment '�X�V“ú•t'
+  , constraint chgchargedep_PKC primary key (PersonCode,StartDate)
+) comment '’S“–ŽÒ•”��—š—ðƒf�[ƒ^' ;
+
 -- ‹¤’Ê”ï‘å�€–Úƒ}ƒXƒ^
 drop table if exists commoncostlarge cascade;
 
@@ -432,6 +446,22 @@
 create index ComponentToTypeMaster_Index2
   on componenttotypemaster(TypeCode);
 
+-- �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 cascade;
 
@@ -454,13 +484,17 @@
   , NonOrderDate date comment '”ñŽó’�“ú'
   , OrdersDecisionPrice decimal(12,0) not null comment '�Å”²Žó’�Œˆ’è‹àŠz'
   , OrdersDecisionPriceInTax decimal(12,0) not null comment '�Å�žŽó’�Œˆ’è‹àŠz'
-  , SalesPersonCode decimal(8,0) comment '‰c‹Æ’S“–ŽÒ–¼'
-  , ConstructionPersonCode decimal(8,0) not null comment '�HŽ–’S“–ŽÒ'
-  , ConstrSubPersonCode decimal(8,0) not null comment '�HŽ–•›’S“–ŽÒ'
-  , ConstructionInstructor decimal(8,0) not null comment '�HŽ–Žw“±ˆõ'
+  , 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 '”­’�ŽÒ‹æ•ª'
+  , 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“ú'
@@ -514,7 +548,7 @@
 
 create table constructionbaseinfodetail (
   ConstructionCode decimal(10,0) unsigned not null comment '�HŽ–ƒR�[ƒh'
-  , DetailNo decimal(2,0) not null comment '–¾�×”Ô�†'
+  , 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'
@@ -827,7 +861,7 @@
 drop table if exists costomerregist cascade;
 
 create table costomerregist (
-  PersonCode decimal(8,0) unsigned not null comment '�\�¿ŽÒ”Ô�†'
+  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�[ƒ^‹æ•ª'
@@ -840,7 +874,7 @@
   , OrderersName1 varchar(60) not null comment '”­’�ŽÒ–¼1'
   , OrderersName2 varchar(60) comment '”­’�ŽÒ–¼‚Q'
   , DepartmentName varchar(60) comment '•”��–¼'
-  , ChargePersonName varchar(60) comment '’S“–ŽÒ–¼'
+  , PersonName varchar(60) comment '’S“–ŽÒ–¼'
   , ZipCode varchar(8) comment '—X•Ö”Ô�†'
   , Address1 varchar(60) comment '�Z�Š‚P'
   , Address2 varchar(60) comment '�Z�Š2'
@@ -852,14 +886,22 @@
   , 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 (PersonCode,CreateDate,SeqNo)
+  , 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);
+
 -- “ú•ñƒf�[ƒ^
 drop table if exists dailydataconstruction cascade;
 
Index: branches/ddl/ƒe�[ƒuƒ‹Žd—l�‘.xlsx
===================================================================
Cannot display: file marked as a binary type.
svn:mime-type = application/octet-stream
