<?php

/*
# -------------------------------------------------------
# Suppress errors when running this query as some installations (<= version 2.4) may already
# have the field welcomeemail_html added to the promotion table.
# -------------------------------------------------------
*/
$query = "ALTER TABLE `promotion` ADD `welcomeemail_html` TEXT NOT NULL AFTER `welcomeemail_text`";
$result = @mysql_query($query);


/*
# --------------------------------------------------------
# SHELL SERVER PLUGIN IS NOW NAMED SKELETON
# --------------------------------------------------------
*/

$query = "SELECT optionname FROM serverplugin_options WHERE optionname LIKE 'plugin_shell_%'";
$result = mysql_query($query) or die("ERROR in query: $query");
while ($row = mysql_fetch_array($result)) {
    $varName = substr($row['optionname'], 13);
    $varName = mysql_real_escape_string($varName);
    $query2 = "UPDATE serverplugin_options SET optionname='plugin_skeleton_$varName' WHERE optionname='{$row['optionname']}'";
    $result2 = mysql_query($query2);
}


/*
# --------------------------------------------------------
# Erase recurringfee entries for cancelled domains that belong to active customers,
# that weren't being erased before when the domain was cancelled because of a bug.
# --------------------------------------------------------
*/

$query = "SELECT r.id FROM domains d, recurringfee r WHERE d.id = r.appliestoid AND d.status =3";
$result = mysql_query($query) or die("ERROR in query: $query");
while ($row = mysql_fetch_array($result)) {
	$query2 = "DELETE FROM recurringfee WHERE id=".$row['id'];
	$result2 = mysql_query($query2) or die("ERROR in query: $query2");
}

/*
# --------------------------------------------------------
# Added possibility to create custom fields of type dropdown
# whose options labels don't have to be equal to its values, by using parenthesis.
# Existing parenthesis must be escaped
# --------------------------------------------------------
*/

updateCustomFieldsTables('customdomainfields', 'domain_customdomainfields');
updateCustomFieldsTables('customuserfields', 'user_customuserfields');

function updateCustomFieldsTables($tableName1, $tableName2)
{
    $query = "SELECT id, dropdownoptions FROM $tableName1 WHERE dropdownoptions != ''";
    $result = mysql_query($query) or die("ERROR in query: $query");
    while ($row = mysql_fetch_array($result)) {
        if (preg_match('/\((.+)\)/', $row['dropdownoptions'], $matches)) {
            $dropdownOptions = explode(',', $row['dropdownoptions']);
            for ($i = 0; $i < count($dropdownOptions); $i++) {
                $dropdownOptions[$i] = preg_replace('/(\()(.+)(\))/', '\\\($2\\\)', $dropdownOptions[$i]);

            }
            $dropdownOptions = implode(',', $dropdownOptions);
            if ($dropdownOptions != $row['dropdownoptions']) {
                $dropdownOptions = mysql_real_escape_string($dropdownOptions);
                $query = "UPDATE $tableName1 SET dropdownoptions = '$dropdownOptions' WHERE id={$row['id']}";
                mysql_query($query);

                $query2 = "SELECT userid, customid, value FROM $tableName2 WHERE customid={$row['id']}";
                $result2 = mysql_query($query2) or die("ERROR in query: $query2");
                while ($row2 = mysql_fetch_array($result2)) {
                    $newValue = preg_replace('/(\()(.+)(\))/', '\\\($2\\\)', $row2['value']);
                    if ($newValue != $row2['value']) {
                        $newValue = mysql_real_escape_string($newValue);
                        $query3 = "UPDATE $tableName2 SET value='$newValue' WHERE userid={$row2['userid']} AND customid={$row2['customid']}";
                        mysql_query($query3);
                    }
                }
            }
        }
    }
}

/*
# --------------------------------------------------------
# New HTML/plaintext widgets related upgrade.
# ASSUMES ALL PREVIOUS TEMPLATES ARE PLAINTEXT
# --------------------------------------------------------
*/

$tArr = array(
    // array(table,     textField,              htmlField)
    array('users',      'signature_text',       'signature_html'),
    array('promotion',  'welcomeemail_text',    'welcomeemail_html'),
    array('package',    'welcomeemail_text',    'welcomeemail_html'),
);
foreach ($tArr as $foo) {
    $query = "SELECT id, {$foo[1]} FROM {$foo[0]}";
    $result = mysql_query($query) or die("ERROR in query: $query");
    while ($row = mysql_fetch_array($result)) {
        if (trim($row[$foo[1]]) == '') {
            continue;
        }
        if(isHtml($row[$foo[1]])){
            $textField = _br2nl($row[$foo[1]]);
            $textField = unHtmlEntities($textField);
            $textField = mysql_real_escape_string($textField);
            
            $htmlField = $row[$foo[1]];
            $htmlField = mysql_real_escape_string($htmlField);
            
            $query2 = "UPDATE {$foo[0]} SET {$foo[1]}='$textField', {$foo[2]}='$htmlField' WHERE id=".$row['id'];
            mysql_query($query2) or die("ERROR in query: $query2");
        }else{
            $htmlField = nl2br($row[$foo[1]]);
            $htmlField = mysql_real_escape_string($htmlField);

            $query2 = "UPDATE {$foo[0]} SET {$foo[2]}='$htmlField' WHERE id=".$row['id'];
            mysql_query($query2) or die("ERROR in query: $query2");   
        }
    }
}

// Settings
$settings = array('Invoice Template', 'Payment Receipt Template', 'Overdue Invoice Template', 'Batch Invoice Notification Template', 'Expiring CC Template', 'Forgot Password Template', 'Get New Password Template', 'Trouble Ticket Template', 'New Trouble Ticket Autoresponder Template', 'Support Ticket Assigned Template');
foreach ($settings as $setting) {
    $query = "SELECT id, value FROM setting WHERE name='$setting'";
    $result = mysql_query($query) or die("ERROR in query: $query");
    while ($row = mysql_fetch_array($result)) {
        if(isHtml($row['value'])){
            $textField = _br2nl($row['value']);
            $textField = unHtmlEntities($textField);
            $textField = mysql_real_escape_string($textField);
            
            $htmlField = $row['value'];
            $htmlField = mysql_real_escape_string($htmlField);
            
            $query2 = "UPDATE setting SET value='$textField' , value_alternate='$htmlField' WHERE id=".$row['id'];
            mysql_query($query2) or die("ERROR in query: $query2");
        }else{
            $htmlField = nl2br($row['value']);
            $htmlField = mysql_real_escape_string($htmlField);

            $query2 = "UPDATE setting SET value_alternate='$htmlField' WHERE id=".$row['id'];
            mysql_query($query2) or die("ERROR in query: $query2");
        }
    }
}

// Terms and Conditions
$query = "SELECT value FROM setting WHERE name = 'Terms and Conditions'";
$result = mysql_query($query) or die("ERROR in query: $query");
$row = mysql_fetch_array($result);
$termsAndConditions = $row['value'];

if (isHTML($termsAndConditions)) {
    $textField = _br2nl($termsAndConditions);
    $textField = mysql_real_escape_string($textField);
    $query2 = "UPDATE setting SET value='$textField' WHERE name='Terms and Conditions'";
    mysql_query($query2) or die("ERROR in query: $query2");
    $termsAndConditions = mysql_real_escape_string($termsAndConditions);
    $query2 = "UPDATE setting SET value_alternate='$termsAndConditions' WHERE name='Terms and Conditions'";
    mysql_query($query2) or die("ERROR in query: $query2");
} else {
    $htmlField = nl2br($termsAndConditions);
    $htmlField = mysql_real_escape_string($htmlField);
    $query2 = "UPDATE setting SET value_alternate='$htmlField' WHERE name='Terms and Conditions'";
    mysql_query($query2) or die("ERROR in query: $query2");
}

$query = "DELETE FROM `setting` WHERE name='Terms and Conditions Format'";
mysql_query($query) or die("ERROR in query: $query");

/**
* Taken from comments on the nl2br function PHP's online manual.
* "Since nl2br doesn't remove the line breaks when adding in the <br /> tags, it is necessary to strip those off before you convert all of the tags, otherwise you will get double spacing"
*/
function _br2nl($str)
{
    $str = preg_replace("/(\r\n|\n|\r)/", "", $str);
    return preg_replace('=<br */?>=i', "\n", $str);
}

function isHtml($str)
{
    $str2 = strip_tags($str);
    return ($str != $str2);
}

function unHtmlEntities($str)
{
    // replace numeric entities
    $str = preg_replace('~&#x([0-9a-f]+);~ei', 'chr(hexdec("\\1"))', $str);
    $str = preg_replace('~&#([0-9]+);~e', 'chr("\\1")', $str);

    // replace literal entities
    if(defined('HTML_ENTITIES')){
        $trans_tbl = get_html_translation_table(HTML_ENTITIES);
    }else{
        $trans_tbl = get_html_translation_table();
    }
    $trans_tbl = array_flip($trans_tbl);
    $str = strtr($str, $trans_tbl);

    return strip_tags($str);
}

?>
