Prikazi cijelu temu 16.05.2014 02:32
zxz Van mreze
Administrator
Registrovan od:03.02.2009
Lokacija:Tuzla


Predmet:Re: Presmetka DPH (DDV) , (PDV) vo cod so popust
Evo procedura.
Moras je prilagoditi svojoj konekciji i svome ini fajlu.
Ja sam je radio u ovoj bazi sto si ti poslao.

PreuzmiIzvorni kôd (Visual Basic):
  1. Function Smetka_Rekap(SmetkaBroj As Integer, Optional Datum1 As Date, Optional Datum2 As Date)
  2. Dim cn As New ADODB.Connection
  3. Dim rs As New ADODB.Recordset
  4. Dim SQLSmetka As String, SQL1 As String, SQL2 As String
  5. Dim Rb As Integer, PDV As Integer
  6. Dim Naziv As String * 35
  7. Dim Dat1 As String, Dat2 As String
  8. Dim Temp As String, Tekst As String
  9. Dim Suma(2) As Currency
  10.  
  11. SQLSmetka = "SELECT tblsmetki_stavki.Stavka, Sum(tblsmetki_stavki.Kolicina) AS KOL," _
  12.           & " tblsmetki_stavki.Ed_Cena, [KOL]*[Ed_cena] AS VUkupno, " & SQL1 & "," _
  13.           & " tblSmetki.Rabat AS Popust, [suma]-[popust] AS Za_Naplatu" _
  14.           & " FROM tblTarifi INNER JOIN (tblSmetki INNER JOIN tblsmetki_stavki ON" _
  15.           & " tblSmetki.ID_Smetka = tblsmetki_stavki.Smetka_Br) ON tblTarifi.Tarifa = tblsmetki_stavki.DDV" _
  16.           & SQL2 _
  17.           & " GROUP BY tblsmetki_stavki.Stavka, tblsmetki_stavki.Ed_Cena, tblSmetki.Rabat"
  18.  
  19. Tekst = "REKAPITULACIJA"
  20. If SmetkaBroj = 0 Then
  21.      If Datum1 = 0 Then Datum1 = Date
  22.      If Datum2 = 0 Then Datum2 = Datum1 + 1
  23.     Dat1 = "#" & Format(Datum1, "mm-dd-yyyy") & "#"
  24.     Dat2 = "#" & Format(Datum2, "mm-dd-yyyy") & "#"
  25.     SQL1 = "(SELECT Sum([kolicina]*[Ed_cena]) AS suma" _
  26.        & " FROM tblSmetki INNER JOIN tblsmetki_stavki ON tblSmetki.ID_Smetka = tblsmetki_stavki.Smetka_Br" _
  27.        & " WHERE tblSmetki.Data Between " & Dat1 & " And " & Dat2 & ") AS suma"
  28.     SQL2 = "WHERE tblSmetki.Data Between " & Dat1 & " And " & Dat2
  29.     If Datum2 - Datum1 = 1 Then
  30.     Tekst = Tekst & " za " & Datum1
  31.     Else
  32.     Tekst = Tekst & " Od " & Datum1 & " do " & Datum2
  33.     End If
  34. Else
  35.     SQL1 = "(SELECT Sum([kolicina]*[Ed_cena]) AS suma" _
  36.        & " FROM tblSmetki INNER JOIN tblsmetki_stavki ON tblSmetki.ID_Smetka = tblsmetki_stavki.Smetka_Br" _
  37.        & " WHERE tblSmetki.Smetka_Broj=" & SmetkaBroj & ") AS Suma"
  38.     SQL2 = "WHERE tblSmetki.Smetka_Broj=" & SmetkaBroj
  39.     Tekst = Tekst & " za Smetka Br: " & SmetkaBroj
  40. End If
  41. SQLSmetka = "SELECT tblsmetki_stavki.Stavka, Sum(tblsmetki_stavki.Kolicina) AS KOL," _
  42.           & " tblsmetki_stavki.Ed_Cena, [KOL]*[Ed_cena] AS VUkupno, " & SQL1 & "," _
  43.           & " tblSmetki.Rabat, [suma]-[rabat] AS Za_Naplatu" _
  44.           & " FROM tblTarifi INNER JOIN (tblSmetki INNER JOIN tblsmetki_stavki ON" _
  45.           & " tblSmetki.ID_Smetka = tblsmetki_stavki.Smetka_Br) ON tblTarifi.Tarifa = tblsmetki_stavki.DDV " _
  46.           & SQL2 _
  47.           & " GROUP BY tblsmetki_stavki.Stavka, tblsmetki_stavki.Ed_Cena, tblSmetki.Rabat"
  48.          
  49. Set cn = CurrentProject.Connection
  50. Forms!F.T = SQLSmetka
  51. rs.Open SQLSmetka, cn
  52. Close #1
  53. Open CurrentProject.Path & "\stampa.txt" For Output As 1
  54.  
  55. If rs.BOF = True Then
  56. MsgBox "Nema podataka"
  57. Exit Function
  58. End If
  59.  rs.MoveFirst
  60.  Temp = Space(5) & Tekst
  61.  Print #1, Temp
  62.  Temp = " rb    Naziv                          Kol.   Cena   Vkupno "
  63.  Print #1, Temp
  64.  Temp = "--------------------------------------------------------"
  65.  Print #1, Temp
  66.    Do While Not rs.EOF
  67.    Rb = Rb + 1
  68.    Temp = Rb & Space(5)
  69.    Naziv = DLookup("Naziv", "tblArtikli_Prodazba", "ID_ArtikalP=" & rs!Stavka) & Space(5)
  70.    Temp = Temp & Naziv
  71.    Temp = Temp & rs!Kol & Space(5)
  72.    Temp = Temp & rs!Ed_cena & Space(5)
  73.    Temp = Temp & rs!Vukupno
  74.    Print #1, Temp
  75.    rs.MoveNext
  76.    Loop
  77.    rs.MoveFirst
  78.    Temp = "------------------------------------------------------"
  79.     Print #1, Temp
  80.  
  81.    Temp = "Vkupno:" & rs!Suma
  82.    Print #1, Temp
  83.    Temp = "Popust  :" & rs!Rabat
  84.    Print #1, Temp
  85.    Temp = "Za naplata :" & rs!Za_Naplatu
  86.    Print #1, Temp
  87.    rs.Close
  88.    SQLSmetka = "SELECT A.DDV, Sum(A.Kolicina) AS KOL, Avg(A.Ed_Cena) AS Cena, tblTarifi.Koeficient," _
  89.               & " [kol]*[Cena] AS VUkupno," & SQL1 & ", Avg(tblSmetki.Rabat) AS Rab," _
  90.               & " [suma]-[rab] AS Za_Naplatu, [Suma]/[VUkupno] AS Procenat," _
  91.               & " [Za_Naplatu]/[procenat] AS N_Sa_Pdv, [N_Sa_Pdv]/[Koeficient] AS N_Bez_PDV, [N_Sa_PDV]-[N_Bez_Pdv] AS PDV_Iznos" _
  92.               & " FROM tblTarifi INNER JOIN (tblSmetki INNER JOIN tblsmetki_stavki AS A ON tblSmetki.ID_Smetka = A.Smetka_Br) ON tblTarifi.Tarifa = A.DDV " _
  93.               & SQL2 _
  94.               & " GROUP BY A.DDV, tblTarifi.Koeficient"
  95.   rs.Open SQLSmetka, cn
  96.   rs.MoveFirst
  97.  Temp = "-------------------------------------------"
  98.  Print #1, Temp
  99.  Temp = "PDV    BezPDV      VK.PDV     VK.SoPDV"
  100.  Print #1, Temp
  101.  Temp = "-------------------------------------------"
  102.  Print #1, Temp
  103.  Do While Not rs.EOF
  104.     Temp = rs!DDV & Space(5)
  105.     Temp = Temp & Format(rs!N_Sa_PDV, "0.00") & Space(5)
  106.     Temp = Temp & Format(rs!N_Bez_PDV, "0.00") & Space(5)
  107.     Temp = Temp & Format(rs!PDV_Iznos, "0.00") & Space(5)
  108.     Print #1, Temp
  109.     Suma(0) = Suma(0) + rs!N_Sa_PDV
  110.     Suma(1) = Suma(1) + rs!N_Bez_PDV
  111.     Suma(2) = Suma(2) + rs!PDV_Iznos
  112.    
  113.     rs.MoveNext
  114.   Loop
  115.   rs.Close
  116. Temp = "-------------------------------------------"
  117. Print #1, Temp
  118. Temp = Space(7) & Format(Suma(0), "0.00") & Space(5) & Format(Suma(1), "0.00") & Space(5) & Format(Suma(2), "0.00")
  119. Print #1, Temp
  120. Close #1
  121. End Function

ako je pozoves sa:
Cal Smetka_Rekap(0)- Dobijas vrijednosti tekuceg dana
cal Smetka_Rekap(0,"10.05.2014")- dobijas vrijednosti na taj datum
Cal Smetka_Rekap(0,"03.05.2014","17.05.2014") dobijas vriijednosti za period
cal Smetka_Rekap(10)-dobijas vrijednosti za racun br 10
Cal Smetka_Rekap(10,"17.05.2014")-Opet dobijas vrijednosti za racun br 10
Podrška samo putem foruma, jer samo tako i ostali imaju koristi od toga.