如何在Coldfusion中输出带有rowspan的备注列而不显示相同组的重复列（已重组） [英] How Can I Output a Remark Column with rowspan Without Duplicates for same group in Coldfusion (Re formated)

问题描述

我正在为公司编写软件。请朋友，我要你帮我。这家公司是一家涂料公司，有不同的产品类型和油漆类型和颜色。将生成显示项目no，描述，数量和备注的通行单。我按产品类型和油漆类型排序。我也分组输出。

在这里，我想要使用相同的产品类型和涂料类型对所有涂料进行rowspan。例如，如果产品类型为Honey，并且油漆类型为纹理。如果有5个蜂蜜纹理与数量为20，说明应该rowspan所有5蜂蜜纹理和写共20个数量的蜂蜜纹理。我尝试循环，但重复的数量，描述和项目的值。

我不想重复ITEM，QUANTITY，DESCRIPTION。只有备注列应该跨行放置具有相同产品类型和涂料类型的行。 ITEM，DESCRIPTION和QUANTITY中的值不应循环。有关详细说明，请参阅图片。我将非常感谢任何帮助。
请不要介意我的html

 < cfquery datasource =ysrname =getlistofbuyings ; 
 SELECT p。*，pt.paintcode，pt。*，pp.producttypename，pts.paintype，l。* 
 FROM purchase p，paint pt，producttype pp，painttype pts，l 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 =itemsbgcolor => 
 
< a name =afteradding> 
< tr bgcolor =＃ccccee> 
< th> ITEM< / th> 
< th> QUANTITY< / th> 
< th class =blankcolspan =3> DESCRIPTION< / th> 
< th>备注< / th> 
< / tr> 
< / a> 
 
 
 
< cfoutput group =producttypenamequery =getlistofbuyings> 
< cfoutput group =paintype> 
< cfoutput group =litrename> 
 
< cfquery datasource =ysrname =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，liter 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 =ysrname =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 =1to =＃ArrayLen（arrOfUsers）＃index =i> 
 
< tr class =item-row> 
 
< cfset curm =＃getlistofbuyings.currentrow＃> 
 
 
 
 
< th>< cfoutput>＃curm＃< / cfoutput>< / th> 
 
 
< th>＃getlistofbuyings.quantity＃< / th> 
 
< th colspan =3class =description>< span> ＃producttypename＃＃paintype＃＃paintcolor＃＃paintcode＃< / span>< / th> 
< cfif i EQ 1> 
< th nowrap =nowraprowspan =＃ArrayLen（arrOfUsers）＃>＃tot＃bags of＃producttypename＃＃paintype＃with＃litrename＃< / th> 
< / cfif> 
 
< / tr> 
< / cfloop> 
< / cfoutput> 
< / cfoutput> 
< / cfoutput>] [1] 
  

解决方案

请尝试以下操作：

 <！--- pseudo query ---& 
< cfscript> 
 report = queryNew（productTypeName，paintType，paintColor，paintCode，quantity，litreName）; 
 queryAddRow（report，[[Honey，Texture，Brilliant White，1700,3，20 liter]]）; 
 queryAddRow（report，[[Honey，Texture，Off White，1701,8，20 liter]]）; 
 queryAddRow（report，[[Magic，Texture，Off White，1701,21，20 liter]]）; 
 queryAddRow（report，[[Magic，Texture，Brilliant White，1700,8，20 liter]]）; 
 queryAddRow（report，[[Princess，Gloss，Brilliant White，9102,9，4 liter]]）; 
 queryAddRow（report，[[Princess，Texture，Rose Pink，1712,3，20 liter]]）; 
 queryAddRow（report，[[Princess，Texture，Ivory，1704,1，20 liter]]）; 
 queryAddRow（report，[[Princess，Texture，Off White，1701,3，20 liter]]）; 
 queryAddRow（report，[[Princess，Texture，Off White，1701,3，20 liter]]）; 
< / cfscript> 
 
<！--- add groupRowspan和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]不是lastProductType）或（report.paintType [rowNum]不是lastPaintType） （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>备注< / 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> 
  

谢谢！

/ p>

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]

解决方案

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

这篇关于如何在Coldfusion中输出带有rowspan的备注列而不显示相同组的重复列（已重组）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！