DEV Community: Henry "HammZ" Hamon

Create and Read MS Word documents with ePython

Henry "HammZ" Hamon — Wed, 28 Dec 2022 00:16:00 +0000

In this article, I will show you how one can easily create and read Microsoft Word documents using InterSystems IRIS with the leverage power of embedded Python.

Setup

First things first, let’s install the Python module called python-docx. There are a lot of modules to write MS Word files in Python. However, this one is the easiest one to use.

Just execute the following command on the terminal:

!pip3 install python-docx

If you are working with Docker, like I do, just add the following line to a Dockerfile

ENV PIP_TARGET=${ISC_PACKAGE_INSTALLDIR}/mgr/python RUN pip3 install python-docx

Let’s Get Started!

Now that you have installed the module, let’s create a very simple document. It will be a skeleton for a more complex document afterward.
To create an empty file, we use the following code:

ClassMethod CreateDocument(path as %String) [ Language = python ]
{

from docx import Document

document = Document()

document.save(path + "document.docx")

}

If you execute it, you’ll see that it will create an empty document.

Quick note:

Since python-docx creates .docx files, you don’t have to use MS Word. Both Google Docs and LibreOffice are free alternatives that support .docx files, and they are just as good as the MS Office suite.

After creating an empty document, let’s add headings and subheadings to its structure our document. To do that we will use the add_heading() method.

The add_heading() method accepts two arguments: the first one is the text, and the second one determines the style by level. There are 10 levels: 0 is the biggest one, while 9 is the smallest.

ClassMethod CreateDocument(path as %String) [ Language = python ]
{

from docx import Document

document = Document()
document.add_heading('The Lord of the Donuts', 0)
document.add_heading('best donut from middle earth', 2)
document.save(path + "document.docx")

}

The document.docx until now should look like this:

Adding Images

To add an image to a word document we will use add_picture() method.

The path to the image is passed as the first parameter.

ClassMethod Create(path As %String) [ Language = python ]
{

from docx import Document

document = Document()

document.add_heading('The Lord of the Donuts', 0)

document.add_heading('best donut from middle earth', 2)

document.add_picture(path + "donut.png")

document.save(path + "document.docx")

}

Also, you can specify the width and height of the image. To resize the image, you might have to recalculate the values using dots-per-inch(dpi) format. However, python-docx has an auxiliary class called docx.shared that helps you to convert inches, centimeters, and millimeters.

document.add_picture(path + "donut.png", width=docx.shared.Inches(5), height=docx.shared.Inches(7))

Writing Paragraphs

To begin writing paragraphs, you can use the add_paragraph() method, as we did with headings.

ClassMethod Create(path As %String) [ Language = python ]
{
    from docx import Document

    document = Document()
    document.add_heading('The Lord of the Donuts', 0)
    document.add_heading('best donut from middle earth', 2)
    document.add_picture('/irisrun/repo/assets/donut.png')

    document.add_paragraph('One donut to rule them all \nOne donut to find them \nOne donut to bring them all \nAnd in the darkness bind them')

    document.save(path + "document.docx")

}

Great, but how do we change the font size, style, and color?

I’m glad you asked. We are going to need to add a run.

Run in Word is a sequence of characters, where all of them share the same character formatting.

So, let’s add our paragraph object to a variable, and we add a run to that variable using add_run().

The first parameter is the text, and the second parameter is the style.

You can use such styles as bold, italic, subscript, underline, strike, double_strike, emboss, etc.

Let’s break our paragraph into parts and apply a bold font to all words “One”, whereas for “find them” we will choose italics.

ClassMethod Create(path As %String) [ Language = python ]
{
    from docx import Document

    document = Document()
    document.add_heading('The Lord of the Donuts', 0)
    document.add_heading('best donut from middle earth', 2)
    document.add_picture(path + 'donut.png')

    paragraph = document.add_paragraph('One donut to rule them all, \n')
    paragraph.add_run('One').bold = True
    paragraph.add_run(' donut to ')
    paragraph.add_run('find them,').italic = True
    paragraph.add_run(' One').bold = True
    paragraph.add_run(' donut to bring them all, \nAnd with sugar bind them\n')

    document.save(path + "document.docx")
}

Great! As you can see Runs are small blocks to stylize.
To change the font, you just need to modify the font.name property from the run.

ClassMethod Create(path As %String) [ Language = python ]
{
    from docx import Document

    document = Document()
    document.add_heading('The Lord of the Donuts', 0)
    document.add_heading('best donut from middle earth', 2)
    document.add_picture(path + 'donut.png')

    paragraph = document.add_paragraph('One donut to rule them all, \n')
    paragraph.add_run('One').bold = True
    paragraph.add_run(' donut to ')
    paragraph.add_run('find them,').italic = True
    paragraph.add_run(' One').bold = True
    paragraph.add_run(' donut to bring them all, \nAnd with sugar bind them\n')

    run = paragraph.add_run('In the Land of Sprinkles where the sweetness lie')
    run.font.name = 'Aharoni'

    document.add_heading('How to order', 2)
    document.add_paragraph('Start with a Coating,', style='List Number')
    document.add_paragraph('Pick a Topping,', style='List Number')
    document.add_paragraph('Choose a Drizzle', style='List Number')

    document.save(path + "document.docx")
}

As you can see, the last line of the paragraph is written in a different font. Great, but there is a better way to improve the paragraph. All you need is to create a custom font style. To specify the style type, we need to import the WD_STYLE_TYPE enum from python-docx. Also, remember to import “Pt” class from shared to use it for the font size. After that, you should call the add_style and give a name to this new style. When adding the run to the paragraph, include the custom style name as the second parameter.

ClassMethod Create(path As %String) [ Language = python ]
{
    from docx import Document
    from docx.enum.style import WD_STYLE_TYPE
    from docx.shared import Pt
    from docx.enum.text import WD_ALIGN_PARAGRAPH

    document = Document()
    document.add_heading('The Lord of the Donuts', 0)
    document.add_heading('best donut from middle earth', 2)
    document.add_picture(path + 'donut.png')

    paragraph = document.add_paragraph('One donut to rule them all, \n')
    paragraph.add_run('One').bold = True
    paragraph.add_run(' donut to ')
    paragraph.add_run('find them,').italic = True
    paragraph.add_run(' One').bold = True
    paragraph.add_run(' donut to bring them all, \nAnd with sugar bind them\n')

    font_styles = document.styles
    font_charstyle = font_styles.add_style('customStyle', WD_STYLE_TYPE.CHARACTER)
    font_object = font_charstyle.font
    font_object.size = Pt(15)
    font_object.name = 'Book Antiqua'
    run = paragraph.add_run('In the Land of Sprinkles where the sweetness lie','customStyle')

    document.save(path + "document.docx")
}

At this point the document should look like this:

Creating Lists

If you want to create an ordered list, just add multiple paragraphs and select the style for the List Number as we did before.

        font_styles = document.styles
    font_charstyle = font_styles.add_style('customStyle', WD_STYLE_TYPE.CHARACTER)
    font_object = font_charstyle.font
    font_object.size = Pt(15)
    font_object.name = 'Book Antiqua'
    run = paragraph.add_run('In the Land of Sprinkles where the sweetness lie','customStyle')

    document.add_heading('How to order', 2)
    document.add_paragraph('Start with a Coating,', style='List Number')
    document.add_paragraph('Pick a Topping,', style='List Number')
    document.add_paragraph('Choose a Drizzle', style='List Number')

    document.save(path + "document.docx")

It’s the same thing as Unordered Lists, but the style must use here will be List Bullet

        font_styles = document.styles
    font_charstyle = font_styles.add_style('customStyle', WD_STYLE_TYPE.CHARACTER)
    font_object = font_charstyle.font
    font_object.size = Pt(15)
    font_object.name = 'Book Antiqua'
    run = paragraph.add_run('In the Land of Sprinkles where the sweetness lie','customStyle')

    document.add_heading('How to order', 2)
    document.add_paragraph('Start with a Coating,', style='List Number')
    document.add_paragraph('Powndered Sugar', style='List Bullet')
    document.add_paragraph('Glazed', style='List Bullet')
    document.add_paragraph('Chocolate Icing', style='List Bullet')

    document.add_paragraph('Pick a Topping,', style='List Number')
    document.add_paragraph('Sprinkles Rainbow', style='List Bullet')
    document.add_paragraph('Chopped Peanuts', style='List Bullet')

    document.add_paragraph('Choose a Drizzle', style='List Number')
    document.add_paragraph('Hot fudge', style='List Bullet')
    document.add_paragraph('Marshmalow', style='List Bullet')
    document.add_paragraph('Salted Caramel', style='List Bullet')

    document.save(path + "document.docx")

Before moving on, we will require a fast refactoring of the code to make it look a little bit more "pythonish".

        font_styles = document.styles
    font_charstyle = font_styles.add_style('customStyle', WD_STYLE_TYPE.CHARACTER)
    font_object = font_charstyle.font
    font_object.size = Pt(15)
    run = paragraph.add_run('In the Land of Sprinkles where the sweetness lie','customStyle')
    run.font.name = 'Book Antiqua'

    steps = dict(coating = ['Powndered Sugar', 'Glazed', 'Chocolate Icing'],
        topping = ['Sprinkles Rainbow','Chopped Peanuts'],
        drizzle = ['Hot fudge', 'Marshmalow', 'Salted Caramel']
    )

    document.add_heading('How to order', 2)
    document.add_paragraph('Start with a Coating,', style='List Number')
    for coat in steps['coating']:
        document.add_paragraph(coat, style='List Bullet')

    document.add_paragraph('Pick a Topping,', style='List Number')
    for top in steps['topping']:
        document.add_paragraph(top, style='List Bullet')

    document.add_paragraph('Choose a Drizzle', style='List Number')
    for drizzle in steps['drizzle']:
        document.add_paragraph(drizzle, style='List Bullet')

    document.save(path + "document.docx")

With this latest modification, we can now see how the document has changed so far.

Working with tables

Working with tables is a little bit different than everything else we have done until now.
Before we start working on tables, let’s get the data.

Just import IRIS and pandas to the method and execute a simple SELECT.

from docx import Document
from docx.enum.style import WD_STYLE_TYPE
from docx.shared import Pt
import iris
import pandas as pd
#
#
#
rs = iris.sql.exec('SELECT id, img, name, "desc" FROM dc_docx_sample.Donuts')

Now we need to add a table to the document by calling a method add_table() and defining how many columns we will require with the cols parameter and adding only one row for the header.

You can define a style for the table by setting style property. For this example, let’s add a grid with the ‘Table Grid’ style.

        table = document.add_table(rows=1, cols=4)
    table.style = "Table Grid"

The table object has an array of rows with a sequence of table cells in each row. For each cell, we can merge them with other cells or add paragraphs with runs as mentioned before. We can also add another table (subtable), or simply add text. Let’s populate the header row:

        table = document.add_table(rows=1, cols=4)
    table.style = "Table Grid"
    heading_row = table.rows[0].cells
    heading_row[0].text = 'ID'
    heading_row[1].text = 'Image'
    heading_row[2].text = 'Product'
    heading_row[3].text = 'Description'

Finally, using a “for loop”, populate each row with the data that we get. The trick is to add a paragraph and add_picture for the img property on the Donuts example class.

        rs = iris.sql.exec('SELECT id, img, name, "desc" FROM dc_docx_sample.Donuts')
    df = rs.dataframe()
    for idx in df.index:
        row_cells = table.add_row().cells
        row_cells[0].text = str(df['id'][idx])
        image_cell = row_cells[1].add_paragraph('')
        image_cell.add_run().add_picture(path + 'img/' + df['img'][idx])
        row_cells[2].text = str(df['name'][idx])
        row_cells[3].text = str(df['desc'][idx])

You can now output the document with the following table:

Look's very good!

Reading MS Word Documents

Last but not least, it’s time to read some MS Word files!

In the assets folder on the repository, you can find three docx files. The first one, called dogs_tale.docx, is just a part of chapter one of a short story, written by Mark Twain.

In the same way, we instantiate the object in the document when creating it. We are going to do it now. The only difference will be the file path and name on the constructor argument.

The paragraphs property is an array of paragraphs in the file in the document order.

Let’s fetch all the paragraphs from the dogs_tale.docx and then display the total number of paragraphs:

ClassMethod DogsTale(path As %String) [ Language = python ]
{
    from docx import Document
    doc = Document(path + "dogs_tale.docx")

    print(len(doc.paragraphs))
}

Nice!

As we have seen before, a Paragraph has a collection of runs. So, let’s dissect the third paragraph of this file:

ClassMethod DogsTale(path As %String) [ Language = python ]
{
    from docx import Document
    doc = Document(path + "dogs_tale.docx")

    print(len(doc.paragraphs))

    p = doc.paragraphs[2]
    for run in p.runs:
        aux = ''
        if (run.bold): aux = '// has Bold style'
        if (run.italic): aux = '// has Italic style'
        print(run.text, aux + '\n')
}

Note that we can get direct access to the third paragraph by index (remember that Python array of elements starts at 0)

Finally, we can get the complete text of the document and print it as an array:

ClassMethod DogsTale(path As %String) [ Language = python ]
{
    from docx import Document
    doc = Document(path + "dogs_tale.docx")

    print(len(doc.paragraphs))

    p = doc.paragraphs[2]
    for run in p.runs:
        aux = ''
        if (run.bold): aux = '// has Bold style'
        if (run.italic): aux = '// has Italic style'
        print(run.text, aux + '\n')

    fulltext = []
    for p in doc.paragraphs:
        fulltext.append(p.text)
    print(fulltext)
}

The last thing we can do to spice it up a little bit is to read the table on order.docx


ClassMethod Read(path As %String) [ Language = python ]
{
    from docx import Document
    doc = Document(path + "order.docx")

    fulltext = []
    for p in doc.paragraphs:
        fulltext.append(p.text)
    print(fulltext)

    tables = doc.tables
    data = {}
    cols = {}
    for table in tables:
        key = None
        for i, row in enumerate(table.rows):
            for col, cell in enumerate(row.cells):
                text = cell.text
                if i == 0:
                    cols[col] = text
                    data[text] = []
                    continue
                data[cols[col]].append(text)
    print(data)
}

Let’s make a template

It would be a fun exercise to practice everything we have learned so far by making a template. A template is a document that contains a "boilerplate" text that does not change. The input parameter for the information you want to replace will be in the following format:

{{input_parameter}}

In the assets folder on the repository, you can find the third file called template.docx

As we did before, let's instantiate the document by passing the file with the path in the constructor argument.

Then, let's define the dictionary, where we will select the parameter names and values.

ClassMethod Template(path As %String) [ Language = python ]
{
    from docx import Document 


    dict = {"company_name": "Mordor co",
        "name": "Saruman the White",
        "employee_name": "Radagast the Brown",
        "employee_job_title": "magician",
        "job_title": "wizard",
        "quality": "wise",
        "recipient_name": "Gandalf the Gray"
        }
    regex1 = re.compile(r"\{\{(.*?)\}\}") 
    doc = Document(path + "template.docx")
}

Now create a Python function to search a pattern into paragraphs and runs to replace it with the dictionary when found.

ClassMethod Template(path As %String) [ Language = python ]
{
    import re 
    from docx import Document 


    def docx_replace_regex(doc_obj, regex, dict): 
        for p in doc_obj.paragraphs: 
            for r in p.runs:
                arr = regex.findall(r.text)
                for word in arr:
                    r.text = r.text.replace('{{'+word+'}}', dict[word])

    dict = {"company_name": "Mordor co",
        "name": "Saruman the White",
        "employee_name": "Radagast the Brown",
        "employee_job_title": "magician",
        "job_title": "wizard",
        "quality": "wise",
        "recipient_name": "Gandalf the Gray"
        }
    regex1 = re.compile(r"\{\{(.*?)\}\}") 
    doc = Document(path + "template.docx")
    docx_replace_regex(doc, regex1, dict) 
    doc.save(path + 'result1.docx')
}

Let me add a quick explanation to those who are new to Python. This little 'r' before the quote means "raw string literal", in which a backslash, for example, means "just a backslash". You don’t need any escape sequences to represent a new line, tabs, etc.

Conclusion

The article covers the process of writing MS Word files, adding a paragraph, runs, headers, and images to MS Word documents. Finally, how to read paragraphs and runs has been explained too.

If you want to go deep, I strongly recommend taking a look at the official documentation of python-docx

Please do reach out to me with all the suggestions, comments, and feedback you may have, I'll be glad to answer you.

Github link to this code

Thanks for reading.

Brief introduction to Test Driven Development with Caché and CosFaker

Henry "HammZ" Hamon — Sun, 14 Feb 2021 18:35:17 +0000

Hello everyone,

I was first introduced to TDD almost 9 year ago, and I immediately fell in love with it.
Nowadays it's become very popular but, unfortunately, I see that many companies don't use it. Moreover, many developers don't even know what it is exactly or how to use it, mainly beginners.

Overview

My goal with this article is to show how to use TDD with %UnitTest. I will show my workflow and explain how to use cosFaker, one of my first projects, which I created using Caché and recently uploaded to OpenExchange.

So buckle up and let's go.

What is TDD?

Test Driven Development (TDD) can be defined as a programming practice that instructs developers to write new code only if an automated test has failed.
There are tons of articles, lectures, talks, whatever, about its advantages and all of them are correct.
Your code is born already tested, you ensure that your system actually meets the requirements defined for it avoiding over-engineering, and you have constant feedback.

So why not use TDD? What is the problem with TDD? The answer is simple: the Cost! It costs a lot!
Because you have to write more lines of code with TDD, it is a slow process. But with TDD you have a final cost of creating a product NOW, without having to add to it in the future.
If you run the tests all the time you find errors early, thus reducing the cost of their correction.
So my advice: Just Do it!

Setup

InterSystems has a documentation and tutorial on how to use %UnitTest, you can read it here.

I use vscode to develop. This way I create a separate folder for tests. I add my project code path to UnitTestRoot and when I execute tests I pass the name of the test subfolder. And I always pass the qualifier loadudl

Set ^UnitTestRoot = "~/code"

Do ##class(%UnitTest.Manager).RunTest("myPack","/loadudl")

Steps

Probably you have heard about the famous TDD cycle: red ➡ green ➡ refactor. You write a test that fails, you write a simple production code to make it pass and you refactor the production code.
So let's get the hands dirty and create a class to do some maths and another class to test it. The latter class shall extend %UnitTest.TestCase.
Now let's create a ClassMethod to return a square of an integer number:

Class Production.Math
{

  ClassMethod Square(pValue As %Integer) As %Integer
  {

  }
}

And test what will happen if we pass 2. It must return 4.

Class TDD.Math Extends %UnitTest.TestCase
{

  Method TestSquare()
  {

    Do $$$AssertEquals(##class(Production.Math).Square(2), 4)

  }
}

If you run:

Do ##class(%UnitTest.Manager).RunTest("TDD","/loadudl")

the test will Fail

Red! The next step is to make it Green.
To make it work let´s return 4 as a result of execution of our Square method.

Class Production.Math
{
  ClassMethod Square(pValue As %Integer) As %Integer
  {
  Quit 4
  }
}

and rerun our test.

Probably you aren't very happy with this solution, because it actually works for only one scenario. Fine! Let's take the next step. Let's create another test scenario, now sending a negative number.

Class TDD.Math Extends %UnitTest.TestCase
{
  Method TestSquare()
  {
    Do $$$AssertEquals(##class(Production.Math).Square(2), 4)
  }

  Method TestSquareNegativeNumber()
  {
    Do $$$AssertEquals(##class(Production.Math).Square(-3), 9)
  }
}

When we run the test:

it will Fail again, so let's refactor the production code:

Class Production.Math
{
  ClassMethod Square(pValue As %Integer) As %Integer
  {
  Quit pValue * pValue
  }
}

and rerun our tests:

Now everything works fine... This is the cycle of TDD, in small steps.

You must be asking yourself, why do I have to follow these steps? Why do I have to see the test fail?
I've worked in teams that wrote the production code and only later wrote the tests. But I prefer to follow these baby steps for the following reasons:
Uncle Bob (Robert C. Martin) said that writing tests after writing the code is not TDD and instead is called “a waste of time”.
Another detail, when I see the test fail, and later I see it pass, I'm testing the test.
Your test is still a code; and it may contain mistakes too. And the way to test it is to guarantee that it fails and passes when it needs to fail and pass. This way you've "tested the test".

cosFaker

To write good tests you might need to generate test data first. One way to do it is to generate a dump of data and use it in your tests.
Another way is to use cosFaker to easily generate fake data when you need it. https://openexchange.intersystems.com/package/CosFaker

You just need to download the xml file, after that go to Management Portal -> System Explorer -> Classes -> Import. Select the xml file to import, or drag the file in Studio.
Also you can import it using Terminal

Do $system.OBJ.Load("yourpath/cosFaker.vX.X.X.xml","ck")

Localization

cosFaker will add locales files in default CSP application folder. For now there are only two languages: English and Brazilian Portuguese (my native language).
The language of data is chosen according to configuration of your Caché.
The localization of cosFaker is an ongoing process, if you want to help, please don't hesitate to create a localized provider for your own locale and submit a Pull Request.
With cosFaker you can generate random Words, Paragraphs, Phone numbers, Names, Addresses, Emails, Prices, Product Names, Dates, Hexadecimal color codes... etc.

All methods are grouped by subject in classes, i.e. to generate a Latitude you call the method Latitude in class Address

 Write ##class(cosFaker.Address).Latitude()

-37.6806

You can also generate Json for your tests

Write ##class(cosFaker.JSON).GetDataJSONFromJSON("{ip:'ipv4',created_at:'date.backward 40',login:'username', text: 'words 3'}")
{
    "created_at":"2019-03-08",
    "ip":"95.226.124.187",
    "login":"john46",
    "text":"temporibus fugit deserunt"
}

Here is a full list of cosFaker classes and methods:

cosFaker.Address
- StreetSuffix
- StreetPrefix
- PostCode
- StreetName
- Latitude
  - Output: -54.7274
- Longitude
  - Output: -43.9504
- Capital( Location = “” )
- State( FullName = 0 )
- City( State = “” )
- Country( Abrev = 0 )
- SecondaryAddress
- BuildingNumber
cosFaker.App
- FunctionName( Group= “”, Separator = “” )
- AppAction( Group= “” )
- AppType
cosFaker.Coffee
- BlendName
  - Output: Cascara Cake
- Variety
  - Output: Mundo Novo
- Notes
  - Output: crisp, slick, nutella, potato defect!, red apple
- Origin
  - Output: Rulindo, Rwanda
cosFaker.Color
- Hexadecimal
  - Output: #A50BD7
- RGB
  - Output: 189,180,195
- Name
cosFaker.Commerce
- ProductName
- Product
- PromotionCode
- Color
- Department
- Price( Min = 0, Max = 1000, Dec = 2, Symbol = “” )
  - Output: 556.88
- CNPJ( Pretty = 1 )
  - CNPJ is the Brazilian National Registry of Legal Entities
  - Output: 44.383.315/0001-30
cosFaker.Company
- Name
- Profession
- Industry
cosFaker.Dates
- Forward( Days = 365, Format = 3 )
- Backward( Days = 365, Format = 3 )
cosFaker.DragonBall
- Character
  - Output: Gogeta
cosFaker.File
- Extension
  - Output: txt
- MimeType
  - Output: application/font-woff
- Filename( Dir = “”, Name = “”, Ext = “”, DirectorySeparator = “/” )
  - Output: repellat.architecto.aut/aliquid.gif
cosFaker.Finance
- Amount( Min = 0, Max = 10000, Dec = 2, Separator= “,”, Symbol = “” )
  - Output: 3949,18
- CreditCard( Type = “” )
  - Output: 3476-581511-6349
- BitcoinAddress( Min = 24, Max = 34 )
  - Output: 1WoR6fYvsE8gNXkBkeXvNqGECPUZ
cosFaker.Game
- MortalKombat
  - Output: Raiden
- StreetFighter
  - Output: Akuma
- Card( Abrev = 0 )
  - Output: 5 of Diamonds
cosFaker.Internet
- UserName( FirstName = “”, LastName = “” )
- Email( FirstName = “”, LastName = “”, Provider = “” )
- Protocol
  - Output: http
- DomainWord
- DomainName
- Url
- Avatar( Size = “” )
  - Output: http://www.avatarpro.biz/avatar?s=150
- Slug( Words = “”, Glue = “” )
- IPV4
  - Output: 226.7.213.228
- IPV6
  - Output: 0532:0b70:35f6:00fd:041f:5655:74c8:83fe
- MAC
  - Output: 73:B0:82:D0:BC:70
cosFaker.JSON
- GetDataOBJFromJSON( Json = “” // JSON template string to create data )
  - Parameter Example: "{dates:'5 date'}"
  - Output: {"dates":["2019-02-19","2019-12-21","2018-07-02","2017-05-25","2016-08-14"]}
cosFaker.Job
- Title
- Field
- Skills
cosFaker.Lorem
- Word
- Words( Num = “” )
- Sentence( WordCount = “”, Min = 3, Max = 10 )
  - Output: Sapiente et accusamus reiciendis iure qui est.
- Sentences( SentenceCount = “”, Separator = “” )
- Paragraph( SentenceCount = “” )
- Paragraphs( ParagraphCount = “”, Separator = “” )
- Lines( LineCount = “” )
- Text( Times = 1 )
- Hipster( ParagraphCount = “”, Separator = “” )
cosFaker.Name
- FirstName( Gender = “” )
- LastName
- FullName( Gender = “” )
- Suffix
cosFaker.Person
- cpf( Pretty = 1 )
  - CPF is the Brazilian Social Security Number
  - Output: 469.655.208-09
cosFaker.Phone
- PhoneNumber( Area = 1 )
  - Output: (36) 9560-9757
- CellPhone( Area = 1 )
  - Output: (77) 94497-9538
- AreaCode
  - Output: 17
cosFaker.Pokemon
- Pokemon( EvolvesFrom = “” )
  - Output: Kingdra
cosFaker.StarWars
- Characters
  - Output: Darth Vader
- Droids
  - Output: C-3PO
- Planets
  - Output: Takodana
- Quotes
  - Output: Only at the end do you realize the power of the Dark Side.
- Species
  - Output: Hutt
- Vehicles
  - Output: ATT Battle Tank
- WookieWords
  - Output: nng
- WookieSentence( SentenceCount = “” )
  - Output: ruh ga ru hnn-rowr mumwa ru ru mumwa.
cosFaker.UFC
- Category
  - Output: Middleweight
- Fighter( Category = “”, Country = “”, WithISOCountry = 0 )
  - Output: Dmitry Poberezhets
- Featherweight( Country = “” )
  - Output: Yair Rodriguez
- Middleweight( Country = “” )
  - Output: Elias Theodorou
- Welterweight( Country = “” )
  - Output: Charlie Ward
- Lightweight( Country = “” )
  - Output: Tae Hyun Bang
- Bantamweight( Country = “” )
  - Output: Alejandro Pérez
- Flyweight( Country = “” )
  - Output: Ben Nguyen
- Heavyweight( Country = “” )
  - Output: Francis Ngannou
- LightHeavyweight( Country = “” )
  - Output: Paul Craig
- Nickname( Fighter = “” )
  - Output: Abacus

Let's create a class for user with a method that returns his username, that will be FirstName concatenated with LastName.

Class Production.User Extends %RegisteredObject
{

Property FirstName As %String;

Property LastName As %String;

Method Username() As %String
 {

 }
}

Class TDD.User Extends %UnitTest.TestCase
{

Method TestUsername()
{

  Set firstName = ##class(cosFaker.Name).FirstName(),
    lastName = ##class(cosFaker.Name).LastName(),
    user = ##class(Production.User).%New(),
    user.FirstName = firstName,
    user.LastName = lastName


  Do $$$AssertEquals(user.Username(), firstName _ "." _ lastName)
}

}

Refactoring:

Class Production.User Extends %RegisteredObject
{

Property FirstName As %String;

Property LastName As %String;

Method Username() As %String
{
  Quit ..FirstName _ "." _ ..LastName
}

}

Now we are going to add an account expiry date and validate it.

Class Production.User Extends %RegisteredObject
{

Property FirstName As %String;

Property LastName As %String;

Property AccountExpires As %Date;

Method Username() As %String
{
  Quit ..FirstName _ "." _ ..LastName
}

Method Expired() As %Boolean
 {

 }

}

Class TDD.User Extends %UnitTest.TestCase
{

Method TestUsername()
{
  Set firstName = ##class(cosFaker.Name).FirstName(),
    lastName = ##class(cosFaker.Name).LastName(),
    user = ##class(Production.User).%New(),
    user.FirstName = firstName,
    user.LastName = lastName

    Do $$$AssertEquals(user.Username(), firstName _ "." _ lastName)

}

Method TestWhenIsNotExpired() As %Status
{
  Set user = ##class(Production.User).%New(),
    user.AccountExpires = ##class(cosFaker.Dates).Forward(40)

  Do $$$AssertNotTrue(user.Expired())
}

}

Refactoring:

Method Expired() As %Boolean
{
  Quit ($system.SQL.DATEDIFF("dd", ..AccountExpires, +$Horolog) > 0)
}

Now let’s test when account is expired:

Method TestWhenIsExpired() As %Status
{
  Set user = ##class(Production.User).%New(),
    user.AccountExpires = ##class(cosFaker.Dates).Backward(40)

  Do $$$AssertTrue(user.Expired())
}

And Everything is green..

I know these are silly examples, but this way you will have simplicity not only in the code but also in the class design.

Conclusion

In this article you have learnt a little bit about Test Driven Development and how to use the %UnitTest class.
We also covered cosFaker and how to generate fake data for your tests.

There's much more to learn about tests and TDD, how to use these practices with legacy code, integration tests, acceptance tests (ATDD), bdd, etc...
If you want to know more about it, I strongly recommend 2 books:

Test Driven Development Teste e design no mundo real com Ruby - Mauricio Aniche, I really don't know if this book has english version. There are editions for Java, C#, Ruby and PHP. This book blew my mind with its awesomeness.

And of course, Kent Beck's book Test Driven Development by Example

Feel free to leave any comments or questions.
That's all folks

Power BI Connector for InterSystems IRIS

Henry "HammZ" Hamon — Wed, 30 Dec 2020 01:17:35 +0000

In an ever-changing world, companies must innovate to stay competitive. This ensures that they’ll make decisions with agility and safety, aiming for future results with greater accuracy.
Business Intelligence (BI) tools help companies make intelligent decisions instead of relying on trial and error. These intelligent decisions can make the difference between success and failure in the marketplace.
Microsoft Power BI is one of the industry’s leading business intelligence tools. With just a few clicks, Power BI makes it easy for managers and analysts to explore a company’s data. This is important because when data is easy to access and visualize, it’s much more like it’ll be used to make business decisions.

Power BI includes a wide variety of graphs, charts, tables, and maps. As a result, you can always find visualizations that are a good fit for your data.

BI tools are only as useful as the data that backs them, however. Power BI supports many data sources, and InterSystems IRIS is a recent addition to those sources. Since Power BI provides an exciting new way to explore data stored in IRIS, we’ll be exploring how to use these two amazing tools together.

This article will explain how to use IRIS Tables and Power BI together on real data. In a follow-up article, we’ll walk through using Power BI with IRIS Cubes.

Project Prerequisites and Setup

You will need the following to get started:

* InterSystems IRIS Data Platform
* Microsoft Power BI Desktop (April 2019 release or more recent)
* InterSystems Sample-BI data

We'll be using the InterSystems IRIS Data Platform, so you’ll need access to an IRIS install to proceed. You can download a trial version from the InterSystems website if necessary.

There are two ways to install the Microsoft Power BI Desktop. You can download an installer and, or install it through the Microsoft Store. Note that if you are running Power BI from a different machine than where you installed InterSystems IRIS, you will need to install the InterSystems IRIS ODBC drivers on that machine separately

To create a dashboard on Power BI we'll need some data. We'll be using the HoleFoods dataset provided by InterSystems here on GitHub. To proceed, either clone or download the repository.

In IRIS, I've created a namespace called SamplesBI. This is not required, but if you want to create a new namespace, in the IRIS Management Portal, go to System Administration > Configuration > System Configuration > Namespace and click on New Namespace. Enter a name, then create a data file or use an existing one.

On InterSystems IRIS Terminal, enter the namespace that you want to import the data into. In this case, SamplesBI:

Execute $System.OBJ.Load() with the full path of buildsample/Build.SampleBI.cls and the "ck" compile flags:

Execute the Build method of Build.SampleBI class, and full path directory of the sample files:

Connecting Power BI with IRIS

Now it's time to connect Power BI with IRIS. Open Power BI and click on "Get Data". Choose "Database", and you will see the InterSystems IRIS connector:

Enter the host address. The host address is the IP address of the host for your InterSystems IRIS instance (localhost in my case), the Port is the instance’s superserver port (IRIS default is 57773), and the Namespace is where your HoleFoods data is located.

Under Data Connectivity mode, choose "DirectQuery", which ensures you’re always viewing current data.

Next, enter the username and password to connect to IRIS. The defaults are "_SYSTEM" and "SYS".

You can import both tables and cubes generated you’ve created in IRIS. Let’s start by importing some tables.

Under Tables and HoleFoods, check:

* Country
* Outlet
* Product
* Region
* SalesTransaction

We're almost there! To tell Power BI about the relationship between our tables, click on "Manage Relationships".

Then, click on New.

Let's make two relationships: "SalesTransaction" and "Product relationship".

On top, select the "SalesTransaction" table and click on the "Product" column. Next, select the "Product" table and click on the "ID" column. You'll see that the Cardinality changes automatically to "Many to One (*:1)".

Repeat this step for the following:

* "SalesTransaction(Outlet)" with "Outlet(ID)"
* "Outlet(Country)" with "Country(ID)"
* "Country(Region)" with "Region(ID)":

Note that these relationships are imported automatically if they are expressed as Foreign Keys.

Power BI also has a Relationships schema viewer. If you click the button on the left side of the application, it will show our data model.

Creating a Dashboard

We now have everything we need to create a dashboard.

Start by clicking the button on the left to switch from schema view back to Report view. On the Home tab under the Insert Group, click the TextBox to add a Title.

The Insert Group includes static elements like Text, Shapes, and Images we can use to enhance our reports.

It's time to add our first visualization! In the Fields pane, check "Name" on "Product" and "UnitsSold" on "SalesTransaction".

Next, go to Style and select "Bold Header".

Now it's time to do some data transformation. Click on the ellipsis next to "SalesTransaction" in the Field pane.

Then, click on "Edit Query". It will open the "Power Query Editor".

Select the "DateOfSale" column and click on "Duplicate Column".

Rename this new column to "Year", and click on "Date" and select "Year".

Apply these changes. Next, select the new column and, on the "Modeling" tab, change "Default Summarization" to "Don't Summarize".

Add a "Line Chart" visualization, then drag Year to Axis, drag "Name" from "Region" to Legend, and drag "AmountOfSale" from "SalesTransaction" to Values.

Imagine that the HoleFoods sales team has a target of selling 2000 units. How can we tell if the team is meeting its goal?

To answer, let's add a visual for metrics and targets.

On "SalesTransaction" in the Field pane, check "UnitsSold", then click Gauge Chart. Under the Style properties, set Max to 3000 and Target to 2000.

KPIs (Key Performance Indicators) are helpful decision-making tools, and Power BI has a convenient KPI visual we can use.

To add it, under "SalesTransaction", check "AmountOfSale" and choose KPI under “Visualizations”. Then, drag "Year" to "Trend axis".

To align all charts and visuals, simply click and drag a visual, and when an edge or center is close to aligning with the edge or center of another visual or set of visuals, red dashed lines appear.

You also can go to the View tab and enable "Show GridLines" and "Snap Objects to Grid".

We’ll finish up by adding a map that shows HoleFoods global presence. Set Longitude and Latitude on "Outlet" to "Don't Summarize" on the Modeling tab.

You can find the map tool in the Visualizations pane. After adding it, drag the Latitude and Longitude fields from Outlet to respective properties on the map. Also from SalesTransaction, drag the AmountOfSale to Size property and UnitsSold to ToolTips.

And our dashboard is finally complete.

You can share your dashboard by publishing it to the Power BI Service. To do this, you’ll have to sign up for a Power BI account.

Conclusion

In just a few minutes, we were able to connect Power BI to InterSystems IRIS and then create amazing interactive visualizations.

As developers, this is great. Instead of spending hours or days developing dashboards for managers, we can get the job done in minutes. Even better, we can show managers how to quickly and easily create reports for themselves.

Although developing visualizations is often part of a developer’s job, our time is usually better spent developing mission-critical architecture and applications. Using IRIS and Power BI together ensures that developer time is used effectively and that managers are able to access and visualize data immediately — without waiting weeks for dashboards to be developed, tested, and deployed to production.