alter table accept_order_details add stock_flag_code smallint(6) after detail_note;
alter table adjustment_details add stock_flag_code smallint(6) after detail_note;
alter table arriving_details add stock_flag_code smallint(6) after detail_note;
alter table disposal_details add stock_flag_code smallint(6) after detail_note;
alter table order_details add stock_flag_code smallint(6) after detail_note;
alter table purchase_details add stock_flag_code smallint(6) after detail_note;
alter table quotation_details add stock_flag_code smallint(6) after detail_note;
alter table restoration_details add stock_flag_code smallint(6) after detail_note;
alter table sale_details add stock_flag_code smallint(6) after detail_note;
alter table sample_details add stock_flag_code smallint(6) after detail_note;
alter table shipping_details add stock_flag_code smallint(6) after detail_note;

#修正の忘れ。。
alter table arriving_details change lot_number lot_number varchar(40) COMMENT "ロット番号";

#製品マスターに追加
alter table master_app_production.products add stock_flag_code smallint(6) after shipment_validity_period;

#在庫管理　あり／なし　ヘルプの追加
INSERT into master_app_production.master_helps set table_name='products', col_name = 'stock_flag_code', title='在庫管理', help='在庫管理する製品かどうかを設定します。「有」の場合は取引の度に在庫表が更新され、「無」の場合は取引で該当の製品が登録されても在庫表は更新されません。', created_at=NOW(), updated_at=NOW();

# update
update accept_order_details set stock_flag_code=1;
update adjustment_details set stock_flag_code=1;
update arriving_details set stock_flag_code=1;
update disposal_details set stock_flag_code=1;
update order_details set stock_flag_code=1;
update purchase_details set stock_flag_code=1;
update quotation_details set stock_flag_code=1;
update restoration_details set stock_flag_code=1;
update sale_details set stock_flag_code=1;
update sample_details set stock_flag_code=1;
update shipping_details set stock_flag_code=1;

update master_app_production.products set stock_flag_code=1;


#受注／発注に非在庫管理製品を除いたtotal_quantityを追加する。
ALTER TABLE accept_orders add rels_total_quantity INT(11) after total_quantity;
ALTER TABLE accept_orders add stock_total_quantity INT(11) after total_quantity;
ALTER TABLE orders add rels_total_quantity INT(11) after total_quantity;
ALTER TABLE orders add stock_total_quantity INT(11) after total_quantity;

UPDATE accept_orders set stock_total_quantity=total_quantity;
UPDATE orders set stock_total_quantity=total_quantity;

UPDATE accept_orders as a
  LEFT JOIN (SELECT 
              order_type_code, 
              SUM(IF(table_name='sales',1,0)) AS sales_cnt, 
              SUM(IF(table_name='purchases',1,0)) AS purchases_cnt,
              SUM(IF(table_name!='sales' AND table_name!='purchases' AND table_name!='orders',1,0)) AS trans_cnt
            FROM master_app_production.table_rels 
            WHERE invalid_flag_code=0
            GROUP BY order_type_code
          ) AS t ON a.accept_order_type_code=t.order_type_code 
          SET rels_total_quantity=((t.sales_cnt + IF(a.supplier_id=1,0,t.purchases_cnt) + IF(a.accept_order_trigger_code=1,t.trans_cnt,0)) * a.total_quantity);

UPDATE orders as a
  LEFT JOIN (SELECT 
              order_type_code, 
              SUM(IF(table_name='purchases',1,0)) AS purchases_cnt
            FROM master_app_production.table_rels 
            WHERE invalid_flag_code=0 
            GROUP BY order_type_code
          ) AS t ON a.order_type_code=t.order_type_code 
          SET rels_total_quantity=(t.purchases_cnt * a.total_quantity);

ALTER TABLE accept_orders add rels_comp_total_quantity INT(11) after rels_total_quantity;
ALTER TABLE orders add rels_comp_total_quantity INT(11) after rels_total_quantity;
UPDATE accept_orders AS a
   LEFT JOIN (
     SELECT accept_order_id, COUNT(*) AS cnt, SUM(total_quantity) AS total_quantity FROM (
       SELECT 'shippings' AS table_name, id, accept_order_id, total_quantity FROM shippings 
         WHERE shipping_state_code=1 AND invalid_flag_code=0 
       UNION SELECT 'restorations' AS table_name, id, accept_order_id, total_quantity FROM restorations
         WHERE state_code=1 AND invalid_flag_code=0 
       UNION SELECT 'purchases' AS table_name, id, accept_order_id, total_quantity FROM purchases
         WHERE state_code=1 AND invalid_flag_code=0 AND purchase_trigger_code != 4
       UNION SELECT 'sales' AS table_name, id, accept_order_id, total_quantity FROM sales
         WHERE state_code=1 AND invalid_flag_code=0
       UNION SELECT 'samples' AS table_name, id, accept_order_id, total_quantity FROM samples
         WHERE state_code=1 AND invalid_flag_code=0
     ) AS tmp GROUP BY accept_order_id
   ) AS b ON a.id=b.accept_order_id
SET rels_comp_total_quantity=b.total_quantity;

UPDATE orders AS a
   LEFT JOIN (SELECT order_id, SUM(IF(tr.sign_type_code=0,-1,1) * total_quantity) AS total_quantity, count(*) AS cnt FROM purchases AS p
              LEFT JOIN master_app_production.table_rels AS tr ON p.purchase_type_code=tr.trans_type_code AND tr.table_name='purchases'
              WHERE p.state_code=1 AND p.invalid_flag_code=0 
              GROUP BY order_id
            ) AS d ON a.id=d.order_id 
SET rels_comp_total_quantity=d.total_quantity;

#relsに赤伝／黒伝からもとの受注と発注をたどって、追加する関数。
INSERT INTO rels
(
parent_table_name,
parent_record_id,
table_name,
record_id,
lock_status_code,
created_at
)
select 
r2.parent_table_name,
r2.parent_record_id,
r1.table_name,
r1.record_id,
r1.lock_status_code,
r1.created_at 
  from rels as r1 
  left join rels as r2 on r1.parent_table_name=r2.table_name and r1.parent_record_id=r2.record_id  
  where r1.parent_table_name=r1.table_name and r2.id is not null;

#sales/purchasesにstock_total_quantityを追加する
ALTER TABLE sales add stock_total_quantity INT(11) after total_quantity;
ALTER TABLE purchases add stock_total_quantity INT(11) after total_quantity;
UPDATE sales set stock_total_quantity=total_quantity;
UPDATE purchases set stock_total_quantity=total_quantity;

