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
|
public void LoadDataFromDatabaseAvancement()
{
#region Avancement
// Date du premier jour du mois precedent
string DatePrec,Datedeb;
if (DateTime.Now.Month == 1)
{
DatePrec = (DateTime.Now.Year -1) + "-" + "12" + "-" + "01";
Datedeb = (DateTime.Now.Year)+ "-" + (DateTime.Now.Month)+ "-" + "01";
}
else
{
DatePrec = DateTime.Now.Year + "-" + (DateTime.Now.Month -1) + "-" + "01";
Datedeb = (DateTime.Now.Year)+ "-" + (DateTime.Now.Month)+ "-" + "01";
}
string CallTable = "SELECT aff.numero,aff.libelle,heuresprev,sum(duree) heureseff,a.avancement ,"
+"if((sum(duree)+ round(((('1'-a.avancement)*'100'*sum(duree))/(a.avancement*'100')),'2') <= (heuresprev+'0.00001')),'',' ') etat, "
+"ifnull(round(((('1'-a.avancement)*'100'*sum(duree))/(a.avancement*'100')),'2'),heuresprev) resteafaire, "
+"aff.IDTYPEAFFAIRE, "
+"aff.idaffaire "
+"FROM affecter a,affaire aff,intervenir i WHERE a.idemploye = '"+this.Employe[0]+"' and aff.idaffaire = a.idaffaire "
+"and aff.idaffaire = i.idaffaire and i.idemploye = a.idemploye "
+"and aff.idaffaire IN(SELECT affa.idaffaire FROM intervenir inte,affecter af,affaire affa "
+"WHERE af.idemploye = '"+this.Employe[0]+"' and affa.idaffaire = af.idaffaire and affa.idaffaire = inte.idaffaire and inte.idemploye = af.idemploye "
+"and inte.datejour >= '"+DatePrec+"' "
+"and inte.datejour < '"+Datedeb+"' ) "
+"and i.datejour < '"+Datedeb+"' "
+"and aff.IDTYPEAFFAIRE < 3 "
+"group by a.idaffaire order by aff.numero desc ;";
AlphaGestBD.TestValidConnection(this.databaseConnection);
MySqlCommand commande = new MySqlCommand();
this.dadaptaterAvancement = new MySqlDataAdapter();
this.DsetAvancement = new DataSet("DsetAvancement");
commande.Connection = databaseConnection;
commande.CommandText = CallTable;
commande.CommandType = CommandType.Text;
this.dadaptaterAvancement.SelectCommand = commande;
this.tblAvancement = new DataTable("Avancement");
this.DsetAvancement.Tables.Add(this.tblAvancement);
#endregion
try
{
if(databaseConnection.State == ConnectionState.Closed)
{
databaseConnection.Open();
}
this.FillTableAvancement();
}
catch(MySql.Data.MySqlClient.MySqlException ex)
{
switch(ex.Number)
{
case 1045 : MessageBox.Show(AlphaGestException.GetMessage("MESS_ERR_003")) ;break;
default : MessageBox.Show(ex.Message.ToString()) ;break;
}
}
finally
{
databaseConnection.Close();
}
} |
Partager