/**
* Информацию по документообороту СБР для админки.
*
* @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;
}
/**
* Возвращает последний опубликованный документ заданного типа.
*
* @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];
}