Beispiel #1
0
def search_order(price_start, price_end, start_date, end_date, is_pay):
    # 组装sql
    sql = "SELECT d.order_code, d.total_money,case when d.order_status=2 then 'haspay' WHEN d.order_status=1 THEN 'unpay' else 'other' end " \
          " from ({tables})d WHERE d.create_date >='{create_date_start}' AND d.create_date <='{create_date_end}' AND d.total_money >={price_start} " \
          "AND d.total_money <={price_end}  AND d.delete_flag =0 AND d.is_pay={is_pay} ORDER  BY d.create_date"
    table = ""
    for i in range(64):
        table += "select * from doing_order_" + StrUtil.format(i)
        if (i < 63):
            table += " UNION ALL "
    sql = sql.format(tables=table,
                     create_date_start=start_date,
                     create_date_end=end_date,
                     price_start=price_start,
                     price_end=price_end,
                     is_pay=is_pay)
    print(sql)
    for i in range(16):
        db_url = DbUtil.get_db_ip(i)
        module = StrUtil.format(i)
        table = "sibu_directsale_order_" + module
        db = pymysql.connect(db_url, "root", "Aa123456", table)
        cursor = db.cursor()
        cursor.execute(sql)
        temp = cursor.fetchall()
        if (temp is not None):
            print(temp)
def scan_exception_address():
    sql = "SELECT m.phone,m.user_name,a.member_id,a.province,a.city,a.district, a.detail FROM("
    for i in range(db_constants.TABLE_SIZE):
        sql += " SELECT * FROM member_address_" + StrUtil.format(i)
        if (i < db_constants.TABLE_SIZE - 1):
            sql += " UNION ALL "
    sql += ")a join sibu_directsale.member m ON a.member_id = m.member_id " \
           "WHERE  (a.province LIKE'上海市' or a.province LIKE '天津市' or a.province LIKE '重庆市' or a.province LIKE '深圳市') and a.city='县'"
    print(sql)
    for i in range(db_constants.DB_SIZE):
        db_url = DbUtil.get_db_ip(i)
        module = StrUtil.format(i)
        table = "sibu_directsale_member_" + module
        db = pymysql.connect(db_url,
                             "root",
                             "Aa123456",
                             table,
                             charset="utf8mb4")
        cursor = db.cursor()
        cursor.execute(sql)
        temp = cursor.fetchall()
        if (temp is not None):
            for i in temp:
                print(i)
        cursor.close()
Beispiel #3
0
def scanNoTradeIdOrder(start_date, end_date):
    exec_trade_sql = 'SELECT c.order_code FROM('
    for i in range(db_constants.TABLE_SIZE):
        index = StrUtil.format(i)
        exec_trade_sql += "SELECT * FROM sibu_directsale_order_%s.doing_order_"+index+" a WHERE a.payment_number IS NULL" \
                          " AND a.order_code NOT IN (select b.order_code " \
                          " FROM sibu_directsale_order_log_%s.trade_log_"+index+" b" \
                          " WHERE b.trade_id not LIKE 'T%')"
        if (i < db_constants.TABLE_SIZE - 1):
            exec_trade_sql += ' union all '
    exec_trade_sql += ") c where c.payment_number is null and c.pay_date>='" + start_date + "' AND c.pay_date<='" + end_date + "'"
    fo = open("resources/test.txt", "r+")
    for i in range(db_constants.DB_SIZE):
        module = StrUtil.format(i)
        db_url = DbUtil.get_db_ip(module)
        db = pymysql.connect(host=db_url,
                             user="******",
                             passwd="Aa123456",
                             db="sibu_directsale",
                             charset="utf8mb4")
        cursor = db.cursor()
        cursor.execute(exec_trade_sql.replace("%s", module))
        result = cursor.fetchall()
        for i in result:
            fo.write(i[0] + "\n")
def repair_member_address():
    result = 0
    for i in range(db_constants.DB_SIZE):
        db_url = DbUtil.get_db_ip(i)
        module = StrUtil.format(i)
        table = "sibu_directsale_member_" + module
        for i in range(db_constants.TABLE_SIZE):
            sql = " update member_address_" + StrUtil.format(i)
            sql += " a set a.province='北京', a.city='北京市'" \
                   " WHERE a.province LIKE '北京市' AND a.city LIKE '县' AND a.city NOT LIKE '市辖区'"
            db = pymysql.connect(db_url,
                                 "root",
                                 "Aa123456",
                                 table,
                                 charset="utf8mb4")
            cursor = db.cursor()
            result += cursor.execute(sql)
            if (result <= 0):
                continue
            try:
                db.commit()
                print("success: " + str(result))
            except:
                print(sql)
                db.rollback()
            db.close()
Beispiel #5
0
def check_bank_deal(deal_result):
    # 查询member_id
    for i in range(db_constants.DB_SIZE):
        db_index = StrUtil.format(i)
        reuslt_list = query_member_id(db_index, deal_result)
        if len(reuslt_list) != 0:
            update_bank_deal(db_index, reuslt_list)
Beispiel #6
0
def get_lose_member_Sql():
    sql = " SELECT b.order_id,b.member_id,b.phone,b.order_code FROM ("
    for i in range(db_constants.TABLE_SIZE):
        sql += " SELECT * FROM sibu_directsale_order_{db_index}.doing_order_%s" % (
            StrUtil.format(i))
        if (i < db_constants.TABLE_SIZE - 1):
            sql += " UNION ALL"
    sql += " )b WHERE b.member_id not in (SELECT  m.member_id FROM sibu_directsale.member m)" \
           " GROUP BY b.member_id"
    return sql
Beispiel #7
0
def update_trade_id_by_create(start_date, end_date):
    exec_trade_sql = 'SELECT a.order_code, a.trade_id FROM('
    for i in range(db_constants.TABLE_SIZE):
        index = StrUtil.format(i)
        exec_trade_sql += " SELECT * FROM trade_log_" + index
        if (i < db_constants.TABLE_SIZE - 1):
            exec_trade_sql += ' union all '
    exec_trade_sql += ")a WHERE a.trade_id NOT LIKE ('T%') AND a.create_date>='" + start_date + "' AND a.create_date<='" + end_date + "'"
    for i in range(db_constants.DB_SIZE):
        db_url = DbUtil.get_db_ip(StrUtil.format(i))
        db = pymysql.connect(host=db_url,
                             user="******",
                             passwd="Aa123456",
                             db="sibu_directsale_order_log_" +
                             StrUtil.format(i),
                             charset="utf8mb4")
        cursor = db.cursor()
        cursor.execute(exec_trade_sql)
        update_order_trade(cursor.fetchall())
        cursor.close()
Beispiel #8
0
def get_tranfer_Sql():
    sql = " SELECT b.transfer_code,b.transfer_id,b.transfer_money,b.out_user_name,b.out_phone,m.user_name in_user_name,m.phone in_phone, b.transfer_memo,b.create_time " \
          " FROM (SELECT a.transfer_code,a.transfer_id,a.transfer_money * 0.01 transfer_money, m.user_name out_user_name, m.phone out_phone, a.in_member_id," \
          " a.transfer_memo, a.create_time FROM ("
    for i in range(db_constants.TABLE_SIZE):
        sql += " SELECT * FROM sibu_directsale_profit_{db_index}.member_transfer_%s" % (
            StrUtil.format(i))
        if (i < db_constants.TABLE_SIZE - 1):
            sql += " UNION ALL"
    sql += ") a JOIN sibu_directsale.member m ON a.out_member_id = m.member_id WHERE a. STATUS = 1  group by a.transfer_code) b" \
           "  JOIN sibu_directsale.member m ON b.in_member_id = m.member_id group by b.transfer_code"
    return sql
Beispiel #9
0
def delete_member_deal(ids):
    sql = "UPDATE sibu_directsale_profit_{db_index}.member_deal d" \
          " SET d.delete_flag = 1" \
          " WHERE" \
          " d.deal_status IN (1,2)" \
          " AND d.apply_member_id IN {ids}"
    for db_index in range(db_constants.DB_SIZE):
        db_index = StrUtil.format(db_index)
        session = DbUtil.get_db_session(ip=DbUtil.get_db_ip(db_index))
        try:
            result = session.execute(sql.format(db_index=db_index, ids=ids[0]))
            session.commit()
        except:
            session.rollback()
Beispiel #10
0
def query_member_deal(ids):
    sql = " SELECT d.deal_code,d.apply_member_id,d.deal_status,d.delete_flag,d.apply_money*0.01 as apply_money,m.phone" \
          " FROM sibu_directsale_profit_{db_index}.member_deal d, sibu_directsale.member m" \
          " WHERE d.deal_status IN (1,2) " \
          " AND d.apply_member_id IN {ids} AND  m.member_id = d.apply_member_id"
    result_list = []
    for db_index in range(db_constants.DB_SIZE):
        db_index = StrUtil.format(db_index)
        session = DbUtil.get_db_session(ip=DbUtil.get_db_ip(db_index))
        result = session.query(Deal.BillDeal).from_statement(
            sql.format(db_index=db_index, ids=ids)).all()
        result_list.extend(result)
    for deal in result_list:
        print(deal.deal_code, deal.apply_member_id, str(deal.deal_status),
              str(deal.delete_flag), str(deal.apply_money), str(deal.phone))
Beispiel #11
0
def get_tranfer_records():
    '''
    获取所有转入,转出记录
    :return:
    '''
    sql = get_tranfer_Sql()
    record_list = list()
    for i in range(db_constants.DB_SIZE):
        db_index = StrUtil.format(i)
        ip = DbUtil.get_db_ip(db_index)
        session = DbUtil.get_db_session(ip)
        exc_sql = sql.format(db_index=db_index)
        print(exc_sql)
        result = session.query(Order.TransferRecord) \
            .from_statement(exc_sql).all()
        record_list.extend(result)
    return record_list
Beispiel #12
0
def get_lose_member():
    '''
    扫描已经下单但是该用户在member表中不存在的member_id和iphone
    :return:
    '''
    sql = get_lose_member_Sql()
    order_list = list()
    for i in range(db_constants.DB_SIZE):
        db_index = StrUtil.format(i)
        ip = DbUtil.get_db_ip(db_index)
        session = DbUtil.get_db_session(ip)
        exc_sql = sql.format(db_index=db_index)
        print(exc_sql)
        result = session.query(Order.DoingOrder) \
            .from_statement(exc_sql).all()
        order_list.extend(result)
    for r in order_list:
        print("%s,%s,%s,%s" % (r.order_id, r.member_id, r.phone, r.order_code))
Beispiel #13
0
def query_deal(start_date, end_date, apply_type):
    deals = list()
    db_url = "mysql+pymysql://root:Aa123456@{ip}:3306/sibu_directsale"
    sql = init_sql(start_date, end_date, apply_type)
    for i in range(db_constants.DB_SIZE):
        db_index = StrUtil.format(i)
        ip = DbUtil.get_db_ip(db_index)
        session = DbUtil.get_db_session(ip)
        exc_sql = sql.format(db_index=db_index)
        print(exc_sql)
        # try:
        result = session.query(Deal.BillDeal) \
            .from_statement(exc_sql).all()
        deals.extend(result)
        # except BaseException as err:
        #     print(err)
        session.close()
    return deals