////////////////////////////////////////////////////////////////
// Creating the menubar 'Database'
//
// Author:
// Stefan Liersch: stefan.liersch@googlemail.com, stefan.liersch@ufz.de
// 
// Date:			2006-10-15
// last modified:	2007-07-23
//
// -------------------------------------------------------------
// ToDo
// -------------------------------------------------------------
//	-	Export Table:
//		- check wether table name is sql valid ! e.g. "-" is not a valid sign
//		- give the user the option to save the sql-commands in a script
// -------------------------------------------------------------
////////////////////////////////////////////////////////////////

#include "pgsql/pg_menu_db.h"
#include "pgsql/pg_dlg_average_gauges.h"
#include "hydrology/rr_dlg_preproc.h"
#include "hydrology/pg_timeseries.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);

#include <fstream>

#include "saga_gui/simplefeature_export_dlg.h"

// ------------------------------------------------------------------------
void Cmenu_db::_On_ConnectDB(void)
{
	try
	{
		// db object must be destroyed (but when and where)??????
		g_pSAGA_Frame->db = new Cpgdatabase();

		// make a connection to the database using PQconnectdb
		if (g_pSAGA_Frame->db->ConnectToDB((const char*) _GenerateConnectionString(connParms).c_str())) //connInfo.c_str()))
		{
			g_pSAGA_Frame->DB_CONNECTED = true;
			MSG_ADD(wxString::Format("%s%s%s","Database ", connParms.dbname.c_str(), " connected"));
			// enable / disable Menu items due to connection status
			_EnableDisableMenu(g_pSAGA_Frame->DB_CONNECTED, m_pDB_Menu);
		} else {
			g_pSAGA_Frame->DB_CONNECTED = false;
			MSG_ADD(wxString::Format("%s%s%s","Database ", connParms.dbname.c_str(), " not connected"));
			_EnableDisableMenu(g_pSAGA_Frame->DB_CONNECTED, m_pDB_Menu);
		}
	}
	catch(...)
	{
		MSG_ADD("Connecting Database not successfull!");
	}
}
// ------------------------------------------------------------------------

string Cmenu_db::_GenerateConnectionString(ConnectionParameters connParms)
{
	string connInfo = "host=";
	if ( connParms.hostaddr != "" ) {
		connInfo += connParms.hostaddr;
	} else {
		connInfo += connParms.host;
	}

	connInfo += " port=";
	connInfo += connParms.port;
	if (connParms.options.length() > 0)
	{
		connInfo += " options=";
		connInfo += connParms.options;
	}
	if (connParms.tty.length() > 0)
	{
		connInfo += " tty=";
		connInfo += connParms.tty;
	}
	connInfo += " dbname=";
	connInfo += connParms.dbname;
	connInfo += " user=";
	connInfo += connParms.user;
	if (connParms.password.length() > 0)
	{
		connInfo += " password=";
		connInfo += connParms.password;
	}
	return(connInfo);
}
// ------------------------------------------------------------------------

void Cmenu_db::_On_DeConnectDB()
{
	g_pSAGA_Frame->db->ExitDB();
	MSG_ADD(wxString::Format("%s%s%s","Database ", connParms.dbname.c_str(), " disconnected"));
	g_pSAGA_Frame->DB_CONNECTED = false;
	// enable / disable Menu items due to connection status
	_EnableDisableMenu(false, m_pDB_Menu);
}
// ------------------------------------------------------------------------

void Cmenu_db::_On_DB_ImportTable()
{
	CSG_Table		*pTable;
	//-----------------------------------------------------
	// DIALOG
	//-----------------------------------------------------
	string			tablename;
	CSG_Parameters	P;

	P.Set_Name(LNG("Import Table from Database"));

	P.Add_Choice(
		NULL	, "TABLE"	, LNG("Select Import Table"),
		"",
		g_pSAGA_Frame->db->GetDBTables().c_str()
	);

	//-----------------------------------------------------
	if( DLG_Parameters(&P) )
	{
		try
		{
			tablename	= P("TABLE")->asString();

			m_psqltable = new Csqltable(tablename,
										g_pSAGA_Frame->db->GetNumberCols(tablename.c_str()),
										g_pSAGA_Frame->db->GetNumberRows(tablename.c_str()));

			if (g_pSAGA_Frame->db->ReadTable(m_psqltable))
			{
				MSG_ADD("Read Table successfull");
				//-----------------------------------------------------
				// create SAGA Table from database table
				pTable = SG_Create_Table();
				CreateSAGATable(m_psqltable, pTable);
				
				// add table to SAGA Workspace
				pTable->Set_Name(tablename.c_str());
				g_pData->Add(pTable);

				delete m_psqltable;

			} else {
				MSG_ADD("Read Table not successfull");
			}

		}
		catch(...)
		{
			MSG_ADD("Read Table not successfull");
		}

	} // end if (DLG_Parameters(...))
}
// ------------------------------------------------------------------------

void Cmenu_db::_On_DB_ExportTable(bool bSQLScript)
{
	//if (bSQLScript)	std::ofstream f("_sql_script.sql");
	//std::ofstream f("_sql_script.sql");

	CSG_Table		*pTable;
	wxString		SQL = "CREATE TABLE ";

	m_pExportTableDLG = new Cdlg_table2pg(g_pSAGA_Frame, -1, "Export SAGA Table to Database");
	if (m_pExportTableDLG->ShowModal() == wxID_OK)
	{
		//-----------------------------------------------------------
		// CREATE TABLE...
		//-----------------------------------------------------------
		pTable = m_pExportTableDLG->Get_ExportTable();
		SQL += m_pExportTableDLG->Get_ExportTableName_wxString();
		SQL += " (";

		for (int i = 0; i < pTable->Get_Field_Count(); i++)
		{
			SQL += m_pExportTableDLG->Get_FieldName(i);
			SQL += " ";
			SQL += m_pExportTableDLG->Get_FieldType(i);
			if (m_pExportTableDLG->Get_Constraint(i) != "")
			{
				SQL += " ";
				SQL += m_pExportTableDLG->Get_Constraint(i);
			}
			if (i <  pTable->Get_Field_Count() -1) SQL += ", ";
			if (i == pTable->Get_Field_Count() -1) SQL += ")";
		}
		// Execute CREATE TABLE statement
		if (g_pSAGA_Frame->db->ExecSQL_wx(SQL))
		{
			MSG_ADD(wxString::Format("%s: %s","Command successful",SQL));
			//if (bSQLScript) f << SQL << std::endl;
		}
		else {
			MSG_ADD(wxString::Format("%s: %s","Create Table NOT successful",SQL));
		}
		//-----------------------------------------------------------

		//-----------------------------------------------------------
		// INSERT INTO...
		//-----------------------------------------------------------
		bool			bAllSuccessful = true;
		string			record;
		string			sql_insert = "";
		PGresult		*res;
		for (int row = 0; row < pTable->Get_Record_Count() && PROGRESSBAR_Set_Position(row,pTable->Get_Record_Count()); row++)
		{
			record = "";
			for (int col = 0; col < pTable->Get_Field_Count(); col++)
			{
				if (m_pExportTableDLG->IsNumeric_sql(col))
				{
					record += pTable->Get_Record(row)->asString(col);
					if (col + 1 < pTable->Get_Field_Count()) record += ",";
				} else {
					record += "'";
					record += pTable->Get_Record(row)->asString(col);
					record += "'";
					if (col + 1 < pTable->Get_Field_Count()) record += ",";
				}
			}
			if (bSQLScript)
			{
				// Execute INSERT INTO statement
				sql_insert = g_pSAGA_Frame->db->Get_SQL_InsertInto(m_pExportTableDLG->Get_ExportTableName_wxString().c_str(), record.c_str());
				{
					res =	g_pSAGA_Frame->db->ExecSQL_wx2(sql_insert.c_str());
					if (!res )// || PQresultStatus(res) != PGRES_TUPLES_OK)
					{
						//MSG_ADD( wxString::Format("%d: %s", row, PQresultErrorMessage(res)) );
						//f << sql_insert << std::endl;
						//f << PQresultErrorMessage(res) << std::endl;
						PQclear(res);
					} else {
						//f << sql_insert << std::endl;
						PQclear(res);
					}
				}
			} else {
				// Execute INSERT INTO statement
				if (!g_pSAGA_Frame->db->InsertIntoTable(m_pExportTableDLG->Get_ExportTableName_wxString().c_str(), record.c_str()))
				{
					if (row == 0)
					{
						MSG_ADD("INSERT INTO NOT successful");
						MSG_ADD(record.c_str());
						bAllSuccessful = false;
					}
				}
			}
		}
		PROCESS_Set_Okay();
		if (bAllSuccessful)
			MSG_ADD("Export successful");
	}
}
// ------------------------------------------------------------------------

void Cmenu_db::_On_DB_DeleteTable()
{
	string			tablename;
	CSG_Parameters	P;

	P.Set_Name(LNG("Delete Table from Database"));

	P.Add_Choice(
		NULL	, "TABLE"	, LNG("Select Import Table"),
		"",
		g_pSAGA_Frame->db->GetDBTables().c_str()
	);

	//-----------------------------------------------------
	if( DLG_Parameters(&P) )
	{
		tablename	= P("TABLE")->asString();

		if (g_pSAGA_Frame->db->DropTable(tablename.c_str()))
		{
			MSG_ADD("Table deleted");
		}
		else {
			MSG_ADD("Failed to delete table");
		}
	}
}

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

///////////////////////////////////////////////////////////////////////
//
//	IMPORT A SIMPLE FEATURE AS SAGA SHAPE FILE
//
///////////////////////////////////////////////////////////////////////

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

void Cmenu_db::_On_DB_ImportSimpleFeature(TSG_SF_CATEGORY category)
{
	CSG_Data_Object		*pObject;
	string				tablename;
	string				geom_name;

	//-----------------------------------------------------
	// DIALOG
	//-----------------------------------------------------
	CSG_Parameters		P;

	P.Set_Name(LNG("Import Simple Feature from PostGIS"));

	P.Add_Choice(
		NULL	, "SF_NAME"	, LNG("Select Simple Feature"),
		"",
		g_pSAGA_Frame->db->GetDBGeometryTables().c_str()
	);
	//-----------------------------------------------------
	
	if( DLG_Parameters(&P) )
	{
		
		tablename	= _GetFeatureNameFromSelection(P("SF_NAME")->asString());
		geom_name	= g_pSAGA_Frame->db->_Get_GeometryColumnName(tablename.c_str());

		pObject = new CSG_SimpleFeature(g_pSAGA_Frame->db->GetConn(),tablename.c_str(),geom_name);
		g_pData->Add(pObject);
		pObject->asSimpleFeature()->Set_Category(category);
	}
}
//---------------------------------------------------------------------
CSG_Data_Object* Cmenu_db::_On_DB_ImportSimpleFeature(CSG_String SF_Name)
{
	CSG_Data_Object		*pObject;
	
	return(pObject = new CSG_SimpleFeature(g_pSAGA_Frame->db->GetConn(),
		SF_Name.c_str(),g_pSAGA_Frame->db->_Get_GeometryColumnName(SF_Name.c_str())));//geom_name));
}
//---------------------------------------------------------------------

CSG_String Cmenu_db::_GetFeatureNameFromSelection(std::string name)
{
		return( _EraseBlanks(_Reduce_TableName(name)).c_str() );
}
//---------------------------------------------------------------------

//CSG_String Cmenu_db::_Get_geom_name(string tablename)
//{
//	unsigned int begin = _FindFirstCharPosition(tablename,'(');
//	unsigned int end = _FindFirstCharPosition(tablename,')');
//	return(tablename.substr(begin+1,end-begin-1).c_str());
//}
//---------------------------------------------------------------------

bool Cmenu_db::Select_Import_Map(CSG_String& FeatureName, CSG_String& GeomName)
{
	//-----------------------------------------------------
	// DIALOG
	//-----------------------------------------------------
	CSG_Parameters		P;

	P.Set_Name(LNG("Import Simple Feature from PostGIS"));

	P.Add_Choice(
		NULL	, "SF_NAME"	, LNG("Select Simple Feature"),
		"",
		g_pSAGA_Frame->db->GetDBGeometryTables().c_str()
	);
	//-----------------------------------------------------
	
	if( DLG_Parameters(&P) )
	{
		FeatureName	= _GetFeatureNameFromSelection(P("SF_NAME")->asString());
		GeomName	= g_pSAGA_Frame->db->_Get_GeometryColumnName(FeatureName.c_str()).c_str();
		return(true);
	} else {
		return(false);
	}
}
//---------------------------------------------------------------------

string Cmenu_db::_Reduce_TableName(string tablename)
{
	unsigned int pos = 0;
	//for (pos = 0; tablename[pos] != '0'; pos++)
	for (pos = 0; pos < tablename.size(); pos++)
	{
		if (tablename[pos] == ' ')
			return(tablename.substr(0,pos));
	}
	return(tablename);
}
//---------------------------------------------------------------------

wxString Cmenu_db::_Reduce_TableName(wxString tablename, char c)
{
	unsigned int pos = 0;
	//for (pos = 0; tablename[pos] != '0'; pos++)
	for (pos = 0; pos < tablename.size(); pos++)
	{
		if (tablename[pos] == c)
			return(tablename.substr(0,pos));
	}
	return(tablename);
}
//---------------------------------------------------------------------

string Cmenu_db::_EraseBlanks(string &input)
{
	char c = ' ';
	string temp = "";
	string str = "";
	for (unsigned int i = 0; i < input.size(); i++)
	{
		c = input[i];
		if (!isspace(c) || c == NULL) str += c;
	}
	return(str);
}
//---------------------------------------------------------------------

// returns the position of the first appearance
// of character 'search_string'
unsigned int Cmenu_db::_FindFirstCharPosition(string &s, char search_char)
{
	unsigned int	pos = 0;

	for (int i = 0 ; s[i] != '\0'; i++ )
	{
		if (s[i] != search_char)
		{
			pos ++;
		} else {
			return (pos);
		}
	}
	return(pos);
}
//---------------------------------------------------------------------

///////////////////////////////////////////////////////////////////////
//
//	EXPORT SAGA SHAPE FILE AS SIMPLE FEATURE
//
///////////////////////////////////////////////////////////////////////

bool Cmenu_db::_On_DB_ExportAsSimpleFeature(CSG_Shapes *pShapes, CSG_String& FeatureName, bool bNameEditable)
{
	wxString str = "Export Shape <";
	str += pShapes->Get_Name();
	str += "> as SimpleFeature";

	CSimpleFeature_export_dlg *dlg 
		= new CSimpleFeature_export_dlg(g_pSAGA_Frame,-1,wxT(str), pShapes);
	
	dlg->Set_PotShapeName(bNameEditable);

	if (dlg->ShowModal() == wxID_OK)
	{			
		FeatureName = dlg->Get_SimpleFeatureName();
		CSG_geometry_columns *p_gc = new CSG_geometry_columns();
		p_gc->TableCatalog		= dlg->Get_TableCatalog();
		p_gc->TableSchema		= dlg->Get_TableSchema();
		p_gc->SRID				= dlg->Get_SRID();
		p_gc->GeometryColumnName= dlg->Get_GeometryColumn(); // this is not used (redundant)
		p_gc->geom				= dlg->Get_GeometryColumn();
		p_gc->CoordDimension	= dlg->Get_CoordDimension();

		CSG_SimpleFeature *p_sf = new CSG_SimpleFeature();

		if (dlg->Get_PK_ColNumber() >= 0) // if an existing column was selected
		{
			p_sf->Create_SF_From_Shape(g_pSAGA_Frame->db->GetConn(),pShapes,p_gc,dlg->Get_SimpleFeatureName().c_str(),false,dlg->Get_PK_ColNumber());
		} else { // if a pk column must be created
			p_sf->Create_SF_From_Shape(g_pSAGA_Frame->db->GetConn(),pShapes,p_gc,dlg->Get_SimpleFeatureName().c_str(),true,0);
		}

		delete (p_gc);
		delete (p_sf);

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


CSG_Shapes* Cmenu_db::Select_Shapefile_for_Export()
{
	CSG_Parameters	P;
	CSG_Shapes*		pShapes;

	P.Set_Name(LNG("Select a Shapefile as basis map")
	);
	
	P.Add_Shapes(NULL, "SHAPE", "Choose a basis map",
		"Choose a shape file as basis for the soil map",
		PARAMETER_INPUT, SHAPE_TYPE_Polygon
	);

	if( DLG_Parameters(&P) )
	{
		pShapes = P("SHAPE")	->asShapes();
		return(pShapes);		
	}

	pShapes = NULL;
	return(NULL);
}
//---------------------------------------------------------------------

void Cmenu_db::_On_DB_ExportAsSimpleFeature()
{
	CSG_Shapes			*pShapes; // Shapefile exported to SimpleFeature
	CSG_Parameters		P1, P2;

	//-----------------------------------------------------
	// Dialog 1
	//-----------------------------------------------------
	P1.Set_Name(LNG("Export Shapefile to PostGIS Simple Feature"));

	P1.Add_Shapes(
		P1("SHAPE")		, "SHAPE"			, "",
		"",
		PARAMETER_INPUT
	);
	//-----------------------------------------------------
	
	if( DLG_Parameters(&P1) )
	{
		pShapes = P1("SHAPE")->asShapes();

		//-----------------------------------------------------
		// Dialog 2
		//-----------------------------------------------------
		wxString str = "Export Shape <";
		str += pShapes->Get_Name();
		str += "> as SimpleFeature";
		CSimpleFeature_export_dlg *dlg 
			= new CSimpleFeature_export_dlg(g_pSAGA_Frame,-1,wxT(str), pShapes);
		
		if (dlg->ShowModal() == wxID_OK)
		{			
			CSG_geometry_columns *p_gc = new CSG_geometry_columns();
			p_gc->TableCatalog		= dlg->Get_TableCatalog();
			p_gc->TableSchema		= dlg->Get_TableSchema();
			p_gc->SRID				= dlg->Get_SRID();
			p_gc->GeometryColumnName= dlg->Get_GeometryColumn(); // this is not used (redundant)
			p_gc->geom				= dlg->Get_GeometryColumn();
			p_gc->CoordDimension	= dlg->Get_CoordDimension();

			CSG_SimpleFeature *p_sf = new CSG_SimpleFeature();

			if (dlg->Get_PK_ColNumber() >= 0) // if an existing column was selected
			{
				p_sf->Create_SF_From_Shape(g_pSAGA_Frame->db->GetConn(),pShapes,p_gc,dlg->Get_SimpleFeatureName().c_str(),false,dlg->Get_PK_ColNumber());
			} else { // if a pk column must be created
				p_sf->Create_SF_From_Shape(g_pSAGA_Frame->db->GetConn(),pShapes,p_gc,dlg->Get_SimpleFeatureName().c_str(),true,0);
			}

			delete (p_gc);
			delete (p_sf);
		}
	} // end if Dialog 1
}
//---------------------------------------------------------------------

void Cmenu_db::_On_DB_DeleteSimpleFeature()
{
	CSG_String			SF_Name;

	//-----------------------------------------------------
	// DIALOG
	//-----------------------------------------------------
	CSG_Parameters		P;

	P.Set_Name(LNG("Select Simple Feature to be deleted from PostGIS"));

	P.Add_Choice(
		NULL	, "SF_NAME"	, LNG("Select Simple Feature"),
		"",
		g_pSAGA_Frame->db->GetDBGeometryTables().c_str()
	);
	//-----------------------------------------------------
	
	if( DLG_Parameters(&P) )
	{
		// get feature to be deleted
		SF_Name	= _GetFeatureNameFromSelection(P("SF_NAME")->asString());
		
		// drop simple feature
		_Drop_SimpleFeature(SF_Name);
	}
}
//---------------------------------------------------------------------
void Cmenu_db::_Drop_SimpleFeature(CSG_String SF_Name)
{
	std::ostringstream	sql;

	// drop table
	g_pSAGA_Frame->db->DropTable(SF_Name.c_str());

	// drop entry in table 'geometry_columns'
	sql << "DELETE FROM geometry_columns ";
	sql << "WHERE f_table_name = '" << SF_Name << "'";
	g_pSAGA_Frame->db->ExecSQL_str(sql.str());
}
//---------------------------------------------------------------------



///////////////////////////////////////////////////////////////////////
//
//	Menu Time series
//
///////////////////////////////////////////////////////////////////////

void Cmenu_db::_On_DB_TimeSeries_AutoAnalysis()
{
	//-----------------------------------------------------
	// Dialog
	//-----------------------------------------------------
	CSG_Parameters		P1;

	P1.Set_Name(LNG("Automatic Time Series Analysis"));

	P1.Add_Choice(
		NULL	, "TABLE"	, LNG("Select Import Table"),
		"",
		g_pSAGA_Frame->db->GetDBTables().c_str()
	);
	P1.Add_String(
		NULL,	"DATE_COL",	LNG("Select Date Column"),
		"",
		""
	);
	P1.Add_String(
		NULL,	"VAL_COL",	LNG("Select Value Column"),
		"",
		""
	);
	P1.Add_Choice(
		NULL,	"METHOD",	LNG("Select a statistical method"),
		"",
		CSG_String::Format("%s|%s|%s|%s|%s|",
			LNG("average"),
			LNG("sum"),
			LNG("minimum"),
			LNG("maximum"),
			LNG("count")
			)
	);
	//-----------------------------------------------------
	
	if( DLG_Parameters(&P1) )
	{
		wxString	tablename	= P1("TABLE")		->asString();
		const char* date_col	= P1("DATE_COL")	->asString();
		const char*	value_col	= P1("VAL_COL")		->asString();
		const char* method;
		const char* m = P1("METHOD")->asString();
		switch(P1("METHOD")->asInt())
		{
			case 0 : method = "avg"; break;
			case 1 : method = "sum"; break;
			case 2 : method = "min"; break;
			case 3 : method = "max"; break;
			case 4 : method = "count"; break;
			default : method = "avg";
		}

		CSG_Table	*pTable;

		int			firstYear = g_pSAGA_Frame->db->GetFirstYear(tablename.c_str(),date_col);
		int			lastYear  = g_pSAGA_Frame->db->GetLastYear(tablename.c_str(),date_col);;
		int			nYears = lastYear - firstYear + 1;
		CYear		*year = new CYear[nYears];

		// create table
		pTable = SG_Create_Table();
		CreateTimeSeries_Table(pTable);

		// Assign monthly statistical values to CYear[]
		double		value = 0.0;
		int			i = 0;
		for (int y = firstYear; y <= lastYear && PROGRESSBAR_Set_Position(y-firstYear,lastYear-firstYear); y++)
		{
			year[i].year = y;
			year[i].method = method;
			year[i].jan = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,1,y);
			year[i].feb = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,2,y);
			year[i].mar = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,3,y);
			year[i].apr = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,4,y);
			year[i].may = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,5,y);
			year[i].jun = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,6,y);
			year[i].jul = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,7,y);
			year[i].aug = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,8,y);
			year[i].sep = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,9,y);
			year[i].oct = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,10,y);
			year[i].nov = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,11,y);
			year[i].dec = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,12,y);
			AddRecord2TimeSeries_Table(pTable,year[i],i,true);
			i++;
		}
		PROCESS_Set_Okay(true);
		CYear summary;
		// Add a blank row
		summary.zeroAll();
		AddRecord2TimeSeries_Table(pTable,summary,i,false);

		// Add monthly statistics of entire period
		summary = AddSummary2TimeSeries_Table("avg",tablename,date_col,value_col, summary);
		AddRecord2TimeSeries_Table(pTable,summary,i+1,false);
		summary = AddSummary2TimeSeries_Table("sum",tablename,date_col,value_col, summary);
		AddRecord2TimeSeries_Table(pTable,summary,i+2,false);
		summary = AddSummary2TimeSeries_Table("min",tablename,date_col,value_col, summary);
		AddRecord2TimeSeries_Table(pTable,summary,i+3,false);	
		summary = AddSummary2TimeSeries_Table("max",tablename,date_col,value_col, summary);
		AddRecord2TimeSeries_Table(pTable,summary,i+4,false);	

		// add table to SAGA Workspace
		wxString	tName = tablename;
		tName += "_";
		tName += method;
		pTable->Set_Name(tName.c_str());
		g_pData->Add(pTable);
		
		delete year;
	} // end if( DLG_Parameters(&P1) )
}
//---------------------------------------------------------------------
void Cmenu_db::CreateTimeSeries_Table(CSG_Table* pTable)
{
	pTable->Add_Field("Year",TABLE_FIELDTYPE_String);
	pTable->Add_Field("jan",TABLE_FIELDTYPE_Double);
	pTable->Add_Field("feb",TABLE_FIELDTYPE_Double);
	pTable->Add_Field("mar",TABLE_FIELDTYPE_Double);
	pTable->Add_Field("apr",TABLE_FIELDTYPE_Double);
	pTable->Add_Field("may",TABLE_FIELDTYPE_Double);
	pTable->Add_Field("jun",TABLE_FIELDTYPE_Double);
	pTable->Add_Field("jul",TABLE_FIELDTYPE_Double);
	pTable->Add_Field("aug",TABLE_FIELDTYPE_Double);
	pTable->Add_Field("sep",TABLE_FIELDTYPE_Double);
	pTable->Add_Field("oct",TABLE_FIELDTYPE_Double);
	pTable->Add_Field("nov",TABLE_FIELDTYPE_Double);
	pTable->Add_Field("dec",TABLE_FIELDTYPE_Double);
}
//---------------------------------------------------------------------
void Cmenu_db::AddRecord2TimeSeries_Table(CSG_Table *pTable, CYear year, int record, bool bfirstColYear)
{
	CSG_Table_Record *pRecord;
	pTable->Add_Record();

	pRecord = pTable->Get_Record(record);
	if (bfirstColYear)
	{
		string str = convert_sl::Int2String(year.year);
		pRecord->Set_Value(0,str.c_str());
	} else {
		pRecord->Set_Value(0,year.method);
	}
	pRecord->Set_Value(1,year.jan);
	pRecord->Set_Value(2,year.feb);
	pRecord->Set_Value(3,year.mar);
	pRecord->Set_Value(4,year.apr);
	pRecord->Set_Value(5,year.may);
	pRecord->Set_Value(6,year.jun);
	pRecord->Set_Value(7,year.jul);
	pRecord->Set_Value(8,year.aug);
	pRecord->Set_Value(9,year.sep);
	pRecord->Set_Value(10,year.oct);
	pRecord->Set_Value(11,year.nov);
	pRecord->Set_Value(12,year.dec);
}
//---------------------------------------------------------------------
CYear &Cmenu_db::AddSummary2TimeSeries_Table(const char* method, const char* tablename, const char* date_col,const char* value_col, CYear &summary)
{
	summary.method = method;
	summary.jan = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,1,-999);
	summary.feb = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,2,-999);
	summary.mar = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,3,-999);
	summary.apr = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,4,-999);
	summary.may = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,5,-999);
	summary.jun = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,6,-999);
	summary.jul = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,7,-999);
	summary.aug = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,8,-999);
	summary.sep = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,9,-999);
	summary.oct = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,10,-999);
	summary.nov = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,11,-999);
	summary.dec = g_pSAGA_Frame->db->GetMonthly(method,tablename,date_col,value_col,12,-999);
	return(summary);
}
//---------------------------------------------------------------------


void Cmenu_db::_On_DB_TimeSeries_Average()
{
	C_dlg_ave_gauges *dlg_ave;
	dlg_ave = new C_dlg_ave_gauges(g_pSAGA_Frame, -1, "Averaging Gauge Time Series");
	dlg_ave->Show();
}
//---------------------------------------------------------------------


///////////////////////////////////////////////////////////////////////
//
//	HYDROLOGY
//
///////////////////////////////////////////////////////////////////////

void Cmenu_db::_On_DB_Hydrology_Modeling()
{
	Crr_dlg_preproc		*rr_dlg;

	rr_dlg = new Crr_dlg_preproc(g_pSAGA_Frame, -1, "Time Series Preprocessing for Rainfall-Runoff Modeling");
	rr_dlg->Show();
}

///////////////////////////////////////////////////////////////////////
//
//
//
///////////////////////////////////////////////////////////////////////

void Cmenu_db::_On_DB_CreateQuery()
{
	CSG_Table	*pTable;
	PGresult	*res;
	int			ncols = 0;
	int			nrows = 0;
	wxString	errorMessage;
	wxString	sqlcmd;

	// create dialog
	txtView = new CTextViewer(g_pSAGA_Frame,-1,"SQL Command",true);

	while (txtView->ShowModal() != wxID_CANCEL)
	{
		txtView->Clear_MsgBox();
		sqlcmd = txtView->Get_Text();
		txtView->Set_Message(sqlcmd);
		res = PQexec(g_pSAGA_Frame->db->GetConn(), sqlcmd.c_str());
		if (g_pSAGA_Frame->db->ExecSQL_wx1(res, sqlcmd, errorMessage))
		{
			ncols = PQnfields(res);
			nrows = PQntuples(res);

			// create SAGA Table from database table
			pTable = SG_Create_Table();
			// create field names and types
			for (int col = 0; col < ncols; col++)
				Add_SAGATableField(PQftype(res,col),PQfname(res,col),pTable);
			// add records
			for (int row = 0; row < nrows; row++)
				Add_SAGATableRecord(res,pTable,row,ncols);
			// add table to SAGA workspace
			pTable->Set_Name("SQL_Query");
			g_pData->Add(pTable);
			// show table
			g_pData->Show(pTable);
			
			PQclear(res);
			//delete txtView;
		} else {
			txtView->Set_Message(errorMessage);
			txtView->Set_Message("Command NOT successful");
			PQclear(res);
		}
	}
	delete txtView;
}

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

void Cmenu_db::_On_DB_Create_SpatialQuery()
{
	CSG_Parameters	P;

	P.Set_Name(LNG("Select Simple Feature"));

	P.Add_Choice(
		NULL	, "SF_NAME1"	, LNG("Select Simple Feature 1"),
		"",
		g_pSAGA_Frame->db->GetDBGeometryTables().c_str()
	);
	P.Add_Choice(
		NULL	, "SF_NAME2"	, LNG("Select Simple Feature 2"),
		"",
		g_pSAGA_Frame->db->GetDBGeometryTables().c_str()
	);
	//-----------------------------------------------------
	if( DLG_Parameters(&P) )
	{
		string featurename1	= _GetFeatureNameFromSelection(P("SF_NAME1")->asString());
		string featurename2	= _GetFeatureNameFromSelection(P("SF_NAME2")->asString());

		CSG_SimpleFeature *pSF1, *pSF2;
		pSF1 = new CSG_SimpleFeature(g_pSAGA_Frame->db->GetConn(),featurename1.c_str());
		pSF2 = new CSG_SimpleFeature(g_pSAGA_Frame->db->GetConn(),featurename2.c_str());

		MSG_ADD(g_pSAGA_Frame->db->Get_SQL_SpatialRelation("intersects",pSF1,pSF2,"560012").c_str());
	}
	
}

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


///////////////////////////////////////////////////////////////////////
//
//
//
///////////////////////////////////////////////////////////////////////

void Cmenu_db::_On_DB_ExecUserSQLCommand()
{
	PGresult	*res;
	wxString	errorMessage;
	wxString	sqlcmd;

	// create dialog
	txtView = new CTextViewer(g_pSAGA_Frame,-1,"SQL Command",true);

	while (txtView->ShowModal() != wxID_CANCEL)
	{
		txtView->Clear_MsgBox();
		sqlcmd = txtView->Get_Text();
		txtView->Set_Message(sqlcmd);
		res = PQexec(g_pSAGA_Frame->db->GetConn(), sqlcmd.c_str());
		if (g_pSAGA_Frame->db->ExecSQL_wx1(res, sqlcmd, errorMessage))
		{
			txtView->Set_Message("SQL Command successful");
			txtView->Set_Message(PQcmdStatus(res));
			PQclear(res);
		} else {
			txtView->Set_Message(errorMessage);
			txtView->Set_Message("Command NOT successful");
			PQclear(res);
		}
	}
	delete txtView;
}


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

string Cmenu_db::Get_FieldTypeFromSAGA(TSG_Table_Field_Type SG_ft)
{
	switch(SG_ft)
	{
	case TABLE_FIELDTYPE_None :
		return("UNKNOWN"); break;
	case TABLE_FIELDTYPE_Char :
		return("VARCHAR(1)"); break;
	case TABLE_FIELDTYPE_Short :
		return("INT4"); break;
	case TABLE_FIELDTYPE_Int :
		return("INT8"); break;
	case TABLE_FIELDTYPE_Long :
		return("INT8"); break;
	case TABLE_FIELDTYPE_Float :
		return("FLOAT4"); break;
	case TABLE_FIELDTYPE_Double :
		return("FLOAT8"); break;
	case TABLE_FIELDTYPE_String :
		return("VARCHAR"); break;
	case TABLE_FIELDTYPE_Color :
		return("UNKNOWN"); break;

	default: return("UNKNOWN");
	}
}
// ------------------------------------------------------------------------

bool Cmenu_db::is_numeric(TSG_Table_Field_Type SG_FT)
{
	switch (SG_FT)
	{
	case TABLE_FIELDTYPE_None :
		return(false); break;
	case TABLE_FIELDTYPE_Char :
		return(false); break;
	case TABLE_FIELDTYPE_Short :
		return(true); break;
	case TABLE_FIELDTYPE_Int :
		return(true); break;
	case TABLE_FIELDTYPE_Long :
		return(true); break;
	case TABLE_FIELDTYPE_Float :
		return(true); break;
	case TABLE_FIELDTYPE_Double :
		return(true); break;
	case TABLE_FIELDTYPE_String :
		return(false); break;
	case TABLE_FIELDTYPE_Color :
		return(false); break;

	default: return(false); 
	} // end switch
}
