SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create or replace
PROCEDURE AGGIORNA_TABELLA_UTENTI AS
BEGIN

--start
  merge into Utenti u

  --seleziona tutti gli utenti da active directory
    using 
  (
    SELECT givenname AS Nome, sn AS Cognome, PhysicalDeliveryOfficeName as Gruppo, 
    department as Dipartimento, telephoneNumber as Telefono,
    mobile as Cellulare, facsimileTelephoneNumber AS Fax, mail as Email, 
      title as Grado, userAccountControl
    FROM OPENROWSET
    (
      'ADSDSOObject', 
      'SACE',
      'SELECT givenname, sn, PhysicalDeliveryOfficeName, mail, telephoneNumber,
        mobile, facsimileTelephoneNumber,
        title, department, company, userAccountControl
      FROM ''LDAP://OU=Users,OU=Lug-Franscini,DC=cbglugano,DC=local'' 
      WHERE
        objectCategory=''user'' 
        AND objectcategory=''person''
       ORDER BY sn' 
    )
    where givenname NOT LIKE 'Template%'
    and givenname NOT LIKE '_Template%'
    AND userAccountControl != 514
    order by Nome
  ) ActiveDirectory --Alias ActiveDirectory

--controlla se trova una corrispondenza tra la tabella "Utenti" e "Active Directory"
on (u.utente_mail = ActiveDirectory.mail)

--se trova una corrispondenza e useraAccountControl == 514, 
--allora aggiorna i dati e imposta lo stato su 'Non attivo'
  when matched and userAccountControl == 514 then 
    update set u.utente_nome = ActiveDirectory.Nome, u.utente_cognome = ActiveDirectory.Cognome,
  u.utente_gruppo = ActiveDirectory.Gruppo, u.utente_dipartimento = ActiveDirectory.Dipartimento,
  u.utente_telefono = ActiveDirectory.Telefono, u.utente_cellulare = ActiveDirectory.Cellulare,
  u.utente_fax = ActiveDirectory.Fax, u.utente_mail = ActiveDirectory.Email, 
  u.utente_grado = ActiveDirectory.Grado, u.utente_stato = 'Non attivo'

--se trova una corrispondenza 
--allora aggiorna i dati
  when matched and userAccountControl != 514 then 
    update set u.utente_nome = ActiveDirectory.Nome, u.utente_cognome = ActiveDirectory.Cognome,
  u.utente_gruppo = ActiveDirectory.Gruppo, u.utente_dipartimento = ActiveDirectory.Dipartimento,
  u.utente_telefono = ActiveDirectory.Telefono, u.utente_cellulare = ActiveDirectory.Cellulare,
  u.utente_fax = ActiveDirectory.Fax, u.utente_mail = ActiveDirectory.Email, 
  u.utente_grado = ActiveDirectory.Grado

--se non trova una corrispondenza, allora inserisci il nuovo utente
 when not matched and userAccountControl != 514 then
  insert 
  (
    utente_nome, utente_cognome, utente_gruppo, utente_dipartimento, 
    utente_telefono, utente_cellulare, utente_fax, utente_mail,
    utente_grado, utente_stato
  )
  values 
  (
    ActiveDirectory.Nome, ActiveDirectory.Cognome, ActiveDirectory.Gruppo, 
    ActiveDirectory.Dipartimento, ActiveDirectory.Telefono,
    ActiveDirectory.Cellulare, ActiveDirectory.Fax, ActiveDirectory.Email, 
    ActiveDirectory.Grado, 'A'
  )

  commit;
END;
GO