Project Overview

Savenia Solar Rating is an application that helps users enter information on their solar system and other energy efficient items in their house to generate a “rating” on how energy efficient their house is.

Challenge Overview

We are building the backend data model and PHP REST API in this challenge for the Rating application.

Challenge Requirement

We will use following technology stack in this challenge :

• PHP

• Yii Framework (use latest stable version)

• MySQL

• Ubuntu OS

Wireframe

Provided in the challenge forums a powerpoint file that represents a wireframe with the workflow for the rating application.

When you open it in a PowerPoint application, go into Slide view, and you will be able to go through the flow by clicking on some buttons, not everything in the slide is clickable, it is only the navigation buttons that are clickable to show you the flow of data.

In this challenge we analyzed the power point and wrote down the data model we think will be proper for this system.

You are highly encouraged to review the powerpoint workflow/content and suggest any improvements.

REST API and PHP Framework Requirement

• We will use Yii Framework to build the REST API.

• You will use MySQL as database.

• All request and response body parameters will be in JSON format.

• Create REST API for following :

  • note that managing User entity or build API for it is out of scope of the challenge.

  • home API

    • It should be a CRUD API : create, update, retrieve, retrieveAll, and delete endpoints. This also should include api endpoints to partially create the associated units (Solar PV, SHell,and HVAC) :

      • POST /homes/{id?}

        • this should accept json object for “UserHome”, and associated child entities “SolarPV”, “Shell”, “HVAC”, “Appliances”, “Lighting”, and “WaterHeater”.

        • {id} parameter is optional.

        • it creates new record or updates existing record.

        • it will create a rating record with status ‘draft’.

        • If entity with {id} does not exist, return error.

      • GET /home/{id}

        • {id} represents the UserHome id.

        • it will return UserHome and all associated child entities.

        • return empty json object if not found.

      • GET /homes

        • this returns all homes and child entities.

        • pagination must be enforced :

          • by default it returns 10 items

      • GET /homes/user/{id}

        • {id} represents user id

        • this returns all homes of specified user id.

        • pagination must be enforced :

          • by default it returns 10 items

      • DELETE /home/{id}

        • {id} represents UserHome id

        • it deletes the entity if exists. return result:true in json, if not exists, return false.

      • POST /home/{id}/type/{type}/{type-id}?

        • creates or update a product and associate it with passed home.

        • {id} represents UserHome id

        • {type} represents the type of child entity to create.

        • {type-id} is optional parameter, represents the id of entity.

        • {type} should be validated, possible values (case insensitve) : HVAC, SolarPV, Shell, Appliance, WaterHeater, Lighting.

        • Validate UserHome with {id} exists, otherwise, return 404 error.

        • if no entity exists with {type-id}, return 404 error.

        • if {type-id} is provided, then validate it belongs to provided UserHome. if not, return error.

        • if {type-id} is absent, then create new record and associate it with provided UserHome id, otherwise, update entity.

      • GET /home/{id}/ratings

        • {id} represents the UserHome id.

        • return all rating records associated with the UserHome id.

        • pagination must be enforced :

          • by default it returns 10 items

      • GET /home/type/{type}/{type-id}?

        • {type} represents the child entity type.

          • possible values (case insensitve) : HVAC, SolarPV, Shell, Appliance, WaterHeater, Lighting.

        • {type-id} represents the entity id.

        • returns all records of type {type}.

        • if {type-id} is provided, returns that record.

        • if no record with id {type-id} exists, return error.

        • pagination must be enforced :

          • by default it returns 10 items

  • rating API

    • It should be a CRUD API : create, update, retrieve, retrieveAll, and delete.

    • endpoints are :

      • POST /ratings/{id}?

        • {id} is optional, represents the rating id.

        • if entity with {id} does not exist, return empty json.

        • creates or updates the entity.

        • it accepts json object with all rating fields.

      • GET /ratings

        • retrieve all rating records

        • pagination must be enforced :

          • by default it returns 10 items

      • GET /rating/{id}

        • retrieves rating of provided ID

        • if not found, return error.

      • DELETE /rating/{id}

        • {id} represents the rating id.

        • deletes the entity if found, response should be result:success, if not found, return result:false.

  • auditing API

    • It should be CRUD : create, update, retrieve, retrieveAll, and delete

    • these endpoints will be used by app when user selects to submit an audit.

      • POST /audit/{id}?

        • {id} represents audit id.

        • it updates or creates new record.

        • if record with {id} does not exist, return error.

        • it accepts json object with all Audit resource fields.

      • GET /audit

        • returns list of audits

        • pagination must be enforced :

          • by default it returns 10 items

      • GET /audit/{id}

        • {id} represents audit id

        • return the corresponding record.

        • if not exist, return empty object.

      • DELETE /audit/{id}

        • {id} represents the id of the audit.

        • deletes the audit record if found, response should be result:success, if not found, return result:false.

• Lookup API

  • For all lookup fields in models, create a REST API endpoint that returns all values with signature ‘GET /lookup/{lookup-name}’

  • The {lookup-name} represents the lookup name.

  • The table name of the lookup should be ‘{lookup-name}_lookup’

  • if user passed a {lookup-name} that does not exist in database as table (i.e. CarModel, where we don’t have a table with name CareModel_lookup table) then error should be returned.

  • an example would be home type record, we need endpoint ‘GET /lookup/homeTypes’ that return list of homeTypes records.

  • In Resources Requirement section, the field with type (lookup) should have a corresponding table in db, and user can query these data.

Resources/Tables Requirement

You will create following table/models :

Audit

• id

• home_id

• time

• date

• will_someone_be_there?

• address

• additional_notes

• shell_audit? (yes/no)

• savenia_audit(yes/no)

• solar_audit (yes/no)

• status (enum: Draft, Submitted, Processed, Done)

UserHome

• user_id

• address

• city

• zip_code

• home_type (lookup : Single Family Home, Townhouse, Duplex, Apartment, Condominium)

• homeowner (name of client : a string)

• year_built (format : yyyy)

• year_renovated (format : yyyy)

• sqf (home square footage)

• created_by (one who filled the rating form)

• created_by_role (enum : Realtor, Contractor, Solar Installer, Homeowner, Auditor, Other)

• email

• phone_number

• solar_pv_id

• shell_id

• hvac_id

• lighting_id

• appliance_id

• waterHeater_id

• rating_id (replaced with UserHome_id foreign key in Rating table)

• status (enum: draft, submitted, done)

Rating

• id

• UserHome_id

• status (enum: rated/inprogress/don’t-qualify)

• other reporting information to be sent via email to user when requesting label/report? (yes/no)

• re-rate ? (yes/no)

• role_id (Lookup: Realtor, Contractor, Solar Installer, Homeowner, Auditor, Other)

SolarPV

• id

SolarPV_product

• ID

• SolarPV_id

• UserHome_id

• ome_contract_id (lookup : own, lease, loan, other)

• lease_type_id (lookup: fixed monthly payments, fixed utility price, prepaid lease)

• lease_end_date(date month/year)

• monthly_payment_amount ($)

• fixed_utility_rate_payment_amount ($ per kWh)

• contract_annual_increase_rate (%x.xx)

• purchase_price ($)

• annual_maintenance_cost ($)

• expected_recs (number)

• recs_from_pvwatts (number)

• recs_sold_state_id (lookup: DC,DE,MA,MD,NC,OH,PA)

• solar_made_year (year...ie 2003)

• years_old (this will be a calculation leading to a number 0-19)

• system_size (DC wattage 2,000-20,000 kW)

• en_prd_12_m (energy production last 12 months in kWh)

• invertor_replaced (true/false)

• ownership_are_you_first_owner (true/false)

• ownership_all_components_new_when_installed (true/false)

• ownership_you_have_ownership_documentation (true/false)

• ownership_3rd_party_manages_recs (true/false)

• rh_broken_glass_on_panels (true/false)

• rh_cracked_panels (true/false)

• rh_loose_solder_connections (true/false)

• rh_panel_failure (true/false)

• rh_inverter_failure (true/false)

• rh_other (true/false)

• rh_dont_know (true/false)

• usage_used_year_around (true/false)

• usage_system_operating_now (true/false)

• usage_service_contract_in_place (true/false)

• usage_annual_panel_cleaning (true/false)

• sys_battery_backup_system (true/false)

• sys_uses_micro_inverters (true/false)

• sys_high_efficiency_panels (true/false)

• sys_panels_made_in_usa (true/false)

• sys_roof_panels (true/false)

• sys_rack_mounted (true/false)

• sys_online_system_monitoring (true/false)

• advanced_data_inputs_module_type_id (lookup: standard, premium, thin film)

• advanced_data_inputs_system_losses (0.00-100 default 14)

• advanced_data_inputs_module_degradation_rate (default = 5)

• advanced_data_inputs_array_type_id (lookup: fixed (open rack), fixed (roof mount), 1-Axis Tracking, 1-Axis Backtracking, 2-Axis Tracking)

• advanced_data_inputs_array_tilt (0-90 degrees)

• advanced_data_inputs_array_azimuth (0-360 degrees)

• advanced_data_inputs_dc_to_ac_ratio(0.5-2.0 default 1.1)

• advanced_data_inputs_inverter_efficiency(90.0-99.5% default 96%)

• advanced_data_inputs_ground_coverage_ratio (0.0-1.0, default 0.4)

• solar_confirmation_docs_received (yes, no)

Shell

• id

Shell_product

• id

• Shell_id

• UserHome_id

• Status_self_upgraded (enum: yes no don’t know)

• Status_other_person_upgraded (yes no don’t know)

• Interested_in_evaluation (yes no don’t know)

• audit_company (performed audit/upgrade)

• any_supported_initiative (yes/no/don't know)

• ai_room_numbers

  • ai : advance input

• ai_hes_score (0-10)

• ai_total_score (mBTUs)

• ai_score_basis (mBTUs)

• ai_stories_above_ground

• ai_average_ceiling_height (feet)

• ai_direction_faced_by_the_front_house_id (lookup: N/NE/E/SE/S/SW/W/N)

• ai_blower door test conducted? (Yes No)

• ai-air_leakage_rate (CFM 50)

• ai-house_professionally_sealed (yes no)

• ai_position_of_unit (enum: middle, left, right)

• ai_windows_front_frame_type_id (lookup)

• ai_windows_front_panes_id (lookup)

• ai_windows_front_glazing_type_id (lookup)

• ai_windows_front_sqf_windows_id (lookup)

• ai_walls_front_construction_id (lookup)

• ai_walls_front_exterior_finish_id (lookup)

• ai_walls_front_wall_insulation_id (lookup)

• ai_windows_left_frame_type_id (lookup)

• ai_windows_left_panes_id (lookup)

• ai_windows_left_glazing_type_id (lookup)

• ai_windows_left_sqf_windows_id (lookup)

• ai_walls_left_construction_id (lookup)

• ai_walls_left_exterior_finish_id (lookup)

• ai_walls_left_wall_insulation_id (lookup)

• ai_windows_back_frame_type_id (lookup)

• ai_windows_back_panes_id (lookup)

• ai_windows_back_glazing_type_id (lookup)

• ai_windows_back_sqf_windows_id (lookup)

• ai_walls_back_construction_id (lookup)

• ai_walls_back_exterior_finish_id (lookup)

• ai_walls_back_wall_insulation_id (lookup)

• ai_windows_right_frame_type_id (lookup)

• ai_windows_right_panes_id (lookup)

• ai_windows_right_glazing_type_id (lookup)

• ai_windows_right_sqf_windows_id (lookup)

• ai_walls_right_construction_id (lookup)

• ai_walls_right_exterior_finish_id (lookup)

• ai_walls_right_wall_insulation_id (lookup)

• ai_is_skylights (yes/no)

• ai_skylights_frame_type_id (lookup)

• ai_skylights_panes_id (lookup)

• ai_skylights_glaze_type_id (lookup)

• ai_skylights_sqf

• ai_skylights_no_panes

• a_tightness_blower_door_test (yes/no)

• ai_tightness_air_leakage_rate

• ai_tightness_professional_air_sealed? (yes/no)

• ai_roof_construction_type_id (lookup)

• ai_roof_exterior_finish_id (lookup)

• ai_roof_insulation_level_id (loookup)

• ai_roof_roof_color_id (lookup)

• ai_attic_type_id (lookup)

• ai_attic_floor_insulation_id (lookup)

• ai_foundation_type_id (lookup)

• ai_foundation_insulation_over_basement_crawlspace_id (lookup)

• ai_foundation_insulation_id (lookup)

• wall_insuation_type_id (lookup)

• wall_insulation_depth

• wall_insulation_year_installed

• attic_insulation_type_id (lookup)

• attic_insulation_depth

• attic_insulation_year_installed

• attic_insulation_2_type_id (lookup)

• attic_insalation_depth

• attic_insalation_year_installed

• ducts_1_location_id (lookup)

• ducts_1_percentage

• ducts_1_is_sealed (true/false)

• ducts_1_is_insulated (true/false)

• ducts_2_location (lookup)

• ducts_2_percentage

• ducts_2_is_sealed (true/false)

• ducts_2_is_insulated (true/false)

• ducts_3_location (lookup)

• ducts_3_percentage

• ducts_3_is_sealed (true/false)

• ducts_3_is_insulated (true/false)

HVAC

• id

HVAC_product

• id

• HVAC_id

• UserHome_id

• Category_id (lookup: Heating/Cooling/HeatPump)

• UnitCategory_id (lookup: Furnace/Boiler/SplitUnit)

• UnitFuelSource_id (Lookup: Electric/NaturalGas/Propane/Oil)

• extra_info_Manufacturer

• extra_info_Model_Num

• extra_info_Size (BTUs)

• extra_info_AFUE(0-100) for furnace

• extra_info_Energy_Star (Yes/No/Don’t know)

• extra_info_BTUs (for Boiler)

• extra_info_AFUE_Rating (For boiler)

• extra_info_ThermalEfficiency  (for boiler)

• extra_info_kWh_Annual (for boiler)

• extra_info_Therms_Annual (for boiler)

• extra_info_SEER (for air conditioner)

• extra_info_HSPF (for heat pump)

• extra_info_SEER_Rating (for heat pump)

• extra_info_purchase_price ($, for all products, Furnace, Boiler, A/C, Heat Pump)

• Unit_Installation_Year (XXXX)

• Unit_sq_foot_served (number square feet)

• Unit_is_dual_system (true/false)

• Unit_is_primary (primary or secondary? true/false, or can be Furnace_unit_Type enum)

Appliance

• id

Appliance_product

• id

• appliances_id

• UserHome_id

• category_id (lookup : washing machine, dishwasher, refrigerator, freezer)

• type_id (lookup: standard/compact for washer, side-by-side, top freezer for refrig .. etc)

• extra_info_manufacturer

• extra_info_model_no

• extra_info_is_energy_star (yes/no/no-idea)

• extra_info_kwh_per_year

• extra_info_purchase_price ($, for all products, washer, dishwasher, refrig, freezer)

• unit_installation_year

• extra_info_size (cubic feet)

• extra_info_water_usage (gallons / year for dishwashers and washing machines)

• defrost (automatic, manual, don’t know) for freezer

waterHeater

• id

waterHeater_product

• id

• waterHeater_id

• UserHome_id

• category_id (lookup : tank, tankless, solar)

• type_id (lookup: electric, natural gas, lpg)

• extra_info_manufacturer

• extra_info_model_no

• extra_info_is_energy_star (yes/no/don’t know)

• extra_info_kwh_per_year

• extra_info_tankless_water_heater_fuel_source_id (lookup: electric, natural gas, lpg)

• extra_info_gpm(for tankless)

• extra_info_FHR (for tank heaters)

• extra_info_capacity (gallons)

• extra_info_energy_factor (for solar hw systems)

• unit_installation_year

• extra_info_number_panels (for solar hw systems)

Lighting

• id (this referenced by room and userHomes)

• UserHome_id

room
Lighting_product

• id

• lighting_id (this will reference the lighting table)

• UserHome_id

• room_bulb_id ( this will reference the room_bulb table, each room will have one or more bulbs)

• room_id (lookup: attic, back porch, basement, bathroom, bedroom, cellar, den, dining room, family room, front porch, garage, gym, hallway, kitchen, laundry, library, living room, master bedroom, mud room, office, pantry, playroom, staircase, study, sun room, TV room, wine cellar, workshop)

• Also need a way to number these as there may be more than one type of room on the same floor - ie bedroom 1, 2, 3, 4, 5, bathroom 1, 2, 3

• floor (basement, 1st, 2nd,3rd,4th,5th)

room_bulb

• room_id

• bulb_id

• fixture_type_id (lookup : recessed, hanging, etc)

• quantity

• dimmable (has dimmer switch?)

• covered_fixture? (yes/no)

bulb (lookup table)

• id

• base_id (lookup: tapered base round top, cone base bulge top, cone base flat top, large round bulb, candle bulb, reflective bulb)

• shape_id (lookup: A Type, BR Type, PAR Type, Globe, Candelabra, Other / Irregular)

• bulb_size_id (lookup: A19, BR30, BR40, PAR20, PAR30, PAR38, MR16, etc)

• bulb_technology_id (lookup: incandescent, CFL, LED, Halogen, other)

• brightness

• brightness_x (where x is PAR_20, PAR_30 .. etc, we have cases where a bulb can have more than one brightness value)

• wattage (number watt)

Lookup tables

• all fields about that are marked with (lookup) should have a corresponding lookup table.

• the lookup should have following fields :

  • name

  • description

  • order (integer)

  • .. other fields depends on the case. i.e. bulb has more fields to insert (refer to the definition above).

All tables should have auditing fields except Lookup tables

All tables should have a primary key "id"

All fields in tables that have (Lookup) description text are foreign keys to the corresponding lookup table.

All fields in columns can be NULL except primary keys.

Unit Testing

All of your code should be test covered (PHPUnit preferred). You must cover at least +90% of the code (getters, setters, and constructor don't need to be tested).

You need to test all cases and in test case assertion you need to assert accuracy of all fields, also it is preferred to use the Wireframe flow to write real case scenarios for what information we would pass for every product or home we create.

Configuration

Follow the standard/best-practices to configure the Yii web application, all sensitive data and environment specific information should be configured

Naming Convention

You can follow best practices or recommended naming convention for PHP, what is important is that you are using a consistent naming convention across the code your solution. confirmed in these threads :

http://apps.topcoder.com/forums/?module=Thread&threadID=849026&start=0&mc=2
http://apps.topcoder.com/forums/?module=Thread&threadID=849040&start=0&mc=4

MySQL Scripts

• Provide scripts for all tables.

• Provide script to insert default users.

• Provide script to insert sample data

Yii Folder Structure

Follow the recommended folder structure by Yii Framework

Postman Client

Using Postman, create a json file for all calls with sample data.

Final Submission Guidelines

Deliverables

• All source code and script implementing the requirements.

• Detailed deployment document that includes the following :

  • Setup prerequisites (what software needed to be installed .. etc)

  • Folder structure

  • Configuration

  • Steps to run the application

  • Verification steps