How To: Create An 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: Create An Automated Sales System - Day-Week-Month

Post by MegaFemaTron2 »

If you are looking for a way to automate creating sale items for your shop, I just worked out a system for my site that I'll share. Perhaps someone might tweak it or have a better way but this is what I came up with.

This system really just moves random products in and out of sale categories that have discount rules applied. You could just create one general sale category or many. It's up to you.

I originally had some math going on in this script that would also insert the discounted amount into the saleprice column in the database for each product on sale but I decided using the discount rules in ISC would be easier. Unfortunately, now I have to figure out a way to let the customers know the item will be discounted once it's placed in the cart since there is no code to scream this on any of the product listing pages. Anyway here goes.... I'll only post the daily script and new ISC files since you only need to copy them to make them for Weekly or Monthly or whatever interval you'd like.


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


2. Create category-based, site-wide discount rules for whatever you like. For example, I created a discount rule to give 40% off products in the Daily Sales category.


3. Create this PHP script that will run daily via cron job: daily.php - This can be used for the Weekly and Monthly sales cats too. Just change the cat id and whatever product limit you want. For example, I put 100 products on sale for daily at 40% off, 200 for Weekly at 20% off and 300 for monthly at 10% off.

Code: Select all

<?php
// Make a MySQL Connection
mysql_connect("localhost", "your db username", "your db password") or die(mysql_error());
mysql_select_db("your db name") or die(mysql_error());


// Grab the product ids and category ids from products containing the category id 13 <--- this will be different for your site.

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


// Remove only category id 13 from these products

while($row = mysql_fetch_array( $result )) {
$prodid = $row['productid'];
$dcatid = ",13";
$result2 = mysql_query("UPDATE isc_products SET prodcatids = REPLACE(prodcatids,'$dcatid','') WHERE productid = '$prodid'")
or die(mysql_error());

// Remove the records containing category id 13 from isc_categoryassociations

$result3 = mysql_query("DELETE FROM isc_categoryassociations WHERE categoryid = '13'") 
or die(mysql_error());
}


// Get 5 new random products with a price of $11 or more.  <--- These can be whatever you like. The next query keeps the script from putting the same product in more than one sale category.

$result4 = mysql_query("SELECT productid, prodcatids FROM isc_products WHERE prodcatids NOT LIKE '%14' AND prodcatids NOT LIKE '%15' AND prodprice > 10.99 AND RAND()<= 0.40 LIMIT 0,5") 
or die(mysql_error());

// Add category id 13 to this selection's prodcatids field (current category ids remain the same)

while($row2 = mysql_fetch_array( $result4 )) {
$prodid = $row2['productid'];
$cid = $row2['prodcatids'];
$dcat = '13';
$result5 = mysql_query("UPDATE isc_products SET prodcatids = '$cid,$dcat' WHERE productid = '$prodid'")
or die(mysql_error());

// Add references to isc_categoryassociations table.

$result6 = mysql_query("INSERT INTO isc_categoryassociations SET productid = '$prodid', categoryid = '$dcat'") 
or die(mysql_error());
}


?>

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

5. 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>

6. 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>

7. 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's it! It's pretty simple and straight forward. I think what I'm going to do is tweak this so that when it adds products to the sales cats, it records those product ids and checks against them so a product doesn't get added to the same sales category more than once within 6-12 months.
ISC 6.1.1 Ultimate Edition
CharlieFoxtrot
Confirmed
Confirmed
Posts: 413
Joined: Sun Aug 09, 2009 1:23 pm

Re: How To: Create An Automated Sales System - Day-Week-Mont

Post by CharlieFoxtrot »

Brilliant idea! I can't wait to see if I can adapt your concept and your PHP code to work with ISC 4.07. ~ Thanks for sharing it!!
ISC 4.0.7

"... and let's be honest that whole "by design" thing is getting old too."
MegaFemaTron2
Confirmed
Confirmed
Posts: 84
Joined: Thu Oct 13, 2011 8:37 pm

Re: How To: Create An Automated Sales System - Day-Week-Mont

Post by MegaFemaTron2 »

CharlieFoxtrot wrote:Brilliant idea! I can't wait to see if I can adapt your concept and your PHP code to work with ISC 4.07. ~ Thanks for sharing it!!
Fantastic! Let me know how it goes. I think this should work with any version that allows discount rules by category. If not, I think you could add a little math into the daily.php file to calculate the sale price and insert it into the right column like my original plan and automate adding a sale price which will do the same thing. It may even be easier on some level since resetting would just involve clearing the sale price column on previous sales items by category.

I'll update my post too when I implement the frequency control. I've been sidetracked by my never ending quest for a collapsible side category list that also allows an active link for the top level category. :)
ISC 6.1.1 Ultimate Edition
Post Reply