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 CMyDatabase::UpdateOfflineAccount (_NetcashDevice &McrStation,CDatabase &m_Database ,int dbms)
{
CString m_Query;
try
{
switch(McrStation.Charging)
{
case MONEY_DEBIT:
m_Query.Format("UPDATE ACCOUNTS SET BALANCE = BALANCE - %f WHERE ACCOUNTID = %d",McrStation.OfflineCost, McrStation.AccountID);
m_Database.ExecuteSQL(m_Query);
break;
case MONEY_CREDIT:
m_Query.Format("UPDATE ACCOUNTS SET BALANCE = BALANCE + %f WHERE ACCOUNTID = %d",McrStation.OfflineCost, McrStation.AccountID);
m_Database.ExecuteSQL( m_Query);
break;
case MONEY_NOCHARGE:
break;
}
}
catch(CDBException *e)
{
CString Message;
Message.Format("CMyDatabase::UpdateOffLineAccount(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
void CMyDatabase::Handle_SessionClosed( _NetcashDevice &pBuf,CDatabase &m_Database)
{
CString m_Query;
try
{
m_Query.Format("UPDATE DOCUGATE_GENERAL_CTLUNIT SET CTLUNIT_STATUS = 'CLOSED' WHERE CTLUNIT_GUID = '%s'",pBuf.UnitGUID);
m_Database.ExecuteSQL (m_Query);
m_Query.Format("UPDATE DOCUGATE_GENERAL_CTLUNIT SET CTLUNIT_LATEST = GETDATE() WHERE CTLUNIT_GUID = '%s'",pBuf.UnitGUID);
m_Database.ExecuteSQL (m_Query);
}
catch( CDBException *e)
{
CString Message;
Message.Format("CMyDatabase::Handle_SessionClosed(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
int CMyDatabase::UpdateSchema( CDatabase &m_Database )
{
CString m_Query;
try
{
m_Query.Format("if not exists (select * from syscolumns where id=object_id('DOCUGATE_GENERAL_CTLUNIT') and name='CTLUNIT_STATUS') alter table DOCUGATE_GENERAL_CTLUNIT add CTLUNIT_STATUS varchar(255) NULL");
m_Database.ExecuteSQL (m_Query);
m_Query.Format("if not exists (select * from syscolumns where id=object_id('DOCUGATE_GENERAL_CTLUNIT') and name='CTLUNIT_LATEST') alter table DOCUGATE_GENERAL_CTLUNIT add CTLUNIT_LATEST datetime NULL");
m_Database.ExecuteSQL (m_Query);
m_Query.Format("if not exists (select * from syscolumns where id=object_id('DOCUGATE_GENERAL_ACTIVITY') and name='ACC_GUID') alter table DOCUGATE_GENERAL_ACTIVITY add ACC_GUID uniqueidentifier NULL");
m_Database.ExecuteSQL (m_Query);
m_Query.Format("if not exists (select * from syscolumns where id=object_id('DOCUGATE_GENERAL_ACTIVITY') and name='OU_GUID') alter table DOCUGATE_GENERAL_ACTIVITY add OU_GUID uniqueidentifier NULL");
m_Database.ExecuteSQL (m_Query);
}
catch( CDBException *e)
{
CString Message;
Message.Format("CMyDatabase::UpdateSchema(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
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: 在此添加控件通知处理程序代码
}
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;
}
void CMyDatabase::LogCopyJob (_NetcashDevice &McrStation,CDatabase &m_Database,int dbms)
{
CString m_Query;
m_Query.Format("INSERT INTO DOCUGATE_GENERAL_ACTIVITY (JOB_GUID,DT_REQUESTED,ACC_BADGE,ACC_GUID,OU_GUID,PAGECOUNT,JOBCOST,COPIES,COLOR,DUPLEX,DOCNAME,CTLUNIT_SERIAL_NO,COMMENTS,OU_NAME,JOBTYPE,CC_CODE,CM_MATTER) VALUES ('%s',GETDATE(),'%s','%s','%s',%d, %f,1,0,0,'COPYJOB','%s','COPYJOB','%s',0,'%s','%s')",
McrStation.InsertGUID,
McrStation.CopyAccount,
McrStation.AccountGUID,
McrStation.Group,
McrStation.CurSessionCount,
McrStation.CurSessionCost,
McrStation.Location,
McrStation.GroupName,
McrStation.CopyClient,
McrStation.CopyMatter);
try
{
m_Database.ExecuteSQL( m_Query);
}
catch( CDBException *e)
{
char Message[512] = {0};
sprintf(Message,"CMyDatabase::LogCopyJob(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
void CMyDatabase::Handle_PingedConnection( _NetcashDevice &pBuf,CDatabase &m_Database,int dbms)
{
CRecordset m_RS( &m_Database );
CString m_Query;
CString m_Data;
try
{
m_Query.Format("SELECT count(*) as MyCOUNTER FROM SMARTPRINT_STATIONS where SC_DEVICEID = '%s'",pBuf.Location);
m_RS.Open(AFX_DB_USE_DEFAULT_TYPE, m_Query,CRecordset::executeDirect);
m_RS.GetFieldValue( short(0),m_Data);
if ( m_RS.IsOpen() )
m_RS.Close();
if ( atoi(m_Data) == 0 ) // need to insert
{
m_Query.Format("INSERT INTO SMARTPRINT_STATIONS (SC_DEVICEID,SC_DEVICEIP,SC_DEVICEGW,SC_DEVICEMASK,SC_STATUS) VALUES ('%s','%s','%s','%s','Connection Alive')",pBuf.Location,pBuf.Address,pBuf.Gateway,pBuf.Mask);
m_Database.ExecuteSQL (m_Query);
}
else // need to upddate
{
m_Query.Format("UPDATE SMARTPRINT_STATIONS SET SC_DEVICEIP = '%s',SC_DEVICEGW= '%s',SC_DEVICEMASK= '%s',SC_STATUS = 'Connection Alive' WHERE SC_DEVICEID = '%s'",pBuf.Address,pBuf.Gateway,pBuf.Mask,pBuf.Location);
m_Database.ExecuteSQL (m_Query);
}
m_Query.Format("UPDATE SMARTPRINT_STATIONS SET SC_LATEST = NOW() WHERE SC_DEVICEID = '%s'",pBuf.Location);
m_Query.Replace("NOW()","GETDATE()");
m_Database.ExecuteSQL (m_Query);
}
catch( CDBException *e)
{
CString Message;
Message.Format("CMyDatabase::Handle_PingedConnection(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
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: 在此添加控件通知处理程序代码
}
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 CTempretureImpl::InsertDataToMySql(vector<CTempData*> tempvec)
{
CDatabase *db = CDbConnect::GetInstance()->getDatabase();
if (!db->BeginTrans())
return;
for (int i = 0; i < tempvec.size(); i++){
CString sql;
sql.Format(_T("insert into tempreture(tempreture,time) values('%s','%s')"), tempvec.at(i)->GetTemperature(), tempvec.at(i)->GetTime());
db->ExecuteSQL(sql);
}
db->CommitTrans();
}
void CMyDatabase::Handle_DroppedConnection( _NetcashDevice &pBuf,CDatabase &m_Database,int dbms)
{
CString m_Query;
try
{
m_Query.Format("UPDATE SMARTPRINT_STATIONS SET SC_STATUS = 'Connection Dropped' WHERE SC_DEVICEID = '%s'",pBuf.Location);
m_Database.ExecuteSQL (m_Query);
m_Query.Format("UPDATE SMARTPRINT_STATIONS SET SC_LATEST = NOW() WHERE SC_DEVICEID = '%s'",pBuf.Location);
m_Query.Replace("NOW()","GETDATE()");
m_Database.ExecuteSQL (m_Query);
}
catch( CDBException *e)
{
CString Message;
Message.Format("CMyDatabase::Handle_DroppedConnection(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
void CTempretureImpl::InsertDataToMySql(CTempData* temp)
{
CDatabase *db = CDbConnect::GetInstance()->getDatabase();
if (!db->BeginTrans())
return;
int n = 3;
while (n--)
{
CString sql;
sql.Format(_T("insert into tempreture(tempreture,time) values('%s','%s')"), temp->GetTemperature(), temp->GetTime());
db->ExecuteSQL(sql);
}
db->CommitTrans();
}
void CMyDatabase::RemoveAllActiveSessions(CDatabase &m_Database)
{
try
{
m_Database.ExecuteSQL ("DELETE FROM DOCUGATE_GENERAL_ACTIVE_SESSION");
}
catch( CDBException *e)
{
char Message[512] = {0};
sprintf(Message,"CMyDatabase::RemoveActiveCopySessions(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
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;
}
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("删除成功");
}
void CMyDatabase::UpdateAccount (_NetcashDevice &McrStation,CDatabase &m_Database ,int dbms)
{
CString m_Query;
try
{
m_Query.Format("UPDATE DOCUGATE_GENERAL_ACC SET ACC_FUNDS = %f WHERE ACC_ACC_GUID = '%s'",McrStation.CurSessionBalance, McrStation.AccountGUID);
m_Database.ExecuteSQL( m_Query);
}
catch(CDBException *e)
{
CString Message;
Message.Format("CMyDatabase::UpdateAccount(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
void CMyDatabase::UpdateOfflineCopier ( _NetcashDevice &McrStation,CDatabase &m_Database,int dbms)
{
CString m_Query;
try
{
m_Query.Format("UPDATE DEVICES SET CURRENTCOUNT = CURRENTCOUNT + %d WHERE DEVICEID = %d",McrStation.OfflineCount, McrStation.DeviceID);
m_Database.ExecuteSQL( m_Query);
}
catch(CDBException *e)
{
CString Message;
Message.Format("CMyDatabase::UpdateOffLineCopier(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
m_DBLock.Unlock();
}
void CMyDatabase::AddActiveSession(LPSTR pstrAccountGUID,CDatabase &m_Database)
{
try
{
CString m_Query;
m_Query.Format("INSERT INTO DOCUGATE_GENERAL_ACTIVE_SESSION (ACC_GUID) VALUES ('%s')",pstrAccountGUID);
m_Database.ExecuteSQL (m_Query);
}
catch( CDBException *e)
{
char Message[512] = {0};
sprintf(Message,"CMyDatabase::AddActiveCopySession(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
void CMyDatabase::Handle_NewConnection( _NetcashDevice &pBuf,CDatabase &m_Database ,int dbms)
{
CString m_Query;
CRecordset m_RS( &m_Database );
CString m_Data;
try
{
m_Query.Format("UPDATE DOCUGATE_GENERAL_CTLUNIT SET CTLUNIT_STATUS = 'CONNECTED' WHERE CTLUNIT_SERIAL_NO = '%s'",pBuf.Location);
m_Database.ExecuteSQL (m_Query);
}
catch( CDBException *e)
{
CString Message;
Message.Format("CMyDatabase::Handle_NewConnection(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
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: 在此添加控件通知处理程序代码
}
void CMyDatabase::DeleteActiveSession(LPSTR pstrAccountGUID,CDatabase &m_Database)
{
CString m_Query;
try
{
m_Query.Format("DELETE FROM DOCUGATE_GENERAL_ACTIVE_SESSION WHERE ACC_GUID = '%s'",pstrAccountGUID);
m_Database.ExecuteSQL (m_Query);
}
catch( CDBException *e)
{
char Message[512] = {0};
sprintf(Message,"CMyDatabase::DeleteActiveCopySession(%s)",e->m_strError);
WriteLog(Message,"");
sprintf(Message,"CMyDatabase::DeleteActiveCopySession(%s)",m_Query);
WriteLog(Message,"");
e->Delete();
}
}
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("修改成功");
}
void CMyDatabase::UpdateCopier ( _NetcashDevice &McrStation,CDatabase &m_Database,int dbms)
{
return;
CString m_Query;
long MyCount = McrStation.InitCount + McrStation.CurSessionCount;
try
{
m_Query.Format("UPDATE DEVICES SET CURRENTCOUNT = %d WHERE DEVICEID = %d",MyCount, McrStation.DeviceID);
m_Database.ExecuteSQL( m_Query);
}
catch(CDBException *e)
{
CString Message;
Message.Format("CMyDatabase::UpdateCopier(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
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: 在此添加控件通知处理程序代码
}
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 CMyDatabase::LogJournel (_NetcashDevice &McrStation,CDatabase &m_Database,int dbms)
{
CString m_Query;
CString m_desc;
m_desc.Format("MCR Unit %s, # of Copies = %d",McrStation.Location,McrStation.CurSessionCount);
m_Query.Format( "INSERT INTO DOCUGATE_GENERAL_ACTIVITY_JOURNEL (J_TYPE,J_AMOUNT,J_GUID,J_DESC) VALUES ('MCR PHOTOCOPY',%.2f,'%s','%s')", \
McrStation.CurSessionCost,
McrStation.AccountGUID,
m_desc);
try
{
m_Database.ExecuteSQL( m_Query);
}
catch( CDBException *e)
{
char Message[512] = {0};
sprintf(Message,"CMyDatabase::LogJournel(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
void CMyDatabase::LogCopyJobPL (_NetcashDevice &McrStation,CDatabase &m_Database,int dbms)
{
CString m_Query;
m_Query.Format("INSERT INTO DOCUGATE_GENERAL_ACTIVITY_PL (JOB_GUID,PL1,PL2,PL3,PL4) VALUES ('%s',%d,%d,%d,%d)",
McrStation.InsertGUID,
McrStation.plCounter[1],
McrStation.plCounter[2],
McrStation.plCounter[3],
McrStation.plCounter[4] );
try
{
m_Database.ExecuteSQL( m_Query);
}
catch( CDBException *e)
{
char Message[512] = {0};
sprintf(Message,"CMyDatabase::LogCopyJob(%s)",e->m_strError);
WriteLog(Message,"");
e->Delete();
}
}
//////////////////////////////////////////////////////////////////////////////
// 函数：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);
}
}