SELECT catalog_product_entity.entity_id AS productID,
       lineTable.value as line,
       partTable.value as part,
       xlentparts_partsauthority_inventorydata.coreprice as corePrice,
       xlentparts_partsauthority_inventorydata.cost as price,
       IFNULL(sumPriceTable.value,0) as sumPrice,
       xlentparts_partsauthority_inventorydata.packs as numberOfPieces,
       xlentparts_partsauthority_inventorydata.qtyonhand,
       xlentparts_partsauthority_products.unit_weight as weight
FROM   catalog_product_entity
       left join catalog_product_entity_varchar AS lineTable
              ON lineTable.entity_id = catalog_product_entity.entity_id
                 AND lineTable.attribute_id = 175
       left join catalog_product_entity_varchar AS partTable
              ON partTable.entity_id = catalog_product_entity.entity_id
                 AND partTable.attribute_id = 166
       left join catalog_product_entity_varchar AS sumPriceTable
              ON sumPriceTable.entity_id = catalog_product_entity.entity_id
                 AND sumPriceTable.attribute_id = 183
       left join xlentparts_partsauthority_inventorydata
              ON partTable.value = xlentparts_partsauthority_inventorydata.part
                 AND lineTable.value =
xlentparts_partsauthority_inventorydata.line
       left join xlentparts_partsauthority_products
              ON partTable.value =
xlentparts_partsauthority_products.inventory_part
                 AND lineTable.value =
       xlentparts_partsauthority_products.inventory_line
WHERE  catalog_product_entity.entity_id IS NOT NULL
       AND lineTable.entity_id IS NOT NULL
       AND partTable.entity_id IS NOT NULL
       AND xlentparts_partsauthority_inventorydata.inventorydata_id IS NOT NULL
       AND xlentparts_partsauthority_products.products_id IS NOT NULL




UPDATE catalog_product_entity_decimal cped
JOIN (
    SELECT catalog_product_entity.entity_id AS productID,
       lineTable.value as line,
       partTable.value as part,
       xlentparts_partsauthority_inventorydata.coreprice as corePrice,
       xlentparts_partsauthority_inventorydata.cost as price,
       IFNULL(sumPriceTable.value,0) as sumPrice,
       xlentparts_partsauthority_inventorydata.packs as numberOfPieces,
       xlentparts_partsauthority_inventorydata.qtyonhand,
       xlentparts_partsauthority_products.unit_weight as weight
FROM   catalog_product_entity
       left join catalog_product_entity_varchar AS lineTable
              ON lineTable.entity_id = catalog_product_entity.entity_id
                 AND lineTable.attribute_id = 175
       left join catalog_product_entity_varchar AS partTable
              ON partTable.entity_id = catalog_product_entity.entity_id
                 AND partTable.attribute_id = 166
       left join catalog_product_entity_varchar AS sumPriceTable
              ON sumPriceTable.entity_id = catalog_product_entity.entity_id
                 AND sumPriceTable.attribute_id = 183
       left join xlentparts_partsauthority_inventorydata
              ON partTable.value = xlentparts_partsauthority_inventorydata.part
                 AND lineTable.value =
xlentparts_partsauthority_inventorydata.line
       left join xlentparts_partsauthority_products
              ON partTable.value =
xlentparts_partsauthority_products.inventory_part
                 AND lineTable.value =
       xlentparts_partsauthority_products.inventory_line
WHERE  catalog_product_entity.entity_id IS NOT NULL
       AND lineTable.entity_id IS NOT NULL
       AND partTable.entity_id IS NOT NULL
       AND xlentparts_partsauthority_inventorydata.inventorydata_id IS NOT NULL
       AND xlentparts_partsauthority_products.products_id IS NOT NULL
) AS selected_products
ON cped.entity_id = selected_products.productID
AND cped.attribute_id = 77
SET cped.value = calculateEbayPrice(selected_products.price, selected_products.corePrice, selected_products.weight, selected_products.sumPrice, selected_products.numberOfPieces);






----------------

WITH selected_products AS (
    SELECT 
        cpe.entity_id AS productID,
        lineTable.value as line,
        partTable.value as part,
        inventory.coreprice as corePrice,
        inventory.cost as price,
        IFNULL(sumPriceTable.value,0) as sumPrice,
        inventory.packs as numberOfPieces,
        inventory.qtyonhand,
        products.unit_weight as weight
    FROM catalog_product_entity cpe
    LEFT JOIN catalog_product_entity_varchar AS lineTable ON lineTable.entity_id = cpe.entity_id AND lineTable.attribute_id = 175
    LEFT JOIN catalog_product_entity_varchar AS partTable ON partTable.entity_id = cpe.entity_id AND partTable.attribute_id = 166
    LEFT JOIN catalog_product_entity_varchar AS sumPriceTable ON sumPriceTable.entity_id = cpe.entity_id AND sumPriceTable.attribute_id = 183
    LEFT JOIN xlentparts_partsauthority_inventorydata AS inventory ON partTable.value = inventory.part AND lineTable.value = inventory.line
    LEFT JOIN xlentparts_partsauthority_products AS products ON partTable.value = products.inventory_part AND lineTable.value = products.inventory_line
    WHERE 
        cpe.entity_id IS NOT NULL
        AND lineTable.entity_id IS NOT NULL
        AND partTable.entity_id IS NOT NULL
        AND inventory.inventorydata_id IS NOT NULL
        AND products.products_id IS NOT NULL
)
UPDATE catalog_product_entity_decimal cped
JOIN selected_products ON cped.entity_id = selected_products.productID AND cped.attribute_id = 77
SET cped.value = calculateEbayPrice(selected_products.price, selected_products.corePrice, selected_products.weight, selected_products.sumPrice, selected_products.numberOfPieces);


---------------



WITH selected_products AS (
   SELECT 
        cpe.entity_id AS productID,
        lineTable.value as line,
        partTable.value as part,
        inventory.coreprice as corePrice,
        inventory.cost as price,
        IFNULL(sumPriceTable.value,0) as sumPrice,
        inventory.packs as numberOfPieces,
        inventory.qtyonhand,
        products.unit_weight as weight
    FROM catalog_product_entity cpe
    LEFT JOIN catalog_product_entity_varchar AS lineTable ON lineTable.entity_id = cpe.entity_id AND lineTable.attribute_id = 175
    LEFT JOIN catalog_product_entity_varchar AS partTable ON partTable.entity_id = cpe.entity_id AND partTable.attribute_id = 166
    LEFT JOIN catalog_product_entity_varchar AS sumPriceTable ON sumPriceTable.entity_id = cpe.entity_id AND sumPriceTable.attribute_id = 183
    LEFT JOIN xlentparts_partsauthority_inventorydata AS inventory ON partTable.value = inventory.part AND lineTable.value = inventory.line
    LEFT JOIN xlentparts_partsauthority_products AS products ON partTable.value = products.inventory_part AND lineTable.value = products.inventory_line
    WHERE 
        cpe.entity_id IS NOT NULL
        AND lineTable.entity_id IS NOT NULL
        AND partTable.entity_id IS NOT NULL
        AND inventory.inventorydata_id IS NOT NULL
        AND products.products_id IS NOT NULL
)

CREATE TEMPORARY TABLE temp_calculated_prices AS
SELECT 
    productID,
    ebay_price,
    ebay_price - ((ebay_price * 15 / 100) + 0.35) as magento_price
FROM (
    SELECT 
        productID,
        calculateEbayPrice(price, corePrice, weight, sumPrice, numberOfPieces) as ebay_price
    FROM selected_products
) as calculated_prices;





------

DROP TEMPORARY TABLE IF EXISTS temp_calculated_prices;

CREATE TEMPORARY TABLE temp_calculated_prices AS
SELECT 
    sp.productID,
    sp.ebay_price,
    sp.ebay_price - ((sp.ebay_price * 15 / 100) + 0.30) as magento_price
FROM (
    SELECT 
        productID,
        calculateEbayPrice(price, corePrice, weight, sumPrice, numberOfPieces) as ebay_price
    FROM (
        SELECT 
            cpe.entity_id AS productID,
            lineTable.value as line,
            partTable.value as part,
            inventory.coreprice as corePrice,
            inventory.cost as price,
            IFNULL(sumPriceTable.value,0) as sumPrice,
            inventory.packs as numberOfPieces,
            inventory.qtyonhand,
            products.unit_weight as weight
        FROM catalog_product_entity cpe
        LEFT JOIN catalog_product_entity_varchar AS lineTable ON lineTable.entity_id = cpe.entity_id AND lineTable.attribute_id = 175
        LEFT JOIN catalog_product_entity_varchar AS partTable ON partTable.entity_id = cpe.entity_id AND partTable.attribute_id = 166
        LEFT JOIN catalog_product_entity_varchar AS sumPriceTable ON sumPriceTable.entity_id = cpe.entity_id AND sumPriceTable.attribute_id = 183
        LEFT JOIN xlentparts_partsauthority_inventorydata AS inventory ON partTable.value = inventory.part AND lineTable.value = inventory.line
        LEFT JOIN xlentparts_partsauthority_products AS products ON partTable.value = products.inventory_part AND lineTable.value = products.inventory_line
        WHERE 
            cpe.entity_id IS NOT NULL
            AND lineTable.entity_id IS NOT NULL
            AND partTable.entity_id IS NOT NULL
            AND inventory.inventorydata_id IS NOT NULL
            AND products.products_id IS NOT NULL
    ) AS selected_products
) AS sp;

-------------





-------------------

DROP TABLE IF EXISTS temp_calculated_prices;

CREATE TABLE temp_calculated_prices AS
SELECT 
    sp.productID,
    sp.ebay_price,
    CEILING((sp.ebay_price - ((sp.ebay_price * 15 / 100) + 0.30)) * 20) / 20 as magento_price,
    sp.qty as magento_qty
FROM (
    SELECT 
        productID,
        calculateEbayPrice(price, corePrice, weight, sumPrice, numberOfPieces) as ebay_price,
        qty
    FROM (
        SELECT 
            cpe.entity_id AS productID,
            lineTable.value as line,
            partTable.value as part,
            inventory.coreprice as corePrice,
            inventory.cost as price,
            IFNULL(sumPriceTable.value,0) as sumPrice,
            CASE WHEN inventory.packs = 0 THEN 1 ELSE inventory.packs END as numberOfPieces,
            CASE WHEN inventory.packs = 0 THEN inventory.qtyonhand ELSE inventory.qtyonhand DIV inventory.packs END as qty,
            products.unit_weight as weight
        FROM catalog_product_entity cpe
        LEFT JOIN catalog_product_entity_varchar AS lineTable ON lineTable.entity_id = cpe.entity_id AND lineTable.attribute_id = 175
        LEFT JOIN catalog_product_entity_varchar AS partTable ON partTable.entity_id = cpe.entity_id AND partTable.attribute_id = 166
        LEFT JOIN catalog_product_entity_varchar AS sumPriceTable ON sumPriceTable.entity_id = cpe.entity_id AND sumPriceTable.attribute_id = 183
        LEFT JOIN xlentparts_partsauthority_inventorydata AS inventory ON partTable.value = inventory.part AND lineTable.value = inventory.line
        LEFT JOIN xlentparts_partsauthority_products AS products ON partTable.value = products.inventory_part AND lineTable.value = products.inventory_line
        WHERE 
            cpe.entity_id IS NOT NULL
            AND lineTable.entity_id IS NOT NULL
            AND partTable.entity_id IS NOT NULL
            AND inventory.inventorydata_id IS NOT NULL
            AND products.products_id IS NOT NULL
    ) AS selected_products
) AS sp;



DROP TABLE IF EXISTS temp_calculated_prices;

CREATE TABLE temp_calculated_prices AS
SELECT 
    sp.price,
    sp.corePrice,
    sp.sumPrice,
    sp.weight,
	sp.ebay_price,
    sp.numberOfPieces,
    sp.qty,
    sp.qtyonhand
FROM (
    SELECT 
           price,
        corePrice,
        weight,
        sumPrice,
        calculateEbayPrice(price, corePrice, weight, sumPrice, numberOfPieces) as ebay_price,
        qtyonhand,
 		qty,
        numberOfPieces
    FROM (
        SELECT 
            inventory.coreprice as corePrice,
            inventory.cost as price,
            0 as sumPrice,
            inventory.qtyonhand as qtyonhand,
            CASE WHEN inventory.packs = 0 THEN 1 ELSE inventory.packs END as numberOfPieces,
            CASE WHEN inventory.packs = 0 THEN inventory.qtyonhand ELSE inventory.qtyonhand DIV inventory.packs END as qty,
            products.unit_weight as weight
        FROM xlentparts_partsauthority_inventorydata inventory
        LEFT JOIN xlentparts_partsauthority_products AS products ON inventory.part = products.inventory_part AND inventory.line = products.inventory_line
        WHERE 
            inventory.inventorydata_id IS NOT NULL
            AND products.products_id IS NOT NULL
    ) AS selected_products
) AS sp;






-------
DROP TABLE IF EXISTS temp_calculated_prices;

CREATE TABLE temp_calculated_prices AS
SELECT 
    sp.productID,
    sp.ebay_price,
    CEILING((sp.ebay_price - ((sp.ebay_price * 15 / 100) + 0.30)) * 20) / 20 as magento_price,
    sp.qty as magento_qty,
    IF(sp.qty - 1 > 3, 3, IF(sp.qty - 1 < 0, 0, sp.qty - 1)) as ebay_qty
FROM (
    SELECT 
        productID,
        calculateEbayPrice(price, corePrice, weight, sumPrice, numberOfPieces) as ebay_price,
        qty
    FROM (
        SELECT 
            cpe.entity_id AS productID,
            lineTable.value as line,
            partTable.value as part,
            inventory.coreprice as corePrice,
            inventory.cost as price,
            IFNULL(sumPriceTable.value,0) as sumPrice,
            CASE WHEN inventory.packs = 0 THEN 1 ELSE inventory.packs END as numberOfPieces,
            CASE WHEN inventory.packs = 0 THEN inventory.qtyonhand ELSE inventory.qtyonhand DIV inventory.packs END as qty,
            products.unit_weight as weight
        FROM catalog_product_entity cpe
        LEFT JOIN catalog_product_entity_varchar AS lineTable ON lineTable.entity_id = cpe.entity_id AND lineTable.attribute_id = 175
        LEFT JOIN catalog_product_entity_varchar AS partTable ON partTable.entity_id = cpe.entity_id AND partTable.attribute_id = 166
        LEFT JOIN catalog_product_entity_varchar AS sumPriceTable ON sumPriceTable.entity_id = cpe.entity_id AND sumPriceTable.attribute_id = 183
        LEFT JOIN xlentparts_partsauthority_inventorydata AS inventory ON partTable.value = inventory.part AND lineTable.value = inventory.line
        LEFT JOIN xlentparts_partsauthority_products AS products ON partTable.value = products.inventory_part AND lineTable.value = products.inventory_line
        WHERE 
            cpe.entity_id IS NOT NULL
            AND lineTable.entity_id IS NOT NULL
            AND partTable.entity_id IS NOT NULL
            AND inventory.inventorydata_id IS NOT NULL
            AND products.products_id IS NOT NULL
    ) AS selected_products
) AS sp;









DROP TABLE IF EXISTS temp_calculated_prices;

CREATE TABLE temp_calculated_prices AS
SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, 0, IF(inventory.packs = 0, 1, inventory.packs), inventory.qtyonhand), '$.ebay_price')) AS ebay_price,
    JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, 0, IF(inventory.packs = 0, 1, inventory.packs), inventory.qtyonhand), '$.magento_price')) AS magento_price,
    JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, 0, IF(inventory.packs = 0, 1, inventory.packs), inventory.qtyonhand), '$.magento_qty')) AS magento_qty,
    JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, 0, IF(inventory.packs = 0, 1, inventory.packs), inventory.qtyonhand), '$.ebay_qty')) AS ebay_qty
FROM xlentparts_partsauthority_inventorydata as inventory
LEFT JOIN xlentparts_partsauthority_products AS products ON inventory.part = products.inventory_part AND inventory.line = products.inventory_line
        WHERE 
            inventory.inventorydata_id IS NOT NULL
            AND products.products_id IS NOT NULL




DROP TEMPORARY TABLE IF EXISTS temp_calculated_prices;

CREATE TEMPORARY TABLE temp_calculated_prices AS
SELECT 
    cpe.entity_id AS productID,
    JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, IFNULL(sumPriceTable.value,0), IF(inventory.packs = 0, 1, inventory.packs), inventory.qtyonhand), '$.ebay_price')) AS ebay_price,
    JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, IFNULL(sumPriceTable.value,0), IF(inventory.packs = 0, 1, inventory.packs), inventory.qtyonhand), '$.magento_price')) AS magento_price,
    JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, IFNULL(sumPriceTable.value,0), IF(inventory.packs = 0, 1, inventory.packs), inventory.qtyonhand), '$.magento_qty')) AS magento_qty,
    JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, IFNULL(sumPriceTable.value,0), IF(inventory.packs = 0, 1, inventory.packs), inventory.qtyonhand), '$.ebay_qty')) AS ebay_qty
FROM catalog_product_entity cpe
        LEFT JOIN catalog_product_entity_varchar AS lineTable ON lineTable.entity_id = cpe.entity_id AND lineTable.attribute_id = 175
        LEFT JOIN catalog_product_entity_varchar AS partTable ON partTable.entity_id = cpe.entity_id AND partTable.attribute_id = 166
        LEFT JOIN catalog_product_entity_varchar AS sumPriceTable ON sumPriceTable.entity_id = cpe.entity_id AND sumPriceTable.attribute_id = 183
        LEFT JOIN xlentparts_partsauthority_inventorydata AS inventory ON partTable.value = inventory.part AND lineTable.value = inventory.line
        LEFT JOIN xlentparts_partsauthority_products AS products ON partTable.value = products.inventory_part AND lineTable.value = products.inventory_line
        WHERE 
            cpe.entity_id IS NOT NULL
            AND lineTable.entity_id IS NOT NULL
            AND partTable.entity_id IS NOT NULL
            AND inventory.inventorydata_id IS NOT NULL
            AND products.products_id IS NOT NULL;

UPDATE catalog_product_entity_decimal cped
LEFT JOIN temp_calculated_prices tcp ON tcp.productID = cped.entity_id
JOIN eav_attribute ea ON ea.attribute_id = cped.attribute_id AND ea.attribute_code = 'price'
SET cped.value = tcp.magento_price
WHERE tcp.productID IS NOT NULL;

INSERT INTO catalog_product_entity_tier_price (
    entity_id, 
    customer_group_id, 
    qty, 
    value, 
    website_id, 
    all_groups, 
    percentage_value
)
SELECT 
    tcp.productID, 
    4 as customer_group_id, 
    tcp.ebay_qty as qty, 
    tcp.ebay_price as value, 
    0 as website_id,
    0 as all_groups,
    NULL as percentage_value
FROM temp_calculated_prices tcp
ON DUPLICATE KEY UPDATE 
    value = VALUES(value), 
    qty = VALUES(qty);


UPDATE cataloginventory_stock_item csi
LEFT JOIN temp_calculated_prices tcp ON tcp.productID = csi.product_id
SET 
    csi.qty = tcp.magento_qty,
    csi.is_in_stock = CASE WHEN tcp.magento_qty <= 0 THEN 0 ELSE 1 END
WHERE tcp.productID IS NOT NULL;

UPDATE inventory_source_item isi
LEFT JOIN temp_calculated_prices tcp ON tcp.productID = isi.source_item_id
SET isi.quantity = tcp.magento_qty
WHERE tcp.productID IS NOT NULL;



SELECT 
  xlentparts_ebaystoremanager_activelisting.*, 
  ebayData.qty as updatedQty,
  ebayData.value as updatedPrice,
  IF(ebayData.qty = 0 AND xlentparts_ebaystoremanager_activelisting.quantity_sold = 0, "end", "revise") as action
FROM 
  xlentparts_ebaystoremanager_activelisting 
  LEFT JOIN catalog_product_entity ON xlentparts_ebaystoremanager_activelisting.sku = catalog_product_entity.sku 
  LEFT JOIN catalog_product_entity_tier_price as ebayData ON catalog_product_entity.entity_id = ebayData.entity_id AND ebayData.customer_group_id = 4
WHERE ebayData.qty != xlentparts_ebaystoremanager_activelisting.quantity OR ebayData.value != xlentparts_ebaystoremanager_activelisting.price