///////////////////////////////////////////////////////////
// File:			rr_preprocessing.cpp
//
// Author:			Stefan Liersch
// Contact:			stefan.liersch@googlemail.com,
//					stefan.liersch@ufz.de
//
// Date:			2007-08-04
// last modified:	2007-08-23
//
//---------------------------------------------------------
// DESCRIPTION
//---------------------------------------------------------
// Preprocessing time series data for rainfall-runoff models
//
//---------------------------------------------------------
// RESTRICIONS
//---------------------------------------------------------
//	
//
//---------------------------------------------------------
// ToDo
//---------------------------------------------------------
//	- make class conform for preprocessing of IHACRES and GR4J
//
///////////////////////////////////////////////////////////

//---------------------------------------------------------
#include "hydrology/rr_preprocessing.h"
#include "pgsql/pg_date_dlg.h"
#include "pgsql/textviewer.h"

#include <fstream>						// for test output only, can be deleted later on
#include <iostream>

#include "libpq-fe.h"

#include "pgsql/pg_menu_db.h"
#include "saga_api/convert_sl.h"
// SAGA Header
#include "saga_gui/saga_frame.h"		// location of class Cpgdatabase
										// using global *g_pSAGA_Frame pointer
#include "saga_gui/wksp_data_manager.h" // adding items to SAGA Workspace *g_pData
#include "saga_gui/res_dialogs.h"		// using DLG_Parameters
#include "saga_gui/res_commands.h"		// Definitions of menu constants (event handler)

#include "saga_gui/helper.h"			// using function MSG_General_Add()
#define MSG_ADD(s)	MSG_General_Add(s, true, true);
//---------------------------------------------------------

///////////////////////////////////////////////////////////////////////
//
//	CONSTRUCTORS
//
///////////////////////////////////////////////////////////////////////

CRR_preprocessing::CRR_preprocessing()
{
}
//---------------------------------------------------------

CRR_preprocessing::CRR_preprocessing(CTimeSeries *pTS, int nTS,
									 wxDateTime Period_Begin, wxDateTime Period_End)
{
	_ZeroAll(); // Set all pointers to NULL

	//-------------------------------------------
	// Set begin and end of simulation period.
	// The dates can be modified later on!
	//-------------------------------------------
	m_begin_period.Set(Period_Begin.GetJDN());
	m_end_period.Set(Period_End.GetJDN());
	//-------------------------------------------

	//-------------------------------------------
	//	Set the number of days of (eventually modified)
	//	simulation period.
	//-------------------------------------------
	_Set_nDays_Period();	// calculate m_nvalues;
	if (m_nvalues > 0)
	{
		//-------------------------------------------
		// Set begin and end date for each time series
		//-------------------------------------------
		_Set_TimeSeriesPeriod(pTS, nTS);
		//-------------------------------------------

		_InitArrays(m_nvalues);

		_Calc_TimeSeries(nTS,pTS, "_dates19002078") ? m_bPCPall = true : m_bPCPall = false;

		if (m_bPCPall)
		{
			//-------------------------------------------
			// Create the final table
			//-------------------------------------------
			_Create_RRModelInput(m_date, m_pTimeSeries1,0,m_nvalues);
			//-------------------------------------------
		} // end if (m_bPCPall && m_bTMPall)
		else {
			if (!m_bPCPall) MSG_ADD("There are missing data in the time series");
			//-------------------------------------------
			// Check, where the missing data in TimeSeries1 are
			// Afterwards, the user must select a valid/unbroken sub-period
			// within the entire period.
			_Set_AvailablePeriod2(m_nvalues); // m_p_available, m_validPeriods
			//-------------------------------------------
				
			bool b_ok;
			wxString selected_period	= _Create_DLG_Period(m_validPeriods, b_ok);
			if (b_ok)
			{
				wxDateTime mod_begin		= _ISO2wxDate( _GetFirstDate(selected_period) );
				wxDateTime mod_end			= _ISO2wxDate( _GetLastDate(selected_period) );

				// give the user the possibility to modify the selected sub-period
				CDateDLG *date_dlg = new CDateDLG(g_pSAGA_Frame, wxID_ANY, wxT("Select a Period"));
				date_dlg->Set_Date1(mod_begin);
				date_dlg->Set_Date2(mod_end);
				if (date_dlg->ShowModal() == wxID_OK)
				{
					mod_begin.Set(date_dlg->Get_Date1().GetJDN());
					mod_end.Set(date_dlg->Get_Date2().GetJDN());
				}
				date_dlg->Destroy();

				int start	= _Get_nDays_TimeSeries(m_begin_period, mod_begin) - 1;
				int nvalues	= _Get_nDays_TimeSeries(mod_begin, mod_end);// + 1;
				
				_Create_RRModelInput(m_date, m_pTimeSeries1,start,nvalues);
				//-------------------------------------------
			} // end if (b_ok)		
		}

	} // end if (m_nvalues > 0)
	else {
		SG_UI_Dlg_Message("There are no data in the selected time serie(s)",
			"Error: Time Series");
	}
	

}
//---------------------------------------------------------

CRR_preprocessing::CRR_preprocessing(CTimeSeries *pDischarge, int nDischarge,
									 CTimeSeries *pPCP, int nPCP,
									 CTimeSeries *pTMP, int nTMP,
									 wxDateTime Period_Begin, wxDateTime Period_End)
{
	_ZeroAll(); // Set all pointers to NULL

	//-------------------------------------------
	// Set begin and end of simulation period.
	// The dates can be modified later on!
	//-------------------------------------------
	m_begin_period.Set(Period_Begin.GetJDN());
	m_end_period.Set(Period_End.GetJDN());
	//-------------------------------------------

	//-------------------------------------------
	// Set number of selected climate stations.
	// The number of discharge gauge is usually one.
	//-------------------------------------------
	m_nDischargeStations	= nDischarge;
	m_nPCPStations			= nPCP;
	m_nTMPStations			= nTMP;
	//-------------------------------------------

	//-------------------------------------------
	// Get and set begin and end of discharge time series
	//-------------------------------------------
	pDischarge[0].Set_Date_Begin( _Get_Period_Begin( pDischarge[0].Get_Name().c_str(),pDischarge[0].Get_date_col().c_str() ).GetJDN() );
	pDischarge[0].Set_Date_End  ( _Get_Period_End  ( pDischarge[0].Get_Name().c_str(),pDischarge[0].Get_date_col().c_str() ).GetJDN() );

	// Check, if begin and end dates are matching
	// begin and end of discharge time series.
	// And if there are missing data in the time series.
	// In both cases the program will stop here.
	if ( !_Check_DischargePeriod(pDischarge) )
	{
		SG_UI_Dlg_Message("There are missing values in the discharge time series\n"
						  "Please make sure you select an unbroken period.",
						  "Missing Data in Time Series");
	} else {		
	//-------------------------------------------

		//-------------------------------------------
		// Set begin and end date for each time series
		//-------------------------------------------
		_Set_TimeSeriesPeriod(pPCP, m_nPCPStations);
		_Set_TimeSeriesPeriod(pTMP, m_nTMPStations);
		//-------------------------------------------

		//-------------------------------------------
		//	Set the number of days of (eventually modified)
		//	simulation period.
		//-------------------------------------------
		_Set_nDays_Period();	// calculate m_nvalues;
		if (m_nvalues > 0)
		{
			_InitArrays(m_nvalues);
			//-------------------------------------------

			//-------------------------------------------
			//	Calculate a time series of arithmetic mean values
			//	using all selected precipiation and temperature / ETpot stations.
			//	After calling this function the combined mean time series
			//	are available as 'm_pTimeSeries1' and m_pTimeSeries2
			//	which can be accessed by public functions:
			//	Get_TimeSeries1(int i) and
			//	Get_TimeSeries2(int i)
			//-------------------------------------------
			// Precipitation, Temperature / ETpot
			_Calc_TimeSeries(m_nPCPStations,pDischarge,pPCP,0) ? m_bPCPall = true : m_bPCPall = false;
			_Calc_TimeSeries(m_nTMPStations,pDischarge,pTMP,1) ? m_bTMPall = true : m_bTMPall = false;
			if (m_bPCPall && m_bTMPall)
			{
				//-------------------------------------------
				// Create the final rr-model input table
				//-------------------------------------------
				_Create_RRModelInput(pDischarge, m_date, m_pTimeSeries1, m_pTimeSeries2,0,m_nvalues);
				//-------------------------------------------
			} // end if (m_bPCPall && m_bTMPall)
			else {
				if (!m_bPCPall) MSG_ADD("There are missing data in time series 1");
				if (!m_bTMPall) MSG_ADD("There are missing data in time series 2");

				//-------------------------------------------
				// Check, where the missing data in TimeSeries1
				// and/or TimeSeries2 are.
				// Afterwards, the user must select a valid/unbroken sub-period
				// within the entire period.
				_Set_AvailablePeriod(m_nvalues); // m_p_available, m_validPeriods
				//-------------------------------------------

				bool b_ok;
				wxString selected_period	= _Create_DLG_Period(m_validPeriods, b_ok);
				if (b_ok)
				{
					wxDateTime mod_begin		= _ISO2wxDate( _GetFirstDate(selected_period) );
					wxDateTime mod_end			= _ISO2wxDate( _GetLastDate(selected_period) );

					// give the user the possibility to modify the selected sub-period
					CDateDLG *date_dlg = new CDateDLG(g_pSAGA_Frame, wxID_ANY, wxT("Select a Period"));
					date_dlg->Set_Date1(mod_begin);
					date_dlg->Set_Date2(mod_end);
					if (date_dlg->ShowModal() == wxID_OK)
					{
						mod_begin.Set(date_dlg->Get_Date1().GetJDN());
						mod_end.Set(date_dlg->Get_Date2().GetJDN());
					}
					date_dlg->Destroy();

					//-------------------------------------------
					// Here we assume that all data are available,
					// otherwise m_bPCPall or m_bTMPall would have been false.
					// Thus, it is not necessary to call _Set_AvailablePeriod()
					//-------------------------------------------
					if ( _Get_FinalDischargeData(pDischarge,mod_begin,mod_end) )
					{
						//-------------------------------------------
						// Create the final rr-model input table
						//-------------------------------------------
						int start	= _Get_nDays_TimeSeries(m_begin_period, mod_begin) - 1;
						int nvalues	= _Get_nDays_TimeSeries(mod_begin, mod_end);// + 1;
						_Create_RRModelInput(pDischarge, m_date, m_pTimeSeries1, m_pTimeSeries2,start,nvalues);
					}
					//-------------------------------------------
				} // end if (b_ok)
			}
		} // end if (m_nvalues >0)
		else {
			SG_UI_Dlg_Message("End of period is earlier than Begin","Error: Simulation Period not valid");
		}
	} // end if ( !_Check_DischargePeriod(pDischarge) )
}
//---------------------------------------------------------

///////////////////////////////////////////////////////////////////////
//
//	DESTRUCTOR
//
///////////////////////////////////////////////////////////////////////

CRR_preprocessing::~CRR_preprocessing()
{
	delete[] m_pTimeSeries1;
	delete[] m_pTimeSeries2;

	delete[] m_p_availableTS1;
	delete[] m_p_availableTS2;
	delete[] m_p_available;
}
//---------------------------------------------------------


///////////////////////////////////////////////////////////////////////
//
//	FUNCTIONS FOR NEW APPROACH
//
///////////////////////////////////////////////////////////////////////
//---------------------------------------------------------
void CRR_preprocessing::_ZeroAll()
{
	m_pTimeSeries1		= NULL;
	m_pTimeSeries2		= NULL;
	m_p_availableTS1	= NULL;
	m_p_availableTS2	= NULL;
	m_p_available		= NULL;
	m_date.Clear();
	m_validPeriods.Clear();
}
//---------------------------------------------------------

wxDateTime CRR_preprocessing::_Get_Period_Begin(const char *tablename, const char *date_col)
{
	//wxDateTime begin = wxDateTime::UNow();
	wxDateTime begin;
	begin.Set(g_pSAGA_Frame->db->Init_wxDateTime().GetJDN());
	begin.SetYear(g_pSAGA_Frame->db->GetFirstYear(tablename,date_col));
	begin.SetDay(g_pSAGA_Frame->db->GetFirstDay(tablename,date_col));
	g_pSAGA_Frame->db->AssignMonthByInt(begin, g_pSAGA_Frame->db->GetFirstMonth(tablename,date_col)-1);

	return(begin);
}
//---------------------------------------------------------

wxDateTime CRR_preprocessing::_Get_Period_End(const char *tablename, const char *date_col)
{
	//wxDateTime end = wxDateTime::UNow();
	wxDateTime end;
	end.Set(g_pSAGA_Frame->db->Init_wxDateTime().GetJDN());
	end.SetYear(g_pSAGA_Frame->db->GetLastYear(tablename,date_col));
	end.SetDay(g_pSAGA_Frame->db->GetLastDay(tablename,date_col));
	g_pSAGA_Frame->db->AssignMonthByInt(end, g_pSAGA_Frame->db->GetLastMonth(tablename,date_col)-1);
		
	return(end);
}
//---------------------------------------------------------

///////////////////////////////////////////////////////////////////////
//
//	MODIFY BEGIN AND END OF SIMULATION PERIOD
//
///////////////////////////////////////////////////////////////////////
//---------------------------------------------------------

bool CRR_preprocessing::_Check_DischargePeriod(CTimeSeries *pDischarge)
{
	int nrows_expected = _Get_nDays_TimeSeries(pDischarge[0].Get_Date_Begin(), pDischarge[0].Get_Date_End());
	int	nrows_actually;
	wxString sql = "SELECT * FROM ";
	sql += pDischarge[0].Get_Name();
	
	PGresult *res =	g_pSAGA_Frame->db->ExecSQL_wx2(sql);
	if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		MSG_ADD(PQresultErrorMessage(res));
		nrows_actually = -9999;
		PQclear(res);
	} else {
		nrows_actually = PQntuples(res);
		PQclear(res);
	}

	if (nrows_actually == nrows_expected)
		return(true);
	else
		return(false);
}
//---------------------------------------------------------

void CRR_preprocessing::_Set_TimeSeriesPeriod(CTimeSeries *pTS, int nTS)
{
	//-------------------------------------------
	// Get and set begin and end of precipitation time series
	//-------------------------------------------
	for (int i = 0; i < nTS && SG_UI_Process_Set_Progress(i,nTS); i++)
	{
		pTS[i].Set_Date_Begin(_Get_Period_Begin(pTS[i].Get_Name().c_str(), pTS[i].Get_date_col().c_str()).GetJDN());
		pTS[i].Set_Date_End(_Get_Period_End(pTS[i].Get_Name().c_str(), pTS[i].Get_date_col().c_str()).GetJDN());
	}
	SG_UI_Process_Set_Okay();
	//-------------------------------------------
}
//---------------------------------------------------------

int CRR_preprocessing::_Get_nDays_TimeSeries(wxDateTime begin, wxDateTime end)
{
	wxTimeSpan ts = end - begin;
	return(ts.GetDays() + 1);
}
//---------------------------------------------------------

void CRR_preprocessing::_Set_nDays_Period()
{
	wxTimeSpan ts = m_end_period - m_begin_period;
	m_nvalues = ts.GetDays() + 1;
}
//---------------------------------------------------------

void CRR_preprocessing::_InitArrays(int nvals)
{
	m_pTimeSeries1		= new double[nvals];
	m_pTimeSeries2		= new double[nvals];
	m_p_availableTS1	= new bool[nvals];
	m_p_availableTS2	= new bool[nvals];
	m_p_available		= new bool[nvals];
}
//---------------------------------------------------------------------

///////////////////////////////////////////////////////////////////////
//
//	Get discharge and climate time series from database
//
///////////////////////////////////////////////////////////////////////
//---------------------------------------------------------------------

bool CRR_preprocessing::_Calc_TimeSeries(unsigned int nStations,
										 CTimeSeries *discharge,
										 CTimeSeries *station,
										 int ts12)	// ts12 = 0 --> m_pTimeSeries1
													// ts12 = 1 --> m_pTimeSeries2
{
	PGconn			*conn = g_pSAGA_Frame->db->GetConn();
	PGresult		*res;
	wxString		sql;

	// create sql query for every gauging station
	// get number of values in time series (equal for all stations,
	// not depending on available data)
	// create double pointer 'data'
	for (unsigned int i = 0; i < nStations && SG_UI_Process_Set_Progress(i,nStations); i++)
	{
		sql = _Get_SQL_LEFT_JOIN(discharge[0].Get_Name().c_str(), discharge[0].Get_date_col().c_str(), discharge[0].Get_value_col().c_str(),
							     station[i].Get_Name().c_str(), station[i].Get_date_col().c_str(), station[i].Get_value_col().c_str(),
								 m_begin_period, m_end_period);

		res = PQexec(conn,sql.c_str());
		// check whether SQL command was successfull or not
		if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
		{
			PQclear(res);
			SG_UI_Dlg_Message(PQerrorMessage(conn),"SQL Error");
			return(false);
		} else {
			// create data pointer for each time series
			if (ts12 == 0 && i == 0)
			{
				discharge[0].Set_nValues(PQntuples(res));
				discharge[0].Set_Create_m_pData(discharge[0].Get_nValues());
			}
			station[i].Set_nValues(discharge[0].Get_nValues());
			// array must be assigned with n_values of discharge time series
			station[i].Set_Create_m_pData(discharge[0].Get_nValues());

			for (int val = 0; val < station[i].Get_nValues(); val++)
			{
				// If no value is available for a certain day, 
				// function StringToDouble() returns the value -9999.
				// --> NoData value = -9999
				if (ts12 == 0 && i == 0)
				{
					m_date.Add(PQgetvalue(res,val,0));
					discharge[0].Set_Data(val,convert_sl::StringToDouble(PQgetvalue(res,val,1)));
				}
				station[i].Set_Data(val,convert_sl::StringToDouble(PQgetvalue(res,val,2)));
			}
		}
	} // end for (int i...
	SG_UI_Process_Set_Okay();

	// Calculate mean of precipitation (m_pTimeSeries1),
	// temperature or pot. evapotranspiration (m_pTimeSeries2)
	if (ts12 == 0)
	{
		return(_Calc_Mean(station, nStations, m_pTimeSeries1, ts12) ? true : false);
	}
	if (ts12 == 1)
	{
		return(_Calc_Mean(station, nStations, m_pTimeSeries2, ts12) ? true : false);
	}

	return(false);
}
//---------------------------------------------------------------------

bool CRR_preprocessing::_Calc_TimeSeries(unsigned int nTS, CTimeSeries *pTS, const char* date_table)
{
	PGconn			*conn = g_pSAGA_Frame->db->GetConn();
	PGresult		*res;
	wxString		sql;

	// create sql query for every gauging station
	// get number of values in time series (equal for all stations,
	// not depending on available data)
	// create double pointer 'data'
	for (unsigned int i = 0; i < nTS && SG_UI_Process_Set_Progress(i,nTS); i++)
	{
		sql = _Get_SQL_LEFT_JOIN2(date_table, "date",
							      pTS[i].Get_Name().c_str(), pTS[i].Get_date_col().c_str(), pTS[i].Get_value_col().c_str(),
								  m_begin_period, m_end_period);

		MSG_ADD(sql.c_str());

		res = PQexec(conn,sql.c_str());
		// check whether SQL command was successfull or not
		if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
		{
			PQclear(res);
			SG_UI_Dlg_Message(PQerrorMessage(conn),"SQL Error");
			return(false);
		} else {
			// create data pointer for each time series

			pTS[i].Set_nValues(PQntuples(res));
			// array must be assigned with n_values
			pTS[i].Set_Create_m_pData(pTS[i].Get_nValues());

			for (int val = 0; val < pTS[i].Get_nValues(); val++)
			{
				// If no value is available for a certain day, 
				// function StringToDouble() returns the value -9999.
				// --> NoData value = -9999
				if (i == 0) m_date.Add(PQgetvalue(res,val,0));
				pTS[i].Set_Data(val,convert_sl::StringToDouble(PQgetvalue(res,val,1)));
			}
		}
	} // end for (int i...
	SG_UI_Process_Set_Okay();

	// Calculate mean time series of selected time series (m_pTimeSeries1),
	return(_Calc_Mean(pTS, nTS, m_pTimeSeries1, 0) ? true : false);
}
//---------------------------------------------------------------------

bool CRR_preprocessing::_Calc_Mean(CTimeSeries *ts, int nTimeSeries, double *mean, int ts12)
{
	bool	bAllAvailable = true;
	double	sum;
	int		nvalid;
	for (int val = 0; val < m_nvalues && SG_UI_Process_Set_Progress(val, m_nvalues); val++)
	{
		nvalid = 0;
		sum = 0.0;
		for (int station = 0; station < nTimeSeries; station++)
		{
			if (ts[station].Get_data(val) != -9999)
			{
				//sum += ts[station].Get_data(val);
				//nvalid++;
				sum += ts[station].Get_data(val) * ts[station].Get_weight();
				nvalid += ts[station].Get_weight();
			}
		}
		if (nvalid > 0)
		{
			mean[val] = sum / nvalid;
			if (ts12 == 0) m_p_availableTS1[val] = true;
			if (ts12 == 1) m_p_availableTS2[val] = true;
		}
		else {
			mean[val] = -9999;
			if (ts12 == 0) m_p_availableTS1[val] = false;
			if (ts12 == 1) m_p_availableTS2[val] = false;
			bAllAvailable = false;
		}
	}
	SG_UI_Process_Set_Okay();
	return(bAllAvailable);
}
//---------------------------------------------------------------------

wxString CRR_preprocessing::_Get_SQL_LEFT_JOIN(const char* left_table, const char* left_col, const char* left_val,
										   const char* right_table, const char* right_col, const char* right_val,
										   wxDateTime begin, wxDateTime end)
{
	wxString SQL = "SELECT ";
	SQL += left_table;
	SQL += ".";
	SQL += left_col;
	SQL += ", ";
	SQL += left_table;
	SQL += ".";
	SQL += left_val;
	SQL += ", ";
	SQL += right_table;
	SQL += ".";
	SQL += right_val;
	SQL += " FROM ";
	SQL += left_table;
	SQL += " LEFT JOIN ";
	SQL += right_table;
	SQL += " ON ";
	SQL += left_table;
	SQL += ".";
	SQL += left_col;
	SQL += " = ";
	SQL += right_table;
	SQL += ".";
	SQL += right_col;
	SQL += " WHERE ";
	SQL += left_table;
	SQL += ".";
	SQL += left_col;
	SQL += " >= '";
	SQL += convert_sl::Int2String(begin.GetYear()).c_str();
	SQL += "-";
	SQL += convert_sl::Int2String(begin.GetMonth()+1).c_str();
	SQL += "-";
	SQL += convert_sl::Int2String(begin.GetDay()).c_str();
	SQL += "' AND ";
	SQL += left_table;
	SQL += ".";
	SQL += left_col;
	SQL += " <= '";
	SQL += convert_sl::Int2String(end.GetYear()).c_str();
	SQL += "-";
	SQL += convert_sl::Int2String(end.GetMonth()+1).c_str();
	SQL += "-";
	SQL += convert_sl::Int2String(end.GetDay()).c_str();
	SQL += "'";
	return(SQL);
}
//---------------------------------------------------------------------

wxString CRR_preprocessing::_Get_SQL_LEFT_JOIN2(const char* left_table, const char* left_col,
												const char* right_table, const char* right_col, const char* right_val,
												wxDateTime begin, wxDateTime end)
{
	wxString SQL = "SELECT ";
	SQL += left_table;
	SQL += ".";
	SQL += left_col;
	SQL += ", ";
	//SQL += left_table;
	//SQL += ".";
	//SQL += left_val;
	//SQL += ", ";
	SQL += right_table;
	SQL += ".";
	SQL += right_val;
	SQL += " FROM ";
	SQL += left_table;
	SQL += " LEFT JOIN ";
	SQL += right_table;
	SQL += " ON ";
	SQL += left_table;
	SQL += ".";
	SQL += left_col;
	SQL += " = ";
	SQL += right_table;
	SQL += ".";
	SQL += right_col;
	SQL += " WHERE ";
	SQL += left_table;
	SQL += ".";
	SQL += left_col;
	SQL += " >= '";
	SQL += convert_sl::Int2String(begin.GetYear()).c_str();
	SQL += "-";
	SQL += convert_sl::Int2String(begin.GetMonth()+1).c_str();
	SQL += "-";
	SQL += convert_sl::Int2String(begin.GetDay()).c_str();
	SQL += "' AND ";
	SQL += left_table;
	SQL += ".";
	SQL += left_col;
	SQL += " <= '";
	SQL += convert_sl::Int2String(end.GetYear()).c_str();
	SQL += "-";
	SQL += convert_sl::Int2String(end.GetMonth()+1).c_str();
	SQL += "-";
	SQL += convert_sl::Int2String(end.GetDay()).c_str();
	SQL += "'";
	return(SQL);
}
//---------------------------------------------------------------------

void CRR_preprocessing::_Set_AvailablePeriod(int nvalues)
{
	if (m_p_availableTS1[0] && m_p_availableTS2[0])
	{
			m_p_available[0] = true;
			m_validPeriods.Add(m_date[0]);
	}
	else {
			m_p_available[0] = false;
	}

	for (int i = 1; i < m_nvalues && SG_UI_Process_Set_Progress(i, m_nvalues); i++)
	{
		if (m_p_availableTS1[i] && m_p_availableTS2[i])
		{
			m_p_available[i] = true;
			if (!m_p_available[i-1]) m_validPeriods.Add(m_date[i]);
		}
		else {
			m_p_available[i] = false;
			if (m_p_available[i-1])	m_validPeriods.Add(m_date[i-1]);
		}
		if (i == nvalues -1)
			if (m_p_available[i-1])	m_validPeriods.Add(m_date[i]);
	}
	SG_UI_Process_Set_Okay();
}
//---------------------------------------------------------------------

void CRR_preprocessing::_Set_AvailablePeriod2(int nvalues)
{
	if (m_p_availableTS1[0])
	{
			m_p_available[0] = true;
			m_validPeriods.Add(m_date[0]);
	}
	else {
			m_p_available[0] = false;
	}

	for (int i = 1; i < m_nvalues && SG_UI_Process_Set_Progress(i, m_nvalues); i++)
	{
		if (m_p_availableTS1[i])
		{
			m_p_available[i] = true;
			if (!m_p_available[i-1]) m_validPeriods.Add(m_date[i]);
		}
		else {
			m_p_available[i] = false;
			if (m_p_available[i-1])	m_validPeriods.Add(m_date[i-1]);
		}
		if (i == nvalues -1)
			if (m_p_available[i-1])	m_validPeriods.Add(m_date[i]);
	}
	SG_UI_Process_Set_Okay();
}
//---------------------------------------------------------------------

bool CRR_preprocessing::_Get_FinalDischargeData(CTimeSeries *pDischarge, wxDateTime begin, wxDateTime end)
{
	// Modify begin and end in discharge time series
	pDischarge->Set_Date_Begin(begin);
	pDischarge->Set_Date_End(end);

	// create sql query
	wxString sql = "SELECT ";
	sql += pDischarge->Get_date_col();
	sql += ", ";
	sql += pDischarge->Get_value_col();
	sql += " FROM ";
	sql += pDischarge->Get_Name();
	sql += " WHERE ";
	sql += pDischarge->Get_date_col();
	sql += " >= '";
	sql += convert_sl::Int2String(begin.GetYear()).c_str();
	sql += "-";
	sql += convert_sl::Int2String(begin.GetMonth()+1).c_str();
	sql += "-";
	sql += convert_sl::Int2String(begin.GetDay()).c_str();
	sql += "' AND ";
	sql += pDischarge->Get_date_col();
	sql += " <= '";
	sql += convert_sl::Int2String(end.GetYear()).c_str();
	sql += "-";
	sql += convert_sl::Int2String(end.GetMonth()+1).c_str();
	sql += "-";
	sql += convert_sl::Int2String(end.GetDay()).c_str();
	sql += "'";

	PGresult *res = g_pSAGA_Frame->db->ExecSQL_wx2(sql);
	// check whether SQL command was successfull or not
	if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		MSG_ADD(PQerrorMessage(g_pSAGA_Frame->db->GetConn()));
		PQclear(res);
		return(false);
	} else {
		m_date.Clear();
		pDischarge[0].Set_Create_m_pData(PQntuples(res));

		for (int val = 0; val < PQntuples(res); val++)
		{
			m_date.Add(PQgetvalue(res,val,0));
			pDischarge[0].Set_Data(val,convert_sl::StringToDouble(PQgetvalue(res,val,1)));
		}
		PQclear(res);
		return(true);
	}
}
//---------------------------------------------------------------------

wxString CRR_preprocessing::_Create_DLG_Period(wxArrayString validPeriods, bool &b_ok)
{
	CSG_Parameters			P;
	wxString				periods;
	wxString				selected_period;

	for (unsigned int v = 0; v < validPeriods.size(); v++)
	{
		if ((v+1) % 2 != 0)
		{
			periods.Append(validPeriods[v]);
			periods.Append(" - ");
		} else {
			periods.Append(validPeriods[v]);
			periods.Append("|");
		}
	}
	
	P.Set_Name("Valid Simulation Periods");
	
	P.Add_Choice(
		NULL,	"PERIOD",	LNG("Select a Simulation Period"),
		"Select a valid simulation period",
		periods
	);
	
	if( DLG_Parameters(&P) )
	{
		selected_period = P("PERIOD")->asString();
		b_ok = true;
		return(selected_period);
	} else {
		b_ok = false;
		return("");
	}
}
//---------------------------------------------------------------------

wxString CRR_preprocessing::_GetFirstDate(wxString selected_period)
{
	return(selected_period.substr(0,10));
}
//---------------------------------------------------------------------

wxString CRR_preprocessing::_GetLastDate(wxString selected_period)
{
	return(selected_period(13,22));
}
//---------------------------------------------------------------------

wxDateTime CRR_preprocessing::_ISO2wxDate(wxString date)
{
	// ISO date format: YYYY-MM-DD
	// function converts a date in ISO format as wxString to wxDateTime
	std::string date_in = date;
	//wxDateTime	date_wx = wxDateTime::UNow();
	wxDateTime date_wx;
	date_wx.Set(g_pSAGA_Frame->db->Init_wxDateTime().GetJDN());

	date_wx.SetYear(convert_sl::StringToInt(date_in.substr(0,4)));
	date_wx.SetDay(convert_sl::StringToInt(date_in.substr(8,2)));
	g_pSAGA_Frame->db->AssignMonthByInt(date_wx, convert_sl::StringToInt(date_in.substr(5,2))-1);
		
	return(date_wx);
}

//---------------------------------------------------------------------

void CRR_preprocessing::_Create_RRModelInput(CTimeSeries *pDischarge, wxArrayString &date_col, double *TS1, double *TS2, int begin, int nvalues)
{
	CSG_Table_Record	*pRecord;
	CSG_Table			*pTable;

	pTable = SG_Create_Table();
	wxString name = pDischarge[0].Get_Name();
	name += "_rr_input";
	pTable->Set_Name(name.c_str());

	// creating the column titles
	pTable->Add_Field("DATE",				 TABLE_FIELDTYPE_String);
	pTable->Add_Field("DISCHARGE",			 TABLE_FIELDTYPE_Double);
	pTable->Add_Field("PCP",				 TABLE_FIELDTYPE_Double);
	pTable->Add_Field("TMP",				 TABLE_FIELDTYPE_Double);

	for (int i = 0; i < nvalues && SG_UI_Process_Set_Progress(i,nvalues); i++)
	{
		// add a new record to the table
		pTable->Add_Record();
		pRecord	= pTable->Get_Record(i);

		// writing the data to the current row
		pRecord->Set_Value(0,date_col[i]);
		pRecord->Set_Value(1,pDischarge[0].Get_data(i));
		pRecord->Set_Value(2,TS1[i+begin]);
		pRecord->Set_Value(3,TS2[i+begin]);
	}
	SG_UI_Process_Set_Okay();

	g_pData->Add(pTable);
}
//---------------------------------------------------------------------

void CRR_preprocessing::_Create_RRModelInput(wxArrayString &date_col, double *TS1, int begin, int nvalues)
{
	CSG_Table_Record	*pRecord;
	CSG_Table			*pTable;

	pTable = SG_Create_Table();
	wxString name = "mean_time_series";
	pTable->Set_Name(name.c_str());

	// creating the column titles
	pTable->Add_Field("DATE", TABLE_FIELDTYPE_String);
	pTable->Add_Field("MEAN", TABLE_FIELDTYPE_Double);

	for (int i = 0; i < nvalues && SG_UI_Process_Set_Progress(i,nvalues); i++)
	{
		// add a new record to the table
		pTable->Add_Record();
		pRecord	= pTable->Get_Record(i);

		// writing the data to the current row
		pRecord->Set_Value(0,date_col[i+begin]);
		pRecord->Set_Value(1,TS1[i+begin]);
	}
	SG_UI_Process_Set_Okay();

	g_pData->Add(pTable);
}
//---------------------------------------------------------------------

///////////////////////////////////////////////////////////////////////
//
//	PUBLIC GET FUNCTION
//
///////////////////////////////////////////////////////////////////////

double CRR_preprocessing::Get_TimeSeries1(int i)
{
	return( i < m_nvalues ? m_pTimeSeries1[i] : -9999 );
}
//---------------------------------------------------------------------
double CRR_preprocessing::Get_TimeSeries2(int i)
{
	return( i < m_nvalues ? m_pTimeSeries2[i] : -9999 );
}
//---------------------------------------------------------------------
