2013 !!  Phew !!  What a year !!

This blog "Tony's Other Blog" started as my tech blog, but then I moved that to a specific tech blog, and I always intended to diarise the things that interest me but do not fit into my normal daily routine.

So as I sit and reflect on the past year I realise that yet again I failed to be consistent in spending a reasonable amount of time on things other than work.

What did happen this year ?  I changed from full time contracting with UniPhi to being a reseller of UniPhi under a new business called Managing by Project. Somewhere in amongst that I gained a qualification with the AIPM and I'll need to update Linked-In with that info in the new year.

I spent way too much time working in my business interests and failed to work on them. A mantra that I have held out to anyone who would listen for the last 20 or more years, and here I am not listening to those words of wisdom. Of course, I do not pretend that I said them first, but I do think it is a must-do.

What will 2014 hold ?  Who knows, but I am going to really make an effort to prioritise better, document everything instead of trying to keep it all in my head, and to spend a better percentage of my awake time outside of my business interests.

Does this really interest anyone else ?  Probably not, but I have always thought that writing something down makes it more possible.

So my thought for 2014 is "Make it Happen" and my reflection on 2013 is that it was a good grounding for next year.

Seasons greetings to all that adhere to that philosophy and Happy New Year to those that follow the same calendar as me.

Urbanspoon Spoonerisms

So I continued with Urbanspoon, just trying to get my 'home' town changed from Hobart to Melbourne. It appears that if you review a restaurant while on a trip, then the restaurant 'home' is considered to be the reviewers 'home' until such time as the reviewer, a) reviews a restaurant in their actual home location, and b) changes their profile to their correct location.

So while I am doing so, I went to check my profile and, yep, the urbanspoon system now thinks I am Michael.

Grrr... I hate badly written software.

Urbanspoon fails basic review

So you go to urbanspoon as a recognised site for peer reviews of restaurants and you would expect to be able to rely on their system.

My first experience is that their backend platform is so unreliable that my newly registered account is cross-linked to another users profile.  Does this mean I can change things as them? Hopefully not, but really raises questions about the reliability of the system.

...and for 'saltyg' if you are looking at this, Hi!

SQL service account

The service account for SQL is generally domain\sqlservice or similar. You should not be using domain\Administrator as that is bad practice.

In order for this, (notionally) non-standard admin account to function, it should be also made a member of the local computer Administrators Group.

Event Log Errors in pairs like:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: ip address local host]

with

SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: ip address local host]

indicate that the service account is not authorised to access its own SQL server. Adding the domain\sqlservice account to the local administrators group should fix this error.

Sugar CRM and Merge Records

SugarCRM provides an ideal platform for centralising name and address details for full CRM processes but also to remove duplication by presenting details to other applications. I work on a project that supports 3 applications that use name/address details and linking them all works really well.

An issue was that when merging records like Accounts in Sugar that there was no complete record kept of what accounts were merged only what account was saved.

Sugar CE (Community Edition aka Open Source) includes a Trackers module that does track some transactions within Sugar for users / sessions but not all.

One of the main shortcomings is that it does not track deletions of accounts. Noting that Sugar does not actually delete but disables via a deleted flag.

So the first task that I addressed was the tracking of what accounts were deleted.

The changes for this are all in the file /data/sugarbean.php starting at line 4264 or there-abouts:

4264 $query = "UPDATE $this->table_name set deleted=1 , date_modified = '$date_modified' where id='$id'";
4265 $this->db->query($query, true,"Error marking record deleted: ");
4266 $this->mark_relationships_deleted($id);
4267
4268 // Take the item off the recently viewed lists

Changing the code to include both a log entry for deletions and the tracker entry.

4264 $query = "UPDATE $this->table_name set deleted=1 , date_modified = '$date_modified' where id='$id'";
4265 # thowden 20090518: Added for logging of deletions
4266 $GLOBALS['log']->info('Deletion ' . $id . ' query : ' . $query);
4267 # end thowden
4268 $this->db->query($query, true,"Error marking record deleted: ");
4269 $this->mark_relationships_deleted($id);
4270 # thowden 20090518 added to tracker for deletions
4271 $this->track_view($current_user->id, $this->module_dir, 'deleted');
4272 # end thowden
4273 // Take the item off the recently viewed lists

The log entry could be changed to only debug, error, or warning level, but I figure that 'delete' will be one of those things we will constantly be chasing for our beloved end-users.

Hand-in-hand with deletion is the concept of restoration and for good measure this can also be tracked.

Further down the file: noting that line numbers shown here vary as they are already changed compared to the original file due to the inclusion of the code above.

4289 $query = "UPDATE $this->table_name set deleted=0 , date_modified = '$date_modified' where id='$id'";
4290 $this->db->query($query, true,"Error marking record undeleted: ");
4291
4292 // call the custom business logic

Changes to:

4295 # thowden 20090518: Added for logging of restorations
4296 $GLOBALS['log']->info('Restoring ' . $name . ' ' . $value . ' query : ' . $query);
4297 # end thowden
4298 $this->db->query($query, true,"Error marking record undeleted: ");
4299 # thowden 20090518 added to tracker for restores
4300 $this->track_view($current_user->id, $this->module_dir, 'restored');
4301 # end thowden

Now you can review in both the sugarcrm.log or whatever you call your log file and in the Tracker table in your SugarCRM database.

For the merge process details to be really useful we wanted to add a trigger in the database to run some scripts. In order for the trigger to function it needs to have a unique record that it 'knows' means that the merge transactions have completed.

So I added a new entry to the Tracker database. File to modify is
modules\MergeRecords\SaveMerge.php
Find line 118 in the file:

118 $GLOBALS['log']->debug("Merged record with id of ".$return_id);

... and add the following

118 $GLOBALS['log']->debug("Merged record with id of ".$return_id);
119 # thowden 20090518 added to tracker for mergecomplete
120 $mergesource->track_view($current_user->id, 'MergeRecords', 'mergecomplete');
121 # end thowden

This completes a merged accounts transaction set for any number of merged accounts with a mergecomplete transaction. The database trigger is linked to that entry and recognises all the transactions by the monitor_id column value which is unique for a transaction set.

To query the Tracker table try using something like the following and modify the date value to something more appropriate.

select * from tracker where convert(char(10),date_modified,112)>='20090520'

This supports merging of Accounts and Contacts in SugarCRM CE 5.2.0d and possibly other 5.x.x variants.