Monday, November 19, 2007

Missing data in XML (format FMPXMLRESULT)

We usually have XML data with some value missing, like in this example:



<COL>
<DATA>Value1</DATA>
<DATA>Value2</DATA>
<DATA>Value3</DATA>
<DATA>Value4</DATA>
</COL>
<COL>
<DATA>240</DATA>
<DATA />
<DATA>6</DATA>
<DATA>6</DATA>
</COL>



In order that the XML reader overlooks the data missing at the second position in a consistent way, the following condition can be used (the following code is "conceptual", which means that it is not the real one that is currently running):


(...)

if (currentReader.NodeType == XmlNodeType.Text)
{
if (isRowToRetrieve)
{
rowInfo[columnIndex] = reader.Value;

if (columnIndex == ProdIDPos)
{
//Values capture
valuesArray[valuesIndex++] = currentReader.Value;
}
else if (columnIndex == QuantityPos)
{
//Quantities capture
quantitiesArray[quantitiesIndex++] = currentReader.Value;
}
else if{(...)}
else{(...)}
}
}

(...)

if ((lastElementName == "DATA") && (currentReader.Name == "DATA") && (lastNodeType != XmlNodeType.Text))
{
Log("Quantity for "+ valuesArray[quantitiesIndex] +" is missing");
}



Then the key item here is the consideration that, between a XML ElementName and EndElementName, a XML NodeType Text is expected

Wednesday, October 17, 2007

Localhost FTP problems

In the past, I had problems using IIS's FTP to work. Therefore, I used WarFTP, which was an easy setup and worked fine.

However, today, I just could not get FTPPuts to work. I was getting an Access Denied error, and had no clue what the problem was. After uninstalling and reinstalling, checking params in the server, in my Filezilla, and in my FTP code, I was still clueless.

OK, WarFTP out, IIS back in - and the same exact error! A "critical transfer error" 550, permissions. It was not IIS or WarFTP that was the problem, but the lack of write permissions for the FTP user in NTFS. Letting "everybody" write to my FTP directory fixed the problem.

Bye bye WarFTP - you've served me well - but I'll stay with IIS now till I have further problems ;->

Friday, October 12, 2007

Abandoned Carts on Credit Card Page

On one of our client's web shops using SecPay's SECPage interface to check out (without 3D turned on - 3D = MasterCard's 'SecureCode', and Visa’s 'Verified by Visa'), we have 4.5% of users that never come back from SecPay (marked "New" in JShop), and 0.5% whose credit cards fail.

Note that "never coming back" might also indicate that they failed SecPay's pre-bank checks - SecPay does not indicate to the merchants if clients did not come back due to the pre-bank checks or because of abandoning the cart. However, it appears that the percentage that fails the pre-bank check is small compared to people who simply do not complete the order.

4.5% is actually good compared to the other web shops that I talked to. One reported 8% (SECPage with 3D turned on), another said 4-8% was "normal" (Secure Trading), and a 3rd report 10% (PayPal).

What to do when the users never come back? We send out an email after 2 hours, and then again after 10 days, with a link to help the user complete the order. In this way, we're able to get almost one third of users to complete their order. One shop (with few orders of much higher totals) follows up with phone calls to all the abandoned carts, and gets a very large percentage (well over 50%) to finish their order.

Thursday, August 02, 2007

FileMaker Auto-Login

While working on automating our daily FileMaker -> SQLServer data dump, we needed a way to automatically log into FileMaker whenever it brought up the password dialog. When Felipe mentioned the problem this morning when we got our daily morning coffee at the bakery downstairs, I realized that it could be done with some old Win32 calls.

I borrowed and stole ideas from my Google searches (starting with EnumWindow C#), and used the trusty old Spy++ application (part of Visual Studio Tools, and something that I haven't fired up in years), to get 90% of the way there. When I was at 90% and trying to set the password text in the edit box, I found Using P/Invoke to Automate Database Signon, which would have saved me quite a bit of time if I had found it earlier, and got me over the last 10%.

Here is the basic code. You can recreate this by creating a WindowsApplication project in VisualStudio 2005 and adding a timer component which fires as often as you need. Be sure to set the timer to "enabled", and hook it up to the timer1_Tick function.


using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;


public delegate bool FMCallBack(int hwnd, int lParam);

namespace FMAutoLogin
{
public partial class Form1 : Form
{
private const int WM_SETTEXT = 0x000C; //TextBox
private const int BM_CLICK = 0x00F5; //Button
[DllImport("user32.Dll")]
public static extern int EnumWindows(FMCallBack x, int y);
[DllImport("User32.Dll")]
public static extern void GetWindowText(int h, StringBuilder s, int nMaxCount);
[DllImport("user32.dll")]
[return: MarshalAs(UnmanagedType.Bool)]
static extern bool SetForegroundWindow(IntPtr hWnd);
[DllImport("user32.dll", CharSet = CharSet.Auto)]
static extern IntPtr SendMessage(IntPtr hWnd,
uint Msg, int wParam, string lParam);
[DllImport("user32.dll")]
static extern IntPtr FindWindowEx(IntPtr hwndParent, IntPtr hwndChildAfter,
string lpszClass, string lpszWindow);

//----------------------------------------------------------------------
public Form1()
{
InitializeComponent();
}

//----------------------------------------------------------------------
private void timer1_Tick(object sender, EventArgs e)
{
EnumWindows(new FMCallBack(Form1.EnumWindowCallBack), 0);
}

//----------------------------------------------------------------------
private static bool EnumWindowCallBack(int hwnd, int lParam)
{
StringBuilder sb = new StringBuilder(1024);
GetWindowText((int)hwnd, sb, sb.Capacity);

if (sb.ToString().Contains(".fp5") &&
sb.ToString().Contains("File \""))
{
SetForegroundWindow((IntPtr) hwnd);
IntPtr editBox = FindWindowEx((IntPtr)hwnd, IntPtr.Zero, "Edit", "");
SendMessage(editBox, WM_SETTEXT, 0, "your_password");
IntPtr okButton = FindWindowEx((IntPtr)hwnd, IntPtr.Zero, "Button", "OK");
SendMessage(okButton, BM_CLICK, 0, "");
}
return true;
}
}
}

Thursday, April 26, 2007

Taking Shop Offline

CT recently had to take the shop offline, and I added the following to /templates/includes/top.html (and top_checkout.html) to make sure that we could use the shop, but not customers...

if ($_SERVER['REMOTE_ADDR'] != "80.28.198.60")
doRedirect("http://www.visiondirect.co.uk/checkoutChanges.txt");
?>

After adding (or removing) this, you need to remove compiled templates.

NOTE: SecPay cannot arrive successfully at the orderSuccess page if you have this code running.
NOTE2: Of course, you additionally have to add the file indicated by "doRedirect".

Wednesday, February 21, 2007

Count sales from Tuesday

Here is how I got a count of all Tuesday sales from GreenLight...
select count(*) as mycount, left(datetime, 8) as mydate from {orders} where dayofweek(datetime) = 3 and referURL like "GL%" group by mydate order by mydate desc

Tuesday, January 30, 2007

Daily GreenLight Sales

When checking a drop in GreenLight sales, I used...

select left (datetime,8) as mydate, left (datetime,4), mid(datetime, 5, 2), mid(datetime, 7, 2), count(*) as mycount from [OrdersTable] where datetime >= "20070101000000" and referURL like "GL|%" group by mydate order by mydate