MySQL – report on sales by month for a given time period

Here’s a nice sql script to get a report of monthly orders and sales for a time period.

SELECT
 YEAR(DATETIME) AS `year`,
 MONTHNAME(DATETIME) AS `month`,
 SUM(subtotal) AS `subtotal`,
 count(*) AS orders
FROM orders
WHERE DATETIME BETWEEN '2014-04-01' AND '2014-07-01'
GROUP BY YEAR(DATETIME), MONTH(DATETIME)

mysql report sales

cfGrid – use full document width

One of the pains of using cfgrid is that the width has to be hard-coded in. As a developer, you can’t assume the user’s screen width, even if they are admins with directions/instructions. Fortunately, to get around this shortcoming is not too difficult.

First we’ll use javascript on the back office welcome page to get the screen width and save that as a cookie. In application.cfc we’ll test for that value first in the session scope, then in cookies, and take action appropriately. Finally in the template with a cfgrid we’ll insert that value as the width.

CMS Welcome Page

When the client signs in successfully, add some javascript to the <head> of the welcome page. Lets load jQuery to make this a bit simpler. Then add some nice cookie functions. At the end of the javascript, save a cookie ‘bodyWidth’ with value of the window’s width – minus 20 just for breathing room. This works within a frame, too, by the way. After that, you want to make sure that the previous (and perhaps incorrect) bodyWidth saved in the session is erased.

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript">
function createCookie(name, value, days) {
 if (days) {
  var date = new Date();
  date.setTime(date.getTime() + (days * 24 * 60 * 60 * 1000));
  var expires = "; expires=" + date.toGMTString();
 } else var expires = "";
  document.cookie = escape(name) + "=" + escape(value) + expires + "; path=/";
}

function readCookie(name) {
 var nameEQ = escape(name) + "=";
 var ca = document.cookie.split(';');
 for (var i = 0; i < ca.length; i++) {
  var c = ca[i];
  while (c.charAt(0) == ' ') c = c.substring(1, c.length);
  if (c.indexOf(nameEQ) == 0) return unescape(c.substring(nameEQ.length, c.length));
 }
 return null;
}

function eraseCookie(name) {
 createCookie(name, "", -1);
}

$(document).ready(function(){
 createCookie("bodyWidth", $(document).width()-20, 14);
});
</script>
<cfset structDelete(session, 'bodyWidth') />

Application.cfc

In the ‘OnRequestStart’ function of application.cfc you’ll need this snippet. It checks to see if there is a session variable of ‘bodyWidth’. If not, it looks for the cookie. If no cookie, default to 1067. Of course you can set that default to whatever you’d like.

<!--- GRID WIDTHS --->
<cfif not isDefined("session.bodyWidth")>
 <cfif isDefined("cookie.bodyWidth")>
  <cfset session.bodyWidth = cookie.bodyWidth>
 <cfelse>
  <cfset session.bodyWidth = 1067>
 </cfif>
</cfif>

Template with cfGrid

Now the easy part. In your cfGrid tag simply use the width stored in session.

...
width="#session.bodyWidth#"
...

Remove Google’s ‘Play’ button with Stylish

I can’t imagine the discussion over at Google to put the Play button in the black Google bar. “Oh yes, this is definitely more important than anything under the ‘More’ menu.”

Anyway – banish it for good with a simple Stylish style. Create a new style and use this as the content:

@namespace url(http://www.w3.org/1999/xhtml);
/* Remove google's Play button */

#gb_78 {
 display: none !important;
}

No more Play button. Back to Work!

ColdFusion tag usage syntax

While beta testing web development software I was asked to provide a list of self-closing CFML tags. After some research I found there are 4 types of tags:

  • Self-closing
  • Self-closing requiring the terminating forward slash, or can be containers
  • Self-closing, must never have the terminating forward slash
  • Containers

Hopefully this may help someone else. As of Adobe’s CF9, here are the tags:

Self Closing Tags

cfabort
cfajaximport
cfajaxproxy
cfapplet
cfapplication
cfargument
cfassociate
cfbreak
cfcalendar
cfcol
cfcollection
cfcontent
cfcontinue
cfcookie
cfdbinfo
cfdirectory
*^ cfdiv
cfdump
! cfelse
! cfelseif
cferror
cfexchangecalendar
cfexchangeconnection
cfexchangecontact
cfexchangefilter
cfexchangetask
cfexit
cffeed
cffile
cffileupload
cfflush
* cfformitem
cfftp
cfgridcolumn
cfgridrow
cfgridupdate
cfheader
cfhtmlhead
cfhttpparam
cfimage
cfimap
cfimport
cfinclude
cfindex
cfinput
cfinsert
* cfinvoke
cflocation
cflog
cfloginuser
cflogout
cfmailparam
cfmap
cfmapitem
cfmediaplayer
* cfmenuitem
cfmessagebox
cfmodule
cfNTauthenticate
cfobject
cfobjectcache
cfparam
* cfpdfform
cfpdfformparam
cfpdfparam
cfpdfsubform
* cfpod
cfpop
* cfprocessingdirective
cfprocparam
cfprocresult
cfprogressbar
cfproperty
cfqueryparam
cfregistry
cfreportparam
cfrethrow
cfreturn
cfschedule
cfsearch
cfset
cfsetting
cfsharepoint
cfslider
cfspreadsheet
cfsprydataset
* cfstoredproc
*^ cfthread
cfthrow
cftrace
cftreeitem
cfupdate
cfwddx
* cfzip
cfzipparam

Container Tags

cfcache
cfcase
cfcatch
cfchart
cfchartdata
cfchartseries
cfcomponent
cfdefaultcase
cfdocument
cfdocumentitem
cfdocumentsection
cfexchangemail
cfexecute
cffinally
cfform
cfformgroup
cffunction
cfgrid
cfhttp
cfif
cfinterface
cfinvokeargument
cflayout
cflayoutarea
cfldap
cflock
cflogin
cfloop
cfmail
cfmailpart
cfmenu
cfoutput
cfpdf
cfpresentation
cfpresentationslide
cfpresenter
cfprint
cfquery
cfreport
cfsavecontent
cfscript
cfselect
cfsilent
cfswitch
cftable
cftextarea
cftimer
cftooltip
cftransaction
cftree
cftry
cfwindow
cfxml

* These tags can also be container tags.
^ These tags when self-closing REQUIRE the terminating forward slash.
! These tags must never have the terminating forward slash.

Automate reporting spam to spamcop.net

Updated 05/31/2013

Reporting spam to SpamCop can get to be tiresome. The normal process is:

  1. Receive spam
  2. Copy the raw message
  3. Log into SpamCop.net
  4. Paste the message
  5. Wait for the verification message
  6. Mark the message as spam in Mail
  7. Delete the message
  8. Click the link in the verification message
  9. Wait for spamcop.net’s nag screen to go away
  10. Submit the spam report
  11. Delete the verification message from Mail

Luckily much of it can be automated with Mac Mail and some AppleScripting. The solution I’m posting boils it down to:

1. Receive spam
2. Fire a script via the applescript menu
9. Wait for SpamCop.net’s nag screen to go away
10. Submit the spam report

As you can see, 7 steps are handled automatically.

Instructions

The first thing you have to do is grab the scripts from github: SpamCop Deputy

Next set up an account at SpamCop.net. When you log in, set the expiration to 1 year.

Enable the AppleScript menu.

1) Open “AppleScript Editor.app”. It will either be in your Applications folder or Utilities folder.
2) Edit General preferences, and turn on “Show Script menu un menu bar”.
Now you’ll have an easy way to fire off the initial script. These items are only available when Mail is the front most application.

Set up a script to send SpamCop the raw source of the spam messages.

1) Create this folder if it does not exist: ~/Library/Scripts/Applications/Mail
2) Drop the attached “SpamCopNotify.scpt” into that folder.
3) Edit line 1 with your unique SpamCop email submission address.
This script will get the raw source of all messages selected in Mail and send them to SpamCop, mark the message as read, mark the message as spam, then delete the message.

Set up a script to handle the SpamCop verification email.

1) Drop the attached “SpamCopIn.scpt” file into ~/Library/Application Scripts/com.apple.mail .
This script will automatically ‘click’ the verification link and delete the verification email.

Add a mail rule to fire SpamCopIn.scpt when the verification email is received.

1) Create a new mail rule
2) If all of the following are met:
+ Received is less than 2 days old (that’s a spamcop rule)
+ From contains “[email protected]
+ Message content contains “http://www.spamcop.net/sc?id=”
3) Perform the following actions:
+ Run AppleScript “~/Library/Scripts/Applications/Mail/SpamCopIn.scpt”
+ Play Sound [any] (this is optional)
4) Do Not Apply when prompted to do so when you save.

Now we’re all ready to go.

Next time you get spam…
1) Select the message(s)
2) From the AppleScript menu, select “SpamCopNotify.scpt”
SpamCopNotify.scpt will get the raw source of all messages selected in Mail and send them to SpamCop.
The messages will be marked as read, marked as spam then deleted.
SpamCop will reply with a verification message(s) shortly.
Mail will evaluate the verification message(s) with it’s rules and launch the SpamCopIn.scpt script.
SpamCopIn.scpt will find the verification link and launch it in your default browser as a new window/tab.
The message will be marked as read then deleted.
3) When your browser opens the SpamCop site, wait for the nag screen to go away then press “Process Spam”.

Trimming a decimal with coldfusion & regex

Today I had to import lots of measurements into a mySql table. The columns allow 3 places after the decimal. The data has mixed precision – some values use all 3 places, others use none. To make the output look clean I didn’t want to show all 3 places all the time. So numberFormat() was out. And unlike PHP, coldFusion’s trim() function does not take any parameters (trim trailing zeros and periods). I came up with using two regular expressions to remove any trailing zeros, then the trailing period if any.

Here is some example data:

  • 6.000
  • 0.375
  • 0.500

When using numberFormat(), the output is:

  • 6
  • 0
  • 1

… and numberFormat(val, 0.000) gives:

  • 6.000
  • 0.375
  • 0.500

Blech. So here’s the function that gave exactly what I wanted:

<cffunction name="trimDecimal">
 <cfargument name="value">
 <cfreturn REReplace(REReplace(value, "0+$", "", "ALL"), ".+$", "")>
</cffunction>

… trimDecimal(val) resulting in…

  • 6
  • 0.375
  • 0.5

Perfect.

Return a query of all CF Scheduled tasks

I was working on a scheduled task in a shared hosting environment. Which is pretty frustrating when you don’t have access to the CF Administrator.

Googling, I found some functions that would return information about scheduled tasks via coldfusion.server.ServiceFactory. That of course would not work since shared hosting environments have that locked down.

Then I came across an old post by Ben Forte. Instead of ServiceFactory he used an undocumented task called ‘__list’. It almost worked… except that it errored out on some task attributes. I guess a mismatch in versions of CF is to blame.

So I took that undocumented call, then rebuilt the rest entirely. I basically turned the data into tab-separated-values. I haven’t gotten around to learning reFind(), etc yet so please excuse the many replace() functions. It’s very low tech, but it works.

It won’t matter if you expect extra column names than __list gives (those columns in the returned query will be [empty string]s). And it also won’t matter if __list gives more than you’ve expected (those extra columns won’t be in the returned query). In either case, there will be no errors.

<cffunction
	name="getCFScheduledTasks"
	returntype="query"
	output="no"
	hint="Returns a query of CF Scheduled tasks on the server"
>
<!---
Thanks to Ben Forte for posting the undocumented call '__list'
--->
	<cfsavecontent variable='tasks'>
	    <cfschedule action='run' task='__list'>
	</cfsavecontent>

	<!--- TRIM WHITESPACE --->
	<cfset tasks = trim(tasks)>
	<cfset tasks = replace(tasks, chr(10), '', 'all')>

	<!--- REMOVE FIRST AND LAST BRACKETS --->
	<cfset tasks = mid(tasks, 2, len(tasks)-3)>

	<!--- CHANGE THE TASK DELIMETER --->
	<cfset tasks = replace(tasks, '}}},', '}}#chr(10)#', 'all')>

	<!--- CHANGE THE COLUMN DELIMETERS --->
	<cfset tasks = replace(tasks, '={{', chr(9), 'all')>
	<cfset tasks = replace(tasks, '},', '}#chr(9)#', 'all')>

	<!--- REMOVE THE ROW TRAILING BRACKETS --->
	<cfset tasks = replace(tasks&chr(10), '}}#chr(10)#', '}#chr(10)#', 'all')>

	<!--- REMOVE THE COLUMN TRAILING BRACKETS --->
	<cfset tasks = replace(tasks, '}#chr(9)#', chr(9), 'all')>

	<!--- TASK DATA COLUMNS --->
	<cfset columns = 'task,start_date,start_time,last_run,end_time,interval,operation,url,resolveurl,request_time_out,username,password,http_port,path,proxy_server,http_proxy_port,file,disabled,paused,publish'>

	<!--- CREATE A QUERY --->
	<cfset cfScheduledTasks=QueryNew(columns)>

	<!--- LOOP OVER THE ROWS & COLUMNS, ADDING TO THE QUERY --->
	<cfloop list="#tasks#" delimiters="#chr(10)#" index="row">
		<cfset QueryAddRow(cfScheduledTasks)>
		<cfloop list="#columns#" index="column">
			<cfloop list="#row#" delimiters="#chr(9)#" index="data">
				<cfif spanExcluding(data, '=') eq column>
					<cfset QuerySetCell(cfScheduledTasks, column, replace(data, column&'={', ''))>
				</cfif>
			</cfloop>
		</cfloop>
	</cfloop>

	<!--- WWWEEEE! --->
	<cfreturn cfScheduledTasks>
</cffunction>

<cfset cfScheduledTasks = getCFScheduledTasks()>
<cfdump var='#cfScheduledTasks#'>

Ben’s original code is at: http://www.forta.com/blog/index.cfm/2006/8/28/GetScheduledTasks-Function-Returns-Scheduled-Task-List

MySQL: Ignore null values when ordering results

Fast answer: use IFNULL() in your select statement.

Lets say you have some products that have a length attribute which is recorded in two different ways: inches and feet. In your CMS, the client enters EITHER inches OR feet. When generating a list of products sorted by length, you’d want the ‘inch’ products up top and the ‘feet’ products under them. At first, you’d probably try this:

SELECT id, title, lenIN, lenFT
FROM products
ORDER BY lenIN, lenFT

But that would give you the ‘feet’ products first since the inch values would be null. A second attempt:

SELECT id, title, lenIN, lenFT
FROM products
ORDER BY lenIN DESC, lenFT

That of course puts the products in the wrong ordering. Here is the fix:

SELECT id, title, IFNULL(lenIN, 1000) as tempIN, lenIN, lenFT
FROM products
ORDER BY tempIN, lenIN, lenFT

It comes from the magic of:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2. So when inches is null, a value of 1000 is used. I picked ‘1000’ because I’m sure no product will be 1000 inches long (feet would surely be used at that point). The result is the product which have inches filled will be put at the top of the list, ordered correctly, followed by the products where inches were blank (and are now 1000 inches).

Change the priority of incoming mail (incl. Apple Mail)

… or, ‘the sender that cried emergency’.

Before I get into this article I’d first like to point out that this solution was not born out of anger, hostility, or any other negative vibe. It’s simply a usability and project management issue.

The Problem

We probably all have a sender or two that abuse the Priority setting of email messages. Perhaps they honestly think many of their message are actually very important. And that’s fine, priority can be objective. But it does cause a problem when checking email.

  • Just like The Boy Who Cried Wolf, a sender that sets too many messages to High Priority runs the risk of being ignored. That certainly is not good for the sender.
  • When other senders don’t use the priority setting, those messages get deferred. Not necessarily a good thing either.
  • Turning off the Priority column to cut down the noise makes legitimately important messages look normal priority.
  • Turning off the Priority column to cut down the noise also gets rid of the ‘Flagged’ designation.

So basically speaking, someone who abuses the priority setting can really muck up the works. And you can’t really them about it either.

Some email programs let you change the priority of incoming mail. I use Apple’s Mail as a mail client and it does not. I checked out scripts, plugins, even a workaround of moving the message to the Drafts mbox, change the priority, then move it back to the in mbox… which is not good because then it sets you as the sender. D’oh! I also tried editing the emlx file on the hard drive but that didn’t work, I guess because I’m using IMAP. What then can we do?

The Fix

What we need to do is target those few senders, reset the priority of their incoming messages, and add a visual clue that it was changed. All other senders should not be affected.

The fix is dependent on the mail server software. I’ll show how to do this with SmarterMail since that’s what I use. We’ll set up a Content Filter to look for high priority messages from a certain sender, and add an action to reset the message’s priority.

1) Click the Settings button, expand My Settings, expand Filtering, then click Content Filtering. Your list may be blank. Mine already has three set up. Just click New to get started.

2) Turn on ‘From specific address’ and ‘Flagged as high priority’ and then click Next.

3) Make sure ‘AND’ is selected. Enter email addresses for the target senders.
Click Next.

4) Now finally the magic. Give this filter a Name. Turn on ‘Add Header’ and enter in whichever header name/value your email program listens to. For priority, Apple Mail uses the mail header “X-Priority”.  That is what I set to 0 (zero). Other mail clients use “Importance” and “X-MSMail-Priority”. Either of those you set to “Medium” instead of 0.
I also prefix the subject with an asterisk. That way I still know the sender thought this message was important, without gumming up everything else.
Click Save.

5) Send yourself a high priority test message. When it comes in, you should see that the Flag column is blank and there is now an asterisk in front of the Subject. When you see the asterisk, feel free to giggle like a school girl, ‘tee hee!’

Also take a look at the raw source. You’ll see that the the original X-Priority of 1 was overwritten with a zero.

Hooray, it works! =)

Want to read more?