def agent_change_set_price(self, agent_id, set_price, pin):
bc = AgentNotifier()
c = bc.dbconn.cursor(MySQLdb.cursors.DictCursor)
c.execute ('''SELECT `product_id` FROM `agent_price`
WHERE `agent_id`=%s''', (agent_id,))
to_del = c.fetchall()
c.execute('''DELETE FROM `agent_price` WHERE `agent_id`=%s''',
(agent_id,))
am = ManageAgentPrice(bc.dbconn, bc.cacheconn)
am.generatePrice(agent_id, setpriceid=set_price)
bc.dbconn.commit()
c_price = DBCache(const.AGENTPRICE_PREFIX, config.DEFAULT_EXPIRE,
const.AGENTPRICE_SQL)
for k in to_del:
c_price.delete(bc.cacheconn, (agent_id, k['product_id']))
class DeviceHelper(object):
'''Helper for device management'''
def __init__(self):
self.pin = hashlib.md5('!elogic123').hexdigest()
self.device_update = {}
self.c_dev = DBCache(const.DEVICES_PREFIX, config.DEFAULT_EXPIRE,
const.DEVICES_SQL)
self.logger = mylogger('AdminHelper')
@cherrypy.expose
def device_preupdate(self, device_id, pin):
bc = AgentNotifier()
tmp = ' OR '.join(map(lambda x: '`device_id`="{0}"'.format(x),
device_id.split(',')))
sql = 'SELECT * FROM `devices` WHERE {0}'.format(tmp)
c = bc.dbconn.cursor(MySQLdb.cursors.DictCursor)
c.execute(sql)
for row in c:
self.device_update[row['device_id']] = row
return json.dumps({'success': 1,})
@cherrypy.expose
def device_postupdate(self, device_id, pin):
bc = AgentNotifier()
lb = LBTopup(bc.dbconn)
lb.cleanRebuild()
bc.cacheconn.delete(SMSSENDER_CACHEKEY)
bc.cacheconn.delete(SMSSENDER_CACHEKEY2)
for devid in self.device_update:
self.c_dev.delete(bc.cacheconn, devid)
self.device_update = {}
return json.dumps({'success': 1,})
@cherrypy.expose
def device_reconnect(self, device_id, pin):
bc = AgentNotifier()
server_id, _, port = device_id.partition('.')
bc.sendMessage('{0}@{1}/{2}'.format(config.LEAFSERVER,
config.MSG_SERVER, server_id), 'RCMD', '{0},1'.format(port))
return json.dumps({'success': 1,})
class ManageAgentPrice(ManageAgent):
'''Set Price management'''
def __init__(self, dbconn=None, cacheconn=None):
super(ManageAgentPrice, self).__init__(dbconn, cacheconn)
self.c_prod = DBCache(const.PRODUCT_PREFIX, config.DEFAULT_EXPIRE,
const.PRODUCT_SQL)
def changePrice(self, setpriceid, product_id, sell_price=-1, commit=True):
'''Change a product price'''
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
if sell_price < 0:
cursor.execute('SELECT `sell_price` from `set_price` where `set_price_id`'
'=%s AND `product_id`=%s LIMIT 1', (setpriceid, product_id))
sell_price = int(cursor.fetchone()['sell_price'])
l = self._genGroupMarkup(setpriceid)
if int(setpriceid) == 1:
l.append(('00001', 0))
for ag, mu in l:
cursor.execute('''UPDATE `agent_price` set `sell_price`=%s WHERE
`agent_id`=%s and `product_id`=%s''', (sell_price + mu, ag,
product_id,))
self.c_agprice.delete(self.cacheconn, (ag, product_id))
if commit:
self.dbconn.commit()
cursor.close()
return True
def addProduct(self, setpriceid, product_id, sell_price=-1, commit=True):
'''Add a product to price list'''
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
# cursor.execute('''SELECT `product_id` from `agent_price` WHERE
# `agent_id`=%s and `product_id`=%s LIMIT 1''', ('00001', product_id,))
# if cursor.fetchone():
# return False
if sell_price < 0:
cursor.execute('SELECT `sell_price` from `set_price` where `set_price_id`'
'=%s AND `product_id`=%s LIMIT 1', (setpriceid, product_id))
sell_price = int(cursor.fetchone()['sell_price'])
l = self._genGroupMarkup(setpriceid)
if int(setpriceid) == 1:
l.append(('00001', 0))
for ag, mu in l:
cursor.execute('''REPLACE INTO `agent_price` (`agent_id`,`product_id`,
`sell_price`,`markup_upline`,`status`,`last_update`) VALUES
(%s,%s,%s,%s,%s,%s)''', (ag, product_id, sell_price + mu,
0, 1, datetime.now()))
if commit:
self.dbconn.commit()
cursor.close()
return True
def removeProduct(self, setpriceid, product_id, commit=True):
'''Remove a product from price list'''
# if not self.c_prod.get(self.dbconn, self.cacheconn, product_id):
# return False
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute('''UPDATE `product` set `active`=0 where `product_id`=%s''',
(product_id,))
self.c_prod.delete(self.cacheconn, product_id)
cursor.execute('''DELETE FROM `agent_price` where `product_id`=%s''',
(product_id,))
cursor.execute('SELECT `agent_id` from `agent` where `set_price`=%s',
(setpriceid,))
for r in cursor:
self.c_agprice.delete(self.cacheconn, (r['agent_id'], product_id))
if commit:
self.dbconn.commit()
cursor.close()
return True
def _genGroupMarkup(self, setpriceid):
result = []
cursor = self.dbconn.cursor(MySQLdb.cursors.DictCursor)
# cursor.execute('SELECT `markup_upline` from `agent` where `upline_id`=%s',
# (agent_id,))
# print cursor.fetchone()
# init_mark = int(cursor.fetchone()['markup_upline'])
get_agent = [('00001', 0),]
while len(get_agent) > 0:
ag, mu = get_agent.pop()
cursor.execute('SELECT `agent_id`, `markup_upline` from `agent` WHERE '
'`upline_id`=%s and `set_price`=%s', (ag, setpriceid))
rows = cursor.fetchall()
tmp = map(lambda i: (i['agent_id'], int(i['markup_upline']) + mu), rows)
result += tmp
get_agent += tmp
cursor.close()
return result
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
