DB_shops

Database: shops

Date: 2009-11-30

Purpose:

allow shops to associate projects (work orders) with time and materials charges
work orders are associated with speedcharts for billing purposes
can also assign "miscellaneous" charges to a speedchart (ie. not associated with a work order)
dept. admin. staff can view and process billing reports
dept. members (anyone in dept. directory and grad students) can view job status

Contact Person(s): (name, email, phone#)

technical, Mary Ann, map@phas.ubc.ca, 2-6913
end-user, Martin, mokeane@phas.ubc.ca, 2-6663

Maintained by: (URLs, users, scripts, cronjobs)

Admin View:

https://www.phas.ubc.ca/forms/shops/admin/index.php
users: technical staff (Tech. Services Director, Shop Techs)

Data Accessed by: (URLs, users, scripts)

Accounting View:

https://www.phas.ubc.ca/forms/shops/acct/index.php
users: kim, finasst

Client View:

https://www.phas.ubc.ca/forms/shops/view/index.php
users: all dept. members

Dependencies/Effects: (databases, files, cronjobs)

uses accounts.main table for speedchart lookup
accounting interface modifies shops.acctstatus table (to record last billing date)
Help Files: located /home/mshop/public_html/shopdb_help_{shop}.html
- explain Status and Priority codes for Client View

Changelog:

2009-11-27 - Added new fields:
- exp_duration - expected duration of project in days
- rem_duration - estimate of remaining days
- status - eg. In Queue, Complete, etc. (see Help Files above)
- priority - eg. A, B. X (see Help Files above)
- in conjunction with roll-out of Client View

Wish List:

doesn't track the progress of a project - eg. date logging each status change with comments
has separate tables for each shop - should use a "shop" field instead

Code Notes:

this is one of our oldest database applications - previously incarnations included DbaseIII+ and FoxPro
original web version written by two co-op students in 200x (~3?)
has good "constructor" interface (index.php is one big switch)
at least some separation of concerns - ie. code<->file<->table match (eg. time.php, stockwd.php)
database and display interfaces hopelessly interwined
way too much dependency on global variables

Tables:

Five tables for each shop:
1. {shop}_stock - Stock Withdrawal/Material Charges
2. {shop}_techs - Shop Technicians
3. {shop}_time - Time Charges
4. {shop}_types - Work Order Types (eg. Consulting, Design)
5. {shop}_worder - Work Order/Project Definition
acctstatus - records last billing date for each shop
users - allowed users list
- access levels: admin, acct, read
- users have a default shop and yes/no view privileges for each shop

Files:

main directory (/wwws/forms/shops):

library.php

    function select_shop () {
    function set_globals ($user,$reqshop="") {
    function shop_begin () {
    function shop_end () {
    function get_worder ($field) {
    function check_date ($date) {
    function get_speed ($field) {
    function valid_name ($name) {
    function valid_username ($username) {
    function valid_emp_id ($emp_id) {
    function valid_type ($type) {
    function valid_type_name ($type_name) {
    function get_enums ($table, $field) {
    function check_enum($table, $field, $value) {
    function get_techs($techs_table) {
admin sub-directory:
index.php
- basically a giant switch on the action variable

act_report.php:
    - activity report (separated from report.php with addition of status/priority codes)
    function report_act () {

edit.php:
    function edit_techs ()
    function edit_types ()

misc.php
    function show_misc($action) {
    function save_misc () {

report.php
    function select_report () {
    function get_dates($button) {
    function report_ts() {
    function report_sw() {
    function report_admin ($action) {
    function report_shop ($action) {
    function report_wo () {
    function report_workload_balance () {
    function report_workload_summary () {

stockwd.php
    function show_stock($action) {
    function save_stock () {

time.php
    function show_time($action) {
    function save_time() {

worders.php
    function matchDDMenu(textacct, allaccts)
    function matchtextbox(acct)
    function select_worder ($screen='') {
    function show_wo () {
    function wo_readdb () {
    function wo_initial () {
    function show_worder_form () {
    function save_worder() {
    function show_charges () {

acct sub-directory (accounting interface):

acctinfo.php:
   - contains code for producing the account reports
   - is included from admin/report.php
   function chk_date ($date)
   function get_quarter($fromdate, $todate)
view sub-directory (client view):
          index.php
             - accesses data and displays it
shopdb_help.php
   - retrieves the shop variable and includes the appropriate help file