def send_message_list(request):
context = {}
user_id = request.user.id
sql = '''
select a.content,c.unit_name,d.name as school_name,a.send_time,
CASE
b.status
WHEN -1 THEN
'取消发送'
WHEN 0 THEN
'未发送'
WHEN 1 THEN
'已发送'
END 'status_name'
from hdkt_jxhd a
INNER join hdkt_jxhd_class b
on a.id = b.message_id
INNER join `school_unit_class` c
on c.id = b.unit_id
INNER join `school` d
on c.school_id = d.id
where a.add_user = %s and a.status != -1 and a.type = 1
ORDER BY a.add_time desc
''' % user_id
print(sql)
messages = db.default.fetchall_dict(sql)
for i in messages:
i['send_time'] = from_unixtime(i['send_time'],
str_format='%Y{y}%m{m}%d{d}' +
' %H:%M:%S').format(y='年', m='月', d='日')
context["data"] = messages
return ajax_ok(data=context)
def get_open_status(user_id, sids):
"""
获取学科开通状态
-----------------------
2017-6-19 王晨光
-----------------------
:param user: User
:param sids: [学科ID]
:return: {学科ID: 开通状态(1/0)}
"""
mslist = db.ketang_slave.mobile_subject.filter(
user_id=user_id, subject_id__in=sids).select('subject_id', 'open_date',
'cancel_date',
'pay_type')[:]
data = []
nowt = int(time.time())
msdict = {m.subject_id: m for m in mslist}
for sid in sids:
m = msdict.get(sid)
if m:
status = 1 if nowt >= m.open_date and (
m.cancel_date <= 0
or nowt < m.cancel_date) and m.open_date > 0 else 0
data.append({
'status':
status,
'subject_id':
sid,
'open_date':
from_unixtime(m.open_date),
'cancel_date':
from_unixtime(m.cancel_date) if m.cancel_date > 0 else '',
'pay_type':
m.pay_type,
})
else:
data.append({
'status': 0,
'subject_id': sid,
'open_date': '',
'cancel_date': '',
'pay_type': 0,
})
return data
def get_user_details(user_id, way, pageno, pagesz=10):
"""
获取用户积分明细
-----------------------
王晨光 2016-12-07
-----------------------
:param user_id: 用户ID
:param way: 明细分类(1收入 2支出 0全部)
:param pageno: 页号
:param pagesz: 每页条数
:return: {balance:余额, pagecount:总页数, details:[detail]}
"""
us = db.slave.score_user.get(app_id=APPID, user_id=user_id)
balance = us.score if us else 0
details = db.slave.score_user_detail.filter(app_id=APPID, user_id=user_id)
if way == 1:
details = details.filter(item_no='tea_voucher_bak')
elif way == 2:
details = details.filter(item_no='tea_voucher')
n = details.count()
pagecount = int(math.ceil(1.0 * n / pagesz))
details = details.order_by('-id')[(pageno - 1) * pagesz:pageno * pagesz]
rows = []
for d in details:
item_no = d.item_no
score = d.score
theway = 0 # 0收入 1支出
if item_no == 'tea_voucher':
title = u"支出%s金豆" % score
theway = 1
elif item_no == 'tea_voucher_bak':
title = u"收入%s金豆" % score
row = Struct()
row.id = d.id
row.item_no = d.item_no
row.way = theway
row.title = title
row.score = score
row.remark = d.remark
row.add_date = from_unixtime(d.add_date).strftime('%Y-%m-%d %H:%M')
rows.append(row)
out = {
'balance': balance,
'pageno': pageno,
'pagecount': pagecount,
'details': rows
}
return out
def get_user_orders(user_id, pageno, pagesz):
"""
获取用户订单明细
-----------------------
王晨光 2016-12-07
-----------------------
:param user_id: 用户ID
:param pageno: 页号
:param pagesz: 每页条数
:return: {pagecount:总页数, orders:[订单列表]}
"""
orders = db.slave.score_gift_order.filter(app_id=APPID, user_id=user_id)
n = orders.count()
pagecount = int(math.ceil(1.0 * n / pagesz))
orders = orders.order_by('-id')[(pageno - 1) * pagesz:pageno * pagesz]
gift_ids = [o.gift_id for o in orders]
gifts = db.slave.score_gift.filter(id__in=gift_ids)
giftdict = {g.id: g for g in gifts}
rows = []
for o in orders:
gift = giftdict.get(o.gift_id)
r = Struct()
r.id = o.id
r.order_number = o.order_number
r.gift_id = gift.id if gift else 0
r.gift_name = gift.name if gift else u''
r.gift_img = get_absurl(gift.img_url) if gift else ''
r.num = o.num
r.score = o.score
r.status = o.status
r.add_time = from_unixtime(o.add_date).strftime('%Y-%m-%d %H:%M')
r.update_time = from_unixtime(o.update_date).strftime('%Y-%m-%d %H:%M')
r.express = o.express
rows.append(r)
data = {'pagecount': pagecount, 'orders': rows}
return data
def get_teacher_messages(user, sids, types, page, psize):
"""
获取教师消息列表
--------------------
王晨光 2017-6-7
--------------------
:param user: User
:param sids: [学科ID], 空是全部消息
:param types: [消息类型], 空是全部消息
:param page: 页号
:param psize: 每页条数
:return: [消息], 总数
"""
units = user.units
unit_ids = [u.id for u in units]
if not unit_ids:
return [], 0
unit_ids_s = ','.join(str(i) for i in unit_ids)
unitd = {u.id: u for u in units}
type_cond = ""
if types:
type_cond = "and m.type in (%s)" % ','.join(str(i) for i in types)
subject_cond = ""
nowt = int(time.time())
if sids:
subject_cond = "and m.subject_id in (%s)" % ','.join(
str(i) for i in sids)
sql = """
select m.id, m.type, m.subject_id, m.object_id, m.add_user, m.title, m.content,
m.add_time, group_concat(c.unit_class_id) unit_ids, m.images
from message m
inner join message_class c on c.message_id=m.id and c.unit_class_id in (%s)
where m.status=1 and m.begin_time <= %s and m.add_user=%s %s %s
group by m.begin_time desc
limit %s, %s
""" % (unit_ids_s, nowt, user.id, subject_cond, type_cond,
(page - 1) * psize, psize)
messages = db.slave.fetchall_dict(sql)
for m in messages:
if m.type == 1 and m.images:
# 格式化图片
m.images = map(lambda x: get_absurl(x), json.loads(m.images))
else:
m.images = []
class_ids = m.pop('unit_ids', '')
class_ids = [int(i) for i in class_ids.split(',') if i]
classes = []
for i in class_ids:
unit = unitd.get(i)
if unit:
classes.append({'id': unit.id, 'name': unit.name})
m.units = classes
m.add_time = from_unixtime(m.add_time)
# 计算总数
sql = """
select count(distinct m.id) n
from message m
inner join message_class c on c.message_id=m.id and c.unit_class_id in (%s)
where m.status=1 and m.begin_time <= %s and m.add_user=%s %s %s
""" % (unit_ids_s, nowt, user.id, subject_cond, type_cond)
row = db.slave.fetchone(sql)
total = row[0] if row else 0
return messages, total
def outside_task_info(user, message_id, unit_id):
"""
获取课外活动作业详情
:param user:
:param message_id:
:param unit_id:
:return:
"""
sql = """
select m.id, m.content, m.title, m.begin_time, m.end_time endtime, m.add_user user_id, images from message m
inner join message_class c on m.id = c.message_id
where m.id = %s and c.unit_class_id = %s;
""" % (message_id, unit_id)
msg = db.slave.fetchone_dict(sql)
if not msg:
return
now = int(time.time())
if msg.endtime <= now:
# 更新教师端检查作业状态
call(update_outside_status, user, msg.id, unit_id)
time_sub = now - int(msg.begin_time)
msg.begin_time = from_unixtime(
msg.begin_time) if time_sub >= 24 * 60 * 60 else change_time(time_sub)
msg.end_time = from_unixtime(msg.endtime)
msg.is_upload = 1
if not msg.images:
msg.figure_images = []
msg.images = []
else:
images = is_empty(
map(lambda x: format_outside_url(x, is_figure=False),
json.loads(msg.images)))
msg.figure_images = is_empty(
map(lambda x: format_outside_url(x, is_figure=True),
json.loads(msg.images)))
msg.images = images
stu_ids = user.get_mobile_order_region().select('user_id').\
filter(unit_class_id=unit_id, user_type=1).flat("user_id")[:]
stu_tests = db.slave.outside_task_test.filter(user_id__in=stu_ids, message_id=message_id).\
select("user_id", "media_url", "type", "content", "thumb_up", "add_time").order_by("-thumb_up", "add_time")[:]
stu_tests_map = {i.user_id: i for i in stu_tests}
if user.is_teacher:
msg.user_name = user.real_name
msg.portrait = user.portrait
else:
tea_user = com_user.get_user(msg.user_id)
msg.user_name = tea_user.real_name
msg.portrait = tea_user.portrait
msg.status = 0
if user.id in stu_tests_map or msg.endtime <= now:
msg.status = 1
userd = com_user.get_users(stu_ids)
msg.finish = []
msg.unfinish = []
for user_id, stu_user in userd.iteritems():
stu_test = stu_tests_map.get(user_id)
if not stu_test:
d = dict(real_name=stu_user.real_name, portrait=stu_user.portrait)
msg.unfinish.append(d)
continue
media_url = is_empty(json.loads(stu_test.media_url))
if not media_url:
stu_test.figure_url = []
stu_test.media_url = []
else:
stu_test.media_url = map(lambda x: format_outside_url(x),
media_url)
if stu_test.type == 1:
stu_test.figure_url = map(
lambda x: format_outside_url(x, is_figure=True), media_url)
stu_test.user_name = stu_user.real_name
stu_test.add_time = from_unixtime(stu_test.add_time)
stu_test.portrait = stu_user.portrait
msg.finish.append(stu_test)
msg.finish.sort(key=lambda m: (-m.thumb_up, m.add_time))
return msg
def get_student_messages(user, sids, types, page, psize):
"""
获取学生消息列表
--------------------
王晨光 2017-6-7
--------------------
:param user: User
:param sids: [学科ID]
:param types: [消息类型], 空是全部消息
:param page: 页号
:param psize: 每页条数
:return: [消息], 总数
"""
unit = user.unit
if not unit:
return [], 0
type_cond = ""
if types:
type_cond = "and m.type in (%s)" % ','.join(str(i) for i in types)
subject_cond = ""
if sids:
subject_cond = "and m.subject_id in (%s)" % ','.join(
str(i) for i in sids)
nowt = int(time.time())
sql = """
select m.id, m.type, m.subject_id, m.object_id, m.add_user, m.title, m.content,
m.add_time, m.end_time endtime, m.images, m.begin_time
from message m
inner join message_class c on c.message_id=m.id and c.unit_class_id=%s and (c.student_ids='' or c.student_ids like '%%|%s|%%')
where m.status=1 AND m.begin_time <= %s %s %s
order by m.begin_time desc,m.add_time desc, m.object_id desc
limit %s, %s
""" % (unit.id, user.id, nowt, subject_cond, type_cond,
(page - 1) * psize, psize)
messages = db.slave.fetchall_dict(sql)
for m in messages:
if m.type == 1 and m.images:
# 格式化图片
m.images = map(lambda x: get_absurl(x), json.loads(m.images))
else:
m.images = []
m.units = [{'id': unit.id, 'name': unit.name}]
m.add_time = from_unixtime(m.add_time)
m.end_time = from_unixtime(m.endtime)
m.begin_time = from_unixtime(m.begin_time)
m.is_active = 0
# 补充完成状态
task_messages = [m for m in messages if m.type in (2, 7, 8, 9, 101)]
groups = itertools.groupby(task_messages, lambda x: x.subject_id)
status_d = {}
for subject_id, msgs in groups:
task_ids = [m.object_id for m in msgs]
d = get_task_status(user.id, subject_id, task_ids)
for task_id, status in d.items():
status_d[(subject_id, task_id)] = status
msg_ids = [m.id for m in messages if m.type == 10]
outside_status = get_outside_task_status(user.id, msg_ids)
for m in messages:
if m.type == 10:
# 判断课外作业状态
m.test_status = 1 if m.id in outside_status else 0
if int(time.time()) > m.endtime:
# 如果用户没有提交， 到结束时间用户将不能提交
m.test_status = 1
else:
test_status = status_d.get((m.subject_id, m.object_id)) or 0
if test_status != 1 and int(time.time()) > m.endtime:
test_status = 3
m.test_status = test_status
# 计算总数
sql = """
select count(distinct m.id) n
from message m
inner join message_class c on c.message_id=m.id and c.unit_class_id=%s and (c.student_ids='' or c.student_ids like '%%|%s|%%')
where m.status=1 and m.begin_time <= %s %s %s
""" % (unit.id, user.id, nowt, subject_cond, type_cond)
row = db.slave.fetchone(sql)
total = row[0] if row else 0
return messages, total
def get_user_students(user, unit_id):
"""
获得班级学生列表
----------------------------
方朋 2014-04-15
王浩 2016-??-?? 增加计费状态
王晨光 2017-02-20 加缓存
----------------------------
:param user: 教师User
:param unit_id: 班级ID
:return: {
'student_num': 班级人数,
'open_num': 开通人数,
'unopen_num': 未开通人数,
'trial_count': 试用人数,
'students': [students]
}
"""
if user.is_teacher:
sid = user.sid
students = get_unit_students(user, unit_id)
# 读取学科开通信息
user_ids = [s.user_id for s in students]
mslist = db.ketang_slave.mobile_subject.select(
'phone_number', 'user_id', 'cancel_date',
'1 send_open', 'open_date', 'status').filter(user_id__in=user_ids,
subject_id=sid)[:]
# nowt = int(time.time())
for m in mslist:
# m.status = 2 if nowt >= m.open_date and (
# m.cancel_date <= 0 or nowt < m.cancel_date) and m.open_date > 0 else 0
if int(m.status) in (2, 3): # 待退订和开通 都是开通
m.status = 2
msdict = {ms.user_id: ms for ms in mslist}
for s in students:
ms = msdict.get(s.user_id)
if ms:
s.update(ms)
# 属性解释：
# status：mobile_subject 的id；
# is_status：0表示非暂存；1表示暂存
# send_open：0表示未下发开通请求；1表示已经下发了开通请求
else:
students = []
student_num = unopen_num = open_num = trial_num = 0
for obj in students:
student_num += 1
now = datetime.datetime.now()
obj.open_date = from_unixtime(obj.open_date)
# 计费状态(1体验状态 2计费状态)
billing = 0
# 计费时间为开通时加一个月
billing_date = obj.open_date + datetime.timedelta(
days=31) if obj.open_date else None
if billing_date:
if billing_date > now:
billing = 1
elif billing_date < now:
billing = 2
obj.billing = billing
obj.billing_date = billing_date
if (obj.status == 2 and obj.billing == 1) or obj.status == 9:
trial_num += 1
obj.state = "trial"
if obj.status == 2 and obj.billing == 2:
open_num += 1
obj.state = "open"
if obj.status not in (2, 9):
unopen_num += 1
obj.state = "unopen"
# 同一手机号只有一个显示为开通, 其余显示体验状态
open_phones = set()
for obj in students:
phone = obj.phone_number
if obj.status == 2 and obj.billing == 2:
if phone in open_phones:
obj.status = 9
obj.state = "trial"
trial_num += 1
open_num -= 1
else:
open_phones.add(phone)
data = {
'student_num': student_num,
'open_num': open_num,
'unopen_num': unopen_num,
'trial_count': trial_num,
'students': students
}
return data