def sale_report(sale_id):
sale_devices = query_db(
"select sd.sale_device_id from tbl_sale_device sd join tbl_device d on d.device_id = sd.device_id join tbl_type t on t.type_id = d.type_id where sale_id=%s order by t.type_name, d.device_name"
% sale_id)
sale_name = get_sale_name_by_sale_id(sale_id)
sale_reports = []
for sale_device in sale_devices:
sale_device_id = sale_device[0]
device_id = query_db(
"select device_id from tbl_sale_device where sale_device_id=%s" %
sale_device_id)[0][0]
device_details = get_extended_device_details_by_sale_device_id(
sale_device_id)
user_emails = query_db(
"select u.user_email from tbl_user u join tbl_user_sale_device usd on u.user_id = usd.user_id where usd.won = 1 and usd.sale_device_id = %s order by u.user_email"
% sale_device_id)
user_names = []
for user_email in user_emails:
user_names.append(' '.join(
map(str.capitalize, user_email[0].split('@')[0].split('.'))))
sale_reports.append((device_details, user_names))
return render_template('sale_report.html',
sale_reports=sale_reports,
sale_name=sale_name,
projecthash=get_hash_of_project())
def sessionLogin():
if request.method == "POST":
email = request.form['email']
password = request.form['password']
user = query_db('select * from users where email=\"'+email+'\"', one=True)
if user is None:
return 'No such email'
else:
if user['password'] == password:
print user['username'],user['role'],' passed login authentication.'
session['email'] = user['email']
session['username'] = user['username']
session['role'] = user['role']
session['password'] = user['password']
session['profile_pic'] = user['profile_pic']
######!!!!!! 아이 테이블에서 사진 정보를 가져와 세션정보에 굽고 JSON리턴에 더해주기
if session['role'] == 'parent':
childInfo = query_db('select * from children where parent_email=\"'+email+'\"', one=True)
if childInfo is None:
return 'No such child'
else:
session['child_pic'] = childInfo['profile_pic']
session['child_name'] = childInfo['name']
return jsonify(username=session['username'],email=session['email'],role=session['role'],password=session['password'],profile_pic=session['profile_pic'],childProfile_pic=session['child_pic'],child_name=session['child_name'])
else :
return jsonify(username=session['username'],email=session['email'],role=session['role'],password=session['password'],profile_pic=session['profile_pic'])
else:
return "Password is incorrect."
def admin_page():
if not session['logged_in']:
return redirect(url_for('login'))
sale_details = query_db(
"select sale_id, sale_name, sale_date from tbl_sale")
admin_details = query_db(
"select admin_id, admin_name, admin_email from tbl_admin")
return render_template('admin_page.html',
sale_details=sale_details,
admin_details=admin_details,
projecthash=get_hash_of_project())
def getMyPartners(role, email):
lResult = [] # 친구들의 정보를 담아 return할 결과 배열
friendInfo = {} # 친구 한 명, 한 명의 정보를 담을 친구정보 딕셔너리
#DB에 쿼리요청을 보내서 레코드를 받아온다.
for friendInfo in query_db(getMyFriendsQueryByRole(role,email)):
# DB 요청에서 받아온 레코드를 하나씩(user) 순회하면서 JSON을 만들기 위한 작업을 수행한다.
# 친구정보 딕셔너리에 현재 친구인지 여부도 기록
friendInfo["currentFriend"] = isCurrentPartner(friendInfo["startDate"], friendInfo["endDate"], get10numberTimestamp())
if role == 'classTeacher' :
# 상대가 엄마인 경우 아이의 이름과 아이ID, 프로필 사진주소 를 가져온다.
childInfo = query_db('select name,child_id,profile_pic from children where parent_email=\''+friendInfo['email']+'\'')
if childInfo is None:
print 'No child'
else: #부모 당 아이수가 1명일 경우를 가정하여서 이렇게 만들었는데 아니라면? 고쳐야 한다 ㅠ
friendInfo["childname"] = childInfo[0]['name']
friendInfo["child_id"] = childInfo[0]['child_id']
friendInfo["childprofile_pic"] = childInfo[0]['profile_pic']
# 아이의 위키정보도 가져온다
childWiki = query_db('select id,title,content from child_description where child_id='+str(friendInfo["child_id"]))
if childWiki is None:
print 'No such child profile'
else:
friendInfo["childDescription"] = []
for singleWikiSection in childWiki:
friendInfo["childDescription"].append(singleWikiSection)
elif role == 'parent':
# 내 아이의 위키정보도 가져온다
child_id_list = query_db('select * from children where parent_email=\''+email+'\'')
childWiki = query_db('select id,title,content from child_description where child_id='+str(child_id_list[0]['child_id']))
friendInfo["child_id"]=child_id_list[0]['child_id']
friendInfo["childprofile_pic"]=child_id_list[0]['profile_pic']
friendInfo["childname"]=child_id_list[0]['name']
if childWiki is None:
friendInfo["childDescription"] = []
print 'No such child profile'
else:
friendInfo["childDescription"] = []
for singleWikiSection in childWiki:
friendInfo["childDescription"].append(singleWikiSection)
# 친구정보를 하나씩 리스트에 삽입하기
lResult.append(friendInfo)
return json.dumps(lResult)
def show_sale(sale_id):
sale_name = get_sale_name_by_sale_id(sale_id)
sale_date = query_db("select sale_date from tbl_sale where sale_id=%s" %
sale_id)[0][0]
sale_details = query_db(
'select sd.device_id, sd.quantity, d.device_name, d.device_description, t.type_name, count(user_sale_device_id), d.price from tbl_sale_device sd join tbl_device d on sd.device_id=d.device_id join tbl_type t on d.type_id=t.type_id left join tbl_user_sale_device usd on usd.sale_device_id=sd.sale_device_id where sd.sale_id=%s group by device_id order by t.type_name, d.device_name'
% sale_id)
return render_template('show_sale_details.html',
sale_id=sale_id,
sale_name=sale_name,
sale_details=sale_details,
sale_date=sale_date,
projecthash=get_hash_of_project())
def show_active_sales():
sales = query_db(
'select sale_id, sale_name, sale_date, active from tbl_sale where active=1'
)
if len(sales) == 1:
return redirect(url_for('show_sale', sale_id=sales[0][0]))
return render_template('show_active_sales.html',
sales=sales,
projecthash=get_hash_of_project())
def ajax_publishAnnouncementNote() : if (request.method == 'POST') : # 'method' : 'publish' 또는 'delete' method = request.form['method'] if ( method== 'publish') : ### id 는 자동적으로 입력해줌 publisher_email = request.form['publisher_email'] published_date = request.form['published_date'] content = request.form['content'] numOfReceivers = (len(request.form)-5) / 2 # 우선 공통공지사항들을 보냅니다 sQuery = 'INSERT INTO common_announcement (publisher_email,published_date,content) ' sQuery = sQuery + 'VALUES (\'' + publisher_email+ '\',\'' + published_date + '\',\'' + content+'\')' g.db.execute(sQuery) g.db.commit() sQuery = 'SELECT id FROM common_announcement where publisher_email = \'' sQuery = sQuery + publisher_email + '\' AND published_date = \'' + published_date+'\'' common_announcement_id = query_db(sQuery) # 각각의 개인 공지는 individual_announcement KEY 안의 딕셔너리들! # 따라서 individual_announcement를 하나하나 업데이트 해줍니다. # 업데이트 사항은 content와 publisher_email, common_announcement_id sQuery = "" for i in range(numOfReceivers): sQuery = 'INSERT INTO individual_announcement ' sQuery = sQuery + '(content,receiver_email,common_announcement_id) VALUES (\'' sQuery = sQuery + request.form['individual_announcement['+str(i)+'][content]'] + '\',\'' sQuery = sQuery + request.form['individual_announcement['+str(i)+'][receiver_email]'] + '\',' sQuery = sQuery + str(common_announcement_id[0]['id'])+')' g.db.execute(sQuery) g.db.commit() return json.dumps(["알림장 발행 완료"]) # 해당 아디를 갖는 모든 공통공지 개별공지 지워뿌림. elif (method == 'delete') : # 지울놈 아이디 기억해 둡니다 target_id = request.form['id'] # 우선 individual_announcement 테이블에서 # common_announcement_id == target_id 인 놈들을 지워요 sQuery = 'DELETE FROM individual_announcement WHERE common_announcement_id = ' + target_id g.db.execute(sQuery) g.db.commit() # 그 뒤에 common_annoucement 테이블에서 # id = target_id 인 놈들을 지워요 sQuery = 'DELETE FROM common_announcement WHERE id =' + target_id g.db.execute(sQuery) g.db.commit() return json.dumps(["알림장 삭제 완료"])
def run_sale(sale_id):
try:
debug("Running sale %s, %s" %
(get_sale_name_by_sale_id(sale_id), sale_id))
devices = query_db(
"select distinct device_id from view_entrants where sale_id=%s" %
sale_id)
for device in devices:
device_id = device[0]
debug(" Selecting buyers for device %s" % device_id)
entrants = list(
query_db(
"select user_id from tbl_user_sale_device usd join tbl_sale_device sd on sd.sale_device_id=usd.sale_device_id where sd.sale_id=%s and device_id=%s order by user_id"
% (sale_id, device_id)))
(sale_device_id, quantity) = query_db(
"select sale_device_id, quantity from tbl_sale_device where sale_id=%s and device_id=%s"
% (sale_id, device_id))[0]
debug(" %s of these devices on hand" % quantity)
debug(" %s buyers want one" % len(entrants))
chosen_buyers = []
# Check if there are more names in the bucket than devices on hand
if len(entrants) > quantity:
debug(" We need to go to a random selection process")
# Loop until we've selected buyers for all of the available items or we've exhausted all of the entrants (which should never happen, but whatever)
while len(chosen_buyers) < quantity and len(entrants) > 0:
# Choose a random integer from 0 to the highest index in the list
chosen_buyer_index = randint(0, len(entrants) - 1)
# Pop the random buyer out of 'entrants' and append him to the 'chosen_buyers' list
chosen_buyers.append(entrants.pop(chosen_buyer_index))
# Give the unselected buyers the bad news
notify_buyers(False, entrants, sale_id, device_id,
sale_device_id)
else:
# Don't bother picking names out of the hat if there are more (or the same number of) devices on hand than names
debug(
" We don't need to go to a random selection process")
chosen_buyers = entrants
# Give the selected entrants the good news
notify_buyers(True, chosen_buyers, sale_id, device_id,
sale_device_id)
deactivate_sale(sale_id)
except Exception as error:
debug(traceback.format_exc())
def refresh(self):
"""Refresh the list of status items in this model."""
data = common.query_db()
self.clear()
self.setHorizontalHeaderLabels(['Name', 'Status'])
for name, items in sorted(data.items()):
self._create_top_item(name, items, self)
def refresh(self):
"""Refresh the list of status items."""
data = common.query_db()
self.clear()
for name, items in sorted(data.items()):
self._create_top_item(name, items, self)
self.expandAll()
def getNoteConfirmationCheck(email):
#common_announcement 에서 published_date를 내림차순으로 정렬한 상태로 받아와 finder 리스트로 만든다
finder=query_db('SELECT published_date FROM common_announcement WHERE publisher_email=\''+email+'\' ORDER BY published_date desc')
keyDate=finder[0]['published_date']
#내림차순으로 정렬했으므로 finder[0]이 가장 최근 날짜임. 날짜 값만 keyDate로 받는다.
dResult={}
#DB에 쿼리요청을 보내서 레코드를 받아온다.
for mailData in query_db('select id from common_announcement where published_date=\'' + keyDate +'\' and publisher_email=\''+email+'\''):
receiver = []
dResult["published_date"]=keyDate
for receiverData in query_db('select email, username, confirmed_date, response_message_date from users, individual_announcement where users.email=individual_announcement.receiver_email and individual_announcement.common_announcement_id=' + str(mailData['id'])) :
parents={}
parents["email"]=receiverData['email']
parents["username"]=receiverData['username']
parents["confirmed_date"]=receiverData['confirmed_date']
parents["response_message_date"]=receiverData['response_message_date']
receiver.append(parents)
dResult["receiver"]=receiver
return json.dumps(dResult)
def ajax_getUserProfilePic(): if request.method == 'POST' : email = request.form["email"] sQuery = 'SELECT email,username,profile_pic,role FROM users ' sQuery = sQuery + 'WHERE email =\'' +email+'\'' userInfo = query_db(sQuery) userInfo[0]['a'] = request.form["a"] userInfo[0]['j'] = request.form["j"] if(userInfo): return json.dumps(userInfo[0]) else : return json.dumps(["there is no person"])
def show_bucket(sale_id, device_id):
bucket_members = query_db(
"select u.user_id, u.user_email from tbl_user u join tbl_user_sale_device usd on usd.user_id = u.user_id join tbl_sale_device sd on sd.sale_device_id = usd.sale_device_id where sd.sale_id = %s and sd.device_id = %s order by u.user_email"
% (sale_id, device_id))
sale_name = get_sale_name_by_sale_id(sale_id)
device_name = get_device_name_by_device_id(device_id)
return render_template('show_bucket.html',
sale_id=sale_id,
device_name=device_name,
sale_name=sale_name,
bucket_members=bucket_members,
projecthash=get_hash_of_project())
def refresh(self):
"""Refresh the list of status items in this model."""
data = common.query_db('data2.json')
self.clear()
# In a Model/View setup, the model is responsible for the header labels.
self.setHorizontalHeaderLabels(['Name', 'Status'])
# Use the operator module to make callables that behave like operators
# For example, operator.itemgetter('foo')(obj) is the same as obj.foo
for each in sorted(data, key=operator.itemgetter('name')):
self._create_item_a(each, self)
def confirm_email():
user_email = request.form['user_email']
device_id = request.form['device_id']
sale_id = request.form['sale_id']
if not user_email.endswith("@flexential.com"):
flash("You must use your @flexential.com email address.", "error")
return redirect(url_for('show_sale', sale_id=sale_id))
user_id = get_user_id_by_user_email(user_email)
user_already_in_bucket = query_db(
"select count(usd.user_id) from tbl_user_sale_device usd join tbl_sale_device sd on usd.sale_device_id=sd.sale_device_id where sd.sale_id=%s and sd.device_id=%s and usd.user_id=%s"
% (sale_id, device_id, user_id))[0][0]
if user_already_in_bucket > 0:
device_name = get_device_name_by_device_id(device_id)
sale_name = get_sale_name_by_sale_id(sale_id)
flash(
"There is already an entry for you under the bucket for a %s in %s"
% (device_name, sale_name), "error")
return redirect(url_for('show_sale', sale_id=sale_id))
(sale_name, sale_date) = get_sale_details_by_sale_id(sale_id)
(device_name, device_description,
price) = get_device_details_by_device_id(device_id)
uuid = generate_uuid()
baseuri = get_base_uri()
company_name = get_company_name()
send_email(
render_template('confirm_email.html',
sale_name=sale_name,
sale_date=sale_date,
device_name=device_name,
device_description=device_description,
price=price,
sale_id=sale_id,
uuid=uuid,
baseuri=baseuri,
company_name=company_name), user_email,
'Email Confirmation')
print "device_id = %s" % device_id
print "sale_id = %s" % sale_id
sale_device_id = get_sale_device_id(device_id, sale_id)
query = "insert into tbl_user_uuid(user_id, uuid, sale_device_id) values(%s, '%s', %s)" % (
user_id, uuid, sale_device_id)
insert_to_db(query)
flash(
"Please check your email for a verification link to add your name to the bucket for the %s"
% device_name)
return redirect(url_for('show_sale', sale_id=sale_id))
def confirm_bucket(sale_id, uuid):
query = "select count(*), user_id, sale_device_id from tbl_user_uuid where uuid='%s'" % uuid
(uuid_count, user_id, sale_device_id) = query_db(query)[0]
if uuid_count != 1:
flash("Sorry, this link UUID is not found. Please try again.",
"error")
return redirect(url_for('show_active_sales'))
(device_name, sale_name
) = get_device_name_and_sale_name_by_sale_device_id(sale_device_id)
try:
insert_to_db(
"insert into tbl_user_sale_device(user_id, sale_device_id) values(%s, %s)"
% (user_id, sale_device_id))
flash(
"Your email address has been put into the bucket for a %s in %s" %
(device_name, sale_name))
except:
flash(
"There is already an entry for you under the bucket for a %s in %s"
% (device_name, sale_name), "error")
finally:
delete_from_db("delete from tbl_user_uuid where uuid='%s'" % uuid)
return redirect(url_for('show_sale', sale_id=sale_id))
def getAnnouncementNotes(role, userEmail):
dResult = {}
publisherEmail = userEmail
## 총 공통공지 개수를 저장하는 변수
total_common_announcement = 0
if role == 'parent':
dMyPartners = json.loads(getMyPartners(role,userEmail))
currentClassTeacherEmail = ""
for teacher in dMyPartners:
if teacher['currentFriend']:
publisherEmail = teacher['email']
break
# for문을 통해 total_common_announcement의 개수를 세서 입력
for common_announcement in query_db('select * from common_announcement \
where publisher_email='+'\''+publisherEmail+'\''):
total_common_announcement = total_common_announcement + 1
# dResult에 다음의 key들을 추가합니다.
dResult['total_common_announcement'] = total_common_announcement
dResult['num_of_article_per_request'] = 5
# dResult['page']를 구하기 위한 연산과정. request당 한 페이지, 한 페이지 당 5개
if (total_common_announcement % dResult['num_of_article_per_request']) != 0 :
dResult['page'] = total_common_announcement / dResult['num_of_article_per_request'] + 1
else :
dResult['page'] = total_common_announcement / dResult['num_of_article_per_request']
dResult['articles'] = []
# dResult['articles'] 에 쓸 내용 담기위해 dArticles 딕셔너리를 만들었어욧.
dArticles = {}
dArticles['individual_announcement'] = []
# cur_id 의 초기값을 -1로 합니다.
cur_id = -1
# 현재 로그인 id가 session['email']이면 query_db의내용을 하나하나 불러옵니다
for common_announcement in query_db('select * from common_announcement where publisher_email='+'\''+publisherEmail+'\''):
# 현재 individual_announcement의 id가 common_announcement의 id와 같으면
# individual_announcement를 append 합니다.
indivisual_announcement_query = 'select * from individual_announcement where common_announcement_id='+str(common_announcement['id'])
if role == 'parent':
indivisual_announcement_query = indivisual_announcement_query + ' AND receiver_email=\''+session['email']+'\''
for individual_announcement in query_db(indivisual_announcement_query):
dArticles['individual_announcement'].append(individual_announcement)
# cur_id가 common_announcement['id']와 다르다면
# 즉 common_announcement의 loop가 충분히 돌아 다른 날짜의 글로 넘어갔다면
# 다음 내용들을 실행합니다.
if cur_id != common_announcement['id']:
dArticles['published_date'] = common_announcement['published_date']
dArticles['publisher_email'] = common_announcement['publisher_email']
dArticles['content'] = common_announcement['content']
dArticles['id'] = common_announcement['id']
dArticles['attached_file'] = common_announcement['attached_file']
# 앞의 내용들이 다 저장되었으면 최종적으로 dResult에 append 합니다
dResult['articles'].append(dArticles)
# append 이후 dArticles 를 초기화합니다.
dArticles = {}
dArticles['individual_announcement'] = []
cur_id = common_announcement['id']
## json.dumps() 함수안에 인자로 리스트나 딕셔너리를 넣어주면
## JSON 스트링으로 바꿔준다.
return json.dumps(dResult)
def getParent():
sResult = ""
for parent in query_db('select * from parent'):
sResult += parent['name']+ ' '+ parent['email'] + '\n'
return sResult
def ajax_respondToAnnouncementNote(): # 메소드가 POST인 경우에만 동작한다. if request.method == 'POST' : # 각 변수에 값을 할당 parent_email = request.form["parent_email"] common_announcement_id = request.form["common_announcement_id"] method = request.form["method"] date = request.form["date"] sQuery = "" # 부모가 알림장 확인을 한 경우 if method == 'confirm': # <확인 날짜>만 업데이트 sQuery = 'UPDATE individual_announcement ' sQuery = sQuery + 'SET confirmed_date =\'' +date+ '\'' sQuery = sQuery + 'WHERE receiver_email = \'' +parent_email+ '\'' sQuery = sQuery + 'AND common_announcement_id = ' +common_announcement_id g.db.execute(sQuery) g.db.commit() return json.dumps(["gooood"]) # 부모가 알림장에 댓글을 단 경우 elif method == 'reply': response_message = request.form["response_message"] # 댓글을 달기 전에 확인버튼을 눌렀는지 알아내기 위해 확인 날짜를 체크 sQuery = 'SELECT confirmed_date FROM individual_announcement ' sQuery = sQuery + 'WHERE common_announcement_id =' +common_announcement_id sQuery = sQuery + ' AND receiver_email = \'' +parent_email+ '\'' check_confirmed_date = query_db(sQuery) # 댓글을 달기 전에 확인버튼을 눌렀으면 <댓글 내용과 댓글 날짜>만 업데이트 if (check_confirmed_date[0]['confirmed_date']) : sQuery = 'UPDATE individual_announcement ' sQuery = sQuery + 'SET response_message_date = \'' +date+ '\', ' sQuery = sQuery + 'response_message = \'' +response_message+ '\' ' sQuery = sQuery + 'WHERE receiver_email = \'' +parent_email+ '\' ' sQuery = sQuery + 'AND common_announcement_id =' +common_announcement_id # 댓글을 달기 전에 확인버튼을 누르지 않았으면 <댓글 내용, 댓글 날짜, 확인 날짜>까지 업데이트 else : sQuery = 'UPDATE individual_announcement ' sQuery = sQuery + 'set confirmed_date =\'' +date+ '\', response_message_date = \'' +date+ '\', ' sQuery = sQuery + 'response_message = \'' +response_message+ '\' ' sQuery = sQuery + 'where receiver_email = \'' +parent_email+ '\' ' sQuery = sQuery + 'AND common_announcement_id = ' +common_announcement_id g.db.execute(sQuery) g.db.commit() return json.dumps(["gooood"]) # 부모가 알림장에 단 댓글을 지우고 싶을 때 elif method == 'delete': sQuery = 'UPDATE individual_announcement ' sQuery = sQuery + 'SET response_message_date = null, ' sQuery = sQuery + 'response_message = null ' sQuery = sQuery + 'WHERE common_announcement_id =' +common_announcement_id sQuery = sQuery + ' AND receiver_email = \'' +parent_email+ '\'' g.db.execute(sQuery) g.db.commit() return json.dumps(["gooood"])
def signal_handler(signal, frame):
debug("Hardware Sale salerunner exiting on SIGINT...")
sys.exit(0)
if __name__ == '__main__':
signal.signal(signal.SIGINT, signal_handler)
if len(sys.argv) > 1:
logfile = sys.argv[1]
sys.stderr = open(logfile, 'a')
debug("Hardware Sale salerunner starting...")
interval = app.config['SALE_RUNNER_INTERVAL']
with app.app_context():
while True:
current_time = datetime.now()
# Calculate the number of seconds to the nearest N minute multiple, N = sale_runner_interval
sleep_seconds = (60 - current_time.second) + (60 * (
(interval - (current_time.minute % interval)) - 1))
target_time = current_time + timedelta(0, sleep_seconds)
debug("Sleeping for %s seconds to reach %s-minute interval of %s" %
(sleep_seconds, interval, target_time.time()))
sleep(sleep_seconds)
active_sales = query_db(
"select sale_id from tbl_sale where active=1 and sale_date<=now()"
)
for sale in active_sales:
debug("Running sale: %s" % sale)
run_sale(sale[0])
