Gjoreski 16.05.2014 17:25
Predmet:Re: Presmetka DPH (DDV) , (PDV) vo cod so popust

Poleto Rabat mi treba bidejki rabatot seuste ne e upisan na smetkata , Rabatot se upisuva na smetkata od koga ke se isprinta.

Ova e napraveno taka bidejki ako se sluci da ne se isprinta smetkata od bilo koi pricini ovaa smetka ostanuva otvorena.
Ako e vo red printajneto togas vo smetkata se upisuva rabatot ,nacinot na plakajne i se zatvora.

zxz 16.05.2014 17:39
Predmet:Re: Presmetka DPH (DDV) , (PDV) vo cod so popust

Citat:
suma]-" & Rabat & " AS Za_Naplatu, [Suma]/[VUkupno
Imas ga ovdje u memoriskoj a odakle ga iscitavas neznam.
Samo bi ja bio oprezan sa ovim, naime nije problem kada se radi o jednom racunu ali kada imas vise racuna kada odaberes rekapitulaciju za odredjeni period.

Gjoreski 16.05.2014 17:47
Predmet:Re: Presmetka DPH (DDV) , (PDV) vo cod so popust

Eve vaka :
Ovaa rekapitulacija e na nivo na smetkata zatoa e i vaka reseno , za presmetka na rekapitulacii za pogolem period imam stor proceduri na SQL serverot.
Ovaj program e samo kasa za prodazba, a podatocite od ovaa bara odnosno samo prodazbata se prefrlaat na SQL server kade ima kompletna Maerijalna evidencija

Rabatot e kako promenliva na vrvot od funkcijata
Eve ovde
Function PrintSmetka(SmetkaBroj As Long, Rabat As Double, Storno As Boolean)

Gjoreski 15.05.2014 12:38
Predmet:Re: Presmetka DPH (DDV) , (PDV) vo cod so popust

eve Primer vo txt kako treba da izgleda so popust i bez popust i excel kako treba da izgleda presmetkata
Prilozi:
Racun bez popust.txt (Velicina datoteke:865 Bajt)
Racun so popust.txt (Velicina datoteke:863 Bajt)
Presmetka PDV so Popust .rar (Velicina datoteke:4.40 KB)

zxz 15.05.2014 14:07
Predmet:Re: Presmetka DPH (DDV) , (PDV) vo cod so popust

Sve ja ovo sada jasno i napravio sam SQL ali me interesuje jeli rekapitulacija ide samo za odredjeni dan ili i za odredjeni racun.

Rekapitulacija je obicno samo dnevna.

Gjoreski 15.05.2014 14:12
Predmet:Re: Presmetka DPH (DDV) , (PDV) vo cod so popust

samo odreden racun so odreden ID ,no ne e loso da go imam i periodicno ako e mozno vo drug sql

zxz 16.05.2014 01:32
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

Gjoreski 16.05.2014 09:58
Predmet:Re: Presmetka DPH (DDV) , (PDV) vo cod so popust

Ova funkcionira no ne mozam da se snajdam da ja izdvojam samo rekapitulacijata.
Mi treba da ja vnesam vo gornata funkcija.

zxz 16.05.2014 10:08
Predmet:Re: Presmetka DPH (DDV) , (PDV) vo cod so popust

Daj zakaci svoj primjer sto radis i napisi gdje treba.

zxz 16.05.2014 10:38
Predmet:Re: Presmetka DPH (DDV) , (PDV) vo cod so popust

Evo probaj ovo implementirati:
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 rs = New ADODB.Recordset
  50.  rs.Open SQLSmetkaStavki, cn, adOpenStatic, adLockOptimistic
  51.  
  52. If SelectPrinter(ReadIniValue(App.Path & "\Setup.ini", "Printeri", "Smetka")) = True Then
  53.         Call MsgBox("PRINTER NOT FOUND  ", vbOKOnly + vbExclamation + vbApplicationModal + vbDefaultButton1, "")
  54.         Exit Function
  55.       End If
  56.   '**************************************************************************
  57.    
  58.     With Printer.Font
  59.         .Name = "Times New Roman"
  60.        ' .Name = "Arial"
  61.        .Size = ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "SizeFont")
  62.     End With
  63.     '.....................................zaglavle tvoje
  64. Printer.Print Space(LevMargin) & "*******************************"
  65. Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Header1")
  66. Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Header2")
  67. Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Header3")
  68. Printer.Print Space(LevMargin) & "-----------------------------------------------"
  69. '...........................................................................
  70. If rs.BOF = True Then
  71. MsgBox "Nema podataka"
  72. Exit Function
  73. End If
  74. rs.MoveFirst
  75. Printer.Print Space(LevMargin) & Space(5) & Tekst
  76. Printer.Print Space(LevMargin) & " rb    Naziv                      Kol.   Cena   Vkupno "
  77. Printer.Print Space(LevMargin) & "-----------------------------------------------"
  78.  
  79.    Do While Not rs.EOF
  80.    Rb = Rb + 1
  81.    Temp = Rb & Space(5)
  82.    Naziv = DLookup("Naziv", "tblArtikli_Prodazba", "ID_ArtikalP=" & rs!Stavka) & Space(5)
  83.    Temp = Temp & Naziv
  84.    Temp = Temp & rs!Kol & Space(5)
  85.    Temp = Temp & rs!Ed_cena & Space(5)
  86.    Temp = Temp & rs!Vukupno
  87.    Printer.Print Space(LevMargin) & Temp
  88.    rs.MoveNext
  89.    Loop
  90.    rs.MoveFirst
  91.    Temp = "------------------------------------------------------"
  92.   Printer.Print Space(LevMargin) & Temp
  93.  
  94.    Temp = "Vkupno:" & rs!Suma
  95.    Printer.Print Space(LevMargin) & Temp
  96.    Temp = "Popust  :" & rs!Rabat
  97.    Printer.Print Space(LevMargin) & Temp
  98.    Temp = "Za naplata :" & rs!Za_Naplatu
  99.    Printer.Print Space(LevMargin) & Temp
  100.    rs.Close
  101.    SQLSmetka = "SELECT A.DDV, Sum(A.Kolicina) AS KOL, Avg(A.Ed_Cena) AS Cena, tblTarifi.Koeficient," _
  102.               & " [kol]*[Cena] AS VUkupno," & SQL1 & ", Avg(tblSmetki.Rabat) AS Rab," _
  103.               & " [suma]-[rab] AS Za_Naplatu, [Suma]/[VUkupno] AS Procenat," _
  104.               & " [Za_Naplatu]/[procenat] AS N_Sa_Pdv, [N_Sa_Pdv]/[Koeficient] AS N_Bez_PDV, [N_Sa_PDV]-[N_Bez_Pdv] AS PDV_Iznos" _
  105.               & " FROM tblTarifi INNER JOIN (tblSmetki INNER JOIN tblsmetki_stavki AS A ON tblSmetki.ID_Smetka = A.Smetka_Br) ON tblTarifi.Tarifa = A.DDV " _
  106.               & SQL2 _
  107.               & " GROUP BY A.DDV, tblTarifi.Koeficient"
  108.   rs.Open SQLSmetka, cn
  109.   rs.MoveFirst
  110.  Temp = "-------------------------------------------"
  111.  Printer.Print Space(LevMargin) & Temp
  112.  Temp = "PDV    BezPDV      VK.PDV     VK.SoPDV"
  113.  Printer.Print Space(LevMargin) & Temp
  114.  Temp = "-------------------------------------------"
  115.  Printer.Print Space(LevMargin) & Temp
  116.  Do While Not rs.EOF
  117.     Temp = rs!DDV & Space(5)
  118.     Temp = Temp & Format(rs!N_Sa_PDV, "0.00") & Space(5)
  119.     Temp = Temp & Format(rs!N_Bez_PDV, "0.00") & Space(5)
  120.     Temp = Temp & Format(rs!PDV_Iznos, "0.00") & Space(5)
  121.     Printer.Print Space(LevMargin) & Temp
  122.     Suma(0) = Suma(0) + rs!N_Sa_PDV
  123.     Suma(1) = Suma(1) + rs!N_Bez_PDV
  124.     Suma(2) = Suma(2) + rs!PDV_Iznos
  125.    
  126.     rs.MoveNext
  127.   Loop
  128.   rs.Close
  129. Temp = "-------------------------------------------"
  130. Printer.Print Space(LevMargin) & Temp
  131. Temp = Space(7) & Format(Suma(0), "0.00") & Space(5) & Format(Suma(1), "0.00") & Space(5) & Format(Suma(2), "0.00")
  132. Printer.Print Space(LevMargin) & Temp
  133.  
  134. '...................... Podnozje
  135. Printer.Print Space(LevMargin) & "-----------------------------------------------"
  136.  
  137. Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Footer1")
  138. Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Footer2")
  139. Printer.Print Space(LevMargin) & ReadIniValue(App.Path & "\Setup.ini", "SmetkaSetup", "Footer3")
  140. Printer.Print Space(LevMargin) & "*******************************"
  141. Printer.EndDoc
  142. End Function