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
| public void StatementMethod(PreparedStatement st,String nom,String Direction,int Annee,String MOIS) throws SQLException
{
int i=1;
if(nom!=null)
{
st.setString(i,nom);i++;
}
if(Direction!=null)
{
st.setString(i,Direction);i++;
}
st.setInt(i,Annee);i++;
st.setString(i,MOIS);
if(Direction!=null || nom!=null)
couts = new double[12];
else if(Direction==null && nom==null)
couts = new double[12*SelectionDirections().length];
}
public String ReturnQuery(String nom,String Direction)
{
String req="";
if(nom==null && Direction!=null)
{
req="SELECT SUM(COUT) FROM REPORTING_FIXE WHERE INTITULE_DIRECT=? AND ANNEE=? AND MOIS=? ";
}
else if(nom!=null && Direction!=null)
{
req="SELECT SUM(COUT) FROM REPORTING_FIXE WHERE DIRECTIONS=? AND INTITULE_DIRECT=? AND ANNEE=? AND MOIS=? ";
}
else if(nom==null && Direction==null)
{
req="SELECT SUM(COUT) FROM REPORTING_FIXE WHERE ANNEE=? AND MOIS=? group by intitule_direct";
}
else if(nom!=null && Direction==null)
{
req="SELECT SUM(COUT) FROM REPORTING_FIXE WHERE Directions=? AND ANNEE=? AND MOIS=? group by intitule_direct";
}
return req;
}
public double[] ReportingFixCout(String nom,String Direction,int Annee)
{
int compteur=0;
String req=ReturnQuery(nom, Direction);
String[] MOIS = {"JANUARY","FEBRUARY","MARCH","APRIL","MAY","JUNE","JULY","AUGUST","SEPTEMBER","OCTOBER","NOVEMBER","DECEMBER"};
for(int j=0;j<MOIS.length;j++)
{
int i=1;
try
{
Connection conn = SingletonConnection.getConnection();
PreparedStatement st = conn.prepareStatement(req);
System.out.println(req);
if(nom!=null && Direction!=null)
{
StatementMethod(st, nom, Direction, Annee,MOIS[j]);
}
else if(nom==null && Direction!=null)
{
StatementMethod(st, null, Direction,Annee,MOIS[j]);
}
else if(nom==null && Direction==null)
{
StatementMethod( st, null, null, Annee , MOIS[j] );
}
else if(nom!=null && Direction==null)
{
StatementMethod( st, nom, null, Annee , MOIS[j] );
}
ResultSet rs = st.executeQuery();
while (rs.next())
{
couts[compteur]=rs.getDouble(1);
System.out.println("k : "+compteur+ " \t" +couts[compteur]+"\t"+rs.getDouble(1));compteur++;
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
return couts;
} |
Partager