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;
}
//定义的向数据库存储新的基本信息的函数
_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;
};
//定义的删除数据的函数
_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;
};
void AccountDeposit::OnClickedAccountDepositComfirm()
{
CDatabase db;
db.Open("bankmanagement");
CString sqlstr,sqlstraccount;//,username,idcard,pwd,address,email,number;
UpdateData(true);
if (m_DepositAmount<1 || m_DepositAmount>10000);
else{
CString timestr;//////获取系统时间
CTime tm; tm = CTime::GetCurrentTime();
timestr = tm.Format("%Y年%m月%d日 %X");
sqlstr.Format(_T("insert into operate_deposit(card_number,money_value,operate_time) values('%s','%f','%s')"), m_DepositCardnumber, m_DepositAmount,timestr);
sqlstraccount.Format(_T("update card set money_balance=money_balance+'%f'where card_number='%s'"), m_DepositAmount, m_DepositCardnumber);
MessageBox("确定吗？");
db.ExecuteSQL(sqlstr);
db.ExecuteSQL(sqlstraccount);
db.Close();
MessageBox("存款成功！");
m_DepositCardnumber = "";
m_DepositUsername = "";
m_DepositAmount = 0;
UpdateData(FALSE);
}
// TODO: 在此添加控件通知处理程序代码
}
void AccountDeposit::OnKillfocusEditDepositCard()
{
UpdateData(TRUE);
CDatabase db;
db.Open("bankmanagement");
CString sqlstrconfirm,foundflag;
sqlstrconfirm.Format(_T("select count(user_name) from user where user_id=(select user_id from card where card_number='%s')"), m_DepositCardnumber);
CRecordset rs(&db);
rs.Open(CRecordset::forwardOnly, (_T("%s"), sqlstrconfirm));
rs.GetFieldValue(_T("count(user_name)"), foundflag);
if (foundflag == '0'){
MessageBox("这张卡不存在");
GetDlgItem(IDC_ACCOUNT_DEPOSIT_COMFIRM)->EnableWindow(false);
}
else
{
GetDlgItem(IDC_ACCOUNT_DEPOSIT_COMFIRM)->EnableWindow(true);
CString sqlstr;
sqlstr.Format(_T("select user_name from user where user_id=(select user_id from card where card_number='%s')"), m_DepositCardnumber);
CRecordset rss(&db);
rss.Open(CRecordset::forwardOnly, (_T("%s"), sqlstr));
rss.GetFieldValue(_T("user_name"), m_DepositUsername);
//MessageBox(m_DepositUsername);
UpdateData(FALSE);
rss.Close();
}
rs.Close();
db.Close();
// TODO: 在此添加控件通知处理程序代码
}
void main(void)
{
// open with datasource name.
MyDB.Open("WoofBase");
// creation of the record set must happen AFTER the Database open!
CMyRecordset MyRecordset("SELECT Text1,Key1,Number1 FROM WOOFTABLE WHERE Number1 = 2",
&MyDB);
// open as a static snapshot
MyRecordset.Open(CRecordset::snapshot);
while(!MyRecordset.IsEOF())
{
MyRecordset.PrintRecord();
MyRecordset.MoveNext();
}
MyRecordset.Close();
MyDB.Close();
cout << endl;
cout << "the select was: " << MyRecordset.GetDefaultSQL() << endl;
}
void Regist::OnKillfocusEditRegistEmail()
{
UpdateData(TRUE);
CDatabase db;
db.Open("bankmanagement");
CString sqlstr;
sqlstr.Format(_T("select count(*) from user where user_email='%s'"), m_RegEmail);
CRecordset rs(&db);
rs.Open(CRecordset::forwardOnly, (_T("%s"), sqlstr));
CString count;
rs.GetFieldValue(_T("count(*)"), count);
//MessageBox(count);
if (count == '1')
{
GetDlgItem(IDC_EMAIL_NOTICE)->SetWindowTextA((CString)"×邮箱已被注册！");
GetDlgItem(IDC_REGIST_REGIST)->EnableWindow(false);
}//MessageBox("用户名已存在，请重新输入。", "输入有误");
else
{
GetDlgItem(IDC_EMAIL_NOTICE)->SetWindowTextA((CString)"√可以使用");
GetDlgItem(IDC_REGIST_REGIST)->EnableWindow(true);
}
rs.Close();
db.Close();
// TODO: 在此添加控件通知处理程序代码
}
/*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;
};
//定义的从数据库读出的基本信息的函数
_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;
};
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;
}
void CDatabaseDlg2::OnBnClickedDeleteFace()
{
// TODO: 在此添加控件通知处理程序代码
int nIndex;
CString id,fpath;
CDatabase db;
db.Open(NULL, FALSE, FALSE, _T("ODBC;DSN=face;UID=root;PWD=root"));
nIndex = m_list.GetNextItem(-1, LVNI_ALL | LVNI_SELECTED);
//if(-1 == nIndex)
// nIndex = 0;
if(-1 != nIndex)
{
id = m_list.GetItemText(nIndex,0);
fpath = m_list.GetItemText(nIndex,4);
}
else
{
MessageBox("请选择要删除人脸!");
db.Close();
return;
}
//删除文件
CFileFind finder;
if(finder.FindFile(fpath))
{
CFile TempFile;
TempFile.Remove(fpath);
}
CString sql;
sql.Format("delete from face where ID=%s", id);
db.ExecuteSQL(sql);
db.Close();
CString cmdStr = "Select * from face order by ID";
m_sname.SetWindowText("");
m_spath.SetWindowText("");
m_fname.SetWindowText("");
m_fpath.SetWindowText("");
ShowDatabase(cmdStr);
MessageBox("删除成功");
}
//定义的表格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;
};
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;
}
void AccountDetail::OnClickedCardWithdraw()
{
CDatabase db;
db.Open("bankmanagement");
CString sqlstr;//,username,idcard,pwd,address,email,number;
sqlstr.Format(_T("update card set card_status='申请注销' where card_number='%s'"), m_DetailCard);
db.ExecuteSQL(sqlstr);
db.Close();
MessageBox("管理员会在10个工作日内处理");
// TODO: 在此添加控件通知处理程序代码
}
/*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;
};
void ManagerProperty::OnClickedInterestFee()
{
CDatabase db;
db.Open("bankmanagement");
CString sqlstr,sqlstrcardnumber,sqlstrafteramount,sqlstrupdate,updatenumber;
int cardnumber;
sqlstr.Format(_T("update card set money_balance=money_balance-2 where card_status='正常'"));//////扣费
db.ExecuteSQL(sqlstr);
sqlstr.Format(_T("select count(*) from card where card_status='正常'"));/////查出卡片数
CRecordset rss(&db);
rss.Open(CRecordset::forwardOnly, (_T("%f"), sqlstr));
rss.GetFieldValue(_T("count(*)"), sqlstrcardnumber);
cardnumber = _ttoi(sqlstrcardnumber);
UpdateData(FALSE);
rss.Close();
sqlstr.Format(_T("select card_number from card where card_status='正常'"));/////记录收费数据
CRecordset r(&db);
r.Open(CRecordset::forwardOnly, (_T("%f"), sqlstr));
CString timestr;///////////get system time
CTime tm;
while (!r.IsEOF())
{
tm = CTime::GetCurrentTime();
timestr = tm.Format("%Y年%m月%d日 %X");
r.GetFieldValue(_T("card_number"), updatenumber);
sqlstrupdate.Format(_T("insert into operate_bank(card_number,money_amount,operate_type,operate_time) values('%s',2,'卡管理费','%s')"), updatenumber,timestr);
db.ExecuteSQL(sqlstrupdate);
r.MoveNext();
}
r.Close();
float fee = cardnumber*2.0;//////转入银行账户
sqlstr.Format(_T("update bank_account set money_amount=money_amount+'%f'"), fee);
db.ExecuteSQL(sqlstr);
sqlstr.Format(_T("select money_amount from bank_account"));////更新现实银行账户余额
CRecordset rs(&db);
rs.Open(CRecordset::forwardOnly, (_T("%f"), sqlstr));
CString stramount;
rs.GetFieldValue(_T("money_amount"), stramount);
m_ManagerPropertyamount = _tstof(stramount);
UpdateData(FALSE);
rs.Close();
db.Close();
MessageBox("手续费处理成功！");
// TODO: 在此添加控件通知处理程序代码
}
void ManageAccount::OnClickedManageModifyModify()
{
UpdateData(true);
CString sqlstr;
sqlstr.Format(_T("update user set user_name='%s',user_idcard='%s',user_address='%s',user_email='%s',phone_number='%s' where user_id ='%d'"), m_ManageaccountUsername, m_ManageaccountIdcard, m_ManageaccountAddress, m_ManageaccountEmail, m_ManageaccountPhone,dealid);
CDatabase db;
db.Open("bankmanagement");
db.ExecuteSQL(sqlstr);
db.Close();
MessageBox("更新成功，点击返回。");
OnOK();
// TODO: 在此添加控件通知处理程序代码
}
void CDatabaseDlg2::OnBnClickedAlterFace()
{
// TODO: 在此添加控件通知处理程序代码
int nIndex;
CString sname,spath,fname,fpath,id;
CDatabase db;
db.Open(NULL, FALSE, FALSE, _T("ODBC;DSN=face;UID=root;PWD=root"));
nIndex = m_list.GetNextItem(-1, LVNI_ALL | LVNI_SELECTED);
//if(-1 == nIndex)
// nIndex = 0;
if(-1 == nIndex)
{
MessageBox("请选择要修改的列");
db.Close();
return;
}
else
{
id = m_list.GetItemText(nIndex,0);
m_sname.GetWindowText(sname);
m_spath.GetWindowText(spath);
m_fname.GetWindowText(fname);
m_fpath.GetWindowText(fpath);
}
spath.Replace("\\","\\\\");
fpath.Replace("\\","\\\\");
CString sql;
sql.Format("update face set SourceImg='%s' , SourcePath='%s' ,FaceName='%s',FacePath='%s' where ID=%s", sname, spath,fname,fpath,id);
db.ExecuteSQL(sql);
db.Close();
CString cmdStr = "Select * from face order by ID";
m_sname.SetWindowText("");
m_spath.SetWindowText("");
m_fname.SetWindowText("");
m_fpath.SetWindowText("");
ShowDatabase(cmdStr);
MessageBox("修改成功");
}
BOOL ManagerChartNew::OnInitDialog()
{
CDialogEx::OnInitDialog();
///////////////////////////////产生图表
CSeries0 lineSeries = (CSeries0)m_chartnew.Series(0);
lineSeries.Clear();
CString sqlstr;
sqlstr.Format(_T("CREATE OR REPLACE VIEW `view_day_property`AS SELECT * FROM day_property ORDER BY day_property.dayproperty_id DESC LIMIT 10;"));
CDatabase db;
db.Open("bankmanagement");
db.ExecuteSQL(sqlstr);
sqlstr.Format(_T("select day_time,bank_balance from view_day_property ORDER BY dayproperty_id ASC;"));
CRecordset rs(&db);
rs.Open(CRecordset::forwardOnly, (_T("%s"), sqlstr));
CString strx, strbalance;
double daybalance;
for (int i = 0; i<10; i++)
{
CString varDayTime;
rs.GetFieldValue(_T("day_time"), varDayTime);
strx.Format(_T("%s"), varDayTime);
CString varBankBalance;
rs.GetFieldValue(_T("bank_balance"), varBankBalance);
strbalance.Format(_T("%s"), varBankBalance);
daybalance = _ttof(strbalance);
lineSeries.AddXY((double)i, daybalance, strx, 0);
rs.MoveNext();
}
rs.Close();
db.Close();
return true;
}
// CDatabaseDlg2 消息处理程序
void CDatabaseDlg2::ShowDatabase(CString cmdStr)
{
m_list.DeleteAllItems();
//打开数据库
CDatabase db;
db.Open(NULL,FALSE,FALSE,"ODBC;DSN=face;UID=root;PWD=root");
//创建查询结果接收集
CRecordset rs(&db);
rs.Open(CRecordset::forwardOnly,(L"%s",cmdStr));
int i = 0;
//操作查询结果
while(!rs.IsEOF())
{
CString id;
rs.GetFieldValue("ID",id);
m_list.InsertItem(i,id);
CString simg;
rs.GetFieldValue("SourceImg",simg);
m_list.SetItemText(i,1,simg);
CString spath;
rs.GetFieldValue("SourcePath",spath);
m_list.SetItemText(i,2,spath);
CString fimg;
rs.GetFieldValue("FaceName",fimg);
m_list.SetItemText(i,3,fimg);
CString fpath;
rs.GetFieldValue("FacePath",fpath);
m_list.SetItemText(i,4,fpath);
CString date;
rs.GetFieldValue("Time",date);
m_list.SetItemText(i,5,date);
i++;
rs.MoveNext();
}
m_list.SetExtendedStyle(LVS_EX_FLATSB | LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);
//关闭数据库
rs.Close();
db.Close();
}
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);
}
}
BOOL ManageAccount::OnInitDialog()
{
CDialogEx::OnInitDialog();
CString sqlstr;
sqlstr.Format(_T("select user_name,user_idcard,user_address,user_email,phone_number from user where user_id ='%d'"), dealid);
CDatabase db;
db.Open("bankmanagement");
CRecordset rs(&db);
rs.Open(CRecordset::forwardOnly, (_T("%s"), sqlstr));
rs.GetFieldValue(_T("user_name"), m_ManageaccountUsername);
rs.GetFieldValue(_T("user_idcard"), m_ManageaccountIdcard);
rs.GetFieldValue(_T("user_address"), m_ManageaccountAddress);
rs.GetFieldValue(_T("user_email"), m_ManageaccountEmail);
rs.GetFieldValue(_T("phone_number"), m_ManageaccountPhone);
rs.Close();
db.Close();
UpdateData(false);
if (modifyflag == 0)
{
GetDlgItem(IDC_MANAGE_ACCOUNT_USERNAME)->EnableWindow(false);
GetDlgItem(IDC_MANAGE_ACCOUNT_IDCARD)->EnableWindow(false);
GetDlgItem(IDC_MANAGE_ACCOUNT_ADDRESS)->EnableWindow(false);
GetDlgItem(IDC_MANAGE_ACCOUNT_EMAIL)->EnableWindow(false);
GetDlgItem(IDC_MANAGE_ACCOUNT_PHONE)->EnableWindow(false);
GetDlgItem(IDC_MANAGE_MODIFY_MODIFY)->ShowWindow(SW_HIDE);
}
else
{
GetDlgItem(IDC_MANAGE_ACCOUNT_USERNAME)->EnableWindow(false);
GetDlgItem(IDC_MANAGE_ACCOUNT_IDCARD)->EnableWindow(true);
GetDlgItem(IDC_MANAGE_ACCOUNT_ADDRESS)->EnableWindow(true);
GetDlgItem(IDC_MANAGE_ACCOUNT_EMAIL)->EnableWindow(true);
GetDlgItem(IDC_MANAGE_ACCOUNT_PHONE)->EnableWindow(true);
GetDlgItem(IDC_MANAGE_MODIFY_MODIFY)->ShowWindow(SW_SHOW);
}
return true;
}
void Regist::OnClickedRegistRegist()
{
CDatabase db;
db.Open("bankmanagement");
CString sqlstr;//,username,idcard,pwd,address,email,number;
UpdateData(TRUE);//更新文本框变量
//MessageBox(m_RegUsername);
sqlstr.Format(_T("insert into user(user_name,user_idcard,user_pwd,user_address,user_email,phone_number) values('%s','%s','%s','%s','%s','%s')"), m_RegUsername, m_RegIdcard, m_RegPwd, m_RegAddress, m_RegEmail, m_RegPhone);
if (m_RegUsername.IsEmpty() || m_RegPwd.IsEmpty() || m_RegPwdConfirm.IsEmpty() || m_RegIdcard.IsEmpty() || m_RegAddress.IsEmpty() || m_RegEmail.IsEmpty())
{
MessageBox("带*为必填项", "输入有误");
}
else if (m_RegUsername == "用户名为字母与数字组合")
{
MessageBox("用户名未修改");
}
else if (m_RegPwd.GetLength() < 6)
{
MessageBox("密码太短！","输入有误");
}
else if (m_RegPwd != m_RegPwdConfirm)
{
MessageBox("输入密码不一致", "输入有误");
}
else
{
db.ExecuteSQL(sqlstr);
db.Close();
MessageBox("注册成功！返回登录。");
//this->GetParent()->SendMessage(WM_CLOSE);//关闭父对话框
Hello dlg;
fatherflag = 1;
ShowWindow(SW_HIDE);
dlg.DoModal();
ShowWindow(SW_SHOW);
}
// TODO: 在此添加控件通知处理程序代码
}
BOOL ManagerPropertyFeeDetail::OnInitDialog()
{
CDialogEx::OnInitDialog();
FeeDetailList.InsertColumn(1, _T("银行卡号"), LVCFMT_LEFT, 80);
FeeDetailList.InsertColumn(2, _T("操作金额"), LVCFMT_LEFT, 80);
FeeDetailList.InsertColumn(3, _T("操作类型"), LVCFMT_LEFT, 80);
FeeDetailList.InsertColumn(4, _T("操作时间"), LVCFMT_LEFT, 200);
CString sqlstr;
sqlstr.Format(_T("select card_number,money_amount,operate_type,operate_time from operate_bank"));
FeeDetailList.DeleteAllItems();
CDatabase db;
db.Open("bankmanagement");
CRecordset rs(&db);
rs.Open(CRecordset::forwardOnly, (_T("%s"), sqlstr));
while (!rs.IsEOF())
{
CString varCardNumber;
rs.GetFieldValue(_T("card_number"), varCardNumber);
FeeDetailList.InsertItem(0, varCardNumber);
CString varMoneyAmount;
rs.GetFieldValue(_T("money_amount"), varMoneyAmount);
FeeDetailList.SetItemText(0, 1, varMoneyAmount);
CString varOperateType;
rs.GetFieldValue(_T("operate_type"), varOperateType);
FeeDetailList.SetItemText(0, 2, varOperateType);
CString varOperateTime;
rs.GetFieldValue(_T("operate_time"), varOperateTime);
FeeDetailList.SetItemText(0, 3, varOperateTime);
rs.MoveNext();
}
FeeDetailList.SetExtendedStyle(LVS_EX_FLATSB | LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);
rs.Close();
db.Close();
return true;
}
void ManagerProperty::OnClickedAddPropertyInfo()
{
CDatabase db;
db.Open("bankmanagement");
CString sqlstr,strbankbalance;
float bankbalance;
CString timestr;//////获取系统时间
CTime tm; tm = CTime::GetCurrentTime();
timestr = tm.Format("%Y-%m-%d");
sqlstr.Format(_T("select sum(money_balance) from card"));
CRecordset rsss(&db);
rsss.Open(CRecordset::forwardOnly, (_T("%f"), sqlstr));
rsss.GetFieldValue(_T("sum(money_balance)"), strbankbalance);
bankbalance = _ttof(strbankbalance);
sqlstr.Format(_T("insert into day_property(bank_balance,day_time)values('%f','%s')"), bankbalance,timestr);
db.ExecuteSQL(sqlstr);
db.Close();
MessageBox("处理成功！");
ManagerProperty::OnInitDialog();
// TODO: 在此添加控件通知处理程序代码
}
//////////////////////////////////////////////////////////////////////////////
// 函数：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);
}
}
BOOL CSourceODBC::PrepareOpen()
{
CDatabase db;
try
{
//if ( !db.Open(GetDefaultConnect()) )
if (!db.Open(L"ToolDB"))
{
AfxMessageBox( _T("Не удалось соедениться с базой данных.") );
return FALSE;
}
}
catch ( CDBException *pException)
{
CString msg = _T("Не удалось соедениться с базой данных.\n") + pException->m_strError;
AfxMessageBox( msg );
pException->Delete(); // Удаляем структуру из памяти!
return FALSE;
}
CRecordset rs(&db);
try
{
if ( !rs.Open(CRecordset::dynaset, CString( _T("SELECT * FROM ") ) + GetDefaultSQL()) ) // читаем всю таблицу из БД в РН
{
AfxMessageBox( _T("Не удалось прочесть базу данных.") );
rs.Close();
db.Close();
return FALSE;
}
}
catch ( CDBException *pException )
{
CString msg = _T("Не удалось прочесть базу данных.\n") + pException->m_strError;
AfxMessageBox( msg );
pException->Delete(); // Удаляем структуру из памяти!
return FALSE;
}
short nFields = rs.GetODBCFieldCount(); // число полей в записи РН
for ( short index = 0; index < nFields; index++ ) // цикл по всем полям записи рабочего набора
{
SDBField *field = new SDBField; // создаём новый описатель поля
CODBCFieldInfo info; // структура с информацией о полях в источнике ODBC
rs.GetODBCFieldInfo( index, info ); // заполнить структуру с информацией о поле
field->FieldName = CString( _T("[") ) + info.m_strName + CString( _T("]") );
field->FieldCaption = info.m_strName;
switch (info.m_nSQLType)
{
case SQL_INTEGER:
field->ID_TYPE = LONG;
break;
case SQL_DOUBLE:
field->ID_TYPE = DOUBLE;
break;
case -9: /// String
field->ID_TYPE = STRING;
break;
case -10: /// Memo
field->ID_TYPE = STRING;
break;
case -4: /// Ole
field->ID_TYPE = BLOB;
break;
case 11:
field->ID_TYPE = TIME;
break;
} // end switch
m_database.m_fields.Add(field); // добавить заполненный описатель в массив
} // end for
rs.Close();
db.Close();
return TRUE;
}
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);
}