You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
122 lines
3.7 KiB
122 lines
3.7 KiB
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
|
|
|
|
|