As I create the PO System for my school, I will keep up with the structure and usage information here as much as possible. Current state is designing the GUI in java and creating the PHP to communicate with the MySQL database.
- 1 The Database
- 2 End-User System's Tiers
- 3 The Flow of POs
- 4 MySQL - PHP - Java
- 5 Code Examples
- 6 Planned Quirks
This is setup on my computer in the database PO System, to see the database use the user name "viewonly" (there is no password) in phpmyadmin at http://bland.no-ip.org/phpmyadmin
The Users table contains records for each user with access to the PO System. User specific data in this table will be replaced by the School's LDAP Server's database containing names, passwords, and an ID. This table will still exist for the FinanceAdmin and UserAdmin fields as well as any other database specific info about a user account, however, not all users are garunteed to have an entry in this table.
- ID - Primary key, smallint(6), unique id for each user
- First - varchar(50), the user's first name
- Last - varchar(50), the user's last name
- Login - varchar(32), username for this user
- Login - varchar(32), password for this user, md5 hash
- FinanceAdmin - tinyint(1), 1 for finance administration permission or 0 for not
- UserAdmin - tinyint(1), 1 for user administration permission or 0 for not
Contains departments for both the middle and upper school.
- ID - Primary key, smallint(6), unique id for each department
- Department - varchar(50), the name of the department
- Division - varchar(50), to make the system extendable, aids in queries and readability
Defines user permissions within departments, but is independent of the Users and Departments tables. Usually generic, but can be specific depending on the flow of the approval cycle. Permissions are applied on a per user, per department basis, thus they define the position of the user also.
- Permission - Primary key, varchar(50), the name/title of this position
- PlaceOrder - tinyint(1), 1 if a user can place POs
- ApproveDpmt - tinyint(1), 1 if a user can approve at the department level
- ApproveDivision - tinyint(1), 1 if a user can approve at the division level
- ApproveFunds - tinyint(1), 1 if a user can approve special funding
- ViewOwn - tinyint(1), 1 if a user can see the POs they have made
- ViewDpmt - tinyint(1), 1 if a user can see the POs made by the department
This table is responsible for linking users with Departments and Permissions. Division heads would be associated with each department in their division for simplicities' sake.
- UserID - Primary Key, Foreign Key, smallint(6), an ID in the Users table
- DepartmentID - Primary Key, Foreign Key, smallint(6), an ID in the Departments table
- Permission - Foreign Key, varchar(50), a Permission in the Permissions table
Define all sources of money within the school's budget and the amount each contains, but not how they are utilized.
- ID - Primary Key, smallint(6), unique id for each fund
- Fund - varchar(50), the name/title of this fund
- Description - varchar(255), a description of this fund
- Type - smallint(6), the type of this fund
- 0 - Department Budget
- 1 - Foundation
- 2 - Grant
- 3 - Miscellaneous
- Special - tinyint(1) - 1 if the funding is "special" and requires approval to be used, 0 otherwise
- Amount - float, the total amount of money available in this fund
Associates Funds with Departments specifying how much of the fund they are "allowed" to use.
- DepartmentID - Primary Key, Foreign Key, smallint(6), an ID in the Departments table
- FundID - Primary Key, Foreign Key, smallint(6), an ID in the Funds table
- Amount - float, the amount of money allocated for this department from this fund
Lists all purchases stored in this database, items are stored in a separate table. Total cost is calculated, not stored.
- OrderNumber - Primary Key, int(11), the PO number, can be anything with numbers
- Vendor - varchar(500), name of the vendor, and whatever else will fit in 500 chars
- ShipTo - varchar(500), the shipping address, and whatever else will fit in 500 chars
- OrderDate - The date the order was... placed?
- PaymentMethod - The tender used to pay for the po, a set of values that are determined program side
- Funding - Foreign Key, smallint(6), the type of funding used in this PO
- PlacedBy - Foreign Key, smallint(6), an ID in the Users table
- ApprovedBy - Foreign Key, smallint(6), an ID in the Users table
- Attachments - mediumblob, compressed files in binary form
Keeps track of the variable number of items in each entry in the Purchases table. Even attached items need to have this entry. Total cost is calculated, not stored.
- PurchaseID - Primary Key, Foreign Key, int(11), the PO number that this item belongs to
- Index - Primary Key, smallint(6), the position this item appears in on the PO, both cosmetic and key-related
- Description - varchar(500), a description of the item to be purchased, could be "see attachment"
- UnitPrice - float, the price of one item
- Quantity - int(11), the number of items ordered
The backbone of the authentication and approval part of the POSystem, associates Purchases, Departments, and states of approval.
- PurchaseID - Primary Key, Foreign Key, int(11), the number of the PO in question
- DepartmentID - Primary Key, Foreign Key, smallint(6), the ID of the Department this PO was placed in
- State - smallint(6), the state of approval this PO is in at the moment
- -1 - Rejected
- 0 - Awaiting Department Approval
- 1 - Awaiting Division Approval
- 2 - Awaiting Funding Approval
- 42 - Approved
- 77 - Finished/Received
- Other Custom States? (User reminders?)
End-User System's Tiers
These are the positions I have defined to be created in the PO System's GUI. Each subheading here will have its own part of the GUI where features unique to that position are available. These positions are for the most part defined by associations made in the PermissionsAssoc table, minus the user-specific, global permissions defined in the Users table.
Have a view of their own PO(s) in their own department(s), the generic user.
- No view of the department budget
- Tab-like layout
- Gives ability to quickly see your history and what needs to be done
- Possible "Finished" station after the PO has been processed
- Rejected POs could be tweaked and resubmitted to make life easier.
- Only able to view their own POs and place POs
- Allowed to choose where the funding for a PO will come from
- Funding is a factor in the approval process
Can see all of their respective department(s) POs, history, and budget information.
- Multiple views:
- Their "Teacher" view (same as a normal Teacher)
- Purchases arranged by placer, showing all POs from the department, Rejected/Pending/Approved
- The "Approval" view which shows only POs needing attention (e.g. reject or approve)
- Could show as well how pending POs would affect the budget(s)
- Possible "Funds" section showing utilization of available funds
- Able to approve their own and their department's POs
- Moves to division head after this
- Can have the permission to approve division as well
Essentially Department Heads with some added permissions, and in all division departments.
- Permission to approve all division approvals within their departments
- If the funding is not from a department budget, it moves to the president's approval
- Otherwise, the placer is notified that the PO has been approved
- Full view rights for each department in their division
- Same ability as Department Heads but for multiple departments
Approves special funding purchases
- Same permissions as a Division Head but in all divisions
- Permission to approve the funding approval
- Does this position need any Financial Office permissions?
- Still has Division Head view access to view the usage and settings, but only the financial office can set the budgets
Jurisdiction over the Funds table and how it is utilized.
- Allowed to view department allocations for funds
- Read access to all POs from all departments, all divisions
Chief Financial Officer
The finance administrator, can modify the Funds and FundsAssoc table via the GUI
- Write access to the Funds table (modify/create)
- Within reason, no mistakes will be allowed, the program will catch them (e.g. deleting a department's budget or reallocation a budget lower than the current spent price.)
- This position has the FinanceAdmin field set in the Users table
Able to modify the Users, Departments, and Permissions tables via the GUI
- Able to modify users, permissions (e.g. positions), departments
- Does not contain any finance access - reserved for the finance office
- Does not contain any PO specific access, cannot look at/modify anything with this privilege alone.
- Only deals with modifying the placement of users, and user information
- This position has the UserAdmin field set in the Users table
The Flow of POs
The most important part to get right before I start writing the GUI, I need to know for certain how the POs will flow through the system. This is my understanding of it currently, for simplicities sake, this is upper school only as unless I specify that it is for the middle school as well.
Step 1: Order is Placed
A teacher, upper or middle school, places a PO in a specific department. The teacher will fill out the PO, and choose the funding from a list, then submit the PO into the system. The PO proceeds to Step 2.
Step 2: Departmental Approval
Every PO will go through this step. Each user in the department with the permission to approve at the departmental level now automatically becomes aware of the PO, and the placer can still view it as awaiting departmental approval. The "department head" can see the whole PO, and how it affects his or her budget(s). The PO is either approved by the "department head" and moves on to Step 3, or is rejected and moves to Step X. If the user that placed the PO has departmental approval permission set for this department, this step is silent and the PO proceeds to Step 3.
Step 3: Division Approval
Each user with the divisional approval permission in this department now becomes aware of the PO and has the same view as the "department head" on the effects of the PO. The placer of the PO can now see the PO as awaiting division approval. If the "division head" rejects this PO, it goes to Step X, otherwise there is a logical fork in the road. The PO can either be counted as "fully approved" if the funding is from the department's budget, or as "awaiting funding approval" if the funding is a special type of funding. If the PO is considered "fully approved" it goes onto Step A, if rejected it goes to Step X, and if it needs special funding approval, to Step 4. This step is silent for the middle school division head, since he has both division and departmental approval the same decision he made in the departmental approval applies here as well.
Step 4: Special Funding Approval
All POs with funding that is marked "special" will end up here if approved by the lower two levels. The president, or any other user with the funding approval permission set, now becomes aware of the PO. The placer of the PO can now see the sate as awaiting funding approval. This is the final possible step of approval, so if the funding is approved, the PO moves to Step A, otherwise to Step X.
Step A: Fully Approved
The PO returns to the placer who is responsible for finishing out the PO and reporting back the status of the PO, placed, received, lost, etc. At this point the PO is, for all intensive purposes, done. In other words, no more steps are required.
Step X: Rejected
For some reason the PO was rejected. At this point it is possible for the placer to make any required modifications to fix any errors there may have been that hindered the approval process and resubmit under the same number with the same information to save time and effort. Otherwise this PO is dead and is only remembered by appearing in the list of rejected POs, it has no effect on anything else anymore.
MySQL - PHP - Java
- All information, except calculated budgets, are stored in the MySQL database defined above
- A desktop program written in java will be the main (only needed) tool for maintaining/using the POSystem
- This will communicate with any server setup with the proper database and php files to access information
- Will provide all access outlined in the Inner Workings section
- PHP will link the program to the database, providing intelligent information access. All traffic will be authenticated, no usable information will be available to a user that is not supposed to be able to use it.
- E.G. Authentication and access control will be server side, along with all queries. Thus preventing a "third party" from meddling in the POSystem
- This setup allows ALL database issues to be transparent to the user interface and the user interface to be transparent to the database, keeping all sensitive operations server side
1. auth.php keeps each page it is included on only accessible to the java program that has previously authenticated itself and initiated a php session. Also keeps php sessions constant over one java connection.
2. PHPConnection.java abstracts communication with the server (in tandem with auth.php) and demonstrates an md5 hash of the user's password for security.
3. PHPObject.java makes transferring structured data (e.g. mysql queries) between php and java painfully easy by allowing java to unserialize and serialize php values (PHP Array <> HashMap?)
- The entire PO system can be backed up by saving the database
- A new year can be created simply by setting up the tables and populating them and changing the database name in the PHP.
- No change to the user's programs needed