Index: trunk/SQL/Functions/SplitAsScalar.txt
===================================================================
--- trunk/SQL/Functions/SplitAsScalar.txt (revision 364)
+++ trunk/SQL/Functions/SplitAsScalar.txt (revision 364)
@@ -0,0 +1,81 @@
+
+SET ANSI_NULLS ON
+GO
+SET QUOTED_IDENTIFIER ON
+GO
+
+--================================================
+-- Create Inline Table-valued Function template
+--================================================
+
+ALTER FUNCTION [dbo].[SplitAsScalar]
+(@in_del_field VARCHAR(4000),
+ @in_pos INT,
+ @in_del VARCHAR(4000) = '/')
+RETURNS VARCHAR(4000)
+AS
+BEGIN
+ /**
+ *
+ * SPLIT Scalar User-Defined function JG 1/13/2006
+ *
+ * The split function will take a delimted string value
+ * and return a value from a location in the string.
+ *
+ * FOR EXAMPLE...
+ *
+ * print dbo.split('franklin,delano,roosevelt',1,',')
+ * print dbo.split('franklin,delano,roosevelt',2,',')
+ * print dbo.split('franklin,delano,roosevelt',3,',')
+ * GO
+ *
+ * THIS RETURNS...
+ *
+ * franklin
+ * delano
+ * roosevelt
+ *
+ *
+ * @param @in_del_field Delimited Text string passed to the function
+ * @param @in_pos Position of value to return
+ * @param @in_del Delimiter. DEFAULT value is ","
+ * @headcom
+ */
+ DECLARE @retVal VARCHAR(4000)
+ DECLARE @varFldDel VARCHAR(4000) SET @varFldDel = @in_del_field + @in_del
+ DECLARE @i int SET @i = 0
+ DECLARE @varExit VARCHAR(5) SET @varExit = 'FALSE'
+ DECLARE @intLastLen int SET @intLastLen = 1
+ --
+ IF @in_pos < 1
+ SET @retVal = 'Not Valid Option, 1 is the first occurance of an item'
+ ElSE
+ --> loop through till the correct column is found
+ WHILE @varExit = 'FALSE'
+ BEGIN
+ SET @i = @i + 1
+ If @i > 1
+ BEGIN
+ SET @varFldDel = SUBSTRING(@varFldDel,@intLastLen,8000)
+ END
+ If CHARINDEX(@in_del,@varFldDel) < 1
+ BEGIN
+ RETURN NULL
+ END
+ --
+ SET @retVal = SUBSTRING(@varFldDel,1,(CHARINDEX(@in_del,@varFldDel)-1))
+ SET @intLastLen = LEN(@retVal) + 2
+ If @i = @in_pos
+ BREAK
+ END
+ --
+ RETURN @retVal
+ --
+END
+GO
+
+SET ANSI_NULLS OFF
+GO
+SET QUOTED_IDENTIFIER OFF
+GO
+
Index: trunk/SQL/Functions/SplitAsTable.txt
===================================================================
--- trunk/SQL/Functions/SplitAsTable.txt (revision 364)
+++ trunk/SQL/Functions/SplitAsTable.txt (revision 364)
@@ -0,0 +1,48 @@
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SplitAsTable]') and xtype in (N'FN', N'IF', N'TF'))
+drop function [dbo].[SplitAsTable]
+GO
+
+SET QUOTED_IDENTIFIER OFF
+GO
+SET ANSI_NULLS OFF
+GO
+
+
+CREATE FUNCTION dbo.SplitAsTable(@String nvarchar(4000), @Delimiter char(1))
+RETURNS @Results TABLE (Items nvarchar(4000))
+AS
+
+
+ BEGIN
+ DECLARE @INDEX INT
+ DECLARE @SLICE nvarchar(4000)
+ -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
+ -- ERO FIRST TIME IN LOOP
+ SELECT @INDEX = 1
+ WHILE @INDEX !=0
+
+
+ BEGIN
+ -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
+ SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
+ -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
+ IF @INDEX !=0
+ SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
+ ELSE
+ SELECT @SLICE = @STRING
+ -- PUT THE ITEM INTO THE RESULTS SET
+ INSERT INTO @Results(Items) VALUES(@SLICE)
+ -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
+ SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
+ -- BREAK OUT IF WE ARE DONE
+ IF LEN(@STRING) = 0 BREAK
+ END
+ RETURN
+END
+
+GO
+SET QUOTED_IDENTIFIER OFF
+GO
+SET ANSI_NULLS ON
+GO
+
Index: trunk/SQL/Reports/RozliczenieProwizjiZaReklame2009.txt
===================================================================
--- trunk/SQL/Reports/RozliczenieProwizjiZaReklame2009.txt (revision 364)
+++ trunk/SQL/Reports/RozliczenieProwizjiZaReklame2009.txt (revision 364)
@@ -0,0 +1,18 @@
+
+SELECT
+W.Agencja,
+LOWER(W.Symbol) AS Agent,
+A.[Imiê] AS Imie,
+A.Nazwisko,
+COUNT(W.ID_FAKTURY) AS IloscFaktur,
+SUM(W.Netto) AS Netto,
+ROUND(SUM(W.Netto * W.procentProwizji), 2) AS Prowizja,
+A.Pracownicy
+FROM WplywyByTytul W
+LEFT OUTER JOIN AGENCI A ON A.Symbol=W.Symbol
+WHERE (YEAR(W.dataWplaty) = @rok) AND (MONTH(W.dataWplaty) = @miesiac)
+AND (@pracownicy IS NULL OR A.Pracownicy = @pracownicy)
+AND (@prawa_agent IS NULL OR W.Symbol=@prawa_agent)
+
+GROUP BY W.Agencja, W.Symbol, A.[Imiê], A.Nazwisko, A.Pracownicy
+ORDER BY A.Nazwisko
Index: trunk/RaportySQL/RozliczenieProwizjiZaReklame2009.rdl
===================================================================
--- trunk/RaportySQL/RozliczenieProwizjiZaReklame2009.rdl (revision 299)
+++ trunk/RaportySQL/RozliczenieProwizjiZaReklame2009.rdl (revision 364)
@@ -155,9 +155,9 @@
- NETTO
+ Netto
System.Decimal
-
- ProcentProwizji
+
+ Prowizja
System.Double
@@ -178,49 +178,21 @@
BAZA_REKLAM
- DECLARE @Prowizje TABLE
-(
- Agencja NVARCHAR(255),
- Agent NVARCHAR(255),
- Imie NVARCHAR(255),
- Nazwisko NVARCHAR(255),
- IloscFaktur INT,
- Netto MONEY,
- Prowizja FLOAT,
- Pracownicy bit
-)
+ SELECT
+W.Agencja,
+LOWER(W.Symbol) AS Agent,
+A.[ImiÄ] AS Imie,
+A.Nazwisko,
+COUNT(W.ID_FAKTURY) AS IloscFaktur,
+SUM(W.Netto) AS Netto,
+ROUND(SUM(W.Netto * W.procentProwizji), 2) AS Prowizja,
+A.Pracownicy
+FROM WplywyByTytul W
+LEFT OUTER JOIN AGENCI A ON A.Symbol=W.Symbol
+WHERE (YEAR(W.dataWplaty) = @rok) AND (MONTH(W.dataWplaty) = @miesiac)
+AND (@pracownicy IS NULL OR A.Pracownicy = @pracownicy)
+AND (@prawa_agent IS NULL OR W.Symbol=@prawa_agent)
-INSERT INTO @Prowizje(Agencja, Agent, Imie, Nazwisko, IloscFaktur, Netto, Prowizja, Pracownicy)
-SELECT
- AGENCJE.Symbol AS Agencja,
- AGENCI.Symbol AS Agent,
- AGENCI.[ImiÄ],
- AGENCI.Nazwisko,
- COUNT(FAKTURY.ID_FAKTURY) AS IloscFaktur,
- CASE WHEN Korekta.Netto IS NULL THEN SUM(FAKTURA_DETAILS.NETTO) ELSE Korekta.Netto END AS NETTO,
- CASE WHEN FAKTURY.idZamowienia IS NULL THEN COALESCE(R.[Procent Prowizji], 0) ELSE COALESCE(P.procentProwizji, 0) END AS procentProwizji,
- AGENCI.Pracownicy
-FROM FAKTURY
- LEFT OUTER JOIN FAKTURA_DETAILS ON FAKTURY.ID_FAKTURY = FAKTURA_DETAILS.ID_FAKTURY
- LEFT OUTER JOIN AGENCI ON FAKTURY.NUMER_ROZ = AGENCI.F_ROZ
- LEFT OUTER JOIN AGENCJE ON AGENCI.ID_AGENCJI = AGENCJE.Id_agencji
- LEFT OUTER JOIN Wplaty ON FAKTURY.ID_FAKTURY = Wplaty.idFaktury
- LEFT OUTER JOIN KLIENCI ON FAKTURY.ID_NABYWCY = KLIENCI.CustomerID
- LEFT OUTER JOIN PROWIZJE AS P ON P.idFaktury = FAKTURY.ID_FAKTURY
- LEFT OUTER JOIN VIEW_KOREKTY AS Korekta ON Korekta.idFakturyKorekta = FAKTURY.ID_FAKTURY
- LEFT OUTER JOIN REKLAMA AS R ON R.ID_FAKTURY = FAKTURY.ID_FAKTURY
-WHERE (FAKTURY.KOREKTA <> 1)
-AND (YEAR(Wplaty.dataWplaty) = @rok)
-AND (MONTH(Wplaty.dataWplaty) = @miesiac)
-GROUP BY
-AGENCI.[ImiÄ], AGENCI.Nazwisko, AGENCI.Symbol,
-AGENCJE.Symbol, Korekta.Netto, R.[Procent Prowizji], FAKTURY.idZamowienia, P.procentProwizji, AGENCI.Pracownicy
-ORDER BY AGENCI.Nazwisko
-
-
-SELECT Agencja, Agent, Imie, Nazwisko, SUM(IloscFaktur) AS IloscFaktur, Sum(Netto) AS NETTO, SUM(Netto*Prowizja) AS ProcentProwizji, Pracownicy FROM @Prowizje
-WHERE (@pracownicy IS NULL OR Pracownicy = @pracownicy)
-AND (@prawa_agent IS NULL OR Agent=@prawa_agent)
-GROUP BY Agencja, Agent, Imie, Nazwisko, Pracownicy
-ORDER BY Nazwisko
+GROUP BY W.Agencja, W.Symbol, A.[ImiÄ], A.Nazwisko, A.Pracownicy
+ORDER BY A.Nazwisko
@@ -537,5 +509,5 @@
true
- =Fields!ProcentProwizji.Value
+ =Fields!Prowizja.Value
@@ -909,14 +881,16 @@
textbox17