2 -- Name: detail_nohierarchy
3 -- Notes: Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
4 -- See www.xtuple.com/CPAL for the full text of the software license.
6 -- Non-hierarchical view to support CSV export from Project List screen
8 SELECT prj_id AS id, 1 AS altId,
9 prj.*, crmacct_name, cntct_name,
10 addr_city, addr_state,
11 prjtype_descr AS project_type,
12 CASE WHEN (prj_status='P') THEN <? value("planning") ?>
13 WHEN (prj_status='O') THEN <? value("open") ?>
14 WHEN (prj_status='C') THEN <? value("complete") ?>
15 ELSE <? value("undefined") ?> END AS project_status
16 <? foreach("char_id_text_list") ?>
17 , charass_alias<? literal("char_id_text_list") ?>.charass_value AS char<? literal("char_id_text_list") ?>
19 <? foreach("char_id_list_list") ?>
20 , charass_alias<? literal("char_id_list_list") ?>.charass_value AS char<? literal("char_id_list_list") ?>
22 <? foreach("char_id_date_list") ?>
23 , charass_alias<? literal("char_id_date_list") ?>.charass_value::date AS char<? literal("char_id_date_list") ?>
26 LEFT JOIN prjtype ON (prj_prjtype_id=prjtype_id)
27 LEFT JOIN crmacct ON (prj_crmacct_id=crmacct_id)
28 LEFT JOIN cntct ON (crmacct_cntct_id_1=cntct_id)
29 LEFT JOIN addr ON (cntct_addr_id=addr_id)
30 <? foreach("char_id_text_list") ?>
31 LEFT OUTER JOIN charass charass_alias<? literal("char_id_text_list") ?> ON ((charass_alias<? literal("char_id_text_list") ?>.charass_target_type='PROJ')
32 AND (charass_alias<? literal("char_id_text_list") ?>.charass_target_id=prj_id)
33 AND (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=<? value("char_id_text_list") ?>))
34 LEFT OUTER JOIN char char_alias<? literal("char_id_text_list") ?> ON (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=char_alias<? literal("char_id_text_list") ?>.char_id)
36 <? foreach("char_id_list_list") ?>
37 LEFT OUTER JOIN charass charass_alias<? literal("char_id_list_list") ?> ON ((charass_alias<? literal("char_id_list_list") ?>.charass_target_type='PROJ')
38 AND (charass_alias<? literal("char_id_list_list") ?>.charass_target_id=prj_id)
39 AND (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=<? value("char_id_list_list") ?>))
40 LEFT OUTER JOIN char char_alias<? literal("char_id_list_list") ?> ON (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=char_alias<? literal("char_id_list_list") ?>.char_id)
42 <? foreach("char_id_date_list") ?>
43 LEFT OUTER JOIN charass charass_alias<? literal("char_id_date_list") ?> ON ((charass_alias<? literal("char_id_date_list") ?>.charass_target_type='PROJ')
44 AND (charass_alias<? literal("char_id_date_list") ?>.charass_target_id=prj_id)
45 AND (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=<? value("char_id_date_list") ?>))
46 LEFT OUTER JOIN char char_alias<? literal("char_id_date_list") ?> ON (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=char_alias<? literal("char_id_date_list") ?>.char_id)
49 <? if exists("search_pattern") ?>
50 AND ( (prj_number ~* <? value("search_pattern") ?>)
51 OR (prj_name ~* <? value("search_pattern") ?>)
52 OR (prj_descrip ~* <? value("search_pattern") ?>) )
54 <? if exists("username") ?>
55 AND (prj_username=<? value("username") ?> OR prj_owner_username=<? value("username") ?>)
57 <? if exists("assigned_username") ?>
58 AND (prj_username=<? value("assigned_username") ?>)
59 <? elseif exists("assigned_usr_pattern") ?>
60 AND (prj_username ~ <? value("assigned_usr_pattern") ?>)
62 <? if exists("owner_username") ?>
63 AND (prj_owner_username=<? value("owner_username") ?>)
64 <? elseif exists("owner_usr_pattern") ?>
65 AND (prj_owner_username ~ <? value("owner_usr_pattern") ?>)
67 <? if exists("crmacct_id") ?>
68 AND (prj_crmacct_id=<? value("crmacct_id") ?>)
70 <? if exists("cntct_id") ?>
71 AND (prj_cntct_id=<? value("cntct_id") ?>)
73 <? if exists("prjtype_id") ?>
74 AND (prj_prjtype_id=<? value("prjtype_id") ?>)
76 <? if exists("prjstatus") ?>
77 AND (prj_status=<? value("prjstatus") ?>)
78 <? elseif not exists("showComplete") ?>
79 AND (prj_status <> 'C')
81 <? if exists("startStartDate") ?>
82 AND (prj_start_date >= <? value("startStartDate") ?>)
84 <? if exists("startEndDate") ?>
85 AND (prj_start_date <= <? value("startEndDate") ?>)
87 <? if exists("dueStartDate") ?>
88 AND (prj_due_date >= <? value("dueStartDate") ?>)
90 <? if exists("dueEndDate") ?>
91 AND (prj_due_date <= <? value("dueEndDate") ?>)
93 <? if exists("assignedStartDate") ?>
94 AND (prj_assigned_date >= <? value("assignedStartDate") ?>)
96 <? if exists("assignedEndDate") ?>
97 AND (prj_assigned_date <= <? value("assignedEndDate") ?>)
99 <? if exists("completedStartDate") ?>
100 AND (prj_completed_date >= <? value("completedStartDate") ?>)
102 <? if exists("completedEndDate") ?>
103 AND (prj_completed_date <= <? value("completedEndDate") ?>)
105 <? if exists("id") ?>
106 AND (prj_id=<? value("id") ?>)
108 <? literal("charClause") ?>
109 <? if exists("prjtype_id") ?>
110 AND (prjtype_id = <? value("prjtype_id") ?>)