Arduan
Posts: 1
Joined: Sun Jun 10, 2018 1:10 pm

Watering system "Tryskacz"

Wed Jun 13, 2018 6:32 pm

Hi everyone. My name is Peter. I want to show you my first project based on Raspberry. This is my first experience with Raspberry and first steps in Python and Django ;-). I know, that not everything I wrote is "Phytonic", but it works, so if you find something that can be done better, please don`t laugh :D , tell me how to do it, I'll be gratefull. Sory for my english BTW.
Part I
Ok. So, "Tryskacz" in polish is not common word for "Sprinkler". Here it is....
https://drive.google.com/file/d/1aPoopy ... sp=sharing
https://drive.google.com/file/d/1EI1fRu ... sp=sharing
https://drive.google.com/file/d/1zfD8y7 ... sp=sharing
These photos are not last, I made small changes, I placed in this casing toroidal transformer (24v, 2,5A - one valve needs 24v and 0,4-0,6A) to supply solenoid valves, and I made one power suply wire for transformer and Pi.
Why I started this project? I have a big garden with 19 (now, but there may be more) watering sections - valves. I had previously 3 8-sections drivers made by one of the top manufacturers. It was difficult to set all drivers to not overlap watering times, but much more harder was to remember what and witch driver do. Now it's easy, and... all I need is configurable from anywhere.
As you see, I used RPi 3 B+ and 3 8-relay boards. Main idea was to be able to fire 3 section in the same time, so I decided to use relay boards without optoisolator (less power consumption 30mA as I remember vs. 100mA with optoisolator per relay). I planned to make "main switch" too, so another 30mA from Pi was nedeed.
Unfortunately, 5v relay boards need more than 3,3V ;-) but some people write, that it should work. Now I know that not, and I know why, but now that's not the point. I made a simple driver board like this:
https://drive.google.com/file/d/1HLa7XU ... sp=sharing
https://drive.google.com/file/d/1k9Akhw ... sp=sharing
https://drive.google.com/file/d/1mOMmsn ... sp=sharing
That's all about circuits, if you have more question, I'll answer with pleasure.
A little about working (control) conception, a lot of thanks for my friend Tomasz ;-)
SQL - contains all information about sections (valves and ways, types of watering), blocks (groups of valves), on-times, durations, and some of config data
cron - runs main python script every minute, checks sql and "fire" watering
web configurable - Django
master switch - cuts off transformer power supply only
rtc - if there is no internet connection
That's all for now, but one thing you should know. After reboot, some GPIO pins are set to HIGH, and open valves. Last pin (40) fortunately not, and this pin is for my master switch (but you can change it in SQL setup table). So, if you want to use all gpio pin as GPIO.ouptut, and avoid watering after reboot, you should run with cron (@reboot) little script, and use one of the "LOW pins" as master switch:

Code: Select all

import RPi.GPIO as GPIO
GPIO.setmode(GPIO.BOARD)
GPIO.setwarnings(False)
for mygpio in [8, 10, 11, 12, 13, 15, 16, 18, 19, 21, 22, 23, 24, 26, 29, 31, 32, 33, 35, 36, 37, 38, 40]:
    GPIO.setup(int(mygpio), GPIO.OUT)
    GPIO.output(int(mygpio), GPIO.LOW)
 
Where are my photos???? I will add them in other way....

Part II.
Few words about sql.
If you have MySQL Workbench, you can control all functions of Tryskacz very easy.
There are 4 tables, Setup, Sections, Block, BlockItem.
Setup:
https://drive.google.com/file/d/1LnDxkb ... sp=sharing
Main script (tryskacz.py) running from cron every minute, takes this setup options, and values from other tables:
sys_active - (0 or 1 - 0 - breaks script immediately)
ms_delay - delay in minutes, when transformer's power supply is cut off if there is no section (blocks) to fire in next XX mintes
sec_delay - delay between sections and section - master switch (in seconds)
ms_gpio - master switch GPIO number
ms_on - master switch on/off (if 0, script works, but there is no water, because trasformer has no power supplay)

Sections:
https://drive.google.com/file/d/1Fc3D6d ... sp=sharing
Is there something that should I explain?
There is a little error in name "sprinkles", I will correct this next time

Blocks:
https://drive.google.com/file/d/1fdsaln ... sp=sharing
It could be called Programs, but I prefer Blocks, because it is sections sequence, as you will see in next table.
I think, there is nothing to explain, but two columns, once and del_if_done
If once is 1, it means that script shoud run this block once and del_if_done (if 1) or deactivate it (if del_if_done = 0)

BlockItem:
https://drive.google.com/file/d/1LPI-KA ... sp=sharing
At first - block_id - this is the connection (join) to Blocks. All section with this block_id belongs to Blocks with this id.
So if you join both tables you will recive block start time, list of sections in this block and duration each one section.
If you combine all three tables, you will have all and clear information about what, where, when and how long.

As you see, with workbench you can take full control.

And now... This is main script (tryskacz.py)

Code: Select all

# -*- coding: utf-8 -*-

import datetime
import RPi.GPIO as GPIO
import mysql.connector
import operator
from optparse import OptionParser
import time
import sys
import copy


def parse_arguments():
        parser = OptionParser()
        parser.add_option("-t", dest = "mytime", help="Time hh:mm (optional)", metavar = "MYTIME", type = "string")
        parser.add_option("-w", dest = "mywday", help="Week day (optional)", metavar = "MYWDAY", type = "int")
        parser.add_option("-d", dest = "mymday", help="Month day (optional)", metavar = "MYMDAY", type = "int")
        (options, args) = parser.parse_args()

        if options.mymday:
            mymday = options.mymday
        else:
            mymday = int(datetime.date.today().strftime("%d"))

        if options.mytime:
                mydatetime = "{}-{} {}".format(datetime.datetime.now().strftime("%Y-%m"), mymday, options.mytime)
                mytime = options.mytime
        else:
                mydatetime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M")
                mytime = datetime.datetime.now().strftime("%H:%M")

        if options.mywday:
                mywday = options.mywday
        else:
                mywday = datetime.datetime.today().weekday() # 0 - monday, 6 - sunday


        mytime = str(mytime)
        mydatetime = str(mydatetime)
        mymday = int(mymday)
        mywday = int(mywday)
        return mywday, mytime, mydatetime, mymday

# get section name and gpio number from db
def get_section_data(section_id):
        query = "select name, gpio from sprinkles_section where id = {}".format(section_id)
        cursor2 = conn.cursor(buffered=True)
        cursor2.execute(query)
        data = cursor2.fetchone()
        name = data[0]
        gpio_x = data[1]
        return(name, gpio_x)

def query_sum(block_id): #get block duration, block_item count, max item number (order) from db
        querysum = "select sum(duration), count(duration), max(order_number) from sprinkles_blockitem where block_id = {}".format(block_id)
        cursor3 = conn.cursor(buffered=True)
        cursor3.execute(querysum)
        tempblocksum = cursor3.fetchone()
        blocksum=tempblocksum[0]
        blockcount=tempblocksum[1]
        blockmax=tempblocksum[2]
        #print(blocksum)
        return(block_id, blocksum, blockcount, blockmax)

# sometimes is needed to sort list of the lists
def sort_table(table, cols):
    """ sort a table by multiple columns
        table: a list of lists (or tuple of tuples) where each inner list
               represents a row
        cols:  a list (or tuple) specifying the column numbers to sort by
               e.g. (1,0) would sort by column 1, then by column 0
    """
    for col in reversed(cols):
        table = sorted(table, key=operator.itemgetter(col))
    return table



mywday, mytime, mydatetime, mymday = parse_arguments()
mytime = datetime.datetime.strptime(mytime, "%H:%M") #convert string to date, but it was old conception. Not used anymore.
mydatetime = datetime.datetime.strptime(mydatetime, "%Y-%m-%d %H:%M") #actual date or date from arguments. It is needed

# sql connecton
conn = mysql.connector.connect(user='username',database='database',password='password')
cursor = conn.cursor(buffered=True)

###############setup
setuplist = []
querysetup = "select * from sprinkles_setup"
cursor.execute(querysetup)
for mysetup in cursor:
    setuplist.append(list(mysetup))
for x in range(0, int(len(setuplist))):
    exec("%s = %d" % (setuplist[x][1] , setuplist[x][2] ))

print ("Date and time: {}, Weekday: {}".format(mydatetime, mywday)) # display "now" date, time and weekday

GPIO.setmode(GPIO.BOARD) # set GPIO to board numbering system
GPIO.setwarnings(False) # no display warnings

#pins 1, 3, 5, 7, 9 (gpio 3, 5, 7) used by RTC
if sys_active == 0:
    print("System is OFF. Set all GPIO to LOW. Stop.")
    for mygpio in [8, 10, 11, 12, 13, 15, 16, 18, 19, 21, 22, 23, 24, 26, 29, 31, 32, 33, 35, 36, 37, 38, 40]:
        GPIO.setup(int(mygpio), GPIO.OUT)
        GPIO.output(int(mygpio), GPIO.LOW)
    exit()

if ms_on == 0:
    print("Master switch OFF. Continue...")
else:
    print("Master switch ON. Continue...")
##############

# query all active blocks and "jojned" blockitem
query = "select sprinkles_block.*, sprinkles_blockitem.* from sprinkles_block, sprinkles_blockitem where sprinkles_block.id = sprinkles_blockitem.block_id and sprinkles_block.active = 1 order by sprinkles_blockitem.order_number, sprinkles_block.id"
cursor.execute(query)


if cursor.rowcount == 0: #jeśli nic nie pobrano
    print("Nie ma aktywnych bloków, wyłączam wszystko, kończę program")
    for mygpio in [8, 10, 11, 12, 13, 15, 16, 18, 19, 21, 22, 23, 24, 26, 29, 31, 32, 33, 35, 36, 37, 38, 40]:
        GPIO.setup(int(mygpio), GPIO.OUT)
        GPIO.output(int(mygpio), GPIO.LOW)
    exit()


#print(cursor.rowcount)
dayoffset = 0
actstop = ""
startlist = []
startlisttemp = []
periodlist = []
templist = []

# 0 - poniedziałek, 6 - niedziela
# z cursor robię listę, żeby ją opracować
for period in cursor:
    startlisttemp.append(list(period)) #każdego tupla trzeba zamienić na listę
    #print(period)


startlisttemp = [x + [[0],[0],[0],[0],[0],[0],[0],[0]] for x in startlisttemp] #przydadzą się nowe kolumny 23, 24, 25, 26, 27, 28, 29, 30
#0-16 - sprinkles_newblock
    #0 - id == #18
    #1 - name
    #2 - description
    #3 - start
    #4 - active
    #5 - mo
    #6 - tu
    #7 - we
    #8 - th
    #9 - fr
    #10 - sa
    #11 - su
    #12 - even (par)
    #13 - odd (npar)
    #14 - each
    #15 - once
    #16 - last_run
    #17 - del_if_done
#18-22 - sprinkles_blockitem
    #18 - id
    #19 - order_number
    #20 - duration
    #21 - block_id == #0
    #22 - section_id

#23 - czas stratu blockitem z datą
#24 - czas stopu blockitem z datą
#25 - czas startu bloku z datą
#26 - czas trwania bloku
#27 - czas stopu bloku z datą
#28 - count in block
#29 - max order in block
#30 - niezależna numeracja itemów, gdyby w sql był ten sam order_number

thisday = "{}-{}".format(datetime.datetime.now().strftime("%Y-%m"), mymday)

############################opracowywuję listę
startlisttemp = sort_table(startlisttemp,(21,19)) #sortuję po kolejności w bloku

block_id = 0
blockduration = 0
blockcount = 0
blockmax = 0
blockitem = 0

for x in range(0, int(len(startlisttemp))):
    #print(startlisttemp[x][21])
    if block_id != startlisttemp[x][21]:
        (block_id, blockduration, blockcount, blockmax)=query_sum(startlisttemp[x][21])
        blockitem=1
    #w [16] jest ostatni czas startu sekcji zapisany w formacie RRRR-MM-DD HH:MM:SS i jest stringiem, może też go nie być czyli 0 dlatego:
    try:
        startlisttemp[x][16] = (datetime.datetime.strptime(startlisttemp[x][16], "%Y-%m-%d %H:%M:%S"))
    except (TypeError, ValueError):
        pass

    startlisttemp[x][26] = int(blockduration)
    startlisttemp[x][28] = int(blockcount)
    startlisttemp[x][29] = int(blockmax)
    startlisttemp[x][30] = blockitem #niezależna numeracja itemów, gdyby w sql był ten sam order_number
    blockitem +=1

startlist = copy.deepcopy(startlisttemp)
templist = copy.deepcopy(startlist)


for x in range(0, int(len(startlist))):

    if startlist[x][30] == 1: #początek nowego bloku
        start=datetime.datetime.strptime("{} {}".format(thisday, startlist[x][3]), "%Y-%m-%d %H:%M") + datetime.timedelta(minutes=0)
        blockstart = datetime.datetime.strptime("{} {}".format(thisday, startlist[x][3]), "%Y-%m-%d %H:%M") + datetime.timedelta(minutes=0)
        inblock = startlist[x][21]

    stop=start + datetime.timedelta(minutes=startlist[x][20])

    if startlist[x][14] == 1: #each
        for y in range(0,2): #buduję dla wczoraj i dzisiaj
            dayoffset = 0 - y
            tempstart=start + datetime.timedelta(days=dayoffset)
            tempstop=stop + datetime.timedelta(days=dayoffset)
            startlist[x][23] = tempstart
            startlist[x][24] = tempstop
            startlist[x][25] = blockstart + datetime.timedelta(days=dayoffset)
            startlist[x][27] = startlist[x][25] + datetime.timedelta(minutes=startlist[x][26])
            templist = copy.deepcopy(startlist)
            if templist[x][27] >= mydatetime: #nie ma sensu tworzyć periodów których stop jest wcześniej mydatetime
                periodlist.append(templist[x])

    if startlist[x][13] == 1: #npar, jeśli dzisiaj jest parzysty to buduję na wczoraj, jeśli nieparzysty na dzisiaj
        if mymday % 2 == 0:
            dayoffset = -1
        else:
            dayoffset = 0
        tempstart=start + datetime.timedelta(days=dayoffset)
        tempstop=stop + datetime.timedelta(days=dayoffset)
        startlist[x][23] = tempstart
        startlist[x][24] = tempstop
        startlist[x][25] = blockstart + datetime.timedelta(days=dayoffset)
        startlist[x][27] = startlist[x][25] + datetime.timedelta(minutes=startlist[x][26])
        templist = copy.deepcopy(startlist)
        if templist[x][27] >= mydatetime: #nie ma sensu tworzyć periodów których stop jest wcześniej mydatetime
            periodlist.append(templist[x])

    if startlist[x][12] == 1: #par, jeśli dzisiaj nieparzysty, to buduję dla wczoraj, jeśli parzysty, na dzisiaj
        if mymday % 2 == 0:
            dayoffset = 0
        else:
            dayoffset = -1
        tempstart=start + datetime.timedelta(days=dayoffset)
        tempstop=stop + datetime.timedelta(days=dayoffset)
        startlist[x][23] = tempstart
        startlist[x][24] = tempstop
        startlist[x][25] = blockstart + datetime.timedelta(days=dayoffset)
        startlist[x][27] = startlist[x][25] + datetime.timedelta(minutes=startlist[x][26])
        templist = copy.deepcopy(startlist)
        if templist[x][27] >= mydatetime: #nie ma sensu tworzyć periodów których stop jest wcześniej mydatetime
            periodlist.append(templist[x])

    if startlist[x][mywday+5] == 1: #jeśli dla dzisiejszego mywday jest 1 to buduję na dzisiaj
        dayoffset = 0
        tempstart=start + datetime.timedelta(days=dayoffset)
        tempstop=stop + datetime.timedelta(days=dayoffset)
        startlist[x][23] = tempstart
        startlist[x][24] = tempstop
        startlist[x][25] = blockstart + datetime.timedelta(days=dayoffset)
        startlist[x][27] = startlist[x][25] + datetime.timedelta(minutes=startlist[x][26])
        templist = copy.deepcopy(startlist)
        if templist[x][27] >= mydatetime: #nie ma sensu tworzyć periodów których stop jest wcześniej mydatetime
            periodlist.append(templist[x])

    if startlist[x][mywday -1 + 5] == 1: #jeśli coś było poprzedniego wday, to muszę sprawdzić czy nie przechodzi z wczoraj na dzisiaj
        dayoffset = -1
        tempstart=start + datetime.timedelta(days=dayoffset)
        tempstop=stop + datetime.timedelta(days=dayoffset)
        startlist[x][23] = tempstart
        startlist[x][24] = tempstop
        startlist[x][25] = blockstart + datetime.timedelta(days=dayoffset)
        startlist[x][27] = startlist[x][25] + datetime.timedelta(minutes=startlist[x][26])
        templist = copy.deepcopy(startlist)
        if templist[x][27] >= mydatetime: #nie ma sensu tworzyć periodów których stop jest wcześniej mydatetime
            periodlist.append(templist[x])


    if startlist[x][12] == 0 and startlist[x][13] == 0 and startlist[x][14] == 0 and startlist[x][mywday+5] == 0 and startlist[x][mywday -1 + 5] == 0:
        pass
        #dayoffset = 365
        #tempstart=start + datetime.timedelta(days=dayoffset)
        #tempstop=stop + datetime.timedelta(days=dayoffset)
        #startlist[x][23] = tempstart
        #startlist[x][24] = tempstop
        #startlist[x][25] = blockstart + datetime.timedelta(days=dayoffset)
        #startlist[x][27] = startlist[x][25] + datetime.timedelta(minutes=startlist[x][26])
        #templist = copy.deepcopy(startlist)
        #if templist[x][27] >= mydatetime: #nie ma sensu tworzyć periodów których stop jest wcześniej mydatetime
            #periodlist.append(templist[x])

    start=stop


if int(len(periodlist)) == 0: #jeśli nic nie ma
    print("Nie utworzono periodów, wyłączam wszystko, kończę program")
    for mygpio in [8, 10, 11, 12, 13, 15, 16, 18, 19, 21, 22, 23, 24, 26, 29, 31, 32, 33, 35, 36, 37, 38, 40]:
        GPIO.setup(int(mygpio), GPIO.OUT)
        GPIO.output(int(mygpio), GPIO.LOW)
    exit()


periodlist = sort_table(periodlist,(23,23)) #sortuję po czasie startu

actcount = 0

GPIO.setup(ms_gpio, GPIO.OUT) # GPIO mainswitcha
##################### Uruchamiam sekcje
for x in range(0, int(len(periodlist))):
    #print("{}    {}    {}    {}    {}".format(periodlist[x][23], periodlist[x][24], periodlist[x][25], periodlist[x][22], periodlist[x][27]))
    if periodlist[x][25] <= mydatetime and mydatetime <= periodlist[x][27]: # wpuszczam cały blok spełniający warunek
        #print("{}    {}    {}    {}    {}".format(periodlist[x][23], periodlist[x][24], periodlist[x][25], periodlist[x][22], periodlist[x][27]))

        s_id = periodlist[x][22]
        (s_name, gpio_id) = get_section_data(s_id)
        GPIO.setup(int(gpio_id), GPIO.OUT)

        #najpierw wyłącz, potem włacz
        #to wyłączenie zadziała, jeśli nie było blackoutu, blackout i tak wyłączy
        if periodlist[x][23] > mydatetime or mydatetime >= periodlist[x][24]: # taka sekcja nie powinna działać
            if GPIO.input(int(gpio_id)) == 1: # jeśli jest uruchomiona, wyłączam
                GPIO.output(int(gpio_id),GPIO.LOW)
                print ("Program: {} Sekcja: {} (id: {}) na gpio {} była włączona. Wyłączono sekcję".format(periodlist[x][1], s_name, s_id, gpio_id))

            if periodlist[x][15] == 1 and periodlist[x][30] == periodlist[x][28] and periodlist[x][27] <= mydatetime:
                #jeśli był jednorazowy i ostatni blockitem z bloku i czas stopu bloku równy (lub mniejszy) od mydatetime
                cursor3 = conn.cursor(buffered=True)
                ##updquery = "DELETE FROM sprinkles_period WHERE id={}".format(startlist[x][0])
                if periodlist[x][17] == 0:
                    updquery = "UPDATE sprinkles_block SET active='0' WHERE id={}".format(periodlist[x][0])
                    cursor3.execute(updquery)
                    conn.commit()
                    myaction = "Deaktywowano"
                else:
                    #updquery = "DELETE FROM sprinkles_blockitem WHERE id={}".format(startlist[x][0])
                    updquery = "DELETE from sprinkles_blockitem where block_id={}".format(periodlist[x][0])
                    cursor3.execute(updquery)
                    conn.commit()
                    updquery = "DELETE from sprinkles_block where id={}".format(periodlist[x][0])
                    cursor3.execute(updquery)
                    conn.commit()
                    myaction = "Usunięto"

                print("{} jednorazowy period {}".format(myaction, periodlist[x][0]))


        if periodlist[x][23] <= mydatetime and mydatetime < periodlist[x][24]: # wpuszczam tylko blockitem (sekcję) spełniającą warunek
            actcount = actcount + 1 # obliczam ilość uruchomionych sekcji
            if GPIO.input(int(gpio_id)) == 1: #jeśli jest uruchomiona
                print ("Program: {} Sekcja: {} (id: {}) na gpio: {} podlewa. Planowany stop: {}".format(periodlist[x][1], s_name, s_id, gpio_id, periodlist[x][24]))
            else: #jeśli z jakiegoś powodu nie była

                if ms_on == 1: #suche testy
                    GPIO.output(ms_gpio, GPIO.HIGH)
                    time.sleep(sec_delay)
                else:
                    GPIO.output(ms_gpio, GPIO.LOW)

                GPIO.output(int(gpio_id), GPIO.HIGH)
                print ("Program: {} Sekcja: {} (id: {}) na gpio: {} była wyłączona. Włączono sekcję. Planowany stop: {}".format(periodlist[x][1], s_name, s_id, gpio_id, periodlist[x][24]))
                if periodlist[x][16] != periodlist[x][25]: #25 przechowuje czas startu bloku z datą startu, a skoro został tu wpuszczony to spełniał warunek
                    # sprawdzam tylko czy był uruchomiony, nie sprawdzam czy pierwszy blockitem, bo po blackoucie może i tak nie być od początku
                    cursor3 = conn.cursor(buffered=True)
                    updquery = "UPDATE sprinkles_block SET last_run='{}' WHERE id={}".format(periodlist[x][25], periodlist[x][0])
                    cursor3.execute(updquery)
                    conn.commit()

                if periodlist[x][15] == 1: #informacyjnie
                    print("Program {} uruchomiono jednorazowo".format(periodlist[x][1]))

                actstop = periodlist[x][24] # zapamiętuję kiedy zatrzymuje się ta sekcja


################# Ustalam start następnej sekcji
for x in range(0, int(len(periodlist))):
    if periodlist[x][23] - mydatetime > datetime.timedelta(seconds=0):
        periodlist[x][1] = periodlist[x][23] - mydatetime # obliczam różnice od teraz do startu i zapisuję w niepotrzebnej już periodlist
    else:
        periodlist[x][1] = datetime.timedelta(days=8)

periodlist = sort_table(periodlist,(1,1)) #sortuję po różnicy czasu

if actcount == 0: # Na wszelki wypadek
    print("Aktualny czas nie pasuje do żadnego periodu. Wyłączam wszystkie sekcje.")
    for mygpio in [8, 10, 11, 12, 13, 15, 16, 18, 19, 21, 22, 23, 24, 26, 29, 31, 32, 33, 35, 36, 37, 38]:
        GPIO.setup(int(mygpio), GPIO.OUT)
        GPIO.output(int(mygpio), GPIO.LOW)
    if periodlist[0][1] > datetime.timedelta(minutes=ms_delay) or periodlist[0][1] < datetime.timedelta(minutes=0):
        print("Start następnej sekcji za więcej niż {} minut. Wyłączam transformator.".format(ms_delay))
        GPIO.setup(ms_gpio, GPIO.OUT)
        GPIO.output(ms_gpio, GPIO.LOW)
else:
    print("Sekcje aktywne: {}.".format(actcount))
    print("Teraz: {}, Stop aktywnej: {}, Start następnej: {}, Start za: {}.".format(mydatetime, actstop, periodlist[0][23], periodlist[0][1]))
Sory for "sprinkles", a lot of unnecessary code and my comments in polish. I will expalin and translate it by the way.

Return to “Automation, sensing and robotics”

Who is online

Users browsing this forum: No registered users and 14 guests