if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetKartotekaList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetKartotekaList]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE     FUNCTION [dbo].[GetKartotekaList] (@MODE int = 1 ,@DATE char(10) = '21.10.2004',@FullInf bit = 1)  
RETURNS @table TABLE 
([O_HMEN01_ID] int,[REGION_COD] int,[O_HMEN01_ID_REGION_COD] int,[FIO] nvarchar(152),  
[FAMILY] nvarchar(50), [FSTNAME] nvarchar(50), [LSTNAME] nvarchar(50),[BIRTHDATE] datetime,[AGE] int,
[DolgName] nvarchar(250),[DB_WORK] datetime,[DF_WORK] datetime,
[WORKPLACE] nvarchar(2000),[LastClassName] nvarchar(150))  AS  
BEGIN 
DECLARE @D datetime
SELECT @D =  CONVERT(DATETIME,@DATE,104)
if (@FullInf = 1)
begin
	if (@MODE = 1)/*Штатные*/
	begin
	    	INSERT INTO @table 
		SELECT  DISTINCT  h01.O_HMEN01_ID,h01.REGION_COD,
		CheckSum(h01.O_HMEN01_ID,h01.REGION_COD) AS O_HMEN01_ID_REGION_COD,h01.FIO,
		h01.FAMILY, h01.FSTNAME, h01.LSTNAME, h01.BIRTHDATE,[dbo].[GetAge](h01.BIRTHDATE,@D) AS AGE,
		O_SRT.[NAME] AS DolgName,ph.DATEBEGIN AS DB_WORK,ph.DATEFINISH AS DF_WORK,
		--'' AS WorkPlace,
		--'' AS LastClassName
		ph.WorkPlace AS WorkPlace,
		[dbo].[GetLastClassName] (h01.O_HMEN01_ID,h01.REGION_COD) as LastClassName
		FROM O_HMEN01 h01  
		INNER JOIN
                      	O_PostHuman ph ON h01.O_HMEN01_ID = ph.O_HMEN01_ID and  h01.REGION_COD = ph.REGION_COD
		INNER JOIN 
		O_SRT on O_SRT.O_SRT_ID = ph.O_SRT_ID and O_SRT.REGION_COD = ph.REGION_COD
		where (@D between ISNULL(ph.DATEBEGIN,'01.01.1900') and ISNULL(ph.DATEFINISH,'01.01.2100'))
			and ph.M_PHRelation_ID=1
	end
	else
	if (@MODE = 2) /*Уволенные*/
	begin
		INSERT INTO @table
		SELECT DISTINCT h01.O_HMEN01_ID,h01.REGION_COD,
		CheckSum(h01.O_HMEN01_ID,h01.REGION_COD) as O_HMEN01_ID_REGION_COD,h01.FIO,
		h01.FAMILY, h01.FSTNAME, h01.LSTNAME,h01.BIRTHDATE ,[dbo].[GetAge](h01.BIRTHDATE,@D) as AGE,
		ow01.DOLGNAME AS DolgName,ow01.DATEBEGIN as DB_WORK,ow01.DATEFINISH as DF_WORK, 
		--'' AS WorkPlace,
		--'' AS LastClassName
		ow01.ORGNAME AS WorkPlace,
		[dbo].[GetLastClassName] (h01.O_HMEN01_ID,h01.REGION_COD) as LastClassName 
		FROM O_HMEN01 h01
		INNER JOIN O_WFL_01 ow01 ON ow01.O_HMEN01_ID = h01.O_HMEN01_ID and  ow01.REGION_COD = h01.REGION_COD
		INNER JOIN M_ORD_01 ON M_ORD_01.M_ORD_01_ID = ow01.M_ORD_01_ID
		WHERE (ow01.DATEBEGIN =
      			(SELECT MAX(DATEBEGIN)FROM o_wfl_01 ow01_1
       			WHERE ow01_1.O_HMEN01_ID = h01.O_HMEN01_ID 
			and ow01_1.REGION_COD = h01.REGION_COD
			AND ow01_1.datebegin <= @D 
			AND M_ORD_01.M_ORD_05_ID = 1))
	end
	else
	
	begin        /*Все*/
		INSERT INTO @table 
		SELECT DISTINCT h01.O_HMEN01_ID,h01.REGION_COD,
		CheckSum(h01.O_HMEN01_ID,h01.REGION_COD) as O_HMEN01_ID_REGION_COD,h01.FIO,
		h01.FAMILY, h01.FSTNAME, h01.LSTNAME,h01.BIRTHDATE ,[dbo].[GetAge](h01.BIRTHDATE,@D) as AGE,
		ow01.DOLGNAME AS DolgName,ow01.DATEBEGIN as DB_WORK,ow01.DATEFINISH as DF_WORK,
		--'' AS WorkPlace,
		--'' AS LastClassName 
		ow01.ORGNAME AS WorkPlace,
		[dbo].[GetLastClassName] (h01.O_HMEN01_ID,h01.REGION_COD) as LastClassName 
		FROM O_HMEN01 h01
		LEFT OUTER JOIN 
		O_WFL_01 ow01 ON ow01.O_HMEN01_ID = h01.O_HMEN01_ID and ow01.REGION_COD = h01.REGION_COD 
		WHERE (ow01.DATEBEGIN =
      		(SELECT MAX(DATEBEGIN)FROM o_wfl_01 ow01_1
       		WHERE ow01_1.O_HMEN01_ID = h01.O_HMEN01_ID 
		and ow01_1.REGION_COD = h01.REGION_COD
		AND ow01_1.datebegin <= @D)) 
		or (ow01.RECNUM is null)
	end
	
	
end
if (@FullInf = 0)
begin
	if (@MODE = 1)/*Штатные*/
	begin
	    	INSERT INTO @table 
		SELECT  DISTINCT  h01.O_HMEN01_ID,h01.REGION_COD,
		CheckSum(h01.O_HMEN01_ID,h01.REGION_COD) AS O_HMEN01_ID_REGION_COD,h01.FIO,
		h01.FAMILY, h01.FSTNAME, h01.LSTNAME, h01.BIRTHDATE,[dbo].[GetAge](h01.BIRTHDATE,@D) AS AGE,
		O_SRT.[NAME] AS DolgName,ph.DATEBEGIN AS DB_WORK,ph.DATEFINISH AS DF_WORK,
		'' AS WorkPlace,
		'' AS LastClassName
		--ph.WorkPlace AS WorkPlace,
		--[dbo].[GetLastClassName] (h01.O_HMEN01_ID,h01.REGION_COD) as LastClassName
		FROM O_HMEN01 h01  
		INNER JOIN
                      	O_PostHuman ph ON h01.O_HMEN01_ID = ph.O_HMEN01_ID and  h01.REGION_COD = ph.REGION_COD
		INNER JOIN 
		O_SRT on O_SRT.O_SRT_ID = ph.O_SRT_ID and O_SRT.REGION_COD = ph.REGION_COD
		where (@D between ISNULL(ph.DATEBEGIN,'01.01.1900') and ISNULL(ph.DATEFINISH,'01.01.2100'))
			and ph.M_PHRelation_ID=1
	end
	else
	if (@MODE = 2) /*Уволенные*/
	begin
		INSERT INTO @table
		SELECT DISTINCT h01.O_HMEN01_ID,h01.REGION_COD,
		CheckSum(h01.O_HMEN01_ID,h01.REGION_COD) as O_HMEN01_ID_REGION_COD,h01.FIO,
		h01.FAMILY, h01.FSTNAME, h01.LSTNAME,h01.BIRTHDATE ,[dbo].[GetAge](h01.BIRTHDATE,@D) as AGE,
		ow01.DOLGNAME AS DolgName,ow01.DATEBEGIN as DB_WORK,ow01.DATEFINISH as DF_WORK, 
		'' AS WorkPlace,
		'' AS LastClassName
		--ow01.ORGNAME AS WorkPlace,
		--[dbo].[GetLastClassName] (h01.O_HMEN01_ID,h01.REGION_COD) as LastClassName 
		FROM O_HMEN01 h01
		INNER JOIN O_WFL_01 ow01 ON ow01.O_HMEN01_ID = h01.O_HMEN01_ID and  ow01.REGION_COD = h01.REGION_COD
		INNER JOIN M_ORD_01 ON M_ORD_01.M_ORD_01_ID = ow01.M_ORD_01_ID
		WHERE (ow01.DATEBEGIN =
      			(SELECT MAX(DATEBEGIN)FROM o_wfl_01 ow01_1
       			WHERE ow01_1.O_HMEN01_ID = h01.O_HMEN01_ID 
			and ow01_1.REGION_COD = h01.REGION_COD
			AND ow01_1.datebegin <= @D 
			AND M_ORD_01.M_ORD_05_ID = 1))
	end
	else
	
	begin        /*Все*/
		INSERT INTO @table 
		SELECT DISTINCT h01.O_HMEN01_ID,h01.REGION_COD,
		CheckSum(h01.O_HMEN01_ID,h01.REGION_COD) as O_HMEN01_ID_REGION_COD,h01.FIO,
		h01.FAMILY, h01.FSTNAME, h01.LSTNAME,h01.BIRTHDATE ,[dbo].[GetAge](h01.BIRTHDATE,@D) as AGE,
		ow01.DOLGNAME AS DolgName,ow01.DATEBEGIN as DB_WORK,ow01.DATEFINISH as DF_WORK,
		'' AS WorkPlace,
		'' AS LastClassName 
		--ow01.ORGNAME AS WorkPlace,
		--[dbo].[GetLastClassName] (h01.O_HMEN01_ID,h01.REGION_COD) as LastClassName 
		FROM O_HMEN01 h01
		LEFT OUTER JOIN 
		O_WFL_01 ow01 ON ow01.O_HMEN01_ID = h01.O_HMEN01_ID and ow01.REGION_COD = h01.REGION_COD 
		WHERE (ow01.DATEBEGIN =
      		(SELECT MAX(DATEBEGIN)FROM o_wfl_01 ow01_1
       		WHERE ow01_1.O_HMEN01_ID = h01.O_HMEN01_ID 
		and ow01_1.REGION_COD = h01.REGION_COD
		AND ow01_1.datebegin <= @D)) 
		or (ow01.RECNUM is null)
	end
	
	
end
RETURN 
END













GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  REFERENCES  ON [dbo].[GetKartotekaList]  TO [public]
GO

