Category Archives: Uncategorized

Hosting with Linode

Let us sing the praises of Linode, a provider of cloud-hosting for Linux-based applications and web sites. At work we’ve had two of these for a year, and they just work, exactly if the servers were sitting in the next room, except that they provide a slick management interface, and you can mix and match among different disk sizes, RAM, processors, redundancy, and various flavors of Linux. Access is via their web site, or via a secure terminal session.

Here’s a shot of the backup screen, (click to enlarge).

 The Linode-provided backup is great for basic backup. It makes backups on a weekly and daily basis. It also has a provision for a snapshot, which is a great way to back up the server before doing upgrades. Full details are on their web site, including the costs, which are a few dollars per month depending on the size of the Linode.

There are some limitations…for example with MySQL database transactions. Linode recommends that you perform a data dump of your MySQL data so that the dump files get backed up.

Advertisements

File Management in Powershell

I’m looking to do the following in Powershell

1. Is a file that exists in a source folder more recent than a file in a target folder?
2. If it the file is more recent…copy it to the target folder, but before copying…
3. Back up the current version in the target folder, by appending a date to the file name.

Does a file exist?

test-path <filename>
This returns TRUE or FALSE if the file exists. If the file is in the current folder, then you can just list the file name, otherwise, the full path has to be included.

What is the file’s date and time?

The file date and time are properties of the file name, obtained via the Get-Item cmdlet.  We can assign these to a variable. Here I will compare file dates between two files that have the same name, but reside in different folders, one located along the default path, and one located on drive W.

$Sourcedate = (Get-Item w:myfile.csv).LastWriteTime
$TargetDate = (Get-Item myfile.csv).LastWriteTime
If ($Sourcedate = $Targetdate) {"True"}
If ($Sourcedate -lt $Targetdate) {"True"}
If ($Sourcedate -gt $Targetdate) {"True"}

Note (rather irritatingly….) that the usual comparison operators are different in Powershell.

=  -eq Equal
<> -ne Not Equal 
>= -ge Greater than or equal 
>  -gt Greater than
<  -lt Less than 
<= -le Less than or equal

 

Back up the target file

Having determined that the source file is newer than the target file, we now want to back up the target file, incorporating the date within the renamed filename.

myfile.csv
myfile0150505.csv
myfile.csv_5150505

Since we’ve used the functions for this in another post someplace,..

$ShortDate= $SourceDate | Get-Date -UFormat %Y%m%d

This returns the date in a short form:

20150505

Now append the date to the filename

$newfilename="myfile_"+$ShortDate+".csv"
Rename-Item myfile.csv $newfilename

This results in a file name of:

myfile_20150505.csv

Now we can do the copy from the source folder to the target.

Copy-Item  w:myfile.csv

 

 

 

 

Sharing QuickBooks 2014

Having recently converted from Peachtree to QuickBooks, we wanted to add an additional person on QB so that both our accountant, and our CFO could work in QB at the same time. We started out sharing the data file from the CFOs computer, but that bogged things down.

The solution is a “server”….  which runs the QuickBooks Database Manager program.  We repurposed an older but reliable HP workstation by installing a fresh copy of Windows 7.  Then, we ran the QuickBooks installation program from their support  link.

While it looks as if you are installing the full QuickBooks program, an initial screen asks what kind of installation you want:

  • Use QuickBooks on this machine, and access a company file stored elsewhere on the network 
  • Use QuickBooks on this machine and host the company file for others on the network 
  • Host the company file on this machine, but access it from Quickbooks on other machines 

By choosing the hosting option, the installation program will install the QuickBooks Database Manager program only, on your server machine.

Networking

Assuming you are not on a domain network, (i.e. your users’ machines are set to be a workgroup) you need to create user accounts on the server machine which match the Windows 7 user accounts that users use to log into their personal workstations.  This will allow people to connect to the server machine using their usual account name, without having to enter it a second time.

Set up the data folder 

Create a folder on the server machine to hold the QuickBooks company file. Copy the company file into that folder.

This needs to be  a shared folder on the network. The QuickBooks file doctor program will turn on folder sharing, and make the folder accessible. Download and run this program on the server.

Finalizing

At this point, you have the company file loaded on the server. Now, run the QuickBooks database manager program.  This will ask you for your company folder so it can find the company file.
Note that the database manager runs as a background service,  so you can exit the program, and the file will still be available to the network.

Now you can go to each workstation, and access the file over the network. I edited the opening QuickBooks screen to show just the networked copy of the file. The next time the user starts QuickBooks, it should automatically open the file without requiring a Windows network log in. Of course, the QuickBooks log in is still required.

Licensing

Using the database server does not require a QuickBooks user license. You can safely move a company file to a server, and have the same number of users access the file. 

Details at:
https://support.quickbooks.intuit.com/support/Articles/HOW13004

Powershell: Search and Replace in text files

Searching and replacing within Powershell, seems a little awkward.

To do a search and replace, use the –replace parameter of ForEach-Object, with two attributes, the first being the thing to search for and the second being the replacement.

Get-Content -path ./NGA_Tracking.csv | ForEach-Object {$_ -replace ‘Tracking’, ‘TrackNum’}

You can do a series of these in a single set of piped commands….

Get-Content -path ./NGA_Tracking.csv | ForEach-Object { $_ -replace ‘Tracking’, ‘TrackNum’ `
    -replace ‘X’, ‘Y’ `
    -replace ‘A’, ‘B’ } |
Set-Content $mynewfile

In the case of a text file, each object being modified in the For-Each loop is a string terminated by a newline.

The good news is that there is full support for regular expressions, so if you know how to deal with those, they can be incorporated within the -replace mechanism.

I guess I’m still looking for something a bit more friendly for casual use, Wouldn’t it be nice, for instance to have something like Get-Content $myfile -replace ‘X’,’Y’.   Well, we actually can do this:

(Get-Content ./NGA_Tracking -raw) -replace ‘Tracking’, ‘TrackNum’ | out-file ./NGA_Tracking -Encoding utf8 

If you don’t put the parentheses around Get-Content $filename -raw you generate an error. The parentheses read the entire file into memory as an object, which can then take the -replace parameter. The -Encoding parameter is there to make sure the characters are all read correctly in subsequent uses of the file. (took lots of experimentation and blood on the floor, but that it what worked.)

The effect is the same, when the file name is replaced.

$Myfile = ‘./NGA_Tracking.csv’
(Get-Content $MyFile)  -replace ‘Tracking’, ‘TrackNum’ | Out-File $MyFile -Encoding utf8

But, if you create an object then it works.

$Myfile = Get-Content ‘./NGA_Tracking.csv’
$Myfile -replace ‘Tracking, ‘Tracknum’ 

Text to HTML

As we start fiddling with text files, the question comes up regarding the creation of html files.  There is a Convertto-HTML commandlet, which, on the face of it looks pretty rudimentary. But this TechNet article explains how to enhance things using styles.

Brightpearl API: Add UPS Tracking Numbers

We have now been using our web store for about a month, and for the most part things have been going pretty smoothly. One issue has been sending orders to our warehouse, and we’ve got a pretty good Powershell script that creates a comma delimited text file (.csv) of order numbers and address information from queries to the Brightpearl API. This file is sent daily to the warehouse via FTP, and warehouse staff  import the orders into their UPS Worldship program.

Screenshot_042715_044611_PM

The second half of this saga is to obtain the UPS Tracking number for each shipment. Once the shipment has been processed in UPS Worldship, a tracking number is generated and stored in the UPS Worldship record for that shipment. Worldship has an export function which will add the tracking number to a .csv file of order numbers and tracking numbers that we can use to update the order record in Brightpearl. The structure of this file, (which is completely customizable) is:

Order Number – In our case it is the Brightpearl sales order number
Tracking Number – from UPS. These look like “1Z 041 388 03 8331 4101”
Expected Delivery Date.
The .csv file looks like this. (UPS loves long field names).

ShipToCustomerID,ShipmentInformationLeadTrackingNumber,ShipmentInformationDeliveryDateTransitTime
"100064","1Z0413880373533722","20150429"
"100020","1Z0413880373302132","20150504"
"100068","1Z0413880373810940","20150430"
"100074","1Z0413880374436157","20150430"

The next step is to walk through the .csv file, find an order number, and update the custom field PCF_TRACKING in Brightpearl to contain the tracking number. Here is the Powershell call to update a single record:

PS>$BPOrders=Invoke-RestMethod `
 -Uri http://ws-use.brightpearl.com/public-api/myBPAccount/order-service/order/100541/custom-field `
 -Body $body `
 -Headers $headers `
 -Method Patch

There are a couple points of interest here. For the most part it is “standard” Powershell syntax for the Invoke-RestMethod.
1. We invoke this by assigning the results of the API call to $BPOrders
2. The call has several lines; the line continuation character is a accent aigu or back-tick.
3. Note that this query uses a $headers variable which includes the two validation properties for the Brightpearl query: , the name of the application and the security token for the application. These are stored as a hashtable.

PS>$headers
brightpearl-app-ref myappreference
brightpearl-staff-token mystaff-tokenXYZ123

More on obtaining the authcode here.

4. The $body variable is also created as a hashtable, but then converted to JSON, and placed between square brackets. This variable contains three parameters, the operation that you are performing on the record, the field that you want to modify, and the value that you want to put in the field. The syntax below simply says, “Replace the contents of the /PCF_TRACKING field with the value of 12345”.

$body=[ordered]@{"op"="replace";"path"="/PCF_TRACKING";"value"="12345"}
$body=($body | ConvertTo-JSON)
$body=("["+$body+"]")

The result is:

PS>$body
[{
"op": "replace",
"path": "/PCF_TRACKING",
"value": "12345"
}]

5. The -Method parameter is a “Patch”. This allows you to replace the contents of a single field in a record rather than replace an entire record as happens when you use PUT.

6. Finally note in the Invoke-RestMethod call, the URI contains “custom-field”. This is a literal, it isn’t the name of your custom field. The name of the custom field is contained in the body.I In the example above, it is “/PCF_TRACKING”

The above API call will replace the contents of a single field in a single record. The next step is to be able to loop through the .csv file, and for each record, find the corresponding record within the Brightpearl database, and update its Tracking number field.

Oh, one more thing, the results of the operation are contained in $BPOrders. The API actually returns the contents of ALL custom fields. You can choose which ones you want to see using dot notation.


PS>$BPOrders.response.PCF_TRACKING
12345

Quick Look at Windows 10

Well, I was going to say that the Windows 10 technical preview solves many of the problems that Windows 8 has, including the lack of a start button.

I installed this in a virtual machine both to take a look at 10, but especially to take advantage of updates to Windows PowerShell, which are available only with Windows 8 or later. I’m happy to say the start button is there.

The start button leads to the tiles left over from Windows 8. Maybe this can be re-configured to avoid the tiles? Didn’t Microsoft get the memo that people working on business-class desktop computers DON’T WANT TILES!  At least not the default ones, with videos, the stock market, etc.

OK so you can right-click and delete a tile.  I can imagine configuring this as a standard operation when deploying a new desktop computer to a co-worker.

If you look in the document explorer the new icons are even more cartoon-like than than before. It makes me nostalgic for the old “cartoon-like” icons of Windows XP.

  Sigh.

PowerShell: Simple GUI Message Boxes

One way to build simple message boxes in PowerShell is to “borrow” from .NET COM objects. Basically you initialize the object…(a.k.a. load the code for generating a messagebox), and then you create instances of the object which are the actual message boxes. These messagebox code may  be familiar to anyone who has programmed in vbScript, or any of the Microsoft programming languages such as Visual Basic or C#.  The boxes can look pretty good, even though the coding is a bit arcane.

To get started, initialize the messagebox object. You only need to do this once per session.

PS> $wshell=New-Object -comObject Wscript.Shell

Now you can make message boxes until the cows come home.

PS> $wshell.Popup(“Hi…This is a lovely messagebox”,0,”My Window Title”)

messagebox1

You can have multi-line boxes by including the newline (back-tick+’n’) to separate lines.

PS> $wshell.Popup(“Hi! A list of shipping addresses will appear in the next window.`n

If you need to edit the addresses then… `n

  1. Go back to BrightPearl`n
  2. Make the address changes.`n
  3. Run this program again.”,0,”Send To Warehouse”)

MessageBox2

The Buttons

You can have a buttons which return an integer based on which button is pressed.

In PowerShell, the button combinations are designated by integers:

0: OK
1: OK, Cancel
2: Abort, Retry, Ignore
3: Yes, No, Cancel,
4: Yes, No,
5: Retry, Cancel

The parameters for showing a window are:

<messagetext>, <duration>, <window title>, <button integers>

PS> $wshell.Popup(“Hi…This is a lovely messagebox”,0,”My Window Title”,1)

The first parameter is the message that you want to appear in the box. This is a string.

The second parameter “duration”, is an integer that specifies how long you want the messagebox to stay open if there is no activity from the user. If you specify ‘0’, then the box stays open forever. Note that your script is completely paused when this is the case. If you use a positive integer, that is the length in seconds that the messagebox stays open.

The third parameter is a string that is the title of the box. This appears in the top of the window frame.

In the example above, the messagebox call specifies “1” for the OK and Cancel button combination. When the user clicks on a button the messagebox closes, and returns an integer, in this case, 1 for OK or 2 for Cancel. The integers are displayed on the PowerShell command line.  (Technically, the integers are returned to the pipeline).

Note that these returned integers are have nothing to do with the integer used to determine which buttons are displayed. (Why do I have to remind myself of this?) 

Button Return Values

OK Cancel Abort Retry Ignore Yes No
1 2 3 4 5 6 7

Instead of having the result go to the pipeline, you can store it in a variable for further use. Capture the result variable by assigning the message box to a variable.

 PS> $result=$wshell.Popup(“Hi…This is a lovely messagebox”,0,”My Window Title”,1)

PS> $result

The Icons

Messageboxes can also have an icon. There are four to choose from, and each has a designated integer.

Stop 16
Question 32
Exclamation 48
Information 64

Now, here’s the arcane part.  To display an icon,  you take the icon’s numeric value and add it to the numeric value of the button numeric value.  So, for example to place a Stop icon on our sample message box with OK and Cancel buttons  we add 16 to 1. Its not like they couldn’t provide another parameter?

PS> $result=$wshell.Popup(“Hi…This is a lovely messagebox”,0,”My Window Title”,65)

Finally:

If you get an error message, when putting up a messagebox, like the one below…   it means that you didn’t run the New-Object cmdlet one time in your PowerShell session, prior to making a call to put up a messagebox.

PS> $wshell=New-Object -comObject Wscript.Shell

Having run that once, you can create as many messageboxes as you need within that particular PowerShell session.

You cannot call a method on a null-valued expression.
At line:1 char:1
+ $result=$wshell.Popup("Hi…This is a lovely messagebox",0,"My Window Title",65)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

MessageboxFinal