-- MySQL dump 10.10 -- -- Host: localhost Database: db12785 -- ------------------------------------------------------ -- Server version 5.0.10a-beta-nt-log /*!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 latin1 */; /*!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 */; -- -- Table structure for table `productscategories` -- create database db12785; use db12785; -- -- Table structure for table `tblclientlocations` -- DROP TABLE IF EXISTS `tblclientlocations`; CREATE TABLE `tblclientlocations` ( `pkClientLocationID` int(10) NOT NULL auto_increment, PRIMARY KEY (`pkClientLocationID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `tblclientlocations` -- /*!40000 ALTER TABLE `tblclientlocations` DISABLE KEYS */; LOCK TABLES `tblclientlocations` WRITE; INSERT INTO `tblclientlocations` VALUES (100),(139),(224),(231),(264),(270),(279),(281),(440),(505),(748),(761),(784),(878),(941),(1056),(1100),(1102),(1109),(3013),(3049); UNLOCK TABLES; /*!40000 ALTER TABLE `tblclientlocations` ENABLE KEYS */; -- -- Table structure for table `tblopportunities` -- DROP TABLE IF EXISTS `tblopportunities`; CREATE TABLE `tblopportunities` ( `pkOpportunityID` int(10) NOT NULL auto_increment, `DateCreated` datetime default NULL, `ProjectName` varchar(100) default NULL, `Description` longtext, `fkClientID` int(10) default NULL, `fkClientLocationID` int(10) default NULL, `fkContactID` int(10) default NULL, `TypeOfSolicitation` smallint(5) default NULL, `SolicitationNumber` varchar(50) default NULL, `WhenDidWeHear` varchar(255) default NULL, `ProposalDueDate` varchar(50) default NULL, `HowDidWeHearAboutJob` varchar(255) default NULL, `RespondBy` varchar(255) default NULL, `TotalBudget` int(10) unsigned default NULL, `NNBudget` decimal(19,0) default NULL, `OpportunityDisposition` smallint(5) default NULL, `DateOpportunityDispositionChanged` datetime default NULL, `Comments` longtext, `ProjectNumber` varchar(255) default NULL, `fkOppPropManagerID` int(10) default NULL, `FiscalSponsor` varchar(100) default NULL, `NNCost` decimal(19,0) default NULL, PRIMARY KEY (`pkOpportunityID`), KEY `ProjectNumber` (`ProjectNumber`), KEY `fkClientLocationID` (`fkClientLocationID`), CONSTRAINT `FK_tblopportunities_1` FOREIGN KEY (`fkClientLocationID`) REFERENCES `tblclientlocations` (`pkClientLocationID`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `tblopportunities` -- /*!40000 ALTER TABLE `tblopportunities` DISABLE KEYS */; LOCK TABLES `tblopportunities` WRITE; INSERT INTO `tblopportunities` VALUES (1,'0000-00-00 00:00:00','Contra Costa Visioning','',349,3013,1196,0,'','1/22/2001','','call from prime.','',0,'0',5,'0000-00-00 00:00:00','Lost - No Interview','01013',28,'','0'),(2,'0000-00-00 00:00:00','Anchorage Transit Route Service Update','',135,139,0,0,'','11/1/2000','2/23/2001','direct from client','',45000,'45000',5,'0000-00-00 00:00:00','6/26 - LOSER','01014',15,'City of Anchorage, AK','0'),(3,'0000-00-00 00:00:00','Treasure Island EIR','',260,270,0,0,'','1/22/2000','','EDAW','',0,'0',5,'0000-00-00 00:00:00','We are not sorry we lost this one, given the absurd politics around development on the island.','01015',28,'City of San Francisco, CA','0'),(4,'0000-00-00 00:00:00','San Jose Planning','',264,1100,735,0,'','','','','',2000,'2000',5,'0000-00-00 00:00:00','','01016',5,'','0'),(5,'0000-00-00 00:00:00','San Marino Parking as Needed','',269,279,0,0,'','1/29/2001','','call from prime','',0,'0',5,'0000-00-00 00:00:00','quick-turnaround letter proposal if we have time to do it.','01017',10,'','0'),(6,'0000-00-00 00:00:00','Presidio Trust Environmental As Need SOQ','',736,761,0,0,'','10/26/2000','11/7/2000','call from prime','',0,'0',5,'0000-00-00 00:00:00','9-18 LOSER. Presidio Trust selected three firms for their list including EPI, Jones & Stokes, & URS. Karen Frye at TetraTech, the prime, was really surprised and has yet to get an explanation from the Trust.','01018',6,'Presidio Trust, SF, CA','0'),(7,'0000-00-00 00:00:00','Sacramento 65th Street Facility Study','',254,264,57,0,'','','2/12/2001','','',0,'9552',5,'0000-00-00 00:00:00','','01019',6,'','0'),(8,'0000-00-00 00:00:00','Foothill Transit/Cal Poly Transit Center Study','',426,440,659,0,'','','','','',20000,'20000',5,'0000-00-00 00:00:00','','01020',0,'','0'),(9,'0000-00-00 00:00:00','Reno RTP Transit Element','Project Add-On from 00124',758,784,239,0,'','','','','',0,'0',5,'0000-00-00 00:00:00','','01021',25,'','0'),(10,'0000-00-00 00:00:00','East Bay Senior Transportation','Replicate Portland, Maine, senior transportation model using volunteer drivers',493,505,1102,0,'','','2/1/2001','','',7500,'7500',5,'0000-00-00 00:00:00','','01022',87,'','0'),(11,'0000-00-00 00:00:00','San Rafael Downtown Shuttle Feasibility Study','',271,281,866,0,'','','','','',35000,'35000',5,'0000-00-00 00:00:00','','01023',36,'','0'),(12,'0000-00-00 00:00:00','Monrovia Shuttle','Verify shuttle route and develop bus spec and patronage est.',223,231,15,0,'','2/1/2001','','from client','',16000,'8000',5,'0000-00-00 00:00:00','BN submitted scope material to Patti.','01024',6,'','0'),(13,'0000-00-00 00:00:00','Evaluation of Menlo Park Midday Shuttle','',216,224,260,0,'','2/10/2001','3/1/2001','Mail from client','',0,'0',5,'0000-00-00 00:00:00','Lost to Cliff Chambers on cost and extra add-ons','01025',28,'','0'),(14,'0000-00-00 00:00:00','Santa Fe Trails Comprehensive Operations Analysis','COA?',850,878,0,0,'','2/10/2001','3/10/2001','Passenger Transport','',0,'0',5,'0000-00-00 00:00:00','5/7 - Apparent winner. Staff is recommedning NN. We barely beat out Urbitran..','01026',25,'Santa Fe Trails, NM','0'),(15,'0000-00-00 00:00:00','State Route 4 Corridor Transit Study','',1056,100,0,0,'','2/1/2000','3/16/2001','call from Cambridge','',800000,'0',5,'0000-00-00 00:00:00','Lost to Wilbur Smith. Team did terrible job at interview.','01027',6,'','0'),(16,'0000-00-00 00:00:00','Ohio - On Call Planning Work','',913,941,0,0,'','','','','',0,'0',4,'0000-00-00 00:00:00','','01028',0,'','0'),(17,'0000-00-00 00:00:00','Plumas County TPA','',723,748,0,0,'','2/5/2001','5/4/2001','Call from client','',20000,'20000',5,'0000-00-00 00:00:00','5/4/01 - CL is writing proposal','01029',10,'Plumas County, CA','0'),(18,'0000-00-00 00:00:00','BART to Livermore I-580 Corridor Study','Do bus system planning as an \"interim\" solution to BART expansion?where the interim may become permanent.',20,1102,480,0,'','','','','',150000,'40000',5,'0000-00-00 00:00:00','','01030',6,'','0'),(19,'0000-00-00 00:00:00','Minnesota Department of Transportation','',479,3049,0,0,'','','','','',0,'0',0,'0000-00-00 00:00:00','','01031',0,'','0'),(20,'0000-00-00 00:00:00','San Francisco Environmental As Needed','',810,1109,0,0,'','','','','',0,'0',5,'0000-00-00 00:00:00','DT got a message from Ellen Cross saying we won the contract and that EDAW is now in contract negociations. Jeffrey Tumlin to follow up.','01032',6,'San Francisco Planning Dept./SF, CA','0'); UNLOCK TABLES; /*!40000 ALTER TABLE `tblopportunities` ENABLE KEYS */; -- -- Table structure for table `tblprojects` -- DROP TABLE IF EXISTS `tblprojects`; CREATE TABLE `tblprojects` ( `pkProjectID` int(10) NOT NULL auto_increment, `fkProposalID` int(10) default NULL, `fkProjectManagerID` int(10) default NULL, `OKtoUseasReference` tinyint(1) unsigned NOT NULL, `RefYear` varchar(4) default NULL, `ReferenceText` longtext, `BeginningDate` datetime default NULL, `EndDate` datetime default NULL, `ReportsinLibrary` longtext, `WPCDArchive` varchar(50) default NULL, `GraphicsCDArchive` varchar(50) default NULL, `ProjectDisposition` tinyint(3) unsigned default NULL, `DateProjectDispositionChanged` datetime default NULL, PRIMARY KEY (`pkProjectID`), KEY `fkProjectManagerID` (`fkProjectManagerID`), KEY `tblProposalstblProjects` (`fkProposalID`), CONSTRAINT `tblprojects_ibfk_1` FOREIGN KEY (`fkProposalID`) REFERENCES `tblproposals` (`pkProposalID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `tblprojects` -- /*!40000 ALTER TABLE `tblprojects` DISABLE KEYS */; LOCK TABLES `tblprojects` WRITE; INSERT INTO `tblprojects` VALUES (1,1,6,0,'','','0000-00-00 00:00:00','0000-00-00 00:00:00','','','',2,'0000-00-00 00:00:00'),(2,2,6,0,'','','0000-00-00 00:00:00','0000-00-00 00:00:00','','','',2,'0000-00-00 00:00:00'),(3,3,45,1,'2002','NelsonNygaard completed a \"windshield survey\" of the cars parked within a half mile of the BART Balboa station.','2002-05-01 00:00:00','2002-08-01 00:00:00','','2002','',2,'0000-00-00 00:00:00'),(4,5,1,1,'2002','Car-sharing is one of the newest additions to the transportation toolbox, and has great potential to reduce parking demand and vehicle travel, and improve mobility for low-income communities.','2003-10-14 00:00:00','0000-00-00 00:00:00','','','',1,'0000-00-00 00:00:00'),(5,7,45,1,'2003','NelsonNygaard is under contract to Caltrans to develop a Master Plan and Program of Projects that will lead to an increase in the use of HOV and Park & Ride facilities (both existing and planned) ','2003-05-01 00:00:00','2004-09-01 00:00:00','','','',1,'0000-00-00 00:00:00'),(6,8,53,0,'','','0000-00-00 00:00:00','0000-00-00 00:00:00','','','',1,'0000-00-00 00:00:00'),(7,9,59,0,'','','0000-00-00 00:00:00','0000-00-00 00:00:00','','','',2,'0000-00-00 00:00:00'),(8,10,5,0,'2002','See 01318','2002-12-05 00:00:00','2003-12-01 00:00:00','','','',1,'0000-00-00 00:00:00'),(9,11,28,1,'2002','NelsonNygaard is assisting the Port of San Francisco to develop parking strategies. .','2002-10-01 00:00:00','2003-02-01 00:00:00','','','',1,'0000-00-00 00:00:00'),(10,12,15,1,'2002','','2003-02-14 00:00:00','0000-00-00 00:00:00','','','',2,'0000-00-00 00:00:00'),(11,11,36,0,'','','0000-00-00 00:00:00','0000-00-00 00:00:00','','','',2,'0000-00-00 00:00:00'),(12,12,6,1,'2002','Marin County Transit District operates in an unusual arrangement with the visible public transit provider, Golden Gate Transit.','0000-00-00 00:00:00','0000-00-00 00:00:00','','','',2,'0000-00-00 00:00:00'),(13,13,6,0,'','Golden Gate Transit has historically provided both transbay transit service over the Golden Gate Bridge and local transit service within Marin County.','0000-00-00 00:00:00','0000-00-00 00:00:00','','','',2,'0000-00-00 00:00:00'),(14,15,44,0,'','The Riverfront Project will consist of a mixed-use development in downtown Napa, CA. NelsonNygaard conducted a shared parking analysis for the development. The project has been approved by the City.','0000-00-00 00:00:00','0000-00-00 00:00:00','','','',2,'0000-00-00 00:00:00'),(15,10,5,1,'2001','NelsonNygaard conducted a comprehensive review of the trip reduction impacts of various employer and non-employer based transportation demand management strategies.','0000-00-00 00:00:00','0000-00-00 00:00:00','','','',2,'0000-00-00 00:00:00'),(16,11,10,0,'2001','','0000-00-00 00:00:00','0000-00-00 00:00:00','','','',2,'0000-00-00 00:00:00'),(17,13,6,1,'2001','NelsonNygaard participated in this multidisciplinary team.','0000-00-00 00:00:00','2001-10-01 00:00:00','','','',2,'0000-00-00 00:00:00'),(18,14,25,0,'2001','In response to growing traffic concerns by the California Polytechnic University-Pomona, university officials considered banning Foothill Transit buses from using their parking facilities as a transit center. ','2001-02-01 00:00:00','2001-05-01 00:00:00','','','',2,'0000-00-00 00:00:00'),(19,18,6,1,'2001','NelsonNygaard is part of a multi-disciplinary team assembled to provide as needed assistance to the San Francisco MUNI as they develop capital and operating projects affecting transit service in the City.','0000-00-00 00:00:00','0000-00-00 00:00:00','','','',2,'0000-00-00 00:00:00'),(20,19,36,1,'2001','The study analyzed the organizational structures of the local operators in the Arroyo Verdugo area determine opportunities for coordinating and/or combining services. ','2002-03-15 00:00:00','0000-00-00 00:00:00','01 Public Transportation Strategy Final Report','','',2,'0000-00-00 00:00:00'); UNLOCK TABLES; /*!40000 ALTER TABLE `tblprojects` ENABLE KEYS */; -- -- Table structure for table `tblproposals` -- DROP TABLE IF EXISTS `tblproposals`; CREATE TABLE `tblproposals` ( `pkProposalID` int(10) NOT NULL auto_increment, `fkOpportunityID` int(10) default NULL, `DateBecameProposal` datetime default NULL, `ProposedTeamingArrangements` varchar(255) default NULL, `InterviewDate` varchar(100) default NULL, `ProposalDisposition` tinyint(3) unsigned default NULL, `DateProposalDispositionChanged` datetime default NULL, `fkPrimeID` varchar(50) default NULL, `Subcontractors` varchar(50) default NULL, `ProposalURL` varchar(100) default NULL, PRIMARY KEY (`pkProposalID`), KEY `fkPrimeID` (`fkPrimeID`), KEY `tblOpportunitiestblProposals` (`fkOpportunityID`), CONSTRAINT `tblproposals_ibfk_1` FOREIGN KEY (`fkOpportunityID`) REFERENCES `tblopportunities` (`pkOpportunityID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `tblproposals` -- /*!40000 ALTER TABLE `tblproposals` DISABLE KEYS */; LOCK TABLES `tblproposals` WRITE; INSERT INTO `tblproposals` VALUES (1,6,'0000-00-00 00:00:00','Prime - Fehr & Peers','',3,'2005-06-30 00:00:00','','',''),(2,7,'0000-00-00 00:00:00','','',3,'1899-12-30 00:00:00','','',''),(3,9,'0000-00-00 00:00:00','','',3,'1899-12-30 00:00:00','','',''),(4,12,'0000-00-00 00:00:00','Prime - MIG','12/16/02',4,'1899-12-30 00:00:00','','',''),(5,5,'0000-00-00 00:00:00','Westat','',3,'1899-12-30 00:00:00','','Westat',''),(6,16,'0000-00-00 00:00:00','','',4,'1899-12-30 00:00:00','','',''),(7,17,'0000-00-00 00:00:00','Subs: Kaku, System Metrics and Roanoke','',3,'1899-12-30 00:00:00','','',''),(8,10,'0000-00-00 00:00:00','Prime: Sam Schwarz.','',3,'1899-12-30 00:00:00','','',''),(9,11,'0000-00-00 00:00:00','Prime: Kittelson','',3,'1899-12-30 00:00:00','','',''),(10,6,'0000-00-00 00:00:00','PRIME: WRT','',4,'1899-12-30 00:00:00','','',''),(11,19,'0000-00-00 00:00:00','PRIME: BFJ','',4,'1899-12-30 00:00:00','','',''),(12,13,'0000-00-00 00:00:00','Primes: Team #1 Torti Gallas, Team #2 WRT and Team #3 DPZ','3/26',4,'1899-12-30 00:00:00','','',''),(13,14,'0000-00-00 00:00:00','','',3,'1899-12-30 00:00:00','','',''),(14,17,'0000-00-00 00:00:00','PRIME: Westat','',4,'1899-12-30 00:00:00','','',''),(15,10,'0000-00-00 00:00:00','','',4,'1899-12-30 00:00:00','','',''),(16,3,'0000-00-00 00:00:00','PRIME: Torti Gallas','',4,'1899-12-30 00:00:00','','',''),(17,5,'0000-00-00 00:00:00','','',3,'1899-12-30 00:00:00','NN','',''),(18,6,'0000-00-00 00:00:00','','',4,'1899-12-30 00:00:00','','',''),(19,7,'0000-00-00 00:00:00','Subs - Debbie Alpers, WESTAT(Health Care expert)','',4,'1899-12-30 00:00:00','','',''),(20,8,'0000-00-00 00:00:00','','',3,'1899-12-30 00:00:00','','',''); UNLOCK TABLES; /*!40000 ALTER TABLE `tblproposals` ENABLE KEYS */; CREATE ALGORITHM= MERGE VIEW `ProductsCategories` AS select tblOpportunities.*, tblProposals.* FROM tblOpportunities INNER JOIN tblProposals On tblOpportunities.pkOpportunityID = tblProposals.fkOpportunityID;