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

23.12.09

Разработка - Запросы

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

             Пытливые умы экономистов как-то раз разродились идеей, построить отчет, в котором по каждой номенклатурной позиции вычислялись бы три последние цены одного типа цен и последнее значение другого типа цен. Все это приводилось бы к единой валюте (по курсам на дату установки цены) и неким образом далее анализировалось. Для реализации этого алгоритм средствами 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С v7.7 Платформа 1С v8.3 Бесплатно (free)

Очень часто нужно при работе с HTTP сервисами или сайтами использовать Асинхронные HTTP запросы, отправку на сервер нескольких файлов, использование сжатия трафика. Эта статья про то, как этого легко добиться.

09.03.2016    38279    Serginio    22    

44

Универсальные функции Запросы Программист Платформа 1С v7.7 Платформа 1С v8.3 Бесплатно (free)

На эту тему уже есть статьи, но этот способ нигде не описан. Хотя я его использую с тех пор, как занимаюсь программированием. Его преимущество в простоте и универсальности: можно применять на 1С, SQL, а также в любом другом языке программирования.

05.07.2015    21961    json    3    

22

Запросы Программист Платформа 1С v7.7 Конфигурации 1cv7 Абонемент ($m)

Пример получения остатков по складу по запросу по почте из программы 1С 7.7. Для получения остатков необходимо пользователю с любого почтовика (с любого "мыла") отправить текст сообщения GiveMyStockBalance_ForAnalize на почтовый адрес определенный в Константа.СерверПолучения. Программа выдаст остатки (можно переписать функцию для выдачи любых данных) в формате xls на почту указанную в константе Константа.СерверОтправки. Программа может быть полезна в тех организациях где трудно или невозможно осуществить прямой доступ к 1С сотрудников для просмотра необходимых данных. Также можно организовать некий почтамт - запрос для клиентов - при посылке определенного логина клиентом на его почту будет автоматически выслана информация, например, акт сверки с клиентов, или процент выполнения его заказа и т.д.

3 стартмани

25.03.2014    25305    5    protexprotex    3    

5

Зарплата Запросы Программист Расчет 7.7 1С:Зарплата и кадры 7.7 Абонемент ($m)

Обзор методов реализации условий и функций с условиями в обращениях к журналу расчетов при помощи прямого запроса.

1 стартмани

06.12.2012    14215    nicotin    5    

9

Запросы Платформа 1С v7.7 Оперативный учет 7.7 Бухгалтерский учет 7.7 Расчет 7.7 Конфигурации 1cv7 Абонемент ($m)

Универсальный отчет для конфигураций на платформе 1С 7.7. Умеет обращатся к справочникам, документам, регистрам и журналам расчетов. Удобен для быстрого получения каких либо данных, которые можно получить посредствам запроса. Предусмотренна возможность сохранения настроек.

1 стартмани

14.04.2012    34146    305    set2333    16    

11
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. Арчибальд 2708 23.12.09 14:34 Сейчас в теме
2. highlander 24.12.09 10:30 Сейчас в теме
Почерпнул для себя много нового и интересного - плюс однозначно
3. flo 30.12.09 09:59 Сейчас в теме
(3)согласна тут ооочень много нового...и такооого интересного!еле оторвалась) мозгами не поделитесь?))))
4. waol 315 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 - не могу добиться оптимизации
7. glassman 208 30.12.09 23:43 Сейчас в теме
(4) Так делать не стоит. Запутывание оптимизатора получится. Он не поймет, что отбор по первым полям индекса идет и пойдет его весь сканить. Плюс дополнительные вычисления на формирование строки, плюс оператор like - не быстрая штука. Во-вторых, если юзать like, то подчеркивания не нужны. Достаточно просто поставить %. Что-то типа такого: like :ИДЦена+:ТекЦена+"%"
8. glassman 208 30.12.09 23:55 Сейчас в теме
(4) Хотя для дбф не могу точно сказать. Не делал прямых запросов к дбфной базе.
По поводу индексов еще. Жаль, что IDD не кластерный... Если планы запросов посмотреть, то там постоянно идет дополнительное обращение к РК по row_id. То же самое и для справочников.
5. Gluk_1C 30.12.09 14:20 Сейчас в теме
Гдеж ты раньше то был :) :D, однозначно + автору
6. Gluk_1C 30.12.09 14:21 Сейчас в теме
+ отдельное спасибо за варианты для 2000 и 2005 сервера.
9. waol 315 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 86 14.01.10 17:03 Сейчас в теме
Оставьте свое сообщение