Add "Average System Cost" column

[ghost]

Add another column to the Dashboard page that shows actual $/watt price values. This code was originally in the Beta and was replaced with the column that price categories (Value, Standard, Premium). So basically restore this column without removing the Price Category column. Add tooltip info to the new $/watt column.

[rlytvynchuk]

delegated to John to complete BE part. For this moment there's no 'cost per watt' property in list of installers JSON.

[ghost]

Added JSON data element to list of installers per request. No php code changes required -- just a stored query adjustment. The new data element and value example is: "avg_$\/DC":"3.62".

Because procedure query SQLis not version controlled in GitHub (it should be), I updated the getDashboardData stored procedure in production to include the new data element. NOTE: sql should be version controlled.

To get the procedure working in the localhost environment, please modify the getDashboardData stored procedure in to the MySQL torrohol_ca_csi database as follows:

SELECT i.id, i.name, i.logo, i.pricing, i.avg_$/DC, c.total_installs, i.yelp_rating, i.yelp_review_count FROM installer i, installer_county c WHERE c.county = grp AND c.license_number = i.contractor_license ORDER BY i.pricing DESC

[rlytvynchuk]

Hi, John

Seems like Sequel Pro for Mac and phpMyAdmin aren't able to edit routines (or I don't know how to do this). Please provide me and Svetlana the SQL query we can launch.

It's better to use SQL. Each of us has different apps for this so total time spend for such sync would be greater than time spend on SQL compose

Also, when I try to launch this query

SELECT i.id, i.name, i.logo, i.pricing, i.avg_$/DC, c.total_installs, i.yelp_rating, i.yelp_review_count FROM installer i, installer_county c WHERE c.county = grp AND c.license_number = i.contractor_license ORDER BY i.pricing DESC

I get error: Unknown column 'i.avg_$' in 'field list'

that's strange, 'cause table 'installer' has such column in my db

[ghost]

Roman,

I will rename the field label and send the new SQL query - the special characters are causing the problem. The field name is an artifact of the data load process from the CSI database every week. I'll also upload a snapshot of the installer table if you prefer to just import that instead of changing the field name in your local copy.

I've kept most of the SQL in stored procedures - by encapsulating the data call logic I hoped to make things easier. However, I can see how that complicates debugging. Would like to discuss this with you in the future from a design perspective.

[ghost]

The stored procedure getDashboardData('county') has the following new SQL:

SELECT i.id, i.name, i.logo, i.pricing, i.avg_cost, c.total_installs, i.yelp_rating, i.yelp_review_count FROM installer i, installer_county c WHERE c.county = grp AND c.license_number = i.contractor_license ORDER BY i.pricing DESC

Note that the field avg_$/DC has been renamed to avg_cost. The field name will need to be also changed in the local copy of the "installer" table in the torrohol_ca_csi database. I uploaded a copy of the new table to Dropbox (installer.sql) if you prefer to just import it instead.

NOTE:

1. Production push will also require a modification of the production database schema as well. Just pushing the code will cause the dashboard page to break.
2. Data load scripts must be modified and tested to use the new field name - this isn't a concern for the Ukraine team, just a note to self.

[ghost]

Roman,

Please see comments in #37. Problem was special characters in field name – renamed to avg_cost.

{"id":"19","name":"Sol-Tek Industries","logo":"soltek.jpg","pricing":"Value","avg_cost":"4.52","total_installs":"71","yelp_rating":"-","yelp_review_count":"-"}

From: rlytvynchuk [mailto:notifications@github.com] Sent: Saturday, July 13, 2013 2:39 AM To: solarisbetter/beta Cc: John Bissell Subject: Re: [beta] Add "Average System Cost" column (#37)

Hi, John

Seems like Sequel Pro for Mac and phpMyAdmin aren't able to edit routines (or I don't know how to do this). Please provide me and Svetlana the SQL query we can launch.

It's better to use SQL. Each of us has different apps for this so total time spend for such sync would be greater than time spend on SQL compose

Also, when I try to launch this query

SELECT i.id, i.name, i.logo, i.pricing, i.avg_$/DC, c.total_installs, i.yelp_rating, i.yelp_review_count FROM installer i, installer_county c WHERE c.county = grp AND c.license_number = i.contractor_license ORDER BY i.pricing DESC

I get error: Unknown column 'i.avg_$' in 'field list'

that's strange, 'cause table 'installer' has such column in my db

— Reply to this email directly or view it on GitHub https://github.com/solarisbetter/beta/issues/37#issuecomment-20917452 . https://github.com/notifications/beacon/CBJC5RaCelq8-xA_RAIm3xaaVS77YMcjxo5VGYtbo6rW26cmA9qAsl0xlSrpDYL3.gif

[rlytvynchuk]

All I needed is this SQL

DROP PROCEDURE IF EXISTS getDashboardData; DELIMITER ;; CREATE DEFINER=torrohol_nik@% PROCEDURE getDashboardData(grp VARCHAR(25)) SELECT i.id, i.name, i.logo, i.pricing, i.avg_cost, c.total_installs, i.yelp_rating, i.yelp_review_count FROM installer i, installer_county c WHERE c.county = grp AND c.license_number = i.contractor_license ORDER BY i.pricing DESC;; DELIMITER ;

Now it works

Svetlana, please

1. Connect to project DB as root
2. rename avg_$/DC column in Installer table to the 'avg_cost' (you can do it using your MySQL Workbench installed)
3. Execute SQL listed above

No need to restart Apache and MySQL

Assigned issue to myself

[rlytvynchuk]

Column added. Assigned to John to specify text of tooltip (now 'Average Cost')

[svlasenko]

Hi John,

I verified this issue in Trunk and Master to pushed everything to Github. I did a sanity testing so I think you can push from Master to production.