-- http://www.phpmyadmin.net
--




--
-- Database: `gsr_dist`
--

-- --------------------------------------------------------

--
-- Table structure for table `Donor`
--

CREATE TABLE IF NOT EXISTS `Donor` (
  `DonorNumber` varchar(8) NOT NULL,
  `DonorName` varchar(60) NOT NULL,
  `Address1` varchar(60) NOT NULL,
  `Address2` varchar(40) DEFAULT NULL,
  `City` varchar(40) NOT NULL,
  `State` varchar(20) NOT NULL,
  `ZipCode` char(5) NOT NULL,
  `FBCProductCategory` varchar(10) NOT NULL,
  `FBCProductSource` varchar(10) NOT NULL,
  `FBCReason` varchar(15) NOT NULL,
  `ProgramNumber` char(5) DEFAULT NULL,
  PRIMARY KEY (`DonorNumber`),
  KEY `ProgramNumber` (`ProgramNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Donor`
--

INSERT INTO `Donor` (`DonorNumber`, `DonorName`, `Address1`, `Address2`, `City`, `State`, `ZipCode`, `FBCProductCategory`, `FBCProductSource`, `FBCReason`, `ProgramNumber`) VALUES
('PD00000', 'Test Donor', '401 Delaware St.', '', 'Kansas City', 'MO', '64105', 'DONATED', 'A2H LOCAL', 'RETGROC', 'F0000');

-- --------------------------------------------------------

--
-- Table structure for table `Item`
--

CREATE TABLE IF NOT EXISTS `Item` (
  `ItemNumber` char(5) NOT NULL,
  `ItemName` varchar(30) NOT NULL,
  PRIMARY KEY (`ItemNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Item`
--

INSERT INTO `Item` (`ItemNumber`, `ItemName`) VALUES
('20020', 'Bread'),
('20039', 'Assorted Refrigerated Product'),
('25000', 'Produce'),
('90000', 'Assorted Mixed Dry'),
('90030', 'Assorted Frozen');

-- --------------------------------------------------------

--
-- Table structure for table `PickupDetail`
--

CREATE TABLE IF NOT EXISTS `PickupDetail` (
  `LineNumber` int(11) NOT NULL,
  `PickupId` int(11) NOT NULL,
  `Quantity` int(11) NOT NULL DEFAULT '0',
  `ItemNumber` char(5) DEFAULT NULL,
  PRIMARY KEY (`LineNumber`,`PickupId`),
  KEY `ItemNumber` (`ItemNumber`),
  KEY `OrderId` (`PickupId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `PickupDetail`
--

INSERT INTO `PickupDetail` (`LineNumber`, `PickupId`, `Quantity`, `ItemNumber`) VALUES
(1, 1, 30, '20020'),
(2, 1, 0, '20039'),
(3, 1, 50, '25000'),
(4, 1, 0, '90000'),
(5, 1, 0, '90030');

-- --------------------------------------------------------

--
-- Table structure for table `PickupHeader`
--

CREATE TABLE IF NOT EXISTS `PickupHeader` (
  `PickupId` int(11) NOT NULL AUTO_INCREMENT,
  `PickupDate` date NOT NULL,
  `TotalAmount` int(11) NOT NULL,
  `PickupEntryTime` datetime NOT NULL,
  `ProgramNumber` char(5) NOT NULL,
  `DonorNumber` varchar(7) NOT NULL,
  PRIMARY KEY (`PickupId`),
  KEY `ProgramNumber` (`ProgramNumber`),
  KEY `DonorNumber` (`DonorNumber`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `PickupHeader`
--

INSERT INTO `PickupHeader` (`PickupId`, `PickupDate`, `TotalAmount`, `PickupEntryTime`, `ProgramNumber`, `DonorNumber`) VALUES
(1, '2014-01-28', 80, '2014-01-28 08:25:32', 'F0000', 'PD00000');

-- --------------------------------------------------------

--
-- Table structure for table `Program`
--

CREATE TABLE IF NOT EXISTS `Program` (
  `ProgramNumber` char(5) NOT NULL,
  `ProgramName` varchar(60) NOT NULL,
  `Username` varchar(30) NOT NULL,
  PRIMARY KEY (`ProgramNumber`),
  KEY `Username` (`Username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Program`
--

INSERT INTO `Program` (`ProgramNumber`, `ProgramName`, `Username`) VALUES
('F0000', 'Test Program', 'F0000');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `Donor`
--
ALTER TABLE `Donor`
  ADD CONSTRAINT `Donor_ibfk_2` FOREIGN KEY (`ProgramNumber`) REFERENCES `Program` (`ProgramNumber`);

--
-- Constraints for table `PickupDetail`
--
ALTER TABLE `PickupDetail`
  ADD CONSTRAINT `PickupDetail_ibfk_1` FOREIGN KEY (`PickupId`) REFERENCES `PickupHeader` (`PickupId`),
  ADD CONSTRAINT `PickupDetail_ibfk_2` FOREIGN KEY (`ItemNumber`) REFERENCES `Item` (`ItemNumber`);

--
-- Constraints for table `PickupHeader`
--
ALTER TABLE `PickupHeader`
  ADD CONSTRAINT `PickupHeader_ibfk_1` FOREIGN KEY (`DonorNumber`) REFERENCES `Donor` (`DonorNumber`),
  ADD CONSTRAINT `PickupHeader_ibfk_2` FOREIGN KEY (`ProgramNumber`) REFERENCES `Program` (`ProgramNumber`);

--
-- Constraints for table `Program`
--
ALTER TABLE `Program`
  ADD CONSTRAINT `Program_ibfk_1` FOREIGN KEY (`Username`) REFERENCES `User` (`Username`);

