def reg_host():
req_json = MultiDict(request.get_json())
already_host = db_session.query(SessionHost).filter(SessionHost.roundtable_id == req_json.get('roundtable_num'),
SessionHost.library_id==req_json.get('library_id'),
SessionHost.session_time==req_json.get('session_time'))
if already_host.count() == 2:
return jsonify(success=False, msg='신청하신 도서관은 진행자 모집이 완료되었습니다.')
session_host_obj = SessionHost()
session_host_obj.roundtable_id = req_json.get('roundtable_num')
session_host_obj.library_id = req_json.get('library_id')
session_host_obj.session_time = 1 if already_host.count() == 0 else 2
session_host_obj.host_user_id = current_user.id
session_host_obj.host_name = current_user.name
session_host_obj.host_belong = req_json.get('organization')
session_host_obj.host_hp = current_user.phone or req_json.get('phone')
session_host_obj.host_email = current_user.email or req_json.get('email')
session_host_obj.host_use_yn = True
db_session.add(session_host_obj)
user_record = db_session.query(User).filter(User.id == current_user.id).first()
if not current_user.phone and req_json.get('phone'):
user_record.phone = req_json.get('phone')
if not current_user.email and req_json.get('email'):
user_record.email = req_json.get('email')
return jsonify(success=True)
def main():
# 최근건 몇 개를 표시하는 것은 항목이 모자를 경우 빈 항목으로 채우고 템플릿에서 처리하게 한다.
# 최근 언론보도 3건
latest_news = list(db_session.query(News).order_by(desc(News.id)).limit(3))
if len(latest_news) < 3:
latest_news = latest_news + [None for _ in range(3 - len(latest_news))]
# 최근 사진첩 3건
latest_photo = list(db_session.query(PhotoAlbum).order_by(desc(PhotoAlbum.id)).limit(3))
if len(latest_photo) < 3:
latest_photo = latest_photo + [None for _ in range(3 - len(latest_photo))]
# 개최일을 카운트다운 하기 위한 추가 작업(메인으로 하고 있는 개최회차 정보를 가져옴)
main_roundtable_record = db_session.query(Roundtable).filter(Roundtable.is_active == True).first()
round_open_date = main_roundtable_record.roundtable_date.strftime("%Y/%m/%d")
# 메인 이미지 내려주기
slide_carousel = SlideCarousel.query.order_by(asc(SlideCarousel.id))
# 도서관 수 반환
open_library_cnt = db_session.query(func.count(RoundtableAndLibrary.roundtable_id)).filter(
RoundtableAndLibrary.roundtable_id == main_roundtable_record.id).first()
return render_template(
"public/index.html",
latest_news=latest_news,
latest_photo=latest_photo,
round_open_date=round_open_date,
slide_carousel=slide_carousel,
open_library_cnt=open_library_cnt[0])
def get(self):
# 메인 강연 회차를 가져온다.
main_roundtable = db_session.query(Roundtable).filter(
Roundtable.is_active == True).first()
area_opened_library = db_session.query(func.distinct(
Library.area)).join(RoundtableAndLibrary).filter(
RoundtableAndLibrary.roundtable_id == main_roundtable.id)
opened_area = [entry[0] for entry in area_opened_library]
# 강연이 열리는 도서관 목록을 가져온다.
opened_library = RoundtableAndLibrary.query.filter(
RoundtableAndLibrary.roundtable == main_roundtable)
records = []
for library_match in opened_library:
# 도서관별 토탈 강연 회차를 가져와서 도서관에 매치된 강연 회차가 몇개 존재하는지 가지고 온다.
records.append(
dict(area=library_match.library.area,
library_id=library_match.library.id,
library_name=library_match.library.library_name,
round_num=library_match.round_num,
lecture_cnt=len(library_match.library.lecture),
host_cnt=len(library_match.library.host)))
return render_template("admin/dashboard.html",
opened_area=opened_area,
opened_library=records)
def delete(self, host):
db_session.query(OTandParty).filter(
OTandParty.party_user_id == host.host_user_id,
OTandParty.roundtable_id == host.roundtable_id).delete()
db_session.delete(host)
return jsonify(success=True)
def get(self):
# 최근 회차 정보 가져오기
main_roundtable = db_session.query(Roundtable).filter(
Roundtable.is_active == True).first()
area_opened_library = db_session.query(func.distinct(
Library.area)).join(RoundtableAndLibrary).filter(
RoundtableAndLibrary.roundtable_id == main_roundtable.id)
return render_template("admin/mail_send.html",
area_opened_library=area_opened_library)
def delete(self, round):
# 참여 도서관 정보 삭제
for entry in round.library:
db_session.delete(entry)
db_session.query(Lecture).filter(
Lecture.roundtable_id == round.id).delete()
db_session.query(SessionHost).filter(
SessionHost.roundtable_id == round.id).delete()
db_session.delete(round)
return jsonify(success=True)
def get(self):
current_page = request.args.get("page", 1, type=int)
search_option = request.args.get("search_option", '')
search_word = request.args.get("search_word", '')
if search_option and search_option in ['name', 'email', 'phone']:
search_column = getattr(User, search_option)
page_url = url_for("admin.member_list")
if search_word:
page_url = url_for("admin.member_list",
search_option=search_option,
search_word=search_word)
page_url = str(page_url) + "&page=$page"
else:
page_url = str(page_url) + "?page=$page"
items_per_page = 10
records = db_session.query(User)
if search_word:
if search_option == "phone":
records = records.filter(
User.phone_search.ilike('%{}%'.format(
search_word.replace("-", ""))))
elif search_option == "username":
# 다른데서 먼저 찾아야 한다.
social_auth_records = db_session.query(
UserSocialAuth.user_id).filter(
UserSocialAuth.provider == 'username',
UserSocialAuth.uid.ilike('%{}%'.format(search_word)))
records = records.filter(User.id.in_(social_auth_records))
else:
records = records.filter(
search_column.ilike('%{}%'.format(search_word)))
records = records.order_by(desc(User.id))
total_cnt = records.count()
paginator = paginate.Page(records,
current_page,
page_url=page_url,
items_per_page=items_per_page,
wrapper_class=SqlalchemyOrmWrapper)
return render_template("admin/member.html",
paginator=paginator,
paginate_link_tag=paginate_link_tag,
page_url=page_url,
items_per_page=items_per_page,
total_cnt=total_cnt,
page=current_page)
def myinfo():
user = db_session.query(User.username, User.name, User.email, User.phone,
UserSocialAuth.provider, User.last_login_date, User.usertype, UserSocialAuth, User).join(
UserSocialAuth, User.id == UserSocialAuth.user_id).filter(
User.id == current_user.id, UserSocialAuth.provider == 'username').first()
# 아래 정보를 추가하는건 도서문화재단 씨앗의 추가 요청사항이 있어서임
# 가장 최근의 회차 정보를 가져온다.
main_roundtable = db_session.query(Roundtable).filter(Roundtable.is_active == True).first()
# 강연자가 최근 회차로 신청한 강연 ID를 가져온다.
lecture_rec = Lecture.query.filter(Lecture.roundtable_id == main_roundtable.id,
Lecture.lecture_user_id == current_user.id).order_by(desc(Lecture.id)).first()
# 추천도서 정보가 있는지 확인한다.
is_vote_btn = False
is_vote_record = False
if lecture_rec:
vote_record = VoteBooks.query.filter(VoteBooks.roundtable_id == main_roundtable.id,
VoteBooks.lecture_id == lecture_rec.id).first()
if vote_record:
is_vote_record = True
# Case 1) 강연 신청를 아예 안한 경우
# Case 2) 강연 신청은 했지만 추천도서 정보를 입력 안한 경우
# Case 3) 강연 신청도 하고 추천도서 정보도 입력한 경우
if lecture_rec and not is_vote_record:
# Case 3
is_vote_btn = True
# 해당 유저가 도서관 사서인지 확인한다. 확인 방법은 이메일로 한다(전화번호는 휴대폰 번호로 입력 안할수도 있기 때문)
is_librarian = False
librarian_office = None
library_record = Library.query.filter(Library.manager_email == user[2]).first()
if library_record:
librarian_office = library_record
# 물론 이 때 이미 해당 유저가 사서로 등록되어 있으면 경고창을 다시 보여주면 안된다
# (하하... 도대체 누구 좋으라고 이 코드를 만드는가..)
is_librarian = True
if library_record.manager_id == current_user.id:
is_librarian = False
return render_template("public/user/myinfo.html", user=user, is_vote_btn=is_vote_btn,
is_librarian=is_librarian, librarian_office=librarian_office)
def reset_complete_password(uuid):
# 해당된 UUID에 일치하는 아이디 찾기
uuid_object = db_session.query(FindPasswordToken).filter(FindPasswordToken.uuid == str(uuid)).first()
reset_complete_user = db_session.query(User).filter(User.id == uuid_object.find_user_id).first()
# 해당된 패스워드를 SHA256 방식으로 변경
req_json = request.get_json()
reset_password = req_json.get('reset_password')
# 해당된 정보를 통해 USERID 변경
reset_complete_user.set_password(reset_password)
db_session.add(reset_complete_user)
return jsonify(success=True)
def delete(self, lecturer):
vote_books = db_session.query(VoteBooks).filter(
VoteBooks.roundtable_id == lecturer.roundtable_id,
VoteBooks.lecture_user_id == lecturer.lecture_user_id).first()
if vote_books:
db_session.delete(vote_books)
db_session.query(OTandParty).filter(
OTandParty.party_user_id == lecturer.lecture_user_id,
OTandParty.roundtable_id == lecturer.roundtable_id).delete()
db_session.delete(lecturer)
return jsonify(success=True)
def get(self):
# 회차 정보만 모아오기(유효성 검증용)
roundtable = map(lambda x: x[0],
db_session.query(Roundtable.roundtable_num))
return render_template("admin/goods_donation_reg.html",
roundtable=roundtable)
def get(self):
current_page = request.args.get("page", 1, type=int)
search_option = request.args.get("search_option", '')
search_word = request.args.get("search_word", '')
if search_option and search_option in ['press_date', 'category', 'news_title', 'news_press']:
search_column = getattr(News, search_option)
page_url = url_for("admin.news")
if search_word:
page_url = url_for("admin.news", search_option=search_option, search_word=search_word)
page_url = str(page_url) + "&page=$page"
else:
page_url = str(page_url) + "?page=$page"
items_per_page = 10
records = db_session.query(News)
if search_word:
records = records.filter(search_column.ilike('%{}%'.format(search_word)))
records = records.order_by(desc(News.id))
total_cnt = records.count()
paginator = paginate.Page(records, current_page, page_url=page_url,
items_per_page=items_per_page,
wrapper_class=SqlalchemyOrmWrapper)
return render_template("admin/news.html", paginator=paginator,
paginate_link_tag=paginate_link_tag,
page_url=page_url, items_per_page=items_per_page,
total_cnt=total_cnt, page=current_page)
def to_python(self, value):
if not value.isdecimal():
raise ValueError('잘못된 요청입니다.')
record = db_session.query(SessionHost).filter(
SessionHost.id == value).first()
return record
def session_host(self):
host_record = db_session.query(SessionHost).filter(
SessionHost.roundtable_id == self.roundtable_id,
SessionHost.library_id == self.library_id,
SessionHost.session_time == self.session_time).first()
return host_record
def get(self):
records = db_session.query(OTandParty).order_by(desc(OTandParty.id))
wb = Workbook()
ws = wb.active
ws['A1'] = '#'
ws['B1'] = '도서관'
ws['C1'] = '신청자'
ws['D1'] = '1차 OT'
ws['E1'] = '2차 OT'
ws['F1'] = '뒤풀이'
for index, entry in enumerate(records, 2):
ws["A{}".format(index)] = index - 1
ws["B{}".format(index)] = library_find(entry.party_user, entry.roundtable)
ws["C{}".format(index)] = entry.party_user.name
ws["D{}".format(index)] = entry.ot1_join and '참석' or '불참'
ws["E{}".format(index)] = entry.ot2_join and '참석' or '불참'
ws["F{}".format(index)] = entry.party_join and '참석' or '불참'
resultIO = io.BytesIO()
wb.save(resultIO)
resultIO.seek(0)
file_name = '10월의 하늘_OT및뒤풀이참석자_{}.xlsx'.format(datetime.datetime.now().strftime("%Y-%m-%d"))
return send_file(resultIO, mimetype="application/zip", as_attachment=True, attachment_filename=file_name)
def add_votebook():
req_json = request.get_json()
# 가장 최근의 회차 정보를 가져온다.
main_roundtable = db_session.query(Roundtable).filter(Roundtable.is_active == True).first()
# 강연자가 최근 회차로 신청한 강연 ID를 가져온다.
lecture_rec = Lecture.query.filter(Lecture.roundtable_id == main_roundtable.id,
Lecture.lecture_user_id == current_user.id).order_by(desc(Lecture.id)).first()
# 추천 도서 정보 기록
vote_book = VoteBooks()
vote_book.roundtable_id = main_roundtable.id
vote_book.lecture_id = lecture_rec.id
vote_book.lecture_user_id = current_user.id
vote_book.book_info = {
'book1': req_json.get('book1'),
'book1_desc': req_json.get('book1_desc'),
'book2': req_json.get('book2'),
'book3': req_json.get('book3'),
'etc': req_json.get('etc')
}
vote_books.enter_path = req_json.get("enter_path")
db_session.add(vote_book)
db_session.flush()
return jsonify(success=True)
def get(self):
current_page = request.args.get("page", 1, type=int)
search_option = request.args.get("search_option", '')
search_word = request.args.get("search_word", '')
area = request.args.get("area", 'all')
if search_option and search_option in ['library_name', 'library_addr']:
search_column = getattr(Library, search_option)
page_url = url_for("admin.library_list")
if search_word:
page_url = url_for("admin.library_list", search_option=search_option, search_word=search_word)
page_url = str(page_url) + "&page=$page"
else:
page_url = str(page_url) + "?page=$page"
items_per_page = 10
records = db_session.query(Library)
if search_word:
records = records.filter(search_column.ilike('%{}%'.format(search_word)))
if area != "all":
records = records.filter(Library.area == area)
records = records.order_by(desc(Library.id))
total_cnt = records.count()
paginator = paginate.Page(records, current_page, page_url=page_url,
items_per_page=items_per_page,
wrapper_class=SqlalchemyOrmWrapper)
return render_template("admin/library.html", paginator=paginator,
paginate_link_tag=paginate_link_tag,
page_url=page_url, items_per_page=items_per_page,
total_cnt=total_cnt, page=current_page)
def post(self):
req_json = MultiDict(request.get_json())
user_record = db_session.query(User).outerjoin(UserSocialAuth).filter(
UserSocialAuth.uid == req_json.get("lectureID")).first()
lecturer_obj = Lecture()
lecturer_obj.roundtable_id = req_json.get('roundtable_id')
lecturer_obj.library_id = req_json.get('library').get('id')
lecturer_obj.session_time = req_json.get('lectureTime')
lecturer_obj.lecture_title = req_json.get('lectureTitle')
lecturer_obj.lecture_summary = req_json.get('lectureSummary')
lecturer_obj.lecture_expected_audience = req_json.get(
'lectureExpectedAudience')
lecturer_obj.lecture_user_id = (user_record and user_record.id) or None
lecturer_obj.lecture_name = req_json.get('lectureName')
lecturer_obj.lecture_belong = req_json.get('lectureBelong')
lecturer_obj.lecture_hp = req_json.get('lectureHp')
lecturer_obj.lecture_email = req_json.get('lectureEmail')
lecturer_obj.lecture_public_yn = req_json.get('lectureUserYn',
type=bool)
db_session.add(lecturer_obj)
return jsonify(success=True)
def ot_and_party_status():
req_json = request.get_json()
main_roundtable = db_session.query(Roundtable).filter(Roundtable.is_active == True).first()
# 현재 유저로 등록된 참석 여부 레코드가 있는지 확인한다.
ot_party_record = db_session.query(OTandParty).filter(OTandParty.party_user_id == current_user.id,
OTandParty.roundtable_id == main_roundtable.id).first()
if not ot_party_record:
return jsonify(success=True, ot1_join=False, ot2_join=False, party_join=False)
else:
return jsonify(
success=True,
ot1_join=bool(ot_party_record.ot1_join),
ot2_join=bool(ot_party_record.ot2_join),
party_join=bool(ot_party_record.party_join))
def delete(self, uid):
social_auth_record = db_session.query(UserSocialAuth).filter(
UserSocialAuth.user_id == uid.id).first()
db_session.delete(social_auth_record)
db_session.delete(uid)
return jsonify(success=True)
def post(self, round):
req_json = request.get_json()
round.roundtable_num = req_json.get('roundtableNum')
round.roundtable_year = req_json.get('roundtableYear')
round.roundtable_date = req_json.get('roundtableDate')
round.staff = req_json.get('staff')
worked_lib = []
for entry in round.library:
put_library = tuple(
filter(
lambda sub_entry: entry.library_id == sub_entry.get(
'library').get('id'), req_json.get('library_list')))
if len(put_library) > 0:
worked_lib.append(entry.library_id)
entry.round_num = put_library[0]['session_time']
entry.library_type = put_library[0]['library_type']
else:
# 사용자가 보낸 도서관을 목록에서 찾지 못하면 삭제 대상으로 결정한다.
db_session.delete(entry)
for item in req_json.get('library_list'):
if item.get('library').get('id') in worked_lib:
continue
lib_info = RoundtableAndLibrary(
round_num=item.get('session_time'),
library_type=item.get('library_type'))
lib_info.library = db_session.query(Library).filter(
Library.id == item.get('library').get('id')).first()
round.library.append(lib_info)
return jsonify(success=True)
def find_match_uuid():
# 해당되는 비밀번호 UUID에 있는 것을 확인
find_uuid = request.args.get('uuid')
# 만료일보다 기간이 지나면 해당 인증번호는 사용을 못하게 막아놔야함
# 첫 비밀번호 확인
find_match_uuid_object = db_session.query(FindPasswordToken).filter(
FindPasswordToken.find_expired_date >= datetime.datetime.now()).\
order_by(FindPasswordToken.id.desc()).first()
find_match_password_uuid = find_match_uuid_object.uuid
if find_password is None:
# 인증번호가 없는 경우
return redirect('/')
else:
# 인증번호가 존재하고 일치할 경우
# 해당 인증번호는 1회용으로 사용 예정
if find_match_password_uuid == find_uuid and\
find_match_uuid_object.find_email_use_yn is False:
find_match_uuid_object.find_email_use_yn = True
return render_template("public/user/reset_password.html", uuid=find_uuid)
else:
# 인증번호가 존재하나 잘못 입력할 경우
return abort(404)
def find_match_password():
req_json = request.get_json()
find_username = req_json.get('find_username')
find_email = req_json.get('find_email')
find_name = req_json.get('find_name')
find_count = db_session.query(User).filter(User.username == find_username,
User.email == find_email,
User.name == find_name).count()
if find_count == 1:
# 아이디, 이메일, 이름 동일한 경우
# 유효기간 : 10분
uuid_string = uuid.uuid4().__str__()
expire_date = datetime.datetime.now()
expire_date_timedelta = datetime.timedelta(seconds=600)
expire_date += expire_date_timedelta
find_object = db_session.query(User).filter(
User.username == find_username,
User.email == find_email,
User.name == find_name)
# 해당되는 uuid Table Insert
find_password_obj = FindPasswordToken()
find_password_obj.find_user_id = find_object.first().id
find_password_obj.find_user_email = find_email
find_password_obj.uuid = uuid_string
find_password_obj.find_expired_date = expire_date
db_session.add(find_password_obj)
# 해당되는 이름 가져오기
user_object = db_session.query(User).filter(User.username == find_username,
User.email == find_email,
User.name == find_name).first()
# 해당되는 Email 파일을 오픈 후 메일 주소의 이름과 링크만 수정한다.
# 해당 HTML 파일을 읽어온다
content = render_template("public/user/mail_password.html",
user_real_name=user_object.name,
uuid=uuid_string)
# Amazon SES Mail Send
ses = SESMail("10월의 하늘 비밀번호 초기화 인증", content, [])
ses.send({"name": find_name, "addr": find_email})
return jsonify(success=True)
else:
# 아닌 경우
return jsonify(success=False)
def get(self, lecturer):
vote_books = db_session.query(VoteBooks).filter(
VoteBooks.roundtable_id == lecturer.roundtable_id,
VoteBooks.lecture_user_id == lecturer.lecture_user_id).first()
return render_template("admin/lecturer_view.html",
lecturer=lecturer,
vote_books=vote_books)
def get(self):
carousel = {}
records = db_session.query(SlideCarousel)
for entry in records:
carousel[entry.id] = entry
return render_template("admin/main_view.html", carousel=carousel)
def to_python(self, value):
record = db_session.query(
User.username, User.name, User.email, User.phone,
User.last_login_date, User.usertype, Library.id,
Library.library_name,
User).outerjoin(Library).filter(User.username == value).first()
return record
def get(self, book):
# 회차 정보만 모아오기(유효성 검증용)
roundtable = map(lambda x: x[0],
db_session.query(Roundtable.roundtable_num))
return render_template("admin/books_edit.html",
book=book,
roundtable=roundtable)
def post(self):
req_json = request.get_json()
page_record = db_session.query(PageTemplate).filter(
PageTemplate.page_name == 'public/boost.html').first()
page_record.page_content = req_json.get('page_content')
return jsonify(success=True)
def get(self):
page_record = db_session.query(PageTemplate).filter(
PageTemplate.page_name == 'public/boost.html').first()
return render_template('public/boost.html',
donate_body=Markup(
render_template_string(
page_record.page_content)))
def post(self):
req_json = request.get_json()
record = db_session.query(DonationGoods).filter(
DonationGoods.id == int(req_json.get('donation_id'))).first()
record.is_received = req_json.get('checked')
return jsonify(success=True)