My integration with 1C

    Hi Habravchanam!

    In this article I want to talk about how integration with the 1C platform in my organization is established. It prompted me to do this almost complete lack of technical information on this topic. Reading various articles and reports on the connection of 1C with any information system, you are convinced over and over again that they are all of a marketing, demonstration nature, 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.

    As a language that will integrate with 1C, I chose Python. It is very well suited for process automation. This is facilitated by minimal 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 the Linix OS will work successfully on Windows.

    To begin, I will outline the data with which we will work. The organization, an energy distribution company in the Far Eastern 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.

    The organization once had 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 the debtor subscribers, distribute the received payments, record the instrument readings. Unsurprisingly, the collection of scripts automating the routine has been constantly growing. Programmers could perform any action without opening the program itself.

    Alas, with the transition to 1C, the freebie ended - there was no way to connect to the base directly. In general, the 1C platform itself is indivisible and poorly integrates with other systems. She, as they say, is a thing in herself. When loading data into 1C, it should be remembered that extracting it from there will not be so simple. But in view of the fact that the organization needed to implement payment systems and a personal account, it was necessary to find some solution.

    The main tasks that confronted me were the ability to quickly receive data on a specific personal account - name, address, metering devices, meter readings, payments, charges. Plus, the formation of documents - an act of reconciliation, payment receipt. So, there is no possibility of a direct connection to 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 of the form aaa1, aaa2. And building queries with such table and field names is simply unrealistic. In addition, many tables 1C (especially the most important, such as a slice of the latter, residues and turns) are virtual and scattered across different physical tables, gathering multiple joins. This method is not suitable.

    Platform 1C provides the ability to connect to it through 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 the language that supports COM technology.

    An Automation Server object is a 1C application that is almost no different from a regular client application. The difference is that in addition there is the possibility of programmatically managing the application instance. When working with a COM Connector object, a lightweight version of a 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 External Connection mode. Initialization of global variables (for example, determining the current user and his settings) should be performed in the external connection module 1C. If a function that is not available in this mode is called in the external connection mode, an exception will be raised (which will be passed to our python script). Call unsafe functions should be framed by constructions of the form

    #Если НЕ ВнешнееСоединение Тогда

    Since working with COM objects is exclusively windows-only technology, it is not surprising that it is not included in the standard Python package. 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. As an alternative, you can immediately install the ActiveState Python distribution , in which the Win32 extension comes out of the box.

    For some time I experimented with a COM connection in the development of web applications, in particular, my personal account. The following disadvantages were identified:

    - COM connection is slow. Slow performance is a known minus 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 on python work as independent servers, the previous point can be compensated by storing the connection in some global variable and, in case of an error, restore it. How to maintain a connection in PHP, to be honest, I have not thought about it yet.
    - Lost cross-platform web application.

    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), uploading 1C data to any convenient format, and the second, platform-independent, capable of working with data, not suspecting anything about 1C in principle.

    The action strategy is as follows: the python script connects to 1C, performs the necessary queries and uploads 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 cannot stand writing raw SQL queries with my hands, all work with the SQLite database is done through SQLAlchemy ORM. All that was needed was to describe the database structure 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’s 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 the 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 on which there is no 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 in 1C can be automated in the same way. For example, you want to record the testimonies left by subscribers on the website of your personal account. In this case, we again connect to 1C and use the software method to 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 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 you familiarize yourself with the code:

    import pythoncom
    import win32com.client
    V82_CONN_STRING = "Srvr=v8_server;Ref=v8_db;Usr=username;Pwd=megapass;"
    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 occurs 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”.

    On the initialized client, we call the Connect () method, passing it the connection string. The string is made up of the server name, database, user and password. The resulting V82 object stores a connection to the 1C application. It does not have a Disconnect () method or anything like that. To disconnect from the database, simply delete the object from memory using the del () function or assign the variable None.

    Having an object, you can access any fields and methods of the 1C global context, operate with universal objects of the type TableDocument, ValueValue, and so on. It is important to consider that when working through a COM connection, 1C works 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 often curse configuration developers who enclose the most important functionality in the Button1Press () 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 medium and there is an English-language analogue for each Russian method, sooner or later it will be necessary to turn to the cyrillic attribute. If in PHP or VBSCript it doesn’t cause any problems,

    Set Con = CreateObject("v81.COMConnector")
    Set v8 =Con.Connect("строкаПодключения")
    Set СчетаМенеджер = v8.Документы.Счета
    Set СчетаЗапись= СчетаМенеджер.СоздатьЭлемент()
    СчетаЗапись.Контрагент = ....

    then the python code just crashes with a Syntax Error. What to do? Edit configuration? No, just use the getattr and setattr methods. Passing the COM object and the Cyrillic attribute name to these functions, you can get and set the values ​​accordingly:

    catalog = getattr(V82.Catalogs, "ЛицевыеСчета")

    The following is important: the names of details, as well as the parameters of functions and methods, must be transmitted in cp1251 encoding. Therefore, in order to avoid a putin with encodings in advance, it makes sense to declare it at the beginning of the file: # coding = cp1251. After that, you can transfer the strings without worrying about their encoding. But! All strings obtained from 1C (results of calling functions, queries) will be in UTF-8 encoding.

    Example code that executes a query in 1C environment, iterates over the result and saves the database in SQLite:

        q = '''
    	ЛицевыеСчета.Код КАК code,
    	ЛицевыеСчета.Строение.НаселенныйПункт.Наименование + ", " + ЛицевыеСчета.КраткийАдрес КАК address,
    	ЛицевыеСчета.Абонент.Наименование КАК fio,
    	ЛицевыеСчета.Дивизион.Наименование КАК psu,
    	ВЫРАЗИТЬ(ХарактеристикиЛицевыеСчетаСрезПоследних.Значение КАК Справочник.ТерриториальноСетевыеОрганизации).Наименование КАК tso,
    	ЛицевыеСчета.Строение.НаселенныйПункт.Наименование КАК np,
    	ЛицевыеСчета.Строение.Улица.Наименование КАК street,
    	ЛицевыеСчета.Строение.Дом КАК house,
    	ЛицевыеСчета.ОсновноеПомещение.НомерПомещения КАК flat,
        ЛицевыеСчета.Дивизион.Родитель.Наименование КАК mro
    	Справочник.ЛицевыеСчета КАК ЛицевыеСчета
    		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ХарактеристикиЛицевыеСчета.СрезПоследних(, ВидХарактеристики = ЗНАЧЕНИЕ(Справочник.ВидыХарактеристик.ТерриториальноСетеваяОрганизация)) КАК ХарактеристикиЛицевыеСчетаСрезПоследних
    		ПО ЛицевыеСчета.Ссылка = ХарактеристикиЛицевыеСчетаСрезПоследних.Объект
        query = V82.NewObject("Query", q)
        selection = query.Execute().Choose()
        CONN = db.connect()
        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.street = selection.street
            abonent.flat = selection.flat
            abonent.mro = selection.mro

    Here CONN is a session of connection to SQLite-base. The query query object is created, its text is filled. As noted above, the request text should be in cp1251, for which the encoding is first declared. After the request is completed, 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 identified the following rules.

    - When choosing fields, assign them names in Latin letters, it will be much more convenient to access them through a selector (dot), instead of getattr ().
    - Only select primitive data types: strings, numbers, date, and boolean. Never select links to an object (document, reference)! In this context, you absolutely do not need links and are even harmful, because any call to the attribute or link method will lead to the request through a COM connection. If you access the link attributes in a loop, it will be extremely slow.
    - If you select a field of type Date, then 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 as convenient to work with it as with the usual datetime. If you pass this object to int (), then timestamp will be returned, from which you can then obtain the datetime using the fromtimestamp () method.

    Now we will consider how paper 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 act. These documents are generated in 1C in accordance with the established requirements, their implementation in Python will take a lot of time. Therefore, it is better to generate documents in 1C and save them in Excel format.

    So, a reconciliation report document is generated by special external processing. For those who are not familiar with the terminology of 1C: processing is an autonomous 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 the function, which will return us a table 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()

    In the given fragment the following is performed. 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 directory). Since the processing often changes, so as not to update the configuration each time, the most frequently changing processing is stored in the "System Reports" directory, in the attribute of the "value storage" type with the name Report. Processing can be initialized by unloading it from the database to disk and loading it, or using the GetURL () method, into which you need to transfer a link to a directory item and a props name. We assign the details to the resulting processing object, call the exported GetDoc () function, and get a table document that is saved in a temporary Excel file. The contents of this file are written to the SQlite database.

    The last thing that remains to be considered is software data entry in 1C. Suppose that you want to enter testimony from subscribers. To do this, it is enough to create and conduct a document “Act of taking testimony”:

    acts = getattr(V82.Documents, "АктСнятияПоказаний")
    act = acts.CreateDocument()
    setattr(act, "Показание", 1024.23)
    setattr(act, "Абонент", "Иванов")
    # Заполнение прочих реквизитов...

    Now data entry is automated.

    So, I outlined a method that is based on software uploading and downloading data using a COM connection. This method has been successfully operating 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 disadvantages of the method (slow COM connection speed), in general, it functions stably. We have data in a platform-independent form (SQLite), which you can work with 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 that you can’t even dream of in 1C.

    This is one of the possible ways of interacting 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 out the maximum details of the process in order to protect you from the pitfalls that I myself was stepping on.

    I wish you all good luck, and remember that 1C is not as scary as it is painted!

    Also popular now: