| Respuesta: Actualizar Grafico de Chart
angelcne21
Disculpame por no haber aclarado que trabajo en sistemas como programador, para que descartaras un error tan basico como el Timer.
Lo primero que hago al tener un error es hacer un paso a paso, y si el problema fuera de timer lo habria notado, pero quedate tranquilo, la funcion se repite y el grafico no sufre modificacion.
================================================== =========
MaLkAvIaN_NeT
Importacion:
Código:
Imports ADODB
Imports System.Data.OleDb
Imports System.Windows.Forms.DataVisualization.Charting
Declaraciones:
Código:
Private DA As New OleDbDataAdapter(cmdSql)
Private sSql As String
Dim fecHasta As Date = Now
Dim fecDesde As Date = Now
Dim dr As OleDbDataReader
las fechas son para otros graficos que realizo con informes semanales y mensuales
Load:
Código:
Private Sub frmTableroRec_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Conectarse()
CalcularRecDiaria()
CalcularRecSemanal()
End Sub
la funcion conectarse se conecta a la bd y lo hace exitosamente, asi q el problema no esta ahi.
CalcularRecDiaria:
Código:
Private Sub CalcularRecDiaria()
Dim rsD As New DataSet
Dim rsServ As New DataSet
Dim cRedondeo As Double
Dim cTotalRendicion As Double
cmdSql.Connection = cnnSql
cmdSql.CommandType = CommandType.Text
On Error Resume Next
sSql = "drop table tmp_ResCob"
cmdSql.CommandText = sSql
cmdSql.ExecuteNonQuery()
On Error GoTo mensaje
Me.Cursor = Cursors.WaitCursor
lblProceso.Text = "Procesando Cajas..."
sSql = "create table tmp_ResCob(IdCobro int, " & _
" IdUsuario int, " & _
" FecCobro datetime, " & _
" CobroParcial bit, " & _
" IntMora bit, " & _
" IntCh bit, " & _
" IdComprobante int, " & _
" IdServicio int, " & _
" DsComp char(50), " & _
" IdCtaFact int, " & _
" Nombre char(50), " & _
" FecEmision datetime, " & _
" FecVto datetime, " & _
" Total money)"
cmdSql.CommandText = sSql
cmdSql.ExecuteNonQuery()
sSql = "select sum(D1.Importe)*-1 as Importe " & _
"from DET_COBRO D1, DET_COBRANZA D2, COBRANZA CZ " & _
"where D1.IdCobro = D2.IdCobro and " & _
" D2.IdCobranza = CZ.IdCobranza and " & _
" CZ.FecInicio between '" & Format(fecDesde, "yyyyMMdd") & "' and '" & Format(fecHasta, "yyyyMMdd 23:59") & "' AND " & _
" D1.IdTipoCobro = 5 "
cmdSql.CommandText = sSql
DA.Fill(rsD, "Recaudacion")
cRedondeo = IIf(rsD.Tables("Recaudacion").Rows.Count > 0, (rsD.Tables("Recaudacion").Rows(0)("Importe")), 0)
lblProceso.Text = "Procesando Cobros..."
sSql = "insert into tmp_ResCob(IdCobro, IdUsuario, FecCobro, CobroParcial, IntMora, IntCh, IdComprobante,IdServicio, DsComp, IdCtaFact, Nombre, FecEmision, FecVto, Total) " & _
"select CB.IdCobro, CZ.IdUsuario, CB.FecCobro, CB.CobroParcial, CC.IntMora, CC.IntCh, CC.IdComprobante,IdServicio,rtrim(TC.Abreviatura) + ' '+ rtrim(convert(char(10), CV.Numero)), CV.IdCtaFact, CV.Nombre, CV.FecEmision, CV.FecVto, CV.Total * TC.Signo as Total " & _
"from DET_COBRANZA DC, COBRO CB, COMP_COBRO CC, COMP_VENTA CV, TIPO_COMPROBANTE TC, COBRANZA CZ " & _
"where DC.IdCobro = CB.IdCobro and " & _
" DC.IdCobranza = CZ.IdCobranza and " & _
" CZ.FecInicio between '" & Format(fecDesde, "yyyyMMdd") & "' and '" & Format(fecHasta, "yyyyMMdd 23:59") & "' and " & _
" CB.IdCobro = CC.IdCobro and " & _
" CC.IdComprobante = CV.IdComprobante and " & _
" CV.IdTipoComp = TC.IdTipoComp and " & _
" TC.IdTipoComp in(1,2,3,29,30,32,33,34,35) and " & _
" CB.CobroParcial = 0 " & _
"order by DC.IdCobro"
cmdSql.CommandText = sSql
cmdSql.ExecuteNonQuery()
lblProceso.Text = "Procesando Cobros Parciales..."
sSql = "select CB.IdCobro, CV.Total, CZ.IdUsuario " & _
"from DET_COBRANZA DC, COBRO CB, COMP_COBRO CC, COMP_VENTA CV, TIPO_COMPROBANTE TC, COBRANZA CZ " & _
"where DC.IdCobranza = CZ.IdCobranza and " & _
" CZ.FecInicio between '" & Format(fecDesde, "yyyyMMdd") & "' and '" & Format(fecHasta, "yyyyMMdd 23:59") & "' and " & _
" DC.IdCobro = CB.IdCobro and " & _
" CB.CobroParcial = 1 and " & _
" CB.IdCobro = CC.IdCobro and " & _
" CC.IdComprobante = CV.IdComprobante and " & _
" CV.IdTipoComp = TC.IdTipoComp and " & _
" TC.IdTipoComp = 34 " & _
"order by DC.IdCobro"
cmdSql.CommandText = sSql
rsD.Clear()
DA.Fill(rsD, "Recaudacion")
If rsD.Tables("Recaudacion").Rows.Count > 0 Then
Dim DT As New DataTable
DT = _
rsD.Tables("Recaudacion")
Dim nombre As Long
Dim cobro As Long
Dim total As Double
Dim i As Integer = 0
For Each row As DataRow In DT.Rows
nombre = rsD.Tables("Recaudacion").Rows(i)("IdUsuario")
cobro = rsD.Tables("Recaudacion").Rows(i)("IdCobro")
total = rsD.Tables("Recaudacion").Rows(i)("Total")
sSql = "insert into tmp_ResCob(IdCobro, IdUsuario, FecCobro, CobroParcial, IntMora, IntCh, IdComprobante,IdServicio, DsComp, IdCtaFact, Nombre, FecEmision, FecVto, Total) " & _
"select CB.IdCobro," & nombre & ", CB.FecCobro, CB.CobroParcial, CC.IntMora, CC.IntCh, CC.IdComprobante,IdServicio, rtrim(TC.Abreviatura) + ' '+ rtrim(convert(char(10), CV.Numero)), CV.IdCtaFact, CV.Nombre, CV.FecEmision, CV.FecVto, " & total & " " & _
"from COBRO CB, COMP_COBRO CC, COMP_VENTA CV, TIPO_COMPROBANTE TC " & _
"where CB.IdCobro = " & cobro & " and " & _
" CB.IdCobro = CC.IdCobro and " & _
" CC.IdComprobante = CV.IdComprobante and " & _
" CV.IdTipoComp = TC.IdTipoComp and " & _
" TC.IdTipoComp <> 34"
cmdSql.CommandText = sSql
cmdSql.ExecuteNonQuery()
i = i + 1
Next
End If
lblProceso.Text = "Procesando Resumen de Caja..."
sSql = "select Sum(Total) as Total " & _
"from tmp_ResCob RE "
cmdSql.CommandText = sSql
DA.Fill(rsServ, "Recaudacion")
sSql = "select SE.IdServicio, SE.DsServicio , Sum(Total) as Total " & _
"from SERVICIOS SE, tmp_ResCob RE " & _
"where SE.idServicio = RE.idServicio " & _
"group by SE.IdServicio, SE.DsServicio " & _
"order by SE.DsServicio"
rsServ.Clear()
cmdSql.CommandText = sSql
DA.Fill(rsServ, "Recaudacion")
Graficar(rsServ)
sSql = "drop table tmp_ResCob"
cmdSql.CommandText = sSql
cmdSql.ExecuteNonQuery()
Me.Cursor = Cursors.Arrow
lblProceso.Text = "PROCESO FINALIZADO!!!"
Exit Sub
mensaje:
MsgBox("Error Nº: " & Err.Number & Chr(13) & Err.Description, vbCritical, "Error")
End Sub
aqui solo vean la ultima query, las anteriores solo trabajan con tablas temporales para mayor performance de la consulta final.
Graficar:
Código:
Private Sub Graficar(ByVal Origen As DataSet)
chrRecaudaciones.Show()
chrRecaudaciones.Series("Series1").XValueMember = "DsServicio"
chrRecaudaciones.Series("Series1").YValueMembers = "Total"
chrRecaudaciones.DataSource = Origen.Tables("Recaudacion")
lblTotal.Text = Origen.Tables("Recaudacion").Rows(0)(0)
End Sub
Timer:
Código:
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
CalcularRecDiaria()
End Sub
|