Tag: archive

Getting TED and Enphase into MariaDB

I want to get my TED and Envoy data into MariaDB. dont know how or what I am going to use it for yet, but I know I will use it. Just need to learn more.
I used PHPMyAdmin to create a database, I called it House, I then created 2 tables one called TED and one called ENPHASE and gave a new user call house full access to it.
I then created a shell script for TED to get data into it:
#!/bin/bash

#lets get in the right directory

cd /etc/ted/

#remove old csv

rm TED.csv

#Now we get the data we need and get it into a number

RAW=$(lynx -dump http://ted6000/stats.htm | grep '(W)' | sed -r 's/s+//g'| cut -d":" -f2)

#make it kW and put it in a file I can use for other things

/usr/bin/echo "scale=3; $RAW/1000" | bc -l >> TED.csv

#Import data the csv file name has to be the same name and case as the table you are importing to.

mysqlimport --local -u house --password=password House --columns=KW TED.csv

##And the one for the Envoy:
#!/bin/bash

cd /etc/enphase

#remove old stuff

rm ENPHASE.csv
rm kw2.txt
rm kw.txt

#Get the data ready for a csv for MariaDB

lynx -dump http://solar.hohenfels.com/production?locale=en | grep ly > kw.txt

# The Enphase Envoy uses Kilowatts (kW) and Watts (W). So a little creativity is required: If I find kW, clean it up, add the date and dump it in the CSV. If I dont find kW then clean it up and put it in a text file called kw2.txt. If it is not kW then it has to be W. when not producing it shows 0W

if grep -q 'kW' "kw.txt"; then
cat kw.txt | sed 's/[A-Za-z,]*//g' | sed -r 's/s+//g' >> ENPHASE.csv
else
cat kw.txt | sed 's/[A-Za-z,]*//g' | sed -r 's/s+//g' > kw2.txt
fi
# Make the kw2.txt file a variable
KW2=$(cat kw2.txt)

# Do some math and get our variable into kW and dump it in the CSV.

/usr/bin/echo "scale=3; $KW2/1000" | bc -l >> ENPHASE.csv

#Import data file name has to be the same name and case as the table you are importing to.

mysqlimport --local -u house --password=password House --columns=KW ENPHASE.csv

I now have my MariaDB populating with date from my TED and Enphase Envoy

TED, Meet Enphase..

Now that we are getting graphs from both TED and Envoy I wanted to combine them to get a graphical representation of production vs. Usage. The heavy lifting is done, just need to stuff it into a graph and cron it:
#!/bin/bash#Create the combined daily graph

rrdtool graph /var/www/html/solar/combine_day.png -s -1day DEF:ted_watts=/etc/ted/watts/ted_watts.rrd:temp:AVERAGE LINE1:ted_watts#FF9900 -h 400 -w 600 -y1:2 --color GRID#dddddd --color MGRID#aaaaaa DEF:enphase=/etc/enphase/enphase.rrd:temp:AVERAGE LINE1:enphase#FF0000 -h 400 -w 600 -y1:2 --color GRID#dddddd --color MGRID#aaaaaa

#Create the combined weekly graph

rrdtool graph /var/www/html/solar/combine_week.png -s -1week DEF:ted_watts=/etc/ted/watts/ted_watts.rrd:temp:AVERAGE LINE1:ted_watts#FF9900 -h 400 -w 600 -y1:2 --color GRID#dddddd --color MGRID#aaaaaa DEF:enphase=/etc/enphase/enphase.rrd:temp:AVERAGE LINE1:enphase#FF0000 -h 400 -w 600 -y1:2 --color GRID#dddddd --color MGRID#aaaaaa

#Create the combined monthly graph

rrdtool graph /var/www/html/solar/combine_month.png -s -1month DEF:ted_watts=/etc/ted/watts/ted_watts.rrd:temp:AVERAGE LINE1:ted_watts#FF9900 -h 400 -w 600 -y1:2 --color GRID#dddddd --color MGRID#aaaaaa DEF:enphase=/etc/enphase/enphase.rrd:temp:AVERAGE LINE1:enphase#FF0000 -h 400 -w 600 -y1:2 --color GRID#dddddd --color MGRID#aaaaaa

#Create the combined yearly graph

rrdtool graph /var/www/html/solar/combine_year.png -s -1year DEF:ted_watts=/etc/ted/watts/ted_watts.rrd:temp:AVERAGE LINE1:ted_watts#FF9900 -h 400 -w 600 -y1:2 --color GRID#dddddd --color MGRID#aaaaaa DEF:enphase=/etc/enphase/enphase.rrd:temp:AVERAGE LINE1:enphase#FF0000 -h 400 -w 600 -y1:2 --color GRID#dddddd --color MGRID#aaaaaa

# All the rrd sctipts we created need to go into roots crontab. I have everything run a 5 minute intervals

*/5 * * * * /usr/local/bin/ted_watts.sh > /dev/null 2>&1
*/5 * * * * /usr/local/bin/enphase.sh > /dev/null 2>&1; /usr/local/bin/combined_solar.sh > /dev/null 2>&1

##Here is the final daily product. TED is Orange, Enphase is Red.

Daily:

Weekly:

Month:

# Here it is with no mark-up
#!/bin/bash#Create the combined daily graph
rrdtool graph /var/www/html/solar/combine_day.png -s -1day DEF:ted_watts=/etc/ted/watts/ted_watts.rrd:temp:AVERAGE LINE1:ted_watts#FF9900 -h 400 -w 600 -y1:2 –color GRID#dddddd –color MGRID#aaaaaa DEF:enphase=/etc/enphase/enphase.rrd:temp:AVERAGE LINE1:enphase#FF0000 -h 400 -w 600 -y1:2 –color GRID#dddddd –color MGRID#aaaaaa

#Create the combined weekly graph
rrdtool graph /var/www/html/solar/combine_week.png -s -1week DEF:ted_watts=/etc/ted/watts/ted_watts.rrd:temp:AVERAGE LINE1:ted_watts#FF9900 -h 400 -w 600 -y1:2 –color GRID#dddddd –color MGRID#aaaaaa DEF:enphase=/etc/enphase/enphase.rrd:temp:AVERAGE LINE1:enphase#FF0000 -h 400 -w 600 -y1:2 –color GRID#dddddd –color MGRID#aaaaaa

#Create the combined monthly graph
rrdtool graph /var/www/html/solar/combine_month.png -s -1month DEF:ted_watts=/etc/ted/watts/ted_watts.rrd:temp:AVERAGE LINE1:ted_watts#FF9900 -h 400 -w 600 -y1:2 –color GRID#dddddd –color MGRID#aaaaaa DEF:enphase=/etc/enphase/enphase.rrd:temp:AVERAGE LINE1:enphase#FF0000 -h 400 -w 600 -y1:2 –color GRID#dddddd –color MGRID#aaaaaa

#Create the combined yearly graph
rrdtool graph /var/www/html/solar/combine_year.png -s -1year DEF:ted_watts=/etc/ted/watts/ted_watts.rrd:temp:AVERAGE LINE1:ted_watts#FF9900 -h 400 -w 600 -y1:2 –color GRID#dddddd –color MGRID#aaaaaa DEF:enphase=/etc/enphase/enphase.rrd:temp:AVERAGE LINE1:enphase#FF0000 -h 400 -w 600 -y1:2 –color GRID#dddddd –color MGRID#aaaaaa

Elgin Side Awning – The Big Cover Up

Here is the front “big” awning. We attached a frame for the awning to a metal building the same way we did the other awning; We used the self sealing metal roof screws to attach the brackets to the side. The brackets are screwed into the 2×6’s used to frame the building. VERY secure!
I wanted to show a better shot of how we did the footings with the 4×4 brackets. We used the 12 inch round forms 2 feet long with 2 1/2 bag cement each
The framing is pretty much the same as the other awning, except the cross beam 2×4’s are set in between the 2×6’s and we used metal braces to help support:
Here is a shot with all the framing done and the brackets on.
Started attaching the metal roofing:
Here is a shot with all the roofing on:
Still need to add the end and side flashing. Will do the electrical next

The Elgin Front Awning – Just a little cover-up

My side walk thru door was getting hammered with rain creating the potential for mold. To help keep everything dry, we built an awning.
I could not find info on how to attach a frame for an awning to a metal building, here is what we did. I used the self sealing metal roof screws to attach the brackets to the side. The brackets are screwed into the 2×6’s used to frame the building. VERY secure!
Then we drilled holes and bolted the 4×4’s to the brackets set in concrete and started framing.
For extra support I cut and drilled 4×4’s for side support:
Then finished up the framing:
Here it is with the metal sheets attached.
Still need to add the end and side flashing, but in my opinion the big part is done.

Elgin Shop – Getting all the storage I can – Under the Out Feed Table

I noticed unused storage space under my out feed table. So I made some simple boxes to store what I can in the unused space.
I started by cutting 3 – 2 foot by 4 foot bases out of some left over plywood I had laying around. Then I screwed on some 2×6 decking boards:
Then put on 4 2 inch casters from Harbor Freight:
I got some storage containers from the Dollar Tree filled them up and labeled them:
Then slid it into place:

Elgin Shop – Breaking it down – Plywood break down station

I needed a better way to break down sheet goods. Something I could put and manage sheet goods on alone. I decided to use the Shop Notes 48 tilt out panel cutting guide. Simple and cost effective.
First I cut everything to size and made a couple test dado cuts to make sure everything will fit:

Then fit it all together. I used the clamps to help guide things into place:

Screws all the way around. I added a screw in the center of each joint to help keep things snug. The screws are on the back I dont want to hit one with mysaw blade on accident.
Next was making the cutting the Red Oak to support the T-track. I could not find any 2″ x 2″ so I glued 1″ x 4″ together. After they dried I cut cut them to get the stock I needed.
Then ran a 3/4″ wide 1/2″ deep dado to hold the T-Track and mounted them to the sides of my frame.
Set it up on the outfeed table to help me get to things a little easier. Mounted up the bottom sheet supports.
Mounted the wall support board using 3 1/2″ lag bolts, added the second row of sheet supports and mounted it to the wall.
I mounted 12″ Red Oak legs to push the bottom away from the wall and create an angle that will help the sheet good stay in place.
Last shot. With the track installed and vertical fence attached:
My version of this will never sit flush against the wall. I plan to build a lumber storage roll around that will park itself in front of the plywood cutting station.
Copy Protected by Chetan's WP-Copyprotect.