# coding:utf-8
from django.db import connection


# 执行django原始sql语句 并返回一个数组对象
def query_json(sql):
    # 获得一个游标(cursor)对象
    cursor = connection.cursor()
    cursor.execute(sql)
    rawData = cursor.fetchall()
    col_names = [desc[0] for desc in cursor.description]
    result = []
    for row in rawData:
        objDict = {}
        # 把每一行的数据遍历出来放到Dict中
        for index, value in enumerate(row):
            objDict[col_names[index]] = value
        result.append(objDict)
    return result


def get_label_json(label_code):
    tblName = "t" + str(label_code).strip(' ')[0:4]
    sql = "SELECT * FROM label." + tblName + f" WHERE code = '{label_code}' ORDER BY tm DESC"
    return query_json(sql)


# 根据标签号取label_id, 没有返回0
def get_label_id(label_code):
    try:
        tblName = "t" + str(label_code).strip(' ')[0:4]
        sql = "SELECT * FROM label." + tblName + f" WHERE code = '{label_code}' ORDER BY tm DESC"
        ret = query_json(sql)
        return int(ret[0]["id"]) if len(ret) > 0 else 0
    except Exception as e:
        raise e


# 根据标签号取label_pk_id, 没有返回0
def get_label_pk_id(label_code):
    try:
        tblName = "t" + str(label_code).strip(' ')[0:4]
        sql = "SELECT * FROM label." + tblName + f" WHERE code = '{label_code}' AND package_id NOT IN (3,4) ORDER BY tm DESC"
        ret = query_json(sql)
        return int(ret[0]["parent_id"]) if len(ret) > 0 else 0
    except Exception as e:
        raise e
