PILOT: We are writing our WorkFlow-configurator. Start
I work in an organization that began to implement software from the ASCON company, to be precise, the ASCON Solution Complex 2013 (hereinafter referred to as the "Complex") + COMPASS. I am administering this "Complex". “Complex” has a three-tier architecture (Client-Application Server-Database Server). The key product of the “Complex” is LOTSMAN: PLM, there is also a set of administration utilities. One of the administration utilities is called the “Pilot WorkFlow Configurator” (hereinafter referred to as the WF Configurator).
Figure 1 - Pilot WorkFlow configurator
During the work, the main disadvantages of the WF configurator were identified:
- There is no Russian-language search by full name, only by login (and only in the form of domain \ login, by the way in the "Complex Control Center", where users are added to the system, there is no search at all ...);
- With a large number of users (> 1K), it began to terribly slow down (about 4 minutes for the user add operation).
Since users had to be added often and a lot, I had to figure out how to optimize this process.
The WF configurator has a lot of functionality, but so far I have only been interested in one - adding users.
To eliminate the expected delays, I decided to work directly with the database, bypassing the "Application Server". After analysis (I had to manually look at the contents of the tables), several tables were defined (wfActors, wfDepartments, wfRoles, wfUserRoles) in which the necessary data is stored. As a result, the procedure for adding a user to the post came down to the fact that you need to add a record to only 1 table (wfUserRoles), which contains records of the form (inIdUser, inIdRole, dtStart, dtEnd):
- inIdUser - user id;
- inIdRole- id of the role (position);
- dtStart - start date;
- dtEnd - end date.
If the start / end date fields are NULL, then the term of the user in the post is unlimited.
The algorithm of actions is as follows:
- We find the user id
select * from wfActors where stDescription like 'Фамилия%' order by stDescription, the result is a table with user id fields, a Russian-language user name. Remember the user id;
- We find which department we need to add the user to
select * from wfDepartments, the result is a table with the fields of the id unit, the name of the unit, and the parent id of the unit. Remember id units;
- We find the position in the desired unit
select * from wfRoles where inIdDepartment = id-подразделения(из пред. шага), the result is a table with the fields of the id-position, id-unit, the name of the position, remember the id-position.
- now we have the user id and position id (in the desired unit), it remains only to add an entry to the table
insert into wfUserRoles values (id-пользователя, id-должности,null,null).
Even executing these SQL queries in Managment Studio, adding the user was faster than through the WF configurator. Then a Python script was written that performed all these actions in the console.
The next step is to develop a GUI for this script, after analyzing which of the GUI libraries there are for Python, I chose PySide.
The following was installed on the home computer:
1) Installed in the virtual machine:
- Windows Server 2008 R2
- MSSQL Server 2008 R2;
- "Complex" ASCON 2013.
2) Installed on the computer:
- Python 3.4.1
- pypyodbc module
- Pyside module.
Once everything is installed, you can begin to develop.
I drew a form for the application in QT Designer and saved it to a file with the .ui extension.
PySide has a great tool pyside-uic.exe, with which you can make a .py file from a ui file. To do this, simply run the following command in the console
pyside-uic.exe file.ui -o file.py. If you specify an argument
-x, then this file.py can be immediately launched and the form drawn in Designer will be displayed. I found out about this parameter (s) a bit later when I already used the instructions from zetcode.com/gui/pysidetutorial , so my code is slightly different from the one generated with
pyside-uic.exe file.ui -x -o file.py.
Here is the form I got:
Figure 2 - My WorkFlow Configurator
Used the pypyodbc library to work with the database, PySide to work with the form.
It turned out 3 files:
- GUI rendering and event binding from the form to various functions;
- class for working with the database
- just a text file from which the initial settings for the form are loaded (and saved when you exit)
To add a user to a position, you must first connect to the base with the “Connect” button (the login and password are registered directly in the program code), the “Organization Structure” tree will appear, you can expand the elements if they have nested ones (1 click) and go to the selected element (double click), then select a position. Find the user by Last Name (“Search” button), select the user found and press the “INSERT” button (the position in the tree and the user in the search results field should be selected).
An interesting point was the construction of a tree, until this moment I had not encountered this. I would like to write somehow easier, but so far I am forming a list of elements.
treeItems= for l in sorted(list1): parent=str(l) itemId=str(l) descr=str(l) treeItem=QtGui.QTreeWidgetItem([descr,itemId,parent]) treeItems.append(treeItem) treeItemsFin= i=1 for item in treeItems: for itemj in treeItems[i:]: if item.text(1)==itemj.text(2): item.insertChild(0,itemj) i+=1 treeItemsFin.append(item) self.treeWid.insertTopLevelItems(0,treeItemsFin)
First, from a regular list (with elements of the form: [id-element, description, parent-id]), I form a list of objects of type QTreeWidgetItem (first cycle), and then from this list I form another list of elements, of type QTreeWidgetItem, but with nesting.
So far I have intentionally refused to display already added users, because it seems to me that this is the problem of the ASCON WF configurator, they have the whole tree updated after each operation and this takes a very long time.
In the future I plan to make the display of users only in the selected branch.
While I use this application only, but other administrators may come in handy ... For the application to work, you need Python, PySide, pypyodbc installed on the administrator’s computer, unlike the ASCON WF-configurator, it is cross-platform.
Of the advantages, the main goals are struck:
+ Russian-language search by users;
+ fast working time.
- Positions are not displayed if they are in the root of the tree (i.e., in none of the units, I plan to finalize);
- already added users are not displayed in the tree (I plan to finalize);
- there is no functionality for creating / deleting units, positions (I plan to finalize);
- there is no functionality of business processes, automatic operations, etc. that is in the ASCON WF-configurator (in the near future I do not plan to add this, because I use it not so often and it seems to work fine).
That's all for now, source code on github.com .
List of sources used