vtiger

[logz05]

I've been playing with neofis's great application of a class to export to a Palm (see http://forums.vtiger.com/viewtopic.php?t=3079&highlight=palm )

The class was written however in 2001 and the new Palm's have a whole bunch of fields that it does not address.

(I use 4.2.3 with Patch 2 heavily hacked)

I needed to get a lot more info from the contacts into the palm address book. Here is the code from modules/contacts/contact.php:


Code:  function create_palm_export_contacts_query($only_me=false)
        {
        global $current_user;
       
          $query = "select
                      contactdetails.firstname CName,
                      contactdetails.lastname CLastName,
                      purchaseordercf.cf_638 Csal,
                      contactdetails.email CEmail,
                      purchaseordercf.cf_642 CPhone,
                      purchaseordercf.cf_644  CMobile,
                      purchaseordercf.cf_646  CFax,
                      poshipads.ship_street CStreet,
                      poshipads.ship_code CCode,
                      poshipads.ship_country CCountry,
                      poshipads.ship_city CCity,
                      poshipads.ship_state CState,
                      contactsubdetails.homephone chome,
                      purchaseorder.duedate cbirthday,
                      purchaseordercf.cf_648 carrival,
                      purchaseordercf.cf_652 cdepart,
                      purchaseordercf.cf_700 ctransp,
                      purchaseordercf.cf_702 csuppok,
                      purchaseordercf.cf_666 cliters,
                      purchaseordercf.cf_668 chours,
                      purchaseordercf.cf_696 cairport,
                      vendor.vendorname cvendor,
                      vendor.phone cvphone,
                      vendorcf.cf_492 cvemerg,
                      vendorcf.cf_498 cvdirec,
                      vendorcf.cf_500  cvdirph,
                      vendorcf.cf_514 cvcont,
                      purchaseordercf.cf_620 croomno,
                       accountscf.cf_538 creferna,
                       account.phone as cacphone,
                       account.otherphone as cacophon,
                      account.accountname as CCompany
   
                    FROM contactdetails
                         inner join crmentity on crmentity.crmid=contactdetails.contactid
                                                                                 LEFT JOIN users ON crmentity.smownerid=users.id
                                                                                 LEFT JOIN account on contactdetails.accountid=account.accountid
                                                                                 LEFT JOIN accountscf on account.accountid=accountscf.accountid
                             LEFT JOIN contactaddress on contactaddress.contactaddressid=contactdetails.contactid
                                                                                 left join purchaseorder on contactdetails.contactid=purchaseorder.contactid
                                                                                 left join poshipads on purchaseorder.purchaseorderid=poshipads.poshipaddressid
                                                                                 left join purchaseordercf on purchaseorder.purchaseorderid=purchaseordercf.purchaseorderid
                                                                                 left join vendor on purchaseorder.vendorid=vendor.vendorid
                                                                                 left join vendorcf on vendor.vendorid=vendorcf.vendorid
                             LEFT JOIN contactsubdetails on contactsubdetails.contactsubscriptionid=contactdetails.contactid
                        WHERE crmentity.deleted=0 and users.status='Active'";
                                                                         
                        if ($only_me) $query.=" AND users.id='" .$current_user->id."'";
                                                                         
       return $query;
        }


basically you can add a lot of information from almost any table in the database as long as there is relation between them (now I am beginning to even like innodb tables!)

To get this into the Address Book of the Palm needs extensive use of th Notes field (which is defined in the php-pdb class but not used in the exportPalm.php. Here's how I did it in exportPalm.php:

Code: function export_contacts()
{

   global $adb;
   $focus = 0;
   $content = '';
   $focus = new Contact;
   $log = LoggerManager::getLogger('export_contacts_palm');
   $db = new PearDatabase();
       
  if ( isset($_REQUEST['all']) )
   {
      $where = '';
   }
       
   else
   {
      $where = $_SESSION['export_where'];
   }

   $order_by = "";
  $more_information=true;
 
  if ((isset($_REQUEST['allContacts']))&&($_REQUEST['allContacts']=="1")){
   $my_contacts=false;
  }
  else $my_contacts=true;

  $query = $focus->create_palm_export_contacts_query($my_contacts);
 
  $result = $adb->query($query,true,"Error exporting $type: "."<BR>$query");

  $datebook = new PalmAddress();
  //$categories = array(1 => 'Vtiger Contacts',2 => 'Vtiger Leads');
  //$datebook->SetCategoryList($categories);
 
  $num_records=$adb->getRowCount($result);
  while($val = $adb->fetchByAssoc($result, -1, false)){
    $num_records--;
    $id = $val["id"];
    $Record = array();

    //LastName
    if (br2nl_vt($val["clastname"])!="") $Record['LastName']=br2nl_vt($val["clastname"]);
    //FirstName
    if (br2nl_vt($val["cname"])!="") $Record['FirstName']=br2nl_vt($val["cname"]);
    //Company

    if (br2nl_vt($val["ccompany"])!="") $cusco.=br2nl_vt($val["ccompany"])."Tel: ";
   // if (br2nl_vt($val["crefna"])!="")  $cusco.=br2nl_vt($val["crefna"])." Tel: ";
    if (br2nl_vt($val["cacphone"])!="")  $cusco.=br2nl_vt($val["cacphone"]);
    //if (br2nl_vt($val["cacophon"])!="")  $cusco.=br2nl_vt($val["cacophon"]);
   
              if ($cusco!="") $Record["Company"]=$cusco;


// if (br2nl_vt($val["ccompany"])!="") $Record['Company']=br2nl_vt($val["ccompany"]);

     //Phone1-E-mail
    $Record['Phone1Type']=PDB_ADDR_LABEL_EMAIL;
    if (br2nl_vt($val["cemail"])!="") $Record['Phone1']=br2nl_vt($val["cemail"]);   
    //Phone2
    $Record['Phone2Type']=PDB_ADDR_LABEL_WORK;
    if (br2nl_vt($val["cphone"])!="") $Record['Phone2']=br2nl_vt($val["cphone"]);
   //Phone3
    $Record['Phone3Type']=PDB_ADDR_LABEL_MOBILE;
    if (br2nl_vt($val["cmobile"])!="") $Record['Phone3']=br2nl_vt($val["cmobile"]);
   //Phone4
    $Record['Phone4Type']=PDB_ADDR_LABEL_HOME;
    if (br2nl_vt($val["chome"])!="") $Record['Phone4']=br2nl_vt($val["chome"]);
    //Phone5
    $Record['Phone5Type']=PDB_ADDR_LABEL_FAX;
    if (br2nl_vt($val["cfax"])!="") $Record['Phone5']=br2nl_vt($val["cfax"]);

    //Custom1
    if ((br2nl_vt($val["cbirthday"])!="")&&(br2nl_vt($val["cbirthday"])!="0000-00-00")) $Record['Custom1']=br2nl_vt($val["cbirthday"]);

   //Custom2
    //$cdates = $carrival."/".$cdepart;

    if ((br2nl_vt($val["carrival"])!="")&&(br2nl_vt($val["carrival"])!="")) $Record['Custom2']=br2nl_vt($val["carrival"]);

     //Custom3
    if ((br2nl_vt($val["cdepart"])!="")&&(br2nl_vt($val["cdepart"])!="0000-00-00")) $Record['Custom3']=br2nl_vt($val["cdepart"]);

  //Custom4


    if (br2nl_vt($val["cvendor"])!="") $cus4.=br2nl_vt($val["cvendor"])."\nName: ";
    if (br2nl_vt($val["cvcont"])!="")  $cus4.=br2nl_vt($val["cvcont"]);
        if ($cus4!="") $Record["Custom4"]=$cus4;

   //Notes


$note="";
    if (br2nl_vt($val["croomno"])!="")  $note.="Room No. ".br2nl_vt($val["croomno"])."\nTransp: ";
    if (br2nl_vt($val["ctransp"])!="")  $note.=br2nl_vt($val["ctransp"])."\nSupplier OK: ";
    if (br2nl_vt($val["csuppok"])!="")  $note.=br2nl_vt($val["csuppok"])."\nCons: ";
    if (br2nl_vt($val["cliters"])!="")  $note.=br2nl_vt($val["cliters"])." l / min for ";
    if (br2nl_vt($val["chours"])!="")  $note.=br2nl_vt($val["chours"])." hrs / day\nAirport Service (1=Yes): ";
    if (br2nl_vt($val["cairport"])!="")  $note.=br2nl_vt($val["cairport"])."\nVendor Tel (Norm & Emerg): ";
    if (br2nl_vt($val["cvphone"])!="")  $note.=br2nl_vt($val["cvphone"])."-";
    if (br2nl_vt($val["cvemerg"])!="")  $note.=br2nl_vt($val["cvemerg"])."\nDir Name: ";
    if (br2nl_vt($val["cvdirec"])!="")  $note.=br2nl_vt($val["cvdirec"])." - Tel: ";
    if (br2nl_vt($val["cvdirph"])!="")  $note.=br2nl_vt($val["cvdirph"]);
 

   
   
           if ($note!="") $Record["Note"]=$note;




   //if ((br2nl_vt($val["ctransp"])!="")&&(br2nl_vt($val["ctransp"])!="")) $Record['Note']=br2nl_vt($val["ctransp"]);
   
                                               
    //Address
    if (br2nl_vt($val["cstreet"])!="") $Record['Address']=br2nl_vt($val["cstreet"]);
    //City
    if (br2nl_vt($val["ccity"])!="") $Record['City']=br2nl_vt($val["ccity"]);
    //State
    if (br2nl_vt($val["cstate"])!="") $Record['State']=br2nl_vt($val["cstate"]);
    //ZipCode
    if (br2nl_vt($val["ccode"])!="") $Record['ZipCode']=br2nl_vt($val["ccode"]);
    //Country
    if (br2nl_vt($val["ccountry"])!="") $Record['Country']=br2nl_vt($val["ccountry"]);
    //Title
    if ((br2nl_vt($val["csal"])!="")&&(br2nl_vt($val["csal"])!="--None--")) $Record['Title']=br2nl_vt($val["csal"]);

    $Record['Display']=1;
   
    // Add the record to the datebook
    $datebook->SetRecordRaw($Record);
    //$datebook->SetRecordAttrib(1);
   
    // Advance to the next record just in case we want to add more events
    if ($num_records!=0) $datebook->GoToRecord("+1");
   }

  //print_r($datebook);
    $datebook->DownloadPDB("palm_contacts.pdb");   
exit;
}


There does appear to be an upper limit on the number of lines / characters in the Notes field. I have looked in Palm's deeloper site but can not find any info.

The key is that not only the Notes can be adjunted but also records like Company. This probabaly means there is almost unlimitable download capabilities.

Watch out though - my file resulting from this code is 11MB in size and beyond this HotSuncing gets a little tedious and sometimes gets stuck.

Hope this helps.

[logz05]

Slightly amended exportPalm.php function to stop repeating and to continue if a record is blank:
Code: function export_contacts()
{

   global $adb;
   $focus = 0;
   $content = '';
   $focus = new Contact;
   $log = LoggerManager::getLogger('export_contacts_palm');
   $db = new PearDatabase();
       
  if ( isset($_REQUEST['all']) )
   {
      $where = '';
   }
       
   else
   {
      $where = $_SESSION['export_where'];
   }

   $order_by = "";
  $more_information=true;
 
  if ((isset($_REQUEST['allContacts']))&&($_REQUEST['allContacts']=="1")){
   $my_contacts=false;
  }
  else $my_contacts=true;

  $query = $focus->create_palm_export_contacts_query($my_contacts);
 
  $result = $adb->query($query,true,"Error exporting $type: "."<BR>$query");

  $datebook = new PalmAddress();
  //$categories = array(1 => 'Vtiger Contacts',2 => 'Vtiger Leads');
  //$datebook->SetCategoryList($categories);
 
  $num_records=$adb->getRowCount($result);
  while($val = $adb->fetchByAssoc($result, -1, false)){
    $num_records--;
    $id = $val["id"];
    $Record = array();

    //LastName
    if (br2nl_vt($val["clastname"])!="") $Record['LastName']=br2nl_vt($val["clastname"]);
    //FirstName
    if (br2nl_vt($val["cname"])!="") $Record['FirstName']=br2nl_vt($val["cname"]);
    //Company

$cusco="";

    $cusco.=br2nl_vt($val["ccompany"])."\nName: ";
    $cusco.=br2nl_vt($val["creferna"])." \nTel: ";
    $cusco.=br2nl_vt($val["cacphone"])." \nOther Tel: ";
    $cusco.=br2nl_vt($val["cacophon"]);
   
              if ($cusco!="") $Record["Company"]=$cusco;


// if (br2nl_vt($val["ccompany"])!="") $Record['Company']=br2nl_vt($val["ccompany"]);

     //Phone1-E-mail
    $Record['Phone1Type']=PDB_ADDR_LABEL_EMAIL;
    if (br2nl_vt($val["cemail"])!="") $Record['Phone1']=br2nl_vt($val["cemail"]);   
    //Phone2
    $Record['Phone2Type']=PDB_ADDR_LABEL_WORK;
    if (br2nl_vt($val["cphone"])!="") $Record['Phone2']=br2nl_vt($val["cphone"]);
   //Phone3
    $Record['Phone3Type']=PDB_ADDR_LABEL_MOBILE;
    if (br2nl_vt($val["cmobile"])!="") $Record['Phone3']=br2nl_vt($val["cmobile"]);
   //Phone4
    $Record['Phone4Type']=PDB_ADDR_LABEL_HOME;
    if (br2nl_vt($val["chome"])!="") $Record['Phone4']=br2nl_vt($val["chome"]);
    //Phone5
    $Record['Phone5Type']=PDB_ADDR_LABEL_FAX;
    if (br2nl_vt($val["cfax"])!="") $Record['Phone5']=br2nl_vt($val["cfax"]);

    //Custom1
    if ((br2nl_vt($val["cbirthday"])!="")&&(br2nl_vt($val["cbirthday"])!="0000-00-00")) $Record['Custom1']=br2nl_vt($val["cbirthday"]);

   //Custom2
    //$cdates = $carrival."/".$cdepart;

    if ((br2nl_vt($val["carrival"])!="")&&(br2nl_vt($val["carrival"])!="")) $Record['Custom2']=br2nl_vt($val["carrival"]);

     //Custom3
    if ((br2nl_vt($val["cdepart"])!="")&&(br2nl_vt($val["cdepart"])!="0000-00-00")) $Record['Custom3']=br2nl_vt($val["cdepart"]);

  //Custom4

$cus4="";

     $cus4.=br2nl_vt($val["cvendor"])."\nName: ";
     $cus4.=br2nl_vt($val["cvcont"]);
        if ($cus4!="") $Record["Custom4"]=$cus4;

   //Notes


$note="";
    $note.="Room No. ".br2nl_vt($val["croomno"])."\nTransp: ";
    $note.=br2nl_vt($val["ctransp"])."\nSupplier OK: ";
    $note.=br2nl_vt($val["csuppok"])."\nCons: ";
    $note.=br2nl_vt($val["cliters"])." l / min for ";
     $note.=br2nl_vt($val["chours"])." hrs / day\nAirport Service (1=Yes): ";
     $note.=br2nl_vt($val["cairport"])."\nVendor Tel (Norm & Emerg): ";
     $note.=br2nl_vt($val["cvphone"])."-";
     $note.=br2nl_vt($val["cvemerg"])."\nDir Name: ";
   $note.=br2nl_vt($val["cvdirec"])." - Tel: ";
   $note.=br2nl_vt($val["cvdirph"]);
 

   
   
           if ($note!="") $Record["Note"]=$note;




   //if ((br2nl_vt($val["ctransp"])!="")&&(br2nl_vt($val["ctransp"])!="")) $Record['Note']=br2nl_vt($val["ctransp"]);
   
                                               
    //Address
    if (br2nl_vt($val["cstreet"])!="") $Record['Address']=br2nl_vt($val["cstreet"]);
    //City
    if (br2nl_vt($val["ccity"])!="") $Record['City']=br2nl_vt($val["ccity"]);
    //State
    if (br2nl_vt($val["cstate"])!="") $Record['State']=br2nl_vt($val["cstate"]);
    //ZipCode
    if (br2nl_vt($val["ccode"])!="") $Record['ZipCode']=br2nl_vt($val["ccode"]);
    //Country
    if (br2nl_vt($val["ccountry"])!="") $Record['Country']=br2nl_vt($val["ccountry"]);
    //Title
    if ((br2nl_vt($val["csal"])!="")&&(br2nl_vt($val["csal"])!="--None--")) $Record['Title']=br2nl_vt($val["csal"]);

    $Record['Display']=1;
   
    // Add the record to the datebook
    $datebook->SetRecordRaw($Record);
    //$datebook->SetRecordAttrib(1);
   
    // Advance to the next record just in case we want to add more events
    if ($num_records!=0) $datebook->GoToRecord("+1");
   }

  //print_r($datebook);
    $datebook->DownloadPDB("palm_contacts.pdb");   
exit;
}