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
| public List<Brouillard> RechercherBrouillaurd(FiltreDate filtreDate, object date)
{
var cmd = new MySqlCommand("", Connection);
cmd.CommandText =
"SELECT M.mvt_date, COALESCE( R.cr_ref, D.cD_ref ) AS reference, COALESCE( R.cr_motif, D.cD_motif ) AS motif, COALESCE( R.cr_montant, 0 ) AS recette, COALESCE( D.cd_montant, 0 ) AS depense, @cumul := @cumul + COALESCE( R.cr_montant, 0 ) - COALESCE( D.cd_montant, 0 ) AS cumul FROM Mouvement M CROSS JOIN ( SELECT @cumul:=0 ) tmp LEFT JOIN entree E ON M.mvt_id = E.mvt_id LEFT JOIN carnetrecette R ON E.cr_id = R.cr_id LEFT JOIN sortie S ON M.mvt_id = S.mvt_id LEFT JOIN carnetdepense D ON S.cd_id = D.cd_id ";
var where = "";
switch (filtreDate)
{
case FiltreDate.Annee:
if (string.IsNullOrWhiteSpace(where))
where = string.Format(" where Year(m.mvt_date) = {0}", (int)date);
else
where += string.Format(" and Year(m.mvt_date) = {0}", (int)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;
}
cmd.CommandText += where;
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;
} |
Partager