// Let's remove BLOB and BINARY for now...
//if ((strpos(strtolower($field_type), 'char') !== false) || (strpos(strtolower($field_type), 'text') !== false) || (strpos(strtolower($field_type), 'blob') !== false) || (strpos(strtolower($field_type), 'binary') !== false))
if ((strpos(strtolower($field_type), 'char') !== false) || (strpos(strtolower($field_type), 'text') !== false))
{
//$sql_fields = "ALTER TABLE {$db->sql_escape($table)} CHANGE " . $db->sql_escape($field_name) . " " . $db->sql_escape($field_name) . " " . $db->sql_escape($field_type) . " CHARACTER SET utf8 COLLATE utf8_bin";
$sql_fields = "ALTER TABLE {$db->sql_escape($table)} CHANGE " . $db->sql_escape($field_name) . " " . $db->sql_escape($field_name) . " " . $db->sql_escape($field_type) . " CHARACTER SET utf8 COLLATE utf8_bin " . (($field_null != 'YES') ? "NOT " : "") . "NULL DEFAULT " . (($field_default != 'None') ? ((!empty($field_default) || !is_null($field_default)) ? (is_string($field_default) ? ("'" . $db->sql_escape($field_default) . "'") : $field_default) : (($field_null != 'YES') ? "''" : "NULL")) : "''");
$db->sql_query($sql_fields);
echo("\t » Field <b style=\"color: #4488aa;\">$field_name</b> (in table <b style=\"color: #009900;\">$table</b>) converted to UTF-8<br />\n");
}
}
echo("<br />\n");
flush();
}
}
$db->sql_close();
echo("<br />\n<br />\n<br />\n<b style=\"color: #dd2222;\">Work Complete!!!</b><br />\n");
// HTML FOOTER - BEGIN
echo("</div>\n");
echo("</body>\n");
echo("</html>\n");
// HTML FOOTER - BEGIN
flush();
exit;
?>
function UpdateAddConEmail( $oldemail='', $newemail='', $contactid, $addconid = 0 )
{
global $db;
global $lang;
global $dbname;
global $dbhost, $supportdb, $dbuser, $dbpasswd;
$dbsupport = new sql_db( $supportdb, $dbuser, $dbpasswd, 'nizex_support', false, true );
$newemail = trim( $newemail );
$oldemail = trim( $oldemail );
/*****************
//after talking with GHH I am changing this function in the following way
//first check to see if there is a userid on the addcon we are editing, if not then
//we allow them to add, edit or clear, we don't care what they do
//if they have a userid then we make sure that who they are changing the email adress to
//is not in the support tables under a different userid, if so then we error and exit;
//***************/
$query = "select UserID from conAdditionalContacts where UserID > 0 and
EmailAddress = '".$oldemail."' and AdditionalContactID = ".$addconid;
if ( !$result = $db->sql_query( $query ) )
{
LogError( 9255, $query ."<br>".$db->sql_error(), false );
return false;
}
if ( $db->sql_numrows( $result ) == 0 )
{
//first make sure they are not changing the email address to one that already exists on the same contact
$query = "select EmailAddress from conAdditionalContacts where EmailAddress = '".$newemail."' and
ContactID = ".$contactid;
if ( !$result = $db->sql_query( $query ) )
{
LogError( 9141, $query .$dblang[ "ErrorInSQL" ]."<br>".$db->sql_error() );
return false;
}
if ( $db->sql_numrows( $result ) == 0 )
{
$query = "update conAdditionalContacts set EmailAddress = '".$newemail."' where AdditionalContactID = ".$addconid;
if ( !$result = $db->sql_query( $query ) )
{
LogError( 9142, $query .$dblang[ "ErrorInSQL" ]."<br>".$db->sql_error() );
return false;
}
}
}//end of editing for addcon with no userid
else
{
//TKS 06.12.2012 noticed if trying to save an add con without an email on a user
//they no longer set to login, it would enter here becuase of the userid then error
//due to invalid email. So if the email was blank before and blank now, we just return
if ( $oldemail == '' && $newemail == '' )
return true;
//if the current email is hooked to a userid they cannot clear out the email address
//TKS 01.25.2012 I moved this to the top of this else so that if they have a userid
//and trying to clear out the email, we just return
if ( !validEmail( $newemail ) || empty( $newemail ) || $newemail == '' )
{
LogError( $lang[ "ErrEmailValid" ], false );
return false;
}
$temprow = $db->sql_fetchrow( $result );
$userid = $temprow['UserID'];
//I spoke with Noel and he said to add Training DB and Demo and exit if they are editing a record with
//a userid in either of those DBs because we have a bunch of customers and employees editing and messing with data
// and it screwed up people's login ability. SO these 2 dbs, if oldemail has userid, they cannot edit it, they
//will have to login to their individual DB to edit the record
//05.14.2013 naj - added trial databases to the list of databases you cannot edit from.
if ( ( $dbname == 'nizex_training' || $dbname == 'nizex_demo' || preg_match('/nizex_trial/', $dbname)) && ( $newemail != $oldemail ) )
{
LogError( $lang[ "ErrDBEmail" ], false );
return false;
}
//first see if the email addresses are different
if ( $newemail != $oldemail )
{
//first check to see if the new address is in the optUsers table, under a different userid if so we return false
$query = "select UserID from optUsers where EmailAddress = '$newemail' and UserID != $userid";
if ( !$result = $dbsupport->sql_query( $query ) )
{
LogError( 9136, $query ."<br>".$dbsupport->sql_error(), false );
return false;
}
if ( $dbsupport->sql_numrows( $result ) > 0 )
{
LogError( $lang[ "ErrEmailExists" ], false );
return false;
}
//grab all company DBs linked to this user
$query = "select optUserCompany.DBName, DBHost from optUserCompany, optUserLinks where optUserLinks.UserID = $userid
and optUserLinks.CompanyID = optUserCompany.CompanyID";
if ( !$result = $dbsupport->sql_query( $query ) )
{
LogError( 9138, $query ."<br>".$dbsupport->sql_error(), false );
return false;
}
//04.09.2013 naj - changed everything to use a transaction, so we can roll this back if it fails
$dbarray = array();
while ( $dbrow = $dbsupport->sql_fetchrow( $result ) )
{
$tempdb = new sql_db( $dbrow[ "DBHost" ], $dbuser, $dbpasswd, $dbrow[ "DBName" ], false, true );
if ( $tempdb->db_connect_id )
{
//04.09.2013 naj - add the current database to the db array.
$dbarray[] = $tempdb;
if ( !$tempresult = $tempdb->sql_query( '', 'BEGIN'))
{
LogError (12118, $query."<br>".$tempdb->sql_error());
foreach ($dbarray as $tempdb)
{
$tempdb->sql_query('', 'ROLLBACK');
$tempdb->sql_close();
}
return false;
}
$query = "update conAdditionalContacts set EmailAddress = '".$newemail."' where EmailAddress = '".$oldemail."' and UserID = $userid";
if ( !$tempresult = $tempdb->sql_query( $query ) )
{
LogError( 9139, $query."<br>".$tempdb->sql_error() );
foreach ($dbarray as $tempdb)
{
$tempdb->sql_query('', 'ROLLBACK');
$tempdb->sql_close();
}
return false;
}
}
}
//now update the optUsers record
$query = "update optUsers set EmailAddress = '".$newemail."' where UserID = $userid";
if ( !$result = $dbsupport->sql_query( $query ) )
{
LogError( 9140, $query ."<br>".$dbsupport->sql_error(), false );
foreach ($dbarray as $tempdb)
{
$tempdb->sql_query('', 'ROLLBACK');
$tempdb->sql_close();
}
return false;
}
//04.09.2013 naj - if we made it this far, then the update is complete.
foreach ($dbarray as $tempdb)
{
$tempdb->sql_query('', 'COMMIT');
$tempdb->sql_close();
}
//12.09.2013 naj - this is to ensure that the current database gets updated to in the event that the user was allowed to login in the past but now is not.
$query = "update conAdditionalContacts set EmailAddress = '".$newemail."' where EmailAddress = '".$oldemail."' and UserID = $userid";
if ( !$result = $db->sql_query( $query ) )
{
LogError(13858, $query ."<br>".$db->sql_error(), false );
return false;
}
}
}
return true;
}//end of UpdateAddConEmail
