def __call__(self):
if api.user.is_anonymous():
self.request.response.redirect('%s/user_login' %
api.portal.get().absolute_url())
return
user = api.user.get_current().getProperty('email')
execSql = SqlObj()
execStr = 'SELECT * from activity where category="%s" and user="******"' % (
'防癌你我他', user)
result = execSql.execSql(execStr)
self.condition_1 = False
self.condition_2 = False
self.condition_3 = False
self.condition_4 = False
for item in result:
tmp = dict(item)
if tmp['activity_date'] == '2018-06-14 12:00':
self.condition_1 = True
elif tmp['activity_date'] == '2018-06-21 15:30' or tmp[
'activity_date'] == '2018-06-21 14:30':
self.condition_2 = True
elif tmp['activity_date'] == '2018-06-26 12:00':
self.condition_3 = True
elif tmp['activity_date'] == '2018-06-21 12:15' or tmp[
'activity_date'] == '2018-06-21 11:00' or tmp[
'activity_date'] == '0':
self.condition_4 = True
return self.template()
def getMyOrder(self):
username = api.user.get_current().id
execSql = SqlObj()
execStr = """SELECT * FROM coupon_status WHERE `user` LIKE '%s' ORDER BY `time` DESC""" % username
results = execSql.execSql(execStr)
return results
def __call__(self):
request = self.request
if api.user.is_anonymous():
request.response.redirect('%s/user_login' %
api.portal.get().absolute_url())
return
q1 = request.get('q1')
q2 = request.get('q2').replace('\xef\xbc\x8c', ',')
q3 = request.get('q3').replace('\xef\xbc\x8c', ',').replace(
'\xe2\x89\xa6', '<=').replace('\xe2\x89\xa7',
'>=').replace('\xef\xbc\x9c', '<')
q4 = request.get('q4')
q5 = request.get('q5')
q6 = request.get('q6')
execSql = SqlObj()
user = api.user.get_current().getProperty('email')
execStr = """INSERT INTO anti_cancer(`user`, `q1`, `q2`, `q3`, `q4`, `q5`, `q6`)
VALUES('{}','{}','{}','{}','{}','{}','{}')""".format(
user, safe_unicode(q1), safe_unicode(q2), safe_unicode(q3),
safe_unicode(q4), safe_unicode(q5), safe_unicode(q6))
try:
execSql.execSql(execStr)
except Exception as e:
print e
self.q1 = q1
self.q2 = q2
self.q3 = q3
self.q4 = q4
self.q5 = q5
self.q6 = q6
return self.template()
def __call__(self):
roles = api.user.get_current().getRoles()
if 'Manager' not in roles:
return '您無權限'
execSql = SqlObj()
execStr = """SELECT * FROM `activity` WHERE category='防癌你我他'"""
result = execSql.execSql(execStr)
data = []
users = api.user.get_users()
all_user = {}
for user in users:
email = user.getProperty('email')
fullname = user.getProperty('fullname')
en_name = user.getProperty('en_name')
user_id = user.getProperty('user_id')
all_user[email] = {
'fullname': fullname,
'en_name': en_name,
'user_id': user_id
}
for item in result:
tmp = dict(item)
user = tmp['user']
if all_user.has_key(user):
fullname = all_user[user]['fullname']
en_name = all_user[user]['en_name']
user_id = all_user[user]['user_id']
activity_date = tmp['activity_date']
note = tmp['note']
data.append([user_id, fullname, user, note, activity_date])
self.data = data
return self.template()
def __call__(self):
request = self.request
answer1_1 = request.get('answer1_1')
answer1_2 = request.get('answer1_2')
answer1_3 = request.get('answer1_3')
answer1_4 = request.get('answer1_4')
answer1_5 = request.get('answer1_5')
answer1_6 = request.get('answer1_6')
answer2_1 = request.get('answer2_1')
answer2_2 = request.get('answer2_2')
answer2_3 = request.get('answer2_3')
answer2_4 = request.get('answer2_4')
answer2_5 = request.get('answer2_5')
answer2_6 = request.get('answer2_6')
answer2_7 = request.get('answer2_7')
personal_pressure = (int(answer1_1) + int(answer1_2) + int(answer1_3) + int(answer1_4) + int(answer1_5) + int(answer1_6)) / 6
work_pressure = (int(answer2_1) + int(answer2_2) + int(answer2_3) + int(answer2_4) + int(answer2_5) + int(answer2_6) + int(answer2_7)) / 7
user = api.user.get_current().getUserName()
execSql = SqlObj()
execStr = """INSERT INTO pressure(user, personal_pressure, work_pressure)
VALUES('{}', '{}', '{}')""".format(user, personal_pressure, work_pressure)
execSql.execSql(execStr)
self.personal_pressure = personal_pressure
self.work_pressure = work_pressure
return self.template()
def __call__(self):
request = self.request
execSql = SqlObj()
currency = request.get('currency')
total = request.get('total')
product_detail = request.get('product_detail')
discount = request.get('discount') or 'Null'
discount_detail = request.get('discount_detail')
username = api.user.get_current().getUserName()
recipient_name = request.get('recipient_name', '')
address = request.get('address', '')
billing_no = request.get('billing_no', '')
coupon_code = request.get('coupon_code') or 'Null'
create_time = request.get('create_time')
coupon_owner = ''
if coupon_code and coupon_code != 'Null':
existCode = api.portal.get_registry_record(
'alpha.content.browser.user_configlet.IUser.promoCode')
coupon_owner = ', '.join([
key for key, value in existCode.items() if value == coupon_code
])
execStr = "INSERT INTO `coupon_status`(`billing_no`, `coupon_code`, `user`, `recipient_name`, `address`, `product_detail`, `currency`, `total`, `discount`, `coupon_owner`, `discount_detail`, `time`) \
VALUES ('%s', %s, '%s', '%s', '%s', '%s', '%s', %s, %s, '%s', '%s', '%s')" \
%(billing_no, coupon_code, username, recipient_name, address, product_detail, currency, total, discount, coupon_owner, discount_detail, create_time)
execSql.execSql(execStr)
def __call__(self):
roles = api.user.get_current().getRoles()
if 'Manager' not in roles:
return '您無權限'
else:
execSql = SqlObj()
execStr = """SELECT * FROM `pressure` ORDER BY time"""
result = execSql.execSql(execStr)
data = []
users = api.user.get_users()
all_user = {}
for user in users:
email = user.getProperty('email')
fullname = user.getProperty('fullname')
en_name = user.getProperty('en_name')
user_id = user.getProperty('user_id')
all_user[email] = {
'fullname': fullname,
'en_name': en_name,
'user_id': user_id
}
for item in result:
tmp = dict(item)
user = tmp['user']
if all_user.has_key(user):
fullname = all_user[user]['fullname']
en_name = all_user[user]['en_name']
user_id = all_user[user]['user_id']
personal_pressure = tmp['personal_pressure']
work_pressure = tmp['work_pressure']
time = tmp['time'].strftime('%Y/%m/%d %H:%M')
data.append([user, personal_pressure, work_pressure, time, fullname, en_name, user_id])
self.data = data
return self.template()
def mkqr(event):
portal = api.portal.get()
user = api.user.get_current().getProperty('email')
execSql = SqlObj()
execStr = "select picture_data from user_picture WHERE user = '******'" % user
result = execSql.execSql(execStr)
if result:
return
abs_url = portal.absolute_url()
# url = 'http://696d405b.ngrok.io/hpe/event/event_handl?email=%s' %(user)
url = '%s/event/event_handl?email=%s' % (abs_url, user)
qr = qrcode.QRCode()
qr.add_data(url)
qr.make_image().save('user.png')
img = open('user.png', 'rb')
b64_img = base64.b64encode(img.read())
execStr = """INSERT INTO user_picture(user, picture_data)
VALUES('{}', '{}')""".format(user, b64_img)
execSql.execSql(execStr)
img.close()
api.portal.show_message(message=_(
u'Dear colleagues, please provide basic information if this is the first time you log in. Thank you'
),
request=portal.REQUEST,
type='info')
def __call__(self):
request = self.request
execSql = SqlObj()
execStr = """SELECT * FROM coupon_status ORDER BY `coupon_status`.`id` DESC"""
self.data = execSql.execSql(execStr)
return self.template()
def __call__(self):
try:
os.makedirs('/tmp/images')
except:
shutil.rmtree('/tmp/images')
os.remove('/tmp/images.zip')
os.makedirs('/tmp/images')
execStr = """SELECT img,image_title FROM bicycle_picture WHERE is_check = 1"""
execSql = SqlObj()
result = execSql.execSql(execStr)
for item in result:
path = '/tmp/images/%s.jpeg' % item[1]
with open(path, 'wb') as f:
f.write(item[0].decode('base64'))
zf = zipfile.ZipFile('/tmp/images.zip', mode='w')
os.chdir('/tmp/images')
for root, folders, files in os.walk("./"):
for sfile in files:
aFile = os.path.join(root, sfile)
zf.write(aFile)
zf.close()
self.request.response.setHeader('Content-Type', 'application/zip')
self.request.response.setHeader('Content-Disposition',
'attachment; filename="images.zip"')
with open('/tmp/images.zip') as thezip:
return thezip.read()
def __call__(self):
request = self.request
portal = api.portal.get()
course = request.get('course')
period = request.get('period')
exceptList = request.get('exceptList')
execSql = SqlObj()
if exceptList:
try:
for k, v in json.loads(exceptList).items():
tmp = [i for i in v.split(',') if i]
sqlStr = """UPDATE course_list SET exceptList = %s WHERE course = '%s' AND period = '%s' and subject = '%s'
""" %(json.dumps(','.join(sorted(set(tmp), key=lambda x: int(x)))), course, period, k)
execSql.execSql(sqlStr)
api.portal.show_message(message='更新成功!!'.encode(), request=request)
return 'success'
except:
return 'error'
if course and period:
sqlStr = """SELECT subject, exceptList, start_time FROM course_list WHERE course = '%s' AND period = '%s' ORDER BY start_time
""" %(course, period)
self.result = execSql.execSql(sqlStr)
self.course = course
self.period = period
return self.template()
def __call__(self):
roles = api.user.get_current().getRoles()
if 'Manager' not in roles:
return '您無權限'
execSql = SqlObj()
execStr = """SELECT * FROM `anti_cancer`"""
self.result = execSql.execSql(execStr)
return self.template()
def __call__(self):
username = api.user.get_current().id
permissions = api.user.get_permissions(
username=username)['Manage Site']
if permissions:
request = self.request
execSql = SqlObj()
execStr = """SELECT * FROM coupon_status ORDER BY `coupon_status`.`id` DESC"""
self.data = execSql.execSql(execStr)
return self.template()
else:
self.request.response.redirect(self.context.portal_url)
def __call__(self):
request = self.request
portal = api.portal.get()
execSql = SqlObj()
contact = {}
locationDict = {}
for i in portal['contact'].getChildNodes():
locationDict[i.id] = i.title
contact[i.id] = {
'good': '',
'bad': '',
'mail': i.description
}
now = datetime.now().strftime('%Y-%m-%d')
sqlStr = """SELECT sa.*, co.location FROM satisfaction as sa, course_list as co
WHERE (question9 != '' OR question10 != '' OR question11 != '' OR question12 != '')
AND sa.timestamp LIKE '{}%%' AND sa.course = co.course AND sa.period = co.period
AND sa.subject = co.subject ORDER BY course
""".format(now)
result = execSql.execSql(sqlStr)
for i in result:
formatStr = """科目:{}<br>課程:{}<br>期數:{}<br>座號:{}<br>教學中心:{}<br>講師:{}<br>時間:{}<br>意見提供:<br>1. {}<br/>2. {}<br/>3. {}<br>4. {}<hr>
""".format(i['course'], i['subject'], i['period'], i['seat'], locationDict[i['location']]
, i['teacher'], i['date'], i['question9'], i['question10'], i['question11']
, i['question12'])
questionList = [i['question%s' %k] for k in range(1, 9)]
location = i['location']
if 1 in questionList or 2 in questionList:
contact[location]['bad'] += formatStr
contact['all']['bad'] += formatStr
else:
contact[location]['good'] += formatStr
contact['all']['good'] += formatStr
smtpObj = smtplib.SMTP('localhost')
for k,v in contact.items():
mail = v['mail']
good = v['good']
bad = v['bad']
if good or bad:
mailStr = '<h1>好的評論</h1>' + good + '<h1>壞的評論</h1>' + bad
for m in mail.split('\r\n'):
if m:
mime_text = MIMEText(mailStr, 'html', 'utf-8')
mime_text['Subject'] = Header("%s 意見提供" %(now), 'utf-8')
smtpObj.sendmail('*****@*****.**', m, mime_text.as_string())
print 'send mail to %s' %k
def __call__(self):
if api.user.is_anonymous():
self.request.response.redirect('%s/user_login'%api.portal.get().absolute_url())
return
user = api.user.get_current().getProperty('email')
execSql = SqlObj()
execStr = """SELECT * FROM bicycle_picture WHERE user = '******' AND is_check = 0""".format(user)
self.audit_data = execSql.execSql(execStr)
execStr = """SELECT * FROM bicycle_picture where user = '******' AND is_check = 1""".format(user)
self.complete_data = execSql.execSql(execStr)
return self.template()
def __call__(self):
request = self.request
abs_url = api.portal.get().absolute_url()
if api.user.is_anonymous():
request.response.redirect('%s/user_login' % abs_url)
return
roles = api.user.get_current().getRoles()
if 'Manager' not in roles:
request.response.redirect('%s/user_login' % abs_url)
execSql = SqlObj()
execStr = """SELECT * FROM log ORDER BY time DESC"""
self.result = execSql.execSql(execStr)
return self.template()
def __call__(self):
request = self.request
execSql = SqlObj()
coupon_code = request.get('coupon_code')
currency = request.get('currency')
total = request.get('total')
product_detail = request.get('product_detail')
discount = request.get('discount')
discount_detail = request.get('discount_detail')
username = api.user.get_current().getUserName()
existCode = api.portal.get_registry_record('alpha.content.browser.user_configlet.IUser.promoCode')
coupon_owner = existCode[coupon_code]
execStr = """INSERT INTO `coupon_status`(`coupon_code`, `user`, `product_detail`, `currency`, `total`, `discount`, `coupon_owner`,
discount_detail) VALUES (%s, '%s', '%s', '%s', %s, %s, '%s', '%s')
""" %(coupon_code, username, product_detail, currency, total, discount, coupon_owner, discount_detail)
execSql.execSql(execStr)
def __call__(self):
request = self.request
# 檢查是否已完整填寫員工資料
current = api.user.get_current()
# import pdb; pdb.set_trace()
if not (current.getProperty('en_name')
and current.getProperty('officephone')
and current.getProperty('cellphone')):
return 'loseData'
select_date = request.get('select_date[]')
if type(select_date) == str:
select_date = [select_date]
event = request.get('event')
user = api.user.get_current().getProperty('email')
step = request.get('step')
special = request.get('special[0][]')
execSql = SqlObj()
if select_date:
for activity in select_date:
# 寫進活動列表
execStr = """INSERT INTO activity(user,category,activity_date,step) VALUES('{}','{}'
,'{}','{}')""".format(user, event, activity, step)
execSql.execSql(execStr)
# 寫進log
log = '報名%s' % activity
execStr = """INSERT INTO log(user,category,log) VALUES('{}','{}','{}')
""".format(user, event, log)
execSql.execSql(execStr)
# 防癌你我他的三選一
if special:
execStr = """INSERT INTO activity(user,category,activity_date,step, note) VALUES('{}','{}'
,'{}','{}','{}')""".format(user, event, special[0], step,
special[1])
execSql.execSql(execStr)
# 寫進log
log = '報名%s' % special[0]
execStr = """INSERT INTO log(user,category,log) VALUES('{}','{}','{}')
""".format(user, event, log)
execSql.execSql(execStr)
return 'success'
def __call__(self):
try:
request = self.request
img_data = request.get('img_data')
location = img_data.split(',')[0]
want_award = img_data.split(',')[1]
img = img_data.split(',')[3]
user = api.user.get_current().getProperty('email')
now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
image_title = '%s_%s_%s' % (user, location, now_time)
execSql = SqlObj()
execStr = """INSERT INTO `bicycle_picture`(`user`, `location`, `want_award`, `img`, `image_title`, `upload_time`) VALUES (
'{}', '{}', '{}', '{}', '{}', '{}')""".format(
user, location, want_award, img, image_title, now_time)
execSql.execSql(execStr)
return 'success'
except Exception as e:
import pdb
pdb.set_trace()
return 'error'
def __call__(self):
request = self.request
if api.user.is_anonymous():
request.response.redirect('login')
return
user = api.user.get_current()
groups = user.getGroups()
self.id = user.id
offset = request.get('offset', 0)
location = self.getLocation(groups)
execSql = SqlObj()
if user.id != 'admin':
sqlStr = """SELECT course, period, MAX(timestamp) as maxtime FROM `course_list` WHERE location = '{}' GROUP BY course, period
ORDER BY `maxtime` DESC""".format(location)
else:
sqlStr = """SELECT course, period, MAX(timestamp) as maxtime, location FROM `course_list` GROUP BY course, period, location
ORDER BY maxtime DESC"""
self.courseList = execSql.execSql(sqlStr)
return self.template()
def __call__(self):
request = self.request
id_list = request.get('id_list[]')
action = request.get('action')
if type(id_list) is str:
id_list = [id_list, 'zzz']
execSql = SqlObj()
try:
now_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
for item in id_list:
if item != 'zzz':
if action == 'pass':
execStr = """UPDATE bicycle_picture SET is_check = 1, complete_time = '{}' WHERE id = {}
""".format(now_time, item)
elif action == 'del':
execStr = """DELETE FROM bicycle_picture WHERE id = {}""".format(
item)
execSql.execSql(execStr)
return 'success'
except Exception as e:
return 'error'
def __call__(self):
request = self.request
activity_date = request.get('activity_date')
category = request.get('category')
lang = request.get('lang')
user = api.user.get_current().getProperty('email')
execSql = SqlObj()
execStr = """DELETE FROM activity WHERE user='******' AND activity_date='{}'
""".format(user, activity_date)
execSql.execSql(execStr)
execStr = """INSERT INTO log(user,log,category) VALUES('{}','{}','{}')
""".format(user, '取消%s' % activity_date, category)
execSql.execSql(execStr)
if lang == 'Chinese':
request.response.redirect('%s/profile' %
api.portal.get().absolute_url())
if lang == 'English':
request.response.redirect('%s/en_profile' %
api.portal.get().absolute_url())
def getDateRange(self, course, period):
execSql = SqlObj()
sqlStr = """SELECT MAX(start_time) AS start, MIN(start_time) AS end FROM `course_list` WHERE course = '{}' and period = {}
""".format(course, period)
result = execSql.execSql(sqlStr)[0]
return '%s~%s' %(result['end'].strftime('%Y-%m-%d %H:%M'), result['start'].strftime('%Y-%m-%d %H:%M'))
def __call__(self):
request = self.request
portal = api.portal.get()
denier = request.get('denier')
filament = request.get('filament')
high_tenacity = request.get('high_tenacity')
elongation = request.get('elongation')
has2 = request.get('has2')
data = {}
uid = request.get('uid')
if uid:
self.result = api.content.get(UID=uid)
return self.category()
else:
filament = float(filament)
denier = float(denier)
sqlInstance = SqlObj()
query = {
'context': portal['products'],
'portal_type': 'product',
'review_state': 'published',
'index_filament': filament,
'index_denier': denier
}
if high_tenacity:
high_tenacity = float(high_tenacity)
query['index_ht_min'] = {
'query': high_tenacity,
'range': 'max'
}
query['index_ht_max'] = {
'query': high_tenacity,
'range': 'min'
}
if has2:
has2 = float(has2)
query['index_h2_min'] = {'query': has2, 'range': 'max'}
query['index_h2_max'] = {'query': has2, 'range': 'min'}
if elongation:
elongation = float(elongation)
query['index_el_min'] = {'query': elongation, 'range': 'max'}
query['index_el_max'] = {'query': elongation, 'range': 'min'}
filterProduct = api.content.find(**query)
if not high_tenacity:
high_tenacity = 'NULL'
if not has2:
has2 = 'NULL'
if not elongation:
elongation = 'NULL'
sqlStr = """INSERT INTO search_log(denier, filament, ht, el, has2) VALUES({}, {}, {}, {}, {})
""".format(denier, filament, high_tenacity, elongation,
has2)
sqlInstance.execSql(sqlStr)
self.filterProduct = filterProduct if filterProduct else False
self.download_file = api.content.find(
context=portal['system_file'], portal_type='File', depth=1)
return self.template()
def __call__(self):
request = self.request
date = request.get('date')
event = request.get('event')
execSql = SqlObj()
all_users = api.user.get_users()
user_data = {}
for item in all_users:
email = item.getProperty('email')
user_id = item.getProperty('user_id')
cellphone = item.getProperty('cellphone')
location = item.getProperty('location')
officephone = item.getProperty('officephone')
fullname = item.getProperty('fullname')
en_name = item.getProperty('en_name')
user_data[email] = {
'user_id': user_id,
'cellphone': cellphone,
'en_name': en_name,
'location': location,
'officephone': officephone,
'fullname': fullname
}
if event == '樂活騎單車':
if date == 'audit':
execStr = """SELECT * FROM bicycle_picture WHERE is_check = 0"""
elif date == 'complete' or date == 'img_preview':
execStr = """SELECT * FROM bicycle_picture WHERE is_check = 1 ORDER BY complete_time asc"""
else:
execStr = """SELECT * FROM activity WHERE category = '{}' AND activity_date = '{}'
ORDER BY sing_up_time""".format(event, date)
result = execSql.execSql(execStr)
data = []
if event == '樂活騎單車':
for item in result:
tmp = dict(item)
user_email = tmp['user']
location = tmp['location']
want_award = tmp['want_award']
img = tmp['img']
image_title = tmp['image_title']
is_check = tmp['is_check']
id = tmp['id']
upload_time = tmp['upload_time']
if user_data.has_key(user_email):
user_list = user_data[user_email]
data.append([
user_list['user_id'], user_email,
user_list['fullname'], user_list['en_name'],
user_list['officephone'], user_list['cellphone'],
user_list['location'], location, want_award, img,
image_title, is_check, id, upload_time
])
else:
for item in result:
tmp = dict(item)
activity_date = tmp['activity_date']
sing_up_time = tmp['sing_up_time']
user_email = tmp['user']
category = tmp['category']
note = tmp['note']
if user_data.has_key(user_email):
user_list = user_data[user_email]
data.append([
category, activity_date, user_list['user_id'],
user_email, user_list['fullname'],
user_list['en_name'], user_list['officephone'],
user_list['cellphone'], user_list['location'],
sing_up_time, note
])
self.data = data
if date == '2018-06-21 12:15' or date == '2018-06-21 11:00' or date == '0':
return self.template1()
elif date == 'img_preview':
return self.img_preview()
elif event == '樂活騎單車':
return self.template2()
else:
return self.template()
def __call__(self):
request = self.request
user = api.user.get_current()
user_name = user.getProperty('email')
reservation_date = user.getProperty('reservation_date')[:10]
peroid = user.getProperty('peroid')
language = request.get('language', '') # 英文預約
brain = api.content.find(portal_type="Reservation",
context=api.portal.get())
for item in brain:
obj = item.getObject()
date = obj.date.strftime('%Y/%m/%d')
if date == reservation_date:
if peroid == 'period1':
date = obj.date
peroid_flag = 'period1'
obj.peroid1 = ''
elif peroid == 'period2':
date = obj.date + datetime.timedelta(minutes=20)
peroid_flag = 'period2'
obj.peroid2 = ''
elif peroid == 'period3':
date = obj.date + datetime.timedelta(minutes=40)
peroid_flag = 'period3'
obj.peroid3 = ''
elif peroid == 'period4':
date = obj.date + datetime.timedelta(minutes=60)
peroid_flag = 'period4'
obj.peroid4 = ''
elif peroid == 'period5':
date = obj.date + datetime.timedelta(minutes=80)
peroid_flag = 'period5'
obj.peroid5 = ''
elif peroid == 'period6':
obj.peroid6 = ''
date = obj.date + datetime.timedelta(minutes=100)
peroid_flag = 'period6'
elif peroid == 'alternate':
alternate_list = obj.alternate.split(',')
alternate_list.remove(user_name)
obj.alternate = ''
for item in alternate_list:
if obj.alternate:
obj.alternate += ',%s' % item
else:
obj.alternate = item
user.setMemberProperties(mapping={
'reservation_date': '',
'peroid': ''
})
execSql = SqlObj()
execStr = """INSERT INTO log(user,category,log) VALUES('{}','{}','{}')
""".format(user_name, '預約', '預約取消')
execSql.execSql(execStr)
if language == 'english':
request.response.redirect('%s/en_reservation' %
api.portal.get().absolute_url())
else:
request.response.redirect('%s/reservation' %
api.portal.get().absolute_url())
def __call__(self):
if api.user.is_anonymous():
self.request.response.redirect('%s/user_login' %
api.portal.get().absolute_url())
return
user = api.user.get_current()
user_email = user.getProperty('email')
execSql = SqlObj()
execStr = """SELECT picture_data FROM user_picture WHERE user = '******'
""".format(user_email)
result = execSql.execSql(execStr)
self.picture_data = 'data:image/png;base64,%s' % dict(
result[0])['picture_data']
self.user_id = user.getProperty('user_id', '')
self.user_email = user_email
self.user_ch_name = user.getProperty('fullname', '')
self.user_en_name = user.getProperty('en_name', '')
self.user_cellphone = user.getProperty('cellphone', '')
self.user_location = user.getProperty('location', '')
self.user_officephone = user.getProperty('officephone', '')
execStr = """SELECT * FROM activity WHERE user='******'""".format(
user_email)
result = execSql.execSql(execStr)
data = []
for item in result:
tmp = dict(item)
category = tmp['category']
activity_date = tmp['activity_date']
is_first = tmp['is_first']
is_end = tmp['is_end']
step = tmp['step']
if is_first == 0:
condition_1 = True
condition_2 = False
condition_3 = False
elif is_first == 1 and is_end == 1:
condition_1 = False
condition_2 = True
condition_3 = False
elif is_first == 1 and is_end == 0:
condition_1 = False
condition_2 = False
condition_3 = True
data.append({
'category': category,
'activity_date': activity_date,
'step': step,
'condition_1': condition_1,
'condition_2': condition_2,
'condition_3': condition_3,
})
self.data = sorted(data)
execStr = """SELECT SUM(step) as sum_step FROM `activity` WHERE user='******' AND is_first=1
AND is_end=1""".format(user_email)
result = execSql.execSql(execStr)
sum_step = dict(result[0])['sum_step']
if not sum_step:
sum_step = 0
self.sum_step = sum_step
return self.template()
def __call__(self):
request = self.request
context = self.context
worker_roles = api.user.get_current().getRoles()
if 'Reader' in worker_roles:
email = request.get('email')
start = time.mktime(context.start.timetuple())
end = time.mktime(context.end.timetuple())
now = time.time()
user_name = api.user.get(username=email).getProperty('fullname')
start_date = context.start.strftime('%Y-%m-%d %H:%M')
category = context.title
step = context.step
now_time = datetime.datetime.fromtimestamp(now).strftime(
'%Y-%m-%d %H:%M:%S')
# 介再開始和結束的時間內且role要是讀者
if now >= start - 14400 and now <= end:
execSql = SqlObj()
execStr = """SELECT * FROM activity WHERE user='******' AND
activity_date='{}'""".format(email, start_date)
result = execSql.execSql(execStr)
# 若沒報名就幫他現場報名
if not result:
execStr = """INSERT INTO activity(user,category,activity_date,is_first
,check_in_time,step) VALUES('{}','{}','{}',1,'{}', '{}')
""".format(email, category, start_date, now_time, step)
execSql.execSql(execStr)
log = "%s 現場報名 %s %s" % (user_name, category, start_date)
execStr = """INSERT INTO log(user,category,log) VALUES('{}','{}','{}')
""".format(email, category, log)
execSql.execSql(execStr)
return '現場報名成功'
else:
tmp = dict(result[0])
if tmp['is_first'] == 0 and tmp['is_end'] == 0 and tmp[
'check_in_time'] == None:
execStr = """UPDATE activity SET is_first = 1,check_in_time='{}' WHERE user='******' AND
activity_date='{}'""".format(
now_time, email, start_date)
execSql.execSql(execStr)
execStr = """INSERT INTO log(user,category,log) VALUES('{}','{}','{}')
""".format(email, '活動進行', '%s 活動參加' % now_time)
execSql.execSql(execStr)
return '%s %s 報到成功' % (user_name, now_time)
elif tmp['is_first'] == 1 and tmp['is_end'] == 0 and tmp[
'check_in_time'] and tmp['finish_time'] == None:
check_in_time = datetime.datetime.strptime(
tmp['check_in_time'], '%Y-%m-%d %H:%M:%S')
now = datetime.datetime.now()
time_delta = now - check_in_time
if time_delta.total_seconds() > 60:
execStr = """UPDATE activity SET is_end = 1,finish_time='{}' WHERE
user='******' AND activity_date='{}'
""".format(now.strftime('%Y-%m-%d %H:%M:%S'),
email, start_date)
execSql.execSql(execStr)
execStr = """INSERT INTO log(user,category,log) VALUES('{}','{}','{}')
""".format(
email, '活動進行',
'%s活動完成' % now.strftime('%Y-%m-%d %H:%M:%S'))
execSql.execSql(execStr)
return '%s %s 活動完成' % (user_name, now_time)
else:
return '%s 您已報到過 請勿重複報到' % user_name
elif tmp['is_first'] == 1 and tmp['is_end'] == 1:
return '%s 您已完成活動 請勿再報到' % user_name
else:
return '活動尚未開始'
else:
return '權限不足'
def __call__(self):
# peroid 和period拼錯
request = self.request
peroid = request.get('peroid')
start_date = request.get('start_date')
time = request.get('time')
user = api.user.get_current().getUserName()
brain = api.content.find(portal_type="Reservation")
flag = False
peroid_flag = ''
for item in brain:
obj = item.getObject()
date = obj.date.strftime('%Y/%m/%d')
if start_date == date:
if peroid == 'period1' and not obj.peroid1:
obj.peroid1 = user
flag = True
peroid_flag = 'period1'
elif peroid == 'period2' and not obj.peroid2:
obj.peroid2 = user
flag = True
peroid_flag = 'period2'
elif peroid == 'period3' and not obj.peroid3:
obj.peroid3 = user
flag = True
peroid_flag = 'period3'
elif peroid == 'period4' and not obj.peroid4:
obj.peroid4 = user
flag = True
peroid_flag = 'period4'
elif peroid == 'period5' and not obj.peroid5:
obj.peroid5 = user
flag = True
peroid_flag = 'period5'
elif peroid == 'period6' and not obj.peroid6:
obj.peroid6 = user
flag = True
peroid_flag = 'period6'
elif peroid == 'alternate':
if obj.alternate:
obj.alternate += ',%s' % user
else:
obj.alternate = user
flag = True
peroid_flag = 'alternate'
####防止同時預約，導致前者被覆蓋####
if flag == True:
execSql = SqlObj()
log = '%s %s 預約成功' % (start_date, peroid)
execStr = """INSERT INTO log(user,category,log) VALUES('{}','{}','{}')
""".format(user, '預約', log)
execSql.execSql(execStr)
api.user.get_current().setMemberProperties(
mapping={
'reservation_date': '%s %s' % (start_date, time),
'peroid': peroid_flag
})
return 'success'
else:
return 'error'
def __call__(self):
request = self.request
response = request.response
data = json.loads(request.get('data'))
course = request.get('course')
period = request.get('period')
execSql = SqlObj()
# execStr = """SELECT COUNT(id), uid FROM emergency WHERE period = '{}' GROUP by uid""".format(period)
# count_result = execSql.execSql(execStr)[0]
# count = count_result[0]
# uid = count_result[1]
execStr = """SELECT COUNT(id) FROM emergency WHERE period = '{}'""".format(
period)
count = execSql.execSql(execStr)[0][0]
execStr = """SELECT uid FROM emergency WHERE period = '{}' and uid != '' LIMIT 1""".format(
period)
uid = execSql.execSql(execStr)[0][0]
content = api.content.get(UID=uid)
numbers = content.numbers
if numbers:
rate = '%s%%' % round(float(count) / float(numbers) * 100, 1)
else:
rate = False
output = StringIO()
workbook = xlsxwriter.Workbook(output)
worksheet1 = workbook.add_worksheet('Sheet1')
worksheet2 = workbook.add_worksheet('Sheet2')
worksheet2.write_column('A1', data['2'].keys())
worksheet2.write_column('B1', data['2'].values())
worksheet2.write_column('C1', data['3'].keys())
worksheet2.write_column('D1', data['3'].values())
worksheet2.write_column('E1', data['4'].keys())
worksheet2.write_column('F1', data['4'].values())
worksheet2.write_column('G1', data['5'].keys())
worksheet2.write_column('H1', data['5'].values())
worksheet2.write_column('I1', data['6'].keys())
worksheet2.write_column('J1', data['6'].values())
worksheet2.write_column('K1', data['7'].keys())
worksheet2.write_column('L1', data['7'].values())
worksheet2.write_column('M1', data['8'].keys())
worksheet2.write_column('N1', data['8'].values())
worksheet2.write_column('O1', data['9'].keys())
worksheet2.write_column('P1', data['9'].values())
worksheet2.write_column('Q1', data['10'].keys())
worksheet2.write_column('R1', data['10'].values())
title_style = workbook.add_format({
'align': 'center',
'valign': 'vcenter',
'font_size': '20'
})
worksheet1.merge_range('A1:P3', '中國勞工安全衛生管理學會', title_style)
worksheet1.merge_range('A4:P6',
'第%s期 【 %s 】 訓練班' % (period, course),
title_style)
worksheet1.merge_range('A7:P9', '訓前調查表', title_style)
worksheet1.merge_range('Q1:W2', '已填份數 / 總份數 = 回收率', title_style)
if rate:
worksheet1.merge_range('Q3:W4',
'%s / %s = %s' % (count, numbers, rate),
title_style)
else:
worksheet1.merge_range('Q3:W4', '尚未設定人數', title_style)
chart_total = workbook.add_chart({'type': 'pie'})
chart_total.add_series({
'name': 'Pie sales data',
'categories': '=Sheet2!$A$1:$A$4',
'values': '=Sheet2!$B$1:$B$4',
'data_labels': {
'percentage': True
},
})
chart_total.set_title({'name': '參訓目的'})
worksheet1.insert_chart('A11', chart_total)
chart_total = workbook.add_chart({'type': 'pie'})
chart_total.add_series({
'name': 'Pie sales data',
'categories': '=Sheet2!$C$1:$C$4',
'values': '=Sheet2!$D$1:$D$4',
'data_labels': {
'percentage': True
},
})
chart_total.set_title({'name': '年齡'})
worksheet1.insert_chart('I11', chart_total)
chart_total = workbook.add_chart({'type': 'pie'})
chart_total.add_series({
'name': 'Pie sales data',
'categories': '=Sheet2!$E$1:$E$11',
'values': '=Sheet2!$F$1:$F$11',
'data_labels': {
'percentage': True
},
})
chart_total.set_title({'name': '行業別'})
worksheet1.insert_chart('A26', chart_total)
chart_total = workbook.add_chart({'type': 'pie'})
chart_total.add_series({
'name': 'Pie sales data',
'categories': '=Sheet2!$G$1:$G$5',
'values': '=Sheet2!$H$1:$H$5',
'data_labels': {
'percentage': True
},
})
chart_total.set_title({'name': '您是如何知道本項訓練課程'})
worksheet1.insert_chart('I26', chart_total)
chart_total = workbook.add_chart({'type': 'pie'})
chart_total.add_series({
'name': 'Pie sales data',
'categories': '=Sheet2!$I$1:$I$4',
'values': '=Sheet2!$J$1:$J$4',
'data_labels': {
'percentage': True
},
})
chart_total.set_title({'name': '您選擇本中心得因素(複選)'})
worksheet1.insert_chart('A41', chart_total)
chart_total = workbook.add_chart({'type': 'pie'})
chart_total.add_series({
'name': 'Pie sales data',
'categories': '=Sheet2!$K$1:$K$2',
'values': '=Sheet2!$L$1:$L$2',
'data_labels': {
'percentage': True
},
})
chart_total.set_title({'name': '是否曾經從事醫護工作'})
worksheet1.insert_chart('I41', chart_total)
chart_total = workbook.add_chart({'type': 'pie'})
chart_total.add_series({
'name': 'Pie sales data',
'categories': '=Sheet2!$M$1:$M$4',
'values': '=Sheet2!$N$1:$N$4',
'data_labels': {
'percentage': True
},
})
chart_total.set_title({'name': '預觸電患者急救時應先'})
worksheet1.insert_chart('A56', chart_total)
chart_total = workbook.add_chart({'type': 'pie'})
chart_total.add_series({
'name': 'Pie sales data',
'categories': '=Sheet2!$O$1:$O$4',
'values': '=Sheet2!$P$1:$P$4',
'data_labels': {
'percentage': True
},
})
chart_total.set_title({
'name': '遇到車禍事件，發現有人員受傷躺在現場，可否移動患者',
'name_font': {
'size': 13
},
})
worksheet1.insert_chart('I56', chart_total)
chart_total = workbook.add_chart({'type': 'pie'})
chart_total.add_series({
'name': 'Pie sales data',
'categories': '=Sheet2!$Q$1:$Q$3',
'values': '=Sheet2!$R$1:$R$3',
'data_labels': {
'percentage': True
},
})
chart_total.set_title({
'name': '發現民眾倒臥再旁，且呈現無意識，缺氧狀快，應於幾分鐘內施予急救',
'name_font': {
'size': 13
},
})
worksheet1.insert_chart('A71', chart_total)
workbook.close()
response.setHeader('Content-Type', 'application/x-xlsx')
response.setHeader(
'Content-Disposition',
'attachment; filename="%s-%s.xlsx"' % (course, period))
return output.getvalue()
