There is quite a sophisticated work behind this website so I decided to share it a little

The Calculator

The quote calculator uses Google maps api which is declared as a script in the html head:

    <script language="JavaScript" type="text/javascript" src="googleMapAPIv3.js"></script>

The quote calculator basically is a table with a java script behind it. The java is initialised when user clicks on tab with the table.

     onclick="initialize()"

initialize() is a function defined in java script file. It looks at "Base" option selected under the table and sets the route starting point marker on the map. The points are set as latitude and longitude for precise placement.

function initialize() {
    if (!initialized) {
        if (!document.base.bs[1].checked) {
            var myLatlng = new google.maps.LatLng(53.79849, -1.58375);
        }else{
            var myLatlng = new google.maps.LatLng(53.776201, -1.545395);
        }
        var myOptions = { center: myLatlng, zoom: 15, mapTypeId: google.maps.MapTypeId.ROADMAP };
        map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
        var marker = new google.maps.Marker({ position: myLatlng });
        marker.setMap(map);

        directionsDisplay.setMap(map);
        initialized = -1;
    };
 }

Then back in html, there is a line under the table which is responsible for map to display.

            <div align="center" id="map_canvas" style="width:100%; height:400px"></div>

The actual calculator is trigered whenever there is a change in any of editable fields on the table.

             onchange="reinit(); return false;"

You make a table cell editable by defining it's input type e.g. "button", "text".

reinit() is again a function defined in the same java script. It runs all function in script to make sure all values from all cells are read and map is drawn and price is displayed. It resets a variable "initialized" to 0 for other functions to know that this is not a minor change like adding a new row but an important chage which requires a whole new calculation. Then it calls all functions necessary to draw the map, calculate the route and the quote. They are defined as separate functions so they can be called sepparately if necessary.

function reinit() {
        initialized = 0;
        initialize();
        calcRoute();
        getQuote();
}

The calRoute() function reads the legs from column of postcodes which is pretty easy. First you refer to the table and define it as variable.

    var table = document.getElementById("tableID");

Then you cycle through values and read them into array.

    for (i = 0; i < rowCount - 3; i++) {
        postcode = table.rows[i+1].cells[1].firstChild.value;
        if (!(postcode == "")) {
            waypts.push({
                location:postcode,
                stopover:true
            });
            rownum.push(i+1);
        };
    };

 

It omits the 0 row which is header and last 2 rows which display results.

Then it gets the time and distance between legs and converts them into hours and miles. Speed is a separate variable for different vahicles. E.g. for a van it is 1.2 compared to Google estimate.

                miles = route.legs[i].distance.value;
                minutes = route.legs[i].duration.value;
                table.rows[rownum[i]].cells[2].innerText = Math.round(miles/1609.344*10)/10
                table.rows[rownum[i]].cells[3].innerText = Math.round(minutes/60*speed);

Then it calls function getQuote()

The getQuote() function multiplies miles with cost of fuel, hours with cost of labour multiplied with number of men driving and working. It's all primary school maths.

    Var total = Math.round((miles * fuel + drivingminutes * speed / 60 * 20 + workinghours * 20 * (1 + men/2))/5)*5;

Then, if user decides to add a row, it is a whole new subroutine which has to define all cells already defined in html again with all the buttons, onClick events and properties.

function addRow(tableID) {

    var table = document.getElementById(tableID);
    var rowCount = table.rows.length;
    var row = table.insertRow(rowCount-2);

    if (rowCount < 11) {
        document.getElementById("rownum").value = rownum;

        var cell1 = row.insertCell(0);
        var element1 = document.createElement("input");
        element1.type = "button";
        element1.value = "x";
        element1.setAttribute("onClick", "DeleteRow(this.parentNode.parentNode); calcRoute(); return false;");
        cell1.appendChild(element1);

        var cell2 = row.insertCell(1);
        var element2 = document.createElement("input");
        element2.type = "text";
        element2.setAttribute("onChange", "calcRoute(this.parentNode.parentNode.rowIndex); return false;");
        cell2.appendChild(element2);

        var cell3 = row.insertCell(2);
        var cell4 = row.insertCell(3);

        var cell5 = row.insertCell(4);
        var element5 = document.createElement("input");
        element5.type = "text";
        element5.value = "0";
        element5.setAttribute("onChange", "getQuote(); return false;");
        cell5.appendChild(element5);
    } else {
        alert("Max 8 waypoints allowed by Google.");
    };
}

The function to delete the row, on contracry, is beautifully simple.

function DeleteRow(r){
    r.parentNode.removeChild(r);
}

 

The Joblist

The joblist is magically updated every time I save my excel joblist on my PC.

When the document is saved, it triggers macro command which saves an xml file which is uploaded to my website server where it is read into table by java script.

To catch the save event and use it as a trigger, press Alt+F11 in excel and double click ThisWorkbook under current document and paste the following code. You would need to resave the document in one which contains macros (*.xlsm)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 'Write your code here
End Sub

But first you create a new worksheet in your document which lists your joblist starting from today onwards. To do that you find the row number where the today date is

    =MATCH(TODAY(),Joblist!A:A)

This will return an index which you continue down with simple =A1+1 for next 100 lines or so.

Then you refer to the newly created column to bring over the actual table contents.

    INDEX(Joblist!A:A,$A1)  INDEX(Joblist!B:B,$A1)  INDEX(Joblist!C:C,$A1)

Now, the actual process of writing xml file is done by

    Open "C:\FTPBox\joblist.xml" For Output As #1
             ...
    Close

First the header is written

        Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & "?>"
        Print #1, "<Joblist>"

Then it goes through all 100 cells and 3 columns one cell at a time

        For Each rw In Sheets("Joblist").Range("B1:D100").Rows
            Print #1, Chr(9) & "<Row>"
            For Each cel In rw.Cells
                ....
            Next
        Next

To tell which column is which, it counts them

                n = n + 1
                Select Case n
                        Case 1
                                 ...

                        Case 2
                                 ...
                        Case 3
                                 ...
                 End Select

The Case 1 and Case 3 is pretty easy

        Print #1, Chr(9) & Chr(9) & "<Date>" & cel & "</Date>"
        Print #1, Chr(9) & Chr(9) & "<Price>" & cel & "</Price>"

 

Case 2 requires more tweaking. To filter out private information, the macro cycles through characters of the cell and looks for patterns. It creates a string where every digit counts as 2 and every letter as 1 then if it encounters something else e.g. space, it looks if the gathered string looks like a postcode or a part of the postcode. It treats differently the first word in string where it does not count "." as a space because of time information at beginning of cell e.g. 10.00.

                        For m = 1 To Len(cel)
                            char = Mid(cel, m, 1)
                            Select Case Asc(char)
                                Case 48 To 57
                                    code = code & 2 ' Digits
                                    cel2 = cel2 & char
                                Case 65 To 122
                                    code = code & 1 ' Letters
                                    cel2 = cel2 & char
                                Case Else
                                    Select Case code
                                        Case "12", "112", "1122", "211", "1211", "11211", "112211"
                                            cel1 = cel1 & cel2 & " "
                                            code = ""
                                            cel2 = ""
                                        Case Else
                                            code = ""
                                            If first = 0 Then
                                                cel1 = cel1 & cel2 & char
                                                If char = " " Then first = 1
                                            End If
'MsgBox (code & Chr(13) & cel1 & Chr(13) & cel2)
                                            cel2 = ""
                                    End Select
                            End Select
                        Next

It finishes the xml file with lines

    Print #1, Chr(9) & "</Row>"
    Print #1, "</Joblist>"

It is uploaded to FTP with curl

    Call Shell("C:\FTPBox\curl.exe -T C:\FTPBox\joblist.xml ftp://www.boxvan.co.nf/boxvan.co.nf/joblist.xml --user user:pasw"

Then there is a Java script on the website which reads the values into table on load. It starts with some gibberish to distinguish between browsers.  But the essence is that it kind of designs the table with the first line and then adds rows to it. No need for html code what so ever. And the wonderful part about it is that you don't need PHP for that. Don't believe people who say to you that if you want to read a text document into your HTML table, you need PHP. That's rubbish. You don't need it. Java is sufficient. The magic is done by xmlDoc.getElementsByTagName function.

document.write("<table><tr><th>Date</th><th>Job</th><th>Price</th></tr>");

var x=xmlDoc.getElementsByTagName("Row");
for (i=0;i<x.length;i++)
  { 
  document.write("<tr><td>");
  document.write(x[i].getElementsByTagName("Date")[0].childNodes[0].nodeValue);
  document.write("</td><td>");
  document.write(x[i].getElementsByTagName("Job")[0].childNodes[0].nodeValue);
  document.write("</td><td>");
  document.write(x[i].getElementsByTagName("Price")[0].childNodes[0].nodeValue);
  document.write("</td></tr>");
  };
document.write("</table>");

All the scripts are not encoded and page source is pretty straight forward. So please feel free to look it up and use it in your projects at your risk :-)