• #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

    <pre>
    <code>
    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’
    BEGIN
    SELECT
    FROM CT_INVOICES i
    INNER JOIN CT_DRAWBATCHES b
    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’)
    )
    END
    ELSE
    SELECT

    FROM CT_INVOICES i
    INNER JOIN CT_DRAWBATCHES b
    ON i.DrawBatchId = b.DrawBatchId
    WHERE CostCode = @CostCode
    AND i.DrawBatchID < @DrawId
    AND i.GLCode = @GLCode
    </code>
    </pre>

  • 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:

    <pre>
    [
    {
    Header: “Hard Costs”,
    Titles:
    [
    {
    Title: “Site Costs”,
    CostCodes:
    [
    “CostCode”: “020101”,
    “CostCode”: “020102”
    ]
    SortOrder: 8
    },
    {
    Title: “Vertical Costs”,
    CostCodes:
    [
    “CostCode”: “030201”,
    “CostCode”: “030204”
    ]
    SortOrder: 9
    }

    ]
    }
    ]
    </pre>

  • 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:

    <pre>
    <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>
    </pre>
    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

    • 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
    {"cards":[{"_id":"4606a4fc6bd2c26f36000043","treeId":"3945628a7b5b8702e3000008","seq":627062,"position":0.5,"parentId":null,"content":"#Newmark"},{"_id":"4606a5ff6bd2c26f36000046","treeId":"3945628a7b5b8702e3000008","seq":627580,"position":1,"parentId":"4606a4fc6bd2c26f36000043","content":"#Construction Tracking"},{"_id":"48a59c0021163d73d7000024","treeId":"3945628a7b5b8702e3000008","seq":617546,"position":3,"parentId":"4606a5ff6bd2c26f36000046","content":"#Loan Draws Interactive HTML Report"},{"_id":"48a59cea21163d73d7000025","treeId":"3945628a7b5b8702e3000008","seq":617560,"position":1,"parentId":"48a59c0021163d73d7000024","content":"#SQL"},{"_id":"48a5ad4221163d73d7000026","treeId":"3945628a7b5b8702e3000008","seq":619728,"position":1,"parentId":"48a59cea21163d73d7000025","content":"##create sproc\nCreate 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"},{"_id":"48a5b6c921163d73d7000028","treeId":"3945628a7b5b8702e3000008","seq":619705,"position":2,"parentId":"48a59cea21163d73d7000025","content":"##add allocation columns to table\nAdd \"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\n "},{"_id":"48a909cb21163d73d7000031","treeId":"3945628a7b5b8702e3000008","seq":620689,"position":3,"parentId":"48a59cea21163d73d7000025","content":"##create sprocs for drill downs\n* ct_g_PreviousDrawInvoicesByCostCode\n\n<pre>\n<code>\nDECLARE\t\t@CostCode\tVARCHAR(50) = '020300',\n\t\t\t@DrawId\t\tINT\t\t\t= 158,\n\t\t\t@DrawType\tVARCHAR(50) = 'Total'\n\nDECLARE\t\t@GLCode\t\tVARCHAR(50)\nSELECT\t\t@GLCode =\t(SELECT Value FROM ct_Params WHERE ParamType = 'Draw Type' AND [Description] = @DrawType)\n\nIF @DrawType = 'Total'\nBEGIN\n\tSELECT\t\t*\n\tFROM\t\tCT_INVOICES\t\ti\n\tINNER JOIN\tCT_DRAWBATCHES\tb\n\tON\t\t\ti.DrawBatchId = b.DrawBatchId\n\tWHERE\t\tCostCode\t = @CostCode\t\t\n\tAND\t\t\ti.DrawBatchID < @DrawId\n\tAND\t\t\t(i.GLCode\t =\t(SELECT Value FROM ct_Params WHERE ParamType = 'Draw Type' AND [Description] = 'Loan') \n\t\tOR\t\t i.GLCode\t = (SELECT Value FROM ct_Params WHERE ParamType = 'Draw Type' AND [Description] = 'Bond') \n\t\t\t\t)\nEND\nELSE\n\tSELECT\t\t*\n\tFROM\t\tCT_INVOICES\t\ti\n\tINNER JOIN\tCT_DRAWBATCHES\tb\n\tON\t\t\ti.DrawBatchId = b.DrawBatchId\n\tWHERE\t\tCostCode\t = @CostCode\t\t\n\tAND\t\t\ti.DrawBatchID < @DrawId\n\tAND\t\t\ti.GLCode\t = @GLCode\n</code>\n</pre>\n"},{"_id":"48a5c2db21163d73d7000029","treeId":"3945628a7b5b8702e3000008","seq":617612,"position":2,"parentId":"48a59c0021163d73d7000024","content":"#VB"},{"_id":"48a5c31021163d73d700002a","treeId":"3945628a7b5b8702e3000008","seq":619765,"position":1,"parentId":"48a5c2db21163d73d7000029","content":"##create web method for report\n* create web method in wsConstructionTracking.vb \"getDrawLoans\" \n* which calls DrawBatch.GetLoanDraws\n* use returned data view to create a List(of Dictionary(of Object, Object) which should return JSON in the following format:\n\n<pre>\n[\n\t{\n\t\tHeader: \"Hard Costs\",\n\t\tTitles: \n\t\t[\n\t\t\t{\n\t\t\t\tTitle: \"Site Costs\",\n\t\t\t\tCostCodes:\n\t\t\t\t[\n\t\t\t\t\t\"CostCode\": \"020101\",\n\t\t\t\t\t\"CostCode\": \"020102\"\n\t\t\t\t]\n\t\t\t\tSortOrder: 8\n\t\t\t},\n\t\t\t{\n\t\t\t\tTitle: \"Vertical Costs\",\n\t\t\t\tCostCodes:\n\t\t\t\t[\n\t\t\t\t\t\"CostCode\": \"030201\",\n\t\t\t\t\t\"CostCode\": \"030204\"\n\t\t\t\t]\n\t\t\t\tSortOrder: 9\n\t\t\t}\n\t\t\t\n\t\t]\n\t}\n]\n</pre>"},{"_id":"48a7fbc621163d73d7000030","treeId":"3945628a7b5b8702e3000008","seq":620268,"position":2,"parentId":"48a5c2db21163d73d7000029","content":"##create web methods for drill downs\n* create web methods in wsConstructionTracking.vb \n* getDrawInvoicesByCostCode(TimeFrame, DrawType, DrawId)\n * 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\n\n* getDrawInvoicesByCostCode('previous', 'loan')\n* getDrawInvoicesByCostCode('previous', 'city')\n* getDrawInvoicesByCostCode('previous', 'total')\n\n* getDrawInvoicesByCostCode('current', 'loan')\n* getDrawInvoicesByCostCode('current', 'city')\n* getDrawInvoicesByCostCode('current', 'total')\n\n* getDrawInvoicesByCostCode('advanced', 'total')\n"},{"_id":"48a6011421163d73d700002b","treeId":"3945628a7b5b8702e3000008","seq":617745,"position":3,"parentId":"48a59c0021163d73d7000024","content":"#Front-End"},{"_id":"48a6019621163d73d700002c","treeId":"3945628a7b5b8702e3000008","seq":619701,"position":1,"parentId":"48a6011421163d73d700002b","content":"##create service\nset up angular service function to return structured JSON data object from getDrawLoans web method"},{"_id":"48a7b65321163d73d700002d","treeId":"3945628a7b5b8702e3000008","seq":619699,"position":2,"parentId":"48a6011421163d73d700002b","content":"##create interactive draw loans template\ncreate HTML template and angular controller to display nested arrays using example logic:\n<pre>\n`<table>\n\t<tbody>\n\t\t<tr ng-repeat-start=\"header in headers\">\n\t\t</tr>\n\t\t<tr ng-repeat-start=\"title in titles\">\n\t\t</tr>\n\t\t<tr ng-repeat=\"costcode in costcodes\" ng-repeat-end>\n\t\t</tr>\n\t</tbody>\n</table>`\n</pre>\nSo that it will be possible to group the nested arrays together.\n* At the top of the page, just below the sub-header, there should be a toolbar\n* with a select element which allows user to change the Draw Type (bond, loan)\n* as well as a select element which allows the user to change the Draw Number (with request date in parentheses)\n"},{"_id":"48a7cbcf21163d73d700002e","treeId":"3945628a7b5b8702e3000008","seq":619700,"position":3,"parentId":"48a6011421163d73d700002b","content":"##implement bootstrap-collapse functionality\n* the report will default to only show the Header rows (e.g., Land Costs, Acquisition Costs, Hard Costs...)\n* these Headers will be clickable to reveal the collection of titles which make them up.\n* in turn, each Title will be clickable to reveal the collection of Cost Codes which make them up.\n* there should be a button for expanding/collapsing all headers, and another for expanding/collapsing all titles\n"},{"_id":"48a7f12221163d73d700002f","treeId":"3945628a7b5b8702e3000008","seq":619930,"position":4,"parentId":"48a6011421163d73d700002b","content":"##drilldowns\n* drilldowns should only be clickable from the most granular view (the cost code row level)\n* drilldowns will appear in modals with a table of invoices related to that cost code / column coordinate\n* relevant columns: previous loan draws, previous city funds, total previous draws, current loan draw, current city draw, total current draws, total advanced\n* drilldown columns should reflect those in the draw request details PDF (Vendor, Code, Description, Amount) with an additional left-most column of Draw #\n* 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) \n* an additional right-most column should include links to the pdf documents associated with that contract"},{"_id":"607770ee5d935859a1000031","treeId":"3945628a7b5b8702e3000008","seq":5618557,"position":0.75,"parentId":null,"content":""}],"tree":{"_id":"3945628a7b5b8702e3000008","name":"Datex","publicUrl":"datex"}}