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

06.12.12

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

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

Скачать файл

ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки, "строительные материалы" для учетной системы. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.

Наименование SM По подписке [?] Купить один файл
1cpp Свод по видам расчетов за год.ert
.ert 79,50Kb
5
5
1 SM
Скачать Купить за 1 850 руб.

 Для работы с прямыми запросами необходима компонента 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С и в режиме прямого запроса. Как говорится, почувствуйте разницу.

См. также

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

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

09.03.2016    38257    Serginio    22    

44

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

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

05.07.2015    21954    json    3    

22

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

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

3 стартмани

25.03.2014    25301    5    protexprotex    3    

5

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

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

1 стартмани

14.04.2012    34138    305    set2333    16    

11

Запросы Программист Оперативный учет 7.7 1С:Комплексная 7.7 1С:Торговля и склад 7.7 Россия Бесплатно (free)

Первая статья из цикла статей "Прямые запросы:...". Рассказывается о применении класса ПрямойЗапрос и компоненты 1С++ для ускорения функции глВернутьЦену() в типовой конфигурации Торговля и склад. Платформа 7.7.

15.11.2011    16596    leshik    21    

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