ALTER PROCEDURE [dbo].[SP_HRXZ_GetRptSalaryReportList] ( @PACKAGE_ID VARCHAR(50), @REGION_CODE NVARCHAR(100), @AREA_NAME NVARCHAR(100), @OUTLET_NAME NVARCHAR(100), @JYMS_NAME NVARCHAR(100), @PTR_NO VARCHAR(50), @PTR_NAME NVARCHAR(100))AS DECLARE @sql NVARCHAR(4000)BEGIN--,[S_BASE_SALARY] -- 月标准底薪--,[S_UNEMPLOYEE] -- 缺勤扣款--,[S_OVERTIME] -- 加班费--,[S_EXTRACT] -- 奖金= 提成奖金 --,[S_TEL_ALLOWANCE] -- 电话津贴--,[S_TAI_PRIZE] -- 台数嘉奖--,[S_ADJUST] -- 调整工资 --,[S_COMPANY_PENSION] -- 公司缴纳养老--,[S_COMPANY_MEDICAL] -- 公司缴纳医疗--,[S_COMPANY_UNEMPLOYEEMENT] -- 公司缴纳失业--,[S_COMPANY_FERTILITY] -- 公司缴纳生育 --,[S_COMPANY_INJURY] -- 公司缴纳工伤--,[S_MANAGEMENT_FEE] -- 服务费 = 管理费--,[S_ECONOMIC_COMPENSATION] -- 赔偿金 = 经济补偿金--,[S_OTHER_ADJUST] -- 调整--,[S_SHIFA_SALARY] -- 实发工资--,[S_YINGFA_SALARY] -- 应发工资 SET @sql='SELECT [OID] ,[PACKAGE_ID] ,[PTR_ID] ,[PTR_NO] ,[PTR_NAME] ,CONVERT(VARCHAR(10),[JOIN_TIME],23) AS JOIN_TIME ,[REGION_ID] ,[REGION_CODE] ,[AREA_ID] ,[AREA_NAME] ,[OUTLET_ID] ,[OUTLET_NAME] ,[JYMS_ID] ,[JYMS_NAME] ,CONVERT(VARCHAR(7),[DATE_FROM],23) AS DATE_FROM ,[DATE_TO] ,[S_STD_SALARY] ,[S_BASE_SALARY] ,[S_UNEMPLOYEE] ,[S_OVERTIME] ,[S_EXTRACT] ,[S_TEL_ALLOWANCE] ,[S_TAI_PRIZE] ,[S_ADJUST] ,[S_COMPANY_PENSION] ,[S_COMPANY_MEDICAL] ,[S_COMPANY_UNEMPLOYEEMENT] ,[S_COMPANY_FERTILITY] ,[S_COMPANY_INJURY] ,[S_MANAGEMENT_FEE] ,[S_ECONOMIC_COMPENSATION] ,[S_OTHER_ADJUST] ,[S_SHIFA_SALARY] ,[S_YINGFA_SALARY] ,'''' AS S_OTHER_PRIZE ,'''' AS S_SUB_TOTAL FROM [HRXZ_SALARY_ALL_ITEMS] WHERE [PACKAGE_ID]=''' + @PACKAGE_ID + ''' 'IF @REGION_CODE<>''SET @sql = @sql + ' AND [REGION_CODE] LIKE ''%' + @REGION_CODE + '%'' 'IF @AREA_NAME<>''SET @sql = @sql + ' AND [AREA_NAME] LIKE ''%' + @AREA_NAME + '%'' 'IF @OUTLET_NAME<>''SET @sql = @sql + ' AND [OUTLET_NAME] LIKE ''%' + @OUTLET_NAME + '%'' 'IF @JYMS_NAME<>''SET @sql = @sql + ' AND [JYMS_NAME] LIKE ''%' + @JYMS_NAME + '%'' 'IF @PTR_NO<>''SET @sql = @sql + ' AND [PTR_NO] LIKE ''%' + @PTR_NO + '%'' 'IF @PTR_NAME<>''SET @sql = @sql + ' AND [PTR_NAME] LIKE ''%' + @PTR_NAME + '%'' 'SET @sql = @sql + ' ORDER BY [REGION_CODE],[PTR_NO] 'PRINT @sqlexec(@sql)END
ALTER PROCEDURE [dbo].[SP_HRXZ_GetRptPTRTurnOver] ( @TURNOVER_TYPE INT, @DATE_FROM_PARA VARCHAR(10), @REGION_CODE NVARCHAR(100), @AREA_NAME NVARCHAR(100), @OUTLET_NAME NVARCHAR(100), @JYMS_NAME NVARCHAR(100), @PTR_NO VARCHAR(50), @PTR_NAME NVARCHAR(100))AS DECLARE @sql NVARCHAR(4000)DECLARE @DATE_YEAR INTDECLARE @DATE_MONTH INTDECLARE @DATE_FROM DATETIMEDECLARE @DATE_TO DATETIMEDECLARE @OID VARCHAR(50)DECLARE @PTR_ID VARCHAR(50)DECLARE @JYMS_VALUE_TEMP NVARCHAR(100) DECLARE @JYMS_NAME_TEMP NVARCHAR(100) BEGINSET @DATE_FROM = CAST(@DATE_FROM_PARA AS DATETIME)SET @DATE_YEAR = YEAR(@DATE_FROM)SET @DATE_MONTH = MONTH(@DATE_FROM)SET @DATE_TO = DATEADD(m,1,@DATE_FROM)BEGIN TRYBEGIN TRANSACTIONDELETE FROM [HRXZ_PTR_TURNOVER] WHERE [DATE_YEAR]= @DATE_YEAR AND [DATE_MONTH] = @DATE_MONTH AND TURNOVER_TYPE = @TURNOVER_TYPE--入职人员IF @TURNOVER_TYPE = 1INSERT INTO [HRXZ_PTR_TURNOVER] ([OID] ,[DATE_YEAR] ,[DATE_MONTH] ,[TURNOVER_TYPE] ,[PTR_ID] ,[PTR_NO] ,[PTR_NAME] ,[REGION_ID] ,[REGION_CODE] ,[REGION_NAME] ,[AREA_ID] ,[AREA_CODE] ,[AREA_NAME] ,[OUTLET_ID] ,[OUTLET_CODE] ,[OUTLET_NAME] ,[JYMS_VALUE] ,[JYMS_NAME] ,[JOIN_TIME] ,[LEAVE_TIME] ,[LEAVE_REASON])SELECT newid(),@DATE_YEAR,@DATE_MONTH,@TURNOVER_TYPE,a.OID ,a.STAFFNUMBER,a.NAME,c.PARENTOID AS REGION_ID,d.SOG_ORG_CODE AS REGION_CODE,d.SOG_ORG_NAME AS REGION_NAME ,b.ORG_OID AS AREA_ID,c.SOG_ORG_CODE AS AREA_CODE,c.SOG_ORG_NAME AS AREA_NAME,a.OUTLETID,b.OUT_OUTID ,b.OUT_SHORTNAME,'','',a.JOINTIME,a.LEAVETIME,''FROM HR_PROMOTER a INNER JOIN TBLOUTLET bON a.OUTLETID = b.OIDLEFT JOIN TBL_SALEORG c ON b.ORG_OID = c.OID AND c.SOG_LEVEL=3 AND c.SOG_BIZLEVEL=3LEFT JOIN TBL_SALEORG d ON c.PARENTOID = d.OID AND d.SOG_LEVEL=2 AND d.SOG_BIZLEVEL=2WHERE a.JOINTIME>=@DATE_FROM AND a.JOINTIME<@DATE_TOELSE--离职人员INSERT INTO [HRXZ_PTR_TURNOVER] ([OID] ,[DATE_YEAR] ,[DATE_MONTH] ,[TURNOVER_TYPE] ,[PTR_ID] ,[PTR_NO] ,[PTR_NAME] ,[REGION_ID] ,[REGION_CODE] ,[REGION_NAME] ,[AREA_ID] ,[AREA_CODE] ,[AREA_NAME] ,[OUTLET_ID] ,[OUTLET_CODE] ,[OUTLET_NAME] ,[JYMS_VALUE] ,[JYMS_NAME] ,[JOIN_TIME] ,[LEAVE_TIME] ,[LEAVE_REASON])SELECT newid(),@DATE_YEAR,@DATE_MONTH,@TURNOVER_TYPE,a.OID ,a.STAFFNUMBER,a.NAME,c.PARENTOID AS REGION_ID,d.SOG_ORG_CODE AS REGION_CODE,d.SOG_ORG_NAME AS REGION_NAME ,b.ORG_OID AS AREA_ID,c.SOG_ORG_CODE AS AREA_CODE,c.SOG_ORG_NAME AS AREA_NAME,a.OUTLETID,b.OUT_OUTID ,b.OUT_SHORTNAME,'','',a.JOINTIME,a.LEAVETIME,''FROM HR_PROMOTER a INNER JOIN TBLOUTLET bON a.OUTLETID = b.OIDLEFT JOIN TBL_SALEORG c ON b.ORG_OID = c.OID AND c.SOG_LEVEL=3 AND c.SOG_BIZLEVEL=3LEFT JOIN TBL_SALEORG d ON c.PARENTOID = d.OID AND d.SOG_LEVEL=2 AND d.SOG_BIZLEVEL=2WHERE a.LEAVETIME>=@DATE_FROM AND a.LEAVETIME<@DATE_TODECLARE MyCursor CURSOR FORSELECT [OID],[PTR_ID] FROM [HRXZ_PTR_TURNOVER] WHERE [DATE_YEAR]=@DATE_YEAR AND [DATE_MONTH] = @DATE_MONTH AND TURNOVER_TYPE = @TURNOVER_TYPEOPEN MyCursorFETCH NEXT FROM MyCursorINTO @OID,@PTR_IDWHILE @@FETCH_STATUS = 0BEGINSET @JYMS_VALUE_TEMP =''SET @JYMS_NAME_TEMP =''exec [SP_HRXZ_GetOutletJYMS] @PTR_ID ,@JYMS_VALUE_TEMP out,@JYMS_NAME_TEMP outUPDATE [HRXZ_PTR_TURNOVER] SET [JYMS_VALUE]=@JYMS_VALUE_TEMP,[JYMS_NAME]=@JYMS_NAME_TEMP WHERE OID=@OIDFETCH NEXT FROM MyCursorINTO @OID,@PTR_IDENDCLOSE MyCursorDEALLOCATE MyCursorCOMMIT TRANSACTIONEND TRYBEGIN CATCHROLLBACK TRANSACTIONEND CATCHSET @sql='SELECT [OID] ,(CAST([DATE_YEAR] AS VARCHAR) + ''.'' + CAST([DATE_MONTH] AS VARCHAR)) AS YEAR_MOTNTH ,[TURNOVER_TYPE] ,[PTR_ID] ,[PTR_NO] ,[PTR_NAME] ,[REGION_ID] ,[REGION_CODE] ,[REGION_NAME] ,[AREA_ID] ,[AREA_CODE] ,[AREA_NAME] ,[OUTLET_ID] ,[OUTLET_CODE] ,[OUTLET_NAME] ,[JYMS_VALUE] ,[JYMS_NAME] ,CONVERT(VARCHAR(10),[JOIN_TIME],23) JOIN_TIME ,CONVERT(VARCHAR(10),[LEAVE_TIME],23) LEAVE_TIME ,[LEAVE_REASON] FROM [HRXZ_PTR_TURNOVER] WHERE TURNOVER_TYPE = ' + CAST(@TURNOVER_TYPE AS VARCHAR)SET @sql =@sql + ' AND DATE_YEAR = ' + CAST(@DATE_YEAR AS VARCHAR)SET @sql =@sql + ' AND DATE_MONTH = ' + CAST(@DATE_MONTH AS VARCHAR)IF @REGION_CODE<>''SET @sql = @sql + ' AND [REGION_CODE] LIKE ''%' + @REGION_CODE + '%'' 'IF @AREA_NAME<>''SET @sql = @sql + ' AND [AREA_NAME] LIKE ''%' + @AREA_NAME + '%'' 'IF @OUTLET_NAME<>''SET @sql = @sql + ' AND [OUTLET_NAME] LIKE ''%' + @OUTLET_NAME + '%'' 'IF @JYMS_NAME<>''SET @sql = @sql + ' AND [JYMS_NAME] LIKE ''%' + @JYMS_NAME + '%'' 'IF @PTR_NO<>''SET @sql = @sql + ' AND [PTR_NO] LIKE ''%' + @PTR_NO + '%'' 'IF @PTR_NAME<>''SET @sql = @sql + ' AND [PTR_NAME] LIKE ''%' + @PTR_NAME + '%'' 'SET @sql =@sql + ' ORDER BY [REGION_CODE],[AREA_NAME],[OUTLET_NAME],[PTR_NAME] 'PRINT @sqlexec(@sql)END
