-- MySQL dump 10.13 Distrib 5.6.24, for Win64 (x86_64) -- -- Host: 107.180.51.252 Database: PowerStructureValidation -- ------------------------------------------------------ -- Server version 5.6.36-cll-lve /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Current Database: `PowerStructureValidation` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `PowerStructureValidation` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `PowerStructureValidation`; -- -- Temporary view structure for view `Duplicated Contacts To Be Added` -- DROP TABLE IF EXISTS `Duplicated Contacts To Be Added`; /*!50001 DROP VIEW IF EXISTS `Duplicated Contacts To Be Added`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE VIEW `Duplicated Contacts To Be Added` AS SELECT 1 AS `UserID`, 1 AS `DemoDownloaderID`, 1 AS `RegistrationDate`, 1 AS `LastModificationDate`, 1 AS `Salutation`, 1 AS `FirstName`, 1 AS `LastName`, 1 AS `Company`, 1 AS `Address`, 1 AS `Address_2`, 1 AS `City`, 1 AS `State`, 1 AS `PostalCode`, 1 AS `Email`, 1 AS `CommunitySiteHandle`, 1 AS `PhoneNumber`, 1 AS `FaxNumber`, 1 AS `SkypeID`, 1 AS `Country`, 1 AS `EnglishProficiency`, 1 AS `PrimaryProfession`, 1 AS `PositionInIndustry`, 1 AS `HowHeardAboutFrameForge`, 1 AS `HowHeardDetails`, 1 AS `IPAddress`, 1 AS `CanSendEmails`, 1 AS `DemoDownloadDate`, 1 AS `SerialNumber`, 1 AS `Duplicates`*/; SET character_set_client = @saved_cs_client; -- -- Temporary view structure for view `Duplicated Contacts To Be Kept` -- DROP TABLE IF EXISTS `Duplicated Contacts To Be Kept`; /*!50001 DROP VIEW IF EXISTS `Duplicated Contacts To Be Kept`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE VIEW `Duplicated Contacts To Be Kept` AS SELECT 1 AS `UserID`, 1 AS `DemoDownloaderID`, 1 AS `RegistrationDate`, 1 AS `LastModificationDate`, 1 AS `Salutation`, 1 AS `FirstName`, 1 AS `LastName`, 1 AS `Company`, 1 AS `Address`, 1 AS `Address_2`, 1 AS `City`, 1 AS `State`, 1 AS `PostalCode`, 1 AS `Email`, 1 AS `CommunitySiteHandle`, 1 AS `PhoneNumber`, 1 AS `FaxNumber`, 1 AS `SkypeID`, 1 AS `Country`, 1 AS `EnglishProficiency`, 1 AS `PrimaryProfession`, 1 AS `PositionInIndustry`, 1 AS `HowHeardAboutFrameForge`, 1 AS `HowHeardDetails`, 1 AS `IPAddress`, 1 AS `CanSendEmails`, 1 AS `DemoDownloadDate`, 1 AS `SerialNumber`, 1 AS `Duplicates`*/; SET character_set_client = @saved_cs_client; -- -- Table structure for table `GeneratedSerialNumbers` -- DROP TABLE IF EXISTS `GeneratedSerialNumbers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `GeneratedSerialNumbers` ( `SerialNumberID` int(11) NOT NULL DEFAULT '0', `SerialCreatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `AssignedTo` int(11) NOT NULL DEFAULT '0', `DeliveredToCompany` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `SerialNumber` varchar(35) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `ValidatingUpgradeSSN` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `ValidatingEDUProof` int(11) NOT NULL, `OperatingSystem` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `InstallationsLeft` int(11) NOT NULL DEFAULT '3', `RecordStatus` int(11) NOT NULL DEFAULT '0', `ExpirationDays` int(11) DEFAULT NULL, `RegistrationDate` date DEFAULT NULL, `LastModificationDate` date DEFAULT NULL, `ProFeaturesTrialStarted` date DEFAULT NULL, `S3DFeaturesTrialStarted` date DEFAULT NULL, `VRFeaturesTrialStarted` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `RegisteredUsers` -- DROP TABLE IF EXISTS `RegisteredUsers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `RegisteredUsers` ( `UserID` int(11) NOT NULL DEFAULT '0', `DemoDownloaderID` int(11) NOT NULL, `RegistrationDate` date DEFAULT NULL, `LastModificationDate` date DEFAULT NULL, `Salutation` varchar(25) CHARACTER SET utf8 DEFAULT NULL, `FirstName` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `LastName` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `Company` varchar(65) CHARACTER SET utf8 DEFAULT NULL, `Address` varchar(80) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Address_2` varchar(80) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `City` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `State` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `PostalCode` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Email` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `CommunitySiteHandle` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `PhoneNumber` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `FaxNumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `SkypeID` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `Country` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `EnglishProficiency` varchar(25) CHARACTER SET utf8 DEFAULT NULL, `PrimaryProfession` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `PositionInIndustry` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `HowHeardAboutFrameForge` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `HowHeardDetails` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `IPAddress` varchar(12) CHARACTER SET utf8 DEFAULT NULL, `CanSendEmails` tinyint(1) DEFAULT NULL, `DemoDownloadDate` date DEFAULT NULL, `SerialNumber` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Duplicates` int(11) NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping routines for database 'PowerStructureValidation' -- /*!50003 DROP FUNCTION IF EXISTS `CountDuplicates` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`DatabaseGod`@`24.94.31.128` FUNCTION `CountDuplicates`(ForEmail varchar(150), ForFirstName varchar(75), ForLastName varchar(75)) RETURNS int(11) BEGIN declare ReturnVal INT; select Count(email) into ReturnVal FROM RegisteredUsers WHERE (Email=ForEmail) AND (FirstName=ForFirstName) and (LastName=ForLastName); RETURN ReturnVal; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP FUNCTION IF EXISTS `COUNTDUPLICATESEx` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`DatabaseGod`@`24.94.31.128` FUNCTION `COUNTDUPLICATESEx`(ForEmail varchar(150), ForFirstName varchar(75), ForLastName varchar(75), ForSerialNumber varchar(75)) RETURNS int(11) BEGIN declare ReturnVal INT; select Count(email) into ReturnVal FROM RegisteredUsers WHERE (Email=ForEmail) AND (FirstName=ForFirstName) and (LastName=ForLastName) And (SerialNumber=ForSerialNumber); RETURN ReturnVal; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP FUNCTION IF EXISTS `getSerialUserData` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`DatabaseGod`@`24.94.31.128` FUNCTION `getSerialUserData`(inSerial VARCHAR(35)) RETURNS varchar(250) CHARSET latin1 BEGIN DECLARE RetVal varchar(250); select CONCAT(FirstName,LastName,Email) into RetVal from RegisteredUsers where UserID = (SELECT AssignedTo From GeneratedSerialNumbers WHERE SerialNumber = inSerial); RETURN RetVal; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Current Database: `FrameForgeUsersAndSerials` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `FrameForgeUsersAndSerials` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `FrameForgeUsersAndSerials`; -- -- Temporary view structure for view `AssignedSerialNumber` -- DROP TABLE IF EXISTS `AssignedSerialNumber`; /*!50001 DROP VIEW IF EXISTS `AssignedSerialNumber`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE VIEW `AssignedSerialNumber` AS SELECT 1 AS `SerialNumber`, 1 AS `UserID`, 1 AS `RegistrationDate`, 1 AS `DeliveredToCompany`, 1 AS `FirstName`, 1 AS `LastName`, 1 AS `Company`, 1 AS `Email`, 1 AS `PhoneNumber`, 1 AS `LastModificationDate`, 1 AS `RecordStatus`, 1 AS `InstallationsLeft`, 1 AS `ExpirationDays`*/; SET character_set_client = @saved_cs_client; -- -- Table structure for table `EDUPudding` -- DROP TABLE IF EXISTS `EDUPudding`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `EDUPudding` ( `EDUProofID` int(11) NOT NULL AUTO_INCREMENT, `ForUserID` int(11) NOT NULL, `FileName` varchar(50) NOT NULL, `FileSize` int(11) NOT NULL, `TheProof` blob NOT NULL, PRIMARY KEY (`EDUProofID`) ) ENGINE=MyISAM AUTO_INCREMENT=137 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `GeneratedSerialNumbers` -- DROP TABLE IF EXISTS `GeneratedSerialNumbers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `GeneratedSerialNumbers` ( `SerialNumberID` int(11) NOT NULL AUTO_INCREMENT, `SerialCreatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `AssignedTo` int(11) NOT NULL DEFAULT '0', `DeliveredToCompany` varchar(15) COLLATE utf8_bin NOT NULL, `SerialNumber` varchar(35) COLLATE utf8_bin NOT NULL, `ValidatingUpgradeSSN` varchar(50) COLLATE utf8_bin DEFAULT NULL, `ValidatingEDUProof` int(11) NOT NULL, `OperatingSystem` varchar(50) COLLATE utf8_bin DEFAULT NULL, `InstallationsLeft` int(11) NOT NULL DEFAULT '3', `RecordStatus` int(11) NOT NULL DEFAULT '0', `ExpirationDays` int(11) DEFAULT NULL, `RegistrationDate` date DEFAULT NULL, `LastModificationDate` date DEFAULT NULL, `ProFeaturesTrialStarted` date DEFAULT NULL, `S3DFeaturesTrialStarted` date DEFAULT NULL, `VRFeaturesTrialStarted` date DEFAULT NULL, PRIMARY KEY (`SerialNumberID`), UNIQUE KEY `SerialNumberID` (`SerialNumberID`), UNIQUE KEY `SerialNumberText` (`SerialNumber`), UNIQUE KEY `SerialNumberUserIDs` (`SerialNumberID`,`AssignedTo`), KEY `SerialNumber_2` (`SerialNumber`), KEY `AssignedTo` (`AssignedTo`) ) ENGINE=MyISAM AUTO_INCREMENT=104254 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary view structure for view `GeneratedSerialNumbersWithUsers` -- DROP TABLE IF EXISTS `GeneratedSerialNumbersWithUsers`; /*!50001 DROP VIEW IF EXISTS `GeneratedSerialNumbersWithUsers`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE VIEW `GeneratedSerialNumbersWithUsers` AS SELECT 1 AS `SerialNumberID`, 1 AS `SerialCreatedDate`, 1 AS `AssignedTo`, 1 AS `DeliveredToCompany`, 1 AS `SerialNumber`, 1 AS `ValidatingUpgradeSSN`, 1 AS `ValidatingEDUProof`, 1 AS `OperatingSystem`, 1 AS `InstallationsLeft`, 1 AS `RecordStatus`, 1 AS `ExpirationDays`, 1 AS `RegistrationDate`, 1 AS `LastModificationDate`, 1 AS `ProFeaturesTrialStarted`, 1 AS `S3DFeaturesTrialStarted`, 1 AS `VRFeaturesTrialStarted`, 1 AS `FirstName`, 1 AS `LastName`, 1 AS `Email`*/; SET character_set_client = @saved_cs_client; -- -- Table structure for table `RegisteredUsers` -- DROP TABLE IF EXISTS `RegisteredUsers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `RegisteredUsers` ( `UserID` int(11) NOT NULL AUTO_INCREMENT, `DemoDownloaderID` int(11) NOT NULL, `RegistrationDate` date DEFAULT NULL, `LastModificationDate` date DEFAULT NULL, `Salutation` varchar(25) DEFAULT NULL, `FirstName` varchar(45) DEFAULT NULL, `LastName` varchar(45) DEFAULT NULL, `Company` varchar(65) DEFAULT NULL, `Address` varchar(80) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Address_2` varchar(80) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `City` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `State` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `PostalCode` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Email` varchar(45) DEFAULT NULL, `CommunitySiteHandle` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `PhoneNumber` varchar(45) DEFAULT NULL, `FaxNumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `SkypeID` varchar(45) DEFAULT NULL, `Country` varchar(45) DEFAULT NULL, `EnglishProficiency` varchar(25) DEFAULT NULL, `PrimaryProfession` varchar(45) DEFAULT NULL, `PositionInIndustry` varchar(45) DEFAULT NULL, `HowHeardAboutFrameForge` varchar(45) DEFAULT NULL, `HowHeardDetails` varchar(45) DEFAULT NULL, `IPAddress` varchar(12) DEFAULT NULL, `CanSendEmails` tinyint(1) DEFAULT NULL, `DemoDownloadDate` date DEFAULT NULL, `SerialNumber` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Duplicates` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`UserID`), KEY `ByLastName` (`LastName`,`FirstName`), KEY `ByFirstName` (`FirstName`,`LastName`), KEY `ByCompany` (`Company`) ) ENGINE=InnoDB AUTO_INCREMENT=45992 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary view structure for view `RegisteredV4Users` -- DROP TABLE IF EXISTS `RegisteredV4Users`; /*!50001 DROP VIEW IF EXISTS `RegisteredV4Users`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE VIEW `RegisteredV4Users` AS SELECT 1 AS `RegistrationDate`, 1 AS `FirstName`, 1 AS `LastName`, 1 AS `Email`, 1 AS `SerialNumber`, 1 AS `ValidatingUpgradeSSN`, 1 AS `OperatingSystem`, 1 AS `RecordStatus`, 1 AS `LastModificationDate`*/; SET character_set_client = @saved_cs_client; -- -- Temporary view structure for view `ReportView` -- DROP TABLE IF EXISTS `ReportView`; /*!50001 DROP VIEW IF EXISTS `ReportView`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE VIEW `ReportView` AS SELECT 1 AS `Edition`, 1 AS `SubType`, 1 AS `RegistrationDate`, 1 AS `DeliveredtoCompany`, 1 AS `FirstName`, 1 AS `LastName`, 1 AS `Company`, 1 AS `Email`, 1 AS `PhoneNumber`, 1 AS `LastModificationDate`, 1 AS `Status`*/; SET character_set_client = @saved_cs_client; -- -- Table structure for table `Seeds` -- DROP TABLE IF EXISTS `Seeds`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `Seeds` ( `SeedID` int(11) NOT NULL AUTO_INCREMENT, `ProductID` int(11) DEFAULT NULL, `ProductVersion` decimal(6,0) DEFAULT '4', `SubType` int(11) DEFAULT NULL, `CurrentSeed` int(11) DEFAULT NULL, PRIMARY KEY (`SeedID`) ) ENGINE=MyISAM AUTO_INCREMENT=2621965 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `TestSerials` -- DROP TABLE IF EXISTS `TestSerials`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `TestSerials` ( `SerialNumberID` int(11) NOT NULL DEFAULT '0', `SerialCreatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `AssignedTo` int(11) NOT NULL DEFAULT '0', `DeliveredToCompany` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `SerialNumber` varchar(35) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `ValidatingUpgradeSSN` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `ValidatingEDUProof` int(11) NOT NULL, `OperatingSystem` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `InstallationsLeft` int(11) NOT NULL DEFAULT '3', `RecordStatus` int(11) NOT NULL DEFAULT '0', `ExpirationDays` int(11) DEFAULT NULL, `RegistrationDate` date DEFAULT NULL, `LastModificationDate` date DEFAULT NULL, `ProFeaturesTrialStarted` date DEFAULT NULL, `S3DFeaturesTrialStarted` date DEFAULT NULL, `VRFeaturesTrialStarted` date DEFAULT NULL, `GlobalUserID` int(11) NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `UniqueUsers` -- DROP TABLE IF EXISTS `UniqueUsers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `UniqueUsers` ( `UserID` int(11) NOT NULL DEFAULT '0', `DemoDownloaderID` int(11) NOT NULL, `RegistrationDate` date DEFAULT NULL, `LastModificationDate` date DEFAULT NULL, `Salutation` varchar(25) CHARACTER SET utf8 DEFAULT NULL, `FirstName` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `LastName` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `Company` varchar(65) CHARACTER SET utf8 DEFAULT NULL, `Address` varchar(80) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Address_2` varchar(80) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `City` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `State` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `PostalCode` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Email` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `CommunitySiteHandle` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `PhoneNumber` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `FaxNumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `SkypeID` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `Country` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `EnglishProficiency` varchar(25) CHARACTER SET utf8 DEFAULT NULL, `PrimaryProfession` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `PositionInIndustry` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `HowHeardAboutFrameForge` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `HowHeardDetails` varchar(45) CHARACTER SET utf8 DEFAULT NULL, `IPAddress` varchar(12) CHARACTER SET utf8 DEFAULT NULL, `CanSendEmails` tinyint(1) DEFAULT NULL, `DemoDownloadDate` date DEFAULT NULL, `SerialNumber` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Duplicates` int(11) NOT NULL DEFAULT '0', `DuplicateCount` bigint(21) NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `ValidCompanies` -- DROP TABLE IF EXISTS `ValidCompanies`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `ValidCompanies` ( `CompanyID` int(11) NOT NULL AUTO_INCREMENT, `CompanyName` text NOT NULL, `BuyNowURL` text NOT NULL, PRIMARY KEY (`CompanyID`) ) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping routines for database 'FrameForgeUsersAndSerials' -- /*!50003 DROP FUNCTION IF EXISTS `EmailFromUserID` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`DatabaseGod`@`24.94.31.128` FUNCTION `EmailFromUserID`(inUserID INT) RETURNS varchar(60) CHARSET latin1 BEGIN DECLARE returnVal varchar(60); SET returnVal = (SELECT Email FROM RegisteredUsers WHERE UserID=inUserID); RETURN returnVal; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Current Database: `PowerStructureValidation` -- USE `PowerStructureValidation`; -- -- Final view structure for view `Duplicated Contacts To Be Added` -- /*!50001 DROP VIEW IF EXISTS `Duplicated Contacts To Be Added`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8 */; /*!50001 SET character_set_results = utf8 */; /*!50001 SET collation_connection = utf8_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`DatabaseGod`@`24.94.31.128` SQL SECURITY DEFINER */ /*!50001 VIEW `Duplicated Contacts To Be Added` AS select `RegisteredUsers`.`UserID` AS `UserID`,`RegisteredUsers`.`DemoDownloaderID` AS `DemoDownloaderID`,`RegisteredUsers`.`RegistrationDate` AS `RegistrationDate`,`RegisteredUsers`.`LastModificationDate` AS `LastModificationDate`,`RegisteredUsers`.`Salutation` AS `Salutation`,`RegisteredUsers`.`FirstName` AS `FirstName`,`RegisteredUsers`.`LastName` AS `LastName`,`RegisteredUsers`.`Company` AS `Company`,`RegisteredUsers`.`Address` AS `Address`,`RegisteredUsers`.`Address_2` AS `Address_2`,`RegisteredUsers`.`City` AS `City`,`RegisteredUsers`.`State` AS `State`,`RegisteredUsers`.`PostalCode` AS `PostalCode`,`RegisteredUsers`.`Email` AS `Email`,`RegisteredUsers`.`CommunitySiteHandle` AS `CommunitySiteHandle`,`RegisteredUsers`.`PhoneNumber` AS `PhoneNumber`,`RegisteredUsers`.`FaxNumber` AS `FaxNumber`,`RegisteredUsers`.`SkypeID` AS `SkypeID`,`RegisteredUsers`.`Country` AS `Country`,`RegisteredUsers`.`EnglishProficiency` AS `EnglishProficiency`,`RegisteredUsers`.`PrimaryProfession` AS `PrimaryProfession`,`RegisteredUsers`.`PositionInIndustry` AS `PositionInIndustry`,`RegisteredUsers`.`HowHeardAboutFrameForge` AS `HowHeardAboutFrameForge`,`RegisteredUsers`.`HowHeardDetails` AS `HowHeardDetails`,`RegisteredUsers`.`IPAddress` AS `IPAddress`,`RegisteredUsers`.`CanSendEmails` AS `CanSendEmails`,`RegisteredUsers`.`DemoDownloadDate` AS `DemoDownloadDate`,`RegisteredUsers`.`SerialNumber` AS `SerialNumber`,`RegisteredUsers`.`Duplicates` AS `Duplicates` from `RegisteredUsers` where ((`RegisteredUsers`.`SerialNumber` is not null) and (`RegisteredUsers`.`SerialNumber` <> '') and (not(`RegisteredUsers`.`UserID` in (select `GeneratedSerialNumbers`.`AssignedTo` from `GeneratedSerialNumbers`)))) group by `RegisteredUsers`.`FirstName`,`RegisteredUsers`.`LastName`,`RegisteredUsers`.`Email`,`RegisteredUsers`.`SerialNumber` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `Duplicated Contacts To Be Kept` -- /*!50001 DROP VIEW IF EXISTS `Duplicated Contacts To Be Kept`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8 */; /*!50001 SET character_set_results = utf8 */; /*!50001 SET collation_connection = utf8_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`DatabaseGod`@`24.94.31.128` SQL SECURITY DEFINER */ /*!50001 VIEW `Duplicated Contacts To Be Kept` AS select `RegisteredUsers`.`UserID` AS `UserID`,`RegisteredUsers`.`DemoDownloaderID` AS `DemoDownloaderID`,`RegisteredUsers`.`RegistrationDate` AS `RegistrationDate`,`RegisteredUsers`.`LastModificationDate` AS `LastModificationDate`,`RegisteredUsers`.`Salutation` AS `Salutation`,`RegisteredUsers`.`FirstName` AS `FirstName`,`RegisteredUsers`.`LastName` AS `LastName`,`RegisteredUsers`.`Company` AS `Company`,`RegisteredUsers`.`Address` AS `Address`,`RegisteredUsers`.`Address_2` AS `Address_2`,`RegisteredUsers`.`City` AS `City`,`RegisteredUsers`.`State` AS `State`,`RegisteredUsers`.`PostalCode` AS `PostalCode`,`RegisteredUsers`.`Email` AS `Email`,`RegisteredUsers`.`CommunitySiteHandle` AS `CommunitySiteHandle`,`RegisteredUsers`.`PhoneNumber` AS `PhoneNumber`,`RegisteredUsers`.`FaxNumber` AS `FaxNumber`,`RegisteredUsers`.`SkypeID` AS `SkypeID`,`RegisteredUsers`.`Country` AS `Country`,`RegisteredUsers`.`EnglishProficiency` AS `EnglishProficiency`,`RegisteredUsers`.`PrimaryProfession` AS `PrimaryProfession`,`RegisteredUsers`.`PositionInIndustry` AS `PositionInIndustry`,`RegisteredUsers`.`HowHeardAboutFrameForge` AS `HowHeardAboutFrameForge`,`RegisteredUsers`.`HowHeardDetails` AS `HowHeardDetails`,`RegisteredUsers`.`IPAddress` AS `IPAddress`,`RegisteredUsers`.`CanSendEmails` AS `CanSendEmails`,`RegisteredUsers`.`DemoDownloadDate` AS `DemoDownloadDate`,`RegisteredUsers`.`SerialNumber` AS `SerialNumber`,`RegisteredUsers`.`Duplicates` AS `Duplicates` from `RegisteredUsers` where `RegisteredUsers`.`UserID` in (select `GeneratedSerialNumbers`.`AssignedTo` from `GeneratedSerialNumbers`) group by `RegisteredUsers`.`FirstName`,`RegisteredUsers`.`LastName`,`RegisteredUsers`.`Email`,`RegisteredUsers`.`SerialNumber` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Current Database: `FrameForgeUsersAndSerials` -- USE `FrameForgeUsersAndSerials`; -- -- Final view structure for view `AssignedSerialNumber` -- /*!50001 DROP VIEW IF EXISTS `AssignedSerialNumber`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8 */; /*!50001 SET character_set_results = utf8 */; /*!50001 SET collation_connection = utf8_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`DatabaseGod`@`24.94.31.128` SQL SECURITY DEFINER */ /*!50001 VIEW `AssignedSerialNumber` AS select `gs`.`SerialNumber` AS `SerialNumber`,`gs`.`AssignedTo` AS `UserID`,`gs`.`RegistrationDate` AS `RegistrationDate`,`gs`.`DeliveredToCompany` AS `DeliveredToCompany`,`ru`.`FirstName` AS `FirstName`,`ru`.`LastName` AS `LastName`,`ru`.`Company` AS `Company`,`ru`.`Email` AS `Email`,`ru`.`PhoneNumber` AS `PhoneNumber`,`gs`.`LastModificationDate` AS `LastModificationDate`,`gs`.`RecordStatus` AS `RecordStatus`,`gs`.`InstallationsLeft` AS `InstallationsLeft`,`gs`.`ExpirationDays` AS `ExpirationDays` from (`RegisteredUsers` `ru` join `GeneratedSerialNumbers` `gs`) where ((`gs`.`AssignedTo` <> 0) and (`ru`.`UserID` = `gs`.`AssignedTo`)) order by `gs`.`LastModificationDate` desc */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `GeneratedSerialNumbersWithUsers` -- /*!50001 DROP VIEW IF EXISTS `GeneratedSerialNumbersWithUsers`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = latin1 */; /*!50001 SET character_set_results = latin1 */; /*!50001 SET collation_connection = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`lordofthebytes`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `GeneratedSerialNumbersWithUsers` AS select `gs`.`SerialNumberID` AS `SerialNumberID`,`gs`.`SerialCreatedDate` AS `SerialCreatedDate`,`gs`.`AssignedTo` AS `AssignedTo`,`gs`.`DeliveredToCompany` AS `DeliveredToCompany`,`gs`.`SerialNumber` AS `SerialNumber`,`gs`.`ValidatingUpgradeSSN` AS `ValidatingUpgradeSSN`,`gs`.`ValidatingEDUProof` AS `ValidatingEDUProof`,`gs`.`OperatingSystem` AS `OperatingSystem`,`gs`.`InstallationsLeft` AS `InstallationsLeft`,`gs`.`RecordStatus` AS `RecordStatus`,`gs`.`ExpirationDays` AS `ExpirationDays`,`gs`.`RegistrationDate` AS `RegistrationDate`,`gs`.`LastModificationDate` AS `LastModificationDate`,`gs`.`ProFeaturesTrialStarted` AS `ProFeaturesTrialStarted`,`gs`.`S3DFeaturesTrialStarted` AS `S3DFeaturesTrialStarted`,`gs`.`VRFeaturesTrialStarted` AS `VRFeaturesTrialStarted`,`ru`.`FirstName` AS `FirstName`,`ru`.`LastName` AS `LastName`,`ru`.`Email` AS `Email` from (`RegisteredUsers` `ru` join `GeneratedSerialNumbers` `gs`) where ((`gs`.`AssignedTo` <> 0) and (`ru`.`UserID` = `gs`.`AssignedTo`)) order by `gs`.`LastModificationDate` desc */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `RegisteredV4Users` -- /*!50001 DROP VIEW IF EXISTS `RegisteredV4Users`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = latin1 */; /*!50001 SET character_set_results = latin1 */; /*!50001 SET collation_connection = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`lordofthebytes`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `RegisteredV4Users` AS select `gs`.`RegistrationDate` AS `RegistrationDate`,`ru`.`FirstName` AS `FirstName`,`ru`.`LastName` AS `LastName`,`ru`.`Email` AS `Email`,`gs`.`SerialNumber` AS `SerialNumber`,`gs`.`ValidatingUpgradeSSN` AS `ValidatingUpgradeSSN`,`gs`.`OperatingSystem` AS `OperatingSystem`,`gs`.`RecordStatus` AS `RecordStatus`,`gs`.`LastModificationDate` AS `LastModificationDate` from (`RegisteredUsers` `ru` join `GeneratedSerialNumbers` `gs`) where ((`gs`.`AssignedTo` <> 0) and (`ru`.`UserID` = `gs`.`AssignedTo`) and (`gs`.`SerialNumber` like 'A%') and isnull(`gs`.`ExpirationDays`) and (locate('@frameforge',lcase(`ru`.`Email`)) = 0)) order by `gs`.`LastModificationDate` desc */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `ReportView` -- /*!50001 DROP VIEW IF EXISTS `ReportView`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8 */; /*!50001 SET character_set_results = utf8 */; /*!50001 SET collation_connection = utf8_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`DatabaseGod`@`75.80.39.105` SQL SECURITY DEFINER */ /*!50001 VIEW `ReportView` AS select (case when (substr(`AssignedSerialNumber`.`SerialNumber`,3,1) in ('C','E','4','7','R','N','H')) then 'CORE' when (substr(`AssignedSerialNumber`.`SerialNumber`,3,1) in ('P','F','5','8','M','Q','J','W','B')) then 'PRO' when (substr(`AssignedSerialNumber`.`SerialNumber`,3,1) in ('X','D','S','G','6','9','A','K','L','Y','U','V')) then 'S3D' else concat('Unknown: ',substr(`AssignedSerialNumber`.`SerialNumber`,3,1)) end) AS `Edition`,(case when (substr(`AssignedSerialNumber`.`SerialNumber`,3,1) in ('C','P','S','R','N','A')) then 'Full' when (substr(`AssignedSerialNumber`.`SerialNumber`,3,1) in ('E','F','G','W','X','Y','H','J','K')) then 'EDU' when (substr(`AssignedSerialNumber`.`SerialNumber`,3,1) in ('9','7','8')) then 'NFR' when (substr(`AssignedSerialNumber`.`SerialNumber`,3,1) in ('R','M','A')) then 'Site' when (substr(`AssignedSerialNumber`.`SerialNumber`,3,1) in ('4','5','6')) then 'Site (EDU)' when (substr(`AssignedSerialNumber`.`SerialNumber`,3,1) in ('N','Q','U','W','X','B','D','Y','V','H','J','K')) then 'Upgrd' when (substr(`AssignedSerialNumber`.`SerialNumber`,3,1) = '2') then 'Demo' else concat('Unknown: ',substr(`AssignedSerialNumber`.`SerialNumber`,3,1)) end) AS `SubType`,`AssignedSerialNumber`.`RegistrationDate` AS `RegistrationDate`,`AssignedSerialNumber`.`DeliveredToCompany` AS `DeliveredtoCompany`,`AssignedSerialNumber`.`FirstName` AS `FirstName`,`AssignedSerialNumber`.`LastName` AS `LastName`,`AssignedSerialNumber`.`Company` AS `Company`,`AssignedSerialNumber`.`Email` AS `Email`,`AssignedSerialNumber`.`PhoneNumber` AS `PhoneNumber`,`AssignedSerialNumber`.`LastModificationDate` AS `LastModificationDate`,(case when (`AssignedSerialNumber`.`RecordStatus` = 0) then 'Valid' when (`AssignedSerialNumber`.`RecordStatus` = 2) then 'Need EDU Proof' when (`AssignedSerialNumber`.`RecordStatus` = 3) then 'Need Upgrade Proof' when (`AssignedSerialNumber`.`RecordStatus` = 5) then 'Need EDU/Upgrade Proof' when (`AssignedSerialNumber`.`RecordStatus` = 10) then 'Upgraded' when (`AssignedSerialNumber`.`RecordStatus` = -(10)) then 'Returned' else concat('Unknown: ',`AssignedSerialNumber`.`RecordStatus`) end) AS `Status` from `AssignedSerialNumber` where ((`AssignedSerialNumber`.`SerialNumber` like 'A%') and (locate('@frameforge',`AssignedSerialNumber`.`Email`) = 0) and (locate('test',`AssignedSerialNumber`.`DeliveredToCompany`) = 0)) order by `AssignedSerialNumber`.`RegistrationDate` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2018-01-04 18:51:25