- $fromdate = date2sql($from);
- $todate = date2sql($to);
-
- $sql = "SELECT ".TB_PREF."audit_trail.*, ".TB_PREF."gl_trans.tran_date,
- SUM(IF(".TB_PREF."gl_trans.amount > 0, ".TB_PREF."gl_trans.amount, 0)) AS amount,
- ".TB_PREF."users.user_id,
- UNIX_TIMESTAMP(".TB_PREF."audit_trail.stamp) as unix_stamp
- FROM ".TB_PREF."audit_trail, ".TB_PREF."gl_trans, ".TB_PREF."users
- WHERE ".TB_PREF."audit_trail.type = ".TB_PREF."gl_trans.type
- AND ".TB_PREF."audit_trail.trans_no = ".TB_PREF."gl_trans.type_no
- AND ".TB_PREF."audit_trail.user = ".TB_PREF."users.id ";
+ $fromdate = date2sql($from) . " 00:00:00";
+ $todate = date2sql($to). " 23:59.59";
+
+ $sql = "SELECT a.*,
+ SUM(IF(ISNULL(g.amount), NULL, IF(g.amount > 0, g.amount, 0))) AS amount,
+ u.user_id,
+ UNIX_TIMESTAMP(a.stamp) as unix_stamp
+ FROM ".TB_PREF."audit_trail AS a JOIN ".TB_PREF."users AS u
+ LEFT JOIN ".TB_PREF."gl_trans AS g ON (g.type_no=a.trans_no
+ AND g.type=a.type)
+ WHERE a.user = u.id ";