Regional Wastewater Treatment Planning

Shawn Goulet

Cape Cod Commission

RESTful API & Front-end Development

Ingredients

Recipes

Your Plate

GIS Analysts

  • Front-end
  • UI/UX
  • Back-end
  • DB Management

Nicholas Duggan : The Spatial Blog

41studio : Blog

Geospatial Developers

Cape Cod Commission

Regional land use planning agency / department of Barnstable County (Cape Cod)

Team of 3 w/ Environmental, GIS, Data Analytics experience

Overview

  1. Introduction
  2. Back-end v1 : PHP Laravel Lumen
  3. FIM front-end : Node.js + Vue.js
  4. Back-end v2 : Node.js & the path fwd

Intro

Historically ESRI + Windows rdbms

change =       +                      what benefits?

Intro

a     interest in ASP.NET

2017 Stack Overflow Developer Survey

Intro

ArcGIS API 3.2

require([
  "esri/map", "esri/dijit/HomeButton", "esri/dijit/Popup", "esri/dijit/PopupTemplate", "esri/dijit/BasemapGallery", "esri/arcgis/utils", "esri/dijit/OverviewMap", "esri/layers/ArcGISTiledMapServiceLayer", "dojo/parser", "esri/layers/FeatureLayer", "esri/dijit/Geocoder", "esri/tasks/query", "esri/tasks/QueryTask", "esri/geometry/Extent", "esri/geometry/Point", "esri/geometry/Polygon", "esri/SpatialReference", "esri/graphic", "esri/graphicsUtils", "esri/symbols/PictureMarkerSymbol", "esri/symbols/SimpleFillSymbol", "esri/symbols/SimpleLineSymbol", "esri/renderers/SimpleRenderer", "esri/lang", "esri/Color", "dojo/dom", "dojo/dom-class", "dojo/dom-construct", "dojo/dom-style", "dojo/domReady!", "dijit/layout/BorderContainer", "dijit/layout/ContentPane", "dijit/TitlePane", "esri/geometry/geodesicUtils", "esri/units"
],

function (Map, HomeButton, Popup, PopupTemplate, BasemapGallery, arcgisUtils, OverviewMap, ArcGISTiledMapServiceLayer, parser, FeatureLayer, Geocoder, Query, QueryTask, Extent, Point, Polygon, SpatialReference, Graphic, GraphicsUtils, PictureMarkerSymbol, SimpleFillSymbol, SimpleLineSymbol, SimpleRenderer, esriLang, Color, dom, domClass, domConstruct, domStyle, geodesicUtils, units)

// BEGIN ESRI WIDGETS
// BUILD THE POPUP
var fill = new SimpleFillSymbol("solid", null, new Color("darkgray"));
var popup = new Popup({fillSymbol: fill,titleInBody: false}, domConstruct.create("div"));
domClass.add(popup.domNode, "dark");

// SET THE INITIAL EXTENT
var initialExtent = new esri.geometry.Extent({"xmin": -7980970.14,"ymin": 5033003.02,"xmax": -7705796.84,"ymax": 5216451.89,"spatialReference": {"wkid": 102100}});

// BUILD THE BASEMAP GALLERY
var basemapGallery = new BasemapGallery({showArcGISBasemaps: true, map: map}, "basemapGallery");
basemapGallery.startup();
basemapGallery.on("error", function (msg) {console.log("basemap gallery error:  ", msg);});

Intro

Transition to js-based front-end

function buildScenarioPolygons(thisScenario, i) {
            var polyGL = new esri.layers.GraphicsLayer();
            var areaGL = new esri.layers.GraphicsLayer();
            var pointGL = new esri.layers.GraphicsLayer();
            polyGLs.push(polyGL);
            areaGLs.push(areaGL);
            pointGLs.push(pointGL);
            var url = "http://www.cch2o.org/TBL/208-viewer-php-build.php?id="

            $.ajax({
                type: "GET",
                url: url + thisScenario,
                dataType: "json",
                success: function(json) {

                    // console.log(json);         //*****UNCOMMENT ON FOR DEVELOPMENT*****

                    var xList = [];
                    var yList = [];

                    var scenarioID = json.ScenarioID;
                    var CreateDate = json.CreateDate;
                    var CreatedBy = json.CreatedBy;
                    var Description = json.ScenarioDescription;
                    var ScenarioName = json.ScenarioName;
                    var Notes = json.ScenarioNotes;
                    var AreaName = json.AreaName;
                    var AreaType = json.AreaType;
                    var TreatmentId = json.TreatmentID
                    var Treatments = json.Treatments;
                    var sr = {
                        wkid: 102100,
                        latestWkid: 3857
                    };

Ajax call to PHP               Data

Intro

<? header('Access-Control-Allow-Origin: *');

// USE CONNECTION SCRIPT TO ACCESS MSSQL DB
include('208-viewer-connection.php');

// SET 'id' TO 'id' USING ACCESSING THE HTTP GET 'id' VARIABLE PASSED FROM FRONT
$id = $_GET['id'];

// ESTABLISH 'obj' variable
$obj;

// BUILD SQL QUERY TO SELECT BY 'ScenarioID' FROM 'Scenario_Wiz' TABLE, INJECT INTO r1 (mssql_query)
$q1 = "select * from [CapeCodMA].[Scenario_Wiz] where ScenarioID = " . $id;
$r1 = mssql_query($q1);

// BUILD SQL QUERY TO SELECT BY 'ScenarioID' FROM 'Treatment_Wiz' TABLE, INJECT INTO r2 (mssql_query) 
$q2 = "select * from [CapeCodMA].[Treatment_Wiz] where ScenarioID = " . $id;
$r2 = mssql_query($q2);

// SET 'scenRow' variable = object returned by 'r1'
$scenRow = mssql_fetch_object($r1);

// SET CHILDREN OF THE 'obj' VARIABLE EQUAL TO THEIR DATA RETURNED BY ROW FROM 'r1' (Scenario_Wiz)
$obj->ScenarioID = $scenRow->ScenarioID;
$obj->CreatedBy = $scenRow->CreatedBy;
$obj->CreateDate = $scenRow->CreateDate;
$obj->ScenarioName = $scenRow->ScenarioName;
$obj->ScenarioDescription = $scenRow->ScenarioDescription;
$obj->ScenarioNotes = $scenRow->ScenarioNotes;
$obj->AreaType = $scenRow->AreaType;
$obj->AreaName = $scenRow->AreaName;

// FOR POLYGON SHAPES CLEAN UP MSSQL SQL SPATIAL FORMATTING
$scenPoly = str_replace('POLYGON ((', '', $scenRow->POLY_STRING);
$scenPoly = str_replace('))', '', $scenPoly);
$scenPoly = explode(', ', $scenPoly);

// FOR EACH POLYGON, DEFINE THE x, y, node (x,y's) & areaGeo (array of nodes)
foreach ($scenPoly as $Poly){
	$x = strstr($Poly, " ", true);
	$y = strstr($Poly, " ");
	$node = [$x,$y];
	$areaGeo[] = $node;
}

// FOR EACH POLYGON, DEFINE THE x, y, point (x,y's & constructPoint (point technologies)
foreach ($scenPoint as $Point){
	$x = strstr($Point, " ", true);
	$y = strstr($Point, " ");
	$point = [$x,$y];
	$constructPoint[] = $point;
}

// SET THE 'AreaPoly' CHILD OF THE 'obj' VARIABLE = 'areaGeo' FROM ABOVE
$obj->AreaPoly = $areaGeo;

// ESTABLISH THE ALL TREATMENTS-ENCOMPASSING 'treatmentsAll' VARIABLE
$treatmentsAll;

// WHILE FETCHING OBJECTS FROM 'r2' (Treatment_Wiz), SET $treatment VARIABLE & CHILDREN CORRESPONDING TO MSSQL DATA
while ($treatRow = mssql_fetch_object($r2)) {

	$treatment->TreatmentID = $treatRow->TreatmentID;
	$treatment->TreatmentTypeId = $treatRow ->TreatmentType_ID; //different
	$treatment->TreatmentType = $treatRow->TreatmentType_Name;
	$treatment->TreatmentParcels = $treatRow->Treatment_Parcels;
	$treatment->TreatmentClass = $treatRow->Treatment_Class;
	$treatment->TreatmentArea = $treatRow->Treatment_Acreage;
	$treatment->Custom = $treatRow->Custom_POLY;
	$treatment->Point = $treatRow->POLY_STRING;

        // CLEAN POINT TREATMENT TECHNOLOGIES SPATIAL FIELD
	if ($treatRow->TreatmentType_ID == 106 OR $treatRow->TreatmentType_ID == 107 OR $treatRow->TreatmentType_ID == 108 OR $treatRow->TreatmentType_ID == 109 OR $treatRow->TreatmentType_ID == 110 OR $treatRow->TreatmentType_ID == 201 OR $treatRow->TreatmentType_ID == 202 OR $treatRow->TreatmentType_ID == 203 OR $treatRow->TreatmentType_ID == 500 OR $treatRow->TreatmentType_ID == 501 OR $treatRow->TreatmentType_ID == 502 OR $treatRow->TreatmentType_ID == 504){
		$treatment->Point = str_replace('POINT(', '', $treatment->Point);
		$treatment->Point = str_replace(', 4326)', '', $treatment->Point);
		$treatment->Point = str_replace(', 3857)', '', $treatment->Point);
		$treatment->Point = explode(', ', $treatment->Point);
		$geometry;
	} else {
		$treatment->Point = NULL;
		$geometry;
	}

        // CLEAN CUSTOM POLYGON TREATMENT TECHNOLOGIES SPATIAL FIELD
	if ($treatRow->Custom_POLY == 1){
		$poly = str_replace('POLYGON ((', '', $treatRow->POLY_STRING);
		$poly = str_replace('POLYGON((', '', $treatRow->POLY_STRING);
		$poly = str_replace('))', '', $poly);
		$geometry;
                
                // CLEAN SCENARIOS > scenarioID 95
		if($scenRow->ScenarioID > 95){
			if (count(explode(', ',$poly))>2){
				$poly = explode(', ', $poly); //for regular version	pre scenario 244
			}else{
				$poly = explode(',', $poly);//after scenario 244
			}

			foreach($poly as $Poly){
				$x = strstr($Poly, " ", true);
				$y = strstr($Poly, " ");
				$node = [$x,$y];
				$geometry[] = $node;
			}
		}else{
			$poly = explode(',',$poly); //for all comma version
			for ($i = 0; $i < count($poly); $i+=2){
				$x  = $poly[$i];
				$y = $poly[$i+1];
				$node = [$x,$y];
				$geometry[] = $node;
			}
		}
                // SET THE '$treatment' VARIABLE TO POLYGONS & POINTS, PUT INTO 'treatmentsAll' ARRAY
		$treatment->Geometry = $geometry;
		$treatment->Point = $point;
		$treatmentsAll[] = $treatment;
		unset($treatment);
		unset($geometry);
	}else{
		$treatmentsAll[] = $treatment;
		unset($treatment);
	}


}

// SET THE 'obj' VARIABLE = 'treatmentsALL, ECHO AS JSON TO RETURN BACK TO THE FRONT
$obj->Treatments = $treatmentsAll;

echo json_encode($obj);

?>

Pure PHP script to get the data

Intro

MSSQL Spatial

Intro

208 Viewer (208.capecodcommission.org)

Intro

Back-end v1:

PHP Laravel Lumen

Beefing up scaffolding : Transition to PHP Laravel Lumen

  1. We needed assistance
  2. We hired a contractor

  3. They had to be on the approved State of MA vendor list

  4. We needed some face-to-face meetings

  5. An extremely small local dev company bid on the rfp

  6. They were PHP devs

Back-end v1

CCC API v1 :

Project Structure

Back-end v1

routes.php

<?php

use Illuminate\Http\Request;
use Illuminate\Http\Response;
use GuzzleHttp\Client as Client;
use Illuminate\Contracts\Cache\Repository;

$app->get('/', function() use ($app) {
  return redirect('docs/index.html');
});

// Welcome Route
$app->group(['prefix' => 'v1', 'middleware' => 'auth'], function() use ($app) {

  $app->get('/', function (Request $request) use ($app) {
    $welcome = new \stdClass;
    $welcome->msg = "Welcome to CCC API";
    $welcome->user = $request->user();
    return response()->json($welcome);
  });

});

// v1/watershed
$app->group(['prefix' => 'v1/watershed', 'middleware' => 'auth'], function() use ($app) {

  $app->get('/', function (Request $request) use ($app) {
    $welcome = new \stdClass;
    $welcome->msg = "Watershed API";
    return response()->json($welcome);
  });

});

// v1/watershed/mvp
$app->group(['prefix' => 'v1/watershed/mvp', 'middleware' => 'auth'], function() use ($app) {

  $app->get('/', function (Request $request) use ($app) {
    $welcome = new \stdClass;
    $welcome->msg = "MVP API";
    return response()->json($welcome);
  });

});

// v1/watershed/mvp/fim
$app->group(['prefix' => 'v1/watershed/mvp/fim', 'middleware' => 'auth'], function() use ($app) {

  $app->get('/', function (Request $request) use ($app) {
    $welcome = new \stdClass;
    $welcome->msg = "Financial Impact Model API";
    return response()->json($welcome);
  });

  $client = new Client([
    'base_uri' => env('FIM_API')
  ]);

  $app->get('getFinanceOptions', function () use ($app, $client) {

    if ( Cache::has('getFinanceOptions') ) {
      return Cache::get('getFinanceOptions');
    }

    $response = $client->get('getFinanceOptions');

    Cache::put('getFinanceOptions', json_decode($response->getBody(), true), env('CACHE_TIME'));

    return json_decode($response->getBody(), true);

  });

  $app->post('calculateInflatedCost', function (Request $request) use ($app, $client) {

    $response = $client->post('calculateInflatedCost', [
      'form_params' => $request->all()
    ]);

    return json_decode($response->getBody(), true);

  });

  $app->get('getScenarios', function () use ($app, $client) {

    $response = $client->get('getScenarios');
    return json_decode($response->getBody(), true);

  });

  $app->get('getScenario/{id}', function ($id) use ($app, $client) {

    $response = $client->get('getScenario/' . $id);
    return json_decode($response->getBody(), true);

  });

  $app->get('getTreatment/{id}', function ($id) use ($app, $client) {

    $response = $client->get('getTreatment/' . $id);
    return json_decode($response->getBody(), true);

  });

  $app->post('getUpdatedTreatmentTimeline', function (Request $request) use ($app, $client) {

    $response = $client->post('getUpdatedTreatmentTimeline', [
      'form_params' => $request->all()
    ]);

    return json_decode($response->getBody(), true);

  });

  $app->post('sumOfAnnualCapitalTotalsForTreatment', function (Request $request) use ($app, $client) {

    $response = $client->post('sumOfAnnualCapitalTotalsForTreatment', [
      'form_params' => $request->all()
    ]);

    return json_decode($response->getBody(), true);

  });

  $app->post('annualCapitalTotalsForTreatment', function (Request $request) use ($app, $client) {

    $response = $client->post('annualCapitalTotalsForTreatment', [
      'form_params' => $request->all()
    ]);

    return json_decode($response->getBody(), true);

  });

  $app->post('costTypeAnnualCapitalTotalsPerTownForTreatment', function (Request $request) use ($app, $client) {

    $response = $client->post('costTypeAnnualCapitalTotalsPerTownForTreatment', [
      'form_params' => $request->all()
    ]);

    return json_decode($response->getBody(), true);

  });

  $app->post('omAnnualCapitalTotalsPerTownForTreatment', function (Request $request) use ($app, $client) {

    $response = $client->post('omAnnualCapitalTotalsPerTownForTreatment', [
      'form_params' => $request->all()
    ]);

    return json_decode($response->getBody(), true);

  });

  $app->post('calculateAnnualCostTypesTotalForTreatment', function (Request $request) use ($app, $client) {

    $response = $client->post('calculateAnnualCostTypesTotalForTreatment', [
      'json' => $request->all()
    ]);

    return json_decode($response->getBody(), true);

  });

});

// v1/watershed/mvp/embayments
$app->group(['prefix' => 'v1/watershed/mvp/embayments', 'middleware' => 'auth'], function() use ($app) {

  $client = new Client([
    'base_uri' => 'http://localhost' //env('EMB_API')
  ]);

  $app->get('/', function (Request $request) use ($app) {
    return [];
  });

  $app->get('/{id}', function ($id) use ($app, $client) {
    return [];
  });

  $app->get('/{id}/subembeyments', function ($id) use ($app, $client) {
    return [];
  });

  $app->get('/{e_id}/subembeyment/{s_id}', function ($e_id, $s_id) use ($app, $client) {
    return [];
  });

});

// v1/techmatrix
$app->group(['prefix' => 'v1/techmatrix', 'middleware' => 'auth'], function() use ($app) {

  $client = new Client([
    'base_uri' => env('TM_API')
  ]);

  $app->get('/', function (Request $request) use ($app) {
    return [];
  });

  $app->get('/{id}', function ($id) use ($app, $client) {
    $response = $client->get('getTechnology/' . $id);
    return json_decode($response->getBody(), true);
  });

});

Back-end v1

Tech Matrix API:

Project Structure

Back-end v1

<?php

namespace App\Repositories;

use DB;
use App\TechnologyMatrix;

class TechMatrixRepository
{
  /**
   * Get treatment tech matrix
   *
   * @param  Int  $treatmentId
   * @return Collection
   */
  public static function forTreatment($treatmentId)
  {
    $treatments = DB::table('Tech_Matrix.dbo.Technology_Matrix')
      ->select(
          'TM_ID',
          'Technology ID',
          'Icon',
          'Technology_Strategy',
          'Technology_Type',
          'Tot_ReplaceUpgrade_Cost',
          'Avg_Life_Cycle_Cost',
          'Adj_OM_Cost_Avg',
          'Adj_Proj_Cost_Avg',


          // Traditional "Capital"
          'Capital_Slope',
          'Capital_Intercept',
          'Capital_Adj_Factor',
          'Proj_Cost_Adj_Factor',
          'Avg_Acres',

          // Traditional "OM"
          'OM_Slope',
          'OM_Intercept',
          'OM_Adj_Factor',

          // Trad OM & Capital
          'High_Lvl_Treatment_Factor',

          // Traditional "Replacement"
          'Replacement_Cost',
          'Replacement_Project_Cost',
          'Useful_Life_Yrs',

          // Help determine if the technology is shared by parcels
          'Type_Of_Cost_Spread'


        )
      ->where('Technology ID', '=', $treatmentId)->first();

    $tech = [
      'tm_id'                   => $treatments->{'TM_ID'},
      'technologyID'            => intval($treatments->{'Technology ID'}),
      'technologyType'          => $treatments->{'Technology_Type'},
      'icon'                    => trim($treatments->{'Icon'}),
      'technologyStrategy'      => $treatments->{'Technology_Strategy'},
      'totalReplaceUpgradeCost' => floatval($treatments->{'Tot_ReplaceUpgrade_Cost'}),
      'avgLiveCycleCost'        => floatval($treatments->{'Avg_Life_Cycle_Cost'}),
      'adjOMCostAvg'            => floatval($treatments->{'Adj_OM_Cost_Avg'}),
      'adjProjCostAvg'          => floatval($treatments->{'Adj_Proj_Cost_Avg'}),

      'capitalSlope'            => floatval($treatments->{'Capital_Slope'}),
      'capitalIntercept'        => floatval($treatments->{'Capital_Intercept'}),
      'capitalAdjFactor'        => floatval($treatments->{'Capital_Adj_Factor'}),
      'projectCostAdjFactor'    => floatval($treatments->{'Proj_Cost_Adj_Factor'}),
      'avgAcres'                => floatval($treatments->{'Avg_Acres'}),

      'omSlope'                 => floatval($treatments->{'OM_Slope'}),
      'omIntercept'             => floatval($treatments->{'OM_Intercept'}),
      'omAdjFactor'             => floatval($treatments->{'OM_Adj_Factor'}),

      'highLevelTreatmentFactor'=> floatval($treatments->{'High_Lvl_Treatment_Factor'}),

      'replacementCost'         => floatval($treatments->{'Replacement_Cost'}),
      'replacementProjectCost'  => floatval($treatments->{'Replacement_Project_Cost'}),
      'usefulLifeInYears'       => intval($treatments->{'Useful_Life_Yrs'}),

      'typeOfCostSpread'     => intval($treatments->{'Type_Of_Cost_Spread'}),



    ];

    return new TechnologyMatrix($tech);
  }
}

TechMatrixRepository.php

Back-end v1

TechnologyMatrix.php

<?php

namespace App;


class TechnologyMatrix implements \JsonSerializable
{
  public $treatmenttm_id;
  public $technologyID;
  public $technologyType;
  public $icon;
  public $technologyStrategy;
  public $totalReplaceUpgradeCost;
  public $avgLiveCycleCost;
  public $adjOMCostAvg;
  public $adjProjCostAvg;

  public $capitalSlope;
  public $capitalIntercept;
  public $capitalAdjFactor;
  public $projectCostAdjFactor;
  public $avgAcres;

  public $omSlope;
  public $omIntercept;
  public $omAdjFactor;

  public $highLevelTreatmentFactor;
  public $usefulLifeInYears;
  public $replacementCost;
  public $replacementProjectCost;
  public $typeOfCostSpread;

  public function __construct(Array $data) {
    $this->treatmenttm_id           = $data['tm_id'];
    $this->technologyID             = $data['technologyID'];
    $this->technologyType           = $data['technologyType'];
    $this->icon                     = $data['icon'];
    $this->technologyStrategy       = $data['technologyStrategy'];
    $this->totalReplaceUpgradeCost  = $data['totalReplaceUpgradeCost'];
    $this->avgLiveCycleCost         = $data['avgLiveCycleCost'];
    $this->adjOMCostAvg             = $data['adjOMCostAvg'];
    $this->adjProjCostAvg           = $data['adjProjCostAvg'];
    $this->capitalSlope             = $data['capitalSlope'];
    $this->capitalIntercept         = $data['capitalIntercept'];

    $this->capitalSlope             = $data['capitalSlope'];
    $this->capitalIntercept         = $data['capitalIntercept'];
    $this->capitalAdjFactor         = $data['capitalAdjFactor'];
    $this->projectCostAdjFactor     = $data['projectCostAdjFactor'];
    $this->avgAcres                 = $data['avgAcres'];

    $this->omSlope                  = $data['omSlope'];
    $this->omIntercept              = $data['omIntercept'];
    $this->omAdjFactor              = $data['omAdjFactor'];

    $this->highLevelTreatmentFactor = $data['highLevelTreatmentFactor'];
    $this->usefulLifeInYears        = $data['usefulLifeInYears'];
    $this->replacementCost          = $data['replacementCost'];
    $this->replacementProjectCost   = $data['replacementProjectCost'];
    $this->typeOfCostSpread         = $data['typeOfCostSpread'];
  }

  public function jsonSerialize()
  {
    return $this;
  }

} // END class TechnologyMatrix

Back-end v1

FIM API:

Project Structure

getScenarios &

getTreatment

Back-end v1

CorsMiddleware.php

Back-end v1

<?php

namespace App\Http\Middleware;

use Closure;

class CorsMiddleware
{
    /**
     * Handle an incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \Closure  $next
     * @return mixed
     */
    public function handle($request, Closure $next)
    {
        $headers = [
            'Access-Control-Allow-Origin'      => '*',
            'Access-Control-Allow-Methods'     => 'POST, GET, OPTIONS, PUT, DELETE',
            'Access-Control-Allow-Credentials' => 'true',
            'Access-Control-Max-Age'           => '86400',
            'Access-Control-Allow-Headers'     => 'Content-Type, Authorization, X-Requested-With'
        ];

        if ($request->isMethod('OPTIONS'))
        {
            return response()->json('{"method":"OPTIONS"}', 200, $headers);
        }

        $response = $next($request);
        foreach($headers as $key => $value)
        {
            $response->header($key, $value);
        }

        return $response;
    }
}

routes.php

<?php

use Illuminate\Http\Request;
use App\Repositories\FinanceOptionsRepository;
use App\Classes\InflatableCost;

// Splash Route
$app->get('/', function () use ($app) {

  $splash = "Cape Cod Commission - Financial Model API";
  return $splash;

});


/**
 * @return JSON Array
 */
// ***** SET THE 'getScenarios' ROUTE, POINT IT TO 'ScenarioTreatmentsController' *****
$app->get('getScenarios', 'ScenarioTreatmentsController@getScenarios');

/**
 * @param Int scenarioId
 * @return JSON Array
 */
$app->get('getScenario/{id}', 'ScenarioTreatmentsController@get');

/**
 * @param Int treatmentId
 * @return JSON Object
 */
// ***** SET THE 'getTreatment' ROUTE, POINT IT TO 'ScenarioTreatmentsController' *****
$app->get('getTreatment/{id}', 'ScenarioTreatmentsController@getTreatment');

/**
 * @param Int treatmentId
 * @param Int relativeStartYear
 * @param Int duration
 * @return JSON Object
 */
$app->post('getUpdatedTreatmentTimeline', 'ScenarioTreatmentsController@getUpdateTreatment');

/**
 * @param Int treatmentId
 * @param Float costToFinance
 * @param Int financeOption
 * @param Int financeDuration
 * @param Int relativeStartYear
 * @param Float principalForgivenessRate
 * @return Array
 */
$app->post('sumOfAnnualCapitalTotalsForTreatment', 'CostWithFinanceController@calculateSumOfAnnualCapitalTotals');
$app->post('annualCapitalTotalsForTreatment', 'CostWithFinanceController@calculateAnnualCapitalTotals');

/**
 * @param Int treatmentId
 * @param Float costToFinance
 * @param Int financeOption
 * @param Int financeDuration
 * @param Int relativeStartYear
 * @param Float principalForgivenessRate
 * @param String costTypeName
 * @return Array
 */
$app->post('costTypeAnnualCapitalTotalsPerTownForTreatment', 'CostWithFinanceController@calculateAnnualCapitalTotalsPerTown');
// called just for OM Cost
$app->post('omAnnualCapitalTotalsPerTownForTreatment', 'CostWithFinanceController@calculateOMAnnualCapitalTotalsPerTown');

/**
 * This post requires HEADER Content-Type to be application/json
 * @param JSON body
 * @return JSON Array
 */
$app->post('calculateAnnualCostTypesTotalForTreatment', 'CostWithFinanceController@annualTotalsToBeAssignToDevParcelsWithWater');

/**
 * Get finance options
 * @return JSON Array
 */
$app->get('getFinanceOptions/', function () use ($app) {

  $repository = new FinanceOptionsRepository();

  return $repository->options()->toJson();

});


/**
 * calculate inflated cost
 * @return FLOAT
 */
$app->post('calculateInflatedCost/', function (Request $request) use ($app) {

  $cost               = (float)$request->input('cost');
  $relativeStartYear  = (int)$request->input('relativeStartYear');

  $inflatedCost = new InflatableCost($cost, $relativeStartYear);
  return $inflatedCost->getInflatedCost();

});

getScenarios & getTreatment

Back-end v1

ScenarioTreatmentsController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Repositories\TreatmentTechRepository;

/**
 * ScenarioTreatmentsController Class
 *
 * @param TreatmentTechRepository $repository
 */
class ScenarioTreatmentsController extends Controller
{
  protected $treatmentTechRepository;
  /**
   * Create a new controller instance.
   *
   * @return void
   */
  public function __construct(TreatmentTechRepository $repository)
  {
      $this->treatmentTechRepository = $repository;
  }

  /**
   * Retrieve Scenarios
   *
   * @param int $id (Scenario ID)
   * @return JSON Array
   */
// ***** INITIALIZE THE 'getScenarios' PUBLIC FUNCTION, RETURN THE 'getScenarios' PUBLIC FUNCTION FROM THE
// 'treatmentTechRepository' *****
  public function getScenarios()
  {
    return $this->treatmentTechRepository->getScenarios();
  }


  /**
   * Retrieve Treatment Techs of the Scenario
   *
   * @param int $id (Scenario ID)
   * @return JSON
   */
  public function get($id)
  {
    if ($id) {
      return $this->treatmentTechRepository->forScenario( (int)$id );
    }

    return [];
  }

  /**
   * Retrieve Specific Treatment Tech
   *
   * @param int $id (Scenario ID)
   * @return JSON
   */
// ***** INITIALIZE THE 'getTreatment' PUBLIC FUNCTION, WHICH RETURNS FROM THE 'treatmentTechRepository', the
// 'getTreatmentTech' (by $id) *****
  public function getTreatment($id = false)
  {
    return $this->treatmentTechRepository->getTreatmentTech( (int) $id );
  }

  /**
   * Retrieve Specific Treatment Tech with updated parameters
   *
   * @param int $id (Scenario ID)
   * @return JSON
   */
  public function getUpdateTreatment(Request $request)
  {
    $params = new \stdClass;

    $rules = [
      'treatmentId'       => 'required|numeric',
      'relativeStartYear' => 'required|numeric|min:0',
      'duration'          => 'required|numeric|min:0',
    ];

    $validator = $this->validate( $request, $rules );

    $params->treatmentId        = (int)$request->input('treatmentId');
    $params->relativeStartYear  = (int)$request->input('relativeStartYear');
    $params->duration           = (int)$request->input('duration');

    return $this->treatmentTechRepository->getUpdatedTreatmentTech( $params->treatmentId,
                                                                    $params->relativeStartYear,
                                                                    $params->duration );
  }



}

Back-end v1

getScenarios & getTreatment

TreatmentTechRepository.php

<?php

namespace App\Repositories;

use GuzzleHttp\Client as Client;
use DB;
use App\Classes\TreatmentTech;
use App\Classes\ScenarioIds;
use App\Classes\TownParcelData;
use App\Classes\TreatmentTechTypes\TreatmentType;
use App\Classes\TreatmentTechTypes\Traditional;
use App\Classes\TreatmentTechTypes\NonTraditional;

/**
 * TreatmentTechRepository Class
 */
class TreatmentTechRepository
{

  protected $client;
  protected $techTypeMap;
  protected $classPath;

  public function __construct()
  {
    $this->client = new Client([
      'base_uri' => env('TM_API')
    ]);


    $this->classPath = "App\Classes\TreatmentTechTypes\\";

    $this->techTypeMap = [
      'Collection Systems'                              => 'NotCalculated',
      'Effluent Disposal'                               => 'NotCalculated',
      'Green Infrastructure'                            => 'NonTraditional',
      'Innovative and Resource-Management Technologies' => 'NonTraditional',
      'Non-Structural Technologies'                     => 'NonTraditional',
      'On-Site Treatment Systems'                       => 'NonTraditional',
      'Solids Processing'                               => 'NotCalculated',
      'System Alterations'                              => 'NonTraditional',
      'Treatment Systems'                               => 'Traditional',
      'Waste Reduction Toilets'                         => 'NonTraditional'
    ];
  }

// ***** 'getscenarioDB' FUNCTION INJECTS SQL TO JOIN 2 TABLES (TECHS & SCENARIOS) ON THE UNIQUE SCENARIO ID
  private function getscenarioDB()
  {
    return DB::table('CapeCodMA.Scenario_Wiz')
      ->join('CapeCodMA.Treatment_Wiz', 'CapeCodMA.Scenario_Wiz.ScenarioID', '=', 'CapeCodMA.Treatment_Wiz.ScenarioID')
      ->select(
          'CapeCodMA.Scenario_Wiz.ScenarioID'
        );      
  }

  /**
   * Return Default TreatmentTech DB Query
   *
   * @return DB
   */
  private function getDBQuery()
  {
    return DB::table('CapeCodMA.Scenario_Wiz')
      ->join('CapeCodMA.Treatment_Wiz', 'CapeCodMA.Scenario_Wiz.ScenarioID', '=', 'CapeCodMA.Treatment_Wiz.ScenarioID')
      ->join('CapeCodMA.Embayments', 'CapeCodMA.Scenario_Wiz.AreaID', '=', 'CapeCodMA.Embayments.EMBAY_ID')
      ->join('CapeCodMA.TechnologyMatrix', 'CapeCodMA.TechnologyMatrix.TechnologyID', '=', 'CapeCodMA.Treatment_Wiz.TreatmentType_ID')
      ->select(
          'CapeCodMA.Scenario_Wiz.ScenarioID',
          'CapeCodMA.Scenario_Wiz.AreaID',
          'CapeCodMA.Treatment_Wiz.Cost_TC_Input',
          'CapeCodMA.Treatment_Wiz.Cost_OM_Input',
          'CapeCodMA.Treatment_Wiz.Treatment_Parcels',
          'CapeCodMA.Treatment_Wiz.Treatment_UnitMetric',
          'CapeCodMA.Treatment_Wiz.Treatment_MetricValue',
          'CapeCodMA.Treatment_Wiz.TreatmentType_ID',
          'CapeCodMA.Treatment_Wiz.TreatmentID',
          'CapeCodMA.Treatment_Wiz.Cost_Replacement',
          'CapeCodMA.Treatment_Wiz.Treatment_Wastewater_Flow',
          'CapeCodMA.Treatment_Wiz.Clipped_Rds_LinFeet',
          'CapeCodMA.Treatment_Wiz.Treatment_WU_Parcels',
          'CapeCodMA.Embayments.EMBAY_ID',
          'CapeCodMA.Embayments.EMBAY_DISP',
          'CapeCodMA.TechnologyMatrix.Tech_Name',
          'CapeCodMA.TechnologyMatrix.Icon'
        );
  }

  /**
   * Return Default TreatmentTech DB Query
   *
   * @return DB
   */
  private function getDBSUBEMQuery( $embay_id )
  {
    return DB::table('dbo.Nitrogen_Allocations_ByTown')
      ->join('CapeCodMA.MATowns', 'CapeCodMA.MATowns.TOWN_ID', '=', 'dbo.Nitrogen_Allocations_ByTown.TOWN_ID')
      ->select(
        'dbo.Nitrogen_Allocations_ByTown.TOWN_ID',
        'dbo.Nitrogen_Allocations_ByTown.N_LOAD_RESPONSIBLE',
        'dbo.Nitrogen_Allocations_ByTown.PCT_CONTRIBUTE',
        'CapeCodMA.MATowns.TOWN',
        'CapeCodMA.MATowns.TOTAL_WU_PAR',
        'CapeCodMA.MATowns.TOTAL_PAR',
        'CapeCodMA.MATowns.MEAN_BLDG_VAL_PRI',
        'CapeCodMA.MATowns.MEAN_BLDG_VAL_SEC',
        'CapeCodMA.MATowns.TOT_ASSESSED_VAL'
      )
      ->where('dbo.Nitrogen_Allocations_ByTown.EMBAY_ID', '=', $embay_id)
      ->get();
  }

  /**
   * Get all of the tasks for a given user.
   *
   * @param  int $scenarioId The Scenario's ID
   * @return Collection Return a collection of TreatmentTech objects
   */
  public function forScenario( $scenarioId )
  {
    $stacks = [];

    $scenarioTreatmentTechs = $this->getDBQuery()->where('CapeCodMA.Scenario_Wiz.ScenarioID', '=', $scenarioId)->get();

    foreach ($scenarioTreatmentTechs as $key => $tech) {
      array_push($stacks, $this->TreatmentTechFactory($tech)->toArray());
    }

    return $stacks;
  }

  /**
   * Return a updated TreatmentTech instance
   *
   * @param int $treatmentId
   * @return App\TreatmentTech
   */
// ***** ESTABLISH THE 'getTreatmentTech', WHICH USES THE $treatmentId TO ESTABLISH A $tech VARIABLE & SET THAT =
// THE $treatmentId, RETURN THE $tech FROM THE 'TreatmentTechFactory *****
  public function getTreatmentTech( $treatmentId )
  {
    $tech = $this->getDBQuery()->where('CapeCodMA.Treatment_Wiz.TreatmentID', '=', $treatmentId)->first();

    return $this->TreatmentTechFactory($tech);
  }

  /**
   * Return a updated TreatmentTech instance
   *
   * @param int $treatmentId
   * @param int $relativeStartYear
   * @param int $duration
   * @return App\TreatmentTech
   */
  public function getUpdatedTreatmentTech( $treatmentId, $relativeStartYear, $duration )
  {
    $tech = $this->getDBQuery()->where('CapeCodMA.Treatment_Wiz.TreatmentID', '=', $treatmentId)->first();

    return $this->TreatmentTechFactory($tech, $relativeStartYear, $duration);
  }

  /**
   * TreatmentTechFactory
   *
   * @param $tech
   * @param $relativeStartYear
   * @param $duration
   * @return App\TreatmentTech
   */
// ***** THE 'TreatmentTechFactory' GETS ALL THE DATA BY TREATMENT USING THE 'TreatmentType_ID', PACKAGES IT INTO A $tt
// VARIABLE AND RETURNS IT TO BE CONSUMED BY THE 'getTreatmentTech' PUBLIC FUNCTION
  private function TreatmentTechFactory($tech, $relativeStartYear = false, $duration=false)
  {
    // TreatmentType_ID
    $response = $this->client->get('getTechnology/' . $tech->TreatmentType_ID);
    $data = json_decode($response->getBody(), true);

    $args = new \stdClass;

    $args->treatmenttm_id           = $data['treatmenttm_id'];
    $args->treatmentTypeId          = intval($tech->TreatmentType_ID);
    $args->treatmentId              = intval($tech->TreatmentID);
    $args->scenarioId               = intval($tech->ScenarioID);
    $args->treatmentName            = $data['technologyStrategy'];
    $args->treatmentType            = $data['technologyType'];
    $args->treatmentIcon            = $data['icon'];
    $args->AreaId                   = intval($tech->AreaID);
    $args->EmbaymentId              = intval($tech->EMBAY_ID);
    $args->EmbaymentName            = $tech->EMBAY_DISP;
    $args->unitMetric               = $tech->Treatment_UnitMetric;
    $args->metricValue              = round( floatval($tech->Treatment_MetricValue), 2 );
    $args->totalReplaceUpgradeCost  = $data['totalReplaceUpgradeCost'];
    $args->avgLiveCycleCost         = $data['avgLiveCycleCost'];
    $args->adjOMCostAvg             = $data['adjOMCostAvg'];
    $args->adjProjCostAvg           = $data['adjProjCostAvg'];

    $args->treatmentWastewaterFlow  = floatval($tech->Treatment_Wastewater_Flow);
    $args->clippedRoadsInLinearFeet = floatval($tech->Clipped_Rds_LinFeet);


    $args->capitalSlope             = $data['capitalSlope'];
    $args->capitalIntercept         = $data['capitalIntercept'];
    $args->capitalAdjFactor         = $data['capitalAdjFactor'];
    $args->projectCostAdjFactor     = $data['projectCostAdjFactor'];
    $args->avgAcres                 = $data['avgAcres'];
    $args->omSlope                  = $data['omSlope'];
    $args->omIntercept              = $data['omIntercept'];
    $args->omAdjFactor              = $data['omAdjFactor'];
    $args->highLevelTreatmentFactor = $data['highLevelTreatmentFactor'];
    $args->usefulLifeInYears        = $data['usefulLifeInYears'];
    $args->replacementCost          = $data['replacementCost'];
    $args->replacementProjectCost   = $data['replacementProjectCost'];

    $args->typeOfCostSpread         = $data['typeOfCostSpread'];

    $args->treatmentWaterUseParcels = intval($tech->Treatment_WU_Parcels);

    $treatmentType = $this->classPath.$this->techTypeMap[$data['technologyType']];

    $treatmentType = new $treatmentType();

    $args->towns = $this->getTownParcelData( $args->EmbaymentId  );

    $tt = false;

    if ( $relativeStartYear AND $duration ) {
      $tt = new TreatmentTech( $args, $treatmentType, $relativeStartYear, $duration );
    } else {
      $tt = new TreatmentTech( $args, $treatmentType );
    }

    return $tt;
  }

  private function getTownParcelData( $subem_id )
  {

    $return = [];

    $towns = $this->getDBSUBEMQuery( $subem_id );

    foreach ($towns as $key => $town) {
      $params = new \stdClass;

      $params->id = intval($town->TOWN_ID);
      $params->name = $town->TOWN;
      $params->nitrogenLoadResponsible = floatval($town->N_LOAD_RESPONSIBLE);
      $params->totalWaterUseParcels = intval($town->TOTAL_WU_PAR);
      $params->townTotalParcels = intval($town->TOTAL_PAR);
      $params->meanPrimaryBuildingValue = floatval($town->MEAN_BLDG_VAL_PRI);
      $params->meanSecondaryBuildingValue = floatval($town->MEAN_BLDG_VAL_SEC);
      $params->totalAssessedValue = floatval($town->TOT_ASSESSED_VAL);
      $params->fallbackPercentage = floatval($town->PCT_CONTRIBUTE);

      array_push($return, new TownParcelData( $params ) );
    }

    return $return;
  }

  public function scenarioIdFactory ($scen) 
  {

    $args = new \stdClass;

    $args->id = intval($scen->ScenarioID);

    $tt = new ScenarioIds($args);

    return $tt;

  }

  /**
   * Return array with scenario ids with scenario meta
   *
   * @return JSON Array
   */
/**

// ***** THE 'getScenarios' PUBLIC FUNCTION PRODUCES AN ARRAY OF SCENARIOS (FROM the 'getscenarioDB' INJECTION) WHERE
// TREATMENTS EXIST, GIVES EACH SCENARIO A KEY, PUSHES THE SCENARIOS TO THE ARRAY, RETURNS THE ARRAY *****
  public function getScenarios()
  {

    $array = [];

    $scenarios = $this->getscenarioDB()->whereNotNull('CapeCodMA.Treatment_Wiz.TreatmentID')->distinct()->get();

    foreach ($scenarios as $key => $scenario) {

      array_push($array, $this->scenarioIdFactory( $scenario )->toArray());
    }

    return $array;
  }
}

Back-end v1

getScenarios & getTreatment

FIM front-end:

Node.js + Vue.js

FIM front-end

FIM front-end

FIM front-end

FIM Front-end :

Project Structure

FIM front-end

// Create nested routes
export default function (router) {
  router.map({
    '/': {
      component: require('./components/Start.vue')
    }, // TODO: Track current routes per treatment
    '/scenario/:id': { // :id is a route parameter that can be passed into a function
      component: require('./components/ScenarioView.vue'),
      subRoutes: {
        '/treatmentsDetails' : { // Routes hereafter are nested within /scenario/:id
          name: 'treatmentDetail',
          component: require('./components/TreatmentDetail.vue'),
        },
        '/financeTreatments' : {
          name: 'financeTreatment',
          component: require('./components/TreatmentFinance.vue'),
        },
        '/scenarioCostSharing' : {
          name: 'pie',
          component: require('./components/pie.vue'),
        },
        '/projectAndFinancing': {
          name: 'pairedbar',
          component: require('./components/pairedbar.vue')
        },
        '/propertyOwnerCosts': {
          name: 'propertyOwnerCosts',
          component: require('./components/propertyOwnerCosts.vue')
        }
      }
    }
  })
  router.redirect({
    '*': '/'
  })
  // router.beforeEach((transition)=>{
  //   transition.next()
  // })
}

routes.js

FIM front-end

actions.js

FIM front-end

store.js

FIM front-end

// Functions to retrieve data from the state
export function getTreatmentIndex (state) {
  return state.treatmentIndex
}

export function getFinanceOptions (state) {
  return state.financeOptions
}

export function getScenario (state) {
  return state.scenario
}

export function getTreatments (state) {
  return state.scenario.treatments
}

export function getSelectedTreatment (state) {
  return state.scenario.treatments[state.treatmentIndex]
}

export function getScenarios (state) {
	return state.scenarios
}

getters.js

FIM front-end

// Import JQuery, VuJS Node modules for state management
import $ from 'jquery'
window.$ = $
import Vue from 'vue'
import store from './vuex/store'
import App from './App'
import configRouter from './routes'
import VueRouter from 'vue-router'
import VueResource from 'vue-resource'
import VueCharts from 'vue-charts'


// Load technology icon path
const iconPath = 'http://208.capecodcommission.org/Images/AltIcons/'

// Start Vue state management services
Vue.use(VueResource)
Vue.use(VueRouter)
Vue.use(VueCharts)

// Currency filter: Add '$' to ints, fixed to 2 decimal places
Vue.filter('currency', { read: function (val) {
  return '$'+ val.toFixed(2)
}})

const router = new VueRouter({
  history: true,
  saveScrollPosition: true,
  root:  '/fim',
  suppressTransitionError: true
})

configRouter(router)

// Start router
router.start({
  store,
  components: { App }
}, '#app')

window.router = router
window.iconPath = iconPath

main.js

FIM front-end

TreatmentDetail.vue

Back-end v2: Node.js

Back-end v2

Why not Laravel Lumen?

Laravel environment setup

Back-end v2

+ Lumen to construct APIs

[the microframework]

Back-end v2

Back-end v2

Back-end v2

//initialize node modules
var express = require("express");
    bodyParser = require("body-parser");
    sql = require("mssql");
    app = express();

// tell the app to use the body parser middleware
app.use(bodyParser.json());

// tell the app to use the CORS Middleware
app.use(function (req, res, next) {

  // Enable the CORS
  res.header("Access-Control-Allow-Origin", "*");
  res.header("Access-Control-Allow-Methods", "GET,HEAD,OPTIONS,POST,PUT");
  res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, contentType,Content-Type, Accept, Authorization");
  next();
});

// set the server & port config as vars
var server = app.listen(process.env.PORT || 8081, function () {

  var port = server.address().port;

  console.log('App now running on port', port);
});

// wMVP3_CapeCodMA' DB config object
var wmvp3_DBConfig = {
  user: 'DBAccess',
  password: 'Acce$$DB',
  // server: '192.138.212.28', //ACESS FROM EXTERNAL TO NETWORK? WHAT TRIVEDI WAS USING?
  server: '10.10.1.174',
  port: '65335',
  database: 'wMVP3_CapeCodMA',
  stream: true,
  pool: {
    max: 100,
    min: 0,
    idleTimeoutMillis: 300000
  }
};

var wqm_DBConfig = {
  user: 'DBAccess',
  password: 'Acce$$DB',
  // server: '192.138.212.28', //ACESS FROM EXTERNAL TO NETWORK? WHAT TRIVEDI WAS USING?
  server: '10.10.1.174',
  port: '65335',
  database: 'WaterQualityMonitoring',
  stream: true,
  pool: {
    max: 100,
    min: 0,
    idleTimeoutMillis: 300000
  }
};

var tm_DBConfig = {
  user: 'DBAccess',
  password: 'Acce$$DB',
  // server: '192.138.212.28', //ACESS FROM EXTERNAL TO NETWORK? WHAT TRIVEDI WAS USING?
  server: '10.10.1.174',
  port: '65335',
  database: 'Tech_Matrix',
  stream: true,
  pool: {
    max: 100,
    min: 0,
    idleTimeoutMillis: 300000
  }
};

// Estbalish a ScenarioWizQuery f(x) to connect to 'wMVP3_CapeCodMA' & get a response
var  executeQuery = function (res, query, config) {

  // Must close open sql connection first before opening a new one
  sql.close()

  // use mssql node package to connect to the 'wMVP3_CapeCodMA' db
  sql.connect(config, function (err) {

    // if there's an error connecting to the db, console.log it out
    if (err) {

      console.log("Error while connecting database :- " + err);
      res.send(err);
    }

    // if there's no error, create the Request object & query the db
    else {

      // indicate connection to the 'wMVP3_CapeCodMA' database
      console.log('we are connected');

      // create Request object
      var request = new sql.Request();

      // query the database
      request.query(query, function (err, recordset) {

        // if there's an error in the query, console.log it out
        if (err) {

          console.log('Error while querying database :- ' + err);
          res.send(err);
        }

        // if there's no error, send the response
        else {

          res.send(recordset);
        }
      })
    }
  })
}

//GET ScenarioWiz table from wmvp3 DB
app.get('/api/ScenarioWiz/:id', function(req , res) {

  var query = 'select * from CapeCodMA.Scenario_Wiz where ScenarioID = ' + req.params.id;

  executeQuery (res, query, wmvp3_DBConfig);
});

//GET TreatmentWiz table from wmvp3 DB
app.get('/api/TreatmentWiz/:id', function(req , res) {

  var query = 'select * from CapeCodMA.Treatment_Wiz where ScenarioID = ' + req.params.id;

  executeQuery (res, query, wmvp3_DBConfig);
});

//GET Embayments table from wmvp3 DB
app.get('/api/Embayments', function(req , res) {

  var query = 'select * from CapeCodMA.Embayments';

  executeQuery (res, query, wmvp3_DBConfig);
});

//GET FTCoeff table from wmvp3 DB
app.get('/api/FTCoeff', function(req , res) {

  var query = 'select * from CapeCodMA.FTCoeff';

  executeQuery (res, query, wmvp3_DBConfig);
});

// GET MATowns from wmvp3
app.get('/api/MATowns', function(req , res) {

  var query = 'select * from CapeCodMA.MATowns';

  executeQuery (res, query, wmvp3_DBConfig);
});

// GET SubEmbayments from wmvp3
app.get('/api/SubEmbayments', function(req , res) {

  var query = 'select * from CapeCodMA.SubEmbayments';

  executeQuery (res, query, wmvp3_DBConfig);
});

// GET Subwatersheds from wmvp3
app.get('/api/Subwatersheds', function(req , res) {

  var query = 'select * from CapeCodMA.Subwatersheds';

  executeQuery (res, query, wmvp3_DBConfig);
});

// GET parcelMaster from wmvp3
// EXAMPLE: scenarioid: /api/parcelMaster/2586
app.get('/api/parcelMaster/:id', function(req , res) {

  var query = 'select * from CapeCodMA.parcelMaster WHERE scenario_id = ' + req.params.id;

  executeQuery (res, query, wmvp3_DBConfig);
});

// GET wiz_treatment_towns from wmvp3
// EXAMPLE: scenarioid: /api/wiz_treatment_towns/2586
app.get('/api/wiz_treatment_towns/:id', function(req , res) {

  var query = 'select * from dbo.wiz_treatment_towns WHERE wtt_scenario_id = ' + req.params.id;

  executeQuery (res, query, wmvp3_DBConfig);
});


// GET WastewaterSource3 from wmvp3
// EXAMPLE: scenarioid: /api/WastewaterSource3/242
app.get('/api/WastewaterSource3/:id', function(req , res) {

  var query = 'select * from dbo.WastewaterSource3 WHERE Muni_ID = ' + req.params.id;

  executeQuery (res, query, wmvp3_DBConfig);
});

// GET ParcelCharacteristics from wmvp3
// EXAMPLE: scenarioid: /api/ParcelCharacteristics/242
app.get('/api/ParcelCharacteristics/:id', function(req , res) {

  var query = 'select * from dbo.ParcelCharacteristics WHERE Muni_ID = ' + req.params.id;

  executeQuery (res, query, wmvp3_DBConfig);
});

// GET StgEmbaymentWaterQualityData from WaterQualityMonitoring
// EXAMPLE: Allen Harbor: /api/StgEmbaymentWaterQualityData/101
app.get("/api/getEmbayment/:name", function(req , res) {

  var query = "SELECT \
                Date as date, \
                case when SalFin is null then SalinityPpt else null end as salinity, \
                case when DoMgFin is null then CorrectedDoMgPL when CorrectedDoMgPL is null then DoMgPL else null end as disolvedoxygen, \
                case when TnPpmFin is null then TnUm end as nitrogen, \
                WaterTempC as water_temp, \
                PrecFin as precipitation, \
                TotalDepthM as depth, \
                NoxUmFin as nitrate_nitrite, \
                Nh4UmFin as ammonium, \
                Po4Um as orthophosphate, \
                ChlaUgPL as chlorophyll, \
                PhaeoUgPL as phaeophytin \
               FROM dbo.WaterQualityReading \
               WHERE StnEquiv = " + "'" + req.params.name + "'" + 'ORDER BY cast(DATE as date)';

  executeQuery (res, query, wqm_DBConfig);
});

// GET StgEmbaymentWaterQualityData from WaterQualityMonitoring
// EXAMPLE: Allen Harbor: /api/StgEmbaymentWaterQualityData/101
app.get('/api/getEmbayments', function(req , res) {

  var query = 'select id as EMBAYMENT_ID, Name as EMBAYMENT from dbo.Embayment where id is not null and id < 161' 

  executeQuery (res, query, wqm_DBConfig);
});



// GET Technology_Matrix from Tech_Matrix
// EXAMPLE: Aquaculture - Shellfish Cultivated In Estuary Bed: /api/Technology_Matrix/11
app.get('/api/Technology_Matrix/:id', function(req , res) {

  var query = 'select * from dbo.Technology_Matrix WHERE TM_ID = ' + req.params.id;

  executeQuery (res, query, tm_DBConfig);
});

Back-end v2

data delivery | app specific math | front end

Shawn Goulet

Cape Cod Commission

Thank You!

FOSS4G 2017 : RESTful api stack & web dev

By Shawn Goulet

FOSS4G 2017 : RESTful api stack & web dev

Presentation to be given @ FOSS4G 2017

  • 2,455