Creating a batch using a loop

[Oasis Agano]

Greetings

im creating a payroll app and i need a to create a batch of payslips

i want to create payslips for all contracts in the database within the running state

 the code

def check_batch(form):

    contraaa = db(db.contract.state == 'Running').select(db.contract.ALL)
    for contraa in contraaa:

        if contraa.salary_structure == 'Less than 30000':
            totgross=contraa.wage+contraa.allowances

            form.vars.employee=contraa.employee.fullname
            form.vars.payslip_name=contraa.employee.fullname
            form.vars.contract=contraa.contract_name

            form.vars.gross=totgross
            form.vars.rssb_emp = totgross*0.03
            form.vars.rssb_comp = totgross*0.05
            form.vars.paye = 0
            totrssb=form.vars.rssb_emp+form.vars.rssb_comp
            form.vars.rssb_tot=form.vars.rssb_emp+form.vars.rssb_comp
            if type(form.vars.loan) !=int :

                form.vars.net = totgross-form.vars.rssb_emp
            else:
                payy=db(db.loanpayment.id == form.vars.loan).select(db.loanpayment.ALL)
                for pay in payy:
                    loanpay=int(pay.amount_payed)
                    form.vars.net = totgross-form.vars.rssb_emp-loanpay

        elif contraa.salary_structure == 'Between 30000 and 100000':

            form.vars.employee=contraa.employee.fullname
            form.vars.payslip_name=contraa.employee.fullname
            form.vars.contract=contraa.contract_name

            totgross=contraa.wage+contraa.allowances
            form.vars.gross=totgross
            form.vars.rssb_emp = totgross*0.03
            form.vars.rssb_comp = totgross*0.05
            varia =totgross-30000
            form.vars.paye = varia*0.2
            totrssb=form.vars.rssb_emp+form.vars.rssb_comp
            form.vars.rssb_tot=form.vars.rssb_emp+form.vars.rssb_comp
            if type(form.vars.loan) !=int :
                form.vars.net = totgross-form.vars.rssb_emp-form.vars.paye
            else:
                payy=db(db.loanpayment.id == form.vars.loan).select(db.loanpayment.ALL)
                for pay in payy:
                    loanpay=int(pay.amount_payed)
                    form.vars.net = totgross-form.vars.rssb_emp-form.vars.paye-loanpay


            #form = SQLFORM(db.payslip)



        elif contraa.salary_structure=='Great than 100000':

            form.vars.employee=contraa.employee
            form.vars.payslip_name=contraa.employee
            form.vars.contract=contraa.contract_name

            totgross=contraa.wage+contraa.allowances
            form.vars.gross=totgross
            form.vars.rssb_emp = totgross*0.03
            form.vars.rssb_comp = totgross*0.05
            varia2 =totgross-100000
            variah = varia2*0.3
            varia3 =70000*0.2
            form.vars.paye = variah+varia3
            totrssb=form.vars.rssb_emp+form.vars.rssb_comp
            form.vars.rssb_tot=form.vars.rssb_emp+form.vars.rssb_comp
            if type(form.vars.loan) !=int :
                form.vars.net = totgross-form.vars.rssb_emp-form.vars.paye
            else:
                payy=db(db.loanpayment.id == form.vars.loan).select(db.loanpayment.ALL)
                for pay in payy:
                    loanpay=int(pay.amount_payed)
                    form.vars.net = totgross-form.vars.rssb_emp-form.vars.paye-loanpay

            #form = SQLFORM(db.payslip)

        else:
            response.flash=T('Select a salary structure and contract')


def batch_payslip():
       db.payslip.gross.readonly =True
       db.payslip.net.readable =False
       db.payslip.rssb_emp.readable =False
       #db.payslip.salary_structure.readable =False
       db.payslip.rssb_comp.readable =False
       db.payslip.paye.readable =False
       db.payslip.employee.readable =False
       db.payslip.employee.writable =False
       db.payslip.contract.readable =False
       db.payslip.contract.writable =False
       db.payslip.payslip_name.readable =False
       db.payslip.payslip_name.writable =False
       db.payslip.loan.readable =False
       db.payslip.loan.writable =False
       db.payslip.user.readable =False
       db.payslip.user.writable =False
       form = SQLFORM(db.payslip)

       if form.process(onvalidation=check_batch).accepted:
           response.flash=T('Payslip Added')
       return dict(form=form)

im getting this error

ValueError: invalid literal for long() with base 10: 'with a contract name'

[Anthony]

Hard to say what the problem is without seeing the full traceback as well as your models.

Also, what are you trying to do in the check_batch function? You are looping through some records and making assignments to form.vars, but only the final run of the loop will end up taking effect -- so what is the point of the loop?

Anthony

[Michele Comitini]

Could be an issue with some reference field.  Are you using a IS_IN_DB validator in your model? could be that you are passing a wrong parameter to the validator.   But with so little info it is just a guess.

--
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 web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Oasis Agano]

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 708, 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/applications/smartwork/controllers/default.py", line 300, in batch_payslip
    if form.process(onvalidation=check_batch).accepted:
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\html.py", line 2298, in process
    self.validate(**kwargs)
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\html.py", line 2236, in validate
    if self.accepts(**kwargs):
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\sqlhtml.py", line 1746, in accepts
    self.vars.id = self.table.insert(**fields)
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\objects.py", line 726, in insert
    ret = self._db._adapter.insert(self, self._listify(fields))
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\adapters\base.py", line 739, in insert
    query = self._insert(table,fields)
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\adapters\base.py", line 730, in _insert
    values = ','.join(self.expand(v, f.type) for f, v in fields)
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\adapters\base.py", line 730, in <genexpr>
    values = ','.join(self.expand(v, f.type) for f, v in fields)
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\adapters\base.py", line 962, in expand
    rv = self.represent(expression, field_type)
  File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1442, in represent
    return str(long(obj))
ValueError: invalid literal for long() with base 10: 'Jul Contract'

Note that this contract is the second in the database

Basically what im trying to do is create a loop(from a form) that will create payslips for all running contracts

[Anthony]

Well, you still haven't shown your models. My guess is one of your fields is a reference field, which stores long int values representing the record ID of the referenced record, but you are attempting to insert a string value.

Anyway, your approach won't work because SQLFORM only does a single insert, which happens after the onvalidation callback runs. So, only the last set of form.vars values assigned in the onvalidation for loop will end up being inserted in the database.

Anthony

[Oasis Agano]

Models are here, if this way cant work can you suggest another way of doing it

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
                )

[Anthony]

In your model, db.payslip.contract is a reference field, but in your code, you attempt to assign a string value to it (form.vars.contract = contraa.contract_name).

Anthony

[Oasis Agano]

i changed it to form.vars.contract = contraa.id

but still getting the same error

[Jim S]

You're setting form.vars.employee = contraa.employee.fullname 

It should be set to the employee id, not the name.  Results in the same problem as with contract name/id.

Should just be contraa.employee I believe...

-Jim

[Oasis Agano]

Well its progressing now, using 

form.vars.employee=contraa.employee
form.vars.payslip_name=contraa.employee.fullname
form.vars.contract=contraa.id

Now it is inserting only one payslip(from one contract) but not inserting the other one

[Jim S]

I think that is all that it should do.

Your check_batch function is being called at the validation stage of processing your form.  All your check_batch function does is reassign values to the form variables that you've captured.  After it is done, the web2py form processing will insert the current values of the form variables into the database for you.  See Anthony's first reply.  I think your approach to this is somewhat flawed.

Here is what I see is happening:

1. User makes a request to the batch_payslips function.  Result is that a form is displayed.  

2. User enters values into the form and clicks on Submit.

3. Your onvalidation method (check_batch) is invoked.  This method resets a bunch of the form variables, but since it is in a loop, the variables set in the last pass through the loop are retained in the form variables.

4. sqlform.process().accepted takes over and inserts a record into payslips based on the current values of the form variables.

From what I think you're trying to do, I'd re-architect a bit.

1. Use a SQFORM.factory to gather whatever information it is your asking of the user.  http://web2py.com/books/default/chapter/29/07/forms-and-validators#SQLFORM-factory

2. Instead of loop through the RUNNING contracts in the onvalidation function (check_batch), put that code after the "if form.process().accepts:" statement.

3. Update that code to manually insert the records into payslips instead of just assigning values to the form variables. http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#insert

I hope this helps.  My apologies if I'm misunderstanding your goals.

-Jim

[Oasis Agano]

Briefly what im trying to do is to create payslips for all contracts in the db through a loop,

i just checked sqlform factory but i have a question where do i pass the form(payslip table) to insert in into after if form.process().accepts:

[Oasis Agano]

Well all the answers helped but the sqlform factory solved it

[Anthony]

Note, you don't have to use SQLFORM.factory -- you can simply use SQLFORM and either call form.validate() or form.process(dbio=False) -- both of those options do the form validation but do not do any database insert. SQLFORM.factory is typically used when you need to create a form that is not based on a database model (i.e., when creating a form from scratch or when combining more than one model in a single form).

Anthony

[Dave S]

On Monday, September 26, 2016 at 1:34:00 PM UTC-7, Oasis Agano wrote:

Briefly what im trying to do is to create payslips for all contracts in the db through a loop,

 

I don't understand.  Is what is supposed to be done a response to a user?  Is that user an employee logging the time spent on jobs? Is that user a bookkeeper processing data for several employees?

If every contract in the database needs a payslip for every employee, then perhaps a scheduled task would be better than an interactive page.  Tasks that take a long time have issues if the browser times out, or even if it just feels like it is never going to return a response.

On the other hand, if the user is an employee entering the time spent, is the task to divide the time up among the contracts the employee is attached to?   Does the user have to show how many hours were spent on each contract?

/dps

[Jim Steil]

You don't have to pass it.  Just use the DAL to insert the records you need.  

What fields are you having the user enter data into when the form is displayed?

You can just use SQLFORM.factory to gather them.  Then in the 'if form.process().accepted:' put the code to insert the records into the database.

Tell me what fields you're gathering from the user and I can write up a sample for you.

-Jim

--

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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/OAORXQMAY6Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Oasis Agano]

I solved it already using sqlform factory then if form accepted do the loops...

and thanks for your help

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.