Specific price through code (programatic way)

[hakeryk2]

Ahoi Devs!

I want to add some specific prices to some products and their attributes in php but actually I can't find any method to do this?

Is there is any function like 
 

$specific_price = New SpecificPrice($id_product, $id_product_attribute);
$specific_price->discount = 10;
$specific_price->method = 'percentage';
$specific_price->ends = '2018-02-10 23:59:59';

$specific_price->save();

This is just imaginery example but You know what I want to achieve Any help?

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

Edit: Got it

 

/**
 * @param int $id_product
 * @param int $id_product_attribute if 0 then for all attributes
 * @param float $reduction_value ie. 0.07 means 7% when percentage as $reduction_type and 10.00 means 10 when amount
 * @param string $reduction_type is 'percentage' or 'amount'
 * @param int $tax if set to 1 then price of specific price is brutto if 0 then netto
 * @param string $from_date is 'YYYY-MM-DD HH:MM:SS'
 * @param string $to_date is 'YYYY-MM-DD HH:MM:SS'
 * @param int $from_quantity of products
 * @param int $id_shop 0 for all
 * @param int $id_shop_group 0 for all
 * @param int $id_currency 0 for all
 * @param int $id_country 0 for all
 * @param int $id_customer 0 for all
 * @param int $price is always -1
 * @param int $from_quantity default is 1
 * @return int id of this specific price created
 */
function addSpecificPrice($id_product, $id_product_attribute = 0, $reduction_value = 0.00, $reduction_type = 'percentage', $from_date = '0000-00-00 00:00:00', $to_date = '0000-00-00 00:00:00', $id_shop = 0, $tax = 1, $from_quantity = 1,  , $id_shop_group = 0, $id_currency = 0, $id_country = 0, $id_customer = 0, $price = -1, $from_quantity = 1) {

    $specificPrice = new SpecificPrice();
    $specificPrice->id_product = (int)$id_product;
    $specificPrice->id_product_attribute = (int)$id_product_attribute; // if 0 then for all attributes

    $specificPrice->reduction = $reduction_value; // 7% is 0.07
    $specificPrice->reduction_type = $reduction_type;
    $specificPrice->reduction_tax = $tax;
    $specificPrice->id_shop = $id_shop;
    $specificPrice->id_currency = $id_currency;
    $specificPrice->id_country = $id_country;
    $specificPrice->id_group = $id_shop_group;
    $specificPrice->id_customer = $id_customer;
    $specificPrice->price = $price;
    $specificPrice->from_quantity = $from_quantity;
    $specificPrice->from = $from_date;
    $specificPrice->to = $to_date;

    $specificPrice->add();
    $specificPrice->save();
    $addedPriceId = $specificPrice->id;

    return $addedPriceId;
}

addSpecificPrice(1935, 8458, 0.10, 'percentage', '0000-00-00 00:00:00', '2018-02-18 23:59:59', 1);

 

Edited January 4, 2018 by hakeryk2
Solution found. (see edit history)

[joseantgv]

Nice! But please instanciate main class to avoid problems with overrides:

 

$specificPrice = new SpecificPrice();

 

[hakeryk2]

Oh, thanks to mention that - I just changde this during develop process to be able to see hints from vars in my IDE  Edited.

Edited January 4, 2018 by hakeryk2 (see edit history)

[mr_absinthe]

Hi, not sure if you're still around, your php is sort of what I was looking for, would you be so kind as to tell me how do I change $from_date to use NOW() and $to_date to use DATE_ADD(NOW(), INTERVAL 7 DAY? I've been trying for hours but to no luck.

[ps8modules]

$from_date = date('Y-m-d H:i:s');

$to_date = date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s').' +7 day'));

 

https://www.php.net/manual/en/function.date.php

Edited February 16, 2023 by ps8moduly.cz (see edit history)

[mr_absinthe]

I tried, the problem is that the function is all comma separated:

function addSpecificPrice($id_product, $reduction_value = 0.00, $reduction_type = 'percentage', $from_date = '0000-00-00 00:00:00', $to_date = '0000-00-00 00:00:00', $id_shop = 0, $tax = 1, $id_shop_group = 0, $id_currency = 0, $id_country = 0, $id_customer = 0, $price = -1, $from_quantity = 1)

I also tried removing it from the function and include it above it, no error but it does nothing

$from_date = date('Y-m-d H:i:s');

function addSpecificPrice($id_product, $reduction_value = 0.00, $reduction_type = 'percentage', $to_date = '0000-00-00 00:00:00', $id_shop = 0, $tax = 1, $id_shop_group = 0, $id_currency = 0, $id_country = 0, $id_customer = 0, $price = -1, $from_quantity = 1) {

So, I guess I change only this line?

addSpecificPrice(4, 0.20, 'percentage', '0000-00-00 00:00:00', '2023-02-18 23:59:59', 1);

 

Edited February 14, 2023 by mr_absinthe (see edit history)

[mr_absinthe]

I figured that the above script will always insert a new ID for specific price instead of updating the current one, so I'm trying to write a simple php file that will update the table. However since the PS developers called one column "from", I keep having errors and this simply doesn't work:

Quote

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'to = 'DATE_ADD(NOW(), INTERVAL 7 DAY)' WHERE id_specific_price = 18' at line 1

Anyone can figure it out pls? Here's the code:

runSql("Update Specific Price", "UPDATE "._DB_PREFIX_."specific_price SET reduction = '0.180000', reduction_type = 'percentage' , "from" = 'NOW()', to = 'DATE_ADD(NOW(), INTERVAL 7 DAY)'  WHERE id_specific_price = 18");

 

[joseantgv]

Paste the full query.

[ps8modules]

I don't understand where the problem is when everything works and no extra update to the database is needed. 🙃

Custom module function:

public function addMySpecificPrice(
        $id_product,
        $price = '-1.000000',
        $from_quantity = 1,
        $reduction = '0.07', /* 7% */
        $reduction_tax = 1, /* id tax */
        $from = '0000-00-00 00:00:00',
        $to = '0000-00-00 00:00:00', 
        $id_currency = 0,
        $id_shop_group = 0,
        $id_shop = 1,
        $id_cart = 0,
        $id_product_attribute = 0,
        $id_specific_price_rule = 0,
        $id_country = 0,
        $id_group = 0,
        $id_customer = 0,
        $reduction_type = 'percentage')
    {
        $specificPrice = new SpecificPrice();
        $specificPrice->id_product = (int)$id_product;
        $specificPrice->id_product_attribute = (int)$id_product_attribute;
        $specificPrice->reduction = $reduction; 
        $specificPrice->reduction_type = $reduction_type;
        $specificPrice->reduction_tax = $reduction_tax;
        $specificPrice->id_shop = $id_shop;
        $specificPrice->id_currency = $id_currency;
        $specificPrice->id_country = $id_country;
        $specificPrice->id_group = $id_shop_group;
        $specificPrice->id_customer = $id_customer;
        $specificPrice->price = $price;
        $specificPrice->from_quantity = $from_quantity;
        $specificPrice->from = $from;
        $specificPrice->to = $to;
        $specificPrice->add();
        $addedPriceId = $specificPrice->id;
        return $addedPriceId;
}

 

Call function:

$getInsertedSpecificPrice = $this->addMySpecificPrice(1, '-1.000000', 1, '0.07', 1, date('Y-m-d H:i:s'), date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s').' +7 day')));

 

[mr_absinthe]

@joseantgv here it is:

<?php
require_once('./config/config.inc.php');

header('Content-Type: text/plain');

echo "Today is " . date('l jS \of F Y h:i:s A') . "\n\n";

function runSql($description, $sql) {
    echo "$description: ";
    $conn = Db::getInstance();
    try {
        $conn->execute($sql);
        $error = $conn->getMsgError();
        if ($error) {
            echo "Failed\nError: " . $conn->getMsgError() . "\n";
        } else {
            echo "Success [updated " . $conn->Affected_Rows() . " records]\n";
        }
    } catch (Exception $e) {
        echo "Failed\nError: " . $e->getMessage() . "\n";
    }
}

runSql("Update Specific Price", "UPDATE "._DB_PREFIX_."specific_price SET reduction = '0.180000', reduction_type = 'percentage' , `from` = 'NOW()', to = 'DATE_ADD(NOW(), INTERVAL 7 DAY)'  WHERE id_specific_price = 18");

?>

What I see as an advantage of the above is that it updates id_specific_price column thus not changing id_shop_group and if needed, we can update more products or customer groups at once by duplicating the runSgl.

[ps8modules]

I wrote to you several times how to add a date.
It is necessary to realize that the SQL function for the date loads a local format that is not valid !!!

runSql("Update Specific Price", "UPDATE "._DB_PREFIX_."specific_price SET reduction = '0.180000', reduction_type = 'percentage' , `from` = '".date('Y-m-d H:i:s')."', `to` = '".date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s').' +7 day'))."' WHERE id_specific_price = 18");

Edited February 16, 2023 by ps8moduly.cz (see edit history)

[mr_absinthe]

It may be obvious to you or to someone who is knowledgeable in coding but not to me I'm afraid. If it was, I wouldn't be posting questions here but answers. Anyway, there still seems to be a problem: 

Quote

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'to = '2023-02-23 16:14:50' WHERE id_specific_price = 18' at line 1

Using PHP version: 7.4.33 and 10.6.12-MariaDB if that makes any difference.

Edited February 16, 2023 by mr_absinthe (see edit history)

[ps8modules]

It is necessary to put the whole error here, not cut it.

[mr_absinthe]

It is all I can see on the screen when I call the file in browser.

[ps8modules]

Check if it's to quotes `to`

https://en.wikipedia.org/wiki/List_of_SQL_reserved_words

Reserved SQL words:

Reserved words / expressions must be enclosed in backticks `

Edited February 16, 2023 by ps8moduly.cz (see edit history)

[mr_absinthe]

That was it, missing backticks, thanks a lot, now it is working as expected, here's the complete file if anyone needs it

<?php
require_once('./config/config.inc.php');

header('Content-Type: text/plain');

echo "Today is " . date('l jS \of F Y h:i:s A') . "\n\n";

function runSql($description, $sql) {
    echo "$description: ";
    $conn = Db::getInstance();
    try {
        $conn->execute($sql);
        $error = $conn->getMsgError();
        if ($error) {
            echo "Failed\nError: " . $conn->getMsgError() . "\n";
        } else {
            echo "Success [updated " . $conn->Affected_Rows() . " records]\n";
        }
    } catch (Exception $e) {
        echo "Failed\nError: " . $e->getMessage() . "\n";
    }
}

//set special offer from today for 7 days, 20% off

runSql("Update Specific Price for Visitors", "UPDATE "._DB_PREFIX_."specific_price SET reduction = '0.200000', reduction_type = 'percentage' , `from` = '".date('Y-m-d H:i:s')."', `to` = '".date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s').' +7 day'))."' WHERE id_specific_price = 18");

runSql("Update Specific Price for Guests", "UPDATE "._DB_PREFIX_."specific_price SET reduction = '0.200000', reduction_type = 'percentage' , `from` = '".date('Y-m-d H:i:s')."', `to` = '".date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s').' +7 day'))."' WHERE id_specific_price = 28");

runSql("Update Specific Price for Customers", "UPDATE "._DB_PREFIX_."specific_price SET reduction = '0.200000', reduction_type = 'percentage' , `from` = '".date('Y-m-d H:i:s')."', `to` = '".date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s').' +7 day'))."' WHERE id_specific_price = 29");

?>

It is quite self-explanatory, the specific price must exist hence the id_specific_price.

[mr_absinthe]

It would be great if we could run the cron job in specific times that are not possible to set using standard cron job settings, ie. every three weeks on Thursday. There are some examples but a wrapper script is required which is currently beyond my knowledge I'm afraid. 🤔

[mr_absinthe]

I think that there might be an elegant solution without a script... Let's assume that we want to schedule the cron job to run on every Saturday at 6am with the interval of 21 days.

0 6 * * 6 lr=./lastrun; [ "`find $lr -mtime -21`" ] || { touch $lr; /path/to/real/job; }

Assuming that . (the home directory) is writable.
The find will report the file if less than 21 days old, then nothing is done.
Otherwise the file is refreshed and the job is done.

[najib elazoui]

On 2/16/2023 at 2:07 PM, joseantgv said:

Paste the full query.

function createOrUpdateSpecificPrice($productId, $idProductAttribute, $reductionPercent) {
    // Convert reduction percentage to a decimal
    $reductionDecimal = $reductionPercent / 100;

    // Define the current date and the date 7 days from now
    $currentDate = date('Y-m-d H:i:s');
    $futureDate = date('Y-m-d H:i:s', strtotime('+7 days'));

    // SQL query to check if the specific price exists
    $sqlCheck = "
        SELECT id_specific_price
        FROM "._DB_PREFIX_."specific_price
        WHERE id_product = ".(int)$productId."
        AND id_product_attribute = ".(int)$idProductAttribute."
        AND id_shop = ".(int)Context::getContext()->shop->id."
        AND reduction_type = 'percentage'
    ";

    // Check if the specific price exists
    $idSpecificPrice = Db::getInstance()->getValue($sqlCheck);

    if ($idSpecificPrice) {
        // Update existing specific price
        $sqlUpdate = "
            UPDATE "._DB_PREFIX_."specific_price
            SET reduction = '".pSQL($reductionDecimal)."',
                reduction_type = 'percentage',
                `from` = '".pSQL($currentDate)."',
                `to` = '".pSQL($futureDate)."'
            WHERE id_specific_price = ".(int)$idSpecificPrice."
        ";

        $result = Db::getInstance()->execute($sqlUpdate);
    } else {
        // Insert new specific price
        $sqlInsert = "
            INSERT INTO "._DB_PREFIX_."specific_price
            (id_product, id_product_attribute, id_shop, reduction, reduction_type, `from`, `to`)
            VALUES
            (".(int)$productId.", ".(int)$idProductAttribute.", ".(int)Context::getContext()->shop->id.",
            '".pSQL($reductionDecimal)."', 'percentage', '".pSQL($currentDate)."', '".pSQL($futureDate)."')
        ";

        $result = Db::getInstance()->execute($sqlInsert);
    }

    return $result;
}