public function displayFormControl()
{
// Identify which forms have data in the database
global $wpdb;
$tableName = $this->plugin->getSubmitsTableName();
$rows = $wpdb->get_results("select distinct `form_name` from `{$tableName}` order by `form_name`");
?>
<div class="shortcodeoptions">
<!-- <div class="label_box"><label-->
<!-- for="form_name_cntl">--><?php
//echo htmlspecialchars(__('form', 'contact-form-7-to-database-extension'))
?>
<!--</label>-->
<!-- </div>-->
<select name="form_name_cntl" id="form_name_cntl" multiple size="20">
<!-- <option value=""-->
<!-- disabled>--><?php
//echo htmlspecialchars(__('* Select a form *', 'contact-form-7-to-database-extension'))
?>
<!--</option>-->
<?php
$formNameList = explode(',', $this->requestParams['postedForm']);
if (count($formNameList) > 1) {
$formNameList[] = $this->requestParams['postedForm'];
}
foreach ($rows as $aRow) {
$formName = $aRow->form_name;
$selected = in_array($formName, $formNameList) ? 'selected' : '';
$formNameEscaped = htmlspecialchars($formName, ENT_QUOTES, 'UTF-8');
?>
<option value="<?php
echo $formNameEscaped;
?>
" <?php
echo $selected;
?>
><?php
echo $formNameEscaped;
?>
</option>
<?php
}
$selected = in_array('*', $formNameList) ? 'selected' : '';
?>
<option value="*" <?php
echo $selected;
?>
><?php
echo htmlspecialchars(__('* All Forms *', 'contact-form-7-to-database-extension'));
?>
</option>
</select>
<div id="form_validations_text" class="validation"></div>
</div>
<?php
}
public function getNewSubmitTime($submitTime)
{
global $wpdb;
$table = $this->plugin->getSubmitsTableName();
$inDBSql = 'select count(submit_time) from ' . $table . ' where submit_time = %F';
while (true) {
$submitTime = $submitTime + 0.0001;
// Propose new submit time
$inDbAlready = $wpdb->get_var($wpdb->prepare($inDBSql, $submitTime));
if (!$inDbAlready) {
break;
}
}
return $submitTime;
}
/**
* @param string|array $formName (if array, must be array of string)
* @param bool $count
* @param $submitTimes array of string submit_time values that are to be specifically queried
* @return string
*/
public function &getPivotQuery($formName, $count = false, $submitTimes = null)
{
global $wpdb;
$tableName = $this->plugin->getSubmitsTableName();
$formNameClause = '1=1';
if (is_array($formName)) {
$formNameArray = $this->escapeAndQuoteArrayValues($formName);
$formNameClause = '`form_name` in ( ' . implode(', ', $formNameArray) . ' )';
} else {
if ($formName !== null && $formName != '*') {
// * => all forms
if (strpos($formName, ',') !== false) {
$formNameArray = explode(',', $formName);
$formNameArray[] = $formName;
// in case the form name is literally the string with commas in it
$formNameArray = $this->escapeAndQuoteArrayValues($formNameArray);
$formNameClause = '`form_name` in ( ' . implode(', ', $formNameArray) . ' )';
} else {
$formNameClause = "`form_name` = '" . $this->escapeString($formName) . "'";
}
}
}
$submitTimesClause = '';
if (is_array($submitTimes) && !empty($submitTimes)) {
$submitTimesClause = 'AND submit_time in ( ' . implode(', ', $submitTimes) . ' )';
}
//$rows = $wpdb->get_results("SELECT DISTINCT `field_name`, `field_order` FROM `$tableName` WHERE $formNameClause ORDER BY field_order"); // Pagination bug
$rows = $wpdb->get_results("SELECT DISTINCT `field_name` FROM `{$tableName}` WHERE {$formNameClause} ORDER BY field_order");
$fields = array();
foreach ($rows as $aRow) {
if ($aRow->field_name && trim($aRow->field_name) != '') {
// Saw a case of a column name of '' and ' ' which caused query to fail
// and no date to be displayed.
$fields[] = $aRow->field_name;
}
}
$sql = '';
if ($count) {
$sql .= 'SELECT count(*) as count FROM (';
}
$sql .= "SELECT `submit_time` AS 'Submitted'";
foreach ($fields as $aCol) {
// Escape single quotes in column name
$aCol = $this->escapeString($aCol);
$sql .= ",\n max(if(`field_name`='{$aCol}', `field_value`, null )) AS '{$aCol}'";
}
if (!$count) {
$sql .= ",\n GROUP_CONCAT(if(`file` is null or length(`file`) = 0, null, `field_name`)) AS 'fields_with_file'";
}
$sql .= "\nFROM `{$tableName}` \nWHERE {$formNameClause} {$submitTimesClause} \nGROUP BY `submit_time` ";
if ($count) {
$sql .= ') form';
} else {
$orderBys = array();
if ($this->options && isset($this->options['orderby'])) {
$orderByStrings = explode(',', $this->options['orderby']);
foreach ($orderByStrings as $anOrderBy) {
$anOrderBy = trim($anOrderBy);
$ascOrDesc = null;
list($ascOrDesc, $anOrderBy) = $this->parseOrderBy($anOrderBy);
if (in_array($anOrderBy, $fields) || $anOrderBy == 'submit_time') {
$orderBys[] = '`' . $anOrderBy . '`' . $ascOrDesc;
} else {
// Want to add a different collation as a different sorting mechanism
// Actually doesn't work because MySQL does not allow COLLATE on a select that is a group function
$collateIdx = stripos($anOrderBy, ' COLLATE');
if ($collateIdx > 0) {
$collatedField = substr($anOrderBy, 0, $collateIdx);
if (in_array($collatedField, $fields)) {
$orderBys[] = '`' . $collatedField . '`' . substr($anOrderBy, $collateIdx) . $ascOrDesc;
}
}
}
}
}
if (empty($orderBys)) {
$sql .= "\nORDER BY `submit_time` DESC";
} else {
$sql .= "\nORDER BY ";
$first = true;
foreach ($orderBys as $anOrderBy) {
if ($first) {
$sql .= $anOrderBy;
$first = false;
} else {
$sql .= ', ' . $anOrderBy;
}
}
}
if (!$this->hasFilterOrTransform() && $this->options && isset($this->options['limit'])) {
// If no filter constraints and have a limit, add limit to the SQL
$sql .= "\nLIMIT " . $this->options['limit'];
}
}
//echo $sql; // debug
return $sql;
}
/**
* @param string|array $formName (if array, must be array of string)
* @param bool $count
* @param $submitTimes array of string submit_time values that are to be specifically queried
* @return string
*/
public function &getPivotQuery($formName, $count = false, $submitTimes = null)
{
global $wpdb;
$tableName = $this->plugin->getSubmitsTableName();
$formNameClause = '';
if (is_array($formName)) {
$formNameClause = '`form_name` in ( \'' . implode('\', \'', $formName) . '\' )';
} else {
if ($formName !== null) {
$formNameClause = "`form_name` = '{$formName}'";
}
}
$submitTimesClause = '';
if (is_array($submitTimes) && !empty($submitTimes)) {
$submitTimesClause = 'AND submit_time in ( ' . implode(', ', $submitTimes) . ' )';
}
//$rows = $wpdb->get_results("SELECT DISTINCT `field_name`, `field_order` FROM `$tableName` WHERE $formNameClause ORDER BY field_order"); // Pagination bug
$rows = $wpdb->get_results("SELECT DISTINCT `field_name` FROM `{$tableName}` WHERE {$formNameClause} ORDER BY field_order");
$fields = array();
foreach ($rows as $aRow) {
$fields[] = $aRow->field_name;
}
$sql = '';
if ($count) {
$sql .= 'SELECT count(*) as count FROM (';
}
$sql .= "SELECT `submit_time` AS 'Submitted'";
foreach ($fields as $aCol) {
$sql .= ",\n max(if(`field_name`='{$aCol}', `field_value`, null )) AS '{$aCol}'";
}
if (!$count) {
$sql .= ",\n GROUP_CONCAT(if(`file` is null or length(`file`) = 0, null, `field_name`)) AS 'fields_with_file'";
}
$sql .= "\nFROM `{$tableName}` \nWHERE {$formNameClause} {$submitTimesClause} \nGROUP BY `submit_time` ";
if ($count) {
$sql .= ') form';
} else {
$orderBys = array();
if ($this->options && isset($this->options['orderby'])) {
$orderByStrings = explode(',', $this->options['orderby']);
foreach ($orderByStrings as $anOrderBy) {
$anOrderBy = trim($anOrderBy);
$ascOrDesc = null;
if (strtoupper(substr($anOrderBy, -5)) == ' DESC') {
$ascOrDesc = " DESC";
$anOrderBy = trim(substr($anOrderBy, 0, -5));
} else {
if (strtoupper(substr($anOrderBy, -4)) == ' ASC') {
$ascOrDesc = " ASC";
$anOrderBy = trim(substr($anOrderBy, 0, -4));
}
}
if ($anOrderBy == 'Submitted') {
$anOrderBy = 'submit_time';
}
if (in_array($anOrderBy, $fields) || $anOrderBy == 'submit_time') {
$orderBys[] = '`' . $anOrderBy . '`' . $ascOrDesc;
} else {
// Want to add a different collation as a different sorting mechanism
// Actually doesn't work because MySQL does not allow COLLATE on a select that is a group function
$collateIdx = stripos($anOrderBy, ' COLLATE');
if ($collateIdx > 0) {
$collatedField = substr($anOrderBy, 0, $collateIdx);
if (in_array($collatedField, $fields)) {
$orderBys[] = '`' . $collatedField . '`' . substr($anOrderBy, $collateIdx) . $ascOrDesc;
}
}
}
}
}
if (empty($orderBys)) {
$sql .= "\nORDER BY `submit_time` DESC";
} else {
$sql .= "\nORDER BY ";
$first = true;
foreach ($orderBys as $anOrderBy) {
if ($first) {
$sql .= $anOrderBy;
$first = false;
} else {
$sql .= ', ' . $anOrderBy;
}
}
}
if (empty($this->rowFilter) && $this->options && isset($this->options['limit'])) {
// If no filter constraints and have a limit, add limit to the SQL
$sql .= "\nLIMIT " . $this->options['limit'];
}
}
//echo $sql; // debug
return $sql;
}
public function ajaxRenameForm()
{
header("Pragma: no-cache");
header("Expires: Thu, 01 Jan 1970 00:00:00 GMT");
$cfdb = new CF7DBPlugin();
if (!$cfdb->canUserDoRoleOption('CanChangeSubmitData')) {
die(1);
}
if (!isset($_REQUEST['form']) || !$_REQUEST['form']) {
echo 'No form name set';
die(1);
}
if (!isset($_REQUEST['newformname']) || !$_REQUEST['newformname']) {
echo 'No new form name set';
die(1);
}
global $wpdb;
$tableName = $cfdb->getSubmitsTableName();
$parametrizedQuery = "UPDATE `{$tableName}` SET `form_name` = %s WHERE `form_name` = %s";
$result = $wpdb->query($wpdb->prepare($parametrizedQuery, $_REQUEST['newformname'], $_REQUEST['form']));
if ($result == false) {
echo 'Failed to update';
} else {
$url = admin_url('admin.php') . '?page=CF7DBPluginSubmissions&form_name=' . $_REQUEST['newformname'];
printf('Form "%s" renamed to <a href="%s">"%s"</a>.', $_REQUEST['form'], $url, $_REQUEST['newformname']);
$backUrl = admin_url('admin.php') . '?page=CF7DBPluginImport';
printf('<br/><a href="%s">%s</a>', $backUrl, 'Back');
}
die;
}
