Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Please help with "Specials by category" date problem


Unikate

Recommended Posts

Hi from Germany,

I am closing my shop and trying to reduce all products by initially 25%, later by more. My "specials by category (v2.3.1)" used to work quite well, but now all of the reduced products have the wrong status (red), so that they are just being shown with the regular price. Manually changing the status works, but is not an option, as I have too many products.

I think it must have something to do with the "expires date", since the database shows the year 2017  for all products which I tried to reduce, which would explain why they are marked red and don't show up.

Could someone please tell me what I have to change in the code??
Thank you so much!

Kate

<?php
/*
  $Id$

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

  Copyright (c) 2010 osCommerce

  Released under the GNU General Public License
*/

  require('includes/application_top.php');

  require(DIR_WS_CLASSES . 'currencies.php');
  $currencies = new currencies();

  $action = (isset($HTTP_GET_VARS['action']) ? $HTTP_GET_VARS['action'] : '');

  if (tep_not_null($action)) {
    switch ($action) {
      case 'setflag':
        tep_set_specials_status($HTTP_GET_VARS['id'], $HTTP_GET_VARS['flag']);

        tep_redirect(tep_href_link(FILENAME_SPECIALS, (isset($HTTP_GET_VARS['page']) ? 'page=' . $HTTP_GET_VARS['page'] . '&' : '') . 'sID=' . $HTTP_GET_VARS['id'], 'NONSSL'));
        break;
      case 'insert':
        $products_id = tep_db_prepare_input($HTTP_POST_VARS['products_id']);
        $products_price = tep_db_prepare_input($HTTP_POST_VARS['products_price']);
        $specials_price = tep_db_prepare_input($HTTP_POST_VARS['specials_price']);
        $expdate = tep_db_prepare_input($HTTP_POST_VARS['expdate']);

        if (substr($specials_price, -1) == '%') {
          $new_special_insert_query = tep_db_query("select products_id, products_price from " . TABLE_PRODUCTS . " where products_id = '" . (int)$products_id . "'");
          $new_special_insert = tep_db_fetch_array($new_special_insert_query);

          $products_price = $new_special_insert['products_price'];
          $specials_price = ($products_price - (($specials_price / 100) * $products_price));
        }

        $expires_date = '';
        if (tep_not_null($expdate)) {
          $expires_date = substr($expdate, 0, 4) . substr($expdate, 5, 2) . substr($expdate, 8, 2);
        }

        tep_db_query("insert into " . TABLE_SPECIALS . " (products_id, specials_new_products_price, specials_date_added, expires_date, status) values ('" . (int)$products_id . "', '" . tep_db_input($specials_price) . "', now(), " . (tep_not_null($expires_date) ? "'" . tep_db_input($expires_date) . "'" : 'null') . ", '1')");

        tep_redirect(tep_href_link(FILENAME_SPECIALS, 'page=' . $HTTP_GET_VARS['page']));
        break;
      case 'update':
        $specials_id = tep_db_prepare_input($HTTP_POST_VARS['specials_id']);
        $products_price = tep_db_prepare_input($HTTP_POST_VARS['products_price']);
        $specials_price = tep_db_prepare_input($HTTP_POST_VARS['specials_price']);
        $expdate = tep_db_prepare_input($HTTP_POST_VARS['expdate']);

        if (substr($specials_price, -1) == '%') $specials_price = ($products_price - (($specials_price / 100) * $products_price));

        $expires_date = '';
        if (tep_not_null($expdate)) {
          $expires_date = substr($expdate, 0, 4) . substr($expdate, 5, 2) . substr($expdate, 8, 2);
        }

        tep_db_query("update " . TABLE_SPECIALS . " set specials_new_products_price = '" . tep_db_input($specials_price) . "', specials_last_modified = now(), expires_date = " . (tep_not_null($expires_date) ? "'" . tep_db_input($expires_date) . "'" : 'null') . " where specials_id = '" . (int)$specials_id . "'");

        tep_redirect(tep_href_link(FILENAME_SPECIALS, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $specials_id));
        break;
      case 'deleteconfirm':
        $specials_id = tep_db_prepare_input($HTTP_GET_VARS['sID']);

        tep_db_query("delete from " . TABLE_SPECIALS . " where specials_id = '" . (int)$specials_id . "'");

        tep_redirect(tep_href_link(FILENAME_SPECIALS, 'page=' . $HTTP_GET_VARS['page']));
        break;
    }
  }

  require(DIR_WS_INCLUDES . 'template_top.php');
?>
<?php
  //Fetch all variables
  $fullprice = (isset($_GET['fullprice']) ? $_GET['fullprice'] : '');
  $productid = (isset($_GET['productid']) ? (int)$_GET['productid'] : '0');
  $inputupdate = (isset($_GET['inputupdate']) ? $_GET['inputupdate'] : '');
  $categories = (isset($_GET['categories']) ? (int)$_GET['categories'] : '0');
  if (array_key_exists('discount',$_GET)) {
      if (is_numeric($_GET['discount'])) {
        $discount = (float)$_GET['discount'];
    } else {
        $discount = -1;        
    }
  } else {
      $discount = -1;
  }

  if ($fullprice == 'yes')
    tep_db_query("DELETE FROM " . TABLE_SPECIALS . " WHERE products_id=$productid;");
  else if($inputupdate == "yes"){
    $inputspecialprice = (isset($_GET['inputspecialprice']) ? $_GET['inputspecialprice'] : '');
    if (substr($inputspecialprice, -1) == '%') {
      $productprice = (isset($_GET['productprice']) ? (float)$_GET['productprice'] : '');
      $specialprice = ($productprice - (($inputspecialprice / 100) * $productprice));
    } else {
         $specialprice = $inputspecialprice;
    }
    $alreadyspecial = tep_db_query ("SELECT * FROM " . TABLE_SPECIALS . " WHERE products_id=$productid;");
    $specialproduct= tep_db_fetch_array($alreadyspecial);
    if ($specialproduct["specials_id"]){
      //print ("Database updated. Status:".$specialproduct["status"]);
      tep_db_query ("UPDATE " . TABLE_SPECIALS . " SET specials_new_products_price='$specialprice' where products_id=$productid ; ");
    } else{
      //print("New product added to specials table");
      $today = date("Y-m-d H:i:s");
      tep_db_query ("INSERT INTO " . TABLE_SPECIALS . " VALUES ('','$productid','$specialprice','$today','','','','1');");
    }
  }
?>
<form action="<?php echo $current_page; ?>" method="get">
<table><tr class="dataTableHeadingRow"><td class="dataTableHeadingContent" colspan="6">
<?php
  echo TEXT_SELECT_CAT .'&nbsp;' . tep_draw_pull_down_menu('categories', tep_get_category_tree(), $categories);
  echo TEXT_ENTER_DISCOUNT . ':&nbsp; ';
?>
<input type="text" size="4" name="discount" value="<?php
  if ($discount > 0) { echo $discount; }
  echo '">' . TEXT_PCT_AND . '&nbsp;';

?>">
<input type="submit" value="<?php echo TEXT_BUTTON_SUBMIT; ?>">
</form></td></tr>
<tr class="dataTableContent"><td class="dataTableContent" colspan="6">
<ul><li><?php echo TEXT_INSTRUCT_1; ?></li>
<li><?php echo TEXT_INSTRUCT_2; ?></li>
</ul>
</td></tr>
<?php
  if ($discount == -1) {
      //echo 'do nothing';
  } else if ($discount == 0) {
    $result2 = tep_db_query("select p.products_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES .
                            " ptc where p.products_id=ptc.products_id and ptc.categories_id=$categories");
    while ( $row = tep_db_fetch_array($result2) ){
      $allrows[] = $row["products_id"];
    }
    tep_db_query("DELETE FROM " . TABLE_SPECIALS . " WHERE products_id in ('".implode("','",$allrows)."');");
  } else if ($discount > 0) {  
    $specialprice = $discount / 100;
    $result2 = tep_db_query("select p.products_id, p.products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES .
                            " ptc where p.products_id=ptc.products_id and ptc.categories_id=$categories");
    while ( $row = tep_db_fetch_array($result2) ){
      $hello2 = $row["products_price"];
      $hello3 = $hello2 * $specialprice;
      $hello4 = $hello2 - $hello3;
      $number = $row["products_id"];
      $result3 = tep_db_query("select * from " . TABLE_SPECIALS . " where products_id = $number");
      $num_rows = tep_db_num_rows($result3);
      if ($num_rows == 0){
          //echo "Insert into specials (products_id, specials_new_products_price) values ($number, '$hello4')";
        tep_db_query("Insert into " . TABLE_SPECIALS . " (products_id, specials_new_products_price) values ($number, '$hello4'); ");
      } else {
        //echo "Update specials set specials_new_products_price='$hello4' where products_id=$number";
        tep_db_query ("Update " . TABLE_SPECIALS . " set specials_new_products_price='$hello4' where products_id=$number;");
      }
    }
  }
  print ("
            <tr class=\"dataTableHeadingRow\">
            <td class=\"dataTableHeadingContent\">". TABLE_HEADING_PRODUCTS ."</td>
            <td class=\"dataTableHeadingContent\">" . TABLE_HEADING_PRODUCTS_PRICE ."</td>
            <td class=\"dataTableHeadingContent\">" . TABLE_HEADING_SPECIAL_PRICE ."</td>
            <td class=\"dataTableHeadingContent\">" . TABLE_HEADING_PCT_OFF ."</td>
            <td class=\"dataTableHeadingContent\">" . TABLE_HEADING_FULL_PRICE . "</td>
            </tr>");
  $result2 = tep_db_query("SELECT * FROM " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES .
                          " ptc, " . TABLE_PRODUCTS . " p where pd.products_id=ptc.products_id and p.products_id=ptc.products_id
               and ptc.categories_id = $categories and pd.language_id = " .(int)$languages_id . " order by pd.products_name asc ");
  while ( $row = tep_db_fetch_array($result2) ) {
    $number = $row["products_id"];
    $result3 = tep_db_query("SELECT * FROM " . TABLE_SPECIALS . " where products_id=$number");
    $num_rows = tep_db_num_rows($result3);
    if ($num_rows == 0) {
      $specialprice = "none";
      $implieddiscount = '';
    } else {
      while ( $row2 = tep_db_fetch_array($result3) ) {
        $specialprice = $row2["specials_new_products_price"];
        if ($row["products_price"] > 0) {
          $implieddiscount = '-'.(100-(($specialprice / $row["products_price"])*100)).'%';
        } else {
            $implieddiscount = '';
        }
      }
    }
    print("<form action=\"$current_page\" method=\"get\">");
    print("
    <tr class=\"dataTableRow\" onmouseover=\"rowOverEffect(this)\" onmouseout=\"rowOutEffect(this)\" >
    <td class=\"dataTableContent\">" . $row["products_name"] . "</td>
    <td class=\"dataTableContent\">" . $row["products_price"] . "</td>
    <td class=\"dataTableContent\"><input name=\"inputspecialprice\" type=\"text\" value=\"$specialprice\"></td>
    <td class=\"dataTableContent\">$implieddiscount </td>
    <td class=\"dataTableContent\"><input type=\"checkbox\" name=\"fullprice\" value=\"yes\"></td>
    <td class=\"dataTableContent\"><input type=\"hidden\" name=\"categories\" value=\"" . $categories ."\">
    <input type=\"hidden\" name=\"productprice\" value=\"" . $row["products_price"] . "\">
    <input type=\"hidden\" name=\"productid\" value=\"" . $row["products_id"] . "\">
    <input type=\"hidden\" name=\"inputupdate\" value=\"yes\">
    <input type=\"submit\" value=\"" . TEXT_BUTTON_UPDATE . "\"></td></tr></form>");
  }
  print ("</table>");
?>
<!------------------------ Code for Specials Admin ends here --------------------------->

    </td>
  </tr>
</table>

<?php
  require(DIR_WS_INCLUDES . 'template_bottom.php');
  require(DIR_WS_INCLUDES . 'application_bottom.php');
?>

 

Link to comment
Share on other sites

Dont know the add-on but try and reset the expire date in admin!

If that is not an option do it at db level by exporting cvs and up dating date and importing.

 

or in phpAdmin run a script to update the expires-date date to null **************Please Back up before running any scripts on db*****************

ALTER TABLE specials
  MODIFY( expires_date DATE NULL )

 

 

Link to comment
Share on other sites

I wouldn't use ALTER, as that's for changing the structure of the table. Something like

UPDATE specials SET expires_date = 2000000000 

(where a timestamp of 2 billion is well in the future) might work. Of course, back up your database first.

Link to comment
Share on other sites

My point was that ALTER is for changing the structure of a table, while UPDATE is for changing the data in the table. If NULL is permitted as a value, and the code understands it as "value not set" and not as a 0 or something, then setting the timestamp to NULL could work.

My guess as to why it is 2017 is that is the last time a special was set for most products. Perhaps it would even be easier not to do it as a "special", but simply UPDATE each product price to 0.75 * the old price, but that might have to be done product-by-product. Easy Populate?

Link to comment
Share on other sites

Hey, JcMagpie and MrPhil,

Thanks for your replies. Unfortunately I cannot reset the date in admin, and I don't know whether I will dare to run any scripts - it's been a long, long time since my last "active" encounter with php et al. I will probably have to go with exporting and re-importing; I  had just hoped that there might be an easier way.

As regards backup of databases - I used to use mysqldumper but that was shut down and o longer works; is there any similar, easy to use and free program that replaces the dumper?

Kate

Link to comment
Share on other sites

MrPhil - I haven't used easypopulate since I last had to reinstall my shop due to a change by my host (I think it was some php version that my old shop was not compatible with?) and it also tells me to backup my database first. How do I do that without my dumper? I seem to remember that there was something about the database backup included with oscommerce that made it not recommendable, but that may have been the old version.  I just have the feeling that the backup is finished much too fast, the dumper needed quite a bit longer?

 

Link to comment
Share on other sites

Sorry for sounding so dumb - I have had my shop since 2005 and reinstalled it in 2016 but haven't really had it open much for personal reasons since that last reinstall- that's why I have forgotten everything I thought I once knew ;-)))

Link to comment
Share on other sites

It's an ordinary MySQL database, so all sorts of Third Party DB backup/restore programs should work. Have you asked your host for any recommendations? If something doesn't work because it times out (runs too long), they may be able to tell you how to get around that limit, or will even occasionally do it for you as a favor. Even phpMyAdmin, or whatever you have similar to that, has a backup function (Export), but that may still be subject to process time limits.

I don't know how good osCommerce's built-in backup is. Typically, backup functions added by application developers aren't quite as good as dedicated backup/restore programs, but some I've seen are atrocious (e.g., SMF).

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...