Issue #23223:prjtype and prjstatus filters

diff --git a/foundation-database/public/tables/metasql/projects-detail_nohierarchy.mql b/foundation-database/public/tables/metasql/projects-detail_nohierarchy.mql
index cb75d4c..7791311 100644 (file)
--- a/foundation-database/public/tables/metasql/projects-detail_nohierarchy.mql
+++ b/foundation-database/public/tables/metasql/projects-detail_nohierarchy.mql
@@ -2,17 +2,17 @@
 -- Name:  detail_nohierarchy
 -- Notes: Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
 --        See www.xtuple.com/CPAL for the full text of the software license.
---        
+--
 --        Non-hierarchical view to support CSV export from Project List screen
 
 SELECT prj_id AS id, 1 AS altId,
   prj.*, crmacct_name, cntct_name,
   addr_city, addr_state,
   prjtype_descr AS project_type,
-  CASE WHEN (prj_status='P') THEN 'Planning'
-       WHEN (prj_status='O') THEN 'In-Process'
-       WHEN (prj_status='C') THEN 'Completed'
-       ELSE 'Unknown' END AS project_status
+  CASE WHEN (prj_status='P') THEN <? value("planning") ?>
+       WHEN (prj_status='O') THEN <? value("open") ?>
+       WHEN (prj_status='C') THEN <? value("complete") ?>
+       ELSE <? value("undefined") ?> END AS project_status
 <? foreach("char_id_text_list") ?>
   , charass_alias<? literal("char_id_text_list") ?>.charass_value AS char<? literal("char_id_text_list") ?>
 <? endforeach ?>
@@ -22,25 +22,25 @@ SELECT prj_id AS id, 1 AS altId,
 <? foreach("char_id_date_list") ?>
   , charass_alias<? literal("char_id_date_list") ?>.charass_value::date AS char<? literal("char_id_date_list") ?>
 <? endforeach ?>
-FROM prj() 
+FROM prj()
 LEFT JOIN prjtype ON (prj_prjtype_id=prjtype_id)
 LEFT JOIN crmacct ON (prj_crmacct_id=crmacct_id)
 LEFT JOIN cntct ON (crmacct_cntct_id_1=cntct_id)
 LEFT JOIN addr ON (cntct_addr_id=addr_id)
 <? foreach("char_id_text_list") ?>
-  LEFT OUTER JOIN charass charass_alias<? literal("char_id_text_list") ?> ON ((charass_alias<? literal("char_id_text_list") ?>.charass_target_type='PROJ') 
+  LEFT OUTER JOIN charass charass_alias<? literal("char_id_text_list") ?> ON ((charass_alias<? literal("char_id_text_list") ?>.charass_target_type='PROJ')
                                                                     AND  (charass_alias<? literal("char_id_text_list") ?>.charass_target_id=prj_id)
                                                                     AND  (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=<? value("char_id_text_list") ?>))
   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)
 <? endforeach ?>
 <? foreach("char_id_list_list") ?>
-  LEFT OUTER JOIN charass charass_alias<? literal("char_id_list_list") ?> ON ((charass_alias<? literal("char_id_list_list") ?>.charass_target_type='PROJ') 
+  LEFT OUTER JOIN charass charass_alias<? literal("char_id_list_list") ?> ON ((charass_alias<? literal("char_id_list_list") ?>.charass_target_type='PROJ')
                                                                     AND  (charass_alias<? literal("char_id_list_list") ?>.charass_target_id=prj_id)
                                                                     AND  (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=<? value("char_id_list_list") ?>))
   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)
 <? endforeach ?>
 <? foreach("char_id_date_list") ?>
-  LEFT OUTER JOIN charass charass_alias<? literal("char_id_date_list") ?> ON ((charass_alias<? literal("char_id_date_list") ?>.charass_target_type='PROJ') 
+  LEFT OUTER JOIN charass charass_alias<? literal("char_id_date_list") ?> ON ((charass_alias<? literal("char_id_date_list") ?>.charass_target_type='PROJ')
                                                                     AND  (charass_alias<? literal("char_id_date_list") ?>.charass_target_id=prj_id)
                                                                     AND  (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=<? value("char_id_date_list") ?>))
   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)
@@ -54,15 +54,15 @@ WHERE (true)
 <? if exists("username") ?>
   AND (prj_username=<? value("username") ?> OR prj_owner_username=<? value("username") ?>)
 <? endif ?>
-<? if exists("assigned_username") ?> 
-  AND (prj_username=<? value("assigned_username") ?>) 
+<? if exists("assigned_username") ?>
+  AND (prj_username=<? value("assigned_username") ?>)
 <? elseif exists("assigned_usr_pattern") ?>
-  AND (prj_username ~ <? value("assigned_usr_pattern") ?>) 
+  AND (prj_username ~ <? value("assigned_usr_pattern") ?>)
 <? endif ?>
-<? if exists("owner_username") ?> 
-  AND (prj_owner_username=<? value("owner_username") ?>) 
+<? if exists("owner_username") ?>
+  AND (prj_owner_username=<? value("owner_username") ?>)
 <? elseif exists("owner_usr_pattern") ?>
-  AND (prj_owner_username ~ <? value("owner_usr_pattern") ?>) 
+  AND (prj_owner_username ~ <? value("owner_usr_pattern") ?>)
 <? endif ?>
 <? if exists("crmacct_id") ?>
   AND (prj_crmacct_id=<? value("crmacct_id") ?>)
@@ -70,6 +70,14 @@ WHERE (true)
 <? if exists("cntct_id") ?>
   AND (prj_cntct_id=<? value("cntct_id") ?>)
 <? endif ?>
+<? if exists("prjtype_id") ?>
+  AND (prj_prjtype_id=<? value("prjtype_id") ?>)
+<? endif ?>
+<? if exists("prjstatus") ?>
+  AND (prj_status=<? value("prjstatus") ?>)
+<? elseif not exists("showComplete") ?>
+  AND (prj_status <> 'C')
+<? endif ?>
 <? if exists("startStartDate") ?>
   AND (prj_start_date >= <? value("startStartDate") ?>)
 <? endif ?>
@@ -94,11 +102,8 @@ WHERE (true)
 <? if exists("completedEndDate") ?>
   AND (prj_completed_date <= <? value("completedEndDate") ?>)
 <? endif ?>
-<? if not exists("showComplete") ?>
-  AND (prj_status <> 'C')
-<? endif ?>
 <? if exists("id") ?>
   AND (prj_id=<? value("id") ?>)
 <? endif ?>
 <? literal("charClause") ?>
-ORDER BY prj_number;
+ORDER BY prj_number;
\ No newline at end of file