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 memberAdd(name,phone,webChat=None,birthday=None,address=None,expenses_not_quota_password="changeme",**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_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 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 exist_category(name):
    session = database.get_session()

    c = session.query(TEST_TASK).filter(TEST_TASK.TaskCaseName == name).count()

    session.close()
    return c > 0

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 employeeAdd(name,phone,webChat=None,birthday=None,address=None,password="changeme",**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 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(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 query_project():
    session = database.get_session()
    project_list = session.query(project).all()
    session.commit()
    session.close()
    return project_list  
      
      

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 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 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 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 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 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 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 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()