Monday, March 8, 2010

Meetups Coming To Town - A Mashup of Meetup, Google Gears, & Google Map APIs

Meetup.com is one of the social network site that facilitates offline group meetings in various locations around the world. I have been attending some of the groups' meetups since 2009 and found they were very helpful, most important of all - all the meetups I went to were FREE!

The Meetup.com API is very easy to use and everybody received one API key when registering with the site. I decided to utilize the API by integrating it with Google Gears and Map APIs to come out a mashup for people can easy to find out what meetups happening in his(her) own town.

To automatically find out the locations that users are in, the Google Gears API is used to retrieve Geolocation. Users will need to install Google Gears to use the feature, otherwise, they have to manually keyin the location information.

The very first time you run the mashup it will ask your authorization -

 


A PHP file is running at back-end to communicate with Meetup API and the main program - mymu.html, I need to modifiy the header of the json output from Meetup in order for Dojo to accept it as a dojox.data.jsonreststore datastore to feed the spreadsheet displayed on top.
<?php

$state = urlencode($_GET["state"]);
$city = urlencode($_GET["city"]);

if (isset($_GET["page"]))
$page = $_GET["page"];
else
$page = "50";

if (isset($_GET["topic"]))
$topic = $_GET["topic"];
else
$topic="";

if (! isset($_GET["type"]))
$type=0;
else
$type=$_GET["type"];

switch($type) {
 case "0":
  break;
   
 case "1":
  $curl = curl_init();
  $req = "http://api.meetup.com/events.json/?country=us&state=" . $state .  "&city=" . $city . "&key=YourMeetupApiKey";
  if ($topic != "") {
  $req .= "&topic=" . $topic;
  }
  if ($page != "") {
  $req .= "&page=" . $page;
  }
  curl_setopt($curl, CURLOPT_URL, $req );
  curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
  $result = curl_exec($curl);

  curl_close($curl);
  $result=str_replace("\"results\":", "identifier:\"id\", items:", $result);
  echo $result;
  break;
}  
?>
You can test the program by going to maps.auctions411.com and see the detail javaScript and HTML codes from there. Beside the "State" and "City" filters, a optional "Topic" field is also available for you the limit the results. A Dojo speradsheet on upper page will show all the future meetups coming to your city, and a Google Map on the lower page will display where they will be held. Click at the row of the spreadsheet or the Google map marker, a more detail window will pop-out.
The main JavaScript function - init() will try to find the user geolocation first, then retrieve data from Meetup API and populate the information on Dojo spreadsheet and a Google Map.(init2)
function init() {
  if (!window.google || !google.gears) {
    alert('Gears is not installed, please get it at http://gears.google.com ');
    return;
  }
 
  function successCallback(p) {
 if (p.gearsAddress.country == "United States") {
 dijit.byId('city').attr('value', p.gearsAddress.city);
 dijit.byId('state').attr('name', p.gearsAddress.city);
 }
 init2();
  }
 
  function errorCallback(err) {
    var msg = 'Error retrieving your location: ' + err.message;
    alert(msg);
  }
 
  try {
    var geolocation = google.gears.factory.create('beta.geolocation');
    geolocation.getCurrentPosition(successCallback,
                                   errorCallback,
                                   { enableHighAccuracy: true,
                                     gearsRequestAddress: true });
  } catch (e) {
    alert('Error using Geolocation API: ' + e.message);
    return;
  }
}

function init2() {
  gridView();  //create spreadsheet
 loadMap(dijit.byId('city').value + ", " + dijit.byId('state').value); // load the city map
 drawMap(store); // draw the markers
 }
 

Sunday, February 28, 2010

Use Dojo Grid to Display Google Spreadsheets

Google Spreadsheets is not only a basic spreadsheet product,but also a very powerful tool for web applications. By using the Spreadsheets Data API it can be easily performed as a web-based database to generate a web form, a online shopping cart, a Google Map mashup .... etc.

Dojo DataGrid is a JavaScript library with spreadsheet view function , it supports datastores like Csv, Json, XML, Googlefeeds... etc. However, there is no Google Spreadsheets datastore for Dojo yet. In order to let Dojo Grid easily read Google Spreadsheets cells data, I created a PHP program to generate the CSV file from Google Spreadsheet as a dojox.data.CsvStore.

The PHP Zend Gdata 1.10.0 library is used to talk to the Google Data API, it takes care the client login, file listing, spread sheet files and worksheet tokens retrieving and cells data feed. retrieve the spreadsheets file list and key tokens, once user decide which file to read,

The first part of the PHP is for client login :
<div style="color: blue;">
<span style="font-size: x-small;"><i>set_include_path(get_include_path() . PATH_SEPARATOR . '/var/www/html/ZendGdata-1.10.0/library');
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Http_Client');
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');</i></span>
<span style="font-size: x-small;"><i>
$user = $_GET["id"];
$pass = $_GET["pass"];
if(isset($_GET["key"]))
$key = $_GET["key"];
else
$key="";</i></span></div>
To read the spreadsheets list :
<div style="color: blue;">
<span style="font-size: x-small;"><i>function promptForSpreadsheet1($gdClient)
{
    $feed =$gdClient->getSpreadsheetFeed();
    $i=0;
    $arr = array();
    foreach( $feed->entries as $entry) {
        $currKey = split('/', $entry->id->text);
        $token = $currKey[5];
        $arr[] = array("name" => $entry->title->text, "id" => $token);
        $i++;
    }
    $jsonStr = json_encode($arr);
    echo "{'identifier':'id','label':'name','items':$jsonStr}";
}</i></span></div>
To read the worksheets list of select spreadsheet :
<span style="color: blue; font-size: x-small;"><i>function getFormWorksheet($gdClient)
{
    global $key, $wkid;
    $query = new Zend_Gdata_Spreadsheets_DocumentQuery();
    $query->setSpreadsheetKey($key);
    $feed = $gdClient->getWorksheetFeed($query);
     echo printFeed($feed);
     $input = getInput("\nSelection"); */
    $currWkshtId = split('/', $feed->entries[0]->id->text);
    $wkid = $currWkshtId[8];
}</i></span>
To read the header and row data and convert them into a CSV formatted file  :
<span style="color: blue; font-size: x-small;"><i>function cellsGetCsv()
{
    global $gdClient, $key, $wkid;
    $arr = array();
    $query = new Zend_Gdata_Spreadsheets_CellQuery();
    $query->setSpreadsheetKey($key);
    $query->setWorksheetId($wkid);
    $feed = $gdClient->getSpreadsheetCellFeedContents($query);
    $jsonStr = json_encode($feed);
    $pre_key="A";
    $columns=0;
    $rows=0;
    $header = array();
    foreach($feed as $key=>$entry){
        if (substr($key, -1) <> "1") {
            $columns = convertAlphabetToInt(substr($pre_key, 0, -1));
            $jsonStr = json_encode($header);
            break;
        }
        $header[] = $entry["value"];
        $pre_key=$key;
    }
    $lastkey = end(array_keys($feed));

    for ($k=1; $k<=strlen($lastkey); $k++){
        if ( is_numeric(substr($lastkey, strlen($lastkey)-$k, 1))==false){
            $rows=(int)substr($lastkey, strlen($lastkey)-$k+1);
        }
    }

    $head = "";
    $data = "";
    for ($i = 0; $i< $columns; $i++){
        $head .=  $header[$i] . ",";
    }
    $arr = array();
    for ($j=2; $j<=$rows; $j++){
        $line = '';
        for ($m=1; $m<=$columns; $m++){
            /*    $value = $feed[convertIntToAlphabet($m)+$j]->value; */
            if(isset($feed[convertIntToAlphabet($m). (string)$j]))
            $value = $feed[convertIntToAlphabet($m). (string)$j]["value"];
            else
            $value = "";

            if ((!isset($value)) OR ($value == "")) {
                $value = ",";
            } else {
                $value = str_replace('"', '""', $value);
                $value = '"' . $value . '"' . ",";
            }
            $line .= $value;
        }
        $data .= trim($line)."\n";
    }
    $data = str_replace("\r", "", $data);
    echo "$head\n$data";
}

function convertAlphabetToInt($alpha_string) {
    $int_wert=0;
    $potenzcounter=0;
    for ($i=strlen($alpha_string);$i>0;$i--) {
        $ordinalwert=(ord(substr($alpha_string,$i-1,1))-64);
        $int_wert+=$ordinalwert*pow(26,$potenzcounter);
        $potenzcounter++;
    }
    return $int_wert;
}

function convertIntToAlphabet($int_wert) {
    $alpha_string="";
    if($int_wert%26>=1) {
        $alpha_string=chr(($int_wert%26)+64).$alpha_string;
        $alpha_string=convertIntToAlphabet($int_wert/26).$alpha_string;
    }
    return $alpha_string;
}</i></span>
The demo program - MAP123.html which interacts with the PHP file (ssheet.php) thru Ajax will need your GMail(or Google Apps account) id and password -
 
If successfully login, then it will show the list of available spreadsheets for you to choose -
Once you decide the spreadsheet to use, click at "get spreadsheet" button to retrieve, however, since the program does not offer you the choice of worksheets (Google Spreadsheets supports multi-worksheet), it will always grab the first available worksheet from the spreadsheet.
A spreadsheet of exact layout of original Google Spreadsheet will be displayed on top of the page!

The bottom part of the program is to display the Google map with the locations(addresses) information on the spreadsheet.As long as there is a header called "ADDRESS" the Google Map will be triggered and display all addresses on the "ADDRESS" column. Click at any row, it will diplay the information windows for the selected row on the Google Map.

You can try the demo program here, or you can download the whole files here.

Tuesday, February 16, 2010

Add A Google Map To XPages

A snapshot of the Lotus XPage embedded Google Map

The goal of this XPage practice is to add a web search function for a traditional Lotus CRM database with a embedded Google Map view. A Google map with address markers will be displayed under based upon the customers search result .

This is the original view of the Notes database which I used for the data source, the database need to be fully indexed for search function to perform correctly -
 

This XPage named "search4" was created under the same database and the layout of this XPage includes three Panels - first is for the search bar, second is for the View control and the third is for the Google Map canvas.

First Panel has two components, one is the "Edie Box" for the search string, we need to bind it to the "View Scope" and name it "searchString".

The second component is the search Button which need to add a "Simple Action" under its onClick event, then choose "Open Page" as the function, the name of the Page should be "computed" -
"search4.xsp?search=" + viewScope.searchString


The View (data grid) for the search result is named "viewPanel1" (default), I selected the data source from Domino view "location"
add following content at "search" field which should be a server side JavaScript(compute dynamically) -
context.getUrlParameter("search");
The third Panel is the easiest one which you just need to give it a name for Google Map API can access it, we named it "map" (actually, it become "view:_id1:map" on web page).

Make "dojoParseOnLoad" on XPage property as "true" since we need to use dojoAddOnLoad to load our JavaScript codes after page loaded.

There is a JavaScript library (geo4.js) needed inside this database script library. The purpose of this library is to dynamically load the Google Javascript header after page load , and to execute the Google API for populating the address markers on the map. I use the the Google Maps API geocoding service via the GClientGeocoder object to decode the address and create marker. To make my life easier, I did some hacks to avoid the javascript variable conversion which I still have problem dealing with, I had to manually find out the name of the table and div on the web page I need and hard-code them on the geo4.js (red section).

Following is the JavaScript codes - geo4.js stored at script library.
var map;

function loadScript() {
  var script = document.createElement("script");
  script.type = "text/javascript";
  script.src = "http://maps.google.com/maps?file=api&v=2.x&key=your_google_api_key&async=2&callback=loadMap";
  document.body.appendChild(script);
}
 
function loadMap() {
  map = new GMap2(document.getElementById("view:_id1:map"));
  map.setCenter(new GLatLng(38.4419, -102.1419), 4);
  map.setUIToDefault();
  mapMe();
  }
 
function mapMe() {
var tb=document.getElementById("view:_id1:viewPanel1");
var tr=tb.getElementsByTagName("tr");

for (i=1; i<tr.length; i++)
var td=tr[i].getElementsByTagName("td");

var sp=td[4].getElementsByTagName("span");
if (sp[0] != undefined)
showAddress(sp[0].firstChild.data, map);

  }
}
 
  function showAddress(address, m) {
      var geocoder = new GClientGeocoder();
      geocoder.getLatLng(
        address,
        function(point) {
          if (!point) {
           // alert(address + " not found");
          } else {
           
            var marker = new GMarker(point);
            m.addOverlay(marker);
                var html = address;
              GEvent.addListener(marker, "click", function() {
             marker.openInfoWindowHtml(html);
              })   
            //marker.openInfoWindowHtml(address);
          }
        }
      );
    }
   
  function showAddressInfo(address, m) {
      var geocoder = new GClientGeocoder();
      geocoder.getLatLng(
        address,
        function(point) {
          if (!point) {
           // alert(address + " not found");
          } else {
           
            var marker = new GMarker(point);
            m.addOverlay(marker);
                var html = address;
              GEvent.addListener(marker, "click", function() {
             marker.openInfoWindowHtml(html);
              })   
            marker.openInfoWindowHtml(address);
          }
        }
      )
    }
   
    dojo.addOnLoad(loadScript);

You can download the design template of this Notes database by clicking here

Monday, February 8, 2010

PayPal 2.0 Certified Developer

 
Just passed the PayPal 2.0 Certified Developer exam couple days ago and received a congratulations email from PayPal who also put me on the list of PayPal Certified Directory! I have been using eBay API (Java) to integrate with Lotus Notes applications since 2001, my company was one of the certified eBay developers back then. Early last year due to a emergent custom project, I started to use PayPal API  (PHP). As a matter of fact, PalPal is an eBay company, therefore, some parts of PayPal API are very similar to eBay API, it sure helped me pick up the developing speed a lot! The PayPal Developer Certification needs to pass three exams - Website Payments Standard, Website Payments Pro and Payflow Gateway. I am very luck to become one of the six certified developers in New York area.

Wednesday, February 3, 2010

Install Lotus Domino Server 8.5.1 on IBM Cloud

Started from January 25, 2010 IBM Smart Business Development and Test on the IBM Cloud is opening a new Pilot Service for selected customers. Whoever with or without cloud experience should deem this as a great opportunity to be the first few people to taste IBM Cloud and it is FREE! Follow the instructions on front page you should be able to register and get the right to create your own application server(instance) on IBM cloud right away!

It is easy to create an instance on IBM cloud, even faster than Amazon EC2, however, there are not too many choices for OS, I chose SUSE Linux Enterprise v10 SP2 and Redhat Enterprise Linux (32-bit) v5.4 to begin with. Following video is the best tutorial to teach you how to connect to your own instances by SSH client: putty -


My goal is to install the latest Lotus Domino server 8.5.1 on IBM Cloud since there is no image pre-installed for Lotus Domino/Notes yet. After successful log in, you can type
sudo -i
groupadd notes
useradd -G notes notes
to have root access right, create "notes" user and "notes" group, assign "notes" user under "notes" group. Upload the Domino 8.51 installation file(lotus_domino851_xlinux_CZ5RWEN.tar) which I used WinSCP. Find the tmp directory to unzipped the tar file, then type "./install" to start the installation -

tar -xf lotus_domino851_xlinux_CZ5RWEN.tar
cd linux/domino
./install

I used all default choices to complete the install. You can always change to your own setting.


After server software installed, you need to use remote server installation to finalize the Domino server setup, I have uploaded a server id file since I want to use it to replicate big chunk of data to do my testing. Unlike the Amazon EC2, IBM Cloud doesn't support any tool for developers to control the ports. Unfortunately, the port 8585 for Domino remote installation is not open, therefore you have to assign a different port to run, I used port 8080. Or you can always post your request on support forum to ask, the original Domino port 1352 was not opened until somebody asked for it.

/opt/ibm/lotus/bin/server -listen 8080

Find a local Windows Domino server type following command under DOS console
nserver -remote
to start the remote server setup, close the listen job when finish.

After complete the setup, log into the instance by notes id, go to /local/notesdata (if this is your assigned data directory), the type

/opt/ibm/lotus/bin/server

Congratulations! You have your first Domino server up and running on IBM Cloud!

Type "top -u notes" to show all notes tasks.

A snapshot of the Cloud Domino server under Administrator view.


Set Up Lotus Domino 8.02 Partitioned Servers on Amazon EC2 - Part I

Sunday, January 31, 2010

Warehouse Automation (II) - LEI As The Data Pump

IBM Lotus Enterprise Integration (LEI) 7.01 is used to act as the data pump, it retrieves scan data (CSV file) on File Server into MySQL database at application server. Even the whole process is nothing to do with Lotus Notes database, the LEI is still my best choice(if performance meets) since no programming(Java, LotusScript or PHP) needed and easy to schedule the tasks.

LEI connection document for Text ODBC Connection :
this is to connect the S/N text file uploaded by GoodSync

 

LEI connection document for MySQL ODBC Connection :

LEI activity document which is a "Direct Transfer" process :
this copy the S/N data into MySQL SNOUT2 table
 
If success, it will run an additional job - "UPDATE SNOUT WITH NEW SN FROM SNOUT2"
  
This is a schedule job, however, it also can be called by other process
 


Command Activity document - UPDATE SNOUT WITH NEW SN FROM SNOUT2

It execute the following SQL command which is to update the SN database with new SN on SNOUT2 table

INSERT INTO champ.SNOUT SELECT * FROM champ.SNOUT2 WHERE CONCAT(INVNO, SN) NOT IN (SELECT CONCAT(INVNO, SN) FROM champ.SNOUT) GROUP BY INVNO, PN, SN;
  
  

Wednesday, December 16, 2009

Warehouse Automation (I) - Create A Low-cost Smart Bar-Code Scanning Solution



Purpose -
To implement a low-cost smart bar-code scanning solution for the warehouse inventory control, automatic data upload process through MS ActiveSync synchronization.

H/W (PDA with built-in Barcode scanner) - 
  • Motorola(Symbol) PPT8800 or MC series, you can find PPT8800 under $200 at eBay, however, I used MC70 for this practice
  • Networked PC with Windows XP, connect the PDA thru USB port
  • Server hosting the database which keep all S/N information, I used a Linux server with MySQL for this practice. You can connect to any database depneds on your environment, even to those non-database applications like Quickbooks, Peachtree, Navision, SAP B1, Lotus Notes ... etc. As long as you set up the connector correctly (normally ODBC)
S/W - 
  • TracerPlus Pro - barcode scan S/W ($215, trial version available)
  • Tracer Desktop - layout editor (free)
  • MS ActiveSync 4.5 - connect PDA and PC for synchronization (download from MS)
  • GoodSync - since ActiveSync only sync data under My document folder we use GoodSync to sync scan data which is under program folder



    Example file - download
    This is a TracerPlus Desktop project file, it includes two scan forms, one for outgoing invoices S/N scanning, he other for receiving S/N scanning. Some checking mechanism have been added to avoid double scan or wrong formats.
    Fields of Outgoing Invoice S/N Form :
    INVNO: Invoice#
    PN: P/N
    SN: S/N
    DATE: scanned date
    SCANNER: scanned by

    Fields of Receiving Orders S/N Form :
    REFNO: PO#
    PN: P/N
    SN: S/N
    DATE: received date
    SCANNER: scanned by

    Implement Data Synchronization and Database Connector -
    set up the GoodSync to upload the scan data to file server everytime when the scanner is attached to the PC, this is a one-way sync








    ODBC setting: the export files from TracerPlus we have predefined as CSV, therefore, we use the "MS Text Driver" for our ODBC System DSN, 


    Once the ODBC ready, we need to decide how to connect to the target database to add the new S/N information. We should have a front-end process to filter the scan data make sure they are before we update the database.

    I implemented three scheduled tasks by different tools which just wan to show you the basic concept to automate your inventory management -
    • update MySQL S/N table by Using PHP and Cron jobs
    • use Invoice# as key to update Quickbooks invoices and add the S/N information into Memo field by using QODBC, ASP and Windows scheduled tasks
    • use Invoice# as key to update Lotus Notes S/N control database by using Lotus LEI (Lotus Enterprise Integrator)
    On the following article, I will publish more details about how to implement these three data pumping tasks.