Подсистема документов и регистров.

Предисловие

    Данная статья предназначена для тех, кто пытается разобраться в структуре хранения данных в системе 1С версии 7.7. А также покажет как можно получать данные напрямую из таблиц 1С, минуя программу 1С. Для понимания о чем идет речь в статье необходимо понимать принципы работы 1С версии 7.7 и иметь начальные навыки работы с SQL Server Enterprise Manager-ом и SQL Server Query Analyzer-ом.

1. Введение

    Следует учесть, что в статье рассматривается только примеры для SQL формата базы данных. Для DBF формата есть некоторые особенности и не все запросы буду идентичны как для DBF, так и для SQL формата баз. В любом случае для выполнения запросов к DBF необходимы или ODBC или OLEDB драйвера. Можно использовать любой драйвер, работающий с DBASE2 форматом DBF. Опыт показывает, что наиболее сопоставимым по тексту запросов есть драйвера Visual FoxPro (ведь что и MS SQL Server и MS Visual FoxPro принадлежат одной фирме и есть вероятность что тексты запросов будут унифицироваться в будущем).  Я советую использовать дврайвер OLE DB Visual FoxPro 9.0, так как в нем меньше ограничений и поддерживает больше функций и методов в отличие от ODBC Visual FoxPro 6.0. Скачать посследний драйвер (OLE DB Visual FoxPro 9.0) можно вот по этой ссылке:
http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en
или же по этой:
http://metaprog.km.ru/secrprog/files/vfpoledb.exe
    Для того чтобы получить ID имен таблиц достаточно просмотреть файл КаталогоИБ()+"1cv7.dds" ("1cv7.dd" для DBF формата базы).
Для получения ID объектов 1С, можно воспользоваться компонентой 1С++ (http://www.1cpp.ru) позволяющей получать ID объектов 1С прямо из 1С (например преобразовывание значение ТекущийДокумент() в его ID (строка(9)) или в длинную строку ИД (строка(13)) включая вид документа, или самый длинный ID, длиной 23 символа (строка(23)) включая ID типа, вида и самого объекта).
Также получение ID объектов рассмотрено в этой статье:
http://www.sinor.ru/~my1c/knowhow/get_id.html
Как формируется ID код можно почитать вот по этой ссылке: Общее описание хранения данных в 1С
.

Общее описание хранения данных 1С в подсистеме документов

    Для начала рассмотрим диаграмму связей таблиц подсистемы оперативного учета (диаграмма не полная, но отражает тот минимум, который необходим для получения выборок по регистрам или документам).
Структура связей таблиц
Рис. 1 «Приблизительная схема связей таблиц 1С подсистемы документов и регистров».

1. Краткое описание таблиц

DH - таблицы документов (реквизитов шапки). Создаются при первом добавлении реквизита шапки в документ.
DT – таблицы документов (реквизитов табличной части). Создаются при первом добавлении реквизита табличной части в документ.
_1SJOURN – таблица документов (общих и системных реквизитов). Именно это и есть таблица полного журнала документов. Все остальные журналы формируются по полному журналу с дополнительным отбором по типам документов.
_1SCRDOC – Таблица подчиненных документов. В этой таблице хранятся ссылки документов-родителей на подчиненные документы.
    В системе 1С 7.7 документы всегда хранятся максимум в 2 таблицах (т.е. табличная часть может быть только одна, и все данные табличной части хранятся в этой таблице) (за исключением общин реквизитов, которые хранятся в одной обще таблице).
    Данные документов хранятся в 2 таблицах: DHххх и DTххх, где ххх – десятичный идентификатор вида документа (так как в файле 1cv7.MD). Непосредтсвенные номера таблиц документов можно посмотреть в файле 1cv7.dds (для dbf версии - 1cv7.dd).
    В таблице DHххх – хранятся данные реквизитов шапки (за исключением общих реквизитов). Т.е. на каждый реквизит отведено минимум одно поле таблицы (может быть и 2 в случае неопределенного реквизита). Также в этой таблице хранятся данные по некоторым реквизитам табличной части (рассмотрим позже). В таблице DTххх – хранятся данные реквизитов табличной части (каждый реквизит минимум одно поле таблицы плюс системное поле LINENO_ - номер строки документа). Отдельно следует рассмотреть реквизиты табличной части типа (Число в понимании 1С), по которым установлен флаг «Итог по колонке» в свойствах реквизита. Итог по этим реквизитам храниться в таблице реквизитов шапки (DHххх), причем название поля совпадает для таблицы шапки (DH) и для таблицы табличной части (DT).
    В таблице реквизитов шапки хранятся все реквизиты за исключением общих реквизитов и атрибутов документов (также и системных, таких как время, флаг проведения и удаления). Они все хранятся в одной общей таблице журналов _1SJOURN (1SJOURN для dbf). Описание полей таблицы _1SJOURN можно посмотреть здесь: Описание общих таблиц 1С V77.
    Таблица журналов _1SJOURN, таблица реквизитов шапки DHххх и таблица реквизитов табличной части DTххх «связаны» по полю IDDOC. Дело в том, связь только «мнимая» (т.е. значения полей как бы равны для соответствующих строк), но это не стандартные святи SQL.
Поэтому необходимо учесть, что 1С не создает связей для ограничения целостности данных (связи между полями) средствами MS SQL. Также особое внимание надо уделить тому, что все поля создаются как NOT NULL, т.е. в них нельзя записывать значения типа NULL (видимо поэтому и нет связей). И если значение пустое (в понимании SQL должно быть NULL), то 1С заполняет это поле таким образом:
  • Для неопределенного типа: '   ' (3 пробела), при этом связанное поле TSP заполняется значением  'U                      '.
  • Для типа «число»: 0 (нулем).
  • Для типа «строка»: '          ' (строка с пробелами на всю размерность поля, в примере -10 пробелов на значение Строка(10)).
  • Для типа «дата»: 01.01.1753. Именно эта дата является началом отсчета дат для типов smalDataTime в SQL.
  • Для типа «Справочник», «Документ», «Счет», «Календарь», «ВидРасчета» неопределенного вида: '   0     0   ' (строка(13)).
  • Для типа «Справочник», «Документ», «Счет», », «ВидРасчета» определенного вида, «Перечисление»: '     0   ' (строка(9)).
  • Для типа «Планы счетов», «Виды Субконто» неопределенного вида: '   0     ' (строка(9)). Бухгалтерская подсистема будет рассмотрена в следующей статье.

Примеры запросов

Пример 1. 

    Получение всех документов одного вида со всеми реквизитами шапки (кроме общих):
Код:
SELECT * FROM DH14


Пример 2.

    Получение всех документов одного вида со всеми реквизитами шапки (включая все общие и системные реквизиты):
Код:
SELECT
               TabJ.*, Tab1.*
FROM
                DH14 As Tab1
INNER JOIN
                _1SJOURN As TabJ ON (Tab1.IDDOC = TabJ.IDDOC)

Пример 3.
    Получение всех документов одного вида со всеми реквизитами шапки (включая все общие и системные реквизиты) а также всех реквизитов табличной части:
Код:       
SELECT
               TabJ.*, Tab1.*, Tab2.*
FROM
                DH14 As Tab1
INNER JOIN
                _1SJOURN As TabJ ON (Tab1.IDDOC = TabJ.IDDOC)
INNER JOIN
                DT14 As Tab2 ON (Tab1.IDDOC = Tab2.IDDOC)

Пример 4.
    Получение всех документов одного вида со всеми реквизитами шапки (включая все общие и системные реквизиты) а также всех реквизитов табличной части с фильтром по дате документа (документов за один день).
В этом примере необходимо учесть, что дата документа храниться в SQL в DBF формате базы по разному. Но, в любом случае, дата документа храниться в таблице журналов _1SJOURN, рассмотрим пример для SQL базы:
Код:              
SELECT
               TabJ.*, Tab1.*, Tab2.*
FROM
                DH14 As Tab1
INNER JOIN
                _1SJOURN As TabJ ON (Tab1.IDDOC = TabJ.IDDOC)
INNER JOIN
                DT14 As Tab2 ON (Tab1.IDDOC = Tab2.IDDOC)
WHERE
                 LEFT(TabJ.DATE_TIME_IDDOC,8) = '20050219'
 
Пример 5.
    Получение списка документов родителей по подчиненному документу (получение только общих реквизитов с отборами и системных реквизитов).
Код:              
SELECT
               TabJ.*
FROM
                _1SCRDOC As TabRod
INNER JOIN
                _1SJOURN As TabJ ON (SUBSTRING(TabRod.PARENTVAL,7,9) = TabJ.IDDOC)
WHERE
                (TabRod.MDID = 0) -- только документы, без граф отбора
                AND (TabRod.CHILDID = '   6C3RK ')
  
где '   6C3RK 'ID документа родителя

Пример 6.
    Получение списка подчиненных документов (получение только общих реквизитов с отборами и системных реквизитов).
Код:       

SELECT
               TabJ.*
FROM
                _1SCRDOC As TabRod
INNER JOIN
                _1SJOURN As TabJ ON (TabRod.CHILDID = TabJ.IDDOC)
WHERE
                (TabRod.MDID = 0) -- только документы, без граф отбора
                AND (TabRod.PARENTVAL = @ИдДокРодителя)
ORDER BY
                TabRod.CHILD_DATE_TIME_IDDOC
  
где @ИдДокРодителяID документа родителя (включая тип и вид документа, т.е. 23 символа), получить такой ID можно с помощью метода ЗначениеВСамуюДлиннуюСтрокуБД(ТекДок) класса MetaDataWork компоненты 1С++.

Пример 7.
    Получение списка только проведенных документов. Для отбора только проведенных документов можно воспользоваться значением поля CLOSED таблицы _1SJOURN. Так первый бит его отвечате за то проведен документ или нет, соответсвенно сделав унарное умножение с 1 получим результат в виде 1 - проведен, 0 - непроведен.
Код:       

SELECT
               TabJ.*, Tab1.*
FROM
                DH14 As Tab1
INNER JOIN
                _1SJOURN As TabJ ON (Tab1.IDDOC = TabJ.IDDOC)
WHERE
                 TabJ.CLOSED&1 = 1
  
Пример 8.
   Получение списка документов, принадлежащих разным компонентам (у которых включен флажек принадлежности к типам учета). Для отбора документов по типам учета применяется унарное умножение поля APPCODE с требуемым значением. Список значений, для отбора по типам учета:
(1)  000001 - опер учет.
(2)  000010 - расчет.
(4)  000100 - бух учет.
(8)  001000 - движения периодических реквизитов
(16) 010000 - существует документ операция у документа (Документ может быть непроведен, но тогда должно быть выбрано Создавать операцию - Всегда в окне свойства докумнета)
(32) 100000 - Выполнен метод Операция.Записать(); в документе, принадлежащем бух. учету.

Соотвтетсвенно, если надо выбрать документы, принадлежащие оперативному и бухгалтерсокму учету, надо наложить бинарную маску (бинарное умножение) на 000001 и 000100, или же вместе 000101, или же в десятичной системе 5.
Код:       

SELECT
               TabJ.*, Tab1.*
FROM
                DH14 As Tab1
INNER JOIN
                _1SJOURN As TabJ ON (Tab1.IDDOC = TabJ.IDDOC)
WHERE
                 TabJ.APPCODE&5 = 5
 
Пример 9.
    Получение списка документов по графе отбора.
Все графы отбора хранятся в 1С в таблице _1SCRDOC. Также в этой таблице хранятся и ссылки на подчиенные документы. Разница между подчиненными документами и графми отбора, в том, что поле MDID для подчиненных документов равно 0, а для граф отбора - десятичным идентификатором графы отбора. Допустим надо получить список документов с отбором по графе "Контрагент". Получим десятичный ИД этой графы отбора с помощью метода ИДОбъекта класса MetaDataWorks компоненты 1С++. К примеру так: ИДГрафы = глMDW.ИДОбъекта(Метаданные.ГрафаОтбора("Контрагент")), где глMDW = СоздатьОбъект("MetaDataWork"). Отбирать будем по столбцу PARENTVAL, в котором хранится полный идентификатор (23 символа)  элемента справочника. Получучить полный ИД необходимого элемента можно тем же путем, что и идентификатор для графы отбора, но немного другим методом. Например, отбираем по клиенту, значение которого хранится в переменной ВыбКлиент, тогда полный его идентификатор полумаем как: IDКлиента = глMDW.ЗначениеВСамуюДлиннуюСтрокуБД(ВыбКлиент).
Код:       

SELECT
    TabJ.IDDOC AS Документ,
    TabJ.IDDOCDEF AS Документ_вид,
    TabJ.DOCNO AS НомерДок
FROM
    dbo._1SCRDOC AS TabGraf (NOLOCK)
INNER JOIN _1SJOURN AS TabJ (NOLOCK) ON (TabJ.IDDOC = TabGraf.CHILDID)
WHERE
    MDID = 14730 -- ИД графы отбора
    AND PARENTVAL = 'B1  4U     2           ' --23 ID элемента справочника
ORDER BY
    TabJ.DATE_TIME_IDDOC

  
где соответсвенно ИДГрафы = 14730, а IDКлиента = 'B1  4U     2           '.

Регистры

Общее описание подситсемы регистров

Схема таблиц регистров 1С для нашего примера
Рис. 2 «Схема таблиц регистров 1С для нашего примера».
    Физически регистры остатков состоят из двух таблиц: таблица остатков RGххх и таблица движений RAххх. В таблице движений хранятся все движения документов по регистрам. Список документов, которые сделали движения по регистру, можно получить выбрав записи с таблицы журналов _1SJOURN с условием равенства поля RFxxx  1 (или не равно 0) , например (в случае, если идентификатор регистра 16, т.е. таблица движений = RG16, а таблица остатков = RA16) для получения всех документов, сделавших вдижения по регистру можно получить вот так:
Код:              
SELECT
               *
FROM
                _1SJOURN
WHERE
                (RF16 = 0x1)
                AND (CLOSED&1=1)
                AND (APPCODE&1=1)
  
где CLOSED&1=1 – ограничение на выбор записей только проведенных документов. Фактически при корректных записях в базе, это условие лишнее, но при каких то «глюках» таким условием можно «отловить» непроведенные документы с существующими движениями (к сожалению такое может встречаться).
А вот условие APPCODE&1=1 – отобрать документы принадлежащие оперативному учету. Подробнее о полях таблицы _1SJOURN см.: Описание таблицы _1SJOURN

    Рассмотрим более подробно то, как храняться данные в таблицах RG и RA.
В таблицу RA записываются все движения документов, с учетом флага прихода или расхода. Т.е. в таблице RA храняться сведения о всех движениях документов, которые сделали движения по этому регистру. Соответсвенно метод "ДвижениеПриходВыполнить()/ДвижениеПриход()" записывает одну строку в таблицу RA с флагом приход (DEBKRED = 0), а методы "ДвижениеРасходВыполнить()/ДвижениеРасход()" также добавляет одну запись в таблицу RA но уже с флагом расхода (DEBKRED = 1). Соответсвенно, если в регитсре отражается приход и расход по складу, и самое первое движение по регистру есть первый приход на склад (т.е. это первое поступление на склад, до этого на складе ничего не было), то для получения остатка на складе нам надо сложить все приходы и вычесть все расходы. Хорошо когда таких приходов немного и начало заполнения таблиц выполняется пару месяцев. А представим что система рабоатет пару лет. В таком случае для получения остатка на складе нам надо выполнить операцию сложения всех приходов и вычитания всех расходов с начала работы. А если еще учесть что остатки нам надо считать каждый раз при формировании новго движения расхода (внесения новой записи в таблицу) (для контроля остатка или для расчета себестоимости) - то на лицо лишние операции сканирования всех записей таблицы RA. Для того чтобы не делать таких персчетов 1С разработали регитсры остатков таким образом, что они состоят из двух таблиц. 1 - RA (хранятся все движения с флагом прихода или расхода), 2 - RG для хранения промежуточных итогов (как бы заблаговременно подсчитаные итоги по таблице RA за какой то период).

Рассмотрим примеры

        Рассмотрим получение остатков и оборотов по регистру остатков.
        Период хранения остатков - месяц. Исходная таблица остатков (RG):
Исходные таблицы

Табл. 1 Таблица остатков по регистру (таблица RG16)

PERIOD SP20  SP22 SP21
2005-02-01 00:00:00.000 '    AA   '  '    1A   '  35.00
2005-03-01 00:00:00.000  '    AA   ' '    1A   '  20.00


Исходная таблица движений (RA)

Табл. 2 Таблица движений по регистру (таблица RA13)
Табл.1 Описание полей таблицы _1SCONNECT
IDDOC LINENO_ ACTNO DEBKRED IDOCDEF DATE_TIME_IDDOC SP20 SP22 SP21
1 0
1 0 12 '200502157579C0     1   ' '    AA   '  '    1A   ' 10.00
2 0 1 0 12 '20050215759EHS     2   ' '    AA   '  '    1A   ' 10.00
6 0 1 0 23 '200502157QOSK0     6   ' '    AA   '  '    1A   ' 15.00
7 0 1 1 23 '200503013KLMO0     7   ' '    AA   '  '    1A   ' 15.00

    Данное описание строится на регистрах остатков, регистры оборотов будут рассмотрены позже.
    При описании таблицы остатков (RG) необходимо отметить важную особенность.
    Особенность заключается в том, что записи в таблице сортируются по периодам остатков (колонка «PERIOD» и это поле является кластерным индексом (в состав индекса также всегда входит и 1 измерения регистра), по умолчанию периодичность регистров остатков - месяц, именно этот случай и рассматривается). В поле «PERIOD» хранится ДАТА НАЧАЛА периода (МЕСЯЦА). Исключением является период, совпадающий с точкой актуальности, в этом периоде все записи относятся не к концу месяца, а к времени или документу ТА (рассчитаны по этот документ, т.е. с учетом его движений, если документ последовательности проведен). Т.е. другими словами, в этой таблице всегда записи или на конец месяца или же на ТА.


Структура и описание полей таблиц регистров.


1. Таблица RGxxx

 Краткое описание:
  Таблица предназначена для хранения итогов по периодам в разрезе до измерений и по всем ресурсам регистра.
Табл.3 Описание полей таблицы RGxxx
Название поля  Описание  
PERIOD Период остатков. Всегда равен началу периода (месяц для нашего примера).  Тип - DateTime (для dbf - Date)
SPххx Измерения, ресурсы или атрибуты регистра (типы: Numeric, DateTime, Char(n), n=1:999)
TSPyyy Дополнение к измерению или реквизиту, заполняется только для неопределенных типов значений (используется совместно с полем «SPyyy»). Тип - Char(3)
 

2. Таблица RAxxx

 Краткое описание:
  Таблица предназначена для хранения движений по регистрам (включая все измерения, ресурсы и реквизиты регистроа).
Табл.4 Описание полей таблицы RAxxx
Название поля  Описание  
IDDOC ID документа, тип «строка», по этому полю осуществляется связь с таблицей документов («1SJOURN» или «_1SJOURN» для SQL). Тип - Char(9)
LINENO_ Номер строки документа (то что устанавливается методом «ПривязатьСтроку()»). Тип - SmallInt
ACTNO Порядковый номер движения (это не номер строки, а именно движения). Тип - Integer
DEBKRED Флаг прихода или расхода. 0 - приход, 1 - расход. Тип – bit.
IDOCDEF Поле присутствует лишь тогда, когда в свойствах регистра установлен флажок «Быстрая обработка движений». ID вида документа (из метаданных, причем как раз номер а не 36-ричное представление). Тип - Integer.
DATE_TIME_IDDOC Поле присутствует лишь тогда, когда в свойствах регистра установлен флажок «Быстрая обработка движений». Значения єтого поля идентичны значениям поля «DATE_TIME_IDDOC» таблицы «_1SJOURN» (журналов). Тип - Char(23)
SPххx Измерения, ресурсы или атрибуты регистра (типы: Numeric, DateTime, Char(n), n=1:999)
TSPyyy Дополнение к измерению или реквизиту, заполняется только для неопределнных типов значений (используется совместно с полем «SPyyy»). Тип - Char(3)
 
    Особое внимание надо уделить полям «TSP». Это поле создается лишь тогда, когда реквизит (измерение, реквизит) имеет неопределенный тип (длина ID кода 23 символа). Опытным путем было установлено:
это поле по умолчанию заполняется пустой строкой (3 пробела).
  • Для невыбранного типа (заполняется по умолчанию):
  • TSP235 = '  ' (3 пробела), при этом связанное неопределенное поле SP235 заполняется значением 'U                      ' (символ U и 22 пробела), что скорее всего означает «Undefined», т.е. неопределенный.
  •  Для типов «Число»:
  • связанное поле SP235 заполняется строковым значением числа, но с символом «N» вначале строки (например 'N                     2' для целого числа 2). Возможные значения TSP235:    
    'F30' - число с плавающей запятой (float);
    '320' - число, состоящее только из десятичных (0.99, т.е. Число(3,2)).
    'A00' - целое число (int);
  •  Для типов «Строка»:
  • связанное поле SP235 заполняется строкой, но с символом 'S' слева (например 'SАбвгдежзклимно        '). Возможные значения TSP235 в этом случае: количество знаков, определенных для строкового значения, например, '14'  - количество знаков в строке, в 36-ричном формате (_IDToStr). Следует отметить, хотя 1С и назначает длину строки больше 22 символов, но фактически в этом поле можно хранить только 22 символа (1 разряд приходиться на обозначение типа поля 'S').
  • Для типов «Дата»:
  • связанное поле SP235 заполняется строкой, но с символом 'D' слева (например 'D20050303              ').  TSP235 - '   ' (3 пробела), т.е. поле пустое.
  • Для типов 1С (Справочник, Документ, Перечисление, Счет, и т.д.):
  • связанное поле SP235 заполняется строкой включающий полный идентификатор объекта (его тип, вид, внутренний ИД код). Следует отметить, что для типов «Перечисление» нельзя не назначать вид, так как не назначение вида приведет к несовпадению типов и не заполнению  неопределенного поля. Возможные значения TSP235:
'0  ' - был назначен только тип объекта, вид не назначен (например «НазначитьТип(«Справочник»)»)
'1  ' - был назначен как тип объекта, так и его вид (например «НазначитьТип(«Справочник.Клиенты»)»)


Пример 1.
    Получение итогов на ТА или на конец периода (месяца в нашем случае).
Поскольку итоги в таблице RG13 хранятся на ТА или на конец месяца (для нашого примера), то для получения итогов не обходимо лиш указать условие по периоду, на который нам надо получить итоги.
Код:
SELECT
               TabRegOst.SP20 As Товар,
               Sum(TabRegOst.SP21) As КвоКонОст
FROM
                RG13 As TabRegOst
WHERE  
                TabRegOst.PERIOD = @PERIODR
GROUP BY
                TabRegOst.SP20
ORDER BY
                TabRegOst.SP20
  
где @PERIODR – переменная типа DateTime, условие по которой служит для получения результата запроса на тот период, который нам необходим.
Например, если надо получить остаток на ТА, при этом ТА = «19.02.2006», то переменной @PERIODR необходимо присвоить значение начала месяца «01.02.2006».
Код:
SET @PERIODR = CONVERT(DateTime, '20060201',112)
   
    Если необходимо рассчитать остаток на конец Января, то переменной необходимо присвоить значение «01.01.2006»:
Код:
SET @PERIODR = CONVERT(DateTime, '20060101',112)
   
    Нетрудно догадаться, что если необходимо получить остаток на конец Января, то надо в качестве условия поставить начало Февраля (ведь остаток на конец января есть остатком на начало Февраля. Именно так и делает 1С при расчете остатка на конец периода).
    Спросите почему присвоение переменной @PERIODR происходит с помощью функции CONVERT, да и еще дата в таком странном формате? Это все дело привычки (дело в том, что в таблице журналов дата документа в поле DATE_TIME_IDDOC как раз храниться в виде строки ГГГГММДД).

Пример 2.
    Получение сумм прихода и расхода за один месяц (оборотов)
    Поскольку обороты храниться в таблице RA13 – то запрос выполняется только по этой таблице с условием на дату документа, сделавшего движение.
Код:
SELECT
                TabReg.SP20 As Товар,
                Sum(TabReg.SP21*((DEBKRED+1)%2)) As КвоПриход,
                Sum(TabReg.SP21*DEBKRED) As КвоРасход,
FROM
                RA13 As TabReg
INNER JOIN _1SJOURN As TabJ
                ON (TabReg.IDDOC = TabJ.IDDOC)
WHERE
               TabJ.DATE_TIME_IDDOC >= @PERIODN
               AND TabJ.DATE_TIME_IDDOC <= @PERIODK
GROUP BY
                TabReg.SP20
ORDER BY
                TabReg.SP20
  
где @PERIODN, @PERIODK – переменные типа Char(8), условия по которых служат для получения результата запроса на тот период, который нам необходим.
В нашем случае @PERIODN = «20060201», @PERIODK = «20060228».
Код для установки переменных:
Код:
SET @PERIODR =  '20060201'
SET @PERIODR =  '20060228'
   
Пример 3.
    Получение итогов и остатков на произвольную дату.
Поскольку итоги в нашем примере хранятся только на конец месяца или ТА, то необходимо выполнить 2 запроса, первый – для получения тога на начало пери ода (из примера 1), второй – для получения оборотов (из примера 2).
Код:
SELECT
               TMP.Товар As Товар,
               SUM(TMP.НачОст) As НачОст,
               SUM(TMP.Приход) As Приход,
               SUM(TMP.Расход) As Расход,
               SUM(TMP.НачОст + TMP.Приход - TMP.Расход) As КонОст       
FROM
          (
          SELECT
                       TabRegOst.SP20 As Товар,
                       TabRegOst.SP21 As НачОст,
                       0 As Приход,
                       0 As Расход
          FROM
                       RG13 As TabRegOst
          WHERE
                       TabRegOst.PERIOD = @PERIODR
          UNION ALL
          SELECT 
                      TabReg.SP20 As Товар,
                      0 As НачОст,
                      (TabReg.SP21*((DEBKRED+1)%2)) As Приход,
                      (TabReg.SP21*DEBKRED) As Расход
          FROM
                      RA13 As TabReg
          INNER JOIN _1SJOURN As TabJ
                      ON (TabReg.IDDOC = TabJ.IDDOC)
          WHERE
                      TabJ.DATE_TIME_IDDOC >= @PERIODN
                      AND TabJ.DATE_TIME_IDDOC <= @PERIODK
          )  As TMP
GROUP BY
             TMP.Товар
ORDER BY
             TMP.Товар
  
где @PERIODR – переменная типа DateTime, условие по которой служит для получения результата запроса на период, меньший на 1 от рассчитываемого. В нашем случае для получения остатка на 20.02.2006 этой переменной необходимо присвоить значения начала Января, т.е. «01.01.2006» (так как в случае условия на 01.02.2006 получим сотаток или на конец Февраля или на точку актуальности):
Код:
SET @PERIODR = CONVERT(DateTime, '20060101',112)
   
@PERIODN, @PERIODK – переменные типа Char(8), условия по которых служат для получения результата в запросе за определенный период. Для нашего примера @PERIODN = «20060201», @PERIODK = «20060220»:
Код:
SET @PERIODR = '20060201'
SET @PERIODR = '20060220'
   
Следует отметить, что условие:

AND TabJ.DATE_TIME_IDDOC <= @PERIODK

предполагает получение остатка на начало дня, это связано с тем что в поле DATE_TIME_IDDOC храниться не только дата в текстовом виде, но и время и идентфикатор документа. Так, чтобы получить строку представления начала дня необходимо выполнить функцию СформироватьПозициюДокумента(<Дата>,<Час>,<Мин>,<Сек>,<ФлагКонцаСекунды>), которая и сформирует строку даты и времени в текстовом представлении. Например для даты 14.03.2006 функция вернет значение '#20060314         0         0   ' (в нашем случае символ # надо убрать). Как видим, первый 0 - это 36 ричное представление времени, а вот второй 0 - это ID документа (невібранного документа). Но, поскольку сравнению в условию поддается каждій символ, то любая пустая строка будет меньше лбюой заполненной сторки (в нашем случае пробелами и 0). Поэтому, чтобы получить остаток на конец дня, надо или отсечь лишние символы в поле DATE_TIME_IDDOC или же сравнивать с завтрашней датой. Я предпочитаю второй вариант, так как в таком случае нагрузка на сервер меньше (не требуется выполнять обрезание значений функцией Left), но приведу в качестве примера оба решения:

AND LEFT(TabJ.DATE_TIME_IDDOC,8) <= @PERIODK
или условие предыдущее, но значение переменной устанавливается на дату @PERIODK = «20060221»:
Код:
SET @PERIODR = '20060221'
   
Пример 4.
    Получение итогов и остатков на документ.
    Задача аналогична задаче из предыдущего примера, за исключением того, что условие периода не по дату, а по позицию документа.
Код:
SELECT
               TMP.Товар As Товар,
               SUM(TMP.НачОст) As НачОст,
               SUM(TMP.Приход) As Приход,
               SUM(TMP.Расход) As Расход,
               SUM(TMP.НачОст + TMP.Приход - TMP.Расход) As КонОст
FROM
           (
           SELECT
                       TabRegOst.SP20 As Товар,
                       TabRegOst.SP21 As НачОст,
                       0 As Приход,
                       0 As Расход
          FROM
                       RG13 As TabRegOst
          WHERE
                       TabRegOst.PERIOD = @PERIODR
          UNION ALL
          SELECT 
                       TabReg.SP20 As Товар,
                       0 As НачОст,
                       (TabReg.SP21*((DEBKRED+1)%2)) As Приход,
                       (TabReg.SP21*DEBKRED) As Расход
          FROM
                       RA13 As TabReg
          INNER JOIN _1SJOURN As TabJ
                       ON (TabReg.IDDOC = TabJ.IDDOC)
          WHERE
                       TabJ.DATE_TIME_IDDOC >= @PERIODN
                       AND TabJ.DATE_TIME_IDDOC < @POZDOK
          )  As TMP
GROUP BY
             TMP.Товар
ORDER BY
             TMP.Товар
   
    где @PERIODR – переменная типа DateTime, условие по которой служит для получения результата запроса на период, меньший на 1 от рассчитываемого. В нашем случае для получения остатка на 20.02.2006 этой переменной необходимо присвоить значения начала Января, т.е. «01.01.2006» (так как в случае условия на 01.02.2006 получим сотаток или на конец Февраля или на точку актуальности):
Код:
SET @PERIODR = CONVERT(DateTime, '20060101',112)
   
где @PERIODN – переменная типа Char(8), условия по которой служит для получения результата в запросе на определеный период. Для нашого примера @PERIODN = «20060201».
Код:
SET @PERIODR = '20060201'

где @POZDOK – переменная типа char(23), позиция документа (получаемая методом ПолучитьПозицию()).
    Следует отметить, что поле DATE_TIME_IDDOC может быть в таблице движений регистра (в случае установленного флага «Быстрая обработка движения» при конфигурировании настроек регистра), в этом случае связь с таблицей журналов (_1SJOURN) необязательна и условие:

AND TabJ.DATE_TIME_IDDOC < @POZDOK

Может выглядеть вот так:

AND TabReg.DATE_TIME_IDDOC < @POZDOK

И строка:

INNER JOIN _1SJOURN As TabJ ON (TabReg.IDDOC = TabJ.IDDOC)

ненужна.
Т.е. Запрос будет выглядеть так
Код:
SELECT
              TMP.Товар As Товар,
               SUM(TMP.НачОст) As НачОст,
               SUM(TMP.Приход) As Приход,
               SUM(TMP.Расход) As Расход,
               SUM(TMP.НачОст + TMP.Приход - TMP.Расход) As КонОст
FROM
           (
           SELECT
                       TabRegOst.SP20 As Товар,
                       TabRegOst.SP21 As НачОст,
                       0 As Приход,
                       0 As Расход
          FROM
                       RG13 As TabRegOst
          WHERE
                       TabRegOst.PERIOD = @PERIODR
          UNION ALL
          SELECT 
                       TabReg.SP20 As Товар,
                       0 As НачОст,
                       (TabReg.SP21*((DEBKRED+1)%2)) As Приход,
                       (TabReg.SP21*DEBKRED) As Расход
          FROM
                       RA13 As TabReg
          WHERE
                       TabReg.DATE_TIME_IDDOC >= @PERIODN
                       AND TabReg.DATE_TIME_IDDOC < @POZDOK
          )  As TMP
GROUP BY
             TMP.Товар
ORDER BY
             TMP.Товар
   


Пример 5.
Получение остатков и оборотов за произвольный  период
Приведенный в этом примере код составлен по таблицам конфигурации ПУБ для Украины. Запрос получает остатки и обороты в разрезе продукции за период с "15.01.2006" по "15.02.2006", при условии что период хранения остатков в регистрах остатков - Месяц.
Код:
-- Объявление переменных
Declare @PERIODR DateTime
Declare @PERIODN Char(8)
Declare @PERIODK Char(8)
Declare @PERIODN2 Char(8)
Declare @PERIODK2 Char(8)
-- присвоение переменным значений
SET @PERIODR = Convert(DateTime,'20051201',112)
SET @PERIODN = '20051201'
SET @PERIODK = '20060115'
SET @PERIODK2 = '20060215'

-- сам запрос
SELECT
               TMP.Продукция AS Продукция,
               SUM(TMP.НачОст) As НачОст,
               SUM(TMP.Приход) As Приход,
               SUM(TMP.Расход) As Расход,
               SUM(TMP.НачОст + TMP.Приход - TMP.Расход) As КонОст
FROM (   
             SELECT
                            TabRegOst.SP1718 As Продукция,
                            TabRegOst.SP1722 As НачОст,
                            0 As Приход,
                            0 As Расход
             FROM
                            RG1714 As TabRegOst
             WHERE
                            TabRegOst.PERIOD = @PERIODR

             UNION ALL
             SELECT 
                            TabReg.SP1718 As Товар,
                            (TabReg.SP1722*((DEBKRED+1)%2))- (TabReg.SP1722*DEBKRED) As НачОст,
                            0 As Приход,
                            0 As Расход
             FROM
                           RA1714 As TabReg
             INNER JOIN _1SJOURN As TabJ
                           ON (TabReg.IDDOC = TabJ.IDDOC)
             WHERE
                           TabJ.DATE_TIME_IDDOC >= @PERIODN
                           AND TabJ.DATE_TIME_IDDOC < @PERIODK

             UNION ALL
             SELECT 
                           TabReg2.SP1718 As Товар,
                           0 As НачОст,
                           (TabReg2.SP1722*((DEBKRED+1)%2)) As Приход,
                           (TabReg2.SP1722*DEBKRED) As Расход
             FROM
                           RA1714 As TabReg2
             INNER JOIN _1SJOURN As TabJ
                           ON (TabReg2.IDDOC = TabJ.IDDOC)
             WHERE
                           TabJ.DATE_TIME_IDDOC >= @PERIODK
                           AND TabJ.DATE_TIME_IDDOC < @PERIODK2
              ) AS TMP
GROUP BY
              TMP.Продукция
   
Пример 6.
    Пересчет итогов по одному из регистров остатков
Даный код приведен как пример работы с регистрами
Код:
-- Создадим процедуры по конвертации 10-36, 30-10
-- Проверим есть ли такие про
--IF EXISTS(SELECT name FROM sysobjects WHERE name = 'Convert10To36') DROP Procedure 'Convert10To36'


CREATE PROCEDURE [Convert10To36] @Deci INT, @Res36 CHAR(9) OUTPUT AS
SET NOCOUNT ON
DECLARE @j INT
DECLARE @Arr36 CHAR(36)
SELECT @Arr36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT @Res36 = ''
SELECT @j = LOG(@Deci)/LOG(36) +1
while @j>0
begin
SELECT @Res36 = LTRIM(RTRIM(@Res36)) + SUBSTRING(@Arr36, @Deci/POWER(36,@j-1) +1 ,1)
SELECT @Deci = @Deci%POWER(36,@j-1)
SELECT @j =@j-1
end
GO
--IF EXISTS(SELECT name FROM sysobjects WHERE name = 'Convert36To10') DROP Procedure 'Convert36To10'
CREATE PROCEDURE [Convert36To10] @Res36 CHAR(9), @Deci INT OUTPUT AS
SET NOCOUNT ON
DECLARE @j INT
DECLARE @Arr36 CHAR(36)
SELECT @Arr36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT @Deci = 0
SELECT @j = 1
while @j <= LEN(LTRIM(RTRIM(@Res36)))
begin
if @j <> 1
SELECT @Deci = @Deci*36
SELECT @Deci = @Deci + CHARINDEX(SUBSTRING(LTRIM(RTRIM(@Res36)), @j,1),@Arr36) -1
SELECT @j = @j+1
End
GO

DECLARE @DATE_POSL datetime
DECLARE @PERIOD_POSL datetime
DECLARE @TIME_POSL int
DECLARE @IDDOC_POSL char(9)
DECLARE @POZ_POSL char(23)
DECLARE @POZ_POSLMAX char(23)
DECLARE @DOC_POSL char(23)
DECLARE @Res36 char(9)
--SELECT @DATE_POSL = Convert(Varchar(23),(SELECT MAX(CURDATE) FROM _1SSYSTEM),112)
-- Получаем дату ТА, время ТА в десятичном формате от начала суток*10000, ID
-- документа последовательности
SELECT @DATE_POSL = MAX(CURDATE),
    @TIME_POSL = MAX(CURTIME),
    @IDDOC_POSL = MAX(EVENTIDTA)
FROM _1SSYSTEM

IF @TIME_POSL <> 0
    -- Конвертируем время в 36 ричный вид
    EXEC Convert10To36 @TIME_POSL, @Res36=@POZ_POSL OUTPUT
ELSE
    SELECT @POZ_POSL= ''
-- Если ТА установлена не на документ - тогда время и ID документа будут ранвы 0
IF @IDDOC_POSL = '     0   ' SELECT @IDDOC_POSL= ''

-- Получаем позицию ТА
SELECT @POZ_POSL = Convert(Varchar(23),@DATE_POSL,112)+LTRIM(RTRIM(@POZ_POSL))+@IDDOC_POSL

-- Получаке документ ТА, если таков имеется (если ТА находиться на документе)
SELECT @DOC_POSL = (SELECT DATE_TIME_IDDOC FROM _1SJOURN WHERE DATE_TIME_IDDOC = @POZ_POSL)
-- Получаем начало месяца даты ТА
SELECT @PERIOD_POSL = DATEADD(dd,1-DATEPART(dd,@DATE_POSL),@DATE_POSL)
--SELECT @DATE_POSL,@TIME_POSL,@POZ_POSL,@DOC_POSL,@PERIOD_POSL

-- Заполняем временную таблицу периодами пересчета
SELECT
    DISTINCT RG8918.PERIOD As PERIOD,
    Convert(Varchar(8),RG8918.PERIOD,112) AS PERIODS,
    (CASE WHEN RG8918.PERIOD <> @PERIOD_POSL THEN Convert(Varchar(8),DATEADD(mm,1,RG8918.PERIOD),112) ELSE @POZ_POSL END) As PERIODPO INTO ##TabPeriod FROM RG8918

GO
   
    --SELECT * FROM ##TabPeriod ORDER BY PERIODS
    -- Получаем курсор для обходя каждой строки временной таблицы ##TabPeriod
    DECLARE MyCur cursor for
        SELECT PERIOD, PERIODS, PERIODPO FROM ##TabPeriod ORDER BY PERIOD
   
    OPEN MyCur
    -- переменные для периодов
    DECLARE @PERIOD datetime
    DECLARE @PERIODPRED datetime
    DECLARE @PERIODS varchar(23)
    DECLARE @PERIODPO varchar(23)
    -- все делаем в транзакии
    -- обходим в цикле по каждой строке таблицы (по каждому периоду)
    FETCH NEXT FROM MyCur INTO @PERIOD, @PERIODS, @PERIODPO
   
    WHILE @@FETCH_STATUS = 0
    BEGIN
       
        -- Получим дату предыдущего периода (месяца)
        SELECT @PERIODPRED = DATEADD(mm,-1,@PERIOD)

        PRINT convert(Varchar(8),@PERIODPRED,112)+'  #  '+@PERIODS+'  #  '+@PERIODPO
        --SET HACT_ABORT ON
        BEGIN TRANSACTION
        -- очищаем текущий период
        DELETE FROM RG8918
        WHERE PERIOD = @PERIOD
        -- заполняем текущий период по пересчитанным данным
       
        INSERT RG8918
        SELECT  @PERIOD,
            T1.SP8908,
            T1.SP8909,
            T1.SP8910,
            T1.SP8911,
            T1.SP8912,
            T1.SP8913,
            T1.SP8914,
            T1.SP8915,
            '',
            T1.SP11200,
            SUM(T1.SP8916),
            0,
            0,
            0,
            0

        FROM
            (
            SELECT  TabRegOst.SP8908 As SP8908,
                TabRegOst.SP8909 As SP8909,
                TabRegOst.SP8910 As SP8910,
                TabRegOst.SP8911 As SP8911,
                TabRegOst.SP8912 As SP8912,
                TabRegOst.SP8913 As SP8913,
                TabRegOst.SP8914 As SP8914,
                TabRegOst.SP8915 As SP8915,
                TabRegOst.SP11200 As SP11200,
                SUM(TabRegOst.SP8916) As SP8916
            FROM RG8918 As TabRegOst (HOLDLOCK)
            WHERE TabRegOst.PERIOD = @PERIODPRED
            GROUP BY
                TabRegOst.SP8908,
                TabRegOst.SP8909,
                TabRegOst.SP8910,
                TabRegOst.SP8911,
                TabRegOst.SP8912,
                TabRegOst.SP8913,
                TabRegOst.SP8914,
                TabRegOst.SP8915,
                TabRegOst.SP11200
            UNION ALL
            SELECT  TabRegOb.SP8908 As SP8908,
                TabRegOb.SP8909 As SP8909,
                TabRegOb.SP8910 As SP8910,
                TabRegOb.SP8911 As SP8911,
                TabRegOb.SP8912 As SP8912,
                TabRegOb.SP8913 As SP8913,
                TabRegOb.SP8914 As SP8914,
                TabRegOb.SP8915 As SP8915,
                TabRegOb.SP11200 As SP11200,
                SUM(TabRegOb.SP8916*(1-TabRegOb.DEBKRED*2)) As SP8916
            FROM RA8918 AS TabRegOb (HOLDLOCK)
            INNER JOIN _1SJOURN As TabJourn ON (TabRegOb.IDDOC = TabJourn.IDDOC)
            WHERE TabJourn.DATE_TIME_IDDOC BETWEEN @PERIODS AND @PERIODPO

            GROUP BY
                TabRegOb.SP8908,
                TabRegOb.SP8909,
                TabRegOb.SP8910,
                TabRegOb.SP8911,
                TabRegOb.SP8912,
                TabRegOb.SP8913,
                TabRegOb.SP8914,
                TabRegOb.SP8915,
                TabRegOb.SP11200
            ) AS T1
        GROUP BY
            T1.SP8908,
            T1.SP8909,
            T1.SP8910,
            T1.SP8911,
            T1.SP8912,
            T1.SP8913,
            T1.SP8914,
            T1.SP8915,
            T1.SP11200
        HAVING SUM(T1.SP8916) <> 0
       
        COMMIT TRANSACTION
       
       

        FETCH NEXT FROM MyCur INTO @PERIOD, @PERIODS, @PERIODPO
    END
   
    CLOSE MyCur
    DEALLOCATE MyCur
   
SELECT * FROM ##TabPeriod ORDER BY PERIODS
DROP TABLE ##TabPeriod
   
© pvase 2006