Python MethodUtil.close方法代码示例

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import close [as 别名]
def getshopid():
    '''
    获取门店编码
    :return list:
    '''
    conn = mtu.getMysqlConn()
    cur = conn.cursor()
    sql = "select ShopID, ShopName from bas_shop_region"
    cur.execute(sql)
    res = cur.fetchall()
    # 释放
    mtu.close(conn, cur)
    return res

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import close [as 别名]
def report(request):
    pageNum = mtu.getReqVal(request, "pageNum", "1")
    start = mtu.getReqVal(request, "start")
    end = mtu.getReqVal(request, "end")
    ucode = mtu.getReqVal(request, "ucode", "")
    pname = mtu.getReqVal(request, "pname", "")
    try:
        if not start:
            start = (datetime.date.today().replace(day=1)).strftime("%Y-%m-%d")
        if not end:
            end = datetime.datetime.today().strftime("%Y-%m-%d")

        conn = mtu.getMysqlConn()
        cur = conn.cursor()

        condition = " where 1=1 "
        condition += " and createtime BETWEEN '{start} 00:00:00' AND '{end} 23:59:59'"\
                     .format(start=start,end=end)
        if ucode:
             condition += " and ucode like '%{ucode}%'".format(ucode=ucode)
        if pname:
            condition += " and name like '%{pname}%'".format(pname=pname)

        sql = "SELECT DATE_FORMAT(createtime,'%Y-%m-%d') createtime,`name`,url," \
              "SUM(CASE WHEN qtype = 1 THEN 1 ELSE 0 END) views," \
              "SUM(CASE WHEN qtype = 2 THEN 1 ELSE 0 END) downs " \
              "FROM bas_pur_log {condition} GROUP BY DATE_FORMAT(createtime,'%Y-%m-%d'),`name` "\
              "order by DATE_FORMAT(createtime,'%Y-%m-%d') DESC "\
              .format(condition=condition)

        cur.execute(sql)
        rlist = cur.fetchall()
        mtu.close(conn,cur)
    except Exception as e:
        rlist = []
        print(e)

    page = Paginator(rlist, __EACH_PAGE_SHOW_NUMBER, allow_empty_first_page=True).page(int(pageNum))
    result = {"page": page, "pageNum": str(pageNum),"start": start,"end":end,"ucode":ucode,"pname":pname,}
    return render(request, "admin/sysConf_retail_purlog_report.html", result)

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import close [as 别名]

#.........这里部分代码省略.........
            row = listtop[i][key]
            if row == None:
                listtop[i][key] = ''
            else:
                if isinstance(row, decimal.Decimal) and key == 'retrate':
                    listtop[i][key] = str(abs(float("%0.2f" % float(listtop[i][key] * 100)))) + '%'
                elif isinstance(row, decimal.Decimal) and key == 'retsum':
                    listtop[i][key] = abs(float("%0.2f" % float(listtop[i][key])))
                elif isinstance(row, decimal.Decimal):
                    listtop[i][key] = "%0.2f" % float(listtop[i][key])
                else:
                    listtop[i][key] = listtop[i][key]

        # 添加汇总合计
        tempshopsalesum += float(listtop[i]['shopsalesum'])
        tempretsum += float(listtop[i]['retsum'])

        # 添加序号
        listtop[i]['sequenceNumber'] = paiming
        paiming += 1

        sql = "select sdate, shopid, shopsale, ret, ret / shopsale as retc " \
              "from `KGshopretsale` " \
              "where shopid='" + listtop[i]['shopid'] + "' " \
                                                        "and sdate between '" + monfirstday + "' and '" + yesterday + "'"

        cur = conn.cursor()
        cur.execute(sql)
        listdetail = cur.fetchall()

        for item in listdetail:
            date = str(item['sdate'])[8:10]

            if (not item['ret']):
                listtop[i]['retsum_' + date] = 0
            else:
                listtop[i]['retsum_' + date] = abs(float(item['ret']))
            if 'retsum_' + date in listtopTotal:
                listtopTotal['retsum_' + date] += listtop[i]['retsum_' + date]
            else:
                listtopTotal['retsum_' + date] = listtop[i]['retsum_' + date]

            if (not item['shopsale']):
                listtop[i]['shopsalesum_' + date] = 0
            else:
                listtop[i]['shopsalesum_' + date] = float(item['shopsale'])
            if 'shopsalesum_' + date in listtopTotal:
                listtopTotal['shopsalesum_' + date] += listtop[i]['shopsalesum_' + date]
            else:
                listtopTotal['shopsalesum_' + date] = listtop[i]['shopsalesum_' + date]

            if (not item['retc']):
                listtop[i]['retrate_' + date] = 0
            else:
                listtop[i]['retrate_' + date] = str(abs(float("%0.2f" % (item['retc'] * 100)))) + '%'

            # 添加当日汇总
            listtopTotal['retsum_' + date] = float("%0.2f" % (listtopTotal['retsum_' + date]))
            listtopTotal['shopsalesum_' + date] = float("%0.2f" % (listtopTotal['shopsalesum_' + date]))
            listtopTotal['retrate_' + date] = str(
                float("%0.2f" % (listtopTotal['retsum_' + date] / listtopTotal['shopsalesum_' + date] * 100))) + '%'

    # 添加门店名称
    for i in range(0, len(listtop)):
        for j in range(0, len(getshopname)):
            if listtop[i]['shopid'] == getshopname[j]['ShopID']:
                listtop[i]['shopname'] = getshopname[j]['ShopName'].strip()

    # 合计转换数据格式
    listtopTotal['shopsalesum'] = float("%0.2f" % tempshopsalesum)
    listtopTotal['retsum'] = float("%0.2f" % tempretsum)
    listtopTotal['retrate'] = str(
        "%0.2f" % float(float(listtopTotal['retsum'] / listtopTotal['shopsalesum']) * 100)) + '%'

    # 转换为dict，导出excel
    TotalDict = {'listtopTotal': listtopTotal}

    # 退货明细
    sqldetail = "select shopid, shopname, sdate, stime, listno, posid, cashierid, goodsid, goodsname, deptid, amount, sale " \
                "from `KGshopretsaleitem` " \
                "where sdate='" + yesterday + "' order by shopid"
    cur = conn.cursor()
    cur.execute(sqldetail)
    retdetail = cur.fetchall()
    # 格式化数据
    for i in range(0, len(retdetail)):
        for key in retdetail[i].keys():
            row = retdetail[i][key]
            if row == None:
                listtop[i][key] = ''
            else:
                if isinstance(row, decimal.Decimal):
                    retdetail[i][key] = "%0.2f" % float(retdetail[i][key])
                elif isinstance(row, datetime.datetime):
                    retdetail[i][key] = retdetail[i][key].strftime("%Y-%m-%d")
                else:
                    retdetail[i][key] = retdetail[i][key]

    mtu.close(conn, cur)
    return locals()

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import close [as 别名]

#.........这里部分代码省略.........
    getshopname = getshopid()

    conn = mtu.getMysqlConn()
    sqltop = "select shopid, sum(costvalue) as costvaluesum, sum(reth) as rethsum, (sum(reth) / sum(costvalue)) as retrate " \
             "from KGretshop " \
             "where ShopID!='C009' AND sdate between '" + monfirstday + "' and '" + yesterday + "' " \
                                                                                                "group by shopid " \
                                                                                                "order by shopid"

    cur = conn.cursor()
    cur.execute(sqltop)
    listtop = cur.fetchall()
    # 最后一行的合计
    listtopTotal = {'sequenceNumber': '合计', 'shopid': '', 'shopname': '', 'costvaluesum': '', 'rethsum': '',
                    'retrate': ''}
    # 汇总总计（每月1日到当日）
    tempcostvaluesum = 0.00
    temprethsum = 0.00

    # 格式化数据
    paiming = 1
    for i in range(0, len(listtop)):
        for key in listtop[i].keys():
            row = listtop[i][key]
            if row == None:
                listtop[i][key] = ''
            else:
                if isinstance(row, decimal.Decimal) and key == 'retrate':
                    listtop[i][key] = str(float("%0.2f" % float(listtop[i][key] * 100))) + '%'
                elif isinstance(row, decimal.Decimal):
                    listtop[i][key] = "%0.2f" % float(listtop[i][key])
                else:
                    listtop[i][key] = listtop[i][key]

        # 添加序号
        listtop[i]['sequenceNumber'] = paiming
        paiming += 1

        # 添加汇总合计
        tempcostvaluesum += float(listtop[i]['costvaluesum'])
        temprethsum += float(listtop[i]['rethsum'])

        sql = "select sdate, shopid, costvalue, reth, reth / costvalue as ret " \
              "from `KGretshop` " \
              "where shopid='" + listtop[i]['shopid'] + "' " \
                                                        "and sdate between '" + monfirstday + "' and '" + yesterday + "'"
        cur = conn.cursor()
        cur.execute(sql)
        listdetail = cur.fetchall()

        for item in listdetail:
            date = str(item['sdate'])[8:10]

            if (not item['reth']):
                listtop[i]['rethsum_' + date] = 0
            else:
                listtop[i]['rethsum_' + date] = float(item['reth'])
            if 'rethsum_' + date in listtopTotal:
                listtopTotal['rethsum_' + date] += listtop[i]['rethsum_' + date]
            else:
                listtopTotal['rethsum_' + date] = listtop[i]['rethsum_' + date]

            if (not item['costvalue']):
                listtop[i]['costvaluesum_' + date] = 0
            else:
                listtop[i]['costvaluesum_' + date] = float(item['costvalue'])
            if 'costvaluesum_' + date in listtopTotal:
                listtopTotal['costvaluesum_' + date] += listtop[i]['costvaluesum_' + date]
            else:
                listtopTotal['costvaluesum_' + date] = listtop[i]['costvaluesum_' + date]

            if (not item['ret']):
                listtop[i]['retrate_' + date] = 0
            else:
                listtop[i]['retrate_' + date] = str(float("%0.2f" % (item['ret'] * 100))) + '%'

            # 添加当日汇总
            listtopTotal['rethsum_' + date] = float("%0.2f" % (listtopTotal['rethsum_' + date]))
            listtopTotal['costvaluesum_' + date] = float("%0.2f" % (listtopTotal['costvaluesum_' + date]))
            listtopTotal['retrate_' + date] = str(
                float("%0.2f" % (listtopTotal['rethsum_' + date] / listtopTotal['costvaluesum_' + date] * 100))) + '%'

    # 添加门店名称
    for i in range(0, len(listtop)):
        for j in range(0, len(getshopname)):
            if listtop[i]['shopid'] == getshopname[j]['ShopID']:
                listtop[i]['shopname'] = getshopname[j]['ShopName'].strip()

    # 合计转换数据格式
    listtopTotal['costvaluesum'] = "%0.2f" % tempcostvaluesum
    listtopTotal['rethsum'] = "%0.2f" % temprethsum
    tempcandr = "%0.2f" % (temprethsum / tempcostvaluesum)
    listtopTotal['retrate'] = str("%0.2f" % float(float(tempcandr) * 100)) + '%'

    mtu.close(conn, cur)

    # 转换为dict，导出excel
    TotalDict = {'listtopTotal': listtopTotal}

    return locals()

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import close [as 别名]

#.........这里部分代码省略.........
            if row is None:
                rows2[i][key] = ''
            else:
                if isinstance(row, int):
                    rows2[i][key] = str(rows2[i][key])
                elif isinstance(row, decimal.Decimal):
                    rows2[i][key] = "%0.2f" % float(rows2[i][key])
    # 2 食品部类
    lis2 = []
    unit2 = []

    for sid in shopsid:
        i = 0
        for row in rows2:
            if sid['ShopID'] == row['shopid'] and i < 20:
                row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
                row['paiming'] = i + 1
                lis2.append(row)
                i += 1
            else:
                continue
        unit2.append(i)

    # 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
    for i in range(0, len(rows3)):
        for key in rows3[i].keys():
            row = rows3[i][key]
            if row is None:
                rows3[i][key] = ''
            else:
                if isinstance(row, int):
                    rows3[i][key] = str(rows3[i][key])
                elif isinstance(row, decimal.Decimal):
                    rows3[i][key] = "%0.2f" % float(rows3[i][key])
    # 3 用品部类
    lis3 = []
    unit3 = []

    for sid in shopsid:
        i = 0
        for row in rows3:
            if sid['ShopID'] == row['shopid'] and i < 20:
                row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
                row['paiming'] = i + 1
                lis3.append(row)
                i += 1
            else:
                continue
        unit3.append(i)

    # 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
    for x in range(0, len(rows4)):
        for key in rows4[x].keys():
            row = rows4[x][key]
            if row is None:
                rows4[x][key] = ''
            else:
                if isinstance(row, int):
                    rows4[x][key] = str(rows4[x][key])
                elif isinstance(row, decimal.Decimal):
                    rows4[x][key] = "%0.2f" % float(rows4[x][key])

    # 将退货数据过滤，值为负
    rows4filter = []
    for i in range(0, len(rows4)):
        if float(rows4[i]['SaleQty']) < 0:
            continue
        else:
            rows4filter.append(rows4[i])

    # 4 家电部类
    lis4 = []
    templist = []

    for sid in shopsid:
        i = 0
        for row in rows4filter:
            if sid['ShopID'] == row['shopid'] and i < 20:
                row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
                row['paiming'] = i + 1
                lis4.append(row)
                i += 1
            else:
                continue
        templist.append(i)

    # 关闭数据库
    mtu.close(conn, cur10)
    mtu.close(conn, cur11)
    mtu.close(conn, cur12)
    mtu.close(conn, cur13)
    mtu.close(conn, cur14)
    mtu.close(conn, cur15)
    mtu.close(conn, cur16)
    mtu.close(conn, cur17)
    mtu.close(conn, cur2)
    mtu.close(conn, cur3)
    mtu.close(conn, cur4)

    return locals()