SQL Procedure示例程序02

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  @sql
	exec(@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                  INT
DECLARE @DATE_MONTH                 INT
DECLARE @DATE_FROM                  DATETIME
DECLARE @DATE_TO                    DATETIME
DECLARE @OID                        VARCHAR(50)
DECLARE @PTR_ID                     VARCHAR(50)
DECLARE @JYMS_VALUE_TEMP            NVARCHAR(100)  
DECLARE @JYMS_NAME_TEMP             NVARCHAR(100)  

BEGIN	
	SET @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 TRY
		BEGIN TRANSACTION
		DELETE FROM [HRXZ_PTR_TURNOVER] WHERE [DATE_YEAR]= @DATE_YEAR AND [DATE_MONTH] = @DATE_MONTH AND TURNOVER_TYPE = @TURNOVER_TYPE

		--入职人员	
		IF @TURNOVER_TYPE = 1
			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 b
				ON a.OUTLETID = b.OID
				LEFT JOIN TBL_SALEORG c ON b.ORG_OID = c.OID AND c.SOG_LEVEL=3 AND c.SOG_BIZLEVEL=3
				LEFT JOIN TBL_SALEORG d ON c.PARENTOID = d.OID AND d.SOG_LEVEL=2 AND d.SOG_BIZLEVEL=2
				WHERE a.JOINTIME>=@DATE_FROM AND a.JOINTIME<@DATE_TO
		ELSE
			--离职人员
			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 b
				ON a.OUTLETID = b.OID
				LEFT JOIN TBL_SALEORG c ON b.ORG_OID = c.OID AND c.SOG_LEVEL=3 AND c.SOG_BIZLEVEL=3
				LEFT JOIN TBL_SALEORG d ON c.PARENTOID = d.OID AND d.SOG_LEVEL=2 AND d.SOG_BIZLEVEL=2
				WHERE a.LEAVETIME>=@DATE_FROM AND a.LEAVETIME<@DATE_TO
		
		DECLARE MyCursor CURSOR FOR
			SELECT [OID],[PTR_ID] FROM [HRXZ_PTR_TURNOVER] 
				WHERE [DATE_YEAR]=@DATE_YEAR AND [DATE_MONTH] = @DATE_MONTH AND TURNOVER_TYPE = @TURNOVER_TYPE
		OPEN MyCursor

		FETCH NEXT FROM MyCursor
			INTO @OID,@PTR_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN

			SET @JYMS_VALUE_TEMP =''
			SET @JYMS_NAME_TEMP =''
			exec [SP_HRXZ_GetOutletJYMS] @PTR_ID ,@JYMS_VALUE_TEMP out,@JYMS_NAME_TEMP out
			UPDATE [HRXZ_PTR_TURNOVER] SET [JYMS_VALUE]=@JYMS_VALUE_TEMP,[JYMS_NAME]=@JYMS_NAME_TEMP WHERE OID=@OID		

			FETCH NEXT FROM MyCursor
					INTO @OID,@PTR_ID
		END
	
		CLOSE MyCursor
		DEALLOCATE MyCursor
		COMMIT TRANSACTION

		
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH

	SET @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  @sql
	exec(@sql)
END