Exemple #1
0
def getStartAndEndDate(contracttype,payTypeName):
    stime = Constants.ERP_START_TIME

    #结算日期
    #erp系统使用的开始时间
    pstart = datetime.date(stime[0],stime[1],stime[2]).strftime("%Y-%m-%d")

    # if contracttype == "d":
    #     #上月底
    #     pend = (datetime.date.today().replace(day=1) - datetime.timedelta(1)).strftime("%Y-%m-%d")
    # else:
    #当前日期
    pend = datetime.datetime.now().strftime("%Y-%m-%d")

    if contracttype == "g":   #购销
        #单据日期
        cstart = datetime.date(stime[0],stime[1],stime[2]).strftime("%Y-%m-%d")
        cend = datetime.datetime.now().strftime("%Y-%m-%d")
    else:
        #单据日期
        if "月" in payTypeName and "半月结" not in payTypeName:
            if "月结30天" in payTypeName or "月结45天" in payTypeName:
                n = -2   #前两个月一整月
            elif "月结60天" in payTypeName:
                n = -3   #前三个月一整月
            else:
                n = -1   #前一个月一整月

            cstart = DateUtil.get_firstday_month(n)
            cend = DateUtil.get_lastday_month(n)
        else:
            cstart = datetime.date(stime[0],stime[1],stime[2]).strftime("%Y-%m-%d")
            cend = datetime.datetime.now().strftime("%Y-%m-%d")

    return pstart,pend,cstart,cend
Exemple #2
0
def query(yesterday):
    rbac = caches['redis2'].get('rbac_role')

    rbacDepart = rbac['depart']
    if len(rbacDepart):
        rbacDepart = json.loads(rbacDepart)
        rbacDepart = rbacDepart['sub'][0:len(rbacDepart['sub']) - 1]
        rbacDepartList = rbacDepart.split(',')
        rbacDepart = '"' + '","'.join(rbacDepartList) + '"'

    rbacClassList = []
    rbacClass = ''
    rbacCategory = rbac['category']
    if len(rbacCategory):
        rbacCategory = rbacCategory.replace('},', '}$')
        rbacCategoryList = rbacCategory.split('$')
        for category in rbacCategoryList:
            category = json.loads(category)
            ClassStr = category['sub'][0:len(category['sub']) - 1]
            ClassList = ClassStr.split(',')
            ClassStr = '"' + '","'.join(ClassList) + '"'

            rbacClassList += ClassList
            rbacClass += ClassStr + ','

    rbacClass = rbacClass[0:len(rbacClass) - 1]

    yearandmon = DateUtil.getyearandmonth()
    # 当前月份第一天
    monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
    # 当前月份最后一天
    monlastday = DateUtil.get_lastday_month()
    # 今天
    today = DateUtil.todaystr()

    for C in rbacClassList:
        if C == '10':
            lis10, unit10 = initDepartData('10', yesterday)
        elif C == '11':
            lis11, unit11 = initDepartData('11', yesterday)
        elif C == '12':
            lis12, unit12 = initDepartData('12', yesterday)
        elif C == '13':
            lis13, unit13 = initDepartData('13', yesterday)
        elif C == '14':
            lis14, unit14 = initDepartData('14', yesterday)
        elif C == '15':
            lis15, unit15 = initDepartData('15', yesterday)
        elif C == '16':
            lis16, unit16 = initDepartData('16', yesterday)
        elif C == '17':
            lis17, unit17 = initDepartData('17', yesterday)
        elif C == '2':
            lis2, unit2 = initDepartData('2', yesterday)
        elif C == '3':
            lis3, unit3 = initDepartData('3', yesterday)
        elif C == '4':
            lis4, unit4 = initDepartData('4', yesterday)

    return locals()
Exemple #3
0
def index(request):
    ucode = request.session.get("s_ucode")
    uname = request.session.get("s_uname")
    timeStart = datetime.date.today() - datetime.timedelta(days=1)
    timeEnd = datetime.date.today()

    # 操作日志
    qtype = mth.getReqVal(request, "qtype", "1")
    if not qtype:
        qtype = "1"
    key_state = mth.getReqVal(request, "key_state", '')
    if qtype == '2' and (not key_state or key_state != '2'):
        qtype = '1'
    path = request.path
    today = datetime.datetime.today()
    BasPurLog.objects.create(name="单品报损超100",
                             url=path,
                             qtype=qtype,
                             ucode=ucode,
                             uname=uname,
                             createtime=today)

    if qtype == "1":
        data = query(timeStart, timeEnd)
        return render(request, 'report/abnormal/loss_rate.html', data)
    else:
        yesterday = DateUtil.get_day_of_day(-1)
        name = '_abnormal_loss_rate100'
        fname = yesterday.strftime('%m.%d') + name + ".xls"
        return export(fname, timeStart, timeEnd)
Exemple #4
0
def index(request):
    exceltype = mtu.getReqVal(request, "exceltype", "2")
    # 操作日志
    if exceltype == '2':
        qtype = "1"
    else:
        qtype = "2"
    key_state = mtu.getReqVal(request, "key_state", '')
    if exceltype == '1' and (not key_state or key_state != '2'):
        exceltype = '2'

    path = request.path
    today = datetime.datetime.today()
    ucode = request.session.get("s_ucode")
    uname = request.session.get("s_uname")
    BasPurLog.objects.create(name="顾客退货率",
                             url=path,
                             qtype=qtype,
                             ucode=ucode,
                             uname=uname,
                             createtime=today)

    # 昨天
    yesterday = DateUtil.get_day_of_day(-1)
    if exceltype == '1':
        fname = yesterday.strftime("%m.%d") + "_daily_customer_returns.xls"
        return export(fname, str(yesterday))
    else:
        data = query(str(yesterday))
        return render(request, "report/daily/customer_returns.html", data)
Exemple #5
0
def writeDataToSheet2(wb, resList, title):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month

    sheet2 = wb.add_sheet("%s负库存商品报告" % title, cell_overwrite_ok=True)
    titlesSheet2 = [[("(%s月)%s负库存商品报告" % (month, title), 0, 1, 15)],
                    [("报表日期", 11, 1, 1),
                     ("%s年-%s月-%s日" % (year, month, date.day), 12, 1, 3)],
                    [("门店编号", 0, 1, 1), ("门店名称", 1, 1, 1), ("管理类别码", 2, 1, 1),
                     ("管理类别名称", 3, 1, 1), ("小类编码", 4, 1, 1), ("小类名称", 5, 1, 1),
                     ("商品编码", 6, 1, 1), ("商品名称", 7, 1, 1), ("商品规格", 8, 1, 1),
                     ("销售单位", 9, 1, 1),
                     ("负库存数量", 10, 1, 1), ("负库存金额", 11, 1, 1),
                     ("解释原因", 12, 1, 1), ("解决方案", 13, 1, 1),
                     ("解决时间", 14, 1, 1)]]
    keylistSheet2 = [
        'shopid', 'shopname', 'sgroupid', 'sgroupname', 'deptid', 'deptname',
        'goodsid', 'goodsname', 'spec', 'unitname', 'qty', 'costvalue',
        'reason1', 'reason2', 'reason3'
    ]
    widthList = [
        600, 1400, 600, 600, 600, 800, 600, 1400, 600, 600, 600, 600, 2000,
        2000, 2000
    ]

    mtu.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList)
    mtu.insertCell2(sheet2, 3, resList, keylistSheet2, None)
Exemple #6
0
def index(request):
    yesterday = DateUtil.get_day_of_day(-1)

    qtype = mtu.getReqVal(request, "qtype", "1")
    # 操作日志
    if not qtype:
        qtype = "1"
    path = request.path
    today = datetime.datetime.today()
    ucode = request.session.get("s_ucode")
    uname = request.session.get("s_uname")
    BasPurLog.objects.create(name="单张小票退货超300",
                             url=path,
                             qtype=qtype,
                             ucode=ucode,
                             uname=uname,
                             createtime=today)

    if qtype == "1":
        data = query(yesterday)
        return render(request, "report/abnormal/ret_shopping_rec_300.html",
                      data)
    else:
        fname = yesterday.strftime(
            "%m.%d") + "_abnormal_ret_shopping_rec_300.xls"
        return export(fname, yesterday)
def index(request):
    date = DateUtil.get_day_of_day(-1)
    start = (date.replace(day=1)).strftime("%Y-%m-%d")
    yesterday = date.strftime("%Y-%m-%d")
    lastDay = calendar.monthrange(date.year, date.month)[1]
    end = "{year}-{month}-{day}".format(year=date.year, month=date.month, day=lastDay)

    qtype = mtu.getReqVal(request, "qtype", "1")
    # 操作日志
    if not qtype:
        qtype = "1"
    key_state = mtu.getReqVal(request, "key_state", '')
    if qtype == '2' and (not key_state or key_state != '2'):
        qtype = '1'
    path = request.path
    today = datetime.datetime.today()
    ucode = request.session.get("s_ucode")
    uname = request.session.get("s_uname")
    BasPurLog.objects.create(name="超市运营日分解", url=path, qtype=qtype, ucode=ucode, uname=uname, createtime=today)

    if qtype == "1":
        data = query(date,start,end,yesterday,lastDay)
        return render(request, "report/daily/group_opt_decompt.html",data)
    else:
        name = '_daily_group_operate_decompt'
        fname = date.strftime('%m.%d')+name+".xls"
        return export(fname,date,start,end,yesterday,lastDay)
Exemple #8
0
def writeDataToSheet2(wb, retdetail):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month
    lastDay = calendar.monthrange(year, month)[1]

    sheet2 = wb.add_sheet("退货明细", cell_overwrite_ok=True)

    titles = [
        [("(%s月%s日)门店顾客退货明细" % (month, date.day), 0, 1, 12)],
        [("门店编码", 0, 2, 1), ("门店名称", 1, 2, 1), ("销售日期", 2, 2, 1),
         ("销售时间", 3, 2, 1), ("退货小票", 4, 2, 1), ("posid", 5, 2, 1),
         ("收银员工号", 6, 2, 1), ("商品编码", 7, 2, 1), ("商品名称", 8, 2, 1),
         ("类别编码", 9, 2, 1), ("退货数量", 10, 2, 1), ("退货金额", 11, 2, 1)],
    ]

    keylist = [
        'shopid', 'shopname', 'sdate', 'stime', 'listno', 'posid', 'cashierid',
        'goodsid', 'goodsname', 'deptid', 'amount', 'sale'
    ]

    widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600]

    # 日销售报表
    mtu.insertTitle2(sheet2, titles, keylist, widthList)
    mtu.insertCell2(sheet2, 3, retdetail, keylist, None)
    titlesLen = len(titles)
    listTopLen = len(retdetail)
Exemple #9
0
def writeDataToSheet11(wb, lis4):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month
    lastDay = calendar.monthrange(year, month)[1]

    sheet4 = wb.add_sheet("4家电", cell_overwrite_ok=True)

    titles = [
        [("(%s月%s日)各店日销售排名日报(家电)" % (month, date.day), 0, 1, 13)],
        [("门店", 0, 2, 1), ("排名", 1, 2, 1), ("商品编码", 2, 2, 1), ("商品名称", 3, 2, 1), ("销售数量", 4, 2, 1),
         ("销售金额", 5, 2, 1), ("成本金额", 6, 2, 1), ("毛利", 7, 2, 1), ("毛利率%", 8, 2, 1), ("当前库存数量", 9, 2, 1),
         ("当前库存金额", 10, 2, 1), ("成本价", 11, 2, 1), ("平均售价", 12, 2, 1)],
    ]

    keylist = ['shopid', 'paiming', 'goodsid', 'goodsname', 'SaleQty', 'SaleValue', 'SaleCost', 'gpvalue', 'gprate', \
               'qty', 'costvalue', 'cprice', 'price']

    widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600]

    # 日销售报表
    mtu.insertTitle2(sheet4, titles, keylist, widthList)
    mtu.insertCell2(sheet4, 3, lis4, keylist, None)
    titlesLen = len(titles)
    listTopLen = len(lis4)
Exemple #10
0
def index(request):
    yesterday = DateUtil.get_day_of_day(-1)
    qtype = mtu.getReqVal(request, "qtype", "1")

    #操作日志
    if not qtype:
        qtype = "1"
    path = request.path
    today = datetime.datetime.today()
    ucode = request.session.get("s_ucode")
    uname = request.session.get("s_uname")
    BasPurLog.objects.create(name="商品连续3天负毛利",
                             url=path,
                             qtype=qtype,
                             ucode=ucode,
                             uname=uname,
                             createtime=today)

    if qtype == "1":
        data = query(yesterday)
        return render(request, "report/abnormal/negprofit_past3days.html",
                      {"rlist": list(data)})
    else:
        fname = yesterday.strftime(
            "%m.%d") + "_abnormal_negprofit_past3day.xls"
        return export(fname, yesterday)
Exemple #11
0
def index(request):
    exceltype = mtu.getReqVal(request, "exceltype", "2")
    # 操作日志
    if exceltype=='2':
        qtype = "1"
    else:
        qtype = "2"
    key_state = mtu.getReqVal(request, "key_state", '')
    if exceltype == '1' and (not key_state or key_state != '2'):
        exceltype = '2'

    path = request.path
    today = datetime.datetime.today()
    ucode = request.session.get("s_ucode")
    uname = request.session.get("s_uname")
    BasPurLog.objects.create(name="顾客退货率", url=path, qtype=qtype, ucode=ucode,uname=uname, createtime=today)

    # 昨天
    yesterday = DateUtil.get_day_of_day(-1)
    if exceltype == '1':
        fname = yesterday.strftime("%m.%d") + "_daily_customer_returns.xls"
        return export(fname,str(yesterday))
    else:
        data = query(str(yesterday))
        return render(request, "report/daily/customer_returns.html", data)
Exemple #12
0
def writeDataToSheet2(wb, retdetail):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month
    lastDay = calendar.monthrange(year, month)[1]

    sheet2 = wb.add_sheet("退货明细", cell_overwrite_ok=True)

    titles = [
        [("(%s月%s日)门店顾客退货明细" % (month, date.day), 0, 1, 12)],
        [("门店编码", 0, 2, 1), ("门店名称", 1, 2, 1), ("销售日期", 2, 2, 1), ("销售时间", 3, 2, 1), ("退货小票", 4, 2, 1),
         ("posid", 5, 2, 1), ("收银员工号", 6, 2, 1), ("商品编码", 7, 2, 1), ("商品名称", 8, 2, 1), ("类别编码", 9, 2, 1),
         ("退货数量", 10, 2, 1), ("退货金额", 11, 2, 1)],
    ]

    keylist = ['shopid', 'shopname', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'goodsid', 'goodsname', 'deptid', 'amount',
               'sale']

    widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600]

    # 日销售报表
    mtu.insertTitle2(sheet2, titles, keylist, widthList)
    mtu.insertCell2(sheet2, 3, retdetail, keylist, None)
    titlesLen = len(titles)
    listTopLen = len(retdetail)
Exemple #13
0
def writeDataToSheet11(wb, lis4):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month
    lastDay = calendar.monthrange(year, month)[1]

    sheet4 = wb.add_sheet("4家电", cell_overwrite_ok=True)

    titles = [
        [("(%s月%s日)各店日销售排名日报(家电)" % (month, date.day), 0, 1, 13)],
        [("门店", 0, 2, 1), ("排名", 1, 2, 1), ("商品编码", 2, 2, 1),
         ("商品名称", 3, 2, 1), ("销售数量", 4, 2, 1), ("销售金额", 5, 2, 1),
         ("成本金额", 6, 2, 1), ("毛利", 7, 2, 1), ("毛利率%", 8, 2, 1),
         ("当前库存数量", 9, 2, 1), ("当前库存金额", 10, 2, 1), ("成本价", 11, 2, 1),
         ("平均售价", 12, 2, 1)],
    ]

    keylist = ['shopid', 'paiming', 'goodsid', 'goodsname', 'SaleQty', 'SaleValue', 'SaleCost', 'gpvalue', 'gprate', \
               'qty', 'costvalue', 'cprice', 'price']

    widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600]

    # 日销售报表
    mtu.insertTitle2(sheet4, titles, keylist, widthList)
    mtu.insertCell2(sheet4, 3, lis4, keylist, None)
    titlesLen = len(titles)
    listTopLen = len(lis4)
Exemple #14
0
def writeDataToSheet2(wb,listShops,listShopTotal,listDept):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month

    titlesSheetShop = [[("(%s月)零库存课组明细日报" % month,0,1,7)],
              [("报表日期",3,1,1),("%s年-%s月-%s日"%(year,month,date.day),4,1,3)],
              [("门店编号",0,1,1),("门店名称",1,1,1),("课组编码",2,1,1),("课组名称",3,1,1),("课组汇总",4,1,1),("零库存数",3,1,1),("占比",3,1,1)]
              ]
    titlesSheetLast = [[("(%s月)零库存课组汇总日报" % month,0,1,5)],
              [("报表日期",2,1,1),("%s年-%s月-%s日"%(year,month,date.day),3,1,2)],
              [("课组编码",0,1,1),("课组名称",1,1,1),("课组汇总",2,1,1),("零库存数",3,1,1),("占比",4,1,1)]
              ]
    keylistSheet2 = ['ShopID','shopname','deptid','deptidname','qtyz','qtyl','zhonbi']
    keylistSheet3 = ['deptid','deptidname','qtyz','qtyl','zhonbi']
    widthList = [600,1100,600,600,600,600]
    sheets = []
    for i in range(0,len(listShops)):
        sheets.append(wb.add_sheet(listShops[i][0]['shopname'],cell_overwrite_ok=True))
    sheets.append(wb.add_sheet("(月)零库存课组汇总日报",cell_overwrite_ok=True))
    for j in range(0,len(sheets)-1):
        mtu.insertTitle2(sheets[j],titlesSheetShop,keylistSheet2,widthList)
        mtu.insertCell2(sheets[j],3,listShops[j-2],keylistSheet2,None)
        titlesLen = len(titlesSheetShop)
        listShopsLen = len(listShops)
        totalDict = {'dict':listShopTotal[j-2]}
        mtu.insertSum2(sheets[j],keylistSheet2,titlesLen+listShopsLen,totalDict,2)

    mtu.insertTitle2(sheets[len(sheets)-1],titlesSheetLast,keylistSheet3,widthList)
    mtu.insertCell2(sheets[len(sheets)-1],3,listDept,keylistSheet3,None)
Exemple #15
0
def index(request):
    date = DateUtil.get_day_of_day(-1)
    start = (date.replace(day=1)).strftime("%Y-%m-%d")
    yesterday = date.strftime("%Y-%m-%d")
    lastDay = calendar.monthrange(date.year, date.month)[1]
    end = "{year}-{month}-{day}".format(year=date.year,
                                        month=date.month,
                                        day=lastDay)

    qtype = mtu.getReqVal(request, "qtype", "1")
    # 操作日志
    if not qtype:
        qtype = "1"
    key_state = mtu.getReqVal(request, "key_state", '')
    if qtype == '2' and (not key_state or key_state != '2'):
        qtype = '1'
    path = request.path
    today = datetime.datetime.today()
    ucode = request.session.get("s_ucode")
    uname = request.session.get("s_uname")
    BasPurLog.objects.create(name="超市运营日分解",
                             url=path,
                             qtype=qtype,
                             ucode=ucode,
                             uname=uname,
                             createtime=today)

    if qtype == "1":
        data = query(date, start, end, yesterday, lastDay)
        return render(request, "report/daily/group_opt_decompt.html", data)
    else:
        name = '_daily_group_operate_decompt'
        fname = date.strftime('%m.%d') + name + ".xls"
        return export(fname, date, start, end, yesterday, lastDay)
Exemple #16
0
def index(request):
    exceltype = mtu.getReqVal(request, "exceltype", "2")
    if exceltype == '2':
        qtype = "1"
    else:
        qtype = "2"
    key_state = mtu.getReqVal(request, "key_state", '')
    if exceltype == '1' and (not key_state or key_state != '2'):
        exceltype = '2'

    # 操作日志
    path = request.path
    today = datetime.datetime.today()
    ucode = request.session.get("s_ucode")
    uname = request.session.get("s_uname")
    BasPurLog.objects.create(name="超市课组销售前十",
                             url=path,
                             qtype=qtype,
                             ucode=ucode,
                             uname=uname,
                             createtime=today)

    yesterday = DateUtil.get_day_of_day(-1)
    if exceltype == '1':
        fname = yesterday.strftime("%m.%d") + "_daily_saletop10_operate.xls"
        return export(fname, yesterday)
    else:
        data = query(yesterday)
        return render(request, "report/daily/saletop10.html", data)
Exemple #17
0
def writeDataToSheet2(wb,listDeptDetail,listDept):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month

    sheet2 = wb.add_sheet("(月)负库存课组明细日报",cell_overwrite_ok=True)
    sheet3 = wb.add_sheet("(月)负库存课组汇总日报",cell_overwrite_ok=True)

    titlesSheet2 = [[("(%s月)负库存课组明细日报" % month,0,1,7)],
              [("报表日期",3,1,1),("%s年-%s月-%s日"%(year,month,date.day),4,1,3)],
              [("门店编号",0,1,1),("门店名称",1,1,1),("课组编码",2,1,1),("课组名称",3,1,1),("课组汇总",4,1,1),("负库存数",3,1,1),("占比",3,1,1)]
              ]
    titlesSheet3 = [[("(%s月)负库存课组汇总日报" % month,0,1,5)],
              [("报表日期",2,1,1),("%s年-%s月-%s日"%(year,month,date.day),3,1,2)],
              [("课组编码",0,1,1),("课组名称",1,1,1),("课组汇总",2,1,1),("负库存数",3,1,1),("占比",4,1,1)]
              ]
    keylistSheet2 = ['shopid','shopname','deptid','deptidname','qtyz','qtyl','zhonbi']
    keylistSheet3 = ['deptid','deptidname','qtyz','qtyl','zhonbi']
    widthList = [600,1100,600,600,600,600]

    mtu.insertTitle2(sheet2,titlesSheet2,keylistSheet2,widthList)
    mtu.insertTitle2(sheet3,titlesSheet3,keylistSheet3,widthList)

    mtu.insertCell2(sheet2,3,listDeptDetail,keylistSheet2,None)
    mtu.insertCell2(sheet3,3,listDept,keylistSheet3,None)
Exemple #18
0
def writeDataToSheet2(wb, listDeptDetail, listDept):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month

    sheet2 = wb.add_sheet("(月)负库存课组明细日报", cell_overwrite_ok=True)
    sheet3 = wb.add_sheet("(月)负库存课组汇总日报", cell_overwrite_ok=True)

    titlesSheet2 = [[("(%s月)负库存课组明细日报" % month, 0, 1, 7)],
                    [("报表日期", 3, 1, 1),
                     ("%s年-%s月-%s日" % (year, month, date.day), 4, 1, 3)],
                    [("门店编号", 0, 1, 1), ("门店名称", 1, 1, 1), ("课组编码", 2, 1, 1),
                     ("课组名称", 3, 1, 1), ("课组汇总", 4, 1, 1), ("负库存数", 3, 1, 1),
                     ("占比", 3, 1, 1)]]
    titlesSheet3 = [[("(%s月)负库存课组汇总日报" % month, 0, 1, 5)],
                    [("报表日期", 2, 1, 1),
                     ("%s年-%s月-%s日" % (year, month, date.day), 3, 1, 2)],
                    [("课组编码", 0, 1, 1), ("课组名称", 1, 1, 1), ("课组汇总", 2, 1, 1),
                     ("负库存数", 3, 1, 1), ("占比", 4, 1, 1)]]
    keylistSheet2 = [
        'shopid', 'shopname', 'deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi'
    ]
    keylistSheet3 = ['deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi']
    widthList = [600, 1100, 600, 600, 600, 600]

    mtu.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList)
    mtu.insertTitle2(sheet3, titlesSheet3, keylistSheet3, widthList)

    mtu.insertCell2(sheet2, 3, listDeptDetail, keylistSheet2, None)
    mtu.insertCell2(sheet3, 3, listDept, keylistSheet3, None)
Exemple #19
0
def writeDataToSheet1(wb, listTop, TotalDict):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month
    lastDay = calendar.monthrange(year, month)[1]

    sheet = wb.add_sheet("(月)负库存排名日报", cell_overwrite_ok=True)

    titles = [[("(%s月)负库存排名日报" % month, 0, 1, 15)],
              [("门店编号", 0, 2, 1), ("门店名称", 1, 2, 1), ("月累计排名(不含联营)", 2, 1, 4)],
              [('有效商品数', 2, 1, 1), ('合计', 3, 1, 1), ('占比', 4, 1, 1),
               ('名次', 5, 1, 1)]]

    keylist = [
        'ShopID', 'shopname', 'qtyzSum', 'qtylSum', 'zhonbiSum', 'mingciSum'
    ]

    widthList = [600, 1100, 600, 600, 600, 600]

    trow1 = titles[1]
    trow2 = titles[2]

    n = 6
    for d in range(1, lastDay + 1):
        trow1.append((str(d) + '日排名(不含联营)', n, 1, 4))
        trow2.append(('有效商品数', n, 1, 1))
        trow2.append(('合计', n + 1, 1, 1))
        trow2.append(('占比', n + 2, 1, 1))
        trow2.append(('名次', n + 3, 1, 1))
        widthList.append(600)
        widthList.append(400)
        widthList.append(400)
        widthList.append(400)
        n += 4
        if d < 10:
            keylist.append('qtyz_0' + str(d))
            keylist.append('qtyl_0' + str(d))
            keylist.append('zhonbi_0' + str(d))
            keylist.append('mingci_0' + str(d))
        else:
            keylist.append('qtyz_' + str(d))
            keylist.append('qtyl_' + str(d))
            keylist.append('zhonbi_' + str(d))
            keylist.append('mingci_' + str(d))
    #日销售报表
    mtu.insertTitle2(sheet, titles, keylist, widthList)
    mtu.insertCell2(sheet, 3, listTop, keylist, None)
    titlesLen = len(titles)
    listTopLen = len(listTop)
    mtu.insertSum2(sheet, keylist, titlesLen + listTopLen, TotalDict, 2)
Exemple #20
0
def getStartAndEndDate(contracttype,payTypeName):
    stime = Constants.ERP_START_TIME

    #结算日期
    #erp系统使用的开始时间
    pstart = datetime.date(stime[0],stime[1],stime[2]).strftime("%Y-%m-%d")

    # if contracttype == "d":
    #     #上月底
    #     pend = (datetime.date.today().replace(day=1) - datetime.timedelta(1)).strftime("%Y-%m-%d")
    # else:
    #当前日期
    pend = datetime.datetime.now().strftime("%Y-%m-%d")

    if contracttype == "g":   #购销
        #单据日期
        cstart = datetime.date(stime[0],stime[1],stime[2]).strftime("%Y-%m-%d")
        cend = datetime.datetime.now().strftime("%Y-%m-%d")
    else:
        #单据日期
        if "月" in payTypeName and "半月结" not in payTypeName:
            if "月结30天" in payTypeName or "月结45天" in payTypeName:
                n = -2   #前两个月一整月
            elif "月结60天" in payTypeName:
                n = -3   #前三个月一整月
            else:
                n = -1   #前一个月一整月


            cstart = DateUtil.get_firstday_month(n)
            cend = DateUtil.get_lastday_month(n)
        else:
            cstart = datetime.date(stime[0],stime[1],stime[2]).strftime("%Y-%m-%d")
            cend = datetime.datetime.now().strftime("%Y-%m-%d")

    return pstart,pend,cstart,cend
Exemple #21
0
def writeDataToSheet1(wb,listTop,TotalDict):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month
    lastDay = calendar.monthrange(year,month)[1]

    sheet = wb.add_sheet("(月)零库存排名日报",cell_overwrite_ok=True)

    titles = [[("(%s月)零库存排名日报" % month,0,1,15)],
              [("门店编号",0,2,1),("门店名称",1,2,1),("月累计排名(不含联营)",2,1,4)],
              [('有效商品数',2,1,1),('合计',3,1,1),('占比',4,1,1),('名次',5,1,1)]
              ]

    keylist = ['ShopID','shopname','qtyzSum','qtylSum','zhonbiSum','mingciSum']

    widthList = [600,1100,600,600,600,600]

    trow1 = titles[1]
    trow2 = titles[2]

    n = 6
    for d in range(1,lastDay+1):
        trow1.append((str(d)+'日排名(不含联营)',n,1,4))
        trow2.append(('有效商品数',n,1,1))
        trow2.append(('合计',n+1,1,1))
        trow2.append(('占比',n+2,1,1))
        trow2.append(('名次',n+3,1,1))
        widthList.append(600)
        widthList.append(400)
        widthList.append(400)
        widthList.append(400)
        n += 4
        if d<10:
            keylist.append('qtyz_0'+str(d))
            keylist.append('qtyl_0'+str(d))
            keylist.append('zhonbi_0'+str(d))
            keylist.append('mingci_0'+str(d))
        else:
            keylist.append('qtyz_'+str(d))
            keylist.append('qtyl_'+str(d))
            keylist.append('zhonbi_'+str(d))
            keylist.append('mingci_'+str(d))
    #日销售报表
    mtu.insertTitle2(sheet,titles,keylist,widthList)
    mtu.insertCell2(sheet,3,listTop,keylist,None)
    titlesLen = len(titles)
    listTopLen = len(listTop)
    mtu.insertSum2(sheet,keylist,titlesLen+listTopLen,TotalDict,2)
Exemple #22
0
def writeDataToSheet1(wb, listtop, TotalDict):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month
    lastDay = calendar.monthrange(year, month)[1]

    sheet1 = wb.add_sheet("门店顾客退货率", cell_overwrite_ok=True)

    titles = [[("(%s月)门店顾客退货率" % month, 0, 1, 13)],
              [("序号", 0, 2, 1), ("门店编码", 1, 2, 1), ("门店名称", 2, 2, 1),
               ('月累计退货', 3, 1, 3)],
              [('销售金额', 3, 1, 1), ('退货金额', 4, 1, 1), ('退货率', 5, 1, 1)]]

    keylist = [
        'sequenceNumber', 'shopid', 'shopname', 'shopsalesum', 'retsum',
        'retrate'
    ]

    widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600]

    trow1 = titles[1]
    trow2 = titles[2]

    n = 6
    for d in range(1, lastDay + 1):
        trow1.append((str(month) + '月' + str(d) + '日', n, 1, 3))
        trow2.append(('销售金额', n, 1, 1))
        trow2.append(('退货金额', n + 1, 1, 1))
        trow2.append(('退货率', n + 2, 1, 1))
        widthList.append(600)
        widthList.append(400)
        widthList.append(400)
        n += 3
        if d < 10:
            keylist.append('retsum_0' + str(d))
            keylist.append('shopsalesum_0' + str(d))
            keylist.append('retrate_0' + str(d))
        else:
            keylist.append('retsum_' + str(d))
            keylist.append('shopsalesum_' + str(d))
            keylist.append('retrate_' + str(d))

    # 日销售报表
    mtu.insertTitle2(sheet1, titles, keylist, widthList)
    mtu.insertCell2(sheet1, 3, listtop, keylist, None)
    titlesLen = len(titles)
    listTopLen = len(listtop)
    mtu.insertSum2(sheet1, keylist, titlesLen + listTopLen, TotalDict, 3)
Exemple #23
0
def writeDataToSheet2(wb, resList, title):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month

    sheet2 = wb.add_sheet("%s负库存商品报告" % title, cell_overwrite_ok=True)
    titlesSheet2 = [
        [("(%s月)%s负库存商品报告" % (month, title), 0, 1, 15)],
        [("报表日期", 11, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 12, 1, 3)],
        [
            ("门店编号", 0, 1, 1),
            ("门店名称", 1, 1, 1),
            ("管理类别码", 2, 1, 1),
            ("管理类别名称", 3, 1, 1),
            ("小类编码", 4, 1, 1),
            ("小类名称", 5, 1, 1),
            ("商品编码", 6, 1, 1),
            ("商品名称", 7, 1, 1),
            ("商品规格", 8, 1, 1),
            ("销售单位", 9, 1, 1),
            ("负库存数量", 10, 1, 1),
            ("负库存金额", 11, 1, 1),
            ("解释原因", 12, 1, 1),
            ("解决方案", 13, 1, 1),
            ("解决时间", 14, 1, 1),
        ],
    ]
    keylistSheet2 = [
        "shopid",
        "shopname",
        "sgroupid",
        "sgroupname",
        "deptid",
        "deptname",
        "goodsid",
        "goodsname",
        "spec",
        "unitname",
        "qty",
        "costvalue",
        "reason1",
        "reason2",
        "reason3",
    ]
    widthList = [600, 1400, 600, 600, 600, 800, 600, 1400, 600, 600, 600, 600, 2000, 2000, 2000]

    mtu.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList)
    mtu.insertCell2(sheet2, 3, resList, keylistSheet2, None)
Exemple #24
0
def writeDataToSheet1(wb, shopTop, TotalDict):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month
    lastDay = calendar.monthrange(year, month)[1]

    sheet = wb.add_sheet("(月)水果报损率日报", cell_overwrite_ok=True)

    titles = [[("(%s月)水果报损率日报" % month, 0, 1, 15)],
              [("门店编号", 0, 2, 1), ("门店名称", 1, 2, 1), ("月累计报损", 2, 1, 3)],
              [
                  ('成本金额', 2, 1, 1),
                  ('销售成本金额', 3, 1, 1),
                  ('报损率', 4, 1, 1),
              ]]

    keylist = [
        'shopid', 'shopname', 'lostvalueSum', 'costvalueSum', 'lrateSum'
    ]

    widthList = [600, 1100, 600, 600, 600, 600]

    trow1 = titles[1]
    trow2 = titles[2]

    n = 5  #1日单元格开始位置
    for d in range(1, lastDay + 1):
        trow1.append((str(d) + '日', n, 1, 3))
        trow2.append(('成本金额', n, 1, 1))
        trow2.append(('销售成本金额', n + 1, 1, 1))
        trow2.append(('报损率', n + 2, 1, 1))
        widthList.append(600)
        widthList.append(400)
        widthList.append(400)
        widthList.append(400)
        n += 3  #每日单元格数量
        dStr = '0' + str(d) if d < 10 else str(d)
        keylist.append('lostvalue_' + dStr)
        keylist.append('costvalue_' + dStr)
        keylist.append('lrate_' + dStr)

    #日销售报表
    mtu.insertTitle2(sheet, titles, keylist, widthList)
    mtu.insertCell2(sheet, 3, shopTop, keylist, None)
    titlesLen = len(titles)
    listTopLen = len(shopTop)
    mtu.insertSum2(sheet, keylist, titlesLen + listTopLen, TotalDict, 2)
Exemple #25
0
def writeDataToSheet1(wb, listtop, TotalDict):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month
    lastDay = calendar.monthrange(year, month)[1]

    sheet1 = wb.add_sheet("门店顾客退货率", cell_overwrite_ok=True)

    titles = [
        [("(%s月)门店顾客退货率" % month, 0, 1, 13)],
        [("序号", 0, 2, 1), ("门店编码", 1, 2, 1), ("门店名称", 2, 2, 1), ('月累计退货', 3, 1, 3)],
        [('销售金额', 3, 1, 1), ('退货金额', 4, 1, 1), ('退货率', 5, 1, 1)]
    ]

    keylist = ['sequenceNumber', 'shopid', 'shopname', 'shopsalesum', 'retsum', 'retrate']

    widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600]

    trow1 = titles[1]
    trow2 = titles[2]

    n = 6
    for d in range(1, lastDay + 1):
        trow1.append((str(month) + '月' + str(d) + '日', n, 1, 3))
        trow2.append(('销售金额', n, 1, 1))
        trow2.append(('退货金额', n + 1, 1, 1))
        trow2.append(('退货率', n + 2, 1, 1))
        widthList.append(600)
        widthList.append(400)
        widthList.append(400)
        n += 3
        if d < 10:
            keylist.append('retsum_0' + str(d))
            keylist.append('shopsalesum_0' + str(d))
            keylist.append('retrate_0' + str(d))
        else:
            keylist.append('retsum_' + str(d))
            keylist.append('shopsalesum_' + str(d))
            keylist.append('retrate_' + str(d))


    # 日销售报表
    mtu.insertTitle2(sheet1, titles, keylist, widthList)
    mtu.insertCell2(sheet1, 3, listtop, keylist, None)
    titlesLen = len(titles)
    listTopLen = len(listtop)
    mtu.insertSum2(sheet1, keylist, titlesLen + listTopLen, TotalDict, 3)
Exemple #26
0
def index(request):
     yesterday = DateUtil.get_day_of_day(-1)

     qtype = mtu.getReqVal(request, "qtype", "1")
     #操作日志
     if not qtype:
         qtype = "1"
     path = request.path
     today = datetime.datetime.today()
     ucode = request.session.get("s_ucode")
     uname = request.session.get("s_uname")
     BasPurLog.objects.create(name="负毛利大于200",url=path,qtype=qtype,ucode=ucode,uname=uname,createtime=today)

     if qtype == "1":
         data = query(yesterday)
         return render(request, "report/abnormal/negprofit_lte200.html", data)
     else:
         fname = yesterday.strftime("%m.%d") + "_abnormal_negprofit_lte200.xls"
         return export(fname,yesterday)
Exemple #27
0
def writeDataToSheet1(wb,shopTop,TotalDict):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month
    lastDay = calendar.monthrange(year,month)[1]

    sheet = wb.add_sheet("(月)水果报损率日报",cell_overwrite_ok=True)

    titles = [[("(%s月)水果报损率日报" % month,0,1,15)],
              [("门店编号",0,2,1),("门店名称",1,2,1),("月累计报损",2,1,3)],
              [('成本金额',2,1,1),('销售成本金额',3,1,1),('报损率',4,1,1),]
              ]

    keylist = ['shopid','shopname','lostvalueSum','costvalueSum','lrateSum']

    widthList = [600,1100,600,600,600,600]

    trow1 = titles[1]
    trow2 = titles[2]

    n = 5 #1日单元格开始位置
    for d in range(1,lastDay+1):
        trow1.append((str(d)+'日',n,1,3))
        trow2.append(('成本金额',n,1,1))
        trow2.append(('销售成本金额',n+1,1,1))
        trow2.append(('报损率',n+2,1,1))
        widthList.append(600)
        widthList.append(400)
        widthList.append(400)
        widthList.append(400)
        n += 3  #每日单元格数量
        dStr = '0'+str(d) if d<10 else str(d)
        keylist.append('lostvalue_'+dStr)
        keylist.append('costvalue_'+dStr)
        keylist.append('lrate_'+dStr)

    #日销售报表
    mtu.insertTitle2(sheet,titles,keylist,widthList)
    mtu.insertCell2(sheet,3,shopTop,keylist,None)
    titlesLen = len(titles)
    listTopLen = len(shopTop)
    mtu.insertSum2(sheet,keylist,titlesLen+listTopLen,TotalDict,2)
Exemple #28
0
def index(request):
    sgroupid = request.REQUEST.get("sgroupid")
    yesterday = DateUtil.get_day_of_day(-1)

    qtype = mtu.getReqVal(request, "qtype", "1")
    # 操作日志
    if not qtype:
        qtype = "1"
    path = request.path
    today = datetime.datetime.today()
    ucode = request.session.get("s_ucode")
    uname = request.session.get("s_uname")
    BasPurLog.objects.create(name="商品连续3天负毛利", url=path, qtype=qtype, ucode=ucode, uname=uname, createtime=today)
    if qtype == "1":
        data = query(sgroupid, yesterday)
        print(data)
        return render(request, "report/abnormal/negStock.html", data)
    else:
        fname = yesterday.strftime("%m.%d") + "_abnormal_negStock.xls"
        return export(fname, sgroupid, yesterday)
Exemple #29
0
def writeDataToSheet1(wb,rlist,sumDict):
    date = DateUtil.get_day_of_day(-1)
    yesterday = date.strftime("%Y-%m-%d")

    sheet = wb.add_sheet("宽广集团销售日报表",cell_overwrite_ok=True)

    titles = [[("宽广集团销售日报表",2,1,13)],
              [("数据日期:",0,1,2),(yesterday,2,1,1),("单位:元",4,1,1)],
              [("机构编码",0,2,1),("机构名称",1,2,1),("POS销售数据",3,1,9),("批发销售数据",4,1,4)],
              [("总客流量",2,1,1),("平均客单价",3,1,1),("销售金额",4,1,1),("折扣金额",5,1,1),("实际销售",6,1,1),("销售成本",7,1,1),
               ("毛利",8,1,1),("毛利率",9,1,1),("优惠占比",10,1,1),("实际销售",11,1,1),("销售成本",12,1,1),("毛利",13,1,1),("毛利率",14,1,1)],
            ]

    keylist = ['shopid','shopnm','tradenumber','tradeprice','salevalue','discvalue','sale','costvalue',
               'salegain','gaintx','yhzhanbi','wsalevalue','wcostvalue','wsalegain',
               'wgaintx']

    widthList = [600,400,1000,800,400,800,800,800,800,800,800,800,800,800,800]

    mtu.insertTitle2(sheet,titles,keylist,widthList)
    count = mtu.insertCell2(sheet,4,rlist,keylist,None)
    mtu.insertSum2(sheet,keylist,count,sumDict,2)
Exemple #30
0
def writeDataToSheet1(wb,rlist,sumDict):
    date = DateUtil.get_day_of_day(-1)
    yesterday = date.strftime("%Y-%m-%d")

    sheet = wb.add_sheet("宽广集团销售日报表",cell_overwrite_ok=True)

    titles = [[("宽广集团销售日报表",2,1,13)],
              [("数据日期:",0,1,2),(yesterday,2,1,1),("单位:元",4,1,1)],
              [("机构编码",0,2,1),("机构名称",1,2,1),("POS销售数据",3,1,9),("批发销售数据",4,1,4)],
              [("总客流量",2,1,1),("平均客单价",3,1,1),("销售金额",4,1,1),("折扣金额",5,1,1),("实际销售",6,1,1),("销售成本",7,1,1),
               ("毛利",8,1,1),("毛利率",9,1,1),("优惠占比",10,1,1),("实际销售",11,1,1),("销售成本",12,1,1),("毛利",13,1,1),("毛利率",14,1,1)],
            ]

    keylist = ['shopid','shopnm','tradenumber','tradeprice','salevalue','discvalue','sale','costvalue',
               'salegain','gaintx','yhzhanbi','wsalevalue','wcostvalue','wsalegain',
               'wgaintx']

    widthList = [600,400,1000,800,400,800,800,800,800,800,800,800,800,800,800]

    mtu.insertTitle2(sheet,titles,keylist,widthList)
    count = mtu.insertCell2(sheet,4,rlist,keylist,None)
    mtu.insertSum2(sheet,keylist,count,sumDict,2)
Exemple #31
0
def writeDataToSheet2(wb, listShops, listShopTotal, listDept):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month

    titlesSheetShop = [[("(%s月)零库存课组明细日报" % month, 0, 1, 7)],
                       [("报表日期", 3, 1, 1),
                        ("%s年-%s月-%s日" % (year, month, date.day), 4, 1, 3)],
                       [("门店编号", 0, 1, 1), ("门店名称", 1, 1, 1),
                        ("课组编码", 2, 1, 1), ("课组名称", 3, 1, 1),
                        ("课组汇总", 4, 1, 1), ("零库存数", 3, 1, 1), ("占比", 3, 1, 1)]]
    titlesSheetLast = [[("(%s月)零库存课组汇总日报" % month, 0, 1, 5)],
                       [("报表日期", 2, 1, 1),
                        ("%s年-%s月-%s日" % (year, month, date.day), 3, 1, 2)],
                       [("课组编码", 0, 1, 1), ("课组名称", 1, 1, 1),
                        ("课组汇总", 2, 1, 1), ("零库存数", 3, 1, 1), ("占比", 4, 1, 1)]]
    keylistSheet2 = [
        'ShopID', 'shopname', 'deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi'
    ]
    keylistSheet3 = ['deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi']
    widthList = [600, 1100, 600, 600, 600, 600]
    sheets = []
    for i in range(0, len(listShops)):
        sheets.append(
            wb.add_sheet(listShops[i][0]['shopname'], cell_overwrite_ok=True))
    sheets.append(wb.add_sheet("(月)零库存课组汇总日报", cell_overwrite_ok=True))
    for j in range(0, len(sheets) - 1):
        mtu.insertTitle2(sheets[j], titlesSheetShop, keylistSheet2, widthList)
        mtu.insertCell2(sheets[j], 3, listShops[j - 2], keylistSheet2, None)
        titlesLen = len(titlesSheetShop)
        listShopsLen = len(listShops)
        totalDict = {'dict': listShopTotal[j - 2]}
        mtu.insertSum2(sheets[j], keylistSheet2, titlesLen + listShopsLen,
                       totalDict, 2)

    mtu.insertTitle2(sheets[len(sheets) - 1], titlesSheetLast, keylistSheet3,
                     widthList)
    mtu.insertCell2(sheets[len(sheets) - 1], 3, listDept, keylistSheet3, None)
Exemple #32
0
def index(request):
     qtype = mtu.getReqVal(request,"qtype","1")

     #操作日志
     if not qtype:
         qtype = "1"
     key_state = mtu.getReqVal(request, "key_state", '')
     if qtype=='2' and (not key_state or key_state!='2'):
         qtype = '1'

     path = request.path
     today = datetime.datetime.today()
     ucode = request.session.get("s_ucode")
     uname = request.session.get("s_uname")
     BasPurLog.objects.create(name="超市销售日报",url=path,qtype=qtype,ucode=ucode,uname=uname,createtime=today)

     date = DateUtil.get_day_of_day(-1)
     if qtype == "1":
         data = query(date)
         return render(request,"report/daily/group_sale.html",data)
     else:
         fname = date.strftime("%m.%d") + "_daily_group_sale.xls"
         return export(fname,date)
Exemple #33
0
def writeDataToSheet2(wb, data):
    date = DateUtil.get_day_of_day(-1)
    year = date.year
    month = date.month

    sheet2 = wb.add_sheet("单品报损超100", cell_overwrite_ok=True)
    titlesSheet2 = [[("单品报损超100", 0, 1, 12)],
                    [("报表日期", 8, 1, 1),
                     ("%s年-%s月-%s日" % (year, month, date.day), 9, 1, 3)],
                    [("机构编号", 0, 1, 1), ("机构名称", 1, 1, 1), ("单据编号", 2, 1, 1),
                     ("商品名称", 3, 1, 1), ("商品编码", 4, 1, 1), ("类别编码", 5, 1, 1),
                     ("类别名称", 6, 1, 1), ("申请数量", 7, 1, 1), ("审批数量", 8, 1, 1),
                     ("实际报损数", 9, 1, 1), ("成本金额", 10, 1, 1),
                     ("解释原因", 11, 1, 1)]]
    keylistSheet2 = [
        'shopid', 'shopname', 'sheetid', 'goodsid', 'goodsname', 'deptid',
        'deptname', 'askqty', 'checkqty', 'qty', 'costvalue'
        'reason'
    ]
    widthList = [600, 600, 600, 600, 1000, 600, 600, 600, 600, 600, 600, 1000]

    mth.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList)
    mth.insertCell2(sheet2, 3, data, keylistSheet2, None)
Exemple #34
0
def index(request):
     qtype = mtu.getReqVal(request,"qtype","1")

     #操作日志
     if not qtype:
         qtype = "1"
     key_state = mtu.getReqVal(request, "key_state", '')
     if qtype=='2' and (not key_state or key_state!='2'):
         qtype = '1'

     path = request.path
     today = datetime.datetime.today()
     ucode = request.session.get("s_ucode")
     uname = request.session.get("s_uname")
     BasPurLog.objects.create(name="超市销售日报",url=path,qtype=qtype,ucode=ucode,uname=uname,createtime=today)

     date = DateUtil.get_day_of_day(-1)
     if qtype == "1":
         data = query(date)
         return render(request,"report/daily/group_sale.html",data)
     else:
         fname = date.strftime("%m.%d") + "_daily_group_sale.xls"
         return export(fname,date)
Exemple #35
0
def index(request):
    sgroupid = request.REQUEST.get('sgroupid')
    yesterday = DateUtil.get_day_of_day(-1)

    qtype = mtu.getReqVal(request, "qtype", "1")
    #操作日志
    if not qtype:
        qtype = "1"
    path = request.path
    today = datetime.datetime.today()
    ucode = request.session.get("s_ucode")
    uname = request.session.get("s_uname")
    BasPurLog.objects.create(name="商品连续3天负毛利",
                             url=path,
                             qtype=qtype,
                             ucode=ucode,
                             uname=uname,
                             createtime=today)
    if qtype == "1":
        data = query(sgroupid, yesterday)
        return render(request, 'report/abnormal/negStock.html', data)
    else:
        fname = yesterday.strftime('%m.%d') + "_abnormal_negStock.xls"
        return export(fname, sgroupid, yesterday)
Exemple #36
0
def index(request):
    exceltype = mtu.getReqVal(request, "exceltype", "2")
    if exceltype=='2':
        qtype = "1"
    else:
        qtype = "2"
    key_state = mtu.getReqVal(request, "key_state", '')
    if exceltype == '1' and (not key_state or key_state != '2'):
        exceltype = '2'

    # 操作日志
    path = request.path
    today = datetime.datetime.today()
    ucode = request.session.get("s_ucode")
    uname = request.session.get("s_uname")
    BasPurLog.objects.create(name="超市课组销售前十", url=path, qtype=qtype, ucode=ucode,uname=uname, createtime=today)

    yesterday = DateUtil.get_day_of_day(-1)
    if exceltype == '1':
        fname = yesterday.strftime("%m.%d") + "_daily_saletop10_operate.xls"
        return export(fname,yesterday)
    else:
        data = query(yesterday)
        return render(request, "report/daily/saletop10.html", data)
Exemple #37
0
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
    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()
Exemple #38
0
def query(yesterday):
    yearandmon = DateUtil.getyearandmonth()
    # 当前月份第一天
    monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
    # 当前月份最后一天
    monlastday = DateUtil.get_lastday_month()
    # 今天
    today = DateUtil.todaystr()
    # 昨天

    # 获取部类编码
    classcode = getclasscode()

    # 获取所有商品的类别编码
    allcode = getallcode()

    # 获取门店编码
    shopsid = getshopid()

    # 查询某个部类下的子类编码
    subcate = {}
    # 将部类编码与子类编码组成dict
    for x in classcode:
        l = []
        for y in allcode:
            y = str(y)
            if len(x) == 1 and y[:1] == x:
                l.append(y)
            if len(x) == 2 and y[:2] == x:
                l.append(y)
        subcate.setdefault(x, l)

    subcate10 = subcate.get('10')
    subcate11 = subcate.get('11')
    subcate12 = subcate.get('12')
    subcate13 = subcate.get('13')
    subcate14 = subcate.get('14')
    subcate15 = subcate.get('15')
    subcate16 = subcate.get('16')
    subcate17 = subcate.get('17')
    subcate2 = subcate.get('2')
    subcate3 = subcate.get('3')
    subcate4 = subcate.get('4')
    sqlsubcate10 = ','.join(subcate10)
    sqlsubcate11 = ','.join(subcate11)
    sqlsubcate12 = ','.join(subcate12)
    sqlsubcate13 = ','.join(subcate13)
    sqlsubcate14 = ','.join(subcate14)
    sqlsubcate15 = ','.join(subcate15)
    sqlsubcate16 = ','.join(subcate16)
    sqlsubcate17 = ','.join(subcate17)
    sqlsubcate2 = ','.join(subcate2)
    sqlsubcate3 = ','.join(subcate3)
    sqlsubcate4 = ','.join(subcate4)

    sql10 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
            "from `kwsaletop10` " \
            "where deptid in (" + sqlsubcate10 + ") " \
                                                 "and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
    sql11 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
            "from `kwsaletop10` " \
            "where deptid in (" + sqlsubcate11 + ") " \
                                                 "and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
    sql12 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
            "from `kwsaletop10` " \
            "where deptid in (" + sqlsubcate12 + ") " \
                                                 "and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
    sql13 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
            "from `kwsaletop10` " \
            "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()
    rows3 = cur3.fetchall()
    rows4 = cur4.fetchall()

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

    # 10 熟食部类
    lis10 = []
    unit10 = []

    for sid in shopsid:
        i = 0
        for row in rows10:
            if sid['ShopID'] == row['shopid'] and i < 10:
                row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
                row['paiming'] = i + 1
                lis10.append(row)
                i += 1
            else:
                continue
        unit10.append(i)

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

    for sid in shopsid:
        i = 0
        for row in rows11:
            if sid['ShopID'] == row['shopid'] and i < 10:
                row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
                row['paiming'] = i + 1
                lis11.append(row)
                i += 1
            else:
                continue
        unit11.append(i)

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

    for sid in shopsid:
        i = 0
        for row in rows12:
            if sid['ShopID'] == row['shopid'] and i < 10:
                row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
                row['paiming'] = i + 1
                lis12.append(row)
                i += 1
            else:
                continue
        unit12.append(i)

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

    for sid in shopsid:
        i = 0
        for row in rows13:
            if sid['ShopID'] == row['shopid'] and i < 10:
                row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
                row['paiming'] = i + 1
                lis13.append(row)
                i += 1
            else:
                continue
        unit13.append(i)

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

    for sid in shopsid:
        i = 0
        for row in rows14:
            if sid['ShopID'] == row['shopid'] and i < 10:
                row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
                row['paiming'] = i + 1
                lis14.append(row)
                i += 1
            else:
                continue
        unit14.append(i)

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

    for sid in shopsid:
        i = 0
        for row in rows15:
            if sid['ShopID'] == row['shopid'] and i < 10:
                row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
                row['paiming'] = i + 1
                lis15.append(row)
                i += 1
            else:
                continue
        unit15.append(i)

    # 判断当天是否有数据,同时转换数据类型 int 转 string, decimal 转 float
    for i in range(0, len(rows16)):
        for key in rows16[i].keys():
            row = rows16[i][key]
            if row is None:
                rows16[i][key] = ''
            else:
                if isinstance(row, int):
                    rows16[i][key] = str(rows16[i][key])
                elif isinstance(row, decimal.Decimal):
                    rows16[i][key] = "%0.2f" % float(rows16[i][key])
    # 16 主食厨房部类
    lis16 = []
    unit16 = []

    for sid in shopsid:
        i = 0
        for row in rows16:
            if sid['ShopID'] == row['shopid'] and i < 10:
                row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
                row['paiming'] = i + 1
                lis16.append(row)
                i += 1
            else:
                continue
        unit16.append(i)

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

    for sid in shopsid:
        i = 0
        for row in rows17:
            if sid['ShopID'] == row['shopid'] and i < 10:
                row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
                row['paiming'] = i + 1
                lis17.append(row)
                i += 1
            else:
                continue
        unit17.append(i)

    # 判断当天是否有数据,同时转换数据类型 int 转 string, decimal 转 float
    for i in range(0, len(rows2)):
        for key in rows2[i].keys():
            row = rows2[i][key]
            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()
Exemple #39
0
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()

    # 合计转换数据格式
    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()
Exemple #40
0
    print(">>>main()")
    # print(DateUtil.get_firstday_month(-2))
    # print(DateUtil.get_lastday_month(-2))
    # print("月结30天" in "")
    # testSort2()

    # str = "CM01201603020064"
    # if str.startswith("CM",0,2):
    #     print("包含")
    # else:
    #     print("不包含")
    #
    # print(str[0:2:])
    # pr = str[0:2:]
    # if pr in ["CM","cM","cm","Cm",]:
    #     print("true");

    # a=[2,3,4,5]
    # b=[2,5,8]
    # print(list(set(a).union(set(b))))
    # a.extend(b)
    # print(a)

    dayOfWeek = datetime.datetime.today().weekday()
    print(dayOfWeek)
    date = DateUtil.strTodate("2016-06-06")
    print(date.weekday())

    date = DateUtil.strTodate("2016-06-05")
    print(date.weekday())
Exemple #41
0
def query(yesterday):
    rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)

    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 sdate between '{monfirstday}' and '{yesterday}' and shopid in ({rbacDepart})" \
             "group by shopid order by shopid"\
             .format(monfirstday=monfirstday,yesterday=yesterday,rbacDepart=rbacDepart)

    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='{shopID}' and sdate between '{monfirstday}' and '{yesterday}'"\
              .format(monfirstday=monfirstday,yesterday=yesterday,shopID = listtop[i]['shopid'])
        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()
Exemple #42
0
def query(yesterday):
    rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
    rbacClassList, rbacClass = reportMth.getRbacClass()

    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 sdate between '{monfirstday}' and '{yesterday}' and shopid in ({rbacDepart})" \
             "group by shopid order by shopid"\
             .format(monfirstday=monfirstday,yesterday=yesterday,rbacDepart=rbacDepart)

    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='{shopID}' and sdate between '{monfirstday}' and '{yesterday}'"\
              .format(shopID=listtop[i]['shopid'],monfirstday=monfirstday,yesterday=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}' and shopid in ({rbacDepart}) and LEFT(deptid,2) in ({rbacClass})" \
                "order by shopid"\
                .format(yesterday=yesterday,rbacDepart=rbacDepart,rbacClass=rbacClass)
    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()