Реализация условий в прямых запросах к журналу расчета.

Публикация № 165063

Разработка - Практика программирования

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

 Для работы с прямыми запросами необходима компонента 1cpp.dll.

 Все примеры  приведены применительно к базе DBF. Для работы с DBF необходим провайдер VFPOLEDB. Файл для его установки VFPOLEDBSETUP.msi можно скачать с сайта Микрософт.

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

     |Расчет = ЖурналРасчетов.Зарплата.ВидРасч;
     |Результат = ЖурналРасчетов.Зарплата.Результат;
     |Функция СуммаВыплаты= Сумма(Результат) когда (Расчет = ВидРасчета.ВыплатаЗарплаты);

или

     |Условие(Расчет.ВходитВГруппу(ГруппаРасчетов.ВсеНачисления)=1);

В этой статье я попытался обобщить полученную на различных форумах информацию о методах реализации таких условий, а также немного добавил от себя.

Напомню назначение основных полей журнала расчетов:
idalg - вид расчета
ids   - объект,в типовых конфигурациях имеет тип Справочник.Сотрудники
result - результат

Условия, относящиеся ко всему запросу, будем размещать в секции WHERE, условия, относящиеся к отдельным функциям - в секции SELECT (хотя из этого правила будут и исключения).
Вначале рассмотрим простые условия с единственным видом расчета. Они легко реализуются с помощью метапарсера. Например, условие

     |Условие(Расчет = ВидРасчета.ВыплатаЗарплаты);

в тексте прямого запроса будет выглядеть так

     |SELECT * FROM $ЖурналРасчетов.Зарплата as ЖР
     |WHERE ЖР.idalg=$ВидРасчета.ВыплатаЗарплаты

Но такой вариант условия практически не встречается. Чаще бывает нужно реализовать функцию с условием:

     |Функция СуммаВыплаты= Сумма(Результат) когда (Расчет = ВидРасчета.ВыплатаЗарплаты);

В базе SQL такую функцию можно реализовать при помощи условного выражения CASE

     |  SUM(CASE WHEN ЖР.idalg=$ВидРасчета.ВыплатаЗарплаты THEN ЖР.result ELSE 0.00 END) AS СуммаВыплаты,

Однако VFP провайдер OLE DB не поддерживает выражение языка SQL CASE. Поэтому здесь и дальше будем реализовывать функцию при помощи "внутренних" функций языка FoxPro ICASE() или более простой IIF():

    |SELECT
    |  SUM(iif(ЖР.idalg=$ВидРасчета.ВыплатаЗарплаты, ЖР.result, 0.00)) AS СуммаВыплаты
    |FROM $ЖурналРасчетов.Зарплата as ЖР

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


 Вариант первый,

 когда размер списка не очень большой, например условие
 Расчет.ВходитВГруппу(ГруппаРасчетов.ОсновныеНачисления)=1

В секции WHERE, то есть применительно ко всему запросу, такого рода условие можно записать в виде
ЖР.idalg IN ('Расч1','Расч2',...,'РасчN'), где Расч1...РасчN - 4-х символьные 36-ричные идентификаторы соответствующих видов расчета. Для формирования такой строки нам понадобятся дополнительные функции и объекты.

  Перем МД; //объект метаданные

  //******************************************************************************
  Функция РасчетСтрокой(Расчет)
     Возврат
"'"+Сред(МД.ЗначениеВСтрокуБД(Расчет),3,4)+"'";
  КонецФункции
  //******************************************************************************
  Функция РасчетыИзГруппы(Группа)
     СписокРасчетов ="(";
     ЭтоПервыйРасчет = 1;
     Для
Сч=1 По Группа.Количество() Цикл
        мдРасчет = Группа.ПолучитьРасчет(Сч);
        СписокРасчетов = СписокРасчетов+?(ЭтоПервыйРасчет=1,"",",")+РасчетСтрокой(мдРасчет);
        ЭтоПервыйРасчет = 0;
     КонецЦикла;
     СписокРасчетов = СписокРасчетов+")";
     Возврат
СписокРасчетов
  КонецФункции

Формирование в основной процедуре строки с условием:

    МД=СоздатьОбъект("MetaDataWork");

    СтрокаОсновныхНачислений = РасчетыИзГруппы(ГруппаРасчетов.ОсновныеНачисления);

И теперь текст запроса с условием примет вид:


   ТекстЗапроса = "
   |SELECT * FROM $ЖурналРасчетов.Зарплата as ЖР
   |WHERE idalg IN "+СтрокаОсновныхНачислений;


Здесь кстати отметим, что если в выборке участвует лишь одна таблица, и даже если таких таблиц несколько, но имя поля среди них уникально, то можно употреблять это имя без дополнительного указания таблицы.

В секции SELECT, как вы уже наверное догадались, мы не сможем применить операцию вхождения IN. И снова воспользуемся встроенными функциями FoxPro. Наиболее подходящая из функций работы со строками, как мне кажется, AT(). Её синтаксис похож на синтаксис функции Найти() в 1С. Но, чтобы использовать её, нужна строка без кавычек, примерно такая -расч1-расч2-...-расчN-. Соответственно изменим функцию РасчетыИзГруппы()


     //******************************************************************************
     Функция РасчетыИзГруппы(Группа)
         СписокРасчетов ="-";
         Для Сч=1 По Группа.Количество() Цикл
               мдРасчет = Группа.ПолучитьРасчет(Сч);
               СписокРасчетов = СписокРасчетов+Сред(МД.ЗначениеВСтрокуБД(мдРасчет),3,4)+"-";
         КонецЦикла;
         Возврат СписокРасчетов
     КонецФункции


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

     ТекстЗапроса = "
     |SELECT
     |  SUM(IIF(AT(ЖР.idalg,"""+СтрокаОсновныхНачислений+""")>0, $ЖР.Дни, 0.00)) AS ОтработаноДней
     |FROM
     |  $ЖурналРасчетов.Зарплата AS ЖР
            |";

Размер строки, используемой в качестве второго параметра AT(), не должен превышать 255 символов. Для выбранного нами формата строки это соответствует 50 видов расчетов. Если фактически расчетов больше, то можно попытаться исключить неиспользуемые настраиваемые виды расчета. Если и это не поможет, тогда

Вариант второй,

когда список расчетов не умещается в строку. В этом случае необходимо строить дополнительную временную таблицу, содержащую список расчетов. Чтобы долго не рассуждать, просто приведу кусок кода , выводящий на экран все начисления за ноябрь 2012 года:


       КонДата = '30.11.12';
       СЗНач = СоздатьОбъект("СписокЗначений");
       ТЗ = СоздатьОбъект("ТаблицаЗначений");
       Для Сч=1 По ГруппаРасчетов.ВсеНачисления.Количество() Цикл
            СЗНач.ДобавитьЗначение(ГруппаРасчетов.ВсеНачисления.ПолучитьРасчет(Сч));
       КонецЦикла;
       ИмяТаблицыНачислений = "";
       RS.УложитьСписокОбъектов(СЗНач, ИмяТаблицыНачислений);
       ТекстПрямогоЗапроса = "
       |SELECT
       |  ЖР.idalg AS [Расчет $ВидРасчета],
       |  SUM(ЖР.result) AS СуммаНачислений
       |FROM
       |  $ЖурналРасчетов.Зарплата AS ЖР
       |WHERE
       |  '  '+idalg+'   ' IN (SELECT Val FROM "+ИмяТаблицыНачислений+")
       |  AND period='"+Формат(НачМесяца(КонДата),"Д ГГГГММДД")+"M'
       |GROUP BY idalg
       |";
       ТЗ = RS.ВыполнитьИнструкцию(ТекстПрямогоЗапроса);
       RS.Закрыть();
       ТЗ.ВыбратьСтроку();


где RS должен быть предварительно создан при помощи метода СоздатьКоманду(). Следует учитывать, что методом УложитьСписокОбъектов() формируется таблица с полем Val длиной 9 символов. Поэтому, чтобы выполнялось условие вхождения, idalg дополнен пробелами слева и справа. Правильнее было бы сформировать таблицу "вручную" c "правильной" длиной при помощи Запросов CREATE TABLE и INSERT INTO. И ещё обратите внимание, в отличие от языка запросов 1С, к полям таблицы можно обращаться, не описывая их в качестве переменных.

Чтобы реализовать для второго варианта функцию с условием, внутренних функций FoxPro уже не хватает, и надо использовать другие методы. Один из способов реализации - это для каждой функции с условием заводить отдельную секцию SELECT с объединением по UNION. Вот код для получения всех начислений и удержаний:


      ГУИД = СоздатьОбъект("GUID");
      ГУИД.Новый();
      ИмяТаблицыНачислений = ГУИД.ВСтроку();
      RS.Выполнить("CREATE TABLE """+ИмяТаблицыНачислений+""" (Val1 C(4))");
      Для Сч=1 По ГруппаРасчетов.ВсеНачисления.Количество() Цикл
         RS.Выполнить("INSERT INTO """+ИмяТаблицыНачислений+""" VALUES ("+РасчетСтрокой(ГруппаРасчетов.ВсеНачисления.ПолучитьРасчет(Сч))+")");
      КонецЦикла;
 
      ГУИД.Новый();
      ИмяТаблицыУдержаний = ГУИД.ВСтроку();
      RS.Выполнить("CREATE TABLE """+ИмяТаблицыУдержаний+""" (Val2 C(4))");
      Для Сч=1 По ГруппаРасчетов.ВсеУдержания.Количество() Цикл
           RS.Выполнить("INSERT INTO """+ИмяТаблицыУдержаний+""" VALUES ("+РасчетСтрокой(ГруппаРасчетов.ВсеУдержания.ПолучитьРасчет(Сч))+")");
      КонецЦикла;
  
      ТекстПрямогоЗапроса = "
      |SELECT
      |  ЖР.idalg AS [Расчет $ВидРасчета],
      |  SUM(ЖР.result) AS СуммаНачислений,
      |  0 AS СуммаУдержаний
      |FROM
      |  $ЖурналРасчетов.Зарплата AS ЖР
      |INNER JOIN "+ИмяТаблицыНачислений+" TabNach
      |  ON (ЖР.idalg = TabNach.Val1)
      |WHERE
      |  ЖР.period='"+Формат(НачМесяца(КонДата),"Д ГГГГММДД")+"M'
      |GROUP BY ЖР.idalg
      |UNION ALL
      |SELECT
      |  ЖР.idalg AS [Расчет $ВидРасчета],
      |  0 AS СуммаНачислений,
      |  SUM(ЖР.result) AS СуммаУдержаний
      |FROM
      |  $ЖурналРасчетов.Зарплата AS ЖР
      |INNER JOIN "+ИмяТаблицыУдержаний+" TabUdr
      |  ON (ЖР.idalg = TabUdr.Val2)
      |WHERE
      |  ЖР.period='"+Формат(НачМесяца(КонДата),"Д ГГГГММДД")+"M'
      |GROUP BY ЖР.idalg
      |";
      ТЗ = RS.ВыполнитьИнструкцию(ТекстПрямогоЗапроса);
      RS.Выполнить("DROP TABLE "+ИмяТаблицыНачислений);
      RS.Выполнить("DROP TABLE "+ИмяТаблицыУдержаний);
      RS.Закрыть();
      ТЗ.ВыбратьСтроку();


По сравнению с предыдущим примером здесь изменён способ формирования временных таблиц. Кроме того, вместо проверки вхождения по IN() сделано, как это рекомендуют, соединение по INNER JOIN.

Наверное, можно ещё предложить и другие способы реализации второго варианта. Однако я на практике не пользуюсь ни первым, ни вторым вариантом формирования списка. Вместо этого предлагаю

Вариант третий

Я создаю одну временную таблицу для всех видов условий, которые встречаются в запросе (здесь имеются ввиду только условия по видам расчетов). Одно поле в этой таблице отведено под идентификатор вида расчета, а на каждый вид условия отводится дополнительное поле. Каждому виду расчета, который есть в конфигурации, соответствует отдельная строка таблицы. В полях для условий проставляются единички для расчетов, которые входят в условие, и нолики для остальных. Например, для начислений и удержаний, как в предыдущем примере:


      RS.Выполнить("CREATE TABLE """+ИмяТаблицы+""" (ID C(4), nNach N(1), nUdr N(1))");
 
      Для Сч=1 По МетаДанные.ВидРасчета() Цикл
          мдРасчет = ВидРасчета.ПолучитьАтрибут(Метаданные.ВидРасчета(Сч));
          стрРасчет = РасчетСтрокой(мдРасчет);
          ВходитВначисления = мдРасчет.ВходитВГруппу(ГруппаРасчетов.ВсеНачисления);
          ВходитВУдержания = мдРасчет.ВходитВГруппу(ГруппаРасчетов.ВсеУдержания);
          RS.Выполнить("INSERT INTO """+ИмяТаблицы+""" VALUES ("+стрРасчет+","+ВходитВНачисления+","+ВходитВУдержания+")");
      КонецЦикла;


А вот так выглядит использование таблицы в запросе:


      ТекстПрямогоЗапроса = "
      |SELECT
      |  ЖР.idalg AS [Расчет $ВидРасчета],
      |  SUM(iif(Tab_gr.nNach=1, ЖР.result, 0.00)) AS СуммаНачислений,
      |  SUM(iif(Tab_gr.nUdr=1, ЖР.result, 0.00)) AS СуммаУдержаний
      |FROM
      |  $ЖурналРасчетов.Зарплата AS ЖР
      |INNER JOIN """+ИмяТаблицы+""" as Tab_gr on Tab_gr.id = ЖР.idalg
      |WHERE
      |  ЖР.period='"+Формат(НачМесяца(КонДата),"Д ГГГГММДД")+"M'
      |  AND (Tab_gr.nNach=1 OR Tab_gr.nUdr=1)
      |GROUP BY ЖР.idalg
      |";


Преимущество метода в том , что он универсален в том смысле, что условия можно использовать в любой секции запроса. Следовательно, можно реализовать как условия для всего запроса, так и для отдельных функций. К недостаткам можно было бы отнести накладные расходы на формирование таблицы, но у меня в разных режимах это занимало не больше 0.5 секунды.
В принципе, вместо условий вхождения в жестко заданные в конфигураторе группы можно использовать функции глобального модуля глВходитВБазу() и гдВходитВБазуПроводки(), чтобы получить базу для начисления налогов/взносов. Но там слишком много дополнительных нюансов по учету разных режимов налогообложения, разных категорий застрахованных лиц, и прочее и прочее. Всё это уже за рамками данной статьи.

В заключение несколько слов о файле для скачивания. Внешняя обработка в основном предназначена для иллюстрации практического применения материала статьи. Однако  она может иметь и самостоятельное значение, Например, очень удобно посмотреть все записи по отдельному сотруднику за год, или найти в своде, кому из сотрудников полагаются скидки при начислении страховых взносов. Предусмотрены два режима выполнения: с обычным запросом 1С и в режиме прямого запроса. Как говорится, почувствуйте разницу.

Скачать файлы

Наименование Файл Версия Размер
1cpp Свод по видам расчетов за год.ert

.ert 79,50Kb
06.12.12
5
.ert 79,50Kb 5 Скачать

Специальные предложения

Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Ёпрст 1041 11.12.12 08:45 Сейчас в теме
Не задействован индекс в тексте запроса.
Без него, это мегатормоз.
http://www.1cpp.ru/forum/YaBB.pl?num=1184317705
2. nicotin 34 11.12.12 18:55 Сейчас в теме
(1) Уточните, про какой индекс идет речь. Если имеется в виду журнал расчетов CJ447, то в приложенном файле отчета использовано выражение, частично оптимизированное под использование PERIOD+ID. Если же речь идет о временной таблице, то экспериментальные замеры показали, что наличие или отсутствие временной таблицы практически не влияет на быстродействие. При запуске по сети обычный запрос в своде за год выполнялся чуть больше 4 мин, прямой запрос примерно 4 сек.
3. Ёпрст 1041 11.12.12 19:04 Сейчас в теме
(2) любое соедиение и условие в твоих примерах идёт без учетов индекса.
4. nicotin 34 11.12.12 19:55 Сейчас в теме
(3) Да я и не возражаю. Все примеры как бэ учебные, такие же, как в известном тебе учебнике. Но согласись, что подбор индексов заслуживает как минимум отдельной публикации и отдельного обсуждения. Я не считаю себя экспертом в этой области и не возьмусь за неё. Можеты быть ты сам, или уважаемый Kiruha ?. Опять же повторю, что в обработке есть частичная оптимизация под индексное выражение PERIOD+ID. Полной оптимизации не удалось достичь ни в запросах за месяц, ни в запросах за год, но и частичная дает приемлемые результаты.
Оставьте свое сообщение

См. также

Формирование строки json в 1С: 7.7

Практика программирования v7.7 1cv7.md 1С7:Комплекс Абонемент ($m)

Предлагается набор функций 1с 7.7 для формирования строки json стандартными средствами.

1 стартмани

10.12.2018    6968    malovandrey    0    

Асинхронное программирование в 1с77 без внешних компонент. Обратные вызовы.

Практика программирования v7.7 Абонемент ($m)

Пример построения программного кода для достижения функционала обратных вызовов (call back) во внешних обработках исключительно штатными средствами. Тестировалось на платформе 1с77 релиз 027. Конфигурация значения не имеет.

1 стартмани

06.10.2018    6677    Vortigaunt    5    

Особенности разделения объектной модели документа и базы данных в 1С 7.7. Забавный глюк

Практика программирования v77::ОУ Абонемент ($m)

Когда занимаешься разработкой в среде 1С, редко задумываешься о том, что программным кодом ты работаешь с объектной моделью базы данных, а не с самой базой данных. И что это вообще разные вещи. Ты создаешь объекты: документы и справочники, записываешь их - и в базе данных появляются соответствующие записи. Это настолько привычно, что когда сталкиваешься с нетипичным поведением платформы, первым делом думаешь: надо протестировать базу, она битая. В этой статье я хочу разобрать одну интересную ситуацию, которая как раз демонстрирует такое поведение. Описанная ниже ситуация воспроизводится как в файловом, так и в клиент-серверном (SQL) варианте. Тестировалось на версии платформы 1с77 релиз 027.

1 стартмани

16.05.2018    8904    Vortigaunt    26    

Изменение структуры баз 1С 7.7 без долгой реструктуризации. Часть 1. Справочники

Практика программирования v7.7 1cv7.md Абонемент ($m)

На днях встретил вопрос на форуме про возможность внесения изменений в конфигурацию без долгого сохранения в рабочей базе большого объема. Вот решил поделиться опытом, как это делал я. База у нас была объемом порядка 120 Гб. К базе обращался сайт в режиме 24/7, поэтому важно было быстро сохранять изменения и желательно без последующего монопольного запуска для восстановления индексов и процедур и без отключения пользователей от базы. Это первая часть статьи и посвящена она справочникам. С одной стороны - это самый простой объект, с другой стороны, именно про справочник спрашивалось на форуме. Если статья будет востребована, то я напишу аналогичные про документы, регистры и может еще что.

1 стартмани

13.08.2013    19248    Reptile    5    

Итоговая строка в форме "Требование-накладная" М-11 в 1С: Бухгалтерия 7.7

Практика программирования v77::БУ 1С7:Бух Россия БУ Абонемент ($m)

Появилась необходимость видеть итог в Требовании-накладная М-11 по колонке "Сумма" Выкладываю - как написать

1 стартмани

15.04.2013    16201    Доня    3    

Обновление не типовой конфигурации на платформе 77 на примере конфигурации "1С: Бухгалтерский учет 7.7"

Практика программирования Администрирование данных 1С v7.7 1cv7.md Россия Абонемент ($m)

На примере обновления не типового релиза 538 "1С: Бухгалтерия 77" по шагам рассказывается как обновить до релиза 539.

1 стартмани

23.04.2012    11484    valux_pux_12345    11    

Исправление ошибки в отчете "Журнал счетов-фактур по Постановлению № 1137" в 1С Бухгалтерии 7.7 537-7.7.538 релиз

Практика программирования v77::БУ 1С7:Бух Россия БУ Абонемент ($m)

В отчете "Журнал счетов-фактур по Постановлению № 1137" в печатной форме не выводятся суммы НДС по ставке 10%, внесенные вручную в документе "Счет-фактура полученный"

1 стартмани

12.04.2012    11617    ksv74    1    

Исправление ошибки вычисления суммы удержанного НДФЛ в ЗиК 320-322

Практика программирования Зарплата Зарплата v77::Расчет 1С7:ЗиК Россия БУ НДФЛ Абонемент ($m)

При заполнении таблицы ДоходыВычетыНалогиСотрудников в функции глобального модуля глСобратьДанныеДляНДФЛ2011() есть ошибка при вычислении колонки "НУ1" (суммы удержанного НДФЛ для целей налогового учёта). Данная статься посвящена её исправлению.

1 стартмани

18.03.2012    17325    andrewks    33    

Берегите родителей или что бывает, когда "Родитель" ушел в себя

Практика программирования Работа с интерфейсом v77::ОУ v77::БУ 1cv7.md Россия Абонемент ($m)

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

1 стартмани

25.05.2011    21137    Tatitutu    14    

Исправление серии ошибок при сохранении/восстановлении множественного фильтра (МФ) в отчётах и обработках в ТиС 9.2

Практика программирования v77::ОУ 1С7:ТиС Россия Абонемент ($m)

Исправление серии ошибок при сохранении/восстановлении множественного фильтра (МФ) в отчётах и обработках в ТиС 9.2 (утеря сохранённых значений и/или некорректное восстановление некоторых параметров МФ при различных вариантах активных элементов формы перед сохранением настройки)

1 стартмани

08.05.2011    15673    andrewks    12    

Динамические массивы в семерке.

Практика программирования v7.7 1cv7.md Россия Абонемент ($m)

Работаем с массивами вычисляемой длины - создаем, пишем, читаем, уничтожаем.

1 стартмани

13.12.2010    18650    Арчибальд    28    

Загогулина (с) "не моё" - 2

Практика программирования v77::Расчет 1С7:ЗиК Россия Абонемент ($m)

Еще один глюк ЗиК 300 в отчете по перс. учету 2010

1 стартмани

27.09.2010    1918    pvk78    3    

Исправление UChoice для корректной работы с SQL

Практика программирования v77::ОУ v77::БУ v77::Расчет 1cv7.md Россия Абонемент ($m)

Рассказывается как исправить типовой универсальный отчет: "Подбор объектов", что бы он работал с SQL. В текущей реализации, которая лежит на ИТС без изменений много лет, в SQL базе отобрать объекты по вхождению в группу справочника невозможно, при этом в DBF варианте все работает.

1 стартмани

31.03.2010    12511    Sk0rp    14    

Доработка отчета "Отчет по кадровым данным и данным расчетов для ЗиК.7.7 ред.2".

Практика программирования Зарплата Управление персоналом (HRM) Зарплата Управление персоналом (HRM) v77::ОУ 1С7:ЗиК Россия Абонемент ($m)

Для подписчиков ИТС доступен отчет "Отчет по кадровым данным и данным расчетов для ЗиК.7.7". Начиная с 2010 года в программе "1С: Зарплата и Кадры" появился еще один журнал расчетов "Страховые взносы". С этим журналом расчетов отчет работать не умеет, т.е. раздел, посвященный ЕСН (с 2010 года страховым взносам), заполняться в 2010 году не будет.

1 стартмани

02.02.2010    18411    bb1962    10    

Ликбез: транспонируем таблицу значений 7.7.

Практика программирования v77::ОУ v77::БУ v77::Расчет 1cv7.md Россия Абонемент ($m)

Меняем "вдоль" на "поперек"

1 стартмани

28.01.2010    14317    Арчибальд    10