Notes

/****** 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


-- exec [dbo].[sp_PDKS1231_rpt] '2021-05-01','2021-05-05','','','','6'
alter PROCEDURE [dbo].[sp_PDKS1231_rpt]
@TarihBas datetime,
@TarihSon datetime,
@firma#cbo_firma nvarchar(1000),
@Pozisyon#cbo_pozisyon nvarchar(1000),
@yaka#cbo_yaka 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
,SicilNo
, s.Ad
, Soyad
,y.Ad yaka
,y.Id YakaId
,p.AD Pozison
,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
,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 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 (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
, 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 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(1)Gun

,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_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_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

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'

Group By s.ID,s.SicilNo,s.PersonelNo, s.Ad,s.Soyad,y.Ad,y.Id ,s.Okod15,p.ad

--select * from #tmp


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

select
s.ID,
s.ad ,
s.Soyad ,
f.Ad Firma,
pz.Ad Pozisyon,
Y.Ad Yaka,
y.Id YakaId,

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
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_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_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,
f.Ad,
pz.Ad,
Y.Ad ,
y.Id,

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
SicilNo ,
t.Ad +' '+ t.Soyad Personel,
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




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



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





END