Replace .select() with ._select() and print/output the result so we can see
the exact SQL generated.
On Monday, September 19, 2016 at 11:53:49 AM UTC-4, Peter wrote:
>
> Thanks for your response Anthony!
>
> I have obviously misunderstood a reference I read somewhere saying that
> reference fields were given special attributes and gave an example
> something like db.fieldname.name I assumed the trailing .name came from
> the referenced table attributes.
>
> So I have modified the code ...
>
> def generate_inv_details():
>
> session.inv_details = []
> session.inv_total = 0
> today = datetime.date.today()
>
> query = db( (db.task.person.belongs( session.company_persons))
> & (db.task.start_time <= today )
> & (db.task.charge_to == 'COMPANY' )
> & (db.task.task_status == 'BILLABLE')
> & (db.person.id==db.task.person))
>
> rows=db(query).select( db.task.person,
> db.person.name,
> db.task.title,
> db.task.start_time,
> db.task.duration,
> db.task.task_type,
> db.task.task_status,
> db.task.charge_to,
> db.task.charge,
> db.task.payment_status )
>
> session.inv_details=rows
>
> for row in session.inv_details:
> session.inv_total += row.task.charge
>
>
> - added the join (I hope correctly) in the query
> - used full "tablename.fieldname" format to reference the
> person.name in the select statement
> - removed the newline slashes
>
>
>
>
> The relevant table excerpts...
>
>
>
> db.define_table('person',
>
> Field( 'name' , requires=IS_NOT_EMPTY() , comment="*"),
> Field( 'status' , requires=IS_IN_SET(PERSON_STATUS) , comment="*")
> ,
> Field( 'type' , requires=IS_IN_SET(PERSON_TYPES) , comment="*")
> ,
> Field( 'company' , 'reference company' , comment="*"),
> Field( 'role' , 'string' , comment='for CONTACTS not CLIENTS'),
> Field( 'address' , comment="Use ',' to seperate address lines"),
> Field( 'mobile_phone' , label='Mobile' , requires=IS_EMPTY_OR(is_phone),
> comment="Can use '-' to separate prefix for clarity" ),
> Field( 'office_phone' , label='Office' , requires=IS_EMPTY_OR(is_phone),
> comment="Can use '-' to separate prefix for clarity" ),
> Field( 'home_phone' , label='Home' , requires=IS_EMPTY_OR(is_phone),
> comment="Can use '-' to separate prefix for clarity" ),
> Field( 'email' , requires=IS_EMPTY_OR(IS_EMAIL()) ),
> Field( 'date_of_birth' , 'date' ),
> Field( 'referrer_ref' , 'string' , default=None),
> Field( 'notes' , 'text' ),
> Field( 'created_by' , db.auth_user , default=me , writable=False ,
> readable=False ),
> Field( 'created_on' , 'datetime' , default=request.now ,
> writable=False , readable=False ),
> format=' %(type) %(name)s Referrer Ref %(referrer_ref)s')
>
> db.define_table('task',
>
> Field( 'task_type' , requires=IS_IN_SET(TASK_TYPES) , comment="*" ),
> Field( 'task_status' , requires=IS_IN_SET(TASK_STATUS) , comment="*"),
> Field( 'start_time' , 'datetime' , default=request.now
> ,requires=IS_NOT_EMPTY() , comment="*"),
> Field( 'duration' , default='1Hr' ),
> Field( 'title' , default="Session ##" , requires=IS_NOT_EMPTY()
> , comment="*"),
> Field( 'person' , 'reference person' , comment="*"),
> Field( 'description' , 'text' ),
> Field( 'charge' , 'double' , default=0.0),
> Field( 'charge_to' , default='COMPANY' ,
> requires=IS_IN_SET(CHARGE_TO) , comment="* [COMPANY for Invoicing,
> CONTACT for Receipting]"),
> Field( 'payment_status' , default='BILLABLE' ,
> requires=IS_IN_SET(PAYMENT_STATUS) , comment="* [BILLABLE for Invoicing or
> Receipting]"),
> Field( 'invoice_number' , 'integer' , default=None , writable=False ),
> Field( 'invoice_date' , 'date' , default=None , writable=False ),
> Field( 'created_by' , db.auth_user , default=me , writable=False ,
> readable=False ),
> Field( 'created_on' , 'datetime' , default=request.now ,
> writable=False , readable=False ),
> format= '%(person)s %(start_time)s %(task_type)s %(task_status)s
> %(charge)s')
>
>
>
> with the modified code I am still getting the same error
>
> Traceback
>
> Traceback (most recent call last):
> File "/home/peter/web2py/gluon/restricted.py", line 227, in restricted
> exec ccode in environment
> File "/home/peter/web2py/applications/PAPAIM/controllers/default.py"
> <http://127.0.0.1:8000/admin/default/edit/PAPAIM/controllers/default.py>,
> line 566, in <module>
> File "/home/peter/web2py/gluon/globals.py", line 417, in <lambda>
> self._caller = lambda f: f()
> File "/home/peter/web2py/gluon/tools.py", line 4241, in f
> return action(*a, **b)
> File "/home/peter/web2py/applications/PAPAIM/controllers/default.py"
> <http://127.0.0.1:8000/admin/default/edit/PAPAIM/controllers/default.py>,
> line 347, in preview_invoice
> generate_inv_details()
> File "/home/peter/web2py/applications/PAPAIM/models/extras.py"
> <http://127.0.0.1:8000/admin/default/edit/PAPAIM/models/extras.py>, line 25,
> in generate_inv_details
> db.task.payment_status )
> File "/usr/local/lib/python2.7/dist-packages/pydal/objects.py", line 2020,
> in select
> return adapter.select(self.query, fields, attributes)
> File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/sqlite.py",
> line 123, in select
> return super(SQLiteAdapter, self).select(query, fields, attributes)
> File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line
> 1296, in select
> return self._select_aux(sql,fields,attributes)
> File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line
> 1253, in _select_aux
> self.execute(sql)
> File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line
> 1388, in execute
> return self.log_execute(*a, **b)
> File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line
> 1382, in log_execute
> ret = self.get_cursor().execute(command, *a[1:], **b)
> OperationalError: near "<": syntax error
>
>
>
>
>
>
>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
