public function get_date1_date2_dept_view() { $start = FormLib::get('date1'); $end = FormLib::get('date2'); $dept_limit = FormLib::get('dept'); $method = FormLib::get('sql-method'); $dlog = false; $dbc = FannieDB::get($this->config->get('OP_DB')); $timing_point_1 = microtime(true); switch ($method) { case 'Large Temporary Table': $where = array('connection' => $dbc, 'clauses' => array(array('sql' => ' trans_type IN (\'I\',\'D\') ', 'params' => array()), array('sql' => ' department BETWEEN 0 AND ? ', 'params' => array($dept_limit)))); $dlog = DTransactionsModel::selectDTrans($start, $end, $where); break; case 'Aggregate Temporary Table': $where = new stdclass(); $where->sql = array("trans_type IN ('I', 'D')", "department BETWEEN 0 AND ?"); $where->params = array($dept_limit); $groupby = array('department'); $dlog = DTransactionsModel::aggregateDtrans($dbc, $start, $end, $where, $groupby); break; case 'Single Query': default: $dlog = DTransactionsModel::selectDTrans($start, $end); break; } $timing_point_2 = microtime(true); $ret = '<p>Using archive table(s): <em>' . $dlog . '</em></p>'; $query = ' SELECT d.dept_name, SUM(t.total) AS ttl FROM ' . $dlog . ' AS t LEFT JOIN departments AS d ON t.department=d.dept_no WHERE t.datetime BETWEEN ? AND ? AND trans_type IN (\'I\', \'D\') AND t.department BETWEEN 0 AND ? GROUP BY d.dept_name'; $prep = $dbc->prepare($query); $args = array($start . ' 00:00:00', $end . ' 23:59:59', $dept_limit); $result = $dbc->execute($prep, $args); $timing_point_3 = microtime(true); $ret .= '<p>Query used:<pre>' . $query . '</pre></p>'; $ret .= '<p>Query succeeded: ' . ($result ? 'Yes' : 'No') . '</p>'; $ret .= '<p>Elapsed time: ' . ($timing_point_3 - $timing_point_1) . '</p>'; return $ret; }