<?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);
	}
}