Untitled

 avatar
unknown
php
2 months ago
5.2 kB
3
Indexable
private function getRegenerateQuery(StoreInterface $store, string $conditionName, array $attributes, array $entityIds = []): string
    {
        $storeId = $store->getId();
        $websiteId = $store->getWebsiteId();
        $entityIdsQuery = $entityIds ? ' WHERE cpe.entity_id IN (' . implode(',', $entityIds) . ')' : '';

        if ($conditionName === 'package_weight_cost') {
            $marginCost = $this->config->getMarginCost();
            $priceAttributeId = $this->getPriceAttributeId();

            return 'INSERT INTO ' . self::PRODUCT_ATTRIBUTE_TABLE_TEXT . ' (entity_id, attribute_id, store_id, value)
                SELECT
                    cpe.entity_id,
                    ' . $attributes['lower_shipping_product_attribute_id'] . ',
                    ' . $storeId . ',
                    (
                        SELECT CONCAT(
                            \'[\',
                                IFNULL(
                                    GROUP_CONCAT(
                                        JSON_OBJECT(
                                            \'dest_zip\', wm.dest_zip,
                                            \'dest_zip_to\', wm.dest_zip_to,
                                            \'price\', round((' . $marginCost . '/100) * wm.price + cped_price.value, 2)
                                        ) SEPARATOR \', \'
                                    ),
                                    \'\'
                                ),
                            \']\'
                        )
                        FROM ' . self::MATRIXRATE_TABLE . ' wm
                        WHERE wm.website_id = ' . $websiteId . '
                        AND cped.value BETWEEN wm.condition_from_value AND wm.condition_to_value
                        AND wm.price = (
                            SELECT MIN(wm2.price)
                            FROM ' . self::MATRIXRATE_TABLE . ' wm2
                            WHERE wm2.website_id = ' . $websiteId . '
                            AND cped.value BETWEEN wm2.condition_from_value AND wm2.condition_to_value
                            AND wm2.dest_zip = wm.dest_zip
                            AND wm2.dest_zip_to = wm.dest_zip_to
                        )
                    ) AS shipping_rates
                FROM ' . self::PRODUCT_ATTRIBUTE_TABLE . ' cpe
                LEFT JOIN ' . self::PRODUCT_ATTRIBUTE_TABLE_DECIMAL . ' cped
                  ON cped.entity_id = cpe.entity_id
                    AND cped.attribute_id = ' . $attributes['condition_product_attribute_id'] . '
                  AND cped.store_id = ' . $storeId . '
                LEFT JOIN ' . self::PRODUCT_ATTRIBUTE_TABLE_DECIMAL . ' cped_price
                  ON cped_price.entity_id = cpe.entity_id
                    AND cped_price.attribute_id = ' . $priceAttributeId . '
                  AND cped_price.store_id = ' . $storeId .
                $entityIdsQuery . '
                ON DUPLICATE KEY UPDATE value = VALUES(value)';
        }

        return 'INSERT INTO ' . self::PRODUCT_ATTRIBUTE_TABLE_TEXT . ' (entity_id, attribute_id, store_id, value)
                SELECT
                    cpe.entity_id,
                    ' . $attributes['lower_shipping_product_attribute_id'] . ',
                    ' . $storeId . ',
                    (
                        SELECT CONCAT(
                            \'[\',
                                IFNULL(
                                    GROUP_CONCAT(
                                        JSON_OBJECT(
                                            \'dest_zip\', wm.dest_zip,
                                            \'dest_zip_to\', wm.dest_zip_to,
                                            \'price\', round(wm.price, 2)
                                        ) SEPARATOR \', \'
                                    ),
                                    \'\'
                                ),
                            \']\'
                        )
                        FROM ' . self::MATRIXRATE_TABLE . ' wm
                        WHERE wm.website_id = ' . $websiteId . '
                        AND cped.value BETWEEN wm.condition_from_value AND wm.condition_to_value
                        AND wm.price = (
                            SELECT MIN(wm2.price)
                            FROM ' . self::MATRIXRATE_TABLE . ' wm2
                            WHERE wm2.website_id = ' . $websiteId . '
                            AND cped.value BETWEEN wm2.condition_from_value AND wm2.condition_to_value
                            AND wm2.dest_zip = wm.dest_zip
                            AND wm2.dest_zip_to = wm.dest_zip_to
                        )
                    ) AS shipping_rates
                FROM ' . self::PRODUCT_ATTRIBUTE_TABLE . ' cpe
                LEFT JOIN ' . self::PRODUCT_ATTRIBUTE_TABLE_DECIMAL . ' cped
                  ON cped.entity_id = cpe.entity_id
                    AND cped.attribute_id = ' . $attributes['condition_product_attribute_id'] . '
                  AND cped.store_id = ' . $storeId .
            $entityIdsQuery . '
                ON DUPLICATE KEY UPDATE value = VALUES(value)';
    }
Editor is loading...
Leave a Comment