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()
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()
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)
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
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()
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
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()
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))
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
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))
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