private void majListeInventaire(boolean dispo){
             listeInventaireLot.clear();
             String chaineDispo="";
              
             if( dispo){
                 chaineDispo=" and lot.commande is null \n";
                 
             } else{
                 
                 chaineDispo="";
             }
             
             
             Session session=CEFXUtil.getSessionFactory().openSession();
             try{
                 // recuperer les validités disponibles
                 String qry="SELECT \n"
                     + "lot.fin_validite as fin_val\n"
                 + " FROM "
                     + "public.type_lot,\n"
                     + " public.lot\n"
                 + " WHERE \n"
                     + " lot.type_lot = type_lot.num_type_lot\n"
                     + chaineDispo
                     + " and num_type_lot ="+typeLot.getNumTypeLot()+"\n"
                         
                 + " group by \n"
                     + "lot.fin_validite"
                 + " order by \n"
                     + "lot.fin_validite";
                       
              //   // System.out.println(qry);      

             ///////
                    Transaction tx=null;
                    
                    // 1ere requete revoit une liste de couples int/date (continuite et dat fin validite)
                    
                    List<Date> val= (List<Date>)session.createSQLQuery(qry)
                         .list();
                    
                    Iterator iter=val.iterator();
                    
                    while(iter.hasNext()){
                              
                        Object obj=iter.next();
                        Date validite =(Date)obj;

                       // // System.out.println("Validité !: "+validite);
                    //2ème requete continuité
                        qry=" select  \n "
                            +" min(s.premier_numero) as pnum, \n "
                            +" count(*) as nblots \n "
                            //+" s.continuite as cont, \n "
//                            +" s.fin_val as finval  \n "
                            +"From \n "
                            +"(SELECT  \n "
                            +"    lot.premier_numero as premier_numero,  \n "
                            +"    type_lot.nb_cartes_par_lot as cartes_par_lot, \n "
                            +"    lot.fin_validite as fin_val,  \n "
                            +"    type_lot.num_type_lot as num_type_lot,  \n "
                            +"    lot.premier_numero-25*dense_rank() over(order by lot.premier_numero) as continuite \n "
                            +"  FROM  \n "
                            +"    public.type_lot,  \n "
                            +"    public.lot \n "
                            +"  WHERE  \n "
                            +"    lot.type_lot = type_lot.num_type_lot and  \n "
                            +"    lot.fin_validite='"+validite.toString()+"')S \n "
                            +"where \n "
                            +" s.num_type_lot ="+typeLot.getNumTypeLot()+" \n "
                            +"group by s.continuite,s.fin_val \n "
                            +"order by s.fin_val,s.continuite \n "
                            ;
                        
                       // // System.out.println("\nRequete continuite :\n"+qry);   
                           
                         List<Object> couple= (List<Object>)session.createSQLQuery(qry)
                         .list();
                         
       
                         
                         
                      Iterator iter2=couple.iterator();

                      while(iter2.hasNext()){
                        Object[] o=(Object[])iter2.next();

                        Integer premLot = Integer.parseInt(String.valueOf(o[0]));
                        Integer nbLots = Integer.parseInt(String.valueOf(o[1]));  
                        

                        Criteria c=session.createCriteria(Lot.class);
                            c.add(Restrictions.eq("typeLot", typeLot));
                        //    c.add(Restrictions.eq("finValidite", validite));
                            
                            c.add(Restrictions.ge("premierNumero", premLot));
                            c.add(Restrictions.le("premierNumero", premLot+(nbLots-1)*typeLot.getNbCartesParLot()));
                            c.addOrder(Order.asc("premierNumero"));
                            c.addOrder(Order.asc("finValidite"));
                            
                            if(dispo){
                                c.add(Restrictions.isNull("commande"));
                            }
                            
                      
                          List<Lot> listeLots= c.list();
                          // System.out.println("LISTE DE LOTS : \n"+listeLots.toString());
                          
                          if(listeLots != null && listeLots.size()>0){
                                // System.out.println("CREATION DE LA LISTE D INVENTAIRE\n");
                                InventaireLot invLot=new InventaireLot(listeLots);

                                this.listeInventaireLot.add(invLot);
                            }
    
                       }


                          
                    }
                    
                    
                    
                    //parcourir la liste et pour chaque couple
                    
                    // deuxieme requete
                    
               
                 
                   }catch (HibernateException he){
                he.printStackTrace();
            }finally{
            session.close();
        }
    }