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