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
| Dim connection As OleDb.OleDbConnection
Dim dataset As DataSet
Dim command As OleDb.OleDbDataAdapter
Try
connection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Clientfile & "';Extended Properties=Excel 8.0;")
command = New System.Data.OleDb.OleDbDataAdapter("select Mois, SUM(déposé) as [Total déposé]," _
& "SUM(`Aff initial`) as [Total initial]," _
& "SUM(`Aff CI`) as [Total CI]," _
& "Round(SUM(`Aff CI`) /SUM(déposé),3) as [Aff Moyen]," _
& "SUM(`NV# Tri 1`) as [Nv Tri 1]," _
& "SUM(`NV# Tri 2`) as [Nv Tri 2]," _
& "SUM(`NV# Tri 3`) as [Nv Tri 3]," _
& "SUM(`NV# Tri 4`) as [Nv Tri 4]," _
& "round(SUM(`NV# Tri 1`)/ sum(déposé) * 100,2) as [Perc_NM1]," _
& "round(SUM(`NV# Tri 2`)/ sum(déposé)* 100,2) as [Perc_NM2]," _
& "round(SUM(`NV# Tri 4`)/ sum(déposé)* 100,2) as [Perc_Meca]," _
& "SUM(prestation) as [Total Prestation]," _
& "round(SUM(prestation)/ sum(déposé/1000),2) as [Presta RE au mille]" _
& "from [Production$]" _
& "WHERE Mois LIKE ""%" & crit1 & "%""" _
& " AND Client LIKE ""%" & crit2 & "%""" _
& " AND Format LIKE ""%" & crit4 & "%""" _
& " AND Produit LIKE ""%" & crit3 & "%""" _
& " GROUP BY Mois", connection)
command.TableMappings.Add("Table", "TestTable")
dataset = New System.Data.DataSet()
command.Fill(dataset)
DataGridView1.DataSource = dataset.Tables(0)
C_Depos.DataSource = dataset.Tables(0)
With DataGridView1
.Columns("Aff Moyen").DefaultCellStyle.Format = "C2"
.Columns("Total Prestation").DefaultCellStyle.Format = "C2"
.Columns("Presta RE au mille").DefaultCellStyle.Format = "C2"
.Columns("Total CI").DefaultCellStyle.Format = "C2"
End With
connection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
AfficheChart()
End If
End Sub
'GRAPHIQUES
Sub AfficheChart()
If Clientfile <> "" Then
Dim T_Depose, T_AffCI, T_AffMoy, T_Presta, T_PrestaMille As Double
Dim TotRows As Integer
Dim percent As Boolean
Select Case SlctProduit.Text
'_________________________________________________________________________________________________________________________________________
'__________________________________________________________________________________________________
'___________________________________________ESP. LIB / PLURIEL _________________________________________
'__________________________________________________________________________________________________
'__________________________________________________________________________________________________
Case "Destinéo Es. Lib.", "Destinéo Pluriel", "Tous les produits", "Destinéo Intégral"
'Dans une series YValueMembers indique quelle colonne utiliser pour les valeurs Y
C_Depos.Series(0).YValueMembers = "Total déposé"
Chart1.Series("Series1").YValueMembers = "Total CI"
Chart2.Series("Series1").YValueMembers = "Aff Moyen"
Chart3.Series("Series1").YValueMembers = "Total Prestation"
Chart4.Series("Series1").YValueMembers = "Presta RE au mille"
Chart1.Series("Series1").XValueMember = "Mois" |
