Select
un_pay_status as Status
, concat(concat(pa_lname, ', '), pa_fname) as Name
, en_ttl_fees - en_ttl_paid as Owed
/* , insert(pa_email, (instr(pa_email,'@')), 1, ' @') as Email */
, concat(concat(concat(substr(pa_email,1,3)) , '*-*-*-*') , substr(pa_email,7)) as Email
, en_ttl_fees as Fees
, en_ttl_paid as Paid
,
substring(
date_add(
concat(
concat( concat(substring(un_confnbr,1,4), '-')
, concat(substring(un_confnbr,5,2), '-')
)
, substring(un_confnbr,7,2)
)
, interval 7 day
)
,6,5
) as DueDt
,
to_days(date_sub(now(), interval 7 day))
- (
to_days(
concat(
concat( concat(substring(un_confnbr,1,4), '-')
, concat(substring(un_confnbr,5,2), '-')
)
, substring(un_confnbr,7,2)
)
)
) as Ovr
/* , concat(concat(pa_phn1, ' : '), pa_phn2) as Phone */
, concat( concat( substring(un_confnbr,1,4), '-'),
concat( substring(un_confnbr,5,2), '-'),
concat( substring(un_confnbr,7,2), ' '),
concat( substring(un_confnbr,9,2), ':'),
concat( substring(un_confnbr,11,2), ' '),
concat( substring(un_confnbr,13,2), ' '),
concat(substring(un_confnbr,15), ' ')
) as Confnbr
, pa.pa_id as Id
, '~' as Send -- jmb 12/8/2012
from un_entry un, pa_participant pa, en_summary en
where un.pa_id = pa.pa_id
and un.ev_id =
and un_confnbr = en_confnbr
and substring(un_pay_status,1,1) = 'U' -- 03/19/2012 jmb bg
group by Name, Status, Email, Owed, Fees, Paid, DueDt, Ovr, Confnbr, Id, Send
union all
Select
ap_pay_status as Status
, concat(concat(pa_lname, ', '), pa_fname) as Name
, en_ttl_fees - en_ttl_paid as Owed
/* , insert(pa_email, (instr(pa_email,'@')), 1, ' @') as Email */
, concat(concat(concat(substr(pa_email,1,3)) , '*-*-*-*') , substr(pa_email,7)) as Email
, en_ttl_fees as Fees
, en_ttl_paid as Paid
,
substring(
date_add(
concat(
concat( concat(substring(ap_confnbr,1,4), '-')
, concat(substring(ap_confnbr,5,2), '-')
)
, substring(ap_confnbr,7,2)
)
, interval 7 day
)
,6,5
) as DueDt
,
to_days(date_sub(now(), interval 7 day))
- (
to_days(
concat(
concat( concat(substring(ap_confnbr,1,4), '-')
, concat(substring(ap_confnbr,5,2), '-')
)
, substring(ap_confnbr,7,2)
)
)
) as Ovr
/* , concat(concat(pa_phn1, ' : '), pa_phn2) as Phone */
, concat( concat( substring(ap_confnbr,1,4), '-'),
concat( substring(ap_confnbr,5,2), '-'),
concat( substring(ap_confnbr,7,2), ' '),
concat( substring(ap_confnbr,9,2), ':'),
concat( substring(ap_confnbr,11,2), ' '),
concat( substring(ap_confnbr,13,2), ' '),
concat(substring(ap_confnbr,15), ' ')
) as Confnbr
, pa.pa_id as Id
, '~' as Send -- jmb 12/8/2012
from ap_entry ap, pa_participant pa, en_summary en
where ap.pa_id = pa.pa_id
and ap.ev_id =
and ap_confnbr = en_confnbr
and (substring(ap_pay_status,2,1) = 'P' -- 03/19/2012 jmb bg report partial payment
and
substring(ap_pay_status,1,1) != 'S' -- 03/19/2012 jmb bg do not report scratches
and
en_ttl_fees > en_ttl_paid ) -- 12/07/2013 jmb bg do not report OverPaid Mantis=55
group by Name, Status, Email, Owed, Fees, Paid, DueDt, Ovr, Confnbr, Id, Send
order by DueDt, Name
Admin Menu Return
Handlers Not Paid or Partially Paid
Check Mark = Send |
Admin Selection Options
| o Who is entered and with what STATUS? (w/Drill Down) | Show Entries (Drill Down Details) |
| o How many runs in each class, seperated by day? | Trial Totals: Dogs / Classes / Dinners etc. |
| o Who is entered at what cost? (w/Drill Down) | Participant Summary (Drill Down Details) |
| o Wait List Info, Is the Trial OVER full? | Wait List Determination |
| o What handlers are tardy with their payment? | UnPaid Participants |
| o Which Classes Have Been Approved for Each Person? | Classes "Approved" for Each Person Entered |
| o What is the Running Order for this trial? | Show Running Order |
| o Is the Running Order ready for all to see online? | Publish Running Order(one time only) |
| o Are there scores that need to be posted? | Update/Finalize Scores |
| o What Participants have withdrawn? | Cancelled Entries |
| o Do you need to UPDATE Particpant Status? | Approve, Waitlist, Cancel an Entry |
| o Who paid by check? | Check Payments |
| o Do you need to PRINT Score Sheets? | PRINT Score Sheets |
| o How many USBCHA Open and Nursery Runs for this trial? | Show USBCHA required information |
| o Do Sponsors need to be Managed by Dog? | Dog Sponsor Management |
| o Approve Dogs that made the Draw? | Draw Dog Approval |
| o Manage Your Club Membership? | Club Member Management |
| o Assist with Entry? | Assist a Participant with Entry |
Training ~ Demos ~ Lessons
Dawn Boyce, www.CollieGirl.com
