/** * Get the condition for Cases List * * @name getConditionCasesList * @param string $sTypeList * @param string $sUIDUserLogged * @param string $ClearSession * @param string $aAdditionalFilter * @return array */ public function getConditionCasesList($sTypeList = 'all', $sUIDUserLogged = '', $ClearSession = true, $aAdditionalFilter = null) { $c = new Criteria('workflow'); $c->clearSelectColumns(); $c->addSelectColumn(ApplicationPeer::APP_UID); $c->addSelectColumn(ApplicationPeer::APP_NUMBER); $c->addSelectColumn(ApplicationPeer::APP_UPDATE_DATE); $c->addSelectColumn(ApplicationPeer::PRO_UID); $c->addSelectColumn(ApplicationPeer::APP_INIT_USER); $c->addSelectColumn(AppDelegationPeer::DEL_PRIORITY); //$c->addSelectColumn(AppDelegationPeer::DEL_TASK_DUE_DATE); $c->addAsColumn( 'DEL_TASK_DUE_DATE', " IF (" . AppDelegationPeer::DEL_TASK_DUE_DATE . " <= NOW(), CONCAT('<span style=\'color:red\';>', " . AppDelegationPeer::DEL_TASK_DUE_DATE . ", '</span>'), " . AppDelegationPeer::DEL_TASK_DUE_DATE . ") " ); global $RBAC; //seems the PM_SUPERVISOR can delete a completed case if ($sTypeList == "completed" && $RBAC->userCanAccess('PM_SUPERVISOR') == 1) { $c->addAsColumn("DEL_LINK", "CONCAT('" . G::LoadTranslation('ID_DELETE') . "')"); } $c->addSelectColumn(AppDelegationPeer::DEL_INDEX); $c->addSelectColumn(AppDelegationPeer::TAS_UID); $c->addSelectColumn(AppDelegationPeer::DEL_INIT_DATE); $c->addSelectColumn(AppDelegationPeer::DEL_FINISH_DATE); $c->addSelectColumn(UsersPeer::USR_UID); $c->addAsColumn('APP_CURRENT_USER', "CONCAT(USERS.USR_LASTNAME, ' ', USERS.USR_FIRSTNAME)"); $c->addSelectColumn(ApplicationPeer::APP_STATUS); $c->addAsColumn('APP_TITLE', 'APP_TITLE.CON_VALUE'); $c->addAsColumn('APP_PRO_TITLE', 'PRO_TITLE.CON_VALUE'); $c->addAsColumn('APP_TAS_TITLE', 'TAS_TITLE.CON_VALUE'); //$c->addAsColumn('APP_DEL_PREVIOUS_USER', 'APP_LAST_USER.USR_USERNAME'); $c->addAsColumn( 'APP_DEL_PREVIOUS_USER', "CONCAT(APP_LAST_USER.USR_LASTNAME, ' ', APP_LAST_USER.USR_FIRSTNAME)" ); $c->addAlias("APP_TITLE", 'CONTENT'); $c->addAlias("PRO_TITLE", 'CONTENT'); $c->addAlias("TAS_TITLE", 'CONTENT'); $c->addAlias("APP_PREV_DEL", 'APP_DELEGATION'); $c->addAlias("APP_LAST_USER", 'USERS'); $c->addJoin(ApplicationPeer::APP_UID, AppDelegationPeer::APP_UID, Criteria::LEFT_JOIN); $c->addJoin(AppDelegationPeer::TAS_UID, TaskPeer::TAS_UID, Criteria::LEFT_JOIN); $appThreadConds[] = array(ApplicationPeer::APP_UID, AppThreadPeer::APP_UID); $appThreadConds[] = array(AppDelegationPeer::DEL_INDEX, AppThreadPeer::DEL_INDEX); $c->addJoinMC($appThreadConds, Criteria::LEFT_JOIN); $c->addJoin(AppDelegationPeer::USR_UID, UsersPeer::USR_UID, Criteria::LEFT_JOIN); $del = DBAdapter::getStringDelimiter(); $appTitleConds = array(); $appTitleConds[] = array(ApplicationPeer::APP_UID, 'APP_TITLE.CON_ID'); $appTitleConds[] = array('APP_TITLE.CON_CATEGORY', $del . 'APP_TITLE' . $del); $appTitleConds[] = array('APP_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($appTitleConds, Criteria::LEFT_JOIN); $proTitleConds = array(); $proTitleConds[] = array(ApplicationPeer::PRO_UID, 'PRO_TITLE.CON_ID'); $proTitleConds[] = array('PRO_TITLE.CON_CATEGORY', $del . 'PRO_TITLE' . $del); $proTitleConds[] = array('PRO_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($proTitleConds, Criteria::LEFT_JOIN); $tasTitleConds = array(); $tasTitleConds[] = array(AppDelegationPeer::TAS_UID, 'TAS_TITLE.CON_ID'); $tasTitleConds[] = array('TAS_TITLE.CON_CATEGORY', $del . 'TAS_TITLE' . $del); $tasTitleConds[] = array('TAS_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($tasTitleConds, Criteria::LEFT_JOIN); $prevConds = array(); $prevConds[] = array(ApplicationPeer::APP_UID, 'APP_PREV_DEL.APP_UID'); $prevConds[] = array('APP_PREV_DEL.DEL_INDEX', AppDelegationPeer::DEL_PREVIOUS); $c->addJoinMC($prevConds, Criteria::LEFT_JOIN); $usrConds = array(); $usrConds[] = array('APP_PREV_DEL.USR_UID', 'APP_LAST_USER.USR_UID'); $c->addJoinMC($usrConds, Criteria::LEFT_JOIN); $c->add(TaskPeer::TAS_TYPE, 'SUBPROCESS', Criteria::NOT_EQUAL); //gral, to_revise, to_reassign dont have userid in the query if ($sTypeList != 'gral' && $sTypeList != 'to_revise' && $sTypeList != 'to_reassign' && $sTypeList != 'my_started' && $sTypeList != 'sent') { $c->add(UsersPeer::USR_UID, $sUIDUserLogged); } /** * Additional filters * By Erik <*****@*****.**> */ if (isset($aAdditionalFilter) && is_array($aAdditionalFilter)) { foreach ($aAdditionalFilter as $sFilter => $sValue) { switch ($sFilter) { case 'PRO_UID': if ($sValue != "0") { $c->add(ApplicationPeer::PRO_UID, $sValue, Criteria::EQUAL); } break; case 'READ': $c->add(AppDelegationPeer::DEL_INIT_DATE, null, Criteria::ISNOTNULL); break; case 'UNREAD': $c->add(AppDelegationPeer::DEL_INIT_DATE, null, Criteria::ISNULL); break; } } } $filesList = array( //7 standard list 'to_do' => 'cases/cases_ListTodo', 'draft' => 'cases/cases_ListDraft', 'paused' => 'cases/cases_ListOnHold', 'cancelled' => 'cases/cases_ListCancelled', 'completed' => 'cases/cases_ListCompleted', 'sent' => 'cases/cases_ListSent', 'selfservice' => 'cases/cases_ListSelfService', //5 admin list 'all' => 'cases/cases_ListAll', 'to_revise' => 'cases/cases_ListToRevise', 'to_reassign' => 'cases/cases_ListAll_Reassign', 'my_started' => 'cases/cases_ListStarted', 'Alldelete' => 'cases/cases_ListAllDelete' ); switch ($sTypeList) { case 'all': $c->add( $c->getNewCriterion( AppThreadPeer::APP_THREAD_STATUS, 'OPEN')-> addOr($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'COMPLETED')-> addAnd($c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0) ) ) ); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'my_started': $oCriteria = new Criteria('workflow'); $oCriteria->addSelectColumn(AppDelayPeer::APP_UID); $oCriteria->add( $oCriteria->getNewCriterion( AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL )->addOr( $oCriteria->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, 0) ) ); //$oCriteria->add(AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL); $oDataset = AppDelayPeer::doSelectRS($oCriteria); $oDataset->setFetchmode(ResultSet::FETCHMODE_ASSOC); $oDataset->next(); $aProcesses = array(); while ($aRow = $oDataset->getRow()) { $aProcesses[] = $aRow['APP_UID']; $oDataset->next(); } $c->add($c->getNewCriterion(ApplicationPeer::APP_INIT_USER, $sUIDUserLogged)); $c->add( $c->getNewCriterion( AppThreadPeer::APP_THREAD_STATUS, 'OPEN' )->addOr( $c->getNewCriterion( ApplicationPeer::APP_STATUS, 'COMPLETED' )->addAnd( $c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0) ) ) ); $c->add($c->getNewCriterion(ApplicationPeer::APP_UID, $aProcesses, Criteria::NOT_IN)); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'to_do': $c->add(ApplicationPeer::APP_STATUS, 'TO_DO'); $c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL); $c->add(AppThreadPeer::APP_THREAD_STATUS, 'OPEN'); $c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN'); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'draft': $c->add(ApplicationPeer::APP_STATUS, 'DRAFT'); $c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL); $c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN'); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'paused': $appDelayConds[] = array(ApplicationPeer::APP_UID, AppDelayPeer::APP_UID); $appDelayConds[] = array(AppDelegationPeer::DEL_INDEX, AppDelayPeer::APP_DEL_INDEX); $c->addJoinMC($appDelayConds, Criteria::LEFT_JOIN); $c->add(AppDelayPeer::APP_DELAY_UID, null, Criteria::ISNOTNULL); $c->add(AppDelayPeer::APP_TYPE, array("REASSIGN", "ADHOC", "CANCEL"), Criteria::NOT_IN); $c->add( $c->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL)-> addOr($c->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, 0)) ); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'cancelled': $c->add( $c->getNewCriterion(AppThreadPeer::APP_THREAD_STATUS, 'CLOSED')-> addAnd($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'CANCELLED')) ); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'completed': $c->add(ApplicationPeer::APP_STATUS, 'COMPLETED'); $c->add(AppDelegationPeer::DEL_PREVIOUS, '0', Criteria::NOT_EQUAL); //$c->addAsColumn('DEL_FINISH_DATE', 'max('.AppDelegationPeer::DEL_FINISH_DATE.')'); $c->addGroupByColumn(ApplicationPeer::APP_UID); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'gral': $c->add( $c->getNewCriterion(AppThreadPeer::APP_THREAD_STATUS, 'OPEN')-> addOr($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'COMPLETED')-> addAnd($c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0))) ); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); $params = array(); $sSql = BasePeer::createSelectSql($c, $params); break; case 'to_revise': $oCriteria = new Criteria('workflow'); $oCriteria->add(ProcessUserPeer::USR_UID, $sUIDUserLogged); $oCriteria->add(ProcessUserPeer::PU_TYPE, 'SUPERVISOR'); $oDataset = ProcessUserPeer::doSelectRS($oCriteria); $oDataset->setFetchmode(ResultSet::FETCHMODE_ASSOC); $oDataset->next(); $aProcesses = array(); while ($aRow = $oDataset->getRow()) { $aProcesses[] = $aRow['PRO_UID']; $oDataset->next(); } $c->add(ApplicationPeer::PRO_UID, $aProcesses, Criteria::IN); $c->add(ApplicationPeer::APP_STATUS, 'TO_DO'); $c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL); $c->add(AppThreadPeer::APP_THREAD_STATUS, 'OPEN'); $c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN'); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'selfservice': //get the valid selfservice tasks for this user $oCase = new Cases(); $tasks = $oCase->getSelfServiceTasks($_SESSION['USER_LOGGED']); $aTasks = array(); foreach ($tasks as $key => $val) { if (strlen(trim($val['uid'])) > 10) { $aTasks[] = $val['uid']; } } $c = new Criteria('workflow'); $c->clearSelectColumns(); $c->addSelectColumn(ApplicationPeer::APP_UID); $c->addSelectColumn(ApplicationPeer::APP_NUMBER); $c->addSelectColumn(ApplicationPeer::APP_UPDATE_DATE); $c->addSelectColumn(ApplicationPeer::PRO_UID); $c->addSelectColumn(ApplicationPeer::APP_INIT_USER); $c->addSelectColumn(AppDelegationPeer::DEL_PRIORITY); $c->addAsColumn( 'DEL_TASK_DUE_DATE', " IF (" . AppDelegationPeer::DEL_TASK_DUE_DATE . " <= NOW(), CONCAT('<span style=\'color:red\';>', " . AppDelegationPeer::DEL_TASK_DUE_DATE . ", '</span>'), " . AppDelegationPeer::DEL_TASK_DUE_DATE . ") " ); $c->addSelectColumn(AppDelegationPeer::DEL_INDEX); $c->addSelectColumn(AppDelegationPeer::TAS_UID); $c->addSelectColumn(AppDelegationPeer::DEL_INIT_DATE); $c->addSelectColumn(AppDelegationPeer::DEL_FINISH_DATE); $c->addSelectColumn(UsersPeer::USR_UID); $c->addAsColumn('APP_CURRENT_USER', "CONCAT(USERS.USR_LASTNAME, ' ', USERS.USR_FIRSTNAME)"); $c->addSelectColumn(ApplicationPeer::APP_STATUS); $c->addAsColumn('APP_TITLE', 'APP_TITLE.CON_VALUE'); $c->addAsColumn('APP_PRO_TITLE', 'PRO_TITLE.CON_VALUE'); $c->addAsColumn('APP_TAS_TITLE', 'TAS_TITLE.CON_VALUE'); $c->addAlias("APP_TITLE", 'CONTENT'); $c->addAlias("PRO_TITLE", 'CONTENT'); $c->addAlias("TAS_TITLE", 'CONTENT'); $c->addJoin(ApplicationPeer::APP_UID, AppDelegationPeer::APP_UID, Criteria::LEFT_JOIN); $c->addJoin(AppDelegationPeer::TAS_UID, TaskPeer::TAS_UID, Criteria::LEFT_JOIN); $appThreadConds[] = array(ApplicationPeer::APP_UID, AppThreadPeer::APP_UID); $appThreadConds[] = array(AppDelegationPeer::DEL_INDEX, AppThreadPeer::DEL_INDEX); $c->addJoinMC($appThreadConds, Criteria::LEFT_JOIN); $c->addJoin(AppDelegationPeer::USR_UID, UsersPeer::USR_UID, Criteria::LEFT_JOIN); $del = DBAdapter::getStringDelimiter(); $appTitleConds = array(); $appTitleConds[] = array(ApplicationPeer::APP_UID, 'APP_TITLE.CON_ID'); $appTitleConds[] = array('APP_TITLE.CON_CATEGORY', $del . 'APP_TITLE' . $del); $appTitleConds[] = array('APP_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($appTitleConds, Criteria::LEFT_JOIN); // $proTitleConds = array(); $proTitleConds[] = array(ApplicationPeer::PRO_UID, 'PRO_TITLE.CON_ID'); $proTitleConds[] = array('PRO_TITLE.CON_CATEGORY', $del . 'PRO_TITLE' . $del); $proTitleConds[] = array('PRO_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($proTitleConds, Criteria::LEFT_JOIN); // $tasTitleConds = array(); $tasTitleConds[] = array(AppDelegationPeer::TAS_UID, 'TAS_TITLE.CON_ID'); $tasTitleConds[] = array('TAS_TITLE.CON_CATEGORY', $del . 'TAS_TITLE' . $del); $tasTitleConds[] = array('TAS_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($tasTitleConds, Criteria::LEFT_JOIN); $c->add(AppDelegationPeer::USR_UID, ''); $c->add(AppDelegationPeer::TAS_UID, $aTasks, Criteria::IN); break; case 'to_reassign': $c->add( $c->getNewCriterion(ApplicationPeer::APP_STATUS, 'TO_DO')-> addOr($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'DRAFT')) ); $c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL); $c->add(AppThreadPeer::APP_THREAD_STATUS, 'OPEN'); $c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN'); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'sent': $oCriteria = new Criteria('workflow'); $oCriteria->addSelectColumn(AppDelayPeer::APP_UID); $oCriteria->add( $oCriteria->getNewCriterion( AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL )->addOr( $oCriteria->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, 0) ) ); $oDataset = AppDelayPeer::doSelectRS($oCriteria); $oDataset->setFetchmode(ResultSet::FETCHMODE_ASSOC); $oDataset->next(); $aProcesses = array(); while ($aRow = $oDataset->getRow()) { $aProcesses[] = $aRow['APP_UID']; $oDataset->next(); } if (isset($aAdditionalFilter) && isset($aAdditionalFilter['MINE'])) { $c->add($c->getNewCriterion(ApplicationPeer::APP_INIT_USER, $sUIDUserLogged)); } else { $c->add( $c->getNewCriterion( ApplicationPeer::APP_INIT_USER, $sUIDUserLogged )->addOr( $c->getNewCriterion( AppDelegationPeer::USR_UID, $sUIDUserLogged ) ) ); } if (isset($aAdditionalFilter) && isset($aAdditionalFilter['APP_STATUS_FILTER'])) { $c->add(ApplicationPeer::APP_STATUS, $sValue, Criteria::EQUAL); } else { $c->add(ApplicationPeer::APP_STATUS, 'DRAFT', Criteria::NOT_EQUAL); } $c->add( $c->getNewCriterion( AppDelegationPeer::DEL_THREAD_STATUS, 'CLOSED' )->addOr( $c->getNewCriterion( ApplicationPeer::APP_STATUS, 'COMPLETED' )->addAnd( $c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0) ) ) ); $c->add($c->getNewCriterion(ApplicationPeer::APP_UID, $aProcesses, Criteria::NOT_IN)); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; } //select the xmlList to show if ($sTypeList == 'gral') { if ($RBAC->userCanAccess('PM_DELETECASE') == 1) { $xmlfile = $filesList['Alldelete']; } else { $xmlfile = $filesList['all']; } } else { $xmlfile = $filesList[$sTypeList]; } if ($ClearSession) { //OPCION_1: claening the $_SESSION and updating the List.... only case List foreach ($filesList as $file) { $id = G::createUID('', $file . '.xml'); unset($_SESSION['pagedTable[' . $id . ']']); unset($_SESSION[$id]); } //OPTION_2: cleaning the $_SESSION and whole List and xmls $cur = array_keys($_SESSION); foreach ($cur as $key) { if (substr($key, 0, 11) === "pagedTable[") { unset($_SESSION[$key]); } else { $xml = G::getUIDName($key, ''); if (strpos($xml, '.xml') !== false) { unset($_SESSION[$key]); } } } } return array($c, $xmlfile); }