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 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207
|
// IMPORT BILIOTHEQUE
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Data.SqlClient
Imports System.Data
Imports Excel = Microsoft.Office.Core
Imports System.Data.Sql
Imports System.Windows.Forms.DataVisualization.Charting
Public Class statist
// DECLARATION VARIABLES
Public j As Integer
Dim MyConnexion As SqlConnection = New SqlConnection("server=tcp:10.1.1.3;database=Data_Bases;User=FBB_User;password=QSpss8!;integrated security=true ")
Dim Mycommand As SqlCommand = MyConnexion.CreateCommand()
Dim command As SqlCommand = MyConnexion.CreateCommand()
Dim command1 As SqlCommand = MyConnexion.CreateCommand()
Dim command2 As SqlCommand = MyConnexion.CreateCommand()
Dim command3 As SqlCommand = MyConnexion.CreateCommand()
Dim command33 As SqlCommand = MyConnexion.CreateCommand()
Dim command4 As SqlCommand = MyConnexion.CreateCommand()
Dim command7 As SqlCommand = MyConnexion.CreateCommand()
Dim command8 As SqlCommand = MyConnexion.CreateCommand()
Dim command9 As SqlCommand = MyConnexion.CreateCommand()
Private Sub rapport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
//COMBO MOIS
Dim compt As Integer
For compt = 1 To 12
txt_mois.Items.Add(compt)
Combomois.Items.Add(compt)
Next
compt = compt + 1
//COMBO ANNE
For compt = 2014 To 2050
txtanne.Items.Add(compt)
Comboanne.Items.Add(compt)
Next
compt = compt + 1
//REMPLISSEMENT COMBO CLIENT
Mycommand.CommandText = "SELECT code_cl FROM CLIENT ;"
MyConnexion.Open()
Dim Reader As SqlDataReader = Mycommand.ExecuteReader
While Reader.Read()
//exploitation ligne courante
For Me.j = 0 To Reader.FieldCount - 1
Comboclient.Items.Add(Reader(j).ToString)
Next j
End While
MyConnexion.Close()
End Sub
Private Sub Comboclient_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Comboclient.SelectedIndexChanged
Try
Mycommand.CommandText = "SELECT* FROM CLIENT ;"
MyConnexion.Open()
Dim myReader As SqlDataReader = Mycommand.ExecuteReader
While myReader.Read()
If Comboclient.Text = myReader!code_cl Then
n_client.Text = myReader!nom_cl
Lieu_chagm.Text = myReader!Lieu_chargem
End If
End While
MyConnexion.Close()
Catch ex As SqlException
MsgBox(ex.Message)
MyConnexion.Close()
End Try
End Sub
Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Try
ListBox2.Items.Clear()
Mycommand.CommandText = "SELECT Count(TRAIN.code_wagons) AS NOMBRE_WAGONS FROM TRAIN WHERE num_train like '" & ListBox1.SelectedItem & "' ;"
command.CommandText = "SELECT sum(poid_lot) AS Moyenne_tonnage FROM TRAIN WHERE num_train like '" & ListBox1.SelectedItem & "' ;"
command1.CommandText = "SELECT MIN(D_depart) AS depart FROM DESTINATION WHERE num_train like '" & ListBox1.SelectedItem & "';"
command2.CommandText = "Select MIN(DESTINATION.D_arrive) AS Date_arr_min, MIN(DESTINATION.D_dechargem) AS Date_dechgt_min, MAX(DESTINATION.D_dechargem) AS Date_dechgt_max, MAX(DESTINATION.D_arrive) AS Date_arr_max FROM DESTINATION WHERE num_train like '" & ListBox1.SelectedItem & "' and Destination like '" & "Chambeshi" & "' or Destination like '" & "Tshimbulu" & "';"
command3.CommandText = "SELECT Avg(DESTINATION.Durée_sur_ZRL) AS Moyenne_ZRL FROM DESTINATION WHERE num_train like '" & ListBox1.SelectedItem & "' and Destination like '" & "Chambeshi" & "';"
command33.CommandText = "SELECT Avg(DESTINATION.Durée_sur_SNCC) AS Moyenne_SNCC FROM DESTINATION WHERE num_train like '" & ListBox1.SelectedItem & "' and Destination like '" & "Sakania" & "';"
command7.CommandText = "SELECT AVG(Duree) as d_mutaka FROM DESTINATION WHERE num_train like '" & ListBox1.SelectedItem & "' and Destination like '" & "Lubumbashi" & "';"
command4.CommandText = "SELECT * FROM TRAIN;"
MyConnexion.Open()
Dim myReader As SqlDataReader = Mycommand.ExecuteReader
//REQUETTE NOMBRE WAGONS
While myReader.Read()
nbwagon.Text = myReader!NOMBRE_WAGONS
End While
MyConnexion.Close()
MyConnexion.Open()
Dim Reader As SqlDataReader = command.ExecuteReader
// REQUETTE MOYENNE
While Reader.Read()
tonnag.Text = Reader!Moyenne_tonnage
End While
MyConnexion.Close()
Try
MyConnexion.Open()
Dim Reader1 As SqlDataReader = command1.ExecuteReader
// REQUETTE AUTRES
While Reader1.Read()
ddepart.Text = Reader1!depart
// REQUETTE DATE MOYENNE RAIL
End While
MyConnexion.Close()
Catch ex As Exception
MsgBox(ex.Message)
MyConnexion.Close()
End Try
Try
MyConnexion.Open()
Dim Reader2 As SqlDataReader = command2.ExecuteReader
// REQUETTE DATE ARRIVEE
While Reader2.Read()
darrive.Text = Reader2!Date_arr_min
d_decha.Text = Reader2!Date_dechgt_min
arrivéN.Text = Reader2!Date_arr_max
dechgN.Text = Reader2!Date_dechgt_max
End While
MyConnexion.Close()
Catch ex As Exception
MsgBox(ex.Message)
MyConnexion.Close()
End Try
Try
MyConnexion.Open()
Dim Reader3 As SqlDataReader = command3.ExecuteReader
While Reader3.Read()
ztr.Text = Reader3!Moyenne_ZRL
End While
MyConnexion.Close()
Catch ex As Exception
MsgBox(ex.Message)
MyConnexion.Close()
End Try
Try
MyConnexion.Open()
Dim Reader33 As SqlDataReader = command33.ExecuteReader
// REQUETTE DATE MOYENNE RAIL
While Reader33.Read()
sncc.Text = Reader33!Moyenne_SNCC
End While
Label17.Text = ListBox1.SelectedItem
MyConnexion.Close()
Catch ex As Exception
MsgBox(ex.Message)
MyConnexion.Close()
End Try
MyConnexion.Open()
Dim Reader4 As SqlDataReader = command4.ExecuteReader
MyConnexion.Close()
// REQUETTE DATE MOYENNE RAIL
Try
// MyConnexion.Open()
Dim sql As String = "SELECT destination, Avg(DESTINATION.dureedprt) AS Durée_Moyenne FROM DESTINATION WHERE num_train like '" & ListBox1.SelectedItem & "' GROUP BY destination;"
Dim dataadapter As New SqlDataAdapter(sql, MyConnexion)
Dim ds As New DataSet()
MyConnexion.Open()
dataadapter.Fill(ds, "DESTINATION")
MyConnexion.Close()
DataGridView1.DataSource = ds
DataGridView1.DataMember = "DESTINATION"
MyConnexion.Close()
Catch ex As SqlException
MsgBox(ex.Message)
MyConnexion.Close()
End Try
End Try
' MyConnexion.Close()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles afficher.Click
ListBox2.Items.Clear()
Mycommand.CommandText = "SELECT code_wagons FROM TRAIN WHERE num_train like '" & ListBox1.SelectedItem & "';"
MyConnexion.Open()
Dim myReader As SqlDataReader = Mycommand.ExecuteReader
'combotrain.Items.Clear()
While myReader.Read()
' exploitation ligne courante
For Me.j = 0 To myReader.FieldCount - 1
ListBox2.Items.Add(myReader(j).ToString)
' mois =
Next j
End While
MyConnexion.Close()
...
End Sub |