class UploadDao(object):
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
def __create_table(self, db):
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_account_image (
id bigint NOT NULL AUTO_INCREMENT,
filename varchar(255) NOT NULL,
imagebinary binary(225) NOT NULL,
account_id bigint NOT NULL,
FOREIGN KEY (account_id) REFERENCES weicbd_account(id),
PRIMARY KEY (id)
) DEFAULT CHARACTER SET=utf8;
''')
def create_images(self, filename, imagebinary, account_id):
images = self.db.execute(
'insert into weicbd_account_image(filename,imagebinary,account_id) values(%s,%s,%s)',
(filename, imagebinary, account_id))
self.db.commit()
return images
def all_images(self, mg):
return self.db.fetchall(
'select id,filename from weicbd_account_image where account_id= %s ',
(mg, ))
def find_images(self, id):
return self.db.fetchone(
'select imagebinary from weicbd_account_image where id=%s', (id, ))
class UploadDao(object):
def __init__(self):
self.db =Mysqldb()
self.__create_table(self.db)
def __create_table(self, db):
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_account_image (
id bigint NOT NULL AUTO_INCREMENT,
filename varchar(255) NOT NULL,
imagebinary binary(225) NOT NULL,
account_id bigint NOT NULL,
FOREIGN KEY (account_id) REFERENCES weicbd_account(id),
PRIMARY KEY (id)
) DEFAULT CHARACTER SET=utf8;
''')
def create_images(self, filename,imagebinary,account_id):
images = self.db.execute('insert into weicbd_account_image(filename,imagebinary,account_id) values(%s,%s,%s)',(filename,imagebinary,account_id))
self.db.commit()
return images
def all_images(self,mg):
return self.db.fetchall('select id,filename from weicbd_account_image where account_id= %s ',(mg,))
def find_images(self,id):
return self.db.fetchone('select imagebinary from weicbd_account_image where id=%s',(id,))
class MessagePatternDao(object):
def __init__(self):
from db import Mysqldb
self.db = Mysqldb()
self.__create_table(self.db)
def __create_table(self, db):
# 事件规则使用表：weicbd_mp_message_patterns(id, site_id, pattern, class, settings)
# 根据获得的xml文本，匹配处理插件
# pattern 为一个xpath 表达式，如：/xml[MsgType="event" and Event="CLICK"]
# 当表达式找到元素，则匹配这个class
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_message_patterns (
id bigint NOT NULL AUTO_INCREMENT,
site_id bigint NOT NULL,
pattern varchar(255) NOT NULL,
class varchar(255) NOT NULL,
settings text,
PRIMARY KEY (id),
FOREIGN KEY (site_id) REFERENCES weicbd_mp_site(id)
) DEFAULT CHARACTER SET=utf8;
''')
db.commit()
def get_mp_plugins_by_token(self, token):
return self.db.fetchall(
'SELECT p.pattern, p.class, p.settings FROM weicbd_mp_message_patterns p, weicbd_mp_site s WHERE p.site_id = s.id AND s.token=%s',
(token, ))
def get_mp_plugins_by_id(self, id):
return self.db.fetchall(
'SELECT id, pattern, class, settings FROM weicbd_mp_message_patterns WHERE site_id=%s',
(id, ))
def add_plugin(self, site_id, pattern, cn, settings):
rt = self.db.execute(
'INSERT IGNORE INTO weicbd_mp_message_patterns(site_id, pattern, class, settings) VALUES(%s, %s, %s, %s)',
(site_id, pattern, cn, settings))
self.db.commit()
return rt
def remove_plugin(self, id):
self.db.execute('DELETE FROM weicbd_mp_message_patterns WHERE id=%s',
(id, ))
self.db.commit()
def weicbd_register(self, ghid, openid):
db = Mysqldb()
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_member_rel (
id bigint NOT NULL AUTO_INCREMENT,
ghid varchar(255) NULL,
openid varchar(255) NULL,
memberid bigint NULL,
FOREIGN KEY (memberid) REFERENCES weicbd_member(id),
PRIMARY KEY (id))''')
result = db.fetchone(
'select id, ghid, openid from weicbd_mp_member_rel where ghid = %s and openid = %s',
(ghid, openid)) or (None, )
if result is None:
db.execute(
'insert into weicbd_mp_member_rel (ghid,openid) values(%s,%s)',
(ghid, openid))
db.commit()
class MessagePatternDao(object):
def __init__(self):
from db import Mysqldb
self.db = Mysqldb()
self.__create_table(self.db)
def __create_table(self, db):
# 事件规则使用表：weicbd_mp_message_patterns(id, site_id, pattern, class, settings)
# 根据获得的xml文本，匹配处理插件
# pattern 为一个xpath 表达式，如：/xml[MsgType="event" and Event="CLICK"]
# 当表达式找到元素，则匹配这个class
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_message_patterns (
id bigint NOT NULL AUTO_INCREMENT,
site_id bigint NOT NULL,
pattern varchar(255) NOT NULL,
class varchar(255) NOT NULL,
settings text,
PRIMARY KEY (id),
FOREIGN KEY (site_id) REFERENCES weicbd_mp_site(id)
) DEFAULT CHARACTER SET=utf8;
''')
db.commit()
def get_mp_plugins_by_token(self, token):
return self.db.fetchall('SELECT p.pattern, p.class, p.settings FROM weicbd_mp_message_patterns p, weicbd_mp_site s WHERE p.site_id = s.id AND s.token=%s', (token,))
def get_mp_plugins_by_id(self, id):
return self.db.fetchall('SELECT id, pattern, class, settings FROM weicbd_mp_message_patterns WHERE site_id=%s', (id,))
def add_plugin(self, site_id, pattern, cn, settings):
rt = self.db.execute('INSERT IGNORE INTO weicbd_mp_message_patterns(site_id, pattern, class, settings) VALUES(%s, %s, %s, %s)',
(site_id, pattern, cn, settings))
self.db.commit()
return rt
def remove_plugin(self, id):
self.db.execute('DELETE FROM weicbd_mp_message_patterns WHERE id=%s', (id,))
self.db.commit()
def __init__(self):
from db import Mysqldb
self.db = Mysqldb()
self.__create_table(self.db)
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
class MenuDao(object):
def __init__(self):
from db import Mysqldb
self.db = Mysqldb()
self.__create_table(self.db)
def __create_table(self, db):
db.execute('''
create table if not exists weicbd_mp_teststatus(
id bigint AUTO_INCREMENT,
ghid varchar(255) NOT NULL,
openid varchar(255) NOT NULL,
state varchar(50) not null,
PRIMARY KEY (id)
)
''')
db.execute('''
create table if not exists weicbd_mp_menu(
id bigint AUTO_INCREMENT,
menu varchar(255) not null,
menuid varchar(255) not null,
content text null,
PRIMARY KEY (id)
)
''')
db.commit()
# 创建/修改状态
def create_status(self, ghid, openid, state):
result = self.db.fetchone(
'select * from weicbd_mp_teststatus where ghid=%s and openid=%s',
(ghid, openid))
if result is not None:
rt = self.db.execute(
'update weicbd_mp_teststatus set state=%s where ghid=%s and openid=%s',
(state, ghid, openid))
self.db.commit()
return rt
else:
rt = self.db.execute(
'REPLACE INTO weicbd_mp_teststatus (ghid,openid,state) values(%s,%s,%s)',
(ghid, openid, state))
self.db.commit()
return rt
# 查看当前状态
def query_current_status(self, ghid, openid):
state, = self.db.fetchone(
'select state from weicbd_mp_teststatus where ghid=%s and openid=%s',
(ghid, openid)) or ('out_wall', )
return state
# 返回当前用户信息
def query_current_userinfo(self, ghid):
return self.db.fetchone(
'select ghid,openid,state weicbd_mp_teststatus wehere ghid=%s',
(ghid, ))
# 创建目录
def create_menu(self, menu, menuid, content):
rt = self.db.execute(
'insert into weicbd_mp_menu (menu,menuid,content) values(%s,%s,%s)',
(menu, menuid, content))
self.db.commit()
return rt
# 查一级菜单列表
def query_all_menu(self):
return self.db.fetchall(
'select menu,content from weicbd_mp_menu where menuid=0')
# 查二级菜单
def query_bottom_menu(self, menu):
return self.db.fetchall(
'select menuid,content from weicbd_mp_menu where menu=%s and menuid>0',
(menu, ))
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
class UserDao(object):
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
# 创建表
def __create_table(self, db):
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_member (
id bigint NOT NULL AUTO_INCREMENT,
mobile varchar(20) NOT NULL,
email varchar(30) NULL,
name varchar(30) NULL,
sex varchar(10) NULL,
birth varchar(50)NULL,
password varchar(30) NULL,
constraint sex check (sex='男' or sex='女') ,
PRIMARY KEY (id)
) DEFAULT CHARACTER SET=utf8;
''')
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_vcode(
id bigint NOT NULL AUTO_INCREMENT,
mobile varchar(20) NOT NULL,
vcode int NULL,
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) DEFAULT CHARACTER SET=utf8;
''')
# self.db.commit()
# 用户注册
def user_register(self, mobile, mail, name, sex, birth, password, ghid,
openid):
fmobile = self.db.fetchone(
'select id from weicbd_member where mobile =%s', (mobile, ))
# if fmobile == None:
# validate = self.db.fetchone('select * from weicbd_mp_vcode where mobile=%s and vcode=%s',(mobile,vcode))
if fmobile is not None:
self.db.execute(
'update weicbd_mp_member_rel set memberid=%s where ghid = %s and openid = %s',
(fmobile, ghid, openid)) or (None, )
self.db.commit()
else:
rt = self.db.execute(
'insert into weicbd_member (mobile,email,name,sex,birth,password) values(%s, %s, %s, %s, %s, %s)',
(mobile, mail, name, sex, birth, password))
self.db.commit()
res, = self.db.fetchone(
'select id from weicbd_member where mobile=%s', (mobile, ))
if res is not None:
self.db.execute(
'update weicbd_mp_member_rel set memberid=%s where ghid = %s and openid = %s',
(res, ghid, openid)) or (None, )
self.db.commit()
return rt
# 用户登录
def user_login(self, mobile, password):
return self.db.fetchone(
'select * from weicbd_member where mobile=%s and password=%s',
(mobile, password))
# 用户信息
def user_info(self, mail, name, sex, password, birth, mobile):
minfo = self.db.fetchone(
'select mobile weicbd_mp_vcode where mobile=%s', (mobile, ))
if minfo is not None:
self.db.execute(
'update weicbd_member set email=%s,name=%s,sex=%s,password=%s,birth=%s where mobile=%s',
(mail, name, sex, password, birth, mobile))
self.db.commit()
else:
self.send_error(401)
# 验证码
def insert_vcode(self, mobile, vcode):
fmobile = self.db.fetchone(
'select mobile from weicbd_member where mobile = %s', (mobile, ))
if fmobile == None:
self.db.execute(
'insert into weicbd_mp_vcode (mobile,vcode) values(%s,%s)',
(mobile, vcode))
self.db.commit()
class PatternDao(object):
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
#表
def __create_table(self,db):
db.execute('''
CREATE TABLE IF NOT EXISTS weicdb_recv_pattern(
id bigint NOT NULL AUTO_INCREMENT,
pattern varchar(515) not null,
name varchar(255) not null,
PRIMARY KEY (id)
)''')
# 查寻
def all_pattern(self):
return self.db.fetchall("select id,pattern,name from weicdb_recv_pattern")
# 更新
def put_pattern(self, pattern, name, pid):
self.db.execute('update weicdb_recv_pattern set pattern = %s, name = %s where id = %s', (pattern, name, pid))
self.db.commit()
# 新建
def add_pattern(self, pattern, name):
pid = self.db.execute("insert into weicdb_recv_pattern(pattern,name) values(%s, %s)", (pattern, name))
self.db.commit()
return pid
# 删除
def del_pattern(self, pid):
rt = self.db.execute("delete from weicdb_recv_pattern where id = %s", (pid,))
self.db.commit()
return rt
class UserDao(object):
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
# 创建表
def __create_table(self,db):
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_member (
id bigint NOT NULL AUTO_INCREMENT,
mobile varchar(20) NOT NULL,
email varchar(30) NULL,
name varchar(30) NULL,
sex varchar(10) NULL,
birth varchar(50)NULL,
password varchar(30) NULL,
constraint sex check (sex='男' or sex='女') ,
PRIMARY KEY (id)
) DEFAULT CHARACTER SET=utf8;
''')
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_vcode(
id bigint NOT NULL AUTO_INCREMENT,
mobile varchar(20) NOT NULL,
vcode int NULL,
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) DEFAULT CHARACTER SET=utf8;
''')
# self.db.commit()
# 用户注册
def user_register(self, mobile, mail, name, sex, birth, password,ghid,openid):
fmobile = self.db.fetchone('select id from weicbd_member where mobile =%s',(mobile,))
# if fmobile == None:
# validate = self.db.fetchone('select * from weicbd_mp_vcode where mobile=%s and vcode=%s',(mobile,vcode))
if fmobile is not None:
self.db.execute('update weicbd_mp_member_rel set memberid=%s where ghid = %s and openid = %s',(fmobile, ghid, openid)) or (None, )
self.db.commit()
else:
rt = self.db.execute('insert into weicbd_member (mobile,email,name,sex,birth,password) values(%s, %s, %s, %s, %s, %s)', (mobile, mail, name, sex, birth, password))
self.db.commit()
res, = self.db.fetchone('select id from weicbd_member where mobile=%s',(mobile, ))
if res is not None:
self.db.execute('update weicbd_mp_member_rel set memberid=%s where ghid = %s and openid = %s',(res, ghid, openid)) or (None, )
self.db.commit()
return rt
# 用户登录
def user_login(self, mobile,password):
return self.db.fetchone('select * from weicbd_member where mobile=%s and password=%s',(mobile,password))
# 用户信息
def user_info(self, mail, name, sex, password, birth, mobile):
minfo = self.db.fetchone('select mobile weicbd_mp_vcode where mobile=%s', (mobile,))
if minfo is not None:
self.db.execute('update weicbd_member set email=%s,name=%s,sex=%s,password=%s,birth=%s where mobile=%s',(mail, name, sex, password, birth, mobile))
self.db.commit()
else:
self.send_error(401)
# 验证码
def insert_vcode(self, mobile, vcode):
fmobile = self.db.fetchone('select mobile from weicbd_member where mobile = %s', (mobile,))
if fmobile == None:
self.db.execute('insert into weicbd_mp_vcode (mobile,vcode) values(%s,%s)', (mobile, vcode))
self.db.commit()
class MPSiteDao(object):
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
def __create_table(self, db):
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_site (
id bigint NOT NULL AUTO_INCREMENT,
user_id bigint NOT NULL,
token varchar(32) NOT NULL,
name varchar(255) DEFAULT NULL,
ghid varchar(255) DEFAULT NULL,
appid varchar(255) DEFAULT NULL,
secret varchar(255) DEFAULT NULL,
validated boolean DEFAULT FALSE,
validateTime timestamp DEFAULT 0,
enabled boolean DEFAULT TRUE,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES weicbd_account(id),
UNIQUE KEY (token)
) DEFAULT CHARACTER SET=utf8;
''')
def close(self):
self.db.commit()
def access(self, token):
n, = self.db.fetchone('SELECT COUNT(id) FROM weicbd_mp_site WHERE token=%s', (token, ))
if n == 1:
self.db.execute('UPDATE weicbd_mp_site SET validated=%s, validateTime=%s WHERE token=%s', (True, datetime.datetime.now(), token))
self.db.commit()
return True
else:
return False
def get_by_token(self, wxtoken):
return self.db.fetchone('''
SELECT id, name, ghid, appid, secret, validated, validateTime, enabled FROM weicbd_mp_site WHERE token=%s
''', (wxtoken, ))
def create(self, wxtoken):
self.db.execute('INSERT IGNORE INTO weicbd_mp_site(token) VALUES(%s)', (wxtoken, ))
def get_all_sites(self):
return self.db.fetchall('SELECT id, token, name, ghid, appid, secret, validated, validateTime, enabled FROM weicbd_mp_site')
def update_sites(self, id, name, ghid, appid, secret, enabled):
self.db.execute('UPDATE weicbd_mp_site SET name=%s, ghid=%s, appid=%s, secret=%s, enabled=%s WHERE id=%s',
(name, ghid, appid, secret, enabled, id))
self.db.commit()
def get_all_sites_by_username(self, username):
role, = self.db.fetchone('SELECT r.role FROM weicbd_role r, weicbd_account u WHERE u.role_id=r.id AND u.username=%s', (username,))
if role == 'admin':
return self.db.fetchall('''
SELECT m.id, m.token, m.name, m.ghid, m.appid, m.secret, m.validated, m.validateTime, m.enabled, u.id, u.username
FROM weicbd_mp_site m, weicbd_account u
WHERE m.user_id=u.id
''')
else:
return self.db.fetchall('''
SELECT m.id, m.token, m.name, m.ghid, m.appid, m.secret, m.validated, m.validateTime, m.enabled, u.id, u.username
FROM weicbd_mp_site m, weicbd_account u
WHERE m.user_id=u.id AND u.username=%s
''', (username,))
def own(self, username, mpid):
r, = self.db.fetchone('''
SELECT COUNT(u.id) FROM weicbd_account u, weicbd_role r WHERE u.role_id=u.id AND u.username=%s AND r.role=%s
''', (username, 'admin'))
if r == 1:
return True
n, = self.db.fetchone('''
SELECT COUNT(m.id) FROM weicbd_account u, weicbd_mp_site m WHERE m.user_id=u.id AND u.username=%s AND m.id=%s
''', (username, mpid))
return n == 1
def update(self, mpid, token, name, ghid, appid, secret, enabled):
rt = self.db.execute('UPDATE weicbd_mp_site SET token=%s, name=%s, ghid=%s, appid=%s, secret=%s, enabled=%s WHERE id=%s',
(token, name, ghid, appid, secret, enabled, mpid))
self.db.commit()
return rt
def delete(self, mpid):
rt = self.db.execute('DELETE FROM weicbd_mp_site WHERE id=%s',
(mpid,))
self.db.commit()
return rt
def add(self, username, token, name, ghid, appid, secret):
rt = self.db.execute('INSERT INTO weicbd_mp_site(token, name, ghid, appid, secret, user_id) SELECT %s, %s, %s, %s, %s, id FROM weicbd_account WHERE username=%s',
(token, name, ghid, appid, secret, username))
self.db.commit()
return rt
class PatternDao(object):
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
#表
def __create_table(self, db):
db.execute('''
CREATE TABLE IF NOT EXISTS weicdb_recv_pattern(
id bigint NOT NULL AUTO_INCREMENT,
pattern varchar(515) not null,
name varchar(255) not null,
PRIMARY KEY (id)
)''')
# 查寻
def all_pattern(self):
return self.db.fetchall(
"select id,pattern,name from weicdb_recv_pattern")
# 更新
def put_pattern(self, pattern, name, pid):
self.db.execute(
'update weicdb_recv_pattern set pattern = %s, name = %s where id = %s',
(pattern, name, pid))
self.db.commit()
# 新建
def add_pattern(self, pattern, name):
pid = self.db.execute(
"insert into weicdb_recv_pattern(pattern,name) values(%s, %s)",
(pattern, name))
self.db.commit()
return pid
# 删除
def del_pattern(self, pid):
rt = self.db.execute("delete from weicdb_recv_pattern where id = %s",
(pid, ))
self.db.commit()
return rt
class MenuDao(object):
def __init__(self):
from db import Mysqldb
self.db = Mysqldb()
self.__create_table(self.db)
def __create_table(self,db):
db.execute('''
create table if not exists weicbd_mp_teststatus(
id bigint AUTO_INCREMENT,
ghid varchar(255) NOT NULL,
openid varchar(255) NOT NULL,
state varchar(50) not null,
PRIMARY KEY (id)
)
''')
db.execute('''
create table if not exists weicbd_mp_menu(
id bigint AUTO_INCREMENT,
menu varchar(255) not null,
menuid varchar(255) not null,
content text null,
PRIMARY KEY (id)
)
''')
db.commit()
# 创建/修改状态
def create_status(self, ghid, openid, state):
result = self.db.fetchone('select * from weicbd_mp_teststatus where ghid=%s and openid=%s',(ghid,openid))
if result is not None:
rt = self.db.execute('update weicbd_mp_teststatus set state=%s where ghid=%s and openid=%s',(state,ghid,openid))
self.db.commit()
return rt
else:
rt = self.db.execute('REPLACE INTO weicbd_mp_teststatus (ghid,openid,state) values(%s,%s,%s)',(ghid,openid,state))
self.db.commit()
return rt
# 查看当前状态
def query_current_status(self,ghid,openid):
state, = self.db.fetchone('select state from weicbd_mp_teststatus where ghid=%s and openid=%s',(ghid,openid)) or('out_wall',)
return state
# 返回当前用户信息
def query_current_userinfo(self, ghid):
return self.db.fetchone('select ghid,openid,state weicbd_mp_teststatus wehere ghid=%s',(ghid,))
# 创建目录
def create_menu(self, menu, menuid, content):
rt = self.db.execute('insert into weicbd_mp_menu (menu,menuid,content) values(%s,%s,%s)',(menu,menuid,content))
self.db.commit()
return rt
# 查一级菜单列表
def query_all_menu(self):
return self.db.fetchall('select menu,content from weicbd_mp_menu where menuid=0')
# 查二级菜单
def query_bottom_menu(self,menu):
return self.db.fetchall('select menuid,content from weicbd_mp_menu where menu=%s and menuid>0',(menu,))
class MessageDaoHandler(object):
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
def __create_table(self,db):
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_message_patterns (
id bigint NOT NULL AUTO_INCREMENT,
site_id bigint NOT NULL,
pattern varchar(255) NOT NULL,
class varchar(255) NOT NULL,
settings text,
PRIMARY KEY (id),
FOREIGN KEY (site_id) REFERENCES weicbd_mp_site(id)
) DEFAULT CHARACTER SET=utf8;
''')
db.commit()
# 查找所有
def query_message_pattern(self,tid):
return self.db.fetchall('select id,site_id,pattern,class,settings from weicbd_mp_message_patterns where site_id=%s',(tid,))
# 查找当前用户的所有信息
def query_user_message_pattern(self,tid):
return self.db.fetchall('select id,pattern,class,settings from weicbd_mp_message_patterns where site_id= %s ',(tid,))
# 查当前用户 siteid
# def query_username(self,siteid):
# return self.db.fetchone('select a.username from weicbd_account as a,weicbd_mp_site as s where a.id=s.user_id and s.id=%s',(siteid,))
# 查当前用户 rid
# def query_rid_username(self,rid):
# siteid = self.db.fetchone('select m.site_id from weicbd_mp_message_patterns as m,weicbd_mp_site as s where m.site_id=s.id and m.id=%s',(rid,))
# return self.db.fetchone('select a.username from weicbd_account as a,weicbd_mp_site as s where a.id=s.user_id and s.id=%s',(siteid[0],))
# 新建
def create_message_pattern(self,userid,pattern,claz,settings):
rt = self.db.execute('insert into weicbd_mp_message_patterns (site_id,pattern,class,settings) values(%s,%s,%s,%s)',(userid,pattern,claz,settings))
self.db.commit()
return rt
# 编辑
def put_message_pattern(self,rid,pattern,claz,setting):
rt = self.db.execute("update weicbd_mp_message_patterns set pattern=%s,class=%s,settings=%s where id=%s",(pattern,claz,setting,rid))
self.db.commit()
return rt
# 删除
def remove_message_pattern(self,rid):
rt = self.db.execute('delete from weicbd_mp_message_patterns where id = %s',(rid,))
self.db.commit()
return rt
def validmp(self, username, mpid):
n, = self.db.fetchone('SELECT COUNT(a.ID) FROM weicbd_account a, weicbd_mp_site m WHERE m.user_id=a.id AND a.username=%s AND m.id=%s',(username, mpid))
return n == 1
def validptn(self, username, rid):
n, = self.db.fetchone('SELECT COUNT(a.ID) FROM weicbd_account a, weicbd_mp_site m, weicbd_mp_message_patterns p WHERE p.site_id=m.id AND m.user_id=a.id AND a.username=%s AND p.id=%s',(username, rid))
return n == 1
class WallDao(object):
def __init__(self):
from db import Mysqldb
self.db = Mysqldb()
self.__create_table(self.db)
def __create_table(self, db):
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_wall_state (
id bigint NOT NULL AUTO_INCREMENT,
ghid varchar(255) NOT NULL,
openid varchar(255) NOT NULL,
state varchar(20) NOT NULL,
PRIMARY KEY (id),
UNIQUE(ghid, openid)
) DEFAULT CHARACTER SET=utf8;
''')
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_wall_message (
id bigint NOT NULL AUTO_INCREMENT,
ghid varchar(255) NOT NULL,
openid varchar(255) NOT NULL,
content text,
PRIMARY KEY (id)
) DEFAULT CHARACTER SET=utf8;
''')
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_spec_wall (
id bigint NOT NULL AUTO_INCREMENT,
ghid varchar(255) NOT NULL,
openid varchar(255) NOT NULL,
flag tinyint DEFAULT 0,
tm TIMESTAMP DEFAULT 0,
PRIMARY KEY (id),
UNIQUE(ghid, openid)
) DEFAULT CHARACTER SET=utf8;
''')
db.commit()
def get_current_state(self, ghid, openid):
state, = self.db.fetchone('SELECT STATE FROM weicbd_mp_wall_state WHERE ghid=%s AND openid=%s', (ghid, openid)) or ('out-wall',)
return state
def push_message(self, ghid, openid, body):
self.db.execute('INSERT INTO weicbd_mp_wall_message(ghid, openid, content) VALUES(%s, %s, %s)', (ghid, openid, body))
self.db.commit()
def set_state(self, ghid, openid, state):
self.db.execute('REPLACE INTO weicbd_mp_wall_state(ghid, openid, state) VALUES(%s, %s, %s)', (ghid, openid, state))
self.db.commit()
def get_all_wall_message(self, token):
return self.db.fetchall('SELECT msg.id, msg.openid, msg.content FROM weicbd_mp_wall_message msg, weicbd_mp_site mp WHERE msg.ghid=mp.ghid AND mp.token=%s ORDER BY ID ASC', (token,)) or []
def get_all_wall_message_after_id(self, token, mid):
return self.db.fetchall('SELECT msg.id, msg.openid, msg.content FROM weicbd_mp_wall_message msg, weicbd_mp_site mp WHERE msg.ghid=mp.ghid AND mp.token=%s AND msg.id>%s ORDER BY ID ASC', (token, mid)) or []
def set_spec_fan_join(self, ghid, openid):
from datetime import datetime
self.db.execute('INSERT IGNORE INTO weicbd_mp_spec_wall(ghid, openid, tm) VALUES(%s, %s, %s)', (ghid, openid, datetime.now()))
self.db.commit()
def get_spec_fan_state(self, ghid, openid):
f, = self.db.fetchone('SELECT flag FROM weicbd_mp_spec_wall WHERE ghid=%s AND openid=%s', (ghid, openid)) or (0,)
return f
def get_all_spec_fan(self, token):
return [x[0] for x in (self.db.fetchall('SELECT w.openid FROM weicbd_mp_spec_wall w, weicbd_mp_site m WHERE w.ghid=m.ghid AND m.token=%s ORDER BY w.id ASC', (token, )) or [])]
def clear_spec_fan(self, token):
ghid, = self.db.fetchone('SELECT ghid FROM weicbd_mp_site WHERE token=%s', (token,))
self.db.execute('DELETE FROM weicbd_mp_spec_wall WHERE ghid=%s', (ghid,))
self.db.commit()
def set_spec_wall_state(self, token, spec_list):
ghid, = self.db.fetchone('SELECT ghid FROM weicbd_mp_site WHERE token=%s', (token,))
self.db.execute('UPDATE weicbd_mp_spec_wall SET flag=0 WHERE ghid=%s', (ghid,))
self.db.executemany('UPDATE weicbd_mp_spec_wall SET flag=%s WHERE openid=%s', [(1, openid) for openid in spec_list])
self.db.commit()
class ProductDao(object):
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
# 创建表
def __create_table(self, db):
# 商品
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_shopping_product(
id bigint NOT NULL AUTO_INCREMENT,
name varchar(50) null,
price float null,
store int null,
description varchar(100) null,
thumb text null,
spec varchar(20) null,
summary varchar(50) null,
status varchar(50) null,
image text null,
priority int null,
taglist text null,
relid bigint null,
FOREIGN KEY (relid) REFERENCES weicbd_mp_member_rel(id),
PRIMARY KEY (id)
)DEFAULT CHARACTER SET=utf8;
''')
# 订单
db.execute('''
create table if not exists weicbd_shopping_indent(
id bigint not null auto_increment,
memberid bigint null,
consignee varchar(30) null,
shippingAddress varchar(120) null,
sendtime date null,
number int null,
meney float null,
bigentime date null,
status varchar(20) null,
info varchar(35) null,
FOREIGN KEY (memberid) REFERENCES weicbd_member(id),
primary key (id)
)DEFAULT CHARACTER SET=utf8;
''')
# 收藏夹
db.execute('''
create table if not exists weicbd_shopping_favorite(
id bigint not null auto_increment,
productid bigint null,
memberid bigint null,
FOREIGN KEY (productid) REFERENCES weicbd_shopping_product(id),
FOREIGN KEY (memberid) REFERENCES weicbd_member(id),
primary key (id)
)DEFAULT CHARACTER SET=utf8;
''')
# 购物车
db.execute('''
create table if not exists weicbd_shopping_cart(
id bigint not null auto_increment,
memberid bigint null,
productid bigint null,
num int null,
FOREIGN KEY (productid) REFERENCES weicbd_shopping_product(id),
FOREIGN KEY (memberid) REFERENCES weicbd_member(id),
primary key (id)
)DEFAULT CHARACTER SET=utf8;
''')
db.commit()
# 新增商品
def add_products(self, name, price, store, description, summary, thumb, spec, status, priority, relid, images, taglist):
#result = self.db.fetchone('select spec from weicbd_shopping_product where spec=%s', (spec,))
#if result is None:
res = self.db.execute('insert into weicbd_shopping_product ( name, price, store, description, summary, thumb, spec, status, priority,relid, image, taglist) value(%s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',(name, price, store, description, summary, thumb, spec, status, priority, relid, images, taglist))
self.db.commit()
return res
# 修改商品
def put_products(self, name, price, store, description, summary, thumb, spec, status, priority, images, id):
self.db.execute('update weicbd_shopping_product set name=%s, price=%s, store=%s, description=%s, summary=%s, thumb=%s, spec=%s, status=%s, priority=%s, image=%s where id=%s',( name, price, store, description, summary, thumb, spec, status, priority, images, id))
self.db.commit()
# 删除商品
def remove_product(self, id):
self.db.execute('delete from weicbd_shopping_product where id = %s',(id,))
self.db.commit()
return id
# 小列表
def all_products(self):
return self.db.fetchall('select id, name, price, store, thumb, summary, status, priority,taglist from weicbd_shopping_product')
# 商品明细
def find_products(self, id):
return self.db.fetchall('select id,name,price,store,description,thumb,spec,summary,status,image,priority,taglist from weicbd_shopping_product where a.id = %s',(id,))
# 按标签查找商品列表
def query_products(self, taglist, id):
if taglist:
substring = ' and '.join(['(count(/tags/tag[self:text()="%s"]) >=1)' % x for x in taglist])
return self.db.fetchall('SELECT id, name, price, store, description, summary, thumb, spec, taglist from weicbd_shopping_product where id in (select ft.id from (SELECT id, EXTRACTVALUE( taglist,\'%s\' ) as flag FROM weicbd_shopping_product) as ft where ft.flag=1) and id =%s'% (substring, id))
else:
return self.db.fetchall('select id,name,price,store,description,summary,thumb,spec,taglist from weicbd_shopping_product where id=%s',(id,))
class CircleDao(object):
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
def __create_table(self, db):
# 圈子
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_circle(
id bigint NOT NULL AUTO_INCREMENT,
memberid bigint NULL,
name varchar(50) NOT NULL,
depict varchar(80) NULL,
FOREIGN KEY (memberid) REFERENCES weicbd_member(id),
PRIMARY KEY (id)
) ''')
# 角色
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_role(
id bigint NOT NULL AUTO_INCREMENT,
name varchar(30) NOT NULL,
circleid bigint NULL,
FOREIGN KEY (circleid) REFERENCES weicbd_mp_circle(id),
PRIMARY KEY (id)
)
''')
# 圈子成员
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_mp_circle_member(
id bigint NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
roleid bigint NULL,
circleid bigint NULL,
memberid bigint NULL,
FOREIGN KEY (circleid) REFERENCES weicbd_mp_circle(id),
FOREIGN KEY (roleid) REFERENCES weicbd_mp_role(id),
FOREIGN KEY (memberid) REFERENCES weicbd_member(id),
PRIMARY KEY (id)
)
''')
db.commit()
# 创建圈子
def create_circle(self, name, member, depict):
# 找到当前帐号的id
memberid, = self.db.fetchone('select id from weicbd_member where mobile=%s',(member,)) or (None, )
membername, = self.db.fetchone('select name from weicbd_member where mobile=%s',(member,)) or (None, )
if memberid is not None:
# 新建圈子成功返回圈子ID
circleid = self.db.execute('insert into weicbd_mp_circle (name,memberid,depict) values(%s,%s,%s)', (name, memberid, depict)) or (None, )
# 新建一个圈主的角色
roleid = self.db.execute('insert into weicbd_mp_role (name,circleid) values("圈主",%s)',(circleid, )) or (None, )
# 新建一个成员变量
self.db.execute('insert into weicbd_mp_circle_member (name, roleid, circleid, memberid) values(%s, %s, %s, %s)', (membername, roleid, circleid, memberid)) or (None,)
self.db.commit()
return circleid
# 添加圈子
def add_circle(self, circleid, member):
# 找到当前帐号的ID
memberid, = self.db.fetchone('select id from weicbd_member where mobile=%s',(member,)) or (None, )
# 找到当前账户的名称
mname, = self.db.fetchone('select name from weicbd_member where mobile=%s',(member,)) or (None, )
if (circleid is not None) and (memberid is not None):
res = self.db.execute('insert into weicbd_mp_circle_member (name,circleid,memberid) values(%s,%s,%s) ',(mname,circleid,memberid))
self.db.commit()
return res
# 退出圈子
def exit_circle(self, mobile, cid):
memberid, = self.db.fetchone('SELECT a.id FROM weicbd_mp_circle_member a, weicbd_mp_circle b, weicbd_member c WHERE a.circleid = b.id AND a.memberid = c.id AND a.circleid =%s AND c.mobile =%s ',(cid,mobile)) or (None, )
if memberid is not None:
self.db.execute('delete from weicbd_mp_circle_member where id=%s', (memberid,)) or (None, )
self.db.commit()
# 踢出成员
def exit_member(self, mobile, memberid):
role, = self.db.fetchone('SELECT a.name FROM weicbd_mp_role a, weicbd_mp_circle_member b, weicbd_member c WHERE a.id = b.roleid AND b.memberid = c.id AND c.mobile = %s', (mobile,)) or (None, )
if role == '圈主':
self.db.execute('delete from weicbd_mp_circle_member where id=%s', (memberid,))
self.db.commit()
# 创建角色
def create_role(self, mobile, circleid, rname):
role, = self.db.fetchone('SELECT a.name FROM weicbd_mp_role a, weicbd_mp_circle_member b, weicbd_member c WHERE a.id = b.roleid AND b.memberid = c.id AND c.mobile = %s', (mobile,)) or (None, )
if role == '圈主':
rid = self.db.execute('insert into weicbd_mp_role (name,circleid) values(%s,%s)',(rname,circleid)) or (None, )
self.db.commit()
return rid
# 修改角色列表
def update_role(self, mobile, name, roleid, cid):
# 找到当前用户是否为圈主
role, = self.db.fetchone('SELECT a.name FROM weicbd_mp_role a, weicbd_mp_circle_member b, weicbd_member c WHERE a.id = b.roleid AND b.memberid = c.id AND c.mobile = %s', (mobile,)) or (None, )
if role == '圈主':
self.db.execute('update weicbd_mp_role set name=%s where id = %s and circleid=%s ', (name, roleid, cid))
self.db.commit()
return roleid
# 修改成员角色
def update_member_role(self, mobile, memberid, roleid, cid):
# 找到当前用户是否为圈主
role, = self.db.fetchone('SELECT a.name FROM weicbd_mp_role a, weicbd_mp_circle_member b, weicbd_member c WHERE a.id = b.roleid AND b.memberid = c.id AND c.mobile = %s',(mobile,)) or (None, )
if (role == '圈主') and (roleid is not None):
self.db.execute('update weicbd_mp_circle_member set roleid=%s where id=%s and circleid=%s',(roleid, memberid, cid)) or (None, )
self.db.commit()
# 删除角色
def reomeve_role(self, roleid):
# 找到当前用户是否为圈主
role, = self.db.fetchone('select a.roleid from weicbd_mp_circle_member a, weicbd_mp_role b where a.roleid=b.id and b.id= %s',(roleid,)) or (None, )
if role is None:
self.db.execute('delete from weicbd_mp_role where id = %s',(roleid, )) or (None, )
self.db.commit()
# 圈子成员列表
def query_circles_list(self, mobile, circleid):
#当前帐号内是否存在该用户
memberid, = self.db.fetchone('select a.memberid from weicbd_mp_circle_member a, weicbd_member b, weicbd_mp_circle c where a.memberid=b.id and a.circleid=c.id and c.id=%s and b.mobile=%s',(circleid, mobile)) or (None, )
if memberid is not None:
# 查出当前圈子下的所有成员
return self.db.fetchall('select a.id, a.name, b.name,b.id,c.name,c.id from weicbd_mp_circle_member a, weicbd_mp_role b, weicbd_mp_circle c where a.roleid=b.id and a.circleid=c.id and c.id=%s',(circleid,)) or (None, )
# 角色列表
def query_role_list(self, circleid):
return self.db.fetchall('select a.id,a.name,b.name,b.id from weicbd_mp_role a, weicbd_mp_circle b where a.circleid=b.id and b.id=%s',(circleid,)) or (None, )
# 搜索全部圈子
def query_all_circle(self):
return self.db.fetchall('select id,name, depict from weicbd_mp_circle')
# 按名称搜索圈子
def query_circle_name(self, circlename):
return self.db.fetchall('select id, name, depict from weicbd_mp_circle where name = %s', (circlename,)) or (None, )
# 找出当前帐号所在的圈子
def query_member_circle(self, mobile):
return self.db.fetchall('select a.id, a.name, a.depict from weicbd_mp_circle a,weicbd_mp_circle_member b,weicbd_mp_member c where a.id=b.circleid and b.memberid=c.id and c.mobile=%s',(mobile,)) or []
# 修改圈子
def update_circle(self, id, name, depict, memberid):
self.db.execute('update weicbd_mp_circle set name, depit, memberid where id=%s',(name, depict, memberid, id)) or (None, )
self.db.commit()
return id
def __init__(self):
from db import Mysqldb
self.db = Mysqldb()
self.__create_table(self.db)
class ProductDao(object):
def __init__(self):
self.db = Mysqldb()
self.__create_table(self.db)
# 创建表
def __create_table(self, db):
# 商品
db.execute('''
CREATE TABLE IF NOT EXISTS weicbd_shopping_product(
id bigint NOT NULL AUTO_INCREMENT,
name varchar(50) null,
price float null,
store int null,
description varchar(100) null,
thumb text null,
spec varchar(20) null,
summary varchar(50) null,
status varchar(50) null,
image text null,
priority int null,
taglist text null,
relid bigint null,
FOREIGN KEY (relid) REFERENCES weicbd_mp_member_rel(id),
PRIMARY KEY (id)
)DEFAULT CHARACTER SET=utf8;
''')
# 订单
db.execute('''
create table if not exists weicbd_shopping_indent(
id bigint not null auto_increment,
memberid bigint null,
consignee varchar(30) null,
shippingAddress varchar(120) null,
sendtime date null,
number int null,
meney float null,
bigentime date null,
status varchar(20) null,
info varchar(35) null,
FOREIGN KEY (memberid) REFERENCES weicbd_member(id),
primary key (id)
)DEFAULT CHARACTER SET=utf8;
''')
# 收藏夹
db.execute('''
create table if not exists weicbd_shopping_favorite(
id bigint not null auto_increment,
productid bigint null,
memberid bigint null,
FOREIGN KEY (productid) REFERENCES weicbd_shopping_product(id),
FOREIGN KEY (memberid) REFERENCES weicbd_member(id),
primary key (id)
)DEFAULT CHARACTER SET=utf8;
''')
# 购物车
db.execute('''
create table if not exists weicbd_shopping_cart(
id bigint not null auto_increment,
memberid bigint null,
productid bigint null,
num int null,
FOREIGN KEY (productid) REFERENCES weicbd_shopping_product(id),
FOREIGN KEY (memberid) REFERENCES weicbd_member(id),
primary key (id)
)DEFAULT CHARACTER SET=utf8;
''')
db.commit()
# 新增商品
def add_products(self, name, price, store, description, summary, thumb,
spec, status, priority, relid, images, taglist):
#result = self.db.fetchone('select spec from weicbd_shopping_product where spec=%s', (spec,))
#if result is None:
res = self.db.execute(
'insert into weicbd_shopping_product ( name, price, store, description, summary, thumb, spec, status, priority,relid, image, taglist) value(%s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
(name, price, store, description, summary, thumb, spec, status,
priority, relid, images, taglist))
self.db.commit()
return res
# 修改商品
def put_products(self, name, price, store, description, summary, thumb,
spec, status, priority, images, id):
self.db.execute(
'update weicbd_shopping_product set name=%s, price=%s, store=%s, description=%s, summary=%s, thumb=%s, spec=%s, status=%s, priority=%s, image=%s where id=%s',
(name, price, store, description, summary, thumb, spec, status,
priority, images, id))
self.db.commit()
# 删除商品
def remove_product(self, id):
self.db.execute('delete from weicbd_shopping_product where id = %s',
(id, ))
self.db.commit()
return id
# 小列表
def all_products(self):
return self.db.fetchall(
'select id, name, price, store, thumb, summary, status, priority,taglist from weicbd_shopping_product'
)
# 商品明细
def find_products(self, id):
return self.db.fetchall(
'select id,name,price,store,description,thumb,spec,summary,status,image,priority,taglist from weicbd_shopping_product where a.id = %s',
(id, ))
# 按标签查找商品列表
def query_products(self, taglist, id):
if taglist:
substring = ' and '.join([
'(count(/tags/tag[self:text()="%s"]) >=1)' % x for x in taglist
])
return self.db.fetchall(
'SELECT id, name, price, store, description, summary, thumb, spec, taglist from weicbd_shopping_product where id in (select ft.id from (SELECT id, EXTRACTVALUE( taglist,\'%s\' ) as flag FROM weicbd_shopping_product) as ft where ft.flag=1) and id =%s'
% (substring, id))
else:
return self.db.fetchall(
'select id,name,price,store,description,summary,thumb,spec,taglist from weicbd_shopping_product where id=%s',
(id, ))