Alter Table zjournal alter column debitor varchar(255)
go

DROP VIEW [dbo].[View_Offene_Mahnungen]
GO

/****** Object:  View [dbo].[View_Offene_Mahnungen]    Script Date: 30.05.2018 23:32:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[View_Offene_Mahnungen]
AS
SELECT        TOP (100) PERCENT dbo.DEBITOR.Nreintrag, dbo.Faktura.Nrfaktura, dbo.DEBITOR.Faelligam, dbo.DEBITOR.Betrag, dbo.DEBITOR.Status, dbo.get_adresse_direkt(dbo.DEBITOR.Nrpatient, 1) AS Patient, 
                         ISNULL(dbo.get_adresse_direkt(dbo.DEBITOR.Nrdebitor, 1), '') AS Debitor, dbo.DEBITOR.Nrpatient, dbo.get_offener_betrag(dbo.Faktura.Nrfaktura) AS Offener_Betrag, dbo.DEBITOR.Nrdebitor, dbo.Faktura.Mahndatum1, 
                         dbo.Faktura.Mahnfaelligkeit1, dbo.Faktura.Mahngebuehr1, dbo.Faktura.Mahndatum2, dbo.Faktura.Mahnfaelligkeit2, dbo.Faktura.Mahngebuehr2, dbo.Faktura.Mahndatum3, dbo.Faktura.Mahnfaelligkeit3, 
                         dbo.Faktura.Mahngebuehr3, dbo.Faktura.erstellt_am, dbo.Faktura.MAHNSTOP
FROM            dbo.DEBITOR INNER JOIN
                         dbo.Faktura ON dbo.DEBITOR.Nrfaktura = dbo.Faktura.Nrfaktura
WHERE        (dbo.DEBITOR.Status <> 9) AND (dbo.DEBITOR.Betrag <> 0) AND (dbo.DEBITOR.Betrag > 0.04) AND (dbo.DEBITOR.aktiv = 1) AND (dbo.get_offener_betrag(dbo.Faktura.Nrfaktura) > 0) AND (dbo.Faktura.Status <> 9)
ORDER BY dbo.DEBITOR.Faelligam DESC

GO

/****** Object:  UserDefinedFunction [dbo].[get_totalblock]    Script Date: 30.05.2018 23:33:16 ******/
DROP FUNCTION [dbo].[get_totalblock]
GO

/****** Object:  UserDefinedFunction [dbo].[get_totalblock]    Script Date: 30.05.2018 23:33:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_totalblock]
(
	-- Add the parameters for the function here
	@typ INT,
	@nrbehanlung INT
)
RETURNS VARCHAR(1024)
AS
BEGIN
	DECLARE @res VARCHAR(1024)
	DECLARE @res2 VARCHAR(1024)
	DECLARE @sum float
	declare @crlf varchar(2)
    SET @crlf  = char(13) + char(10)
	SET @res=''
	SET @res2=''

	SELECT @sum=ISNULL(dbo.get_totale(1,@nrbehanlung),0)
	IF @sum<>0 BEGIN
       IF @res<>'' BEGIN
	      SET @res=@res+@crlf
		  SET @res2=@res2+@crlf
	   end
	   SET @res=@res+'Total Leistungen'
	   SET @res2=@res2+STR(@sum,10,2)
	END
    SELECT @sum=ISNULL(dbo.get_totale(2,@nrbehanlung),0)
	IF @sum<>0 BEGIN
       IF @res<>'' BEGIN
	      SET @res=@res+@crlf
		  SET @res2=@res2+@crlf
	   end
	   SET @res=@res+'Materialien'
	   SET @res2=@res2+STR(@sum,10,2)
	END
	SELECT @sum=ISNULL(dbo.get_totale(3,@nrbehanlung),0)
	IF @sum<>0 BEGIN
       IF @res<>'' BEGIN
	      SET @res=@res+@crlf
		  SET @res2=@res2+@crlf
	   end
	   SET @res=@res+'Labor'
	   SET @res2=@res2+STR(@sum,10,2)
	END
	DECLARE @rate FLOAT
    SELECT @rate=ratenzuschlag FROM dbo.Behandlu WHERE Nrbehandlung=@nrbehanlung
	IF @rate>0 BEGIN
       IF @res<>'' BEGIN
	      SET @res=@res+@crlf
		  SET @res2=@res2+@crlf
	   end
	   SET @res=@res+'Ratenzuschlag'
	   SET @res2=@res2+STR(@rate,10,2)
	end
	SELECT @sum=ISNULL(dbo.get_totale(4,@nrbehanlung),0)
	IF @sum<>0 BEGIN
       IF @res<>'' BEGIN
	      SET @res=@res+@crlf
		  SET @res2=@res2+@crlf
	   end
	   SET @res=@res+'./. Rabatt'
	   SET @res2=@res2+STR(@sum,10,2)
	END
	SELECT @sum=ISNULL(dbo.get_totale(5,@nrbehanlung),0)
	IF @sum<>0 BEGIN
       IF @res<>'' BEGIN
	      SET @res=@res+@crlf
		  SET @res2=@res2+@crlf
	   end
	   SET @res=@res+'./. Vorauszahlunagen'
	   SET @res2=@res2+STR(@sum,10,2)
	END
	IF @typ=1 RETURN @res
	IF @typ=2 RETURN @res2
	RETURN ''
END
GO