sum column with filtered in hook file

[amyat]

in table.php on hook file area

Code: Select all

	function invoices_header($contentType, $memberInfo, &$args) {
		$header='';

		switch($contentType) {
			case 'tableview':
				 $sum=sqlvalue("SELECT SUM(REPLACE(`invoices`.`bill_amount`,',','')) FROM invoices ");
	                 $header="<%%HEADER%%><script type=\"text/javascript\">
	                 \$j(function(){
		         \$j('td.invoices-bill_amount').last().text(Intl.NumberFormat().format(".$sum.")).addClass('text-right');
			});
	               	</script>";
				break;
		}

		return $header;
	}

what should i need to change sql query for depend on filtered
like that SELECT SUM(REPLACE(`invoices`.`bill_amount`,',','')) FROM {$x->QueryFrom} {$QueryWhere}

please help me

[pbottcher]

Hi,

can you explain a little more detailed what you need?

[amyat]

database store only number in front of comma value when use number format(1,234) on calculated field with decimal data type
and show column sum is wrong because of number format
and

another fields depend on this calculated field database
so fields result is wrong ( 1,234 * another field => 1* another field) because of number format


so i change data type to varchar and use Intl.NumberFormat().format(".value.") in html and REPLACE(`value` , ',' , '') in query

write this following code for show column sum in hook/table.php/ function table_heander()

Code: Select all

function invoices_header($contentType, $memberInfo, &$args) {
		$header='';

		switch($contentType) {
			case 'tableview':
				$sum_bill_amount=sqlValue("SELECT SUM(REPLACE(`invoices`.`bill_amount`,',','')) as `bill_amount` FROM invoices");				   
	                 $header="<%%HEADER%%><script type=\"text/javascript\">
	                 \$j(function(){
		         \$j('td.invoices-bill_amount').last().text(Intl.NumberFormat().format(".$sum_bill_amount.")).addClass('text-right');
			});
	               	</script>";
				break;
		}

		return $header;
	}

but column sum result is not depend on filter
i want to column sum result depend on filter
what should i need to write for column sum result change when use filter

[pbottcher]

Hi again,

sorry, but I still do not get what you try to do. Why are you not using dircetly decimal as datatype? You can set in AppGini the presenation of the data to be like you want. (Use the data format option in the field definition).
In the database, decimals are always stored with a dot as delimiter. So there is no need to use replace or such on decimals.

[amyat]

hi,
calculated fields with data format
decimal type only store 12,500 => 12
957,975 => 957
5,100 => 5
250 => 250
so show column sum is 1,224 instead of 975,825

is my query

Code: Select all

SELECT format(SUM(`invoice_items`.`unit_price` * `invoice_items`.`qty`),',') FROM `invoices` 
LEFT JOIN `invoice_items` ON `invoice_items`.`invoice_id`=`invoices`.`id` 
WHERE `invoices`.`id`='%ID%'

"calculated field with format and show column sum with correct and save 12500 instead of 12" is my point
is my wrong? please patience
thank you

[jsetzer]

There are two or three issues:

(1) I remember right, unfortunately this is an old and well known bug in AppGini: Numeric Input fields only accept point "." as decimal separator unless you modify them by code. When using comma "," the part behind will be cut off. Try to enter 257.99 instead of 257,99. Then check the value being stored in database. Should be correct when using ".", should be cut when using ",". But honestly speaking I can only say for AG 5.9x. This is annoying for all of us around the world who are using different decimal separators.

(2) Check the datatype of your calculated field. If your SQL returns a string (as a result of using SQLs FORMAT function), your field should be VarChar or Text. But if your field is decimal, your SQL should return a numeric value, not string value. Whenever you are using such values for calculation, I recommend numeric datatype. If you need that calculated result just for display, using FORMAT function and storing string should be fine.

(3) I think when AppGini recalculates calculated fields, the number format, chosen in the model, will not be applied after calculation. But I may be wrong with latest version. I have seen many times in TV that well formatted numbers (calculated fields) get overwritten by the same value, but unformatted.

Due to such problems I sometimes create an input field just for input, visible in DV, hidden in TV, then a second field, calculated, visible in TV only.

[amyat]

hi ,

that is the best idea and good solution for me today

Due to such problems I sometimes create an input field just for input, visible in DV, hidden in TV, then a second field, calculated, visible in TV only.

calculated field for show dv and hide tv view.
input file for show tv and
input file read only as well as hide dv view


thank you for your advice and solution
i will appreciate it ....