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
|
public List<Brouillard> RechercheBrouillard2(FiltreDate filtreDate, object date)
{
try
{
var cmd = new MySqlCommand("", Connection);
cmd.Parameters.Add(new MySqlParameter("@cum", MySqlDbType.Int64));
cmd.Parameters["@cum"].Value = 0;
cmd.CommandText =
"select mvt_date, reference, motif, recette, depense, cumul from ( select mvt_date, reference, motif, recette, depense, @cum := @cum + recette - depense as cumul, periode, mvt_id, rang from vue cross join (select @cum:=0) as x ) as y";
var where = "";
switch (filtreDate)
{
case FiltreDate.Annee:
if (string.IsNullOrWhiteSpace(where))
where = string.Format(" where Year(m.mvt_date) = {0}", (double)date);
else
where += string.Format(" and Year(m.mvt_date) = {0}", (double)date);
break;
case FiltreDate.EntreAnnee:
var rangei = (RangeEntier)date;
if (string.IsNullOrWhiteSpace(where))
where = string.Format(" where Year(m.mvt_date) between {0} and {1}", rangei.Debut, rangei.Fin);
else
where += string.Format(" and Year(m.mvt_date) between {0} and {1}", rangei.Debut, rangei.Fin);
break;
case FiltreDate.EntreJour:
var ranged = (RangeDate)date;
if (string.IsNullOrWhiteSpace(where))
where = string.Format(" where m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", ranged.Debut, ranged.Fin);
else
where += string.Format(" and m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", ranged.Debut, ranged.Fin);
break;
case FiltreDate.EntreMois:
var rangem = (RangeDate)date;
if (string.IsNullOrWhiteSpace(where))
where = string.Format(" where m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", rangem.Debut.Value.FirstDayOfMonth(), rangem.Fin.Value.LastDayOfMonth());
else
where += string.Format(" and m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", rangem.Debut.Value.FirstDayOfMonth(), rangem.Fin.Value.LastDayOfMonth());
break;
case FiltreDate.Jour:
if (string.IsNullOrWhiteSpace(where))
where = string.Format(" where m.mvt_date = '{0:yyyy-MM-dd}'", (DateTime)date);
else
where += string.Format(" and m.mvt_date = '{0:yyyy-MM-dd}'", (DateTime)date);
break;
case FiltreDate.Mois:
var rangems = new RangeDate() { Debut = ((DateTime)date).FirstDayOfMonth(), Fin = ((DateTime)date).LastDayOfMonth() };
if (string.IsNullOrWhiteSpace(where))
where = string.Format(" where m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", rangems.Debut.Value.FirstDayOfMonth(), rangems.Fin.Value.LastDayOfMonth());
else
where += string.Format(" and m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", rangems.Debut.Value.FirstDayOfMonth(), rangems.Fin.Value.LastDayOfMonth());
break;
}
var order = "";
cmd.CommandText += where;
order="order by mvt_id, rang";
cmd.CommandText += order;
Connection.Open();
var reader = cmd.ExecuteReader();
var rest = new List<Brouillard>();
while (reader.Read())
{
var brouillard = new Brouillard();
brouillard.Date = reader.GetDateTime(0);
brouillard.Ref = reader.GetString(1);
brouillard.Motif = reader.GetString(2);
brouillard.MontantR = reader.GetInt64(3);
brouillard.MontantD = reader.GetInt64(4);
brouillard.SoldeC = reader.GetInt64(5);
rest.Add(brouillard);
}
Connection.Close();
return rest;
}
catch (MySqlException e)
{
Console.WriteLine(e);
}
Connection.Close();
return null;
} |
