The functions outlined above represent several types of client and server tasks.

• Credentials

  You will be provided with:

  • a server account on Parks' student server

    Your server account has a userid and password.

    The userid will be of the form glxxxx.

    The first two letters are lower case "GL".

    The last four caracters will be four numbers. (e.g., "gl1234")

    The nine character password is of the form: "AAAddaaaA"

    The first the characters are UPPER case alphabetic characters (AAA)
    Followed by two numbers (dd)
    Followed by three lower case alphabetic characters (aaa)
    Followed by one upper case alphabetic characters (A)
    e.g., ABC44defG

    This information will be emailed the second week of class.

    This userid/password will be used when you FTP files to and from the server

    Associated with this userid is a subdirectory on the server that will contain all of your programs.
    The subdirectory name is the same as your userid (i.e., "glxxxx")

    The URL of the server is:

    auckland.bauer.uh.edu

    (NOTE: there is NO www. prefix)

    The URL of your account will be:

    http://auckland.bauer.uh.edu/students/glxxxx/

    (NOTE: there is NO www. prefix)

  • Homepage

    This will consist of a user menu page (name this assignment homepage index.htm. It should be stored in your disc-nt account directory on the disc.cba.uh.edu server). The general structure of the index.htm page is just a table of links:

    TASKS SPRING 2021	asp	VB.NET	C#
    Database Create/Drop glmaster and je tables	1. link to glrestore.asp	9. link to glrestore.asp (modify to use "SRCGLV")1	17. link to glrestore.asp (modify to use "SRCGLC")1
    Full Trial Balance Lists all glmaster account details Sum of balances and row count	2. link to a44.asp	10. link to a44v.aspx	18. link to a44c.aspx
    Add an account to glmaster Must check to be sure the row doesn't currently exist	3. link to a41.asp	11. link to a41v.aspx	19. link to a41c.aspx
    Modify an account description Must check to be sure the row exists	4. link to a42.asp	12. link to a42v.aspx	20. link to a42c.aspx
    Delete an account Must check to be sure the account has a zero balance	5. link to a43.asp	13. link to a43v.aspx	21. link to a43c.aspx
    glmaster Account Query Display a glmaster account details	6. link to a45.asp	14. link to a45v.aspx	22. link to a45c.aspx
    Journal Voucher Posting Posts journal voucher	7. link to a3.asp	15. link to a3v.aspx	23. link to a3c.aspx
    Journal Voucher Query Lists all je entries for a specific SRN	8. link to a46.asp	16. link to a46v.aspx	24. link to a46c.aspx
    The suggested sequence	This column first top to bottom	Then, this column top to bottom (Omit for Spring 2020)	Then, this column top to bottom (Omit for Spring 2020)

    ¹ Note: See the database names used to load the glmaster table in the instructions of the final exams. (e.g., FALL 2012 MIS 4372 FINAL Item #1)

     

     This index.htm page is IMPORTANT.

     I will grade your assignments from this page!

     1. Do NOT make DEAD links. Place only those links on the page that WORK!

     2. Adhere strictly to the program name structure above. Do not make up other program names.
       If my grading program can't find a program, it doesn't get graded

     3. Place your name and the last four digits of your PSID at the top of the HEAD of every program.

     

  • Your MS-SQL Server-Side Database

    Each student will be assigned a unique database. Two tables will need to be created.

    The first table is the General Ledger Master Table (named glmaster). The table is organized:

    • major -- four digit mumeric, NOT NULL
    • minor -- four digit mumeric, NOT NULL
    • sub1 -- four digit mumeric, NOT NULL
    • sub2 -- four digit mumeric, NOT NULL
    • acctdesc -- a 50 character description (required)
    • balance -- a numeric (18,2) value

    The primary key is the composite of major-minor-sub1-sub2. Transaction amounts from the journal voucher are posted to this table.

    The second table is the Journal Entry Year-to-Date table (named je).

    This table has:

    • sourceref -- four digit number (the preprinted number on the paper journal voucher), NOT NULL
    • srseq -- an integer sequence number indicating the journal entry's physical position on the voucher (i.e., 1 through 6), NOT NULL
    • jemajor -- four digit mumeric, NOT NULL
    • jeminor -- four digit mumeric, NOT NULL
    • jesub1 -- four digit mumeric, NOT NULL
    • jesub2 -- four digit mumeric, NOT NULL

      these last four fields make up the general ledger account number (the primary key of glmast) to which the journal entry was posted

    • jedesc -- a 50 character description field for each journal entry (optional)

    • jeamount -- a currency value indicating the amount of the transaction posted to the account number shown above, NOT NULL

    The primary key of the table is the composite of sourceref and srseq fields.

    MS-SQL Server databases require:

    1. a database name
    2. a SQL login-id
    3. and a SQL login password

    To simplify matters:

    1. Your database is named the same as your userid (i.e., "glxxxx")
    2. Your SQL login-id is named the same as your server userid (i.e., "glxxxx")
    3. Your SQL login password is named the same as your server password (i.e., "AAAddaaaA")

    These two tables are to be created in your database using three different programs (glrestore.asp, glrestorev.aspx and glrestorec.aspx). All three programs perform the SAME create/drop functions.

    A working asp version is available here:

    get the source code for glrestor.asp here

    Execute glrestor.asp

    • You can copy this code to your PC and use it as a starting point.
    • BE SURE TO CHANGE THE CODE (lines 147-149) to the credentials provided to you in class

      So change the existing lines:

      fdsn="gl001"
      fuid="gl001"
      fpwd="11111"
      

      to your credentials (double quotes are required in the code)

      fdsn="glxxxx"
      fuid="glxxxx"
      fpwd="AAAddaaaA"

    • FTP the code to your and account and verify that it works

  • General Ledger Maintenance (Add, Modify, Delete GL Accounts)
    1. ADD a New Account to glmaster
       (three versions named: a41.asp; Omit these for Spring 2020 → a41v.aspx; and a41c.aspx)
       • The user enters a major-minor-sub-1-sub2 account number and description.
       • On submission, the new account will be inserted into the glmaster table.
       • On the server-side, your must check to be sure that the account DOES NOT currently exist in the glmaster table. If it does exist, you provide an error message to the user and gracefully exit.
       • The glmaster account balance will be assigned a zero value

       A sample asp version of this program can be found here

    2. MODIFY an Existing Account Description in glmaster
       (three versions named: a42.asp; a42v.aspx; and a42c.aspx)
       Only account descriptions can be modified (balances cannot)
       • The user specifies the account number and "new" account description
       • On submission, the account description will replace the existing account description in the glmaster table.
       • On the server-side, your must check to be sure that the account DOES currently exist in the glmaster table. If it does NOT exist, you provide an error message to the user and gracefully exit.

       A sample asp version of this program can be found here

    3. DELETE an existing glmaster account
       (three versions named: a43.asp; a43v.aspx; and a43c.aspx)
       • User submits the account number to be deleted.
       • On submission, the account will be deleted if it exists.
       • If the account DOES NOT exist provide an error message to the user and gracefully exit.
       • If account balance is NOT zero, the account SHOULD NOT BE DELETED. Provide an error message to the user and gracefully exit.

       A sample asp version of this program can be found here

  • Report and Queries

    The page should provide three page options for the one report and two queries

    1. Full Trial Balance Report
       Three Versions named: a44.htm; a44v.aspx; and a44c.aspx
       A full trial balance report is a table contains:
       • ALL rows (i.e., accounts) in the glmaster table
       • ALL columns values for each account
       • Sorted in major ASC, minor ASC, sub1 ASC, sub2 ASC order
       • The sum of ALL the account balances at the end (should be zero)
       • A Count of the number of account (i.e., number of rows) in glmaster

       A sample asp version of this program can be found here

    2. glmaster Acccount Query
       Three versions named: a45.htm; a45v.aspx; and a45c.aspx
       • the user enters an account number (i.e., major, minor, sub1, and sub2)
       • The report shows ALL the column values for the account

       A sample asp version of this program can be found here

    3. Journal Voucher Query
       Three versions named: a46.htm; a46v.aspx; and a46c.aspx
       • The user enters a source reference number (SRN)
       • The query returns ALL journal enteries for the specified SRN with ALL column values
       • Display a count of journal entries for the specified SRN

       A sample asp version of this program can be found here

  • Journal Voucher Posting (a3)
    Three Versions named: a3.asp; a3v.aspx; and a3c.aspx

    The journal voucher contains:

    1. a four digit numeric source reference number
    2. a two digit numeric fiscal month
    3. a maximum of six journal entries per voucher. Each journal entry contains:
       1. major account number (maximum of four digits, minimum of 1, cannot be 0, cannot be blank)
       2. minor account number (maximum of four digits, minimum of 1, cannot be 0, cannot be blank)
       3. sub1 account number (maximum of four digits, minimum of 1, may be zero, cannot be blank)
       4. sub2 account number (maximum of four digits, minimum of 1 may be zero, cannot be blank)
       5. 50 character text description (optional)
       6. transaction amount (a numeric value with a decimal point and two digits to the right -- can be negative, cannot be zero, cannot be blank)
       7. a display of the sum of the journal entries (must be zero)

    An example of a client-side version of this program can be found here:

    http://auckland.bauer.uh.edu/Students/parks/a3.htm

    You may use this program to provide data for a3.asp, a3v.aspx and a3c.aspx.

    The server side processes for the Journal Voucher:

    Once the data is received on the server side, the first thing you will need to determine is the number of journal entries the user entered on the Journal Voucher. You can do this many ways. One simple way is to count the number of values for the major account number submitted. Call this nunmber nr. It will be used frequently below.

    Three major tasks must be performed:

    1. Existence Tests

       Two things must be checked BEFORE posting begins:

       1. Initialize an error switch (i.e., create a variable, called something like error_count, and set it to zero)

       2. All account numbers submitted MUST exist in glmaster

          This test requires that you look up EACH submitted account number in glmaster to see if the account number exists in the table. This is a simple loop (i.e., 1 thru nr from above) through all the accounts submitted that reads the table to determine the account number existence. If any account IS NOT FOUND in the table, add 1 to error_count and display a detailed message that the account was not found.

       3. The Source Reference Number submitted MUST NOT BE in the je table. This is a lookup of the SRN in je. If one or more occurrences of the SRN are found in the table, add 1 to error_count and display a detailed message that the SRN was found.

       IF both the conditions above are satisfied (i.e., all the account numbers DO EXIST in glmaster AND the SRN DOES NOT in je, it is ok to proceed to the posting task. You will know the answer to this question: If error_count=0 then you proceed, otherwise, write a message to the user that errors were detected and exit the program.

    2. Posting the Journal Entries

       To get to this point, error_count should be zero. The posting process has two tasks that should be under "transaction control".

       So first, start a transaction, like this:

       cn.BeginTrans

       Then:

       1. Post the transaction amounts to the balances in the glmaster table

          For each journal entry (i.e., in a loop -- use 1 thru nr from above), perform an UPDATE of glmaster. Specifically:

          UPDATE glmaster set balance = balance + the transaction amount value for this journal entry WHERE
          major = the major value for this journal entry AND
          minor = the minor value for this journal entry AND
          sub1 = the sub1 value for this journal entry AND
          sub2 = the sub2 value for this journal entry

          After the UPDATE, check that the number of rows modified is equal to 1. If NOT add 1 to the error_count and display an error message that the specific journal entry failed in posting.

       2. Append the journal entry to the je table

          For each journal entry (i.e., in a loop -- use 1 thru nr from above), perform an UPDATE of glmaster. Specifically:

          INSERT INTO je (sourceref,srseq,jemajor,jeminor,jesub1,jesub2,jedesc,jeamount) VALUES
          ( the source reference number for the voucher,
          the source reference sequence number -- use the loop index 1,2,3... for this value,
          the major value for this journal entry,
          the minor value for this journal entry,
          the sub1 value for this journal entry,
          the sub2 value for this journal entry,
          the journal entry description value for this journal entry,
          the transaction amount for this journal entry)

          After the INSERT, check that the number of rows insert is equal to 1. If NOT, add 1 to the error_count and display an error message that the specific journal entry failed in the je table insert.

    3. Wrapup

       After the UPDATE loop and the INSERT loop have been completed you must decide whether or not to write the transactions to the database. The error-count variable is the key to this decision. Thus:

       if error_count=0
           cn.commitTrans
       else
           cn.rollbackTrans

    Write a message that tells the user that the data has been committed or NOT.

    and you are DONE!

  ---

  While this all may seem overwhelming, there are really only eight tasks -- but 24 programs:

  1. Create/Drop the database tables (3 times: glrestore.asp [this had already been written for you], glrestorec.aspx, glrestorec.aspx)
  2. ADD a row (3 times: a41.asp, a41c.aspx)
  3. Modify existing rows (3 times: a42.asp, a42v.aspx, a42c.aspx)
  4. Delete existing rows (3 times: a43.asp, a43v.aspx, a43c.aspx)

        Reports and queries:

  5. Full Trial Balance (3 times: a44.asp,a44v.aspx, a44c.aspx)
  6. GLmaster Query (3 times: a45.asp, a45v.aspx, a45c.aspx)
  7. JE Query (3 times: a46.asp, a46v.aspx, a46c.aspx)

  8. The Complex Posting Programs (3 times: a3.asp,a3v.aspx, a3c.aspx)

  The sequence in which your should perform these tasks is:

  1. glrestore.asp (get your database created using asp, this program is already written for you)
  2. a44.asp (a simple table dump that you need to see the glmaster table)
  3. a41.asp (a simple INSERT)
  4. a42.asp (a simple UPDATE)
  5. a43.asp (a simple DELETE)
  6. a45.asp (a simple Query)
  7. a3.asp (the complex posting program)
  8. a46.asp (a simple Query, but you can't see the je table data until after the a3.asp program is written)

  ALL THE ABOVE SHOULD BE FINISHED BEFORE THE MIDTERM
  (at a minimum the first six above, getting the posting program done before the midterm will be a challenge)

  Then All the VB.NET versions (now you know what to do and conversion to VB.NET will be quick):

  9. glrestorev.aspx
  10. a44v.aspx
  11. a41v.aspx
  12. a42v.aspx
  13. a43v.aspx
  14. a45v.aspx
  15. a3v.aspx
  16. a46v.aspx

  Then All the C# versions (this will be even quicker as the code structure for these is nearly identical to the VB.NET ones above):

  17. glrestorec.aspx
  18. a44c.aspx
  19. a41c.aspx
  20. a42c.aspx
  21. a43c.aspx
  22. a45c.aspx
  23. a3c.aspx
  24. a46c.aspx

  Don't Delay!

  ---

  A Cautiuonary note about testing equality with a zero:

  The sum of the transaction amount MUST be zero on the client side AND the sum of the bal;ances in glmaster should always be zero.

  Be very careful here. Do NOT attempt to add up the amounts and ask the question:

  Is the sum equal to zero?

  As you store the amounts in a real variable (i.e., floating point field); then do arithmentic (i.e., add them up); there may be round off error. Consider this example in javascript:

  
  Value 1
  Value 2
  Value 3
  Value 4

  Sum of 1 thru 4
  Is the sum Equal to Zero

  Clearly, if you add the numbers up you SHOULD Get zero. But you actually get:

  0.0000000000009094947017729282 (or so)

  This is NOT EXACTLY ZERO. So your test for zero to avoid this round off error with floating point arithmetic should be something like this:

  if ( sum > -0.000001 && sum < 0.0000001)

  in order to test that sum is equal to zero.

  ---

  Return to Parks' MIS homepage