Hello Habravchans!
In this article, I want to talk about how the integration with the 1C platform is established in my organization. The almost complete lack of technical information on this topic prompted me to do this. Reading various articles and reports on the topic of linking 1C with any information system, over and over again you are convinced that they are all marketing, demonstration, and never technical, reflecting the problem and the essence of its solution.
I warn you that the method in no way claims to be universal. Since there are many 1C configurations themselves, and there are even more information systems, languages and platforms, the number of possible combinations is huge. My goal is to demonstrate one of the possible solutions.
I chose Python as the language that will integrate with 1C. It is very well suited for process automation. This is facilitated by the minimalistic syntax (the code is typed very quickly), the rich standard library (less need for third-party modules), cross-platform - with a high probability, the code written in the Linix OS will work successfully on Windows.
To begin with, I will outline the data with which we will work. The organization - an energy sales company in the Far East region - serves approximately 400 thousand subscribers, 1C base on a self-written configuration. For each subscriber, his payments, charges, consumed services and calculation schemes, metering devices, readings and many other data are stored.
Once in the organization there was a program written in Delphi and using MSSQL / Firebird as a database. In those glorious times, it was possible to connect to the database using any language and perform many actions - select debtor subscribers, post payments received, record instrument readings. Unsurprisingly, the collection of scripts that automate the routine grew steadily. Programmers could perform any action without opening the program itself.
Alas, with the transition to 1C, the freebie ended - it was no longer possible to connect to the base directly. In general, the 1C platform itself is indivisible and does not go well for integration with other systems. She, as they say, is a thing in itself. When loading data into 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 implement payment systems and a personal account, it was necessary to find some kind of solution.
The main tasks facing me were the ability to quickly obtain data on a specific personal account - name, address, metering devices, instrument readings, payments, charges. Plus the formation of documents - a reconciliation act, a payment receipt. So, there is no direct connection to the database - everyone who looked at the 1C database on the SQL server saw that it was difficult to understand the mass of tables like aaa1, aaa2. And building queries with such names of tables and fields is simply unrealistic. In addition, many 1C tables (especially the most important ones, such as a cut of the latter, residuals and revolutions) are virtual and scattered across different physical tables, collected by multiple joins. This method is not suitable.
The 1C platform 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 supports COM technology.
The Automation Server object is a 1C application that is almost no different from a regular client application. The difference is that in addition, it becomes possible to programmatically control the application instance. When working with the COM Connector object, a lightweight version of the 1C application is launched, in which forms, as well as functions and methods related to the interface and visual effects, are not available. The application itself is launched in the "External connection" mode. Initialization of global variables (for example, defining the current user and his settings) should be performed in the 1C external connection module. If in the external connection mode in the code a function is called that is not available in this mode, an exception will be raised (which will be passed to our python script). Calls of unsafe functions should be surrounded by constructs like
# If NOT OuterConnection Then Warning ("Hello!"); #EndIf
Since working with COM objects is an exclusively windows-only technology, it is not surprising that it is absent in the standard Python package. You will need to install an extension - a set of modules that provide all the necessary functionality for programming under Windows in 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, which comes with the Win32 extension out of the box.
For some time I experimented with COM connection in the development of web applications, in particular, my personal account. The following disadvantages were identified:
COM connection is slow. Poor performance is a known disadvantage of COM technology.
- The process of establishing a connection with 1C, depending on the configuration, can take from 1 to 8 seconds (in my case, 6 seconds). Needless to say, establishing a connection for each request will result in each page loading for 8 seconds.
- Since web applications in Python work as independent servers, the previous point can be compensated for by storing the connection in some global variable and, in case of an error, restore it. To be honest, I haven’t thought about how to maintain a connection in PHP.
- The cross-platform functionality of the web application is lost.
Based on the points listed above, it was decided to change the principle of interaction, dividing it into 2 parts - the first platform-dependent (Windows), unloading 1C data in some convenient format, and the second, platform-independent, capable of working with data without suspecting anything about 1C in principle.
The action strategy 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 in python, and since I hate writing raw SQL queries by hand, all work with the SQLite database is done through the SQLAlchemy ORM. It was only necessary to describe the database data structure in a declarative style:
From sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, Numeric, DateTime, Unicode, Boolean, LargeB, 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" # and so on ...
Now it is enough to import this module into any python project, and you can work with data.
I foresee your question - "why SQLite"? The main reason is that the database is read-only, so 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 a super-extension for FireFox). Thirdly, in some cases it was required to get 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 this machine will have access to all the information.
Unloading occurs once a day at night. Entering data into 1C can be automated in the same way. For example, it is required to record the readings left by subscribers on the website of their personal account. In this case, we again connect to 1C and, using the program method, create and execute the document “Act of taking readings”. I will give the code below.
Working with COM objects in Python is a bit unusual. Firstly, the “pythonicity” of the code is lost - the rules for naming variables and functions in 1C, to put it mildly, do not correspond to the Zen of Python. Secondly, everyone knows that 1C objects are often called Cyrillic symbols, which will cause problems when developing in Python ... but they can be solved. I suggest you read 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 you can see from the code, the client is initialized to work with 1C. The definition of a COM object is called "V82.COMConnector". Please note that this name is valid for the V8.2 platform, if you have version 8.1, the name will be "V81.COMConnector".
On an initialized client, we call the Connect () method, passing it a connection string. The string consists of the server name, base, user and password. The resulting V82 object stores the connection with the 1C application. It doesn't have a Disconnect () method or anything like that. To disconnect from the base, it is enough to delete the object from memory using the del () function or assign it to the variable None.
Having an object, you can access any fields and methods of the 1C global context, operate with universal objects such as TabularDocument, ValuesTable, etc. It is important to take into account that when working through a COM connection, 1C operates in the "External connection" mode. Any functions for interactive work are not available in it, for example, pop-up dialogs, notifications, and, most importantly, forms. I am sure that you will curse the configuration developers more than once, who enclose the most important functionality in the Button1Click () procedure in the document form module.
Let's talk about such an important thing as Cyrillic Attributes. Despite the fact that 1C is a bilingual environment and for every Russian method there is an English-language analogue, sooner or later it will be necessary to turn to the cyrillic attribute. If in PHP or VBSCript languages this does not cause any problems,
Set Con = CreateObject ("v81.COMConnector") Set v8 = Con.Connect ("ConnectionString") Set AccountsManager = v8.Documents.Invoices .... Set AccountsRecord = AccountsManager.CreateElement () AccountsRecord.Contractor = .... .... AccountsWrite.Write ()
Then the Python code will simply crash with a 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 name of the attribute to these functions, you can get and set the values accordingly:
# coding = cp1251 catalog = getattr (V82.Catalogs, "Personal Accounts")
The following is important: the names of the attributes, as well as the parameters of functions and methods must be passed in cp1251 encoding. Therefore, in order to avoid the encoding path in advance, it makes sense to declare it at the beginning of the file: # coding = cp1251. After that, you can transfer strings without worrying about their encoding. But! All strings received from 1C (results of function calls, requests) will be encoded in UTF-8.
An example of code that executes a query in a 1C environment, iterates over the result and saves the database to SQLite:
# coding = cp1251 q = "" "SELECT Personal Accounts.Code AS code, Personal Accounts.Structure.PopulatedPart.Name +", "+ Personal Accounts.ShortAddress AS address, Personal Accounts.Subscriber.Name AS fio, Personal Accounts.CA Division psu.Number EXPRESS (CharacteristicsPersonalAccountsSliceLast.Value AS Directory.TerritoriallyNetworkOrganizations) .Name AS tso, PersonalAccounts.Structure.PopulatedPart.Name AS np, PersonalAccounts.Street.Room.Room.Number AS street, Face.AccountsNo. , PersonalAccounts.Division.Parent.Name AS mro FROM Directory.PersonalAccounts AS PersonalAccounts LEFT JOIN Data Register.CharacteristicsPersonalAccounts.CutLast (, TypeCharacteristics = VALUE (Directory.TypesCharacteristics. = 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 to connect to the SQLite database. The query object is created, its text is filled. As noted above, the request text must be in cp1251, for which the encoding is declared first. After executing the request, all subscribers are deleted in the database so as not to add duplicates, then they are added in a loop and the final commit follows.
When working with queries, I found the following rules.
When choosing fields, assign them names in Latin letters, it will be much more convenient to refer to them through a selector (dot), instead of getattr ().
- Choose only primitive data types: strings, numbers, date and boolean. Never select references to an object (document, reference)! In this context, links are absolutely unnecessary for you and even harmful, because any call to a props or a link method will lead to a request through a COM connection. If you access 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. It is a special data type for passing date / time in a COM connection. It is not as convenient to work with it as with the usual datetime. If you pass this object to int (), the timestamp will be returned, from which you can then get the datetime using the fromtimestamp () method.
Now let's look at how printed documents are formed. The fact is that the consumer needs to be given the opportunity to download pre-prepared documents, for example, a payment receipt or a reconciliation statement. These documents are generated in 1C in accordance with the established requirements; their implementation in Python will take a long time. Therefore, it is better to generate documents in 1C and save them in Excel format.
So, the document of the reconciliation act is generated by a special external processing. For those who are not familiar with 1C terminology: processing is a stand-alone program that has its own module, forms, templates, designed to run in a 1C environment. It is necessary to initialize the processing, fill in its details and call a function that will return us a spreadsheet 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, "SystemReports"). FindByDescription ("Elaine Reconciliation Act") nav_url = V82.GetURL (link, "Report") name = V82.ExternalReports.Connect (nav_url) ExternalReport = V82.ExternalReports.Creport (name) setattr (ExternalReport, "Personal Account", 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)
The above snippet does the following. The processing that forms the document is connected. Processing can be built into the configuration, stored on disk or in the 1C database (in some kind of reference book). Since the processing changes frequently, so that every time the configuration is not updated, the most frequently changing processing is stored in the System Reports directory, in the attribute of the “value store” type named Report. Processing can be initialized by unloading it from the database to disk and loading it, or by using the GetURL () method, into which you need to pass a link to a catalog item and the name of an attribute. We assign the attribute values to the received processing object, call the exported function GetDoc (), get a spreadsheet document that is saved to a temporary Excel file. The contents of this file are written to the SQlite database.
The last thing that remains to be considered is the software entry of data into 1C. Suppose you want to enter readings from subscribers. To do this, it is enough to create and carry out the document "Statement of taking readings":
# coding = cp1251 acts = getattr (V82.Documents, "Acceptance Act") act = acts.CreateDocument () setattr (act, "Indication", 1024.23) setattr (act, "Subscriber", "Ivanov") # Filling in other details. .. act.Write ()
Now data entry is automated.
So, I have outlined a method that is based on programmatically unloading and loading data using a COM connection. This method has been successfully functioning in my organization for almost a year. The base, formed from 1C, 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) that can be worked with from any language. And the main part of the code is written in Python, which means that there are many tools and techniques available that cannot even be dreamed of in 1C.
This is one of the possible ways to interact with 1C. I am sure that it is not new and has probably already been tested and optimized by someone. However, I tried to set out as many details of the process as possible in order to save you from the pitfalls that I myself stepped on.
I wish you all good luck, and remember that 1C is not as scary as it is painted!
One of the options for data exchange between 1C bases is exchange via a COM connection.
Using a COM connection, you can connect to another from one 1C database and read or write data. This method can be used both in client-server versions of databases and in file databases. In this article, we will analyze examples of this kind of connections. The examples use platform 8.2.
You can create two types of COM objects for a 1C application. This V82.Application and V82.COMConnector... In case of V82.Application almost a full-fledged instance of the 1C application is launched. in case of use V82.COMConnector a small server part starts up.
The operating speed is higher in this case, but some functions may not be available. In particular, work with forms and with common modules for which the property of working with external joins is not set. Mostly you need to use V82.COMConnector and only in case of lack of functionality V82.Application... The difference in speed can be especially noticeable on large databases.
So let's get started
- Let's create a COM object
- for V82.Application Connection = New COMObject ("V82.Application");
- for V82.COMConnector Connection = New COMObject ("V82.COMConnector");
- Let's form a connection string
- for the server version of the database ConnectionString = "Srvr =" "ServerName" "; Ref =" "BaseName";
- for the file version of the database ConnectionString = "File =" "Path to Base" "; Usr = UserName; Pwd = Password";
- We connect to the base Attempt to Connect = Connect. Connect (ConnectionString); Exception Message = New Message to User; Message. Text = + DescriptionErrors (); Message. To report() ; End of Attempts;
- We break the connection to the base Connection = Undefined;
For object V82.Application it is imperative to terminate the connection, otherwise an unfinished session will remain hanging, which will then have to be deleted manually. In case of V82.COMConnector the connection is terminated automatically at the end of the procedure in which the connection was made. And there is one more small moment.
For the user under whom the connection is made, the checkbox "Request confirmation when closing the program" in its settings must be disabled.
Now let's put all the code together.
Connection = New COMObject ("V82.Application"); // Connection = New COMObject ("V82.COMConnector"); ConnectionString = "Srvr =" "Server1C" "; Ref =" "MyBase" "; Usr = Petya; Pwd = 123"; // ConnectionString = "File =" "С: \ MyBase" "; Usr = Petya; Pwd = 123"; Attempt to Connect = Connect. Connect (ConnectionString); Exception Message = New Message to User; Message. Text = "Failed to connect to the database"+ DescriptionErrors (); Message. To report() ; End of Attempts; Connection = Undefined;
For connection type V82.Application the method is used for a COM object that was originally created, and for V82.COMConnector the method is applied to the connection. further work with the request is carried out using standard 1C tools. in the code it looks like this:
Request = Connection. NewObject ("Request"); // for V82.COMConnector Request = Connection. NewObject ("Request"); // for V82.Application Inquiry. Text = "SELECT | Positions of Organizations. Code, | Positions of Organizations.| FROM | Directory. Positions of Organizations AS Positions of Organizations "; Result = Request. Run (); Sample = Result. Select() ; While Sampling. Next () Loop End of Loop;
For version 1C: Enterprise 8.3, everything remains unchanged, except that when creating COM objects, you must use "V83.COMConnector" or "V83.Application".
Print (Ctrl + P)
One of the options for data exchange between 1C bases is exchange via a COM connection. Using a COM connection, you can connect to another from one 1C database and read or write data. This method can be used both in client-server versions of databases and in file databases. This article discusses this kind of connections on the 8.3 platform
com connection
You can create two types of COM objects for a 1C application. It's an ole connection V83.Application and com connections V83.COMConnector ... In case of V83.Application almost a full-fledged instance of the 1C application is launched. In case of use V83.COMConnector a small server part starts up. The operating speed is higher in this case, but some functions may not be available. In particular, work with forms and with common modules for which the property of working with external joins is not set. Mostly you need to use V83.COMConnector and only in case of lack of functionality V83.Application... The difference in speed can be especially noticeable on large databases. For platform 8.2 is used V82.Application or V82.COMConnector
Establish OLE connection
Connection = New COMObject (“V83.Application”);
Establish COM connection
Connection = New COMObject (“V83.COMConnector”);
Connection string
// For client-server option
StringConnection= “Srvr =“ “ServerName” “; Ref =“ “BaseName”;
// For the file mode option:
StringConnection= “File =“ “Path to Base” “; Usr = UserName; Pwd = Password ";
Attempt
Connection = Connection ... Connect(ConnectionString);
An exception
Message = New Message to User;
Message ... Text =
"Failed to connect to the base" +
DescriptionErrors (); Message ... To report();
End of Attempts;
Disconnect
Connection = Undefined;
For object V83.Application it is imperative to terminate the connection, otherwise an unfinished session will remain hanging, which will then have to be deleted manually. In case of V83.COMConnector the connection is terminated automatically at the end of the procedure in which the connection was made. And there is one more small moment. For the user under whom the connection is made, the checkbox "Request confirmation when closing the program" in its settings must be disabled.
NewObject () method
To create a new object, you can use the NewObject () method, for example:
for V83.COMConnector
RequestCOM = Connection. NewObject ( "Inquiry ")
;
TableCOM = Connection. NewObject ( "Table of Values")
;
ArrayCOM = Connection. NewObject (“Array”);
WidCOM = Connection.NewObject
for V83.Application
RequestOLE = Connection. NewObject (“ Inquiry ")
;
Table OLE = Connection. NewObject("Table of Values")
;
ArrayOLE = Connection.NewObject(“Array”);
WidCOM = Connection.NewObject(“UniqueIdentifier”, StringUID);
RequestCOM ... Text ="SELECT
| Positions of Organizations. Code,
| Positions of Organizations.
| FROM | Directory.Organizations' Positions
AS Positions of Organizations ”;
Result = RequestCOM. Run ();
Sample = Result. Select () ;
While Sampling. Next()Cycle
End of Cycle;
You can also use the configuration object managers:
ReferenceCOM = Connection. Reference books. Directory Name;
DocumentCOM = Connection. Documentation. DocumentName;
Register COM = Connection. Information Registers... Register Name;
Getting and comparing an enum over a COM connection
To compare the values of enumeration elements defined in the configuration, it is necessary to convert these elements to one of the primitive types, the comparison of which is not difficult. These types can be either numeric or string types. You can convert the value of an enumeration element to a numeric type like this
EnumerationElement = Connection.Directories.Directory1.FindByCode (1) .Props1;
PossibleValues = EnumerationElement.Metadata (). EnumerationValues;
EnumerationElementNumber = PossibleValues.Index (PossibleValues.Find (Connection.XMLString (EnumerationElement)));
If EnumerationElementNumber = 0 Then Report ( "EnumerationValue1");
ElseIf EnumerationElementNumber = 1 Then Report (“EnumerationValue2”);
EndIf;
Getting an object via COM by identifier
Through the managers of configuration objects, we get a com object, for example:
DocumentCOM = Connection. Documentation. DocumentName;
Then we get the unique identifier string:
StringUID = Connection.string ( DocumentCOM.UniqueIdentifier())
ID = New U uniqueID (StringUID);
WITH linkBy ID = Documents [DocumentName] .GetLink (ID);
If you need to find a com object by document by identifier, then you need to write like this:
WidCOM = Connection.NewObject(“UniqueIdentifier”, StringUID);
ReferenceById = Connection.Dokumenty [DocumentName] .GetLink (UIDCOM);
