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