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

Question

I am writing a Software for a company. Please friends, I want you to help me. This company is a paint company that has different Product Type and Paint Type and colors. A way bill will be generated showing the item no, the description, the quantity, and the remark. I am sorting by the Product Type and the Paint Type. I am also grouping the output.

Here, I want the remark to rowspan all the paint with the same Product Type and Paint Type. For instance, if the Product Type is Honey, and the Paint Type is Texture. If there are 5 Honey Texture with a quantity of 20, The remark should rowspan all the 5 honey texture and write "Total of 20 quantity for Honey Texture". I tried looping but the values for quantity, description and item are repeated.

I don't want ITEM, QUANTITY, DESCRIPTION to be repeated. Only the Remark Column should rowspan the rows with the same product type and paint type. The Values in the ITEM, DESCRIPTION and QUANTITY should not be looped. See Picture for more explanation. I will be very grateful with any help. Please, don't mind my html

<cfquery datasource="ysr" name="getlistofbuyings">
        SELECT p.*, pt.paintcode, pt.*, pp.producttypename, pts.paintype, l.*
        FROM purchase p, paint pt, producttype pp, painttype pts, litre l
        WHERE p.transactionid = #transactionid#
        AND p.paintid = pt.paintid
        AND pt.producttypeid = pp.producttypeid
        AND pt.painttypeid = pts.painttypeid
        AND pt.litreid = l.litreid
        ORDER BY pp.producttypename, pts.paintype ASC       
    </cfquery>


            <table id="items" bgcolor="">

             <a name="afteradding"> 
              <tr bgcolor="#ccccee">
                  <th>ITEM</th>
                  <th>QUANTITY</th>
                  <th  class="blank" colspan="3">DESCRIPTION</th>
                  <th>REMARKS</th>
              </tr>
              </a>    



              <cfoutput group="producttypename" query="getlistofbuyings">              
               <cfoutput group="paintype">             
               <cfoutput group="litrename">

<cfquery datasource="ysr" name="numvb">
                    SELECT p.*, COUNT(purchaseid) as pan, SUM(quantity) as quani, pa.*, py.*, pp.producttypename, l.litrename
                    FROM purchase p, paint pa, painttype py, producttype pp, litre l
                    WHERE p.transactionid = #transactionid#
                    AND p.paintid = pa.paintid
                    AND pa.painttypeid = py.painttypeid
                    AND pa.litreid = l.litreid
                    AND pa.producttypeid = #producttypeid#
                    AND py.painttypeid = #painttypeid#
                    AND pa.producttypeid = pp.producttypeid
                    AND l.litreid = #litreid#

                </cfquery>

                <cfquery datasource="ysr" name="kilop">
                    SELECT COUNT(purchaseid) as newpur
                    FROM purchase
                    WHERE transactionid = #transactionid#
                </cfquery>

                <cfset rowss = #numvb.pan#>
                <cfset tot = #numvb.quani#>

                <cfset arrOfUsers = ArrayNew(1)>
                <cfoutput>
                    <cfset ArrayAppend(arrOfUsers,'<td>'&getlistofbuyings.quantity&getlistofbuyings.producttypeid&getlistofbuyings.painttypeid&'</td>')>
                </cfoutput>

                <cfloop from="1" to="#ArrayLen(arrOfUsers)#" index="i">

                <tr class="item-row">

                <cfset curm = #getlistofbuyings.currentrow#>




                <th><cfoutput>#curm#</cfoutput></th>


                <th>#getlistofbuyings.quantity#</th>

                 <th colspan="3" class="description"><span> #producttypename# #paintype# #paintcolor# #paintcode#</span></th>
                <cfif i EQ 1>
                <th nowrap="nowrap" rowspan="#ArrayLen(arrOfUsers)#" >#tot# bags of #producttypename# #paintype# with #litrename#</th>
                </cfif>

              </tr>
            </cfloop>
                </cfoutput>
                </cfoutput>
                </cfoutput>][1]

Answer 1

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