CDatabase *CCachedDBConnections::GetConnection(const CString &strConnect, BOOL bAutoOpen)
{
CCacheMap::CPair *pKeyValPair = m_DBCache.PLookup(strConnect);
CDatabase *pDB = NULL;
if (pKeyValPair != NULL)
{
pDB = pKeyValPair->value;
}
if (NULL == pDB)
{
try { pDB = new CDatabase; }
catch (...) { pDB = NULL; }
}
if (pDB != NULL && !pDB->IsOpen() && bAutoOpen)
{
ATLVERIFY(pDB->OpenEx(strConnect,
CDatabase::useCursorLib | CDatabase::noOdbcDialog));
}
if (NULL == pKeyValPair)
{
m_DBCache[strConnect] = pDB;
}
return pDB;
}
bool EVUtil::ODBCCmdSQL( CString szDBName,CString sql )
{
CDatabase db;
CString strConnect;
strConnect.Format(_T("ODBC;DRIVER={MICROSOFT ACCESS DRIVER (*.mdb)};UID=sa;PWD=;DBQ=%s"), szDBName);
db.Open(NULL, FALSE, FALSE, strConnect,FALSE);
if(db.IsOpen())
{
//连接数据库成功
CRecordset rs(&db); //SQL语句
try
{
db.ExecuteSQL(sql);
}
catch (...)
{
db.Close();
return false;
}
db.Close();
return true;
}
return false;
}
bool CGUIDialogMediaFilter::GetMinMax(const std::string &table, const std::string &field, int &min, int &max, const CDatabase::Filter &filter /* = CDatabase::Filter() */)
{
if (table.empty() || field.empty())
return false;
CDatabase *db = NULL;
CDbUrl *dbUrl = NULL;
if (m_mediaType == "movies" || m_mediaType == "tvshows" || m_mediaType == "episodes" || m_mediaType == "musicvideos")
{
CVideoDatabase *videodb = new CVideoDatabase();
if (!videodb->Open())
{
delete videodb;
return false;
}
db = videodb;
dbUrl = new CVideoDbUrl();
}
else if (m_mediaType == "artists" || m_mediaType == "albums" || m_mediaType == "songs")
{
CMusicDatabase *musicdb = new CMusicDatabase();
if (!musicdb->Open())
{
delete musicdb;
return false;
}
db = musicdb;
dbUrl = new CMusicDbUrl();
}
if (db == NULL || !db->IsOpen() || dbUrl == NULL)
{
delete db;
delete dbUrl;
return false;
}
CDatabase::Filter extFilter = filter;
std::string strSQLExtra;
if (!db->BuildSQL(m_dbUrl->ToString(), strSQLExtra, extFilter, strSQLExtra, *dbUrl))
{
delete db;
delete dbUrl;
return false;
}
std::string strSQL = "SELECT %s FROM %s ";
min = static_cast<int>(strtol(db->GetSingleValue(db->PrepareSQL(strSQL, std::string("MIN(" + field + ")").c_str(), table.c_str()) + strSQLExtra).c_str(), NULL, 0));
max = static_cast<int>(strtol(db->GetSingleValue(db->PrepareSQL(strSQL, std::string("MAX(" + field + ")").c_str(), table.c_str()) + strSQLExtra).c_str(), NULL, 0));
db->Close();
delete db;
delete dbUrl;
return true;
}
//定义的向数据库存储新的基本信息的函数
_declspec(dllexport) int WINAPI putmsg(void *res)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState());
whichtab=0;
bmsg bt;
if(res==NULL)
return 1; //error
memset((void*)&bt,0,sizeof(bt));
memcpy((void*)&bt,res,sizeof(bt));
CString str;
crt_conn(str);
CDatabase db;
if(db.IsOpen())
db.Close();
try
{db.Open(NULL,false,false,str);}
catch(CDBException *ep)
{
MessageBox(NULL,ep->m_strError,"from dll",0);
return 1;
}
myrecord mrd(&db);
if(mrd.IsOpen())
mrd.Close();
try
{mrd.Open(AFX_DB_USE_DEFAULT_TYPE,"SELECT * FROM base_msg");}
catch(CDBException *ep)
{
db.Close();
MessageBox(NULL,ep->m_strError,"from dll",0);
return 1;
}
mrd.AddNew();
// memset((void*)&mrd.bsd,0,sizeof(mrd.bsd));
// memcpy((void*)&mrd.bsd,bt,sizeof(mrd.bsd));
/*2015-6-15 不使用整个结构拷贝，而是逐个字段赋值的方法可以避免自动增量字段的人为改变。
这里还有一个微软认可的bug,就是在调试模式时，就会出现一个CRecordset的成员地址改变的傻逼断言，
该断言好像在dbrfx.cpp文件中（line560），通过查看dbrfx.cpp文件发现，这个断言是在#ifdef _DEBUG的判断下
执行的，所以在release版本下不会有错误，但是在调式模式下会出现断言错误。
*/
mrd.bsd.code=bt.code;
mrd.bsd.gmax=bt.gmax;
mrd.bsd.gstd=bt.gstd;
mrd.bsd.gtimes=bt.gtimes;
mrd.bsd.gtype=bt.gtype;
mrd.bsd.gwatch=bt.gwatch;
memcpy((void *)&(mrd.bsd.name),(void*)&(bt.name),60);
mrd.bsd.gcycle=bt.gcycle;
mrd.bsd.gdecay=bt.gdecay;
mrd.Update();
mrd.Close();
db.Close();
return 0;
};
bool COleDBConnectionProp::Connect(CDatabase& database, bool bShowDialog)
{
CWaitCursor cursor;
if(m_strDSN.IsEmpty())
{
ShowError("Empty ODBC Source");
return false;
}
if(m_strLoginName.IsEmpty())
{
ShowError("Empty Login Name");
return false;
}
CString ConnectString =
"DSN=" + m_strDSN + ";";
if(!m_strDatabaseName.IsEmpty())
{
ConnectString +=
"DATABASE=" + m_strDatabaseName + ";";
}
ConnectString +=
"UID=" + m_strLoginName + ";" +
"PWD=" + m_strPassword + ";"
;
try
{
DWORD dwOptions = CDatabase::useCursorLib;
if(!bShowDialog)
dwOptions |= CDatabase::noOdbcDialog;
if(database.IsOpen())
database.Close();
if(!database.OpenEx(ConnectString,dwOptions))
{
return false;
}
}
catch( CDBException* pExc)
{
pExc->ReportError();
pExc->Delete();
return false;
}
return true;
}
//定义的删除数据的函数
_declspec(dllexport) int WINAPI deldata(void *res)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState());
if(res==NULL)
return 1;
whichtab=1;
CString str,conn;
crt_conn(conn);
CDatabase db;
if(db.IsOpen())
db.Close();
try
{db.Open(NULL,false,false,conn);}
catch(CDBException *ep)
{
MessageBox(NULL,ep->m_strError,"from dll-008",0);
return 1;
}
str.Format("%s",(char*)res);
myrecord mrd(&db);
if(mrd.IsOpen())
mrd.Close();
try
{mrd.Open(AFX_DB_USE_DEFAULT_TYPE,str);}
catch(CDBException *ep)
{
db.Close();
MessageBox(NULL,ep->m_strError,"from dll-010",0);
return 1;
}
if(mrd.IsEOF() || !mrd.CanUpdate() || !mrd.CanTransact())
{
MessageBox(NULL,"delete result error!","from dll-009",0);
mrd.Close();
db.Close();
return 1;
}
/* mrd.MoveFirst();
while(!mrd.IsEOF())
{
mrd.Delete();
mrd.MoveNext();
}
*/
mrd.Delete();
db.CommitTrans();
mrd.Close();
db.Close();
return 0;
};
//定义的从数据库读出的基本信息的函数
_declspec(dllexport) int WINAPI getmsg(void *res)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState());
// if(res==NULL)
// return 1;
whichtab=0;
dlmsg.RemoveAll();
CString str,s1;
crt_conn(str);
CDatabase db;
if(db.IsOpen())
db.Close();
try
{db.Open(NULL,false,false,str);}
catch(CDBException *ep)
{
MessageBox(NULL,ep->m_strError,"from dll",0);
return 1;
}
s1.Format("SELECT * FROM base_msg");
myrecord mrd(&db);
if(mrd.IsOpen())
mrd.Close();
try
{mrd.Open(AFX_DB_USE_DEFAULT_TYPE,s1);}
catch(CDBException *ep)
{
db.Close();
MessageBox(NULL,ep->m_strError,"from dll-001",0);
return 1;
}
if(mrd.IsEOF())
{
mrd.Close();
db.Close();
return 0;
}
mrd.MoveFirst();
while(!mrd.IsEOF())
{
dlmsg.Add(mrd.bsd);
mrd.MoveNext();
}
mrd.Close();
db.Close();
return 0;
};
/*2015-6-21修改，修改为按查询条件取得记录，条件查询语句直接在函数外构造，并直接通过参数传入
在本函数直接使用。*/
_declspec(dllexport) int WINAPI get_dt(void *res)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState());
if(res==NULL)
return 1;
whichtab=3;
dldt.RemoveAll();
CString str;
crt_conn(str);
CDatabase db;
if(db.IsOpen())
db.Close();
try
{db.Open(NULL,false,false,str);}
catch(CDBException *ep)
{
MessageBox(NULL,ep->m_strError,"from dll-1007",0);
return 1;
}
str.Format("%s",(char*)res,strlen((char*)res));
myrecord mrd(&db);
if(mrd.IsOpen())
mrd.Close();
try
{mrd.Open(AFX_DB_USE_DEFAULT_TYPE,str,0);}
catch(CDBException *ep)
{
db.Close();
MessageBox(NULL,ep->m_strError,"from dll-1008",0);
return 1;
}
if(mrd.IsEOF())
{
mrd.Close();
db.Close();
return 0;
}
mrd.MoveFirst();
while(!mrd.IsEOF())
{
dldt.Add(mrd.dt);
mrd.MoveNext();
}
mrd.Close();
db.Close();
return 0;
};
//定义的表格4 data_tab的存储函数，该函数也用于记录的批量处理
_declspec(dllexport) int WINAPI put_dt(void *res)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState());
if(res==NULL)
return 1;
whichtab=3;
CArray<data_tab,data_tab&> *ca;
ca=(CArray<data_tab,data_tab&> *)res;
CString str;
int i,j;
crt_conn(str);
CDatabase db;
if(db.IsOpen())
db.Close();
try
{db.Open(NULL,false,false,str);}
catch(CDBException *ep)
{
MessageBox(NULL,ep->m_strError,"from dll-1003",0);
return 1;
}
myrecord mrd(&db);
if(mrd.IsOpen())
mrd.Close();
try
{mrd.Open(AFX_DB_USE_DEFAULT_TYPE,"SELECT * FROM data_tab",0);}
catch(CDBException *ep)
{
db.Close();
MessageBox(NULL,ep->m_strError,"from dll-1004",0);
return 1;
}
i=ca->GetCount();
for(j=0;j<i;j++)
{
mrd.AddNew();
memset((void*)&(mrd.dt),0,sizeof(mrd.dt));
mrd.dt=ca->GetAt(j);
mrd.Update();
}
mrd.Close();
db.Close();
return 0;
};
int CMyDatabase::CloseConnection( CDatabase &m_Database)
{
try
{
if ( m_Database.IsOpen() )
m_Database.Close();
}
catch( CDBException *e)
{
char Message[255] = {0};
sprintf(Message,"CMyDatabase::CloseConnection(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
return 0;
}
void CCachedDBConnections::RemoveConnection(const CString &strConnect)
{
CCacheMap::CPair *pKeyValPair = m_DBCache.PLookup(strConnect);
if (pKeyValPair != NULL)
{
CDatabase *pDB = NULL;
std::swap(pDB, pKeyValPair->value);
if (pDB != NULL)
{
if (pDB->IsOpen())
{
pDB->Close();
}
delete pDB;
pDB = NULL;
}
m_DBCache.RemoveKey(strConnect);
}
}
/*2015-6-21修改，该函数无需批量处理，所以传入的参数不是队列而是一个结构指针*/
_declspec(dllexport) int WINAPI put_ct(void *res)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState());
if(res==NULL)
return 1;
whichtab=2;
CString str;
crt_conn(str);
CDatabase db;
if(db.IsOpen())
db.Close();
try
{db.Open(NULL,false,false,str);}
catch(CDBException *ep)
{
MessageBox(NULL,ep->m_strError,"from dll-1001",0);
return 1;
}
myrecord mrd(&db);
if(mrd.IsOpen())
mrd.Close();
try
{mrd.Open(AFX_DB_USE_DEFAULT_TYPE,"SELECT * FROM code_tab");}
catch(CDBException *ep)
{
db.Close();
MessageBox(NULL,ep->m_strError,"from dll-1002",0);
return 1;
}
/* mrd.AddNew();
memset((void*)&(mrd.ct),0,sizeof(mrd.ct));
memcpy((void*)&(mrd.ct),res,sizeof(mrd.ct)); */
mrd.MoveFirst();
mrd.Edit();
memcpy((void*)&(mrd.ct),res,sizeof(mrd.ct));
mrd.Update();
mrd.Close();
db.Close();
return 0;
};
BOOL CServerSocet::CloseSocket(CString* sMessage)
{
if(SocWin != NULL)
if(SocWin->m_hWnd != NULL)
SocWin->DestroyWindow();
if (sokClientSocket!= NULL)
{
int i;
for(i=0;i<iMaxCol;i++)
{
if(sokClientSocket[i]!=NULL)
{
CDatabase* dBase;
if(sokClientSocket[i]->dBase != NULL)
{
try
{
dBase = sokClientSocket[i]->dBase;
if(dBase!=NULL)
{
if(dBase->IsOpen())
{
dBase->Close();
}
}
}
catch(...)
{
}
delete(dBase);
sokClientSocket[i]->dBase = NULL;
}
if(sokClientSocket[i]->hThread != NULL)
{
TerminateThread(sokClientSocket[i]->hThread,0);
}
sokClientSocket[i]->hThread = NULL;
delete(sokClientSocket[i]);
sokClientSocket[i] = NULL;
}
}
delete(sokClientSocket);
sokClientSocket = NULL;
}
CString S;
S.Format(_T("Закрытие сокета"));
if(sMessage != NULL)
{
*sMessage = *sMessage + S;
}
if(bCreateSocket)
closesocket(sokServerSocket);
bCreateSocket = FALSE;
if(sMessage != NULL)
*sMessage = *sMessage + _T("\tвыполненно\n");
bStatus = FALSE;
return TRUE;
}
UINT CNav_ExportCustomsDlg::ExportData(LPVOID p)
{
HRESULT hRes;
Excel::_ApplicationPtr appExcel;
hRes = appExcel.CreateInstance( _T("Excel.Application"));
CNav_ExportCustomsDlg *Dialog;
Dialog = (CNav_ExportCustomsDlg*)p;
if(Dialog != NULL)
{
CString sStart, sEnd;
Dialog->m_EndDate.GetWindowText(sEnd);
Dialog->m_StartDate.GetWindowText(sStart);
CString sGTD;
Dialog->m_EdGTDNUMBER.GetWindowText(sGTD);
COleDateTime datStart,datEnd, cDate;
datStart.ParseDateTime(sStart);
datEnd.ParseDateTime(sEnd);
CString sConnect;
CString sServer, sDatabase;
sServer = sReadFromIni(_T("DB"),_T("SERVER"),_T("svbyminssq3"));
//sWriteToIni(_T("DB"),_T("SERVER"),sServer);
sDatabase = sReadFromIni(_T("DB"),_T("DATABASE"),_T("SHATE-M-8"));
//sWriteToIni(_T("DB"),_T("DATABASE"),sDatabase);
Excel::WorkbooksPtr ExcelBooks;
Excel::_WorkbookPtr ExcelBook;
Excel::_WorksheetPtr ExcelSheet;
Excel::RangePtr range;
VARIANT bTRUE;
bTRUE.vt = 11;
bTRUE.boolVal = TRUE;
appExcel->Visible[0] = FALSE;
ExcelBook= appExcel->Workbooks->Add();
ExcelSheet = ExcelBook->Worksheets->Item[1];
sConnect.Format(_T("DRIVER=SQL Server;SERVER=%s;UID=;WSID=%s;Trusted_Connection=Yes;DATABASE=%s;LANGUAGE=русский"),sServer,GetWinUserName(),sDatabase);
CDatabase* dBase;
dBase = NULL;
try
{
dBase = new(CDatabase);
dBase->SetQueryTimeout(600);
dBase->OpenEx(sConnect,CDatabase::noOdbcDialog);
sConnect.Format(_T("EXEC [sp_setapprole] '%s', '%s', 'none', 0, 0"),_T("$ndo$shadow"),_T("FF5EC4E40F67BD4EDF3D04F8B84364DAD0"));
dBase->ExecuteSQL(sConnect);
}
catch(CDBException *exsept)
{
appExcel->Visible[0] = TRUE;
Dialog->m_stState.SetWindowTextW(exsept->m_strError);
exsept->Delete();
if(dBase != NULL)
{
if(dBase->IsOpen())
{
dBase->Close();
}
delete(dBase);
}
dBase = NULL;
Dialog->m_BtOK.ShowWindow(1);
Dialog->SecondThread = NULL;
return 0;
}
CString sSQL;
try
{
CRecordset Query(dBase);
int iField;
CDBVariant dbValue;
Dialog->m_stState.SetWindowTextW(_T("Формирование"));
sGTD = sGTD + _T("'");
sSQL = _T("select distinct SIH.[Posting Date],(SIL.[TTN Series]+SIL.[TTN Number]) AS TTN,SIL.[Item No_ 2], SIH.[Bill-to Name] ");
sSQL = sSQL + _T(" from [")+sDatabase;
sSQL = sSQL + _T("$Sales Invoice Header] as SIH join [");
sSQL = sSQL + sDatabase;
sSQL = sSQL + _T("$Sales Invoice Line] as SIL on SIL.[Document No_] = SIH.[No_] and SIL.[No_] is not null and SIL.[No_] <> '' and SIL.[TTN Series] <> '' and SIL.[TTN Number] <> '' join [");
sSQL = sSQL + sDatabase;
sSQL = sSQL + _T("$Custom Declaration Relation] as CDR on CDR.[Item No_] = SIL.[No_] and CDR.[Document Type] = 5 and CDR.[CD No_] = '") + sGTD;
sSQL = sSQL + _T(" where [Sales Process Type Code] = 'Б/Н_ДОСТАВКА' and Left(CONVERT ( nchar , SIH.[Posting Date], 112),8) >= '")+ datStart.Format(_T("%Y%m%d"))+_T("'");
sSQL = sSQL + _T(" and Left(CONVERT ( nchar , SIH.[Posting Date], 112),8) <= '")+ datEnd.Format(_T("%Y%m%d"))+_T("'");
//sSQL = sSQL + _T(" and CDR.[CD No_] = '") + sGTD;
CString sDat;
int iRow;
iRow = 1;
sDat = _T("Дата учета продажи");
ExcelSheet->Cells->Item[iRow,2] = sDat.AllocSysString();
sDat = _T("Номер ТТН, ТН");
ExcelSheet->Cells->Item[iRow,3] = sDat.AllocSysString();
sDat = _T("Код проданного товара (код товара2)");
ExcelSheet->Cells->Item[iRow,4] = sDat.AllocSysString();
sDat = _T("Клиент");
ExcelSheet->Cells->Item[iRow,5] = sDat.AllocSysString();
Query.Open(CRecordset::snapshot,sSQL, CRecordset::readOnly);
while(!Query.IsEOF())
{
iRow++;
if((iRow -2) % 100 == 0)
{
sSQL.Format(_T("Обработанно %d"),iRow-2);
Dialog->m_stState.SetWindowTextW(sSQL);
}
iField = 0;
Query.GetFieldValue(iField, dbValue);
sDat = GetValue(&dbValue);
ExcelSheet->Cells->Item[iRow,2] = sDat.AllocSysString();
iField = 1;
Query.GetFieldValue(iField, dbValue);
sDat = GetValue(&dbValue);
ExcelSheet->Cells->Item[iRow,3] = sDat.AllocSysString();
iField = 2;
Query.GetFieldValue(iField, dbValue);
sDat = GetValue(&dbValue);
ExcelSheet->Cells->Item[iRow,4] = sDat.AllocSysString();
iField = 3;
Query.GetFieldValue(iField, dbValue);
sDat = GetValue(&dbValue);
ExcelSheet->Cells->Item[iRow,5] = sDat.AllocSysString();
Query.MoveNext();
}
Query.Close();
}
catch(CDBException *exsept)
{
/*appExcel->Visible[0] = TRUE;
appExcel = NULL;*/
appExcel->Visible[0] = TRUE;
Dialog->m_stState.SetWindowTextW(exsept->m_strError);
Dialog->m_EdError.SetWindowTextW(sSQL);
exsept->Delete();
if(dBase != NULL)
{
if(dBase->IsOpen())
{
dBase->Close();
}
delete(dBase);
}
Dialog->m_BtOK.ShowWindow(1);
Dialog->SecondThread = NULL;
dBase = NULL;
return 0;
}
Dialog->m_stState.SetWindowTextW(_T("Выполненно"));
Dialog->m_BtOK.ShowWindow(1);
}
appExcel->Visible[0] = TRUE;
Dialog->SecondThread = NULL;
return 1;
}
