source: trunk/zpush/backend/expresso/providers/contactProvider.php @ 7589

Revision 7589, 77.6 KB checked in by douglas, 11 years ago (diff)

Ticket #3209 - Integrar módulo de sincronização Z-push ao Expresso

Line 
1<?php
2include_once(__DIR__.'/../../../lib/default/diffbackend/diffbackend.php');
3
4
5class ExpressoContactProvider extends BackendDiff
6{
7    var $db;
8    var $_uidnumber;
9
10    /**
11     * Returns a list (array) of folders, each entry being an associative array
12     * with the same entries as StatFolder(). This method should return stable information; ie
13     * if nothing has changed, the items in the array must be exactly the same. The order of
14     * the items within the array is not important though.
15     *
16     * @access protected
17     * @return array/boolean        false if the list could not be retrieved
18     */
19    public function GetFolderList()
20    {
21        ZLog::Write(LOGLEVEL_DEBUG, "ExpressoContactProvider->GetFolderList()");
22        return array($this->StatFolder("contacts"));
23    }
24
25    /**
26     * Returns an actual SyncFolder object with all the properties set. Folders
27     * are pretty simple, having only a type, a name, a parent and a server ID.
28     *
29     * @param string        $id           id of the folder
30     *
31     * @access public
32     * @return object   SyncFolder with information
33     */
34    public function GetFolder($id)
35    {
36        ZLog::Write(LOGLEVEL_DEBUG, "ExpressoContactProvider->GetFolder()");
37        if($id == "contacts") {
38            $folder = new SyncFolder();
39            $folder->serverid = $id;
40            $folder->parentid = "0";
41            $folder->displayname = "Contatos";
42            $folder->type = SYNC_FOLDER_TYPE_CONTACT;
43
44            return $folder;
45        }
46        else return false;
47    }
48
49    /**
50     * Returns folder stats. An associative array with properties is expected.
51     *
52     * @param string        $id             id of the folder
53     *
54     * @access public
55     * @return array
56     *          Associative array(
57     *              string  "id"            The server ID that will be used to identify the folder. It must be unique, and not too long
58     *                                      How long exactly is not known, but try keeping it under 20 chars or so. It must be a string.
59     *              string  "parent"        The server ID of the parent of the folder. Same restrictions as 'id' apply.
60     *              long    "mod"           This is the modification signature. It is any arbitrary string which is constant as long as
61     *                                      the folder has not changed. In practice this means that 'mod' can be equal to the folder name
62     *                                      as this is the only thing that ever changes in folders. (the type is normally constant)
63     *          )
64     */
65    public function StatFolder($id)
66    {
67        ZLog::Write(LOGLEVEL_DEBUG, "ExpressoContactProvider->StatFolder()");
68
69        $folder = $this->GetFolder($id);
70
71        $stat = array();
72        $stat["id"] = $id;
73        $stat["parent"] = $folder->parentid;
74        $stat["mod"] = $folder->displayname;
75
76        return $stat;
77    }
78
79    /**
80     * Creates or modifies a folder
81     *
82     * @param string        $folderid       id of the parent folder
83     * @param string        $oldid          if empty -> new folder created, else folder is to be renamed
84     * @param string        $displayname    new folder name (to be created, or to be renamed to)
85     * @param int           $type           folder type
86     *
87     * @access public
88     * @return boolean                      status
89     * @throws StatusException              could throw specific SYNC_FSSTATUS_* exceptions
90     *
91     */
92    public function ChangeFolder($folderid, $oldid, $displayname, $type)
93    {
94        // TODO: Implement ChangeFolder() method.
95    }
96
97    /**
98     * Deletes a folder
99     *
100     * @param string        $id
101     * @param string        $parent         is normally false
102     *
103     * @access public
104     * @return boolean                      status - false if e.g. does not exist
105     * @throws StatusException              could throw specific SYNC_FSSTATUS_* exceptions
106     */
107    public function DeleteFolder($id, $parentid)
108    {
109        // TODO: Implement DeleteFolder() method.
110    }
111
112    /**
113     * Returns a list (array) of messages, each entry being an associative array
114     * with the same entries as StatMessage(). This method should return stable information; ie
115     * if nothing has changed, the items in the array must be exactly the same. The order of
116     * the items within the array is not important though.
117     *
118     * The $cutoffdate is a date in the past, representing the date since which items should be shown.
119     * This cutoffdate is determined by the user's setting of getting 'Last 3 days' of e-mail, etc. If
120     * the cutoffdate is ignored, the user will not be able to select their own cutoffdate, but all
121     * will work OK apart from that.
122     *
123     * @param string        $folderid       id of the parent folder
124     * @param long          $cutoffdate     timestamp in the past from which on messages should be returned
125     *
126     * @access public
127     * @return array/false                  array with messages or false if folder is not available
128     */
129    public function GetMessageList($folderid, $cutoffdate)
130    {
131        ZLog::Write(LOGLEVEL_DEBUG, "ExpressoContactProvider->GetMessageList()");
132
133        $messages = array();
134        $ids = array();
135        try {
136            $result = pg_query($this->db,"BEGIN;");
137            if ($result == FALSE) throw new Exception(pg_last_error($this->db));
138            $result = pg_query($this->db, "select given_names, family_names, last_update, id_contact from phpgw_cc_contact where id_owner = " . $this->_uidnumber . ";");
139            if ($result == FALSE) throw new Exception(pg_last_error($this->db));
140            while ($row = pg_fetch_row($result)) {
141                $message = array();
142                $message["id"] = $row[3];
143                $message["mod"] = substr($row[2], 0, strlen($row[2])-3);
144                $message["flags"] = 1; // always 'read'
145                $messages[] = $message;
146            }
147            $result = pg_query($this->db,"COMMIT;");
148            if ($result == FALSE) throw new Exception(pg_last_error($this->db));
149        } catch (Exception $e) {
150            pg_query($this->db,"ROLLBACK;");
151       //     debugLog("exception -> " . $e->getMessage() . " - ARQUIVO: " . $e->getFile() . " - LINHA: " . $e->getLine());
152        }
153
154
155        return $messages;
156    }
157
158    /**
159     * Returns the actual SyncXXX object type. The '$folderid' of parent folder can be used.
160     * Mixing item types returned is illegal and will be blocked by the engine; ie returning an Email object in a
161     * Tasks folder will not do anything. The SyncXXX objects should be filled with as much information as possible,
162     * but at least the subject, body, to, from, etc.
163     *
164     * @param string            $folderid           id of the parent folder
165     * @param string            $id                 id of the message
166     * @param ContentParameters $contentparameters  parameters of the requested message (truncation, mimesupport etc)
167     *
168     * @access public
169     * @return object/false                 false if the message could not be retrieved
170     */
171    public function GetMessage($folderid, $id, $contentparameters)
172    {
173        ZLog::Write(LOGLEVEL_DEBUG, "ExpressoContactProvider->GetMessage()");
174
175        // Parse the database into object
176        $message = new SyncContact();
177        try {
178            $result = pg_query($this->db,"BEGIN;");
179            if ($result == FALSE) throw new Exception(pg_last_error($this->db));
180            $result_contact = pg_query($this->db, "select id_contact, id_owner, id_status, photo, alias, id_prefix, given_names, family_names, names_ordered, id_suffix, birthdate, sex, pgp_key, notes, is_global, last_status, last_update, category, web_page, corporate_name, job_title, department from phpgw_cc_contact where id_contact = " . $id . ";");
181            if ($result_contact == FALSE) throw new Exception(pg_last_error($this->db));
182            while ($row_contact = pg_fetch_row($result_contact)) {
183                //if(isset($row_contact[3])) {
184                //      $message->picture = base64_encode($row_contact[3]);
185                //}
186                if(isset($row_contact[4])) {
187                    $message->nickname = utf8_encode($row_contact[4]);
188                }
189                if(isset($row_contact[7])) {
190                    $arrayFamilyName = explode(' ',trim($row_contact[7]));
191                    if (sizeof($arrayFamilyName) > 1) {
192                        $message->lastname = utf8_encode($arrayFamilyName[sizeof($arrayFamilyName) - 1]);
193                        for ($i = 0; $i < (sizeof($arrayFamilyName) - 1); $i++) {
194                            $message->middlename .= " " . utf8_encode($arrayFamilyName[$i]);
195                        }
196                        $message->middlename = trim($message->middlename);
197                    } else {
198                        $message->lastname = utf8_encode(trim($row_contact[7]));
199                    }
200                    $message->fileas = $message->lastname;
201                }
202                if(isset($row_contact[6])) {
203                    $message->firstname = utf8_encode(trim($row_contact[6]));
204                    if(isset($row_contact[7])) $message->fileas .= ', ' . $message->firstname;
205                    else $message->fileas = $message->firstname;
206                }
207                if(isset($row_contact[10])) {
208                    $dataParte = explode('-',$row_contact[10]);
209                    $data = $dataParte[2] . '-' . $dataParte[1] . '-' . $dataParte[0];
210                    $tz = date_default_timezone_get();
211                    date_default_timezone_set('UTC');
212                    $message->birthday = strtotime($data);
213                    $message->birthday += 3600 * 10; // Soma 10 horas para nao alterar a data quando mudar o Timezone
214                    date_default_timezone_set($tz);
215                }
216                // TODO:Incluir campo de Aniversario na sincronizacao. O BD do Expresso ainda nao tem esse campo :-(
217                if(isset($row_contact[13])) {
218                    $message->body = utf8_encode(str_replace('\\n', chr(13) . chr(10), $this->escape($row_contact[13])));
219                    $message->bodysize = strlen($message->body);
220                    $message->bodytruncated = 0;
221                }
222                //TODO:Tratar o conteudo do campo de categorias
223                //if(isset($row_contact[17])) {
224                //      $message->categories = utf8_encode($row_contact[17]);
225                //}
226                if(isset($row_contact[18])) {
227                    $message->webpage = utf8_encode($row_contact[18]);
228                }
229                if(isset($row_contact[19])) {
230                    $message->companyname = utf8_encode($row_contact[19]);
231                    if (!isset($row_contact[6]) and !isset($row_contact[7])) $message->fileas = $message->companyname;
232                }
233                if(isset($row_contact[20])) {
234                    $message->jobtitle = utf8_encode($row_contact[20]);
235                }
236                if(isset($row_contact[21])) {
237                    $message->department = utf8_encode($row_contact[21]);
238                }
239
240                // Endereço Comercial
241                $result_addresses_comercial = pg_query($this->db,"select co.id_address, co.id_city, city_name, co.id_state, state_symbol, co.id_country, address1, address2, complement, address_other, postal_code, po_box, address_is_default from phpgw_cc_addresses co join phpgw_cc_contact_addrs ca on (co.id_address = ca.id_address) join phpgw_cc_typeof_ct_addrs tca on (ca.id_typeof_contact_address = tca.id_typeof_contact_address) left outer join phpgw_cc_city ci on (ci.id_city = co.id_city) left outer join phpgw_cc_state cs on (cs.id_state = co.id_state) where tca.contact_address_type_name = 'Comercial' and ca.id_contact = " . $row_contact[0] . ";");
242                if ($result_addresses_comercial == FALSE) throw new Exception(pg_last_error($this->db));
243                while ($row_addresses_comercial = pg_fetch_row($result_addresses_comercial)) {
244                    if (isset($row_addresses_comercial[2])) {
245                        $message->businesscity = utf8_encode($row_addresses_comercial[2]);
246                    }
247                    if (isset($row_addresses_comercial[5])) {
248                        $message->businesscountry = utf8_encode($row_addresses_comercial[5]);
249                    }
250                    if (isset($row_addresses_comercial[10])) {
251                        $message->businesspostalcode = utf8_encode($row_addresses_comercial[10]);
252                    }
253                    if (isset($row_addresses_comercial[4])) {
254                        $message->businessstate = utf8_encode($row_addresses_comercial[4]);
255                    }
256                    if (isset($row_addresses_comercial[6])) {
257                        $message->businessstreet = utf8_encode($row_addresses_comercial[6]);
258                    }
259                    if (isset($row_addresses_comercial[8])) {
260                        if (isset($message->businessstreet)) {
261                            $message->businessstreet .= ":";
262                        }
263                        $message->businessstreet .= utf8_encode($row_addresses_comercial[8]);
264                    }
265                    if (isset($row_addresses_comercial[7])) {
266                        if (isset($message->businessstreet)) {
267                            $message->businessstreet .= ":";
268                        }
269                        $message->businessstreet .= utf8_encode($row_addresses_comercial[7]);
270                    }
271                    if (isset($row_addresses_comercial[9])) {
272                        if (isset($message->businessstreet)) {
273                            $message->businessstreet .= ":";
274                        }
275                        $message->businessstreet .= utf8_encode($row_addresses_comercial[9]);
276                    }
277                    if (isset($row_addresses_comercial[11])) {
278                        if (isset($message->businessstreet)) {
279                            $message->businessstreet .= ":";
280                        }
281                        $message->businessstreet .= utf8_encode($row_addresses_comercial[11]);
282                    }
283                }
284                // Endereço Residencial
285                $result_addresses_residencial = pg_query($this->db,"select co.id_address, co.id_city, city_name, co.id_state, state_name, co.id_country, address1, address2, complement, address_other, postal_code, po_box, address_is_default from phpgw_cc_addresses co join phpgw_cc_contact_addrs ca on (co.id_address = ca.id_address) join phpgw_cc_typeof_ct_addrs tca on (ca.id_typeof_contact_address = tca.id_typeof_contact_address) left outer join phpgw_cc_city ci on (ci.id_city = co.id_city) left outer join phpgw_cc_state cs on (cs.id_state = co.id_state) where tca.contact_address_type_name = 'Residencial' and ca.id_contact = " . $row_contact[0] . ";");
286                if ($result_addresses_residencial == FALSE) throw new Exception(pg_last_error($this->db));
287                while ($row_addresses_residencial = pg_fetch_row($result_addresses_residencial)) {
288                    if (isset($row_addresses_residencial[2])) {
289                        $message->homecity = utf8_encode($row_addresses_residencial[2]);
290                    }
291                    if (isset($row_addresses_residencial[5])) {
292                        $message->homecountry = utf8_encode($row_addresses_residencial[5]);
293                    }
294                    if (isset($row_addresses_residencial[10])) {
295                        $message->homepostalcode = utf8_encode($row_addresses_residencial[10]);
296                    }
297                    if (isset($row_addresses_residencial[4])) {
298                        $message->homestate = utf8_encode($row_addresses_residencial[4]);
299                    }
300                    if (isset($row_addresses_residencial[6])) {
301                        $message->homestreet = utf8_encode($row_addresses_residencial[6]);
302                    }
303                    if (isset($row_addresses_residencial[8])) {
304                        if (isset($message->homestreet)) {
305                            $message->homestreet .= ":";
306                        }
307                        $message->homestreet .= utf8_encode($row_addresses_residencial[8]);
308                    }
309                    if (isset($row_addresses_residencial[7])) {
310                        if (isset($message->homestreet)) {
311                            $message->homestreet .= ":";
312                        }
313                        $message->homestreet .= utf8_encode($row_addresses_residencial[7]);
314                    }
315                    if (isset($row_addresses_residencial[9])) {
316                        if (isset($message->homestreet)) {
317                            $message->homestreet .= ":";
318                        }
319                        $message->homestreet .= utf8_encode($row_addresses_residencial[9]);
320                    }
321                    if (isset($row_addresses_residencial[11])) {
322                        if (isset($message->homestreet)) {
323                            $message->homestreet .= ":";
324                        }
325                        $message->homestreet .= utf8_encode($row_addresses_residencial[11]);
326                    }
327                }
328                // Emails
329                $result_emails = pg_query($this->db,"select cn.id_connection, connection_name, connection_value, connection_is_default from phpgw_cc_connections cn join phpgw_cc_contact_conns cc on (cn.id_connection = cc.id_connection) join phpgw_cc_typeof_ct_conns ct on (ct.id_typeof_contact_connection = cc.id_typeof_contact_connection) where ct.contact_connection_type_name = 'Email' and cc.id_contact = "  . $row_contact[0] . ";");
330                if ($result_emails == FALSE) throw new Exception(pg_last_error($this->db));
331                while ($row_emails = pg_fetch_row($result_emails)) {
332                    if ($row_emails[1] == "Principal") {
333                        $message->email1address = utf8_encode($row_emails[2]);
334                    }
335                    if ($row_emails[1] == "Alternativo") {
336                        $message->email2address = utf8_encode($row_emails[2]);
337                    }
338                    //TODO : Atribuir o email3address. O Expresso ainda não tem campo para um terceiro email :(
339                }
340                // Telefones
341                $result_tel = pg_query($this->db,"select cn.id_connection, connection_name, connection_value, connection_is_default from phpgw_cc_connections cn join phpgw_cc_contact_conns cc on (cn.id_connection = cc.id_connection) join phpgw_cc_typeof_ct_conns ct on (ct.id_typeof_contact_connection = cc.id_typeof_contact_connection) where ct.contact_connection_type_name = 'Telefone' and cc.id_contact = "  . $row_contact[0] . ";");
342                if ($result_tel == FALSE) throw new Exception(pg_last_error($this->db));
343                while ($row_tel = pg_fetch_row($result_tel)) {
344                    if ($row_tel[1] == "Trabalho") {
345                        $message->businessphonenumber = utf8_encode($row_tel[2]);
346                    }
347                    if ($row_tel[1] == "Casa") {
348                        $message->homephonenumber = utf8_encode($row_tel[2]);
349                    }
350                    if ($row_tel[1] == "Celular") {
351                        $message->mobilephonenumber = utf8_encode($row_tel[2]);
352                    }
353                    if ($row_tel[1] == "Fax") {
354                        $message->businessfaxnumber = utf8_encode($row_tel[2]);
355                    }
356                    if ($row_tel[1] == "Principal") {
357                        $message->business2phonenumber = utf8_encode($row_tel[2]);
358                    }
359                    if ($row_tel[1] == "Alternativo") {
360                        $message->home2phonenumber = utf8_encode($row_tel[2]);
361                    }
362                    //TODO : Permitir mais de um número de telefone para Trabalho, Casa e Celular. O Expresso ainda não suporta isso :(
363                }
364            }
365            $result = pg_query($this->db,"COMMIT;");
366            if ($result == FALSE) throw new Exception(pg_last_error($this->db));
367        } catch (Exception $e) {
368            pg_query($this->db,"ROLLBACK;");
369            debugLog("exception -> " . $e->getMessage() . " - ARQUIVO: " . $e->getFile() . " - LINHA: " . $e->getLine());
370            return;
371        }
372        return $message;
373    }
374
375    /**
376     * Returns message stats, analogous to the folder stats from StatFolder().
377     *
378     * @param string        $folderid       id of the folder
379     * @param string        $id             id of the message
380     *
381     * @access public
382     * @return array or boolean if fails
383     *          Associative array(
384     *              string  "id"            Server unique identifier for the message. Again, try to keep this short (under 20 chars)
385     *              int     "flags"         simply '0' for unread, '1' for read
386     *              long    "mod"           This is the modification signature. It is any arbitrary string which is constant as long as
387     *                                      the message has not changed. As soon as this signature changes, the item is assumed to be completely
388     *                                      changed, and will be sent to the PDA as a whole. Normally you can use something like the modification
389     *                                      time for this field, which will change as soon as the contents have changed.
390     *          )
391     */
392    public function StatMessage($folderid, $id)
393    {
394        ZLog::Write(LOGLEVEL_DEBUG, "ExpressoContactProvider->StatMessage()");
395
396        try {
397            $result = pg_query($this->db,"BEGIN;");
398            if ($result == FALSE) throw new Exception(pg_last_error($this->db));
399            $result_contact = pg_query($this->db, "select last_update from phpgw_cc_contact where id_contact = " . $id . ";");
400            if ($result_contact == FALSE) throw new Exception(pg_last_error($this->db));
401            while ($row_contact = pg_fetch_row($result_contact)) {
402                if(isset($row_contact[0])) {
403                    $message = array();
404                    $message["mod"] = substr($row_contact[0], 0, strlen($row_contact[0])-3);
405                    $message["id"] = $id;
406                    $message["flags"] = 1;
407                    return $message;
408                }
409            }
410            $result = pg_query($this->db,"COMMIT;");
411            if ($result == FALSE) throw new Exception(pg_last_error($this->db));
412        } catch (Exception $e) {
413            pg_query($this->db,"ROLLBACK;");
414            debugLog("exception -> " . $e->getMessage() . " - ARQUIVO: " . $e->getFile() . " - LINHA: " . $e->getLine());
415        }
416        return false;
417    }
418
419    /**
420     * Called when a message has been changed on the mobile. The new message must be saved to disk.
421     * The return value must be whatever would be returned from StatMessage() after the message has been saved.
422     * This way, the 'flags' and the 'mod' properties of the StatMessage() item may change via ChangeMessage().
423     * This method will never be called on E-mail items as it's not 'possible' to change e-mail items. It's only
424     * possible to set them as 'read' or 'unread'.
425     *
426     * @param string        $folderid       id of the folder
427     * @param string        $id             id of the message
428     * @param SyncXXX       $message        the SyncObject containing a message
429     *
430     * @access public
431     * @return array                        same return value as StatMessage()
432     * @throws StatusException              could throw specific SYNC_STATUS_* exceptions
433     */
434    public function ChangeMessage($folderid, $id, $message)
435    {
436        ZLog::Write(LOGLEVEL_DEBUG, "ExpressoContactProvider->ChangeMessage()");
437
438        try {
439            $result = pg_query($this->db,"BEGIN;");
440            if ($result == FALSE) throw new Exception(pg_last_error($this->db));
441
442            // Obtem o id_contact
443            $found_id_contact = false;
444            if (trim($id) !== "") {
445                $result_contact = pg_query($this->db, "select id_contact from phpgw_cc_contact where id_contact = " . $id . ";");
446                if ($result_contact == FALSE) throw new Exception(pg_last_error($this->db));
447                // tenta localizar id_contact para fazer Update
448                while ($row_contact = pg_fetch_row($result_contact)) {
449                    if(isset($row_contact[0])) {
450                        $id_contact = $row_contact[0];
451                        $found_id_contact = true;
452                    }
453                }
454            }
455            // se não encontrou id_contact para fazer Update, define o próximo id_contact para fazer Insert
456            if (!isset($id_contact)) {
457                $result = pg_query($this->db,"LOCK TABLE phpgw_cc_contact IN ACCESS EXCLUSIVE MODE;");
458                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
459                $result_contact_max_id = pg_query($this->db, "select max(id_contact) from phpgw_cc_contact;");
460                if ($result_contact_max_id == FALSE) throw new Exception(pg_last_error($this->db));
461                $row_contact_max_id = pg_fetch_row($result_contact_max_id);
462                if (isset($row_contact_max_id[0])) {
463                    $id_contact = $row_contact_max_id[0] + 1;
464                } else $id_contact = 1;
465            }
466
467            // Procura o id_address comercial e residencial
468            $result_address = pg_query($this->db, "select co.id_address, tca.contact_address_type_name from phpgw_cc_addresses co join phpgw_cc_contact_addrs ca on (co.id_address = ca.id_address) join phpgw_cc_typeof_ct_addrs tca on (ca.id_typeof_contact_address = tca.id_typeof_contact_address) where ca.id_contact = " . $id_contact . ";");
469            if ($result_address == FALSE) throw new Exception(pg_last_error($this->db));
470            $found_id_address_comercial = false;
471            $found_id_address_residencial = false;
472            while ($row_address = pg_fetch_row($result_address)) {
473                if(isset($row_address[0])) {
474                    if ($row_address[1] == "Comercial") {
475                        $id_address_comercial = $row_address[0];
476                        $found_id_address_comercial = true;
477                    }
478                    if ($row_address[1] == "Residencial") {
479                        $id_address_residencial = $row_address[0];
480                        $found_id_address_residencial = true;
481                    }
482                }
483            }
484            // Verifica se os campos de endereco estao preenchidos na mensagem recebida do dispositivo movel
485            $isset_business_address_fields = false;
486            if(isset($message->businessstate) or isset($message->businesspostalcode) or isset($message->businessstreet)) {
487                $isset_business_address_fields = true;
488            }
489            $isset_home_address_fields = false;
490            if(isset($message->homestate) or isset($message->homepostalcode) or isset($message->homestreet)) {
491                $isset_home_address_fields = true;
492            }
493            // Obtem o ultimo id_address
494            if (!($found_id_address_comercial and $found_id_address_residencial) and ($isset_business_address_fields or $isset_home_address_fields)) {
495                $result = pg_query($this->db,"LOCK TABLE phpgw_cc_addresses IN ACCESS EXCLUSIVE MODE;");
496                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
497                $result_address_max_id = pg_query($this->db, "select max(id_address) from phpgw_cc_addresses;");
498                if ($result_address_max_id == FALSE) throw new Exception(pg_last_error($this->db));
499                $array_row_address_max_id = pg_fetch_row($result_address_max_id);
500                if (isset($array_row_address_max_id)) $row_address_max_id = $array_row_address_max_id[0];
501                if (!isset($row_address_max_id)) $row_address_max_id = 0;
502            }
503            $next_offset_address_to_insert = 0;
504            // se não encontrou id_address_comercial para fazer Update, define o próximo id_address_comercial para fazer Insert
505            if (!$found_id_address_comercial and $isset_business_address_fields) {
506                $next_offset_address_to_insert += 1;
507                $id_address_comercial = $row_address_max_id + $next_offset_address_to_insert;
508            }
509            // se não encontrou id_address_residencial para fazer Update, define o próximo id_address_residencial para fazer Insert
510            if (!$found_id_address_residencial and $isset_home_address_fields) {
511                $next_offset_address_to_insert += 1;
512                $id_address_residencial = $row_address_max_id + $next_offset_address_to_insert;
513            }
514
515            // Procura o id_connection de Emails e Telefones
516            $result_connection = pg_query($this->db, "select cn.id_connection, connection_name, connection_is_default, cc.id_typeof_contact_connection from phpgw_cc_connections cn join phpgw_cc_contact_conns cc on (cn.id_connection = cc.id_connection) join phpgw_cc_typeof_ct_conns ct on (ct.id_typeof_contact_connection = cc.id_typeof_contact_connection) where cc.id_contact = " . $id_contact . ";");
517            if ($result_connection == FALSE) throw new Exception(pg_last_error($this->db));
518            $found_id_connection_email_principal = false;
519            $found_id_connection_email_alternativo = false;
520            $found_id_connection_tel_trabalho = false;
521            $found_id_connection_tel_casa = false;
522            $found_id_connection_tel_celular = false;
523            $found_id_connection_tel_fax = false;
524            $found_id_connection_tel_principal = false;
525            $found_id_connection_tel_alternativo = false;
526            $tel_default = "";
527            $email_default = "";
528            while ($row_connection = pg_fetch_row($result_connection)) {
529                if(isset($row_connection[0])) {
530                    if ($row_connection[1] == "Principal" and $row_connection[3] == 1) {
531                        $id_connection_email_principal = $row_connection[0];
532                        $found_id_connection_email_principal = true;
533                        if ($row_connection[2] == 't') $email_default = "Principal";
534                    }
535                    if ($row_connection[1] == "Alternativo" and $row_connection[3] == 1) {
536                        $id_connection_email_alternativo = $row_connection[0];
537                        $found_id_connection_email_alternativo = true;
538                        if ($row_connection[2] == 't') $email_default = "Alternativo";
539                    }
540                    if ($row_connection[1] == "Trabalho") {
541                        $id_connection_tel_trabalho = $row_connection[0];
542                        $found_id_connection_tel_trabalho = true;
543                        if ($row_connection[2] == 't') $tel_default = "Trabalho";
544                    }
545                    if ($row_connection[1] == "Casa") {
546                        $id_connection_tel_casa = $row_connection[0];
547                        $found_id_connection_tel_casa = true;
548                        if ($row_connection[2] == 't') $tel_default = "Casa";
549                    }
550                    if ($row_connection[1] == "Celular") {
551                        $id_connection_tel_celular = $row_connection[0];
552                        $found_id_connection_tel_celular = true;
553                        if ($row_connection[2] == 't') $tel_default = "Celular";
554                    }
555                    if ($row_connection[1] == "Fax") {
556                        $id_connection_tel_fax = $row_connection[0];
557                        $found_id_connection_tel_fax = true;
558                        if ($row_connection[2] == 't') $tel_default = "Fax";
559                    }
560                    if ($row_connection[1] == "Principal" and $row_connection[3] == 2) {
561                        $id_connection_tel_principal = $row_connection[0];
562                        $found_id_connection_tel_principal = true;
563                        if ($row_connection[2] == 't') $tel_default = "Principal";
564                    }
565                    if ($row_connection[1] == "Alternativo" and $row_connection[3] == 2) {
566                        $id_connection_tel_alternativo = $row_connection[0];
567                        $found_id_connection_tel_alternativo = true;
568                        if ($row_connection[2] == 't') $tel_default = "Alternativo";
569                    }
570                }
571            }
572            // Obtem o ultimo id_connection
573            if (!($found_id_connection_email_principal and $found_id_connection_email_alternativo and $found_id_connection_tel_trabalho and $found_id_connection_tel_celular and $found_id_connection_tel_casa and $found_id_connection_tel_fax and $found_id_connection_tel_principal and $found_id_connection_tel_alternativo) and (isset($message->email1address) or isset($message->email2address) or isset($message->businessphonenumber) or isset($message->homephonenumber) or isset($message->mobilephonenumber) or isset($message->businessfaxnumber) or isset($message->business2phonenumber) or isset($message->home2phonenumber))){
574                $result = pg_query($this->db,"LOCK TABLE phpgw_cc_connections IN ACCESS EXCLUSIVE MODE;");
575                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
576                $result_connection_max_id = pg_query($this->db, "select max(id_connection) from phpgw_cc_connections;");
577                if ($result_connection_max_id == FALSE) throw new Exception(pg_last_error($this->db));
578                $array_row_connection_max_id = pg_fetch_row($result_connection_max_id);
579                if (isset($array_row_connection_max_id)) $row_connection_max_id = $array_row_connection_max_id[0];
580                if (!isset($row_connection_max_id)) $row_connection_max_id = 0;
581            }
582            $next_offset_connection_to_insert = 0;
583            // se não encontrou id_connection_email_principal para fazer Update, define o próximo id_connection_email_principal para fazer Insert
584            if (!$found_id_connection_email_principal and isset($message->email1address)) {
585                $next_offset_connection_to_insert += 1;
586                $id_connection_email_principal = $row_connection_max_id + $next_offset_connection_to_insert;
587            }
588            // se não encontrou id_connection_email_alternativo para fazer Update, define o próximo id_connection_email_alternativo para fazer Insert
589            if (!$found_id_connection_email_alternativo and isset($message->email2address)) {
590                $next_offset_connection_to_insert += 1;
591                $id_connection_email_alternativo = $row_connection_max_id + $next_offset_connection_to_insert;
592            }
593            // se não encontrou $id_connection_tel_trabalho para fazer Update, define o próximo $id_connection_tel_trabalho para fazer Insert
594            if (!$found_id_connection_tel_trabalho and isset($message->businessphonenumber)) {
595                $next_offset_connection_to_insert += 1;
596                $id_connection_tel_trabalho = $row_connection_max_id + $next_offset_connection_to_insert;
597            }
598            // se não encontrou $id_connection_tel_casa para fazer Update, define o próximo $id_connection_tel_casa para fazer Insert
599            if (!$found_id_connection_tel_casa and isset($message->homephonenumber)) {
600                $next_offset_connection_to_insert += 1;
601                $id_connection_tel_casa = $row_connection_max_id + $next_offset_connection_to_insert;
602            }
603            // se não encontrou $id_connection_tel_celular para fazer Update, define o próximo $id_connection_tel_celular para fazer Insert
604            if (!$found_id_connection_tel_celular and isset($message->mobilephonenumber)) {
605                $next_offset_connection_to_insert += 1;
606                $id_connection_tel_celular = $row_connection_max_id + $next_offset_connection_to_insert;
607            }
608            // se não encontrou $id_connection_tel_fax para fazer Update, define o próximo $id_connection_tel_fax para fazer Insert
609            if (!$found_id_connection_tel_fax and isset($message->businessfaxnumber)) {
610                $next_offset_connection_to_insert += 1;
611                $id_connection_tel_fax = $row_connection_max_id + $next_offset_connection_to_insert;
612            }
613            // se não encontrou $id_connection_tel_principal para fazer Update, define o próximo $id_connection_tel_principal para fazer Insert
614            if (!$found_id_connection_tel_principal and isset($message->business2phonenumber)) {
615                $next_offset_connection_to_insert += 1;
616                $id_connection_tel_principal = $row_connection_max_id + $next_offset_connection_to_insert;
617            }
618            // se não encontrou $id_connection_tel_alternativo para fazer Update, define o próximo $id_connection_tel_alternativo para fazer Insert
619            if (!$found_id_connection_tel_alternativo and isset($message->home2phonenumber)) {
620                $next_offset_connection_to_insert += 1;
621                $id_connection_tel_alternativo = $row_connection_max_id + $next_offset_connection_to_insert;
622            }
623
624            // Incluir/Alterar registro na tabela phpgw_cc_contact no Banco de Dados
625            //if(isset($message->picture)) {
626            //  $arrayContact["photo"] = base64_decode($message->picture);
627            //}
628            if(isset($message->nickname)) {
629                $arrayContact["alias"] = utf8_decode($message->nickname);
630            }
631            if(isset($message->firstname)) {
632                $arrayContact["given_names"] = utf8_decode($message->firstname);
633                $arrayContact["names_ordered"] = utf8_decode($message->firstname);
634            }
635            if (isset($message->middlename)) {
636                $arrayContact["family_names"] = utf8_decode($message->middlename);
637                if(isset($message->firstname)) {
638                    $arrayContact["names_ordered"] .= " ";
639                }
640                $arrayContact["names_ordered"] .= utf8_decode($message->middlename);
641            }
642            if(isset($message->lastname)) {
643                if(isset($message->middlename)) {
644                    $arrayContact["family_names"] .= " ";
645                }
646                if (isset($arrayContact["family_names"])) $arrayContact["family_names"] .= utf8_decode($message->lastname);
647                else $arrayContact["family_names"] = utf8_decode($message->lastname);
648                if(isset($message->firstname) or isset($message->middlename)) {
649                    $arrayContact["names_ordered"] .= " ";
650                }
651                $arrayContact["names_ordered"] .= utf8_decode($message->lastname);
652            }
653            if (isset($message->birthday)) {
654                $tz = date_default_timezone_get();
655                date_default_timezone_set('UTC');
656                $arrayContact["birthdate"] = date("Y-m-d",$message->birthday);
657                date_default_timezone_set($tz);
658            }
659            //TODO: Incluir o campo de Aniversario na Sincronizacao. O DB do Expresso nao tem esse campo :-(
660
661            if(isset($message->rtf)) {
662                $rtf_to_ascii = new rtf();
663                $rtf_to_ascii->output("ascii");
664                $result_loadrtf = $rtf_to_ascii->loadrtf(base64_decode($message->rtf));
665                if ($result_loadrtf == true) $rtf_to_ascii->parse();
666                $arrayContact["notes"] = $rtf_to_ascii->out;
667            }
668            //TODO: Tratar o conteudo do campo de categorias
669            //if(isset($message->categories)) {
670            //  $arrayContact["category"] = $this->truncateString(utf8_decode($message->categories),20);
671            //}
672            if(isset($message->webpage)) {
673                $arrayContact["web_page"] = $this->truncateString(utf8_decode($message->webpage),100);
674            }
675            if(isset($message->companyname)) {
676                $arrayContact["corporate_name"] = $this->truncateString(utf8_decode($message->companyname),100);
677            }
678            if(isset($message->jobtitle)) {
679                $arrayContact["job_title"] = $this->truncateString(utf8_decode($message->jobtitle),40);
680            }
681            if(isset($message->department)) {
682                $arrayContact["department"] = $this->truncateString(utf8_decode($message->department),30);
683            }
684            if (!$found_id_contact){
685                $arrayContact["id_contact"] = $id_contact;
686                $arrayContact["id_owner"] = $this->_uidnumber;
687                $result = pg_insert($this->db, 'phpgw_cc_contact', $arrayContact);
688                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
689            } else {
690                $result = pg_update($this->db, 'phpgw_cc_contact', $arrayContact, array('id_contact' => $id_contact));
691                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
692            }
693
694            // Incluir/Alterar Endereco Comercial na tabela phpgw_cc_addresses no Banco de Dados
695            if(isset($message->businessstate)) {
696                $result = pg_query($this->db, "select id_state, id_country from phpgw_cc_state where LOWER(to_ASCII(state_name)) = '" . trim(strtolower($this->removeAccents(utf8_decode($message->businessstate)))) . "' or LOWER(to_ASCII(state_symbol)) = '" . trim(strtolower($this->removeAccents(utf8_decode($message->businessstate)))) . "';");
697                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
698                $row = pg_fetch_row($result);
699                if(isset($row[0])) {
700                    $arrayAddressComercial["id_state"] = $row[0];
701                }
702                if(isset($row[1])) {
703                    $arrayAddressComercial["id_country"] = $row[1];
704                }
705                if(isset($message->businesscity) and isset($arrayAddressComercial["id_state"])) {
706                    $result = pg_query($this->db, "select id_city from phpgw_cc_city where id_state = " . $arrayAddressComercial["id_state"] . " and LOWER(to_ASCII(city_name)) = '" . trim(strtolower($this->removeAccents(utf8_decode($message->businesscity)))) . "';");
707                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
708                    $row = pg_fetch_row($result);
709                    if(isset($row[0])) {
710                        $arrayAddressComercial["id_city"] = $row[0];
711                    }
712                }
713            }
714            if(isset($message->businesspostalcode)) {
715                $arrayAddressComercial["postal_code"] = $this->truncateString(utf8_decode($message->businesspostalcode),15);
716            }
717            if(isset($message->businessstreet)) {
718                $arrayAddressComercial["address1"] = $this->truncateString(utf8_decode($message->businessstreet),60);
719            }
720            if($isset_business_address_fields) {
721                if (!$found_id_address_comercial) {
722                    $arrayAddressComercial["id_address"] = $id_address_comercial;
723                    $result = pg_insert($this->db, 'phpgw_cc_addresses', $arrayAddressComercial);
724                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
725                    $arrayContactAddressComercial["id_contact"] = $id_contact;
726                    $arrayContactAddressComercial["id_address"] = $id_address_comercial;
727                    $arrayContactAddressComercial["id_typeof_contact_address"] = 1; //comercial
728                    $result = pg_insert($this->db, 'phpgw_cc_contact_addrs', $arrayContactAddressComercial);
729                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
730                } else {
731                    $result = pg_update($this->db, 'phpgw_cc_addresses', $arrayAddressComercial, array('id_address' => $id_address_comercial));
732                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
733                }
734            } elseif ($found_id_address_comercial) {
735                $result = pg_delete($this->db, "phpgw_cc_contact_addrs", array('id_contact' => $id_contact, 'id_address' => $id_address_comercial));
736                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
737                $result = pg_delete($this->db, "phpgw_cc_addresses", array('id_address' => $id_address_comercial));
738                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
739            }
740
741            // Incluir/Alterar Endereco Residencial na tabela phpgw_cc_addresses no Banco de Dados
742            if(isset($message->homestate)) {
743                $result = pg_query($this->db, "select id_state, id_country from phpgw_cc_state where LOWER(to_ASCII(state_name)) = '" . trim(strtolower($this->removeAccents(utf8_decode($message->homestate)))) . "' or LOWER(to_ASCII(state_symbol)) = '" . trim(strtolower($this->removeAccents(utf8_decode($message->homestate)))) . "';");
744                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
745                $row = pg_fetch_row($result);
746                if(isset($row[0])) {
747                    $arrayAddressResidencial["id_state"] = $row[0];
748                }
749                if(isset($row[1])) {
750                    $arrayAddressResidencial["id_country"] = $row[1];
751                }
752                if(isset($message->homecity) and isset($arrayAddressResidencial["id_state"])) {
753                    $result = pg_query($this->db, "select id_city from phpgw_cc_city where id_state = " . $arrayAddressResidencial["id_state"] . " and LOWER(to_ASCII(city_name)) = '" . trim(strtolower($this->removeAccents(utf8_decode($message->homecity)))) . "';");
754                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
755                    $row = pg_fetch_row($result);
756                    if(isset($row[0])) {
757                        $arrayAddressResidencial["id_city"] = $row[0];
758                    }
759                }
760            }
761            if(isset($message->homepostalcode)) {
762                $arrayAddressResidencial["postal_code"] = $this->truncateString(utf8_decode($message->homepostalcode),15);
763            }
764            if(isset($message->homestreet)) {
765                $arrayAddressResidencial["address1"] = $this->truncateString(utf8_decode($message->homestreet),60);
766            }
767            if($isset_home_address_fields) {
768                if (!$found_id_address_residencial) {
769                    $arrayAddressResidencial["id_address"] = $id_address_residencial;
770                    $result = pg_insert($this->db, 'phpgw_cc_addresses', $arrayAddressResidencial);
771                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
772                    $arrayContactAddressResidencial["id_contact"] = $id_contact;
773                    $arrayContactAddressResidencial["id_address"] = $id_address_residencial;
774                    $arrayContactAddressResidencial["id_typeof_contact_address"] = 2; //residencial
775                    $result = pg_insert($this->db, 'phpgw_cc_contact_addrs', $arrayContactAddressResidencial);
776                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
777                } else {
778                    $result = pg_update($this->db, 'phpgw_cc_addresses', $arrayAddressResidencial, array('id_address' => $id_address_residencial));
779                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
780                }
781            } elseif ($found_id_address_residencial) {
782                $result = pg_delete($this->db, "phpgw_cc_contact_addrs", array('id_contact' => $id_contact, 'id_address' => $id_address_residencial));
783                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
784                $result = pg_delete($this->db, "phpgw_cc_addresses", array('id_address' => $id_address_residencial));
785                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
786            }
787
788            // Email Principal
789            if(isset($message->email1address)) {
790                $arrayConnectionEmailPrincipal["connection_value"] = $this->truncateString($this->formatMail(utf8_decode($message->email1address)),100);
791                if (!$found_id_connection_email_principal){
792                    $arrayConnectionEmailPrincipal["id_connection"] = $id_connection_email_principal;
793                    $arrayConnectionEmailPrincipal["connection_name"] = "Principal";
794                    if ($email_default != "Alternativo"){
795                        $arrayConnectionEmailPrincipal["connection_is_default"] = 't';
796                        $email_default = "Principal";
797                    } else {
798                        $arrayConnectionEmailPrincipal["connection_is_default"] = 'f';
799                    }
800                    $result = pg_insert($this->db, 'phpgw_cc_connections', $arrayConnectionEmailPrincipal);
801                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
802                    $arrayContactConnection["id_contact"] = $id_contact;
803                    $arrayContactConnection["id_connection"] = $id_connection_email_principal;
804                    $arrayContactConnection["id_typeof_contact_connection"] = 1;
805                    $result = pg_insert($this->db, 'phpgw_cc_contact_conns', $arrayContactConnection);
806                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
807                } else {
808                    $result = pg_update($this->db, 'phpgw_cc_connections', $arrayConnectionEmailPrincipal, array('id_connection' => $id_connection_email_principal));
809                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
810                }
811
812            } elseif ($found_id_connection_email_principal) {
813                $result = pg_delete($this->db, "phpgw_cc_contact_conns", array('id_contact' => $id_contact, 'id_connection' => $id_connection_email_principal));
814                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
815                $result = pg_delete($this->db, "phpgw_cc_contact_grps", array('id_connection' => $id_connection_email_principal));
816                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
817                $result = pg_delete($this->db, "phpgw_cc_connections", array('id_connection' => $id_connection_email_principal));
818                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
819            }
820
821            // Email Alternativo
822            if(isset($message->email2address)) {
823                $arrayConnectionEmailAlternativo["connection_value"] = $this->truncateString($this->formatMail(utf8_decode($message->email2address)),100);
824                if (!$found_id_connection_email_alternativo){
825                    $arrayConnectionEmailAlternativo["id_connection"] = $id_connection_email_alternativo;
826                    $arrayConnectionEmailAlternativo["connection_name"] = "Alternativo";
827                    if ($email_default != "Principal"){
828                        $arrayConnectionEmailAlternativo["connection_is_default"] = 't';
829                        $email_default = "Alternativo";
830                    } else {
831                        $arrayConnectionEmailAlternativo["connection_is_default"] = 'f';
832                    }
833                    $result = pg_insert($this->db, 'phpgw_cc_connections', $arrayConnectionEmailAlternativo);
834                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
835                    $arrayContactConnection["id_contact"] = $id_contact;
836                    $arrayContactConnection["id_connection"] = $id_connection_email_alternativo;
837                    $arrayContactConnection["id_typeof_contact_connection"] = 1;
838                    $result = pg_insert($this->db, 'phpgw_cc_contact_conns', $arrayContactConnection);
839                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
840                } else {
841                    $result = pg_update($this->db, 'phpgw_cc_connections', $arrayConnectionEmailAlternativo, array('id_connection' => $id_connection_email_alternativo));
842                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
843                }
844
845            } elseif ($found_id_connection_email_alternativo) {
846                $result = pg_delete($this->db, "phpgw_cc_contact_conns", array('id_contact' => $id_contact, 'id_connection' => $id_connection_email_alternativo));
847                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
848                $result = pg_delete($this->db, "phpgw_cc_contact_grps", array('id_connection' => $id_connection_email_alternativo));
849                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
850                $result = pg_delete($this->db, "phpgw_cc_connections", array('id_connection' => $id_connection_email_alternativo));
851                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
852            }
853            //TODO : Atribuir o email3address. O Expresso ainda não tem campo para um terceiro email :(
854
855            // Telefone Trabalho
856            if(isset($message->businessphonenumber)) {
857                $arrayConnectionTelTrabalho["connection_value"] = $this->truncateString(utf8_decode($message->businessphonenumber),100);
858                if (!$found_id_connection_tel_trabalho){
859                    $arrayConnectionTelTrabalho["id_connection"] = $id_connection_tel_trabalho;
860                    $arrayConnectionTelTrabalho["connection_name"] = "Trabalho";
861                    if ($tel_default != "Celular" and $tel_default != "Casa" and $tel_default != "Fax"  and $tel_default != "Principal"  and $tel_default != "Alternativo"){
862                        $arrayConnectionTelTrabalho["connection_is_default"] = 't';
863                        $tel_default = "Trabalho";
864                    } else {
865                        $arrayConnectionTelTrabalho["connection_is_default"] = 'f';
866                    }
867                    $result = pg_insert($this->db, 'phpgw_cc_connections', $arrayConnectionTelTrabalho);
868                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
869                    $arrayContactConnection["id_contact"] = $id_contact;
870                    $arrayContactConnection["id_connection"] = $id_connection_tel_trabalho;
871                    $arrayContactConnection["id_typeof_contact_connection"] = 2;
872                    $result = pg_insert($this->db, 'phpgw_cc_contact_conns', $arrayContactConnection);
873                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
874                } else {
875                    $result = pg_update($this->db, 'phpgw_cc_connections', $arrayConnectionTelTrabalho, array('id_connection' => $id_connection_tel_trabalho));
876                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
877                }
878
879            } elseif ($found_id_connection_tel_trabalho) {
880                $result = pg_delete($this->db, "phpgw_cc_contact_conns", array('id_contact' => $id_contact, 'id_connection' => $id_connection_tel_trabalho));
881                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
882                $result = pg_delete($this->db, "phpgw_cc_contact_grps", array('id_connection' => $id_connection_tel_trabalho));
883                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
884                $result = pg_delete($this->db, "phpgw_cc_connections", array('id_connection' => $id_connection_tel_trabalho));
885                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
886            }
887
888            // Telefone Celular
889            if(isset($message->mobilephonenumber)) {
890                $arrayConnectionTelCelular["connection_value"] = $this->truncateString(utf8_decode($message->mobilephonenumber),100);
891                if (!$found_id_connection_tel_celular){
892                    $arrayConnectionTelCelular["id_connection"] = $id_connection_tel_celular;
893                    $arrayConnectionTelCelular["connection_name"] = "Celular";
894                    if ($tel_default != "Trabalho" and $tel_default != "Casa" and $tel_default != "Fax"   and $tel_default != "Principal"  and $tel_default != "Alternativo"){
895                        $arrayConnectionTelCelular["connection_is_default"] = 't';
896                        $tel_default = "Celular";
897                    } else {
898                        $arrayConnectionTelCelular["connection_is_default"] = 'f';
899                    }
900                    $result = pg_insert($this->db, 'phpgw_cc_connections', $arrayConnectionTelCelular);
901                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
902                    $arrayContactConnection["id_contact"] = $id_contact;
903                    $arrayContactConnection["id_connection"] = $id_connection_tel_celular;
904                    $arrayContactConnection["id_typeof_contact_connection"] = 2;
905                    $result = pg_insert($this->db, 'phpgw_cc_contact_conns', $arrayContactConnection);
906                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
907                } else {
908                    $result = pg_update($this->db, 'phpgw_cc_connections', $arrayConnectionTelCelular, array('id_connection' => $id_connection_tel_celular));
909                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
910                }
911
912            } elseif ($found_id_connection_tel_celular) {
913                $result = pg_delete($this->db, "phpgw_cc_contact_conns", array('id_contact' => $id_contact, 'id_connection' => $id_connection_tel_celular));
914                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
915                $result = pg_delete($this->db, "phpgw_cc_contact_grps", array('id_connection' => $id_connection_tel_celular));
916                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
917                $result = pg_delete($this->db, "phpgw_cc_connections", array('id_connection' => $id_connection_tel_celular));
918                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
919            }
920
921            // Telefone Casa
922            if(isset($message->homephonenumber)) {
923                $arrayConnectionTelCasa["connection_value"] = $this->truncateString(utf8_decode($message->homephonenumber),100);
924                if (!$found_id_connection_tel_casa){
925                    $arrayConnectionTelCasa["id_connection"] = $id_connection_tel_casa;
926                    $arrayConnectionTelCasa["connection_name"] = "Casa";
927                    if ($tel_default != "Trabalho" and $tel_default != "Celular" and $tel_default != "Fax" and $tel_default != "Principal"  and $tel_default != "Alternativo"){
928                        $arrayConnectionTelCasa["connection_is_default"] = 't';
929                        $tel_default = "Casa";
930                    } else {
931                        $arrayConnectionTelCasa["connection_is_default"] = 'f';
932                    }
933                    $result = pg_insert($this->db, 'phpgw_cc_connections', $arrayConnectionTelCasa);
934                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
935                    $arrayContactConnection["id_contact"] = $id_contact;
936                    $arrayContactConnection["id_connection"] = $id_connection_tel_casa;
937                    $arrayContactConnection["id_typeof_contact_connection"] = 2;
938                    $result = pg_insert($this->db, 'phpgw_cc_contact_conns', $arrayContactConnection);
939                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
940                } else {
941                    $result = pg_update($this->db, 'phpgw_cc_connections', $arrayConnectionTelCasa, array('id_connection' => $id_connection_tel_casa));
942                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
943                }
944
945            } elseif ($found_id_connection_tel_casa) {
946                $result = pg_delete($this->db, "phpgw_cc_contact_conns", array('id_contact' => $id_contact, 'id_connection' => $id_connection_tel_casa));
947                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
948                $result = pg_delete($this->db, "phpgw_cc_contact_grps", array('id_connection' => $id_connection_tel_casa));
949                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
950                $result = pg_delete($this->db, "phpgw_cc_connections", array('id_connection' => $id_connection_tel_casa));
951                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
952            }
953
954            // Fax
955            if(isset($message->businessfaxnumber)) {
956                $arrayConnectionFax["connection_value"] = $this->truncateString(utf8_decode($message->businessfaxnumber),100);
957                if (!$found_id_connection_tel_fax){
958                    $arrayConnectionFax["id_connection"] = $id_connection_tel_fax;
959                    $arrayConnectionFax["connection_name"] = "Fax";
960                    if ($tel_default != "Trabalho" and $tel_default != "Celular" and $tel_default != "Casa" and $tel_default != "Principal"  and $tel_default != "Alternativo"){
961                        $arrayConnectionFax["connection_is_default"] = 't';
962                        $tel_default = "Fax";
963                    } else {
964                        $arrayConnectionFax["connection_is_default"] = 'f';
965                    }
966                    $result = pg_insert($this->db, 'phpgw_cc_connections', $arrayConnectionFax);
967                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
968                    $arrayContactConnection["id_contact"] = $id_contact;
969                    $arrayContactConnection["id_connection"] = $id_connection_tel_fax;
970                    $arrayContactConnection["id_typeof_contact_connection"] = 2;
971                    $result = pg_insert($this->db, 'phpgw_cc_contact_conns', $arrayContactConnection);
972                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
973                } else {
974                    $result = pg_update($this->db, 'phpgw_cc_connections', $arrayConnectionFax, array('id_connection' => $id_connection_tel_fax));
975                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
976                }
977
978            } elseif ($found_id_connection_tel_fax) {
979                $result = pg_delete($this->db, "phpgw_cc_contact_conns", array('id_contact' => $id_contact, 'id_connection' => $id_connection_tel_fax));
980                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
981                $result = pg_delete($this->db, "phpgw_cc_contact_grps", array('id_connection' => $id_connection_tel_fax));
982                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
983                $result = pg_delete($this->db, "phpgw_cc_connections", array('id_connection' => $id_connection_tel_fax));
984                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
985            }
986
987            // Telefone Principal
988            if(isset($message->business2phonenumber)) {
989                $arrayConnectionTelPrincipal["connection_value"] = $this->truncateString(utf8_decode($message->business2phonenumber),100);
990                if (!$found_id_connection_tel_principal){
991                    $arrayConnectionTelPrincipal["id_connection"] = $id_connection_tel_principal;
992                    $arrayConnectionTelPrincipal["connection_name"] = "Principal";
993                    if ($tel_default != "Celular" and $tel_default != "Casa" and $tel_default != "Fax"  and $tel_default != "Trabalho"  and $tel_default != "Alternativo"){
994                        $arrayConnectionTelPrincipal["connection_is_default"] = 't';
995                        $tel_default = "Principal";
996                    } else {
997                        $arrayConnectionTelPrincipal["connection_is_default"] = 'f';
998                    }
999                    $result = pg_insert($this->db, 'phpgw_cc_connections', $arrayConnectionTelPrincipal);
1000                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1001                    $arrayContactConnection["id_contact"] = $id_contact;
1002                    $arrayContactConnection["id_connection"] = $id_connection_tel_principal;
1003                    $arrayContactConnection["id_typeof_contact_connection"] = 2;
1004                    $result = pg_insert($this->db, 'phpgw_cc_contact_conns', $arrayContactConnection);
1005                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1006                } else {
1007                    $result = pg_update($this->db, 'phpgw_cc_connections', $arrayConnectionTelPrincipal, array('id_connection' => $id_connection_tel_principal));
1008                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1009                }
1010
1011            } elseif ($found_id_connection_tel_principal) {
1012                $result = pg_delete($this->db, "phpgw_cc_contact_conns", array('id_contact' => $id_contact, 'id_connection' => $id_connection_tel_principal));
1013                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1014                $result = pg_delete($this->db, "phpgw_cc_contact_grps", array('id_connection' => $id_connection_tel_principal));
1015                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1016                $result = pg_delete($this->db, "phpgw_cc_connections", array('id_connection' => $id_connection_tel_principal));
1017                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1018            }
1019
1020            // Telefone Alternativo
1021            if(isset($message->home2phonenumber)) {
1022                $arrayConnectionTelAlternativo["connection_value"] = $this->truncateString(utf8_decode($message->home2phonenumber),100);
1023                if (!$found_id_connection_tel_alternativo){
1024                    $arrayConnectionTelAlternativo["id_connection"] = $id_connection_tel_alternativo;
1025                    $arrayConnectionTelAlternativo["connection_name"] = "Alternativo";
1026                    if ($tel_default != "Trabalho" and $tel_default != "Celular" and $tel_default != "Fax" and $tel_default != "Principal"  and $tel_default != "Casa"){
1027                        $arrayConnectionTelAlternativo["connection_is_default"] = 't';
1028                        $tel_default = "Alternativo";
1029                    } else {
1030                        $arrayConnectionTelAlternativo["connection_is_default"] = 'f';
1031                    }
1032                    $result = pg_insert($this->db, 'phpgw_cc_connections', $arrayConnectionTelAlternativo);
1033                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1034                    $arrayContactConnection["id_contact"] = $id_contact;
1035                    $arrayContactConnection["id_connection"] = $id_connection_tel_alternativo;
1036                    $arrayContactConnection["id_typeof_contact_connection"] = 2;
1037                    $result = pg_insert($this->db, 'phpgw_cc_contact_conns', $arrayContactConnection);
1038                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1039                } else {
1040                    $result = pg_update($this->db, 'phpgw_cc_connections', $arrayConnectionTelAlternativo, array('id_connection' => $id_connection_tel_alternativo));
1041                    if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1042                }
1043
1044            } elseif ($found_id_connection_tel_alternativo) {
1045                $result = pg_delete($this->db, "phpgw_cc_contact_conns", array('id_contact' => $id_contact, 'id_connection' => $id_connection_tel_alternativo));
1046                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1047                $result = pg_delete($this->db, "phpgw_cc_contact_grps", array('id_connection' => $id_connection_tel_alternativo));
1048                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1049                $result = pg_delete($this->db, "phpgw_cc_connections", array('id_connection' => $id_connection_tel_alternativo));
1050                if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1051            }
1052
1053            //TODO : Permitir mais de um número de telefone para Trabalho, Celular e Casa. O Expresso ainda não suporta isso :(
1054
1055            $result = pg_query($this->db,"COMMIT;");
1056            if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1057            if (!$id) {
1058                $id = $id_contact;
1059            }
1060        } catch (Exception $e) {
1061            debugLog("exception -> " . $e->getMessage() . " - ARQUIVO: " . $e->getFile() . " - LINHA: " . $e->getLine());
1062            pg_query($this->db,"ROLLBACK;");
1063            return false;
1064        }
1065        return $this->StatMessage($folderid, $id);
1066    }
1067
1068    /**
1069     * Changes the 'read' flag of a message on disk. The $flags
1070     * parameter can only be '1' (read) or '0' (unread). After a call to
1071     * SetReadFlag(), GetMessageList() should return the message with the
1072     * new 'flags' but should not modify the 'mod' parameter. If you do
1073     * change 'mod', simply setting the message to 'read' on the mobile will trigger
1074     * a full resync of the item from the server.
1075     *
1076     * @param string        $folderid       id of the folder
1077     * @param string        $id             id of the message
1078     * @param int           $flags          read flag of the message
1079     *
1080     * @access public
1081     * @return boolean                      status of the operation
1082     * @throws StatusException              could throw specific SYNC_STATUS_* exceptions
1083     */
1084    public function SetReadFlag($folderid, $id, $flags)
1085    {
1086
1087        // TODO: Implement SetReadFlag() method.
1088    }
1089
1090    /**
1091     * Called when the user has requested to delete (really delete) a message. Usually
1092     * this means just unlinking the file its in or somesuch. After this call has succeeded, a call to
1093     * GetMessageList() should no longer list the message. If it does, the message will be re-sent to the mobile
1094     * as it will be seen as a 'new' item. This means that if this method is not implemented, it's possible to
1095     * delete messages on the PDA, but as soon as a sync is done, the item will be resynched to the mobile
1096     *
1097     * @param string        $folderid       id of the folder
1098     * @param string        $id             id of the message
1099     *
1100     * @access public
1101     * @return boolean                      status of the operation
1102     * @throws StatusException              could throw specific SYNC_STATUS_* exceptions
1103     */
1104    public function DeleteMessage($folderid, $id)
1105    {
1106        ZLog::Write(LOGLEVEL_DEBUG, "ExpressoContactProvider->DeleteMessage()");
1107        $result = pg_query($this->db,"BEGIN;");
1108        try {
1109            $result_contact = pg_query($this->db, "select id_contact from phpgw_cc_contact where id_contact = " . $id . ";");
1110            if ($result_contact == FALSE) throw new Exception(pg_last_error($this->db));
1111            while ($row_contact = pg_fetch_row($result_contact)) {
1112                if(isset($row_contact[0])) {
1113                    $id_contact = $row_contact[0];
1114                }
1115            }
1116            if(!isset($id_contact)){
1117                return true;
1118            }
1119
1120            $result_contact_addrs = pg_query($this->db, "select id_address from phpgw_cc_contact_addrs where id_contact = " . $id_contact . ";");
1121            if ($result_contact_addrs == FALSE) throw new Exception(pg_last_error($this->db));
1122            while ($row_contact_addrs = pg_fetch_row($result_contact_addrs)) {
1123                if(isset($row_contact_addrs[0])) {
1124                    $id_address = $row_contact_addrs[0];
1125                    $result_delete_address = pg_delete($this->db, "phpgw_cc_addresses", array('id_address' => $id_address));
1126                    if ($result_delete_address == FALSE) throw new Exception(pg_last_error($this->db));
1127                }
1128            }
1129
1130            $result_delete_contact_addrs = pg_delete($this->db, "phpgw_cc_contact_addrs", array('id_contact' => $id_contact));
1131            if ($result_delete_contact_addrs == FALSE) throw new Exception(pg_last_error($this->db));
1132
1133            $result_contact_conns = pg_query($this->db, "select id_connection from phpgw_cc_contact_conns where id_contact = " . $id_contact . ";");
1134            if ($result_contact_conns == FALSE) throw new Exception(pg_last_error($this->db));
1135            while ($row_contact_conns = pg_fetch_row($result_contact_conns)) {
1136                if(isset($row_contact_conns[0])) {
1137                    $id_connection = $row_contact_conns[0];
1138                    $result_delete_connections = pg_delete($this->db, "phpgw_cc_connections", array('id_connection' => $id_connection));
1139                    if ($result_delete_connections == FALSE) throw new Exception(pg_last_error($this->db));
1140                    $result_delete_contact_grps = pg_delete($this->db,"phpgw_cc_contact_grps", array('id_connection' => $id_connection));
1141                    if ($result_delete_contact_grps == FALSE) throw new Exception(pg_last_error($this->db));
1142                }
1143            }
1144
1145            $result_delete_contact_conns = pg_delete($this->db, "phpgw_cc_contact_conns", array('id_contact' => $id_contact));
1146            if ($result_delete_contact_conns == FALSE) throw new Exception(pg_last_error($this->db));
1147            $result_delete_contact = pg_delete($this->db, "phpgw_cc_contact", array('id_contact' => $id_contact));
1148            if ($result_delete_contact == FALSE) throw new Exception(pg_last_error($this->db));
1149            $result = pg_query($this->db,"COMMIT;");
1150            if ($result == FALSE) throw new Exception(pg_last_error($this->db));
1151        } catch (Exception $e) {
1152            pg_query($this->db,"ROLLBACK;");
1153            debugLog("exception -> " . $e->getMessage() . " - ARQUIVO: " . $e->getFile() . " - LINHA: " . $e->getLine());
1154            return false;
1155        }
1156        return true;
1157    }
1158
1159    /**
1160     * Called when the user moves an item on the PDA from one folder to another. Whatever is needed
1161     * to move the message on disk has to be done here. After this call, StatMessage() and GetMessageList()
1162     * should show the items to have a new parent. This means that it will disappear from GetMessageList()
1163     * of the sourcefolder and the destination folder will show the new message
1164     *
1165     * @param string        $folderid       id of the source folder
1166     * @param string        $id             id of the message
1167     * @param string        $newfolderid    id of the destination folder
1168     *
1169     * @access public
1170     * @return boolean                      status of the operation
1171     * @throws StatusException              could throw specific SYNC_MOVEITEMSSTATUS_* exceptions
1172     */
1173    public function MoveMessage($folderid, $id, $newfolderid)
1174    {
1175        // TODO: Implement MoveMessage() method.
1176    }
1177
1178    /**
1179     * Authenticates the user
1180     *
1181     * @param string        $username
1182     * @param string        $domain
1183     * @param string        $password
1184     *
1185     * @access public
1186     * @return boolean
1187     * @throws FatalException   e.g. some required libraries are unavailable
1188     */
1189    public function Logon($username, $domain, $password)
1190    {
1191        ZLog::Write(LOGLEVEL_DEBUG, "ExpressoContactProvider->Logon()");
1192
1193        $ldapConfig = parse_ini_file(EXPRESSO_PATH . '/prototype/config/OpenLDAP.srv' , true );
1194        $ldapConfig =  $ldapConfig['config'];
1195        $sr = ldap_search( $GLOBALS['connections']['ldap'] , $ldapConfig['context'] , "(uid=$username)" , array('uidNumber'), 0 , 1 );
1196        if(!$sr) return false;
1197
1198        $entries = ldap_get_entries( $GLOBALS['connections']['ldap'] , $sr );
1199        $this->_uidnumber = $entries[0]['uidnumber'][0];
1200        $this->db =  $GLOBALS['connections']['db'] ;
1201
1202        return true;
1203    }
1204
1205    /**
1206     * Logs off
1207     * non critical operations closing the session should be done here
1208     *
1209     * @access public
1210     * @return boolean
1211     */
1212    public function Logoff()
1213    {
1214       return true;
1215    }
1216
1217    /**
1218     * Sends an e-mail
1219     * This messages needs to be saved into the 'sent items' folder
1220     *
1221     * Basically two things can be done
1222     *      1) Send the message to an SMTP server as-is
1223     *      2) Parse the message, and send it some other way
1224     *
1225     * @param SyncSendMail        $sm         SyncSendMail object
1226     *
1227     * @access public
1228     * @return boolean
1229     * @throws StatusException
1230     */
1231    public function SendMail($sm)
1232    {
1233        // TODO: Implement SendMail() method.
1234    }
1235
1236    /**
1237     * Returns the waste basket
1238     *
1239     * The waste basked is used when deleting items; if this function returns a valid folder ID,
1240     * then all deletes are handled as moves and are sent to the backend as a move.
1241     * If it returns FALSE, then deletes are handled as real deletes
1242     *
1243     * @access public
1244     * @return string
1245     */
1246    public function GetWasteBasket()
1247    {
1248        // TODO: Implement GetWasteBasket() method.
1249    }
1250
1251    /**
1252     * Returns the content of the named attachment as stream. The passed attachment identifier is
1253     * the exact string that is returned in the 'AttName' property of an SyncAttachment.
1254     * Any information necessary to locate the attachment must be encoded in that 'attname' property.
1255     * Data is written directly - 'print $data;'
1256     *
1257     * @param string        $attname
1258     *
1259     * @access public
1260     * @return SyncItemOperationsAttachment
1261     * @throws StatusException
1262     */
1263    public function GetAttachmentData($attname)
1264    {
1265        // TODO: Implement GetAttachmentData() method.
1266    }
1267
1268    function escape($data){
1269        if (is_array($data)) {
1270            foreach ($data as $key => $val) {
1271                $data[$key] = $this->escape($val);
1272            }
1273            return $data;
1274        }
1275        $data = str_replace("\r\n", "\n", $data);
1276        $data = str_replace("\r", "\n", $data);
1277        $data = str_replace(array('\\', ';', ',', "\n"), array('\\\\', '\\;', '\\,', '\\n'), $data);
1278        return $data;
1279    }
1280
1281    function truncateString($string, $size)
1282    {
1283        if(strlen($string) <= $size) return $string;
1284        else return substr($string, 0, $size - 1);
1285    }
1286
1287    function formatMail($mail)
1288    {
1289        if (preg_match('/[^\W][a-zA-Z0-9_]+(\.[a-zA-Z0-9_]+)*\@[a-zA-Z0-9_]+(\.[a-zA-Z0-9_]+)*\.[a-zA-Z]{2,4}/', $mail , $mat ))
1290            return $mat[0];
1291        else
1292            return '';
1293    }
1294
1295    function  removeAccents($data){
1296        $data = strtr($data,"ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÜÚÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùüúþÿ","aaaaaaaceeeeiiii noooooxouuutbaaaaaaaceeeeiiii nooooo/ouuuty");
1297        return $data;
1298    }
1299
1300}
Note: See TracBrowser for help on using the repository browser.