Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Tiered Pricing Automation
 
kimchi209
Member
 
Total Posts:  67
Joined:  2008-12-22
 

We are moving from OSCommerce to Magento and are in the process of getting the data from OSC to Mag. We used the import tool, but have many products that need to come over that didn’t. So we have two data entry clerks working on entering the data from our catalogs to the new magento site before we go live.

In the meantime we are trying to figure out the best way to enter the prices. We do have four tiered pricing on our products based on quantity. I know that we can enter those amounts for each product, but we were trying to find a way to enter in one price and have the other prices calculate automatically to save time for our data entry clerks having to calculate each product out themself.

Any ideas on how I could implement this?

Here is an idea I came up with. After reviewing the sql data I think this might work. Let me know if you have any thoughts.

If we entered all the data in for quantity one pricing and then ran this command on the db I think it should add all the additional fields that we need. Any thoughts?

<?

    $query1 
"SELECT * FROM Catalog_product_entity_decimal WHERE attribute_id='60' ";
    
$result mysql_query($query1)
        or die (
"Couldn't execute query.");
    
$num_rows mysql_num_rows($result);  

    IF (
$num_rows=="0"){
        
echo    "NONE From Query 1.";
}
    
ELSE {
    
    

WHILE ($row mysql_fetch_array($result))

{
    extract
($row);

$basevalue $value
$fivevalue 
$basevalue .90
$tenvalue 
$basevalue .85
$twentyvalue 
$basevalue .82
$fiftyvalue 
$basevalue .77

UPDATE catalog_product_entity_tier_price SET value
=$fivevalue WHERE qty=AND $entity_id='$entity_id'
UPDATE catalog_product_entity_tier_price SET value=$tenvalue WHERE qty=10 AND $entity_id='$entity_id'
UPDATE catalog_product_entity_tier_price SET value=$twentyvalue WHERE qty=20 AND $entity_id='$entity_id'
UPDATE catalog_product_entity_tier_price SET value=$fiftyvalue WHERE qty=50 AND $entity_id='$entity_id'

}

?>

Do you think this could work?

 
Magento Community Magento Community
Magento Community
Magento Community
 
kimchi209
Member
 
Total Posts:  67
Joined:  2008-12-22
 

OK - i got it to work. What I did was created a php file in the root and ran it… see below for the file to use this for your own purposes

<?


$dbh
=mysql_connect ("localhost""dbuser""dbpass") or die ('I cannot connect to the database because: ' mysql_error());
mysql_select_db ("yourdb"); 



echo 
"About to start query 1<br>";

    
$query1 "SELECT * FROM catalog_product_entity_decimal WHERE attribute_id='60' ";
    
$result mysql_query($query1)
        or die (
"Couldn't execute query.");
    
$num_rows mysql_num_rows($result);  

    IF (
$num_rows=="0"){
        
echo    "NONE From Query 1.";
}
    
ELSE {
    
echo "Got Query, about to start while statement<br>";    

WHILE (
$row mysql_fetch_array($result))

{
    extract
($row);
echo 
"Extracted Row about to declare variables<br>";

$part $entity_id;

$basevalue $value;

$cost $basevalue .5;

$fivevalue $cost .55;
$tenvalue $cost .59;
$twentyvalue $cost .61;
$fiftyvalue $cost .65;

echo 
"Part - $part<br>Base - $basevalue<br> Cost - $cost<br>5 - $fivevalue<br>10 - $tenvalue<br>20 - $twentyvalue<br>50 - $fiftyvalue <br>";


echo 
"Ran calculations about to post variables to db<br>";

    
$queryt "TRUNCATE TABLE `catalog_product_entity_tier_price`  ";
    
$resultt mysql_query($queryt)
        or die (
"Couldn't execute queryt.<br>");

    
$querya "INSERT INTO catalog_product_entity_tier_price (value, entity_id, qty, website_id) Values ('$fivevalue', '$part', '5', '0')";
    
$resulta mysql_query($querya)
        or die (
"Couldn't execute querya.<br>");
        
    
$queryb "INSERT INTO catalog_product_entity_tier_price (value, entity_id, qty, website_id) Values ('$tenvalue', '$part', '10', '0')";
    
$resultb mysql_query($queryb)
        or die (
"Couldn't execute queryb.<br>");
        
    
$queryc "INSERT INTO catalog_product_entity_tier_price (value, entity_id, qty, website_id) Values ('$twentyvalue', '$part', '20', '0')";
    
$resultc mysql_query($queryc)
        or die (
"Couldn't execute queryc.<br>");
    
    
$queryd "INSERT INTO catalog_product_entity_tier_price (value, entity_id, qty, website_id) Values ('$fiftyvalue', '$part', '50', '0')";
    
$resultd mysql_query($queryd)
        or die (
"Couldn't execute queryd.<br>");

}

echo "<b>Completed $num_rows products</b><br><br><br>";
}
?>
 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top