Reporting invoice hours by project

classic Classic list List threaded Threaded
2 messages Options
ronstone ronstone
Reply | Threaded
Open this post in threaded view
|

Reporting invoice hours by project

Hi all,

I have a client for whom I am doing two projects. These are billed on separate invoices. All hours are tracked in the calendar, kept separate using categories, and imported to invoices using the calendar hook. Some calendar events repeat, such as weekly meetings, but do not always take place.

The client needs a report on total hours/$ billed to date on each project. I'm having a hard time seeing how to get at this information accurately. I don't see an association between line items on invoices and the categories of the calendar items on which they were based.

Any tips? Is there an SQL tally that would produce the numbers I need?

Many thanks,

Ron
Nathan Gray Nathan Gray
Reply | Threaded
Open this post in threaded view
|

Re: Reporting invoice hours by project

On Thu, Jun 23, 2011 at 3:30 PM, ronstone <[hidden email]> wrote:

> I have a client for whom I am doing two projects
> ...
> The client needs a report on total hours/$ billed to date on each project.
> I'm having a hard time seeing how to get at this information accurately. I
> don't see an association between line items on invoices and the categories
> of the calendar items on which they were based.
>
> Any tips? Is there an SQL tally that would produce the numbers I need?


Project manager can help keep them separate, and performs totaling.
Takes a little extra work though.  You might be able to add it in
retroactivly, but I'm not sure how it works with recurring events.

You could write SQL to total it up.  Something like:
SELECT SUM(unit_price * quantity), cal_category
FROM perp_invoice_line
LEFT JOIN  egw_cal ON
perp_invoice_line.invoicable_class = 'CalendarInvoicable' AND
perp_invoice_line.invoicable_id = egw_cal.cal_id
GROUP BY cal_category

Nathan

------------------------------------------------------------------------------
Simplify data backup and recovery for your virtual environment with vRanger.
Installation's a snap, and flexible recovery options mean your data is safe,
secure and there when you need it. Data protection magic?
Nope - It's vRanger. Get your free trial download today.
http://p.sf.net/sfu/quest-sfdev2dev
_______________________________________________
Perp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/perp-developers