def more_reviews():
if request.method == 'POST':
views_amount = request.form.get('views_amount')
print(views_amount)
sql = '''SELECT reader.reader_name, reader.reader_id
FROM reader
WHERE reader_id IN
(SELECT comment_by FROM review
GROUP BY comment_by
HAVING COUNT(comment_by) > '%s');''' % views_amount
cursor = db.cursor()
try:
cursor.execute(sql)
db.commit()
answers = cursor.fetchall()
print(answers)
if answers:
return render_template('show_readers_views.html',
answers=answers,
nav='reader',
amount=views_amount)
else:
return render_template('more_views.html', error='no')
except:
db.rollback()
return render_template('more_views.html', error='error')
return render_template('more_views.html')
def create(account_id=None,
application_name=None,
application_guid=None,
application_key=None,
application_secret=None,
application_algorithm=None,
created_user_id=None,
app_metadata=None,
training_status=None):
try:
application = Applications(account_id=account_id,
application_name=application_name,
application_guid=application_guid,
application_key=application_key,
application_secret=application_secret,
application_algorithm=application_algorithm,
created_user_id=created_user_id,
app_metadata=app_metadata,
training_status=training_status)
db.add(application)
db.commit()
return application.application_id
except Exception as e:
db.rollback()
raise Exception(e.message)
def read_by_user(user_id=None, account_id=None):
try:
nerds = db.query(Nerds).filter(Nerds.users.any(user_id=user_id)). \
filter(Nerds.account_id.like(account_id)).all()
return nerds
except Exception as e:
db.rollback()
raise Exception(e.message)
def read_by_user(user_id=None, account_id=None):
try:
nerds = db.query(Nerds).filter(Nerds.users.any(user_id=user_id)). \
filter(Nerds.account_id.like(account_id)).all()
return nerds
except Exception as e:
db.rollback()
raise Exception(e.message)
def save(self):
db.add(self)
try:
db.commit()
except Exception as ex:
db.rollback()
db.add(self)
db.commit()
def truncate_images():
"""Remove all keywords"""
try:
db.query(Image).delete()
db.query(Keyword).delete()
db.commit()
except sqlalchemy.exc.SQLAlchemyError as sqlexc:
db.rollback()
print(sqlexc)
def add_organization(self,
name,
manager='',
status=8,
domain='',
extra=None,
address='',
phone='',
website='',
notes=''):
"""
creates an organization in the osticket database
"""
def get_org_id_from_name():
query_template = """
SELECT id FROM ost_organization WHERE name=%s
"""
cur = db.cursor()
cur.execute(query_template, (name, ))
org_id = cur.fetchone().get("id")
cur.close()
return {"organization_id": org_id}
query_template = """
START TRANSACTION;
BEGIN;
INSERT INTO ost_organization
(name, manager, status, domain, extra, created, updated)
VALUES (%s, %s, %s, %s, %s, %s, %s);
SET @OrgID = LAST_INSERT_ID();
INSERT INTO ost_organization__cdata
(org_id, name, address, phone, website, notes)
VALUES (@OrgID, %s, %s, %s, %s, %s);
COMMIT;
SELECT @OrgID;
"""
cur = db.cursor()
cur.execute(query_template,
(name, manager, status, domain, extra, datetime.now(),
datetime.now(), name, address, phone, website, notes))
cur.close()
try:
db.commit()
except Exception as exc:
db.rollback()
raise ServiceException(exc.message)
return get_org_id_from_name()
def get_all_apps():
"""
Get all apps
:return:
"""
try:
applications = db.query(Applications).all()
return applications
except Exception as e:
db.rollback()
raise Exception(e.message)
def set_passwd(self, old, new):
try:
user = db.session.query(User).get(current_user.name)
if not check_password_hash(user.passwd, old):
return False
new_hash = generate_password_hash(new)
user.passwd = new_hash
db.session.commit()
return True
except:
db.rollback()
return False
def read_accounts_by_id_list(id_list=None):
"""
Reading the records from a table
:param query:
:return:
"""
try:
accounts = db.query(Accounts) \
.filter(Accounts.account_id.in_(id_list)).all()
return accounts
except Exception as e:
db.rollback()
raise Exception(e.message)
def read(query=None):
"""
Reading the records from a table
:param query:
:return:
"""
try:
accounts = db.query(Accounts) \
.filter_by(**query).all()
return accounts
except Exception as e:
db.rollback()
raise Exception(e.message)
def read_accounts_by_id_list(id_list=None):
"""
Reading the records from a table
:param query:
:return:
"""
try:
accounts = db.query(Accounts) \
.filter(Accounts.account_id.in_(id_list)).all()
return accounts
except Exception as e:
db.rollback()
raise Exception(e.message)
def read(query=None):
"""
Reading the records from a table
:param query:
:return:
"""
try:
accounts = db.query(Accounts) \
.filter_by(**query).all()
return accounts
except Exception as e:
db.rollback()
raise Exception(e.message)
def publish_details():
sql = "SELECT * FROM bookpubdetails"
cursor = db.cursor()
try:
cursor.execute(sql)
db.commit()
answer = cursor.fetchall()
if answer:
return render_template('bookpubdetails.html', details=answer)
else:
return render_template('bookpubdetails.html', error='no')
except:
db.rollback()
return render_template('bookpubdetails.html', error='error')
def insert_raw_news(raw_news_entity: RawNewsEntity):
if raw_news_entity is None:
logging.error(f'failed to create raw_news_entity')
return
logging.info(raw_news_entity)
try:
db.add(raw_news_entity)
db.commit()
except IntegrityError:
db.rollback()
logging.info(f'Duplicated {raw_news_entity.link}')
except Exception as err:
db.rollback()
logging.error(f'failed to store raw_news_entity')
logging.error(err)
def read(query=None):
"""
Reading the records from a table
:param query:
:return:
"""
try:
users = db.query(Users)\
.filter_by(**query).all()
assert isinstance(users, list)
return users
except Exception as e:
db.rollback()
raise Exception(e.message)
def delete(query=None):
"""
This methods deletes the record
:param query:
:return:
"""
try:
db.query(Users).\
filter_by(**query).\
delete()
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def read(query=None):
"""
Reading the records from a table
:param query:
:return:
"""
try:
users = db.query(Users)\
.filter_by(**query).all()
assert isinstance(users, list)
return users
except Exception as e:
db.rollback()
raise Exception(e.message)
def read(query=None):
"""
Reading the records from a table
:param query:
:return:
"""
try:
configuration = db.query(Configuration) \
.filter_by(**query).all()
assert isinstance(configuration, list)
return configuration
except Exception as e:
db.rollback()
raise Exception(e.message)
def delete(query=None):
"""
This methods deletes the record
:param query:
:return:
"""
try:
db.query(Users).\
filter_by(**query).\
delete()
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def add_user(query, user):
"""
Adds users to an existing account
:param query:
:param user:
:return:
"""
try:
nerd = db.query(Nerds). \
filter_by(**query).one()
nerd.users.append(user)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def add_user(query, user):
"""
Adds users to an existing account
:param query:
:param user:
:return:
"""
try:
account = db.query(Accounts). \
filter_by(**query).one()
account.users.append(user)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def library_details():
sql = '''SELECT * FROM librarydetails'''
cursor = db.cursor()
try:
cursor.execute(sql)
db.commit()
answers = cursor.fetchall()
if answers:
return render_template('library_details.html',
answers=answers,
nav='library-details')
else:
return render_template('more_views.html', error='no')
except:
db.rollback()
return render_template('more_views.html', error='error')
def update(query=None, updated_value=None):
"""
This method update the account
:param query:
:param updated_value:
:return:
"""
try:
db.query(Accounts) \
.filter_by(**query) \
.update(updated_value)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def update(query=None, new_user=None):
"""
This method update the user
:param query:
:param new_user:
:return:
"""
try:
db.query(Users) \
.filter_by(**query) \
.update(new_user)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def update(query=None, new_user=None):
"""
This method update the user
:param query:
:param new_user:
:return:
"""
try:
db.query(Users) \
.filter_by(**query) \
.update(new_user)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def add_bot(query, nerd):
"""
Adding applications to accounts
:param query:
:param bot:
:return:
"""
try:
account = db.query(Accounts). \
filter_by(**query).one()
account.nerds.append(nerd)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def update(query=None, updated_value=None):
"""
This method update the account
:param query:
:param updated_value:
:return:
"""
try:
db.query(Nerds) \
.filter_by(**query) \
.update(updated_value)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def add_bot(query, nerd):
"""
Adding applications to accounts
:param query:
:param bot:
:return:
"""
try:
account = db.query(Accounts). \
filter_by(**query).one()
account.nerds.append(nerd)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def create(configuration_guid=None,
bot_name=None,
account_guid=None,
plan_family=None,
plan_metadata=None):
try:
configuration = Configuration(configuration_guid=None,
bot_name=None,
account_guid=None,
plan_family=None,
plan_metadata=None)
db.add(configuration)
db.commit()
return configuration.application_id
except Exception as e:
db.rollback()
raise Exception(e.message)
def make_apikey_inactive(self, api_key):
"""
marks the api key inactive so that one cannot use it
"""
query_template = """
UPDATE ost_api_key SET isactive=0, updated=%s WHERE apikey=%s
"""
cur = db.cursor()
cur.execute(query_template, (datetime.now(), api_key))
cur.close()
try:
db.commit()
except Exception as exc:
db.rollback()
raise ServiceException(exc.message)
def find_staffs():
if request.method == 'POST':
staff_name = request.form.get('staff_name')
sql = "SELECT * FROM librarydetails WHERE librarydetails.staff_name = '%s' ;" % staff_name
cursor = db.cursor()
try:
cursor.execute(sql)
db.commit()
answer = cursor.fetchall()
if answer:
return render_template('show_staff.html',
nav='library-details',
details=answer)
else:
return render_template('find_staff.html', error='no')
except:
db.rollback()
return render_template('find_staff.html', error='error')
return render_template('find_staff.html')
def create(user_guid=None,
email=None,
password=None,
first_name=None,
last_name=None,
is_system=False,
company=None):
try:
user = Users(user_guid=user_guid,
email=email,
password=password,
first_name=first_name,
last_name=last_name,
is_system=is_system,
company=company)
db.add(user)
db.commit()
return user
except Exception as e:
db.rollback()
raise Exception(e.message)
def add_manager_to_organization(self, org_id, manager_id):
"""
adds a manager to the organization
"""
query_template = """
UPDATE ost_organization SET manager=%s, updated=%s
WHERE id=%s
"""
cur = db.cursor()
cur.execute(query_template, (manager_id, datetime.now(), org_id))
cur.close()
try:
db.commit()
except Exception as exc:
db.rollback()
raise ServiceException(exc.message)
def query_books_by_author():
if request.method == 'POST':
author_name = request.form.get('author_name')
sql = "SELECT * FROM detailofbook WHERE detailofbook.author_name = '%s' ;" % author_name
cursor = db.cursor()
try:
cursor.execute(sql)
db.commit()
answer = cursor.fetchall()
if answer:
return render_template('detailofbooks.html',
nav='book-details',
details=answer)
else:
return render_template('query_books_by_author.html',
error='no')
except:
db.rollback()
return render_template('query_books_by_author.html', error='error')
return render_template('query_books_by_author.html')
def attach_user_to_organization(self, uid, org_id):
"""
attaches a user in the user directory to a particular organization
"""
if not org_service.organization_exists(org_id):
raise ServiceException('No organization exists for given org_id')
query_template = """
UPDATE ost_user SET org_id=%s, updated=%s
WHERE id=%s
"""
cur = db.cursor()
cur.execute(query_template, (org_id, datetime.now(), uid))
cur.close()
try:
db.commit()
except Exception as exc:
db.rollback()
raise ServiceException(exc.message)
def create_apikey(self, ip_address, isactive=1, notes='',
can_create_tickets=0, can_exec_cron=0):
"""
creates the api key
uses the hardcoded values for can_create_tickets, can_exec_cron
as 0
"""
uid = uuid.uuid4()
random_string = str(uid.hex[:16])
random_hex = hashlib.md5(random_string.encode("utf-8")).hexdigest()
api_key = hashlib.md5(
'{}.{}.{}'.format(
time.time(), ip_address, random_hex
).encode('utf-8')).hexdigest().upper()
query_template = """
INSERT INTO ost_api_key (
isactive, ipaddr, apikey, can_create_tickets, can_exec_cron,
notes, updated, created
) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
"""
cur = db.cursor()
cur.execute(query_template, (isactive, ip_address, api_key,
can_create_tickets, can_exec_cron, notes,
datetime.now(), datetime.now()))
cur.close()
try:
db.commit()
return api_key
except Exception as exc:
db.rollback()
raise ServiceException(exc.message)
def create(user_guid=None,
email=None,
password=None,
first_name=None,
last_name=None,
is_system=False,
company=None
):
try:
user = Users(user_guid=user_guid,
email=email,
password=password,
first_name=first_name,
last_name=last_name,
is_system=is_system,
company=company)
db.add(user)
db.commit()
return user
except Exception as e:
db.rollback()
raise Exception(e.message)
def create(account_name=None,
account_guid=None,
account_type=None,
company=None,
is_active=True,
is_deleted=False,
owner=None):
try:
account = Accounts(account_name=account_name,
account_guid=account_guid,
account_type=account_type,
company=company,
is_active=is_active,
is_deleted=is_deleted)
if owner:
account.users.append(owner)
db.add(account)
db.commit()
return account.account_id
except Exception as e:
db.rollback()
raise Exception(e.message)
def create(account_name=None,
account_guid=None,
account_type=None,
company=None,
is_active=True,
is_deleted=False,
owner=None):
try:
account = Accounts(account_name=account_name,
account_guid=account_guid,
account_type=account_type,
company=company,
is_active=is_active,
is_deleted=is_deleted)
if owner:
account.users.append(owner)
db.add(account)
db.commit()
return account.account_id
except Exception as e:
db.rollback()
raise Exception(e.message)
def create(account=None,
nerd_guid=None,
nerd_name=None,
nerd_url=None,
is_deleted=False,
is_active=True,
user=None):
try:
nerd = Nerds(
nerd_url=nerd_url,
nerd_guid=nerd_guid,
nerd_name=nerd_name,
is_deleted=is_deleted,
is_active=is_active,
)
nerd.users.append(user)
account.nerds.append(nerd)
db.add(account)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def create(account=None,
nerd_guid=None,
nerd_name=None,
nerd_url=None,
is_deleted=False,
is_active=True,
user=None):
try:
nerd = Nerds(
nerd_url=nerd_url,
nerd_guid=nerd_guid,
nerd_name=nerd_name,
is_deleted=is_deleted,
is_active=is_active,
)
nerd.users.append(user)
account.nerds.append(nerd)
db.add(account)
db.commit()
except Exception as e:
db.rollback()
raise Exception(e.message)
def own_all():
cursor = db.cursor()
sql = '''SELECT
library.lib_name,lib_address
FROM
library
WHERE
NOT EXISTS (
SELECT * FROM book
WHERE NOT EXISTS
(SELECT * FROM collection
WHERE collection.book_isbn = book.book_isbn
AND collection.lib_id=library.lib_id));'''
try:
cursor.execute(sql)
db.commit()
answers = cursor.fetchall()
if answers:
return render_template('own_all.html', answers=answers)
else:
return render_template('own_all.html', error='no')
except:
db.rollback()
return render_template('own_all.html', error='error')
def on_finish(self):
db.close()
db.rollback()
def rollback():
rollback(request.args.get(db_arg))
return success("reverted")
def save_new_review_form(db):
occ_id = request.forms.occupancy_id
if occ_id is None:
raise HTTPError(400)
occupancy = get_occupancy_to_review(db, occ_id)
last_review = occupancy.review
if 'delete' in request.forms:
review = m.Review(
occupancy=occupancy,
published_at=datetime.now(),
hidden=True
)
else:
sections = []
for key, value in request.forms.iteritems():
match = re.match(r'^section-(\d+)$', key)
if match:
heading_id = int(match.group(1))
try:
heading = db.query(m.ReviewHeading).filter_by(id=heading_id).one()
except NoResultFound:
raise HTTPError(400)
if value.strip():
sections += [
m.ReviewSection(
heading=heading,
content=value
)
]
elif heading.is_summary:
raise HTTPError(400, "{!r} section cannot be left blank".format(heading.name))
# validate the rating
try:
rating = int(request.forms.rating)
except ValueError:
raise HTTPError(404, "Rating must be an integer")
if rating < 0 or rating > 10:
raise HTTPError(404, "Rating must be between 0 and 10")
review = m.Review(
sections=sections,
occupancy=occupancy,
published_at=datetime.now(),
rating=rating
)
# if an edit occurred, record the editor
if occupancy.resident != request.user:
review.editor = request.user
db.add(review)
# check we haven't hit a double-post situation
if last_review and review.contents_eq(last_review):
raise HTTPError(400, "Same as last review")
# TODO: fail gracefully
db.rollback()
flash_some_message()
redirect_anyway()
# look for references in the review
import find_references
for ref in find_references.scan_review(review):
db.add(ref)
redirect('/rooms/{}#review-{}'.format(occupancy.listing.room_id, review.id))
def on_finish(self):
db.close()
db.rollback()
def save_new_review_form(db):
occ_id = request.forms.occupancy_id
if occ_id is None:
raise HTTPError(400)
occupancy = get_occupancy_to_review(db, occ_id)
last_review = occupancy.review
if 'delete' in request.forms:
review = m.Review(
occupancy=occupancy,
published_at=datetime.now(),
hidden=True
)
else:
sections = []
for key, value in request.forms.iteritems():
match = re.match(r'^section-(\d+)$', key)
if match:
heading_id = int(match.group(1))
try:
heading = db.query(m.ReviewHeading).filter_by(id=heading_id).one()
except NoResultFound:
raise HTTPError(400)
if value.strip():
sections += [
m.ReviewSection(
heading=heading,
content=value
)
]
elif heading.is_summary:
raise HTTPError(400, "{!r} section cannot be left blank".format(heading.name))
# validate the rating
try:
rating = int(request.forms.rating)
except ValueError:
raise HTTPError(404, "Rating must be an integer")
if rating < 0 or rating > 10:
raise HTTPError(404, "Rating must be between 0 and 10")
review = m.Review(
sections=sections,
occupancy=occupancy,
published_at=datetime.now(),
rating=rating
)
# if an edit occurred, record the editor
if occupancy.resident != request.user:
review.editor = request.user
db.add(review)
# check we haven't hit a double-post situation
if last_review and review.contents_eq(last_review):
raise HTTPError(400, "Same as last review")
# TODO: fail gracefully
db.rollback()
flash_some_message()
redirect_anyway()
# look for references in the review
import find_references
for ref in find_references.scan_review(review):
db.add(ref)
redirect('/rooms/{}#review-{}'.format(occupancy.listing.room_id, review.id))
