Каталог решений - Работа с периодическими реквизитами 1С версии 7.7 при помощи прямых запросов.

             Пытливые умы экономистов как-то раз разродились идеей, построить отчет, в котором по каждой номенклатурной позиции вычислялись бы три последние цены одного типа цен и последнее значение другого типа цен. Все это приводилось бы к единой валюте (по курсам на дату установки цены) и неким образом далее анализировалось. Для реализации этого алгоритм средствами 1С требовалась бы активная работа с объектом «Периодический» и время формирования такого отчета по тысяче-двум номенклатурных позиции затянулось бы на неопределенное время. Как хорошо, что база на SQL сервере, да еще и 2005-ом. Реализация этого отчета на прямых запросах и побудила меня поделиться знаниями.

            При описании буду рассчитывать на то, что читателю знакомы основы языка SQL. Большинство примеров, приведенных здесь, работают на 2000-ом сервере. Для некоторых примеров будет приведено два решения с пояснениями: для 2000-ого сервера и для 2005-ого. Для работы с прямыми запросами я предпочитаю внешнюю компоненту 1С++ и все примеры будут приведены для нее. Думаю, что переписать код для работы с другими компонентами особого труда составить не должно. Все примеры буду приводить для стандартной конфигурации «Торговля и склад 9.2», т.к. она большинству должна быть хорошо знакома.

            Хочу заметить, что язык SQL позволяет одни и те же задачи решать многими способами. Поэтому приведенные здесь запросы не претендуют на то, чтобы считаться единственно верными и оптимальными. Кто-то может предложить другие варианты решения каких-то задач, предлагайте, обсудим.

            Для начала давайте рассмотрим то, как 1С хранит периодические реквизиты. Все значения всех реквизитов хранятся в одной таблице _1SCONST. Если заглянуть в 1Cv7.dds, то там можно найти описание полей таблицы и ее индексов. Рассмотрим интересующие нас и самые важные.

Теперь про индексы. Их три.

1. PK__1SCONST. Индекс по ключевому полю ROW_ID.
2. IDD. Самый для нас интересный индекс. Используется для сортировки периодических реквизитов в хронологическом порядке. Состав: ID, OBJID, DATE, TIME, DOCID, ROW_ID. Т.е. внутри одной даты периодические реквизиты сортируются сначала по времени, потом ID документа.
3. DOC. Служит для отбора значений периодических реквизитов, установленных документом. Состав: DOCID, ACTNO, ROW_ID.

Начнем с самой простой задачи: посмотреть историю изменения розничной цены для конкретного товара. Итак, допустим, в переменной ТекНоменклатура у нас хранится ссылка на элемент справочника номенклатуры. Сперва, чтобы не нагружать запрос, найдем элемент справочника «Цены», соответствующий розничному типу цен:

       спрЦены=СоздатьОбъект(«Справочник.Цены»);

       спрЦены.ИспользоватьВладельца(ТекНоменклатура);

       спрЦены.НайтиПоРеквизиту(«ТипЦен»,Константа.РозничныйТипЦен,0);

Ссылка на элемент справочника, ID которого должно стоять в поле OBJID таблицы _1SCONST у нас, можно сказать, есть. Значение для поля ID можно получить использую объект «MetaDataWork». Например, вот так:

мд=СоздатьОбъект(«MetaDataWork»);

ИДРеквизита=мд.ИДОбъекта(метаданные.Справочник(«Цены»).Реквизит(«Цена»));

Теперь процедура для выборки всей истории розничной цены для товара ТекНоменклатура будет выглядеть так:

Процедура Сформировать()

       ЗагрузитьВнешнююКомпоненту(«1cpp.dll»);

       спрЦены=СоздатьОбъект(«Справочник.Цены»);

       спрЦены.ИспользоватьВладельца(ТекНоменклатура);

       спрЦены.НайтиПоРеквизиту(«ТипЦен»,Константа.РозничныйТипЦен,0);

       мд=СоздатьОбъект(«MetaDataWork»);

       рс=СоздатьОбъект(«ODBCRecordSet»);

       текст=»

       |select * from _1sconst (nolock)

       |where objid=:ТекЦена

       |      and id=:ИДЦена«;

       рс.УстановитьТекстовыйПараметр(«ТекЦена»,спрЦены.ТекущийЭлемент());

       рс.УстановитьТекстовыйПараметр(«ИДЦена»,мд.ИДОбъекта(метаданные.Справочник(«Цены»).Реквизит(«Цена»)));

       тз=рс.ВыполнитьИнструкцию(текст);

       тз.выбратьстроку();

КонецПроцедуры

В этом примере мы выбрали все поля из таблицы _1SCONST. Вид не очень удобный. Чуть изменим текст запроса, чтобы выводилась цена и дата ее установки.

       текст=»

       |select cast(value as numeric(14,2)) Цена

|      ,date Дата

|from _1sconst (nolock)

       |where objid=:ТекЦена

       |      and id=:ИДЦена«;

Преобразование типов при работе с полем value – важный момент. Т.к. поле строковое, а в нем может храниться число, то лучше, если есть возможность, явно указывать преобразование типов для того, чтобы избежать проблем.

Теперь перейдем к наиболее распространенной задаче. Определение значения периодического реквизита на указанную дату. Как не сложно догадаться, периодическое значение в базе скорее всего не хранится на интересующую нас дату. Поэтому придется фильтровать выборку не по равенству даты, а по неравенству. Значения даты должны быть меньше либо равно интересующего нас. Из полученной выборки нужно отобрать значение, соответствующее максимальной дате. Для этого удобнее всего воспользоваться конструкцией «Select top 1 … order by …» с сортировкой в обратном хронологическом порядке, не забывая, что значения могут устанавливаться документами внутри одного дня. Примерно так:

select top 1 cast(value as numeric(14,2)) Цена

from _1sconst (nolock)

where objid=:ТекЦена

       and id=:ИДЦена

and date<=:ЗначениеНаДату

order by date desc, time desc, iddoc desc, row_id desc

Зачем все это писать, если 1С++ поддерживает виртуальное значение «$ПоследнееЗначение»? Например, выше приведенный запрос можно переписать примерно так:

       select $ПоследнееЗначение.Цены.Цена(:ТекЦена,:ЗначениеНаДату) Цена

результат тот же. Если выполнять этот запрос с включенной отладкой, то будет видно, что он развернется в практически точную копию приведенного мной. Да, виртуальные значения – это наглядно, удобно. Но не всегда позволяет решить поставленные задачи. А если надо, например, знать дату установки последней цены? Тогда «плюшки» 1С++ уже не помогают, надо писать вручную, например, так:

select top 1 cast(value as numeric(14,2)) Цена

       ,date Дата

from _1sconst (nolock)

where objid=:ТекЦена

       and id=:ИДЦена

and date<=:ЗначениеНаДату

order by date desc, time desc, iddoc desc, row_id desc

            Далее приведу несколько примеров решения разных задач по периодическим реквизитам при помощи прямых запросов.

1.      Выбрать все товары, у которых когда-либо какая-либо из цен была установлена равной нулю. Вывести товар, тип цен.

                Select СпрЦ.parentext [Тов $Справочник.Номенклатура]

             ,$СпрЦ.типЦен [ТипЦен $Справочник.ТипыЦен]

       from

       (

             select distinct objid

             from _1sconst (nolock)

             where id=:ИДЦена

                    and cast(value as numeric(14,2))=0

       ) const

       inner join $Справочник.Цены СпрЦ (nolock) on СпрЦ.id=const.objid

2.      Удалить из истории цен все нулевые цены.

delete from _1sconst where id=:ИДЦена and cast(value as numeric(14,2))=0

3.      Удалить из истории все значения всех реквизитов, установленные раньше определенной даты

delete from _1sconst where date<:НаДату and objid<>$ПустойИД

4.      Удалить из истории все цены и единицы измерения по типу цен «Розничные», значения которых были установлены вручную.

delete

from _1sconst

inner join $Справочник.Цены СпрЦ (nolocok) on СпрЦ.id=_1sconst.objid

and $СпрЦ.типЦен=:ПоТипуЦен

       and _1sconst.id in (:ИДЦена, :ИДЕдиница)

       and docid=$ПустойИД

Напомню, что

ИДЦена=мд.ИДОбъекта(метаданные.Справочник(«Цены»).Реквизит(«Цена»))

ИДЕдиница =мд.ИДОбъекта(метаданные.Справочник(«Цены»).Реквизит(«Единица»))

5.      Рассчитать все значения всех цен и единиц измерения всех ТМЦ на дату. Полезно при чистке истории и удалении устаревших значений. Тут несколько вариантов запросов может быть.

5.1.   select b.objid, b.id, b.value, b.tvalue

       from

       (

             select objid,id

,max(substring(convert(varchar,date,120),1,10)

+right(‘0000000000’+cast(time as varchar),10)

+docid

+right(‘0000000000’+cast(row_id as varchar),10)) md

             from _1sconst (nolock)

             where objid<>$ПустойИД and id in (:ИДЦена, :ИДЕдиница) and date<:НаДату

             group by objid,id

       ) a

       inner join

       (

             select objid,id

,substring(convert(varchar,date,120),1,10)

+right(‘0000000000’+cast(time as varchar),10)

+docid

+ right(‘0000000000’+cast(row_id as varchar),10) d

, value, tvalue

             from _1sconst (nolock)

             where objid<>$ПустойИД and id in (:ИДЦена, :ИДЕдиница) and date<:НаДату

       ) b

   on a.objid=b.objid and a.id=b.id and a.md=b.d

Здесь использован метод «склеивания» нескольких полей для нахождения максимума. Т.е. поля DATE, TIME, DOCID и ROW_ID собраны в одну строку. Последнее в алфавитном порядке значение этой строки будет соответствовать последнему значению. Если пойти дальше, то можно переписать этот запрос со всего одним проходом по таблице констант.

5.2.

       select objid, id

             ,reverse(substring(reverse(md),4,charindex(char(3),reverse(md))-4)) value

             ,reverse(substring(reverse(md),1,3)) tvalue

       from

       (

             select objid,id,

   max(substring(convert(varchar,date,120),1,10)

       +right(‘0000000000’+cast(time as varchar),10)+docid

+right(‘0000000000’+cast(row_id as varchar),10)

+char(3)+value+tvalue) md

             from _1sconst (nolock)

             where objid<>$ПустойИД and id in (:ИДЦена, :ИДЕдиница) and date<:НаДату

             group by objid,id

       ) a

Идея – к строковому полю «приклеить» нужные нам значения через символ-разделитель (в данном случае – char(3)), а после нахождения максимума из полученной строки вырезать значения value и tvalue.

5.3. Для тех, кто работает с 2005-ым сервером все упрощается.

       select objid, id, value, tvalue from

       (

             select objid,id,value,tvalue

,row_number() over(partition by id,objid

order by date desc, time desc

, docid desc, row_id desc) rn

             from _1sconst (nolock)

             where objid<>$ПустойИД and id in (:ИДЦена, :ИДЕдиница) and date<:НаДату

       ) a

       where rn=1

6.      Удаление дублирующих значений реквизитов. Например, история реквизита выглядит так:

10

10 <- ненужная запись

20

20 <- ненужная запись

30

20

Т.е. запись, value которой равно value предыдущей записи, не нужна. Предыдущая запись – это запись с максимальной датой меньше текущей. Поиск предыдущей записи в реализации для 2000-ого сервера не эффективный, медленный. Готовый запрос:

delete from _1sconst where row_id in

(

select row_id from

(

     select *,

        (

               select top 1 value+tvalue

               from

               (

                    select row_id, objid, id, value, tvalue,

substring(convert(varchar,date,120),1,10)

+right(‘000000000’+cast(time as varchar),10)+docid

+right(‘000000000’+cast(row_id as varchar),10) ind

                    from _1sconst (nolock)

               ) c

               where c.objid=a.objid

                      and c.id=a.id

                      and c.ind<a.ind

               order by c.ind desc

        ) pred

     from

      (

        select row_id, objid, id, value, tvalue, date,

               substring(convert(varchar,date,120),1,10)

+right(‘000000000’+cast(time as varchar),10)+docid

               +right(‘000000000’+cast(row_id as varchar),10) ind

        from _1sconst (nolock)

where objid<>$ПустойИД

     ) a

) a

where pred is not null and value+tvalue=pred

)

Реализация запроса для 2005-ого сервера, благодаря функциям ранжирования, выглядит значительно проще и работает значительно быстрее.

delete from _1sconst where row_id in

(

select row_id from

(

       select *, rn—rnk razn

, row_number() over(partition by id,objid,value+tvalue,rn—rnk

order by date,time,docid,row_id) por_nomer

from

       (

             select *

                    , dense_rank() over(partition by id,objid,value+tvalue

order by date,time,docid,row_id) rnk

                    , row_number() over(partition by id,objid

order by date,time,docid,row_id) rn

             from _1sconst (nolock)

             where objid<>‘     0   ‘

       ) a

) a

where por_nomer>1

)

7.      Как одним запросом получить розничные цены, единицы измерения на указанную дату и курсы валют цены на дату ее (цены) установки? Приведу реализацию для 2005-ого сервера. Для 2000-ого у меня получается большой и не понятный запрос, хотя вполне работоспособный.

            select тов [тов $Справочник.Номенклатура]

             ,Цена

             ,ДатаУстановкиЦены

             ,Единица [Единица $Справочник.Единицы]

             ,Вал [Валюта $Справочник.Валюты]

             ,Курс

       from

       (

             select a.*, cast(b.value as numeric(10,4)) курс

                    ,row_number() over(partition by a. тов

order by b.date desc, b.time desc, b.docid desc, b.row_id desc) rn2

             from

             (

                    select Тов, Вал

                       ,max(case when id=:ИДЦена then cast(value as numeric(14,2)) end) Цена

                       ,max(case when id=:ИДЦена then date end) ДатаУстановкиЦены

                       ,max(case when id=:ИДЕдиница then left(value,8) end) Единица

                    from

                    (

                       select _1sconst.value, $СпрЦ.валюта Вал

                           , objid, _1sconst.id, date, СпрЦ.parentext Тов

                          ,row_number() over(partition by _1sconst.id, _1sconst.objid

                          order by date desc, time desc, docid desc, _1sconst.row_id desc) rn

                       from _1sconst (nolock)

                       inner join $Справочник.Цены СпрЦ (nolock) on СпрЦ.id=_1sconst.objid

                       where _1sconst.id in (:ИДЦена, : ИДЕдиница)

                          and date<=:ВыбДата

                          and $СпрЦ.ТипЦен=:РозничныйТипЦен

                    ) a

                    where rn=1

                    group by Тов, Вал

             ) a        

             left join _1sconst b (nolock)

             on a. Вал=b.objid and b.date<=a. ДатаУстановкиЦены

             where b.id=:ИДКурс

       ) a

       Where rn2=1

Надеюсь, вы найдете здесь что-то новое и интересное. С удовольствием отвечу на ваши вопросы.