def payment_method_update(id):
    form = PaymentMethodForm()

    with db.connection.cursor() as cursor:
        # heroku reconnect
        db.reconnect()

        cursor.execute(
            'SELECT payment_name FROM payment_method WHERE payment_method_id = %s',
            (id))
        method = cursor.fetchone()

        form.name.data = method['payment_name']

        if form.validate_on_submit():
            # Update from
            cursor.execute(
                '''UPDATE payment_method SET payment_name = %s WHERE payment_method_id = %s''',
                (request.form['name'], id))
            # commit update
            db.connection.commit()

            flash('Payment method updated')

            return redirect(url_for('admin.payment_method_list'))

    return render_template('admin/payment_method_update.html', form=form)
def country_update(id):
    '''Update country'''
    form = CountryForm()

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()

        # get country
        cursor.execute("SELECT name FROM country WHERE country_id=%s", (id))
        country = cursor.fetchone()

        # populate field
        form.name.data = country['name']

        if form.validate_on_submit():
            # update country
            cursor.execute("UPDATE country SET name = %s WHERE country_id=%s",
                           (request.form['name'], id))
            db.connection.commit()

            flash('Update successfull')

            return redirect(url_for('admin.country_list'))

    return render_template('admin/country_update.html', form=form)
Exemple #3
0
def product_create(id):
    form = ProductCreateForm()

    with db.connection.cursor() as cursor:
        # reconnect by default because heroku server connection is unstable
        db.reconnect()

        # get this store_id
        #cursor.execute('SELECT * FROM user WHERE store_id=(%s)', (id))
        #user = cursor.fetchone()

        # select all categories
        cursor.execute('SELECT * FROM category')
        category_list = cursor.fetchall()
        form.category.choices = [(category['category_id'],
                                  category['category_name'])
                                 for category in category_list]

        if form.validate_on_submit():
            cursor.execute(
                '''INSERT INTO product (name, price, available, category_id, description, store_id) VALUES (%s, %s, %s, %s, %s, %s)''',
                (form.name.data, form.price.data, True, form.category.data,
                 form.description.data, g.user['store_id']))
            # Commit changes to db
            db.connection.commit()
            flash('Product added')

            return redirect(
                url_for('store.store_manager', id=g.user['store_id']))

    return render_template('product_create.html', form=form)
Exemple #4
0
def profile_update(id):
    '''Controller to update the user's email, name, lastname'''

    profile_form = ProfileUpdateForm()
    address_form = AddressUpdateForm()
    password_form = UserPasswordUpdateForm()

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()

        if profile_form.validate_on_submit():
            cursor.execute(
                'UPDATE customer_view SET firstname=%s, lastname=%s, email=%s WHERE user_id=%s',
                (request.form['firstname'], request.form['lastname'],
                 request.form['email'], id))
            db.connection.commit()

            flash('Successfully updated')

            return redirect(url_for('users.profile', id=g.user['user_id']))

    return render_template('profile.html',
                           profile_form=profile_form,
                           address_form=address_form,
                           password_form=password_form)
def product_create():
    '''Create new product'''
    form = ProductCreateForm()

    with db.connection.cursor() as cursor:
        # reconnect with heroku
        db.reconnect()

        # get categories
        cursor.execute("SELECT * FROM category")
        category_list = cursor.fetchall()
        # get stores
        cursor.execute("SELECT * FROM store")
        store_list = cursor.fetchall()

        # populate select field
        form.category.choices = [(category['category_id'],
                                  category['category_name'])
                                 for category in category_list]
        form.store.choices = [(store['store_id'], store['store_name'])
                              for store in store_list]

        if form.validate_on_submit():
            cursor.execute(
                "INSERT INTO product (name, price, description, category_id, store_id) VALUES (%s, %s, %s, %s, %s)",
                (form.name.data, form.price.data, form.description.data,
                 form.category.data, form.store.data))
            # commit changes
            db.connection.commit()

            flash('Product added')

            return redirect(url_for('admin.product_list'))

    return render_template('admin/product_create.html', form=form)
def city_create():
    form = CityForm()

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()
        # get countries
        cursor.execute('SELECT * FROM country')
        # fetch results
        country_list = cursor.fetchall()
        # select field for countries
        form.country.choices = [(country['country_id'], country['name'])
                                for country in country_list]

        if form.validate_on_submit():
            cursor.execute(
                "INSERT INTO city (name, country_id) VALUES (%s, %s)",
                (form.name.data, form.country.data))
            # commit to db
            db.connection.commit()

            flash('City has been added')

            return redirect(url_for('admin.city_list'))

    return render_template('admin/city_create.html', form=form)
def purchase_history(id):
    with db.connection.cursor() as cursor:
        db.reconnect()

        cursor.execute(
            'CREATE VIEW purchase_history_store '
            'AS SELECT p.name, oi.quantity, oi.total_price, pm.payment_name, u.firstname, u.lastname '
            'FROM product p, order_item oi, payment_method pm, user u, transaction t '
            'WHERE t.payment_method_id=pm.payment_method_id '
            'AND t.order_item_id=oi.order_item_id '
            'AND oi.product_id=p.product_id '
            'AND t.user_id= u.user_id '
            'AND t.store_id=(%s)', (id))

        db.connection.commit()

        cursor.execute('SELECT * FROM purchase_history_store')
        phs = cursor.fetchall()
        print(phs)

        cursor.execute('DROP VIEW purchase_history_store')

        db.connection.commit()

        return render_template('purchase_history_store.html', phs=phs)
def store_detail(id):
    '''Display the store\'s name & owner'''

    with db.connection.cursor() as cursor:
        db.reconnect()
        # Inner join to get the the necessary data to display
        # the
        cursor.execute(
            '''SELECT u.firstname, u.lastname, s.store_name
                          FROM user u
                          INNER JOIN store s
                          ON u.store_id=s.store_id
                          WHERE s.store_id = (%s)
                          ''', (id))
        # fetch the data
        owner = cursor.fetchone()

        # select this store's products
        cursor.execute(
            '''SELECT p.product_id, p.name, p.description, p.price
                          FROM product p 
                          INNER JOIN store s ON p.store_id=s.store_id
                          WHERE p.store_id=%s''', (id))
        product_list = cursor.fetchall()

    return render_template("store_detail.html",
                           owner=owner,
                           product_list=product_list)
Exemple #9
0
def register():
    '''Controller for registering new users'''
    form = UserRegistrationForm()

    if form.validate_on_submit():
        with db.connection.cursor() as cursor:
            db.reconnect()
            # insert new user object into user
            cursor.execute(
                'INSERT INTO user (firstname, lastname, email, password, is_active) VALUES (%s, %s, %s, %s, %s)',
                (form.firstname.data, form.lastname.data, form.email.data,
                 generate_password_hash(form.password1.data), True))

            # commit to db
            db.connection.commit()

            # select new registered user
            cursor.execute('SELECT user_id FROM user WHERE email LIKE (%s)',
                           (form.email.data))
            user = cursor.fetchone()
            session.pop('user', None)

            session['is_authenticated'] = True
            session['user'] = user

        flash('Awesome! You just created an account and can now login')

        # redirect user to address form
        return redirect(url_for('users.register_address', id=user['user_id']))

    else:
        print(form.errors)
        print('Failed to insert new user to db')

    return render_template('register.html', form=form)
def admin_update(id):
    '''Update selected administrator'''
    form = AdminUpdateForm()

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()
        # retrieve selected admin data
        cursor.execute('SELECT * FROM admin WHERE admin_id=%s', int(id))
        admin = cursor.fetchone()

        # populate fields
        form.username.data = admin['username']
        form.email.data = admin['email']

        if form.validate_on_submit():
            # UPDATE admin
            cursor.execute(
                "UPDATE admin SET username = %s, email = %s, password1 = %s, password2 = %s WHERE admin_id = %s",
                (request.form['username'], request.form['email'],
                 form.password1.data, form.passord2.data, id))
            db.connection.commit()

            return redirect(url_for('admin.admin_list'))

    return render_template('admin/admin_update.html', form=form, admin=admin)
def order_confirm():
    form = PaymentMethodForm()

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()

        # get the current user data
        cursor.execute('SELECT * FROM customer_view')
        customer = cursor.fetchone()

        # get the payment methods
        cursor.execute('SELECT * FROM payment_method')
        payment_methods = cursor.fetchall()

        form.payment_method.choices = [(p['payment_method_id'],
                                        p['payment_name'])
                                       for p in payment_methods]

        if form.validate_on_submit():
            # insert into transaction and related tables
            cursor.execute(
                "INSERT INTO transaction (payment_method, user_id) VALUES (%s, %s)",
                (form.payment_method.data, session['user'].user_id))
            db.connection.commit()

            # select the transaction that belongs to this user
            cursor.execute('SELECT * FROM transaction WHERE user_id=%s',
                           (session['user'].user_id))
            transaction = cursor.fetch()

            return redirect(url_for('order_confirmed.html'))

    return render_template('order_confirm.html', form=form, customer=customer)
def store_delete(id):
    '''Delete store'''

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()
        #

    return redirect(url_for('users.account', id=g.user['user_id']))
def customer_create():
    '''Create new user'''
    form = CustomerCreateForm()

    with db.connection.cursor() as cursor:
        # reconnect to heroku server
        db.reconnect()

        # get all the cities
        cursor.execute('SELECT * FROM city')
        city_list = cursor.fetchall()
        form.city.choices = [(city['city_id'], city['name'])
                             for city in city_list]

        if form.validate_on_submit():
            # insert into user first
            cursor.execute(
                'INSERT INTO user (firstname, lastname, email, password, is_active) VALUES (%s, %s, %s, %s, %s)',
                (form.firstname.data, form.lastname.data, form.email.data,
                 generate_password_hash(form.password1.data), True))
            db.connection.commit()

            # select this user
            #cursor.execute('SELECT user_id FROM user WHERE email LIKE (%s)', (
            #                form.email.data))
            #user = cursor.fetchone()

            cursor.execute(
                'INSERT INTO address (line1, line2, line3, postal_code, city_id) VALUES (%s, %s, %s, %s, %s)',
                (form.address_line1.data, form.address_line2.data,
                 form.address_line3.data, form.postal_code.data,
                 form.city.data))
            db.connection.commit()

            cursor.execute('SELECT address_id FROM address WHERE line1=%s',
                           (form.address_line1.data))
            address = cursor.fetchone()

            cursor.execute('UPDATE user SET address_id=%s',
                           (address['address_id']))

            ## insert address into customer_view
            #cursor.execute('UPDATE customer_view SET address_line1=%s, address_line2=%s, address_line3=%s, postal_code=%s, city_id=%s WHERE user_id=%s', (
            #                form.address_line1.data,
            #                form.address_line2.data,
            #                form.address_line3.data,
            #                form.postal_code.data,
            #                form.city.data,
            #                user['user_id']))

            db.connection.commit()

            flash(f'{form.firstname.data} {form.lastname.data} created')

            return redirect(url_for('admin.customer_list'))

    return render_template('admin/customer_create.html', form=form)
def payment_method_delete(id):
    '''Delete selected payment method'''

    with db.connection.cursor() as cursor:
        db.reconnect()
        # delete from
        cursor.execute(
            'DELETE FROM payment_method WHERE payment_method_id = %s', (id))
        # Commit changes

        return redirect(url_for('admin.payment_method_list'))
def admin_list():
    '''List of all the administrators'''

    with db.connection.cursor() as cursor:
        # reconnect to heroku server
        db.reconnect()
        # Select all administrators
        cursor.execute('SELECT * FROM admin')
        # fetch the result
        admin_list = cursor.fetchall()

    return render_template('admin/admin_list.html', admin_list=admin_list)
def category_list():
    '''List of all the categories'''

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()
        # retrieve all categories
        cursor.execute('SELECT * FROM category')
        # fetch results
        category_list = cursor.fetchall()

    return render_template('admin/category_list.html',
                           category_list=category_list)
def product_update(id):
    '''Update selected product'''
    form = ProductUpdateForm()

    with db.connection.cursor() as cursor:
        # reconnect with heroku
        db.reconnect()
        # get select product to update
        cursor.execute(
            "SELECT name, price, description, category_id, store_id FROM product WHERE product_id = %s",
            (id))
        product = cursor.fetchone()

        # get categories
        cursor.execute("SELECT * FROM category")
        category_list = cursor.fetchall()

        # get stores
        cursor.execute("SELECT * FROM store")
        store_list = cursor.fetchall()

        # populate select field
        form.name.data = product['name']
        form.price.data = product['price']
        form.description.data = product['description']
        form.category.data = product['category_id']
        form.store.data = product['store_id']

        # select fields
        form.category.choices = [(category['category_id'],
                                  category['category_name'])
                                 for category in category_list]
        form.store.choices = [(store['store_id'], store['store_name'])
                              for store in store_list]

        if form.validate_on_submit():
            # update the selected product
            cursor.execute(
                "UPDATE product SET name = %s, price = %s, description = %s, category_id = %s, store_id = %s WHERE product_id = %s",
                (request.form['name'], request.form['price'],
                 request.form['description'], request.form['category'],
                 request.form['store'], id))
            # commit changes
            db.connection.commit()

            flash('Product added')

            return redirect(url_for('admin.product_list'))

    return render_template('admin/product_update.html', form=form)
def customer_delete(id):
    '''Delete selected customer from db'''

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()
        # delete
        cursor.execute('DELETE FROM user WHERE user_id=%s', (id))
        # commmit changes
        db.connection.commit()

        flash('Customer deleted')

        return redirect(url_for('admin.customer_list'))
def customer_list():
    '''List of all the customers'''

    with db.connection.cursor() as cursor:
        # reconnect to heroku server
        db.reconnect()
        # Select all customers
        cursor.execute(
            'SELECT u.user_id, u.firstname, u.lastname, u.email, u.joined_on, u.is_active, s.store_name FROM user u LEFT JOIN store s ON u.store_id=s.store_id ORDER BY u.joined_on DESC'
        )
        customer_list = cursor.fetchall()

    return render_template('admin/customer_list.html',
                           customer_list=customer_list)
def payment_method_list():
    '''List out the payment methods'''

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()
        # Retrieve all payment methods
        cursor.execute(
            'SELECT payment_method_id AS id, payment_name AS name FROM payment_method'
        )
        payment_method_list = cursor.fetchall()

    return render_template('admin/payment_method_list.html',
                           payment_method_list=payment_method_list)
Exemple #21
0
def product_delete(id):

    with db.connection.cursor() as cursor:
        # reconnect to heroku clear_db
        db.reconnect()
        # delete product from database
        cursor.execute('DELETE FROM product WHERE product.product_id=%s',
                       (int(id)))
        db.connection.commit()

        # success message
        flash('Product deleted')

        return redirect(url_for('store.store_manager', id=g.user['store_id']))
Exemple #22
0
def account(id):
    '''Controller for the user account'''

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()
        cursor.execute(
            'SELECT firstname, lastname, email, address_line1, address_line2, address_line3, postal_code, city_id, city_name FROM customer_view WHERE user_id=%s',
            (id))
        user = cursor.fetchone()

    image = url_for('static', filename="profile_img/default_avatar.png")

    return render_template('user_dashboard.html', image=image, user=user)
def store_manager(id):
    '''Display the store owner's products'''

    with db.connection.cursor() as cursor:
        db.reconnect()
        cursor.execute(
            '''SELECT p.product_id, p.name, p.price, p.category_id, p.added_on, c.category_name FROM product p
                          INNER JOIN category c
                          ON p.category_id=c.category_id
                          WHERE p.store_id = (%s)''', (id))
        product_list = cursor.fetchall()

    print(session)

    return render_template('store_manager.html', product_list=product_list)
def admin_delete(id):
    '''Delete selected administrator'''

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()
        # delete from admin
        cursor.execute('DELETE FROM admin WHERE admin.admin_id=(%s)',
                       (int(id)))
        # commit changes
        db.connection.commit()

        flash('Administrator has been deleted')

        return redirect(url_for('admin.admin_list'))
def address_list():
    '''List of all address'''

    with db.connection.cursor() as cursor:
        # reconnect to heroku server
        db.reconnect()

        # select all addresses
        cursor.execute('SELECT * FROM address_view')

        # fetch all
        address_list = cursor.fetchall()

    return render_template('admin/address_list.html',
                           address_list=address_list)
def store_list():
    '''List of all the stores'''

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()
        # retrieve all stores
        cursor.execute(
            '''SELECT s.store_id, s.store_name, s.joined_on, concat(u.firstname, ' ', u.lastname) AS fullname FROM store s
                          INNER JOIN user u ON s.store_id=u.store_id 
                       ''')
        # fetch results
        store_list = cursor.fetchall()

    return render_template('admin/store_list.html', store_list=store_list)
def filtered_by_category(name):

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()

        cursor.execute('SELECT DISTINCT * FROM category')
        category_list = cursor.fetchall()

        cursor.execute(
            'SELECT * FROM product_view WHERE category_name LIKE (%s)', (name))
        product_list = cursor.fetchall()

    return render_template('product_filtered_by_category.html',
                           product_list=product_list,
                           category_list=category_list)
def address_update(id):
    '''Add a new address'''
    form = AddressCreateForm()

    with db.connection.cursor() as cursor:
        # recconect to heroku
        db.reconnect()
        # get selected address date
        cursor.execute(
            'SELECT line1, line2, line3, postal_code, city_id FROM address WHERE address_id = %s',
            (id))
        # get address
        address = cursor.fetchone()

        # get all cities for select form
        cursor.execute('''SELECT city_id, name FROM city''')
        city_list = cursor.fetchall()
        form.city.choices = [(city['city_id'], city['name'])
                             for city in city_list]

        # populate fields
        form.line1.data = address['line1']
        form.line2.data = address['line2']
        form.line3.data = address['line3']
        form.postal_code.data = address['postal_code']
        #form.city.data = address['city_id']
        #form.country.data = address['country_id']

        print(form.city.data)
        print(type(address['city_id']))

        if form.validate_on_submit():
            # Update selected address
            cursor.execute(
                "UPDATE address SET line1 = %s, line2 = %s, line3 = %s, postal_code = %s, city_id = %s WHERE address_id = %s",
                (request.form['line1'], request.form['line2'],
                 request.form['line3'], request.form['postal_code'],
                 form.city.data, id))
            #commit to db
            db.connection.commit

            flash('Address updated')

            return redirect(url_for('admin.address_list'))

    return render_template('admin/address_update.html', form=form)
def product_list():
    '''List of all the products sorted by store owner'''

    with db.connection.cursor() as cursor:
        # reconnect to heroku
        db.reconnect()
        # retrieve all products and order by the latest added on timestamp
        cursor.execute(
            'SELECT p.product_id, p.name, p.price, p.added_on, s.store_name, c.category_name FROM product p '
            'INNER JOIN category c ON c.category_id=p.category_id '
            'INNER JOIN store s ON p.store_id=s.store_id ORDER BY p.added_on DESC'
        )
        # fetch results
        product_list = cursor.fetchall()

    return render_template('admin/product_list.html',
                           product_list=product_list)
Exemple #30
0
def purchase_history(id):

    with db.connection.cursor() as cursor:
        db.reconnect()
        # SELECT ALL transaction from this user
        cursor.execute(
            'SELECT t.transaction_id, t.ordered_on FROM transaction t WHERE t.user_id=%s',
            (id))
        transaction_list = cursor.fetchall()
        cursor.execute(
            'SELECT firstname, lastname, email, address_line1, address_line2, address_line3, postal_code, city_id, city_name FROM customer_view WHERE user_id=%s',
            (id))
        user = cursor.fetchone()

    return render_template('purchase_history_user.html',
                           transaction_list=transaction_list,
                           user=user)