Javascript Compression — Tools and Process

This article is a review of the tools and processes that I have tested and gives plusses and minuses of each.

Software Uncompressed Compressed Percent Comment
Closure Compiler 39K 16K 59% with ADVANCED_OPTIMIZATIONS
YUI Compressor 39K 22K 44%
perl-Javascript-Minifier 39K 25K 36%

 

perl-Javascript-Minifier
Since CPAN library’s Javascript-Minifier and CSS-Minifier are immediately available linux tools they are a good starting point. The Javascript-Minifier is simple to use. Here is a script that you can try to see how it works:

#!/usr/bin/perl
 use JavaScript::Minifier qw(minify);
 my($iFile)=$ARGV[0];
 my($oFile)=$iFile;
 $oFile=~ s/[.]js//;
 $oFile="${oFile}_perl_min.js";
 open(INFILE, "$iFile") or die;
 open(OUTFILE, ">$oFile") or die;
 minify(input => *INFILE, outfile => *OUTFILE);
 close(INFILE);
 close(OUTFILE);

In my tests, it didn’t break my code, but did generate errors because of incorrectnesses in my code. I used the google chrome jslint plugin to find the errors. jslint only works on pure javascript, but strings are not parsed. Thus you can use php to initialize variables by putting the php code inside of quotes, and still check it with jslint.

 

YUI Compressor
The YUI Compressor is Yahoo’s library, and works better than perl-Javascript-Minifier. Here is an example command for using YUI Compressor:

java -jar yuicompressor-2.4.7.jar --type js -o filename_yui_min.js filename.js

A nice feature of the yuicompressor is that it can accept javascript strings from the command line. This makes it simple to script. It’s goal is to not break code, and in my tests this was observed to be true.

 

Closure Compiler
The google closure compiler is the most advanced of the ones that I tested. It has a simple mode that doesn’t break code and an option for ADVANCED_OPTIMIZATIONS that produces very compressed code. Here is an example command for using the closure compiler in simple mode:

java -jar compiler.jar --js filename.js --js_output_file filename_closure_min.js --externs externs.js

And similarly for advanced mode:

java -jar compiler.jar --compilation_level ADVANCED_OPTIMIZATIONS --js filename.js --js_output_file filename_closure_min.js --externs exterms.js

Similar to perl-Javascript-Minifier, closure compiler only works on pure javascript files. Because of the effectiveness of the optimizations that it does, it can break code. To effectively use it, you need to design your javascript with minification in mind. Typically you want to use your javascript as a library (i.e. as handers for events such as mouse clicks) to do this, you need to add a small amount of code that preserves the function names that will be available to external scripts. Similarly if you want to use external libraries in your library, you need to add extern declarations that will preserve the external symbols. There are less modifications required if you use the simple mode than for the advanced mode. I wanted to use advanced mode for some script that contains jQuery calls (including jQuery Mobile), but wasn’t able to find a way to preserve the jQuery and $ symbols. I tried using –externs with the externs file available as an addon from google svn, but this didn’t solve the problem. Therefore I recommend using simple mode for files containing jQuery and advanced mode for files that do not.

 

In summary of the tools reviewed google closure compiler is the most effective, perl-Javascript-Minifier is the least likely to break code, and yuicompressor is a compromise between these extremes. Additionally each of these tools can be run locally on your machine.

Guidelines for writing great articles

Part of my efforts with the Mississippi Magic Magazine involve setting up guidelines for writing great articles.  If you are wondering why a technologies consultant is posting guidelines on writing great articles then let me explain.  We are in the business of creating success in our constituents that we partner with through consulting and development.  That being said… Mississippi Magic Magazine provides an outlet of expression for people and businesses in Mississippi but it does not employ writers to produce magazine content.  Therefore, all the writing done on the Mississippi Magic Magazine is done by volunteer writers that have a vested interest in the community.  From business owners to pastors of churches, people active in leadership in the community provide all of the content found in magazine.  To make this a profitable venture for each of them, we’ve found it necessary to publish a guidelines for writing great articles.

Benefits:

  • People like to do business with those they are familiar with.   Through connecting with our community through your articles, you’ll lower the barriers that people have to traditional broadcast style advertising communications.  .
  • Articles will be broadcasted to the RSS subscription readership.
  • Articles will be indexed by search engines so that anyone looking the information you provided will find it and you on the internet.
  • Articles will spread in a viral method through through social media sites such as twitter, facebook, and linked in.
  • Unlike traditional media, articles written will be available online well into the indefinite future.
  • Articles will link your thoughts, expertise, and methods to your perceived value as a solutions provider and allow people to reach you.
  • Articles always make the front page when they are published.
Good practices:
  • Offering needed information to make an informed decisions.
  • Offering solutions by showing how to solve a problem.
  • Providing lists of methods or resources.
  • Expounding upon a subject.
  • Offering information about events by featuring information, pictures, and video covering people in the community.
  • Praising and pointing out the good about others.
  • Simplifying complex information, instructions, or events.
Avoid:
  • Slandering others.
  • Defaming others.
  • Advertising your products or services.
  • Soliciting business.
Using these methods, community leaders can provide invaluable content that leads people in the community to them as a provider of services and goods.  Through the relationship formed between the reader and author in the magazine articles these leaders have an inroad to the reader when the reader seeks to find a provider for a need or want that the author can fulfill.
This style of marketing defined in these guidelines for writing great articles are known today as inbound marketing.  By using them you’ll get found on the internet and connect with your customer.
– Joel

Excel spreadsheets with PHP – a PHPExcel overview

Writing Excel spreadsheets with PHP using PHPExcel:  The pros and cons

PHPExcel is a php library for reading and writing excel spreadsheets.  It is very well documented and has good community support.  When searching the web for an open source solution for writing spreadsheets in PHP I reviewed everything from PHPClasses to Pear and finally I came across this package.  It’s the most up to date looking project out there for excel spreadsheets with PHP.  I didn’t want to use a project that was in the twilight of its development cycle so I opted to try PHPExcel.  Here’s a a brief overview of what I found:

Pros – The good points:

  • Very well documented.
  • Good examples
  • Support forum with active participation.
  • Supports many of the builtin excel functions.
  • Easy to style
  • Supports modern Excel formats plus csv, html, and pdf
  • Write spreadsheets
  • Read spreadsheets
Cons – what I couldn’t stand:
  • SLOW….  This library takes considerable time to produce a spreadsheet when memory conserving disk caching is used.  Consider running your process in the background.
  • Memory Intensive  PHPExcel offers disk caching to reduce its in memory size, but it basically is an in memory spreadsheet and must load completely into memory while being before being written to file.  This can considerably hinder your performance as the spreadsheet object and writer object can take over 25 mg of ram while the program is running with an additional 1 kb of ram per cell loaded in memory.  For an example of how this can impact your server or hosting service consider the load of a 50 column 10000 row spreadsheet which would take 525 mb of ram to load when writing to file.  While its possible to set the scripts memory high enough to handle this you’ll find that if several of these scripts try to run at the same time you’ll have a mess that could crash your server.
  • Write to file is batched processed at the end of the script and happens all at one time.  Regardless of your disk caching settings the entire spreadsheet is loaded into memory and then written to the file.  This creates a serious bottleneck if you have a large file and will oftentimes crash your script.  The only exception to this is when PHPExcel writes a csv file in which it allows for a more granular approach to memory to file writing.
Workarounds that will reduce memory usage and improve speed.
  • Set a cells value explicitly.  Setting the cells value and data type explicitly is much faster than writing cell values for PHPExcel to determine the data type of.
  • Disable formula precalculation by setting it to false.
  • Use disk caching.
  • Don’t try to use it to create reports from large data sets.
In summary:  If you are trying to read and write to small spreadsheets with less than two hundred and fifty thousand fields or cells then this library will be a good fit for your project.  It’s great for summary reporting!!  However if you’ve got extremely large record sets that you need to send to a spreadsheet you’ll need a different approach.  I’ll be writing about how we did that in the next blog post.
Stay tuned and may the source be with you!!
– Joel

PHP and curl for remote site data extraction

Sometimes you’ll need to get data from another site on the internet.  In this article we’ll go over a quick and easy how to for using php and curl for remote site data extraction.

To get things started we’ll need to initialize a curl object and set some parameters so it can act as a web browser and log into the targeted remote site.

        // Initialize cURL
        $Curl_Obj = curl_init(); 

        // Enable Posting.
        curl_setopt($Curl_Obj, CURLOPT_POST, 1);

        // Enable Cookies
        curl_setopt ($Curl_Obj, CURLOPT_COOKIEJAR, 'cookie.txt'); 

        // Set the browser you will emulate
        $userAgent = 'Mozilla/5.0 (X11; Linux i686; rv:2.0.1) Gecko/20100101 Firefox/4.0.1';
        curl_setopt($Curl_Obj, CURLOPT_USERAGENT, $userAgent);

        // Don't include the header in the output.
        curl_setopt ($Curl_Obj, CURLOPT_HEADER, 0);

        // Allow referer field when following Location redirects.
        curl_setopt($Curl_Obj, CURLOPT_AUTOREFERER, TRUE);

        // Follow server redirects.
        curl_setopt($Curl_Obj, CURLOPT_FOLLOWLOCATION, 1);

        // Return output as string.
        curl_setopt ($Curl_Obj, CURLOPT_RETURNTRANSFER, 1);

You can find a complete list of php curl options at http://www.php.net/manual/en/function.curl-setopt.php if you need more details.

Now that we’ve got our curl object set up lets post some login credentials to a login script.  To do this we’ll need to get the input ids from the login form and supply them values and setup the post with the curl object.  Then we’ll post the credentials to the script detailed in the action of the login form.  Here’s how we do it:

       // Set up post fields from login form.
       curl_setopt($Curl_Obj, CURLOPT_POSTFIELDS, "UserFieldId=UserName&$PasswordFieldId=$Password&AnotherFieldId=$AnotherValue");

       // Set the url to which the data will be posted.
       curl_setopt ($Curl_Obj, CURLOPT_URL, 'http://yoursite.com/login.asp');

       // Execute the post and get the output.
       $output = curl_exec ($Curl_Obj);

       // Empty the post fields so you don't re-post on the next request.
       curl_setopt($Curl_Obj, CURLOPT_POSTFIELDS, "");

At this point we’ve obtained the output of the login attempt and we can parse it to see if it was successful using php string manipulation functions.  For the sake of simplicity* I’ll leave that part to you.  Once you’ve insured that you’ve logged in you can proceed to navigate to the next url using the steps below:

       // Set curl object url option.
       curl_setopt ($Curl_Obj, CURLOPT_URL, $Get_This_Url);
       // Execute query and obtain content.
       $output = curl_exec($Curl_Obj);

Finally you’ve downloaded the content from the page that was not accessible prior to your virtual login.  You can parse it with php’s string functions or use xpath to read its content if you’d like.  Don’t forget to close the curl object before you exit your script!!!

// Close curl object.
curl_close ($Curl_Obj);

 

Happy coding and please use responsibly.

– Joel

 

* Here’s a simple string comparison function you can use when using php and curl for remote site data extraction.

function extract_snippet($html, $Start_Term, $End_Term, $PrintOutput = false)

    {
        $start = strpos($html,$Start_Term);
        if (!$start === false)
        {
            $end = strpos($html,$End_Term,$start);
            $T_Sel = $start + strlen($Start_Term);
            $snippet = substr($html,$T_Sel,$end-$T_Sel);
            if ($PrintOutput)
            {
                print "\n<br>$snippet</br>";
            }
            return $snippet;
        }
        return false;
    }

A quick php data sanitation guide

PHP data sanitation is the method of testing inputs and output for acceptable ranges of data to insure that a script will produce the desired result.  Data sanitation is sometimes referred to as a sanity check as insane things tend to happen when a script gets values it was never intended to process or renders values to the users browser that breaks the html document or javascript functionality of the page or worse yet cause the page to work in a manner it was never intended to.  PHP data sanitation for data input into scripts when the user is passing values in can be easily accomplished  in three steps and data sanitation for output to the users is even simpler.

Three steps for a php scripts data sanitation on input:

Step 1: Check for the existence of the required inputs from the four types of inputs you can use $_REQUEST[‘var_name’], $_POST[‘var_name’], $_GET[‘var_name’], and $_COOKIE[‘var_name’]. Use the function isset() to determine if the variable is set and doesn’t have a null value.  Use the function isempty() if you’d like to check to do the same type of test while accepting null values.  Tip don’t use $_REQUEST as you cannot validate where the data came from.

Step 2: Check inputs for approved data types. If the type doesn’t match reject the input value as tainted.  Functions for checking data types are as follows:

is_object
is_infinite
is_real
is_int
is_string
is_long
is_scalar
is_nan
is_double
is_uploaded_file
is_array
is_bool 

Step 3: Whitelist the input values by checking them for acceptable values and reject inputs that do not meet your specified input parameters.  Check for acceptable values using character type functions for strings and comparison operators for numeric values.   Ctype functions include:

  • ctype_alnum — Check for alphanumeric character(s)
  • ctype_alpha — Check for alphabetic character(s)
  • ctype_cntrl — Check for control character(s)
  • ctype_digit — Check for numeric character(s)
  • ctype_graph — Check for any printable character(s) except space
  • ctype_lower — Check for lowercase character(s)
  • ctype_print — Check for printable character(s)
  • ctype_punct — Check for any printable character which is not whitespace or an alphanumeric character
  • ctype_space — Check for whitespace character(s)
  • ctype_upper — Check for uppercase character(s)
  • ctype_xdigit — Check for character(s) representing a hexadecimal digit

Comparison operator include:

Example Name Result
$a == $b Equal TRUE if $a is equal to $b after type juggling.
$a === $b Identical TRUE if $a is equal to $b, and they are of the same type. (introduced in PHP 4)
$a != $b Not equal TRUE if $a is not equal to $b after type juggling.
$a <> $b Not equal TRUE if $a is not equal to $b after type juggling.
$a !== $b Not identical TRUE if $a is not equal to $b, or they are not of the same type. (introduced in PHP 4)
$a < $b Less than TRUE if $a is strictly less than $b.
$a > $b Greater than TRUE if $a is strictly greater than $b.
$a <= $b Less than or equal to TRUE if $a is less than or equal to $b.
$a >= $b Greater than or equal to TRUE if $a is greater than or equal to $b.

Pulling it all together with some pseudo code:  Encapsulate in function: IF isset(YourPostVar) AND isdigit(YourPostVar) AND YourPostVar >= 0 THEN return YourPostVar Else return false;  As you can see we combined all the stages here to insure that the numeric value we received was set, and was a digit with a value of zero or greater.

Note: When inputing data into databases use the dbms’s escape string functionality to insure that variables are properly escaped.  If the dbms doesn’t have an available escape string you can use addslashes().

Output sanitation is very simple. Always encapsulate output to the user with htmlentites() or htmlspecialchars() when sending data straight from inputs or a database.

Hope this helps.

Joel Caton

Browser Compatibility and Acid3

The question:  Can users be persuaded to change browsers?

Recently I asked a question on linkedin about how to encourage users to upgrade or change their browsers.  I got a lot of interesting answers and some good ideas, but nothing that put the issue to rest for me.  I’m still thinking about browser compatibility.  The issue at the heart of this whole deal is the disparaging levels of support that browser producers have for css and HTML.  I believe that the answer to the question can be found by educating the end user so they can make the best choices.

Browser compatibility problems

From a development perspective browser compatibility issues make it harder to write web applications that perform as designed because not every browser is going to function the same.  Some of them don’t recognize all of the css properties and don’t use all of the functionality of html.  This is compounded by the fact that CSS2, CSS3, and html5 are not finished works but have been adopted in part by each of the major browsers to varying extents and in different ways.  The lack of timeliness for the production of standards is a bottle neck the progress of the internet.

A browser compatibility benchmark is needed

Enter the Acid3 test by WaSP.  This tool is a great piece for end user education because it scores the browsers compatibility with web standards in an easy to understand score and offers an interactive model.  Users visiting the url where the test is located watch an animation take place on their screen while a score starting at 0 / 100 increments as the browser is tested.  Links with this graphical tool coupled with the right compelling offer could be used encourage users to seek the best possible browser or at least upgrade their current version.  Wikipedia has a great writeup on the Acid3 test if you’d like a little more information on what it does and how it works.  Testing your browser is as easy as visiting the site.  An example of the test result done on Firefox is listed below.  You may click the image to test your browser.

Firefox 4.01 Acid3 Test Results

 

Why should I bother?

The future of distributed software lies within html, css, and JavaScript.  Browsers are the containers in which these applications run and the environment in which they subsist.  By educating the end user and allowing them to make the best decision you will force browser developers to increase their adoption of existing standards and cause the W3C increase the pace at which it finalizes standards.  Over time barriers caused by browser compatibility issues will dissolve and innovation will occur at a much faster rate and improve the end users online options.  While the education of the market may seem to some to be a distraction from business goals, I believe that over time it will broaden the range and increase the depth of opportunities available via the internet.  Additionally consumers which read your educational material will appreciate the fact that you an innovator that cares about their experience and wants them to have the best.

Innovation Lives!!  Stagnation Dies!!– Joel

 

 

Developments

A quick update on developments lately.

The business card lead conversion project

This project is hardly finished but it’s functional enough that I’m going to start using it.  I’ll be emailing the information for now but my long term plans are to integrate site registration with this process and offer the reports and worksheets on a perpetual basis to the end user.

The Ideal Customer Survey

I’m looking at ways to improve the questionnaire as it currently is written with a focus on business to business and I’d like to add some business to consumer questions or possibly break this into b2b and b2c surveys.

Site Graphics Update:

New graphics are forthcoming as I’m looking to take a more laid back approach with the site.  I’m integrating a nature scene for the background and move the navigation to a floating panel on the right side of the page.  The catontech eye logo is being revamped so it will be more understandable and less weird.

Thanks for the Referrals

A word of thanks to Janet Kelly of Kellybucks and JK Real Estate for the referral to Debbie Carrol her staff at Boaz Tax Sales Properties.  Its great to be able to help friends and Janet you’ve certainly helped me.  Debbie, Wanda, Laura, and Monica are great and I really enjoy helping them out.

Joel

SQL tables for website development of html forms

A programmers resource: SQL tables for website development of html forms using selects and multiselects.

The following sql statements create tables that you can use to generate html selects and multi selects for web application menus.  I’ve posted some of the most common tables that I think are needed.  Let me know if there is one that I don’t have listed and I’ll add it.  You can copy these sql statements to a file or execute them directly to create the tables.

States:

CREATE TABLE `States` (
`Abr` varchar(2) NOT NULL,
`Name` varchar(56) NOT NULL,
`Sort_Order` smallint(6) NOT NULL,
PRIMARY KEY  (`Abr`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `States` VALUES(‘AL’, ‘Alabama’, 1);
INSERT INTO `States` VALUES(‘AK’, ‘Alaska’, 2);
INSERT INTO `States` VALUES(‘AZ’, ‘Arizona’, 3);
INSERT INTO `States` VALUES(‘AR’, ‘Arkansas’, 4);
INSERT INTO `States` VALUES(‘CA’, ‘California’, 5);
INSERT INTO `States` VALUES(‘CO’, ‘Colorado’, 6);
INSERT INTO `States` VALUES(‘CT’, ‘Connecticut’, 7);
INSERT INTO `States` VALUES(‘DE’, ‘Delaware’, 8);
INSERT INTO `States` VALUES(‘DC’, ‘District Of Columbia’, 9);
INSERT INTO `States` VALUES(‘FL’, ‘Florida’, 10);
INSERT INTO `States` VALUES(‘GA’, ‘Georgia’, 11);
INSERT INTO `States` VALUES(‘HI’, ‘Hawaii’, 12);
INSERT INTO `States` VALUES(‘ID’, ‘Idaho’, 13);
INSERT INTO `States` VALUES(‘IL’, ‘Illinois’, 14);
INSERT INTO `States` VALUES(‘IN’, ‘Indiana’, 15);
INSERT INTO `States` VALUES(‘IA’, ‘Iowa’, 16);
INSERT INTO `States` VALUES(‘KS’, ‘Kansas’, 17);
INSERT INTO `States` VALUES(‘KY’, ‘Kentucky’, 18);
INSERT INTO `States` VALUES(‘LA’, ‘Louisiana’, 19);
INSERT INTO `States` VALUES(‘ME’, ‘Maine’, 20);
INSERT INTO `States` VALUES(‘MD’, ‘Maryland’, 21);
INSERT INTO `States` VALUES(‘MA’, ‘Massachusetts’, 22);
INSERT INTO `States` VALUES(‘MI’, ‘Michigan’, 23);
INSERT INTO `States` VALUES(‘MN’, ‘Minnesota’, 24);
INSERT INTO `States` VALUES(‘MS’, ‘Mississippi’, 25);
INSERT INTO `States` VALUES(‘MO’, ‘Missouri’, 26);
INSERT INTO `States` VALUES(‘MT’, ‘Montana’, 27);
INSERT INTO `States` VALUES(‘NE’, ‘Nebraska’, 28);
INSERT INTO `States` VALUES(‘NV’, ‘Nevada’, 29);
INSERT INTO `States` VALUES(‘NH’, ‘New Hampshire’, 30);
INSERT INTO `States` VALUES(‘NJ’, ‘New Jersey’, 31);
INSERT INTO `States` VALUES(‘NM’, ‘New Mexico’, 32);
INSERT INTO `States` VALUES(‘NY’, ‘New York’, 33);
INSERT INTO `States` VALUES(‘NC’, ‘North Carolina’, 34);
INSERT INTO `States` VALUES(‘ND’, ‘North Dakota’, 35);
INSERT INTO `States` VALUES(‘OH’, ‘Ohio’, 36);
INSERT INTO `States` VALUES(‘OK’, ‘Oklahoma’, 37);
INSERT INTO `States` VALUES(‘OR’, ‘Oregon’, 38);
INSERT INTO `States` VALUES(‘PA’, ‘Pennsylvania’, 39);
INSERT INTO `States` VALUES(‘RI’, ‘Rhode Island’, 40);
INSERT INTO `States` VALUES(‘SC’, ‘South Carolina’, 41);
INSERT INTO `States` VALUES(‘SD’, ‘South Dakota’, 42);
INSERT INTO `States` VALUES(‘TN’, ‘Tennessee’, 43);
INSERT INTO `States` VALUES(‘TX’, ‘Texas’, 44);
INSERT INTO `States` VALUES(‘UT’, ‘Utah’, 45);
INSERT INTO `States` VALUES(‘VT’, ‘Vermont’, 46);
INSERT INTO `States` VALUES(‘VA’, ‘Virginia’, 47);
INSERT INTO `States` VALUES(‘WA’, ‘Washington’, 48);
INSERT INTO `States` VALUES(‘WV’, ‘West Virginia’, 49);
INSERT INTO `States` VALUES(‘WI’, ‘Wisconsin’, 50);
INSERT INTO `States` VALUES(‘WY’, ‘Wyoming’, 51);

 

Age Range:

CREATE TABLE `AgeRange` (
`ID` tinyint(4) NOT NULL,
`Range` varchar(128) NOT NULL,
PRIMARY KEY  (`ID`),
UNIQUE KEY `Range` (`Range`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `AgeRange` VALUES(0, ‘Under 18′);
INSERT INTO `AgeRange` VALUES(1, ’18 to 24′);
INSERT INTO `AgeRange` VALUES(2, ’25 to 29′);
INSERT INTO `AgeRange` VALUES(3, ’30 to 34′);
INSERT INTO `AgeRange` VALUES(4, ’35 to 39′);
INSERT INTO `AgeRange` VALUES(5, ’40 to 49′);
INSERT INTO `AgeRange` VALUES(6, ’50 to 59′);
INSERT INTO `AgeRange` VALUES(7, ’60 to 69′);
INSERT INTO `AgeRange` VALUES(8, ’70 to 80’);
INSERT INTO `AgeRange` VALUES(9, ‘Over 80’);

Organizational Role

CREATE TABLE `Organizational_Role` (
`ID` tinyint(4) NOT NULL,
`Position` varchar(255) NOT NULL,
UNIQUE KEY `Position` (`Position`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `Organizational_Role` VALUES(0, ‘Owner / Operator’);
INSERT INTO `Organizational_Role` VALUES(1, ‘General Management’);
INSERT INTO `Organizational_Role` VALUES(2, ‘Administrative Support’);
INSERT INTO `Organizational_Role` VALUES(3, ‘Marketing’);
INSERT INTO `Organizational_Role` VALUES(4, ‘Finance’);
INSERT INTO `Organizational_Role` VALUES(5, ‘Operations’);
INSERT INTO `Organizational_Role` VALUES(6, ‘Sales’);
INSERT INTO `Organizational_Role` VALUES(7, ‘IT’);
INSERT INTO `Organizational_Role` VALUES(8, ‘Legal / Regulator’);
INSERT INTO `Organizational_Role` VALUES(9, ‘Customer Service’);
INSERT INTO `Organizational_Role` VALUES(10, ‘Public relations / corporate communications’);
INSERT INTO `Organizational_Role` VALUES(11, ‘Accounting’);
INSERT INTO `Organizational_Role` VALUES(12, ‘Cross-functional role’);

Employment Level

CREATE TABLE `EmploymentLevel` (
`ID` tinyint(4) NOT NULL,
`Level` varchar(128) NOT NULL,
PRIMARY KEY  (`ID`),
UNIQUE KEY `Level` (`Level`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `EmploymentLevel` VALUES(0, ‘CEO, president, owner, senior partner, principal’);
INSERT INTO `EmploymentLevel` VALUES(1, ‘CXO other than CEO (CFO, CIO, etc.)’);
INSERT INTO `EmploymentLevel` VALUES(2, ‘Partner / senior executive’);
INSERT INTO `EmploymentLevel` VALUES(3, ‘Middle Manager’);
INSERT INTO `EmploymentLevel` VALUES(4, ‘Supervisor’);
INSERT INTO `EmploymentLevel` VALUES(5, ‘Professional (consultant, legal, accountant, programmer, etc.)’);
INSERT INTO `EmploymentLevel` VALUES(6, ‘Front-line employee’);
INSERT INTO `EmploymentLevel` VALUES(7, ‘Adminstrative staff’);
INSERT INTO `EmploymentLevel` VALUES(8, ‘Student’);
INSERT INTO `EmploymentLevel` VALUES(9, ‘Not employed’);
INSERT INTO `EmploymentLevel` VALUES(10, ‘Retired’);

Number of Employees

CREATE TABLE `NumberOfEmployees` (
`ID` tinyint(4) NOT NULL,
`Range` varchar(96) NOT NULL,
PRIMARY KEY  (`ID`),
UNIQUE KEY `Range` (`Range`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `NumberOfEmployees` VALUES(0, ‘1’);
INSERT INTO `NumberOfEmployees` VALUES(1, ‘2’);
INSERT INTO `NumberOfEmployees` VALUES(2, ‘3’);
INSERT INTO `NumberOfEmployees` VALUES(3, ‘4 to 9′);
INSERT INTO `NumberOfEmployees` VALUES(4, ’10 to 16′);
INSERT INTO `NumberOfEmployees` VALUES(5, ’17 to 29′);
INSERT INTO `NumberOfEmployees` VALUES(6, ’30 to 49′);
INSERT INTO `NumberOfEmployees` VALUES(7, ’50 to 99’);
INSERT INTO `NumberOfEmployees` VALUES(8, ‘100 to 299’);
INSERT INTO `NumberOfEmployees` VALUES(9, ‘300 to 599’);
INSERT INTO `NumberOfEmployees` VALUES(10, ‘600 to 999’);
INSERT INTO `NumberOfEmployees` VALUES(11, ‘1000 to 1499’);
INSERT INTO `NumberOfEmployees` VALUES(12, ‘1500 to 1999’);
INSERT INTO `NumberOfEmployees` VALUES(13, ‘2000 to 3000’);
INSERT INTO `NumberOfEmployees` VALUES(14, ‘more than 3000’);

Revenue Volume

CREATE TABLE `RevenueVolume` (
`ID` tinyint(4) NOT NULL,
`Volume` varchar(96) NOT NULL,
PRIMARY KEY  (`ID`),
UNIQUE KEY `Volume` (`Volume`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `RevenueVolume` VALUES(0, ‘None’);
INSERT INTO `RevenueVolume` VALUES(1, ‘Less than $100,000’);
INSERT INTO `RevenueVolume` VALUES(2, ‘Less than $200,000’);
INSERT INTO `RevenueVolume` VALUES(3, ‘Less than $300,000’);
INSERT INTO `RevenueVolume` VALUES(4, ‘Less than $400,000’);
INSERT INTO `RevenueVolume` VALUES(5, ‘Less than $500,000’);
INSERT INTO `RevenueVolume` VALUES(6, ‘Less than $750,000’);
INSERT INTO `RevenueVolume` VALUES(7, ‘Less than $1,000,000’);
INSERT INTO `RevenueVolume` VALUES(8, ‘Less than $2,000,000’);
INSERT INTO `RevenueVolume` VALUES(9, ‘Less than $5,000,000’);
INSERT INTO `RevenueVolume` VALUES(10, ‘Less than ten million’);
INSERT INTO `RevenueVolume` VALUES(11, ‘Less than 50 million’);
INSERT INTO `RevenueVolume` VALUES(12, ‘Less than 100 million’);
INSERT INTO `RevenueVolume` VALUES(13, ‘Less than 250 million’);
INSERT INTO `RevenueVolume` VALUES(14, ‘Less than 500 million’);
INSERT INTO `RevenueVolume` VALUES(15, ‘Less than 750 million’);
INSERT INTO `RevenueVolume` VALUES(16, ‘Less than a billion’);
INSERT INTO `RevenueVolume` VALUES(17, ‘More than a billion’);

Time Frame

CREATE TABLE `TimeFrame` (
`ID` tinyint(4) NOT NULL,
`Time Frame` varchar(128) NOT NULL,
`Seconds` bigint(20) NOT NULL,
PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `TimeFrame` VALUES(0, ‘One Day’, 86400);
INSERT INTO `TimeFrame` VALUES(1, ‘Three Days’, 259200);
INSERT INTO `TimeFrame` VALUES(2, ‘Five Days’, 432000);
INSERT INTO `TimeFrame` VALUES(3, ‘One Week’, 604800);
INSERT INTO `TimeFrame` VALUES(4, ‘Ten Days’, 864000);
INSERT INTO `TimeFrame` VALUES(5, ‘Two Weeks’, 1209600);
INSERT INTO `TimeFrame` VALUES(6, ‘Fifteen Days’, 1296000);
INSERT INTO `TimeFrame` VALUES(7, ‘Eighteen Days’, 1555200);
INSERT INTO `TimeFrame` VALUES(8, ‘Three Weeks’, 1814400);
INSERT INTO `TimeFrame` VALUES(9, ‘Twenty Five Days’, 2160000);
INSERT INTO `TimeFrame` VALUES(10, ‘Four Weeks’, 2419200);
INSERT INTO `TimeFrame` VALUES(11, ‘Thirty Days’, 2592000);
INSERT INTO `TimeFrame` VALUES(12, ‘Five Weeks’, 3024000);
INSERT INTO `TimeFrame` VALUES(13, ‘Six Weeks’, 3628800);
INSERT INTO `TimeFrame` VALUES(14, ‘Forty Five Days’, 3888000);
INSERT INTO `TimeFrame` VALUES(15, ‘Seven Weeks’, 4233600);
INSERT INTO `TimeFrame` VALUES(16, ‘Eight Weeks’, 4838400);
INSERT INTO `TimeFrame` VALUES(17, ‘Sixty Days’, 5184000);
INSERT INTO `TimeFrame` VALUES(18, ‘Nine Weeks’, 5443200);
INSERT INTO `TimeFrame` VALUES(19, ‘Ten Weeks’, 6048000);
INSERT INTO `TimeFrame` VALUES(20, ‘Eleven Weeks’, 6652800);
INSERT INTO `TimeFrame` VALUES(21, ‘Twelve Weeks’, 7257600);
INSERT INTO `TimeFrame` VALUES(22, ‘Ninety Days’, 7776000);
INSERT INTO `TimeFrame` VALUES(23, ‘Sixteen Weeks’, 9676800);
INSERT INTO `TimeFrame` VALUES(24, ‘Twenty Weeks’, 12096000);
INSERT INTO `TimeFrame` VALUES(25, ‘Twenty Four Weeks’, 14515200);
INSERT INTO `TimeFrame` VALUES(26, ‘Twenty Eight Weeks’, 16934400);
INSERT INTO `TimeFrame` VALUES(27, ‘Thirty Two Weeks’, 19353600);
INSERT INTO `TimeFrame` VALUES(28, ‘Thirty Six Weeks’, 21772800);
INSERT INTO `TimeFrame` VALUES(29, ‘Forty Weeks’, 24192000);
INSERT INTO `TimeFrame` VALUES(30, ‘Forty Four Weeks’, 26611200);
INSERT INTO `TimeFrame` VALUES(31, ‘Forty Eight Weeks’, 29030400);
INSERT INTO `TimeFrame` VALUES(32, ‘Fifty Two Weeks’, 31449600);
INSERT INTO `TimeFrame` VALUES(33, ‘One Year’, 31536000);
INSERT INTO `TimeFrame` VALUES(34, ‘One Year, Three Months’, 39420000);
INSERT INTO `TimeFrame` VALUES(35, ‘One Year, Six Months’, 47304000);
INSERT INTO `TimeFrame` VALUES(36, ‘One Year, Nine Months’, 55188000);
INSERT INTO `TimeFrame` VALUES(37, ‘Two Years’, 63072000);
INSERT INTO `TimeFrame` VALUES(38, ‘More Than Two Years’, 63072001);
INSERT INTO `TimeFrame` VALUES(39, ‘Unknown’, 0);
INSERT INTO `TimeFrame` VALUES(40, ‘Not time based’, 0);

I hope this saves you time.

– Joel

Web Development: Industry Classification csv file

A web development resource: Industry classifications for menus.

A project I’m working on requires a classification of businesses for a menu and I’ve searched the internet over for a usable resource.  I wanted an Industry classification csv file to load into a database.  I ended my search when I found the North American Industry Classification System (NAICS).  They offer a complete classification scheme used by the Federal Government with explanations and downloadable files.  I’ve modified some of the files that they offer for easy loading into a database table. You can download them here:

If you know of a better classification system or a way to improve on this one let me know.  I’ll be using this on the card project if you’d like to preview it here http://catontech.com/card

– Joel

Introduction of Sales Process Funnel Chart

The Sales Process Funnel
Sales Process Funnel

Here’s the first revision of the sales process funnel chart.  I’ll be publishing a series on each stage and element of the process going forward.

Any thoughts?

– Joel