protected function action_run() { $stmt = $this->db->query('select * from query where query_id = :query_id', array('query_id' => $_POST['query_id'])); if ($row = $stmt->fetch()) { if (stripos($row['query'], 'file ') === 0) { $this->transfer(substr($row['query'], 5), array('dest' => $_POST['dest'])); } else { $stmt2 = $this->db->query($row['query']); $r = new Report(); if ($_POST['dest'] == 'screen') { $r->html($row['title'], $stmt2); } else { if ($_POST['dest'] == 'pdf') { $r->pdf($row['title'], $stmt2); } else { if ($_POST['dest'] == 'csv') { $r->csv($stmt2); } } } } } else { $this->message('Query not found'); } }
protected function action_report() { $stmt = $this->db->query('select name, count(specialty_id) as count from member join specialty using (specialty_id) group by specialty_id order by name'); if ($stmt->rowCount() == 0) { $this->message('No records found', true); } else { $r = new Report(); if ($_POST['dest'] == 'screen') { $r->html('Speciaties', $stmt); } else { if ($_POST['dest'] == 'pdf') { $r->pdf('Speciaties', $stmt); } } } }
protected function action_report() { $hdgs = array('Number', 'Date', 'Start', 'Stop', 'Title'); $ttl = "{$_POST['year']} Panels"; $stmt = $this->db->query('select number, date_held, time_start, time_stop, title from panelcwa where year = :year order by number', array('year' => $_POST['year'])); if ($stmt->rowCount() == 0) { $this->message('No records found', true); } else { $r = new Report(); if ($_POST['dest'] == 'screen') { $r->html($ttl, $stmt, $hdgs); } else { if ($_POST['dest'] == 'pdf') { $r->pdf($ttl, $stmt, array(50, 50, 50, 50), $hdgs); } else { if ($_POST['dest'] == 'csv') { $r->csv($stmt); } } } } }
function generate_report() { $db = Database::getInstance(); $mysqli = $db->getConnection(); $hdgs = array('First', 'Last', 'Email', 'Username', 'Phone No'); //$hdgs = array('First', 'Last'); $ttl = "Registered Users"; $sql = "SELECT fname, lname, email, username, phone_no FROM users"; /**$members = array( 0 => array(0 => 'Andy', 1 => 'Gutmans'), 1 => array(0 => 'Leon', 1 => 'Atkinson'), 2 => array(0 => 'Stigg', 1 => 'Bakken') );*/ //$report = new Report(); //$report->pdf($ttl, $members, NULL, $hdgs, NULL, 'P', 'A4'); $result = $mysqli->query($sql); if ($result->num_rows == 0) { echo "No records found"; } else { $report = new Report(); $report->pdf($ttl, $result, $hdgs, $widths, $aligns, $orientation, $page_size); } }
/** * Generate a PDF report of rent collection for a specified period of time * @param int $prop_id The ID used to identify the property * @param string $start A date specifying the start period of collection * @param string $end The end period of rent collection * @return file A PDF file containing the report in tabular format */ public static function generatePdfReport($prop_id, $start, $end) { $hdgs = array('name', 'room_no', 'date_paid', 'receipt_no', 'amount'); $aligns = array('L', 'L', 'L', 'L', 'R'); $widths = NULL; $property = Property::findById($prop_id); $ttl = $property->getPropertyName(); $db = Database::getInstance(); $mysqli = $db->getConnection(); // Fetch rent payments for period $sql = "SELECT CONCAT_WS(' ', t.fname, t.lname) AS name, rm.label AS room_no, "; $sql .= "rt.date_paid, rt.receipt_no, FORMAT(rt.amount,0) AS amount FROM tenants t "; $sql .= "RIGHT JOIN room rm ON rm.id = t.rid RIGHT JOIN rent rt ON "; $sql .= "rt.tid = t.id WHERE rt.date_paid >= '" . $mysqli->real_escape_string($start); $sql .= "' AND rt.date_paid <= '" . $mysqli->real_escape_string($end) . "' "; $sql .= "AND rt.pid = " . $mysqli->real_escape_string($prop_id); $result = $mysqli->query($sql); // Check Arrears Paid $qry = "SELECT CONCAT_WS(' ', t.fname, t.lname) AS name, rm.label AS room_no, "; $qry .= "DATE_FORMAT(ap.start_date, '%M %Y') AS month, ap.date_paid, ap.receipt_no, "; $qry .= "FORMAT(ap.amount, 0) as amount FROM tenants t RIGHT JOIN room rm ON "; $qry .= "rm.id = t.rid RIGHT JOIN arrears_paid ap ON ap.tid = t.id WHERE (SELECT "; $qry .= "CONVERT(ap.date_paid, DATE) BETWEEN '" . $mysqli->real_escape_string($start); $qry .= "' AND '" . $mysqli->real_escape_string($end) . "' = 1)"; $arrears_paid = $mysqli->query($qry); // Check Outstanding Arrears $arr = "SELECT CONCAT_WS(' ', t.fname, t.lname) AS name, rm.label AS room_no, "; $arr .= "DATE_FORMAT(ar.start_date, '%M %Y') AS month, FORMAT(ar.amount, 0) AS "; $arr .= "amount FROM tenants t RIGHT JOIN room rm ON rm.id = t.rid RIGHT JOIN "; $arr .= "arrears ar ON ar.tid = t.id"; $arrears = $mysqli->query($arr); // Expenses $exp = "SELECT name AS name_of_expense, DATE_FORMAT(start_date, '%M %Y') AS month, "; $exp .= "incurred AS date_made, FORMAT(amount, 0) AS amount FROM expense "; $exp .= "WHERE (SELECT incurred BETWEEN '" . $mysqli->real_escape_string($start) . "' "; $exp .= "AND '" . $mysqli->real_escape_string($end) . "' = 1)"; $expenses = $mysqli->query($exp); if ($result->num_rows == 0) { return false; } else { $report = new Report(); $report->pdf($ttl, $result, $arrears_paid, $arrears, $expenses, $hdgs, $widths, $aligns, 'P', 'A4', $prop_id, $start, $end); } }