QcWebServerGame/data_manage/operate.py

from model.DateUtils import DateUtils
from model.DataBaseUtils import *
from config.common_config import super_auth
import pandas as pd
du = DateUtils()


def user_channel(arg):
    db = MysqlUtils()
    # 返回所有公众号
    sql = "select id,nick_name from t_mp_account where del_flag=0"
    return db.zx.getData_json(sql)


def user_channel_group(arg):
    db = MysqlUtils()
    di = db.zx.get_data_dict("select cast(id as char) ,nick_name from t_mp_account where del_flag=0")
    user_id = arg.get('user_id')
    if user_id:
        sql = f"""SELECT  id,name,channel_ids from channel_group where id in  
               (select channel_group_id from user_channel_group_auth where user_id={user_id})"""
    else:
        # 不传user_id 返回所有组
        sql = "select id,name,channel_ids from channel_group"
    data = db.quchen_text.getData_json(sql)
    for i in data:
        sr =''
        for j in i['channel_ids'].split(','):
            sr+=di[j]+','
        i['channel_names']=sr
    return data


def user(arg):
    db = MysqlUtils()
    # 返回所有用户
    sql = "SELECT user_id,nick_name from t_sys_user where del_flag=0 and status=0"
    return db.zx.getData_json(sql)



def group_add(arg):
    db = MysqlUtils()
    name = arg.get("name")
    ids = arg.get("channel_ids")
    if name and ids:

        sql = "insert into channel_group (name,channel_ids)  values (%s,%s)"
        db.quchen_text.execute(sql,(name,ids))


def group_del(arg):
    db = MysqlUtils()
    id = arg.get("id")

    if id:
        sql = f"delete from  channel_group where id={id}"
        db.quchen_text.execute(sql)



def group_auth_add(arg):
    db = MysqlUtils()
    user_id = arg.get("user_id")
    group_id = arg.get("group_id")
    if user_id and group_id:
        sql = "insert into user_channel_group_auth (user_id,channel_group_id)  values (%s,%s)"
        db.quchen_text.execute(sql, (user_id, group_id))

def group_auth_del(arg):
    db = MysqlUtils()
    user_id = arg.get("user_id")
    group_id = arg.get("group_id")
    if user_id and group_id:
        sql = f"delete from  user_channel_group_auth where user_id={user_id} and channel_group_id={group_id}"
        # print(sql)
        db.quchen_text.execute(sql)

def get_channel(arg):
    db = MysqlUtils()
    user_id= arg.get('user_id')
    name = arg.get('name')
    if user_id in super_auth:
        sql = f"select id,nick_name from t_mp_account where nick_name like '%{name}%' and del_flag=0"
        return db.zx.getData_json(sql)


    op = f" nick_name like '%{name}%'" if name else ''

    sql = f"""select id,nick_name from t_mp_account where {op} and (oper_user_id in 
         (select user_id from t_sys_user_group_relation where group_id=(select id from t_sys_user_group where del_flag=0 and group_user_id={user_id})) or 
         put_user_id in (select user_id from t_sys_user_group_relation where group_id=(select id from t_sys_user_group where del_flag=0 and group_user_id={user_id})))"""
    data1 = db.zx.getData_json(sql)

    sql2 = f"""select GROUP_CONCAT(channel_ids) from user_channel_group_auth a
                     left join channel_group b on a.channel_group_id=b.id
                     where user_id={user_id}"""

    data2 = db.quchen_text.getOne(sql2)

    if data2:
        data2_2 = tuple(data2.split(','))
        sql3 = f"""select id,nick_name from t_mp_account where nick_name like '%{name}%' and id in {str(data2_2)}"""
        data3 = db.zx.getData_json(sql3)
    else:
        data3 = []
    return data1 + data3


def get_pitcher(arg):
    user_id = arg.get('user_id')

    db = MysqlUtils()

    if user_id in super_auth:
           sql = """
                select a.user_id,nick_name from t_sys_user a 
                left join t_sys_user_role c on a.user_id =c.user_id 
                               where a.del_flag=0 and c.role_id in (7,9) group by a.user_id,nick_name"""
           return db.zx.getData_json(sql)


    sql = f"""select  b.user_id,b.nick_name from t_sys_user_group_relation a
               left join t_sys_user b on a.user_id =b.user_id 
            left join t_sys_user_role c on b.user_id =c.user_id 
               where c.role_id in (7,9) and  a.group_id=(select id from t_sys_user_group where del_flag=0 and group_user_id={user_id}) 
            group by b.user_id,b.nick_name
            """

    # print(db.zx.getData_json(sql))

    return db.zx.getData_json(sql)


def get_user_name_by_id(user_id):
    db = MysqlUtils()
    sql = "select nick_name from t_sys_user where user_id="+str(user_id)
    return  db.zx.getOne(sql)


if __name__ == '__main__':
    # print(get_pitcher({'user_id':78}))
    # print(get_user_name_by_id(78))
    user_channel_group({})