1 -- Group: orderActivityByProject
6 formatQty(qty) AS f_qty,
7 formatMoney(value) AS f_value,
8 'curr' AS qty_xtnumericrole,
9 'curr' AS value_xtnumericrole,
10 CASE WHEN COALESCE(hrs_balance,0) < 0 THEN 'red' END AS hrs_balance_qtforegroundrole,
11 CASE WHEN COALESCE(exp_balance,0) < 0 THEN 'red' END AS exp_balance_qtforegroundrole
17 NULL AS section_qtdisplayrole,
20 WHEN prj_status = 'C' THEN <? value('complete') ?>
21 WHEN prj_status = 'O' THEN <? value('inprocess') ?>
22 WHEN prj_status = 'P' THEN <? value('planning') ?>
24 prjtype_code AS project_type,
26 firstline(prj_descrip) AS descrip,
27 crmacct_name AS customer,
28 cntct_name AS contact,
33 NULL::numeric AS value,
35 prj_assigned_date AS assigned,
36 prj_start_date AS started,
37 prj_completed_date AS completed,
38 NULL::numeric AS hrs_budget,
39 NULL::numeric AS hrs_actual,
40 NULL::numeric AS hrs_balance,
41 NULL::numeric AS exp_budget,
42 NULL::numeric AS exp_actual,
43 NULL::numeric AS exp_balance,
44 CASE WHEN (prj_status = 'O' AND prj_due_date < current_date) THEN 'red'
45 WHEN (prj_status = 'O' AND prj_due_date BETWEEN current_date AND current_date + (fetchmetricvalue('ProjectDueDateWarning')||' days')::interval) THEN 'orange'
46 END AS due_qtforegroundrole,
49 LEFT OUTER JOIN prjtype ON (prj_prjtype_id=prjtype_id)
50 LEFT OUTER JOIN crmacct ON (prj_crmacct_id=crmacct_id)
51 LEFT OUTER JOIN cntct ON (crmacct_cntct_id_1=cntct_id)
52 LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
54 WHERE (prj_id = <? value("prj_id") ?> )
58 SELECT DISTINCT -1 AS id,
62 'Tasks' AS section_qtdisplayrole,
65 NULL::text AS project_type,
67 NULL::text AS descrip,
68 NULL::text AS customer,
69 NULL::text AS contact,
74 NULL::numeric AS value,
76 NULL::date AS assigned,
77 NULL::date AS started,
78 NULL::date AS completed,
79 NULL::numeric AS hrs_budget,
80 NULL::numeric AS hrs_actual,
81 NULL::numeric AS hrs_balance,
82 NULL::numeric AS exp_budget,
83 NULL::numeric AS exp_actual,
84 NULL::numeric AS exp_balance,
86 NULL::text AS due_qtforegroundrole,
91 SELECT prjtask_id AS id,
95 NULL AS section_qtdisplayrole,
96 prjtask_number AS name,
98 WHEN prjtask_status = 'C' THEN <? value('complete') ?>
99 WHEN prjtask_status = 'O' THEN <? value('inprocess') ?>
100 WHEN prjtask_status = 'P' THEN <? value('planning') ?>
102 NULL::text AS project_type,
103 prjtask_name AS item,
104 prjtask_descrip AS descrip,
105 cust_name as customer,
106 cntct_name AS contact,
109 NULL::numeric AS qty,
111 NULL::numeric AS value,
112 prjtask_due_date AS due,
113 prjtask_assigned_date AS assigned,
114 prjtask_start_date AS started,
115 prjtask_completed_date AS completed,
116 prjtask_hours_budget AS hrs_budget,
117 prjtask_hours_actual AS hrs_actual,
118 (prjtask_hours_budget-prjtask_hours_actual) AS hrs_balance,
119 prjtask_exp_budget AS exp_budget,
120 prjtask_exp_actual AS exp_actual,
121 (prjtask_exp_budget-prjtask_exp_actual) AS exp_balance,
122 CASE WHEN (prjtask_status = 'O' AND prjtask_due_date < current_date) THEN 'red'
123 WHEN (prjtask_status = 'O' AND prjtask_due_date BETWEEN current_date AND current_date + (fetchmetricvalue('ProjectDueDateWarning')||' days')::interval) THEN 'orange'
124 END AS due_qtforegroundrole,
127 LEFT OUTER JOIN te.teprjtask ON (prjtask_id = teprjtask_prjtask_id)
128 LEFT OUTER JOIN custinfo ON (teprjtask_cust_id=cust_id)
129 LEFT OUTER JOIN cntct ON (cust_corrcntct_id=cntct_id)
130 LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
132 WHERE (prjtask_prj_id = <? value("prj_id") ?> )
133 GROUP BY custinfo.cust_name, prjtask.prjtask_id, addr.addr_city, addr.addr_state, cntct_name
135 <? if exists("showIn") ?>
137 ----- INCIDENTS -----
139 SELECT DISTINCT -1 AS id,
143 'Incidents' AS section_qtdisplayrole,
145 NULL::text AS status,
146 NULL::text AS project_type,
148 NULL::text AS descrip,
149 NULL::text AS customer,
150 NULL::text AS contact,
153 NULL::numeric AS qty,
155 NULL::numeric AS value,
157 NULL::date AS assigned,
158 NULL::date AS started,
159 NULL::date AS completed,
160 NULL::numeric AS hrs_budget,
161 NULL::numeric AS hrs_actual,
162 NULL::numeric AS hrs_balance,
163 NULL::numeric AS exp_budget,
164 NULL::numeric AS exp_actual,
165 NULL::numeric AS exp_balance,
167 NULL::text AS due_qtforegroundrole,
172 SELECT incdt_id AS id,
176 NULL AS section_qtdisplayrole,
177 incdt_number::text AS name,
179 WHEN incdt_status = 'L' THEN <? value('closed') ?>
180 WHEN incdt_status = 'N' THEN <? value('new') ?>
181 WHEN incdt_status = 'F' THEN <? value('feedback') ?>
182 WHEN incdt_status = 'A' THEN <? value('assigned') ?>
183 WHEN incdt_status = 'R' THEN <? value('resolved') ?>
184 WHEN incdt_status = 'C' THEN <? value('confirmed') ?>
186 NULL::text AS project_type,
187 incdt_number::text AS item,
188 incdt_summary AS descrip,
189 crmacct_number AS customer,
190 crmacct_name AS contact,
193 NULL::numeric AS qty,
195 NULL::numeric AS value,
197 NULL::date AS assigned,
198 NULL::date AS started,
199 NULL::date AS completed,
200 NULL::numeric AS hrs_budget,
201 NULL::numeric AS hrs_actual,
202 NULL::numeric AS hrs_balance,
203 NULL::numeric AS exp_budget,
204 NULL::numeric AS exp_actual,
205 NULL::numeric AS exp_balance,
206 NULL::text AS due_qtforegroundrole,
209 LEFT OUTER JOIN crmacct ON (incdt_crmacct_id=crmacct_id)
210 LEFT OUTER JOIN cntct ON (incdt_cntct_id=cntct_id)
211 LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
213 WHERE (incdt_prj_id = <? value("prj_id") ?> )
214 GROUP BY incdt_id, incdt_prj_id, incdt_number, crmacct_number, crmacct_name, addr.addr_city, addr.addr_state, cntct_name, incdt_number, incdt_status, incdt_assigned_username, incdt_owner_username, incdt_number, incdt_summary
217 <? if exists("showSo") ?>
221 SELECT DISTINCT -1 AS id,
225 <? value("quotes") ?> AS section_qtdisplayrole,
226 <? value("quotes") ?> AS name,
227 NULL::text AS status,
228 NULL::text AS project_type,
230 NULL::text AS descrip,
231 NULL::text AS customer,
232 NULL::text AS contact,
235 NULL::numeric AS qty,
237 NULL::numeric AS value,
239 NULL::date AS assigned,
240 NULL::date AS started,
241 NULL::date AS completed,
242 NULL::numeric AS hrs_budget,
243 NULL::numeric AS hrs_actual,
244 NULL::numeric AS hrs_balance,
245 NULL::numeric AS exp_budget,
246 NULL::numeric AS exp_actual,
247 NULL::numeric AS exp_balance,
249 NULL::text AS due_qtforegroundrole,
252 <? if exists("owner_username") ?>
253 JOIN prj ON (prj_id=quhead_prj_id
254 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
256 WHERE (quhead_prj_id = <? value("prj_id") ?>)
257 <? if exists("owner_username") ?>
258 AND (quhead_owner_username=<? value("owner_username") ?>)
263 SELECT quhead_id AS id,
265 quhead_number AS subtype,
267 <? value("quotes") ?> AS section_qtdisplayrole,
268 quhead_number AS name,
269 CASE WHEN (quhead_status = 'C') THEN
270 <? value("converted") ?>
271 WHEN (quhead_status = 'X') THEN
272 <? value("canceled") ?>
273 WHEN (COALESCE(quhead_expire, current_date + 1) > current_date) THEN
276 <? value("expired") ?>
278 NULL::text AS project_type,
280 NULL::text AS descrip,
281 cust_name AS customer,
282 NULL::text AS contact,
289 NULL::date AS assigned,
290 NULL::date AS started,
291 NULL::date AS completed,
292 NULL::numeric AS hrs_budget,
293 NULL::numeric AS hrs_actual,
294 NULL::numeric AS hrs_balance,
295 NULL::numeric AS exp_budget,
296 NULL::numeric AS exp_actual,
297 NULL::numeric AS exp_balance,
299 NULL::text AS due_qtforegroundrole,
302 JOIN custinfo ON (quhead_cust_id=cust_id)
303 <? if exists("owner_username") ?>
304 JOIN prj ON (prj_id=quhead_prj_id
305 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
307 JOIN quitem ON (quitem_quhead_id = quhead_id)
308 WHERE (quhead_prj_id = <? value("prj_id") ?>)
309 <? if exists("owner_username") ?>
310 AND (quhead_owner_username=<? value("owner_username") ?>)
312 GROUP BY quhead_id, quhead_number, quhead_status, quhead_expire, quhead_freight, quhead_misc, custinfo.cust_name
316 SELECT quitem_id AS id,
318 quhead_number AS subtype,
320 <? value("quotes") ?> AS section_qtdisplayrole,
321 quitem_linenumber::text AS name,
322 CASE WHEN (quhead_status = 'C') THEN
323 <? value("converted") ?>
324 WHEN (quhead_status = 'X') THEN
325 <? value("canceled") ?>
326 WHEN (COALESCE(quhead_expire, current_date + 1) > current_date) THEN
329 <? value("Expired") ?>
331 NULL::text AS project_type,
333 item_descrip1 || ' ' || item_descrip2 AS descrip,
334 NULL::text AS customer,
335 NULL::text AS contact,
340 (quitem_qtyord * quitem_qty_invuomratio) * (quitem_price / quitem_price_invuomratio) AS value,
342 NULL::date AS assigned,
343 NULL::date AS started,
344 NULL::date AS completed,
345 NULL::numeric AS hrs_budget,
346 NULL::numeric AS hrs_actual,
347 NULL::numeric AS hrs_balance,
348 NULL::numeric AS exp_budget,
349 NULL::numeric AS exp_actual,
350 NULL::numeric AS exp_balance,
352 NULL::text AS due_qtforegroundrole,
355 <? if exists("owner_username") ?>
356 JOIN prj ON (prj_id=quhead_prj_id
357 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
359 JOIN quitem ON (quitem_quhead_id = quhead_id)
360 JOIN uom ON (quitem_qty_uom_id = uom_id)
361 JOIN itemsite ON (quitem_itemsite_id = itemsite_id)
362 JOIN item ON (itemsite_item_id = item_id)
363 WHERE (quhead_prj_id = <? value("prj_id") ?>)
364 <? if exists("owner_username") ?>
365 AND (quhead_owner_username=<? value("owner_username") ?>)
370 SELECT quhead_id AS id,
372 quhead_number AS subtype,
374 <? value("quotes") ?> AS section_qtdisplayrole,
375 <? value("total") ?> AS name,
377 NULL::text AS project_type,
379 NULL::text AS descrip,
380 NULL::text AS customer,
381 NULL::text AS contact,
386 SUM((quitem_qtyord * quitem_qty_invuomratio) * (quitem_price / quitem_price_invuomratio)) AS value,
388 NULL::date AS assigned,
389 NULL::date AS started,
390 NULL::date AS completed,
391 NULL::numeric AS hrs_budget,
392 NULL::numeric AS hrs_actual,
393 NULL::numeric AS hrs_balance,
394 NULL::numeric AS exp_budget,
395 NULL::numeric AS exp_actual,
396 NULL::numeric AS exp_balance,
398 NULL::text AS due_qtforegroundrole,
401 <? if exists("owner_username") ?>
402 JOIN prj ON (prj_id=quhead_prj_id
403 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
405 JOIN quitem ON (quitem_quhead_id = quhead_id)
406 WHERE (quhead_prj_id = <? value("prj_id") ?>)
407 <? if exists("owner_username") ?>
408 AND (quhead_owner_username=<? value("owner_username") ?>)
410 GROUP BY quhead_id, quhead_number
416 MAX(quhead_number) AS subtype,
418 <? value("quotes") ?> AS section_qtdisplayrole,
419 <? value("total") ?> || ' ' || <? value("quotes") ?> AS name,
421 NULL::text AS project_type,
423 NULL::text AS descrip,
424 NULL::text AS customer,
425 NULL::text AS contact,
430 SUM((quitem_qtyord * quitem_qty_invuomratio) * (quitem_price / quitem_price_invuomratio)) AS value,
432 NULL::date AS assigned,
433 NULL::date AS started,
434 NULL::date AS completed,
435 NULL::numeric AS hrs_budget,
436 NULL::numeric AS hrs_actual,
437 NULL::numeric AS hrs_balance,
438 NULL::numeric AS exp_budget,
439 NULL::numeric AS exp_actual,
440 NULL::numeric AS exp_balance,
442 NULL::text AS due_qtforegroundrole,
445 <? if exists("owner_username") ?>
446 JOIN prj ON (prj_id=quhead_prj_id
447 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
449 JOIN quitem ON (quitem_quhead_id = quhead_id)
450 WHERE (quhead_prj_id = <? value("prj_id") ?>)
451 <? if exists("owner_username") ?>
452 AND (quhead_owner_username=<? value("owner_username") ?>)
457 ------ SALES ORDERS ------
458 SELECT DISTINCT -1 AS id,
462 <? value("sos") ?> AS section_qtdisplayrole,
463 <? value("sos") ?> AS name,
464 NULL::text AS status,
465 NULL::text AS project_type,
467 NULL::text AS descrip,
468 NULL::text AS customer,
469 NULL::text AS contact,
472 NULL::numeric AS qty,
474 NULL::numeric AS value,
476 NULL::date AS assigned,
477 NULL::date AS started,
478 NULL::date AS completed,
479 NULL::numeric AS hrs_budget,
480 NULL::numeric AS hrs_actual,
481 NULL::numeric AS hrs_balance,
482 NULL::numeric AS exp_budget,
483 NULL::numeric AS exp_actual,
484 NULL::numeric AS exp_balance,
486 NULL::text AS due_qtforegroundrole,
489 <? if exists("owner_username") ?>
490 JOIN prj ON (prj_id=cohead_prj_id
491 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
493 WHERE (cohead_prj_id = <? value("prj_id") ?>)
497 SELECT cohead_id AS id,
499 cohead_number::text AS subtype,
501 <? value("sos") ?> AS section_qtdisplayrole,
502 cohead_number::text AS name,
504 CASE WHEN (coitem_status = 'O') THEN
506 WHEN (coitem_status = 'C') THEN
509 <? value("canceled") ?>
512 (SELECT coitem_status,
514 WHEN (coitem_status = 'O') THEN 1
515 WHEN (coitem_status = 'C') then 2
519 WHERE (coitem_cohead_id=cohead_id)
521 LIMIT 1) AS sts) ,'O')
523 NULL::text AS project_type,
525 shipto_name AS descrip,
526 cust_name AS customer,
527 NULL::text AS contact,
534 NULL::date AS assigned,
535 NULL::date AS started,
536 NULL::date AS completed,
537 NULL::numeric AS hrs_budget,
538 NULL::numeric AS hrs_actual,
539 NULL::numeric AS hrs_balance,
540 NULL::numeric AS exp_budget,
541 NULL::numeric AS exp_actual,
542 NULL::numeric AS exp_balance,
544 NULL::text AS due_qtforegroundrole,
547 JOIN custinfo ON (cust_id = cohead_cust_id)
548 LEFT OUTER JOIN shiptoinfo ON (cohead_shipto_id = shipto_id)
549 <? if exists("owner_username") ?>
550 JOIN prj ON (prj_id=cohead_prj_id
551 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
553 JOIN coitem ON (coitem_cohead_id = cohead_id)
554 WHERE (cohead_prj_id = <? value("prj_id") ?>)
555 GROUP BY cohead_id, cohead_number, shipto_num, shipto_name, cust_name
559 SELECT coitem_id AS id,
561 cohead_number::text AS subtype,
563 <? value("sos") ?> AS section_qtdisplayrole,
564 coitem_linenumber::text AS name,
565 CASE WHEN (coitem_status = 'O') THEN
567 WHEN (coitem_status = 'C') THEN
568 <? value("closed") ?>
569 WHEN (coitem_status = 'X') THEN
570 <? value("canceled") ?>
572 NULL::text AS project_type,
574 item_descrip1 || ' ' || item_descrip2 AS descrip,
575 NULL::text AS customer,
576 NULL::text AS contact,
581 (coitem_qtyord * coitem_qty_invuomratio) * (coitem_price / coitem_price_invuomratio) AS value,
583 NULL::date AS assigned,
584 NULL::date AS started,
585 NULL::date AS completed,
586 NULL::numeric AS hrs_budget,
587 NULL::numeric AS hrs_actual,
588 NULL::numeric AS hrs_balance,
589 NULL::numeric AS exp_budget,
590 NULL::numeric AS exp_actual,
591 NULL::numeric AS exp_balance,
593 NULL::text AS due_qtforegroundrole,
596 <? if exists("owner_username") ?>
597 JOIN prj ON (prj_id=cohead_prj_id
598 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
600 JOIN coitem ON (coitem_cohead_id = cohead_id)
601 JOIN uom ON (coitem_qty_uom_id = uom_id)
602 JOIN itemsite ON (coitem_itemsite_id = itemsite_id)
603 JOIN item ON (itemsite_item_id = item_id)
604 WHERE (cohead_prj_id = <? value("prj_id") ?>)
608 SELECT cohead_id AS id,
610 cohead_number::text AS subtype,
612 <? value("sos") ?> AS section_qtdisplayrole,
613 <? value("total") ?> AS name,
615 NULL::text AS project_type,
617 NULL::text AS descrip,
618 NULL::text AS customer,
619 NULL::text AS contact,
624 SUM((coitem_qtyord * coitem_qty_invuomratio) * (coitem_price / coitem_price_invuomratio)) AS value,
626 NULL::date AS assigned,
627 NULL::date AS started,
628 NULL::date AS completed,
629 NULL::numeric AS hrs_budget,
630 NULL::numeric AS hrs_actual,
631 NULL::numeric AS hrs_balance,
632 NULL::numeric AS exp_budget,
633 NULL::numeric AS exp_actual,
634 NULL::numeric AS exp_balance,
636 NULL::text AS due_qtforegroundrole,
639 <? if exists("owner_username") ?>
640 JOIN prj ON (prj_id=cohead_prj_id
641 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
643 JOIN coitem ON (coitem_cohead_id = cohead_id)
644 WHERE (cohead_prj_id = <? value("prj_id") ?>)
645 GROUP BY cohead_id, cohead_number
651 MAX(cohead_number::text) AS subtype,
653 <? value("sos") ?> AS section_qtdisplayrole,
654 <? value("total") ?> || ' ' || <? value("sos") ?> AS name,
656 NULL::text AS project_type,
658 NULL::text AS descrip,
659 NULL::text AS customer,
660 NULL::text AS contact,
665 SUM((coitem_qtyord * coitem_qty_invuomratio) * (coitem_price / coitem_price_invuomratio)) AS value,
667 NULL::date AS assigned,
668 NULL::date AS started,
669 NULL::date AS completed,
670 NULL::numeric AS hrs_budget,
671 NULL::numeric AS hrs_actual,
672 NULL::numeric AS hrs_balance,
673 NULL::numeric AS exp_budget,
674 NULL::numeric AS exp_actual,
675 NULL::numeric AS exp_balance,
677 NULL::text AS due_qtforegroundrole,
680 <? if exists("owner_username") ?>
681 JOIN prj ON (prj_id=cohead_prj_id
682 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
684 JOIN coitem ON (coitem_cohead_id = cohead_id)
685 WHERE (cohead_prj_id = <? value("prj_id") ?>)
689 ------ INVOICES -------
690 SELECT DISTINCT -1 AS id,
694 <? value("invoices") ?> AS section_qtdisplayrole,
695 <? value("invoices") ?> AS name,
696 NULL::text AS status,
697 NULL::text AS project_type,
699 NULL::text AS descrip,
700 NULL::text AS customer,
701 NULL::text AS contact,
704 NULL::numeric AS qty,
706 NULL::numeric AS value,
708 NULL::date AS assigned,
709 NULL::date AS started,
710 NULL::date AS completed,
711 NULL::numeric AS hrs_budget,
712 NULL::numeric AS hrs_actual,
713 NULL::numeric AS hrs_balance,
714 NULL::numeric AS exp_budget,
715 NULL::numeric AS exp_actual,
716 NULL::numeric AS exp_balance,
718 NULL::text AS due_qtforegroundrole,
721 <? if exists("owner_username") ?>
722 JOIN prj ON (prj_id=invchead_prj_id
723 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
725 WHERE (invchead_prj_id = <? value("prj_id") ?>)
729 SELECT invchead_id AS id,
731 invchead_invcnumber::text AS subtype,
733 <? value("invoices") ?> AS section_qtdisplayrole,
734 invchead_invcnumber::text AS name,
735 CASE WHEN (invchead_posted) THEN
736 <? value("posted") ?>
737 ELSE <? value("unposted") ?>
739 NULL::text AS project_type,
741 shipto_name AS descrip,
742 cust_name AS customer,
743 NULL::text AS contact,
750 NULL::date AS assigned,
751 NULL::date AS started,
752 NULL::date AS completed,
753 NULL::numeric AS hrs_budget,
754 NULL::numeric AS hrs_actual,
755 NULL::numeric AS hrs_balance,
756 NULL::numeric AS exp_budget,
757 NULL::numeric AS exp_actual,
758 NULL::numeric AS exp_balance,
760 NULL::text AS due_qtforegroundrole,
763 JOIN custinfo ON (cust_id=invchead_cust_id)
764 LEFT OUTER JOIN shiptoinfo ON (invchead_shipto_id = shipto_id)
765 <? if exists("owner_username") ?>
766 JOIN prj ON (prj_id=invchead_prj_id
767 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
769 JOIN invcitem ON (invcitem_invchead_id = invchead_id)
770 WHERE (invchead_prj_id = <? value("prj_id") ?>)
771 GROUP BY invchead_id, invchead_invcnumber, shipto_num, shipto_name, invchead_freight, invchead_misc_amount, invchead_posted, cust_name
775 SELECT invcitem_id AS id,
777 invchead_invcnumber::text AS subtype,
779 <? value("invoices") ?> AS section_qtdisplayrole,
780 invcitem_linenumber::text AS name,
781 CASE WHEN (invchead_posted) THEN
782 <? value("posted") ?>
783 ELSE <? value("unposted") ?>
785 NULL::text AS project_type,
786 COALESCE(item_number,invcitem_number) AS item,
787 COALESCE(item_descrip1 || ' ' || item_descrip2,invcitem_descrip) AS descrip,
788 NULL::text AS customer,
789 NULL::text AS contact,
792 invcitem_billed AS qty,
794 (invcitem_billed * invcitem_qty_invuomratio) * (invcitem_price / invcitem_price_invuomratio) AS value,
796 NULL::date AS assigned,
797 NULL::date AS started,
798 NULL::date AS completed,
799 NULL::numeric AS hrs_budget,
800 NULL::numeric AS hrs_actual,
801 NULL::numeric AS hrs_balance,
802 NULL::numeric AS exp_budget,
803 NULL::numeric AS exp_actual,
804 NULL::numeric AS exp_balance,
806 NULL::text AS due_qtforegroundrole,
809 <? if exists("owner_username") ?>
810 JOIN prj ON (prj_id=invchead_prj_id
811 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
813 JOIN invcitem ON (invcitem_invchead_id = invchead_id)
814 LEFT OUTER JOIN item ON (invcitem_item_id = item_id)
815 LEFT OUTER JOIN uom ON (invcitem_qty_uom_id = uom_id)
816 WHERE (invchead_prj_id = <? value("prj_id") ?>)
820 SELECT invchead_id AS id,
822 invchead_invcnumber::text AS subtype,
824 <? value("invoices") ?> AS section_qtdisplayrole,
825 <? value("total") ?> AS name,
827 NULL::text AS project_type,
829 NULL::text AS descrip,
830 NULL::text AS customer,
831 NULL::text AS contact,
836 SUM((invcitem_billed * invcitem_qty_invuomratio) * (invcitem_price / invcitem_price_invuomratio)) AS value,
838 NULL::date AS assigned,
839 NULL::date AS started,
840 NULL::date AS completed,
841 NULL::numeric AS hrs_budget,
842 NULL::numeric AS hrs_actual,
843 NULL::numeric AS hrs_balance,
844 NULL::numeric AS exp_budget,
845 NULL::numeric AS exp_actual,
846 NULL::numeric AS exp_balance,
848 NULL::text AS due_qtforegroundrole,
851 <? if exists("owner_username") ?>
852 JOIN prj ON (prj_id=invchead_prj_id
853 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
855 JOIN invcitem ON (invcitem_invchead_id = invchead_id)
856 WHERE (invchead_prj_id = <? value("prj_id") ?>)
857 GROUP BY invchead_id, invchead_invcnumber
863 MAX(invchead_invcnumber::text) AS subtype,
865 <? value("invoices") ?> AS section_qtdisplayrole,
866 <? value("total") ?> || ' ' || <? value("invoices") ?> AS name,
868 NULL::text AS project_type,
870 NULL::text AS descrip,
871 NULL::text AS customer,
872 NULL::text AS contact,
877 SUM((invcitem_billed * invcitem_qty_invuomratio) * (invcitem_price / invcitem_price_invuomratio)) AS value,
879 NULL::date AS assigned,
880 NULL::date AS started,
881 NULL::date AS completed,
882 NULL::numeric AS hrs_budget,
883 NULL::numeric AS hrs_actual,
884 NULL::numeric AS hrs_balance,
885 NULL::numeric AS exp_budget,
886 NULL::numeric AS exp_actual,
887 NULL::numeric AS exp_balance,
889 NULL::text AS due_qtforegroundrole,
892 <? if exists("owner_username") ?>
893 JOIN prj ON (prj_id=invchead_prj_id
894 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
896 JOIN invcitem ON (invcitem_invchead_id = invchead_id)
897 WHERE (invchead_prj_id = <? value("prj_id") ?>)
902 <? if exists("showWo") ?>
905 ------ WORK ORDERS -------
906 SELECT DISTINCT -1 AS id,
910 <? value("wos") ?> AS section_qtdisplayrole,
911 <? value("wos") ?> AS name,
912 NULL::text AS status,
913 NULL::text AS project_type,
915 NULL::text AS descrip,
916 NULL::text AS customer,
917 NULL::text AS contact,
920 NULL::numeric AS qty,
922 NULL::numeric AS value,
924 NULL::date AS assigned,
925 NULL::date AS started,
926 NULL::date AS completed,
927 NULL::numeric AS hrs_budget,
928 NULL::numeric AS hrs_actual,
929 NULL::numeric AS hrs_balance,
930 NULL::numeric AS exp_budget,
931 NULL::numeric AS exp_actual,
932 NULL::numeric AS exp_balance,
934 NULL::text AS due_qtforegroundrole,
937 <? if exists("owner_username") ?>
938 JOIN prj ON (prj_id=wo_prj_id
939 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
941 WHERE (wo_prj_id = <? value("prj_id") ?>)
947 formatWoNumber(wo_id) AS subtype,
949 <? value("wos") ?> AS section_qtdisplayrole,
950 formatWoNumber(wo_id) AS name,
951 CASE WHEN (wo_status = 'O') THEN
953 WHEN (wo_status = 'E') THEN
954 <? value("exploded") ?>
955 WHEN (wo_status = 'R') THEN
956 <? value("released") ?>
957 WHEN (wo_status = 'I') THEN
958 <? value("inprocess") ?>
959 WHEN (wo_status = 'C') THEN
960 <? value("closed") ?>
962 NULL::text AS project_type,
964 item_descrip1 || ' ' || item_descrip2 AS descrip,
965 NULL::text AS customer,
966 NULL::text AS contact,
971 wo_postedvalue AS value,
973 NULL::date AS assigned,
974 NULL::date AS started,
975 NULL::date AS completed,
976 NULL::numeric AS hrs_budget,
977 NULL::numeric AS hrs_actual,
978 NULL::numeric AS hrs_balance,
979 NULL::numeric AS exp_budget,
980 NULL::numeric AS exp_actual,
981 NULL::numeric AS exp_balance,
983 NULL::text AS due_qtforegroundrole,
986 <? if exists("owner_username") ?>
987 JOIN prj ON (prj_id=wo_prj_id
988 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
990 JOIN itemsite ON (itemsite_id=wo_itemsite_id)
991 JOIN item ON (itemsite_item_id=item_id)
992 JOIN uom ON (item_inv_uom_id=uom_id)
993 WHERE (wo_prj_id = <? value("prj_id") ?>)
999 MAX(formatWoNumber(wo_id)) AS subtype,
1001 <? value("wos") ?> AS section_qtdisplayrole,
1002 <? value("total") ?> || ' ' || <? value("wos") ?> AS name,
1004 NULL::text AS project_type,
1006 NULL::text AS descrip,
1007 NULL::text AS customer,
1008 NULL::text AS contact,
1010 NULL::text AS state,
1013 SUM(wo_postedvalue) AS value,
1015 NULL::date AS assigned,
1016 NULL::date AS started,
1017 NULL::date AS completed,
1018 NULL::numeric AS hrs_budget,
1019 NULL::numeric AS hrs_actual,
1020 NULL::numeric AS hrs_balance,
1021 NULL::numeric AS exp_budget,
1022 NULL::numeric AS exp_actual,
1023 NULL::numeric AS exp_balance,
1025 NULL::text AS due_qtforegroundrole,
1028 <? if exists("owner_username") ?>
1029 JOIN prj ON (prj_id=wo_prj_id
1030 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
1032 WHERE (wo_prj_id = <? value("prj_id") ?>)
1037 <? if exists("showPo") ?>
1040 ------ PURCHASE REQUESTS ------
1041 SELECT DISTINCT -1 AS id,
1045 <? value("prs") ?> AS section_qtdisplayrole,
1046 <? value("prs") ?> AS name,
1047 NULL::text AS status,
1048 NULL::text AS project_type,
1050 NULL::text AS descrip,
1051 NULL::text AS customer,
1052 NULL::text AS contact,
1054 NULL::text AS state,
1055 NULL::numeric AS qty,
1057 NULL::numeric AS value,
1059 NULL::date AS assigned,
1060 NULL::date AS started,
1061 NULL::date AS completed,
1062 NULL::numeric AS hrs_budget,
1063 NULL::numeric AS hrs_actual,
1064 NULL::numeric AS hrs_balance,
1065 NULL::numeric AS exp_budget,
1066 NULL::numeric AS exp_actual,
1067 NULL::numeric AS exp_balance,
1069 NULL::text AS due_qtforegroundrole,
1072 <? if exists("owner_username") ?>
1073 JOIN prj ON (prj_id=pr_prj_id
1074 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
1076 WHERE (pr_prj_id = <? value("prj_id") ?>)
1082 pr_number::text || '-' || pr_subnumber::text AS subtype,
1084 <? value("prs") ?> AS section_qtdisplayrole,
1085 pr_number::text || '-' || pr_subnumber::text AS name,
1086 <? value("open") ?> AS status,
1087 NULL::text AS project_type,
1088 item_number AS item,
1089 (item_descrip1 || ' ' || item_descrip2) AS descrip,
1090 NULL::text AS customer,
1091 NULL::text AS contact,
1093 NULL::text AS state,
1096 stdcost(item_id) * pr_qtyreq AS value,
1098 NULL::date AS assigned,
1099 NULL::date AS started,
1100 NULL::date AS completed,
1101 NULL::numeric AS hrs_budget,
1102 NULL::numeric AS hrs_actual,
1103 NULL::numeric AS hrs_balance,
1104 NULL::numeric AS exp_budget,
1105 NULL::numeric AS exp_actual,
1106 NULL::numeric AS exp_balance,
1108 NULL::text AS due_qtforegroundrole,
1111 <? if exists("owner_username") ?>
1112 JOIN prj ON (prj_id=pr_prj_id
1113 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
1115 JOIN itemsite ON (itemsite_id = pr_itemsite_id)
1116 JOIN item ON (itemsite_item_id = item_id)
1117 JOIN uom ON (item_inv_uom_id = uom_id)
1118 WHERE (pr_prj_id=<? value("prj_id") ?>)
1124 MAX(pr_number::text || '-' || pr_subnumber::text) AS subtype,
1126 <? value("prs") ?> AS section_qtdisplayrole,
1127 <? value("total") ?> || ' ' || <? value("prs") ?> AS name,
1129 NULL::text AS project_type,
1131 NULL::text AS descrip,
1132 NULL::text AS customer,
1133 NULL::text AS contact,
1135 NULL::text AS state,
1138 SUM(stdcost(item_id) * pr_qtyreq) AS value,
1140 NULL::date AS assigned,
1141 NULL::date AS started,
1142 NULL::date AS completed,
1143 NULL::numeric AS hrs_budget,
1144 NULL::numeric AS hrs_actual,
1145 NULL::numeric AS hrs_balance,
1146 NULL::numeric AS exp_budget,
1147 NULL::numeric AS exp_actual,
1148 NULL::numeric AS exp_balance,
1150 NULL::text AS due_qtforegroundrole,
1153 <? if exists("owner_username") ?>
1154 JOIN prj ON (prj_id=pr_prj_id
1155 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
1157 JOIN itemsite ON (itemsite_id = pr_itemsite_id)
1158 JOIN item ON (itemsite_item_id = item_id)
1159 WHERE (pr_prj_id = <? value("prj_id") ?>)
1163 ------ PURCHASE ORDERS ------
1164 SELECT DISTINCT -1 AS id,
1168 <? value("pos") ?> AS section_qtdisplayrole,
1169 <? value("pos") ?> AS name,
1170 NULL::text AS status,
1171 NULL::text AS project_type,
1173 NULL::text AS descrip,
1174 NULL::text AS customer,
1175 NULL::text AS contact,
1177 NULL::text AS state,
1178 NULL::numeric AS qty,
1180 NULL::numeric AS value,
1182 NULL::date AS assigned,
1183 NULL::date AS started,
1184 NULL::date AS completed,
1185 NULL::numeric AS hrs_budget,
1186 NULL::numeric AS hrs_actual,
1187 NULL::numeric AS hrs_balance,
1188 NULL::numeric AS exp_budget,
1189 NULL::numeric AS exp_actual,
1190 NULL::numeric AS exp_balance,
1192 NULL::text AS due_qtforegroundrole,
1195 <? if exists("owner_username") ?>
1196 JOIN prj ON (prj_id=poitem_prj_id
1197 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
1199 WHERE (poitem_prj_id = <? value("prj_id") ?>)
1203 SELECT pohead_id AS id,
1205 pohead_number::text AS subtype,
1207 <? value("pos") ?> AS section_qtdisplayrole,
1208 pohead_number::text AS name,
1209 CASE WHEN (pohead_status = 'U') THEN
1210 <? value("unreleased") ?>
1211 WHEN (pohead_status = 'O') THEN
1213 WHEN (pohead_status = 'C') THEN
1214 <? value("closed") ?>
1216 NULL::text AS project_type,
1218 NULL::text AS descrip,
1219 NULL::text AS customer,
1220 NULL::text AS contact,
1222 NULL::text AS state,
1227 NULL::date AS assigned,
1228 NULL::date AS started,
1229 NULL::date AS completed,
1230 NULL::numeric AS hrs_budget,
1231 NULL::numeric AS hrs_actual,
1232 NULL::numeric AS hrs_balance,
1233 NULL::numeric AS exp_budget,
1234 NULL::numeric AS exp_actual,
1235 NULL::numeric AS exp_balance,
1237 NULL::text AS due_qtforegroundrole,
1240 JOIN poitem ON (poitem_pohead_id = pohead_id)
1241 <? if exists("owner_username") ?>
1242 JOIN prj ON (prj_id=poitem_prj_id
1243 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
1245 WHERE (poitem_prj_id = <? value("prj_id") ?>)
1246 GROUP BY pohead_id, pohead_number, pohead_freight, pohead_status
1250 SELECT poitem_id AS id,
1252 pohead_number::text AS subtype,
1254 <? value("pos") ?> AS section_qtdisplayrole,
1255 poitem_linenumber::text AS name,
1256 CASE WHEN (poitem_status = 'U') THEN
1257 <? value("unreleased") ?>
1258 WHEN (poitem_status = 'O') THEN
1260 WHEN (poitem_status = 'C') THEN
1261 <? value("closed") ?>
1263 NULL::text AS project_type,
1264 COALESCE(item_number,poitem_vend_item_number) AS item,
1265 COALESCE((item_descrip1 || ' ' || item_descrip2),poitem_vend_item_descrip) AS descrip,
1266 NULL::text AS customer,
1267 NULL::text AS contact,
1269 NULL::text AS state,
1271 poitem_vend_uom AS uom,
1272 (poitem_qty_ordered * poitem_unitprice) AS value,
1274 NULL::date AS assigned,
1275 NULL::date AS started,
1276 NULL::date AS completed,
1277 NULL::numeric AS hrs_budget,
1278 NULL::numeric AS hrs_actual,
1279 NULL::numeric AS hrs_balance,
1280 NULL::numeric AS exp_budget,
1281 NULL::numeric AS exp_actual,
1282 NULL::numeric AS exp_balance,
1284 NULL::text AS due_qtforegroundrole,
1287 JOIN poitem ON (poitem_pohead_id = pohead_id)
1288 <? if exists("owner_username") ?>
1289 JOIN prj ON (prj_id=poitem_prj_id
1290 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
1292 LEFT OUTER JOIN itemsite ON (poitem_itemsite_id=itemsite_id)
1293 LEFT OUTER JOIN item ON (itemsite_item_id = item_id)
1294 WHERE (poitem_prj_id = <? value("prj_id") ?>)
1298 SELECT pohead_id AS id,
1300 pohead_number::text AS subtype,
1302 <? value("pos") ?> AS section_qtdisplayrole,
1303 <? value("total") ?> AS name,
1305 NULL::text AS project_type,
1307 NULL::text AS descrip,
1308 NULL::text AS customer,
1309 NULL::text AS contact,
1311 NULL::text AS state,
1314 SUM(poitem_qty_ordered * poitem_unitprice) AS value,
1316 NULL::date AS assigned,
1317 NULL::date AS started,
1318 NULL::date AS completed,
1319 NULL::numeric AS hrs_budget,
1320 NULL::numeric AS hrs_actual,
1321 NULL::numeric AS hrs_balance,
1322 NULL::numeric AS exp_budget,
1323 NULL::numeric AS exp_actual,
1324 NULL::numeric AS exp_balance,
1326 NULL::text AS due_qtforegroundrole,
1329 JOIN poitem ON (poitem_pohead_id = pohead_id)
1330 <? if exists("owner_username") ?>
1331 JOIN prj ON (prj_id=poitem_prj_id
1332 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
1334 WHERE (poitem_prj_id = <? value("prj_id") ?>)
1335 GROUP BY pohead_id, pohead_number
1341 MAX(pohead_number::text) AS subtype,
1343 <? value("sos") ?> AS section_qtdisplayrole,
1344 <? value("total") ?> || ' ' || <? value("pos") ?> AS name,
1346 NULL::text AS project_type,
1348 NULL::text AS descrip,
1349 NULL::text AS customer,
1350 NULL::text AS contact,
1352 NULL::text AS state,
1355 SUM(poitem_qty_ordered * poitem_unitprice) AS value,
1357 NULL::date AS assigned,
1358 NULL::date AS started,
1359 NULL::date AS completed,
1360 NULL::numeric AS hrs_budget,
1361 NULL::numeric AS hrs_actual,
1362 NULL::numeric AS hrs_balance,
1363 NULL::numeric AS exp_budget,
1364 NULL::numeric AS exp_actual,
1365 NULL::numeric AS exp_balance,
1367 NULL::text AS due_qtforegroundrole,
1370 JOIN poitem ON (poitem_pohead_id = pohead_id)
1371 <? if exists("owner_username") ?>
1372 JOIN prj ON (prj_id=poitem_prj_id
1373 AND ((prj_owner_username=<? value("owner_username") ?>) OR (prj_username=<? value("owner_username") ?>)))
1375 WHERE (poitem_prj_id = <? value("prj_id") ?>)
1380 ORDER BY section, subtype, type, id;