/////////////////////////////////////////////////////////////////////////////
// Name:			pgsql_date.cpp
// Author:			Stefan Liersch
// Contact:			stefan.liersch@ufz.de stefan.liersch@googlemail.com
// Created:			2007-07-25
// last modified	2007-07-25
/////////////////////////////////////////////////////////////////////////////

#include "pgdatabase.h"
#include "saga_api/convert_sl.h"
#include "saga_gui/saga_frame.h"		// location of class Cpgdatabase
										// using global *g_pSAGA_Frame pointer
#include "saga_gui/helper.h"			// using function MSG_General_Add()
#define MSG_ADD(s)	MSG_General_Add(s, true, true);

//---------------------------------------------------------------------
wxDateTime Cpgdatabase::Init_wxDateTime()
{
	wxDateTime date = wxDateTime::UNow();
	date.SetYear(2000);
	date.SetMonth(wxDateTime::Jan);
	date.SetDay(1);
	return(date);
}
//---------------------------------------------------------------------
// This function returns the first year of a time series.
// A column with data type 'date' must exist.
int Cpgdatabase::GetFirstYear(const char *tablename, const char* date_col)
{
	int			firstYear;
	PGresult	*res;
	wxString	SQL = "SELECT extract(year from ";
	SQL += date_col;
	SQL += ") FROM ";
	SQL += tablename;
	SQL += " ORDER BY date ASC LIMIT 1";

	res = g_pSAGA_Frame->db->ExecSQL_wx2(SQL);
	if (g_pSAGA_Frame->db->get_ResultStatus(res))
	{
		firstYear = convert_sl::StringToInt(PQgetvalue(res,0,0));
		PQclear(res);
		return(firstYear);
	}
	MSG_ADD(SQL.c_str());
	return(-1);
}
//---------------------------------------------------------------------
// This function returns the first month of a time series.
// A column with data type 'date' must exist.
int Cpgdatabase::GetFirstMonth(const char *tablename, const char* date_col)
{
	int			firstMonth;
	PGresult	*res;
	wxString	SQL = "SELECT extract(month from ";
	SQL += date_col;
	SQL += ") FROM ";
	SQL += tablename;
	SQL += " ORDER BY date ASC LIMIT 1";

	res = g_pSAGA_Frame->db->ExecSQL_wx2(SQL);
	if (g_pSAGA_Frame->db->get_ResultStatus(res))
	{
		firstMonth = convert_sl::StringToInt(PQgetvalue(res,0,0));
		PQclear(res);
		return(firstMonth);
	}
	MSG_ADD(SQL.c_str());
	return(-1);
}
//---------------------------------------------------------------------
// This function returns the first day of a time series.
// A column with data type 'date' must exist.
int Cpgdatabase::GetFirstDay(const char *tablename, const char* date_col)
{
	int			firstDay;
	PGresult	*res;
	wxString	SQL = "SELECT extract(day from ";
	SQL += date_col;
	SQL += ") FROM ";
	SQL += tablename;
	SQL += " ORDER BY date ASC LIMIT 1";

	res = g_pSAGA_Frame->db->ExecSQL_wx2(SQL);
	if (g_pSAGA_Frame->db->get_ResultStatus(res))
	{
		firstDay = convert_sl::StringToInt(PQgetvalue(res,0,0));
		PQclear(res);
		return(firstDay);
	}
	MSG_ADD(SQL.c_str());
	return(-1);
}

//---------------------------------------------------------------------
// This function returns the last year of a time series.
// A column with data type 'date' must exist.
int Cpgdatabase::GetLastYear(const char *tablename, const char* date_col)
{
	int			lastYear;
	PGresult	*res;
	wxString	SQL = "SELECT extract(year from ";
	SQL += date_col;
	SQL += ") FROM ";
	SQL += tablename;
	SQL += " ORDER BY date DESC LIMIT 1";

	res = g_pSAGA_Frame->db->ExecSQL_wx2(SQL);
	if (g_pSAGA_Frame->db->get_ResultStatus(res))
	{
		lastYear = convert_sl::StringToInt(PQgetvalue(res,0,0));
		PQclear(res);
		return(lastYear);
	}
	MSG_ADD(SQL.c_str());
	return(-1);
}
//---------------------------------------------------------------------
// This function returns the last month of a time series.
// A column with data type 'date' must exist.
int Cpgdatabase::GetLastMonth(const char *tablename, const char* date_col)
{
	int			lastMonth;
	PGresult	*res;
	wxString	SQL = "SELECT extract(month from ";
	SQL += date_col;
	SQL += ") FROM ";
	SQL += tablename;
	SQL += " ORDER BY date DESC LIMIT 1";

	res = g_pSAGA_Frame->db->ExecSQL_wx2(SQL);
	if (g_pSAGA_Frame->db->get_ResultStatus(res))
	{
		lastMonth = convert_sl::StringToInt(PQgetvalue(res,0,0));
		PQclear(res);
		return(lastMonth);
	}
	MSG_ADD(SQL.c_str());
	return(-1);
}
//---------------------------------------------------------------------
// This function returns the last day of a time series.
// A column with data type 'date' must exist.
int Cpgdatabase::GetLastDay(const char *tablename, const char* date_col)
{
	int			lastDay;
	PGresult	*res;
	wxString	SQL = "SELECT extract(day from ";
	SQL += date_col;
	SQL += ") FROM ";
	SQL += tablename;
	SQL += " ORDER BY date DESC LIMIT 1";

	res = g_pSAGA_Frame->db->ExecSQL_wx2(SQL);
	if (g_pSAGA_Frame->db->get_ResultStatus(res))
	{
		lastDay = convert_sl::StringToInt(PQgetvalue(res,0,0));
		PQclear(res);
		return(lastDay);
	}
	MSG_ADD(SQL.c_str());
	return(-1);
}
//---------------------------------------------------------------------

// The day and year in wxDateTime can be assigned by integer values,
// but unfortunately it is not possible to assign the month like this.
bool Cpgdatabase::AssignMonthByInt(wxDateTime& date, int month)
{
	switch(month)
	{
		case(0):	date.SetMonth(wxDateTime::Jan); return(true);
		case(1):	date.SetMonth(wxDateTime::Feb); return(true);
		case(2):	date.SetMonth(wxDateTime::Mar); return(true);
		case(3):	date.SetMonth(wxDateTime::Apr); return(true);
		case(4):	date.SetMonth(wxDateTime::May); return(true);
		case(5):	date.SetMonth(wxDateTime::Jun); return(true);
		case(6):	date.SetMonth(wxDateTime::Jul); return(true);
		case(7):	date.SetMonth(wxDateTime::Aug); return(true);
		case(8):	date.SetMonth(wxDateTime::Sep); return(true);
		case(9):	date.SetMonth(wxDateTime::Oct); return(true);
		case(10):	date.SetMonth(wxDateTime::Nov); return(true);
		case(11):	date.SetMonth(wxDateTime::Dec); return(true);

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

//---------------------------------------------------------------------
double Cpgdatabase::GetMonthly(const char *method, const char *tablename, const char* date_col, const char* val_column, int month, int year)
{
	double		value = 0.0;
	PGresult	*res;
	wxString	SQL = "SELECT (SELECT ";
	SQL += method;
	SQL += "(";
	SQL += val_column;
	SQL += ")) FROM ";
	SQL += tablename;
	SQL += " WHERE (SELECT extract(month from ";
	SQL += date_col;
	SQL += ") = ";
	SQL += convert_sl::Int2String(month).c_str();
	if (year > 0)
	{
		SQL += ") AND (SELECT extract(year from ";
		SQL += date_col;
		SQL += ") = ";
		SQL += convert_sl::Int2String(year).c_str();
		SQL += ")";
	} else {
		SQL += ")";
	}

	res = g_pSAGA_Frame->db->ExecSQL_wx2(SQL);
	if (g_pSAGA_Frame->db->get_ResultStatus(res))
	{
		value = atof(PQgetvalue(res,0,0));
		PQclear(res);
		return(value);
	}
	MSG_ADD(SQL.c_str());
	return(-99.);
}

