--Le but de ce script est d'individuer les foyers domestiques qui s'installent dans un supercarré jamais occupé auparavant pendant les 25 premières années depuis sa première occupation ; tous ces foyers sont suivis longitudinalement pendant 25 ans. Ce script n'identifie pas le supercarré, lesquelles cordonnées doivent être prises d'une autre façon (par ex. avec un SIG) et insérées en début de script.

declare @year, @tab, @tempo, @xcord, @ycord, @fondc, @fondn, @id, @maison;

set @year = 101;

set @tempo = 1;

--declarer coordonnées X et Y du coin en bas à gauche du supercarré à explorer
set @xcord = 573000;
set @ycord = 4751400;


--créer une table temporaire 1 
   CREATE TABLE  "tmpTab" (

"Time" double precision, 
"Simulation"double precision, 
"Id"double precision, 
"Xmodel"double precision, 
"Ymodel"double precision, 
"NbPersons"double precision, 
"NbFamilies"double precision, 
"NbHouseholds"double precision, 	
"TimeStart"double precision, 
"TimeEnd"double precision, 
"05Living"double precision, 
"05dead"double precision, 
"05sent"double precision, 
"05received"double precision, 
"515Living"double precision, 
"515dead"double precision, 
"515sent"double precision, 
"515received"double precision, 
"1517Living"double precision, 
"1517dead"double precision, 
"1517sent"double precision, 
"1517received"double precision, 
"1725Living"double precision, 
"1725dead"double precision, 
"1725sent"double precision, 
"1725received"double precision, 
"25+Living"double precision, 
"25+dead"double precision, 
"60-Living"double precision, 
"60-dead"double precision, 
"60-sent"double precision, 
"60-received"double precision, 
"60+Living"double precision, 
"60+dead"double precision, 
"60+sent"double precision, 
"60+received"double precision, 
"EntryMales"double precision, 
"EntryFemales"double precision, 
"OutputMales"double precision, 
"OutputFemales"double precision, 
"IdScissionFrom"double precision, 
"NbBovins"double precision, 
"NbCaprins"double precision, 
"NbPorcins"double precision, 
"GainsBovins"double precision, 
"GainsCaprins"double precision, 
"GainsPorcins"double precision, 
"Hunting"double precision, 
"Picking"double precision, 
"Seed"double precision, 
"NetCrop"double precision

)
WITH (
  OIDS=FALSE
   );




while @year < 701


begin
   SET @tab = 'sites' + CAST (@year AS STRING); -- Casts @I
  -- SET @tab2 =  CAST (@year AS STRING); -- Casts @I


-- les données du supercarré selectionné sont extraites dans la table temporaire 1
INSERT INTO "tmpTab"
SELECT 
 *
 FROM
 public.@tab
 WHERE 
 @tab."Xmodel" >= @xcord AND
@tab."Xmodel" <= (@xcord + 20000) AND
@tab."Ymodel" >= @ycord AND
@tab."Ymodel" <= (@ycord + 20000) ;	

 SET @year = @year + 1;
 END


--créer une table temporaire 2

 CREATE TABLE  "tmpTab2" (

"Time" double precision, 
"Simulation"double precision, 
"Id"double precision, 
"Xmodel"double precision, 
"Ymodel"double precision, 
"NbPersons"double precision, 
"NbFamilies"double precision, 
"NbHouseholds"double precision, 	
"TimeStart"double precision, 
"TimeEnd"double precision, 
"05Living"double precision, 
"05dead"double precision, 
"05sent"double precision, 
"05received"double precision, 
"515Living"double precision, 
"515dead"double precision, 
"515sent"double precision, 
"515received"double precision, 
"1517Living"double precision, 
"1517dead"double precision, 
"1517sent"double precision, 
"1517received"double precision, 
"1725Living"double precision, 
"1725dead"double precision, 
"1725sent"double precision, 
"1725received"double precision, 
"25+Living"double precision, 
"25+dead"double precision, 
"60-Living"double precision, 
"60-dead"double precision, 
"60-sent"double precision, 
"60-received"double precision, 
"60+Living"double precision, 
"60+dead"double precision, 
"60+sent"double precision, 
"60+received"double precision, 
"EntryMales"double precision, 
"EntryFemales"double precision, 
"OutputMales"double precision, 
"OutputFemales"double precision, 
"IdScissionFrom"double precision, 
"NbBovins"double precision, 
"NbCaprins"double precision, 
"NbPorcins"double precision, 
"GainsBovins"double precision, 
"GainsCaprins"double precision, 
"GainsPorcins"double precision, 
"Hunting"double precision, 
"Picking"double precision, 
"Seed"double precision, 
"NetCrop"double precision

)
WITH (
  OIDS=FALSE
   );

--les données des foyers vivants dans la première année d'occupation du supercarré sont extraites dans la table temporaire 2  (la raison : identifier la première année de colonisation)
INSERT INTO "tmpTab2"
SELECT 
 *
 FROM
 public."tmpTab"
 WHERE 
"tmpTab"."Time" = "tmpTab"."TimeStart";




SET @maison = 1;






declare @fondc, @maison,@tempo, @id, @lock;

--l'année de première colonisation est identifiée et devient une variable
SET @fondc = SELECT MIN(CAST("Time" AS intEgER))
FROM public."tmpTab2" ;


--créer une table temporaire 3
CREATE TABLE  "tmpTab3" (

"Time" double precision, 
"Simulation"double precision, 
"Id"double precision, 
"Xmodel"double precision, 
"Ymodel"double precision, 
"NbPersons"double precision, 
"NbFamilies"double precision, 
"NbHouseholds"double precision, 	
"TimeStart"double precision, 
"TimeEnd"double precision, 
"05Living"double precision, 
"05dead"double precision, 
"05sent"double precision, 
"05received"double precision, 
"515Living"double precision, 
"515dead"double precision, 
"515sent"double precision, 
"515received"double precision, 
"1517Living"double precision, 
"1517dead"double precision, 
"1517sent"double precision, 
"1517received"double precision, 
"1725Living"double precision, 
"1725dead"double precision, 
"1725sent"double precision, 
"1725received"double precision, 
"25+Living"double precision, 
"25+dead"double precision, 
"60-Living"double precision, 
"60-dead"double precision, 
"60-sent"double precision, 
"60-received"double precision, 
"60+Living"double precision, 
"60+dead"double precision, 
"60+sent"double precision, 
"60+received"double precision, 
"EntryMales"double precision, 
"EntryFemales"double precision, 
"OutputMales"double precision, 
"OutputFemales"double precision, 
"IdScissionFrom"double precision, 
"NbBovins"double precision, 
"NbCaprins"double precision, 
"NbPorcins"double precision, 
"GainsBovins"double precision, 
"GainsCaprins"double precision, 
"GainsPorcins"double precision, 
"Hunting"double precision, 
"Picking"double precision, 
"Seed"double precision, 
"NetCrop"double precision

)
WITH (
  OIDS=FALSE
   );

--toutes les données du supercarré relatives aux 100 saisons (25 ans) suivants au moment de sa colonisation sont exctraites dans la table temporaire 3 .
INSERT INTO "tmpTab3"
SELECT 
 *
 FROM
 public."tmpTab2"
 WHERE 
"tmpTab2"."TimeStart" < @fondc + 100;


--procédure pour identifier les foyers arrivées dans le supercarré pendant les 25 ans qui vient d'être identifiés

set @maison = 1;

while @maison < 6
begin

set @tempo = 1;

while @tempo < 26

begin

SET @id = SELECT CAST (
  "tmpTab3"."Id" AS INTEGER)
FROM 
  public."tmpTab3" limit 1;

SET @lock = SELECT CAST (
  "tmpTab3"."TimeStart" AS INTEGER)
FROM 
  public."tmpTab3" limit 1;
  
  --ces données sont insérées dans la table appélée renod, qui avait être crée en dehors de ce script. @tempo se refère à l'année du front pionnier, donc de renod1 à renod25

INSERT INTO renod@tempo
SELECT 
 *
 FROM
 public."tmpTab"
 WHERE 
"tmpTab"."Time" = @lock + (4 * @tempo) - 4
AND "tmpTab"."Id" = @id;
 SET @tempo = @tempo +1;

END



 
 SET @maison = @maison +1;
 DELETE FROM   public."tmpTab3" WHERE "tmpTab3"."Id" = @id;
 END






SET @maison = 1;


--création de la table temporaire 4

CREATE TABLE  "tmpTab4" (

"Time" double precision, 
"Simulation"double precision, 
"Id"double precision, 
"Xmodel"double precision, 
"Ymodel"double precision, 
"NbPersons"double precision, 
"NbFamilies"double precision, 
"NbHouseholds"double precision, 	
"TimeStart"double precision, 
"TimeEnd"double precision, 
"05Living"double precision, 
"05dead"double precision, 
"05sent"double precision, 
"05received"double precision, 
"515Living"double precision, 
"515dead"double precision, 
"515sent"double precision, 
"515received"double precision, 
"1517Living"double precision, 
"1517dead"double precision, 
"1517sent"double precision, 
"1517received"double precision, 
"1725Living"double precision, 
"1725dead"double precision, 
"1725sent"double precision, 
"1725received"double precision, 
"25+Living"double precision, 
"25+dead"double precision, 
"60-Living"double precision, 
"60-dead"double precision, 
"60-sent"double precision, 
"60-received"double precision, 
"60+Living"double precision, 
"60+dead"double precision, 
"60+sent"double precision, 
"60+received"double precision, 
"EntryMales"double precision, 
"EntryFemales"double precision, 
"OutputMales"double precision, 
"OutputFemales"double precision, 
"IdScissionFrom"double precision, 
"NbBovins"double precision, 
"NbCaprins"double precision, 
"NbPorcins"double precision, 
"GainsBovins"double precision, 
"GainsCaprins"double precision, 
"GainsPorcins"double precision, 
"Hunting"double precision, 
"Picking"double precision, 
"Seed"double precision, 
"NetCrop"double precision

)
WITH (
  OIDS=FALSE
   );
   
   --la meme procédure effectuée précedemment (avec la table temp3) est répétée : dans le modèle BEAN, deux foyers domestiques s'installent simultanément dans le hameau (foyer des parents et foyer du couple marié). Par conséquent, la procédure est effectuée deux fois.

INSERT INTO "tmpTab4"
SELECT 
 *
 FROM
 public."tmpTab"
 WHERE 
"tmpTab"."TimeStart" < @fondc;

set @maison = 1;

while @maison < 6
begin

set @tempo = 1;


SET @id = SELECT CAST (
  "tmpTab4"."Id" AS INTEGER)
FROM 
  public."tmpTab4" limit 1;

SET @lock = SELECT CAST (
  "tmpTab4"."Time" AS INTEGER)
FROM 
  public."tmpTab4" limit 1;

while @tempo < 26

begin


INSERT INTO renod@tempo
SELECT 
 *
 FROM
 public."tmpTab"
 WHERE 
"tmpTab"."Time" = @lock + (4 * @tempo) - 4
AND "tmpTab"."Id" = @id;
 SET @tempo = @tempo +1;

END



 
 SET @maison = @maison +1;
 DELETE FROM   public."tmpTab4" WHERE "tmpTab4"."Id" = @id;
 END

--les données finales se trouvent donc dans 25 tables, appélées de renod1 à renod 25; chaque table est une année de vie sur le front pionnier. Ces tables peuvent être ensuite traitées separemment

--les tables temporaires sont effacées

 DROP TABLE "tmpTab";
DROP TABLE "tmpTab2";
DROP TABLE "tmpTab3";
DROP TABLE "tmpTab4";