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 rs = New ADODB.Recordset
 rs.Open SQLSmetkaStavki, cn, adOpenStatic, adLockOptimistic

If SelectPrinter(ReadIniValue(App.Path & "\Setup.ini", "Printeri", "Smetka")) = True Then
        Call MsgBox("PRINTER NOT FOUND  ", vbOKOnly + vbExclamation + vbApplicationModal + vbDefaultButton1, "")
        Exit Function
      End If
  '**************************************************************************
    
    With Printer.Font
        .Name = "Times New Roman"
       ' .Name = "Arial"
        .Size = ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "SizeFont")
    End With
    '.....................................zaglavle tvoje
Printer.Print Space(LevMargin) & "*******************************"
Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Header1")
Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Header2")
Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Header3")
Printer.Print Space(LevMargin) & "-----------------------------------------------"
'...........................................................................
If rs.BOF = True Then
MsgBox "Nema podataka"
Exit Function
End If
rs.MoveFirst
Printer.Print Space(LevMargin) & Space(5) & Tekst
Printer.Print Space(LevMargin) & " rb    Naziv                      Kol.   Cena   Vkupno "
Printer.Print Space(LevMargin) & "-----------------------------------------------"

   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
   Printer.Print Space(LevMargin) & Temp
   rs.MoveNext
   Loop
   rs.MoveFirst
   Temp = "------------------------------------------------------"
  Printer.Print Space(LevMargin) & Temp

   Temp = "Vkupno:" & rs!Suma
   Printer.Print Space(LevMargin) & Temp
   Temp = "Popust  :" & rs!Rabat
   Printer.Print Space(LevMargin) & Temp
   Temp = "Za naplata :" & rs!Za_Naplatu
   Printer.Print Space(LevMargin) & 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 = "-------------------------------------------"
 Printer.Print Space(LevMargin) & Temp
 Temp = "PDV    BezPDV      VK.PDV     VK.SoPDV"
 Printer.Print Space(LevMargin) & Temp
 Temp = "-------------------------------------------"
 Printer.Print Space(LevMargin) & 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)
    Printer.Print Space(LevMargin) & 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 = "-------------------------------------------"
Printer.Print Space(LevMargin) & Temp
Temp = Space(7) & Format(Suma(0), "0.00") & Space(5) & Format(Suma(1), "0.00") & Space(5) & Format(Suma(2), "0.00")
Printer.Print Space(LevMargin) & Temp

'...................... Podnozje
Printer.Print Space(LevMargin) & "-----------------------------------------------"
 
Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Footer1")
Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Footer2")
Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Footer3")
Printer.Print Space(LevMargin) & "*******************************"
Printer.EndDoc
End Function