DULMEN.net   Follow me on Facebook Contact DULMEN.net  

ClienteleLabelPrint - Programming@DULMEN.net

ClienteleLabelPrint
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.
 
Features
-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
 
Description
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.
 
Screenshots
ClienteleLabelPrint screenshot ClienteleLabelPrint configuration
 
Commandline options
CLP.exe [CurrentID] [CurrentIDColumnName]

[CurrentID] = The unique identifier of a selected record
[CurrentIDColumnName] = The name of the [CurrentID] column
 
Configuration file settings
<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&gt;:{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.
 
Version history
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
 
Known bugs
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.
 
Future enhancements
None planned.
 
Download
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.

VersionFileSize
v1.2ClienteleLabelPrint_v1.2.zip412,53 KB (422430 bytes)
v1.2ClienteleLabelPrint_v1.2.7z336,99 KB (345080 bytes)
ClienteleLabelPrint_ZPL_Manual.pdf14,36 MB (15060650 bytes)
 
 
Share
DULMEN.net © v8 2018 - Designed & written by Arno van Dulmen