bool SQLQueryData::UpdateCS(const QString& tempfirst, const QString& tempmid, const QString& templast, const QString& tempgender, const int& bday, const int& dday, const int& id)
{
SQLConnect database;
database.ConnectToDB();
QSqlQuery query = database.GetQuery();
query.prepare("UPDATE scientists SET first_name = ?, middle_name = ?, last_name = ?, gender =?, birth_year =?, death_year=? WHERE id=?");
query.bindValue(0,tempfirst);
if(tempmid != "")
{
query.bindValue(1,tempmid);
}
else
{
query.bindValue(1,QString());
}
query.bindValue(2,templast);
query.bindValue(3,tempgender);
query.bindValue(4,bday);
query.bindValue(5,dday);
query.bindValue(6,id);
if(query.exec())
{
database.Disconnect();
query.clear();
return true;
}
database.Disconnect();
query.clear();
return false;
}
void firstDiagnostics::on_buttonBox_accepted()
{
QSqlDatabase::database().transaction();
QSqlQuery query;
query.prepare("insert into diagnosis(fk_customer, fk_doctor, type, fk_partner, dateD, timeD) values(?, ?, ?, ?, ?, ?)");
query.addBindValue(customerID);
query.addBindValue(ui->doctorCombobox->itemData(ui->doctorCombobox->currentIndex()));
query.addBindValue(0);
query.addBindValue(partnerID);
query.addBindValue(ui->dateEdit->date());
query.addBindValue(ui->timeEdit->time());
query.exec();
query.clear();
query.prepare("select fk_customerCard from Customer where id = ?");
query.addBindValue(customerID);
query.exec();
int cardID = 0;
if(query.next())
cardID = query.value(0).toInt();
query.clear();
query.prepare("insert into custCardDoctor values(?, ?)");
query.addBindValue(cardID);
query.addBindValue(ui->doctorCombobox->itemData(ui->doctorCombobox->currentIndex()));
query.exec();
if(!QSqlDatabase::database().commit())
{
QSqlError err;
err = QSqlDatabase::database().lastError();
QSqlDatabase::database().rollback();
QMessageBox::warning(this, tr("Error"), err.text());
}
this->deleteLater();
}
int KraftDB::processSqlCommands( const SqlCommandList& commands )
{
int cnt = 0;
foreach( SqlCommand cmd, commands ) {
if( !cmd.message().isEmpty() ) {
emit statusMessage( cmd.message() );
}
if( !cmd.command().isEmpty() ) {
bool res = true;
QSqlQuery q;
q.clear();
res = q.exec(cmd.command()) || cmd.mayfail();
if ( res ) {
kDebug() << "Successful SQL Command: " << cmd.command() << endl;
cnt ++;
} else {
QSqlError err = q.lastError();
res = false;
kDebug() << "###### Failed SQL Command " << cmd.command() << ": " << err.text() << endl;
}
q.clear();
emit processedSqlCommand( res );
}
}
return cnt;
}
void login::on_login_login_clicked()
{
if(ui->userName->text().isEmpty() || ui->password->text().isEmpty()){
QMessageBox::information(this,tr("提示"),tr("不能为空"),QMessageBox::Ok);
ui->userName->clear();
ui->password->clear();
return;
}
QString uid = ui->userName->text();
QString pwd = ui->password->text();
QSqlQuery query;
query.prepare("select * from ADMIN where A_USER=:uid and A_KEY = :pwd");
query.bindValue(":uid",uid);
query.bindValue(":pwd",pwd);
query.exec();
if(query.next()){
MainWindow *m = new MainWindow();
m->show();
this->hide();
query.clear();
}
else{
QMessageBox::information(this,tr("提示"),tr("请重新输入"),QMessageBox::Ok);
ui->userName->clear();
ui->password->clear();
query.clear();
return;
}
}
QSqlQuery MainData::makeQuery(QString q_str,bool& made,bool silent)
{
QSqlQuery query;
try
{
query.exec(q_str);
if(query.isActive())
{
if(query.size()==0) throw "Неверный логин/пароль";
if(!query.first()) throw "Ошибка при запросе данных из БД";
made=true;
return query;
}
else
throw "Не могу выполнить запрос к БД";
}
catch(QString message)
{
if(!silent) MessageHandler::showMessage(MessageHandler::Error,message,0);
made=false;
query.clear();
}
catch(...)
{
if(!silent) MessageHandler::showMessage(MessageHandler::Error,"Ошибка при работе с БД\n"+q_str,0);
made=false;
query.clear();
}
}
void RequestHistory::groupHistory(const QString& name, const QVector<int> requestIds)
{
QSqlQuery *q = new QSqlQuery(m_database);
q->prepare("SELECT id FROM groups WHERE name = :name");
q->bindValue(":name", name);
query(q);
int index = 0;
if( q->first() ) {
index = q->value(0).toInt();
} else {
index = lastIncrementId("groups");
q->clear();
q->prepare(""
"INSERT INTO groups (id, name)"
" VALUES (:id, :name)"
);
q->bindValue(":id", index);
q->bindValue(":name", name);
query(q);
}
QString ids;
for (int i = 0; i < requestIds.size(); ++i) {
ids += QString::number(requestIds.at(i)) + ", ";
}
ids = ids.remove(ids.length()-2, 2);
q->clear();
q->prepare(QString("UPDATE requests SET group_id = :group_id WHERE id IN (%1)").arg(ids));
q->bindValue(":group_id", index);
query(q);
delete q;
}
void InsertTierDialog::on_buttonBox_accepted()
{
QSqlQuery q;
q.prepare("SELECT PK_Stall FROM STALL WHERE Stallart = ?");
q.bindValue(0,ui->stall->currentText());
q.exec();
q.first();
int stallpk = q.value(0).toInt();
q.clear();
q.prepare("SELECT PK_Futter FROM FUTTER WHERE Name = ?");
q.bindValue(0,ui->futter->currentText());
q.exec();
q.first();
int futterpk = q.value(0).toInt();
q.clear();
QString insertQuery =
QString("SELECT usp_TierHinzufuegen(%1,'%2','%3','%4',%5,'%6',%7,%8)")
.arg(
QString::number(stallpk),
ui->Name->text(),
ui->geburtsDatum->date().toString("yyyy-MM-dd"),
ui->anschaffungsDatum->date().toString("yyyy-MM-dd"),
QString::number(ui->gewicht->value()),
ui->tierart->currentText(),
QString::number(futterpk),
QString::number(ui->futterMenge->value())
);
if(!q.exec(insertQuery)) {
ErrorDialog() << q.lastError();
}
}
void MainWindow::updateTime()
{
// qDebug()<<"timer";
ui->actualTime->clear();
ui->schedulerTime->clear();
QSqlQuery query;
query.clear();
query.prepare("SELECT time(NOW())");
query.exec();
while(query.next())
{
ui->actualTime->setText(query.value(0).toString());
}
query.clear();
query.exec("CALL spGetSchedulerTime()");
while(query.next())
{
ui->schedulerTime->setText(query.value(0).toString());
}
}
void TheSQDMonitoring::SQDMupdateSQL()
{
QSqlQuery query;
query.clear();
//query.prepare("select LAST_INSERT_ID() from serialporttest");
query.prepare("select MAX(idserialtest) from serialporttest");
query.exec();
if(query.next())
{
QString IDVAL=query.value(0).toString();
query.clear();
query.prepare("select * from serialporttest where idserialtest=:idserialtest");
query.bindValue(":idserialtest",IDVAL);
query.exec();
if(query.next())
{
ui->SQDM_EquipmentID_lable->setText(query.value(2).toString());
ui->SQDM_EquipmentProductionNumber_LCDNumber->display(query.value(7).toString());
ui->SQDM_EquipmentTemperture_QwtTherm->setValue(query.value(4).toFloat());
ui->SQDM_EquipmentHumidity_QwtTherm->setValue(query.value(5).toFloat());
ui->SQDM_Equipmentelectric_LCDNumber->display(abs((query.value(6).toFloat()-512)/1024*5*100));
}
}
}
Request* RequestHistory::getRequest(int requestId)
{
QSqlQuery *q = new QSqlQuery(m_database);
q->prepare("SELECT * FROM requests WHERE id = :id");
q->bindValue(":id", requestId);
query(q);
if( !q->first() ) {
delete q;
throw false;//Request not found
}
Request *request = new Request(q->value("url").toString(), q->value("type").toString());
request->setResponse(q->value("response").toString());
request->setResponseCode(q->value("code").toInt());
request->setError(q->value("error").toString());
request->setMessage(q->value("message").toString());
QString gistId = q->value("gist_id").toString();
if (!gistId.isEmpty()) {
request->setGistId(q->value("gist_id").toString());
}
q->clear();
//load params
q->prepare("SELECT * FROM requests_params WHERE request_id = :id");
q->bindValue(":id", requestId);
query(q);
while (q->next()) {
request->addRequestParam(q->value("name").toString(), q->value("value").toString());
}
q->clear();
//load headers
q->prepare("SELECT * FROM request_headers WHERE request_id = :id");
q->bindValue(":id", requestId);
query(q);
while (q->next()) {
request->addRequestHeader(q->value("name").toString(), q->value("value").toString());
}
q->clear();
//load response headers
q->prepare("SELECT * FROM response_headers WHERE request_id = :id");
q->bindValue(":id", requestId);
query(q);
while (q->next()) {
request->addResponseHeader(q->value("name").toString(), q->value("value").toString());
}
q->clear();
q->prepare("SELECT * FROM request_raw WHERE request_id = :id");
q->bindValue(":id", requestId);
query(q);
if( q->first() ) {
request->setRaw(q->value("body").toString());
}
delete q;
return request;
}
bool historialClinico::buscar()
{
QSqlQuery query;
query.prepare("select * from HistorialClinico where idCliente=? and doctor=? and fecha=?");
query.bindValue(0,idCliente);
query.bindValue(1,doctor);
query.bindValue(2,fecha);
if(query.exec())
{
if(query.size()!=0)
{
query.first();
idHistorialClinico=query.value(0).toString();
query.clear();
query.prepare("select idMedidaHistorial from HistorialClinico_has_MedidaHistorial where idHistorialClinico=? and distancia=? and ojo=?");
query.bindValue(0,idHistorialClinico);
query.bindValue(1,"Cerca");
query.bindValue(2,"Derecho");
query.exec();
query.first();
mCercaDerecha.setIdMedidasHistorial(query.value(0).toString());
query.clear();
query.prepare("select idMedidaHistorial from HistorialClinico_has_MedidaHistorial where idHistorialClinico=? and distancia=? and ojo=?");
query.bindValue(0,idHistorialClinico);
query.bindValue(1,"Cerca");
query.bindValue(2,"Izquierdo");
query.exec();
query.first();
mCercaIzquierda.setIdMedidasHistorial(query.value(0).toString());
query.clear();
query.prepare("select idMedidaHistorial from HistorialClinico_has_MedidaHistorial where idHistorialClinico=? and distancia=? and ojo=?");
query.bindValue(0,idHistorialClinico);
query.bindValue(1,"Lejos");
query.bindValue(2,"Derecho");
query.exec();
query.first();
mLejosDerecha.setIdMedidasHistorial(query.value(0).toString());
query.clear();
query.prepare("select idMedidaHistorial from HistorialClinico_has_MedidaHistorial where idHistorialClinico=? and distancia=? and ojo=?");
query.bindValue(0,idHistorialClinico);
query.bindValue(1,"Lejos");
query.bindValue(2,"Izquierdo");
query.exec();
query.first();
mLejosIzquierda.setIdMedidasHistorial(query.value(0).toString());
if(mCercaDerecha.buscar()&&mCercaIzquierda.buscar()&&mLejosDerecha.buscar()&&mLejosIzquierda.buscar())
return true;
else
return false;
}
return false;
}
else
return false;
return false;
}
void MainWindow::on_searchButton_clicked()
{
int currentId = -1;
//Запрос на наличие записи
QSqlQuery *q = new QSqlQuery(db);
q->prepare("SELECT wtable.id_word FROM wtable WHERE word=?");
q->bindValue(0, ui->wordLine->text());
q->exec();
if (q->next())
{
// Идентификатор введенного слова
currentId = q->value(0).toInt();
// Сбрасываем запрос
q->clear();
QString str = "";
// Выполняем новый запрос, который вернет нам синонимы
q->prepare("SELECT wtable.word FROM wtable, stable WHERE stable.id_syn = wtable.id_word AND stable.id_word = ?");
q->bindValue(0, currentId);
q->exec();
if (q->next())
{
do
{
str.append("-- ").append(q->value(0).toString()).append("\n");
}
while (q->next());
}
else
{
q->clear();
q->prepare("SELECT wtable.word FROM wtable, stable WHERE stable.id_word = wtable.id_word AND stable.id_syn = ?");
q->bindValue(0, currentId);
q->exec();
while (q->next())
str.append("-- ").append(q->value(0).toString()).append("\n");
}
// Закинуть текст в виджет
ui->synList->setText(str);
}
else
QMessageBox::critical(0, "Ошибка", "Данного слова нет в базе");
}
/**
* @brief Database::AddRevenue
* Add amount to revenue attribute in stadiums table
* @param id stadiums id
* @param revenue of the stadium
* @return
*/
bool Database::AddRevenue(int id, double revenue)
{
QSqlQuery query;
query.prepare("select revenue from stadiums where id = :id");
query.bindValue(":id", id);
if(query.exec()){
if(query.next()){
double originalRev = query.record().field("revenue").value().toDouble();
query.clear();
query.prepare("update stadiums set revenue = :newRev where id = :id");
double newRev = originalRev + revenue;
query.bindValue(":newRev", newRev);
query.bindValue(":id", id);
return query.exec();
}
else
{
qDebug() << query.lastError().text();
return false;
}
}
else
{
qDebug() << query.lastError().text();
return false;
}
}
QString Prefix::getPath(const QString prefix_name) const{
QString value;
if (prefix_name == "Default"){
value = QDir::homePath();
value.append("/.wine");
return value;
}
QSqlQuery query;
query.prepare("SELECT path FROM prefix WHERE name=:prefix_name");
query.bindValue(":prefix_name", prefix_name);
if (query.exec()){
query.first();
if (query.isValid()){
if (query.value(0).toString().isEmpty()){
value = QDir::homePath();
value.append("/.wine");
} else {
value.append(query.value(0).toString());
}
}
} else {
qDebug()<<"SqlError: "<<query.lastError();
}
query.clear();
return value;
}
//**********************************************
st_qRes SendSimpleQueryStrWR(QSqlDatabase *db,const QString& q_str)
{
st_qRes result;
result.sel_data.clear();
st_svMAP col;
QSqlQuery *query = new QSqlQuery(*db);
if(!query->exec(q_str)){
db->rollback();
result.q_result = false;
qDebug() << query->lastError().databaseText();
qDebug() << query->lastError().driverText();
QMessageBox::critical(new QWidget,QObject::tr("Error"),"query: "+q_str+"\ndriver: "+
query->lastError().driverText()+
"\ndatabase: "+query->lastError().databaseText());
}
else{
result.q_result = true;
st_svMAP col;
while(query->next()){
for(int counter=0; counter < query->record().count() ;counter++){
col.map.insert(query->record().fieldName(counter),query->value(counter));
}
result.sel_data << col;
col.map.clear();
}
}
query->clear();
return result;
}
vector<computersabstract> ComputerManipulation::sortComputers(QString& str, char& choice)
{
//Create objects
SQLConnect database;
vector<computersabstract> returnvec;
//Connect to database and get query
database.ConnectToDB();
QSqlQuery query = database.GetQuery();
switch(choice)
{
case 1:
query.prepare(QString("SELECT * FROM scientists ORDER BY name, year").arg(str));
break;
case 2:
query.prepare(QString("SELECT * FROM scientists ORDER BY year, name").arg(str));
break;
case 3:
query.prepare(QString("SELECT * FROM scientists ORDER BY type, name").arg(str));
break;
}
query.exec();
FillcsVector(query,returnvec);
//Close the database and make sure that the vector has only values nothing empty.
database.Disconnect();
query.clear();
//return vector
return returnvec;
}
QSqlError DBHelper::updateCurrenciesTable(const QList<Currency>& list) {
QSqlQuery q;
for(QList<Currency>::const_iterator i=list.begin(); i!=list.end(); ++i) {
q.prepare(QString("select name from currencies where code=?"));
q.addBindValue(i->code);
q.exec();
if(q.next()) { q.clear(); continue; }
q.clear();
q.prepare(QString("insert into currencies (code, name) values (?, ?)"));
q.addBindValue(i->code);
q.addBindValue(i->name);
if(!q.exec()) return q.lastError();
}
return QSqlError();
}
bool PodcastStore::syncEpisodeWithDB(Episode* e)
{
QSqlQuery query;
QString cmd;
bool ret;
QTextStream(&cmd) << "SELECT * FROM podcasts WHERE podcastname='" << e->podcastName() << "' AND " <<
" episodename='" << e->name() << "'";
ret = query.exec(cmd);
if(!ret) return false;
if (query.next())
{
// this episode alrady exists in the database
return true;
} else
{
// need to create new record for the episode
query.clear();
e->setStatus(Episode::ENotDownloaded);
cmd.clear();
QTextStream(&cmd) << "INSERT INTO podcasts VALUES ('" << e->podcastName() << "', '" <<
e->name() << "', " << e->status() << ", " << e->size() << ", " <<
e->duration() << ", " << e->playPosition() << ")";
if(!query.exec(cmd)) return false;
}
return true;
}
void Card::deleteRecord(void){
QSqlDatabase db;
db.removeDatabase("qt_sql_default_connection");
db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("www.themindspot.com");
db.setPort(3306);
db.setDatabaseName("themind1_AMS");
db.setUserName("themind1_ams");
db.setPassword("nerdsrule");
db.open();
QSqlQuery record;
record.prepare("DELETE FROM SurveyPerson WHERE surveyPersonID = ?");
record.addBindValue(cardID->text().toInt());
record.exec();
record.clear();
db.close();
clearFields();
emit newRecord();
return;
}
std::vector< Profile > DatabaseFacade::getUsers()
{
std::vector<Profile> profiles;
if(!connected)
{
qDebug() << "Not connected to database";
return profiles;
}
QSqlQuery query;
query.exec("SELECT * FROM Gamers");
QSqlError err = query.lastError();
if(err.isValid())
qDebug() << err.databaseText() << "\n" << err.driverText() << "\n" << err.nativeErrorCode();
while (query.next())
{
int id = query.value(0).toInt();
QString name = query.value(1).toString();
QPixmap picture;
picture.loadFromData(query.value(2).toByteArray());
profiles.emplace_back(id, name, picture);
}
query.clear();
return profiles;
}
void Prefix::fixPrefixPath(){
QSqlQuery query;
query.prepare("SELECT id, path FROM prefix");
if (query.exec()){
while (query.next()) {
if (query.value(0).isValid()){
QString path = query.value(1).toString();
if (path.length()>1){
if (path.right(1)=="/"){
path = path.left(path.length()-1);
QSqlQuery fixquery;
fixquery.prepare("UPDATE prefix SET path=:path WHERE id=:id");
fixquery.bindValue(":path", path);
fixquery.bindValue(":id", query.value(0).toString());
if (!fixquery.exec()){
qDebug()<<"SqlError: "<<fixquery.lastError();
}
}
}
}
}
} else {
qDebug()<<"SqlError: "<<query.lastError();
}
query.clear();
}
bool otros::actualizar()
{
Sesion* s=Sesion::getSesion();
QSqlQuery query;
query.prepare("UPDATE Producto SET codigo=?,descripcion=?,precioCompra=?,precioVenta=?,precioDescuento=?,accesorios=?,stock=?,observaciones=?,Estado_idEstado=?,Marca_idMarca=?,idColaborador WHERE idProducto=?");
query.bindValue(0,codigo);
query.bindValue(1,descripcion);
query.bindValue(2,precioCompra);
query.bindValue(3,precioVenta);
query.bindValue(4,precioDescuento);
query.bindValue(5,accesorios);
query.bindValue(6,stock);
query.bindValue(7,observaciones);
query.bindValue(8,pEstado.getIdEstado());
query.bindValue(9,pMarca.getIdMarca());
query.bindValue(10,s->getIdColaborador());
query.bindValue(11,idProducto);
if(query.exec())
{
query.clear();
query.prepare("UPDATE Otros SET Color_idColor=?,Talla_idTalla=?,Calidad_idCalidad=?,TipoOtros_idTipoOtros=?,Genero_idGenero=? WHERE Producto_idProducto=?");
query.bindValue(0,pColor.getIdColor());
query.bindValue(1,pTalla.getIdTalla());
query.bindValue(2,pCalidad.getIdCalidad());
query.bindValue(3,pTipoOtros.getIdTipoOtros());
query.bindValue(4,pGenero.getIdgenero());
query.bindValue(5,idProducto);
if(query.exec())
return true;
else
return false;
}
else
return false;
}
vector<ComputerScientist> SQLQueryData::SearchCS(const QString& search)
{
SQLConnect database;
database.ConnectToDB();
QSqlQuery query = database.GetQuery();
vector<ComputerScientist> returnvec;
query.prepare("SELECT * FROM scientists WHERE ((first_name LIKE '%'||?||'%') OR (middle_name LIKE '%'||?||'%') OR "
"(last_name LIKE '%'||?||'%') OR (birth_year LIKE '%'||?||'%') OR (death_year LIKE '%'||?||'%')) ORDER BY first_name");
query.bindValue(0,search);
query.bindValue(1,search);
query.bindValue(2,search);
query.bindValue(3,search);
query.bindValue(4,search);
query.exec();
FillcsVector(query,returnvec);
returnvec.shrink_to_fit();
database.Disconnect();
query.clear();
return returnvec;
}
QList<QString> TPR_Bank_Dao::getReport(QDateTime from, QDateTime to)
{
QList<QString> result;
bool bTransaction = m_dbBank.transaction();
if ( bTransaction ){
//:from "2011-03-05 00:00:00"
//:to "2011-03-05 24:00:00"
QSqlQuery query;
QString dtFormat("yyyy-MM-dd hh:mm:ss");
query.prepare("SELECT * FROM tipra WHERE tpr_date BETWEEN :from AND :to ORDER BY tpr_date;");
query.bindValue( ":from", from.toString(dtFormat) );
query.bindValue( ":to", to.toString(dtFormat) );
query.exec();
while (query.next()) {
QString strRow = QString("%1;%2;%3;%4;%5;")
.arg(query.value(0).toString())
.arg(query.value(1).toString())
.arg(query.value(2).toString())
.arg(query.value(3).toString())
.arg(query.value(4).toString());
result.push_back( strRow );
}
query.clear();
m_dbBank.commit();
} else {
std::cerr<< "TPR_Bank_Dao::getReport(): Cannot open transaction;" << std::endl;
}
return result;
}
Movie Database::getMovie(QString title) {
QSqlQuery getMovieQuery;
Movie temp;
getMovieQuery.prepare("SELECT * FROM movieDB where originalTitle LIKE :title ;");
getMovieQuery.bindValue(":title", "%"+title+"%");
getMovieQuery.exec();
getMovieQuery.last();
if(getMovieQuery.at()+1 == 1) {
temp.setTMDB_ID(getMovieQuery.value(0).toInt());
temp.setYear(getMovieQuery.value(3).toInt());
temp.setOriginalTitle(getMovieQuery.value(2).toString());
temp.setTitle(getMovieQuery.value(1).toString());
temp.setRuntime(getMovieQuery.value(4).toInt());
temp.setSynopsis(getMovieQuery.value(5).toString());
}else if(getMovieQuery.at()+1 == -1) {
getMovieQuery.clear();
getMovieQuery.prepare("SELECT * FROM movieDB where Title LIKE :title ;");
getMovieQuery.bindValue(":title", "%"+title+"%");
getMovieQuery.exec();
getMovieQuery.last();
if(getMovieQuery.at()+1 == 1) {
temp.setTMDB_ID(getMovieQuery.value(0).toInt());
temp.setYear(getMovieQuery.value(3).toInt());
temp.setOriginalTitle(getMovieQuery.value(2).toString());
temp.setTitle(getMovieQuery.value(1).toString());
temp.setRuntime(getMovieQuery.value(4).toInt());
temp.setSynopsis(getMovieQuery.value(5).toString());
}
}
return temp;
}
std::vector< Score > DatabaseFacade::getHightScores( const QString& gameName_, int limit_ )
{
std::vector< Score > scores;
if(!connected)
{
qDebug() << "Not connected to database";
return scores;
}
QSqlQuery query;
query.exec(queryGetHighScores.arg(gameName_));
QSqlError err = query.lastError();
if(err.isValid())
qDebug() << err.databaseText() << "\n" << err.driverText() << "\n" << err.nativeErrorCode();
while (query.next())
{
QString name = query.value(0).toString();
QPixmap picture;
picture.loadFromData(query.value(1).toByteArray());
int score = query.value(2).toInt();
scores.emplace_back(name, picture, score);
}
query.clear();
return scores;
}
void SensorGraph::drawPlot() {
QStringList list;
QSqlQuery q;
q.prepare("SELECT DISTINCT address FROM Temps"); //get each adress
q.exec();
while (q.next()) {
QString address = q.value(0).toString();
list.append(address); //add each address to the list
//qDebug() << "address : " << q.value(0).toString();
q.clear();
q.prepare("SELECT temp, time FROM Temps WEHRE address = :address ORDER BY time DESC LIMIT 200");
q.bindValue(":address",address);
q.exec();
while (q.next()) {
//add temps to array of doubles
//add timestamps to qwt plot label and convert timestamp to ordered double
}
}
if(!list.isEmpty()){
}
}
void MainWindow::on_btnDeleteAccount_clicked()
{
QString selectedAccountName = getAccountName();
QString warningMessage = qApp->tr("This will delete account ");
warningMessage.append(selectedAccountName);
if (QMessageBox::warning(0,qApp->tr("Delete Account"),
warningMessage,
QMessageBox::Ok | QMessageBox::Cancel)
== QMessageBox::Ok)
{
int accountId = getAccountId();
//remove any transactions associated with this account
QSqlQuery q;
q.prepare("DELETE FROM trans WHERE id_account = ?");
q.addBindValue(accountId);
if(!q.exec())
{
transactionFailedError(qApp->tr("Could not delete account"));
}
//remove the account
q.clear();
q.prepare("DELETE FROM account WHERE pk_uid = ?");
q.addBindValue(accountId);
if(!q.exec())
{
transactionFailedError(qApp->tr("Could not delete account"));
}
}
refreshAccountTree();
}
void Peminjaman::cariPeminjaman(QStandardItemModel *theModel, const QString &kolom, const QString &value, QString status, const QString &hal){
model = theModel;
kolomP = kolom;
valueP = value;
statusP = status;
halP = hal;
QSqlQuery queryPinjam;
QSqlQuery countData;
QString currentPage = QString::number(hal.toInt()*50);
if(queryPinjam.exec("SELECT * FROM tbl_peminjaman WHERE "+kolom+" LIKE \"%"+value+"%\" AND kembali LIKE \"%"+status+"%\" LIMIT "+currentPage+", 50")){
//qDebug()<<"Cari Query: "+queryPinjam.lastQuery();
theModel->clear();
int counterPinjam=0;
// column index
int siswa = queryPinjam.record().indexOf("siswa");
int buku = queryPinjam.record().indexOf("buku");
int tgl_pinjam = queryPinjam.record().indexOf("tgl_pinjam");
int tgl_tempo = queryPinjam.record().indexOf("tgl_tempo");
int tgl_kembali = queryPinjam.record().indexOf("tgl_kembali");
int kembali = queryPinjam.record().indexOf("kembali");
while(queryPinjam.next()){
theModel->setItem(counterPinjam,0,new QStandardItem(QString(queryPinjam.value(siswa).toString())));
theModel->setItem(counterPinjam,1,new QStandardItem(QString(queryPinjam.value(buku).toString())));
this->buku->setKode(queryPinjam.value(buku).toString());
theModel->setItem(counterPinjam,2,new QStandardItem(QString( this->buku->getData("judul") )));
QString tglPinjam = QLocale(QLocale::Indonesian,QLocale::Indonesia).toString(QDate::fromString(queryPinjam.value(tgl_pinjam).toString(),"yyyy-MM-dd"),"dd MMMM yyyy");
theModel->setItem(counterPinjam,3,new QStandardItem(QString(tglPinjam)));
QString tglTempo = QLocale(QLocale::Indonesian,QLocale::Indonesia).toString(QDate::fromString(queryPinjam.value(tgl_tempo).toString(),"yyyy-MM-dd"),"dd MMMM yyyy");
theModel->setItem(counterPinjam,4,new QStandardItem(QString(tglTempo)));
QString tglKembali = QLocale(QLocale::Indonesian,QLocale::Indonesia).toString(QDate::fromString(queryPinjam.value(tgl_kembali).toString(),"yyyy-MM-dd"),"dd MMMM yyyy");
theModel->setItem(counterPinjam,5,new QStandardItem(QString(tglKembali)));
QString status;
if(queryPinjam.value(kembali).toString() == "1"){
status = "Sudah Kembali";
}else if(queryPinjam.value(kembali).toString() == "0"){
status = "Belum Kembali";
}
theModel->setItem(counterPinjam,6,new QStandardItem(QString(status)));
counterPinjam++;
}
countResult = queryPinjam.size();
//Count Peminjam
countData.exec("SELECT COUNT(no_induk) FROM tbl_anggota WHERE no_induk = \""+value+"\" ");
countData.next();
countSiswa = countData.value(0).toInt();
countData.clear();
}
}
void DatabasePersister::persistImageFileData(DatabasePersistRequest* request)
{
if (databaseConnection->getDatabase().open())
{
QSqlQuery query;
query.prepare("INSERT INTO images (filename, timedate, location) VALUES (:filename, :timestamp, :location)");
query.bindValue(":filename", request->originalImageFilename);
query.bindValue(":timestamp", request->timeStamp);
query.bindValue(":location", QString("cam"));
if(!query.exec())
{
QString error("inserting image data.");
throw ErrorException(error, 0);
}
query.clear();
query.prepare("SELECT id FROM images WHERE filename = :filename");
query.bindValue(":filename", request->originalImageFilename);
if(!query.exec())
{
QString error("inserting image data.");
throw ErrorException(error, 1);
}
query.first();
qint64 imageId = query.value(0).toLongLong();
for (unsigned int i = 0; i < request->facesFilenames.size(); i++)
{
query.clear();
query.prepare("INSERT INTO faces (filename, image_id) VALUES (:filename, :imageId)");
query.bindValue(":filename", request->facesFilenames[i]);
query.bindValue(":imageId", imageId);
if(!query.exec())
{
QString error("inserting face data.");
throw ErrorException(error, 2);
}
}
}
else
{
QString error("database closed.");
throw ErrorException(error, 3);
}
}