Function Smetka_Rekap(SmetkaBroj As Integer, Optional Datum1 As Date, Optional Datum2 As Date)
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQLSmetka As String, SQL1 As String, SQL2 As String
Dim Rb As Integer, PDV As Integer
Dim Naziv As String * 35
Dim Dat1 As String, Dat2 As String
Dim Temp As String, Tekst As String
Dim Suma(2) As Currency

SQLSmetka = "SELECT tblsmetki_stavki.Stavka, Sum(tblsmetki_stavki.Kolicina) AS KOL," _
          & " tblsmetki_stavki.Ed_Cena, [KOL]*[Ed_cena] AS VUkupno, " & SQL1 & "," _
          & " tblSmetki.Rabat AS Popust, [suma]-[popust] AS Za_Naplatu" _
          & " FROM tblTarifi INNER JOIN (tblSmetki INNER JOIN tblsmetki_stavki ON" _
          & " tblSmetki.ID_Smetka = tblsmetki_stavki.Smetka_Br) ON tblTarifi.Tarifa = tblsmetki_stavki.DDV" _
          & SQL2 _
          & " GROUP BY tblsmetki_stavki.Stavka, tblsmetki_stavki.Ed_Cena, tblSmetki.Rabat"

Tekst = "REKAPITULACIJA"
If SmetkaBroj = 0 Then
     If Datum1 = 0 Then Datum1 = Date
     If Datum2 = 0 Then Datum2 = Datum1 + 1
    Dat1 = "#" & Format(Datum1, "mm-dd-yyyy") & "#"
    Dat2 = "#" & Format(Datum2, "mm-dd-yyyy") & "#"
    SQL1 = "(SELECT Sum([kolicina]*[Ed_cena]) AS suma" _
       & " FROM tblSmetki INNER JOIN tblsmetki_stavki ON tblSmetki.ID_Smetka = tblsmetki_stavki.Smetka_Br" _
       & " WHERE tblSmetki.Data Between " & Dat1 & " And " & Dat2 & ") AS suma"
    SQL2 = "WHERE tblSmetki.Data Between " & Dat1 & " And " & Dat2
    If Datum2 - Datum1 = 1 Then
    Tekst = Tekst & " za " & Datum1
    Else
    Tekst = Tekst & " Od " & Datum1 & " do " & Datum2
    End If
Else
    SQL1 = "(SELECT Sum([kolicina]*[Ed_cena]) AS suma" _
       & " FROM tblSmetki INNER JOIN tblsmetki_stavki ON tblSmetki.ID_Smetka = tblsmetki_stavki.Smetka_Br" _
       & " WHERE tblSmetki.Smetka_Broj=" & SmetkaBroj & ") AS Suma"
    SQL2 = "WHERE tblSmetki.Smetka_Broj=" & SmetkaBroj
    Tekst = Tekst & " za Smetka Br: " & SmetkaBroj
End If
SQLSmetka = "SELECT tblsmetki_stavki.Stavka, Sum(tblsmetki_stavki.Kolicina) AS KOL," _
          & " tblsmetki_stavki.Ed_Cena, [KOL]*[Ed_cena] AS VUkupno, " & SQL1 & "," _
          & " tblSmetki.Rabat, [suma]-[rabat] AS Za_Naplatu" _
          & " FROM tblTarifi INNER JOIN (tblSmetki INNER JOIN tblsmetki_stavki ON" _
          & " tblSmetki.ID_Smetka = tblsmetki_stavki.Smetka_Br) ON tblTarifi.Tarifa = tblsmetki_stavki.DDV " _
          & SQL2 _
          & " GROUP BY tblsmetki_stavki.Stavka, tblsmetki_stavki.Ed_Cena, tblSmetki.Rabat"
          
Set cn = CurrentProject.Connection
Forms!F.T = SQLSmetka
rs.Open SQLSmetka, cn
Close #1
Open CurrentProject.Path & "\stampa.txt" For Output As 1

If rs.BOF = True Then
MsgBox "Nema podataka"
Exit Function
End If
 rs.MoveFirst
 Temp = Space(5) & Tekst
 Print #1, Temp
 Temp = " rb    Naziv                          Kol.   Cena   Vkupno "
 Print #1, Temp
 Temp = "--------------------------------------------------------"
 Print #1, Temp
   Do While Not rs.EOF
   Rb = Rb + 1
   Temp = Rb & Space(5)
   Naziv = DLookup("Naziv", "tblArtikli_Prodazba", "ID_ArtikalP=" & rs!Stavka) & Space(5)
   Temp = Temp & Naziv
   Temp = Temp & rs!Kol & Space(5)
   Temp = Temp & rs!Ed_cena & Space(5)
   Temp = Temp & rs!Vukupno
   Print #1, Temp
   rs.MoveNext
   Loop
   rs.MoveFirst
   Temp = "------------------------------------------------------"
    Print #1, Temp

   Temp = "Vkupno:" & rs!Suma
   Print #1, Temp
   Temp = "Popust  :" & rs!Rabat
   Print #1, Temp
   Temp = "Za naplata :" & rs!Za_Naplatu
   Print #1, Temp
   rs.Close
   SQLSmetka = "SELECT A.DDV, Sum(A.Kolicina) AS KOL, Avg(A.Ed_Cena) AS Cena, tblTarifi.Koeficient," _
              & " [kol]*[Cena] AS VUkupno," & SQL1 & ", Avg(tblSmetki.Rabat) AS Rab," _
              & " [suma]-[rab] AS Za_Naplatu, [Suma]/[VUkupno] AS Procenat," _
              & " [Za_Naplatu]/[procenat] AS N_Sa_Pdv, [N_Sa_Pdv]/[Koeficient] AS N_Bez_PDV, [N_Sa_PDV]-[N_Bez_Pdv] AS PDV_Iznos" _
              & " FROM tblTarifi INNER JOIN (tblSmetki INNER JOIN tblsmetki_stavki AS A ON tblSmetki.ID_Smetka = A.Smetka_Br) ON tblTarifi.Tarifa = A.DDV " _
              & SQL2 _
              & " GROUP BY A.DDV, tblTarifi.Koeficient"
  rs.Open SQLSmetka, cn
  rs.MoveFirst
 Temp = "-------------------------------------------"
 Print #1, Temp
 Temp = "PDV    BezPDV      VK.PDV     VK.SoPDV"
 Print #1, Temp
 Temp = "-------------------------------------------"
 Print #1, Temp
 Do While Not rs.EOF
    Temp = rs!DDV & Space(5)
    Temp = Temp & Format(rs!N_Sa_PDV, "0.00") & Space(5)
    Temp = Temp & Format(rs!N_Bez_PDV, "0.00") & Space(5)
    Temp = Temp & Format(rs!PDV_Iznos, "0.00") & Space(5)
    Print #1, Temp
    Suma(0) = Suma(0) + rs!N_Sa_PDV
    Suma(1) = Suma(1) + rs!N_Bez_PDV
    Suma(2) = Suma(2) + rs!PDV_Iznos
    
    rs.MoveNext
  Loop
  rs.Close
Temp = "-------------------------------------------"
Print #1, Temp
Temp = Space(7) & Format(Suma(0), "0.00") & Space(5) & Format(Suma(1), "0.00") & Space(5) & Format(Suma(2), "0.00")
Print #1, Temp
Close #1
End Function