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);
}
}
