Manufacturer price increase by percentage

[Chance]

This may be already out there- I searched the contributions and didn't really see something I could adapt- so someone point me in the right direction if I missed something please.

 

About every six months my manufacturers do across the board price updates- say Brand A will do a 5% price increase across the board, and Brand B will do a 7% price increase. Likewise, Brand C may do a 3% price decrease.

 

I need a contribution (or ideas on how to do it myself) where I can specify price increase (either in percentage, or dollar ammount much like special pricing now) per manufacturer. The way it is now, I have about 300 products for some manufacturers alone, so price updates eat up a huge ammount of my time.

 

Thoughts?

[♥kymation]

This contribution looks like it might do the job. I haven't installed it yet, so I could be wrong. I'm going to need somthing like this in a couple of months, so I'd have to make one if this doesn't work.

 

It shouldn't be too hard to pull all of the products from a given manufacturer and change the price by a specified percentage/amount. I need to brush up on my SQL coding anyway.

 

Regards

Jim

[♥ecartz]

Backup before you try, but something like

update products set products_price = products_price * 1.05 where manufacturers_id = '19';

should increase the price by 5% for all products made by the manufacturer whose manufacturers_id is 19.

 

Hth,

Matt

[♥kymation]

Thanks Matt. That was about what I had in mind. The Contribution that I mentioned above didn't do quite what I wanted, so I wrote something that did. Needless to say, it got a bit more complex that Matt's elegant SQL. Here's the result:

  require('includes/application_top.php');

 function tep_get_manufacturers($manufacturers_array = '') {
   if (!is_array($manufacturers_array)) $manufacturers_array = array();
   $manufacturers_query = tep_db_query("select manufacturers_id, manufacturers_name from " . TABLE_MANUFACTURERS . " order by manufacturers_name");
   while ($manufacturers = tep_db_fetch_array($manufacturers_query)) {
     $manufacturers_array[] = array('id' => $manufacturers['manufacturers_id'], 'text' => $manufacturers['manufacturers_name']);
   }
   return $manufacturers_array;
 }
 
 if (isset($_GET['action']) && $_GET['action'] == 'update') {
   if (isset($_POST['manufacturers_id'])) {
     $mfr = (int)$_POST['manufacturers_id']; 
   } else {
     $mfr = 0;
   }

   if (isset($_POST['add'])) {
     $add = (int)$_POST['add']; 
   } else {
     $add = 1;
   }

   if (isset($_POST['fixed'])) {
     $fixed = (int)$_POST['fixed']; 
   } else {
     $fixed = 1;
   }

   if (isset($_POST['value'])) {
      $value = ereg_replace('[^0-9.]', '', $_POST['value']); 
   } else {
     $value = 0;
   }
 
   if ($mfr == 0) {
     $where_string = ''; 
   } else {
     $where_string = 'WHERE manufacturers_id=' . $mfr; 
   }
 
   $products_update_query = tep_db_query('SELECT products_id AS id, 
                                                 products_price AS price 
                                          FROM ' . TABLE_PRODUCTS . ' ' . 
                                          $where_string
                                        );
   $count = 0;
   while ($products_update = tep_db_fetch_array($products_update_query)) {
     if ($fixed == 0) {  // Fixed price change
    if ($add == 0) {  // Subtract
         $new_price = $products_update['price'] - $value;
    } else {  // Add
         $new_price = $products_update['price'] + $value;
       }  
     } else {  // Percent change
    if ($add == 0) {  // Subtract
         $new_price = $products_update['price'] * (1 - ($value / 100));
    } else {  // Add
         $new_price = $products_update['price'] * (1 + ($value / 100));
       }  
  }
     tep_db_query("UPDATE " . TABLE_PRODUCTS . " 
                SET products_price='" . $new_price . "' 
                   WHERE products_id='" . $products_update['id'] . "'"
                 );
     $count++;
   }  // Products while loop
if ($mfr != 0) {
  $manufacturers_query = tep_db_query("SELECT manufacturers_name FROM " . TABLE_MANUFACTURERS . " WHERE manufacturers_id=" . $mfr);
     $manufacturers = tep_db_fetch_array($manufacturers_query);
     $manufacturer = $manufacturers['manufacturers_name'];
   } else {
     $manufacturer = 'All manufacturers';
   }
   $fixed_string = '';
   if ($fixed == 1) {
     $fixed_string = '% ';
   }
   $add_string = ' decreased by ';
   if ($add == 1) {
   $add_string = ' increased by ';
   }
   $update_string = $manufacturer . ' prices were' . $add_string . $value . $fixed_string;

 } // Action=update

 

?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<script language="javascript" src="includes/general.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF" onload="SetFocus();">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
   </table></td>
<!-- body_text //-->
   <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
     <tr>
       <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tr>
           <td class="pageHeading">Product Price Updater</td>
           <td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
         </tr>
       </table></td>
     </tr>
     <tr>
       <td>
<?php 
 if (isset($count) && $count != 0) {
   echo $update_string . '</td></tr>';
   echo '<tr><td>' . $count . ' products were updated. ';
 } else {
   echo 'No products were updated. ';
 }
  
?>
       </td>
     </tr>
     <tr>
       <td><?php echo tep_draw_separator('pixel_trans.gif', "100%", 20); ?></td>
     </tr>
     <tr>
       <td>
         <form action="price_updater.php?action=update" method=post>
<?php 
 echo tep_draw_pull_down_menu('manufacturers_id', tep_get_manufacturers(array(array('id' => '0', 'text' => 'All Manufacturers '))));
 echo ' ';
 echo tep_draw_pull_down_menu('add', array(array('id' => '1', 'text' => '+'), array('id' => '0', 'text' => '-'))); 
 echo ' ';
 echo tep_draw_pull_down_menu('fixed', array(array('id' => '1', 'text' => '%'), array('id' => '0', 'text' => 'Fixed'))); 
 echo ' ';
 echo tep_draw_input_field('value', "0"); 
?>
            <input type="submit" value="Update Prices">
         </form>
       </td>
     </tr>
   </table></td>
 </tr>
</table>

</body>

</html>

Save that as price_updater.php and put it in your Admin directory. A link to it from one of the boxes (maybe Catalog) will make it easier to get to.

 

I've tested this code on my development box and it appears to work as expected. However, please back up before you try it. I zeroed all of the prices on my test box when I was debugging this. I found that bug, but I give no guarantees on any others. Caveat emptor.

 

Regards

Jim

[Chance]

Matt-

 

Yeah, thats how I was doing it now, but I am trying to stay away from pure SQL commands in case I have to turn this project over one day. Thanks though, thats the first thing I thought of and it does work well.

 

Jim-

 

I will try this out on my development server, and if I can but test it enough I might work on it a bit and talk to you about making a contrib. Its something that osC really needs, more so for larger merchants and resellers.

[♥kymation]

I cleaned up the code a bit and made it into a Contribution. It now looks more like part of the Admin. Thanks to everyone who made comments and suggestions.

 

http://www.oscommerce.com/community/contributions,2014

 

Regards

Jim