MProof Clientele ITSM is a versatile software package based on a SQL database for managing service calls, tasks, products etc. It just missed a label print function.
|
| |
| - | Print labels from any find form within Clientele ITSM related to the selected line |
| - | Add your own SQL query for each find form |
| - | Add your own ZPL coded label for each find form |
| - | Sends labels by FTP to Zebra printer (no printer installation nessesary) |
| - | Uses a read-only database account for accessing data |
| - | View the query result before printing |
| - | Print up to 25 labels at once |
| - | Optimized to work in conjunction with MProof Clientele ITSM 2018.1 (using Shell Command in menu's and toolbars) |
| - | Tested with Zebra ZM400, ZT410 and QLN420 printers |
| - | Temporary alternative label printer setting |
| - | CLP window opens on top of the Clientele window |
|
| |
For managing service requests, change requests and incidents we use the MProof Clientele ITSM software package. It has very nice configurable dashboards, planning tools, an online portal, an answer book, etc, etc. Based on an SQL database it is a versatile tool with many options to adapt to your special needs. We even (mis)use it as a sparepart managing system. For more information take a look at https://www.mproof.nl/.
And yet ... it did mis the label print function we needed for labeling our spareparts. As we updated our version in 2018 I checked all new options in the help files and found a very promising feature: from the configurable menu's and taskbars you can start an external application with the active content as commandline parameters.
This means that if e.g. an item is currently opened or selected in Clientele ITSM the parameters for the external program would be "ItemID" and the corresponding Unique Identifier (UID) from the product. Using these two parameters I could extract the product information from the SQL database.
The same can be done for products and support calls (incidents, service requests, change requests). So why limit myself to labeling spareparts using the item data if I could also label products and support calls (the latter comes in handy when have a product brought in for repair or when we are collection materials for a change request).
Browsing through the AutoHotkey forum I found a way to access a database and read information from it using a query. As I wanted to make things configurable I wanted the queries in some form of configuration file. The INI configuration files I use for most applications are not suitable for holding queries. So I decided I wanted a configuration file in XML format. An XML file can hold multi-line fields and data can be extracted quickly due to its structure. On the AutoHotkey forum there are plenty of examples how to handle XML files.
To access the database I use a read-only account with a static password. Depending on the commandline the UID is invoked in a query for support calls, products or items. The database returns the data requested by the query. This data is displayed in the label print application.
The XML file not only contains a query for each column the parameters from Clientle ITSM refer to but also as many label formats in Zebra ZPL code. When pressing the print button the data from the database is invoked in the ZPL code, saved as a temporary file and send to a Zebra printer by FTP.
To limit the amount of data neccesary for the labels a prefix and suffix can be configured. These are added at the beginning and ending of each label format when a label is printed. We use this for adding the company logo to all label formats. See the example of a ZPL label code in the XML file below and the ZPL manual in the downloads. You can create your own label format at http://labelary.com/viewer.html, it is not completely accurate but can help a lot.
 |
| ClienteleLabelPrint label example for a support call |
If all configuration is done you can deploy ClienteleLabelPrint within Clientele ITSM. Users then get automatically the additional files the first time they login to the client software. Don't forget to create a link to ClienteleLabelPrint on a taskbar or menu using the commandline options as mentioned below.
|
| |
 |
 |
| ClienteleLabelPrint screenshot |
ClienteleLabelPrint configuration |
|
| |
CLP.exe [CurrentID] [CurrentIDColumnName]
[CurrentID] = The unique identifier of a selected record
[CurrentIDColumnName] = The name of the [CurrentID] column
|
| |
<ClienteleLabelPrint>
<ApplicationSettings>
<Password>
Encoded password for settings (can only be set from within application)
</Password>
<ClienteleWindowTitle>
First part of the Clientele ITSM window title (without username portion)
</ClienteleWindowTitle>
</ApplicationSettings>
<DatabaseSettings>
<Source>
IP address or hostname of database server
</Source>
<Catalog>
Clientele ITSM Application database catalog name (e.g. Clientele_ITSM_Prod_Application)
</Catalog>
<Username>
Username of a read-only database account
</Username>
<Password>
Encoded password of the read-only database account (can only be set from within application)
</Password>
</DatabaseSettings>
<PrinterSettings>
<Printer>
IP address or hostname of the preffered Zebra label printer
</Printer>
<Username>
Username (if any) for the printers FTP server
</Username>
<Password>
Encoded password (if any) for the printers FTP server (can only be set from within application)
</Password>
</PrinterSettings>
<Queries>
<ColumnName without ID>
Your query for the ColumnName (1)
</ColumnName without ID>
<...
...>
</Queries>
<Labels>
<ColumnName without ID>
Your ZPL label code for the ColumnName (2)
</ColumnName without ID>
<...
...>
<LabelPrefix>
Prefix ZPL code portion for any label (can be used to specify a general format for all labels)
</LabelPrefix>
<LabelSuffix>
Suffix ZPL code portion for any label (can be used to specify a general format for all labels)
</LabelSuffix>
</Labels>
</ClienteleLabelPrint>
|
(1): within the SQL queries the string {ITSM_ID} will be replaced with the actual value
(2): within the ZPL the strings {ColumnName} will be replaced with the actual values where ColumnName can be any column that is present in the query result
|
| |
Query example in the XML configuration file:
<ClienteleLabelPrint>
<Queries>
<Item>
SELECT
dbo.Items.ItemID,
dbo.Items.Code,
dbo.Items.Name,
dbo.Items.Description,
dbo.Items.DetailedDescription,
dbo.LineOfBusiness.Name AS Category,
Organization_1.Name AS Supplier,
dbo.Organization.Name AS Manufacturer,
AV_4.ValueShortText AS ManufacturerPartNo,
CAST(dbo.ItemsPrice.Cost AS decimal(18, 2)) AS Costs,
AV_3.ValueShortText AS Location,
AV_2.ValueShortText AS LocationDetail,
FLOOR(AV_1.ValueDecimal) AS CurrentQty,
CONVERT(date, dbo.AttributeValue.ValueDateTime) AS OnDate,
FLOOR(AV_5.ValueDecimal) AS MinimumStockQty,
CASE When AV_7.ValueBoolean = 'True' then 'Y' ELSE 'N' END AS LowStock
FROM
dbo.Items LEFT OUTER JOIN
dbo.AttributeValue ON dbo.Items.ItemID = dbo.AttributeValue.ParentID
AND dbo.AttributeValue.AttributeBoundColumnID = 'C022D47B-8AF1-43FF-9E16-17C982661EC2' LEFT OUTER JOIN
dbo.AttributeValue AS AV_7 ON dbo.Items.ItemID = AV_7.ParentID
AND AV_7.AttributeBoundColumnID = '0935057d-6cff-4fa9-9420-6b63bc561c00' LEFT OUTER JOIN
dbo.AttributeValue AS AV_6 ON dbo.Items.ItemID = AV_6.ParentID
AND AV_6.AttributeBoundColumnID = '268c3401-5b9e-4640-adcf-41aefb46b9a8' LEFT OUTER JOIN
dbo.AttributeValue AS AV_5 ON dbo.Items.ItemID = AV_5.ParentID
AND AV_5.AttributeBoundColumnID = '2a770a71-02a3-4e00-82bf-f4345c1fc354' LEFT OUTER JOIN
dbo.AttributeValue AS AV_4 ON dbo.Items.ItemID = AV_4.ParentID
AND AV_4.AttributeBoundColumnID = '3fb820e7-8b6d-4478-aa72-62598ba6f4e5' LEFT OUTER JOIN
dbo.AttributeValue AS AV_3 ON dbo.Items.ItemID = AV_3.ParentID
AND AV_3.AttributeBoundColumnID = '2f4f4f72-14b6-4447-aa0d-582da0f3b9bd' LEFT OUTER JOIN
dbo.AttributeValue AS AV_2 ON dbo.Items.ItemID = AV_2.ParentID
AND AV_2.AttributeBoundColumnID = 'A718E51E-36CC-4E73-9D16-50BBEB38F5FB' LEFT OUTER JOIN
dbo.AttributeValue AS AV_1 ON dbo.Items.ItemID = AV_1.ParentID
AND AV_1.AttributeBoundColumnID = 'F9DAE5C6-5784-41E3-BC55-8FCF2CFE6EA1' LEFT OUTER JOIN
dbo.LineOfBusiness ON dbo.Items.LineOfBusinessID = dbo.LineOfBusiness.LineOfBusinessID LEFT OUTER JOIN
dbo.Organization ON dbo.Items.PartyManufacturerID = dbo.Organization.OrganizationID LEFT OUTER JOIN
dbo.Organization AS Organization_1 ON dbo.Items.PartySupplierID = Organization_1.OrganizationID LEFT OUTER JOIN
dbo.ItemsPrice ON dbo.ItemsPrice.ItemID = dbo.Items.ItemID
WHERE dbo.Items.ItemID = '{ITSM_ID}'
</Item>
</Queries>
</ClienteleLabelPrint>
|
Note the {ITSM_ID} string at the WHERE statement which will be replaced with the UID from the commandline.
This query is an example only as we use specific attributes only valid for our system.
|
| |
ZPL label code example in the XML configuration file:
<ClienteleLabelPrint>
<Labels>
<Item>
^FO100,10^AQN,20,12^FDItem^FS
^FO100,35^FR^APN,60,60^FD{Name}^FS
^FO100,95^ARN,20,10^FB600,2,0,L,0^FD{Description}^FS
^PW440
^FO100,160^AQN,10,10^FDSupplier: {Supplier}^FS
^FO100,185^AQN,10,10^FDManufac: {Manufacturer}^FS
^FO100,210^AQN,20,12^FDLocation: {Location}^FS
^PW800
^FO450,160^AQN,10,10^FDCode: {Code}^FS
^FO450,185^AQN,10,10^FDPartNo: {ManufacturerPartNo}^FS
^FO450,210^AQN,10,10^FDDetail: {LocationDetail}^FS
^BY2
^FO100,250^ARN^B3N,N,75,Y,N^FD>:{Name}^FS
</Item>
<Prefix>
^XA
^FPH ^FX Label horizontal
^PW800 ^FX Label width in px
^LH20,40 ^FX Label home x,y
^GB760,360,3,B,0^FS ^FX Square w,h,line,b/w
</Prefix>
<Suffix>
^FO3,3^GFA,4248,4248,12,
FFFFFFFFFFFFFFFFFFFFFF80
FFFFFFFFFFFFFFFFFFFFFF80
FFFFFFFFFFFFFFFFFFFFFF80
FFFFFFFF9FFFFFFFFFFFFF80
FFFFFFFF1FFFFFFFFFFFFF80
FFFFFFE01FFFFFFFFFFFFF80
FFFFFFE03FFFFFFFFFFFFF80
...
FFFFFFFFFFFFFFFFFFFFFF80
FFFFFFFFFFFFFFFFFFFFFF80
FFFFFFFFFFFFFFFFFFFFFF80
^FS^XZ
</Suffix>
</Labels>
</ClienteleLabelPrint>
|
Note the strings in in parentheses {***} will be replaced with the corresponding columns from the query output.
The data at the bottom is graphic data representing a company logo. It is for the most part cut out to keep the example readable.
|
| |
| v1.0 |
Initial setup |
| v1.1 |
Removed configuration window (to prevent users to change configuration) |
|
Combined INI, SQL and ZPL configuration files within a single XML file |
| v1.1b |
Bugfix 02 moved temporary file from A_ScriptDir to A_Temp |
| v1.2 |
Removed substitution of the string {itsm_Column} within queries (not needed) |
|
Re-enabled configuration window (needed to add encoded passwords to the XML settings) |
|
Added password for opening configuration (default = sysman) |
|
Window now opens on top of the Clientele window instead of last known position |
|
No need for CLP.dll anymore, graphics are saved in the CLP.exe
|
|
Bug fix 01: Added additional check in XML handling |
|
| |
| Bug no. |
Version |
Fixed in |
Description |
| 01 |
v1.1 |
v1.2 |
Exception occured when XML key not present in settings file |
| 02 |
v1.1 |
v1.1b |
On Windows 10 the temporary label file cannot be written to the Clientele installation folder |
| 03 |
v1.2 |
|
On Windows 10 the XML configuration file cannot be written to the Clientele installation folder.
Use a workaround to set the configuration starting ClienteleLabelPrint from a temporary folder before deploying it in ClienteleITSM. |
|
| |
None planned.
|
| |
Please read the legal notice first, allthough you probably heard it before :)
Copyright 2018 DULMEN.net. All rights reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
- Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
- Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY DULMEN.net ''AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL DULMEN.net OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
The views and conclusions contained in the software and documentation are those of the authors and
should not be interpreted as representing official policies, either expressed or implied, of DULMEN.net.
|
You can download ClienteleLabelPrint as a compressed folder containing all files needed. No need to install the software, just unzip, adapt the configuration file ClienteleLabelPrint.ini and run ClienteleLabelPrint.exe.
ClienteleLabelPrint is free for personal and bussiness use and is free to distribute as is as long as the program is not altered and accompanied with the ClienteleLabelPrint text file.
|
| |
|
