
select * from 
	(select 
		b.id, 
		hospital_id, 
		dealer_id, 
		lot_number , 
		count(*), 
		sum(shipping_quantity)  
	from shipping_details as a left join shippings as b on a.shipping_id=b.id where input_date > "2011-02-28" 
	group by hospital_id, dealer_id, lot_number) as c left join 
	(select 
		e.id, 
		warehouse_id, 
		customer_id,
		lot_number,
		count(*),
		sum(quantity)
	from sanix_development.shipping_details as d left join sanix_development.shippings as e on d.shipping_id=e.id where input_date>"2011-02-28"
	group by warehouse_id, customer_id, lot_number) as f on c.hospital_id=f.warehouse_id and c.dealer_id=f.customer_id and c.lot_number=f.lot_number;



#返却分表示
select * from sanix_development.stocks as j inner join 
(select i.st_lot, h.add_quantity from
  (select lot_number as st_lot from sanix_development.stocks where warehouse_id=660 group by warehouse_id, lot_number) as i inner join  
  (select glan_lot, sum(glan_quantity) as add_quantity from 
  
    	(select * from 
            	(select
                   	 	b.id,
                    	hospital_id,
                    	dealer_id,
                    	lot_number as glan_lot,
                    	count(*) as glan_count,
                    	sum(shipping_quantity) as glan_quantity
            	from shipping_details as a left join shippings as b on a.shipping_id=b.id where input_date > "2011-02-28"
            	group by hospital_id, dealer_id, lot_number) as c left join
            	(select
                    	e.id as sanix_id,
                    	warehouse_id,
                    	customer_id,
                    	lot_number,
                    	count(*) as sanix_count,
                    	sum(quantity)
            	from sanix_development.shipping_details as d left join sanix_development.shippings as e on d.shipping_id=e.id where input_date>"2011-02-28"
            	group by warehouse_id, customer_id, lot_number) as f on c.hospital_id=f.warehouse_id and c.dealer_id=f.customer_id and c.glan_lot=f.lot_number where sanix_id is NULL) as g group by g.glan_lot) as h
  on i.st_lot=h.glan_lot ) as k on j.warehouse_id=660 and j.lot_number = k.st_lot


#返却分update分
update sanix_development.stocks as j inner join 
(select i.st_lot, h.add_quantity from
  (select lot_number as st_lot from sanix_development.stocks where warehouse_id=660 group by warehouse_id, lot_number) as i inner join  
  (select glan_lot, sum(glan_quantity) as add_quantity from 
  
      (select * from 
              (select
                      b.id,
                      hospital_id,
                      dealer_id,
                      lot_number as glan_lot,
                      count(*) as glan_count,
                      sum(shipping_quantity) as glan_quantity
              from shipping_details as a left join shippings as b on a.shipping_id=b.id where input_date > "2011-02-28"
              group by hospital_id, dealer_id, lot_number) as c left join
              (select
                      e.id as sanix_id,
                      warehouse_id,
                      customer_id,
                      lot_number,
                      count(*) as sanix_count,
                      sum(quantity)
              from sanix_development.shipping_details as d left join sanix_development.shippings as e on d.shipping_id=e.id where input_date>"2011-02-28"
              group by warehouse_id, customer_id, lot_number) as f on c.hospital_id=f.warehouse_id and c.dealer_id=f.customer_id and c.glan_lot=f.lot_number where sanix_id is NULL) as g group by g.glan_lot) as h
  on i.st_lot=h.glan_lot ) as k on j.warehouse_id=660 and j.lot_number = k.st_lot
set j.existing_quantity=j.existing_quantity + k.add_quantity, j.available_quantity=j.available_quantity + k.add_quantity;


#主に仕入れ分表示
select * from 
    (select glan_lot, sum(glan_quantity) as add_quantity, glan_ubd, glan_product_model, glan_product_set_id from 
      (select * from 
              (select
                      b.id,
                      hospital_id,
                      dealer_id,
                      lot_number as glan_lot,
                      ubd_date as glan_ubd,
                      count(*) as glan_count,
                      product_model_id as glan_product_model,
                      product_set_id as glan_product_set_id,
                      sum(shipping_quantity) as glan_quantity
              from shipping_details as a left join shippings as b on a.shipping_id=b.id where input_date > "2011-02-28"
              group by hospital_id, dealer_id, lot_number) as c left join
              (select
                      e.id as sanix_id,
                      warehouse_id,
                      customer_id,
                      lot_number,
                      count(*) as sanix_count,
                      sum(quantity)
              from sanix_development.shipping_details as d left join sanix_development.shippings as e on d.shipping_id=e.id where input_date>"2011-02-28"
              group by warehouse_id, customer_id, lot_number) as f on c.hospital_id=f.warehouse_id and c.dealer_id=f.customer_id and c.glan_lot=f.lot_number where sanix_id is NULL) as g group by g.glan_lot) as h
    left join (select i.st_lot, h.add_quantity from
      (select lot_number as st_lot from sanix_development.stocks where warehouse_id=660 group by warehouse_id, lot_number) as i inner join  
      (select glan_lot, sum(glan_quantity) as add_quantity from 
      
          (select * from 
                  (select
                          b.id,
                          hospital_id,
                          dealer_id,
                          lot_number as glan_lot,
                          count(*) as glan_count,
                          sum(shipping_quantity) as glan_quantity
                  from shipping_details as a left join shippings as b on a.shipping_id=b.id where input_date > "2011-02-28"
                  group by hospital_id, dealer_id, lot_number) as c left join
                  (select
                          e.id as sanix_id,
                          warehouse_id,
                          customer_id,
                          lot_number,
                          count(*) as sanix_count,
                          sum(quantity)
                  from sanix_development.shipping_details as d left join sanix_development.shippings as e on d.shipping_id=e.id where input_date>"2011-02-28"
                  group by warehouse_id, customer_id, lot_number) as f on c.hospital_id=f.warehouse_id and c.dealer_id=f.customer_id and c.glan_lot=f.lot_number where sanix_id is NULL) as g group by g.glan_lot) as h
      on i.st_lot=h.glan_lot ) as k on h.glan_lot = k.st_lot where st_lot is NULL;
      
  
#主に仕入れ分表示
insert into sanix_development.stocks (warehouse_id, customer_id, supplier_id, product_set_id, product_id, lot_number, serial_number, ubd, validity_period, unit_code,existing_quantity,available_quantity, short_shipped_quantity) 
select 660, NULL, 1, glan_product_set_id, glan_product_model, glan_lot, "", glan_ubd, 24, 1, glan_add_quantity, glan_add_quantity, 0 from 
    (select glan_lot, sum(glan_quantity) as glan_add_quantity, glan_ubd, glan_product_model, glan_product_set_id from 
      (select * from 
              (select
                      b.id,
                      hospital_id,
                      dealer_id,
                      lot_number as glan_lot,
                      ubd_date as glan_ubd,
                      count(*) as glan_count,
                      product_model_id as glan_product_model,
                      product_set_id as glan_product_set_id,
                      sum(shipping_quantity) as glan_quantity
              from shipping_details as a left join shippings as b on a.shipping_id=b.id where input_date > "2011-02-28"
              group by hospital_id, dealer_id, lot_number) as c left join
              (select
                      e.id as sanix_id,
                      warehouse_id,
                      customer_id,
                      lot_number,
                      count(*) as sanix_count,
                      sum(quantity)
              from sanix_development.shipping_details as d left join sanix_development.shippings as e on d.shipping_id=e.id where input_date>"2011-02-28"
              group by warehouse_id, customer_id, lot_number) as f on c.hospital_id=f.warehouse_id and c.dealer_id=f.customer_id and c.glan_lot=f.lot_number where sanix_id is NULL) as g group by g.glan_lot) as h
    left join (select i.st_lot, h.add_quantity from
      (select lot_number as st_lot from sanix_development.stocks where warehouse_id=660 group by warehouse_id, lot_number) as i inner join  
      (select glan_lot, sum(glan_quantity) as add_quantity from 
      
          (select * from 
                  (select
                          b.id,
                          hospital_id,
                          dealer_id,
                          lot_number as glan_lot,
                          count(*) as glan_count,
                          sum(shipping_quantity) as glan_quantity
                  from shipping_details as a left join shippings as b on a.shipping_id=b.id where input_date > "2011-02-28"
                  group by hospital_id, dealer_id, lot_number) as c left join
                  (select
                          e.id as sanix_id,
                          warehouse_id,
                          customer_id,
                          lot_number,
                          count(*) as sanix_count,
                          sum(quantity)
                  from sanix_development.shipping_details as d left join sanix_development.shippings as e on d.shipping_id=e.id where input_date>"2011-02-28"
                  group by warehouse_id, customer_id, lot_number) as f on c.hospital_id=f.warehouse_id and c.dealer_id=f.customer_id and c.glan_lot=f.lot_number where sanix_id is NULL) as g group by g.glan_lot) as h
      on i.st_lot=h.glan_lot ) as k on h.glan_lot = k.st_lot where st_lot is NULL;
      
update sanix_development.stocks as a left join master_app_production.product_sets as b on a.product_set_id=b.id set product_category_id=b.product_category_id where a.product_cateogry_id is NULL;
