ASE Home Page Products Download Purchase Support About ASE
ChartDirector Support
 Forum HomeForum Home   SearchSearch

Message ListMessage List     Post MessagePost Message

  piestub from clickable piechart
Posted by seafree on Nov-11-2015 02:36
| Reply | Monitor
Help me please, I need a send sql pameter to a piestub.pl chart from a
clickable pie chart:

I sent parameters:
my $imageMap = $c->getHTMLImageMap("piestub.pl",
"label={label}&value={value}&percent={percent}&aaa={field0}&xxx={$sql_eventos}",
"title='{dataSetName}@{xLabel}:{field0}'",
"sql='{dataSetName}@{xLabel_sql}:{$sql_eventos2}'");

I read parameters in chart piestub.pl:
<div style="font-size:10pt; font-family:arial">
    <li><b>Sistema</b> : @{[$query->param("label")]}</li><br>
    <li><b>Indisponibilidad</b> : @{[$query->param("value")]}</li><br>
    <li><b>Porcentaje</b> : @{[$query->param("percent")]}%</li><br>
    <li><b>Costo</b> :      @{[$query->param("aaa")]}</li>
    <li><b>SQL</b> :      @{[$query->param("xxx")]}</li>
    </div>

But when I show SQL parameter the result is $sql_eventos preview defined but I sent
$sql_eventos2.

I hope could you help me please.

  Re: piestub from clickable piechart
Posted by Peter Kwan on Nov-12-2015 00:24
| Reply | Monitor
Hi seafree,

I assume the $sql_eventos and $sql_eventos2 are constant SQL relative to the sectors,
that is, they are the same for every sector. In this case, you just need to add them as
constants in the URL:

$c->getHTMLImageMap("piestub.pl",
"label={label}&value={value}&percent={percent}&aaa=
{field0}&xxx=$sql_eventos&yyy=$sql_eventos2",
"title='{dataSetName}@{xLabel}:{field0}'");

The $sql_eventos2 is then:

$sql2 = @{[$query->param("yyy")]};

Note that as the $sql_eventos and $sql_eventos2 are passed as URL query parameters, if
they contain characters that are not valid in URL, you should escape them first before using
them in the URL. See:

http://search.cpan.org/dist/URI/lib/URI/Escape.pm

Hope this can help.

Regards
Peter Kwan

  Re: piestub from clickable piechart
Posted by seafree on Nov-12-2015 04:05
| Reply | Monitor
THANKS A LOT PETER, YOU ARE A EXCELENT PERSON.

Your answer thats ok, but now I have other problem $sql_eventos2 is relative to each sector,
that is, they are diferent for every sector and in this moment are the same, do you some suggestio?

Regards
Seafree

  Re: piestub from clickable piechart
Posted by Peter Kwan on Nov-13-2015 00:52
| Reply | Monitor
Hi seafree,

If $sql_eventos2 is different for each sector, that means you have many SQLs, one for each
sector. For example, if you have 5 sectors, that means you have 5 different SQLs.

In this case, please put the SQLs into an array (just like the sector data values are in an
array), and add the array as an extra field:

$c->addExtraField($myArrayOfSQLs);

Then you can use {field0} to represent the array:

$c->getHTMLImageMap("piestub.pl", "label={label}&value={value}&percent=
{percent}&aaa={field0}&xxx=$sql_eventos&yyy={field0}",
"title='{dataSetName}@{xLabel}:{field0}'");

Note that in this case, the SQL does not need to be escaped. In general, if the text in the
URL is specify by your code (like xxx=$sql_eventos), you need to escape the text to avoid
invalid URL characters. If the text is substituted by ChartDirector (such {field0}, which will
be substituted with the actual text from the extra field), ChartDirector will automatically
escape the text to avoid invalid URL characters.

Hope this can help.

Regards
Peter Kwan

  Re: piestub from clickable piechart
Posted by seafree on Nov-14-2015 00:46
| Reply | Monitor
Good Moorning Peter, I'm confuse, I send you this code:

First: I form SQL

if ($tuplas > 0){
        while($reg_dcev = $sth->fetchrow_hashref()) {
                $descrip_subsist=$reg_dcev->{'descrip_subsist'};
                $id_subsist=$reg_dcev->{'id_subsist'};
                $tir=$reg_dcev->{'tir'};
                $cri=$reg_dcev->{'cri'};
                $critir=$reg_dcev->{'cri_tir'};
                $costo=$reg_dcev->{'costo'};
                $sql_eventos2 = "
                        SELECT descrip_subsist,descrip_comp,descrip_tcomp,subest,alarmas,fecha_sale,tir,cri,(cri*tir) as CRI_TIR, costo
                        FROM
                        (SELECT cs.descrip_subsist,cs.id_subsist,cc.descrip_comp,cc.id_componente,te.id_tipo_componente,ctc.descrip_tcomp,
                        te.subest,alarmas,fecha_sale,sum(tir) as tir, sum(cri) as cri, sum(costo) as costo
                        FROM t_eventos te, c_tipo_componentes ctc, c_componentes cc, c_subsistemas cs
                        WHERE ";
                if ($p_subest ne 'TODO'){
                        $sql_eventos2.= "te.subest=\\'$p_subest\\' AND ";
                }
                $sql_eventos2.=" (fecha_sale BETWEEN \\'$f_ini\\' AND \\'$f_fin\\') AND indice='S' ";
                $sql_eventos2.=" AND descrip_subsist= \\'label\\' ";
                $sql_eventos2.=" AND
                        te.id_tipo_componente=ctc.id_tipo_componente AND ctc.id_componente=cc.id_componente AND cc.id_subsist=cs.homoclave
                        GROUP BY descrip_subsist,cs.id_subsist,cc.descrip_comp,cc.id_componente,te.id_tipo_componente,ctc.descrip_tcomp,
                        descrip_tcomp,te.subest,alarmas,fecha_sale
                        ORDER BY cs.id_subsist) AS result
                        GROUP BY descrip_subsist,id_subsist,descrip_comp,descrip_tcomp,subest,alarmas,fecha_sale,tir,cri,costo
                        ORDER BY descrip_subsist,fecha_sale";
                push(@$myArrayOfSQLs, "$sql_eventos2");
                print "push(@$myArrayOfSQLs, $sql_eventos2)";
                push(@$labels, "$descrip_subsist");
                $indis_porcent=(100*$costo)/$indis_total;
                push(@$data_ciento, $indis_porcent);
                push(@$data_costo, $costo);
                push(@$colors, $colores_componentes{$id_subsist});
                push(@$myArrayOfSQLs, $colores_componentes{$id_subsist});
        } #END WHILE ($reg_dcev = $sth->fetchrow_hashref)
} #if ($tuplas > 0)
.
.
I put the SQLs into an array (just like the sector data values are in an
array), and add the array as an extra field:
.
.
$c->addExtraField($data_costo);
$c->addExtraField($myArrayOfSQLs);
$c->setLabelLayout($c.SideLayout);
.
.
Then I use {field0} to represent the array:

my $imageMap = $c->getHTMLImageMap("piestub.pl",
"label={label}&value={value}&percent={percent}&aaa=
{field0}&xxx=$sql_eventos&yyy={field0}",
"title='{dataSetName}@{xLabel}:{field0}'");

After that, I show web result, click in the sector in order view SQL sector value, and the result is:

<ul>
    <div style="font-size:10pt; font-family:arial">
    <li><b>Sistema</b> : @{[$query->param("label")]}</li><br>
    <li><b>Indisponibilidad</b> : @{[$query->param("value")]}</li><br>
    <li><b>Porcentaje</b> : @{[$query->param("percent")]}%</li><br>
    <li><b>Costo</b> :      @{[$query->param("aaa")]}</li>
    <li><b>SQL</b> :      @{[$query->param("yyy")]}</li>
    </div>
</ul>

Costo and SQL have the costo value ($10000), it's no correct. Could you help Peter please.

Thak you.

  Re: piestub from clickable piechart
Posted by Peter Kwan on Nov-16-2015 23:46
| Reply | Monitor
Hi seafree,

Sorry. I forgot that you already had an extra field addExtraField($data_costo). In this case,
the addExtraField($myArrayOfSQLs) is {field1}. If you have multiple extra fields, they are
called {field0}, {field1}, {field2}, etc..

Hope this can help.

Regards
Peter Kwan

  Re: piestub from clickable piechart
Posted by seafree on Nov-18-2015 01:23
| Reply | Monitor
Hi Peter you are incredible :)

Now I have another dude:

When push the each sector then I send appropiarite sql but when I execute sql then I need correct the sql form because is bad formed like this "costoFROM",  then I separate sql sentence with perl "substr", but it's depend sector name length and not always is the same sql length why if I send sql sentence appropiarite why no received the same estring?


if ($tuplas > 0){
        .
        .
        while($reg_dcev = $sth->fetchrow_hashref()) {
               .
                 push(@$myArrayOfSQLs, "$sql_eventos2");

        } #END WHILE ($reg_dcev = $sth->fetchrow_hashref)
} #if ($tuplas > 0)
.
I verify @$myArrayOfSQLs created:

print "---ARRAY SQL: ---<br><br>@$myArrayOfSQLs2";

All the sentence are correct form, the print is before show the chart, after that I create and show the chart:

my $imageMap = $c->getHTMLImageMap("piestub.pl",
"label={label}&value={value}&percent={percent}&aaa={field0}&xxx=$sql_eventos}&yyy={field1}","title='{dataSetName}@{xLabel}:{field0}'");


After that, I received parameters (piestub.pl):

<ul>
    <div style="font-size:10pt; font-family:arial">
    <li><b>Sistema</b> : @{[$query->param("label")]}</li><br>
    <li><b>Indisponibilidad</b> : @{[$query->param("value")]}</li><br>
    <li><b>Porcentaje</b> : @{[$query->param("percent")]}%</li><br>
    <li><b>Costo</b> :      @{[$query->param("aaa")]}</li>
    <li><b>SQL</b> :      @{[$query->param("yyy")]}</li>
    </div>
</ul>

Show web result, but sometimes the param "yyy" is null, could you help me please Peter?. Chartdirector is fantastic.

Regards.

  Re: piestub from clickable piechart
Posted by seafree on Nov-18-2015 03:08
| Reply | Monitor
The null value is clear for me however I don't received well formed SQLs: "costoFROM". do you now any reason?

  Re: piestub from clickable piechart
Posted by Peter Kwan on Nov-18-2015 03:51
| Reply | Monitor
Hi seafree,

Is it possible to save your web page output for the pie chart, with the print @$myArrayOfSQLs2 line include, and also for the "piestub", and email to me (or attach it in
the forum message)? I need to see what is your SQL and how it is mal-formed to diagnose
the problem. My email is "pkwan@advsofteng.net".

Regards
Peter Kwan

  Re: piestub from clickable piechart
Posted by seafree on Nov-18-2015 05:22
| Reply | Monitor
Hi Peter,

I sent you email Peter.

Thank a lot.

  Re: piestub from clickable piechart
Posted by seafree on Nov-19-2015 00:10
| Reply | Monitor
Good Morning Peter:

Do you received mi email with the programs?

Thanks a lot.
seafree

  Re: piestub from clickable piechart
Posted by Peter Kwan on Nov-19-2015 00:20
| Reply | Monitor
Hi seafree,

Thanks for the information. I think the issue may be due to the very long SQL you are
using.

In the current code, the SQL and other information are passed as query parameters to
"piestub.pl". However, there is a practical limit to the length of the URL, which is around
2Kbytes depending on browser. It means the total length of all the query parameters
need to be shorted than 2K bytes. For your case, due to the very long SQL, the URL is
above 2 Kbytes, so the browser may have difficulty processing the query parameters.

To solve the problem, please do not pass large amount of information using query
parameters. There are several methods:

(a) In your original code, it created a lot of very long SQL in the "pie_chart.html" and
pass them to "piestub.pl" as query parameters. Instead, please do not create the SQL in
"pie_chart.html". Simply create them directly in "piestub.pl" so that you do need to pass
the long SQL. If certain information is needed to create the SQL, please pass those
information to "piestub.pl" so that it can create the SQL by itself.

(b) If you must pass a large amount of information from one script "pie_chart.pl" to
another script "piestub.pl", please use session variables instead. For example, you can
store the SQL array created in "pie_chart.pl" into a session variable. Then in "piestub.pl",
youi can read the session variable back to get the array, then get the SQL from the
array.

http://search.cpan.org/~sherzodr/CGI-Session-3.95/Session/Tutorial.pm

Hope this can help.

Regards
Peter Kwan

  Re: piestub from clickable piechart
Posted by seafree on Nov-19-2015 01:45
| Reply | Monitor
It's very simply thanks to you, Peter.

Regards
Seafree

  Re: piestub from clickable piechart
Posted by seafree on Nov-19-2015 05:05
| Reply | Monitor
Hi Peter,  I draw $imageMap:

1) my $imageMap = $c->getHTMLImageMap("piestub.pl","label={label}&value={value}&percent={percent}&aaa={field0}&xxx={field1}&yyy={field2}","title='{dataSetName}@{xLabel}:{field0}'");

However sometimes the parameters xxx and yyy are null, for control that I use the follow:

2)foreach (@$myArrayOfSQLs2){
        print $_ . "<br><br>";
}

2) my $imageMap = $c->getHTMLImageMap("piestub.pl","label={label}&value={value}&percent={percent}&aaa={field0}&xxx={field1}&yyy={field2}","title='{dataSetName}@{xLabel}:{field0}'");

And all parameters have values.

Whats happen with that?, Could you help me please?

Thaks a lot.

  Re: piestub from clickable piechart
Posted by Peter Kwan on Nov-20-2015 00:14
| Reply | Monitor
Hi seafree,

Are you using short SQL now, that is, the {field1} and {field2} are now short, so that
the URL will be a few hundred bytes at most?

I am suspecting, if you use URL that are longer than what the browser can support, the
browser can process the HTML incorrectly or reliably in some cases. It is possible if your
print the contents out, the browser can process it, while if you do not print the
contents, the browser cannot process it properly.

To determine the cause of the problem, please make sure you are using something that is
supported by browsers, which means the URL should not be too long. It means {field1}
and {field2} should be short.

If you are already using short URL, but it still does not solve the problem, would you mind
to email me the resulting "pie_chart.html" to be again? Please use the following methods
to obtain two copies of the "pie_chart.html":

(a) Open the "pie_chart.pl" and use the "Save As" function of the browser to save the
resulting HTML.

(b) Create a web page as follows:

<html><body><a href="/cgi-bin/pie_chart.pl">abc</a></body></html>

(Note: please replace "/cgi-bin/pie_chart.pl" to the actual path of your script.)

Load the above page. You should see a link "abc". "Right click" on the link, and select
"Save Link As" and save the result to a file. Do not "left click" on the link to jump to the
link.

The method (b) above will try to obtain the output of the script directly. In method (a),
the output is obtained from the browser, not directly from the script. If the browser does
not process the HTML correctly, the output in (a) will be different from that in (b).
Comparing the two files may help trouble-shooting the problem.


Regards
Peter Kwan

  Re: piestub from clickable piechart
Posted by seafree on Nov-20-2015 05:10
| Reply | Monitor
Thanks a lot Peter.

Regards
seafree