How can ı take sum NETTOTAL the under of the table ?

[nurcanb61753314]

ı made a form but  ı must show all sum nettotal money under of the table.

How can ı do it?help please.

for exp:the under of nettotal it will be write 100.000 like that.

<cfparam name="attributes.keyword" default="">

<cfparam name="maxrow" default=20>
<cfparam name="attributes.start_date" default="">
<cfparam name="attributes.finish_date" default="">

<cfif isdefined("attributes.form_submitted") and isdefined("form.maxrow")>

<cfquery name="GET_SALES_EMP" datasource="#DSN#" maxrows="#form.maxrow#">

SELECT inv.INVOICE_DATE,
inv.INVOICE_NUMBER,
inv.TAXTOTAL,
inv.OTV_TOTAL,
inv.OTHER_MONEY,
inv.COMPANY_ID,
inv.PARTNER_ID,
inv.NETTOTAL,
E.EMPLOYEE_NAME,
E.EMPLOYEE_SURNAME,
sc.IS_ACTIVE,
sc.SUBSCRIPTION_HEAD
FROM [WORKCUBE_TEKNOTEL].[WORKCUBE_TEKNOTEL_2019_1].[INVOICE] AS inv
left join [WORKCUBE_TEKNOTEL].[WORKCUBE_TEKNOTEL_1].SUBSCRIPTION_CONTRACT as sc on inv.PARTNER_ID=sc.PARTNER_ID
left join [WORKCUBE_TEKNOTEL].[WORKCUBE_TEKNOTEL].EMPLOYEES as e ON e.EMPLOYEE_ID=inv.SALE_EMP
where e.EMPLOYEE_NAME is not null
<cfif len(attributes.keyword)>
AND (e.EMPLOYEE_NAME LIKE '%#attributes.keyword#%'

</cfif>

order by sc.IS_ACTIVE desc

</cfquery>

<cfelse>
<cfset GET_SALES_EMP.recordcount = 0>
</cfif>

<cfform name="form" action="#request.self#?fuseaction=myhome.my_test" method="post">
<input type="hidden" name="form_submitted" value="1" />
<cfoutput>
<table>
<tr>
<td>Filtre</td>
<td><input name="keyword" value="#attributes.keyword#" placeholder="isim"/></td>
<TD><input type="number" name="maxrow" id="maxrow" value="<cfoutput>#maxrow#</cfoutput>"></td>
<td><cf_wrk_search_button></td>
<td><div class="form-group" id="form_ul_start_date">
<div class="input-group x-12">
<cfsavecontent variable="message"><cf_get_lang no ='237.Lütfen Başlangıç Tarihi Kontrol Ediniz'>!</cfsavecontent>
<cfinput type="text" name="start_date" value="#dateformat(attributes.start_date,dateformat_style)#" validate="#validate_style#" maxlength="10" message="#message#">
<span class="input-group-addon"><cf_wrk_date_image date_field="start_date"></span>
</div>
</div> </td>
<td> <div class="form-group" id="form_ul_finish_date">
<div class="input-group x-12">
<cfsavecontent variable="message"><cf_get_lang no ='238.Lütfen Bitiş Tarihi Kontrol Ediniz'>!</cfsavecontent>
<cfinput type="text" name="finish_date" value="#dateformat(attributes.finish_date, dateformat_style)#" validate="#validate_style#" maxlength="10" message="#message#">
<span class="input-group-addon"><cf_wrk_date_image date_field="finish_date"></span>
</div>
</div></td>
</tr>
</table>
</cfoutput>
</cfform>

<cf_big_list>
<thead>
<tr>
<th>SATIŞ TEMSİLCİSİ</th>
<th>FATURA KESİM TARİHİ</th>
<th>KURUM ADI</th>
<th>FATURA BEDELİ</th>
<th>AKTİFLİK DURUMU</th>
<TH>PARA ÇEŞİDİ</th>
<TH>FATURA NUMARASI</TH>
<TH>VERGİ TOPLAMI</TH>
<TH>OTV VERGİSİ</TH>

</tr>
</thead>
<tbody>
<cfif GET_SALES_EMP.recordcount>
<cfoutput query="GET_SALES_EMP">
<tr>
<td>#EMPLOYEE_NAME# #EMPLOYEE_SURNAME#</td>
<td>#dateFormat(INVOICE_DATE,'dd/mm/yyyy')#</td>
<td>#SUBSCRIPTION_HEAD#</td>
<td>#NETTOTAL#</td>
<td>#IS_ACTIVE#</td>
<td>#OTHER_MONEY#</td>
<td>#INVOICE_NUMBER#</td>
<td>#TAXTOTAL#</td>
<td>#OTV_TOTAL#</td>

</tr>
</cfoutput>
<cfelse>
<tr>
<td colspan="6"><cfif isdefined("attributes.form_submitted")>Kayit Yok<cfelse>Arama Yapiniz</cfif></td>
</tr>
</cfif>

</tbody>
</cf_big_list>

[John123]

If you want to show the total for just what is displayed on the page then you can add to a running total variable inside your output loop.

Before the output loop

<cfset SumNetToal = 0>

Inside the loop

<cfset SumNetToal = SumNetToal + GET_SALES_EMP.NETTOTAL>

After the loop out put a totals TR and use variable SumNetTotal as the value.

[BKBK]

Hi Nurcanbarlas, 

I am assuming that you're happy with the result of your code. That is, the database is giving you the results you expect, but you are looking for suggestions on how to display money totals. If so, then I will suggest LSCurrencyFormat. 

An example to illustrate:

<!--- Store the current system locale in a variable--->
<cfset existingSystemLocale=getLocale()>

<cfset amountToDisplay=12345678/100>

<!--- Set the locale temporarily to Turkish to display total --->
<cfset tempLocale = setLocale("tr_TR")>
<cfoutput>
Turkish currency format (without the currency symbol): #lsCurrencyFormat(amountToDisplay, "none")#<br>
</cfoutput>

<!--- Restore locale back to original system locale --->
<cfset setLocale(existingSystemLocale)>

Applying that to your NETTOTAL gives:

<cfif GET_SALES_EMP.recordcount>
<cfoutput query="GET_SALES_EMP">
<tr>
<td>#EMPLOYEE_NAME# #EMPLOYEE_SURNAME#</td>
<td>#dateFormat(INVOICE_DATE,'dd/mm/yyyy')#</td>
<td>#SUBSCRIPTION_HEAD#</td>

<!--- Current system locale --->
<cfset existingSystemLocale=getLocale()>
<!--- Set locale temporarily to Turkish to display total --->
<cfset tempLocale = setLocale("tr_TR")>
<td>#lsCurrencyFormat(NETTOTAL, "none")#</td>
<!--- Restore locale back to original system locale --->
<cfset setLocale(existingSystemLocale)>

<td>#IS_ACTIVE#</td>
<td>#OTHER_MONEY#</td>
<td>#INVOICE_NUMBER#</td>
<td>#TAXTOTAL#</td>
<td>#OTV_TOTAL#</td>

</tr>
</cfoutput>
<cfelse>
<tr>
<td colspan="6"><cfif isdefined("attributes.form_submitted")>Kayit Yok<cfelse>Arama Yapiniz</cfif></td>
</tr>
</cfif>