/**
* получение списка точек
*
* @param $params
*/
public static function getDots($params)
{
$db = Oracle::init();
$sql = "\n select * from " . Oracle::$prefix . "V_WEB_POS_LIST t where 1 = 1\n ";
if (!empty($params['group_id'])) {
//$sql .= ' and not exists (select 1 from '.Oracle::$prefix.'V_POS_GROUPS_ITEMS pg where pg.group_id = '.intval($params['group_id']).')';
}
if (!empty($params['POS_ID'])) {
$sql .= ' and t.POS_ID = ' . intval($params['POS_ID']);
}
if (!empty($params['ID_EMITENT'])) {
$sql .= ' and t.ID_EMITENT = ' . intval($params['ID_EMITENT']);
}
if (!empty($params['ID_TO'])) {
$sql .= " and t.ID_TO like '%" . Oracle::quote($params['ID_TO']) . "%'";
}
if (!empty($params['POS_NAME'])) {
$sql .= " and t.POS_NAME like '%" . Oracle::quote($params['POS_NAME']) . "%'";
}
if (!empty($params['OWNER'])) {
$sql .= " and t.OWNER like '%" . Oracle::quote($params['OWNER']) . "%'";
}
if (!empty($params['POS_ADDRESS'])) {
$sql .= " and t.POS_ADDRESS like '%" . Oracle::quote($params['POS_ADDRESS']) . "%'";
}
return $db->pagination($sql, $params);
}
/**
* получаем данные по клиенту
*
* @param $clientId
*/
public static function getClient($clientId)
{
if (empty($clientId)) {
return false;
}
$db = Oracle::init();
$sql = "select * from " . Oracle::$prefix . "V_WEB_CLIENTS_PROFILE where client_id = " . Oracle::quote($clientId);
$client = $db->row($sql);
return $client;
}
/**
* собираем доступные пользовалю сообщения
*
* @param array $params
* @return array
*/
public static function getList($params = [])
{
if (empty($user)) {
$user = Auth::instance()->get_user();
}
$db = Oracle::init();
$sql = "select * from " . Oracle::$prefix . "V_WEB_NOTIFICATION where manager_id = " . $user['MANAGER_ID'];
if (!empty($params['not_read'])) {
$sql .= " and status = " . self::MESSAGE_STATUS_NOTREAD;
}
if (!empty($params['search'])) {
$search = mb_strtoupper(Oracle::quote($params['search']));
$sql .= " and (upper(NOTIFICATION_BODY) like '%" . $search . "%' or subject like '%" . $search . "%') ";
}
$sql .= ' order by date_time desc';
if (!empty($params['pagination'])) {
return $db->pagination($sql, $params);
}
return $db->query($sql);
}
/**
* получаем список доступный клиентов по манагеру
*
* @param array $params
* @return array
*/
public static function getClientsList($params = [])
{
$db = Oracle::init();
$user = Auth::instance()->get_user();
if (empty($params['manager_id'])) {
$managerId = $user['MANAGER_ID'];
} else {
$managerId = $params['manager_id'];
}
if (empty($params['agent_id'])) {
$agentId = $user['AGENT_ID'];
} else {
$agentId = $params['agent_id'];
}
$sql = "select *\n from " . Oracle::$prefix . "V_WEB_CLIENTS_LIST t \n where t.agent_id = " . $agentId . "\n and not exists\n (\n select 1 \n from " . Oracle::$prefix . "V_WEB_MANAGER_CLIENTS vwc \n where vwc.client_id = t.client_id and vwc.agent_id = t.agent_id and vwc.manager_id = " . $managerId . "\n )";
if (!empty($params['search'])) {
$sql .= " and upper(t.NAME) like '%" . mb_strtoupper(Oracle::quote($params['search'])) . "%'";
}
$sql .= " order by t.client_id desc ";
return $db->query($sql);
}
/**
* получаем историю операция по карте
*
* @param $cardId
* @param $limit
*/
public static function getOperationsHistory($cardId, $params = [])
{
if (empty($cardId)) {
return [];
}
$db = Oracle::init();
$card = Model_Card::getCard($cardId);
$user = Auth::instance()->get_user();
$where = ["card_id = " . Oracle::quote($cardId), "agent_id = " . $user['AGENT_ID']];
if (!empty($card['CONTRACT_ID'])) {
$where[] = "contract_id = " . Oracle::quote($card['CONTRACT_ID']);
}
$sql = "\n\t\t\tselect *\n\t\t\tfrom " . Oracle::$prefix . "V_WEB_CRD_HISTORY\n\t\t\twhere " . implode(" and ", $where) . "\n\t\t\torder by HISTORY_DATE desc\n\t\t";
if (!empty($params['pagination'])) {
return $db->pagination($sql, $params);
}
return $db->query($sql);
}
/**
* Обороты по договору
*
* @param $contractId
*/
public static function getTurnover($contractId)
{
if (empty($contractId)) {
return [];
}
$db = Oracle::init();
$sql = "\n\t\t\tselect *\n\t\t\tfrom " . Oracle::$prefix . "V_WEB_CTR_BALANCE\n\t\t\twhere 1=1\n\t\t";
if (!empty($contractId)) {
$sql .= " and contract_id = '" . Oracle::quote($contractId) . "'";
}
$turnover = $db->row($sql);
return $turnover;
}
/**
* список поставщиков
*
* @param $search
* @param ids
* @return array|bool|int
*/
public static function getSuppliers($search, $ids = [])
{
if (empty($search) && empty($ids)) {
return false;
}
$db = Oracle::init();
$user = Auth::instance()->get_user();
$sql = "select * from " . Oracle::$prefix . "V_WEB_SUPPLIERS_LIST t where t.agent_id = " . $user['AGENT_ID'];
if (!empty($search)) {
$sql .= " and upper(t.SUPPLIER_NAME) like '%" . mb_strtoupper(Oracle::quote($search)) . "%'";
}
if (!empty($ids)) {
$sql .= " and t.ID in (" . implode(',', $ids) . ")";
}
return $db->query($db->limit($sql, 0, self::$limit));
}
/**
* получаем настройки для шаблона отчета
*
* @param $reportId
*/
public static function getReportTemplateSettings($reportId)
{
if (empty($reportId)) {
return false;
}
$db = Oracle::init();
$sql = "select * from " . Oracle::$prefix . "V_WEB_REPORTS_FORM t where t.report_id = " . Oracle::quote($reportId);
$settings = $db->tree($sql, 'PROPERTY_TYPE');
return $settings;
}
