andre@3: /* Copyright (C) 2016 by ETH Zürich
andre@3:  * Software engineering by Intevation GmbH
andre@3:  *
andre@3:  * This file is Free Software under the GNU GPL (v>=2)
andre@3:  * and comes with ABSOLUTELY NO WARRANTY!
andre@3:  * See LICENSE.txt for details.
andre@3:  */
andre@3: 
andre@3: #include "converter.h"
andre@3: #include <QDebug>
andre@3: #include <QRegularExpression>
andre@3: #include <QRegularExpressionMatch>
andre@23: #include <QTextDocument>
andre@23: #include <QPrinter>
andre@26: #include <QImage>
andre@26: #include <QPainter>
andre@3: 
andre@3: #include "xlsxdocument.h"
andre@3: #include "xlsxconditionalformatting.h"
andre@3: 
andre@3: #include "constants.h"
andre@3: 
andre@3: QTXLSX_USE_NAMESPACE
andre@3: 
andre@38: Converter::Converter(const QString &input, const QStringList &outputs,
andre@38:                      const QString &title):
andre@3:     QThread(Q_NULLPTR),
andre@3:     mInput(input),
andre@38:     mOutputs(outputs),
andre@3:     mTitle(title)
andre@3: {
andre@3:     mTitleFmt.setFontUnderline(Format::FontUnderlineSingle);
andre@3:     mTitleFmt.setFontSize(18);
andre@3:     mTitleFmt.setFontName("Calibri");
andre@3:     mTitleFmt.setFontBold(true);
andre@3:     mTitleFmt.setVerticalAlignment(Format::AlignTop);
andre@3: 
andre@23: 
andre@3:     mQuestionFmt.setFontSize(11);
andre@3:     mQuestionFmt.setFontName("Calibri");
andre@3:     mQuestionFmt.setFontBold(true);
andre@3:     mQuestionFmt.setTopBorderStyle(Format::BorderThin);
andre@6:     mQuestionFmt.setBottomBorderStyle(Format::BorderThin);
andre@3:     mQuestionFmt.setTextWarp(true);
andre@3: 
andre@3:     mAnswerChoiceFmt.setFontSize(11);
andre@3:     mAnswerChoiceFmt.setFontName("Calibri");
andre@23:     mAnswerChoiceFmt.setHorizontalAlignment(Format::AlignLeft);
andre@6:     mAnswerChoiceFmt.setTextWarp(true);
andre@3: 
andre@3:     mChoiceTextFmt = mAnswerChoiceFmt;
andre@3:     mChoiceTextFmt.setVerticalAlignment(Format::AlignVCenter);
andre@3: 
andre@15:     mChoiceVotesFmt = mChoiceTextFmt;
andre@15:     mChoiceVotesFmt.setFontSize(10);
andre@15: 
andre@6:     mFreeTextFmt = mQuestionFmt;
andre@6:     mFreeTextFmt.setFontBold(false);
andre@3: 
andre@6:     mAnswerTextFmt = mQuestionFmt;
andre@6:     mAnswerTextFmt.setVerticalAlignment(Format::AlignVCenter);
andre@6:     mAnswerTextFmt.setHorizontalAlignment(Format::AlignLeft);
andre@25: 
andre@25:     mTitleStyle = QStringLiteral("<tr><td colspan='3' style='vertical-align: top;"
andre@26:                                  "font-weight: bold; text-decoration:underline; font-size: 18pt;'>"
andre@26:                                  "%1</td></tr><tr/>");
andre@32:     mQuestionStyle = QStringLiteral("<tr><td colspan='3' style='font-size: 11pt;font-weight: bold;'"
andre@32:                                     "><hr/>%1<hr/></td></tr>");
andre@26:     mAnswerChoiceStyle= QStringLiteral("<tr><td colspan='3' style='text-align: left; font-size: 11pt;'>Answer</td></tr>");
andre@26:     mChoiceTextStyle= QStringLiteral("<tr><td align='right' style='vertical-align: middle; font-size:11pt;'>%1</td>");
andre@25:     mChoiceVotesStyle = QStringLiteral("<td style='text-align: left;vertical-algin: center;"
andre@26:                                        "font-size:10pt;'</td>%1</td></tr>");
andre@26:     mAnswerTextStyle = QStringLiteral("<tr><td colspan='3' style='font-weight: bold;vertical-algin: middle;"
andre@26:                                       "font-size:11pt;'>Answer<hr/></td></tr>");
andre@26:     mFreeTextStyle = QStringLiteral("<tr><td colspan='3'; font-size:11pt;'>%1<hr/></td></tr>");
andre@25:     mEmptyRow = QStringLiteral("<tr style='height: %1px'/>").arg(CHOICE_ROW_HEIGHT);
andre@3: }
andre@3: 
andre@3: void Converter::run()
andre@3: {
andre@3:     QFile infile;
andre@3: 
andre@3:     if (mInput.isEmpty()) {
andre@3:         if (!infile.open(stdin, QIODevice::ReadOnly)) {
andre@3:             mErrors << tr("Failed to open standard input and no input file provided.");
andre@3:             return;
andre@3:         }
andre@3:     } else {
andre@3:         infile.setFileName(mInput);
andre@3:         if (!infile.open(QIODevice::ReadOnly)) {
andre@3:             mErrors << tr("Failed to open %1 for reading.").arg(mInput);
andre@3:             return;
andre@3:         }
andre@3:     }
andre@3:     QTextStream instream(&infile);
andre@3: 
andre@38:     QList<QFile*> outfiles;
andre@38: 
andre@38:     if (mOutputs.isEmpty()) {
andre@38:         QFile *outfile = new QFile();
andre@38:         if (!outfile->open(stdout, QIODevice::WriteOnly)) {
andre@3:             mErrors << tr("Failed to open standard output and no output file provided.");
andre@3:             return;
andre@3:         }
andre@38:         outfiles << outfile;
andre@38:     }
andre@38:     foreach (const QString &fileName, mOutputs) {
andre@38:         QFile *outfile = new QFile();
andre@38:         outfile->setFileName(fileName);
andre@38:         if (!outfile->open(QIODevice::WriteOnly)) {
andre@38:             mErrors << tr("Failed to open %1 for writing.").arg(fileName);
andre@3:             return;
andre@3:         }
andre@38:         outfiles << outfile;
andre@3:     }
andre@38:     convertToXSLX(instream, outfiles);
andre@3: }
andre@3: 
andre@38: static void makeBar(QTextStream &html, double percent, QTextDocument &doc)
andre@25: {
andre@38:     QImage image(QSize(IMAGE_WIDTH, 25), QImage::Format_RGB32);
andre@26:     QPainter painter(&image);
andre@26:     QRect rect = image.rect();
andre@38:     if (percent) {
andre@38:         rect.setRight(rect.right() / (100. / percent));
andre@38:         painter.fillRect(rect, QColor(BAR_COLOR));
andre@38:         rect.setLeft(rect.right());
andre@38:     }
andre@26:     qDebug() << "Filled " << rect << " with color";
andre@38:     rect.setRight(IMAGE_WIDTH);
andre@26:     painter.fillRect(rect, Qt::white);
andre@26:     qDebug() << "Filled " << rect << " with white";
andre@38:     doc.addResource(QTextDocument::ImageResource, QUrl(QStringLiteral("internal://bar%1.png").arg((int)percent)),
andre@26:                     QVariant(image));
andre@38:     html << QStringLiteral("<td style='vertical-align: middle'><img src=\"internal://bar%1.png\"/></td>").arg((int)percent);
andre@25:     return;
andre@25: }
andre@25: 
andre@38: void Converter::convertToXSLX(QTextStream& instream, QList<QFile *>outputs)
andre@3: {
andre@3:     Document xlsx;
andre@23:     QTextDocument doc;
andre@23:     QString htmlString;
andre@23:     QTextStream html (&htmlString);
andre@3: 
andre@3:     ConditionalFormatting bars;
andre@3: 
andre@6:     bars.addDataBarRule(QColor(0xFF, 0x99, 0x33), ConditionalFormatting::VOT_Num,
andre@6:                         "0", ConditionalFormatting::VOT_Num, "100", false);
andre@3: 
andre@3:     const double colWidth[] = COLUMN_WIDTHS;
andre@6:     double sum = 0;
andre@23: 
andre@3:     for (int i = 1; i <= COLUMN_CNT; i++) {
andre@3:         xlsx.setColumnWidth(i, colWidth[i-1]);
andre@6:         sum += colWidth[i-1];
andre@3:     }
andre@3: 
andre@6:     /* For the merged cell wordwrap trick. */
andre@15:     xlsx.setColumnWidth(26, sum + 1);
andre@6:     xlsx.setColumnHidden(26, true);
andre@6: 
andre@26:     int row = 1;
andre@23:     html << "<html><meta http-equiv=\"Content-Type\" content=\"text/html;charset=UTF-8\">"
andre@38:             "<body><table border=\"0\" width:\"100%\">";
andre@38:     html << QStringLiteral("<tr><th width=\"%1%\"></th>").arg(HTML_COL1_PERCENT);
andre@38:     html << QStringLiteral("<th width=\"%1%\"></th>").arg(HTML_COL2_PERCENT);
andre@38:     html << QStringLiteral("<th width=\"%1%\"></th>").arg(HTML_COL3_PERCENT);
andre@23: 
andre@23:     const QString title = mTitle.isEmpty() ? DEFAULT_TITLE : mTitle;
andre@23:     // Set the title of the Questionaire
andre@23:     xlsx.write(row++, 1, title, mTitleFmt);
andre@25:     html << mTitleStyle.arg(title.toHtmlEscaped());
andre@20:     xlsx.mergeCells("A1:C1");
andre@20:     xlsx.setRowHeight(1, TITLE_ROW_HEIGHT);
andre@3: 
andre@43:     QString input = instream.readAll();
andre@3: 
andre@3:     QRegularExpression questionEx(QUESTION_PATTERN);
andre@3:     QRegularExpression choiceEx(CHOICE_PATTERN);
andre@38:     QRegularExpression choiceAltEx(CHOICE_UNFILLED_PATTERN);
andre@38:     QRegularExpression freetxtEx(FREETXT_PATTERN);
andre@56:     QRegularExpression firstQuestionEx(FIRST_QUESTION_PATTERN);
andre@3: 
andre@43:     input.replace("\r\n", "\n");
andre@43:     input.replace("\n\r", "\n");
andre@43:     input.replace("#NAME?\n", "");
andre@43: 
andre@56:     QRegularExpressionMatch match = firstQuestionEx.match(input);
andre@3:     bool foundSomething = false;
andre@3:     int cursor = match.capturedEnd();
andre@3:     while (match.hasMatch() && cursor != -1) {
andre@3:         /* We've matched a question pattern. With the answer
andre@3:            line */
andre@3:         if (!match.lastCapturedIndex() == 2) {
andre@3:             /* Should not happen without misconfiguration. */
andre@3:             mErrors << "Internal parser error.";
andre@3:             return;
andre@3:         }
andre@3:         foundSomething = true;
andre@3:         const QString question = match.captured(1).trimmed();
andre@3:         const QString answerLine = match.captured(2).trimmed();
andre@6:         xlsx.write(row, 2, QString(" "), mQuestionFmt);
andre@6:         xlsx.write(row, 3, QString(" "), mQuestionFmt);
andre@3:         xlsx.write(row++, 1, question, mQuestionFmt);
andre@25:         html << mQuestionStyle.arg(question.toHtmlEscaped());
andre@3: 
andre@3:         if (answerLine == QStringLiteral(CHOICE_IDENTIFIER)) {
andre@3:             xlsx.setRowHeight(row, CHOICE_ROW_HEIGHT);
andre@21:             xlsx.write(row++, 1, "Answer", mAnswerChoiceFmt);
andre@25:             html << mAnswerChoiceStyle;
andre@3:             int firstChoiceRow = row;
andre@3:             int lastChoiceRow = row;
andre@38: repeat:
andre@38:             QRegularExpressionMatch choiceMatch = choiceEx.match(input, cursor);
andre@3:             while (choiceMatch.hasMatch() && choiceMatch.capturedStart() == cursor + 1) {
andre@3:                 /* We use the cursor here to keep track of the state. Only if an answer
andre@3:                    follows immediately behind the last answer we treat it as valid as
andre@3:                    otherwise we can't figure out when the next question begins. */
andre@3:                 cursor = choiceMatch.capturedEnd();
andre@3: 
andre@3:                 /* Write the values */
andre@12:                 QString choiceName = choiceMatch.captured(1).trimmed();
andre@12:                 if (choiceName.startsWith("=")) {
andre@12:                     choiceName = " " + choiceName;
andre@12:                 }
andre@12:                 xlsx.write(row, 1, choiceName, mChoiceTextFmt);
andre@25:                 html << mChoiceTextStyle.arg(choiceName.toHtmlEscaped());
andre@38:                 qDebug() << "Captured for choice: " << choiceMatch.captured(0);
andre@3:                 bool ok;
andre@56:                 QString percentStr = choiceMatch.captured("percent");
andre@38:                 double percent;
andre@63:                 if (percentStr.isEmpty()) {
andre@38:                     percent = 0;
andre@38:                     ok = true;
andre@38:                 } else {
andre@38:                     percent = percentStr.toDouble(&ok);
andre@38:                 }
andre@3:                 if (!ok) {
andre@3:                     mErrors << "Unparsable number in string: " + choiceMatch.captured();
andre@63:                     percent = 0;
andre@63:                     percentStr = QString();
andre@63:                     /* PercentStr was not a number. */
andre@3:                 }
andre@38:                 makeBar(html, percent, doc);
andre@39:                 xlsx.write(row, 2, percent == 0 ? QVariant() : percent);
andre@38:                 const QString numStr = choiceMatch.captured("num");
andre@23:                 const QString numVotesString = QString("%1% | %2 Number of votes").
andre@55:                            arg(percentStr.isEmpty() ? QStringLiteral("0") : percentStr).
andre@55:                            arg(numStr.isEmpty() ? QStringLiteral("0") : numStr);
andre@25:                 html << mChoiceVotesStyle.arg(numVotesString.toHtmlEscaped());
andre@23:                 xlsx.write(row, 3, numVotesString, mChoiceVotesFmt);
andre@3:                 xlsx.setRowHeight(row, CHOICE_ROW_HEIGHT);
andre@3:                 /* As long as we can match a choice which is either before the next question
andre@3:                    or before the end of the document */
andre@3:                 choiceMatch = choiceEx.match(input, cursor);
andre@3:                 row++;
andre@3:                 lastChoiceRow++;
andre@3:             }
andre@38:             choiceMatch = choiceAltEx.match(input, cursor);
andre@38:             bool additionalFound = false;
andre@38:             while (choiceMatch.hasMatch() && choiceMatch.capturedStart() <= cursor + 1) {
andre@38:                 additionalFound = true;
andre@38:                 const QString choice = choiceMatch.captured(1);
andre@38:                 cursor = choiceMatch.capturedEnd();
andre@38:                 /* Alternative answer that is just a list of strings */
andre@38:                 qDebug() << "Caputured unfilled choice: " << choice;
andre@38:                 html << mChoiceTextStyle.arg(choice.toHtmlEscaped());
andre@38:                 makeBar(html, 0, doc);
andre@39:                 xlsx.write(row, 2, QVariant());
andre@38:                 const QString numVotesString = QStringLiteral("Keine eingegangenen Antworten");
andre@38:                 html << mChoiceVotesStyle.arg(numVotesString.toHtmlEscaped());
andre@38:                 xlsx.write(row, 3, numVotesString, mChoiceVotesFmt);
andre@38:                 xlsx.setRowHeight(row, CHOICE_ROW_HEIGHT);
andre@38:                 row++;
andre@38:                 lastChoiceRow++;
andre@38:                 choiceMatch = choiceAltEx.match(input, cursor);
andre@38:                 QRegularExpressionMatch realMatch = choiceEx.match(input, cursor);
andre@38:                 if (choiceMatch.hasMatch() && choiceMatch.capturedStart() == realMatch.capturedStart()) {
andre@38:                     /* We have a real match so back to the other pattern. */
andre@38:                     break;
andre@38:                 }
andre@38:             }
andre@38:             if (additionalFound) {
andre@38:                 goto repeat;
andre@38:             }
andre@3:             bars.addRange(QString("B%1:B%2").arg(firstChoiceRow).arg(lastChoiceRow));
andre@13: //            xlsx.groupRows(firstChoiceRow - 2, lastChoiceRow - 1, false);
andre@3:         } else if (answerLine == QStringLiteral(TEXT_IDENTIFIER)) {
andre@6:             QRegularExpressionMatch textMatch = freetxtEx.match(input, cursor);
andre@6:             xlsx.setRowHeight(row, CHOICE_ROW_HEIGHT);
andre@21:             xlsx.write(row++, 1, "Answer", mAnswerTextFmt);
andre@25:             html << mAnswerTextStyle;
andre@23: 
andre@23:             /* To handle the workaround for quotes in answers we store
andre@23:              * the number of rows and only afterwards create the html rows. */
andre@23:             int firstFreeRow = row;
andre@6:             while (textMatch.hasMatch()) {
andre@6:                 if (textMatch.capturedStart() != cursor + 1) {
andre@6:                     /* The format allows unescaped quotes in the text.
andre@6:                        This makes a workaround neccessary. If we have
andre@6:                        an Unquoted string between the next quoted string
andre@6:                        and that Unquoted string is before the next question
andre@6:                        we append the unquoted string and the next quoted string
andre@6:                        with Quotes in the Row.*/
andre@6:                     QRegularExpressionMatch nextQuestion = questionEx.match(input, cursor);
andre@6:                     if (nextQuestion.hasMatch() &&
andre@6:                         nextQuestion.capturedStart() < textMatch.capturedEnd()) {
andre@6:                         /* The next question comes before the textMatch so we really have
andre@6:                            a new question. */
andre@6:                         break;
andre@6:                     }
andre@6:                     const QString lastRow = xlsx.read(row - 1, 26).toString();
andre@6:                     int unquotedLen = textMatch.capturedStart() - cursor;
andre@6:                     const QString unquoted = input.mid(cursor, unquotedLen);
andre@6:                     qDebug() << "Found inner quoted string: " << unquoted;
andre@6:                     /* Now combine */
andre@6:                     const QString combined = QString("%1\"%2\"%3").arg(lastRow).
andre@6:                                                                    arg(unquoted).
andre@6:                                                                    arg(textMatch.captured(1).trimmed());
andre@6:                     qDebug() << "Last row: " << lastRow;
andre@6:                     qDebug() << "Next Question is at: " << nextQuestion.capturedStart();
andre@6:                     qDebug() << "Text match is: " << textMatch.captured(1).trimmed();
andre@6:                     qDebug() << "cursor is at: " << cursor;
andre@6:                     qDebug() << "text match starts at: " << textMatch.capturedStart();
andre@6:                     xlsx.write(row - 1, 26, combined, mFreeTextFmt);
andre@12:                     xlsx.write(row - 1, 1, combined, mFreeTextFmt);
andre@6:                     cursor = textMatch.capturedEnd();
andre@6:                     textMatch = freetxtEx.match(input, cursor);
andre@6:                     continue;
andre@6:                 }
andre@6:                 cursor = textMatch.capturedEnd();
andre@6: 
andre@6:                 QString text = textMatch.captured(1).trimmed();
andre@6:                 qDebug() << "Found free text: " << text;
andre@12:                 if (text.startsWith("=")) {
andre@12:                     text = " " + text;
andre@12:                 }
andre@6: 
andre@6:                 /* Merge the cells */
andre@6:                 xlsx.mergeCells(QString("A%1:C%1").arg(row), mFreeTextFmt);
andre@6: 
andre@6:                 /* Merged cells ignore wordwrap the following trick is based on:
andre@6:                    http://excel.tips.net/T003207_Automatic_Row_Height_For_Merged_Cells_with_Text_Wrap.html
andre@6:                 */
andre@6:                 /* Write the values */
andre@6:                 xlsx.write(QString("Z%1").arg(row), text, mFreeTextFmt);
andre@12:                 xlsx.write(row, 1, text, mFreeTextFmt);
andre@6:                 row++;
andre@23: 
andre@6:                 textMatch = freetxtEx.match(input, cursor);
andre@6:             }
andre@23:             for (int i = firstFreeRow; i < row; i++) {
andre@25:                 html << mFreeTextStyle.arg(xlsx.read(i, 1).toString().toHtmlEscaped());
andre@23:             }
andre@3:         }
andre@3:         /* Insert Empty row. */
andre@3:         xlsx.setRowHeight(row++, CHOICE_ROW_HEIGHT);
andre@3:         match = questionEx.match(input, cursor);
andre@3:         cursor = match.capturedEnd();
andre@25:         html << mEmptyRow;
andre@3:     }
andre@3:     xlsx.addConditionalFormatting(bars);
andre@3: 
andre@3:     if (!foundSomething) {
andre@3:         mErrors << tr("Failed to parse input document.");
andre@3:     }
andre@3: 
andre@23:     html << "</table></body></html>";
andre@38:     doc.setHtml(htmlString);
andre@23: 
andre@38:     /* Fixup images for html */
andre@38:     QRegularExpression htmlRe = QRegularExpression("<td style='vertical-align: middle'><img src=\"internal://bar(\\d+).png\"/></td>");
andre@38:     htmlString.replace(htmlRe, QStringLiteral("<td style='background:linear-gradient(to right,"
andre@38:                                               BAR_COLOR ", " BAR_COLOR " \\1%, #ffffff \\1%)'></td>"));
andre@38: 
andre@38:     foreach (QFile *output, outputs) {
andre@38:         const QString fName = output->fileName().toLower();
andre@38:         if (fName.endsWith(".html")) {
andre@38:             QTextStream outstream(output);
andre@38:             outstream << htmlString;
andre@38:             output->close();
andre@38:         } else if (fName.endsWith(".pdf")) {
andre@38:             output->close();
andre@38:             QPrinter printer(QPrinter::PrinterResolution);
andre@38:             printer.setOutputFormat(QPrinter::PdfFormat);
andre@38:             printer.setPaperSize(QPrinter::A4);
andre@38:             printer.setOutputFileName(output->fileName());
andre@38:             /*
andre@38:             QPageLayout layout = printer.pageLayout();
andre@38:             layout.setUnits(QPageLayout::Millimeter);
andre@38:             layout.setMargins(QMarginsF(20, 20, 20, 20));
andre@38:             printer.setPageLayout(layout);
andre@38:             doc.setPageSize(printer.pageRect().size());
andre@38:             */
andre@38:             doc.print(&printer);
andre@38:         } else {
andre@38:             if (!xlsx.saveAs(output)) {
andre@38:                 mErrors << tr("Saving the XLSX document failed.");
andre@38:             }
andre@38:             output->close();
andre@38:         }
andre@23:     }
andre@3: }