def share_userlist(request,template_name="share_user.html",remcommend_limit=5,limit=18):
'''
分享用户列表
'''
args={}
try:
userProfile=UserProfile.objects.get(user=request.user)
#推荐用户
userProfileList=UserProfile.objects.select_related('user').filter(avatar_name_status='3').exclude(gender=userProfile.gender).exclude(user_id__in=STAFF_MEMBERS)[:remcommend_limit]
userlist=[{'userId':user.user_id,'username':user.user.username,'avatar':user.avatar_name} for user in userProfileList]
userIdList=[str(user['userId']) for user in userlist]
count=len(userProfileList)
sql='''
SELECT %s from third_party_login u1 left join user_profile u4 on u1.user_id =u4.user_id
left join auth_user u2 on u1.user_id=u2.id
where u1.provider='3' and exists (select my_id from weixin_score_rank u3 where u1.user_id=u3.my_id)
and u4.gender !='%s' and u1.user_id not in (1%s)
order by u2.last_login desc %s
'''
#分享人数
count+=connection_to_db(sql%('count(*)',userProfile.gender,','+','.join(userIdList),''))[0][0]
scoreRankList=connection_to_db(sql%('u1.user_id,u2.username,u4.avatar_name',userProfile.gender,','+','.join(userIdList),'limit %s'%(limit)),type=True)
userlist+=[{'userId':user['user_id'],'username':user['username'],'avatar':user['avatar_name']} for user in scoreRankList]
args['userlist']=userlist
args['count']=count
except Exception as e:
logger.exception('完善我对别人打分信息：%s'%(e.message))
args={'result':'error','error_message':(e.message)}
template_name='error.html'
return render(request,template_name,args)
def get_no_read_message_dynamic_list_count(receiverId):
sql = '''
SELECT count(*) from(
SELECT u2.id,u2.sender_id,u3.username as sender_name,u4.avatar_name,u4.avatar_name_status,u1.receiver_id,u2.content,u2.sendTime,u2.type,u1.isRead,
null as friendDynamic_id,null as friendDynamic_content ,null as data
from message_log u1 LEFT JOIN message u2 on u1.message_id=u2.id
LEFT JOIN auth_user u3 on u3.id=u2.sender_id LEFT JOIN user_profile u4 on u4.user_id=u2.sender_id
where isDeletereceiver = False and u1.isRead=False AND receiver_id =%s
UNION
SELECT u3.id,u3.sender_id,u1.username as sender_name,u4.avatar_name,u4.avatar_name_status,%s as receiver_id,u3.content,u3.sendTime,u3.type,0 as isRead,
null as friendDynamic_id,null as friendDynamic_content ,null as data
from message u3 LEFT JOIN auth_user u1 on u1.id=u3.sender_id LEFT JOIN user_profile u4 on u4.user_id=u3.sender_id
where u3.type=0 and u3.id not in (SELECT u4.message_id from message_log u4 where u4.receiver_id=%s) and now()<=u3.expireTime
UNION
SELECT u1.id,u1.reviewer_id as sender_id ,u3.username as sender_name,u4.avatar_name,u4.avatar_name_status,
u1.receiver_id,u1.content,u1.commentTime as sendTime ,5 as type,u1.isRead,
u1.friendDynamic_id,u2.content as friendDynamic_content ,u2.data
from friend_dynamic_comment u1 LEFT JOIN friend_dynamic u2 on u2.id=u1.friendDynamic_id
LEFT JOIN auth_user u3 on u3.id=u1.reviewer_id LEFT JOIN user_profile u4 on u4.user_id=u1.reviewer_id
WHERE u1.receiver_id=%s and u1.isRead=0
UNION
SELECT u1.id,u1.user_id as sender_id ,u3.username as sender_name,u4.avatar_name,u4.avatar_name_status,
u2.publishUser_id as receiver_id,null as content,u1.time as sendTime ,3 as type,u1.isRead,
u1.friendDynamic_id,u2.content as friendDynamic_content ,u2.data
from friend_dynamic_argee u1 LEFT JOIN friend_dynamic u2 on u2.id=u1.friendDynamic_id
LEFT JOIN auth_user u3 on u3.id=u1.user_id LEFT JOIN user_profile u4 on u4.user_id=u1.user_id
WHERE u2.publishUser_id=%s and u1.isRead=0
)s
'''
result = connection_to_db(
sql,
param=[receiverId, receiverId, receiverId, receiverId, receiverId])
return result[0][0]
def get_no_read_messagelog(self,
userId,
first=None,
end=None,
userList=None):
userListSql = ''
if userList is not None:
userListSql = ('WHERE sender_id in (' +
('%s,' * len(userList))[:-1] +
')') % tuple(userList)
sql = '''
select * from(
SELECT u2.id,u2.sender_id,u3.username as sender_name,u4.avatar_name,u4.avatar_name_status,u1.receiver_id,u2.content,u2.sendTime,u2.type,u1.isRead,
null as friendDynamic_id,null as friendDynamic_content ,null as data
from message_log u1 LEFT JOIN message u2 on u1.message_id=u2.id
LEFT JOIN auth_user u3 on u3.id=u2.sender_id LEFT JOIN user_profile u4 on u4.user_id=u2.sender_id
where isDeletereceiver = False and u1.isRead=False AND receiver_id =%s and type=1
UNION
SELECT u3.id,u3.sender_id,u1.username as sender_name,u4.avatar_name,u4.avatar_name_status,%s as receiver_id,u3.content,u3.sendTime,u3.type,0 as isRead,
null as friendDynamic_id,null as friendDynamic_content ,null as data
from message u3 LEFT JOIN auth_user u1 on u1.id=u3.sender_id LEFT JOIN user_profile u4 on u4.user_id=u3.sender_id
where u3.type=0 and u3.id not in (SELECT u4.message_id from message_log u4 where u4.receiver_id=%s) and now()<=u3.expireTime
)s ''' + userListSql + '''
ORDER BY sendTime desc
'''
if first is not None:
sql = sql + ' limit %s , %s' % (first, end)
return connection_to_db(sql, param=[userId, userId, userId], type=True)
def statistic_price(self,**kwargs):
sqlTime=''
if kwargs.get("startTime",'')!='':
sqlTime=' where gmt_payment >= %s and gmt_payment <= '%(kwargs.get("startTime",''),kwargs.get("endTime",''))
sql="""
select sum(u.total_fee) from alipay_dpn u where u.trade_status='TRADE_SUCCESS'
"""+sqlTime
return connection_to_db(sql,param=[],type=True)
def get_no_read_agree_count(self, userId):
sql = '''
SELECT count(*)
from friend_dynamic_argee u1 LEFT JOIN friend_dynamic u2 on u2.id=u1.friendDynamic_id
LEFT JOIN auth_user u3 on u3.id=u1.user_id LEFT JOIN user_profile u4 on u4.user_id=u1.user_id
WHERE u2.publishUser_id=%s and u1.isRead=0
'''
return connection_to_db(sql, param=[userId])[0][0]
def tset_match(request):
sql = '''
SELECT DISTINCT name from facebook_user_info where user_id='M2345620' and `name` LIKE BINARY %s
'''
from util.connection_db import connection_to_db
match_result = connection_to_db(sql, ('%北大%'))
# callback = request.GET.get('callback')
# match_result=["2", [0, 1, 4, 5, 6], {"username": "******", "city": "Hangzhou, China", "game_count": 12, "uid": "100007203789389", "facebookPhotoList": "[{\"pk\": \"1401716753411771\", \"model\": \"third_party_login_app.facebookphoto\", \"fields\": {\"bigPhoto\": \"https://scontent-a.xx.fbcdn.net/hphotos-ash3/t1.0-9/1622000_1401716753411771_999418056_n.jpg\", \"smailPhoto\": \"https://fbcdn-photos-e-a.akamaihd.net/hphotos-ak-ash3/t1/1622000_1401716753411771_999418056_s.jpg\", \"user\": \"100007203789389\", \"description\": \"\"}}, {\"pk\": \"1402746386642141\", \"model\": \"third_party_login_app.facebookphoto\", \"fields\": {\"bigPhoto\": \"https://scontent-a.xx.fbcdn.net/hphotos-prn1/t1.0-9/s720x720/1497566_1402746386642141_544486113_n.jpg\", \"smailPhoto\": \"https://fbcdn-photos-g-a.akamaihd.net/hphotos-ak-prn1/t1/1497566_1402746386642141_544486113_s.jpg\", \"user\": \"100007203789389\", \"description\": \"penguin\"}}, {\"pk\": \"1412908328959280\", \"model\": \"third_party_login_app.facebookphoto\", \"fields\": {\"bigPhoto\": \"https://scontent-a.xx.fbcdn.net/hphotos-ash3/t1/1904078_1412908328959280_1054220465_n.jpg\", \"smailPhoto\": \"https://fbcdn-photos-a-a.akamaihd.net/hphotos-ak-ash3/t1/1904078_1412908328959280_1054220465_s.jpg\", \"user\": \"100007203789389\", \"description\": \"\"}}, {\"pk\": \"1412908335625946\", \"model\": \"third_party_login_app.facebookphoto\", \"fields\": {\"bigPhoto\": \"https://scontent-b.xx.fbcdn.net/hphotos-prn2/t1/1656267_1412908335625946_1404365753_n.jpg\", \"smailPhoto\": \"https://fbcdn-photos-b-a.akamaihd.net/hphotos-ak-prn2/t1/1656267_1412908335625946_1404365753_s.jpg\", \"user\": \"100007203789389\", \"description\": \"\"}}, {\"pk\": \"1412908342292612\", \"model\": \"third_party_login_app.facebookphoto\", \"fields\": {\"bigPhoto\": \"https://scontent-b.xx.fbcdn.net/hphotos-prn2/t1.0-9/1798191_1412908342292612_583950951_n.jpg\", \"smailPhoto\": \"https://fbcdn-photos-h-a.akamaihd.net/hphotos-ak-prn2/t1.0-0/1798191_1412908342292612_583950951_s.jpg\", \"user\": \"100007203789389\", \"description\": \"\"}}, {\"pk\": \"1412908362292610\", \"model\": \"third_party_login_app.facebookphoto\", \"fields\": {\"bigPhoto\": \"https://scontent-a.xx.fbcdn.net/hphotos-prn2/t1.0-9/1796596_1412908362292610_1486033668_n.jpg\", \"smailPhoto\": \"https://fbcdn-photos-c-a.akamaihd.net/hphotos-ak-prn2/t1/1796596_1412908362292610_1486033668_s.jpg\", \"user\": \"100007203789389\", \"description\": \"\"}}]", "smallAvatar": "https://fbcdn-profile-a.akamaihd.net/hprofile-ak-ash3/t1/c0.0.50.50/p50x50/1622000_1401716753411771_999418056_t.jpg", "age": 24, "avatar": "https://fbcdn-profile-a.akamaihd.net/hprofile-ak-ash3/t1/c0.0.80.80/p80x80/1622000_1401716753411771_999418056_a.jpg"}]
json = simplejson.dumps(match_result)
return HttpResponse(json)
def get_no_read_follow_message_count(self, userId):
sql = '''
SELECT count(*)
from message_log u1 LEFT JOIN message u2 on u1.message_id=u2.id
LEFT JOIN auth_user u3 on u3.id=u2.sender_id LEFT JOIN user_profile u4 on u4.user_id=u2.sender_id
where isDeletereceiver = False AND receiver_id =%s and type=2 and isRead=0
'''
return connection_to_db(sql, param=[
userId,
])[0][0]
def get_comment_list(self, userId):
sql = '''
SELECT u1.id,u1.reviewer_id as sender_id ,u3.username as sender_name,u4.avatar_name,u4.avatar_name_status,
u1.receiver_id,u1.content,u1.commentTime as sendTime ,5 as type,u1.isRead,
u1.friendDynamic_id,u2.content as friendDynamic_content ,u2.data
from friend_dynamic_comment u1 LEFT JOIN friend_dynamic u2 on u2.id=u1.friendDynamic_id
LEFT JOIN auth_user u3 on u3.id=u1.reviewer_id LEFT JOIN user_profile u4 on u4.user_id=u1.reviewer_id
WHERE u1.receiver_id=%s
ORDER BY sendTime desc
'''
return connection_to_db(sql, param=[userId], type=True)
def get_agree_List(self, userId):
sql = '''
SELECT u1.id,u1.user_id as sender_id ,u3.username as sender_name,u4.avatar_name,u4.avatar_name_status,
u2.publishUser_id as receiver_id,null as content,u1.time as sendTime ,3 as type,u1.isRead,
u1.friendDynamic_id,u2.content as friendDynamic_content ,u2.data
from friend_dynamic_argee u1 LEFT JOIN friend_dynamic u2 on u2.id=u1.friendDynamic_id
LEFT JOIN auth_user u3 on u3.id=u1.user_id LEFT JOIN user_profile u4 on u4.user_id=u1.user_id
WHERE u2.publishUser_id=%s
ORDER BY time desc
'''
return connection_to_db(sql, param=[userId], type=True)
def get_no_read_private_msessge_count(self, userId):
sql = '''
SELECT count(*) from (
SELECT u3.id
from message u3
where u3.type=0 and u3.id not in (SELECT u4.message_id from message_log u4 where u4.receiver_id=%s) and now()<=u3.expireTime
UNION
SELECT u2.id from message_log u1 LEFT JOIN message u2 on u1.message_id=u2.id
where isDeletereceiver = False and isRead=0 AND receiver_id = %s and type=1
) s
'''
return connection_to_db(sql, param=[userId, userId])[0][0]
def get_follow_message_list(self, userId, isRead, first=None, end=None):
sql = '''
SELECT u2.id,u2.sender_id,u3.username as sender_name,u4.avatar_name,u4.avatar_name_status,u1.receiver_id,u2.content,u2.sendTime,u2.type,u1.isRead,
null as friendDynamic_id,null as friendDynamic_content ,null as data
from message_log u1 LEFT JOIN message u2 on u1.message_id=u2.id
LEFT JOIN auth_user u3 on u3.id=u2.sender_id LEFT JOIN user_profile u4 on u4.user_id=u2.sender_id
where isDeletereceiver = False AND receiver_id =%s and u1.isRead=%s and type=2
ORDER BY sendTime desc
'''
if first is not None:
sql = sql + ' limit %s , %s' % (first, end)
return connection_to_db(sql, param=[userId, isRead], type=True)
def get_message_list_121(self, userId, otherId, first=None, end=None):
sql = '''
SELECT u1.*,u2.*,u3.username as receiverName,u4.username as senderName from message_log u1 LEFT JOIN message u2 on u1.message_id=u2.id
INNER JOIN auth_user u3 on u1.receiver_id=u3.id INNER JOIN auth_user u4 on u2.sender_id=u4.id
where isDeletereceiver = False AND receiver_id in (%s,%s) and sender_id in (%s,%s)
ORDER BY sendTime DESC
'''
if first is not None:
sql = sql + 'limit %s , %s' % (first, end)
return connection_to_db(sql,
param=[userId, otherId, userId, otherId],
type=True)
def get_message_list_with_first_row(self, senderId, receiverId):
sql = ''
if senderId == ADMIN_ID or receiverId == ADMIN_ID:
sql = '''
SELECT * from (
SELECT * from (
SELECT null as id1,3 as receiver_id,null as message_id,null as isDeleteSender,
null as isDeletereceiver ,null as isRead, u3.* ,null as receiverName,u4.username as senderName
from message u3 INNER JOIN auth_user u4 on u4.id=u3.sender_id
where u3.type=0 and u3.id not in (SELECT u4.message_id from message_log u4) and now()<=u3.expireTime
UNION
SELECT u1.*,u2.*,u3.username as receiverName,u4.username as senderName from message_log u1 LEFT JOIN message u2 on u1.message_id=u2.id
INNER JOIN auth_user u3 on u1.receiver_id=u3.id INNER JOIN auth_user u4 on u2.sender_id=u4.id
where (isDeletereceiver = False AND receiver_id in(%s,%s) and sender_id in(%s,%s))
) s
ORDER BY sendTime desc
LIMIT 1,100
)s1
'''
return connection_to_db(
sql,
param=[senderId, receiverId, senderId, receiverId],
type=True)
else:
sql = '''
SELECT * from (
SELECT u1.id as id1,receiver_id, message_id,isDeleteSender,null as isDeletereceiver , isRead,u2.*,u3.username as receiverName,u4.username as senderName
from message_log u1 LEFT JOIN message u2 on u1.message_id=u2.id
INNER JOIN auth_user u3 on u1.receiver_id=u3.id INNER JOIN auth_user u4 on u2.sender_id=u4.id
where (isDeletereceiver = False AND receiver_id in(%s,%s) and sender_id in(%s,%s))
ORDER BY sendTime desc
limit 1,100
)s
'''
return connection_to_db(
sql,
param=[senderId, receiverId, senderId, receiverId],
type=True)
def get_agree_List_by_ids(self, idList, dynamicId):
sql = '''
SELECT u1.id,u1.user_id as sender_id ,u3.username as sender_name,u4.avatar_name,u4.avatar_name_status,
u2.publishUser_id as receiver_id,null as content,u1.time as sendTime ,3 as type,u1.isRead,
u1.friendDynamic_id,u2.content as friendDynamic_content ,u2.data
from friend_dynamic_argee u1 LEFT JOIN friend_dynamic u2 on u2.id=u1.friendDynamic_id
LEFT JOIN auth_user u3 on u3.id=u1.user_id LEFT JOIN user_profile u4 on u4.user_id=u1.user_id
WHERE u2.publishUser_id in (''' + (
'%s,' * len(idList))[:-1] + ''') and u1.user_id in (''' + (
'%s,' * len(idList))[:-1] + ''') and u1.friendDynamic_id=%s
ORDER BY sendTime desc
'''
paramList = idList
paramList.extend(idList)
paramList.append(dynamicId)
return connection_to_db(sql, param=paramList, type=True)
def clean_message_by_ids(self, userId, Ids):
sql = '''
SELECT id
from message u3
where u3.type=0 and u3.id not in (SELECT u4.message_id from message_log u4 where u4.receiver_id=%s ) and now()<=u3.expireTime '''
sql = sql + 'and id in (' + ('%s,' * len(Ids))[:-1] + ')'
paramList = [userId]
paramList.extend(Ids)
ressult = connection_to_db(sql, paramList)
if len(ressult) > 0:
messageLogList = []
for i in ressult:
messageLog = MessageLog(receiver_id=userId,
message_id=i[0],
isRead=True)
messageLogList.append(messageLog)
MessageLog.objects.bulk_create(messageLogList)
MessageLog.objects.filter(receiver_id=userId,
message_id__in=Ids).update(isRead=True)
def get_message_list(self, userId, first=None, end=None):
sql = '''
SELECT * from (
SELECT * from (
SELECT null as id1,%s as receiver_id,null as message_id,null as isDeleteSender,
null as isDeletereceiver ,null as isRead, u3.* ,null as receiverName,u4.username as senderName
from message u3 INNER JOIN auth_user u4 on u4.id=u3.sender_id
where u3.type=0 and u3.id not in (SELECT u4.message_id from message_log u4) and now()<=u3.expireTime
UNION
SELECT u1.*,u2.*,u3.username as receiverName,u4.username as senderName from message_log u1 LEFT JOIN message u2 on u1.message_id=u2.id
INNER JOIN auth_user u3 on u1.receiver_id=u3.id INNER JOIN auth_user u4 on u2.sender_id=u4.id
where (isDeletereceiver = False AND receiver_id = %s and sender_id!=%s)
) s
ORDER BY type,sendTime desc
)s1
GROUP BY sender_id '''
if first is not None:
sql = sql + 'limit %s , %s' % (first, end)
return connection_to_db(sql, param=[userId, userId, userId], type=True)
def messagelog_list_by_userid(self,
senderId,
receiver_id,
first=None,
end=None):
sql = '''
SELECT * from (
SELECT u2.id,u2.sender_id,u3.username as sender_name,u4.avatar_name,u4.avatar_name_status,u1.receiver_id,u2.content,u2.sendTime,u2.type,u1.isRead
from message_log u1 LEFT JOIN message u2 on u1.message_id=u2.id
LEFT JOIN auth_user u3 on u3.id=u2.sender_id LEFT JOIN user_profile u4 on u4.user_id=u2.sender_id
where receiver_id in(%s,%s) and sender_id in(%s,%s) and type=1
) s
ORDER BY sendTime desc
'''
if first is not None:
sql = sql + ' limit %s , %s' % (first, end)
return connection_to_db(
sql,
param=[senderId, receiver_id, senderId, receiver_id],
type=True)
def has_permission_to_del_commment(self, userId, commentId):
sql = '''
SELECT IFNULL(1,0) as 'a' from friend_dynamic_comment u1 LEFT JOIN friend_dynamic u2 on u1.friendDynamic_id=u2.id
where u1.id=%s and ( u1.reviewer_id=%s or u2.publishUser_id=%s )
'''
return connection_to_db(sql, param=[commentId, userId, userId])[0][0]
