#金額系
alter table accept_orders add charge_customer_id INT(11) COMMENT "請求先" after customer_id;
alter table accept_orders add price_fraction_method_code SMALLINT(6) COMMENT "金額端数処理方式" after note;
alter table accept_orders add unit_price_fraction_method_code SMALLINT(6) COMMENT "単価端数処理方式" after note;
alter table accept_orders add fraction_method_code smallint(6) comment '端数処理方式' after note;
alter table accept_orders add frac_digit_code smallint(6) comment '端数処理桁' after note;
alter table accept_orders add duty_calc_type_code smallint(6) comment '税計算方式' after note;
alter table accept_orders add price_duty_type_code smallint(6) comment '価格課税方式' after note;
alter table accept_orders add duty_rate int(11) comment '税率' after note;
alter table accept_orders add duty_type_code smallint(6) comment '課税' after note;
alter table accept_orders add total_duty int(11) comment '消費税' after note;
alter table accept_orders add total_price int(11) comment '合計金額' after note;
alter table accept_orders add exchange_rate decimal(16,2) comment '為替レート' after note;
alter table accept_orders add currency_type_code smallint(6) comment '通貨' after note;
alter table accept_orders add payto_supplier_id int(11) comment '支払先' after note;

alter table accept_order_details add price decimal(16,3) comment '価格' after quantity;

#見積
alter table accept_orders add quotation_id int(11) comment '見積ID' after id;

CREATE TABLE quotations (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  quotation_id INT(11) COMMENT "親見積ID",
  rev_number INT(11) COMMENT "リビジョン番号",
  input_date DATE COMMENT "入力日",
  target_date DATE COMMENT "処理日",
  expire_date DATE COMMENT "見積有効期限",
  effective_period INT(11) COMMENT "見積有効日数",
  user_id INT(11) COMMENT "入力者",
  section_id INT(11) COMMENT "入力者部署",
  supplier_id INT(11) COMMENT "仕入先",
  customer_id INT(11) COMMENT "得意先",
  charge_customer_id INT(11) COMMENT "請求先",
  warehouse_id INT(11) COMMENT "納品先",
  user_position_id INT(11) COMMENT "営業担当者",
  sales_user_id INT(11) COMMENT "営業担当者",
  sales_area_id INT(11) COMMENT "営業エリア",
  sales_section_id INT(11) COMMENT "営業部署",  
  shipment_customer_id int(11) COMMENT "配送先",
  delivery_id int(11) COMMENT "営業店止",
  trucking_id int(11) COMMENT "配送業者",
  receiver_name varchar(20) COMMENT "受取人",
  request_time varchar(20) COMMENT "時間指定",
  state_code INT(11) COMMENT "状態",
  note VARCHAR(200) COMMENT "備考",
  quotation_note VARCHAR(200) COMMENT "備考",
  currency_type_code SMALLINT(6) COMMENT "通貨",
  exchange_rate DECIMAL(16,2) COMMENT "為替レート",
  quotation_sheet_type_code smallint(6) COMMENT "見積書形式",
  total_quantity INT(11) COMMENT "総数",
  total_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 "金額端数処理方式",
  invalid_flag_code SMALLINT(6) COMMENT "削除フラグ",
  print_sheet_date DATETIME COMMENT "伝票印刷日時",
  deleted_at DATETIME COMMENT "削除日時",
  created_at DATETIME COMMENT "作成日時",
  updated_at DATETIME COMMENT "更新日時"
);
ALTER TABLE quotations COMMENT = '見積';
CREATE INDEX target_date ON quotations (target_date);

CREATE TABLE quotation_details (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  quotation_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(20) COMMENT "ロット",
  serial_number VARCHAR(40) COMMENT "シリアル",
  ubd DATE COMMENT "UBD",
  validity_period INT(11) COMMENT "有効期限",
  unit_code SMALLINT(6) COMMENT "単位",
  quantity INT(11) COMMENT "数量",
  fixed_price DECIMAL(16,3) COMMENT "定価",
  price DECIMAL(16,3) COMMENT "価格",
  detail_note VARCHAR(200) COMMENT "備考",
  created_at DATETIME COMMENT "作成日時",
  updated_at DATETIME COMMENT "更新日時"
);
ALTER TABLE quotation_details COMMENT = '見積明細';
CREATE INDEX quotation_id ON quotation_details (quotation_id);

#master_appのコードマスターに見積もり伝票を追加
insert into master_app_production.code_types set id=57, disp_name="見積書タイプ", j_name="見積書タイプ", e_name="Quotation Sheet Type", note="伝票フォーマット形式", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set id=264, code_type_id=57, code_number=1, disp_name="標準", disp_sort=1, j_name="標準", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set id=265, code_type_id=57, code_number=2, disp_name="様式1", disp_sort=2, j_name="様式1", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set id=266, code_type_id=57, code_number=3, disp_name="様式2", disp_sort=3, j_name="様式2", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
alter table master_app_production.customers add quotation_sheet_type_code smallint(6) after price_fraction_method_code ;
update master_app_production.customers set quotation_sheet_type_code=1;

insert into menus set id=92, menu_category_id=2, name="quotations", disp_name="見積処理", disp_sort=10, invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into menus set id=93, menu_category_id=3, name="quotation_details", disp_name="見積明細", disp_sort=10, invalid_flag_code=0, created_at=NOW(), updated_at=NOW();

#売掛／買掛繰越処理にtotal_dutyを追加する
alter table receivable_carry_remains add charge_customer_id int(11) COMMENT "請求先" after customer_id;
alter table receivable_carry_remains add price_fraction_method_code smallint(6) COMMENT "金額端数処理" after total_carry;
alter table receivable_carry_remains add unit_price_fraction_method_code smallint(6) COMMENT "単価端数処理" after total_carry;
alter table receivable_carry_remains add fraction_method_code smallint(6) COMMENT "端数処理方式" after total_carry;
alter table receivable_carry_remains add frac_digit_code smallint(6) COMMENT "端数処理桁" after total_carry;
alter table receivable_carry_remains add duty_calc_type_code smallint(6) COMMENT "税計算方式" after total_carry;
alter table receivable_carry_remains add price_duty_type_code smallint(6) COMMENT "価格課税方式" after total_carry;
alter table receivable_carry_remains add duty_rate int(11) COMMENT "税率" after total_carry;
alter table receivable_carry_remains add duty_type_code smallint(6) COMMENT "課税" after total_carry;
alter table receivable_carry_remains add total_carry_duty int(11) COMMENT "消費税" after total_carry;
alter table receivable_carry_remains change credit_timing_code credit_timing_code smallint(6) COMMENT "調整時期";
alter table receivable_carry_remains change total_carry total_carry int(11) COMMENT "合計調整額";
alter table receivable_carry_remains change customer_group_id charge_customer_group_id int(11) COMMENT "請求先グループ";
update receivable_carry_remains set duty_type_code=1,duty_rate=5,price_duty_type_code=0,duty_calc_type_code=1,frac_digit_code=1,fraction_method_code=1,unit_price_fraction_method_code=3,price_fraction_method_code=3,total_carry_duty=0;
update receivable_carry_remains set charge_customer_id=customer_id;

alter table payable_carry_remains add payto_supplier_id int(11) COMMENT "支払先" after supplier_id;
alter table payable_carry_remains add price_fraction_method_code smallint(6) COMMENT "金額端数処理" after total_carry;
alter table payable_carry_remains add unit_price_fraction_method_code smallint(6) COMMENT "単価端数処理" after total_carry;
alter table payable_carry_remains add fraction_method_code smallint(6) COMMENT "端数処理方式" after total_carry;
alter table payable_carry_remains add frac_digit_code smallint(6) COMMENT "端数処理桁" after total_carry;
alter table payable_carry_remains add duty_calc_type_code smallint(6) COMMENT "税計算方式" after total_carry;
alter table payable_carry_remains add price_duty_type_code smallint(6) COMMENT "価格課税方式" after total_carry;
alter table payable_carry_remains add duty_rate int(11) COMMENT "税率" after total_carry;
alter table payable_carry_remains add duty_type_code smallint(6) COMMENT "課税" after total_carry;
alter table payable_carry_remains add total_carry_duty int(11) COMMENT "消費税" after total_carry;
alter table payable_carry_remains change credit_timing_code credit_timing_code smallint(6) COMMENT "調整時期";
alter table payable_carry_remains change total_carry total_carry int(11) COMMENT "合計調整額";
alter table payable_carry_remains change supplier_group_id payto_supplier_group_id int(11) COMMENT "支払先グループ";

alter table receivables change total_carry total_carry int(11) COMMENT "調整合計";
alter table receivables add total_carry_duty int(11) COMMENT "消費税調整合計" after total_carry;
update receivables set total_carry_duty=0;

alter table payables change total_carry total_carry int(11) COMMENT "調整合計";
alter table payables add total_carry_duty int(11) COMMENT "消費税調整合計" after total_carry;
update payables set total_carry_duty=0;

alter table customer_ledgers change total_carry total_carry int(11) COMMENT "月間調整額";
alter table customer_ledgers add total_carry_duty int(11) COMMENT "月間消費税調整額" after total_carry;
update customer_ledgers set total_carry_duty=0;

alter table supplier_ledgers change total_carry total_carry int(11) COMMENT "月間調整額";
alter table supplier_ledgers add total_carry_duty int(11) COMMENT "月間消費税調整額" after total_carry;
update supplier_ledgers set total_carry_duty=0;

#メニュー名変更
update menus set disp_name="売掛金調整処理" where id=31;
update menus set disp_name="買掛金調整処理" where id=32;
