Python MethodUtil.getMysqlConn方法代码示例

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMysqlConn [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 getMysqlConn [as 别名]
def findKxListBySheetId(sheetId):
    try:
        conn = mtu.getMysqlConn()
        cur = conn.cursor()
        sql = """SELECT b.inshopid,b.kno,k.kname,b.kmoney,'扣款(从货款扣)' as kkflag,b.note,k.prtflag
                  FROM  billheadkxitem0 b LEFT JOIN kxd k ON k.kno = b.kno
                  WHERE b.kkflag=1 and b.sheetid='{sheetId}' order by b.inshopid
              """.format(sheetId=sheetId)      #
        cur.execute(sql)
        kxlist = cur.fetchall()
    except Exception as e:
        print(e)
        kxlist = []
    finally:
        cur.close()
        conn.close()
    return kxlist

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMysqlConn [as 别名]
def getallcode():
    '''
    获取所有商品类别编码
    :return:
    '''
    conn = mtu.getMysqlConn()
    cur = conn.cursor()
    sql = "select distinct(deptid) from kwsaletop10"
    cur.execute(sql)
    res = cur.fetchall()
    # 释放
    cur.close()
    lis = []

    for y in res:
        lis.append(y['deptid'])

    return lis

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMysqlConn [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 getMysqlConn [as 别名]
def query(date):
    yesterday = date.strftime("%Y-%m-%d")
    # 查询当月销售
    try:
        sql = "CALL k_d_wholesale ('{start}','{end}') ".format(start=yesterday, end=yesterday)
        conn = mtu.getMysqlConn()
        cur = conn.cursor()
        cur.execute(sql)
        list = cur.fetchall()
        rlist = []
        sumDict = {}
        sum = {"shopid": "合计", "shopnm": "", "tradeprice": 0.0, "tradenumber": 0, "salevalue": 0.0, "discvalue": 0.0,
               "sale": 0.0,
               "costvalue": 0.0, "salegain": 0.0, "gaintx": "", "yhzhanbi": "", "wsalevalue": 0.0, "wcostvalue": 0.0,
               "wsalegain": 0.0, "wgaintx": ""}
        sumDict.setdefault("sum1", sum)

        unsumkey = ["gaintx", "wgaintx"]
        for obj in list:
            if obj['shopid'] != 'C009':
                row = {}
                for key in obj.keys():
                    item = obj[key]
                    newkey = key.lower()
                    if item:
                        if isinstance(item, int) or isinstance(item, decimal.Decimal):
                            if newkey not in unsumkey:
                                row.setdefault(newkey, float(item))
                                sum[newkey] += float(item)
                            else:
                                row.setdefault(newkey, "%0.2f" % item + "%")
                        elif isinstance(item, datetime.datetime):
                            row.setdefault(newkey, item.strftime("%Y-%m-%d"))
                        else:
                            row.setdefault(newkey, item)
                    else:
                        row.setdefault(newkey, "")

                if row["sale"] > 0:
                    yhzhanbi = "%0.2f" % (row["discvalue"] * 100.0 / row["sale"]) + "%"
                else:
                    yhzhanbi = ""

                row.setdefault("yhzhanbi", yhzhanbi)
                rlist.append(row)

        if sum["sale"] > 0:
            sum["gaintx"] = "%0.2f" % (sum["salegain"] * 100.0 / sum["sale"]) + "%"
            sum["yhzhanbi"] = "%0.2f" % (sum["discvalue"] * 100.0 / sum["sale"]) + "%"
        else:
            sum["gaintx"] = ""
            sum["yhzhanbi"] = ""

        if sum["wsalevalue"] > 0:
            sum["wgaintx"] = "%0.2f" % (sum["wsalegain"] * 100.0 / sum["wsalevalue"]) + "%"
        else:
            sum["wgaintx"] = ""

        for key in sum.keys():
            item = sum[key]
            if not isinstance(item, str) and not isinstance(item, int):
                sum[key] = "%0.2f" % item
    except Exception as e:
        print(">>>>>>>>>>>>[异常]", e)
        # 计算月累加合计

    data = {"gslist":rlist,"sumDict":sumDict}
    return data

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMysqlConn [as 别名]
def query(yesterday):
    yearandmon = DateUtil.getyearandmonth()
    # 当前月份第一天
    monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
    # 当前月份最后一天
    monlastday = DateUtil.get_lastday_month()
    # 今天
    today = DateUtil.todaystr()

    # 获取门店信息
    getshopname = getshopid()

    conn = mtu.getMysqlConn()
    sqltop = "select shopid, sum(shopsale) as shopsalesum, sum(ret) as retsum, (sum(ret) / sum(shopsale)) as retrate " \
             "from `KGshopretsale` " \
             "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': '', 'shopsalesum': '', 'retsum': '',
                    'retrate': ''}
    # 汇总总计（每月1日到当日）
    tempshopsalesum = 0.00
    tempretsum = 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(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()
#.........这里部分代码省略.........

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMysqlConn [as 别名]
def query(yesterday):
    yearandmon = DateUtil.getyearandmonth()
    # 当前月份第一天
    monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
    # 当前月份最后一天
    monlastday = DateUtil.get_lastday_month()
    # 今天
    today = DateUtil.todaystr()

    # 获取门店信息
    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
#.........这里部分代码省略.........

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMysqlConn [as 别名]
def query():
    ###门店排名###
    monthFirst = datetime.date.today().replace(day=1)
    today = datetime.datetime.today()

    if (str(today)[8:10] == '01'):
        monthFirst = datetime.date(datetime.date.today().year, datetime.date.today().month - 1, 1)
        today = datetime.date(datetime.date.today().year, datetime.date.today().month, 1) - datetime.timedelta(1)
    todayStr = today.strftime('%y-%m-%d')
    # 获取当月天数
    dayNum = todayStr[-2:]
    monthFirstStr = str(monthFirst)
    conn = mtu.getMysqlConn()
    cur = conn.cursor()

    # 各门店报表数据
    listShops = []
    listShopTotal = []

    # 月份汇总数据
    sqlTop = 'SElECT ShopID,shopname, SUM(qtyz) AS qtyzSum,SUM(qtyl) AS qtylSum,(sum(qtyl) / sum(qtyz)) AS zhonbiSum ' \
             'FROM Kzerostock ' \
             'WHERE ShopID!="C009" AND sdate BETWEEN "' + monthFirstStr + '" AND "' + todayStr + '" GROUP BY ShopID ORDER BY ShopID'
    cur.execute(sqlTop)
    listTop = cur.fetchall()
    # 门店排名合计
    listTotal = {'ShopID': '合计', 'shopname': '', 'qtyzSum': 0}  # 初始化最后一行
    # 转换数据类型并求纵向合计
    for i in range(0, len(listTop)):
        if (not listTop[i]['qtyzSum']):
            listTop[i]['qtyzSum'] = 0
        else:
            listTop[i]['qtyzSum'] = float(listTop[i]['qtyzSum'])
            listTop[i]['qtyzSum'] = round((listTop[i]['qtyzSum']) / int(today.day))
        if 'qtyzSum' in listTotal:
            listTotal['qtyzSum'] += listTop[i]['qtyzSum']
        else:
            listTotal['qtyzSum'] = listTop[i]['qtyzSum']

        if (not listTop[i]['qtylSum']):
            listTop[i]['qtylSum'] = 0
        else:
            listTop[i]['qtylSum'] = float(listTop[i]['qtylSum'])
            listTop[i]['qtylSum'] = round((listTop[i]['qtylSum']) / int(today.day))
        if 'qtylSum' in listTotal:
            listTotal['qtylSum'] += listTop[i]['qtylSum']
        else:
            listTotal['qtylSum'] = listTop[i]['qtylSum']

        if (not listTop[i]['zhonbiSum']):
            listTop[i]['zhonbiSum'] = 0
        else:
            listTop[i]['zhonbiSum'] = float('%0.2f' % (listTop[i]['zhonbiSum'] * 100))

        # 有效商品数，有效商品数合计 除以天数 四舍五入取整
        listTotal['qtyzSum'] = round(float('%0.2f'%(listTotal['qtyzSum'] / int(dayNum))))
        listTotal['qtylSum'] = round(float('%0.2f'%(listTotal['qtyzSum'] / int(dayNum))))

        listTotal['zhonbiSum'] = listTotal['qtylSum']/listTotal['qtyzSum']
        listTotal['zhonbiSum'] = str(float('%0.2f'%(listTotal['zhonbiSum']*100)))+'%'

        listTotal['mingciSum'] = ''

        sql = "SELECT b.sdate,SUM(b.qtyz) qtyz , SUM(b.qtyl) qtyl, (SUM(b.qtyl)/SUM(b.qtyz)) zhonbi, (SELECT COUNT(DISTINCT zhonbi) FROM Kzerostock a WHERE a.zhonbi <= b.zhonbi) AS mingci " \
              "FROM Kzerostock AS b " \
              "WHERE ShopID ='" + listTop[i]['ShopID'] +"' AND sdate BETWEEN '"+monthFirstStr+"' AND '"+todayStr+"' GROUP BY sdate"
        cur.execute(sql)
        listDetail = cur.fetchall()
        for item in listDetail:
            date = str(item['sdate'])[8:10]
            if (not item['qtyz']):
                listTop[i]['qtyz_' + date] = 0
            else:
                listTop[i]['qtyz_' + date] = float(item['qtyz'])
            if 'qtyz_' + date in listTotal:
                listTotal['qtyz_' + date] += listTop[i]['qtyz_' + date]
            else:
                listTotal['qtyz_' + date] = listTop[i]['qtyz_' + date]

            if (not item['qtyl']):
                listTop[i]['qtyl_' + date] = 0
            else:
                listTop[i]['qtyl_' + date] = float(item['qtyl'])
            if 'qtyl_' + date in listTotal:
                listTotal['qtyl_' + date] += listTop[i]['qtyl_' + date]
            else:
                listTotal['qtyl_' + date] = listTop[i]['qtyl_' + date]

            if (not item['zhonbi']):
                listTop[i]['zhonbi_' + date] = 0
            else:
                listTop[i]['zhonbi_' + date] = float('%0.2f' % (item['zhonbi'] * 100))

            listTotal['zhonbi_' + date] = listTotal['qtyl_' + date] / listTotal['qtyz_' + date]
            listTotal['zhonbi_' + date] = str(float('%0.2f' % (listTotal['zhonbi_' + date] * 100))) + '%'

            listTotal['mingci_' + date] = ''

        # 各个门店的sheet数据
        sqlShop = "SELECT ShopID,shopname,deptid,deptidname,qtyz,qtyl,zhonbi " \
#.........这里部分代码省略.........

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMysqlConn [as 别名]
def query():
    today = datetime.date.today()
    monthFirst = datetime.date.today().replace(day=1)

    if (str(today)[8:10] == '01'):
        monthFirst = datetime.date(datetime.date.today().year, datetime.date.today().month, 1)
        today = datetime.date(datetime.date.today().year, datetime.date.today().month, 1) - datetime.timedelta(days=1)
    todayStr = today.strftime('%y-%m-%d')
    monthFirstStr = str(monthFirst)

    shopTop = []
    shopTopTotal = {'shopid': '合计', 'shopname': ''}

    conn = mtu.getMysqlConn()
    cur = conn.cursor()
    # 月累计报损
    sqlMonthTotal = 'select shopid,shopname,sum(costvalue) costvalueSum,sum(lostvalue) lostvalueSum,(sum(lostvalue)/sum(costvalue)) lrateSum ' \
                    'from KGshop17lost ' \
                    'where ShopID!="C009" AND sdate between "' + monthFirstStr + '" and "' + todayStr + '" ' \
                                                                                                        'group by shopid order by shopid '
    cur.execute(sqlMonthTotal)
    shopTop = cur.fetchall()

    # 每日报损
    sqlDaily = 'select sdate,shopid,costvalue,lostvalue,lrate ' \
               'from KGshop17lost ' \
               'where ShopID!="C009" AND sdate between "' + monthFirstStr + '" and "' + todayStr + '" ' \
                                                                                                   'order by sdate '
    cur.execute(sqlDaily)
    listDaily = cur.fetchall()
    # 计算纵向合计、格式化数据、拼接月累计报损和每日报损
    for obj1 in shopTop:
        obj1['lostvalueSum'] = float(obj1['lostvalueSum']) if obj1['lostvalueSum'] else 0
        if 'lostvalueSum' in shopTopTotal:
            shopTopTotal['lostvalueSum'] += obj1['lostvalueSum']
        else:
            shopTopTotal['lostvalueSum'] = obj1['lostvalueSum']

        obj1['costvalueSum'] = float(obj1['costvalueSum']) if obj1['costvalueSum'] else 0
        if 'costvalueSum' in shopTopTotal:
            shopTopTotal['costvalueSum'] += obj1['costvalueSum']
        else:
            shopTopTotal['costvalueSum'] = obj1['costvalueSum']

        obj1['lrateSum'] = str(float('%0.2f' % (obj1['lrateSum'] * 100))) + '%' if obj1['lrateSum'] else '0.00%'
        shopTopTotal['lrateSum'] = shopTopTotal['lostvalueSum'] / shopTopTotal['costvalueSum']
        shopTopTotal['lrateSum'] = str(float('%0.2f' % (shopTopTotal['lrateSum'] * 100))) + '%'
        for obj2 in listDaily:
            date = str(obj2['sdate'])[8:10]
            if obj1['shopid'] == obj2['shopid']:
                obj1['costvalue_' + date] = float('%0.2f' % obj2['costvalue']) if obj2['costvalue'] else 0
                if 'costvalue_' + date in shopTopTotal:
                    shopTopTotal['costvalue_' + date] += obj1['costvalue_' + date]
                    shopTopTotal['costvalue_' + date] = float('%0.2f' % shopTopTotal['costvalue_' + date])
                else:
                    shopTopTotal['costvalue_' + date] = obj1['costvalue_' + date]

                obj1['lostvalue_' + date] = float('%0.2f' % obj2['lostvalue']) if obj2['lostvalue'] else 0
                if 'lostvalue_' + date in shopTopTotal:
                    shopTopTotal['lostvalue_' + date] += obj1['lostvalue_' + date]
                    shopTopTotal['lostvalue_' + date] = float('%0.2f' % shopTopTotal['lostvalue_' + date])
                else:
                    shopTopTotal['lostvalue_' + date] = obj1['lostvalue_' + date]

                obj1['lrate_' + date] = str(float(obj2['lrate'] * 100)) + '%' if obj2['lrate'] else '0.00%'
                shopTopTotal['lrate_' + date] = (shopTopTotal['lostvalue_' + date] / shopTopTotal['costvalue_' + date])
                shopTopTotal['lrate_' + date] = str(float('%0.2f' % (shopTopTotal['lrate_' + date] * 100))) + '%'

    TotalDict = {'shopTopTotal': shopTopTotal}
    return locals()

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMysqlConn [as 别名]
def balanceArticle(request):
    grpCode = request.session.get('s_grpcode')       #用户所属单位
    s_suppname = request.session.get('s_suppname')   #用户所属单
    grpName = Constants.SCM_UNIT[grpCode]
    contracttype = request.session.get("s_contracttype")   #经营方式
    paytypeid = str(int(request.session.get("s_paytypeid")))   #经营方式

    sheetId = request.GET.get('sheetid','')
    queryAction = request.POST.get('actionTxt','')
    #更新确认状态
    if queryAction == 'check':
        balanceObj = Billhead0.objects.get(sheetid__contains=sheetId,grpcode=grpCode)
        balanceObj.status='Y'
        balanceObj.save()

    #结算通知单汇总      ,beginsdate,endsdate
    balance = Billhead0.objects.values("shopid","venderid","vendername","sheetid","paytype","begindate","enddate"
                                               ,"editdate","curdxvalue","payablemoney","kxinvoice","kxmoney","kxcash",
                                               "premoney","editor","checker","paychecker","contracttype","beginsdate","endsdate","advance")\
                                       .get(sheetid__contains=sheetId)
    #结算通知明细
    balanceItems = Billheaditem0.objects.values("inshopid","refsheettype","refsheetid","managedeptid","payabledate",
                                                "costvalue","costtaxvalue","costtaxrate","salevalue","dkrate","invoicesheetid")\
                                        .filter(sheetid__contains=sheetId).exclude(costvalue=0)\
                                        .order_by("inshopid","refsheettype","refsheetid")

    itemList = []
    itemShopId = None
    for item in balanceItems:
        shopid = item["inshopid"]
        if itemShopId != shopid:
            vlist = []
            itemList.append([shopid,vlist])

        vlist.append(item)
        itemShopId = item["inshopid"]

    itemList = sorted(itemList)

    #本期帐扣发票金额
    if balance.get('kxinvoice'):
        cfpkx = balance.get('kxinvoice')
    else:
        cfpkx = decimal.Decimal(0.0)

    #帐扣扣项 帐扣金额
    zkkx = balance.get('kxmoney')-balance.get('kxcash')

     #本期应付金额
    if balance.get('curdxvalue'):
        curdxValue = balance.get('curdxvalue')
    else:
        curdxValue = decimal.Decimal(0.0)

    if balance.get('payablemoney'):
        payableMoney = balance.get('payablemoney')
    else:
        payableMoney= decimal.Decimal(0.0)

    #预付款余额
    if balance.get('premoney'):
        premoney = balance.get('premoney')
    else:
        premoney = decimal.Decimal(0.0)

    if curdxValue == 0:
        invoicePay =payableMoney-cfpkx#应开票金额
        realPay = payableMoney-zkkx-premoney#实付金额
    else:
        invoicePay = curdxValue-cfpkx
        realPay = curdxValue-zkkx-premoney

    #实付金额大写
    realPayUpper = mtu.rmbupper(float(round(realPay,2)))
    #打印日期
    printDate = datetime.date.today()
    ssum1 = decimal.Decimal(0.0)
    ssum2 = decimal.Decimal(0.0)
    ssum3 = decimal.Decimal(0.0)
    try:
        conn = mtu.getMysqlConn()
        cur = conn.cursor()
        sql = """select InShopID,sum(CostValue) AS CostValue,SUM(CostTaxValue) AS CostTaxValue,sum(salevalue) AS SaleValue
                 from billheaditem0 where SheetID ='{sheetId}' group by InShopID""".format(sheetId=sheetId)
        cur.execute(sql)
        slist = cur.fetchall()
        sdict = {}
        for row in slist:
            ssum1 += row["CostValue"]
            ssum2 += (row["CostValue"]-row["CostTaxValue"])
            ssum3 += row["SaleValue"]
            sdict[row["InShopID"]] = [ row["CostValue"],(row["CostValue"]-row["CostTaxValue"]),row["SaleValue"]]
    except Exception as e:
        print(e)

    #应结金额总额
    totalCostValue = decimal.Decimal(0.0)
    #税金总额
    totalCostTax = decimal.Decimal(0.0)
    mdept = []
#.........这里部分代码省略.........

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMysqlConn [as 别名]
def query(date,start,end,yesterday,lastDay):
    # 查询所有超市门店
    slist = BasShopRegion.objects.values("shopid", "shopname") \
        .filter(shoptype=11).exclude(shopid='C009').order_by("shopid")
    shopids = "','".join([shop["shopid"] for shop in slist])

    conn = mtu.getMysqlConn()
    cur = conn.cursor()
    # 查询全月预算
    ysql = "SELECT dateid saledate,groupid,shopid shopcode,SUM(salevalue)/10000 sale,SUM(salegain)/10000 gain FROM Estimate " \
           " WHERE dateid BETWEEN '" + start + " 00:00:00' AND '" + end + " 23:59:59.999' AND deptlevelid=2   AND shopid IN ('" + shopids + "')  " \
                                                                                                                                            "and groupid < 50 and groupid <> 42 GROUP BY dateid,shopid,groupid ORDER BY shopid,groupid,dateid "
    cur.execute(ysql)
    ylist = cur.fetchall()
    yshopdict, ygrpdict, ys_grplist = queryData(ylist, lastDay, date.day)

    # 查询全月销售实际
    sale_sql = "SELECT sdate saledate,shopcode,LEFT(sccode,2) groupid,SUM(svalue-discount)/10000 sale,SUM(svalue-discount-scost)/10000 gain   " \
               " FROM sales_pro WHERE sdate BETWEEN '" + start + " 00:00:00' AND '" + yesterday + " 23:59:59.999' AND shopcode IN ('" + shopids + "') " \
                                                                                                                                                  "and LEFT(sccode,2) < 50 and LEFT(sccode,2) <> 42 GROUP BY shopcode,LEFT(sccode,2),DATE_FORMAT(sdate,'%Y-%m-%d') "
    cur.execute(sale_sql)
    sale_list = cur.fetchall()
    sshopdict, sgrpdict, ss_grplist = queryData(sale_list, lastDay, date.day)

    # 查询批发销售单
    pf_sale_sql = "SELECT sdate saledate,shopid shopcode,LEFT(deptid,2) groupid,SUM(salevalue)/10000 sale,SUM(salevalue-costvalue)/10000 gain " \
                  " FROM kwholesale WHERE sdate BETWEEN '" + start + " 00:00:00' AND '" + yesterday + " 23:59:59.999' " \
                                                                                                      "and LEFT(deptid,2) < 50 and LEFT(deptid,2) <> 42  GROUP BY DATE_FORMAT(sdate,'%Y-%m-%d'),shopid,LEFT(deptid,2) " \
                                                                                                      "ORDER BY shopid,LEFT(deptid,2),DATE_FORMAT(sdate,'%Y-%m-%d') "
    cur.execute(pf_sale_sql)
    pf_sale_list = cur.fetchall()
    pfshopdict, pfgrpdict, pfs_grplist = queryData(pf_sale_list, lastDay, date.day)

    # 计算实际销售
    # 门店
    shop_saledict, shop_zbdict, shop_sumlist = countSale(yshopdict, sshopdict, pfshopdict, lastDay)
    # 课组
    group_saledict, group_zbdict, group_sumlist = countSale(ygrpdict, sgrpdict, pfgrpdict, lastDay)

    rslist = []
    grslist = []
    # 计算合计占比
    countSumZb(shop_sumlist)
    countSumZb(group_sumlist)

    # 合并list
    rslist.extend(shop_sumlist)
    grslist.extend(group_sumlist)

    mergeData(rslist, slist, yshopdict, shop_saledict, shop_zbdict, lastDay)
    mergeGroupData(grslist, ygrpdict, group_saledict, group_zbdict, lastDay)

    formate_data(rslist)
    formate_data(grslist)

    # 各个门店的课组每日明细
    srslist = []
    for row in slist:
        temp_rslist = []
        sid = row["shopid"]
        if sid in ys_grplist:
            yshopdata = ys_grplist[sid]
        else:
            yshopdata = {}

        if sid in ss_grplist:
            sshopdata = ss_grplist[sid]
        else:
            sshopdata = {}

        if sid in pfs_grplist:
            pfshopdata = pfs_grplist[sid]
        else:
            pfshopdata = {}

        shop_grp_saledict, shop_grp_zbdict, shop_grp_sumlist = countSale(yshopdata, sshopdata, pfshopdata, lastDay)

        countSumZb(shop_grp_sumlist)

        temp_rslist.extend(shop_grp_sumlist)

        mergeGroupData(temp_rslist, yshopdata, shop_grp_saledict, shop_grp_zbdict, lastDay)

        formate_data(temp_rslist)

        srslist.append(temp_rslist)

    shoplist = []
    for row in slist:
        item = {}
        item.setdefault("shopid", row["shopid"])
        item.setdefault("shopname", row["shopname"].strip())
        shoplist.append(item)


    data  = {"rlist": rslist, "shoplist": shoplist, "grslist": grslist, "srslist": srslist}
    return data

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

#.........这里部分代码省略.........
            "where deptid in (" + sqlsubcate13 + ") " \
                                                 "and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
    sql14 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
            "from `kwsaletop10` " \
            "where deptid in (" + sqlsubcate14 + ") " \
                                                 "and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
    sql15 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
            "from `kwsaletop10` " \
            "where deptid in (" + sqlsubcate15 + ") " \
                                                 "and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
    sql16 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
            "from `kwsaletop10` " \
            "where deptid in (" + sqlsubcate16 + ") " \
                                                 "and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
    sql17 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
            "from `kwsaletop10` " \
            "where deptid in (" + sqlsubcate17 + ") " \
                                                 "and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"

    sql2 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
           "from `kwsaletop10` " \
           "where deptid in (" + sqlsubcate2 + ") " \
                                               "and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
    sql3 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
           "from `kwsaletop10` " \
           "where deptid in (" + sqlsubcate3 + ") " \
                                               "and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
    sql4 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
           "from `kwsaletop10` " \
           "where deptid in (" + sqlsubcate4 + ") " \
                                               "and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"

    # 连接数据库
    conn = mtu.getMysqlConn()
    cur10 = conn.cursor()
    cur11 = conn.cursor()
    cur12 = conn.cursor()
    cur13 = conn.cursor()
    cur14 = conn.cursor()
    cur15 = conn.cursor()
    cur16 = conn.cursor()
    cur17 = conn.cursor()
    cur2 = conn.cursor()
    cur3 = conn.cursor()
    cur4 = conn.cursor()
    cur10.execute(sql10)
    cur11.execute(sql11)
    cur12.execute(sql12)
    cur13.execute(sql13)
    cur14.execute(sql14)
    cur15.execute(sql15)
    cur16.execute(sql16)
    cur17.execute(sql17)
    cur2.execute(sql2)
    cur3.execute(sql3)
    cur4.execute(sql4)
    # 获取各部类下的销售数据
    rows10 = cur10.fetchall()
    rows11 = cur11.fetchall()
    rows12 = cur12.fetchall()
    rows13 = cur13.fetchall()
    rows14 = cur14.fetchall()
    rows15 = cur15.fetchall()
    rows16 = cur16.fetchall()
    rows17 = cur17.fetchall()
    rows2 = cur2.fetchall()

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMysqlConn [as 别名]
def query():
    monthFirst = datetime.date.today().replace(day=1)
    today = datetime.datetime.today()
    if (str(today)[8:10] == '01'):
        monthFirst = datetime.date(datetime.date.today().year, datetime.date.today().month - 1, 1)
        today = datetime.date(datetime.date.today().year, datetime.date.today().month, 1) - datetime.timedelta(1)
    todayStr = today.strftime('%y-%m-%d')
    monthFirstStr = str(monthFirst)

    conn = mtu.getMysqlConn()
    sqlTop = 'SElECT ShopID,shopname, SUM(qtyz) AS qtyzSum,SUM(qtyl) AS qtylSum,(sum(qtyl) / sum(qtyz)) AS zhonbiSum ' \
             'FROM KNegativestock ' \
             'WHERE ShopID!="C009" AND sdate BETWEEN "' + monthFirstStr + '" AND "' + todayStr + '" GROUP BY ShopID ORDER BY ShopID'
    cur = conn.cursor()
    cur.execute(sqlTop)
    listTop = cur.fetchall()

    listTotal = {'ShopID': '合计', 'shopname': '', 'qtyzSum': 0}  # 初始化最后一行
    for i in range(0, len(listTop)):
        if (not listTop[i]['qtyzSum']):
            listTop[i]['qtyzSum'] = 0
        else:
            listTop[i]['qtyzSum'] = float(listTop[i]['qtyzSum'])
            listTop[i]['qtyzSum'] = round((listTop[i]['qtyzSum']) / int(today.day))
        if 'qtyzSum' in listTotal:
            listTotal['qtyzSum'] += listTop[i]['qtyzSum']
        else:
            listTotal['qtyzSum'] = listTop[i]['qtyzSum']

        if (not listTop[i]['qtylSum']):
            listTop[i]['qtylSum'] = 0
        else:
            listTop[i]['qtylSum'] = float(listTop[i]['qtylSum'])
            listTop[i]['qtylSum'] = round((listTop[i]['qtylSum']) / int(today.day))
        if 'qtylSum' in listTotal:
            listTotal['qtylSum'] += listTop[i]['qtylSum']
        else:
            listTotal['qtylSum'] = listTop[i]['qtylSum']

        if (not listTop[i]['zhonbiSum']):
            listTop[i]['zhonbiSum'] = 0
        else:
            listTop[i]['zhonbiSum'] = float('%0.2f' % (listTop[i]['zhonbiSum'] * 100))
        listTotal['zhonbiSum'] = listTotal['qtylSum'] / listTotal['qtyzSum']
        listTotal['zhonbiSum'] = str(float('%0.2f' % (listTotal['zhonbiSum'] * 100))) + '%'

        listTotal['mingciSum'] = ''

        sql = "SELECT b.sdate,SUM(b.qtyz) qtyz , SUM(b.qtyl) qtyl, (SUM(b.qtyl)/SUM(b.qtyz)) zhonbi, (SELECT COUNT(DISTINCT zhonbi) FROM KNegativestock a WHERE a.zhonbi <= b.zhonbi) AS mingci " \
              "FROM KNegativestock AS b " \
              "WHERE ShopID ='" + listTop[i]['ShopID'] + "' AND sdate BETWEEN '" + monthFirstStr + "' AND '" + todayStr + "' GROUP BY sdate"

        cur.execute(sql)
        listDetail = cur.fetchall()
        for item in listDetail:
            date = str(item['sdate'])[8:10]
            if (not item['qtyz']):
                listTop[i]['qtyz_' + date] = 0
            else:
                listTop[i]['qtyz_' + date] = float(item['qtyz'])
            if 'qtyz_' + date in listTotal:
                listTotal['qtyz_' + date] += listTop[i]['qtyz_' + date]
            else:
                listTotal['qtyz_' + date] = listTop[i]['qtyz_' + date]

            if (not item['qtyl']):
                listTop[i]['qtyl_' + date] = 0
            else:
                listTop[i]['qtyl_' + date] = float(item['qtyl'])
            if 'qtyl_' + date in listTotal:
                listTotal['qtyl_' + date] += listTop[i]['qtyl_' + date]
            else:
                listTotal['qtyl_' + date] = listTop[i]['qtyl_' + date]

            if (not item['zhonbi']):
                listTop[i]['zhonbi_' + date] = 0
            else:
                listTop[i]['zhonbi_' + date] = float('%0.2f' % (item['zhonbi'] * 100))

            listTotal['zhonbi_' + date] = listTotal['qtyl_' + date] / listTotal['qtyz_' + date]
            listTotal['zhonbi_' + date] = str(float('%0.2f' % (listTotal['zhonbi_' + date] * 100))) + '%'

            listTotal['mingci_' + date] = ''
    TotalDict = {'listTotal': listTotal}
    listTop = ranking(listTop, 'zhonbiSum', 'mingciSum')

    monthFirstReal = int(str(listDetail[0]['sdate'])[8:10])
    for date in range(monthFirstReal, today.day + 1):
        if (date < 10):
            listTop = ranking(listTop, 'zhonbi_0' + str(date), 'mingci_0' + str(date))
        else:
            listTop = ranking(listTop, 'zhonbi_' + str(date), 'mingci_' + str(date))
    listTop.sort(key=lambda x: x['ShopID'])

    ###课组汇总###
    sqlDept = 'select deptid,deptidname,sum(qtyz) qtyz,sum(qtyl) qtyl,(sum(qtyl)/sum(qtyz)) zhonbi from KNegativestock' \
              ' where ShopID!="C009" AND sdate="' + todayStr + '" group by deptid,deptidname order by deptid'
    cur = conn.cursor()
    cur.execute(sqlDept)
    listDept = cur.fetchall()
#.........这里部分代码省略.........