def query_member_id(db_index, deals):
ip = DbUtil.get_db_ip(db_index)
session = DbUtil.get_db_session(ip=ip)
exc_sql = "SELECT c.member_id,d.deal_id"\
" FROM" \
" sibu_directsale_member_{db_index}.member_account c" \
" JOIN sibu_directsale_profit_{db_index}.member_deal d ON c.member_id = d.apply_member_id"\
" WHERE c.bank_account = '{bank_account}'" \
" AND c.bank_user = '******'" \
" AND d.apply_money = {apply_money}" \
" AND d.apply_type = 1"
result_list = []
for deal in deals:
result = session.query(Deal.BillDealResult) \
.from_statement(exc_sql.format(db_index=db_index,bank_account=deal.bank_account,bank_user=deal.bank_user, apply_money=deal.apply_money)).first()
if result is None:
continue
deal.deal_id = result.deal_id
deal.member_id = result.member_id
result_list.append(deal)
for d in result_list:
deals.remove(d)
session.close()
return result_list
def copy_tradenum(order_list):
for i in order_list:
module = DbUtil.get_mod_16(DbUtil.get_module_by_order_code(i))
table_index = DbUtil.get_mode_64(DbUtil.get_module_by_order_code(i))
db_url = DbUtil.get_db_ip(module)
db = pymysql.connect(db_url, "root", "Aa123456", "sibu_directsale")
cursor = db.cursor()
sql = "SELECT t.trade_id\
FROM sibu_directsale_order_log_%s.trade_log_%s t \
WHERE t.order_code = '%s' ORDER BY create_date ASC LIMIT 1" % (
module, table_index, i)
cursor.execute(sql)
result = cursor.fetchone()
if result is None:
print("failed: trade is none")
continue
elif result[0] is None or str(result[0]).startswith("T"):
print("failed: " + i + "-" + result[0])
continue
sql = "UPDATE sibu_directsale_order_%s.doing_order_%s o SET o.payment_number = %s WHERE o.order_code = '%s'" % (
module, table_index, result[0], i)
cursor.execute(sql)
try:
db.commit()
print("success: " + i + "-" + result[0])
except:
print("failed: " + i)
db.rollback()
db.close()
def update_success(db_index, deal):
exec_sql = "UPDATE sibu_directsale_profit_%s.member_deal SET"\
" deal_status = %d," \
" update_date = now()," \
" give_user_id = 1," \
" give_date = now()," \
" give_invoice = '%s'," \
" service_charge_money = 0," \
" proxy_tax_money = 0," \
" back_money = 0," \
" give_money = apply_money," \
" deduct_tax_money = 0," \
" remainder_money = 0" \
" WHERE" \
" delete_flag = 0" \
" AND" \
" deal_id = '%s'" \
" AND" \
" deal_status = 2" % (db_index,deal.deal_status,deal.give_invoice,deal.deal_id)
session = DbUtil.get_db_session(ip=DbUtil.get_db_ip(db_index))
session.begin(subtransactions=True)
try:
result = session.execute(exec_sql)
if (result.rowcount == 1):
session.commit()
else:
session.rollback()
except Exception as err:
print(err)
session.rollback()
session.close()
def update_failed(db_index, deal):
# 更新提现状态
exec_deal_sql = "UPDATE sibu_directsale_profit_%s.member_deal SET" \
" deal_status = %d," \
" update_date = now()," \
" give_user_id = 1," \
" give_date = now()," \
" give_invoice = '%s'," \
" service_charge_money = 0," \
" proxy_tax_money = 0," \
" back_money = 0," \
" give_money = apply_money," \
" deduct_tax_money = 0," \
" remainder_money = 0" \
" WHERE" \
" delete_flag = 0" \
" AND" \
" deal_id = '%s'" \
" AND" \
" deal_status = 2" % (db_index,deal.deal_status, deal.give_invoice, deal.deal_id)
profit_total_sql = "UPDATE sibu_directsale_profit_%s.member_profit_total " \
" SET available_money = available_money +%d," \
" deal_sum_money = deal_sum_money -%d WHERE member_id ='%s'" % (db_index,deal.apply_money,deal.apply_money,deal.member_id)
session = DbUtil.get_db_session(ip=DbUtil.get_db_ip(db_index))
try:
session.execute(exec_deal_sql)
session.execute(profit_total_sql)
session.commit()
except:
session.rollback()
def get_member_id(phone_list):
session = DbUtil.get_db_session(ip=DbUtil.get_db_ip("master"))
fo = open("../resources/text.txt", "w+")
fo.write('[')
for i in phone_list:
result = session.execute(
"select member_id from sibu_directsale.member WHERE phone =%s" % i)
print(result.cursor._rows[0][0])
fo.write('\'' + result.cursor._rows[0][0] + '\',')
fo.write(']')
fo.close()
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 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 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 add_three_month(member_list):
failed_count = 0
success_count = 0
sql = " UPDATE sibu_directsale_profit_{module}.member_deal d SET d.apply_date = DATE_ADD(d.apply_date, INTERVAL 3 MONTH ) WHERE d.apply_month = {profit_month} " \
"AND d.apply_money = {apply_money}*100 AND d.apply_member_id = '{member_id}' AND d.deal_status = 8"
for i in member_list:
apply_money = i[2]
profit_month = i[1]
member_id = i[0]
module = DbUtil.get_mod_16(member_id)
db = PyMysql.connect("10.47.32.108", "sibu_dbuser", "TY3WxTv9CIiOtefN",
"sibu_directsale")
cursor = db.cursor()
cursor.execute(
sql.format(module=module,
profit_month=profit_month,
apply_money=apply_money,
member_id=member_id))
try:
db.commit()
success_count += 1
print("successCount: " + str(success_count))
except:
failed_count += 1
print("failed: " + member_id + " count:" + str(failed_count))
db.rollback()
db.close()
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 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 query_order(order_list):
for i in order_list:
sql = "SELECT order_code, order_status, express_id, express_code,address,ship_date from sibu_directsale_order_{module}.doing_order_{table_index} WHERE order_code='{order_code}'"
module = DbUtil.get_mod_16(DbUtil.get_module_by_order_code(i))
table_index = DbUtil.get_mode_64(DbUtil.get_module_by_order_code(i))
db_url = DbUtil.get_db_ip(module)
db = pymysql.connect(host=db_url,
user="******",
passwd="Aa123456",
db="sibu_directsale",
charset="utf8mb4")
sql = sql.format(module=module, table_index=table_index, order_code=i)
cursor = db.cursor()
cursor.execute(sql)
print(cursor.fetchall())
db.close()
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 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
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 update_order_trade(list):
for i in list:
order_code = str(i[0])
trade_id = str(i[1])
module = DbUtil.get_mod_16(DbUtil.get_module_by_order_code(order_code))
table_index = DbUtil.get_mode_64(
DbUtil.get_module_by_order_code(order_code))
db_url = DbUtil.get_db_ip(module)
db = pymysql.connect(db_url, "root", "Aa123456", "sibu_directsale")
cursor = db.cursor()
sql = "UPDATE sibu_directsale_order_%s.doing_order_%s o SET o.payment_number = %s WHERE o.payment_number IS NULL AND o.order_code = '%s'" % (
module, table_index, trade_id, order_code)
result = cursor.execute(sql)
if (result <= 0):
print("订单流水号已经存在: %s" + order_code)
continue
try:
db.commit()
print("success:%s %s" % (order_code, trade_id))
except:
print("failed: " + order_code)
db.rollback()
db.close()
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()