def delCanceledEnstrustmentsInDB(user_name=None, market=None, symbol=None):
"""
函数功能：从数据库里面删除已经删除的委托单，如果不传user_name
输入参数：
输出参数：
"""
try:
db = mydb.MyDB()
if (user_name == None) and (market == None) and (symbol == None):
# 不传任何参数时删除所有完全成交的委托单
update_sql = """DELETE
FROM Entrustment
WHERE 'status' = 5;"""
db.update_data_in_db(update_sql)
elif user_name != None:
update_sql = """DELETE
FROM Entrustment
WHERE userId =
(SELECT userId
FROM UserTelphone
WHERE telphone = {0}
)
AND 'status' = 5
AND market = {1}
AND symbol = {2};""".format(
repr(user_name), repr(market), repr(symbol))
db.update_data_in_db(update_sql)
except Exception as err:
print(err)
finally:
db.close_db()
def ChangeOrderTimeInDB(order_ID_NO=None, delta_time=20):
"""
函数名：ChangeOrderTimeInDB
函数功能：从数据库里面订单下单修改时间，时间格式为2018-09-05 11:35:06
输入参数：订单号order_ID_NO, delta_time为时间变量，单位是min，负数则将时间回拨，正数则将时间前挪
输出参数：None
"""
try:
db = mydb.MyDB()
get_ordertime_sql = """SELECT *
FROM FiatDealTradeOrder
WHERE tradeOrderId = {};""".format(order_ID_NO)
order_time = db.fetch_data_from_db(get_ordertime_sql)[0]["orderTime"]
print("订单更新前的时间为:{}".format(order_time))
# 转为时间数组
time_array = time.strptime(str(order_time), "%Y-%m-%d %H:%M:%S")
# 转换为时间戳
old_timestamp = int(time.mktime(time_array))
new_timestamp = old_timestamp + delta_time * 60
# 将新的时间戳转换为格式化的时间
new_order_time = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(new_timestamp))
#print(new_order_time, type(new_order_time))
change_order_time_sql = """UPDATE FiatDealTradeOrder
SET orderTime = {0}
WHERE tradeOrderId = {1};""".format(
repr(new_order_time), order_ID_NO)
db.update_data_in_db(change_order_time_sql)
except Exception as err:
print(err)
else:
print("更新下单时间成功,更新后的时间为:{}".format(new_order_time))
finally:
db.close_db()
def delFinishedOrdersInDB(user_nickname="手机商家", currency=None):
"""
函数名：从数据库里面删除指定商家或者币种的已完成订单，不传币种则删除该用户所有的已完成订单
函数功能：
输入参数：
输出参数：
"""
try:
db = mydb.MyDB()
user_sql = """SELECT *
FROM User
WHERE nikeName = {0};""".format(repr(user_nickname))
user_id = db.fetch_data_from_db(user_sql)[0]["userId"]
if (currency == 'all'):
update_sql_1 = """DELETE
FROM FiatDealTradeOrder
WHERE userId = {0}
AND orderStatus = 3
AND tradeStatus = 2;""".format(repr(user_id))
db.update_data_in_db(update_sql_1)
update_sql_2 = """DELETE
FROM FiatDealTradeOrder
WHERE adUserId = {0}
AND orderStatus = 3
AND tradeStatus = 2;""".format(repr(user_id))
db.update_data_in_db(update_sql_2)
else:
update_sql_3 = """DELETE
FROM FiatDealTradeOrder
WHERE userId = {0}
AND tradeCode = {1}
AND orderStatus = 3
AND tradeStatus = 2;""".format(
repr(user_id), repr(currency))
db.update_data_in_db(update_sql_3)
update_sql_4 = """DELETE
FROM FiatDealTradeOrder
WHERE adUserId = {0}
AND tradeCode = {1}
AND orderStatus = 3
AND tradeStatus = 2;""".format(
repr(user_id), repr(currency))
db.update_data_in_db(update_sql_4)
except Exception as err:
print(err)
else:
print("删除已完成订单成功！")
finally:
db.close_db()
def getUserInfoInDB(user_nickname="手机商家"):
"""
函数名：getUserInfoInDB
函数功能：在数据库中获取某个用户的各种信息
输入参数：
user_nickname---用户的昵称，如果是非商家用户则为手机或者邮箱号，如果是商家则是商家昵称
输出参数：返回一个字典，通过键来访问
user_id---唯一标志用户的字段
login_times---登录次数统计，目前暂时无法实现
reg_device---注册的设备，1PC,2安卓，3IOS，4H5
verify_identity_status---身份认证状态，1身份已认证，2身份未认证
Verify_tel_status---绑定手机状态，返回Y或者N
Verify_email_status---绑定邮箱状态，返回Y或者N
Verify_Google_status---开启谷歌二次认证，Y或者N
set_account_pwd_status---是否设置资金密码，Y或者N
reg_time---注册时间，返回datetime.datetime()格式的，也许需要转换才能使用
my_invite_code---当前用户的邀请码
inviter_code---邀请该用户的用户邀请码，如果没有返回None
first_deal_status---是否进行首次交易，1则不是首次交易，2是没有进行首次交易
fiat_unit---法币切换，目前这字段暂时不使用
"""
try:
db = mydb.MyDB()
user_sql = """SELECT *
FROM User
WHERE nikeName = {0};""".format(repr(user_nickname))
user_info_dict = db.fetch_data_from_db(user_sql)[0]
return dict(user_id=user_info_dict['userId'],
login_times=user_info_dict['loginCount'],
reg_device=user_info_dict['regDev'],
verify_identity_status=user_info_dict['isVerifyIdentity'],
Verify_tel_status=user_info_dict['isVerifyTel'],
Verify_email_status=user_info_dict['isVerifyEmail'],
Verify_Google_status=user_info_dict['isVerifyGoogle'],
set_account_pwd_status=user_info_dict['isSetAcc'],
reg_time=user_info_dict['regtime'],
my_invite_code=user_info_dict['myInviteCode'],
inviter_code=user_info_dict['inviterCode'],
first_deal_status=user_info_dict['isFirst'],
fiat_unit=user_info_dict['fiatUnit'])
except Exception as err:
print(err)
else:
pass
finally:
db.close_db()
def getUserInfoByAdID(ad_ID_NO=None):
try:
db = mydb.MyDB()
# 多行注释先ctrl+c，ctrl+k
# user_sql = """SELECT *
# FROM User
# WHERE nikeName = {0};""".format(repr(user_nickname))
# user_id = db.fetch_data_from_db(user_sql)[0]["userId"]
sql = """SELECT *
FROM Advertising
WHERE AdvertisingOrderId = {0};""".format(repr(ad_ID_NO))
r = db.fetch_data_from_db(sql)
return [i['userId'] for i in r]
except Exception as err:
print(err)
def delUserRegInfoInDB(sql):
"""
函数名：getUserInfoInDB
函数功能：在数据库中获取某个用户的各种信息
输入参数
"""
try:
db = mydb.MyDB()
for i in sql:
db.update_data_in_db(i)
except Exception as err:
print(err)
else:
pass
finally:
db.close_db()
def getAdIDByUser(user_nickname=None, currency=None):
try:
db = mydb.MyDB()
# 多行注释先ctrl+c，ctrl+k
# user_sql = """SELECT *
# FROM User
# WHERE nikeName = {0};""".format(repr(user_nickname))
# user_id = db.fetch_data_from_db(user_sql)[0]["userId"]
sql = """SELECT *
FROM Advertising
WHERE nickname = {0}
AND tradeCode = {1}
;""".format(repr(user_nickname), repr(currency))
r = db.fetch_data_from_db(sql)
return [i['advertisingOrderId'] for i in r]
except Exception as err:
print(err)
def delOrdersInDB(user_nickname="手机商家", currency=None):
"""
函数名：从数据库里面删除指定商家或者币种的广告单，不传币种则删除该用户所用的广告单，这些广告单是解冻的广告单
函数功能：
输入参数：
输出参数：
"""
try:
db = mydb.MyDB()
user_sql = """SELECT *
FROM User
WHERE nikeName = {0};""".format(repr(user_nickname))
user_id = db.fetch_data_from_db(user_sql)[0]["userId"]
if (currency == 'all'):
update_sql = """DELETE
FROM Advertising
WHERE userId = {0}
AND frozenStatus = 1
AND frozenOrderVal = 0;""".format(repr(user_id))
db.update_data_in_db(update_sql)
else:
update_sql = """DELETE
FROM Advertising
WHERE userId = {0}
AND tradeCode = {1}
AND frozenStatus = 1
AND frozenOrderVal = 0;""".format(repr(user_id), repr(currency))
db.update_data_in_db(update_sql)
except Exception as err:
print(err)
else:
print("删除成功！")
#fiat_fund_sql = """SELECT *
# FROM {4}
# WHERE userId = {0};""" .format(repr(user_id))
#for i, j in db.fetch_data_from_db(fiat_fund_sql)[0][""]
finally:
db.close_db()
def initUserFundAccount(fund_type='UserFiatFunds',
user_nickname='手机商家',
currency=None,
available_balance=None,
frozen_balance=None):
"""
函数名：initUserFundAccount
函数功能：初始化资金账户
输入参数：
fund_type---资金账户类型
user_nickname---用户昵称，如果是商家则是商家自己的名字，如果不是则是手机或者邮箱
currency---币种代号
available_balance---可用余额
frozen_balance---冻结余额
输出参数：
无
"""
try:
db = mydb.MyDB()
user_sql = """SELECT *
FROM User
WHERE nikeName = {0};""".format(repr(user_nickname))
user_id = db.fetch_data_from_db(user_sql)[0]["userId"]
if (currency != "all"):
if (available_balance == None):
update_sql = """UPDATE {3}
SET frozenBalance = {0}
WHERE userId = {1}
AND tradeCode = {2};""".format(
frozen_balance, repr(user_id), repr(currency), fund_type)
db.update_data_in_db(update_sql)
elif (frozen_balance == None):
update_sql = """UPDATE {3}
SET availableBalance = {0}
WHERE userId = {1}
AND tradeCode = {2};""".format(
available_balance, repr(user_id), repr(currency),
fund_type)
db.update_data_in_db(update_sql)
else:
update_sql = """UPDATE {4}
SET availableBalance = {0}, frozenBalance = {1}
WHERE userId = {2}
AND tradeCode = {3};""".format(
available_balance, frozen_balance, repr(user_id),
repr(currency), fund_type)
db.update_data_in_db(update_sql)
elif (currency == 'all'):
if (available_balance == None):
update_sql = """UPDATE {2}
SET frozenBalance = {0}
WHERE userId = {1};""".format(
frozen_balance, repr(user_id), fund_type)
db.update_data_in_db(update_sql)
elif (frozen_balance == None):
update_sql = """UPDATE {2}
SET availableBalance = {0}
WHERE userId = {1};""".format(
available_balance, repr(user_id), fund_type)
db.update_data_in_db(update_sql)
else:
update_sql = """UPDATE {3}
SET availableBalance = {0}, frozenBalance = {1}
WHERE userId = {2};""".format(
available_balance, frozen_balance, repr(user_id),
fund_type)
db.update_data_in_db(update_sql)
except Exception as err:
print(err)
else:
print("更新成功！")
#fiat_fund_sql = """SELECT *
# FROM {4}
# WHERE userId = {0};""" .format(repr(user_id))
#for i, j in db.fetch_data_from_db(fiat_fund_sql)[0][""]
finally:
db.close_db()
