/***********************************************************************/ 
/* Copyright (C) 2002 Definitive Solutions, Inc.  All Rights Reserved. */ 
/* THIS COMPUTER PROGRAM IS PROPRIETARY AND CONFIDENTIAL TO DEFINITIVE */ 
/* SOLUTIONS, INC. AND ITS LICENSORS AND CONTAINS TRADE SECRETS OF     */ 
/* DEFINITIVE SOLUTIONS, INC. THAT ARE PROVIDED PURSUANT TO A WRITTEN  */ 
/* AGREEMENT CONTAINING RESTRICTIONS ON USE AND DISCLOSURE.  ANY USE,  */ 
/* REPRODUCTION, OR TRANSFER EXCEPT AS PROVIDED IN SUCH AGREEMENT      */ 
/* IS STRICTLY PROHIBITED.                                             */ 
/***********************************************************************/ 

#include "stdafx.h"

#include "MyExporter.h"
#include "MyApp.h"
#include "MyLog.h"
#include "Generic.h"
#include <comdef.h>								// _bstr_t
#include <afxdb.h>								// CDBException
#include "odbcinst.h"							// ::SQLGetInstalledDrivers

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif


/////////////////////////////////////////////////////////////////////////////
// IMyExporter

// Constructor.
IMyExporter::IMyExporter()
{
	// Note that CoInitiaize() can be called as many times as you want, as 
	// long as each is matched by a CoUnitialize().
	VERIFY(SUCCEEDED(::CoInitialize(NULL)));
}

// Destructor.
/* pure virtual */ IMyExporter::~IMyExporter()
{
	HRESULT hr(S_OK);

	if (NULL != m_Recordset  &&  adStateOpen == m_Recordset->GetState()) {
		m_Recordset->Close();
	}

	if (NULL != m_Connection  &&  adStateOpen == m_Connection->GetState()) {
		EC_HEC(m_Connection->Close());
	}

	// Note that CoInitiaize() can be called as many times as you want, as 
	// long as each is matched by a CoUnitialize().
	::CoUninitialize();
}

// Open the file. Requires the fully qualified pathname.
HRESULT IMyExporter::OpenExportFile(IN const CString&      sPath,
									IN const CStringArray& saHeaders)
{
	_ASSERTE(! sPath.IsEmpty());
	_ASSERTE(0 < saHeaders.GetSize());
	_ASSERTE(NULL == m_Connection         &&  "Only call OpenExportFile() once per object!");
	_ASSERTE(NULL == m_Recordset          &&  "Only call OpenExportFile() once per object!");
	_ASSERTE(0 == m_saHeaders.GetSize()   &&  "Only call OpenExportFile() once per object!");

	#ifdef _DEBUG
	{
		for (int nHeader = 0; nHeader < saHeaders.GetSize(); ++nHeader) {
			CString sHeader(saHeaders.GetAt(nHeader));
			_ASSERTE(-1 == sHeader.FindOneOf(".")  &&  "Headers MUST NOT contain any of these characters");
		}
	}
	#endif

	HRESULT hr(S_OK);

	// File MUST NOT exist already, because the only way to add the headers
	// is to create a new file (for most data file types, anyway).
	if (! Generic::IsFile(sPath)) {
		EC_H(MakeNewFile(sPath, saHeaders));

		// Connect to the file.
		try {

			if (SUCCEEDED(hr)) {
				DOMYLOGT ("Connecting to file <%s>...\n", sPath);

				// Create the ADO connection object.
				EC_H(m_Connection.CreateInstance(__uuidof(Connection)));
				_ASSERTE(NULL != m_Connection);
				if (FAILED(hr)) _com_issue_error(hr);
				DOMYLOGD ("ADO Connection object <%p> created.\n", m_Connection);

				// Create the ADO recordset object.
				EC_HEC(m_Recordset.CreateInstance(__uuidof(Recordset)));
				_ASSERTE(NULL != m_Recordset);
				if (FAILED(hr)) _com_issue_error(hr);
				DOMYLOGD ("ADO Recordset object <%p> created.\n", m_Recordset);

				// Create the connection string.
				CString sConn;
				EC_H(GetConnectionString(sPath, sConn));
				if (FAILED(hr)) _com_issue_error(hr);
				DOMYLOGT ("Connecting using <%s>...\n", sConn);

				EC_H(m_Connection->Open(_bstr_t(sConn),
				  /* bstrUserId */ _bstr_t(""), /* bstrPassword */ _bstr_t(""),
				  adConnectUnspecified));

				if (FAILED(hr)) _com_issue_error(hr);

				// Open the recordset.
				CString sSelect;
				EC_H(GetSelectString(sPath, sSelect));

				EC_HEC(m_Recordset->Open(_bstr_t(sSelect),
				  _variant_t((IDispatch*) m_Connection), adOpenForwardOnly,
				  adLockOptimistic, adCmdText));

				DOMYLOGT ("... connected to file <%s>.\n", sPath);

				// We will need these in the AppendRow() method.
				for (int nHeader = 0; nHeader < saHeaders.GetSize(); ++nHeader) {
					m_saHeaders.Add(saHeaders.GetAt(nHeader));
				}
			}
		}
		catch(_com_error &e) {
			_ASSERTE(! "COM Error!");
			Generic::PrintComError(e);
			if (SUCCEEDED(hr)) hr = e.WCodeToHRESULT(e.WCode());
			if (SUCCEEDED(hr)) hr = E_FAIL;
			
			if (m_Connection) {
				_variant_t vtConnect = &m_Connection;
				
				if (VT_DISPATCH == vtConnect.vt) {
					Generic::PrintProviderError(vtConnect);
				}
			}
		}
		catch(CDBException* e) {
			
			// Log the error.
			_ASSERTE(! "Error - failed to open database");
			DOMYLOG ("Failed to open the database using path <%s>.\n", sPath);
			
			// Log the exception's error text.
			CString sError;
			VERIFY(e->GetErrorMessage(sError.GetBuffer(512), 512));
			sError.ReleaseBuffer();
			DOMYLOG ("CException error text is <%s>.\n", sError);
			
			// Display the error.
			e->ReportError();
			e->Delete();
			if (SUCCEEDED(hr)) hr = e->m_nRetCode;
			if (SUCCEEDED(hr)) hr = E_FAIL;
		}
	}
	else {
		_ASSERTE(! "Error");
		DOMYLOGE ("File <%s> already exists!\n", sPath);
		hr = E_FAIL;
	}

	return hr;
}

// Sets the value of a given cell. If the cell does not exist, it is added.
// Row and Col values are zero-based, but row zero is the header.
//
// Note: we add data a row at a time because (a) it's faster than doing it
// one cell at a time, and (b) the Microsoft Text Driver doesn't support
// "Update", only "Insert".
HRESULT IMyExporter::AppendRow(IN const CStringArray& saValues)
{
	_ASSERTE(NULL != m_Connection  &&  "You must call OpenExportFile() first!");
	_ASSERTE(NULL != m_Recordset   &&  "You must call OpenExportFile() first!");
	_ASSERTE(m_saHeaders.GetSize() == saValues.GetSize()  &&  "Must be the same size");

	HRESULT hr(S_OK);

	// Check for the runtime user; the "Unable to impersonate DCOM client"
	// error message is not especially helpful.
	if (NULL != m_Connection  &&  NULL != m_Recordset) {

		// Create the fields array.
		int nColumns(m_saHeaders.GetSize());
		COleSafeArray vaFieldList;
		vaFieldList.CreateOneDim(VT_VARIANT, nColumns);

		// Fill the fields array.
		long lArrayIndex[1];

		for (int nHeader = 0; nHeader < nColumns; ++nHeader) {
			lArrayIndex[0] = nHeader;
			vaFieldList.PutElement(lArrayIndex, &(_variant_t(m_saHeaders.GetAt(nHeader))));
		}
		
		// Create the values array.
		COleSafeArray vaValueArray;
		vaValueArray.CreateOneDim(VT_VARIANT, nColumns);
		
		// Fill the values array.
		for (nHeader = 0; nHeader < nColumns; ++nHeader) {
			lArrayIndex[0] = nHeader;
			vaValueArray.PutElement(lArrayIndex, &(_variant_t(saValues.GetAt(nHeader))));
		}
		
		// Add the new record to the recordset.
		EC_HEC(m_Recordset->AddNew(vaFieldList, vaValueArray));
		EC_HEC(m_Recordset->Update());
	}
	else {
		hr = E_FAIL;

		DOMYLOGE ("Either m_Connection or m_Recordset are NULL. Did you call "
		  "OpenExportFile() first?\n");
	}

	return hr;
}

// This is what is referred to as a "virtual constructor". It creates a
// derived object based on the three-letter extension passed in. If you add a
// new derived class, be sure to update this method. The caller is responsible
// for deleting the returned object.
/* static */ IMyExporter* IMyExporter::Create(IN const CString& sExt)
{
	if (0 == sExt.CompareNoCase("xls")) {
		return new MyExcelExporter;
	}
	else if (0 == sExt.CompareNoCase("csv")) {
		return new MyCsvExporter;
	}
	else {
		// Did you forget to add an entry here for your new type?
		_ASSERTE(! "Error");

		DOMYLOGE ("The extension <%s> is not found in IMyExporter::Create().\n",
		  sExt);

		return NULL;
	}
}

// This returns the list of supported types; if you add a new derived class,
// be sure to update this method.
/* static */ void IMyExporter::GetExportFormats(OUT CStringArray& saFormats)
{
	_ASSERTE(0 == saFormats.GetSize()  &&  "Not a good idea to send this populated!");

	saFormats.RemoveAll();

	// Here is where you would add any new formats. The first three letters
	// must be the file extension (so that other classes can use this info).
	saFormats.Add("XLS - Microsoft Excel spreadsheet");
	saFormats.Add("CSV - Comma Separated Values file");
}


/////////////////////////////////////////////////////////////////////////////
// MyExcelExporter

// Constructor.
MyExcelExporter::MyExcelExporter()
{
}

// Destructor.
/* pure virtual */ MyExcelExporter::~MyExcelExporter()
{
}

// Given the file name, return the connection string.
/* pure virtual */ HRESULT MyExcelExporter::GetConnectionString(
											 IN  const CString& sPath,
											 OUT CString&       sConn) const
{
	_ASSERTE(! sPath.IsEmpty());
	_ASSERTE(sConn.IsEmpty()  &&  "Not a good idea to send this populated!");

	HRESULT hr(E_FAIL);
	sConn.Empty();

	if (! sPath.IsEmpty()) {

		sConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
		  "Data Source=" + sPath + ";"
		  "Extended Properties=\"Excel 8.0\"";

		hr = S_OK;
	}
	else {
		DOMYLOGE ("Empty path sent to MyExcelExporter::GetConnectionString().\n");
	}

	return hr;
}

// Create a new file from the sent path and headers. There is no easy way to do
// this, and no way at all using ADO or ADOX.
/* pure virtual */ HRESULT MyExcelExporter::MakeNewFile(
										IN const CString&      sPath,
										IN const CStringArray& saHeaders) const
{
	_ASSERTE(! sPath.IsEmpty());
	_ASSERTE(0 < saHeaders.GetSize());
	_ASSERTE(! Generic::IsFile(sPath)  &&  "File already exists!");

	HRESULT hr(S_OK);

	// Get the Excel ODBC driver.
	char szDrivers[16 * 1024] = { 0 };
	WORD wBytesOut(0);

	EC_B(::SQLGetInstalledDrivers(szDrivers, sizeof(szDrivers) / sizeof(char),
	  &wBytesOut));

	// Search for the Excel driver.
	CString sExcelDriver;

	if (SUCCEEDED(hr)) {
		hr = E_FAIL;
		const char* pszBuf = szDrivers;

		do {
			if (strstr(pszBuf, "Excel")) {
				sExcelDriver = pszBuf;
				hr = S_OK;
				break;
			}

			pszBuf = strchr(pszBuf, '\0') + 1;
		}
		while (pszBuf[1]);
	}

	// If we found the ODBC driver, build the DSN.
	if (SUCCEEDED(hr)) {
		CString sConnect;

		// Now create the MFC CDatabase. But, there's a problem. The maximum
		// length allowed for 'sPath' is 64 characters. I don't know why. My
		// assumption is that this is an ODBC limit on the length of entity
		// names (like tables, databases, indexes, etc.).
		//
		// To work around this problem, we simply create the file in the TEMP
		// directory, then move it to the real pathname when we're done.
		//
		// For more info, or to see if anyone has suggested a fix, yet, just
		// search <http://groups.google.com> for "CREATE_DB to make an Excel
		// "file - filename has 64 char limit?".

		// Create the temporary filename in the TEMP folder. Note that we have
		// to replace the TMP extension with one that the Jet engine will
		// recognize from the Registry.
		CString sTempDir;
		EC_B(::GetTempPath(MAX_PATH, sTempDir.GetBuffer(MAX_PATH)));
		sTempDir.ReleaseBuffer();

		CString sTempPathName;

		EC_B(::GetTempFileName(sTempDir, /* sPrefix */ "MyE",
		  /* uiUinque */ 0U, sTempPathName.GetBuffer(MAX_PATH)));

		sTempPathName.ReleaseBuffer();

		EC_B(0 < sTempPathName.Replace(".tmp", ".xls")  ||
		  0 < sTempPathName.Replace(".TMP", ".xls"));
		
		_ASSERTE(64 >= sTempPathName.GetLength()  &&  "Still too long - now what?!");

		// Create the SQL command.
		sConnect.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=0;READONLY=FALSE;"
		  "CREATE_DB=\"%s\";DBQ=%s", sExcelDriver, sTempPathName, sTempPathName);
		
		CDatabase db;
		EC_BED(db.OpenEx(sConnect, CDatabase::noOdbcDialog));

		// Add the columns.
		CString sCmd("create table [Sheet1](");
		
		for (int nHeader = 0;
		  SUCCEEDED(hr)  &&  nHeader < saHeaders.GetSize();
		  ++nHeader) {

			CString sHeader;
			sHeader.Format("[%s] char (255)", saHeaders.GetAt(nHeader));

			if (nHeader + 1 == saHeaders.GetSize()) {
				sHeader += ")";
			}
			else {
				sHeader += ",";
			}

			sCmd += sHeader;
		}

		// Create the database using the SQL command. Note there is a list
		// somewhere in the Registry of legal exts for Excel files, and there's
		// no guarantee, if the user enters anything other than XLS, that it
		// will work correctly here.
		EC_VEC_C(db.ExecuteSQL(sCmd), m_Connection);

		// Done.
		EC_V_(db.Close());

		// Now copy the temp file to the user-specified path.
		EC_B(::MoveFileEx(sTempPathName, sPath, MOVEFILE_COPY_ALLOWED | 
		  MOVEFILE_WRITE_THROUGH));

		DOMYLOGT ("Moved file <%s> to <%s>.\n", sTempPathName, sPath);
	}
	else {
		_ASSERTE(! "Error");
		DOMYLOGE ("Could not find an Excel ODBC driver.\n");
	}

	return hr;
}

// Given the filename, return the select string for the recordset.
/* pure virtual */ HRESULT MyExcelExporter::GetSelectString(
											IN const CString& sPath,
											OUT CString&      sSelect) const
{
	UNUSED_ALWAYS(sPath);
	_ASSERTE(sSelect.IsEmpty()  &&  "Not a good idea to send this populated!");
	
	sSelect = "select * from [Sheet1$A1:IV65536]";

	return S_OK;
}


/////////////////////////////////////////////////////////////////////////////
// MyCsvExporter

// Constructor.
MyCsvExporter::MyCsvExporter()
{
}

// Destructor.
/* pure virtual */ MyCsvExporter::~MyCsvExporter()
{
}

// Given the file name, return the connection string.
/* pure virtual */ HRESULT MyCsvExporter::GetConnectionString(
											 IN  const CString& sPath,
											 OUT CString&       sConn) const
{
	_ASSERTE(! sPath.IsEmpty());
	_ASSERTE(sConn.IsEmpty()  &&  "Not a good idea to send this populated!");

	HRESULT hr(E_FAIL);
	sConn.Empty();

	if (! sPath.IsEmpty()) {

		sConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
		  "Data Source=" + Generic::DirFromPath(sPath) + ";"
		  "Extended Properties=\"Text;HDR=YES;FMT=Delimited\"";

		hr = S_OK;
	}
	else {
		DOMYLOGE ("Empty path sent to MyCsvExporter::GetConnectionString().\n");
	}

	return hr;
}

// Create a new file from the sent path and headers.
/* pure virtual */ HRESULT MyCsvExporter::MakeNewFile(
										IN const CString&      sPath,
										IN const CStringArray& saHeaders) const
{
	_ASSERTE(! sPath.IsEmpty());
	_ASSERTE(0 < saHeaders.GetSize());
	_ASSERTE(! Generic::IsFile(sPath)  &&  "File already exists!");

	HRESULT hr(S_OK);

	// Open the file.
	CStdioFile fil;
	EC_B(fil.Open(sPath, CFile::modeCreate | CFile::modeWrite | CFile::shareExclusive));

	// Write the headers out.
	CString sHeader;

	for (int nHeader = 0; nHeader < saHeaders.GetSize(); ++nHeader) {
		sHeader += "\"" + saHeaders.GetAt(nHeader) + "\"";

		if (saHeaders.GetSize() - 1 != nHeader) {
			sHeader += ",";
		}
	}

	EC_VEF(fil.WriteString(sHeader));

	// Close the file.
	EC_VEF(fil.Close());

	return hr;
}

// Given the filename, return the select string for the recordset.
/* pure virtual */ HRESULT MyCsvExporter::GetSelectString(
											IN const CString& sPath,
											OUT CString&      sSelect) const
{
	_ASSERTE(! sPath.IsEmpty());
	_ASSERTE(sSelect.IsEmpty()  &&  "Not a good idea to send this populated!");

	sSelect.Format("select * from [%s]", Generic::FileFromPath(sPath));

	return S_OK;
}