PHP SQL::fetch_all方法代码示例

 public function filterContent(Comments $comment)
 {
     if (!is_string($check_text)) {
         return $check_text;
     }
     $sql = new SQL();
     $sql->query("select word from filter where level = 2");
     $arr = $sql->fetch_all();
     if (!empty($arr)) {
         foreach ($arr as $v) {
             if (strstr($check_text, $v['word'])) {
                 $check_text = $this->replaceChar($check_text, $v['word']);
             }
         }
     }
     return $check_text;
 }

    case 'y':
        $_ENV['title'] = 'Income Statement: Y:' . $this->date_alpha_f . ' to ' . $this->date_omega_f;
        break;
    default:
        $_ENV['title'] = 'Income Statement: ' . $this->date_alpha_f . ' to ' . $this->date_omega_f;
        break;
}
// Revenues
$sql = " select a.id as account_id,a.code as account_code, a.full_code, a.name as full_name,sum(b.amount) as balance from ";
$sql .= " account a join account_ledger b on a.id=b.account_id join account_journal c on b.account_journal_id=c.id ";
$sql .= " where a.kind like 'Revenue%' and c.date >= '{$this->date_alpha}' and c.date<='{$this->date_omega}' ";
if ('true' == $_GET['xc']) {
    $sql .= " and c.kind != 'C' ";
}
$sql .= " group by a.id,a.full_code,a.code,a.name ";
$sql .= " order by a.full_code,a.code ";
//echo "<p>$sql</p>";
$this->RevenueAccountList = SQL::fetch_all($sql);
Session::flash('fail', SQL::lastError());
// Expenses
$sql = " select a.id as account_id,a.code as account_code, a.full_code, a.name as full_name,sum(b.amount) as balance from ";
$sql .= " account a join account_ledger b on a.id=b.account_id join account_journal c on b.account_journal_id=c.id ";
$sql .= " where a.kind like 'Expense%' and c.date >= '{$this->date_alpha}' and c.date<='{$this->date_omega}' ";
if ('true' == $_GET['xc']) {
    $sql .= " and c.kind != 'C' ";
}
$sql .= " group by a.id,a.full_code,a.code,a.name ";
$sql .= " order by a.full_code,a.code ";
//echo "<p>$sql</p>";
$this->ExpenseAccountList = SQL::fetch_all($sql);
Session::flash('fail', SQL::lastError());

        $sql .= ' abs(amount) = ? OR account_journal_id = ? ';
        $arg[] = abs($q_term);
        $arg[] = intval($q_term);
        break;
    case 'amount':
        $sql .= ' abs(amount) = ? ';
        $arg[] = abs($q_term);
        break;
    case 'open':
    default:
        $sql .= ' note ~* ? ';
        $arg[] = $q_term;
        // OR date = ? ';
}
$sql .= ' ORDER BY date DESC, kind DESC, account_journal_id, amount ';
$res = SQL::fetch_all($sql, $arg);
Session::flash('fail', SQL::lastError());
if (empty($res) || 0 == count($res)) {
    Session::flash('info', 'No Matching Transactions');
    return 0;
}
echo '<table>';
foreach ($res as $rec) {
    echo '<tr class="rero">';
    if ($rec['account_journal_id'] != $account_journal_id_x) {
        // Date
        echo '<td class="c"><a href="' . Radix::link('/account/transaction?id=' . $rec['account_journal_id']) . '">' . date('m/d/y', strtotime($rec['date'])) . '</a></td>';
        // Name & Note
        echo '<td>' . html($rec['note']) . '</td>';
        echo '</tr>';
        echo '<tr><td>&nbsp;</td>';

$sql = 'SELECT count(id) FROM invoice ';
$sql .= ' WHERE status IN (\'Active\') ';
$sql .= ' AND (extract(days from current_timestamp - date) > ?) ';
$res = SQL::fetch_one($sql, array($cli_opt['span']));
echo "<p>" . $res . " Active Invoices to POST</p>\n";
$sql = 'SELECT count(id) FROM invoice ';
$sql .= ' WHERE status IN (\'Hawk\', \'Sent\') ';
$sql .= ' AND (extract(days from current_timestamp - date) > ?) ';
$res = SQL::fetch_one($sql, array($cli_opt['span']));
echo "<p>" . $res . " Sent Invoices over {$cli_opt['span']} days old</p>\n";
// Find Hawkable Invoices
$sql = 'SELECT *, extract(days from current_timestamp - date) as days FROM invoice ';
$sql .= ' WHERE status IN (\'Hawk\', \'Sent\') ';
// $sql.= ' AND ((extract(days from current_timestamp - date)::integer % ?) = 0) ';
$sql .= ' ORDER BY date, contact_id, id ASC ';
$res = SQL::fetch_all($sql);
// , array($cli_opt['span']));
echo "<p>Past Due Invoices: " . count($res) . "</p>\n";
$sum = 0;
foreach ($res as $rec) {
    $iv = new Invoice($rec);
    $co = new Contact($rec['contact_id']);
    $bal = floatval($rec['bill_amount']) - floatval($rec['paid_amount']);
    echo "<p>Customer: {$co['name']} {$co['email']}</p>\n";
    echo "<p>Invoice #{$iv['id']}/{$iv['status']} \${$bal} from {$iv['date']} ({$rec['days']} due)</p>\n";
    if (empty($co['email'])) {
        echo "<p style='color:#f00;font-weight:bold;'>FAIL: No Email for this Contact!</p>\n";
        continue;
    }
    // List of Invoices
    // $sql = 'SELECT * FROM invoice ';

    $html .= _draw_details($res);
} else {
    $html .= "<p>No Pending Work</p>\n";
}
// echo "Active Work:\n";
// _draw_details($res);
// Completed Work - From Yesterday
$html .= "<h2>Completed Work</h2>\n";
$date = strftime('%Y-%m-%d', $time - 86400);
$sql = 'SELECT workorder_item.*,contact.name as contact_name ';
$sql .= '  FROM workorder_item';
$sql .= '  JOIN workorder ON workorder_item.workorder_id = workorder.id ';
$sql .= '  JOIN contact ON workorder.contact_id = contact.id ';
$sql .= '  WHERE workorder_item.date = ? AND workorder_item.status = ? ';
$sql .= '  ORDER BY workorder_item.workorder_id';
$res = SQL::fetch_all($sql, array($date, 'Complete'));
if (count($res)) {
    $html .= _draw_details($res);
} else {
    $html .= "<p>No Completed Work</p>\n";
}
$html .= '</body>';
$html .= '</html>';
// Make New Message
// http://www.emailonacid.com/blog/details/C13/the_importance_of_content-type_character_encoding_in_html_emails
$head = array();
$head['Content-Transfer-Encoding'] = '8bit';
$head['Content-Type'] = 'text/html; charset="UTF-8"';
$head['From'] = sprintf('"%s" <%s>', $_ENV['company']['name'], $_ENV['mail']['from']);
$head['MIME-Version'] = '1.0';
$head['Message-Id'] = md5(openssl_random_pseudo_bytes(256)) . '@' . parse_url($_ENV['application']['base'], PHP_URL_HOST);