def create_user_and_sample_Items(name, phone_num):
    customer = Customer(name, phone_num)
    cookie = Item(name + "_Cookie", 1.50)
    muffin = Item(name + "_Muffin", 2.00, "Carby stuff")
    latte = Item(name + "_Latte", 4.35, "Steamed milk over expresso")
    mocha = Item(name + "_Mocha", 5.00,
                 "Steamed milk over expresso and chocolate syrup")

    session.add(customer)
    session.add(cookie)
    session.add(muffin)
    session.add(latte)
    session.add(mocha)
    session.commit()

    result = {"customer": customer}
    item_names = ['cookie', 'muffin', 'latte', 'mocha']
    for name in item_names:

        item = session.query(Item).\
            filter(func.lower(Item.name).like('%' + name + '%')).\
            filter(func.lower(Item.name).like('%' + customer_name + '%')).\
            scalar()

        result[name] = item

    return result

def queue_del(title=None, imdb_id=None, tmdb_id=None, session=None, movie_id=None, queue_name='default'):
    """
    Delete the given item from the queue.

    :param title: Movie title
    :param imdb_id: Imdb id
    :param tmdb_id: Tmdb id
    :param session: Optional session to use, new session used otherwise
    :param queue_name: Name of movie queue to get items from
    :return: Title of forgotten movie
    :raises QueueError: If queued item could not be found with given arguments
    """
    log.debug('queue_del - title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s, queue_name=%s',
              title, imdb_id, tmdb_id, movie_id, queue_name)
    query = session.query(QueuedMovie).filter(func.lower(QueuedMovie.queue_name) == queue_name.lower())
    if imdb_id:
        query = query.filter(QueuedMovie.imdb_id == imdb_id)
    elif tmdb_id:
        query = query.filter(QueuedMovie.tmdb_id == tmdb_id)
    elif title:
        query = query.filter(func.lower(QueuedMovie.title) == func.lower(title))
    elif movie_id:
        query = query.filter(QueuedMovie.id == movie_id)
    try:
        item = query.one()
        title = item.title
        session.delete(item)
        return title
    except NoResultFound as e:
        raise QueueError(
            'title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s not found in queue %s' % (
                title, imdb_id, tmdb_id, movie_id, queue_name))
    except MultipleResultsFound:
        raise QueueError('title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s matches multiple results in queue %s' %
                         (title, imdb_id, tmdb_id, movie_id, queue_name))

def select(q, **kwargs):
    if kwargs.get('ids'):
        print("ids == " + str(kwargs.get('ids')))
        q = q.filter(Flight.id.in_(kwargs.get('ids')))

    if kwargs.get('date_from'):
        try:
            date_from = datetime.strptime(kwargs.get('date_from'), "%Y-%m-%d")
            q = q.filter(Flight.takeoff_time >= date_from)
            print("takeoff_time >= " + str(date_from))
        except:
            print("Cannot parse date-from")
            return None

    if kwargs.get('date_to'):
        try:
            date_to = datetime.strptime(kwargs.get('date_to'), "%Y-%m-%d")
            q = q.filter(Flight.takeoff_time < date_to)
            print("takeoff_time < " + str(date_to))
        except:
            print("Cannot parse date-to")
            return None

    if kwargs.get('uploaded_from'):
        try:
            uploaded_from = datetime.strptime(kwargs.get('uploaded_from'), "%Y-%m-%d")
            q = q.filter(Flight.time_created >= uploaded_from)
            print("time_created >= " + str(uploaded_from))
        except:
            print("Cannot parse uploaded-from")
            return None

    if kwargs.get('uploaded_to'):
        try:
            uploaded_to = datetime.strptime(kwargs.get('uploaded_to'), "%Y-%m-%d")
            q = q.filter(Flight.time_created < uploaded_to)
            print("time_created < " + str(uploaded_to))
        except:
            print("Cannot parse uploaded-to")
            return None

    if not kwargs.get('private'):
        print("privacy_level == PUBLIC")
        q = q.filter(Flight.privacy_level == Flight.PrivacyLevel.PUBLIC)

    if kwargs.get('country_code'):
        country_code = kwargs.get('country_code')

        q = q.join(Flight.takeoff_airport)
        q = q.filter(func.lower(Airport.country_code) == func.lower(country_code))
        print("takeoff_airport country code: " + country_code)

    if kwargs.get('airport_name'):
        airport_name = kwargs.get('airport_name')

        q = q.join(Flight.takeoff_airport)
        q = q.filter(func.lower(Airport.name) == func.lower(airport_name))
        print("takeoff_airport name: " + airport_name)

    return q

    def login(self):
        request = self.request
        params = request.params
        if self.logged_in:
            return HTTPForbidden()
        login_url = request.route_url('login')
        referrer = request.referrer
        if referrer == login_url:
            referrer = '/'
        came_from = request.params.get('came_from', referrer)
        if 'form.submitted' in params:
            user = DBSession.query(User).filter(func.lower(User.email) == func.lower(params['email'])).first()

            if user is not None:
                if user.confirmed is False:
                    request.session.flash('Account must be activated before logging in.',
                                          queue='notifications')
                elif check_password(params['email'], params['password']):
                    userid = user.id
                    print("userid: " + str(userid))
                    print("email: " + str(user.email))
                    headers = remember(request, str(userid))
                    self.gibs['current_user'] = user.email
                    request.session.flash('Successfully logged in as ' + str(user.email),
                                        queue='successes')
                    return HTTPFound(location=came_from,
                                    headers=headers)

            request.session.flash('Email or password was incorrect, or user does not exist.',
                                  queue='warnings')
        return {'gibs': self.gibs,
                'came_from': came_from,
                }

def _by_name(session, gene_id, chromosome_ids):
    gene_db = (session
               .query(Gene)
               .filter(func.lower(Gene.name) == func.lower(gene_id))
               .filter(Gene.chromosome_id.in_(chromosome_ids))
               .all())
    return gene_db, False

    def _search_video(self, query, location, size, start=0):
        videos = Video.query.filter_by(deleted=False, status="published").outerjoin(
            VideoLocaleMeta, (VideoLocaleMeta.video_id == Video.id) & (VideoLocaleMeta.locale == "")
        )

        owner_filter = VIDEO_OWNER_USER_QUERY_RE.match(query.strip())
        if owner_filter:
            videos = videos.join(
                AccountUser, (AccountUser.account_id == Video.account_id) & (AccountUser.id == owner_filter.group(1))
            ).distinct()
            query = ""

        collab_filter = VIDEO_COLLABORATOR_QUERY_RE.match(query.strip())
        if collab_filter:
            videos = (
                videos.join(VideoCollaborator, VideoCollaborator.video_id == Video.id)
                .join(
                    AccountUser,
                    (func.lower(AccountUser.username) == func.lower(VideoCollaborator.email))
                    & (AccountUser.id == collab_filter.group(1)),
                )
                .distinct()
            )
            query = ""

        videos, total = self._db_match(videos, size, start, query, Video.search_vector)
        items = [self._video_item(v) for v in videos]
        return dict(items=items, total=total)

def event_contains(value, query):
    value = value.lower()
    q = query.filter(or_(
        func.lower(Event.name).contains(value),
        func.lower(Event.description).contains(value)
    ))
    return q

def modifyNotesEntry(session, teamnum, comp, old_notes, new_notes, notestag):
    notes = session.query(NotesEntry).filter(NotesEntry.team==teamnum).\
                                      filter(func.lower(NotesEntry.competition)==func.lower(comp)).\
                                      filter(NotesEntry.data==old_notes)
    if notes:
        notes.data = new_notes
        notes.tag = notestag                                  

 def get_asset_type_model_user_name_num(self, asset_type,
                                        model_id, user_name, store_state):
     return self.session.query(AssetConsumeInfoModel).filter(
         and_(AssetConsumeInfoModel.asset_type_id == asset_type,
              AssetConsumeInfoModel.model_id == model_id,
              AssetConsumeInfoModel.store_state_id == store_state,
              func.lower(AssetConsumeInfoModel.user_name) == func.lower(user_name))).first()

def get_quadbounds(location):
    try:
        quads = DBSession.query(
                                Quadrant.latitude,
                                Quadrant.longitude,
                                Quadrant.name,
                            ).filter(
                                func.lower(Quadrant.location)==
                                    func.lower(location)
                            ).all()
        center = DBSession.query(
                                Location.latitude,
                                Location.longitude,
                            ).filter(
                                func.lower(location)==
                                    func.lower(Location.name),
                            ).first()
    except DBAPIError as e:
        print e
        return []
    
    bounds = []

    for i in quads:
        if i[1] <= center[1]:
            bounds.append([i[0], i[1], center[0], center[1], i[2]])
        else:
            bounds.append([center[0], center[1], i[0], i[1], i[2]])

    return bounds

def recommended_books_by_publisher(books_read_by_user, recommended_books):
    read_books_isbn_list = [book.book_isbn for book in books_read_by_user]
    # Get publishers and the number of books read by the user of the publisher
    # in descending order
    publisher_count = db.session.query(
        func.lower(Books.publisher),
        func.count(Books.publisher)
    ).filter(
        Books.isbn.in_(read_books_isbn_list)
    ).group_by(
        func.lower(Books.publisher)
    ).order_by(
        func.count(Books.publisher).desc()
    ).all()
    publisher_count_dict = {str(publisher): count for (
        publisher, count) in publisher_count}
    unread_books_by_same_publishers = db.session.query(
        Books.isbn,
        func.lower(Books.publisher)
    ).filter(
        and_(
            func.lower(Books.publisher).in_([x[0] for x in publisher_count]),
            ~Books.isbn.in_(read_books_isbn_list)
        )
    ).all()
    # Gets the books of the above publishers which are not read by the user
    for unread_book in unread_books_by_same_publishers:
        isbn = unread_book[0]
        publisher = unread_book[1]
        weight = g.user.publisher_weight * publisher_count_dict[publisher]
        if isbn not in recommended_books:
            recommended_books.update({isbn: weight})
        else:
            recommended_books[isbn] += weight

def get_recentTrapCount(location, date):
    try:
        dates = DBSession.query(
                    func.max(Trap.date).label('date'),
                    Region.quadrant.label('quad'),
                    Region.name.label('region'),
                ).filter(
                    Region.name==Trap.region,
                    Trap.date <= date,
                    func.lower(Region.location)==func.lower(location),
                ).group_by(Trap.region).subquery()
        
        counts = DBSession.query(
                    dates.c.date,
                    dates.c.quad,
                    Trap.count,
                ).filter(
                    Region.name==Trap.region,
                    Trap.date==dates.c.date,
                    Trap.region==dates.c.region,
                ).all()
    except DBAPIError as e:
        print e
        return []

    counts = [[str(i[0]), str(i[1]), int(i[2])] for i in counts]

    return counts

def get_rainData(start_date, end_date, location):
    data = {}
    try:
        readings = DBSession.query(
                                Gauge.quadrant,
                                RainReading.date,
                                func.avg(RainReading.amount),
                            ).filter(
                                Gauge.gauge_id==RainReading.gauge_id,
                                func.lower(Gauge.location)==func.lower(location),
                                func.lower(RainReading.location)==
                                    func.lower(location),
                                RainReading.date.between(start_date, end_date),
                            ).group_by(
                                Gauge.quadrant,
                                RainReading.date,
                            ).all()
    except DBAPIError as e:
        print e
        return {}

    for i in readings:
        new_item = [str(i[1]), int(i[2])]
        if i[0] in data:
            data[i[0]].append(new_item)
        else:
            data[i[0]] = [new_item]

    return data

 def search_sample(self, sample_code):
     """ return a single Sample from the current batch with sample_code """
     try:
         return self.samples.filter( func.lower(Sample.code) == func.lower(sample_code),
             Sample.batch_id == self.id ).one()
     except NoResultFound:
         return None

 def find_user_by_email(cls, email):
     """ Finds user by EMAIL, making sure to look in all lowercase
         Returns Query object not user! Then can use first_or_404.
     """
     user_query = cls.query.filter(
                 func.lower(cls.email) == func.lower(email))
     return user_query

    def person_exists(self, email=None, id=None, nickname=None):
        """
        Check if a Person with this email is stored in the database

        :type email: str
        :param email: An email address to search the database for

        :type id: str
        :param id: A user id to search for.

        :type nickname: str
        :param nickname: A nickname to search for.
        """

        query = self.session.query(Person)
        if email:
            return query.filter(
                    func.lower(Person.email) == func.lower(email)).count() != 0
        elif id:
            return query.filter_by(id=id).count() != 0
        elif nickname:
            return query.filter(
                    func.lower(Person.nickname) == func.lower(
                             nickname)).count() != 0
        else:
            return False

 def find_user_by_twitter(cls, twitter):
     """ Finds user by EMAIL, making sure to look in all lowercase
         Returns Query object not user! Then can use first_or_404.
     """
     user_query = cls.query.filter(
                 func.lower(cls.twitter_handle) == func.lower(twitter))
     return user_query

    def get_person(self, person_email=None, id=None, nickname=None):
        """
        Convenience function to retrieve a person object from an email,
        id, or nickname.

        :type person_email: str
        :param person_email: The email address of a Person in the database

        :type id: str
        :param id: The id of a Person in the database

        :type nickname: str
        :param nickname: The nickname of a Person in the database
        """

        query = self.session.query(Person)

        if person_email and self.person_exists(email=person_email):
            return query.filter(
                    func.lower(Person.email) == \
                            func.lower(person_email)).one()
        elif id and self.person_exists(id=id):
            return query.filter_by(id=id).one()
        elif nickname and self.person_exists(nickname=nickname):
            return query.filter(
                    func.lower(Person.nickname) == \
                            func.lower(nickname)).one()
        else:
            return None

def get_user_by_name_or_email(name_or_email):
    return db.session \
        .query(db.User) \
        .filter(
            (func.lower(db.User.name) == func.lower(name_or_email))
            | (func.lower(db.User.email) == func.lower(name_or_email))) \
        .first()

    def get_badges_from_tags(self, tags, match_all=False):
        """
        Return badges matching tags.

        :type tags: list
        :param tags: A list of string badge tags

        :type match_all: boolean
        :param match_all: Returned badges must have all tags in list
        """

        badges = list()

        if match_all:
            # Return badges matching all tags
            # ... by doing argument-expansion on a list comprehension
            badges.extend(self.session.query(Badge).filter(and_(*[
                func.lower(Badge.tags).contains(str(tag + ',').lower())
                for tag in tags])))
        else:
            # Return badges matching any of the tags
            for tag in tags:
                badges.extend(self.session.query(Badge).filter(
                              func.lower(Badge.tags).contains(
                              str(tag + ',').lower())).all())

        # Eliminate any duplicates.
        unique_badges = list()
        for badge in badges:
            if badge not in unique_badges:
                unique_badges.append(badge)

        return unique_badges