Diachok's IT blog

Monday, February 4, 2013

Формирование планов в Excel по данным из 1С 8.2 УТ11

Поступила от руководства интересная задача по формированию планов.
Фактически данную задачу можно было бы сформировать и в 1С, но манагеры люди гордые им удобнее работать в excel'е. В связи с этим и пришлось хорошенько помучиться, что бы все работало правильно.
Плюс ко всему из-за чего был выбран именно Excel, уточнение в задаче состояло что выбрать для формирования планов нужно было всю номенклатуру с группировкой до 2 уровня.
1. Проблемой которая возникла, использование excel на сервере, так как используем 1С 8.2 (x64) то на сервере в обязательном порядке должен быть установлен офис (x64).
2. Проблема состоит в том что необходимо разрешить использование DCOM, в противном случае не получиться инициализировать Excel.
3. Проблема состоялась в правах на чтение и запись от имени пользователя под которым запущен 1с.
4. Прикол excel необходимо создать каталог (без него excel отказывался записывать файл) c:\Windows\SysWOW64\config\systemprofile\Desktop\
5. C excel'ом пришлось подолбаться хорошо, благо часть задач помогло решить работа с макросами (формируем необходимый файл, после включаем запись макроса, выполняем необходимое действие руками, останавливаем макрос, и входим в него в режиме отладки), но с некоторыми было тяжеловато, пришлось перебирать кучу страниц из поиска в поисках оптимальных идей.

Ниже приведен код работы внешней обработки, основные 2 процедуры ВыгрузитьФайлик и ОформитьПлан.
1. Часть формируем запрос в 1с
2. Для скорости выгружаем данные через OLEDB
3. Открываем в excel и формируем Сводную таблицу (Pivot table)
4. Так как в сводную таблицу очень тяжело что либо вносить, копируем
5. Форматируем в необходимый вид
6. Добавляем две колонки по работе с планами и заполняем иерархию формулами
7. Далее уже от того, что необходимо, либо отсылаем по почте(за комментировано), либо отдаем пользователю

&НаКлиенте
Процедура Команда1(Команда)
    СтруктФункц = Новый Структура();
    СтруктФункц.Вставить("Подр", ЭтаФорма.Подразделение);
    СтруктФункц.Вставить("ПоказыватьСебестоимость", НЕ ЭтаФорма.ПоказыватьСебестоимость);
    СтруктФункц.Вставить("ДатаНачала",ЭтаФорма.ПериодОтчета.ДатаНачала);
    СтруктФункц.Вставить("ДатаОкончания",ЭтаФорма.ПериодОтчета.ДатаОкончания);
    СтруктФункц.Вставить("ФормаУИД",ЭтаФорма.УникальныйИдентификатор);
    Адрес = ВыгрузитьФайлик(СтруктФункц);
    ПолучитьФайл(Адрес,Transliterate(Подразделение)+".xlsx",Истина);
КонецПроцедуры

&НаСервереБезКонтекста 
Функция ВернутьТекстЗапроса()
    Возврат "ВЫБРАТЬ
            |   Номенклатура.Ссылка КАК Ссылка,
            |   Номенклатура.НаправленияДеятельности.Наименование КАК НаправленияДеятельности
            |ИЗ
            |   Справочник.Номенклатура КАК Номенклатура
            |ГДЕ
            |   Номенклатура.ЭтоГруппа
            |   И Номенклатура.Ссылка В
            |           (ВЫБРАТЬ
            |               КлючиАналитикиУчетаНоменклатуры.Номенклатура.Родитель
            |           ИЗ
            |               РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&ПериодНачала, &ПериодОкончания, , ) КАК ВыручкаИСебестоимостьПродажОбороты ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.КлючиАналитикиУчетаНоменклатуры КАК КлючиАналитикиУчетаНоменклатуры
            |                   ПО
            |                       ВыручкаИСебестоимостьПродажОбороты.АналитикаУчетаНоменклатуры = КлючиАналитикиУчетаНоменклатуры.Ссылка
            |           СГРУППИРОВАТЬ ПО
            |                       КлючиАналитикиУчетаНоменклатуры.Номенклатура.Родитель)
            |
            |УПОРЯДОЧИТЬ ПО
            |   Номенклатура.Ссылка ИЕРАРХИЯ
            |;
            |
            |////////////////////////////////////////////////////////////////////////////////
            |ВЫБРАТЬ
            |   ВыручкаИСебестоимостьПродажОбороты.Период КАК Период,
            |   КлючиАналитикиУчетаПоПартнерам.Партнер.ОсновнойМенеджер.Наименование КАК Менеджер,
            |   КлючиАналитикиУчетаПоПартнерам.Партнер.ОсновнойМенеджер.ТекущееПодразделение.Наименование + "" ("" + КлючиАналитикиУчетаПоПартнерам.Партнер.ОсновнойМенеджер.ТекущееПодразделение.Код + "")"" КАК Подразделение,
            |   КлючиАналитикиУчетаПоПартнерам.Партнер.Наименование + "" ("" + КлючиАналитикиУчетаПоПартнерам.Партнер.Код + "")"" КАК Партнер,
            |   СУММА(ВыручкаИСебестоимостьПродажОбороты.КоличествоОборот) КАК Количество,
            |   СУММА(ВыручкаИСебестоимостьПродажОбороты.СуммаВыручкиОборот) КАК Выручка,
            |   ВЫБОР
            |       КОГДА &ПоказыватьСебестоимость
            |           ТОГДА 0
            |       ИНАЧЕ СУММА(ВыручкаИСебестоимостьПродажОбороты.СебестоимостьОборот)
            |   КОНЕЦ КАК Себестоимость,
            |   ВЫБОР
            |       КОГДА &ПоказыватьСебестоимость
            |           ТОГДА 0
            |       ИНАЧЕ СУММА(ВыручкаИСебестоимостьПродажОбороты.СуммаДополнительныхРасходовОборот)
            |   КОНЕЦ КАК ДопРасходы,
            |   КлючиАналитикиУчетаНоменклатуры.Номенклатура.Родитель КАК Ссылка
            |ИЗ
            |   РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(
            |           &ПериодНачала,
            |           &ПериодОкончания,
            |           Месяц,
            |           АналитикаУчетаПоПартнерам В
            |               (ВЫБРАТЬ
            |                   КлючиАналитикиУчетаПоПартнерам.Ссылка
            |               ИЗ
            |                   Справочник.КлючиАналитикиУчетаПоПартнерам КАК КлючиАналитикиУчетаПоПартнерам
            |               ГДЕ
            |                   КлючиАналитикиУчетаПоПартнерам.Организация = &Организация
            |                   И КлючиАналитикиУчетаПоПартнерам.Партнер В
            |                       (ВЫБРАТЬ
            |                           ПартнерыСегмента.Партнер
            |                       ИЗ
            |                           РегистрСведений.ПартнерыСегмента КАК ПартнерыСегмента ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Партнеры КАК Партнеры ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Пользователи КАК Пользователи
            |                                   ПО
            |                                       Партнеры.ОсновнойМенеджер = Пользователи.Ссылка
            |                               ПО
            |                                   ПартнерыСегмента.Партнер = Партнеры.Ссылка
            |                       ГДЕ
            |                           ПартнерыСегмента.Сегмент = &Сегмент
            |                           И НЕ Партнеры.ОсновнойМенеджер = ЗНАЧЕНИЕ(Справочник.Пользователи.ПустаяСсылка)
            |                           И Пользователи.ТекущееПодразделение В
            |                               (ВЫБРАТЬ
            |                                   СтруктураПредприятия.Ссылка
            |                               ИЗ
            |                                   Справочник.СтруктураПредприятия КАК СтруктураПредприятия
            |                               ГДЕ
            |                                   СтруктураПредприятия.Ссылка В ИЕРАРХИИ (&Подразделение))))) КАК ВыручкаИСебестоимостьПродажОбороты
            |       ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.КлючиАналитикиУчетаНоменклатуры КАК КлючиАналитикиУчетаНоменклатуры
            |       ПО ВыручкаИСебестоимостьПродажОбороты.АналитикаУчетаНоменклатуры = КлючиАналитикиУчетаНоменклатуры.Ссылка
            |       ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.КлючиАналитикиУчетаПоПартнерам КАК КлючиАналитикиУчетаПоПартнерам
            |       ПО ВыручкаИСебестоимостьПродажОбороты.АналитикаУчетаПоПартнерам = КлючиАналитикиУчетаПоПартнерам.Ссылка
            |
            |СГРУППИРОВАТЬ ПО
            |   ВыручкаИСебестоимостьПродажОбороты.Период,
            |   КлючиАналитикиУчетаПоПартнерам.Партнер.ОсновнойМенеджер.Наименование,
            |   КлючиАналитикиУчетаНоменклатуры.Номенклатура.Родитель,
            |   КлючиАналитикиУчетаПоПартнерам.Партнер.ОсновнойМенеджер.ТекущееПодразделение.Наименование + "" ("" + КлючиАналитикиУчетаПоПартнерам.Партнер.ОсновнойМенеджер.ТекущееПодразделение.Код + "")"",
            |   КлючиАналитикиУчетаПоПартнерам.Партнер.Наименование + "" ("" + КлючиАналитикиУчетаПоПартнерам.Партнер.Код + "")""
            |
            |УПОРЯДОЧИТЬ ПО
            |   Ссылка,
            |   Подразделение,
            |   Менеджер,
            |   Партнер
            |;
            |
            |////////////////////////////////////////////////////////////////////////////////
            |ВЫБРАТЬ
            |   Партнеры.Наименование + "" ("" + Партнеры.Код + "")"" КАК Партнер,
            |   Партнеры.ОсновнойМенеджер.Наименование КАК Менеджер,
            |   Партнеры.ОсновнойМенеджер.ТекущееПодразделение.Наименование + "" ("" + Партнеры.ОсновнойМенеджер.ТекущееПодразделение.Код + "")"" КАК Подразделение,
            |   0 КАК Количество,
            |   0 КАК Выручка,
            |   0 КАК Себестоимость,
            |   0 КАК ДопРасходы
            |ИЗ
            |   Справочник.Партнеры КАК Партнеры
            |       ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Пользователи КАК Пользователи
            |       ПО Партнеры.ОсновнойМенеджер = Пользователи.Ссылка
            |ГДЕ
            |   Партнеры.Ссылка В
            |           (ВЫБРАТЬ
            |               ПартнерыСегмента.Партнер
            |           ИЗ
            |               РегистрСведений.ПартнерыСегмента КАК ПартнерыСегмента
            |           ГДЕ
            |               ПартнерыСегмента.Сегмент = &Сегмент)
            |   И НЕ Партнеры.ОсновнойМенеджер = ЗНАЧЕНИЕ(Справочник.Пользователи.ПустаяСсылка)
            |   И Пользователи.ТекущееПодразделение В
            |           (ВЫБРАТЬ
            |               СтруктураПредприятия.Ссылка
            |           ИЗ
            |               Справочник.СтруктураПредприятия КАК СтруктураПредприятия
            |           ГДЕ
            |               СтруктураПредприятия.Ссылка В ИЕРАРХИИ (&Подразделение))";
КонецФункции


&НаСервереБезКонтекста
Функция Transliterate(srtRusWord) Экспорт 
    strRUS = "АБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ"; 
    strENG = "A///B///V///G///D///E///YO//ZH//Z///I///Y///K///L///M///N///O///P///R///S///T///U///F///KH//TS//CH//SH//SHCH'///Y///////E///YU//JA//"; 
    strResult = ""; 
    Для i = 1 по СтрДлина(srtRusWord) Цикл 
        s = Сред(srtRusWord, i,1); 
        s=ВРег(s); 
        k = Найти(strRUS, s); 
        Если k = 0 тогда 
            strResult = strResult + s; 
        Иначе 
            strResult = strResult + СтрЗаменить(Сред(strENG, (k - 1) * 4 + 1, 4), "/", ""); 
        КонецЕсли 
    КонецЦикла ; 
    Возврат СокрЛП(strResult); 
КонецФункции

&НаСервереБезКонтекста
Функция ПолучитьНазваниеДляВыгрузки(НРодитель)
    //Родитель = Справочники.Номенклатура.СоздатьГруппу();
    Если НРодитель=null or НРодитель.Пустая() тогда    
        Возврат "";
    КонецЕсли;
    СтрокаВозврата = "";
    Если Не НРодитель.КодСортировки=0 тогда    
        СтрокаВозврата = СтрокаВозврата + Формат(НРодитель.КодСортировки,"ЧЦ=3; ЧДЦ=0; ЧН=0; ЧВН=; ЧГ=0")+". ";
    КонецЕсли;
    СтрокаВозврата = СтрокаВозврата  + НРодитель.Наименование + " (" + НРодитель.Код+")";
    return СтрокаВозврата;
КонецФункции

&НаСервере
Функция ВыгрузитьФайлик(СтруктФункц) Экспорт
    
    ВремяНачала = ТекущаяДата();
    
    //ИмяФайлаЭксель = "C:\1\"+Transliterate(Подр.Наименование)+".xlsx"; // Имя создаваемого файла
    ИмяФайлаЭксель1 = КаталогВременныхФайлов()+Transliterate(СтруктФункц.Подр.Наименование)+".xlsx"; // Имя создаваемого файла
    ИмяФайлаЭксель = КаталогВременныхФайлов()+Transliterate(СтруктФункц.Подр.Наименование)+"(1).xlsx"; // Имя создаваемого файла
    ИмяФайлаZIP = КаталогВременныхФайлов()+Transliterate(СтруктФункц.Подр.Наименование)+".zip"; // Имя создаваемого файла
    УдалитьФайлы(ИмяФайлаЭксель);
    // Собираем строку подключения
    Command = Неопределено;
    //Connection.Close();
    Connection = Неопределено;
    Table = Неопределено;
    Catalog = Неопределено;
    
    СтрокаПодключения = "
    |Provider=Microsoft.ACE.OLEDB.12.0;
    |Data Source="+ИмяФайлаЭксель+";
    |Extended Properties=""Excel 12.0 Xml;HDR=YES;"";";
    
    
    // Создаем соединение
    Connection = Новый COMОбъект("ADODB.Connection");
    Connection.Open(СтрокаПодключения);
    Command = Новый COMОбъект("ADODB.Command");
    Command.ActiveConnection = Connection;
    Command.CommandType = 1;
    
    
    
    Command.CommandText = "CREATE TABLE [Table]     
    |(  
    |Родитель1 char(120) WITH Compression   
    |,Родитель2 char(120) WITH Compression      
    |,НаправленияДеятельности char(50) WITH Compression
    |,Период date
    |,ПериодМесяц char(7)
    |,ПериодГод int
    |,Подразделение char(50) WITH Compression
    |,Менеджер  char(50)  WITH Compression
    |,Партнер char(50) WITH Compression
    |,Количество float
    |,Отгрузка currency
    |,Себестоимость currency
    |,ДопРасходы currency);";
    
    
    Command.Execute();
    
    // Получаем номенклатуру для выгрузки
    Запрос = Новый Запрос(ВернутьТекстЗапроса());
    ПериодНачала = НачалоДня(СтруктФункц.ДатаНачала);
    ПериодОкончания = КонецДня(СтруктФункц.ДатаОкончания);
    Запрос.УстановитьПараметр("ПериодНачала",ПериодНачала);//НачалоМесяца(ТекущаяДата()));
    Запрос.УстановитьПараметр("ПериодОкончания",ПериодОкончания);
    Запрос.УстановитьПараметр("Организация",Справочники.Организации.НайтиПоНаименованию("Опт"));
    Запрос.УстановитьПараметр("Подразделение",СтруктФункц.Подр);
    Запрос.УстановитьПараметр("ПоказыватьСебестоимость",СтруктФункц.ПоказыватьСебестоимость);
    Запрос.УстановитьПараметр("Сегмент",Справочники.СегментыПартнеров.НайтиПоНаименованию("Рабочие"));
    Результат = Запрос.ВыполнитьПакет();
    Выборка1 = Результат[0].Выбрать(); //Номенклатура
    Выборка2 = Результат[1].Выбрать(ОбходРезультатазапроса.ПоГруппировкам); //  ВыручкаИСебестоимостьПродаж
    ВыборкаОбщий = Результат[2].Выбрать(); // Полный
    
    
    ТаблНоменклатура =  Результат[0].Выгрузить();
    ТаблНоменклатура.Колонки.Добавить("Родитель1",Новый ОписаниеТипов("Строка",, Новый КвалификаторыСтроки(120)));
    ТаблНоменклатура.Колонки.Добавить("Родитель2",Новый ОписаниеТипов("Строка",, Новый КвалификаторыСтроки(120)));
    ТаблНоменклатура.Колонки.Добавить("Группа1",Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
    ТаблНоменклатура.Колонки.Добавить("Группа2",Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
    ТаблНоменклатура.Колонки.Добавить("Количество",Новый ОписаниеТипов("Число",, Новый КвалификаторыЧисла(12,2)));
    ТаблНоменклатура.Колонки.Добавить("Отгрузка",Новый ОписаниеТипов("Число",, Новый КвалификаторыЧисла(12,2)));
    ТаблНоменклатура.Колонки.Добавить("Себестоимость",Новый ОписаниеТипов("Число",, Новый КвалификаторыЧисла(12,2)));
    ТаблНоменклатура.Колонки.Добавить("ДопРасходы",Новый ОписаниеТипов("Число",, Новый КвалификаторыЧисла(12,2)));
    Для Каждого СтрокаНом Из ТаблНоменклатура Цикл
        СтруктураГрупп = ВернутьГруппы(СтрокаНом.Ссылка);
        СтрокаНом.Родитель1 = СтруктураГрупп.Группа1С;
        СтрокаНом.Родитель2 = СтруктураГрупп.Группа2С;
        СтрокаНом.Группа1 = СтруктураГрупп.Группа1О;
        СтрокаНом.Группа2 = СтруктураГрупп.Группа2О;
    КонецЦикла;
    ТаблНоменклатура.Свернуть("Родитель1,Группа1,Родитель2,Группа2","Количество,Отгрузка,Себестоимость,ДопРасходы");
    
    ТаблПродажи =  Результат[1].Выгрузить();
    ТаблПродажи.Колонки.Добавить("Родитель1",Новый ОписаниеТипов("Строка",, Новый КвалификаторыСтроки(120)));
    ТаблПродажи.Колонки.Добавить("Родитель2",Новый ОписаниеТипов("Строка",, Новый КвалификаторыСтроки(120)));
    ТаблПродажи.Колонки.Добавить("Группа1",Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
    ТаблПродажи.Колонки.Добавить("Группа2",Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
    Для Каждого СтрокаПродаж Из ТаблПродажи Цикл
        СтруктураГрупп = ВернутьГруппы(СтрокаПродаж.Ссылка);
        СтрокаПродаж.Родитель1 = СтруктураГрупп.Группа1С;
        СтрокаПродаж.Родитель2 = СтруктураГрупп.Группа2С;
        СтрокаПродаж.Группа1 = СтруктураГрупп.Группа1О;
        СтрокаПродаж.Группа2 = СтруктураГрупп.Группа2О;
    КонецЦикла;
    ТаблПродажи.Свернуть("Период,Менеджер,Подразделение,Партнер,Родитель1,Группа1,Родитель2,Группа2","Количество,Выручка,Себестоимость,ДопРасходы");          

    
    
    
    ПериодОкончанияНД =  НачалоМесяца(ПериодОкончания);
    // Заполняем таблицу данными выборки
    
    Для Каждого СтрокаНом Из ТаблНоменклатура Цикл
        
        ВыборкаОбщий.Сбросить();
        Пока ВыборкаОбщий.Следующий() Цикл
            
            
            Выборка= СоздатьСтруктуру();
            ЗаполнитьЗначенияСвойств(Выборка,СтрокаНом);
            
            ЗаполнитьЗначенияСвойств(Выборка,ВыборкаОбщий);

            Выборка.Период = ПериодОкончанияНД;//ПериодНачала;
            
            Command.CommandText = СформироватьСтрокуЗапроса(Выборка);
            Command.Execute();  
                    
            
        КонецЦикла;
        
        
        
        Для каждого НайденаяСтрока из ТаблПродажи.НайтиСтроки(Новый Структура("Группа1,Группа2",СтрокаНом.Группа1,СтрокаНом.Группа2)) Цикл
            
            Выборка= СоздатьСтруктуру();
            ЗаполнитьЗначенияСвойств(Выборка,СтрокаНом);
            ЗаполнитьЗначенияСвойств(Выборка,НайденаяСтрока);
                        

            Command.CommandText = СформироватьСтрокуЗапроса(Выборка);
            Command.Execute();
        КонецЦикла;
        
    КонецЦикла;
    ВремяОкончания = ТекущаяДата();
    
    Сообщить("Время начала:"+ВремяНачала);
    Сообщить("Время окончания:"+ВремяОкончания);
    // Закрываем соединение
    Command = Неопределено;
    Connection.Close();
    Connection = Неопределено;
    
    
    Excel = новый COMОбъект("Excel.Application");
    Excel.DisplayAlerts=0;
    Excel.Visible = 0;
    Excel.ScreenUpdating = False;
    Excel.EnableEvents = False;

    Книга = Excel.Workbooks.Open(ИмяФайлаЭксель);
    
    
    ОформитьПлан(Excel, Книга);

 
    Книга.Save();
    Excel.Workbooks.Close();
    Excel.Quit();
    Excel=Неопределено;

//  СписокРассылки = Новый СписокЗначений;
//  СписокРассылки.Добавить("dyachok.n");
//  
//  ФайлZip = Новый ЗаписьZipФайла(ИмяФайлаZIP,, "Выгрузка отгрузок", МетодСжатияZIP.Сжатие, УровеньСжатияZIP.Максимальный);
//  ФайлZip.Добавить(ИмяФайлаЭксель,РежимСохраненияПутейZIP.НеСохранятьПути,РежимОбработкиПодкаталоговZIP.НеОбрабатывать);
//  ФайлZip.Записать();
//  ФайлZip = Неопределено;
//  
//  Мас = Новый Массив;
//  //ИмяФайлаНью = КаталогВременныхФайлов()+ИмяФайлаЭксель;
//  
//  Мас.Добавить(ИмяФайлаZIP);
//  
//  
//  //ИмяФайла = КаталогВременныхФайлов()+"Price_"+Формат(ДатаФормированияПрайса,"ДФ=yyyy-MM-dd")+".xls";
//  //Мас.Добавить(ИмяФайла);
//  //ВыгрузитьВXLS(ИмяФайла);
//  ДанныеПисьма = Новый Структура("Тема,Тело","Информация по отгрузке:"+Подр,"Данные выгружены за период с "+Формат(ДатаНачала,"ДФ=dd.MM.yyyy")+" по "+Формат(ДатаОкончания,"ДФ=dd.MM.yyyy"));
//  Отправить(ДанныеПисьма,Мас,СписокРассылки);
//  УдалитьТемпФайл(ИмяФайлаZIP);
//  УдалитьТемпФайл(ИмяФайлаЭксель);
//  УдалитьТемпФайл(ИмяФайлаЭксель1);

    Адрес = ПоместитьВоВременноеХранилище(Новый ДвоичныеДанные(ИмяФайлаЭксель),СтруктФункц.ФормаУИД);
    Возврат Адрес;  
    
КонецФункции

&НаСервереБезКонтекста
Функция rgb(r,g,b)    
    Возврат r+g*256+b*65536; 
КонецФункции 

&НаСервереБезКонтекста
Функция ФЕ(ЧислоЗнч)
    Возврат Формат(ЧислоЗнч,"ЧЦ=10; ЧДЦ=0; ЧГ=0");
КонецФункции


//http://www.tayloredmktg.com/rgb/ - цвета 
&НаСервереБезКонтекста
Процедура ОформитьПлан(Знач Excel, Знач Книга)
    Лист = Книга.WorkSheets("Table"); 
    Лист.Select();
    ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
    ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row; 
    
    
    Лист.Activate();
    
    
    дДиапазонДанных=Лист.UsedRange.Address;
    ДиапазонДанных=Лист.Name+"!"+Строка(дДиапазонДанных);    
    
    xlDatabase = 1;
    
    SourceType = xlDatabase;
    SourceData = ДиапазонДанных;
    

    
    
    TableDestination="";
    TableName="Plan";
    
    
    ЛистА = Книга.Sheets.Add(,Excel.Sheets("Table"));
    Е = Excel.ActiveWorkbook.PivotCaches().Create(xlDatabase, ДиапазонДанных, 4);
    СВТ = Е.CreatePivotTable(ЛистА.Name+"!R3C1", TableName, 4);
    
    СВТ.SubtotalLocation(1);
    СВТ.RowAxisLayout(2);
    СВТ.TableStyle2 = "PivotStyleMedium2";
    
    СВТПодразделение = СВТ.PivotFields("Подразделение");
    СВТПодразделение.Orientation = 1;
    СВТПодразделение.Position = 1;
    
    СВТМенеджер = СВТ.PivotFields("Менеджер");
    СВТМенеджер.Orientation = 1;
    СВТМенеджер.Position = 2;
    
    СВТПартнер = СВТ.PivotFields("Партнер");
    СВТПартнер.Orientation = 1;
    СВТПартнер.Position = 3;
    
    СВТРодитель1 = СВТ.PivotFields("Родитель1");
    СВТРодитель1.Orientation = 1;
    СВТРодитель1.Position = 4;
    
    СВТРодитель2 = СВТ.PivotFields("Родитель2");
    СВТРодитель2.Orientation = 1;
    СВТРодитель2.Position = 5;
    
    СВТПериодМесяц = СВТ.PivotFields("ПериодМесяц");
    СВТПериодМесяц.Orientation = 2;
    СВТПериодМесяц.Position = 1;
    
    СВТ.AddDataField(СВТ.PivotFields("Количество"),"(Кол-во)",-4157);
    СВТ.AddDataField(СВТ.PivotFields("Отгрузка"),"(Отгрузка)",-4157);
    
    ВсегоКолонок1 = ЛистА.Cells(1,1).SpecialCells(11).Column;
    ВсегоСтрок1 = ЛистА.Cells(1,1).SpecialCells(11).Row; 
    
    
    ЛистА.Activate();
    
    Книга.ActiveSheet.Name = "План1";
    дДиапазонДанных=ЛистА.UsedRange.Address;
    ДиапазонДанных=ЛистА.Name+"!"+Строка(дДиапазонДанных);  
    

    
    Excel.Range(ДиапазонДанных).copy();
    ЛистИ = Книга.Sheets.Add(,Excel.Sheets("План1"));
    ЛистИ.Name = "План";
    ЛистИ.Select();
    ЛистИ.Range("A1").Select();
    Excel.Range("План!A1").PasteSpecial(-4163,-4142,false,false);
    
    
    Excel.Sheets("План1").
    delete();   
    Excel.Sheets("Table").delete();
    
    ЛистИ.Outline.SummaryRow = 0; // группировка сверху
    
    ВсегоКолонок = ЛистИ.Cells(1,1).SpecialCells(11).Column;
    ВсегоСтрок = ЛистИ.Cells(1,1).SpecialCells(11).Row; 
    
        
    
    //Дальше формируем группировку
    ОбщСтр = Новый Структура;
    Для х=1 по 4 Цикл
        ОбщСтр.Вставить("Гр"+ФЕ(х),Новый Структура("ТекЗнач,НачЗнач,НачСтрока"));
    КонецЦикла;
    RangeCopy = ЛистИ.Range("$"+КЕ(6)+"$"+ФЕ(1)+":$"+КЕ(6+1)+"$"+ФЕ(ВсегоСтрок));
    RangeCopy.Insert(-4161,0);
    Range = ЛистИ.Range("$"+КЕ(6)+"$"+ФЕ(1)+":$"+КЕ(6+1)+"$"+ФЕ(ВсегоСтрок));
    Range.Interior.Color =  rgb(135,206,250);
    Range.Borders(1).Weight = 2;
    Range.Borders(2).Weight = 2;
    Range.Borders(3).Weight = 2;
    Range.Borders(4).Weight = 2;
    RangePlan = ЛистИ.Range("$"+КЕ(6)+"$"+ФЕ(1)+":$"+КЕ(6+1)+"$"+ФЕ(1));
    RangePlan.Merge();
    RangePlan.Value="План";
    Range = ЛистИ.Range("$"+КЕ(6)+"$"+ФЕ(2)+":$"+КЕ(6+1)+"$"+ФЕ(2));  
    Range.Merge();
    ЛистИ.Cells(3, 6).Value = "(Кол-во)";
    ЛистИ.Cells(3, 7).Value ="(Отгрузка)";
    
    
    Range = ЛистИ.Range("$A$1:$"+КЕ(ВсегоКолонок+2)+"$3");  
    Range.Font.Name = "Arial Narrow";
    Range.Font.Size = 12;
    Range.HorizontalAlignment =3;
    Range.VerticalAlignment  =2;
    Range.Borders(1).Weight = 2;
    Range.Borders(2).Weight = 2;
    Range.Borders(3).Weight = 2;
    Range.Borders(4).Weight = 2;
    Range.Interior.Color =  rgb(54,96,146);
    Range.Font.Color =  rgb(255,255,255);

    
    
    Для СуммКол = 6 по ВсегоКолонок Цикл
        Если  СуммКол%2 тогда
            ЛистИ.Columns(СуммКол).NumberFormat  ="# ##0,00";   
        Иначе
            ЛистИ.Columns(СуммКол).NumberFormat  ="# ##0";  
            Если НЕ СуммКол+2>ВсегоКолонок тогда
                Range = ЛистИ.Range("$"+КЕ(СуммКол)+"$"+ФЕ(2)+":$"+КЕ(СуммКол+1)+"$"+ФЕ(2));  
                Range.Merge();
            КонецЕсли;
        КонецЕсли; 
    КонецЦикла;
    Для СуммКол = ВсегоКолонок по ВсегоКолонок+2 Цикл // Для итогов не объединяем.
        Если  СуммКол%2 тогда
            ЛистИ.Columns(СуммКол).NumberFormat  ="# ##0,00";   
        Иначе
            ЛистИ.Columns(СуммКол).NumberFormat  ="# ##0";              
        КонецЕсли;
    КонецЦикла;
    ВсегоКолонок = ВсегоКолонок+2;

    
    Для СтрокаЕ=4 по ВсегоСтрок Цикл
        Для х=1 по 4 Цикл
            ОбщСтр["Гр"+х].ТекЗнач = ЛистИ.Cells(СтрокаЕ, х).Value;
            Если (Не ПустаяСтрока(ОбщСтр["Гр"+х].ТекЗнач) И  ОбщСтр["Гр"+х].ТекЗнач<>ОбщСтр["Гр"+х].НачЗнач) тогда
                Если НЕ ОбщСтр["Гр"+х].НачЗнач=НЕопределено тогда
                    ЛистИ.Range("A"+ФЕ(ОбщСтр["Гр"+х].НачСтрока+1)+":"+КЕ(х)+ФЕ(СтрокаЕ-1)).Rows.Group();
                    Для у=х+1 по 4 Цикл
                        ЛистИ.Range("A"+ФЕ(ОбщСтр["Гр"+у].НачСтрока+1)+":"+КЕ(у)+ФЕ(СтрокаЕ-1)).Rows.Group();              
                        Для СуммКол = 6 по 8 Цикл
                            ЛистИ.Cells(ОбщСтр["Гр"+у].НачСтрока, СуммКол).FormulaR1C1 = "=SUMIF(R[1]C["+ФЕ(5-СуммКол)+"]:R["+ФЕ(СтрокаЕ-ОбщСтр["Гр"+у].НачСтрока-1)+"]C["+ФЕ(5-СуммКол)+"],""*"",R[1]C:R["+ФЕ(СтрокаЕ-ОбщСтр["Гр"+у].НачСтрока-1)+"]C)";
                        КонецЦикла;
                        ОбщСтр["Гр"+у].НачЗнач = Неопределено;
                        ОбщСтр["Гр"+у].НачСтрока =  СтрокаЕ-1;
                    КонецЦикла;
                    Для СуммКол = 6 по 8 Цикл
                        ЛистИ.Cells(ОбщСтр["Гр"+х].НачСтрока, СуммКол).FormulaR1C1 = "=SUMIF(R[1]C["+ФЕ(5-СуммКол)+"]:R["+ФЕ(СтрокаЕ-ОбщСтр["Гр"+х].НачСтрока-1)+"]C["+ФЕ(5-СуммКол)+"],""*"",R[1]C:R["+ФЕ(СтрокаЕ-ОбщСтр["Гр"+х].НачСтрока-1)+"]C)";
                    КонецЦикла;
                    
                КонецЕсли;
                ОбщСтр["Гр"+х].НачСтрока =  СтрокаЕ;
                ОбщСтр["Гр"+х].НачЗнач =  ОбщСтр["Гр"+х].ТекЗнач;
                Range = ЛистИ.Range("$"+КЕ(х)+"$"+ФЕ(СтрокаЕ)+":$"+КЕ(ВсегоКолонок)+"$"+ФЕ(СтрокаЕ));  
                Range.Font.Size = 16-х;
                Range.Interior.Color =  rgb(255,255,255-(х*10));
            ИначеЕсли  НЕ ПустаяСтрока(ОбщСтр["Гр"+х].НачЗнач) тогда
                //ЛистИ.Cells(СтрокаЕ, х).Value = ОбщСтр["Гр"+х].НачЗнач;
                //ЛистИ.Cells(СтрокаЕ, х).Font.Color= rgb(255,255,255);
            КонецЕсли;
        КонецЦикла;
    КонецЦикла;
    ЛистИ.Cells.EntireColumn.AutoFit();
    
КонецПроцедуры

&НаСервереБезКонтекста
Функция КЕ(Номерколонки) // преобразуем число в колонку excel
    Дивиденд =  Номерколонки;
    Модуль=0;
    СтрВозврата="";
    Пока Дивиденд > 0 Цикл
        Модуль = (Дивиденд-1) % 26;
        Симв = Символ(65+Модуль);
        СтрВозврата = Симв+СтрВозврата;
        Дивиденд = Окр((Дивиденд - Модуль) / 26);
    КонецЦикла;
    Возврат СтрВозврата;
КонецФункции





&НаСервереБезКонтекста
Функция СоздатьСтруктуру()
    Возврат Новый Структура("Код
    |,Наименование              
    |,Родитель1 
    |,Родитель2
    |,Родитель3
    |,Родитель4 
    |,НаправленияДеятельности
    |,Период
    |,Подразделение
    |,Менеджер
    |,Партнер
    |,Количество
    |,Выручка
    |,Себестоимость
    |,ДопРасходы");
КонецФункции



&НаСервереБезКонтекста
Функция СформироватьСтрокуЗапроса(СтруктураВыборки)
    Возврат
    "INSERT INTO [Table] VALUES ("
    +"'"+СтрЗаменить(СтруктураВыборки.Родитель1,"'","''")+"'"    
    +",'"+СтрЗаменить(СтруктураВыборки.Родитель2,"'","''")+"'"       
    +",'"+СтрЗаменить(СтруктураВыборки.НаправленияДеятельности,"'","''")+"'"
    +",'"+СтрЗаменить(Формат(СтруктураВыборки.Период,"ДФ=MM/dd/yyyy"),"'","''")+"'"
    +",'"+СтрЗаменить(Формат(СтруктураВыборки.Период,"ДФ=MM.yyyy"),"'","''")+"'"
    +","+Формат(СтруктураВыборки.Период,"ДФ=yyyy")+""
    +",'"+СтрЗаменить(СтруктураВыборки.Подразделение,"'","''")+"'"
    +",'"+СтрЗаменить(СтруктураВыборки.Менеджер,"'","''")+"'"
    +",'"+СтрЗаменить(СтруктураВыборки.Партнер,"'","''")+"'"
    +","+Формат(СтруктураВыборки.Количество,"ЧЦ=12; ЧДЦ=0; ЧРД=.; ЧН=0; ЧГ=0")+""   
    +","+Формат(СтруктураВыборки.Выручка,"ЧЦ=12; ЧДЦ=2; ЧРД=.; ЧН=0; ЧГ=0")+""
    +","+Формат(СтруктураВыборки.Себестоимость,"ЧЦ=12; ЧДЦ=2; ЧРД=.; ЧН=0; ЧГ=0")+""
    +","+Формат(СтруктураВыборки.ДопРасходы,"ЧЦ=12; ЧДЦ=2; ЧРД=.; ЧН=0; ЧГ=0")+""
    +");";
КонецФункции


&НаКлиенте
Процедура ПриОткрытии(Отказ)
    
КонецПроцедуры

&НаСервере
Процедура ПриСозданииНаСервере(Отказ, СтандартнаяОбработка)
    ЭтаФорма.Подразделение = Справочники.СтруктураПредприятия.НайтиПоНаименованию("Опт");
    ЭтаФорма.ПериодОтчета.Вариант=ВариантСтандартногоПериода.ПрошлыйКвартал; 
КонецПроцедуры

Процедура Отправить(ДанныеПисьма,Вложения,Знач Кому) экспорт
    //для получения доступа к набору свойств для соединения с сервером
    //создадим новый объект
    
    Профиль = Новый ИнтернетПочтовыйПрофиль;
    
    //Пропишем параметры соединения
    //ip адрес или имя SMTP сервера
    Профиль.АдресСервераSMTP = "*********"; 
    
    //Порт SMTP сервера
    Профиль.ПортSMTP = "25";
    
    //Имя пользователя почтового ящика
    Профиль.Пользователь = "user********";
    
    //Пароль доступа к почтовому ящику
    Профиль.Пароль = "*****************";
    
    //При необходимости добавляем аутентификацию
    Профиль.АутентификацияSMTP     = СпособSMTPАутентификации.Login;
    Профиль.ПарольSMTP           = Профиль.Пароль;
    Профиль.ПользовательSMTP     = Профиль.Пользователь; 
    // Создаем объект для работы с почтой
    Почта = Новый ИнтернетПочта;
    
    //Выполняем подключение, согласно указанный параметров
    Попытка
        Почта.Подключиться(Профиль);
    Исключение
        //При подключении возникла ошибка, сообщим
        Сообщить("Ошибка при подключении к серверу" + ОписаниеОшибки());
        Возврат;
    КонецПопытки;
    
    //Создаем новое письмо (сообщение)
    Сообщение = Новый ИнтернетПочтовоеСообщение;
    
    //Указываем отправителя
    Сообщение.Отправитель = "user********";
    Для Каждого ЗначКому из Кому Цикл
        Сообщение.Получатели.Добавить(ЗначКому);
    КонецЦикла;
    //Заполняем получателя
    //Сообщение.Получатели.Добавить("dyachok.n@");   
    
    //Пишем тему письма
    Сообщение.Тема = ДанныеПисьма.Тема;
    
    //Формируем текст письма,указываем тип письма
    Сообщение.Тексты.Добавить(ДанныеПисьма.Тело,ТипТекстаПочтовогоСообщения.HTML);
    
    //При необходимости делаем вложение с файлом
    Если ТипЗнч(Вложения)= Тип("Строка") тогда
        Сообщение.Вложения.Добавить(Вложения);
    ИначеЕсли ТипЗнч(Вложения) = Тип("Массив") тогда
        Для Каждого Файл из Вложения Цикл
            Сообщение.Вложения.Добавить(Файл);
        КонецЦикла;
    КонецЕсли;
    
    //Непосредственно отправка сообщения
    Почта.Послать(Сообщение);
    
    //После отправки закрываем соединение
    Почта.Отключиться();
    Сообщение = Неопределено;
    Почта = Неопределено;
КонецПроцедуры

Процедура УдалитьТемпФайл(ИмяФайла)
    ФайлНаДиске = Новый Файл(ИмяФайла); 
    Если ФайлНаДиске.Существует() тогда
        УдалитьФайлы(ИмяФайла);
    КонецЕсли;
КонецПроцедуры


&НаСервереБезКонтекста
Функция ВернутьГруппы(Знач Ном)
    //Ном = Справочники.Номенклатура.СоздатьЭлемент();
    СтруктураГрупп = Новый Структура("Группа1О,Группа2О,Группа3О,Группа1С,Группа2С,Группа3С");
    Поиск = Ном;    
    ТекУровень = Поиск.Уровень();
    
    Пока Истина Цикл

        Если Поиск.Пустая() тогда
            прервать;
        КонецЕсли;
        
        Если НЕ Поиск.ЭтоГруппа тогда
            Поиск = Поиск.Родитель;
            продолжить;
        КонецЕсли;
        
        ТекУровень = Поиск.Уровень()+1;
        Если ТекУровень = 1 тогда
            СтруктураГрупп.Группа1О = Поиск;
            СтрокаВозврата = "";
            Если НЕ Поиск.КодСортировки=0 тогда
                СтрокаВозврата=СтрокаВозврата+Формат(Поиск.КодСортировки,"ЧЦ=3; ЧДЦ=0; ЧН=0; ЧВН=; ЧГ=0")+". ";     
            КонецЕсли;
            СтруктураГрупп.Группа1С=СтрокаВозврата+Поиск.Наименование + " ("+Поиск.Код +")";
        ИначеЕсли ТекУровень = 2 тогда
            СтруктураГрупп.Группа2О = Поиск;
            СтрокаВозврата = "";
            Если НЕ Поиск.КодСортировки=0 тогда
                СтрокаВозврата=СтрокаВозврата+Формат(Поиск.КодСортировки,"ЧЦ=3; ЧДЦ=0; ЧН=0; ЧВН=; ЧГ=0")+". ";     
            КонецЕсли;
            СтруктураГрупп.Группа2С=СтрокаВозврата+Поиск.Наименование + " ("+Поиск.Код +")";
        ИначеЕсли ТекУровень = 3 тогда
            СтруктураГрупп.Группа3О = Поиск;
            СтрокаВозврата = "";
            Если НЕ Поиск.КодСортировки=0 тогда
                СтрокаВозврата=СтрокаВозврата+Формат(Поиск.КодСортировки,"ЧЦ=3; ЧДЦ=0; ЧН=0; ЧВН=; ЧГ=0")+". ";     
            КонецЕсли;
            СтруктураГрупп.Группа3С=СтрокаВозврата+Поиск.Наименование + " ("+Поиск.Код +")";
        КонецЕсли;
        Поиск = Поиск.Родитель;
    КонецЦикла;
    
    Возврат СтруктураГрупп;
КонецФункции

Wednesday, March 21, 2012

Новый скрипт по реиндексации

Обновленный скрипт по реиндекации

use GlobalBase;
DECLARE @command nvarchar(4000); 

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
IF OBJECT_ID('#work_to_do') IS NOT NULL
    DROP TABLE #work_to_do;
 
 SELECT      'BEGIN TRY ALTER INDEX ALL ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE = ON) END TRY BEGIN CATCH ALTER INDEX ALL ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE=OFF) END CATCH;' AS [Инструкция T-SQL]
INTO #work_to_do
FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp
WHERE afp.database_id = DB_ID()
AND   afp.index_type_desc IN ('CLUSTERED INDEX')
AND   (afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60)
AND   afp.page_count > 12
UNION ALL
SELECT      [Инструкция T-SQL] =
      CASE
            WHEN afp.avg_fragmentation_in_percent >= 15
            OR afp.avg_page_space_used_in_percent <= 60
            THEN 'BEGIN TRY ALTER INDEX [' + i.name + '] ON [' +OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+ OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE = ON) END TRY BEGIN CATCH ALTER INDEX ['+ i.name + '] ON [' +OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+ OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE=OFF) END CATCH;'
            WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
            OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
            THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REORGANIZE;'
      END
FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp
JOIN  sys.indexes AS i
ON    (afp.OBJECT_ID = i.OBJECT_ID AND afp.index_id = i.index_id)
AND   afp.database_id = DB_ID()
AND   afp.index_type_desc IN ('NONCLUSTERED INDEX')
AND   (
            (afp.avg_fragmentation_in_percent >= 10 AND    afp.avg_fragmentation_in_percent < 15)
      OR    (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
      )
AND   afp.page_count > 12
AND   afp.OBJECT_ID NOT IN    (    
                             SELECT      OBJECT_ID
                             FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED')
                             WHERE database_id = DB_ID()
                             AND   index_type_desc IN ('CLUSTERED INDEX')
                             AND   (avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)
                             AND   page_count > 1
                        )
--ORDER BY [Инструкция T-SQL]

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;
FETCH NEXT
           FROM partitions
           INTO @command;
-- Loop through the partitions.
WHILE (@@FETCH_STATUS = 0)
    BEGIN;
        print ''''+(@command)+'''';
        exec(@command)
        

        FETCH NEXT
           FROM partitions
           INTO @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;

Monday, March 12, 2012

Удаление устаревших данных с 1С за определенный период

Иногда просят почистить базу 1С за определенный период, стандартными методами это долгий и хлопотный процесс, используя же mssql получаем быстро и комфортно.

use work_TC_Digma;
Declare @Instruction as varchar(MAX) 
declare @DateB as varchar(MAX);
set @DateB='4012-01-01 00:00:00.000'
Declare TablesAndFields cursor for 
SELECT objects.name as Tablename, isnull(objects1.name,'') as Tablename1, columns.name as columnname 
FROM 
   dbo.sysobjects as objects 
left join dbo.syscolumns as columns 
    on objects.id = columns.id
Left join dbo.sysobjects as objects1
    on objects1.name like objects.name+'%' 
where objects.xtype = 'U'
and columns.xtype = 61 and (columns.name='_Period' or columns.name='_Date_Time')
order by objects.name, objects1.name desc
 
open TablesAndFields
 
Declare @TableName as varchar(100) 
Declare @TableName1 as varchar(100) 
Declare @ColumnName as varchar(100) 
 
FETCH NEXT FROM TablesAndFields 
    into @TableName, @TableName1, @ColumnName
 
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @TableName1 =@TableName
        set @Instruction = 'delete '+@TableName+' where '+@ColumnName+'<'''+@DateB+''''
    ELSE
        set @Instruction = 'delete '+@TableName1 +' where ' + @TableName+'_IDRRef in (select _IDRRef from '+@TableName+' where '+@ColumnName+'<'''+@DateB+''')'
    print @Instruction
    EXEC(@Instruction)
    

   FETCH NEXT FROM TablesAndFields
    into @TableName,@TableName1, @ColumnName
END
 
close TablesAndFields
deallocate TablesAndFields

Tuesday, March 6, 2012

Статья по анализу работы MSSQL

Очень правильная статья по анализу работы скула http://msdn.microsoft.com/ru-ru/magazine/cc135978.aspx

Mssql Regex

столкнулся с проблемой когда в скуле необходимо воспользоваться регулярными выражениями, после не долгого шуршания по гуглу нашел ресурс 1. разрешаем скулу пользоваться Ole Automation Procedures 2. Создаем в скуле необходимые процедуры

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
/*
The OLE Regex Match function
-----------------------------

Let's start off with something simple, a function for testing a string against a regular expression */

IF OBJECT_ID (N'dbo.RegexMatch') IS NOT NULL
   DROP FUNCTION dbo.RegexMatch
GO

CREATE FUNCTION dbo.RegexMatch
    (
      @pattern VARCHAR(2000),
      @matchstring VARCHAR(MAX)--Varchar(8000) got SQL Server 2000
    )
RETURNS INT
/* The RegexMatch returns True or False, indicating if the regular expression matches (part of) the string. (It returns null if there is an error).
When using this for validating user input, you'll normally want to check if the entire string matches the regular expression. To do so, put a caret at the start of the regex, and a dollar at the end, to anchor the regex at the start and end of the subject string.
*/ 
AS BEGIN
    DECLARE @objRegexExp INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(255),
        @hr INT,
        @match BIT

    SELECT  @strErrorMessage = 'creating a regex object'
    EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
        --Specifying a case-insensitive match 
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
        --Doing a Test' 
    IF @hr = 0 
        EXEC @hr= sp_OAMethod @objRegexExp, 'Test', @match OUT, @matchstring
    IF @hr <> 0 
        BEGIN
            RETURN NULL
        END
    EXEC sp_OADestroy @objRegexExp
    RETURN @match
   END
GO

/*

With this function, the passing back of errors is rudimentary. If an OLE error occurs, then a null is passed back.

There are two other basic Regex functions available. With them, you can use regular expressions in all sorts of places in TSQL without having to get to direct grips with the rather awkward OLE interface.

The OLE Regex Replace function
-----------------------------

*/


IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL 
    DROP FUNCTION dbo.RegexReplace
GO
CREATE FUNCTION dbo.RegexReplace
    (
      @pattern VARCHAR(255),
      @replacement VARCHAR(255),
      @Subject VARCHAR(MAX),
      @global BIT = 1,
     @Multiline bit =1
    )
RETURNS VARCHAR(MAX)

/*The RegexReplace function takes three string parameters. The pattern (the regular expression) the replacement expression, and the subject string to do the manipulation to.

The replacement expression is one that can cause difficulties. You can specify an empty string '' as the @replacement text. This will cause the Replace method to return the subject string with all regex matches deleted from it (see "strip all HTML elements out of a string" below). 
To re-insert the regex match as part of the replacement, include $& in the replacement text. (see "find a #comment and add a TSQL --" below)
If the regexp contains capturing parentheses, you can use backreferences in the replacement text. $1 in the replacement text inserts the text matched by the first capturing group, $2 the second, etc. up to $9. (e.g. see import delimited text into a database below) To include a literal dollar sign in the replacements, put two consecutive dollar signs in the string you pass to the Replace method.*/
AS BEGIN
    DECLARE @objRegexExp INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(255),
        @Substituted VARCHAR(8000),
        @hr INT,
        @Replace BIT

    SELECT  @strErrorMessage = 'creating a regex object'
    EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Setting the Regex pattern',
                @objErrorObject = @objRegexExp
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
    IF @hr = 0 /*By default, the regular expression is case sensitive. Set the IgnoreCase property to True to make it case insensitive.*/
        SELECT  @strErrorMessage = 'Specifying the type of match' 
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Doing a Replacement' 
    IF @hr = 0 
        EXEC @hr= sp_OAMethod @objRegexExp, 'Replace', @Substituted OUT,
            @subject, @Replacement
     /*If the RegExp.Global property is False (the default), Replace will return the @subject string with the first regex match (if any) substituted with the replacement text. If RegExp.Global is true, the @Subject string will be returned with all matches replaced.*/   
    IF @hr <> 0 
        BEGIN
            DECLARE @Source VARCHAR(255),
                @Description VARCHAR(255),
                @Helpfile VARCHAR(255),
                @HelpID INT
   
            EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
                @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
            SELECT  @strErrorMessage = 'Error whilst '
                    + COALESCE(@strErrorMessage, 'doing something') + ', '
                    + COALESCE(@Description, '')
            RETURN @strErrorMessage
        END
    EXEC sp_OADestroy @objRegexExp
    RETURN @Substituted
   END
GO

/*
The OLE Regex Find (Execute) function
-----------------------------

This is the most powerful function for doing complex finding and replacing of text. As it passes back detailed records of the hits, including the location and the backreferences, it allows for complex manipulations.

This is written as a table function. The Regex Routine actually passes back a collection for each 'hit'. In the relational world, you'd normally represent this in two tables, so we've returned a left outer join of the two logical tables so as to pass back all the information. This seems to cater for all the uses we can think of. We also append an error column, which should be blank!

*/

IF OBJECT_ID(N'dbo.RegexFind') IS NOT NULL 
    DROP FUNCTION dbo.RegexFind
GO
CREATE FUNCTION RegexFind(
    @pattern VARCHAR(255),
    @matchstring VARCHAR(MAX),
    @global BIT = 1,
   @Multiline bit =1)
RETURNS
    @result TABLE
        (
        Match_ID INT,
          FirstIndex INT ,
          length INT ,
          Value VARCHAR(2000),
          Submatch_ID INT,
          SubmatchValue VARCHAR(2000),
         Error VARCHAR(255)
        )


AS -- columns returned by the function
   BEGIN
    DECLARE @objRegexExp INT,
        @objErrorObject INT,
        @objMatch INT,
        @objSubMatches INT,
        @strErrorMessage VARCHAR(255),
       @error VARCHAR(255),
        @Substituted VARCHAR(8000),
        @hr INT,
        @matchcount INT,
        @SubmatchCount INT,
        @ii INT,
        @jj INT,
        @FirstIndex INT,
        @length INT,
        @Value VARCHAR(2000),
        @SubmatchValue VARCHAR(2000),
        @objSubmatchValue INT,
        @command VARCHAR(8000),
        @Match_ID INT
        
    DECLARE @match TABLE
        (
          Match_ID INT IDENTITY(1, 1)
                       NOT NULL,
          FirstIndex INT NOT NULL,
          length INT NOT NULL,
          Value VARCHAR(2000)
        )    
    DECLARE @Submatch TABLE
        (
          Submatch_ID INT IDENTITY(1, 1),
          match_ID INT NOT NULL,
          SubmatchNo INT NOT NULL,
          SubmatchValue VARCHAR(2000)
        )
       


    SELECT  @strErrorMessage = 'creating a regex object',@error=''
    EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Setting the Regex pattern',
                @objErrorObject = @objRegexExp
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Specifying a case-insensitive match' 
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Doing a match' 
    IF @hr = 0 
        EXEC @hr= sp_OAMethod @objRegexExp, 'execute', @objMatch OUT,
            @matchstring
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Getting the number of matches'     
    IF @hr = 0 
        EXEC @hr= sp_OAGetProperty @objmatch, 'count', @matchcount OUT
    SELECT  @ii = 0 
    WHILE @hr = 0
        AND @ii < @Matchcount
        BEGIN
/*The Match object has four read-only properties. 
The FirstIndex property indicates the number of characters in the string to the left of the match. 
The Length property of the Match object indicates the number of characters in the match. 
The Value property returns the text that was matched.*/
            SELECT  @strErrorMessage = 'Getting the FirstIndex property',
                    @command = 'item(' + CAST(@ii AS VARCHAR) + ').FirstIndex'    
            IF @hr = 0 
                EXEC @hr= sp_OAGetProperty @objmatch, @command,
                    @Firstindex OUT
            IF @hr = 0 
                SELECT  @strErrorMessage = 'Getting the length property',
                        @command = 'item(' + CAST(@ii AS VARCHAR) + ').Length'    
            IF @hr = 0 
                EXEC @hr= sp_OAGetProperty @objmatch, @command, @Length OUT
            IF @hr = 0 
                SELECT  @strErrorMessage = 'Getting the value property',
                        @command = 'item(' + CAST(@ii AS VARCHAR) + ').Value'    
            IF @hr = 0 
                EXEC @hr= sp_OAGetProperty @objmatch, @command, @Value OUT
            INSERT  INTO @match
                    (
                      Firstindex,
                      [Length],
                      [Value]
                    )
                    SELECT  @firstindex + 1,
                            @Length,
                            @Value
            SELECT  @Match_ID = @@Identity         
/*The SubMatches property of the Match object is a collection of strings. It will only hold values if your regular expression has capturing groups. The collection will hold one string for each capturing group. The Count property (returned as SubmatchCount) indicates the number of string in the collection. The Item property takes an index parameter, and returns the text matched by the capturing group. 
*/
            IF @hr = 0 
                SELECT  @strErrorMessage = 'Getting the SubMatches collection',
                        @command = 'item(' + CAST(@ii AS VARCHAR)
                        + ').SubMatches'    
            IF @hr = 0 
                SELECT  @strErrorMessage = 'Getting the number of submatches'     
            IF @hr = 0 
                EXEC @hr= sp_OAGetProperty @objSubmatches, 'count',
                    @submatchCount OUT
            SELECT  @jj = 0 
            WHILE @hr = 0
                AND @jj < @submatchCount
                BEGIN
                    IF @hr = 0 
                        SELECT  @strErrorMessage = 'Getting the submatch value property',
                                @command = 'item(' + CAST(@jj AS VARCHAR)
                                + ')' ,@submatchValue=NULL   
                    IF @hr = 0 
                        EXEC @hr= sp_OAGetProperty @objSubmatches, @command,
                            @SubmatchValue OUT
                    INSERT  INTO @Submatch
                            (
                              Match_ID,
                              SubmatchNo,
                              SubmatchValue
                            )
                            SELECT  @Match_ID,
                                    @jj+1,
                                    @SubmatchValue
                    SELECT  @jj = @jj + 1
                END   
                EXEC @hr= sp_OAGetProperty @objmatch, @command,
                    @objSubmatches OUT     
            SELECT  @ii = @ii + 1
        END
    IF @hr <> 0 
        BEGIN
            DECLARE @Source VARCHAR(255),
                @Description VARCHAR(255),
                @Helpfile VARCHAR(255),
                @HelpID INT
   
            EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
                @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
            SELECT  @Error = 'Error whilst '
                    + COALESCE(@strErrorMessage, 'doing something') + ', '
                    + COALESCE(@Description, '')
        END
    EXEC sp_OADestroy @objRegexExp
     EXEC sp_OADestroy        @objMatch
     EXEC sp_OADestroy        @objSubMatches

INSERT INTO @result
          (Match_ID,
          FirstIndex,
          [length],
          [Value],
          Submatch_ID,
          SubmatchValue,
         error)


    SELECT  m.[Match_ID],
           [FirstIndex],
           [length],
           [Value],[SubmatchNo],
           [SubmatchValue],@error
  FROM    @match m
    LEFT OUTER JOIN   @submatch s
    ON m.match_ID=s.match_ID    
IF @@rowcount=0 AND LEN(@error)>0
INSERT INTO @result(error) SELECT @error
RETURN 
END
GO

Saturday, March 3, 2012

Очень часто возникает ситуации, когда в 1с нужно исправить один уникальный идентификато на другой, особенно когда у вас есть 2 базы с одинаковыми наименование или кодами, этот код поможет быстро и правильно исправить и установить уникальный идентификатор.




use GlobalBase;


--временные таблицы
if OBJECT_id('dbo.#TTable') is not null
    drop table dbo.#TTable;
go


use GlobalBase;
SET NOCOUNT ON
DECLARE @tablename sysname;
DECLARE @columnname sysname;
declare @SRef binary(16);
declare @TRef binary(16);
declare @inSRef binary(16);
declare @inTRef binary(16);
declare @sql nvarchar(max);
declare @sSRef nvarchar(34);
declare @sTRef nvarchar(34);


select TSource._IDRRef as [NewRef], TTarget._IDRRef as [OldRef]
        ,TSource._Description as NEW_Description, TTarget._Description as OLD_Description        
into dbo.#TTable
    from rl_distrib.dbo._Reference82 as TSource
left join GlobalBase.dbo._Reference141 as TTarget on TSource._Description=TTarget._Description collate DATABASE_DEFAULT 
where TSource._IDRRef not in (select _IDRRef from GlobalBase.dbo._Reference141)  and (not TTarget._IDRRef is null)
and TSource._Description not in 
(select _Description from rl_distrib.dbo._Reference82 -- проверка на задвоенные значения
group by _Description
having count(_Description)>1)


    DECLARE cur_name CURSOR SCROLL FOR
        SELECT table_name=sysobjects.name,
            column_name=syscolumns.name
            --datatype=systypes.name,
            --length=syscolumns.length
        FROM sysobjects
        JOIN syscolumns ON sysobjects.id = syscolumns.id
        JOIN systypes ON syscolumns.xtype=systypes.xtype
        WHERE sysobjects.xtype='U' and systypes.name='binary' and syscolumns.length=16 and Right(syscolumns.name,3)='Ref'
        ORDER BY sysobjects.name,syscolumns.colid
    OPEN cur_name
    FETCH NEXT FROM cur_name INTO @tablename, @columnname
    while @@FETCH_STATUS = 0
    BEGIN
        set @sSRef = Convert(nvarchar(34),CONVERT(char(34),@SRef,1));
        set @sTRef = Convert(nvarchar(34),CONVERT(char(34),@TRef,1));
        set @sql = N'update updateTarget set ' + @columnname + N'= tablesource.[NewRef] 
        From 
            GlobalBase.dbo.'+@tablename+N' AS updateTarget
            Join 
            dbo.#TTable as tablesource
                on updateTarget.' + @columnname + N' = tablesource.[OldRef]';
        
        
        
        
        --set @sql = 'select @TRef'
        
        -- EXEC (@sql )
        EXEC sp_executesql @stmt = @sql;

        FETCH NEXT FROM cur_name INTO @tablename, @columnname
        
    END
    CLOSE cur_name
    DEALLOCATE cur_name
if OBJECT_id('dbo.#TTable') is not null
    drop table dbo.#TTable;
go