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 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;
}
int CMyDatabase::OpenConnection( CDatabase &m_Database,int dbms,int Index )
{
char drivername[255] = {0};
int result = -1;
StationIndex = Index;
try
{
m_Database.SetLoginTimeout(2);
m_Database.OpenEx(m_strConnect, CDatabase::noOdbcDialog);
}
catch( CDBException *e)
{
result = -1;
char Message[255] = {0};
sprintf(Message,"CMyDatabase::OpenConnection(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
return result;
}
int CMyDatabase::OpenConnection( CDatabase &m_Database )
{
char drivername[255] = {0};
int result = 0;
char Message[255] = {0};
try
{
m_Database.SetLoginTimeout(2);
m_Database.OpenEx(m_strConnect, CDatabase::noOdbcDialog);
result = 1;
}
catch( CDBException *e)
{
sprintf(Message,"CMyDatabase::OpenConnection(%s)",e->m_strError);
printf( "%s\n",Message);
WriteLog(Message,"");
e->Delete();
}
return result;
}
BOOL C51JobWebPost::CreatExcelDb(CString dbpath,CString tableName,CString &strError)
{
if (_access(dbpath,0)==0) //存在
{
strError = "数据库已存在";
return TRUE;
}
CDatabase database;
CString sDriver;
CString sExcelFile;
CString sSql;
//检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)"
sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
// 没有发现Excel驱动
strError = "请先安装Excel软件才能使用导出功能";
return FALSE;
}
// 创建进行存取的字符串
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver, sExcelFile, dbpath);
// 创建数据库 (既Excel表格文件)
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
CString strTemp;
strTemp.Format("账号 TEXT,密码 TEXT,推荐人 TEXT,邮箱 TEXT,网址 TEXT,时间 TEXT");
// 创建表结构
sSql = "CREATE TABLE " + tableName + " ( " + strTemp + " ) ";
database.ExecuteSQL(sSql);
return TRUE;
}
else
{
strError = "创建EXCEL数据库失败";
return FALSE;
}
}
//////////////////////////////////////////////////////////////////////////////
// 函数：void CListCtrlToExcel(CListCtrl* pList, CString strTitle)
// 参数：
// pList 需要导出的List控件指针
// strTitle 导出的数据表标题
// 说明:
// 导出CListCtrl控件的全部数据到Excel文件。Excel文件名由用户通过“另存为”
// 对话框输入指定。创建名为strTitle的工作表，将List控件内的所有数据（包括
// 列名和数据项）以文本的形式保存到Excel工作表中。保持行列关系。
//
//////////////////////////////////////////////////////////////////////////////
void CListCtrlToExcel(CListCtrl* pList, CString strTitle)
{
CString warningStr;
if(pList->GetItemCount()>0)
{
CDatabase database;
CString sDriver;
CString sExcelFile;
CString sSql;
CString tableName = strTitle;
//检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)"
sDriver = GetExcelDriver();
if(sDriver.IsEmpty())
{
//没有发现Excel驱动
AfxMessageBox("没有安装Excel!\n请先安装Excel软件才能使用导出功能!");
return;
}
//默认文件名
if(!GetDefaultXlsFileName(sExcelFile))
return;
//创建进行存取的字符串
sSql.Format("DRIVER={%s};DSN='';FIRSTORWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver,sExcelFile,sExcelFile);
//创建数据库(即Excel表格文件)
if(database.OpenEx(sSql,CDatabase::noOdbcDialog))
{
//创建表结构
int i;
LVCOLUMN columnData;
CString columnName;
int columnNum=0;
CString strH,strV;
sSql="";
strH="";
columnData.mask = LVCF_TEXT;
columnData.cchTextMax = 100;
columnData.pszText = columnName.GetBuffer(100);
for(i=0;pList->GetColumn(i,&columnData);i++)
{
if(i!=0)
{
sSql=sSql+", ";
strH=strH+", ";
}
sSql = sSql + " " + columnData.pszText + " TEXT";
strH = strH + " " + columnData.pszText + " ";
}
columnName.ReleaseBuffer();
columnNum = i;
sSql = "CREATE TABLE " + tableName + " ( " + sSql + " ) ";
database.ExecuteSQL(sSql);
//插入数据项
int nItemIndex;
for(nItemIndex=0;nItemIndex<pList->GetItemCount();nItemIndex++)
{
strV = "";
for(i=0;i<columnNum;i++)
{
if(i!=0)
{
strV = strV + ", ";
}
strV = strV + " '" + pList->GetItemText(nItemIndex,i) + "' ";
}
sSql = "INSERT INTO " + tableName + " (" + strH + ")" + "VALUES(" + strV + ")";
database.ExecuteSQL(sSql);
}
}
//关闭数据库
database.Close();
warningStr.Format("导出文件保存于%s中!",sExcelFile);
AfxMessageBox(warningStr);
ShellExecute(NULL,"open",sExcelFile,NULL,NULL,SW_SHOW); //最后才显示文件名为strFile的数据表
}
else
{
//没有数据
MessageBox(NULL,"没有数据，不能导出!","提示",MB_OK|MB_ICONWARNING|MB_TOPMOST);
}
}
void CList2Excel::ExportListToExcel(CListCtrl* pList, CString strTitle,CString strExcFile)
{
CString warningStr;
if (pList->GetItemCount ()>0)
{
CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel安装驱动
CString sExcelFile = strExcFile; // 要建立的Excel文件
CString sSql;
CString strField = "",strField2 = "";
int m;
int n = pList->GetItemCount();
// 创建进行存取的字符串
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver, sExcelFile, sExcelFile);
// 创建数据库 (既Excel表格文件)
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
// 创建表结构
int i;
LVCOLUMN columnData;
CString columnName;
int columnNum = 0;
CString strH;
CString strV;
sSql = "";
strH = "";
columnData.mask = LVCF_TEXT;
columnData.cchTextMax =256;
columnData.pszText = columnName.GetBuffer (256);
for(i=0;pList->GetColumn(i,&columnData);i++)
{
strField = strField + "[" + columnData.pszText +"]" + " char(255), ";
strField2 = strField2 + "[" + columnData.pszText +"], ";
}
columnName.ReleaseBuffer ();
m = i;
// 创建表结构
sSql.Format("CREATE TABLE [%s] (",strTitle);
strField.Delete(strField.GetLength()-2, 2);
strField += ")";
sSql += strField;
database.ExecuteSQL(sSql);
strField2.Delete(strField2.GetLength()-2, 2);
//Add By HuangXiao Ke；把导出时进度信息显示出来，耦合性太大，如果别的程序要用这个累，把这些无关代码去掉
CD_Progress* pDlg = (CD_Progress*)g_dlgQueryManager.GetDialog(IDD_DIALOG_PROGRESS);
pDlg->ShowWindow(SW_SHOW);
pDlg->m_cProgress.SetRange(0,n);
pDlg->m_cProgress.SetPos(0);
CString str;
// 插入数值
for (i=0; i<n; i++)
{
pDlg->m_cProgress.SetPos(i);
sSql="";
sSql.Format("INSERT INTO [%s] (",strTitle);
sSql += strField2;
sSql += ") VALUES (";
for (int j = 0; j <m ; j++)
{
str = "";
str.Format("'%s', ", pList->GetItemText(i,j));
sSql = sSql + str;
}
sSql.Delete(sSql.GetLength()-2, 2);
sSql += ")";
//OutputDebugString(sSql);
database.ExecuteSQL(sSql);
}
pDlg->ShowWindow(SW_HIDE);
}
// 关闭数据库
database.Close();
warningStr.Format("导出文件保存于%s.xls!",sExcelFile);
AfxMessageBox(warningStr);
}
}
void CDlgBDSelectRezTL::OnBnClickedButtonExportExcel()
{
CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // exactly the same name as in the ODBC-Manager
CString sExcelFile = "Тележки1067.xls"; // Filename and path for the file to be created
if( iNewProc ) sExcelFile = "Тележки1067arx.xls";
CString sSql;
CString strColListInsert = "";
CString strColListCreate = "";
int iColCount = 0;
LVITEM Item;
HDITEM hItem;
enum { sizeOfBuffer = 256 };
TCHAR lpBuffer[sizeOfBuffer];
bool fFound = false;
hItem.mask = HDI_TEXT;
hItem.pszText = lpBuffer;
hItem.cchTextMax = sizeOfBuffer;
CHeaderCtrl *hcHeader = m_List.GetHeaderCtrl();
iColCount = hcHeader->GetItemCount();
CString strTitle = "";
for( int i = 0; i < iColCount; i++ )
{
if( i )
{
strColListInsert += ", ";
strColListCreate += ", ";
}
if( hcHeader->GetItem(i, &hItem) )
{
strTitle = hItem.pszText;
strTitle.Remove('.');
strColListInsert = strColListInsert + "[" + strTitle + "]";
strColListCreate = strColListCreate + "[" + strTitle + "] TEXT";
}
}
// Build the creation string for access without DSN
DeleteFile(".\\" + sExcelFile);
if( !CopyFile(".\\html\\" + sExcelFile, ".\\" + sExcelFile, 1) )
{
// Создам шаблон
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\".\\html\\%s\";DBQ=.\\html\\%s",
sDriver, sExcelFile, sExcelFile);
database.OpenEx(sSql,CDatabase::noOdbcDialog);
sSql = "CREATE TABLE [Тележки 1067] (" + strColListCreate + ")";
database.ExecuteSQL(sSql);
database.Close();
CopyFile(".\\html\\" + sExcelFile, ".\\" + sExcelFile, 1);
}
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\".\\%s\";DBQ=%s",
sDriver, sExcelFile, sExcelFile);
// Create the database (i.e. Excel sheet)
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
/*
TRY
{
sSql = "CREATE TABLE [Тележки 1067] (" + strColListCreate + ")";
database.ExecuteSQL(sSql);
}
CATCH_ALL(e)
{
}
END_CATCH_ALL
*/
for( int i = 0; i < m_List.GetItemCount(); i++ )
{
CString strLine = "";
for( int j = 0; j < iColCount; j++ )
{
if( j ) strLine += ", ";
strLine += "'" + m_List.GetItemText(i, j) + "'";
}
//sSql = "INSERT INTO [Тележки 1067] (" + strColListInsert + ") VALUES ( " + strLine + " )";
sSql = "INSERT INTO [Тележки 1067] VALUES ( " + strLine + " )";
database.ExecuteSQL(sSql);
}
}
database.Close();
CString str = "excel.exe .\\" + sExcelFile;
ShellExecute(NULL, "open", "excel.exe",
sExcelFile,
NULL, SW_SHOWNORMAL);
}
///////////////////////////////////////////////////////////////////////////////
// void GetExcelDriver(CListCtrl* pList, CString strTitle)
// 参数：
// pList 需要导出的List控件指针
// strTitle 导出的数据表标题
// 说明:
// 导出CListCtrl控件的全部数据到Excel文件。Excel文件名由用户通过“另存为”
// 对话框输入指定。创建名为strTitle的工作表，将List控件内的所有数据（包括
// 列名和数据项）以文本的形式保存到Excel工作表中。保持行列关系。
//
///////////////////////////////////////////////////////////////////////////////
void CFunc::ExportListToExcel(CListCtrl* pList[], CString strTitle[], int number)
{
CString warningStr;
bool l_bStart = true;
for (int index = 0; index < number; index++)//判断交易分析是否完成
{
if (pList[index]->GetItemCount () == 0 || pList[index]->GetItemCount () < 0)
{
l_bStart = false;
}
}
if (l_bStart) //遍历每一行的循环
{
CDatabase database;
CString sDriver;
CString sExcelFile;
CString sSql;
CString tableName;
// 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)"
sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
// 没有发现Excel驱动
AfxMessageBox("You've not installed Excel!\nPlease install Excel first!");
return;
}
///默认文件名
if (!GetDefaultXlsFileName(sExcelFile))
return;
// 创建进行存取的字符串
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver, sExcelFile, sExcelFile);
database.OpenEx(sSql,CDatabase::noOdbcDialog);
// 创建数据库 (既Excel表格文件)
for (int tableIndex = 0; tableIndex < number; tableIndex++)//创建多个Excel表的循环
{
tableName = strTitle[tableIndex];
// 创建表结构
int i;
LVCOLUMN columnData;
CString columnName;
int columnNum = 0;
CString strH;
CString strV;
sSql = "";
strH = "";
columnData.mask = LVCF_TEXT;
columnData.cchTextMax =100;
columnData.pszText = columnName.GetBuffer (100);
for(i=1;pList[tableIndex]->GetColumn(i,&columnData);i++)//第一列没有数据应跳过
{
if (i!=1)
{
sSql = sSql + ", " ;
strH = strH + ", " ;
}
sSql = sSql + " " + columnData.pszText +" TEXT";//数据暂时全部是以文本类型写入Excel
strH = strH + " " + columnData.pszText +" ";
}
columnName.ReleaseBuffer ();
columnNum = i;
sSql = "CREATE TABLE " + tableName + " ( " + sSql + " ) ";
database.ExecuteSQL(sSql);
// 插入数据项
int nItemIndex;
for (nItemIndex=0;nItemIndex < pList[tableIndex]->GetItemCount(); nItemIndex++)
{
strV = "";
for(i=1;i<columnNum;i++)//第一列没有数据应跳过
{
if (i!=1)
{
strV = strV + ", " ;
}
strV = strV + " '" + pList[tableIndex]->GetItemText(nItemIndex,i) +"' ";
}
sSql = "INSERT INTO "+ tableName
+" ("+ strH + ")"
+" VALUES("+ strV + ")";
database.ExecuteSQL(sSql);
sSql.Empty();
}
}//end for创建多个Excel表的循环
// 关闭数据库
database.Close();
warningStr.Format("Excel file is saved as %s!",sExcelFile);
AfxMessageBox(warningStr);
}//end for遍历每一行的循环
}
BOOL CEdRptExport::ExportDBData(ofstream &htm, ofstream &txt)
{
CDatabase db;
BOOL bRet = FALSE;
CString csHeadFile = m_pDoc->m_szHeadRight;
if (m_pDoc->m_szODBCLink.IsEmpty())
{
ShowMessage(m_pDoc->GetHWnd(), "没有给定数据库指针，也没有指定链接字！");
return FALSE;
}
if (!db.OpenEx(m_pDoc->m_szODBCLink, CDatabase::openReadOnly | CDatabase::noOdbcDialog))
{
ShowMessage(m_pDoc->GetHWnd(), "无法打开数据库连接 %s", m_pDoc->m_szODBCLink);
return FALSE;
}
try
{
UINT nRecCount = m_pDoc->GetRecordCount(db);
CString szSQL = m_pDoc->m_szSQL;
if (nRecCount > (int)m_pDoc->m_nMaxRecordNum)
{
CString str;
str.Format(_T("共有 %d 条记录，但模版设计最大记录数为 %d，\n是否只 %d 条记录？\n"
"注意：如何选否，会给数据库很大压力，甚至取消操作！"),
nRecCount, m_pDoc->m_nMaxRecordNum, m_pDoc->m_nMaxRecordNum);
UINT ulSelect = IDNO;
if (m_pDoc->GetHWnd())
ulSelect = MessageBox(m_pDoc->GetHWnd(), str, _T("EdFc"), MB_YESNOCANCEL);
if (ulSelect == IDNO)
;
else if (ulSelect == IDCANCEL)
return FALSE;
else // IDYES
{
csHeadFile.Format(_T(" %d条记录未导出"), nRecCount - m_pDoc->m_nMaxRecordNum);
nRecCount = m_pDoc->m_nMaxRecordNum;
CString csDBDrv = getDBDriver(m_pDoc->m_szODBCLink);
csDBDrv.MakeLower();
if (csDBDrv.Find("oracle") >= 0)
{
CString szTmp = szSQL;
szTmp.MakeLower();
int nFind = szTmp.Find(" where ");
if (nFind > 0)
{
szTmp.Format(" ROWNUM <= %d AND ", m_pDoc->m_nMaxRecordNum);
szSQL.Insert(nFind + (int)_tcslen(" where "), (LPCSTR)szTmp);
}
else
{
szTmp.Format(" WHERE ROWNUM <= %d ", m_pDoc->m_nMaxRecordNum);
szSQL += szTmp;
}
}
else if (csDBDrv.Find("mysql") >= 0)
{
CString szTmp;
szTmp.Format(" Limit 0, %d ", m_pDoc->m_nMaxRecordNum);
szSQL += szTmp;
}
// else if (csDBDrv.IsEmpty())
// ShowMessage(m_pDoc->GetHWnd(), "报表打印组件无法判断该数据库的类型。");
else
{
CString str;
str.Format(_T("[ %s ] 数据库无法使用修改SQL的方法限制记录个数，而打开大数据集可能会出现异常\n，是否先修改查询条件，再进行打印？"), csDBDrv);
if (m_pDoc->GetHWnd() && MessageBox(m_pDoc->GetHWnd(), str,_T("BSRpter"), MB_YESNO) == IDYES)
return FALSE;
}
}
}
CRecordset rc(&db);
rc.Open(CRecordset::forwardOnly, szSQL);
UINT nFieldCount = rc.GetODBCFieldCount();
CString szTitle = m_pDoc->m_szTitle;
TxtToHtml(szTitle);
CString szHeadRight = m_pDoc->m_szHeadRight;
TxtToHtml(szHeadRight);
htm << "<html><head><title>"
<< (LPCTSTR)szTitle
<< "</title></head>"
<< endl;
htm << "<body>" << endl
<< "<table width=100%>" << endl
<< "<tr>" << endl
<< "\t<td width=20%> </td>" << endl
<< "\t<td width=60% align=center><font size=+3>"
<< (LPCTSTR)szTitle
<< "</font></td>" << endl
<< "\t<td width=20% align=right>"
<< (LPCTSTR)csHeadFile
<< "</td>" << endl
<< "</tr>" << endl
<< "</table>" << endl;
htm << "<table border=1 width=100%>" << endl
<< "<tr>" << endl;
int nWidth = 0;
for (UINT col = 0; col < nFieldCount; col++)
nWidth += m_pDoc->m_ColFmt[col].width;
CString str;
for (col = 0; col < nFieldCount; col++)
{
str = m_pDoc->m_ColFmt[col].name;
if (str.IsEmpty())
{
CODBCFieldInfo fi;
rc.GetODBCFieldInfo(col, fi);
str = fi.m_strName;
}
txt << "\"" << (LPCTSTR)str << "\"";
TxtToHtml(str);
htm << "\t<td width=" << m_pDoc->m_ColFmt[col].width * 100 / nWidth
<< "% align=center>" << (LPCTSTR)str << "</td>" << endl;
if (col == nFieldCount - 1)
{
txt << endl;
htm << "</tr>" << endl;
}
else
txt << ',';
}
if (m_pDoc->GetHWnd())
::SendMessage(m_pDoc->GetHWnd(), WM_CREATEPROGRESS, nRecCount, 0);
CDBVariant varValue;
CString strHtml;
UINT nCount = 0;
for(UINT row = 0; row < nRecCount; row ++)
{
htm << "<tr>" << endl;
for (UINT col = 0; col < nFieldCount; col ++)
{
strHtml = _T("");
rc.GetFieldValue(col, varValue);
if (varValue.m_dwType == DBVT_BINARY)
{
str = GetPicFile(varValue.m_pbinary, nCount++);
if (str.IsEmpty()) //error
continue;
m_ZipArc.AddFileToZip(str);
DeleteFile(str);
str = str.Right(str.GetLength() - str.ReverseFind('\\') - 1);
strHtml.Format("<A href=%s>%s</A>", str, str);
}
else
str = m_pDoc->GetVarStr(&varValue, m_pDoc->m_ColFmt[col].format);
varValue.Clear();
txt << "\"" << (LPCTSTR)str << "\"";
TxtToHtml(str);
if (strHtml.IsEmpty())
strHtml = str;
htm << "\t<td align=center>" << (LPCTSTR)strHtml << "</td>" << endl;
if (col == nFieldCount - 1)
{
txt << endl;
htm << "</tr>" << endl;
}
else
txt << ',';
}
if (m_pDoc->GetHWnd())
::SendMessage(m_pDoc->GetHWnd(), WM_STEPPROGRESS, nRecCount, row);
rc.MoveNext();
}
rc.Close();
CString szFootLeft = m_pDoc->m_szFootLeft;
TxtToHtml(szFootLeft);
CString szFootRight = m_pDoc->m_szFootRight;
if (szFootRight.IsEmpty())
szFootRight = CTime::GetCurrentTime().Format("%m/%d/%y %H:%M:%S");
TxtToHtml(szFootRight);
htm << "</table>" << endl
<< "<p>" << endl
<< "<table width=100%>" << endl
<< "<tr>" << endl
<< "\t<td width=50% align=left>"
<< (LPCTSTR)szFootLeft
<< "</td>" << endl
<< "\t<td width=50% align=right>"
<< (LPCTSTR)szFootRight
<< "</td>" << endl
<< "</table>" << endl
<< "<p><hr>" << endl
<< "<b>FmtFile Infomation</b><br>" << endl
<< "<ul>" << endl
<< "\t<li>ODBC: " << (LPCTSTR)m_pDoc->m_szODBCLink << "<br>" << endl
<< "\t<li>SQL : " << (LPCTSTR)m_pDoc->m_szSQL
<< "</ul>" << endl
<< "</body>" << endl
<< "</html>" << endl;
if (m_pDoc->GetHWnd())
::SendMessage(m_pDoc->GetHWnd(), WM_DESTROYPROGRESS, 0, 0);
bRet = TRUE;
}
catch(CException *edb)
{
static TCHAR lpszError[MAX_PATH];
CString csError;
if (edb->GetErrorMessage(lpszError, MAX_PATH))
csError = lpszError;
if (csError.GetLength() <= 0)
csError = _T("无法获得数据！");
ShowMessage(m_pDoc->GetHWnd(), csError);
edb->Delete();
bRet = FALSE;
}
catch(...)
{
ShowMessage(m_pDoc->GetHWnd(), _T("无法获得数据！"));
bRet = FALSE;
}
db.Close();
return bRet;
}
BOOL CEdRptDoc::GetDataFromDB()
{
CDatabase db;
BOOL bRet = FALSE;
try
{
if (m_szODBCLink.IsEmpty())
{
ShowMessage(m_hWnd, "没有给定数据库指针，也没有指定链接字！");
return FALSE;
}
if (!db.OpenEx(m_szODBCLink, CDatabase::openReadOnly | CDatabase::noOdbcDialog))
{
ShowMessage(m_hWnd, "无法打开数据库连接 %s", m_szODBCLink);
return FALSE;
}
m_nRecCount = GetRecordCount(db);
CRecordset rc(&db);
CString szSQL = m_szSQL;
CString csHeadRight = m_szHeadRight;
if (m_nRecCount > (int)m_nMaxRecordNum)
{
CString str;
str.Format(_T("共有 %d 条记录，但模版设计最大记录数为 %d，\n是否只 %d 条记录？\n"
"注意：如何选否，会给数据库很大压力，甚至取消操作！"),
m_nRecCount, m_nMaxRecordNum, m_nMaxRecordNum);
UINT ulSelect = IDNO;
if (m_hWnd)
ulSelect = MessageBox(m_hWnd, str, _T("BSRpter"), MB_YESNOCANCEL);
if (ulSelect == IDNO)
;
else if (ulSelect == IDCANCEL)
return FALSE;
else // IDYES
{
csHeadRight.Format(_T(" %d条记录未打印"), m_nRecCount - m_nMaxRecordNum);
m_nRecCount = m_nMaxRecordNum;
CString csDBDrv = getDBDriver(m_szODBCLink);
csDBDrv.MakeLower();
if (csDBDrv.Find("oracle") >= 0)
{
CString szTmp = szSQL;
szTmp.MakeLower();
int nFind = szTmp.Find(" where ");
if (nFind > 0)
{
szTmp.Format("ROWNUM <= %d AND ", m_nMaxRecordNum);
szSQL.Insert(nFind + (int)_tcslen(" where "), (LPCSTR)szTmp);
}
else
{
szTmp.Format(" WHERE ROWNUM <= %d ", m_nMaxRecordNum);
szSQL += szTmp;
}
}
else if (csDBDrv.Find("mysql") >= 0)
{
CString szTmp;
szTmp.Format(" Limit 0, %d ", m_nMaxRecordNum);
szSQL += szTmp;
}
else
{
CString str;
str.Format(_T("[ %s ] 数据库无法使用修改SQL的方法限制记录个数，"
"而打开大数据集可能会出现异常\n，是否先修改查询条件，再进行打印？")
, csDBDrv);
if (m_hWnd != NULL && MessageBox(m_hWnd, str,_T("EdFc"), MB_YESNO) == IDYES)
return FALSE;
}
}
}
rc.Open(CRecordset::forwardOnly, szSQL);
m_nFieldCount = rc.GetODBCFieldCount();
while (m_ColFmt.GetSize() > m_nFieldCount && m_nFieldCount > 1)
m_ColFmt.RemoveAt(m_ColFmt.GetSize() - 1);
COLFMT cf;
while (m_ColFmt.GetSize() < m_nFieldCount)
m_ColFmt.Add(cf);
m_Grid.DeleteAllItems();
int nRowCount = m_nRecCount + (m_bFootSum ? 2 : 1);
m_Grid.SetRowCount(nRowCount);
m_Grid.SetColumnCount(m_nFieldCount);
// set ColumName;
GV_ITEM Item;
Item.mask = GVIF_TEXT|GVIF_FORMAT;
Item.row = 0;
Item.nFormat = DT_CENTER|DT_VCENTER|DT_SINGLELINE|DT_END_ELLIPSIS|DT_NOPREFIX;
for (Item.col = 0; Item.col < m_nFieldCount; Item.col++)
{
m_Grid.SetColumnWidth(Item.col, m_ColFmt[Item.col].width);
Item.szText = m_ColFmt[Item.col].name;
if (m_ColFmt[Item.col].name.IsEmpty())
{
CODBCFieldInfo fi;
rc.GetODBCFieldInfo(Item.col, fi);
Item.szText = fi.m_strName;
}
m_Grid.SetItem(&Item);
}
if (m_hWnd != NULL && m_nRecCount > 0)
::SendMessage(m_hWnd, WM_CREATEPROGRESS, m_nRecCount, 0);
CDBVariant varValue;
Item.mask = GVIF_TEXT|GVIF_FORMAT;
Item.nFormat = DT_CENTER|DT_VCENTER|DT_SINGLELINE|DT_END_ELLIPSIS|DT_NOPREFIX;
for(int row = 0; row < m_nRecCount; row ++)
{
for (int col = 0; col < m_nFieldCount; col ++)
{
rc.GetFieldValue(col, varValue);
Item.row = row + 1;
Item.col = col;
Item.szText = GetVarStr(&varValue, m_ColFmt[col].format);
m_Grid.SetItem(&Item);
varValue.Clear();
}
if (m_hWnd != NULL)
::SendMessage(m_hWnd, WM_STEPPROGRESS, m_nRecCount, row);
rc.MoveNext();
}
if (m_bFootSum)
GetStaticData(rc);
rc.Close();
if (m_hWnd != NULL)
::SendMessage(m_hWnd, WM_DESTROYPROGRESS, 0, 0);
bRet = TRUE;
}
catch(CException *edb)
{
static TCHAR lpszError[MAX_PATH];
CString csError;
if (edb->GetErrorMessage(lpszError, MAX_PATH))
csError = lpszError;
if (csError.GetLength() <= 0)
csError = _T("无法获得数据！");
ShowMessage(m_hWnd, csError);
edb->Delete();
}
catch(...)
{
ShowMessage(m_hWnd, _T("无法获得数据！"));
}
return bRet;
}
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;
}
void fmserver (void)
{
struct fmparam param[1];
// MessageBox (NULL, "Hello", "Message", MB_OK);
init_param(param);
/* test
CDatabase db;
db.OpenEx (_T("DSN=droits"), 0);
CRecordsetClients rs;
rs.Open (AFX_DB_USE_DEFAULT_TYPE, "SELECT * FROM clients", 0);
rs.MoveFirst();
for (;;)
{
if (rs.IsEOF())
break;
MessageBox (NULL, rs.m_login, "Login", MB_OK);
rs.MoveNext();
}
rs.Close();
*/
WSADATA wsda;
SOCKET sListen, sClient;
struct hostent * hp;
char buf[10000];
int len;
SOCKADDR_IN addr, remote_addr;
int ret;
int iAddrLen;
WSAStartup (MAKEWORD(1,1), &wsda);
hp = gethostbyname ("localhost");
/*sListen = socket (hp->h_addrtype, SOCK_STREAM, 0);*/
sListen = socket (AF_INET, SOCK_STREAM, 0 /*IPPROTO_IP*/);
if (sListen == SOCKET_ERROR)
{
MessageBox (NULL, "Error creating socket", "Error", MB_OK);
exit(0);
}
memset (&addr, 0, sizeof(addr));
addr.sin_family = AF_INET;
addr.sin_port = htons(6030);
addr.sin_addr.s_addr = htonl (INADDR_ANY);
ret = bind (sListen, (struct sockaddr *)&addr, sizeof(addr));
if (ret == SOCKET_ERROR)
{
MessageBox (NULL, "Error bind", "Error", MB_OK);
exit(0);
}
ret = listen (sListen, 10);
if (ret == SOCKET_ERROR)
{
sprintf (buf, "Error listen: %d", WSAGetLastError());
MessageBox (NULL, buf, "Error listen", MB_OK);
exit(0);
}
for (;;)
{
iAddrLen = sizeof(remote_addr);
sClient = accept (sListen, (struct sockaddr *) &remote_addr, &iAddrLen);
if (sClient == SOCKET_ERROR)
{
MessageBox (NULL, "Error accept", "Error", MB_OK);
exit(0);
}
// MessageBox (NULL, "Accepted", "server", MB_OK);
/*
ret = recv (sClient, buf, sizeof(buf), 0);
if (ret == SOCKET_ERROR)
{
MessageBox (NULL, "Error recv", "Error", MB_OK);
exit(0);
}
buf[ret] = 0;
MessageBox (NULL, buf, "Received", MB_OK);
*/
/*
input (sClient, buf);
MessageBox (NULL, buf, "received", MB_OK);
*/
char dirfax[100];
char basedroits[100];
char basefax[100];
char faxdir[100];
char language[100];
char login[100];
char pass[100];
strcpy (basedroits, "droits");
strcpy (basefax, "AT05");
strcpy (faxdir, "L:\\AT05");
for (;;)
{
input (sClient, buf);
if (status == SOCKET_ERROR)
break;
// MessageBox (NULL, buf, "commande", MB_OK);
if (!strcmp (buf, "BASEFAX"))
{
input (sClient, dirfax);
input (sClient, language);
if (*dirfax)
{
lire_fichier (dirfax, "baserights.txt", basedroits);
lire_fichier (dirfax, "basefax.txt", basefax);
lire_fichier (dirfax, "faxdir.txt", faxdir);
}
output (sClient, "ID\tIdentifiant\t30\n");
output (sClient, "CNX\tDate\t20\n");
output (sClient, "TIME\tDurée\t5\n");
output (sClient, "PGENB\tPages\t5\n");
output (sClient, "HGN\tStatus\t5\n");
output (sClient, "FILE\tFichier\t15\n");
output (sClient, "\n");
}
else if (!strcmp (buf, "USER"))
{
input (sClient, login);
}
else if (!strcmp (buf, "PASS"))
{
input (sClient, pass);
output (sClient, "LOGIN\n1\n");
}
else if (!strcmp (buf, "LISTREC"))
{
char param[100];
char strsince[100];
input (sClient, param);
input (sClient, strsince);
CDatabase db;
char dsn[100];
sprintf (dsn, "DSN=%s", basefax);
db.OpenEx (_T(dsn), 0);
CRecordsetRec rs;
rs.Open (AFX_DB_USE_DEFAULT_TYPE, "SELECT * FROM REC", 0);
rs.MoveFirst();
// MessageBox (NULL, "Reading base", "trace", MB_OK);
for (;;)
{
if (rs.IsEOF())
break;
/*MessageBox (NULL, rs.m_ID, "id", MB_OK);*/
sprintf (buf,
"<MESSAGE><BASE>FAXBT</BASE><ID>%s</ID><CNX>%s</CNX><TIME>%lf</TIME><PGENB>%lf</PGENB><HGN>%lf</HGN><FILE>%s</FILE></MESSAGE>\n",
LPCSTR(rs.m_ID),
LPCSTR(rs.m_CNX),
rs.m_TIME,
rs.m_PGENB,
rs.m_HGN,
LPCSTR(rs.m_FILE));
/*MessageBox (NULL, buf, "Message", MB_OK);*/
output (sClient, buf);
rs.MoveNext();
}
output (sClient, "\n");
rs.Close();
}
else if (!strcmp (buf, "END"))
{
// MessageBox (NULL, buf, "END", MB_OK);
break;
}
else
{
// MessageBox (NULL, buf, "autre commande", MB_OK);
}
}
closesocket (sClient);
}
/*
for (;;)
{
input (commande);
MessageBox (NULL, commande, "Commande", MB_OK);
}
*/
}