TASK_RTF_NOTES в MS Project или RTF в MS SQL. Как его победить и готовить кубы в SSAS

Имеется довольно наболевшая для программистов организаций-пользователей MS Project задачка — получение заметок ответственных лиц. Заметки имеют значительную ценность (при правильной постановке задачи управления), потому что без исходной информации проблемы не классифицировать и правильное решение не принять. Их, заметки, конечно, надо выводить в отчеты.

С точки зрения пользователя все вроде бы просто — отчет он и есть отчет, но с технической точки зрения открывается масса нюансов и вопросов. В этой статье я привожу свое решение, основанное на некоторых кусках кода, раскиданных то тут, то там, по сети, и надеюсь, что оно пригодится моим коллегам.

Не претендую на оригинальность, однако аналоги данного решения почему-то не нашел, пришлось собирать самому. Кроме того, я не очень глубокий спец по MS SQL, поэтому, буде возникнут дельные комментарии — прошу комментировать.

Заметки, очевидно, хранятся в поле с названием TASK_RTF_NOTES. Поле заполняется пользователем при передаче отчетов по задачам, за которые он отвечает. Тут вроде бы нет особой необходимости в средствах разметки, более того, они, эти средства, от пользователя скрыты, но уважаемые (хоть и не понимаемые) мною индусы зачем-то сохраняют эту информацию в формате RTF. Все бы ничего, но надо подготавливать отчеты, в том числе не по одному проекту, а по куче разом. Хорошим инструментом для подготовки отчетов является SSAS в совокупности, например, с MS Excel.

Да, в MS Project Professional, конечно, имеется «построитель отчетов». Но это не кубы, это куча запросов с выводом в табличку. И поменять «на ходу» разрезы, даже если очень хочется, у Вас не получится. И еще Вы этот отчет должны будете переслать куче о.л. вместо того, чтобы они сами взяли его в любой момент времени.

Т.е., предположительно, схема примерно такая:
1) Извлечь значение поля
2) Передать значение в измерение куба
3) Получить красивый отчет в MS Excel (например)

Т.е. как-то так:

Но не тут-то было.

Первый сюрприз — это то, что поле имеет тип image. Зачем? Непонятно. Почему я на этой мелочи остановился — будет понятно далее. Преобразовали в varbinary(MAX), понеслись дальше.
Второй сюрприз — собственно, RTF. что же с ним делать? Лезем в сеть и обнаруживаем пачку советов, которые я разбил на 2 категории:

1) Воспользоваться объектом RTFTextBox.
Совет лежит прямо в MSDN! Это, видимо, официальная позиция MS по этому поводу! Закрадывается даже смутное сомнение, а не заложили ли эту фичу в MSP специально, дабы иметь возможность продавать построители отчетов?

2) Не морочить голову людям проблемами и использовать поле TASK_NOTES.
Здорово. Особенно, если дальше почитать. А дальше написано, что тут хранятся только обрезки поля TASK_RTF_NOTES.

И все. Есть еще советы разбирать RTF при помощи регулярных выражений. Но регулярные выражения в MS SQL тоже как бы скорее нет, чем есть, для их реализации надо еще и «функцию среды CLR» добавить… Что делать-то?

Пришлось поискать еще. Теперь я уже имел более широкие возможности — т.к. без addon’а явно не обойтись, я подумал-подумал и решил написать addon конкретно для разбирания RTF. Думаете, я воспользовался советом №1? Да, правильно, им я и воспользовался. Но вот SQL почему-то пожевал мою dll-ку и выплюнул ее с фразой «не верю я вашей dll-ке System.Drawing, кривая она, плохо написана и есть в ней куча уязвимостей». Забавно! MS, оказывается, таки пишет кривые dll-ки, да еще и сам об этом мне сообщает.

Тогда я окончательно понял, что без парсинга RTF тут не обойтись и, наконец, наткнулся вот на это место:
NRTFTRee by Oliver

Здесь, конечно, ни слова про TASK_RTF_NOTES. Да еще и комменты на испанском. Однако тут я увидел реальную возможность собрать исходники парсера, что и было сделано.

Мелочи по части сборки без System.Drawing я опущу — достаточно немного фантазии и понимания, что на самом деле все эти классы для данной задачи не очень нужны, а незаменимых нет.

Пример SQL-скрипта, который теперь действительно работает:

SELECT TOP 1000 [TASK_UID]       ,[TASK_NAME]       ,[PROJ_UID]       ,convert(varchar(max),[dbo].ConvertFromRTF(convert(varbinary(max),[TASK_RTF_NOTES]))) 	  ,[TASK_NOTES]   FROM [ProjectServer2010_Published].[dbo].[MSP_TASKS] where not [TASK_RTF_NOTES] is null 

На вопрос «ЗАЧЕМ covert так много раз???» отвечу — почему-то функции CLR не поддерживают передачу image и varchar (по крайней мере у нас 2005-й MS SQL, про остальное не знаю).

Да, есть еще нюанс — кодировка в нашем MSP почему-то не 1251, а 1252 (но не всегда, т.к. часть проектов перенесена), поэтому сама функция конфетирования написана так:

[SqlFunction(DataAccess = DataAccessKind.Read)]         public static SqlBytes ConvertFromRTF(SqlBytes bytes)         {             if (bytes == null) return null;             if (bytes.IsNull) return null;             if (bytes.Length == 0) return null;              MemoryStream strm = new MemoryStream(bytes.Value);             byte[] buff = new byte[4096];             int rd = strm.Read(buff, 0, buff.Length);              RtfTree tree = new RtfTree();              string s1 = Encoding.Default.GetString(buff);             tree.LoadRtfText(s1);              string text = tree.Text;             SqlBytes res;             if (s1.IndexOf("ansicpg1252") > -1)             {                 res = new SqlBytes(Encoding.GetEncoding("Windows-1252").GetBytes(tree.Text));             }             else if (s1.IndexOf("ansicpg1251") > -1)             {                 res = new SqlBytes(Encoding.GetEncoding("Windows-1251").GetBytes(tree.Text));             }             else             {                 res = new SqlBytes(Encoding.Default.GetBytes(tree.Text));             }             //SqlBytes res = new SqlBytes(Encoding.Default.GetBytes(tree.Text));             //SqlBytes res = new SqlBytes(Encoding.GetEncoding("Windows-1252").GetBytes(tree.Text));             //SqlBytes res = new SqlBytes(buff);             return res;         } 

Ссылки:
1. Оригинал конвертера: NRTFTRee by Oliver
2. Архивчик с решением: LibRTFToVarchar