-- IGC only    
UPDATE `resource` SET `erp_ref` = '6634579' WHERE facility=1; # fly services
UPDATE `resource` SET `erp_ref` = '6266286' WHERE facility=2; # Aquatics services
UPDATE `resource` SET `erp_ref` = '6266290' WHERE facility=3; # EM
UPDATE `resource` SET `erp_ref` = '6266277' WHERE facility=4; # AIF
UPDATE `resource` SET `erp_ref` = '3281760' WHERE facility=5; # FCF
UPDATE `resource` SET `erp_ref` = '6440585' WHERE facility=6; # Histo
UPDATE `resource` SET `erp_ref` = '6419950' WHERE facility=8;; # Rodents
UPDATE `resource` SET `erp_ref` = '6266282' WHERE facility=10; # Genomics NGS
-- UPDATE `resource` SET `erp_ref` = '9610161' WHERE facility=12; # washing
UPDATE `resource` SET `erp_ref` = '6634578' WHERE facility=13; # fruit fly trans
UPDATE `resource` SET `erp_ref` = '6266289' WHERE facility=15; # bioinfo
UPDATE `resource` SET `erp_ref` = '9587622' WHERE facility=19; # QDS


update fields, resource set fields.erp_ref="6266290" where facility=3 and fields.resource=resource.id; #EM requests
update fields, resource set fields.erp_ref="6266277" where facility=4 and fields.resource=resource.id; #AIF assistance etc
update fields, resource set fields.erp_ref="3281760" where facility=5 and fields.resource=resource.id; #FCF assistance etc
update fields, resource set fields.erp_ref="6440585" where facility=6 and fields.resource=resource.id; #Histo requests
update fields, resource set fields.erp_ref="7745420" where facility=9 and fields.resource=resource.id; #antibodies
update fields, resource set fields.erp_ref="9610161" where facility=12 and fields.resource=resource.id; #washing
update fields, resource set fields.erp_ref="6634578" where facility=13 and fields.resource=resource.id; #trans
update fields, resource set fields.erp_ref="6266289" where facility=15 and fields.resource=resource.id; #trans
update fields, resource set fields.erp_ref="9587622" where facility=19 and fields.resource=resource.id; #qds


update product_field, product, product_class set product_field.erp_ref="6634579" where product_class.facility=1 and product_field.product=product.id and product.product_class=product_class.id and product_class not in (2);
update product_field, product, product_class set product_field.erp_ref="6266288" where product_class.facility=1 and product_field.product=product.id and product.product_class=product_class.id and product_class in (2);
update product_field, product, product_class set product_field.erp_ref="6266287" where product_class.facility=2 and product_field.product=product.id and product.product_class=product_class.id and product_class in (42);
update product_field, product, product_class set product_field.erp_ref="6266286" where product_class.facility=2 and product_field.product=product.id and product.product_class=product_class.id and product_class not in (42);

-- histo
update product_field, product, product_class set product_field.erp_ref="6440585" where product_class.facility=6 and product_field.product=product.id and product.product_class=product_class.id;
update product_field, product, product_class set product_field.erp_ref="6266284" where product_class.facility=8 and product_field.product=product.id and product.product_class=product_class.id and product_class in (1); # rodents
update product_field, product, product_class set product_field.erp_ref="6419950" where product_class.facility=8 and product_field.product=product.id and product.product_class=product_class.id and product_class not in (1); #rodents
update product_field, product, product_class set product_field.erp_ref="6266282" where product_class.facility=10 and product_field.product=product.id and product.product_class=product_class.id and product_class not in (4,55);

-- genomics
update product_field, product, product_class set product_field.erp_ref="6266283" where product_class.facility=10 and product_field.product=product.id and product.product_class=product_class.id and product_class in (4,55);

-- antibodies
update product_field, product, product_class set product_field.erp_ref="7745420" where product_class.facility=9 and product_field.product=product.id and product.product_class=product_class.id;

-- washing
update product_field, product, product_class set product_field.erp_ref="9610161" where product_class.facility=12 and product_field.product=product.id and product.product_class=product_class.id;
     
-- plants
update product_field, product, product_class set product_field.erp_ref="9610161" where product_class.facility=20 and product_field.product=product.id and product.product_class=product_class.id;

-- QDS
update product_field, product, product_class set product_field.erp_ref="9587622" where product_class.facility=19 and product_field.product=product.id and product.product_class=product_class.id;

-- bioinfo
update product_field, product, product_class set product_field.erp_ref="6266289" where product_class.facility=19 and product_field.product=product.id and product.product_class=product_class.id;

-- EM
update product_field, product, product_class set product_field.erp_ref="6266290" where product_class.facility=3 and product_field.product=product.id and product.product_class=product_class.id;

-- Updating datawarehouse erp_ref
update datawarehouse 
  inner join calendar on datawarehouse.ref=calendar.id
  inner join resource on calendar.resource=resource.id
  set datawarehouse.erp_ref=resource.erp_ref;

update datawarehouse 
  inner join request_quantity on request_quantity.id=right(ref,char_length(ref)-locate(".", ref))
  inner join product_field on request_quantity.field=product_field.id             
  set datawarehouse.erp_ref=product_field.erp_ref where datawarehouse.type<>"resource";

