iTop running on Windows portable PHP/MySQL MoWeS

Based on my previous topic informing about the nice functionalities of MoWeS to get easily and quickly a PHP/MySQL environment, I did set up an iTop environment in order to discover and play with the CMDB/ITIL ticketing service desk tool.

While doing so, I also discovered the MoWeS Mixer is not available any longer. Apparently the company Chsoftware behind this tools has stopped in 2012. But the MoWeS tool still works very well on Windows 7 x64 and new packages can easily be added by updating the mowes.ini and packages.ini files manually.

In order to get iTop running, some other parameters had to be updated as well: extra memory allocation is to be allowed in php.ini by changing the memory_limit from 32M to 64M. The php_soap.dll extension needs to be loaded as well by uncommenting it in the php.ini file. For mysql, the allowed packets needed to be extended by adding a parameter max_allowed_packet=500M within the [mysqld] section of the my.ini file.

I’ve created an archive containing a clean MoWeS environment with clean iTop. The iTop still needs to be configured using the web wizard upon first connection to the iTop site (localhost/itop) (mysql username: root, password: <empty>. I’ve also a MoWeS environment with base demo iTop installed (itop username: admin, password: itop).

vCard contact export from Excel contact list

Recently I needed a way to easily convert a list with contact information into a vCard file, respecting the vCard syntax, in order to import this vCard contact information into a phone.

After some quick Google lookup I ended with this nice Excel created and shared by Savas Geivanidis.

After entering all the contact information into the sheet as instructed, the export macro started executing it’s work and I ended with a vCard file as I wanted. But the process took very long (more than 4 minutes), even if my contact list only contained 100 contacts.

Since Savas was so kind to keep his VBS macro unprotected, I analyzed it and optimized it. The main performance issues I detected was the multiple loops and the cell deletions used in the code. A loop was set up to run over the contact data, and copy all data it into a new sheet. Next a loop was used to run through the data and delete all cell containing the value ‘no data’. Next the new sheet was saved into a text .vcf file.

After rewriting the VBS macro I ended up with one loop, running through the data and saving the information immediately into a text file. The same list of 100 contacts now takes less than 1sec to export instead of more than 4 minutes. Some tests with 1000 contacts were positive as it took only 4sec or less to export. I ended up cleaning up the full sheet and creating a template of it to allow easy reuse. Feel free to use this version 1.0 of vCard Exporter for any purpose.

The optimized source code is shown below for illustration:

Sub Create_vCard() 
  
'

' This Macro creates a vCard from an existing phonebook Excel list

'__________________________________________________________________

' Based on DesktopContacts v0.8 by Savas Geivanidis (savas@mycosmos.gr) egnatia.ee.auth.gr/~aki/dc/DesktopContacts0.8.xls

' Major performance improvements by myT in vCardExporter v1.0

'___________________________________________________________________

    
    Application.ScreenUpdating = False

    ' turns off screen updating

    Application.DisplayStatusBar = True

    ' makes sure that the statusbar is visible

    
    'detect number of data contacts provided

    'mainsheet = ActiveSheet.Name

    mainsheet = "PhoneBook"

    Range("A30000").Select

    Selection.End(xlUp).Select

    nod = ActiveCell.Row

    Range("B30000").Select

    Selection.End(xlUp).Select

    nod1 = ActiveCell.Row

    If nod1 > nod Then

        nod = nod1

    End If

    
    'Check if data is provided

    If nod <= 2 Then

        popup = MsgBox("No contact data could be found. Fill in the contact data in the 'PhoneBook' sheet and provide at least a first or last name for each contact." & vbLf & "See the 'Information' sheet and http://myTselection.blogpsot.com for more information", vbExclamation + vbOKOnly + vbMsgBoxSetForeground, "vCard Exporter: No data found")

        Application.ScreenUpdating = True

        Exit Sub

    End If

    
    'request target filename

    vcardname = Application.GetSaveAsFilename("", "vCard Files (*.vcf), *.vcf", , "Please select the name of the vCard to export")

    If vcardname = False Then

        Application.ScreenUpdating = True

        Exit Sub

    End If

    start_time = Now()

    Application.StatusBar = "Preparing VCARD data"

    
    
    Set vcardFileSystemObject = CreateObject("Scripting.FileSystemObject")

    Set vcardFile = vcardFileSystemObject.CreateTextFile(vcardname, True)

    
    
    'copy formula for VCARD syntax to all rows with contact data

    Range(Cells(1, 61), Cells(1, 101)).Copy

    Range(Cells(3, 61), Cells(nod, 101)).PasteSpecial Paste:=xlPasteFormulas

    Application.CutCopyMode = False

    Range("A3").Select

    
    Application.StatusBar = "Processing contact data..."

    
    vcardFile.writeline ("")

    For iRow = 3 To nod

        For iColumn = 61 To 101

            currentValue = Worksheets(mainsheet).Cells(iRow, iColumn).Value

            Application.StatusBar = "Processing contact data " & Round(((iRow - 2) / (nod - 2)) * 100) & "%" '&" on row " & iRow & ", column " & iColumn & ", value: " & currentValue

            If (currentValue <> "no data") Then

                vcardFile.writeline (currentValue)

            End If

            If currentValue = "END:VCARD" Then

                vcardFile.writeline ("")

            End If

        Next iColumn

    Next iRow

    vcardFile.Close

    Application.StatusBar = "All " & nod - 2 & " contact data processed"

    
    Worksheets(mainsheet).Range(Cells(2, 61), Cells(nod, 101)).ClearContents

    Worksheets(mainsheet).Range("A3").Select

    end_time = Now()

    'DateDiff("s", start_time, end_time)

    Application.StatusBar = "vCard export in " & PrintHrMinSec(DateDiff("s", start_time, end_time)) & "sec done into file: " & vcardname

    Application.ScreenUpdating = True

    
    If (nod - 2 = 1) Then

        popup = MsgBox(nod - 2 & " contact is exported to:" & vbLf & "'" & vcardname & "'" & vbLf & vbLf & "Please send this file to your phone and run it in order to add this contact into it." & vbLf & vbLf & "Original development 'Desktop Contacts v0.8' by Savas Geivanidis (aki@egnatia.ee.auth.gr)" & vbLf & vbLf & "Major layout and performance improvements myT in 'vCard Exporter v1.0' - http://myTselection.blogpsot.com", vbInformation + vbOKOnly + vbMsgBoxSetForeground, "vCard Exporter: data exported")

    Else

        popup = MsgBox(nod - 2 & " contacts were exported to:" & vbLf & "'" & vcardname & "'" & vbLf & vbLf & "Please send this file to your phone and run it in order to add these contacts into it." & vbLf & vbLf & "Original development 'Desktop Contacts v0.8' by Savas Geivanidis (aki@egnatia.ee.auth.gr)" & vbLf & vbLf & "Major layout and performance improvements myT in 'vCard Exporter v1.0' - http://myTselection.blogpsot.com"", vbInformation + vbOKOnly + vbMsgBoxSetForeground, "vCard Exporter: data exported")

    End If

    Application.DisplayStatusBar = True

End Sub

'*********************** 
  
'* This function calculates hours, minutes

'* and seconds based on how many seconds

'* are passed in and returns a nice format

Public Function PrintHrMinSec(elap)

  Dim hr

  Dim min

  Dim sec

  Dim remainder

  
  elap = Int(elap) 'Just use the INTeger portion of the variable

  
  'Using "\" returns just the integer portion of a quotient

  hr = elap \ 3600 '1 hour = 3600 seconds

  remainder = elap - hr * 3600

  min = remainder \ 60

  remainder = remainder - min * 60

  sec = remainder

  
  'Prepend leading zeroes if necessary

  If Len(sec) = 1 Then sec = "0" & sec

  If Len(min) = 1 Then min = "0" & min

  
  'Only show the Hours field if it's non-zero

  If hr = 0 Then

     PrintHrMinSec = min & ":" & sec

  Else

     PrintHrMinSec = hr & ":" & min & ":" & sec

  End If

  
End Function

Google Apps Script demo – Send SMS on new important mail in GMail

Google Drive allows you to easily create and edit Documents, Presentations, Spreadsheets, Forms and Drawings. But not everyone might be familiar with the Google App Script integration functionalities similar to the Visual Basic scripting integration within Microsoft Office macro’s.

Recently, I found this tutorial to link GMail to Google Calendar written by Romain Vialard.

Based on this tutorial, I made the custom script below in order to get a free SMS’s from Google on every new unread e-mail marked as ‘Important’ within GMail. Setting up this little script will give you a brief indication of the power and possibilities of the Google App Script integration.

1. In Google Calendar, register your mobile phone within the ‘Settings’ > ‘Mobile Setup’, to enable SMS notifications. Sending SMS’s from within Google is free of charges, but it might be limited to 50 messages per day.
2. In Gmail, create a new label named 'NbSMS' (Notified by SMS). Once a new mail has been notified by SMS, this label will be assigned to the mail in order to make sure the notification is send out only once and the mail will be ignored by the script once this label is assigned.
3. In Google Drive, create a new Spreadsheet, give it any name.
4. Choose the menu ‘Tools’ > ‘Script Editor’.
5. Choose ‘Create script for: Blank Project’.
6. Copy and paste the following script:

   function sendText() {
     Logger.log('Start of sendText script');
     var today = new Date();
     var nowHour = today.getHours();
     var startTime = 8;
     var endTime = 24;
     
     Logger.log('The SMS notification will only run between: ' + startTime + ' and ' + endTime + ', current hour: ' + nowHour);
     if (nowHour <= startTime || nowHour >= endTime) {
       Logger.log('Quite time, no SMS notification will be sent between: ' + startTime + ' and ' + endTime + ', current hour: ' + nowHour);
     } else {
       // var events = cal.getEvents(new Date(startDateAndTime), new Date(endDateAndTime));
       //based on https://developers.google.com/apps-script/articles/gmail_filter_sms
       var notifiedBySmsLabel = 'NbSMS';
       var unreadPriority = GmailApp.getPriorityInboxUnreadCount();
       var unreadsFound = 0;
       Logger.log("Number of unread emails in your Priority Inbox : " +
                  GmailApp.getPriorityInboxUnreadCount());
       if (unreadPriority > 0) {
         var threads = GmailApp.getPriorityInboxThreads();
         //threads.refresh();
         var now = new Date().getTime();
         var alreadyNotified = false;
         for(i in threads){
           if (!threads[i].isUnread()) {
             alreadyNotified = true;
             if(unreadsFound >= unreadPriority) {
               break;
             } else {
               continue;
             }
           } else { 
             ++unreadsFound;
           }
           var threadLabels = threads[i].getLabels();
           for(y in threadLabels) {
             if(threadLabels[y].getName() == notifiedBySmsLabel) {
               alreadyNotified = true;
               break;
             }
           }
           if (!alreadyNotified) {
             var smsText = 'Mail: '+threads[i].getFirstMessageSubject() + ', from: ' + threads[i].getMessages()[0].getFrom();
             smsText = smsText + ' ' + threads[i].getMessages()[0].getPlainBody();
             Logger.log('Event with SMS will be created with content: ' + smsText);
             var event = CalendarApp.createEvent(smsText,
                                                 new Date(now+60000),
                                                 new Date(now+60000));
             event.setDescription(smsText);
             event.addSmsReminder(0);
             var label = GmailApp.getUserLabelByName(notifiedBySmsLabel);
             label.addToThread(threads[i]);
           }
         }
         //threads.refresh();
         Logger.log('All important messages treated and label ' + notifiedBySmsLabel + ' applied.');
       }
     }
   }

   
   



7. Choose the menu ‘Resources’ > ‘Current project’s triggers…’ and add a new trigger.



8. Select the function ‘sendText()’ > Events: ‘Time-driven’ > ‘Minutes timer’ > ‘Every 5 minutes’, and save the trigger.



9. Save the script.



10. Click the Run ► icon. A pop-up opens asking you for your authorization to access the Gmail and Google Calendar services.



11. Click the Authorize button.



12. Click the Run ► icon again.



13. Choose the menu ‘View’ > ‘Logs’ to see the log output.



14. Debugging is possible using the bug icon. Set some breakpoints by clicking on the line number next to the script.



15. After one minute, you should receive a text on your mobile device, containing the subject of the important unread emails within GMail.



16. To see what other functionalities are available within GMail, Google Calendar en other Google services, see the Google App Script reference guide.



17. Some other interesting tutorial to work with Google Calendar events is available in this blog post.

Update 09/04/2014: optimized the labeling to only apply the label to messages which were not yet labeled, instead of all messages in priority inbox.

Update 21/10/2015: Google no longer supports to get notified by SMS for a calendar item. So the approach above won’t work any longer (calendar item will be created, but no sms will be received).

Svn file full history

If you’d need to search in the full history of a file stored in SVN, the script below can make an export of all changes ever applied into a file, starting for the first revision (full file) and adding each time an overview of all changes applied.

If you remember you ever applied some changes in a file, but removed it afterwards, this can help to search in the full history. Only tested on text based files.

For each revision, the revision number, author, timestamp and svn comments will be listed, followed by an overview of the removed lines, added lines, update lines etc. The initial revision will be a full extract of the original file.

Usage: save the SvnFileFullHistory.bat script in an SVN folder next to the file for which you’d need a full history extract. Drop the svn file onto the bat script to start the script and retrieve the full history of the dropped svn file. The full svn file history will be saved in a txt file in the same folder and with the same base name as the file to extract, but adding “-FullSvnHistory.txt” behind the file name. The extract can take a while, depending on the number of revisions and the size of the svn file. But while the extract is ongoing, the “…-FullSvnHistory.txt” can be read already, reloading it to get future updates.

A similar linux shell script has been created as well (not fully tested).

Based on information found on stackoverflow by user ladenedge.

Full script content:

@echo off   
TITLE SVN - Full file history    
REM Original source: http://stackoverflow.com/questions/282802/how-can-i-view-all-historical-changes-to-a-file-in-svn and http://stackoverflow.com/questions/5622367/generate-history-of-changes-on-a-file-in-svn/5721533#5721533
echo Copy this bat script next to the checked out svn file on which to get full svn history. Drag and drop the svn file onto the bat script to start fetching the info (or a open command window and provide the name of the svn file as first parameter to the bat script execution)    
if "%1%"=="" pause    
set file=%1    
set report=%file%-FullSvnHistory.txt  
if [%file%] == [] (   
  echo Usage: "%0 <file>"    
  exit /b    
)
echo Retrieving svn history of file, please wait...   
echo The report will be saved in the file: %report%.    
echo To stop the process press Ctrl+c.
rem first revision as full text   
for /F "tokens=1 delims=-r " %%R in ('"svn log -q %file%"') do (    
  svn log -r %%R %file% > %report%    
  svn cat -r %%R %file% >> %report%    
  goto :diffs    
)
:diffs
rem remaining revisions as differences to previous revision   
for /F "skip=2 tokens=1 delims=-r " %%R in ('"svn log -q %file%"') do (    
  echo.    
  svn log -r %%R %file% >> %report%    
  svn diff -c %%R %file% >> %report%    
)