def __call__(self):
request = self.request
type = request.get('type')
category = request.get('category')
date = request.get('date')
department = request.get('department')
title = request.get('title')
detail_1 = request.get('detail_1')
detail_2 = request.get('detail_2')
detail_3 = request.get('detail_3')
detail_4 = request.get('detail_4')
user = api.user.get_current().getUserName()
if type and category and date:
sqlInstance = SqlObj()
year = datetime.now().year - 1911
sqlStr = """SELECT MAX(code) FROM signature WHERE year = {}""".format(
year)
code = sqlInstance.execSql(sqlStr)[0][0]
code = int(code) + 1 if code else 1
sqlStr = """INSERT INTO `signature`(`user`, `category`, `type`, `date`, `department`, `title`, `detail_1`, `detail_2`,
`detail_3`, `detail_4`, year, code) VALUES ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}', {}, {})
""".format(user, category, type, date, department, title,
detail_1, detail_2, detail_3, detail_4, year,
code)
sqlInstance.execSql(sqlStr)
api.portal.show_message(message='新增簽呈成功!'.decode(),
request=request)
abs_url = api.portal.get().absolute_url()
request.response.redirect(abs_url + '/signature_view')
self.sample = self.GetSignatureSample()
return self.template()
def __call__(self):
request = self.request
id = request.get('id')
type = request.get('type')
category = request.get('category')
date = request.get('date')
department = request.get('department')
title = request.get('title')
detail_1 = request.get('detail_1')
detail_2 = request.get('detail_2')
detail_3 = request.get('detail_3')
detail_4 = request.get('detail_4')
sqlInstance = SqlObj()
if category and title:
sqlStr = """UPDATE signature SET title='{}',category='{}',date='{}',type='{}',department='{}',title='{}',detail_1='{}',
detail_2='{}',detail_3='{}',detail_4='{}' WHERE id = {}""".format(
title, category, date, type, department, title, detail_1,
detail_2, detail_3, detail_4, id)
sqlInstance.execSql(sqlStr)
url = api.portal.get().absolute_url() + '/signature_view'
api.portal.show_message(message='更新簽呈成功!'.decode(),
request=request)
request.response.redirect(url)
else:
sqlStr = """SELECT * FROM signature WHERE id = {}""".format(id)
self.result = sqlInstance.execSql(sqlStr)
self.sample = self.GetSignatureSample()
return self.template()
def __call__(self):
request = self.request
receipt_id = request.get('receipt_id')
if receipt_id:
sqlInstance = SqlObj()
sqlStr = """UPDATE receipt SET is_cancel = 1 WHERE id = {}""".format(
int(receipt_id))
sqlInstance.execSql(sqlStr)
request.response.redirect('%s/@@admin_receipt_list' %
self.context.absolute_url())
def __call__(self):
request = self.request
id = request.get('id')
action = request.get('action')
sqlInstance = SqlObj()
if action == 'search':
sqlStr = """SELECT * FROM signature_sample WHERE id = {}""".format(
id)
result = dict(sqlInstance.execSql(sqlStr)[0])
return json.dumps(result)
elif action == 'delect':
sqlStr = """DELETE FROM signature_sample WHERE id = {}""".format(
id)
sqlInstance.execSql(sqlStr)
def __call__(self):
request = self.request
id = request.get('id')
execSql = SqlObj()
sqlStr = """SELECT embedUrl FROM youtube WHERE id = '%s'""" % id
self.result = execSql.execSql(sqlStr)
return self.template()
def __call__(self):
request = self.request
action = request.get('action')
self.action = action
key_word = request.get('key_word')
if key_word:
sqlInstance = SqlObj()
if action == 'between_date':
start_date = key_word.split(',')[0]
end_date = key_word.split(',')[1]
sqlStr = """SELECT * FROM receipt WHERE receipt_date between '{}' AND '{}'""".format(
start_date, end_date)
elif action == 'code':
keyList = key_word.split(',')
trainingCenter = keyList[0]
year = keyList[1]
sn = keyList[2]
sqlStr = """SELECT * FROM receipt WHERE 1"""
if year:
sqlStr += ' AND year = %s' % year
if trainingCenter:
sqlStr += " AND training_center LIKE '%s%%%%'" % trainingCenter
if sn:
sqlStr += " AND serial_number = %s" % sn
else:
sqlStr = """SELECT * FROM receipt WHERE {} LIKE '{}%%'""".format(
action, key_word)
self.result = sqlInstance.execSql(sqlStr)
return self.search_result()
else:
return self.template()
def __call__(self):
request = self.request
id = request.get('id')
type = request.get('type')
sqlInstance = SqlObj()
sqlStr = """SELECT * FROM signature WHERE id = {}""".format(id)
result = sqlInstance.execSql(sqlStr)[0]
obj = dict(result)
parameter = {}
sn = obj['year']
for i in range(4 - len(str(obj['code']))):
sn = '0' + str(sn)
parameter['sn'] = safe_unicode(sn)
parameter['year'] = safe_unicode(obj['date'].year)
parameter['month'] = safe_unicode(obj['date'].month)
parameter['day'] = safe_unicode(obj['date'].day)
parameter['title'] = safe_unicode(obj['title'])
parameter['detail_1'] = safe_unicode(obj['detail_1'])
parameter['detail_2'] = safe_unicode(obj['detail_2'])
parameter['detail_3'] = safe_unicode(obj['detail_3'])
parameter['detail_4'] = safe_unicode(obj['detail_4'])
parameter['department'] = safe_unicode(obj['department'])
parameter['type'] = safe_unicode(obj['type'])
parameter['category'] = safe_unicode(obj['category'])
if type == '1':
parameter['condition'] = safe_unicode('會辦部門行政服務處')
filePath = '/home/andy/cshm/zeocluster/src/cshm.content/src/cshm/content/browser/static/signature.docx'
doc = DocxTemplate(filePath)
doc.render(parameter)
doc.save("/tmp/temp.docx")
return self.downloadFile('%s_簽呈' % sn)
def __call__(self):
request = self.request
sqlInstance = SqlObj()
uid = self.context.UID()
sqlStr = """SELECT * FROM receipt WHERE uid = '{}' ORDER BY receipt_date""".format(
uid)
self.result = sqlInstance.execSql(sqlStr)
return self.template()
def __call__(self):
request = self.request
id = request.get('id')
status = request.get('status')
sqlInstance = SqlObj()
if status:
sqlStr = """UPDATE signature SET status = '{}' WHERE id = {}""".format(
status, id)
sqlInstance.execSql(sqlStr)
url = api.portal.get().absolute_url() + '/signature_view'
api.portal.show_message(message='更新狀態成功!'.decode(),
request=request)
request.response.redirect(url)
else:
sqlStr = """SELECT * FROM signature WHERE id = {}""".format(id)
self.result = sqlInstance.execSql(sqlStr)
return self.template()
def getAllStudent(self):
uid = self.context.UID()
sqlInstance = SqlObj()
sqlStr = """SELECT *, training_status_code.status FROM `reg_course`, `training_status_code`, education_code WHERE uid = '{}'
and isAlt = 0 and seatNo IS NOT null and reg_course.training_status = training_status_code.id AND
reg_course.education_id = education_code.id ORDER BY reg_course.seatNo""".format(
uid)
return sqlInstance.execSql(sqlStr)
def __call__(self):
request = self.request
portal = api.portal.get()
execSql = SqlObj()
now = datetime.datetime.now()
prepareList = api.content.find(
context=portal['language_study'],
portal_type='Prepare',
start_date={
'query': (datetime.date(now.year, now.month, now.day),
datetime.date(now.year, now.month, now.day)),
'range':
'min: max'
})
# prepareList = [api.content.get(UID='e9a28fd812014e849e5f4868168e3f4a')]
for brain in prepareList:
obj = brain.getObject()
# for obj in prepareList:
uid = obj.UID()
sqlStr = "SELECT name FROM attend WHERE uid = '{}'".format(uid)
attend = execSql.execSql(sqlStr)
attend = [i[0] for i in attend]
parentUid = obj.getParentNode().UID()
sqlStr = "SELECT * FROM student WHERE uid = '{}' AND verify = 1".format(
parentUid)
student = execSql.execSql(sqlStr)
date = now.strftime('%Y-%m-%d')
for item in student:
temp = dict(item)
name = temp['name']
county = temp['county']
school = temp['school']
course = '%s%s' % (temp['course'], temp['language'])
if name not in attend:
sqlStr = """INSERT INTO attend(name, date, status, course, county, school, uid) VALUES('{}', '{}', '{}', '{}', '{}',
'{}', '{}')""".format(name, date, 'notOnCall',
course, county, school, uid)
execSql.execSql(sqlStr)
def __call__(self):
request = self.request
user_id = request.get('user_id')
sqlInstance = SqlObj()
sqlStr = """SELECT reg_course.*,training_status_code.status FROM reg_course,training_status_code WHERE reg_course.id = '{}' AND
reg_course.training_status = training_status_code.id""".format(
user_id)
self.result = sqlInstance.execSql(sqlStr)
return self.template()
def __call__(self):
request = self.request
receipt_id = request.get('receipt_id')
if receipt_id:
sqlInstance = SqlObj()
sqlStr = """SELECT * FROM receipt WHERE id = {}""".format(
receipt_id)
self.result = sqlInstance.execSql(sqlStr)
self.receipt_id = receipt_id
return self.template()
def __call__(self):
request = self.request
receipt_id = request.get('receipt_id')
sqlInstance = SqlObj()
sqlStr = """SELECT * FROM receipt WHERE id = {}""".format(receipt_id)
self.result = sqlInstance.execSql(sqlStr)
self.trainingCenter = self.context.trainingCenter.to_object.title
return self.template()
def __call__(self):
self.portal = api.portal.get()
context = self.context
uid = context.UID()
sqlInstance = SqlObj()
# 正取名單
sqlStr = """SELECT reg_course.*,receipt_money.money FROM reg_course LEFT JOIN receipt_money ON receipt_money.user_id = reg_course.id
WHERE reg_course.uid = '{}' and isAlt = 0 and on_training = 1 ORDER BY reg_course.seatNo""".format(
uid)
self.admit = sqlInstance.execSql(sqlStr)
sqlStr = """SELECT id,user_id FROM receipt WHERE is_cancel = 0"""
receipt = sqlInstance.execSql(sqlStr)
idDict = {}
for item in receipt:
idList = item[1].split(',')
for id in idList:
if id:
idDict[id] = item[0]
self.idDict = idDict
return self.template()
def __call__(self):
context = self.context
portal = api.portal.get()
uid = context.UID()
request = self.request
execSql = SqlObj()
execStr = """SELECT * FROM material WHERE uid = '%s'""" % uid
order = execSql.execSql(execStr)
orderList = []
totalMaterialList = {}
if order:
for item in order:
tmp = dict(item)
status = tmp['status']
apply_time = tmp['apply_time']
order_id = tmp['id']
logistics_code = tmp['logistics_code']
send_date = tmp['send_date']
action = tmp['action']
orderList.append([
status, apply_time, order_id, logistics_code, send_date,
action
])
# 切割detail，判斷action
for detail in tmp['detail'].split('/'):
if detail:
name = detail.split('*')[0]
amount = int(detail.split('*')[1])
if action == 'return':
amount = amount * -1
if totalMaterialList.has_key(name):
totalMaterialList[name] += amount
else:
totalMaterialList[name] = amount
self.orderList = orderList
self.totalMaterialList = totalMaterialList
materialList = []
availableMaterial = context.availableMaterial
# 抓此期別底下可以被新增的教材
for materialUID in availableMaterial:
content = api.content.get(UID=materialUID)
title = content.title
price = content.price
discountPrice = content.discountPrice
unit = content.unit
finalPrice = discountPrice if discountPrice else price
materialList.append([title, finalPrice, unit])
self.materialList = materialList
return self.template()
def __call__(self):
context = self.context
execSql = SqlObj()
data = []
abs_url = api.portal.get().absolute_url()
context_uid = context.UID()
execStr = "SELECT COUNT(id) FROM `reg_course` WHERE uid = '%s'" % context_uid
totalNumber = execSql.execSql(execStr)[0][0]
for child in context.getChildNodes():
child_uid = child.UID()
execStr = "SELECT seat FROM satisfaction WHERE uid = '%s' ORDER BY seat" % child_uid
seatList = execSql.execSql(execStr)
seatStr = ''
for seat in seatList:
seatStr += str(seat[0]) + ','
content = api.content.get(UID=child_uid)
subjectName = content.title
teacher = content.teacher.to_object.title
startDateTime = content.startDateTime.strftime('%Y-%m-%d %H:%M:%S')
if totalNumber:
rate = round(float(len(seatList)) / float(totalNumber),
1) * 100
else:
rate = '無人報名'
data.append([subjectName, seatStr, startDateTime, teacher, rate])
url = "%s/check_survey" % (context.absolute_url())
# 製作qrcode
qr = qrcode.QRCode()
qr.add_data(url)
qr.make_image().save('url.png')
img = open('url.png', 'rb')
b64_img = base64.b64encode(img.read())
self.url = url
self.b64_img = b64_img
self.data = data
return self.template()
def __call__(self):
request = self.request
year = request.get('year')
category = request.get('category')
item = request.get('item')
kw = request.get('kw')
sqlInstance = SqlObj()
if year and category:
sqlStr = """SELECT * FROM signature WHERE year = {} and category = '{}' ORDER BY code DESC""".format(
year, category)
self.result = sqlInstance.execSql(sqlStr)
return self.template2()
elif item and kw:
sqlStr = """SELECT * FROM signature WHERE {} = '{}' ORDER BY code DESC""".format(
item, kw)
self.result = sqlInstance.execSql(sqlStr)
return self.template2()
else:
sqlStr = """SELECT DISTINCT(year) FROM `signature`"""
self.yearList = sqlInstance.execSql(sqlStr)
return self.template()
def __call__(self):
request = self.request
sample_name = request.get('sample_name')
category = request.get('category')
title = request.get('title')
detail_1 = request.get('detail_1')
detail_2 = request.get('detail_2')
detail_3 = request.get('detail_3')
detail_4 = request.get('detail_4')
user = api.user.get_current().getUserName()
if sample_name:
sqlInstance = SqlObj()
sqlStr = """INSERT INTO signature_sample(`sample_name`, `user`, `category`, `title`, `detail_1`, `detail_2`, `detail_3`,
`detail_4`) VALUES('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')""".format(
sample_name, user, category, title, detail_1, detail_2,
detail_3, detail_4)
sqlInstance.execSql(sqlStr)
api.portal.show_message(message='新增範本成功!'.decode(),
request=request)
abs_url = api.portal.get().absolute_url()
request.response.redirect(abs_url + '/signature_view')
else:
return self.template()
def __call__(self):
try:
request = self.request
money = int(request.get('money'))
user_id = int(request.get('user_id'))
uid = self.context.UID()
path = self.context.absolute_url_path()
sqlInstance = SqlObj()
sqlStr = """SELECT id FROM receipt_money WHERE user_id = {}""".format(
user_id)
check = sqlInstance.execSql(sqlStr)
if check:
sqlStr = """UPDATE receipt_money SET money = {} WHERE user_id = {}""".format(
money, user_id)
sqlInstance.execSql(sqlStr)
else:
sqlStr = """INSERT INTO receipt_money(user_id, money, uid, path) VALUES({}, {}, '{}', '{}')
""".format(user_id, money, uid, path)
sqlInstance.execSql(sqlStr)
return 'success'
except Exception as e:
print e
return 'error'
def __call__(self):
request = self.request
material_detail = request.get('detail')
send_date = request.get('send_date')
remark = request.get('remark')
address = request.get('address')
action = request.get('action')
context = self.context
uid = context.UID()
course = context.getParentNode().title
period = context.id
context_url = context.absolute_url()
organizer = api.user.get_current().getUserName()
# 做新增及退還
execSql = SqlObj()
execStr = """INSERT INTO `material`( `course`, `period`, `uid`, `status`, `send_date`, `logistics_code`, `detail`, `remark`,
`organizer`, `address`, `action`) VALUES('{}','{}','{}','{}','{}','{}','{}','{}', '{}', '{}', '{}')
""".format(course, period, uid, '處理中(寫死)', send_date, ' ',
material_detail, remark, organizer, address,
action)
execSql.execSql(execStr)
request.response.redirect(context_url + '/@@material_view')
def __call__(self):
request = self.request
receipt_id = request.get('receipt_id')
if receipt_id:
sqlInstance = SqlObj()
now = datetime.datetime.now().strftime('%Y-%m-%d')
user_name = api.user.get_current().getUserName()
time = request.get('time')
if time == 'one':
check = 'one_check'
date = 'one_check_date'
elif time == 'two':
check = 'two_check'
date = 'two_check_date'
sqlStr = """UPDATE receipt SET {} = 1,{} = '{}',inspector = '{}' WHERE id = {}
""".format(check, date, now, user_name, receipt_id)
self.result = sqlInstance.execSql(sqlStr)
request.response.redirect('%s/@@admin_receipt_list' %
self.context.absolute_url())
def __call__(self):
request = self.request
userList = request.get("userList", "")
if userList:
userList = json.loads(userList)
if type(userList) == int:
userList = [userList, 'aa']
sqlInstance = SqlObj()
sqlStr = """SELECT name,tax_no,company_name,receipt_money.money FROM reg_course LEFT JOIN receipt_money ON receipt_money.user_id =
reg_course.id WHERE reg_course.id in {}""".format(
tuple(userList))
result = sqlInstance.execSql(sqlStr)
title = ''
totalMoney = 0
for item in result:
name = item[0]
self.tax_no = item[1] if item[1] != 'None' and item[1] else ''
money = item[3]
company_name = item[2]
if money:
totalMoney += money
if company_name:
title = company_name
elif title:
title += ',%s' % name
else:
title = name
self.title = title
self.totalMoney = totalMoney
try:
userList.remove('aa')
except:
pass
self.userList = userList
return self.template()
else:
return self.empty_template()
def __call__(self):
request = self.request
order_id = request.get('order_id')
execSql = SqlObj()
context = self.context
execStr = """SELECT * FROM material WHERE id = %s""" % order_id
self.result = execSql.execSql(execStr)
availableMaterial = context.availableMaterial
materialList = {}
for materialUID in availableMaterial:
content = api.content.get(UID=materialUID)
title = content.title
price = content.price
discountPrice = content.discountPrice
unit = content.unit
finalPrice = discountPrice if discountPrice else price
materialList[title] = [finalPrice, unit]
self.materialList = materialList
return self.template()
def __call__(self):
portal = api.portal.get()
echelonBrain = api.content.find(context=portal['mana_course'],
portal_type='Echelon')
nowDate = datetime.datetime.now().date()
data = {}
execSql = SqlObj()
for item in echelonBrain:
obj = item.getObject()
courseStart = obj.courseStart
if courseStart and courseStart >= nowDate:
trainingCenter = str(obj.trainingCenter.to_object.title)
period = obj.id
name = str(obj.getParentNode().title)
uid = obj.UID()
execStr = """SELECT COUNT(id), organizer FROM `material` WHERE uid = '%s' GROUP BY organizer""" % uid
result = execSql.execSql(execStr)
# 若result為空代表尚未請領
times = result[0][0] if result else '尚未請領'
organizer = result[0][1] if result else ''
contentUrl = obj.absolute_url()
courseStart = courseStart.strftime('%Y-%m-%d')
if data.has_key(trainingCenter):
data[trainingCenter].append([
name, period, courseStart, organizer, times, contentUrl
])
else:
data[trainingCenter] = [[
name, period, courseStart, organizer, times, contentUrl
]]
for k, v in data.items():
data[k] = sorted(v, key=lambda x: x[2])
self.data = data
self.trainingCenterBrains = api.content.find(
portal['resource']['training_center'],
portal_type='TrainingCenter')
return self.template()
def __call__(self):
request = self.request
keyword = request.get('keyword')
language = request.get('language')
if keyword or language:
execSql = SqlObj()
if keyword and language:
sqlStr = """SELECT * FROM youtube WHERE keyword LIKE '%%{}%%' AND language LIKE '%%{}%%'""".format(
keyword, language)
elif keyword:
sqlStr = """SELECT * FROM youtube WHERE keyword LIKE '%%{}%%'""".format(
keyword)
elif language:
sqlStr = """SELECT * FROM youtube WHERE language LIKE '%%{}%%'""".format(
language)
else:
self.result = []
return self.template2()
self.result = execSql.execSql(sqlStr)
return self.template2()
else:
return self.template()
def __call__(self):
sqlInstance = SqlObj()
sqlStr = """SELECT * FROM signature ORDER BY code desc LIMIT 50"""
self.result = sqlInstance.execSql(sqlStr)
return self.template()
def __call__(self):
request = self.request
order_id = request.get('order_id', '')
uid = self.context.UID()
type = request.get('type')
execSql = SqlObj()
if type == 'shipment':
execStr = """SELECT * FROM material WHERE id = '%s'""" % order_id
self.result = execSql.execSql(execStr)
return self.template_shipment()
elif type == 'return':
execStr = """SELECT * FROM material WHERE id = '%s'""" % order_id
self.result = execSql.execSql(execStr)
return self.template_return()
elif type == 'material':
execStr = """SELECT * FROM material WHERE uid = '%s'""" % uid
result = execSql.execSql(execStr)
organizerList = []
applyTimeList = []
returnList = {}
materialList = {}
availableList = {}
availableMaterial = self.context.availableMaterial
# 抓此期別底下可以被新增的教材
for available in availableMaterial:
content = api.content.get(UID=available)
title = content.title
price = content.price
discountPrice = content.discountPrice
availableList[
title] = discountPrice if discountPrice else price
# 切割detail，判斷action
for item in result:
for material in item[8].split('/'):
name = material.split('*')[0]
if name:
amount = int(material.split('*')[1])
if item[12] == 'return':
if returnList.has_key(name):
returnList[name] += amount
else:
returnList[name] = amount
amount = amount * -1
if materialList.has_key(name):
materialList[name][0] += amount
else:
price = availableList[name]
materialList[name] = [amount, price]
if item[10] not in organizerList:
organizerList.append(item[10])
if item[5] not in applyTimeList:
applyTimeList.append(item[5])
self.returnList = returnList
self.materialList = materialList
self.organizerList = organizerList
self.applyTimeList = applyTimeList
return self.template_material()
def __call__(self):
request = self.request
mode = request.get('mode')
period = request.get('period')
search = request.get('search')
selected_course = request.get('selected_course')
portal = api.portal.get()
execSql = SqlObj()
lang_cata = {
'阿美語': '南勢阿美語 秀姑巒阿美語 海岸阿美語 馬蘭阿美語 恆春阿美語',
'泰雅語': '賽考利克泰雅語 澤敖利泰雅語 汶水泰雅語 萬大泰雅語 四季泰雅語 澤敖利泰雅語',
'賽夏語': '賽夏語',
'邵語': '邵語',
'賽德克語': '都達語 德固達雅語 德魯固語',
'布農語': '卓群布農語 卡群布農語 丹群布農語 巒群布農語 郡群布農語',
'排灣語': '東排灣語 北排灣語 中排灣語 南排灣語',
'魯凱語': '東魯凱語 霧臺魯凱語 大武魯凱語 多納魯凱語 茂林魯凱語 萬山魯凱語',
'太魯閣語': '太魯閣語',
'噶瑪蘭語': '噶瑪蘭語',
'鄒語': '鄒語',
'卡那卡那富語': '卡那卡那富語',
'拉阿魯哇語': '拉阿魯哇語',
'卑南語': '南王卑南語 知本卑南語 初鹿卑南語 建和卑南語',
'雅美語': '雅美語',
'撒奇萊雅語': '撒奇萊雅語'
}
if search:
course = api.content.find(
context=portal['language_study'][period]['class_intro'],
depth=1,
portal_type='Course')
courseList = []
for i in course:
courseList.append({'title': i.Title, 'id': i.id})
return json.dumps(courseList)
if mode == 'lang':
course = api.content.find(
context=portal['language_study'][period]['class_intro'],
depth=1,
portal_type='Course')
count = {
'阿美語': 0,
'泰雅語': 0,
'賽夏語': 0,
'邵語': 0,
'賽德克語': 0,
'布農語': 0,
'排灣語': 0,
'魯凱語': 0,
'太魯閣語': 0,
'噶瑪蘭語': 0,
'鄒語': 0,
'卡那卡那富語': 0,
'拉阿魯哇語': 0,
'卑南語': 0,
'雅美語': 0,
'撒奇萊雅語': 0
}
total = 0
for i in course:
title = i.Title[6:]
flag = True
for k, v in lang_cata.items():
if title in v.split(' '):
count[k] += 1
total += 1
flag = False
continue
self.count = json.dumps(count)
self.total = total
return self.template2()
elif mode == 'student_rate':
count = {'到課': 0, '缺課': 0}
start = request.get('start')
end = request.get('end')
month = request.get('month')
sqlStr = """SELECT COUNT(status) FROM attend WHERE date """
if start and end:
onCallStr = """BETWEEN '{}' AND '{}' AND status = 'onCall'""".format(
start, end)
notOnCallStr = """BETWEEN '{}' AND '{}' AND status = 'notOnCall'""".format(
start, end)
elif month:
onCallStr = """like '{}' AND status = 'onCall'""".format(
month + '%%')
notOnCallStr = """like '{}' AND status = 'notOnCall'""".format(
month + '%%')
onCall = execSql.execSql('%s %s' % (sqlStr, onCallStr))[0][0]
notOnCall = execSql.execSql('%s %s' % (sqlStr, notOnCallStr))[0][0]
self.start = start
self.end = end
self.month = month
self.result = json.dumps({
'onCall': onCall,
'notOnCall': notOnCall
})
return self.template3()
elif mode == 'notOnCallDetail':
start = request.get('start')
end = request.get('end')
month = request.get('month')
if start and end:
sqlStr = """SELECT * FROM attend WHERE date BETWEEN '{}' AND '{}'""".format(
start, end)
elif month:
sqlStr = """SELECT * FROM attend WHERE date LIKE '{}'""".format(
month + '%%')
result = execSql.execSql(sqlStr)
count = {}
for item in result:
obj = dict(item)
course = obj['course'][6:].encode('utf-8')
status = obj['status']
for k, v in lang_cata.items():
if course in v.split(' '):
if count.has_key(k):
count[k]['出席' if status == 'onCall' else '缺席'] += 1
if status == 'notOnCall':
count[k]['缺席資料'].append(obj)
else:
count[k] = {
'出席': 1 if status == 'onCall' else 0,
'缺席': 1 if status == 'notOnCall' else 0,
'缺席資料': [obj] if status == 'notOnCall' else []
}
continue
self.count = count
return self.template4()
elif mode == 'page_count':
period = request.get('period')
url = 'http://apc2.17study.com.tw/language_study/%s/class_intro/' % period
sqlStr = """SELECT url,count,title FROM page_count WHERE url LIKE '{}%%'""".format(
url)
self.count = execSql.execSql(sqlStr)
return self.template5()
elif mode == 'number':
number_period = request.get('number_period')
sqlStr = """SELECT language FROM student WHERE course like '{}%%' AND verify = 1 AND cancel = 0""".format(
number_period)
result = execSql.execSql(sqlStr)
count = {
'阿美語': 0,
'泰雅語': 0,
'賽夏語': 0,
'邵語': 0,
'賽德克語': 0,
'布農語': 0,
'排灣語': 0,
'魯凱語': 0,
'太魯閣語': 0,
'噶瑪蘭語': 0,
'鄒語': 0,
'卡那卡那富語': 0,
'拉阿魯哇語': 0,
'卑南語': 0,
'雅美語': 0,
'撒奇萊雅語': 0
}
for lang in result:
for k, v in lang_cata.items():
if lang[0].encode('utf-8') in v.split(' '):
count[k] += 1
continue
self.count = count
self.total = len(result)
return self.template6()
elif mode == 'satisfaction':
sqlStr = """SELECT anw1, anw2, anw3, anw4, anw5 FROM satisfaction"""
result = execSql.execSql(sqlStr)
count = {
'anw1': {
'非常滿意': 0,
'滿意': 0,
'普通': 0,
'不滿意': 0,
'非常不滿意': 0
},
'anw2': {
'非常滿意': 0,
'滿意': 0,
'普通': 0,
'不滿意': 0,
'非常不滿意': 0
},
'anw3': {
'非常滿意': 0,
'滿意': 0,
'普通': 0,
'不滿意': 0,
'非常不滿意': 0
},
'anw4': {
'非常滿意': 0,
'滿意': 0,
'普通': 0,
'不滿意': 0,
'非常不滿意': 0
},
'anw5': {
'非常滿意': 0,
'滿意': 0,
'普通': 0,
'不滿意': 0,
'非常不滿意': 0
},
}
for item in result:
obj = dict(item)
anw1 = obj['anw1'].encode('utf-8')
anw2 = obj['anw2'].encode('utf-8')
anw3 = obj['anw3'].encode('utf-8')
anw4 = obj['anw4'].encode('utf-8')
anw5 = obj['anw5'].encode('utf-8')
count['anw1'][anw1] += 1
count['anw2'][anw2] += 1
count['anw3'][anw3] += 1
count['anw4'][anw4] += 1
count['anw5'][anw5] += 1
self.count = json.dumps(count)
return self.template7()
else:
self.brain = api.content.find(portal_type='LiveClass')
return self.template()
if api.is_anonymous():
self.call_powerbi()
def GetSignatureSample(self):
sqlInstance = SqlObj()
user = api.user.get_current().getUserName()
sqlStr = """SELECT * FROM `signature_sample` WHERE user = '******' or user is NULL """.format(
user)
return sqlInstance.execSql(sqlStr)
