Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Price Updater


kymation

Recommended Posts

Please post all support requests for this contribution here (since I seem to be missing some folks' requests - sorry.)

 

The contribution is here.

 

Regards

Jim

See my profile for a list of my addons and ways to get support.

Link to comment
Share on other sites

thx for the new thread jim!

 

ok, onto my question! :D

 

is there an easy way to make this effect all attribute prices as well? not just the main product price, but the 7 attributes w/ additional dollar amounts as well?

 

thx!

Link to comment
Share on other sites

Not easily. The attribute prices are stored separately and are dynamically linked to the products. Changes to these values will also show up on any other products that they have been assigned to.

 

If you have only seven attribute prices, I would suggest changing them manually. If you have hundreds of them, then it might be worth making an updater for attributes. This would probably have to be a separate tool, or at least a separate page on this one.

 

Regards

Jim

See my profile for a list of my addons and ways to get support.

Link to comment
Share on other sites

gotcha.

 

i believe 99.9% of my products have seperate prices, there may be a couple that are shared. but i'm dealing w/ 5k products w/.. geez.. 7k attributes?

 

lemme check...

 

ahh, close guess, 5129 products, 7445 attributes! :D

 

but i do see why it was not included w/ the price updater code. makes sence w/ the shared attributes.

 

-Who Dah?

Edited by whodah
Link to comment
Share on other sites

hey, wait a tic...

 

ok, if you had 13 things assigned to one attribute called:

Blue rings

 

and you changed 'Blue rings', it would show up on all 13 items.

 

but the prices are uniquely assigned in products_attributes, right?

 

thus if you were to change the price of 1 of those 13 'Blue rings' it would not effect the price of the other 12.

 

as this is a 'price updater' and not an attribute editor, it would still be feasible wouldn't it?

 

lemme know if i'm overlooking something. thx!

-Who Dah?

Link to comment
Share on other sites

ok, so yer code is obviously modifying prices in table 'products' on the field 'products_price'.

 

from that relationship, get products_id (which i bet you already are? dunno..)

 

then, in table products_attributes

 

UPDATE products_attributes SET options_values_price = <original price +/- price updater price> WHERE products_id='products_id'

 

at that point, one doesn't even care about the verbage description of the attribute, just the price.

 

thoughts?

 

-Who Dah?

 

PS: thx for even entertaining this conversation! :D

Link to comment
Share on other sites

ok, ignoring a checkbox in the config to maybe 'include attributes in price change' or something, do you think it's this 'simple'?

 

 

in price_updater.php, the while loop that does the update around line 153...

 

a second while loop (nested in yours) which fundamentally does this:

select * from TABLE_PRODUCTS_ATTRIBUTES where products_id = $products_update['id']

while <above query>{
 <code that takes the price and add/subtract/multiply price difference>

 <query that updates it similar to line 167 of yours>
}

Link to comment
Share on other sites

heya!

 

ty VERY much for the module!

 

i've modified the price_updater.php file to update the price of the attributes as well (if you want to, i've added a checkbox option to the menu to optionally update attributes).

 

if an attribute has a '-' price_prefix, then the OPPOSITE is done. i.e. if you add $1 to every product and include attributes and have an option of:

- $6

 

then it will update that price to:

- $5

 

not to

- $7

 

i don't use '-' myself, but felt that this is how it should be treated? dunno, easy enough to change in the code if not.

 

at any rate, i dind't make a contribution, if you'd like to kymation, by all means, go for it! :)

 

typical warning:

** do at yer own risk, i take no responsibility, blah blah blah **

 

and here it is:

 

price_updater.php w/ optional price attribute price updater

<?php
/*
 $Id: price_updater.php,v 1.1 2004/04/18 jck Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2004 osCommerce

 Released under the GNU General Public License
*/

 require('includes/application_top.php');

// Functions to fill the dropdown boxes
 function tep_get_manufacturers($manufacturers_array = '') { // Function borrowed from the Catalog side
   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;
 }

 function tep_get_categories($categories_array = '') { // Function modified from tep_get_manufacturers()
   global $language;
   if (!is_array($categories_array)) $categories_array = array();
   $categories_query = tep_db_query("SELECT categories_id, 
                                         categories_name
                                     FROM " . TABLE_CATEGORIES_DESCRIPTION . " cd, 
                                          " . TABLE_LANGUAGES . " l 
          WHERE l.languages_id = cd.language_id
            AND l.name = '" . $language . "' 
          ORDER BY categories_name"
      	 );
   while ($categories = tep_db_fetch_array($categories_query)) {
     $categories_array[] = array('id' => $categories['categories_id'], 'text' => $categories['categories_name']);
   }
   return $categories_array;
 }
 

 function tep_get_models($models_array = '') { // Function modified from tep_get_manufacturers()
   global $language, $first, $last;
   if (!is_array($models_array)) $models_array = array();
   $models_query = tep_db_query("SELECT products_id,
                                     products_model 
                                 FROM " . TABLE_PRODUCTS . " 
                                 ORDER BY products_model"
       );
   $count = 0;
   while ($models = tep_db_fetch_array($models_query)) {
  if ($count == 0) {
    $first = $models['products_model'];  
  }
     $models_array[] = array('id' => $models['products_model'], 'text' => $models['products_model']);
  $count++;
  $last = $models['products_model'];
   }

   return $models_array;
 }


 $models_array = tep_get_models();
 $from = $first;
 $to = $last;
 

// Process the request data
 if (isset($_GET['action']) && $_GET['action'] == 'update') {

// Get the data from the form and sanitize it
   if (isset($_POST['manufacturers_id'])) {
     $mfr = (int)$_POST['manufacturers_id']; 
   } else {
     $mfr = 0;
   }

   if (isset($_POST['categories_id'])) {
     $cat = (int)$_POST['categories_id']; 
   } else {
     $cat = 0;
   }

   if (isset($_POST['from_id'])) {
     $from = $_POST['from_id']; 
   }

   if (isset($_POST['to_id'])) {
     $to = $_POST['to_id']; 
   }

   if (isset($_POST['like'])) {
     $like = $_POST['like']; 
   } else {
     $like = '';
   }

   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;
   }


// Set the SQL where function 
   if ($mfr == 0) {
  if ($cat == 0) {
       $where_string = '';
  } else {
       $where_string = ' AND pcat.categories_id = ' . $cat;
  }
   } else {
     $where_string = ' AND manufacturers_id=' . $mfr; 
  if ($cat != 0) {
       $where_string .= ' AND pcat.categories_id = ' . $cat;
  }
   }

   if ($like == '') {
  if ($from != $first) {
       $where_string .= " AND p.products_model >= '" . $from . "'"; 
  }
  if ($to != $last) {
       $where_string .= " AND p.products_model <= '" . $to . "'";
     } 
   } else {
     $where_string .= " AND p.products_model LIKE '" . $like . "'";
}
 
// Query to get the selected products and make the changes
   $products_update_query = tep_db_query('SELECT p.products_id AS id, 
                                                 p.products_price AS price 
                                          FROM ' . TABLE_PRODUCTS . ' p, ' . 
                                                   TABLE_PRODUCTS_TO_CATEGORIES . ' pcat 
                                          WHERE p.products_id = pcat.products_id' . 
                                          $where_string
                                        );
   $count = 0;
 $count_attr = 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'] . "'"
                 );
	 if ($update_attributes) {
    // main product updated at this point, now lets optionally update the attributes!
   $attributes_update_query = tep_db_query('SELECT *
                                                FROM ' . TABLE_PRODUCTS_ATTRIBUTES . ' 
                        where products_id = '. $products_update['id']);
   while ($attributes_update = tep_db_fetch_array($attributes_update_query)) {
     // add/sub/percent price code
  	 // update query
  	 if($attributes_update[options_values_price]==0) {
      $new_attr_price = $attributes_update['options_values_price'];
  	 }
  	 elseif ($attributes_update[price_prefix]=="-") { // need to reverse sign logic...
      if ($fixed == 0) {  // Fixed price change
            if ($add == 0) {  // Subtract
               $new_attr_price = $attributes_update['options_values_price'] + $value;
            } else {  // Add
               $new_attr_price = $attributes_update['options_values_price'] - $value;
             }  
           } else {  // Percent change
            if ($add == 0) {  // Subtract
               $new_attr_price = $attributes_update['options_values_price'] * (1 + ($value / 100));
            } else {  // Add
               $new_attr_price = $attributes_update['options_values_price'] * (1 - ($value / 100));
             }  
          }
  	 }
  	 else {
      if ($fixed == 0) {  // Fixed price change
            if ($add == 0) {  // Subtract
               $new_attr_price = $attributes_update['options_values_price'] - $value;
            } else {  // Add
               $new_attr_price = $attributes_update['options_values_price'] + $value;
             }  
           } else {  // Percent change
            if ($add == 0) {  // Subtract
               $new_attr_price = $attributes_update['options_values_price'] * (1 - ($value / 100));
            } else {  // Add
               $new_attr_price = $attributes_update['options_values_price'] * (1 + ($value / 100));
             }  
          }
  	 }
  	 tep_db_query("UPDATE " . TABLE_PRODUCTS_ATTRIBUTES . " 
                SET options_values_price='" . $new_attr_price . "' 
                   WHERE products_attributes_id='" . $attributes_update['products_attributes_id'] . "'");
  	 $count_attr++;
   }
	 }
     $count++;
   }  // Products while loop


// If a manufacturer was selected, get the name
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 = TEXT_ALL_MANUFACTURERS;
   }


// If a category was selected, get the name
   if ($cat != 0) {
     $categories_query = tep_db_query("SELECT cd.categories_name
                                       FROM " . TABLE_CATEGORIES_DESCRIPTION . " cd, 
                                            " . TABLE_LANGUAGES . " l 
            WHERE l.languages_id = cd.language_id
              AND l.name = '" . $language . "' 
              AND categories_id = " . $cat);
     $categories = tep_db_fetch_array($categories_query);
     $category = TEXT_THE . $categories['categories_name'] . TEXT_CATEGORY;
   } else {
     $category = TEXT_ALL_CATEGORIES;
   }


// Finish the rest of the update text information
   $fixed_string = '';
   if ($fixed == 1) {
     $fixed_string = TEXT_PERCENT;
   }

   $add_string = TEXT_DECREASED_BY;
   if ($add == 1) {
   $add_string = TEXT_INCREASED_BY;
   }

   $update_string = $manufacturer . TEXT_PRICES_IN . $category . TEXT_WERE . $add_string . $value . $fixed_string;

 } // End 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>
           <table border=0 width=700 cellspacing=0 cellpadding=5>
             <tr class="dataTableHeadingRow">
               <td class="dataTableHeadingContent"> <?php echo TABLE_HEADING_UPDATES; ?> </td>
             </tr>
             <tr class="dataTableRow">
               <td class="dataTableContent">
<?php
  if (isset($count) && $count != 0) {
   echo $update_string;
 }
?>
               </td>
             </tr>
             <tr class="dataTableRow">
               <td class="dataTableContent">
<?php
 if (isset($count) && $count != 0) {
   echo $count;
 } else {
   echo TEXT_NO;
 }
 echo TEXT_PRODUCTS_UPDATED;
if (isset($count) && $count != 0) {
   echo " ($count_attr attributes were updated)";
 } 
?>
   </td>
             </tr>
           </table>
       </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>
           <table border=0 width=700 cellspacing=0 cellpadding=2>
             <tr>
               <td colspan=7><?php echo tep_black_line(); ?></td>
             </tr>
             <tr class="dataTableHeadingRow">
               <td class="dataTableHeadingContent"> <?php echo TABLE_HEADING_MANUFACTURER; ?> </td>
               <td class="dataTableHeadingContent"> <?php echo TABLE_HEADING_CATEGORY; ?> </td>
       <td class="dataTableHeadingContent"> Update<br>Attributes </td>
       <!--<td colspan=2 class="dataTableHeadingContent"> <?php echo TABLE_HEADING_CATEGORY; ?> </td>-->
               <td class="dataTableHeadingContent"> <?php echo TABLE_HEADING_PLUS_MINUS; ?> </td>
               <td class="dataTableHeadingContent"> <?php echo TABLE_HEADING_FIXED; ?> </td>
               <td class="dataTableHeadingContent"> <?php echo TABLE_HEADING_VALUE; ?> </td>
               <td class="dataTableHeadingContent"> <?php echo TABLE_HEADING_ACTION; ?> </td>
             </tr>
             <tr>
               <td colspan=7><?php echo tep_black_line(); ?></td>
             </tr>
             <tr class=attributes-odd>
               <td class="smallText"> <?php echo tep_draw_pull_down_menu('manufacturers_id', tep_get_manufacturers(array(array('id' => '0', 'text' => 'All Manufacturers ')))); ?> </td>
               <td class="smallText"> <?php echo tep_draw_pull_down_menu('categories_id', tep_get_categories(array(array('id' => '0', 'text' => 'All Categories ')))); ?> </td>
       <td class="smallText"> <?php echo tep_draw_checkbox_field('update_attributes', '1', false);?> </td>
               <!--<td colspan=2 class="smallText"> <?php echo tep_draw_pull_down_menu('categories_id', tep_get_categories(array(array('id' => '0', 'text' => 'All Categories ')))); ?> </td>-->
               <td class="smallText"> <?php echo tep_draw_pull_down_menu('add', array(array('id' => '1', 'text' => '+'), array('id' => '0', 'text' => '-'))); ?> </td>
               <td class="smallText"> <?php echo tep_draw_pull_down_menu('fixed', array(array('id' => '1', 'text' => '%'), array('id' => '0', 'text' => 'Fixed'))); ?> </td>
               <td class="smallText"> <?php echo tep_draw_input_field('value', "0"); ?> </td>
               <td class="smallText"> <?php echo tep_image_submit('button_update.gif', IMAGE_UPDATE); ?> </td>
             </tr>
             <tr>
               <td colspan=7><?php echo tep_black_line(); ?></td>
             </tr>
             <tr>
               <td colspan=7><?php echo tep_draw_separator('pixel_trans.gif', '100%', '5'); ?></td>
             </tr>
             <tr>
               <td colspan=7><?php echo tep_black_line(); ?></td>
             </tr>
             <tr class="dataTableHeadingRow">
               <td colspan=7 class="dataTableHeadingContent"> <?php echo TABLE_HEADING_MODEL; ?> </td>
             </tr>
             <tr>
               <td colspan=7><?php echo tep_black_line(); ?></td>
             </tr>
             <tr class=attributes-odd>
               <td class="smallText"> <?php echo tep_draw_pull_down_menu('from_id', $models_array, $from); ?>   <?php echo TEXT_TO ?> </td>
               <td class="smallText"> <?php echo tep_draw_pull_down_menu('to_id', $models_array, $to); ?> </td>
               <td class="smallText"> Like: </td>
               <td colspan=4 class="smallText"> <?php echo tep_draw_input_field('like', ""); ?>   <?php echo TEXT_NOTES ?> </td>
             </tr>
             <tr>
               <td colspan=7><?php echo tep_black_line(); ?></td>
             </tr>
           </table>
         </form>
       </td>
     </tr>
   </table></td>
 </tr>
</table>

</body>

</html>

 

ggs!

Link to comment
Share on other sites

Looks good! I'm going to go play with this a bit. I'm still not certain that it will work in every case, say when an attribute is used for multiple products and some don't need to be changed. I could be wrong here....

 

Regards

Jim

See my profile for a list of my addons and ways to get support.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...