[lapidarist]

This is my first xcart add-on. I wrote some code to show New Products on its own page.

Crude install notes

1) You need to edit {xcart_dir}/skin1/customer/home_main.tpl

Add the following lines before the {else} at bottom of file:

{elseif $main eq "newproducts"}
{include file="customer/main/newproducts.tpl"}


2) Copy newproducts.tpl file to {$xcart_dir}/skin1/customer/main/

example: /xcart/skin1/customer/main/newproducts.tpl


3) Copy newproducts.php file to {xcart_dir}/

example: /xcart/newproducts.php


4) Apply the SQL Language file 'language.sql' into the Patch file section
of the X-cart Admin :: Patch/Upgrade center. Or load via mysql


5) You should now be able to put a link using

<domain_name>/{$xcart_dir}/newproducts.php


You can add the link anywhere (speed bar, href, help section, etc).


Let me know if this is useful to you

James



Save this file as newproducts.php

<?
#+-----------------------------------------------------------------------------+
#| New Products add-on                                                         |
#| Copyright (c) 2005 James Carpenter <jamesc@unconventionallapidarist.com>    |
#| All rights reserved.                                                        |
#+-----------------------------------------------------------------------------+
#
# $Id: newproducts.php,v 1.0.0.1 2005/09/23 05:00:00 jfc Exp $
#

require "./auth.php";

require $xcart_dir."/include/categories.php";

# I am not sure what the membership should be set. On a generic 
# Try to determine how to get this info from the current environment.
$membership = "";

# this could be set as a config option
if (!$days || $days == 0) {$days = 7;}

# default sorting if needed. This could be a config option.
if (!$sortby || $sortby == "") { $sortby = "add_date"; }


# This array is used to determine the from/to date range
# in the WHERE clause. It is also used for the labels 
# for the range.
$newproducts_dayrange = array(
	1	=>	func_get_langvar_by_name("lbl_newproducts_24hrs"),
	3	=>	func_get_langvar_by_name("lbl_newproducts_3days"),
	7 	=>	func_get_langvar_by_name("lbl_newproducts_7days"),
   30	=>	func_get_langvar_by_name("lbl_newproducts_30days")
);


# This array is used to determine the order by clause and labels
# used for sorting
$sort_fields = array(
	"add_date"		=> func_get_langvar_by_name("lbl_newproducts_adddate"),
	"productcode" 	=> func_get_langvar_by_name("lbl_newproducts_sku"),
	"product" 		=> func_get_langvar_by_name("lbl_newproducts_product"),
    "price" 		=> func_get_langvar_by_name("lbl_newproducts_price")
);




# set date ranges
$date_range = 86400 * (integer)$days; // 60(sec) * 60(min) * 24(hrs) * $days in unix time
$now_time = time();
$from_time = $now_time - $date_range;

$query = "AND $sql_tbl[products].avail > 0 AND add_date > $from_time AND add_date < $now_time ";


# set the default
$newproducts = "";

# The function 'func_search_products' was taken from '{xcart_dir}/include/func.php'
# in version 4.0.16
# It was modified to give more product data back (descr,fulldescr, etc) and 
# to be able to get the count that the query would return.  
#
function func_search_newproducts($query, $membership, $retcount, $orderby="", $limit="") {
	global $current_area, $user_account;
	global $store_language, $sql_tbl;
	global $config;
	global $cart, $login;
	global $active_modules;

	#
	# Generate ORDER BY rule
	#
	if (empty($orderby)) {
		$orderby = ($config["Appearance"]["products_order"] ? $config["Appearance"]["products_order"] : "orderby");
		if($orderby == 'title') {
			$orderby = 'product';
		} elseif($orderby == 'quantity') {
			$orderby = "$sql_tbl[products].avail";
		} elseif($orderby == "orderby") {
			$orderby = "$sql_tbl[products_categories].orderby";
		} elseif($orderby == "quantity") {
			$orderby = "$sql_tbl[products].avail";
		} elseif($orderby == "price") {
			$orderby = "price";
		} elseif($orderby == "productcode") {
			$orderby = "$sql_tbl[products].productcode";
		}
	}

	
	#
	# Generate membership condition
	#
	if ($current_area == "C") {
		$membership_condition = " AND ($sql_tbl[categories].membership='".addslashes($membership)."' OR $sql_tbl[categories].membership='') AND $sql_tbl[products].forsale='Y'";
	}
	else
		$membership_condition = "";

	#
	# Generate products availability condition
	#
	if ($config["General"]["unlimited_products"]=="N" && (($current_area == "C" || $current_area == "B") && $config["General"]["disable_outofstock_products"] == "Y"))
		$avail_condition = " AND $sql_tbl[products].avail>0 ";
	else 
		$avail_condition = "";

	#$select_query = "SELECT $sql_tbl[products].productid, $sql_tbl[products].product, $sql_tbl[products].productcode, $sql_tbl[products].avail, MIN($sql_tbl[pricing].price) AS price";
	if ($retcount == 'Y') {
		$select_query = "SELECT COUNT($sql_tbl[products].productid)";
	}
	else {
		$select_query = "SELECT $sql_tbl[products].*, MIN($sql_tbl[pricing].price) AS price";
	}
	
	$from_query = " FROM $sql_tbl[products], $sql_tbl[categories], $sql_tbl[products_categories], $sql_tbl[pricing]";
	
	$where_query = " WHERE $sql_tbl[products].productid=$sql_tbl[products_categories].productid AND $sql_tbl[products_categories].categoryid=$sql_tbl[categories].categoryid AND $sql_tbl[products].productid=$sql_tbl[pricing].productid AND $sql_tbl[pricing].quantity=1 AND ($sql_tbl[pricing].membership='".addslashes($membership)."' OR $sql_tbl[pricing].membership='') $membership_condition $avail_condition";

	$where_query .= " AND $sql_tbl[pricing].variantid = 0";	

	if ($current_area == 'C' && empty($active_modules['Product_Configurator'])) {
		$where_query .= " AND $sql_tbl[products].product_type <> 'C' AND $sql_tbl[products].product_type <> 'B' ";
	}

	$groupby_query = " GROUP BY $sql_tbl[products].productid";
	
	$orderby_query = " ORDER BY $orderby";
	
	if (!empty($limit))
		$limit_query = " LIMIT $limit";

	if ($retcount == 'Y') {
		$search_query = $select_query.$from_query.$where_query.$query.$groupby_query;
		# print "QUERY: $search_query\n\n";
		$result = db_query($search_query);
		$total_items = db_num_rows($result);
		# print "Total Items: $total_items\n";
		db_free_result($result);
		$result = $total_items;
	}
	else
	{
		#
		# Check if product have prodyct class (Feature comparison)
		# 
		if(!empty($active_modules['Feature_Comparison']) && $current_area == "C") {
			global $comparison_list_ids;
			$from_query .= " LEFT JOIN $sql_tbl[product_features] ON $sql_tbl[product_features].productid = $sql_tbl[products].productid";
			$select_query .= ", $sql_tbl[product_features].fclassid";
			if(($config['Feature_Comparison']['fcomparison_show_product_list'] == 'Y') && $config['Feature_Comparison']['fcomparison_max_product_list'] > @count((array)$comparison_list_ids)) {
				$select_query .= ", IF($sql_tbl[product_features].fclassid IS NULL || $sql_tbl[product_features].productid IN ('".@implode("','",@array_keys((array)$comparison_list_ids))."'),'','Y') as is_clist";
			}
		}

		#
		# Check if product have product options (Product options)
		# 
		if(!empty($active_modules['Product_Options'])) {
			$from_query .= " LEFT JOIN $sql_tbl[classes] ON $sql_tbl[classes].productid = $sql_tbl[products].productid LEFT JOIN $sql_tbl[variants] ON $sql_tbl[variants].productid = $sql_tbl[products].productid";
			if ($current_area == 'C' && $config["General"]["disable_outofstock_products"] == "Y" && $config["General"]["unlimited_products"] != "Y") {
				$from_query .= " AND $sql_tbl[variants].avail > 0";
			}
			$select_query .= ", SUM(IF($sql_tbl[classes].classid IS NULL,0,1)) as product_options, IF ($sql_tbl[classes].classid IS NULL,'','Y') as is_product_options, IF($sql_tbl[variants].variantid IS NULL,'','Y') as is_variant";
		}
		
		if ($current_area == "C" && $store_language != $config["default_customer_language"])
			$from_query .= " LEFT JOIN $sql_tbl[products_lng] ON $sql_tbl[products].productid=$sql_tbl[products_lng].productid";

		#
		# Generate search query
		#
		$search_query = $select_query.$from_query.$where_query.$query.$groupby_query.$orderby_query.$limit_query;
		
		$result = func_query($search_query);	

		if ($result && ($current_area=="C" || $current_area=="B") ) {
		#
		# Post-process the result products array
		#
			foreach ($result as $key=>$value) {
				if (!empty($cart) and !empty($cart["products"]) && $current_area=="C") {
				#
				# Update quantity for products that already placed into the cart
				#
					$in_cart = 0;
					foreach ($cart["products"] as $cart_item)
					if ($cart_item["productid"] == $value["productid"])
						$in_cart += $cart_item["amount"];
					$result[$key]["avail"] -= $in_cart;
				}
		
				#
				# Get thumbnail's URL (uses only if images stored in FS) 
				#  
				$result[$key]["tmbn_url"] = func_get_thumbnail_url($result[$key]["productid"]);
				
				if ($current_area == "C") {
					$result[$key]["taxes"] = func_get_product_taxes($result[$key], $login);
				}

				#
				# Check if product have product options
				# 

				$int_res = func_query_first("SELECT * FROM $sql_tbl[products_lng] WHERE code='$store_language' AND productid='$value[productid]'");
				
				if (!empty($int_res["product"]))
					$result[$key]["product"] = stripslashes($int_res["product"]);
				
				if (!empty($int_res["descr"]))
					$result[$key]["descr"] = stripslashes($int_res["descr"]);
		
				if ($result[$key]["descr"] == strip_tags($result[$key]["descr"]))
					$result[$key]["descr"] = str_replace("\n", "
", $result[$key]["descr"]);

				if (!empty($int_res["full_descr"]))
					$result[$key]["full_descr"] = stripslashes($int_res["full_descr"]);

				if ($result[$key]["full_descr"] == strip_tags($result[$key]["full_descr"]))
					$result[$key]["full_descr"] = str_replace("\n", "
", $result[$key]["full_descr"]);
			}
		}
	}
	return $result;
}


# determine how many products fit the query criteria.
$total_items = func_search_newproducts($query, $membership, $retcount="Y");

if ($total_items > 0) 
{
	#
	# Prepare the page navigation
	#
	if(!isset($objects_per_page)) {
		if ($current_area == "C" || $current_area == "B")
			$objects_per_page = $config["Appearance"]["products_per_page"];
		else
			$objects_per_page = $config["Appearance"]["products_per_page_admin"];
	}
	$total_nav_pages = ceil($total_items/$objects_per_page)+1;

	
	# calculate the upper and lower LIMIT for the SQL statement
	if (($page == 1) || $page == "")
		{$lowerLimit = 0; }
	else
		{$lowerLimit = (($page - 1) * $objects_per_page); }
	
	if ($sortby == "price") { $sortorder = "$sql_tbl[pricing].$sortby";}
		else { $sortorder = "$sql_tbl[products].$sortby"; }

	if (!sortdirection || ($sortdirection == "") || ($sortdirection == "0")) { 
			$sort_dir = "desc";
			$sortdirection = "0";
	}
	else { 
		$sort_dir = "asc";
		$sortdirection = "1";
	}
}

# if we have items to show.. go get them.. if not it will default to empty string "".
if ($total_items > 0) {
	# get the product list that is limited to the page we need to display.
	$newproducts = func_search_newproducts($query, $membership, $retcount="N", $orderby="$sortorder $sort_dir", $limit="$lowerLimit, $objects_per_page");
}

# If I want to have a secondary sort with the product code? Haven't decided yet
#$newproducts = func_search_newproducts($query, $membership, $retcount="N", $orderby="$sortorder $sort_dir, $sql_tbl[products].productcode $sort_dir", $limit="$lowerLimit, $objects_per_page");


include $xcart_dir."/include/navigation.php";


# flag set that is used in home_main.tpl
$smarty->assign("main","newproducts");

$smarty->assign("newproducts_dayrange", $newproducts_dayrange);
$smarty->assign("sort_fields", $sort_fields);
$smarty->assign("days",$days);
$smarty->assign("sortby",$sortby);
$smarty->assign("sortdirection",$sortdirection);

# give the product array to smarty to make it available sitewide.
$smarty->assign("products",$newproducts);
$smarty->assign("first_item", $first_page+1);
$smarty->assign("last_item", min($first_page+$objects_per_page, $total_items));
$smarty->assign("total_items",$total_items);
$smarty->assign("navigation_script","newproducts.php?days=$days&sortby=$sortby&sort_direction=$sort_direction");

# create the location bar  xxx :: New Products
$location[] = array(func_get_langvar_by_name("lbl_newproducts"), "newproducts.php");
$smarty->assign("location",$location);

# This function will start the display of the .tpl's
func_display("customer/home.tpl",$smarty);

?>

Save as newproducts.tpl

{* $Id: newproducts.tpl,v 1.0.0.1 2005/09/23 01:00:00 jfc Exp $ *}
{* This uses 
	$lng.lbl_newproducts_since 	=> "Products added since"
	$lng.lbl_newproducts		=> "New Products"
*}


{if ($navigation_page eq "")||($navigation_page eq "1")}{$current_category.description|regex_replace:"/[\n]/":"
"}

{/if}
{capture name=dialog}

{assign var="tmp" value="0"}
{assign var="url" value="newproducts.php?"}

{* ext-1 - this could be moved to a new .tpl file *}
<font size="2">{$lng.lbl_newproducts_since}:
	{foreach from=$newproducts_dayrange key=name item=field}
	{if $name eq $days}{$field}{else}{$field}{/if}
	{/foreach}
</font>
{* ext-1 *}




{if $products ne "" }
<BR clear="left">
<HR size="1" noshade>

{/if}

{if !empty($products) && is_array($products)}
	{* ext-2 - this could be moved to a new .tpl file *}
	{if $sort_fields}
		<DIV align="right"><font size="2">
			{$lng.lbl_sort_by}:
			{foreach from=$sort_fields key=name item=field}
				{if $name eq $sortby}[img]{$ImagesDir}/{if $sortdirection eq [/img]{/if}{$field}{if $name eq $sortby}{/if}
			{/foreach}</font>
		</DIV>
	{/if}
	{* ext-2 *}	

	{if $total_pages gt 2}
		
{ include file="customer/main/navigation.tpl" }
		{/if}

	<HR size="1" width="100%">
	{include file="customer/main/products.tpl" products=$products}
{else}
	{$lng.txt_no_newproducts}
{/if}
{/capture}

{include file="dialog.tpl" title=$lng.lbl_newproducts content=$smarty.capture.dialog extra="width=100%"}

{ include file="customer/main/navigation.tpl" }

Save file as language.sql

INSERT INTO xcart_languages (code, descr,name,value,topic) VALUES ('US','New Products','lbl_newproducts','New Products','Labels');
INSERT INTO xcart_languages (code, descr,name,value,topic) VALUES ('US','New Products added since (days)','lbl_newproducts_since','Products added since','Labels');

INSERT INTO xcart_languages (code, descr,name,value,topic) VALUES ('US','24 hour label','lbl_newproducts_24hrs','24 Hours','Labels');
INSERT INTO xcart_languages (code, descr,name,value,topic) VALUES ('US','3 day label','lbl_newproducts_3days','3 Days','Labels');
INSERT INTO xcart_languages (code, descr,name,value,topic) VALUES ('US','7 day label','lbl_newproducts_7days','7 Days','Labels');
INSERT INTO xcart_languages (code, descr,name,value,topic) VALUES ('US','30 days label','lbl_newproducts_30days','30 Days','Labels');


INSERT INTO xcart_languages (code, descr,name,value,topic) VALUES ('US','New Products add_date','lbl_newproducts_adddate','Add Date','Labels');
INSERT INTO xcart_languages (code, descr,name,value,topic) VALUES ('US','New Products add_date','lbl_newproducts_sku','SKU','Labels');
INSERT INTO xcart_languages (code, descr,name,value,topic) VALUES ('US','New Products add_date','lbl_newproducts_product','Title','Label s');
INSERT INTO xcart_languages (code, descr,name,value,topic) VALUES ('US','New Products add_date','lbl_newproducts_price','Price','Labels' );

INSERT INTO xcart_languages (code, descr,name,value,topic) VALUES ('US','New Products not found','txt_no_newproducts','No new products have been listed within timeframe you specified','Text');

Here is an example on my site. This site is a live site.
http://www.unconventionallapidarist.com/xcart/newproducts.php