@using Seal.Model;
@using System.Net;
@using System.Data
@using System.Data.OleDb
@using Seal.Helpers
@using System.DirectoryServices;

@{
    SecurityUser user = Model;
    user.Name = user.WebUserName; //Display name for the log file
    bool blConnected = false;

    // first validate the access of the user in the LDAP
    try
    {
        // Get the connection
        DirectoryEntry Ldap = new DirectoryEntry("LDAP://"+user.Security.GetValue("ldap_server"), user.WebUserName, user.WebPassword);
        object nativeObject = Ldap.NativeObject;
        blConnected = true;
    }

    catch(DirectoryServicesCOMException Ex)
    {
        Console.WriteLine(Ex.Message);
        user.Error = Ex.Message;
    }	

    try
    {
        if (blConnected == true) {
            // If the connection is successfull, get the user group in the excel sheet
            //string connectionString = "Server=;Database=dbSegurancaBI;User Id=sa;Password=mengo74*;";
            
            string connectionString = "Provider=SQLOLEDB.1;Initial Catalog=dbSegurancaBI;Data Source=SERVERNAME;User Id=sa;Password=sapassword;";
                
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();

            string strSql = "EXECUTE spu_BuscaUsuario " + Helper.QuoteSingle(user.WebUserName) + "";

            OleDbCommand command = new OleDbCommand(string.Format(strSql), connection);

            object group = command.ExecuteScalar();

            if (group != null && group != DBNull.Value && (string)group != "SEM")
            {
                user.AddSecurityGroup((string)group);	
            }
            else 
            {
                user.Error = "No reporting group assigned to this user";
                throw new Exception("usuário autenticado com Sucesso, porém sem nenhum vínculo com os objetos de segurança do ATP BI.");
            }	
                
        }
        else {
            user.Error = "Please check your user / password";
        }
    }
    catch(Exception e)
    {
        throw e;
    }
}

CREATE PROCEDURE [dbo].[spu_BuscaUsuario]
(
	@NomeAD	VARCHAR(255)
)
AS

SET @NomeAD = REPLACE(@NomeAD,'/','\')

IF CHARINDEX('@',@NomeAD) > 0 BEGIN
	SET @NomeAD	=	'ATP-BSB\' + SUBSTRING(@NomeAD,0,CHARINDEX('@',@NomeAD))
END ELSE BEGIN
	IF CHARINDEX('\',@NomeAD) = 0 BEGIN
		SET @NomeAD	=	'ATP-BSB\' + @NomeAD
	END
END

IF NOT EXISTS (
			SELECT 
					1
			FROM 
					Usuario
			WHERE
					Usuario.NomeAD = @NomeAD
		)
BEGIN
	INSERT INTO Usuario (NomeAD) VALUES(LOWER(@NomeAD))
	SELECT GrupoSeal  as Grupo FROM Usuario
END;

SELECT 
		GrupoSeal as Grupo 
FROM 
		Usuario 
WHERE 
		Usuario.NomeAD = @NomeAD
GO

var sources = Repository.Instance.Sources;
var connectionString = sources[0].Connection.FullConnectionString; //Main connection
var connections = sources[0].Connections;