My integration with 1C
Hello Habravchanam!
In this article I want to talk about how to integrate with the 1C platform in my organization. It made me do this almost complete lack of technical information on this topic. Reading various articles and reports on the 1C bundle with any information system, time after time you are convinced that all of them are of a marketing, demonstration nature, and never technical, reflecting the problem and the essence of its solution.
I warn you that the method does not in any way claim to universality. Since the 1C configurations themselves exist quite a lot, and there are even more information systems, languages ​​and platforms, the number of possible combinations is enormous. My goal is to demonstrate one of the possible solutions.
')
As a language that will integrate with 1C, I chose Python. It is very well suited for process automation. This is facilitated by the minimalism of the syntax (the code is typed very quickly), a rich standard library (less need for third-party modules), cross-platform - with a high probability, the code written in Linix OS will work successfully in Windows.
To begin, outline the data with which we will work. The organization - an energy sales company in the Far East region - serves approximately 400,000 subscribers, with a 1C base on a samopisi configuration. For each subscriber his payments, charges, consumable services and calculation schemes, metering devices, readings and a lot of other data are stored.
Once in the organization there was a program written in Delphi and using MSSQL / Firebird as the database. In those glorious times, it was possible to connect to the database with the help of any language and perform a variety of actions — select debtor subscribers, post incoming payments, record instrument readings. Not surprisingly, the collection of scripts that automate the routine has grown steadily. Programmers could perform any actions without opening the program itself.
Alas, with the transition to the 1C freebie ended - there was no opportunity to connect with the base directly. In general, the 1C platform itself is indivisible and poorly integrated with other systems. She, as they say, is a thing in herself. Loading data in 1C, it should be remembered that it will not be so easy to extract them from there. But in view of the fact that the organization needed to introduce payment systems and a personal account, it was necessary to find some kind of solution.
The main tasks facing me are the ability to quickly obtain data on a specific personal account - name, address, metering devices, instrument readings, payments, charges. Plus the formation of documents - the act of reconciliation, payment receipt. So, there is no direct connection with the database - everyone who looked at the 1C database on the SQL server saw that it was difficult to figure out the mass of tables like aaa1, aaa2. And building queries with the names of such tables and fields is simply unrealistic. In addition, many 1C tables (especially the most important ones, like the cut-off of the latter, remnants and turns) are virtual and scattered over different physical tables, being collected by multiple joins. This method does not fit.
Platform 1C provides the ability to connect to it via a COM connection. Like many windows programs, during the installation of 1C, two COM objects are registered in the system - Automation Server and COM Connector. You can work with both objects using a language that provides support for COM technology.
The Automation Server object is a 1C application, almost no different from a regular client application. The difference is that in addition there is the possibility of programmatically controlling the application instance. When working with the COM Connector object, a lightweight version of the 1C application is launched, in which forms are not available, as well as functions and methods related to the interface and visual effects. The application itself starts in the "External connection" mode. The initialization of global variables (for example, determining the current user and its settings) should be performed in the external connection module 1C. If in the external connection mode in the code a function that is not available in this mode is called, an exception will be raised (which will be passed to our python script). Calling insecure functions should be framed with view constructs.
Since working with COM objects is only windows-only technology, it is not surprising that it is absent in the standard Python delivery. You will need to install the Win32 extension - a set of modules that provide all the necessary functionality for programming under Windows on Python. It can be downloaded as an already assembled exe-installer. The extension itself provides access to the registry, services, ODBC, COM objects, etc. Alternatively, you can immediately install the ActiveState Python distribution, in which the Win32 extension comes out of the box.
For a while, I experimented with a COM connection in developing web applications, in particular, a personal account. The following disadvantages were identified:
- COM connection is slow. Poor performance - a well-known minus COM-technology.
- The process of establishing a connection with 1C, depending on the configuration, may take from 1 to 8 seconds (in my case, 6 seconds). Needless to say, establishing a connection for each request will cause each page to load for 8 seconds.
“Since Python web applications work as independent servers, the previous item can be compensated for by keeping the connection in a global variable and restoring it in case of an error.” How to maintain the connection in PHP, I honestly have not thought.
- Lost cross-platform web application.
Based on the above points, it was decided to change the principle of interaction, dividing it into 2 parts - the first platform-dependent (screw), unloading 1C data into a convenient format, and the second, platform-independent, able to work with data without suspecting about 1C in principle.
The strategy of actions is as follows: the python script connects to 1C, executes the necessary queries and uploads the data to the SQLite database. You can connect to this database from Python, PHP, Java. Most of our projects work on python, and since I cannot stand writing raw SQL queries with my hands, all work with the SQLite database is performed via ORM SQLAlchemy. It was required only to describe the data structure of the database in a declarative style:
Now it is enough to import this module into any Python project, and you can work with the data.
I foresee your question - “why SQLite”? The main reason is that the base is only for reading, therefore problems with writing to SQLite should not worry us. Secondly, the format of this DBMS is convenient - it is more convenient to view it (there are many free utilities, including the super-extension for FireFox). Thirdly, in some cases it was required to gain access to subscribers from those machines that do not have a connection to the MySQL server. In this case, it is enough to copy the SQLite database file, and on this machine you will have access to all the information.
Unloading occurs once a day at night. Data entry in 1C can be automated in the same way. For example, it is required to record the testimony left by subscribers on the site of the personal account. In this case, we again connect to 1C and programmatically create and carry out the document “Act of taking readings”. I will give the code below.
Working with COM objects in Python is a bit unusual. First, the “pitonicity” of the code is lost - the rules for naming variables and functions in 1C, to put it mildly, do not correspond to Zen of Python. Secondly, everyone knows that 1C objects are often referred to as Cyrillic characters, which will cause problems when developing on Python ... but they are solvable. I suggest to get acquainted with the code:
As can be seen from the code, the client is initialized to work with 1C. The COM object is defined by the name "V82.COMConnector". Please note that this name is valid for the V8.2 platform, if you have version 8.1, then the name will be “V81.COMConnector”.
At the initialized client, we call the Connect () method, passing it the connection string. The string consists of the server name, database, user and password. The resulting object V82 stores the connection with the application 1C. It does not have a Disconnect () method or something like that. To disconnect from the database, simply delete the object from memory using the function del () or assign the variable None.
Having the object, you can refer to any fields and methods of the global context 1C, operate with universal objects such as Tabular Document, Table of Values, and so on. It is important to note that when working through a COM connection, 1C operates in the “External Connection” mode. It does not have any functions for interactive work, for example, pop-up dialogs, notifications, and, most importantly, forms. I am sure that you will not just curse the developers of the configuration that enclose the most important functionality in the procedure Button 1Press () in the document form module.
Let's talk about such important things as cyrillic attributes. Despite the fact that 1C is a bilingual environment and for each Russian method there is an English-language counterpart, sooner or later it will be necessary to refer to the cyrillic attribute. If in PHP or VBSCript languages ​​this will not cause any problems,
then the code on the python just crashes with an error Syntax Error. What to do? Edit configuration? No, it is enough to use the getattr and setattr methods. By passing the COM object and the Cyrillic attribute name to these functions, you can respectively obtain and set values:
The following is important: the names of the requisites, as well as the parameters of the functions and methods must be transmitted in cp1251 encoding. Therefore, in order to avoid keyboards with encodings in advance, it makes sense to declare it at the beginning of the file: # coding = cp1251. After that, you can pass strings without worrying about their encoding. But! All strings obtained from 1C (the results of calling functions, queries) will be in UTF-8 encoding.
An example of code that executes a query in 1C environment, iterates the result and saves the database to SQLite:
Here CONN is a session of connection with SQLite-base. The query object is created and its text is filled. As noted above, the request text must be in cp1251, for which the encoding is first declared. After the request is executed, all subscribers are deleted in the database in order not to add duplicates, then they are added in a loop and the final commit follows.
When working with queries, I identified the following rules.
- Choosing fields, assign them names in Latin, it will be much more convenient to refer to them through the selector (point), instead of getattr ().
- Choose only primitive data types: strings, numbers, date and boolean. Never select links to an object (document, directory)! In this context, links are absolutely unnecessary to you, and even harmful, because any reference to the requisite or link method will lead to a request via a COM connection. If you refer to the link attributes in a loop, it will be extremely slow.
- If you select a Date field, it will be returned as a PyTime object. This is a special data type for transmitting date-time in a COM connection. It is not so convenient to work with it as with the usual datetime. If you pass this object to int (), then the timestamp will return, from which you can then get the datetime using the fromtimestamp () method.
Now we will consider how printed documents are formed. The fact is that the consumer should be given the opportunity to download documents prepared in advance, for example, a payment receipt or reconciliation report. These documents are formed in 1C in accordance with the established requirements; their implementation on Python will take a long time. Therefore, it is better to generate documents in 1C and save them in Excel format.
Thus, the document of the act of reconciliation is generated by a special external processing. For those who are not familiar with the terminology of 1C: processing is a standalone program that has its own module, forms, templates, designed to run in 1C environment. It is necessary to initiate processing, fill in its details and call a function that will return us the tabular document intended for viewing in 1C. This document must be saved in Excel format and copied to the server or written to the database.
In the given fragment the following is carried out. The processing forming the document is connected. Processing can be embedded in the configuration, stored on disk or in the 1C database (in a directory). Since the processing changes frequently, in order not to update the configuration each time, the most frequently changing processing is stored in the “System Reports” directory, in the “value storage” type with the name Report. Processing can be initialized by unloading it from the database to disk and loading it, or by using the GetURL () method, to which you need to transfer a reference to the directory element and the name of the requisite. We assign the values ​​of the requisites to the received processing object, call the exported function GetDoc (), get the tabular document, which is saved to a temporary Excel file. The content of this file is written to the SQlite database.
The last thing to consider is the programmatic entry of data into 1C. Suppose that you want to bring readings from subscribers. To do this, it is enough to create and carry out the document “Act of taking testimony”:
Now data entry is automated.
So, I outlined a method that is based on software unloading and loading data using a COM connection. This method has been successfully operating in my organization for almost a year. The base formed from 1 serves 3 payment systems, Internet acquiring (payment by cards via the Internet), as well as a personal account. In addition, various scripts are connected to the database to automate the routine.
Despite the shortcomings of the method (slow speed of the COM connection), in general, it functions stably. We have data in a platform-independent form (SQLite), with which you can work from any language. And the main part of the code is written in Python, which means that a lot of tools and techniques are available, which even you cannot dream of in 1C.
This is one of the possible ways to interact with 1C. I am sure that it is not new and probably has already been tested and optimized by someone. However, I tried to set forth the maximum details of the process in order to protect you from the pitfalls that I attacked.
I wish you all good luck, and remember that 1C is not so bad as it is painted!
In this article I want to talk about how to integrate with the 1C platform in my organization. It made me do this almost complete lack of technical information on this topic. Reading various articles and reports on the 1C bundle with any information system, time after time you are convinced that all of them are of a marketing, demonstration nature, and never technical, reflecting the problem and the essence of its solution.
I warn you that the method does not in any way claim to universality. Since the 1C configurations themselves exist quite a lot, and there are even more information systems, languages ​​and platforms, the number of possible combinations is enormous. My goal is to demonstrate one of the possible solutions.
')
As a language that will integrate with 1C, I chose Python. It is very well suited for process automation. This is facilitated by the minimalism of the syntax (the code is typed very quickly), a rich standard library (less need for third-party modules), cross-platform - with a high probability, the code written in Linix OS will work successfully in Windows.
To begin, outline the data with which we will work. The organization - an energy sales company in the Far East region - serves approximately 400,000 subscribers, with a 1C base on a samopisi configuration. For each subscriber his payments, charges, consumable services and calculation schemes, metering devices, readings and a lot of other data are stored.
Once in the organization there was a program written in Delphi and using MSSQL / Firebird as the database. In those glorious times, it was possible to connect to the database with the help of any language and perform a variety of actions — select debtor subscribers, post incoming payments, record instrument readings. Not surprisingly, the collection of scripts that automate the routine has grown steadily. Programmers could perform any actions without opening the program itself.
Alas, with the transition to the 1C freebie ended - there was no opportunity to connect with the base directly. In general, the 1C platform itself is indivisible and poorly integrated with other systems. She, as they say, is a thing in herself. Loading data in 1C, it should be remembered that it will not be so easy to extract them from there. But in view of the fact that the organization needed to introduce payment systems and a personal account, it was necessary to find some kind of solution.
The main tasks facing me are the ability to quickly obtain data on a specific personal account - name, address, metering devices, instrument readings, payments, charges. Plus the formation of documents - the act of reconciliation, payment receipt. So, there is no direct connection with the database - everyone who looked at the 1C database on the SQL server saw that it was difficult to figure out the mass of tables like aaa1, aaa2. And building queries with the names of such tables and fields is simply unrealistic. In addition, many 1C tables (especially the most important ones, like the cut-off of the latter, remnants and turns) are virtual and scattered over different physical tables, being collected by multiple joins. This method does not fit.
Platform 1C provides the ability to connect to it via a COM connection. Like many windows programs, during the installation of 1C, two COM objects are registered in the system - Automation Server and COM Connector. You can work with both objects using a language that provides support for COM technology.
The Automation Server object is a 1C application, almost no different from a regular client application. The difference is that in addition there is the possibility of programmatically controlling the application instance. When working with the COM Connector object, a lightweight version of the 1C application is launched, in which forms are not available, as well as functions and methods related to the interface and visual effects. The application itself starts in the "External connection" mode. The initialization of global variables (for example, determining the current user and its settings) should be performed in the external connection module 1C. If in the external connection mode in the code a function that is not available in this mode is called, an exception will be raised (which will be passed to our python script). Calling insecure functions should be framed with view constructs.
# ("!"); # Since working with COM objects is only windows-only technology, it is not surprising that it is absent in the standard Python delivery. You will need to install the Win32 extension - a set of modules that provide all the necessary functionality for programming under Windows on Python. It can be downloaded as an already assembled exe-installer. The extension itself provides access to the registry, services, ODBC, COM objects, etc. Alternatively, you can immediately install the ActiveState Python distribution, in which the Win32 extension comes out of the box.
For a while, I experimented with a COM connection in developing web applications, in particular, a personal account. The following disadvantages were identified:
- COM connection is slow. Poor performance - a well-known minus COM-technology.
- The process of establishing a connection with 1C, depending on the configuration, may take from 1 to 8 seconds (in my case, 6 seconds). Needless to say, establishing a connection for each request will cause each page to load for 8 seconds.
“Since Python web applications work as independent servers, the previous item can be compensated for by keeping the connection in a global variable and restoring it in case of an error.” How to maintain the connection in PHP, I honestly have not thought.
- Lost cross-platform web application.
Based on the above points, it was decided to change the principle of interaction, dividing it into 2 parts - the first platform-dependent (screw), unloading 1C data into a convenient format, and the second, platform-independent, able to work with data without suspecting about 1C in principle.
The strategy of actions is as follows: the python script connects to 1C, executes the necessary queries and uploads the data to the SQLite database. You can connect to this database from Python, PHP, Java. Most of our projects work on python, and since I cannot stand writing raw SQL queries with my hands, all work with the SQLite database is performed via ORM SQLAlchemy. It was required only to describe the data structure of the database in a declarative style:
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, Numeric, DateTime, Unicode, Boolean, LargeBinary, ForeignKey Base = declarative_base() class Abonent(Base): __tablename__ = "abonents" id = Column(Integer, primary_key=True) account = Column(Unicode(32), index=True) code = Column(Unicode(32)) address = Column(Unicode(512)) fio = Column(Unicode(256)) source = Column(Unicode(16)) psu = Column(Unicode(256)) tso = Column(Unicode(256)) np = Column(Unicode(256)) street = Column(Unicode(256)) house = Column(Integer) flat = Column(Integer) mro = Column(Unicode(256)) class Payment(Base): __tablename__ = "payments" # ... Now it is enough to import this module into any Python project, and you can work with the data.
I foresee your question - “why SQLite”? The main reason is that the base is only for reading, therefore problems with writing to SQLite should not worry us. Secondly, the format of this DBMS is convenient - it is more convenient to view it (there are many free utilities, including the super-extension for FireFox). Thirdly, in some cases it was required to gain access to subscribers from those machines that do not have a connection to the MySQL server. In this case, it is enough to copy the SQLite database file, and on this machine you will have access to all the information.
Unloading occurs once a day at night. Data entry in 1C can be automated in the same way. For example, it is required to record the testimony left by subscribers on the site of the personal account. In this case, we again connect to 1C and programmatically create and carry out the document “Act of taking readings”. I will give the code below.
Working with COM objects in Python is a bit unusual. First, the “pitonicity” of the code is lost - the rules for naming variables and functions in 1C, to put it mildly, do not correspond to Zen of Python. Secondly, everyone knows that 1C objects are often referred to as Cyrillic characters, which will cause problems when developing on Python ... but they are solvable. I suggest to get acquainted with the code:
import pythoncom import win32com.client V82_CONN_STRING = "Srvr=v8_server;Ref=v8_db;Usr=username;Pwd=megapass;" pythoncom.CoInitialize() V82 = win32com.client.Dispatch("V82.COMConnector").Connect(V82_CONN_STRING) As can be seen from the code, the client is initialized to work with 1C. The COM object is defined by the name "V82.COMConnector". Please note that this name is valid for the V8.2 platform, if you have version 8.1, then the name will be “V81.COMConnector”.
At the initialized client, we call the Connect () method, passing it the connection string. The string consists of the server name, database, user and password. The resulting object V82 stores the connection with the application 1C. It does not have a Disconnect () method or something like that. To disconnect from the database, simply delete the object from memory using the function del () or assign the variable None.
Having the object, you can refer to any fields and methods of the global context 1C, operate with universal objects such as Tabular Document, Table of Values, and so on. It is important to note that when working through a COM connection, 1C operates in the “External Connection” mode. It does not have any functions for interactive work, for example, pop-up dialogs, notifications, and, most importantly, forms. I am sure that you will not just curse the developers of the configuration that enclose the most important functionality in the procedure Button 1Press () in the document form module.
Let's talk about such important things as cyrillic attributes. Despite the fact that 1C is a bilingual environment and for each Russian method there is an English-language counterpart, sooner or later it will be necessary to refer to the cyrillic attribute. If in PHP or VBSCript languages ​​this will not cause any problems,
Set Con = CreateObject("v81.COMConnector") Set v8 =Con.Connect("") Set = v8.. .... Set = .() . = .... .... .() then the code on the python just crashes with an error Syntax Error. What to do? Edit configuration? No, it is enough to use the getattr and setattr methods. By passing the COM object and the Cyrillic attribute name to these functions, you can respectively obtain and set values:
#coding=cp1251 catalog = getattr(V82.Catalogs, "") The following is important: the names of the requisites, as well as the parameters of the functions and methods must be transmitted in cp1251 encoding. Therefore, in order to avoid keyboards with encodings in advance, it makes sense to declare it at the beginning of the file: # coding = cp1251. After that, you can pass strings without worrying about their encoding. But! All strings obtained from 1C (the results of calling functions, queries) will be in UTF-8 encoding.
An example of code that executes a query in 1C environment, iterates the result and saves the database to SQLite:
#coding=cp1251 q = ''' . code, ... + ", " + . address, .. fio, .. psu, (. .). tso, ... np, ... street, .. house, .. flat, ... mro . ..(, = (..)) . = . ''' query = V82.NewObject("Query", q) selection = query.Execute().Choose() CONN = db.connect() CONN.query(models.Abonent).delete() while selection.Next(): abonent = models.Abonent() abonent.account = selection.code.strip() abonent.code = selection.code abonent.fio = selection.fio abonent.address = selection.address abonent.psu = selection.psu abonent.tso = selection.tso abonent.source = u"ASRN" abonent.np = selection.np abonent.street = selection.street abonent.house = selection.house abonent.flat = selection.flat abonent.mro = selection.mro CONN.add(abonent) CONN.commit() Here CONN is a session of connection with SQLite-base. The query object is created and its text is filled. As noted above, the request text must be in cp1251, for which the encoding is first declared. After the request is executed, all subscribers are deleted in the database in order not to add duplicates, then they are added in a loop and the final commit follows.
When working with queries, I identified the following rules.
- Choosing fields, assign them names in Latin, it will be much more convenient to refer to them through the selector (point), instead of getattr ().
- Choose only primitive data types: strings, numbers, date and boolean. Never select links to an object (document, directory)! In this context, links are absolutely unnecessary to you, and even harmful, because any reference to the requisite or link method will lead to a request via a COM connection. If you refer to the link attributes in a loop, it will be extremely slow.
- If you select a Date field, it will be returned as a PyTime object. This is a special data type for transmitting date-time in a COM connection. It is not so convenient to work with it as with the usual datetime. If you pass this object to int (), then the timestamp will return, from which you can then get the datetime using the fromtimestamp () method.
Now we will consider how printed documents are formed. The fact is that the consumer should be given the opportunity to download documents prepared in advance, for example, a payment receipt or reconciliation report. These documents are formed in 1C in accordance with the established requirements; their implementation on Python will take a long time. Therefore, it is better to generate documents in 1C and save them in Excel format.
Thus, the document of the act of reconciliation is generated by a special external processing. For those who are not familiar with the terminology of 1C: processing is a standalone program that has its own module, forms, templates, designed to run in 1C environment. It is necessary to initiate processing, fill in its details and call a function that will return us the tabular document intended for viewing in 1C. This document must be saved in Excel format and copied to the server or written to the database.
link = getattr(V82.Catalogs, "").FindByDescription(" ") nav_url = V82.GetURL(link, "") name = V82.ExternalReports.Connect(nav_url) ExternalReport = V82.ExternalReports.Create(name) setattr(ExternalReport, "", reference) table_doc = ExternalReport.GetDoc() path = V82.GetTempFileName("xls") table_doc.Write(path, V82 .SpreadsheetDocumentFileType.XLS) report = models.Report() report.account = reference.Code.strip() report.type = u"act" report.document = open(path, "rb").read() CONN.add(report) In the given fragment the following is carried out. The processing forming the document is connected. Processing can be embedded in the configuration, stored on disk or in the 1C database (in a directory). Since the processing changes frequently, in order not to update the configuration each time, the most frequently changing processing is stored in the “System Reports” directory, in the “value storage” type with the name Report. Processing can be initialized by unloading it from the database to disk and loading it, or by using the GetURL () method, to which you need to transfer a reference to the directory element and the name of the requisite. We assign the values ​​of the requisites to the received processing object, call the exported function GetDoc (), get the tabular document, which is saved to a temporary Excel file. The content of this file is written to the SQlite database.
The last thing to consider is the programmatic entry of data into 1C. Suppose that you want to bring readings from subscribers. To do this, it is enough to create and carry out the document “Act of taking testimony”:
#coding=cp1251 acts = getattr(V82.Documents, "") act = acts.CreateDocument() setattr(act, "", 1024.23) setattr(act, "", "") # ... act.Write() Now data entry is automated.
So, I outlined a method that is based on software unloading and loading data using a COM connection. This method has been successfully operating in my organization for almost a year. The base formed from 1 serves 3 payment systems, Internet acquiring (payment by cards via the Internet), as well as a personal account. In addition, various scripts are connected to the database to automate the routine.
Despite the shortcomings of the method (slow speed of the COM connection), in general, it functions stably. We have data in a platform-independent form (SQLite), with which you can work from any language. And the main part of the code is written in Python, which means that a lot of tools and techniques are available, which even you cannot dream of in 1C.
This is one of the possible ways to interact with 1C. I am sure that it is not new and probably has already been tested and optimized by someone. However, I tried to set forth the maximum details of the process in order to protect you from the pitfalls that I attacked.
I wish you all good luck, and remember that 1C is not so bad as it is painted!
Source: https://habr.com/ru/post/139272/
All Articles