Request New Attributes

[sparadrus]

Hello, I use your CRON script to sync user. It work perfectly with my 2 AD and it save my life :)

I wish request new fields in my LDAP, such as telephoneNumber, Site location, .. How i can implement this please? The purpose would be to update Jomsocial db fields. Thank you in advance, Regards Philippe

[Tazzios]

I do something like that Community builder fields. i also insert every user in joomla so that the address book is always complete.

Take this script https://samjlevy.com/mydap-v4/ Setup you LDAP connection

Line 26 change false to true to get atributes

Add this at line 148

// DB verbinding gegevens
$servername = "127.0.0.1";
$username = "Joomla";
$password = "password";
$dbname = "joomla";
$Joomla_prefix="epf4j";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

line 161 add extra fields like $telephonenumber = isset($attr['telephonenumber'][0]) ? $attr['telephonenumber'][0] : "000";

line 162 do stuff!

    //Check if account is active (probably not the best way)
    if ($Actief==514 or $Actief==546 or $Actief==66050 or $Actief==66082 or $Actief==252658 or $Actief==26290 or $Actief==328194 or $Actief==328226): 
    {
        //account not active so disable in joomla
        $sql = "UPDATE IGNORE ".$Joomla_prefix ."_comprofiler Ldap INNER JOIN ".$Joomla_prefix    ."_users joomla ON Ldap.user_id=Joomla.id SET 
        joomla.block = 1 WHERE joomla.USERNAME ='$samaccountname'";

        if ($conn->query($sql) === TRUE) {
            echo "$i. account disabled<br>";
        } else {
            echo "$i. Error account disabled: " . $sql . "<br>" . $conn->error;
        }

    }
    else:
    {

        //Check if user exist
        $sql = "SELECT * FROM ".$Joomla_prefix ."_users WHERE USERNAME='$samaccountname'";
        if(mysqli_num_rows($conn->query($sql)) > 0)
        {
        //echo "$i. already exist"
        }
        else
        {

            // add user to joomla
            $sql = "INSERT IGNORE INTO ".$Joomla_prefix ."_users (name,username,email,params,registerDate) VALUES ('$name','$samaccountname','$mail','{\"auth_type\":\"LDAP\",\"auth_domain\":\"WEBSITENAME\"}','$now')";
            if ($conn->query($sql) === TRUE) {
                echo "$i. account added<br>";
            } else {
                echo "$i. Error account adding: " . $sql . "<br>" . $conn->error;
            }

            // basis rights (2 = Registered).
            $sql = "INSERT IGNORE INTO ".$Joomla_prefix ."_user_usergroup_map (user_id ,group_id ) SELECT id,2 FROM ". $Joomla_prefix ."_users joomla where joomla.USERNAME='$samaccountname'";
            if ($conn->query($sql) === TRUE) {
                //echo "account right added <br>";
            } else {
                echo "$i. Error account  right added : " . $sql . "<br>" . $conn->error;
            }

        }
    }
    endif;

    // Sync community builder and joomla
    if ($conn->query("INSERT IGNORE INTO ". $Joomla_prefix ."_comprofiler(id,user_id) SELECT id,id FROM ". $Joomla_prefix ."_users joomla where joomla.USERNAME='$samaccountname'") === TRUE) {
        //echo "Cb updated" ;
    } else {
        echo "$i. Error table sync: <br>" . $conn->error;
    }

    // Show result 
    echo "$i. $samaccountname - $mail - $name - ${'sector'} - $physicaldeliveryofficename - " . substr($telephonenumber, -3) ;

    // update communitybuilder 
    $sql = "update ignore ". $Joomla_prefix ."_comprofiler Ldap inner join ". $Joomla_prefix ."_users joomla on Ldap.user_id=Joomla.id SET 
    Ldap.cb_kantoor= '$physicaldeliveryofficename', 
    Ldap.cb_telefoonnummer='$telephonenumber',
    Ldap.cb_afdeling= '$department',
    ldap.cb_titel= '$title',
    ldap.cb_telnrkort = '" . substr($telephonenumber, -3) . "',
    Ldap.avatar= '$samaccountname.png',
    joomla.name= '$name'
    WHERE joomla.USERNAME ='$samaccountname'";

    if ($conn->query($sql) === TRUE) {
        echo "<br>";
    } else {
        echo "$i. Error updating CB fields: " . $sql . "<br>" . $conn->error;
    }

line 172

//sluit DB verBinding
$conn->close();

[sparadrus]

Great Thanks, i test this asap ! Regards, Philippe

[sparadrus]

Hello, i have test with one attribute (telephoneNumber), it's ok but my code is not optimized.. Jomsocial create one table field_value and link user by id. i'm not develloper but if it aidful other user of Jomsocial it's cool.. I'll be back with a proper script in few days

Thanks again ;)

// DB Connection and Jomsocial ID Attributes

$servername = "127.0.0.1";
$username = "UserOfDB";
$password = "MyPass123";
$dbname = "DatabaseName";
$Joomla_prefix = "lx66666";
$JomsTelephoneID = "8";

// JOMSOCIAL UPDATES

// User exist in Joomla ? If not, skip at row 232

$req_usercreated = mysqli_query($conn, "SELECT id FROM ". $Joomla_prefix ."_users WHERE USERNAME='$samaccountname'");
$nbuser = mysqli_num_rows($req_usercreated);    
if ($nbuser > 0)
        {       
// Get ID of user in Joomla

$req_userid = mysqli_prepare($conn, "SELECT id,username FROM ". $Joomla_prefix ."_users WHERE username='$samaccountname'") or die(mysqli_error($conn));
mysqli_stmt_execute($req_userid) or die(mysqli_error($conn));
mysqli_stmt_bind_result($req_userid, $userid['id'], $userid['username']);
mysqli_stmt_fetch($req_userid);
$req_userid->close();

// Attribute (row) exist in Jomsocial Tables?
// TelephoneNumber
$req_JomsTelephone = mysqli_query($conn, "SELECT value FROM ". $Joomla_prefix ."_community_fields_values WHERE user_id=". $userid['id'] ." AND field_id=". $JomsTelephoneID .""); 
$nb = mysqli_num_rows($req_JomsTelephone);

if ($nb > 0)
        {
        echo "Attribute Exist for ". $samaccountname ." (". $userid['id'] .") -> UPDATE ". "\n" ."";
        $req_JomsTelephone = mysqli_query($conn, "UPDATE IGNORE ". $Joomla_prefix ."_community_fields_values SET value=". $telephonenumber ." WHERE user_id=". $userid['id'] ." AND field_id=". $JomsTelephoneID ."");
        }
        else
        {
        echo "No attribute in Jomsocial for ". $samaccountname ." (". $userid['id'] .")  -> INSERT ". "\n" ."";
        $req_JomsTelephone = mysqli_query($conn, "INSERT IGNORE INTO ". $Joomla_prefix ."_community_fields_values (id,user_id,field_id,value,access,params) VALUES (0,". $userid['id'] ." ,". $JomsTelephoneID .",". $telephonenumber .",10,'')");
        }
}       
// Fin du traitement
        else
        {
        echo "No user with the login ". $samaccountname ." are created in Joomla -> SKIP ". "\n" ."";
        }
}       
// Close LDAP Connection
mydap_end();
// Close DB Connection
$conn->close();
// Here you can open a new connection with mydap_connect() if needed, such as to a different AD server`

[Tazzios]

@sparadrus Do you still use the sync script for jomsocial and do you have a new version?

It would be nice if I can place it in my git project for others to use and improve. ( tough, i have to rename the project then offcourse. )

https://github.com/Tazzios/Joomla-CB-LDAP-sync