/*------------------------------------------------------------------------------*
 * File Name: datasource.h	   													*
 * Creation: AW 11/21/2005														*
 * Purpose: utility functios for data source	 								*
 * Copyright (c) OriginLab Corp.	2005										*
 * All Rights Reserved															*
 * 																				*
 * Modification Log:															*
 *	ML 1/25/2008 QA70-10972-P4 ADO_EXCEPTION_ON_EMPTY_RECORDSET					*
 *	Sophy 7/27/2009 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER		*
 *	Sophy 8/3/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
 *------------------------------------------------------------------------------*/

#ifndef _DATA_SOURCE_H_
#define _DATA_SOURCE_H_

#define STR_QUERY_FILE_EXT				".odq"
#define STR_DS_FILE_EXT					".ods"
//#define STR_DB_PREVIEW					" TOP 5 "

///Sophy 8/3/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifdef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
#define	STR_QUERY_MODE_ODBC		"ODBC"
#define	STR_QUERY_MODE_ADO		"ADO"
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
/*
enum {
 DBWKS_DISCONNECT, // connect to data source
 DBWKS_CONNECT,    // prepare cols in wks, remember which col to which in db table
 DBWKS_PREVIEW, // get first a few data to wks
 DBWKS_GETDATA, // get full data to wks
 DBWKS_SHOWINFO,
};
*/
enum {
adStateClosed=0 , 
adStateOpen=1, 
adStateConnecting=2, 
adStateExecuting= 4, 
adStateFetching = 8, 
};

enum AffectEnum
    {	adAffectCurrent	= 1,
	adAffectGroup	= 2,
	adAffectAll	= 3,
	adAffectAllChapters	= 4
    };
enum ResyncEnum
    {	adResyncUnderlyingValues	= 1,
	adResyncAllValues	= 2
    };

    
/// AW 1/21/2008 QA80-10972 MAKE_CONNECTION_STRING   
///  referer to http://support.microsoft.com/kb/193332
//CPY: we don't need this, it seems, we should just leave it for user to build their connection string
/// END MAKE_CONNECTION_STRING

/**
		Load data source string from ODQ file
	Parameters:
		lpcszFilename=[in]ODQ file name
		strDatasource=[out] result data source string
		pstrDSfilename=[out] when it is not NULL retrive original data source filename
	Example1:
		void db_load_data_source_ex1()
		{
			string	strFilename = GetOpenBox("[*.odq] *.odq");	
			string 	strDatasource, strDSfilename;
			if( !db_load_data_source(strFilename, strDatasource, &strDSfilename) )
			{
				out_str("Fail to get data source info from odq file");
				return;
			}
			out_str("Data Source :  " + strDatasource);
		}
	Return:
		Returns TRUE on successful exit and FALSE on failure.
	SeeAlso:
		 db_load_query, db_save_query, db_wks_set_query
*/
BOOL db_load_data_source(LPCSTR lpcszFileName, string& strDatasource, string* pstrDSfilename = NULL);

/**
		To check databse connection string if include password information.
	Parameters:
		lpcszConnStr = [input] connection string
		pstrUseName = [output] optional output, to receive user name
		pstrDS = [output] optional output, to receive database name
	Example1:
		void db_is_connection_str_has_password_ex1()
		{
			string	strConn = "Provider=Microsoft.Jet.OLEDB.4.0; User ID=admin;";
			
			string	strUserName, strDS;
			bool	bHasPW = db_is_connection_str_has_password(strConn);	
			
			if( !bHasPW )
			{
				string 	strNewConn;
				db_add_password_to_connection_str(strConn, "tester", "123", strNewConn);
				out_str(strNewConn);
			}
		}
	Return:
		Returns TRUE on successful exit and FALSE on failure.
	SeeAlso:
		 db_add_password_to_connection_str		
*/
BOOL db_is_connection_str_has_password(LPCSTR lpcszConnStr, string* pstrUseName = NULL, string* pstrDS = NULL);

/**
		To add user name and password to connection string
	Parameters:
		lpcszConnStr = [input] original connection string
		lpcszUser = [input] user name
		lpcszPwd = [input] password
		strConnStr = [output] the connection string after add username and password
	Return:
		Returns TRUE on successful exit and FALSE on failure.
	SeeAlso:
		 db_is_connection_str_has_password		
*/
BOOL db_add_password_to_connection_str(LPCSTR lpcszConnStr, LPCSTR lpcszUser, LPCSTR lpcszPwd, string& strConnStr);

/**
		Load query and data source strings from the query file or data source file. 
	Example1:
		// get query information from input ODQ file and do import
		int db_load_query_ex1()
		{
			string	strFilename = GetOpenBox("[*.odq] *.odq");
			
			string 	strQuery, strDataSource, stDSFileName;
			if ( !db_load_query(strDataSource, strQuery, strFilename, &stDSFileName) )
			{
				out_str("Failed to get query information from the file");
				return -1;				
			}
			
			Worksheet wks = Project.ActiveLayer();
			
			if ( ! db_wks_set_query(wks, strDataSource, strQuery) )
			{
				out_str("Failed to put query information into worksheet");
				return -1;				
			}	
			return db_wks_import(wks);
		}
	Parameters:
		strDatasource=[out] data source string
		strQuery=[out] query string
		lpcszFilename=[in]query file name
		pstrDSfilename=[out] when it is not NULL retrive original data source filename in query file
		pstrODQfilename[out] when not NULL to retrive the ODQ file name since input might be NULL
	Return:
		Returns TRUE on successful exit and FALSE on failure.
	SeeAlso:
		 db_save_query, db_wks_set_query
*/
///Sophy 8/3/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifndef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
BOOL db_load_query(string& strDatasource, string& strQuery, LPCSTR lpcszFileName, string* pstrDSfilename = NULL, string* pstrODQfilename= NULL);
#else
BOOL db_load_query(string& strDatasource, string& strQuery, LPCSTR lpcszFileName, string* pstrDSfilename = NULL, string* pstrODQfilename = NULL, string* pstrQueryMode = NULL);
#endif	//SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER

/**
		Save query and data source strings to the query file and data source file. 
	Example1:
		void db_save_query_ex1()
		{
			string	strDatabaseFile = GetAppPath(1) + "Samples\\Import and Export\\stars.mdb";	
			string	strConn;
			strConn.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s; User ID=admin; Password=;", strDatabaseFile);
			string	strSQL = "Select Stars.Index, Stars.Name, Stars.LightYears, Stars.Magnitude From Stars";	
		
			string	strFilename = GetSaveAsBox("[*.odq] *.odq");	
			if ( !db_save_query(strFilename, strConn, strSQL) )
			{
				out_str("Failed to get query information from the file");
			}	
		}
	Parameters:
		lpcszFilename=[input]query file name
		lpcszDatasource=[input] data source string
		lpcszQuery=[input] query string
		lpcszDSfilename=[input] when it is not NULL it is used as the filename where to save data source string
	Return:
		Returns TRUE on successful exit and FALSE on failure.
	SeeAlso:
		 db_load_query
*/
///Sophy 8/3/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifndef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
BOOL db_save_query(LPCSTR lpcszFilename, LPCSTR lpcszDatasource, LPCSTR lpcszQuery, LPCSTR lpcszDSfilename = NULL);
#else
BOOL db_save_query(LPCSTR lpcszFilename, LPCSTR lpcszDatasource, LPCSTR lpcszQuery, LPCSTR lpcszDSfilename = NULL, LPCSTR lpcszQueryMode = NULL);
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__

/**
		Set query and data source strings to the worksheet. 
	Example1:
		int db_wks_set_query_ex1()
		{
			string	strFilename = GetOpenBox("[*.odq] *.odq");
			
			string strQuery, strDataSource, stDSFileName;
			if ( !db_load_query(strFilename, strQuery, strDataSource, &stDSFileName) )
			{
				out_str("Failed to get query information from the file");
				return -1;				
			}
			Worksheet wks = Project.ActiveLayer();
			
			if ( ! db_wks_set_query(wks, strDataSource, strQuery) )
			{
				out_str("Failed to put query information into worksheet");
				return -1;				
			}	
			return 0;
		}
	Parameters:
		wks = [input]worksheet name where query and data source strings to be saved
		lpcszDatasource = [input] query string
		lpcszQuery = [input] data source string
		lpcszDSfilename = [input] the data source file that was used
		bAfterImportSuccess = [input] this will determin to set LastModified(false) or LastUsed (true) nodes in the SQL object
		pbUseODBC = [input] if not NULL, indicate whether use ODBC to import data
	Return:
		Returns TRUE on successful exit and FALSE on failure.
	SeeAlso:
		 db_wks_get_query, db_load_query
*/
///Sophy 7/27/2009 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifndef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
BOOL db_wks_set_query(Worksheet& wks,  LPCSTR lpcszDatasource = NULL, LPCSTR lpcszQuery = NULL, LPCSTR lpcszDSfilename = NULL, bool bAfterImportSuccess = false);
#else
BOOL db_wks_set_query(Worksheet& wks,  LPCSTR lpcszDatasource = NULL, LPCSTR lpcszQuery = NULL, LPCSTR lpcszDSfilename = NULL, bool bAfterImportSuccess = false, BOOL* pbUseODBC = NULL);
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
///end SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
/**
		Get query and data source strings from worksheet.
	Example1:
		BOOL db_wks_get_query_ex1(LPCSTR lpcszFilename, LPCSTR lpcszDSfilename = NULL)
		{
			string strQuery, strDataSource, stDSFileName;
			if ( !db_wks_get_query(wks, strDataSource, strQuery) )
			{
				out_str("Failed to get query information from the worksheet");
				return FALSE;
			}
			if ( !db_save_query(lpcszFilename, strDataSource, strQuery, &stDSFileName) )
			{
				out_str("Failed to get query information from the file");
				return FALSE;				
			}
			return TRUE;
		}
	Parameters:
		wks = [input]worksheet name where query and data source strings to be saved
		strDatasource = [output] query string
		strDatasource = [output] data source string
		strDSfilename = [output] data source file name that might have been saved as well
		pbUseODBC = [output] if not NULL, retrieve the settings of whether to import data using ODBC
	Return:
		Returns TRUE on successful exit and FALSE on failure.
	SeeAlso:
		 db_save_query, db_wks_set_query
*/
///Sophy 7/27/2009 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifndef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
BOOL db_wks_get_query(Worksheet& wks, string& strDatasource, string& strQuery, string& strDSfilename = NULL);
#else
BOOL db_wks_get_query(Worksheet& wks, string& strDatasource, string& strQuery, string& strDSfilename = NULL, BOOL* pbUseODBC = NULL);
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
///end SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER

/**
		worksheet database import using SQL info stored in the worksheet
	Example1:		
		// Import included a small Access database in the Samples folder called stars.mdb to active worksheet.
		#include <..\Originlab\datasource.h>
		void db_wks_import_ex1()
		{
			Worksheet	wks = Project.ActiveLayer();
			if( !wks )
				return;
			
			// set settings to worksheet
			string	strDatabaseFile = GetAppPath(1) + "Samples\\Import and Export\\stars.mdb";	
			if( strDatabaseFile.IsFile() )
			{
				string	strConn;
				strConn.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s; User ID=admin; Password=;", strDatabaseFile);			
				string	strSQL = "Select Stars.Index, Stars.Name, Stars.LightYears, Stars.Magnitude From Stars";
				
				if( db_wks_set_query(wks, strConn, strSQL) )
				{
					//do import 
					db_wks_import(wks, 0, 0, true);
					
					// remove settings from worksheet
					db_wks_set_query(wks);
					
					return;
				}
				MessageBox(NULL, "Fail to set query to worksheet.");
			}
			else
			{
				string	strErr;
				strErr.Format("Not found database file %s under %s", GetFileName(strDatabaseFile), GetFilePath(strDatabaseFile) );
				MessageBox(NULL, strErr);
			}
		}		
	
	Parameters:
		wks = [input]worksheet object 
		nPreviewLines = [input] if > 0, will import only the 1st number of rows specfied, if = 0, will import all 
		nColBegin = [input] first column to start import
		bAllowSavePassword = [input] if save password to worksheet
		bNeedPassWord = [input] need check password in connection
	Return:
		return <0 err code or 0 for success, returns 1 if connection string is updated user id and password
	SeeAlso:
		 db_wks_set_query, db_load_query
*/
int db_wks_import(Worksheet& wks, int nPreviewLines = 0, int nColBegin = 0, bool bAllowSavePassword = false, bool bNeedPassWord = true, HWND hDlg = NULL); /// AW 05/05/2006 HANDLE_PASSWORD_IN_CONNECTION

/// 02/04/08 AW MORE_ON_LOGIN_PROMPT
//bool DB_GetPassword(HWND hWndParent, string& strDS, string& strUser, string& strConn, bool bAllowSavePassword = false); /// AW 05/05/2006 HANDLE_PASSWORD_IN_CONNECTION
bool DB_GetPassword(HWND hWndParent, string& strDS, string& strUser, string& strConn, LPCSTR lpcszDlgTitle, bool bAllowSavePassword= false);
/// END MORE_ON_LOGIN_PROMPT




/// AW 02/04/08 MORE_ON_LOGIN_PROMPT
/**
		check connect string, if return true need set querybuild.LoginPrompt as TRUE
*/
BOOL db_need_login_prompt(LPCSTR lpcszConnStr);
/// END MORE_ON_LOGIN_PROMPT
/*
	worksheet data source action	
*/
//int db_act(Worksheet& wks, const string& strConn, const string& strQuery, int nAction, int nColBegin = 0);
enum
{
	ADO_ERR_CREATE_OBJ = -4,
	ADO_ERR_GET_SQL_STR = -3,
	ADO_ERROR_NONE = 0,
	ADO_ERROR_CONN_STR_CHANGED = 1,
	ADO_ERROR_CLOSE_CONN,
	ADO_ERROR_CLOSE_RECORDSET,
	ADO_ERROR_OPEN_CONN,
	ADO_ERROR_OPEN_RECORDSET,
	ADO_ERROR_RESYNC_RECORDSET,
	ADO_ERROR_RECORDSET_IS_NOT_OPEN,
	INVALID_WKS_DB_COMMAND,
	FAILED_TO_PUT_RECORD_TO_WKS,
	/// ML 1/25/2008 QA70-10972-P4 ADO_EXCEPTION_ON_EMPTY_RECORDSET
	ADO_ERROR_EMPTY_RECORDSET,
	/// end ADO_EXCEPTION_ON_EMPTY_RECORDSET
	///Sophy 7/27/2009 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifdef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
	ADO_ERROR_INVALID_ODBC_DSN,
	ADO_ERROR_CONN_ODBC_FAIL,
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
	///end SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
};

/**
	Work for access database in workbook
*/
class OWKSDB
{
public:
	OWKSDB(Worksheet& wks);
	~OWKSDB();
//public:
//	int Act(Worksheet& wks, int nAction, int nColBegin);
//	int Act(Worksheet& wks, const string& strConn, const string& strQuery, int nAction, int nColBegin);
//	int Connect(const string& strConn, const string& strSQL);
//	int Disconnect();
	//if nPreviewLines > 0, will preview that many lines
	int Import(int nColBegin = 0, int nPreviewLines = 0, bool bAllowSavePassword = false, bool bNeedPassWord = true, HWND hDlg = NULL);
//protected:
private:
	//Object		m_rs;
	//Object 		m_Conn;
	Worksheet 	m_wks;
};

#endif //_DATA_SOURCE_H_