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