#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use POSIX;
use lib qw(/usr/local/lib/mpmasql/modules);
use File::Path;
use List::Util qw[min max];
use Storable qw/dclone/;
use Data::Dumper;
use Term::ReadKey;
#Own Classes
use CtUtilities qw(trim get_unique queryhash extqueryhash strip_sql nice_hash_dump);
use IndexedTable;
use SimpleObjectRegistry;
use MpmasQLConfigReader qw(ReadCfg ReadCll ReadIni ReadVar);
use MpmasQLVariableProcessing qw(define_scenarios adapt_for_scenario);
use MpmasQLFunctionLanguage;
use MpmasQLClassDefinition;
#MP-MAS Input File Classes
use MpmasInputfile;
use MpmasInputfileSet;
use MpmasBData;
use MpmasSpatialExchangeInfo;
use MpmasTimehandler;
use MpmasMarketSet;
use MpmasMarket;
use MpmasMILPSet;
use MpmasMILP;
use MpmasMILPMultiperiod;
use MpmasNetworkObject;
use MpmasMILPActivity;
use MpmasMILPConstraint;
use MpmasMILPSOS;
use MpmasClusterSet;
use MpmasDemographySet;
use MpmasNetworkSet;
use MpmasSegmentSet;
use MpmasCropExt;
use MpmasCropExtLinkXN;
use MpmasCropDbgActivityID;
use MpmasCropDbgYield;
use MpmasCropYieldsSet;
use MpmasCropSimilarity;
use MpmasRegion;
use MpmasPerennial;
use MpmasLivestock;
use MpmasCropWat;
use MpmasCropWatPrecipitation;
use MpmasCropWatEvapotranspiration;
use MpmasEDICRiverFlows;
use MpmasEDICRiverFlowTimeSeries;
use MpmasEDICIrrigation;
use MpmasEDICSurface;
use MpmasEDICSubsurface;
use MpmasActualWaterRights;
use MpmasInfrastructureProjects;
use MpmasEDICCanalModel;
use MpmasRentalContracts;
use MpmasProducerOrganizationSet;
use MpmasProducerOrganizationMILPSet;
use MpmasSoilToNRU;
use MpmasLandMarket;
use MpmasInvestmentsAttachedToLand;
use MpmasEUCAP2003;
#auxiliary File classes
use MpmasActivityInfo;
use MpmasActivityInfoEdicMatlab;
use MpmasConstraintInfo;
use MpmasConstraintInfoEdicMatlab;
use MpmasNetworkObjectInfo;
use MpmasStataLabelsU;
use MpmasStataLabelsK;
use MpmasStataLabelsP;
use MpmasStataGroups;
use MpmasStataImport;
use MpmasRImport;
use MpmasQLConstraintAnalysis;
use MpmasExternalScriptCalls;
=head1 mpmasql

mpmasql Perl script to create MPMAS input files (ASCII) from SQL databases and configuration files


=cut

#my $logMem = 0;

#if ($logMem) {
	#use Devel::SizeMe qw(perl_size heap_size size total_size);
	#use Devel::Cycle;
#}

##Version:

my $thisversion = "v3.123";

my $lastchange = "15 Nov 2021";
my $starttime = time;

my $longwelcome = <<WC;


####################################################################
####			mpmasql $thisversion				####
####	Perl script to create MPMAS input files (ASCII) 	####
####	from SQL databases and configuration files		####
####								####
####	(c)	Christian Troost, Univ. Hohenheim, 2009-2021	####
####	Version: $thisversion		$lastchange			####
####################################################################
WC

my $shortwelcome = <<WC2;
mpmasql $thisversion		
(c)	Christian Troost, Universität Hohenheim, 2009-2021
WC2

#Declarations and hard-coded defaults initializations
my $ininame;
my $singlemode = 0;
my $singlefiles = 0;
my $octave = 0;
my $selgroup = "crop";
my $mpmasoff = 0;
my $tableau_compact = 0;
my $outdir = "testoutput/"; #global for the output directory
my $perennials = 0;
my $outdir_override = 0;
my %verbosityflag;
my %additionaloutput;
my $backwards;
my $runonly = 0;
my %setstrings;
my $saveCONSTYPEforRimport = -1;
my $saveSHDW_SWITCHforRimport = -1;

print $shortwelcome;

#Parse commandline arguments
while (@ARGV) {
	my $element = shift @ARGV;
	if  ($element =~ /^-v$/) {
		print $longwelcome;
		exit 0;
	}
	elsif  ($element =~ s/^-S//) {
		#set conversion modus to Single Matrix Mode
		$singlemode = 1;
		$singlefiles = 1;
		unshift @ARGV, "-".$element if (defined $element and not $element =~/^\s*$/);
	}
	elsif  ($element =~ /^--octave/) {
		#create single matrix for use with octave
		$octave = 1;
		$singlemode = 1; 
		$selgroup = shift @ARGV if (not $ARGV[0] =~ /^-/);
	}
	elsif ($element =~ /^-O/) {
		# alternative output directory
		$outdir_override = 1;
		my $od;
		$od = shift @ARGV if (not $ARGV[0] =~ /^-/);
		$outdir = $od || ".";
		print "Output directory in control file overridden by commandline argument...\n";
	}
	elsif ($element =~ /^-R/) {
		my $od;
		srand( shift( @ARGV)) if ($ARGV[0] =~ /^\s*\d+\s*/);
		
	}
	elsif  ($element =~ s/^--nomas$// or $element =~ s/^-n//) {
		#verbosity 1
		$mpmasoff = 1;
		print "MPMAS specific checking of MILP switched off!\n";
		unshift @ARGV, "-".$element if (defined $element and not $element =~/^\s*$/);

	}
	elsif  ($element =~ s/^--compact$// or $element =~ s/^-c//) {
		#verbosity 1
		$tableau_compact = 1;
		print "Use compact format for decision matrix\n";
		unshift @ARGV, "-".$element if (defined $element and not $element =~/^\s*$/);
	}
	elsif  ($element =~ s/^--runonly$// or $element =~ s/^-r//) {
		#verbosity 1
		$runonly = 1;
		print "Only create run scripts\n";
		unshift @ARGV, "-".$element if (defined $element and not $element =~/^\s*$/);
	}
	elsif  ($element =~ /^-l(\d+)$/) {
		
		#verbosity flags
		my %temp = (
			1 => "VERBOSE_READINI" ,
			2 => "VERBOSE_READCFG",
			3 => "VERBOSE_READCLL",
			4 => "VERBOSE_SCEN",
			5 => "VERBOSE_BIND",
			6 => "VERBOSE_CLASS",
			7 => "VERBOSE_INTERPRETE",
			8 => "VERBOSE_SORT" ,
			9 => "VERBOSE_FILES");
		my $flag = $temp{$1};
		$verbosityflag{$flag} = 1;
	}
	elsif  ($element =~ s/^--nocomment$// or $element =~ s/^-x//) {
	      $additionaloutput{"NO_COMMENTED"} = 1;
	      unshift @ARGV, "-".$element if (defined $element and not $element =~/^\s*$/);
	}
	elsif  ($element =~ /^-a(\d+)$/) {
		my %temp = (
			0 => "NO_COMMENTED",
			1 => "ADD_CONANALYSIS",
			2 => "ADD_EDICMATLAB");
		my $flag = $temp{$1};
		$additionaloutput{$flag} = 1;
	}	
	elsif  ($element =~ /^--bw(\d+)$/) {
		$backwards = $1;

	}
	elsif ($element =~ /^--set/ ) {
		if (not $ARGV[0] =~ /^-/ ) {
		    my $setstring = shift @ARGV;
		    my ($var, $val) = split /=/,$setstring,2;
		    $var =~ s/^\s*//;
		    $val =~ s/^\s*//;
		    $var =~ s/\s*$//;
		    $val =~ s/\s*$//;

		    $setstrings{$var} = $val;
		}
	}
	elsif (not $element =~ /^-/) {
		# inifile name
		$ininame = $element;
		print "\nConversion control file:\t\t". $ininame."\n";  
	}
	elsif ($element =~ /^-h/ or $element =~ /^--help/ or $element =~ /^-\?/) {
		print $shortwelcome;
		print <<HELP;
Usage:
	mpmasql <filename> [options]
	
	<filename>	name of the conversion control file to be processed

Options:
	-S		single matrix mode
	-c, --compact	use compact matrix format (only for use with mqlmatrix)
	-n, --nomas	do not check for compatibility with MPMAS format

	-x, --nocomment	suppress writing of commented output files
	-O <directory>	override output directory set in .ini file (for both uncommented and commented files)
	-r, --runonly	create only run script
	
	--set "<variable>=<value>"	change value of .ini file entry

	-a1		report on matrix consistency
	-a2		produce files for EDICMatlab routines

	-v		print version information
	-h, --help, -?	print this help
		
	Verbosity flags:
	-l1		show conversion control settings
	-l2		show model configuration 
	-l3		verbose when reading model structure file
	-l4		verbose when processing scenario adaptations
	-l5		verbose when binding variables and tables
	-l6		verbose when preparing model elements
	-l7		verbose when parsing field and value definitions
	-l8		verbose when sorting matrix elements
	-l9		verbose when creating input files

HELP
		exit (0);
	}
}



print "Verbosity flag $_\n" foreach keys %verbosityflag;

#Give error message if no input file was specified
print STDERR "You forgot to specify a conversion control file (.ini)\n" and exit(1) unless $ininame;

######## Read Ini-File
my $inihash = ReadIni($ininame);
if ( $verbosityflag{VERBOSE_READINI}) {
		print "\nConversion control settings:\n";
		print nice_hash_dump($inihash);
}
print STDERR "Error: You forgot to specify a CONFIGURATION file in $ininame" and exit(1) unless (exists $inihash->{CONFIGURATION});
print STDERR "Error: You forgot to specify a DATA file in $ininame" and exit(1) unless (exists $inihash->{DATA});
print STDERR "Error: You forgot to specify a STRUCTURE file in $ininame" and exit(1) unless (exists $inihash->{STRUCTURE});

foreach my $k (keys %setstrings) {
	$inihash->{$k} = $setstrings{$k};
}
my $outdir_cm;
if ($outdir_override) {
	$outdir_cm = $outdir;
}
elsif ($singlemode) {
	$outdir =  $inihash->{OUTDIR} if (exists $inihash->{OUTDIR});
	$outdir =  $inihash->{OUTDIR_S} if (exists $inihash->{OUTDIR_S});   
	$outdir_cm = $inihash->{OUTDIR_S_CM} || $outdir;
}
else {
	$outdir =  $inihash->{OUTDIR} if (exists $inihash->{OUTDIR});   
	$outdir_cm = $inihash->{OUTDIR_CM} || $outdir;
}
#



#create the output path if non-existent
&mkpath ($outdir); 
&mkpath ($outdir_cm); 

my $skipping = 0;
$skipping = 1  if exists $inihash->{SKIP} and $inihash->{SKIP};

my %skiplist;
if ($skipping > 0) {
	for my $i (@{$inihash->{SKIP}}) {
		my $h = lc($i);
		$skiplist{$h} = 1;
		print "Skipping $h -> $skiplist{$h}\n";
	}
}
 


print "\nModel configuration file:\t\t". $inihash->{CONFIGURATION}."\n";
print "Model structure file:\t\t\t". $inihash->{STRUCTURE}."\n";
print "Model data file:\t\t\t". $inihash->{DATA}."\n";
print "\nMPMAS input files (.dat) will be written to directory:\n\t\t". $outdir."\n";
print "Commented input files (.txt) will be written to directory:\n\t\t". $outdir_cm."\n";

#### READ in CONFIGURATION FILE
my $baseconfigs;
$baseconfigs = ReadCfg("/etc/mpmasql/default.cfg", $baseconfigs);
$baseconfigs = ReadCfg( $inihash->{CONFIGURATION}, $baseconfigs);
$baseconfigs->{SYMBOLICS} = 0; #this value is auto-determined later by the number of networkobjects of type 2 found 
$baseconfigs->{MPMASCHECKSOFF}  = $mpmasoff;
if ( $verbosityflag{VERBOSE_READCFG}) {
		print "\nModel configuration:\n";
		print nice_hash_dump($baseconfigs);
}
##Initialize grouping hashes, so we do not get an error if no resultgroup has been defined
$baseconfigs->{Groupings}->{activity} = {};
$baseconfigs->{Groupings}->{constraint} = {};


#### READ DATA FILE
my ($basesetup, $baseglobals, $instances2, $basetables, $globalrank);
($basesetup, $baseglobals, $instances2, $basetables, $baseconfigs, $globalrank) =  ReadVar( $inihash->{DATA}, $baseconfigs); 

#### READ STRUCTURE FILES
my $baseclassregistry = SimpleObjectRegistry->create();
my $baseinstances;
($baseclassregistry, $baseconfigs) = ReadCll("/etc/mpmasql/default.cll" , $baseclassregistry, $baseconfigs, $verbosityflag{VERBOSE_READCLL}, $mpmasoff);
($baseclassregistry, $baseconfigs, $baseinstances) = ReadCll( $inihash->{STRUCTURE}, $baseclassregistry, $baseconfigs, $verbosityflag{VERBOSE_READCLL}, $mpmasoff);
#Merge instances read from .var file and instances read from .cll file:
@{$baseinstances}{keys %$instances2} = values %$instances2;


#### Connect to Database
my $dbh;
ReadMode(0);
if (defined  $inihash->{DBTYPE} and defined $inihash->{DB}) {
	if (not $inihash->{DBPWD}) {
		print "Password: ";
		ReadMode 2;	
		 $inihash->{DBPWD} = ReadLine (0);
		print "\n";
		ReadMode 1;
	}
	chomp( $inihash->{DBPWD});
	$dbh = DBI->connect("dbi:".$inihash->{DBTYPE}.":". $inihash->{DB}, $inihash->{DBUSER}, $inihash->{DBPWD}, { RaiseError => 1, AutoCommit => 1 }) or die $!;
	print "Successfully connected to " . $inihash->{DB} . " as ". $inihash->{DBUSER} . "\n"; 
	$dbh->do("SET NAMES utf8");
	$dbh->{mysql_auto_reconnect} = 1;

}


MpmasQLFunctionLanguage->set_globals( $singlemode, $dbh, $inihash,"",0);
for my $s ("DBGMILPS", "SKIP_AGENTS", "USE_ONLY_AGENTS") {
	if ($inihash->{$s} and not ref $inihash->{$s}) { #no interpretation if simple list (, ) has already been converted into array
		#if ($s =~ /[-#+\/*~[\]\\^]+/) {
					my ($temp, $tempfor);
					eval { 	($temp, $tempfor) = MpmasQLFunctionLanguage->translate_and_interprete($inihash->{$s},{}); };
					$inihash->{$s} = $temp;
		#}
	}
}
#### READ SCENARIO DEFINITIONS ( and RHSides if in  Standalone Matrix mode)
MpmasQLFunctionLanguage->set_globals( $singlemode, $dbh, $inihash,"",0);
my ($uscenlist, $scendef, $scencommon) = define_scenarios( $dbh, $inihash->{SCENLIST}, $inihash->{SCENDEF}, $inihash->{COMMON}, $verbosityflag{VERBOSE_SCEN});

my @scenlist = sort { $a cmp $b} @$uscenlist;



my ($baserhsdef, $rhslist, $collected_rhshash);
if ($singlemode) {
    if (defined $inihash->{RHSDEF} and $inihash->{RHSDEF}->type =~ /^\s*transform_assets/) {
	($rhslist, $baserhsdef) = define_scenarios( $dbh, $inihash->{RHSLIST}, undef, undef, $verbosityflag{VERBOSE_SCEN}) ;
		$baserhsdef = "-99";
    }
    else {
	($rhslist, $baserhsdef) = define_scenarios( $dbh, $inihash->{RHSLIST}, $inihash->{RHSDEF}, undef, $verbosityflag{VERBOSE_SCEN}) 
    }
}

my $scenflags = {};
my @ConstraintGroupingsForAllScenarios;
my @ActivityGroupingsForAllScenarios;
my $maxperiods = 1;
my ($fh, $frh);
####LOOP OVER SCENARIOS 
SCENARIOLOOP: for my $curScen (  @scenlist){
	print "\e[K\n>> Preparing Scenario $curScen...\n\n";
	#if ($logMem) {
		#print "heap size/ perl size: ", heap_size(),", ", perl_size(),"\n";
	#}
	### reset counters
	MpmasQLElementDefinition->reset();
	MpmasNetworkObject->reset_existing_onames();
	### Bind all variables in CFG and VAR entries with scenario specific settings
	my ($mname, $setup, $instances, $configs, $tableregistry, $rhsdef) = adapt_for_scenario( $curScen, $verbosityflag{VERBOSE_SCEN}, $verbosityflag{VERBOSE_BIND}, $dbh, $inihash, $scendef, $scencommon, $baseglobals, $basesetup, $baseinstances, $baseconfigs, $basetables, $globalrank, $baserhsdef, $singlemode);
	my $classregistry = dclone($baseclassregistry); # make an independent copy so scenario changes are not taken over to the next scenario
	MpmasQLFunctionLanguage->set_globals( $singlemode, $dbh, $setup, $tableregistry, $verbosityflag{VERBOSE_INTERPRETE} );
	
	unless ($runonly) {
	      my @milplist;
	      my @landownermilptypes = (1);
	      push @landownermilptypes, 2 if ($configs->{LANDMARKET_MODEL} == 2);
	      for my $t (@landownermilptypes) {
			  for my $m (0..$configs->{MARKETS} -1) {
				  push @milplist, sprintf("%01d%01d" , $t , $m);
			  }
	      }
	      MpmasInputfile->set_milplist(\@milplist);

	      ### Declare Registries for Model Objects
	      my $NetworkObjectRegistry = SimpleObjectRegistry->create();
	      my $ConstraintRegistry = SimpleObjectRegistry->create();
	      my $ActivityRegistry = SimpleObjectRegistry->create();
	      my $PerennialObjectRegistry = SimpleObjectRegistry->create();
	      my $LivestockObjectRegistry = SimpleObjectRegistry->create();	
	      my $TPPenaltyObjectRegistry = SimpleObjectRegistry->create();	
	      my $SOSRegistry = SimpleObjectRegistry->create();

	      ### Create default values for class elements
	      my $defaults = $classregistry->get_object_by_id("DEFAULT");
	      
	      for my $i ( $defaults->elements) {
		      for my $j ($i->fields ) {
			      my $x = $j->content;
			      my $temp;
			      eval {	$temp = MpmasQLFunctionLanguage->interprete ($x, {}, $j->content_verbosity); };
			      print STDERR "Error when interpreting DEFAULT class, Element $i, Field $j:\n$@\n" and exit(1) if $@;
			      $j->set_content($temp->[0]);
			      print "DEFAULT ENTRY ".$i->id." ". $j->id." "." $x -> $temp->[0]\n" if $verbosityflag{VERBOSE_CLASS};
			      
			      if ($i->id eq "asset") {
						MpmasNetworkObject->set_default_value(	$j->id, 	$temp->[0] );

				  }
				  elsif ($i->id eq "activity") {
						MpmasMILPActivity->set_default_value(	$j->id, 	$temp->[0] );

				  }
				  elsif ($i->id eq "constraint") {
						MpmasMILPConstraint->set_default_value(	$j->id, 	$temp->[0] );

				  }		
				  elsif ($i->id eq "sos") {
						MpmasMILPSOS->set_default_value(	$j->id, 	$temp->[0] );

				  }			      
		      }
	      }
	      
	     # MpmasQLClassDefinition->set_defaults($defaults);
		 MpmasNetworkObject->set_default_value(	"eqshare", 	$setup->{EQSHARE} );
	     MpmasNetworkObject->set_default_value(	"irate", 	$setup->{INTEREST_LGCRD} );

	      
	      my @list_of_classes_used = keys %{$instances};

	      #### Loop over all classes and create MPMAS model elements for instances
	      print "\e[K \rPreparing instances\r";
	      CLASSLOOP: for my $curClassId (@list_of_classes_used){
		      my $curClass = $classregistry->get_object_by_id($curClassId);
		      unless ($curClass ) {
			      print STDERR "ERROR!!!: Class $curClassId was not defined in class file\n" and exit(1);
		      }
		      #Loop over all instances of one class
		      for my $curInstanceId ( @{$instances->{$curClassId}}){
			      next unless (defined $curInstanceId);
			      #Create the new model elements which are related to the current instance...
			      my ($NewConstraints, $NewActivities, $NewNetworkObjects, $NewPerennialFileEntries, $NewLivestockFileEntries,$NewTPPenaltyEntries, $NewSOS) = $curClass->create_model_elements_for_instance($curInstanceId, $setup, \@milplist, $configs->{MULTIPERIOD}, $verbosityflag{VERBOSE_CLASS});

			      #...and then add them to the respective registries
			      $ConstraintRegistry->add_objects(@$NewConstraints);
			      $ActivityRegistry->add_objects(@$NewActivities);
			      $NetworkObjectRegistry->add_objects(@$NewNetworkObjects);
			      if (scalar(@$NewPerennialFileEntries) > 0 and not $configs->{PERMCROP_MODEL}) {
						print STDERR "Error: Found model element 'perennial', but perennial model has not been activated\nPlease include PERMCROP_MODEL = 1 into the model configuration\n";
						exit(1);
					}
			      $PerennialObjectRegistry->add_objects(@$NewPerennialFileEntries);
			      $LivestockObjectRegistry->add_objects(@$NewLivestockFileEntries);
			      $TPPenaltyObjectRegistry->add_objects(@$NewTPPenaltyEntries);
			      $SOSRegistry->add_objects(@$NewSOS);
		      } #END INSTANCE LOOP
	      }#END CLASSLOOP
	      
	      
	      MpmasInputfile->set_global_references($mname, $outdir, $outdir_cm, $inihash, $configs, $setup, $ActivityRegistry, $ConstraintRegistry, $NetworkObjectRegistry,$PerennialObjectRegistry,$LivestockObjectRegistry,$TPPenaltyObjectRegistry,$SOSRegistry,$dbh, $backwards, $tableau_compact, undef, undef, undef, undef);	#Note: Other globals are set by the prepare_shared methods of InputFiles, especially by MpmasMILPfile
	      #### Prepare input files
	      $| = 1; #autoflush print
	      ## for standalone matrix use
	      
	      if ($singlemode) { 			
		      my $milp ;
		      if( $configs->{MULTIPERIOD} ) {
				   $milp = MpmasMILPMultiperiod->new(10,1,"","");
			  }
			  else {
				  $milp = MpmasMILP->new(10,1,"","");
		      }
		      $milp->prepare_shared($singlemode);
		      
		      ## create rhshash if in #transform_assets mode
		      if (defined $baserhsdef and not ref $baserhsdef and $baserhsdef == -99) {
			    my $soiltab;
			    if (defined $inihash->{RHS_SOILTAB}) {
				  $inihash->{RHS_SOILTAB}->set_keys(2);
				  $soiltab = $inihash->{RHS_SOILTAB}->transform($dbh);
			    }
			    elsif (defined $inihash->{RHS_MAPDIR}) {
				  my $mapdir = $inihash->{RHS_MAPDIR};	
				  $mapdir = $mapdir ."/" unless ($mapdir =~/\/$/);
				  use EnhancedGrid;
				  my $soilmap = EnhancedGrid->create_from_ascii($mapdir."CatchMap00Soil.txt");
				  my $propmap = EnhancedGrid->create_from_ascii($mapdir."CatchMap00Prop.txt");
				  $soiltab = IndexedTable->create_from_hash($soilmap->tabulate_values_by_grid($propmap), 2, "soiltab");
			    }
		#	    my $agentinfo = $milp->config_entry("AGENT_INFO")->transform ($dbh,1);
	#		    my $householdtab = $milp->config_entry("HOUSEHOLD_COMPOSITION")->transform ($dbh,2);
#			    my $assettab = $milp->config_entry("ASSET_ENDOWMENTS")->transform ($dbh,2);
#			    my $sexagedef = $milp->config_entry("HOUSEHOLD_MEMBER_TYPES")->transform ($dbh,1);

			    
			    my $nwset = MpmasNetworkSet->new();
			    $nwset->define_specobjs();
			    $nwset->prepare_shared_files;
			    my $dmset = MpmasDemographySet->new();
			    $dmset->prepare_shared_files;
			    my $demotab = $milp->config_entry("CONVERTED_DEMOTAB");
			    
				my $cluset = MpmasClusterSet->new();
				$cluset->prepare_shared_files();
				
				my $agentinfo = $cluset->agentinfo;
			    my $householdtab = $cluset->householdhash;
			    my $assettab = $cluset->assethash;
			    my $sexagedef = $cluset->sexagedef;
				
			    
			    my %specobjindex = %{$milp->get_specobj_indices()};
			    my ($lilprow, $liq_constraint) = $milp->find_special_constraint("MASliq" ,1, ,1);
			    my ($numsoilrows, $firstsoilrow) = $milp->find_special_constraints("MASsoil");
			    my $rhshash;
			    foreach my $ag (@{$rhslist}) {
				    ##transform assets to rhs
				    my $liqassigned = 0;
				    foreach my $as ($assettab->query([$ag],0)) {
					  if ($as eq "liquidity") {
					      #special treatment for liquidity
					      $rhshash->{$ag}->{$liq_constraint} = $assettab->query([$ag, $as],3);
					      $liqassigned = 1;
					  }
					  elsif (defined $specobjindex{$as}) {
					      #skip other special objects
					      next;
					  }
					  else {
						my $asset_object = $milp->networkobject($as);
						print STDERR "Error in RHS for agent $ag. Asset $as is not part of the current model.\n" and exit(1) unless (defined $asset_object);
						my $constraint = $milp->constraint_at_row( $asset_object->con );
						my $rhs_multiplier = $asset_object->attr("multiplier");
						$rhshash->{$ag}->{$constraint->id} = $rhs_multiplier * $assettab->query([$ag, $as],3);
						#print "$ag\t$as\t".join("\t", $rhs_multiplier * $assettab->query([$ag, $as],3), $assettab->query([$ag, $as],3), $rhs_multiplier)."\n" if ($ag == 4320);
					  }
				    }
				    #assign global liquidity value if no special liquidity as been assigned
				    $rhshash->{$ag}->{$liq_constraint} = $milp->config_entry("LIQUIDITY") unless ($liqassigned);
				    ##transform houshold members to labor rhs
				    my $pop = $agentinfo->query([$ag], 2);
				    
				    
				    foreach my $hm ($householdtab->query([$ag],0)) {
					    my $career = $sexagedef->query([$hm],2);
					    my $age = ($sexagedef->query([$hm],4) +  $sexagedef->query([$hm],5)) / 2 ;
					    my $agecat;
					    for my $agc (sort { $b <=> $a }  $demotab->query([$pop, $career],0 )) {
						    if ($age <= $agc) {
							  $agecat = $agc;
							  last;
						    }
					    }
					    print STDERR "Error: Household member age ($age) for agent $ag out of bounds of demography information for population $pop and career $career!\n" and exit(1) unless (defined $agecat);
					    my $labcat = $demotab->query([$pop, $career, $agecat],4);
					    my $labprov = $demotab->query([$pop, $career, $agecat],5);
					    my $constraint = $milp->constraint_at_row($milp->config_entry("FIRSTLAB") + $milp->config_entry("LABGRPS")->{$labcat}->{lid}); 
					    $rhshash->{$ag}->{$constraint->id} = $householdtab->query([$ag, $hm],3) * $labprov;
					    
				    }
				    ##transform soils to soil rhs
				    foreach my $soil ($soiltab->query([$ag],0)) {
					    print STDERR "Error, when creating RHS: Soil index $soil out of bounds for agent $ag!\n" and exit(1) if ($soil >= $numsoilrows or $soil < 0 );
					    my $constraint = $milp->constraint_at_row($firstsoilrow + $soil); 
					    $rhshash->{$ag}->{$constraint->id} = $soiltab->query([$ag, $soil],3);
				    }
			    }
			    $rhsdef = IndexedTable->create_from_hash($rhshash, 2, "RHSDEF");
		      }
		      #print Dumper($rhshash);
		      print STDERR "Warning: You requested a stand-alone matrix, but did not specify any agent-specifc right hand side values." unless (ref $rhsdef);
		      $collected_rhshash->{$curScen} = $rhsdef;
		      
		      ##preparation of matrix files
		      ## for GNU Octave
		      
		      if ($octave) {
			    my $groupcodes = $milp->write_octave($selgroup);
			    my $fn = $milp->outdir().$milp->mname()."_OGROUP.dat";
			    open($fh, ">", $fn ) or die "Can't open $fn: $!";
			    print $fh "Code\tGroup\n";
			    for my $g (sort {$groupcodes->{$a} <=> $groupcodes->{$b}  } keys %$groupcodes) {
				print $fh "$g\t$groupcodes->{$g}\n";
			    }
			    close($fh);
			    my $fnh = $milp->outdir().$milp->mname()."_ORHS.dat";
			    open($frh, ">", $fnh ) or die "Can't open $fnh: $!";
			    my $c = 1;
			    
			    for my $curRHS (@{$rhslist}) {
	      
				  for my $curC ($rhsdef->query([$curRHS],0)) {
					my $x = $milp->constraint($curC);
					print STDERR "Error: RHS value for $curC provided, but constraint $curC does not exist!\n" and exit(1) unless (defined $x);
					print $frh join("\t", $c, $x->lp_row + 1, $rhsdef->query([$curRHS,$curC],4))."\n";
				  }
				  $c++;
			    }
			    close($frh);
		      }
		      ## for mqlmatrix/MilpCheckExe
		      if ($singlefiles) {
			    $milp->prepare_special($singlemode);
			    for my $curRHS (@{$rhslist}) {
				    print "\nWriting matrix file for RHS $curRHS...\n";

				    $milp->write(0,$curRHS, $rhsdef, $singlemode);
				    $milp->write(1,$curRHS, $rhsdef, $singlemode) unless( $additionaloutput{NO_COMMENTED});
				    
			    }
			    
			    for my $curFile ( "MpmasActivityInfo", "MpmasConstraintInfo") {
				    my $f;
				    if (eval("$curFile"."->required") ){	
					    eval("\$f = $curFile"."->new(1)")  or die "Error $@ trying to create  $curFile\n";
					    $f->prepare_shared(); 
					    $f->prepare_special();
					    $f->write(0); 
					    $f->write(1)  unless( $additionaloutput{NO_COMMENTED});
				    }
				    die $@ if $@;

			    }
		      }
		      $milp->blank_file;
	      } 
	      ### for full model use
	      else { 
		      my @filelist = ("MpmasMILPSet", "MpmasNetworkSet", "MpmasSegmentSet", "MpmasDemographySet","MpmasTimehandler","MpmasRegion", "MpmasMarketSet", "MpmasClusterSet","MpmasPerennial", "MpmasLivestock", "MpmasProducerOrganizationSet", "MpmasProducerOrganizationMILPSet", "MpmasEDICIrrigation","MpmasCropWat","MpmasEDICRiverFlows", "MpmasEDICRiverFlowTimeSeries", "MpmasEDICSurface", "MpmasEDICSubsurface","MpmasEDICCanalModel","MpmasActualWaterRights","MpmasCropWatPrecipitation",  "MpmasCropWatEvapotranspiration", "MpmasRentalContracts", "MpmasBData", "MpmasCropExt", "MpmasCropExtLinkXN", "MpmasCropDbgActivityID", "MpmasCropDbgYield","MpmasCropYieldsSet", "MpmasCropSimilarity","MpmasSoilToNRU", "MpmasLandMarket", "MpmasInvestmentsAttachedToLand", "MpmasInfrastructureProjects","MpmasEUCAP2003", "MpmasSpatialExchangeInfo", "MpmasActivityInfo", "MpmasConstraintInfo", "MpmasNetworkObjectInfo","MpmasStataLabelsU","MpmasStataLabelsK","MpmasStataLabelsP", "MpmasStataGroups", "MpmasExternalScriptCalls" );
		      push @filelist, "MpmasQLConstraintAnalysis" if $additionaloutput{ADD_CONANALYSIS};
		      push @filelist, ("MpmasConstraintInfoEdicMatlab", "MpmasActivityInfoEdicMatlab") if $additionaloutput{ADD_EDICMATLAB};

		      my $fileregistry = SimpleObjectRegistry->create();
			  MpmasInputfile->set_fileregistry($fileregistry);
			  	
		      ##Create input files: Shared Preparation loop -> this is run in any case, even if the user wants to skip the file, because it prepares data that might be needed by other files as well
		      my $c = 0;
		      for my $curFile (@filelist){
			      my $f;
			      $c += 10;
			      if (eval("$curFile"."->required") ){	
				      eval("\$f = $curFile"."->new($c)")  or die "Error $@ trying to create  $curFile\n";
				      $fileregistry->add_objects($f);
				      my $msg = "\e[K \rPreparing shared information from ".$f->filename."...\r" ;
				      $msg = "\e[K \rPreparing shared information from ".$f->filename."...\n"  if $verbosityflag{VERBOSE_FILES};
				      print $msg;
				      $f->prepare_shared(); 
				      
			      }
			      die $@ if $@;
		      }
		      print "\e[K \rFinished preparing shared information\n";

		      ##Create input files: Special Preparation and Writing loop
		      for my $curFile (sort {$a->priority <=> $b->priority}  $fileregistry->object_list()) {		

			      my $simplefilename = $curFile;
			      $simplefilename =~ s/^Mpmas//;
			      $simplefilename =~ s/file$//;
			      $simplefilename =~ s/Set$//;
			      if ($curFile->required and not exists $skiplist{$simplefilename} ) {
				      my $msg = "\e[K \rCreating ".$curFile->filename."...\r" ;
				      $msg = "\e[K \rCreating ".$curFile->filename."...\n"  if $verbosityflag{VERBOSE_FILES};
				      print $msg;
				      $curFile->prepare_special(); #layout of file is prepared
				      $curFile->write(0) ; #MP-MAS input version of file is written
				      $curFile->write(1) unless( $additionaloutput{NO_COMMENTED}); # commented version is written
			      }
		      }
		      ##Set scenarios specific MP-MAS command line options
		      if ($configs->{EDICEXTENDEDCANAL}) {
				$scenflags->{$curScen}->{C} = "-C".$inihash->{RUNCNLDIR}.$mname."_CanalModel.dat";
		      }
		      if (exists $configs->{EDICSURPLUS}) {
				$scenflags->{$curScen}->{H} = "-H".$configs->{EDICSURPLUS};
		      }
		      if (exists $configs->{MAPS}) {
				$scenflags->{$curScen}->{P} = "-P".$configs->{MAPS};
		      }
		      if (exists $configs->{TERRAINMAPSTUB}) {
				$scenflags->{$curScen}->{t} = "-t".$configs->{TERRAINMAPSTUB};
		      }
			  if ($inihash->{SKIP_AGENTS}  || $inihash->{USE_ONLY_AGENTS}) {
				$scenflags->{$curScen}->{b} = "-b";
			  }
		      ##extend list of all groupings
		      @ConstraintGroupingsForAllScenarios = keys %{ MpmasInputfile->config_entry("Groupings")->{constraint} };
		      @ActivityGroupingsForAllScenarios = keys %{ MpmasInputfile->config_entry("Groupings")->{activity} };
		      $maxperiods = MpmasInputfile->config_entry("NYEARS")  if ( MpmasInputfile->config_entry("NYEARS") > $maxperiods);
	      }

	      print "\e[K \r<< Finished writing of input files for scenario $curScen\n";
      }
      else { #runonly
	      if ($configs->{EDICEXTENDEDCANAL}) {
			$scenflags->{$curScen}->{C} = "-C".$inihash->{RUNCNLDIR}.$mname."_CanalModel.dat";

	      }
	      if (exists $configs->{EDICSURPLUS}) {
			$scenflags->{$curScen}->{H} = "-H".$configs->{EDICSURPLUS};

	      }
	      if (exists $configs->{MAPS}) {
			$scenflags->{$curScen}->{P} = "-P".$configs->{MAPS};
	      }
	      if ($inihash->{SKIP_AGENTS}  || $inihash->{USE_ONLY_AGENTS}) {
			  $scenflags->{$curScen}->{b} = "-b";
		  }
		  if (exists $configs->{TERRAINMAPSTUB}) {
			$scenflags->{$curScen}->{t} = "-t".$configs->{TERRAINMAPSTUB};
		  }
		  
		  MpmasInputfile->set_configs($configs);
		  my $th =  MpmasTimehandler->new(10);
		  $th->prepare_shared();#to set NYEARS
		  $maxperiods = MpmasInputfile->config_entry("NYEARS")  if ( MpmasInputfile->config_entry("NYEARS") > $maxperiods);

		
      }
      if ($saveCONSTYPEforRimport == -1  and $saveSHDW_SWITCHforRimport == -1) {
		  $saveCONSTYPEforRimport = MpmasInputfile->config_entry("CONSTYPE");
		  $saveSHDW_SWITCHforRimport = MpmasInputfile->config_entry("SHDW_SWITCH");
	  }
	  elsif ( $saveCONSTYPEforRimport != MpmasInputfile->config_entry("CONSTYPE")) {
		  print STDERR "Warning: Using different values for CONSTYPE in different scenarios in the same ..._importR file will not work.
				        Consider splitting up scenarios with and without consumption model into different run files.\n";
		  
	  }
	  elsif ( $saveSHDW_SWITCHforRimport != MpmasInputfile->config_entry("SHDW_SWITCH")) {
		  print STDERR "Warning: Using different values for SHDW_SWITCH in different scenarios in the same ..._importR file will not work.
				        Consider splitting up scenarios with and without consumption model into different run files.\n";
		  
	  }
      MpmasInputfile->set_global_references(undef,undef, undef, undef, undef, undef, undef, undef, undef,undef,undef,undef,undef,undef, undef, undef,undef,undef, undef, undef);	
}#END SCENARIO LOOP


RUNFILES:	{
	print "\nWriting run scripts...";
	$inihash->{RUNSCRIPTDIR} = "./" unless (exists $inihash->{RUNSCRIPTDIR});
	my $runfilename =  $inihash->{RUNSCRIPTDIR}.$inihash->{PREFIX}."run";
	open(my $runout, ">", $runfilename) or print STDERR "$!" and exit(1);
	

	my $sfmout;
	my $scenout;
	my $rhsout;
	if ($singlemode) {
		open($sfmout, ">",  $outdir.$inihash->{PREFIX}."sam_list.txt") or die "$!";
		open($scenout, ">",  $outdir.$inihash->{PREFIX}."sceninfo.txt") or die "$!";
		open($rhsout, ">",  $outdir.$inihash->{PREFIX}."rhsinfo.txt") or die "$!";
		print $sfmout "scenario\tfarm\n";
		print $scenout "scenario\tvariable\tvalue\n";
		print $rhsout "scenario\tagent\tvariable\tvalue\n";
	}

	print $runout "#!/bin/sh\n";
	print $runout "# Info: This script was created with  mpmasql $thisversion ($lastchange ) on ", scalar(localtime), "\n";
	print "ulimit -c unlimited\n" if $inihash->{DUMP_CORE};
	my $maspipeout;
	if (exists $inihash->{MASPIPESERVER}) {
	      my $runscriptdir = "./";
	      $runscriptdir = $inihash->{RUNSCRIPTDIR} if (exists $inihash->{RUNSCRIPTDIR});
	      open($maspipeout, ">",  $runscriptdir.$inihash->{PREFIX}."runmaspipe") or print STDERR "$!" and exit(1);
	      print $maspipeout "#!/bin/sh\n";
	}
	for my $curScen (@scenlist){
		my $mname = "$inihash->{PREFIX}"."$curScen";
		unless ($singlemode) { 
			my $exec = $inihash->{EXEC} || "mpmas";
			if ($inihash->{ADD_TIME_CMD}) {
				if (not defined $inihash->{TIMING_FORMAT}) {
					$inihash->{TIMING_FORMAT} = "%C: %x, real %E, user %U, system %S, Percent cpu %P, Total memory %K, Resident memory %M, Nswapped %W\\n"
				}
				print $runout "\\time -f \"" . $inihash->{TIMING_FORMAT} . "\" -o ";  
				print $runout $inihash->{RUNOUTDIR} ."/" if (defined $inihash->{RUNOUTDIR});
				print $runout "out/".$mname."_runStats.log ";
			}
			print $runout $exec."  -N".$mname."_ ";
			print $runout " -I".$inihash->{RUNINDIR} if (defined $inihash->{RUNINDIR});
			print $runout " -O".$inihash->{RUNOUTDIR} if (defined $inihash->{RUNOUTDIR});
			print $runout " -X".$inihash->{RUNTDTDIR} if (defined $inihash->{RUNTDTDIR});
			print $runout " ".$inihash->{FLAGS} if (defined $inihash->{FLAGS});
			if (exists $scenflags->{$curScen}) {
				  for my $i (keys %{ $scenflags->{$curScen} }) {
					print $runout " ".$scenflags->{$curScen}->{$i} ." ";
				  }
			}
			print $runout "\n";
			if (exists $inihash->{MASPIPESERVER}) {
					  my $user = " ";
					  $user = "-u".$inihash->{MASPIPEUSER} if (exists $inihash->{MASPIPEUSER});
					  print $maspipeout "maspipe ".$user. " run ".$inihash->{MASPIPESERVER}." ".$inihash->{MASPIPEEXEC}. " ". $inihash->{MASPIPEVERSION}. " ". $mname."_ ". $inihash->{FLAGS};
					  if (exists $scenflags->{$curScen}) {
						for my $i (keys %{ $scenflags->{$curScen} }) {
							  print $maspipeout " ".$scenflags->{$curScen}->{$i} ." ";
						}
					  }
					  print $maspipeout "\n";
			}
		}
		elsif ($singlemode) {
			for my $i ( ref $scendef ? $scendef->query([$curScen],0) : "base"   ){
				print $scenout "$mname\t$i\t".$scendef->query([$curScen,$i],3)."\n" if (ref $scendef);
			}
				for my $curRHS(@{$rhslist}) {
					print $sfmout "$mname\t$curRHS\n";
					if (ref  $collected_rhshash->{$curScen} ) {
					    for my $j ( $collected_rhshash->{$curScen}->query([$curRHS],0)){
						    print $rhsout "$mname\t$curRHS\t$j\t".$collected_rhshash->{$curScen}->query([$curRHS,$j],3)."\n";
					    }
					}
				}
			

		}		
	
	}
	if ($singlemode) {
			my $exec = $inihash->{EXEC} || "mpmasqlBatchSolve";
			if ($inihash->{ADD_TIME_CMD}) {
				if (not defined $inihash->{TIMING_FORMAT}) {
					$inihash->{TIMING_FORMAT} = "%C: %x, real %E, user %U, system %S, Percent cpu %P, Total memory %K, Resident memory %M, Nswapped %W\\n"
				}
				print $runout "\\time -f \"" . $inihash->{TIMING_FORMAT} . "\" -o ";  
				print $runout $inihash->{RUNOUTDIR} ."/" if (defined $inihash->{RUNOUTDIR});
				print $runout "out/".$inihash->{PREFIX}."_runStats.log ";
			}
			print $runout $exec." ".$inihash->{PREFIX}." ";
			if (defined $inihash->{FLAGS}) 
			{
				my $flags = $inihash->{FLAGS};
				$flags =~ s/\\"/"/g;
				print $runout " $flags";
			}
			print $runout "\n";
	}

	close $scenout if $singlemode;
	close $rhsout if $singlemode;
	close $sfmout if $singlemode;
	close $runout;
	chmod 0755, $runfilename;

	close $maspipeout if (exists $inihash->{MASPIPESERVER}) ;

	unless ($singlemode) {
		#Write importing scripts for statistical programs
		MpmasStataImport->create();
		MpmasStataImport->write($inihash, \@scenlist, $maxperiods, \@ConstraintGroupingsForAllScenarios, \@ActivityGroupingsForAllScenarios);
		MpmasRImport->create();
		MpmasRImport->write($inihash, \@scenlist, $maxperiods, $saveCONSTYPEforRimport, $saveSHDW_SWITCHforRimport);
	}
} #END RUNFILES


print "\n";
if (defined $dbh) {
      $dbh->disconnect;
      print "Disconnected from database $inihash->{DB}.\n";
}
my $totaltime = (time - $starttime);
my $minutes = int ($totaltime / 60);
my $seconds = $totaltime % 60;
	#if ($logMem) {
		#print "heap size/ perl size: ", heap_size(),", ", perl_size(),"\n";
	#}
print "Needed $minutes:".sprintf("%02d",$seconds)." minutes.\n\n";


