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
|
const string QUERY_STATION = @"
select
cast(st.id as smallint),
st.asname,
st.ComAusDatum_DT,
st.ComAusDauer,
st.ComEinDatum_DT,
st.ComEinDauer,
st.szyk,
st.sdat,
id.id beaid,
id.fname,
id.vname,
c1.synchro,
c1.einstellung
from te_isi_sta st
left outer join te_isi_id id on id.id = st.beaid and id.loskz = 0 and id.inaktiv = 0
left outer join te_isi_c1 c1 on c1.asquelle = 1 and c1.asziel = st.id
where st.loskz = 0
and st.id between @station_min and @station_max;
";
const string QUERY_LOG = @"
select
ComStationNr,
dbo.CU_CRMDate_To_Date(cast(ComDatum_DT / 1000000000 as int)),
count(*),
sum(ComFileSize),
sum(ComAnz)
from te_isi_c3
where ComArt = 2
and ComEinAus = 0
and dbo.CU_CRMDate_To_Date(cast(ComDatum_DT / 1000000000 as int)) > dateadd(day, -31, getdate())
and ComStationNr between @station_min and @station_max
group by ComStationNr, dbo.CU_CRMDate_To_Date(cast(ComDatum_DT / 1000000000 as int));
";
public CRMStationCollection()
{
stations = new Dictionary<short, CRMStation>();
// Chargement des stations depuis la base de données
using (SqlConnection cnx = new SqlConnection("Server=..."))
{
cnx.Open();
// Chargement de toutes les stations de la base de données
using (SqlCommand cmd = cnx.CreateCommand())
{
cmd.CommandText = string.Concat(QUERY_STATION, QUERY_LOG);
cmd.Parameters.AddWithValue("station_min", STATION_MIN);
cmd.Parameters.AddWithValue("station_max", STATION_MAX);
// Ajout de toutes les stations à la liste
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);
object[] values = new object[13];
/*
0 : Station number
1 : Station name
2 : Last out date
3 : Last out duration
4 : Last in date
5 : Last in duration
6 : Synchro perdiod
7 : Last synchro date
8 : Station's rep ID
9 : Station's rep first name
10 : Station's rep last name,
11 : Synchro flag
12 : Synchro format
*/
while (dr.Read())
{
dr.GetValues(values);
if (values[8] == DBNull.Value)
{
stations[(short)values[0]] = new CRMStation((short)values[0], (string)values[1], null, (long)values[2], (int)values[3], (long)values[4], (int)values[5], (int)values[6], (int)values[7], (bool)values[11], (string)values[12]);
}
else
{
stations[(short)values[0]] = new CRMStation((short)values[0], (string)values[1], (long)values[8], (string)values[9], (string)values[10], (long)values[2], (int)values[3], (long)values[4], (int)values[5], (int)values[6], (int)values[7], (bool)values[11], (string)values[12]);
}
stations[(short)values[0]].HasRecord = true;
}
dr.NextResult();
values = new object[5];
/*
0 : Station number
1 : Date
2 : Number of synchronisation this date
3 : Total size of files this date
4 : Total time this date
*/
while (dr.Read())
{
dr.GetValues(values);
if (stations.ContainsKey((short)values[0]))
{
stations[(short)values[0]].AddLog(new CRMLog((DateTime)values[1], (short)values[2], (long)values[3], (short)values[4]));
}
}
dr.Close();
}
cnx.Close();
} |
