select statement help - advanced level

Applications Grid / Reports
#1

hi guys, i have a control application with select statement returns many values… i want to display them in between 2 dates… i created 2 dates and works fine but i want to display them after the submission of date only…

currently, i put the sql statement in the onValidate even and it works and created another field to display the values as label… but it is displayed whatever the start-date and end-date are… i want them only to be displayed if the dates are selected…

one more issue, once clicking the “ok” button it doesn’t calcuate the sql directly, it needs second click to calculate!! any idea!?

my code


$start={startdate};
$end={enddate};
	
sc_lookup(data, " SELECT count(distinct(customer)) as customer,
       count(distinct case when video = 1 then customer) as video1,
       count(distinct case when video = 2 then customer end) as video3,
       count(distinct case when video = 3 then customer end) as video4,
       count(distinct case when video = 4 then customer end) as video5,
       count(distinct case when video = 5 then customer end) as video6,
       count(distinct case when video = 6 then customer end) as video7
FROM my_table
WHERE date(date_field) BETWEEN '$start' AND '$end'");	
[customer] ={data[0][0]};
[video1] ={data[0][1]};
[video2] ={data[0][2]};
[video3] ={data[0][3]};
[video4] ={data[0][4]};
[video5] ={data[0][5]};
[video6] ={data[0][6]};
[video7] ={data[0][7]};

how to display the data after selecting the date fileds only? using control application? i created second block for data display and put the global variable as html in label field… but not sure why the data is still lappearing even i tried on validateSuccess event

as there better approach for this?

#2

okay, here is what worked for me:

created control application: added 2 fields as {startdate} and {enddate}

in onvalidate event i put:


$start = {startdate};
$end = {enddate};

sc_lookup (data1, "SELECT count(distinct(customer)) FROM my_table where date_field BETWEEN'$start' AND '$end'");
if ({data1} === false) {echo "Error";}elseif (empty({data1})){echo "No data found";}
else {{customer} ={data1[0][0]};}
echo "<br>";
echo "Total number of customers= {customer}";
echo "<br>";

echo "<b>Details</b>";

sc_lookup (data2, "SELECT count(distinct(customer)) FROM my_table where mats=1");
if ({data2} === false) {echo "Error";} elseif (empty({data2})){echo "No data for the selected date range";}
else {{mats} = {data2[0][0]};}
echo "<br>";
echo "Mats quantity = {mats}";

sc_lookup (data3, "SELECT count(distinct(customer)) FROM my_table where vga=1");
if ({data3} === false) {echo "Error";} elseif (empty({data3})){echo "No data for the selected date range";}
else {{vga} = {data3[0][0]};}
echo "<br>";
echo "Vga quantity = {vga}";


/// and so on... this will be repeated for all fields, and each choice for each field!

now, works on control application, shows first 2 fields to select date… once i select the dates, gives me
1- the total number of customer = correct number
2- quantity of mats = 3 (correct)
3- quantity of vga = 10 (correct)

problems are:

1- the selects will be veeeery long, is there a way to shortcut this statement?
2- today date and end-date doesn’t count! seems like selecting based on the php time zone or something… how i can make it select correct date
ps. the date field is timestamp of mysql
3- it is still ugly, shows in the page left-up, how i can show it under the fields? what should i use a block in layout and define a block to show the data? any shortcut for these long tries???

#3

never mind, found the solution somehow,

as i have the reulsts defined in label fields of the seond block “output” i’ve added the onLoad event this code


if(!isset({startdate}) || empty({enddate}))
{ sc_block_display(output, 'off'); }
else {sc_block_display(ouput, 'on');}

still don’t know why i have to click 2 times the OK button in order to get the result correctly… first ok click gives no data or 0 or stays as it was from the previous date-range selection, second ok calculate and gets the query correct then returns the data in the block!

any idea?