Пример #1
0
def export(rslist, sum1, sum2, sum3):
    sname = "门店-大类销售汇总"

    titles = [
        ("门店名称", "1000", "shopid"),
        ("销售数量", "1000", "promflag"),
        ("实际销售", "1000", "startdate"),
        ("销售成本", "1000", "enddate"),
    ]

    fmtlist = [None, "0.00", "0.000", "0.000"]

    shopDict = findShop()

    dictlist = [shopDict, None, None, None]

    sumList = ["合计", sum1, sum2, sum3]

    book = mtu.exportXls(sname, titles, rslist, sumList, dictlist, fmtlist)

    outtype = "application/vnd.ms-excel"
    fname = "sale_counter.xls"
    response = mtu.getResponse(HttpResponse(), outtype, fname)
    book.save(response)
    return response
Пример #2
0
def exportXls(rslist):

    sname = "商品资料一览表"

    titles = [("门店编码", "1000", "shopid"), ("门店名称", "1000", "shopid"),
              ("商品条码", "1000", "barcodeid"), ("商品名称", "5000", "name"),
              ("规格", "500", "spec"), ("进价", "500", "cost"),
              ("执行售价", "1000", "price"), ("销售状态", "1000", "flag"),
              ("供应商", "1000", "venderid"), ("税率", "500", "costtaxrate"),
              ("单位", "500", "unitname"), ("小类", "1000", "deptid"),
              ("商品编码", "1000", "goodsid"), ("促销标志", "1000", "promflag"),
              ("开始日期", "1000", "startdate"), ("结束日期", "1000", "enddate")]

    fmtlist = [
        None, None, None, None, None, None, None, None, None, None, None, None,
        None, None, "%Y-%m-%d", "%Y-%m-%d"
    ]

    shopDict = findShop()

    dictlist = [
        None, shopDict, None, None, None, None, None, Constants.SALE_STATUS,
        None, None, None, None, None, Constants.PROM_FLAG, None, None
    ]

    book = mtu.exportXls(sname, titles, rslist, None, dictlist, fmtlist)

    outtype = 'application/vnd.ms-excel'
    fname = "goods.xls"
    response = mtu.getResponse(HttpResponse(), outtype, fname)
    book.save(response)
    return response
Пример #3
0
def export(rslist, sum1, sum2, sum3):
    sname = "门店-大类销售汇总"

    titles = [("门店名称", "1000", "shopid"), ("销售数量", "1000", "promflag"),
              ("实际销售", "1000", "startdate"), ("销售成本", "1000", "enddate")]

    fmtlist = [None, "0.00", "0.000", "0.000"]

    shopDict = findShop()

    dictlist = [shopDict, None, None, None]

    sumList = ["合计", sum1, sum2, sum3]

    book = mtu.exportXls(sname, titles, rslist, sumList, dictlist, fmtlist)

    outtype = 'application/vnd.ms-excel'
    fname = "sale_counter.xls"
    response = mtu.getResponse(HttpResponse(), outtype, fname)
    book.save(response)
    return response
Пример #4
0
def exportXls(rslist):

    sname = "商品资料一览表"

    titles = [("门店编码","1000","shopid"),("门店名称","1000","shopid"),("商品条码","1000","barcodeid"),("商品名称","5000","name"),("规格","500","spec"),
              ("进价","500","cost"),("执行售价","1000","price"),("销售状态","1000","flag"),("供应商","1000","venderid"),("税率","500","costtaxrate"),
              ("单位","500","unitname"),("小类","1000","deptid"),("商品编码","1000","goodsid"),("促销标志","1000","promflag"),("开始日期","1000","startdate"),
              ("结束日期","1000","enddate")]

    fmtlist = [None,None,None,None,None,None,None,None,None,None,
               None,None,None,None,"%Y-%m-%d","%Y-%m-%d"]

    shopDict = findShop()

    dictlist = [None,shopDict,None,None,None,None,None,Constants.SALE_STATUS,None,
               None,None,None,None,Constants.PROM_FLAG,None,None]

    book = mtu.exportXls(sname,titles,rslist,None,dictlist,fmtlist)

    outtype = 'application/vnd.ms-excel'
    fname = "goods.xls"
    response = mtu.getResponse(HttpResponse(),outtype,fname)
    book.save(response)
    return response
Пример #5
0
def detail1(request):

    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname")

    shopcode = mtu.getReqVal(request,"shopCode")
    sdate = mtu.getReqVal(request,"sdate")
    start = mtu.getReqVal(request,"start")
    end = mtu.getReqVal(request,"end")
    qtype = mtu.getReqVal(request,"qtype","1")

      #当月1号
    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")

    codes = ''
    shops = ''
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0,len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0:len(codes)-2]
            shops = shops[0:len(shops)-1]
    else:
        codes = '1=1'
        shops = '全部'

    type = ""
    if sdate:
        start = sdate;
        end = sdate;
        type = '日'
    else:
        sdate = ""

    sql ="SELECT t1.scost,t1.svalue,t1.num,t1.shopcode,t2.shopnm,t1.discount FROM(             "
    sql +="	SELECT SUM(scost) scost,SUM(svalue) svalue,SUM(num) num,shopcode,SUM(discount) discount FROM ( "
    sql +="		SELECT * FROM sales_pro WHERE grpcode='"+grpcode+"' AND sstyle<>''        "
    sql +="	        AND ("+codes+") AND   "
    sql +="		DATE_FORMAT(sdate,'%Y-%m-%d')>= '"+start+"' AND "
    sql +="		DATE_FORMAT(sdate,'%Y-%m-%d')<= '"+end+"' "
    sql +="	        AND supercode = '"+spercode+"'  "
    sql +="	        ORDER BY shopcode  "
    sql +="	) t3  "
    sql +="	GROUP BY shopcode "
    sql +=") t1 "
    sql +="LEFT JOIN bas_shop t2 ON t1.shopcode=t2.shopcode "
    sql +="WHERE t2.grpcode='"+grpcode+"' "

    cursor = connection.cursor()
    cursor.execute(sql)
    list = cursor.fetchall()

    sum1 = decimal.Decimal("0.0")
    sum2 = decimal.Decimal("0.0")
    sum3 = decimal.Decimal("0.0")
    rslist = []
    if list:
        for row in list:
            item = ["","","","",""]
            item[0] = row[3]
            item[1] = row[4]
            item[2] = row[2]
            item[3] = (row[1] - row[5])
            item[4] = row[0]

            sum1 += row[2]
            sum2 += (row[1]- row[5])
            sum3 += row[0]

            rslist.append(item)

    if qtype=='1':
        result = {"list":rslist}
        result.setdefault("type",type)
        result.setdefault("grpname",grpname)
        result.setdefault("shops",shops)
        result.setdefault("start",start)
        result.setdefault("end",end)
        result.setdefault("sdate",sdate)
        result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
        result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
        result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))
        return render(request, "user_sale_analysis_dtlshop.html",result)
    else:
        return exportDetail1(rslist,sum1,sum2,sum3,type)
Пример #6
0
def query(request):

    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname")

    shopcode = mtu.getReqVal(request,"shopCode","")
    start = mtu.getReqVal(request,"start","")
    end = mtu.getReqVal(request,"end","")
    qtype = mtu.getReqVal(request,"qtype","1")

     #当月1号
    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")

    codes = ''
    shops = ''
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0,len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0:len(codes)-2]
            shops = shops[0:len(shops)-1]
    else:
        codes = '1=1'
        shops = '全部'

    sum = decimal.Decimal('0.0')
    sum1 = decimal.Decimal('0.0')
    sum2 = decimal.Decimal('0.0')
    sum3 = decimal.Decimal('0.0')
    sum4 = decimal.Decimal('0.0')
    try:
        sql1 = "select sum(ifnull(tb1.svalue,0)) from (select svalue from sales_pro " \
               " where grpcode='"+grpcode+"'  and ("+codes+") and DATE_FORMAT(sdate,'%Y-%m-%d')>='"+start+"'  and DATE_FORMAT(sdate,'%Y-%m-%d')<= '"+end+"' " \
               " and (sstyle <> '') and supercode='"+spercode+"' ) tb1"

        cursor = connection.cursor()
        cursor.execute(sql1)
        rsobj = cursor.fetchone()

        if rsobj:
            sum = rsobj[0]

        sql2 = "select date_format(tb1.sdate,'%Y-%m-%d') AS sdate,sum(tb1.svalue) AS svalue,sum(tb1.scost) AS scost," \
               "sum(tb1.num) AS num,sum(tb1.discount) AS discount " \
               "from (select * from sales_pro " \
               " where grpcode='"+grpcode+"'  and ("+codes+") and DATE_FORMAT(sdate,'%Y-%m-%d')>='"+start+"'  and DATE_FORMAT(sdate,'%Y-%m-%d')<= '"+end+"' "\
               " and (sstyle <> '') and supercode='"+spercode+"' )tb1 group by tb1.sdate order by tb1.sdate desc"

        cursor.execute(sql2)
        list = cursor.fetchall()

        rslist = []
        if list:
            for row in list:
                item = ["","","","","","","","",""]
                item[0]=row[0] #sdate
                item[1]=""
                item[2]=""
                item[3]=""
                item[4]=row[3]  #num
                item[5]=(row[1]-row[4]) #svalue - discount
                item[6]=row[2]  #scost

                if sum:
                    radio = (row[1] / sum * 100)
                    sum4 += radio

                item[7]=radio #占比
                item[8]=sum4 #累计占比

                sum1 += item[4]
                sum2 += item[5]
                sum3 += item[6]

                rslist.append(item)

        cursor.close()
    except Exception as e:
        print(e)
        rslist = []

    #折线图
    #imgfile = analysisImg(rslist,spercode,start,end)

    if qtype=='1':
        result = {"rslist":rslist}
        result.setdefault("shops",shops)
        result.setdefault("grpname",grpname)
        result.setdefault("shopCode",shopcode)
        result.setdefault("start",start)
        result.setdefault("end",end)
        result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
        result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
        result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))
        # result.setdefault("imgfile",imgfile)
        return render(request, "user_sale_analysis.html",result)
    else:
        return export(rslist,sum1,sum2,sum3)
Пример #7
0
def detail(request):
    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname","")
    shopcode = mtu.getReqVal(request,"shopCode","")
    sccode = mtu.getReqVal(request,"sccode","")
    start = mtu.getReqVal(request,"start","")
    end = mtu.getReqVal(request,"end","")
    qtype = mtu.getReqVal(request,"qtype","1")

     #当月1号
    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")

    codes = ''
    shops = ''
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0,len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0:len(codes)-2]
            shops = shops[0:len(shops)-1]
    else:
        codes = '1=1'
        shops = '全部'

    ccodes = ''
    if sccode:
        list = sccode.split(",")
        for i in range(0,len(list)):
            code = list[i]
            if code:
                ccodes += " sccode like '"+code+"%' or"
        if ccodes:
            ccodes = ccodes[0:len(ccodes)-2]
    else:
        ccodes = '1=1'

    sum = decimal.Decimal('0.0')
    sum1 = decimal.Decimal('0.0')
    sum2 = decimal.Decimal('0.0')
    sum3 = decimal.Decimal('0.0')
    sum4 = decimal.Decimal('0.0')
    try:
        sql1 = "select sum(scost) from sales_pro where grpcode='"+grpcode+"' "
        sql1 += "and supercode='"+spercode+"' and ("+codes+") and sstyle<>'' and "
        sql1 += "date_format(sdate,'%Y-%m-%d')>='"+start+"' and date_format(sdate,'%Y-%m-%d')<='"+end+"' and ("+ccodes+") "

        cursor = connection.cursor()
        cursor.execute(sql1)
        rsobj = cursor.fetchone()

        if rsobj:
            sum = rsobj[0]

        sql2 = "select tb1.sccode,tb1.scname,sum(tb1.num) as num,sum(tb1.svalue) as svalue,sum(tb1.discount) as discount,sum(tb1.scost) as scost "
        sql2 += ",tb1.sdate from( "
        sql2 += "     select sdate,sccode,scname,svalue,discount,scost,num from sales_pro "
        sql2 += "	  where grpcode='"+grpcode+"' "
        sql2 += "    and supercode='"+spercode+"' and ("+codes+") and sstyle<>'' and "
        sql2 += "    date_format(sdate,'%Y-%m-%d')>='"+start+"' and date_format(sdate,'%Y-%m-%d')<='"+end+"' and ("+ccodes+")  "
        sql2 += " ) tb1 "
        sql2 += "group by tb1.sccode,tb1.scname,tb1.sdate order by tb1.sccode "

        cursor.execute(sql2)
        list = cursor.fetchall()

        rslist = []
        if list:
            for row in list:
                item = ["","","","","","","",""]

                if sum:
                    radio = (row[5] / sum * 100)  #占比
                    sum4 += radio #累计占比

                item[0] = row[6]
                item[1] = row[0]
                item[2] = row[1]
                item[3] = row[2]
                item[4] = (row[3]-row[4])
                item[5] = row[5]
                item[6] = radio
                item[7] = sum4

                sum1 += item[3]
                sum2 += item[4]
                sum3 += item[5]

                rslist.append(item)

        cursor.close()
    except Exception as e:
        print(e)

    if qtype=='1':
        result = {"rslist":rslist}
        result.setdefault("shops",shops)
        result.setdefault("grpcode",grpcode)
        result.setdefault("grpname",grpname)
        result.setdefault("shopCode",shopcode)
        result.setdefault("sccode",sccode)
        result.setdefault("start",start)
        result.setdefault("end",end)
        result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
        result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
        result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))

        return render(request, "user_sale_ctg_detail.html",result)
    else:
        return exportDetail(rslist,sum1,sum2,sum3)
Пример #8
0
def detail3(request):

    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname")

    shopcode = mtu.getReqVal(request, "shopcode")
    sdate = mtu.getReqVal(request, "sdate")
    start = mtu.getReqVal(request, "start")
    end = mtu.getReqVal(request, "end")
    qtype = mtu.getReqVal(request, "qtype", "1")

    #当月1号
    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")

    codes = ''
    shops = ''
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0, len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0:len(codes) - 2]
            shops = shops[0:len(shops) - 1]
    else:
        codes = '1=1'
        shops = '全部'

    type = ""
    if sdate:
        start = sdate
        end = sdate
        type = '日'
    else:
        sdate = ""

    sv = decimal.Decimal('0.0')
    sum1 = decimal.Decimal('0.0')
    sum2 = decimal.Decimal('0.0')
    sum3 = decimal.Decimal('0.0')
    try:
        sql = "select tb1.teamcode,tb1.teamname,sum(tb1.num) as num ,sum(tb1.svalue) as svalue,sum(tb1.discount) as discount "
        sql += ",sum(tb1.scost) as scost,sum(tb1.zzk) as zzk from "
        sql += "(select * from sales_pro where "
        sql += "grpcode='" + grpcode + "' and (" + codes + ") and ( "
        sql += "sstyle<>'' ) and DATE_FORMAT(sdate,'%Y-%m-%d')>='" + start + "' "
        sql += "and DATE_FORMAT(sdate,'%Y-%m-%d')<='" + end + "' "
        sql += "and supercode='" + spercode + "' order by teamcode) tb1 "
        sql += "group by tb1.teamcode,tb1.teamname "

        cursor = connection.cursor()
        cursor.execute(sql)
        list = cursor.fetchall()
        outlist = []
        if list:
            for row in list:
                item = ["", "", "", "", ""]
                item[0] = row[0]
                item[1] = row[1]
                item[2] = row[2]
                item[3] = row[3] - row[4]
                item[4] = row[5]

                sum1 += item[2]
                sum2 += item[3]
                sum3 += item[4]

                outlist.append(item)

        cursor.close()
    except Exception as e:
        print(e)

    if qtype == '1':
        result = {"rslist": list}
        result.setdefault("type", type)
        result.setdefault("grpname", grpname)
        result.setdefault("shops", shops)
        result.setdefault("start", start)
        result.setdefault("end", end)
        result.setdefault("sdate", sdate)
        result.setdefault("sum1", sum1.quantize(decimal.Decimal('0.00')))
        result.setdefault("sum2", sum2.quantize(decimal.Decimal('0.000')))
        result.setdefault("sum3", sum3.quantize(decimal.Decimal('0.000')))
        return render(request, "user_sale_analysis_dtlgz.html", result)
    else:
        return exportDetail3(outlist, sum1, sum2, sum3, type)
Пример #9
0
def query(request):
    #接收前台参数,不区别get/post方法
    grpname = request.session.get("s_grpname", "")
    grpcode = request.session.get("s_grpcode")
    suppcode = request.session.get("s_suppcode")

    qtype = mtu.getReqVal(request, "qtype", "1")
    pageNum = mtu.getReqVal(request, "pageNum", "1")
    shopcode = mtu.getReqVal(request, "shopCode", "")
    barcode = mtu.getReqVal(request, "barcode", "")
    goodsName = mtu.getReqVal(request, "goodsName", "")
    orderstyle = mtu.getReqVal(request, "orderstyle", "")
    flag = mtu.getReqVal(request, "flag", "")

    #组合查询条件
    q = Q()
    karrs = {}
    shopnames = ""
    shopList = findShop()

    #设置默认查询条件
    q.add(~Q(grpcode=""), Q.AND)
    karrs.setdefault("grpcode", grpcode)
    karrs.setdefault("venderid", suppcode)
    if shopcode:
        clist = []
        codes = shopcode.split(",")
        for i in range(0, len(codes)):
            if codes[i]:
                clist.append(codes[i])
                shopnames += "%s," % shopList[str(codes[i])]
        karrs.setdefault('shopid__in', clist)

    if barcode:
        karrs.setdefault('barcodeid__icontains', barcode.strip())
    if goodsName:
        karrs.setdefault("name__icontains", goodsName.strip())
    #商品状态
    if flag:
        q.add(Q(flag=flag), Q.AND)

    #设置默认排序方式
    orderby = orderstyle
    if not orderby:
        orderby = "shopid"

    #分页查询数据
    pubList = BasGoods.objects \
        .filter(q,**karrs) \
        .order_by("-"+orderby) \
        .values("grpcode","name","unitname","spec","brandid","deptid","deptname","goodsid","shopid","venderid","cost",
                "costtaxrate","dkrate","promflag","startdate","enddate","flag","price","barcodeid")

    page = Paginator(pubList,
                     __EACH_PAGE_SHOW_NUMBER,
                     allow_empty_first_page=True).page(int(pageNum))

    if qtype == '1':
        result = {
            "page": page,
            "pageNum": str(pageNum),
            "gstatus": Constants.SALE_STATUS
        }
        result.setdefault("promflag", Constants.PROM_FLAG)
        result.setdefault("shopCode", shopcode)
        result.setdefault("barcode", barcode)
        result.setdefault("goodsName", goodsName)
        result.setdefault("orderstyle", orderstyle)
        result.setdefault("flag", flag)
        result.setdefault("grpname", grpname)
        result.setdefault("shopnames", shopnames[0:len(shopnames) - 1])
        return render(request, "user_base.html", result)
    else:
        return exportXls(pubList)
Пример #10
0
def query(request):
    user = request.session.get("s_user")
    suppcode = request.session.get("s_suppcode")
    utype = request.session.get("s_utype")
    grpname = request.session.get("s_grpname")
    dept = user["dept"]

    pageNum = MethodUtil.getReqVal(request,"pageNum","1")    #页码
    shopcode =  MethodUtil.getReqVal(request,"shopCode","")  #门店编号
    status =  MethodUtil.getReqVal(request,"status","A")   #确认状态
    state =  MethodUtil.getReqVal(request,"state","")    #过期状态
    logistics = MethodUtil.getReqVal(request,"logistics","")  #订单类型
    start = MethodUtil.getReqVal(request,"start","")   #审核日期:开始时间
    end = MethodUtil.getReqVal(request,"end","")  #审核日期:结束时间
    orderstyle =  MethodUtil.getReqVal(request,"orderstyle","")   #排序条件
    ordercode = MethodUtil.getReqVal(request,"ordercode","")   #订单编号

    inflag =  MethodUtil.getReqVal(request,"inflag","")    #验收状态

    #组合查询条件
    shopnames = ""
    karrs = {}
    karrs.setdefault("spercode",suppcode)   #供应商ID
    shopList = findShop()

    if utype=="1":
         karrs.setdefault('shopid',dept)
    else:
        if shopcode:
            clist = []
            codes = shopcode.split(",")
            for i in range(0,len(codes)):
                if codes[i]:
                    clist.append(codes[i])
                    shopnames+="%s," % shopList[str(codes[i])]
            karrs.setdefault('shopcode__in',clist)

    if ordercode:
        karrs.setdefault('ordercode__icontains',ordercode.strip())

    if status and status!="A":
        karrs.setdefault("status",status)

    if state=='Y':
        karrs.setdefault("sdate__lt",datetime.datetime.now().strftime("%Y-%m-%d"))
    elif state=='N':
        karrs.setdefault("sdate__gte",datetime.datetime.now().strftime("%Y-%m-%d"))

    if logistics and logistics!="A":
        karrs.setdefault("logistics",logistics)

    if start:
        karrs.setdefault("checkdate__gte",(start))
    else:
        karrs.setdefault("checkdate__gte",(datetime.datetime.now() - datetime.timedelta(days = 7)).strftime("%Y-%m-%d"))

    if end:
        karrs.setdefault("checkdate__lte","{end} 23:59:59".format(end=end))
    else:
        karrs.setdefault("checkdate__lte","{end} 23:59:59".format(end=datetime.datetime.now().strftime("%Y-%m-%d")))

    if inflag and inflag!="":
        karrs.setdefault("inflag",inflag)

    #设置默认排序方式
    orderby = orderstyle
    if not orderby:
        orderby = "sdate"

    #含税进价金额合计
    sumList = Ord.objects.filter(**karrs).aggregate(taxSum = Sum("inprice_tax"))
    if not sumList["taxSum"]:
        sumList["taxSum"] = 0.0

    #分页查询数据
    pubList = Ord.objects \
        .filter(**karrs) \
        .order_by("inflag","-"+orderby) \
        .values("remark","logistics","inflag","ordercode","checkdate","concode","style","spercode","spername","status","sdate",
                "shopcode","inprice_tax","printnum","seenum","purday","spsum","sjshsum","ssspzb")

    for item in pubList:
        slist = OrdStatus.objects.filter(ordercode=item["ordercode"]).values("status")
        if slist:
            item["status"] = slist[0]["status"]

    page = Paginator(pubList,__EACH_PAGE_SHOW_NUMBER,allow_empty_first_page=True).page(int(pageNum))

    result = {"page":page,"pageNum":str(pageNum)}
    result.setdefault("shopCode",shopcode)
    result.setdefault("status",status)
    result.setdefault("grpname",grpname)
    result.setdefault("state",state)
    result.setdefault("inflag",inflag)
    result.setdefault("logistics",logistics)
    result.setdefault("start",start)
    result.setdefault("end",end)
    result.setdefault("ordercode",ordercode)
    result.setdefault("orderstyle",orderstyle)
    result.setdefault("today",datetime.datetime.today())
    result.setdefault("shopnames",shopnames[0:len(shopnames)-1])
    result.setdefault("sum",'%.4f' % sumList["taxSum"])
    return render(request,"user_order.html",result)
Пример #11
0
def query(request):
    grpname = request.session.get("s_grpname", "")
    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")

    shopcode = mtu.getReqVal(request, "shopCode", "")
    teamcode = mtu.getReqVal(request, "teamcode", "")  #条件无效
    teamname = mtu.getReqVal(request, "teamname", "")  #条件无效
    start = mtu.getReqVal(request, "start")
    end = mtu.getReqVal(request, "end")
    qtype = mtu.getReqVal(request, "qtype", "1")

    #当月1号
    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")

    codes = ''
    shops = ''
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0, len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0:len(codes) - 2]
            shops = shops[0:len(shops) - 1]
    else:
        codes = '1=1'
        shops = '全部'
    #门店商品销售汇总
    #门店编号、总折扣、供应商折扣、销售金额、销售成本金额、销售数量
    sql = "SELECT tb1.shopcode,IFNULL(tb1.num,0),IFNULL(tb1.svalue,0),IFNULL(tb1.discount,0),IFNULL(tb1.scost,0),IFNULL(tb1.zzk,0) "
    sql += "FROM(SELECT a.shopcode,b.svalue,b.scost,b.num,b.discount,b.zzk "
    sql += "FROM(SELECT DISTINCT shopcode FROM sales_pro "
    sql += "WHERE grpcode='" + grpcode + "' AND (" + codes + ") AND supercode='" + spercode + "'  and teamcode like  '%" + teamcode.strip(
    ) + "%' and teamname like  '%" + teamname.strip() + "%' ) a "  #
    sql += "LEFT JOIN(SELECT tb2.shopcode,SUM(tb2.svalue) svalue,SUM(tb2.scost)scost,SUM(tb2.num)num, "
    sql += "SUM(tb2.discount) discount, SUM(tb2.zzk) zzk FROM (SELECT shopcode,svalue,scost,num,discount,zzk FROM sales_pro "
    sql += "WHERE grpcode='" + grpcode + "' AND DATE_FORMAT(sdate,'%Y-%m-%d')>='" + start + "' AND DATE_FORMAT(sdate,'%Y-%m-%d')<='" + end + "'  and teamcode like  '%" + teamcode.strip(
    ) + "%' and teamname like  '%" + teamname.strip() + "%' "
    sql += "AND (" + codes + ") AND (sstyle is not null and sstyle<>'' ) AND supercode='" + spercode + "' "  #
    sql += ") tb2 GROUP BY tb2.shopcode)b ON a.shopcode=b.shopcode) tb1 "

    sum1, sum2, sum3 = decimal.Decimal('0.0'), decimal.Decimal(
        '0.0'), decimal.Decimal('0.0')

    try:
        cursor = connection.cursor()
        cursor.execute(sql)
        list = cursor.fetchall()
        #计算合计
        rslist = []
        if list:
            for row in list:
                item = ["", "", "", ""]
                item[0] = row[0]
                item[1] = row[1]
                item[2] = row[2] - row[3]
                item[3] = row[4]

                sum1 += item[1]
                sum2 += item[2]
                sum3 += item[3]
                rslist.append(item)
        else:
            rslist = []

        cursor.close()
        # connection.close()
    except Exception as e:
        print(e)

    if qtype == "1":
        result = {"rslist": rslist}
        result.setdefault("grpcode", grpcode)
        result.setdefault("grpname", grpname)
        result.setdefault("shops", shops)
        result.setdefault("shopCode", shopcode)
        result.setdefault("teamcode", teamcode)
        result.setdefault("teamname", teamname)
        result.setdefault("start", start)
        result.setdefault("end", end)
        result.setdefault("sum1", "%.2f" % sum1)
        result.setdefault("sum2", "%.3f" % sum2)
        result.setdefault("sum3", "%.3f" % sum3)
        return render(request, "user_sale_counter.html", result)
    else:

        return export(rslist, sum1, sum2, sum3)
Пример #12
0
def detail(request):
    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname", "")

    shopCode = mtu.getReqVal(request, "shopCode")
    teamcode = mtu.getReqVal(request, "teamcode", "")
    teamname = mtu.getReqVal(request, "teamname", "")
    start = mtu.getReqVal(request, "start")
    end = mtu.getReqVal(request, "end")
    qtype = mtu.getReqVal(request, "qtype", "1")

    #当月1号
    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")

    shopDict = findShop()
    if shopCode:
        codes = " shopcode='%s' " % shopCode
        shops = "%s" % shopDict[str(shopCode)]

    sum1 = decimal.Decimal('0.0')
    sum2 = decimal.Decimal('0.0')
    sum3 = decimal.Decimal('0.0')

    sum6 = decimal.Decimal('0.0')
    teamsv = decimal.Decimal('0.0')
    try:
        #销售成本总和
        sql = "select sum(IFNULL(scost,0)) scost "
        sql += " from sales_pro where grpcode = '" + grpcode + "' and (" + codes + ") and sstyle<>''"
        sql += " and DATE_FORMAT(sdate,'%Y-%m-%d') >= '" + start + "' and DATE_FORMAT(sdate,'%Y-%m-%d') <= '" + end + "' and teamcode like '%" + teamcode.strip(
        ) + "%'"
        sql += " and supercode='" + spercode + "' and teamname like '%" + teamname.strip(
        ) + "%' "

        cursor = connection.cursor()
        cursor.execute(sql)
        rsobj = cursor.fetchone()

        if rsobj:
            teamsv = rsobj[0]

        #门店下商品大类销售汇总
        #总折扣、供应商折扣、实际销售、销售数量、销售成本、大类编码、销售日期、大类名称
        sql2 = "select IFNULL(tb2.zzk,0),IFNULL(tb2.discount,0),IFNULL(tb2.svalue,0),IFNULL(tb2.num,0),IFNULL(tb2.scost,0),tb2.bccode,tb2.sdate,org.orgname "
        sql2 += "    from(select sum(tb1.zzk) as zzk,sum(tb1.discount) as discount,sum(tb1.svalue) as svalue,sum(tb1.num) as num,"
        sql2 += "    sum(tb1.scost) as scost,tb1.bccode,date_format(tb1.sdate,'%Y-%m-%d') as sdate from("
        sql2 += "        select zzk,discount,svalue,num,scost,bccode,sdate "
        sql2 += "            from sales_pro  where grpcode='" + grpcode + "' and (" + codes + ") and sstyle<>'' and DATE_FORMAT(sdate,'%Y-%m-%d') >= '" + start + "' "
        sql2 += "            and DATE_FORMAT(sdate,'%Y-%m-%d') <= '" + end + "' and teamcode like  '%" + teamcode.strip(
        ) + "%' and teamname like  '%" + teamname.strip() + "%' "
        sql2 += "            and supercode='" + spercode + "' and bccode is not null"
        sql2 += "     ) tb1  "
        sql2 += "     group by tb1.bccode,date_format(tb1.sdate,'%Y-%m-%d')"
        sql2 += ") tb2,bas_org org where tb2.bccode = org.orgcode order by tb2.bccode,tb2.sdate"

        # print(sql2)
        cursor.execute(sql2)
        list = cursor.fetchall()

        rslist = []
        outlist = []
        type1, type2 = "", ""
        if list:
            for row in list:
                type2 = row[5]

                item = ["", "", "", "", "", "", "", "", ""]
                #销售数量、实际销售、销售成本、占比、累计占比
                if type1 != type2 and type1 != '':
                    itemsum = ["", "", "", "", "", "", "", "", ""]
                    itemsum[0] = '小计:'
                    itemsum[3] = sum1
                    itemsum[4] = sum2
                    itemsum[5] = sum3
                    itemsum[8] = '1'

                    outitemsum = itemsum[:]
                    outitemsum.pop()

                    rslist.append(itemsum)
                    outlist.append(outitemsum)

                item[0] = str(row[5])  #大类编码
                item[1] = str(row[7])  #大类名称
                item[2] = row[6]  #销售日期
                item[3] = row[3]  #销售数量
                item[4] = (row[2] - row[1])  #实际销售
                item[5] = row[4]  #销售成本
                if teamsv:
                    radio = (row[4] / teamsv * 100)
                    item[6] = str(radio)  #占比
                    sum6 += radio

                    item[7] = sum6  #累计占比

                item[8] = '2'  #类型

                type1 = type2

                sum1 += item[3]
                sum2 += item[4]
                sum3 += item[5]

                outitem = item[:]
                outitem.pop()
                rslist.append(item)
                outlist.append(outitem)

        cursor.close()
    except Exception as e:
        print(e)

    if qtype == "1":
        result = {"rslist": rslist}
        result.setdefault("grpcode", grpcode)
        result.setdefault("grpname", grpname)
        result.setdefault("shops", shops)
        result.setdefault("shopCode", shopCode)
        result.setdefault("teamcode", teamcode)
        result.setdefault("teamname", teamname)
        result.setdefault("start", start)
        result.setdefault("end", end)
        result.setdefault("sum1", sum1.quantize(decimal.Decimal('0.00')))
        result.setdefault("sum2", sum2.quantize(decimal.Decimal('0.000')))
        result.setdefault("sum3", sum3.quantize(decimal.Decimal('0.000')))
        return render(request, "user_sale_counter_detail.html", result)
    else:
        return exportDetail(outlist, sum1, sum2, sum3)
Пример #13
0
def query(request):
    grpname = request.session.get("s_grpname", "")
    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")

    shopcode = mtu.getReqVal(request, "shopCode", "")
    teamcode = mtu.getReqVal(request, "teamcode", "")  # 条件无效
    teamname = mtu.getReqVal(request, "teamname", "")  # 条件无效
    start = mtu.getReqVal(request, "start")
    end = mtu.getReqVal(request, "end")
    qtype = mtu.getReqVal(request, "qtype", "1")

    # 当月1号
    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")

    codes = ""
    shops = ""
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0, len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0 : len(codes) - 2]
            shops = shops[0 : len(shops) - 1]
    else:
        codes = "1=1"
        shops = "全部"
    # 门店商品销售汇总
    # 门店编号、总折扣、供应商折扣、销售金额、销售成本金额、销售数量
    sql = "SELECT tb1.shopcode,IFNULL(tb1.num,0),IFNULL(tb1.svalue,0),IFNULL(tb1.discount,0),IFNULL(tb1.scost,0),IFNULL(tb1.zzk,0) "
    sql += "FROM(SELECT a.shopcode,b.svalue,b.scost,b.num,b.discount,b.zzk "
    sql += "FROM(SELECT DISTINCT shopcode FROM sales_pro "
    sql += (
        "WHERE grpcode='"
        + grpcode
        + "' AND ("
        + codes
        + ") AND supercode='"
        + spercode
        + "'  and teamcode like  '%"
        + teamcode.strip()
        + "%' and teamname like  '%"
        + teamname.strip()
        + "%' ) a "
    )  #
    sql += "LEFT JOIN(SELECT tb2.shopcode,SUM(tb2.svalue) svalue,SUM(tb2.scost)scost,SUM(tb2.num)num, "
    sql += "SUM(tb2.discount) discount, SUM(tb2.zzk) zzk FROM (SELECT shopcode,svalue,scost,num,discount,zzk FROM sales_pro "
    sql += (
        "WHERE grpcode='"
        + grpcode
        + "' AND DATE_FORMAT(sdate,'%Y-%m-%d')>='"
        + start
        + "' AND DATE_FORMAT(sdate,'%Y-%m-%d')<='"
        + end
        + "'  and teamcode like  '%"
        + teamcode.strip()
        + "%' and teamname like  '%"
        + teamname.strip()
        + "%' "
    )
    sql += "AND (" + codes + ") AND (sstyle is not null and sstyle<>'' ) AND supercode='" + spercode + "' "  #
    sql += ") tb2 GROUP BY tb2.shopcode)b ON a.shopcode=b.shopcode) tb1 "

    sum1, sum2, sum3 = decimal.Decimal("0.0"), decimal.Decimal("0.0"), decimal.Decimal("0.0")

    try:
        cursor = connection.cursor()
        cursor.execute(sql)
        list = cursor.fetchall()
        # 计算合计
        rslist = []
        if list:
            for row in list:
                item = ["", "", "", ""]
                item[0] = row[0]
                item[1] = row[1]
                item[2] = row[2] - row[3]
                item[3] = row[4]

                sum1 += item[1]
                sum2 += item[2]
                sum3 += item[3]
                rslist.append(item)
        else:
            rslist = []

        cursor.close()
        # connection.close()
    except Exception as e:
        print(e)

    if qtype == "1":
        result = {"rslist": rslist}
        result.setdefault("grpcode", grpcode)
        result.setdefault("grpname", grpname)
        result.setdefault("shops", shops)
        result.setdefault("shopCode", shopcode)
        result.setdefault("teamcode", teamcode)
        result.setdefault("teamname", teamname)
        result.setdefault("start", start)
        result.setdefault("end", end)
        result.setdefault("sum1", "%.2f" % sum1)
        result.setdefault("sum2", "%.3f" % sum2)
        result.setdefault("sum3", "%.3f" % sum3)
        return render(request, "user_sale_counter.html", result)
    else:

        return export(rslist, sum1, sum2, sum3)
Пример #14
0
def detail(request):
    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname", "")

    shopCode = mtu.getReqVal(request, "shopCode")
    teamcode = mtu.getReqVal(request, "teamcode", "")
    teamname = mtu.getReqVal(request, "teamname", "")
    start = mtu.getReqVal(request, "start")
    end = mtu.getReqVal(request, "end")
    qtype = mtu.getReqVal(request, "qtype", "1")

    # 当月1号
    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")

    shopDict = findShop()
    if shopCode:
        codes = " shopcode='%s' " % shopCode
        shops = "%s" % shopDict[str(shopCode)]

    sum1 = decimal.Decimal("0.0")
    sum2 = decimal.Decimal("0.0")
    sum3 = decimal.Decimal("0.0")

    sum6 = decimal.Decimal("0.0")
    teamsv = decimal.Decimal("0.0")
    try:
        # 销售成本总和
        sql = "select sum(IFNULL(scost,0)) scost "
        sql += " from sales_pro where grpcode = '" + grpcode + "' and (" + codes + ") and sstyle<>''"
        sql += (
            " and DATE_FORMAT(sdate,'%Y-%m-%d') >= '"
            + start
            + "' and DATE_FORMAT(sdate,'%Y-%m-%d') <= '"
            + end
            + "' and teamcode like '%"
            + teamcode.strip()
            + "%'"
        )
        sql += " and supercode='" + spercode + "' and teamname like '%" + teamname.strip() + "%' "

        cursor = connection.cursor()
        cursor.execute(sql)
        rsobj = cursor.fetchone()

        if rsobj:
            teamsv = rsobj[0]

        # 门店下商品大类销售汇总
        # 总折扣、供应商折扣、实际销售、销售数量、销售成本、大类编码、销售日期、大类名称
        sql2 = "select IFNULL(tb2.zzk,0),IFNULL(tb2.discount,0),IFNULL(tb2.svalue,0),IFNULL(tb2.num,0),IFNULL(tb2.scost,0),tb2.bccode,tb2.sdate,org.orgname "
        sql2 += "    from(select sum(tb1.zzk) as zzk,sum(tb1.discount) as discount,sum(tb1.svalue) as svalue,sum(tb1.num) as num,"
        sql2 += "    sum(tb1.scost) as scost,tb1.bccode,date_format(tb1.sdate,'%Y-%m-%d') as sdate from("
        sql2 += "        select zzk,discount,svalue,num,scost,bccode,sdate "
        sql2 += (
            "            from sales_pro  where grpcode='"
            + grpcode
            + "' and ("
            + codes
            + ") and sstyle<>'' and DATE_FORMAT(sdate,'%Y-%m-%d') >= '"
            + start
            + "' "
        )
        sql2 += (
            "            and DATE_FORMAT(sdate,'%Y-%m-%d') <= '"
            + end
            + "' and teamcode like  '%"
            + teamcode.strip()
            + "%' and teamname like  '%"
            + teamname.strip()
            + "%' "
        )
        sql2 += "            and supercode='" + spercode + "' and bccode is not null"
        sql2 += "     ) tb1  "
        sql2 += "     group by tb1.bccode,date_format(tb1.sdate,'%Y-%m-%d')"
        sql2 += ") tb2,bas_org org where tb2.bccode = org.orgcode order by tb2.bccode,tb2.sdate"

        # print(sql2)
        cursor.execute(sql2)
        list = cursor.fetchall()

        rslist = []
        outlist = []
        type1, type2 = "", ""
        if list:
            for row in list:
                type2 = row[5]

                item = ["", "", "", "", "", "", "", "", ""]
                # 销售数量、实际销售、销售成本、占比、累计占比
                if type1 != type2 and type1 != "":
                    itemsum = ["", "", "", "", "", "", "", "", ""]
                    itemsum[0] = "小计:"
                    itemsum[3] = sum1
                    itemsum[4] = sum2
                    itemsum[5] = sum3
                    itemsum[8] = "1"

                    outitemsum = itemsum[:]
                    outitemsum.pop()

                    rslist.append(itemsum)
                    outlist.append(outitemsum)

                item[0] = str(row[5])  # 大类编码
                item[1] = str(row[7])  # 大类名称
                item[2] = row[6]  # 销售日期
                item[3] = row[3]  # 销售数量
                item[4] = row[2] - row[1]  # 实际销售
                item[5] = row[4]  # 销售成本
                if teamsv:
                    radio = row[4] / teamsv * 100
                    item[6] = str(radio)  # 占比
                    sum6 += radio

                    item[7] = sum6  # 累计占比

                item[8] = "2"  # 类型

                type1 = type2

                sum1 += item[3]
                sum2 += item[4]
                sum3 += item[5]

                outitem = item[:]
                outitem.pop()
                rslist.append(item)
                outlist.append(outitem)

        cursor.close()
    except Exception as e:
        print(e)

    if qtype == "1":
        result = {"rslist": rslist}
        result.setdefault("grpcode", grpcode)
        result.setdefault("grpname", grpname)
        result.setdefault("shops", shops)
        result.setdefault("shopCode", shopCode)
        result.setdefault("teamcode", teamcode)
        result.setdefault("teamname", teamname)
        result.setdefault("start", start)
        result.setdefault("end", end)
        result.setdefault("sum1", sum1.quantize(decimal.Decimal("0.00")))
        result.setdefault("sum2", sum2.quantize(decimal.Decimal("0.000")))
        result.setdefault("sum3", sum3.quantize(decimal.Decimal("0.000")))
        return render(request, "user_sale_counter_detail.html", result)
    else:
        return exportDetail(outlist, sum1, sum2, sum3)
Пример #15
0
def query(request):
    user = request.session.get("s_user")
    suppcode = request.session.get("s_suppcode")
    utype = request.session.get("s_utype")
    grpname = request.session.get("s_grpname")
    dept = user["dept"]

    pageNum = MethodUtil.getReqVal(request, "pageNum", "1")  #页码
    shopcode = MethodUtil.getReqVal(request, "shopCode", "")  #门店编号
    status = MethodUtil.getReqVal(request, "status", "A")  #确认状态
    state = MethodUtil.getReqVal(request, "state", "")  #过期状态
    logistics = MethodUtil.getReqVal(request, "logistics", "")  #订单类型
    start = MethodUtil.getReqVal(request, "start", "")  #审核日期:开始时间
    end = MethodUtil.getReqVal(request, "end", "")  #审核日期:结束时间
    orderstyle = MethodUtil.getReqVal(request, "orderstyle", "")  #排序条件
    ordercode = MethodUtil.getReqVal(request, "ordercode", "")  #订单编号

    inflag = MethodUtil.getReqVal(request, "inflag", "")  #验收状态

    #组合查询条件
    shopnames = ""
    karrs = {}
    karrs.setdefault("spercode", suppcode)  #供应商ID
    shopList = findShop()

    if utype == "1":
        karrs.setdefault('shopid', dept)
    else:
        if shopcode:
            clist = []
            codes = shopcode.split(",")
            for i in range(0, len(codes)):
                if codes[i]:
                    clist.append(codes[i])
                    shopnames += "%s," % shopList[str(codes[i])]
            karrs.setdefault('shopcode__in', clist)

    if ordercode:
        karrs.setdefault('ordercode__icontains', ordercode.strip())

    if status and status != "A":
        karrs.setdefault("status", status)

    if state == 'Y':
        karrs.setdefault("sdate__lt",
                         datetime.datetime.now().strftime("%Y-%m-%d"))
    elif state == 'N':
        karrs.setdefault("sdate__gte",
                         datetime.datetime.now().strftime("%Y-%m-%d"))

    if logistics and logistics != "A":
        karrs.setdefault("logistics", logistics)

    if start:
        karrs.setdefault("checkdate__gte", (start))
    else:
        karrs.setdefault("checkdate__gte",
                         (datetime.datetime.now() -
                          datetime.timedelta(days=7)).strftime("%Y-%m-%d"))

    if end:
        karrs.setdefault("checkdate__lte", "{end} 23:59:59".format(end=end))
    else:
        karrs.setdefault(
            "checkdate__lte", "{end} 23:59:59".format(
                end=datetime.datetime.now().strftime("%Y-%m-%d")))

    if inflag and inflag != "":
        karrs.setdefault("inflag", inflag)

    #设置默认排序方式
    orderby = orderstyle
    if not orderby:
        orderby = "sdate"

    #含税进价金额合计
    sumList = Ord.objects.filter(**karrs).aggregate(taxSum=Sum("inprice_tax"))
    if not sumList["taxSum"]:
        sumList["taxSum"] = 0.0

    #分页查询数据
    pubList = Ord.objects \
        .filter(**karrs) \
        .order_by("inflag","-"+orderby) \
        .values("remark","logistics","inflag","ordercode","checkdate","concode","style","spercode","spername","status","sdate",
                "shopcode","inprice_tax","printnum","seenum","purday","spsum","sjshsum","ssspzb")

    for item in pubList:
        slist = OrdStatus.objects.filter(
            ordercode=item["ordercode"]).values("status")
        if slist:
            item["status"] = slist[0]["status"]

    page = Paginator(pubList,
                     __EACH_PAGE_SHOW_NUMBER,
                     allow_empty_first_page=True).page(int(pageNum))

    result = {"page": page, "pageNum": str(pageNum)}
    result.setdefault("shopCode", shopcode)
    result.setdefault("status", status)
    result.setdefault("grpname", grpname)
    result.setdefault("state", state)
    result.setdefault("inflag", inflag)
    result.setdefault("logistics", logistics)
    result.setdefault("start", start)
    result.setdefault("end", end)
    result.setdefault("ordercode", ordercode)
    result.setdefault("orderstyle", orderstyle)
    result.setdefault("today", datetime.datetime.today())
    result.setdefault("shopnames", shopnames[0:len(shopnames) - 1])
    result.setdefault("sum", '%.4f' % sumList["taxSum"])
    return render(request, "user_order.html", result)
Пример #16
0
def detail3(request):

    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname")

    shopcode = mtu.getReqVal(request,"shopcode")
    sdate = mtu.getReqVal(request,"sdate")
    start = mtu.getReqVal(request,"start")
    end = mtu.getReqVal(request,"end")
    qtype = mtu.getReqVal(request,"qtype","1")

      #当月1号
    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")

    codes = ''
    shops = ''
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0,len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0:len(codes)-2]
            shops = shops[0:len(shops)-1]
    else:
        codes = '1=1'
        shops = '全部'

    type = ""
    if sdate:
        start = sdate;
        end = sdate;
        type = '日'
    else:
        sdate = ""

    sv = decimal.Decimal('0.0')
    sum1 = decimal.Decimal('0.0')
    sum2 = decimal.Decimal('0.0')
    sum3 = decimal.Decimal('0.0')
    try:
        sql = "select tb1.teamcode,tb1.teamname,sum(tb1.num) as num ,sum(tb1.svalue) as svalue,sum(tb1.discount) as discount "
        sql += ",sum(tb1.scost) as scost,sum(tb1.zzk) as zzk from "
        sql += "(select * from sales_pro where "
        sql += "grpcode='"+grpcode+"' and ("+codes+") and ( "
        sql += "sstyle<>'' ) and DATE_FORMAT(sdate,'%Y-%m-%d')>='"+start+"' "
        sql += "and DATE_FORMAT(sdate,'%Y-%m-%d')<='"+end+"' "
        sql += "and supercode='"+spercode+"' order by teamcode) tb1 "
        sql += "group by tb1.teamcode,tb1.teamname "

        cursor = connection.cursor()
        cursor.execute(sql)
        list = cursor.fetchall()
        outlist = []
        if list:
            for row in list:
                item = ["","","","",""]
                item[0] = row[0]
                item[1] = row[1]
                item[2] = row[2]
                item[3] = row[3]-row[4]
                item[4] = row[5]

                sum1 += item[2]
                sum2 += item[3]
                sum3 += item[4]

                outlist.append(item)

        cursor.close()
    except Exception as e:
        print(e)

    if qtype=='1':
        result = {"rslist":list}
        result.setdefault("type",type)
        result.setdefault("grpname",grpname)
        result.setdefault("shops",shops)
        result.setdefault("start",start)
        result.setdefault("end",end)
        result.setdefault("sdate",sdate)
        result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
        result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
        result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))
        return render(request, "user_sale_analysis_dtlgz.html",result)
    else:
         return exportDetail3(outlist,sum1,sum2,sum3,type)
Пример #17
0
def query(request):
    #接收前台参数,不区别get/post方法
    grpname = request.session.get("s_grpname","")
    grpcode = request.session.get("s_grpcode")
    suppcode = request.session.get("s_suppcode")

    qtype = mtu.getReqVal(request,"qtype","1")
    pageNum = mtu.getReqVal(request,"pageNum","1")
    shopcode =  mtu.getReqVal(request,"shopCode","")
    barcode =  mtu.getReqVal(request,"barcode","")
    goodsName =  mtu.getReqVal(request,"goodsName","")
    orderstyle =  mtu.getReqVal(request,"orderstyle","")
    flag = mtu.getReqVal(request,"flag","")

    #组合查询条件
    q = Q()
    karrs = {}
    shopnames = ""
    shopList = findShop()

    #设置默认查询条件
    q.add(~Q(grpcode=""),Q.AND)
    karrs.setdefault("grpcode",grpcode)
    karrs.setdefault("venderid",suppcode)
    if shopcode:
        clist = []
        codes = shopcode.split(",")
        for i in range(0,len(codes)):
            if codes[i]:
                clist.append(codes[i])
                shopnames+="%s," % shopList[str(codes[i])]
        karrs.setdefault('shopid__in',clist)

    if barcode:
        karrs.setdefault('barcodeid__icontains',barcode.strip())
    if goodsName:
        karrs.setdefault("name__icontains",goodsName.strip())
    #商品状态
    if flag:
        q.add(Q(flag=flag),Q.AND)

    #设置默认排序方式
    orderby = orderstyle
    if not orderby:
        orderby = "shopid"

    #分页查询数据
    pubList = BasGoods.objects \
        .filter(q,**karrs) \
        .order_by("-"+orderby) \
        .values("grpcode","name","unitname","spec","brandid","deptid","deptname","goodsid","shopid","venderid","cost",
                "costtaxrate","dkrate","promflag","startdate","enddate","flag","price","barcodeid")

    page = Paginator(pubList,__EACH_PAGE_SHOW_NUMBER,allow_empty_first_page=True).page(int(pageNum))

    if qtype=='1':
        result = {"page":page,"pageNum":str(pageNum),"gstatus":Constants.SALE_STATUS}
        result.setdefault("promflag",Constants.PROM_FLAG)
        result.setdefault("shopCode",shopcode)
        result.setdefault("barcode",barcode)
        result.setdefault("goodsName",goodsName)
        result.setdefault("orderstyle",orderstyle)
        result.setdefault("flag",flag)
        result.setdefault("grpname",grpname)
        result.setdefault("shopnames",shopnames[0:len(shopnames)-1])
        return render(request,"user_base.html",result)
    else:
        return exportXls(pubList)
Пример #18
0
def query(request):
    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname", "")

    shopcode = mtu.getReqVal(request, "shopCode", "")
    sccode = mtu.getReqVal(request, "sccode", "")
    start = mtu.getReqVal(request, "start", "")
    end = mtu.getReqVal(request, "end", "")
    qtype = mtu.getReqVal(request, "qtype", "1")

    #当月1号
    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")

    codes = ''
    shops = ''
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0, len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0:len(codes) - 2]
            shops = shops[0:len(shops) - 1]
    else:
        codes = '1=1'
        shops = '全部'

    ccodes = ''
    if sccode:
        list = sccode.split(",")
        for i in range(0, len(list)):
            code = list[i]
            if code:
                ccodes += " sccode like '" + code + "%' or"
        if ccodes:
            ccodes = ccodes[0:len(ccodes) - 2]
    else:
        ccodes = '1=1'

    sum = decimal.Decimal('0.0')
    sum1 = decimal.Decimal('0.0')
    sum2 = decimal.Decimal('0.0')
    sum3 = decimal.Decimal('0.0')
    sum4 = decimal.Decimal('0.0')
    try:
        sql1 = "select sum(scost) from sales_pro where grpcode='" + grpcode + "' "
        sql1 += "and supercode='" + spercode + "' and (" + codes + ") and sstyle<>'' and "
        sql1 += "date_format(sdate,'%Y-%m-%d')>='" + start + "' and date_format(sdate,'%Y-%m-%d')<='" + end + "' and (" + ccodes + ") "

        cursor = connection.cursor()
        cursor.execute(sql1)
        rsobj = cursor.fetchone()

        if rsobj:
            sum = rsobj[0]

        sql2 = "select tb1.sccode,tb1.scname,sum(tb1.num) as num,sum(tb1.svalue) as svalue,sum(tb1.discount) as discount,sum(tb1.scost) as scost "
        sql2 += " from( "
        sql2 += "     select sccode,scname,svalue,discount,scost,num from sales_pro "
        sql2 += "	  where grpcode='" + grpcode + "' "
        sql2 += "    and supercode='" + spercode + "' and (" + codes + ") and sstyle<>'' and "
        sql2 += "    date_format(sdate,'%Y-%m-%d')>='" + start + "' and date_format(sdate,'%Y-%m-%d')<='" + end + "' and (" + ccodes + ")  "
        sql2 += " ) tb1 "
        sql2 += "group by tb1.sccode,tb1.scname order by tb1.sccode "

        cursor.execute(sql2)
        list = cursor.fetchall()

        rslist = []
        if list:
            for row in list:

                item = ["", "", "", "", "", "", ""]
                item[0] = row[0]
                item[1] = row[1]
                item[2] = row[2]
                item[3] = (row[3] - row[4])
                item[4] = row[5]

                if sum:
                    radio = (row[5] / sum * 100)  #占比
                    sum4 += radio  #累计占比

                item[5] = radio
                item[6] = sum4

                sum1 += item[2]
                sum2 += item[3]
                sum3 += item[4]

                rslist.append(item)

        cursor.close()
    except Exception as e:
        print(e)

    if qtype == '1':
        result = {"rslist": rslist}
        result.setdefault("shops", shops)
        result.setdefault("grpcode", grpcode)
        result.setdefault("grpname", grpname)
        result.setdefault("shopCode", shopcode)
        result.setdefault("sccode", sccode)
        result.setdefault("start", start)
        result.setdefault("end", end)
        result.setdefault("sum1", sum1.quantize(decimal.Decimal('0.00')))
        result.setdefault("sum2", sum2.quantize(decimal.Decimal('0.000')))
        result.setdefault("sum3", sum3.quantize(decimal.Decimal('0.000')))

        return render(request, "user_sale_ctg.html", result)
    else:
        return export(rslist, sum1, sum2, sum3)
Пример #19
0
def query(request):
    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname","")

    shopcode = mtu.getReqVal(request,"shopCode","")
    sccode = mtu.getReqVal(request,"sccode","")
    start = mtu.getReqVal(request,"start","")
    end = mtu.getReqVal(request,"end","")
    pcode = mtu.getReqVal(request,"pcode","")
    pname = mtu.getReqVal(request,"pname","")
    tax = mtu.getReqVal(request,"tax","")
    barcode = mtu.getReqVal(request,"barcode","")
    orderstyle = mtu.getReqVal(request,"orderstyle","")
    qtype = mtu.getReqVal(request,"qtype","1")
    # fpcode = mtu.getReqVal(request,"fpcode")

     #当月1号
    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")

    codes = ''
    shops = ''
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0,len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0:len(codes)-2]
            shops = shops[0:len(shops)-1]
    else:
        codes = '1=1'
        shops = '全部'

    ccodes = ''
    if sccode:
        list = sccode.split(",")
        for i in range(0,len(list)):
            code = list[i]
            if code:
                ccodes += " sccode like '"+code+"%' or"
        if ccodes:
            ccodes = ccodes[0:len(ccodes)-2]
    else:
        ccodes = '1=1'

    if not orderstyle:
         orderstyle = "pcode"

    sum = decimal.Decimal('0.0')
    sum1 = decimal.Decimal('0.0')
    sum2 = decimal.Decimal('0.0')
    sum3 = decimal.Decimal('0.0')
    sum4 = decimal.Decimal('0.0')
    try:
        sql1 = "select sum(scost) scost from sales_pro where "
        sql1 += "grpcode='"+grpcode+"' and ("+codes+") "
        sql1 += "and ("+ccodes+") "
        sql1 += "and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
        sql1 += "and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
        sql1 += "and supercode='"+spercode+"' "
        if pcode:
            sql1 += "and pcode like '%"+pcode.strip()+"%' "
        if pname:
            sql1 += "and pname like '%"+pname.strip()+"%' "
        if tax:
            sql1 += "and tax='"+tax+"' "
        if barcode:
            sql1 += "and barcode like '%"+barcode.strip()+"%' "
        sql1 += "and sstyle<>'' "
        # sql1 += "and fpcode like '%%' "

        cursor = connection.cursor()
        cursor.execute(sql1)
        rsobj = cursor.fetchone()

        if rsobj:
            sum = rsobj[0]

        sql2 = "select pcode,barcode,pname,sccode,scname,classes,tax,num,svalue,discount,scost,zzk from(                                            "
        sql2 += "	select pcode,max(barcode) barcode,pname,sccode,scname,classes,tax,     "
        sql2 += "	sum(num) num,sum(svalue) svalue,sum(discount) discount  "
        sql2 += "	,sum(scost) scost,sum(zzk) zzk,      "
        sql2 += "	sstyle from(                         "
        sql2 += "		select * from sales_pro where                      "
        sql2 += "           grpcode='"+grpcode+"' and ("+codes+") "
        sql2 += "           and ("+ccodes+") "
        sql2 += "           and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
        sql2 += "           and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
        sql2 += "           and supercode='"+spercode+"' "
        if pcode:
            sql2 += "           and pcode like '%"+pcode.strip()+"%' "
        if pname:
            sql2 += "           and pname like '%"+pname.strip()+"%' "
        # sql2 += "           and fpcode like '%%' "
        if tax:
            sql2 += "           and tax='"+tax+"' "
        if barcode:
            sql2 += "           and barcode like '%"+barcode.strip()+"%' "
        sql2 += "           and sstyle<>'' "
        sql2 += "	) tb1 group by sstyle,pcode,pname,sccode,scname,classes,tax "
        sql2 += ") tb2 order by "+orderstyle+" desc   "

        cursor.execute(sql2)
        list = cursor.fetchall()

        rslist = []
        if list:
            for row in list:
                item = ["","","","","","","","","","","",""]

                if sum:
                    radio = (row[10] / sum * 100)   #占比
                    # item[12] = str(radio)
                    sum4 += radio   #累计占比

                    # item[13] = sum4

                item[0] = row[0]
                item[1] = row[1]
                item[2] = row[2]
                item[3] = row[3]
                item[4] = row[4]
                item[5] = row[5]
                item[6] = row[6]
                item[7] = row[7]
                item[8] = (row[8]-row[9])
                item[9] = row[10]
                item[10] = radio
                item[11] = sum4

                sum1 += item[7]
                sum2 += item[8]
                sum3 += item[9]

                rslist.append(item)

        cursor.close()
    except Exception as e:
        print(e)

    if qtype=='1':
        result = {"rslist":rslist}
        result.setdefault("shops",shops)
        result.setdefault("grpcode",grpcode)
        result.setdefault("grpname",grpname)
        result.setdefault("shopCode",shopcode)
        result.setdefault("sccode",sccode)
        result.setdefault("start",start)
        result.setdefault("end",end)
        result.setdefault("pcode",pcode)
        result.setdefault("pname",pname)
        result.setdefault("tax",tax)
        result.setdefault("barcode",barcode)
        result.setdefault("orderstyle",orderstyle)
        result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
        result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
        result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))

        return render(request, "user_sale_sellinfo.html",result)
    else:
        return export(rslist,sum1,sum2,sum3)
Пример #20
0
def query(request):

    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname")

    shopcode = mtu.getReqVal(request, "shopCode", "")
    start = mtu.getReqVal(request, "start", "")
    end = mtu.getReqVal(request, "end", "")
    qtype = mtu.getReqVal(request, "qtype", "1")

    #当月1号
    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")

    codes = ''
    shops = ''
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0, len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0:len(codes) - 2]
            shops = shops[0:len(shops) - 1]
    else:
        codes = '1=1'
        shops = '全部'

    sum = decimal.Decimal('0.0')
    sum1 = decimal.Decimal('0.0')
    sum2 = decimal.Decimal('0.0')
    sum3 = decimal.Decimal('0.0')
    sum4 = decimal.Decimal('0.0')
    try:
        sql1 = "select sum(ifnull(tb1.svalue,0)) from (select svalue from sales_pro " \
               " where grpcode='"+grpcode+"'  and ("+codes+") and DATE_FORMAT(sdate,'%Y-%m-%d')>='"+start+"'  and DATE_FORMAT(sdate,'%Y-%m-%d')<= '"+end+"' " \
               " and (sstyle <> '') and supercode='"+spercode+"' ) tb1"

        cursor = connection.cursor()
        cursor.execute(sql1)
        rsobj = cursor.fetchone()

        if rsobj:
            sum = rsobj[0]

        sql2 = "select date_format(tb1.sdate,'%Y-%m-%d') AS sdate,sum(tb1.svalue) AS svalue,sum(tb1.scost) AS scost," \
               "sum(tb1.num) AS num,sum(tb1.discount) AS discount " \
               "from (select * from sales_pro " \
               " where grpcode='"+grpcode+"'  and ("+codes+") and DATE_FORMAT(sdate,'%Y-%m-%d')>='"+start+"'  and DATE_FORMAT(sdate,'%Y-%m-%d')<= '"+end+"' "\
               " and (sstyle <> '') and supercode='"+spercode+"' )tb1 group by tb1.sdate order by tb1.sdate desc"

        cursor.execute(sql2)
        list = cursor.fetchall()

        rslist = []
        if list:
            for row in list:
                item = ["", "", "", "", "", "", "", "", ""]
                item[0] = row[0]  #sdate
                item[1] = ""
                item[2] = ""
                item[3] = ""
                item[4] = row[3]  #num
                item[5] = (row[1] - row[4])  #svalue - discount
                item[6] = row[2]  #scost

                if sum:
                    radio = (row[1] / sum * 100)
                    sum4 += radio

                item[7] = radio  #占比
                item[8] = sum4  #累计占比

                sum1 += item[4]
                sum2 += item[5]
                sum3 += item[6]

                rslist.append(item)

        cursor.close()
    except Exception as e:
        print(e)
        rslist = []

    #折线图
    #imgfile = analysisImg(rslist,spercode,start,end)

    if qtype == '1':
        result = {"rslist": rslist}
        result.setdefault("shops", shops)
        result.setdefault("grpname", grpname)
        result.setdefault("shopCode", shopcode)
        result.setdefault("start", start)
        result.setdefault("end", end)
        result.setdefault("sum1", sum1.quantize(decimal.Decimal('0.00')))
        result.setdefault("sum2", sum2.quantize(decimal.Decimal('0.000')))
        result.setdefault("sum3", sum3.quantize(decimal.Decimal('0.000')))
        # result.setdefault("imgfile",imgfile)
        return render(request, "user_sale_analysis.html", result)
    else:
        return export(rslist, sum1, sum2, sum3)
Пример #21
0
def query(request):
    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname","")

    shopcode = mtu.getReqVal(request,"shopCode","")
    sccode = mtu.getReqVal(request,"sccode","")
    start = mtu.getReqVal(request,"start","")
    end = mtu.getReqVal(request,"end","")
    pcode = mtu.getReqVal(request,"pcode","")
    pname = mtu.getReqVal(request,"pname","")
    tax = mtu.getReqVal(request,"tax","")
    barcode = mtu.getReqVal(request,"barcode","")
    orderstyle = mtu.getReqVal(request,"orderstyle","")
    qtype = mtu.getReqVal(request,"qtype","1")
    # fpcode = mtu.getReqVal(request,"fpcode")

     #当月1号
    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")

    codes = ''
    shops = ''
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0,len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0:len(codes)-2]
            shops = shops[0:len(shops)-1]
    else:
        codes = '1=1'
        shops = '全部'

    ccodes = ''
    if sccode:
        list = sccode.split(",")
        for i in range(0,len(list)):
            code = list[i]
            if code:
                ccodes += " sccode like '"+code+"%' or"
        if ccodes:
            ccodes = ccodes[0:len(ccodes)-2]
    else:
        ccodes = '1=1'

    if not orderstyle:
         orderstyle = "pcode"

    sum = decimal.Decimal('0.0')
    sum1 = decimal.Decimal('0.0')
    sum2 = decimal.Decimal('0.0')
    sum3 = decimal.Decimal('0.0')
    sum4 = decimal.Decimal('0.0')
    try:
        sql1 = "select sum(scost) scost from sales_pro where "
        sql1 += "grpcode='"+grpcode+"' and ("+codes+") "
        sql1 += "and ("+ccodes+") "
        sql1 += "and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
        sql1 += "and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
        sql1 += "and supercode='"+spercode+"' "
        if pcode:
            sql1 += "and pcode like '%"+pcode.strip()+"%' "
        if pname:
            sql1 += "and pname like '%"+pname.strip()+"%' "
        if tax:
            sql1 += "and tax='"+tax+"' "
        if barcode:
            sql1 += "and barcode like '%"+barcode.strip()+"%' "
        sql1 += "and sstyle<>'' "
        # sql1 += "and fpcode like '%%' "

        cursor = connection.cursor()
        cursor.execute(sql1)
        rsobj = cursor.fetchone()

        if rsobj:
            sum = rsobj[0]

        sql2 = "select pcode,barcode,pname,sccode,scname,classes,tax,num,svalue,discount,scost,zzk from(                                            "
        sql2 += "	select pcode,max(barcode) barcode,pname,sccode,scname,classes,tax,     "
        sql2 += "	sum(num) num,sum(svalue) svalue,sum(discount) discount  "
        sql2 += "	,sum(scost) scost,sum(zzk) zzk,      "
        sql2 += "	sstyle from(                         "
        sql2 += "		select * from sales_pro where                      "
        sql2 += "           grpcode='"+grpcode+"' and ("+codes+") "
        sql2 += "           and ("+ccodes+") "
        sql2 += "           and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
        sql2 += "           and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
        sql2 += "           and supercode='"+spercode+"' "
        if pcode:
            sql2 += "           and pcode like '%"+pcode.strip()+"%' "
        if pname:
            sql2 += "           and pname like '%"+pname.strip()+"%' "
        # sql2 += "           and fpcode like '%%' "
        if tax:
            sql2 += "           and tax='"+tax+"' "
        if barcode:
            sql2 += "           and barcode like '%"+barcode.strip()+"%' "
        sql2 += "           and sstyle<>'' "
        sql2 += "	) tb1 group by sstyle,pcode,pname,sccode,scname,classes,tax "
        sql2 += ") tb2 order by "+orderstyle+" desc   "

        cursor.execute(sql2)
        list = cursor.fetchall()

        rslist = []
        if list:
            for row in list:
                item = ["","","","","","","","","","","",""]

                if sum:
                    radio = (row[10] / sum * 100)   #占比
                    # item[12] = str(radio)
                    sum4 += radio   #累计占比

                    # item[13] = sum4

                item[0] = row[0]
                item[1] = row[1]
                item[2] = row[2]
                item[3] = row[3]
                item[4] = row[4]
                item[5] = row[5]
                item[6] = row[6]
                item[7] = row[7]
                item[8] = (row[8]-row[9])
                item[9] = row[10]
                item[10] = radio
                item[11] = sum4

                sum1 += item[7]
                sum2 += item[8]
                sum3 += item[9]

                rslist.append(item)

        cursor.close()
    except Exception as e:
        print(e)

    if qtype=='1':
        result = {"rslist":rslist}
        result.setdefault("shops",shops)
        result.setdefault("grpcode",grpcode)
        result.setdefault("grpname",grpname)
        result.setdefault("shopCode",shopcode)
        result.setdefault("sccode",sccode)
        result.setdefault("start",start)
        result.setdefault("end",end)
        result.setdefault("pcode",pcode)
        result.setdefault("pname",pname)
        result.setdefault("tax",tax)
        result.setdefault("barcode",barcode)
        result.setdefault("orderstyle",orderstyle)
        result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
        result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
        result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))

        return render(request, "user_sale_sellinfo.html",result)
    else:
        return export(rslist,sum1,sum2,sum3)
Пример #22
0
def detail1(request):

    spercode = request.session.get("s_suppcode")
    grpcode = request.session.get("s_grpcode")
    grpname = request.session.get("s_grpname")

    shopcode = mtu.getReqVal(request, "shopCode")
    sdate = mtu.getReqVal(request, "sdate")
    start = mtu.getReqVal(request, "start")
    end = mtu.getReqVal(request, "end")
    qtype = mtu.getReqVal(request, "qtype", "1")

    #当月1号
    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")

    codes = ''
    shops = ''
    shopDict = findShop()
    if shopcode:
        list = shopcode.split(",")
        for i in range(0, len(list)):
            code = list[i]
            if code:
                codes += " shopcode='%s' or" % code
                shops += "%s," % shopDict[str(code)]
        if codes:
            codes = codes[0:len(codes) - 2]
            shops = shops[0:len(shops) - 1]
    else:
        codes = '1=1'
        shops = '全部'

    type = ""
    if sdate:
        start = sdate
        end = sdate
        type = '日'
    else:
        sdate = ""

    sql = "SELECT t1.scost,t1.svalue,t1.num,t1.shopcode,t2.shopnm,t1.discount FROM(             "
    sql += "	SELECT SUM(scost) scost,SUM(svalue) svalue,SUM(num) num,shopcode,SUM(discount) discount FROM ( "
    sql += "		SELECT * FROM sales_pro WHERE grpcode='" + grpcode + "' AND sstyle<>''        "
    sql += "	        AND (" + codes + ") AND   "
    sql += "		DATE_FORMAT(sdate,'%Y-%m-%d')>= '" + start + "' AND "
    sql += "		DATE_FORMAT(sdate,'%Y-%m-%d')<= '" + end + "' "
    sql += "	        AND supercode = '" + spercode + "'  "
    sql += "	        ORDER BY shopcode  "
    sql += "	) t3  "
    sql += "	GROUP BY shopcode "
    sql += ") t1 "
    sql += "LEFT JOIN bas_shop t2 ON t1.shopcode=t2.shopcode "
    sql += "WHERE t2.grpcode='" + grpcode + "' "

    cursor = connection.cursor()
    cursor.execute(sql)
    list = cursor.fetchall()

    sum1 = decimal.Decimal("0.0")
    sum2 = decimal.Decimal("0.0")
    sum3 = decimal.Decimal("0.0")
    rslist = []
    if list:
        for row in list:
            item = ["", "", "", "", ""]
            item[0] = row[3]
            item[1] = row[4]
            item[2] = row[2]
            item[3] = (row[1] - row[5])
            item[4] = row[0]

            sum1 += row[2]
            sum2 += (row[1] - row[5])
            sum3 += row[0]

            rslist.append(item)

    if qtype == '1':
        result = {"list": rslist}
        result.setdefault("type", type)
        result.setdefault("grpname", grpname)
        result.setdefault("shops", shops)
        result.setdefault("start", start)
        result.setdefault("end", end)
        result.setdefault("sdate", sdate)
        result.setdefault("sum1", sum1.quantize(decimal.Decimal('0.00')))
        result.setdefault("sum2", sum2.quantize(decimal.Decimal('0.000')))
        result.setdefault("sum3", sum3.quantize(decimal.Decimal('0.000')))
        return render(request, "user_sale_analysis_dtlshop.html", result)
    else:
        return exportDetail1(rslist, sum1, sum2, sum3, type)