JavaScript document map

JavaScript code to generate a document map/table of contents from the document structure.

NB – Sorry I need to update this to get the examples working in WordPress

This page generates the following document map:

[jscript]

You’ll want a target div for the table of contents somewhere on your page:

Then call the function at the end of a page or stick a window.onload function in:

Parameters

The parameters are pretty straight forward:

use_body
1 will take everything in the body of the document
0 if you are going to pass HTML in
html_text
The HTML you want it to use if you aren’t using the whole document. If you aren’t passing any in, just leave it blank – “”. You can just pass in the html of a particular div like this: document.getElementById(“a_div”).innerHTML. I should probably change it so you can just pass a div ID in..
do_bookmarks
1 if you want the document map hyper-linked to the headings. (This will add anchors at the heading elements).
0 will just give a plain text listing.
heading_level
The first item in the listing will be a heading, followed by nested lists. This sets the heading level you want it to be. (most likely 2 or 3). The default is 3.
show_level
If you set this to 1 it will add the heading level to the end of the link (like in the example on this page.
0 will leave it off

It would probably be better to replace use_body with an element ID and do it that way, but I haven’t got round to doing that yet..

Example..

Some headings just to show how the toc works.

A heading

Blah blah blah

A sub heading

Blah blah blah

Another sub heading

Another sub heading
Another sub heading

Another heading

Another sub heading

A bad heading structure

Angular ngAnimate table row detail slide down (similar to jQuery.slideDown)

Let’s start with an example of what we’re trying to achieve – click the rows to show and hide a detail row:

e
IndexIdCol 1Col 2
{{$index + 1}} {{row.id}} {{row.column1}} {{row.column2}}

{{rowDetail}}

I figured this would be pretty straight forward with lots of examples out there in the wild but unless google is broke I can’t find a good example of what I’m after! I don’t want a tonne of hidden table rows, I want to leverage Angulars repeater and ng-if so we don’t clutter up the DOM. Initially I tried to do it using pure CSS but I hit some rendering issues on the table row – the browser would only fully update the table on completion so weird things would happen during the animation. I’ll include that solution at the end, maybe somebody knows how to fix it.

Anyway, so here’s what I did to get it to work using some javascript and ngAnimate.

First, the html:

Main things to note here are that I’m using ng-repeat-start/end to render the data row, then the detail row. ng-if in the detail row will get angular to add/remove the row to the DOM as required. Also within the td on the row (where I’ve set the colspan to cover the all columns) I’ve had to add two divs. The first I’ve given the class ‘slide-outer’, which is the element we’ll be resizing in script, and the second div is our actual content. Doing it this way means we can grab the height of the inner divs content, so we know what size we need to use on the outer one.

OK so next, the CSS. This is simply:

Next is the script. I’m just going to dump the whole lot in, and then describe what’s going on:

So – first, I’m just generating some data that all goes into the table variable, and creating some random detail for each row. Then I’m setting up my angular app with the “slider” controller. This just has two functions, one that sets the selectedRow when a row is clicked, and one that is used to evaluate which row is selected. Pretty standard stuff so far.

Next is where the magic happens, in the app.animation section. When a row is clicked, the enter and leave functions are called by ngAnimate. These functions find my slider div then call initShrinky, telling it which direction to animate, so up for leave and down for enter. The doneFn is an internal ngAnimate function that we pass over and call when we’re finished so it knows we’re done.

initShrinky stuffs the row into the shrinkyItems array, which gives us an id that we’ll use to work out what we’re animating. We then call the shrinky function in a timeout, which simply looks at the direction we’re animating, applies the new height, then if we’ve not reached our target calls itself again. Once we hit the target height, we call the doneFn function and clear out the array position. (I think this final bit in particular could be improved!)

Then Boom! That’s all there is to it!

Adding SQL Code Highlighter to Reporting Services using VB

Having created a rudimentary audit of DDL changes to SQL Server, I wanted to be able to quickly look at the changes through SSMS. To do this, I added a custom report on the object explorer right click menu that just queryed the audit data against the particular function/procedure etc. This just makes it very quick and easy to see what has changed and when (particularly if you don’t have source control on your SQL Server Database!)

That was all easy enough, but then to make those long procedures a bit easier to read I thought it would be good to get some code highlighting involved. I can’t install a custom assembly on the server, so I had to find another way. Fortunately, this is relatively simple(ish!) using a placeholder, inserting a text object, and creating a code element in the report to pass the TSQL to for formatting.

I’m sure there is room for improvement (I particularly don’t like removing then re-adding the code comments, there must be nice regex way to do this!), but it seems to do the job OK so it’s a good start!

DDlChangesReport

RS Page breaks by row number and group: Using DENSE_RANK and ROW_NUMBER to create group ids

[highlighter]

I’m a bit green when it comes to the rank functions in T-SQL so this was a nice exercise.

We needed a report to display totals per page in grouped data so this was a bit of a head scratcher.. Reporting Services doesn’t make it easy to do that! I tried a number of methods then came up with this solution. Basically, due to the type of data we’re displaying

we knew we only ever need to show 6 rows on one page unless we hit one of the other things we’re grouping on where we reset the group.. I want to split the result set into a series of named groups

which will allow me to do my group by totals in the report.

Make sense?

No?

Then read on..

Which gives me:

groupid rnum group1reference group2value sort_date
1 1 2015 Friday 7th December 2012 07/12/2012 09:00:00
1 2 2015 Friday 7th December 2012 07/12/2012 11:00:00
1 3 2015 Friday 7th December 2012 07/12/2012 13:00:00
1 4 2015 Friday 7th December 2012 07/12/2012 15:00:00
1 5 2015 Friday 7th December 2012 07/12/2012 17:00:00
1 6 2015 Friday 7th December 2012 07/12/2012 19:00:00
2 7 2015 Friday 7th December 2012 07/12/2012 21:00:00
3 1 2015 Saturday 8th December 2012 08/12/2012 09:00:00
3 2 2015 Saturday 8th December 2012 08/12/2012 11:00:00
3 3 2015 Saturday 8th December 2012 08/12/2012 13:00:00
4 1 2016 Saturday 8th December 2012 08/12/2012 15:00:00
4 2 2016 Saturday 8th December 2012 08/12/2012 17:00:00
4 3 2016 Saturday 8th December 2012 08/12/2012 19:00:00
4 4 2016 Saturday 8th December 2012 08/12/2012 21:00:00
5 1 2017 Monday 6th May 2013 06/05/2013 09:00:00
5 2 2017 Monday 6th May 2013 06/05/2013 11:00:00
5 3 2017 Monday 6th May 2013 06/05/2013 13:00:00
6 1 2017 Tuesday 7th May 2013 07/05/2013 08:00:00
6 2 2017 Tuesday 7th May 2013 07/05/2013 08:00:00
6 3 2017 Tuesday 7th May 2013 07/05/2013 10:00:00
6 4 2017 Tuesday 7th May 2013 07/05/2013 10:00:00
6 5 2017 Tuesday 7th May 2013 07/05/2013 12:00:00
6 6 2017 Tuesday 7th May 2013 07/05/2013 12:00:00
7 7 2017 Tuesday 7th May 2013 07/05/2013 14:00:00
7 8 2017 Tuesday 7th May 2013 07/05/2013 16:00:00
7 9 2017 Tuesday 7th May 2013 07/05/2013 16:00:00
7 10 2017 Tuesday 7th May 2013 07/05/2013 18:00:00
8 1 2018 Tuesday 7th May 2013 07/05/2013 18:00:00
8 2 2018 Tuesday 7th May 2013 07/05/2013 20:00:00
8 3 2018 Tuesday 7th May 2013 07/05/2013 20:00:00

Thanks to ChrisM at SQLServerCentral for help with the dense_rank!

BST – Converting UTC time to local time

This is my take at sorting out the issue of storing UTC dates but needing to display the time during british summer time correctly. I found a few solutions with crazy lookup tables so this bit of SQL should be a lot quicker! I just need to return the time as a string but obviously you can change that to whatever suits your needs.

Date only datetime

Sometimes you just want a date without the time – maybe a getdate() or a datetime stored in your database. There are a couple of ways of clearing out the time, but here’s a nice conversion method I came across:

[highlighter]

So in this case getdate() would return 2010-12-31 08:29:04.973 but with the conversion to a float and back you get 2010-12-31 00:00:00.000

If you are interested to know what’s happening, basically converting the datetime to a float gave 40541.3535297839, then floor rounded it down to 40541. We then converted that back to a datetime, which gives us just the date. Yippee!

Incidentally, if you want tomorrow’s date you can use ceiling instead of floor

SQL Server date function with st, th, nd and rd

SQL Server (and Reporting Services) give you a number of ways to format dates – bar the ones you actually want.. :p

There isn’t, for example, a way to pull out a standard date format that you would use in a letter – i.e. 29th November 2010. Instead you’d have to make to with 29 November 2010.

SO! Here is a magical function that will cure this horrendous lack of ordinal suffixes and return a tidy formatted date that we are not ashamed of putting on our reports.

[highlighter]

Hurrah!

Example:

SQL Server date function with st, th, nd and rd

SQL Server (and Reporting Services) give you a number of ways to format dates – bar the ones you actually want.. :p

There isn’t, for example, a way to pull out a standard date format that you would use in a letter – i.e. 29th November 2010. Instead you’d have to make to with 29 November 2010.

SO! Here is a magical function that will cure this horrendous lack of ordinal suffixes and return a tidy formatted date that we are not ashamed of putting on our reports.

Hurrah!

Example:

Java infinite star field

So I’ve been faffing about with Java lately (well, I’m waiting for a new job, I got no cash, so I gotta do something!!) Anyway I’m working on a simply amazing space battle game and I wanted to create an infinite universe.. so here is version 1! It’s a pretty basic starfield with a fake feeling of depth to it.

Star field

Just use arrow keys to change directions. (You may need to click on the applet first if nothing is happening)

Creating the starfield is pretty straight forward, and sits in the StarField class:

[highlighter]

So that will build a starfield for us. Now for a simple test application:

And that’s it! When using the constructor – StarField(no_stars, getWidth(), getHeight(), depth) I’m using the applet height and width

as the starfield size. Obviously you can make that much bigger so that the stars don’t loop around so quickly.

To give the impression of depth each star is given a z value. Whenever the screen is moved, all you have to do is if divide the

depth by the screen offset (the width and height divided by 2 so we are centered) and that will alter the speed at which the star moves. Easy eh!

I might get round to a version 2 with images for stars and nebula and so on but I’ll see how it goes.. Stay tuned!

And finally.. I’m a bit new at this Java lark though so if anyone can point out ways to improve the above let me know!

CKEditor word count

There are a couple of ways you may want to do this – I’ve described two below. The first is a word count that runs itself and updates a value somewhere on the page, the second a word count initiated by the user.

Method 1: Using a timed function

My solution for the first method is simply a function that runs at a regular interval. It grabs the text from the specific CKEditor instance, performs a word count and updates a div on the page

Stick this function in the head of your document:
[highlighter]

Put an element on the page and give it the id ‘wordcount’. Then after your CKEditor element is on the page, put another script tag in and run this:

That should do the job! It will update the word count every 5 seconds

Method 2: Using a CKEditor plugin

To do it this way we’ll need to create a folder in the CKEditor plugins directory. Name it wordcount and create a blank plugin.js file in there. Copy the code below in:

You might want to put a nice word count image icon in (icon.png). Here is a lame one I did – just save it into your word count plugin directory: word count icon

When you are initiating your CKEditor you’ll need to add the plugin in, something like this:

You should now have a button in your editor that will give you your word count. Hooray!

windowssystem32configsystem missing or corrupt

Well, this was a total pain in the arse!! So my netbook suddenly died, I’ve just arrived in a foreign country and no mr microsoft, I neither have a cd drive (nor my windows cd) and I don’t trust giving my netbook into to some random pc repair place.

This took a bit of searching around but here is a way to fix this if you don’t have cds/cd drives etc.

Basically, you should be able take a copy of the file from a previous system restore point and just copy it across. Simple eh? Only first you have to be able to access the file system on your hard disc.. Luckily for me I was able to borrow someones pc and internet connection. If you don’t have either of these things, err.. find them somehow!

Once you have a usb stick and someone willing to let you use their internet connection, do this:

  1. Create a bootable usb stick with linux on it. (simple eh…) I used a version of linux mint-just follow the instructions on this page: http://www.pendrivelinux.com/install-linux-mint-8-to-a-flash-drive-in-windows/
    (that’s what I used originally, though you might prefer to follow the instructions here: http://www.pendrivelinux.com/universal-usb-installer-easy-as-1-2-3/ for an up to date version of mint/whatever)
  2. Boot your pc with the usb drive in
  3. Once you are in linux, open whatever file browser it has (in linux mint, it’s simply an icon on the desktop called ‘computer’)
  4. navigate to the hard disc with the dead copy of XP on it – in most cases I expect it would be the only hard disc listed
  5. Find the folder System Volume Information – this is where XP keeps it’s restore data (interestingly you can’t get into this folder through windows)
  6. In this folder there will be something that looks like _restore{xxxx} – open this folder and sort by date last modified – you want to recover the most recent good file you can of course. In my case this was RP131

Within this folder open the folders snapshotrepository and find the file _REGISTRY_MACHINE_SYSTEM

  • copy this file to windowssystem32config (i.e. what would be the c:windowssystem32config folder in windows
  • rename the current system file to system.bak
  • rename the file you copied over to system
  • Hopefully – that should do it! reboot and pray for the windows logo to show up! I guess if that doesn’t work, try the next most recent _restore folder and hope that does the job!

 

Hope that helped someone!!

Multiple insert trigger

If you are inserting/updating multiple rows in a database and you need to do some processing on the inserted data you may need to add a bit of recursion to your trigger code to handle each record corretly.

The example below uses a while loop to run through each inserted row and updates some relational data in the updated table.

As an addendum… In general, avoid triggers unless you absolutely have to use them

[highlighter]

Reporting Services – groups alternating colour

So to create alternate coloured rows in SSRS we create an expression on the background property of the row data:

I found myself wanting to apply the alternating colour not to each row, but to each group to make it clear when one begins and another ends in the view. To do this you can do something like this:

Where grouped_field is the field you are grouping on.

You may need to put a group into the scope:

CKEditor insert html plugin

I wanted to be able to insert tokens using a drop down in CKEditor. I wanted tokens in brackets that would be replaced later by an application with something meaningful – so [name] for example would be replaced with a persons name.

Working this out meant trawling through the existing plugins and seeing how they were put together. Here is how I did it:

Step 1

Create a tokens folder in plugins, then save this in plugin.js.

Plugin.js:

[highlighter]

Step 2

Then to make the drop down appear you need to use the extraPugins option:

At the moment this code will work but may have problems if you try to put HTML into the value fields – it has a problem with ‘ and “. I haven’t looked into sorting it out yet, though some clever escaping may sort it out..

SQL mail problems

Well, there are all sorts of problems you can face with your sql mail. Typically you'll test it out, it will work fine, you'll put it in an SP and it will fail on you. Here are some things to look at.

msdb.dbo.sp_send_dbmail

Make sure user is in correct group on msdb database (DatabaseMailUserRole)

If you get a mysterious error like this:

Set the mail account profile to public:

  • management\database mail

  • configure database mail

  • manage profile security

SQL Server split function

You’ll find a few functions on the internet for splitting delimited data. Here is one I found and changed slightly to do the job. Don’t remember where the original came from , so if it’s yours – thanks!

This function allows you to pass in a list and a delimiter (defaulting to a ','). It will return a table of the values with an ID for each row. You don't neccessarily need the ID, but I’ve found this useful in quickly grabbing data from a block of text like an address for example. Pass in a line break as the delimeter, then for address line 1 you can grab the row with ID of 1.

Replication attachment size

[highlighter]

If you are storing any large text/image data in sql server and are trying to replicate it across to another server you may find that you can't. If you get an error looking something like this:

You can change the size limit of images/long text fields in replication

How to search for specific text in the whole DB

[highlighter]

I found this bit of code somewhere on the internet, I think I used it to find all stored procedures with the database name hardcoded into the SP.

Alternative to cursor

[highlighter]

Cursors are slow and can cause table locks when running directly against the source table. You can instead store a list of IDs or records in a temporary table and use a WHILE loop. Using this technique, you can then query the source data as you need it, or keep that data you want to work with in your temporary table. Obviously this means there’s a chance it will be updated by some other process whilst it’s in your temporary table, so something to be aware of.

Actually, whilst we’re on the subject, you can use a cursor on a temporary table too, really, but some people just don’t like cursors!

The real question to ask, is why are you looping through set data? Sometimes you need to, but I’ve seen plenty of occasions where row by row manipulation is happening where it really isn’t needed. But I digress! On to the example..

I think this bit of code comes from here originally: www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx

Building an HTML table of contents in SQL Server

(NB. When was this ever a good idea???)

This SQL table function takes a block of html as input and returns a table containing a table of contents both linked and unlinked, and also the html with added anchors within the page.

SQL probably isn’t the best place to do this, and this might be a good thing to use CLR for but here it is in case you ever need it!