Ejemplo n.º 1
0
def create_folder():
    """Append new folder to folder table

            Returns:
                String: Success if operation successful else Error
    """
    if request.method == 'POST' and 'parentID' in request.form and 'name' in request.form \
            and 'MailUser_ID' in request.form:

        parent_id, name, user_id = get_folder_form_data(request.form)

        if not parent_id or not name or not user_id:
            return "Missing data, could not create folder"

        connection = mysql.connect()
        cursor = connection.cursor()

        try:
            cursor.execute(
                "INSERT INTO `folder` (`parentID`, `name`, `MailUser_ID`) VALUES (%s, %s, %s)",
                (parent_id, name, user_id))
            connection.commit()
            cursor.close()
            connection.close()
            return "Success"
        except MySQLError:
            cursor.close()
            connection.close()
            return "Error connecting to mysql database"

    return "Error"
Ejemplo n.º 2
0
def comments(id, page=1):
    cur = mysql.connect().cursor()
    cur.execute("select count(*) from Comments where phoneId = %s" % (id))
    total = cur.fetchone()[0]
    page, per_page, offset = get_page_items()
    phon = ""
    phones = [
        Samsung, Apple, Microsoft, Nokia, Sony, LG, HTC, Motorola, Huawei,
        Lenovo, Xiaomi, Acer, Asus, Oppo, Blackberry, Alcatel, ZTE, Toshiba,
        Gigabyte, Pantech, XOLO, Lava, Micromax, BLU, Spice, Verykool, Maxwest,
        Celkon, Gionee, Vivo, NIU, Yezz, Parla, Plum
    ]
    for phone in phones:
        if not phon:
            phon = db.session.query(phone).filter_by(phoneId=id).first()
    sql = "select * from Comments where phoneId=%s order by id limit {}, {}".format(
        offset, per_page) % (id)
    cur.execute(sql)
    comments = cur.fetchall()
    count = Comments.query.filter_by(phoneId=id).count()
    pagination = get_pagination(page=page,
                                per_page=per_page,
                                total=total,
                                record_name='comments',
                                format_total=True,
                                format_number=True)
    return render_template('comments.html',
                           phone=phon,
                           comments=comments,
                           id=id,
                           count=count,
                           page=page,
                           per_page=per_page,
                           pagination=pagination)
Ejemplo n.º 3
0
    def deleteOrder(order_id):
        #TODO support multiple items
        conn = mysql.connect()
        delete_cursor = conn.cursor()

        all_orders = Order(order_id).getOrderInfo(fetch_all=True)
        if 'order' in all_orders:
            all_order_ids = Order.fetchAllOrderIds(all_orders)
            order_info = all_orders['order']
        else:
            order_info = all_orders
            all_order_ids = str(order_info['order_id'])

        if order_info is None:
            return {'status': 'false'}

        delete_cursor.execute("""SELECT inventory_id FROM order_history WHERE
        order_id = %d""" % (order_id))
        inventory_id = delete_cursor.fetchone()[0]

        q_cond = """ AND fetched = 1"""

        delete_cursor.execute("""DELETE FROM inventory WHERE inventory_id =
        """ + str(inventory_id) + q_cond)
        conn.commit()

        delete_cursor.execute(
            "DELETE orders, order_history FROM orders INNER JOIN \
        order_history WHERE orders.order_id = order_history.order_id AND orders.order_id IN ("
            + all_order_ids + ")")
        conn.commit()
        delete_cursor.close()
        return {'status': 'true'}
Ejemplo n.º 4
0
def update_folder(folder_id):
    """Update folder from folder table

            Returns:
                String: Success if operation successful else Error
    """
    if request.method == 'PUT':
        parent_id, name, user_id = get_folder_form_data(request.form)
        connection = mysql.connect()
        cursor = connection.cursor()
        try:
            cursor.execute(
                "UPDATE `folder` SET `parentID` = COALESCE(NULLIF(%s, ''), `parentID`),"
                "`name` = COALESCE(NULLIF(%s, ''), `name`), "
                "`MailUser_ID` = COALESCE(NULLIF(%s, ''), `MailUser_ID`)"
                "WHERE id = %s",
                (parent_id, name, user_id, folder_id))
            connection.commit()
            cursor.close()
            connection.close()
            return "Success"
        except MySQLError:
            cursor.close()
            connection.close()
            return "Error connecting to mysql database"
        except ValueError:
            cursor.close()
            connection.close()
            return "Wrong password"

    return "Could not update folder"
Ejemplo n.º 5
0
def add_symptom(user_id, symptom_name, rating, time):

	conn = mysql.connect()
	cursor = conn.cursor()

	query = "SELECT Symptom.symptom_id FROM Symptom WHERE Symptom.description = %s"
	# statement = "INSERT INTO Symptom VALUES (0, %s)"
	cursor.execute(query, [symptom_name])
	data = cursor.fetchone()
	# if data is None:
	# 	cursor.execute(statement, [symptom_name])
	# 	cursor.execute(query, [symptom_name])
	# 	data = cursor.fetchone()
	symptom_id = int(data[0])

	# Insert into Eats, if necessary
	query = "SELECT Has.symptom_id FROM Has WHERE Has.symptom_id = %s AND Has.user_id = %s AND Has.rating = %s AND Has.time = %s"
	statement = "INSERT INTO Has VALUES (%s, %s, %s, %s)"
	cursor.execute(query, [symptom_id, user_id, rating, time])
	data = cursor.fetchone()
	if data is None:
		cursor.execute(statement, [symptom_id, user_id, rating, time])

	conn.commit()
	cursor.close()
	conn.close()
Ejemplo n.º 6
0
    def logMetadata(self, app_version):
        conn = mysql.connect()
        cursor = conn.cursor()
        cursor.execute(
            """UPDATE users SET last_app_version = %s, last_used_timestamp = 
                CURRENT_TIMESTAMP WHERE user_id = %s""",
            (app_version, self.user_id))
        conn.commit()

        client = MongoClient(webapp.config['MONGO_DB'])
        db = client.ostrich

        user_id = int(self.user_id)
        user_set = db.user_access_frequency.find({'_id': user_id}).count()
        if user_set:
            db.user_access_frequency.update(
                {'_id': user_id},
                {'$addToSet': {
                    'dates': datetime.now().strftime('%Y-%m-%d')
                }})
        else:
            db.user_access_frequency.insert({
                '_id':
                user_id,
                'dates': [datetime.now().strftime('%Y-%m-%d')]
            })
        return True
Ejemplo n.º 7
0
def delete_user(user_id):
    """Delete user from user table

            Returns:
                String: Success if operation successful else Error
    """
    if request.method == 'DELETE' and 'password' in request.form:
        connection = mysql.connect()
        cursor = connection.cursor()
        try:
            cursor.execute(
                "SELECT `hashedPassword` FROM `mailuser` WHERE id = %s",
                user_id)
            pw_hash = cursor.fetchone()

            #: if provided password is correct, delete user
            if pw_hash and bcrypt.check_password_hash(
                    pw_hash[0], request.form['password']):
                cursor.execute("DELETE FROM `mailuser` WHERE id = %s", user_id)
                connection.commit()
                cursor.close()
                connection.close()
                return "Success"
        except MySQLError:
            cursor.close()
            connection.close()
            return "Error connecting to mysql database"
        except ValueError:
            cursor.close()
            connection.close()
            return "Wrong password"

    return "Could not delete user"
Ejemplo n.º 8
0
def service():
  cur = mysql.connect().cursor()
  cur.execute("SELECT * FROM post ORDER BY id ASC LIMIT 12")
  r = [dict((cur.description[i][0], value)
      for i, value in enumerate(row)) for row in cur.fetchall()]
  print(r)
  return jsonify(r)
Ejemplo n.º 9
0
 def indexCollections(self, query_condition):
     #TODO fetch collection object from Collection class
     cursor = mysql.connect().cursor()
     condition = 'WHERE c.partial_order = 0'
     if query_condition:
         condition += ' AND ' + query_condition
     cursor.execute("""SELECT c.*,
        (select group_concat(ci.item_id SEPARATOR ',') FROM collections_items ci
        WHERE ci.collection_id = c.collection_id) AS item_ids
        FROM collections c """ + condition)
     num_items = cursor.rowcount
     for i in range(num_items):
         collection = Utils.fetchOneAssoc(cursor)
         collection['item_ids'] = [
             int(_) for _ in collection['item_ids'].split(',')
         ]
         collection['metadata'] = self.fetchCollectionsMetadata(
             collection['collection_id'])
         try:
             self.es.index(index=self.es_index,
                           doc_type='collections',
                           id=collection['collection_id'],
                           body=collection,
                           refresh=True)
             self.updateItems(collection['item_ids'], collection['name'])
         except Exception, e:
             print str(e), collection['collection_id']
Ejemplo n.º 10
0
    def indexItems(self, query_condition='', limit='', custom_keys={}):

        search_query = """SELECT i.item_id, i.item_name, i.author, i.price,i.ratings, 
        i.num_ratings, i.num_reviews, i.img_small, i.asin, i.goodreads_id, i.summary, i.slug_url,
        (select group_concat(c.category_name SEPARATOR '|') FROM categories c 
        INNER JOIN items_categories ic ON ic.category_id = c.category_id WHERE 
        ic.item_id = i.item_id) AS categories
        FROM items i WHERE i.active=1"""

        if query_condition:
            search_query += query_condition

        if limit:
            search_query += ' LIMIT ' + limit

        cursor = mysql.connect().cursor()
        cursor.execute(search_query)

        num_items = cursor.rowcount
        for num in range(num_items):
            record = Utils.fetchOneAssoc(cursor)
            print record['item_id']
            if record['categories'] is not None:
                record['categories'] = record['categories'].split("|")
            else:
                record['categories'] = []
            record = self.fetchItemProperties(record, custom_keys)
            record = self.extendItemProperties(record)

            record = self.handleUnicode(record)
            self.indexItemObject(record)
Ejemplo n.º 11
0
    def getArborBooks(client):
        cursor = mysql.connect().cursor()
        cursor.execute(
            """SELECT * FROM arbor_inventory WHERE
                 client=%s GROUP BY item_id""", (client.lower(), ))
        stock, taken = [], []
        for _ in range(cursor.rowcount):
            item = Utils.fetchOneAssoc(cursor)
            item['arbor_id'] = '_'.join([
                item['client'],
                str(item['item_id']),
                str(item['inventory_id'])
            ])
            item['item'] = WebUtils.extendItemWebProperties(
                [Item(item['item_id']).getObj()])[0]

            categories = []
            for category in item['item']['categories'][:3]:
                categories.append(Item.fetchCategory(name=category))
            item['item']['categories'] = categories

            if item['in_stock']:
                stock.append(item)
            else:
                taken.append(item)
        return [stock, taken]
Ejemplo n.º 12
0
    def getUserOrders(user_id, for_mobile=False):
        cursor = mysql.connect().cursor()
        cursor.execute(
            """SELECT * FROM arbor_orders ao 
            INNER JOIN  arbor_inventory ai ON ai.inventory_id = ao.inventory_id
            WHERE ao.user_id = %s ORDER BY order_placed DESC""", (user_id, ))
        orders = {'reading': [], 'history': []}
        for _ in range(cursor.rowcount):
            item = Utils.fetchOneAssoc(cursor)
            item['arbor_id'] = '_'.join([
                item['owner'],
                str(item['item_id']),
                str(item['inventory_id'])
            ])
            if for_mobile:
                item['items'] = [
                    WebUtils.extendItemWebProperties(
                        [Item(item['item_id']).getObj()])[0]
                ]
            else:
                item['item'] = WebUtils.extendItemWebProperties(
                    [Item(item['item_id']).getObj()])[0]

            if not item['order_returned']:
                orders['reading'].append(item)
            else:
                orders['history'].append(item)
        return orders
Ejemplo n.º 13
0
    def get(self):

        mydata = parser.parse_args()

        conn = mysql.connect()
        cursor = conn.cursor()
        result = []

        cursor.execute("select * from orders ")

        data = cursor.fetchall()

        for i in data:

            result.append({
                'orderId': i[0],
                'itemId': i[1],
                "userId": i[2],
                "quantity": i[3],
                "price": i[4]
            })

        if result != []:
            return {"data": result, "statusCode": 1}
        else:
            return {"message": "no data found", "statusCode": 0}
Ejemplo n.º 14
0
def news_comments(id, page=1):
    cur = mysql.connect().cursor()
    cur.execute("select count(*) from Comments_news where newsid = %s" % (id))
    total = cur.fetchone()[0]
    page, per_page, offset = get_page_items()
    sql = "select * from Comments_news where newsid=%s order by id limit {}, {}".format(
        offset, per_page) % (id)
    cur.execute(sql)
    comments = cur.fetchall()
    news = News.query.filter_by(newsid=id).first()
    p = re.compile(r'<.*?>')
    new = p.sub('', news.Content)
    pagination = get_pagination(page=page,
                                per_page=per_page,
                                total=total,
                                record_name='news_comments',
                                format_total=True,
                                format_number=True)
    count = 0
    comm = Comments_news.query.filter_by(newsid=id).all()
    if comm:
        for comment in comm:
            count = Comments_news.query.filter_by(
                newsid=id).count() + Replies_news.query.filter_by(
                    ids=comment.id).count()
    return render_template('news_comments.html',
                           news=news,
                           new=new,
                           comments=comments,
                           count=count,
                           page=page,
                           per_page=per_page,
                           pagination=pagination)
Ejemplo n.º 15
0
def create_user():
    """Append new user to user table

            Returns:
                String: Success if operation successful else Error
    """
    if request.method == 'POST' and 'username' in request.form and 'password' in request.form \
            and 'mail' in request.form:

        name = request.form['username']
        mail = request.form['mail']
        password = request.form['password']

        if not password or not name or not mail:
            return "password, username and email fields cannot be empty"

        connection = mysql.connect()
        cursor = connection.cursor()

        pw_hash = bcrypt.generate_password_hash(password)
        try:
            cursor.execute(
                "INSERT INTO `mailuser` (`name`, `hashedPassword`, `mailAddress`) VALUES (%s, %s, %s)",
                (name, pw_hash, mail))
            connection.commit()
        except MySQLError:
            return "Error connecting to mysql database"
        return "Success"
    return "Error"
Ejemplo n.º 16
0
def predict_android():
    # get data
    json_data = request.get_json()
    _time = json_data.get('time')
    _mac = json_data.get('mac')
    result = json_data.get('result')

    # push notifications & send messages
    push_url = base_url + "/admin-api/trigger-push-notifications"
    requests.post(url=push_url,
                  data=json.dumps({
                      "title": "응급상황이 발생했습니다.",
                      "body": result
                  }))
    # send_url = base_url + "/admin-api/send-messages"
    # requests.post(url=send_url, data=json.dumps(
    #     {"title": "응급상황이 발생했습니다.", "body": result}))

    # mysql insert emergency log
    conn = mysql.connect()
    curs = conn.cursor()
    curs.callproc('p_insert_emergency', (result, _time, _mac))
    conn.commit()
    conn.close()

    return jsonify({"status": "success"})
Ejemplo n.º 17
0
def login():
    form = LoginForm()
    # s.bind((HOST,PORT))
    # s.listen(1)				#Listen for a connection
    # incoming_socket, address = s.accept()
    # sendData1 = "Login detected at" + time.strftime("%a, %d %b %Y %H:%M:%S %Z")
    # print(sendData1)
    # stringAddress= str(address)

    if form.validate_on_submit():
        username = form.username.data
        password = form.password.data
        cursor = mysql.connect().cursor()
        cursor.execute("SELECT * FROM Employees WHERE username='******' AND password_hash='" + password + "'")
        data = cursor.fetchone()
        if data is not None:
            session['logged_in'] = True
            session['username'] = username
            # return """<h1>Login Successful</h1>"""
            return redirect(url_for('home'))
        else:
            sendData1 = "This is the Organization's SMTP MAIL Service, <br> Login detected at " + time.strftime(
                "%a, %d %b %Y %H:%M:%S %Z")
            sendData2 = request.environ.get('HTTP_X_REAL_IP',
                                            request.remote_addr)

            return """<h1>Username or Password is incorrect!</h1><br>
					<a href= "/login" >Login</a><br><br>
					""" + sendData1 + """
					<br>
					Intruder IP = """ + sendData2 + """
					<br>
					"""
    return render_template('login.html', form=form)
Ejemplo n.º 18
0
def update_user():
    conn = None
    cursor = None
    try:
        _first_name = request.form['inputFirstName']
        _last_name = request.form['inputLastName']
        _email = request.form['inputEmail']
        _password = request.form['inputPassword']
        _id = request.form['id']
        # validate the received values
        if _first_name and _last_name and _email and _password and _id and request.method == 'POST':
            #do not save password as a plain text
            _hashed_password = generate_password_hash(_password)
            print(_hashed_password)
            # save edits
            sql = "UPDATE users SET first_name=%s, last_name=%s, email=%s, password=%s WHERE id=%s"
            data = (_first_name, _last_name, _email, _hashed_password, _id,)
            conn = mysql.connect()
            cursor = conn.cursor()
            cursor.execute(sql, data)
            conn.commit()
            flash('User updated successfully!')
            return redirect('/')
        else:
            return 'Error while updating user'
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        conn.close()
Ejemplo n.º 19
0
    def getData(self, collection_id):
        from app import cache
        cache_key = 'collection_' + str(collection_id)
        collection_data = cache.get(cache_key)
        if collection_data:
            return collection_data

        cursor = mysql.connect().cursor()
        cursor.execute(
            """SELECT c.*, 
            (select group_concat(ci.item_id order by ci.sort_order asc separator ',') from collections_items ci 
            where ci.collection_id = c.collection_id) as item_ids,
            (select group_concat(concat(cm.meta_key,":",cm.meta_value) separator '&') from collections_metadata cm 
            where cm.collection_id = c.collection_id) as metadata
            FROM collections c WHERE c.collection_id = %s""",
            (collection_id, ))
        data = Utils.fetchOneAssoc(cursor)

        if data['metadata']:
            collections_metadata_raw = data['metadata']
            data['metadata'] = {}
            for props in collections_metadata_raw.split('&'):
                props_formatted = props.split(':')
                data['metadata'][props_formatted[0]] = props_formatted[1]

        if data['item_ids']:
            data['item_ids'] = [int(_) for _ in data['item_ids'].split(',')]
            data['items'] = Search().getById(data['item_ids'])
        else:
            data['items'] = []

        if not data:
            data = {}
        cache.set(cache_key, data)
        return data
Ejemplo n.º 20
0
    def applyReferralCode(self, code):
        code_details = self.isCodeValid(code)
        if not code_details:
            return False
        else:
            [code_id, referrer_id] = code_details
            conn = mysql.connect()
            apply_code_cursor = conn.cursor()
            apply_code_cursor.execute(
                "UPDATE user_invite_codes SET counter = counter +1 \
                    WHERE code_id = %d" % (code_id))
            conn.commit()

            log_referral_cursor = conn.cursor()
            log_referral_cursor.execute(
                "INSERT INTO referrals (referrer_id, referent_id, \
                    activated, activation_date, source, source_id) VALUES (%d, %d, %d, '%s', '%s', \
                    %d)" %
                (referrer_id, self.user_id, 1, Utils.getCurrentTimestamp(),
                 'invite_code', code_id))
            conn.commit()
            referral_id = log_referral_cursor.lastrowid

            Wallet.creditTransaction(self.wallet_id, self.user_id, 'referral',
                                     referral_id)
            return True
Ejemplo n.º 21
0
def index():
    if request.method == 'GET':
        if session.get('auth'):
            return render_template('index.html')
        else:
            return redirect(url_for('login'))
    else:
        conn = mysql.connect()
        cur = conn.cursor()
        cur.execute('desc pokemon_mv;')
        data = cur.fetchmany(3)
        attrs = []
        for d in data:
            attrs.append(d[0])

        cur.execute('select * from pokemons;')
        data1 = cur.fetchmany(5)

        cur.execute('select * from pokemons;')
        data2 = cur.fetchmany(5)

        cur.close()
        print(len(data1), len(data2))
        return render_template('relation.html',
                               attrs=attrs,
                               data1=data1,
                               data2=data2,
                               data=data1)
Ejemplo n.º 22
0
def add_user():
    conn = None
    cursor = None
    try:
        _first_name = request.form['inputFirstName']
        _last_name = request.form['inputLastName']
        _email = request.form['inputEmail']
        _password = request.form['inputPassword']
        # validate the received values
        if _first_name and _last_name and _email and _password and request.method == 'POST':
            #do not save password as a plain text
            _hashed_password = generate_password_hash(_password)
            # save edits
            sql = "INSERT INTO users(first_name, last_name, email, password) VALUES(%s, %s, %s, %s)"
            data = (_first_name, _last_name, _email, _hashed_password)
            conn = mysql.connect()
            cursor = conn.cursor()
            cursor.execute(sql, data)
            conn.commit()
            flash('User added successfully!')
            return redirect('/')
        else:
            return 'Error while adding user'
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        conn.close()
Ejemplo n.º 23
0
    def post(self):
        try:
            """
            Synchronous Key Auth
            """
            # Parse the arguments
            parser = reqparse.RequestParser()
            parser.add_argument('username', type=str, help='username for authentication')
            parser.add_argument('password', type=str, help='Password for authentication')
            parser.add_argument('encoded', type=str, help='encoded payload')
            args = parser.parse_args()

            _username = args['username']
            _password = args['password']
            __encoded = args['encoded']
            _hashed_password = generate_password_hash(_password)

            conn = mysql.connect()
            cursor = conn.cursor()
            data = cursor.callproc('sp_AuthenticateUser', (_username, _hashed_password))
            secret = data[0]

            result = jwt.decode(_encoded,  secret, algorithm='HS512')

            if(_username == result.username):
                return {'status' : 200, 'message' : 'authenticated'}
            else:
                return {'status' : 100, 'message' : 'rejected'}
        except Exception as e:
            return {'error': str(e)}
Ejemplo n.º 24
0
def login():
    if request.method == 'POST':
        try:
            username = request.form['username']
            password = request.form['password']
            
            db      = mysql.connect()
            cursor  = db.cursor()
            cursor.callproc('validateLogin',(username))
            data    = cursor.fetchall()
            
            if len(data) > 0:
                if check_password_hash(str(data[0][3]),password):
                    session['user'] = data[0][0]
                    return redirect('/')
                else:
                    return render_template('error.html',error = 'Wrong username or Password.')
            else:
                return render_template('error.html',error = 'Wrong username or Password.')
                
        except Exception as e:
            return render_template('error.html',error = str(e))
        finally:
            cursor.close()
            db.close()
    
    if session.get('user'):
        return render_template('userHome.html')
    else:
        return render_template('signin.html')
Ejemplo n.º 25
0
def addrecipe():
    if request.method == 'POST':
        try:
            if session.get('user'):
                title       = request.form['title']
                description = request.form['description']
                
                user        = session.get('user')
    
                db = mysql.connect()
                cursor = db.cursor()
                cursor.callproc('addRecipe',(title,description,user))
                data = cursor.fetchall()
    
                if len(data) is 0:
                    db.commit()
                    return redirect('/userHome')
                else:
                    return render_template('error.html',error = 'An error occurred!')
    
            else:
                return render_template('error.html',error = 'Unauthorized Access')
        except Exception as e:
            return render_template('error.html',error = str(e))
        finally:
            cursor.close()
            db.close()    
    return render_template('addRecipe.html')
Ejemplo n.º 26
0
    def getData(self, user_id, login_type):

        get_data_query = """SELECT u.user_id, u.username, u.name, u.email, u.phone, u.google_id, 
                u.gcm_id, u.picture_url, u.date_created, ui.invite_code, uw.wallet_id, uw.amount as wallet_balance FROM users u 
                LEFT JOIN user_invite_codes ui ON ui.user_id = u.user_id 
                LEFT JOIN user_wallet uw ON uw.user_id = u.user_id 
                WHERE u.%s = %d"""
        if login_type != 'user_id':
            get_data_query = get_data_query.replace("%d", "'%s'")
        else:
            user_id = int(user_id)

        obj_cursor = mysql.connect().cursor()
        obj_cursor.execute(get_data_query % (login_type, user_id))
        self.data = Utils.fetchOneAssoc(obj_cursor)
        if not self.data:
            self.data = {}
        else:
            # TODO shift this to getAddressInfo
            self.data['address'] = []
            obj_cursor.execute("SELECT * FROM user_addresses WHERE \
                    user_id = %d" % (self.user_id))
            num_address = obj_cursor.rowcount
            for i in range(num_address):
                address_data = Utils.fetchOneAssoc(obj_cursor)
                address_data.update(
                    Utils.getDeliveryCharge(address_data['distance']))
                self.data['address'].append(address_data)
Ejemplo n.º 27
0
def signup():
    if request.method == 'POST':
        try:
            email       = request.form['email'].strip()
            username    = request.form['username'].strip()
            password    = request.form['password'].strip()
            firstname   = request.form['firstname'].strip()
            lastname    = request.form['lastname'].strip()
            gender      = request.form['gender'].strip()
            birthday    = request.form['birthday'].strip()
            
            if email and username and password and firstname and lastname and birthday and gender:
                db      = mysql.connect()
                cursor  = db.cursor()
                password_hash = generate_password_hash(password)
                
                cursor.callproc('createUser',(email, username, password_hash, firstname, lastname, gender, birthday))
                data = cursor.fetchall()
    
                if len(data) is 0:
                    db.commit()
                    return json.dumps({'message':'Account successfully created!'})
                else:
                    return json.dumps({'error':str(data[0])})
            else:
                return json.dumps({'html':'<span>All fields are required</span>'})
                
        except Exception as e:
            return json.dumps({'error':str(e)})
        finally:
            cursor.close() 
            db.close()     
            
    return render_template('signup.html')
Ejemplo n.º 28
0
def create_mail():
    """Append new email to mail table

            Returns:
                String: Success if operation successful else Error
    """
    if request.method == 'POST' and 'fromMail' in request.form and 'fromName' in request.form \
            and 'receivedData' in request.form and 'subject' in request.form and 'Folder_ID' in request.form \
            and 'MailUser_ID' in request.form and 'body' in request.form:

        from_mail, from_name, received_data, subject, body, folder_id, mail_user_id = get_mail_form_data(
            request.form)
        if not from_name or not from_mail or not received_data or not subject or not body \
                or not folder_id or not mail_user_id:
            return "Missing data, could not append email to database"

        connection = mysql.connect()
        cursor = connection.cursor()

        try:
            cursor.execute(
                "INSERT INTO `mail` (fromMail, fromName, receivedData, subject, body, Folder_ID, MailUser_ID)"
                "VALUES (%s, %s, %s, %s, %s, %s, %s)",
                (from_mail, from_name, received_data, subject, body, folder_id,
                 mail_user_id))
            connection.commit()
            cursor.close()
            connection.close()
            return "Success"
        except MySQLError:
            cursor.close()
            connection.close()
            return "Error connecting to mysql database"

    return "Error"
Ejemplo n.º 29
0
def pickupSchedule():
    current_ts = Utils.getCurrentTimestamp()
    date = current_ts.split(' ')[0]

    # Order Pickup
    order_list = []
    cursor = mysql.connect().cursor()
    user_id_format_char = ",".join(["%s"] * len(Utils.getAdmins()))

    cursor.execute(
        """SELECT COUNT(*) 
        FROM orders
        WHERE DATE(order_return) = %s AND order_id NOT IN
        (SELECT DISTINCT parent_id FROM orders) AND user_id NOT IN (""" +
        user_id_format_char + """)
        AND order_status >= 4""",
        tuple([date]) + tuple(Utils.getAdmins()))
    order_ids = cursor.fetchone()
    if order_ids[0] > 0:
        Utils.notifyAdmin(-1, "PICKUPS DUE TODAY!!")

    cursor.execute(
        """SELECT COUNT(*)
        FROM b2b_users WHERE DATE(timestamp) = %s""",
        (Utils.getDefaultReturnTimestamp(current_ts, -21).split(' ')[0], ))
    order_ids = cursor.fetchone()
    if order_ids[0] > 0:
        Utils.notifyAdmin(-1, "B2B PICKUPS DUE!!")

    return None
Ejemplo n.º 30
0
 def getAddressInfo(address_id):
     address_cusor = mysql.connect().cursor()
     address_cusor.execute("""SELECT * FROM user_addresses
             WHERE address_id = %d""" % (address_id))
     address_obj = Utils.fetchOneAssoc(address_cusor)
     address_obj.update(Utils.getDeliveryCharge(address_obj['distance']))
     return address_obj if address_obj else {}
Ejemplo n.º 31
0
 def removeFromUnregistered(self):
     conn = mysql.connect()
     cursor = conn.cursor()
     cursor.execute("""DELETE FROM users_unregistered WHERE gcm_id = %s""",
                    (self.gcm_id, ))
     conn.commit()
     return True
Ejemplo n.º 32
0
    def submitReview(review_data):
        user_id = Utils.getParam(review_data, 'user_id')
        item_id = Utils.getParam(review_data, 'item_id')
        order_id = Utils.getParam(review_data, 'order_id')
        if not (user_id and item_id and order_id):
            return False

        title = Utils.getParam(review_data, 'title')
        description = Utils.getParam(review_data, 'description')
        rating = Utils.getParam(review_data, 'rating')

        conn = mysql.connect()
        cursor = conn.cursor()

        cursor.execute(
            """SELECT review_id FROM reviews WHERE user_id = %s AND item_id = %s""",
            (user_id, item_id))
        present_review_id = cursor.fetchone()
        if present_review_id:
            review_data['review_id'] = present_review_id[0]
            review = Review(review_id=review_data['review_id'])
            review.editReview(review_data)
            return review_data['review_id']

        cursor.execute(
            """INSERT INTO reviews (user_id, item_id, order_id, title, description, rating) VALUES (%s,%s,%s,%s,%s,%s)""",
            (user_id, item_id, order_id, title, description, rating))
        conn.commit()
        review_id = cursor.lastrowid

        #TODO Index item again with review
        return review_id
Ejemplo n.º 33
0
    def updateOrderComment(data):
        data['delivered_by'] = Utils.getParam(data, 'delivered_by', default='')
        data['delivery_amount'] = Utils.getParam(data,
                                                 'delivery_amount',
                                                 default=0)
        data['picked_by'] = Utils.getParam(data, 'picked_by', default='')
        data['pickup_amount'] = Utils.getParam(data,
                                               'pickup_amount',
                                               default=0)
        data['edited'] = Utils.getParam(data, 'edited', default=0)

        conn = mysql.connect()
        cursor = conn.cursor()
        cursor.execute(
            """UPDATE orders_admin_notes SET 
                comment = %s, 
                edited = %s,
                delivered_by = %s,
                delivery_amount = %s,
                picked_by = %s,
                pickup_amount = %s
                WHERE order_id = %s AND order_type = %s""",
            (data['comment'], data['edited'], data['delivered_by'],
             data['delivery_amount'], data['picked_by'], data['pickup_amount'],
             data['order_id'], data['order_type']))
        conn.commit()
        affected = cursor.rowcount
        if not affected:
            cursor.execute(
                """INSERT INTO orders_admin_notes (order_id, order_type, comment, edited)
                VALUES (%s, %s, %s, %s)""",
                (data['order_id'], data['order_type'], data['comment'],
                 data['edited']))
            conn.commit()
        return True
Ejemplo n.º 34
0
    def getAllRentals(self):
        from app.models import Order
        inv_cursor = mysql.connect().cursor()
        inv_cursor.execute("""SELECT i.*, l.*
                FROM inventory i
                INNER JOIN lenders l ON l.inventory_id = i.inventory_id
                WHERE l. user_id = %d""" % (self.user_id))
        num_items = inv_cursor.rowcount
        inv_items = []
        for slot in range(num_items):
            inv_info = Utils.fetchOneAssoc(inv_cursor)
            inv_info['items'] = [Item(int(inv_info['item_id'])).getObj()]
            all_timeslots = Order.getTimeSlot()
            ts = [
                _ for _ in all_timeslots
                if _['slot_id'] == inv_info['pickup_slot']
            ][0]
            inv_info['pickup_time'] = Utils.cleanTimeSlot(ts)
            inv_items.append(inv_info)

        rental_statses = {"rentals": [], "rental_history": []}
        current_timestamp = datetime.now()
        for item in inv_items:
            date_removed = datetime.strptime(item['date_removed'],
                                             "%Y-%m-%d %H:%M:%S")
            diff = current_timestamp - date_removed
            if diff.days > 0:
                rental_statses["rental_history"].append(item)
            else:
                rental_statses["rentals"].append(item)
        return rental_statses
Ejemplo n.º 35
0
def reviewLiked():
    conn = mysql.connect()
    cursor = conn.cursor()
    cursor.execute("SELECT * from liked")
    data = cursor.fetchall()
    cursor.close()
    print "view"
    return [dict(small=row[1], big=row[2]) for row in data]
Ejemplo n.º 36
0
def deleteSubscribers(id):
	conn = mysql.connect()
	cursor = conn.cursor()
	cursor.execute("DELETE FROM SUBSCRIBERS WHERE ID=%s", [id])
	cursor.close()
	conn.commit()
	conn.close()
	return True
Ejemplo n.º 37
0
 def getQuestionAll(self):
     cursor = mysql.connect().cursor()
     cursor.execute("SELECT * from question_value")
     data = cursor.fetchall()
     if data is None:
         return False
     else:
         return data
Ejemplo n.º 38
0
def loadSubscribers():
	conn = mysql.connect()
	cursor = conn.cursor()
	cursor.execute("SELECT id, name, email, phone_num FROM SUBSCRIBERS")
	data = cursor.fetchall()	
	cursor.close ()
  	conn.close ()
	print data
	return data
Ejemplo n.º 39
0
def mobiles(brand, page=1):
    cur = mysql.connect().cursor()
    cur.execute("select count(*) from %s" % (brand))
    total = cur.fetchone()[0]
    page, per_page, offset = get_page_items()
    sql = "select phoneId, Name, Picture, OS, Announced, Type, `Primary`, WLAN, GPS,Bluetooth from %s order by name limit {}, {}".format(offset, per_page) % (brand)
    cur.execute(sql)
    phones = cur.fetchall()
    pagination = get_pagination(page=page, per_page=per_page, total=total, record_name='phones', format_total=True, format_number=True)
    return render_template('phones.html', phones=phones,page=page, per_page=per_page, pagination=pagination,brand=brand)
Ejemplo n.º 40
0
def delete_food_item(user_id, item_id, time):
	conn = mysql.connect()
	cursor = conn.cursor()

	statement = "DELETE FROM Eats WHERE Eats.user_id = %s AND Eats.item_id = %s AND Eats.time = %s"
	cursor.execute(statement, [user_id, item_id, time])

	conn.commit()
	cursor.close()
	conn.close()
Ejemplo n.º 41
0
def delete_symptom(user_id, symptom_id, time):
	conn = mysql.connect()
	cursor = conn.cursor()

	statement = "DELETE FROM Has WHERE Has.user_id = %s AND Has.symptom_id = %s AND Has.time = %s"
	cursor.execute(statement, [user_id, symptom_id, time])

	conn.commit()
	cursor.close()
	conn.close()
Ejemplo n.º 42
0
def processSubscriber(name, email, phone_num):
	if phone_num.isdigit==False:
		return False
	conn = mysql.connect()
	cursor = conn.cursor()
	cursor.execute("INSERT INTO SUBSCRIBERS VALUES (%s, %s, %s, %s);", (0,name, email, int(phone_num)))
	cursor.close ()
   	conn.commit ()
  	conn.close ()
	print name, email, phone_num
	return True
Ejemplo n.º 43
0
def new(page=1):
  cur = mysql.connect().cursor()
  cur.execute("select count(*) from news")
  total = cur.fetchone()[0]
  page, per_page, offset = get_page_items()
  sql = "select * from news order by id limit {}, {}".format(offset, per_page)
  cur.execute(sql)
  news = cur.fetchall()
  pagination = get_pagination(page=page, per_page=per_page, total=total, record_name='news', format_total=True, format_number=True)
  for new in news:
    p = re.compile(r'<.*?>')
    return render_template('all_news.html',news=news,p=p,page=page, per_page=per_page, pagination=pagination)
Ejemplo n.º 44
0
def news_comment(id):
  form = CommentForm()
  cur = mysql.connect().cursor()
  sql = "select newsid, Picture, Content, Name from news WHERE newsid=%s" %(id)
  cur.execute(sql)
  news = cur.fetchone()
  p = re.compile(r'<.*?>')
  new = p.sub('', news[2])
  if form.validate():
    comment = Comments_news(newsid=id,created_at=datetime.datetime.now(),body=form.body.data,author=form.author.data)
    db.session.add(comment)
    db.session.commit()
  return render_template('news_comment.html',news=news,new=new,form=form)
Ejemplo n.º 45
0
def sign_up_user(name, email, password):
	conn = mysql.connect()
	cursor = conn.cursor()

	# query = "SELECT User.user_id FROM User WHERE User.email = %s"
	statement = "INSERT INTO User VALUES (0, %s, %s, %s)"
	cursor.execute(statement, [name, email, password])
	# cursor.execute(query, [email])
	# data = cursor.fetchone()

	conn.commit()
	cursor.close() 
	conn.close()
Ejemplo n.º 46
0
def add_food_item(user_id, item_name, ingr_list, time):
	conn = mysql.connect()
	cursor = conn.cursor()

	# Insert into Food_Item, if necessary
	# query = "SELECT MAX (Food_Item.item_id) FROM Food_Item WHERE Food_Item.name = %s"
	query = "SELECT LAST_INSERT_ID()"
	statement = "INSERT INTO Food_Item VALUES (0, %s)"
	# cursor.execute(query, [item_name])
	#data = cursor.fetchone()
	# if data is None:
	cursor.execute(statement, [item_name])
	cursor.execute(query)
	data = cursor.fetchone()
	item_id = int(data[0])


	# Insert into Eats, if necessary
	query = "SELECT Eats.item_id FROM Eats WHERE Eats.user_id = %s AND Eats.item_id = %s and Eats.time = %s"
	statement = "INSERT INTO Eats VALUES (%s, %s, %s)"
	cursor.execute(query, [user_id, item_id, time])
	data = cursor.fetchone()
	#if data is None:
	cursor.execute(statement, [user_id, item_id, time])
		
	# Insert into Ingredients, if necessary
	
	# ingr_list = map(str.strip, str(ingr_list).split(','))
	for ingr in ingr_list:
		query = "SELECT Ingredient.ingredient_id FROM Ingredient WHERE Ingredient.name = %s"
		statement = "INSERT INTO Ingredient VALUES (0, %s)"
		cursor.execute(query, [ingr])
		data = cursor.fetchone()
		if data is None:
			cursor.execute(statement, [ingr])
			cursor.execute(query, [ingr])
			data = cursor.fetchone()
		ingr_id = int(data[0])

		# Insert into Contains, if necessary
		query = "SELECT Contains.item_id FROM Contains WHERE Contains.ingredient_id = %s AND Contains.item_id = %s"
		statement = "INSERT INTO Contains VALUES (%s, %s)"
		cursor.execute(query, [ingr_id, item_id])
		data = cursor.fetchone()
		if data is None:
			cursor.execute(statement, [ingr_id, item_id])

	conn.commit()
	cursor.close()
	conn.close()
Ejemplo n.º 47
0
def get_result(query, values):
	conn = mysql.connect()
	cursor = conn.cursor()
	cursor.execute(query, values)
	data = []
	row = row_to_dict(cursor)
	
	while row is not None:
		data.append(row)
		row = row_to_dict(cursor)

	cursor.close() 
	conn.close()
	return data
Ejemplo n.º 48
0
def LikeImage(smallURL, bigURL):
    conn = mysql.connect()
    cursor = conn.cursor()
    cursor.execute("SELECT * from liked where url_s = '"+smallURL+"'")
    data = cursor.fetchone()
    if data is None:
        cursor.execute("INSERT into liked values ('', '"+smallURL+"','"+bigURL+"')")
        conn.commit()
        cursor.close()
        print "INSERT"
        return "This image is liked!"
    else:
        cursor.close()
        print "already liked"
        return "This image has already been liked!"
Ejemplo n.º 49
0
def comments(id, page=1):
  cur = mysql.connect().cursor()
  cur.execute("select count(*) from Comments where phoneId = %s" % (id))
  total = cur.fetchone()[0]
  page, per_page, offset = get_page_items()
  phon = ""
  phones = [Samsung,Apple,Microsoft,Nokia,Sony,LG,HTC,Motorola,Huawei,Lenovo,Xiaomi,Acer,Asus,Oppo,Blackberry,Alcatel,
            ZTE,Toshiba,Gigabyte,Pantech,XOLO,Lava,Micromax,BLU,Spice,Verykool,Maxwest,Celkon,Gionee,Vivo,NIU,Yezz,Parla,Plum]
  for phone in phones:
    if not phon:
      phon = db.session.query(phone).filter_by(phoneId=id).first()
  sql = "select * from Comments where phoneId=%s order by id limit {}, {}".format(offset, per_page) % (id)
  cur.execute(sql)
  comments = cur.fetchall()
  count = Comments.query.filter_by(phoneId=id).count()
  pagination = get_pagination(page=page, per_page=per_page, total=total, record_name='comments', format_total=True, format_number=True)
  return render_template('comments.html',phone=phon,comments=comments, id=id, count=count,page=page, per_page=per_page, pagination=pagination)
Ejemplo n.º 50
0
 def get(self):
     cursor = mysql.connect().cursor()
     cursor.execute("SELECT * from next_question where soft_delete = 0")
     data = cursor.fetchall()
     my_question = []
     for i in data:
         self.nid = i[0]
         self.option_one = i[2]
         self.option_two = i[3]
         self.option_three = i[4]
         self.option_four = i[5]
         my_question.append({'nid':self.nid,
                         'option_one':self.option_one,
                         'option_two':self.option_two,
                         'option_three':self.option_three,
                         'option_four':self.option_four})
     return my_question
Ejemplo n.º 51
0
def news_comments(id,page=1):
  cur = mysql.connect().cursor()
  cur.execute("select count(*) from Comments_news where newsid = %s" % (id))
  total = cur.fetchone()[0]
  page, per_page, offset = get_page_items()
  sql = "select * from Comments_news where newsid=%s order by id limit {}, {}".format(offset, per_page) % (id)
  cur.execute(sql)
  comments = cur.fetchall()
  news = News.query.filter_by(newsid=id).first()
  p = re.compile(r'<.*?>')
  new = p.sub('', news.Content)
  pagination = get_pagination(page=page, per_page=per_page, total=total, record_name='news_comments', format_total=True, format_number=True)
  count = 0
  comm = Comments_news.query.filter_by(newsid=id).all()
  if comm:
    for comment in comm:
      count = Comments_news.query.filter_by(newsid=id).count()+Replies_news.query.filter_by(ids=comment.id).count()
  return render_template('news_comments.html',news=news,new=new,comments=comments,count=count,page=page, per_page=per_page, pagination=pagination)
Ejemplo n.º 52
0
 def getQuestionbyId(self):
     cursor = mysql.connect().cursor()
     cursor.execute("SELECT * from question_value where soft_delete = 0 and aid = "+str(self.aid))
     data = cursor.fetchall()
     my_question = []
     for i in data:
         self.aid = i[0]
         self.question = i[1]
         self.option_one = i[2]
         self.option_two = i[3]
         self.option_three = i[4]
         self.option_four = i[5]
         my_question.append({'aid':self.aid,
                      'question':self.question,
                         'option_one':self.option_one,
                         'option_two':self.option_two,
                         'option_three':self.option_three,
                         'option_four':self.option_four})
     return my_question
Ejemplo n.º 53
0
def getRecipes():
    try:
        if session.get('user'):
            _user = session.get('user')

            con = mysql.connect()
            cursor = con.cursor()
            cursor.callproc('sp_GetWishByUser',(_user,))
            recipes = cursor.fetchall()

            recipes_dict = []
            for recipe in recipes:
                recip_dict = {
                        'Id': recipe[0],
                        'Title': recipe[1],
                        'Description': recipe[2],
                        'Date': recipe[4]}
                recipes_dict.append(recip_dict)

            return json.dumps(recipes_dict)
        else:
            return render_template('error.html', error = 'Unauthorized Access')
    except Exception as e:
        return render_template('error.html', error = str(e))
Ejemplo n.º 54
0
def dislikeImage(smallURL, bigURL):
    conn = mysql.connect()
    cursor = conn.cursor()
    cursor.execute("DELETE from liked where url_s = '"+smallURL+"'")
    conn.commit()
    print "delete"
Ejemplo n.º 55
0
def get_cursor():
    """ Get a MySQL cursor """
    cursor = mysql.connect().cursor()
    return cursor
Ejemplo n.º 56
0
def get_connection():
    """ Tests MySQL connection """
    return mysql.connect()
Ejemplo n.º 57
0
def check_connection():
    """ Tests MySQL connection """
    conn = mysql.connect()
    return conn.close()
Ejemplo n.º 58
0
 def insert(self):
     conn = mysql.connect()
     cursor = conn.cursor()
     cursor.execute('insert into question_value(question,option_one,option_two,option_three,option_four) values("'+self.question+'","'+self.option_one+'","'+self.option_two+'","'+self.option_three+'","'+self.option_four+'")')
     conn.commit()
     return 'success'
Ejemplo n.º 59
0
from flaskext.mysql import MySQL
from app import mysql

connection = mysql.connect()

def get_top_questions(q_ref_id=None):
    cursor = connection.cursor()
    query = "SELECT * from Questions order by q_votes desc"
    if q_ref_id:
        query = "SELECT * from Questions where q_ref_id = '"+q_ref_id+"' order by q_votes desc"

    cursor.execute(query)

    data = cursor.fetchall()

    results = []

    for _d in data:
        print _d
        _data = {
            'q_id': _d[0],
            'q_question': _d[1],
            'q_ref_id': _d[2],
            'q_user_id': _d[3],
            'q_votes': _d[4],
            'q_state': _d[5]
            }
        results.append(_data)

    return results