Python Method.getMysqlConn方法代码示例

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def index(request):
    role_id = request.session.get('s_roleid')
    shop = request.session.get('s_shopcode')
    role = request.session.get('s_roleid')
    user = request.session.get('s_uid')

    if request.method == 'POST':
        start = request.POST.get('start', '')
        end = request.POST.get('end', '')
        endTime = datetime.datetime.strptime(end, '%Y-%m-%d').date() + datetime.timedelta(1)
        whereStr = "WHERE t.shopcode = s.shop_code AND t.add_time >='" + start + "' AND t.add_time <='" + str(endTime) + "' "

        if role in ('2', '10'):
            whereStr += " AND t.shopcode ='" + shop + "'"
        conn = mth.getMysqlConn()
        cur = conn.cursor()

        shopsCode = ''
        if role_id == '9':
            shopsCode = mth.getCityShopsCode('T')
            shopsCodeStr = "'" + "','".join(shopsCode) + "'"
            whereStr += " AND t.shopcode IN (" + shopsCodeStr + ")"
        if role_id == '8':
            shopsCode = mth.getCityShopsCode('C')
            shopsCodeStr = "'" + "','".join(shopsCode) + "'"
            whereStr += " AND t.shopcode IN (" + shopsCodeStr + ")"

        sql = """
    SELECT
    	date_format(
    		t.add_time,
    		'%Y年%m月%d日'
    	) AS create_date,
    	s.shop_name,
    	t.order_sn,
    	t.borrow_depart,
    	t.order_val * t.order_num AS borrow_account,
    	IFNULL(o.total_amount, 0) AS balance,
    	IFNULL(o.paid_amount, 0) AS actual,
    	IFNULL(o.discount_rate, 0) AS voucher,
    	t.paid_time
    FROM
    	order_borrow t
    LEFT JOIN orders o ON t.reply_order = o.order_sn,
     shops s """ + whereStr + """
    ORDER BY
    	create_date,
    	s.shop_name
            """
        cur.execute(sql)
        List = cur.fetchall()

        borrow_total = 0.00
        balance_total = 0.00
        actual_total = 0.00
        for row in List:
            borrow_total += float(row['borrow_account'])
            balance_total += float(row['balance'])
            actual_total += float(row['actual'])
    return render(request, 'report/finance/borrowCard.html', locals())

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def query(request):
    cardsStr = request.POST.get('cards','')
    cards = json.loads(cardsStr)
    listTotal = []
    conn = mth.getMysqlConn()
    cur = conn.cursor()
    for obj in cards:
        sql = " SELECT card_no, card_value, card_blance, card_status,is_store" \
              " FROM card_inventory" \
              " WHERE (card_no >= {start} AND card_no <= {end})" \
              " ORDER BY card_no" \
            .format(start=obj['start'], end=obj['end'])
        cur.execute(sql)
        qs_card = cur.fetchall()
        listTotal.extend(qs_card)

    cardNoList = []
    listTotalNew = []
    for item in listTotal:
        if(item['card_no'] in cardNoList):
            pass
        else:
            cardNoList.append(item['card_no'])
            item['card_blance']=float(item['card_blance'])
            listTotalNew.append(item)
    cur.close()
    conn.close()
    return HttpResponse(json.dumps(listTotalNew))

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def index(request):
    shop = request.session.get('s_shopcode', '')
    role_id = request.session.get('s_roleid')
    if request.method == 'GET':
        start = str(datetime.date.today().replace(day=1))
        end = str(datetime.date.today())
        endTime = str(datetime.date.today() + datetime.timedelta(1))
    if request.method == 'POST':
        today = datetime.date.today()
        start = request.POST.get('start', today)
        end = request.POST.get('end', today)
        endTime = str(datetime.datetime.strptime(end, '%Y-%m-%d').date() + datetime.timedelta(1))

    conn = mth.getMysqlConn()
    cur = conn.cursor()
    if role_id == '1' or role_id == '6':
        shops = mth.getCityShops()
        shopsCode = mth.getCityShopsCode()
    if role_id == '9':
        shops = mth.getCityShops('T')
        shopsCode = mth.getCityShopsCode('T')
    if role_id == '8':
        shops = mth.getCityShops('C')
        shopsCode = mth.getCityShopsCode('C')
    if role_id == '10' or role_id == '2':
        shops = base.findShop(shop)
        shopsCode = [shop]

    shopsCodeStr = "'" + "','".join(shopsCode) + "'"

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def save(request):
    res = {}
    if request.method == 'POST':
        # 检测session中Token值，判断用户提交动作是否合法
        Token = request.session.get('postToken', default=None)
        # 获取用户表单提交的Token值
        userToken = request.POST.get('postToken', '')
        if userToken != Token:
            res["msg"] = 0
            return HttpResponse(json.dumps(res))

        shop_code = request.session.get('s_shopcode')
        name = request.POST.get('name')
        start_no = request.POST.get('start_no')
        end_no = request.POST.get('end_no')
        try:
            with transaction.atomic():
                conn = mth.getMysqlConn()
                cur = conn.cursor()
                sql = " update card_inventory set username='{name}'" \
                      " where is_store=1 and shop_code='{shop}' and username=''" \
                      " and card_no >= {start} AND card_no <= {end}"\
                    .format(start=start_no,end=end_no,name=name,shop=shop_code)
                cur.execute(sql)
                res['msg'] = 1
                del request.session['postToken']

        except Exception as e:
            print(e)
            res['msg'] = 0

    return HttpResponse(json.dumps(res))

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def order2(request):
    shops = base.findShop()
    today = datetime.date.today()
    monthFirst = str(datetime.date.today().replace(day=1))
    today = str(today)

    shop = mth.getReqVal(request, 'shop', '')
    start = mth.getReqVal(request, 'start', monthFirst)
    end = mth.getReqVal(request, 'end', today)
    startTime = start + ' 00:00:00'
    endTime = end + ' 23:59:59'
    whereShop ='a.shop_code ="'+shop+'" ' if shop  else '1=1'

    conn = mth.getMysqlConn()
    cur = conn.cursor()
    saleSql = 'select a.order_sn,a.shop_code,a.add_time,a.buyer_company,b.pay_value,b.received_time,c.change_time ' \
              ' from orders as a inner join order_payment_info as b on a.order_sn = b.order_id left join order_payment_credit as c on a.order_sn = c.order_id ' \
              ' where a.add_time>="{start}" and a.add_time<="{end}" and b.pay_id=4 and {whereShop}' \
              ' order by a.order_sn' \
        .format(start=startTime, end=endTime,whereShop=whereShop)

    cur.execute(saleSql)
    listSale = cur.fetchall()

    cur.close()
    conn.close()
    return render(request, 'report/card/nopay/order2.html', locals())

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def index(request):
    shop = request.session.get('s_shopcode')
    vip_id = request.GET.get('vip_id')
    buyer = Vip.objects.filter(id=vip_id, status='1').values('company', 'person', 'tel', 'id').first()
    token = 'allow'
    request.session['postToken'] = token
    if request.method == 'POST':
        time_start = request.POST.get('start')
        time_end = request.POST.get('end')
        nextDay = datetime.datetime.strptime(time_end, '%Y-%m-%d') + datetime.timedelta(1)
        # kwargs = {}
        # kwargs.setdefault('vipOrders__status','1')
        # kwargs.setdefault('add_time__lte',nextDay)
        # kwargs.setdefault('add_time__gte',time_start)
        # orders = Orders.objects.filter(**kwargs)\
        #     .values('order_sn','paid_amount','add_time','buyer_name','buyer_tel','orderPays__is_pay')

        sql = '''SELECT `orders`.`order_sn`, `orders`.`paid_amount`,`orders`.`add_time`,`orders`.`buyer_name`,
                `orders`.`buyer_tel`,`order_payment_info`.`is_pay`
                FROM `orders`
                INNER JOIN `vip_order` ON (`orders`.`order_sn` = `vip_order`.`order_sn`)
                LEFT OUTER JOIN `order_payment_info` ON ( `orders`.`order_sn` = `order_payment_info`.`order_id` )
                WHERE ( `vip_order`.`disc_state` = 1 AND `vip_order`.`order_state` = 0 AND `orders`.`add_time` <= '{end}'
                AND `orders`.`add_time` >= '{start}' AND `orders`.`shop_code`='{shop}')'''\
            .format(start=time_start,end=nextDay,shop=shop)
        conn = mth.getMysqlConn()
        cur = conn.cursor()
        cur.execute(sql)
        orders = cur.fetchall()
        cur.close()
        conn.close()
    return render(request, 'vip/settlement.html', locals())

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def index(request):
    if request.method == 'POST':
        operator = str(request.session.get('s_uid', ''))
        shopcode = request.session.get('s_shopcode', '')

        today = datetime.datetime.today().replace(hour=23,minute=59,second=59)
        monthFirstDay = datetime.datetime.today().replace(day=1,hour=0,minute=0,second=0,microsecond=0)
        start = request.POST.get('start')
        if not start:
            start = monthFirstDay
        end = request.POST.get('end')
        endStr = datetime.datetime.strptime(end,'%Y-%m-%d')+datetime.timedelta(1)
        if not end:
            end = today
        buyerName = (mth.getReqVal(request,"buyerName","")).strip()
        #售卡数据
        queryWhereSale1 = " and b.buyer_name='"+buyerName+"'" if buyerName else ''
        queryWhereSale2 = " and b.add_time >='{start}' and b.add_time <='{end}' ".format(start=start, end=endStr)
        conn = mth.getMysqlConn()
        cur = conn.cursor()
        sqlSale="select a.pay_value,b.order_sn,b.operator_id,b.add_time,b.buyer_name,b.buyer_tel,b.paid_amount, " \
                " IFNULL(c.pay_value, 0) as credit_value, a.pay_id " \
            " from order_payment_info as a ,orders as b " \
            " left join (select sum(p.pay_value) as pay_value, p.order_id from order_payment_credit p group by p.order_id) as c " \
            " on b.order_sn = c.order_id " \
            " where a.order_id=b.order_sn and a.pay_id in (4, 3) and a.is_pay != '1' and b.shop_code ='"+shopcode+"'"+queryWhereSale1+queryWhereSale2
        cur.execute(sqlSale)
        listSale = cur.fetchall()

        cur.close()
        conn.close()

    return render(request, 'card/nopay/ordinary.html', locals())

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def order(request):
    today = datetime.date.today()
    today = datetime.datetime.strftime(today, '%Y-%m-%d')

    shop = mth.getReqVal(request,'shop','')
    start = mth.getReqVal(request,'start',today)
    end = mth.getReqVal(request,'end',today)
    startTime = start + ' 00:00:00'
    endTime = end + ' 23:59:59'

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

    saleSql = 'select a.order_sn,a.buyer_company,b.is_pay,b.pay_value as no_pay,' \
              'c.bank_name,c.pay_value,c.pay_id,c.bank_sn,c.pay_company,c.change_time ' \
              'from orders as a inner join order_payment_info as b on a.order_sn = b.order_id left join order_payment_credit as c on a.order_sn = c.order_id '\
              'where a.add_time>="{start}" and a.add_time<="{end}" and a.shop_code ="{shop}" and b.pay_id=4 ' \
              'order by b.is_pay,a.order_sn' \
              .format(start=startTime, end=endTime, shop=shop)

    cur.execute(saleSql)
    saleList = cur.fetchall()
    saleData,saleTotalPay,saleTotalNoPay= mergeData(saleList)


    data = saleData
    totalPay = saleTotalPay
    totalNoPay = saleTotalNoPay
    data = sorted(data,key=itemgetter('is_pay','order_sn'),reverse=True)
    return render(request, 'report/card/nopay/order.html', locals())

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def detail(request):
    today = datetime.date.today()
    page = mth.getReqVal(request, 'page', 1)
    shop_code = request.GET.get('shop')
    pay_id = request.GET.get('pay_id')

    shop = request.session.get('s_shopcode', '')
    role_id = request.session.get('s_roleid')
    if role_id == '9':
        shopsCode = mth.getCityShopsCode('T')
        if shop_code not in shopsCode:
            return render(request, '500.html', locals())
    elif role_id == '8':
        shopsCode = mth.getCityShopsCode('C')
        if shop_code not in shopsCode:
            return render(request, '500.html', locals())
    elif role_id == '10' or role_id == '2' or role_id == '12':
        if shop != shop_code:
            return render(request, '500.html', locals())


    start = request.GET.get('start', today)
    end = request.GET.get('end', today)
    endTime = str(datetime.datetime.strptime(end, '%Y-%m-%d').date() + datetime.timedelta(1))

    conn = mth.getMysqlConn()
    cur = conn.cursor()
    if pay_id == '3':
        sql = " select a.order_sn,a.action_type,b.is_pay,b.pay_value,c.bank_name,c.bank_sn,c.pay_company" \
              " from orders as a inner join order_payment_info as b on a.order_sn = b.order_id left join order_payment_credit as c on a.order_sn = c.order_id"\
              " where a.add_time>='{start}' and a.add_time<='{end}' and a.shop_code ='{shop}' and b.pay_id=3" \
              " order by b.is_pay,a.order_sn"\
              .format(start=start, end=endTime, shop=shop_code)
    else:
        sql = " select a.order_sn,a.action_type,a.buyer_name,a.buyer_tel,a.buyer_company,b.pay_value,b.is_pay" \
              " from orders as a ,order_payment_info as b" \
              " where a.order_sn = b.order_id and a.shop_code = '{shop}' and a.add_time >= '{start}'" \
              " and a.add_time <= '{end}' and b.pay_id = '{pay}'"\
            .format(shop=shop_code,start=start,end=end,pay=pay_id)

    cur.execute(sql)
    List = cur.fetchall()

    for item in List:
        if item['pay_value']:
            item['pay_value'] = float(item['pay_value'])
        else:
            item['pay_value'] = 0

    return render(request, 'report/card/saleGroupByShop/Detail.html', locals())

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def index(request):
    shops = base.findShop()
    if request.method == 'POST':
        shop = request.POST.get('shop','')
        if shop:
            whereShop = 'shop_code = "{shop}"'.format(shop=shop)
        else:
            whereShop ='1=1'
        start = request.POST.get('start')
        end = request.POST.get('end')
        nextDay = datetime.datetime.strptime(end,'%Y-%m-%d')+datetime.timedelta(1)

        sqlOrder = 'select a.rec_sn,a.shop_code,a.add_time,a.rec_name,SUM(b.card_nums) as card_nums from card_receive as a, receive_info as b ' \
                   'where a.rec_sn=b.rec_id and add_time >= "{start}" and add_time<="{nextDay}" and {whereShop} group by b.rec_id order by a.shop_code,a.add_time'\
                   .format(start=start,nextDay=nextDay,whereShop=whereShop)
        conn = mth.getMysqlConn()
        cur = conn.cursor()
        cur.execute(sqlOrder)
        orders = cur.fetchall()

    return render(request, 'report/card/sentCardGroupByOrder.html', locals())

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def index(request):
    role_id = request.session.get('s_roleid')
    shop = request.session.get('s_shopcode')
    role = request.session.get('s_roleid')
    user = request.session.get('s_uid')

    if request.method == 'POST':
        start = request.POST.get('start','')
        end = request.POST.get('end','')
        endTime = datetime.datetime.strptime(end,'%Y-%m-%d').date() + datetime.timedelta(1)
        whereStr ="WHERE a.add_time >='"+start+"' AND a.add_time <='"+str(endTime)+"' AND a.order_sn = b.order_id "

        if role in ('2','10') :
            whereStr += " AND a.shop_code ='"+shop+"'"
        conn = mth.getMysqlConn()
        cur = conn.cursor()

        shopsCode= ''
        if role_id == '9':
            shopsCode = mth.getCityShopsCode('T')
            shopsCodeStr = "'" + "','".join(shopsCode) + "'"
            whereStr += " AND a.shop_code IN (" + shopsCodeStr + ")"
        if role_id == '8':
            shopsCode = mth.getCityShopsCode('C')
            shopsCodeStr = "'" + "','".join(shopsCode) + "'"
            whereStr += " AND a.shop_code IN (" + shopsCodeStr + ")"

        sql = "SELECT b.pay_id,SUM(b.pay_value) AS pay_value FROM orders as a,order_payment_info as b "+whereStr+" GROUP BY b.pay_id ORDER BY b.pay_id"
        cur.execute(sql)
        payList = cur.fetchall()

        nopay = "SELECT SUM(b.pay_value) AS pay_value FROM orders as a,order_payment_info as b "+whereStr+" AND a.order_sn = b.order_id AND b.is_pay='0' AND b.pay_id=4"

        cur.execute(nopay)
        nopayDict = cur.fetchone()

        payTotal = 0.00
        for row in payList:
            payTotal += float(row['pay_value'])
    return render(request, 'report/card/saleGroupByPay.html', locals())

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def index(request):
    shop = request.session.get('s_shopcode')
    role = request.session.get('s_roleid')
    shops = Shops.objects.values('shop_code','shop_name','city').order_by('shop_code')
    if role in ('1','6','7') :
        shops = shops
    elif role == '8':#承德总部财务
        shops = shops.filter(city='C')
    elif role == '9':#唐山总部财务
        shops = shops.filter(city='T')
    else:
        shops = shops.filter(shop_code=shop)
    shops_len = len(shops)
    if request.method == 'POST':
        shopcode = mth.getReqVal(request, 'shopcode', '')
        keyword = mth.getReqVal(request, 'keyword', '')
        # 表单分页参数开始
        page = mth.getReqVal(request, 'page', 1)
        show_num = mth.getReqVal(request, 'show_num', 8)
        # 表单分页参数结束

        conn = mth.getMysqlConn()
        cur = conn.cursor()
        whereStr = "WHERE t.is_store = 1 "

        if role in ('2', '10'):
            whereStr += " AND t.shop_code ='" + shop + "'"


        if shopcode != '':
            whereStr += " AND t.shop_code ='" + shopcode + "'"
        else:
            shopsCode = ''
            if role == '9':
                shopsCode = mth.getCityShopsCode('T')
                shopsCodeStr = "'" + "','".join(shopsCode) + "'"
                whereStr += " AND t.shop_code IN (" + shopsCodeStr + ")"
            if role == '8':
                shopsCode = mth.getCityShopsCode('C')
                shopsCodeStr = "'" + "','".join(shopsCode) + "'"
                whereStr += " AND t.shop_code IN (" + shopsCodeStr + ")"

        if keyword != '':
            whereStr += " AND (c.username like '%" + keyword + "%' OR c.`name` like '%" + keyword + "%')"

        sql = """
SELECT
	t.shop_code,
	s.shop_name,
	c.username as user_code,
	IFNULL(c.`name`, '未分配') AS username,
	t.card_value,
	count(t.card_no) AS account,
	sum(CASE WHEN t.card_status = 2 THEN 1 ELSE 0 END) AS out_num,
	sum(CASE WHEN t.card_status = 1 THEN 1 ELSE 0 END) AS in_num
FROM
	card_inventory t LEFT JOIN shops s ON t.shop_code = s.shop_code
	LEFT JOIN cashier_list c ON t.shop_code = c.shop_code and t.username = c.username
"""+ whereStr +"""
GROUP BY
	t.shop_code,
	s.shop_name,
	c.username,
	c.`name`,
	t.card_value
                """
        cur.execute(sql)
        List = cur.fetchall()

        # 表单分页开始
        paginator = Paginator(List, show_num)

        try:
            List = paginator.page(page)

            if List.number > 1:
                page_up = List.previous_page_number
            else:
                page_up = 1

            if List.number < List.paginator.num_pages:
                page_down = List.next_page_number
            else:
                page_down = List.paginator.num_pages

        except Exception as e:
            print(e)
            # 表单分页结束

    return render(request, 'consign/list.html', locals())

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def date_detail(request):
    shop = mth.getReqVal(request, 'shopcode','')
    start = mth.getReqVal(request, 'start','')
    end = mth.getReqVal(request, 'end','')
    endTime = str(datetime.datetime.strptime(end,'%Y-%m-%d').date() + datetime.timedelta(1))
    page = mth.getReqVal(request, 'page', 1)

    conn = mth.getMysqlConn()
    cur = conn.cursor()
    sale_sql = "select DATE_FORMAT(a.add_time, '%Y-%m-%d') as add_time,b.pay_id,sum(b.pay_value) as pay_value " \
               " from orders as a, order_payment_info as b" \
               " where a.order_sn = b.order_id and a.add_time>='{start}' and a.add_time<='{end}' and a.shop_code ='{shop}'" \
               " group by DATE_FORMAT(a.add_time, '%Y-%m-%d'),b.pay_id" \
                .format(start=start,end=endTime,shop=shop)
    cur.execute(sale_sql)
    data_sale = cur.fetchall()

    sale_disc_sql="select DATE_FORMAT(add_time, '%Y-%m-%d') as add_time," \
                  "SUM(case when disc_amount>=y_cash then disc_amount else diff_price+disc_amount end) as disc, " \
                  "SUM(y_cash) as disc_cash,"  \
                  "SUM(case when disc_amount>=y_cash then disc_amount-y_cash else disc_amount+diff_price-y_cash end) as disc_card " \
                  "from orders " \
                  "where add_time>='{start}' and add_time<='{end}' and shop_code ='{shop}' "  \
                  "group by DATE_FORMAT(add_time, '%Y-%m-%d') " \
                    .format(start=start, end=endTime, shop=shop)
    cur.execute(sale_disc_sql)
    data_sale_disc = cur.fetchall()

    change_sql = "select DATE_FORMAT(a.add_time, '%Y-%m-%d') as add_time,b.pay_id,sum(b.pay_value) as pay_value " \
               " from order_change_card as a, order_change_card_payment as b" \
               " where a.order_sn = b.order_id and a.add_time>='{start}' and a.add_time<='{end}' and a.shop_code ='{shop}'" \
               " group by DATE_FORMAT(a.add_time, '%Y-%m-%d'),b.pay_id" \
                .format(start=start,end=endTime,shop=shop)
    cur.execute(change_sql)
    data_change = cur.fetchall()

    change_disc_sql = "select DATE_FORMAT(add_time, '%Y-%m-%d') as add_time," \
                      "SUM(case when disc>=disc_cash then disc else disc_pay+disc end) as disc," \
                    "SUM(disc_cash) as disc_cash," \
                    "SUM(case when disc>=disc_cash then disc-disc_cash else disc+disc_pay-disc_cash end) as disc_card " \
                    "from order_change_card " \
                    "where add_time>='{start}' and add_time<='{end}' and shop_code ='{shop}' " \
                    "group by  DATE_FORMAT(add_time, '%Y-%m-%d') " \
        .format(start=start, end=endTime, shop=shop)
    cur.execute(change_disc_sql)
    data_change_disc = cur.fetchall()

    fill_sql = "select DATE_FORMAT(add_time, '%Y-%m-%d') as add_time,sum(diff_price) as pay_value" \
               " from order_up_card" \
               " where add_time>='{start}' and add_time<='{end}' and shop_code ='{shop}'" \
               " group by  DATE_FORMAT(add_time, '%Y-%m-%d') " \
                .format(start=start, end=endTime, shop=shop)
    cur.execute(fill_sql)
    data_fill = cur.fetchall()

    dates = mth.dateRange(start,end)

    res_list = []
    paymentsRate = Payment.objects.values('id', 'dis_rate').filter(dis_rate__gte=0)
    paymentsRateDict = {item['id']: float(item['dis_rate']) for item in paymentsRate}
    for date in dates:
        item = {'date': '',
                'disc': 0, 'disc_6': 0, 'disc_7': 0, 'disc_8': 0, 'disc_10': 0, 'disc_11': 0, 'disc_cash': 0,
                'disc_card': 0,
                'inSub': 0, 'pay_1': 0, 'pay_2': 0, 'pay_3': 0, 'pay_4': 0, 'pay_5': 0, 'pay_6': 0, 'pay_7': 0,
                'pay_8': 0,
                'pay_9': 0, 'pay_10': 0, 'pay_11': 0, }
        item['date'] = date
        for sale in data_sale:
            if sale['add_time'] == date:
                # 横向各门店售卡汇总赋值
                # pay_id = sale['pay_id']
                if sale['pay_id'] == 1:
                    item['pay_1'] += float(sale['pay_value'])
                    item['inSub'] += float(sale['pay_value'])
                if sale['pay_id'] == 2:
                    item['pay_2'] += float(sale['pay_value'])
                    item['inSub'] += float(sale['pay_value'])
                if sale['pay_id'] == 3:
                    item['pay_3'] += float(sale['pay_value'])
                    item['inSub'] += float(sale['pay_value'])
                if sale['pay_id'] == 4:
                    item['pay_4'] += float(sale['pay_value'])
                    item['inSub'] += float(sale['pay_value'])
                if sale['pay_id'] == 5:
                    item['pay_5'] += float(sale['pay_value'])
                    item['inSub'] += float(sale['pay_value'])
                if sale['pay_id'] == 6:
                    item['pay_6'] += float(sale['pay_value'])
                    item['disc_6'] += float(sale['pay_value']) * paymentsRateDict[6]
                    item['inSub'] += float(sale['pay_value'])
                if sale['pay_id'] == 7:
                    item['pay_7'] += float(sale['pay_value'])
                    item['disc_7'] += float(sale['pay_value']) * paymentsRateDict[7]
                    item['inSub'] += float(sale['pay_value'])
                if sale['pay_id'] == 8:
                    item['pay_8'] += float(sale['pay_value'])
                    item['disc_8'] += float(sale['pay_value']) * paymentsRateDict[8]
                    item['inSub'] += float(sale['pay_value'])
                if sale['pay_id'] == 9:
#.........这里部分代码省略.........

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def month_pay4(request):
    """
    :param request:
    :return:
    """
    shops = mth.getCityShopsCode()
    if request.method == 'POST':
        postStart = request.POST.get('start')
        if postStart<'2017-06':
            postStart = '2017-06'
        postEnd = request.POST.get('end')

        start = '{postStart}-01 00:00:00'.format(postStart=postStart)
        endTemp = datetime.datetime.strptime(postEnd,'%Y-%m')
        if endTemp.month == 12:
            end = datetime.datetime(endTemp.year+1, 1, 1)
        else:
            end = datetime.datetime(endTemp.year, endTemp.month + 1, 1)

        shop_code = request.POST.get('shop','')
        parameterShop = '1=1'
        if shop_code:
            parameterShop = 'a.shop_code="{shop}"'.format(shop=shop_code)

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

        #在起始日期之前赊销，在起始日期之前未到账
        sqlBeforeNoPay = 'select sum(b.pay_value) as total from orders as a, order_payment_info as b' \
                         ' where a.add_time>="2017-06-01 00:00:00" and a.add_time<="{start}" and {shop}' \
                         ' and b.pay_id =4 and is_pay=0 and a.order_sn = b.order_id' \
                        .format(start=start,shop=parameterShop)
        cur.execute(sqlBeforeNoPay)
        beforeNoPay1 = cur.fetchone()
        totalNoPayBefore1 = beforeNoPay1['total']
        totalNoPayBefore1 = totalNoPayBefore1 if totalNoPayBefore1 else 0
        #在起始日期之前赊销，在起始日期之后到账
        sqlBeforeNoPay2 = 'select sum(b.pay_value) as total' \
                          ' from order_payment_credit as b LEFT JOIN orders as a ON b.order_id = a.order_sn' \
                          ' where a.add_time>="2017-06-01 00:00:00" and a.add_time<="{start}"' \
                          ' and b.change_time>="{start}" and {shop} and b.pay_id_old =4' \
                          .format(start=start, end=end, shop=parameterShop)
        cur.execute(sqlBeforeNoPay2)
        beforeNoPay2 = cur.fetchone()
        totalNoPayBefore2 = beforeNoPay2['total']
        totalNoPayBefore2 = totalNoPayBefore2 if totalNoPayBefore2 else 0
        #历史累计
        totalNoPayBefore = totalNoPayBefore1 +totalNoPayBefore2

        #赊出记录
        sqlNoPay =' select a.order_sn,a.buyer_company,a.shop_code,a.add_time,b.pay_id ,b.pay_value' \
                  ' from orders as a,order_payment_info as b' \
                  ' where a.add_time>="2017-06-01 00:00:00" and a.add_time>="{start}" and a.add_time<="{end}" and {shop}' \
                  ' and b.pay_id =4 and a.order_sn = b.order_id' \
                  ' order by a.add_time'.format(start=start, end=end,shop=parameterShop)
        cur.execute(sqlNoPay)
        noPayList = cur.fetchall()
        #还款记录
        sqlPaid = ' select a.order_sn,a.shop_code,b.pay_company,b.change_time,b.pay_id ,b.pay_value' \
                  ' from order_payment_credit as b LEFT JOIN orders as a' \
                  ' ON b.order_id = a.order_sn' \
                  ' where a.add_time>="2017-06-01 00:00:00" and b.change_time>="{start}" and {shop}' \
                  ' and b.change_time<="{end}" and b.pay_id_old=4' \
                  ' order by b.change_time'.format(start=start, end=end,shop=parameterShop)
        cur.execute(sqlPaid)
        paidList = cur.fetchall()

        noPayList = splitData('0',noPayList,start,end)
        paidList = splitData('1',paidList,start,end)
        dataSelect = noPayList + paidList
        totalNoPay = float(totalNoPayBefore)
        if len(dataSelect)>0:
            dataSorted = sorted(dataSelect, key=itemgetter('time'), reverse=False)
            totalPay,MonthNoPay,MonthPay,index, = 0,0,0,0
            resData,monthSubTimeList = [],[]

            for row in dataSorted:
                if index == 0:
                    rowTimePre = rowTimeNow
                else:
                    rowTimeNow = row['time']
                    rowTimePre = dataSorted[index-1]['time']
                if isNextMonth(rowTimeNow,rowTimePre) and (row['time'].date()+datetime.timedelta(days=-1) not in monthSubTimeList):
                    time = datetime.datetime(rowTimeNow.year,rowTimeNow.month,1)+datetime.timedelta(days=-1)
                    if time>=datetime.datetime.strptime(start,'%Y-%m-%d %H:%M:%S'):
                        monthSubitem = MonthSub(MonthPay,MonthNoPay,time)
                        resData.append(monthSubitem)
                        monthSubTimeList.append(time)
                        MonthNoPay = 0
                        MonthPay = 0
                if 'is_pay' in row:
                    if row['is_pay'] == '1':
                        MonthPay += float(row['pay'])
                        totalPay += float(row['pay'])
                        totalNoPay += -float(row['pay'])
                        row['sub'] = totalNoPay
                    else:
                        MonthNoPay += float(row['noPay'])
                        totalNoPay += float(row['noPay'])
                        row['sub'] = totalNoPay
#.........这里部分代码省略.........

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getMysqlConn [as 别名]
def index(request):
    shopcode = request.session.get('s_shopcode','')
    operator = request.session.get('s_uid','')
    rates = request.session.get('s_rates')
    # 在服务端session中添加key认证，避免用户重复提交表单
    token = 'allow'  # 可以采用随机数
    request.session['postToken'] = token

    today = str(datetime.date.today())
    start = today
    end = today
    if request.method == 'POST':
        shopcode = request.session.get('s_shopcode','')
        departName = (request.POST.get('departName','')).strip()
        departCode = (request.POST.get('departCode','')).strip()
        state = request.POST.get('state','')
        start = request.POST.get('start','')
        end = request.POST.get('end','')

        nextDay = datetime.datetime.strptime(end,'%Y-%m-%d')+datetime.timedelta(1)

        kwargs = {}
        whereStr =''
        kwargs.setdefault('operator',operator)
        kwargs.setdefault('is_paid','0')
        whereStr +=' and is_paid="0"'

        if departName:
            whereStr +=' and borrow_depart="'+departName+'"'
            kwargs.setdefault('borrow_depart',departName)
        if departCode:
            whereStr +=' and borrow_depart_code="'+departCode+'"'
            kwargs.setdefault('borrow_depart_code',departCode)
        if start:
            whereStr +=' and add_time>="'+str(start)+'"'
            kwargs.setdefault('add_time__gte',start)
        if end:
            whereStr +=' and add_time<="'+str(nextDay)+'"'
            kwargs.setdefault('add_time__lte',nextDay)

        kwargs.setdefault('shopcode',shopcode)

        #查询借卡明细
        listSale = OrderBorrow\
                .objects\
                .values('order_sn','order_val','order_num','operator','borrow_depart','borrow_depart_code','borrow_phone','borrow_name','add_time','is_paid')\
                .filter(**kwargs).order_by('order_sn')

        #查询退卡明细
        conn = mth.getMysqlConn()
        cur = conn.cursor()
        sqlBack = 'select a.order_sn,a.operator,a.borrow_depart,a.borrow_depart_code,SUM(b.card_balance) as back_val,COUNT(b.card_no) as back_num,b.back_time' \
                  ' from order_borrow as a,order_borrow_info as b ' \
                  ' where a.shopcode = "'+shopcode+'" and a.order_sn=b.order_sn and b.is_back="1"'+whereStr+' group by(a.order_sn)'
        cur.execute(sqlBack)
        listBack = cur.fetchall()


        #查询未退卡明细
        sqlCardNoBack = 'select a.order' \
                        '_sn,b.card_no as cardId,b.card_balance as cardVal from order_borrow as a,order_borrow_info as b ' \
                  ' where a.shopcode ="'+shopcode+'" and a.order_sn=b.order_sn and b.is_back is null '+whereStr+''
        cur.execute(sqlCardNoBack)

        listNoBack = cur.fetchall()
        if len(listNoBack)>0:
            for item in listNoBack:
                item['cardVal'] = str(item['cardVal'])
        else:
            listNoBack=[]

        cur.close()
        conn.close()
    return render(request, 'card/borrow/pay.html', locals())