Bu hesaplamayı yaptığım form sayfam şu şekilde.
Dim baglanti As New OleDbConnection
Dim komut, cmd As New OleDb.OleDbCommand
Dim okuyucu, rd As OleDbDataReader
baglanti.ConnectionString = Opet_tasiyici.baglanti_string
baglanti.Open()
cmd.Connection = baglanti
komut.Connection = baglanti
komut.CommandText = "SELECT * from urunler order by barkod asc"
okuyucu = komut.ExecuteReader
Dim sayi = 1
While okuyucu.Read
sayi = sayi + 1
End While
okuyucu.Close()
ProgressBar1.Maximum = sayi
okuyucu = komut.ExecuteReader
sayi = 1
Dim barkod As String
Dim alinan, iade, satilan, fark As Integer
Dim fiyat As Decimal
DataGridView1.Rows.Clear()
While okuyucu.Read
barkod = okuyucu("barkod")
fiyat = okuyucu("alis")
alinan = 0
iade = 0
satilan = 0
fark = 0
DataGridView1.Rows.Add()
DataGridView1.Item(0, sayi - 1).Value = barkod
DataGridView1.Item(11, sayi - 1).Value = fiyat & " TL"
DataGridView1.Item(1, sayi - 1).Value = okuyucu("stok_adi")
cmd.CommandText = "SELECT sum(alinan_adet)as alinan from stok where alinan_adet>0 and durum=true and barkod='" & barkod & "' and tarih<#" & ay & "/1/" & yil & "#"
rd = cmd.ExecuteReader
While rd.Read
If IsNumeric(rd("alinan")) Then alinan = rd("alinan")
End While
rd.Close()
cmd.CommandText = "SELECT sum(alinan_adet)as iade from stok where alinan_adet<0 and durum=true and barkod='" & barkod & "' and tarih<#" & ay & "/1/" & yil & "#"
rd = cmd.ExecuteReader
While rd.Read
If IsNumeric(rd("iade")) Then
iade = rd("iade")
End If
End While
rd.Close()
cmd.CommandText = "SELECT sum(satilan_adet)as satilan from stok where durum=false and barkod='" & barkod & "' and tarih<#" & ay & "/1/" & yil & "#"
rd = cmd.ExecuteReader
While rd.Read
If IsNumeric(rd("satilan")) Then satilan = rd("satilan")
End While
rd.Close()
fark = alinan + iade - satilan
DataGridView1.Item(2, sayi - 1).Value = alinan
DataGridView1.Item(3, sayi - 1).Value = -1 * iade
DataGridView1.Item(4, sayi - 1).Value = satilan
DataGridView1.Item(5, sayi - 1).Value = fark
alinan = 0
iade = 0
satilan = 0
fark = 0
If ay = 12 Then
cmd.CommandText = "SELECT sum(alinan_adet)as alinan from stok where alinan_adet>0 and durum=true and barkod='" & barkod & "' and tarih>=#" & ay & "/1/" & yil & "#" & " and tarih<#" & "1/1/" & yil + 1 & "#"
Else
cmd.CommandText = "SELECT sum(alinan_adet)as alinan from stok where alinan_adet>0 and durum=true and barkod='" & barkod & "' and tarih>=#" & ay & "/1/" & yil & "#" & " and tarih<#" & ay + 1 & "/1/" & yil & "#"
End If
rd = cmd.ExecuteReader
While rd.Read
If IsNumeric(rd("alinan")) Then alinan = rd("alinan")
End While
rd.Close()
If ay = 12 Then
cmd.CommandText = "SELECT sum(alinan_adet)as iade from stok where alinan_adet<0 and durum=true and barkod='" & barkod & "' and tarih>=#" & ay & "/1/" & yil & "#" & " and tarih<#" & "1/1/" & yil + 1 & "#"
Else
cmd.CommandText = "SELECT sum(alinan_adet)as iade from stok where alinan_adet<0 and durum=true and barkod='" & barkod & "' and tarih>=#" & ay & "/1/" & yil & "#" & " and tarih<#" & ay + 1 & "/1/" & yil & "#"
End If
rd = cmd.ExecuteReader
While rd.Read
If IsNumeric(rd("iade")) Then
iade = rd("iade")
End If
End While
rd.Close()
If ay = 12 Then
cmd.CommandText = "SELECT sum(satilan_adet)as satilan from stok where durum=false and barkod='" & barkod & "' and tarih>=#" & ay & "/1/" & yil & "#" & " and tarih<#" & "1/1/" & yil + 1 & "#"
Else
cmd.CommandText = "SELECT sum(satilan_adet)as satilan from stok where durum=false and barkod='" & barkod & "' and tarih>=#" & ay & "/1/" & yil & "#" & " and tarih<#" & ay + 1 & "/1/" & yil & "#"
End If
rd = cmd.ExecuteReader
While rd.Read
If IsNumeric(rd("satilan")) Then satilan = rd("satilan")
End While
rd.Close()
fark = alinan + iade - satilan
DataGridView1.Item(6, sayi - 1).Value = alinan
DataGridView1.Item(7, sayi - 1).Value = -1 * iade
DataGridView1.Item(8, sayi - 1).Value = satilan
DataGridView1.Item(9, sayi - 1).Value = fark
DataGridView1.Item(10, sayi - 1).Value = DataGridView1.Item(5, sayi - 1).Value + fark
DataGridView1.Item(12, sayi - 1).Value = Convert.ToInt16(DataGridView1.Item(10, sayi - 1).Value) * fiyat
DataGridView1.Item(12, sayi - 1).Value = DataGridView1.Item(12, sayi - 1).Value & " TL"
sayi = sayi + 1
ProgressBar1.Value += 1
End While
okuyucu.Close()
baglanti.Close()
ProgressBar1.Value = 0
ProgressBar1.Visible = False
Else
MsgBox("Lütfen Ay ve Tarih Bilgilerini Seçiniz")
End If
End Sub