selenium_action/wechat_action/sql_tools.py

import json


# def save_ad_layout_result(layout_info, table_layout):
#     insert_layout = table_layout.insert()
#     insert_layout = insert_layout.values \
#         (id=layout_info['id'],
#          userid=layout_info['userid'],
#          result=layout_info['result'],
#          layout=layout_info['layout'])
#     return insert_layout


def save_wechat_cookies(wechat_cookies_info, table_wechat_cookies):
    insert_wechat_cookies = table_wechat_cookies.insert()
    insert_layout = insert_wechat_cookies.values \
        (user_id=wechat_cookies_info['user_id'],
         cookies=wechat_cookies_info['cookies'],
         scan_action=wechat_cookies_info['scan_action'],
         wechat_id=wechat_cookies_info['wechat_id']
         )
    return insert_layout


def save_human_info(human_info, table_human):
    insert_human = table_human.insert()
    insert_human = insert_human.values \
        (service_name=human_info['service_name'],
         wechat_name=human_info['wechat_name'],
         human_info=human_info['human_info']
         )
    return insert_human


def save_layout_typesetting_info(layout_typesetting_info, table_layout_typesetting):
    insert_layout_typesetting = table_layout_typesetting.insert()
    insert_layout_typesetting = insert_layout_typesetting.values \
        (typesetting=layout_typesetting_info['typesetting'],
         user_id=layout_typesetting_info['user_id'],
         name=layout_typesetting_info['name']
         )
    return insert_layout_typesetting


def save_ad_plan_typesetting_info(ad_plan_typesetting_info, table_ad_plan_typesetting):
    # TODO:正常action_record  返回plan_name layout_name有问题
    insert_ad_plan_typesetting = table_ad_plan_typesetting.insert()
    insert_ad_plan_typesetting = insert_ad_plan_typesetting.values \
        (typesetting=ad_plan_typesetting_info['typesetting'],
         user_id=ad_plan_typesetting_info['user_id'],
         name=ad_plan_typesetting_info['name']
         )
    return insert_ad_plan_typesetting


def save_wechat_info(wechat_info, table_wechat):
    insert_wechat = table_wechat.insert()
    insert_wechat = insert_wechat.values \
        (service_name=wechat_info['service_name'],
         wechat_name=wechat_info['wechat_name'],
         appid=wechat_info['appid'],
         wxname=wechat_info['wxname'],
         user_id=wechat_info['user_id']
         )
    return insert_wechat


def save_action_record(action_record_info, table_action_record):
    # TODO:service_name ,wechat_name,action_type 设置全局唯一
    insert_action_record = table_action_record.insert()
    insert_action_record = insert_action_record.values \
        (service_name=action_record_info['service_name'],
         wechat_name=action_record_info['wechat_name'],
         user_id=action_record_info['user_id'],
         object_name=action_record_info['object_name'],
         action_type=action_record_info['action_type'],
         status=action_record_info['status'],
         task_name=action_record_info['task_name'],
         result=action_record_info['result'] if 'result' in action_record_info.keys() else None
         )
    return insert_action_record


def delete_wechat_info(sql_session, user_id):
    sql = '''
    delete from wechat_info 
        where user_id = '{}' 
    '''.format(user_id)
    sql_session.execute(sql)
    sql_session.commit()


def get_human_info(sql_session, service_name, wechat_name):
    sql = '''
        select human_info from human_info hi 
        where service_name='{service_name}' and wechat_name='{wechat_name}' ;
    '''.format(service_name=service_name, wechat_name=wechat_name)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result_list = lines[0][0]
    return result_list


def get_scan_action_status(user_id, sql_session):
    # 扫码成功 done 扫码失败 error(doing ,超过一分钟) 扫码中 doing
    sql = '''select timestampdiff(minute ,update_time ,current_timestamp ) as diff_time,
        scan_action from wechat_cookies wc 
        where user_id ='{}'
    '''.format(user_id)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    if len(lines):
        diff_time, scan_action = lines[0]
        if scan_action == 'done':
            return 'done'
        elif scan_action == 'doing':
            if diff_time > 1:
                return 'error'
            else:
                return 'doing'
    return 'error'


def get_layout_typesetting_rough(sql_session, user_id, typesetting_name):
    sql = '''
            select typesetting,name,create_time,update_time from layout_typesetting lt 
            where user_id ='{}' and is_delete=0 and name like '%{}%';
    '''.format(user_id, typesetting_name)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result_list = [line for line in lines]
    return result_list


def get_layout_typesetting(sql_session, user_id, typesetting_name):
    sql = '''
            select typesetting from layout_typesetting lt 
            where user_id ='{}' and is_delete=0 and name='{}' ;
    '''.format(user_id, typesetting_name)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    if lines:
        result_list = lines[0][0]
        return result_list


def delete_layout_typesetting_vir(sql_session, user_id, typesetting_name):
    sql = '''
            update layout_typesetting lt 
            set is_delete=1
            where user_id ='{}' and name = '{}';
    '''.format(user_id, typesetting_name)
    sql_session.execute(sql)
    sql_session.commit()


def get_plan_typesetting_rough(sql_session, user_id, typesetting_name):
    sql = '''
            select typesetting,name,create_time,update_time from ad_plan_typesetting lt 
            where user_id ='{}'  and name like '%{}%';
    '''.format(user_id, typesetting_name)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result_list = [line for line in lines]
    return result_list


def get_ad_plan_typesetting(sql_session, user_id, typesetting_name):
    sql = '''
            select typesetting from ad_plan_typesetting lt 
            where user_id ='{}' and name='{}' ;
    '''.format(user_id, typesetting_name)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    if lines:
        result_list = lines[0][0]
        return result_list


def delete_ad_plan_typesetting_vir(sql_session, user_id, typesetting_name, wechat_name, service_name):
    sql = '''
            update action_record lt 
            set is_delete=1
            where action_type='create_ad_plan' and
             user_id ='{user_id}' and object_name = '{name}' and 
            wechat_name='{wechat_name}' and service_name='{service_name}';
    '''.format(user_id=user_id, name=typesetting_name,
               service_name=service_name, wechat_name=wechat_name)
    sql_session.execute(sql)
    sql_session.commit()


def get_plan_record(sql_session, user_id, service_name, wechat_name,
                    status, plan_name):
    other_info = ''
    if service_name:
        other_info = other_info + '\n and service_name="{}" \n'.format(service_name)
    if wechat_name:
        other_info = other_info + '\n and wechat_name="{}" \n'.format(wechat_name)
    if status:
        other_info = other_info + '\n and status="{}" \n'.format(status)
    if plan_name:
        other_info = other_info + '\n and typesetting_list.name="{}" \n'.format(plan_name)

    sql = '''
        select foo.*,concat(wechat_info.appid,'\n',wechat_info.wxname ) as wechat_id_info
         from (select typesetting_list.user_id , typesetting_list.name,
        record_list.service_name ,record_list.wechat_name,
        typesetting_list.create_time,record_list.status,
        typesetting_list.typesetting 
        from 
        ad_plan_typesetting as typesetting_list 
        left join
        action_record as record_list
        on object_name=name
        where typesetting_list.user_id ='{user_id}'
        and record_list.is_delete = 0
        and record_list.status in ('done','error')
        {other_info}
        ) as foo left join wechat_info 
        on foo.service_name =wechat_info.service_name and foo.wechat_name = wechat_info.wechat_name ;
    '''.format(user_id=user_id, other_info=other_info)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result = [line for line in lines]
    return result


def get_undo_action(sql_session, user_id):
    # TODO:sql 里面添加doing,error状态的挑选
    sql = '''
        select action_type ,wechat_name ,service_name 
            from action_record 
        where user_id='{}' and status ='todo' ;
    '''.format(user_id)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result_list = [line for line in lines]
    return result_list


def get_task_in_hand_num(user_id, sql_session):
    sql = '''
    select count(*) from 
    action_record ar 
    where timestampdiff(minute ,update_time ,current_timestamp )<60
    and status ='todo' and user_id='{}'
    '''.format(user_id)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result = lines[0][0]
    return result


def get_task_in_hand_limit_one(user_id, sql_session):
    sql = '''
    select foo.typesetting,foo.wechat_name ,foo.service_name ,foo.action_type ,foo2.task_name from 
    (select ad.typesetting,ar.wechat_name,ar.action_type ,ar.service_name ,
    ar.task_name from action_record ar join ad_plan_typesetting ad
    on ar.object_name = ad.name 
    where action_type in ('create_ad_plan' ,'refresh_wechat_info')
    and status ='todo'
    and timestampdiff(minute ,ar.update_time ,current_timestamp )<60
    and ad.user_id ='{user_id}') as foo
    join
    (select task_name from action_record ar 
    where status ='todo' and action_type in ('create_ad_plan' ,'refresh_wechat_info')
    and user_id = '{user_id}'
    order by create_time 
    limit 1) as foo2 on foo.task_name=foo2.task_name
    '''.format(user_id=user_id)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    return lines


def update_task_status_error(sql_session, user_id, task_name):
    sql = '''
    update action_record 
    set status ='error'
    where status ='todo' and user_id ='{}' and task_name ='{}'
    '''.format(user_id, task_name)
    sql_session.execute(sql)
    sql_session.commit()


def update_user_scan_action(user_id, sql_session):
    sql = '''update wechat_cookies 
            set scan_action ='doing',update_time=current_timestamp
            where user_id ='{}' '''.format(user_id)
    sql_session.execute(sql)
    sql_session.commit()


def get_wechat_id_from_cookies(user_id, sql_session):
    sql = '''
    select wechat_id from wechat_cookies wc 
    where user_id ='{}'
    '''.format(user_id)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    wechat_id = None
    if lines:
        wechat_id = lines[0][0]
    return wechat_id


def get_ad_task(sql_session, user_id):
    sql = '''
    select task_name,status,count(*),min(ar.create_time),apt.typesetting from action_record ar
        join ad_plan_typesetting apt on ar.object_name =apt.name
		where ar.user_id='{}'
        and action_type ='create_ad_plan'
        group by task_name ,status ; 
    '''.format(user_id)

    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result_list = [line for line in lines]
    return result_list


def get_ad_status(sql_session, user_id):
    sql = '''
       select  action_type ,wechat_name ,service_name,max(update_time ),max(create_time),status from action_record  
       where user_id='{}'
group by  action_type ,wechat_name ,service_name,status ;
    '''.format(user_id)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result = [line for line in lines]
    return result


def get_action_status(sql_session, user_id):
    # TODO:sql 里面添加doing,error状态的挑选
    sql = '''
        select count(*)
            from action_record 
        where user_id='{}' and status ='doing' ;
    '''.format(user_id)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result = lines[0][0]
    return result


def action_record(res, sql_session, action_type, user_id, object_name, action_table, service_name,
                  wechat_name, task_name):
    status = 'done' if res['sucess'] else 'error'
    update_res = action_table.update() \
        .where(action_table.c.service_name == service_name) \
        .where(action_table.c.wechat_name == wechat_name) \
        .where(action_table.c.user_id == user_id) \
        .where(action_table.c.object_name == object_name) \
        .where(action_table.c.action_type == action_type) \
        .where(action_table.c.task_name == task_name) \
        .where(action_table.c.status == 'todo') \
        .values({
        action_table.c.status: status,
        action_table.c.result: res['result_info']
    })
    # values 添加两个列
    sql_session.execute(update_res)
    sql_session.commit()


def check_layout_alive(sql_session, service_name, wechat_name, layout_name):
    sql = '''
            select count(*) from action_record ar 
        where service_name='{service_name}'
        and wechat_name='{wechat_name}'
        and action_type like '%layout_create%'
        and action_type like '%{layout_name}%'
        and status='done'
    '''.format(service_name=service_name, wechat_name=wechat_name, layout_name=layout_name)
    cursor = sql_session.execute(sql)
    num = cursor.fetchall()[0][0]
    return num


def check_plan_alive(sql_session, service_name, wechat_name, plan_name):
    sql = '''
               select count(*) from action_record ar 
           where service_name='{service_name}'
           and wechat_name='{wechat_name}'
           and action_type like '%ad_plan_create%'
           and action_type like '%{plan_name}%'
           and status='done'
       '''.format(service_name=service_name, wechat_name=wechat_name, plan_name=plan_name)
    cursor = sql_session.execute(sql)
    num = cursor.fetchall()[0][0]
    return num


def get_wechat_info(sql_session, user_id):
    sql = '''
        select service_name ,wechat_name from wechat_info
        where user_id ='{}' ;
    '''.format(user_id)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result_list = [line for line in lines]
    return result_list


def get_wechat_info_service_name(sql_session, user_id):
    sql = '''
        select distinct(service_name) from wechat_info
        where user_id ='{}' ;
    '''.format(user_id)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result_list = [line[0] for line in lines]
    return result_list


def get_wechat_info_wechat_name(sql_session, user_id, service_name):
    sql = '''
        select service_name ,wechat_name from wechat_info
        where user_id ='{}' and service_name='{}';
    '''.format(user_id, service_name)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    result_list = [line for line in lines]
    return result_list


def get_wechat_cookies(sql_session, user_id):
    sql = '''
    select cookies from wechat_cookies where user_id='{}'
    '''.format(user_id)
    cursor = sql_session.execute(sql)
    lines = cursor.fetchall()
    if lines:
        return lines[0][0]