Notes

USE [MCDONALDS13906_Meyer]
GO
/****** Object: StoredProcedure [dbo].[sp_PDKS1230_rpt] Script Date: 5/31/2021 3:07:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 1 2 3 4 5 6 7 8 9
--Sicil No Personel "Normal Çalışma
--Maliyet" "Hafta Tatili
--Maliyet" "Resmi Tatil
--Maliyet" "Yıllık İzin
--Maliyet" "Ücretli İzin
--Maliyet" "SGK İşveren
--Maliyet" "Fazla Mesai
--Maliyet" "Tatil Fazla
--Mesai Maliyet" "Brüt
--Ücret
--(1+2+3+4+5+7+8)" Ücret Saati Bölüm

-- Personel Ücret Saat 13,08

-- "Normal Çalışma
--Maliyet" Personel Saatlik Ücret X Normal Çalışma Saati
-- "Hafta Tatili
--Maliyet" Personel Saatlik Ücret X Hafta Tatili Çalışma Saati "Not: Bu raporda işyerinde çalışan personellerin filtrede girilen tarih aralığındaki toplam çalışma
--maliyetini göstermelidir. Personeller alt alta sıralı şelikde gelmelidir. Rapor sonunda kolon toplamları yer almalıdır."
-- "Resmi Tatil
--Maliyet" Personel Saatlik Ücret X Resmi Tatil Çalışma Saati
-- "Yıllık İzin
--Maliyet" Personel Saatlik Ücret X Yıllık İzin Çalışma Saati
-- "Ücretli İzin
--Maliyet" Personel Saatlik Ücret X Ücretli İzin Çalışma Saati
-- "SGK İşveren
--Maliyet" Personel Saatlik Ücret X SGK İşveren Rapor Çalışma Saati
-- "Fazla Mesai
--Maliyet" Personel Saatlik Ücret X (Fazla Mesai Saat X 1.5)
-- "Tatil Fazla
--Mesai Maliyet" Personel Saatlik Ücret X Tatil Fazla Mesai Çalışma Saati
-- "Brüt
--Ücret" 1+2+3+4+5+6+7+8 kolonların toplamı
-- Ücret Saati Brüt Ücret / Personel Saatlik Ücret


-- exec [dbo].[sp_PDKS1230_rpt] '2021-05-01','2021-05-05','','','','6','','','','','','',''
ALTER PROCEDURE [dbo].[sp_PDKS1230_rpt]
@TarihBas datetime,
@TarihSon datetime,
@SicilNo nvarchar(20),
@Ad nvarchar(20),
@Soyad nvarchar(20),
@firma#cbo_firma nvarchar(1000),
@Bolum#cbo_bolum nvarchar(1000),
@Pozisyon#cbo_pozisyon nvarchar(1000),
@altfirma#cbo_altfirma nvarchar(1000),
@direktorluk#cbo_direktorluk nvarchar(1000),
@yaka#cbo_yaka nvarchar(1000),
@gorev#cbo_gorev nvarchar(1000),
@islemno nvarchar(200)



-- select * From requestinfo order by zaman desc

AS
BEGIN

set nocount on;

set datefirst 1
--set language Turkish
declare @xsicilid int
declare @loginid int
declare @logintime datetime
declare @expiretime datetime
declare @loginname nvarchar(150)
declare @adet as int

select @loginid = loginid, @logintime = logintime, @expiretime = dateadd(second,60,logintime) from sessioninfo with (nolock) where tokenid = (select tokenid from requestinfo with (nolock) where islemno = @islemno)
select @xsicilid=sicil.Id , @loginname = isnull(sicil.ad + ' ' + sicil.soyad,loginname) from loginangel left outer join sicil on loginangel.xSicilID = sicil.Id where loginangel.Id=@loginid

select @TarihBas= cast(convert(nvarchar,@TarihBas,112)as smalldatetime)
select @TarihSon= cast(convert(nvarchar,@TarihSon,112)as smalldatetime)
declare @takvim int
select @takvim=datediff(day,@TarihBas,@TarihSon) + 1
DECLARE @saatlikOdeme float=13.08
------------------------
select
s.ID SicilID
--,REPLICATE('0',4- LEN(SicilNo))+SicilNo SicilNo select * from ent_personel where name like '%yusuf%'0.01
,SicilNo
, s.Ad
, Soyad
,y.Ad yaka
,y.Id YakaId
,b.Ad Bolum
,max(case when s.okod14=3 then cast(s.Okod15 as decimal(10, 2)) when s.okod14=1 and s.Okod15 not like '%.%' then cast(cast(nullif(s.Okod15,0)as decimal(10, 4) )/cast (225 as decimal(10, 4))as decimal(10, 4)) else 0.01 end) SaatlikUcret

--, 13.08 SaatlikUcret
--,CONVERT(NUMERIC(38,0),CAST(EP.Wage AS FLOAT)) SaatlikUcret --select * from ent_personel
,sum(isnull(MesaiSuresi,0))MesaiSuresi
,sum(case when IzinTip=3 and Ucretli=1 then case when t.IzinSaat=0 and s.Yaka =6 then 300 else 450 end else 0 end)YILS
,sum(case when IzinTip=3 and IzinSaat = 0 and IzinSaat2 = 0 and Ucretli=1 then 1 else 0 end)YILG
--,sum(case when IzinTip=9 and Ucretli=1 then case when t.IzinSaat=0 then 450 else t.IzinSaat end else 0 end)Idari_Sosyal
,sum(case when IzinTip in(6,7,4,1) and Ucretli=1 then case when t.IzinSaat2=0 then case when s.Yaka=6 then 300 else 450 end else t.IzinSaat2 end else 0 end)Idari_Sosyal
,sum(case when IzinTip=9 and Ucretli=1 then case when t.IzinSaat=0 then case when s.Yaka=6 then 300 else 450 end else t.IzinSaat end else 0 end)IdariSaat

,sum(case when IzinTip=2 and T.Izinsaat2=0 and Ucretli=0 then 1 else 0 end)IstrahatGun
,sum(case when IzinTip=2 and Ucretli=0 then case when t.IzinSaat2=0 then 450 else t.IzinSaat2 end else 0 end)IstrahatSaat

,sum(case when IzinTip=10 and Ucretli=1 then 1 else 0 end)SgkRaporOdenenGun
,(sum(case when IzinTip=10 and Ucretli=1 then case when t.IzinSaat=0 then 450 else t.IzinSaat end else 0 end))SgkRaporOdenenSaat

,sum(case when IzinTip=12 and Ucretli=0 then 1 else 0 end)KCOGun
,(sum(case when IzinTip=12 and Ucretli=0 then case when t.IzinSaat2=0 then 450 else t.IzinSaat2 end else 0 end))KCOSaat

, sum(case when IzinTip in (2,16,4) and IzinTip>0 and IzinSaat = 0 and IzinSaat2 = 0 and Ucretli=0 then 1 else 0 end) SSKEG -- SSK Eksik Çalışma Nedeni
, sum(case when IzinTip in (2,16,4) and IzinTip>0 and IzinSaat = 0 and IzinSaat2 = 0 and Ucretli=0 then 1 else 0 end) IECN -- İşkur Eksik Çalışma Nedeni
, s.Ad+' '+s.Soyad AdSoyad
, sum(case when ((Mesaibas=MesaiBit and Hakedis=0) or ((Mesaibas<>MesaiBit) and GirisKayitTipi>0))
and t.ResmiTatil=0 and (IzinTip = 0 or (IzinTip>0 and (IzinSaat>0 or Izinsaat2>0))) then 1 else 0 end ) NG
--, sum(case when NormalMesai>0 then NormalMesai else 0 end ) NS
,
dbo.fn_CSaatTam( sum(isnull(t.NormalMesai,0))) NS
, sum(case when y.Id<>6 and ResmiTatil=0 and IzinTip=0 and tf.htsure>0 then 1 else 0 end) HFT -- select * from tasnif
, (sum(case when ResmiTatil=0 and IzinTip=0 and tf.htsure>0 then tf.htsure else 0 end)) HFTS
--, sum(case when ResmiTatil>0 and IzinTip=0 then 1 else 0 end) RT
--, sum(case when ResmiTatil>0 and IzinTip=0 then MesaiSuresi else 0 end) RTS
, (sum(case when ResmiTatil > 0 and (MesaiBas<>MesaiBit or EksikGunYuzde>0)
then case when tt.Tamgun = 1 then 1 else case
when MesaiBas < 13 * 60 and MesaiBit > 13 * 60 then 0.5
when MesaiBas < 13 * 60 and MesaiBit <= 13 * 60 then 0
when MesaiBas >= 13 * 60 AND MesaiBit > 13 * 60 then 1
end
end else 0 end)) RT

--, dbo.fn_CSaatTam(sum(case when ResmiTatil > 0 then case when tt.Tamgun = 1 and s.Yaka not in (4,6) then 450 when tt.Tamgun = 1 and s.Yaka=4 then 400 when tt.Tamgun = 1 and s.Yaka =6 then 300 else
--case
-- --when MesaiBas < 13 * 60 and MesaiBit > 13 * 60 then MesaiBit - 13 * 60
-- --when MesaiBas < 13 * 60 and MesaiBit <= 13 * 60 then 0
-- --when MesaiBas >= 13 * 60 AND MesaiBit > 13 * 60 then 450
-- when s.Yaka=6 then 150
-- when s.Yaka=4 then 200
-- when s.Yaka not in(4,6)then 225
-- end
--end else 0 end)) RTS

-- , sum(case when
-- tf.rtfm > 0 and s.Yaka=4 then 450
--when tf.rtfm > 0 and s.Yaka=6 and tt.Tamgun=1 then 300
--when tf.rtfm > 0 and s.Yaka not in (4,6) and tt.Tamgun=1 then 450
--when tf.rtfm > 0 and s.Yaka not in (4,6) and tt.Tamgun=0 then 225
--when tf.rtfm>225 and tt.Tamgun=0 and s.Yaka =4 then 225
--when tf.rtfm>150 and tt.Tamgun=0 and s.Yaka =6 then 150
--else

--tf.rtfm

--end) RTS

, sum(case when
tf.rtfm > 0 and s.Yaka=4 and tt.Tamgun=1 then 450
when tf.rtfm > 0 and s.Yaka=6 and tt.Tamgun=1 then 300
when tf.rtfm > 0 and s.Yaka not in (4,6) and tt.Tamgun=1 then 450
when tf.rtfm > 0 and s.Yaka not in (4,6) and tt.Tamgun=1 then 225
when tf.rtfm>225 and tt.Tamgun=0 and s.Yaka =4 then 225
when tf.rtfm>225 and tt.Tamgun=0 and s.Yaka =6 then 150
else

case when s.Yaka <> 6 and ResmiTatil > 0 then case when tt.tamgun = 1 then 450 else

case when s.Yaka <> 6 then 450 / 2 else 300 / 2 end

end else tf.rtfm end

end) RTS

, sum(case when
tf.rtfm > 0 and s.Yaka=4 and tt.Tamgun=1 then 450
when tf.rtfm > 0 and s.Yaka=6 and tt.Tamgun=1 then 300
when tf.rtfm > 0 and s.Yaka not in (4,6) and tt.Tamgun=1 then 450
when tf.rtfm > 0 and s.Yaka not in (4,6) and tt.Tamgun=0 then 225
when tf.rtfm>225 and tt.Tamgun=0 and s.Yaka =4 then 225
when tf.rtfm>225 and tt.Tamgun=0 and s.Yaka =6 then 150
else

tf.rtfm

end) RTS2

, sum(case when
tf.rtfm > 0 then nmsure
else

0

end) RTNM

, sum(case when IzinTip>0 and IzinTip not in(3) and IzinSaat = 0 and IzinSaat2 = 0 and Ucretli=1 then 1 else 0 end) UCL
,sum(case when t.IzinTip>0 and IzinTip not in(3,10,2) and t.Ucretli>0 then case when t.IzinSaat=0 and t.IzinSaat2=0 then case when s.Yaka=6 then 300 else 450 end else t.IzinSaat end else 0 end)UCLS
,sum(case when t.IzinTip>0 and IzinTip in(2) and t.Ucretli>0 then case when t.IzinSaat=0 and t.IzinSaat2=0 then case when s.Yaka=6 then 300 else 450 end else t.IzinSaat end else 0 end)UCLS2
, sum(case when IzinTip >0 and t.Izintip not in (2,12) and Izinsaat=0 and Izinsaat2=0 and Ucretli=0 then 1 else 0 end) UCZ
,sum(case when t.Izintip>0 and t.IzinSaat2 > 0 and t.Izintip not in (2,12) and t.Ucretli=0 then (Izinsaat2) else 0 end) -- Ücretsiz Saatlik İzin
+
(sum(case when t.Izintip>0 and t.IzinSaat2 =0 and t.Izintip not in (2,12) and t.Ucretli=0 then 450 else 0 end))UCZS --UcretsizIzinSaat,
,dbo.fn_CSaatTam(SUM(case when t.GeceVardiya>0 and t.HamFazlaMesai>0 then t.HamFazlaMesai else 0 end ))GVS
,(SUM(case when t.GeceVardiya>0 and t.HamFazlaMesai>0 then 1 else 0 end ))GV
, dbo.fn_CSaatTam(sum(case when mesaibas<>mesaibit and ResmiTatil=0 and GirisKayitTipi>0 and CikisKayitTipi>0 and Izinsaat2=0 then MesaiSuresi-(normalmesai+resmitatilmesai+(case when Izinsaat>0 and Ucretli=1 then Izinsaat else 0 end))else 0 end)) EM
, sum(case when y.Id=6 and m.MesaiBas <> m.MesaiBit and IzinTip = 0 and ResmiTatil = 0 and t.GirisKayitTipi=0 and [Index] < cast(convert(varchar,getdate(),112) as datetime) then 1 else 0 end) DEVG
, (sum(case when m.MesaiBas <> m.MesaiBit and IzinTip = 0 and ResmiTatil = 0 and t.GirisKayitTipi=0 and [Index] < cast(convert(varchar,getdate(),112) as datetime) then MesaiSuresi else 0 end)) DEVS

--, sum(case when OnaylananFazlaMesai > 0 and ((MesaiBas = MesaiBit and EksikGunYuzde > 0) or MesaiBas <> MesaiBit) and ResmiTatil = 0 then 1 else 0 end) FMHI
--, sum(
--case when t.NormalMesai+t.FazlaMesai>11*60 AND T.GeceVardiya+T.HamFazlaMesai=0 then ( t.NormalMesai+t.FazlaMesai)-11*60
-- when (T.GeceVardiya+T.HamFazlaMesai)>450 then ((T.GeceVardiya+T.HamFazlaMesai)-450)


-- else 0 end)
--+
--sum(case when OnaylananFazlaMesai > 0 and ResmiTatil > 0 and tt.Tamgun=1 and (t.FazlaMesai>450) and y.Id<>6 then 450- t.FazlaMesai else 0 end) -- Part time hariç -> Resmi tatil de fazla mesai 7.5 den fazla ise 7.5den fazla kısmını Hfataici Fm ye at.
--+
--sum(case when OnaylananFazlaMesai > 0 and ResmiTatil > 0 and tt.Tamgun=1 and (t.FazlaMesai>300) and y.Id=6 then 300- t.FazlaMesai else 0 end) -- Sadece part time Resmi tatil de fazla mesai 5 den fazla ise 5 den fazla kısmını Hfataici Fm ye at.
,( sum(case when tf.gz>0 then tf.gz else 0 end + case when tf.ustu11>0 then tf.ustu11 else 0 end ))FMHI
,dbo.fn_CSaatTam3(sum(case when tf.tsure=0 and tf.fm>0 then tf.fm else 0 end ))FMHTS
, dbo.fn_CSaatTam3(sum(case when OnaylananFazlaMesai > 0 and ResmiTatil > 0 then 1 else 0 end)) FMGTGun3

--, dbo.fn_CSaatTam3(sum(case when OnaylananFazlaMesai > 0 and ResmiTatil > 0 then case when y.Id<>6

--then case when OnaylananFazlaMesai>450 then 450 else OnaylananFazlaMesai end else

--case when OnaylananFazlaMesai>300 then 300 else OnaylananFazlaMesai end end else 0 end)) select * from tasnif
--FMGT3

--, dbo.fn_CSaatTam3(sum(case when OnaylananFazlaMesai > 0 and ResmiTatil > 0 then case when y.Id<>6

--then case when OnaylananFazlaMesai>450 then OnaylananFazlaMesai-450 else OnaylananFazlaMesai end else

--case when OnaylananFazlaMesai>300 then OnaylananFazlaMesai - 300 else OnaylananFazlaMesai end end else 0 end))
--FMGT3


, dbo.fn_CSaatTam3(sum(case when tf.rtfm>case when s.Yaka <> 6 then 450 else 5*60 end and tt.Tamgun =1
then tf.rtfm- case when s.Yaka <> 6 then 450 else 5*60 end
when tf.rtfm>225 and tt.Tamgun =0 and s.Yaka not in (4,6) then tf.rtfm - 225
when tf.rtfm>225 and tt.Tamgun =0 and s.Yaka=4 then tf.rtfm - 225
when tf.rtfm>150 and tt.Tamgun =0 and s.Yaka=6 then tf.rtfm - 150
else
0
end ))FMGT3


,sum(case when
tf.rtfm>case when s.Yaka <> 6 then 450 else 5*60 end and tt.Tamgun =1 -- select * from cbo_Yaka
then tf.rtfm- case when s.Yaka <> 6 then 450 else 5*60 end
when tf.rtfm>225 and tt.Tamgun =0 and s.Yaka not in (4,6) then tf.rtfm - 225
when tf.rtfm>225 and tt.Tamgun =0 and s.Yaka=4 then tf.rtfm - 225
when tf.rtfm>150 and tt.Tamgun =0 and s.Yaka=6 then tf.rtfm - 150
else
0
end) FM

--, sum(case when OnaylananFazlaMesai > 0 and ResmiTatil > 0 and tt.SabitTatil = 0 then 1 else 0 end) FMDTGun4
--, sum(case when OnaylananFazlaMesai > 0 and ResmiTatil > 0 and tt.SabitTatil = 0 then OnaylananFazlaMesai else 0 end) FMDT4

,sum(1)Gun


--, sum(case when s.Yaka = 6 then (nmsure - fm) else (nmsure ) end) nmsure
,sum(nmsure)nmsure

into #tmp
from dbo.Sicil s with(nolock)
--inner join [dbo].[mysicil](@xsicilid) my on my.LId=s.Id
inner join dbo.Tasnifleme t with(nolock) on t.SicilID=s.ID
left outer join dbo.Tasnif tf with(nolock) on tf.sicilid=t.SicilID and tf.mesaitarih=t.MesaiTarih
inner join dbo.Mesailer m with(nolock) on t.Mesaibirimi=m.ID
--inner join Ent_Personel ep with (nolock)on ep.Code=s.SicilNo
inner join dbo.cbo_Firma f with(nolock) on s.firma=f.ID and s.Firma= case when @firma#cbo_firma <> '' then case when s.Firma in(select cast(data as int) from dbo.string2table(@firma#cbo_firma ,',')) then s.Firma else -1 end else s.Firma end
inner join dbo.cbo_Bolum b with(nolock) on s.bolum=b.ID and s.bolum = case when @Bolum#cbo_bolum <> '' then case when s.bolum in(select cast(data as int) from dbo.string2table(@Bolum#cbo_bolum, ',')) then s.bolum else -1 end else s.bolum end
inner join dbo.cbo_pozisyon p with(nolock) on p.ID=s.pozisyon and s.pozisyon = case when @Pozisyon#cbo_pozisyon <> '' then case when s.pozisyon in(select cast(data as int) from dbo.string2table(@Pozisyon#cbo_pozisyon ,',')) then s.pozisyon else -1 end else s.pozisyon end
inner join dbo.cbo_altfirma af with(nolock) on af.ID=s.altfirma and s.AltFirma= case when @altfirma#cbo_altfirma <> '' then case when s.AltFirma in(select cast(data as int) from dbo.string2table(@altfirma#cbo_altfirma ,',')) then s.AltFirma else -1 end else s.AltFirma end
inner join dbo.cbo_Direktorluk d with(nolock) on d.ID=s.Direktorluk and s.Direktorluk= case when @direktorluk#cbo_direktorluk<> '' then case when s.Direktorluk in(select cast(data as int) from dbo.string2table(@direktorluk#cbo_direktorluk,',')) then s.Direktorluk else -1 end else s.Direktorluk end
inner join dbo.cbo_Yaka y with(nolock) on y.ID=s.Yaka and s.Yaka = case when @yaka#cbo_Yaka <> '' then case when s.Yaka in(select cast(data as int) from dbo.string2table(@yaka#cbo_Yaka,',')) then s.Yaka else -1 end else s.Yaka end
inner join dbo.cbo_Gorev g with(nolock) on g.ID=s.Gorev and s.Gorev = case when @gorev#cbo_gorev <> '' then case when s.Gorev in(select cast(data as int) from dbo.string2table(@gorev#cbo_gorev,',')) then s.Gorev else -1 end else s.Gorev end
left join TatilTipleri tt with(nolock) on tt.ID=t.ResmiTatil
Where
((GirisTarih is null or (cast(convert(varchar,Giris,112) as smalldatetime)>=GirisTarih)) and (CikisTarih is null or (cast(convert(varchar,Giris,112) as smalldatetime)<=CikisTarih)))
and cast(convert(varchar,Giris,112) as smalldatetime) between @TarihBas and @TarihSon
and SicilNo>''
and s.Firma<>'91'
and SicilNo like @SicilNo + '%' and s.Ad like @Ad + '%' and Soyad like @Soyad + '%'
Group By s.ID,s.SicilNo,s.PersonelNo, s.Ad,s.Soyad,y.Ad,y.Id,b.Ad ,s.Okod15

--select * from #tmp


------------------------------------- Haftalık 45 Saat Kontrolü ----------------------------------------------------------------------

select s.ID,
s.ad , s.Soyad ,
f.Ad Firma,
b.Ad Bolum,
pz.Ad Pozisyon,
g.Ad Gorev,
Y.Ad Yaka,
y.Id YakaId,
af.Ad AltFirma,
D.Ad Direktorluk,
datepart(ISO_WEEK,t.MesaiTarih) Hafta ,Convert(nvarchar,@TarihBas,105)+' - '+Convert(nvarchar,@TarihSon,105) TarihAraligi,
max(1)Hft,
max(case when @TarihBas<@TarihSon and DATENAME(dw,tf.mesaitarih)='Sunday' then tf.fm else 0 end )TasnifFm,

max(case when @TarihBas<@TarihSon and IzinTip<>0 and Ucretli=0 then 1 else 0 end )Dusme,

--max(case when @TarihBas<@TarihSon and Ucretli=0 and IzinTip not in (12,13,0) then 450 else 0 end )Devamsiz
max(case when @TarihBas<@TarihSon and DATENAME(dw,tf.mesaitarih)='Sunday' and IzinTip=0 and GirisID=0 and tf.htsure=0 then 450 else 0 end )Devamsiz

into #tmpHaftalikFM_
from tasnifleme t
left outer join tasnif tf on tf.sicilid=t.SicilID and t.MesaiTarih=tf.mesaitarih
inner join Sicil s with(nolock) on s.ID=t.SicilID and s.ad like @Ad + '%' and s.Soyad like @soyad + '%' and s.SicilNo like @SicilNo + '%'
inner join cbo_firma f on s.firma= f.Id
and f.Id= case when @firma#cbo_firma <> '' then case when f.Id in (select cast(data as int) from dbo.string2table(@firma#cbo_firma,',')) then f.Id else -1 end else f.Id end
inner join cbo_bolum b on s.bolum= b.Id
and b.Id= case when @bolum#cbo_bolum <> '' then case when b.Id in (select cast(data as int) from dbo.string2table(@bolum#cbo_bolum,',')) then b.Id else -1 end else b.Id end
inner join cbo_pozisyon pz on s.pozisyon= pz.Id
and pz.Id= case when @pozisyon#cbo_pozisyon <> '' then case when pz.Id in (select cast(data as int) from dbo.string2table(@pozisyon#cbo_pozisyon,',')) then pz.Id else -1 end else pz.Id end
inner join cbo_gorev g on s.gorev= g.Id
and g.Id= case when @gorev#cbo_gorev <> '' then case when g.Id in (select cast(data as int) from dbo.string2table(@gorev#cbo_gorev,',')) then g.Id else -1 end else g.Id end
inner join cbo_altfirma af on s.altfirma= af.Id
and af.Id= case when @altfirma#cbo_altfirma <> '' then case when af.Id in (select cast(data as int) from dbo.string2table(@altfirma#cbo_altfirma,',')) then af.Id else -1 end else af.Id end
inner join cbo_direktorluk d on s.direktorluk= d.Id
and d.Id= case when @direktorluk#cbo_direktorluk <> '' then case when d.Id in (select cast(data as int) from dbo.string2table(@direktorluk#cbo_direktorluk,',')) then d.Id else -1 end else d.Id end
inner join cbo_yaka y on s.yaka= y.Id
and y.Id= case when @yaka#cbo_yaka <> '' then case when y.Id in (select cast(data as int) from dbo.string2table(@yaka#cbo_yaka,',')) then y.Id else -1 end else y.Id end
inner join mesailer m with(nolock) on m.ID=t.Mesaibirimi

where t.MesaiTarih between @TarihBas AND @TarihSon
and (CikisTarih is null or CikisTarih>=cast(convert(nvarchar,Giris,112) as smalldatetime))
and (GirisTarih is null or GirisTarih<=cast(convert(nvarchar,Giris,112) as smalldatetime))
group by
s.ID,
s.ad,
s.Soyad,
b.Ad,
f.Ad,
b.Ad ,
pz.Ad,
g.Ad ,d.Ad,
Y.Ad ,
y.Id,
af.Ad,

datepart(ISO_WEEK,t.MesaiTarih)






select ID SicilId,Ad,Soyad, (sum( TasnifFm))HaftalikFM,sum(Hft)Hft,sum(Devamsiz)devamsiz,sum(Dusme) Dusme --,sum(PartTimeHFT)PartTimeHFT
into #tmpHaftalikFM from #tmpHaftalikFM_
group by ID,Ad,Soyad
--select * from #tmpHaftalikFM_
--select * from #tmpHaftalikFM
------------------------------------------------------------------------------------------------------------------


--select
--*
-- from #tmp t inner join #tmpHaftalikFM tfm on t.SicilID=tfm.SicilId

select
SicilNo ,
t.Ad +' '+ t.Soyad Personel,
--(case when t.YakaId not in (6,4,2,3) then gun*7.5-(DBO.fn_CSaatTam( tfm.HFT*450)+RTS+YILS+Idari_Sosyal+UCZS+IstrahatSaat+SgkRaporOdenenSaat+KCOSaat)else NS end)*SaatlikUcret NormalCalismaMaliyeti,
--(case when t.YakaId not in (6,4,2,3) then Ns else NS end)*SaatlikUcret NormalCalismaMaliyeti,


DBO.fn_CSaatTam( case when t.YakaId<>6 then case when gun>30 and devamsiz=0 and Dusme=0 then 30 when gun>=28 and month(@TarihBas)=2 and Dusme=0 then 30 else gun end *450-(t.HFT*450+RTS+YILS+Idari_Sosyal+IdariSaat+UCZS+IstrahatSaat+SgkRaporOdenenSaat+KCOSaat+devamsiz) else nmsure-tfm.HaftalikFM-YILS-UCLS-UCLS2-RTNM end)*SaatlikUcret NormalCalismaMaliyeti,
replace(cast((DBO.fn_CSaatTam(t.HFTS)*SaatlikUcret) as varchar),'.',',') HaftaTatiliMaliyet,
replace(cast(DBO.fn_CSaatTam(RTS)*SaatlikUcret as varchar),'.',',') ResmiTatilMaaliyet,
replace(cast((dbo.fn_CSaatTam(YILS))*SaatlikUcret as varchar),'.',',') YillikIzinMaaliyet,
replace(cast((dbo.fn_CSaatTam(UCLS))*SaatlikUcret as varchar),'.',',')UcretliIzinMaaliyet,
replace(cast(dbo.fn_CSaatTam(SgkRaporOdenenSaat)*SaatlikUcret as varchar),'.',',')SgkRaporIsverenMaaliyet,
--replace(cast((FMHI+tfm.HaftalikFM)*SaatlikUcret * 1.5 as varchar),'.',',') FazlaMesaiMaaliyet,--
--replace(cast((dbo.fn_Csaattam3(FMHI+tfm.HaftalikFM))*SaatlikUcret * 1.5 as varchar),'.',',') FazlaMesaiMaaliyet,
replace(cast(((dbo.fn_Csaattam3(FMHI+tfm.HaftalikFM+FM)*1.5)*SaatlikUcret) as varchar),'.',',') FazlaMesaiMaaliyet,
replace(cast(dbo.fn_Csaattam(RTS2)*SaatlikUcret as varchar),'.',',') BayramCalismasiMaliyet,


DBO.fn_CSaatTam(case when t.YakaId<>6 then case when gun>30 and devamsiz=0 and Dusme=0 then 30 when gun>=28 and month(@TarihBas)=2 and Dusme=0 then 30 else gun end *450-(t.HFT*450+RTS+YILS+Idari_Sosyal+IdariSaat+UCZS+IstrahatSaat+SgkRaporOdenenSaat+KCOSaat+devamsiz) else nmsure-tfm.HaftalikFM-YILS-UCLS-UCLS2-RTNM end)*SaatlikUcret
+ DBO.fn_CSaatTam(t.HFTS)*SaatlikUcret
+ DBO.fn_CSaatTam(RTS)*SaatlikUcret
+ (dbo.fn_CSaatTam(YILS))*SaatlikUcret
+ DBO.fn_CSaatTam(UCLS)*SaatlikUcret
+ dbo.fn_CSaatTam(SgkRaporOdenenSaat)*SaatlikUcret
+ (dbo.fn_Csaattam3(FMHI+tfm.HaftalikFM+FM))*SaatlikUcret * 1.5
+ dbo.fn_Csaattam(RTS2)*SaatlikUcret

BrutUcret
,


(( DBO.fn_CSaatTam(case when t.YakaId<>6 then case when gun>30 and devamsiz=0 and Dusme=0 then 30 when gun>=28 and month(@TarihBas)=2 and Dusme=0 then 30 else gun end *450-(t.HFT*450+RTS+YILS+Idari_Sosyal+IdariSaat+UCZS+IstrahatSaat+SgkRaporOdenenSaat+KCOSaat+devamsiz) else nmsure-tfm.HaftalikFM-YILS-UCLS-UCLS2-RTNM end)*SaatlikUcret
+ DBO.fn_CSaatTam(t.HFTS)*SaatlikUcret
+ DBO.fn_CSaatTam(RTS)*SaatlikUcret
+ (dbo.fn_CSaatTam(YILS))*SaatlikUcret
+ DBO.fn_CSaatTam(UCLS)*SaatlikUcret
+ dbo.fn_CSaatTam(SgkRaporOdenenSaat)*SaatlikUcret
+ (dbo.fn_Csaattam3(FMHI+tfm.HaftalikFM+FM))*SaatlikUcret * 1.5
+ dbo.fn_Csaattam(RTS2)*SaatlikUcret))/SaatlikUcret UcretSaati,
Bolum




from #tmp t inner join #tmpHaftalikFM tfm on t.SicilID=tfm.SicilId

union all
select
'Genel Toplam :' SicilNo ,
''Personel,
-- (case when t.YakaId not in (6,4,2,3) then gun*7.5-(DBO.fn_CSaatTam( tfm.HFT*450)+RTS+YILS+Idari_Sosyal+UCZS+IstrahatSaat+SgkRaporOdenenSaat+KCOSaat)else NS end)*SaatlikUcret NormalCalismaMaliyeti,
--sum( (case when t.YakaId not in (6,4,2,3) then dbo.fn_CSaatTam(case when t.YakaId not in (6,4,2,3) then gun*450-(t.HFT*450+RTS+YILS+Idari_Sosyal+UCZS+IstrahatSaat+SgkRaporOdenenSaat+KCOSaat)else nmsure end) else dbo.fn_CSaatTam(case when t.YakaId not in (6,4,2,3) then gun*450-(t.HFT*450+RTS+YILS+Idari_Sosyal+UCZS+IstrahatSaat+SgkRaporOdenenSaat+KCOSaat)else nmsure end) end)) NormalCalismaMaliyeti,
sum(DBO.fn_CSaatTam(case when t.YakaId<>6 then case when gun>30 and devamsiz=0 and Dusme=0 then 30 when gun>=28 and month(@TarihBas)=2 and Dusme=0 then 30 else gun end *450-(t.HFT*450+RTS+YILS+Idari_Sosyal+IdariSaat+UCZS+IstrahatSaat+SgkRaporOdenenSaat+KCOSaat+devamsiz) else nmsure-tfm.HaftalikFM-YILS-UCLS-UCLS2-RTNM end)*SaatlikUcret) NormalCalismaMaliyeti,
replace(cast(sum(DBO.fn_CSaatTam(t.HFTS)*SaatlikUcret) as varchar),'.',',') HaftaTatiliMaliyet,
replace(cast(sum(DBO.fn_CSaatTam(RTS)*SaatlikUcret) as varchar),'.',',') ResmiTatilMaaliyet,
replace(cast(sum(dbo.fn_CSaatTam(YILS*SaatlikUcret)) as varchar),'.',',') YillikIzinMaaliyet,
replace(cast(sum(dbo.fn_CSaatTam(UCLS)*SaatlikUcret) as varchar),'.',',')UcretliIzinMaaliyet,
replace(cast(sum(dbo.fn_CSaatTam(SgkRaporOdenenSaat)*SaatlikUcret) as varchar),'.',',')SgkIsverenMaaliyet,
-- replace(cast(sum(dbo.fn_Csaattam3(FMHI+tfm.HaftalikFM)*SaatlikUcret*1.5) as varchar),'.',',') FazlaMesaiMaaliyet,--
replace(cast(sum((dbo.fn_Csaattam3(FMHI+tfm.HaftalikFM+FM)*1.5)*SaatlikUcret) as varchar),'.',',') FazlaMesaiMaaliyet,

replace(cast(sum(dbo.fn_Csaattam(RTS2)*SaatlikUcret) as varchar),'.',',') BayramCalismasiMaliyet,



sum(DBO.fn_CSaatTam(case when t.YakaId<>6 then case when gun>30 and devamsiz=0 and Dusme=0 then 30 when gun>=28 and month(@TarihBas)=2 and Dusme=0 then 30 else gun end *450-(t.HFT*450+RTS+YILS+Idari_Sosyal+IdariSaat+UCZS+IstrahatSaat+SgkRaporOdenenSaat+KCOSaat+devamsiz) else nmsure-tfm.HaftalikFM-YILS-UCLS-UCLS2-RTNM end) *SaatlikUcret
+ DBO.fn_CSaatTam(t.HFTS)*SaatlikUcret
+ DBO.fn_CSaatTam(RTS)*SaatlikUcret
+ dbo.fn_CSaatTam(YILS)*SaatlikUcret
+ DBO.fn_CSaatTam(UCLS)*SaatlikUcret
+ dbo.fn_CSaatTam(SgkRaporOdenenSaat)*SaatlikUcret
+ dbo.fn_Csaattam3(FMHI+tfm.HaftalikFM+FM)*SaatlikUcret * 1.5
+dbo.fn_Csaattam(RTS2)*SaatlikUcret) BrutUcret,

sum(
DBO.fn_CSaatTam(case when t.YakaId<>6 then case when gun>30 and devamsiz=0 and Dusme=0 then 30 when gun>=28 and month(@TarihBas)=2 and Dusme=0 then 30 else gun end *450-(t.HFT*450+RTS+YILS+Idari_Sosyal+IdariSaat+UCZS+IstrahatSaat+SgkRaporOdenenSaat+KCOSaat+devamsiz) else nmsure-tfm.HaftalikFM-YILS-UCLS-UCLS2-RTNM end) *SaatlikUcret
+ DBO.fn_CSaatTam(t.HFTS)*SaatlikUcret
+ DBO.fn_CSaatTam(RTS)*SaatlikUcret
+ (dbo.fn_CSaatTam(YILS))*SaatlikUcret
+ DBO.fn_CSaatTam(UCLS)*SaatlikUcret
+ dbo.fn_CSaatTam(SgkRaporOdenenSaat)*SaatlikUcret
+ (dbo.fn_Csaattam3(FMHI+tfm.HaftalikFM+FM))*SaatlikUcret * 1.5
+dbo.fn_Csaattam(RTS2)*SaatlikUcret)
/max(SaatlikUcret)

UcretSaati,



'' Bolum




from #tmp t inner join #tmpHaftalikFM tfm on t.SicilID=tfm.SicilId





END