Mass combination price impact calculation script

[printshop]

Hi, I'm not sure if this is the right place for my query, if it isnt then I appologise in advance.

Hopefully someone has faced something similar annd can help.

I have on average between 500-600 combinations per product, I have entered the whole sale value for each combination and the price impact for each one is set to '0' at the moment (couldnt use combination generator for this as there is no set value for each attribute value, it all varieseach combination).

Basically what I am after is a script that will do the following calculation and update the combination price for each combination:

(Combination Wholesale Price + %) - Product Pre-Tax Price = Combination Impact

 

I understand that due to the large number of combinations this could take forever, so if ther was a value in the script where I could change the product ID so that it only pulls the product pre-tax price and only updates combinations linked to that combination then this would be great and hopefully will prevent any sort of timing out issues, etc. And obviously where I can edit the percentage in the script.

 

If someone can help it would be much appreciated as it will save me sitting there with a calculator and going through each combination one-by-one.

 

Im not after any fancy forms etc, just the the script (where i will edit the product ID and percent value before uploading) and then just point my browser to the file and it simply display "Done" when its finished.

I am using Prestashop 1.6.0.6

 

Thankyou in advance for anyone who can help, and hopefully this will help others in similar situations.

[printshop]

Ok, I had a go at starting the code, hopefully to give a better understanding of what I'm after, but unfortunately my knowledge is limited and have no Idea to retrieve multiple records, edit and then update so hopefully someone can help.

I left the $combinations and $update parts in to provide a more visual way to show what I am after, obviously I know they won't do anything as they are, they are just there for display purposes.

$impact is the formula to create the impact price, I preset values for $wholesale_price and $pretax like Whats done for $percent amd it works great calculating the one result, will probably need updating to handle all the combinations.

<?php

$DBServer = 'SERVER'; // e.g 'localhost' or '192.168.1.100'
$DBUser = 'USER';
$DBPass = 'PASSWORD^';
$DBName = 'DATABASENAME';
$pid = '1'; //We shall say we are going to update combination impact price for all combinations linked to Product ID 1
$percent = '50'; //We shall say we are going to add 50% profit margin on the Combination Wholesale Price 

$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);

//Get Product Pre Tax Price
$price = $conn->prepare("SELECT `price` FROM `PREFIX_product` WHERE `id_product` = ?");
$price->bind_param('s', $pid);
$price->execute();
$price->bind_result($pretax);
//Getting Pre Tax Price Works How It Should


//Fetch Attribute Combination ID And Wholsale Price that are linked to the specified Product ID - Obviously just an example code
$combinations = $conn->prepare("SELECT `id_product_attribute`, `wholesale_price` FROM `PREFIX_product_attribute` WHERE `id_product` = ?");
$combinations->bind_param('s', $pid);
//Not sure how to get all rows and how to handle the results, Need help with the above bit.

//We Shall Say we got the above results and we shall calculate Price Impact for the combination
$impact = ($wholsale_price + ($wholesale_price * ($percent / 100))) - $pretax; //Add percentage to wholesale price and then take away pretax price. This formula works great when I tested using single predefined values

//Lets say all $impact have been calculated for all rows, then we update the 'unit_price_impact' value for each existing row - Obviously just an example code
$update = $conn->prepare("UPDATE `PREFIX_product_attribute` SET `unit_price_impact` = ? WHERE 'id_product_attribute' = ?");
$update->bind_param('ss',$impact,$attribute_id);


//When Done Show the Number of Rows Updated
printf ("Affected rows: %d\n", $update->affected_rows);

$conn->close();
?>

If someone could help me with completing/updating this code I would be forever greatful. Thank you in advance if you can help.

[printshop]

Ok, hours of learning has meant another update. Unfortunately I am stuck on Updating the table with the selected (and then edited) data.

I have put the id_product in the statements rather than using $pid, dont ask why, everything just seems to work better. Ignore the 'print', just there to display results so I could check the right information was being retrieved and the impact price was being altered correctly.

The only thing I cant do now is update the table with the new impact price. From what I an gather i need to use a 'foreach' statement, but this is deffinitely beyond me as I have spent hours trying to understand it, I'm surprised I got this far.

Please, if someone can help I would be grateful.

<?php

$DBServer = 'SERVER'; // e.g 'localhost' or '192.168.1.100'
$DBUser = 'USER';
$DBPass = 'PASSWORD';
$DBName = 'DATABASENAME';
$percent = '35';

$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);

//Get Product Pre Tax Price
$price = "SELECT price FROM PREFIX_product WHERE id_product = 1 ORDER by id_product ASC LIMIT 0,1";
if ($presult=mysqli_query($conn,$price)) {
	while ($pricerow = mysqli_fetch_assoc($presult)) {
		$pretax = $pricerow['price'];
		//print $pretax . '<br /><br />';
	}

}

//Get Combinations
$combinations = "SELECT id_product_attribute, wholesale_price, unit_price_impact FROM PREFIX_product_attribute WHERE id_product = 1 ORDER by id_product_attribute ASC LIMIT 0,2000";
if ($cresult=mysqli_query($conn,$combinations)) {
    /* fetch associative array */
    while ($row = mysqli_fetch_assoc($cresult)) {
		$row['unit_price_impact'] = (($row['wholesale_price']) + (($row['wholesale_price']) * ($percent / 100))) - $pretax;
		//print $row['id_product_attribute'] . ', ' . $row['wholesale_price'] . ', ' . $row['unit_price_impact'] . ', ' . $pretax . '; <br />';
	}

}

$update = $conn->prepare("UPDATE PREFIX_product_attribute SET unit_price_impact = ?, WHERE id_product_attribute = ?");
$update->bind_param('ss', $row['unit_price_impact'],$row['id_product_attribute']);
//The abover bind_param not working, believe a 'foreach' statement should do the trick, unfortunately beyond my knowledge
$update->execute();
$update->close();

//printf ("Affected rows (UPDATE): %d\n", $stmt->affected_rows);

$conn->close();
?>

[printshop]

Thank you to anyone who has looked at my problem, its taken much more learning but I've completed the script by adding the upload query and its fully working!

I also descovered I had to update price impact in 2 tables and its just 'price' not 'unit_price_impact' that needed updating.

 

Thank you to everyone who took time to read this topic.