bugs with virtual

grand_search_grid_NG.py

# -*- coding: utf-8 -*-

from gluon.sqlhtml import represent
from gluon import FieldMethod

from helpers import (action_button, expandable_form_fields, expandable_section, FORM_SEPARATOR, random_password, represent_boolean)
# from notemptymarker import mark_not_empty
from searching import search_form
# from solidform import SOLIDFORM
# from lib.admin import settings_value
# from lib.email import set_mailer
# from lib.user import email_activated_user
from lib.w2ui import make_orderby, save_export, serialized_lists



@auth.requires_permission('list', 'user')
def users():
    response.subtitle = T('user__list_form')
    response.files.append(URL('static', 'user/js/users.js'))
    response.files.append(URL('static', 'user/js/add_user.js'))

    cid = 'users'

    filters = [
        {'label': T('core__all'), 'data': {}},
        {'label': T('user__active'), 'selected': True, 'data': {'active': 'T'}},
        {'label': T('user__inactive'), 'data': {'active': 'F'}}
    ]

    form = search_form(
        cid,
        [db.auth_user.username, db.auth_user.type],
        [db.auth_user.email, db.auth_user.data_level],
        [Field('active', label=db.auth_user.active.label,
               requires=IS_IN_SET([('T', T('core__yes')), ('F', T('core__no'))], zero=T('core__all'))),
        db.branch_staff.branch_id,
        ],
        hidden={
            'users_autocomplete_username': URL('user', 'autocomplete_username.json'),
            'users_autocomplete_email': URL('user', 'autocomplete_email.json')
        },
        table_name='auth_user',
        filters=filters,
        formstyle='divs' if IS_MOBILE else None,
        _class='mobile_solidform' if IS_MOBILE else None
    )

    w2grid_coumns = [            
                {'field': f, 'caption': db.auth_user[f].label, 'size': "100%", 'sortable': True, 'resizable': True}
                for f in "username email active type data_level".split()
            ]
    # print( "DBG w2grid_coumns:", json(w2grid_coumns) )

    # response.view = 'user/users.html'
    response.view = 'plugin_search_form/grid.html'
    return {'cid': cid, 'form': form, 'w2grid_coumns':w2grid_coumns}


@auth.requires_login()
def autocomplete_username():
    records = db(db.auth_user.username.contains(request.vars.term)).select(
        db.auth_user.username,
        groupby=db.auth_user.username,
        orderby=db.auth_user.username
    )
    result = [r.username for r in records]
    return json(result)


@auth.requires_login()
def autocomplete_email():
    records = db(db.auth_user.email.contains(request.vars.term)).select(
        db.auth_user.email,
        groupby=db.auth_user.email,
        orderby=db.auth_user.email
    )
    result = [r.email for r in records]
    return json(result)



def w2ui_colname( field ):
    """because w2ui interprets '.' more than needed"""
    return str(field).replace('.', ':')
    # return str(field)

def w2ui_colname_decode( field ):
    """because w2ui interprets '.' more than needed"""
    return str(field).replace(':', '.')
    
def unpack_w2ui_colname( name ):
    if ':' in name:
        table_name, field_name = name.split(':')
        return table_name, field_name 
     
# @auth.requires_signature()
def w2ui_grid(query=True, fields_4columns=None, fields_4virtual=None, left=None, join=None, groupby=None, having=None, represent4export=None, data_name=None):
    cid = request.vars.cid
    status = 'success'
    cmd = request.vars.cmd
    
    fields_4columns = fields_4columns or []
    fields_4virtual = fields_4virtual or [] 
    
    fields_4columns_selectable = [f for f in fields_4columns if not isinstance( f, FieldMethod )]
    selected_fields = ( fields_4columns_selectable + fields_4virtual ) or db[request.controller].ALL
    represent4export = represent4export or {} # mapping to functions
    data_name = data_name or request.controller
    

    if cmd in ('get-records', 'export-records'):
        if not auth.has_permission('list', data_name):
            return {'status': 'error', 'message': MSG_NO_PERMISSION}

        orderby = None
        limitby = None
        records = []

        # print('DBG serialized_lists', request.vars)
        extra = serialized_lists(request.vars)  # sort, search
        # print("DBG extra", extra)
    
        if 'sort' in extra:
            # def split_colname_4fields_mapping(x):
                # try:
                    # table_name, field_name  = unpack_w2ui_colname(x['field'])    
                    # #if table_name in db and field_name in db[table_name]:
                    # return db[table_name][field_name]
                # except Exception as e:
                    # print("DBG split_colname_4fields_mapping", e)
                    # return None
                
            # fields_mapping = { x['field']:split_colname_4fields_mapping(x)  for x in extra['sort'] if split_colname_4fields_mapping(x) }
            fields_mapping = { x['field']:w2ui_colname_decode(x['field'])  for x in extra['sort'] }
            
            # orderby = make_orderby(db, extra['sort'], table_name=table_name)
            orderby = make_orderby(db, extra['sort'], fields_mapping=fields_mapping)

        
        if cmd == 'get-records':
            offset = int(request.vars.offset)
            limit = int(request.vars.limit)
            limitby = (offset, offset + limit)

        ###############   SELECT  #################### 
        # sql = db(query)._select(
            # TOTAL_ROWS, *selected_fields, 

            # orderby=orderby,
            # limitby=limitby,
            # #not mandatory
            # left=left,
            # join=join,
            # groupby=groupby,
            # having=having
        # )
        
        # print "DBG sql", sql
        
        # from gluon.storage import Storage
        # rows = db.executesql( sql )
        # rows = [ Storage({f:r[i] for i, f in enumerate(selected_fields) })   for r in rows ]
        rows = db(query).select(
            TOTAL_ROWS, *selected_fields, 

            orderby=orderby,
            limitby=limitby,
            #not mandatory
            left=left,
            join=join,
            groupby=groupby,
            having=having
        )
        

        # print "DBG rows", rows
        
        if cmd == 'export-records':
            for field in fields_4columns:
                if w2ui_colname(field) in represent4export:
                    field.represent = represent4export[ w2ui_colname(field) ]
            # was sth like:    
            # db.auth_user.active.represent = lambda value: represent_boolean(value, html=False)

        for r in rows:
            represented = {
                     w2ui_colname(f): represent(
                                                    f, 
                                                    r[f._tablename][f.name],
                                                    r[f._tablename] 
                                            )    for f in fields_4columns_selectable
            }
            
            represented.update ({
                    w2ui_colname(f): f
                        for f in fields_4columns if isinstance(f, FieldMethod)
            })
            
            represented['recid'] = db.auth_user.id

            # print ("dbg represented:", represented) 

            records.append( represented )
            
            # records.append({
                # 'recid': r.auth_user.id,
                # 'username': represent(db.auth_user.username, r.auth_user.username, r.auth_user),
                # 'email': represent(db.auth_user.email, r.auth_user.email, r.auth_user),
                # 'active': represent(db.auth_user.active, r.auth_user.active, r.auth_user),
                # 'type': represent(db.auth_user.type, r.auth_user.type, r.auth_user),
                # 'data_level': represent(db.auth_user.data_level, r.auth_user.data_level, r.auth_user)
            # })
        

        if cmd == 'get-records':
            total = rows[0][TOTAL_ROWS] if rows else 0
            return {'status': status, 'total': total, 'records': records}

        elif cmd == 'export-records':
            labels = {
                w2ui_colname(f): f.label  for f in fields_4columns
            }
            columns = request.vars.getlist('columns[]')
            data = [[labels[c] for c in columns]]
            for r in records:
                data.append([r[c] for c in columns])

            save_export(cid, data)
            return {'status': status}

    elif cmd == 'delete-records':
        if not auth.has_permission('delete', data_name):
            return {'status': 'error', 'message': MSG_NO_PERMISSION}

        selected = request.vars.getlist('selected[]')
        try:
            for s in selected:
                del db.auth_user[s]
        except:
            db.rollback()
            return {'status': 'error', 'message': MSG_ACTION_FAILED}
        return {'status': status}

    return {'status': status}

@auth.requires_permission('list', 'user')
def test_registry_NG_users():  # TODO
    from plugin_search_form.search_form import SearchField, SearchForm
    from plugin_joins_builder.joins_builder import build_joins_chain  # uses another grand plugin
    
    
    cid = request.function 
    data_name = 'user' # for checking permissions, etc..

    def my_grand_search_form(*fields, **kwargs):
        from applications.app.modules.searching import search_form as grand_search_form
        return grand_search_form(cid, *fields, **kwargs)
            

    search = SearchForm(  # jurgio plugin'as
        [ SearchField( db.auth_user.email ), SearchField( db.auth_user.username ) ],
        formstyle='table3cols',
        form_factory = my_grand_search_form,
        
        # w2ui
        _name = '{0}__form'.format(cid),
        _action = 'javascript:w2ui__grid("{0}");'.format(cid)

    )
    
    db.auth_user.virtual_color = Field.Virtual('bla', lambda row:   "nice" + row.color )
    db.auth_user.virtual_color.label = "Virtual"
    db.auth_user.virtual_color.represent = lambda row:   "nice" + row.color 
    
    fields_4virtual = [    db.auth_user.color  ]


    fields_4columns=[   
                        db.auth_user.id,
                        db.auth_user.username,
                        db.auth_user.email ,
                        db.auth_user.active,
                        db.auth_user.type  ,
                        db.auth_user.data_level ,
                        db.auth_user.virtual_color
                    ] 
    
                    
    """
    select_fields | select_expr
    show_fields | show_expr
    ghost_fields | ghost_expr
    """
    
                 
    if request.vars._grid == 'True':  # GRID
        grid_kwargs = { key: search.get(key) for key in 'left join groupby having'.split() }
        represent4export = { w2ui_colname(db.auth_user.active): lambda value: represent_boolean(value, html=False) }
        return w2ui_grid(
                        search.query,   
                        fields_4columns=fields_4columns, fields_4virtual=fields_4virtual, 
                        represent4export=represent4export, data_name=data_name, **grid_kwargs 
                        )
          
    else:                
        response.view = 'plugin_search_form/w2ui_grid.html'

        context = dict(        
            cid = cid,
            form = search.form,
            w2grid_columns =  [            
                 {'field': w2ui_colname(f), 'caption': f.label, 'size': "100%", 'sortable': True, 'resizable': True}
                 for f in fields_4columns
             ],
            grid_function = request.function,       # or 'users_grid'     
            data_name = data_name , # request.controller could be default       
            w2grid_sort = [  {'field': w2ui_colname(db.auth_user.username), 'direction': "asc"} ]
            # ,dbg = response.toolbar()
        )
        return context