CREATE TABLE manufactures (
 id                               int(11)        AUTO_INCREMENT PRIMARY KEY,
 rev_number                       int(11)        COMMENT "リビジョン番号",
 input_date                       date           COMMENT "入力日",
 target_date                      date           COMMENT "処理日",
 manufacture_trigger_code         smallint(6)     COMMENT "加工要因",
 manufacture_type_code            smallint(6)    COMMENT "加工区分",
 user_id                          int(11)        COMMENT "入力者",
 section_id                       int(11)        COMMENT "入力者部署",
 supplier_id                      int(11)        COMMENT "加工先",
 customer_id                      int(11)        COMMENT "得意先",
 delivery_id                      int(11)        COMMENT "着荷場所",
 trucking_id                      int(11)        COMMENT "運送業者",
 receiver_name                    varchar(20)    COMMENT "受取人",
 arrival_date                     date           COMMENT "着荷日",
 request_time                     varchar(20)    COMMENT "時間指定",
 warehouse_id                     int(11)        COMMENT "納品先",
 own_warehouse_id                 int(11)        COMMENT "倉庫元",
 total_quantity                   int(11)        COMMENT "総数",
 stock_total_quantity             int(11)        COMMENT "",
 manufacture_product_total_quantity int(11)      COMMENT "",
 rels_total_quantity              int(11)        COMMENT "",
 rels_comp_total_quantity         int(11)        COMMENT "",
 state_code                       smallint(6)    COMMENT "状態",
 manufacture_note                 varchar(200)   COMMENT "伝票備考",
 note                             varchar(200)   COMMENT "備考",
 payto_supplier_id                int(11)        COMMENT "支払先",
 currency_type_code               smallint(6)    COMMENT "通貨",
 exchange_rate                    decimal(16,2)  COMMENT "為替レート",
 total_price                      int(11)        COMMENT "原価合計",
 total_labor_price                int(11)        COMMENT "労務費合計",
 total_duty                       int(11)        COMMENT "消費税",
 duty_type_code                   smallint(6)    COMMENT "課税",
 duty_rate                        int(11)        COMMENT "税率",
 price_duty_type_code             smallint(6)    COMMENT "価格課税方式",
 duty_calc_type_code              smallint(6)    COMMENT "税計算方式",
 frac_digit_code                  smallint(6)    COMMENT "端数処理桁",
 fraction_method_code             smallint(6)    COMMENT "端数処理方式",
 unit_price_fraction_method_code  smallint(6)    COMMENT "単価端数処理",
 price_fraction_method_code       smallint(6)    COMMENT "金額端数処理",
 sign_type_code                   smallint(6)    COMMENT "赤黒",
 invalid_flag_code                smallint(6)    COMMENT "削除フラグ",
 print_sheet_date                 datetime       COMMENT "伝票印刷日時",
 deleted_at                       datetime       COMMENT "削除日時",
 created_at                       datetime       COMMENT "作成日時",
 updated_at                       datetime       COMMENT "更新日時"
) type=InnoDB;
CREATE TABLE manufacture_details (
 id                   int(11)                    AUTO_INCREMENT PRIMARY KEY,
 manufacture_id       int(11)                    COMMENT "伝票番号",
 seq_number           int(11)                    COMMENT "枝番",
 product_category_id  int(11)                    COMMENT "製品カテゴリ",
 product_set_id       int(11)                    COMMENT "製品群",
 product_id           int(11)                    COMMENT "製品型番",
 lot_number           varchar(40)                COMMENT "ロット番号",
 serial_number        varchar(40)                COMMENT "シリアル",
 ubd                  date                       COMMENT "有効期限",
 validity_period      int(11)                    COMMENT "有効期間",
 unit_code            smallint(6)                COMMENT "単位",
 quantity             int(11)                    COMMENT "数量",
 price                decimal(16,3)              COMMENT "原価",
 stock_type_code      smallint(6)                COMMENT "在庫区分",
 detail_note          varchar(200)               COMMENT "備考",
 stock_flag_code      smallint(6)                COMMENT "在庫管理区分",
 created_at           datetime                   COMMENT "作成日時",
 updated_at           datetime                   COMMENT "更新日時"
) type=InnoDB;

ALTER TABLE master_app_production.products add manufacture_product_type_code smallint(6) after stock_flag_code;
UPDATE master_app_production.products set manufacture_product_type_code=0;

CREATE TABLE master_app_production.manufacture_products (
  id                   int(11)                AUTO_INCREMENT PRIMARY KEY,
  parent_product_id    int(11)                COMMENT "加工品製品型番",
  product_set_id       int(11)                COMMENT "製品群",
  product_id           int(11)                COMMENT "製品型番",
  quantity             int(11)                COMMENT "数量",
  created_at           datetime               COMMENT "作成日時",
  updated_at           datetime               COMMENT "更新日時"
) type=InnoDB;

CREATE TABLE manufacture_product_details (
  id                     int(11)                    AUTO_INCREMENT PRIMARY KEY,
  manufacture_id         int(11)                    COMMENT "加工伝票ID",
  manufacture_detail_id  int(11)                    COMMENT "加工明細ID",
  parent_product_category_id int(11)                COMMENT "加工製品カテゴリ",
  parent_product_set_id  int(11)                    COMMENT "加工製品群",
  parent_product_id      int(11)                    COMMENT "加工製品型番",
  parent_lot_number      varchar(40)                COMMENT "加工ロット番号",
  parent_serial_number   varchar(40)                COMMENT "加工シリアル",
  parent_ubd             date                       COMMENT "加工有効期限",
  seq_number             int(11)                    COMMENT "枝番",
  product_category_id    int(11)                    COMMENT "製品カテゴリ",
  product_set_id         int(11)                    COMMENT "製品群",
  product_id             int(11)                    COMMENT "製品型番",
  lot_number             varchar(40)                COMMENT "ロット番号",
  serial_number          varchar(40)                COMMENT "シリアル",
  ubd                    date                       COMMENT "有効期限",
  quantity               int(11)                    COMMENT "数量",
  price                  decimal(16,3)              COMMENT "価格",
  created_at             datetime                   COMMENT "作成日時",
  updated_at             datetime                   COMMENT "更新日時"
) type=InnoDB;

INSERT INTO menus set menu_category_id=2, name="manufactures",        disp_name="加工処理フロー", disp_sort=3, invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
INSERT INTO menus set menu_category_id=3, name="manufacture_details", disp_name="加工明細",    disp_sort=3, invalid_flag_code=1, created_at=NOW(), updated_at=NOW();

UPDATE menus set disp_sort=4  WHERE name="purchases";
UPDATE menus set disp_sort=5  WHERE name="sales";
UPDATE menus set disp_sort=6  WHERE name="shippings";
UPDATE menus set disp_sort=7  WHERE name="restorations";
UPDATE menus set disp_sort=8  WHERE name="disposals";
UPDATE menus set disp_sort=9  WHERE name="samples";
UPDATE menus set disp_sort=10 WHERE name="adjustments";

#仕入のtrigger_code
INSERT INTO master_app_production.code_masters set code_type_id=52, code_number=5, disp_name="加工処理フロー", disp_sort=4, invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
UPDATE master_app_production.code_masters set disp_sort=5 where code_type_id=52 and code_number=1;
#仕入に加工処理IDを追加
ALTER TABLE purchases ADD manufacture_id int(11) after order_id;

#インデックスの追加
CREATE INDEX manufacture_id ON purchases (manufacture_id);
CREATE INDEX targer_date ON manufactures (target_date);
CREATE INDEX supplier_id ON manufactures (supplier_id);
CREATE INDEX warehouse_id ON manufactures (warehouse_id);
CREATE INDEX own_warehouse_id ON manufactures (own_warehouse_id);
CREATE INDEX invalid_flag_code ON manufactures (invalid_flag_code);

CREATE INDEX manufacture_id ON manufacture_details (manufacture_id);
CREATE INDEX product_set_id ON manufacture_details (product_set_id);
CREATE INDEX product_id ON manufacture_details (product_id);

CREATE INDEX manufacture_id ON manufacture_product_details (manufacture_id);
CREATE INDEX manufacture_detail_id ON manufacture_product_details (manufacture_detail_id);
CREATE INDEX parent_product_id ON manufacture_product_details (parent_product_id);

#加工処理フローの関連処理
INSERT INTO master_app_production.table_rels set table_name="purcahses", order_type_code=19, disp_sort=1, trans_type_code=8, sign_type_code=0, j_table_name="仕入処理(加工出庫)", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
INSERT INTO master_app_production.table_rels set table_name="purcahses", order_type_code=19, disp_sort=2, trans_type_code=9, sign_type_code=1, j_table_name="仕入処理(加工仕入)", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
INSERT INTO master_app_production.table_rels set table_name="purcahses", order_type_code=20, disp_sort=1, trans_type_code=8, sign_type_code=0, j_table_name="仕入処理(加工出庫)", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
INSERT INTO master_app_production.table_rels set table_name="purcahses", order_type_code=20, disp_sort=2, trans_type_code=9, sign_type_code=1, j_table_name="仕入処理(加工仕入)", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
UPDATE master_app_production.table_rels set j_table_name="仕入処理(加工仕入)" where order_type_code=18 and table_name="purchases";

INSERT INTO master_app_production.code_types set id=71, disp_name="加工製品区分", j_name="加工製品区分", e_name="manufacture product type", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
INSERT INTO master_app_production.code_masters set code_type_id=71, code_number=0, disp_sort=1, disp_name="非加工製品", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
INSERT INTO master_app_production.code_masters set code_type_id=71, code_number=1, disp_sort=2, disp_name="加工製品", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();

INSERT INTO master_app_production.code_types set id=72, disp_name="加工区分", j_name="加工区分", e_name="manufacture type", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
INSERT INTO master_app_production.code_masters set code_type_id=72, code_number=19, disp_sort=1, disp_name="組立", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
INSERT INTO master_app_production.code_masters set code_type_id=72, code_number=20, disp_sort=2, disp_name="分解", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();

INSERT INTO master_app_production.code_types set id=73, disp_name="加工要因", j_name="加工要因", e_name="manufacture trigger code", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
INSERT INTO master_app_production.code_masters set code_type_id=73, code_number=1, disp_sort=1, disp_name="自社加工", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();

INSERT INTO menus set menu_category_id=6, name="manufacture_products", disp_name="加工製品構成",    disp_sort=1004, invalid_flag_code=0, created_at=NOW(), updated_at=NOW();

INSERT into master_app_production.master_helps set table_name='products', col_name = 'manufacture_product_type_code', title='加工製品区分', help='他の製品から加工して生成する製品の場合は加工製品、そうでない場合は非加工製品を選択します。', created_at=NOW(), updated_at=NOW();
