开发者

how to count the sum of the list of products prices ( ColdFusion )

here is the query:

<cfquery name="GET_SHIP_ROW" datasource="#DSN2#">
    SELECT
        SR.*,
        S.STOCK_CODE
    FROM
        SHIP_ROW SR,
        #dsn3_alias#.STOCKS S
    WHERE
        SR.STOCK_ID = S.STOCK_ID AND 
        SR.SHIP_ID = #attributes.ship_id#
    ORDER BY
        SR.SHIP_ROW_ID
</cfquery>

for example i have a loop code of the prices:

  <cfloop from="#satir_start#" to="#satir_end#" index="i">
    <cfif i lte get_ship_row.recordcount>
        <cfscript>
            if(len(get_ship_row.discount[i]))indirim = get_ship_row.discount[i]; else indirim = 0;
            adim_1 = get_ship_row.amount[i] * get_ship_row.price[i];
            adim_2 = (adim_1/100)*(100-indirim);
            adim_3 = adim_2*(get_ship_row.tax[i]/100);
            adim_4 = adim_2+adim_3;
        </cfscript>
        <cfquery name开发者_开发知识库="GET_BARCODE" datasource="#DSN3#">
            SELECT
                BARCOD
            FROM
                PRODUCT
            WHERE 
                PRODUCT_ID = #get_ship_row.product_id[i]#
        </cfquery>
            <table>
          <tr>
            <td style="width:30mm;"><cfoutput>#get_ship_row.stock_code[i]#</cfoutput></td>
        <td style="width:50mm;"><cfoutput>#left(get_ship_row.name_product[i],53)#</cfoutput></td>
        <td style="width:25mm;" align="right"><cfoutput>#get_ship_row.amount[i]# #get_ship_row.unit[i]#</cfoutput></td>
        <td style="width:25mm;" align="right"><cfoutput>#TLFormat(get_ship_row.price[i])#</cfoutput> TL</td>
        <td style="width:35mm;" align="right"><cfoutput>#TLFormat(get_ship_row.amount[i] * get_ship_row.price[i])#</cfoutput> TL</td>
      </tr>
    </table>    
    </cfif>
  </cfloop>

there are displayed a list of the names and its prices, all i want to count, is their sum of prices, i mean of all the products. how do i dow it? thx for help!


I'm just taking a guess based on your (presumed) database structure. I'd recommend moving the calculation completely into the query so the db server is doing the work it's best at. Manipulating something like this in CF is less than optimal.

You'll have to test this, but I think it's close enough to get started:

    <cfquery name="GET_SHIP_ROW" datasource="#DSN2#">
SELECT
    SR.*,
    S.STOCK_CODE,
    ( (sr.amount*sr.price) * ( (100-discount)/100 ) * ( tax/100 ) ) as itemCost
FROM
    SHIP_ROW SR,
    #dsn3_alias#.STOCKS S
WHERE
    SR.STOCK_ID = S.STOCK_ID AND 
    SR.SHIP_ID = #attributes.ship_id#
ORDER BY
    SR.SHIP_ROW_ID
   </cfquery>

Once you have the line item totals in the recordset, running a query of queries to get the sum of all line items is pretty straightforward:

<cfquery name="total" dbtype="query">
select sum(itemCost) as shipmentTotal from GET_SHIP_ROW
</cfquery>

Does that help?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜