<?php

IncludeModuleLangFile(__FILE__);

class CWorkflow
{
	public static function OnPanelCreate()
	{
		global $APPLICATION, $USER;
		$cur_page_param = $APPLICATION->GetCurPageParam();
		$cur_page = $APPLICATION->GetCurPage(true);
		$cur_dir = $APPLICATION->GetCurDir();

		// New page
		$flow_link_new = self::GetEditLink([SITE_ID, rtrim(GetDirPath($cur_page), '/') . '/untitled.php'], $status_id, $status_title, 'standart.php', LANGUAGE_ID, $cur_page_param);
		$create_permission = $flow_link_new <> '' && $USER->CanDoFileOperation('fm_edit_in_workflow', [SITE_ID, $cur_dir]);
		// Document history
		$flow_link_hist = '/bitrix/admin/workflow_history_list.php?lang=' . LANGUAGE_ID . '&find_filename=' . urlencode($cur_page) . '&find_filename_exact_match=Y&set_filter=Y';
		$history_permission = $USER->CanDoFileOperation('fm_edit_in_workflow', [SITE_ID, $cur_page]);
		// Current page
		$flow_link_edit = self::GetEditLink([SITE_ID, $cur_page], $status_id, $status_title, '', LANGUAGE_ID, $cur_page_param);
		$edit_permission = $flow_link_edit <> '' && $history_permission;

		//Big button
		if ($edit_permission)
		{
			$public_edit = $APPLICATION->GetPopupLink([
				'URL' => $flow_link_edit . '&bxpublic=Y&from_module=workflow',
				'PARAMS' => [
					'min_width' => 700,
					'min_height' => 400,
					'height' => 700,
					'width' => 400,
				],
			]);

			$APPLICATION->AddPanelButton([
				'HREF' => 'javascript:' . $public_edit,
				'TYPE' => 'BIG',
				'ID' => 'edit',
				'ICON' => 'bx-panel-edit-page-icon',
				'ALT' => GetMessage('top_panel_edit_title'),
				'TEXT' => GetMessage('top_panel_edit_new'),
				'MAIN_SORT' => '200',
				'SORT' => 10,
				'MENU' => [],
				'HK_ID' => 'top_panel_edit_new',
				'RESORT_MENU' => true,
				'HINT' => [
					'TITLE' => GetMessage('top_panel_edit_new_tooltip_title'),
					'TEXT' => GetMessage('top_panel_edit_new_tooltip'),
				],
			]);
		}

		// New page
		if ($create_permission)
		{
			$APPLICATION->AddPanelButtonMenu('create', ['SEPARATOR' => true, 'SORT' => 49]);
			$APPLICATION->AddPanelButtonMenu('create', [
				'SRC' => '/bitrix/images/workflow/new_page.gif',
				'TEXT' => GetMessage('FLOW_PANEL_CREATE_WITH_WF'),
				'TITLE' => GetMessage('FLOW_PANEL_CREATE_ALT'),
				'ACTION' => "jsUtils.Redirect([], '" . CUtil::JSEscape($flow_link_new) . "')",
				'HK_ID' => 'FLOW_PANEL_CREATE_WITH_WF',
				'SORT' => 50,
			]);
		}

		if ($edit_permission || $history_permission)
		{
			$APPLICATION->AddPanelButtonMenu('edit', ['SEPARATOR' => true, 'SORT' => 79]);
		}

		// Current page
		if ($edit_permission)
		{
			$APPLICATION->AddPanelButtonMenu('edit', [
				'SRC' => '/bitrix/images/workflow/edit_flow_public.gif',
				'TEXT' => GetMessage('FLOW_PANEL_EDIT_WITH_WF'),
				'TITLE' => (intval($status_id) > 0 ? GetMessage('FLOW_CURRENT_STATUS') . ' [' . $status_id . '] ' . $status_title : GetMessage('FLOW_PANEL_EDIT_ALT')),
				'ACTION' => "jsUtils.Redirect([], '" . CUtil::JSEscape($flow_link_edit) . "')",
				'HK_ID' => 'FLOW_PANEL_EDIT_WITH_WF',
				'SORT' => 80,
			]);
		}

		// Document history
		if ($history_permission)
		{
			$flow_link_hist = '/bitrix/admin/workflow_history_list.php?lang=' . LANGUAGE_ID . '&find_filename=' . urlencode($cur_page) . '&find_filename_exact_match=Y&set_filter=Y';
			$APPLICATION->AddPanelButtonMenu('edit', [
				'SRC' => '/bitrix/images/workflow/history.gif',
				'TEXT' => GetMessage('FLOW_PANEL_HISTORY'),
				'TITLE' => GetMessage('FLOW_PANEL_HISTORY_ALT'),
				'ACTION' => "jsUtils.Redirect([], '" . CUtil::JSEscape($flow_link_hist) . "')",
				'HK_ID' => 'FLOW_PANEL_HISTORY',
				'SORT' => 81,
			]);
		}
	}

	public static function OnChangeFile($path, $site)
	{
		global $BX_WORKFLOW_PUBLISHED_PATH, $BX_WORKFLOW_PUBLISHED_SITE;
		if ($BX_WORKFLOW_PUBLISHED_PATH == $path && $BX_WORKFLOW_PUBLISHED_SITE == $site)
		{
			return;
		}

		global $DB, $USER, $APPLICATION;
		$HISTORY_SIMPLE_EDITING = COption::GetOptionString('workflow', 'HISTORY_SIMPLE_EDITING', 'N');
		if ($HISTORY_SIMPLE_EDITING == 'Y')
		{
			$HISTORY_COPIES = intval(COption::GetOptionString('workflow', 'HISTORY_COPIES', '10'));
			self::CleanUpHistoryCopies_SE($path, $HISTORY_COPIES - 1);
			if ($HISTORY_COPIES > 0)
			{
				$DOC_ROOT = \Bitrix\Main\SiteTable::getDocumentRoot($site === false ? null : $site);
				$filesrc = $APPLICATION->GetFileContent($DOC_ROOT . $path);
				$arContent = ParseFileContent($filesrc);
				$TITLE = $arContent['TITLE'];
				$BODY = $arContent['CONTENT'];
				$arFields = [
					'DOCUMENT_ID' => 0,
					'MODIFIED_BY' => $USER ? $USER->GetID() : 1,
					'TITLE' => $TITLE,
					'FILENAME' => $path,
					'SITE_ID' => $site,
					'BODY' => $BODY,
					'BODY_TYPE' => 'html',
					'STATUS_ID' => 1,
					'~TIMESTAMP_X' => $DB->CurrentTimeFunction(),
				];
				$DB->Add('b_workflow_log', $arFields, ['BODY'], 'workflow');
			}
		}
	}

	public static function SetHistory($DOCUMENT_ID)
	{
		global $DB;

		$LOG_ID = false;
		$DOCUMENT_ID = intval($DOCUMENT_ID);
		$HISTORY_COPIES = intval(COption::GetOptionString('workflow', 'HISTORY_COPIES', '10'));
		$z = self::GetByID($DOCUMENT_ID);
		if ($zr = $z->Fetch())
		{
			self::CleanUpHistoryCopies($DOCUMENT_ID, $HISTORY_COPIES - 1);
			if ($HISTORY_COPIES > 0)
			{
				$arFields = [
					'DOCUMENT_ID' => $DOCUMENT_ID,
					'MODIFIED_BY' => $zr['MODIFIED_BY'],
					'TITLE' => $zr['TITLE'],
					'FILENAME' => $zr['FILENAME'],
					'SITE_ID' => $zr['SITE_ID'],
					'BODY' => $zr['BODY'],
					'BODY_TYPE' => $zr['BODY_TYPE'],
					'STATUS_ID' => $zr['STATUS_ID'],
					'COMMENTS' => $zr['COMMENTS'],
					'~TIMESTAMP_X' => $DB->CurrentTimeFunction(),
				];
				$LOG_ID = $DB->Add('b_workflow_log', $arFields, ['BODY'], 'workflow');
			}
		}

		return $LOG_ID;
	}

	// Deletes old copies from document's history
	public static function CleanUpHistoryCopies($DOCUMENT_ID=false, $HISTORY_COPIES=false)
	{
		global $DB;

		if ($HISTORY_COPIES === false)
		{
			$HISTORY_COPIES = intval(COption::GetOptionString('workflow', 'HISTORY_COPIES', '10'));
		}

		$DOCUMENT_ID = intval($DOCUMENT_ID);
		if ($DOCUMENT_ID > 0)
		{
			$strSqlSearch = ' and ID = ' . $DOCUMENT_ID . ' ';
		}
		else
		{
			$strSqlSearch = '';
		}

		$strSql = 'SELECT ID FROM b_workflow_document WHERE 1=1 ' . $strSqlSearch;
		$z = $DB->Query($strSql);
		while ($zr = $z->Fetch())
		{
			$DID = $zr['ID'];
			$strSql = '
				SELECT
					ID
				FROM
					b_workflow_log
				WHERE
					DOCUMENT_ID = ' . $DID . '
				ORDER BY
					ID desc
				';
			$t = $DB->Query($strSql);
			$i = 0;
			$str_id = '0';
			while ($tr = $t->Fetch())
			{
				$i++;
				if ($i > $HISTORY_COPIES)
				{
					$str_id .= ', ' . $tr['ID'];
				}
			}
			$strSql = 'DELETE FROM b_workflow_log WHERE ID in (' . $str_id . ')';
			$DB->Query($strSql);
		}
	}

	// Deletes old copies from document's history (simple edit - SE)
	public static function CleanUpHistoryCopies_SE($FILENAME, $HISTORY_COPIES=false)
	{
		global $DB;

		if ($HISTORY_COPIES === false)
		{
			$HISTORY_COPIES = intval(COption::GetOptionString('workflow', 'HISTORY_COPIES', '10'));
		}
		$strSql = "
			SELECT
				ID
			FROM
				b_workflow_log
			WHERE
				FILENAME = '" . $DB->ForSql($FILENAME, 255) . "'
			and DOCUMENT_ID = 0
			ORDER BY
				ID desc
			";
		$t = $DB->Query($strSql);
		$i = 0;
		$str_id = '0';
		while ($tr = $t->Fetch())
		{
			$i++;
			if ($i > $HISTORY_COPIES)
			{
				$str_id .= ', ' . $tr['ID'];
			}
		}
		$strSql = 'DELETE FROM b_workflow_log WHERE ID in (' . $str_id . ')';
		$DB->Query($strSql);
	}

	// saves changes history and send e-mails on status change
	public static function SetMove($DOCUMENT_ID, $STATUS_ID, $OLD_STATUS_ID, $LOG_ID)
	{
		global $DB, $USER, $APPLICATION;

		$DOCUMENT_ID = intval($DOCUMENT_ID);
		$STATUS_ID = intval($STATUS_ID);
		$OLD_STATUS_ID = intval($OLD_STATUS_ID);
		$LOG_ID = intval($LOG_ID);

		$arFields = [
			'TIMESTAMP_X' => $DB->GetNowFunction(),
			'DOCUMENT_ID' => $DOCUMENT_ID,
			'OLD_STATUS_ID' => $OLD_STATUS_ID,
			'STATUS_ID' => $STATUS_ID,
			'LOG_ID' => $LOG_ID,
			'USER_ID' => intval($USER->GetID()),
		];
		$DB->Insert('b_workflow_move', $arFields);

		if ($STATUS_ID != $OLD_STATUS_ID)
		{
			CTimeZone::Disable();
			$d = self::GetByID($DOCUMENT_ID);
			CTimeZone::Enable();

			if ($dr = $d->Fetch())
			{
				$STATUS_ID = $dr['STATUS_ID'];

				// gather email of the workflow admins
				$WORKFLOW_ADMIN_GROUP_ID = COption::GetOptionInt('workflow', 'WORKFLOW_ADMIN_GROUP_ID', 0);
				$strSql = '
					SELECT
						U.ID,
						U.EMAIL
					FROM
						b_user U,
						b_user_group UG
					WHERE
						UG.GROUP_ID = ' . $WORKFLOW_ADMIN_GROUP_ID . "
						and U.ID = UG.USER_ID
						and U.ACTIVE = 'Y'
				";
				$a = $DB->Query($strSql);
				$arAdmin = [];
				while ($ar = $a->Fetch())
				{
					$arAdmin[$ar['ID']] = $ar['EMAIL'];
				}

				// gather email for BCC
				$arBCC = [];

				// gather all who changed doc in its current status
				$strSql = '
					SELECT
						USER_ID
					FROM
						b_workflow_move
					WHERE
						DOCUMENT_ID = ' . $DOCUMENT_ID . '
						and OLD_STATUS_ID = ' . $STATUS_ID . '
				';
				$z = $DB->Query($strSql);
				while ($zr = $z->Fetch())
				{
					$arBCC[$zr['EMAIL']] = $zr['EMAIL'];
				}

				// gather all editors
				// in case status have notifier flag
				$strSql = '
					SELECT DISTINCT
						UG.USER_ID
						,U.EMAIL
					FROM
						b_workflow_status S,
						b_workflow_status2group SG,
						b_user U,
						b_user_group UG
					WHERE
						S.ID = ' . $STATUS_ID . "
						and S.NOTIFY = 'Y'
						and SG.STATUS_ID = S.ID
						and SG.PERMISSION_TYPE = '2'
						and UG.GROUP_ID = SG.GROUP_ID
						and U.ID = UG.USER_ID
						and U.ACTIVE = 'Y'
				";
				$z = $DB->Query($strSql);
				while ($zr = $z->Fetch())
				{
					if (!array_key_exists($zr['EMAIL'], $arBCC))
					{
						$grp = [];
						$rs = $USER->GetUserGroupList($zr['USER_ID']);
						while ($ar = $rs->Fetch())
						{
							$grp[] = $ar['GROUP_ID'];
						}

						$arTasks = $APPLICATION->GetFileAccessPermission($dr['FILENAME'], $grp, true);
						foreach ($arTasks as $task_id)
						{
							$arOps = CTask::GetOperations($task_id, true);
							if (in_array('fm_edit_in_workflow', $arOps))
							{
								$arBCC[$zr['EMAIL']] = $zr['EMAIL'];

								break;
							}
						}
					}
				}

				unset($arBCC[$dr['EUSER_EMAIL']]);

				if (array_key_exists($dr['ENTERED_BY'], $arAdmin))
				{
					$dr['EUSER_NAME'] .= ' (Admin)';
				}

				// it is not new doc
				if ($OLD_STATUS_ID > 0)
				{
					if (array_key_exists($dr['MODIFIED_BY'], $arAdmin))
					{
						$dr['MUSER_NAME'] .= ' (Admin)';
					}
					$q = CWorkflowStatus::GetByID($OLD_STATUS_ID);
					$qr = $q->Fetch();
					// send change notification
					$arEventFields = [
						'ID' => $dr['ID'],
						'ADMIN_EMAIL' => implode(',', $arAdmin),
						'BCC' => implode(',', $arBCC),
						'PREV_STATUS_ID' => $OLD_STATUS_ID,
						'PREV_STATUS_TITLE' => $qr['TITLE'],
						'STATUS_ID' => $dr['STATUS_ID'],
						'STATUS_TITLE' => $dr['STATUS_TITLE'],
						'DATE_ENTER' => $dr['DATE_ENTER'],
						'ENTERED_BY_ID' => $dr['ENTERED_BY'],
						'ENTERED_BY_NAME' => $dr['EUSER_NAME'],
						'ENTERED_BY_EMAIL' => $dr['EUSER_EMAIL'],
						'DATE_MODIFY' => $dr['DATE_MODIFY'],
						'MODIFIED_BY_ID' => $dr['MODIFIED_BY'],
						'MODIFIED_BY_NAME' => $dr['MUSER_NAME'],
						'FILENAME' => $dr['FILENAME'],
						'SITE_ID' => $dr['SITE_ID'],
						'TITLE' => $dr['TITLE'],
						'BODY_HTML' => ($dr['BODY_TYPE'] == 'html' ? $dr['BODY'] : TxtToHTML($dr['BODY'])),
						'BODY_TEXT' => ($dr['BODY_TYPE'] == 'text' ? $dr['BODY'] : HtmlToTxt($dr['BODY'])),
						'BODY' => $dr['BODY'],
						'BODY_TYPE' => $dr['BODY_TYPE'],
						'COMMENTS' => $dr['COMMENTS'],
					];
					CEvent::Send('WF_STATUS_CHANGE', $dr['SITE_ID'], $arEventFields);
				}
				else // otherwise
				{
					// it was new one
					$arEventFields = [
						'ID' => $dr['ID'],
						'ADMIN_EMAIL' => implode(',', $arAdmin),
						'BCC' => implode(',', $arBCC),
						'STATUS_ID' => $dr['STATUS_ID'],
						'STATUS_TITLE' => $dr['STATUS_TITLE'],
						'DATE_ENTER' => $dr['DATE_ENTER'],
						'ENTERED_BY_ID' => $dr['ENTERED_BY'],
						'ENTERED_BY_NAME' => $dr['EUSER_NAME'],
						'ENTERED_BY_EMAIL' => $dr['EUSER_EMAIL'],
						'FILENAME' => $dr['FILENAME'],
						'SITE_ID' => $dr['SITE_ID'],
						'TITLE' => $dr['TITLE'],
						'BODY_HTML' => ($dr['BODY_TYPE'] == 'html' ? $dr['BODY'] : TxtToHTML($dr['BODY'])),
						'BODY_TEXT' => ($dr['BODY_TYPE'] == 'text' ? $dr['BODY'] : HtmlToTxt($dr['BODY'])),
						'BODY' => $dr['BODY'],
						'BODY_TYPE' => $dr['BODY_TYPE'],
						'COMMENTS' => $dr['COMMENTS'],
					];
					CEvent::Send('WF_NEW_DOCUMENT', $dr['SITE_ID'], $arEventFields);
				}
			}
		}
	}

	public static function Delete($DOCUMENT_ID)
	{
		global $DB;

		self::CleanUpFiles($DOCUMENT_ID);
		self::CleanUpPreview($DOCUMENT_ID);
		$DB->Query('DELETE FROM b_workflow_move WHERE DOCUMENT_ID = ' . intval($DOCUMENT_ID));
		$DB->Query('DELETE FROM b_workflow_document WHERE ID = ' . intval($DOCUMENT_ID));
	}

	public static function IsAdmin()
	{
		global $USER;

		if ($USER->IsAdmin())
		{
			return true;
		}
		else
		{
			$WORKFLOW_ADMIN_GROUP_ID = COption::GetOptionString('workflow', 'WORKFLOW_ADMIN_GROUP_ID');
			if (in_array($WORKFLOW_ADMIN_GROUP_ID, $USER->GetUserGroupArray()))
			{
				return true;
			}
		}

		return false;
	}

	// check edit rights for the document
	// depending on it's status and lock
	public static function IsAllowEdit($DOCUMENT_ID, &$locked_by, &$date_lock, $CHECK_RIGHTS='Y')
	{

		$DOCUMENT_ID = intval($DOCUMENT_ID);
		$LOCK_STATUS = self::GetLockStatus($DOCUMENT_ID, $locked_by, $date_lock);
		if ($LOCK_STATUS == 'red')
		{
			return false;
		}
		if ($LOCK_STATUS == 'yellow')
		{
			return true;
		}
		if ($LOCK_STATUS == 'green')
		{
			if ($CHECK_RIGHTS == 'Y')
			{
				return self::IsHaveEditRights($DOCUMENT_ID);
			}
			else
			{
				return true;
			}
		}

		return false;
	}

	public static function GetStatus($DOCUMENT_ID)
	{
		global $DB;

		$DOCUMENT_ID = intval($DOCUMENT_ID);
		$strSql = '
			SELECT
				S.*
			FROM
				b_workflow_document D,
				b_workflow_status S
			WHERE
				D.ID=' . $DOCUMENT_ID . '
			and	S.ID = D.STATUS_ID
			';
		$z = $DB->Query($strSql);

		return $z;
	}

	// check edit rights for the document
	// check is based only on status no lock
	public static function IsHaveEditRights($DOCUMENT_ID)
	{
		global $DB, $USER;

		if (self::IsAdmin())
		{
			return true;
		}

		$arGroups = $USER->GetUserGroupArray();
		if (!is_array($arGroups) || count($arGroups) <= 0)
		{
			$arGroups = [2];
		}

		$strSql = '
			SELECT
				G.ID
			FROM
				b_workflow_document D,
				b_workflow_status2group G
			WHERE
				D.ID = ' . intval($DOCUMENT_ID) . "
				and G.STATUS_ID = D.STATUS_ID
				and G.PERMISSION_TYPE >= '2'
				and G.GROUP_ID in (" . implode(', ', $arGroups) . ')
		';
		$z = $DB->Query($strSql);

		if ($zr = $z->Fetch())
		{
			return true;
		}
		else
		{
			return false;
		}
	}

	public static function UnLock($DOCUMENT_ID)
	{
		global $DB, $USER;

		$DOCUMENT_ID = intval($DOCUMENT_ID);
		$z = self::GetByID($DOCUMENT_ID);
		$zr = $z->Fetch();
		if (self::IsAdmin() || $zr['LOCKED_BY'] == $USER->GetID())
		{
			$arFields = [
				'DATE_LOCK' => 'null',
				'LOCKED_BY' => 'null',
			];
			$rows = $DB->Update('b_workflow_document', $arFields, 'WHERE ID=' . $DOCUMENT_ID);

			return intval($rows);
		}

		return false;
	}

	public static function Lock($DOCUMENT_ID)
	{
		global $DB, $USER;

		$DOCUMENT_ID = intval($DOCUMENT_ID);
		$z = self::GetByID($DOCUMENT_ID);
		if ($zr = $z->Fetch())
		{
			if ($zr['STATUS_ID'] != 1)
			{
				$arFields = [
					'DATE_LOCK' => $DB->GetNowFunction(),
					'LOCKED_BY' => $USER->GetID(),
				];
				$DB->Update('b_workflow_document', $arFields, "WHERE ID='" . $DOCUMENT_ID . "'");
			}
		}
	}

	// return edit link depending on rights and status
	public static function GetEditLink($FILENAME, &$status_id, &$status_title, $template='', $lang=LANGUAGE_ID, $return_url='')
	{
		global $USER;

		$link = '';
		CMain::InitPathVars($SITE_ID, $FILENAME);

		if ($USER->CanDoFileOperation('fm_edit_in_workflow', [$SITE_ID, $FILENAME]))
		{
			//Check if user have access at least to one status
			if (!self::IsAdmin())
			{
				$arGroups = $USER->GetUserGroupArray();
				if (!is_array($arGroups))
				{
					$arGroups = [2];
				}
				$arFilter = [
					'GROUP_ID' => $arGroups,
					'PERMISSION_TYPE_1' => 1,
				];
				$rsStatuses = CWorkflowStatus::GetList('s_c_sort', 'asc', $arFilter, null, ['ID']);
				if (!$rsStatuses->Fetch())
				{
					return '';
				}
			}

			$link = '/bitrix/admin/workflow_edit.php?lang=' . $lang . '&site=' . $SITE_ID . '&fname=' . $FILENAME;
			if ($template <> '')
			{
				$link .= '&template=' . urlencode($template);
			}
			if ($return_url <> '')
			{
				$link .= '&return_url=' . urlencode($return_url);
			}
			$z = self::GetByFilename($FILENAME, $SITE_ID);
			if ($zr = $z->Fetch())
			{
				$status_id = $zr['STATUS_ID'];
				$status_title = $zr['STATUS_TITLE'];
				if ($status_id != 1)
				{
					$DOCUMENT_ID = $zr['ID'];
					if (self::IsHaveEditRights($DOCUMENT_ID))
					{
						$link .= '&ID=' . $DOCUMENT_ID;
					}
					else
					{
						return '';
					}
				}
			}
		}

		return $link;
	}

	public static function DeleteHistory($ID)
	{
		global $DB;
		$DB->Query('
			DELETE FROM b_workflow_log
			WHERE ID = ' . intval($ID) . '
		');
	}

	public static function CleanUp()
	{
		self::CleanUpPublished();
		self::CleanUpHistory();
		self::CleanUpFiles();
		self::CleanUpPreview();

		return 'CWorkflow::CleanUp();';
	}

	public static function CleanUpFiles($DOCUMENT_ID=false, $FILE_ID=false)
	{
		global $DB;

		if ($DOCUMENT_ID === false)
		{
			$strSql = 'SELECT TEMP_FILENAME FROM b_workflow_file WHERE DOCUMENT_ID is null';
		}
		else
		{
			$DOCUMENT_ID = intval($DOCUMENT_ID);
			$strSql = 'SELECT TEMP_FILENAME FROM b_workflow_file WHERE DOCUMENT_ID = ' . $DOCUMENT_ID;
		}
		if ($FILE_ID !== false)
		{
			$FILE_ID = intval($FILE_ID);
			$strSql .= ' and ID = ' . $FILE_ID;
		}
		$z = $DB->Query($strSql);
		while ($zr = $z->Fetch())
		{
			self::DeleteFile($zr['TEMP_FILENAME']);
		}
	}

	public static function CleanUpPreview($DOCUMENT_ID=false)
	{
		global $DB;

		if ($DOCUMENT_ID === false)
		{
			$strSql = '
				SELECT
					P.FILENAME, D.SITE_ID
				FROM
					b_workflow_document D,
					b_workflow_preview P
				WHERE
					D.STATUS_ID = 1
					and P.DOCUMENT_ID = D.ID
				';
		}
		else
		{
			$DOCUMENT_ID = intval($DOCUMENT_ID);
			$strSql = '
				SELECT
					FILENAME
				FROM
					b_workflow_preview
				WHERE
					DOCUMENT_ID = ' . $DOCUMENT_ID . '
				';
		}
		$z = $DB->Query($strSql);
		while ($zr = $z->Fetch())
		{
			self::DeletePreview($zr['FILENAME'], $zr['SITE']);
		}
	}

	public static function DeletePreview($FILENAME, $site = false)
	{
		global $DB;

		$strSql = "DELETE FROM b_workflow_preview WHERE FILENAME='" . $DB->ForSql($FILENAME, 255) . "'";
		$DB->Query($strSql);
		$DOC_ROOT = \Bitrix\Main\SiteTable::getDocumentRoot($site === false ? null : $site);
		$path = $DOC_ROOT . $FILENAME;
		if (file_exists($path))
		{
			unlink($path);
		}
	}

	public static function DeleteFile($FILENAME)
	{
		global $DB;

		$strSql = "DELETE FROM b_workflow_file WHERE TEMP_FILENAME='" . $DB->ForSql($FILENAME, 255) . "'";
		$DB->Query($strSql);
		$temp_path = self::GetTempDir() . $FILENAME;
		if (file_exists($temp_path))
		{
			unlink($temp_path);
		}
	}

	public static function IsFilenameExists($FILENAME)
	{
		global $DB;

		$strSql = "SELECT ID FROM b_workflow_file WHERE TEMP_FILENAME='" . $DB->ForSql($FILENAME, 255) . "'";
		$z = $DB->Query($strSql);
		$zr = $z->Fetch();

		return intval($zr['ID']);
	}

	public static function GetUniqueFilename($filename)
	{
		$ext = GetFileExtension($filename);
		$temp_file = md5($filename . uniqid(rand())) . '.' . $ext;
		while (self::IsFilenameExists($temp_file))
		{
			$temp_file = md5($filename . uniqid(rand())) . '.' . $ext;
		}

		return $temp_file;
	}

	public static function IsPreviewExists($FILENAME)
	{
		global $DB;

		$z = $DB->Query("
			SELECT ID
			FROM b_workflow_preview
			WHERE FILENAME='" . $DB->ForSql($FILENAME, 255) . "'
		");

		$zr = $z->Fetch();

		return intval($zr['ID']);
	}

	public static function GetUniquePreview($DOCUMENT_ID)
	{
		global $DB;

		$z = $DB->Query('
			SELECT FILENAME
			FROM b_workflow_document
			WHERE ID = ' . intval($DOCUMENT_ID) . '
		');

		$zr = $z->Fetch();
		if ($zr)
		{
			$DOCUMENT_PATH = GetDirPath($zr['FILENAME']);
			do
			{
				$temp_file = $DOCUMENT_PATH . md5(uniqid(rand())) . '.php';
			}
			while (self::IsPreviewExists($temp_file));
		}

		return $temp_file;
	}

	public static function SetStatus($DOCUMENT_ID, $STATUS_ID, $OLD_STATUS_ID, $history=true)
	{
		global $DB, $APPLICATION, $USER, $strError;

		//$arMsg = Array();
		$DOCUMENT_ID = intval($DOCUMENT_ID);
		$STATUS_ID = intval($STATUS_ID);
		$OLD_STATUS_ID = intval($OLD_STATUS_ID);
		if ($STATUS_ID == 1) // if "[1] Published"
		{
			// get all files associated with the document
			$files = self::GetFileList($DOCUMENT_ID);
			while ($file = $files->Fetch())
			{
				$path = $file['FILENAME'];
				$DOC_ROOT = \Bitrix\Main\SiteTable::getDocumentRoot($file['SITE_ID'] === false ? null : $file['SITE_ID']);
				$pathto = $DOC_ROOT . $path;
				$pathfrom = self::GetTempDir() . $file['TEMP_FILENAME'];
				if (
					$USER->CanDoFileOperation('fm_edit_in_workflow', [$file['SITE_ID'], $path])
					&& $USER->CanDoFileOperation('fm_edit_existent_file', [$file['SITE_ID'], $path])
					&& $USER->CanDoFileOperation('fm_create_new_file', [$file['SITE_ID'], $path])
				)
				{
					if (!copy($pathfrom, $pathto))
					{
						$str = GetMessage('FLOW_CAN_NOT_WRITE_FILE', ['#FILENAME#' => $path]);
						$strError .= $str . '<br>';
					}
				}
				else
				{
					$str = GetMessage('FLOW_ACCESS_DENIED_FOR_FILE_WRITE', ['#FILENAME#' => $path]);
					$strError .= $str . '<br>';
				}
			}

			// still good
			if ($strError == '')
			{
				// publish the document
				$y = self::GetByID($DOCUMENT_ID);
				$yr = $y->Fetch();
				if (
					$USER->CanDoFileOperation('fm_edit_in_workflow', [$yr['SITE_ID'], $yr['FILENAME']])
					&& $USER->CanDoFileOperation('fm_edit_existent_file', [$yr['SITE_ID'], $yr['FILENAME']])
					&& $USER->CanDoFileOperation('fm_create_new_file', [$yr['SITE_ID'], $yr['FILENAME']])
				)
				{
					// save file
					$prolog = $yr['PROLOG'];
					if ($prolog <> '')
					{
						$title = $yr['TITLE'];
						$prolog = SetPrologTitle($prolog, $title);
					}
					$content = ($yr['BODY_TYPE'] == 'text') ? TxtToHTML($yr['BODY']) : $yr['BODY'];
					$content = WFToPath($content);
					$epilog = $yr['EPILOG'];
					$filesrc = $prolog . $content . $epilog;
					global $BX_WORKFLOW_PUBLISHED_PATH, $BX_WORKFLOW_PUBLISHED_SITE;
					$BX_WORKFLOW_PUBLISHED_PATH = $yr['FILENAME'];
					$BX_WORKFLOW_PUBLISHED_SITE = $yr['SITE_ID'];
					$DOC_ROOT = \Bitrix\Main\SiteTable::getDocumentRoot($yr['SITE_ID'] === false ? null : $yr['SITE_ID']);
					$APPLICATION->SaveFileContent($DOC_ROOT . $yr['FILENAME'], $filesrc);
					$BX_WORKFLOW_PUBLISHED_PATH = '';
					$BX_WORKFLOW_PUBLISHED_SITE = '';
				}
				else // otherwise
				{
					// throw error
					$str = GetMessage('FLOW_ACCESS_DENIED_FOLDER', ['#FILENAME#' => $yr['FILENAME']]);
					$strError .= GetMessage('FLOW_ERROR') . htmlspecialcharsbx($str) . '<br>';
				}
			}
		}

		if ($strError == '')
		{
			// update db
			$arFields = [
				'DATE_MODIFY' => $DB->GetNowFunction(),
				'MODIFIED_BY' => $USER->GetID(),
				'STATUS_ID' => intval($STATUS_ID),
			];
			$DB->Update('b_workflow_document', $arFields, 'WHERE ID=' . $DOCUMENT_ID);
			if ($history === true)
			{
				$LOG_ID = self::SetHistory($DOCUMENT_ID);
				self::SetMove($DOCUMENT_ID, $STATUS_ID, $OLD_STATUS_ID, $LOG_ID);
			}
		}
		else
		{
			$strError = GetMessage('FLOW_DOCUMENT_NOT_PUBLISHED') . '<br>' . $strError;
		}
		self::CleanUpPublished();
	}

	public static function LinkFiles2Document($arUploadedFiles, $DOCUMENT_ID)
	{
		global $DB;

		$DOCUMENT_ID = intval($DOCUMENT_ID);
		if (is_array($arUploadedFiles) && count($arUploadedFiles) > 0)
		{
			foreach ($arUploadedFiles as $FILE_ID)
			{
				$FILE_ID = intval($FILE_ID);
				$strSql = 'UPDATE b_workflow_file SET DOCUMENT_ID=' . $DOCUMENT_ID . ' WHERE ID=' . $FILE_ID;
				$DB->Query($strSql);
			}
		}
		self::CleanUpFiles();
	}

	public static function GetFileByID($DOCUMENT_ID, $FILENAME)
	{
		global $DB;

		$DOCUMENT_ID = intval($DOCUMENT_ID);
		$strSql = '
			SELECT
				F.*
			FROM
				b_workflow_file F
			WHERE
				F.DOCUMENT_ID = ' . $DOCUMENT_ID . "
			and F.FILENAME = '" . $DB->ForSql($FILENAME, 255) . "'
			";
		$z = $DB->Query($strSql);

		return $z;
	}

	public static function GetTempDir()
	{
		$upload_dir = COption::GetOptionString('', 'upload_dir', '/upload/');
		$dir = $_SERVER['DOCUMENT_ROOT'] . '/' . $upload_dir . '/workflow/';
		$dir = str_replace('//', '/', $dir);

		return $dir;
	}

	public static function GetFileContent($did, $fname, $wf_path='', $site=false)
	{
		global $DB, $APPLICATION, $USER;

		$did = intval($did);
		$io = CBXVirtualIo::GetInstance();

		// check if executable
		if (
			$USER->IsAdmin()
			|| (
				$io->ValidatePathString($fname)
				&& !HasScriptExtension($fname)
			)
		)
		{
			if ($did > 0)
			{
				// check if it is associated wtih document
				$z = self::GetFileByID($did, $fname);
				// found one
				if ($zr = $z->Fetch())
				{
					// get it's contents
					$path = self::GetTempDir() . $zr['TEMP_FILENAME'];
					if (file_exists($path))
					{
						return $APPLICATION->GetFileContent($path);
					}
				}
				else
				{
					// lookup in database
					$strSql = 'SELECT FILENAME, SITE_ID FROM b_workflow_document WHERE ID = ' . $did;
					$y = $DB->Query($strSql);
					// found
					if ($yr = $y->Fetch())
					{
						// get it's directory
						$path = GetDirPath($yr['FILENAME']);
						// absolute path
						$pathto = Rel2Abs($path, $fname);
						$DOC_ROOT = \Bitrix\Main\SiteTable::getDocumentRoot($yr['SITE_ID'] === false ? null : $yr['SITE_ID']);
						$path = $DOC_ROOT . $pathto;
						// give it another try
						$u = self::GetFileByID($did, $pathto);
						// found
						if ($ur = $u->Fetch())
						{
							// get it's contents
							$path = self::GetTempDir() . $ur['TEMP_FILENAME'];
							if (file_exists($path))
							{
								return $APPLICATION->GetFileContent($path);
							}
						}
						elseif (file_exists($path)) // it is already on disk
						{
							// get it's contents
							if ($USER->CanDoFileOperation('fm_view_file', [$yr['SITE_ID'], $pathto]))
							{
								return $APPLICATION->GetFileContent($path);
							}
						}
					}
				}
			}
			$DOC_ROOT = \Bitrix\Main\SiteTable::getDocumentRoot($site === false ? null : $site);
			// new one
			if ($wf_path <> '')
			{
				$pathto = Rel2Abs($wf_path, $fname);
				$path = $DOC_ROOT . $pathto;
				if (file_exists($path)) // it is already on disk
				{
					// get it's contents
					if ($USER->CanDoFileOperation('fm_view_file', [$site, $pathto]))
					{
						$src = $APPLICATION->GetFileContent($path);

						return $src;
					}
				}
			}

			// still failed to find
			// get path
			$path = $DOC_ROOT . $fname;
			if (file_exists($path))
			{
				// get it's contents
				if ($USER->CanDoFileOperation('fm_view_file', [$site, $fname]))
				{
					return $APPLICATION->GetFileContent($path);
				}
			}
		}
// it is executable
		else
		{
			return GetMessage('FLOW_ACCESS_DENIED_PHP_VIEW');
		}
	}

	public static function __CheckSite($site)
	{
		if ($site !== false)
		{
			if ($site <> '')
			{
				$res = CSite::GetByID($site);
				if (!$res->Fetch())
				{
					$site = false;
				}
			}
			else
			{
				$site = false;
			}
		}

		return $site;
	}

	public static function Insert($arFields)
	{
		global $DB;

		$arFields['~DATE_MODIFY'] = $DB->CurrentTimeFunction();
		$arFields['~DATE_ENTER'] = $DB->CurrentTimeFunction();
		$ID = $DB->Add('b_workflow_document', $arFields, [], 'workflow');
		$LOG_ID = self::SetHistory($ID);
		self::SetMove($ID, $arFields['STATUS_ID'], 0, $LOG_ID);

		return $ID;
	}

	public static function Update($arFields, $DOCUMENT_ID)
	{
		global $DB;

		$z = self::GetByID($DOCUMENT_ID);
		$change = false;
		if ($zr = $z->Fetch())
		{
			if (
				$zr['STATUS_ID'] != $arFields['STATUS_ID']
				|| $zr['BODY'] != $arFields['BODY']
				|| $zr['BODY_TYPE'] != $arFields['BODY_TYPE']
				|| $zr['COMMENTS'] != $arFields['COMMENTS']
				|| $zr['FILENAME'] != $arFields['FILENAME']
				|| $zr['SITE_ID'] != $arFields['SITE_ID']
				|| $zr['TITLE'] != $arFields['TITLE']
			)
			{
				$change = true;
			}
		}

		$strUpdate = $DB->PrepareUpdate('b_workflow_document', $arFields, 'workflow');
		if ($strUpdate)
		{
			$DB->Query('
				UPDATE b_workflow_document
				SET ' . $strUpdate . ', DATE_MODIFY=now(), DATE_ENTER=now()
				WHERE ID = ' . $DOCUMENT_ID,
			);
		}

		if ($change)
		{
			$LOG_ID = self::SetHistory($DOCUMENT_ID);
			self::SetMove($DOCUMENT_ID, $arFields['STATUS_ID'], $zr['STATUS_ID'], $LOG_ID);
		}
	}

	public static function GetLockStatus($DOCUMENT_ID, &$locked_by, &$date_lock)
	{
		global $DB, $USER;
		$connection = \Bitrix\Main\Application::getConnection();
		$helper = $connection->getSqlHelper();

		$DOCUMENT_ID = intval($DOCUMENT_ID);
		$MAX_LOCK = COption::GetOptionInt('workflow', 'MAX_LOCK_TIME', '60');
		$uid = intval($USER->GetID());
		$strSql = '
			SELECT
				LOCKED_BY,
				' . $DB->DateToCharFunction('DATE_LOCK') . " DATE_LOCK,
				case
				when DATE_LOCK is null then 'green'
				when " . $helper->addSecondsToDateTime($MAX_LOCK * 60, 'DATE_LOCK') . " < now() then 'green'
				when LOCKED_BY = " . $uid . " then 'yellow'
				else 'red' end LOCK_STATUS
			FROM
				b_workflow_document
			WHERE
				ID = " . $DOCUMENT_ID . '
			';
		$z = $DB->Query($strSql);
		$zr = $z->Fetch();
		$locked_by = $zr ? $zr['LOCKED_BY'] : 0;
		$date_lock = $zr? $zr['DATE_LOCK'] : false;

		return $zr['LOCK_STATUS'];
	}

	public static function GetList($by = 's_date_modify', $order = 'desc', $arFilter = [])
	{
		global $DB, $USER;
		$connection = \Bitrix\Main\Application::getConnection();
		$helper = $connection->getSqlHelper();

		$obQueryWhere = new CSQLWhere();
		static $arWhereFields = [
			'ID' => [
				'TABLE_ALIAS' => 'D',
				'FIELD_NAME' => 'D.ID',
				'FIELD_TYPE' => 'int', //int, double, file, enum, int, string, date, datetime
				'JOIN' => false,
			],
			'MODIFIED_BY' => [
				'TABLE_ALIAS' => 'D',
				'FIELD_NAME' => 'D.MODIFIED_BY',
				'FIELD_TYPE' => 'int',
				'JOIN' => false,
			],
			'STATUS_ID' => [
				'TABLE_ALIAS' => 'D',
				'FIELD_NAME' => 'D.STATUS_ID',
				'FIELD_TYPE' => 'int',
				'JOIN' => false,
			],
		];
		$obQueryWhere->SetFields($arWhereFields);

		$arSqlSearch = [];
		$MAX_LOCK = COption::GetOptionInt('workflow', 'MAX_LOCK_TIME', '60');
		$arGroups = $USER->GetUserGroupArray();
		if (!is_array($arGroups))
		{
			$arGroups[] = 2;
		}
		$groups = implode(', ', $arGroups);
		$uid = intval($USER->GetID());
		if (is_array($arFilter))
		{
			foreach ($arFilter as $key => $val)
			{
				if ((string)$val == '' || (string)$val == 'NOT_REF')
				{
					continue;
				}
				if (is_array($val) && count($val) <= 0)
				{
					continue;
				}
				$match_value_set = array_key_exists($key . '_EXACT_MATCH', $arFilter);
				$key = strtoupper($key);
				switch ($key)
				{
					case 'ID':
					case 'STATUS_ID':
						$arSqlSearch[] = $obQueryWhere->GetQuery([$key => $val]);

						break;
					case 'DATE_MODIFY_1':
						if (CheckDateTime($val))
						{
							$arSqlSearch[] = 'D.DATE_MODIFY >= ' . $DB->CharToDateFunction($val, 'SHORT');
						}

						break;
					case 'DATE_MODIFY_2':
						if (CheckDateTime($val))
						{
							$arSqlSearch[] = 'D.DATE_MODIFY < ' . $DB->CharToDateFunction($val, 'SHORT') . ' + INTERVAL 1 DAY';
						}

						break;
					case 'MODIFIED_BY':
						$match = ($match_value_set && $arFilter[$key . '_EXACT_MATCH'] == 'Y') ? 'N' : 'Y';
						$filter = GetFilterQuery('UM.LOGIN, UM.NAME, UM.LAST_NAME', $val, $match);
						if ($filter)
						{
							$arSqlSearch[] = $obQueryWhere->GetQuery(['MODIFIED_BY' => $val]) . ' or ' . $filter;
						}

						break;
					case 'MODIFIED_USER_ID':
						$arSqlSearch[] = $obQueryWhere->GetQuery(['MODIFIED_BY' => $val]);

						break;
					case 'LOCK_STATUS':
						$arSqlSearch[] = "
							case
								when D.DATE_LOCK is null then 'green'
								when " . $helper->addSecondsToDateTime($MAX_LOCK * 60, 'D.DATE_LOCK') . " < now() then 'green'
								when D.LOCKED_BY = " . $uid . " then 'yellow'
								else 'red' end = '" . $DB->ForSql($val) . "'";

						break;
					case 'STATUS':
						$match = ($match_value_set && $arFilter[$key . '_EXACT_MATCH'] == 'Y') ? 'N' : 'Y';
						$filter = GetFilterQuery('S.TITLE', $val, $match);
						if ($filter)
						{
							$arSqlSearch[] = $obQueryWhere->GetQuery(['STATUS_ID' => $val]) . ' or ' . $filter;
						}

						break;
					case 'SITE_ID':
					case 'TITLE':
					case 'BODY':
						$match = ($match_value_set && $arFilter[$key . '_EXACT_MATCH'] == 'Y') ? 'N' : 'Y';
						$arSqlSearch[] = GetFilterQuery('D.' . $key, $val, $match);

						break;
					case 'FILENAME':
						$match = ($match_value_set && $arFilter[$key . '_EXACT_MATCH'] == 'Y') ? 'N' : 'Y';
						$arSqlSearch[] = GetFilterQuery('D.FILENAME', $val, $match, ['/', '\\', '.', '_']);

						break;
				}
			}
		}

		if ($by === 's_id')
		{
			$strSqlOrder = 'ORDER BY D.ID';
		}
		elseif ($by === 's_lock_status')
		{
			$strSqlOrder = 'ORDER BY LOCK_STATUS';
		}
		elseif ($by === 's_date_modify')
		{
			$strSqlOrder = 'ORDER BY D.DATE_MODIFY';
		}
		elseif ($by === 's_modified_by')
		{
			$strSqlOrder = 'ORDER BY D.MODIFIED_BY';
		}
		elseif ($by === 's_filename')
		{
			$strSqlOrder = 'ORDER BY D.FILENAME';
		}
		elseif ($by === 's_title')
		{
			$strSqlOrder = 'ORDER BY D.TITLE';
		}
		elseif ($by === 's_site_id')
		{
			$strSqlOrder = 'ORDER BY D.SITE_ID';
		}
		elseif ($by === 's_status')
		{
			$strSqlOrder = 'ORDER BY D.STATUS_ID';
		}
		else
		{
			$strSqlOrder = 'ORDER BY D.DATE_MODIFY';
		}

		if ($order != 'asc')
		{
			$strSqlOrder .= ' desc ';
		}

		$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
		if (self::IsAdmin())
		{
			$strSql = '
				SELECT DISTINCT
					D.*,
					' . $DB->DateToCharFunction('D.DATE_ENTER') . ' DATE_ENTER,
					' . $DB->DateToCharFunction('D.DATE_MODIFY') . ' DATE_MODIFY,
					' . $DB->DateToCharFunction('D.DATE_LOCK') . " DATE_LOCK,
					concat_ws(' ', " . $DB->Concat("'('", 'UM.LOGIN', "')'") . ", nullif(UM.NAME, ''), nullif(UM.LAST_NAME, '')) MUSER_NAME,
					concat_ws(' ', " . $DB->Concat("'('", 'UE.LOGIN', "')'") . ", nullif(UE.NAME, ''), nullif(UE.LAST_NAME, '')) EUSER_NAME,
					S.TITLE STATUS_TITLE,
					case
						when D.DATE_LOCK is null then 'green'
						when " . $helper->addSecondsToDateTime($MAX_LOCK * 60, 'D.DATE_LOCK') . " < now() then 'green'
						when D.LOCKED_BY = " . $uid . " then 'yellow'
					else 'red' end LOCK_STATUS
				FROM
					b_workflow_document D
					LEFT JOIN b_workflow_status S ON (S.ID = D.STATUS_ID)
					LEFT JOIN b_user UM ON (UM.ID = D.MODIFIED_BY)
					LEFT JOIN b_user UE ON (UE.ID = D.ENTERED_BY)
				WHERE
				" . $strSqlSearch . '
				' . $strSqlOrder . '
				';
		}
		else
		{
			$strSql = '
				SELECT DISTINCT
					D.*,
					' . $DB->DateToCharFunction('D.DATE_ENTER') . ' DATE_ENTER,
					' . $DB->DateToCharFunction('D.DATE_MODIFY') . ' DATE_MODIFY,
					' . $DB->DateToCharFunction('D.DATE_LOCK') . " DATE_LOCK,
					concat_ws(' ', " . $DB->Concat("'('", 'UM.LOGIN', "')'") . ", nullif(UM.NAME, ''), nullif(UM.LAST_NAME, '')) MUSER_NAME,
					concat_ws(' ', " . $DB->Concat("'('", 'UE.LOGIN', "')'") . ", nullif(UE.NAME, ''), nullif(UE.LAST_NAME, '')) EUSER_NAME,
					S.TITLE STATUS_TITLE,
					case
						when D.DATE_LOCK is null then 'green'
						when " . $helper->addSecondsToDateTime($MAX_LOCK * 60, 'D.DATE_LOCK') . " < now() then 'green'
						when D.LOCKED_BY = " . $uid . " then 'yellow'
					else 'red' end LOCK_STATUS
				FROM
					b_workflow_document D
					INNER JOIN b_workflow_status2group G ON (G.STATUS_ID = D.STATUS_ID)
					LEFT JOIN b_workflow_status S ON (S.ID = D.STATUS_ID)
					LEFT JOIN b_user UM ON (UM.ID = D.MODIFIED_BY)
					LEFT JOIN b_user UE ON (UE.ID = D.ENTERED_BY)
				WHERE
				" . $strSqlSearch . '
				and G.GROUP_ID in (' . $groups . ")
				and G.PERMISSION_TYPE >= '2'
				" . $strSqlOrder . '
				';
		}

		$rs = $DB->Query($strSql);
		$arr = [];
		while ($ar = $rs->Fetch())
		{
			if ($USER->CanDoFileOperation('fm_edit_in_workflow', [$ar['SITE_ID'], $ar['FILENAME']]))
			{
				$arr[] = $ar;
			}
		}
		$rs = new CDBResult();
		$rs->InitFromArray($arr);

		return $rs;
	}

	public static function GetByID($ID)
	{
		global $DB, $USER;
		$connection = \Bitrix\Main\Application::getConnection();
		$helper = $connection->getSqlHelper();

		$ID = intval($ID);
		$MAX_LOCK = COption::GetOptionInt('workflow', 'MAX_LOCK_TIME', '60');
		$uid = intval($USER->GetID());
		$strSql = '
			SELECT
				D.*,
				' . $DB->DateToCharFunction('D.DATE_ENTER') . ' DATE_ENTER,
				' . $DB->DateToCharFunction('D.DATE_MODIFY') . ' DATE_MODIFY,
				' . $DB->DateToCharFunction('D.DATE_LOCK') . " DATE_LOCK,
				concat_ws(' ', " . $DB->Concat("'('", 'UM.LOGIN', "')'") . ", nullif(UM.NAME, ''), nullif(UM.LAST_NAME, '')) MUSER_NAME,
				concat_ws(' ', " . $DB->Concat("'('", 'UE.LOGIN', "')'") . ", nullif(UE.NAME, ''), nullif(UE.LAST_NAME, '')) EUSER_NAME,
				concat_ws(' ', " . $DB->Concat("'('", 'UL.LOGIN', "')'") . ", nullif(UL.NAME, ''), nullif(UL.LAST_NAME, '')) LUSER_NAME,
				UE.EMAIL EUSER_EMAIL,
				S.TITLE STATUS_TITLE,
				case
					when D.DATE_LOCK is null then 'green'
					when " . $helper->addSecondsToDateTime($MAX_LOCK * 60, 'D.DATE_LOCK') . " < now() then 'green'
					when D.LOCKED_BY = " . $uid . " then 'yellow'
				else 'red' end LOCK_STATUS
			FROM
				b_workflow_document D
				LEFT JOIN b_user UM ON (UM.ID = D.MODIFIED_BY)
				LEFT JOIN b_user UE ON (UE.ID = D.ENTERED_BY)
				LEFT JOIN b_user UL ON (UL.ID = D.LOCKED_BY)
				LEFT JOIN b_workflow_status S ON (S.ID = D.STATUS_ID)
			WHERE
				D.ID = " . $ID . '
			';
		$res = $DB->Query($strSql);

		return $res;
	}

	public static function GetByFilename($FILENAME, $SITE_ID, $arFilter = false)
	{
		if (!is_array($arFilter))
		{
			$arFilter = [
				'!STATUS_ID' => 1,
			];
		}

		$obQueryWhere = new CSQLWhere();
		$obQueryWhere->SetFields([
			'STATUS_ID' => [
				'TABLE_ALIAS' => 'D',
				'FIELD_NAME' => 'D.STATUS_ID',
				'FIELD_TYPE' => 'int',
				'JOIN' => false,
			],
		]);
		$strSqlWhere = $obQueryWhere->GetQuery($arFilter);

		global $DB, $USER;
		$connection = \Bitrix\Main\Application::getConnection();
		$helper = $connection->getSqlHelper();

		$MAX_LOCK = COption::GetOptionInt('workflow', 'MAX_LOCK_TIME', '60');
		$uid = intval($USER->GetID());
		$strSql = '
			SELECT
				D.*,
				' . $DB->DateToCharFunction('D.DATE_ENTER') . ' DATE_ENTER,
				' . $DB->DateToCharFunction('D.DATE_MODIFY') . ' DATE_MODIFY,
				' . $DB->DateToCharFunction('D.DATE_LOCK') . " DATE_LOCK,
				concat_ws(' ', " . $DB->Concat("'('", 'UM.LOGIN', "')'") . ", nullif(UM.NAME, ''), nullif(UM.LAST_NAME, '')) MUSER_NAME,
				concat_ws(' ', " . $DB->Concat("'('", 'UE.LOGIN', "')'") . ", nullif(UE.NAME, ''), nullif(UE.LAST_NAME, '')) EUSER_NAME,
				concat_ws(' ', " . $DB->Concat("'('", 'UL.LOGIN', "')'") . ", nullif(UL.NAME, ''), nullif(UL.LAST_NAME, '')) LUSER_NAME,
				S.TITLE STATUS_TITLE,
				case
					when D.DATE_LOCK is null then 'green'
					when " . $helper->addSecondsToDateTime($MAX_LOCK * 60, 'D.DATE_LOCK') . " < now() then 'green'
					when D.LOCKED_BY = " . $uid . " then 'yellow'
				else 'red' end LOCK_STATUS
			FROM
				b_workflow_document D
				LEFT JOIN b_user UM ON (UM.ID = D.MODIFIED_BY)
				LEFT JOIN b_user UE ON (UE.ID = D.ENTERED_BY)
				LEFT JOIN b_user UL ON (UL.ID = D.LOCKED_BY)
				LEFT JOIN b_workflow_status S ON (S.ID = D.STATUS_ID)
			WHERE
				SITE_ID = '" . $DB->ForSql($SITE_ID, 2) . "'
				AND D.FILENAME = '" . $DB->ForSql($FILENAME, 255) . "'
				" . ($strSqlWhere ? 'AND ' . $strSqlWhere : '') . '
		';
		$res = $DB->Query($strSql);

		return $res;
	}

	public static function GetHistoryList($by = 's_id', $order = 'desc', $arFilter = [])
	{
		global $DB;
		$connection = \Bitrix\Main\Application::getConnection();
		$helper = $connection->getSqlHelper();

		$obQueryWhere = new CSQLWhere();
		static $arWhereFields = [
			'ID' => [
				'TABLE_ALIAS' => 'L',
				'FIELD_NAME' => 'L.ID',
				'FIELD_TYPE' => 'int', //int, double, file, enum, int, string, date, datetime
				'JOIN' => false,
			],
			'DOCUMENT_ID' => [
				'TABLE_ALIAS' => 'L',
				'FIELD_NAME' => 'L.DOCUMENT_ID',
				'FIELD_TYPE' => 'int',
				'JOIN' => false,
			],
			'MODIFIED_BY' => [
				'TABLE_ALIAS' => 'L',
				'FIELD_NAME' => 'L.MODIFIED_BY',
				'FIELD_TYPE' => 'int',
				'JOIN' => false,
			],
			'STATUS_ID' => [
				'TABLE_ALIAS' => 'L',
				'FIELD_NAME' => 'L.STATUS_ID',
				'FIELD_TYPE' => 'int',
				'JOIN' => false,
			],
		];
		$obQueryWhere->SetFields($arWhereFields);

		$arSqlSearch = [];
		if (is_array($arFilter))
		{
			foreach ($arFilter as $key => $val)
			{
				if ((string)$val == '' || (string)$val == 'NOT_REF')
				{
					continue;
				}
				if (is_array($val) && !$val)
				{
					continue;
				}
				$match_value_set = array_key_exists($key . '_EXACT_MATCH', $arFilter);
				$key = strtoupper($key);
				switch ($key)
				{
					case 'ID':
					case 'DOCUMENT_ID':
					case 'MODIFIED_BY':
					case 'STATUS_ID':
						$arSqlSearch[] = $obQueryWhere->GetQuery([$key => $val]);

						break;
					case 'DATE_MODIFY_1':
						if (CheckDateTime($val))
						{
							$arSqlSearch[] = 'L.TIMESTAMP_X >= ' . $DB->CharToDateFunction($val, 'SHORT');
						}

						break;
					case 'DATE_MODIFY_2':
						if (CheckDateTime($val))
						{
							$arSqlSearch[] = 'L.TIMESTAMP_X < ' . $helper->addDaysToDateTime(1, $DB->CharToDateFunction($val, 'SHORT'));
						}

						break;
					case 'MODIFIED_USER':
						$match = ($match_value_set && $arFilter[$key . '_EXACT_MATCH'] == 'Y') ? 'N' : 'Y';
						$filter = GetFilterQuery('U.LOGIN, U.NAME, U.LAST_NAME', $val, $match);
						if ($filter)
						{
							$arSqlSearch[] = $obQueryWhere->GetQuery(['MODIFIED_BY' => $val]) . ' or ' . $filter;
						}

						break;
					case 'TITLE':
					case 'SITE_ID':
						$match = ($match_value_set && $arFilter[$key . '_EXACT_MATCH'] == 'Y') ? 'N' : 'Y';
						$arSqlSearch[] = GetFilterQuery('L.' . $key, $val, $match);

						break;
					case 'FILENAME':
						$match = ($match_value_set && $arFilter[$key . '_EXACT_MATCH'] == 'Y') ? 'N' : 'Y';
						$arSqlSearch[] = GetFilterQuery('L.FILENAME', $val, $match, ['/', '\\', '.', '_']);

						break;
					case 'BODY':
						$match = ($match_value_set && $arFilter[$key . '_EXACT_MATCH'] == 'N') ? 'Y' : 'N';
						$arSqlSearch[] = GetFilterQuery('L.BODY', $val, $match, [], 'Y');

						break;
					case 'STATUS':
						$match = ($match_value_set && $arFilter[$key . '_EXACT_MATCH'] == 'Y') ? 'N' : 'Y';
						$filter = GetFilterQuery('S.TITLE', $val, $match);
						if ($filter)
						{
							$arSqlSearch[] = $obQueryWhere->GetQuery(['STATUS_ID' => $val]) . ' or ' . $filter;
						}

						break;
				}
			}
		}

		if ($by === 's_id')
		{
			$strSqlOrder = 'ORDER BY L.ID';
		}
		elseif ($by === 's_document_id')
		{
			$strSqlOrder = 'ORDER BY L.DOCUMENT_ID';
		}
		elseif ($by === 's_date_modify')
		{
			$strSqlOrder = 'ORDER BY L.TIMESTAMP_X';
		}
		elseif ($by === 's_modified_by')
		{
			$strSqlOrder = 'ORDER BY L.MODIFIED_BY';
		}
		elseif ($by === 's_filename')
		{
			$strSqlOrder = 'ORDER BY L.FILENAME';
		}
		elseif ($by === 's_site_id')
		{
			$strSqlOrder = 'ORDER BY L.SITE_ID';
		}
		elseif ($by === 's_title')
		{
			$strSqlOrder = 'ORDER BY L.TITLE';
		}
		elseif ($by === 's_status')
		{
			$strSqlOrder = 'ORDER BY L.STATUS_ID';
		}
		else
		{
			$strSqlOrder = 'ORDER BY L.ID';
		}

		if ($order != 'asc')
		{
			$strSqlOrder .= ' desc ';
		}

		$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
		$strSql = '
			SELECT DISTINCT
				L.*,
				' . $DB->DateToCharFunction('L.TIMESTAMP_X') . " TIMESTAMP_X,
				concat_ws(' ', " . $DB->Concat("'('", 'U.LOGIN', "')'") . ", nullif(U.NAME, ''), nullif(U.LAST_NAME, '')) USER_NAME,
				S.TITLE STATUS_TITLE
			FROM
				b_workflow_log L
				LEFT JOIN b_workflow_status S ON (S.ID = L.STATUS_ID)
				LEFT JOIN b_user U ON (U.ID = L.MODIFIED_BY)
			WHERE
			" . $strSqlSearch . '
			' . $strSqlOrder . '
			';

		$res = $DB->Query($strSql);

		return $res;
	}

	public static function GetHistoryByID($ID)
	{
		global $DB;

		$ID = intval($ID);
		$strSql = '
			SELECT DISTINCT
				L.*,
				' . $DB->DateToCharFunction('L.TIMESTAMP_X') . " TIMESTAMP_X,
				concat_ws(' ', " . $DB->Concat("'('", 'U.LOGIN', "')'") . ", nullif(U.NAME, ''), nullif(U.LAST_NAME, '')) USER_NAME,
				S.TITLE STATUS_TITLE
			FROM
				b_workflow_log L
				LEFT JOIN b_workflow_status S ON (S.ID = L.STATUS_ID)
				LEFT JOIN b_user U ON (U.ID = L.MODIFIED_BY)
			WHERE
				L.ID = " . $ID . '
		';
		$res = $DB->Query($strSql);

		return $res;
	}

	public static function CleanUpHistory()
	{
		$connection = \Bitrix\Main\Application::getConnection();
		$helper = $connection->getSqlHelper();

		$HISTORY_DAYS = COption::GetOptionInt('workflow', 'HISTORY_DAYS', '-1');
		if ($HISTORY_DAYS >= 0)
		{
			$strSql = 'DELETE FROM b_workflow_log WHERE TIMESTAMP_X < ' . $helper->addDaysToDateTime(-$HISTORY_DAYS);
			$connection->query($strSql);
		}

		if (CModule::IncludeModule('iblock'))
		{
			CIblockElement::WF_CleanUpHistory();
		}
	}

	public static function CleanUpPublished()
	{
		$connection = \Bitrix\Main\Application::getConnection();
		$helper = $connection->getSqlHelper();

		$DAYS_AFTER_PUBLISHING = COption::GetOptionInt('workflow', 'DAYS_AFTER_PUBLISHING', '0');
		if ($DAYS_AFTER_PUBLISHING >= 0)
		{
			$strSql = '
				SELECT
					ID
				FROM
					b_workflow_document
				WHERE
					STATUS_ID = 1
					and DATE_MODIFY < ' . $helper->addDaysToDateTime(-$DAYS_AFTER_PUBLISHING) . '
				';
			$z = $connection->query($strSql);
			while ($zr = $z->fetch())
			{
				self::Delete($zr['ID']);
			}
		}
	}

	public static function GetFileList($DOCUMENT_ID)
	{
		global $DB;

		$DOCUMENT_ID = intval($DOCUMENT_ID);
		$strSql = '
			SELECT
				F.*, D.SITE_ID,
				' . $DB->DateToCharFunction('F.TIMESTAMP_X') . " TIMESTAMP_X,
				concat_ws(' ', " . $DB->Concat("'('", 'U.LOGIN', "')'") . ", nullif(U.NAME, ''), nullif(U.LAST_NAME, '')) USER_NAME
			FROM
				b_workflow_document D
				INNER JOIN b_workflow_file F ON (F.DOCUMENT_ID = D.ID)
				LEFT JOIN b_user U ON (U.ID = F.MODIFIED_BY)
			WHERE
				D.ID = " . $DOCUMENT_ID . '
			ORDER BY
				F.TIMESTAMP_X desc
		';
		$z = $DB->Query($strSql);

		return $z;
	}
}