168 lines
13 KiB
SQL
168 lines
13 KiB
SQL
SELECT c.Id, c.PiyangoId, c.Baslik, ISNULL(c.CreateDate, '0001-01-01') AS OlusturmaTarihi, MIN(ISNULL(cb.BaslangicTarihi, '0001-01-01')) AS BaslangicTarihi, MIN(ISNULL(cb.BitisTarihi, '0001-01-01')) AS BitisTarihi,
|
||
|
||
/* DÜZENLEYEN (mevcut)*/ COALESCE (NULLIF (d .DernekUnvan, ''), NULLIF (d .GercekTicariUnvan, ''), NULLIF (d .kamuUnvan, ''), NULLIF (d .SirketUnvan, ''), NULLIF (d .TuzelUnvan, ''), LTRIM(RTRIM(COALESCE (d .GercekAdi,
|
||
|
||
'') + ' ' + COALESCE (d .GercekSoyadi, ''))), iblist.IrtibatAdSoyad, 'Bilinmiyor') AS Duzenleyen, ISNULL(d .Id, 0) AS DuzenleyenId,
|
||
|
||
/* MÜDÜRLÜK (yeni - 2. başlık)*/
|
||
CASE
|
||
WHEN odlast.Mudurluk = 1 THEN '1 No''lu Özel Çekilişler İzin ve Takip Şubesi Müdürlüğü'
|
||
WHEN odlast.Mudurluk = 2 THEN '2 No''lu Özel Çekilişler İzin ve Takip Şubesi Müdürlüğü'
|
||
WHEN odlast.Mudurluk = 3 THEN '3 No''lu Özel Çekilişler İzin ve Takip Şubesi Müdürlüğü'
|
||
ELSE ISNULL(birim.BirimAdi, '')
|
||
END AS Mudurluk,
|
||
|
||
MIN(cy.Deger) AS CekilisYontemi, MIN(cb.CekilisYontemiId) AS CekilisYontemiId, MIN(cb.KatilimciListesiTeslimYontemiId) AS KatilimciListesiTeslimYontemiId, MIN(klt.Deger) AS KatilimciListesiTeslimYontemi, ik.Ikramiyeler,
|
||
|
||
ISNULL(odlast.OnayDurumuIslemTipiId, 0) AS DurumId, ISNULL(odlast.IslemTipi, 'Yeni Piyango') AS Durum, MIN(ISNULL(cb.CekilisTarihi, '0001-01-01')) AS CekilisTarihi, ap.AmacAdi AS piyangoamac,
|
||
|
||
c.amacpiyangoId AS piyangoAmacId, ISNULL(oc.OnayVerecekKisi1Id, 0) AS OnayVerecekKisi1Id, ISNULL(oc.OnayVerecekKisi2Id, 0) AS OnayVerecekKisi2Id, ISNULL(oc.OnayVerecekKisi3Id, 0) AS OnayVerecekKisi3Id,
|
||
|
||
c.izinBedelNo, c.BasvuruBedelNo, c.izinBedeliDurum, c.basvuruBedeliDurum, c.baglisirketId, /* Atanmis (mevcut)*/ CASE WHEN ISNULL(oc.OnayVerecekKisi1Id, 0) > 0 OR
|
||
|
||
ISNULL(oc.OnayVerecekKisi2Id, 0) > 0 OR
|
||
|
||
ISNULL(oc.OnayVerecekKisi3Id, 0) > 0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS Atanmis, ISNULL(atananlar.AlananIsimler, '') AS Atananlar, /* ARACI FİRMA (yeni - 5. başlık)*/ CASE WHEN EXISTS
|
||
|
||
(SELECT 1
|
||
|
||
FROM AspNetUserRoles ur INNER JOIN
|
||
|
||
AspNetRoles r ON ur.RoleId = r.Id
|
||
|
||
WHERE ur.UserId = d .ParentUserId AND r.Name = 'AraciFirma') THEN COALESCE (NULLIF (parentUser.DernekUnvan, ''), NULLIF (parentUser.GercekTicariUnvan, ''), NULLIF (parentUser.kamuUnvan, ''),
|
||
|
||
NULLIF (parentUser.SirketUnvan, ''), NULLIF (parentUser.TuzelUnvan, ''), LTRIM(RTRIM(COALESCE (parentUser.GercekAdi, '') + ' ' + COALESCE (parentUser.GercekSoyadi, ''))), '') ELSE '' END AS AraciFirma,
|
||
|
||
/* İZİN TARİHİ (yeni - 7. başlık)*/ odlast.IzinTarihi AS IzinTarihi, /* İZİN SAYISI (yeni - 8. başlık)*/ odlast.IzinSayisi AS IzinSayisi,
|
||
|
||
/* ÇEKİLİŞ GÖREVLİSİ (yeni - 12. başlık)*/
|
||
CASE
|
||
WHEN odlast.CekilisGorevlisi = 1 THEN 'Antalya Şube Müdürlüğü'
|
||
WHEN odlast.CekilisGorevlisi = 2 THEN 'Gaziantep Şube Müdürlüğü'
|
||
WHEN odlast.CekilisGorevlisi = 3 THEN 'Aksaray Şube Müdürlüğü'
|
||
WHEN odlast.CekilisGorevlisi = 4 THEN 'Kadıköy Şube Müdürlüğü'
|
||
WHEN odlast.CekilisGorevlisi = 5 THEN 'Karşıyaka Şube Müdürlüğü'
|
||
WHEN odlast.CekilisGorevlisi = 6 THEN 'Muğla Şube Müdürlüğü'
|
||
WHEN odlast.CekilisGorevlisi = 7 THEN 'Trabzon Şube Müdürlüğü'
|
||
WHEN odlast.CekilisGorevlisi = 8 THEN 'Noter'
|
||
WHEN odlast.CekilisGorevlisi = 9 THEN 'Başkanlık Personeli'
|
||
ELSE COALESCE (LTRIM(RTRIM(COALESCE (personel.GercekAdi, '') + ' ' + COALESCE (personel.GercekSoyadi, ''))), LTRIM(RTRIM(COALESCE (birimSefi.GercekAdi, '') + ' ' + COALESCE (birimSefi.GercekSoyadi, ''))), '')
|
||
END AS CekilisGorevlisi,
|
||
|
||
/* GAZETE İLAN TARİHİ (yeni - 13. başlık)*/ MIN(cb.ilantarihi) AS GazeteIlanTarihi, /* GAZETE ADI (yeni - 14. başlık)*/ MIN(cb.ilanEdilecekGazete)
|
||
|
||
AS GazeteAdi, /* KDV Oranı = 0.20 (sabit)*/ CASE WHEN ikramiyeTutar.ToplamTutar IS NOT NULL AND izinBedelOrani.Oran IS NOT NULL THEN CAST((ikramiyeTutar.ToplamTutar * izinBedelOrani.Oran / 100.0)
|
||
|
||
* 1.20 AS DECIMAL(18, 2)) ELSE NULL END AS IzinBedeliTutari, /* İKRAMİYE TUTARI (yeni - 16. başlık)*/ ISNULL(ikramiyeTutar.ToplamTutar, 0) AS IkramiyeTutari,
|
||
|
||
/* TEMİNAT TARİHİ (yeni - 17. başlık)*/ ts .TeminantDate AS TeminatTarihi, /* TEMİNAT SAYISI (yeni - 18. başlık)*/ ts .TeminantNo AS TeminatSayisi, /* TEMİNAT BANKASI (yeni - 19. başlık)*/ ts .BankName AS TeminatBankasi,
|
||
|
||
/* TEMİNAT TUTARI (yeni - 20. başlık)*/ ts .Amount AS TeminatTutari, /* T. PARA CİNSİ (yeni - 21. başlık)*/ ISNULL(paraBirimi.ParaBirimiSembol, '') AS ParaBirimiSembol,
|
||
|
||
/* T. İADE TARİHİ (yeni - 22. başlık)*/ ts .RefundDate AS TeminatIadeTarihi, /* T. İADE SAYISI (yeni - 23. başlık)*/ ts .RefundCount AS TeminatIadeSayisi
|
||
|
||
FROM Cekilisler c LEFT JOIN
|
||
|
||
CekilisBilgiler cb ON cb.CekilisId = c.Id LEFT JOIN
|
||
|
||
AspNetUsers d ON d .Id = c.DuzenleyenId LEFT JOIN
|
||
|
||
Birimler birim ON birim.Id = d .BirimId /* MÜDÜRLÜK için*/ LEFT JOIN
|
||
|
||
AspNetUsers parentUser ON parentUser.Id = d .ParentUserId /* ARACI FİRMA için*/ LEFT JOIN
|
||
|
||
AspNetUsers personel ON personel.Id = c.PersonelId /* ÇEKİLİŞ GÖREVLİSİ için*/ LEFT JOIN
|
||
|
||
AspNetUsers birimSefi ON birimSefi.Id = c.BirimSefiId /* ÇEKİLİŞ GÖREVLİSİ için*/ LEFT JOIN
|
||
|
||
PiyangoAmacs ap ON ap.Id = c.amacpiyangoId LEFT JOIN
|
||
|
||
OnayCekilisler oc ON oc.CekilisId = c.Id LEFT JOIN
|
||
|
||
CekilisYontemleri cy ON cb.CekilisYontemiId = cy.Id LEFT JOIN
|
||
|
||
KatilimciListesiTeslimYontemleri klt ON cb.KatilimciListesiTeslimYontemiId = klt.Id /* APPLY ile son OnayDurumu (İZİN TARİHİ, İZİN SAYISI, MÜDÜRLÜK ve ÇEKİLİŞ GÖREVLİSİ dahil)*/ OUTER APPLY
|
||
|
||
(SELECT TOP 1 od.OnayDurumuIslemTipiId, od.IslemTipi, od.IzinTarihi, od.IzinSayisi, od.Mudurluk, od.CekilisGorevlisi
|
||
|
||
FROM OnayCekilisler oc2 INNER JOIN
|
||
|
||
OnayDurumlari od ON oc2.Id = od.OnayCekilisId
|
||
|
||
WHERE oc2.CekilisId = c.Id
|
||
|
||
ORDER BY od.Tarih DESC) odlast /* APPLY ile ikramiyeler string*/ OUTER APPLY
|
||
|
||
(SELECT STUFF
|
||
|
||
((SELECT ', ' + i2.Marka + ' ' + i2.Model
|
||
|
||
FROM Ikramiyeler i2
|
||
|
||
WHERE i2.CekilisId = c.Id FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')) ik(Ikramiyeler) /* APPLY ile ikramiye tutarı toplamı*/ OUTER APPLY
|
||
|
||
(SELECT SUM(i3.BirimFiyat * i3.AsilTalihliAdedi) AS ToplamTutar
|
||
|
||
FROM Ikramiyeler i3
|
||
|
||
WHERE i3.CekilisId = c.Id) ikramiyeTutar /* APPLY ile aktif izin bedel oranı (ilk aktif oran)*/ OUTER APPLY
|
||
|
||
(SELECT TOP 1 ibo.Oran
|
||
|
||
FROM IzinBedelOranlari ibo
|
||
|
||
WHERE ibo.Aktif = 1
|
||
|
||
ORDER BY ibo.Id) izinBedelOrani /* APPLY ile para birimi sembolü (ilk ikramiyenin para birimi)*/ OUTER APPLY
|
||
|
||
(SELECT TOP 1 pb.ParaBirimiSembol
|
||
|
||
FROM Ikramiyeler i4 LEFT JOIN
|
||
|
||
ParaBirimleri pb ON pb.Id = i4.ParaBirimiId
|
||
|
||
WHERE i4.CekilisId = c.Id AND pb.ParaBirimiSembol IS NOT NULL
|
||
|
||
ORDER BY i4.Id) paraBirimi /* APPLY ile irtibat bilgileri string*/ OUTER APPLY
|
||
|
||
(SELECT STUFF
|
||
|
||
((SELECT ', ' + ib2.AdSoyad
|
||
|
||
FROM IrtibatBilgiler ib2
|
||
|
||
WHERE ib2.CekilisId = c.Id FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')) iblist(IrtibatAdSoyad) /* APPLY ile atanan kullanıcı isimleri*/ OUTER APPLY
|
||
|
||
(SELECT STUFF
|
||
|
||
((SELECT ', ' + u.GercekAdi + ' ' + u.GercekSoyadi
|
||
|
||
FROM AspNetUsers u
|
||
|
||
WHERE u.Id IN (ISNULL(oc.OnayVerecekKisi1Id, 0), ISNULL(oc.OnayVerecekKisi2Id, 0), ISNULL(oc.OnayVerecekKisi3Id, 0)) FOR XML PATH(''), TYPE ).value('.',
|
||
|
||
'NVARCHAR(MAX)'), 1, 2, '')) atananlar(AlananIsimler) /* APPLY ile teminat bilgileri (son teminat kaydı)*/ OUTER APPLY
|
||
|
||
(SELECT TOP 1 ts2.TeminantDate, ts2.TeminantNo, ts2.BankName, ts2.Amount, ts2.RefundDate, ts2.RefundCount
|
||
|
||
FROM TeminantStates ts2
|
||
|
||
WHERE ts2.CekilisId = c.Id
|
||
|
||
ORDER BY ts2.Id DESC) ts
|
||
|
||
GROUP BY c.Id, c.PiyangoId, c.Baslik, c.CreateDate, d .DernekUnvan, d .GercekTicariUnvan, d .kamuUnvan, d .SirketUnvan, d .TuzelUnvan, d .GercekAdi, d .GercekSoyadi, d .Id, d .ParentUserId,
|
||
|
||
birim.BirimAdi, ap.AmacAdi, c.amacpiyangoId, oc.OnayVerecekKisi1Id, oc.OnayVerecekKisi2Id, oc.OnayVerecekKisi3Id, c.izinBedelNo, c.BasvuruBedelNo, c.izinBedeliDurum,
|
||
|
||
c.basvuruBedeliDurum, c.baglisirketId, ik.Ikramiyeler, iblist.IrtibatAdSoyad, odlast.OnayDurumuIslemTipiId, odlast.IslemTipi, odlast.IzinTarihi, odlast.IzinSayisi,
|
||
|
||
atananlar.AlananIsimler, personel.GercekAdi, personel.GercekSoyadi, birimSefi.GercekAdi, birimSefi.GercekSoyadi, parentUser.DernekUnvan, parentUser.GercekTicariUnvan,
|
||
|
||
parentUser.kamuUnvan, parentUser.SirketUnvan, parentUser.TuzelUnvan, parentUser.GercekAdi, parentUser.GercekSoyadi, ikramiyeTutar.ToplamTutar, izinBedelOrani.Oran,
|
||
|
||
paraBirimi.ParaBirimiSembol, ts .TeminantDate, ts .TeminantNo, ts .BankName, ts .Amount, ts .RefundDate, ts .RefundCount, odlast.Mudurluk, odlast.CekilisGorevlisi;
|
||
|
||
|