Your browser doesn't support the features required by impress.js, so you are presented with a simplified version of this presentation.

For the best experience please use the latest Chrome, Safari or Firefox browser.

#Construction Tracking

#Loan Draws Interactive HTML Report

create sproc

Create a modified version of the ct_g_LoanDraws procedure which returns all the same fields, but broken down by cost code (as it exists natively in the ct_DrawsBudget table), instead of grouped by title and header

add allocation columns to table

Add “BondAllocation”, “LoanAllocation”, and “EntityAllocation” columns to ct_DrawsBudget table, which will be used to indicate the amount allocated to be spent against from each source of funds, for each cost code

create sprocs for drill downs

  • ct_g_PreviousDrawInvoicesByCostCode

DECLARE        @CostCode    VARCHAR(50) = '020300',
            @DrawId        INT            = 158,
            @DrawType    VARCHAR(50) = 'Total'

DECLARE        @GLCode        VARCHAR(50)
SELECT        @GLCode =    (SELECT Value FROM ct_Params WHERE ParamType = 'Draw Type' AND [Description] = @DrawType)

IF @DrawType = 'Total'
    SELECT        *
    FROM        CT_INVOICES        i
    ON            i.DrawBatchId = b.DrawBatchId
    WHERE        CostCode      = @CostCode        
    AND            i.DrawBatchID < @DrawId
    AND            (i.GLCode      =    (SELECT Value FROM ct_Params WHERE ParamType = 'Draw Type' AND [Description] = 'Loan') 
        OR         i.GLCode      = (SELECT Value FROM ct_Params WHERE ParamType = 'Draw Type' AND [Description] = 'Bond') 
    SELECT        *
    FROM        CT_INVOICES        i
    ON            i.DrawBatchId = b.DrawBatchId
    WHERE        CostCode      = @CostCode        
    AND            i.DrawBatchID < @DrawId
    AND            i.GLCode      = @GLCode

create web method for report

  • create web method in wsConstructionTracking.vb “getDrawLoans”
  • which calls DrawBatch.GetLoanDraws
  • use returned data view to create a List(of Dictionary(of Object, Object) which should return JSON in the following format:
        Header: "Hard Costs",
                Title: "Site Costs",
                    "CostCode": "020101",
                    "CostCode": "020102"
                SortOrder: 8
                Title: "Vertical Costs",
                    "CostCode": "030201",
                    "CostCode": "030204"
                SortOrder: 9


create web methods for drill downs

  • create web methods in wsConstructionTracking.vb
  • getDrawInvoicesByCostCode(TimeFrame, DrawType, DrawId)

    • each unique combination of parameters will run through a Select Case to determine which Shared Function in the DrawBatch class to call (because unique stored procedures will be necessary to do this more easily; since this is a one-off framework, it will be better to copy and paste 7 different sprocs than to create a dynamic sproc
  • getDrawInvoicesByCostCode(‘previous’, ‘loan’)

  • getDrawInvoicesByCostCode(‘previous’, ‘city’)
  • getDrawInvoicesByCostCode(‘previous’, ‘total’)

  • getDrawInvoicesByCostCode(‘current’, ‘loan’)

  • getDrawInvoicesByCostCode(‘current’, ‘city’)
  • getDrawInvoicesByCostCode(‘current’, ‘total’)

  • getDrawInvoicesByCostCode(‘advanced’, ‘total’)

create service

set up angular service function to return structured JSON data object from getDrawLoans web method

create interactive draw loans template

create HTML template and angular controller to display nested arrays using example logic:

<table> <tbody> <tr ng-repeat-start="header in headers"> </tr> <tr ng-repeat-start="title in titles"> </tr> <tr ng-repeat="costcode in costcodes" ng-repeat-end> </tr> </tbody> </table>

So that it will be possible to group the nested arrays together.

  • At the top of the page, just below the sub-header, there should be a toolbar
  • with a select element which allows user to change the Draw Type (bond, loan)
  • as well as a select element which allows the user to change the Draw Number (with request date in parentheses)

implement bootstrap-collapse functionality

  • the report will default to only show the Header rows (e.g., Land Costs, Acquisition Costs, Hard Costs…)
  • these Headers will be clickable to reveal the collection of titles which make them up.
  • in turn, each Title will be clickable to reveal the collection of Cost Codes which make them up.
  • there should be a button for expanding/collapsing all headers, and another for expanding/collapsing all titles


  • drilldowns should only be clickable from the most granular view (the cost code row level)
  • drilldowns will appear in modals with a table of invoices related to that cost code / column coordinate
  • relevant columns: previous loan draws, previous city funds, total previous draws, current loan draw, current city draw, total current draws, total advanced
  • drilldown columns should reflect those in the draw request details PDF (Vendor, Code, Description, Amount) with an additional left-most column of Draw #
  • which should be clickable and change the modal view to display ALL the invoices in relevant to the clicked draw #, with a breadcrumb OR “back” button (a left arrow near the top) to return to the previous view (leaning towards the back button, since the breadcrumb shows you your path of navigation, but conceptually, a breadcrumb relationship is meant to show inheritance of conceptual relationship, whereas these are both views of invoices but with different filters applied (invoices filtered by cost code to invoices filtered by draw)
  • an additional right-most column should include links to the pdf documents associated with that contract