void DataConnect::borrowInsertNew(Borrower b)
{
QSqlDatabase mydb = QSqlDatabase::addDatabase("QSQLITE");
mydb.setDatabaseName("data.db");
if (mydb.open()) {
std::cout<<"Database is open\n";
} else {
std::cout<<"Database is not open\n";
}
QString name = QString::fromStdString(b.getName());
QString type = QString::fromStdString(b.getType());
QString department = QString::fromStdString(b.getDepartment());
QString mobile = QString::fromStdString(b.getMobileNumber());
int itemBorr = b.getItemBorr();
int itemLate = b.getItemLate();
QString itemString = QString::fromStdString(b.getItemString());
int id = b.getId();
QSqlQuery comman;
QString exc = QString("INSERT INTO borrowers (name , type, department,")
+QString("mobileNumber, itemBorr, itemLate ,itemString, id) VALUES ")
+QString("('")+ name +QString("','")+type+QString("','")+department
+QString("','")+ mobile +QString("',")+ QString::number(itemBorr) +QString(",")
+QString::number(itemLate)+QString(",'") +itemString+ QString("',")+ QString::number(id) +QString(");");
std::cout << exc.toStdString()<< std::endl;
if(comman.exec(exc)) {
std::cout <<"insert success borr!\n";
} else {
std::cout << "insert failure borr!\n";
}
mydb.close();
}
void Report::reportOverdue()
{
std::vector <Borrower *> borrowers;
borrowers = Service::getAllBorrowers();
// Javascript column headers array
QString columns = "var columns = [ "
"{ 'sTitle': 'ID' }, "
"{ 'sTitle': 'Name' }, "
"{ 'sTitle': 'Mobile' }, "
"{ 'sTitle': 'Type' }, "
"{ 'sTitle': 'Department' }, "
"{ 'sTitle': 'Overdue items' }, "
"];";
// Start javascript data array
QString data = "var data = [ ";
// Get data for the js array
for (int i = 0; i < borrowers.size(); ++i) {
Borrower *borrower = borrowers.at(i);
QString overdues = Service::getOverdueTitlesBy(borrower);
// Skip if this borrower has no overdues
if (overdues.isEmpty()) {
continue;
}
// ID, name, mobile, borrower type, department name, overdue titles
data.append(QString("[%1, '%2', '%3', '%4', '%5', '%6']")
.arg(borrower->getId())
.arg(borrower->getName())
.arg(borrower->getPhone())
.arg(borrower->getBorrowerType()->getName())
.arg(borrower->getDepartment()->getName())
.arg(overdues)
);
if (i != borrowers.size() - 1) {
data.append(",");
}
}
// Close js data array
data.append(" ];");
// Specify report name
data.append(" tableName = 'Overdue'; ");
data.append(columns);
generateReport(data);
}
void Report::reportBorrowers()
{
std::vector <Borrower *> borrowers;
borrowers = Service::getAllBorrowers();
// Javascript column headers array
QString columns = "var columns = [ "
"{ 'sTitle': 'ID' }, "
"{ 'sTitle': 'Name' }, "
"{ 'sTitle': 'Mobile' }, "
"{ 'sTitle': 'Type' }, "
"{ 'sTitle': 'Department' }, "
"{ 'sTitle': 'Items Borrowed (Past)' }, "
"{ 'sTitle': 'Items Borrowing' }, "
"];";
// Start javascript data array
QString data = "var data = [ ";
// Get data for the js array
for (int i = 0; i < borrowers.size(); ++i) {
Borrower *borrower = borrowers.at(i);
std::vector<int> nums
= Service::getNumbersOfItemsBorrowedBy(borrower);
// ID, name, mobile, borrower type, department name, items borrowed, items borrowing
data.append(QString("[%1, '%2', '%3', '%4', '%5', '%6', '%7']")
.arg(borrower->getId())
.arg(borrower->getName())
.arg(borrower->getPhone())
.arg(borrower->getBorrowerType()->getName())
.arg(borrower->getDepartment()->getName())
.arg(QString("Borrowed %1")
.arg(nums[0]))
.arg(QString("Borrowing %1")
.arg(nums[1]))
);
if (i != borrowers.size() - 1) {
data.append(",");
}
}
// Close js data array
data.append(" ];");
// Specify report name
data.append(" tableName = 'Borrower'; ");
data.append(columns);
generateReport(data);
}
void MultimediaCollection::addBorrower(Borrower b)
{
borrowers.push_back(b);
try {
sql::Driver *driver;
sql::Connection *con;
sql::PreparedStatement *prepareStmt;
driver = get_driver_instance();
con = driver->connect("tcp://127.0.0.1:3306", "root", "dhis");
con->setSchema("test");
prepareStmt = con->prepareStatement(
"INSERT INTO borrower(id, name, type, department, mobile, numborrow, numreturnlate)"
"VALUES (?,?,?,?,?,?,?)");
prepareStmt->setString(1, b.getId());
prepareStmt->setString(2, b.getName());
prepareStmt->setString(3, b.getType());
prepareStmt->setString(4, b.getDepartment());
prepareStmt->setString(5, b.getMobile());
prepareStmt->setInt(6, b.getNumItemBorrow());
prepareStmt->setInt(7, b.getNumItemLateBorrow());
prepareStmt->execute();
delete prepareStmt;
delete con;
} catch (sql::SQLException &e) {
cout << "# ERR: SQLException in " << __FILE__;
cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
cout << "# ERR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << " )" << endl;
}
}
