How To: Method 2 - Automated Sales System - Day-Week-Month

For articles specific to version 6.x
Post Reply
MegaFemaTron2
Confirmed
Confirmed
Posts: 84
Joined: Thu Oct 13, 2011 8:37 pm

How To: Method 2 - Automated Sales System - Day-Week-Month

Post by MegaFemaTron2 » Sun Apr 08, 2012 3:21 am

Ok. I posted viewtopic.php?f=27&t=1547 previously about how to create an automated sales system. It works great and I've been using it on my site ever since I posted it. But I wanted to be able to show the regular price of the item with a line through it and the actual sale price and preferably at (you saved $x) as well. I foolishly thought adding a "sale" price for the products would do this just like I foolishly thought the "prodprice" field in the database was the price shown on the site but I digress. :)

In my store, the price being shown is "prodcalculatedprice" in the database (i'm sure this has something to do with my display choice for prices). Here are the steps to create an automated sales system that does the same thing as my first method only this one displays the pricing for the product in a way that online shoppers are used to seeing.

I also needed to limit the amount of each product a customer could buy of products in these sales categories so this was added to the scripts.

The first part of the script resets the products already in the sales categories. So say you already have a batch in your daily sales category and it's time to remove those and add new ones. The first part of the script resets the max quantity field back to original value, sets the prodcalculatedprice back to the retail price and removes the category reference for the daily sales category.

**Please be sure of how your store is using the price fields in ISC. When you use the scripts you have to make sure the right filed is being changed. Make sure retailprice always has a value. I originally had no retail prices set, only prodprice and prodcalculatedprice. Creating a datafeed for my affiliate program sort of forced me to add the price in the retailprice field even though it's not being displayed in my store. It came in handy for this project for sure.

This is one of the products on my site after I set this up today:
The way your prices will display with this mod
The way your prices will display with this mod

1. Create a Daily, Weekly and Monthly Sales product category in ISC.

2. Copy the code below into 3 different php files. One for daily, weekly and monthly. You can adjust your sale numbers and quantity limits however you like. *** NOTE: the scripts have a bit of code like this "NOT LIKE '%1213' AND prodcatids NOT LIKE '%1214'" This code keeps the script from adding products that are already in one of your other sales categories. So you have to change these numbers in each script to match your category numbers respectively. ***



This is daily.php: I run it via cron every 24 hours - be sure to change the category number to the one you create for daily sales.

Code: Select all

<?php
// Make a MySQL Connection
mysql_connect("localhost", "dbuser", "dbpass") or die(mysql_error());
mysql_select_db("dbname") or die(mysql_error());


// Grab the productid and catids from products containing the catid 1212

$result = mysql_query("SELECT productid, prodcatids, prodretailprice FROM luv_products WHERE prodcatids LIKE '%1212'") 
or die(mysql_error());


while($row = mysql_fetch_array( $result )) {
$prodid = $row['productid'];

// Set maximum purchase quantity limit to 0
$result2 = mysql_query("UPDATE luv_products SET prodmaxqty = 0 WHERE productid = '$prodid'")
or die(mysql_error());

// Set prodcalculatedprice the same as prodretailprice
$retail = $row['prodretailprice'];
$result3 = mysql_query("UPDATE luv_products SET prodcalculatedprice = '$retail' WHERE productid = '$prodid'")
or die(mysql_error());

// Remove catid 1212 from prodcatids field for this query & remove these records from luv_categoryassociations for catid 1212
$pcid = $row['prodcatids'];
$dcatid = ",1212";
$result4 = mysql_query("UPDATE luv_products SET prodcatids = REPLACE(prodcatids,'$dcatid','') WHERE productid = '$prodid'")
or die(mysql_error());

// Remove references from luv_categoryassociations table.
$result5 = mysql_query("DELETE FROM luv_categoryassociations WHERE categoryid = '1212'") 
or die(mysql_error());
}


// Get 10 new products with a retail price between $40-79.99.

$result6 = mysql_query("SELECT productid, prodcatids, prodcalculatedprice FROM luv_products WHERE prodcatids NOT LIKE '%1213' AND prodcatids NOT LIKE '%1214' AND prodcurrentinv > 2 AND prodprice > 39.99 AND prodprice < 80.00 AND RAND()<= 0.15 LIMIT 0,10") 
or die(mysql_error());


while($row2 = mysql_fetch_array( $result6 )) {
$prodid = $row2['productid'];

// Set maximum purchase quantity to 1
$result7 = mysql_query("UPDATE luv_products SET prodmaxqty = 1 WHERE productid = '$prodid'")
or die(mysql_error());

// Reduce prodcalculatedprice by 50%
$regularp = $row2['prodcalculatedprice'];
$discount = ($regularp / 2);
$result8 = mysql_query("UPDATE luv_products SET prodcalculatedprice = '$discount' WHERE productid = '$prodid'")
or die(mysql_error());

// Add catid 1212 to this selection's prodcatids field.
$cid = $row2['prodcatids'];
$dcat = '1212';
$result9 = mysql_query("UPDATE luv_products SET prodcatids = '$cid,$dcat' WHERE productid = '$prodid'")
or die(mysql_error());

// Add references to isc_categoryassociations table.
$result10 = mysql_query("INSERT INTO luv_categoryassociations SET productid = '$prodid', categoryid = '$dcat'") 
or die(mysql_error());
}


?>



This is weekly.php: I run this via cron once a week. - Be sure to change the category number to the one you created for weekly sales.

Code: Select all

<?php
// Make a MySQL Connection
mysql_connect("localhost", "dbuser", "dbpass") or die(mysql_error());
mysql_select_db("dbname") or die(mysql_error());


// Grab the productid and catids from products containing the catid 1213

$result = mysql_query("SELECT productid, prodcatids, prodretailprice FROM luv_products WHERE prodcatids LIKE '%1213'") 
or die(mysql_error());


while($row = mysql_fetch_array( $result )) {
$prodid = $row['productid'];

// Set maximum purchase quantity limit to 0
$result2 = mysql_query("UPDATE luv_products SET prodmaxqty = 0 WHERE productid = '$prodid'")
or die(mysql_error());

// Set prodcalculatedprice the same as prodretailprice
$retail = $row['prodretailprice'];
$result3 = mysql_query("UPDATE luv_products SET prodcalculatedprice = '$retail' WHERE productid = '$prodid'")
or die(mysql_error());

// Remove catid 1213 from prodcatids field for this query & remove these records from luv_categoryassociations for catid 1213
$pcid = $row['prodcatids'];
$dcatid = ",1213";
$result4 = mysql_query("UPDATE luv_products SET prodcatids = REPLACE(prodcatids,'$dcatid','') WHERE productid = '$prodid'")
or die(mysql_error());

// Remove references from luv_categoryassociations table.
$result5 = mysql_query("DELETE FROM luv_categoryassociations WHERE categoryid = '1213'") 
or die(mysql_error());
}


// Get 50 new products with a retail price between $40-89.99.

$result6 = mysql_query("SELECT productid, prodcatids, prodcalculatedprice FROM luv_products WHERE prodcatids NOT LIKE '%1212' AND prodcatids NOT LIKE '%1214' AND prodcurrentinv > 2 AND prodprice > 39.99 AND prodprice < 90.00 AND RAND()<= 0.25 LIMIT 0,50") 
or die(mysql_error());


while($row2 = mysql_fetch_array( $result6 )) {
$prodid = $row2['productid'];

// Set maximum purchase quantity to 2
$result7 = mysql_query("UPDATE luv_products SET prodmaxqty = 2 WHERE productid = '$prodid'")
or die(mysql_error());

// Reduce prodcalculatedprice by 30%
$total = $row2['prodcalculatedprice'];
$discount = (0.3 * $total);
$newprice = ($total - $discount);
$result8 = mysql_query("UPDATE luv_products SET prodcalculatedprice = '$newprice' WHERE productid = '$prodid'")
or die(mysql_error());

// Add catid 1213 to this selection's prodcatids field.
$cid = $row2['prodcatids'];
$dcat = '1213';
$result9 = mysql_query("UPDATE luv_products SET prodcatids = '$cid,$dcat' WHERE productid = '$prodid'")
or die(mysql_error());

// Add references to isc_categoryassociations table.
$result10 = mysql_query("INSERT INTO luv_categoryassociations SET productid = '$prodid', categoryid = '$dcat'") 
or die(mysql_error());
}


?>

This is Monthly.php: I run this via cron once a month on the first. - Be sure to change the category number to the one you created for monthly sales.

Code: Select all

<?php
// Make a MySQL Connection
mysql_connect("localhost", "dbuser", "dbpass") or die(mysql_error());
mysql_select_db("dbname") or die(mysql_error());

// Grab the productid and catids from products containing the catid 1214

$result = mysql_query("SELECT productid, prodcatids, prodretailprice FROM luv_products WHERE prodcatids LIKE '%1214'") 
or die(mysql_error());


while($row = mysql_fetch_array( $result )) {
$prodid = $row['productid'];

// Set maximum purchase quantity limit to 0
$result2 = mysql_query("UPDATE luv_products SET prodmaxqty = 0 WHERE productid = '$prodid'")
or die(mysql_error());

// Set prodcalculatedprice the same as prodretailprice
$retail = $row['prodretailprice'];
$result3 = mysql_query("UPDATE luv_products SET prodcalculatedprice = '$retail' WHERE productid = '$prodid'")
or die(mysql_error());

// Remove catid 1214 from prodcatids field for this query & remove these records from luv_categoryassociations for catid 1214
$pcid = $row['prodcatids'];
$dcatid = ",1214";
$result4 = mysql_query("UPDATE luv_products SET prodcatids = REPLACE(prodcatids,'$dcatid','') WHERE productid = '$prodid'")
or die(mysql_error());

// Remove references from luv_categoryassociations table.
$result5 = mysql_query("DELETE FROM luv_categoryassociations WHERE categoryid = '1214'") 
or die(mysql_error());
}


// Get 100 new products with a price of $40 or more.

$result6 = mysql_query("SELECT productid, prodcatids, prodcalculatedprice FROM luv_products WHERE prodcatids NOT LIKE '%1212' AND prodcatids NOT LIKE '%1213' AND prodcurrentinv > 2 AND prodprice > 39.99 AND RAND()<= 0.15 LIMIT 0,100") 
or die(mysql_error());


while($row2 = mysql_fetch_array( $result6 )) {
$prodid = $row2['productid'];

// Set maximum purchase quantity to 3
$result7 = mysql_query("UPDATE luv_products SET prodmaxqty = 3 WHERE productid = '$prodid'")
or die(mysql_error());

// Reduce prodcalculatedprice by 10%
$total = $row2['prodcalculatedprice'];
$discount = (0.1 * $total);
$newprice = ($total - $discount);
$result8 = mysql_query("UPDATE luv_products SET prodcalculatedprice = '$newprice' WHERE productid = '$prodid'")
or die(mysql_error());

// Add catid 1214 to this selection's prodcatids field.
$cid = $row2['prodcatids'];
$dcat = '1214';
$result9 = mysql_query("UPDATE luv_products SET prodcatids = '$cid,$dcat' WHERE productid = '$prodid'")
or die(mysql_error());

// Add references to isc_categoryassociations table.
$result10 = mysql_query("INSERT INTO luv_categoryassociations SET productid = '$prodid', categoryid = '$dcat'") 
or die(mysql_error());
}


?>


3. Create the new Display class, HomeDailySaleProducts.php, below in /includes/display/ - Notice the new snippet references. These and the cat id needs to be changed if you are also creating one of these for a Weekly and/or Monthly sales display. Also make sure you changed the class name when creating new files for Weekly and Monthly.

Code: Select all

<?php
class ISC_HOMEDAILYSALEPRODUCTS_PANEL extends PRODUCTS_PANEL
{
	public function SetPanelSettings()
	{
		$count = 0;
		$GLOBALS['SNIPPETS']['HomeDailySaleProducts'] = '';

		if (GetConfig('HomeNewProducts') == 0) {
			$this->DontDisplay = true;
			return;
		}

		if(!GetConfig('ShowProductRating')) {
			$GLOBALS['HideProductRating'] = "display: none";
		}
  
             // This query should contain your daily category id. Since the catid is added to whatever cat ids are already present, you need to use LIKE

		$query = $this->getProductQuery(
			'p.prodcatids LIKE "%13"',
			'RAND()',
			getConfig('HomeNewProducts')
		);
		$result = $GLOBALS['ISC_CLASS_DB']->Query($query);

		$GLOBALS['AlternateClass'] = '';
		while($row = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) {
			$this->setProductGlobals($row);
			$GLOBALS['SNIPPETS']['HomeDailySaleProducts'] .= $GLOBALS['ISC_CLASS_TEMPLATE']->GetSnippet("HomeDailySaleProductsItem");
		}
		if(!$GLOBALS['SNIPPETS']['HomeDailySaleProducts']) {
			$this->DontDisplay = true;
			return;
		}
	}
}

4. Create the following new panel in templates/yourtemplatename/Panels/HomeDailySaleProducts.html

Code: Select all

<div class="Block SaleProducts Moveable Panel"  id="HomeSaleProducts" style="width: 100%;">
		<h2>Amazing Daily Sales!</h2>
		<div class="BlockContent">
			<ul class="ProductList">
				%%SNIPPET_HomeDailySaleProducts%%
			</ul>
		</div>
		<br class="Clear" />
	</div>

5. Create the following new snippet, templates/yourtemplatename/Snippets/HomeDailySaleProductsItem.html (you can actually use the current HomeSaleProductsItem for all of them if you want. You just have to make sure it's referenced in the display file.)

Code: Select all

<li class="%%GLOBAL_AlternateClass%%">
	<div class="ProductImage">
		%%GLOBAL_ProductThumb%%
	</div>
	<div class="ProductDetails">
		<strong><a href="%%GLOBAL_ProductLink%%">%%GLOBAL_ProductName%%</a></strong>
	</div>
	<div class="ProductPriceRating">
		<em>%%GLOBAL_ProductPrice%%</em>
		<span class="Rating Rating%%GLOBAL_ProductRating%%"><img src="%%GLOBAL_IMG_PATH%%/IcoRating%%GLOBAL_ProductRating%%.gif" alt="" style="%%GLOBAL_HideProductRating%%" /></span>
	</div>
	<div class="ProductActionAdd" style="display:%%GLOBAL_HideActionAdd%%;">
		<a href="%%GLOBAL_ProductURL%%">%%GLOBAL_ProductAddText%%</a>
	</div>
</li>

6. Add the shortcode where ever you want. I added the daily, weekly and monthly panels to my front page. The categories themselves can be easily accessed in the category menu and I'll eventually create banners and sidebar images for them to make them more obvious.

Code: Select all

%%Panel.HomeDailySaleProducts%%

That should do it! I also decreased the amount of products on sale for each category. I believe in the first method I had a ridiculous amount of products for sale in each one. :) These numbers work better.

Cheers! :ugeek:
ISC 6.1.1 Ultimate Edition

Post Reply