bool Finder::rowCount(QXlsx::Document &schedule,int & lastRow)
{
for (int row=7; row<65536; ++row)
{
bool abort = m_abort;
if (abort) {
removeCopiedFiles();
emit finished(false);
return false;
}
if(QXlsx::Cell *cell=schedule.cellAt(row, 6))
{
if(cell->value() == QVariant("Masa"))
{
lastRow = row - 2;
break;
}
}
if(m_searchCriterion == "Others") {
if(QXlsx::Cell *cell=schedule.cellAt(row, 10))
if(!cell->value().toString().isEmpty()){
QStringList list = cell->value().toString().split(" ");
if(list.back()!="")m_copartnerSet.insert(cell->value().toString().toLower());
else m_copartnerSet.insert(cell->value().toString().toLower().replace(" ",""));
}
}
}
return true;
}
bool Finder::checkSchedule(QXlsx::Document &schedule)
{
QString orderDigit = schedule.cellAt(6, 2)->value().toString();
QString drawingNr = schedule.cellAt(6, 3)->value().toString();
QString cooperator = schedule.cellAt(6, 10)->value().toString();
if( orderDigit.contains("L.p.") && drawingNr.contains("Nr rys.") && cooperator.contains("Kooperant") )
return true;
else
return false;
}
void MainWindow::checkExcelFile(QString file_name)
{
bool debug = false;
QXlsx::Document * xlsx = new QXlsx::Document(file_name);
QSqlQuery query;
if (!xlsx->selectSheet("Check"))
{
return;
}
QXlsx::CellRange range = xlsx->dimension();
int rowCount = range.rowCount();
// int columnCount = range.columnCount();
int columnCount = visible_columns.at(0);
/*
if (check_box_check->isChecked() &&
columnCount != visible_columns.at(0))
{
QMessageBox::information(this,tr(""), tr("Check columns do not fit database"));
}
*/
if (debug)
{
QMessageBox::information(this,tr("Rows * Columns"),
QString::number(rowCount) + tr(" * ") + QString::number(columnCount));
}
int newCount = 0;
int insertCount = 0;
for (int i = 1; i <= rowCount; ++i)
{
QString name = xlsx->read(i, 1).toString();
if (debug)
{
QMessageBox::information(this,tr("name"), name);
}
SearchResult result = searchCompany(name);
if (debug)
{
QMessageBox::information(this,tr("Search result: "), result.information);
}
if (result.color != Qt::white)
{
for (int j = 1; j <= columnCount; ++j)
{
QVariant value = xlsx->read(i, j);
if (!value.isValid())
{
continue;
}
QXlsx::Format format = xlsx->cellAt(i, j)->format();
format.setPatternBackgroundColor(result.color);
xlsx->write(i, j, value, format);
}
// Department;
xlsx->write(i, columnCount + 1, result.department);
QXlsx::Format format = xlsx->cellAt(i, columnCount + 1)->format();
format.setPatternBackgroundColor(result.color);
xlsx->write(i, columnCount + 1, result.department, format);
// Row(s)
QVariant value(result.information);
xlsx->write(i, columnCount + 2, value);
format = xlsx->cellAt(i, columnCount + 2)->format();
format.setPatternBackgroundColor(result.color);
xlsx->write(i, columnCount + 2, value, format);
}
if ((result.color == Qt::white) // add to database
&& check_box_check->isChecked())
{
newCount++;
QString q = "INSERT INTO " + table_names.at(0) + " (";
for (int k = 1; k < column_names.at(0).size(); ++k)
{
q += column_names.at(0).at(k).first;
if (k < column_names.at(0).size() - 1)
{
q += ", ";
}
else
{
q += ") VALUES(";
}
}
for (int k = 1; k < column_names.at(0).size(); ++k)
{
q += ":" + column_names.at(0).at(k).first;
if (k < column_names.at(0).size() - 1)
{
q += ", ";
}
else
{
q += ");";
}
}
query.prepare(q);
// qDebug() << q;
QString name = "";
for (int k = 1; k <= visible_columns.at(0); ++k)
{
QVariant value = xlsx->read(i, k);
query.bindValue(":" + column_names.at(0).at(k).first, value);
// qDebug() << ":" + column_names.at(0).at(k).first << " = " << value.toString();
if (column_names.at(0).at(k).first == "Name")
{
name = value.toString();
}
}
QString name1 = refineString1(name);
QString name2 = refineString2(name1);
query.bindValue(":Name1", QVariant(name1));
query.bindValue(":Name2", QVariant(name2));
if (query.exec())
{
insertCount++;
}
}
}
xlsx->save();
delete xlsx;
QMessageBox::information(this,tr(""),
tr("Check ok: new = ") + QString::number(newCount) +
tr("; insert = ") + QString::number(insertCount));
}
void Finder::findCells(QXlsx::Document &schedule, QXlsx::Cell *cell, int row, QMap<QString, QColor> &colorsMap)
{
QString currentCellNumber=0;
if(m_searchCriterion.isEmpty()) {
if(cell->format().patternBackgroundColor().toRgb() == colorsMap["nocolor"] && !cell->value().toString().isEmpty())
{
m_fileList << cell->value().toString().trimmed() + ".pdf";
currentCellNumber = schedule.cellAt(row, 2)->value().toString();
if(schedule.cellAt(row+1,2)->value().toString().contains(currentCellNumber)) {
m_fileList << cell->value().toString().trimmed() + "_wykaz.pdf";
m_fileList.swap(m_fileList.size()-1, m_fileList.size()-2);
}
}
if ((cell->format().patternBackgroundColor().toRgb() == colorsMap["orange"] && !cell->value().toString().isEmpty()) ||
(cell->format().patternBackgroundColor().toRgb() == colorsMap["orange2"] && !cell->value().toString().isEmpty()) ||
(cell->format().patternBackgroundColor().toRgb() == colorsMap["orange3"] && !cell->value().toString().isEmpty()))
{
m_fileList << cell->value().toString().trimmed() + ".pdf";
}
if((cell->format().patternBackgroundColor().toRgb() == colorsMap["yellow"] && !cell->value().toString().isEmpty() && schedule.cellAt(row, 10)->value().toString().contains("Sigma", Qt::CaseInsensitive)) ||
(cell->format().patternBackgroundColor().toRgb() == colorsMap["yellow2"] && !cell->value().toString().isEmpty() && schedule.cellAt(row, 10)->value().toString().contains("Sigma", Qt::CaseInsensitive)))
{
m_fileList << cell->value().toString().trimmed() + ".pdf";
}
}
else {
if(cell->format().patternBackgroundColor().toRgb() == colorsMap["nocolor"] &&
!cell->value().toString().isEmpty() &&
schedule.cellAt(row, 10)->value().toString().contains(m_searchCriterion, Qt::CaseInsensitive))
{
m_fileList << cell->value().toString().trimmed() + ".pdf";
currentCellNumber = schedule.cellAt(row, 2)->value().toString();
if(schedule.cellAt(row+1,2)->value().toString().contains(currentCellNumber)) {
m_fileList << cell->value().toString().trimmed() + "_wykaz.pdf";
m_fileList.swap(m_fileList.size()-1, m_fileList.size()-2);
}
}
if ((cell->format().patternBackgroundColor().toRgb() == colorsMap["orange"] &&
schedule.cellAt(row, 10)->value().toString().contains(m_searchCriterion, Qt::CaseInsensitive) &&
!cell->value().toString().isEmpty()) ||
(cell->format().patternBackgroundColor().toRgb() == colorsMap["orange2"] &&
schedule.cellAt(row, 10)->value().toString().contains(m_searchCriterion, Qt::CaseInsensitive) &&
!cell->value().toString().isEmpty()) ||
(cell->format().patternBackgroundColor().toRgb() == colorsMap["orange3"] &&
schedule.cellAt(row, 10)->value().toString().contains(m_searchCriterion, Qt::CaseInsensitive) &&
!cell->value().toString().isEmpty()))
{
m_fileList << cell->value().toString().trimmed() + ".pdf";
}
if((cell->format().patternBackgroundColor().toRgb() == colorsMap["yellow"] &&
!cell->value().toString().isEmpty() &&
schedule.cellAt(row, 10)->value().toString().contains(m_searchCriterion, Qt::CaseInsensitive)) ||
(cell->format().patternBackgroundColor().toRgb() == colorsMap["yellow2"] &&
!cell->value().toString().isEmpty() &&
schedule.cellAt(row, 10)->value().toString().contains(m_searchCriterion, Qt::CaseInsensitive)))
{
m_fileList << cell->value().toString().trimmed() + ".pdf";
}
}
}