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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
