def do_add_user(phone, uid, reg_pwd, agentId, expireDate, itype):
if (itype == 0):
atype = 0 #0:分钟数帐户
else:
atype = 1 #1:正常预付费帐户
try:
sql = "insert into v_field(field_name, pstn_num, field_desc, agent_id, fee_table_id, enable_flag, create_time, active_time) values ('%s','','',%d,%d,'1',now(),now())" % (
uid, agentId, 2)
ret = db_util.modify_db(sql)
if not ret:
logger.info("插入v_field表失败:%s" % phone)
return False
sql = "select field_id from v_field where field_name='%s' limit 1" % uid
row = db_util.query_db(sql)
if not row:
logger.warning(
"v_field中不存在field_id,查询失败, please contact the system admin")
return False
fieldId = int(row['field_id'])
if (itype != 1): #不是正常的终端用户注册均不赠送
sql = "insert into field_account(field_id,balance,discount,account_type,quota,money_id) values (%d,%d,100,%d,0,1)" % (
fieldId, 0, atype)
else:
sql = "insert into field_account(field_id,balance,discount,account_type,quota,money_id) values (%d,%d,100,%d,0,1)" % (
fieldId, config.given, atype)
ret = db_util.modify_db(sql)
if not ret:
logger.info("插入field_account表失败:%s" % phone)
return False
sql = "select field_account_id from field_account where field_id='%s' limit 1" % fieldId
row = db_util.query_db(sql)
if not row:
logger.warning(
"field_account中不存在field_account_id,查询失败, please contact the system admin"
)
return False
sql = "insert into user(user_type,user_name,reg_pwd,long_name,rep_name,email,phone,address,field_id,enable_flag,user_desc,create_time,active_time,valid_date) values('%s','%s','%s','%s','','','','',%d,'1','',NOW(),NOW(),'%s')" % (
itype, uid, reg_pwd, phone, fieldId, expireDate)
ret = db_util.modify_db(sql)
if not ret:
logger.info("插入user表失败:%s" % phone)
return False
sql = "select user_id from user where field_id='%s' limit 1" % fieldId
row = db_util.query_db(sql)
if not row:
logger.warning(
"user中不存在user_id,查询失败, please contact the system admin")
return False
sql = "UPDATE agent SET customer=customer+1 WHERE agent_id='%s'" % agentId
db_util.modify_db(sql)
return fieldId
except:
logger.info("do_add_user proc exception: %s" % traceback.format_exc())
return False
def is_free(field_id, balance, package=''):
no_fee = False
now = int(time.strftime('%H', time.localtime()))
if now >= 2 and now < 6:
no_fee = True
else:
stg = time.strftime('%H:%M:00', time.localtime())
if ((stg >= '06:00:00' and stg < '11:30:00')
or (stg >= '14:00:00' and stg < '17:30:00')):
beg = time.strftime('%Y-%m-%d 00:00:00', time.localtime())
end = time.strftime('%Y-%m-%d 17:30:00', time.localtime())
sql = "SELECT SUM(money) AS money FROM charge_log WHERE chargee_id='%s' AND (time>='%s' AND time<'%s') AND (operate_type='充值' OR operate_type='划账')" % (
field_id, beg, end)
cur = db_util.query_db(sql) #当天累计充值
if (cur and cur['money'] and (cur['money'] >= 50000000) and
(float(balance) * 1000000 > float(cur['money']))): #并且没有扣款
no_fee = True
else:
beg = time.strftime('%Y-%m-%d 00:00:00',
time.localtime(time.time() - 86400))
end = time.strftime('%Y-%m-%d 17:30:00', time.localtime())
sql = "SELECT SUM(money) AS money FROM charge_log WHERE chargee_id='%s' AND (time>='%s' AND time<'%s') AND (operate_type='充值' OR operate_type='划账')" % (
field_id, beg, end)
two = db_util.query_db(sql) #两天累计充值
if (two and two['money'] and (two['money'] >= 100000000) and
(float(balance) * 1000000 > float(two['money']))): #并且没有扣款
no_fee = True
else:
day = int(time.strftime('%w', time.localtime()))
if (day == 0):
ydate = time.strftime('%Y-01-01 00:00:00',
time.localtime())
sql = "SELECT SUM(money) AS money FROM charge_log WHERE chargee_id='%s' AND (time>='%s' AND time<NOW()) AND (operate_type='充值' OR operate_type='划账')" % (
field_id, ydate)
hos = db_util.query_db(sql)
sql = "SELECT SUM(money) AS money FROM charge_log WHERE chargee_id='%s' AND (time>='%s' AND time<NOW()) AND (operate_type='扣款')" % (
field_id, ydate)
drp = db_util.query_db(sql)
if drp and hos and drp['money'] > 0 and hos[
'money'] > 0:
hos['money'] = hos['money'] - drp['money']
pkg = ((package != None) and (package != '')
and (int(package[0]['month_left_time']) > 100))
if ((hos['money'] >= 10000000)
and ((float(balance) * 1000000 > 15000000) or
(pkg == True))): #本年度累计充值
no_fee = True
if ((float(balance) * 1000000 <= 0) and
(pkg == True)): #包月用户如果账户余额不足，自动追加余额5分钱
sql = "UPDATE field_account SET balance=balance+50000 WHERE field_id='%s' LIMIT 1" % field_id
if not (db_util.modify_db(sql)):
logger.info(
"包月用户余额不足，自动追加5分钱失败: field_id=%s" %
field_id)
return no_fee
def no_bill_and_not_logs():
sql = "SELECT * FROM call_log_temp"
list = db_util.query_db(sql, 2)
for row in list:
t = row['field_units'] % 60
if t > 0:
t = 1
field_units = row['field_units'] / 60 + t
field_fee = field_units * 150000
sql = "SELECT user_name,field_id FROM user WHERE long_name='%s'" % row[
'mobile'][1:]
tmp = db_util.query_db(sql)
if not tmp:
print "%s in user table not found!!!" % (row['mobile'])
continue
from_number = tmp['user_name']
sql = "UPDATE field_account SET balance = balance - %s WHERE field_id = '%s'" % (
field_fee, tmp['field_id'])
db_util.modify_db(sql)
sql = "SELECT balance FROM field_account WHERE field_id = '%s'" % (
tmp['field_id'])
bln = db_util.query_db(sql)
print "%s after:%s\tdrop:%s" % (row['mobile'], bln['balance'],
field_fee)
if row['to_number'][:2] == '00' and row['to_number'][:4] != '0086':
call_type = 4
else:
call_type = 2
sql = """INSERT INTO call_log(session_id, from_number, to_number, raw_from_number, raw_to_number, from_ip, to_ip, start_time, end_time, call_type, call_time, pkg_call_time, billing_time, agent_id, field_id, field_name, field_rate, field_units, field_fee, long_name) VALUES (floor(rand()*10000000), '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', %d, %d, %d, %d, %d, '%s', '%s', %d, %d, '%s')""" % (
row['mobile'], row['to_number'], from_number, row['to_number'],
row['from_ip'], row['to_ip'], row['start_time'], row['end_time'],
call_type, row['call_time'], 0, row['call_time'], 1,
tmp['field_id'], from_number, '0.150/60', field_units, field_fee,
from_number + ' :CB')
db_util.modify_db(sql)
time.sleep(1)
def change_phone(old_phone, pwd, new_phone):
try:
new_phone = tool.number_deal(new_phone)
if not new_phone:
logger.info("手机格式错误:old_phone:%s,new_phone:%s" %
(old_phone, new_phone))
return '{"result":"-3"}'
if (old_phone[0] == '0' and old_phone[1] == '1'):
old_phone = old_phone[1:]
if (new_phone[0] == '0' and new_phone[1] == '1'):
new_phone = new_phone[1:]
sql = "SELECT field_id, user_name, reg_pwd FROM user WHERE long_name='%s'" % old_phone
ret = db_util.query_db(sql)
if not ret or pwd != ret['reg_pwd']:
logger.info("手机对应帐号或密码错误:%s" % old_phone)
return '{"result":"-2"}'
new_uid = tool.get_bind_uid(new_phone)
if new_uid:
logger.info("新手机已存在,old_phone:%s,new_phone:%s" %
(old_phone, new_phone))
return '{"result":"-4"}'
sql = "SELECT balance FROM field_account WHERE field_id='%s'" % ret[
'field_id']
rec = db_util.query_db(sql)
if not rec:
logger.info("余额查询失败,old_phone:%s,field_id:%s" %
(old_phone, ret['field_id']))
return '{"result":"-10"}'
elif rec and long(rec['balance']) < 30000000:
logger.info("改绑失败:%s余额小于30元,不允许改绑" % old_phone)
return '{"result":"-5"}'
sql = "update user set long_name='%s' where long_name='%s'" % (
new_phone, old_phone)
res = db_util.modify_db(sql)
if not res:
logger.info("改绑失败:%s" % old_phone)
return '{"result":"-6"}'
logger.info("改绑成功:%s-->%s " % (old_phone, new_phone))
return '{"result":"0","uid":"%s"}' % ret['user_name']
except:
logger.info('密码格式错误')
return '{"result":"-2"}'
def change_pwd(phone, old_pwd, new_pwd):
try:
phone = tool.number_deal(phone)
if not phone:
logger.info("手机格式错误:phone:%s" % (phone))
return '{"result":"-3"}'
if (phone[0] == '0' and phone[1] == '1'):
phone = phone[1:]
sql = "SELECT reg_pwd FROM user WHERE long_name='%s'" % phone
ret = db_util.query_db(sql)
if not ret or old_pwd != ret['reg_pwd']:
logger.info("原密码错误:%s" % old_pwd)
return '{"result":"-2"}'
sql = "update user set reg_pwd='%s' where long_name='%s'" % (new_pwd,
phone)
res = db_util.modify_db(sql)
if not res:
logger.info("密码修改失败,phone:%s,new_pwd:%s" % (phone, new_pwd))
return '{"result":"-4"}'
logger.info("密码修改成功:phone=%s,%s-->%s " % (phone, old_pwd, new_pwd))
return '{"result":"0"}'
except:
logger.info('传入参数错误')
return '{"result":"-10"}'
def can_call(uid):
try:
sql = "SELECT b.balance, a.valid_date, a.field_id, a.enable_flag, a.user_type, a.long_name FROM user a, field_account b WHERE a.field_id=b.field_id AND a.user_name='%s' LIMIT 1" % uid
row = db_util.query_db(sql)
if not row:
logger.info("can not call: 账户不存在:%s" % uid)
return '{"result":"-2"}'
res = {}
res['result'] = '0'
res['balance'] = "%.3f" %(long(row['balance']) / 1000000.0)
res['validate'] = row['valid_date'].strftime('%Y-%m-%d')
res['field_id'] = row['field_id']
itype = int(row['user_type'])
iname = str(row['long_name'])
if res['validate'] < datetime.now().strftime('%Y-%m-%d'):
sql = "SELECT field_type FROM v_field WHERE field_id='%s' LIMIT 1" %row['field_id']
tmp = db_util.query_db(sql)
if tmp and tmp['field_type'] == '0':
sql = "UPDATE v_field SET field_type='1' WHERE field_id='%s' LIMIT 1" %row['field_id']
ret = db_util.modify_db(sql)
if not ret:
logger.info("修改field_type失败:%s" %iname)
return '{"result":"-8", "validate":"%s", "balance":"%s"}' % (res['validate'], res['balance'])
sql = "UPDATE user SET valid_date=DATE_ADD(NOW(),INTERVAL 24 MONTH) WHERE field_id='%s' LIMIT 1" %row['field_id']
ret = db_util.modify_db(sql)
if not ret:
logger.info("修改valid_date失败:%s" %iname)
return '{"result":"-8", "validate":"%s", "balance":"%s"}' % (res['validate'], res['balance'])
else:
logger.info("can not call: 账户过有效期:%s" % uid)
return '{"result":"-8", "validate":"%s", "balance":"%s"}' % (res['validate'], res['balance'])
if row['enable_flag'] != '1':
logger.info("can not call: 账户已被冻结:%s" % uid)
return '{"result":"-10", "validate":"%s", "balance":"%s"}' % (res['validate'], res['balance'])
sql = "SELECT product, eff_time, exp_time FROM time_acct WHERE user_name = '%s' AND NOW() <= exp_time ORDER BY exp_time DESC LIMIT 1" %uid
row = db_util.query_db(sql)
if not row:
if (float(res['balance'])*1000000 < config.MIN_BALANCE):
logger.info("can not call: 余额不足:%s" % uid)
return '{"result":"-9", "validate":"%s", "balance":"%s"}' % (res['validate'], res['balance'])
res['package'] = ''
else:
if itype == 0:
fen = 0
sql = "SELECT balance FROM phone WHERE phone ='%s' LIMIT 1" %iname
tmp = db_1255577.query_db(sql)
if tmp and tmp['balance']:
fen = int(tmp['balance'])
sql = "UPDATE time_acct SET month_left_time = %d WHERE user_name = '%s' AND NOW() <= exp_time ORDER BY exp_time DESC LIMIT 1" %(fen, uid)
db_util.modify_db(sql)
else:
sql = "SELECT SUM(month_left_time) AS month_left_time FROM time_acct WHERE user_name = '%s' AND NOW() <= exp_time" %uid
tmp = db_util.query_db(sql)
fen = tmp['month_left_time']
if ((fen <= 0) and (float(res['balance'])*1000000 < config.MIN_BALANCE)):
logger.info("can not call: 余额不足并且包月分钟数也已用完:%s" % uid)
return '{"result":"-9", "validate":"%s", "balance":"%s"}' % (res['validate'], res['balance'])
row['month_left_time'] = str(fen)
row['eff_time'] = row['eff_time'].strftime('%Y-%m-%d')
row['exp_time'] = row['exp_time'].strftime('%Y-%m-%d')
res['package'] = [row]
res = json.dumps(res)
res = eval("u'%s'" % res)
return res.encode('utf-8')
except:
logger.info("can_call异常%s" % traceback.format_exc())
return '{"result":"-11"}'
def package(uid, packageid, no, orderno, web, paytype, convert=1):
logger.info("收到包月充值请求: uid: %s, packageid: %s, num: %s, orderno: %s" %
(uid, packageid, no, orderno))
productid = int(packageid)
cnt = int(no) #购买包月套餐的个数
#取出终端用户的余额等信息
sql = "SELECT u.long_name, u.field_id, u.valid_date, u.user_type, NOW() AS now_date, f.balance, f.account_type, v.agent_id, v.field_type FROM v_field AS v, user AS u LEFT JOIN field_account AS f ON u.field_id=f.field_id WHERE u.field_id=v.field_id AND u.user_name='%s'" % uid
user = db_util.query_db(sql)
if not user:
logger.info("查询用户余额等信息失败:UID=%s" % uid)
return '{"result":"-2"}'
packs_type = '1' #1：计时账户、代理商正常返点
chargee_name = str(user['long_name'])
chargee_field_id = int(user['field_id'])
chargee_money_before = int(user['balance'])
if convert == 1:
if int(user['account_type'] == 0): #1255577业务充包月
if productid == 12: #100元包30天
base = 10000
days_per_month = 30
days = days_per_month * cnt
month_left_time = 30000 * cnt
elif productid == 14: #10元包2天
base = 1000
days_per_month = 2
days = days_per_month * cnt
month_left_time = 1000 * cnt
else:
return '{"result":"-1"}'
else: #普通回拨业务充包月
if productid == 1: #30元包月[回拨]
base = 3000
days_per_month = 30
days = days_per_month * cnt
month_left_time = 500 * cnt
elif productid == 18: #50元包月[回拨]
base = 5000
days_per_month = 30
days = days_per_month * cnt
month_left_time = 1000 * cnt
elif productid == 12: #100元包月[回拨]
base = 10000
days_per_month = 30
days = days_per_month * cnt
month_left_time = 2500 * cnt
elif productid == 13: #300元包月[回拨]
base = 30000
days_per_month = 30
days = days_per_month * cnt
month_left_time = 100000 * cnt
elif productid == 14: #无限打开通半年+包30天
mday = 180
base = 15000
days_per_month = 30
days = days_per_month * cnt
month_left_time = 30000 * cnt
elif productid == 15: #无限打开通半年+包99天
mday = 180
base = 22000
days_per_month = 99
days = days_per_month * cnt
month_left_time = 50000 * cnt
elif productid == 16: #无限打开通一年+包30天
mday = 360
base = 23000
days_per_month = 30
days = days_per_month * cnt
month_left_time = 30000 * cnt
elif productid == 17: #无限打开通一年+包99天
mday = 360
base = 30000
days_per_month = 99
days = days_per_month * cnt
month_left_time = 50000 * cnt
else:
return '{"result":"-1"}'
if int(user['account_type']) == 1:
sql = "SELECT order_id FROM card WHERE order_id='%s' AND is_actived=1" % orderno
car = db_util.query_db(sql)
if car:
logger.info("自发卡充包月=>充账户余额: UID=%s" % uid)
result = add_money(uid, base, base, orderno, 'card', web)
return str(result)
sql = "SELECT 1 FROM charge_log WHERE order_id = '%s'" % orderno
if db_util.query_db(sql):
logger.info("订单号已存在:UID=%s, orderId=%s" % (uid, orderno))
return '{"result":"-3"}'
if int(user['field_type']) == 0:
if ((productid == 1) or (productid == 18)): #30元包月=>无限打
days_per_month = 30
days = days_per_month * cnt
month_left_time = 30000 * cnt
elif productid == 12: #100元包月=>无限打
days_per_month = 99
days = days_per_month * cnt
month_left_time = 50000 * cnt
packs_type = '2' #2：充值包月无限打不修改帐户有效期并且代理商不返点
if ((productid == 14) or (productid == 15) or (productid == 16)
or (productid == 17)):
sql = "UPDATE v_field SET field_type='0' WHERE field_id='%s' LIMIT 1" % chargee_field_id
ret = db_util.modify_db(sql)
if not ret:
logger.info("修改field_type失败:%s" % chargee_name)
#return '{"result":"-1"}'
if ((productid == 14) or (productid == 16)):
productid = 1
else:
productid = 12
packs_type = '0' #0：开通包月无限打修改帐户有效期至半年或一年并且代理商固定返40元人民币
else: #1255577业务充分钟数=>充包月
iyear = int(datetime.now().strftime('%Y'))
imonth = int(datetime.now().strftime('%m'))
iday = int(datetime.now().strftime('%d'))
days = calendar.monthrange(iyear, imonth)[1] - iday
if productid == 1: #30元包300分钟
base = 3000
days_per_month = days + 30
days = days_per_month * cnt
month_left_time = 300 * cnt
elif productid == 18: #50元包600分钟
base = 5000
days_per_month = days + 30
days = days_per_month * cnt
month_left_time = 600 * cnt
elif productid == 12: #100元包1600分钟
base = 10000
days_per_month = days + 60
days = days_per_month * cnt
month_left_time = 1600 * cnt
else:
return '{"result":"-1"}'
try:
#修改终端用户帐户的有效期
if packs_type == '0':
sql = "UPDATE user SET valid_date=DATE_ADD(NOW(),INTERVAL %d DAY) WHERE user_name='%s' LIMIT 1" % (
mday, uid)
ret = db_util.modify_db(sql)
if not ret:
logger.info("修改用户有效期失败:%s" % chargee_name)
return '{"result":"-1"}'
elif packs_type == '1':
month = 24 #base/500
if user['valid_date'] < user['now_date']:
sql = "UPDATE user SET valid_date=DATE_ADD(NOW(),INTERVAL %d MONTH) WHERE user_name='%s' LIMIT 1" % (
month, uid)
else:
sql = "UPDATE user SET valid_date=DATE_ADD(valid_date,INTERVAL %d MONTH) WHERE user_name='%s' LIMIT 1" % (
month, uid)
ret = db_util.modify_db(sql)
if not ret and month:
logger.info("修改用户有效期失败:%s" % chargee_name)
return '{"result":"-1"}'
#添加包月
sql = "INSERT INTO time_acct SET user_name='%s', product='%s', prefix='0086', " % (
uid, productid)
sql = sql + "month_left_time=%d, day_total_time=%d, day_left_time=%d, " % (
month_left_time, month_left_time / days, month_left_time / days)
sql = sql + "last_call_time=NOW(), eff_time=NOW(), exp_time=DATE_ADD(NOW(),INTERVAL %d DAY)" % days
db_util.modify_db(sql)
if web != '1':
#充值日志
sql = "INSERT INTO charge_log SET money=%d, given=%d, time=NOW(), chargee_id=%d, " % (
base * 10000, 0, chargee_field_id)
sql = sql + "chargee_money_before=%d, chargee_money_after=%d, agent_id='%s', " % (
chargee_money_before, chargee_money_before, user['agent_id'])
sql = sql + "chargee_name='%s', order_id='%s', operate_type=%%s" % (
chargee_name, str(orderno))
db_util.insert_db(sql, "充值")
logger.info("package for account: balance = %d long_name = %s" %
(base, chargee_name))
if int(user['agent_id']) == 1 or str(paytype) == 'none' or int(
packs_type) == 2:
logger.info("自己的终端用户或不返点")
return '{"result":"0"}'
elif int(packs_type) == 0:
logger.info("无限打开通代理暂未现实返点")
return '{"result":"0"}'
#代理商返点,一直往上找到顶级代理
info = []
running = True
while running:
list = {}
#下级
sql = "SELECT a.good_type, a.good_dot, a.agent_name, a.link_man, a.agent_id FROM agent a, v_field v WHERE v.agent_id=a.agent_id AND v.field_name='%s'" % uid
agent2 = db_util.query_db(sql)
#上级
sql = "SELECT a.agent_name, a.link_man, a.agent_id FROM agent a, v_field v WHERE v.agent_id=a.agent_id AND v.field_name='%s'" % agent2[
'agent_name']
agent1 = db_util.query_db(sql)
if agent2 and agent1:
if (int(agent1['agent_id']) == 0):
running = False
break
#取出上级的余额
sql = "SELECT f.balance, v.field_id FROM field_account f, v_field v WHERE v.field_id=f.field_id AND field_name='%s'" % agent1[
'agent_name']
bf1 = db_util.query_db(sql)
#取出下级的余额
sql = "SELECT f.balance, v.field_id FROM field_account f, v_field v WHERE v.field_id=f.field_id AND field_name='%s'" % agent2[
'agent_name']
bf2 = db_util.query_db(sql)
if bf1 and bf2:
list['chargee_money_before'] = int(bf2['balance'])
list['charger_money_before'] = int(bf1['balance'])
list['chargee_field_id'] = int(bf2['field_id'])
list['charger_field_id'] = int(bf1['field_id'])
else:
logger.info("查询代理商余额失败:agent1=%s,agent2=%s" %
(agent1['link_man'], agent2['link_man']))
return '{"result":"-1"}'
list['chargee_name'] = str(agent2['link_man'])
list['charger_name'] = str(agent1['link_man'])
list['chargee_agent_id'] = int(agent2['agent_id'])
list['charger_agent_id'] = int(agent1['agent_id'])
else:
logger.info("查找上级代理商失败:UID=%s" % uid)
return '{"result":"-1"}'
uid = str(agent2['agent_name'])
if int(agent2['good_type']) == 1:
sql = "SELECT count(*) AS nums FROM charge_log WHERE chargee_id='%s'" % chargee_field_id
res = db_util.query_db(sql)
if res and res['nums'] != 1:
continue
elif not res:
logger.info("查询用户充值记录失败:name=%s" % chargee_name)
return '{"result":"-4"}'
#操作金额,数据库里的格式
list['balance'] = base * int(agent2['good_dot']) / 1000
info.insert(0, list)
#从顶级代理开始逐级返点
for list in info:
if (list['charger_money_before'] < list['balance']):
logger.info("返点失败：%s余额不足" % list['charger_name'])
break
else:
#上级代理扣款
sql = "UPDATE field_account SET balance=balance-%d WHERE field_id='%s' AND balance >= %d LIMIT 1" % (
list['balance'], list['charger_field_id'], list['balance'])
ret = db_util.modify_db(sql)
if not ret:
logger.info("上级代理扣款失败:%s" % list['chargee_name'])
return '{"result":"-1"}'
else:
#下级代理加钱
sql = "UPDATE field_account SET balance=balance+%d WHERE field_id='%s' LIMIT 1" % (
list['balance'], list['chargee_field_id'])
ret = db_util.modify_db(sql)
if not ret:
logger.info("下级代理返点失败:%s" % list['chargee_name'])
return '{"result":"-1"}'
#充值日志
sql = "INSERT INTO charge_log SET money=%d, given=%d, time=NOW(), " % (
base * 10000, list['balance'])
sql = sql + "chargee_money_before=%d, chargee_money_after=%d, " % (
list['chargee_money_before'],
list['chargee_money_before'] + list['balance'])
sql = sql + "charger_money_before=%d, charger_money_after=%d, " % (
list['charger_money_before'],
list['charger_money_before'] - list['balance'])
sql = sql + "chargee_level=%d, chargee_id=%d, charger_level=%d, charger_id=%d, " % (
list['chargee_agent_id'], list['chargee_field_id'],
list['charger_agent_id'], list['charger_field_id'])
sql = sql + "chargee_name='%s', charger_name='%s', order_id='%s', operate_type=%%s" % (
list['chargee_name'], list['charger_name'], str(orderno))
db_util.insert_db(sql, "返点")
if int(user['user_type']) == 0:
url = "http://112.124.58.27/index.php/iface/addcharge/{%s}/{%s}/kzcz13544225433/52c69e3a57331081823331c4e69d3f2e" % (
chargee_name, base / 100)
f = urllib2.urlopen(url)
xmlstr = str(f.read())
#xmlstr = xmlstr.replace('\r', '')
#xmlstr = xmlstr.replace('\n', '')
#xmlstr = xmlstr.replace('\t', '')
#doc = minidom.parseString(xmlstr)
#root = doc.documentElement
#nodes = root.getElementsByTagName('error')
#value = nodes[0].childNodes[0].nodeValue
#result = value.encode('utf-8','ignore')
#return '{"result":"%s"}' %result
return '{"result":"0"}'
except:
logger.info("充值时异常：%s" % traceback.format_exc())
conn.rollback()
return '{"result":"-1"}'
def add_money(uid, actual, virtual, order_id, paytype, web):
logger.info("charge: uid: %s, actual: %s, virtual: %s, order id: %s" %
(uid, actual, virtual, order_id))
money = int(virtual)
if str(paytype) == 'card':
base = int(actual) / 3
else:
base = int(actual)
try:
#取出终端用户的余额等信息
sql = "SELECT u.long_name, u.field_id, u.valid_date, NOW() AS now_date, f.balance, f.account_type, v.agent_id, v.field_type FROM v_field AS v, user AS u LEFT JOIN field_account AS f ON u.field_id=f.field_id WHERE u.field_id=v.field_id AND u.user_name='%s'" % uid
user = db_util.query_db(sql)
if not user:
logger.info("查询用户余额等信息失败:UID=%s" % uid)
return '{"result":"-2"}'
chargee_name = str(user['long_name'])
chargee_field_id = int(user['field_id'])
chargee_money_before = int(user['balance'])
if int(user['account_type']) == 0:
logger.info("1255577业务充分钟数=>充包月: UID=%s" % uid)
if actual == '10000':
packageid = 12
elif actual == '5000':
packageid = 18
elif actual == '3000':
packageid = 1
else:
return '{"result":"-1"}'
result = package(uid, packageid, 1, order_id, web, 0)
return str(result)
sql = "SELECT 1 FROM charge_log WHERE order_id = '%s'" % order_id
if db_util.query_db(sql):
logger.info("订单号已存在:UID=%s, orderId=%s" % (uid, order_id))
return '{"result":"-3"}'
#修改终端用户帐户的有效期
if int(user['field_type']) == 1:
if base < 10000:
month = 2 * 12 #base/500
else:
month = 5 * 12
if user['valid_date'] < user['now_date']:
sql = "UPDATE user SET valid_date=DATE_ADD(NOW(),INTERVAL %d MONTH) WHERE user_name='%s' LIMIT 1" % (
month, uid)
else:
sql = "UPDATE user SET valid_date=DATE_ADD(valid_date,INTERVAL %d MONTH) WHERE user_name='%s' LIMIT 1" % (
month, uid)
ret = db_util.modify_db(sql)
if not ret and month:
logger.info("修改用户有效期失败:%s" % chargee_name)
return '{"result":"-1"}'
#修改终端用户的余额
balance = money * 10000 #操作金额,数据库里的格式
sql = "UPDATE field_account SET balance=balance+%d WHERE field_id='%s' LIMIT 1" % (
balance, chargee_field_id)
ret = db_util.modify_db(sql)
if not ret:
logger.info("修改用户余额失败:%s" % chargee_name)
return '{"result":"-1"}'
if web != '1':
#充值日志
sql = "INSERT INTO charge_log SET money=%d, given=%d, time=NOW(), chargee_id=%d, " % (
base * 10000, balance - base * 10000, chargee_field_id)
sql = sql + "chargee_money_before=%d, chargee_money_after=%d, agent_id='%s', " % (
chargee_money_before, chargee_money_before + balance,
user['agent_id'])
sql = sql + "chargee_name='%s', order_id='%s', operate_type=%%s" % (
chargee_name, str(order_id))
db_util.insert_db(sql, "充值")
logger.info("charege for account: balance = %d long_name = %s" %
(balance, chargee_name))
if int(user['agent_id']) == 1 or str(paytype) == 'card' or str(
paytype) == 'none':
logger.info("自己的终端用户或不返点")
return '{"result":"0"}'
#代理商返点,一直往上找到顶级代理
info = []
running = True
while running:
list = {}
#下级
sql = "SELECT a.good_type, a.good_dot, a.agent_name, a.link_man, a.agent_id FROM agent a, v_field v WHERE v.agent_id=a.agent_id AND v.field_name='%s'" % uid
agent2 = db_util.query_db(sql)
#上级
sql = "SELECT a.agent_name, a.link_man, a.agent_id FROM agent a, v_field v WHERE v.agent_id=a.agent_id AND v.field_name='%s'" % agent2[
'agent_name']
agent1 = db_util.query_db(sql)
if agent2 and agent1:
if (int(agent1['agent_id']) == 0):
running = False
break
#取出上级的余额
sql = "SELECT f.balance, v.field_id FROM field_account f, v_field v WHERE v.field_id=f.field_id AND field_name='%s'" % agent1[
'agent_name']
bf1 = db_util.query_db(sql)
#取出下级的余额
sql = "SELECT f.balance, v.field_id FROM field_account f, v_field v WHERE v.field_id=f.field_id AND field_name='%s'" % agent2[
'agent_name']
bf2 = db_util.query_db(sql)
if bf1 and bf2:
list['chargee_money_before'] = int(bf2['balance'])
list['charger_money_before'] = int(bf1['balance'])
list['chargee_field_id'] = int(bf2['field_id'])
list['charger_field_id'] = int(bf1['field_id'])
else:
logger.info("查询代理商余额失败:agent1=%s,agent2=%s" %
(agent1['link_man'], agent2['link_man']))
return '{"result":"-1"}'
list['chargee_name'] = str(agent2['link_man'])
list['charger_name'] = str(agent1['link_man'])
list['chargee_agent_id'] = int(agent2['agent_id'])
list['charger_agent_id'] = int(agent1['agent_id'])
else:
logger.info("查找上级代理商失败:UID=%s" % uid)
return '{"result":"-1"}'
uid = str(agent2['agent_name'])
if int(agent2['good_type']) == 1:
sql = "SELECT count(*) AS nums FROM charge_log WHERE chargee_id='%s'" % chargee_field_id
res = db_util.query_db(sql)
if res and res['nums'] != 1:
continue
elif not res:
logger.info("查询用户充值记录失败:name=%s" % chargee_name)
return '{"result":"-4"}'
#操作金额,数据库里的格式
list['balance'] = base * int(agent2['good_dot']) / 1000
info.insert(0, list)
#从顶级代理开始逐级返点
for list in info:
if (list['charger_money_before'] < list['balance']):
logger.info("返点失败：%s余额不足" % list['charger_name'])
break
else:
#上级代理扣款
sql = "UPDATE field_account SET balance=balance-%d WHERE field_id='%s' AND balance >= %d LIMIT 1" % (
list['balance'], list['charger_field_id'], list['balance'])
ret = db_util.modify_db(sql)
if not ret:
logger.info("上级代理扣款失败:%s" % list['chargee_name'])
return '{"result":"-1"}'
else:
#下级代理加钱
sql = "UPDATE field_account SET balance=balance+%d WHERE field_id='%s' LIMIT 1" % (
list['balance'], list['chargee_field_id'])
ret = db_util.modify_db(sql)
if not ret:
logger.info("下级代理返点失败:%s" % list['chargee_name'])
return '{"result":"-1"}'
#充值日志
sql = "INSERT INTO charge_log SET money=%d, given=%d, time=NOW(), " % (
base * 10000, list['balance'])
sql = sql + "chargee_money_before=%d, chargee_money_after=%d, " % (
list['chargee_money_before'],
list['chargee_money_before'] + list['balance'])
sql = sql + "charger_money_before=%d, charger_money_after=%d, " % (
list['charger_money_before'],
list['charger_money_before'] - list['balance'])
sql = sql + "chargee_level=%d, chargee_id=%d, charger_level=%d, charger_id=%d, " % (
list['chargee_agent_id'], list['chargee_field_id'],
list['charger_agent_id'], list['charger_field_id'])
sql = sql + "chargee_name='%s', charger_name='%s', order_id='%s', operate_type=%%s" % (
list['chargee_name'], list['charger_name'], str(order_id))
db_util.insert_db(sql, "返点")
return '{"result":"0"}'
except:
logger.info("充值时异常：%s" % traceback.format_exc())
conn.rollback()
return '{"result":"-1"}'
def domain():
cnt = 0
while True:
time.sleep(900)
#每间隔30分钟处理一次国际资费：54秒一分钟来扣费
cnt = cnt + 1
if (cnt % 2 == 0):
call_log_proc.bill_by_54s()
#8:00-24:00、0:00-4:30白天处理"帐户自动解冻"
stg = time.strftime('%H:%M', time.localtime())
now = datetime.date.today()
if ((stg > '08:00') or (stg < '04:30')):
hr = time.strftime('%H', time.localtime())
if (int(hr) % 2 == 0):
dat = now - datetime.timedelta(days=3)
sql = "UPDATE user SET enable_flag='1' WHERE enable_flag='0' AND create_time<'%s 00:00:00'" % dat
ret = db_util.modify_db(sql)
sql = "update wap_disable set disable_call='1' where disable_call='0'"
ret &= db_wap.modify_db(sql)
print "ret = %s sql = %s" % (ret, sql)
continue
#删除多余的垃圾记录
sql = "delete from field_account where field_id not in (select field_id from user)"
ret = db_util.modify_db(sql)
print "ret = %s sql = %s" % (ret, sql)
time.sleep(15)
sql = "delete from v_field where field_id not in (select field_id from user)"
ret = db_util.modify_db(sql)
print "ret = %s sql = %s" % (ret, sql)
time.sleep(15)
#4:30-8:00零晨处理"清理话单"
dat = now - datetime.timedelta(days=35)
for h in range(0, 24): #00-23点
for m in range(0, 20): #每间隔3分钟
sql = "DELETE FROM call_log WHERE start_time<='%s %02d:%02d:59'" % (
dat, h, m * 3)
ret = db_util.modify_db(sql)
print "ret = %s sql = %s" % (ret, sql)
time.sleep(5)
print "%s 的通话记录清理完成" % dat
#4:30-8:00零晨处理"清理未使用的6元帐户"
dat = now - datetime.timedelta(days=66)
sql = "SELECT field_id FROM v_field WHERE create_time>='%s 00:00:00' LIMIT 1" % dat
fd1 = db_util.query_db(sql)
dat = now - datetime.timedelta(days=65)
sql = "SELECT field_id FROM v_field WHERE create_time>='%s 00:00:00' LIMIT 1" % dat
fd2 = db_util.query_db(sql)
if fd1 and fd2 and fd1['field_id'] and fd2['field_id']:
print "开始清理 %s 之前注册了未使用的垃圾数据" % dat
os.system(
"ps -ef|grep itf.py|grep -v grep|awk '{print$2}'|xargs kill -9"
)
sql = "DELETE FROM field_account WHERE balance='%s' AND (field_id>='%s' and field_id<'%s')" % (
config.given, fd1['field_id'], fd2['field_id'])
ret = db_util.modify_db(sql)
print "ret = %s sql = %s" % (ret, sql)
time.sleep(15)
sql = "DELETE FROM user WHERE field_id NOT IN (SELECT field_id FROM field_account)"
ret = db_util.modify_db(sql)
print "ret = %s sql = %s" % (ret, sql)
time.sleep(15)
sql = "DELETE FROM v_field WHERE field_id NOT IN (SELECT field_id FROM field_account)"
ret = db_util.modify_db(sql)
print "ret = %s sql = %s" % (ret, sql)
os.system("cd /home/itf/; nohup python itf.py > /dev/null 2>&1 &")
continue
def call(uid, pwd, called, echo, ip=''):
logger.info("收到回拨请求: uid:%s;被叫号码:%s" % (uid, called))
called = tool.number_deal(called)
if not called:
logger.info("%s呼叫%s被叫号码格式验证失败" % (uid, called))
return '{"result":"-3"}'
data = tool.get_status_data(uid)
if data:
caller = str(data['long_name'])
if pwd != data['reg_pwd']:
logger.info("%s呼叫%s密码验证失败" % (caller, called))
return '{"result":"-2"}'
elif '1' != data['enable_flag']:
logger.info("%s呼叫%s账户已被冻结" % (caller, called))
return '{"result":"-10"}'
if called[0] == '0':
tmp_called = called[1:]
else:
tmp_called = called
if ((caller == tmp_called) or (caller[:2] == '00')):
logger.info("%s呼叫%s呼叫失败" % (caller, called))
return '{"result":"-3"}'
can_call = search_balance.can_call(uid)
can_call = json.loads(can_call)
if can_call['result'] != '0':
return json.dumps(can_call)
stg = time.strftime('%H:%M:00', time.localtime())
package = can_call['package']
if (float(can_call['balance']) <= 6.0) and (package == None
or package == ''):
if (stg >= '17:30:00' and stg < '23:50:00'):
cnt = 10
elif (stg >= '00:00:00' and stg < '06:30:00'):
cnt = 3
else:
cnt = 5
else:
if (stg >= '17:30:00' and stg < '23:50:00'):
cnt = 25
elif (stg >= '00:00:00' and stg < '06:30:00'):
cnt = 10
else:
cnt = 25
#A号码不停的呼叫号码
limited_time = time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(time.time() - 3600))
sql = "SELECT COUNT(*) AS count FROM call_log WHERE field_name='%s' AND to_number='0%s' AND start_time>'%s' AND start_time<=NOW()" % (
uid, caller, limited_time)
res = db_util.query_db(sql)
if (res and res['count'] >= cnt and caller.find('13172388331') <= -1
and caller.find('15015759543') <= -1
and caller.find('15502010898') <= -1):
sql = "UPDATE user SET enable_flag=0 WHERE user_name='%s'" % uid
db_util.modify_db(sql)
logger.info("%s呼叫过于频繁,冻结账户" % caller)
return '{"result":"-10"}'
#B号码不停的被呼叫
sql = "SELECT COUNT(*) AS count FROM call_log WHERE to_number LIKE '%%%s' AND start_time>'%s' AND start_time<=NOW()" % (
called, limited_time)
ret = db_util.query_db(sql)
if (ret and ret['count'] >= 8 and called.find('13172388331') <= -1
and called.find('15015759543') <= -1
and called != '075586105758'
and called.find('15502010898') <= -1):
logger.info("%s被过于频繁呼叫" % called)
return '{"result":"-5"}'
is_old = True
if called[:2] != '00':
sql = "SELECT count(*) AS nums FROM charge_log WHERE chargee_id='%s' AND (operate_type='充值' OR operate_type='划账')" % (
can_call['field_id'])
res = db_util.query_db(sql)
#新老用户判断，并加以前缀
if res and res['nums'] == 0 and float(can_call['balance']) <= 6.0:
if tool.is_mobile(called):
called = phone_add(config.prefix + '0', called)
else:
called = phone_add(config.prefix, called)
is_old = False
logger.info("新用户呼叫:caller=%s called=%s ip=%s" %
(caller, called, ip))
elif not res:
logger.info("查询用户充值记录失败:UID=%s" % uid)
elif is_free(can_call['field_id'], can_call['balance'],
package): #老用户免费活动
if tool.is_mobile(called):
called = phone_add(config.nights + '0', called)
else:
called = phone_add(config.nights, called)
caller = phone_add(config.nights + '00', caller)
logger.info("免费呼叫:caller=%s called=%s" % (caller, called))
# elif 190 == data['agent_id']:
# logger.info('新疆用户禁止打国外电话')
# return '{"result":"-10"}'
#
# #jmdw_2008此代理特殊处理
# if 1098 == data['agent_id'] and called[:2] == '00':
# sql = "SELECT count(*) AS nums FROM charge_log WHERE chargee_id='%s' AND (operate_type='充值' OR operate_type='划账')" %(can_call['field_id'])
# res = db_util.query_db(sql)
# #新老用户判断，并加以前缀
# if res and res['nums'] == 0 and float(can_call['balance']) <= 6.0:
# is_old = False
# logger.info("新用户呼叫:caller=%s called=%s" %(caller, called))
# elif not res:
# logger.info("查询用户充值记录失败:UID=%s" %uid)
# if 1098 == data['agent_id'] and is_old:
# if called[:2] != '00':
# #国内回拨1角,被叫号码加前缀0010以示区别
# called = '0010' + called
# elif called[:4] == '0084':
# called = '0070' + called[4:]
# elif called[:4] == '0081':
# called = '0071' + called[4:]
# elif called[:4] == '0082':
# called = '0072' + called[4:]
global sn
sn = sn + 1
cburl = get_cb_url(caller, config.CB_ADD)
if cburl:
try:
sign = md5.new('uid=%s&key=%s' %
(uid, config.CB_KEY)).hexdigest()
url = cburl % (str(sn) + str(random.randint(10, 99)), uid,
caller, called, echo, '2', '', sign)
logger.info(url)
f = urllib2.urlopen(url)
result = f.read()
except:
logger.info("callback_itf Error: %s" % traceback.format_exc())
return '{"result":"-11"}'
else:
logger.info("%s没有配置回拨分流服务器IP" % caller)
return '{"result":"-11"}'
if result == '0':
return '{"result":"0","balance":"%s"}' % (can_call['balance'])
elif result == '1' or result == '2':
logger.info('ERROR,result:%s' % result)
return '{"result":"-12"}'
else:
logger.info('ERROR,result:%s' % result)
return '{"result":"-13"}'
else:
logger.info("%s呼叫%s捆绑手机号查找失败" % (uid, called))
return '{"result":"-4"}'