Hi,
I have a question on using the checkboxes widget, and the correct way
to go about what I want to do. Here's some background:
The company I work for places environmental monitoring equipment in
the field. I recently made a small database application to keep track
of where we have equipment. My boss has asked if I can include payment
details in the database. I said "sure."
The "business logic" goes about like this--
Someone called a "sitefinder" finds a place where we can put some
equipment. Every so often, the sitefinder will send an invoice to the
head office for his sites. Head office sends the sitefinder a check.
Usually the first check is for the value of (# of sites) * (price per
site) * 70%. After the equipment has been on site for a month, the
sitefinder will get another check for the additional 30%. Sometimes,
the check will be mixed-- for example 5 new sites @ 70% and 4 old
sites @ 30%.
So, I already had a table for the sites where we have equipment. It's
db.plads (name is for legacy reasons). There's also a table for the
sitefinders (db.wards-- also for legacy reasons). There are a few
other tables (status, state, etc.).
I added two tables. They are db.payments and db.payment details.
The models for the two new tables look like this:
db.define_table('payments',
Field('sitefinder', db.wards),
Field('date', 'date'),
Field('amount', 'double'),
Field('comments', 'text'),
format = '%(sitefinder.wardname)s, %(date)s')
db.payments.sitefinder.requires = IS_IN_DB(db, db.wards.id, '%
(wardname)s')
db.payments.date.requires = IS_DATE()
db.define_table('paymentdetails',
Field('payment', db.payments),
Field('plads', db.plads),
Field('percentage', 'integer'))
db.paymentdetails.payment.requires = IS_IN_DB(db, db.payments.id, '%
(sitefinder)s, %(date)s')
db.paymentdetails.plads.requires = IS_IN_DB(db, db.plads.id, '%
(name)s, %(address)s')
The idea is that when we pay someone the boss (or more likely, me
doing the data entry) will create a new payment showing who we are
paying, the date, amount we're paying, and various notes (like check
#, etc.). Once the payment is created, we add sites to the payment
(paymentdetails table).
The most straightforward way I can think of to do this is to have a
page showing a list of all sites from a sitefinder that are eligible
for payment. There should be a checkbox next to each site, and box at
the bottom to enter the percentage of payment. So, I can check 5
sites, enter 70 in the percentage box, hit submit, and 5 rows will be
added to the paymentdetails table containing the payment id, place id,
and percentage of payment.
Here's the code that I've written to implement this (not the
prettiest, still a work in progress):
Controller:
def paymentdetails():
def gettotalpercent(somesite):
#Calculates total percentage of payments on a site
percentset = [x.percentage for x in
somesite.paymentdetails.select()]
return sum(percentset)
#Query database for already existing paymentdetails on this
payment
payment = db.payments(request.args(0))
details = db(db.paymentdetails.payment == db.payments.id).select()
#Generate the list of sites from sitefinder w/ less than 100%
payment for checkboxes widget
placedbysitefinder = db(db.plads.sitefinder ==
payment.sitefinder).select()
placedlessthan100 = filter(lambda a: gettotalpercent(a) < 100,
placedbysitefinder)
placeddict = {}
for place in placedlessthan100:
placeddict[place.id] = place.name + ', ' +
str(gettotalpercent(place)) + '%'
#create a the SQL form. Shows a checkbox widget with all sites
from sitefinder that haven't been fully paid
#user can check which sites to add to a payment, and input the
percentage of payment
addform = SQLFORM.factory(
Field('Add', requires=IS_EMPTY_OR(IS_IN_SET(placeddict,
zero=T('Choose One'))),
widget=SQLFORM.widgets.checkboxes.widget),
Field('Percentage',
requires=IS_EMPTY_OR(IS_INT_IN_RANGE(0,101)))
)
#step through all of the checked sites and insert them into
paymentdetails
if addform.process().accepted:
percentage = addform.vars.Percentage
for place in addform.vars.Add:
db.paymentdetails.insert(payment = payment.id, plads =
place, percentage = percentage)
redirect(URL('paymentdetails', args=payment.id))
return dict(details = details, addform = addform)
View:
{{extend 'layout.html'}}
<h1>This is the default/paymentdetails.html template</h1>
{{=details}}
{{=addform}}
So, this kind of works, but not really. I'm running into two problems:
1) In the following section of code:
for place in addform.vars.Add:
db.paymentdetails.insert(payment = payment.id, plads =
place, percentage = percentage)
The value that gets inserted is not plads.id. Instead, it appears that
the index of site in the checkboxes widget is inserted. So, if I check
the first item in the checkboxes widget, 0 gets inserted instead of
the id of the place on that row.
2) If I select multiple checkboxes, the form does not validate. It
gives me 'value not allowed' below each checkbox.
I have two questions:
1) Am I even going about this the correct way? Am I totally off base
on how to implement records of payments?
2) If I am going about this the correct way, how can I insert the
correct value for plads.id? How can I insert multiple values?
Thanks for any help. Best regards,
Nate Atkinson
