Глава 20. Импорт из произвольных баз данных и XML-файлов

В ФинГраде предусмотрены возможности импорта не только из 1С, но и из других источников. Нажав кнопку в списке источников импорта (см. п. 18.5.1. Список источников импорта), вы можете добавить источники для импорта:

  1. Из базы данных БЭСТ (фирма-разработчик БЭСТ, http://www.bestnet.ru/company/) :

Импорт из БЭСТ выполняется аналогично импорту из 1С.

  1. Из произвольных баз данных с помощью SQL-запросов.
  1. Из системы автоматизации складского учёта StoreHouse 4 (разработчик — компания UCS, http://www.ucs.ru/products/r-keeper/storehouse/).
  2. Из систем автоматизации ресторанов R-Keeper версий 6, 7 (разработчик — компания UCS, https://www.ucs.ru/products/r-keeper/).
  3. Из iiko — ERP-системы для автоматизации ресторанного бизнеса (http://iiko.ru/about/about-us/).

А вот импорт из произвольных баз данных с помощью запросов на языке SQL (Structured Query Language) — инструмент гораздо более универсальный и мощный. С его помощью вы можете импортировать данные из любых источников, к которым Windows позволяет обращаться на языке SQL. В частности, это базы данных на основе систем управления базами данных Microsoft SQL Server, PostgresPro Server, Oracle, MySQL, Microsoft Access, Microsoft FoxPro, файлы Microsoft Excel, файлы в формате баз данных dBase (.dbf) и многие другие.

20.1. Добавление источника для импорта данных

Чтобы добавить в список источник для импорта произвольной базы данных с помощью SQL-запроса:

  • щёлкните мышью надпись Импорт из баз данных (1С, SQL и др.) на главном экране ФинГрада;
  • или выберите в меню ФинГрада пункт Работа | Импорт проводок бухгалтерии (1С, АБС и т. д.).

Откроется окно с источниками импорта. Нажмите кнопку справа от надписи Импорт проводок бухгалтерии.

В выпадающем списке выберите пункт Из произвольной базы данных:

Откроется окно Импорт из произвольной БД:

Укажите параметры импорта из базы данных:

  1. Нажмите кнопку справа от надписи Параметры БД. Откроется окно Свойства канала передачи данных.
  1. на вкладке выберите метод подключения к базе данных (чаще всего это SQL Server Native Client):

  1. нажав кнопку , перейдите на вкладку . На этой вкладке в поле Введите или выберите имя сервера(Selectorenteraservername) надо ввести имя сервера базы данных — например, (local)\SQLEXPRESS, если сервер базы данных Microsoft SQL Express располагается на том же компьютере. Имя сервера базы данных можно узнать у администратора баз данных или системного программиста в вашей организации, или посмотреть при запуске программы Среда SQLServerManagementStudio:

  1. в поле Выберите базу данных (Selectthedatabase) выберите базу данных — для этого можно щёлкнуть треугольничек в правой части этого поля и выбрать имя базы данных из списка.

  1. нажмите кнопку и вернитесь в окно Импорт из произвольной БД.
  1. В окне Импорт из произвольной БД следует задать параметры подключения к базе данных:
  1. в поле Логин — ввести имя пользователя (учётной записи) для подключения к серверу базы данных;
  2. в поле Пароль — ввести соответствующий пароль;
  3. справа от надписи Юр. Лицо — надо щёлкнуть гиперссылку Выбрать, затем в справочнике аналитики Юр. Лицо щёлкнуть наименование юридического лица, данные о хозяйственных операциях которого Вы хотите импортировать, и затем щёлкнуть кнопку .

Если нужного наименования юридического лица в справочнике нет, надо щёлкнуть кнопку или нажать клавишу на цифровой клавиатуре, чтобы создать новую строку в справочнике, заполнить её и щёлкнуть кнопку (см. главу Глава 9. Справочники аналитик);

  1. в поле Имя источника надо назначить имя источника данных (то есть, условное имя для данного импорта). Имена, назначенные разным источникам данных, не должны совпадать. По умолчанию предлагается имя источника данных, совпадающее с наименованием юридического лица, данные о хозяйственных операциях которого Вы собираетесь импортировать.
  1. Задав эти параметры, нажмите кнопку в левом нижнем углу окна Импорт из произвольной БД. ФинГрад проверит правильность параметров подключения к базе данных. Если параметры были заданы правильно, ФинГрад выдаст сообщение: «Соединение с БД установлено успешно»:

Если же к базе данных подключиться не удалось, ФинГрад выведет сообщение об ошибке. В этом случае надо будет изменить параметры подключения к базе данных и снова проверить подключение к базе данных.

  1. Под надписью Запрос к БД в окне Импорт из произвольной БД приведён пример SQL-запроса к базе данных с комментариями. Вместо этого примера введите запрос к вашей базе данных. О том, как составлять эти запросы, смотрите в п. 20.2. Составление SQL-запроса.

После того, как текст запроса введён, нажмите кнопку , расположенную в нижней части окна. ФинГрад проверит корректность введённого SQL-запроса. Для этого ФинГрад выполняет запрос, но без записи данных в базу данных ФинГрада. Если при этом не возникло ошибок, то ФинГрад выводит сообщение: «Запрос к БД выполнен успешно»:

Если же при выполнении запроса возникли ошибка, ФинГрад выведет сообщение об ошибке, например, такого вида:

В этом случае нужно изменить текст SQL-запроса и снова проверить корректность запроса, щёлкнув мышью кнопку .

  1. После того, как параметры подключения к базе данных и SQL-запрос созданы и проверены, нажмите кнопку .

Вы вернётесь в список источников импорта. В этом списке появится новая строка для импорта из базы данных с помощью SQL-запроса:

В поле Тип этой строки будет написано Inversion (так как наиболее частое применение импорта из базы данных с помощью SQL-запроса — это импорт из банковской системы «Инверсия»). Поле Дата начала учёта для таких строк не заполняется.

20.2. Составление SQL-запроса

20.2.1. Формат запроса при импорте из SQL базы данных

Импорт в ФинГрад из базы данных, работающей под управлением системы управления базами данных, использующей язык SQL (Microsoft SQL, PostgresPro, Oracle, MySQL), обычно осуществляется с помощью SQL-запроса следующего вида:

SELECT

Выражение1ASимя-колонки-ФинГрада-1,

Выражение2ASимя-колонки-ФинГрада-2,

ВыражениеNASимя-колонки-ФинГрада-N

FROMтаблица

WHERE opDate >=? ANDopDate<?(здесь могут быть указаны дополнительные условия)

ORDER BY opDate ASC

Здесь:

  • Выражение1, Выражение2, … ВыражениеN — выражения языка SQL, задающие значения, которые будут присвоены соответствующие полям проводки ФинГрада;
  • имя-колонки-ФинГрада-1, имя-колонки-ФинГрада-2, … имя-колонки-ФинГрада-N —имена, указывающие, каким колонкам (полям) проводки ФинГрада будут присвоены эти значения. Перечень имён колонок приведён ниже;
  • таблица — выражение языка SQL, задающее таблицу, на основании которой формируются проводки ФинГрада. По каждой строке этой таблицы, удовлетворяющей условию WHERE, формируется проводка ФинГрада. А при определённых условиях, о которых будет сказано ниже — даже две или три проводки (см. п. 20.7. Отличия от импорта из 1С).

20.2.2. Имена колонок ФинГрада, используемые в SQL-запросе

Имена колонок ФинГрада, которые можно применять в SQL-запросе, таковы.

Имя колонки ФинГрада

 

Назначение

opDate

 

Дата проводки. Импортируемые данные должны быть упорядочены по дате проводки.

LegalEntity

 

Наименование юридического лица, к которому относится проводка.

Comment

 

Комментарий к проводке.

SK_имя_аналитики1
SK_имя_аналитики2

 

Имена аналитик ФинГрада, относящихся ко всей проводке. Имя аналитики задаётся после символов SK_. Например, SK_Документ означает, что соответствующие данные будут записаны в аналитику ФинГрада Документ.

Db_account

 

Имя счёта дебета проводки. Это значение не может быть пустым.

Cr_account

 

Имя счёта кредита проводки. Это значение не может быть пустым.

Db_currency

 

Валюта дебета проводки (например, RUB, USD, EUR и т. п.).

Cr_currency

 

Валюта кредита проводки (например, RUB, USD, EUR и т. п.).

Db_sum

 

Сумма дебета проводки (выраженная в валюте дебета проводки).

Cr_sum

 

Сумма кредита проводки (выраженная в валюте кредита проводки).

Db_GoodsAmount

 

Количество товара дебета проводки.

Cr_GoodsAmount

 

Количество товаракредита проводки.

Db_accountHint

 

Подсказка, используемая при создании счёта дебета проводки(см. п. 20.4. Создание счетов при импорте ниже). Этот параметр не обязателен.

Cr_accountHint

 

Подсказка, используемая при создании счёта кредита проводки (см. п. 20.4. Создание счетов при импорте ниже). Этот параметр не обязателен.

Db_accountComment

 

Комментарий, который задаётся при создании счёта дебета проводки (см. п. 20.4. Создание счетов при импорте ниже). Этот параметр не обязателен.

Cr_accountComment

 

Комментарий, который задаётся при создании счёта кредита проводки (см. п. 20.4. Создание счетов при импорте ниже). Этот параметр не обязателен.

Db_SK_имя_аналитики1
Db_SK_имя_аналитики2

 

Имена аналитик ФинГрада, относящихся к дебету проводки. Имя аналитики задаётся после символов Db_SK_. Например, Db_SK_Товар означает, что соответствующие данные относятся к дебету проводки и будут записаны в аналитику ФинГрада Товар.

Cr_SK_имя_аналитики1
Cr_SK_имя_аналитики2

 

Имена аналитик ФинГрада, относящихся к кредиту проводки. Имя аналитики задаётся после символов Cr_SK_. Например, Cr_SK_Товар означает, что соответствующие данные относятся к кредиту проводки и будут записаны в аналитику ФинГрада Товар.

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

'Фирма1' AS LegalEntity

2. Выражение ORDER BY opDate ASC в запросе обеспечивает упорядоченность выдаваемых запросом данных по возрастанию значения opDate — даты проводки.

20.2.3. Таблица и условие отбора

Таблица, на основании которой формируются проводки ФинГрада, указывается после оператора языка SQL FROM. Эта таблица может содержаться в базе данных, например:

FROM [VF_SQL_2].[dbo].[PostsData$]

Но чаще эта таблица задаётся как выражение SQL, поскольку нужные для импорта в ФинГрад данные приходится собирать из нескольких таблиц базы данных. Например:

FROM xxi.trn LEFT JOIN xxi.CDE

ON trn.itrnnum=cde.icdetrnnum AND trn.itrnanum=cde.icdetrnanum

LEFT JOIN gtr ON trn.itrnnum = igtrtrnnum AND trn.itrnanum = igtrtrnanum AND igtrcat=703

LEFT JOIN obg ON igtrcat=iobgcat AND igtrnum=iobgnum

Условие отбора. Выше мы написали, что в SQL-запросе для импорта из базы данных должна содержаться следующая строка:

WHERE opDate >=? AND opDate < ?

Эта строка задаёт условие отбора: импортироваться будут только записи о хозяйственных операциях, в которых дата проводки находятся между начальной и конечной датой, заданными в окне Импорт проводок.

Но если данные для импорта собираются в SQL-запросе из нескольких таблиц базы данных, то к условиям opDate >=? и opDate<? могут добавиться дополнительные условия, например:

WHERE opDate >=? AND opDate<? AND (dt.ACC_NAME is not null AND ct.ACC_NAME is not null AND fAcDt.DtIgnor = 0 AND fAcKt.KtIgnor = 0)

Примеры составления SQL-запросов для импорта в ФинГрад из баз данных мы приведём в п. п. 20.9. Пример 1. Импорт проводки, заданной в SQL-запросе20.12. Примеры SQL-запросов, использованных в реальных задачах.

20.3. Соответствие счетов и аналитик при импорте

При импорте из проводок 1С соответствие счетов 1С счетам ФинГрада (счетам управленческого учёта), задаётся на вкладке в настройках импорта (см. п. 18.8. Настройка отображения счетов 1С в счета ФинГрада). А соответствие субконто 1С аналитикам ФинГрада — на вкладке . А при импорте из базы данных с помощью SQL-запроса этих вкладок в настройках импорта нет. Это значит, что отображение наименований счетов и аналитической информации, содержащихся в исходных проводках, в ФинГрад, должны быть выполнены иными средствами.

20.3.1. Соответствие счетов при импорте

При выполнении SQL-запроса в полях Db_account и Cr_account должны быть записаны имена счетов ФинГрада. Однако в исходных проводках, которые надо импортировать в ФинГрад, чаще всего содержатся не названия счетов ФинГрада, а наименования (номера) счетов бухгалтерского учёта. Поэтому Вы можете:

  1. Либо использовать в ФинГраде те же наименования счетов, что и в бухгалтерском учёте. Обычно удобно создавать эти счета как субсчета соответствующих им счетов ФинГрада.
  2. Либо задать отображение наименований счетов бухгалтерского учёта в наименования счетов ФинГрада.

Первый вариант обычно используется в банках — там счетов бухгалтерского учёта может быть очень много, десятки и сотни тысяч. Ведь операции по каждому открытому в банке расчётному, кредитному или депозитному счёту отображаются на отдельном счёте бухгалтерского учёта. Счета ФинГрада, соответствующие этим счетам бухгалтерского учёта, как правило, автоматически создаются при импорте и автоматически помещаются в нужную группу счетов ФинГрада на основании сведений, содержащихся в справочнике аналитики Добавление счетов из импорта (см. п. 20.4. Создание счетов при импорте и 20.10.6. Заполнение справочника аналитики «Добавление счетов из импорта»).

Второй вариант обычно используется в небанковских организациях, применяющих обычный план счетов бухгалтерского учёта. Отображение номеров счетов бухгалтерского учёта в имена счетов ФинГрада обычно осуществляется с помощью вспомогательной таблицы данных. В п. 20.10.4. Задание наименований счетов ФинГрада мы покажем, как это делается средствами Microsoft Excel, а в п. 20.11.3. Запрос для импорта из базы данных Microsoft SQL Server — как это делается с использованием возможностей языка SQL, то есть, в SQL-запросе при импорте в ФинГрад.

20.3.2. Соответствие аналитик при импорте

Имена аналитик, в которые будут записаны данные, импортированные из базы данных с помощью SQL-запроса, задаются непосредственно в SQL-запросе. Как уже сказано выше, для задания этих имён аналитик в SQL-запросе используются следующие названия:

Имя колонки ФинГрада

 

Назначение

SK_имя_аналитики1
SK_имя_аналитики 2

 

Имена аналитик ФинГрада, относящихся ко всей проводке. Имя аналитики задаётся после символов SK_. Например, SK_Документ означает, что соответствующие данные будут записаны в аналитику ФинГрада Документ.

Db_SK_имя_аналитики1
Db_SK_имя_аналитики 2

 

Имена аналитик ФинГрада, относящихся к дебету проводки. Имя аналитики задаётся после символов Db_SK_. Например, Db_SK_Товар означает, что соответствующие данные относятся к дебету проводки и будут записаны в аналитику ФинГрада Товар.

Cr_SK_имя_аналитики1
Cr_SK_имя_аналитики 2

 

Имена аналитик ФинГрада, относящихся к кредиту проводки. Имя аналитики задаётся после символов Cr_SK_. Например, Cr_SK_Товар означает, что соответствующие данные относятся к кредиту проводки и будут записаны в аналитику ФинГрада Товар.

Если имя аналитики ФинГрада содержит пробелы, точки, дефисы или иные специальные символы, то всё имя соответствующей колонки, включая символы SK_, или Db_SK_, или Cr_SK_, надо заключить в квадратные скобки. Например:

[SK_Документ 1С]

[Db_SK_90.1 Разнесение выручки]

[Cr_SK_Наценки.Ставка]

Аналитики ФинГрада, имена которых задаются после символов SK_, или Db_SK_, или Cr_SK_, при выполнении импорта должны уже существовать. Если после символов SK_, или Db_SK_, или Cr_SK_ встретится неизвестное ФинГраду имя аналитики, то ФинГрад выдаст ошибку и импорт не будет выполнен.

20.4. Создание счетов при импорте

Если при импорте из базы данных с помощью SQL-запроса ФинГрад не найдёт счёта, указанного в поле Db_account или Cr_account, то он может сам создать счёт ФинГрада с заданным именем. Нужно только указать ФинГраду, в какой группе счетов надо создавать этот счёт. Это необходимо для того, чтобы отчёты о финансовых результатах были правильными.

20.4.1. Справочник аналитики «Добавление счетов из импорта»

Для указания группы счетов, в которой должен быть создан счёт, используется справочник аналитики Добавление счетов из импорта.

Назначение колонок справочника. В основной колонке справочника аналитики Добавление счетов из импорта записаны маски — строки символов, в которых могут использоваться специальные символы * и ?. Эти символы имеют обычное значение:

? — любой символ или отсутствие символа,

* — любое количество любых символов или отсутствие символа.

В колонке Группа счетов указывается группа счетов, в которой будет создаваться счёт ФинГрада, если имя счёта или подсказка удовлетворяют данной маске (см. ниже).

Если в справочнике имеются другие колонки, то они задают свойства создаваемого счёта.

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

20.4.2. Порядок использования справочника

Справочник аналитики Добавление счетов из импорта используется ФинГрадом, если он при импорте из базы данных с помощью SQL-запроса не найдёт счёта, указанного в поле Db_account или Cr_account. Порядок использования этого справочника следующий.

  1. Сначала ФинГрад определяет строку символов, по которой будет искать подходящую маску в справочнике:
  1. если ФинГрад обращается к справочнику аналитики Добавление счетов из импорта потому, что он не нашёл счёта, указанного в поле Db_account, то строка символов для поиска — это значение поля Db_accountHint (подсказка). А если это значение не задано — то значение поля Db_account (имя счёта дебета проводки);
  2. аналогично, если ФинГрад обращается к справочнику аналитики Добавление счетов из импорта потому, что он не нашёл счёта, указанного в поле Cr_account, то строка символов для поиска маски — это значение поля Cr_accountHint (подсказка). А если оно не задано — то значение поля Cr_account (имя счёта кредита проводки).
  1. Далее ФинГрад просматривает в алфавитном порядке основную колонку справочника аналитики Добавление счетов из импорта. И ищет в этой колонке маску, под которую подходит строка, заданная в п. 1.
  2. Если такая маска имеется, то:
  1. значение поля в колонке Группа счетов той строки, в которой найдена подходящая маска, определяет название группы счетов, в которой ФинГрад будет создавать новый счёт (субсчёт);
  2. если в справочнике аналитики, кроме основной колонки и колонки Группа счетов, есть и другие колонки, то ФинГрад устанавливает для созданного счёта свойства согласно значениям в этих колонках той же строки справочника.
  1. Если для счёта не найдена подходящая маска, или если счёт не может быть создан, или если произошла какая-то иная ошибка, то вместо такого счёта используется счёт ФинГрада К разноске.

Замечания. 1. Счёт не может быть создан, например, если значение в колонке Группа счетов указывает на счёт, по которому имеются проводки. Создавать субсчета к таким счетам нельзя.

2. После нахождения подходящей маски ФинГрад прекращает поиск в таблице аналитики Добавление счетов из импорта. Иными словами, ФинГрад создаёт счёт в группе счетов, указанной в колонке Группа счетов первой строки, в которой обнаружена подходящая маска.

20.4.3. О подсказках Db_accountHint и Cr_accountHint

«Подсказки» Db_accountHint и Cr_accountHint могут использоваться для указания группы счетов ФинГрада, в которой надо создавать новый счёт при импорте с помощью SQL-запроса.

Для банков группы счетов ФинГрада, к которым относится счёт банковского учёта (банковский счёт), обычно определяются по наименованию банковского счёта. Но некоторые банковские счета являются исключением. Их надо отображать в иные группы счетов ФинГрада, чем банковские счета с похожим именем. Для обработки таких счетов-исключений используются «подсказки» Db_accountHint и Cr_accountHint.

При импорте данных о хозяйственных операциях российских небанковских организаций в качестве подсказки для создания счетов (Db_accountHint или Cr_accountHint) удобно использовать номера счётов бухгалтерского учёта, содержащиеся в импортируемых проводках. В этом случае подсказки Db_accountHint и Cr_accountHint могут задаваться во всех импортируемых проводках.

20.4.4. Примеры составления справочника аналитики «Добавление счетов из импорта»

Пример заполнения справочника аналитики Добавление счетов из импорта для банковских организаций приведён на рисунке выше, в п. 20.4.1. Справочник аналитики «Добавление счетов из импорта». Здесь новые счета при импорте добавляются на основании наименований счетов банковского бухгалтерского учёта. Полностью этот пример приведён в приложении Приложение 7. Справочник «Добавление счетов из импорта» для банков.

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

Добавление счетов из импорта

Группа счетов

01*

Основные средства

02*

Основные средства

03*

Товары на складах

04*

Прочие операционные расходы

07*

Товары на складах

08*

Прочие активы

10*

Товарные запасы

11*

Товарные запасы

14*

Товарные запасы

15*

Товарные запасы

16*

Товарные запасы

19*

НДС по приобретенным ценностям (сч.19)

20*

Товарные запасы

21*

Товарные запасы

23*

Товарные запасы

28*

Товарные запасы

29*

Товарные запасы

40*

Товарные запасы

41*

Товарные запасы

42*

Товарные запасы

43*

Товарные запасы

44*

Операционные расходы

46*

Расчеты с покупателями

50*

Касса

55.01*

Денежные средства

55.21*

Денежные средства

57*

Деньги в пути

58*

Финансовые вложения

59*

Финансовые вложения

60*

Поставщики

62*

Покупатели

66*

Кредиты и займы полученные

67*

Кредиты и займы полученные

68*

Расчеты по налогам

69*

Прочие расчеты по социальному страхованию и обеспечению

73*

Прочие расчеты

75*

Вложения капитала

76*

Прочие расчеты

80*

Вложения капитала

81*

Вложения капитала

82*

Резервный капитал

83*

Вложения капитала

84*

Нераспределенная прибыль

86*

Прочие Доходы/Расходы

90.02*

Себестоимость продаж

90.04*

Себестоимость продаж

90.05*

Себестоимость продаж

91*

Прочие Доходы/Расходы

94*

Недостатки и потери от порчи ценностей

96*

Резервы прочие

97*

Расходы будущих периодов

98*

Доходы будущих периодов

99*

Прибыли и Убытки

Можно поступить и проще — не задавать многочисленные правила для создания счетов, а создать всего один счёт ФинГрада, например, Счета, добавленные при SQL-импорте. И сделать справочник аналитики Добавление счетов из импорта всего с одной строкой:

Добавление счетов из импорта

Группа счетов

*

Счета, добавленные при SQL-импорте

В этом случае при импорте в ФинГрад с помощью SQL-запроса, когда в поле Db_account или Cr_account будет встречено имя счёта, которого нет в плане счетов ФинГрада, то этот счёт будет создан как субсчёт в группе счетов Счета, добавленные при SQL-импорте. После выполнения импорта все счета, появившиеся в этой группе счетов, надо будет вручную переместить в нужные группы счетов ФинГрада.

Замечания. 1. В приведённой выше таблице счёт 20 «Незавершённое производство» отображается в группу счетов «Товарные запасы», поскольку согласно п. 6 стандарта МСФО (IAS) 2 «Запасы», активы, находящиеся в процессе производства для продажи в ходе обычной деятельности, учитываются в составе запасов.

2. Счетов 25 «Общепроизводственные расходы» и 26 «Общехозяйственные расходы» в приведённой выше таблице нет, так как обычно расходы, которые учитываются на этих счетах, должны разноситься в разные группы счетов МСФО в соответствии с их экономическим смыслом. Это может осуществляться, например, с помощью трансформационной проводки при импорте. Счёт ФинГрада, на который разносятся расходы, может определяться с помощью справочника аналитики. Например, на приведённом ниже рисунке разнесение общепроизводственных расходов выполняется с помощью справочника аналитики 25 Разнесение общепроизводственных расходов. В колонке Статья этой аналитики указывается статья общепроизводственных расходов, а в колонке Счет расходов — соответствующий счёт ФинГрада. В этом примере у аналитики есть ещё колонка Подразделение, так как часто нужный счёт ФинГрада должен выбираться в зависимости от того, к какому подразделению организации относится проводка.

Здесь условие на аналитику Контрагент: Колонка("Юр.лицо контрагента").Значение == "" || Колонка("Юр.лицо контрагента").Значение == р.Аналитика("Юр. лицо").Значение означает, что зависимая проводка не генерируется для внутрихолдинговых хозяйственных операций.

20.5. Трансформации при импорте

При импорте из базы данных с помощью SQL-запроса, так же как при импорте из проводок 1С, можно задавать правила трансформаций. Для задания трансформаций используется вкладка окна параметров импорта.

Чтобы настроить трансформации при импорте из базы данных с помощью SQL-запроса, следует:

На экран будет выведена вкладка окна настроек импорта. Эта вкладка выглядит и используется так же, как при импорте из проводок 1С (см. п. 18.10. Настройка трансформаций при импорте).

Чтобы настроить правила трансформаций, надо щёлкнуть мышью кнопку в нижнем левом углу окна.

Рекомендуемые трансформации при импорте. При импорте из базы данных с помощью SQL-запроса обычно имеет смысл задавать следующие правила трансформации.

  1. Трансформации для игнорирования проводок. При импорте из проводок 1С ФинГрад по умолчанию предлагает игнорировать проводки по дебету и кредиту некоторых счетов бухгалтерского учёта (см. п. 18.8.3. Настройка игнорирования проводок по дебету или кредиту счёта). В частности, обычно игнорируются проводки по закрытию счетов для определения прибыли и убытка (все проводки по счетам бухгалтерского учёта 99.1 «Прибыли и убытки», 91.9 «Прибыль/убыток от продаж», 91.9 «Сальдо прочих доходов и расходов», проводки по кредиту счетов 90.2 «Себестоимость продаж», 90.3 «Налог на добавленную стоимость», и по дебету счёта 90.1 «Выручка»). Это делается на вкладке окна настроек импорта из 1С.
  2. При импорте из базы данных с помощью SQL-запроса аналогичные действия по игнорированию проводок выполняются с помощью правил трансформаций, как это описано в п. 18.10.4. Использование трансформации для фильтрации проводок.
  3. Трансформации для распределения издержек. Часто проводки по учёту затрат, для которых в бухгалтерском учёте используются счета 25 «Общепроизводственные расходы», 26 «Общехозяйственные расходы», 44 «Расходы на продажу», должны разноситься в разные группы счетов МСФО в соответствии с их экономическим смыслом. В таких случаях в настройках импорта из базы данных с помощью SQL-запроса следует написать правила трансформаций, которые вместо проводок бухгалтерского учёта по распределению затрат создают проводки ФинГрада, по тем правилам, которые установлены в учётной политике управленческого учёта.
  4. Трансформации для корректировки различий между учётными политиками бухгалтерского и управленческого учёта. В управленческом учёте часто применяется учётная политика, отличная от учётной политики бухгалтерского учёта. Это может быть вызвано следующими причинами:
  1. потребностями управления. Например, себестоимость продукции в управленческом учёте может быть необходимо оценивать по прямым издержкам (метод «директ-костинг»);
  2. стремлением соблюсти в управленческом учёте стандарты МСФО;
  3. организационными причинами. Например, в дочерней компании иностранной фирмы бухгалтерский учёт должен вестись согласно национальным стандартам, а управленческий учёт может вестись по правилам, установленным материнской компанией.

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

  1. генерировали те корректировочные проводки, которые можно создавать автоматически;
  2. помечали данные о проводках, в связи с которыми требуется решение человека (бухгалтера, ведающего управленческим учётом, финансового директора и т. п.). Например, у таких проводок счётом дебета или счётом кредита может быть установлен субсчёт, у которого в имени содержатся слова к разноске. Или же для них может быть установлено определённое значение аналитики.

20.6. Выполнение импорта из базы данных

После того, как мы произвели все необходимые настройки для импорта из базы данных с помощью SQL-запроса, а именно:

мы можем выполнить импорт из базы данных с помощью SQL-запроса.

Запуск импорта. Чтобы начать импорт из базы данных с помощью SQL-запроса, следует:

  • щёлкнуть мышью надпись Импорт из баз данных (1С, SQLи др.) на Главном экране ФинГрада;
  • или выбрать в меню ФинГрада пункт Работа | Импорт проводок бухгалтерии (1С, АБСи т. д.).

На экран будет выведен список источников импорта. В этом списке следует:

  1. Щёлкнуть мышью строку, соответствующую нужному источнику импорта.
  2. В нижнем левом углу окна со списком источников импорта указать период, за который Вы будете импортировать данные о хозяйственных операциях (проводки).

Задание периода осуществляется так же, как при импорте из 1С (см. п. 18.5.2. Задание периода импорта).

  1. Щёлкнуть мышью кнопку . ФинГрад запустит процесс импорта.

Выполнение импорта. Как и при импорте из проводок 1С, Вы можете остановить выполнение импорта, щёлкнув мышью кнопку , которая выводится в левом нижнем углу окна ФинГрада, пока ФинГрад импортирует проводки. А также узнать, какая часть проводок уже импортирована, по индикатору хода выполнения импорта:

По окончании импорта будет выдано сообщение о количестве ошибок и предупреждений при импорте:

Просмотр результатов импорта. После того, как Вы щёлкнете кнопку в сообщении о количестве ошибок и предупреждений (или об успешном выполнении импорта), на экран будет выдано окно со списком проводок — то есть, с результатами импорта:

Замечание. Импортированы будут только те проводки, у которых наименование юридического лица (поле LegalEntity SQL-запроса) совпадает с наименованием юридического лица, указанным в колонке Юр. Лицо той строки списка источников данных, с помощью которой был запущен импорт. Если в базе данных содержатся сведения о хозяйственных операциях нескольких юридических лиц, и их надо импортировать в ФинГрад, надо для каждого юридического лица создать свою строку в окне со списком источников данных. . Или указать в SQL-запросе поле LegalEntity как константу, а значение аналитики Юр. Лицо импортировать через другое поле SQL-запроса, например, как поле [SK_Юр. лицо]. Тогда при импорте всегда будут импортироваться проводки для всех юридических лиц.

Ошибки и предупреждения. Если при импорте были сформированы сообщения об ошибках и/или предупреждения, то они выводятся в панели Ошибки и предупреждения:

Действия при наличии список ошибок и предупреждений. Вы можете:

  • просматривать список ошибок и предупреждений;
  • исправлять ошибки в настройках импорта на вкладке окна настроек импорта;
  • исправлять SQL-запрос, щёлкнув в окне со списком источников импорта соответствующую строку списка, и затем щёлкнув кнопку ;
  • настраивать план счетов ФинГрада (команда меню Основная информация | Счета) и настройки аналитик (команда меню Основная информация | Аналитики);
  • заново запускать импорт щелчком по кнопке .

Выход из окна просмотра и настроек импорта. Окончив работу по импорту данных, следует щёлкнуть мышью кнопку .

20.7. Отличия от импорта из 1С

В ФинГраде импорт из базы данных с помощью SQL-запроса имеет некоторые отличия от импорта из проводок базы данных 1С.

Отличия в значениях аналитик. ФинГрад помещает в создаваемые при импорте проводки значения некоторых служебных аналитик. Набор и значения этих аналитик при импорте из базы данных с помощью SQL-запроса несколько отличаются от тех, что имеют место при импорте из проводок базы данных 1С.

  1. При импорте из проводок 1С ФинГрад устанавливает значения аналитик, указанных на вкладке окна настроек импорта, а также значения аналитик Db счет 1C, Cr счет 1C, Валюта 1С, Валютная сумма 1С, Рублевая сумма 1С, Документ 1С.

А при импорте из базы данных с помощью SQL-запроса ФинГрад устанавливает значения аналитик, указанных в SQL-запросе (поля запроса SK_имя-аналитики, или Db_SK_имя-аналитики, или Cr_SK_имя-аналитики).

  1. При импорте из базы данных с помощью SQL-запроса у аналитики Источник данных:
  1. не задано значение колонки Дата начала учета;
  2. в колонке Тип указано значение Inversion.

Отсутствие запросаначальных остатков. Ещё одно важное отличие от импорта из проводок 1С состоит в том, что при импорте из базы данных с помощью SQL-запроса ФинГрад не производит импорт начальных остатков (см. п. 18.5.8. Импорт начальных остатков).

Отсутствие главной валюты. В 1С имеется «главная валюта», как правило, это российский рубль. И если для дебета или кредита проводки не указана валюта, то считается, что это главная валюта. А при импорте из базы данных с помощью SQL-запроса такого понятия, как «главная валюта», нет. Поэтому валюту необходимо задавать и для дебета, и для кредита каждой проводки.

Обработка «расщепления» проводок. Как мы описывали в п. 18.11.3. Генерация двух проводок ФинГрада по одной проводке 1С, при импорте из 1С бывают ситуации, когда исходная проводка не может быть представлена в виде одной проводки ФинГрада. Например, если по дебету и кредиту исходной проводки имеется один и тот же вид субконто (который переходит в аналитику ФинГрада), но с разными значениями для дебета и кредита. Или если суммы, валюты или количество по дебету и кредиту в исходной проводке не совпадают. В этих случаях исходная проводка представляется в виде двух проводок ФинГрада.

При импорте из базы данных с помощью SQL-запроса исходная проводка тоже не всегда может быть представлена в виде одной проводки ФинГрада. Но обрабатывается эта ситуация не совсем так, как при импорте из 1С. Отличия следующие:

  1. В качестве промежуточного счёта в создаваемых проводках используется:
  • при импорте из 1С — счёт 00;
  • при импорте с помощью SQL-запроса — счёт Транзит для импорта из БД.
  1. Если при импорте из 1С оказывается, что валюты по дебету и кредиту в исходной проводке не совпадают, то ФинГрад использует в качестве промежуточного счёта для создаваемых проводок не счёт 00, а счёт Обмен, и генерирует две проводки.

А при импорте с помощью SQL-запроса на счёте Обмен осуществляется только обмен валют. Если в исходной проводке по дебету и кредиту указаны не только разные валюты, но и разные количества, или разные значения одной и той же аналитики, то в дополнение к проводке для обмена валюты создаются ещё две проводки ФинГрада, с использованием промежуточного счёта Транзит для импорта из БД.

Таким образом, по одной исходной проводке могут быть сформированы:

  • при импорте из 1С — одна или две проводки ФинГрада;
  • при импорте с помощью SQL-запроса — одна, две или три проводки ФинГрада.

Переименование счетов ФинГрада. При импорте из 1С все данные о счетах ФинГрада хранятся в ФинГраде, и 1С ничего о них не знает. ФинГрад корректно обрабатывает изменения в своём плане счетов, поэтому Вы можете переименовывать счета ФинГрада, перемещать их из одной группы счетов в другую, и импорт из 1С после этого всё равно будет производиться правильно. Ранее импортированные проводки при этом не надо будет повторно импортировать в ФинГрад.

А при импорте с помощью SQL-запроса результат SQL-запроса должен содержать имена счетов ФинГрада. Поэтому счета ФинГрада, которые используются в проводках, импортируемых из базы данных с помощью SQL-запроса, можно перемещать из одной группы счетов в другую. Но переименовывать эти счета нельзя. Если это сделать, то ФинГрад при следующем импорте с помощью SQL-запроса сторнирует ранее импортированные из того же источника данных проводки по переименованному счёту, создаст счёт со старым именем и добавит проводки по этому счёту.

Если переименование счёта ФинГрада, используемого в проводках, импортируемых из базы данных с помощью SQL-запроса, всё же необходимо, надо не только переименовать счёт, но и обеспечить, чтобы SQL-запрос выдавал (в полях Db_account или Cr_account) новое имя счёта ФинГрада.

Переименование аналитик ФинГрада. Если требуется переименовать аналитику ФинГрада, используемую в проводках, импортируемых из базы данных с помощью SQL-запроса, то имя аналитики надо поменять не только в запросе Основная информация | Аналитики, но и в SQL-запросе.

20.8. Особенности импорта из внешних источников данных

ФинГрад может импортировать данные с помощью SQL-запроса не только из SQL баз данных, но и таких источников, как базы данных Microsoft Access, Microsoft FoxPro, из файлов Microsoft Excel, файлов dBase и тому подобных источников.

Импорт из таких источников данных обычно осуществляется через установленные в Windows ODBC или OLEDB драйверы. Эти драйверы поддерживают лишь ограниченное подмножество возможностей языка SQL. Из-за этих ограничений вроде бы вполне корректные SQL-запросы могут вызывать ошибки при импорте: драйвер отказывается выполнять импорт и выдаёт сообщение об ошибке. В частности, проблемы при импорте могут вызывать:

  1. Оператор ORDERBY —например, драйвер ODBC или OLEDB отказывается выполнять импорт из Microsoft Access или Microsoft Excel, если в SQL-запросе имеется оператор ORDERBY. Поэтому приходится этот оператор не использовать в запросе. Это означает, что таблица, из которой будет производиться импорт, должна быть упорядочена по дате и времени проводки (по полю opDate).
  2. Задание имён колонок с помощью оператора AS. Например, при импорте из Microsoft Excel выражения типа

    ВыражениеASимя-колонки-ФинГрада

    могут вызывать сообщения об ошибке. Поэтому приходится создавать таблицу данных, из которой импортируются данные в ФинГрад так, чтобы имена колонок в этой таблице были такие, как нужно ФинГраду (см. п. 20.2.2. Имена колонок ФинГрада, используемые в SQL-запросе). И чтобы нужные ФинГраду данные содержались в этой таблице, а не вычислялись в SQL-запросе. Это позволяет обойтись без оператора AS.

    производиться импорт, должна быть упорядочена по дате и времени проводки (по полю opDate).
  3. При импорте из Microsoft Excel поля с точками в названии и пробелами в начале или конце названия импортироваться не будут. Если в файле-источнике есть такие поля, переименуйте их перед импортом.

Например, запрос для импорта в ФинГрад из Microsoft Excel может быть таким:

SELECT

opDate, LegalEntity, comment,

[SK_ДокументНомер], [SK_Документ], [SK_ЖурналНомер],

Db_account, Db_accountHint, Db_GoodsAmount, Db_currency, Db_sum,

[Db_SK_Товар], [Db_SK_Сотрудник], [Db_SK_Контрагент],

Cr_account, Cr_accountHint, Cr_GoodsAmount, Cr_currency, Cr_sum,

[Cr_SK_Товар], [Cr_SK_Сотрудник], [Cr_SK_Контрагент]

FROM [Проводки$]

WHERE opDate >= ? AND opDate < ?

Здесь в запросе используются аналитики ДокументНомер, Документ, и ЖурналНомер, относящиеся к проводке в целом, и аналитики Товар, Сотрудник и Контрагент, которые могут относиться как к дебету, так и к кредиту проводки. Выражение FROM [Проводки$] в запросе означает, что данные импортируются из листа Проводки файла таблицы Microsoft Excel. Названия колонок следует указывать в первой строке листа Excel:

Пример такого импорта из файлов Microsoft Excel мы покажем в п. 20.10. Пример 2. Импорт данных о проводках из Microsoft Excel.

Импорт с помощью Microsoft SQL/PostgresPro Server. Есть ещё один способ импорта в ФинГрад из баз данных MicrosoftAccess, Microsoft FoxPro, файлов Microsoft Excel, файлов dBase и тому подобных источников. Это использование возможностей Microsoft SQL/PostgresPro Server, который сам умеет импортировать данные из внешних источников. В п. 20.11.4. Запрос для импорта из файла Microsoft Excel и 20.11.5. Запрос для импорта из базы данных Microsoft Access мы приведём примеры такого импорта данных из файлов Microsoft Excel и Microsoft Access.

Такой импорт выполняется следующим образом.

  1. При настройке в ФинГраде источника импортируемых данных (см. п. 20.1. Добавление источника для импорта данных) следует выбрать в окне Свойства канала передачи данных на вкладке метод подключения SQL Server Native Client.
  2. На вкладке этого окна в поле Введите или выберите имя сервера (Select or enter a server name) надо ввести имя сервера базы данных (например, (local)\SQLEXPRESS, если Microsoft SQL/PostgresPro Server установлен на том же компьютере.
  3. В поле Выберите базу данных (Select the database) надо ввести имя новой базы данных. А если при импорте в ФинГрад используется какая-то база данных Microsoft SQL/PostgresPro Server — то выбрать в указанном поле эту базу данных.
  4. В SQL-запросе для обращения к исходным данным после ключевого слова FROM вместо имени таблицы следует использовать функцию OPENROWSET. Например, для импорта из листа Проводки Excel-файла D:\TEST\FG.XLSX можно использовать в SQL-запросе конструкцию:

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 XML;Database=D:\TEST\FG.XLSX;HDR=YES',

'SELECT * FROM [Проводки$]')

При импорте из файла с расширением .XLS вместо Excel 12.0 XML надо написать Excel 8.0 .

А для импорта из таблицы Проводки базы данных Microsoft Access D:\TEST\FG.ACCDB можно использовать в SQL-запросе конструкцию:

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'D:\TEST\FG.accdb';'Admin';,

'SELECT * FROM [Проводки]')

Замечание. Некоторые версии драйвера Microsoft.ACE.OLEDB.12.0 при указании параметра HDR=YES ошибочно передают в качестве результата на одну запись больше. Чтобы обойти эту ошибку, можно в конец SQL-запроса включить выражение WHERE opDate IS NOT NULL .

20.9. Пример 1. Импорт проводки, заданной в SQL-запросе

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

Мы составим три SQL-запроса, передающих в ФинГрад одну проводку с данными, указанными в самом тексте запроса. К базе данных эти запросы не обращаются, и никаких данных из базы данных не получают.

20.9.1. Создаём источник импорта и вводим одну обычную проводку

Создадим для нашего примера новое юридическое лицо, чтобы наши эксперименты не внесли изменений в существующую отчётность. И для этого юридического лица создадим источник данных для импорта с помощью SQL-запроса.

  1. Щёлкнем мышью надпись Импорт из баз данных (1С, SQL и др.) на Главном экране ФинГрада, или выберем в меню ФинГрада пункт Работа | Импорт проводок бухгалтерии (1С, АБС и т. д.).
  2. В выведенном на экран списке источников импорта щёлкнем мышью кнопку справа от надписи Импорт проводок бухгалтерии, и выберем в выведенном меню пункт Из произвольной базы данных. На экран будет выведен диалоговый запрос Импорт из произвольной БД:
  3. Щёлкнем кнопку справа от надписи Параметры БД, выведем окно Свойства канала передачи данных. В этом окне зададим подключение к любой базе данных, как это описано в п. 20.1. Добавление источника для импорта данных, например, к базе данных ФинГрада. Это совершенно безопасно, так как наш запрос не вносит изменения в базу данных, и даже вообще к ней не обращается.
  4. В поля Логин и Пароль введём имя пользователя (учётной записи) и пароль для подключения к серверу базы данных.
  5. Щёлкнем гиперссылку Выбрать справа от надписи Юр. Лицо.
  6. В выведенном на экран справочнике аналитики Юр. Лицо щёлкнем кнопку и введём в новую строку в основную колонку аналитики строку Рога и копыта. Затем и щёлкнем кнопку и потом кнопку .

  1. В поле Имя источника будет предложено название источника импорта Рога и копыта. Можно оставить название или поменять его на какое-то другое. Например, можно назвать источник импорта так: Рога и копыта — пример 1.
  2. Ниже, под надписью Запрос к БД, вместо записанного образца надо ввести следующий запрос:

SELECT

'Рога и копыта' as LegalEntity, -- Юридическое лицо

getdate() as opDate, -- Дата проводки

'Тестовая проводка 1' as comment, -- Комментарий к проводке

'Петров' as SK_Контрагент, -- Аналитика для проводки

-- Дебет

'Касса' as Db_account, -- Имя счета дебета проводки

'RUB' as Db_currency, -- Валюта дебета проводки

300.0 as Db_sum, -- Сумма дебета проводки

0 as Db_GoodsAmount, -- Количество товара дебета проводки

-- Кредит

'Поставщики' as Cr_account, -- Имя счета кредита проводки

'RUB' as Cr_currency, -- Валюта кредита проводки

300.0 as Cr_sum, -- Сумма кредита проводки

0 as Cr_GoodsAmount -- Количество товара кредита проводки

Теперь окно запроса Импорт из произвольной БД будет выглядеть так:

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

FROM таблица

WHERE opDate >= ? AND opDate < ?

ORDER BY opDate ASC

  1. Проверим, что мы задали все параметры верно:
  1. щёлкаем мышью кнопку — ФинГрад должен выдать сообщение: «Соединение с БД установлено успешно»;
  2. щёлкаем мышью кнопку — ФинГрад должен выдать сообщение: «Запрос к БД выполнен успешно».

Если при проверке были обнаружены ошибки, надо исправить параметры подключения или текст SQL-запроса, и снова выполнить указанные выше проверки.

  1. После того, как параметры подключения и SQL-запрос введены и проверены, надо щёлкнуть мышью кнопку . Мы вернёмся в список источников импорта. Там появится новая строчка, с помощью которой можно выполнять импорт данных для юридического лица Рога и копыта:

  1. Щёлкнем поле Период в нижнем левом углу окна, и выберем период импорта Сегодня.
  2. Теперь всё готово, и мы можем начать импорт. Для этого щёлкнем мышью кнопку . Если всё было сделано правильно, то ФинГрад запустит процесс импорта и сообщит, что импорт был проведён успешно. Надо будет щёлкнуть кнопку в сообщении об итогах импорта.
  3. Мы увидим список импортированных проводок. Как видно, ФинГрад создал одну проводку с заданными нами параметрами.

Теперь можно щёлкнуть мышью кнопку , и мы вернёмся к списку источников данных.

20.9.2. Импорт проводки, требующей обмена валют

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

  1. В окне со списком источников импорта щёлкнем мышью строку списка для импорта из юридического лица Рога и копыта, и затем щёлкнем кнопку .
  2. Заполним поле для ввода SQL-запроса следующим образом:

SELECT

'Рога и копыта' as LegalEntity, -- Юридическое лицо

getdate() as opDate, -- Дата проводки

'Тестовая проводка 1' as comment, -- Комментарий к проводке

'Петров' as SK_Контрагент, -- Аналитика для проводки

 

-- Дебет

'Касса' as Db_account, -- Имя счета дебета проводки

'USD' as Db_currency, -- Валюта дебета проводки

10.0 as Db_sum, -- Сумма дебета проводки

0 as Db_GoodsAmount, -- Количество товара дебета проводки

 

-- Кредит

'Поставщики' as Cr_account, -- Имя счета кредита проводки

'RUB' as Cr_currency, -- Валюта кредита проводки

300.0 as Cr_sum, -- Сумма кредита проводки

0 as Cr_GoodsAmount -- Количество товара кредита проводки

  1. Проверим, что мы задали все параметры верно (кнопки и ).
  2. После того, как параметры подключения и SQL-запрос введены и проверены, щёлкнем мышью кнопку и вернёмся в список источников импорта.
  3. В поле Период в нижнем левом углу окна уже должен быть установлен период импорта Сегодня. Если это не так, установим период импорта Сегодня.
  4. Щёлкнем мышью кнопку . ФинГрад запустит процесс импорта и сообщит, что импорт был проведён успешно. После того, как мы щёлкнем кнопку в сообщении об итогах импорта, мы увидим результаты импорта.

Как видно, ФинГрад сторнировал проводку, созданную ранее, на этапе 1, и создал по одной исходной проводке две новые проводки ФинГрада. С помощью первой проводки осуществляется обмен валют — 10 долларов США меняется на 300 рублей. Для обменных операций используется счёт ФинГрада Обмен, на котором накапливаются курсовые разницы (потом они отражаются в балансе и отчёте о прибылях и убытках как прибыль или убыток от обменных операций).

Щёлкнем мышью кнопку , и мы вернёмся к списку источников данных.

20.9.3. Импорт проводки с разными значениями аналитики (субконто) по дебету и кредиту

Давайте ещё раз изменим наш запрос так, чтобы ФинГраду не только понадобилось произвести обмен валют, но и создать две проводки в связи с тем, что по дебету и по кредиту исходной проводки используются разные значения одной аналитики (субконто).

  1. В окне со списком источников импорта щёлкнем мышью строку списка для импорта из юридического лица Рога и копыта, и затем щёлкнем кнопку .
  2. Заполним поле для ввода SQL-запроса следующим образом:

SELECT

'Рога и копыта' as LegalEntity, -- Юридическое лицо

getdate() as opDate, -- Дата проводки

'Тестовая проводка 1' as comment, -- Комментарий к проводке

'Петров' as SK_Контрагент, -- Аналитика для проводки

 

-- Дебет

'Основные средства (нач. стоимость)' as Db_account, -- Имя счета дебета проводки

'RUB' as Db_currency, -- Валюта дебета проводки

30000.0 as Db_sum, -- Сумма дебета проводки

0 as Db_GoodsAmount, -- Количество товара дебета проводки

'ЦФО-Администрация' as [Db_SK_Компания (ЦФО)], -- Аналитика для дебета проводки

 

-- Кредит

'Прочие активы' as Cr_account, -- Имя счета кредита проводки

'USD' as Cr_currency, -- Валюта кредита проводки

1000.0 as Cr_sum, -- Сумма кредита проводки

0 as Cr_GoodsAmount, -- Количество товара кредита проводки

'ЦФО-Общее' as [Cr_SK_Компания (ЦФО)] -- Аналитика для кредита проводки

  1. Проверим, что мы задали все параметры верно (кнопки и ).
  2. После того, как параметры подключения и SQL-запрос введены и проверены, щёлкнем мышью кнопку и вернёмся в список источников импорта.
  3. В поле Период в нижнем левом углу окна уже должен быть установлен период импорта Сегодня. Если это не так, установим период импорта Сегодня.
  4. Щёлкнем мышью кнопку . ФинГрад запустит процесс импорта и сообщит, что импорт был проведён успешно. После того, как мы щёлкнем кнопку в сообщении об итогах импорта, мы увидим результаты импорта.

Как видно, ФинГрад сторнировал проводки, созданные ранее при импорте из того же источника данных, и создал по одной исходной проводке три проводки ФинГрада. С помощью первой проводки осуществляется обмен валют. Вторая и третья проводка отражают передачу ресурсов на 30000 рублей со счёта Прочие активы на счёт Основные средства (нач. стоимость). Это делается двумя проводками, так как для дебета и для кредита исходной проводки была указана одна и та же аналитика Компания (ЦФО), и значения этой аналитики в дебете и кредите проводки было разным.

Щёлкнем мышью кнопку , и мы вернёмся к списку источников данных.

20.10. Пример 2. Импорт данных о проводках из Microsoft Excel

В этом примере мы покажем, как можно импортировать данные из проводок, записанных в таблице Microsoft Excel.

20.10.1. Постановка задачи

Пусть имеется файл (книга) Microsoft Excel c данными о хозяйственных операциях фирмы Рога и Копыта. Пусть эти данные записаны на листе Проводки в виде следующей таблицы:

Суммы приведены в рублях. Мы хотим импортировать эти данные в ФинГрад.

20.10.2. Создание листа Microsoft Excel с импортируемыми данными

Создадим в файле (книге) Microsoft Excel лист, на котором будут размещены данные о хозяйственных операциях в форме, пригодной для импорта. Поступаем так.

  1. Создадим в файле (книге) Microsoft Excel новый лист. Назовём его, например, ПроводкиФГ — там мы сформируем данные о проводках в формате, нужном для импорта в ФинГрад.
  2. В первой строке этого листа запишем названия колонок, указанные в п. 20.2.2. Имена колонок ФинГрада, используемые в SQL-запросе. Порядок колонок может быть любым. Колонки Db_accountHint, Cr_accountHint, Db_accountComment и Cr_accountComment не обязательны.

Мы в нашем примере из этих необязательных колонок создадим колонки Db_accountHint и Cr_accountHint, так как мы будем использовать подсказки для создания новых счетов при импорте.

Таким образом, в первой строке листа ПроводкиФГ в нашем примере должны быть следующие заголовки колонок: opDate, LegalEntity, comment, Db_account, Db_accountHint, Db_GoodsAmount, Db_currency, Db_sum, Cr_account, Cr_accountHint, Cr_GoodsAmount, Cr_currency и Cr_sum.

Замечание. Заголовки полей (столбцов) с точками в названии и пробелами в начале или конце названия импортироваться не будут. Если в файле-источнике есть такие заголовки полей, переименуйте их перед импортом.

  1. В нашем примере в импортируемых проводках содержатся следующая аналитическая информация:
  • о названии и номере документа. Эти сведения относятся к проводке в целом;
  • о контрагенте и товаре для дебета и для кредита проводки.

Поэтому в первую строку листа ПроводкиФГ следует записать соответствующие заголовки колонок, например:

Имена колонок ФинГрада

 

Назначение

SK_ Документ, SK_ДокументНомер

 

Соответствуют аналитикам Документ иДокументНомер, в которые мы будем записывать сведения о названии и номере документа для импортируемых проводок.

Db_SK_Товар, Db_SK_Контрагент

 

Соответствуют аналитикам Товар иКонтрагент,в которые мы будем записывать сведения о наименованиях товара и контрагента для дебета проводки.

Cr_SK_Товар, Cr_SK_Контрагент

 

Соответствуют аналитикам Товар и Контрагент, в которые мы будем записывать сведения о наименованиях товара и контрагента для кредита проводки.

  1. На листе могут использоваться и другие колонки, если они нужны в формулах, вычисляющих значения, импортируемые в ФинГрад. В этом случае следует записать в первой строке листа названия этих колонок. Все названия колонок должны быть разными.
  2. В следующих строках этого листа должны содержаться данные о проводках — в каждой строке сведения об одной проводке.
  3. После строки с данными о последней проводке все строки на этом листе Excel должны быть пустыми.

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

Правила для строк со сведениями о проводках. Строки со сведениями о проводках должны составляться по следующим правилам:

  1. В каждой колонке должны содержаться сведения, указанные в её заголовке (ячейке в первой строке этой колонки).
  2. Данные в строках со сведениями о проводках могут быть как постоянными величинами (константами), так и вычисляться по формулам.
  3. В колонке со сведениями о дате проводки (то есть, в колонке с заголовком opDate) должны содержаться численные значения дат, а не строки символов, являющиеся символьным выражением даты.
  4. Строки со сведениями о проводках должны быть упорядочены по возрастанию даты проводки, то есть, по значению в колонке с заголовком opDate. При необходимости следует упорядочить (отсортировать) эти строки.

20.10.3. Перенос сведений о хозяйственных операциях на лист с импортируемыми данными

Строки со сведениями о проводках на листе ПроводкиФГ можно заполнить ссылками на соответствующие ячейки листа с исходными данными.

Например, во второй строке листа ПроводкиФГ:

  • в ячейку в колонке opDate (ячейку A2) помещаем ссылку на ячейку в колонке Дата (ячейку A2) листа с исходными данными: формула =Проводки!A2
  • в ячейку в колонке SK_ДокументНомер (ячейку B2) помещаем ссылку на ячейку в колонке (ячейку B2) листа с исходными данными: формула =Проводки!B2

Аналогично переносим в эту строку листа ПроводкиФГ другие сведения из второй строки листа с исходными данными (листа Проводки). При этом в нашем примере:

  • поскольку мы будем использовать номера счетов бухгалтерского учёта как подсказки для создания счетов ФинГрада при выполнении импорта, то в ячейку в колонке Db_accountHint нам нужно поместить ссылку на ячейку D2 (ячейку в колонке Дебет) листа Проводки: формула =Проводки!D2
  • аналогично, в ячейку в колонке Cr_accountHint нужно поместить ссылку на ячейку E2 (ячейку в колонке Кредит) листа Проводки: формула =Проводки!E2
  • в ячейки в колонках Db_sum и Cr_sum нужно поместить ссылки на одну и ту же ячейку в колонке Сумма (ячейку G2) листа с исходными данными: формула =Проводки!G2
  • в ячейки в колонках Db_GoodsAmount и Cr_GoodsAmount нужно поместить ссылки на одну и ту же ячейку в колонке Кол-во (ячейку F2) листа с исходными данными: формула =Проводки!F2

В колонку LegalEntity записываем значение (константу) Рога и копыта, так как все проводки относятся к фирме «Рога и копыта».

В колонки Db_currency и Cr_currency записываем значения (константы) RUB, так как все суммы в проводках приведены в одной валюте — российских рублях.

Таким образом, первые две строки листа ПроводкиФГ в режиме показа формул будут выглядеть так:

Как заполнить ячейки в колонках Db_account и Cr_account, мы скажем ниже.

20.10.4. Задание наименований счетов ФинГрада

При импорте данных в ФинГрад с помощью SQL-запроса в полях Db_account и Cr_account следует указывать названия счетов ФинГрада. А в нашем примере в исходных проводках для дебета и кредита каждой проводки указаны не названия счетов ФинГрада, а номера счетов бухгалтерского учёта, согласно российскому «Плану счетов бухгалтерского учёта финансово-хозяйственной деятельности организаций».

Конечно, можно использовать в ФинГраде счета с такими же названиями, как номера счетов бухгалтерского учёта. Надо только сделать эти счета субсчетами в нужных группах счетов ФинГрада. Но мы продемонстрируем другой способ, который обычно более удобен — когда в импортируемых проводках будут использоваться имена счетов из существующего в ФинГраде плана счетов управленческого учёта.

Чтобы сопоставить номерам счетов бухгалтерского учёта наименования счетов ФинГрада, создадим ещё один лист Microsoft Excel и присвоим ему имя Счета. На этом листе создадим таблицу, в которой содержатся:

  • коды счетов, используемые в импортируемых проводках (колонка Код);
  • описания этих счетов бухгалтерского учёта (колонка Счет РСБУ);
  • и наименования соответствующих им счетов ФинГрада (колонка Счёт ФинГрада).

В колонке Группа счетов указано, в какой группе счетов ФинГрада надо будет создавать счёт, если счёта с указанным именем в ФинГраде при импорте не окажется. Эта колонка нам приходится в дальнейшем.

Можно заполнить колонку Счет ФинГрада полностью вручную. Но часто удобно задавать имена многих счетов ФинГрада на основании описания и номера соответствующего счёта бухгалтерского учёта. Для этого можно добавить к этой таблице ещё одну колонку, и вычислять значения в ней по формуле:

Здесь мы для заполнения ячеек в колонке DbCr_Account использовали такую формулу:

=ЕСЛИ(СОВПАД(C2;""); B2 & " (счёт РСБУ " & A2 & ")"; C2)

Это формула для ячейки E2, её надо будет скопировать во все остальные ячейки колонка E для строк, в которых задаются сведения о счетах.

Согласно приведённой формуле, значение в колонке E равно значению в той же строке в колонке С (Счёт ФинГрада), а если оно не задано, то значению в колонке B (Счет РСБУ), с добавлением пояснения, содержащего номер счёта бухгалтерского учёта (Код).

Теперь на листе Счета содержится таблица со сведениями о том, в какие счета и группы счетов ФинГрада преобразуются в ходе импорта российские счета бухгалтерского учёта, содержащиеся в исходных проводках. И мы можем на основании этой таблицы заполнить колонки Db_account и Cr_account на листе с данными для импорта в ФинГрад. Сделаем это так:

  1. Выделим на листе Счета диапазон ячеек со сведениями о счетах бухгалтерского учёта и соответствующих им наименованиях счетов ФинГрада. В нашем случае это ячейки в колонках от А до E, от второй строки до последней строки, содержащей сведения о счетах.
  2. Запишем в поле имён Microsoft Excel имя для этого диапазона ячеек. Назовём его, например, ТаблСчетов:

  1. На листе ПроводкиФГ запишем в ячейку в колонке Db_account второй строки (ячейку F2) формулу для вычисления наименования счёта дебета в проводке, которую будет импортировать ФинГрад:

=ЕСЛИОШИБКА(ВПР(E2;ТаблСчетов;5;ЛОЖЬ); E2 & " (счёт РСБУ)")

Эта формула ищет в первой колонке таблицы ТаблСчетов счёт дебета исходной проводки, указанный в ячейке E2 на листе ПроводкиФГ (в колонке Db_accountHint). И если находит его, то возвращает значение из пятой колонки (то есть, колонки DbCrAccount) той же строки таблицы ТаблСчетов. А если не находит, то формула выдаёт в качестве значения счёт дебета исходной проводки с добавлением пояснения: (счёт РСБУ).

  1. Аналогично, в ячейке, находящейся во второй строке и в колонке Cr_account, записываем формулу:

=ЕСЛИОШИБКА(ВПР(J2;ТаблСчетов;5;ЛОЖЬ); J2 & " (счёт РСБУ)")

В этой формуле J2 — это ссылка на значение кредита исходной проводки, указанное в колонке Cr_AccountHint.

20.10.5. Заполнение строк со сведениями о проводках

Мы заполнили только одну строку в таблице со сведениями о проводках на листе Проводки. Чтобы заполнить остальные строки:

  1. Посмотрим на листе с исходными данными (листе Проводки), сколько там имеется строк с проводками.
  2. Перейдём на лист ПроводкиФГ и выделим там вторую строку, щёлкнув мышью номер этой строки (цифру 2).
  3. Подведём указатель мыши к толстому квадратику в левом нижнем углу выделенного диапазона ячеек. Указатель мыши пример форму плюсика: .

  1. Нажмём левую кнопку мыши и, не отпуская её, переместим указатель мыши вниз, чтобы выделить столько же строк, сколько у нас имеется исходных проводок. И затем отпустим кнопку мыши. Microsoft Excel заполнит все выделенные строки по образцу второй строки.

Замечание. Если Вы по ошибке заполнили лишние строки, надо удалить их содержимое.

20.10.6. Заполнение справочника аналитики «Добавление счетов из импорта»

Если импорт в ФинГрад с помощью SQL-запроса — это одноразовое мероприятие, то все необходимые счета ФинГрада можно добавить вручную. Но если импорт с помощью SQL-запроса планируется повторять много раз, то целесообразно предусмотреть правила создания счетов, чтобы ФинГрад при появлении в импортируемых данных названий счетов, отсутствующих в его плане счетов, мог самостоятельно создавать эти счета и помещать их в нужные группы счетов. Покажем, как это делается.

В нашем примере в каждой импортируемой проводке указываются «подсказки» Db_accountHint или Cr_accountHint, значения которых — это номера счетов бухгалтерского учёта дебета и кредита в исходных проводках. ФинГрад будет создавать новые счета при импорте на основании этих подсказок.

Посмотрим созданную нами таблицу со счетами на листе Счета.

По таблице видно, что:

  • если номер счёта бухгалтерского учёта в исходных проводках принадлежит группе 01 или группе 02, то соответствующий счёт ФинГрада надо создавать в группе счетов Основные средства;
  • если номер счёта бухгалтерского учёта в исходных проводках принадлежит группе 03, то соответствующий счёт ФинГрада надо создавать в группе счетов Товары на складах;
  • и так далее.

Соответственно заполняем справочник аналитики Добавление счетов из импорта:

Добавление счетов из импорта

Группа счетов

01*

Основные средства

02*

Основные средства

03*

Товары на складах

04*

Прочие операционные расходы

07*

Товары на складах

08*

Прочие активы

10*

Товарные запасы

И так далее.

Замечание. При редактировании справочника аналитики Добавление счетов из импорта Вы сможете выбирать имеющиеся счета из списка счетов, но не сможете добавлять в этот список новые счета. Поэтому перед редактированием справочника аналитики может понадобиться добавить в список счетов ФинГрада отсутствующие в нём счета (которые будут использоваться как группы счетов).

20.10.7. Проверка корректности задания счетов и групп счетов в настройках импорта

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

  1. Названия счетов ФинГрада, указанные в полях Db_account и Cr_account в импортируемых проводках, не совпадают:
  1. с названиями имеющихся групп счетов ФинГрада;
  2. с наименованиями групп счетов, указанных в колонке Группа счетов справочника аналитики Добавление счетов из импорта.
  1. В колонке Группа счетов справочника аналитики Добавление счетов из импорта не указаны счета ФинГрада, по которым имеются проводки. Поскольку ФинГрад не разрешает создавать субсчета к таким счетам.

20.10.8. Проверка наличия аналитик

Также следует проверить, что в ФинГраде существуют все аналитики, использованные в настройках импорта. При импорте из Excel — это имена аналитик, указанные в заголовках колонок листа Microsoft Excel с импортируемыми данными. Иными словами, если в первой строке листа Microsoft Excel с импортируемыми данными имеется заголовок вида SK_имя_аналитики, или Db_SK_имя_аналитики, или Cr_SK_имя_аналитики, то при выполнении импорта аналитика с указанным именем должна существовать. Если каких-то из этих аналитик в ФинГраде не существует, их надо создать.

В нашем примере мы использовали названия аналитик Товар, Контрагент, Документ и ДокументНомер. Наличие этих аналитик и надо проверить, и недостающие аналитики создать. Аналитики Товар и Контрагент, скорее всего, будут в наличии, так как они предусмотрены в комплекте поставки ФинГрада.

20.10.9. Сохранение и закрытие файла Excel

Перед тем, как начать импорт данных из файла Microsoft Excel, надо сохранить файл (книгу) Microsoft Excel, и затем:

  • либо закрыть этот файл (команда меню Файл | Закрыть);
  • либо выйти из программы Microsoft Excel (команда меню Файл | Выход).

20.10.10. Создание источника данных для импорта

Создадим источник данных для импорта из файла Excel. Это делается примерно так же, как в предыдущем примере (см. п. 20.9.1. Создаём источник импорта и вводим одну обычную проводку).

  1. Щёлкнем мышью надпись Импорт из баз данных (1С, SQL и др.) на Главном экране ФинГрада, или выберем в меню ФинГрада пункт Работа | Импорт проводок бухгалтерии (1С, АБС и т. д.).
  2. В выведенном на экран списке источников импорта щёлкнем мышью кнопку справа от надписи Импорт проводок бухгалтерии, и выберем в выведенном меню пункт Из произвольной базы данных. На экран будет выведен диалоговый запрос Импорт из произвольной БД.
  3. В поле справа от надписи Параметры БД введём строку подключения.
  1. если наш файл Microsoft Excel имеет расширение имени .XLSX, то строка подключения будет такой:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=полное-имя-файла;Extended Properties="Excel 12.0 Xml;HDR=YES;"

Например, если файл называется FG.XLSX и находится в папке D:\TEST, то строка подключения будет такова:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TEST\FG.XLSX;Extended Properties="Excel 12.0 Xml;HDR=YES;"

  1. если же файл Microsoft Excel имеет расширение имени .XLS (то есть, это файл в формате Microsoft Excel 97–2003), то строка подключения будет такой:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=полное-имя-файла;Extended Properties="Excel 8.0;HDR=YES;"

Например, если файл называется FG.XLS и находится в папке D:\TEST, то строка подключения будет такова:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TEST\FG.XLS;Extended Properties="Excel 8.0;HDR=YES;"

  1. Поля Логин и Пароль оставим пустыми.
  2. Щёлкнем гиперссылку Выбрать справа от надписи Юр. Лицо, и в выведенном на экран справочнике аналитики Юр. Лицо щёлкнем строку Рога и копыта, которую мы создали в предыдущем примере (см. п. 20.9.1. Создаём источник импорта и вводим одну обычную проводку). И затем щёлкнем кнопку .
  3. В поле Имя источника будет предложено название источника импорта Рога и копыта. Поменяем его, например, на Рога и копыта — импорт из Excel
  4. Ниже, под надписью Запрос к БД, вместо записанного образца надо ввести следующий запрос для импорта из файла Microsoft Excel:

SELECT

opDate, LegalEntity, comment,

[SK_ДокументНомер], [SK_Документ],

Db_account, Db_accountHint, Db_GoodsAmount, Db_currency, Db_sum,

[Db_SK_Товар], [Db_SK_Контрагент],

Cr_account, Cr_accountHint, Cr_GoodsAmount, Cr_currency, Cr_sum,

[Cr_SK_Товар], [Cr_SK_Контрагент]

FROM [ПроводкиФГ$]

WHERE (opDate >= ?) AND (opDate < ?)

Здесь выражение FROM [ПроводкиФГ$] означает, что мы будем импортировать данные с листа ПроводкиФГ файла Microsoft Excel.

Выражение ORDERBYopDateASС в запросе отсутствует, так как используемый для импорта из Microsoft Excel драйвер его не воспринимает. Поэтому таблица с данными о проводках, из которой будет производиться импорт, должна быть упорядочена по дате и времени проводки (то есть, по колонке opDate).

  1. Запрос Импорт из произвольной БД теперь имеет такой вид:

  1. Проверим, что мы задали все параметры верно (кнопки и ).
  2. После того, как параметры подключения и SQL-запрос введены и проверены, щёлкнем мышью кнопку и вернёмся в список источников импорта.

Замечания. 1. Импорт из Excel можно также выполнять из заданного диапазона ячеек. Для этого надо присвоить диапазону ячеек имя и указать это имя в SQL-запросе после FROM. Например, если присвоить диапазону ячеек с исходными данными имя ТаблицаПроводок, то в SQL-запросе надо будет написать выражение FROM ТаблицаПроводок или FROM [ТаблицаПроводок]. В первой строке этого диапазона ячеек должны содержаться заголовки столбцов для импортируемых данных.

2. Указанный способ импорта из файлов Microsoft Excel использует драйвер доступа к данным, входящий в состав Microsoft Access. Если на вашем компьютере СУБД Microsoft Access не установлена, следует скачать с сайта фирмы Microsoft и установить соответствующую компоненту для доступа к данным.

Ссылки для скачивания:

20.10.11. Запуск импорта

Чтобы запустить импорт из файла Microsoft Excel с помощью созданного нами источника данных, следует:

  1. Щёлкнуть мышью строку с этим источником данных в окне со списком источников импорта.
  2. Задать период импорта в нижнем левом углу этого окна:

  1. Теперь всё готово, и мы можем начать импорт, щёлкнув мышью кнопку .

20.10.12. Просмотр результатов и исправление ошибок

Если всё было сделано правильно, то ФинГрад запустит процесс импорта и сообщит о результатах импорта — сколько при импорте было ошибок и предупреждений.

После щелчка мышью по кнопке в сообщении об итогах импорта, мы увидим список импортированных проводок.

Сообщения об ошибках. Если при импорте были сформированы сообщения об ошибках и/или предупреждения, то они выводятся в панели Ошибки и предупреждения, например:

Вы можете:

  • просматривать результаты импорта и список ошибок и предупреждений;
  • исправлять ошибки в исходном файле Microsoft Excel;
  • исправлять в ФинГраде настройки импорта:
  • в SQL-запросе для импорта из файла Microsoft Excel;
  • в справочнике аналитики Добавление счетов из импорта;
  • на вкладке в окне просмотра результатов и настроек импорта.
  • настраивать план счетов ФинГрада (команда меню Основная информация | Счета) и настройки аналитик (команда меню Основная информация | Аналитики);
  • заново запускать импорт щелчком по кнопке .

Выход из окна просмотра и настроек импорта. По окончании импорта данных из файла Microsoft Excel, следует щёлкнуть мышью кнопку .

20.11. Пример 3. Импорт данных из нескольких таблиц

В предыдущем примере все преобразования исходных данных к тому виду, который может воспринять ФинГрад, были выполнены средствами Microsoft Excel. После чего эти преобразованные данные были «закачаны» в ФинГрад простейшим SQL-запросом.

Но в большинстве случаев удобнее использовать другой способ импорта, который не требует трудоёмкой ручной «доработки» исходных данных. Если данные о хозяйственных операциях, которые надо импортировать в ФинГрад, представляют собой одну или несколько прямоугольных таблиц с данными, составленных так, как это требуется для реляционных баз данных, то все преобразования исходных данных к виду, который может воспринять ФинГрад, можно запрограммировать непосредственно в SQL-запросе. Покажем, как это делается.

20.11.1. Постановка задачи

Возьмём в качестве исходных данных те же данные, что в предыдущем параграфе. И покажем, как тот же результат может быть достигнут без ручной «доработки» исходных данных, а средствами языка SQL, то есть, в самом SQL-запросе.

Пусть у нас имеется таблица c данными о хозяйственных операциях (бухгалтерских проводках) фирмы Рога и Копыта:

Суммы в таблице приведены в рублях. В колонках Дебет и Кредит этой таблицы указаны номера счетов бухгалтерского учёта, согласно российскому «Плану счетов бухгалтерского учёта финансово-хозяйственной деятельности организаций». В колонках Д_Контрагент и Д_Товар указаны наименования контрагента и товара для дебета проводки, а в колонках К_Контрагент и К_Товар — наименования контрагента и товара для кредита проводки.

Для задания отображения номеров счетов бухгалтерского учёта в наименования счетов ФинГрада используется ещё одна таблица данных следующего вида

Пусть мы хотим, чтобы:

  • если значение в колонке Счет ФинГрада не задано, то оно считалось бы равным значению в колонке Счет РСБУ с добавлением пояснения, содержащего номер счёта бухгалтерского учёта (значения в колонке Код).

Например, счёту 03.09 Выбытие материальных ценностей будет сопоставлен счёт ФинГрада Выбытие материальных ценностей (счёт РСБУ 03.09)

  • если для счёта исходной проводки не найдено соответствия в указанной выше таблице счетов, имя счёта ФинГрада для него — это имя счёта исходной проводки с добавлением пояснения: (счёт РСБУ).

Теперь мы покажем, как решать эту задачу, если исходные данные содержатся:

  1. В базе данных Microsoft SQL/PostgresPro Server. Пусть:
  • таблица с исходными проводками и таблица со сведениями о счетах содержатся в базе данных с именем FinGradSQLImport;
  • таблица с проводками называется [dbo].[Проводки], а таблица со сведениями о счетах называется [dbo].[Счета].
  1. В табличном файле Microsoft Excel — на листах Проводки и Счета файла Microsoft Excel D:\TEST\FG.xlsx.
  2. В базе данных Microsoft Access — в таблицах Проводки и Счета файла Microsoft Access D:\TEST\FG.accdb.

Мы будем считать, что справочник аналитики Добавление счетов из импорта заполнен, как это описывалось в п. 20.10.6. Заполнение справочника аналитики «Добавление счетов из импорта».

20.11.2. Создание источника импорта

Для всех трёх случаев (импорта из базы данных Microsoft SQL/PostgresPro Server, из файла Microsoft Excel, из базы данных Microsoft Access) источник импорта создаётся аналогично.

  1. Щёлкнем мышью надпись Импорт из баз данных (1С, SQL и др.) на Главном экране ФинГрада, или выберем в меню ФинГрада пункт Работа | Импорт проводок бухгалтерии (1С, АБС и т. д.).
  2. В выведенном на экран списке источников импорта щёлкнем мышью кнопку справа от надписи Импорт проводок бухгалтерии. В появившемся меню выберем пункт Из произвольной базы данных. На экран будет выведен диалоговый запрос Импорт из произвольной БД.
  3. Щёлкнем кнопку справа от надписи Параметры БД и выведем окно Свойства канала передачи данных. В этом окне:
  1. на вкладке надо выбрать метод подключения к базе данных SQL Server Native Client.
  2. на вкладке в поле Введите или выберите имя сервера(Selectorenteraservername) надо ввести имя сервера базы данных — например, (local)\SQLEXPRESS, если сервер базы данных Microsoft SQL Express располагается на том же компьютере.
  3. в поле Введите информацию для подключения к серверу (Enter information to log on to the server) надо указать метод подключения к серверу — чаще всего это встроенные средства безопасности Windows (Use Windows NT integrated security).
  4. в поле Выберите базу данных (Select the database) надо выбрать нужную нам базу данных FinGradSQLImport. Для этого можно щёлкнуть треугольничек в правой части этого поля и выбрать имя базы данных из списка.
  5. щёлкните мышью кнопку , и Вы вернётесь в окно Импорт из произвольной БД.
  1. В поля Логин и Пароль введём имя пользователя (учётной записи) и пароль для подключения к серверу базы данных.
  2. Щёлкнем гиперссылку Выбрать справа от надписи Юр. Лицо, в выведенном на экран справочнике аналитики Юр. Лицо щёлкнем строку Рога и копыта, и потом кнопку .
  3. В поле Имя источника будет предложено название источника импорта Рога и копыта. Изменим его на:
  • Рога и копыта — пример 3SQLServer, для импорта из SQL Server;
  • или на Рога и копыта — пример 3 Excel, для импорта из файла Excel;
  • или на Рога и копыта — пример 3 Access, для импорта из базы данных Access.
  1. Ниже, под надписью Запрос к БД, надо вместо записанного образца ввести нужный нам SQL-запрос (какой именно — будет показано ниже).
  2. Проверим, что мы задали все параметры верно (кнопки и ).
  3. После того, как параметры подключения и SQL-запрос введены и проверены, щёлкнем мышью кнопку и вернёмся в список источников импорта. С помощью созданной нами строки в списке источников импорта можно будет импортировать наши данные.

20.11.3. Запрос для импорта из базы данных Microsoft SQL Server

Составляем раздел FROM оператора SELECT. Для получения данных из базы данных в SQL используется оператор SELECT. В этом оператора после ключевого слова FROM указывается, откуда нужно получить данные.

В нашем примере нам нужно получить данные из таблицы с проводками — таблицы [dbo].[Проводки]. И для каждой строки этой таблицы нам нужно два раза обратиться к таблице счетов [dbo].[Счета], чтобы извлечь оттуда данные из строк, соответствующих счетам дебета и кредита проводки. Это может быть выполнено следующей конструкцией языка SQL:

FROM [FinGradSQLImport].[dbo].[Проводки] D

    LEFT JOIN [FinGradSQLImport].[dbo].[Счета] ADB ON D.Дебет = ADB.Код

    LEFT JOIN [FinGradSQLImport].[dbo].[Счета] ACR ON D.Кредит = ACR.Код

Здесь:

  • D — это заданный нами алиас (синоним) для таблицы проводок [dbo].[Проводки], который мы будем указывать для обозначения того, что поля относятся к этой таблице;
  • ADB и ACR — это два алиаса (синонима) для таблицы проводок [dbo].[Счета]:
  • алиас ADB мы будем использовать для доступа к строке таблицы счетов, соответствующей счёту дебета проводки (то есть, к строке, для которой выполняется условие D.Дебет = ADB.Код);
  • алиас ACR мы будем использовать для доступа к строке таблицы счетов, соответствующей счёту кредита проводки (то есть, к строке, для которой выполняется условие D.Кредит = ACR.Код).

Задаём значения колонок ФинГрада. Теперь мы можем, на основе данных из таблиц D, ADB и ACR, задать значения колонок ФинГрада, указанных в п. 20.2.2. Имена колонок ФинГрада, используемые в SQL-запросе. Это делается с помощью выражений:

ВыражениеASимя-колонки-ФинГрада

Для большинства колонок ФинГрада нужные выражения — это значения полей таблицы проводок, то есть полей таблицы D. Поэтому мы можем написать так:

D.[Дата] as opDate,

D.[№] as [SK_ДокументНомер],

D.[Документ] as [SK_Документ],

D.[Дебет] as Db_accountHint,

D.[Кредит] as Cr_accountHint,

D.[Кол-во] as Db_GoodsAmount,

D.[Кол-во] as Cr_GoodsAmount,

D.[Сумма] as Db_sum,

D.[Сумма] as Cr_sum,

D.[Комментарий] as comment,

D.[Д_Контрагент] as [Db_SK_Контрагент],

D.[Д_Товар] as [Db_SK_Товар],

D.[К_Контрагент] as [Cr_SK_Контрагент],

D.[К_Товар] as [Cr_SK_Товар],

Значения некоторых колонок ФинГрада в нашем примере являются постоянными величинами (константами). Поэтому мы пишем так:

'Рога и копыта' as LegalEntity,

'RUB' as Db_currency,

'RUB' as Cr_currency

Задаём значения счетов дебета и кредита. Нам осталось задать только значения колонок ФинГрада Db_account и Cr_account. Если бы в колонке Счет ФинГрада таблицы счетов всегда указывался счёт ФинГрада, и для всех исходных проводок всегда находились бы строки таблицы счетов, соответствующие счетам дебета и кредита проводки, то мы могли бы написать в SQL-запросе так:

ADB.[Счет ФинГрада] AS Db_account,

ACR.[Счет ФинГрада] AS Cr_account,

Однако эти условия не выполняются. И нам приходится обрабатывать случаи, когда значение в колонке Счет ФинГрада не указано, и когда нужная строка в таблице счетов не найдена. Это можно сделать так:

case

    when ISNULL(ADB.[Счет ФинГрада], '') <> '' then ADB.[Счет ФинГрада]

    when ISNULL (ADB.[Счет РСБУ], '') <> '' then ADB.[Счет РСБУ] + ' (счёт РСБУ ' + D.[Дебет] + ')'

    else D.[Дебет] + ' (счёта нет в плане счетов РСБУ)' end

    as Db_account,

case

    when ISNULL(ACR.[Счет ФинГрада], '') <> '' then ACR.[Счет ФинГрада]

    when ISNULL (ACR.[Счет РСБУ], '') <> '' then ACR.[Счет РСБУ] + ' (счёт РСБУ ' + D.[Кредит] + ')'

    else D.[Кредит] + ' (счёта нет в плане счетов РСБУ)' end

    as Cr_account,

Здесь функция ISNULL используется для того, чтобы заместить отсутствующие значения пустыми строками (то есть, строками нулевой длины).

Итоговый SQL-запрос. Теперь нам осталось написать в начале запроса ключевое слово SELECT, а в конец — условие отбора проводок по дате. И мы получаем нужный нам текст SQL-запроса:

SELECT

    D.[Дата] as opDate,

    D.[№] as [SK_ДокументНомер],

    D.[Документ] as [SK_Документ],

    case

        when ISNULL(ADB.[Счет ФинГрада], '') <> '' then ADB.[Счет ФинГрада]

        when ISNULL (ADB.[Счет РСБУ], '') <> '' then ADB.[Счет РСБУ] + ' (счёт РСБУ ' + D.[Дебет] + ')'

        else D.[Дебет] + ' (счёта нет в плане счетов РСБУ)' end

       as Db_account,

  D.[Дебет] as Db_accountHint,

    case

       when ISNULL (ACR.[Счет ФинГрада], '') <> '' then ACR.[Счет ФинГрада]

       when ISNULL (ACR.[Счет РСБУ], '') <> '' then ACR.[Счет РСБУ] + ' (счёт РСБУ ' + D.[Кредит] + ')'

        else D.[Кредит] + ' (счёта нет в плане счетов РСБУ)' end

        as Cr_account,

  D.[Кредит] as Cr_accountHint,

  D.[Кол-во] as Db_GoodsAmount,

  D.[Кол-во] as Cr_GoodsAmount,

  D.[Сумма] as Db_sum,

  D.[Сумма] as Cr_sum,

  D.[Комментарий] as comment,

    D.[Д_Контрагент] as [Db_SK_Контрагент],

    D.[Д_Товар] as [Db_SK_Товар],

    D.[К_Контрагент] as [Cr_SK_Контрагент],

    D.[К_Товар] as [Cr_SK_Товар],

    'Рога и копыта' as LegalEntity,

    'RUB' as Db_currency,

    'RUB' as Cr_currency

FROM [FinGradSQLImport].[dbo].[Проводки] D

    LEFT JOIN [FinGradSQLImport].[dbo].[Счета] ADB ON D.Дебет = ADB.Код

    LEFT JOIN [FinGradSQLImport].[dbo].[Счета] ACR ON D.Кредит = ACR.Код

WHERE (D.[Дата] >= ?) AND (D.[Дата] < ?)

20.11.4. Запрос для импорта из файла Microsoft Excel

Пусть таблицы со сведениями о проводках и счетах записаны на листах Проводки и Счета файла Microsoft Excel D:\TEST\FG.xlsx. Заголовки столбцов этих таблиц должны содержаться в первой строке соответствующего листа.

SQL-запрос для импорта из файла Microsoft Excel будет отличаться от приведённого выше, в п. 20.11.3. Запрос для импорта из базы данных Microsoft SQL Server, только разделом FROM. В п. 20.11.3. Запрос для импорта из базы данных Microsoft SQL Server мы получали исходные данные из таблиц базы данных Microsoft SQL Server, а сейчас, вместо этого, мы прикажем Microsoft SQL Server импортировать нужные нам данные из файла Microsoft Excel.

Для этого, в разделе FROM SQL-запроса вместо имён таблиц Microsoft SQL Server мы будем использовать вызовы функции OPENROWSET:

OPENROWSET('Microsoft.ACE.OLEDB.12.0',

     'Excel 12.0 XML;Database=полное-имя-файла-Excel;IMEX=1;HDR=Yes;',

     'SELECT * FROM [имя-листа$]')

В нашем случае полное имя файла Excel (включающее имя диска и папки) — это D:\TEST\FG.xlsx, а данные нам надо импортировать из листов Проводки и Счета. Поэтому раздел FROM нашего SQL-запроса будет выглядеть так:

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

   'Excel 12.0 XML;Database=D:\TEST\FG.xlsx;IMEX=1;HDR=Yes;',

   'SELECT * FROM [Проводки$]') D

LEFT JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0',

   'Excel 12.0 XML;Database=D:\TEST\FG.xlsx;IMEX=1;HDR=Yes;',

   'SELECT * FROM [Счета$]') ADB ON D.Дебет = ADB.Код

LEFT JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0',

   'Excel 12.0 XML;Database=D:\TEST\FG.xlsx;IMEX=1;HDR=Yes;',

   'SELECT * FROM [Счета$]') ACR ON D.Кредит = ACR.Код

Замечания. 1. Если файл Microsoft Excel имеет расширение имени .XLS (то есть, это файл в формате Microsoft Excel 97–2003), то вместо Excel 12.0 XML надо написать Excel 8.0 .

2. Если на листах с импортируемыми данными есть ещё какие-то данные, которые могут помешать импорту, то можно выделить импортируемый диапазон ячеек (в первой строке этого диапазона ячеек должны содержаться заголовки столбцов импортируемых данных), присвоить этому диапазону ячеек имя (как мы это делали в п. 20.10.4. Задание наименований счетов ФинГрада), и вместо выражения 'SELECT * FROM [имя-листа$]' использовать 'SELECT * FROM [имя-диапазона-ячеек]' (заметьте, что символ $ добавлять не надо). Например, если присвоить диапазону ячеек с исходными проводками имя ТаблицаПроводок, то в SQL-запросе надо будет написать выражение 'SELECT * FROM [ТаблицаПроводок]' .

3. Указанный способ импорта из файлов Microsoft Excel использует драйвер доступа к данным, входящий в состав Microsoft Access. Если на вашем компьютере СУБД Microsoft Access не установлена, следует скачать с сайта фирмы Microsoft и установить соответствующую компоненту для доступа к данным. Ссылки для скачивания:

4. Указание IMEX=1 означает, что столбцы исходных данных, в которых встречаются значения разных типов (например, как числовые, так и текстовые), будут импортироваться как текстовые значения. Как правило, это позволяет импортировать данные наиболее корректно.

20.11.5. Запрос для импорта из базы данных Microsoft Access

Пусть таблицы со сведениями о проводках и счетах записаны в таблицах Проводки и Счета базы данных Microsoft Access D:\TEST\FG.accdb. Импорт этих данных выполняется аналогично, с помощью вызовов функции OPENROWSET. SQL-запрос для импорта будет отличаться от приведённого выше, в п. 20.11.3. Запрос для импорта из базы данных Microsoft SQL Server, только разделом FROM. Раздел FROM SQL-запроса для импорта из базы данных Microsoft Access будет выглядеть так:

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

   'D:\TEST\FG.accdb';'Admin';,

   'SELECT * FROM [Проводки]') D

LEFT JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0',

   'D:\TEST\FG.accdb';'Admin';,

   'SELECT * FROM [Счета]') ADB ON D.Дебет = ADB.Код

LEFT JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0',

   'D:\TEST\FG.accdb';'Admin';,

   'SELECT * FROM [Счета]') ACR ON D.Кредит = ACR.Код

Замечание. В приведённом выше запросе мы указывали имя пользователя базы данных Microsoft Access Admin, и пустой пароль. При необходимости можно указать иные имя пользователя и пароль для доступа к базе данных Microsoft Access. Например, выражение при вызове функции OPENROWSET

   'D:\TEST\FG.accdb';'Финдиректор'; 'Сидоров',

Указывает, что при подключении к базе данных D:\TEST\FG.accdb будут использоваться имя пользователя Финдиректор и пароль Сидоров.

20.12. Примеры SQL-запросов, использованных в реальных задачах

В этом разделе приведены примеры SQL-запросов, использованных в реальных задачах, то есть, для импорта в ФинГрад баз данных со сведениями о хозяйственных операциях организаций — пользователей ФинГрада. По этим примерам Вы можете увидеть те средства языка SQL, которые могут использоваться для преобразования исходных данных о хозяйственных операциях в формат, требуемый для импорта в ФинГрад.

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

20.12.1. Импорт из Акцент-бухгалтерии (база данных на основе Microsoft Access)

SELECT

IIF(ISNULL(fAcDt.AccFg), dt.ACC_NAME , fAcDt.AccFg) asDb_account,

IIF(ISNULL(fAcKt.AccFg), ct.ACC_NAME , fAcKt.AccFg) as Cr_account,

d.opDate as opDate,

j.J_MEMO as comment,

'UAH' as Db_currency,

'UAH' as Cr_Currency,

j.J_SUM as Db_sum,

j.J_SUM as Cr_sum,

Switch(c.CUR='UAH' or J_SUM=0,'',true,c.CUR) as [SK_Валюта 1С],

Switch(c.CUR='UAH' or J_SUM=0,'',true,J_sum_crc) as [SK_Валютнаясумма 1С],

j.J_QUANT as Db_goodsAmount,

o.obj_name + ', ' +

IIF(ISNULL(u.UN_LONGNAME), IIF(ISNULL(u.UN_NAME),'Назадано',u.UN_NAME),u.UN_LONGNAME) as SK_Товар,

IIF(ISNULL(fAcDt.AgentAnalitic),

   IIF(ISNULL(a1.AG_NAME) AND ISNULL(a2.AG_NAME), 'Незадано', a1.AG_NAME),

   fAcDt.AgentAnalitic) as Db_SK_Контрагент,

IIF(ISNULL(fAcKt.AgentAnalitic), a2.AG_NAME, fAcKt.AgentAnalitic) as Cr_SK_Контрагент,

d.DOC_NAME as SK_Документ,

'Фирма2' as LegalEntity,

fAcDt.AccAnalitic as [Db_SK_Банковскиесчета],

fAcKt.AccAnalitic as [Cr_SK_Банковскиесчета],

'Не задано' as [SK_Основные средства],

'Не задано' as [SK_Договоры],

'Не задано' as [SK_Статьи затрат],

'Не задано' as [SK_Статьи движения денежных средств]

FROM ((((((((((journal j

left join (select * from agents where ag_self = FALSE) a1 on a1.AG_ID = j.J_AG1)

left join (select * from agents where ag_self = FALSE) a2 on a2.AG_ID = j.J_AG2)

inner join (select DOC_ID, DOC_NAME, Switch(DOC_DATE<=DateValue('2009-12-31'),DateValue('2009-12-31'),true,DOC_DATE) as opDate from DOCUMENTS where DOC_DONE = 2) d on d.DOC_ID = j.DOC_ID)

left join UNITS u on u.UN_ID = j.UN_ID)

inner join ACCOUNTS dt on dt.ACC_ID = j.ACC_DB)

inner join ACCOUNTS ct on ct.ACC_ID = j.ACC_CR)

left join fgAccounts fAcDt on dt.ACC_NAME = fAcDt.AccName)

left join fgAccounts fAcKt on ct.ACC_NAME = fAcKt.AccName)

left join objects o on o.OBJ_ID = j.J_OBJ)

left join (select CRC_ID, Switch(CRC_SHORT='грн','UAH',CRC_SHORT='руб. РФ','RUB',true,CRC_SHORT) as CUR from [CURRENCY]) c on c.CRC_ID = j.CRC_ID)

WHERE (dt.ACC_NAME is not null and ct.ACC_NAME is not null AND fAcDt.DtIgnor = 0 AND fAcKt.KtIgnor = 0)

and opDate >=? and opDate<?

20.12.2. Импорт из учётной системы ИС-ПРО (база данных на основе MS SQL Server)

SELECT

'Фирма1' as LegalEntity,

opdate,

Prw_Note as comment,

CASE WHEN ACCDT.AccFg IS NOT NULL THEN ACCDT.AccFg ELSE ACCDT.SprPls_Nm END as Db_account,

CASE WHEN ACCCT.AccFg IS NOT NULL THEN ACCCT.AccFg ELSE ACCCT.SprPls_Nm END as Cr_account,

'UAH' AS Db_currency, 'UAH' AS Cr_currency,

Prw_VSm AS Db_sum, Prw_VSm AS Cr_sum,

DOC.Srd_OprNm AS SK_Документ,

CASE WHEN (DtAgents.Ptn_NmSh IS NULL OR DtAgents.Ptn_NmSh = '') AND (CtAgents.Ptn_NmSh IS NULL OR CtAgents.Ptn_NmSh = '') THEN 'Не задано' ELSE DtAgents.Ptn_NmSh END AS [Db_SK_Контрагент],

CtAgents.Ptn_NmSh AS [Cr_SK_Контрагент],

ACCDT.AccAnalitic AS [Db_SK_Банковские счета],

ACCCT.AccAnalitic AS [Cr_SK_Банковские счета],

'Не задано' AS [SK_Статьи затрат],

'Не задано' AS [SK_Договоры],

'Не задано' AS [SK_Товар],

CASE WHEN ACCDT.AccFg = 'Основное производство' THEN ACCDT.AccName ELSE

CASE WHEN ACCCT.AccFg = 'Основное производство' THEN ACCCT.AccName ELSE '' END END as [SK_Счет-укр]

, CASE WHEN ACCDT.AccFg = 'Кредиты и займы полученные' THEN ACCDT.SprPls_Sch END AS [SK_Счет-номер-укр-дб]

, CASE WHEN ACCCT.AccFg = 'Кредиты и займы полученные' THEN ACCCT.SprPls_Sch END AS [SK_Счет-номер-укр-кт]

FROM (select Srd_dat as opdate, * from SRDPRW) T

INNER JOIN (SELECT * FROM SprPls LEFT OUTER JOIN tempFingrad.dbo.fgAccounts ON SprPls_Nm = AccName WHERE DtIgnor <> 1 OR DtIgnor IS NULL) AS ACCDT on T.Prw_Dt = ACCDT.SprPls_Rcd

INNER JOIN (SELECT * FROM SprPls LEFT OUTER JOIN tempFingrad.dbo.fgAccounts ON SprPls_Nm = AccName WHERE KtIgnor <> 1 OR KtIgnor IS NULL) AS ACCCT ON T.Prw_Kt = ACCCT.SprPls_Rcd

INNER JOIN SVl AS CURRDT ON T.Prw_ValDt = CURRDT.SVl_Rcd

INNER JOIN SVl AS CURRCT ON T.Prw_ValKt = CURRCT.SVl_Rcd

INNER JOIN Srd AS DOC ON T.Srd_DocRcd = DOC.Srd_DocRcd AND T.Srd_Rcd = Doc.Srd_Rcd AND T.Srd_CdDoc = doc.Srd_CdDoc

LEFT OUTER JOIN PTNRK AS CtAgents ON DOC.Srd_RcdSnd = CtAgents.Ptn_Rcd

LEFT OUTER JOIN PTNRK AS DtAgents ON DOC.Srd_RcdRec = DtAgents.Ptn_Rcd

WHERE Prw_Dt<>0 AND Prw_Kt<>0 AND ACCDT.SprPls_Nm <> ACCCT.SprPls_Nm

AND T.srd_stt = 0 AND opDate >=? AND opDate<?

20.12.3. Импорт из автоматизированной банковской системы «Инверсия»

SELECT distinct 'Москва' AS LegalEntity,

trn.DTRNTRAN as opDate,

trn.CTRNACCD as Db_Account,

trn.CTRNACCC as Cr_Account,

case when round(itrnba2d/10)= 9999 then trn.CTRNCURC else trn.CTRNCUR end as Db_currency,

case when round(itrnba2c/10)= 9999 then trn.CTRNCUR else trn.CTRNCURC end as Cr_currency,

case when round(itrnba2d/10)= 9999 then trn.MTRNSUMC else trn.MTRNSUM end as Db_sum,

case when round(itrnba2c/10)= 9999 then trn.MTRNSUM else trn.MTRNSUMC end as Cr_sum,

trn.ctrnpurp||' {'||to_char(trn.itrnnum)||'_'||to_char(trn.itrnanum)||'}' as "comment",

'00_'||trim(to_char(xxi.scb_accotd(xxi.trn.itrnnum, xxi.trn.itrnanum,0) ,'999900')) as "Db_SK_Офис2",

'00_'||trim(to_char(xxi.scb_accotd(xxi.trn.itrnnum, xxi.trn.itrnanum,1) ,'999900')) as "Cr_SK_Офис2",

cde.ncdeagrid as "SK_Кредит",

obg.cobgname as "SK_Признак бюджета",

case when substr(trn.CTRNACCD,1,1) = '9' then '02 Банк забаланс' else '01 Банк баланс' end as "SK_Источник2"

FROM xxi.trn left join xxi.CDE

on trn.itrnnum=cde.icdetrnnum and trn.itrnanum=cde.icdetrnanum

left join gtr on trn.itrnnum = igtrtrnnum and trn.itrnanum = igtrtrnanum and igtrcat=703

left join obg on igtrcat=iobgcat and igtrnum=iobgnum

WHERE xxi.trn.itrnba2c <> 70603 and xxi.trn.itrnba2d <> 70608 AND

xxi.trn.CTRNSTATE1='4' AND xxi.trn.CTRNSTATE5<>'2'

and trn.MTRNSUM <> 0 and trn.MTRNSUMC <> 0

and trn.DTRNTRAN >= ? and trn.DTRNTRAN < ?

Пример настройки таблицы аналитики Добавление счетов для импорта для этого примера приведён в приложении Приложение 7. Справочник «Добавление счетов из импорта» для банков.

20.13. Импорт проводок из XML-файлов

Чтобы начать импорт проводок из XML-файла:

  • щёлкните мышью надпись Импорт проводок на главном экране ФинГрада;
  • или выберите в главном меню ФинГрада раздел Работа | Импорт проводок бухгалтерии (1С, АБС и т. д.).

Откроется окно с источниками импорта. Нажмите кнопку справа от надписи Импорт проводок бухгалтерии.

В выпадающем списке выберите пункт Из XML-файлов:

Откроется окно Импорт проводок из XML-файлов:

Укажите параметры импорта и параметры XML:

  • в поле Наименование импорта задайте условное имя для данного источника данных. Имена разных источников данных не должны совпадать;
  • в поле Каталог исходных файлов выберите из списка папку, из которой хотите импортировать данные;
  • в поле Маска файлов введите повторяющуюся часть наименования файлов, если выполняете импорт из нескольких XML-файлов. В случае, если наименования файлов не повторяются либо неизвестны заранее, воспользуйтесь значением «*.»;
  • в поле Дата начала учета введите дату, начиная с которой ФинГрад будет импортировать данные о хозяйственных операциях из XML-файлов;
  • в поле Образец XML-файла необходимо выбрать файл, на основании перечня тэгов из которого будут формироваться списки тэгов для заполнения последующих полей.

После выбора файла открывается окно Образец XML-файла, в котором отображается набор тэгов, содержащихся в выбранном файле:

  • в поле XML-тэг «Номер документа» выберите из списка тэг, определяющий номер документа, в рамках которого осуществляются проводки;
  • в поле XML-тэг «Дата операции» выберите из списка тэг, определяющий дату формирования документа, в рамках которого осуществляются проводки;
  • в поле XML-тэг «Сумма» выберите из списка тэг, определяющий сумму по документу, в рамках которого осуществляются проводки;
  • в поле XML-тэг «Валюта» выберите из списка тэг валюты, в которой осуществлены импортируемые проводки. Если поле останется пустым, ФинГрад автоматически выбирает валюту RUR (рубль);
  • в поле XML-тэг «Счет дебет» выберите из списка тэг, определяющий номер дебетового счета. Если поле останется пустым, ФинГрад автоматически проставляет значения из соответствующих полей вкладки Маппинг в окне Импорт проводок из XML-файлов;
  • в поле XML-тэг «Счет кредит» выберите из списка тэг, определяющий номер кредитного счета. Если поле останется пустым, ФинГрад автоматически проставляет значения из соответствующих полей вкладки Маппинг в окне Импорт проводок из XML-файлов.

Указав параметры импорта, нажмите кнопку . Окно Импорт проводок из XML-файлов закроется, и в списке источников данных появится новая строка со сведениями об указанном источнике импортируемых данных:

Если вы уже выполняли импорт в ФинГрад из XML-файлов, 1С, файлов Excel или иных источников, то в окне Импорт проводок отображаются строки, соответствующие ранее настроенным источникам импорта. Этот список позволяет быстро запустить импорт из того же источника, что использовался ранее. Просто щёлкните нужную строку в списке мышью. Более подробно возможности работы со списком источников данных разобраны в п. 18.5.1. Список источников импорта.

После выбора источника данных необходимо указать период, за который будете импортировать проводки (см.п. 18.5.2. Задание периода импорта).

Для корректной записи проводок из XML-файлов в базу ФинГрад необходимо установить соответствие между аналитиками в ФинГрад и XML-тэгами из XML-файлов, которое устанавливается следующим образом.

В окне Импорт проводок выберите в графе Источник данных созданный импорт проводок и нажмите .

В результате откроется окно созданного импорта проводок, содержащее вкладки Маппинг, Трансформация и Проводки:

Во вкладке Маппинг напротив значений графы Параметр в графе Значение проставьте аналитики ФинГрад. Для этого нажмите , после чего будет доступно редактирование полей в графе Значение:

  • Дебет и Кредит заполняются выбором значений в окне Выбор счета;
  • Сумма заполняется выбором аналитики Сумма;
  • строки в области Измерения и реквизиты регистра заполняются выбором аналитик в окне Выбор аналитики.

После внесения требуемых данных необходимо нажать . ФинГрад запустит процесс импорта.

Просмотр результатов импорта. После того, как ФинГрад выполнит импорт, Вы сможете просмотреть на экране во вкладке Проводки импортированные проводки. Просмотр результатов импорта проводок из XML-файлов аналогичен просмотру импорта из 1С (см.п. 18.5.6. Просмотр результатов импорта).

Настройка трансформаций при импорте.Более сложные условия для преобразования проводок при импорте можно задать с помощью правил трансформации на вкладке Трансформация окна параметров импорта проводок (см. п. 18.10. Настройка трансформаций при импорте).