1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
| Dim oDataset As DataSet = New DataSet("oDataset")
Dim DateDebV As String
DateDebV = ReportAutoBq.ComboBoxVisual.SelectedItem
Try
Dim oConnection As OracleConnection = New OracleConnection()
oConnection.ConnectionString = "Server=OraOLEDB.Oracle; Data Source=X3_X32CLIP.clip-sa.local ;USER ID=odbc; PASSWORD=odbc"
oConnection.Open()
'CLIP.XDOSCLI2.XRECT1_0, CLIP.XDOSCLI2.XRECT2_0, CLIP.XDOSCLI2.XRECT3_0, CLIP.XDOSCLI2.XRECT4_0, CLIP.XDOSCLI2.XRECT5_0, CLIP.XDOSCLI2.XRECT6_0, CLIP.XDOSCLI2.XVERS1_0, CLIP.XDOSCLI2.XVERS2_0, CLIP.XDOSCLI2.XFONDCHQ_0
'FctCalcNbCoul(XRECT1_0, XRECT2_0, XRECT3_0, XRECT4_0, XRECT5_0, XRECT6_0, XVERS1_0, XVERS2_0, XFONDCHQ_0) as Nb Couleur ,
Dim oAdapt As New OracleDataAdapter("SELECT XBQABR_0, XCODBANK_0, XBQNAM_0, XCODPRO_0, CLIP.XDOSCLI.XCOMDAT_0, CLIP.XDOSCLI.XDCNUM_0, CLIP.XDOSCLI2.XRECT1_0, CLIP.XDOSCLI2.XRECT2_0, CLIP.XDOSCLI2.XRECT3_0, CLIP.XDOSCLI2.XRECT4_0, CLIP.XDOSCLI2.XRECT5_0, CLIP.XDOSCLI2.XRECT6_0, CLIP.XDOSCLI2.XVERS1_0, CLIP.XDOSCLI2.XVERS2_0, CLIP.XDOSCLI2.XFONDCHQ_0, " & _
"XREFCLI_0, XDLIVDEM_0, DLVDAT_0, XBATDRET_0, SHIDAT_0, XQTEDEM_0, (QTY_0 * 1000) AS QTY_0, cast(XMONTANT_0 As Decimal (15,2)) As XMONTANT_0, NETWEI_0, CLIP.SDELIVERY.SDHNUM_0 FROM CLIP.XBANQUE, CLIP.SDELIVERY, CLIP.SINVOICED, CLIP.XDOSCLI, CLIP.XDOSCLI2 " & _
"WHERE CLIP.XBANQUE.XBQNUM_0=CLIP.SDELIVERY.XBQNUM_0 " & _
"AND CLIP.SDELIVERY.XDCNUM_0=CLIP.XDOSCLI.XDCNUM_0 " & _
"AND CLIP.SDELIVERY.XDCNUM_0=CLIP.XDOSCLI2.XDCNUM_0 " & _
"AND CLIP.SINVOICED.XDCNUM_0=CLIP.XDOSCLI.XDCNUM_0 " & _
"AND CLIP.SDELIVERY.SDHNUM_0=CLIP.SINVOICED.SDHNUM_0 " & _
"AND SHIDAT_0 > To_Date('31/12/2005', 'DD/MM/YYYY')", oConnection)
'ComboBoxVisual.Text 'contient la valeur affichée correspondant à la ligne sélectionnée et déterminée par la propriété .DisplayMember
'ComboBoxVisual.SelectedValue.ToString() 'réf d'objet non instancié... 'contient la valeur réelle qui sera utilisée en cas de bind et déterminée par la propriété .ValueMember
'ComboBoxVisual.SelectedItem.ToString 'réf d'objet non instancié... 'contient l'item correspondant à la ligne sélectionnée.
'SelectedIndex => la valeur numérique de la ligne
'SelectedText => le texte qui apparait
'SelectedValue => la valeur (cachée)
'SelectedItem => l'objet ligne de la structure
'Création d'une table contenat tous les champs requis
Dim oTable As DataTable = New DataTable()
'Remplissage de la datatable à partir de la requete sql
oAdapt.Fill(oTable)
oTable.Columns.Add("NbCouleur", Type.GetType("System.SByte"))
Dim myRow As DataRow
For Each myRow In oTable.Rows
myRow("NbCouleur") = FctCalcNbCoul("XFONDCHQ_0", Couleurs())
oTable.Rows.Add(myRow("NbCouleur"))
Next myRow
'Fermeture de la connexion afin de ne pas monopoliser les ressources
oConnection.Close()
Dim oDataview As DataView = New DataView()
oDataview.Table = oDataset.Tables("oTable")
oDataview = oTable.DefaultView
oDataview.Sort = "SHIDAT_0"
'oDataview.RowFilter = "SHIDAT_0 > '" & ReportAutoBq.ComboBoxVisual.SelectedItem & "' AND SHIDAT_0 < '" & ReportAutoBq.ComboBoxVisual2.SelectedItem & "'"
'oDataset.AcceptChanges()
'oDataview.RowFilter = "SHIDAT_0 > '" & DateDebV & "' AND SHIDAT_0 < '" & DateFinV & "'"
oDataview.RowFilter = "SHIDAT_0 >= '01/01/" & DateDebV & "' AND SHIDAT_0 <= '31/12/" & DateDebV & "'"
'Masquer certaines colonnes
oDataview.Table.Columns("XRECT1_0").ColumnMapping = MappingType.Hidden
oDataview.Table.Columns("XRECT2_0").ColumnMapping = MappingType.Hidden
oDataview.Table.Columns("XRECT3_0").ColumnMapping = MappingType.Hidden
oDataview.Table.Columns("XRECT4_0").ColumnMapping = MappingType.Hidden
oDataview.Table.Columns("XRECT5_0").ColumnMapping = MappingType.Hidden
oDataview.Table.Columns("XRECT6_0").ColumnMapping = MappingType.Hidden
oDataview.Table.Columns("XVERS1_0").ColumnMapping = MappingType.Hidden
oDataview.Table.Columns("XVERS2_0").ColumnMapping = MappingType.Hidden
oDataview.Table.Columns("XFONDCHQ_0").ColumnMapping = MappingType.Hidden
DataGridViewVisual.DataSource = oDataview
' Vérif si erreur
Catch ex As Exception
MsgBox("Erreur !!! " & ex.Message)
End Try
End Sub
Public Couleurs() As String = {"XRECT1_0", "XRECT2_0", "XRECT3_0", "XRECT4_0", "XRECT5_0", "XRECT6_0", "XVERS1_0", "XVERS2_0"}
Public Function FctCalcNbCoul(ByVal XFONDCHQ As String, ByVal ParamArray Couleurs() As String) As Integer
Dim NbCoul As Integer
Dim Ctr As Integer
For Each coul As String In Couleurs
If coul <> "" Then
Ctr += 1
End If
Next
XFONDCHQ = XFONDCHQ.Remove(0, 3)
XFONDCHQ = XFONDCHQ.Remove(5, 9)
NbCoul = CInt(XFONDCHQ) + Ctr
Return NbCoul
End Function |
Partager