from sqlalchemy import (
    Column,
    Index,
    Integer,
    Text,
    String,
    Unicode,
    ForeignKey,
    Table,
    Date,
    )
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
    backref,
    relationship,
    )
from zope.sqlalchemy import ZopeTransactionExtension
DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()
class Users(Base):
    __tablename__ = 'users'
    __table_args__ = {'mysql_engine':'InnoDB'}
    
    id = Column(Integer, primary_key=True)
    username = Column(Unicode(32), unique=True)
    email    = Column(Unicode(32), unique=True)
    password = Column(Unicode(64))
    blocked = Column(Integer)
    
    roles = association_proxy('roles_users', 'roles')
    
    profiles = relationship("Profiles", uselist=False)
    
    def __init__(self, username, email, password, blocked, roles=None):
        self.username = username
        self.password = password
        self.email    = email
        self.blocked  = blocked
        if roles:
            self.roles  = roles
        
    def check_password(self, passwd):
        return self.password == passwd
def get_user(username):
    user = DBSession.query(Users).filter(Users.username == username).all()
    if user:
        return user[0]
    else:
        return None
    
class Roles(Base):
    __tablename__ = 'roles'
    __table_args__ = {'mysql_engine':'InnoDB'}
    
    id          = Column(Integer, primary_key=True)
    name        = Column(Unicode(32))
    rolesgroup  = Column(Unicode(32))
    description = Column(Unicode(255))
    
    users = association_proxy('roles_users', 'users')
    
    def __init__(self, name):
        self.name = name
        #self.description = description
        
class RolesUsers(Base):
    __tablename__ = 'roles_users'
    __table_args__ = {'mysql_engine':'InnoDB'}
    
    user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
    role_id = Column(Integer, ForeignKey('roles.id'), primary_key=True)
    
    users = relationship('Users', backref=backref("roles_users"))
    roles = relationship('Roles', backref=backref("roles_users"))
    
    def __init__(self, user_id, role_id):
        self.user_id = user_id
        self.role_id = role_id
class Profiles(Base):
    __tablename__ = 'profiles'
    __table_args__ = {'mysql_engine':'InnoDB', 'mysql_collate': 'utf8_general_ci'}
    
    id        = Column(Integer, primary_key=True)
    userid    = Column(Integer, ForeignKey('users.id'))
    fullname  = Column(Unicode(64))
    register  = Column(Date)
    lastlogin = Column(Date)
    expired   = Column(Date)
    
    def __init__(self, fullname, register, lastlogin, expired):
        self.fullname  = fullname
        self.register  = register
        self.lastlogin = lastlogin
        self.expired   = expired
    

from pyramid.view import view_config, view_defaults
from .common import *
from sqlalchemy import desc, asc
import transaction
from testproject.models.users import (
    Users,
    Roles,
    RolesUsers,
    DBSession,
    )
import json
@view_config(route_name='managerparam4', xhr=True, renderer='string', match_param=('p1=options', 'p2=control', 'p3=users', 'p4=updateitem'))
def updateitem(request):
    post = request.POST.mixed()
    roles = json.loads(post['roles'])
    #Добавляем запись
    obj = DBSession.query(Users).filter(Users.id == post['id']).one()
    obj.username = post['username']
    obj.email    = post['email']
    obj.profiles.fullname = post['fullname']
    if post['password'] != '':
        obj.password = post['password']
    obj.blocked  = 0
    DBSession.add(obj)
    #transaction.commit()
    
    #Удаляем роли
    DBSession.query(RolesUsers).filter(RolesUsers.user_id == post['id']).delete()
    #transaction.commit()
    
    #Добавляем роли
    for e in roles:
        ru = RolesUsers(post['id'], e)
        DBSession.add(ru)
    DBSession.flush()
    transaction.commit()
    
    result = 'this'
    return result

DBSession.add(obj)

DBSession.flush()

transaction.commit()