project posting

[contact at echazen.com]

>From a client:

============================================================
 
I've developed a large application in PHP/Mysql. The application is in use and bookkeeping data is being generated by it. So many initially unrequested features have been added to the application that it is surpassing my moderate MYSQL skills. There are 20+ reports that are working but could be improved upon. I am willing to pay a consultant that can help improve my SQL throughout the report subsystem in this application. As all of the reports are already working I don't estimate there will be a lot of hours on this project, but I am willing to pay a premium hourly rate to a real guru. 

I am self taught, PERL/PHP/MYSQL but all you'll need to know is MYSQL. If this query makes sense to you and you think you can improve it, you're the person for me. 

$query = "INSERT INTO $db_tmp3 
(SELECT a.room,a.folio,CONCAT(n.lname,', ',n.fname), 

-- bal fwd 
IF(a.time=$start, ' ',TRUNCATE((a.amnt/a.days)*(TO_DAYS(ADDDATE(rt.time, INTERVAL (sm.days) DAY)) - TO_DAYS($end)),2)), 

-- income 
IF(a.time=$start, TRUNCATE((a.amnt/a.days)*(TO_DAYS(ADDDATE(rt.time, INTERVAL (sm.days) DAY)) - TO_DAYS($end)),2), ' '), 

c.translate,' ', 

-- tax 
TRUNCATE(a.amnt/a.days - ROUND(((a.amnt/IF(sm.days>30,100,$calc_taxrt))/a.days)*100,2),2), 

-- rent 
ROUND(((a.amnt/IF(sm.days>30,100,$calc_taxrt) )/a.days)*100,2), 

TRUNCATE((a.amnt/a.days)*(TO_DAYS(ADDDATE(rt.time, INTERVAL sm.days DAY)) - TO_DAYS($end) -1),2), 

-- ckin 
DATE_FORMAT(rt.time, '%m/%e/%Y'), 
-- so 
DATE_FORMAT(ADDDATE(rt.time, INTERVAL (sm.days-a.days) DAY), '%m/%e'), 
-- ckout 
DATE_FORMAT(ADDDATE(rt.time, INTERVAL sm.days DAY), '%m/%e') 

FROM $db_tmp AS a 

LEFT JOIN $db_tmp4 AS dw ON a.folio=dw.folio 

LEFT JOIN $db_rtran AS rt ON a.folio=rt.folio AND rt.time<=$end 
LEFT JOIN $db_rtran AS rt2 ON a.folio=rt2.folio AND rt.time<=$end AND rt.time>rt2.time 

LEFT JOIN $db_fol AS f ON a.folio=f.folio 
LEFT JOIN id_DB AS n ON n.sys_id=f.sys_id AND (n.type=1 OR n.type=3) AND n.hotel='$use_hotel' 
LEFT JOIN codes AS c ON (a.paid=c.code) AND c.tblname='howpaid' 

LEFT JOIN $db_tmp2 AS sm ON a.folio=sm.folio 

WHERE TO_DAYS( ADDDATE(rt.time, INTERVAL (sm.days) DAY) )>TO_DAYS($end) AND dw.dw='W' AND ISNULL(rt2.folio) AND rt.time<=$end 
GROUP BY a.folio ORDER BY a.ID ) 

UNION 

(SELECT a.room,a.folio,CONCAT(n.lname,', ',n.fname), 

-- bal fwd 
IF(a.time=$start, ' ',TRUNCATE((a.amnt/a.days)*(TO_DAYS(ADDDATE(rt.time, INTERVAL (sm.days) DAY)) - TO_DAYS($end)),2)), 

-- income 
IF(a.time=$start, TRUNCATE((a.amnt/a.days)*(TO_DAYS(ADDDATE(rt.time, INTERVAL (sm.days) DAY)) - TO_DAYS($end)),2), ' '), 

c.translate, 

-- rent 
ROUND(((a.amnt/IF(sm.days>30,100,$calc_taxrt) )/a.days)*100,2), 

-- tax 
TRUNCATE(a.amnt/a.days - ROUND(((a.amnt/IF(sm.days>30,100,$calc_taxrt))/a.days)*100,2),2), 

' ', 

TRUNCATE((a.amnt/a.days)*(TO_DAYS(ADDDATE(a.time, INTERVAL sm.days DAY)) - TO_DAYS($end) -1),2), 

-- ckin 
DATE_FORMAT(rt.time, '%m/%e/%Y'), 
-- so 
DATE_FORMAT(ADDDATE(rt.time, INTERVAL (sm.days-a.days) DAY), '%m/%e'), 
-- ckout 
DATE_FORMAT(ADDDATE(rt.time, INTERVAL sm.days DAY), '%m/%e') 

FROM $db_tmp AS a 
LEFT JOIN $db_tmp4 AS dw ON a.folio=dw.folio 

LEFT JOIN $db_rtran AS rt ON a.folio=rt.folio AND rt.time<=$end 
LEFT JOIN $db_rtran AS rt2 ON a.folio=rt2.folio AND rt.time<=$end AND rt.time>rt2.time 

LEFT JOIN $db_fol AS f ON a.folio=f.folio 
LEFT JOIN id_DB AS n ON n.sys_id=f.sys_id AND (n.type=1 OR n.type=3) AND n.hotel='$use_hotel' 
LEFT JOIN codes AS c ON (a.paid=c.code) AND c.tblname='howpaid' 

LEFT JOIN $db_tmp2 AS sm ON a.folio=sm.folio 

WHERE TO_DAYS( ADDDATE(rt.time, INTERVAL (sm.days) DAY) )>TO_DAYS($end) AND dw.dw='D' AND ISNULL(rt2.folio) AND rt.time<=$end 
GROUP BY a.folio ORDER BY a.ID ) 

ORDER BY room,folio"; 

mysql_query($query) or die("$query failed " . mysql_error()); 

========================================================================

This guy is willing to pay good money for help.  This is beyond me.  Please contact me if you can do this.

Jason Eugene Ho-Ching
eChazen.com Internet Solutions
jason at echazen.com
AIM: echazensupport
YAHOO: echazen
MSN: support at echazen.com
Phone:  480.452.8560
Fax: (866)284-3605