RESEARCH SUBSCRIPTIONS DEVELOPMENT

SETTING UP DEVELOPMENT ENVIRONMENT

OVERVIEW

This section is made to guide new developers through the process of setting up a development environment for the subscription service. It is intended to be as thorough as possible so as to remove the need for guesswork; if all of the steps were followed, the user should have a working development copy of the subscription software on his/her system.

REQUIREMENTS

Brief overview of the requirements to have the software running properly--meant only as a quick reference

SOFTWARE

• LAMPPP (Linux Apache2 MySQL PHP5 PHPMyAdmin PostGreSQL) See below for software setup.
• Ubuntu (currently using 12.04).
• INTL extension for php. A special number formatting class is used that requires this
• Latex support for Ubuntu. Required for writing to the pdf directory. (Both the texlive-latex-base and texlive-recommended-fonts packages are required.)

DATABASE

• Local clone of inventory database
• Local clone of subscription database
• Remote access to UWDir database

USER ACCESS

• Access to subscription software repo to make a clone. I.e. from: https://git.uwaterloo.ca/cscf/subscription (for ssh gitlab@git.uwaterloo.ca:cscf/subscription.git)
• Access to subscription database (user account and password) to download a clone. I.e. from: postgres.cs.uwaterloo.ca (database called _cscf_subscription)
• Access to Inventory database (user account and password) to download a clone. I.e. from: http://cs.uwaterloo.ca/phpMyAdmin (database called equipment)
• Access to UWDir database (user account and password) for remote access. I.e. from: postgres.odyssey.uwaterloo.ca (database called odyssey)

OBTAIN NECESSARY USER ACCESS

1. (SUBSCRIPTION REPO ACCESS) Obtain a user name and a password to access the source code from https://git.uwaterloo.ca/groups/cscf.
2. (SUBSCRIPTION DATABASE ACCESS) Obtain a user name and password to access the subscription database in order to download the database called _cscf_subscription from postgres.cs.uwaterloo.ca as an sql file.
3. (INVENTORY DATABASE ACCESS) Obtain a user name and password to access the database called equipment from the inventory database at http://cs.uwaterloo.ca/phpMyAdmin
4. (UWDIR DATABASE REMOTE ACCESS) Obtain a user name and password to access the database called odyssey from postgres.odyssey.uwaterloo.c

SETTING UP NECESSARY SERVER STACK

1. (SETUP LAMPP) A set of instructions for the installation of the LAMPP stack can be found in the Inventory Installation TWiki page here. Only follow the LAMPP installation section. Additionally, follow steps 1 & 4 under "Apache Config Files" to enable phpmyadmin and .htaccess files in the apache config file.
2. (INSTALL POSTGRESQL) Install PostgreSQL with the following commands in terminal.
   1. (UPDATE APT-GET) sudo apt-get update
   2. (INSTALL POSTGRESQL AND PHPPGADMIN) sudo apt-get install postgresql postgresql-contrib phppgadmin
3. (EDIT APACHE FILE TO LOAD PHPPGADMIN) Similar to step 1, the apache config file must be edited again to allow phppgadmin to load. Do the following Steps:
   1. (OPEN THE APACHE CONFIG FILE) sudo gedit /etc/apache2/apache2.conf
   2. (APPEND LINE) Add the following line to the bottom: "Include /etc/apache2/conf-available/phppgadmin.conf"
   3. (RELOAD AND RESTART APACHE) sudo service apache2 reload; sudo service apache2 restart;
4. (SETUP POSTGRES USER) Perform the following terminal commands:
   1. (LOG INTO POSTGRES) sudo -i -u postgres; #log on as postgres user
   2. (CREATE NEW POSTGRES USER) createuser
   3. (SET USER NAME) #set the user name as "_cscf_subscription" and make this a super user by accepting the prompt.
   4. (CREATE NEW POSTGRES DB) createdb _cscf_subscription;
   5. (ADD PASSWORD FOR _CSCF_SUBSCRIPTION) Use the same password for connecting to the live remote database.
      1. psql -d _cscf_subscription;
      2. \password _cscf_subscription <return>
      3. [enter password]
      4. ctrl+z
   6. (EDIT PG_HBA.CONF) Open the file "/etc/postgresql/9.3/main/pg_hba.conf" (note the path might be different depending on your version installed!). Edit all of the terms under the header "METHOD" so it says md5.
   7. (RELOAD APACHE) sudo service apache2 reload;
   8. (EDIT USERS TABLE) Use the following commands to give all required access to the subscription software (the software will check this table to determine a user's privileges. Replace <username> with your username for accessing this db. It must be the same one that you enter when you first try to gain access to the subscription website. Since it will check the $_SERVER['REMOTE_USER'] var against it.
   • psql -U _cscf_subscription
   • INSERT INTO _cscf_subscription.users (username, comments, expire, flags) VALUES ('<username>', null, null, 'rmdb');
5. (INSTALL PDO)
   • Add 'extension=pgsql.so'
   • Uncomment 'extension=php_pdo_pgsql.dll' in /etc/php/5.6/apache2/php.ini
   • Install php5.6-psql
6. (INSTALL ADDITIONAL EXTENSIONS) Additional extensions are required. Install them with the following terminal commands
   1. (INTL EXT FOR PHP) sudo apt-get install php5-intl
   2. (LATEX SUPPORT) sudo apt-get install texlive-latex-base texlive-fonts-recommended
   3. (GIT) sudo apt-get install git

SETTING UP DATABASES

INSTALLING THE SUBSCRIPTION DATABASE

1. (LOG INTO POSTGRES) sudo -i -u postgres; #log on as postgres user
2. (GET DATA FILE FOR IMPORTING) Ask Daniel for a copy of data
3. (UPLOAD SCHEMA) In terminal use this command and follow prompts: psql -U _cscf_subsctipion --password -d _cscf_subscription < ~/path_to_data_file

INSTALLING INVENTORY DATABASE

1. (SEE INVENTORY INSTALLATION GUIDE) Follow the directions from the inventory installation guide here to get a local copy of the inventory database. Note it is unnecessary to install the rt-math-1 database.

CONFIGURING DATABASE PASSWORD FILE

1. (DOWNLOAD PASSWORD TEMPLATE FILE) Download the template file from here.
2. (ADD PASSWORDS AND USERNAMES) Starting from the template, the passwords and usernames need to be added for each database.
3. (MOVE PASSWORD FILE TO WITHIN HOME DIRECTORY) Move the file to the home directory of the user who will be developing.
4. (ACCESS DATABASES USING SSH TUNNEL)
   
   1. Subscription DB:
      • local: ~$ psql -U _cscf_subscription _cscf_subscription
        
   2. UWDIR DB:
      • ssh -L 5433:postgres.odyssey.uwaterloo.ca:5432 your_watiam@linux.cs.uwaterloo.ca
      • local: ~$ psql -p 5433 -h localhost -d odyssey
        
   3. RT_IST DB:
      • ssh -L 3310:rt44.uwaterloo.ca:3306 your_watiam@www152
      • local:~$ mysql -u rt-cs -p --port 3310 -h www152.cs.uwaterloo.ca rt4

INSTALLING AND CONFIGURING SUBSCRIPTION SOURCE FILES

1. (DOWNLOAD SOURCE) Make the directory /var/www/subscription/ and navigate to it in terminal. Then run the following commands in terminal:
   1. (INITIALIZE GIT REPO) git init
   2. (ADD REMOTE REFERENCE) git remote add origin gitlab@git.uwaterloo.ca:cscf/subscription.git
   3. (FETCH REPOS) git fetch origin
   4. (CHECKOUT BRANCH) git checkout -b prod --track origin/prod
2. (ENABLE DEV MODE AND SET USER NAME) Open the file /subscription/inc/globalConst.inc and set DEVMODE to "true" then set DEVUSER to the account name of the computer (this is so it can find the home directory where db-passwords.inc is located).
3. (ENABLE WRITE ACCESS) Open the terminal and navigate to /var/www/subscrip/subscription/ then do the following commands, "sudo chmod 777 bills pdf spreadsheets". This is because those directories will be used for writing for the bills and need to be downloadable.

ARCHITECTURE OVERVIEW

OVERVIEW

This is a description of how the different software components (php file) come together to form the program. It is meant to brief while not lacking a significant amount of detail in order to serve as a valuable reference for development. The first section will serve as an abstract interpretation while still referencing the various components and the second part will discuss each of the component handler files separately.

ABSTRACT

The subscription web app was made in order to aid the CSCF department in keeping track of research subscriptions. It is more than a simple CRUD design since it also applies specific logic from the business side of the subscriptions service provided by CSCF. The subscription web app queries from three different databases: the inventory database, the UWDir database, and the subscription database. The inventory database is used to determine which machines are currently tied to a subscription and which subscription it is. The UWDir database is used to pool information about a user such as their full first and last name for printing on the bills. The subscription database is used to keep track of the financial aspect and status of each subscription.

TYPICAL WORK FLOW

The subscription web app uses only two Classes (SupportAcct and SupportTerm). It's structure is somewhat inconsistent because part of the application seems to have an MVC design--the Support Classes appear to be using MVC ideology such that the classes are designed similar to how a model class would be designed in MVC framework. It is notable that the views are not organized in a strict fashion and tracing the program thread path of display elements may be difficult because of this.

A typical workflow for the subscription application is as follows:

1. User makes a request to one of the handler documents (below are the ones used for the largest portion of the application):

• Admin.php
• Pay.php
• User.php
• View.php

3. GlobalConst.inc and db-passwords.inc will have their constants loaded.

4. Connect.inc will be used to establish a database connection.

5. Authentication will be checked against the subscription database.

6. For a typical request, the handlers may pass the data to dataCheck.inc to verify the data passed in the request is in a valid format according to the application's requirements.

7. DataCheck.inc will use specific criteria to check the data against, including hardcoded checks; it will also likely call sqlFunct.inc to check the data against the database.

8. DataCheck.inc will return to the original handler page whether the request contained valid data.

9. Htmlfunct.inc will build and display the html.

Note: If the query was regarding the support_term or the support_acct database tables, then after authenticating and determining the nature of the request, an instance of the respective tables may be instantiated and class functions used for validating, querying and displaying data--by calling dataCheck.inc, sqlFunct.inc, and htmlFunct.inc respectively.

AUTHENTICATION

The authentication has of a user accessing the software has a few steps involved.

1. (APACHE) - Apache will first determine if the user with the incoming ip address is allowed access to the site.
2. (APACHE HTPASSWD) Apache will check the user name against the .htpasswd file specified. The path to the .htpasswd file may be specificed in the apache2.conf or in the local .htaccess file of the root of the webapp directory. (Use Ctrl+H) to view dot files while in Nautilus.
3. To generate an access password for your user, see https://cs.uwaterloo.ca/twiki/view/CF/InventoryInstallation#The_htpasswd_inv_File
4. (POSTGRES ACCESS) Subscription app will try to open a connection to the Subscription database with the information set in db-passwords.inc.
5. (SUBSCRIPTION ACCESS) Subscription app will then perform a query for the username taken from $_SERVER['REMOTE_USER'] (the sign in used for apache) and will determine what (if any) privileges the user has).

DATA HANDLING

The application architecture hasdivided the data handling into separate operations. Database connections are all handled in the connect.inc file. All database queries (with only a few exceptions) will be performed in sqlFunct.inc. Data validation will be performed in dataCheck.inc. There is also a model designed for the support_acct and support_term tables which play integral parts in the handling/checking/manipulating of this data.

DISPLAY AND VIEWS

The displaying of information and document elements has no particular strict design which the application has adhered to; though, upon observation it can be noticed that there is a certain trend which may help a developper to locate specific elements.

• Key HTML components, such as the head and footer, are created (but not displayed) in htmlFunct.inc.
• Tables and table displays are created using functions from htmlFunct.inc
• The actual echo statements are, more often than not, written into the main request handler for the current page the user is viewing.

FOLDERS

• (BILLS) - Contains the .TEX format of the bills--generated after pressing the generate bills button. Directory must be readable and writable since the server writes the .Tex files here.
• (CSS) - Contains CSS files for web app.
• (IMAGES) - Images for buttons, loading sign, arrows and various other features.
• (INC) - Shared library files containing core functions of the web app.
• (JS) - Third party Javascript source files. Using jQuery and various plugins.
• (PDF) - PDF formatted bills generated after the generate bills button is pushed. Directory must be read and writable.
• (PHPUNIT) - Contains the source for PHPUnit which is a unit testing framework for the PHP programming language.
• (SPREADSHEETS) - Holds the spreadsheet files which the server creates upon request. Directory must be readable and writable.
• (SUB-OUT) - Contains downloadable output files created by the server in html format. Lists are of the current dedicated and desktop subscriptions. Needs to be read and writable.
• (TESTS) - Folder containing application testing pages.
• (TINYMCE) - Contains jQuery plugin TinyMCE.

PHP COMPONENT FILES

• (INC CORE FILES) - These files are necessary for the functionality of the handler pages and must be included.
• (SUPPORT.INC) - This contains the classes for "Support Account" and "Support Term". They are related directly to the tables with the same name in the subscription database. In a sense these are models for how to handle the data in those tables. All data handling of those tables SHOULD be done through these class methods.
• (AUTH.INC) - Called before any requests are processed to determine user authentication.
• (CONNECT.INC) - Here is the file for setting up conntections to the inventory, subscription database and uwdirectory (UWDir). It may need to be modified if you cannot get the postgres service files to work or if desiring to hardcode a connection. This is included in any php file that requires database connections.
• (DATACHECK.INC) - This file contains the functions used in checking user input. All of the functions here recieve $_POST data as a passed in parameter (though it is not necessary to pass it as a parameter since it will be available through the entire request).This is used as a filter whenever the user pushes a button. It is called on most button actions and therefore it can be especially useful in debugging.
• (ERRORHANDLING.INC) - Used to handle errors. Frequently referred to in sqlFunct upon bad connections.
• (GLOBALCONST.INC) - Contains constants in php using the "define()" statement. This is where DEVMODE and DEVUSER is set.
• (HTMLFUNCT.INC) - Creates common HTML essential components.
• (PASSWORD.INC) - This file was originally meant to contain database passwords and connection information that was declared in PHP define() statements, but it was moved to home directory to be out of reach of possible do-no-gooders.
• (SQLFUNCT.INC) - The core database querying file. All of the sql queries are performed in this file.
• (SQLFUNCT.MANUALBACKUPBEFOREMAJORCHANGE) - A specially made php file used to manually back up before making a major change.

REQUEST HANDLERS -- BASE DIR PHP FILES

• (ADMIN.PHP) - Handles most admin features of the script, including setting prices, hours and billing templates, it also contains links to billing and review.
• (CONVERT.PHP) - Converts old subscriptions table to new subscriptions table, includes cleanupimport.csv which imports term data.
• (CONVERTINV.PHP) - Converts inventory to use new sc- subscription codes.
• (CONVERTRT.PHP) - Converts RT to using 'sc-' subscription codes.
• (CONVERTTONEWFACULTY.PHP) - Converts data in rel_acc_users for the "profs" (faculty) field to the new "user_payments" table.
• (FORE.PHP) - CSCF FORE spreadsheet generation. Also contains the option to clear printed bills (the ones where has_been_printed=true).
• (LOGOUT.PHP) - Logs user out.
• (OUT.PHP) - Include this file for outside world to view limited information on subscriptions.
• (PAY.PHP) - Handles most payment related things, generating bills, viewing account ledgers etc.
• (REPORT.PHP) - Handles most reporting, SQL queries are from old subscription management system, exactly the same functionality as old system.
• (REVIEW.PHP) - Handles review, past 3 terms are shown.
• (USER.PHP) - Handles most user related things such as changing permissions and comments, also includes fields for credit functions, but the processor itself is in pay.php.
• (VIEW.PHP) - Handles most subscription based things, including editing, creating and viewing subscriptions.

DATABASE SCHEMA

CHARGE

Charges and Payments table. Holds the information regarding the transactions an individual account has made--including: charges, payments, why the transaction occured, when the transaction happened, the type of account and whether it's been printed.

• (SUPPORT_ACCT) - Account associated with the transaction row.
• (TERM_ID) - Term_id number to reference a specific school term. (I.e. 1145 references Spring 2014). This is used as a FOREIGN KEY to connect with the other tables.
• (DEBIT) - Amount that the account has been charged by the subscription service on a specific transaction.
• (CREDIT) - Amount the account has paid to the subscription service on a specific transaction.
• (REASON) - Reason the transaction occured.
• (COMMENTS) - Additional comments on the transaction. This appears to be a mostly stale column as only 15 items have this filled in.
• (DATE) - Date and time of the transaction in the format: yyyy-mm-dd 24h:min:sec.decimal_seconds
• (DEL_WHEN_REGEN) - Used when adding charges to a user. It is a failsafe to ensure that someone is not double charged during the same term. It also marks a row for deletion just before generating a bill so it is calculated fresh every time. Upon creating a new term all of the rows with a del_when_regen value of true are set to del_when_regen = false.
• (TYPE) - Specifies the whether account is subscription or personal.
• (HAS_BEEN_PRINTED) - Indicates an item has been printed. It is only set to true when the Clear Bills button is pressed in admin > FORE spreadsheet.

REL_ACCT_USER

A relations table one that relates user_ids with support_ids (i.e. subscription codes). A single user may be related to more than one subscription.

• (SUPPORT_ACCT) - Subscription Code. Links to Support_Acct table. Also relates to 'subscriptionCode' from Inventory db.
• (USER_ID) - User's ID. Links to users table.
• (REL_STATUS) - Status of the user. Links to user_FLDS table.

SUPPORT_ACCT

Information regarding the individual subscriptions and their current status.

• (SUPPORT_ID) - Subscription ID. Relates to supportCode in Inventory db.
• (INACTIVATION_DATE) - Date subscription became inactive. Keep for records.
• (COMMENTS) - HTML formatted comment
• (BILLING_STATUS) - Numeric (0,1,9). English bill, dont bill, or undecided.
• (DATE_CREATE) - Date subscription was created.
• (DATE_EDIT) - Date last edited.
• (MFCF_SUPPORT) - Link to a MFCF help page. Can be NULL. Most if not all links here are stale.
• (WEBSITE) - Can be NULL.
• (GROUP_NAME) - Describes what group the subscription is related to. Must not be NULL.
• (ORG_UNIT) - Organizational Unit. Only used for organization purposes. Not used in any code branching. Currently only 'CS' and NULL.

SUPPORT_TERM

Data relating to the subscription status of the subscription accounts on a by term basis. I.e. units billed and type of account etc.

• (UNITS_DD) - Dedicated units the subscription was billed for, for the given term. These work different than desktops since they are charged hourly.
• (UNITS_DK) - Desktop units the subscription was billed for, for the given term. Refers to computer units. Not necessarily equal to the number of Computer items linked to a subscription in inventory db.
• (TYPE) - Type of account: 'dedicated or desktop'.
• (SUPPORT_ACCT) - Subscription account code. Links to support_acct table.
• (TERM_ID) - Numerical term id which represents a year and season (Fall, Winter, Spring).
• (POC) - Point of Contact. LINKS to Users table. There will only be one point of contact for a given subscription.

TERM

Information about each term such as the cost of the subscription for dedicated and desktop subscriptions for a given term. Also has templates for latex.

• (NUMERICAL_ID) - ID number of the term.
• (UNIT_COST) - Cost per unit for the term.
• (MACHINE_COST) - Cost per desktop machine for the term.
• (UNIT_HR) - Cost of an hour of a unit for term.
• (LATEX_TEMPLATE_DD) - Latex template used for printing the bills for dedicated term subscriptions.
• (LATEX_TEMPLATE_DK) - Latex template used for printing the bill for desktop term subscriptions.
• (UNIT_HR_DK) - Desktop unit cost (per hour) for the term.
• (DD_BILL_PAGINATION) - Number of dedicated units to paginate on bills.
• (DK_BILL_PAGINATION) - Number of desktop units to paginate on bills.

USER_FLDS

Table used for categorizing users and creating new categories if desired. Every user will have an associated rel_status from here.

• (REL_STATUS) - Primary key for the table and thus a unique integer identifier by which to relate each row.
• (FIELD_NAME) - Computer/code friendly field name, not meant to be viewed.
• (FORM_NAME) - Label that will be displayed in subscriptions.
• (BILLING_FIELD) - Determines whether or not the user should be billed for their subscriptions.
• (ORDER_LIST) - Unique column used to change the order of results when database is queried. I.e. used in conjunction with an ORDER BY SQL command.

USER_PAYMENTS

Subscription payment default information for auto-filling payment form. Also holds some stored account numbers.

• (SUPPORT_ID) - Subscription code which relates to Support_acct table.
• (USERNAME) - User associated with the subscription and partly responsible for payment
• (METHOD) - Preferred method of payment for the given subscription. Valid values are: '%', 'even', or '$'.
• (VALUE) - Value is used when the method requires it. If the method was '%' or '$'.
• (ACCOUNT_NUMBER) - Account number of the user. Doesn't necessarily need to be filled in.

USERS

List of users as well as expiry dates and privilege flags for database use/access.

• (EXPIRE) - Can be null or it can accept a date in the format: yyyy-mm-dd. The purpose is to determine whether a user should still be active in the system.
• (FLAGS) - String field which can be null as well. Represents user privileges when accessing the database.
  • 'r' read
  • 'd' delete
  • 'b' bill (to process a bill)
  • 'm' for modify (to modify data)

RECOMMENDED DEVELOPMENT ENVIRONMENT

A good development environment will make a huge difference in productivity and ease of work in general. For some recommendations, please click here to proceed to the software recommendations section in the inventory installation guide.