root/branches/Prowizje/SQL/Views/vDanePlatnosciEcard.txt

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[vDanePlatnosciEcard]
AS

SELECT 
F.ID_faktury,
F.Numer,
F.Numer_Roz,
F.Numer_Rok,
F.SystemKsiegowyId,
K.FirstName, 
K.LastName, 
K.Address, 
K.PostalCode, 
K.City, 
REPLACE(REPLACE(K.nip, ' ', ''), '-', '') AS nip,
CAST(F.Numer AS nvarchar) + '/' +  F.Numer_Roz + '/' + CAST(F.Numer_Rok AS nvarchar) AS [Faktura Numer], 
F.Nabywca_nip, 
CAST(COALESCE(SUM(FD.Netto * (1 + FD.S_Vat)), 0) AS decimal(9,2)) AS Brutto, 
F.Data_Wystawienia, 
F.Termin_zaplaty, 
F.Sposob_zaplaty, 
A.[Imiê] AS Imie, 
A.Nazwisko, 
Ag.ulica, 
Ag.kod, 
Ag.miasto, 
Ag.telefon, 
CAST(COALESCE(F.waluta_brutto, 0) AS decimal(9,2)) AS waluta_brutto, 
F.waluta_miano
FROM Faktury AS F
INNER JOIN Faktura_Details AS FD ON FD.Id_Faktury=F.Id_Faktury
INNER JOIN Agenci AS A ON A.F_ROZ= F.NUMER_ROZ
INNER JOIN Agencje AS Ag ON Ag.Id_agencji= F.Id_Sprzedawcy
INNER JOIN Klienci AS K ON K.CustomerId = F.Id_Nabywcy
GROUP BY
F.Numer_Rok,
F.Numer_Roz,
F.Numer,
K.FirstName, 
K.LastName, 
K.Address, 
K.PostalCode, 
K.City, 
K.nip,
F.Nabywca_nip,
F.Data_Wystawienia, 
F.Termin_zaplaty, 
F.Sposob_zaplaty, 
F.ID_faktury, 
A.[Imiê], 
A.Nazwisko, 
Ag.ulica, 
Ag.kod, 
Ag.miasto, 
Ag.telefon, 
F.waluta_brutto, 
F.waluta_miano, 
F.SystemKsiegowyId 

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO