def update(VenueId,Name,PhoneNo,Address,Email,Priority,updater):
session = database.get_session()
session.query(Venue).filter(Venue.VenueId == VenueId).update({'Name':Name.strip(),'PhoneNo':PhoneNo,
'Address':Address,'Email':Email,'Priority':Priority,
'Modifier':updater,'LastUpdateDate':datetime.now()})
session.commit()
session.close()
def employeeAdd(name,
phone,
webChat=None,
birthday=None,
address=None,
password="******",
**args):
"""
Add Employee, the default password is 'changeme'. At the first login, we need to force change password
"""
session = get_session()
employee = Employee()
employee.name = name
employee.phone = phone
employee.password = password
if not webChat is None:
employee.wchat = webChat
if not birthday is None:
employee.birthday = birthday
if not address is None:
employee.address = address
for item in args:
if hasattr(Employee, item):
setattr(employee, item, args[item])
else:
raise "Employee Didn't have this property: " + item
session.add(employee)
session.commit()
session.close()
def delete_post(post_id):
session = database.get_session()
post = Post()
post_id = Post.query.filter(Post.id == post_id).one()
session.delete(post_id)
session.commit()
def exist_category(name):
session = database.get_session()
c = session.query(TEST_TASK).filter(TEST_TASK.TaskCaseName == name).count()
session.close()
return c > 0
def memberAdd(name,phone,webChat=None,birthday=None,address=None,expenses_not_quota_password="******",**args):
"""
Add Member, the default password is 'changeme'. At the first login, we need to force change password
"""
session=get_session()
member=Member()
member.name=name
member.phone=phone
member.expenses_not_quota_password=expenses_not_quota_password
if not webChat is None:
member.wchat=webChat
if not birthday is None:
member.birthday=birthday
if not address is None:
member.address=address
for item in args:
if hasattr(Member, item):
setattr(member,item,args[item])
else:
raise Exception("Member Didn't have this property: " + item)
session.add(member)
session.commit()
session.close()
def employeeAdd(name,phone,webChat=None,birthday=None,address=None,password="******",**args):
"""
Add Employee, the default password is 'changeme'. At the first login, we need to force change password
"""
session=get_session()
employee=Employee()
employee.name=name
employee.phone=phone
employee.password=password
if not webChat is None:
employee.wchat=webChat
if not birthday is None:
employee.birthday=birthday
if not address is None:
employee.address=address
for item in args:
if hasattr(Employee, item):
setattr(employee,item,args[item])
else:
raise "Employee Didn't have this property: " + item
session.add(employee)
session.commit()
session.close()
def memberAdd(name,
phone,
webChat=None,
birthday=None,
address=None,
expenses_not_quota_password="******",
**args):
"""
Add Member, the default password is 'changeme'. At the first login, we need to force change password
"""
session = get_session()
member = Member()
member.name = name
member.phone = phone
member.expenses_not_quota_password = expenses_not_quota_password
if not webChat is None:
member.wchat = webChat
if not birthday is None:
member.birthday = birthday
if not address is None:
member.address = address
for item in args:
if hasattr(Member, item):
setattr(member, item, args[item])
else:
raise Exception("Member Didn't have this property: " + item)
session.add(member)
session.commit()
session.close()
def update_yard(YardId,YardName,Galleryful,Usage,updater):
session = database.get_session()
session.query(Yard).filter(Yard.YardId == YardId).update({'YardName':YardName.strip(),'Galleryful':Galleryful,
'Usage':Usage.strip(),'Modifier':updater,
'LastUpdateDate':datetime.now()})
session.commit()
session.close()
def disable_user(user_id):
session = database.get_session()
user = session.query(UserProfile).filter(UserProfile.UserId == user_id).update({'Status':UserStatus.Disabled})
session.commit()
session.close()
def update_projectProfile(projectkey,dockerprofile):
session = database.get_session()
docker = session.query(projectProfile).filter(projectProfile.ProjectKey == projectkey).one()
docker.DockerProfile = dockerprofile
docker.LastUpdateDate = datetime.now()
session.commit()
session.close()
def update_plan(ptype,CourseId,Yardid,Coach_id,BeginDate,EndDate,WorkTime,OffTime,WeekSchedule_list_or_str,updater):
session = database.get_session()
updatedict = {'BeginDate':BeginDate,'EndDate':EndDate,'Modifier':updater,'LastUpdateDate':datetime.now()}
if CourseId:
c = Course()
c.CourseId = CourseId
updatedict.update({'Course':[c]})
if Yardid:
y = Yard()
y.Yardid = Yardid
updatedict.update({'Yard':[y]})
if Coach_id:
m = Member()
m.UserId = Coach_id
updatedict.update({'Coach':[m]})
if WorkTime:
updatedict.update({'WorkTime':WorkTime})
if OffTime:
updatedict.update({'OffTime':OffTime})
if WeekSchedule_list_or_str:
if ptype=='a':
s = ''
for e in WeekSchedule_list_or_str:
s+= '&'.join(e)+'^'
updatedict.update({'WeekSchedule':s.strip('^')})
elif ptype=='b':
updatedict.update({'WeekSchedule':WeekSchedule_list_or_str})
else:
pass
session.query(Course).filter(Course.CourseId == CourseId).update(updatedict)
session.commit()
session.close()
def create_plan(PlanType,CourseId,Yardid,BeginDate,EndDate,WorkTime,OffTime,WeekSchedule_list_or_str,creator):
session = database.get_session()
p = Plan()
p.PType = PlanType.strip()
c = Course()
c.CourseId = CourseId
p.Course.append(c)
y = Yard()
y.Yardid = Yardid
p.Yard.append(y)
p.BeginDate = BeginDate
p.EndDate = EndDate
if PlanType.strip() == 'a': #团课
s = ''
for e in WeekSchedule_list_or_str:
s+= '&'.join(e)+'^'
p.WeekSchedule = s.strip('^')
elif PlanType.strip() == 'b': #私教
p.WorkTime = WorkTime
p.OffTime = OffTime
p.WeekSchedule = WeekSchedule_list_or_str
else:
pass
p.Creator = creator
p.CreateDate = datetime.now()
session.add(p)
session.commit()
session.close()
def disable_user(user_id):
session = database.get_session()
user = session.query(UserProfile).filter(UserProfile.UserId == user_id).update({'Status':UserStatus.Disabled})
session.commit()
session.close()
def update_yards(VenueId,datalist,updater):
session = database.get_session()
for e in datalist:
session.query(Yard).filter(Yard.YardId == e[0]).update({'YardName':e[1].strip(),'Galleryful':int(e[2]),
'Usage':e[3].strip(),'Modifier':updater,
'LastUpdateDate':datetime.now()})
session.commit()
session.close()
def query_project():
session = database.get_session()
project_list = session.query(project).all()
session.commit()
session.close()
return project_list
def update(id,taskcasename,jmxname,threads,rampup,threadloop,scale):
# taskcasename ='cc'
session = database.get_session()
task = session.query(TEST_TASK).filter(TEST_TASK.TestTaskId == id).update({'TaskCaseName':taskcasename,'JmxName':jmxname,'Threads':threads,'RampUp':rampup,'ThreadLoop':threadloop,'SCALE':scale})
session.commit()
session.close()
def employeeQueryByRoleName(key,offset=0,limit=10):
seesion=get_session()
roleID=roleservice.getIDByName(key)
querys=seesion.query(Employee).filter(Employee.role==roleID)
querys=querys.offset(offset)
querys=querys.limit(limit)
return querys.all()
def employeeQueryByRoleName(key, offset=0, limit=10):
seesion = get_session()
roleID = roleservice.getIDByName(key)
querys = seesion.query(Employee).filter(Employee.role == roleID)
querys = querys.offset(offset)
querys = querys.limit(limit)
return querys.all()
def roleGetByID(roleID):
"""
Query member by name,phone,wehchat. it could be used for page when using offset,limit. Default is return first 10
"""
seesion = database.get_session()
querys = seesion.query(Role).filter(Role.id == roleID)
role = querys.first()
seesion.close()
return role
def employeeGetByName(employName):
"""
Query employee by name,phone,wehchat. it could be used for page when using offset,limit. Default is return first 10
"""
seesion=get_session()
querys=seesion.query(Employee).filter(Employee.name==employName)
employee=querys.first()
seesion.close()
return employee
def assign_admin(user_id):
session = database.get_session()
user = session.query(UserProfile).filter(UserProfile.UserId == user_id).one()
user.IsAdmin = not user.IsAdmin
session.commit()
session.close()
def roleGetByID(roleID):
"""
Query member by name,phone,wehchat. it could be used for page when using offset,limit. Default is return first 10
"""
seesion=database.get_session()
querys=seesion.query(Role).filter(Role.id==roleID)
role=querys.first()
seesion.close()
return role
def delete_project(project_id):
# project_id = int(project_id)
session = database.get_session()
dp = session.query(project).filter(project.ProjectId == project_id).one()
projectkey = dp.ProjectKey
session.query(projectProfile).filter(projectProfile.ProjectKey == projectkey).delete()
session.query(project).filter(project.ProjectId == project_id).delete()
session.commit()
session.close()
def employeeFuzzyQuery(key,offset=0,limit=10):
seesion=get_session()
querys=seesion.query(Employee)
querys=querys.filter(or_(Employee.name.like("%"+key+"%"),Employee.phone.like("%"+key+"%"),Employee.wchat.like("%"+key+"%")))
querys=querys.offset(offset)
querys=querys.limit(limit)
return querys.all()
def employeeGetByName(employName):
"""
Query employee by name,phone,wehchat. it could be used for page when using offset,limit. Default is return first 10
"""
seesion = get_session()
querys = seesion.query(Employee).filter(Employee.name == employName)
employee = querys.first()
seesion.close()
return employee
def assign_admin(user_id):
session = database.get_session()
user = session.query(UserProfile).filter(UserProfile.UserId == user_id).one()
user.IsAdmin = not user.IsAdmin
session.commit()
session.close()
def delete(VenueId):
session = database.get_session()
#先删除所属场地
session.query(Yard).filter(Yard.Venue == VenueId).delete(synchronize_session=False)
session.commit() # or session.expire_all()
#再删除场馆
session.query(Venue).filter(Venue.VenueId == VenueId).delete()
session.commit()
session.close()
def query_order(page_no,page_size,order_by,current_user,PayMode=0,OrderType=0,PayStatus=0):
session = database.get_session()
subdata = session.query(Order).filter(Order.Operator == current_user).filter(Order.PayMode == PayMode).filter(Order.OrderType == OrderType).filter(Order.PayStatus == PayStatus).all()
subdata_list = []
for i in subdata:
subdata_list.append(i[0])
(data,row_count,page_count,page_no) = database.query_more(subdata,order_by,page_no,page_size)
session.close()
return (data,subdata_list,row_count,page_count,page_no)
def query(page_no,page_size,order_by,current_user,CType='a'):
session = database.get_session()
subdata = session.query(Course).filter(Course.Creator == current_user).filter(Course.CType == CType)
subdata_list = []
for i in subdata:
subdata_list.append(i.CourseId)
(data,row_count,page_count,page_no) = database.query_more(subdata,order_by,page_no,page_size)
session.close()
return (data,subdata_list,row_count,page_count,page_no)
def query(page_no,page_size,order_by,current_user):
session = database.get_session()
subdata = session.query(Venue).filter(Venue.Creator == current_user).all()
subdata_list = []
for i in subdata:
subdata_list.append(i[0])
(data,row_count,page_count,page_no) = database.query_more(subdata,order_by,page_no,page_size)
session.close()
return (data,subdata_list,row_count,page_count,page_no)
def memberGetByID(employId):
"""
Query member by name,phone,wehchat. it could be used for page when using offset,limit. Default is return first 10
"""
seesion = get_session()
querys = seesion.query(Member).filter(Member.id == employId)
member = querys.first()
seesion.close()
return member
def memberFuzzyQuery(key,offset=0,limit=10):
seesion=get_session()
querys=seesion.query(Member)
querys=querys.filter(or_(Member.name.like("%"+key+"%"),Member.phone.like("%"+key+"%"),Member.wchat.like("%"+key+"%")))
querys=querys.offset(offset)
querys=querys.limit(limit)
return querys.all()
def delete_course(CourseId):
session = database.get_session()
#先删除课程安排信息
session.query(Plan).filter(Plan.Course == [CourseId]).delete(synchronize_session=False)
session.commit()
#再删除课程
session.query(Course).filter(Course.CourseId == CourseId).delete()
session.commit()
session.close()
def memberGetByID(employId):
"""
Query member by name,phone,wehchat. it could be used for page when using offset,limit. Default is return first 10
"""
seesion=get_session()
querys=seesion.query(Member).filter(Member.id==employId)
member=querys.first()
seesion.close()
return member
def delete(VenueId):
session = database.get_session()
#先删除所属场地
session.query(Yard).filter(Yard.Venue == VenueId).delete(
synchronize_session=False)
session.commit() # or session.expire_all()
#再删除场馆
session.query(Venue).filter(Venue.VenueId == VenueId).delete()
session.commit()
session.close()
def query(page_no, page_size, order_by, current_user):
session = database.get_session()
subdata = session.query(Venue).filter(Venue.Creator == current_user).all()
subdata_list = []
for i in subdata:
subdata_list.append(i[0])
(data, row_count, page_count,
page_no) = database.query_more(subdata, order_by, page_no, page_size)
session.close()
return (data, subdata_list, row_count, page_count, page_no)
def refund_order(OrderId):#退款 只有PayStatus=6的订单可退款
session = database.get_session()
order = session.query(Order).filter(Order.OrderId == OrderId).first()
if order.PayStatus!=6:
session.close()
return False
order.PayStatus = 5
session.commit()
session.close()
return True
def confirm_order(OrderId):#确认订单 PayStatus=1的订单为交易完成的订单 只有PayStatus=3即待确认的订单有次操作
session = database.get_session()
order = session.query(Order).filter(Order.OrderId == OrderId).first()
if order.PayStatus!=3:
session.close()
return False
order.PayStatus = 1
session.commit()
session.close()
return True
def pay_order(OrderId): #支付订单 只有 PayStatus为4的待支付订单才可以 “支付订单”
session = database.get_session()
order = session.query(Order).filter(Order.OrderId == OrderId).first()
if order.PayStatus != 4:
session.close()
return False
order.PayStatus = 3
session.commit()
session.close()
return True
def confirm_and_refund_order(OrderId):#确认并退款 只有PayStatus=7的订单可确认并退款
session = database.get_session()
order = session.query(Order).filter(Order.OrderId == OrderId).first()
if order.PayStatus!=7:
session.close()
return False
order.PayStatus = 5
session.commit()
session.close()
return True
def query_fitnessorder():
session = database.get_session()
fitnessorderlist = session.query(Order).all()
session.close()
fitnessorder_list = []
for i in fitnessorderlist:
Price =i.Price
Price = str(Price)
fitnessorder_list.append({'OrderId':i.OrderId,'OrderName':i.OrderName,'OrderType':i.OrderType,'Amount':i.Amount,'Price':Price,'BuyName':i.BuyName,'BuyerName':i.BuyerName,'Comment':i.Comment})
return fitnessorder_list
def refund_order(OrderId): #退款 只有PayStatus=6的订单可退款
session = database.get_session()
order = session.query(Order).filter(Order.OrderId == OrderId).first()
if order.PayStatus != 6:
session.close()
return False
order.PayStatus = 5
session.commit()
session.close()
return True
def confirm_and_refund_order(OrderId): #确认并退款 只有PayStatus=7的订单可确认并退款
session = database.get_session()
order = session.query(Order).filter(Order.OrderId == OrderId).first()
if order.PayStatus != 7:
session.close()
return False
order.PayStatus = 5
session.commit()
session.close()
return True
def pay_order(OrderId):#支付订单 只有 PayStatus为4的待支付订单才可以 “支付订单”
session = database.get_session()
order = session.query(Order).filter(Order.OrderId == OrderId).first()
if order.PayStatus!=4:
session.close()
return False
order.PayStatus = 3
session.commit()
session.close()
return True
def create_yard(Venue_id, YardName, Galleryful, Usage, creator):
session = database.get_session()
y = Yard()
y.Venue = int(Venue_id)
y.YardName = YardName.strip()
y.Galleryful = int(Galleryful)
y.Usage = Usage.strip()
y.creator = creator
session.add(y)
session.commit()
session.close()
def employeeFuzzyQuery(key, offset=0, limit=10):
seesion = get_session()
querys = seesion.query(Employee)
querys = querys.filter(
or_(Employee.name.like("%" + key + "%"),
Employee.phone.like("%" + key + "%"),
Employee.wchat.like("%" + key + "%")))
querys = querys.offset(offset)
querys = querys.limit(limit)
return querys.all()
def memberFuzzyQuery(key, offset=0, limit=10):
seesion = get_session()
querys = seesion.query(Member)
querys = querys.filter(
or_(Member.name.like("%" + key + "%"),
Member.phone.like("%" + key + "%"),
Member.wchat.like("%" + key + "%")))
querys = querys.offset(offset)
querys = querys.limit(limit)
return querys.all()
def query_role(name='', permission=-1):
session = database.get_session()
querys = session.query(Role)
if name != '':
querys = querys.filter(Role.name.like("%" + name + "%"))
if permission > 0:
querys = querys.filter(Role.permissions.op('&')(permission) > 0)
rolelist = querys.all()
session.close()
return rolelist
def confirm_order(
OrderId): #确认订单 PayStatus=1的订单为交易完成的订单 只有PayStatus=3即待确认的订单有次操作
session = database.get_session()
order = session.query(Order).filter(Order.OrderId == OrderId).first()
if order.PayStatus != 3:
session.close()
return False
order.PayStatus = 1
session.commit()
session.close()
return True
def create_yards(Venue_id, datalist, creator):
session = database.get_session()
y = Yard()
for e in datalist:
y.Venue = int(Venue_id)
y.YardName = e[0].strip()
y.Galleryful = int(e[1])
y.Usage = e[2].strip()
y.creator = creator
session.add(y)
session.commit()
session.close()
def employeeDeleteByID(employId):
"""
Delete employee by id
"""
seesion = get_session()
querys = seesion.query(Employee).filter(Employee.id == employId)
employee = querys.first()
seesion.delete(employee)
seesion.commit()
seesion.close()
return employee
def create(Name, PhoneNo, Address, Email, Priority, creator):
session = database.get_session()
v = Venue()
v.Name = Name.strip()
v.PhoneNo = PhoneNo.strip()
v.Address = Address.strip()
v.Email = Email.strip()
v.Priority = int(Priority)
v.creator = creator
session.add(v)
session.commit()
session.close()
def updateRole(roleid, rolename, taozhang, comments, permissions=0):
r = roleGetByID(roleid)
r.name = rolename.strip()
r.taozhang = taozhang
r.comments = comments
r.permissions = permissions
seesion = database.get_session()
seesion.add(r)
seesion.commit()
seesion.close()
def change_password(raw_password,new_password,user_id):
session = database.get_session()
user = session.query(UserProfile).filter(UserProfile.UserId == user_id).first()
if not user.Password == raw_password:
session.close()
return False
user.Password = new_password
session.commit()
session.close()
return True
def employeeModify(employId, **args):
session = get_session()
querys = session.query(Employee).filter(Employee.id == employId)
employee = querys.first()
session.add(employee)
for item in args:
if hasattr(employee, item):
setattr(employee, item, args[item])
else:
raise "Employee Didn't have this property: " + item
session.commit()
session.close()
def employeeQuery(name=None, phone=None, webChat=None, offset=0, limit=10):
"""
Query employee by name,phone,wehchat. it could be used for page when using offset,limit. Default is return first 10
"""
seesion = get_session()
querys = seesion.query(Employee)
if name is not None:
querys = querys.filter(Employee.name.like("%" + name + "%"))
if phone is not None:
querys = querys.filter(Employee.phone.like(phone + "%"))
if webChat is not None:
querys = querys.filter(Employee.wchat.like("%" + webChat + "%"))
querys = querys.offset(offset)
querys = querys.limit(limit)
return querys.all()
def cancel_order(OrderId): #取消订单 只有 PayStatus为1,3,4的订单（待支付,确认或已完成的）为可取消的订单
session = database.get_session()
order = session.query(Order).filter(Order.OrderId == OrderId).first()
if order.PayStatus not in (1, 3, 4):
session.close()
return False
if order.PayStatus == 1:
order.PayStatus = 6
elif order.PayStatus == 3:
order.PayStatus = 7
else:
order.PayStatus = 2
session.commit()
session.close()
return True
def createRole(roles):
"""
Create Role
"""
seesion = database.get_session()
for r in roles:
querys = seesion.query(Role)
role = querys.filter_by(name=r).first()
if role is None:
role = Role(name=r)
role.permissions = roles[r][0]
seesion.add(role)
seesion.commit()
seesion.close()
def memberModify(employId, **args):
session = get_session()
querys = session.query(Member).filter(Member.id == employId)
member = querys.first()
session.add(member)
for item in args:
if hasattr(member, item):
if item == 'birthday':
birthday = datetime.strptime(args[item], '%m/%d/%Y').date()
setattr(member, item, birthday)
else:
setattr(member, item, args[item])
session.commit()
session.close()
def update_yard(YardId, YardName, Galleryful, Usage, updater):
session = database.get_session()
session.query(Yard).filter(Yard.YardId == YardId).update({
'YardName':
YardName.strip(),
'Galleryful':
Galleryful,
'Usage':
Usage.strip(),
'Modifier':
updater,
'LastUpdateDate':
datetime.now()
})
session.commit()
session.close()
