Esempio n. 1
0
 /**
  * Информацию по документообороту СБР для админки.
  *
  * @param int    $scheme     тип схем сделок.
  * @param array  $filter     фильтр
  * @param int    $page       номер страницы
  * @param string $dir        сортировка ASC|DESC
  * @param int    $dir_col    поле сортировки.
  * @param int    $page_count вернет всего кол-во строк.
  *
  * @return array
  */
 public function getDocsFlow($scheme = sbr::SCHEME_AGNT, $filter = null, $page = 1, $dir = 'DESC', $dir_col = 0, &$page_count = null)
 {
     $dir = $dir == 'DESC' ? 'DESC' : 'ASC';
     $limit = self::PAGE_SA_SIZE;
     $offset = ($page - 1) * $limit;
     $where = $this->_buildFilterPeriod('ss.arch_closed_time', $filter);
     $page_count = 1;
     $emp_upload_docs_cond = sbr::DOCS_TYPE_ACT | sbr::DOCS_TYPE_ARB_REP;
     // документы, после загрузки которых выводим работодателя в док-те.
     $frl_upload_docs_cond = sbr::DOCS_TYPE_ACT;
     if ($scheme) {
         if ($scheme != -1) {
             $where[] = "ss.arch_closed_time > NOW()::date - interval '6 months'";
             $scheme_cond = "AND s.scheme_type = {$scheme}";
             if ($scheme == sbr::SCHEME_PDRD || $scheme == sbr::SCHEME_PDRD2) {
                 $scheme_cond = "AND ( s.scheme_type = {$scheme} OR s.scheme_type = " . sbr::SCHEME_PDRD2 . ')';
             }
         }
         $docs_cond = 'AND su.docs_received = false AND su.is_removed = false';
     } else {
         if ($filter['archive'] == 't') {
             $where[] = "ss.arch_closed_time < NOW()::date - interval '6 months'";
         }
         if ($filter['archive'] == 'f') {
             $where[] = "ss.arch_closed_time >= NOW()::date - interval '6 months'";
         }
     }
     if ($fv = pg_escape_string(trim($filter['contract_num']))) {
         $where[] = "'СБР-'||ss.sbr_id||'-'||ss.num ILIKE '%{$fv}%'";
     }
     if ($fv = pg_escape_string(trim($filter['user']))) {
         $where[] = "(u.login ILIKE '%{$fv}%' OR u.uname ILIKE '%{$fv}%' OR u.usurname ILIKE '%{$fv}%')";
     }
     if ($fv = pg_escape_string(trim($filter['name']))) {
         $where[] = "ss.name ILIKE '%{$fv}%'";
     }
     if ($fv = round((double) str_replace(array(' ', ','), array('', '.'), $filter['act_sum']), 2)) {
         $where[] = "round(su.act_lcomm+su.act_lintr, 2) = {$fv}";
     }
     if ($fv = (int) $filter['act_sys']) {
         $where[] = "ss.act_sys = {$fv}";
     }
     if ($fv = $filter['has_docs']) {
         $where[] = "su.docs_received = '{$fv}'";
     }
     if ($fv = $filter['has_act']) {
         $where[] = '((su.uploaded_docs & ' . sbr::DOCS_TYPE_COPY_ACT . ") <> 0) = '{$fv}'";
     }
     if ($fv = $filter['has_fct']) {
         $where[] = '((su.uploaded_docs & ' . sbr::DOCS_TYPE_COPY_FACTURA . ") <> 0) = '{$fv}'";
     }
     if ($fv = $filter['has_reqv']) {
         $where[] = "COALESCE(sr.is_filled[sr.form_type], false) = '{$fv}'";
     }
     if ($fv = $filter['is_removed']) {
         $where[] = "su.is_removed = '{$fv}'";
     }
     if ($scheme == 0) {
         $where[] = ' ( ss.scheme_type <> ' . sbr::SCHEME_LC . ' ) ';
         // исключаем Аккредитив
     }
     if ($where) {
         $where = 'WHERE ' . implode(' AND ', $where);
     }
     $leftPdrd = 'WHERE sp.completed IS NOT NULL OR su.user_id IS NOT NULL';
     if ($scheme == SBR::SCHEME_PDRD || $scheme == sbr::SCHEME_PDRD2) {
         $leftPdrd = "LEFT JOIN \n                 sbr_stages_users su1 \n                   ON su1.stage_id = wss.id \n                  AND su1.user_id = wss.frl_id \n                  AND (su1.uploaded_docs & {$frl_upload_docs_cond}) <> 0 \n                  WHERE su1.user_id IS NOT NULL OR su.user_id IS NOT NULL\n                  ";
     }
     $from = "\n            FROM (\n              WITH w_sbr_stages AS (\n                SELECT ss.*, s.emp_id, s.frl_id, s.scheme_id, s.scheme_type, s.cost_sys, \n                        arb.resolved, arb.frl_percent\n                  FROM sbr s\n                INNER JOIN\n                  sbr_stages ss\n                    ON ss.sbr_id = s.id\n                LEFT JOIN sbr_stages_arbitrage arb\n                    ON arb.stage_id = ss.id\n                 WHERE s.reserved_id IS NOT NULL\n                   AND s.norisk_id IS NULL\n                       {$scheme_cond}\n              )\n              SELECT wss.*, wss.emp_id as user_id, wss.cost_sys as act_sys,\n                    wss.closed_time as arch_closed_time\n                FROM w_sbr_stages wss \n              LEFT JOIN\n                sbr_stages_payouts sp\n                  ON sp.stage_id = wss.id\n                 AND sp.user_id = wss.frl_id\n              LEFT JOIN\n                sbr_stages_users su\n                  ON su.stage_id = wss.id\n                 AND su.user_id = wss.emp_id\n                 AND (su.uploaded_docs & {$emp_upload_docs_cond}) <> 0\n              {$leftPdrd}  \n              UNION ALL\n              SELECT wss.*, wss.frl_id, sp.credit_sys,\n                    sp.requested as arch_closed_time\n                FROM w_sbr_stages wss\n              INNER JOIN\n                sbr_stages_payouts sp\n                  ON sp.stage_id = wss.id\n                 AND sp.user_id = wss.frl_id\n            ) as ss\n          INNER JOIN\n            sbr_stages_users su\n              ON su.stage_id = ss.id\n             AND su.user_id = ss.user_id\n             {$docs_cond}\n          INNER JOIN\n            users u\n              ON u.uid = ss.user_id\n          LEFT JOIN\n            sbr_reqv sr\n              ON sr.user_id = ss.user_id\n        ";
     $sql = "\n          SELECT ss.*, sr.*, ss.id as stage_id,\n                 su.uploaded_docs, su.docs_received, su.act_lcomm, su.act_lintr, su.act_lndfl, su.act_lnp, su.act_lcomm + su.act_lintr as act_sum, su.act_notnp, su.is_removed,\n                 sp.credit_sys, sp.credit_sum,\n                 u.login, u.uname, u.usurname, u.role,\n                 COALESCE(docs.publ_time, ss.arch_closed_time) as act_upload_time\n          {$from}\n          LEFT JOIN\n            sbr_stages_payouts sp\n\n              ON sp.stage_id = ss.id\n             AND sp.user_id = ss.user_id\n             \n          --LEFT JOIN sbr_docs docs ON docs.stage_id = ss.id AND docs.access_role IN (1,2) AND docs.type IN (1,8) AND docs.is_deleted = false AND docs.owner_role = 0\n          LEFT JOIN (\n                SELECT DISTINCT ON (stage_id, access_role) stage_id, d.publ_time, f.modified, d.access_role FROM sbr_docs d\n                INNER JOIN file_sbr f ON f.id = d.file_id\n                WHERE \n                is_deleted = false AND access_role IN (1,2)\n                AND owner_role = 0\n                ORDER BY stage_id, access_role, publ_time DESC\n          ) docs ON docs.stage_id = ss.id AND (docs.access_role = substring(u.role, 1, 1)::integer + 1)\n          \n          {$where}\n           ORDER BY {$this->form_cols['docsflow'][$dir_col][1][$dir]}\n           LIMIT {$limit} OFFSET {$offset}\n        ";
     /*
               LEFT JOIN (
         SELECT DISTINCT ON (stage_id) stage_id, d.publ_time, f.modified FROM sbr_docs d
         INNER JOIN sbr s ON s.id = d.sbr_id
         INNER JOIN file_sbr f ON f.id = d.file_id
         WHERE type IN (1,8) 
         --AND status = 4 
         AND is_deleted = false AND access_role IN (1,2)
         AND owner_role = 0
         ORDER BY stage_id, publ_time DESC
               ) docs ON docs.stage_id = ss.id
     */
     if ($res = pg_query(self::connect(), $sql)) {
         if ($ret = pg_fetch_all($res)) {
             $account = new account();
             foreach ($ret as &$row) {
                 if ($row['uploaded_docs']) {
                     $access_role = is_emp($row['role']) ? self::DOCS_ACCESS_EMP : self::DOCS_ACCESS_FRL;
                     if ($docs = sbr_meta::getDocs("WHERE sd.stage_id = '{$row['id']}' AND (sd.access_role & {$access_role}) = {$access_role} AND sd.is_deleted = false", null, true)) {
                         $row['uploaded_docs_a'] = array();
                         foreach ($docs as $doc) {
                             $row['uploaded_docs_a'][$doc['type']] = $doc;
                         }
                     }
                 }
                 // это потом переделать
                 $account->GetInfo($row['user_id']);
                 $row['attaches'] = $account->getAllAttach();
             }
             $sql = "SELECT COUNT(1) as cnt {$from} {$where}";
             $mem = new memBuff();
             if ($rows = $mem->getSql($err, $sql, 60)) {
                 $page_count = $rows[0]['cnt'];
             }
         }
     }
     return $ret;
 }
Esempio n. 2
0
 /**
  * Возвращает последний опубликованный документ заданного типа.
  * 
  * @param integer $type   тип документа.
  * @param integer $stage_id   ид. этапа сделки.
  * @return array   
  */
 function getLastPublishedDocByType($type, $stage_id = NULL)
 {
     $where = "WHERE sd.sbr_id = {$this->id} AND sd.is_deleted = false AND sd.status = " . self::DOCS_STATUS_PUBL;
     if ($stage_id) {
         $where .= " AND (sd.stage_id IS NULL OR sd.stage_id = {$stage_id})";
     }
     $order = 'sd.publ_time DESC';
     $docs = parent::getDocs($where, $order, false);
     return $docs[0];
 }