gifts2017

Работа с периодическими реквизитами 1С версии 7.7 при помощи прямых запросов.

Опубликовал Дмитрий Глазырин (glassman) в раздел Программирование - Практика программирования

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

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

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

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

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

ROW_ID

Автоинкрементное поле типа INT. Служит для поддержки уникальности

OBJID

ID элемента справочника, для которого установлено периодическое значение. Если равно «     0   », то это константа.

ID

Числовое ID реквизита справочника, для которого установлено периодическое значение.

DATE

Дата установки периодического значения. Тип: DATETIME

VALUE

Собственно, само значение периодического реквизита. Обратим внимание на тип. Это строка переменной длины, максимальная длина 255.

DOCID

ID документа, установившего значение периодического реквизита. При ручной установке значений равен «     0   ».

TIME

Числовое представление времени документа. Если посмотреть в таблицу журналов _1sjourn, то в поле DATE_TIME_IDDOC между датой и первым пробелом то же самое время, только в 36-ричной системе. Для перевода числа из поля TIME в формат «ЧЧ:ММ:СС» можно воспользоваться следующей конструкцией:

cast(floor(time/36000000.0) as varchar)+':'

+cast(floor((time%36000000)/600000.0) as varchar)+':'

+cast((time%36000000)%600000/10000 as varchar)

TVALUE

Строка длиной 3 символа. Заполняется в случае, если периодический реквизит неопределенного типа. В нем хранится ID типа значения из поля VALUE.

 

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

  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

 

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

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Александр Рытов (Арчибальд) 23.12.09 14:34
2. highlander highlander (highlander) 24.12.09 10:30
Почерпнул для себя много нового и интересного - плюс однозначно
3. flo flo (flo) 30.12.09 09:59
(3)согласна тут ооочень много нового...и такооого интересного!еле оторвалась) мозгами не поделитесь?))))
4. Олег Валуйский (waol) 30.12.09 14:00
есть ли смысле переписать
текст="
|select * from _1sconst (nolock)
|where objid=:ТекЦена
| and id=:ИДЦена";

в таком виде:
текст="
|select * from _1sconst (nolock)
|where id+objid+... остальн.части индекса IDD like :ИДЦена+:ТекЦена+"__________" (кол.прочерков д.быть таким, чтобы привести правую часть к длине индекса)?

нет достаточного опыта работы с sql;
вообще говоря, интересуют запросы к 1sconst в dbf с использованием индекса idd - не могу добиться оптимизации
5. Юрий Наливко (Gluk_1C) 30.12.09 14:20
Гдеж ты раньше то был :) :D, однозначно + автору
6. Юрий Наливко (Gluk_1C) 30.12.09 14:21
+ отдельное спасибо за варианты для 2000 и 2005 сервера.
7. Дмитрий Глазырин (glassman) 30.12.09 23:43
(4) Так делать не стоит. Запутывание оптимизатора получится. Он не поймет, что отбор по первым полям индекса идет и пойдет его весь сканить. Плюс дополнительные вычисления на формирование строки, плюс оператор like - не быстрая штука. Во-вторых, если юзать like, то подчеркивания не нужны. Достаточно просто поставить %. Что-то типа такого: like :ИДЦена+:ТекЦена+"%"
8. Дмитрий Глазырин (glassman) 30.12.09 23:55
(4) Хотя для дбф не могу точно сказать. Не делал прямых запросов к дбфной базе.
По поводу индексов еще. Жаль, что IDD не кластерный... Если планы запросов посмотреть, то там постоянно идет дополнительное обращение к РК по row_id. То же самое и для справочников.
9. Олег Валуйский (waol) 31.12.09 00:09
да, вы правы, между подчеркиваниями и % действительно нет разницы по скорости, только второе - легче в написании, учту.
вот по поводу поведения дбф такой вариант фильтра

Константы.id+Константы.objid+dtos(Константы.date)+Константы.time+Константы.docid+str(Константы.partno,3) like $ИсторияРеквизита.Цены.Цена+:ВыбФильтр+'%'

выполняется порядка 2-3 мс,
а такой:
Константы.id=$ИсторияРеквизита.Цены.Цена and Константы.objid=:ВыбФильтр

порядка 280 мс.
видимо у Fox Pro своя специфика работы с индексами в сравнении с sql


правда мне никак не добиться, чтобы индекс начал работать, когда есть соединение 1sconst с какой нибудь другой таблицей, с тем же справочником Цены.

но это несколько не по теме, извиняюсь. Буду поднимать тему на 1cpp.ru


10. Сергей Солнышкин (Myti) 14.01.10 17:03
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа