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 On Monday, September 26, 2016 at 10:04:30 AM UTC-4, Oasis Agano wrote: > > 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 > ) > > > On Monday, September 26, 2016 at 5:00:14 PM UTC+3, Anthony wrote: >> >> 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 >> >> On Monday, September 26, 2016 at 9:56:34 AM UTC-4, Oasis Agano wrote: >>> >>> 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" >>> >>> <http://127.0.0.1:8000/admin/default/edit/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" >>> >>> <http://127.0.0.1:8000/admin/default/edit/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 >>> >>> >>> On Monday, September 26, 2016 at 4:21:56 PM UTC+3, Anthony wrote: >>>> >>>> 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 >>>> >>>> On Monday, September 26, 2016 at 8:29:35 AM UTC-4, Oasis Agano wrote: >>>>> >>>>> 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' >>>>> >>>>> -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.