[SOLVED] PHP show all products with quantity combination equal to 0 ?

[catzarov]

Hello very honourable comrades
I've read many forum threads, many search engine results, but can't find a solution! From all the synthesized information, I understand that ajax collects information about the availability of products with combinations. Someone please guide me, how with php can I extract a list in (standalone external file) which php script show me only ID or Ref# of products and which currently have 0 quantity in any of their sale attributes ? 

I tried to achieve this process through API, but somewhere my knowledge falls short and I can't pull out the availability.
 

<?php

// API URL for products
$url_products = 'https://example.com/api/products/?display=[id,reference,name,active]&output_format=JSON';

// API Key
$api_key = 'fullaccess';

// Initialize cURL for products
$ch_products = curl_init($url_products);

// Set cURL options for products
curl_setopt($ch_products, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch_products, CURLOPT_HTTPHEADER, array(
    'Authorization: Basic ' . base64_encode($api_key . ':')
));

// Execute cURL request for products
$response_products = curl_exec($ch_products);

// Check for cURL errors for products
if(curl_errno($ch_products)) {
    echo 'Error:' . curl_error($ch_products);
} else {
    // Decode JSON response for products
    $data_products = json_decode($response_products, true);
    
    // Check if there are any products returned
    if(isset($data_products['products'])) {
        // Loop through products
        foreach($data_products['products'] as $product) {
            // Check if product is active
            if($product['active']) {
                // Extract product names from nested array of objects
                $product_names = array_column($product['name'], 'value');
                // Combine product names into a single string
                $product_name = implode(', ', $product_names);
                
                // Output product ID, Ref, and Name
                echo "ID: " . $product['id'] . ", Ref: " . $product['reference'] . ", Name: " . $product_name . "<br>";
                
                // API URL for product combinations
                $url_combinations = 'https://example.com/api/combinations/?display=[reference,quantity]&filter[id_product]=' . $product['id'] . '&output_format=JSON';
                
                // Initialize cURL for product combinations
                $ch_combinations = curl_init($url_combinations);
                
                // Set cURL options for product combinations
                curl_setopt($ch_combinations, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($ch_combinations, CURLOPT_HTTPHEADER, array(
                    'Authorization: Basic ' . base64_encode($api_key . ':')
                ));
                
                // Execute cURL request for product combinations
                $response_combinations = curl_exec($ch_combinations);
                
                // Check for cURL errors for product combinations
                if(curl_errno($ch_combinations)) {
                    echo 'Error:' . curl_error($ch_combinations);
                } else {
                    // Decode JSON response for product combinations
                    $data_combinations = json_decode($response_combinations, true);
                    
                    // Check if there are any combinations returned
                    if(isset($data_combinations)) {
                        // Loop through combinations and output reference and quantity
                        foreach($data_combinations as $combination) {
                            // Check if 'reference' and 'quantity' keys exist in the combination
                            if(isset($combination['reference']) && isset($combination['quantity'])) {
                                // Output reference
                                echo "Size Ref: " . $combination['reference'] . ", ";
                                
                                // Output quantity attributes and their quantities
                                foreach($combination['quantity'] as $attribute => $quantity) {
                                    echo $attribute . ": " . $quantity . " броя, ";
                                }
                            } else {
                                echo "No combinations found for this product.";
                            }
                            
                            // Add a line break
                            echo "<br>";
                        }
                    } else {
                        echo "No combinations found for this product.";
                    }
                }
                
                // Close cURL session for product combinations
                curl_close($ch_combinations);
                
                // Add a line break for better readability
                echo "<br>";
            }
        }
    } else {
        echo "No products found.";
    }
}

// Close cURL session for products
curl_close($ch_products);
?>

For me, it's irrelevant whether I get the list via the API protocol or with programming code that directly messes with mysql and outputs the information. I just want to see that on product ID100, there are 0 quantity per size M and 0 quantity per size XXXL

Thank you for your time and attention. 

Best regards

Edited April 25 by catzarov
the topic is solved (see edit history)

[Nickz]

The best way would be to ask directly what you need to do, and wish to archive naming the PS Version, the theme, and the module.

[catzarov]

Hello @Nickz Thank you very much for your time and your comment on my question. You probably have a lot of knowledge, but I can't reach a solution to my problem after your comment. Please, if you understand what I have asked, give me a solution, a sample code or a correction of my code so that I understand you. I don't want to waste each other's time in comments, if you can't help me, just delete your comment, I'll delete mine so someone who understands can be helpful. I say this with the best of sentiments and intentions. In my PHP code maybe something needs to be added somewhere, but the prestashop documentation literature doesn't give me enough clarity on how to reach a solution myself, so I decided to ask.

Best regards @Nickz

[Nickz]

This is a forum which needs those problem so others can repair their issues

19 hours ago, catzarov said:

I understand that ajax collects information about the availability of products with combinations. 

I bet @musicmaster can help

Edited April 24 by Nickz (see edit history)

[ventura]

for products and combinations stock

https://devdocs.prestashop-project.org/8/webservice/resources/stock_availables/

[catzarov]

Hello,
I found the solution myself, I will not use API but directly with database connection. The lower code for version 1.7.8.7 works correctly, which makes me very happy.
I tested it on several products that were purchased in their last available sizes and had 0 quantity in the menu Monitoring -> "List of products with combinations but without available quantities for sale". With the code below, it is much faster to remove the 0 quantity. 

<?php
$servername = "localhost"; // 
$username = ""; // 
$password = ""; // 
$dbname = ""; // 

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id_product FROM ps17_product WHERE active = 1";
$result = $conn->query($sql);
$active_products = array();
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $active_products[] = $row["id_product"];
    }
}
$zero_quantity_attributes = array();

foreach ($active_products as $product_id) {
    $sql = "SELECT pa.id_product_attribute FROM ps17_product_attribute pa
            JOIN ps17_stock_available sa ON pa.id_product_attribute = sa.id_product_attribute
            WHERE pa.id_product = $product_id AND sa.quantity = 0
            AND pa.id_product_attribute NOT IN (
                SELECT pa2.id_product_attribute FROM ps17_product_attribute pa2
                JOIN ps17_stock_available sa2 ON pa2.id_product_attribute = sa2.id_product_attribute
                WHERE pa2.id_product = $product_id AND sa2.quantity > 0
            )";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $zero_quantity_attributes[$product_id][] = $row["id_product_attribute"];
        }
    }
}
$attribute_ids = array();

foreach ($zero_quantity_attributes as $product_id => $attributes) {
    foreach ($attributes as $attribute_id) {
        $sql = "SELECT id_attribute FROM ps17_product_attribute_combination WHERE id_product_attribute = $attribute_id";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $attribute_ids[$product_id][$attribute_id][] = $row["id_attribute"];
            }
        }
    }
}
$attribute_names = array();

foreach ($attribute_ids as $product_id => $attributes) {
    foreach ($attributes as $attribute_id => $attribute_list) {
        foreach ($attribute_list as $attribute) {
            $sql = "SELECT name FROM ps17_attribute_lang WHERE id_attribute = $attribute AND id_lang = 1";
            $result = $conn->query($sql);

            if ($result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {
                    $attribute_names[$product_id][$attribute_id][] = $row["name"];
                }
            }
        }
    }
}

if (count($attribute_names) > 0) {
    foreach ($attribute_names as $product_id => $attributes) {
        echo "<p>Product ID: $product_id</p>";
        echo "<ul>";
        foreach ($attributes as $attribute_id => $attribute_list) {
            foreach ($attribute_list as $attribute_name) {
                echo "<li>$attribute_name <form method='POST'><input type='hidden' name='product_id' value='$product_id'><input type='hidden' name='attribute_id' value='$attribute_id'><input type='submit' name='delete_attribute' value='DELETE'></form></li>";
            }
        }
        echo "</ul>";
    }
} else {
    echo "No products with zero quantity found.";
}

if (isset($_POST['delete_attribute'])) {
    $product_id = $_POST['product_id'];
    $attribute_id = $_POST['attribute_id'];
    $sql_delete = "DELETE FROM ps17_product_attribute WHERE id_product = $product_id AND id_product_attribute = $attribute_id";
    if ($conn->query($sql_delete) === TRUE) {
        echo "The attribute has been successfully deleted.";
    } else {
        echo "An error occurred when deleting the attribute: " . $conn->error;
    }
}

$conn->close();
?>

 

Best regards to all