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