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

SET SESSION FOREIGN_KEY_CHECKS=0;

/* Drop Indexes */

DROP INDEX ActionScheduleData_Index1 ON ActionScheduleData;

DROP INDEX ActionScheduleData_Index2 ON ActionScheduleData;

DROP INDEX ActionScheduleData_Index3 ON ActionScheduleData;

DROP INDEX AttendDaily_Index1 ON AttendanceDailyData;

DROP INDEX AttendDaily_Index2 ON AttendanceDailyData;

DROP INDEX BusinessTypeMaster_Index1 ON BusinessTypeMaster;

DROP INDEX ComponentLinkMaster_Index1 ON ComponentLinkMaster;

DROP INDEX ComponentLinkMaster_Index2 ON ComponentLinkMaster;

DROP INDEX ComponentMaster_Index1 ON ComponentMaster;

DROP INDEX ComponentToTypeMaster_Index1 ON ComponentToTypeMaster;

DROP INDEX ComponentToTypeMaster_Index2 ON ComponentToTypeMaster;

DROP INDEX BaseInfo_Index1 ON ConstructionBaseInfo;

DROP INDEX BaseInfo_Index2 ON ConstructionBaseInfo;

DROP INDEX BaseInfo_Index3 ON ConstructionBaseInfo;

DROP INDEX BaseInfo_Index4 ON ConstructionBaseInfo;

DROP INDEX BaseInfo_Index5 ON ConstructionBaseInfo;

DROP INDEX BaseInfoDetail_Idx1 ON ConstructionBaseInfoDetail;

DROP INDEX BaseInfoDetail_Idx2 ON ConstructionBaseInfoDetail;

DROP INDEX LedgerDetail_Index01 ON ConstructionLedgerDetail;

DROP INDEX LedgerDetail_Index02 ON ConstructionLedgerDetail;

DROP INDEX LedgerDetail_Index03 ON ConstructionLedgerDetail;

DROP INDEX LedgerExcute_Index1 ON ConstructionLedgerExcute;

DROP INDEX ConstructionLink_Index1 ON ConstructionLink;

DROP INDEX ConstructionLink_Index2 ON ConstructionLink;

DROP INDEX ConstructionLink_Index3 ON ConstructionLink;

DROP INDEX ConstructionSpecMaster_Index1 ON ConstructionSpecMaster;

DROP INDEX CostDataOfPerson_Index1 ON CostDataOfPerson;

DROP INDEX CostDataOfPerson_Index2 ON CostDataOfPerson;

DROP INDEX CostomerRegist_Index1 ON CostomerRegist;

DROP INDEX DailyDataConstruction_Index1 ON DailyDataConstruction;

DROP INDEX DailyDataConstruction_Index2 ON DailyDataConstruction;

DROP INDEX DailyDataConstruction_Index3 ON DailyDataConstruction;

DROP INDEX DailyDataDetail_Index ON DailyDataDetail;

DROP INDEX DailyDataField_Index ON DailyDataField;

DROP INDEX DailyDataMaterials_Index ON DailyDataMaterials;

DROP INDEX DailyDataSubcontractors_Index ON DailyDataSubcontractors;

DROP INDEX DailyDataVehicles_Index ON DailyDataVehicles;

DROP INDEX DepartmentMaster_index1 ON DepartmentMaster;

DROP INDEX EstimateData_Index1 ON EstimateData;

DROP INDEX EstimateDataBody_Index1 ON EstimateDataBody;

DROP INDEX EstimateDataDetail_Index1 ON EstimateDataDetail;

DROP INDEX HolidayCalender_Index1 ON HolidayCalenderMaster;

DROP INDEX HolidayCalenderMaster_Index2 ON HolidayCalenderMaster;

DROP INDEX HolidayCalenderMaster_Index3 ON HolidayCalenderMaster;

DROP INDEX InputSearchLogData_Index1 ON InputSearchLogData;

DROP INDEX MessageBoardData_Index1 ON MessageBoardData;

DROP INDEX MessageBoardData_Index2 ON MessageBoardData;

DROP INDEX MessageBoardTerget_Index1 ON MessageBoardTerget;

DROP INDEX PersonDepartmentMaster_Index1 ON PersonDepartmentMaster;

DROP INDEX PersonDepartmentMaster_Index2 ON PersonDepartmentMaster;

DROP INDEX PersonInChargeMaster_Index1 ON PersonInChargeMaster;

DROP INDEX PersonInChargeMaster_Index2 ON PersonInChargeMaster;

DROP INDEX PersonSalaryMaster_Index1 ON PersonSalaryMaster;

DROP INDEX PersonSalaryMaster_Index2 ON PersonSalaryMaster;

DROP INDEX ProcessExcuteMaster_Index1 ON ProcessExcuteMaster;

DROP INDEX ProcessExcuteMaster_Index2 ON ProcessExcuteMaster;

DROP INDEX POrderDetail_Index1 ON PurchaseOrderDetail;

DROP INDEX SalesGraphData_Idx1 ON SalesGraphData;

DROP INDEX SalesGraphData_idx2 ON SalesGraphData;

DROP INDEX SecurityMaster_Index1 ON SecurityMaster;

DROP INDEX SecurityMaster_Index2 ON SecurityMaster;

DROP INDEX SecurityMaster_Index3 ON SecurityMaster;

DROP INDEX SubContractorJobItem_Index1 ON SubContractorJobItem;

DROP INDEX SubContractorJobItem_Index2 ON SubContractorJobItem;

DROP INDEX SubContractorJobLink_Index1 ON SubContractorJobLink;

DROP INDEX SubContractorJobLink_Index2 ON SubContractorJobLink;

DROP INDEX SubContractorMaster_Index1 ON SubContractorMaster;

DROP INDEX TermMaster_Index1 ON TermMaster;

DROP INDEX TranceportDailyData_Index1 ON TranceportDailyData;

DROP INDEX UnitMaster_Index1 ON UnitMaster;

DROP INDEX VehicleScheduleData_Index1 ON VehicleScheduleData;

DROP INDEX VehicleScheduleData_Index2 ON VehicleScheduleData;

DROP INDEX VehicleScheduleData_Index3 ON VehicleScheduleData;

/* Drop Tables */

DROP TABLE IF EXISTS ActionScheduleData;

DROP TABLE IF EXISTS ApprovalMaster;

DROP TABLE IF EXISTS AttendanceDailyData;

DROP TABLE IF EXISTS AttendanceData;

DROP TABLE IF EXISTS BK_ConstructionLedger;

DROP TABLE IF EXISTS BK_ConstructionLedgerDetail;

DROP TABLE IF EXISTS BK_ConstructionLedgerExcute;

DROP TABLE IF EXISTS BK_EstimateData;

DROP TABLE IF EXISTS BK_EstimateDataBody;

DROP TABLE IF EXISTS BK_EstimateDataDetail;

DROP TABLE IF EXISTS BusinessTypeMaster;

DROP TABLE IF EXISTS CommonCostLarge;

DROP TABLE IF EXISTS CommonCostLinkMS;

DROP TABLE IF EXISTS CommonCostMiddle;

DROP TABLE IF EXISTS CommonCostMiddleSearchWord;

DROP TABLE IF EXISTS ComponentLinkMaster;

DROP TABLE IF EXISTS ComponentMaster;

DROP TABLE IF EXISTS ComponentToTypeMaster;

DROP TABLE IF EXISTS ConstructionBaseInfo;

DROP TABLE IF EXISTS ConstructionBaseInfoDetail;

DROP TABLE IF EXISTS ConstructionBudget;

DROP TABLE IF EXISTS ConstructionBudgetDetail;

DROP TABLE IF EXISTS ConstructionItemMaster;

DROP TABLE IF EXISTS ConstructionLedger;

DROP TABLE IF EXISTS ConstructionLedgerDetail;

DROP TABLE IF EXISTS ConstructionLedgerExcute;

DROP TABLE IF EXISTS ConstructionLink;

DROP TABLE IF EXISTS ConstructionProgressDate;

DROP TABLE IF EXISTS ConstructionSpecMaster;

DROP TABLE IF EXISTS ConstructionSpecUnitPrice;

DROP TABLE IF EXISTS ConstructionTypeMaster;

DROP TABLE IF EXISTS CostDataOfPerson;

DROP TABLE IF EXISTS CostomerRegist;

DROP TABLE IF EXISTS DailyDataConstruction;

DROP TABLE IF EXISTS DailyDataDetail;

DROP TABLE IF EXISTS DailyDataField;

DROP TABLE IF EXISTS DailyDataMaterials;

DROP TABLE IF EXISTS DailyDataSubcontractors;

DROP TABLE IF EXISTS DailyDataVehicles;

DROP TABLE IF EXISTS DepartmentExpensesMaster;

DROP TABLE IF EXISTS DepartmentMaster;

DROP TABLE IF EXISTS DivisionMaster;

DROP TABLE IF EXISTS EstimateBudget;

DROP TABLE IF EXISTS EstimateBudgetDetail;

DROP TABLE IF EXISTS EstimateData;

DROP TABLE IF EXISTS EstimateDataBody;

DROP TABLE IF EXISTS EstimateDataDetail;

DROP TABLE IF EXISTS HolidayCalenderMaster;

DROP TABLE IF EXISTS InputSearchLogData;

DROP TABLE IF EXISTS MessageBoardData;

DROP TABLE IF EXISTS MessageBoardTerget;

DROP TABLE IF EXISTS MessageBrowsingHistory;

DROP TABLE IF EXISTS OrderersMaster;

DROP TABLE IF EXISTS OrdersPriceData;

DROP TABLE IF EXISTS PersonApproval;

DROP TABLE IF EXISTS PersonApprovalComment;

DROP TABLE IF EXISTS PersonDepartmentMaster;

DROP TABLE IF EXISTS PersonInChargeMaster;

DROP TABLE IF EXISTS PersonSalaryMaster;

DROP TABLE IF EXISTS ProceedingsData;

DROP TABLE IF EXISTS ProceedingsDataAttend;

DROP TABLE IF EXISTS ProceedingsDataDetail;

DROP TABLE IF EXISTS ProcessApproval;

DROP TABLE IF EXISTS ProcessApprovalComment;

DROP TABLE IF EXISTS ProcessApprovalDetail;

DROP TABLE IF EXISTS ProcessExcuteMaster;

DROP TABLE IF EXISTS PurchaseOrder;

DROP TABLE IF EXISTS PurchaseOrderDetail;

DROP TABLE IF EXISTS SalesGraphData;

DROP TABLE IF EXISTS SecurityMaster;

DROP TABLE IF EXISTS SubConstrJobItemRegist;

DROP TABLE IF EXISTS SubConstrJobLinkRegist;

DROP TABLE IF EXISTS SubConstrRegist;

DROP TABLE IF EXISTS SubContractorItemMaster;

DROP TABLE IF EXISTS SubContractorJobCategory;

DROP TABLE IF EXISTS SubContractorJobItem;

DROP TABLE IF EXISTS SubContractorJobLink;

DROP TABLE IF EXISTS SubContractorMaster;

DROP TABLE IF EXISTS SuppliersMaster;

DROP TABLE IF EXISTS SystemExecute;

DROP TABLE IF EXISTS SystemMaster;

DROP TABLE IF EXISTS TermMaster;

DROP TABLE IF EXISTS TranceportDailyData;

DROP TABLE IF EXISTS TypeExpensesMaster;

DROP TABLE IF EXISTS UnitMaster;

DROP TABLE IF EXISTS VehicleMaster;

DROP TABLE IF EXISTS VehicleRecordData;

DROP TABLE IF EXISTS VehicleScheduleData;

/* Create Tables */

-- 担当者行動予定データ

CREATE TABLE ActionScheduleData

(

        TargetDate date NOT NULL COMMENT '対象日',

        PersonCode decimal(8,0) NOT NULL COMMENT '担当者コード',

        ActionSchedule varchar(30) COMMENT '行動予定',

        TargetYear year(4) NOT NULL COMMENT '該当年',

        TargetMonth year(2) NOT NULL COMMENT '該当月',

        TargetDay year(2) NOT NULL COMMENT '該当日',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (TargetDate, PersonCode)

) COMMENT = '担当者行動予定データ';

-- 承認経路マスタ

CREATE TABLE ApprovalMaster

(

        ApprovalCode decimal(3,0) unsigned NOT NULL COMMENT '承認機能番号',

        DepartmentCode decimal(5,0) unsigned NOT NULL COMMENT '部署コード',

        SeqNo decimal(2) NOT NULL COMMENT '枝番',

        DisplayOrder decimal(2) NOT NULL COMMENT '表示順',

        ApprovalPerson decimal(8) unsigned NOT NULL COMMENT '承認者コード',

        ApprovalAuthority decimal(1) NOT NULL COMMENT '承認権限区分',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ApprovalCode, DepartmentCode, SeqNo)

) COMMENT = '承認経路マスタ';

-- 出勤日報データ

CREATE TABLE AttendanceDailyData

(

        PersonCode decimal(8,0) unsigned NOT NULL COMMENT '作成者コード',

        AttendanceDate date NOT NULL COMMENT '日報作成日',

        SeqNo decimal(3) unsigned NOT NULL COMMENT '明細行番号',

        ActionResult varchar(30) NOT NULL COMMENT '行動実績',

        OvertimeHours decimal(4,1) NOT NULL COMMENT '残業時間',

        WorkingComment varchar(120) COMMENT 'コメント',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (PersonCode, AttendanceDate, SeqNo)

) COMMENT = '出勤日報データ';

-- 出勤管理データ

CREATE TABLE AttendanceData

(

        PersonCode decimal(8,0) unsigned NOT NULL COMMENT '作成者コード',

        AttendanceMonth date NOT NULL COMMENT '出勤管理年月',

        LastDays decimal(2) unsigned NOT NULL COMMENT '当月最終日',

        AttendDayCount decimal(2) unsigned NOT NULL COMMENT '出勤日数',

        TotalOverTimes decimal(4,1) NOT NULL COMMENT '残業時間数',

        OrderDate date NOT NULL COMMENT '申請日',

        OrderNo decimal(2) unsigned NOT NULL COMMENT '申請受付番号',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (PersonCode, AttendanceMonth)

) COMMENT = '出勤管理データ';

-- 工事詳細台帳データ【退避用】

CREATE TABLE BK_ConstructionLedger

(

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        ConstructionPeriod decimal(4) unsigned NOT NULL COMMENT '事業期',

        CreatorCode decimal(8,0) COMMENT '作成者コード',

        CreatorName varchar(60) COMMENT '作成者名',

        CreateDate date NOT NULL COMMENT '作成日',

        ConstructionTimes decimal(5,2) NOT NULL COMMENT '工期（単位・月）',

        ConstructionStart date NOT NULL COMMENT '契約工期開始',

        ConstructionEnd date NOT NULL COMMENT '契約工期完了',

        OrdersDecisionPrice decimal(14,0) NOT NULL COMMENT '税抜受注決定金額',

        CompanyExpenses decimal(10,0) NOT NULL COMMENT '会社経費合計',

        DepartmentExpenses decimal(10,0) NOT NULL COMMENT '部署経費合計',

        SalesExpenses decimal(10,0) NOT NULL COMMENT '営業経費合計',

        TotalPayment decimal(10,0) NOT NULL COMMENT '総支払額',

        GrossProfit decimal(10,0) NOT NULL COMMENT '粗利',

        Allowance decimal(10,0) NOT NULL COMMENT '給与',

        NetProfit decimal(10,0) NOT NULL COMMENT '純利益',

        ComplateFlg decimal(1) NOT NULL COMMENT '入力完了フラグ',

        IntegrationFlg decimal(1) NOT NULL COMMENT '受注金額積算フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode)

) COMMENT = '工事詳細台帳データ【退避用】';

-- 工事詳細台帳明細データ【退避用】

CREATE TABLE BK_ConstructionLedgerDetail

(

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        GroupCount decimal(2,0) unsigned NOT NULL COMMENT 'グループ番号',

        LineCount decimal(5) unsigned NOT NULL COMMENT '行番号',

        ComponentCode decimal(5,0) unsigned NOT NULL COMMENT '構成キー',

        ItemCode decimal(5,0) unsigned NOT NULL COMMENT '工種キー',

        FirstString varchar(120) COMMENT '項目名称',

        SecondString varchar(120) COMMENT '工事内容',

        CompanyType decimal(1) NOT NULL COMMENT '協力会社コードタイプ',

        CompanyCode decimal(8,0) NOT NULL COMMENT '協力会社コード',

        CompanyName varchar(120) COMMENT '協力会社名称',

        EstimatePrice decimal(12,0) NOT NULL COMMENT '予算（見積）金額',

        ExecutionAmount decimal(12,0) NOT NULL COMMENT '実行金額',

        AmountConfigRate decimal(5,2) NOT NULL COMMENT '金額構成率',

        PaymentBurden decimal(12,0) NOT NULL COMMENT '支払補填額',

        FixDataFlg decimal(1) NOT NULL COMMENT '固定データフラグ',

        IndependentFlg decimal(1) NOT NULL COMMENT '独立データフラグ',

        FluctuationFlg decimal(1) NOT NULL COMMENT '増減データフラグ',

        SalaryFlg decimal(1) NOT NULL COMMENT '給与振分区分',

        SalaryDays decimal(4) NOT NULL COMMENT '給与振分日数',

        OperatingFlg decimal(1) NOT NULL COMMENT '担当中フラグ',

        SourceCode decimal(10) unsigned NOT NULL COMMENT '元工事番号',

        JoinTitleFlg decimal(1) NOT NULL COMMENT '工事名称タイトル',

        SalaryOnRegist decimal(12,0) NOT NULL COMMENT '登録時月額給与',

        PurchaseOrderFlg decimal(2) NOT NULL COMMENT '注文書発行フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode, GroupCount, LineCount)

) COMMENT = '工事詳細台帳明細データ【退避用】';

-- 工事詳細台帳実行データ【退避用】

CREATE TABLE BK_ConstructionLedgerExcute

(

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        GroupCount decimal(2,0) unsigned NOT NULL COMMENT 'グループ番号',

        LineCount decimal(5) unsigned NOT NULL COMMENT '行番号',

        ColumnCount decimal(3) NOT NULL COMMENT '列番号',

        PaymentAmount decimal(12,0) NOT NULL COMMENT '支払金額',

        TargetMonth date NOT NULL COMMENT '対象年月',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode, GroupCount, LineCount, ColumnCount)

) COMMENT = '工事詳細台帳実行データ【退避用】';

-- 積算見積データ【退避用】

CREATE TABLE BK_EstimateData

(

        SaveDate datetime NOT NULL COMMENT '退避日付',

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        LineCount decimal(3) unsigned NOT NULL COMMENT '行番号',

        ComponentCode decimal(5,0) unsigned NOT NULL COMMENT '構成キー',

        FixedItemCode varchar(1) COMMENT '固定項目キー',

        ItemName varchar(120) COMMENT '作業名称',

        SpecName varchar(120) COMMENT '作業／品質・形状・寸法',

        PriceValue decimal(14,0) NOT NULL COMMENT '金額',

        note varchar(60) COMMENT '備考',

        MyExpensesFlg decimal(1) NOT NULL COMMENT '自社経費フラグ',

        InputFlg decimal(1) NOT NULL COMMENT '入力フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (SaveDate, ConstructionCode, LineCount)

) COMMENT = '積算見積データ【退避用】';

-- 積算見積ページデータ【退避用】

CREATE TABLE BK_EstimateDataBody

(

        SaveDate datetime NOT NULL COMMENT '退避日付',

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        PageCount decimal(4) unsigned NOT NULL COMMENT 'ページ番号',

        Category decimal(1) NOT NULL COMMENT 'ページ区分',

        UnionComponentCode decimal(5) unsigned NOT NULL COMMENT '所属構成キー',

        ComponentCode decimal(5,0) unsigned NOT NULL COMMENT '構成キー',

        ItemCode decimal(5,0) unsigned NOT NULL COMMENT '工種キー',

        SelectComponent decimal(5) unsigned NOT NULL COMMENT '選択構成キー',

        PageTitle varchar(120) NOT NULL COMMENT 'ページ表示名',

        DetailLineCount decimal(3) unsigned NOT NULL COMMENT '明細行番号',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (SaveDate, ConstructionCode, PageCount)

) COMMENT = '積算見積ページデータ【退避用】';

-- 積算見積データ明細【退避用】

CREATE TABLE BK_EstimateDataDetail

(

        SaveDate datetime NOT NULL COMMENT '退避日付',

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        PageCount decimal(4) unsigned NOT NULL COMMENT 'ページ番号',

        LineCount decimal(3) unsigned NOT NULL COMMENT '行番号',

        DataType decimal(1) NOT NULL COMMENT 'データ種別',

        ComponentCode decimal(5,0) unsigned NOT NULL COMMENT '構成キー',

        ItemCode decimal(5,0) unsigned NOT NULL COMMENT '工種キー',

        SpecCode decimal(5,0) unsigned NOT NULL COMMENT '作業キー',

        ItemName varchar(120) COMMENT '作業名称',

        SpecName varchar(120) COMMENT '作業／品質・形状・寸法',

        Unitcount decimal(8,2) NOT NULL COMMENT '数量',

        UnitName varchar(30) COMMENT '単位名称',

        UnitPrice decimal(9,2) NOT NULL COMMENT '単価',

        LineTotal decimal(12,0) NOT NULL COMMENT '金額',

        note varchar(60) COMMENT '備考',

        CompanyCode decimal(8) NOT NULL COMMENT '協力会社コード',

        MyExpensesFlg decimal(1) NOT NULL COMMENT '自社経費フラグ',

        InputFlg decimal(1) COMMENT '入力フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (SaveDate, ConstructionCode, PageCount, LineCount)

) COMMENT = '積算見積データ明細【退避用】';

-- 法人格マスタ

CREATE TABLE BusinessTypeMaster

(

        BusinessTypeCode decimal(5,0) unsigned NOT NULL COMMENT '法人格コード',

        DisplayOrder decimal(5) unsigned NOT NULL COMMENT '表示順',

        BusinessTypeName varchar(100) DEFAULT '' NOT NULL COMMENT '業種名',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (BusinessTypeCode)

) COMMENT = '法人格マスタ';

-- 共通費大項目マスタ

CREATE TABLE CommonCostLarge

(

        LargeCode decimal(5,0) unsigned NOT NULL COMMENT '大項目キー',

        DisplayOrder decimal(5) unsigned NOT NULL COMMENT '表示順',

        LargeName varchar(50) DEFAULT '' NOT NULL COMMENT '大項目名称',

        CommentText varchar(120) COMMENT '説明書き',

        DeleteFlg decimal(1) NOT NULL COMMENT '削除フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (LargeCode)

) COMMENT = '共通費大項目マスタ';

-- 共通費工種関連マスタ

CREATE TABLE CommonCostLinkMS

(

        LargeCode decimal(5,0) unsigned NOT NULL COMMENT '大項目キー',

        MiddleCode decimal(5,0) unsigned NOT NULL COMMENT '中項目キー',

        ItemCode decimal(5,0) unsigned NOT NULL COMMENT '工種キー',

        EntryDate datetime COMMENT '登録日付',

        UpdateDate datetime COMMENT '更新日付',

        PRIMARY KEY (LargeCode, MiddleCode, ItemCode)

) COMMENT = '共通費工種関連マスタ';

-- 共通費中項目マスタ

CREATE TABLE CommonCostMiddle

(

        LargeCode decimal(5,0) unsigned NOT NULL COMMENT '大項目キー',

        MiddleCode decimal(5,0) unsigned NOT NULL COMMENT '中項目キー',

        DisplayOrder decimal(5) unsigned NOT NULL COMMENT '表示順',

        MiddleName varchar(50) DEFAULT '' NOT NULL COMMENT '中項目名称',

        CommentText varchar(120) COMMENT '説明書き',

        DeleteFlg decimal(1) NOT NULL COMMENT '削除フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (LargeCode, MiddleCode)

) COMMENT = '共通費中項目マスタ';

-- 共通費中項目キーマスタ

CREATE TABLE CommonCostMiddleSearchWord

(

        LargeCode decimal(5,0) unsigned NOT NULL COMMENT '大項目キー',

        MiddleCode decimal(5,0) unsigned NOT NULL COMMENT '中項目キー',

        SeqNo decimal(5) unsigned NOT NULL COMMENT '順序',

        SearchWord varchar(50) DEFAULT '' NOT NULL COMMENT '検索項目',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (LargeCode, MiddleCode, SeqNo)

) COMMENT = '共通費中項目キーマスタ';

-- 構成工種関連マスタ

CREATE TABLE ComponentLinkMaster

(

        ComponentCode decimal(5,0) unsigned NOT NULL COMMENT '構成キー',

        ItemCode decimal(5,0) unsigned NOT NULL COMMENT '工種キー',

        DisplayOrder decimal(5) unsigned COMMENT '表示順',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ComponentCode, ItemCode)

) COMMENT = '構成工種関連マスタ';

-- 構成マスタ

CREATE TABLE ComponentMaster

(

        ComponentCode decimal(5,0) unsigned NOT NULL COMMENT '構成キー',

        DisplayOrder decimal(5) unsigned NOT NULL COMMENT '表示順',

        ComponentName varchar(120) DEFAULT '' NOT NULL COMMENT '構成名称',

        SubItemsFlg decimal(1) COMMENT '下位項目フラグ',

        DirectInputFlg decimal(1) COMMENT '直接入力フラグ',

        MyExpensesFlg decimal(1) COMMENT '自社経費フラグ',

        DeleteFlg decimal(1) NOT NULL COMMENT '削除フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ComponentCode)

) COMMENT = '構成マスタ';

-- 構成工事種別関連マスタ

CREATE TABLE ComponentToTypeMaster

(

        ComponentCode decimal(5,0) unsigned NOT NULL COMMENT '構成キー',

        TypeCode decimal(5,0) unsigned NOT NULL COMMENT '工事種別コード',

        Dummy decimal(1) NOT NULL COMMENT 'ダミー項目',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ComponentCode, TypeCode)

) COMMENT = '構成工事種別関連マスタ';

-- 工事基本情報

CREATE TABLE ConstructionBaseInfo

(

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        TyingFlg decimal(1) NOT NULL COMMENT '紐付データフラグ',

        JoinFlg decimal(1) NOT NULL COMMENT '結合契約工事フラグ',

        ConstructionYear smallint unsigned NOT NULL COMMENT '工事年度',

        ConstructionPeriod smallint unsigned NOT NULL COMMENT '工事期数',

        RequestedDate date COMMENT '依頼受け日',

        EstimatesSubmitDeadline date COMMENT '見積提出期限',

        EstimatesSubmittedDate date COMMENT '見積提出日',

        ProvisionalOrderDate date COMMENT '仮受注日',

        OrderDate date COMMENT '受注日',

        OrderStartingDate date COMMENT '開始予定日',

        OrderCompletionDate date COMMENT '完了予定日',

        PreparationStartDate date COMMENT '施工準備開始日',

        ConstructionStartingDate date COMMENT '施工開始日',

        ConstructionCompletionDate date COMMENT '施工完了日',

        NonOrderDate date COMMENT '非受注日',

        OrdersDecisionPrice decimal(12,0) NOT NULL COMMENT '税抜受注決定金額',

        OrdersDecisionPriceInTax decimal(12,0) NOT NULL COMMENT '税込受注決定金額',

        SalesPersonCode decimal(8,0) COMMENT '営業担当者名',

        ConstructionPersonCode decimal(8,0) NOT NULL COMMENT '工事担当者',

        ConstrSubPersonCode decimal(8,0) NOT NULL COMMENT '工事副担当者',

        ConstructionInstructor decimal(8,0) NOT NULL COMMENT '工事指導員',

        TransferConstruction decimal(8,0) NOT NULL COMMENT '工事移管先コード',

        TransferConstructionDate date COMMENT '工事移管日',

        OrderersDivision decimal(5) NOT NULL COMMENT '発注者区分',

        OrderersCode decimal(5,0) unsigned NOT NULL COMMENT '発注者コード',

        EstimatesExpirationDate date COMMENT '見積有効期限',

        ConstructionPeriodStart date COMMENT '契約工期開始日',

        ConstructionPeriodEnd date COMMENT '契約工期完了',

        ConstructionPeriodStart2 date COMMENT '契約工期開始（延長時）',

        ConstructionPeriodEnd2 date COMMENT '契約工期完了（延長時）',

        StartDate date COMMENT '工事開始日',

        EndDate date COMMENT '工事終了日',

        PurchaseOrderMailingDate date COMMENT '注文書郵送日',

        PurchaseOrderReturnDate date COMMENT '注文書返送日',

        PurchaseOrderReturnCheckDate date COMMENT '注文書返送確認日',

        ConsumptionTaxFlg decimal(1) NOT NULL COMMENT '消費税フラグ',

        PrimeContractorFlg decimal(1) NOT NULL COMMENT '元請フラグ',

        SalesCostFlg decimal(1) NOT NULL COMMENT '営業経費発生フラグ',

        ConstructionStatusFlg decimal(2,0) NOT NULL COMMENT '工事状態フラグ',

        ConstructionType decimal(5) NOT NULL COMMENT '工事種別コード',

        EstimateType decimal(1) NOT NULL COMMENT '見積種別',

        BillingStartDate date NOT NULL COMMENT '請求準備開始日',

        BillingDate date NOT NULL COMMENT '請求日',

        BillingSplitFlg decimal(1) NOT NULL COMMENT '請求回数フラグ',

        BillingSendDate date COMMENT '請求書郵送/手渡し日',

        LedgerComplateDate date NOT NULL COMMENT '工事詳細台帳入力完了日付',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode)

) COMMENT = '工事基本情報';

-- 工事基本情報明細

CREATE TABLE ConstructionBaseInfoDetail

(

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        DetailNo decimal(2) NOT NULL COMMENT '明細番号',

        DetailString varchar(120) COMMENT '明細内容',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode, DetailNo)

) COMMENT = '工事基本情報明細';

-- 工事施工予算データ

CREATE TABLE ConstructionBudget

(

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        CreatorCode decimal(8,0) NOT NULL COMMENT '作成者コード',

        CreatorName varchar(60) COMMENT '作成者名',

        CreatorCosts decimal(11,0) NOT NULL COMMENT '作成者給与',

        AssistantCode decimal(8,0) NOT NULL COMMENT '副担当者コード',

        AssistantName varchar(60) COMMENT '副担当者名',

        AssistantCosts decimal(11,0) NOT NULL COMMENT '副担当者給与',

        InstructorCode decimal(8,0) NOT NULL COMMENT '工事指導員コード',

        InstructorName varchar(60) COMMENT '工事指導員名',

        InstructorCosts decimal(11,0) NOT NULL COMMENT '工事指導員給与',

        CreateDate date NOT NULL COMMENT '作成日',

        ConstructionTimes decimal(5,2) NOT NULL COMMENT '工期（単位・月）',

        ConstructionStart date NOT NULL COMMENT '契約工期開始',

        ConstructionEnd date NOT NULL COMMENT '契約工期完了',

        InstructorTimes decimal(5,2) NOT NULL COMMENT '指導員稼働月数',

        SalaryFlg decimal(1) NOT NULL COMMENT '給与振分区分',

        SalaryDays decimal(4) NOT NULL COMMENT '振分日数',

        A_SalaryFlg decimal(1) NOT NULL COMMENT '副担当者給与振分区分',

        A_SalaryDays decimal(4) NOT NULL COMMENT '副担当者振分日数',

        I_SalaryFlg decimal(1) NOT NULL COMMENT '指導員給与振分区分',

        I_SalaryDays decimal(4) NOT NULL COMMENT '指導員振分日数',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode)

) COMMENT = '工事施工予算データ';

-- 工事施工予算データ明細

CREATE TABLE ConstructionBudgetDetail

(

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        GroupCount decimal(2,0) unsigned NOT NULL COMMENT 'グループ番号',

        LineCount decimal(3) unsigned NOT NULL COMMENT '行番号',

        ComponentCode decimal(5,0) unsigned NOT NULL COMMENT '構成キー',

        ItemCode decimal(5,0) unsigned NOT NULL COMMENT '工種キー',

        FirstString varchar(120) COMMENT '項目名称',

        SecondString varchar(120) COMMENT '工事内容',

        CompanyType decimal(1) NOT NULL COMMENT '協力会社コードタイプ',

        CompanyCode decimal(8,0) NOT NULL COMMENT '協力会社コード',

        CompanyName varchar(120) COMMENT '協力会社名称',

        EstimatePrice decimal(12,0) NOT NULL COMMENT '積算時見積金額',

        NegotiationPrice decimal(12,0) NOT NULL COMMENT '積算時交渉金額',

        OrderDesiredAmount decimal(12,0) NOT NULL COMMENT '発注希望金額',

        ExecutionAmount decimal(12,0) NOT NULL COMMENT '実行金額',

        AmountConfigRate decimal(5,2) NOT NULL COMMENT '金額構成率',

        NegotiateFlg decimal(1) NOT NULL COMMENT '協力業者交渉フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode, GroupCount, LineCount)

) COMMENT = '工事施工予算データ明細';

-- 工種マスタ

CREATE TABLE ConstructionItemMaster

(

        ItemCode decimal(5,0) unsigned NOT NULL COMMENT '工種キー',

        DisplayOrder decimal(5) unsigned NOT NULL COMMENT '表示順',

        ItemName varchar(120) DEFAULT '' NOT NULL COMMENT '工種名称',

        SubItemsFlg decimal(1) COMMENT '下位項目フラグ',

        MyExpensesFlg decimal(1) NOT NULL COMMENT '自社経費フラグ',

        DeleteFlg decimal(1) NOT NULL COMMENT '削除フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ItemCode)

) COMMENT = '工種マスタ';

-- 工事詳細台帳データ

CREATE TABLE ConstructionLedger

(

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        ConstructionPeriod decimal(4) unsigned NOT NULL COMMENT '事業期',

        CreatorCode decimal(8,0) COMMENT '作成者コード',

        CreatorName varchar(60) COMMENT '作成者名',

        CreateDate date NOT NULL COMMENT '作成日',

        ConstructionTimes decimal(5,2) NOT NULL COMMENT '工期（単位・月）',

        ConstructionStart date NOT NULL COMMENT '契約工期開始',

        ConstructionEnd date NOT NULL COMMENT '契約工期完了',

        OrdersDecisionPrice decimal(14,0) NOT NULL COMMENT '税抜受注決定金額',

        CompanyExpenses decimal(10,0) NOT NULL COMMENT '会社経費合計',

        DepartmentExpenses decimal(10,0) NOT NULL COMMENT '部署経費合計',

        SalesExpenses decimal(10,0) NOT NULL COMMENT '営業経費合計',

        TotalPayment decimal(10,0) NOT NULL COMMENT '総支払額',

        GrossProfit decimal(10,0) NOT NULL COMMENT '粗利',

        Allowance decimal(10,0) NOT NULL COMMENT '給与',

        NetProfit decimal(10,0) NOT NULL COMMENT '純利益',

        ComplateFlg decimal(1) NOT NULL COMMENT '入力完了フラグ',

        IntegrationFlg decimal(1) NOT NULL COMMENT '受注金額積算フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode)

) COMMENT = '工事詳細台帳データ';

-- 工事詳細台帳明細データ

CREATE TABLE ConstructionLedgerDetail

(

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        GroupCount decimal(2,0) unsigned NOT NULL COMMENT 'グループ番号',

        LineCount decimal(3) unsigned NOT NULL COMMENT '行番号',

        ComponentCode decimal(5,0) unsigned NOT NULL COMMENT '構成キー',

        ItemCode decimal(5,0) unsigned NOT NULL COMMENT '工種キー',

        FirstString varchar(120) COMMENT '項目名称',

        SecondString varchar(120) COMMENT '工事内容',

        CompanyType decimal(1) NOT NULL COMMENT '協力会社コードタイプ',

        CompanyCode decimal(8,0) NOT NULL COMMENT '協力会社コード',

        CompanyName varchar(120) COMMENT '協力会社名称',

        EstimatePrice decimal(12,0) NOT NULL COMMENT '予算（見積）金額',

        ExecutionAmount decimal(12,0) NOT NULL COMMENT '実行金額',

        AmountConfigRate decimal(5,2) NOT NULL COMMENT '金額構成率',

        PaymentBurden decimal(12,0) NOT NULL COMMENT '支払補填額',

        FixDataFlg decimal(1) NOT NULL COMMENT '固定データフラグ',

        IndependentFlg decimal(1) NOT NULL COMMENT '独立データフラグ',

        FluctuationFlg decimal(1) NOT NULL COMMENT '増減データフラグ',

        SalaryFlg decimal(1) NOT NULL COMMENT '給与振分区分',

        SalaryDays decimal(4) NOT NULL COMMENT '給与振分日数',

        OperatingFlg decimal(1) NOT NULL COMMENT '担当中フラグ',

        SourceCode decimal(10) unsigned NOT NULL COMMENT '元工事番号',

        JoinTitleFlg decimal(1) NOT NULL COMMENT '工事名称タイトル',

        SalaryOnRegist decimal(12,0) NOT NULL COMMENT '登録時月額給与',

        PurchaseOrderFlg decimal(2) NOT NULL COMMENT '注文書発行フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode, GroupCount, LineCount)

) COMMENT = '工事詳細台帳明細データ';

-- 工事詳細台帳実行データ

CREATE TABLE ConstructionLedgerExcute

(

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        GroupCount decimal(2,0) unsigned NOT NULL COMMENT 'グループ番号',

        LineCount decimal(3) unsigned NOT NULL COMMENT '行番号',

        ColumnCount decimal(3) NOT NULL COMMENT '列番号',

        PaymentAmount decimal(12,0) NOT NULL COMMENT '支払金額',

        TargetMonth date NOT NULL COMMENT '対象年月',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode, GroupCount, LineCount, ColumnCount)

) COMMENT = '工事詳細台帳実行データ';

-- 工事リンク情報

CREATE TABLE ConstructionLink

(

        ConstructionCode decimal(10,0) unsigned NOT NULL COMMENT '工事コード',

        FluctuationCode decimal(10,0) NOT NULL COMMENT '子工事番号',

        LinkType decimal(1) NOT NULL COMMENT 'リンク種別',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode, FluctuationCode)

) COMMENT = '工事リンク情報';

-- 案件進捗日付データ

CREATE TABLE ConstructionProgressDate

(

        ConstructionCode decimal(10,0) NOT NULL COMMENT '工事番号',

        ConstructionStatusFlg decimal(2,0) NOT NULL COMMENT '設定工事状態',

        PreviousStatusFlg decimal(2,0) COMMENT '変更前状態',

        ChangeDate datetime NOT NULL COMMENT '設定日付',

        ChangePersonCode decimal(8,0) NOT NULL COMMENT '設定担当者',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ConstructionCode, ConstructionStatusFlg)

) COMMENT = '案件進捗日付データ';

-- 作業マスタ

CREATE TABLE ConstructionSpecMaster

(

        ItemCode decimal(5,0) unsigned NOT NULL COMMENT '工種キー',

        SpecCode decimal(5,0) unsigned NOT NULL COMMENT '作業キー',

        DisplayOrder decimal(5) unsigned NOT NULL COMMENT '表示順',

        ItemName varchar(120) DEFAULT '' COMMENT '作業名称',

        SpecName varchar(120) COMMENT '作業名称',

        UnitName varchar(30) COMMENT '単位名称',

        UnitPrice decimal(9,2) NOT NULL COMMENT '単価',

        DeleteFlg decimal(1) NOT NULL COMMENT '削除フラグ',

        EntryDate datetime NOT NULL COMMENT '登録日付',

        UpdateDate datetime NOT NULL COMMENT '更新日付',

        PRIMARY KEY (ItemCode, SpecCode)

) COMMENT = '作業マスタ';

-- 作業単価マスタ

CREATE TABLE ConstructionSpecUnitPrice

(

        ComponentCode decimal(5,0) unsigned NOT NULL COMMENT '構成キー',

        ItemCode decimal(5,0) unsigned NOT NULL COMMENT '工種キー',

        SpecCode decimal(5,0) unsigned NOT NULL COMMENT '作業キー',

        ConstructionTypeCode mediumint unsigned NOT NULL COMMENT '工事種別コード',

        UnitPrice decimal(9,2) NOT NULL COMMENT '単価',