|
|
|
|
|
|
|
-- exec sal.spEmployeeSel @shdridref=0,@Id=-1,@IsDeleted=0,@Emptype=1,@SalGroup=-1,@SalDate1=N'1399/01',@SalDate2=N'1405/01'
|
|
-- exec sal.spEmployeeSel @shdridref=0,@Id=-1,@IsDeleted=0,@Emptype=1,@SalGroup=-1,@SalDate1=N'',@SalDate2=N''
|
|
|
|
|
|
|
|
alter proc [sal].[spEmployeeSel]
|
|
@shdridref int ,
|
|
@Id BIGINT,
|
|
@IsDeleted int ,
|
|
@Emptype BIT =1 ,
|
|
@SalGroup INT ,
|
|
@SalDate1 varchar(7)='',
|
|
@SalDate2 varchar(7)=''
|
|
AS
|
|
|
|
|
|
|
|
|
|
if (@SalDate1 <>'' and @SalDate2 <>'')
|
|
|
|
begin
|
|
|
|
|
|
SELECT DISTINCT e.Id, e.TfIdRef, e.Name, e.Family
|
|
|
|
FROM sal.Employee AS e
|
|
LEFT OUTER JOIN sal.SalItm as i on EmpIdRef=e.id
|
|
LEFT OUTER JOIN sal.SalHdr as H on SHdrIdRef=h.id
|
|
LEFT OUTER JOIN sal.BSalGroup AS bg ON i.SalGroup = bg.Id
|
|
|
|
where (e.Id=@Id or @Id=-1) AND (e.IsDeleted=@IsDeleted or @IsDeleted=-1) and isnull(i.Emptype,e.Emptype)=@Emptype
|
|
AND (isnull(i.SalGroup,e.SalGroup)=@SalGroup OR @SalGroup=-1 or e.SalGroup is null)
|
|
and (h.saldate between @SalDate1 and @SalDate2 )
|
|
ORDER BY e.TfIdRef
|
|
|
|
return
|
|
end
|
|
|
|
|
|
DECLARE @MabnaCode1 bigint,@MabnaCode21 bigint,@MabnaCode22 bigint ,@MabnaCode26 bigint,@MabnaCode27 bigint,@MabnaCode53 BIGINT
|
|
|
|
select @MabnaCode1 = val from sal.BSettingAcitve where Code=1
|
|
select @MabnaCode21= val from sal.BSettingAcitve where Code=21
|
|
select @MabnaCode22= val from sal.BSettingAcitve where Code=22
|
|
select @MabnaCode26= val from sal.BSettingAcitve where Code=26
|
|
select @MabnaCode27= val from sal.BSettingAcitve where Code=27
|
|
select @MabnaCode53= val from sal.BSettingAcitve where Code=53
|
|
SELECT DISTINCT e.Id, e.TfIdRef, e.Name, e.Family, e.FatherName, e.Gender,e.IdNo, e.CodeMeli, e.BDate,
|
|
e.BCity, e.SDate, e.SCity, e.Address, e.BimNo, e.ContractType,
|
|
e.SalGroup, e.SalIndex, e.MaritalStatus, e.InsuManuCode, e.Child, e.DependCount,
|
|
e.TaxCode,e.BankId, rtrim(ltrim(e.SalBankNo)) SalBankNo , e.BonBankNo, e.BonCardNo, e.BimContract, e.InsuPer7,
|
|
e.InsuPer20, e.InsuPer3, e.InsuPer4, e.InsuCard1, e.InsuCard2, e.InsuCard3,
|
|
e.BasePrice, e.JazbPrice, e.ExpertPrice, e.SuperPrice, e.HardPrice, e.MatchPrice,
|
|
e.RegionPrice, e.ExtraPrice1, e.ExCode1, e.ExtraPrice2, e.ExCode2, e.ExtraPrice3,
|
|
e.ExCode3, e.LunchPrice, e.TransPrice, e.HousingPrice, e.OverHour2, e.ShiftWorkPer,
|
|
e.HasRefah, e.HasOmr, e.HasOmrBase, e.SanavatType, e.Degree, e.JobCode,e.JobName, e.EstDate, e.EndDate, e.PostCode,
|
|
b.Title AS DegreeTitle , bt3.Title AS ContractTypeTitle, bs.Title AS MaritalStatusTitle,
|
|
bg.Title AS SalGroupTitle,bi.Title AS SalIndexTitle,
|
|
bi2.Title AS ExCode1Title,bi3.Title AS ExCode2Title, bi4.Title AS ExCode3Title, bp.Title AS TaxCodeTitle,
|
|
bm.Title AS InsuManuTitle, ab.BankName,e.AccCode,
|
|
CASE WHEN e.SanavatType=0 THEN 'هيچکدام'
|
|
WHEN e.SanavatType=1 THEN 'فقط سنوات'
|
|
WHEN e.SanavatType=2 THEN 'سنوات و عیدی'
|
|
END AS SanavatTypeTitle,
|
|
|
|
CASE WHEN e.Gender =1 THEN 'زن'
|
|
WHEN e.Gender=0 THEN 'مرد'
|
|
END AS GenderTitle,e.IsDeleted
|
|
,Daywork,i.OverHour1
|
|
,Timecartprj=(select case when COUNT(*)>0 then 1 else 0 end from sal.SalItmPrj where EmpIdRef=e.Id and sal.SalItmPrj.SalItmIdRef=i.Id)
|
|
,e.AgreePrice ,CAST (0 AS bit) SelectRow , bms.Id MilitaryStatusName,e.MilitaryStatus ,e.EndContractDate,e.SumSanavatPrice
|
|
FROM sal.Employee AS e
|
|
LEFT JOIN sal.BDegree AS b ON e.Degree = b.Id
|
|
LEFT JOIN sal.BMilitaryStatus AS bms ON e.MilitaryStatus = bms.Id
|
|
LEFT OUTER JOIN sal.BContractType AS bt3 ON e.ContractType = bt3.Code
|
|
LEFT OUTER JOIN sal.BMaritalStatus AS bs ON e.MaritalStatus = bs.Id
|
|
LEFT OUTER JOIN sal.BSalIndex AS bi ON e.SalIndex = bi.Code
|
|
LEFT OUTER JOIN sal.BTaxInsu AS bi2 ON e.ExCode1 = bi2.Id
|
|
LEFT OUTER JOIN sal.BTaxInsu AS bi3 ON e.ExCode2 = bi3.Id
|
|
LEFT OUTER JOIN sal.BTaxInsu AS bi4 ON e.ExCode3 = bi4.Id
|
|
LEFT OUTER JOIN sal.BTaxPer AS bp ON e.TaxCode = bp.Id
|
|
LEFT OUTER JOIN sal.BInsuManu AS bm ON e.InsuManuCode = bm.Code
|
|
LEFT OUTER JOIN com.ArzBankId AS ab ON e.BankId = ab.Id
|
|
LEFT OUTER JOIN sal.SalItm as i on EmpIdRef=e.id and SHdrIdRef=@shdridref
|
|
LEFT OUTER JOIN sal.BSalGroup AS bg ON i.SalGroup = bg.Id
|
|
where (e.Id=@Id or @Id=-1) AND (e.IsDeleted=@IsDeleted) and isnull(i.Emptype,e.Emptype)=@Emptype
|
|
AND (isnull(i.SalGroup,e.SalGroup)=@SalGroup OR @SalGroup=-1 or e.SalGroup is null)
|
|
|
|
ORDER BY e.TfIdRef
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [sal].[spRptFish] Script Date: 6/20/2026 10:38:24 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/*
|
|
select * from sal.Employee
|
|
[sal].[spRptFish111] 0,23,-1,'1403/01','1404/06'
|
|
|
|
select EmpIdRef empid
|
|
,Sum( case when MisPriceCode=1 then MisPrice+LunchPrice+CostPrice else 0 end ) MisPrice1
|
|
,Sum( case when MisPriceCode=2 then MisPrice+LunchPrice+CostPrice else 0 end ) MisPrice2
|
|
,Sum( case when MisPriceCode=3 then MisPrice+LunchPrice+CostPrice else 0 end ) MisPrice3
|
|
,Sum( case when MisPriceCode=4 then MisPrice+LunchPrice+CostPrice else 0 end ) MisPrice4
|
|
into #mis
|
|
from sal.Mission group by empidref
|
|
*/
|
|
|
|
|
|
alter proc [sal].[spRptFish]
|
|
@shdridref bigint=0,@EmpIdRef int,@Salgroup int,@SalDate1 varchar(7)='',@SalDate2 varchar(7)='',@UserId int=0,@AllowPrintFish bit=1
|
|
as
|
|
|
|
create table #SHdr (ShdrId int )
|
|
if @shdridref>0
|
|
insert into #SHdr (ShdrId) values (@shdridref)
|
|
|
|
if @shdridref <=0 and @SalDate1>'' and @SalDate2 >''
|
|
begin
|
|
|
|
INSERT INTO [sal].[LogRptFish] ([EmpIdRef] ,[SalDate1] ,[SalDate2] ,[MainUserId] ,[RptDate])
|
|
select @EmpIdRef ,@SalDate1 ,@SalDate2 ,@UserId ,getdate()
|
|
insert into #SHdr (ShdrId) select distinct h.id from sal.SalHdr h join sal.salitm i on shdridref=h.id
|
|
where ( EmpIdRef=@EmpIdRef or @EmpIdRef =-1) and SalDate between @SalDate1 AND @SalDate2
|
|
and Daywork+SickDay>0 /*and payprice>0*/
|
|
and (AllowPrintFish=@AllowPrintFish or @shdridref=-1)-- از برنامه اتوماسیون که افراد فیش می گیرند یک می آید و از مالی صفر می آید
|
|
end
|
|
|
|
--ممکن است فیش از ماه گذشته باشد
|
|
-- در اینصورت شرح های مربوط به همان ماه استفاده میشود
|
|
select r.*,h.SHdrIdRef ShdrId into #r from sal.BReduction r
|
|
join sal.SalHdrReduction h on r.id=h.RSIdRef
|
|
where h.SHdrIdRef in (select ShdrId from #SHdr)
|
|
|
|
|
|
|
|
select EmpIdRef empid ,SHdrIdRef ShdrId
|
|
,Sum( case when MisPriceCode=1 then MisPrice+LunchPrice+CostPrice else 0 end ) MisPrice1
|
|
,Sum( case when MisPriceCode=2 then MisPrice+LunchPrice+CostPrice else 0 end ) MisPrice2
|
|
,Sum( case when MisPriceCode=3 then MisPrice+LunchPrice+CostPrice else 0 end ) MisPrice3
|
|
,Sum( case when MisPriceCode=4 then MisPrice+LunchPrice+CostPrice else 0 end ) MisPrice4
|
|
,Sum( case when MisPriceCode=5 then MisPrice+LunchPrice+CostPrice else 0 end ) MisPrice5
|
|
into #mis
|
|
from sal.Mission where SHdrIdRef in (select ShdrId from #SHdr) AND ( EmpIdRef=@EmpIdRef or @EmpIdRef =-1) group by empidref,SHdrIdRef
|
|
|
|
|
|
|
|
|
|
|
|
|
|
declare @Val int =0,@Val34 int=0
|
|
|
|
select @Val= defaultValue from pcs.Settings where ID =30032
|
|
select @Val34= defaultValue from pcs.Settings where ID =30034
|
|
|
|
|
|
|
|
SELECT i.EmpIdRef, e.TfIdRef,e.Name,e.Family
|
|
,BimNo,i.InsuCard1,i.InsuCard2,i.InsuCard3
|
|
,i.Child,i.SalIndex salindexCode,i.SalGroup,CodeMeli,i.SalBankNo
|
|
,i.BasePrice,i.DayWork
|
|
,i.OverHour1+i.OverHour2 overHour
|
|
,g.code salgroupCode
|
|
,g.Title salgrouptitle
|
|
,ind.Title salindextitle
|
|
,i.CalcBasePrice,i.InsuManuCode,SickDay,TotalMisTime,i.DelayMin
|
|
,BasePriceDesc=(select Dsc from #r where Code=100 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcSuperPrice
|
|
,SuperPriceDesc=(select Dsc from #r where Code=101 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcJazbPrice
|
|
,JazbPriceDesc=(select Dsc from #r where Code=102 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcExpertPrice
|
|
,ExpertPriceDesc=(select Dsc from #r where Code=103 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcRegionPrice
|
|
,RegionPriceDesc=(select Dsc from #r where Code=106 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcOverPrice
|
|
,OverPriceDesc=(select Dsc from #r where Code=107 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcShiftWorkPrice
|
|
,ShiftWorkPriceDesc=(select Dsc from #r where Code=108 and ShdrId =i.SHdrIdRef)
|
|
,i.LunchPrice
|
|
,LunchPriceDesc=(select Dsc from #r where Code=109 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcHardPrice
|
|
,CalcHardPriceDesc=(select Dsc from #r where Code=110 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcHousingPrice
|
|
,HousingPriceDesc=(select Dsc from #r where Code=111 and ShdrId =i.SHdrIdRef)
|
|
,i.MonthlySanavatPrice
|
|
,MonthlySanavatPriceDesc=(select Dsc from #r where Code=112 and ShdrId =i.SHdrIdRef)
|
|
,i.MonthlyEydPrice
|
|
,MonthlyEydPriceDesc=(select Dsc from #r where Code=104 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcChildPrice
|
|
,ChildPriceDesc=(select Dsc from #r where Code=113 and ShdrId =i.SHdrIdRef)
|
|
,i.TransPrice
|
|
,TransPriceDesc=(select Dsc from #r where Code=114 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcBonPrice
|
|
,BonPriceDesc=(select Dsc from #r where Code=115 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcStandbyPrice
|
|
,StandbyPriceDesc=(select Dsc from #r where Code=125 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcExtraPrice1
|
|
,ExtraPrice1Desc=(select Dsc from #r where Code=116 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcExtraPrice2
|
|
,ExtraPrice2Desc=(select Dsc from #r where Code=117 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcExtraPrice3
|
|
,ExtraPrice3Desc=(select Dsc from #r where Code=118 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcWifePrice
|
|
,WifePriceDesc=(select Dsc from #r where Code=132 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcOutstandingPrice
|
|
,OutstandingPriceDesc=(select Dsc from #r where Code=133 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcHokmPrice1
|
|
,CalcHokmPrice1Desc=(select Dsc from #r where Code=134 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcHokmPrice2
|
|
,CalcHokmPrice2Desc=(select Dsc from #r where Code=135 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.ExtraPriceTemp1
|
|
,ExtraPriceTemp1Desc=(select Dsc from #r where Code= case when isnull(i.ExCodeGrpTemp1,0)>0 then i.ExCodeGrpTemp1 else 119 end and ShdrId =i.SHdrIdRef )
|
|
,i.ExtraPriceTemp2
|
|
,ExtraPriceTemp2Desc=(select Dsc from #r where Code= case when isnull(i.ExCodeGrpTemp2,0)>0 then i.ExCodeGrpTemp2 else 120 end and ShdrId =i.SHdrIdRef)
|
|
|
|
|
|
,i.TaxPrice
|
|
,TaxPriceDesc=(SELECT Dsc from #r where Code=401 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcInsuPersonPrice
|
|
,InsuPersonPriceDesc=(SELECT Dsc from #r where Code=402 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcMedInsuPrsPrice1
|
|
,MedInsuPrsPrice1Desc=(SELECT Dsc from #r where Code=407 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcMedInsuPrsPrice2+i.CalcMedInsuPrsPrice3 MedInsuPrsPrice
|
|
,MedInsuPrsPriceDesc=(SELECT Dsc from #r where Code=406 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcOmrPrsPrice1
|
|
,OmrPrsPrice1Desc=(SELECT Dsc from #r where Code=404 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcOmrPrsPrice2
|
|
,OmrPrsPrice2Desc=(SELECT Dsc from #r where Code=405 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcDelayPrice
|
|
,DelayPriceDesc=(SELECT Dsc from #r where Code=408 and ShdrId =i.SHdrIdRef)
|
|
|
|
|
|
,CASE WHEN ReductCodeTemp1=1 then ReductPriceTemp1 else 0 end + case when ReductCodeTemp2=1 then ReductPriceTemp2 else 0 end Bedehi
|
|
,BedehiDesc=(select Dsc from #r where Code=201 and ShdrId =i.SHdrIdRef)
|
|
|
|
,CASE WHEN ReductCodeTemp1=1 then 0 else ReductPriceTemp1 end ReductPriceTemp1
|
|
,ReductPriceTemp1Desc=(select Dsc from #r where id=ReductCodeTemp1 and ReductCodeTemp1<>1 and ShdrId =i.SHdrIdRef)
|
|
|
|
,CASE WHEN ReductCodeTemp2=1 then 0 else ReductPriceTemp2 end ReductPriceTemp2
|
|
,ReductPriceTemp2Desc=(select Dsc from #r where id=ReductCodeTemp2 and ReductCodeTemp2<>1 and ShdrId =i.SHdrIdRef)
|
|
|
|
|
|
|
|
,i.RefahPersonPrice
|
|
,RefahPersonPriceDesc=(select Dsc from #r where Code=403 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.RefahCompanyPrice
|
|
,RefahCompanyPriceDesc=(select Dsc from #r where Code=306 and ShdrId =i.SHdrIdRef)
|
|
|
|
|
|
----------------------------------------------------
|
|
,case when @Val34 =1 then i.CalcInsuCompanyPrice else 0 end CalcInsuCompanyPrice
|
|
--,i.CalcInsuCompanyPrice
|
|
,InsuCompanyPriceDesc=(select Dsc from #r where Code=301 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcInsuWorklessPrice
|
|
,InsuWorklessDesc=(select Dsc from #r where Code=303 and ShdrId =i.SHdrIdRef)
|
|
|
|
,i.CalcInsuHardPrice
|
|
,InsuHardPriceDesc=(select Dsc from #r where Code=308 and ShdrId =i.SHdrIdRef)
|
|
|
|
,case when @Val34 =1 then i.CalcmedinsuCmpPrice1+i.CalcmedinsuCmpPrice3 else 0 end medinsuCmpPrice
|
|
--,i.CalcmedinsuCmpPrice1+i.CalcmedinsuCmpPrice3 medinsuCmpPrice
|
|
,MedCmpPriceDesc=(select Title from #r where Code=305 and ShdrId =i.SHdrIdRef)
|
|
|
|
|
|
, i.CalcOmrCmpPrice1
|
|
,CalcOmrCmpPrice1Desc=(select Title from #r where Code=302 and ShdrId =i.SHdrIdRef)
|
|
,i.CalcOmrCmpPrice2
|
|
,CalcOmrCmpPrice2Desc=(select Title from #r where Code=304 and ShdrId =i.SHdrIdRef)
|
|
|
|
|
|
|
|
,case when @Val =0 then i.TotalMisPrice else 0 end TotalMisPrice
|
|
,TotalMisPriceDesc=(select Dsc from #r where Code=121 and ShdrId =i.SHdrIdRef)
|
|
,case when @Val =0 then i.TotalMisLunchPrice else 0 end TotalMisLunchPrice
|
|
,TotalMisLunchPriceDesc=(select Dsc from #r where Code=122 and ShdrId =i.SHdrIdRef)
|
|
|
|
,case when @Val =1 then m.MisPrice1 else 0 end MisPrice1
|
|
,MisPriceDesc1=(select Dsc from #r where Code=128 and ShdrId =i.SHdrIdRef)
|
|
,case when @Val =1 then m.MisPrice2 else 0 end MisPrice2
|
|
,MisPriceDesc2=(select Dsc from #r where Code=129 and ShdrId =i.SHdrIdRef)
|
|
,case when @Val =1 then m.MisPrice3 else 0 end MisPrice3
|
|
,MisPriceDesc3=(select Dsc from #r where Code=130 and ShdrId =i.SHdrIdRef)
|
|
,case when @Val =1 then m.MisPrice4 else 0 end MisPrice4
|
|
,MisPriceDesc4=(select Dsc from #r where Code=131 and ShdrId =i.SHdrIdRef)
|
|
,case when @Val =1 then m.MisPrice5 else 0 end MisPrice5
|
|
,MisPriceDesc5=(select Dsc from #r where Code=131 and ShdrId =i.SHdrIdRef)
|
|
|
|
|
|
|
|
|
|
,i.ExPrice1
|
|
,ExPrice1Desc=(select Dsc from #r where Code=126 and ShdrId =i.SHdrIdRef)
|
|
,i.ExPrice2
|
|
,ExPrice2Desc=(select Dsc from #r where Code=127 and ShdrId =i.SHdrIdRef)
|
|
|
|
|
|
,i.TotalCmpPrice
|
|
,i.TotalWagePrice +ISNULL(i.ExPrice1,0)+ISNULL(i.ExPrice2,0) TotalWagePrice
|
|
,i.TotalReductPrice
|
|
,i.CalcLoanPrice
|
|
,i.PayPrice+ISNULL(i.ExPrice1,0)+ISNULL(i.ExPrice2,0) PayPrice
|
|
|
|
,CASE WHEN @Val34 =1 THEN i.SanavatPrice ELSE 0 END SanavatPrice
|
|
,MonthTitle=(select monthname +' '+ cast(h.Year as varchar) from sal.BCalendar where month=h.Month and year=h.Year)
|
|
,i.SHdrIdRef
|
|
FROM sal.Employee e
|
|
join sal.SalItm i on e.Id=i.EmpIdRef
|
|
join sal.SalHdr h on h.Id=i.SHdrIdRef
|
|
left join sal.BSalGroup g on g.Id=i.SalGroup
|
|
left join sal.BSalIndex Ind on Ind.Id=i.SalIndex
|
|
left join #mis m on m.EmpId=i.EmpIdRef and m.ShdrId=h.id
|
|
|
|
WHERE h.Id in (select ShdrId from #SHdr)
|
|
and (i.EmpIdRef=@EmpIdRef or @EmpIdRef=-1)
|
|
and (i.SalGroup=@Salgroup or @SalGroup=-1)
|
|
and (h.closed=1 or e.changed=0 )
|
|
and i.CalcType in (4,8) and i.EmpType=1
|
|
ORDER BY e.TfIdRef , h.SalDate
|
|
|
|
|
|
|
|
DROP TABLE #mis,#r,#SHdr
|
|
|
|
|
|
|
|
|
|
GO
|
|
|
|
|