// 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