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