void homestead::GenerateXTabAllCounties(){
//////////// Cntyname vs MSHEC for all counties ///////////////
sQuestion.append("Do you want to generate an All Counties\n");
sQuestion.append("cross tab report for Homestead?\n");
int reply
= QMessageBox::question(this, tr
("QMessageBox::showQuestion()"),
ui.leStatus->setText("Preparing XTab Report...");
QString queryStr
= "SELECT cntyname, M1,M2,M3,M4,S1,S2,S3,S4,VA,";
queryStr.append("(SUM(M1)+SUM(M2)+SUM(M3)+SUM(M4)+SUM(S1)+SUM(S2)+SUM(S3)+SUM(S4)+SUM(VA)) AS Totals ");
queryStr.append("FROM(SELECT cntyname, ");
queryStr.append("SUM(CASE WHEN offuse = 'M-1' THEN 1 ELSE 0 END)AS M1,");
queryStr.append("SUM(CASE WHEN offuse = 'M-2' THEN 1 ELSE 0 END)AS M2,");
queryStr.append("SUM(CASE WHEN offuse = 'M-3' THEN 1 ELSE 0 END)AS M3,");
queryStr.append("SUM(CASE WHEN offuse = 'M-4' THEN 1 ELSE 0 END)AS M4,");
queryStr.append("SUM(CASE WHEN offuse = 'S-1' THEN 1 ELSE 0 END)AS S1,");
queryStr.append("SUM(CASE WHEN offuse = 'S-2' THEN 1 ELSE 0 END)AS S2,");
queryStr.append("SUM(CASE WHEN offuse = 'S-3' THEN 1 ELSE 0 END)AS S3,");
queryStr.append("SUM(CASE WHEN offuse = 'S-4' THEN 1 ELSE 0 END)AS S4,");
queryStr.append("SUM(CASE WHEN offuse = 'VA' THEN 1 ELSE 0 END)AS VA ");
queryStr.append("FROM hap");
queryStr.append(this->dbYear);
queryStr.append(".property_");
queryStr.append(" GROUP BY cntyname ORDER BY cntyname)");
queryStr.append("GROUP BY cntyname,M1,M2,M3,M4,S1,S2,S3,S4,VA ORDER BY cntyname");
XtabModel->setQuery(queryStr);
if (XtabModel->rowCount() > 0){
ui.leStatus->setText("Processing XTab rows ...");
// generate html code for MS (offuse) versus Percent Exmption for all counties
int nRecs = XtabModel->rowCount();
int totM1 = 0;
int totM2 = 0;
int totM3 = 0;
int totM4 = 0;
int totS1 = 0;
int totS2 = 0;
int totS3 = 0;
int totS4 = 0;
int totVA = 0;
int totTotals = 0;
XTabName.append(dateToday.toString("MMdd"));
XTabName.append("_XTab_CntyName_vs_MSHEC");
XTabName.append(".HTML");
XTabName.prepend("reports/");
ui.leStatus->setText("Can't open XTab_CntyName_vs_MSHEC html file for output");
return;
}
//mod the lines below to change code to batches between dates.
out << homestead::HTML_Header1 ;
out << "Homestead " << this->dbYear << " Crosstab Report for All Counties ";
out << homestead::HTML_Header2 << "\n";
out << "<TABLE width='100%' align=center><TR><TH>Homestead " ;
out << this->dbYear << " Crosstab Report for All Counties " ;
out << "</TH></TR></TABLE><P></P>" << "\n";
out << "<TABLE width='100%' ><B><TR>";
out << "<TH align=left width='20%'>County Name</TH>";
out << "<TH width='8%' align=right>M1</TH>";
...
delete *XtabModel
}
void homestead::GenerateXTabAllCounties(){
//////////// Cntyname vs MSHEC for all counties ///////////////
QString sQuestion;
sQuestion.append("Do you want to generate an All Counties\n");
sQuestion.append("cross tab report for Homestead?\n");
int reply = QMessageBox::question(this, tr("QMessageBox::showQuestion()"),
sQuestion, QMessageBox::Yes, QMessageBox::No);
if (reply == QMessageBox::Yes){
ui.leStatus->setText("Preparing XTab Report...");
QString queryStr = "SELECT cntyname, M1,M2,M3,M4,S1,S2,S3,S4,VA,";
queryStr.append("(SUM(M1)+SUM(M2)+SUM(M3)+SUM(M4)+SUM(S1)+SUM(S2)+SUM(S3)+SUM(S4)+SUM(VA)) AS Totals ");
queryStr.append("FROM(SELECT cntyname, ");
queryStr.append("SUM(CASE WHEN offuse = 'M-1' THEN 1 ELSE 0 END)AS M1,");
queryStr.append("SUM(CASE WHEN offuse = 'M-2' THEN 1 ELSE 0 END)AS M2,");
queryStr.append("SUM(CASE WHEN offuse = 'M-3' THEN 1 ELSE 0 END)AS M3,");
queryStr.append("SUM(CASE WHEN offuse = 'M-4' THEN 1 ELSE 0 END)AS M4,");
queryStr.append("SUM(CASE WHEN offuse = 'S-1' THEN 1 ELSE 0 END)AS S1,");
queryStr.append("SUM(CASE WHEN offuse = 'S-2' THEN 1 ELSE 0 END)AS S2,");
queryStr.append("SUM(CASE WHEN offuse = 'S-3' THEN 1 ELSE 0 END)AS S3,");
queryStr.append("SUM(CASE WHEN offuse = 'S-4' THEN 1 ELSE 0 END)AS S4,");
queryStr.append("SUM(CASE WHEN offuse = 'VA' THEN 1 ELSE 0 END)AS VA ");
queryStr.append("FROM hap");
queryStr.append(this->dbYear);
queryStr.append(".property_");
queryStr.append(" GROUP BY cntyname ORDER BY cntyname)");
queryStr.append("GROUP BY cntyname,M1,M2,M3,M4,S1,S2,S3,S4,VA ORDER BY cntyname");
QSqlQueryModel *XtabModel = NEW QSqlQueryModel;
XtabModel->setQuery(queryStr);
if (XtabModel->rowCount() > 0){
ui.leStatus->setText("Processing XTab rows ...");
// generate html code for MS (offuse) versus Percent Exmption for all counties
int nRecs = XtabModel->rowCount();
int totM1 = 0;
int totM2 = 0;
int totM3 = 0;
int totM4 = 0;
int totS1 = 0;
int totS2 = 0;
int totS3 = 0;
int totS4 = 0;
int totVA = 0;
int totTotals = 0;
QDate dateToday = QDate::currentDate();
QString XTabName = "D_";
XTabName.append(dateToday.toString("MMdd"));
XTabName.append("_XTab_CntyName_vs_MSHEC");
XTabName.append(".HTML");
XTabName.prepend("reports/");
QFile file(XTabName);
if (!file.open(QIODevice::WriteOnly | QIODevice::Text)){
ui.leStatus->setText("Can't open XTab_CntyName_vs_MSHEC html file for output");
return;
}
QTextStream out(&file);
//mod the lines below to change code to batches between dates.
out << homestead::HTML_Header1 ;
out << "Homestead " << this->dbYear << " Crosstab Report for All Counties ";
out << homestead::HTML_Header2 << "\n";
out << "<TABLE width='100%' align=center><TR><TH>Homestead " ;
out << this->dbYear << " Crosstab Report for All Counties " ;
out << "</TH></TR></TABLE><P></P>" << "\n";
out << "<TABLE width='100%' ><B><TR>";
out << "<TH align=left width='20%'>County Name</TH>";
out << "<TH width='8%' align=right>M1</TH>";
...
delete *XtabModel
}
To copy to clipboard, switch view to plain text mode
Bookmarks