예제 #1
0
def create_strategy(ref_type, area, description, days):
    try:
        c = d.cursor()
        
        statement = '''INSERT INTO Strategy(ref_type_id, area, description)
        VALUES(%s, "%s", "%s");''' % (ref_type, area, description)
        c.execute(statement)
        d.commit()
        statement = '''SELECT strategy_id FROM Strategy
        WHERE area = "%s" and ref_type_id = %s;''' % (area, ref_type)
        c.execute(statement)
        rows = c.fetchall()
        for row in rows:
            areaid = row[0]
        print "areaid"
        print areaid
        print ref_type
        print days
        for day in days:
            statement = '''INSERT INTO Rota (ref_strategy_id,
            day) VALUES
            (%s, %s);''' % (areaid, day)
            c.execute(statement)
            d.commit()
            print "rota added"
        return True
    except:
        return False
예제 #2
0
def get_complete():
    complete = {}
    c = d.cursor()
    statement = '''SELECT COUNT(Batch_id) FROM Food_Processing
        where Activity = "Cooking";'''
    c.execute(statement)
    i = c.fetchall()
    if i == 1:
        complete['Cooking'] = True
    else:
        complete['Cooking'] = False

    statement = '''SELECT COUNT(Batch_id) FROM Food_Processing
        where Activity = "Cooling";'''
    c.execute(statement)
    i = c.fetchall()
    if i == 1:
        complete['Cooling'] = True
    else:
        complete['Cooling'] = False
    
    statement = '''SELECT COUNT(Batch_id) FROM Food_Processing
        where Activity = "Hot Hold";'''
    c.execute(statement)
    i = c.fetchall()
    if i == 1:
        complete['HotHold'] = True
    else:
        complete['HotHold'] = False
    
    return complete
예제 #3
0
def get_user_stat(u_id):
    user_stat = {}
    c = d.cursor()
    statement = '''select count(Ref_Staff_id) from Temperature 
        where Ref_Staff_id ="%s" and date(datetime) between 
        date(CURDATE())-INTERVAL 30 DAY AND CURDATE()
        ;''' % (u_id)
    c.execute(statement)
    rows = c.fetchall()
    for row in rows:
        user_stat['Temperature'] = row[0]

    statement = '''select count(Ref_Staff_id) from Checks c
        where Ref_Staff_id = "%s" and ref_type_id = 1 and 
        date(c.date) between
        date(CURDATE())-INTERVAL 30 DAY AND CURDATE()
        ;''' % (u_id)
    c.execute(statement)
    rows = c.fetchall()
    for row in rows:
        user_stat['Pest'] = row[0]

    statement = '''select count(Ref_Staff_id) from Checks c 
        where Ref_Staff_id = "%s" and ref_type_id=2 and 
        DATE(c.date) BETWEEN
        DATE(CURDATE())-INTERVAL 30 DAY AND CURDATE()
        ;''' % (u_id)
    c.execute(statement)
    rows = c.fetchall()
    for row in rows:
        user_stat['Cleaning'] = row[0]

    return user_stat
예제 #4
0
def validate(firstname, lastname, email, passwd):
    c = d.cursor()
    statement = '''select staff_id from Staff
        where first_name ="%s" and last_name ="%s";'''\
        % (firstname, lastname)
    try:
        c.execute(statement)
        rows = c.fetchall()
        for row in rows:
            staff_id = int(row[0])
        print staff_id
        statement = '''select count(ref_staff_id) from
            Login where ref_staff_id = "%s";''' % (staff_id)
        c.execute(statement)
        rows = c.fetchall()
        for row in rows:
            num = int(row[0])
        if num == 0:
            passwd = generate_password_hash(passwd)
            statement = '''INSERT INTO Login VALUES
                ("%s","%s","%s");''' % (email, passwd, staff_id)
            c.execute(statement)
            d.commit()
            return True
        return False
    except:
        return False
예제 #5
0
def get_user_contract(u_id):
    c = d.cursor()
    statement = '''SELECT contract from Staff
    where Staff_id = %s;''' % (u_id)
    c.execute(statement)
    rows = c.fetchall()
    for row in rows:
        return row[0]
예제 #6
0
def get_name(u_id):
    c = d.cursor()
    statement = '''SELECT First_Name from Staff
    where Staff_id = %s;''' % (u_id)
    c.execute(statement)
    rows = c.fetchall()
    for row in rows:
        return row[0]
예제 #7
0
def get_admin(u_id):
    c = d.cursor()
    statement = '''SELECT Admin FROM Login WHERE
    Ref_Staff_Id = %s;''' % (u_id)
    c.execute(statement)
    rows = c.fetchall()
    for row in rows:
        return row[0]
예제 #8
0
def get_email(u_id):
    c = d.cursor()
    statement = '''select email from Login
    WHERE ref_staff_id = %s;''' % (u_id)
    c.execute(statement)
    rows = c.fetchall()
    for row in rows:
        return row[0]
예제 #9
0
def get_user_id(email):
    c = d.cursor()
    statement = "SELECT Staff_Id FROM Staff join Login\
    ON Staff_id = Ref_Staff_id\
    WHERE email = '%s';" % (email)
    c.execute(statement)
    rows = c.fetchall()
    for row in rows:
        return row[0]
예제 #10
0
def set_notes(u_id, notes):
    c = d.cursor()
    statement = '''delete from Notes where
    Ref_Staff_id = %s;''' % (u_id)
    c.execute(statement)
    
    statement = '''INSERT INTO Notes VALUES (%s, CURRENT_TIMESTAMP,
    "%s");''' % (u_id, notes)
    c.execute(statement)
    d.commit()
예제 #11
0
def get_devices():
    c = d.cursor()
    statement = '''SELECT Appliance_id, temp_stat from Appliance
    WHERE CURRENT = True and temp_stat != "Hot";'''
    c.execute(statement)
    rows = c.fetchall()
    devices = {}
    for row in rows:
        devices[row[0]] = {}
        devices[row[0]]["device"] = row[0]
        devices[row[0]]["range"] = row[1]
    return devices
예제 #12
0
def get_notes(u_id):
    c = d.cursor()
    statement = '''SELECT notes FROM Notes n
    WHERE Ref_Staff_id = %s and DATE(n.date) =
    DATE(CURRENT_TIMESTAMP);''' % (u_id)
    c.execute(statement)
    rows = c.fetchall()
    for row in rows:
        notes = row[0]
    if notes:
        return notes
    else:
        return "failed"
예제 #13
0
def get_count_remaining_tasks(ref_type):
    c = d.cursor()
    statement = '''SELECT COUNT(s.Strategy_id) FROM Strategy s JOIN
    Rota r on  s.strategy_id = r.Ref_Strategy_Id LEFT JOIN Checks c 
    ON c.Ref_Strategy_Id = s.Strategy_id
    WHERE live=true AND Day=DAYOFWEEK(CURRENT_TIMESTAMP)
    AND s.ref_type_id =%s AND s.Strategy_id NOT IN (SELECT Ref_strategy_id
    FROM Checks c WHERE DATE(c.date) = DATE(CURRENT_TIMESTAMP));''' % (ref_type)
    c.execute(statement)
    rows = c.fetchall()
    for row in rows:
        count = row[0]
    return count
예제 #14
0
    def validate(self, passwd, email):

        c = d.cursor()
        statement = '''select password from Login
            where email ="%s";'''\
            % (email)
        try:
            c.execute(statement)
            rows = c.fetchall()
            for row in rows:
                password = row[0]
            if check_password_hash(password, passwd):     
                return True
        except:
            return False
예제 #15
0
def get_overview(ref_type):
        c = d.cursor()
        overview = {}
        statement = '''SELECT s.strategy_id, s.area, s.description, c.ref_staff_id
        FROM Strategy s join Rota r ON s.strategy_id=r.ref_strategy_id
        LEFT JOIN Checks c on c.ref_strategy_id = s.strategy_id
        WHERE DAYOFWEEK(CURRENT_TIMESTAMP) = r.day and s.ref_type_id = %s
        AND s.strategy_id not in (select ref_strategy_id from Checks c where DATE(c.date)<
        DATE(CURRENT_TIMESTAMP));'''\
        % (ref_type)
        c.execute(statement)
        rows = c.fetchall()
        for row in rows:
            overview[row[0]] = {}
            overview[row[0]]["area_id"] = row[0]
            overview[row[0]]["area"] = row[1]
            overview[row[0]]["description"] = row[2]
            overview[row[0]]["staff_id"] = row[3]
        return overview
예제 #16
0
def get_todays_tasks(ref_type):
    try:
        c = d.cursor()
        tasks = {}
        statement = '''SELECT strategy_id, Area FROM Strategy JOIN
        Rota ON strategy_id = Ref_Strategy_Id
        WHERE live=true and Day=DAYOFWEEK(CURRENT_TIMESTAMP)
        and ref_type_id =%s;''' % (ref_type)

        c.execute(statement)
        rows = c.fetchall()
        for row in rows:
            tasks[row[0]] = {}
            tasks[row[0]]['area_id'] = row[0]
            tasks[row[0]]['area'] = row[1]

        return tasks
    except:
        return "None"
예제 #17
0
def create_check(ref_type, area, comment, ref_staff_id):
    print ref_type
    print area
    print comment
    print int(ref_staff_id)
    try:
        c = d.cursor()
        statement = '''SELECT Strategy_Id from Strategy WHERE
        Area = "%s" and ref_type_id= %s;''' % (area, ref_type)
        c.execute(statement)
        rows = c.fetchall()
        for row in rows:
            ref_area_id = int(row[0])
            print ref_area_id
        statement = '''INSERT INTO Checks(Ref_Type_id, ref_strategy_id,
        ref_staff_id, comment, complete) VALUES (%s, %s, %s, "%s", True);''' \
        % (ref_type, ref_area_id, int(ref_staff_id), comment)
        c.execute(statement)
        d.commit()
        return True
    except:
        return False