Relationships are complicated.

PE has a relationships feature, which can link clients to each other using relationship types (and reciprocal types...) - Mostly I use these at my place of work to track seat-mates and spouses. If you have a large group of people that like to attend together, but pay separately, this is one way to handle the data.  The problem is that it is difficult to report on this data, since you can end up with any number of structures depending on how the links are entered. Ideally you want something that looks like an asterisk, with one group leader as the center that links to everyone else.  Usually this is not the case - you end up with circles, trees, and other shapes that look like tangles of string. Here is a script I wrote that will take this and put into groups of patrons, using the person with the most connections as the leader (or if it is a circular link, the lowest account number is chosen.)  If one were to run this on facebook, it would put 99% of people in Tom's group (or whoever that default friend dude is) - but these are closed groups of seat-mates, so not everyone is linked. (seven seatmates to Kevin Bacon anyone?)

Anyway- here are the goods - this takes the relationship type as input.  This script assumes the same reciprocal and main relationship type for each link.
declare @rtype int

set @rtype = 5

create table #l (cltcode int, link int, lnkgrp int, lnkgrpleader int)

insert into #l (cltcode,link)
select relcltparentid, relcltrelationid from relationships where relfromtype = @rtype or reltotype = @rtype
union select relcltrelationid , relcltparentid from relationships where relfromtype = @rtype or reltotype = @rtype

update #l set lnkgrp = case when cltcode < link then cltcode else link end
declare @i int
set @i = 0
while (
exists (select 1 from #l l1 inner join (select cltcode, lnkgrp from #l) as l2 on l2.cltcode = l1.link and l2.lnkgrp < l1.lnkgrp)
or exists (select 1 from #l l1 inner join (select link, lnkgrp from #l) as l2 on l2.link = l1.cltcode and l2.lnkgrp < l1.lnkgrp)
) and @i < 10 --limit loop to 10 iterations (degrees of separation).
begin
update l1 set lnkgrp = l2.lnkgrp
from #l l1 inner join (select cltcode, lnkgrp from #l) as l2 on l2.cltcode = l1.link and l2.lnkgrp < l1.lnkgrp

update l1 set lnkgrp = l2.lnkgrp
from #l l1 inner join (select link, lnkgrp from #l) as l2 on l2.link = l1.cltcode and l2.lnkgrp < l1.lnkgrp
set @i = @i + 1
end

update #l set lnkgrpleader = lm.gpwinner
from #l as l1 inner join
(select l1.cltcode,
(select top 1 cltcode from #l as l2 where l2.lnkgrp = l1.lnkgrp group by cltcode order by count(*) desc, cltcode ) as gpwinner
from #l as l1)
as lm on lm.cltcode = l1.cltcode

create table #gl (acct int , lnkgrp int, isgroupleader bit, lname varchar(999), fname varchar(999))
insert #gl (acct, lnkgrp, isgroupleader)
select cltcode, lnkgrp,
case when cltcode = lnkgrpleader then 1 else 0 end as isgroupleader
from #l group by cltcode, lnkgrp, case when cltcode = lnkgrpleader then 1 else 0 end
order by lnkgrp, case when cltcode = lnkgrpleader then 1 else 0 end desc, cltcode

update #gl set lname = cltsurname, fname = cltfirstname
from #gl inner join clients on cltcode = acct

--select acct from #gl group by acct having count(*) > 1

select * from #gl

drop table #gl, #l

Remote Ticketing and Printing ~~~ tips and tricks

I recently had to look into options for doing off-site ticketing, with printers this time. There are many to do this. I tested quite a few of them, and here are the results:
There are many ways to connect and use PE: VPN, RDP, open the application ports on the firewall and do a public NAT for your PE app server (I didn't test this, since it could be a security risk). The option I settled on was RDP, which supports redirection of printers and ports (see next section for printing...) Testing over a VPN connection I noticed a sluggishness in PE - which I assume is due to the network latency. There is not much data going back and fourth, but instead hundreds of little requests to the app server every minute. When you add a 100 millisecond latency to each request, it really adds up to a bad user experience.  I assume that opening the application on the firewall would have the same results.  So - RDP it is then.  RDP is only sending bitmap image data, which may be more data, but the application will run much faster. I use a SSL-VPN 2000 from sonicwall to manage the connectivity, and a couple of XP virtual machines as the hosts.  The bandwidth used per workstation comes to about (from the host network's point of view) 6KBps ingress and 1.5KBps egress (or 48 Kbps up / 12Kbps down if you count in bits).  These are average numbers - the traffic fluctuates with use, so plan on twice the total for a good experience.

Printing
Now for the tricky part, as your ticket printer probably has a parallel port, and your laptop probably does not have a parallel port.  In addition, the "forward printers" feature in RDP will not create a permanent printer name for tix_printserver to use

1. First, I got an adapter cable to connect my boca miniMB as a usb printer. 
2. In order to print anything you need to install the printer on the local laptop.  I use the boca drivers for this, but it may not matter which driver you use, since it is just going to forward raw port data to the printer. 
3. After setting up the usb printer, go to printer properties > ports > check the "enable printer pooling" checkbox, and  also check the "com 3" port.  There will now be 2 ports checked; USB001 as the first, and COM3. 
4. Now on the host machine (the one you RDP to) create a local printer, and have it point to COM3.  Use the printer driver for the ticket printer.  
5. Configure your RDP terminal services connection to "forward ports".  No need to "forward printers", since we are doing it manually.
6. Print a test ticket on the host printer and also the local laptop printer.  Both should print a test ticket.
7. On the host machine, start tix_printserver.exe, and point it at the printer you just created.

 Other notes:

• If COM3 is forwarding, check device manager on the host, and disable the COM3 port if present.  This will force the host OS to send it to the RDP COM3 instead of a physical COM3.
• If your laptop has an LPT port, you can set the host to print to LPT1, and it will send it directly to the laptop's lpt1 port (without the need to install a printer on the laptop)  You will need to disable LPT1 on the host device manager > ports.

The only configuration required from users after all this setup is to start the tix_printserver.exe application once logged in.  (see BB docs about tix print server, the syntax is something like "C:\Program Files\Blackbaud\The Patron Edge\TIX_PrintServer.exe" /id36 /a /min) The salespoint configured for the host machine will be set to point to the printer you just set up, or perhaps another host's printer if you wanted to share printers.

Jumpy seat map scrolling in IE9

I just got the IE9 download, and noticed that the seat selection screen on PEO is jumpy when you are trying to scroll through an area. (not as jumpy as Amy here...) It seems that it is limited to IE9 and also limited to this one screen, and also limited to seating areas that have more than, say, 400 seats.

The reason for the jumpyness - the seats are actually little gif images, which are put through an image filter, which will rotate them to the top, left, right, bottom depending on where you say the stage is. When IE9 does this for 1000 little .gifs, it tends to freeze up a bit. firefox, IE8, and Chrome had no problems. The javascript has the following lines;

var img_filter = 'progid:DXImageTransform.Microsoft.BasicImage(rotation=2)';
...


Taking the filter property out of the image tag in seats_plan.asp line 226 fixed it right up, and actually had the page loading faster in all browsers. You may have to rotate all of the little seat images with an image editor so that they point toward the "stage".

Series House List

You want to see the seats that have been sold in a series, with a few details. Maybe even see the seats that have been sold on a linked series (sharing a required event). Well, now you can see the series nest all at once in a list format. You will still no be able to see the universe in its entirety unless you look into the tardis, though.

create procedure moa_series_houselist (@s int, @show_overlap bit)
as

/*
declare @s int, @show_overlap bit
set @s = 775
set @show_overlap = 1
*/

create table #srhl (
chaircode int primary key ,
sec varchar(99),
row varchar(99),
seat varchar(99),
account int,
transact int,
pricetype varchar(99),
pricelevel varchar(99),
cltname varchar(99),
reservation_type varchar(99),
series varchar(99),
lock_info varchar(99)
)

declare @areas table (arcode int)

insert #srhl (chaircode,account ,transact ,reservation_type ,series, pricetype, pricelevel )
select proseatnum, prosubsnum, protransactnum, 'Seated', srdescr, sbtdescr, tadescr
from moa_pe..subsprogram inner join
moa_pe..serieses on srcode = proseriescode
inner join moa_pe..subscriptiontype on sbtcode = prosubstype
inner join moa_pe..tariff on tacode = propricelevel
where procurrentstatus = 10 and proseriescode = @s

if @show_overlap = 1
begin
insert @areas select sraarea from moa_pe..seriesareas where sraseries = @s

insert #srhl (chaircode,account ,transact ,reservation_type ,series, pricetype, pricelevel )
select a2.ahchaircode, prosubsnum, protransactnum, 'Seated - other series.', srdescr, sbtdescr, tadescr
from moa_pe..subsprogram inner join
moa_pe..serieses on srcode = proseriescode
inner join moa_pe..subscriptiontype on sbtcode = prosubstype
inner join moa_pe..tariff on tacode = propricelevel
inner join moa_pe..areachair a1 on proseatnum = ahchaircode
inner join moa_pe..areachair a2 on a1.ahparentseatid = a2.ahparentseatid
inner join @areas ar on ar.arcode = a2.ahareacode
where procurrentstatus = 10
and proseriescode in
(select l1.lseseriescode
from moa_pe..linkseriesevents l1
inner join moa_pe..linkseriesevents l2 on l1.lseeventcode = l2.lseeventcode
and l1.lseIsRequiered = 1 and l2.lseIsRequiered = 1
and l2.lseseriescode = @s
and l1.lseseriescode <> @s
)
and a2.ahchaircode not in (select chaircode from #srhl) --should not happen, but exclude anyway.
end

update #srhl set sec = ahshortarname, row = ahline, seat = ahchair
from #srhl inner join moa_pe..areachair on ahchaircode = chaircode

update #srhl set lock_info = isnull(clrdescr,'')
from #srhl
left outer join moa_pe..serieslockedseats on slsseries = @s and slschair = chaircode
left outer join moa_pe..colors on slsticktype = clrstatus

update #srhl set cltname = isnull(cltclientname,'?')
from #srhl left outer join moa_pe..clients on cltcode = account

select #srhl.*, ss.id as sortseatid, sr.id as sortrowid
from #srhl
left outer join sortseat ss on seatname = seat
left outer join sortrow as sr on rowname = row

drop table #srhl

Think Inside The Box

If you are using Packages and coupons, you probably have found that it can be difficult to see what is inside the package at a glance. This bit of sql will compress a package to a 2D dataset for easy viewing. Note - this will expand a show list to a list of events attached, but it will not do calculations on time-limitations. It only lists time limitations.

**updated 9/14/11 - old code had incorrect link from limitsetslink to packageitems.


declare @packageid int
set @packageid = 4

declare @item_type as table (code int, descr varchar(99))
insert @item_type (code, descr)
select 1,'Event'
union select 3,'Merchandise'
union select 4,'Series'
union select 5,'Memberships'
union select 6,'Events'
union select 7,'Shows'

declare @limittype as table (code int, descr varchar(99), source varchar(99))

insert @limittype (code, descr, source)
select 9,'Pricetype', 'pricetypes'
union select 10,'Subs Price Type', 'subscriptiontype'
union select 2,'Shows', 'shows'
union select 3,'Halls', 'halls'
union select 7,'Merchandise', 'merchandiseitems'
union select 8,'Price Levels', 'tariff'
union select 14,'Time Limitations', 'timelimitations'
union select 4,'Areas', 'areas'
union select 6,'Series', 'serieses'


declare @benefit_type table (code int, descr varchar(99))
insert @benefit_type (code, descr)
select 0,'No Benefit (qualifier)'
union select 1,'Nominal'
union select 2,'Percent'
union select 3,'Free Item'

declare @timelimitcat as table (code int, descr varchar(99))
insert @timelimitcat (code, descr)
select 1, 'Dates+Hours'
union select 2,'Days of Week + Hours'
union select 3,'Hours'

--select * from @item_type
--select * from @limittype
--select * from @benefit_type
--select * from @timelimitcat

SELECT
pgi_code,
pgi_BasketItemType,
isnull(it.descr ,'?') as item_type_descr,
pgi_MinItemsCount, pgi_MaxItemsCount,
pgi_BenefitType,
isnull(bt.descr,'?') as benefit_type_descr,
pgi_BenefitValue,
0 as pgi_PriceTypeForCommission,
'' as pricetype_for_commission,
lsl_LimitType,
isnull(limittype.descr,'?') as limit_type_descr,
lsl_LimitID,
lsl_AvailabilityType,
replicate(' ',50) as limit_descr,
isnull(eveventdate,'1/1/1900') as evdate
into #pkg
FROM
PKG_PackagesItems PKG_PackagesItems left outer JOIN
LimitSetsLink ON pgi_limitset = lsl_LimitSetID
left outer join @limittype as limittype on limittype.code = lsl_LimitType
left outer join @benefit_type as bt on bt.code = pgi_BenefitType
left outer join @item_type as it on it.code = pgi_BasketItemType
left outer join events on evshow = lsl_LimitID and lsl_limittype = 2
WHERE (pgi_Package = @packageid)
order by pgi_Package, pgi_code

update #pkg set limit_descr = 'All' where lsl_limittype in (10,9,2,3,7,8,4,6) and lsl_limitID = 0

update #pkg
set limit_descr = pctdescr
from #pkg
inner join PriceType ON lsl_limitID = pctCode
where lsl_limittype = 9

update #pkg
set limit_descr = sbtdescr
from #pkg
inner join subscriptiontype ON lsl_limitID = sbtCode
where lsl_limittype = 10

update #pkg
set limit_descr = left(shdescr,50)
from #pkg
inner join shows ON lsl_limitID = shcode
where lsl_limittype = 2

update #pkg
set limit_descr = left(haname,50)
from #pkg
inner join halls ON lsl_limitID = hacode
where lsl_limittype = 3

update #pkg
set limit_descr = left(srdescr,50)
from #pkg
inner join serieses ON lsl_limitID = srcode
where lsl_limittype = 6


update #pkg
set limit_descr = left(mitdescr,50)
from #pkg
inner join merchandiseitems ON lsl_limitID = mitcode
where lsl_limittype = 7

update #pkg
set limit_descr = left(tadescr,50)
from #pkg
inner join tariff ON lsl_limitID = tacode
where lsl_limittype = 8

update #pkg
set limit_descr =
case when lsl_availabilitytype = 1 then 'Available for: ' else 'Not Available for: ' end
+ left(ltg_description,50)
from #pkg
inner join LimitTimeGroup ON lsl_limitID = ltg_recordid
where lsl_limittype = 14


select * from #pkg
order by pgi_code, limit_type_descr, evdate, limit_descr

drop table #pkg



Profiles at a glance!

So, you have probably had to dig around in the profile editing program in PE to find who has access to what. If you have more than a couple profiles this can be time consuming.
I put together a bit of sql which helps greatly when testing new versions (and troubleshooting why someone does not have access to something when you do...)

This will sort all profile data by profile-setting-group, profile-setting, and then by profile:



declare @m table (grp varchar(99), node varchar(99), morder int, trail varchar(256), lastparent varchar(99) )

set nocount on
insert into @m
select menus.mnugroup,mnunode,mnuorder,menus.mnugroup , menus.mnugroup
from menus menus with(nolock) where mnunode not in (select mnugroup from menus menus with(nolock)) and mnuactive = 1 order by menus.mnugroup, mnuorder

while exists (select 1 from @m inner join menus menus with(nolock) on mnunode = lastparent and mnunodedepth > 0)
begin
update @m set lastparent = mnugroup, trail = mnugroup + ' > ' + trail
from @m inner join menus menus with(nolock) on mnunode = lastparent and mnunodedepth > 0
end


SELECT
Profiles.proDescr,
isnull(m.trail , case when prmformdescr like '%???%'then prmformname else prmformdescr end) as groupname,
case when prbcontroldescr = '' then prbcontrolname else prbControlDescr end as prbControlDescr ,
convert(int,priAvailable) as priAvailable
FROM
ProfileBase ProfileBase with(nolock) INNER JOIN
ProfileInfo ProfileInfo with(nolock) ON ProfileBase.prbFormName = ProfileInfo.priFormName AND ProfileBase.prbControlName = ProfileInfo.priControlName INNER JOIN
Profiles Profiles with(nolock) ON ProfileInfo.priProfile = Profiles.proCode left outer join
@m m on m.node = prbControlname left outer join
profilemenus profilemenus with(nolock) on prmformname = prbformname
WHERE (ProfileBase.prbAvailable = 1)
order by Profiles.proDescr, isnull(m.trail , case when prmformdescr like '%???%' then prmformname else prmformdescr end),
morder, ProfileBase.prbControlDescr


You will have this access mess sorted out in no time at all! maybe even print this out for PCI ocumentation!

Tips for building a CIM module in asp.net

**** NOTE: These functions re-written on 4/10/11. the upedated functions return more authorization data, and have a consistent naming scheme. I was going to use the sdk compiled code, but there was no method to create a profile and payment profile at the same time, resulting in twice the number of calls. This code will accomplish the same tasks that I need, and with fewer api calls. ******

Recently, I had to make an interface for the authorize.net CIM (Customer Information Management) web service, in order to store credit-card data off site in a pci-compliant manner. The authorize.net CIM service stores all kinds of customer and order data, but I only send it the info needed to charge a card (billing address, name, and card info). The steps in an average transaction are:

• send request to create customer and payment profile


• parse the response


• send request for transaction (charge, void, or refund)


• parse the response

Each communication and response is in the form of an xml file. The request is sent as an https post, and the response is sent back with the page request.

The first thing you will need is a function to send an https post request. The other functions will create an xml file in memory and write it to a string for the http post function. The http request returns an xml doc, which needs to be parsed, which is handled by some of the other fuinctions here.

Imports Microsoft.VisualBasic
Imports System.IO
Imports System.Xml
Imports System.Net
Imports weborders
Imports System.Text
Imports System.Web.HttpUtility

Public Class api

    Public Shared Function PostData(ByVal data As String, ByVal url As String) As String

        Dim response As String = ""
        Dim tmpstr As String = ""
        Try
            Dim request As HttpWebRequest = WebRequest.Create(url)
            request.Method = WebRequestMethods.Http.Post
            request.ContentType = "text/xml"
            request.ContentLength = data.Length
            request.Timeout = 25000
            Dim writer As New StreamWriter(request.GetRequestStream(), System.Text.Encoding.ASCII)
            writer.Write(data)
            writer.Close()

            Dim stream As Stream = request.GetResponse().GetResponseStream()
            Dim reader As New StreamReader(stream)
            tmpstr = reader.ReadToEnd()
            response = tmpstr
        Catch ex As Exception
            response = ex.Message
        End Try
        ''get rid of namespace
        response = response.Replace("xmlns:", "noname")
        response = response.Replace("xmlns", "noname")
        Return response
    End Function

    Public Shared Function xml_MerchAuth(ByRef xmlobj As XmlTextWriter) As Boolean
        Dim loginname As String = ConstClass.ReadAppSettings("api_loginname")
        Dim transactkey As String = ConstClass.ReadAppSettings("api_transactkey")
        xmlobj.WriteStartElement("merchantAuthentication")
        xmlobj.WriteElementString("name", loginname)
        xmlobj.WriteElementString("transactionKey", transactkey)
        xmlobj.WriteEndElement()
        Return True
    End Function

    Public Shared Function apicall_get_response_type(ByRef xd As XmlDocument) As String
        Dim t As String = "NA"
        Try
            t = xd.SelectSingleNode("/*").Name
        Catch ex As Exception
        End Try
        Return t
    End Function
    Public Shared Function apicall_get_messages_resultCode(ByRef xd As XmlDocument) As String
        Dim t As String = ""
        Try
            t = xd.SelectSingleNode("/*/messages/resultCode").InnerText
        Catch
        End Try
        Return t
    End Function
    Public Shared Function apicall_get_messages_message_code(ByRef xd As XmlDocument) As String
        Dim t As String = ""
        Try
            t = xd.SelectSingleNode("/*/messages/message/code").InnerText
        Catch ex As Exception
        End Try
        Return t
    End Function

    Public Shared Function apicall_get_messages_message_text(ByRef xd As XmlDocument) As String
        Dim t As String = ""
        Try
            t = xd.SelectSingleNode("/*/messages/message/text").InnerText
        Catch ex As Exception
        End Try
        Return t
    End Function
    Public Shared Function apicall_get_customerProfileId(ByRef xd As XmlDocument) As String
        Dim t As String = ""
        Try
            t = xd.SelectSingleNode("/createCustomerProfileResponse/customerProfileId").InnerText
        Catch ex As Exception
        End Try
        Return t
    End Function
    Public Shared Function apicall_get_PayProfileId(ByRef xd As XmlDocument) As String
        Dim t As String = ""
        Try
            t = xd.SelectSingleNode("/*/customerPaymentProfileIdList/numericString").InnerText
        Catch ex As Exception
        End Try
        Return t
    End Function
    Public Shared Function apicall_get_tran_directResponse(ByRef xd As XmlDocument) As String
        Dim t As String = ""
        Try
            t = xd.SelectSingleNode("/createCustomerProfileTransactionResponse/directResponse").InnerText
        Catch ex As Exception
        End Try
        Return t
    End Function
    Public Shared Function apicall_get_profile_directResponse(ByRef xd As XmlDocument) As String
        Dim t As String = ""
        Try
            t = xd.SelectSingleNode("/*/validationDirectResponseList/string").InnerText
        Catch ex As Exception
        End Try
        Return t
    End Function
    Public Shared Function apicall_get_udprofile_directResponse(ByRef xd As XmlDocument) As String
        Dim t As String = ""
        Try
            t = xd.SelectSingleNode("/*/validationDirectResponse").InnerText
        Catch ex As Exception
        End Try
        Return t
    End Function


    Public Shared Function apicall_create_profile(ByVal orderid As Integer, ByVal fname As String, _
    ByVal lname As String, ByVal company As String, ByVal address As String, ByVal city As String, _
    ByVal state As String, ByVal zip As String, ByVal country As String, ByVal phone As String, _
    ByVal fax As String, ByVal cardnumber As String, ByVal exp As String, _
    ByVal cardcode As String, ByVal bank_routing As String, ByVal bank_account As String, ByVal bank_nameonacct As String, _
    ByVal bankName As String, ByVal accountType As String, _
    ByVal is_bank As Boolean, ByVal validate As Boolean, ByRef out_message As String, ByRef out_code As String) _
    As Boolean 'will return "I00001" or error text if error.
        'out message returns reason for decline, or success message
        'out_code returns response code (I00001, etc)

        Dim vmode As String = "testMode" 'will only validate fields
        If validate Then vmode = "liveMode" 'will send test transaction to processor for 0.01

        If orderid = Nothing Then
            out_code = "null"
            out_message = "order id is not set"
            Return False
        End If
        If is_bank = False And cardnumber.Length < 13 Or cardnumber.Length > 16 Then
            out_code = "ER"
            out_message = "Card number length is not valid"
            Return False
        End If
        If is_bank = True And (bank_routing.Length <> 9 Or bank_account.Length < 5 Or bank_account.Length > 17 Or bank_nameonacct.Length = 0) Then
            out_code = "ER"
            out_message = "Bank account information is not valid."
            Return False
        End If

        Dim RandomClass As New Random()
        Dim last4 As String = ""
        If is_bank Then
            last4 = bank_account.Substring(bank_account.Length - 4, 4)
        Else
            last4 = cardnumber.Substring(cardnumber.Length - 4, 4)
        End If
        If bank_nameonacct.Length > 22 Then bank_nameonacct = bank_nameonacct.Substring(0, 22)

        Dim callstr As String = ""
        Dim memory_stream As New MemoryStream
        Dim writer As New XmlTextWriter(memory_stream, System.Text.Encoding.UTF8)

        Try
            writer.Formatting = Formatting.Indented
            writer.Indentation = 4
            writer.WriteStartDocument(True)

            writer.WriteStartElement("createCustomerProfileRequest")
            writer.WriteAttributeString("xmlns", "AnetApi/xml/v1/schema/AnetApiSchema.xsd")
            api.xml_MerchAuth(writer) 'this adds the xml that will authenticate to auth.net
            writer.WriteStartElement("profile")
            writer.WriteElementString("merchantCustomerId", orderid.ToString)
            writer.WriteElementString("description", RandomClass.Next().ToString)
            writer.WriteStartElement("paymentProfiles")
            writer.WriteStartElement("billTo")
            writer.WriteElementString("firstName", fname)
            writer.WriteElementString("lastName", lname)
            writer.WriteElementString("company", company)
            writer.WriteElementString("address", address)
            writer.WriteElementString("city", city)
            writer.WriteElementString("state", state)
            writer.WriteElementString("zip", zip)
            writer.WriteElementString("country", country)
            writer.WriteElementString("phoneNumber", phone)
            writer.WriteElementString("faxNumber", fax)
            writer.WriteEndElement() 'end for billTo
            writer.WriteStartElement("payment")

            If is_bank = False Then
                writer.WriteStartElement("creditCard")
                writer.WriteElementString("cardNumber", cardnumber)
                writer.WriteElementString("expirationDate", exp) ''YYYY-MM
                If cardcode.Length > 0 Then writer.WriteElementString("cardCode", cardcode) ''cvv/cvv2...
                writer.WriteEndElement() 'end for creditcard
            Else
                writer.WriteStartElement("bankAccount")
                writer.WriteElementString("accountType", accountType) ''(checking|savings)
                writer.WriteElementString("bankName", bankName) ''(checking|savings)

                writer.WriteElementString("routingNumber", bank_routing)
                writer.WriteElementString("accountNumber", bank_account)
                writer.WriteElementString("nameOnAccount", bank_nameonacct)
                writer.WriteElementString("echeckType", "WEB") ''(CCD|PPD|TEL|WEB)
                writer.WriteEndElement() 'end for bankAcount
            End If

            writer.WriteEndElement() 'end for payment
            writer.WriteEndElement() 'end for payprofiles
            writer.WriteEndElement() 'end for profile
            writer.WriteElementString("validationMode", vmode)
            writer.WriteEndElement() 'end for createcustomer...
            writer.WriteEndDocument()
            writer.Flush()

            Dim stream_reader As New StreamReader(memory_stream)
            memory_stream.Seek(0, SeekOrigin.Begin)
            callstr = stream_reader.ReadToEnd()
            writer.Close()
        Catch ex As Exception
            ConstClass.record_error("api.vb: apicall_create_profile, generate xml", ex.ToString)
            out_code = "ER"
            out_message = "Error constructing XML"
            Return False
        End Try


        Dim response As String = "" 'string containing xml response
        Dim xd As XmlDocument = New XmlDocument()
        Try
            response = PostData(callstr, ConstClass.ReadAppSettings("api_url"))
            If response = "Unable to connect to the remote server" Then
                out_code = "TO"
                out_message = response
                Return False
            Else
                xd.LoadXml(response)
            End If
        Catch ex As Exception
            ConstClass.record_error("api.vb: apicall_create_profile, get response", ex.ToString)
            out_code = "ER"
            out_message = "Unable to load response."
            Return False
        End Try

        Dim responsetype As String = "", profileid As Integer = 0, payprofileid As Integer = 0
        Dim authmessage As String = ""
        Dim auth_response_code As String = "", auth_response_reason_code As String = "", db_reason_text As String = ""
        Try
            responsetype = apicall_get_response_type(xd)
            out_code = apicall_get_messages_message_code(xd)
            out_message = "Message: " + apicall_get_messages_message_text(xd)
            authmessage = apicall_get_profile_directResponse(xd)
            If authmessage <> "" Then
                auth_response_code = authmessage.Split(",")(0) '1
                auth_response_reason_code = authmessage.Split(",")(2) '3
                out_message = authmessage.Split(",")(3) '4

                'get translation for common errors from db;
                Dim qta As New weborders_apiTableAdapters.QueriesTableAdapter
                db_reason_text = qta.get_decline_message(auth_response_code, auth_response_reason_code)
                If db_reason_text <> "" Then out_message = db_reason_text

            End If
        Catch ex As Exception
            ConstClass.record_error("api.vb: apicall_create_profile, parse response", ex.ToString)
            out_code = "ER"
            out_message = "Unable to parse response."
            Return False
        End Try

        If responsetype = "ErrorResponse" Then Return False

        'record new profile/payprofileid's if not error
        If out_code = "I00001" Then
            Try
                profileid = api.apicall_get_customerProfileId(xd)
                payprofileid = api.apicall_get_PayProfileId(xd)
            Catch ex As Exception
                ConstClass.record_error("api.vb: apicall_create_profile, parse profileid", ex.ToString)
                out_code = "ER"
                out_message = "Unable to parse profile_ids"
                Return False
            End Try
            Try
                Dim cctype As Integer = -1, ccdigit As String = ""
                If is_bank = False Then
                    ccdigit = cardnumber.Substring(0, 1)
                    If cardnumber.Length = 14 And ccdigit = "3" Then
                        cctype = 5 'DinersClub
                    ElseIf ccdigit = "4" Then
                        cctype = 1 'visa
                    ElseIf ccdigit = "5" Then
                        cctype = 2 'MC
                    ElseIf ccdigit = "3" Then
                        cctype = 3 'Amex
                    ElseIf ccdigit = "6" Then
                        cctype = 4 'Discover
                    End If
                Else
                    cctype = 101 'EFT bank transfer
                End If
                If exp = "" Then exp = "3000-01"
                Dim tc As New webordersTableAdapters.ordersTableAdapter
                tc.UpdateQuery_cim(1, last4, exp, profileid, payprofileid, cctype, orderid)
            Catch ex As Exception
                ConstClass.record_error("api.vb: apicall_create_profile, record response", ex.ToString)
                out_code = "ER"
                out_message = "Unable to record response."
                Return False
            End Try
        End If
        If out_code = "" Then out_code = "ER"

        If out_code = "I00001" Then
            Return True
        Else
            Return False 'if not I00001, the profile did not validate.
        End If
    End Function

    Public Shared Function apicall_update_payment_profile(ByVal profileid As Integer, ByVal payprofileid As Integer, _
                                ByVal userid As Integer, ByVal newccnum As String, ByVal newexp As String, ByVal orderid As Integer, _
                                ByVal is_bank As Boolean, ByVal bank_account As String, ByVal bank_routing As String, ByVal bank_nameonacct As String, _
                                ByVal validate As Boolean, ByRef out_code As String, ByRef out_message As String) As Boolean

        Dim callstr As String = ""
        Dim memory_stream As New MemoryStream
        Dim writer As New XmlTextWriter(memory_stream, System.Text.Encoding.UTF8)
        Dim vmode As String = "testMode" 'will only validate fields
        If validate Then vmode = "liveMode" 'will send test transaction to processor for 0.01

        If bank_nameonacct.Length > 22 Then bank_nameonacct = bank_nameonacct.Substring(0, 22)

        Dim last4 As String = ""
        If is_bank = True Then
            last4 = bank_account.Substring(bank_account.Length - 4, 4)
        Else
            last4 = newccnum.Substring(newccnum.Length - 4, 4)
        End If

        Try
            writer.Formatting = Formatting.Indented
            writer.Indentation = 4
            writer.WriteStartDocument(True)

            writer.WriteStartElement("updateCustomerPaymentProfileRequest")
            writer.WriteAttributeString("xmlns", "AnetApi/xml/v1/schema/AnetApiSchema.xsd")
            api.xml_MerchAuth(writer) 'this adds the xml that will authenticate to auth.net

            writer.WriteElementString("customerProfileId", profileid)
            writer.WriteStartElement("paymentProfile")
            writer.WriteStartElement("payment")

            If is_bank = False Then
                writer.WriteStartElement("creditCard")
                writer.WriteElementString("cardNumber", newccnum)
                writer.WriteElementString("expirationDate", newexp) ''YYYY-MM
                writer.WriteEndElement() 'end for creditcard
            Else
                writer.WriteStartElement("bankAccount")
                'writer.WriteElementString("accountType", accountType) ''(checking|savings)
                writer.WriteElementString("routingNumber", bank_routing)
                writer.WriteElementString("accountNumber", bank_account)
                writer.WriteElementString("nameOnAccount", bank_nameonacct)
                writer.WriteElementString("echeckType", "WEB") ''(CCD|PPD|TEL|WEB)
                writer.WriteEndElement() 'end for bankAcount
            End If

            writer.WriteEndElement() 'end for payment
            writer.WriteElementString("customerPaymentProfileId", payprofileid)
            writer.WriteEndElement() 'end for paymentProfile
            writer.WriteElementString("validationMode", vmode)
            writer.WriteEndElement() 'end for root element...
            writer.WriteEndDocument()
            writer.Flush()

            Dim stream_reader As New StreamReader(memory_stream)
            memory_stream.Seek(0, SeekOrigin.Begin)
            callstr = stream_reader.ReadToEnd()
            writer.Close()
        Catch ex As Exception
            ConstClass.record_error("api.vb: apicall_update_payment_profile, generate xml", ex.ToString)
            out_code = "ER"
            out_message = "error generating xml"
            Return False
        End Try

        Dim responsetype As String = ""
        Dim xd As XmlDocument = New XmlDocument()
        Dim response As String = "", authmessage As String = ""
        Dim auth_response_code As String = "", auth_response_reason_code As String = "", db_reason_text As String = ""
        Dim qta As New weborders_apiTableAdapters.QueriesTableAdapter
        Try
            response = PostData(callstr, ConstClass.ReadAppSettings("api_url"))
            xd.LoadXml(response)
            responsetype = api.apicall_get_response_type(xd)
            out_code = apicall_get_messages_message_code(xd)
            out_message = api.apicall_get_messages_message_text(xd)
            authmessage = apicall_get_udprofile_directResponse(xd)
            If authmessage <> "" Then
                auth_response_code = authmessage.Split(",")(0) '1
                auth_response_reason_code = authmessage.Split(",")(2) '3
                out_message = authmessage.Split(",")(3) '4
            End If

            'get translation for common errors from db;
            db_reason_text = qta.get_decline_message(auth_response_code, auth_response_reason_code)
            If db_reason_text <> "" Then out_message = db_reason_text

        Catch ex As Exception
            ConstClass.record_error("api.vb: apicall_update_payment_profile, get response", ex.ToString)
            out_message = "Error loading / parsing response."
            out_code = "ER"
            Return False
        End Try

        If responsetype = "ErrorResponse" Then Return False

        'record new data if not error
        If out_code = "I00001" Then
            Try
                Dim tc As New webordersTableAdapters.ordersTableAdapter
                Dim cctype As Integer = -1, ccdigit As String = ""

                If is_bank = False Then
                    ccdigit = newccnum.Substring(0, 1)
                    If ccdigit = "X" Or newccnum.Length < 12 Then 'updating only exp
                        tc.UpdateCim_ccExpirationDt(newexp, orderid)
                    Else 'updating card number too
                        If newccnum.Length = 14 And ccdigit = "3" Then
                            cctype = 5 'DinersClub
                        ElseIf ccdigit = "4" Then
                            cctype = 1 'visa
                        ElseIf ccdigit = "5" Then
                            cctype = 2 'MC
                        ElseIf ccdigit = "3" Then
                            cctype = 3 'Amex
                        ElseIf ccdigit = "6" Then
                            cctype = 4 'Discover
                        End If
                        tc.Update_cim_cc_payprofile(last4, newexp, cctype, orderid)
                    End If
                Else 'update for bank
                    tc.Update_cim_cc_payprofile(last4, CDate("1/1/3000"), 101, orderid)
                End If
            Catch ex As Exception
                ConstClass.record_error("api.vb: apicall_update_payment_profile, record response", ex.ToString)
                out_message = "Error recording response."
                out_code = "ER"
                Return False
            End Try
        End If
        If out_code = "I00001" Then
            Return True
        Else
            Return False
        End If
    End Function


    Public Shared Function apicall_create_charge_request(ByVal profileid As Integer, ByVal payprofileid As Integer, _
                            ByVal amount As Decimal, ByVal ccv As String, ByVal userid As Integer, ByVal orderid As Integer, _
                            ByVal payscheduleid As Integer, ByVal cctype As Integer, ByVal last4 As String, _
                            ByRef out_code As String, ByRef out_message As String) As Boolean

        Dim callstr As String = "", responsetype As String = ""
        Dim authcode As String = "", authmessage As String = ""
        Dim authtransid As String = "", authpaymethod As String = "", paymethod As Integer = 0
        Dim xd As XmlDocument = New XmlDocument()
        Dim response As String = ""

        Dim memory_stream As New MemoryStream
        Dim writer As New XmlTextWriter(memory_stream, _
            System.Text.Encoding.UTF8)
        Try
            writer.Formatting = Formatting.Indented
            writer.Indentation = 4
            writer.WriteStartDocument(True)

            writer.WriteStartElement("createCustomerProfileTransactionRequest")
            writer.WriteAttributeString("xmlns", "AnetApi/xml/v1/schema/AnetApiSchema.xsd")
            api.xml_MerchAuth(writer) 'this adds the xml that will authenticate to auth.net
            writer.WriteStartElement("transaction")
            writer.WriteStartElement("profileTransAuthCapture")
            writer.WriteElementString("amount", amount)
            writer.WriteElementString("customerProfileId", profileid)
            writer.WriteElementString("customerPaymentProfileId", payprofileid)
            writer.WriteElementString("recurringBilling", "false")
            If ccv.Length > 2 Then
                writer.WriteElementString("cardCode", ccv)
            End If
            writer.WriteEndElement() 'end ProfileTransAuthCapture
            writer.WriteEndElement() 'end transaction
            writer.WriteEndElement() 'end for base
            writer.WriteEndDocument()
            writer.Flush()
            'dump xml mem stream into string
            Dim stream_reader As New StreamReader(memory_stream)
            memory_stream.Seek(0, SeekOrigin.Begin)
            callstr = stream_reader.ReadToEnd()
            writer.Close()
        Catch ex As Exception
            ConstClass.record_error("api.vb: apicall_create_tran_request, generate xml portion", ex.ToString)
            out_code = "ER"
            out_message = "error generating xml."
            Return False
        End Try

        'send api request

        Try
            response = PostData(callstr, ConstClass.ReadAppSettings("api_url"))
            If response = "Unable to connect to the remote server" Then
                out_code = "TO"
                out_message = response
                Return False
            Else
                xd.LoadXml(response)
            End If
        Catch ex As Exception
            ConstClass.record_error("api.vb: apicall_create_tran_request, get response portion", ex.ToString)
            out_code = "ER"
            out_message = "error getting response ."
            Return False
        End Try

        Try
            responsetype = api.apicall_get_response_type(xd)

            out_code = api.apicall_get_messages_message_code(xd)
            out_message = api.apicall_get_messages_message_text(xd)
            authmessage = apicall_get_tran_directResponse(xd)
            If responsetype = "ErrorResponse" Then Return False

            Dim auth_response_code As String = "", auth_response_reason_code As String = "", db_reason_text As String = ""
            If authmessage <> "" Then
                auth_response_code = authmessage.Split(",")(0) '1
                auth_response_reason_code = authmessage.Split(",")(2) '3
                out_message = authmessage.Split(",")(3) '4
                authcode = authmessage.Split(",")(4) ''vb.net has 0 based array - auth is 5th record 
                authtransid = authmessage.Split(",")(6) '7
                authpaymethod = authmessage.Split(",")(10) '11

                'get translation for common errors from db;
                Dim qta As New weborders_apiTableAdapters.QueriesTableAdapter
                db_reason_text = qta.get_decline_message(auth_response_code, auth_response_reason_code)
                If db_reason_text <> "" Then out_message = db_reason_text

                If authpaymethod = "CC" Then
                    paymethod = 1
                End If
                If authpaymethod = "ECHECK" Then
                    paymethod = 2
                End If

                If out_code = "I00001" Then
                    Dim pl As New webordersTableAdapters.payments_logTableAdapter
                    pl.InsertCharge(orderid, profileid, payprofileid, payscheduleid, amount, _
                        True, authcode, authmessage, userid, authtransid, paymethod, False, 0, 0, cctype, last4)
                    If payscheduleid > 0 Then
                        Dim tps As New webordersTableAdapters.payments_scheduledTableAdapter
                        tps.UpdateStatusById(1, payscheduleid)
                    End If
                End If
            End If
        Catch ex As Exception
            ConstClass.record_error("api.vb: function apicall_create_tran_request, parse xml portion", ex.ToString)
            out_code = "ER"
            out_message = "Error parsing response."
            Return False
        End Try

        If out_code = "I00001" Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Shared Function _
        apicall_create_void_request(ByVal userid As Integer, ByVal gateway_id As Long, ByVal paymentid As Integer, _
                                        ByVal payscheduleid As Integer, ByRef out_code As String, ByRef out_message As String) As Boolean
        Dim refid As String = "" ''dont have a use for it yet...
        Dim callstr As String = ""
        Dim memory_stream As New MemoryStream
        Dim responsetype As String = ""
        Try
            Dim writer As New XmlTextWriter(memory_stream, _
                System.Text.Encoding.UTF8)

            writer.Formatting = Formatting.Indented
            writer.Indentation = 4
            writer.WriteStartDocument(True)

            writer.WriteStartElement("createCustomerProfileTransactionRequest")
            writer.WriteAttributeString("xmlns", "AnetApi/xml/v1/schema/AnetApiSchema.xsd")
            api.xml_MerchAuth(writer) 'this adds the xml that will authenticate to auth.net
            writer.WriteStartElement("transaction")
            writer.WriteStartElement("profileTransVoid")
            writer.WriteElementString("transId", gateway_id)
            writer.WriteEndElement() 'profileTransvoid
            writer.WriteEndElement() 'transaction
            writer.WriteEndElement() 'createcustomer...
            writer.WriteEndDocument()
            writer.Flush()
            Dim stream_reader As New StreamReader(memory_stream)
            memory_stream.Seek(0, SeekOrigin.Begin)
            callstr = stream_reader.ReadToEnd()
            writer.Close()
        Catch ex As Exception
            ConstClass.record_error("api.vb: function apicall_create_void_request, xml generate request portion", ex.ToString)
            out_code = "ER"
            out_message = "Error generating xml."
            Return False
        End Try

        Dim xd As XmlDocument = New XmlDocument()
        Dim response As String = ""

        Try
            'send api request
            response = PostData(callstr, ConstClass.ReadAppSettings("api_url"))

            If response = "Unable to connect to the remote server" Then
                out_code = "TO"
                out_message = response
                Return False
            Else
                xd.LoadXml(response)
            End If
        Catch ex As Exception
            ConstClass.record_error("api.vb: function apicall_create_void_request, response portion", ex.ToString)
            out_code = "ER"
            out_message = "Error loading response."
            Return False
        End Try

        Try
            out_code = apicall_get_messages_message_code(xd)
            out_message = apicall_get_messages_message_text(xd)
            If out_code = "I00001" Then
                Dim tc As New webordersTableAdapters.payments_logTableAdapter
                tc.UpdateVoid(userid, paymentid)
                If payscheduleid > 0 Then
                    Dim psta As New webordersTableAdapters.payments_scheduledTableAdapter
                    psta.UpdateStatusById(0, payscheduleid)
                End If
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            ConstClass.record_error("api.vb: function apicall_create_void_request, parse response portion", ex.ToString)
            out_code = "ER"
            out_message = "Error parsing response."
            Return False
        End Try
    End Function

    Public Shared Function _
        apicall_create_refund_request(ByVal orderid As Integer, ByVal userid As Integer, ByVal gateway_id As Long, ByVal paymentid As Integer, _
                                        ByVal payscheduleid As Integer, ByVal profileid As Integer, ByVal payprofileid As Integer, _
                                        ByVal amount As Decimal, ByRef out_code As String, ByRef out_message As String) As Boolean

        Dim callstr As String = ""
        Dim memory_stream As New MemoryStream
        Dim writer As New XmlTextWriter(memory_stream, System.Text.Encoding.UTF8)
        Dim response As String = "", responsetype As String = ""
        Dim xd As XmlDocument = New XmlDocument()
        Dim authcode As String = ""
        Dim authtransid As String = "", paymethod As Integer = 0
        Try
            writer.Formatting = Formatting.Indented
            writer.Indentation = 4
            writer.WriteStartDocument(True)
            writer.WriteStartElement("createCustomerProfileTransactionRequest")
            writer.WriteAttributeString("xmlns", "AnetApi/xml/v1/schema/AnetApiSchema.xsd")
            api.xml_MerchAuth(writer) 'this adds the xml that will authenticate to auth.net
            writer.WriteStartElement("transaction")
            writer.WriteStartElement("profileTransRefund")
            writer.WriteElementString("amount", amount)
            writer.WriteElementString("customerProfileId", profileid)
            writer.WriteElementString("customerPaymentProfileId", payprofileid)
            writer.WriteElementString("transId", gateway_id) 'original transaction id
            writer.WriteEndElement() 'profileTransRefund
            writer.WriteEndElement() 'transaction
            writer.WriteEndElement() 'createcustomer...
            writer.WriteEndDocument()
            writer.Flush()
            Dim stream_reader As New StreamReader(memory_stream)
            memory_stream.Seek(0, SeekOrigin.Begin)
            callstr = stream_reader.ReadToEnd()
            writer.Close()
        Catch ex As Exception
            ConstClass.record_error("api.vb: function apicall_create_refund_request, generate xml portion", ex.ToString)
            out_code = "ER"
            out_message = "Error generating xml."
            Return False
        End Try
        Try
            'send api request
            response = PostData(callstr, ConstClass.ReadAppSettings("api_url"))
            If response = "Unable to connect to the remote server" Then
                out_code = "TO"
                out_message = response
                Return False
            Else
                xd.LoadXml(response)
            End If
        Catch ex As Exception
            ConstClass.record_error("api.vb: function apicall_create_refund_request, get response portion", ex.ToString)
            out_code = "ER"
            out_message = "Error loading response."
            Return False
        End Try

        Dim auth_response_code As String = "", auth_response_reason_code As String = "", db_reason_text As String = ""
        Dim qta As New weborders_apiTableAdapters.QueriesTableAdapter
        Try
            out_code = api.apicall_get_messages_message_code(xd)
            out_message = api.apicall_get_messages_message_text(xd)
            Dim authmessage As String = apicall_get_tran_directResponse(xd)
            If authmessage <> "" Then
                auth_response_code = authmessage.Split(",")(0) '1
                auth_response_reason_code = authmessage.Split(",")(2) '3
                out_message = authmessage.Split(",")(3) '4 = reason for decline
                authcode = authmessage.Split(",")(4) ''vb.net has 0 based array - auth is 5th record 
                authtransid = authmessage.Split(",")(6) '7

                db_reason_text = qta.get_decline_message(auth_response_code, auth_response_reason_code)
                If db_reason_text <> "" Then out_message = db_reason_text

            End If
            'I00001 is success, anything else is fail
            If out_code = "I00001" Then
                Dim tc As New webordersTableAdapters.payments_logTableAdapter
                Dim cc_type As Integer = tc.get_cctype_by_id(paymentid)
                Dim last4 As String = tc.get_last4_by_id(paymentid)
                tc.InsertCharge(orderid, profileid, payprofileid, payscheduleid, amount * -1, 1, authcode, authmessage, userid, authtransid, 1, 1, gateway_id, payscheduleid, cc_type, last4)
                If payscheduleid > 0 Then
                    Dim psta As New webordersTableAdapters.payments_scheduledTableAdapter
                    psta.UpdateStatusById(6, payscheduleid)
                End If
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            ConstClass.record_error("api.vb: function apicall_create_refund_request, parse response portion", ex.ToString)
            out_code = "ER"
            out_message = "Error parsing response."
            Return False
        End Try
    End Function


    '''''''''''''''''''''''''''''''''''''
    ''FUNCTIONS THAT ARE NOT USED (YET)''
    '''''''''''''''''''''''''''''''''''''

    Public Shared Function apicall_delete_profile(ByVal profileid As Integer, ByVal orderid As Integer) As Boolean
        Dim callstr As String = ""
        Dim memory_stream As New MemoryStream
        Dim writer As New XmlTextWriter(memory_stream, _
            System.Text.Encoding.UTF8)

        Try
            writer.Formatting = Formatting.Indented
            writer.Indentation = 4
            writer.WriteStartDocument(True)
            writer.WriteStartElement("deleteCustomerProfileRequest")
            writer.WriteAttributeString("xmlns", "AnetApi/xml/v1/schema/AnetApiSchema.xsd")
            api.xml_MerchAuth(writer) 'this adds the xml that will authenticate to auth.net
            writer.WriteElementString("customerProfileId", profileid)
            writer.WriteEndElement()
            writer.WriteEndDocument()
            writer.Flush()

            'dump xml mem stream into string
            Dim stream_reader As New StreamReader(memory_stream)
            memory_stream.Seek(0, SeekOrigin.Begin)
            callstr = stream_reader.ReadToEnd()
            writer.Close()
        Catch ex As Exception
            ConstClass.record_error("api.vb: apicall_delete_profile, generate xml portion", ex.ToString)
            Return False
        End Try
        Dim responsetype As String = ""
        Dim result As String = ""
        Dim responsemessagetext As String = ""
        Dim response As String = ""
        Dim xd As XmlDocument = New XmlDocument()
        Try
            'send api request
            response = PostData(callstr, ConstClass.ReadAppSettings("api_url"))
            xd.LoadXml(response)
            responsetype = api.apicall_get_response_type(xd)
        Catch ex As Exception
            ConstClass.record_error("api.vb: apicall_delete_profile, get response portion", ex.ToString)
            Return False
        End Try
        'record response data
        Try
            result = api.apicall_get_messages_resultCode(xd)
            responsemessagetext = api.apicall_get_messages_message_text(xd)
            If result <> "Error" Then
                Dim tc As New webordersTableAdapters.ordersTableAdapter
                tc.UpdateQuery_cim(0, "", "1/1/1900", 0, 0, 0, orderid)
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            ConstClass.record_error("api.vb: apicall_delete_profile, parse xml portion", ex.ToString)
            Return False
        End Try

    End Function

End Class

I have references in this example to record the results, which you will have to update to your own data table references of course.

Other Tips:

• Record the resulting authorize.net transaction ID as a BIGINT - they use numbers well over +2^31, which would cause arithmetic overflow errors if the data type is not right. (I guess they do alot of business?)


• ASP.net info: http://www.asp.net/get-started/


• debug by adding something to the post-function to record all calls and responses, but disable this for live runs (the call would contan card data...)


• If an xml node is not found, an error is thrown. There may be another way around this, but I like to use the Try...catch...end try to handle this.

Good luck!

PE version 3.3.4 and PCI compliance

I downloaded the latest PE release recently. After a bit of testing, It seems that blackbaud really has made this pci compliant. What this means is that:

--Any transfer of credit-card data is encrypted
--The credit card data is deleted from the database once the charge completes (except for the last four digits).

We have some business processes that depend on stored credit-card numbers, so we are waiting to upgrade until those are sorted out.

I did find that card numbers are stored in the pc-charge datbase (under heavy encryption). these numbers can only be accessed one at a time using teh trout id. It is a solution for an accountant that needs to refund a charge to an unknown patron, but would not be efficient for reporting purposes. I imagine that similar functions exist in other payment processing programs.

Raisers edge has a functionality for recurring payments in it's latest version. Perhaps a similar feature would benefit us PE users well? Be sure to put in a request if this is something that you can use.

If you decide to store card numbers outside of PE (on paper or otherwise) , your organization must comply with pci standards. Search for PCI "Self-Assesment Questionaire D" or SAQ-D to get the checklist required for such storage.

Happy PEing!

(ps, sorry about the data pollution. My last post about "clean coal" has been moved to another blog)

Soundscan

If any of you send files to nielsen for reporting cd sales, here is the format for the file that is sent. I may be the first to post this anywhere on the internet. how very exciting.


EAN DOWNLOAD DATA FORMAT

This is the format used by vendors who download data to the SoundScan system.

RECORDS/FIELDS CHARACTERS POSITION

STORE RETAIL SALES HEADER RECORD
Record Number (92) 2 1 - 2
Chain Number 5 3 - 7
Individual Store No. 5 8 - 12
Period End Date (YYMMDD) 6 13 - 18
Filler (Blanks) 2 19 - 20

STORE RETAIL SALES RECORD
Record Number (I3) 2 1 - 2
UPC Number of Selection 13 3 - 15
Position 3 = "0" if less the 13 digits
Positions 3-15 = EAN 13
Quantity Sold 5 16 - 20

STORE RETAIL SALES TRAILER RECORD
Record Number (94) 2 1 - 2
Number of Sales Records (I3's) 5 3 - 7
Number of Units Sold 7 8 - 14
Filler (Blanks) 6 15 - 20


20 Byte Records:
1 - 92 = Header Record per store
Multiple - I3 Data sales info records ( one for each EAN sold)
1 - 94 = Trailer Record with sum of items sold



FILE FORMAT - ASCII, DOS CR/LF, OEF AT END OF FILE

920090100010061029
I30000002900049-0001
I3000002900015900001
I3012342900106900001
I3123457994450200001
I3000007994455200001
94 5 4



List series locks in seat ranges

Aha!
I have rigged this one up using a sonic screwdriver, and it just might work.
This is like the single-event-lock-list rpt in a prev post, except for series locks:


CREATE procedure moa_series_lock_count_list (@series int, @season int, @scheme int)
as
declare @l table (event int, chair int, area int, row int, col int, vertical bit, rowname varchar(5), seatname varchar(5), sec varchar(5), ticktype int )

insert into @l (event, chair, area, row, col, vertical, rowname, seatname, sec, ticktype)
select slsseries, slschair, slsarea , ahrow, ahcol, 0, ahline, ahchair, left(ahshortarname,5), slsticktype
from moa_pe..serieslockedseats serieslockedseats with(nolock)
inner join moa_pe..areachair areachair with(nolock) on slschair = ahchaircode
where slsseries > 0 and slsticktype between 2000 and 3000
and slsseries in (select srcode from moa_pe..serieses sr with(nolock)
where (srcode = @series or @series = 0)
and (srseasons = @season or @season = 0)
and (srshowtype = @scheme or @scheme = 0) )
order by slsseries, slschair, slsarea, ahrow, ahcol

update @l set vertical = 1 where area in (select arcode from moa_pe..areas with(nolock) where arname like '%boxes%' )

declare @out table (event int, list varchar(255), row varchar(5), area int, cnt int, ticktype int, sec varchar(10) )

declare @ev int, @oldev int, @ar int, @oldar int, @row int, @oldrow int, @col int, @oldcol int
declare @sec varchar(5), @oldsec varchar(5) , @rowname varchar(5), @oldrowname varchar(5), @seat varchar(5), @oldseat varchar(5)
declare @ticktype int, @oldticktype int
declare @count int, @seatstr varchar(5000)



declare ch cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 0 order by event, area, row, col

open ch
fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col - 1
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @row <> @oldrow or @ticktype <> @oldticktype or @col <> @oldcol + 1)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close ch
deallocate ch


--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec

declare cv cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 1 order by event, area, col, row

open cv
fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row - 1
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @col <> @oldcol or @ticktype <> @oldticktype or @row <> @oldrow + 1 or @oldrowname <> @rowname)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close cv
deallocate cv

--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec



update @out set list = left(list,len(list)-1) where len(list) > 1
update @out set list = right(list,len(list)-1) where list like ',%'
select o.*, clrdescr, srdescr, arname
from @out as o inner join
moa_pe..colors c on clrstatus = o.ticktype inner join
moa_pe..serieses on srcode = o.event inner join
moa_pe..areas on arcode = o.area
order by srdescr, arname, sec, row, list


GO

Yet More useful lock-reporting.

Ah yes. Now what if you have a lock on one series, that shares events with another series, and there is a lock on the other series? How are you to be master of your locks, let alone the universe, if you have a messy lock situation?
Not sure how that happened, but it's as easy as pi to find them:



create procedure moa_series_locks_overlapping
as
SELECT Serieses.srDescr AS series1, Colors.clrDescr AS lock1, Serieses_1.srDescr AS series2, Colors_1.clrDescr AS lock2, AreaChair.ahShortArName AS sec,
AreaChair.ahLine AS row, AreaChair.ahChair AS seat
FROM
moa_pe..LinkSeriesEvents LinkSeriesEvents with(nolock) INNER JOIN
moa_pe..LinkSeriesEvents LinkSeriesEvents_1 with(nolock) INNER JOIN
moa_pe..SeriesLockedSeats SeriesLockedSeats_1 with(nolock) INNER JOIN
moa_pe..SeriesLockedSeats SeriesLockedSeats with(nolock) ON SeriesLockedSeats_1.slsSeries > SeriesLockedSeats.slsSeries AND
SeriesLockedSeats_1.slsChair = SeriesLockedSeats.slsChair ON LinkSeriesEvents_1.lseSeriesCode = SeriesLockedSeats_1.slsSeries ON
LinkSeriesEvents.lseSeriesCode = SeriesLockedSeats.slsSeries AND LinkSeriesEvents.lseEventCode = LinkSeriesEvents_1.lseEventCode INNER JOIN
moa_pe..Serieses Serieses with(nolock) ON SeriesLockedSeats.slsSeries = Serieses.srCode INNER JOIN
moa_pe..Serieses Serieses_1 with(nolock) ON SeriesLockedSeats_1.slsSeries = Serieses_1.srCode INNER JOIN
moa_pe..Colors Colors with(nolock) ON SeriesLockedSeats.slsTickType = Colors.clrStatus INNER JOIN
moa_pe..Colors Colors_1 with(nolock) ON SeriesLockedSeats_1.slsTickType = Colors_1.clrStatus INNER JOIN
moa_pe..AreaChair AreaChair with(nolock) ON SeriesLockedSeats_1.slsChair = AreaChair.ahChairCode
WHERE serieses_1.srdescr not like '%qrt' and serieses.srdescr not like '%qrt'
group by Serieses.srDescr , Colors.clrDescr , Serieses_1.srDescr , Colors_1.clrDescr , AreaChair.ahShortArName ,
AreaChair.ahLine , AreaChair.ahChair

GO

More lock-reporting tidbits

Now its time for some real fun. a report that does something useful for somone i'm sure. This will allow you to find groups of 2 adjacent locks, singles, n adjacent locks, or just list them all.
It takes parameters for event date range and lock type, and lists out the seat locations of these groups of locks. If you are like us, you have some areas in which the rows run vertical. the section of code 'update ... set vertical = 1 where ...' accounts for these.
In our business, single locks are useless for certain lock types, like whell-chair locks, which need a pair.
There is a 65.36 percent chance that you will find this code useful.



CREATE procedure moa_event_lock_count_list (@event int, @evdatefrom datetime, @evdateto datetime, @showtype int, @locktype int, @cntfilter int)
as
declare @l table (event int, chair int, area int, row int, col int, vertical bit, rowname varchar(5), seatname varchar(5), sec varchar(5), ticktype int )

insert into @l (event, chair, area, row, col, vertical, rowname, seatname, sec, ticktype)
select elsevent, elschair, elsarea , ahrow, ahcol, 0, ahline, ahchair, left(ahshortarname,5), elsticktype
from
moa_pe..eventlockedseats eventlockedseats with(nolock)
inner join moa_pe..areachair areachair with(nolock) on elschair = ahchaircode
where
elsticktype between 2000 and 3000
and (elsticktype = @locktype or @locktype = 0)
and elsevent in (select evcode
from moa_pe..events with(nolock) inner join
moa_pe..shows with(nolock) on shcode = evshow
where eveventdate between @evdatefrom and @evdateto
and (shshowtype = @showtype or @showtype = 0) )

order by elsevent, elschair, elsarea, ahrow, ahcol

update @l set vertical = 1 where area in (select arcode from moa_pe..areas with(nolock) where arname like '%boxes%' )

declare @out table (event int, list varchar(255), row varchar(5), area int, cnt int, ticktype int, sec varchar(10) )

declare @ev int, @oldev int, @ar int, @oldar int, @row int, @oldrow int, @col int, @oldcol int
declare @sec varchar(5), @oldsec varchar(5) , @rowname varchar(5), @oldrowname varchar(5), @seat varchar(5), @oldseat varchar(5)
declare @ticktype int, @oldticktype int
declare @count int, @seatstr varchar(5000)



declare ch cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 0 order by event, area, row, col

open ch
fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col - 1
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @row <> @oldrow or @ticktype <> @oldticktype or @col <> @oldcol + 1)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close ch
deallocate ch


--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec

declare cv cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 1 order by event, area, col, row

open cv
fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row - 1
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @col <> @oldcol or @ticktype <> @oldticktype or @row <> @oldrow + 1 or @oldrowname <> @rowname)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close cv
deallocate cv

--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec



update @out set list = left(list,len(list)-1) where len(list) > 1
update @out set list = right(list,len(list)-1) where list like ',%'
select o.*, clrdescr, eveventdate, left(shdescr,15) as showname, arname
from @out as o inner join
moa_pe..colors c with(nolock) on clrstatus = o.ticktype inner join
moa_pe..events with(nolock) on evcode = o.event inner join
moa_pe..areas with(nolock) on arcode = o.area inner join
moa_pe..shows with(nolock) on shcode = evshow
where (cnt = @cntfilter or @cntfilter = 0)
order by eveventdate, arname, sec, row, list


GO



How did k-9 come up with so many significant digits for a probability of random occurences? It might help that he was in a time machine...

Get a handle on your series Locks!

You have serieses to sell, and many of them. you put locks on the series chart to prevent the single events from selling on those seats, so you can sell the subscriptions with all the same seat. You have a bad rep that took the lock on just one of the events in the series, (or you were the victim of the bug (now fixed) in which the series locks did not propogate if seat-info was on the event seat) and now that seat can't be sold for the subscription without allocating one of the seats to a different seat. annoying, right? With a bit of sql you can generate a list of seats that have been 'pirated' in this way:
The following gets a list of locks that should be there, and checks to see if the locks are there, and if not, the seat status. Arr, matey.


--replace moa_pe.. w/ yourdbname..
--the @inc_stage_ext is an option to include or exclude event-lock type pirates

CREATE PROCEDURE moa_series_lock_pirated_seats
(@season int, @inc_stage_ext bit = 0)

AS
/*
--drop table #sl
declare @season int
set @season = 17
declare @inc_stage_ext bit
set @inc_stage_ext = 0
*/
--get all eventt locks that should be there:
create table #sl (s int, e int, c int, ticktype varchar(40), ticktypecode int, lockuser varchar(50), lockdate datetime, evc int)
create index slec on #sl (e,c)

--declare #sl table (s int, e int, c int, ticktype varchar(40), ticktypecode int, lockuser varchar(50), lockdate datetime)
declare @out table (s int, e int, c int, srname varchar(20), evdate datetime, shname varchar(50), lockuser varchar(50),
ticktype varchar(50), pirate varchar(50), acct int, transact int, piratedate datetime, sec varchar(10), row varchar(5), seat varchar(5), reason varchar(50), lockdate datetime )

insert into #sl (s, e, c, ticktype, ticktypecode , evc)
SELECT
SeriesLockedSeats.slsSeries as s,
LinkSeriesEvents.lseEventCode as e,
SeriesLockedSeats.slsChair as c ,
clrdescr,
slsticktype,
ah2.ahchaircode
FROM
moa_pe.dbo.Serieses Serieses with(nolock) INNER JOIN
moa_pe.dbo.SeriesLockedSeats SeriesLockedSeats with(nolock) ON Serieses.srCode = SeriesLockedSeats.slsSeries INNER JOIN
moa_pe.dbo.LinkSeriesEvents LinkSeriesEvents with(nolock) ON SeriesLockedSeats.slsSeries = LinkSeriesEvents.lseSeriesCode inner join
moa_pe..eventsareas with(nolock) on evaevent = lseeventcode inner join
moa_pe..areachair ah1 with(nolock) on ah1.ahchaircode = slschair inner join
moa_pe..areachair ah2 with(nolock) on ah2.ahparentseatid = ah1.ahparentseatid and ah2.ahareacode = evaarea
inner join moa_pe..colors colors with(nolock) on clrstatus = slsticktype inner join
moa_pe..events with(nolock) on evcode = lseeventcode and evhall = srhall
WHERE (Serieses.srSeasons = @season)
--AND (LinkSeriesEvents.lseIsRequiered = 1)
and slsticktype between 2000 and 3000
order by LinkSeriesEvents.lseEventCode , SeriesLockedSeats.slsChair

delete from #sl
from #sl inner join moa_pe..subsprogram with(nolock) on proseatnum = c and proseriescode = s and procurrentstatus = 10 and proseasoncode = @season

update #sl set lockuser = uscode ,lockdate = slhdatelocked
from #sl as sl
inner JOIN
moa_pe..SeatLockHistory SeatLockHistory with(nolock) ON sl.s = SeatLockHistory.slhEventCode AND
sl.c = SeatLockHistory.slhSeatCode AND sl.ticktypecode = SeatLockHistory.slhTickType
and slhlocktype = 3
left outer join
moa_pe..users users with(nolock) on usrecid = slhuserlocked

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tiactualsaleddate,
uscode,
timailinglist,
titransactnum,
'Sold as single ticket',
lockdate
from #sl as sl inner join
moa_pe..tickets t with(nolock) on tievent = sl.e and tichair = sl.evc and tistatus = 1 inner join
moa_pe..shifts with(nolock) on sfcode = tishift and sfactiontype = 0 left outer join
moa_pe..users with(nolock) on usrecid = sfuser

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tisactualsaledate,
uscode,
prosubsnum,
tistransactnum,
'Sold as subscription ticket',
lockdate
from #sl as sl inner join
moa_pe..ticketssubscription t with(nolock) on tisevent = sl.e and tischair = sl.evc and tisstatus = 1 inner join
moa_pe..shifts with(nolock) on sfcode = tisshift and sfactiontype = 0 inner join
moa_pe..subsprogram with(nolock) on case when prolasttransact > 0 then prolasttransact else prorecnum end = tissubsprogram and procurrentstatus = 10 left outer join
moa_pe..users with(nolock) on usrecid = sfuser


insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tibtimestamp,
uscode,
0,
0,
'Event Lock-stage ext',
lockdate
from #sl as sl inner join
moa_pe..ticketbase t with(nolock) on tibevent = sl.e and tibchair = sl.evc and tibticktype = 2 left outer join
moa_pe..users with(nolock) on usrecid = tibuser

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tbxactualactiondate,
uscode,
tbxmailcustomer,
tbxtransactnum,
'Sold as Reservation',
lockdate
from #sl as sl inner join
moa_pe..ticketbaseextra t with(nolock) on tbxevent = sl.e and tbxchair = sl.evc and tbxstatus = 1 left outer join
moa_pe..users with(nolock) on usrecid = tbxuser

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select sl.s,sl.e,sl.c, sl.ticktype, sl.lockuser,
'1/1/1900',
isnull(srdescr,''),
0,
0,
'Locked as: ' + clrdescr,
sl.lockdate
from #sl as sl inner join
moa_pe..eventlockedseats els with(nolock) on elsevent = sl.e and elschair = sl.evc and elsticktype <> sl.ticktypecode and elsticktype between 2000 and 3000 left outer join
@out o on sl.c = o.c and sl.e = o.e and sl.s = o.s inner join
moa_pe..colors with(nolock) on clrstatus = elsticktype left outer join
moa_pe..serieses sr with(nolock) on srcode = elsseries
where o.e is null

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s.s,s.e,s.c, s.ticktype, s.lockuser, '1/1/1900', '', 0, 0, 'Not locked on event', s.lockdate
from #sl s left outer join
@out o on s.c = o.c and s.e = o.e left outer join
moa_pe..eventlockedseats on elsevent = s.e and elschair = s.evc and elsticktype = s.ticktypecode
where elsevent is null and o.e is null

update @out
set
srname = left(srdescr,20),
evdate = eveventdate,
shname = left(shdescr,20),
sec = ahshortarname,
row = ahline,
seat = ahchair
from @out inner join
moa_pe..serieses on srcode = s inner join
moa_pe..areachair on ahchaircode = c inner join
moa_pe..events on evcode = e inner join
moa_pe..shows on shcode = evshow

select * from @out
where (@inc_stage_ext = 1 or reason not like '%stage ext%')
order by srname, sec, row, seat

drop table #sl

GO

Move audience and Access control

So, this will be the last post on move-audience. I am obsessed.
We used move-audience to move tickets between events, and found that the barcodes table is not updated in the move. The barcodes table stores the event code in the brtentity field, which the scanning sp's use to look up the event.
An alternative to this query is to use the company table setting 'Reset print status On Move Audience' which will unprint the moved tickets, so you can re-issue them with valid barcodes.
So, if you use move-audience between events, and also use scanners, use this sql to update the barcodes table :

update barcodes
set brtentitycode = tievent
from
Tickets with(nolock) INNER JOIN
Barcodes ON Tickets.tiCode = Barcodes.brtSourceCode
AND Tickets.tiEvent <> Barcodes.brtEntityCode inner join
events with(nolock) on tievent = evcode
WHERE
(Barcodes.brtSourceTable = 1)
AND (Tickets.tiSaleDate > '1/1/08')
AND (Tickets.tiStatus = 1)
and brtEntityType = 1
and eveventdate > getdate()

Move Audience Audit script

I came across the instance where ticket sales ws confused after a customer called in claiming they had ordered different seats than what was mailed to them. the reason their seats had changed was due to a stage extension on the event, where the audience was moved using the move-audience feature in PE administration. I figured that a good solution to this problem would be to insert a remark for each seat affected on the patron's account, since there is no other way to see move-audience history with PE . The following stored procedure is what I use to do this. I have it set up to stuff remarks every night for seat moves that occured in the past 24 hours:


create procedure moa_create_moveaudience_remarks as

declare @fromdate datetime
set @fromdate = getdate() - 1

select
identity(int) as id,
masl.maslrecordid,
maslupdatedate,
uscode,
slpmachinename,
tix.cltcode,
tix.cltgroupcode,
tix.type,
evfrom.eveventdate as fromevdate,
shfrom.shdescr as fromshow,
arfrom.arname as fromar,
ahfrom.ahshortarname as fromsec,
ahfrom.ahline as fromrow,
ahfrom.ahchair as fromseat,
evto.eveventdate as toevdate,
shto.shdescr as toshow,
arto.arname as toar,
ahto.ahshortarname as tosec,
ahto.ahline as torow,
ahto.ahchair as toseat
into #m
from
MoveAudienceSeatsLog masl with(nolock)
inner join
(select ticode as tcode, timailinglist as cltcode, ticlientgroupcode as cltgroupcode, 'Single' as type from tickets with(nolock)
union
select tiscode, prosubsnum, procustnum, 'Subs' from ticketssubscription with(nolock)
inner join subsprogram with(nolock) on prorecnum = tissubsprogram
union
select tbxcode, tbxmailcustomer, tbxclientgroupcode, 'Reservation' from ticketbaseextra with(nolock) )
as tix on masl.maslticketcode = tix.tcode inner join
areachair ahfrom with(nolock) on masl.masloldseat = ahfrom.ahchaircode inner join
areachair ahto with(nolock) on masl.maslnewseat = ahto.ahchaircode inner join
areas arfrom with(nolock) on arfrom.arcode = masl.masloldareacode inner join
areas arto with(nolock) on arto.arcode = masl.maslnewareacode inner join
events evfrom with(nolock) on masl.masloldeventcode = evfrom.evcode inner join
shows shfrom with(nolock) on shfrom.shcode = evfrom.evshow inner join
events evto with(nolock) on evto.evcode = masl.maslneweventcode inner join
shows shto with(nolock) on shto.shcode = evto.evshow inner join
users with(nolock) on masluser = usrecid inner join
salespoints with(nolock) on salespoints.slpnumber = masl.maslsalepoint
where maslupdatedate > @fromdate
order by maslrecordid

declare @rmkstartnum int, @rmknewnum int
set @rmkstartnum = (SELECT coLastNumber + 1 FROM Counters WHERE (coName = 'TableCustomerRemarks'))
set @rmknewnum = (select count(*) from #m) + @rmkstartnum
update Counters set coLastNumber = @rmknewnum where (coName = 'TableCustomerRemarks')


insert into moa_pe.dbo.customerremarks
select id + @rmkstartnum , cltgroupcode, cltcode, 0, 53, maslupdatedate,
'From ' + convert(varchar(20),fromevdate,101) + ' ' + left(fromshow,10) + ' To: ' + convert(varchar(20),toevdate,101) + ' - ' + left(toshow,10)
+ char(13) + char(10)
+ 'From: ' + ' ' + fromsec + ' ' + fromrow + ' ' + fromseat + ' to' + char(13) + char(10)
+ 'To: ' + ' ' + tosec + ' ' + torow + ' ' + toseat + ' ' + char(13) + char(10)
+ 'By: ' + Uscode + ' ' + slpmachinename
,
1, 0, 0, '1/1/1900', 1
from #m

--select * from #m

drop table #m


GO



Print at Home

I recently had to design our print at home ticket documents. Read this and save time. You're welcome in advance.
Print at home ticket design Basics:

• Use ctrl+x to remove a field
• If you put html in a free-text field, be sure to include the / at the end of image, br, or other tags e.g. <br /> instead of <br >.
• Always use PDF format! When html is emailed to gmail, alo, etc, the ticket gets completely destroyed! Try it, its kind of funny to see.
• always test whenever you make a change, to make sure you don't get the 'print at home error', which really could mean anything, so keep track of your changes too.
• use the export utility to backup functional ticket designs (or use the document copy feature)

Date format:
To format dates in the doc designer:

• Day name: ddd
• day number: dd or d for 03 or 3
• Month name: mmm (like Aug , Sep, Nov)
• Month Number: mm
• Year: yy or yyyy for 09 or 2009
• Hour: h or hh for 7 or 07
• minute: mm
• AM / PM: ampm

So for example you want the format
Sun Aug 3 2008 7:00 PM
use
ddd mmm d yyyy h:mm ampm
and enter this in the 'format' field when you have the event date highlighted.

Layering Images and Text:

I could not get text to appear on top of an image. the designer makes it look like you can control what appears on top of what if you have 2 fields overlapping. It appears to save the info, but when the designer is closed and re-opened, it does not save the change. In addition, i did not see a place in the database to store any ordinal information. I tried to get around this using transparent images - but - when printed from firefox, text underneath a transparent image does not show on the printed page.

Concatenating Fields:

You can concatenate fields by using
concat(@FirstName,' ',@LastName)
or concat(@fieldname1,'anytext',@fieldname2)

Barcodes:

I had the barcode width set to 300, the pah barcode width set to 300 as well, but the image still measured in at 200 pixels. If anyone figures out how to get a bigger barcode, kudos and let me know. for now, 200 pixels seems to work fine and scans fine when printed.

PEO: Recommended Events

This feature can be cumbersome, but here are some ideas to make it easier to read:
1:
When adding anything to this list, ALL shows display in alphabetical order. this is dumb.
to fix:
go to System Setup / Column Rules >> find the entry where name = rcshowcode and table = Recommended
Open the record, and change the "Source" to:

SELECT Shows.shCode, CONVERT(varchar(20), MIN(Events.evDateTime), 101) + ' ' + Shows.shName AS shname
FROM Shows INNER JOIN Events ON Shows.shCode = Events.evShowCode
WHERE Events.evDateTime > GETDATE()
GROUP BY Shows.shCode, Shows.shName
ORDER BY min(evdatetime)


Change the SQL SELECT to:

rcShowCode = ISNULL(( SELECT TOP 1 CONVERT(varchar(20), MIN(Events.evDateTime), 101) + ' ' + Shows.shName
FROM Shows INNER JOIN Events ON Shows.shCode = Events.evShowCode
WHERE shCode = rcShowCode GROUP BY Shows.shCode, Shows.shName), 0)

Ok. Now you can use the feature.
Go back to recommended events, and add a new show to a category. it should work. bada bing, easy to use, its the way the world should be.
There is also a 'Site level' type of entry, which governs which events show up on the default 'all events' page, the page the users see when they first get to the site. We like to have the next 7 non-rental events on this page at all times. To manage this, I use a nightly sql job to populate the Site Level events.
Here it is:


CREATE PROCEDURE Kustom_recommended AS
--Thats Kustom with a kapital K

--for testing...
--delete from recommended where rctype = 1
--get show list
SELECT
TOP 7
min(Recommended.rcCategoryCode) as cat,
Recommended.rcShowCode as show
into #ev
FROM Recommended INNER JOIN
Shows ON Recommended.rcShowCode = Shows.shCode INNER JOIN
Category ON Recommended.rcCategoryCode = Category.cgCode inner join
events on events.evshowcode = shows.shcode
WHERE
events.evendsaledate > GETDATE()
and Events.evDateTime > GETDATE()
AND Recommended.rcType = 2 --category level entries
AND Category.cgName NOT LIKE '%rental%'
group by Recommended.rcShowCode
ORDER BY min(Events.evDateTime)

--insert into recommended
declare @next int, @show int, @cat int

declare r cursor for select cat, show from #ev
open r
fetch next from r into @cat, @show
while @@fetch_status = 0
begin
if not exists (select * from recommended where rctype = 1 and rcshowcode = @show )
begin
set @next = (SELECT cnValue + 1 FROM Counters WHERE (cnName = 'NewRecommend'))
update counters set cnvalue = @next, cnlastupdate = getdate() where cnname = 'NewRecommend'
insert into recommended select @next, 1, @cat, @show, getdate()
end

fetch next from r into @cat, @show
end
close r
deallocate r
drop table #ev

--clean up recommended table:
delete from --select * from
recommended
where rcshowcode in (SELECT shCode FROM Shows WHERE shMaxDate < GETDATE())
GO


This also will delete old entries that are no longer needed.
Disclaimer: use at your own risk. Always test before using. i am not responsible for anything that may happen to you. Don't run with scissors.