Wednesday
Mar252015

Creating a Dynamic Table of Contents for PHPKB

We use phpkb at work for an internal knowledge base and overall I'm pretty happy with it, it does pretty much everything that I wanted in a KB except for one or two things. One of those was to allow for a dynamic table of contents based on use of heading tags in your article, something that most wiki software does out of the box, as does MS Word, which is where much of the content we are migrating into phpkb lived before.

So I wrote a little bit of JavaScript to enable a dymanic TOC myself. It is easy to implement if you are using phpkb, here is how it looks and how to turn this on for article pages.

The TOC is inserted at the top of your article and indented based on heading use

You can cheat and just get the jscript file and my modded article.php in this zipped copy.

Step 1 - Modify article.php (back it up first..)
Paste these lines in that page right before the '$include_files' insertion.

<script type=\"text/javascript\" src=\"$path_kb/thesaurus/js/jquery1-7-1.js\"></script>
<script type=\"text/javascript\" src=\"$path_kb/include/toc.js\"></script>


Step 2 - Create a custom field called 'Show TOC' and make it a non-required checkbox with Yes,No options.


Step 3 - Drop the toc.js file into the include folder for your installation. Here is the source for that file so you can see what is going on.

$(document).ready(function(){
	$('.customfields').find('li').each(function(i) {  // Looks at custom fields on page to see if enabled
		var current = $(this);
		if(current.text() == 'Show TOC: Yes') {  // Enabled, show table of contents		
			$("#ARTICLECONTENT").prepend('<div id="toc"><b>Table of Contents</b></div>');
			$("#toc").css('border','1px solid #eccf59');
			$("#toc").css('background','#f1eddf');
			$("#toc").css('margin','5px 0 0 0');
			$("#toc").css('padding','5px 5px 15px 5px');
			$("#toc").append("<ul>");
			var iToc = 0; // to track if we find any headings
			$('article').children("h1, h2, h3, h4, h5").each(function(i) {
				iToc ++;
				var current = $(this);
				current.attr("id", "title" + i);
				var sp = '';
				switch(current.prop("tagName")){
					case 'H1': sp = '10px'; break;
					case 'H2': sp = '20px'; break;
					case 'H3': sp = '30px'; break;
					case 'H4': sp = '40px'; break;
					case 'H5': sp = '50px'; break;				
				}
				if(current.html().trim()){
					$("#toc").append("<li style='padding-left:" + sp + ";'><a id='link" + i + 
						"' href='#title" + i + "' title='" + current.attr("tagName") + 
						"'>" + current.html() + "</a></li>");
				}
			});	
			$("#toc").append("</ul>");
			if(iToc == 0) { $("#toc").hide(); } // if no content for TOC we just hide it			
		}
	});
});

As you can see, with some very little changes you can repurpose this for other applications that use the same 'H tag' heading hierarchy that you commonly run into.

Tuesday
Apr012014

The Gentleman's Richard - 'The Sophisticated Workplace Dickie'

I'm really excited to finally be able to talk about my first patent and the new business I'm launching today.  I've been working in silence for months building a business plan, landing angel investors and cutting up thousands of shirts until I got it right.  As of today I quit my job, threw another mortgage on the pile and am pursuing this dream full time.  Wish me luck friends!

For years now I've talked about a dickie for the workplace and have finally made my dream a reality.  Gone are the days of a binding oxford-style shirt underneath my sweater, the Gentleman's RichardTM aka 'The Sophisticated Workplace Dickie' is here to save the day and set us free.  Being in the office now feels like I'm running through the Austrian hills, the world my oyster.  Pre-sales to finer men's shops are lining up now, so I have a lot of shirts to butcher.

 

Here I am showing my Gentleman's Richard both 'raw' (t-shirt optional if you want to go to work in 'wildling' mode) and with the sweater covering.  This setup is accessorized with the 'Faux Cuffs', and stay tuned for the launch of the 'HalfTie - the necktie that you won't have to.'

Business on the outside, party on the inside.

Look like a professional but be ready for fisticuffs. - The Gentleman's Richard

Are you wearing a Gentleman's Richard? - A gentleman never tells...

Monday
Jan272014

Super-fast Excel to SQL Insert Statement Converter Workbook

Most data comes from our business folks in Excel and needs to be in SQL for us to provide the analysis they are typically after.  SQL has some tools to ingest data from Excel into SQL but most of them have proven to be a pain in the butt (data conversions to get unicode and non-unicode columns is at the front of that list).  So a few lines of Excel macro and a few sheets to go with those and I have a simple workbook that generates insert statements for you.  This has, since I threw it together, saved me a pile of time fiddling with getting data where I need it.  It supports options of adding a carriage return between inserts and overriding the default setting to escape quotes if you want.  It sends the resulting SQL statements straight to your clipboard.

You can specify how many columns to pick up, starting at the far left, and how many rows to pick up as well.  I could have made this sense last used on both axes but, well, didn't.  yet.

The file is available for download and is a macro-enabled Excel workbool (.xlsm) with the following hashes:

MD5 : f94307aeb7140a1f0fbe57d5812859de
SHA-1: 49b02668774d006b152621895f5cb1ed7aa7bf65

If that still makes you uncomfortable, below is the code and general layout so you can homebrew your own.

Control tab - Where you set your options.  If you call these tabs something else just change the Const values in the script.

 

Data tab - Not much to it, it will take the first row values and interpret them as the column names on the insert.


Running the sheet against the data in the image just puts the following in your clipboard:

INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Grohl','Dave','Foo Fighters')
INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Walker','Butch','Butch Walker')
INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Berg','Moe','TPOH')
INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Ellison','Jim','Material Issue')

Sub GenerateSql()
Const DataTab = "Data"
Const ControlTab = "Control"

Dim szDbName As String, szTableName As String, szLastCol As String, szLastColumn As String, iLastRow As Integer
Dim iColumnCount As Integer, iRowCount As Integer
Dim rCols As Range, rData As Range, szCols As String, szData As String
Dim szSql As String, szSqlRowPreface As String, szSqlRow As String, szSqlCols As String, szSqlDataRow As String
Dim bSettings_ExtraLines As Boolean, bSettings_NoEscape As Boolean

szDbName = Trim(Sheets(ControlTab).Range("C3").Value)
szTableName = Trim(Sheets(ControlTab).Range("C4").Value)
szLastColumn = Trim(Sheets(ControlTab).Range("C6").Value)
iLastRow = Trim(Sheets(ControlTab).Range("C7").Value)
iColumnCount = 0
iRowCount = 0

' Handle settings
    If Sheets(ControlTab).Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then
        bSettings_ExtraLines = True
    Else
        bSettings_ExtraLines = False
    End If
    If Sheets(ControlTab).Shapes("Check Box 3").OLEFormat.Object.Value = 1 Then
        bSettings_NoEscape = True
    Else
        bSettings_NoEscape = False
    End If

' Build ranges
    szCols = "A1:" & szLastColumn & "1"
    szData = "A2:" & szLastColumn & iLastRow
    Set rCols = Sheets(DataTab).Range(szCols)
    Set rData = Sheets(DataTab).Range(szData)

CopyTextToClipboard ("Error, import failed") ' In case we encounter problems

' Build insert, destination and columns:
    For Each col In rCols.Columns
        szSqlCols = szSqlCols + ("[" & col.Value & "],")
        iColumnCount = iColumnCount + 1
    Next col
    szSqlCols = Left(szSqlCols, Len(szSqlCols) - 1) ' remove trailing comma
    szSqlRowPreface = "INSERT INTO " & szDbName & ".dbo." & szTableName & " (" & szSqlCols & ") VALUES ("

' Build values
    For Each rw In rData.Rows
        iRowCount = iRowCount + 1
        szSqlDataRow = "" ' Reset this for each row
        For Each col In rw.Columns
            szSqlDataRow = szSqlDataRow + ("'" & FormatSql(col.Value, bSettings_NoEscape) & "',")
        Next col
        szSqlDataRow = Left(szSqlDataRow, Len(szSqlDataRow) - 1) ' remove trailing comma
        szSql = szSql + szSqlRowPreface & szSqlDataRow & ")" & vbNewLine
        
        If bSettings_ExtraLines = True Then
            szSql = szSql & vbNewLine
        End If
    Next rw

' Add to clipboard and complete:
CopyTextToClipboard (szSql)
MsgBox ("Completed with " & iColumnCount & " data columns and " & iRowCount & " rows." & vbNewLine & vbNewLine & _
    "Results are in the clipboard.")

End Sub

Function CopyTextToClipboard(ByVal inText As String)
  Dim objClipboard As Object
  Set objClipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  objClipboard.SetText inText
  objClipboard.PutInClipboard
  Set objClipboard = Nothing
End Function

Function FormatSql(ByVal inText As String, ByVal skip As Boolean)
    If skip = True Then
        FormatSql = Trim(inText)
    Else
        FormatSql = Trim(Replace(inText, "'", "''"))
    End If
End Function
Thursday
Jan232014

HabitRPG - The Gamefication of My Todo List

I have a rotten memory and a job that often has me managing far too many tasks every day than I can keep straight in my head.  Over the years I've developed habits to help mitigate this, I methodically store detailed notes in my wiki and use task trackers.  I've gone through more task trackers over the years than I can really remember, some good, some not good, but the one that I've been using for the last year is my favorite to date.  The fact that I've been using it for a year says a lot about this tool.

It's called HabitRPG and they describe the (free) site/service thusly: 'HabitRPG is an open source habit building program which treats your life like a Role Playing Game. Level up as you succeed, lose HP as you fail, earn money to buy weapons and armor'.

Gear, mounts, pets, classes, spells, potions, parties, guilds, quests, boss fights.  I.  Freaking.  Love.  It. 

I have habits that I track, good and bad, things I should do daily and my running todo list.  When I do things I should, I gain gold and experience which lead to better gear, helpful when I don't do what I should.  Missing items or engaging in behavior I am trying to curb (such as beer on a weekday) makes me take damage.  That happens enough and I die, losing gold and precious gear.  I know, it all sounds ridiculous, but it works, I've stayed engaged with this todo list far more than any before it and have a free, yet real additional incentive to get things done.

I've recently joined a party, where we take on 'boss monsters' as a group.  What this really means is that my achieving my daily goals (or not) helps or hurts the group.  That adds support among this group of (previously) strangers and additional incentive.  You can aslo accept 'quests' created by the community, accepting the challenge to adopt a good habit for a period, and gaining loot if successful. All of these boosts to getting things done are tiny, but are boosts.

HabitRPG was a brilliant idea by Tyler Renelle that I funded on Kickstarter in its infancy and has evolved into a awesome, fun and effective tool for me to stay on target.  Give it a spin and drop me a note if you want to join the party, we could use a mage that does their work on time...

My dashboard and party

Monday
Dec162013

Resource for SharePoint farm upgrade with server name change

This was a question I had someone ask last week regarding how you might handle the situation where you want to change the host name of your SharePoint farm in an upgrade (or just the host name for any web app really) knowing that all embedded links will be broken.

 

The low hanging fruit is to set a DNS record so that the old and new point to your new server address, but if you want to alter some site names and/or alert users that they have an old, deprecated url instead of just forwarding them on, the solution below might work for you.

 

The following script is for the node.js platform and, if run on a server with your old host name's resolution, will intercept incoming http requests, map it to your new server, allowing for site renaming and provide the user with a message as well as the new 'best guess' url to what they were trying to reach.  It is pretty straightforward and should be relatively easy to modify as needed.

 

HTTPS (443): This is geared toward standard HTTP traffic, if you need a version to work with HTTPS on port 443, you can find some subtle changes that will be needed in the node.js information here http://nodejs.org/api/https.html.

 

// Kevin Guyer - 2013
// Node.js script to handle server (SharePoint) name changes
//  This is designed to catch the use of old urls and present an infomational page with the new url to the resource
//  Test with this to see URL altering:  http://localhost:8080/hr/foosite/barsite/somelibrary/somedoc.pdf

// Intercept incoming port 80 or 443 (needs to operate on both) - set the port below for flavor you are running
var http = require('http');
var url = require("url");
var server = http.createServer();

server.on('request', function(request, response) {
	var hostname = request.headers.host;
    var newHostname = 'https://yournewfarmpath';  // no trailing slash, set to your new farm/server name and path
    var pathname = url.parse(request.url).pathname;
    var newPathname = pathname;
    var query = url.parse(request.url).query;  // optional, not used yet
	
    // Run match logic on array elements to determine what needs to be displayed
    var aResPath = pathname.split('/');
    
    // Modify path elements as needed with element matching:
    var wasAltered = false;
	if(aResPath.length >=1){
		try{
			if(aResPath[1].toLowerCase() == 'hr'){ aResPath[1] = 'HumanResources'; wasAltered = true; }
			if(aResPath[3].toLowerCase() == 'barsite'){ aResPath[3] = 'NewBarSite'; wasAltered = true; }
			// add others as needed, sites on root starting at index [1]...
		} 
		catch(err) {
			// handle as desired...
		}
		
		if(wasAltered == true){
			console.log("Detected swap string in path, altering output.");
			newPathname = '';
			for(i = 1; i < aResPath.length; i++){
				newPathname = newPathname + '/' + aResPath[i];
			}
		}
	}
    // Build response message
    var payload = '<h2>Oops.</h2><p>The url you attempted to reach (<em>' + hostname +  pathname + '</em>) is no longer valid.</p>';
    // Optional: Build and supply a best bet url    
    payload += '<p>Our best guess for the new url for this resource is <a href="'+ newHostname + newPathname+'">'+ newHostname + newPathname+'</a>.</p>';
    payload += '<p>Please update your favorite or link source to reflect this new address.</p>';

    // Optional step to log the old url hit via a web API (REST) - todo...

    // Build and deliver response    
    response.writeHead(200, {"Content-Type": "text/html"});
    response.write(payload);
    response.end();
});

var port = 8080;  // change this to 80 or 443 as needed
server.listen(port);
server.once('listening', function() {
    console.log('Redirect server is now listening on port %d', port);
});