dbo.trulio_validacion_lote_prueba2

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
SELECT * FROM PADRONES WHERE identificacion = '20955834373'
*/

CREATE OR ALTER PROCEDURE dbo.trulio_validacion_lote_prueba2  
AS
BEGIN
    SET NOCOUNT ON;
 declare @reference int, 
         @FirstName     VARCHAR(100),
   @SurName       VARCHAR(100),
   @Gender        VARCHAR(1),
   @StreetAddress VARCHAR(250),
   @ZipCode       VARCHAR(20),
   @DateOfBirth   DATE,
   @IDNumber      VARCHAR(20),
   @FechaFallecimiento DATE,
   @DNI varchar(20)

 declare @resultado table 
 ( reference   int, 
  nuestrocuit varchar(20),
  FirstName     VARCHAR(100),
  SurName       VARCHAR(100),
  Gender        VARCHAR(1),
  StreetAddress VARCHAR(250),
  ZipCode       VARCHAR(20),
  DateOfBirth   DATE,
  IDNumber      VARCHAR(20),
  FechaFallecimiento DATE,
  DNI varchar(20),
  error_name      varchar(20),
  error_name_contains  varchar(20),
  error_gender    varchar(20),
  error_address   varchar(20),
  error_zipcode   varchar(20),
  error_nac       varchar(20),
  error_cuit      varchar(20),
  error_dni       varchar(20),
  error_fall      varchar(20),
  financial_status char(2),
  request_date date,
  transaction_id int ,
  transaction_status int ,
  period_year int ,
  period_month int ,
  period_count int 
 )

 DECLARE C_busqueda CURSOR FOR
  select 
  Reference, 
  ForeName as Firstname,
  SurName  as Surname,
  '' as Gender, 
  isnull(ltrim(rtrim(AddressLine1)),'') + ' ' + 
  isnull(ltrim(rtrim(AddressLine2)),'') + ' ' + 
  isnull(ltrim(rtrim(AddressLine3)),'') + ' ' + 
  isnull(ltrim(rtrim(AddressLine4)),'') + ' ' + 
  isnull(ltrim(rtrim(AddressLine5)),'') + ' ' as StreetAddress,
  AddressLine6 as ZipCode,
  yob+'-'+mob+'-'+dob as DateOfBirth,
  replace([Tax ID (CUIL/CUIT)],'-','') as IDNumber,
  null as FechaFallecimiento, 
  [DNI ] as DNI
  from [dbo].[test-trulioo]
  --where [DNI ]= '28061875 '

 OPEN C_busqueda
 FETCH NEXT FROM C_busqueda INTO @Reference, 
         @FirstName,
         @SurName,
         @Gender,
         @StreetAddress,
         @ZipCode,
         @DateOfBirth,
         @IDNumber,
         @FechaFallecimiento,
         @DNI
 WHILE @@fetch_status = 0
 BEGIN 
 select @StreetAddress = REPLACE(@StreetAddress,'       ',' ')
 select @StreetAddress = REPLACE(@StreetAddress,'      ',' ')
 select @StreetAddress = REPLACE(@StreetAddress,'     ',' ')
 select @StreetAddress = REPLACE(@StreetAddress,'    ',' ')
 select @StreetAddress = REPLACE(@StreetAddress,'   ',' ')
 select @StreetAddress = REPLACE(@StreetAddress,'  ',' ')
 select @StreetAddress = REPLACE(@StreetAddress,' ',' ')
 select @StreetAddress = REPLACE(@StreetAddress,CHAR(9),'')
   /*insert into @resultado 
          EXEC [Trulioo_Validacion_NEW] @FirstName,
    @SurName,
    @Gender,
    @StreetAddress,
    @ZipCode,
    @DateOfBirth,
    @IDNumber,
    @FechaFallecimiento,
    @DNI*/

            declare @error_cuit    varchar(10) = 'false'
    declare @error_name    varchar(10) = 'false'
    declare @error_name_contains    varchar(20) = 'false'
    declare @error_address varchar(10) = 'false'
    declare @error_zipcode varchar(10) = 'false'
    declare @error_nac     varchar(10) = 'false'
 declare @error_gender  varchar(10) = 'false'
 declare @error_fall    varchar(10) = 'false'
 declare @error_dni     varchar(10) = 'false'


 declare @resp int 
    declare @busqueda_cuit int, @busqueda_igual int, @busqueda_contains int, @busqueda_dni int
    declare @domicilios table (id int, domicilio varchar(200), pai_nombre varchar(20), prv_nombre varchar(100), loc_nombre varchar(100), codpos varchar(10), tipo_domicilio varchar(10), created datetime)
    declare @p_cuit varchar(20) = LTRIM(rtrim(@IDNumber))
 if (@p_cuit = '') set @p_cuit = null 
 if (@DNI = '') set @DNI = null 
 declare @DNI2 char(8) = (select right(('0000000'+@DNI),8))

    set @DNI = LTRIM(rtrim(@DNI))
 if (@FechaFallecimiento = '1900-01-01') set @FechaFallecimiento = null
 if (@DateOfBirth = '1900-01-01') set @DateOfBirth = null

    declare @nombre varchar(200) = LTRIM(rtrim(@SurName)) +  ' ' + LTRIM(rtrim(@FirstName))
 declare @nombre_contains varchar(500)
    while (@nombre like '%  %')
    begin
  set @nombre = (select replace(@nombre, '  ', ' '))
    end 
 set @nombre_contains = (select replace(@nombre, ' ' , ' NEAR '))
 --select @dni
set @busqueda_cuit = 0
set @busqueda_dni  = 0

/* Busqueda por CUIT */
if (@p_cuit is not null) 
begin 
 set @busqueda_cuit = (select count(*) 
                            from padrones 
                            where identificacion = @p_Cuit and habilitado = 1 and tipo_persona != 'J' 
                                and identificador in (select identificador from identificadores where country_code = 'AR'))
 if( @busqueda_cuit > 0)
 begin
  set @error_cuit = 'true'
  if (select COUNT(*) from padrones 
    where identificacion = @p_Cuit and habilitado = 1 and tipo_persona != 'J' 
    and (cuerpo_clave = @DNI or cuerpo_clave = @DNI2) 
    and identificador in (select identificador from identificadores where country_code = 'AR')) > 0 
    set @error_dni = 'true'
 end 
end 

/* Busqueda por DNI (si no vino el cuit, o no lo encontro) */
if (@busqueda_cuit = 0 and @DNI is not null)
begin 
 set @busqueda_dni =  (select count(*) 
                            from padrones 
                            where (cuerpo_clave = @DNI or cuerpo_clave = @DNI2) 
         and habilitado = 1 and tipo_persona != 'J' 
                              and identificador in (select identificador from identificadores where country_code = 'AR'))
 if(@busqueda_dni = 1)
 begin
  set @p_cuit = (select identificacion 
        from padrones 
        where (cuerpo_clave = @DNI or cuerpo_clave = @DNI2) 
          and habilitado = 1 and tipo_persona != 'J' 
          and identificador in (select identificador from identificadores where country_code = 'AR'))
  set @error_dni = 'true'
 end 
 if(@busqueda_dni > 1)
 begin
  set @p_cuit = (select top 1 identificacion 
        from padrones 
        where (cuerpo_clave = @DNI or cuerpo_clave = @DNI2) 
          and habilitado = 1 and tipo_persona != 'J' 
          and contains(nombre_persona, @nombre_contains) 
          and identificador in (select identificador from identificadores where country_code = 'AR')) 
  set @error_dni = 'true'
 end 
end

if((@busqueda_cuit > 0 or @busqueda_dni > 0) and @p_cuit is not null)

begin

/* Sexo */

 declare @sexo char(1) =  (select distinct top 1 sexo 
                                from padrones 
                               where identificacion = @p_Cuit and habilitado = 1 and tipo_persona != 'J' 
                                 and identificador in (select identificador from identificadores where country_code = 'AR')
          and isnull(sexo, '') != '')
 if (isnull(@sexo,'') = '') 
 begin
  set @error_gender = 'true'
 end 
 else 
 begin
  if (@sexo = @gender)
   begin
    set @error_gender = 'true'
   end 
 end
         
    
/* Nombre */


    set @busqueda_igual = (select count(distinct nombre_persona) 
                            from padrones 
                            where identificacion = @p_Cuit and habilitado = 1 and tipo_persona != 'J'
                                and nombre_persona = @nombre 
                                and identificador in (select identificador from identificadores where country_code = 'AR'))
    if( @busqueda_igual > 0)
    begin
        set @error_name = 'true'
        set @error_name_contains = 'true'
    end
    else 
    begin 
        set @busqueda_contains = (select count(distinct nombre_persona) 
                              from padrones 
           where identificacion = @p_Cuit  
             and habilitado = 1 
          and tipo_persona != 'J'  
          and contains(nombre_persona, @nombre_contains) 
          and identificador in(select identificador from identificadores where country_code = 'AR'))
        if(@busqueda_contains > 0)
        begin
            set @error_name = 'true'
   set @error_name_contains = 'partially true'
        end 
    end 

/* Domicilio */
    declare @domicilio varchar(200)
    declare @codpos    varchar(20)
 BEGIN TRANSACTION
 insert into @domicilios 
 exec sp_obtener_domicilios @p_cuit 
 COMMIT TRANSACTION

    DECLARE dom CURSOR FOR
            select distinct upper(domicilio),
                            codpos
                from @domicilios 
    OPEN dom
    FETCH NEXT FROM dom INTO @domicilio,
                             @codpos
    WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
        exec @resp = [sp_comparo_domicilios] @domicilio,  
                                             @StreetAddress
         
        if (@resp = 1) 
        begin
            set @error_address = 'true'
            if (lower(ltrim(rtrim(@codpos))) = lower(ltrim(rtrim(@ZipCode))) )
      begin
                set @error_zipcode = 'true'
            end
            BREAK
        end  
  FETCH NEXT FROM dom INTO @domicilio,
                                 @codpos
    END 
 CLOSE dom
 DEALLOCATE dom
 delete from @domicilios

/* Fecha de Nacimiento */ 
 declare @fecha_nac date = (select c02_fecha_nac from datos_personas where c01_cuit = @p_cuit) 
 if (@fecha_nac is null and @DateOfBirth is null) 
 begin
  set @error_nac = 'true'
 end 
 else 
 begin
  if (@fecha_nac = @DateOfBirth)
   begin
    set @error_nac = 'true'
   end 
 end

/* Fecha de Fallecimiento */ 
 declare @fecha_defuncion date = (select fecha_defuncion from datos_personas where c01_cuit = @p_cuit) 
 if (@fecha_defuncion = '') set @fecha_defuncion = null
 if ((@fecha_defuncion is null and @FechaFallecimiento is null)
     or  
  (@fecha_defuncion = @FechaFallecimiento))
 begin
  set @error_fall = 'true'
 end 
  
end 


insert into @resultado
SELECT   @Reference as reference, 
@p_cuit as nuestro_cuit, 
      @FirstName       as FirstName, 
      @SurName         as SurName,
      @Gender          as Gender,
      @StreetAddress   as StreetAddress, 
      @ZipCode         as ZipCode, 
      @DateOfBirth     as DateOfBirth, 
      @IDNumber        as IDNumber, 
   @fecha_defuncion as Fecha_Defuncion, 
   @DNI      as DNI,
   @error_name      as error_Name,
   @error_name_contains  as error_Name_contains,
   @error_gender    as error_Gender, 
   @error_address   as error_Address,
   @error_zipcode   as error_Zipcode, 
   @error_nac       as error_DateOfBirth, 
   @error_cuit      as error_IDNumber,
   @error_dni       as error_DNI,
   @error_fall      as error_Defuncion,


   'Ok'           as financial_status,
      GETDATE()      as request_date,
      '20000'        as transaction_id,
      1              as transaction_status,
      '2018'         as period_year,
      '10'           as period_month,
      '100'          as period_count

  FETCH NEXT FROM C_busqueda INTO  @Reference, 
            @FirstName,
         @SurName,
         @Gender,
         @StreetAddress,
         @ZipCode,
         @DateOfBirth,
         @IDNumber,
         @FechaFallecimiento,
         @DNI
 END 
 CLOSE C_busqueda
 DEALLOCATE C_busqueda 

 select Reference, 
     isnull(FirstName,'') as 'FirstName', 
  isnull(Surname,'') as 'SurName',
  --isnull(Gender,'') as 'Gender',
  --isnull(StreetAddress,'') as 'Address', 
  --isnull(ZipCode,'') as 'ZipCode', 
  --isnull(convert(varchar,DateOfBirth),'') as 'DateOfBirth',
  isnull(IDNumber,'') as 'IDNumber', 
  isnull(convert(char(8),DNI),'') as 'DNI',
  isnull(convert(varchar,FechaFallecimiento),'') as 'FechaFallecimiento', 
  UPPER(error_cuit)          as 'Error TAX-ID',
  UPPER(error_dni)     as 'Error DNI',
  UPPER(error_name_contains) as 'Error name',
  --UPPER(error_gender)      as 'Error Gender',
  UPPER(error_address)       as 'Error Address',
  UPPER(error_zipcode)       as 'Error Zipcode',
  UPPER(error_nac)     as 'Error DOB',

  UPPER(error_fall)     as 'Error Defunción'   
   from @resultado 

END 
GO