Clay Schumacher Josh Weisskopf Cory Simon Advisor: Tien Nguyen

1. Reiman Gardens Plant Database Dec 08-06 Clay Schumacher Josh Weisskopf Cory Simon Advisor: Tien Nguyen

2. Planning

3. Problem Statement • Reiman Gardens • Occupies 14 acres on the ISU campus • Indoor and outdoor gardens, five greenhouses • Keep detailed records of all plants in the gardens • Existing Database System • Difficult to use • Isolated from other software and equipment • Inaccessible to the public

4. Project Goal • Create a new plant database for Reiman Gardens: • Primary: • Plant record management • Friendly user interface • Low Cost • Secondary: • Create files for label making machines • Allow public access • Print forms for use in the gardens

5. Basic Requirements • The product shall store nine types of records: Accession, Genus, Species, Supplier, Location, Reference, Family, Photo, and Propagation • The product shall allow users to create, edit, view, and search records • The product shall have three user groups: administrator, staff, and public • The product shall export files containing plant information to be printed by label makers

6. Market SurveyBG-Base

7. Market SurveyKE EMu

8. Design Alternatives • Shared Drive Database • Similar to existing system, host database on the existing shared drive at Reiman Gardens • Local Server • Use a web based approach hosted on a new server at Reiman Gardens.

9. Final Design Concept • Web based system hosted off site • Capable of satisfying all requirements • Familiar interface for users • Tested and supported design • LAMP server solution stack • Linux operating system • Apache web server • MySQL database management system • PHP scripting language

10. Design vs. Goals • Positive • Plant record management • Relatively low cost • Customized user interface • Public access • Customized file output • Negative • Cost: previous system was free • Capability: commercial systems have more functionality

11. Basic Diagram Registered User Client Label Makers Web Browser Public User Server PHP HTTP Server Queries Responses Database

12. Schedule • Initially had four members – scheduled 135 hours for each 1st Semester, 90 hours for each 2nd semester • Created WBS for each semester and assigned tasks to each group member • Major headings: • Project Plan • Design Document • Implementation • User Manual • Testing

13. Schedule After First Semester • We had produced a basic system design • Ready for development • Total hours to this point: • 538 (out of 540 scheduled) • Rolando: 123 • Clay: 134 • Cory: 135 • Josh: 146 • Planned to finish implementation by Thanksgiving

14. System Design

15. Input/Output Specification • Input: HTTP Page Requests • Output: HTML, CSS, JavaScript, and Labelmaker Files

16. User Interface Specification • Login Page • Welcome Page • Help Page • Search Results Page • Model Index Page • Add Record Page • View Record Page • Edit Record Page

17. Software SpecificationModel-View-Controller

18. Software SpecificationCakePHP • CakePHP is a rapid development framework for PHP • Based on Model-View-Controller architecture pattern • Easy installation and no configuration required • Active community with useful forums • Object oriented design

19. Software SpecificationModels Families Alternative Names References Genera Species Nativities Accessions Gardens Removals Users Suppliers Propagations

20. Software SpecificationModels • All Models inherit from AppModel class • Defines defaults that can be overridden • Each class defines key fields and methods • Validation rules and error messages • Identifying field for auto complete • Input Cues • Related models • Visible fields and related models • Get display name function – for special names • CakePHP provides functions through model classes for retrieving/saving info from the DB

21. Software SpecificationControllers • AppController Superclass • Contains index, add, edit, and view • Contains search and autocomplete functions • Verifies authentication, access control • Additional Actions: • Accessions and Gardens: Export to Excel • Species: Upload Image, Remove Image, Full Image • Users: Login, Logout, Register, Reset Password • Unique Controllers • Species: Overwrites add, edit, view • Genus: Overwrites add, edit

22. Software SpecificationComponents • Classes containing useful functions for controllers • Prebuilt components • Email: used by users controller • Auth, Session, Cookie: used for authentication • Custom components • Upload: used for uploading images

23. Software SpecificationViews • Views are the interface • Layout • Defined in one file and used everywhere • Header • Navigation, Search, Breadcrumbs • Footer • Content • Individually defined for each controller and action • Sandwiched between header and footer • CSS • Defined in one file and used everywhere • Dictates cosmetics (positioning, color scheme, etc.)

24. Software SpecificationHelpers and elements • Helpers • Classes that provide presentation logic • Standard CakePHP helpers: • HTML, Javascript, Form, Ajax, and Pagination • Additional helpers • Breadcrumbs and Excel .xls generation • Custom helpers • Add, edit, view forms and cosmetic markup • Elements • Reusable chunks of markup (mini-views) • Custom element standardizes search results, indices

25. Software SpecificationExternal Interface • Label maker software accepts Excel files containing data • Fields: • Accession Number • Common Name, Genus, Specific Epithet • Variety, Subspecies, or Forma • Cultivar, Family • Nativity • Quantity, Size • Source, Date Planted, Notes

26. Implementation and Testing

27. Development Tools • Apache • Web Server • Samba • File Sharing • Subversion • Version Control • Trac • Project management • Wiki • Issue tracking • Activity log

28. Development Strategy • A basic process was followed for each team member to accomplish a task

29. Release Strategy • Alpha Release – November 9 • Basic functionality • Beta Release – December 3 • Feature complete • Final Release – December 15

30. Stress Testing • Imported taxa from USDA database to create a large load on the system • Over 400 families, 4000 genera, 38000 species • Highlighted deficiencies in code • Areas that weren’t limiting query results • Beneficial side-effect: system is pre-populated with taxa

31. Key Challenges Security • Public users cannot edit any database information • Users must be registered by an administrator • Authenticated users use SSL for all data transfers • Authentication verified before any controller action is executed • Data Sanitization – CakePHP Component • Remove any HTML, PHP, Javascript, or SQL in input

32. Key Challenges Search • One of the most important functions for the database is the search • CakePHP provides model functions to search the database • Powerful, but unfamiliar – learning curve • A challenge to search for records of one model that are related to another

33. Key Challenges Search • Basic search strings match as “and” terms delimited by spaces • To match a record must contain a match for each term (in any field) • Which related models to search for each model is specified in the model class • User has finer grain control over results displayed – still refining • Code is modular for search and index • Search uses index of each model being searched • Use a common view element to display results

34. Key Challenges Excel Files • Individual Accession or all Accessions from a garden can be exported to an .xls file • File serves dual purpose • It can be printed and serve as a hardcopy report • It is compatible with label-making software

35. Conclusion

36. Status • Currently all requirements are met • The project is very nearly complete • Finish Search • Create Help Page • Visual touch-ups • Expect to be finished by next week

37. Scope Reduction • Evaluated progress, reduced scope with client approval on November 13 • Redesigned photo requirements • Previous requirement: • Independent model containing pictures associated with Gardens, Species, and Accessions • Reduced requirement • Photo field in Species model only

38. Ticket Activity

39. Subversion Commit Activity

40. Project Hours • Fall: 658 • Spring: 538

41. Evaluation • BCWS: 900 hours @ $10/hour = $9,000 • ACWP: 1,196 @ $10/hour = $11,960 • BCWP: $9000 x 95% = $8,550 • Cost Variance: 9,000 – 11,960 = -2,960 • Schedule Variance: 8,550 – 9,000 = -450 • Cost Performance: 9,000/11,960 = 0.753 • Schedule Performance: 8,550/9,000 = 0.95

42. Lessons Learned • Lost team member prior to implementation phase • Chose to proceed as planned • Significantly more hours • Should have reassessed requirements and reduced project scope • Could have dropped public access and used a single log on system • Selected CakePHP during design phase • This was the right decision • Should have created CakePHP prototype during design phase • Would have helped the team understand capabilities and limitations • CakePHP is complicated, would have jumpstarted our development

43. Demo