#include "eccad.h"

EccadDB connectEccadDB(const char* server, const char* dbName,
             const char* user, const char* pass, const char* port)
{
  EccadDB conn = PQsetdbLogin(server, port, NULL, NULL, dbName, user, pass);
  if(!checkConnectionStatus(conn))
  {
    if (PQstatus(conn) == CONNECTION_BAD)
    {
//      fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
//      fprintf(stderr, "%s", PQerrorMessage(conn));
      PQfinish(conn);
    }
    return NULL;
  }
  return conn;
}

void closeEccadDB(EccadDB conn)
{
   PQfinish(conn);
}

gdImagePtr grid2img(EccadDB db, ptrGrid g, ptrParam p, int idMap, int idClassdis)
{
  gdImagePtr im=NULL;
  register int i,j;
  LIST map;

  map= mapOfParamById(db, idMap, idClassdis, p->mini, p->maxi);
//  fprintf(stderr,"start colorAllocate with %d colors\n", map->count);

  if(map)
  {
	if (im= gdImageCreate(g->width, g->height))
	{
//		fprintf(stderr," width %d height %d\n",g->width, g->height );
		colorAllocate(im, map);
//		fprintf(stderr,"im->sy=%d\n",im->sy);
//		fprintf(stderr,"im->sx=%d\n",im->sx);
//		for (j=0 ;  j < im->sy; j++)
//			for (i=0  ; i < im->sx ; i++)
        for (j=0 ;  j < gdImageSY(im); j++)
            for (i=0  ; i < gdImageSX(im) ; i++)
			{
//				int value= val2Color(g->datas[j * im->sx +i],  map);
				int value= val2Color(g->datas[j * g->width +i],  map);
//if (i>=363 && i<=365 && j>=81 && j<=83)	fprintf(stderr,"i=%d, j=%d, color Paris=%d, val=%ld\n",i,j,value,g->datas[j * g->width +i]);
				gdImageSetPixel(im, i, j, value);
			}
		}
  }
  return im;
}

// insertion d'une classmap dans une image gd
// aucune couleur ne doit etre alloue dans l'image d'origine
void colorAllocate(gdImagePtr im, LIST map)
{
   ptrInterval v;
   int i, j=0;
   loop_through_list(map, v, ptrInterval)
   {
     i=gdImageColorAllocate(im, v->color->red ,v->color->green, v->color->blue);
     //fprintf(stderr, "color Allocatealloue a l-ndex %d :%d \n", j, i);
     j++;
   }
}

char * oid2filename(EccadDB db, int oidGrille, int width, int height,
                    char * idParam, int idMap, int idClassdis, char *resultFile)
{
   char *fileName=NULL;
   int grilleId;
   char query[512];

   PQexec(db,("BEGIN"));
   if( (grilleId = lo_open(db, oidGrille, INV_READ)) != -1)
   {
     ptrGrid g= allocGrid( width, height);
     if(g)
     {
       unsigned long K;
       unsigned long size;

       size=  (long)g->width * (long)g->height * sizeof(int);
//fprintf(stderr, "MAXINT %ld 2*MAXINT %ld\n", MAXINT, (unsigned)MAXINT + MAXINT -1); 
       if( (K= lo_read(db, grilleId, (char*)g->datas, size)) == size)
       {
         PGresult *res;
         sprintf(query, 
            "SELECT min_param,max_param FROM parametre WHERE id_param='%s'",
            idParam);
         res=PQexec(db,query);
         if(checkSQLErrors(res)) 
         {
           double mini= atof( PQgetvalue(res, 0, 0));
           double maxi= atof( PQgetvalue(res, 0, 1));
           LIST map;

           if(  map = mapOfParamById(db, idMap, idClassdis, mini, maxi))
           {
             gdImagePtr im;
             if (im= gdImageCreate(width, height))
             {
               register int i,j;
               FILE *out;
//fprintf(stderr," oidGrille %d mini %f maxi %f\n", oidGrille, mini,maxi);
               colorAllocate(im, map);
               for (j=0 ;  j < im->sy; j++)
                 for (i=0  ; i < im->sx ; i++)
                 {
                   int value= val2Color(g->datas[j * im->sx +i], map);
// if( g->datas[j * im->sx +i] !=0) printf( " %d\n", g->datas[j * im->sx +i]);
                   gdImageSetPixel(im, i, j, value);
                 }
//               sprintf(query, "/tmp/%d.%d.png", oidGrille, idMap);
               if( out= fopen(resultFile, "w"))
               {
                 gdImagePng(im, out);
                 fclose(out);
                 fileName=resultFile;
               }
               else fprintf(stderr,"Erreur open File %s\n", fileName);
               gdImageDestroy (im);
             }
             else fprintf(stderr,"Erreur gdImageCreate\n");
             freeMap(map);
           }
           else fprintf(stderr,"Erreur Check Map idMap %d\n", idMap);
         }
         else fprintf(stderr,"Erreur Check min max from idParam %s\n", idParam);
         PQclear(res);
       }
       else fprintf(stderr," Erreur Read OID : %ld sur %d octets\n", K, size);
//fprintf (stderr, " now free grid\n");
       freeGrid(g);
     }
     lo_close(db, grilleId);
   }
   else fprintf(stderr," Erreur Open OID : %d\n", oidGrille);
   PQexec(db,("END"));
   return fileName;
}

char * getRaster(const char *dbHost, const char * dbName, const char *dbUser, int idGrille, int idMap, int idClassdis)
{
    char *resultFile;
    struct stat sts;
    char query[256];

    //sprintf(query,"/tmp/%d.%d.%d.png", idGrille, idMap, idClassdis);
    sprintf(query,"/tmp/%d.%d.png", idGrille, idMap);
    resultFile= strdup(query);

    if ( stat (query, &sts) != 0) 
    {
      char *fileName=NULL;
      EccadDB db; 

      if((db= PQsetdbLogin(dbHost,"5432", NULL, NULL, dbName, dbUser, NULL)) &&
		(PQstatus(db) != CONNECTION_BAD) )
      {
        PGresult *res;
        sprintf(query, "SELECT valeurs_grille, id_param, ncol_grille, nlign_grille FROM grille WHERE id_grille = %d;",idGrille);
        res=PQexec(db,query);
        if(checkSQLErrors(res) && PQntuples(res))
        {
          int oid=   atoi(PQgetvalue(res, 0, 0));
          int width=  atoi(PQgetvalue(res, 0, 2));
          int height= atoi(PQgetvalue(res, 0, 3));
          fileName= oid2filename(db, oid, width, height, PQgetvalue(res, 0, 1), idMap, idClassdis, resultFile);
        }
        else  fprintf(stderr, "No found id-grille %d\n", idGrille);  
        PQclear(res);
        PQfinish(db);
      }
      else  fprintf(stderr, "Erreur connect to db %s on host %s\n", dbName, dbHost);
      resultFile= fileName;
   }
   return resultFile;
}
//	============================ Fonctions ==================================


int checkConnectionStatus(EccadDB conn)
{
  char* erreur;
	
  if (conn==NULL || PQstatus(conn) == CONNECTION_BAD)
  {
     return 0==1;
  }
  else{
    // la connection est ok
    // mise en place de la date en UTC/GMT
    // db->clock_consumed=clock();
      PGresult* res=PQexec(conn, "set time zone 'UTC';");
      if (!(res) || 
          ((PQresultStatus(res) != PGRES_TUPLES_OK ) &&
	  	  (PQresultStatus(res) != PGRES_COMMAND_OK) ) ){
		PQclear(res);
      	return 0==1;
      }
      res= PQexec(conn, "set enable_seqscan='false';");
      if (!(res) ||
          ((PQresultStatus(res) != PGRES_TUPLES_OK ) &&
          (PQresultStatus(res) != PGRES_COMMAND_OK) ) )
      {
	PQclear(res);
      	return 0==1;
      }
      res= PQexec(conn, "set datestyle to 'ISO, YMD';");
      if (!(res) ||
          ((PQresultStatus(res) != PGRES_TUPLES_OK ) &&
          (PQresultStatus(res) != PGRES_COMMAND_OK) ) )
      {
	PQclear(res);
      	return 0==1;
      }
      res= PQexec(conn, "set autocommit to 'on';");
      if (!(res) ||
          ((PQresultStatus(res) != PGRES_TUPLES_OK ) &&
          (PQresultStatus(res) != PGRES_COMMAND_OK) ) )
      {
	PQclear(res);
      	return 0==1;
      }
    }
    return 0==0;
}

int checkSQLErrors(PGresult* res){
	if ( !(res) ||
          ((PQresultStatus(res) != PGRES_TUPLES_OK ) &&
          (PQresultStatus(res) != PGRES_COMMAND_OK) ) )
      {
		return 0==1;
	}
	return 0==0;
}

LIST initColors(EccadDB conn)
{
    LIST colors= make_list();
    PGresult *res;
    res=PQexec(conn,"SELECT * FROM color;");
    if(checkSQLErrors(res)) 
    {
      register int i;
      for(i=0; i< PQntuples(res); i++)
      {
         ptrRGB c= alloueColor( 
             atoi(PQgetvalue(res, i, 0)),
             atoi(PQgetvalue(res, i, 1)),
             atoi(PQgetvalue(res, i, 2)),
             atoi(PQgetvalue(res, i, 3)));
        add_to_tail(colors, c);
      }
    }
    PQclear(res);

    return colors;
}
LIST initParams(EccadDB conn)
{
    LIST params= make_list();
    PGresult *res;
    res=PQexec(conn,"SELECT * FROM parametre;");
    if(checkSQLErrors(res)) 
    {
      register int i;
      for(i=0; i< PQntuples(res); i++)
      {
//      	 fprintf(stderr, " PQgetvalue*--> maxPar %s   %s", PQgetvalue(res, i, 6), PQgetvalue(res, i, 3));
      	 //fprintf(stderr, " PQgetvalue-> maxPar %f", (double)strtod(PQgetvalue(res, i, 6),NULL));
      	
         ptrParam p= allocParam( 
             atoi(PQgetvalue(res, i, 0)),    //id
             atoi(PQgetvalue(res, i, 2)),    //id unite
             PQgetvalue(res, i, 3),         // full name
             atof(PQgetvalue(res, i, 6)),    // minPar
             atof(PQgetvalue(res, i, 7)),NULL);   // maxPar
        add_to_tail(params, p);
      }

    }
    PQclear(res);
    return params;
}

ptrParam getParamFromId(EccadDB db, int idParam)
{
	PGresult *res;
	char query[256];
	sprintf(query, "SELECT id_param, id_unite, fullname_param, min_param, max_param, shortname_unite FROM parametre inner join unite using (id_unite) WHERE id_param = %d;", idParam);
	res=PQexec(db,query);
	ptrParam p = NULL;

	if(checkSQLErrors(res) && PQntuples(res) )
	{
		p= allocParam(
            atoi(PQgetvalue(res, 0, 0)),    //id
            atoi(PQgetvalue(res, 0, 1)),    //id unite
            PQgetvalue(res, 0, 2),          // full name
            atof(PQgetvalue(res, 0, 3)),    // minParam
            atof(PQgetvalue(res, 0, 4)),    // maxParam
            PQgetvalue(res, 0, 5));         // shortname unite
	}
	PQclear(res);
	return p;
}

ptrParam getParamFromName(EccadDB db, char* nomParam)
{
	PGresult *res;
	char query[256];
	sprintf(query, "SELECT id_param, id_unite, fullname_param, min_param, max_param, shortname_unite FROM parametre inner join unite using (id_unite) WHERE fullname_param = '%s';", nomParam);
	res=PQexec(db,query);
	ptrParam p = NULL;

	if(checkSQLErrors(res) && PQntuples(res) )
	{
		p= allocParam(
            atoi(PQgetvalue(res, 0, 0)),    //id
            atoi(PQgetvalue(res, 0, 1)),    //id unite
            PQgetvalue(res, 0, 2),          // full name
            atof(PQgetvalue(res, 0, 3)),    // minPar
            atof(PQgetvalue(res, 0, 4)),    // maxPar
       		PQgetvalue(res, 0, 5));   		// shortname unite
	}
	PQclear(res);
	return p;
}

// generation de la table de couleur d'une map de la base
//        idMap : id de la base
//        mini/maxi : valeurs min max du parametre de la base 
//            ces deux valeurs sont nécessaire car les intervalles de couleurs
//              sont codes en unsigned maxhort / au extremes du param 

LIST mapOfParamById(EccadDB conn, int idMap, int idClassdis, double mini, double  maxi)
{
   LIST map=NULL;
   static LIST colors=NULL;
   PGresult *res, *resc;
   char query[256];
   
   int isDynamic = 0;
   
   /*****************/
   //mini=-1;
   //maxi=100;
   //int pas=10;
   /****************/

// mapping des couleurs de la base
   if(!colors) colors=initColors(conn);

//fprintf(stderr,  "Generation interval for idMap %d With  min %f max %f\n",
//    idMap,  mini,  maxi);

//verification de la classmap dynamique
sprintf(query, 
       "SELECT scl_name FROM scale_value WHERE scl_id=%d;", idClassdis);
res=PQexec(conn,query);
 if(checkSQLErrors(res) && PQntuples(res)) 
   {
   	   isDynamic = (!strcmp(PQgetvalue(res, 0, 0),"linear") || !strcmp(PQgetvalue(res, 0, 0),"logarithm") || !strcmp(PQgetvalue(res, 0, 0),"exponential"));
   }

// recherche des interval de la classmap
   sprintf(query,"SELECT * FROM class_distribution as c JOIN class_index USING (num_order) WHERE scl_id=%d and clm_id=%d order by c.num_order;",idClassdis, idMap);
   res=PQexec(conn,query);
   if(checkSQLErrors(res) && PQntuples(res))
   {
// mise en place des intervalles base dans la liste map
      ptrInterval interv;
      register int i;
      map = make_list();
//      fprintf(stderr,  "--------*******--------min %ld max %ld\n",
//      v2int(mini,mini,maxi), v2int(maxi,mini,maxi));
      unsigned int cmin,cmax;
      double borneMin, borneMax;
      for(i=0; i< PQntuples(res); i++)
      {

	//conversion en unsigned int des intervalles
 
        int id=atoi(PQgetvalue(res, i, 5));//idColor
        borneMin = atof(PQgetvalue(res, i, 3));
        borneMax = atof(PQgetvalue(res, i, 4));
        if (borneMin < 0) borneMin = 0;
        if (isDynamic)  cmin= v2int(borneMin*maxi,mini, maxi);
        else  cmin= v2int(borneMin,mini, maxi);
        if (isDynamic)  cmax= v2int(borneMax*maxi,mini, maxi);
        else cmax= v2int(borneMax,mini, maxi);
//fprintf(stderr,  "--------*i=%d, min=%f, max=%f, f1=%.1f, f2=%.1f, cmin=%ld, cmax=%ld\n",i,mini,maxi,borneMin,borneMax,cmin,cmax);
	/*
        int id= atoi(PQgetvalue(res, i, 1));   //idColor
        if (isDynamic)  cmin= v2int(atof(PQgetvalue(res, i, 3))* maxi,mini, maxi);
        else  cmin= v2int(atof(PQgetvalue(res, i, 3)),mini, maxi);
        if (isDynamic)  cmax= v2int(atof(PQgetvalue(res, i, 2))* maxi,mini, maxi);
        else cmax= v2int(atof(PQgetvalue(res, i, 2)),mini, maxi);
	*/


	//affectation des couleurs aux intervalles
        ptrRGB c;
        if( c= (ptrRGB)search_list(colors, &id, (PFI)inColorId) )
        {
			interv = alloueInterval( c,cmin, cmax);
//			fprintf(stderr,  "add interval: cmin %s  %ld cmax %s  %ld id -> col %d \n",
//			PQgetvalue(res, i, 3), cmin, PQgetvalue(res, i, 4), cmax, c->id);

			add_to_tail( map , interv);
         }
         else
         {
           fprintf(stderr,  "No found color for id %d\n", id);
           exit(1);
         }
      }
   }
   else fprintf(stderr,  "No found color for idMap  %d\n", idMap);

   PQclear(res);

   return map;
}

//generation d'une table de couleur en fonction d'un parametre //
/*LIST mapOfParam(EccadDB conn, ptrParam p)
{
   LIST map=NULL;
   PGresult *res;
   char query[256];
   
   sprintf(query,"%s \'%s\';", 
		"SELECT cm_id, scl_default_id FROM param_color NATURAL JOIN parametre WHERE fullName_param = ",p->name);
   res=PQexec(conn,query);
   if(checkSQLErrors(res)) 
      map=  mapOfParamById(conn, atoi(PQgetvalue(res, 0, 0)), atoi(PQgetvalue(res, 0, 1)), p->mini, p->maxi);
   PQclear(res);
   return map;
}*/

// w, h : size of origin grid isur zone globale implicite
// z :  zone a extraire en inputs zone extraite en output
static ptrGrid loadDatasFromBd(EccadDB db,int oidGrille,int w,int h,ptrZone z)
{
// la grille origine sans data alloue
   Grid g;
   g.width=w;
   g.height=h;
// la grille de sortie
   ptrGrid grid=NULL;
   {
     int id;
     PQexec(db,("BEGIN"));
     if( (id = lo_open(db, oidGrille, INV_READ)) != -1)
     {
       if ((z != NULL) && memcmp(z, &globalZone, sizeof(Zone)) )
       {
         int iDeb ,iFin, jDeb, jFin;
	 double  paslon, paslat;
         float x, y, latMin, lonMin, latMax, lonMax;
         unsigned long K;
// le pas dorigine	 
	 paslat= pasLat(&globalZone, &g);
	 paslon= pasLon(&globalZone, &g);
          // la zone qui doit etre extraite
//         fprintf(stderr, "Extrait Origin Lat %f->%f  pasLat %.2f lon %f->%f pasLon %.2f\n",
//        		 z->latMin,z->latMax,paslat,z->lonMin,z->lonMax,paslon);
// la zone à extraire
//		 z->lonMax = z->lonMax - paslon/2.;
//		 z->lonMin = z->lonMin + paslon/2.;
//		 z->latMax = z->latMax - paslat/2.;
//		 z->latMin = z->latMin + paslat/2.;
		 if (z->latMin == z->latMax && z->lonMin == z->lonMax) {
			 if (z->latMin == 0 && z->lonMin == 0) {
				 lonMax = paslon/2.;
				 lonMin = paslon/2.;
				 latMax = paslon/2.;
				 latMin = paslon/2.;
			 } else {
				 lonMax = z->lonMax;
				 lonMin = z->lonMin;
				 latMax = z->latMax;
				 latMin = z->latMin;
			 }
		 } else {
			 lonMax = z->lonMax - paslon/2.;
			 lonMin = z->lonMin + paslon/2.;
			 latMax = z->latMax - paslat/2.;
			 latMin = z->latMin + paslat/2.;
		 }
		 jDeb= lat2j(latMin, &g, &globalZone);
         jFin= lat2j(latMax, &g, &globalZone);
         iDeb= lon2i(lonMin, &g, &globalZone);
         iFin= lon2i(lonMax, &g, &globalZone);
//fprintf(stderr,"************ 1) zone:lat=%f->%f, lon=%f->%f, lat2j=%d->%d, lon2i=%d->%d, w=%d, h=%d, paslat=%f, paslon=%f\n",
//		latMin,latMax,lonMin,lonMax,jDeb,jFin,iDeb,iFin,w,h,paslat,paslon);
         //la borne en width et height ne peut etre atteinte
//         if( iDeb == g.width) iDeb--;
//         if( jDeb == g.height) jDeb--;
//         if( iFin == g.width) iFin--;
//         if( jFin == g.height) jFin--;
         
         // recalcul de la zone extraite
//         lonMin = i2lon(iDeb,  &g, &globalZone);
//         latMin = j2lat(jDeb,  &g, &globalZone);
//         lonMax = i2lon(iFin, &g, &globalZone) ;
//         latMax = j2lat(jFin, &g, &globalZone)
// ajout des limites de domaines
// car les positions sont aux centres pour les macro I2 et j2
//		 z->lonMax = lonMax + paslon/2.;
//         z->lonMin = lonMin - paslon/2.;
//         z->latMax = latMax - paslat/2.;
//         z->latMin = latMin + paslat/2.;
//fprintf(stderr,"************ 2) zone:lat=%f->%f, lon=%f->%f\n",z->latMin,z->latMax,z->lonMin,z->lonMax);
         
	 // extraction dans une nouvelle grille
         grid= allocGrid(iFin -iDeb +1, jFin -jDeb +1);
//		grid= allocGrid((z->lonMax-z->lonMin)*paslon, (z->latMax-z->latMin)*paslon);
         if(iFin < iDeb)
         {
            freeGrid(grid);
            grid=NULL;
//            fprintf(stderr,"  Not now ok on partial grid crossing in longitude \n iDeb %d Ifin %d jDeb %d jFin %d\n", iDeb ,iFin, jDeb, jFin);
         }
         else
         {
           int i,j, pos, skip, width;
           unsigned char *data=(unsigned char *)(grid->datas);
           pos= (g.width*jDeb + iDeb) * sizeof(unsigned int);
           lo_lseek(db, id, pos, SEEK_SET);
           width= (iFin -iDeb +1) * sizeof(unsigned int);
           skip= g.width * sizeof(unsigned int) - width ;
//           fprintf(stderr,"*** id=%d, g.w=%d, w=%d, pos=%d, skip=%d\n", id, g.width, width, pos, skip);
           for(i=jDeb; i<=jFin; i++, data += width)
           {
        	   K= lo_read(db, id, (unsigned char*)data, width);
//fprintf(stderr,"*** i=%d, K=%d\n", i, K);
             if(K != (unsigned long)width)
             {
               fprintf(stderr, "Err readLine ligne %d read %ld sur %d demandes\n", i, K, width); 
               fprintf(stderr, "EXIT trap:\n iDeb %d iFin %d jDeb %d jFin %d\n pos %d width %d skip %d\n",
                                 iDeb , iFin , jDeb , jFin , pos , width , skip);
               exit (1);
             }
             lo_lseek(db, id, skip, SEEK_CUR);
           }
         }
       }
       else
       {
         unsigned long  K, size= g.width * g.height * sizeof(int);
         grid= allocGrid(g.width , g.height);
         if( (K= lo_read(db, id, (char*)grid->datas, size)) != size)
            fprintf(stderr," Erreur Read OID : %ld sur %d octets\n", K, size);
//         else
//        	 fprintf(stderr,"  Full Read OID : %ld sur %d octets\n", K, size);
       }
       lo_close(db,id);
     }
     else fprintf(stderr," Erreur Open OID : %d\n", oidGrille);
     PQexec(db,("END"));
   }
//   fprintf(stderr,"Open OID \n");
   return grid;
}

ptrGridGL extractGridFromDb(EccadDB db, int idGrille, ptrZone z)
{
   static LIST params=NULL;
   ptrGridGL gl=NULL;
   PGresult *res;
   char query[256];

   sprintf(query,
     "SELECT valeurs_grille, id_param, ncol_grille, nlign_grille, date_grille FROM grille WHERE id_grille = %d;", idGrille);
   res=PQexec(db,query);
   if(checkSQLErrors(res) && PQntuples(res))
   {
      int oid=     atoi(PQgetvalue(res, 0, 0));
      int idParam= atoi(PQgetvalue(res, 0, 1));
      int w=  atoi(PQgetvalue(res, 0, 2));
      int h=  atoi(PQgetvalue(res, 0, 3));
//      if(!params) params=initParams(db);
      ptrParam p;
//      if( p= (ptrParam)search_list(params, &idParam, (PFI)inParamId) )
      if( p = (ptrParam)getParamFromId(db,idParam) )
      {
        ptrGrid g;
	Zone zoneExtr;
	g= loadDatasFromBd( db, oid, w, h, z);
	strcpy(g->date, PQgetvalue(res, 0, 4) );
        gl=  allocGridGL(g, z);
        gl->param=p;
      }
      else fprintf(stderr, " No found para for id %d\n", idParam);

    }
    PQclear(res);
    return gl;  
}

LIST getGrids(EccadDB db, int idProduct, int idSecteur, int idRcp, ptrParam p, ptrZone z,
                      const char* dateBegin, const char* dateEnd, const char* alt)
{
   int i;
   LIST grids=NULL;
   char query[2000];
   strcpy(query, "SELECT valeurs_grille, ncol_grille, nlign_grille, date_grille, deltadate_grille");
   strcat(query, ", (cast(date_grille+deltadate_grille as date)-1) as dateFinal FROM grille");
   if (strcmp(alt,"0")) {
	   strcat(query, " LEFT JOIN code ON id_grille = num_code");
   }
   sprintf(&query[strlen(query)], " WHERE id_param = %d",  p->id);
   sprintf(&query[strlen(query)], " AND id_produit = %d",  idProduct);
   sprintf(&query[strlen(query)], " AND date_grille BETWEEN '%s' AND '%s'", dateBegin, dateEnd);
   if (idSecteur != 0) {
	   sprintf(&query[strlen(query)], " AND id_secteur = %d",  idSecteur);
   } else {
	   strcat(query, " AND (id_secteur is null OR id_secteur");
	   strcat(query, " IN (select id_secteur from secteur where fullname_secteur <> 'Aviation'))");
   }
   if (idRcp != 0) {
	   sprintf(&query[strlen(query)], " AND id_rcp = %d",  idRcp);
   } else {
	   strcat(query," AND id_rcp is null");
   }
   if (strcmp(alt,"0")) {
   	   sprintf(&query[strlen(query)], " AND legend_code = '%s'",  alt);
   }
   strcat(query," order by date_grille");
fprintf(stderr, "********* requete= %s\n", query);
	PGresult *res=PQexec(db,query);
//fprintf(stderr, "found  %d grilles\n", PQntuples(res));
//fprintf(stderr, "param=%d, prod=%d, dateBeg=%s, dateEnd=%s\n", p->id, idProduct, dateBegin,dateEnd);
   if(checkSQLErrors(res) && PQntuples(res) ) 
   {
	 // origin size
     int width= atoi(PQgetvalue(res,0,1));
     int height= atoi(PQgetvalue(res,0,2));
//fprintf(stderr, "w=%d, h=%d\n", width,height);
     ptrZone zTemp = (ptrZone)malloc(sizeof(Zone));
	 memcpy(zTemp, z, sizeof(Zone));

     for(i=0; i< PQntuples(res); i++)
     {
    	memcpy(z, zTemp, sizeof(Zone));
       int oid= atoi(PQgetvalue(res,i,0) );
//fprintf(stderr, "Load oid %d from %dx%d grille date %s\n", oid,  width, height, PQgetvalue(res, i, 3) );

//fprintf(stderr,"Avant--------- : Extrait Reel Lat min %f max %f   lon min %f max %f \n",z->latMin,z->latMax,z->lonMin,z->lonMax);


       ptrGrid g= loadDatasFromBd( db, oid, width, height, z);
       
//fprintf(stderr,"Après--------- : Extrait Reel Lat min %f max %f   lon min %f max %f \n",z->latMin,z->latMax,z->lonMin,z->lonMax);
//fprintf(stderr,"date grille %s\n",g->date);
       if(g)
       {
         char *date= PQgetvalue(res, i, 3);
         sprintf(g->date, "%.4s%.2s%.2s", date, &date[5], &date[8]);
         char *delta= PQgetvalue(res, i, 4);
         sprintf(g->delta, "%s", delta);
         char *dateEndPeriod= PQgetvalue(res, i, 5);
         sprintf(g->dateEndPeriod, "%.4s%.2s%.2s", dateEndPeriod, &dateEndPeriod[5], &dateEndPeriod[8]);
//         sprintf(g->date, "%s", date);
//fprintf(stderr,"date grille=%s, dateFinPeriode=%s, delta=%s, i=%d, nbtuples=%d\n",g->date,g->dateEndPeriod,g->delta,i,PQntuples(res));
         if(!grids) grids = make_list();
			 add_to_tail(grids, g);
       }
       else fprintf(stderr, "loadGrid error\n");
     }
   }
   PQclear(res);
   return grids;
}



ptrGrid getGrid(EccadDB db, int idGrille, ptrZone z)
{
//	fprintf(stderr, "--getGrid \n");fflush(stderr);
	PGresult *res;
   	char query[256];
   	sprintf(query,
      "SELECT valeurs_grille, ncol_grille, nlign_grille, date_grille FROM grille WHERE id_grille = %d;", idGrille);
// fprintf(stderr, "-->requete getGrid -> %s\n",query );
   res=PQexec(db,query);
   //fprintf(stderr, "found  %d grilles SQLErrors %d\n", PQntuples(res), checkSQLErrors(res));
   ptrGrid g = NULL;
   
   if(checkSQLErrors(res) && PQntuples(res) ) 
   {
   	// origin size 
     int width= atoi(PQgetvalue(res,0,1));
     int height= atoi(PQgetvalue(res,0,2));
     
     int oid= atoi(PQgetvalue(res,0,0) );
//fprintf(stderr,"Avant--------- : Extrait Reel Lat min %f max %f   lon min %f max %f \n",z->latMin,z->latMax,z->lonMin,z->lonMax);

     g= loadDatasFromBd( db, oid, width, height, z);
     
//fprintf(stderr,"Apres--------- : Extrait Reel Lat min %f max %f   lon min %f max %f \n",z->latMin,z->latMax,z->lonMin,z->lonMax);
     
     
     if(g)
     {
     	char *date= PQgetvalue(res, 0, 3);
        sprintf(g->date, "%.4s%.2s%.2s", date, &date[5], &date[8]);
        
//   				fprintf(stderr, "date grille %s \n", date);
     }   
   }
   PQclear(res);
   return g;
}

ptrGrid getGridFromProduit(EccadDB db, char* nomProd, ptrZone z)
{
	PGresult *res;
	char query[256];
	sprintf(query,
		"SELECT valeurs_grille, ncol_grille, nlign_grille, date_grille FROM grille WHERE id_produit in (select id_produit from produit where title_produit = '%s');", nomProd);
	res=PQexec(db,query);
	ptrGrid g = NULL;
	if(checkSQLErrors(res) && PQntuples(res) )
	{
		// origin size
		int width= atoi(PQgetvalue(res,0,1));
		int height= atoi(PQgetvalue(res,0,2));
		int oid= atoi(PQgetvalue(res,0,0) );
		g= loadDatasFromBd( db, oid, width, height, z);

		if(g)
		{
			char *date= PQgetvalue(res, 0, 3);
			sprintf(g->date, "%.4s%.2s%.2s", date, &date[5], &date[8]);
		}
	}
	PQclear(res);
	return g;
}

double* getMaxParam(int idGrid, EccadDB conn)
{
	double* maxParam = NULL;
	PGresult *res;
	char query[256];
    sprintf(query,"SELECT id_param FROM grille WHERE id_grille=%d;", idGrid);
    res=PQexec(conn,query);
    
    if(checkSQLErrors(res) && PQntuples(res) ) 
    {
    	int idParam= atoi(PQgetvalue(res,0,0));
    	
//    	ptrParam p;
//    	LIST params=initParams(conn);
//    	if( p= (ptrParam)search_list(params, (NODE)&idParam, (PFI)inParamId) )
//			maxParam = &p->maxi;

    	ptrParam p = (ptrParam)getParamFromId(conn,idParam);
    	maxParam = &p->maxi;
    }
   	return maxParam;
}

double* getMinParam(int idGrid, EccadDB conn)
{
	double* minParam = NULL;
	PGresult *res;
	char query[256];
    sprintf(query,"SELECT id_param FROM grille WHERE id_grille=%d;", idGrid);
    res=PQexec(conn,query);
    
    if(checkSQLErrors(res) && PQntuples(res) ) 
    {
    	int idParam= atoi(PQgetvalue(res,0,0));
    	
//    	ptrParam p;
//    	LIST params=initParams(conn);
//    	if( p= (ptrParam)search_list(params, (NODE)&idParam, (PFI)inParamId) )
//			minParam = &p->mini;

    	ptrParam p = (ptrParam)getParamFromId(conn,idParam);
		minParam = &p->mini;

    }
    
	return minParam;
}

long datation (long j, long m, long a){
	long n = a*365 + (m-1)*31 + j ;
	if (m <= 2) a-- ;
	n = n + a/4 - a/100 + a/400 ;
	if (m > 2) n = n - (int)((m-1)*0.4+2.7) ;
	return n ;
}