db.define_table('product cost',
Field('quote_products_id', db.quote_products, requires=IS_IN_DB
(db,
'quote_products.id', lambda row: row.product.description')),
Field('quote_products_id', db.quote_products, requires=IS_IN_DB
(db,
'quote_products.id', 'quote_products.product')),
Field('human_cost', 'double'),
Field('external_cost', 'double'),
Field('total_cost', 'double', writable=False, compute=lambda
row: double(row['human_cost'])*double(row['external_cost'])))
On Jan 29, 5:59 am, Leandro - ProfessionalIT <lsever...@gmail.com>
wrote:
> Friends,
>
> I have this situation:
> My customers quote products and all products in the quote has a
> separated cost. The sum of all cost for all products of a quote is the
> price of product for this customer.
>
> To map this scenario, I have these tables:
>
> a) customer:
>
> db.define_table('customer',
> Field('name))
>
> Example Data:
> id | name
> 1 | Google
> 2 | IBM
> 3 | Oracle
>
> b) products:
>
> db.define_table('products',
> Field('internal_code', 'string', length=4),
> Field('description', 'string', length=50))
>
> Example Data:
> id | internal_code | description
> 1 | A001 | Website
> 2 | A002 | CRM
> 3 | B001 | Mobile App
>
> c) quotes:
> db.define_table('quotes',
> Field('quote_date', 'date'),
> Field('customer_id', db.customers, requires=IS_IN_DB(db,
> 'customer.id', 'customer.name')))
>
> Example Data:
> id | quote_date | customer_id
> 1 | 2010-10-01 | 1 (Google)
> 2 | 2010-10-02 | 2 (IBM)
>
> d) quote_products:
> db.define_table('quote_products',
> Field('quote_id', db.quotes, requires=IS_IN_DB(db, 'quotes.id',
> 'quotes.id')),
> Field('product_id', db.products, requires=IS_IN_DB(db,
> 'products.id', 'products.description')))
>
> Example Data:
> id | quote_id | product_id
> 1 | 1 | 3 (Mobile APP)
> 2 | 1 | 2 (CRM)
> 3 | 1 | 1 (Website)
>
> e) product cost
> id
> quote_products_id
> human_cost
> external_cost
> total_cost
>
> db.define_table('product cost',
> # A) this don't runs, generate a error, because the column
> description can not be accessed.
> #Field('quote_products_id', db.quote_products, requires=IS_IN_DB(db,
> 'quote_products.id', 'quote_products.product.description')),
> Field('quote_products_id', db.quote_products, requires=IS_IN_DB(db,
> 'quote_products.id', 'quote_products.product')),
> Field('human_cost', 'double'),
> Field('external_cost', 'double'),
> Field('total_cost', 'double'))
>
> Example Data:
> id | quote_products_id | human_cost | external_cost | total_cost
> 1 | 1 | 2.00 |
> 5.00 | 7.00
> 2 | 2 | 1.00
> | | 1
> 3 | 3 | 5.00 |
> 6.00 | 11.00
>
> PS:
> For each item in quote_products table...I can have a record in the
> table product_cost.
> And the SUM() of the all 'total_cost columns' grouped by
> quote_products_id is the total value for a quote.
> For example, the cost of the quote in the date 2010-10-01 (id=01)
> is $ 19.00
>
> Then, I have two questions:
> 1) How to solve the A) question ?
> 2) How to, automatically, SUM the columns 'human_cost' and
> 'external_cost' and put this sum in the column total_cost ?
>
> -- Leandro.
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to web...@googlegroups.com.
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
