RE: Programmable Specials (ie. Start & End Date for Specials

This message was sent from: Suggestions and Proposals
http://forums.oscommerce.com/viewtopic.php?p=199975#199975
----------------------------------------------------------------

I did exactly that as I was in need to be able to schedule specials and i was really missing this function, but the drawback is now that you might enable and disable sepcials in admin, but if there valid date is like the current date they will automatically start and expire.

I did expand the table specials for the field start_date

[quote]ALTER TABLE `specials` ADD `start_date` DATETIME DEFAULT NULL[/quote]

Further I've changed specials.php in the admin section, see changes below, you'll find the corresponding lines easily

[code]    case 'insert':
// insert a product on special
      if (substr($HTTP_POST_VARS['specials_price'], -1) == '%') {
        $new_special_insert_query = tep_db_query("select products_id, products_price from " . TABLE_PRODUCTS . " where products_id = '" . $HTTP_POST_VARS['products_id'] . "'");
        $new_special_insert = tep_db_fetch_array($new_special_insert_query);
        $HTTP_POST_VARS['products_price'] = $new_special_insert['products_price'];
        $HTTP_POST_VARS['specials_price'] = ($HTTP_POST_VARS['products_price'] - (($HTTP_POST_VARS['specials_price'] / 100) * $HTTP_POST_VARS['products_price']));
      }	

      $start_date = '';
      if ($HTTP_POST_VARS['s_day'] && $HTTP_POST_VARS['s_month'] && $HTTP_POST_VARS['s_year']) {
        $start_date = $HTTP_POST_VARS['s_year'];
        $start_date .= (strlen($HTTP_POST_VARS['s_month']) == 1) ? '0' . $HTTP_POST_VARS['s_month'] : $HTTP_POST_VARS['s_month'];
        $start_date .= (strlen($HTTP_POST_VARS['s_day']) == 1) ? '0' . $HTTP_POST_VARS['s_day'] : $HTTP_POST_VARS['s_day'];
      }

      $expires_date = '';
      if ($HTTP_POST_VARS['e_day'] && $HTTP_POST_VARS['e_month'] && $HTTP_POST_VARS['e_year']) {
        $expires_date = $HTTP_POST_VARS['e_year'];
        $expires_date .= (strlen($HTTP_POST_VARS['e_month']) == 1) ? '0' . $HTTP_POST_VARS['e_month'] : $HTTP_POST_VARS['e_month'];
        $expires_date .= (strlen($HTTP_POST_VARS['e_day']) == 1) ? '0' . $HTTP_POST_VARS['e_day'] : $HTTP_POST_VARS['e_day'];
      }

      tep_db_query("insert into " . TABLE_SPECIALS . " (products_id, specials_new_products_price, specials_date_added, start_date, expires_date, status) values ('" . $HTTP_POST_VARS['products_id'] . "', '" . $HTTP_POST_VARS['specials_price'] . "', now(), '" . $start_date . "', '" . $expires_date . "', '1')");
      tep_redirect(tep_href_link(FILENAME_SPECIALS, 'page=' . $HTTP_GET_VARS['page']));
      break;

///
//
    case 'update':
// update a product on special
      if (substr($HTTP_POST_VARS['specials_price'], -1) == '%') $HTTP_POST_VARS['specials_price'] = ($HTTP_POST_VARS['products_price'] - (($HTTP_POST_VARS['specials_price'] / 100) * $HTTP_POST_VARS['products_price']));

      $start_date = '';
      if ($HTTP_POST_VARS['s_day'] && $HTTP_POST_VARS['s_month'] && $HTTP_POST_VARS['s_year']) {
        $start_date = $HTTP_POST_VARS['s_year'];
        $start_date .= (strlen($HTTP_POST_VARS['s_month']) == 1) ? '0' . $HTTP_POST_VARS['s_month'] : $HTTP_POST_VARS['s_month'];
        $start_date .= (strlen($HTTP_POST_VARS['s_day']) == 1) ? '0' . $HTTP_POST_VARS['s_day'] : $HTTP_POST_VARS['s_day'];
      }

      $expires_date = '';
      if ($HTTP_POST_VARS['e_day'] && $HTTP_POST_VARS['e_month'] && $HTTP_POST_VARS['e_year']) {
        $expires_date = $HTTP_POST_VARS['e_year'];
        $expires_date .= (strlen($HTTP_POST_VARS['e_month']) == 1) ? '0' . $HTTP_POST_VARS['e_month'] : $HTTP_POST_VARS['e_month'];
        $expires_date .= (strlen($HTTP_POST_VARS['e_day']) == 1) ? '0' . $HTTP_POST_VARS['e_day'] : $HTTP_POST_VARS['e_day'];
      }

      tep_db_query("update " . TABLE_SPECIALS . " set specials_new_products_price = '" . $HTTP_POST_VARS['specials_price'] . "', specials_last_modified = now(), start_date = '" . $start_date . "', expires_date = '" . $expires_date . "' where specials_id = '" . $HTTP_POST_VARS['specials_id'] . "'");
      tep_redirect(tep_href_link(FILENAME_SPECIALS, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $specials_id));
      break;

///
//[/code]

and later in the same file 
[code]
  if ( ($HTTP_GET_VARS['action'] == 'new') || ($HTTP_GET_VARS['action'] == 'edit') ) {
    $form_action = 'insert';
    if ( ($HTTP_GET_VARS['action'] == 'edit') && ($HTTP_GET_VARS['sID']) ) {
	  $form_action = 'update';

      $product_query = tep_db_query("select p.products_id, pd.products_name, p.products_price, s.specials_new_products_price, s.start_date, s.expires_date from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and p.products_id = s.products_id and s.specials_id = '" . $HTTP_GET_VARS['sID'] . "'");
      $product = tep_db_fetch_array($product_query);

      $sInfo = new objectInfo($product);
    } else {
      $sInfo = new objectInfo(array());

// create an array of products on special, which will be excluded from the pull down menu of products
// (when creating a new product on special)
      $specials_array = array();
      $specials_query = tep_db_query("select p.products_id from " . TABLE_PRODUCTS . " p, " . TABLE_SPECIALS . " s where s.products_id = p.products_id");
      while ($specials = tep_db_fetch_array($specials_query)) {
        $specials_array[] = $specials['products_id'];
      }
    }
?>
      <tr><form name="new_special" <?php echo 'action="' . tep_href_link(FILENAME_SPECIALS, tep_get_all_get_params(array('action', 'info', 'sID')) . 'action=' . $form_action, 'NONSSL') . '"'; ?> method="post"><?php if ($form_action == 'update') echo tep_draw_hidden_field('specials_id', $HTTP_GET_VARS['sID']); ?>
        <td><br><table border="0" cellspacing="0" cellpadding="2">
          <tr>
            <td class="main"><?php echo TEXT_SPECIALS_PRODUCT; ?>&nbsp;</td>
            <td class="main"><?php echo ($sInfo->products_name) ? $sInfo->products_name . ' <small>(' . $currencies->format($sInfo->products_price) . ')</small>' : tep_draw_products_pull_down('products_id', 'style="font-size:10px"', $specials_array); echo tep_draw_hidden_field('products_price', $sInfo->products_price); ?></td>
          </tr>
          <tr>
            <td class="main"><?php echo TEXT_SPECIALS_SPECIAL_PRICE; ?>&nbsp;</td>
            <td class="main"><?php echo tep_draw_input_field('specials_price', $sInfo->specials_new_products_price); ?></td>
          </tr>
          <tr>
            <td class="main"><?php echo TEXT_SPECIALS_START_DATE; ?>&nbsp;</td>
            <td class="main"><?php echo tep_draw_input_field('s_day', substr($sInfo->start_date, 8, 2), 'size="2" maxlength="2" class="cal-TextBox"') . tep_draw_input_field('s_month', substr($sInfo->start_date, 5, 2), 'size="2" maxlength="2" class="cal-TextBox"') . tep_draw_input_field('s_year', substr($sInfo->start_date, 0, 4), 'size="4" maxlength="4" class="cal-TextBox"'); ?><a class="so-BtnLink" href="javascript:calClick();return false;" onmouseover="calSwapImg('BTN_s_date', 'img_Date_OVER',true);" onmouseout="calSwapImg('BTN_s_date', 'img_Date_UP',true);" onclick="calSwapImg('BTN_s_date', 'img_Date_DOWN');showCalendar('new_special','s_','BTN_s_date');return false;"><?php echo tep_image(DIR_WS_IMAGES . 'cal_date_up.gif', 'Calendar', '22', '17', 'align="absmiddle" name="BTN_s_date"'); ?></a></td>
          </tr>
          <tr>
            <td class="main"><?php echo TEXT_SPECIALS_EXPIRES_DATE; ?>&nbsp;</td>
            <td class="main"><?php echo tep_draw_input_field('e_day', substr($sInfo->expires_date, 8, 2), 'size="2" maxlength="2" class="cal-TextBox"') . tep_draw_input_field('e_month', substr($sInfo->expires_date, 5, 2), 'size="2" maxlength="2" class="cal-TextBox"') . tep_draw_input_field('e_year', substr($sInfo->expires_date, 0, 4), 'size="4" maxlength="4" class="cal-TextBox"'); ?><a class="so-BtnLink" href="javascript:calClick();return false;" onmouseover="calSwapImg('BTN_date', 'img_Date_OVER',true);" onmouseout="calSwapImg('BTN_date', 'img_Date_UP',true);" onclick="calSwapImg('BTN_date', 'img_Date_DOWN');showCalendar('new_special','e_','BTN_date');return false;"><?php echo tep_image(DIR_WS_IMAGES . 'cal_date_up.gif', 'Calendar', '22', '17', 'align="absmiddle" name="BTN_date"'); ?></a></td>
          </tr>
        </table></td>
      </tr>
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="2">
          <tr>
            <td class="main"><br><?php echo TEXT_SPECIALS_PRICE_TIP; ?></td>
            <td class="main" align="right" valign="top"><br><?php echo (($form_action == 'insert') ? tep_image_submit('button_insert.gif', IMAGE_INSERT) : tep_image_submit('button_update.gif', IMAGE_UPDATE)). '&nbsp;&nbsp;&nbsp;<a href="' . tep_href_link(FILENAME_SPECIALS, 'page=' . $HTTP_GET_VARS['page'] . '&sID=' . $HTTP_GET_VARS['sID']) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>'; ?></td>
          </tr>
        </table></td>
      </form></tr>
<?php
//////////////////////////////////////////////////////
  } else {
//////////////////////////////////////////////////////
[/code]
Those comment lines are made by me as my editor does not support code folding :-)

To make it work I also had to change the file includes/javascript/calendarcode.js in the admin section, see below, its pretty self explanatory (I abuse the fields and risk a breach in the future, well, I know)
[code]
..
var prefix = '';
..

function showCalendar(frmName, pref_x, btnImg, hideDrops, MnDt, MnMo, MnYr, MxDt, MxMo, MxYr,runFuncs) {
    hideDropDowns = hideDrops;
    FuncsToRun = runFuncs;
    calfrmName = frmName;
    prefix = pref_x;
..
}

...
function clearDay() {
    eval('document.' + calfrmName + '.' + prefix + 'day.value = \'\'');
    eval('document.' + calfrmName + '.' + prefix + 'month.value = \'\'');
    eval('document.' + calfrmName + '.' + prefix + 'year.value = \'\'');
    hideCalendar();
    if (FuncsToRun!=null)
        eval(FuncsToRun); 
}

function changeDay(whatDay) {
    curDate.setDate(whatDay);
//    eval('document.' + calfrmName + '.' + prefix + '.value = "'+ padout(curDate.getDate()) + '-' + padout(curDate.getMonth()+1) + '-' + curDate.getFullYear() + '"');
    eval('document.' + calfrmName + '.' + prefix + 'day.value = "'+ padout(curDate.getDate()) + '"');
    eval('document.' + calfrmName + '.' + prefix + 'month.value = "'+ padout(curDate.getMonth()+1) + '"');
    eval('document.' + calfrmName + '.' + prefix + 'year.value = "'+ curDate.getFullYear() + '"');
    hideCalendar();
    if (FuncsToRun!=null)
        eval(FuncsToRun); 
}


[/code]

and finally a change to includes/functions/specials.php in the catalogue section
[code]
<?php
/*
  $Id: specials.php,v 1.5 2003/02/11 01:31:02 hpdl Exp $

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

  Copyright (c) 2003 osCommerce

  Released under the GNU General Public License
*/

////
// Sets the status of a special product
  function tep_set_specials_status($specials_id, $status) {
    return tep_db_query("update " . TABLE_SPECIALS . " set status = '" . $status . "', date_status_change = now() where specials_id = '" . $specials_id . "'");
  }

////
// Auto expire products on special
  function tep_actualize_specials() {
    $specials_query = tep_db_query("select specials_id from " . TABLE_SPECIALS . " where status = '1' and CURDATE() > expires_date and expires_date > 0 or CURDATE() < start_date and start_date > 0 ");
    if (tep_db_num_rows($specials_query)) {
      while ($specials = tep_db_fetch_array($specials_query)) {
        tep_set_specials_status($specials['specials_id'], '0');
      }
    }

    $specials_query = tep_db_query("select specials_id from " . TABLE_SPECIALS . " where status = '0' and CURDATE() >= start_date and CURDATE() <= expires_date and expires_date > 0");
    if (tep_db_num_rows($specials_query)) {
      while ($specials = tep_db_fetch_array($specials_query)) {
        tep_set_specials_status($specials['specials_id'], '1');
      }
    }
  }
?>
[/code]

Ok, that was just quickly fetched together, it might be that I have missed a point or two, but if you have trouble to make it work just let me know, I'll try then to help as quick as possible.

All in all, I hope this helps you as it helped me :-)

Have a nice day.
[i]All decision is left to your taste.[/i]