/*------------------------------------------------------------------------------*
 * File Name: OADO.h	   														*
 * Creation: AW 11/21/2005														*
 * Purpose: Used for ADO programming for Origin DB 								*
 * Copyright (c) OriginLab Corp.	2005										*
 * All Rights Reserved															*
 * 																				*
 * Modification Log:															*
 *------------------------------------------------------------------------------*/
#ifndef _ORIGIN_ADO_H
#define _ORIGIN_ADO_H

#define IDD_ADO_QUERY_BUILDER           182 
#define IDC_ACTIVEQUERYBUILDERX1        2454
#define IDC_EDIT_SQL                    2455
#define IDC_CONNECTION                  2458
#define IDC_QUERY_COMBO1                2459
#define IDC_CREATE_QUERY                2460




#define ON_ACTIVEX_EVENT(_evt, _idCntrl, _ocFunc, _argList)				msgMap.AddEventMsg(_evt, _idCntrl, _ocFunc, _argList);

#define ON_BUILDER_SQL_CHANGED(_idCntl, _ocFunc)		ON_ACTIVEX_EVENT(212, _idCntl, _ocFunc, VTS_CTRL VTS_NONE )


#define STR_QUERY_NODE_TAG			"SQLNode"
#define STR_QUERY_NODE_NAME			"Name"
#define STR_QUERY_NODE_SQL			"SQL"
#define STR_QUERY_NODE_CONNECTION	"Connection"

#define SAVED_SQL_FILE_NAME			"SQLQuery.xml"


class ADOBuildQueryDlg : public ResizeDialog
{
protected:
	Object 			m_ActiveQueryBuilderX;
	Edit			m_SQL, m_Conectoion;
	ComboBox		m_ComboQuery;
	Tree 			m_trSQL;
	string 			m_strSavedFilePath;
	BOOL 			m_bSave;

public:
	string			m_strConnection, m_strSQL, m_strName;
	
public:
	ADOBuildQueryDlg(LPCSTR lpcszName = NULL, LPCSTR lpcszConnection=NULL, LPCSTR lpcszSQL=NULL) : ResizeDialog(IDD_ADO_QUERY_BUILDER, "ODlg8")
	{
		//m_strName = lpcszName;
		//m_strConnection = lpcszConnection;
		//m_strSQL = lpcszSQL;
		m_bSave = FALSE;
		try
		{
			m_strSavedFilePath = okutil_get_origin_path(ORIGIN_PATH_USER) + SAVED_SQL_FILE_NAME;
			if ( m_strSavedFilePath.IsFile() )
			{
				m_trSQL.Load(m_strSavedFilePath);	
			}
		}
		catch (int nErr)
		{
			MessageBox(NULL, "Can not load saved query file!", "error");
		}

	}
	int DoModalEx(HWND hWndParent = NULL)
	{
		InitMsgMap();// will be called from internal later
		return DoModal(hWndParent);
	}
	virtual int  Create(HWND hParent = NULL)
	{
		InitMsgMap();
		int nRet = ResizeDialog::Create(hParent);
		
		return nRet;
	}

protected:
	///----------------------------------------------
	///----------------- Message Map ----------------
	///
	EVENTS_BEGIN
		ON_INIT(OnInitDialog) 
		ON_SIZE(OnDlgResize)
		ON_BN_CLICKED(IDOK, OnOK)
		ON_BN_CLICKED(IDC_CREATE_QUERY, OnCreateQuery)
		ON_BUILDER_SQL_CHANGED(IDC_ACTIVEQUERYBUILDERX1, OnBulderSQLChanged)
		ON_CBN_SELCHANGE(IDC_QUERY_COMBO1, OnComboQuerySelectedChanged)
		ON_EN_KILLFOCUS(IDC_EDIT_SQL, OnSQLStringChanged)

	EVENTS_END
	///----------------------------------------------
	
	///////////////////////////////////////////////////////
	/// Event Handlers
	///////////////////////////////////////////////////////
	BOOL OnInitDialog()
	{
		ResizeDialog::OnInitDialog();
		
		
		try 
		{
			Control QueryControl = GetItem(IDC_ACTIVEQUERYBUILDERX1);
			m_SQL = GetItem(IDC_EDIT_SQL);
			m_Conectoion = GetItem(IDC_CONNECTION);
			m_ComboQuery = GetItem(IDC_QUERY_COMBO1);
			TreeNode trNameNode;
			foreach (TreeNode trSQLNode in m_trSQL.Children)
			{
				trNameNode = trSQLNode.GetNode(STR_QUERY_NODE_NAME);
				m_ComboQuery.AddString(trNameNode.Text);
			}
			if(QueryControl)
			{
				m_ActiveQueryBuilderX = QueryControl.GetActiveXControl();
			}

		}
		catch (int nError)
		{
			MessageBox(NULL, "Query Builder Exception error!", "error");
			return false;
			
		}
		if ( NULL == m_ActiveQueryBuilderX )
		{
			MessageBox(NULL, "Can't initialize Query Builder control !", "error");
			return false;
		}
		return true;
	
	}

	BOOL OnDlgResize(int nType, int cx, int cy)
	{
		uint nRightIDs[]  = {IDOK, IDCANCEL, 0};
		uint nBottomIDs[]  = {IDC_CONNECTION, IDC_EDIT_SQL, 0};
		ResizeMoveControlsRightBottom(IDC_ACTIVEQUERYBUILDERX1, nRightIDs, nBottomIDs, cx, cy);
		return true;
	}
	
	void OnBulderSQLChanged(Control cntrl)
	{
		string sql = m_ActiveQueryBuilderX.FormattedSQL;
		m_SQL.Text = sql;
	}
	void	OnComboQuerySelectedChanged(Control ctrl)
	{
		int nSelRow = m_ComboQuery.GetCurSel();
		string strName;
		m_ComboQuery.GetLBText(nSelRow, strName);
		TreeNode trSQLNode, trTemp;
		string strSQL, strConnection; 
		try
		{
			
			TreeNode trSQLNode = GetSelectedNode(strName);
			if ( trSQLNode.IsValid() )
			{
				m_strName = trSQLNode.GetNode(STR_QUERY_NODE_NAME).Text;
				m_strSQL = trSQLNode.GetNode(STR_QUERY_NODE_SQL).Text;
				m_strConnection = trSQLNode.GetNode(STR_QUERY_NODE_CONNECTION).Text;
				BuildQuery();
			}
			else
			{
				MessageBox(NULL, "Wrong selected string for Query file!", "error");
			}
		}
		catch (int eErr)
		{
			MessageBox(NULL, "Wrong Query file!", "error");
		}
	}
	void OnSQLStringChanged(Control cntrl)
	{
		try
		{
			m_ActiveQueryBuilderX.Sql = m_SQL.Text;
		}
		catch (int eErr)
		{
			MessageBox(NULL, "can not update query builder", "error");
		}
	}
	
	BOOL OnOK(Control cntrl)
	{
		string strConnection, strSQL;
		strConnection = m_strConnection;
		strSQL = m_strSQL;
		m_strConnection = m_ActiveQueryBuilderX.ConnectionString;
		m_strSQL = m_ActiveQueryBuilderX.FormattedSQL;
		if ( m_bSave || (0 != m_strSQL.Compare(strSQL) || 0 != m_strConnection.Compare(strConnection)) )
		{
			BOOL bOK;
			do
			{
				string strName;
				try
				{
					m_strName = InputBox("Do you want to save current query?", m_strName);
				}
				catch (int nErr)
				{
					return true;
				}
				bOK = UpdateQueryFile(m_strName);
			} while (!bOK);
			
		}
		m_trSQL.Save(m_strSavedFilePath);
		
		return PutRecordSetInWks();
	}
	
	
	BOOL OnCreateQuery(Control cntrl)
	{
		return CreateQuery();
	}
	BOOL CreateQuery()
	{
		string strName;
		BOOL bExits;
		do
		{
			try
			{
				strName = InputBox("Input a name for new query","");
			}
			catch (int nErr)
			{
				return FALSE;
			}
			TreeNode trNode = GetSelectedNode(strName);
			bExits = trNode.IsValid(); //nNamePos(strName) >= 0;
			
			if ( bExits )
				MessageBox(NULL, "The Name already exists, please use another name", "Error!");
		} while ( bExits );
		m_strName = strName;
		try
		{
			m_ActiveQueryBuilderX.EditConnectionString();
			string strConnection = m_ActiveQueryBuilderX.ConnectionString;
			m_SQL.Text = "";
			m_Conectoion.Text = strConnection;	
			if(!strConnection.IsEmpty()) 
			{
				m_strConnection = strConnection;
				m_strSQL = " ";
				m_ActiveQueryBuilderX.Connect();
				m_bSave = TRUE;
			}
			m_ActiveQueryBuilderX.SQL = " ";
		}
		catch (int nErr)
		{
			MessageBox(NULL, "can not create new query", "error");	
		}
		return true;
	}
	/*
	int nNamePos(LPCSTR lpcszOldName)
	{
		return m_ComboQuery.FindStringExact(0, lpcszOldName);
	}
	*/
	
	void BuildQuery()
	{
		try
		{
			string strempty = "";
			if ( m_strConnection.IsEmpty() )
			{
				m_ActiveQueryBuilderX.EditConnectionString();
				//m_ActiveQueryBuilderX.SQL = strempty;
				m_SQL.Text = strempty;
			} 	
			else
			{
				m_ActiveQueryBuilderX.ConnectionString = m_strConnection;
				m_ActiveQueryBuilderX.SQL = m_strSQL;
				m_SQL.Text = m_strSQL;
			}
			m_Conectoion.Text = m_ActiveQueryBuilderX.ConnectionString;			
			
			if(!m_strConnection.IsEmpty()) 
			{
				//m_strConnection = m_ActiveQueryBuilderX.ConnectionString;
				m_ActiveQueryBuilderX.Connect();
			}
		}
		catch (int nError)
		{
			MessageBox(NULL, "Query Builder Exception error!", "error");
			
		}
		
	}
	BOOL UpdateQueryFile(LPCSTR lpcszRueryName)
	{
		BOOL bOK = false;
		TreeNode trSQLNode, trNameNode;
		trSQLNode = GetSelectedNode(lpcszRueryName);
		TreeNode trTempNode;
		string strTemp;
		strTemp.Format("Do you want replace %s?", lpcszRueryName);
		if ( trSQLNode.IsValid() )
		{
			if( MessageBox( NULL, strTemp, "Warning!" , MB_YESNO | MB_ICONQUESTION | MB_DEFBUTTON2 ) == IDNO )
			{
				return false;
			}
			//trSQLNode.strVal = lpcszRueryName;
			trTempNode = trSQLNode.GetNode(STR_QUERY_NODE_SQL);
			trTempNode.Text = m_strSQL;
			trTempNode = trSQLNode.GetNode(STR_QUERY_NODE_CONNECTION);
			trTempNode.Text = m_strConnection;
		}
		else
		{
			trSQLNode = m_trSQL.AddNode(STR_QUERY_NODE_TAG);
			trTempNode = trSQLNode.AddTextNode(lpcszRueryName, STR_QUERY_NODE_NAME);
			//trSQLNode.strVal = lpcszRueryName;
			trTempNode = trSQLNode.AddTextNode(m_strSQL, STR_QUERY_NODE_SQL);
			trTempNode = trSQLNode.AddTextNode(m_strConnection, STR_QUERY_NODE_CONNECTION);
		}
		return true;
	}
	
	TreeNode GetSelectedNode(LPCSTR lpcszName)
	{
		TreeNode trSQLNode, trNameNode, trJunk;
		string strName(lpcszName);
		BOOL bFind = false;
		foreach (trSQLNode in m_trSQL.Children)
		{
			trNameNode = trSQLNode.GetNode(STR_QUERY_NODE_NAME);
			if ( trNameNode.IsValid() )
			{
				string strTemp = trNameNode.Text;
				if ( strName.Compare(strTemp) == 0 )
				{
					bFind = true;
					break;
				}
			}
		}
		if ( bFind ) 
			return trSQLNode;
		return trJunk;
	}
	
	BOOL PutRecordSetInWks()
	{
		Object ocrs;
		try
		{
			ocrs = CreateObject("ADODB.Recordset");
		}
		catch(int nError)
		{
			out_str("Failed to create ADODB.Recordset");
			return false;
		}
		
		if( !ocrs )
			return false;		
		ocrs.CursorLocation = 3;
		try
		{
			ocrs.open( m_strSQL, m_strConnection, 0, 1);
		}
		catch(int nError)
		{
			out_str("Failed to open database");
			return false;
		}
		Worksheet wks = Project.ActiveLayer();
		Matrix myMat("MBook1");
	
		try
		{
			myMat.PutRecordset(ocrs);
		}
		catch(int nError)
		{
			out_str("Failed to put data in Matrix");
			return false;
		}
		try
		{
			wks.PutRecordset(ocrs);
		}
		catch(int nError)
		{
			out_str("Failed to put data in Wks");
			return false;
		}
		return true;
	}

	
private:
	///////////////////////////////////////////////////////
	/// General utilities
	///////////////////////////////////////////////////////
	
};


















#endif  // _ORIGIN_ADO_H