[Solved] How Can I Output a Remark Column with rowspan Without Duplicates for same group in Coldfusion (Re formated)

by


Please try the following:

<!--- pseudo query --->
<cfscript>
  report = queryNew("productTypeName,paintType,paintColor,paintCode,quantity,litreName");
  queryAddRow(report, [["Honey","Texture","Brilliant White",1700,3,"20 litres"]]);
  queryAddRow(report, [["Honey","Texture","Off White",1701,8,"20 litres"]]);
  queryAddRow(report, [["Magic","Texture","Off White",1701,21,"20 litres"]]);
  queryAddRow(report, [["Magic","Texture","Brilliant White",1700,8,"20 litres"]]);
  queryAddRow(report, [["Princess","Gloss","Brilliant White",9102,9,"4 litres"]]);
  queryAddRow(report, [["Princess","Texture","Rose Pink",1712,3,"20 litres"]]);
  queryAddRow(report, [["Princess","Texture","Ivory",1704,1,"20 litres"]]);
  queryAddRow(report, [["Princess","Texture","Off White",1701,3,"20 litres"]]);
  queryAddRow(report, [["Princess","Texture","Off White",1701,3,"20 litres"]]);
</cfscript>

<!--- add groupRowspan and groupTotalQuantity columns --->
<cfscript>
  queryAddColumn(report, "groupRowspan", "integer", []);
  queryAddColumn(report, "groupTotalQuantity", "integer", []);
  if(report.RecordCount) {
      lastQueryRowToUpdate = 0;
      lastProductType = lastPaintType = lastLitreName = "";
      groupRowspan = 0;
      groupTotalQuantity = 0;
      for(rowNum=1; rowNum<=report.RecordCount; rowNum++) {
          if((report.productTypeName[rowNum] is not lastProductType) or (report.paintType[rowNum] is not lastPaintType) or (report.litreName[rowNum] is not lastLitreName)) {
              if(lastQueryRowToUpdate) {
                  querySetCell(report, "groupRowspan", groupRowspan, lastQueryRowToUpdate);
                  querySetCell(report, "groupTotalQuantity", groupTotalQuantity, lastQueryRowToUpdate);
              }
              lastQueryRowToUpdate = rowNum;
              lastProductType = report.productTypeName[rowNum];
              lastPaintType = report.paintType[rowNum];
              lastLitreName = report.litreName[rowNum];
              groupRowspan = 0;
              groupTotalQuantity = 0;
          }
          groupRowspan++;
          if(isValid("integer", report.quantity[rowNum])) {
              groupTotalQuantity += report.quantity[rowNum];
          }
          if((rowNum is report.RecordCount) and lastQueryRowToUpdate) {
              querySetCell(report, "groupRowspan", groupRowspan, lastQueryRowToUpdate);
              querySetCell(report, "groupTotalQuantity", groupTotalQuantity, lastQueryRowToUpdate);
          }
      }
  }
</cfscript>

<!--- table w/ rowspan --->
<table>
  <tr style="background-color:#cce;">
    <th>ITEM</th>
    <th>QUANTITY</th>
    <th>DESCRIPTION</th>
    <th>REMARKS</th>
  </tr>
  <cfloop query="report">
    <tr style="background-color:#ccc;">
      <cfoutput>
      <td>#report.CurrentRow#</td>
      <td>#report.quantity#</td>
      <td>#report.productTypeName# #report.paintType# #report.paintColor# #report.paintCode#</td>
      <cfif isValid("integer", report.groupRowspan)>
        <td rowspan="#report.groupRowspan#">#report.groupTotalQuantity# bags of #report.productTypeName# #report.paintType# with #report.litreName#</td>
      </cfif>
      </cfoutput>
    </tr>
  </cfloop>
</table>

Thanks!,

-Aaron

3

solved How Can I Output a Remark Column with rowspan Without Duplicates for same group in Coldfusion (Re formated)