. */ /** * @brief manage contacts duplicates * * * @file * @author Laurent Giovannoni * @date $date$ * @version $Revision$ * @ingroup admin */ require_once 'apps' . DIRECTORY_SEPARATOR . $_SESSION['config']['app_id'] . DIRECTORY_SEPARATOR . 'class' . DIRECTORY_SEPARATOR . 'class_business_app_tools.php'; $admin = new core_tools(); $admin->test_admin('admin_contacts', 'apps'); $func = new functions(); $db = new Database(); $business = new business_app_tools(); $_SESSION['m_admin'] = array(); /****************Management of the location bar ************/ $init = false; if (isset($_REQUEST['reinit']) && $_REQUEST['reinit'] == "true") { $init = true; } $level = ""; if ( isset($_REQUEST['level']) && ( $_REQUEST['level'] == 2 || $_REQUEST['level'] == 3 || $_REQUEST['level'] == 4 || $_REQUEST['level'] == 1 ) ) { $level = $_REQUEST['level']; } $page_path = $_SESSION['config']['businessappurl'] . 'index.php?page=manage_duplicates&admin=contacts'; $page_label = _MANAGE_DUPLICATES; $page_id = "manage_duplicates"; $admin->manage_location_bar($page_path, $page_label, $page_id, $init, $level); /***********************************************************/ $color = array('#254e7b','#5584b1','#85c1e5','#a2adc3'); function randomColor($lastColor) { if ($lastColor <> 0) { $val = rand(0, $lastColor - 1); } else { $val = 3; } return $val; } echo '

 ' . _MANAGE_DUPLICATES . '

'; echo '
'; echo '
'; //TODO: ENABLE THIS FUNCTION FOR ALL COLLECTION USING CONTACTS //update NULL to '' $db->query("UPDATE contacts_v2 SET user_id='' WHERE user_id IS NULL"); //duplicates by society $selectDuplicatesBySociety = "SELECT contact_id, society, lower(translate(society,'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-','aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) as society_comp from contacts_v2 WHERE is_corporate_person = 'Y' AND lower(translate(society,'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-','aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) in ( SELECT lower(translate(society,'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-','aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) FROM contacts_v2 GROUP BY lower(translate(society,'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-','aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) HAVING Count(lower(translate(society,'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-','aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr '))) > 1 and lower(translate(society,'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-','aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) <> '' ) order by lower(society),contact_id"; $htmlTabSoc = '
'; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $tabSoc = array(); $socCompare = ''; $colorToUse = ''; $colorNumber = '2'; $stmt = $db->query($selectDuplicatesBySociety); $cptSoc = 0; $i = 0; while($lineDoublSoc = $stmt->fetchObject()) { $stmt2 = $db->query("SELECT id,firstname,lastname,address_num,address_street,address_postal_code,address_town,email FROM contact_addresses WHERE contact_id = ?", array($lineDoublSoc->contact_id)); $res = $stmt2->fetchObject(); $nb_addresses = $stmt2->rowCount(); $arr_address = array($res->address_num,$res->address_street,$res->address_postal_code,$res->address_town); $tmp_address = implode(' ', $arr_address); if(!empty($res->firstname) || !empty($res->lastname) ){ $arr_address = array($res->firstname.' '.$res->lastname,$tmp_address); $address = implode(', ', $arr_address); }else{ $address = $tmp_address; } if(!empty($res->email)){ $address .= ' ('.$res->email.')'; } if (empty(trim($address))) { $address = 'adresse vide ...'; } if ($lineDoublSoc->contact_id <> ''/* && $result_address->id <> '' */) { $cptSoc++; if ($socCompare == $lineDoublSoc->society_comp) { //echo 'doublon
'; } else { $i++; //echo 'new doublon
'; $class = 'duplicate_'.$i; if ($colorToUse == 'LightYellow'){ $colorToUse = 'white'; }else{ $colorToUse = 'LightYellow'; } } $socCompare = $lineDoublSoc->society_comp; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= ''; } $comp_society = $lineDoublSoc->contact_id; } $htmlTabSoc .= ''; //$func->show_array($tabSoc); $htmlTabSoc .= '
' . _DUPLICATES_BY_SOCIETY . '
 ' . _ID . '' . _STRUCTURE_ORGANISM . '' . _ADDRESS.' '._INFO. '' . _ADDRESS.' '._NB. ' 
' . $lineDoublSoc->contact_id . '' . $lineDoublSoc->society . '' . $address . '' . $nb_addresses.' adresse(s) au total ...
'; $htmlTabSoc .= ''; $htmlTabSoc .= ''; $htmlTabSoc .= '
'; if ($cptSoc == 0) { echo _NO_SOCIETY_DUPLICATES . '
'; } else { echo $htmlTabSoc; } ?>
1 and lower(translate(lastname||' '||firstname,'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-','aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) <> ' ') order by lower(translate(lastname||' '||firstname,'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-','aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')),contact_id"; $htmlTabName = '
'; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $tabName = array(); $nameCompare = ''; $colorToUse = ''; $colorNumber = '2'; $stmt = $db->query($selectDuplicatesByName); $cptName = 0; $i++; while($lineDoublName = $stmt->fetchObject()) { $stmt2 = $db->query("SELECT id,address_num,address_street,address_postal_code,address_town,email FROM contact_addresses WHERE contact_id = ?", array($lineDoublName->contact_id)); $res = $stmt2->fetchObject(); $nb_addresses = $stmt2->rowCount(); $arr_address = array($res->address_num,$res->address_street,$res->address_postal_code,$res->address_town); $tmp_address = implode(' ', $arr_address); $address = $tmp_address; if(!empty($res->email)){ $address .= ' ('.$res->email.')'; } if (empty(trim($address))) { $address = 'adresse vide ...'; } if ($lineDoublName->contact_id <> '') { $cptName++; if ($nameCompare == $lineDoublName->lastname_firstname) { //echo 'doublon
'; } else { //echo 'new doublon
'; $i++; $class = 'duplicate_person_'.$i; if ($colorToUse == 'LightYellow'){ $colorToUse = 'white'; }else{ $colorToUse = 'LightYellow'; } } $corporatePeople = ($lineDoublName->is_corporate_person == "Y")? _YES : _NO; $nameCompare = $lineDoublName->lastname_firstname; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; $htmlTabName .= ''; } } $htmlTabName .= ''; //$func->show_array($tabName); $htmlTabName .= '
' . _DUPLICATES_BY_NAME . '
 ' . _ID . '' . _TITLE2 . '' . _LASTNAME . '' . _FIRSTNAME . '' . _ADDRESS.' '._INFO. '' . _ADDRESS.' '._NB. ' 
' . $lineDoublName->contact_id . '' . $business->get_label_title($lineDoublName->title) . '' . $lineDoublName->lastname . '' . $lineDoublName->firstname . '' . $address . '' . $nb_addresses.' adresse(s) au total ...
'; $htmlTabName .= ''; $htmlTabName .= '
'; if ($cptName == 0) { echo '
'; echo _NO_NAME_DUPLICATES . '
'; } else { echo $htmlTabName; } echo '
'; echo '
';