Listing all products, with all permutations

[gabrielcrowe]

I'm looking for a way to get every possible product and permutation out of the database into a big list. This is for a stock management tool i'm writing that i want to connect to actinic.

SELECT Product.[Short description], ProductProperties.sString1, ProductProperties.nType
FROM ProductProperties 
 LEFT JOIN Product ON Product.[Product Reference] = ProductProperties.[sProductRef]
 
 WHERE ProductProperties.nType = 7

is a very bad start because it does not take into consideration the hierarchical nature of permutations. Can anyone help me with this SQL (or even any other way of getting every possible combo of products out)

Thanks in advance.

[I-CC]

How about PHP at site generation time that creates a tabbed file and then use simple block ifs for matching requirements which then writes to the file on a per line basis.

Just an idea.

[fergusw]

Hi Gabe,

Whats the primary purpose for this stock management? i.e. specifically what info do you require? e.g. only permutations which are associated to products? or does the specification for this tool have to be more open/complex?

[fergusw]

How about PHP at site generation time that creates a tabbed file and then use simple block ifs for matching requirements which then writes to the file on a per line basis.

Just an idea.

Nice idea. Actinic could generate this file for you itself.

[gabrielcrowe]

http://scannableapp.com/

This is my SaaS app.

It uses off the shelf hardware to get the job done. It's for SMB, not really for the enterprise. I have a couple of Actinic people that like the idea but I have to invent something foolproof that lets it handle permutations.

The purpose of the 'list of everything' is to initially import into the system to get a searchable list of everything.

[gabrielcrowe]

I think i managed something like that I was looking for in PHP. I-CC's suggestion helped.

its a dirty solution because it uses a load of database calls per product, instead of 1 per list, or at worst 1 per product.

Luckily this is not something that has to run all the time and its a one off thing.

function comb($a){
	$out = array();
	if (count($a) == 1) {
		$x = array_shift($a);
		foreach ($x as $v) $out[] = array($v);
		return $out;
	}
	foreach ($a as $k => $v){
		$b = $a;
		unset($b[$k]);
		$x = comb($b);
		foreach ($v as $v1){
			foreach ($x As $v2) 
			$out[] = array_merge(array($v1), $v2);
		}
	}
	return $out;
}

function get_permutations($conn,$ProductReference) {
$arr=array();

	$sql = "
	SELECT sString1, nType, nPropertyID, nParentPropertyID FROM ProductProperties
	WHERE sProductRef = '$ProductReference'
	"; 
	
	$res2 = $conn->Execute($sql);
	$arr=array();
	while (!$res2->EOF)
	{
	
	//data set
	$sString1 = $res2->Fields['sString1']->Value;
	$nType = $res2->Fields['nType']->Value;
	$nParentPropertyID = $res2->Fields['nParentPropertyID']->Value;
	$nPropertyID = $res2->Fields['nPropertyID']->Value;
	
	
		$arr[]=array('sString'=> $sString1, 'nType'=> $nType, 'nPropertyID'=> $nPropertyID, 'nParentPropertyID'=> $nParentPropertyID);

	$res2->MoveNext();
	}


return $arr;
}

       
$sql = "

SELECT TOP 200 Product.[Short description], Product.[Product Reference]
FROM Product

"; 

$res = $conn->Execute($sql);
while (!$res->EOF)
{

//data set
$Shortdescription = $res->Fields['Short description']->Value;
$ProductReference = $res->Fields['Product Reference']->Value;
$permutations = get_permutations($conn,$ProductReference);
$innerpermutations = $permutations;
$arr=array();
foreach ($permutations as $permutation) {
	if ($permutation['nType'] == 6) { 
		$permarr=array();
		foreach ($innerpermutations as $option) {
			if ($option['nParentPropertyID'] == $permutation['nPropertyID']) { 
				$permarr[]= $option['sString'];
			}
		}	
	$arr[]=$permarr;
	}
}

$x = comb($arr);

foreach ($x as $prod) {
	echo $Shortdescription ." - ".implode($prod,", ")."<br>";
}



	

$res->MoveNext();
}

[gabrielcrowe]

(This code example runs through Actinet, NOT Actinic's own php interpreter)