Hi

I got the aastra scripts to search SugarCRM and would like to have them do the same for vtiger. Here is a link to the sugar setup.

Links hidden for unregistered users. Login or register Here

I am not a php or mysql guru and need some help getting started. I cant figure out how to point things at the vtiger db instead of sugar db. I think it has to do with $database but am not sure how to trace where that is set.

Thanks,
Don

If you can organize a bounty, I would program this support. We use vtiger, so it would be nice to have for our org.

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

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.






Any help would be great.

Don

Here is the file so far. Couldnt figure out how to add it after an error to the above post.

Made a little progress on this. Thanks to Pinaki on the vtiger forum.

It now searches but does not find anything. I guess it is searching for the wrong things. Trying to get names and numbers matched up in the query now. I think I found some matches but not the right ones yet.

Thanks,
Don

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

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