Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- if ($augmentations['costfrom'] == 'invoiceable') {
- $cost_cols = "sum(i.quantity) AS quantity , sum(i.cost) AS cost";
- }else {
- $cost_cols = "a.quantity, a.cost";
- }
- SELECT $cols $ec , sum(revenue) as revenue, $cost_cols
- FROM (".note_detail_report_view(). ") AS i
- LEFT JOIN (
- SELECT $join_col , SUM(cost) AS cost, SUM(quantity) AS quantity
- FROM (".note_detail_report_view(). ") AS note_detail_report_view
- $whereClause AND *n_quote_status = 0*
- GROUP BY $join_col
- ) AS a
- ON $joiner
- $whereClause AND invoice = true $limit_inv
- GROUP BY $group_by $ec, a.cost , a.quantity
- ORDER BY $order_by
- SELECT
- CONCAT(client.company, ' ', client.name_first, ' ', client.name_last) AS customer,
- SUM(COALESCE(bill_item.unit_price, billable.unit_price, 0) * bill_item.quantity) AS revenue,
- SUM(bill_item.quantity) AS quantity,
- SUM(COALESCE(bill_item.unit_cost, billable.unit_cost, 0) * bill_item.quantity) AS cost
- FROM
- ja_clients AS account
- JOIN ja_customers AS client ON client.clientid = account.id
- JOIN ja_jobs AS job ON client.id=job.customerid
- JOIN ja_notes AS note ON note.jobid = job.id
- JOIN dm.bill_items AS bill_item ON bill_item.bill_item_id=note.bill_item_id
- LEFT JOIN dm.bills AS bill ON bill.bill_id=bill_item.bill_id
- LEFT JOIN dm.invoices AS invoice ON invoice.invoice_id=bill.invoice_id
- OR invoice.invoice_id=bill_item.invoice_id
- LEFT JOIN dm.billables AS billable ON billable.billable_id=note.billable_id
- LEFT JOIN dm.labors AS labs ON labs.billable_id = billable.billable_id
- JOIN ja_mobiusers AS "user" ON "user".id = note.mobiuserid
- JOIN ja_status AS status ON status.id = job.status_label_id
- JOIN ja_role AS ROLE ON ROLE.id="user".user_type
- WHERE
- note.note_type::text = ANY (ARRAY[ ('time'::CHARACTER VARYING)::text, ('part'::CHARACTER VARYING)::text ]) AND
- NOT job.templated AND
- NOT job.deleted AND
- job.clientid = 6239 AND
- time_job >= 1438351200 AND
- time_job <= 1448888340 AND
- bill_item.for_invoicing = TRUE
- GROUP BY
- customer
- ORDER BY
- revenue DESC
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement