Пытливые умы экономистов как-то раз разродились идеей, построить отчет, в котором по каждой номенклатурной позиции вычислялись бы три последние цены одного типа цен и последнее значение другого типа цен. Все это приводилось бы к единой валюте (по курсам на дату установки цены) и неким образом далее анализировалось. Для реализации этого алгоритм средствами 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. |
Теперь про индексы. Их три.
- PK__1SCONST. Индекс по ключевому полю ROW_ID.
- IDD. Самый для нас интересный индекс. Используется для сортировки периодических реквизитов в хронологическом порядке. Состав: ID, OBJID, DATE, TIME, DOCID, ROW_ID. Т.е. внутри одной даты периодические реквизиты сортируются сначала по времени, потом ID документа.
- 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
Надеюсь, вы найдете здесь что-то новое и интересное. С удовольствием отвечу на ваши вопросы.
