Sum issues while computing the total of a table field

[Oasis Agano]

Greetings im trying to perform a sum query that calculates the total gross(float) so that i can pass it to the view but im getting the error described in the traceback;

i tried this https://groups.google.com/forum/#!searchin/web2py/sum/web2py/paz06IC3slo/13Lj3tErj8EJ but no clue

Traceback

Traceback (most recent call last):
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\restricted.py", line 227, in restricted
    exec ccode in environment
  File "D:/lab/PycharmProjects/Smartwork/web2py/applications/smartwork/controllers/default.py", line 1210, in <module>
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\globals.py", line 417, in <lambda>
    self._caller = lambda f: f()
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\tools.py", line 4241, in f
    return action(*a, **b)
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\tools.py", line 4241, in f
    return action(*a, **b)
  File "D:/lab/PycharmProjects/Smartwork/web2py/applications/smartwork/controllers/default.py", line 41, in dashboard_admin
    print rows.first()[db.payslip.gross.sum()]
IOError: [Errno 22] Invalid argument

CONTROLLER

@auth.requires_membership('Admin')
def dashboard_admin():

    rows=db(db.payslip.state == 'Confirmed').select(db.payslip.gross.sum())
    print rows.first()[db.payslip.gross.sum()]


    return dict(rows=rows)

MODELS
MARITAL_SET = [
    T('Single'),
    T('Married'),
    T('Divorced'),
    T('Widower')


]
TIME_SET = [
    T('Part time'),
    T('Full time'),


]

STATE_SET = [
    T('Trial'),
    T('Employed'),
    T('Fired'),
    T('Retired'),


]
#@auth.requires_login()

db.define_table('employee',
                Field('emp_photo', 'upload',label='Photo'),
                Field('first_name','string',label='First Name'),
                Field('last_name','string',label='Last Name'),
                Field('fullname','string',readable='False',compute=lambda r: r.first_name+' '+r.last_name),
                Field('email','string'),
                Field('phone','string'),
                Field('marital_status', requires=IS_IN_SET(MARITAL_SET, zero=T('--choose marital status--'))),
                Field('number_of_children', 'string'),
                Field('name_of_children','text'),
                Field('address','text'),
                Field('Manager', 'reference employee'),
                #Field('date','datetime'),
                Field('dob', 'datetime', label='Date of Birth'),
                Field('doj', 'datetime', label='Hired Date'),
                Field('ismanager', 'boolean', default=False,label='Is Department Manager'),
                Field('department', 'reference department',requires=IS_EMPTY_OR(IS_IN_DB(db, "department.id", '%(department_name)s'))),
                Field('user', 'reference auth_user',requires=IS_EMPTY_OR(IS_IN_DB(db, "auth_user.id", '%(fullname)s'))),
                Field('job', 'reference job_title'),
                Field('Other', 'text'),
                Field('state', requires=IS_IN_SET(STATE_SET, zero=T('--choose employment status--'))),
                auth.signature,
                format='%(fullname)s'

                )
db.define_table('job_title',
                Field('job_name', 'string', label='Job Title'),
                auth.signature,
                format='%(job_name)s'
                )
#db = DAL(lazy_tables=True)
db.define_table('department',
                Field('department_name', 'string', label='Department Name'),
                Field('parent_dept', 'reference department'),
                Field('Other', 'text'),
                format='%(department_name)s'
                )
#db.employee.department.requires = IS_IN_DB(db, db.department.id, '%(department_name)s')
 
CONTRACTTYPE_SET = [
    T('Employee'),
    T('Consultant'),
    T('Freelance'),
    T('Internship'),

]

SALARYSTRUCTURE_SET = [
    T('Less than 30000'),
    T('Between 30000 and 100000'),
    T('Great than 100000')

]

CONTRACTSTATE_SET = [
    T('Draft'),
    T('Running'),
    T('Expired/To Renew'),
    T('Closed'),
    T('Cancelled'),


]


db.define_table('contract',
                Field('employee', 'reference employee', required='true',
                      requires=IS_IN_DB(db, "employee.id", '%(fullname)s')),
                # Field('date','datetime'),
                Field('contract_name', 'string', label='Contract Title'),
                Field('contract_type', requires=IS_IN_SET(CONTRACTTYPE_SET, zero=T('--choose contract type--'))),
                Field('salary_structure', requires=IS_IN_SET(SALARYSTRUCTURE_SET, zero=T('--choose salary type--'))),
                Field('job', 'reference job_title'),
                Field('contract_scan', 'upload',label='Contract Upload'),
                Field('date_trial_start', 'datetime', label='Starting Trial Date'),
                Field('date_trial_end', 'datetime', label='End Trial Date'),
                Field('date_work_start', 'datetime', label='Starting Working Date'),
                Field('date_work_end', 'datetime', label='End of Contract'),
                Field('wage', 'float'),
                Field('rssb', 'boolean', default=True, label='Pay RSSB'),
                Field('batch', 'boolean', default=True, label='Enable Batch Payslip'),
                Field('allowances', 'float'),
                Field('visa_no', 'string', label='Visa No'),
                Field('work_permit_no', 'string', label='Work Permit No'),
                Field('visa_exp', 'datetime', label='Visa Expiry Date'),
                Field('other','text'),
                Field('state', requires=IS_IN_SET(CONTRACTSTATE_SET, zero=T('--State--'))),
                auth.signature
                )



LOANSTATE_SET = [
    T('Draft'),
    T('Confirmed'),
    T('Refused'),
    T('Paid'),



]

LOANTYPE_SET = [
    T('Advance'),
    T('Long Term'),

]
db.define_table('loans',
                Field('loan_name', 'string', label='Loan Title'),
                Field('employee', 'reference employee', required='true',
                      requires=IS_IN_DB(db, "employee.id", '%(fullname)s')),
                Field('contract', 'reference contract', required='true',
                      requires=IS_IN_DB(db, "contract.id", '%(contract_name)s')),
                Field('loan_type', requires=IS_IN_SET(LOANTYPE_SET, zero=T('--Type--'))),
                Field('loan_amount', 'float',label='Loan Amount'),
                Field('paid', 'float', label='Amount Paid',default='0.0'),
                Field('balance', 'float', label='Loan Amount Remaining',default='0.0',readable=False),
                Field('date','datetime',label='Date request',default=lambda:datetime.now()),
                Field('date_start', 'datetime', label='Start of Payment'),
                Field('no_of_months', 'integer',label='No of Months'),
                Field('state', requires=IS_IN_SET(LOANSTATE_SET, zero=T('--State--')),default='Draft'),
                auth.signature
                )

db.define_table('loanpayment',
                Field('loanpayment_name', 'string', label='Payment Title'),
                Field('employee', 'reference employee', required='true',
                      requires=IS_IN_DB(db, "employee.id", '%(fullname)s')),
                Field('loan', 'reference loans', required='true',
                      requires=IS_IN_DB(db, "loans.id", '%(loan_name)s')),
                Field('amount_payed', 'float', label='Loan Amount Payed'),
                Field('date', 'datetime', label='Date of Payment'),
                auth.signature
                )

PAYSLIPSTATE_SET = [
    T('Draft'),
    T('Confirmed'),
    T('Closed'),
    T('Cancelled'),



]
db.define_table('payslip',
                Field('employee', 'reference employee', required='true',
                      requires=IS_IN_DB(db, "employee.id", '%(fullname)s')),
                Field('contract', 'reference contract', required='true',
                      requires=IS_EMPTY_OR(IS_IN_DB(db, "contract.id", '%(contract_name)s'))),
                Field('payslip_name', 'string', label='Payslip Title'),
                Field('gross', 'float',readable=False, writable=False),
                Field('net', 'float',readable=False, writable=False),
                Field('rssb_emp','float',readable=False, writable=False),
                Field('rssb_comp','float',readable=False, writable=False),
                Field('rssb_tot','float',readable=False, writable=False),
                Field('paye','float',readable=False, writable=False),
                #Field('loan','float',default='0'),
                Field('loan', 'reference loanpayment',label='Loan ',requires=IS_EMPTY_OR(IS_IN_DB(db, "loanpayment.id", '%(loanpayment_name)s'))),
                Field('date','datetime',default=lambda:datetime.now()),
                Field('date_pay_start', 'datetime', label='Start of Payment Period'),
                Field('date_pay_end', 'datetime', label='End of Payment Period'),
                #Field('contract_type', requires=IS_IN_SET(CONTRACTTYPE_SET, zero=T('--choose contract type--')),readable=False, writable=False),
                #Field('salary_structure', requires=IS_IN_SET(SALARYSTRUCTURE_SET, zero=T('--choose salary type--')),readable=False, writable=False),
                #Field('job', 'reference job_title',readable=False, writable=False),
                Field('user', 'reference auth_user',label='Link to User',requires=IS_EMPTY_OR(IS_IN_DB(db, "auth_user.id", '%(fullname)s'))),
                Field('state', requires=IS_IN_SET(PAYSLIPSTATE_SET, zero=T('--State--'))),

                auth.signature
                )

[stifan kristi]

pls try :

query = (db.payslip.state == 'Confirmed')

sum = db.payslip.gross.sum()

print db(query).select(sum).first()[sum]

ref:

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#sum--avg--min--max-and-len

best regards,

stifan

[Oasis Agano]

I tried it but i got another error

CONTROLLER

def dashboard_admin():

    query = (db.payslip.state == 'Confirmed')
    sum = db.payslip.gross.sum()
    print db(query).select(sum).first()[sum]

    return locals()

Error

Traceback (most recent call last):
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\restricted.py", line 227, in restricted
    exec ccode in environment

  File "D:/lab/PycharmProjects/Smartwork/web2py/applications/smartwork/controllers/default.py", line 1212, in <module>
  

File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\globals.py", line 417, in <lambda>
    self._caller = lambda f: f()
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\tools.py", line 4241, in f
    return action(*a, **b)
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\tools.py", line 4241, in f
    return action(*a, **b

)
  File "D:/lab/PycharmProjects/Smartwork/web2py/applications/smartwork/controllers/default.py", line 42, in dashboard_admin
    print db(query).select(sum).first()[sum]

IOError: [Errno 22] Invalid argument

[黄祥]

pls try not tested :

def dashboard_admin():

    query = (db.payslip.state == 'Confirmed')

    sum = db.payslip.gross.sum()

    sum_result = db(query).select(sum).first()[sum]

    return locals()

    #return dict(sum_result = sum_result) # try if return locals() not work

this should try in python console not in web2py controller

>>> query = (db.payslip.state == 'Confirmed')

>>> sum = db.payslip.gross.sum()

>>> print db(query).select(sum).first()[sum]

best regards,

stifan

[Anthony Bastardi]

Hey all,

Please don't cc my personal email address on web2py Google Group posts. I'll see any posts directly on the group. Thanks.

Anthony

[Anthony]

Is that the full traceback? What happens if you remove the "print" statement? Can you print just rows.first()?

Anthony

[Oasis Agano]

Solved by this

Controller

def dashboard_admin():
    query = (db.payslip.state == 'Confirmed')
    sum = db.payslip.gross.sum()
    sum_result = db(query).select(sum).first()[sum]

    #NET
    #query = (db.payslip.state == 'Confirmed')
    neti = db.payslip.rssb_emp.sum()
    net_result = db(query).select(neti).first()[neti]
    return locals()

View

{{=sum_result}}
{{=net_result}}


Thanks to all