USE [hrp.229.PM]
GO
/****** Object:  StoredProcedure [dbo].[GetMapalEmployeesToRegister]    Script Date: 12-08-2019 12:18:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[GetMapalEmployeesToRegister]
as
    begin
    declare @maxDays int = 0
    select @maxDays = dbo.GetSetting(null,'Mapal.Sync.Delta')

    select top 10
        c.Id as ContractId, c.IdentificationNumber, p.LastName, p.FirstName, case when gender.Code='M' then 'M' when gender.Code='F' then 'H' else '0' end as Gender,
        convert(nvarchar(20),p.BirthDate,120) as BirthDate, p.PersonalNumber, dbo.fnGetWorkingHoursPerDay(cwp.WorkingHours, cwp.WorkingHoursType) as ContractWorkingHours, convert(nvarchar(20),c.StartDate,120) as StartDate,
        cc.Code as ExternalUnitId, d.Name as Category
    from
        Contract c
        inner join dbo.Person p on p.Id = c.PersonId
        inner join Metadata.EnumerationValue gender on gender.Id = p.Gender
        cross apply dbo.fnGetContractDetails_ContractWorkProgram(c.Id,getdate()) details_cwp
        cross apply dbo.fnGetContractDetails_ContractOrganizationalAssignment(c.Id,getdate()) details_coa
        inner join dbo.ContractWorkProgram cwp on cwp.id = details_cwp.Id
        inner join dbo.ContractOrganizationalAssignment coa on coa.Id = details_coa.Id
        left join dbo.CostCenter cc on cc.Id = coa.CostCenterId
        left join dbo.Domain d on d.Id = coa.DomainId
    where
        dbo.fnIsActiveContract(c.Id,getdate(),dateadd(day,@maxDays,getdate())) = 1 and
        (not exists(select * from dbo.ContractCustomFieldValue pcfv inner join dbo.CustomField cf on cf.Id = pcfv.CustomFieldId where cf.Name='MapalUserId' and pcfv.ContractId = c.Id)
        --or exists(select * from dbo.PersonCustomFieldValue pcfv inner join dbo.CustomField cf on cf.Id = pcfv.CustomFieldId where cf.Name='MapalUserState' and pcfv.PersonId = p.Id and pcfv.TextValue = 'deleted')
        )
        and c.StartDate >='2019-07-29 00:00:00'
    order by c.Date desc
end