CREATE TABLE master_app_production.case_numbers (
  id                               int(11)        AUTO_INCREMENT PRIMARY KEY,
  warehouse_id                     int(11)        COMMENT "施設",
  customer_id                      int(11)        COMMENT "得意先",
  global_area_id                   int(11)        COMMENT "営業所",
  sales_area_id                    int(11)        COMMENT "営業エリア",
  user_position_id                 int(11)        COMMENT "担当者",
  user_id                          int(11)        COMMENT "担当ユーザー",
  target_year                      int(11)        COMMENT "対象年",
  target_case_id                   int(11)        COMMENT "症例区分",
  case_quantity                    int(11)        COMMENT "症例数",
  note                             varchar(400)   COMMENT "備考",
  invalid_flag_code                SMALLINT(6)    COMMENT "有効/無効",
  deleted_at                       datetime       COMMENT "削除日時",
  created_at                       datetime       COMMENT "作成日時",
  updated_at                       datetime       COMMENT "更新日時"
) ENGINE=InnoDB;

INSERT INTO menus set menu_category_id=6, name="case_numbers", disp_name="納品先別症例数", disp_sort=2003, invalid_flag_code=0, created_at=NOW(), updated_at=NOW();

CREATE TABLE master_app_production.target_cases (
  id                  int(11)      AUTO_INCREMENT PRIMARY KEY,
  disp_name           varchar(40)  COMMENT "表示名",
  disp_sort           int(11)      COMMENT "表示順",
  j_name              varchar(40)  COMMENT "日本語名",
  e_name              varchar(40)  COMMENT "英語名",
  kana_name           varchar(80)  COMMENT "カナ名",
  invalid_flag_code   smallint(6)  COMMENT "有効フラグ",
  deleted_at          datetime     COMMENT "削除日時",
  created_at          datetime     COMMENT "作成日時",
  updated_at          datetime     COMMENT "更新日時"
) ENGINE=InnoDB;

INSERT INTO menus set menu_category_id=6, name="target_cases", disp_name="症例区分", disp_sort=5011, invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
INSERT INTO master_app_production.target_cases set disp_name="PCI", disp_sort=1, j_name="PCI", e_name="PCI", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();


load data infile '/tmp/case_quantity_2012.csv' into table
master_app_production.case_numbers fields terminated by ','
(warehouse_id,case_quantity,note,target_case_id,target_year);

update master_app_production.case_numbers AS c
left join (select
s.id,s.user_position_id,a.global_area_id,s.sales_area_id,s.warehouse_id,s.customer_id,
s.target_date
from (select * from sales
where target_date < '2013-01-01'
and sales_user_id != 1
and invalid_flag_code = 0
order by target_date DESC)AS s
left join master_app_production.sales_areas as a on s.sales_area_id =
a.id group by s.warehouse_id) AS t
ON t.warehouse_id = c.warehouse_id
SET c.customer_id = t.customer_id,
c.global_area_id = t.global_area_id,
c.sales_area_id = t.sales_area_id,
c.user_position_id = t.user_position_id,
c.created_at=NOW(),
c.updated_at = NOW() ;

update master_app_production.case_numbers set case_quantity = NULL where case_quantity = 0;

load data infile '/tmp/new_warehouse.csv' into table
master_app_production.warehouses fields terminated by ','
(sales_area_id, user_position_id, disp_name, j_name, e_name, kana_name, zip, address_1, address_2, tel, fax, operation_number);

insert into master_app_production.case_numbers 
(warehouse_id,
customer_id,
global_area_id,
sales_area_id,
user_position_id,
user_id,
target_year,
target_case_id,
case_quantity,
created_at,
updated_at)
SELECT 
  w.id,
  NULL,
  sa.global_area_id,
  w.sales_area_id,
  w.user_position_id,
  up.user_id,
  "2012",
  1,
  w.operation_number,
  NOW(),
  NOW()
 FROM master_app_production.warehouses AS w 
 LEFT JOIN master_app_production.sales_areas AS sa ON w.sales_area_id = sa.id
 LEFT JOIN master_app_production.user_positions AS up ON w.user_position_id = up.id
 WHERE w.invalid_flag_code is null;
UPDATE master_app_production.case_numbers set user_position_id=NULL where user_position_id=0;
UPDATE master_app_production.case_numbers set sales_area_id=NULL where sales_area_id=0;
UPDATE master_app_production.case_numbers set user_id=NULL where user_id=0;

update master_app_production.warehouses set invalid_flag_code = 0, created_at=NOW(), updated_at=NOW(), disp_sort=id, own_flag_code=0, title_code=2 where invalid_flag_code is null;

