function handle(&$params)
{
$app =& Dataface_Application::getInstance();
$query = $app->getQuery();
$query['-skip'] = 0;
$query['-limit'] = 999999999;
$at = Dataface_ActionTool::getInstance();
$emailAction = $at->getAction(array('name' => 'email'));
if (!isset($emailAction) or !isset($emailAction['email_column'])) {
return PEAR::raiseError("No email column specified");
}
$col = $emailAction['email_column'];
$qb = new Dataface_QueryBuilder($query['-table'], $query);
$sql = "select `" . $col . "` " . $qb->_from() . $qb->_secure($qb->_where());
$res = mysql_query($sql, df_db());
if (!$res) {
trigger_error(mysql_error(df_db()), E_USER_ERROR);
}
$addresses = array();
while ($row = mysql_fetch_row($res)) {
$addresses[] = $row[0];
}
@mysql_free_result($res);
header("Content-type: text/plain");
echo implode(', ', $addresses);
exit;
}
function block__before_result_list_content()
{
$app = Dataface_Application::getInstance();
$query = $app->getQuery();
$builder = new Dataface_QueryBuilder($query['-table'], $query);
$sql = 'select sum(num_words) ' . $builder->_from() . $builder->_where();
$res = df_q($sql);
$row = mysql_fetch_row($res);
@mysql_free_result($res);
$app->addHeadContent('<style type="text/css">#total-words-found {float:right;width: 200px;}</style>');
echo '<div id="total-words-found">Total Words: ' . $row[0] . '</div>';
Dataface_JavascriptTool::getInstance()->import('swete/actions/batch_google_translate.js');
}
/**
* Creates an import table for this table. An import table is an empty clone
* of this table. It serves as an intermediate step towards importing data into
* the main table.
*/
function createImportTable()
{
/*
* It is a good idea to clean the import tables before we create them.
* That way they don't get cluttered
*/
$this->cleanImportTables();
$rand = rand(10000, 999999);
$name = $this->tablename . '__import_' . strval(time()) . '_' . strval($rand);
$qb = new Dataface_QueryBuilder($this->tablename);
$res = mysql_query("CREATE TABLE `{$name}` SELECT * " . $qb->_from() . " LIMIT 0", $this->db);
if (!$res) {
trigger_error("Failed to create import table `{$name}` because a mysql error occurred: " . mysql_error($this->db) . "\n" . Dataface_Error::printStackTrace(), E_USER_ERROR);
}
return $name;
}
function getResultFilters()
{
ob_start();
$app =& Dataface_Application::getInstance();
$query =& $app->getQuery();
echo '<div class="resultlist-filters">
<h3>' . df_translate('scripts.Dataface_ResultList.MESSAGE_FILTER_RESULTS', 'Filter Results') . ':</h3>
<script language="javascript"><!--
function resultlist__updateFilters(col,select){
var currentURL = "' . $app->url('') . '";
var currentParts = currentURL.split("?");
var currentQuery = "?"+currentParts[1];
var value = select.options[select.selectedIndex].value;
var regex = new RegExp(\'([?&])\'+col+\'={1,2}[^&]*\');
if ( currentQuery.match(regex) ){
if ( value ){
prefix = "=";
} else {
prefix = "";
}
currentQuery = currentQuery.replace(regex, \'$1\'+col+\'=\'+prefix+encodeURIComponent(value));
} else {
currentQuery += \'&\'+col+\'==\'+encodeURIComponent(value);
}
window.location=currentParts[0]+currentQuery;
}
//--></script>
<ul>';
$qb = new Dataface_QueryBuilder($this->_table->tablename, $query);
foreach ($this->_filterCols as $col) {
$field =& $this->_table->getField($col);
unset($vocab);
if (isset($field['vocabulary'])) {
$vocab =& $this->_table->getValuelist($field['vocabulary']);
} else {
$vocab = null;
}
echo '<li> ' . htmlspecialchars($field['widget']['label']) . ' <select onchange="resultlist__updateFilters(\'' . addslashes($col) . '\', this);"><option value="">' . df_translate('scripts.GLOBAL.LABEL_ALL', 'All') . '</option>';
$res = df_query("select `{$col}`, count(*) as `num` " . $qb->_from() . " " . $qb->_secure($qb->_where(array($col => null))) . " group by `{$col}`", null, true);
if (!$res and !is_array($res)) {
trigger_error(mysql_error(df_db()), E_USER_ERROR);
}
if (@$query[$col] and $query[$col][0] == '=') {
$queryColVal = substr($query[$col], 1);
} else {
$queryColVal = @$query[$col];
}
//while ( $row = mysql_fetch_assoc($res) ){
foreach ($res as $row) {
if (isset($vocab) and isset($vocab[$row[$col]])) {
$val = $vocab[$row[$col]];
} else {
$val = $row[$col];
}
if ($queryColVal == $row[$col]) {
$selected = ' selected';
} else {
$selected = '';
}
echo '<option value="' . htmlspecialchars($row[$col]) . '"' . $selected . '>' . htmlspecialchars($val) . ' (' . $row['num'] . ')</option>';
}
//@mysql_free_result($res);
echo '</select></li>';
}
echo '</ul></div>';
$out = ob_get_contents();
ob_end_clean();
return $out;
}
function getTitles($ordered = true, $genericKeys = false, $ignoreLimit = false)
{
$app =& Dataface_Application::getInstance();
if (!isset($this->_titles[$ordered][$genericKeys][$ignoreLimit])) {
$titleColumn = $this->_table->titleColumn();
$keys = array_keys($this->_table->keys());
if (!is_array($keys) || count($keys) == 0) {
throw new Exception(df_translate('No primary key defined', 'There is no primary key defined on table "' . $this->_table->tablename . '". Please define a primary key.', array('table' => $this->_table->tablename, 'stack_trace' => '')), E_USER_ERROR);
}
$len = strlen($titleColumn);
if ($titleColumn[$len - 1] != ')' and $titleColumn[$len - 1] != '`') {
$titleColumn = '`' . $titleColumn . '`';
}
$builder = new Dataface_QueryBuilder($this->_tablename, $this->_query);
$builder->action = 'select';
$from = $builder->_from();
$sql = "SELECT `" . implode('`,`', $keys) . "`,{$titleColumn} as `__titleColumn__` {$from}";
$where = $builder->_where();
$where = $builder->_secure($where);
$limit = $builder->_limit();
if (strlen($where) > 0) {
$sql .= " {$where}";
}
if ($ordered) {
$sql .= " ORDER BY `__titleColumn__`";
} else {
$sql .= $builder->_orderby();
}
if (strlen($limit) > 0 and !$ignoreLimit) {
$sql .= " {$limit}";
} else {
if (!$ignoreLimit) {
$sql .= " LIMIT 250";
}
}
$res = $this->dbObj->query($sql, $this->_table->db, null, true);
if (!$res and !is_array($res)) {
$app->refreshSchemas($this->_table->tablename);
// updates meta tables such as workflow tables to make sure that they
// are up to date.
$res = $this->dbObj->query($sql, $this->_table->db, null, true);
if (!$res and !is_array($res)) {
throw new Exception(df_translate('scripts.Dataface.QueryTool.getTitles.ERROR_ERROR_RETRIEVING_TITLES', "Error retrieving title from database in Dataface_QueryTool::getTitles(): ") . $sql . xf_db_error($this->_table->db), E_USER_ERROR);
}
}
$titles = array();
//while ( $row = xf_db_fetch_row($res) ){
foreach ($res as $row) {
$title = array_pop($row);
if (!$genericKeys) {
$keyvals = array();
reset($keys);
while (sizeof($row) > 0) {
$keyvals[current($keys)] = array_shift($row);
next($keys);
}
$keystr = '';
foreach ($keyvals as $keykey => $keyval) {
$keystr .= urlencode($keykey) . "=" . urlencode($keyval) . "&";
}
$keystr = substr($keystr, 0, strlen($keystr) - 1);
$titles[$keystr] = $title;
} else {
$titles[] = $title;
}
}
//@xf_db_free_result($res);
$this->_titles[$ordered][$genericKeys][$ignoreLimit] =& $titles;
}
return $this->_titles[$ordered][$genericKeys][$ignoreLimit];
}
/**
* Returns the from clause for the SQL query.
* eg: FROM users
*/
function _from($tablename = null)
{
$app =& Dataface_Application::getInstance();
if (!isset($tablename)) {
$tablename = $this->_table->tablename;
}
$table =& Dataface_Table::loadTable($tablename);
$proxyView = $table->getProxyView();
$tsql = $table->sql();
$fromq = '';
if ($proxyView) {
$fromq = "`" . $proxyView . "`";
} else {
if (isset($tsql)) {
$fromq = "(" . $tsql . ")";
} else {
$fromq = "`" . $this->tablename($tablename) . "`";
}
}
$parent =& $table->getParent();
if (isset($parent)) {
$qb2 = new Dataface_QueryBuilder($parent->tablename);
$pfrom = $qb2->_from();
$as_pos = strpos(strtolower($pfrom), ' as ') !== false ? strlen($pfrom) - strpos(strrev(strtolower($pfrom)), ' sa ') - 3 : false;
if ($as_pos !== false) {
$pfrom = substr($pfrom, strlen('FROM '), $as_pos - strlen('FROM '));
} else {
$pfrom = substr($pfrom, strlen('FROM '));
}
$pkeys = array_keys($parent->keys());
$ckeys = array_keys($table->keys());
$joinq = array();
for ($i = 0; $i < count($pkeys); $i++) {
$joinq[] = '`t___child`.`' . $ckeys[$i] . '`=`t___parent`.`' . $pkeys[$i] . '`';
}
$joinq = implode(' and ', $joinq);
$out = "FROM (select * from " . $fromq . " as `t___child` left join " . $pfrom . " as `t___parent` on ({$joinq})) as `" . $this->tablename($tablename) . "`";
} else {
if (isset($tsql) or isset($proxyView)) {
$out = "FROM " . $fromq . " as `" . $this->tablename($tablename) . "`";
} else {
$out = "FROM " . $fromq;
}
}
if ($this->metadata and $this->action == 'select') {
$out .= " LEFT JOIN `{$tablename}__metadata` ON ";
$keys = array_keys($table->keys());
if (count($keys) == 0) {
throw new Exception("The table '" . $tablename . "' has no primary key.", E_USER_ERROR);
}
$clauses = array();
foreach ($keys as $key) {
$clauses[] = "`{$tablename}`.`{$key}`=`{$tablename}__metadata`.`{$key}`";
}
$out .= "(" . implode(' and ', $clauses) . ")";
}
return $out;
}
/**
* @brief Creates an import table for this table. An import table is an empty clone
* of this table. It serves as an intermediate step towards importing data into
* the main table.
* @return string The name of the created table.
* @see getImportTables()
*
*/
function createImportTable()
{
import('Dataface/QueryBuilder.php');
/*
* It is a good idea to clean the import tables before we create them.
* That way they don't get cluttered
*/
$this->cleanImportTables();
$rand = rand(10000, 999999);
$name = $this->tablename . '__import_' . strval(time()) . '_' . strval($rand);
$qb = new Dataface_QueryBuilder($this->tablename);
$res = xf_db_query("CREATE TABLE `{$name}` SELECT * " . $qb->_from() . " LIMIT 0", $this->db);
if (!$res) {
throw new Exception("Failed to create import table `{$name}` because a mysql error occurred: " . xf_db_error($this->db) . "\n", E_USER_ERROR);
}
return $name;
}
/**
* implements action handle() method.
*/
function handle(&$params)
{
$action =& $params['action'];
//print_r($params);
$app =& Dataface_Application::getInstance();
$query =& $app->getQuery();
$query['-skip'] = 0;
$query['-limit'] = 9999999;
// Let's validate some of the parameters first
// The actions.ini file should define an email_column and email_table parameters
// to indicate:
// a. the name of the column from the current table that should be used
// as the "send to" email address.
// b. the name of the table that should store the email messages.
if (!@$action['email_column']) {
return PEAR::raiseError("No email column specified in actions.ini", DATAFACE_E_WARNING);
}
if (!@$action['email_table']) {
return PEAR::raiseError("No email table specified in actions.ini", DATAFACE_E_WARNING);
}
// Make sure the table and column names are not malicious.
$this->emailColumn = $col = $action['email_column'];
if (strpos($col, '`') !== false) {
return PEAR::raiseError("Invalid email column name: '{$col}'", DATAFACE_E_WARNING);
}
$this->emailTable = $table = $action['email_table'];
if (strpos($table, '`') !== false) {
return PEAR::raiseError("Invalid email table name: '{$table}'", DATAFACE_E_WARNING);
}
$this->joinTable = $join_table = $query['-table'] . '__' . $table;
$this->recipientsTable = $query['-table'];
// The name of the table that tracks which records have had email sent.
// Next make sure that the email table(s) exist(s)
if (!Dataface_Table::tableExists($table, false) || !Dataface_Table::tableExists($join_table, false)) {
$this->createEmailTables($table, $join_table);
}
$emailTableObj =& Dataface_Table::loadTable($this->emailTable);
$contentField =& $emailTableObj->getField('content');
$contentField['widget']['atts']['rows'] = 20;
$contentField['widget']['atts']['cols'] = 60;
$contentField['widget']['label'] = 'Message body';
$contentField['widget']['description'] = 'Please enter your message content in plain text.';
$contentField['widget']['type'] = 'htmlarea';
$contentField['widget']['editor'] = 'nicEdit';
$subjectField =& $emailTableObj->getField('subject');
$subjectField['widget']['atts']['size'] = 60;
$fromField =& $emailTableObj->getField('from');
$fromField['widget']['atts']['size'] = 60;
$fromField['widget']['description'] = 'e.g. Web Lite Solutions <info@weblite.ca>';
$ccField =& $emailTableObj->getField('cc');
$ccField['widget']['atts']['size'] = 60;
$ignoreBlacklistField =& $emailTableObj->getField('ignore_blacklist');
$ignoreBlacklistField['widget']['type'] = 'checkbox';
$ignoreBlacklistField['widget']['description'] = 'The black list is a list of email addresses that have opted out of receiving email. I.e. Users on the black list do not want to receive email. Check this box if you want to send to blacklisted addresses despite their wish to be left alone.';
$form = df_create_new_record_form($table);
$form->_build();
$form->addElement('hidden', '-action');
$form->addElement('hidden', '-table');
$form->setDefaults(array('-action' => $query['-action'], '-table' => $query['-table']));
$form->insertElementBefore($form->createElement('checkbox', 'send_now', '', 'Send now (leave this box unchecked if you wish these emails to be queued for later sending by the daily cron job. Recommended to leave this box unchecked for large found sets (>100 records).)'), 'submit_new_newsletters_record');
$form->addElement('hidden', '-query_string');
$form->setDefaults(array('-query_string' => base64_encode(serialize($query))));
if (@$app->_conf['from_email']) {
$form->setDefaults(array('from' => $app->_conf['from_email']));
}
if ($form->validate()) {
$res = $form->process(array(&$form, 'save'), true);
if (PEAR::isError($res)) {
return $res;
}
// The form saved ok.. so we can send the emails.
//$resultSet = $app->getResultSet();
//$resultSet->loadSet();
//$it =& $resultSet->iterator();
$vals = $form->exportValues();
$q2 = unserialize(base64_decode($vals['-query_string']));
//print_r($q2);
//exit;
$qb = new Dataface_QueryBuilder($query['-table'], $q2);
$sql = "insert ignore into `{$join_table}` (recipient_email,messageid,date_created) select `" . $col . "`, '" . addslashes($form->_record->val('id')) . "' as messageid, now() as date_created " . $qb->_from() . " " . $qb->_secure($qb->_where());
//echo $sql;exit;
$sres = mysql_query($sql, df_db());
if (!$sres) {
trigger_error(mysql_error(df_db()), E_USER_ERROR);
}
//while ($row = mysql_fetch_row($sres) ){
// $join_rec = new Dataface_Record($join_table, array('messageid'=>$form->_record->val('id'),
// 'recipient_email'=>$row[0],
// 'date_created'=>date('Y-m-d h:i:s')));
// $res = $join_rec->save();
// if ( !$res ) return PEAR::raiseError("Failed to add entry for email '".$curr->val($col)."'", DATAFACE_E_WARNING);
// unset($join_rec);
// unset($curr);
//}
//$it = df_get_records($query['-table'], $q2);
//while ( $it->hasNext() ){
// $curr =& $it->next();
// $join_rec = new Dataface_Record($join_table, array('messageid'=>$form->_record->val('id'),
// 'recipient_email'=>$curr->val($col),
// 'date_created'=>date('Y-m-d h:i:s')));
// $res = $join_rec->save();
// if ( !$res ) return PEAR::raiseError("Failed to add entry for email '".$curr->val($col)."'", DATAFACE_E_WARNING);
// unset($join_rec);
// unset($curr);
//}
//$this->messages = array();
// If we're set to send the email right now
//if ( $form->exportValue('send_now') ){
// $this->sendMail($form->_record->val('id'));
//}
$this->postJob($form->_record->val('id'), $this->emailTable, $this->joinTable, $this->recipientsTable, $this->emailColumn);
//$this->messages[] = "Email has been queued for delivery.";
//if ( count($this->messages) > 0 ){
//$_SESSION['--msg'] = implode("\n",$this->messages);
//echo $_SESSION['--msg'];
//exit;
//}
$q2['-action'] = 'list';
unset($q2['-limit']);
header('Location: ' . $app->url($q2) . '&--msg=' . urlencode("The message has been queued for delivery"));
exit;
}
$addresses = array();
//$resultSet = $app->getResultSet();
//$resultSet->loadSet();
//$it =& $resultSet->iterator();
//$it = df_get_records($query['-table'], array_merge($query, array('-limit'=>30)));
//while ( $it->hasNext() ){
// $curr =& $it->next();
// $addresses[] = $curr->val($col);
//
// unset($curr);
//}
ob_start();
$form->display();
$context = array();
$context['email_form'] = ob_get_contents();
$profileTable =& Dataface_Table::loadTable($query['-table']);
$context['fields'] = array_keys($profileTable->fields(false, true, true));
//$context['blacklist'] = $this->getBlackListed($addresses);
//$context['addresses'] = array_diff($addresses, $context['blacklist']);
ob_end_clean();
df_register_skin('email', DATAFACE_PATH . '/modules/Email/templates');
df_display($context, 'email_form.html');
}
function test_metadata_from()
{
$app =& Dataface_Application::getInstance();
$original_value = @$app->_conf['metadata_enabled'];
$app->_conf['metadata_enabled'] = 1;
import('Dataface/MetadataTool.php');
$mt = new Dataface_MetadataTool('Profiles');
$mt->createMetadataTable();
$builder = new Dataface_QueryBuilder('Profiles');
echo $from = $builder->_from();
$sql = "select * {$from} where id='10'";
//$res = xf_db_query($sql, $app->db());
//if ( !$res ) trigger_error(xf_db_error($app->db()), E_USER_ERROR);
}
