Hi,
We have a pretty complicated query that uses multiple summed fields and I
would like to use SQLFORM.grid to display them. Two situations first I need
to figure out how to translate this query into DAL (unless grid can handle
executesql?), then need to know how to use the sum() like you would with a
normal query.select().
Normal SQL
"""
select to_char(report_date,'yyyy-mm') report_date,distributor_id,
sum(sponsorbonus) sponsorbonus, sum(leadbonus) leadbonus, sum(binarybonus)
binarybonus,
sum(matchingbonus) matchingbonus, sum(growingbonus) growingbonus,
sum(adviewpayback) adviewpayback, sum(adpackcomm) adpackcomm,
sum(kvalue_deduction) kvalue_deduction, sum(group_management_fee)
group_management_fee
from bonus_daysummary
where distributor_id = %(distributor_id)s
group by to_char(report_date,'yyyy-mm'), distributor_id
"""
DAL so far:
sum_sponsor_bonus = db.bonus_summary.sponsorbonus.sum()
sum_lead_bonus = db.bonus_summary.leadbonus.sum()
sum_binary_bonus = db.bonus_summary.binarybonus.sum()
sum_matching_bonus = db.bonus_summary.matchingbonus.sum()
sum_growing_bonus = db.bonus_summary.growingbonus.sum()
sum_adview_payback = db.bonus_summary.adviewpayback.sum()
sum_ad_pack_comm = db.bonus_summary.adpackcomm.sum()
sum_kvalue_deduction = db.bonus_summary.kvalue_deduction.sum()
sum_group_manage_fee = db.bonus_summary.group_management_fee.sum()
query = db(db.bonus_daysummary.distributor_id == distributor.id)
monthlyBonusReport= SQLFORM.grid(
query, paginate=10, deletable=False, editable=False,
details=False, csv=True, formname='monthlyBonus',
)
--
--
Regards,
Bruce Wade
http://ca.linkedin.com/in/brucelwade
http://www.wadecybertech.com
http://www.warplydesigned.com
http://www.fitnessfriendsfinder.com
