I'm still busy, but want a quick break, so anyway...

<!---
	Count how many of each wood type is used...
--->
<cfset WoodTypes = [] />
<cfloop query="batch_report3">
	<cfset WoodName = extractWoodName(Description) />
 
	<cfif StructKeyExists(WoodTypes,WoodName)>
		<cfset WoodTypes[WoodName]++ />
	<cfelse>
		<cfset WoodTypes[WoodName] = 0 />
	</cfif>
</cfloop>
 
<!---
	Work out what the most common wood type is...
--->
<cfset MaxCount = 0 />
<cfset MaxType = "" />
<cfloop item="CurType" collection=#WoodTypes#>
	<cfif WoodTypes[CurType] GT MaxCount >
		<cfset MaxCount = WoodTypes[CurType] />
		<cfset MaxType = CurType />
	</cfif>
</cfloop>
 
<cfoutput>
	<p>Most common wood type is "<b>#MaxType#</b>".</p>
</cfoutput>
 
 
<!---
	Extract the name from the description.
	(May need changing/expanding depending on other description formats.)
--->
<cffunction name="extractWoodName" returntype="string" output="false" >
	<cfargument name="Text" type="string" required />
 
	<cfset var Name = rereplace(Arguments.Text,'^\d/\d x RW','') />
 
	<cfset Name = rereplace(Arguments.Text,'(?:prime|\da?)? (?:common|boards|unselected|blocking) green') />
 
	<cfreturn Trim(Name) />
</cffunction>

If one of the many crazy columns has just the wood name on its own, you don't need the extractWoodName bit.

I didn't test that, so there might be bugs in it, but hopefully not.


Oh, and that only returns one maximum, so if there's like 5 Cherry, 5 Hard Maple, 2 White Oak, it'll say "cherry" OR "hard maple", but not both.
Don't know if that's good enough, or if you do need to cater for that?



If I wasn't busy, I'd suggest a billion ways to make your code look nicer, but I will say just two things:

TABS ARE YOUR FRIEND!
Identing blocks makes things MUCH easier to read.
(Although using eight space for it, like Firefox is doing above, is a tad annoying.)

Also, you don't need lots of those #s - they basically only need to be used when outputting HTML/etc (i.e. inside cfoutput), or when using a variable in a string (i.e. inside "quotes"), but generally not in cfset tags.

Yeah there is no doubt my code isn't nice.

 

I think I've found the easiest way. There is a product id table that seems to have the types in some kind of unique code. I'll take that approach.

 

Thanks man!

Was trying to use some of that code you made to see if I could get it to extract the most common used and I get this error.



It's pointing to this bit:



Any ideas?

WRONG :@

Spaces are your friend. Tabs* are evil.

* the ASCII #9 character

Oh crap, I always do that. :(

Change index="CurType" to item="CurType"

Oh no, you're not one of those freaks are you!? :P

Tabs allow configurable indenting. Spaces don't.

Spaces are silly.

(Though still preferable to no indents at all.)

If you have a decent editor, spaces do allow configurable indenting. Plus they guarantee consistency of appearance, which is important when you're working with other people on a file and there is a mandated maximum line length.

 

(honestly though I don't care much - if I download some sample code that uses tab characters I just do :retab in Vim to convert them to spaces. Consistency is what's important)

No they don't, they allow convertable indenting, because the editor needs to convert from actual to preferred (and probably back again when saving).

Tabs allow it to be done without touching the data.


Also, a "decent editor" could deal without any indenting (or newlines) at all - but we don't always have a decent editor to hand, so keeping the raw files with sensible whitespace makes far more sense.



WTF!? Who has maximum line lengths? This isn't 1970s any more.

Get a decent editor and you have:
1) horizontal scrollbars
2) working (soft) word wrap.

And get a decent language and structure and you'll almost never go beyond 100 characters wide anyway.

Can't get it to work, but that's OK because I've confused myself to the point that I'm not sure what I'm trying to accomplish.

 

Fuck my head hurts!

Is it giving an error or just producing the wrong result?

error. but I'm stuck on trying to figure out how to approach this.

 

I have the needed info in the database. So I just need to compare or count and that's where I'm stuck atm.

OK Pete help me!

I have this:

<CFQUERY NAME="count" DATASOURCE="isiswood">
	SELECT COUNT(*) AS WoodType 
	FROM ProductionDetail WHERE rudID='041811BD' AND rudCategory='Produced' AND rudProductID LIKE '%RO%';
</cfquery>

And it's working, but how can I automate this so I don't have to put every time of shit we have. It's already in the database.

Max line length matters when I have 4 columns of code visible at once. Besides, I didn't make the rule.

You make a compelling argument in favour of tabs for indenting, though.

Which one is the actual wood type, the rudId ?

Try something along the lines of this:

<CFQUERY NAME="count" DATASOURCE="isiswood">
	SELECT rudID AS WoodType, COUNT(*) AS WoodCount
	FROM ProductionDetail WHERE rudCategory='Produced' AND rudProductID LIKE '%RO%'
	GROUP BY rudID
</cfquery>

Well here's the thing. The %RO% could be about 20 different things. Should I just create a table to hold what they could be and use it to loop through?

Oh, so "RO" is one type of wood, but the ProductID contains other stuff in addition to that?

Is it always two characters at the same location?

If so, you can use mid or substring or whatever function MSSQL uses to do this... looks like it is SUBSTRING, so...

SELECT SUBSTRING(rudProductID,6,2) AS WoodType, COUNT(*) AS WoodCount
FROM ProductionDetail WHERE rudCategory='Produced'
GROUP BY SUBSTRING(rudProductID,6,2)

If the RO (and similar values) changes position in the code then you can't do that, but probably they're in the same place?

Nice! Yes always in the same spot. That should be saweet!

That worked like a charm! Thanks!

OK Peter I'm stuck again.

 

I can get it to output the types and count for a query, but how can I tell it the one with the biggest number is the one I want to use?

Nevermind I got it. Probably not the prettiest but it's working :)