subscription.php

См. документацию.

<?php
IncludeModuleLangFile(__FILE__);
 
class CSubscription
{
    public $LAST_ERROR = '';
    public $LAST_MESSAGE = '';
 
    public static function GetList($aSort=[], $arFilter=[], $arNavStartParams=false)
    {
        global $DB;
        $arSqlSearch = [];
        $from1 = '';
        if (is_array($arFilter))
        {
            foreach ($arFilter as $key => $val)
            {
                if (!is_array($val))
                {
                    if ( ((string)$val === '') || ($val === 'NOT_REF') )
                    {
                        continue;
                    }
                }
                switch (strtoupper($key))
                {
                    case 'ID':
                        $arSqlSearch[] = GetFilterQuery('S.ID', $val, 'N');
                        break;
                    case 'EMAIL':
                        $arSqlSearch[] = GetFilterQuery('S.EMAIL', $val, 'Y', ['@', '.', '_']);
                        break;
                    case 'UPDATE_1':
                        $arSqlSearch[] = 'S.DATE_UPDATE>=' . $DB->CharToDateFunction($val);
                        break;
                    case 'UPDATE_2':
                        $arSqlSearch[] = 'S.DATE_UPDATE<=' . $DB->CharToDateFunction($val . ' 23:59:59');
                        break;
                    case 'INSERT_1':
                        $arSqlSearch[] = 'S.DATE_INSERT>=' . $DB->CharToDateFunction($val);
                        break;
                    case 'INSERT_2':
                        $arSqlSearch[] = 'S.DATE_INSERT<=' . $DB->CharToDateFunction($val . ' 23:59:59');
                        break;
                    case 'USER':
                        $arSqlSearch[] = GetFilterQuery('U.ID, U.LOGIN, U.NAME, U.LAST_NAME', $val);
                        break;
                    case 'USER_ID':
                        $arSqlSearch[] = GetFilterQuery('S.USER_ID', $val, 'N');
                        break;
                    case 'ANONYMOUS':
                        $arSqlSearch[] = ($val == 'Y') ? '(S.USER_ID = 0 or S.USER_ID is null)' : '(S.USER_ID > 0 and S.USER_ID is not null)';
                        break;
                    case 'CONFIRMED':
                        $arSqlSearch[] = ($val == 'Y') ? "S.CONFIRMED='Y'" : "S.CONFIRMED='N'";
                        break;
                    case 'ACTIVE':
                        $arSqlSearch[] = ($val == 'Y') ? "S.ACTIVE='Y'" : "S.ACTIVE='N'";
                        break;
                    case 'FORMAT':
                        $arSqlSearch[] = ($val == 'text') ? "S.FORMAT='text'" : "S.FORMAT='html'";
                        break;
                    case 'RUBRIC':
                    case 'RUBRIC_MULTI':
                    case 'DISTRIBUTION':
                        if (is_array($val))
                        {
                            $val = implode(' | ', $val);
                        }
                        if ($val !== '')
                        {
                            $from1 = ' INNER JOIN b_subscription_rubric SR ON (SR.SUBSCRIPTION_ID=S.ID) ';
                            $arSqlSearch[] = GetFilterQuery('SR.LIST_RUBRIC_ID', $val, 'N');
                        }
                        else
                        {
                            $arSqlSearch[] = ' 1=0 ';
                        }
                        break;
                }
            }
        }
        $strSqlSearch = GetFilterSqlSearch($arSqlSearch);
 
        $arOrder = [];
        foreach ($aSort as $by => $ord)
        {
            $by = mb_strtoupper($by);
            $ord = (mb_strtoupper($ord) !== 'ASC' ? 'DESC' : 'ASC');
            switch ($by)
            {
                case 'ID':
                    $arOrder[$by] = 'S.ID ' . $ord;
                    break;
                case 'DATE_INSERT':
                    $arOrder[$by] = 'S.DATE_INSERT ' . $ord;
                    break;
                case 'DATE_UPDATE':
                    $arOrder[$by] = 'S.DATE_UPDATE ' . $ord;
                    break;
                case 'DATE_CONFIRM':
                    $arOrder[$by] = 'S.DATE_CONFIRM ' . $ord;
                    break;
                case 'ACT':
                    $arOrder[$by] = 'S.ACTIVE ' . $ord;
                    break;
                case 'CONF':
                    $arOrder[$by] = 'S.CONFIRMED ' . $ord;
                    break;
                case 'EMAIL':
                    $arOrder[$by] = 'S.EMAIL ' . $ord;
                    break;
                case 'FMT':
                    $arOrder[$by] = 'S.FORMAT ' . $ord;
                    break;
                case 'USER':
                    $arOrder[$by] = 'S.USER_ID ' . $ord;
                    break;
                case 'CONFIRM_CODE':
                    $arOrder[$by] = 'S.CONFIRM_CODE ' . $ord;
                    break;
            }
        }
        if (!$arOrder)
        {
            $arOrder['ID'] = 'S.ID DESC';
        }
 
        if (is_array($arNavStartParams))
        {
            $strSql = '
                SELECT count(' . ($from1 ? 'DISTINCT S.ID' : "'x'") . ') as C
                FROM
                    b_subscription S
                    LEFT JOIN b_user U ON (S.USER_ID=U.ID)
                    ' . $from1 . '
                WHERE
                ' . $strSqlSearch;
 
            $res_cnt = $DB->Query($strSql);
            $res_cnt = $res_cnt->Fetch();
            $cnt = $res_cnt['C'];
 
            $strSql = '
                SELECT
                    S.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED,
                    ' . $DB->DateToCharFunction('S.DATE_UPDATE') . ' DATE_UPDATE,
                    ' . $DB->DateToCharFunction('S.DATE_INSERT') . ' DATE_INSERT,
                    ' . $DB->DateToCharFunction('S.DATE_CONFIRM') . ' DATE_CONFIRM,
                    U.LOGIN USER_LOGIN,
                    U.NAME USER_NAME,
                    U.LAST_NAME USER_LAST_NAME
                FROM
                    b_subscription S
                    LEFT JOIN b_user U ON (S.USER_ID=U.ID)
                    ' . $from1 . '
                WHERE
                ' . $strSqlSearch . '
                ' . ($from1 ?
                    'GROUP BY S.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED, S.DATE_CONFIRM, S.DATE_UPDATE, S.DATE_INSERT, U.LOGIN, U.NAME, U.LAST_NAME' :
                    ''
                ) . '
                ORDER BY ' . implode(', ', $arOrder);
 
            $res = new CDBResult();
            $res->NavQuery($strSql, $cnt, $arNavStartParams);
            $res->is_filtered = (IsFiltered($strSqlSearch));
 
            return $res;
        }
        else
        {
            $strSql = '
                SELECT
                    S.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED,
                    ' . $DB->DateToCharFunction('S.DATE_UPDATE') . ' DATE_UPDATE,
                    ' . $DB->DateToCharFunction('S.DATE_INSERT') . ' DATE_INSERT,
                    ' . $DB->DateToCharFunction('S.DATE_CONFIRM') . ' DATE_CONFIRM,
                    U.LOGIN USER_LOGIN,
                    U.NAME USER_NAME,
                    U.LAST_NAME USER_LAST_NAME
                FROM
                    b_subscription S
                    LEFT JOIN b_user U ON (S.USER_ID=U.ID)
                    ' . $from1 . '
                WHERE
                ' . $strSqlSearch . '
                ' . ($from1 ?
                    'GROUP BY S.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED, S.DATE_CONFIRM, S.DATE_UPDATE, S.DATE_INSERT, U.LOGIN, U.NAME, U.LAST_NAME' :
                    ''
                ) . '
                ORDER BY ' . implode(', ', $arOrder);
 
            $res = $DB->Query($strSql);
            $res->is_filtered = (IsFiltered($strSqlSearch));
 
            return $res;
        }
    }
 
    //list of subscribed categories
    public static function GetRubricList($ID)
    {
        global $DB;
        $ID = intval($ID);
        $strSql = '
            SELECT R.ID, R.NAME, R.SORT, R.LID, R.ACTIVE, R.VISIBLE
            FROM b_list_rubric R, b_subscription_rubric SR
            WHERE R.ID = SR.LIST_RUBRIC_ID AND SR.SUBSCRIPTION_ID = ' . $ID . '
            ORDER BY R.LID, R.SORT, R.NAME
        ';
        return $DB->Query($strSql);
    }
 
    //array of subscribed categories
    public static function GetRubricArray($ID)
    {
        $ID = intval($ID);
        $aSubscrRub = [];
        if ($ID > 0)
        {
            $subscr_rub = CSubscription::GetRubricList($ID);
            while ($subscr_rub_arr = $subscr_rub->Fetch())
            {
                $aSubscrRub[] = $subscr_rub_arr['ID'];
            }
        }
        return $aSubscrRub;
    }
 
    //subscription of current user from cookies
    public static function GetUserSubscription()
    {
        global $USER;
        $email_cookie = COption::GetOptionString('main', 'cookie_name', 'BITRIX_SM') . '_SUBSCR_EMAIL';
 
        $subscr_EMAIL = ($_COOKIE[$email_cookie] <> '' ? $_COOKIE[$email_cookie] : $USER->GetParam('EMAIL'));
        if ($subscr_EMAIL <> '')
        {
            $subscr = CSubscription::GetByEmail($subscr_EMAIL, intval($USER->GetID()));
            if (($subscr_arr = $subscr->Fetch()))
            {
                return $subscr_arr;
            }
        }
        return ['ID' => 0, 'EMAIL' => ''];
    }
 
    //get by ID
    public static function GetByID($ID)
    {
        global $DB;
        $ID = intval($ID);
        $strSql = '
            SELECT S.*,
                ' . $DB->DateToCharFunction('S.DATE_UPDATE', 'FULL') . ' AS DATE_UPDATE,
                ' . $DB->DateToCharFunction('S.DATE_INSERT', 'FULL') . ' AS DATE_INSERT,
                ' . $DB->DateToCharFunction('S.DATE_CONFIRM', 'FULL') . ' AS DATE_CONFIRM
            FROM b_subscription S
            WHERE S.ID = ' . $ID . '
        ';
 
        return $DB->Query($strSql);
    }
 
    // deletion
    public static function Delete($ID)
    {
        global $DB;
        $ID = intval($ID);
 
        foreach (GetModuleEvents('subscribe', 'OnBeforeSubscriptionDelete', true) as $arEvent)
        {
            if (ExecuteModuleEventEx($arEvent, [$ID]) === false)
            {
                return false;
            }
        }
 
        $DB->StartTransaction();
 
        $res = $DB->Query("DELETE FROM b_subscription_rubric WHERE SUBSCRIPTION_ID='" . $ID . "'");
        if ($res)
        {
            $res = $DB->Query("DELETE FROM b_subscription WHERE ID='" . $ID . "' ");
        }
 
        if ($res)
        {
            foreach (GetModuleEvents('subscribe', 'OnAfterSubscriptionDelete', true) as $arEvent)
            {
                ExecuteModuleEventEx($arEvent, [$ID]);
            }
 
            $DB->Commit();
            return $res;
        }
 
        $DB->Rollback();
        return false;
    }
 
    //check fields before writing
    public function CheckFields(&$arFields, $ID, $SITE_ID=SITE_ID)
    {
        global $DB, $APPLICATION;
 
        $APPLICATION->ResetException();
        $this->LAST_ERROR = '';
        $aMsg = [];
        if ($ID > 0)
        {
            $arFields['ID'] = $ID;
        }
 
        if ($ID > 0)
        {
            $db_events = GetModuleEvents('subscribe', 'OnStartSubscriptionUpdate', true);
        }
        else
        {
            $db_events = GetModuleEvents('subscribe', 'OnStartSubscriptionAdd', true);
        }
 
        foreach ($db_events as $arEvent)
        {
            if (ExecuteModuleEventEx($arEvent, [&$arFields, $SITE_ID]) === false)
            {
                break;
            }
        }
 
        if (is_set($arFields, 'EMAIL'))
        {
            if ($arFields['EMAIL'] == '' || !check_email($arFields['EMAIL']))
            {
                $aMsg[] = ['id' => 'EMAIL', 'text' => GetMessage('class_subscr_addr')];
            }
            else
            {
                $res = static::GetByEmail($arFields['EMAIL'], intval($arFields['USER_ID']));
                $ar = $res->Fetch();
                if ($ar && ($ar['ID'] <> intval($ID)))
                {
                    $aMsg[] = ['id' => 'EMAIL', 'text' => GetMessage('class_subscr_addr2')];
                }
            }
        }
 
        if (is_set($arFields, 'USER_ID'))
        {
            if (intval($arFields['USER_ID']) > 0)
            {
                $res = $DB->Query("SELECT 'x' FROM b_user WHERE ID = " . intval($arFields['USER_ID']));
                if (!$res->Fetch())
                {
                    $aMsg[] = ['id' => 'USER_ID', 'text' => GetMessage('class_subscr_user')];
                }
            }
        }
 
        if ($ID > 0)
        {
            $db_events = GetModuleEvents('subscribe', 'OnBeforeSubscriptionUpdate', true);
        }
        else
        {
            $db_events = GetModuleEvents('subscribe', 'OnBeforeSubscriptionAdd', true);
        }
 
        foreach ($db_events as $arEvent)
        {
            if (ExecuteModuleEventEx($arEvent, [&$arFields, $SITE_ID]) === false)
            {
                $err = $APPLICATION->GetException();
                if ($err)
                {
                    $aMsg[] = ['id' => '', 'text' => $err->GetString()];
                    $APPLICATION->ResetException();
                }
                else
                {
                    $aMsg[] = ['id' => '', 'text' => 'Unknown error.'];
                }
                break;
            }
        }
 
        if ($ID > 0)
        {
            unset($arFields['ID']);
        }
 
        if (!empty($aMsg))
        {
            $e = new CAdminException($aMsg);
            $APPLICATION->ThrowException($e);
            $this->LAST_ERROR = $e->GetString();
            return false;
        }
 
        return true;
    }
 
    //link with categories
    public function UpdateRubrics($ID, $aRubric, $SITE_ID=false)
    {
        global $DB;
        $ID = intval($ID);
 
        if ($SITE_ID !== false)
        {
            $strSql = "
            SELECT sr.SUBSCRIPTION_ID,sr.LIST_RUBRIC_ID
            FROM
                b_subscription_rubric sr
                INNER JOIN b_list_rubric lr ON lr.ID=sr.LIST_RUBRIC_ID
            WHERE
                sr.SUBSCRIPTION_ID='" . $ID . "'
                AND lr.LID='" . $DB->ForSql($SITE_ID) . "'
            ";
            $rs = $DB->Query($strSql);
            while ($ar = $rs->Fetch())
            {
                $strSql = '
                DELETE FROM b_subscription_rubric
                WHERE SUBSCRIPTION_ID=' . intval($ar['SUBSCRIPTION_ID']) . '
                AND LIST_RUBRIC_ID=' . intval($ar['LIST_RUBRIC_ID']) . '
                ';
                $DB->Query($strSql);
            }
 
            $sID = '0';
            if (is_array($aRubric))
            {
                foreach ($aRubric as $rub)
                {
                    $sID .= ',' . intval($rub);
                }
            }
 
            $strSql = "
                INSERT INTO b_subscription_rubric (SUBSCRIPTION_ID, LIST_RUBRIC_ID)
                SELECT '" . $ID . "', ID
                FROM b_list_rubric
                WHERE ID IN (" . $sID . ")
                AND LID='" . $DB->ForSql($SITE_ID) . "'
            ";
            $DB->Query($strSql);
        }
        else
        {
            $DB->Query("DELETE FROM b_subscription_rubric WHERE SUBSCRIPTION_ID='" . $ID . "'");
 
            $sID = '0';
            if (is_array($aRubric))
            {
                foreach ($aRubric as $rub)
                {
                    $sID .= ',' . intval($rub);
                }
            }
 
            $strSql = "
                INSERT INTO b_subscription_rubric (SUBSCRIPTION_ID, LIST_RUBRIC_ID)
                SELECT '" . $ID . "', ID
                FROM b_list_rubric
                WHERE ID IN (" . $sID . ')
            ';
            $DB->Query($strSql);
        }
    }
 
    //adding
    public function Add($arFields, $SITE_ID=SITE_ID)
    {
        global $DB;
 
        if (!$this->CheckFields($arFields, 0, $SITE_ID))
        {
            return false;
        }
 
        if (array_key_exists('USER_ID', $arFields) && (intval($arFields['USER_ID']) <= 0))
        {
            $arFields['USER_ID'] = false;
        }
 
        $arFields['CONFIRM_CODE'] = \Bitrix\Main\Security\Random::getString(8, true);
        $arFields['~DATE_INSERT']  = $DB->CurrentTimeFunction();
        $arFields['~DATE_CONFIRM'] = $DB->CurrentTimeFunction();
 
        $ID = $DB->Add('b_subscription', $arFields);
 
        if ($ID > 0)
        {
            if (is_set($arFields,'ALL_SITES') && $arFields['ALL_SITES'] == 'Y')
            {
                $this->UpdateRubrics($ID, $arFields['RUB_ID']);
            }
            else
            {
                $this->UpdateRubrics($ID, $arFields['RUB_ID'], $SITE_ID);
            }
 
            if ($arFields['SEND_CONFIRM'] <> 'N')
            {
                static::ConfirmEvent($ID, $SITE_ID);
            }
        }
 
        return $ID;
    }
 
    //Updating record
    public function Update($ID, $arFields, $SITE_ID=SITE_ID)
    {
        global $DB;
        $ID = intval($ID);
        $this->LAST_MESSAGE = '';
 
        if (!$this->CheckFields($arFields, $ID, $SITE_ID))
        {
            return false;
        }
 
        if (array_key_exists('USER_ID', $arFields) && (intval($arFields['USER_ID']) <= 0))
        {
            $arFields['USER_ID'] = false;
        }
 
        //Check whether email changed. If changed, we must to generate new confirm code.
        $strSql = 'SELECT EMAIL, CONFIRM_CODE, CONFIRMED FROM b_subscription WHERE ID = ' . $ID;
        $db_check = $DB->Query($strSql);
 
        if (!($db_check_arr = $db_check->Fetch()))
        {
            $this->LAST_ERROR = GetMessage('class_subscr_perm') . '<br>';
            return false;
        }
 
        $CONFIRM_CODE = $arFields['CONFIRM_CODE'];
        unset($arFields['CONFIRM_CODE']);
        if (!is_set($arFields, 'EMAIL') || mb_strtoupper($db_check_arr['EMAIL']) === mb_strtoupper($arFields['EMAIL']))
        {
            //the same email - check confirm code
            if ($CONFIRM_CODE <> '' && $db_check_arr['CONFIRMED'] <> 'Y')
            {
                if ($CONFIRM_CODE == $db_check_arr['CONFIRM_CODE'])
                {
                    //let's confirm the subscription
                    $arFields['CONFIRMED'] = 'Y';
                    $this->LAST_MESSAGE = 'CONF';
                }
                else
                {
                    $this->LAST_ERROR = GetMessage('class_subscr_conf') . '<br>';
                    return false;
                }
            }
        }
        else
        {
            //new email - new confirm code
            $arFields['CONFIRM_CODE'] = \Bitrix\Main\Security\Random::getString(8, true);
            if ($arFields['CONFIRMED'] <> 'Y')
            {
                $arFields['CONFIRMED'] = 'N';
            }
        }
 
        $strUpdate = $DB->PrepareUpdate('b_subscription', $arFields);
        if ($strUpdate <> '')
        {
            $strSql =
                'UPDATE b_subscription SET ' . $strUpdate . ', ' . '    DATE_UPDATE=' . $DB->GetNowFunction() . ' ' . ($arFields['CONFIRM_CODE'] <> '' ? ',' . '    DATE_CONFIRM=' . $DB->GetNowFunction() . ' '
                : '')
                . 'WHERE ID=' . $ID;
            if (!$DB->Query($strSql))
            {
                return false;
            }
        }
 
        //subscription categories
        if (is_set($arFields, 'RUB_ID'))
        {
            if (is_set($arFields,'ALL_SITES') && $arFields['ALL_SITES'] == 'Y')
            {
                $this->UpdateRubrics($ID, $arFields['RUB_ID']);
            }
            else
            {
                $this->UpdateRubrics($ID, $arFields['RUB_ID'], $SITE_ID);
            }
        }
        //send confirmation code if needed
        if ($arFields['SEND_CONFIRM'] <> 'N' && $arFields['CONFIRM_CODE'] <> '')
        {
            static::ConfirmEvent($ID, $SITE_ID);
            $this->LAST_MESSAGE = 'SENT';
        }
 
        return true;
    }
 
    //message with subscription confirmation
    public static function ConfirmEvent($ID, $SITE_ID=SITE_ID)
    {
        static $SITE_DIR_CACHE = [];
        CTimeZone::Disable();
        $subscr = CSubscription::GetByID($ID);
        CTimeZone::Enable();
        if ($subscr_arr = $subscr->Fetch())
        {
            if (!array_key_exists($SITE_ID, $SITE_DIR_CACHE))
            {
                $db_lang = CLang::GetByID($SITE_ID);
                if ($ar_lang = $db_lang->Fetch())
                {
                    $SITE_DIR_CACHE[$SITE_ID] = $ar_lang['DIR'];
                }
                else
                {
                    $SITE_DIR_CACHE[$SITE_ID] = LANG_DIR;
                }
            }
 
            $subscr_arr['USER_NAME'] = '';
            $subscr_arr['USER_LAST_NAME'] = '';
            if (intval($subscr_arr['USER_ID']) > 0)
            {
                $rsUser = CUser::GetByID($subscr_arr['USER_ID']);
                if ($arUser = $rsUser->Fetch())
                {
                    $subscr_arr['USER_NAME'] = $arUser['NAME'];
                    $subscr_arr['USER_LAST_NAME'] = $arUser['LAST_NAME'];
                }
            }
 
            $arFields = [
                'ID' => $subscr_arr['ID'],
                'EMAIL' => $subscr_arr['EMAIL'],
                'CONFIRM_CODE' => $subscr_arr['CONFIRM_CODE'],
                'USER_NAME' => $subscr_arr['USER_NAME'] . ' ' . $subscr_arr['USER_LAST_NAME'],
                'DATE_SUBSCR' => ($subscr_arr['DATE_UPDATE'] <> '' ? $subscr_arr['DATE_UPDATE'] : $subscr_arr['DATE_INSERT']),
                'SUBSCR_SECTION' => str_replace(
                    ['#SITE_DIR#', '#LANG_DIR#'],
                    [$SITE_DIR_CACHE[$SITE_ID], $SITE_DIR_CACHE[$SITE_ID]],
                    COption::GetOptionString('subscribe', 'subscribe_section')
                ),
            ];
            CEvent::Send('SUBSCRIBE_CONFIRM', $SITE_ID, $arFields);
            return true;
        }
        return false;
    }
 
    //checks and set user authorization
    public static function Authorize($ID, $CONFIRM_CODE=false)
    {
        global $USER;
 
        $subscr = CSubscription::GetByID($ID);
        if (($subscr_arr = $subscr->Fetch()))
        {
            //unconditional auth
            if ($CONFIRM_CODE === false)
            {
                $_SESSION['SESS_SUBSCR_AUTH'][$ID] = 'YES';
                return true;
            }
            //anonymous subscription
            if ($subscr_arr['CONFIRM_CODE'] == $CONFIRM_CODE)
            {
                $_SESSION['SESS_SUBSCR_AUTH'][$ID] = 'YES';
                return true;
            }
            //user account subscription
            if (intval($subscr_arr['USER_ID']) > 0)
            {
                if ($USER->IsAuthorized())
                {
                    //user is already authorized
                    if ($USER->GetID() == $subscr_arr['USER_ID'])
                    {
                        $_SESSION['SESS_SUBSCR_AUTH'][$ID] = 'YES';
                        return true;
                    }
                }
            }
        }
        $_SESSION['SESS_SUBSCR_AUTH'][$ID] = 'NO';
        return false;
    }
 
    //retuns user's subscription authorization
    public static function IsAuthorized($ID)
    {
        return ($_SESSION['SESS_SUBSCR_AUTH'][$ID] == 'YES');
    }
 
    //*****************************
    // Events
    //*****************************
 
    //user deletion event
    public static function OnUserDelete($user_id)
    {
        //clear user subscriptions on user deletion
        global $DB;
        $user_id = intval($user_id);
 
        $strSql = 'SELECT ID FROM b_subscription WHERE USER_ID = ' . $user_id;
        $res = $DB->Query($strSql);
        $arIn = [];
        while ($res_arr = $res->Fetch())
        {
            $arIn[] = intval($res_arr['ID']);
        }
 
        if (count($arIn) > 0)
        {
            $sIn = implode(',',$arIn);
            if (
                $DB->Query('DELETE FROM b_subscription_rubric WHERE SUBSCRIPTION_ID IN (' . $sIn . ')') &&
                $DB->Query('DELETE FROM b_subscription WHERE ID IN (' . $sIn . ')')
            )
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        else
        {
            return true;
        }
    }
 
    //user logout event
    public static function OnUserLogout($user_id)
    {
        //let's reset subscriptions authorization on user logout
        global $DB;
        $user_id = intval($user_id);
        if ($user_id > 0)
        {
            $strSql = 'SELECT ID FROM b_subscription WHERE USER_ID=' . $user_id;
            $res = $DB->Query($strSql);
            while ($res_arr = $res->Fetch())
            {
                $_SESSION['SESS_SUBSCR_AUTH'][$res_arr['ID']] = 'NO';
            }
        }
        return true;
    }
 
    //*****************************
    // Agents
    //*****************************
 
    //agent to delete unconfirmed subscription
    public static function CleanUp()
    {
        $connection = \Bitrix\Main\Application::getConnection();
        $helper = $connection->getSqlHelper();
 
        $interval = intval(COption::GetOptionString('subscribe', 'subscribe_confirm_period'));
        if ($interval > 0)
        {
            $sql = "
                SELECT ID
                FROM b_subscription
                WHERE CONFIRMED <> 'Y'
                AND DATE_CONFIRM < " . $helper->addDaysToDateTime(1) . "
            ";
            $res = $connection->query($sql);
            $sIn = '0';
            while ($res_arr = $res->fetch())
            {
                $sIn .= ',' . $res_arr['ID'];
            }
 
            $connection->queryExecute('DELETE FROM b_subscription_rubric WHERE SUBSCRIPTION_ID IN (' . $sIn . ')');
            $connection->queryExecute('DELETE FROM b_subscription WHERE ID IN (' . $sIn . ')');
        }
 
        return 'CSubscription::CleanUp();';
    }
 
    //get by e-mail
    public static function GetByEmail($email, $user_id = false)
    {
        global $DB;
 
        if ($user_id === false)
        {
            $sWhere = '';
        }
        elseif ($user_id > 0)
        {
            $sWhere = 'AND S.USER_ID = ' . intval($user_id);
        }
        else
        {
            $sWhere = 'AND S.USER_ID IS NULL';
        }
 
        $strSql = '
            SELECT S.*,
                ' . $DB->DateToCharFunction('S.DATE_UPDATE', 'FULL') . ' AS DATE_UPDATE,
                ' . $DB->DateToCharFunction('S.DATE_INSERT', 'FULL') . ' AS DATE_INSERT,
                ' . $DB->DateToCharFunction('S.DATE_CONFIRM', 'FULL') . " AS DATE_CONFIRM
            FROM
                b_subscription S
            WHERE
                S.EMAIL='" . $DB->ForSQL($email) . "'
                " . $sWhere . '
        ';
 
        return $DB->Query($strSql);
    }
}