void DataLayer::InitMenu(Menu * mm) {
if (connectDB()) {
qDebug()<<"connected";
QSqlQuery q;
q.prepare ("select * from [food item] ");
q.exec();
// int count = q.numRowsAffected();
while(q.next()){
QString name = q.value(0).toString();
float price = q.value(1).toFloat();
QString details = q.value(2).toString();
QString unit = q.value(3).toString();
QString type = q.value(4).toString();
mm->Add(name, price, details, unit, type);
}
closeDB();
//return true;
}
else {qDebug()<<"not connected";
}
}
int PointSqlQueryModel::qt_metacall(QMetaObject::Call _c, int _id, void **_a)
{
_id = QSqlQueryModel::qt_metacall(_c, _id, _a);
if (_id < 0)
return _id;
if (_c == QMetaObject::InvokeMetaMethod) {
switch (_id) {
case 0: setQuery((*reinterpret_cast< const QString(*)>(_a[1])),(*reinterpret_cast< const QSqlDatabase(*)>(_a[2]))); break;
case 1: setQuery((*reinterpret_cast< const QString(*)>(_a[1]))); break;
case 2: setQuery((*reinterpret_cast< const QSqlQuery(*)>(_a[1]))); break;
case 3: { QVariant _r = data((*reinterpret_cast< const QModelIndex(*)>(_a[1])),(*reinterpret_cast< int(*)>(_a[2])));
if (_a[0]) *reinterpret_cast< QVariant*>(_a[0]) = _r; } break;
case 4: refresh((*reinterpret_cast< double(*)>(_a[1])),(*reinterpret_cast< double(*)>(_a[2])),(*reinterpret_cast< double(*)>(_a[3]))); break;
case 5: refreshSearch((*reinterpret_cast< const QString(*)>(_a[1]))); break;
case 6: { QVariant _r = datasingle((*reinterpret_cast< const QString(*)>(_a[1])));
if (_a[0]) *reinterpret_cast< QVariant*>(_a[0]) = _r; } break;
case 7: { bool _r = openDB();
if (_a[0]) *reinterpret_cast< bool*>(_a[0]) = _r; } break;
case 8: initDB(); break;
case 9: closeDB(); break;
case 10: { double _r = distance((*reinterpret_cast< double(*)>(_a[1])),(*reinterpret_cast< double(*)>(_a[2])),(*reinterpret_cast< double(*)>(_a[3])),(*reinterpret_cast< double(*)>(_a[4])));
if (_a[0]) *reinterpret_cast< double*>(_a[0]) = _r; } break;
default: ;
}
_id -= 11;
}
return _id;
}
void Food::showAllThisType()
{
openDB();
int result;
char * errmsg = NULL;
char **dbResult; //是 char ** 类型，两个*号
int nRow, nColumn;
string SQLCode = "select * from product where type = \"food\";";
result = sqlite3_get_table(Product::db, SQLCode.c_str(), &dbResult, &nRow, &nColumn, &errmsg);
cout << "-----------------------------------------------" << endl;
for (int i = 1; i <= nRow; i++)
{
cout << "product ID:" << dbResult[i * 7 + 0] << endl;
cout << "type:" << dbResult[i * 7 + 1] << endl;
cout << "name:" << dbResult[i * 7 + 2] << endl;
cout << "price:" << dbResult[i * 7 + 3] << endl;
cout << "sales volumn:" << dbResult[i * 7 + 4] << endl;
cout << "left:" << dbResult[i * 7 + 5] << endl;
cout << "description:" << dbResult[i * 7 + 6] << endl;
cout << "-----------------------------------------------" << endl;
}
closeDB();
}
/**
* @brief Deletes the database.
*
* @return bool
*/
bool DatabaseManager::deleteDB()
{
Macaw::DEBUG("[DatabaseManager] deleteDB");
closeDB();
return QFile::remove(m_db.databaseName());
}
void cleanup(){
if(mWorld.p)
pcap_close(mWorld.p);
closeDB(&mWorld);
}
bool CacheDatabase::deleteDB()
{
// Close database
closeDB();
// Remove file
return QFile::remove(mDbPath);
}
double Product::getPrice(string productId)
{
openDB();
int result;
char * errmsg = NULL;
char **dbResult; //是 char ** 类型，两个*号
int nRow, nColumn;
/*string SQLCode = "select * from food as th, books as de where th.productId = \""
+ productId + "\" or de.productId = \""
+ productId + "\";";*/
string SQLCode = "select * from product where productId = \""
+ productId + "\";";
result = sqlite3_get_table(db, SQLCode.c_str(), &dbResult, &nRow, &nColumn, &errmsg);
//cout << nRow << endl;
//cout << errmsg << endl;
//cout << dbResult[10] << endl;
closeDB();
if (nRow > 0)
return atof(dbResult[10]);
else
return -1;
}
void MainWindow::update_tableView_Cart()
{
openDB();
QSqlQuery query;
//doesn't involve discounts:
query.prepare("select title, wm_concat(author.name), isbn, cart.price, 'Delete' "
"from cart join book on cart.isbn = book.isbn "
"join book_s_author ba on ba.isbn = book.isbn "
"join author on ba.author_ID = author.AUTHOR_ID "
"where cart.customer_id = :cust_id "
"group by title, isbn, cart.price");
query.bindValue(":cust_id", current_customer_ID);
/* query.prepare("select asd1.title, asd1.auth_name, asd1.isbn, nvl(asd2.discount, asd1.price), 'Delete' "
"from (select title, wm_concat(author.name) auth_name, book.isbn, price, cart.bundle_id "
"from cart join book on cart.isbn = book.isbn "
"join book_s_author ba on ba.isbn = book.isbn "
"join author on ba.author_ID = author.AUTHOR_ID "
"group by title, book.isbn, price, cart.bundle_id) asd1 "
"left outer join "
"(select discount, isbn, bundle_id "
"from bundledbook) asd2 "
"on asd1.isbn = asd2.isbn and asd1.bundle_id = asd2.bundle_id");*/
qDebug()<<"TableView updated: "<<query.exec();
qDebug()<<query.lastError();
cartModel->setQuery(query);
ui->label_page_count->setText(QString("Page %1 out of %2").arg(current_book_page + 1).arg(max_book_pages + 1));
cartModel->setHeaderData( 0, Qt::Horizontal, QObject::tr("Title") );
cartModel->setHeaderData( 1, Qt::Horizontal, QObject::tr("Author") );
cartModel->setHeaderData( 2, Qt::Horizontal, QObject::tr("ISBN") );
cartModel->setHeaderData( 3, Qt::Horizontal, QObject::tr("Price") );
cartModel->setHeaderData( 4, Qt::Horizontal, QObject::tr(""));
closeDB();
}
void BufferManager::closeAll(){
for(int i=0;i<max_connection;i++){
if(mdt[i].id!=-1 && mdt[i].isopen){
closeDB(i);
}
}
}
bool DBUtil::openDB(const char* db) {
if (pDB != NULL)
closeDB();
result = sqlite3_open(db, &pDB);
return result == SQLITE_OK;
}
void MainWindow::update_tableView_Bundles()
{
openDB();
QSqlQuery query;
query.prepare("select name, ttl, asd1.bundle_id, trunc(prc,2), 'Add to cart' "
"from "
"( "
"select bundle.name, wm_concat(book.title) ttl, bundle.bundle_id "
"from bundle, bundledbook, book "
"where book.isbn = bundledbook.isbn and bundle.bundle_id = bundledbook.bundle_id "
"group by bundle.name, bundle.bundle_id "
") asd1 "
"join "
"( "
"select sum((1-discount)*price) prc, bundle.bundle_id "
"from bundle, bundledbook, book "
"where book.isbn = bundledbook.isbn and bundle.deleted != 1 and bundle.bundle_id = bundledbook.bundle_id "
"group by bundle.bundle_id "
") asd2 "
"on asd1.bundle_id = asd2.bundle_id");
qDebug()<<"bundles update: "<<query.exec();
qDebug()<<query.lastError();
bundlesModel->setQuery(query);
qDebug()<<"rows found: "<<bundlesModel->rowCount()<<"\n";
bundlesModel->setHeaderData( 0, Qt::Horizontal, QObject::tr("Bundle"));
bundlesModel->setHeaderData( 1, Qt::Horizontal, QObject::tr("Books in bundle"));
bundlesModel->setHeaderData( 2, Qt::Horizontal, QObject::tr("Bundle ID"));
bundlesModel->setHeaderData( 3, Qt::Horizontal, QObject::tr("Price"));
bundlesModel->setHeaderData( 4, Qt::Horizontal, QObject::tr(""));
ui->tableView_Bundles->hideColumn(2);
closeDB();
}
int openDB (char *name)
{ char *ptr,*top;
FILE *f;
struct stat sdata;
unsigned long dn;
closeDB ();
f = fopen (name,"r");
stat (name,&sdata);
DB.pals = malloc (sdata.st_size);
fread (DB.pals,sdata.st_size,1,f);
fclose (f);
ptr = DB.pals; top = ptr + sdata.st_size;
dn = 0;
while (ptr < top)
{ while (*ptr != '\n') ptr++;
dn++; *ptr++ = 0;
}
DB.ptrs = malloc ((dn+1)*sizeof(char*));
dn = 0; ptr = DB.pals;
DB.ptrs[0] = NULL;
while (ptr < top)
{ DB.ptrs[++dn] = ptr;
while (*ptr++);
}
DB.npals = dn;
return DB.npals;
}
void MainWindow::on_pushButton_clicked() //search
{
openDB();
QSqlQuery query;
query.prepare("select title, new_name, price, ISBN, 'Add to cart' from "
"( "
"select row_number() over(order by title) NUM, title, new_name, price, ISBN, 'Add to cart' from "
"( "
"select title, wm_concat(a.name) as new_name, price, b.ISBN, 'Add to cart' "
"from BOOK b INNER JOIN BOOK_S_AUTHOR ba on b.ISBN = ba.ISBN INNER JOIN AUTHOR a on ba.author_id = a.author_id "
"where (lower(b.title) LIKE lower(:search1)) or (lower(a.name) LIKE lower(:search2)) or (lower(b.isbn) LIKE lower(:search3)) "
"group by title, price, b.ISBN "
") asdf "
") foo where Num>:lowvalue and Num<:highvalue");
QString searchCriteria(ui->lineEdit->text());
searchCriteria = '%' + searchCriteria + '%';
qDebug()<<"What to search?: "<<searchCriteria;
query.bindValue(":search1", searchCriteria);
query.bindValue(":search2", searchCriteria);
query.bindValue(":search3", searchCriteria);
query.bindValue(":lowvalue", current_book_page*10);
query.bindValue(":highvalue", 10+current_book_page*10);
qDebug()<<"search query: "<<query.exec();
qDebug()<<query.lastError();
booksModel->setQuery(query);
qDebug()<<"rows count: "<<booksModel->rowCount();
closeDB();
}
int main(void)
{
int field;
int i;
char recv[100];
if (connectDB() < 0) {
return -1;
}
//mysql_set_character_set(conn, "utf8");
if (runQuery("select * from fish")) {
return -2;
}
field = mysql_num_fields(res);
while ((row = mysql_fetch_row(res))) {
for (i = 0; i < field; i++) {
printf("%s ", row[i] ? row[i] : "NULL");
}
printf("\n");
}
printf("MySQL Client Version: %s\n", mysql_get_client_info());
closeDB();
return 0;
}
void process_choice(int choice){
BufferManager *bu = BufferManager::getBufferManager();
float hr = -1;
switch(choice){
case 1:
initCache(bu);
break;
case 2:
createDB(bu);
break;
case 3:
openDB(bu);
break;
case 4:
readDB(bu);
break;
case 5:
writeDB(bu);
break;
case 6:
expandDB(bu);
break;
case 7:
commitDB(bu);
break;
case 8:
closeDB(bu);
break;
case 9:
commitCache(bu);
break;
case 10:
releaseCache(bu);
break;
case 11:
dropDB(bu);
break;
case 12:
cout<<endl<<"Enter Cache id: ";
cin>>choice;
(*bu).printHex(choice, (*bu).getPageSize());
break;
case 13:
(*bu).printCacheHeaders();
break;
case 14:
hr = (*bu).getHitRate();
if(hr!=-1)
cout<<endl<<"\tHit rate is: "<<hr*100.0<<" %"<<endl;
else {
cout<<endl<<"\tHit rate cannot be calculated because cache";
cout<<" is either not initialized, or no operation";
cout<<" has been performed on cache yet."<<endl;
}
break;
case 15:
// releaseCache(bu);
break;
}
}
void MainWindow::changeDB(const QString & dbf){
closeDB();
this->dbf = dbf;
connectDB();
initWidget();
loadMoneyTree();
loadEventToLog();
}
void freeIndex (Index S, bool libobj)
{ vpt *tree = (vpt*)S;
free (tree->descr);
freevpt (&tree->node);
free (tree);
if (libobj) closeDB();
}
int BufferManager::expandDB(int mdtID, long size){
int dbStatus = closeDB(mdtID);
if( dbStatus == 1 || dbStatus == -2){
mdtID=openDB(mdt[mdtID].dbPath, mdt[mdtID].dbName);
if(mdt[mdtID].isopen){
mdt[mdtID].fd.seekp(1, ios::end);
for(int i=0; i<(size-1); i++){
mdt[mdtID].fd.write("", 1);
}
closeDB(mdtID);
mdtID = openDB(mdt[mdtID].dbPath, mdt[mdtID].dbName);
}
return mdtID;
}
else
return dbStatus;
}
bool DataLayer::addEmployee(QString cnic){
if(connectDB()){
closeDB();
return true;
}
else{
return false;
}
}
void MainWindow::clear_Cart()
{
openDB();
QSqlQuery query;
query.prepare("delete from cart");
qDebug()<<"Clear cart: "<<query.exec();
qDebug()<<query.lastError();
update_tableView_Cart();
closeDB();
}
void MainWindow::on_actionConnect_Database_2_triggered(){
QString path = QFileDialog::getOpenFileName(this,"Connect database",".","Database (*.db);;All file (*.*)");
if(path != ""){
this->dbf = path;
closeDB();
connectDB();
initWidget();
loadMoneyTree();
loadEventToLog();
}
}
bool Product::judgeExistProductId(string productId)
{
openDB();
openDB();
int result;
char * errmsg = NULL;
char **dbResult; //是 char ** 类型，两个*号
int nRow, nColumn;
string SQLCode = "select * from product where productId = \""
+ productId + "\";";
result = sqlite3_get_table(db, SQLCode.c_str(), &dbResult, &nRow, &nColumn, &errmsg);
closeDB();
if (nRow > 0)
return true;
else
return false;
closeDB();
}
void MainWindow::on_pushButton_3_clicked() //buy
{
openDB();
QSqlQuery query;
query.prepare("select wallet from customer where customer_id = :id");
query.bindValue(":id", current_customer_ID);
qDebug()<<"purchase |wallet| query exec: "<<query.exec();
qDebug()<<query.lastError();
query.next();
double wallet = query.value(0).toDouble();
QSqlQuery query1;
query1.prepare("select sum(price) from cart");
query1.exec();
query1.next();
double price = query1.value(0).toDouble();
closeDB();
if (wallet < price)
QMessageBox::information(0, "Warning", "Insufficient funds");
else
{
QMessageBox msgBox;
msgBox.setText("Buy all books in the cart?");
msgBox.addButton(QMessageBox::Ok); msgBox.addButton(QMessageBox::Cancel);
int status = msgBox.exec();
if (status == QMessageBox::Ok)
{
openDB();
QSqlQuery queryBuy;
queryBuy.prepare("call BUY_CART(:cust_id)");
queryBuy.bindValue(":cust_id", current_customer_ID);
qDebug()<<"Buy cart: "<<queryBuy.exec();
qDebug()<<queryBuy.lastError();
closeDB();
update_wallet();
clear_Cart();
update_tableView_History();
}
}
}
void MainWindow::update_wallet()
{
openDB();
QSqlQuery query;
query.prepare("select wallet from customer where customer_id = :id");
query.bindValue(":id", current_customer_ID);
qDebug()<<"update_wallet query exec: "<<query.exec();
qDebug()<<query.lastError();
query.next();
double wallet = query.value(0).toDouble();
closeDB();
ui->label_2->setText(QString::number(wallet, 'f', 2) + '$');
}
int main(void){
//Declara e inicia database
database_t db;
initDB(&db);
//Loop do Menu Principal
while(menu_principal(&db));
//Encerra database, liberando todas as estruturas
closeDB(&db);
return 0;
}
bool MainWindow::saveChangesBeforeProceeding(const QString &title, bool close)
{
if (current_db_open && this->isWindowModified()) {
QMessageBox message(this);
message.setWindowTitle(title);
message.setWindowModality(Qt::WindowModal);
message.setWindowFlags(message.windowFlags() | Qt::Sheet);
message.setIcon(QMessageBox::Information);
message.setText(tr("The database has been modified."));
message.setInformativeText(tr("Do you want to save your changes?"));
message.addButton(tr("&Save"), QMessageBox::AcceptRole);
message.addButton(tr("&Discard"), QMessageBox::DestructiveRole);
message.addButton(tr("Cancel"), QMessageBox::RejectRole);
switch (message.exec()) {
case 0: // Save
current_db_open = false;
save();
if (close) {
closeDB();
}
return false;
break;
case 1: // Discard
current_db_open = false;
if (close) {
closeDB();
}
return false;
break;
case 2: // Cancel
return true;
break;
}
} else if (current_db_open && (!this->isWindowModified())) {
if (close) { closeDB(); } return false;
}
return false;
}
void MainWindow::delete_book_from_cart(int row_index)
{
openDB();
QSqlQuery query;
query.prepare("call DELETE_BOOK_FROM_CART(:isbn, :cur_customer_id)");
QModelIndex index = cartModel->index(row_index, 2);
QString value_isbn = cartModel->itemData(index)[0].toString();
query.bindValue(":isbn", value_isbn);
query.bindValue(":cur_customer_id", current_customer_ID);
qDebug()<<"delete book from cart: "<<query.exec();
qDebug()<<query.lastError();
closeDB();
update_tableView_Cart();
}
FLSqlDatabase::~FLSqlDatabase()
{
if (manager_) {
manager_->finish();
delete manager_;
}
if (managerModules_) {
managerModules_->finish();
delete managerModules_;
}
closeDB();
}
void MainWindow::add_bundle_to_cart(int row_index)
{
openDB();
QSqlQuery query;
query.prepare("call ADD_BUNDLE_TO_CART(:customer_id, :bundle_id)");
QModelIndex index = bundlesModel->index(row_index, 2);
QString value_bundle_id = bundlesModel->itemData(index)[0].toString();
query.bindValue(":customer_id", current_customer_ID);
query.bindValue(":bundle_id", value_bundle_id);
qDebug()<<"add bundle to cart: "<<query.exec();
qDebug()<<query.lastError();
closeDB();
update_tableView_Cart();
}
/**
* @brief Deletes the database.
*
* @return bool
*/
bool DatabaseManager::deleteDB()
{
debug("[DatabaseManager] deleteDB");
closeDB();
#ifdef Q_OS_LINUX
QString path(QDir::home().path());
path.append(QDir::separator()).append("movie-project.sqlite");
path = QDir::toNativeSeparators(path);
return QFile::remove(path);
#else
// Remove created database binary file
return QFile::remove("movie-project.sqlite");
#endif
}
