codice:
For i As Integer = 0 To foundRows.Length - 1
Dim anagr2 As System.Data.OleDb.OleDbDataReader
Dim dbComm2 As OleDbCommand = New OleDbCommand("SELECT sum(quantita) as qta1, (qta1 * prezzo) as tot FROM documenti_acq where codice=@cod and data between @dat_i and @dat_f group by codice,prezzo", conn)
dbComm2.Parameters.AddWithValue("@cod", foundRows(i)(0))
dbComm2.Parameters.AddWithValue("@dat_i", ToEng1(dtinizio.Text))
dbComm2.Parameters.AddWithValue("@dat_f", ToEng1(dtfine.Text))
dbComm2.CommandType = CommandType.Text
Try
anagr2 = dbComm2.ExecuteReader()
Catch ex As Exception
Exit Sub
End Try
If anagr2.Read Then
Try
q_car = anagr2("qta1")
tot_car = CDec(anagr2("tot"))
sub_tot_car = sub_tot_car + tot_car
sub_q_car = sub_q_car + q_car
Catch
tot_car = "0,00"
q_car = 0
End Try
Else
Try
q_car = foundRows(i)(3)
tot_car = CDec(foundRows(i)(4)) * q_car
sub_tot_car = sub_tot_car + tot_car
sub_q_car = sub_q_car + q_car
Catch
tot_car = "0,00"
q_car = 0
End Try
End If
Dim anagr3 As System.Data.OleDb.OleDbDataReader
Dim dbComm3 As OleDbCommand = New OleDbCommand("SELECT sum(qta) as qt,sum(prezzoV_1) as prezzov FROM scarico where codice=@cod and data >= #" & ToEng1(dtinizio_s.Text) & "# and data<=#" & ToEng1(dtfine_s.Text) & "#", conn)
dbComm3.Parameters.AddWithValue("@cod", foundRows(i)(0)) '
'dbComm2.Parameters.AddWithValue("@dat_i", dtinizio.Text) ' and data between @dat_i and @dat_f group by codice
' dbComm2.Parameters.AddWithValue("@dat_f", dtfine.Text)
'dbComm2.Parameters.AddWithValue("@dat_i", ToEng1(dtinizio.Text))
'dbComm2.Parameters.AddWithValue("@dat_f", ToEng1(dtfine.Text))
dbComm3.CommandType = CommandType.Text
Try
anagr3 = dbComm3.ExecuteReader()
Dim prezzo As Decimal = "0,00"
If anagr3.Read Then
Try
tot_scar = anagr3("prezzov") / iva
q_scar = anagr3("qt")
sub_tot_scar = sub_tot_scar + tot_scar
sub_q_scar = sub_q_scar + q_scar
'prezzo = anagr3("prezzoV_1")
Catch
tot_scar = "0,00"
q_scar = 0
prezzo = "0,00"
End Try
Try
q_res = foundRows(i)(3)
tot_res = CDec(foundRows(i)(4) * q_res)
sub_tot_res = sub_tot_res + tot_res
sub_q_res = sub_q_res + q_res
Catch
tot_res = "0,00"
End Try
Else
Try
q_res = foundRows(i)(3)
tot_res = CDec(foundRows(i)(4) * q_res)
sub_tot_res = sub_tot_res + tot_res
sub_q_res = sub_q_res + q_res
Catch
tot_res = "0,00"
End Try
End If
Catch ex As Exception
' Exit Sub
End Try
margine = CDec(tot_scar) - (CDec(foundRows(i)(4)) * q_scar)
sub_margine = sub_margine + CDec(tot_scar) - (CDec(foundRows(i)(4)) * q_scar)
DataGrid.Rows.Add(foundRows(i)(0), foundRows(i)(1), foundRows(i)(8), foundRows(i)(6), q_car, FormatNumber(CDec(tot_car), 2, TriState.UseDefault), q_scar, FormatNumber(CDec(tot_scar), 2, TriState.UseDefault), FormatNumber(CDec(tot_scar) - CDec(tot_car), 2, TriState.UseDefault), q_res, FormatNumber(CDec(tot_res), 2, TriState.UseDefault), FormatNumber(CDec(margine), 2, TriState.UseDefault))
Try
If foundRows(i)(8) <> foundRows(i + 1)(8) Then
tot_q_car = tot_q_car + sub_q_car
tot_q_scar = tot_q_scar + sub_q_scar
tot_q_res = tot_q_res + sub_q_res
tot_tot_car = tot_tot_car + sub_tot_car
tot_tot_scar = tot_tot_scar + sub_tot_scar
tot_tot_res = tot_tot_res + sub_tot_res
tot_tot_mar = tot_tot_mar + sub_margine
'DataGrid.RowsDefaultCellStyle.BackColor = Color.Yellow
DataGrid.Rows.Add(">Sub_Totali", "---Categoria---" & UCase(foundRows(i)(8)), UCase(foundRows(i)(8)), foundRows(i)(6), sub_q_car, FormatNumber(CDec(sub_tot_car), 2, TriState.UseDefault), sub_q_scar, FormatNumber(CDec(sub_tot_scar), 2, TriState.UseDefault), FormatNumber(CDec(sub_tot_scar) - CDec(sub_tot_car), 2, TriState.UseDefault), sub_q_res, FormatNumber(CDec(sub_tot_res), 2, TriState.UseDefault), FormatNumber(CDec(sub_margine), 2, TriState.UseDefault))
'DataGrid.RowsDefaultCellStyle.BackColor = Color.White
sub_q_car = 0
sub_q_scar = 0
sub_q_res = 0
sub_tot_car = "0,00"
sub_tot_scar = "0,00"
sub_tot_res = "0,00"
margine = "0,00"
sub_margine = "0,00"
MsgBox(UCase(foundRows(i)(8)) & i)
'MsgBox(i)
End If
Catch
DataGrid.Rows.Add(">Sub_Totali", "---Categoria---" & UCase(foundRows(i)(8)), UCase(foundRows(i)(8)), foundRows(i)(6), sub_q_car, FormatNumber(CDec(sub_tot_car), 2, TriState.UseDefault), sub_q_scar, FormatNumber(CDec(sub_tot_scar), 2, TriState.UseDefault), FormatNumber(CDec(sub_tot_scar) - CDec(tot_car), 2, TriState.UseDefault), sub_q_res, FormatNumber(CDec(sub_tot_res), 2, TriState.UseDefault), FormatNumber(CDec(sub_margine), 2, TriState.UseDefault))
tot_q_car = tot_q_car + sub_q_car
tot_q_scar = tot_q_scar + sub_q_scar
tot_q_res = tot_q_res + sub_q_res
tot_tot_car = tot_tot_car + sub_tot_car
tot_tot_scar = tot_tot_scar + sub_tot_scar
tot_tot_res = tot_tot_res + sub_tot_res
tot_tot_mar = tot_tot_mar + sub_margine
sub_q_car = 0
sub_q_scar = 0
sub_q_res = 0
sub_tot_car = "0,00"
sub_tot_scar = "0,00"
sub_tot_res = "0,00"
margine = "0,00"
sub_margine = "0,00"
'MsgBox(i)
MsgBox(UCase(foundRows(i)(8)) & i)
End Try
q_scar = 0
tot_scar = "0,00"
q_car = 0
tot_car = "0,00"
q_res = 0
tot_res = "0,00"
conta_n.Text = i
Next
MsgBox(conta_n.Text)
DataGrid.Rows.Add(">Totali", "", "", "", tot_q_car, FormatNumber(CDec(tot_tot_car), 2, TriState.UseDefault), tot_q_scar, FormatNumber(CDec(tot_tot_scar), 2, TriState.UseDefault), FormatNumber(CDec(tot_tot_scar) - CDec(tot_tot_car), 2, TriState.UseDefault), tot_q_res, FormatNumber(CDec(tot_tot_res), 2, TriState.UseDefault), FormatNumber(CDec(tot_tot_mar), 2, TriState.UseDefault))
Rispondi quotando