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();
} |
Partager