from flask import Flask, render_template
from flask import request
import psycopg2
import psycopg2.extras            

app = Flask(__name__) #flask object

def getConnection(): #function to get the connection to database
        connectionStr = ("host = 'cmpstudb-01.cmp.uea.ac.uk' dbname = 'fjb18tfu' user = 'fjb18tfu' password = 'password'")
        connection = psycopg2.connect(connectionStr)
        return connection

@app.route('/') #maps URL to the funtion
def HomePage():
    return render_template('HomePage.html')
    if __name__ == '__main__': # causes flask to auto reload when modified
        app.run(debug = True) 

@app.route('/Q1', methods = ['POST']) #POST method since it is changing something
def Query1():
        try:
            connection = None #need this if finally is executing when there is a problem connecting
            categoryID = int(request.form['categoryid']) #gets data from form
            name = request.form['name']
            categoryType = request.form['categorytype']

            connection = getConnection() #calls get connection function
        
            cursor = connection.cursor()
        
            cursor.execute('SET search_path to public') #directs to the correct schema

            cursor.execute('INSERT INTO Category VALUES (%s, %s, %s)', [categoryID, name, categoryType])
            
            
            connection.commit() #saves changes to database
            return render_template('HomePage.html', msg1 = 'Record has been added')
           
        except Exception as e:
            return render_template('HomePage.html', msg1 = 'ERROR! Record not added', error1 = e)

        finally:
            if connection:
                cursor.close()
                connection.close()
                print("PostgreSQL connection is closed")

@app.route('/Q2', methods = ['POST'])
def Query2():
        try:
                connection = None
                categoryID = int(request.form['categoryid'])
                connection = getConnection()
                cursor = connection.cursor()
                cursor.execute('SET search_path to public')

                cursor.execute('DELETE FROM Category WHERE CategoryID = (%s)', [categoryID])
                connection.commit()
                return render_template('HomePage.html', msg2 = 'Record deleted')

        except Exception as e:
                return render_template('HomePage.html', msg2 = 'ERROR! Record not deleted', error2 = e)

        finally:
            if connection:
                cursor.close()
                connection.close()
                print("PostgreSQL connection is closed")
                
@app.route('/Q3', methods = ['POST']) 
def Query3():
        try:
                connection = None
                connection = getConnection()
                cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor)
                cursor.execute('SET search_path to public')

                cursor.execute('CREATE OR REPLACE VIEW Book_Summary AS \
                        SELECT ROUND(AVG(Price),2) AS Average_Price, COUNT (BookID) AS Amount_Of_Books_In_Each_Category, Category.CategoryID, Category.Name AS Category_name \
                        FROM Book, Category \
                        WHERE Book.CategoryID = Category.CategoryID \
                        GROUP BY Category.CategoryID, Category.Name')
                
                cursor.execute('SELECT * FROM Book_Summary')
                connection.commit()
                
                rows = cursor.fetchall()
                
                for row in rows:
                        print (row[0], row[1], row[2], row[3])

                cursor.execute('SELECT ROUND(SUM(Average_Price),2) AS Avg_Total FROM Book_Summary')
                connection.commit()
                summary = cursor.fetchall()

                for row in summary:
                        print (row[0])
                if summary:
                        return render_template('Query3.html', rows = rows, summary = summary)
                else:
                        return render_template('HomePage.html', msg3 = 'Table not found')

        except Exception as e:
                return render_template('HomePage.html', msg3 = 'Table not found', error3 = e)

        finally:
            if connection:
                cursor.close()
                connection.close()
                print("PostgreSQL connection is closed")


@app.route('/Q4', methods = ['POST'])
def Query4():
        try:
                connection = None
                publisherName = request.form['publishername']
                connection = getConnection()
                cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor)
                cursor.execute('SET search_path to public')

                cursor.execute('SELECT DISTINCT Book.BookID, Title, COUNT(Orderline.ShopOrderID) AS Total_Orders, SUM(Quantity) AS Total_Quantity, \
                               ROUND(SUM(UnitSellingPrice + Price),2) AS Total_Selling_Value, Publisher.Name AS Publisher_name, \
                                MAX(EXTRACT(MONTH FROM OrderDate)) AS Month, \
                                MAX(EXTRACT(YEAR FROM OrderDate)) AS Year \
                                FROM Book, Orderline, ShopOrder, Publisher \
                                WHERE Book.BookID = Orderline.BookID AND ShopOrder.ShopOrderID = Orderline.ShopOrderID AND Publisher.Name = (%s) \
                                GROUP BY Book.BookID, Publisher.Name', [publisherName])
                connection.commit()

                rows = cursor.fetchall()
                for row in rows:
                        print (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7])
                
                if rows:
                        return render_template('Query4.html', rows = rows)
                else:
                        return render_template('HomePage.html', msg4 = 'Table not found')
                
        except Exception as e:
                return render_template('HomePage.html', msg4 = 'Table not found', error4 = e)       
                
        finally:
                if connection:
                        cursor.close()
                        connection.close()
                        print("PostgreSQL connection is closed")

@app.route('/Q5', methods = ['POST']) 
def Query5():
        try:
                connection = None
                bookID = int(request.form['bookid'])
                connection = getConnection()
                cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor)
                cursor.execute('SET search_path to public')

                cursor.execute('CREATE OR REPLACE VIEW Order_History AS \
                                SELECT DISTINCT Title AS Order_Title, OrderDate, Price, UnitSellingPrice, SUM(Quantity) AS Total_Quantity,\
                                ROUND(Price,2) AS Order_Value, Shop.Name AS Shop_Name \
                                FROM Book, Shop, ShopOrder, Orderline \
                                WHERE Book.BookID = Orderline.BookID AND ShopOrder.ShopOrderID = Orderline.ShopOrderID \
                                AND Book.BookID = (%s) \
                                GROUP BY Title, OrderDate, Price, UnitSellingPrice, Quantity, Shop_Name', [bookID])
                
                cursor.execute('SELECT * FROM Order_History')
                connection.commit()
                
                rows = cursor.fetchall()

                for row in rows:
                        print (row[0], row[1], row[2], row[3], row[4], row[5], row[6])

                cursor.execute('SELECT ROUND(SUM(Total_Quantity),2) AS Total_Copies_Ordered, SUM(Price + UnitSellingPrice) AS Total_Selling_Value \
                                FROM Order_History')
                connection.commit()
                summary = cursor.fetchall()

                for row in summary:
                        print (row[0], row[1])
                        
                if summary:
                        return render_template('Query5.html', rows = rows, summary = summary)
                else:
                        return render_template('HomePage.html', msg5 = 'Table not found')

        except Exception as e:
                return render_template('HomePage.html', msg5 = 'Table not found', error5 = e)

        finally:
                if connection:
                        cursor.close()
                        connection.close()
                        print("PostgreSQL connection is closed")

@app.route('/Q6', methods = ['POST'])
def Query6():
        try:
                connection = None
                startDate = request.form['startdate']
                endDate = request.form['enddate']
                connection = getConnection()
                cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor)
                cursor.execute('SET search_path to public')

                cursor.execute('SELECT DISTINCT SalesRep.SalesRepID, Name, COALESCE(SUM(Book.Price)) AS Total_Order_Value , SUM(Quantity) AS Total_Units_Sold \
                                FROM SalesRep, ShopOrder, Orderline, Book \
                                WHERE OrderDate BETWEEN (%s) AND (%s) \
                                AND SalesRep.SalesRepID = ShopOrder.SalesRepID AND ShopOrder.ShopOrderID = Orderline.ShopOrderID \
                                GROUP BY SalesRep.SalesRepID \
                                ORDER BY Total_Units_Sold DESC', [startDate, endDate])
                connection.commit()

                rows = cursor.fetchall()
                for row in rows:
                        print (row[0], row[1], row[2], row[3])
                
                if rows:
                        return render_template('Query6.html', rows = rows)
                else:
                        return render_template('HomePage.html', msg6 = 'Table not found')
                
        except Exception as e:
                return render_template('HomePage.html', msg6 = 'Table not found', error6 = e)       
                
        finally:
                if connection:
                        cursor.close()
                        connection.close()
                        print("PostgreSQL connection is closed")        
                
@app.route('/Q7', methods = ['POST'])
def Query7():
        try:
                connection = None
                categoryID = int(request.form['categoryid'])
                discount = float(request.form['discount'])
                connection = getConnection()
                cursor = connection.cursor()
                
                cursor.execute('SET search_path to public')

                cursor.execute('UPDATE Book \
                                SET Price = (Price-(Price * (%s))) \
                                WHERE CategoryID = (%s)', [discount, categoryID])
                
                connection.commit()
                
                return render_template('HomePage.html', msg7 = 'discount added')
                
        except Exception as e:
                return render_template('HomePage.html', msg7 = 'discount not added', error7 = e)       
                
        finally:
                if connection:
                        cursor.close()
                        connection.close()
                        print("PostgreSQL connection is closed")  
						
@pp.route('/sort', methods=['GET'])
def sort():
	print(request.form)
	n1 = int(request.args['number1'])
	n2 = int(request.args['number2'])
	action = request.args['action']
	if action == 'asc':
		if n1>n2:
                        temp=n2
                        n2=n1
                        n1=temp
		

	return render_template('HomePage.html', number1 = n1, number2 = n2)
