Printing Setup

I'm bored. Lets talk about printing.

I will explain my printing setup, and maybe it will help yours.

When PE prints a ticket, here is what is going on:

PE locks the ticket, so that no-one else can print during the process
A record is prepared and inserted into a print-spool table in the database.
The tix_printserver.exe application queries this table every x seconds to look for pending tickets.
When it gets a ticket, the tix_printserver.exe sends the ticket to the printer.
If successful, the tix server will mark the tik as printed, and unlock the ticket for printing by others.
The computer that runs the boca printer driver spools the ticket for printing, and then it prints.

So, I have found that it is best to have the tix_printserver running on the database-server for a couple of reasons: 1; Someone has to be logged in for tix-print to run (it can't run as a service), so leave a login open on the db-server for this kind of process (the pc-charge payment server has the same circumstance. I use the same open-login for print servers andpccharge). 2: since the app is querying the db every 5 seconds, it will ease network traffic to have the app running on the db server.

The Boca printers themselves I have hooked up to HP printer hubs. Network connection on one end, paralell on the other. Use the HP jet-admin utility to configure. You assign a tcp/port number to "create" a tcp printing port on the server.

The PE application assigns printers according to sales point. every sp gets a main and alternate printer. There is a user-option to "print to alternate", which will print to the alternate for that salespoint.

If you run into the problem of a botched batch of tickets, you must unlock the printing transaction so someone else can print the tickets. there is a utility in PE admin for this.

Thats all I have for now.

Don't ask me how to print reciepts that you can't print otherwise, cause I won't tell you till next week.

Till next time, happy PEing!

PEO: Print At Home

Anybody out there using the print-at-home feature with PEO?
Do you have any advice for those (like myself) who are about to start using this feature?

PEO Module List

Don't ask how I got this. I will only say that teeth pulling was involved. This is a list of PEO Modules, along with prices. The document had a datestamp of DEC 07. Enjoy ;-)

Recommended Shows
Recommend shows to online patrons based on shows they are browsing or are in their online basket.
$3000


Ticket Discounts


Promotion Codes
For single OR series, not both
Define promotional codes that will discount and event or group of events
$1800

Quantity Discounts

# of Tickets
Provide a discount on tickets when a minimum number of tickets is reached in the online transaction
$2000

# of Events
Provide a discount on tickets when a minimum number of events is reached in the online transaction
$2000

# of Shows
Provide a discount on tickets when a minimum number of shows is reached in the online transaction
$2000



Combination Discounts

Show combo
Provide a discount when a specific number of tickets are purchased to two shows in combination (i.e. 2 tickets for show 1 and 1 ticket for show two results in a discount on tickets for show 2)
$2000

Event Combo
Provide a discount when a specific number of tickets are purchased to two events in combination (i.e. 2 tickets for event 1 and 1 ticket for event 1 results in a discount on show 2)
$2000

Ticket Restrictions

Event based restrictions
Restrict a designated event or events from purchase unless purchased in combination with another event or events
$2000

Show based restrictions
Restrict a designated show or shows from purchase unless purchased in combination with another event or shows
$2000



Member or Subscriber Benefits

$3000
Member/Subscriber pricing (discounts)
Allow access to special pricing to current members or subscribers

Member/Subscriber restrictions
Allows for special presales or “members only” events




Promotional tools for Memberships and Subscriptions


Promotion Codes
Define promotional codes that will discount specific memberships or series
$1800

Restricted memberships or subscriptions
Restrict a designated from purchase unless purchased in combination with another membership/series
$1800

PE: Move Audience

The Move Audience feature is pretty cool. Butt. It has one potentially dangerouse flaw: it keeps NO record of which seats you moved. It does keep an event-level log, listing how many seats were moved per event.

Another draw-back - if you do accounting on events using transaction-date-range as a criteria, move audience will move money from one event to another, effecting the report for past transaction date ranges. This goes against the principles of any good accounting system. Just do not use this for moving tickets between events and you'll be fine.

Ideally, the ticket records would reflect the movement. (insert a status 9, change the old to status 8, and pull the new status 1 ticket)

PEO BUG: User Can't order tickets in more than 1 area for the same show

Lets say that your venue has 2 "areas" : "Main Floor" and "Balcony"

A user gets tickets to an event on the main floor, and presses add-to-cart. He remembers that he has to get an extra ticket for his mother in law, so he looks in the Balcony. He clicks on "continue shopping" and goes back to the seating chart, selects the balcony area, and selects the cheapest seat he could find. He presses add-to-cart, but in the cart-display section, he only sees the 1 balcony seat, and not the premium Main Floor seats he really wanted.
He goes back to the main floor to get the primo seats, and now the cart only displays the Main Floor seats. He takes this as a sign that he should not go to a show with his mother in law.

Blackbaud has acknowledged this bug ... no news yet on which version it will be fixed in.

PE: Is For Mailing check box on CRM Client editing window checked for more than one address.

This problem could effect your reports, if you are depending on the assumption that only one address on an account will have claisformailing = 1. (many of mine do...)
The causes of this include:

PEO Delivery address (see prev. post)
and
1st address had a type that is restricted to the user group that checked the box on another address. - (there might be more... but thats all I got)

The fix I use to take care of these bad bad bits :


--duplicate claisformailing = 1 on same account. nono. peo bad.
update clientaddresses set claisformailing = 0
where claaddresscode in
(
SELECT MAX(claAddressCode)
FROM ClientAddresses
WHERE (claIsForMailing = 1)
GROUP BY claClientCode
HAVING (COUNT(*) > 1)
)


You will need something more involved if you experienced the bug as a result of reason 2.

PEO BUG: Merchandise with different delivery address - address is not visible through PE

When you sell a merchandise item in PEO, and you have the delivery address enabled in the PEO checkout, PEO will record the delivery address in the orders table. The problem: It is not visible when you are in the PE CRM merchandise tab. Ideally, it would behave like single tickets, where you right-click on the item and boom, show me the address. but no.

The address does get recorded in the client record under the address type that you specify, but if they order yet another item that will get overwritten. There is even a bug associated with this - the is for mailing check-box ends up checked for both the billing and delivery addresses. (see next post)

A workaround I have in place is to run a nightly process to extract these addresses and stuff them into remarks. The following code is what I use as a workaround. Note: this writes data to the pe database. I am not responsible for your eagerness to press the green play button.


--insert remarks for online merchandise sales w/ different delivery addr...
--when merch sold on peo, adds order w/ addr, which can't be seen in pe. doh.
--jrh 12/1/07
SELECT
identity(int) as id,
convert(varchar(20),mcdtransactnum) +' - Internet Merch Order. Shipping Addr:' + char(13) + char(10)
+ ordclientname + char(13) + char(10)
+ ordfield1 + + char(13) + char(10)
+ ordfield2 + ', ' + ordfield4 + ' ' + ordfield3 as rmk,
min(ordopendate) as ordopendate,
mcdmailinglist,
mcdclientgroupcode
into #om
FROM
Orders with(nolock) INNER JOIN
Merchandise with(nolock) ON Orders.ordCode = Merchandise.mcdOrder
WHERE
(Orders.ordOrderTypeTitle = 5)
and mcdstatus = 0
and ordopendate > getdate() - 1
group by mcdmailinglist, mcdtransactnum, ordclientname , ordfield1, ordfield2, ordfield3, ordfield4, mcdclientgroupcode

declare @cn int set @cn = (select colastnumber from counters where coname = 'tablecustomerremarks')
update counters set colastnumber = @cn + (select count(*) from #om) where coname = 'tablecustomerremarks'

insert into customerremarks
select id + @cn, mcdclientgroupcode, mcdmailinglist, 0, 42, ordopendate, rmk, 1, 0, 0, '1/1/1900', 1
from #om
drop table #om


PEO: Live Subscription Seating Online

PEO does not currently have this feature.
Let me describe my dream:
A user goes to your web-site, and is able to buy a subscription ticket package online. The customer selects the seat just like they would a single ticket (with all the fancy peo stuff - area maps, view from seat... Thou shalt not leave isolated seats pop-up window). The user adds a couple single tickets to the basket along with a merchandise item and a donation. The user gives you lots of money without you having to talk to them. Brilliant.

If you like the idea, be sure to let blackbaud know. In fact, I am begging you to let them know. This will increase the chances that it will happen soon.
Thanks you for your support.

PE: Back Button in Series Payments Screen does not work

When purchasing a subscription in PE, and you get to the payments screen, you can't go back. If the amount came out to an amount that you did not expect, you have to start all over. This in combination with the commissions resetting during the subscription-selling process makes this bug especially frustrating. (you know, when you add a commission or discount, and then click the events button, the changes dissappear!)
I have been told that this might be fixed in the next version, but it is not out yet. It may come out soon. It may come out later. Woe. Woe is me. I'll be in the server room crying on a pile of cat 5 network cable. You can cry on my blog if you like. There there. Does that feel better?

Bug PEO Subscription Renewals do not work if new series has a GA event.

It turns out that PEO will not renew a series if the renewing-into series has a GA event, and the series is seated. In addition to all the other criteria that the peo renewing function must meet, it has made it unuseable by us...
"The Other Criteria" being: The old and new series must have the same number of events. The new series must have that number of events as it's "maximum num of events". The renewal must have the same price type from season to season. and Discounts are not supported.

Anyone find a fix or workaround for this one?

Deposit Balance Details View

In PE, when you make a payment to a client account, and just post it to that clients account, it makes a deposit. Here is the data structure for deposits:


CREATE VIEW dbo.moa_deposit_balance
AS
SELECT ReceiptDeposit.rcdActionType AS Type, BalanceCategories.bcCategoryName AS CategoryName, ReceiptDeposit.rcdCategory AS Category,
ReceiptDeposit.rcdCode, Clients.cltClientName AS ClientName, ReceiptDeposit.rcdClientCode, ReceiptDeposit.rcdPayAmount AS [Original Amount],
ISNULL(bdDebit.Debits, 0) AS Movement, ReceiptDeposit.rcdPayAmount + ISNULL(bdDebit.Debits, 0) AS Balance,
ReceiptDeposit.rcdTransactNum AS Transact, ReceiptDeposit.rcdReceiptCode AS Receipt, ReceiptDeposit.rcdTransactType,
ReceiptDeposit.rcdTransactSource, ReceiptDeposit.rcdDueDate AS Due
FROM dbo.ReceiptDeposit ReceiptDeposit LEFT OUTER JOIN
dbo.BalanceCategories BalanceCategories ON ReceiptDeposit.rcdCategory = BalanceCategories.bcCode LEFT OUTER JOIN
dbo.Clients Clients ON ReceiptDeposit.rcdClientCode = Clients.cltCode LEFT OUTER JOIN
(SELECT bdReceiptCreditNum, SUM(bdAmount * - 1) AS Debits
FROM MOA_PE.dbo.BalanceDetails BalanceDetails
GROUP BY bdReceiptCreditNum) bdDebit ON ReceiptDeposit.rcdCode = bdDebit.bdReceiptCreditNum
WHERE (ReceiptDeposit.rcdActionType = 0) AND (ReceiptDeposit.rcdStatus = 1) AND (ReceiptDeposit.rcdPayAmount + ISNULL(bdDebit.Debits, 0) <> 0)




Invoice Balance Details View

This view will display Invoices that have a balance due, along with some other info. Merry Xmas PEons.


CREATE VIEW dbo.moa_invoice_balance_details
AS
SELECT ReceiptDeposit.rcdActionType AS Type, ReceiptDeposit.rcdClientCode, ReceiptDeposit.rcdPayAmount * - 1 + ISNULL(bdCredit.Credits, 0) AS Balance,
ReceiptDeposit.rcdTransactNum AS Transact, MAX(ISNULL(BalanceCategories.bcCategoryName, 'Undefined .')) AS Expr1,
ReceiptDeposit.rcdGroupCode, ReceiptDeposit.rcdCategory, users.usCode, ReceiptDeposit.rcdDueDate,
ReceiptDeposit.rcdTransactDate AS entry_date, ReceiptDeposit.rcdCode, ReceiptDeposit.rcdReceiptCode, ReceiptDeposit.rcdShiftNum AS shift,
ISNULL(BalanceCategories.bcCategoryName, 'Undefined .') AS balance_category
FROM MOA_PE.dbo.ReceiptDeposit AS ReceiptDeposit WITH (nolock) LEFT OUTER JOIN
MOA_PE.dbo.BalanceCategories AS BalanceCategories WITH (nolock) ON ReceiptDeposit.rcdCategory = BalanceCategories.bcCode LEFT OUTER JOIN
(SELECT bdReceiptDebitNum, SUM(bdAmount) AS Credits
FROM MOA_PE.dbo.BalanceDetails AS BalanceDetails WITH (nolock)
GROUP BY bdReceiptDebitNum) AS bdCredit ON ReceiptDeposit.rcdCode = bdCredit.bdReceiptDebitNum INNER JOIN
MOA_PE.dbo.Users AS users WITH (nolock) ON users.usRecId = ReceiptDeposit.rcdUser
WHERE (ReceiptDeposit.rcdStatus = 1) AND (ReceiptDeposit.rcdActionType = 1) AND (ReceiptDeposit.rcdPayAmount * - 1 + ISNULL(bdCredit.Credits, 0) <> 0)
GROUP BY ReceiptDeposit.rcdClientCode, ReceiptDeposit.rcdTransactNum, ReceiptDeposit.rcdActionType, ReceiptDeposit.rcdPayAmount, bdCredit.Credits,
ReceiptDeposit.rcdGroupCode, ReceiptDeposit.rcdCategory, users.usCode, ReceiptDeposit.rcdDueDate, ReceiptDeposit.rcdTransactDate,
ReceiptDeposit.rcdCode, ReceiptDeposit.rcdReceiptCode, ReceiptDeposit.rcdShiftNum, ISNULL(BalanceCategories.bcCategoryName, 'Undefined .')


Calculate the actual price of a subscription ticket

PE does not do a good job at telling you the actual ticket value for subscription tickets... The subscription may have a 20% discount, yet the reports would report full value. Use this function as such: select moa_subtik_actual_price(tiscode) from ticketssubscription .... to get the actual value. Enjoy ;-)


CREATE FUNCTION dbo.moa_seriestik_actual_price (@prorecnum int)
RETURNS money AS
--added 11/15/06 jrh
-----------------------------------------------WILL RETURN NEGATIVE VALUE FOR RETURNS-------
--for reports that need live data, and only process a handful of tix

BEGIN

--declare @prorecnum int
--set @prorecnum = 224458
declare @actualprice money
set @actualprice = (
SELECT top 1 (subsprogram.proprice * procach.m) + isnull(discounts.clcsum,0) AS actualprice

FROM (SELECT cc.clcTicketNum, SUM(cc.clcSum * cach.m) AS clcsum
FROM CalculatedCommission cc WITH (NOLOCK) INNER JOIN
Commission WITH (NOLOCK) ON cc.clcCommission = Commission.cmsCode
inner join (select 1 as c, -1 as m union select 0 , 1) as cach on cc.clccachinout = cach.c
WHERE (Commission.cmsCommissionType = 4)
GROUP BY clcticketnum)
discounts right outer JOIN
subsprogram WITH (NOLOCK) ON discounts.clcTicketNum = subsprogram.prorecnum
inner join
(select 10 as s, 1 as m union
select 17, 1 union
select 28, 1 union
select 18, 1 union
select 29, -1 union
select 27, -1 union
select 21, - 1
) as procach on subsprogram.procurrentstatus = procach.s
WHERE (subsprogram.prorecnum = @prorecnum)
)

return isnull(@actualprice,0)

END


Tracing Merged Records in PE from old (merged) account to new account

--this function takes an int input of the possibly no-longer-existing account, and returns the account number (cltcode) of the current account



CREATE FUNCTION dbo.moa_merge_trace (
@old int
) RETURNS int
as begin
declare @new int, @count int
set @count = 0 -- to prevent infinite loops
--begin recursive trace
if exists (select cltcode from clients with(nolock) where cltcode = @old) return @old

while exists (SELECT FromID FROM PATRONEDGE_MERGELOG with(nolock) where FromID = @old and metaobject_id = 713) and @count < 100
begin
set @new = (SELECT ToID FROM PATRONEDGE_MERGELOG with(nolock) where FromID = @old and metaobject_id = 713)
set @old = @new
set @count = @count + 1
end

return @new
end


Welcome to the Patron's Edge Blog.

I work for an organization that uses Patron's Edge. In an effort to connect with other PE users, and to expand the public knowledge base on this product, I created this blog. Have you discovered a bug? Found a fix or workaround to that bug? Have learned that a bug has been fixed in the latest release? Let us know! Enjoy !