DROP TEMPORARY TABLE IF EXISTS temp_prices;

CREATE TEMPORARY TABLE temp_prices AS SELECT 
  xlentparts_ebaystoremanager_activelisting.price as currentPrice, 
  xlentparts_partsauthority_inventorydata.cost, 
  xlentparts_partsauthority_inventorydata.coreprice, 
  xlentparts_partsauthority_products.unit_weight, 
  xlentparts_partsauthority_inventorydata.cost + xlentparts_partsauthority_inventorydata.coreprice + 20 + (
    (
      xlentparts_partsauthority_inventorydata.cost + xlentparts_partsauthority_inventorydata.coreprice + 20
    ) * 15 / 100
  ) as suggestedPrice, 
  xlentparts_ebaystoremanager_activelisting.price - (
    xlentparts_partsauthority_inventorydata.cost + xlentparts_partsauthority_inventorydata.coreprice + 20 + (
      (
        xlentparts_partsauthority_inventorydata.cost + xlentparts_partsauthority_inventorydata.coreprice + 20
      ) * 15 / 100
    )
  ) as diffPrice,
  xlentparts_ebaystoremanager_activelisting.item_id,
  catalog_product_entity.sku 
FROM 
  xlentparts_ebaystoremanager_activelisting 
  LEFT JOIN catalog_product_entity ON catalog_product_entity.sku = xlentparts_ebaystoremanager_activelisting.sku 
  LEFT JOIN catalog_product_entity_varchar as line ON line.entity_id = catalog_product_entity.entity_id 
  AND line.attribute_id = 175 
  LEFT JOIN catalog_product_entity_varchar as part ON part.entity_id = catalog_product_entity.entity_id 
  AND part.attribute_id = 166 
  LEFT JOIN xlentparts_partsauthority_inventorydata ON xlentparts_partsauthority_inventorydata.line = line.value 
  AND xlentparts_partsauthority_inventorydata.part = part.value 
  LEFT JOIN xlentparts_partsauthority_products ON xlentparts_partsauthority_products.inventory_line = xlentparts_partsauthority_inventorydata.line 
  AND xlentparts_partsauthority_products.inventory_part = xlentparts_partsauthority_inventorydata.part 
WHERE 
  catalog_product_entity.entity_id IS NOT NULL 
  AND line.entity_id IS NOT NULL 
  AND part.entity_id IS NOT NULL 
  AND xlentparts_partsauthority_inventorydata.inventorydata_id IS NOT NULL;
  

SELECT suggestedPrice, sku, item_id, suggestedPrice - ((suggestedPrice + (suggestedPrice * 7 / 100) ) * 11.25 / 100) - temp_prices.cost - temp_prices.coreprice - 16 as profit   FROM temp_prices;

DROP TEMPORARY TABLE temp_prices;