How I left elma
Three years ago, in order to automate the management of organizational and administrative documentation, the elma system was acquired. But after a year of operation, it became clear that it would have to be abandoned.
The story of how I went from elma to easla.com.
Before purchasing elma , a thorough analysis of the system on the adequacy of the tasks was carried out. I liked the following in the system:
It is a pity that I could not see the pitfalls immediately. I will not go into much detail, but about three months after the start of operation, problems of a different plan appeared. First of all, I was certainly worried about technical problems, for example:
There were other problems. But that, technical problems which end users do not concern.
However, users also regularly complained about inconveniences, for example:
After a year of operation elma, it became clear that the system will lead the organization to a standstill. We will not be able to implement other processes on it without degrading performance. And there is no guarantee that, having implemented the processes, they will work stably, taking into account constantly “getting out bugs”. All the advantages of the system have disappeared.
A search was made for the system instead of elma . One of the search criteria was its free or low cost, because the management of the organization did not want to spend money on the purchase of a new system, and I, as the head of the IT department, who made the decision to purchase elma at one time and justifying its acquisition was once again ashamed to ask for the same money. Also, in the list of criteria were:
Just easla.com approached all items.
After registering in the system began to develop relevant business processes. Moreover, the attributes of objects had to be implemented so that they could receive imported data from elma .
It took four processes:
The process " Customers " can hardly be called a process. Objects do not even have statuses. In fact, it is a catalog of contractors and related contacts. But the contractor differs from all other objects in the number of attributes. It allows you to store not only the name, but also the legal and postal addresses, as well as bank details. Only 40 attributes! A contact is an individual who is an employee of the counterparty.
The “Contracts” process at that time was also a simple catalog of contracts. He was needed solely for the classification of letters.
The process of " correspondence " - a very important process for the organization. He manages the official incoming and outgoing correspondence, which affects the adoption of technical decisions and participates in resolving conflict situations in court. Incoming letters may cause damage due to late consideration or response. Outgoing letters can bring six- and sometimes seven-digit profit when they are sent in a timely manner. Important differences of the Outgoing Document object are:
The process of " Tasks " is important in the same way as "Correspondence", because is its logical continuation. With the help of tasks, the execution of orders for each incoming or outgoing document is monitored. In addition, in each task, the registration of labor costs in minutes and a bundle with the incoming and outgoing letter is carried out.
The development of processes finished in a month and the question of the actual data import arose. But before - export from elma .
')
Import to easla.com has some features to keep in mind:
I did not find any regular means of uploading data from elma , so I had to go into the database. In the base of elma there is a sea ​​of ​​different tables, the necessary tables were found intuitively. Still, programmers think the same way.
Began to export with simple objects. First counterparties. I wrote this SQL query:
Multiple values ​​for one attribute are combined into one using STUFF (...). The result of the query is saved in csv format with a separator ";". The first line in the file added attribute notation in easla.com . Uploaded file in the section "Import Objects". Then he specified the encoding of the file and the user on whose behalf to perform the import. Launched import. The import procedure is not very fast, it can take about one second to create each object. The number of imported records was about 500. It looked like this:
Now contacts. I wrote this SQL query:
All actions are similar to the previous import. The peculiarity of importing contacts after counterparties was that the contact possesses the attribute "Counterparty", which refers to the counterparty. Therefore, contractors were imported first, followed by contacts. When importing contacts in the "Counterparty" field, easla.com found and substituted links to counterparties ! The number of records imported was approximately 1,800.
After importing counterparties and contacts, a structured database was obtained in which all contacts belong to certain counterparties. It took about 2 hours to import the data. And half the time spent on developing SQL queries. As a result, I got two ready and interrelated tables.
Counterparty Registry:
Contact register:
Next in line was the import of contracts. Unloaded them using this SQL query:
All actions are also similar to previous imports. And just like with contacts, all contracts having the attribute "Counterparty" during the import contacted the corresponding counterparties. The number of records imported was a little over 200.
Now, when all contractors, contacts and contracts referred to by incoming and outgoing documents were in easla.com , it was possible to proceed with the import of correspondence.
The number of incoming and outgoing letters was large - approximately 5400 and 5300, respectively.
It is interesting that incoming and outgoing letters refer to each other, i.e. Outgoing can be sent in response to incoming and information about this is stored in the corresponding attribute of the outgoing document. The incoming document can be received in response to the outgoing document and information about it is stored in the corresponding attribute of the incoming document. Thus, when importing one of the object types, it is impossible to ensure the formation of all links. The first imported documents were imported, but before that, the type of the attribute that stores the link to the outgoing document was changed from “Object” to “String”. This made it possible to save in the attribute a description of the outgoing document that is not yet in the database and use it later, after importing outgoing documents. Ultimately, this is a SQL query for uploading incoming documents:
By the way, the situation was complicated by the fact that along with the data it was necessary to upload and import all files.
It was completely unexpected that the elma developers at some point changed the way they stored files, so I had to create two different requests and merge them together to upload files. There was a problem with the first unloading attempt. The names of some files were repeated and overwritten each other. I had to change the name of the final file in the request, adding id to it. In addition, in fact, the file and its name needed to be transferred to easla.com and additional data stored with the file. They also had to be entered in the file name. Thus, the final file name was formed by the principle: id data filename.ext. Since elma stores files in a separate directory, the result of the query was planned to be converted into a bat file to upload files to the right place. Ultimately, to upload the files of incoming documents, the following query appeared:
Loaded csv file, downloaded all incoming files. The procedure was long, because The total file size was several gigabytes. It's good that importing data by 1000 records, since no lining has not done. Be sure to pop up in every thousand of 10-15 errors. It was possible to identify errors before the import procedure, using a preview. Very comfortably. In general, every thousand required advance preparation.
In the same way imported all outgoing documents.
After that, in the objects was written the procedure for converting file names. She deleted the id, and data, transferred to the revdata file in easla.com . Thus, the application files received the original names filename.ext.
A procedure was written especially for incoming letters, which replaced descriptions of references to outgoing documents with their identifiers.
Checking the work of these procedures launched a batch update of objects. Slowly but surely easla.com updated all the objects! After that, changed the attribute type referencing the outgoing document in the incoming document from “String” to “Object” so that the object identifiers become objects.
Thus, easla.com renamed files and organized cross-references between incoming and outgoing documents!
In general, the export and import procedure took about 3 working days. Download files on easla.com carried out in the evenings, when the Internet channel was free. Importing data by 1000 was also convenient because old letters could be imported immediately, without worrying about data integrity. And just before the “combat” launch I downloaded and imported the remnants - the last couple of hundred letters.
Migration was carried out at the end of 2014, and launched into operation all the described business processes on January 12, 2015.
More than a year has passed. The total amount of letters exceeded 20 thousand. The number of tasks exceeded 10 thousand. Here is the latest data from the main menu:
I can say with confidence that a year ago I did everything right. Here are the main benefits:
At the moment, other processes have also been developed: management of tasks, coordination, subcontract agreements. All that was previously feared to do at elma is now done at easla.com !
PS Please do not regard the article as elma anti-advertising. The system did not suit us, but it may well suit others, if they are not “squeezing the juice out of it,” as we are.
The story of how I went from elma to easla.com.
Before purchasing elma , a thorough analysis of the system on the adequacy of the tasks was carried out. I liked the following in the system:
- Modern web interface
- Ability to customize business processes using flowcharts
- The ability to write code in C #.
It is a pity that I could not see the pitfalls immediately. I will not go into much detail, but about three months after the start of operation, problems of a different plan appeared. First of all, I was certainly worried about technical problems, for example:
- It is extremely difficult to name the files of an object programmatically (remember, technical support offered to write more than a hundred lines of code in different events for this), although two months later the developers fixed this problem.
- Problems creating processes with branches. An incoming letter review process was created, which was branched out according to the number of responsible executives. There was practically no code in the process. However, phantom tasks appeared regularly - I called them so. 5 tasks were assigned, but it turns out 10. It was not possible to solve the problem with the help of technical support and I had to write “crutches” that destroyed phantoms programmatically. But still, in unpredictable situations, they manifested themselves.
- Development of new processes was possible only on a separate, test server, for which regularly had to ask for a license, because It is not included in the delivery. The finished process had to be manually transferred from the test server to the battle one. At the same time, the combat server had to be restarted. During working hours this is impossible, which led to the regular transfer of working time at night.
- Each update brought its own corrections and its own bugs. Once, an update brought a bug that was fixed 2-3 updates back. And sometimes the mistakes were funny - children. For example, two updates in a row could not expand the main menu to the full height of the browser.
There were other problems. But that, technical problems which end users do not concern.
However, users also regularly complained about inconveniences, for example:
- The search for objects is carried out only on a separate page, which seems to be logical, and even search criteria can be entered very complex, but for the end user this functionality was inconvenient. All have long been accustomed to looking for conveniently indicating the criteria in the header of the table, and switching to a separate page and multi-entry criteria caused dissatisfaction and rejection.
- Elma, working in a local network, began to amaze with her thoughtfulness. We studied the system from the inside. It turned out that for each object the system creates a separate table and a handful of adjacent tables for storing data, i.e. braking should not be. However, when the number of entries for each object exceeded five thousand, the brakes became noticeable on an intuitive level. List of pages opened a few seconds.
After a year of operation elma, it became clear that the system will lead the organization to a standstill. We will not be able to implement other processes on it without degrading performance. And there is no guarantee that, having implemented the processes, they will work stably, taking into account constantly “getting out bugs”. All the advantages of the system have disappeared.
Search
A search was made for the system instead of elma . One of the search criteria was its free or low cost, because the management of the organization did not want to spend money on the purchase of a new system, and I, as the head of the IT department, who made the decision to purchase elma at one time and justifying its acquisition was once again ashamed to ask for the same money. Also, in the list of criteria were:
- User-friendly web interface
- Development and change of processes in real time
- File storage flexibility
- The ability to import data from elma to the new system.
Just easla.com approached all items.
Development
After registering in the system began to develop relevant business processes. Moreover, the attributes of objects had to be implemented so that they could receive imported data from elma .
It took four processes:
- Customers
- Treaties
- Correspondence
- Tasks.
The process " Customers " can hardly be called a process. Objects do not even have statuses. In fact, it is a catalog of contractors and related contacts. But the contractor differs from all other objects in the number of attributes. It allows you to store not only the name, but also the legal and postal addresses, as well as bank details. Only 40 attributes! A contact is an individual who is an employee of the counterparty.
The “Contracts” process at that time was also a simple catalog of contracts. He was needed solely for the classification of letters.
The process of " correspondence " - a very important process for the organization. He manages the official incoming and outgoing correspondence, which affects the adoption of technical decisions and participates in resolving conflict situations in court. Incoming letters may cause damage due to late consideration or response. Outgoing letters can bring six- and sometimes seven-digit profit when they are sent in a timely manner. Important differences of the Outgoing Document object are:
- The way of storing the file of the letter
- The method of storage of the attached documents
- The possibility of developing a letter online
- Ability to send a letter in one of a dozen ways.
The process of " Tasks " is important in the same way as "Correspondence", because is its logical continuation. With the help of tasks, the execution of orders for each incoming or outgoing document is monitored. In addition, in each task, the registration of labor costs in minutes and a bundle with the incoming and outgoing letter is carried out.
The development of processes finished in a month and the question of the actual data import arose. But before - export from elma .
')
Export Import
Import to easla.com has some features to keep in mind:
- The first line of the data file may contain attribute designations.
- You can only import 1000 records at a time.
- Multiple values ​​for one attribute must be separated by the symbol "|"
- Imported files must have different names.
I did not find any regular means of uploading data from elma , so I had to go into the database. In the base of elma there is a sea ​​of ​​different tables, the necessary tables were found intuitively. Still, programmers think the same way.
Began to export with simple objects. First counterparties. I wrote this SQL query:
ELMA_export_contragents.sql
SELECT DISTINCT [ELMA].[dbo].[Contractor].[Name] AS [crm_management_contragent_name] ,[LF].[LongName] AS [crm_management_contragent_opf] ,[ELMA].[dbo].[ContractorType].[Name] AS [crm_management_contragent_type] ,[Site] AS [crm_management_contragent_url] ,[crm_management_contragent_email] = STUFF(( SELECT '|' + [ELMA].[dbo].[Email].[EmailString] FROM [ELMA].[dbo].[Contractor_Email] LEFT OUTER JOIN [ELMA].[dbo].[Email] ON [ELMA].[dbo].[Email].[Id] = [ELMA].[dbo].[Contractor_Email].[Email] WHERE [ELMA].[dbo].[Contractor_Email].[Contractor] = [ELMA].[dbo].[Contractor].[Id] FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') ,[Fax] AS [crm_management_contragent_fax] ,[crm_management_contragent_phone] = STUFF(( SELECT '|' + [ELMA].[dbo].[Phone].[PhoneString] FROM [ELMA].[dbo].[Contractor_Phone] LEFT OUTER JOIN [ELMA].[dbo].[Phone] ON [ELMA].[dbo].[Phone].[Id] = [ELMA].[dbo].[Contractor_Phone].[Phone] WHERE [ELMA].[dbo].[Contractor_Phone].[Contractor] = [ELMA].[dbo].[Contractor].[Id] FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') ,[LAC].[Name] AS [crm_management_contragent_legal_country] ,[LA].[Region] AS [crm_management_contragent_legal_region] ,[LA].[District] AS [crm_management_contragent_legal_district] ,[LA].[City] AS [crm_management_contragent_legal_city] ,[LA].[Locality] AS [crm_management_contragent_legal_locality] ,[LA].[Street] AS [crm_management_contragent_legal_street] ,[LA].[Building] AS [crm_management_contragent_legal_house] ,[LA].[Stroenie] AS [crm_management_contragent_legal_building] ,[LA].[Korpus] AS [crm_management_contragent_legal_corp] ,[LA].[Appartment] AS [crm_management_contragent_legal_apartment] ,[LA].[Zip] AS [crm_management_contragent_legal_postcode] ,[PAC].[Name] AS [crm_management_contragent_post_country] ,[PA].[Region] AS [crm_management_contragent_post_region] ,[PA].[District] AS [crm_management_contragent_post_district] ,[PA].[City] AS [crm_management_contragent_post_city] ,[PA].[Locality] AS [crm_management_contragent_post_locality] ,[PA].[Street] AS [crm_management_contragent_post_street] ,[PA].[Building] AS [crm_management_contragent_post_house] ,[PA].[Stroenie] AS [crm_management_contragent_post_building] ,[PA].[Korpus] AS [crm_management_contragent_post_corp] ,[PA].[Appartment] AS [crm_management_contragent_post_apartment] ,[PA].[Zip] AS [crm_management_contragent_post_postcode] ,[INN] AS [crm_management_contragent_inn] ,[ELMA].[dbo].[ContractorLegal].[KPP] AS [crm_management_contragent_kpp] ,[ELMA].[dbo].[ContractorLegal].[OGRN] AS [crm_management_contragent_ogrn] ,[ELMA].[dbo].[ContractorLegal].[BIK] AS [crm_management_contragent_bik] ,[ELMA].[dbo].[ContractorLegal].[BANK] AS [crm_management_contragent_bank] ,[ELMA].[dbo].[ContractorLegal].[KS] AS [crm_management_contragent_ks] ,[ELMA].[dbo].[ContractorLegal].[RS] AS [crm_management_contragent_rs] FROM [ELMA].[dbo].[Contractor] LEFT OUTER JOIN [ELMA].[dbo].[ContractorType] ON [ELMA].[dbo].[ContractorType].[Id] = [ELMA].[dbo].[Contractor].[Type] LEFT OUTER JOIN [ELMA].[dbo].[ContractorLegal] ON [ELMA].[dbo].[ContractorLegal].[Id] = [ELMA].[dbo].[Contractor].[Id] LEFT OUTER JOIN [ELMA].[dbo].[Address] AS [LA] ON [LA].[Id] = [ELMA].[dbo].[Contractor].[LegalAddress] LEFT OUTER JOIN [ELMA].[dbo].[Country] AS [LAC] ON [LAC].[Id] = [LA].[Country] LEFT OUTER JOIN [ELMA].[dbo].[Address] AS [PA] ON [PA].[Id] = [ELMA].[dbo].[Contractor].[PostalAddress] LEFT OUTER JOIN [ELMA].[dbo].[Country] AS [PAC] ON [PAC].[Id] = [PA].[Country] LEFT OUTER JOIN [ELMA].[dbo].[LegalForm] AS [LF] ON [LF].[Id] = [ELMA].[dbo].[ContractorLegal].[LegalForm] WHERE [ELMA].[dbo].[Contractor].[IsDeleted] = 0 AND [ELMA].[dbo].[Contractor].[Name] IS NOT NULL AND [ELMA].[dbo].[Contractor].[id] > 500 Multiple values ​​for one attribute are combined into one using STUFF (...). The result of the query is saved in csv format with a separator ";". The first line in the file added attribute notation in easla.com . Uploaded file in the section "Import Objects". Then he specified the encoding of the file and the user on whose behalf to perform the import. Launched import. The import procedure is not very fast, it can take about one second to create each object. The number of imported records was about 500. It looked like this:
Now contacts. I wrote this SQL query:
ELMA_export_contacts.sql
SELECT [Surname] AS [crm_management_contact_lastname] ,[Firstname] AS [crm_management_contact_firstname] ,[Middlename] AS [crm_management_contact_middlename] ,[ELMA].[dbo].[Contractor].[Name] AS [crm_management_contact_contragent] ,[Department] AS [cnt_management_contact_department] ,[Position] AS [cnt_management_contact_position] ,[crm_management_contact_email] = STUFF(( SELECT '|' + [ELMA].[dbo].[Email].[EmailString] FROM [ELMA].[dbo].[Contact_Email] LEFT OUTER JOIN [ELMA].[dbo].[Email] ON [ELMA].[dbo].[Email].[Id] = [ELMA].[dbo].[Contact_Email].[Email] WHERE [ELMA].[dbo].[Contact_Email].[Contact] = [ELMA].[dbo].[Contact].[Id] FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') ,[crm_management_contact_phone] = STUFF(( SELECT '|' + [ELMA].[dbo].[Phone].[PhoneString] FROM [ELMA].[dbo].[Contact_Phone] LEFT OUTER JOIN [ELMA].[dbo].[Phone] ON [ELMA].[dbo].[Phone].[Id] = [ELMA].[dbo].[Contact_Phone].[Phone] WHERE [ELMA].[dbo].[Contact_Phone].[Contact] = [ELMA].[dbo].[Contact].[Id] FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') ,CASE WHEN [Year] IS NULL THEN NULL ELSE CONVERT(varchar, [Birthday],104) END AS [crm_management_contact_birthday] ,[RAC].[Name] AS [crm_management_contact_country] ,[RA].[Region] AS [crm_management_contact_region] ,[RA].[District] AS [crm_management_contact_district] ,[RA].[City] AS [crm_management_contact_city] ,[RA].[Locality] AS [crm_management_contact_locality] ,[RA].[Street] AS [crm_management_contact_street] ,[RA].[Building] AS [crm_management_contact_house] ,[RA].[Stroenie] AS [crm_management_contact_building] ,[RA].[Korpus] AS [crm_management_contact_corp] ,[RA].[Appartment] AS [crm_management_contact_apartment] ,[RA].[Zip] AS [crm_management_contact_postcode] FROM [ELMA].[dbo].[Contact] LEFT OUTER JOIN [ELMA].[dbo].[Contractor] ON [ELMA].[dbo].[Contractor].[Id] = [ELMA].[dbo].[Contact].[Contractor] LEFT OUTER JOIN [ELMA].[dbo].[Address] AS [RA] ON [RA].[Id] = [ELMA].[dbo].[Contact].[RegistrationAddress] LEFT OUTER JOIN [ELMA].[dbo].[Country] AS [RAC] ON [RAC].[Id] = [RA].[Country] WHERE [ELMA].[dbo].[Contact].[Id] > 1919 All actions are similar to the previous import. The peculiarity of importing contacts after counterparties was that the contact possesses the attribute "Counterparty", which refers to the counterparty. Therefore, contractors were imported first, followed by contacts. When importing contacts in the "Counterparty" field, easla.com found and substituted links to counterparties ! The number of records imported was approximately 1,800.
After importing counterparties and contacts, a structured database was obtained in which all contacts belong to certain counterparties. It took about 2 hours to import the data. And half the time spent on developing SQL queries. As a result, I got two ready and interrelated tables.
Counterparty Registry:
Contact register:
Next in line was the import of contracts. Unloaded them using this SQL query:
ELMA_export_contracts.sql
SELECT [RegYear] AS [agr_management_contract_year] ,[RegNum] AS [agr_management_contract_num] ,[RegAppendixFront] AS [agr_management_contract_appendix_front] ,[RegAppendixBack] AS [agr_management_contract_appendix_back] ,[RegCode] ,[ELMA].[dbo].[Contractor].[Name] AS [agr_management_contract_contragent] ,'"'+REPLACE(REPLACE(REPLACE([Title],CHAR(10),''),CHAR(13),''),';',',')+'"' AS [agr_management_contract_title] ,CONVERT(varchar, [RegDate],104) AS [agr_management_contract_reg_date] ,CONVERT(varchar, [SignDate],104) AS [agr_management_contract_sign_date] ,[PM1].[EMail] AS [agr_management_contract_project_manager] ,[PM2].[EMail] AS [agr_management_contract_project_manager_helper] ,agr_management_contract_fields = STUFF(( SELECT '|' + [F1].[Title] FROM [ELMA].[dbo].[Project_Fields] LEFT OUTER JOIN [ELMA].[dbo].[Field] AS [F1] ON [F1].[Id] = [ELMA].[dbo].[Project_Fields].[Field] WHERE [ELMA].[dbo].[Project_Fields].[Parent] = [ELMA].[dbo].[Project].[Id] FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') FROM [ELMA].[dbo].[Project] LEFT JOIN [ELMA].[dbo].[Contractor] ON [ELMA].[dbo].[Contractor].[Id] = [ELMA].[dbo].[Project].[Contragent] LEFT JOIN [ELMA].[dbo].[User] AS [PM1] ON [PM1].[Id] = [ELMA].[dbo].[Project].[ProjectManager] LEFT JOIN [ELMA].[dbo].[User] AS [PM2] ON [PM2].[Id] = [ELMA].[dbo].[Project].[ProjectManagerHelper] All actions are also similar to previous imports. And just like with contacts, all contracts having the attribute "Counterparty" during the import contacted the corresponding counterparties. The number of records imported was a little over 200.
Now, when all contractors, contacts and contracts referred to by incoming and outgoing documents were in easla.com , it was possible to proceed with the import of correspondence.
The number of incoming and outgoing letters was large - approximately 5400 and 5300, respectively.
It is interesting that incoming and outgoing letters refer to each other, i.e. Outgoing can be sent in response to incoming and information about this is stored in the corresponding attribute of the outgoing document. The incoming document can be received in response to the outgoing document and information about it is stored in the corresponding attribute of the incoming document. Thus, when importing one of the object types, it is impossible to ensure the formation of all links. The first imported documents were imported, but before that, the type of the attribute that stores the link to the outgoing document was changed from “Object” to “String”. This made it possible to save in the attribute a description of the outgoing document that is not yet in the database and use it later, after importing outgoing documents. Ultimately, this is a SQL query for uploading incoming documents:
ELMA_export_incomings.sql
SELECT CASE [DocMethod] WHEN 1 THEN '. ' WHEN 2 THEN ' ' WHEN 3 THEN '' WHEN 4 THEN ' ' END AS [crs_management_incoming_method] ,[ELMA].[dbo].[Contractor].[Name] AS [crs_management_incoming_contragent] ,[ELMA].[dbo].[Contact].[Surname] + ' ' + SUBSTRING([ELMA].[dbo].[Contact].[Firstname],1,1) + '.' + SUBSTRING([ELMA].[dbo].[Contact].[Middlename],1,1) + '.' AS [crs_management_incoming_contact] ,[crs_management_incoming_performers] = STUFF(( SELECT '|' + [C1].[Surname] + ' ' + SUBSTRING([C1].[Firstname],1,1) + '.' + SUBSTRING([C1].[Middlename],1,1) + '.' FROM [ELMA].[dbo].[IncomingDoc_Performers] LEFT OUTER JOIN [ELMA].[dbo].[Contact] AS [C1] ON [C1].[Id] = [ELMA].[dbo].[IncomingDoc_Performers].[Performer] WHERE [ELMA].[dbo].[IncomingDoc_Performers].[Parent] = [ELMA].[dbo].[IncomingDoc].[Id] FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') ,CONVERT(varchar, [ELMA].[dbo].[IncomingDoc].[ContragentDate], 104) AS [crs_management_incoming_contragent_date] ,[ELMA].[dbo].[IncomingDoc].[ContragentRegNum] AS [crs_management_incoming_contragent_regnum] ,CONVERT(VARCHAR,[ELMA].[dbo].[IncomingDoc].[ReceiveDate],20) AS [crs_management_incoming_receive_date] ,[ELMA].[dbo].[IncomingDoc].[ReceiveOriginalDate] AS [crs_management_incoming_receive_original_date] ,[ReceiveOriginalDateFlag] AS [crs_management_incoming_receive_original_flag] ,[ELMA].[dbo].[IncomingDoc].[ReceiveRegNum] AS [crs_management_incoming_receive_regnum] ,REPLACE(REPLACE([ELMA].[dbo].[IncomingDoc].[Subj],CHAR(13),''),CHAR(10),'') AS [crs_management_incoming_subj] ,CASE [ELMA].[dbo].[IncomingDoc].[DocType] WHEN 0 THEN '' WHEN 1 THEN '' WHEN 2 THEN ' ' WHEN 3 THEN ' ' WHEN 4 THEN ' ' WHEN 5 THEN ' ' WHEN 6 THEN ' ' WHEN 7 THEN ' ' WHEN 8 THEN ' ' WHEN 9 THEN '' WHEN 13 THEN '' WHEN 14 THEN '' WHEN 17 THEN '' WHEN 15 THEN '' WHEN 10 THEN '' WHEN 18 THEN ' ' WHEN 11 THEN ' ' WHEN 12 THEN ' ' WHEN 16 THEN ' ' WHEN 99 THEN '' END AS [crs_management_incoming_content] ,[U1].[UserName]+'@sngp.ru' AS [crs_management_incoming_to] ,[U2].[UserName]+'@sngp.ru' AS [crs_management_incoming_forwardto] ,[ELMA].[dbo].[OutgoingDoc].[RegNum] AS [crs_management_incoming_outgoing] ,[ELMA].[dbo].[Project].[RegCode] AS [crs_management_incoming_contract] ,[crs_management_incoming_attachments] = STUFF(( SELECT '|' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(STR([F0].[Id]) + ' ' + [F0].[Name]),'«',''),'»',''),'–','-'),'.zip.zip','.zip'),'.docx','.docx'),' ',' ') FROM [ELMA].[dbo].[IncomingDoc_DocAttachments] LEFT OUTER JOIN [ELMA].[dbo].[FS_FILES] AS [F0] ON [F0].[Uid] = [ELMA].[dbo].[IncomingDoc_DocAttachments].[DocAttachment] WHERE [ELMA].[dbo].[IncomingDoc_DocAttachments].[Parent] = [ELMA].[dbo].[IncomingDoc].[Id] FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') ,LTRIM(STR(ISNULL([F1].[Id],[F2].[Id]))) + REVERSE(SUBSTRING(REVERSE(ISNULL([F1].[Name],[F2].[Name])), 0, CHARINDEX('.',REVERSE(ISNULL([F1].[Name],[F2].[Name]))) + 1)) AS [crs_management_incoming_document] ,[S].[Name] AS [status] FROM [ELMA].[dbo].[IncomingDoc] LEFT OUTER JOIN [ELMA].[dbo].[Contractor] ON [ELMA].[dbo].[Contractor].[Id] = [ELMA].[dbo].[IncomingDoc].[Contragent] LEFT OUTER JOIN [ELMA].[dbo].[Contact] ON [ELMA].[dbo].[Contact].[Id] = [ELMA].[dbo].[IncomingDoc].[Contact] LEFT JOIN [ELMA].[dbo].[User] AS [U1] ON [U1].[Id] = [ELMA].[dbo].[IncomingDoc].[To] LEFT JOIN [ELMA].[dbo].[User] AS [U2] ON [U2].[Id] = [ELMA].[dbo].[IncomingDoc].[ForwardTo] LEFT JOIN [ELMA].[dbo].[Project] ON [ELMA].[dbo].[Project].[Id] = [ELMA].[dbo].[IncomingDoc].[Project] LEFT JOIN [ELMA].[dbo].[OutgoingDoc] ON [ELMA].[dbo].[OutgoingDoc].[Id] = [ELMA].[dbo].[IncomingDoc].[OutgoingDoc] LEFT JOIN [ELMA].[dbo].[DocumentVersion] AS [DV1] ON [DV1].[DocumentId] = [ELMA].[dbo].[IncomingDoc].[Id] AND [DV1].[Status] = 2 AND ISNUMERIC([DV1].[File]) = 1 LEFT JOIN [ELMA].[dbo].[DocumentVersion] AS [DV2] ON [DV2].[DocumentId] = [ELMA].[dbo].[IncomingDoc].[Id] AND [DV2].[Status] = 2 AND ISNUMERIC([DV1].[File]) = 0 LEFT JOIN [ELMA].[dbo].[FS_FILES] AS [F1] ON [F1].[Id] = [DV1].[File] LEFT JOIN [ELMA].[dbo].[FS_FILES] AS [F2] ON [F2].[Uid] = [DV2].[File] LEFT JOIN [ELMA].[dbo].[Document] ON [ELMA].[dbo].[Document].[Id] = [ELMA].[dbo].[IncomingDoc].[Id] LEFT JOIN [ELMA].[dbo].[LifeCycleStatus] AS [S] ON [S].[Id] = [ELMA].[dbo].[Document].[Status] WHERE --[ELMA].[dbo].[IncomingDoc].[Subj] NOT LIKE '%' AND [ELMA].[dbo].[IncomingDoc].[Subj] NOT LIKE 'test%' [ELMA].[dbo].[IncomingDoc].[Id] > 12193 ORDER BY [ELMA].[dbo].[IncomingDoc].[ReceiveDate] By the way, the situation was complicated by the fact that along with the data it was necessary to upload and import all files.
It was completely unexpected that the elma developers at some point changed the way they stored files, so I had to create two different requests and merge them together to upload files. There was a problem with the first unloading attempt. The names of some files were repeated and overwritten each other. I had to change the name of the final file in the request, adding id to it. In addition, in fact, the file and its name needed to be transferred to easla.com and additional data stored with the file. They also had to be entered in the file name. Thus, the final file name was formed by the principle: id data filename.ext. Since elma stores files in a separate directory, the result of the query was planned to be converted into a bat file to upload files to the right place. Ultimately, to upload the files of incoming documents, the following query appeared:
ELMA_export_incoming_files.sql
SELECT 'copy "\\ssv11\c$\ELMA3-Standart.cfg\UserConfig\Files\' + [FNAME] + '" "c:\temp\elma\incoming\' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL([ENDNAME],[FNAME]),'«',''),'»',''),'–','-'),'.zip.zip','.zip'),'.docx','.docx'),' ',' ') + '"' FROM ( SELECT [ELMA].[dbo].[IncomingDoc].[Id] AS [Id], [ELMA].[dbo].[IncomingDoc].[Subj] AS [Subj], [ELMA].[dbo].[IncomingDoc].[ReceiveDate] AS [RD], LTRIM(STR([F0].[Id])) + REVERSE(SUBSTRING(REVERSE([F0].[Name]), 0, CHARINDEX('.',REVERSE([F0].[Name])) + 1)) AS [FNAME], LTRIM(STR([F0].[Id]) + ' ' + [Name]) AS [ENDNAME] FROM [ELMA].[dbo].[IncomingDoc] LEFT JOIN [ELMA].[dbo].[IncomingDoc_DocAttachments] ON [ELMA].[dbo].[IncomingDoc_DocAttachments].[Parent] = [ELMA].[dbo].[IncomingDoc].[Id] LEFT OUTER JOIN [ELMA].[dbo].[FS_FILES] AS [F0] ON [F0].[Uid] = [ELMA].[dbo].[IncomingDoc_DocAttachments].[DocAttachment] UNION SELECT [ELMA].[dbo].[IncomingDoc].[Id] AS [Id], [ELMA].[dbo].[IncomingDoc].[Subj] AS [Subj], [ELMA].[dbo].[IncomingDoc].[ReceiveDate] AS [RD], LTRIM(STR(ISNULL([F1].[Id],[F2].[Id]))) + REVERSE(SUBSTRING(REVERSE(ISNULL([F1].[Name],[F2].[Name])), 0, CHARINDEX('.',REVERSE(ISNULL([F1].[Name],[F2].[Name]))) + 1)) AS [FNAME], NULL AS [ENDNAME] FROM [ELMA].[dbo].[IncomingDoc] LEFT JOIN [ELMA].[dbo].[DocumentVersion] AS [DV1] ON [DV1].[DocumentId] = [ELMA].[dbo].[IncomingDoc].[Id] AND [DV1].[Status] = 2 AND ISNUMERIC([DV1].[File]) = 1 LEFT JOIN [ELMA].[dbo].[DocumentVersion] AS [DV2] ON [DV2].[DocumentId] = [ELMA].[dbo].[IncomingDoc].[Id] AND [DV2].[Status] = 2 AND ISNUMERIC([DV1].[File]) = 0 LEFT JOIN [ELMA].[dbo].[FS_FILES] AS [F1] ON [F1].[Id] = [DV1].[File] LEFT JOIN [ELMA].[dbo].[FS_FILES] AS [F2] ON [F2].[Uid] = [DV2].[File] ) AS [F] WHERE [FNAME] IS NOT NULL ORDER BY [RD] Loaded csv file, downloaded all incoming files. The procedure was long, because The total file size was several gigabytes. It's good that importing data by 1000 records, since no lining has not done. Be sure to pop up in every thousand of 10-15 errors. It was possible to identify errors before the import procedure, using a preview. Very comfortably. In general, every thousand required advance preparation.
In the same way imported all outgoing documents.
After that, in the objects was written the procedure for converting file names. She deleted the id, and data, transferred to the revdata file in easla.com . Thus, the application files received the original names filename.ext.
function updateAttachmentsFileName() { $files = cobjectref()->attributeref('crs_management_incoming_attachments')->availableFiles(); foreach ($files as $f) { $parts = explode(' ', $f->nowname, 2); if (count($parts) == 2) { $f->nowname = $parts[1]; $f->save(); } } } A procedure was written especially for incoming letters, which replaced descriptions of references to outgoing documents with their identifiers.
function updateOutgoing() { if (empty(cobjectref()->attributeref('crs_management_incoming_outgoing')->value)) return; $outgoing_doc = selectAll( 'crs_management', 'crs_management_outgoing', array(), array('crs_management_outgoing_regnum'=>cobjectref()->attributeref('crs_management_incoming_outgoing')->value) ); if (empty($outgoing_doc)) return; cobjectref()->attributeref('crs_management_incoming_outgoing')->value = $outgoing_doc['id']; } Checking the work of these procedures launched a batch update of objects. Slowly but surely easla.com updated all the objects! After that, changed the attribute type referencing the outgoing document in the incoming document from “String” to “Object” so that the object identifiers become objects.
Thus, easla.com renamed files and organized cross-references between incoming and outgoing documents!
In general, the export and import procedure took about 3 working days. Download files on easla.com carried out in the evenings, when the Internet channel was free. Importing data by 1000 was also convenient because old letters could be imported immediately, without worrying about data integrity. And just before the “combat” launch I downloaded and imported the remnants - the last couple of hundred letters.
Migration was carried out at the end of 2014, and launched into operation all the described business processes on January 12, 2015.
More than a year has passed. The total amount of letters exceeded 20 thousand. The number of tasks exceeded 10 thousand. Here is the latest data from the main menu:
Results
I can say with confidence that a year ago I did everything right. Here are the main benefits:
- The time for registering letters has been reduced to seconds or minutes.
- The time for sending an outgoing letter has been reduced from 30-40 minutes to seconds
- Now you can place outgoing email files on the easla.com file sharing service.
- Developing a letter has become easier, because Now the file in docx format is stored in the easla.com object, and not anywhere in the user
- Communication tasks and letters allows you to generate and upload complex reports that bring the organization a profit
- The contract management process was developed, if earlier it was a simple catalog, now it is a real process with statuses, execution control, signing of acts and receipt of funds.
At the moment, other processes have also been developed: management of tasks, coordination, subcontract agreements. All that was previously feared to do at elma is now done at easla.com !
PS Please do not regard the article as elma anti-advertising. The system did not suit us, but it may well suit others, if they are not “squeezing the juice out of it,” as we are.
Source: https://habr.com/ru/post/282662/
All Articles