Aastra XML scrips to search Vtiger Help?

Discussion in 'General' started by donhwyo, Dec 2, 2008.

  1. donhwyo

    Joined:
    Aug 8, 2008
    Messages:
    293
    Likes Received:
    0
  2. ethans

    Joined:
    Dec 2, 2008
    Messages:
    1
    Likes Received:
    0
    If you can organize a bounty, I would program this support. We use vtiger, so it would be nice to have for our org.
     
  3. donhwyo

    Joined:
    Aug 8, 2008
    Messages:
    293
    Likes Received:
    0
    I am not familiar with bounties but I think it should be fairly simple to do. It works with sugars mysql and aastra wrote a nice script. Should be able to change a few lines and variables. I haven't done much programing since the punch card days so I have a little catching up to do. Hopefully some one at aastra may jump at it. They seem to be into this and it would help them sell more phones.

    How much bounty do you think it would cost?

    Don
     
  4. donhwyo

    Joined:
    Aug 8, 2008
    Messages:
    293
    Likes Received:
    0
    Made a little progress. The db name was hard coded. That was easy but I tried to make it hard. The contacts table in sugar is like vtiger_contactdetails so made changes to reflect that. Had to change last_name to lastname and someting like that for the phone#.

    Now I am stuck at this error.

    Code:
    elastix httpd: PHP Fatal error:  Cannot use object of type DB_Error as array in /var/www/html/aastra/asterisk/testvtigercrm.php on line 109

    Code:
    function countListRecords($lookup)
    {
    Line 109 $query  = "SELECT COUNT(id) FROM vtiger_contactdetails WHERE deleted = 0 ";
    if ($lookup) $query .= "and lastname like '$lookup%' "; 
    $query = getRowSQL($query,'vtigercrm503');
    return $query['COUNT(id)'];
    }
    Any help would be great.

    Don
     
  5. donhwyo

    Joined:
    Aug 8, 2008
    Messages:
    293
    Likes Received:
    0
  6. donhwyo

    Joined:
    Aug 8, 2008
    Messages:
    293
    Likes Received:
    0
  7. aimin

    Joined:
    Aug 2, 2009
    Messages:
    2
    Likes Received:
    0
    The reason you wont get much results is that most of the contacts are stored as leads.
    You have to search both.
    I dont do php, but here's the sql I used when I wrote a .net app. It works.
    Yeah it's huge!!! really messy database schema.

    where you find '" & Trim(strPhone) & "' replace it with variable containing the phone number you are searching.

    If you want tosearch on a different variable you should be able to figure it out from there.

    strSql = "SELECT vtiger_contactdetails.firstname AS FirstName,vtiger_contactdetails.lastname AS Surname, vtiger_account.accountname AS Company, vtiger_contactdetails.phone AS Telephone, vtiger_contactdetails.mobile AS Cellphone, vtiger_contactdetails.contactid AS CRMID,vtiger_contactdetails.email as Email FROM vtiger_potential INNER JOIN vtiger_account ON (vtiger_potential.accountid = vtiger_account.accountid) INNER JOIN vtiger_contpotentialrel ON (vtiger_potential.potentialid = vtiger_contpotentialrel.potentialid) INNER JOIN vtiger_contactdetails ON (vtiger_contactdetails.accountid = vtiger_account.accountid) WHERE vtiger_contactdetails.phone = '" & Trim(strPhone) & "' OR vtiger_contactdetails.mobile = '" & Trim(strPhone) & "' OR vtiger_account.phone = '" & Trim(strPhone) & "' OR vtiger_account.otherphone = '" & Trim(strPhone) & "' UNION SELECT vtiger_leaddetails.firstname AS FirstName,vtiger_leaddetails.lastname AS Surname, vtiger_leaddetails.company AS Company, vtiger_leadaddress.phone AS Telephone,vtiger_leadaddress.mobile AS cellphone,vtiger_crmentity.crmid,vtiger_leaddetails.email AS CRMID FROM vtiger_leaddetails INNER JOIN vtiger_crmentity ON (vtiger_crmentity.crmid = vtiger_leaddetails.leadid)INNER JOIN vtiger_leadsubdetails ON (vtiger_leadsubdetails.leadsubscriptionid = vtiger_leaddetails.leadid)INNER JOIN vtiger_leadaddress ON (vtiger_leadaddress.leadaddressid = vtiger_leadsubdetails.leadsubscriptionid)INNER JOIN vtiger_leadscf ON (vtiger_leaddetails.leadid = vtiger_leadscf.leadid)LEFT OUTER JOIN vtiger_groups ON (vtiger_groups.groupid = vtiger_crmentity.smownerid)LEFT OUTER JOIN vtiger_users ON (vtiger_users.id = vtiger_crmentity.smownerid)WHERE vtiger_crmentity.deleted = 0 AND vtiger_leaddetails.converted = 0 AND (vtiger_leadaddress.phone = '" & Trim(strPhone) & "' OR vtiger_leadaddress.mobile = '" & Trim(strPhone) & "')"



    The reason I use unions is to line up results in a grid control, it should work fine though.


    I'd like to use the vtiger scripts too. Which script is it and I'll take a look at it?



    Ed

    www.voipsure.com
     
  8. donhwyo

    Joined:
    Aug 8, 2008
    Messages:
    293
    Likes Received:
    0
    Hi Aimin
    Thanks for the reply. Its been a while since I looked at this. I was kind of waiting for 5.1 that has just been released so I will be looking at again soon.
    I started with the sugar script and tried to modify it for the vtiger db. See above. The original file was /var/www/html/aastra/asterisk/sugarCRM.php . I think the key is to compare the schemas and search for the correct things. It was working at a very limited success rate. Some of that may be what you said above but also due to incoming calls using rollover # not listed in the db.

    The newer version of vtiger has some nice new features in it. One is a popup window for incoming calls. There are some growing pains with it still but I think they will get it going. The other is click to dial. That seems to work well after a couple mods to reflect elastix settings. Search vtigers forums as I can't find the links now. There site is very slow so be patient.

    Thanks,
    Don
     

Share This Page