Python Method.getCityShops方法代码示例

# 需要导入模块: from sellcard.common import Method [as 别名]
# 或者: from sellcard.common.Method import getCityShops [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 getCityShops [as 别名]
def getData(role_id,shop,start,endTime):
    conn = mth.getMysqlConn()
    cur = conn.cursor()
    if role_id == '1' or role_id == '6':
        shops = mth.getCityShops()
        shopsCode = mth.getCityShopsCode()
    elif role_id == '9':
        shops = mth.getCityShops('T')
        shopsCode = mth.getCityShopsCode('T')
    elif role_id == '8':
        shops = mth.getCityShops('C')
        shopsCode = mth.getCityShopsCode('C')
    elif role_id == '10' or role_id == '2' or role_id == '12' or role_id == '11':
        shops = base.findShop(shop)
        shopsCode = [shop]

    shopsCodeStr = "'" + "','".join(shopsCode) + "'"
    # shopsCodeStr = "('C003')"
    # saleDiscGroupByShop = 'SELECT a.shop_code, SUM(CASE WHEN b.card_balance<= a.diff_price THEN a.y_cash+b.card_balance ELSE a.disc_amount END ) AS disc,' \
    #                       'SUM(a.y_cash) AS disc_cash,SUM(case WHEN b.card_balance<= a.diff_price THEN b.card_balance ELSE b.card_balance-a.diff_price END) AS disc_card ' \
    #                       'FROM orders AS a ' \
    #                       'LEFT JOIN (SELECT  order_id,SUM(card_balance) AS card_balance ' \
    #                       'FROM order_info WHERE card_attr="2" GROUP BY order_id,card_attr) AS b ' \
    #                       'ON b.order_id = a.order_sn ' \
    #                       'WHERE a.add_time >= "{start}" AND a.add_time <= "{end}" AND a.shop_code IN ({shopsCodeStr}) group by a.shop_code '\
    #                      .format(start=start, end=endTime,shopsCodeStr=shopsCodeStr)

    saleDiscGroupByShop = 'select shop_code,' \
                          '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 in ({shopsCodeStr}) ' \
                          'group by shop_code ' \
                          .format(start=start, end=endTime, shopsCodeStr=shopsCodeStr)
    cur.execute(saleDiscGroupByShop)
    saleDiscList = cur.fetchall()

    salePayGroupByShop = 'select ord.shop_code,info.pay_id,info.change_time,SUM(info.pay_value) as pay_value ' \
                         'from orders as ord , order_payment_info as info ' \
                         'where ord.add_time>="{start}" and ord.add_time<="{end}" and ord.shop_code in ({shopsCodeStr})' \
                         ' and ord.order_sn = info.order_id ' \
                         'group by ord.shop_code,info.pay_id,info.change_time ' \
                        .format(start=start, end=endTime, shopsCodeStr=shopsCodeStr)
    cur.execute(salePayGroupByShop)
    salePayList = cur.fetchall()

    fillList = OrderUpCard.objects \
        .values('shop_code') \
        .filter(add_time__gte=start, add_time__lte=endTime, shop_code__in=shopsCode) \
        .annotate(fill=Sum('diff_price')) \
        .order_by('shop_code')

    # changeDiscGroupByShop = 'SELECT a.shop_code, SUM(CASE WHEN b.card_balance<= a.disc_pay THEN a.disc_cash+b.card_balance ELSE a.disc END) AS disc,' \
    #                         'SUM(a.disc_cash) AS disc_cash,SUM(case WHEN b.card_balance<= a.disc_pay THEN b.card_balance ELSE b.card_balance-a.disc_pay END) AS disc_card ' \
    #                         'FROM order_change_card AS a '\
    #                         'LEFT JOIN (SELECT  order_sn,sum(card_balance) as card_balance ' \
    #                         'FROM order_change_card_info where type="1" group by order_sn,type)'\
    #                         'AS b on b.order_sn = a.order_sn '\
    #                         'WHERE a.add_time >= "{start}" AND a.add_time <= "{end}" AND a.shop_code IN ({shopsCodeStr}) ' \
    #                         'group by a.shop_code ' \
    #                         .format(start=start, end=endTime,shopsCodeStr=shopsCodeStr)

    changeDiscGroupByShop = 'select shop_code,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 in ({shopsCodeStr}) ' \
                            'group by shop_code ' \
                            .format(start=start, end=endTime, shopsCodeStr=shopsCodeStr)
    cur.execute(changeDiscGroupByShop)
    changeDiscList = cur.fetchall()
    changePayGroupByShop = 'select ord.shop_code ,info.pay_id,info.change_time,SUM(info.pay_value) as pay_value ' \
                           'from order_change_card as ord , order_change_card_payment as info ' \
                           'where ord.add_time>="{start}" and ord.add_time<="{end}" and shop_code in ({shopsCodeStr}) and ord.order_sn = info.order_id ' \
                           'group by ord.shop_code,info.pay_id,info.change_time' \
                            .format(start=start, end=endTime, shopsCodeStr=shopsCodeStr)
    cur.execute(changePayGroupByShop)
    changePayList = cur.fetchall()

    paymentsRate = Payment.objects.values('id', 'dis_rate').filter(dis_rate__gte=0)
    paymentsRateDict = {item['id']: float(item['dis_rate']) for item in paymentsRate}
    totalDict = {'discTotal': 0,
                 'discCashTotal': 0, 'total_disc_6': 0, 'total_disc_7': 0, 'total_disc_8': 0, 'total_disc_10': 0,
                 'total_disc_11': 0, 'total_disc_qita': 0, 'discCardTotal': 0,
                 'inSubTotal': 0, 'total_1': 0, 'total_2': 0, 'total_3': 0,
                 'total_4': 0, 'total_5': 0, 'total_6': 0, 'total_7': 0, 'total_8': 0, 'total_9': 0, 'total_10': 0,
                 'total_11': 0, }

    dataList = []
    for i in range(0, len(shops)):
        # shopcode = shops[i]['shop_code']
        if shops[i]['shop_code'] != 'ZBTG':
            item = {'shop_code': '',
                    '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['shop_code'] = shops[i]['shop_code']
#.........这里部分代码省略.........