ab5ni
Posts: 177
|
| Posted: 10/02/2006, 11:45 AM |
|
Hi, Folks.
We're trying to use the report builder to pull data from multiple tables in a MySQL database.
Originally, the tables were created as DBF files in a Microsoft Visual FoxPro database.
We exported all of the tables to MySQL, and now we have to emulate the reports (and all)
as a web-based intranet app. (with CCS generating the php code). The SQL code to
do this in VFP uses many left joins and tables, and I was wondering if there is an
easy way to enter this query, edit it for MySQL, and jam it somewhere into the report
builder. I looked at the expression builder inside of the report builder wizard, but I
couldn't find an "editor mode: click here to enter your SQL query directly." Does
such a mode exist in Report Builder? If not, what's the easiest way to enter
this convoluted, obfiscated SQL command and have MySQL run the thing? :^)
Regards,
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
 |
 |
peterr
Posts: 5971
|
| Posted: 10/02/2006, 12:05 PM |
|
In the Report Builder you'd select "Build Query" and then select "SQL" instead of "Table" in the top-left corner.
Though first you may want to test your query directly in MySQL to assure that it works (since it may be converted from another database syntax).
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
ab5ni
Posts: 177
|
| Posted: 10/02/2006, 12:53 PM |
|
Thanks a bunch, Peter. I'll try the query using the CLI on MySQL. I also have one more
question that's related. I'll just post the original VFP query for reference.
<start VFP code>
SELECT
x.dsid, x.rdt, HOUR(x.rdt) as rdt_hour, MINUTE(x.rdt) as rdt_min, x.ttyp, x.tnum, t.cname as tco,
$ALLTRIM(org.ccity)+", "+$ALLTRIM(org.cstate) as origin_city, org.cname as origin_loc,
dst.cname as dest_loc, $ALLTRIM(dst.ccity)+", "+$ALLTRIM(dst.cstate) as dest_city,
cmp.cname as client, y.afe, y.type, y.bkname, y.rgname, y.wname,
SUBSTR(CMONTH(rdt),1,3)+SUBSTR($ALLTRIM(str(YEAR(rdt))),3,2)+SUBSTR(dsid,2,10)
as dnum,
x.fsc-x.fsc as bRate,
x.fsc-x.fsc as ISC,
x.fsc-x.fsc as FSC,
x.fsc-x.fsc as per,
x.fsc-x.fsc as nen,
x.fsc-x.fsc as haz,
x.fsc-x.fsc as hr,
x.fsc-x.fsc as det,
x.fsc-x.fsc as other,
x.fsc-x.fsc as sTotal,
x.fsc-x.fsc as bHaul,
x.fsc-x.fsc as Total,
x.fsc-x.fsc as Savings,
x.edt,
HOUR(x.edt) as edt_hour, MINUTE(x.edt) as edt_min,
$ALLTRIM(uo.fname)+" "+$ALLTRIM(uo.lname) as orderedby,
$ALLTRIM(ue.fname)+" "+$ALLTRIM(ue.lname) as enteredby
FROM c_d!dsheet x
LEFT OUTER JOIN c_d!accounts y ON x.acc = y.afeid
LEFT JOIN c_d!companies cmp ON cmp.cmpid = y.cmpid
LEFT JOIN c_d!companies org ON org.cmpid = x.ploc
LEFT JOIN c_d!companies dst ON dst.cmpid = x.sloc
LEFT JOIN c_d!companies t ON t.cmpid = x.tco
LEFT JOIN c_d!users uo ON uo.uid = x.dby
LEFT JOIN c_d!users ue ON ue.uid = x.eby
WHERE cmp.cname like lcCname AND TTOD(rdt) >= ldDateLow AND TTOD(rdt) <= ldDateHigh AND DELETED() = .F. AND x.acc = lcAccID INTO CURSOR curDnumList readwrite
</end VFP code>
Needless to say, that's quite a hairy select statement :^). Actually, I've seen worse,
believe it or not  . Anywho, If you'll notice, there are some $ALLTRIM() calls in
the code, along with other functions being called directly, that are being used to
build the SQL statement. Off the top of my head (and as a newb to PHP and CCS),
I was wondering if it would be easier for me to just write the query in PHP and
replace the VFP calls with PHP to build the query in the same fashion? I know
$trim() would replace $ALLTRIM(), and HOUR() and MINUTE() could easily
be replaced with my own $hour() and $minute() calls that would get the data
out of $date("H") and $date("M") calls.
So, my original question could be expanded upon by stating this: Will report
builder allow me to make PHP calls in-line, allowing me to emulate whats
been done in VFP? If not, would be easier/faster to make PHP calls, build a MySQL
query, and then submit the query via mysql_ calls in Apache? Also, if PHP
is the route to go, should I just use something other than the report builder
to build the reports, such as PHP and HTML for an HTML report, or FPDF lib,
ezPDF class, or some other class I don't know about for pdf-based reports?
Best Regards, and I hope there's an easy solution for this out there!
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
peterr
Posts: 5971
|
| Posted: 10/02/2006, 1:22 PM |
|
I recommend using MySQL functions where possible, while you can also modify the SQL dynamically at run-time and add the additional PHP functions that way. Here is the basic syntax: http://docs.codecharge.com/studio3/html/ProgrammingTech.../ModifySQL.html
http://docs.codecharge.com/studio3/html/ProgrammingTech...HEREClause.html
This way you can either modify the default Data Source SQL, or ignore and replace it.
As for using other techniques, this probably fully depends on specific situation, report complexity, your programming skills, etc. You basically would need to program the eholw layout with indents, groupings, page-breaks, etc.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
