none
Informe RDLC desde DGW VB.NET no muestra todas las tablas que paso a traves de DataTable (3 DGW y 3 DT) RRS feed

  • Pregunta

  • Estimados, solicito su ayuda! tengo un codigo para una pequeña aplicacion la cual busca traer datos de 4 DB diferentes, consultando tablas identicas en estructura y datos, cargo con filtros entre fecha y nombres desde varias tablas en cada base de datos ciertos campos, y los asigno a 3 datagridview distintos, estoy buscando pasar los datos de esos 3 DGW a un reporte RDLC, para ellos genere un DS donde inclui 3 DT, que lleno con una funcion que recorre los DGW y guarda momentaneamente los datos, para asi trasapasarlos por origen de datos al informe, el problema esta en que solo visualizo la primera tabla del informe con los datos del DGW1, pero la 2da y 3ra tabla quedan vacias, les dejo el codigo completo de la app para que me den alguna idea del por que no esta mostrando los datos de la 2da y 3ra tabla o DGW3.

    Espero me puedan ayudar, y tambien espero que se encuentren muy bien los que leeran esta pregunta.

    Imports System.Windows.Forms
    Imports Microsoft.Reporting.WinForms
    Imports System.Data.SqlClient
    Imports System.Data.SqlClient.SqlDataAdapter
    Imports System.Configuration
    Public Class Consolidador
        'Este comando llama a la funcion GetAllConductores, cargando automaticamente al momento 
        'de iniciar la aplicacion el listado de Conductores de el ComboBox Conductor
        Dim fuentereme As New ReportDataSource
        Dim fuenterend As New ReportDataSource
        Dim fuentedev As New ReportDataSource
        Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Carga la lista de conductores al inciar la aplicacion para el filtro
            Try
                Conductor.DataSource = GetAllConductores()
                Conductor.DisplayMember = "U_Conductor"
                Conductor.ValueMember = "U_Conductor"
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
        Sub LLamarReporte()
            Imprime.ReportViewer1.LocalReport.DataSources.Clear()
            Imprime.ReportViewer1.LocalReport.DataSources.Add(fuentereme)
            Imprime.ReportViewer1.LocalReport.DataSources.Add(fuenterend)
            Imprime.ReportViewer1.LocalReport.DataSources.Add(fuentedev)
            Imprime.ReportViewer1.LocalReport.ReportEmbeddedResource = "ConsolidaRemesas.Imprimible.rdlc"
            Imprime.Show()
        End Sub
        Public Sub BtnBuscar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnBuscar.Click
            Try
                DataGridViewReme.DataSource = Filtrarxfechareme(FechaIniReme.Value.Date, FechaFinReme.Value, ConductorReme.Text)
                DataGridViewRend.DataSource = FiltrarxfechaRend(FechaIniRend.Value.Date, FechaFinRend.Value, ConductorRend.Text)
                DataGridViewDev.DataSource = Filtrarxfechadev(FechaIniRend.Value.Date, FechaFinRend.Value, ConductorDev.Text)
                Try ' Ejecutamos la consulta al DGV REME.
                    Dim queryreme As IEnumerable(Of Object) =
                        From row As DataGridViewRow In DataGridViewReme.Rows.Cast(Of DataGridViewRow)()
                        Where (
                                (row.Cells("Debit").Value IsNot Nothing) AndAlso
                                (row.Cells("Debit").Value IsNot DBNull.Value)
                                ) Select row.Cells("Debit").Value
                    ' Obtenemos la suma de las remesas y la asignamos al TextBox.
                    Dim resultadoreme As Integer =
                        queryreme.Sum(Function(row) Convert.ToDecimal(row))
    
                    Remesas1.Text = (resultadoreme)
                    Try ' Ejecutamos la consulta al DGV REND.
                        Dim queryviaticos As IEnumerable(Of Object) =
                            From row As DataGridViewRow In DataGridViewRend.Rows.Cast(Of DataGridViewRow)()
                            Where (
                                        (row.Cells("Debit").Value IsNot Nothing) AndAlso
                                        (row.Cells("Debit").Value IsNot DBNull.Value) And
                                        (row.Cells("U_NombGasto").Value.ToString() = ("VIATICOS"))
                                        ) Select row.Cells("Debit").Value
                        ' Obtenemos la suma de los viaticos y la asignamos al TextBox.
                        Dim resultadoviatico As Integer =
                            queryviaticos.Sum(Function(row) Convert.ToDecimal(row))
                        Viaticos1.Text = (resultadoviatico)
                        Try ' Ejecutamos la consulta al DGV REND.
                            Dim queryviaticossinviaje As IEnumerable(Of Object) =
                                From row As DataGridViewRow In DataGridViewRend.Rows.Cast(Of DataGridViewRow)()
                                Where (
                                            (row.Cells("Debit").Value IsNot Nothing) AndAlso
                                            (row.Cells("Debit").Value IsNot DBNull.Value) And
                                            (row.Cells("U_NombGasto").Value.ToString() = ("VIATICOS SIN VIAJE"))
                                            ) Select row.Cells("Debit").Value
                            ' Obtenemos la suma de los viaticos y la asignamos al TextBox.
                            Dim resultadoviaticosinviaje As Integer =
                                queryviaticossinviaje.Sum(Function(row) Convert.ToDecimal(row))
    
                            Viaticossviaje1.Text = (resultadoviaticosinviaje)
                            Try ' Ejecutamos la consulta al DGV REND.
                                Dim querypeajes As IEnumerable(Of Object) =
                            From row As DataGridViewRow In DataGridViewRend.Rows.Cast(Of DataGridViewRow)()
                            Where (
                                        (row.Cells("Debit").Value IsNot Nothing) AndAlso
                                        (row.Cells("Debit").Value IsNot DBNull.Value) And
                                        (row.Cells("U_NombGasto").Value.ToString() = ("PEAJES"))
                                        ) Select row.Cells("Debit").Value
                                ' Obtenemos la suma de los peajes y la asignamos al TextBox.
                                Dim resultadopeajes As Integer =
                            querypeajes.Sum(Function(row) Convert.ToDecimal(row))
                                Peajes1.Text = (resultadopeajes)
                                Try ' Ejecutamos la consulta al DGV REND.
                                    Dim querygastoagenerales As IEnumerable(Of Object) =
                            From row As DataGridViewRow In DataGridViewRend.Rows.Cast(Of DataGridViewRow)()
                            Where (
                                        (row.Cells("Debit").Value IsNot Nothing) AndAlso
                                        (row.Cells("Debit").Value IsNot DBNull.Value) And
                                        (row.Cells("U_NombGasto").Value.ToString.Contains("GASTOS GENERALES"))
                                        ) Select row.Cells("Debit").Value
    
                                    ' Obtenemos la suma de los gastos generales y la asignamos al TextBox.
                                    Dim resultadogastosgenerales As Integer =
                            querygastoagenerales.Sum(Function(row) Convert.ToDecimal(row))
                                    GasGen1.Text = (resultadogastosgenerales)
                                    Try ' Ejecutamos la consulta al DGV REND.
                                        Dim querymantenciones As IEnumerable(Of Object) =
                                From row As DataGridViewRow In DataGridViewRend.Rows.Cast(Of DataGridViewRow)()
                                Where (
                                            (row.Cells("Debit").Value IsNot Nothing) AndAlso
                                            (row.Cells("Debit").Value IsNot DBNull.Value) And
                                            (row.Cells("U_NombGasto").Value.ToString.Contains("MANTENCIONES Y REPARACIONES EN RUTA"))
                                            ) Select row.Cells("Debit").Value
    
                                        ' Obtenemos la suma de las mantenciones y mantenciones en ruta y la asignamos al TextBox.
                                        Dim resultadomantenciones As Integer =
                                querymantenciones.Sum(Function(row) Convert.ToDecimal(row))
                                        Mant1.Text = (resultadomantenciones)
                                        Try ' Ejecutamos la consulta al DGV REND.
                                            Dim queryrevtec As IEnumerable(Of Object) =
                                    From row As DataGridViewRow In DataGridViewRend.Rows.Cast(Of DataGridViewRow)()
                                    Where (
                                                (row.Cells("Debit").Value IsNot Nothing) AndAlso
                                                (row.Cells("Debit").Value IsNot DBNull.Value) And
                                                (row.Cells("U_NombGasto").Value.ToString.Contains("REVISION"))
                                                ) Select row.Cells("Debit").Value
                                            ' Obtenemos la suma de las revisiones tecnicas y la asignamos al TextBox.
                                            Dim resultadorevtec As Integer =
                                    queryrevtec.Sum(Function(row) Convert.ToDecimal(row))
                                            RevTec1.Text = (resultadorevtec)
                                            Try ' Ejecutamos la consulta al DGV REND.
                                                Dim querymultas As IEnumerable(Of Object) =
                                    From row As DataGridViewRow In DataGridViewRend.Rows.Cast(Of DataGridViewRow)()
                                    Where (
                                                (row.Cells("Debit").Value IsNot Nothing) AndAlso
                                                (row.Cells("Debit").Value IsNot DBNull.Value) And
                                                (row.Cells("U_NombGasto").Value.ToString.Contains("MULTAS"))
                                                ) Select row.Cells("Debit").Value
                                                ' Obtenemos la suma de las multas y la asignamos al TextBox.
                                                Dim resultadomultas As Integer =
                                    querymultas.Sum(Function(row) Convert.ToDecimal(row))
                                                Multas1.Text = (resultadomultas)
                                                Try ' Ejecutamos la consulta al DGV REND.
                                                    Dim querydevoluciones As IEnumerable(Of Object) =
                                            From row As DataGridViewRow In DataGridViewDev.Rows.Cast(Of DataGridViewRow)()
                                            Where (
                                                  (row.Cells("Debit").Value IsNot Nothing) AndAlso
                                                  (row.Cells("Debit").Value IsNot DBNull.Value)
                                     ) Select row.Cells("Debit").Value
                                                    ' Obtenemos la suma de las devoluciones y la asignamos al TextBox.
                                                    Dim resultadodevoluciones As Integer =
                                            querydevoluciones.Sum(Function(row) Convert.ToDecimal(row))
                                                    Devoluciones1.Text = (resultadodevoluciones)
                                                    Try ' Ejecutamos la consulta al DGV REND.
                                                        Dim querygasext As IEnumerable(Of Object) =
                                                From row As DataGridViewRow In DataGridViewRend.Rows.Cast(Of DataGridViewRow)()
                                                Where (
                                                      (row.Cells("Debit").Value IsNot Nothing) AndAlso
                                                      (row.Cells("Debit").Value IsNot DBNull.Value)
                                         ) Select row.Cells("Debit").Value
                                                        ' Obtenemos la suma de las gastos extras y la asignamos al TextBox.
                                                        Dim resultadogasext As Integer =
                                                querygasext.Sum(Function(row) Convert.ToDecimal(row))
                                                        GasExt1.Text = (resultadogasext)
                                                        Try ' Ejecutamos la consulta al DGV REND.
                                                            Dim querytotrend As IEnumerable(Of Object) =
                                                         From row As DataGridViewRow In DataGridViewRend.Rows.Cast(Of DataGridViewRow)()
                                                         Where (
                                                                  (row.Cells("Debit").Value IsNot Nothing) AndAlso
                                                                  (row.Cells("Debit").Value IsNot DBNull.Value)
                                                          ) Select row.Cells("Debit").Value
                                                            ' Obtenemos la suma de las rendiciones y la asignamos al TextBox.
                                                            Dim resultadototrend As Integer =
                                                         querytotrend.Sum(Function(row) Convert.ToDecimal(row))
                                                            TotRendiciones1.Text = (resultadototrend)
                                                            Try
                                                                Remesas2.Text = FormatCurrency(Remesas1.Text)
                                                                Viaticos2.Text = FormatCurrency(Viaticos1.Text)
                                                                Viaticossviaje2.Text = FormatCurrency(Viaticossviaje1.Text)
                                                                Peajes2.Text = FormatCurrency(Peajes1.Text)
                                                                Mant2.Text = FormatCurrency(Mant1.Text)
                                                                GasGen2.Text = FormatCurrency(GasGen1.Text)
                                                                RevTec2.Text = FormatCurrency(RevTec1.Text)
                                                                Multas2.Text = FormatCurrency(Multas1.Text)
                                                                Devoluciones2.Text = FormatCurrency(Devoluciones1.Text)
                                                                TotFavCon2.Text = FormatCurrency(TotFavCon1.Text)
                                                                TotRendiciones2.Text = FormatCurrency(TotRendiciones1.Text)
                                                                FavorConductorEmpresa()
                                                                Try
                                                                    Almacena()
                                                                    LLamarReporte()
                                                                Catch ex As Exception
                                                                End Try
                                                            Catch ex As Exception
                                                                MessageBox.Show(ex.Message)
                                                            End Try
                                                        Catch ex As Exception
                                                            MessageBox.Show(ex.Message)
                                                        End Try
                                                    Catch ex As Exception
                                                        MessageBox.Show(ex.Message)
                                                    End Try
                                                Catch ex As Exception
                                                    MessageBox.Show(ex.Message)
                                                End Try
                                            Catch ex As Exception
                                                MessageBox.Show(ex.Message)
                                            End Try
                                        Catch ex As Exception
                                            MessageBox.Show(ex.Message)
                                        End Try
                                    Catch ex As Exception
                                        MessageBox.Show(ex.Message)
                                    End Try
                                Catch ex As Exception
                                    MessageBox.Show(ex.Message)
                                End Try
                            Catch ex As Exception
                                MessageBox.Show(ex.Message)
                            End Try
                        Catch ex As Exception
                            MessageBox.Show(ex.Message)
                        End Try
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    End Try
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
        Sub Almacena()
            AlmacenaDev()
            AlmacenaReme()
            AlmacenaRend()
        End Sub
        Sub AlmacenaDev()
            Try
                Dim dsdev As New DataSetImprimible
                Dim dtwdev As DataRow
                For i As Integer = 0 To DataGridViewDev.Rows.Count - 1
                    dtwdev = dsdev.dtReme.NewRow()
                    dtwdev("TransId") = DataGridViewDev.Item(0, i).Value
                    dtwdev("DueDate") = DataGridViewDev.Item(1, i).Value
                    dtwdev("U_Entregado_a") = DataGridViewDev.Item(2, i).Value
                    dtwdev("Debit") = Replace(CDbl(DataGridViewDev.Item(3, i).Value), ",", ".")
                    dtwdev("Empresa") = DataGridViewDev.Item(4, i).Value
                    dsdev.dtReme.Rows.Add(dtwdev)
                Next
                fuentedev.Name = "dtDev"
                fuentedev.Value = dsdev.Tables(0)
            Catch ex As Exception
            End Try
        End Sub
        Sub AlmacenaRend()
            Try
                Dim dsrend As New DataSetImprimible
                Dim dtwrend As DataRow
                For i As Integer = 0 To DataGridViewRend.Rows.Count - 1
                    dtwrend = dsrend.dtRend.NewRow()
                    dtwrend("TransId") = DataGridViewRend.Item(0, i).Value
                    dtwrend("DueDate") = DataGridViewRend.Item(1, i).Value
                    dtwrend("U_Entregado_a") = DataGridViewRend.Item(2, i).Value
                    dtwrend("U_NombGasto") = DataGridViewRend.Item(3, i).Value
                    dtwrend("Debit") = Replace(CDbl(DataGridViewRend.Item(4, i).Value), ",", ".")
                    dtwrend("Empresa") = DataGridViewRend.Item(5, i).Value
                    dsrend.dtRend.Rows.Add(dtwrend)
                Next
                fuenterend.Name = "dtRend"
                fuenterend.Value = dsrend.Tables(0)
            Catch ex As Exception
            End Try
        End Sub
        Sub AlmacenaReme()
            Try
                Dim dsreme As New DataSetImprimible
                Dim dtwreme As DataRow
                For i As Integer = 0 To DataGridViewReme.Rows.Count - 1
                    dtwreme = dsreme.dtReme.NewRow()
                    dtwreme("TransId") = DataGridViewReme.Item(0, i).Value
                    dtwreme("DueDate") = DataGridViewReme.Item(1, i).Value
                    dtwreme("U_Entregado_a") = DataGridViewReme.Item(2, i).Value
                    dtwreme("Debit") = Replace(CDbl(DataGridViewReme.Item(3, i).Value), ",", ".")
                    dtwreme("Empresa") = DataGridViewReme.Item(4, i).Value
                    dsreme.dtReme.Rows.Add(dtwreme)
                Next
                fuentereme.Name = "dtReme"
                fuentereme.Value = dsreme.Tables(0)
            Catch ex As Exception
            End Try
        End Sub
        Public Function Filtrarxfechareme(ByVal FechaIniReme As DateTime, ByVal FechaFinReme As DateTime, ByVal ConductorReme As String) As DataTable
            Using cnx = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
                Const sqlAction As String =
    "SELECT EIRL.dbo.JDT1.TransId, EIRL.dbo.JDT1.DueDate, EIRL.dbo.JDT1.U_Entregado_a, EIRL.dbo.JDT1.Debit, 'EIRL' AS Empresa FROM EIRL.dbo.JDT1 " _
    & "INNER JOIN EIRL.dbo.OJDT On EIRL.dbo.JDT1.TransId = EIRL.dbo.OJDT.TransId " _
    & "WHERE EIRL.dbo.JDT1.Debit <> 0 AND EIRL.dbo.OJDT.U_Nulo = 'N' AND EIRL.dbo.JDT1.LineMemo LIKE '%REMESA%' AND EIRL.dbo.JDT1.DueDate BETWEEN @fechainireme AND @fechafinreme AND EIRL.dbo.JDT1.U_Entregado_a = @conductorReme " _
    & "UNION ALL Select JDT1_3.TransId, JDT1_3.DueDate, JDT1_3.U_Entregado_a, JDT1_3.Debit, 'MCR' AS Empresa FROM MCR.dbo.JDT1 AS JDT1_3 " _
    & "INNER JOIN MCR.dbo.OJDT AS OJDT_3 ON JDT1_3.TransId = OJDT_3.TransId " _
    & "WHERE JDT1_3.Debit <> 0 And OJDT_3.U_Nulo = 'N' AND JDT1_3.LineMemo LIKE '%REMESA%' AND JDT1_3.DueDate BETWEEN @fechainireme AND @fechafinreme AND JDT1_3.U_Entregado_a = @conductorReme " _
    & "UNION ALL SELECT JDT1_2.TransId, JDT1_2.DueDate, JDT1_2.U_Entregado_a, JDT1_2.Debit, 'SA' AS Empresa FROM SA.dbo.JDT1 AS JDT1_2 " _
    & "INNER JOIN SA.dbo.OJDT AS OJDT_2 ON JDT1_2.TransId = OJDT_2.TransId " _
    & "WHERE JDT1_2.Debit <> 0 And OJDT_2.U_Nulo = 'N' AND JDT1_2.LineMemo LIKE '%REMESA%' AND JDT1_2.DueDate BETWEEN @fechainireme AND @fechafinreme AND JDT1_2.U_Entregado_a = @conductorReme " _
    & "UNION ALL SELECT JDT1_1.TransId, JDT1_1.DueDate, JDT1_1.U_Entregado_a, JDT1_1.Debit, 'ADP' AS Empresa FROM ADP.dbo.JDT1 AS JDT1_1 " _
    & "INNER JOIN ADP.dbo.OJDT AS OJDT_1 ON JDT1_1.TransId = OJDT_1.TransId " _
    & "WHERE JDT1_1.Debit <> 0 And OJDT_1.U_Nulo = 'N' AND JDT1_1.LineMemo LIKE '%REMESA%' AND JDT1_1.DueDate BETWEEN @fechainireme AND @fechafinreme  AND JDT1_1.U_Entregado_a = @conductorReme " _
    & "ORDER BY DueDate"
                Using cmd As New SqlCommand(sqlAction, cnx)
                    cmd.Parameters.AddWithValue("@fechainiReme", FechaIniReme)
                    cmd.Parameters.AddWithValue("@fechafinReme", FechaFinReme)
                    cmd.Parameters.AddWithValue("@conductorReme", ConductorReme)
                    Dim dtReme As New DataTable
                    Dim daReme As New SqlDataAdapter(cmd)
                    daReme.Fill(dtReme)
                    Return dtReme
                End Using
            End Using
        End Function
        Public Function FiltrarxfechaRend(ByVal FechaIniRend As DateTime, ByVal FechaFinRend As DateTime, ByVal ConductorRend As String) As DataTable
            Using cnx = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
                Const sqlAction As String =
    "Select EIRL.dbo.JDT1.TransId, EIRL.dbo.JDT1.DueDate, EIRL.dbo.JDT1.U_Entregado_a, EIRL.dbo.JDT1.U_NombGasto, EIRL.dbo.JDT1.Debit, 'EIRL' AS Empresa FROM EIRL.dbo.JDT1 " _
    & "INNER JOIN EIRL.dbo.OJDT ON EIRL.dbo.JDT1.TransId = EIRL.dbo.OJDT.TransId " _
    & "WHERE EIRL.dbo.JDT1.Debit <> 0 AND EIRL.dbo.OJDT.U_Nulo = 'N' AND EIRL.dbo.JDT1.LineMemo LIKE '%RENDICION%' AND EIRL.dbo.JDT1.DueDate BETWEEN @fechainirend AND @fechafinrend AND EIRL.dbo.JDT1.U_Entregado_a = @conductorRend " _
    & "UNION ALL SELECT JDT1_3.TransId, JDT1_3.DueDate, JDT1_3.U_Entregado_a, JDT1_3.U_NombGasto, JDT1_3.Debit, 'MCR' AS Empresa FROM MCR.dbo.JDT1 AS JDT1_3 " _
    & "INNER JOIN MCR.dbo.OJDT AS OJDT_3 ON JDT1_3.TransId = OJDT_3.TransId " _
    & "WHERE JDT1_3.Debit <> 0 AND OJDT_3.U_Nulo = 'N' AND JDT1_3.LineMemo LIKE '%RENDICION%' AND JDT1_3.DueDate BETWEEN @fechainirend AND @fechafinrend AND JDT1_3.U_Entregado_a = @conductorRend " _
    & "UNION ALL SELECT JDT1_2.TransId, JDT1_2.DueDate, JDT1_2.U_Entregado_a, JDT1_2.U_NombGasto, JDT1_2.Debit, 'SA' AS Empresa FROM SA.dbo.JDT1 AS JDT1_2 " _
    & "INNER JOIN SA.dbo.OJDT AS OJDT_2 ON JDT1_2.TransId = OJDT_2.TransId " _
    & "WHERE JDT1_2.Debit <> 0 AND OJDT_2.U_Nulo = 'N' AND JDT1_2.LineMemo LIKE '%RENDICION%' AND JDT1_2.DueDate BETWEEN @fechainirend AND @fechafinrend AND JDT1_2.U_Entregado_a = @conductorRend " _
    & "UNION ALL SELECT JDT1_1.TransId, JDT1_1.DueDate, JDT1_1.U_Entregado_a, JDT1_1.U_NombGasto, JDT1_1.Debit, 'ADP' AS Empresa FROM ADP.dbo.JDT1 AS JDT1_1 " _
    & "INNER JOIN ADP.dbo.OJDT AS OJDT_1 ON JDT1_1.TransId = OJDT_1.TransId " _
    & "WHERE JDT1_1.Debit <> 0 AND OJDT_1.U_Nulo = 'N' AND JDT1_1.LineMemo LIKE '%RENDICION%' AND JDT1_1.DueDate BETWEEN @fechainirend AND @fechafinrend AND JDT1_1.U_Entregado_a = @conductorRend " _
    & "ORDER BY DueDate"
                Using cmd As New SqlCommand(sqlAction, cnx)
                    cmd.Parameters.AddWithValue("@fechainiRend", FechaIniRend)
                    cmd.Parameters.AddWithValue("@fechafinRend", FechaFinRend)
                    cmd.Parameters.AddWithValue("@conductorRend", ConductorRend)
                    Dim dtRend As New DataTable
                    Dim daRend As New SqlDataAdapter(cmd)
                    daRend.Fill(dtRend)
                    Return dtRend
                End Using
            End Using
        End Function
        Public Function Filtrarxfechadev(ByVal FechaIniDev As DateTime, ByVal FechaFinDev As DateTime, ByVal ConductorDev As String) As DataTable
            Using cnx = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
                Const sqlAction As String =
    "SELECT EIRL.dbo.JDT1.TransId, EIRL.dbo.JDT1.DueDate, U_Entregado_a = @conductorDev, EIRL.dbo.JDT1.Debit, 'EIRL' AS Empresa FROM EIRL.dbo.JDT1 " _
    & "INNER JOIN EIRL.dbo.OJDT On EIRL.dbo.JDT1.TransId = EIRL.dbo.OJDT.TransId " _
    & "INNER JOIN EIRL.dbo.OCRD ON EIRL.dbo.OCRD.CardCode = EIRL.dbo.JDT1.ContraAct " _
    & "WHERE EIRL.dbo.JDT1.Debit <> 0 AND EIRL.dbo.OJDT.U_Nulo = 'N' AND EIRL.dbo.OJDT.Memo LIKE '%DEVOLUCION%' AND EIRL.dbo.JDT1.DueDate BETWEEN @fechainiDev AND @fechafinDev AND EIRL.dbo.OCRD.CardName = @conductorDev " _
    & "UNION ALL Select JDT1_3.TransId, JDT1_3.DueDate, U_Entregado_a = @conductorDev, JDT1_3.Debit, 'MCR' AS Empresa FROM MCR.dbo.JDT1 AS JDT1_3 " _
    & "INNER JOIN MCR.dbo.OJDT AS OJDT_3 ON JDT1_3.TransId = OJDT_3.TransId " _
    & "INNER JOIN MCR.dbo.OCRD AS OCRD_3 ON OCRD_3.CardCode = JDT1_3.ContraAct " _
    & "WHERE JDT1_3.Debit <> 0 AND OJDT_3.U_Nulo = 'N' AND OJDT_3.Memo LIKE '%DEVOLUCION%' AND JDT1_3.DueDate BETWEEN @fechainiDev AND @fechafinDev AND OCRD_3.CardName = @conductorDev " _
    & "UNION ALL SELECT JDT1_2.TransId, JDT1_2.DueDate, U_Entregado_a = @conductorDev, JDT1_2.Debit, 'SA' AS Empresa FROM SA.dbo.JDT1 AS JDT1_2 " _
    & "INNER JOIN SA.dbo.OJDT AS OJDT_2 ON JDT1_2.TransId = OJDT_2.TransId " _
    & "INNER JOIN SA.dbo.OCRD AS OCRD_2 ON OCRD_2.CardCode = JDT1_2.ContraAct " _
    & "WHERE JDT1_2.Debit <> 0 AND OJDT_2.U_Nulo = 'N' AND OJDT_2.Memo LIKE '%DEVOLUCION%' AND JDT1_2.DueDate BETWEEN @fechainiDev AND @fechafinDev AND OCRD_2.CardName = @conductorDev " _
    & "UNION ALL SELECT JDT1_1.TransId, JDT1_1.DueDate, U_Entregado_a = @conductorDev, JDT1_1.Debit, 'ADP' AS Empresa FROM ADP.dbo.JDT1 AS JDT1_1 " _
    & "INNER JOIN ADP.dbo.OJDT AS OJDT_1 ON JDT1_1.TransId = OJDT_1.TransId " _
    & "INNER JOIN ADP.dbo.OCRD AS OCRD_1 ON OCRD_1.CardCode = JDT1_1.ContraAct " _
    & "WHERE JDT1_1.Debit <> 0 AND OJDT_1.U_Nulo = 'N' AND OJDT_1.Memo LIKE '%DEVOLUCION%' AND JDT1_1.DueDate BETWEEN @fechainiDev AND @fechafinDev AND OCRD_1.CardName = @conductorDev " _
    & "ORDER BY DueDate "
                Using cmd As New SqlCommand(sqlAction, cnx)
                    cmd.Parameters.AddWithValue("@fechainiDev", FechaIniDev)
                    cmd.Parameters.AddWithValue("@fechafinDev", FechaFinDev)
                    cmd.Parameters.AddWithValue("@conductorDev", ConductorDev)
                    Dim dtDev As New DataTable
                    Dim daDev As New SqlDataAdapter(cmd)
                    daDev.Fill(dtDev)
                    Return dtDev
                End Using
            End Using
        End Function
        Public Sub ExpExcelReme_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExpExcelReme.Click 'Llama a clase Exportar Excel
            ExportarAExcel1TodasRemesas.ExpDGVtoExcell(Me.DataGridViewReme)
        End Sub
        Public Sub ExpExcelRend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExpExcelRend.Click 'Llama a clase Exportar Excel
            ExportarAExcel2TodasRendiciones.ExpDGVtoExcell(Me.DataGridViewRend)
        End Sub
        Public Sub FechaIniRemeEIRL_ValueChanged(sender As Object, e As EventArgs) Handles FechaIniReme.ValueChanged 'Copia como texto las fechas de la primera seleccion para poder validar
            FechaIniRend.Text = FechaIniReme.Text
            FechaIniDev.Text = FechaIniReme.Text
        End Sub
        Public Sub FechaFinReme_ValueChanged(sender As Object, e As EventArgs) Handles FechaFinReme.ValueChanged 'Copia como texto las fechas de la primera seleccion para poder validar
            FechaFinRend.Text = FechaFinReme.Text
            FechaFinDev.Text = FechaFinReme.Text
        End Sub
        Public Sub Conductor_SelectedIndexChanged(sender As Object, e As EventArgs) Handles Conductor.SelectedIndexChanged 'Copia el texto del listbox conductor, y lo asigna a 3 textbox desde la primera seleccion para poder validar
            ConductorReme.Text = Conductor.Text
            ConductorRend.Text = Conductor.Text
            ConductorDev.Text = Conductor.Text
        End Sub
        Public Sub FormConsolida_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing 'Mensaje de tipo alerta al salir de la aplicacion cerrando formulario desde la X
            If e.CloseReason = CloseReason.UserClosing Then
                MessageBox.Show("Realmente desea salir de la aplicación?", "Alerta!")
            End If
        End Sub
        'Esta funcion llena el Combobox Conductor con CardName de la tabla OCRD de las 4 BD donde se estan extrayendo los nombres de los conductores (Group 104)
        ' y a la vez suprime los duplicados para mostrar una lista unificada de los conductores de las 4 BD
        Public Function GetAllConductores() As DataTable
            Using cnx = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
                Const sqlAction As String = "SELECT U_Conductor FROM EIRL.dbo.[@CONDUCTOR] " _
                                            & "UNION SELECT U_Conductor FROM MCR.dbo.[@CONDUCTOR]" _
                                            & "UNION SELECT U_Conductor FROM SA.dbo.[@CONDUCTOR]" _
                                            & "UNION SELECT U_Conductor FROM ADP.dbo.[@CONDUCTOR]" _
                                            & "ORDER BY U_Conductor ASC"
                Using cmd As New SqlCommand(sqlAction, cnx)
                    Dim dtcond As New DataTable()
                    Dim dacond As New SqlDataAdapter(cmd)
                    dacond.Fill(dtcond)
                    Return dtcond
                End Using
            End Using
        End Function
        Public Sub Remesas1_TextChanged(sender As Object, e As EventArgs) Handles Remesas1.TextChanged
            Calcular()
        End Sub
        Public Sub Viaticos1_TextChanged(sender As Object, e As EventArgs) Handles Viaticos1.TextChanged
            Calcular()
        End Sub
        Public Sub Peajes1_TextChanged(sender As Object, e As EventArgs) Handles Peajes1.TextChanged
            Calcular()
        End Sub
        Public Sub GasGen1_TextChanged(sender As Object, e As EventArgs) Handles GasGen1.TextChanged
            Calcular()
        End Sub
        Public Sub Viaticossviaje1_TextChanged(sender As Object, e As EventArgs) Handles GasExt1.TextChanged
            Calcular()
        End Sub
        Public Sub Mant1_TextChanged(sender As Object, e As EventArgs) Handles Viaticossviaje1.TextChanged
            Calcular()
        End Sub
        Public Sub RevTec1_TextChanged(sender As Object, e As EventArgs) Handles Mant1.TextChanged
            Calcular()
        End Sub
        Public Sub Multas1_TextChanged(sender As Object, e As EventArgs) Handles RevTec1.TextChanged
            Calcular()
        End Sub
        Public Sub Devoluciones1_TextChanged(sender As Object, e As EventArgs) Handles Multas1.TextChanged
            Calcular()
        End Sub
        Public Sub GasExt1_TextChanged(sender As Object, e As EventArgs) Handles Devoluciones1.TextChanged
            Calcular()
        End Sub
        Public Sub GasExt2_TextChanged(sender As Object, e As EventArgs) Handles GasExt2.TextChanged
            Calcular()
        End Sub
        Public Sub FavorConductorEmpresa() 'Rellena textbox FavConEmp preseguido del label "Saldo Favor" y asigna ya sea a Conductor / Empresa
            Dim n As Integer
            n = TotFavCon1.Text
            If (n > 0) Then
                FavConEmp.Text = " Empresa"
            Else
                If (n < 0) Then
                    FavConEmp.Text = " Conductor"
                Else
                    FavConEmp.Text = " Saldo 0"
                End If
            End If
        End Sub
        Public Sub Calcular() 'Calcula montos de remesas, gastos y devoluciones y las asigna a los textbox correspondientes
            Dim val1 As Integer = 0
            Dim val2 As Integer = 0
            Dim val3 As Integer = 0
            Dim val4 As Integer = 0
            Dim val8 As Integer = 0
            Dim val9 As Integer = 0
            Dim val10 As Integer = 0
            Dim val11 As Integer = 0
            Dim val12 As Integer = 0
            Dim val13 As Integer = 0
            Int32.TryParse(Remesas1.Text, val1)
            Int32.TryParse(Viaticos1.Text, val2)
            Int32.TryParse(Peajes1.Text, val3)
            Int32.TryParse(GasGen1.Text, val4)
            Int32.TryParse(Viaticossviaje1.Text, val8)
            Int32.TryParse(Mant1.Text, val9)
            Int32.TryParse(RevTec1.Text, val10)
            Int32.TryParse(Multas1.Text, val11)
            Int32.TryParse(Devoluciones1.Text, val12)
            Int32.TryParse(GasExt1.Text, val13)
            TotFavCon1.Text = CStr(FormatCurrency(val1 - val2 - val3 - val4 - val8 - val9 - val10 - val11 - val12))
            GasExt2.Text = CStr(FormatCurrency(val13 - val2 - val8 - val3 - val4 - val9 - val10 - val11))
        End Sub
    End Class


    Carlos Lizana Sepulveda

    • Cambiado Enrique M. Montejo domingo, 17 de junio de 2018 14:39 Pregunta relacionada con el control de informes de Windows Forms.
    jueves, 14 de junio de 2018 21:57