Radim program za POSKASU za pekare i imam problem kod računanja ukupnog pdva za različite stope..
imam dvije tablice koje izgledaju ovako u mysql bazi:
racuni
id-------------int(11)
datum--------datetime
izdao---------text
racun_broj----int(11)
i
racun_artikli
id-------------int(11)
kod-----------int(11) (Šifra artikla)
naziv--------- text
cijena---------decimal(12,4) (Čista cijena artikla)
povratna_naknada---double (cijena povratne naknade ako je ima
pdv-----------double (pdv u obliku 0,23 ili 0,20)
kolicina--------decimal(12,3)
racun_broj-----int(11)
I sada mi treba da mi program izračuna koliki je promet bio danas ali po pdv-ovima
npr
Iznos PDVa osnovica 0:
Iznos PDVA osnovica 23:
Iznos PDVA osnovica 6:
tj ovisi koji je pdv spremnjen u redku(pdv)
ja sam pokušao preko SQL komande grupirati PDV i napravit sumu: sum(racun_artikli.cijena * racun_artikli.kolicina) i to nakraju ispada ovako:
ali imam problem što jako dugo vremena treba da ispiše te različite PDV-ove i nekad zna napisati da nije mogao dobiti odgovor od baze podataka kao previše treba vremena da baza odgovori.
Jeli se može nekako drugačije to napisati il popravit se moj kod? molim vas pomagajte mi =))))
Code:
Private Function pdv_ispis()
Dim dtpod As Date
Dim dtpdo As Date
Dim tekst As String = ""
'// Format datuma
dtpod = dtpOd2.Value.Date
Dim dtpod1 As String = Format(dtpod, "yyyy-MM-dd 0:0:0")
dtpdo = dtpDo1.Value.Date
Dim dtpdo2 As String = Format(dtpdo, "yyyy-MM-dd 23:59:59")
Dim DBCon As MySqlConnection
Dim DBCommand As MySqlCommand
Dim DBReader As MySqlDataReader
Dim naziv As Integer
Dim bezpdva As Object
Try
DBCon = New MySqlConnection(myConnString1)
DBCon.Open()
Dim strVerifyLogin As String
strVerifyLogin = "SELECT sum(racun_artikli.cijena * racun_artikli.kolicina) AS bezPDVa, racun_artikli.pdv as naziv " & _
" FROM racuni LEFT JOIN racun_artikli ON racun_artikli.racun_broj=racuni.racun_broj" & _
" WHERE (racuni.datum between '" & dtpod1 & "' and '" & dtpdo2 & "')" & _
" GROUP BY racun_artikli.pdv ORDER BY racun_artikli.pdv ASC"
DBCommand = New MySqlCommand
With DBCommand
.Connection = DBCon
.CommandText = strVerifyLogin
.CommandType = CommandType.Text
DBReader = .ExecuteReader
End With
If DBReader.HasRows = True Then
While DBReader.Read
If Not DBReader.IsDBNull(1) Then
naziv = DBReader("naziv")
Else
naziv = CInt("0")
End If
If Not DBReader.IsDBNull(0) Then
bezpdva = DBReader("bezPDVa")
Else
bezpdva = CInt("0")
End If
tekst = ("Promet bez pdv-a osnovica: " & naziv * 100 & " : " & FormatNumber(bezpdva, 2, , , TriState.False) & " kn")
End While
End If
Catch ex As InvalidOperationException
MessageBox.Show(ex.Message, "Error16", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch ex As MySqlException
MessageBox.Show(ex.Message, "Error17", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch ex As Exception ' // General exception
MessageBox.Show(ex.Message, "Error18", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
End Try
'završetak pdva
Return tekst
End Function
Private Function pdv_ispis()
Dim dtpod As Date
Dim dtpdo As Date
Dim tekst As String = ""
'// Format datuma
dtpod = dtpOd2.Value.Date
Dim dtpod1 As String = Format(dtpod, "yyyy-MM-dd 0:0:0")
dtpdo = dtpDo1.Value.Date
Dim dtpdo2 As String = Format(dtpdo, "yyyy-MM-dd 23:59:59")
Dim DBCon As MySqlConnection
Dim DBCommand As MySqlCommand
Dim DBReader As MySqlDataReader
Dim naziv As Integer
Dim bezpdva As Object
Try
DBCon = New MySqlConnection(myConnString1)
DBCon.Open()
Dim strVerifyLogin As String
strVerifyLogin = "SELECT sum(racun_artikli.cijena * racun_artikli.kolicina) AS bezPDVa, racun_artikli.pdv as naziv " & _
" FROM racuni LEFT JOIN racun_artikli ON racun_artikli.racun_broj=racuni.racun_broj" & _
" WHERE (racuni.datum between '" & dtpod1 & "' and '" & dtpdo2 & "')" & _
" GROUP BY racun_artikli.pdv ORDER BY racun_artikli.pdv ASC"
DBCommand = New MySqlCommand
With DBCommand
.Connection = DBCon
.CommandText = strVerifyLogin
.CommandType = CommandType.Text
DBReader = .ExecuteReader
End With
If DBReader.HasRows = True Then
While DBReader.Read
If Not DBReader.IsDBNull(1) Then
naziv = DBReader("naziv")
Else
naziv = CInt("0")
End If
If Not DBReader.IsDBNull(0) Then
bezpdva = DBReader("bezPDVa")
Else
bezpdva = CInt("0")
End If
tekst = ("Promet bez pdv-a osnovica: " & naziv * 100 & " : " & FormatNumber(bezpdva, 2, , , TriState.False) & " kn")
End While
End If
Catch ex As InvalidOperationException
MessageBox.Show(ex.Message, "Error16", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch ex As MySqlException
MessageBox.Show(ex.Message, "Error17", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch ex As Exception ' // General exception
MessageBox.Show(ex.Message, "Error18", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
End Try
'završetak pdva
Return tekst
End Function