#include "stdafx.h"

/*
* Access Êý¾Ý¿â½Ó¿Ú³ÌÐò
*
*
*
*
*/

int CreateMdb(CString MdbPathName)
{
	//µ¼Èë¶ÔÓ¦Dll


	//::CoInitialize(NULL);
	ADOX::_CatalogPtr   pCatalog(__uuidof(ADOX::Catalog));
	CString s1,s2;
//	s1=_T("Provider=Microsoft.Jet.OLEDB.4.0;data source=")+MdbPathName;
	s1=_T("Provider=Microsoft.ACE.OLEDB.12.0;data source=")+MdbPathName;
//	s1=_T("File Name=.\\A1.UDL");
	_bstr_t   str=s1;  

	try   
	{   s1.Format(_T("=> Try To Create Mdb %s \r\n"),MdbPathName);
		OutputDebugString(s1);		
		pCatalog->Create(str);   //´´½¨Êý¾Ý¿â   
		return 1;
	}   
	catch(_com_error   &e)   
	{   
		//´íÎó´¦Àí   
		// ::MessageBox(NULL,_T("Êý¾Ý¿â´´½¨Ê§°Ü¡£"), _T("Ìáʾ"),MB_OK | MB_ICONINFORMATION);
		_bstr_t bt=e.Description();
		s2=(LPTSTR)bt;
		s1.Format(_T("=>LOG   Catch e Êý¾Ý¿â %s ´´½¨Ê§°Ü  %s  \r\n %s \r\n"),MdbPathName,e.ErrorMessage(),s2);
//		SysLog(s1);
		//s1.AppendFormat(_T("%ws"),e.Description());
		// ::MessageBox(NULL,s2+e.Description(),e.ErrorMessage(),MB_OK|MB_ICONINFORMATION);

		//TRACE(s1);
		OutputDebugString(s1);

		return -1;
	}
	//CoUninitialize();
	return 0;
}
int OpenMdb(CString MdbPathFileName,_ConnectionPtr & pConnectionPtr)
{
	CString strConnection;
	CString strMsg;
	CString strTemp1, strTemp2,strAddField1£¬strAddField2;
	CString s1;
//	_ConnectionPtr pConnection;


	// strTemp1 = _T("ÐÂ×Ö¶Î1");
	// strTemp2 = _T("ÐÂ×Ö¶Î2");

	strConnection.Format(_T("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s;Persist Security Info=False;"),MdbPathFileName);
	HRESULT hr = pConnectionPtr.CreateInstance( __uuidof(Connection) );
	if ( FAILED(hr) )
	{
		::MessageBox(NULL,_T("ÄúϵͳÖеÄÊý¾Ý¿âÇý¶¯ÓÐÎÊÌâ¡£"), _T("Ìáʾ"),MB_OK | MB_ICONINFORMATION);
		//				SysLog(_T("ÄúϵͳÖеÄÊý¾Ý¿âÇý¶¯ÓÐÎÊÌâ¡£\r\n"));
		return 0;
	}
	try
	{
		pConnectionPtr->ConnectionTimeout = 18;
		pConnectionPtr->ConnectionString =_bstr_t(strConnection);
		pConnectionPtr->Open( _bstr_t(L""), _bstr_t(L""), _bstr_t(L""), -1 );
		//½¨Á¢±í
	}
	catch( _com_error& e)
	{
		_bstr_t bstrError = e.Description();
		strMsg=(LPCTSTR)bstrError;
		// ::MessageBox(NULL,strMsg, e.ErrorMessage(),MB_OK | MB_ICONINFORMATION);
		//	_bstr_t bt=e.Description();
		//	s2=(LPTSTR)bt;
		s1.Format(_T("=>LOG  Êý¾Ý¿â %s Á¬½Óʧ°Ü  %s  \r\n %s \r\n"),MdbPathFileName,e.ErrorMessage(),strMsg);
		//SysLog(s1);
		OutputDebugString(s1);
	//	pConnectionPtr->Close();
		pConnectionPtr.Release();
		return 0;
	}
//	(* pConnectionPtr)=pConnection;
//	pConnection->Close();
//	pConnection.Release();
	return 1;
	//CoUninitialize();
}

int CloseMdb(_ConnectionPtr & pConnection)
{
	pConnection->Close();
	pConnection.Release();
	return 0;
}

int RunSql1( _ConnectionPtr pConnection,CString SqlStr1)//ÔËÐÐûÓзµ»ØÖµµÄSQLÓï¾ä
{
	_variant_t RecordsAffected;
	CString s1,s2;
	CString strMsg;
	try
	{
		pConnection->Execute(_bstr_t(SqlStr1),&RecordsAffected,adCmdText);

	}
	catch(_com_error& e)
	{
		_bstr_t bstrError = e.Description();
		strMsg=(LPCTSTR)bstrError;
		s1.Format(_T("=>LOG  RunSQL1 %s \r\n %s \r\n"),e.ErrorMessage(),strMsg);
		OutputDebugString(s1);
		//  SysLog(s1);
	}
	return 0;
}

int RunSql2( _ConnectionPtr pConnection,CString sqlquery1,_RecordsetPtr & pRecordsetPtr)//ÔËÐÐÓзµ»ØÖµµÄSQLÓï¾ä
{
	_variant_t RecordsAffected;
	CString s1,s2;
	CString strMsg;
	int j;
	int RowCount,FiledCount;
	try
	{
//		pConnection->Execute(_bstr_t(SqlStr1),&RecordsAffected,adCmdText);
		pRecordsetPtr.CreateInstance(__uuidof(Recordset));
		j=pRecordsetPtr->Open(_variant_t(sqlquery1),pConnection.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdText);
		s1.Format(_T("-mdbÊý¾Ý¿â¶ÁÈ¡³É¹¦ readok ״̬Âë %x\r\n"),j);
		//			::SysLog(s1);
		RowCount=pRecordsetPtr->GetRecordCount();

		FieldsPtr fs2;
		CString name2,value2;
//		fs2.CreateInstance(__uuidof(Fields));
		fs2=pRecordsetPtr->GetFields();
		FiledCount=fs2->Count;
		s1.Format(_T("result %x  %d\r\n"),RowCount,FiledCount);
		//				SysLog(s1);
	}
	catch(_com_error& e)
	{
		_bstr_t bstrError = e.Description();
		strMsg=(LPCTSTR)bstrError;
		s1.Format(_T("=>LOG  RunSQL2 %s \r\n %s \r\n"),e.ErrorMessage(),strMsg);
		//  SysLog(s1);
		return -1;
	}
	return RowCount;
}

MyDataBase::MyDataBase()
{
	//µ¼Èë¶ÔÓ¦Dll
	//	::CoInitialize(NULL);	
	IsOpened=0;
}

MyDataBase::~MyDataBase()
{
	if (IsOpened)
	{
		CloseMdb(m_ConnectionPtr);
		//m_ConnectionPtr;
	}
	//CoUninitialize();
}

int MyDataBase::CreateMdb()
{
	return ::CreateMdb(MdbPathName);
	return 1;
}

int MyDataBase::CreateMdbStruct()
{
	if (!IsOpened)
	{
		IsOpened=OpenMdb(MdbPathName,m_ConnectionPtr);
	}
	//½¨Á¢±í
	CString strAddField1= _T("Create Table [Products](SN COUNTER,  ProductCode Text(32), ProductType Text(32),ProductDesc Text(120), ColorNo Integer, Quality Integer, PowerNo Integer,ProductLineNo Integer,Time1 DATETIME)");
	//pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
	int j=RunSql1(m_ConnectionPtr,strAddField1);

	CString strAddField2= _T("Create Table [Results](ID Integer, Names1 Text(50),Value1 Text(120))");
	//pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
//	RunSql1(m_ConnectionPtr,strAddField2);

	CString strAddField3= _T("Create Table [Counter](ID1 Integer, Names1 TEXT(50),vvv VarChar(200))");
	//pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
//	RunSql1(m_ConnectionPtr,strAddField3);

	/*
		//ÐèÒªÔö¼ÓµÄ×ֶμ°Êý¾ÝÀàÐÍ
	strAddField1 = TEXT("ALTER TABLE Covers ADD COLUMN ") + strTemp1 + TEXT(" int");
		pConnection->Execute(_bstr_t(strAddField1),&RecordsAffected,adCmdText);

		//ÐèÒªÔö¼ÓµÄ×ֶμ°Êý¾ÝÀàÐÍ
	CString  strAddField2 = TEXT("ALTER TABLE Covers ADD COLUMN ") + strTemp2 + TEXT(" datetime");
		pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
	*/
		//::MessageBox(NULL,_T("Êý¾Ý¿â¹¹Ôì¸üгɹ¦¡£"), _T("Ìáʾ"),MB_OK | MB_ICONINFORMATION);
//		  SysLog(_T("Êý¾Ý¿â¹¹Ôì¸üгɹ¦¡£\r\n"));
	return j;
	//CoUninitialize();
}

int MyDataBase::AddToOutPutDataBase(int Index, CString ProductCode, CString ProductType, CString ProductDesc, int ColorNo,int Quality,int PowerNo,int ProductLineNo)
{
	CString sqlstr1;
	if (Index==0)
	{
		sqlstr1.Format(_T("Insert Into Products(ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo,time1) \
						  Values('%s','%s','%s','%d','%d','%d','%d',Now())"),
						  ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo);

	}
	else
	{
		sqlstr1.Format(_T("Insert Into Products(SN,ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo,time1) \
						  Values('%d','%s','%s','%s','%d','%d','%d','%d',Now())"),
						  Index,ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo);
	}
	int j;
	j=RunSql1(this->m_ConnectionPtr,sqlstr1);

	//ɾ³ý±íÖмǼ£¬Ö»±£Áô×îеÄ200Ìõ
	sqlstr1.Format(_T("DELETE FROM Products WHERE SN NOT IN (SELECT TOP 200 SN FROM Products ORDER BY SN DESC)"));
	j=RunSql1(this->m_ConnectionPtr,sqlstr1);
	return j;
}

int MyDataBase::AddToMdb(int channel,CString SN)
{
	//	::CoInitialize(NULL);

	CString Sql1;
	Sql1.Format(_T("INSERT INTO COUNTERS(CHANNEL,SN,TIME1) values('%d','%s',TIME())"),channel,SN);
	//  SysLog(Sql1+_T("\r\n"));
	RunSql1(m_ConnectionPtr,Sql1);
	// pConnection->Execute(_bstr_t(Sql1),&RecordsAffected,adCmdText);

	return 1;
	//CoUninitialize();

}

int MyDataBase::CheckMdb1(CString SerialNo)
{
	int j,k;
	CString s1;
	int fieldcount;
	int readok=0;
	_variant_t v2;
	CString namestr,valuestr;
	_ConnectionPtr m_connection2;
	_RecordsetPtr m_recordset2;
	k=m_connection2.CreateInstance(__uuidof(Connection));
//	m_DBConnection.CreateInstance("ADODB.Connection");
	CString db2constr;
	db2constr.Format(_T("Driver={Microsoft Access Driver (*.mdb)};Dbq=%s;CharSet=gbk"),MdbPathName); //Uid=admin;Pwd=123;
		
//	m_connection2->ConnectionString="Provider=MSDAORA.1;password=stream;user id=stream;Data Source=stream.bly.com;Persist Security Info=True";
	m_connection2->ConnectionString=_bstr_t(db2constr);
//	Logtxt(db2constr+"\r\n");
	try 
	{
		j=m_connection2->Open(_T(""),_T(""),_T(""),-1);
//		DB2opened=1;
		s1.Format(_T("-mdbÊý¾Ý¿âÁ¬½Ó³É¹¦ DBOpened ״̬Âë %x %x\r\n"),j,k);
		//::SysLog(s1);
	//	m_DBConnection->Execute("Insert into workers(name) values('zhang')",&v1,0);
		m_recordset2.CreateInstance(__uuidof(Recordset));
		CString sqlquery;
		sqlquery.Format(_T("Select * from COUNTERS Where SN='%s'"),SerialNo);
		j=m_recordset2->Open(_variant_t(sqlquery),m_connection2.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdText);
		readok=1;
		s1.Format(_T("-mdbÊý¾Ý¿â¶ÁÈ¡³É¹¦ readok ״̬Âë %x\r\n"),j);
//			::SysLog(s1);
		namestr.Format(_T("serial"));
		valuestr.Format(_T("'%s'"),SerialNo);
		int rowcount;
		FieldsPtr fs2;
		CString name2,value2;
		fs2.CreateInstance(__uuidof(Fields));
		fs2=m_recordset2->GetFields();
		fieldcount=fs2->Count;
		j=fieldcount;
		k=m_recordset2->GetRecordCount();


		s1.Format(_T("result %x  %d\r\n"),j,k);
//				SysLog(s1);

		FieldPtr field2;
		field2.CreateInstance(__uuidof(Field));
		rowcount=0;
/*
		for (i=0;i<fieldcount;i++)
		{
			field2=fs2->GetItem(_variant_t(long(i)));
	//		v1=field->GetValue();
			name2=(LPCTSTR)field2->GetName();
//				m_list1.InsertColumn(i,name2,LVCFMT_LEFT,80);
		}
*/			
/*
		while (!m_recordset2->adoEOF)
		{
//				m_list1.InsertItem(rowcount,value2,0);
			(*resultstr).Empty();
			for (i=0;i<fieldcount;i++)
			{
				field2=fs2->GetItem(_variant_t(long(i)));
		//		v1=field->GetValue();
				name2=(LPCTSTR)field2->GetName();
				v2=	m_recordset2->GetCollect(_variant_t(name2));

				if (v2.vt==VT_NULL)
				{
					value2.Empty();
					l=0;
				}
*/					/*
				else if (v2.vt==VT_R8||v2.vt==VT_R4)
				{
					double dval1;
					v2.ChangeType(VT_R8);
					dval1=v2.dblVal;
					value2.Format(_T("%f"),dval1);
					l=value2.GetLength();
				}
				*/
/*					else
				{
					v2.ChangeType(VT_BSTR);
					value2=(char *)_bstr_t(v2.bstrVal);
					l=_bstr_t(v2.bstrVal).length();
				}
//						m_list1.SetItemText(rowcount,i,value2);
					if (name2==_T("Serial")) {continue;}
					if (name2==_T("DateTime")) 
				{
					namestr+=_T(",")+name2;
					valuestr+=_T(",to_date('")+value2+_T("','YYYY-mm-dd hh24:MI:ss')");
					continue;
				}
					namestr+=_T(",")+name2;
					valuestr+=_T(",'")+value2+_T("'");
					s1.Format(_T("%s=%s;"),name2,value2);
					(*resultstr)+=s1;
			}
//				if (m_pRecordset->GetadoEof
			s1.Format(_T("%s %d \r\n"),(*resultstr),(*resultstr).GetLength());
//				Logtxt(s1);
			m_recordset2->MoveNext();
			rowcount++;
		}
*/
		m_recordset2->Close();
		m_connection2->Close();	
		return k;
	}
	catch (_com_error e1)
	{	
//		DB2opened=0; 
		s1.Format(_T("-mdbÊý¾Ý¿âÁ¬½Óʧ°Ü ¾ßÌåÐÅÏ¢ÈçÏÂ\r\n  %s\r\n"),(char *)(e1.Description()));
//			::SysLog(s1);
		return -1;
	}
	return 0;

}