مع العلم أن الكود كان يحفظ الأقساط بدون حفظ عدد الأبناء ومع كثير من التعديلات صار لا يحفظ نهائيا ... صار مأساة برمجية هههههه
هذا هو الكود النهائي بعد التعديل ويعمل بكفاءة حيث يتم ضرب المبلغ الحالي في عدد الأبناء + 1 ثم جمع القيمة مع المبلغ الإجمالي
كود :
Private Sub SaveOrUpdateData()
Me.BindingContext(CType(DataGridView1.DataSource, DataTable)).EndCurrentEdit()
Dim year As String = Comboyears.Text
Dim monthIndex As Integer = ComboMonth.SelectedIndex + 1
Dim monthCol As String = "M" & monthIndex
If Con.State = ConnectionState.Closed Then Con.Open()
Dim dt As DataTable = CType(DataGridView1.DataSource, DataTable)
For Each row As DataRow In dt.Rows
If row.IsNull("FatherID") Then Continue For
Dim fid As Integer = CInt(row("FatherID"))
Dim numChildren As Integer = If(IsDBNull(row("عدد الأبناء")), 0, CInt(row("عدد الأبناء")))
Dim addAmount As Decimal = If(IsDBNull(row("المبلغ المستحق")), 0D, CDec(row("المبلغ المستحق")))
Dim monthPaid As Boolean = False
If dt.Columns.Contains("حالة الدفع") Then
monthPaid = If(IsDBNull(row("حالة الدفع")), False, CBool(row("حالة الدفع")))
End If
Using cmdCheck As New OleDbCommand("SELECT TotalAmount FROM TBL_Amount WHERE FatherID=@fid AND AddYears=@year", Con)
cmdCheck.Parameters.AddWithValue("@fid", fid)
cmdCheck.Parameters.AddWithValue("@year", year)
Dim prevTotalObj = cmdCheck.ExecuteScalar()
Dim prevTotal As Decimal = If(IsDBNull(prevTotalObj) Or prevTotalObj Is Nothing, 0D, CDec(prevTotalObj))
Dim newTotal As Decimal = prevTotal + ((numChildren + 1) * addAmount)
' التحقق من وجود السجل
Dim sqlExists As String = "SELECT COUNT(*) FROM TBL_Amount WHERE FatherID=@fid AND AddYears=@year"
Using cmdExist As New OleDbCommand(sqlExists, Con)
cmdExist.Parameters.AddWithValue("@fid", fid)
cmdExist.Parameters.AddWithValue("@year", year)
Dim exists As Integer = CInt(cmdExist.ExecuteScalar())
If exists > 0 Then
Dim sqlUpdate As String = "UPDATE TBL_Amount SET NumberChildren=@num, AddAmount=@add, TotalAmount=@tot, [" & monthCol & "]=@paid WHERE FatherID=@fid AND AddYears=@year"
Using cmdUpdate As New OleDbCommand(sqlUpdate, Con)
cmdUpdate.Parameters.AddWithValue("@num", numChildren)
cmdUpdate.Parameters.AddWithValue("@add", addAmount)
cmdUpdate.Parameters.AddWithValue("@tot", newTotal)
cmdUpdate.Parameters.AddWithValue("@paid", monthPaid)
cmdUpdate.Parameters.AddWithValue("@fid", fid)
cmdUpdate.Parameters.AddWithValue("@year", year)
cmdUpdate.ExecuteNonQuery()
End Using
Else
Dim sqlInsert As String = "INSERT INTO TBL_Amount (FatherID, AddYears, NumberChildren, AddAmount, TotalAmount, [" & monthCol & "]) VALUES (@fid, @year, @num, @add, @tot, @paid)"
Using cmdInsert As New OleDbCommand(sqlInsert, Con)
cmdInsert.Parameters.AddWithValue("@fid", fid)
cmdInsert.Parameters.AddWithValue("@year", year)
cmdInsert.Parameters.AddWithValue("@num", numChildren)
cmdInsert.Parameters.AddWithValue("@add", addAmount)
cmdInsert.Parameters.AddWithValue("@tot", newTotal)
cmdInsert.Parameters.AddWithValue("@paid", monthPaid)
cmdInsert.ExecuteNonQuery()
End Using
End If
End Using
End Using
Next
MessageBox.Show("تم الحفظ والتحديث بنجاح")
If Con.State = ConnectionState.Open Then Con.Close()
End Sub