def cancel(self):
sql = '''SELECT * FROM `ticket` WHERE `status`=0 ORDER BY `checked_dt` ASC'''
c = self.execSQL(MySQLdb.cursors.DictCursor, sql)
tickets = c.fetchall()
sql = '''UPDATE `ticket` SET `status`=3 WHERE `id`=%s'''
c_agent = DBCache(const.AGENT_PREFIX, config.DEFAULT_EXPIRE,
const.AGENT_SQL)
for t in tickets:
self.execSQL(None, sql, (t['id'],))
ag = c_agent.get(self.dbconn, self.cacheconn, t['agent_id'])
self.writeNotifyOut(ag['default_protocol'], 'ticket_canceled', {})
self.dbconn.commit()
def web_trx(self, agent_id, product, msisdn, pin):
bc = AgentNotifier()
ma = ManageAgent(bc.dbconn, bc.cacheconn)
hlrmap = HLRMap(bc.dbconn, bc.cacheconn)
x = ma.verifyAgent(agent_id, '1234', True, False)
if x['code'] in (agent.AGST_NOTFOUND, agent.AGST_NOTACTIVE):
return json.dumps({'success': 0, 'reason': 'Agent not registered / not active'})
prod_c = DBCache(const.PRODUCT_PREFIX, config.DEFAULT_EXPIRE,
const.PRODUCT_SQL)
prod = prod_c.get(bc.dbconn, bc.cacheconn, product)
if not prod:
return json.dumps({'success': 0, 'reason': 'Invalid product id'})
msisdn = sanitizeMSISDN(msisdn)
method = firstMethod(hlrmap, prod, msisdn)
tran_id = self.topup(bc, x['agent'], msisdn, prod, method)
return json.dumps({'success': 1, 'reason': '', 'tranid': tran_id})
def _expired(self, ticket):
if (ticket['request_dt'] + timedelta(hours=1)) >= datetime.now():
return False
sql = '''UPDATE `ticket` SET `status`=2 WHERE `id`=%s'''
self.execSQL(None, sql, (ticket['id'],))
c_agent = DBCache(const.AGENT_PREFIX, config.DEFAULT_EXPIRE,
const.AGENT_SQL)
ag = c_agent.get(self.dbconn, self.cacheconn, ticket['agent_id'])
if not ag:
return True
self.writeNotifyOut(ag['default_protocol'], 'ticket_expired', {
'agent_id': ag['agent_id'],
'name': ag['agent_name'],
'ticket': thousandSeparator(ticket['given_amount']),
'datetime': ticket['request_dt'].strftime('%d-%m-%y %H:%M'),
})
c_agent = None
return True
def thUSSD(self, bc, msg, msisdn):
sql = '''SELECT `device_id` FROM `sd` WHERE `sd_id`=%s'''
c = bc.dbconn.cursor(MySQLdb.cursors.DictCursor)
c.execute(sql, (ACTIVE_SD,))
x = c.fetchone()
if not x:
print 'INVALID SD'
return
c_device = DBCache(const.DEVICES_PREFIX, config.DEFAULT_EXPIRE,
const.DEVICES_SQL)
sd_dev = c_device.get(bc.dbconn, bc.cacheconn, x['device_id'])
if not sd_dev:
print 'INVALID DEVICE_ID FOR SD'
return
msg = msg.format(msisdn=msisdn, pin=sd_dev['pin'],
device_id=sd_dev['device_id'])
leaf_server = '{0}@{2}/{1}'.format(config.LEAFSERVER, sd_dev['server_id'],
config.MSG_SERVER)
bc.sendMessage(leaf_server, 'JBDV', msg, commit=True)
class MassStockCheck(BaseComponent):
def __init__(self):
BaseComponent.__init__(self, 'MC')
self.c_dev = DBCache(const.DEVICES_PREFIX, config.DEFAULT_EXPIRE,
const.DEVICES_SQL)
self.logger = mylogger('MassStockCheck')
#----------------------
def check(self):
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute('''SELECT `device_id` from `devices`
WHERE `function`=3 and `active`=1 and `status`=1''')
rows = cursor.fetchall()
for r in rows:
if not self.stock_check(r['device_id']):
self.logger.warning('<check> check stock device {0} fail'.format(r['device_id']))
cursor.close()
#----------------------
def stock_check(self, dev_id):
dev = self.c_dev.get(self.dbconn, self.cacheconn, dev_id)
if not dev:
return False
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute('''SELECT `check_stock_counter` from `devices` WHERE
`device_id`=%s LIMIT 1''', (dev_id,))
old_count = int(cursor.fetchone()['check_stock_counter'])
cursor.execute('''SELECT `stock_check_1`,`stock_check_2` from `operator`
WHERE `operator_id`=%s''', (dev['operator_id'],))
r = cursor.fetchone()
server_id, port = dev_id.split('.')
leaf_server = '{0}@{2}/{1}'.format(config.LEAFSERVER, server_id,
config.MSG_SERVER)
parse = multipleReplace({'<pin>': dev['pin'],}, r['stock_check_1'])
msg = '{0},{1},{2}'.format(port, '0', parse)
self.sendMessage(leaf_server, 'JBDV', msg, commit=False)
if len(r['stock_check_2']) > 0:
parse = multipleReplace({'<pin>': dev['pin'],}, r['stock_check_2'])
msg = '{0},{1},{2}'.format(port, '0', parse)
self.sendMessage(leaf_server, 'JBDV', msg, commit=False)
self.dbconn.commit()
cursor.close()
return True
class GetInfo(BaseComponent):
'''Class for getting info from an operator or supplier response such as
destination MSISDN
'''
def __init__(self):
BaseComponent.__init__(self, 'GI')
self.c_words = DBCache(const.WORDS_PREFIX, config.DEFAULT_EXPIRE,
const.WORDS_SQL)
self.reloadWords()
self.haystack = None
self.ref = None
self.prefix = None
self.success = None
self.found_msisdn = None
self.reload = datetime.now() + timedelta(seconds=RELOAD_WORDS)
def __repr__(self):
return 'Haystack: {0}\nPrefix: {1}\nRef marker: {2}\nSuccess: {3}\n' \
'MSISDN: {4}\nMSISDN SQL text: {5}\nFound Ref: {6}'.\
format(self.haystack, self.prefix, self.ref, self.success,
self.found_msisdn, self.getMSISDNSQL(), self.getSN())
def reloadWords(self):
tmp = self.c_words.get(self.dbconn, self.cacheconn, 1)
self.success_words = map(lambda x: x['word'].lower(), tmp)
tmp = self.c_words.get(self.dbconn, self.cacheconn, 0)
self.fail_words = map(lambda x: x['word'].lower(), tmp)
def process(self, haystack, prefix=None, ref='', ussd=False):
self.haystack = haystack
if ref == None:
ref = ''
try:
ref = str(ref)
except:
ref = ''
self.ref = ref
self.prefix = prefix
self.success = self.determineSuccessOrFail()
self.found_msisdn = self.getMSISDN()
if len(self.found_msisdn) == 0 and not ussd:
self.success = 2
def determineSuccessOrFail(self):
if datetime.now() > self.reload:
self.reload = datetime.now() + timedelta(seconds=RELOAD_WORDS)
self.reloadWords()
hs = self.haystack.lower()
# fail = [x['word'] for x in self.fail_words if x['word'] in hs]
fail = [x for x in self.fail_words if x in hs]
if len(fail) > 0:
return 0
# success = [x['word'] for x in self.success_words if x['word'] in hs]
success = [x for x in self.success_words if x in hs]
if len(success) > 0:
return 1
return 2
def getMSISDN(self):
def remove62(nums):
result = []
for i in nums:
if i[:2] == '62':
result.append('0{0}'.format(i[2:]))
else:
result.append(i)
return result
def addZero(nums):
result = []
for i in nums:
if i[0] != '0':
result.append('0{0}'.format(i))
else:
result.append(i)
return result
def prefixFilterGSM(nums):
result = []
for i in nums:
if i[:4] in self.prefix:
result.append(i)
return result
def prefixFilterCDMA(nums):
result = []
for i in nums:
if i[3] in self.prefix or i[4] in self.prefix:
result.append(i)
return result
tmp = re.findall(r'(\d{9,})', self.haystack)
tmp = remove62(tmp)
tmp = addZero(tmp)
# tmp = [x for x in tmp if len(x) > 9]
if not self.prefix:
return tmp
if len(self.prefix[0]) == 4:
tmp = prefixFilterGSM(tmp)
elif len(self.prefix[0]) == 1:
tmp = prefixFilterCDMA(tmp)
return tmp
def getMSISDNSQL(self, field='msisdn_destination'):
first = True
result = ''
for i in self.found_msisdn:
if first:
result = '`{1}`="{0}"'.format(i, field)
first = False;
else:
result = '{0} OR `{2}`="{1}"'.format(result, i, field)
return result
def getSN(self):
if self.ref != '':
hs = self.haystack.find(self.ref) + len(self.ref)
tmp = re.search(r'\b(\w+)\b', self.haystack[hs:])
if not tmp:
return None
return tmp.group(0)
tmp = re.findall(r'\b(\w+)\b', self.haystack)
tmp.sort(lambda x,y: cmp(len(x), len(y)), None, True)
if not self.found_msisdn:
return tmp[0]
elif len(self.found_msisdn) > 1:
return tmp[0]
elif len(self.found_msisdn) == 1:
for i in tmp:
found = [x for x in self.found_msisdn if i[-9:] in x]
if len(found) == 0:
return i
break
class SDLPSync(LowPrioritySync):
'''
'''
def __init__(self):
super(SDLPSync, self).__init__()
self.notifyin_handler = {
'ceksaldo': self._checkDeposit,
'sd_daftar': self._sd_daftar,
'sd_transfer': self._sd_t,
'sd_t500m': self._sd_t500m,
'sd_t1g': self._sd_t1g,
'sd_t2g': self._sd_t2g,
'sd_t5g': self._sd_t5g,
'sd_transfer_ord': self._sd_t,
'sd_t500m_ord': self._sd_t500m,
'sd_t1g_ord': self._sd_t1g,
'sd_t2g_ord': self._sd_t2g,
'sd_t5g_ord': self._sd_t5g,
'3sakti_t': self._3sakti_t,
'3sakti_t_ord': self._3sakti_t,
}
self.c_device = DBCache(const.DEVICES_PREFIX, config.DEFAULT_EXPIRE,
const.DEVICES_SQL)
self.tran_parse = {
't': '{device_id},0,ussd://*897*1*1*{msisdn}*{amount}*{pin}#',
't500m': '{device_id},0,ussd://*897*1*2*{msisdn}*{amount}*{pin}#',
't1g': '{device_id},0,ussd://*897*1*3*{msisdn}*{amount}*{pin}#',
't2g': '{device_id},0,ussd://*897*1*4*{msisdn}*{amount}*{pin}#',
't5g': '{device_id},0,ussd://*897*1*5*{msisdn}*{amount}*{pin}#',
}
self.price = {
't500m': 33500,
't1g': 48000,
't2g': 72000,
't5g': 120000,
}
def doPoint(self):
return
def doBonus(self):
return
@verifyAgent2
def _3sakti_t(self, p, ag):
try:
am = int(p['amount'])
except:
self.writeNotifyOut(p['protocol'], 'amount_not_number',
{'amount': p['amount']})
return
c = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
sql = '''SELECT * FROM `devices` WHERE `operator_id`=13 LIMIT 1'''
c.execute(sql)
sd_dev = c.fetchone()
# sd_dev = self.c_device.get(self.dbconn, self.cacheconn, ch['device_id'])
if not sd_dev:
self.writeNotifyOut(p['protocol'], 'topup_fail', {})
print 'INVALID DEVICE_ID'
return
if am < 10000 or am > 100000000:
self.writeNotifyOut(p['protocol'], 'amount_not_number',
{'amount': p['amount']})
msisdn = sanitizeMSISDN(p['msisdn'])
if 'order' not in p:
p['order'] = 1
prod_id = '3S*{0}'.format(thousandSeparator(am))
if self.checkTrx(p['protocol'], ag['agent_id'], prod_id, p['order'], msisdn):
return
cur_bal = self.dm.getBalance(ag['agent_id'])
if cur_bal < am:
self.writeNotifyOut(p['protocol'], 'agent_not_enough_balance',
{'product_id': am, 'dest': msisdn, 'balance': cur_bal,})
return
new_deposit = self.dm.debit(ag['agent_id'], am,
'transfer ke {0}'.format(msisdn))
# new_deposit = 999
sql = '''INSERT INTO `transaction` (`method`,`agent_id`,`agent_name`,
`reg_protocol`,`deposit`,`base_price`,`sell_price`,`profit`,
`product_id`,`operator_product_id`,`hlr_id`,`operator_id`,
`msisdn_destination`,`order`,`transaction_datetime`,`device_id`,
`status`) VALUES (%(method)s,%(agent_id)s,%(agent_name)s,
%(reg_protocol)s,%(deposit)s,%(base_price)s,%(sell_price)s,%(profit)s,
%(product_id)s,%(operator_product_id)s,%(hlr_id)s,%(operator_id)s,
%(msisdn_destination)s,%(order)s,%(transaction_datetime)s,%(device_id)s,
%(status)s)'''
c.execute(sql, {
'method': 'CH',
'agent_id': ag['agent_id'],
'agent_name': ag['agent_name'],
'reg_protocol': p['protocol'],
'deposit': new_deposit,
'base_price': am,
'sell_price': am,
'profit': 0,
'product_id': prod_id,
'operator_product_id': prod_id,
'hlr_id': 0,
'operator_id': 13,
'msisdn_destination': msisdn,
'order': p['order'],
'transaction_datetime': datetime.now(),
'device_id': sd_dev['device_id'],
'status': const.TR_INPROGRESS,
})
template = '{device_id},0,sms://089611223344,trf.{msisdn}.{amount}.{pin}'
msg = template.format(msisdn=msisdn, amount=p['amount'],
pin=sd_dev['pin'], device_id=sd_dev['device_id'])
leaf_server = '{0}@{2}/{1}'.format(config.LEAFSERVER, sd_dev['server_id'],
config.MSG_SERVER)
self.sendMessage(leaf_server, 'JBDV', msg, commit=False)
print msg
@verifyAgent2
def _sd_daftar(self, p, ag):
c = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
sql = '''SELECT `msisdn` FROM `rs` WHERE `msisdn`=%s'''
msisdn = sanitizeMSISDN(p['msisdn'])
c.execute(sql, (msisdn,))
if c.fetchone():
self.writeNotifyOut(p['protocol'], 'rs_exist', {'msisdn': p['msisdn']})
return
sql = '''SELECT `device_id` FROM `sd` WHERE `sd_id`=%s'''
c.execute(sql, (ACTIVE_SD,))
x = c.fetchone()
if not x:
print 'INVALID SD'
return
sd_dev = self.c_device.get(self.dbconn, self.cacheconn, x['device_id'])
if not sd_dev:
print 'INVALID DEVICE_ID FOR SD'
return
msg = SD_DAFTAR.format(msisdn=msisdn, pin=sd_dev['pin'],
device_id=sd_dev['device_id'])
leaf_server = '{0}@{2}/{1}'.format(config.LEAFSERVER, sd_dev['server_id'],
config.MSG_SERVER)
self.sendMessage(leaf_server, 'JBDV', msg, commit=False)
sql = '''INSERT INTO `rs` (`msisdn`,`sd_id`,`name`,`address`,`agent_id`,`status`)
VALUES (%(msisdn)s,%(sd_id)s,%(name)s,%(address)s,%(agent_id)s,%(status)s)'''
c.execute(sql, {
'msisdn': msisdn,
'sd_id': ACTIVE_SD,
'name': p['name'],
'address': p['address'],
'agent_id': ag['agent_id'],
'status': 9,
})
self.writeNotifyOut(p['protocol'], 'begin_rs_register', {'msisdn': msisdn})
@verifyAgent2
def _sd_t(self, p, ag):
self._sd_transfer(p, ag, 't')
@verifyAgent2
def _sd_t500m(self, p, ag):
self._sd_transfer(p, ag, 't500m')
@verifyAgent2
def _sd_t1g(self, p, ag):
self._sd_transfer(p, ag, 't1g')
@verifyAgent2
def _sd_t2g(self, p, ag):
self._sd_transfer(p, ag, 't2g')
@verifyAgent2
def _sd_t5g(self, p, ag):
self._sd_transfer(p, ag, 't5g')
def _sd_transfer(self, p, ag, prod='t'):
try:
am = int(p['amount'])
except:
self.writeNotifyOut(p['protocol'], 'amount_not_number',
{'amount': p['amount']})
return
min_am = 1
if prod == 't':
min_am = 10000
if am < min_am or am > 100000000:
self.writeNotifyOut(p['protocol'], 'amount_not_number',
{'amount': p['amount']})
return
msisdn = sanitizeMSISDN(p['msisdn'])
if 'order' not in p:
p['order'] = 1
sql = '''SELECT `rs`.`msisdn`,`rs`.`status`,`sd`.`device_id` FROM `rs`
INNER JOIN `sd` ON `sd`.`sd_id` = `rs`.`sd_id`
WHERE `rs`.`msisdn`=%s'''
c = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
c.execute(sql, (msisdn,))
ch = c.fetchone()
if not ch:
self.writeNotifyOut(p['protocol'], 'rs_not_registered',
{'msisdn': msisdn})
return
if int(ch['status']) == 9:
self.writeNotifyOut(p['protocol'], 'rs_not_registered',
{'msisdn': msisdn})
return
if int(ch['status']) == 0:
self.writeNotifyOut(p['protocol'], 'rs_suspended',
{'msisdn': msisdn})
return
sd_dev = self.c_device.get(self.dbconn, self.cacheconn, ch['device_id'])
if not sd_dev:
self.writeNotifyOut(p['protocol'], 'topup_fail', {})
print 'INVALID DEVICE_ID'
return
if prod == 't':
prod_id = 'S#{0}'.format(thousandSeparator(am))
else:
prod_id = '{0}#{1}'.format(prod.upper(), am)
am = self.price[prod] * am
if self.checkTrx(p['protocol'], ag['agent_id'], prod_id, p['order'], msisdn):
return
cur_bal = self.dm.getBalance(ag['agent_id'])
if cur_bal < am:
self.writeNotifyOut(p['protocol'], 'agent_not_enough_balance',
{'product_id': am, 'dest': msisdn, 'balance': cur_bal,})
return
new_deposit = self.dm.debit(ag['agent_id'], am,
'transfer ke {0}'.format(msisdn))
sql = '''INSERT INTO `transaction` (`method`,`agent_id`,`agent_name`,
`reg_protocol`,`deposit`,`base_price`,`sell_price`,`profit`,
`product_id`,`operator_product_id`,`hlr_id`,`operator_id`,
`msisdn_destination`,`order`,`transaction_datetime`,`device_id`,
`status`) VALUES (%(method)s,%(agent_id)s,%(agent_name)s,
%(reg_protocol)s,%(deposit)s,%(base_price)s,%(sell_price)s,%(profit)s,
%(product_id)s,%(operator_product_id)s,%(hlr_id)s,%(operator_id)s,
%(msisdn_destination)s,%(order)s,%(transaction_datetime)s,%(device_id)s,
%(status)s)'''
c.execute(sql, {
'method': 'CH',
'agent_id': ag['agent_id'],
'agent_name': ag['agent_name'],
'reg_protocol': p['protocol'],
'deposit': new_deposit,
'base_price': am,
'sell_price': am,
'profit': 0,
'product_id': prod_id,
'operator_product_id': prod_id,
'hlr_id': 0,
'operator_id': 4,
'msisdn_destination': msisdn,
'order': p['order'],
'transaction_datetime': datetime.now(),
'device_id': sd_dev['device_id'],
'status': const.TR_INPROGRESS,
})
template = self.tran_parse[prod]
msg = template.format(msisdn=msisdn, amount=p['amount'],
pin=sd_dev['pin'], device_id=sd_dev['device_id'])
leaf_server = '{0}@{2}/{1}'.format(config.LEAFSERVER, sd_dev['server_id'],
config.MSG_SERVER)
self.sendMessage(leaf_server, 'JBDV', msg, commit=False)
def checkTrx(self, prot, agent_id, prod_id, order, msisdn):
def getStatusMsg(status):
if status < const.TR_EXECUTED:
return 'DALAM PROSES'
elif status == const.TR_RETRIED:
return 'DALAM PROSES'
elif status >= const.TR_EXECUTED and status < const.TR_FAILED_HLR:
return 'BERHASIL'
return 'GAGAL'
sql = '''SELECT `product_id`,`status`,`order`,`references`,`transaction_datetime`
FROM `transaction`
WHERE `transaction_datetime`>=%s and `transaction_datetime`<=%s
AND `agent_id`=%s and `msisdn_destination`=%s
AND `product_id`=%s and `order`=%s
ORDER BY `transaction_id` DESC LIMIT 1'''
tmp = datetime.now()
start_date = datetime(tmp.year, tmp.month, tmp.day, 0, 0, 0, 0)
end_date = datetime(tmp.year, tmp.month, tmp.day, 23, 59, 59)
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(sql, (start_date, end_date, agent_id, msisdn, prod_id,
order))
r = cursor.fetchone()
if r: # trx exist, return status
topup_status = getStatusMsg(r['status'])
st = int(r['status'])
if st == const.TR_RETRIED:
ref = ''
elif st >= const.TR_EXECUTED and st < const.TR_FAILED_HLR:
ref = 'SN:{0}'.format(r['references'])
else:
ref = ''
self.writeNotifyOut(prot, 'transaction_exist', {
'product_id': self.productIdAndOrder(r['product_id'],
r['order']),
'dest': msisdn,
'timestamp': r['transaction_datetime'].\
strftime('%d/%m/%y %H:%M:%S'),
'status': topup_status,
'ref': ref,
})
return True
return False
class ManageAgent(object):
'''Agent management like new agent, change markup, etc'''
def __init__(self, dbconn=None, cacheconn=None):
self.dbconn = dbconn
self.cacheconn = cacheconn
self.c_agent = DBCache(const.AGENT_PREFIX, config.DEFAULT_EXPIRE,
const.AGENT_SQL)
self.c_regprotocol = DBCache(const.REGPROTOCOL_PREFIX, config.DEFAULT_EXPIRE,
const.REGPROTOCOL_SQL)
self.c_agprice = DBCache(const.AGENTPRICE_PREFIX, config.DEFAULT_EXPIRE,
const.AGENTPRICE_SQL)
def setConn(self, dbconn, cacheconn):
self.dbconn = dbconn
self.cacheconn = cacheconn
def getAgentData(self, agent_id):
return self.c_agent.get(self.dbconn, self.cacheconn, agent_id)
def resolveProtocol(self, prot):
agent = self.c_regprotocol.get(self.dbconn, self.cacheconn, prot)
if not agent:
return None
return agent['agent_id']
def newAgent(self, name, addr, atype, pin, upline, markup, setprice, defprot):
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute('''SELECT `agent_id` from `agent` order by `agent_id` DESC
LIMIT 1 FOR UPDATE''')
r = cursor.fetchone()
if not r:
next_id = '{0:0>{1}}'.format(1, config.AGENT_ID_LENGTH)
markup_upline = 0
else:
next_id = '{0:0>{1}}'.format(int(r['agent_id']) + 1, config.AGENT_ID_LENGTH)
cursor.execute('SELECT `markup` from `agent` where `agent_id`=%s', (upline,))
markup_upline = int(cursor.fetchone()['markup'])
try:
cursor.execute('''INSERT INTO `agent` (`agent_id`,`active`,`agent_name`,
`agent_address`,`agent_type`,`pin`,`upline_id`,`set_price`,
`register_date`,`last_update_by`,`markup`,`markup_upline`,
`default_protocol`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''',
(next_id, 1, name, addr, atype, pin, upline,
setprice, datetime.now(), 'comp:ManageAgent', markup, markup_upline,
defprot))
self.generatePrice(next_id, markup_upline, setprice)
cursor.close()
except:
cursor.close()
raise
# return None
return next_id
def registerProtocols(self, agent_id, protocols):
'''Register an agent's protocol(s) with the system.
protocols is a comma separated values string'''
cursor = self.dbconn.cursor()
to_db = []
prots = protocols.split(',')
for p in prots:
if p.strip() == '':
continue
cursor.execute('''SELECT `reg_protocol` from `regprotocol` WHERE
`reg_protocol`=%s LIMIT 1''', (p,))
if cursor.fetchone():
continue
to_db.append({'protocol': p, 'agent_id': agent_id})
if len(to_db) == 0:
return False
cursor.executemany('''INSERT INTO `regprotocol` (`reg_protocol`,`agent_id`)
VALUES (%(protocol)s,%(agent_id)s)''', to_db)
return True
def clearAgentCache(self, agent_id):
'''Clear an agent's cached data from memcache
'''
self.c_agent.delete(self.cacheconn, agent_id)
cursor = self.dbconn.cursor()
cursor.execute('''SELECT `reg_protocol` from `regprotocol` WHERE
`agent_id`=%s''', (agent_id,))
for row in cursor:
self.cacheconn.delete('{0}_{1}'.format(const.REGPROTOCOL_PREFIX, row[0]))
cursor.close()
def verifyAgent(self, agent_id, pin, cache=True, strict=True):
'''Verify and fetch an agent's data
Returns a dictionary, key "code" & "agent"'''
if cache:
agent = self.c_agent.get(self.dbconn, self.cacheconn, agent_id)
else:
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(const.AGENT_SQL, (agent_id,))
agent = cursor.fetchone()
cursor.close()
if not agent:
return {
'code': AGST_NOTFOUND,
'agent': None,
}
if strict:
stagent = None
else:
stagent = agent
if int(agent['active']) == 0:
return {
'code': AGST_NOTACTIVE,
'agent': stagent,
}
if pin != agent['pin']:
return {
'code': AGST_WRONGPIN,
'agent': stagent,
}
return {
'code': AGST_FOUND,
'agent': agent,
}
def verifyFrontline(self, agent_id, front_id):
'''Verify upline-frontline relationship. Returns True or False'''
cursor = self.dbconn.cursor()
cursor.execute('''SELECT `agent_id` from `agent` where `agent_id`=%s
AND `upline_id`=%s LIMIT 1''', (front_id, agent_id))
exist = cursor.fetchone()
cursor.close()
if exist:
return True
return False
def verifyDownline(self, agent_id, dl_id):
'''Verify upline-downline relationship including frontline.
Returns True or False'''
c = self.dbconn.cursor()
found_id = dl_id
ag_type = 1
while found_id != agent_id and ag_type != 0:
c.execute('''SELECT `agent_type`,`upline_id` FROM `agent`
WHERE `agent_id`=%s''', (found_id,))
ag = c.fetchone()
if not ag:
return False
found_id = ag['upline_id']
ag_type = int(ag['agent_type'])
if found_id != agent_id or ag_type != 0:
return False
elif found_id == agent_id:
return True
return False
def countMarkupDifference(self, agent_id, new_markup):
'''Returns markup difference from the old markup value'''
cursor = self.dbconn.cursor()
cursor.execute('''SELECT `markup_upline` from `agent` where `agent_id`=%s
LIMIT 1''', (agent_id,))
exist = cursor.fetchone()
cursor.close()
if not exist:
return 0
markup_change = new_markup - int(exist[0])
return markup_change
def changeDownlineMarkup(self, _agent_id, new_markup):
cursor = self.dbconn.cursor()
cursor.execute('SELECT SQL_NO_CACHE `markup_upline` from `agent` where `agent_id`=%s',
(_agent_id,))
old_markup = cursor.fetchone()[0]
self.changeDownlineMarkup2(_agent_id, old_markup, new_markup, True)
def changeDownlineMarkup2(self, _agent_id, old_markup, new_markup,
self_update=True):
'''Change all price of an agent and his/her downline(s)
'''
diff_markup = new_markup - old_markup
to_change = self._genDownlineList(_agent_id)
to_change.append(_agent_id)
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
for agent_id in to_change:
cursor.execute('''SELECT `agent_id`,`product_id`,`sell_price`,`markup_upline`
FROM `agent_price` where `agent_id`=%s''', (agent_id,))
for r in cursor:
cursor.execute('''UPDATE `agent_price` set `sell_price`=%s WHERE
`agent_id`=%s and `product_id`=%s''', (int(r['sell_price'])+diff_markup,
r['agent_id'], r['product_id']))
self.c_agprice.delete(self.cacheconn, (r['agent_id'], r['product_id']))
cursor.execute('UPDATE `agent_price` set `markup_upline`=%s WHERE \
`agent_id`=%s', (new_markup, _agent_id,))
if not self_update:
cursor.close()
return
cursor.execute('UPDATE `agent` set `markup_upline`=%s where `agent_id`=%s \
LIMIT 1', (new_markup, _agent_id))
cursor.close()
def changeGlobalMarkup(self, agent_id, new_markup, self_update=True):
'''Change prices for all downlines
'''
frontlines_ = self._getFrontlineList(agent_id)
for frontline in frontlines_:
self.changeDownlineMarkup(frontline, new_markup)
if not self_update:
return
cursor = self.dbconn.cursor()
cursor.execute('UPDATE `agent` set `markup`=%s where `agent_id`=%s LIMIT 1',
(new_markup, agent_id))
cursor.close()
def generatePrice(self, agent_id, markup_=-1, setpriceid=1):
'''Generate an agent's price based on his/her upline's price and markup
'''
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute('SELECT `upline_id`,`markup_upline` from `agent` WHERE \
`agent_id`=%s', (agent_id,))
exist = cursor.fetchone()
if not exist:
return False
upline_id = exist['upline_id']
if markup_ < 0:
markup_upline = int(exist['markup_upline'])
else:
markup_upline = markup_
if int(setpriceid) == 1:
cursor.execute('SELECT `agent_id`,`product_id`,`sell_price`,`markup_upline` \
FROM `agent_price` where `agent_id`=%s', (upline_id,))
else:
cursor.execute('SELECT `product_id`,`sell_price` \
FROM `set_price` where `set_price_id`=%s', (setpriceid,))
for r in cursor:
cursor.execute('''REPLACE INTO `agent_price` set `sell_price`=%s,
`agent_id`=%s, `product_id`=%s, `markup_upline`=%s''',
(int(r['sell_price'])+markup_upline, agent_id, r['product_id'],
markup_upline))
self.c_agprice.delete(self.cacheconn, (agent_id, r['product_id']))
if markup_ < 0:
cursor.close()
return True
cursor.execute('UPDATE `agent` set `markup_upline`=%s where `agent_id`=%s LIMIT 1',
(markup_upline, agent_id))
cursor.close()
self.c_agent.delete(self.cacheconn, agent_id)
return True
def _genDownlineList(self, agent_id):
result = []
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
get_agent = [agent_id,]
while len(get_agent) > 0:
ag = get_agent.pop()
cursor.execute('SELECT `agent_id` from `agent` where `upline_id`=%s', (ag,))
rows = cursor.fetchall()
tmp = map(lambda i: i['agent_id'], rows)
result += tmp
get_agent += tmp
if ag == agent_id:
continue
#result.append('-')
cursor.close()
return result
def _getFrontlineList(self, agent_id):
'''List all frontline. Return a list'''
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
# get frontline
cursor.execute('SELECT `agent_id` from `agent` where `upline_id`=%s', (agent_id,))
result = map(lambda x: x['agent_id'], cursor.fetchall())
cursor.close()
return result
def getProduct(self, bc, prodid):
prod_c = DBCache(const.PRODUCT_PREFIX, config.DEFAULT_EXPIRE,
const.PRODUCT_SQL)
return prod_c.get(bc.dbconn, bc.cacheconn, prodid)
class Authorizer(BaseComponent):
"""Authorize Topup Request
"""
def __init__(self):
BaseComponent.__init__(self, "AU")
self.c_operator = DBCache(
const.OPERATOR_PREFIX,
config.DEFAULT_EXPIRE,
const.OPERATOR_SQL,
user_func={"prefix": lambda x: x.split(",")},
)
self.c_product = DBCache(const.PRODUCT_PREFIX, config.DEFAULT_EXPIRE, const.PRODUCT_SQL)
self.c_agentprice = DBCache(const.AGENTPRICE_PREFIX, config.DEFAULT_EXPIRE, const.AGENTPRICE_SQL)
self.dm = DepositMutation(5, self.dbconn, self.cacheconn)
self.um = UnitMutation(5, self.dbconn, self.cacheconn)
self.log = mylogger("Authorizer", "authorizer.log")
def authorize(self):
requests = self.checkTopupRequest()
if len(requests) == 0:
return False
for req in requests:
agent_id, product_id = (req["agent_id"], req["product_id"])
tr_id, op_id = (req["transaction_id"], req["operator_id"])
prod_status, prod = self.checkProduct(product_id)
if prod_status != PRODUCT_AVAILABLE:
# self.productNotAvailable(req['reg_protocol'], product_id, prod_status)
self.productNotAvailable(req["reg_protocol"], product_id)
self.setDenied(tr_id, const.TR_DENIED_PRODUCT)
continue
ptype = int(prod["type"])
if ptype in (1, 3):
num_status, op_name = self.checkDestination(op_id, req["msisdn_destination"])
if num_status != DEST_OP_MATCH:
self.destOperatorDontMatch(req["reg_protocol"], req["msisdn_destination"], op_name)
self.setDenied(tr_id, const.TR_DENIED_WRONG_NUMBER)
continue
if ptype in (1, 2, 3):
prod_price, markup = self.getPriceForAgent(agent_id, product_id)
if not prod_price:
self.productNotAvailable(req["reg_protocol"], product_id)
self.setDenied(tr_id, const.TR_DENIED_PRODUCT)
continue
if ptype in (1, 2):
bal_stat, balance = self.deductBalance(agent_id, prod_price, tr_id)
if ptype == 3:
balance = self.um.debit(agent_id, product_id, 1, tr_id, "Topup {0}".format(tr_id))
if balance in (NOT_ENOUGH_BALANCE, LOCK_FAILED):
bal_stat = BALANCE_NOT_ENOUGH
balance = 0
if bal_stat != BALANCE_OK:
self.notEnoughBalance(req["reg_protocol"], product_id, balance, req["msisdn_destination"])
self.setDeniedBalance(tr_id, const.TR_DENIED_BALANCE, balance)
continue
self.setAuthorized(tr_id, prod_price, balance, markup)
self.dbconn.commit()
return True
def checkTopupRequest(self):
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(
"SELECT SQL_NO_CACHE `transaction_id`,`agent_id`,"
"`product_id`,`reg_protocol`,`msisdn_destination`,`operator_id` "
"FROM `transaction` where `status`={0} LIMIT 100".format(const.TR_WAIT)
)
requests = cursor.fetchall()
cursor.close()
return requests
def checkProduct(self, prodid):
prod = self.c_product.get(self.dbconn, self.cacheconn, prodid)
if not prod:
return (PRODUCT_NOT_REGISTERED, None)
if prod["active"] == 0:
return (PRODUCT_NOT_SOLD, None)
return (PRODUCT_AVAILABLE, prod)
def checkDestination(self, op_id, dest):
opdata = self.c_operator.get(self.dbconn, self.cacheconn, op_id)
if opdata == None:
print "No data for operator id {0}".format(op_id)
self.log.critical("No data for operator id {0}".format(op_id))
return None
try:
if opdata["type"] == "G":
if dest[0:4] in opdata["prefix"]:
return (DEST_OP_MATCH, opdata["operator_name"])
else:
return (DEST_OP_DONT_MATCH, opdata["operator_name"])
elif opdata["type"] == "C":
if (dest[3] in opdata["prefix"]) or (dest[4] in opdata["prefix"]):
return (DEST_OP_MATCH, opdata["operator_name"])
else:
return (DEST_OP_DONT_MATCH, opdata["operator_name"])
except:
return (DEST_OP_DONT_MATCH, opdata["operator_name"])
def deductBalance(self, agentid, price, tuid):
balance = self.dm.getBalance(agentid)
if balance < price:
return (BALANCE_NOT_ENOUGH, balance)
balance = self.dm.debit(agentid, price, "Topup {0}".format(tuid))
if balance == LOCK_FAILED:
return (BALANCE_NOT_ENOUGH, 0)
return (BALANCE_OK, balance)
def setAuthorized(self, ids, price, balance, markup):
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(
"""UPDATE `transaction` set `status`={0}, `sell_price`=%s,
`deposit`=%s, `markup_margin`=%s where `transaction_id`=%s""".format(
const.TR_AUTHORIZED
),
(price, balance, markup, ids),
)
cursor.close()
def setDenied(self, ids, status):
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("UPDATE `transaction` set `status`={0} " "WHERE `transaction_id`=%s".format(status), (ids,))
cursor.close()
def setDeniedBalance(self, ids, status, balance):
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(
"UPDATE `transaction` set `status`={0}," "`deposit`=%s where `transaction_id`=%s".format(status),
(balance, ids),
)
cursor.close()
def getPriceForAgent(self, agentid, prodid):
ap = self.c_agentprice.get(self.dbconn, self.cacheconn, (agentid, prodid))
if not ap:
self.log.warning(
"<getPriceForAgent> price not found for agent " "{0} and product {1}".format(agentid, prodid)
)
return (None, None)
return (int(ap["sell_price"]), int(ap["markup_upline"]))
def productNotAvailable(self, prot, prodid):
self.writeNotifyOut(prot, "prod_not_registered", {"product_id": prodid})
def notEnoughBalance(self, prot, prodid, balance, dest):
self.writeNotifyOut(
prot,
"agent_not_enough_balance",
{"product_id": prodid, "balance": thousandSeparator(balance), "dest": dest},
)
def destOperatorDontMatch(self, prot, num, opname):
self.writeNotifyOut(prot, "num_op_dont_match", {"dest": num, "operator": opname})
