IF (Select Count(*) from @dt) = 0 BEGIN insert into @resumeIds select refnumber from resumemaster (nolock) where loginId=@candidateId and cvenable=1 END ELSE BEGIN insert into @resumeIds select Id from @dt END DECLARE @CurrentDate date = CONVERT(DATE,GETDATE())
IF @Take>50 BEGIN SET @Take = 50 END
;with cteApplyListJoinResume as ( SELECT TOP(@Take) * --into #ApplyListJoinResume FROM ( SELECT COUNT(a.Dummyid ) OVER() [Total], ROW_NUMBER() OVER(ORDER BY a.last_updated DESC) AS RowNumber, a.Dummyid AS Id, a.PostingUser AS EmployerId, a.PostingDateN AS AppliedDate, a.JobRefNo AS JobId, a.ClientJobRefNo As ClientJobrefNo, c.CvName AS CvName, c.Refnumber AS CvId, a.loginId, (select top 1 cl.coverletter from CoverLetterDB cl with (nolock) where cl.Dummyid=a.Dummyid And cl.ALoginId=a.loginId) AS CoverLetter, a.last_updated AS LastApplyDate, (CASE WHEN a.trash IS NULL OR a.trash = 0 THEN 0 ELSE 1 END) AS IsArchived, (CASE WHEN a.CV_Lang = 'N' THEN 1 ELSE 0 END) as PullBackApply, a.ApplicationDeleteCount -- Başvurunun silinme sayısı --0 AS ApplicationDeleteCount -- Başvurunun silinme sayısı FROM adaylogdb a WITH (NOLOCK) INNER JOIN ResumeMaster c WITH (NOLOCK) ON a.AdayRefNumber = c.Refnumber INNER JOIN jobsdb b WITH (NOLOCK) ON a.PostingUser = b.PostingUser AND a.ClientJobRefNo = b.ClientJobRefNo WHERE ISNULL(a.trash,'0') = @SearchType and isnull(a.Silindi,0)=0 AND a.LoginId = @CandidateId AND a.FirmaEkledi = 0 AND a.adayRefnumber IN (select Id from @resumeIds) and c.CvEnable=1 and (b.EmployerInfo like '%'+@Keyword+'%' or b.DigerPozisyon like '%'+@Keyword+'%')) AS Result WHERE RowNumber > @Skip ) SELECT ROW_NUMBER() OVER(ORDER BY a.LastApplyDate DESC) AS RowNumber, a.Id AS Id, a.EmployerId AS EmployerId, b.EmployerInfo AS EmployerName, b.DigerPozisyon AS Position, CASE WHEN b.Conf=1 then '' else b.Client_Logo end as EmployerLogo, jpd.PozisyonNo AS PositionId, mt.Baslik AS MessageTitle, m.MesajHareketID AS MessageId, m.GonderimTarihi AS SentDate, m.OkunmaTarihi AS ReadDate, CASE WHEN ((CASE WHEN ISDATE(B.ClosingDateN)=1 THEN CONVERT(DATE, CONVERT(varchar,CONVERT(int,B.ClosingDateN)), 0) ELSE @CurrentDate END) >= @CurrentDate AND CONVERT(DATE, CONVERT(varchar,CONVERT(int,B.PostingDateN)), 0) <= @CurrentDate AND B.aktif = 1) THEN 1 else 0 end AS IsActive , b.ClosingDateN As ClosingDate, b.iller AS Cities, a.AppliedDate AS AppliedDate, a.JobId AS JobId, a.ClientJobRefNo As ClientJobrefNo, b.Conf AS IsPrivate, js.Status AS ClosedStatus, a.CvName AS CvName, a.CvId AS CvId, (select top 1 cl.coverletter from CoverLetterDB cl with (nolock) where cl.Dummyid=a.Id And cl.ALoginId=a.loginId) AS CoverLetter, a.LastApplyDate AS LastApplyDate, b.PostingUser AS FirmaNo, b.YayinTarihi AS FirstPublicationDate, a.Total AS TotalApplicationCount, a.ApplicationDeleteCount, -- Başvurunun silinme sayısı CONVERT(bit,a.IsArchived) as IsArchived, CONVERT(bit,a.PullBackApply) as PullBackApply, CONVERT(bit,(CASE WHEN tmpM.CandidateHasUnreadReadMessage > 0 AND tmpM.mID > 0 THEN 1 ELSE 0 END)) AS CandidateHasUnreadReadMessage, jd.JobAplicationViewDate AS JobApplicationViewDate, (SELECT COUNT(*) FROM ShowCvDB shw (nolock) left join TestClient tc (nolock) on shw.PostingUser=tc.PostingUser WHERE shw.AdayRefNumber =a.CvId and tc.PostingUser is null and shw.Postinguser=a.EmployerId and shw.AdayRefNumber=a.CvId and shw.ClientJobrefno=a.ClientJobRefNo) as incelenmeSayisi, CASE WHEN (SELECT COUNT(*) FROM ShowCvDB shw (nolock) left join TestClient tc (nolock) on shw.PostingUser=tc.PostingUser WHERE shw.AdayRefNumber =a.CvId and tc.PostingUser is null and shw.Postinguser=a.EmployerId and shw.AdayRefNumber=a.CvId and shw.ClientJobrefno=a.ClientJobRefNo)>0 THEN 'goruntulendi' WHEN(select count(*) from FiltrelemeAdayFirmaLogDB faf (nolock) left join TestClient tc (nolock) on faf.PostingUser=tc.PostingUser where faf.refnumber=a.CvId and faf.Postinguser=a.EmployerId and faf.ClientJobrefno=a.ClientJobRefNo and isnull(faf.clientjobrefno,'')<>'' and tc.PostingUser is null )>0 THEN 'listelendi' ELSE 'iletildi' END as ApplyStatus -- FROM #ApplyListJoinResume a WITH (NOLOCK) FROM cteApplyListJoinResume a WITH (NOLOCK) INNER JOIN jobsdb b WITH (NOLOCK) ON a.EmployerId = b.PostingUser AND a.ClientJobRefNo = b.ClientJobRefNo LEFT JOIN JobsDetailDB jd WITH (NOLOCK) ON jd.JobRefNo1 = b.JobRefNo LEFT JOIN JobsPozisyonDB jpd WITH (NOLOCK) ON jpd.ilanNo = b.JobRefNo LEFT JOIN (SELECT MAX(mesajHareketID) AS mID,postinguser,clientjobrefno,SUM(CASE WHEN OkunmaTarihi IS NULL THEN 1 ELSE 0 END) AS CandidateHasUnreadReadMessage FROM MesajHareketDB WITH (NOLOCK) WHERE MesajHareketDB.ALoginID=@CandidateId AND MesajHareketDB.Clientjobrefno IS NOT NULL GROUP BY postinguser,clientjobrefno) tmpM ON a.EmployerId=tmpM.postinguser AND a.ClientJobRefNo=tmpM.clientjobrefno LEFT JOIN MesajHareketDB m WITH (NOLOCK) ON m.MesajHareketID=tmpM.mID LEFT JOIN MesajTanimDB mt WITH (NOLOCK) ON m.MesajID=mt.MesajID AND m.Postinguser=mt.Postinguser LEFT JOIN JobClosedStatus js with (nolock) on js.Id = b.pasif_neden OPTION (OPTIMIZE FOR (@CandidateId=18050126)) END
